`scripts/migrate_012_payout_method_completeness.sql`

24 lines
-- migrate_012_payout_method_completeness.sql — payout-method field completeness.
--
-- BUG (cold audit, cycle 23): payout_methods.kind CHECK allows 'sinpe_movil' /
-- 'cr_iban' / 'plaid_ach', but NOTHING required the matching identifier column to be
-- present. A host could register kind='cr_iban' with cr_iban NULL (or sinpe_movil
-- with sinpe_phone NULL), it becomes their default, and at payout time
-- lib/payments/tilopay.js payout() builds {phone: sinpe_phone} — a cr_iban method has
-- no phone, so the host silently gets $0 (or a row stuck 'processing') in LIVE mode.
-- The route now 400s an incomplete method (routes/app.js) and payouts.js fails loud
-- in live for cr_iban; these DB CHECKs are the backstop so no incomplete row can
-- exist regardless of the entry path.
--
-- plaid_ach carries its identifier via plaid_account_id (set by the /host/plaid/*
-- exchange), so it is intentionally NOT constrained here.
--
-- PROD-APPLY: pre-check for existing violators first (a plain ADD CONSTRAINT fails if
-- any row violates), then remediate before applying:
--   SELECT id, kind FROM payout_methods
--    WHERE (kind='sinpe_movil' AND sinpe_phone IS NULL) OR (kind='cr_iban' AND cr_iban IS NULL);
--   -- if any: fix or delete those rows (verify the host's real payout target) first.
ALTER TABLE payout_methods
  ADD CONSTRAINT payout_methods_sinpe_phone_reqd CHECK (kind <> 'sinpe_movil' OR sinpe_phone IS NOT NULL),
  ADD CONSTRAINT payout_methods_iban_reqd        CHECK (kind <> 'cr_iban'     OR cr_iban     IS NOT NULL);