Reporting storage: make the contract-2 ledger upgrade rolling-deploy safe (expand/contract) #1856

Follow-up to #1831. Today the 0.2.x upgrade (migrations 0002 / 0006) is stopped-worker only. When the first new process boots, create_schema() widens the generation, obligation-period and status-checkpoint keys with offering_id. From then on, earlier-release processes can't write (their ON CONFLICT targets no longer match a unique index, 42P10) or restart (0.1.0b4's boot check accepts only the 4-column configuration PK). The workaround for now is a Recreate or two-phase rollout (docs, #1855). SDK adopters running several replicas will want a rolling upgrade that just works.

Feasibility (prototyped against real PostgreSQL with the published wheels)

An expand/contract split works. Prototype sequence:

1. seed with adcp==9.3.1 + adcp-reporting-storage==0.1.0b4;
2. upgrade with 0.2.x;
3. apply the expand state;
4. run old pods, new pods, then old pods again;
5. contract.

Results:

• Old 0.1.0b4: boots (with and without notifications), and its boot leaves the catalog unchanged. Reads, commit_obligation, put_configuration and full worker turns all succeed.
• New code (patched): boots and runs worker turns; reads include the old pods' rows.
• After contract: the catalog and function bodies are identical to a fresh install (with notifications), and unpatched new code boots on it.
• Rollback to 0.1.0b4 keeps working until contract.

Expand state (delta from the current contract-2 catalog)

Keep what 0.1.0b4 needs under its original names, and give the widened objects new names:

• reporting_configurations_pkey = PK on the 4 columns. reporting_configurations_generation_key = UNIQUE on the 5 columns; it is the FK target and matches the new ON CONFLICT.
• reporting_obligations_period_key = 6 columns. The 7-column index becomes reporting_obligations_offering_period_key.
• The old 4-column FKs from reconciliation records and scope checkpoints return beside the 5-column …_generation_fkey.
• With notifications: the 6-column checkpoint PK plus a 7-column …_offering_key; the old 6-column event FK plus …_events_offering_fkey; the old 7-column event unique key plus …_events_offering_key.
• CHECK (offering_id = '') on the widened tables until contract. Old processes would otherwise treat a subscription generation as account-level.

0.1.0b4's boot check reads only the PK columns, and its validators compare fingerprints by object name. So the 4-column PK under the original name is what lets old pods boot. A 4-column unique index beside a 5-column PK does not.

Contract

A new stopped-worker migration (0007) drops the expand-only objects and CHECKs, renames the widened ones back, and reruns the current DDL. It should refuse while a contract-1 worker could still be running (e.g. require an explicit operator flag or python -m adcp migrate --contract).

What it costs

• No subscription generations (a second offering of a configuration version) until contract.
• Old boots revert functions: each old-process boot reruns its DDL and reverts 3 reconciliation functions (plus 2 status guards with notifications) to the 0.1.0b4 bodies. That's harmless while every row is ''. But the old reporting_reconciliation_validate rejects payload keys offering_id / path_template, so new pods' reconciliation writes that carry path_template fail until a new pod reboots. A fix: new boots re-assert the current bodies, or a 0.1.0b6 stops rerunning its DDL once 0002 is recorded.
• Changes on next:
  • 0002 / 0006 must recognize the expand state instead of re-widening (today they fail on it with DuplicateColumn offering_id);
  • the notification and status manifests must accept the expand fingerprints;
  • a new 0007;
  • apply_pending on the store= boot path should not auto-apply the contract step.
• Tests: extend test_reporting_storage_published_upgrade_pg.py to run seed (old) → expand → old pod writes and restart → new → contract, and compare the catalog with a fresh one.

Order

Port main's storage changes (#1827, #1828, #1835, #1836) to next first, so this lands on one schema.

🤖 Generated with Claude Code