schema.sql

-- Fraud Detection PostgreSQL Schema
-- RBI retention: 8 years (PMLA)
-- PCI-DSS: no raw PAN, no CVV2 in any table

-- ── transactions ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS transactions (
    id                     BIGSERIAL PRIMARY KEY,
    transaction_id         VARCHAR(64)  NOT NULL UNIQUE,
    timestamp_utc          TIMESTAMPTZ  NOT NULL,
    rail                   VARCHAR(16)  NOT NULL,
    channel                VARCHAR(32)  NOT NULL,
    amount_inr             NUMERIC(14,2) NOT NULL,
    currency_code          CHAR(3)      NOT NULL DEFAULT 'INR',
    customer_id_hash       VARCHAR(64)  NOT NULL,
    client_id              VARCHAR(64),             -- BaaS tenant identifier
    card_token             VARCHAR(64),
    bin_8                  VARCHAR(8),
    pan_last4              CHAR(4),
    merchant_id            VARCHAR(64),
    mcc_code               CHAR(4),
    agent_id               VARCHAR(64),
    device_id_hash         VARCHAR(64),
    is_fraud               SMALLINT,
    label_source           VARCHAR(32),
    label_confirmed_at_utc TIMESTAMPTZ,
    data_source            VARCHAR(16)  NOT NULL,
    schema_version         VARCHAR(16)  NOT NULL DEFAULT '2.0.0',
    created_at             TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_txn_customer_ts
    ON transactions (customer_id_hash, timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS idx_txn_card_ts
    ON transactions (card_token, timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS idx_txn_agent_ts
    ON transactions (agent_id, timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS idx_txn_rail_ts
    ON transactions (rail, timestamp_utc DESC);

-- ── model_decisions (full audit log — every scoring decision) ─────────────────
-- TODO: enforce WORM at DB layer — run migrations/001_worm_model_decisions.sql to revoke
-- UPDATE on model_decisions from the app role. Immutable fields are protected by record_hash
-- (SHA-256), but DB-level REVOKE closes the threat model for compromised app credentials.
-- Mutable columns (shap_stored, confirmed_fraud) remain updatable by design.
CREATE TABLE IF NOT EXISTS model_decisions (
    id                      BIGSERIAL PRIMARY KEY,
    audit_id                UUID         NOT NULL DEFAULT gen_random_uuid(),
    transaction_id          VARCHAR(64)  NOT NULL,
    scored_at               TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    decision                VARCHAR(8)   NOT NULL,  -- ALLOW | REVIEW | BLOCK
    risk_score              REAL         NOT NULL,
    risk_score_int          SMALLINT     NOT NULL,
    lgbm_score              REAL,
    lstm_score              REAL,
    ae_score                REAL,
    xgb_score               REAL,
    catboost_score          REAL,
    client_id               VARCHAR(64),            -- BaaS tenant identifier
    rules_triggered         JSONB        NOT NULL DEFAULT '[]',
    top_reasons             JSONB        NOT NULL DEFAULT '[]',
    model_version           VARCHAR(32)  NOT NULL,
    feature_set_version     VARCHAR(16)  NOT NULL DEFAULT '2.0.0',
    latency_ms              REAL,
    confidence              VARCHAR(8),             -- HIGH | MEDIUM | LOW
    customer_id_hash        VARCHAR(64),
    rail                    VARCHAR(16),
    -- SHAP stored async after response
    shap_stored             BOOLEAN      NOT NULL DEFAULT FALSE,
    record_hash             VARCHAR(64),            -- SHA-256 of all fields (tamper detection)
    confirmed_fraud         BOOLEAN,               -- populated on label arrival
    trace_id                VARCHAR(128),           -- TPS correlation ID (X-Trace-ID header passthrough)
    CONSTRAINT fk_txn FOREIGN KEY (transaction_id) REFERENCES transactions (transaction_id)
                       ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_md_transaction_id
    ON model_decisions (transaction_id);
CREATE INDEX IF NOT EXISTS idx_md_scored_at
    ON model_decisions (scored_at DESC);
CREATE INDEX IF NOT EXISTS idx_md_model_version
    ON model_decisions (model_version, scored_at DESC);
CREATE INDEX IF NOT EXISTS idx_md_decision
    ON model_decisions (decision, scored_at DESC);

-- ── user_profiles (batch-updated daily — Tier 2 source for Redis) ─────────────
CREATE TABLE IF NOT EXISTS user_profiles (
    customer_id_hash   VARCHAR(64)   PRIMARY KEY,
    amount_avg_30d     NUMERIC(14,2),
    amount_std_30d     NUMERIC(14,2),
    txn_count_30d      INTEGER,
    account_age_days   INTEGER,
    home_lat           DOUBLE PRECISION,
    home_lon           DOUBLE PRECISION,
    last_txn_ts        TIMESTAMPTZ,
    last_cp_lat        DOUBLE PRECISION,
    last_cp_lon        DOUBLE PRECISION,
    state              VARCHAR(32),
    district           VARCHAR(64),
    updated_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── blocklists (O(1) lookup via Redis; Postgres is source of truth) ───────────
CREATE TABLE IF NOT EXISTS blocklists (
    id             BIGSERIAL PRIMARY KEY,
    entity_type    VARCHAR(16)  NOT NULL,  -- CARD_HASH | MERCHANT | IP | AGENT | AADHAAR | DEVICE
    entity_value   VARCHAR(128) NOT NULL,
    reason         VARCHAR(256),
    added_at       TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    expires_at     TIMESTAMPTZ,
    added_by       VARCHAR(64),
    is_active      BOOLEAN      NOT NULL DEFAULT TRUE,
    UNIQUE (entity_type, entity_value)
);

CREATE INDEX IF NOT EXISTS idx_bl_entity
    ON blocklists (entity_type, entity_value) WHERE is_active = TRUE;

-- ── shap_explanations (3-year retention — RBI customer dispute window) ─────────
CREATE TABLE IF NOT EXISTS shap_explanations (
    id               BIGSERIAL PRIMARY KEY,
    transaction_id   VARCHAR(64) NOT NULL,
    audit_id         UUID        NOT NULL,
    stored_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    top_reasons      JSONB       NOT NULL DEFAULT '[]',
    all_shap_values  JSONB,  -- full SHAP vector (compressed)
    base_fraud_prob  REAL,
    model_version    VARCHAR(32) NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_shap_txn_id ON shap_explanations (transaction_id);