fix(db): contract migration -- drop 'cancelled' from CHECK constraints and drop cancelled_by column #47

Context

PR LeanerCloud/cloud-commitments-cli#1277 lands the EXPAND half of a cancelled->canceled rename (expand-contract pattern). The EXPAND migration (000078) is intentionally additive and non-normalizing:

• it widens both status CHECK constraints to accept BOTH spellings,
• it adds the canceled_by column and does a best-effort copy of existing cancelled_by values,
• it does NOT normalize status='cancelled' rows and does NOT authoritatively drain cancelled_by (a one-time backfill during EXPAND would be a false guarantee, since old code still running keeps writing fresh 'cancelled' / cancelled_by-only rows right after the backfill).

Throughout EXPAND the application reads BOTH spellings (status filter + KPI/cancel switches accept both; every column read projects COALESCE(canceled_by, cancelled_by)), so any row written by old or new code at any time reads correctly.

This CONTRACT step runs only after PR LeanerCloud/cloud-commitments-cli#1277 is deployed and every old code instance is gone — at which point the set of legacy rows is final and can be normalized completely.

Work

Create migration 000079 (next after 000078) that, in this order:

1. Normalize late legacy data FIRST (this is the authoritative pass the EXPAND migration deliberately skipped):
   • UPDATE purchase_executions SET status = 'canceled' WHERE status = 'cancelled';
   • UPDATE ri_exchange_history SET status = 'canceled' WHERE status = 'cancelled';
   • Drain attribution before dropping the column: UPDATE purchase_executions SET canceled_by = cancelled_by WHERE canceled_by IS NULL AND cancelled_by IS NOT NULL;
2. Drop 'cancelled' from the purchase_executions_status_check constraint (leaving only 'canceled' and the other statuses).
3. Drop 'cancelled' from the ri_exchange_history_status_check constraint.
4. ALTER TABLE purchase_executions DROP COLUMN IF EXISTS cancelled_by;

Ordering matters: normalize/drain (step 1) must run BEFORE narrowing the constraints (steps 2-3) and dropping the column (step 4), or the narrowed constraint rejects surviving 'cancelled' rows / the drop loses un-drained attribution. (Same ordering lesson as 000078's down.sql.)

Pre-conditions before applying

• PR fix(db): rename status value cancelled->canceled (expand-contract, US spelling) cloud-commitments-cli#1277 is deployed to production and verified stable.
• All old code instances are gone (no process still writes 'cancelled' or cancelled_by). This is the gate that makes step 1's normalization complete and final — it cannot be guaranteed during the EXPAND rolling deploy, which is exactly why normalization was deferred to here.

Code cleanup (same PR or follow-up)

Once the data is normalized and the column dropped, remove the EXPAND-window dual-spelling scaffolding added by LeanerCloud/cloud-commitments-cli#1277:

• config.LegacyStatusCanceled and every reference to it (history status filter, the cancel/KPI switches, the cancel-recovery check).
• The COALESCE(canceled_by, cancelled_by) in the 10 read projections in internal/config/store_postgres.go -> plain canceled_by.
• The legacy fixture in the KPI regression test and the file-level //nolint:misspell on the 000078 integration test.

Down migration

The down migration should:

1. Re-add 'cancelled' to both CHECK constraints.
2. ALTER TABLE purchase_executions ADD COLUMN IF NOT EXISTS cancelled_by TEXT;
3. Backfill cancelled_by from canceled_by for any rows that need it.
   (Order: re-add column/constraints widening before any data move; converting canceled->cancelled is optional on the down path since the widened-again constraint accepts both.)