Database schemas organized by domain (orthogonal design).
Architecture: 01_ARCHITECTURE.md
Components: 02_COMPONENTS.md
Compliance: 03_COMPLIANCE.md
| # | Principle | Description |
|---|---|---|
| 1 | Domain Alignment | DB schema mirrors domain boundaries (Payment vs Compliance) |
| 2 | Orthogonal Storage | Each domain owns its tables, minimal cross-domain joins |
| 3 | FBO/Nostro Alignment | TigerBeetle accounts map to bank-level structures |
| 4 | Legal Entity First | Tenants assigned to licensed entities per region |
| 5 | Identity vs Policy | Tenant identity separate from pricing/limits |
| 6 | Compliance by Design | DHE, CESOP, AML/KYC built-in at schema level |
| 7 | Audit Everything | Immutable trail for regulatory requirements |
| 8 | Geo-Partitioning | Data residency enforced at database level |
┌─────────────────────────────────────────────────────────────────────────────┐
│ DATA RESPONSIBILITY │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ PostgreSQL 18 TigerBeetle │
│ ┌─────────────────────┐ ┌─────────────────────┐ │
│ │ • Business metadata │ │ • TENANT_WALLET │ ◄─ FBO sub-ledger │
│ │ • Legal entities │ │ • REGIONAL_SETTLE │ ◄─ Nostro balance │
│ │ • Tenant config │ │ • FEE_REVENUE │ │
│ │ • Compliance logs │ │ • FX_SETTLEMENT │ │
│ │ • Pricing policies │ │ • PENDING_IN/OUT │ │
│ │ • River job queue │ │ • Atomic guarantees │ │
│ │ • Geo-partitioned │ └─────────────────────┘ │
│ └─────────────────────┘ │
│ │
│ Redis 8 Kafka │
│ ┌─────────────────────┐ ┌─────────────────────┐ │
│ │ • FX rate locks │ │ • Transfer events │ │
│ │ • Rate limiting │ │ • Audit stream │ │
│ │ • Idempotency keys │ │ • Compliance events │ │
│ │ • Session cache │ │ • Webhook DLQ │ │
│ └─────────────────────┘ └─────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Following Unix Philosophy "Do one thing well":
| Document | Domain | Purpose |
|---|---|---|
| CORE.md | Payment | Transfer, wallet, recipient, quote tables |
| COMPLIANCE.md | Compliance | KYC/AML, sanctions, audit tables |
| LEDGER.md | Ledger | TigerBeetle schema, account structure |
| OPERATIONS.md | Operations | Jobs, config, multi-tenancy |
Principle: Each domain's DB schema lives with that domain's documentation.
Minimal, Well-Defined Interfaces:
-- Payment domain references Compliance via foreign key ONLY
-- No joins on compliance internals
CREATE TABLE transfers (
id UUID PRIMARY KEY,
tenant_id UUID NOT NULL,
compliance_check_id UUID, -- Reference only, no join
-- ... other fields
);
-- Compliance domain owns screening details
CREATE TABLE compliance_checks (
id UUID PRIMARY KEY,
transfer_id UUID NOT NULL,
risk_score INTEGER,
screening_result JSONB,
-- ... detailed screening data
);┌─────────────────────────────────────────────────────────────────────────────┐
│ TIGERBEETLE ↔ BANK ALIGNMENT │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ BANK LEVEL (Regulatory) TIGERBEETLE (Internal Ledger) │
│ ──────────────────────── ───────────────────────────── │
│ │
│ FBO EUR @ Deutsche Bank ───► SUM(TENANT_WALLET where currency=EUR) │
│ FBO GBP @ Barclays ───► SUM(TENANT_WALLET where currency=GBP) │
│ FBO IDR @ Bank Mandiri ───► SUM(TENANT_WALLET where currency=IDR) │
│ │
│ Nostro EUR @ Deutsche Bank ───► REGIONAL_SETTLEMENT_EU │
│ Nostro GBP @ Barclays ───► REGIONAL_SETTLEMENT_UK │
│ Nostro IDR @ Bank Mandiri ───► REGIONAL_SETTLEMENT_ID │
│ │
│ Kovra Revenue Account ───► FEE_REVENUE (per currency) │
│ │
│ (Internal only - no bank) ───► FX_SETTLEMENT │
│ (Internal only - no bank) ───► PENDING_INBOUND │
│ (Internal only - no bank) ───► PENDING_OUTBOUND │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Invariants:
SUM(TENANT_WALLET per currency)== FBO Bank StatementREGIONAL_SETTLEMENT per region== Nostro Bank Statement- Daily reconciliation validates these invariants
| Code | Type | Maps To | Owner | Purpose |
|---|---|---|---|---|
| 0x01 | TENANT_WALLET | FBO sub-ledger | Tenant (segregated) | Client funds per currency |
| 0x02 | FEE_REVENUE | Kovra operational | Kovra | Collected fees |
| 0x03 | FX_SETTLEMENT | FX Position | System | Platform's currency position |
| 0x04 | PENDING_INBOUND | Transit | System | Funds being collected |
| 0x05 | PENDING_OUTBOUND | Transit | System | Funds being disbursed |
| 0x06 | REGIONAL_SETTLEMENT | Nostro accounts | Kovra | Pre-funded settlement liquidity |
⚠️ TigerBeetle Constraint: All accounts in a single transfer MUST be in the same ledger. Cross-currency FX requires two separate transfer chains coordinated at application level.
-- Schema migrations tracked in _schema_versions table
CREATE TABLE _schema_versions (
version INTEGER PRIMARY KEY,
applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
description TEXT NOT NULL,
author TEXT NOT NULL
);
-- Current version check
SELECT MAX(version) FROM _schema_versions;PostgreSQL LIST partitioning by compliance_region:
-- Partition column
compliance_region TEXT GENERATED ALWAYS AS (
CASE
WHEN source_currency = 'IDR' OR dest_currency = 'IDR' THEN 'ID'
WHEN source_currency IN ('EUR','SEK','DKK') THEN 'EU'
WHEN source_currency = 'GBP' THEN 'UK'
END
) STORED;
-- Partitions
CREATE TABLE transfers_id PARTITION OF transfers FOR VALUES IN ('ID');
CREATE TABLE transfers_eu PARTITION OF transfers FOR VALUES IN ('EU');
CREATE TABLE transfers_uk PARTITION OF transfers FOR VALUES IN ('UK');- CORE.md - Payment domain tables
- COMPLIANCE.md - KYC/AML tables
- LEDGER.md - TigerBeetle detailed schema
- OPERATIONS.md - Jobs, config, audit
Prev: 01_ARCHITECTURE.md
Next: Choose domain-specific schema doc above
Last Updated: 2026-02-15