vmall/apps/api/migrations/0024_payments.sql

-- 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;