Reference solution
The findings below are ordered by priority.
Findings
1. The expansion migration rewrites and locks the large table
Location: migrations/20260801-add-normalized-email.sql:1-5
The column is introduced as NOT NULL with a default and the index is built by the ordinary blocking operation. Under the explicitly stated database behavior, deployment rewrites the profile table and blocks writes instead of performing a safe online expansion.
Add a nullable column without a default first, then build the index with the database's online operation only after backfill.
2. New application writes stop maintaining the legacy column
Location: src/update-profile-email.ts:3-10
The update writes only normalizedEmail. Old application versions still read email, so users routed to them observe stale addresses throughout the mixed-version deployment.
Dual-write both representations in one repository transaction until fleet state proves every legacy reader and writer is retired.
3. Offset pagination skips rows as the missing set shrinks
Location: src/normalized-email-backfill.ts:9-17
After the first batch becomes non-null, those rows disappear from listMissing. Advancing an offset by the batch size then skips the next portion of the shrinking result set, allowing the worker to finish with null rows untouched.
Use the supplied listMissingAfterId keyset operation, ordered by stable profile ID, and resume from loadCommittedProfileId.
4. Backfill updates can overwrite a concurrent user change
Location: src/profile-migration-types.ts:13-16 and src/normalized-email-backfill.ts:18-25
updateNormalized has no conditional null precondition. A profile write that sets a fresh normalized value after selection can be overwritten with normalization of the stale email captured by the batch.
Use updateNormalizedIfMissing so the write checks both the null precondition and the selected email version. Treat an already non-null row as successfully skipped and retry a stale-email result before advancing beyond that ID.
5. The checkpoint commits before the batch data
Location: src/normalized-email-backfill.ts:16-25
The worker saves its next offset before any profile update, and the writes are independent promises rather than one batch transaction. A crash or partial rejection can make restart skip rows whose data never committed.
Run the conditional batch updates and saveCommittedProfileId through one database.transaction, then expose that committed checkpoint to the next run.
6. Replica lag can authorize an incomplete contraction
Location: src/finish-profile-migration.ts:10-11
The null count is read from a replica. A lagging replica can report an older state and is not authoritative for deciding whether the primary can accept a NOT NULL constraint.
Validate zero nulls on the primary immediately before contraction and stop if that authoritative check fails.
7. Constraints and column removal ignore mixed-version fleet state
Location: src/finish-profile-migration.ts:6-14
The injected fleet dependency is unused. The function sets NOT NULL and immediately drops email even while legacy processes may still write null normalized values or read the legacy column, breaking both versions and eliminating rollback.
Wait for explicit no-legacy-writers confirmation before NOT NULL, and defer legacy-column removal to a later independently reversible deployment.
8. The test avoids every resumability and concurrency boundary
Location: src/normalized-email-backfill.test.ts:6-31
One row and an immediate empty page cannot expose shrinking-offset skips, a crash after checkpoint, partial updates, concurrent dual writes, the 500-row bound, or primary-versus-replica validation. The test must also avoid asserting an offset value as the checkpoint; a correct keyset test should expect the greatest processed profile ID, which is 10 in this fixture.
Add multi-page keyset cases, injected failures at each commit boundary, conditional-update races, restart replay, and rollout-order integration tests.
Reasonable non-findings
- Lowercasing and trimming are the stated normalization policy; locale-specific address rewriting is not required.
- Batching up to 500 rows is appropriate once each batch is transactional and keyset-based.
- Keeping a durable checkpoint is useful; its identity and commit ordering are the defects.
- A later dedicated contraction may remove the legacy column after compatibility and rollback windows close.