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

---

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.