Data Migration Strategy

Superseded design generation — strategy survives, SQL does not

Every SQL block in this document was drafted against a schema generation that never shipped. It assumed a users table, patient_persons, user_organizations, BIGSERIAL/integer PKs, a users.role column with a user_role enum, and SET app.current_role = '<role>' as an authorization mechanism. None of those exist. This is not a rename problem — the described shape is wrong.

What the platform actually has (verified against services/api/migrations/core/, 2026-08-02):

Superseded assumption	Actual schema
users table, user_id columns	principals is the root identity for every actor (human, agent, service_account, system); human profiles live in humans (PK principal_id → principals(id)). humans carries no name column — it holds provider_subject_id, provider_org_id, email, confirmed, blocked, portal_credential_generation, last_activity, preferred_language, timezone. Actor columns are principal_id (any actor) or human_id (a human is semantically required). See decisions.md → Why principals as the root identity.
patient_persons	patient_profiles — the portable, patient-owned identity. No organization_id; it is the documented RLS exception.
user_organizations	organization_memberships (principal_id, organization_id, role_id, is_owner, last_used_at). The only per-org membership table — and staff-only: patients are never memberships (patients rows grant portal access at an org).
users.role enum, SET app.current_role	Per-org permission codes. RLS calls current_app_has_permission(resource, action). Never a role-string compare. Role templates are rows in roles (specialist, customer_support, admin, seeded with organization_id IS NULL); superadmin is platform_memberships, human-only.
BIGSERIAL / integer PKs, setval(..._id_seq)	UUIDv7 (P26). There are no sequences to reset.
app.current_user_id, current_app_user_id()	Session vars app.current_principal_id, app.current_actor_type, app.current_org_id; helpers current_app_principal_id(), current_app_principal_type(), current_app_org_id().

Timing has also changed. The legacy migration is now Phase 3 of platform-completion.md — planned only after the platform is feature-complete, with no target date. It cannot be pulled forward: there is nowhere to migrate ~20k legacy patients into until F1–F6 (specialists, forms, scheduling, appointments, documents) exist.

Current sourcing thinking lives elsewhere. leo-port-map.md §8.11 supersedes this document's assumptions about where legacy data comes from — most importantly, the Intakes Postgres schema is readable today at restartix-intakes/core/db/drizzle/schema.ts, which several earlier port maps claimed was unavailable.

What still holds: the migration strategy — foreign-data-wrapper bridge, staged parallel running, cutover, rollback. Read those sections as background. Treat the SQL below as annotated pseudocode showing target shape and open questions, never as a runnable script.

This document describes the strategy for migrating data from the legacy PostgreSQL tables (Strapi + Intakes) to the platform's Go 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.

Two legacy sources, not one:

• Strapi — the franchise/user/patient/specialist record (legacy.franchises, legacy.up_users, legacy.up_roles, legacy.patients, legacy.specialists, and their *_links join tables). These legacy.* names in the SQL below are the real source tables and are correct as written.
• Intakes — bookings, openings, availability, holds. Schema at restartix-intakes/core/db/drizzle/schema.ts; see leo-port-map.md §0.1.

Throughout the SQL below: legacy.* = source (correct as-is); everything unqualified = target (corrected in this pass, or marked unbuilt).

Migration Steps

Step 1: Schema Preparation

Migrations run against DATABASE_DIRECT_URL (port 5432), never through pgbouncer — golang-migrate takes a session-scoped pg_advisory_lock (P44).

# From services/api/
make migrate-up   # migrate -path migrations/core -database "$DATABASE_DIRECT_URL" up

Step 2: ID mapping is a prerequisite, not a detail

Legacy PKs are integers. Platform PKs are UUIDv7 (P26). No legacy ID can be carried across as a PK, which means every INSERT ... SELECT l.id in the original draft of this document was structurally impossible, not merely misnamed.

The migration tool therefore needs a staging id-map — one row per (legacy table, legacy integer id, minted UUID) — populated before any target insert and joined on for every FK. This is migration-tool scaffolding in a staging schema, not a platform table, and its shape is deliberately undesigned here (Phase 3 work; see platform-completion.md).

-- Illustrative only. Lives in a staging schema, dropped after cutover.
CREATE TABLE staging.id_map (
    legacy_table TEXT   NOT NULL,
    legacy_id    BIGINT NOT NULL,
    new_id       UUID   NOT NULL,
    PRIMARY KEY (legacy_table, legacy_id)
);

Everything below assumes staging.id_map exists and reads m.new_id wherever a legacy integer id used to be written directly.

Step 3: Data Migration Script

-- ============================================================================
-- MIGRATION: Legacy → Platform schema
-- Run against the PLATFORM database.
-- Assumes the legacy DB is accessible as schema 'legacy' (foreign data
-- wrapper) or was pg_restore'd into a staging schema.
--
-- ANNOTATED PSEUDOCODE. Target shape is correct as of 2026-08-02; the
-- transaction boundaries, batching and error handling for ~20k users are
-- Phase 3 work and are NOT designed here.
-- ============================================================================

-- 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
-- Legacy `franchises` → `organizations`. Column names line up; the PK does
-- not (integer → UUID), so mint and record the mapping.
-- ---------------------------------------------------------------------------
WITH minted AS (
    INSERT INTO organizations (name, slug, tagline, email, phone, website, location,
        created_at, updated_at)
    SELECT f.name, f.slug, f.tagline, f.email, f.phone, f.website, f.location,
           f.created_at, f.updated_at
    FROM legacy.franchises f
    RETURNING id, slug
)
INSERT INTO staging.id_map (legacy_table, legacy_id, new_id)
SELECT 'franchises', f.id, m.id
FROM legacy.franchises f JOIN minted m ON m.slug = f.slug;

-- NOTE: `organizations` also carries description, logo_url, icon_url,
-- language_code, portal_self_signup_enabled, branding JSONB, tenancy_mode,
-- activated_at. Legacy has no equivalents; defaults apply. `tenancy_mode`
-- stays 'shared' — dedicated mode is reserved, not sellable.
-- No sequence to reset: PKs are UUIDs.

-- ---------------------------------------------------------------------------
-- 1b. patient_meta_* label overrides  → ⛔ TARGET NOT BUILT
--
-- Legacy pattern (verified in restartix-leo-api/src/api/franchise/
-- content-types/franchise/schema.json): five hardcoded caption columns on the
-- franchise row — patient_meta_birthdate, _residence, _occupation, _sex,
-- _cnp — that customise how those fields are LABELLED per franchise. They are
-- captions, not values; the values live in `meta_values` (see step 6a-bis).
--
-- The rationale for porting them is sound and worth preserving: the platform
-- equivalent is a per-org custom-field definition carrying BOTH a stable
-- system identifier (so PDF templates bind to something that survives a
-- relabel) and an org-authored label (so a Romanian clinic sees Romanian
-- captions). Legacy already has the definition half — `meta_fields` has
-- entity / franchise / key / label / type / options / is_private — so this is
-- a shape the port can follow rather than invent.
--
-- But there is NO `custom_fields` table in any migration. Nor
-- `custom_field_values`, `form_templates`, `forms`, `documents` or
-- `pdf_templates`. This is F3 (Forms) territory and is unbuilt.
--
-- Do not draft the DDL here. The authoritative specs are
-- apps/docs/features/forms/ and leo-port-map.md §8 (custom fields, system
-- fields, the CNP opt-in decision). Settled 2026-08-02: CNP is opt-in per
-- template, stored pii_regulated/encrypted BYTEA on patient_profiles —
-- never in a generic value store.
-- ---------------------------------------------------------------------------

-- ---------------------------------------------------------------------------
-- 2. Actors: principals + humans
-- There is no `users` table. Every legacy user becomes a `principals` row of
-- type 'human' plus a `humans` row. `humans` has NO name and NO username
-- column — see the open question below.
-- ---------------------------------------------------------------------------
WITH minted AS (
    INSERT INTO principals (principal_type, created_at)
    SELECT 'human', u.created_at
    FROM legacy.up_users u
    RETURNING id
),
-- (Illustrative: a real run pairs each minted principal to its source row
-- deterministically — e.g. one INSERT per legacy row in the tool, not a
-- set-based RETURNING that loses the correspondence.)
mapped AS (
    SELECT * FROM staging.id_map WHERE legacy_table = 'up_users'
)
INSERT INTO humans (principal_id, email, confirmed, blocked, last_activity,
    provider_subject_id)
SELECT m.new_id,
       LOWER(u.email),
       COALESCE(u.confirmed, FALSE),
       COALESCE(u.blocked, FALSE),
       u.last_activity,
       NULL          -- see Step 5: provider_subject_id is set post-migration
FROM legacy.up_users u
JOIN mapped m ON m.legacy_id = u.id;

-- ⚠️ OPEN — legacy `up_users` carries THREE name-ish fields (`username`
--    Required+Unique, `name`, `displayName`) and `humans` has NO name column
--    at all. Patient names land on `patient_profiles.name`; staff display
--    names have no home in the shipped schema. Decide in Phase 3: drop them,
--    fold into patient_profiles.name for patients, or add a staff-profile
--    column as part of F1. Do not add a column to `humans` speculatively.
--
-- ⚠️ OPEN — `humans.email` is NOT NULL and `provider_subject_id` is UNIQUE.
--    Legacy duplicate/blank emails must be resolved BEFORE this insert.
--
-- NOTE: legacy `up_users.currentFranchise` has no target column, by design.
-- "Which org am I in" is DERIVED from organization_memberships.last_used_at
-- (staff) and patients.last_used_at (patients) — symmetric by design (P35).
-- Seeding those two timestamps from currentFranchise is a reasonable way to
-- preserve the patient's landing org across cutover; it is an open call, not
-- a settled one.
--
-- NOTE: legacy `password`, `resetPasswordToken`, `confirmationToken` and
-- `refreshToken` are NOT migrated — the auth provider owns credentials.

-- ---------------------------------------------------------------------------
-- 3. Roles: NOT a column on the actor
-- Legacy `up_roles.type` was a single global role per user. The platform has
-- per-org memberships pointing at role templates, and patients are not
-- memberships at all.
--
--   'specialist' | 'admin' | 'customer_support' → organization_memberships
--                                                  (role_id → roles.code)
--   'authenticated' (= patient)                 → a `patients` row (step 6c);
--                                                  NO membership row
--   'superadmin'                                → platform_memberships
--                                                  (human-only CHECK; handled
--                                                   manually, not bulk-migrated)
-- ---------------------------------------------------------------------------
INSERT INTO organization_memberships (principal_id, organization_id, role_id)
SELECT um.new_id,
       fm.new_id,
       (SELECT r.id FROM roles r
        WHERE r.organization_id IS NULL
          AND r.code = CASE lr.type
                           WHEN 'specialist'       THEN 'specialist'
                           WHEN 'admin'            THEN 'admin'
                           WHEN 'customer_support' THEN 'customer_support'
                       END)
FROM legacy.up_users_franchises_links fl
JOIN legacy.up_users u  ON u.id = fl.user_id
JOIN legacy.up_roles  lr ON lr.id = u.role
JOIN staging.id_map um ON um.legacy_table = 'up_users'   AND um.legacy_id = fl.user_id
JOIN staging.id_map fm ON fm.legacy_table = 'franchises' AND fm.legacy_id = fl.franchise_id
WHERE lr.type IN ('specialist', 'admin', 'customer_support');

-- ⚠️ OPEN — `is_owner`. Exactly one owner per org is enforced by a partial
--    unique index, and org-creation normally inserts it in the same
--    transaction. Legacy carries no owner signal. Who owns each of the
--    migrated orgs is a business decision, not a query.
--
-- ⚠️ Permissions, not roles. Nothing downstream should ever compare
--    role codes — RLS calls current_app_has_permission(resource, action).
--    The role template is only how permission grants are bundled.

-- ---------------------------------------------------------------------------
-- 4. Specialties  → ⛔ TARGET NOT BUILT
-- 5. Specialists / specialist_specialties → ⛔ TARGET NOT BUILT
--
-- No `specialties`, `specialists`, `specialist_specialties`, `offerings`,
-- `calendars`, `availability` or `appointments` table exists in any migration.
-- This is F1 + F2.1 + F4 + F5, unbuilt.
--
-- Sources are real and mapped: legacy.specialities,
-- legacy.specialities_franchise_links, legacy.specialists,
-- legacy.specialists_user_links, legacy.specialists_specialities_links,
-- plus the Intakes side (openings, schedules, overrides) at
-- restartix-intakes/core/db/drizzle/schema.ts.
--
-- Settled 2026-08-02: `offerings` ships as the catalog identity; `specialties`
-- are per-org (organization_id NOT NULL). Two live constraints to carry into
-- the design, from leo-port-map.md:
--   • §8.11(b) — leo's random-nanoid slugs back live public URLs
--     ({speciality.slug}/{template.slug}); preserving them verbatim is an
--     open decision with real external-link consequences.
--   • §8.12 — specialists.organization_id NOT NULL means a specialist at two
--     clinics gets two profiles; cross-org double-booking is unguarded.
--
-- Do not draft DDL here. See apps/docs/features/scheduling/,
-- apps/docs/architecture/appointments-substrate.md (⚠️ four known defects —
-- leo-port-map.md §0.3), and leo-port-map.md §8.
-- ---------------------------------------------------------------------------

-- ---------------------------------------------------------------------------
-- 6a. Patient profiles (portable, patient-owned identity)
-- `patient_persons` does not exist. The table is `patient_profiles`, keyed by
-- UUID, linked to a human via `human_id` (UNIQUE, nullable, ON DELETE SET NULL
-- — a profile can outlive its login). It carries NO organization_id: this is
-- the documented RLS exception, resolved via current_human_patient_profile_ids().
-- ---------------------------------------------------------------------------
-- Verified against restartix-leo-api/src/api/patient/content-types/patient/
-- schema.json: the legacy `patients` row carries ONLY name, phone, password,
-- consumer_id and a 1:1 user relation. It has NO demographic columns —
-- see the pivot note below.
INSERT INTO patient_profiles (id, human_id, name, phone, created_at, updated_at)
SELECT pm.new_id,
       um.new_id,
       p.name,
       p.phone,        -- PLAINTEXT. See the note below.
       p.created_at, p.updated_at
FROM legacy.patients p
JOIN staging.id_map pm ON pm.legacy_table = 'patients' AND pm.legacy_id = p.id
LEFT JOIN legacy.patients_user_links pl ON pl.patient_id = p.id
LEFT JOIN staging.id_map um ON um.legacy_table = 'up_users' AND um.legacy_id = pl.user_id;

-- ✅ `phone` is PLAINTEXT (class pii_basic), not `phone_encrypted`. There is
--    no such column and there never was in the shipped schema. Column-level
--    encryption is reserved for `auth_secret` and `pii_regulated` only —
--    phone search (caller-ID, partial / last-N-digit lookup) is a required
--    clinic feature that random-nonce AES-GCM makes impossible. See
--    decisions.md → "Why most PII is plaintext (and what isn't)". The
--    migration explicitly does NOT encrypt phone numbers.
--
-- ⚠️ `name` is NOT NULL on patient_profiles and required in legacy — but
--    legacy `name` is on the PATIENT row while a legacy user without a
--    patient row has only `username` / `displayName`. Patients with no
--    legacy patient row need a decision (Step 2's open username question).

-- ---------------------------------------------------------------------------
-- 6a-bis. Demographics: an EAV pivot, not a column copy
--
-- The platform stores demographics as TYPED columns on patient_profiles:
-- date_of_birth DATE, sex TEXT, occupation TEXT, residence TEXT, blood_type,
-- allergies TEXT[], chronic_conditions TEXT[], emergency_contact_*,
-- insurance_entries JSONB.
--
-- Legacy stores them as a generic key/value store — verified in leo-api:
--   • `meta_fields` (entity enum patient|specialist|appointment|franchise,
--     franchise relation, key UNIQUE, label, type enum, options, is_private)
--   • `meta_values` (value RICHTEXT, key, relations to user / meta_field /
--     appointment)
-- Note the value is attached to the USER, not the patient row, and its type
-- is richtext — so every value is free-form HTML-ish string data.
--
-- The franchise `patient_meta_birthdate` / `_residence` / `_occupation` /
-- `_sex` / `_cnp` columns are LABEL OVERRIDES for those meta_fields, which is
-- what step 1b above is really about — they are captions, not values.
--
-- ⚠️ This is a pivot with lossy input, and the hardest part of the patient
--    migration. Every target column has a CHECK or a type that legacy
--    richtext will violate:
--      · `sex` CHECK ∈ ('Male','Female','Other','Prefer not to say') — legacy
--        is whatever the clinic typed, in Romanian
--      · `blood_type` CHECK ∈ the eight ABO/Rh values
--      · `date_of_birth` DATE — legacy is a richtext string in unknown format
--    Normalisation rules, and who signs off that a normalised answer is still
--    the patient's answer, are the same class of question as
--    leo-port-map.md §8.11(c)/(d). Not designed here.
--
-- ⚠️ CNP. `patient_meta_cnp` exists in legacy, so the ~20k dataset contains
--    Romanian national IDs sitting in a generic richtext value store. That is
--    exactly the shape the 2026-08-02 decision rules out: CNP is
--    pii_regulated, opt-in per template, encrypted BYTEA on patient_profiles,
--    and NEVER in a generic value store. The column does not exist yet (F3),
--    so CNP has no legal destination today. Migrating it before that column
--    lands would mean re-creating the rejected design.
-- ---------------------------------------------------------------------------

-- ---------------------------------------------------------------------------
-- 6b. Caregiver links
-- `patient_person_managers` does not exist. The table is `patient_caregivers`
-- (patient_profile_id, caregiver_human_id, relationship) with a composite PK
-- and a CHECK on relationship:
-- 'self' | 'parent' | 'child' | 'spouse' | 'sibling' | 'caregiver' | 'other'.
-- Like patient_profiles it has no organization_id — org access is gated on
-- the patient's own `patients` row, never on a caregiver-of link.
-- ---------------------------------------------------------------------------
INSERT INTO patient_caregivers (patient_profile_id, caregiver_human_id, relationship)
SELECT pp.id, pp.human_id, 'self'
FROM patient_profiles pp
WHERE pp.human_id IS NOT NULL;

-- ---------------------------------------------------------------------------
-- 6c. Patients (per-org clinical link)
-- The org↔profile join. Its existence is what grants portal access at an org.
-- Patients are NEVER organization_memberships — see decisions.md → "Why
-- patients are not memberships".
-- ---------------------------------------------------------------------------
INSERT INTO patients (organization_id, patient_profile_id, consumer_id,
    created_at, updated_at)
SELECT fm.new_id,
       pm.new_id,
       p.consumer_id::TEXT,
       p.created_at, p.updated_at
FROM legacy.patients p
JOIN staging.id_map pm ON pm.legacy_table = 'patients' AND pm.legacy_id = p.id
JOIN legacy.patients_user_links pl ON pl.patient_id = p.id
JOIN legacy.up_users_franchises_links fl ON fl.user_id = pl.user_id
JOIN staging.id_map fm ON fm.legacy_table = 'franchises' AND fm.legacy_id = fl.franchise_id;

-- NOTE: creating this row is what discloses the portable profile to the
--       clinic. There is no separate profile-sharing consent to backfill
--       (P8, retired 2026-08-20) — a migrated patient's record is readable
--       by the clinic they were migrated into, and by no other.
-- NOTE: password is NOT migrated (the auth provider owns credentials).
-- NOTE: `patients_profile_org_active_uniq` is partial on deleted_at IS NULL —
--       at most one ACTIVE row per (profile, org). Legacy duplicates across
--       franchises are fine; duplicates within one franchise are not.
-- NOTE: `deleted_at` — soft delete only. Patient records are never
--       hard-deleted; GDPR erasure is anonymisation on patient_profiles.

-- Downstream tables (forms, appointments, documents, custom field values)
-- have no targets yet — see the ⛔ markers above.

COMMIT;

Step 4: Post-migration encryption — mostly does not apply

The original draft of this step assumed a sweeping "encrypt the PHI that was plaintext in legacy" pass. That is the opposite of the platform's rule. Column-level encryption is reserved for exactly two classes:

• auth_secret — credentials, API keys, signing secrets
• pii_regulated — national IDs (CNP, SSN, passport)

Everything else — names, emails, phones, addresses, allergies, diagnoses — is plaintext plus layered defence (RLS + audit + at-rest disk encryption + encrypted backups + restricted DB access), and this is mechanically enforced by cmd/check-classification. See decisions.md → Why most PII is plaintext.

So the real post-migration encryption surface is small. Encrypted BYTEA columns that exist today:

Column	Migration	Class
organization_settings.tax_id_encrypted	000003	pii_regulated
platform_service_providers.credentials_encrypted	000015	auth_secret
outbound_webhook_subscriptions.signing_secret_encrypted / ..._previous_encrypted	000016	auth_secret
organization_integrations.credentials_encrypted / inbound_signing_secret_encrypted	000017	auth_secret

There is no CNP column on patient_profiles today. It was settled 2026-08-02 as opt-in per form template, pii_regulated, encrypted BYTEA on patient_profiles, never in a generic value store — but the column ships with F3, not before. Until then there is nothing to encrypt on the patient side.

// cmd/migrate-encrypt/main.go — illustrative shape only.
// Wire format is [1-byte version][12-byte nonce][ciphertext+tag] in BYTEA,
// via internal/core/crypto (crypto.Encrypt). Keys come from AWS KMS —
// never from code, config, or environment variables.

func main() {
    // Re-encrypt legacy org integration credentials under the platform keyring.
    // Decrypt with the legacy key, re-encrypt with crypto.Encrypt.
    rows, _ := db.Query(ctx, `SELECT organization_id, integration_service_id
                              FROM organization_integrations`)
    for rows.Next() {
        var orgID, svcID uuid.UUID
        rows.Scan(&orgID, &svcID)
        // ... decrypt-with-old-key → crypto.Encrypt → UPDATE ...
    }

    // DO NOT add a phone-encryption pass here. patient_profiles.phone is
    // pii_basic/plaintext by design; encrypting it breaks caller-ID and
    // partial-number search and fails cmd/check-classification.
}

Step 5: Auth-provider account migration

The original draft wrote users.clerk_user_id. The actual column is humans.provider_subject_id — deliberately provider-agnostic so swapping the auth provider never requires a rename. It holds the JWT sub claim (Clerk's user_xxx today) and is UNIQUE. The sibling humans.provider_org_id is the provider-side organisation identifier, NULL for shared-mode tenants.

// cmd/migrate-provider-accounts/main.go — illustrative shape only.

func main() {
    rows, _ := db.Query(ctx, `SELECT principal_id, email
                              FROM humans
                              WHERE provider_subject_id IS NULL`)
    for rows.Next() {
        var principalID uuid.UUID
        var email string
        rows.Scan(&principalID, &email)

        u, err := clerkClient.Users.Create(ctx, &clerk.CreateUserParams{
            EmailAddress: []string{email},
        })
        if err != nil {
            // Log the principal ID, never the email — PII must not be logged.
            slog.Error("provider account creation failed",
                "principal_id", principalID, "error", err)
            continue
        }

        db.Exec(ctx, `UPDATE humans SET provider_subject_id = $1
                      WHERE principal_id = $2`, u.ID, principalID)
    }
}

A just-in-time path already exists and is live

A bulk pre-creation pass is not the only option, and may not be the chosen one. The shipped legacy → handoff bridge (services/api/internal/core/domain/handoff/, routes POST /v1/public/handoff, /handoff/session, /handoff/reentry, POST /v1/me/handoff/claim) already provisions principals + humans on first sight and then the patient_profiles + patients rows on claim, from a signed handoff token — with mandatory consents collected in the flow. It has been serving real patients in production since 2026-06-05 and is explicitly the path the ~20k migration is expected to use.

Which patients get bulk-provisioned versus self-migrated through the bridge is a Phase 3 decision that is not made yet. Note the hard constraint from platform-completion.md: counsel sign-off on the v2 legal text gates the migration, because once a patient is migrated the consent shape is permanent.

Migration Verification Checklist

-- Record counts — target tables that actually exist today.
SELECT 'organizations'           AS entity, COUNT(*) FROM organizations
UNION ALL SELECT 'principals',              COUNT(*) FROM principals
UNION ALL SELECT 'humans',                  COUNT(*) FROM humans
UNION ALL SELECT 'organization_memberships',COUNT(*) FROM organization_memberships
UNION ALL SELECT 'patient_profiles',        COUNT(*) FROM patient_profiles
UNION ALL SELECT 'patient_caregivers',      COUNT(*) FROM patient_caregivers
UNION ALL SELECT 'patients',                COUNT(*) FROM patients;
-- Add specialists / specialties / offerings / calendars / appointments /
-- form_templates / forms / documents when F1–F6 land. None exist today.

-- Every legacy row landed exactly once.
SELECT 'unmapped legacy users' AS check, COUNT(*)
FROM legacy.up_users u
WHERE NOT EXISTS (SELECT 1 FROM staging.id_map m
                  WHERE m.legacy_table = 'up_users' AND m.legacy_id = u.id);

-- FK integrity.
SELECT 'patients with missing org' AS check, COUNT(*)
FROM patients p
WHERE NOT EXISTS (SELECT 1 FROM organizations o WHERE o.id = p.organization_id)
UNION ALL
SELECT 'patients with missing profile', COUNT(*)
FROM patients p
WHERE NOT EXISTS (SELECT 1 FROM patient_profiles pp WHERE pp.id = p.patient_profile_id)
UNION ALL
SELECT 'humans with no principal', COUNT(*)
FROM humans h
WHERE NOT EXISTS (SELECT 1 FROM principals pr WHERE pr.id = h.principal_id);

-- Architectural invariants (these catch a migration that "worked" but
-- reproduced the legacy shape).
--
-- ⚠️ The first check is NOT a database constraint — nothing in the schema
--    forbids one human holding both a membership and a patient_profile. It
--    matters because the shipped hand-off bridge treats "has ANY
--    organization_memberships row" as the definition of staff
--    (handoff/service.go rejectStaffEmail → ErrStaffIdentity, a generic 401).
--    A legacy patient who also lands a membership row is therefore locked out
--    of the portal entry path entirely. A non-zero count here almost always
--    means the role mapping in step 3 leaked 'authenticated' users into
--    memberships. Genuine staff-who-are-also-patients, if the business allows
--    them, are a real product decision — not something to resolve by
--    loosening this query.
SELECT 'patients wrongly given a membership' AS check, COUNT(*)
FROM organization_memberships om
JOIN patient_profiles pp ON pp.human_id = om.principal_id
UNION ALL
SELECT 'orgs without exactly one owner', COUNT(*)
FROM organizations o
WHERE (SELECT COUNT(*) FROM organization_memberships om
       WHERE om.organization_id = o.id AND om.is_owner) <> 1
UNION ALL
SELECT 'non-human principals in platform_memberships', COUNT(*)
FROM platform_memberships pm
JOIN principals pr ON pr.id = pm.principal_id
WHERE pr.principal_type <> 'human';

-- RLS smoke test. Session vars are app.current_principal_id /
-- app.current_actor_type / app.current_org_id. There is NO app.current_role
-- and NO current_app_user_id() — authorization is per-org permission codes,
-- evaluated by current_app_has_permission(resource, action).
-- Run on an RLS-subject connection (AppPool), not the admin pool.
SELECT set_config('app.current_principal_id', '<principal-uuid>', TRUE);
SELECT set_config('app.current_actor_type',   'human',            TRUE);
SELECT set_config('app.current_org_id',       '<org-uuid>',       TRUE);

SELECT current_app_principal_id(), current_app_org_id();
SELECT current_app_has_permission('organizations', 'view_directory');

-- A staff principal sees only their org's patients.
SELECT COUNT(*) FROM patients;

-- A patient principal resolves through the patient-side helpers
-- (patient_profiles has no organization_id — the documented RLS exception).
SELECT current_human_patient_profile_ids();
SELECT current_human_is_patient_at('<org-uuid>');

Use set_config(..., TRUE), not SET

Runtime traffic goes through pgbouncer in transaction pooling mode (P44). Session-scoped SET leaks across pooled connections. The transaction-scoped set_config(name, value, TRUE) form is the only correct one — which is also why a migration run that verifies RLS must be careful about which pool it is on.

Parallel Running Strategy

Assumption to revisit

The phases below were written for a same-database rebuild, where the Go API could shadow-read the legacy tables directly. That premise no longer holds: the platform runs its own schema, has been live in production since 2026-06-05, and the legacy systems are separate services. Read these as the shape of a staged cutover — shadow, dual, primary, decommission — which is still the right shape. The concrete mechanism (FDW bridge, replication, or dual-write at the application layer) is a Phase 3 decision.

Phase A: Read-only shadow

Run the new API alongside the legacy API in read-only mode to verify correctness before it takes any traffic.

Client → Legacy API (primary, read/write)
         Platform API (shadow, read-only)

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

Phase B: Dual-write

Legacy still handles writes; the platform handles reads and verifies writes.

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

Phase C: Platform primary

The platform handles everything. Legacy is kept on standby for emergency rollback.

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

Phase D: Legacy decommission

Client → Platform API

Rollback Procedure

If critical issues are found after the platform goes primary:

1. Switch the load balancer back to the legacy API
2. Investigate and fix
3. Re-attempt cutover

The original step 2 was wrong and the difference matters

The earlier draft claimed "any writes the Go API made to the database are compatible (same schema)." They are not. The platform and legacy schemas are structurally different — different PK types, different identity model, RLS everywhere. Rolling back the traffic switch does not roll back data.

Any write the platform accepted during the primary window has no legacy representation and is lost on rollback unless it is either replayed into legacy or the cutover is designed so the platform is the sole writer from a hard, agreed instant. The rollback window is therefore bounded by "how much new data can we afford to reconcile by hand," not by "how fast can we flip the load balancer." Designing that boundary is Phase 3 work.

Implementation Order

Historical — this list is done

The 20-step "when starting the Go project, build in this order" sequence that lived here described bootstrapping the API service from scratch. That work shipped: the skeleton, auth middleware, org/RLS middleware, /me, org CRUD, telemetry, webhooks, S3 and the Daily.co integration are all in production, and the foundation gate closed 2026-05-15.

The remaining build order — the clinic-operations stack this document's migration depends on (F1 Specialists, F3 Forms, F4 Scheduling, F5 Appointments, F6 Documents) — is owned by platform-completion.md → Phase 1. That document is authoritative; do not re-derive an order here.

Where to go next

• platform-completion.md — the live plan. Migration is Phase 3, after Phase 1 closes, no target date.
• leo-port-map.md — the survey behind the plan. §0.1 (the Intakes source is available), §0.3 (four defects in appointments-substrate.md), §8.11 (legacy migration: source of truth and fidelity — supersedes this document's sourcing assumptions), §8.13 (decisions that must land before the relevant migration).
• platform-inventory.md — code-verified state of what exists. Note services/migration-tools/ does not exist despite being referenced as a real path by the deployment runbook.
• data-model.md and glossary.md — authoritative for target design; the glossary wins naming disputes.