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>
6.7 KiB
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
- Announce maintenance window to all users (24-48hr notice)
- Create backup of Supabase database (pg_dump via Supabase console or API)
- Verify local postgres is running and accessible
- Verify schema exists in local postgres (or run migrations)
- Stop IMS app:
ssh setia@175.136.239.121 "pm2 stop wsims"
Phase 2: Data Export & Import
- Export from Supabase:
# Use Supabase console or pg_dump with connection string pg_dump <supabase-connection-string> > /tmp/supabase-backup.sql - Transfer dump to production server
- 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" - Verify import completed without errors
Estimated duration: 5-15 minutes for 100MB database
Phase 3: Data Validation
Run validation checks on production server:
- Row count verification: Compare table row counts before/after
- users, incidents, investigations, capa_actions, evidence_files, etc.
- 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 - Password hash validation: Verify password_hash column contains valid bcryptjs hashes (
2aor2bprefix) - Recent data spot check: Verify last 5-10 incidents, users, CAPA records are present
- Timestamp validation: Verify created_at, updated_at timestamps are intact
Phase 4: Application Cutover
- Update production .env.production:
- Remove all
SUPABASE_*variables - Keep
DATABASE_URLpointing to local postgres
- Remove all
- Restart IMS app:
ssh setia@175.136.239.121 "pm2 restart wsims" - Monitor app logs for connection errors
- Manual test:
- Login as admin@ims.local
- View incidents dashboard
- Create a test incident
- Verify email notifications work
- Check app health endpoint or dashboard
Phase 5: Post-Migration
- Keep Supabase backup for 7 days (in case emergency rollback needed)
- Monitor local postgres performance (disk usage, query latency)
- Update documentation:
- CLAUDE.md: Remove Supabase references, add local postgres setup
- memory/ims-database-setup.md: Mark as complete
- After 30 days with no issues, delete Supabase project
Rollback Plan
If critical issues discovered during cutover:
- Stop app
- Restore Supabase connection in .env.production
- Restart app to revert to Supabase
- Investigate issue in local postgres
- 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
- CLAUDE.md: Remove Supabase references, document local postgres requirement
- memory/ims-database-setup.md: Update from "needs setup" to "migrated from Supabase"
- 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)