Compliance Database Schema

KYC/AML domain tables: sanctions, screening, audit, regulatory reporting.

Architecture: 01_ARCHITECTURE.md
Compliance Domain: 03_COMPLIANCE.md

Table Overview

Table	Purpose	Independence
sanctions_entities	Local OFAC/UN/EU/UK lists	Works 100% standalone
entity_verifications	KYC verification records	Local first, external optional
document_verifications	OCR/doc validation results	Local processing
risk_scores	ML/rule-based risk assessment	Fallback to rules
compliance_checks	Per-transfer screening results	Fast local check
compliance_logs	Detailed screening audit (geo-partitioned)	Immutable
dhe_records	Indonesia DHE compliance	Local calculation
cesop_reports	EU CESOP reporting	Batch generation
audit_trail	Immutable change log (geo-partitioned)	Always local

Design Principles

1. Local-First Storage

All compliance data stored locally - no external dependencies:

-- Sanctions lists replicated locally
SELECT COUNT(*) FROM sanctions_entities;  -- ~100K records, always available

-- External APIs = optional enhancement
-- Update via async batch jobs, never block payment flow

2. Fast Path Schema

Two-tier screening:

-- Tier 1: Fast local check (< 100ms) - ALWAYS WORKS
SELECT * FROM sanctions_entities 
WHERE metaphone(name) = metaphone('Search Name');

-- Tier 2: Enhanced screening (async, optional)
-- Results stored in compliance_logs, non-blocking

3. Orthogonal References

Minimal coupling to payment domain:

-- Payment domain owns transfers
-- Compliance domain owns screening
-- Linked by UUID only, no foreign key constraints
-- This allows independent evolution

CREATE TABLE compliance_checks (
    id UUID PRIMARY KEY,
    transfer_id UUID NOT NULL,  -- Reference only, not FK
    -- ... screening details
);

Sanctions Entities

Local copy of OFAC, UN, EU, UK sanctions lists.

CREATE TABLE sanctions_entities (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    -- Identity
    name                    VARCHAR(500) NOT NULL,
    name_metaphone          VARCHAR(100),  -- For fuzzy search
    aliases                 JSONB NOT NULL DEFAULT '[]',
    entity_type             VARCHAR(50) NOT NULL,  -- INDIVIDUAL, COMPANY, VESSEL
    
    -- Source
    sanctions_list          VARCHAR(50) NOT NULL,  -- OFAC, UN, EU, UK
    list_program            VARCHAR(100),  -- e.g., "SDN", "Consolidated"
    source_url              TEXT,
    
    -- Details
    country                 CHAR(2),
    addresses               JSONB,
    id_numbers              JSONB,  -- Passports, tax IDs, etc.
    birth_dates             JSONB,
    
    -- Metadata
    first_seen_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_updated_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    is_active               BOOLEAN NOT NULL DEFAULT true,
    
    -- Search optimization
    search_vector           TSVECTOR  -- For full-text search
);

-- Indexes for fast screening
CREATE INDEX idx_sanctions_name ON sanctions_entities(name);
CREATE INDEX idx_sanctions_metaphone ON sanctions_entities(name_metaphone);
CREATE INDEX idx_sanctions_list ON sanctions_entities(sanctions_list);
CREATE INDEX idx_sanctions_country ON sanctions_entities(country);
CREATE INDEX idx_sanctions_active ON sanctions_entities(is_active) WHERE is_active = true;
CREATE INDEX idx_sanctions_search ON sanctions_entities USING GIN(search_vector);

-- Update search vector trigger
CREATE OR REPLACE FUNCTION update_sanctions_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('simple', COALESCE(NEW.name, '')), 'A') ||
        setweight(to_tsvector('simple', COALESCE(array_to_string(NEW.aliases, ' '), '')), 'B');
    NEW.name_metaphone := metaphone(NEW.name, 10);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sanctions_search_update
    BEFORE INSERT OR UPDATE ON sanctions_entities
    FOR EACH ROW EXECUTE FUNCTION update_sanctions_search_vector();

Population:

Initial load: Full OFAC/UN/EU/UK lists
Updates: Daily async batch job (optional external API)
Fallback: Manual CSV import for air-gapped environments

Entity Verifications (KYC)

B2B entity verification records.

CREATE TYPE verification_status_enum AS ENUM (
    'pending', 'in_progress', 'verified', 'rejected', 'expired'
);

CREATE TYPE entity_type_enum AS ENUM (
    'company', 'partnership', 'sole_proprietor', 'ngo', 'government'
);

CREATE TABLE entity_verifications (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    tenant_id               UUID NOT NULL,  -- Reference only
    
    -- Entity Info
    entity_name             VARCHAR(200) NOT NULL,
    entity_type             entity_type_enum NOT NULL,
    registration_number     VARCHAR(100),
    tax_id                  VARCHAR(100),
    lei                     VARCHAR(20),  -- Legal Entity Identifier
    
    -- Address
    registered_address      JSONB NOT NULL,
    operating_address       JSONB,
    
    -- Country
    incorporation_country   CHAR(2) NOT NULL,
    operating_countries     CHAR(2)[],
    
    -- Verification Status
    status                  verification_status_enum NOT NULL DEFAULT 'pending',
    verified_at             TIMESTAMPTZ,
    verified_by             VARCHAR(100),  -- System or user
    expiry_date             DATE,  -- Annual re-verification
    
    -- Risk Assessment
    risk_level              VARCHAR(20) NOT NULL DEFAULT 'medium',  -- low/medium/high
    risk_factors            JSONB NOT NULL DEFAULT '[]',
    
    -- Compliance
    pep_identified          BOOLEAN NOT NULL DEFAULT false,
    sanctions_match         BOOLEAN NOT NULL DEFAULT false,
    adverse_media_found     BOOLEAN NOT NULL DEFAULT false,
    
    -- Metadata
    verification_method     VARCHAR(50),  -- manual, api, document
    external_reference      VARCHAR(100),  -- Optional external KYC provider
    
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_entity_verifications_tenant ON entity_verifications(tenant_id);
CREATE INDEX idx_entity_verifications_status ON entity_verifications(status);
CREATE INDEX idx_entity_verifications_lei ON entity_verifications(lei);
CREATE INDEX idx_entity_verifications_country ON entity_verifications(incorporation_country);
CREATE INDEX idx_entity_verifications_expiry ON entity_verifications(expiry_date) 
    WHERE status = 'verified';

Document Verifications

OCR and document validation records.

CREATE TYPE document_type_enum AS ENUM (
    'siup', 'tdp', 'certificate_of_incorporation', 'passport',
    'driving_license', 'utility_bill', 'bank_statement'
);

CREATE TABLE document_verifications (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    entity_verification_id  UUID NOT NULL,
    tenant_id               UUID NOT NULL,
    
    -- Document Info
    document_type           document_type_enum NOT NULL,
    document_number         VARCHAR(100),
    issuing_country         CHAR(2),
    issued_date             DATE,
    expiry_date             DATE,
    
    -- OCR Results
    extracted_name          VARCHAR(200),
    extracted_address       TEXT,
    extracted_number        VARCHAR(100),
    raw_ocr_text            TEXT,
    confidence_score        NUMERIC(3,2),  -- 0.00 to 1.00
    
    -- Verification
    status                  verification_status_enum NOT NULL DEFAULT 'pending',
    verified_at             TIMESTAMPTZ,
    verification_method     VARCHAR(50),  -- ocr, manual, api
    
    -- File Storage
    file_hash               VARCHAR(64),  -- SHA-256
    storage_path            TEXT,
    
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_doc_verifications_entity ON document_verifications(entity_verification_id);
CREATE INDEX idx_doc_verifications_type ON document_verifications(document_type);
CREATE INDEX idx_doc_verifications_status ON document_verifications(status);

UBOs (Ultimate Beneficial Owners)

Beneficial ownership records for B2B entities.

CREATE TABLE ubos (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    entity_verification_id  UUID NOT NULL,
    
    -- Personal Info
    full_name               VARCHAR(200) NOT NULL,
    date_of_birth           DATE,
    nationality             CHAR(2),
    
    -- Ownership
    ownership_percentage    NUMERIC(5,2) NOT NULL,
    ownership_type          VARCHAR(50),  -- direct, indirect, beneficial
    
    -- Verification
    verified                BOOLEAN NOT NULL DEFAULT false,
    is_pep                  BOOLEAN NOT NULL DEFAULT false,
    pep_details             JSONB,  -- If is_pep = true
    
    -- Documents
    id_document_type        VARCHAR(50),
    id_document_number      VARCHAR(100),
    
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ubos_entity ON ubos(entity_verification_id);
CREATE INDEX idx_ubos_pep ON ubos(is_pep) WHERE is_pep = true;

Risk Scores

Calculated risk assessment per entity/transfer.

CREATE TABLE risk_scores (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    entity_id               UUID,  -- Can be tenant or verification
    transfer_id             UUID,  -- NULL for entity-level scores
    
    -- Score
    score_value             NUMERIC(5,2) NOT NULL,  -- -1.00 to 1.00
    risk_level              VARCHAR(20) NOT NULL,  -- low/medium/high
    
    -- Factors
    factors                 JSONB NOT NULL,  -- {entity_age: 0.2, country_risk: 0.3, ...}
    model_version           VARCHAR(20),  -- ML model or 'rule-based'
    
    -- Explanation
    explanation             TEXT,  -- Human-readable explanation
    
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_risk_scores_entity ON risk_scores(entity_id);
CREATE INDEX idx_risk_scores_transfer ON risk_scores(transfer_id);
CREATE INDEX idx_risk_scores_level ON risk_scores(risk_level);

Compliance Checks

Per-transfer screening results.

CREATE TYPE screening_result_enum AS ENUM (
    'allowed', 'blocked', 'review_required'
);

CREATE TABLE compliance_checks (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    transfer_id             UUID NOT NULL,
    tenant_id               UUID NOT NULL,
    
    -- Screening Results
    result                  screening_result_enum NOT NULL,
    risk_score              INTEGER,  -- 0-100
    
    -- Sanctions Screening
    sanctions_hits          JSONB,  -- [{entity_id, name, score}, ...]
    sanctions_check_at      TIMESTAMPTZ,
    
    -- PEP Screening
    pep_hits                JSONB,
    pep_check_at            TIMESTAMPTZ,
    
    -- Velocity Checks
    velocity_violations     JSONB,  -- [daily_limit, amount, ...]
    
    -- Details
    raw_screening_data      JSONB,  -- Full response from screening engine
    screened_by             VARCHAR(50),  -- System or user ID
    
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_compliance_checks_transfer ON compliance_checks(transfer_id);
CREATE INDEX idx_compliance_checks_tenant ON compliance_checks(tenant_id);
CREATE INDEX idx_compliance_checks_result ON compliance_checks(result);
CREATE INDEX idx_compliance_checks_created ON compliance_checks(created_at);

Compliance Logs (Geo-Partitioned)

Detailed audit of all compliance activities.

CREATE TABLE compliance_logs (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    transfer_id             UUID NOT NULL,
    tenant_id               UUID NOT NULL,
    
    -- Activity
    activity_type           VARCHAR(50) NOT NULL,  -- screening, review, decision
    screening_type          VARCHAR(50),  -- sanctions, pep, velocity, etc.
    result                  VARCHAR(50) NOT NULL,
    
    -- Details
    risk_score              INTEGER,
    raw_response            JSONB,
    screened_by             VARCHAR(100),
    
    -- Geo-partitioning
    compliance_region       TEXT NOT NULL,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY LIST (compliance_region);

-- Partitions
CREATE TABLE compliance_logs_id PARTITION OF compliance_logs FOR VALUES IN ('ID');
CREATE TABLE compliance_logs_eu PARTITION OF compliance_logs FOR VALUES IN ('EU');
CREATE TABLE compliance_logs_uk PARTITION OF compliance_logs FOR VALUES IN ('UK');

-- Indexes
CREATE INDEX idx_compliance_logs_transfer ON compliance_logs(transfer_id);
CREATE INDEX idx_compliance_logs_tenant ON compliance_logs(tenant_id);
CREATE INDEX idx_compliance_logs_type ON compliance_logs(activity_type);
CREATE INDEX idx_compliance_logs_region ON compliance_logs(compliance_region);

DHE Records (Indonesia)

Devisa Hasil Ekspor compliance tracking.

CREATE TABLE dhe_records (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    tenant_id               UUID NOT NULL,
    legal_entity_id         UUID NOT NULL,
    
    -- Period
    period_year             INTEGER NOT NULL,
    period_month            INTEGER NOT NULL,
    
    -- Export Data
    total_export_usd        NUMERIC(20,2) NOT NULL DEFAULT 0,
    export_count            INTEGER NOT NULL DEFAULT 0,
    
    -- DHE Requirements
    dhe_required            BOOLEAN NOT NULL DEFAULT false,
    dhe_required_amount     NUMERIC(20,2),
    dhe_deposited           NUMERIC(20,2) DEFAULT 0,
    dhe_deadline            DATE,
    
    -- Compliance
    compliant               BOOLEAN NOT NULL DEFAULT false,
    reported_to_bi          BOOLEAN NOT NULL DEFAULT false,
    reported_at             TIMESTAMPTZ,
    
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    CONSTRAINT unique_dhe_period UNIQUE (tenant_id, period_year, period_month)
);

CREATE INDEX idx_dhe_tenant ON dhe_records(tenant_id);
CREATE INDEX idx_dhe_period ON dhe_records(period_year, period_month);
CREATE INDEX idx_dhe_compliant ON dhe_records(compliant) WHERE compliant = false;

CESOP Reports (EU)

Central Electronic System of Payment Information reports.

CREATE TABLE cesop_reports (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    legal_entity_id         UUID NOT NULL,
    
    -- Period
    period_year             INTEGER NOT NULL,
    period_quarter          INTEGER NOT NULL,
    
    -- Statistics
    total_payees            INTEGER NOT NULL DEFAULT 0,
    total_transactions      INTEGER NOT NULL DEFAULT 0,
    reportable_payees       INTEGER NOT NULL DEFAULT 0,
    
    -- Report Status
    report_status           VARCHAR(20) NOT NULL DEFAULT 'pending',
    submitted_at            TIMESTAMPTZ,
    submission_reference    VARCHAR(100),
    
    -- File storage
    report_file_path        TEXT,
    
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    CONSTRAINT unique_cesop_period UNIQUE (legal_entity_id, period_year, period_quarter)
);

CREATE INDEX idx_cesop_entity ON cesop_reports(legal_entity_id);
CREATE INDEX idx_cesop_status ON cesop_reports(report_status);

Audit Trail (Immutable, Geo-Partitioned)

Immutable record of all changes.

CREATE TABLE audit_trail (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    tenant_id               UUID,
    legal_entity_id         UUID,
    
    -- Resource
    resource_type           VARCHAR(50) NOT NULL,  -- transfer, tenant, etc.
    resource_id             UUID NOT NULL,
    action                  VARCHAR(50) NOT NULL,  -- create, update, delete
    
    -- Actor
    actor_type              VARCHAR(20) NOT NULL,  -- user, system, api
    actor_id                VARCHAR(100) NOT NULL,
    
    -- Changes
    before_state            JSONB,
    after_state             JSONB,
    
    -- Context
    ip_address              INET,
    request_id              VARCHAR(100),
    user_agent              TEXT,
    
    -- Geo-partitioning
    compliance_region       TEXT NOT NULL,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY LIST (compliance_region);

-- Partitions
CREATE TABLE audit_trail_id PARTITION OF audit_trail FOR VALUES IN ('ID');
CREATE TABLE audit_trail_eu PARTITION OF audit_trail FOR VALUES IN ('EU');
CREATE TABLE audit_trail_uk PARTITION OF audit_trail FOR VALUES IN ('UK');

-- Immutability Trigger
CREATE OR REPLACE FUNCTION prevent_audit_modification()
RETURNS TRIGGER AS $$
BEGIN
    RAISE EXCEPTION 'Audit trail records are immutable';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_immutable 
    BEFORE UPDATE OR DELETE ON audit_trail
    FOR EACH ROW EXECUTE FUNCTION prevent_audit_modification();

-- Indexes
CREATE INDEX idx_audit_resource ON audit_trail(resource_type, resource_id);
CREATE INDEX idx_audit_tenant ON audit_trail(tenant_id);
CREATE INDEX idx_audit_actor ON audit_trail(actor_type, actor_id);
CREATE INDEX idx_audit_created ON audit_trail(created_at);

Query Examples

Sanctions Screening (Fast Path)

-- Metaphone fuzzy match (fast, always works)
SELECT * FROM sanctions_entities 
WHERE name_metaphone = metaphone('John Doe', 10)
  AND is_active = true;

Risk Score Distribution

SELECT risk_level, COUNT(*) 
FROM entity_verifications 
WHERE status = 'verified'
GROUP BY risk_level;

Pending DHE

SELECT tenant_id, dhe_required_amount, dhe_deposited,
       (dhe_required_amount - dhe_deposited) as shortfall
FROM dhe_records 
WHERE dhe_required = true 
  AND compliant = false
  AND dhe_deadline < CURRENT_DATE + INTERVAL '30 days';

Compliance Activity

SELECT DATE(created_at), activity_type, COUNT(*)
FROM compliance_logs
WHERE compliance_region = 'ID'
  AND created_at > CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created_at), activity_type
ORDER BY DATE(created_at);

Data Retention

Table	Hot (PG)	Warm	Cold	Total
sanctions_entities	Current	-	Archive	Permanent
compliance_checks	90 days	1 year	7 years	7 years
compliance_logs	90 days	1 year	7 years	7 years
audit_trail	30 days	1 year	7 years	7 years
dhe_records	3 years	7 years	-	7 years
cesop_reports	5 years	10 years	-	10 years

Next: LEDGER.md for TigerBeetle schema
Prev: CORE.md for payment tables
Domain: 03_COMPLIANCE.md

Last Updated: 2026-02-15

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Compliance Database Schema

Table Overview

Design Principles

1. Local-First Storage

2. Fast Path Schema

3. Orthogonal References

Sanctions Entities

Entity Verifications (KYC)

Document Verifications

UBOs (Ultimate Beneficial Owners)

Risk Scores

Compliance Checks

Compliance Logs (Geo-Partitioned)

DHE Records (Indonesia)

CESOP Reports (EU)

Audit Trail (Immutable, Geo-Partitioned)

Query Examples

Sanctions Screening (Fast Path)

Risk Score Distribution

Pending DHE

Compliance Activity

Data Retention

FilesExpand file tree

COMPLIANCE.md

Latest commit

History

COMPLIANCE.md

File metadata and controls

Compliance Database Schema

Table Overview

Design Principles

1. Local-First Storage

2. Fast Path Schema

3. Orthogonal References

Sanctions Entities

Entity Verifications (KYC)

Document Verifications

UBOs (Ultimate Beneficial Owners)

Risk Scores

Compliance Checks

Compliance Logs (Geo-Partitioned)

DHE Records (Indonesia)

CESOP Reports (EU)

Audit Trail (Immutable, Geo-Partitioned)

Query Examples

Sanctions Screening (Fast Path)

Risk Score Distribution

Pending DHE

Compliance Activity

Data Retention