Spring database backfill: assign historical rows before enforcing ownership

The checked row state

The H2 fixture inserts one row before expansion and one old-writer row afterward. Both have null tenant_id until an UPDATE assigns legacy. A count then finds two legacy rows. This is an explicit local rule chosen for the fixture; it is not a safe universal rule for assigning real customer records.

For a real tenant migration, derive ownership from trusted existing data. Quarantine rows whose owner cannot be proven. Count unresolved rows before adding a NOT NULL constraint. Tenant predicates only protect reads once the stored owner is correct; a wrong backfill can make authorization consistently wrong.

Bound the production job

The fixture updates the entire tiny table in one statement. On a large table, choose bounded batches with a stable key range or cursor, record progress, make reruns idempotent and monitor lock waits. A process can fail between batches; restarting should not duplicate or skip work. Verify sample rows and total counts before contraction.

A migration tool can version the DDL, but a data backfill may need a separate controlled job and audit trail. The runner lesson states what this kit does not automate. The contract test shows the failure old writers see after the final constraint.

Checked source

update shipment_receipt
set tenant_id = 'legacy'
where tenant_id is null;

Verification boundary

SchemaEvolutionContractTest.nullableExpansionAcceptsOldWriterAndBackfillPreservesRows. The excerpt is shortened or a labelled design sketch; the kit contains the checked tests.

Costs and limits

The test has two rows and a single H2 UPDATE. It does not prove correct ownership of real tenant data, bounded lock time or restart-safe batch progress.

Common Mistakes

• Do not invent a tenant owner for ambiguous historical data.
• Do not run an unbounded update on a large table without measuring locks.
• Do not enforce NOT NULL while unresolved rows remain.

Read next

Spring database rollout: add a nullable column before changing every writer, Spring database contract phase: reject old writers only after backfill, Spring Boot migrations: what a versioned runner must add to the SQL test, Spring JdbcTemplate tenant predicates: put ownership in the SQL query.