Data Migration Strategy

This document describes the strategy for migrating data from the legacy PostgreSQL tables (Strapi + Intakes) to the new Go-based Core API schema.

Overview

Migrate data from the legacy PostgreSQL tables to the new schema. The databases can be on the same PostgreSQL server (different schemas) or different servers.

Migration Steps

Step 1: Schema Preparation

# Create new database
createdb restartix_go

# Run migrations
migrate -path migrations -database "postgres://...restartix_go" up

Step 2: Data Migration Script

-- ============================================================================
-- MIGRATION: Legacy → New Schema
-- Run this in a transaction against the NEW database.
-- Assumes old DB is accessible as schema 'legacy' (use foreign data wrapper)
-- or via pg_dump/pg_restore to a staging schema.
-- ============================================================================

-- Connect the old database as a foreign schema (if same server)
-- CREATE EXTENSION IF NOT EXISTS postgres_fdw;
-- CREATE SERVER legacy_server FOREIGN DATA WRAPPER postgres_fdw
--   OPTIONS (dbname 'restartix_legacy');
-- CREATE USER MAPPING FOR restartix_app SERVER legacy_server
--   OPTIONS (user 'legacy_reader', password '...');
-- IMPORT FOREIGN SCHEMA public FROM SERVER legacy_server INTO legacy;

-- Or: import into staging schema via pg_dump

BEGIN;

-- 1. Organizations
INSERT INTO organizations (id, name, slug, tagline, email, phone, website, location,
    created_at, updated_at)
SELECT
    f.id,
    f.name,
    f.slug,
    f.tagline,
    f.email,
    f.phone,
    f.website,
    f.location,
    f.created_at,
    f.updated_at
FROM legacy.franchises f;

-- 1b. Migrate patient_meta_* label overrides → custom_fields (system fields)
-- Legacy pattern: hardcoded label columns on franchise table for PDF customization
-- New pattern: custom_fields with system_key — stable identifier for PDF templates,
-- while key/label are whatever the org admin configured (e.g., Romanian labels)
INSERT INTO custom_fields (organization_id, entity_type, key, label, field_type, sort_order, system_key)
SELECT f.id, 'patient', 'birthdate',  COALESCE(f.patient_meta_birthdate, 'Date of Birth'), 'date', 0, 'patient_birthdate'
FROM legacy.franchises f
UNION ALL
SELECT f.id, 'patient', 'residence',  COALESCE(f.patient_meta_residence, 'Residence'), 'text', 1, 'patient_residence'
FROM legacy.franchises f
UNION ALL
SELECT f.id, 'patient', 'occupation', COALESCE(f.patient_meta_occupation, 'Occupation'), 'text', 2, 'patient_occupation'
FROM legacy.franchises f
UNION ALL
SELECT f.id, 'patient', 'sex',        COALESCE(f.patient_meta_sex, 'Sex'), 'select', 3, 'patient_sex'
FROM legacy.franchises f;

-- Reset sequence
SELECT setval('organizations_id_seq', (SELECT MAX(id) FROM organizations));

-- 2. Users
INSERT INTO users (id, email, username, role, confirmed, blocked, last_activity,
    current_organization_id, created_at, updated_at)
SELECT
    u.id,
    LOWER(u.email),
    LOWER(u.username),
    CASE
        WHEN r.type = 'authenticated' THEN 'patient'::user_role
        WHEN r.type = 'specialist' THEN 'specialist'::user_role
        WHEN r.type = 'admin' THEN 'admin'::user_role
        WHEN r.type = 'customer_support' THEN 'customer_support'::user_role
        WHEN r.type = 'superadmin' THEN 'superadmin'::user_role
        ELSE 'patient'::user_role
    END,
    COALESCE(u.confirmed, FALSE),
    COALESCE(u.blocked, FALSE),
    u.last_activity,
    -- current_organization_id from legacy franchise membership
    (SELECT fl.franchise_id FROM legacy.up_users_franchises_links fl
     WHERE fl.user_id = u.id LIMIT 1),
    u.created_at,
    u.updated_at
FROM legacy.up_users u
LEFT JOIN legacy.up_roles r ON u.role = r.id;

-- NOTE: clerk_user_id will be NULL initially.
-- After Clerk migration, update with:
-- UPDATE users SET clerk_user_id = '...' WHERE email = '...';

SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

-- 3. User-Organization memberships
INSERT INTO user_organizations (user_id, organization_id)
SELECT fl.user_id, fl.franchise_id
FROM legacy.up_users_franchises_links fl;

-- 4. Specialties
INSERT INTO specialties (id, title, slug, organization_id, created_at, updated_at)
SELECT s.id, s.title, s.slug, sl.franchise_id, s.created_at, s.updated_at
FROM legacy.specialities s
LEFT JOIN legacy.specialities_franchise_links sl ON sl.speciality_id = s.id;

SELECT setval('specialties_id_seq', (SELECT MAX(id) FROM specialties));

-- 5. Specialists
INSERT INTO specialists (id, user_id, name, title, slug,
    minicrm_name, created_at, updated_at)
SELECT s.id,
    (SELECT ul.user_id FROM legacy.specialists_user_links ul WHERE ul.specialist_id = s.id),
    s.name, s.title, s.slug,
    s.minicrm_name, s.created_at, s.updated_at
FROM legacy.specialists s;

SELECT setval('specialists_id_seq', (SELECT MAX(id) FROM specialists));

-- 6. Specialist-Specialty links
INSERT INTO specialist_specialties (specialist_id, specialty_id)
SELECT ssl.specialist_id, ssl.speciality_id
FROM legacy.specialists_specialities_links ssl;

-- 7a. Patient persons (portable profile)
INSERT INTO patient_persons (id, user_id, name, phone_encrypted)
SELECT p.id,
    (SELECT pl.user_id FROM legacy.patients_user_links pl WHERE pl.patient_id = p.id),
    p.name,
    NULL  -- phone_encrypted set post-migration via Go script
FROM legacy.patients p;

SELECT setval('patient_persons_id_seq', (SELECT MAX(id) FROM patient_persons));

-- 7b. Patient person managers (self-management links)
INSERT INTO patient_person_managers (patient_person_id, manager_user_id, relationship)
SELECT pp.id, pp.user_id, 'self'
FROM patient_persons pp
WHERE pp.user_id IS NOT NULL;

-- 7c. Patients (org-patient link)
INSERT INTO patients (id, organization_id, patient_person_id, consumer_id, created_at, updated_at)
SELECT p.id,
    (SELECT fl.franchise_id FROM legacy.up_users_franchises_links fl
     JOIN legacy.patients_user_links pl ON pl.user_id = fl.user_id
     WHERE pl.patient_id = p.id LIMIT 1),
    p.id,  -- patient_person_id = same id (1:1 from legacy)
    p.consumer_id, p.created_at, p.updated_at
FROM legacy.patients p;
-- NOTE: profile_shared defaults to FALSE; consent collected post-migration
-- NOTE: phone is on patient_persons, encrypted post-migration via Go script
-- NOTE: password field is NOT migrated (Clerk handles auth)

SELECT setval('patients_id_seq', (SELECT MAX(id) FROM patients));

-- Continue for all tables: form_templates, appointment_templates,
-- appointments, forms, appointment_documents,
-- custom_fields, custom_field_values, organization_integrations...

COMMIT;

Step 3: Post-Migration Encryption

After data is migrated, encrypt PHI fields that were plaintext in the legacy system:

// cmd/migrate-encrypt/main.go
// One-time script to encrypt existing plaintext data

func main() {
    // 1. Encrypt patient phone numbers (stored on patient_persons)
    rows, _ := db.Query(ctx, "SELECT id, phone FROM patient_persons_staging WHERE phone IS NOT NULL")
    for rows.Next() {
        var id int64
        var phone string
        rows.Scan(&id, &phone)

        encrypted, _ := encryptor.Encrypt(phone)
        db.Exec(ctx, "UPDATE patient_persons SET phone_encrypted = $1 WHERE id = $2", encrypted, id)
    }

    // 2. Encrypt form values
    rows, _ = db.Query(ctx, "SELECT id, value FROM form_values_staging WHERE value IS NOT NULL")
    for rows.Next() {
        var id int64
        var value string
        rows.Scan(&id, &value)

        encrypted, _ := encryptor.Encrypt(value)
        db.Exec(ctx, "UPDATE form_values SET value_encrypted = $1 WHERE id = $2", encrypted, id)
    }

    // 3. Re-encrypt organization integration API keys (already encrypted, but different format)
    // ... decrypt with old key, re-encrypt with new key
}

Step 4: Clerk User Migration

// cmd/migrate-clerk/main.go
// Create Clerk accounts for existing users

func main() {
    rows, _ := db.Query(ctx, "SELECT id, email, username FROM users WHERE clerk_user_id IS NULL")
    for rows.Next() {
        var id int64
        var email, username string
        rows.Scan(&id, &email, &username)

        // Create user in Clerk
        clerkUser, err := clerkClient.Users.Create(ctx, &clerk.CreateUserParams{
            EmailAddress: []string{email},
            Username:     &username,
            // They'll need to reset password on first login
        })
        if err != nil {
            slog.Error("failed to create Clerk user", "email", email, "error", err)
            continue
        }

        // Link Clerk ID to internal user
        db.Exec(ctx, "UPDATE users SET clerk_user_id = $1 WHERE id = $2", clerkUser.ID, id)
    }
}

Migration Verification Checklist

-- Verify record counts match
SELECT 'organizations' as entity, COUNT(*) FROM organizations
UNION ALL SELECT 'users', COUNT(*) FROM users
UNION ALL SELECT 'specialties', COUNT(*) FROM specialties
UNION ALL SELECT 'specialists', COUNT(*) FROM specialists
UNION ALL SELECT 'patient_persons', COUNT(*) FROM patient_persons
UNION ALL SELECT 'patients', COUNT(*) FROM patients
UNION ALL SELECT 'appointment_templates', COUNT(*) FROM appointment_templates
UNION ALL SELECT 'appointments', COUNT(*) FROM appointments
UNION ALL SELECT 'forms', COUNT(*) FROM forms
UNION ALL SELECT 'appointment_documents', COUNT(*) FROM appointment_documents
UNION ALL SELECT 'custom_fields', COUNT(*) FROM custom_fields
UNION ALL SELECT 'custom_field_values', COUNT(*) FROM custom_field_values;

-- Verify FK integrity
SELECT 'appointments with missing org' as check,
       COUNT(*) FROM appointments a
       WHERE NOT EXISTS (SELECT 1 FROM organizations o WHERE o.id = a.organization_id)
UNION ALL
SELECT 'appointments with missing patient_person',
       COUNT(*) FROM appointments a
       WHERE a.patient_person_id IS NOT NULL
       AND NOT EXISTS (SELECT 1 FROM patient_persons pp WHERE pp.id = a.patient_person_id);
-- ... repeat for all FKs

-- Verify RLS works
SET app.current_user_id = '42';
SET app.current_org_id = '1';
SET app.current_role = 'patient';
SELECT COUNT(*) FROM appointments; -- Should only return user 42's appointments in org 1

SET app.current_role = 'superadmin';
SELECT COUNT(*) FROM appointments; -- Should return all

Parallel Running Strategy

Phase A: Read-Only Go API (2 weeks)

Run the Go API alongside the legacy API. Go API reads from same database (read-only) to verify correctness.

Client → Legacy API (primary, read/write)
         Go API (shadow, read-only) → same PostgreSQL

• Compare responses between legacy and Go for same requests
• Log discrepancies
• Fix until responses match

Phase B: Dual-Write (2 weeks)

Legacy API still handles writes, Go API handles reads + verifies writes.

Client → Load Balancer
         ├── GET requests → Go API
         └── POST/PUT/DELETE → Legacy API

Phase C: Go Primary (1 week)

Go handles everything. Legacy API is kept on standby for emergency rollback.

Client → Go API (primary)
         Legacy API (standby, ready for rollback)

Phase D: Legacy Decommission

Remove legacy API, Go is the sole API.

Client → Go API

Rollback Procedure

If critical issues are found after Go goes primary:

1. Switch load balancer back to legacy API
2. Any writes that Go made to the database are compatible (same schema)
3. Investigate and fix issues in Go
4. Re-attempt cutover

Implementation Order

When starting the Go project, build in this order:

1. Project skeleton - main.go, config, database connection, health check
2. Auth middleware - Clerk verification, user context loading
3. Organization middleware - RLS session variables
4. User endpoints - /me, /me/switch-organization, Clerk webhook
5. Organization CRUD - Basic org management
6. Appointment CRUD - Basic CRUD first
7. Appointment workflows - createFromTemplate, book (most complex)
8. Calendar - Calendar view queries
9. Patient management - Onboard, CRUD
10. Specialist management - CRUD
11. Form system - Templates, instances, values (with profile auto-fill sync)
12. Reports & Prescriptions - CRUD (via appointment_documents)
13. Custom fields - Field definitions, values, patient/specialist profiles
14. Export - CSV export with security filters
15. GDPR endpoints - Export, delete, anonymize
16. Telemetry service integration - Audit forwarding + analytics
17. Scheduling domain - Availability engine, holds, booking (see ../scheduling/)
18. Daily.co integration - Videocall room management
19. Webhook system - Org-scoped event subscriptions (see ../webhooks/)
20. S3 integration - File upload/download