Merge the accounting sequence twin counters at the production cutover

adb-accounting.accounting_sequence holds the continuous journal-entry number (FEC field #3) and the per-event-type piece-reference chrono (FEC field #9). Each counter exists under two spellings of the organisation field: the legacy organizationId, written until about January 2025, and organisationId, which the JVM has written since about March 2025 (CustomSequenceRepoImpl.java:32). When both exist for one counter, whichever one is continued can re-issue numbers that are already used. migrate-accounting-sequence-org-spelling.mjs renames every counter to organisationId, merges each pair keeping the highest value, and then creates the two unique indexes that stop a counter from ever forking again. Run it once, against production, at the cutover, with the JVM accounting writes quiesced: after the production database is final, and before the Cloudflare accounting Workers serve traffic. It is step 2 of the production order in Data migrations: migrations 0002 and 0004 follow it. Its --apply is race-safe since 2026-10-10: every write is conditioned on the read it was planned from, a counter is only ever raised ($max), a write that finds its document changed makes it re-read and re-plan, and a canonical EJSON backup of the collection is written before the first write.

At a glance

Steps

Run every command from the repository root (workspace installed: pnpm install --frozen-lockfile), in a terminal, with PROD_MONGO_URI exported in the shell. Never paste the URI into a file, a ticket or a command argument: the script reads it from MONGO_URI, set for the one command. Each command prints the cluster as scheme + hosts and asks you to type the host back.

1. Dry-run against production. It writes nothing. Read the plan: every MERGE line must keep the larger sequence, and the run must end without ERRORS.

   MONGO_URI="$PROD_MONGO_URI" node services/accounting/scripts/migrate-accounting-sequence-org-spelling.mjs --i-know-this-is-prod
   

2. Quiesce the JVM accounting writes, then apply, once. The apply writes its backup first and prints its path (BACKUP ~/accounting_sequence-backup-….ejson), then works in rounds: each round re-reads the collection and applies the plan of that read, one conditioned write at a time; a CONFLICT line means a document moved under it, and the next round re-plans. It ends with result: APPLIED and the two indexes, or says why not (exit 1).

   MONGO_URI="$PROD_MONGO_URI" node services/accounting/scripts/migrate-accounting-sequence-org-spelling.mjs --i-know-this-is-prod --apply
   

3. Verify. The next dry-run must print renamed: 0 merged: 0, and the index listing must show both unique indexes. Then pnpm migrate up production --only 0002 --cutover --i-know-this-is-prod must show every starred preflight count at 0 (Data migrations).

   MONGO_URI="$PROD_MONGO_URI" node services/accounting/scripts/migrate-accounting-sequence-org-spelling.mjs --i-know-this-is-prod
   

   mongosh "$PROD_MONGO_URI" --quiet --eval 'print(db.getSiblingDB("adb-accounting").accounting_sequence.getIndexes().map(i=>i.name).join(", "))'
   

Rollback

The merge deletes the losing twin of each pair. To restore the exact pre-migration state, reload the backup the apply printed; --drop also drops the two new indexes:

mongoimport --uri "$PROD_MONGO_URI" --db adb-accounting --collection accounting_sequence --drop --jsonArray --file ~/accounting_sequence-backup-<host>-<time>.ejson

Do it only if the plan was wrong: once a new number has been issued after the migration, restoring the backup would let that number be issued again.

Details

• Why MAX. The higher counter of a pair is the one whose numbers have been issued furthest. Keeping the lower one would re-issue every number between the two.
• Race safety. Until 2026-10-10 the apply read once and wrote $set: { sequence } / deleteOne({ _id }) without checking what it had read; reproduced on the indexed fake, a JVM increment after the read was lowered back (2000 → 1102), a twin continued after the read was deleted, and a JVM upsert before a rename left a British/British pair. Now each write's filter pins the document as read (_id, sequence, both spellings, year / accountingEventType); the survivor takes $max; a rename, or an American survivor turning British, is held while a British counter exists for its key; a write that matches nothing ends the round, and the next one re-reads and re-plans (at most 4 rounds). The run converges when a fresh read has nothing left to do; the indexes are built only then. The remaining window — a JVM upsert in the millisecond between a rename's check and its write — leaves a British/British pair that the next round merges at MAX and reports (ERRORS, no index, exit 1).
• Code safety net. Until the migration runs, the Cloudflare allocators (getNewSequence, nextPieceReference in packages/data/accounting/sequence/mutations.mjs) read both spellings and sort by sequence descending, so they always continue the higher twin. The JVM does not: it only reads organisationId, so on a database with twins it keeps continuing the newer, usually lower, counter.
• Evidence on staging, 2026-10-06, read-only before the migration. There were 66 piece references shared by distinct event families (children legitimately share their parent's reference, so the count is per family). All of them were created between October 2024 and July 2025, and none since June 2026. Example: incoming supplier invoices had counters at 10 (organizationId) and 16 (organisationId), and the 10 overlapping numbers are exactly the 10 duplicates.
• ensureAccountingIndexes() (packages/data/accounting/indexes.mjs) is not called anywhere at runtime. The migration creates the two sequence indexes itself. accounting_event and invoices already carry their unique_1 index from the JVM-era scripts.

Open questions

• The race-safe apply has been run on the indexed fake and as a dry-run on staging (2026-10-10), not as an apply on a real cluster.
• Production has not been probed. Numbers that were already issued twice stay duplicated after the migration; whether the FEC needs a correction or a documented note is for the accountant to decide.