-- Configurable payments: one auditable checkout-wide payment unit with -- per-order allocations, idempotent attempts, durable provider commands and -- verified events, receipts, original-method refunds, versioned channel -- configuration and legacy-demo provenance. -- -- Append-only: applied migrations are never edited. State columns are TEXT -- with CHECK constraints so future states can be added by later migrations. -- --------------------------------------------------------------------------- -- 1) Legacy provenance for historical status-only paid orders. -- --------------------------------------------------------------------------- ALTER TABLE orders ADD COLUMN payment_provenance TEXT NOT NULL DEFAULT 'payment_backed'; ALTER TABLE orders ADD CONSTRAINT orders_payment_provenance_ck CHECK (payment_provenance IN ('payment_backed', 'legacy_demo')); -- Orders that were marked paid without any collected funds are historical -- demo records. They are never supplied fictional captures; their refunds -- keep the explicitly labeled legacy demo wallet-credit path. UPDATE orders SET payment_provenance = 'legacy_demo' WHERE status IN ('paid', 'fulfilling', 'shipped', 'completed'); CREATE INDEX idx_orders_payment_provenance ON orders (payment_provenance) WHERE payment_provenance = 'legacy_demo'; -- --------------------------------------------------------------------------- -- 2) Aftersale states for external original-method refunds. -- --------------------------------------------------------------------------- ALTER TYPE aftersale_status ADD VALUE IF NOT EXISTS 'refund_pending'; ALTER TYPE aftersale_status ADD VALUE IF NOT EXISTS 'refund_failed'; -- --------------------------------------------------------------------------- -- 3) Payment configuration: channels, immutable revisions, append-only audit. -- --------------------------------------------------------------------------- CREATE TABLE payment_channels ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), adapter TEXT NOT NULL CHECK (adapter IN ('wallet', 'stripe_crypto')), label JSONB NOT NULL, display_order INT NOT NULL DEFAULT 0, enabled BOOLEAN NOT NULL DEFAULT false, supported_currencies TEXT[] NOT NULL, min_amount_minor BIGINT NOT NULL CHECK (min_amount_minor > 0), max_amount_minor BIGINT CHECK (max_amount_minor IS NULL OR max_amount_minor >= min_amount_minor), provider_account_id TEXT, -- Secret REFERENCES only (deployment-allowlisted env-var identifiers). -- Plaintext credentials are never stored on any row. secret_key_ref TEXT, webhook_secret_ref TEXT, environment TEXT NOT NULL CHECK (environment IN ('demo', 'sandbox', 'live')), archived BOOLEAN NOT NULL DEFAULT false, revision INT NOT NULL DEFAULT 1 CHECK (revision >= 1), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_payment_channels_env ON payment_channels (environment, enabled, archived); -- Immutable per-revision configuration snapshots; attempts and refunds retain -- the revision they ran against. CREATE TABLE payment_channel_revisions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), channel_id UUID NOT NULL REFERENCES payment_channels (id) ON DELETE CASCADE, revision INT NOT NULL CHECK (revision >= 1), snapshot JSONB NOT NULL, adapter_contract_version TEXT NOT NULL, created_by UUID REFERENCES users (id), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (channel_id, revision) ); -- Append-only configuration audit with redacted changes. Reference -- identifiers may appear; secret values never do. CREATE TABLE payment_channel_audit ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), channel_id UUID NOT NULL REFERENCES payment_channels (id) ON DELETE CASCADE, revision INT NOT NULL, action TEXT NOT NULL CHECK ( action IN ('created', 'updated', 'enabled', 'disabled', 'verified', 'archived') ), actor TEXT NOT NULL, changes JSONB NOT NULL DEFAULT '{}'::jsonb, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_payment_channel_audit_channel ON payment_channel_audit (channel_id, created_at DESC); -- --------------------------------------------------------------------------- -- 4) Payments, checkout idempotency and immutable order membership. -- --------------------------------------------------------------------------- CREATE TABLE payments ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), owner_user_id UUID NOT NULL REFERENCES users (id), currency CHAR(3) NOT NULL REFERENCES currencies (code), -- Checked total: equals SUM(payment_orders.amount) at creation. total_minor BIGINT NOT NULL CHECK (total_minor > 0), -- Immutable deployment environment tag. environment TEXT NOT NULL CHECK (environment IN ('demo', 'sandbox', 'live')), status TEXT NOT NULL DEFAULT 'unapplied' CHECK (status IN ('unapplied', 'applied', 'closed')), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), applied_at TIMESTAMPTZ, closed_at TIMESTAMPTZ ); CREATE INDEX idx_payments_owner ON payments (owner_user_id, created_at DESC); -- Immutable checkout-wide membership; each order belongs to at most one -- payment and its amount equals the snapshotted order total. CREATE TABLE payment_orders ( payment_id UUID NOT NULL REFERENCES payments (id), order_id UUID NOT NULL UNIQUE REFERENCES orders (id), amount_minor BIGINT NOT NULL CHECK (amount_minor >= 0), PRIMARY KEY (payment_id, order_id) ); CREATE INDEX idx_payment_orders_payment ON payment_orders (payment_id); -- Owner-scoped checkout idempotency: the durable replay record for an -- uncertain checkout response. The request hash covers the full payload -- (address, currency, coupons, group intent, cart snapshot). CREATE TABLE checkout_requests ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), owner_user_id UUID NOT NULL REFERENCES users (id), idempotency_key TEXT NOT NULL, request_hash TEXT NOT NULL, payment_id UUID NOT NULL REFERENCES payments (id), response JSONB NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (owner_user_id, idempotency_key) ); -- --------------------------------------------------------------------------- -- 5) Attempts: one durable operation per initiation, idempotency-bound. -- --------------------------------------------------------------------------- CREATE TABLE payment_attempts ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), payment_id UUID NOT NULL REFERENCES payments (id), channel_id UUID NOT NULL REFERENCES payment_channels (id), channel_revision INT NOT NULL, owner_user_id UUID NOT NULL REFERENCES users (id), idempotency_key TEXT NOT NULL, request_hash TEXT NOT NULL, return_surface TEXT NOT NULL CHECK (return_surface IN ('mall', 'mobile')), return_url TEXT NOT NULL, state TEXT NOT NULL DEFAULT 'created' CHECK (state IN ('created', 'pending', 'unknown', 'settled', 'failed', 'expired')), -- Stable provider operation identity (Stripe idempotency key, or the -- internal wallet operation key). Permanent local identity. provider_operation_key TEXT NOT NULL UNIQUE, provider_reference TEXT, provider_intent_reference TEXT, next_action_type TEXT NOT NULL DEFAULT 'none' CHECK (next_action_type IN ('none', 'redirect', 'wait')), next_action_url TEXT, expires_at TIMESTAMPTZ, error_detail TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (payment_id, idempotency_key) ); -- At most one unresolved attempt per payment. CREATE UNIQUE INDEX payment_attempts_one_unresolved ON payment_attempts (payment_id) WHERE state IN ('created', 'pending', 'unknown'); CREATE INDEX idx_payment_attempts_payment ON payment_attempts (payment_id, created_at DESC); -- --------------------------------------------------------------------------- -- 6) Receipts: immutable actual collections, unique provider identity. -- --------------------------------------------------------------------------- CREATE TABLE payment_receipts ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), payment_id UUID NOT NULL REFERENCES payments (id), attempt_id UUID REFERENCES payment_attempts (id), provider_account_id TEXT, environment TEXT NOT NULL CHECK (environment IN ('demo', 'sandbox', 'live')), -- Unique provider-account/environment/transaction identity. provider_transaction TEXT NOT NULL, amount_minor BIGINT NOT NULL CHECK (amount_minor > 0), currency CHAR(3) NOT NULL, method TEXT NOT NULL CHECK (method IN ('wallet', 'stripe_crypto')), applied BOOLEAN NOT NULL DEFAULT false, quarantined BOOLEAN NOT NULL DEFAULT false, quarantine_reason TEXT, -- Provider evidence and optional crypto metadata (asset identity and -- integer base-unit quantity as a decimal string; never floats). evidence JSONB NOT NULL DEFAULT '{}'::jsonb, crypto JSONB, received_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (provider_account_id, environment, provider_transaction) ); CREATE INDEX idx_payment_receipts_payment ON payment_receipts (payment_id); -- --------------------------------------------------------------------------- -- 7) Refunds: original-method operations with reserved budgets. -- --------------------------------------------------------------------------- CREATE TABLE payment_refunds ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), payment_id UUID NOT NULL REFERENCES payments (id), receipt_id UUID NOT NULL REFERENCES payment_receipts (id), -- The allocation being refunded (aftersale refunds); NULL for -- compensation of receipts that were never applied to any order. allocation_order_id UUID REFERENCES orders (id), aftersale_id UUID REFERENCES aftersales (id), purpose TEXT NOT NULL CHECK (purpose IN ('aftersale', 'compensation')), amount_minor BIGINT NOT NULL CHECK (amount_minor > 0), currency CHAR(3) NOT NULL, idempotency_key TEXT NOT NULL, provider_operation_key TEXT NOT NULL UNIQUE, provider_reference TEXT, -- The channel revision/credential references used remain fixed here. channel_revision INT NOT NULL, state TEXT NOT NULL CHECK (state IN ('pending', 'unknown', 'succeeded', 'failed')), error_detail TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), completed_at TIMESTAMPTZ, UNIQUE (payment_id, idempotency_key) ); -- A permitted replacement has a new operation identity and must not overlap -- an unresolved operation on the same target. CREATE UNIQUE INDEX payment_refunds_one_unresolved ON payment_refunds ( receipt_id, COALESCE(allocation_order_id::text, ''), COALESCE(aftersale_id::text, '') ) WHERE state IN ('pending', 'unknown'); CREATE INDEX idx_payment_refunds_payment ON payment_refunds (payment_id, created_at DESC); CREATE INDEX idx_payment_refunds_aftersale ON payment_refunds (aftersale_id); -- --------------------------------------------------------------------------- -- 8) Durable provider commands and the verified event inbox. -- --------------------------------------------------------------------------- CREATE TABLE payment_commands ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), kind TEXT NOT NULL CHECK (kind IN ('create_checkout', 'refund', 'reconcile')), payment_id UUID REFERENCES payments (id), attempt_id UUID REFERENCES payment_attempts (id), refund_id UUID REFERENCES payment_refunds (id), -- Stable provider idempotency key carried through every retry. operation_key TEXT NOT NULL UNIQUE, idempotency_key TEXT NOT NULL, payload JSONB NOT NULL DEFAULT '{}'::jsonb, state TEXT NOT NULL DEFAULT 'pending' CHECK (state IN ('pending', 'leased', 'succeeded', 'failed', 'cancelled')), due_at TIMESTAMPTZ NOT NULL DEFAULT now(), lease_owner TEXT, lease_expires_at TIMESTAMPTZ, attempts_count INT NOT NULL DEFAULT 0, last_error TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), finished_at TIMESTAMPTZ, UNIQUE (kind, idempotency_key) ); CREATE INDEX idx_payment_commands_due ON payment_commands (state, due_at) WHERE state IN ('pending', 'leased'); -- Materialized exception rows so operations can list and resolve them: -- quarantined receipts, received-but-unapplied collections, and operations -- whose outcome remains unknown. Resolution reuses the normal refund / -- reconciliation paths; nothing here asserts a financial outcome. CREATE TABLE payment_exceptions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), payment_id UUID NOT NULL REFERENCES payments (id), kind TEXT NOT NULL CHECK ( kind IN ('unapplied_receipt', 'extra_collection', 'amount_mismatch', 'unknown_operation') ), receipt_id UUID REFERENCES payment_receipts (id), attempt_id UUID REFERENCES payment_attempts (id), detail TEXT, status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'resolved')), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), resolved_at TIMESTAMPTZ ); CREATE INDEX idx_payment_exceptions_payment ON payment_exceptions (payment_id, status); -- Verified provider notifications, persisted before 2xx acknowledgement and -- deduplicated by provider account, environment and event id. CREATE TABLE payment_events ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), channel_id UUID NOT NULL REFERENCES payment_channels (id), provider_account_id TEXT, environment TEXT NOT NULL CHECK (environment IN ('demo', 'sandbox', 'live')), provider_event_id TEXT NOT NULL, event_type TEXT NOT NULL, payload JSONB NOT NULL, signature_key_id TEXT, received_at TIMESTAMPTZ NOT NULL DEFAULT now(), processed_at TIMESTAMPTZ, process_error TEXT, UNIQUE (environment, provider_account_id, provider_event_id) ); CREATE INDEX idx_payment_events_unprocessed ON payment_events (received_at) WHERE processed_at IS NULL;