Data Model

Data Model

Complete database reference for FinCore Engine. All migrations are managed via Liquibase 5.0.2 in src/main/resources/db/changelog/. Companion to Domain-Model, Architecture-Overview.

---

Schemas

The database is organized into five bounded-context schemas plus a shared utilities schema.

Schema	Page	Contents
ledger	Data-Model-Ledger	accounts, transactions, entries (partitioned), account_balances, invariant trigger
payments	Data-Model-Payments	payments, payment_events, processed_webhooks
compliance	Data-Model-Compliance	kyc_sessions, kyc_documents, aml_alerts, aml_rules, compliance_cases, case_notes, processed_events
decision	Data-Model-Decision	decision_rules, rule_versions, decision_logs (partitioned), replay_runs
platform + shared	Data-Model-Platform	idempotency_keys, outbox_events, webhook_subscriptions, webhook_deliveries, audit_events (partitioned), processed_events, set_updated_at()

---

Conventions

• Database: PostgreSQL 17 (LTS).
• Schema-per-bounded-context in v0.1 (single instance, multiple schemas): ledger, payments, compliance, decision, platform.
• All identifiers: time-sortable 128-bit values (ULID-compatible), stored as UUID in PostgreSQL. Generated in application code (UUID.randomUUID() or a ULID generator). The application edge layer serializes them as prefixed strings (acc_, tx_, ent_, pay_, dec_) over Crockford base32 for API consumers; the prefix is never stored in the database. See ADR-0010.
• Timestamps: TIMESTAMPTZ, default NOW() for created_at; updated_at refreshed by a BEFORE UPDATE trigger (shared.set_updated_at()).
• Money: NUMERIC(38, 18) (signed for entries; unsigned for amounts and balances). Never float or double.
• Currency: CHAR(3) (ISO 4217).
• Booleans: BOOLEAN, never 0/1 integers.
• JSON: JSONB only (never JSON); validated at the application layer.
• Enums: VARCHAR(N) with a CHECK constraint and @Enumerated(EnumType.STRING) in JPA. Native Postgres enum types are not used (painful to migrate).
• Soft delete: deleted_at TIMESTAMPTZ NULL only where regulation requires history. Most entities are immutable.
• Foreign keys: ON DELETE RESTRICT everywhere. No cascade deletes in financial data.
• Cross-schema FK: deliberately absent. Services reference other schemas by plain UUID columns. This preserves the option to split schemas to separate databases without migration surgery.
• All DDL is idempotent (IF NOT EXISTS / CREATE OR REPLACE) so re-running a changelog on an existing database is safe.
• Naming:
  • Tables: snake_case, plural (accounts, transactions, entries)
  • Columns: snake_case
  • Indexes: idx_<table>_<columns>
  • FKs: fk_<from-table>__<to-table>
  • Unique: uq_<table>_<columns>
  • Check constraints: ck_<table>_<rule>

---

ERD overview

erDiagram
    ACCOUNTS ||--o{ ENTRIES : "has"
    TRANSACTIONS ||--o{ ENTRIES : "groups"
    TRANSACTIONS ||--o| TRANSACTIONS : "reverses (self)"
    ACCOUNTS ||--o{ ACCOUNT_BALANCES : "incremental row"
    PAYMENTS ||--o{ PAYMENT_EVENTS : "logs"
    PAYMENTS ||--o| TRANSACTIONS : "settles via"
    PAYMENTS ||--o| COMPLIANCE_CASES : "may flag"
    PAYMENTS ||--o| IDEMPOTENCY_KEYS : "tracked by"

    KYC_SESSIONS ||--o{ KYC_DOCUMENTS : "has"
    AML_ALERTS }o--|| TRANSACTIONS : "references"
    AML_ALERTS ||--o| COMPLIANCE_CASES : "may open"
    COMPLIANCE_CASES ||--o{ CASE_NOTES : "has"
    COMPLIANCE_CASES }o--o| KYC_SESSIONS : "may link"

    DECISION_RULES ||--o{ RULE_VERSIONS : "has versions"
    DECISION_LOGS }o--|| RULE_VERSIONS : "evaluated against"

    OUTBOX_EVENTS }o--|| ANY_AGGREGATE : "describes"
    PROCESSED_EVENTS }o--|| ANY_EVENT : "tracks"
    WEBHOOK_SUBSCRIPTIONS ||--o{ WEBHOOK_DELIVERIES : "delivers via"
    PROCESSED_WEBHOOKS }o--|| ANY_PROVIDER : "dedups"

Loading

---

Initialization (extensions, schemas)

-- v0.1/000-bootstrap.xml - runs first in every environment
CREATE SCHEMA IF NOT EXISTS shared;
CREATE SCHEMA IF NOT EXISTS ledger;
CREATE SCHEMA IF NOT EXISTS payments;
CREATE SCHEMA IF NOT EXISTS compliance;
CREATE SCHEMA IF NOT EXISTS decision;
CREATE SCHEMA IF NOT EXISTS platform;

CREATE EXTENSION IF NOT EXISTS pgcrypto;           -- gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS btree_gin;          -- composite GIN indexes
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- query performance monitoring
CREATE EXTENSION IF NOT EXISTS pg_partman;         -- partition management

---

Liquibase changelog organization

src/main/resources/db/changelog/
├── db.changelog-master.xml         -- includes all changelogs in order
└── v0.1/
    ├── 000-bootstrap.xml           -- schemas, extensions
    ├── 001-shared-functions.xml    -- shared.set_updated_at() trigger function
    ├── 010-ledger-accounts.xml
    ├── 011-ledger-transactions.xml
    ├── 012-ledger-entries.xml      -- partitioned table + initial quarterly partitions
    ├── 013-ledger-invariant-trigger.xml
    ├── 014-ledger-balances.xml     -- account_balances real table (ADR-0011)
    ├── 020-payments-payments.xml
    ├── 021-payments-events.xml
    ├── 022-payments-processed-webhooks.xml
    ├── 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
    ├── 040-decision-rules.xml
    ├── 041-decision-rule-versions.xml
    ├── 042-decision-logs.xml       -- partitioned table + initial monthly partitions
    ├── 043-decision-replay-runs.xml
    ├── 050-platform-idempotency.xml
    ├── 051-platform-outbox.xml
    ├── 052-platform-webhook-subscriptions.xml
    ├── 053-platform-webhook-deliveries.xml
    ├── 054-platform-audit-events.xml
    └── 055-platform-processed-events.xml

Every migration file is append-only. Never edit a committed migration. To correct a mistake: write a new migration that makes the corrective change.

If you have manually edited an already-applied changeset (e.g. during local development), run liquibase --clear-checksums before the next migration to reset the stored hash.

---

Index strategy summary

Pattern	Index type
Primary key lookup	B-tree (implicit)
Foreign key column	B-tree (always added explicitly; PostgreSQL does not auto-index FKs)
Status / enum filter + time sort	B-tree on (status, created_at DESC)
Pagination by date with cursor tiebreaker	B-tree on (created_at DESC, id DESC)
Partial (e.g. PENDING rows only)	B-tree with WHERE clause
JSONB containment search	GIN
Array containment (e.g. webhook event types)	GIN
Time-series large tables	PARTITION BY RANGE (created_at) - quarterly for entries, monthly for decision_logs and audit_events

---

Query performance budgets

Query	p99 target
Get account by ID	< 1ms
Get balance (incremental row)	< 5ms
List entries (50 items, single account, cursor pagination)	< 30ms
Post transaction (entries + balance update + outbox + invariant trigger)	< 50ms DB time
Get payment by ID	< 5ms
List payments (50 items, status filter)	< 50ms
AML rule lookup (active rules for ruleset)	< 10ms with Redis cache
Decision log insert	< 5ms
Outbox dispatcher batch SELECT (100 rows, SKIP LOCKED)	< 20ms

These targets are DB-time only. Application-level overhead adds 5-20ms typical.

---

Backup and recovery

See Architecture-Resilience for the full disaster recovery runbook.

• WAL archiving to S3 (continuous)
• Logical replication to standby (synchronous for primary, async for read replicas)
• Nightly base backup
• PITR window: 35 days
• Quarterly DR drills (verify restore + ledger invariant)

---

Encryption-at-rest summary

Table	Column	Encryption	DEK source
compliance.kyc_sessions	evidence_*	AES-256-GCM column	Vault
compliance.kyc_documents	external_ref	AES-256-GCM column	Vault
platform.webhook_subscriptions	secret_encrypted	AES-256-GCM column	Vault
All other tables	-	Filesystem-level (LUKS)	KMS

DEK rotation: quarterly. KEK rotation: annually (separate runbook).

---

What is deliberately absent in v0.1

• No tenants table (single-tenant per deployment)
• No users table (managed by external IdP - Keycloak)
• No accounts.balance column (balance is in account_balances, updated incrementally)
• No transactions.is_deleted flag (immutable journal)
• No native Postgres ENUM types (use VARCHAR + CHECK)
• No triggers on payments or cases (logic in application layer; only invariant trigger on ledger)
• No cross-schema foreign keys (services may split to separate databases)

---

Related reading

• Data-Model-Ledger - accounts, transactions, entries, balances
• Data-Model-Payments - payments, payment_events, webhooks
• Data-Model-Compliance - KYC, AML, compliance cases
• Data-Model-Decision - decision rules, logs, replay
• Data-Model-Platform - idempotency, outbox, audit, webhooks
• Domain-Model - domain aggregates that map to these tables
• Architecture-Overview - how services own their schemas
• Architecture-Security - encryption at rest, audit
• ADR-0007 - double-entry invariant enforcement
• ADR-0010 - API identifier format
• ADR-0011 - balance strategy