Idempotent migrations and partial-failure recovery

An operational database cannot be changed in one shot like a fresh database. Preserve existing rows, converge to the same state on repeated runs, and expose enough failure context for a safe retry.

1. Expand/contract order

1. Add new columns and indexes in a compatible shape.
2. Deploy code that can read and write both old and new paths.
3. Backfill and verify, then switch the default path.
4. Remove obsolete contracts and constraints in a separate cleanup step.

Codingstairs applies the same approach to content_kind=note by repairing the legacy blog/edu CHECK. Existing data is preserved, the new contract is added, and Admin rolls the transaction back if the migration fails.

2. Idempotent seeding

Declare stable keys such as (language, content_kind, category_slug, slug) and (series_slug, language, slug), then use ON CONFLICT. Preserving publication dates and user-entered duration during an upsert is also part of idempotency. If one file fails, return the error list instead of reporting the whole run as successful.

3. Design indexes with queries

Public lists filter and order by language, kind, publication state, and time. Category, popular, and keyword reads need indexes that match their real predicates. Array keyword search pairs a GIN index with keywords @> ARRAY[$1]::text[]. Compare representative queries with EXPLAIN (ANALYZE, BUFFERS) on staging data after adding an index.

4. Recovery criteria

• Running the same migration twice is a no-op without errors.
• A file failure is observable through status, path, and retry guidance.
• After a failed transaction, the same command can normalize the database.
• After restore, the order remains schema repair → reference data → file seed.

Deleting a database or recreating a volume is not a recovery test. Use a separate restore target and read-only checks.