# Migration and cutover plan

How we get from the running Supabase app to the new system without
losing a row, a dollar, or a month of billing continuity. Written to be
executed stepwise; each step has a verification gate.

## Principles

1. **The old app is never modified** during the rebuild (bugfixes only).
   It runs until a full month has closed cleanly in the new system.
2. **The diff harness comes first** (react.md rule) — precisely: the
   canonical-JSON spec + the OLD-side extractor land in R0 (they need
   nothing but the legacy data); the NEW-side emitter grows
   per-milestone with per-slice green gates (09-build-plan). It is
   the regression oracle and the milestone acceptance mechanism.
3. **Migration scripts are idempotent and rehearsable** — run against a
   fresh snapshot nightly during development, so cutover day is the
   hundredth run, not the first.
4. **Reversible cutover** — until the old app is archived, we can fall
   back to it (its data untouched; at worst the new-system month is
   re-entered).

## Step 0 — the diff harness (R0, before anything else)

A script pair, one per system, each emitting the same canonical JSON:

- Per-month, per-project: entry count, source seconds, cost total,
  billout total (with and without project adjustments).
- Per-invoice: line count, attached-entry ids, totals.
- Per-CC-batch: line count, assigned totals by project, billed totals.
- Entity inventories: operators/clients/projects with entry counts;
  team members with rates.

Old side reads Supabase (read-only service key — staged in a
mode-600 env file with a `REPLACE_WITH_` placeholder per the server
secrets workflow, never argv, never echoed); new side reads the new
Postgres. `diff-harness compare <month>` → green/red with a field-
level diff. Green across ALL history = migration correctness; green
on the parallel-run month = behavioral parity. Harness fields include
`source_rate_usd`/`source_amount_usd` sums so raw import provenance
is diff-checked too, not just computed totals.

**Grouping key is pinned to survive the schema change:** per-project
aggregates group by `project_id` on BOTH sides (the old side's FK
already exists on every resolvable row), with unassigned rows
(project_id NULL) grouped by their text triple old-side and their
`raw_*` triple new-side — identical keys by construction, so the
text-cache removal can't produce phantom group diffs.

**Known accepted diffs** get an explicit allowlist with reasons (e.g.
`converted_duration_seconds` dropped with Toggl). Anything not
allowlisted is a failure. The allowlist also carries one conditional
entry: **if** the transferred-straggler path fires (step 2's judgment
call #22 — expected never), the per-org "Legacy transfers" invoice's
attached-entry set legitimately differs old-vs-new; the entry records
the exact straggler ids so the diff is explained row-for-row, not
waved through.

## Step 1 — snapshot + extract

**Precondition (Day-0, before R0):** the snapshot path is PROVEN,
not assumed — `pg_dump` executed successfully from mosiah against
the session-pooler host (the direct host is IPv6-only; pg_dump major
version must match the server), the auth-scrub script tested, one
scrubbed baseline archived to the NAS, and the Supabase project's
tier + pause/retention policy recorded here. The nightly job then
runs unattended from M0 (09-build-plan).

- `pg_dump` the Supabase database (their dashboard export or
  `pg_dump` against the connection string; both, ideally — belt and
  suspenders since this is the books).
- **Credential scrub before anything lands:** the dump's `auth`
  schema contains password hashes and recovery/confirmation tokens
  (`encrypted_password`, `recovery_token`, etc.). Pattern B's rule is
  "delete the app's password store" — so the landed snapshot NULLs
  every credential-bearing column in `auth.users` (keeping id, email,
  created_at for identity mapping), and the long-term NAS archive is
  taken from the scrubbed copy. The raw unscrubbed dump exists only
  transiently during cutover and is shredded after the scrubbed
  snapshot verifies.
- Land the scrubbed dump in the new Postgres as a `legacy` schema.
  All transform scripts read `legacy.*`, write `public.*` — the
  snapshot stays queryable for forensics forever.

## Step 2 — transform (legacy → party/engagement model)

Order matters; each block is one idempotent script with row-count
assertions:

1. **Parties, then the tenant.** `legacy.operators` → org parties;
   `legacy.clients` → org parties; Person parties come from
   `legacy.team_members` ONLY, deduped by lower(email) across teams
   (rian appears in two teams → one party). Unmatched
   `source_user_email`s create NOTHING — their entries stay Blocked
   with raw_* identity, exactly as today (pinned per the plan
   critique; supersedes any distinct-emails reading). Dedupe rule: same
   lower(name) under the SAME operator merges (already impossible —
   the old unique index prevented it); same name under DIFFERENT
   operators stays separate parties by default (conservative;
   merging parties later is an owner action, splitting is painful).
   Operator names colliding with client names ("PlusROI" the
   operator vs "PlusROI" the client used for admin projects) DO
   merge — one party wearing two hats is exactly the model. Then
   the **tenant**: Bowden Works (its party now exists). Rian/Adi
   user rows from `legacy.profiles` keyed by their id-auth usernames
   (accounts created at Day-0 — 09-build-plan checklist #6 — so
   usernames are fixed before the FIRST rehearsal).
2. **Client→operator edges.** One `direct_client` engagement per
   `legacy.clients` row (client party ↔ its operator party,
   tenant_id now available — this step MUST follow tenant creation;
   engagements are tenant-scoped). These edges carry the hierarchy
   the pickers, per-operator client-name uniqueness, and the
   resolver chain need (02-domain-model §2).
3. **Engagements + terms (seed set).** PRODUCTION seeds ONLY
   legacy-derived rows (the domain rosters are fixture-knowledge for
   dev/test seeds, NOT production inserts): one engagement per
   team_member with TWO terms per the `kind` model (02 §2) — cost
   from `cost_rate_usd`, billout from `billout_rate_usd` (Gary:
   cost 0 + billout 25); rian's official_partnership on BW; the
   BW↔PlusROI partnership engagement (no terms yet — R4's job).
   Only current rate values exist, so terms start `effective_from =
   min(entry date)`; **recalc guard**: historical stamps were written
   under older rate models — re-stamp actions must never treat the
   backdated effective_from as license to recompute history (stamps
   are copied verbatim; the invariant holds). `project_rate_overrides` →
   the new `rate_overrides` table **near-verbatim** (project-wide
   NULL-member rows and pct rows keep their exact resolution-time
   semantics — 02-domain-model §2 explains why these don't flatten
   into compensation_terms in R1).
4. **Projects.** `legacy.projects` → projects with operator_party_id
   (from the operator chain) + client_party_id. Income + adjustment
   columns carried over.
5. **Import batches.** `legacy.clockify_imports` → import_batches
   (source, filename, name, notes, counts, **imported_by → the
   uploader's user/party, imported_at**). The uploader mapping is
   load-bearing: importer-scoped visibility ("worker sees own
   imports") is uncomputable for historical batches without it, and
   batch lists order by imported_at. Pending imports: drop (staging
   data; anything unresolved at cutover gets re-uploaded).
6. **Time entries.** The big one. Field mapping:
   - `id` → `id` — **entry ids carry through unchanged.** The diff
     harness compares attached-entry id sets across systems; stable
     ids are what makes that comparison (and the comment re-keying,
     and any future forensics against the legacy schema) possible.
   - `team_member_id` → worker_party_id (via the member→person party
     map); entries whose email matched no member keep
     worker_party_id NULL + original source fields (same "Blocked"
     semantics as today).
   - `project_id` → project_id (rows with project_id NULL stay
     NULL = Blocked).
   - `operator`/`client`/`project` text → **raw_operator /
     raw_client / raw_project, carried on every row** (02-domain-model
     §3: the triple is an immutable import payload, not a cache).
     This is what keeps Blocked-row semantics identical to today —
     the text IS those rows' only identity; /entries displays it and
     the resolver resolves from it. For resolved rows it's provenance
     only; display and grouping always use FKs.
   - via_engagement_id: the worker's engagement with BW.
   - cost/billout stamps: **copied verbatim, never recomputed** (they
     are locked historical facts; recomputation is exactly what the
     invariant forbids). `source_rate_usd`/`source_amount_usd`:
     **carried** (raw import provenance).
   - **Currency stamping (committed default, NOT a rehearsal gate —
     07 #21 amended):** all work-app amounts stamp CAD (Adi bills
     CAD; the books are CAD; the `_usd` suffix was a misnomer).
     Rehearsals run on this default from day one; every
     reconciliation report shows per-currency row counts until rian's
     final yes/no confirmation lands (Day-0 checklist #7). Same stamping applies to
     compensation_terms/rate_overrides seeded in step 3, invoices in
     step 7 (single-currency check runs against their entries), and
     CC batches in step 8. The diff harness compares totals
     **per currency** (a USD total and a CAD total never merge).
   - `source` values carry as-is — the new CHECK admits the
     historical `toggl`/`backfill` values; only the *importer* is
     restricted to writing clockify/manual. No provenance rewriting.
   - **The 2025-12-31 baseline entries are load-bearing** (the 20
     manual rows described "Transfer baseline from Toggl…" carry the
     opening balances of every prepaid support block — the "hourly"
     quick filter's lifetime totals depend on them). They migrate as
     ordinary entries; anything that looks like "Toggl residue
     cleanup" must explicitly exclude them. See 02-domain-model §2
     (prepaid_block).
   - `transferred_at`-only rows (locked but not on any invoice): see
     judgment call below.
   - Dropped fields recorded in the allowlist: rate_proportion,
     converted_user, converted_duration_seconds (note: a handful of
     legacy rows have converted values with NULL duration_seconds —
     they remain queryable in the `legacy` schema; nothing in the new
     app reads converted values). The text triple is NOT dropped —
     see raw_* above.
7. **Invoices.** `legacy.invoices` → invoices, column-for-column
   (R1 keeps the current shape — 02-domain-model §3): name → name
   (unique per tenant), status open/sent/paid → same, invoice_date →
   same (the accounting date that buckets months), sent_at / paid_at /
   created_by / notes / **manual_total_usd** → carried verbatim.
   New columns seeded by the PINNED algorithm (critique-corrected —
   a naive operator→from / client→to mapping would invert the money
   direction on the largest invoices): **from_party = Bowden Works
   (the tenant) for every legacy invoice.** to_party: if the
   invoice's operator scope ≠ Bowden Works, to_party = that operator
   party (a PlusROI-scoped invoice is BW billing PlusROI — the
   client_id chip there is display scope, NOT the counterparty);
   else to_party = the client party. Fallbacks in order: owner-set
   scope chips → operator set derived from attached entries (unique
   operator → use it; mixed → NULL) → NULL for owner review; all
   NULL cases + the "Legacy transfers" sentinels enumerated in the
   run report. Attached entries become `time`-kind invoice_lines
   (snapshot display names as of migration — best available
   approximation of attach-time state), carrying
   `invoice_applied_at`/`invoice_applied_by` → attached_at /
   attached_by. Entry lock carries via invoice_line_id.
8. **CC expenses.** Batches, lines, auto-rules, highlight keywords —
   near-verbatim; lines keep project links. (No billed-state column
   exists today — bill-through is a stateless paste-block export; the
   new `invoice_line_id` on expense lines starts NULL everywhere and
   only future invoicing sets it.)
9. **Comments.** The current comments table is a global board with no
   row anchors — carries as-is (author, body, resolved state,
   timestamps), only re-keying author ids to the new users.
10. **Metadata columns carry by default.** Any nullable
   free-text/flag column not explicitly dropped or remapped carries
   to its owner's new home verbatim: projects.notes,
   team_members.notes → the person party / engagement notes,
   team_members.is_active → engagement ended_on (inactive → ended at
   migration date), team_members.consolidate_as → the import-mapping
   config on the worker's engagement (02 §7), project_rate_overrides
   .notes → rate_overrides.notes, teams.name → recorded in the
   legacy schema only (teams dissolve). The reconciliation report
   enumerates every column with its disposition — "not mentioned
   anywhere" is a failure, not a default.
11. **Users + view-as state.** profiles.is_super_admin → users;
    must_change_password dies (id-auth owns credentials).

Every script logs (source count, written count, skipped count with
reasons) and the run aborts on unexplained deltas.

**Transferred-but-not-invoiced rows (judgment call #22):** after the
2026-05-25 invoice migration's backfill, every transferred row should
already sit on a per-org **"Legacy transfers"** invoice — the expected
count of `transferred_at IS NOT NULL AND invoice_id IS NULL` rows is
**zero**. The script checks that count and reports; if it's nonzero
(something used the orphaned bulkMarkTransferred action since), the
rows attach to the *existing* per-org "Legacy transfers" invoice
(same one the old backfill created — per ORG, not per operator; no
new sentinel invoices, no name-uniqueness collision), creating it
only if the org somehow lacks one (create-if-absent with an even
louder warning — makes the script total), with a WARNING in the run
report for owner review. Never abort for this; never leave a second
lock mechanism. This supersedes any per-operator-sentinel or
abort-on-sight reading elsewhere in the package.

## Step 3 — validation gates (all must pass before parallel run)

- Diff harness green on ALL historical months.
- Row-count reconciliation report (every legacy table accounted for:
  migrated / dropped-allowlisted / error).
- Spot-audit pack for rian: 10 random invoices + the 3 biggest months
  rendered side-by-side old vs new.
- The ported test suite green (current 116 + RPC-semantics ports +
  authz truth tables).
- **External preconditions verified, not assumed:** (a) "Sign in
  with BW" has been live on the temp domain since M1 (Pattern B
  shipped 2026-07-07 — judgment #31; this precondition is satisfied
  months early by design); (b) id-auth accounts exist since Day-0;
  (c) **Adi is briefed**: the month is REPLAY-based (07 #23 amended)
  — he uploads to the old app only and owes zero extra data entry;
  his role is one login test + real usage for UX/permission
  feedback. Schedule the month against his availability anyway (no
  vacation weeks) since his feedback is half the point.

## Step 4 — parallel run (one full month)

- New app live at a temporary domain (id-auth gated,
  `account_required:true`), seeded with the migrated data.
- **Replay month (07 #23 amended — no dual human entry):** Adi
  uploads to the old app only; a same-day replay step (rian or a
  script driving the new import API with the IDENTICAL file) mirrors
  each artifact into the new system. CC statement pastes: same paste
  text both sides at month-end (minutes). Invoices: created new-side
  from a checklist of what was created old-side. Bit-same inputs, no
  memory dependence — and the replay step itself has been rehearsed
  weekly since M4 (shadow uploads, 09-build-plan).
- **Mid-month divergence = fix + replay the recorded inputs; the
  calendar month does NOT restart.** Only a vacuous-comparison gap
  (a surface that never got both-sides input) extends the run.
  Cutover happens at a month boundary — days 1–5 of the following
  month, AFTER both systems complete the month's invoicing (the
  invoicing cycle IS the month close).
- Diff harness compares the month continuously; any divergence is a
  bug fixed in the new system, then re-verified.
- rian + Adi use the new app for real work during this month (the
  old one remains source of truth); UX feedback lands as issues.
- **The parallel month in the new system is a rehearsal, not a
  ledger** (judgment call #23): at cutover, the final migration run
  rebuilds `public.*` from the final legacy snapshot — the old app
  was source of truth all month, so the replayed native copy
  of the month is *discarded and replaced* by the migrated version.
  Anything created ONLY in the new system during the rehearsal
  (comments, UI preferences) is enumerated by the cutover script and
  listed for explicit acceptance before the wipe. This is what makes
  the final run deterministic — no merge, no dedupe heuristics.

## Step 5 — cutover

1. Freeze old app — ENFORCED, not announced: flip the old app's
   read-only middleware flag (the same one step 5.4's parking uses)
   BEFORE the final snapshot. A stray write between announcement and
   snapshot would silently break the deterministic-rebuild property.
2. Final snapshot → final migration run (idempotent; minutes) —
   rebuilds from legacy per step 4's rehearsal rule.
3. Diff harness green on the final state.
4. id-auth site config: new app takes over `work.bowden.works`
   (Pattern B, account_required); old app parked at
   `work-legacy.bowden.works`, **read-only** (a middleware flag), gated
   owner-only. Note the parking quirk: the legacy app's `/login` and
   `/auth/*` routes collide with the id-auth snippet's reserved-path
   exclusions, so those paths reach the legacy app ungated — harmless
   (they're Supabase-auth'd themselves and the app is read-only), but
   documented so nobody mistakes it for a gate failure.
5. First "Sign in with BW" login verified for both rian and Adi on
   the production hostname (accounts exist since Day-0; login has
   been live on the temp domain since M1).
6. New system is source of truth. Old app + Supabase project retained
   read-only for one full quarter, then: final pg_dump archived to
   the NAS backup set, Supabase project deleted, legacy schema kept
   in the new database indefinitely (it's small).

## Rollback

Any failure before step 5.6: point `work.bowden.works` back at the old
app (one id-auth/Caddy config change), which was never modified. Data
entered only in the new system during a failed parallel month is
re-importable (entries came from CSVs we still have; invoices are
re-creatable from the same entries).

**Post-cutover rollback horizon (critique addition):** the likeliest
real failure is a subtle stamp/lock bug found WEEKS after cutover,
once real invoices exist only in the new system. Therefore: (1) the
old app stays warm-standby (un-parkable, not merely archived) until
one full month closes clean in the new system; (2) the un-park
procedure is written as exact commands (read-only flag off; id-auth
site config repoint of work.bowden.works) in the cutover runbook;
(3) the new app ships a standing "re-entry pack" export — post-cutover
entries as Clockify-format CSVs + invoice list + CC paste text — so
new-system data is mechanically re-enterable into the old app in a
money-corruption emergency.

## Backup regime (must exist BEFORE cutover — this becomes the books)

- Postgres sidecar: nightly `pg_dump` into the server's existing
  backup rotation + NAS sync (same pattern as other stateful services
  on mosiah).
- Restore drill executed once before cutover (restore into a scratch
  container, diff harness green against production).