`scripts/migrate_011_payments_one_inflight.sql`

43 lines
-- migrate_011_payments_one_inflight.sql — backstop for a double-charge race.
--
-- BUG (cold audit, cycle 19): POST /bookings/:code/pay does a lock-free
-- check-then-act — SELECT "any in-flight payment for this booking?" then INSERT a
-- 'processing' row, then call the processor. Two truly concurrent pay requests for
-- one booking (double-tap "Pay" on flaky CR mobile data, or a client auto-retry
-- racing a manual retry) BOTH pass the SELECT (each sees 0 in-flight), BOTH INSERT,
-- and BOTH call provider.createCharge() -> the traveler's card is charged TWICE for
-- one booking. Neither adapter sends a provider idempotency key, so the processor
-- doesn't dedupe it either. Reproduced at the DB layer against two interleaved pg
-- connections.
--
-- The app-level SELECT can't fix a TOCTOU; the DB must enforce it. This partial
-- unique index allows at most ONE non-terminal (in-flight) payment per booking —
-- the exact mirror of payouts_one_per_booking_rail (migrate_009) on the payout leg,
-- which the charge leg was missing. A booking can still have many failed/succeeded/
-- refunded payments (a retry after a 'failed' attempt is fine — 'failed' isn't
-- in-flight); only concurrent in-flight charges are blocked. The race-loser's INSERT
-- raises 23505, which routes/app.js catches and turns into "reuse the winner's
-- in-flight payment" -> no second charge fires.
--
-- ('requires_action' is never actually stored — the payments.status CHECK coerces it
-- to 'processing' — but the predicate mirrors the route's in-flight check exactly.)
--
-- PROD-APPLY (do BOTH, in order):
-- (1) PRE-FLIGHT DUPLICATE CHECK — `payments` is the hottest write path and the bug
--     this fixes may ALREADY have created ≥2 in-flight rows for a booking, which would
--     make the index build FAIL. Check first, and remediate (keep the newest in-flight
--     row, mark the rest 'failed') before building:
--       SELECT booking_id, count(*) FROM payments
--        WHERE status IN ('processing','requires_action') GROUP BY booking_id HAVING count(*) > 1;
--       -- if any rows: UPDATE payments SET status='failed' WHERE id IN (
--       --   SELECT id FROM (SELECT id, row_number() OVER (PARTITION BY booking_id ORDER BY created_at DESC) rn
--       --     FROM payments WHERE status IN ('processing','requires_action')) t WHERE rn > 1);
--       -- (verify against the processor which of the dupes actually charged before failing the rest.)
-- (2) BUILD CONCURRENTLY — a plain CREATE UNIQUE INDEX SHARE-locks payments (blocks
--     every checkout write) for the build; CONCURRENTLY does not. apply-migrations.sh
--     runs this file outside a transaction, so CONCURRENTLY is safe through the runner.
--     If a CONCURRENTLY build is interrupted it leaves an INVALID index:
--       DROP INDEX CONCURRENTLY IF EXISTS payments_one_inflight_per_booking;  -- then re-run.
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS payments_one_inflight_per_booking
  ON payments (booking_id) WHERE status IN ('processing','requires_action');