Data migrations (pnpm migrate)

pnpm migrate replaces "run this mongosh script by hand on each environment" for the MongoDB changes the code depends on — indexes first. A migration is a file packages/data/migrations/NNNN-kebab-name.mjs with an up and a down; umzug 3.8.3 runs the pending ones in order and records each one in adb-system._migrations with the sha256 of its file, so every environment can say which migrations it has. up and down print a dry-run plan unless --apply; one runner at a time holds a lock; assert exits 1 while anything is pending, and pnpm infra:setup <slug> runs it before applying an environment. The first four migrations apply the indexes the money and sync guards rely on (BUGS.md FL-EA51-1); on staging they were applied and verified on 2026-10-09. Production is an environment of its own since 2026-10-10 (engineer decision): PROD_MONGO_URI only, --i-know-this-is-prod plus the host typed back on a terminal, cutover-only migrations behind --cutover, read-only preflights, and an --apply that must repeat the hash of the plan its dry-run printed — see Production.

At a glance

The migrations

The ensure* functions are called as they are: the migration binds coll() to its client and records, for every index the function asks for, whether this run created it or found it already there.

Production says whether up may run on production while the JVM still writes (online-safe) or only at the cutover with the JVM accounting writes quiesced (cutover-only; engineer classification, 2026-10-10). 0002 because its accounting_sequence unique build fails while the legacy organizationId year counters exist — it follows the twin-counter migration; 0004 because, with the index, two JVM payments of one organisation created in the same millisecond share a key and the JVM's saveIfNotExists answers the first one for the second (apps/ui/docs/parity/gaps.md FL-EA51). 0007, merged after that classification, is online-safe because nothing on the JVM side writes accounting_sequence_voids (its only writer was packages/data/accounting/sequence/mutations.mjs, and nothing writes it since the JE number is assigned after the posting committed): a unique build on a new, empty collection — to be confirmed by the engineer. 0008 and 0009, brought in by the merge of main (2026-10-11), are online-safe like 0005 and 0006: non-unique read index builds, no data precondition, build load only (off-peak) — to be confirmed by the engineer too. 0010, brought in by the merge of main on 2026-10-12, is cutover-only: a DATA migration that rewrites labels and person targets on payments, ledger lines and invoices, which the JVM accounting services write too, and whose down is irreversible (the Atlas snapshot taken before --apply) — to be confirmed by the engineer. The ten are declared in support/prod-safety.mjs, not in their files: 0001–0009 are applied on staging (read-only status, 2026-10-11), 0010 may be applied anywhere from its merged file, and editing an applied file moves its checksum (a test pins the ten files). The next migration, 0011 and on, exports prodSafety in its own file (export const prodSafety = "online-safe" | "cutover-only", optionally prodSafetyReason); one without a valid declaration fails packages/data/test/migrations-files.test.mjs, and production refuses it.

Everyday use

pnpm migrate status staging                    # applied / pending / changed / unknown, and the lock
pnpm migrate up staging                        # the plan (dry-run): what --apply would build, keep or refuse
pnpm migrate up staging --apply --to 0003      # apply up to and including 0003
pnpm migrate assert staging                    # exit 1 unless in sync — the pre-deploy gate
pnpm migrate down local                        # the plan of reverting the last applied migration
pnpm migrate down local --apply --to 0000      # revert every applied migration (0 or 0000 = all)
pnpm migrate up staging --only 0003,0005       # exactly these pending migrations, oldest first

From a worktree, the staging credentials stay in the main checkout: pnpm migrate status staging --staging-env <main checkout>/apps/ui/e2e/.auth/staging.env (not --env-file: node looks at that flag anywhere on its command line). --to takes NNNN, the file name, or the file name without .mjs; up --to runs up to and including it, down --to reverts down to and including it. --to and --step are exclusive; down alone reverts the last applied migration.

How it behaves

• The plan is what --apply runs. The dry-run asks umzug for the same selection over a storage that records nothing, and runs the migrations through a read-only facade: reads pass, createIndex and dropIndex are recorded instead of sent, anything else throws. It predicts the server's refusals measured on mongod 7.0.37 and 8.2.11: same index name with another spec (86), same index under another name (85), and a unique build over keys several documents already share (11000, found by a read-only $group over the index's partial filter, a missing field counting as null).
• A pre-existing index is never dropped. An index already there with the same spec (built by an Atlas script, by local-sync-backfill.mjs, by hand) is recorded created: false; down keeps it. down also refuses to drop an index whose spec changed since the migration created it, and skips one already gone.
• A failed migration leaves nothing behind and is not recorded. When an ensure* fails half-way (its builds run in parallel), the runner waits for every build, drops back the ones this run created, and stops; the migrations before it stay applied, the ones after it do not run.
• One runner at a time. --apply takes the lock with one atomic upsert on { _id, expired or no expiry } (a held lock makes the insert collide on _id); a second runner is refused with the holder's name and expiry. The clock is the server's (hello.localTime) — mongod refuses $expr in an upsert predicate. A run that dies holds the lock until its expiry; a run that loses its lock stops before the next migration.
• Never edit an applied migration. up and down refuse while an applied file's checksum moved; add a new migration instead. To add an index to an ensure* set: change the declaration, then add a migration that calls the same ensure* — the indexes already there are recorded as pre-existing, so its down drops only the new one.
• --only picks exactly the migrations named (comma-separated NNNN or names), each pending for up (oldest first) or applied for down (newest first); it excludes --to and --step. A migration applied out of order leaves the older pending ones marked (OUT OF ORDER: older than an applied migration) in status — expected on production after the online-safe step, where 0002 and 0004 wait for the cutover.
• Irreversible means a down that throws. down = irreversible("why, and what to do instead") (support/migration.mjs). The loader refuses a module without down: umzug would otherwise mark it reverted without doing anything.

When status says changed or unknown

• changed — the file of an applied migration no longer matches the checksum recorded when it was applied. If the edit was a mistake, restore the file. If it was deliberate and the database is already in the state the new file describes, a person (not this tool) updates the checksum of that document in adb-system._migrations, after reading the diff.
• unknown — the database has a migration this checkout does not have: the checkout is behind (pull) or the migration was applied from an unmerged branch.

Both make up, down and assert refuse until resolved.

The pre-deploy gate

pnpm infra:setup <slug> runs pnpm migrate assert <slug> --gate before it applies anything (infra-cf/scripts/data-migrations-gate.sh). Not in sync → the setup is refused with the commands that fix it; --check only reports; --skip-migrations-gate applies anyway. It never fails a run for lack of a URI: --gate skips with a note when no MongoDB is configured for the slug (pr-*), and the gate skips when the pnpm workspace is not installed. production is always skipped, before anything is read — even with PROD_MONGO_URI exported in the shell, the gate never connects to production; check it by hand with pnpm migrate assert production --i-know-this-is-prod.

Production

Run from the repository root, in a terminal, with the connection string exported in that shell only: export PROD_MONGO_URI='…' (never in a file, a ticket or a command argument). Every production command prints the cluster as scheme + hosts, then asks you to type the host back; stdin must be a terminal (a pipe or CI is refused). The tool connects only after that. The order (engineer decision, 2026-10-10):

(a) Read-only: the state, the dry-runs and the preflights. Nothing is written; each dry-run ends with its plan hash (or says why it has none) and the exact command that applies it.

pnpm migrate status production --i-know-this-is-prod
pnpm migrate up production --only 0001,0003,0005,0006,0007,0008,0009,0011 --i-know-this-is-prod
pnpm migrate up production --only 0002 --cutover --i-know-this-is-prod   # its preflight refuses until step 2 ran
pnpm migrate up production --only 0004 --cutover --i-know-this-is-prod
pnpm migrate up production --only 0010 --cutover --i-know-this-is-prod   # matched / to update / unresolved / skipped per collection
pnpm migrate up production --only 0012 --cutover --i-know-this-is-prod   # live lines carrying account.parent.balances
pnpm migrate up production --only 0013 --cutover --i-know-this-is-prod   # « [null] » events / reversal lines
MONGO_URI="$PROD_MONGO_URI" node services/accounting/scripts/migrate-accounting-sequence-org-spelling.mjs --i-know-this-is-prod

(b) Step 1, the online-safe apply. Review the dry-run, then apply exactly its hash.

pnpm migrate up production --only 0001,0003,0005,0006,0007,0008,0009,0011 --i-know-this-is-prod
pnpm migrate up production --only 0001,0003,0005,0006,0007,0008,0009,0011 --i-know-this-is-prod --apply --plan <hash>
pnpm migrate status production --i-know-this-is-prod     # 0002 and 0004 pending, OUT OF ORDER: expected; 0010, 0012 and 0013 pending

(c) Steps 2-5, the cutover, with the JVM accounting writes quiesced:

MONGO_URI="$PROD_MONGO_URI" node services/accounting/scripts/migrate-accounting-sequence-org-spelling.mjs --i-know-this-is-prod
MONGO_URI="$PROD_MONGO_URI" node services/accounting/scripts/migrate-accounting-sequence-org-spelling.mjs --i-know-this-is-prod --apply
MONGO_URI="$PROD_MONGO_URI" node services/accounting/scripts/migrate-accounting-sequence-org-spelling.mjs --i-know-this-is-prod   # renamed: 0   merged: 0
pnpm migrate up production --only 0002 --cutover --i-know-this-is-prod                     # every starred count 0
pnpm migrate up production --only 0002 --cutover --i-know-this-is-prod --apply --plan <hash>
pnpm migrate up production --only 0004 --cutover --i-know-this-is-prod
pnpm migrate up production --only 0004 --cutover --i-know-this-is-prod --apply --plan <hash>
pnpm migrate up production --only 0010 --cutover --i-know-this-is-prod
pnpm migrate up production --only 0010 --cutover --i-know-this-is-prod --apply --plan <hash>   # after the Atlas snapshot
pnpm migrate assert production --i-know-this-is-prod                                       # OK: in sync

What production adds, each rule with its test in tools/scripts/test/data-migrations-production.test.mjs and data-migrations-cli.test.mjs:

• prodSafety. up refuses a selection holding a cutover-only migration unless --cutover, and any migration that declares no prodSafety, before anything is read; the refusal names the online-safe ones to pass to --only. down has no prodSafety gate (dropping an index is not what the JVM collides with) but the same confirmation and hash.

• Preflights (read-only, through the dry-run facade), printed for every selected migration that has one; a starred count other than 0 refuses: the dry-run prints no hash, the apply stops before any write.

  Migration	Counts (starred = refusing)
  0001	user_preferences documents; * duplicate (organisationId, userId) pairs
  0002	accounting_sequence documents; * JE year counters and * pieceReference counters without organisationId; * twin counters; * British/British duplicates; counters of unknown kind — the twin-counter migration's own planner, so all-zero means it has run
  0004	* duplicate (organisationId, unique) payment pairs (FL-EA51's pipeline)

• The plan hash (16 hex digits) covers the cluster's hosts, the direction, --cutover, each selected migration's name, checksum and prodSafety, every operation the dry-run facade predicted for it (sorted: the builds run in parallel), and the refusing preflight counts — not the informational counts nor anything else that moves on its own. --apply needs --plan <hash>; under the lock it re-runs the preflights and the plan, then refuses — before any write — a refusing count, a plan predicted to fail, or a hash other than the one passed. A hash from an older dry-run stays valid only while nothing it covers has moved.

• The selection is explicit. Production computes what runs (--only, --to, --step) and hands umzug that list, so what is gated, checked and hashed is what runs.

• No new write. Production applies through the same lock, tracking documents and index helpers as staging. The twin-counter migration is not recorded in adb-system._migrations: a record with no file in packages/data/migrations makes every up, down and assert refuse (unknown). The 0002 preflight checks its result from the data instead.

State, measured 2026-10-09 to 11

Open questions

• Production has not been probed by this tool: the preflight counts it will print (2026-07-28 probe: 78 of 88 chrono and 9 of 13 year counters still organizationId) are not measured yet. The cutover's "JVM accounting writes quiesced" is a manual procedure this runbook does not describe.
• The plan hash binds the plan, not the person: whoever runs --apply with the printed hash applies it. The review is the operator reading the dry-run.
• The real-mongod integration test (MIGRATE_INT_MONGO_URI) covers local and staging paths only; the production rules are tested on the indexed fake, and the terminal confirmation through a pseudo-terminal against an unresolvable host.
• The gate runs in pnpm infra:setup only. Workers Builds deploys the services on push without it; a gate there needs a MongoDB URI in the build environment.
• invoices.unique_1 (the full index the live twin replaces, docs/local-sync/WP-D.md) is not managed here: staging has only unique_1_live, the local dump only unique_1.
• A DATA migration plans through bulkWrite only: the dry-run facade (tools/scripts/lib/data-migrations/clients.mjsplanningClient) records a bulkWrite of updateOne / updateMany without upsert and answers { dryRun: true }; an insert, a replace, a delete or an upsert is refused like any other write. The migration plans from its reads (0010 logs matched / to update / unresolved / skipped per collection, with sample ids).