Skip to content

Data Model Compliance

Jump to bottom
Tiana_ edited this page May 30, 2026 · 1 revision

Data Model - Compliance Schema

Part of the Data Model reference. Schema: compliance. Companion to Domain-Model, Architecture-Security.

Tables in this schema

Table Role
compliance.kyc_sessions KYC verification session per user (mutable, versioned)
compliance.kyc_documents Document references per KYC session (encrypted external refs)
compliance.aml_alerts AML rule match alerts (append-only)
compliance.aml_rules AML rule definitions and versions
compliance.compliance_cases Investigation cases (mutable, versioned)
compliance.case_notes Append-only notes per case
compliance.processed_events Consumer-side event deduplication

Table: kyc_sessions 

CREATE TABLE IF NOT EXISTS compliance.kyc_sessions (
    id                      UUID          PRIMARY KEY,
    user_id                 UUID          NOT NULL,
    provider                VARCHAR(64)   NOT NULL,
    provider_session_id     VARCHAR(255),
    hosted_url              VARCHAR(2048),
    status                  VARCHAR(32)   NOT NULL DEFAULT 'PENDING',
    rejection_reason        VARCHAR(2048),
    expires_at              TIMESTAMPTZ   NOT NULL,
    version                 BIGINT        NOT NULL DEFAULT 0,
    created_at              TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ   NOT NULL DEFAULT NOW(),

    -- Encrypted columns (DEK from Vault, AES-256-GCM; see Architecture-Security)
    evidence_external_ref   BYTEA,
    evidence_provider       VARCHAR(64),
    evidence_verified_at    TIMESTAMPTZ,
    evidence_confidence     NUMERIC(3, 2),
    evidence_sanctions_hit  BOOLEAN,
    evidence_pep_hit        BOOLEAN,
    evidence_adverse_media  BOOLEAN,

    CONSTRAINT ck_kyc_sessions_status CHECK (status IN (
        'PENDING','AWAITING_DOCUMENTS','IN_REVIEW',
        'APPROVED','REJECTED','EXPIRED','MANUAL_REVIEW'
    )),
    CONSTRAINT ck_kyc_sessions_expires    CHECK (expires_at > created_at),
    CONSTRAINT ck_kyc_sessions_confidence CHECK (
        evidence_confidence IS NULL
        OR (evidence_confidence >= 0 AND evidence_confidence <= 1)
    )
);

CREATE INDEX IF NOT EXISTS idx_kyc_sessions_user_id
    ON compliance.kyc_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_kyc_sessions_status
    ON compliance.kyc_sessions(status);
-- Partial: only sessions still awaiting expiry need this lookup
CREATE INDEX IF NOT EXISTS idx_kyc_sessions_expires
    ON compliance.kyc_sessions(expires_at)
    WHERE status IN ('PENDING','AWAITING_DOCUMENTS');

CREATE OR REPLACE TRIGGER trg_kyc_sessions_updated_at
    BEFORE UPDATE ON compliance.kyc_sessions
    FOR EACH ROW EXECUTE FUNCTION shared.set_updated_at();

Query patterns:

  • Load session by ID: PK lookup
  • List sessions for user: idx_kyc_sessions_user_id + status filter
  • Expiry batch job: idx_kyc_sessions_expires (partial, active sessions only)

Notes:

  • provider is a logical identifier; no real KYC provider names are stored in DDL.
  • evidence_* columns store results returned from the provider after verification. evidence_external_ref is encrypted at the column level (AES-256-GCM, DEK from Vault). See Architecture-Security.
  • version maps to @Version for optimistic locking.

Table: kyc_documents (references only - no PII content stored)

CREATE TABLE IF NOT EXISTS compliance.kyc_documents (
    id              UUID        PRIMARY KEY,
    session_id      UUID        NOT NULL,
    type            VARCHAR(64) NOT NULL,
    external_ref    BYTEA       NOT NULL,    -- encrypted reference to external storage
    status          VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    uploaded_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_kyc_documents_session FOREIGN KEY (session_id)
        REFERENCES compliance.kyc_sessions(id) ON DELETE RESTRICT,
    CONSTRAINT ck_kyc_documents_status CHECK (status IN ('PENDING','VERIFIED','REJECTED'))
);

CREATE INDEX IF NOT EXISTS idx_kyc_documents_session
    ON compliance.kyc_documents(session_id);

Notes:

  • No document content is stored in the database. external_ref is an encrypted pointer to the document stored in an external object store.
  • Append-only for regulatory completeness; no deletes.

Table: aml_alerts 

CREATE TABLE IF NOT EXISTS compliance.aml_alerts (
    id              UUID    PRIMARY KEY,
    transaction_id  UUID    NOT NULL,
    rule_set_id     VARCHAR(64)  NOT NULL,
    risk_score      INT          NOT NULL,
    matched_rules   JSONB        NOT NULL,
    status          VARCHAR(32)  NOT NULL DEFAULT 'OPEN',
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    CONSTRAINT ck_aml_alerts_status     CHECK (status IN ('OPEN','LINKED_TO_CASE','CLOSED_NO_ACTION')),
    CONSTRAINT ck_aml_alerts_risk_score CHECK (risk_score BETWEEN 0 AND 100)
);

CREATE INDEX IF NOT EXISTS idx_aml_alerts_transaction
    ON compliance.aml_alerts(transaction_id);
CREATE INDEX IF NOT EXISTS idx_aml_alerts_status
    ON compliance.aml_alerts(status);
CREATE INDEX IF NOT EXISTS idx_aml_alerts_created
    ON compliance.aml_alerts(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_aml_alerts_risk
    ON compliance.aml_alerts(risk_score DESC);

Query patterns:

  • Load alerts for a transaction: idx_aml_alerts_transaction
  • Open alert dashboard (sorted by risk): idx_aml_alerts_risk filtered by status
  • Alert list by recency: idx_aml_alerts_created

Notes:

  • Append-only. Status transitions via update (OPEN -> LINKED_TO_CASE or CLOSED_NO_ACTION).
  • transaction_id references ledger.transactions but no FK constraint is declared (cross-schema; services may split). Referential integrity is enforced at the application layer.

Table: aml_rules 

CREATE TABLE IF NOT EXISTS compliance.aml_rules (
    id              UUID          PRIMARY KEY,
    rule_set_id     VARCHAR(64)   NOT NULL,
    name            VARCHAR(255)  NOT NULL,
    priority        INT           NOT NULL,
    severity        VARCHAR(16)   NOT NULL,
    definition      JSONB         NOT NULL,
    status          VARCHAR(16)   NOT NULL DEFAULT 'DRAFT',
    version         INT           NOT NULL DEFAULT 1,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    created_by      VARCHAR(64)   NOT NULL,
    activated_at    TIMESTAMPTZ,

    CONSTRAINT ck_aml_rules_status   CHECK (status   IN ('DRAFT','ACTIVE','DEPRECATED')),
    CONSTRAINT ck_aml_rules_severity CHECK (severity IN ('LOW','MEDIUM','HIGH','CRITICAL'))
);

CREATE INDEX IF NOT EXISTS idx_aml_rules_rule_set_status
    ON compliance.aml_rules(rule_set_id, status, priority DESC);

Query patterns:

  • Load active rules for a rule set (ordered by priority): composite index (rule_set_id, status, priority DESC), p99 < 10ms with Redis cache

Table: compliance_cases 

CREATE TABLE IF NOT EXISTS compliance.compliance_cases (
    id                  UUID          PRIMARY KEY,
    alert_id            UUID,
    payment_id          UUID,
    kyc_session_id      UUID,
    status              VARCHAR(32)   NOT NULL DEFAULT 'OPEN',
    decision            VARCHAR(32),
    resolved_by         VARCHAR(64),
    resolved_at         TIMESTAMPTZ,
    resolution_reason   VARCHAR(2048),
    priority            VARCHAR(16)   NOT NULL DEFAULT 'MEDIUM',
    assigned_to         VARCHAR(64),
    ai_explanation      TEXT,
    draft_report        TEXT,
    version             BIGINT        NOT NULL DEFAULT 0,
    created_at          TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ   NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_cases_alert FOREIGN KEY (alert_id)
        REFERENCES compliance.aml_alerts(id) ON DELETE RESTRICT,
    CONSTRAINT ck_cases_status CHECK (status IN ('OPEN','IN_REVIEW','RESOLVED','ESCALATED')),
    CONSTRAINT ck_cases_decision CHECK (
        decision IS NULL OR decision IN ('APPROVED','REJECTED','ESCALATED_TO_REGULATOR')
    ),
    CONSTRAINT ck_cases_priority CHECK (priority IN ('LOW','MEDIUM','HIGH','CRITICAL')),
    CONSTRAINT ck_cases_at_least_one_link CHECK (
        alert_id IS NOT NULL OR payment_id IS NOT NULL OR kyc_session_id IS NOT NULL
    ),
    CONSTRAINT ck_cases_resolution CHECK (
        (status = 'RESOLVED' AND decision IS NOT NULL AND resolved_by IS NOT NULL)
        OR (status != 'RESOLVED' AND resolved_at IS NULL)
    )
);

CREATE INDEX IF NOT EXISTS idx_cases_status
    ON compliance.compliance_cases(status);
CREATE INDEX IF NOT EXISTS idx_cases_assigned
    ON compliance.compliance_cases(assigned_to) WHERE status = 'IN_REVIEW';
CREATE INDEX IF NOT EXISTS idx_cases_priority
    ON compliance.compliance_cases(priority, created_at DESC) WHERE status = 'OPEN';

CREATE OR REPLACE TRIGGER trg_compliance_cases_updated_at
    BEFORE UPDATE ON compliance.compliance_cases
    FOR EACH ROW EXECUTE FUNCTION shared.set_updated_at();

Query patterns:

  • Open case queue sorted by priority: idx_cases_priority (partial, OPEN only)
  • Cases assigned to investigator: idx_cases_assigned (partial, IN_REVIEW only)
  • Case by ID: PK lookup

Notes:

  • ck_cases_resolution enforces that a RESOLVED case always has a decision and resolved_by, and a non-resolved case cannot have resolved_at set.
  • payment_id and kyc_session_id are plain UUIDs (no cross-schema FK) for the same split-readiness reason as in the payments schema.

Table: case_notes 

CREATE TABLE IF NOT EXISTS compliance.case_notes (
    id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id     UUID        NOT NULL,
    author_id   VARCHAR(64) NOT NULL,
    body        TEXT        NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_case_notes_case FOREIGN KEY (case_id)
        REFERENCES compliance.compliance_cases(id) ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_case_notes_case
    ON compliance.case_notes(case_id, created_at);

Notes:

  • Append-only audit trail. No updates to note body after creation.

Table: processed_events (consumer deduplication)

CREATE TABLE IF NOT EXISTS compliance.processed_events (
    event_id        UUID        PRIMARY KEY,
    consumer_group  VARCHAR(64) NOT NULL,
    processed_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_compliance_processed_events_processed
    ON compliance.processed_events(processed_at);
-- Cleanup job: DELETE WHERE processed_at < NOW() - INTERVAL '30 days'

Notes:

  • Records Kafka/Redpanda events already consumed by compliance consumers to enable idempotent processing.
  • A mirror table exists in platform.processed_events for shared/centralized consumers.

Encryption summary for this schema

Table Column Method
kyc_sessions evidence_external_ref AES-256-GCM column, DEK from Vault
kyc_documents external_ref AES-256-GCM column, DEK from Vault

All other columns: filesystem-level encryption (LUKS). See Architecture-Security for DEK rotation schedule.

Liquibase changelogs for this schema

v0.1/
  030-compliance-kyc-sessions.xml
  031-compliance-kyc-documents.xml
  032-compliance-aml-alerts.xml
  033-compliance-aml-rules.xml
  034-compliance-cases.xml
  035-compliance-case-notes.xml
  036-compliance-processed-events.xml

Related

FinCore Engine - open-source fintech core (BSL 1.1 -> Apache 2.0). Repo - Roadmap - Vision

Product

Architecture

Decisions (ADR)

Engineering

Delivery

Risk and Ops

Clone this wiki locally