migrations/0025_payouts.sql

-- 0025_payouts.sql
-- Automated Payout Engine for Rtix
-- Tracks bank payout schedules, disbursements, and reconciliation.

CREATE TABLE IF NOT EXISTS merchant_bank_accounts (
    id              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    merchant_id     VARCHAR(36) NOT NULL REFERENCES merchants(merchant_id) ON DELETE CASCADE,
    account_holder  VARCHAR(255) NOT NULL,
    account_number  VARCHAR(50) NOT NULL,  -- stored encrypted in practice
    ifsc_code       VARCHAR(20) NOT NULL,
    bank_name       VARCHAR(100),
    is_primary      BOOLEAN NOT NULL DEFAULT TRUE,
    is_verified     BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(merchant_id, account_number)
);

CREATE TABLE IF NOT EXISTS payouts (
    id              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    merchant_id     VARCHAR(36) NOT NULL REFERENCES merchants(merchant_id) ON DELETE CASCADE,
    bank_account_id VARCHAR(36) REFERENCES merchant_bank_accounts(id),
    amount_inr      NUMERIC(14, 2) NOT NULL CHECK (amount_inr > 0),
    fee_inr         NUMERIC(10, 2) NOT NULL DEFAULT 0.0,
    net_inr         NUMERIC(14, 2) GENERATED ALWAYS AS (amount_inr - fee_inr) STORED,
    status          VARCHAR(20) NOT NULL DEFAULT 'PENDING'
                        CHECK (status IN ('PENDING', 'PROCESSING', 'SUCCESS', 'FAILED', 'REVERSED')),
    mode            VARCHAR(10) NOT NULL DEFAULT 'NEFT'
                        CHECK (mode IN ('NEFT', 'IMPS', 'RTGS', 'UPI')),
    utr_number      VARCHAR(50),           -- Unique Transaction Reference from bank
    initiated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    processed_at    TIMESTAMPTZ,
    failure_reason  TEXT,
    order_ids       JSONB DEFAULT '[]',    -- array of settled order IDs in this payout
    notes           TEXT
);

CREATE TABLE IF NOT EXISTS notification_log (
    id              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    merchant_id     VARCHAR(36),
    recipient_email VARCHAR(255) NOT NULL,
    event_type      VARCHAR(100) NOT NULL, -- e.g. ORDER_PLACED, PAYOUT_SUCCESS, SUB_RENEWAL
    subject         VARCHAR(500) NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'SENT'
                        CHECK (status IN ('SENT', 'FAILED', 'BOUNCED')),
    provider_id     VARCHAR(255),          -- external message ID from Resend/SES
    sent_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    error_message   TEXT
);

CREATE INDEX IF NOT EXISTS idx_payouts_merchant ON payouts(merchant_id);
CREATE INDEX IF NOT EXISTS idx_payouts_status ON payouts(status);
CREATE INDEX IF NOT EXISTS idx_payouts_initiated ON payouts(initiated_at DESC);
CREATE INDEX IF NOT EXISTS idx_bank_accounts_merchant ON merchant_bank_accounts(merchant_id);
CREATE INDEX IF NOT EXISTS idx_notification_log_merchant ON notification_log(merchant_id);
CREATE INDEX IF NOT EXISTS idx_notification_log_event ON notification_log(event_type);