Scaling FinTech Platforms to 1M+ Daily Transactions with SOC2 and PCI-DSS

-- Immutable Double-Entry Ledger Schema
CREATE TABLE financial_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    account_type VARCHAR(32) NOT NULL, -- 'ASSET', 'LIABILITY', 'EQUITY', 'REVENUE', 'EXPENSE'
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE ledger_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reference_id VARCHAR(128) UNIQUE NOT NULL, -- Idempotency key from payment gateway
    description TEXT NOT NULL,
    posted_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE ledger_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id UUID NOT NULL REFERENCES ledger_transactions(id) ON DELETE RESTRICT,
    account_id UUID NOT NULL REFERENCES financial_accounts(id) ON DELETE RESTRICT,
    amount_cents BIGINT NOT NULL, -- Positive for Debit, Negative for Credit
    entry_type VARCHAR(10) NOT NULL CHECK (entry_type IN ('DEBIT', 'CREDIT')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Constraint Trigger: Ensure sum of entries for any transaction is strictly ZERO
CREATE OR REPLACE FUNCTION verify_transaction_balance() RETURNS TRIGGER AS $$
DECLARE
    balance_sum BIGINT;
BEGIN
    SELECT COALESCE(SUM(amount_cents), 0) INTO balance_sum
    FROM ledger_entries
    WHERE transaction_id = NEW.transaction_id;

    IF balance_sum != 0 THEN
        RAISE EXCEPTION 'Ledger transaction % is out of balance: sum is % cents', NEW.transaction_id, balance_sum;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trg_verify_balance
AFTER INSERT OR UPDATE ON ledger_entries
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION verify_transaction_balance();