Data Migration Documentation¶

This document describes the process for migrating data from the existing Supabase database to the new Medusa PostgreSQL database.

Overview¶

The migration scripts transfer data from Supabase tables to Medusa custom module tables: - advertisements → advertisement table - settings → setting table - translations → translation table

Prerequisites¶

1. Supabase Access¶

You need access to the existing Supabase project with: - Supabase project URL - Service role key (has full read access to all tables)

2. Medusa Database Access¶

You need the Medusa PostgreSQL connection string from the DATABASE_URL environment variable.

3. Required Dependencies¶

Install the following dependencies in the backend:

cd backend
npm install @supabase/supabase-js pg
npm install --save-dev @types/node

4. Environment Variables¶

Add the following to your .env file:

# Supabase Configuration
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_SERVICE_KEY=your-service-role-key

# Medusa Database (already exists)
DATABASE_URL=postgresql://postgres:password@localhost:5432/medusa

Migration Scripts¶

1. Migration Script¶

File: backend/scripts/migrate-from-supabase.ts

This script performs the actual data migration:

Connects to both Supabase and Medusa PostgreSQL
Fetches data from Supabase tables
Inserts data into Medusa tables
Handles conflicts with ON CONFLICT DO NOTHING for advertisements and translations
Handles conflicts with ON CONFLICT DO UPDATE for settings (upsert behavior)
Reports counts before and after migration

Running the Migration¶

cd backend
npx ts-node scripts/migrate-from-supabase.ts

Or using the compiled version:

cd backend
node dist/scripts/migrate-from-supabase.js

Expected Output¶

Starting data migration from Supabase to Medusa...
==============================================
Connected to Medusa PostgreSQL database

Initial counts:
  Supabase Advertisements: 10
  Supabase Settings: 5
  Supabase Translations: 150
  Medusa Advertisements: 0
  Medusa Settings: 0
  Medusa Translations: 0

Starting migration:
Migrating advertisements...
Found 10 advertisements in Supabase
Inserted 10 advertisements into Medusa
Migrating settings...
Found 5 settings in Supabase
Inserted 5 settings into Medusa
Migrating translations...
Found 150 translations in Supabase
Inserted 150 translations into Medusa

Final counts:
  Medusa Advertisements: 10
  Medusa Settings: 5
  Medusa Translations: 150

==============================================
Migration completed successfully!
  Advertisements migrated: 10
  Settings migrated: 5
  Translations migrated: 150

2. Verification Script¶

File: backend/scripts/verify-migration.ts

This script verifies that the migration was successful:

Compares record counts between Supabase and Medusa
Performs spot checks on sample records
Compares field values for sample records
Reports pass/fail status for each table

Running the Verification¶

cd backend
npx ts-node scripts/verify-migration.ts

Or using the compiled version:

cd backend
node dist/scripts/verify-migration.js

Expected Output¶

Starting migration verification...
==============================================
Connected to Medusa PostgreSQL database

--- Count Verification ---
Verifying advertisements...
  Supabase count: 10
  Medusa count: 10
  Match: ✓
  Sample IDs: adv_001, adv_002, adv_003, adv_004, adv_005
Verifying settings...
  Supabase count: 5
  Medusa count: 5
  Match: ✓
  Sample IDs: free_delivery_threshold, pickup_message, ceo_whatsapp
Verifying translations...
  Supabase count: 150
  Medusa count: 150
  Match: ✓
  Sample IDs: trans_001, trans_002, trans_003, trans_004, trans_005

--- Data Spot Check ---

Spot checking advertisement data...
  ✓ Advertisement adv_001: Data matches
  ✓ Advertisement adv_002: Data matches
  ✓ Advertisement adv_003: Data matches

Spot checking settings data...
  ✓ Setting free_delivery_threshold: Value matches
  ✓ Setting pickup_message: Value matches
  ✓ Setting ceo_whatsapp: Value matches

Spot checking translation data...
  ✓ Translation trans_001: Data matches
  ✓ Translation trans_002: Data matches
  ✓ Translation trans_003: Data matches

==============================================
Verification Summary:
==============================================
advertisements: ✓ PASS (10 → 10)
settings: ✓ PASS (5 → 5)
translations: ✓ PASS (150 → 150)

==============================================
✓ All counts match!
Migration verification PASSED

Data Mapping¶

Advertisements¶

Supabase Field	Medusa Field	Notes
id	id	Direct mapping
title	title	Direct mapping
image_url	image_url	Direct mapping
link_url	link_url	Direct mapping
location	location	Defaults to "home_slider" if null
display_order	display_order	Defaults to 0 if null
is_active	is_active	Defaults to true if undefined
created_at	created_at	Defaults to current timestamp if null

Settings¶

Supabase Field	Medusa Field	Notes
key	key	Direct mapping (primary key)
value	value	Direct mapping

Conflict Handling: Settings use ON CONFLICT DO UPDATE, so existing settings in Medusa will be updated with values from Supabase.

Translations¶

Supabase Field	Medusa Field	Notes
id	id	Direct mapping
language_code	language_code	Defaults to "ar" if null
key	key	Direct mapping
value	value	Direct mapping
section	section	Direct mapping, null if not provided

Troubleshooting¶

Script Fails to Connect to Supabase¶

Error: Error connecting to Supabase

Solution: 1. Verify SUPABASE_URL is correct 2. Verify SUPABASE_SERVICE_KEY is valid and has service role permissions 3. Check network connectivity to Supabase

Script Fails to Connect to Medusa Database¶

Error: Error connecting to Medusa PostgreSQL

Solution: 1. Verify DATABASE_URL is correct 2. Ensure PostgreSQL is running (if using Docker: docker compose ps postgres) 3. Check database credentials

Count Mismatch After Migration¶

Error: Verification shows count mismatch

Solution: 1. Check for duplicate IDs in Supabase data 2. Check for data type mismatches 3. Review migration script logs for insertion errors 4. Run the migration script again (it will skip existing records due to ON CONFLICT)

Field Value Mismatch in Spot Check¶

Error: Spot check shows data mismatch

Solution: 1. Verify the field mapping in the migration script 2. Check for null handling logic 3. Review Supabase data for unexpected values 4. Update migration script if field mapping needs adjustment

Missing Dependencies¶

Error: Cannot find module '@supabase/supabase-js' or Cannot find module 'pg'

Solution:

cd backend
npm install @supabase/supabase-js pg

TypeScript Compilation Errors¶

Error: TypeScript errors when running scripts

Solution: 1. Ensure @types/node is installed: npm install --save-dev @types/node 2. Use npx ts-node to run TypeScript directly 3. Or compile first: npm run build then run the compiled JS file

Running Migration in Docker¶

If running the backend in Docker, you have two options:

Option 1: Run Scripts Inside Container¶

# Enter the medusa container
docker compose exec medusa bash

# Run the migration script
npx ts-node scripts/migrate-from-supabase.ts

# Run the verification script
npx ts-node scripts/verify-migration.ts

Option 2: Run Scripts from Host¶

# Set environment variables and run from host
cd backend
SUPABASE_URL=... SUPABASE_SERVICE_KEY=... DATABASE_URL=... npx ts-node scripts/migrate-from-supabase.ts

Rollback Plan¶

If the migration needs to be rolled back:

Option 1: Truncate Tables (Complete Rollback)¶

docker compose exec postgres psql -U postgres -d medusa -c "TRUNCATE TABLE advertisement, setting, translation CASCADE;"

Option 2: Delete Specific Records¶

If you have a list of migrated IDs, delete them individually:

DELETE FROM advertisement WHERE id IN ('adv_001', 'adv_002', ...);
DELETE FROM setting WHERE key IN ('key1', 'key2', ...);
DELETE FROM translation WHERE id IN ('trans_001', 'trans_002', ...);

Post-Migration Steps¶

After successful migration:

Verify Data in Admin Dashboard
Navigate to http://localhost:9000/app
Check Advertisements page for migrated ads
Check Settings page for migrated settings
Check Translations page for migrated translations
Test Store Frontend
Navigate to http://localhost:3000
Verify advertisements display correctly
Verify settings are applied (delivery threshold, etc.)
Verify translations load correctly
Update DNS/Routing
If switching from Supabase to Medusa, update any DNS or routing configuration
Update environment variables in production
Decommission Supabase
After verifying everything works, consider archiving or decommissioning the Supabase project
Keep backups for a reasonable period

Security Considerations¶

Service Role Key: The Supabase service role key has full access to the database. Keep it secure and never commit it to version control.
Database Credentials: The Medusa database credentials should also be kept secure and not committed to version control.
Network Access: Ensure the migration scripts are run from a secure network with access to both Supabase and Medusa databases.
Backup: Always backup both databases before running migration scripts.

Performance Considerations¶

Batch Size: The current migration script processes records one at a time. For large datasets (10,000+ records), consider batching inserts.
Transaction Wrapping: For very large migrations, consider wrapping inserts in transactions for better performance and atomicity.
Indexing: Ensure Medusa tables have appropriate indexes before migration for faster inserts.

Additional Notes¶

Idempotency: The migration script is idempotent - running it multiple times will not create duplicate records due to ON CONFLICT clauses.
Partial Migration: If the script fails partway through, you can run it again to complete the migration.
Custom Fields: If Supabase tables have additional fields not in the Medusa schema, you'll need to either:
Add those fields to the Medusa models
Ignore those fields in the migration script
Map them to existing Medusa fields