d359a74a5f
Migration 983 was crashing backend startup on my local DB because (a) I'd manually applied it via psql during B day 3 development before the migration runner saw it, so the constraint existed but was not tracked; (b) the migration used plain ADD CONSTRAINT which Postgres doesn't support with IF NOT EXISTS for CHECK constraints. Fix: wrap the ALTER TABLE in a DO block that catches `duplicate_object` — re-running the migration becomes a no-op, matches the idempotency contract the other migrations in this directory observe. Any env where the constraint already exists (manual apply, prior successful run) now proceeds cleanly. Verified: backend starts cleanly after the fix. Pre-rc1 blocker resolved. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
57 lines
2.6 KiB
SQL
57 lines
2.6 KiB
SQL
-- v1.0.7 item B (day 3): CHECK constraint guaranteeing that every
|
|
-- row in status='reversal_pending' has next_retry_at set.
|
|
--
|
|
-- Failure mode this prevents: a reversal_pending row with
|
|
-- next_retry_at=NULL is invisible to the worker's select
|
|
-- WHERE status='reversal_pending' AND (next_retry_at IS NULL OR next_retry_at <= NOW())
|
|
-- (actually the `IS NULL` branch IS included in the worker query,
|
|
-- so the row would be picked up — but the pattern is fragile:
|
|
-- anyone who later writes a stricter WHERE clause expecting
|
|
-- next_retry_at NOT NULL would silently orphan these rows).
|
|
--
|
|
-- Belt-and-suspenders: the constraint makes the invariant structural.
|
|
-- reverseSellerAccounting always sets next_retry_at=NOW() on
|
|
-- transition; the worker always sets a retry timestamp on
|
|
-- same-state retry; terminal transitions (reversed,
|
|
-- permanently_failed) clear next_retry_at back to NULL. No code
|
|
-- path produces NULL in combination with reversal_pending — this
|
|
-- constraint codifies that.
|
|
--
|
|
-- The constraint is a CHECK not a NOT NULL because the column is
|
|
-- legitimately NULL in the other statuses (pending, completed,
|
|
-- failed without pending retry, reversed, permanently_failed).
|
|
|
|
-- ADD CONSTRAINT is not natively IF NOT EXISTS in Postgres — the
|
|
-- DO block catches `duplicate_object` so re-running the migration
|
|
-- (test runs, manual psql apply, re-deploys) is a no-op. Same shape
|
|
-- the runner expects from other idempotent migrations in this
|
|
-- directory.
|
|
DO $$
|
|
BEGIN
|
|
ALTER TABLE seller_transfers
|
|
ADD CONSTRAINT chk_reversal_pending_has_next_retry_at
|
|
CHECK (status <> 'reversal_pending' OR next_retry_at IS NOT NULL)
|
|
NOT VALID;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN
|
|
-- constraint already present (prior manual apply or prior run)
|
|
NULL;
|
|
END $$;
|
|
|
|
-- NOT VALID creates the constraint without scanning existing rows.
|
|
-- Pre-v1.0.7.2 rows are grandfathered — item A + day 2 of B don't
|
|
-- produce any reversal_pending rows with NULL next_retry_at, but
|
|
-- if any slipped in via tests or manual ops writes, validation
|
|
-- would fail the migration. Use VALIDATE CONSTRAINT in a follow-up
|
|
-- once the table is known to be clean:
|
|
--
|
|
-- ALTER TABLE seller_transfers VALIDATE CONSTRAINT chk_reversal_pending_has_next_retry_at;
|
|
--
|
|
-- Running VALIDATE requires a SHARE UPDATE EXCLUSIVE lock (not
|
|
-- AccessExclusive), which is cheap and non-blocking for readers.
|
|
-- Left as a separate one-shot so the deploy is independent of
|
|
-- table cleanup timing.
|
|
--
|
|
-- Post-validate, future INSERTs / UPDATEs that violate the
|
|
-- invariant will fail synchronously — the constraint is enforced
|
|
-- regardless of NOT VALID / VALIDATED state.
|