Layer 6 — Data: Aurora PostgreSQL + Object Storage

One Aurora cluster for case data and Temporal persistence; S3 for documents; Redis for session and agent memory.

---

Table of Contents

1. 9.1 Database Strategy
2. 9.2 Core Data Model (DDL)
   1. Audit Trail Integrity
3. 9.3 Document Storage (Amazon S3)
4. 9.4 Redis — Session and Agent Memory
5. 9.5 The Core Banking Ledger — Design Principles
6. 9.6 Transactional Outbox & Change Data Capture
7. 9.7 Chart of Accounts & Deposit Taxonomy
8. 9.8 Party Model — Entities, Beneficial Owners & Signers
9. 9.9 Balance Types & Fund Holds
10. 9.10 Client Entitlements & Approval Authority

---

9.1 Database Strategy

Amazon Aurora PostgreSQL serves three roles:

1. Application database — all business data (cases, companies, users, products, audit)
2. Temporal persistence backend — Temporal Server uses a PostgreSQL schema for workflow history, task queues, and visibility
3. AI embedding store — pgvector extension stores document embeddings for AI Assistant’s semantic search over the regulation reference corpus

Two separate Aurora clusters (or separate databases on one cluster for cost efficiency):

• platform-db — application data + audit
• temporal-db — Temporal Server persistence (Temporal manages this schema; separate cluster recommended for isolation)

---

9.2 Core Data Model (DDL)

-- Case (one per onboarding request)
CREATE TABLE cases (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_number     VARCHAR(40) UNIQUE NOT NULL,  -- e.g., CASE-2026-004
    client_segment  VARCHAR(20) NOT NULL,          -- SMALL | MEDIUM | ENTERPRISE
    workflow_run_id VARCHAR(200),                   -- Temporal run ID
    stage           VARCHAR(60) NOT NULL,
    state           VARCHAR(20) NOT NULL,           -- OPEN | COMPLIANCE | PROVISIONING | COMPLETE | CANCELLED
    opened_by       UUID REFERENCES users(id),
    opened_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at    TIMESTAMPTZ,
    sla_target_at   TIMESTAMPTZ,
    sla_status      VARCHAR(10)                     -- ON_TRACK | AT_RISK | BREACHED
);

-- Company (the client being onboarded)
CREATE TABLE companies (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id         UUID REFERENCES cases(id),
    legal_name      VARCHAR(200) NOT NULL,
    tax_id          VARCHAR(20),
    core_banking_id VARCHAR(64),                   -- ID in Core Banking System
    address         JSONB,
    related_entities JSONB                         -- parent/subsidiary structure (Enterprise)
);

-- Selected products per case
CREATE TABLE case_products (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id      UUID REFERENCES cases(id),
    product_code VARCHAR(40) NOT NULL,
    action       VARCHAR(10) NOT NULL,            -- ADD | MODIFY | REMOVE
    status       VARCHAR(20) NOT NULL,            -- PENDING | IN_PROGRESS | COMPLETE | FAILED
    accounts     UUID[],                          -- linked account IDs
    provisioned_at TIMESTAMPTZ
);

-- Human tasks (surfaces in React queue panels)
CREATE TABLE human_tasks (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id         UUID REFERENCES cases(id),
    task_type       VARCHAR(60) NOT NULL,           -- ORDER_APPROVAL | DOCUMENT_REVIEW | BSA_ATTESTATION | ...
    assigned_group  VARCHAR(60),                    -- maps to React role-based queue
    assigned_to     UUID REFERENCES users(id),
    status          VARCHAR(20) NOT NULL DEFAULT 'OPEN',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    due_at          TIMESTAMPTZ,
    completed_at    TIMESTAMPTZ,
    completed_by    UUID REFERENCES users(id),
    decision        VARCHAR(20),                   -- APPROVED | REJECTED | ATTESTED | SKIPPED
    comments        TEXT,
    ai_assessment   JSONB                          -- AI Assistant pre-assessment (doc review, AML)
);

-- Immutable audit trail
CREATE TABLE audit_events (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id     UUID REFERENCES cases(id),
    event_type  VARCHAR(80) NOT NULL,
    actor_id    UUID,
    actor_type  VARCHAR(20),                      -- USER | SYSTEM | AI_AGENT
    actor_name  VARCHAR(200),
    event_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    payload     JSONB NOT NULL,
    ip_address  INET,
    session_id  VARCHAR(100)
) WITH (fillfactor = 90);  -- append-only pattern; no updates

-- AI suggestions (pre-fills surfaced in UI)
CREATE TABLE ai_suggestions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id         UUID REFERENCES cases(id),
    field_path      VARCHAR(200) NOT NULL,         -- e.g., "company.taxId"
    suggested_value TEXT,
    confidence      NUMERIC(4,3),
    source          VARCHAR(80),                   -- CRM_PREFILL | DOCUMENT_EXTRACT | RULE_BASED
    status          VARCHAR(20) DEFAULT 'PENDING', -- PENDING | ACCEPTED | REJECTED
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

Audit Trail Integrity

The audit_events table uses PostgreSQL’s append-only pattern. The application database role has no UPDATE or DELETE privileges on this table. A separate compliance role has SELECT only. This means:

• Every case action — human or AI — is permanently recorded
• No record can be altered after the fact, even by the application
• Retention: 7-year lifecycle policy for AML records (regulatory requirement)

---

9.3 Document Storage (Amazon S3)

All onboarding documents (AML forms, signature cards, agreements, ID documents) are stored in S3:

s3://platform-documents/
├── cases/
│   └── {case_id}/
│       ├── aml-questionnaire.pdf
│       ├── signature-card.pdf
│       ├── board-resolution.pdf
│       └── ...
└── templates/
    ├── credential-delivery-email.html
    └── onboarding-completion-letter.html

Upload path: React UI requests a pre-signed S3 PUT URL from document-service → file uploaded directly from browser to S3 (never transits the API server)

Read path: document-service issues a pre-signed GET URL (15-minute TTL) for in-browser viewing

Processing path: S3 event → SQS → DocumentReviewAgent in the agent service

Retention: 7-year lifecycle policy (regulatory requirement for AML records)

---

9.4 Redis — Session and Agent Memory

Amazon ElastiCache (Redis) serves two distinct roles:

Role	Key Pattern	TTL
Operator session state	session:{sessionId}	8 hours (workday)
AI Assistant working memory	agent:context:{caseId}:{userId}	4 hours
Distributed lock	lock:workflow:{caseId}	30s (auto-expire)

The AI Assistant working memory allows the conversational AI to maintain context across multiple turns in the same operator session — without persisting conversation history to Aurora. When the session expires, the context is cleared.

---

9.5 The Core Banking Ledger — Design Principles

The Core Banking Ledger is the platform’s system of record for accounts, balances, and money movement — the LOB-agnostic foundation every line of business (treasury today; corporate trust, custody, and others later) posts against. Because it is the source of truth for money, its design is governed by three non-negotiable principles.

1 · Immutable double-entry, append-only journal

Balances are never updated in place. Every financial movement appends two immutable rows — an equal debit and credit — to a journal, and an account’s balance is derived by summing that journal, not stored in a mutable column.

• Balancing invariant: for every posting, Σ debits = Σ credits. A set of legs that does not balance is rejected, never persisted.
• Source of truth: the append-only journal_entries log. Current balances are a materialized view (or on-demand sum) over that log — reproducible, and auditable back to the originating entry.
• This is the same philosophy as the platform’s append-only audit_events table (§9.2), now applied to money: the record of what happened can be read, never rewritten.

-- The account: identity, chart-of-accounts classification, and a version for
-- optimistic locking — NOT a balance column (balances are derived)
CREATE TABLE accounts (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_no   VARCHAR(34) UNIQUE NOT NULL,
    account_type VARCHAR(16) NOT NULL,        -- ASSET | LIABILITY | EQUITY | REVENUE | EXPENSE (sets the normal balance side)
    currency     CHAR(3) NOT NULL,
    status       VARCHAR(20) NOT NULL,        -- OPEN | FROZEN | CLOSED
    lock_version BIGINT NOT NULL DEFAULT 0    -- optimistic concurrency (principle 2)
);

-- Transaction header: one row per logical money movement, carrying the
-- idempotency key and the originating Temporal workflow execution
CREATE TABLE ledger_transactions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    idempotency_key VARCHAR(200) UNIQUE NOT NULL,  -- data-layer exactly-once (principle 3)
    workflow_id     VARCHAR(128),                  -- originating Temporal workflow execution
    description     TEXT NOT NULL,
    posted_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- The immutable journal: one transaction = >= 2 balanced legs
CREATE TABLE journal_entries (
    id             BIGSERIAL PRIMARY KEY,
    transaction_id UUID NOT NULL REFERENCES ledger_transactions(id),
    account_id     UUID NOT NULL REFERENCES accounts(id),
    direction      CHAR(1) NOT NULL,          -- 'D' debit | 'C' credit
    amount         NUMERIC(20,4) NOT NULL CHECK (amount > 0),
    currency       CHAR(3) NOT NULL,
    entry_sequence INT NOT NULL,              -- leg order within the transaction
    posted_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);  -- append-only: the application role holds no UPDATE/DELETE on this table

Modeling notes. account_type is the chart-of-accounts classification that fixes each account’s normal balance side — assets and expenses are debit-normal; liabilities, equity, and revenue are credit-normal — so a raw balance is interpreted correctly. Each leg is recorded as an explicit direction plus a positive amount; this is equivalent to the signed-amount convention (debits +, credits −, Σ = 0) — the same invariant expressed as a column rather than a sign. The ledger_transactions header carries the idempotency_key — a UNIQUE constraint that makes a duplicate posting impossible at the data layer — and the workflow_id, so every balanced posting traces back to the exact workflow execution that produced it (complementing the API-level replay in principle 3 below).

2 · Concurrency control — optimistic by default, pessimistic where sequencing is strict

When multiple services or Temporal workflows post entries concurrently, the double-entry invariants must hold under contention.

• Optimistic locking (default, high-throughput): the lock_version on the account. Reads take no lock; at write time, if the version changed since the read, the write is rejected and retried with fresh data. Best when conflicts are rare.
• Pessimistic locking (strict safety): SELECT … FOR UPDATE takes a row-level lock so no other transaction can touch the account until this one commits. Reserved for high-stakes operations that demand strict sequencing.

3 · Idempotency by default

Network timeouts and Temporal’s at-least-once retries make duplicate requests inevitable — so every state-mutating ledger call must be safe to receive twice.

• Clients send an Idempotency-Key header on every mutating request.
• The server records the key alongside the original result; a repeat carrying the same key replays the stored response instead of posting the transaction again.

CREATE TABLE idempotency_keys (
    key           VARCHAR(200) PRIMARY KEY,
    request_hash  VARCHAR(64) NOT NULL,       -- rejects key reuse with a different body
    response_body JSONB NOT NULL,
    status_code   INT NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

Ledger invariant: money is created only by a balanced, idempotent posting to the append-only journal; balances are derived, never directly written.

---

9.6 Transactional Outbox & Change Data Capture

A ledger posting is rarely the only thing that must happen when money moves — downstream consumers (read-model projections, the notification service, analytics, fraud scoring) need to learn about TransferInitiated or AccountDebited too. The naïve approach writes the ledger row and then publishes an event to the message broker as a second step. That is the classic dual-write problem: if the process dies between the commit and the publish, the ledger says one thing and the event stream says another, and the two never reconcile.

The transactional outbox removes the second write entirely. In the same database transaction that appends the journal entries (§9.5 above), the application also inserts the domain event into an outbox_events table. Either both commit or neither does — the event can never diverge from the ledger state that produced it.

-- Written in the SAME transaction as the journal_entries it describes
CREATE TABLE outbox_events (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_type  VARCHAR(64) NOT NULL,          -- e.g., Account | Transfer
    aggregate_id    VARCHAR(64) NOT NULL,          -- the entity the event concerns
    event_type      VARCHAR(64) NOT NULL,          -- e.g., AccountDebited | TransferInitiated
    payload         JSONB NOT NULL,
    idempotency_key VARCHAR(64) UNIQUE NOT NULL,   -- lets the consumer dedupe
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    processed_at    TIMESTAMPTZ                     -- NULL until the relay publishes it
);
-- Partial index: the relay only ever scans the unprocessed tail
CREATE INDEX idx_outbox_unprocessed ON outbox_events (created_at) WHERE processed_at IS NULL;

A relay then moves committed rows to the broker (Kafka / EventBridge) and stamps processed_at. Two implementations, one contract:

• CDC (preferred at scale): a Debezium connector tails the PostgreSQL write-ahead log and streams new outbox_events rows with no polling load on the database.
• Polling publisher (simpler): a lightweight scheduled worker selects WHERE processed_at IS NULL, publishes, and marks rows done — adequate for the reference build.

How this coexists with Temporal — and why it is not redundant. Temporal already guarantees that a workflow runs to completion despite failures, so it is fair to ask why the outbox is needed at all. The distinction is scope. Temporal guarantees the orchestration; the outbox guarantees that the ledger service’s own domain events reach other systems atomically with its database commit. A workflow activity that writes the ledger and publishes to Kafka in two steps still has the dual-write gap inside that activity — the outbox closes exactly that gap. The two are complementary: Temporal owns “the process finished”; the outbox owns “everyone who needed to hear about the commit did, exactly once.” Consumers dedupe on idempotency_key, completing the exactly-once chain that begins at the ledger (§9.5).

---

9.7 Chart of Accounts & Deposit Taxonomy

The ledger (§9.5 above) fixes how money is recorded; the chart of accounts fixes what kinds of accounts money moves between. Each account carries a GAAP classification (account_type, which sets its normal balance side) and an operational subtype that drives product behaviour and compliance gating. The internal system / transit accounts — the control accounts that make deferred and reversible settlement safe — are flagged so they never surface as a client-facing balance.

-- Extends the accounts table in §9.5 with operational classification
ALTER TABLE accounts
    ADD COLUMN account_subtype            VARCHAR(24) NOT NULL,  -- COMMERCIAL_DDA | MONEY_MARKET | ESCROW_TRUST | SUSPENSE_TRANSIT | ACH_CLEARING | FEE_REVENUE
    ADD COLUMN is_internal_system_account BOOLEAN NOT NULL DEFAULT FALSE;  -- true for transit / suspense control accounts

Subtype	Normal balance (account_type)	Operational role	Lifecycle & gating
COMMERCIAL_DDA	Liability	Operating checking	High-velocity transactional flow (wire · ACH · RTP); the standard client deposit liability
MONEY_MARKET	Liability	Liquidity / treasury	Interest-bearing; automated sweep thresholds
ESCROW_TRUST	Liability	Fiduciary custody	Segregated client funds (escrow collateral, trust custody); a compliance gate check is required before any debit
SUSPENSE_TRANSIT	Liability — settlement suspense · internal	Real-time outbound hold	Holds debited client funds pending synchronous wire confirmation; reversible via Saga (Temporal)
ACH_CLEARING	Liability — settlement suspense · internal	Batch clearing hold	Keeps funds in PENDING_SETTLEMENT across the T+1/T+2 return window (Payment Rails)
FEE_REVENUE	Revenue	Income recognition	Accrues recurring custody and transfer-processing fees

Why the transit accounts matter. SUSPENSE_TRANSIT and ACH_CLEARING are the concrete hold / clearing accounts the settlement-state machine (Payment Rails) posts against. A debit never leaves a client’s COMMERCIAL_DDA straight to the outside world: it first moves to a transit account, and only a confirmed settlement — or a compensating reversal (Temporal) — clears it. A worked flow: Debit COMMERCIAL_DDA, Credit SUSPENSE_TRANSIT on pre-flight hold; then Debit SUSPENSE_TRANSIT, Credit the bank’s reserve asset on settlement, or reverse the hold on rejection. This is what lets the ledger represent “funds committed but not yet final” as a real, balanced position rather than a flag — and is_internal_system_account keeps those balances out of any client-facing total.

Chart-of-accounts invariant: every account has a GAAP account_type (normal balance side) and an operational account_subtype; money leaving a client liability account transits a flagged internal control account before it settles externally, so a reversible movement is always a balanced ledger position, never an unbacked flag.

---

9.8 Party Model — Entities, Beneficial Owners & Signers

In commercial banking the customer is a legal entity, not a person — an LLC, corporation, or fund with an ownership structure and named individuals authorized to act. Modelling that structure as first-class rows (not a JSONB blob) is what lets the compliance gate screen each party independently and gate activation on the result.

-- Beneficial owners — natural persons owning >= 25% (FinCEN CDD ownership prong),
-- plus the single control-prong individual. Each is screened independently.
CREATE TABLE beneficial_owners (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id        UUID NOT NULL REFERENCES companies(id),
    full_name         VARCHAR(200) NOT NULL,
    ownership_pct     NUMERIC(5,2) NOT NULL CHECK (ownership_pct >= 0 AND ownership_pct <= 100),
    is_control_person BOOLEAN NOT NULL DEFAULT FALSE,
    cip_status        VARCHAR(16) NOT NULL DEFAULT 'PENDING',  -- PENDING | CLEARED | REVIEW | BLOCKED
    screening_ref     VARCHAR(64)                              -- sanctions / OFAC screening correlation id
);

-- Authorized signers / control persons — who may act on the account, and in what capacity
CREATE TABLE authorized_signers (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id        UUID NOT NULL REFERENCES companies(id),
    full_name         VARCHAR(200) NOT NULL,
    title             VARCHAR(120),                            -- e.g., Treasurer, CFO
    cip_status        VARCHAR(16) NOT NULL DEFAULT 'PENDING'
);

-- Onboarding cannot complete until KYC clears for every party on the entity
ALTER TABLE companies
    ADD COLUMN kyc_status VARCHAR(16) NOT NULL DEFAULT 'PENDING_KYC';  -- PENDING_KYC | IN_REVIEW | ACTIVE | REJECTED

How activation is gated. A company moves PENDING_KYC → ACTIVE only when every beneficial_owners and authorized_signers row reaches cip_status = CLEARED and an authorized officer has signed the master agreement. The screening runs inside the Compliance Gate child workflow: the agent layer pre-fills and flags each party’s screening result, and — consistent with the BSA/AML invariant — a human compliance officer attests; no agent clears a party on its own. Accounts (§9.5 above) for the entity are created only after activation, so money can never post against a party that has not cleared.

Party invariant: the client is a legal entity with independently screened beneficial owners (≥ 25%) and authorized signers; no account activates, and no funds move, until every party clears CIP / sanctions screening and an officer signs.

---

9.9 Balance Types & Fund Holds

A single “balance” is not enough to run payments correctly. The amount an account contains and the amount it can spend right now differ whenever a payment is in flight. Anchita distinguishes them explicitly and models the difference — a hold — as a first-class row rather than a mutable number.

-- A hold earmarks funds against AVAILABLE balance without moving money in the
-- double-entry ledger (§9.5 above). Only a balanced posting moves the ledger
-- balance; a hold only reduces what is spendable.
CREATE TABLE balance_holds (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id   UUID NOT NULL REFERENCES accounts(id),
    amount       NUMERIC(20,4) NOT NULL CHECK (amount > 0),
    currency     CHAR(3) NOT NULL,
    reason       VARCHAR(24) NOT NULL,        -- PENDING_TRANSFER | AWAITING_APPROVAL | CARD_AUTH | REGULATORY
    status       VARCHAR(12) NOT NULL DEFAULT 'ACTIVE',  -- ACTIVE | CAPTURED | RELEASED
    workflow_id  VARCHAR(128),                -- the payment workflow that placed it
    placed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    released_at  TIMESTAMPTZ
);
CREATE INDEX idx_active_holds ON balance_holds (account_id) WHERE status = 'ACTIVE';

Two balances, both derived — never stored in a mutable column:

ledger balance    = Σ journal_entries for the account         (§9.5 above)
available balance = ledger balance − Σ ACTIVE balance_holds

Lifecycle, and why a hold is not a ledger posting. When a payment is submitted (see Payment Rails — Dual Control) a hold is placed (AWAITING_APPROVAL / PENDING_TRANSFER): available balance drops, ledger balance does not — the money is still in COMMERCIAL_DDA, just not spendable twice. On release the hold is CAPTURED and now the real balanced posting moves funds to a transit account (COMMERCIAL_DDA → SUSPENSE_TRANSIT, §9.7 above); ledger balance drops to match. On rejection or approval-timeout the hold is RELEASED with no ledger movement at all. This cleanly separates “earmarked” (a hold) from “moved” (a posting), and is what prevents the classic double-spend where two concurrent payments each see the full balance.

Balance invariant: available balance = derived ledger balance − active holds; a hold earmarks funds but moves no money — only a balanced, append-only posting (§9.5 above) changes the ledger balance.

---

9.10 Client Entitlements & Approval Authority

Provisioning a product turns a capability on for the company; entitlements decide which signer may do what, up to what amount. This is the authorization model behind the dual-control decision (see Payment Rails — Dual Control), and it is where “service provisioning” becomes real rather than a feature flag.

-- What each authorized signer (§9.8 above) may do, and up to what amount.
-- Drives both product access and the dual-control approval decision.
CREATE TABLE entitlements (
    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id     UUID NOT NULL REFERENCES companies(id),
    signer_id      UUID NOT NULL REFERENCES authorized_signers(id),
    product_code   VARCHAR(40) NOT NULL,     -- scope: WIRE_DOMESTIC | ACH_ORIGINATION | ...
    capability     VARCHAR(12) NOT NULL,     -- INITIATE | APPROVE | VIEW
    approval_limit NUMERIC(20,4),            -- max amount for this capability (NULL = not permitted to authorize an amount)
    currency       CHAR(3) NOT NULL DEFAULT 'USD',
    UNIQUE (signer_id, product_code, capability)
);

• Role separation is data, not code. INITIATE, APPROVE, and VIEW are distinct capabilities a signer holds per product; the maker-checker rule is simply “the approver holds APPROVE for this product, with a limit ≥ the amount, and is not the initiator.”
• Limits are per signer, per product. A treasurer may initiate any wire but approve only up to their approval_limit; amounts above it escalate to a signer with a higher limit — the mechanism behind tiered approval.
• Segregation of duties is enforced at authorization time by comparing signer_ids and checking the entitlement row, so no scattered conditionals decide who can release money — the same “one explicit record, not conditionals” discipline as segment routing.

Entitlement invariant: authority to move money is an explicit (signer, product, capability, limit) grant, screened at onboarding and checked at payment time; no signer both initiates and approves the same instruction.