Files
ims/docs/superpowers/specs/2026-09-30-supabase-to-local-migration-design.md
adminandClaude Haiku 4.5 fe0dd27448 docs: Supabase to local postgres migration design spec
Migration plan for moving from Supabase cloud to self-hosted local postgres.
Scope: 30 users, 500 incidents, ~100MB database.
Downtime: Planned maintenance window.
Approach: Direct pg_dump/restore with validation.

Co-Authored-By: Claude Haiku 4.5 <noreply@anthropic.com>
2026-09-30 08:06:11 +08:00

6.7 KiB

Supabase → Local Postgres Migration Design

Date: 2026-09-30
Status: Approved
Goal: Migrate from Supabase cloud to self-hosted local postgres, reduce cloud dependency

Overview

IMS currently uses Supabase cloud postgres as primary database. Migration to local postgres (127.0.0.1:5433/ims) will enable full self-hosting and eliminate cloud dependency.

Data scope: 30 users, 500 incidents, ~100MB
Downtime: Planned maintenance window acceptable
Services affected: Database only (no auth, storage, or real-time services to migrate)

Architecture

Current State

  • Primary DB: Supabase postgres (nkcfjbgappslicotwopl.supabase.co)
  • Local postgres: Configured in DATABASE_URL but empty, not used
  • App config: .env.production has both DATABASE_URL and SUPABASE_* vars
  • Deployment: /var/www/ims on production server

Target State

  • Primary DB: Local postgres (127.0.0.1:5433/ims)
  • Supabase: Fully decommissioned
  • App config: DATABASE_URL only, all SUPABASE_* vars removed
  • Data retention: Local backups for disaster recovery

Migration Plan

Phase 1: Pre-Migration Setup

  1. Announce maintenance window to all users (24-48hr notice)
  2. Create backup of Supabase database (pg_dump via Supabase console or API)
  3. Verify local postgres is running and accessible
  4. Verify schema exists in local postgres (or run migrations)
  5. Stop IMS app: ssh setia@175.136.239.121 "pm2 stop wsims"

Phase 2: Data Export & Import

  1. Export from Supabase:
    # Use Supabase console or pg_dump with connection string
    pg_dump <supabase-connection-string> > /tmp/supabase-backup.sql
    
  2. Transfer dump to production server
  3. Import to local postgres:
    ssh setia@175.136.239.121 "psql -h 127.0.0.1 -p 5433 -U app_admin -d ims -f /tmp/supabase-backup.sql"
    
  4. Verify import completed without errors

Estimated duration: 5-15 minutes for 100MB database

Phase 3: Data Validation

Run validation checks on production server:

  1. Row count verification: Compare table row counts before/after
    • users, incidents, investigations, capa_actions, evidence_files, etc.
  2. Integrity checks: Verify foreign key constraints
    SELECT COUNT(*) FROM users;  -- Should match Supabase count
    SELECT COUNT(*) FROM incidents WHERE reported_by NOT IN (SELECT id FROM users);  -- No orphans
    
  3. Password hash validation: Verify password_hash column contains valid bcryptjs hashes (2a or 2b prefix)
  4. Recent data spot check: Verify last 5-10 incidents, users, CAPA records are present
  5. Timestamp validation: Verify created_at, updated_at timestamps are intact

Phase 4: Application Cutover

  1. Update production .env.production:
    • Remove all SUPABASE_* variables
    • Keep DATABASE_URL pointing to local postgres
  2. Restart IMS app:
    ssh setia@175.136.239.121 "pm2 restart wsims"
    
  3. Monitor app logs for connection errors
  4. Manual test:
    • Login as admin@ims.local
    • View incidents dashboard
    • Create a test incident
    • Verify email notifications work
  5. Check app health endpoint or dashboard

Phase 5: Post-Migration

  1. Keep Supabase backup for 7 days (in case emergency rollback needed)
  2. Monitor local postgres performance (disk usage, query latency)
  3. Update documentation:
    • CLAUDE.md: Remove Supabase references, add local postgres setup
    • memory/ims-database-setup.md: Mark as complete
  4. After 30 days with no issues, delete Supabase project

Rollback Plan

If critical issues discovered during cutover:

  1. Stop app
  2. Restore Supabase connection in .env.production
  3. Restart app to revert to Supabase
  4. Investigate issue in local postgres
  5. Re-attempt migration after fixes

Rollback window: 7 days (Supabase backup retained)

Implementation Details

Prerequisites

  • Supabase connection credentials (for pg_dump)
  • Local postgres credentials (app_user, app_admin)
  • Access to production server (ssh -p 9321)
  • PM2 running on production

Validation Queries

-- Count check
SELECT 'users' as table_name, COUNT(*) as count FROM users
UNION ALL
SELECT 'incidents', COUNT(*) FROM incidents
UNION ALL
SELECT 'investigations', COUNT(*) FROM investigations
UNION ALL
SELECT 'capa_actions', COUNT(*) FROM capa_actions
UNION ALL
SELECT 'evidence_files', COUNT(*) FROM evidence_files;

-- Orphan check
SELECT COUNT(*) as orphan_users FROM incidents WHERE reported_by NOT IN (SELECT id FROM users);
SELECT COUNT(*) as orphan_incidents FROM investigations WHERE incident_id NOT IN (SELECT id FROM incidents);

-- Recent data check
SELECT id, email, created_at FROM users ORDER BY created_at DESC LIMIT 5;
SELECT id, title, created_at FROM incidents ORDER BY created_at DESC LIMIT 5;

Success Criteria

  • All data successfully imported to local postgres
  • Row counts match Supabase
  • No orphaned foreign keys
  • Password hashes intact
  • App connects to local postgres without errors
  • Login works (test with admin@ims.local)
  • Recent incidents visible and editable
  • No error logs during cutover window
  • Performance acceptable on local postgres

Timeline

  • Preparation: 1-2 hours (backup, schema verification, announce)
  • Migration: 15-30 minutes (export, import, validation)
  • Testing: 30 minutes (health checks, manual testing)
  • Total downtime: 1-2 hours

Risks & Mitigations

Risk Mitigation
Data corruption during transfer Backup Supabase before migration, validate checksums
Foreign key constraint violations Run orphan checks post-import
App connection fails Have Supabase rollback ready, test connection strings
Local postgres out of disk space Check disk before import, provision sufficient space
Password hashes not recognized Verify bcryptjs format ($2a$/2b), test login before declaring success

Configuration Changes

.env.production (Production Server)

Remove:

NEXT_PUBLIC_SUPABASE_URL=...
NEXT_PUBLIC_SUPABASE_ANON_KEY=...
SUPABASE_SERVICE_ROLE_KEY=...

Keep:

DATABASE_URL=postgres://app_user:145edf53b9fdca6b189ccf6260422809355b0ce258178ecdeba206f825dcd144@127.0.0.1:5433/ims
DATABASE_URL_ADMIN=postgres://app_admin:7b7c201d2461c8868a7427f81182aa36e1df4eabc0520d0621a5692dee11feeb@127.0.0.1:5433/ims

Documentation Updates

  1. CLAUDE.md: Remove Supabase references, document local postgres requirement
  2. memory/ims-database-setup.md: Update from "needs setup" to "migrated from Supabase"
  3. New doc: /docs/database/local-postgres-setup.md (backup/restore procedures)

Future Considerations

  • Automated backups of local postgres (daily dump to /var/backups/ims/)
  • Monitoring for disk space on /var/www/ims database volume
  • Consider replication to secondary postgres for HA (future phase)