SD08 — Customer App & Back Office data model

Database: app (Neon Postgres, ap-southeast-2) Repo: bank-app ADRs: ADR-001 (Postgres as OLTP), ADR-024 (Neon hosting; partially superseded by ADR-052 — naming convention correction only; all other decisions remain in force), ADR-026 (Cognito passwordless auth), ADR-028 (S3 document storage), ADR-020 (automation rules), ADR-048 (database-enforced invariants) Schema owner: SD08 Customer App & Back Office Platform

This is the authoritative schema reference for all modules in bank-app. Use exact column names and types from this document — do not invent alternatives.

The app schema is the primary write domain for MOD-049 (consent capture), MOD-050 (disclosure enforcement), MOD-051 (financial automation rules), MOD-052 (role-scoped data access), MOD-053 (case and complaint management), MOD-054 (call recording), MOD-064 (operations work queue), MOD-068 (authentication and session management), MOD-069 (app shell), MOD-070 (transaction history), MOD-071 (payment initiation), MOD-072 (customer profile), MOD-073 (document vault), MOD-074 (back-office customer 360), MOD-077 (account dashboard), MOD-078 (card and account controls), MOD-083 (agent assist), and MOD-127 (product configuration panel). SD08 is a consumer of data from SD01–SD05 via their APIs; it writes primarily to the app schema for UX state, consent, compliance, and case management.

---

Schema: app

app.customer_sessions

Authentication sessions for customers using the mobile and web app, managed by MOD-068 using AWS Cognito passwordless auth. One row per active session; expired sessions are retained for security audit.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
user_id	uuid	NOT NULL REFERENCES access.user_identities(user_id)	Authenticated user — FK to access.user_identities
cognito_sub	text	NOT NULL	AWS Cognito subject identifier
session_token_hash	text	NOT NULL	SHA256 of the session token; never store the token itself
device_id	text		Device fingerprint from MOD-024
device_type	text	CHECK (device_type IN ('IOS','ANDROID','WEB','DESKTOP'))
ip_address	text		Session originating IP
jurisdiction	char(2)	NOT NULL
auth_method	text	NOT NULL CHECK (auth_method IN ('BIOMETRIC','PASSKEY','SMS_OTP','EMAIL_OTP','PIN'))
session_status	text	NOT NULL CHECK (session_status IN ('ACTIVE','EXPIRED','REVOKED','STEP_UP_REQUIRED'))
mfa_completed	boolean	NOT NULL DEFAULT false	Step-up auth completed
initiated_at	timestamptz	NOT NULL DEFAULT now()
last_active_at	timestamptz	NOT NULL DEFAULT now()
expires_at	timestamptz	NOT NULL CHECK (expires_at > initiated_at)
revoked_at	timestamptz
revocation_reason	text
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_customer_sessions_user_id_active on (user_id) WHERE session_status = 'ACTIVE' - idx_customer_sessions_expires_at on (expires_at) WHERE session_status = 'ACTIVE' - idx_customer_sessions_device_id on (device_id) WHERE device_id IS NOT NULL - idx_customer_sessions_token_hash on (session_token_hash)

Notes: Session tokens are never stored. Only the SHA256 hash is persisted. Session validation reads this hash to verify the presented token.

---

app.consents

Customer consent records captured by MOD-049. Each row represents a specific consent grant or withdrawal for a defined purpose. Required for PRI-001 (privacy policy) and PAY-010 (Open Banking consent).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	cross-domain ref to SD02 party.parties(party_id)
consent_type	text	NOT NULL CHECK (consent_type IN ('PRIVACY_POLICY','TERMS_CONDITIONS','MARKETING_EMAIL','MARKETING_SMS','DATA_SHARING','OPEN_BANKING','CREDIT_CHECK','PRODUCT_DISCLOSURE'))
consent_version	text	NOT NULL	Version of the consent document
status	text	NOT NULL CHECK (status IN ('GRANTED','WITHDRAWN','EXPIRED'))
granted_at	timestamptz		NULL if status = WITHDRAWN at creation (e.g. pre-existing opt-out record)
withdrawn_at	timestamptz
withdrawal_method	text	CHECK (withdrawal_method IN ('APP','STAFF','API','EMAIL','BRANCH'))
delivery_channel	text	CHECK (delivery_channel IN ('APP','WEB','BRANCH','API'))	Channel where consent was collected
disclosure_id	uuid	REFERENCES app.disclosures(id)	Disclosure presented at time of consent
ip_address	text		If digital consent
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_consents_party_id_type on (party_id, consent_type) - idx_consents_status_type on (status, consent_type) WHERE status = 'GRANTED' - idx_consents_party_current on (party_id) WHERE status = 'GRANTED'

---

app.ob_consents

Open banking consent arrangements managed by MOD-049 (V002). Jurisdiction-profile-aware model per FR-669. Each row represents a full TPP arrangement: the consented data scopes, the TPP identity, the sharing period, and the full jurisdiction-native payload. Complements app.consents — the OPEN_BANKING row in app.consents acts as a summary/status flag; this table carries the arrangement detail.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
consent_id	uuid	NOT NULL UNIQUE DEFAULT gen_random_uuid()	Public consent reference (returned to caller)
jurisdiction_profile	text	NOT NULL CHECK (jurisdiction_profile IN ('au_cdr','nz_payments_nz','generic_fapi2'))
customer_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id)
third_party_id	uuid	NOT NULL REFERENCES app.ob_third_parties(id)
granted_scopes	text[]	NOT NULL	Internal scope identifiers (profile-agnostic)
granted_at	timestamptz	NOT NULL DEFAULT now()
expires_at	timestamptz		NULL = no expiry
status	text	NOT NULL DEFAULT 'active' CHECK (status IN ('active','revoked','expired','suspended'))
consent_payload	jsonb	NOT NULL	Full jurisdiction-native consent representation
arrangement_id	text		CDR arrangement_id or profile-native reference
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_ob_consents_customer_status on (customer_id, status) WHERE status = 'active' - idx_ob_consents_third_party on (third_party_id, status) - idx_ob_consents_expires_active on (expires_at) WHERE status = 'active' AND expires_at IS NOT NULL — sweeper scan

---

app.ob_consent_events

Immutable audit log of every open banking consent lifecycle event. ADR-048 Cat 1. Append-only — no row may ever be updated or deleted. Provides the full consent audit trail required by FR-314 and CON-007.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
consent_id	uuid	NOT NULL REFERENCES app.ob_consents(consent_id)
event_type	text	NOT NULL CHECK (event_type IN ('GRANTED','AMENDED','REVOKED','EXPIRED','SUSPENDED'))
customer_id	uuid	NOT NULL
actor	text	NOT NULL CHECK (actor IN ('CUSTOMER','STAFF','SYSTEM','TPP'))	Who triggered the event
previous_status	text		NULL on GRANTED
new_status	text	NOT NULL
changed_scopes	text[]		Populated on AMENDED — delta only
channel	text	CHECK (channel IN ('APP','API','SYSTEM'))
ip_address	text
trace_id	text
recorded_at	timestamptz	NOT NULL DEFAULT now()

Immutability: ADR-048 Cat 1 — trigger rejects all UPDATE and DELETE on this table unconditionally.

Indexes: - idx_ob_consent_events_consent_id on (consent_id, recorded_at DESC) - idx_ob_consent_events_customer on (customer_id, recorded_at DESC)

---

app.ob_third_parties

Stub TPP registry used by MOD-049 (V004). Stores known Accredited Data Recipients / API clients that may hold open banking consents with the platform's customers. Zero-seeded on deploy; populated via SQL or a future admin endpoint. Grant endpoint validates third_party_id against this table before inserting a consent.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
display_name	text	NOT NULL	Human-readable TPP name shown in consent UI
jurisdiction_profile	text	NOT NULL CHECK (jurisdiction_profile IN ('au_cdr','nz_payments_nz','generic_fapi2'))
status	text	NOT NULL DEFAULT 'active' CHECK (status IN ('active','suspended','deregistered'))
logo_url	text		HTTPS only
registered_at	timestamptz	NOT NULL DEFAULT now()

---

app.staff_training_acks

Immutable record of AML/CFT training completions per staff member. ADR-048 Cat 1. Satisfies AML-010 LOG — the platform's evidence of staff awareness obligations under NZ AML/CFT Act and AU AML/CTF Act. Seven-year retention. Managed by MOD-049 (V005).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
staff_id	text	NOT NULL	Staff identifier from Cognito staff pool
training_code	text	NOT NULL	e.g. AML_ANNUAL_2026, AML_ONBOARDING
training_version	text	NOT NULL	Version of training content completed
completed_at	timestamptz	NOT NULL	When training was completed
delivery_method	text	NOT NULL CHECK (delivery_method IN ('ONLINE','IN_PERSON','BLENDED'))
acknowledged_at	timestamptz	NOT NULL DEFAULT now()	When ack was submitted to this endpoint
ip_address	text

Immutability: ADR-048 Cat 1 — trigger rejects all UPDATE and DELETE unconditionally. Seven-year row-level retention policy.

Indexes: - idx_staff_training_acks_staff on (staff_id, training_code, completed_at DESC)

---

app.disclosures

Disclosure document registry managed by MOD-050 (AD-7 split design). One row per versioned disclosure document. Content (PDFs) lives in S3; this table stores metadata and the content-addressed SHA-256 for integrity verification. Mutable only for superseded_at / superseded_by_id transitions when a new version supersedes the previous one.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
product_id	text	NOT NULL	e.g. PRD-001
version	text	NOT NULL	Semantic version of this disclosure document
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
disclosure_type	text	NOT NULL CHECK (disclosure_type IN ('CREDIT_CONTRACT','PRODUCT_TERMS','FEE_SCHEDULE','PDS','KEY_FACTS_SHEET','PRIVACY_NOTICE','HARDSHIP_RIGHTS','RESPONSIBLE_LENDING'))
s3_key	text	NOT NULL	S3 key for the disclosure document content
content_checksum_sha256	text	NOT NULL	SHA-256 of the document content at publish time
disclosure_context	jsonb	NOT NULL DEFAULT '{}'	Key-value substitution context for template rendering
effective_from	date	NOT NULL	When this version came into effect
superseded_at	timestamptz	NULL	Set when a newer version supersedes this one
superseded_by_id	uuid	NULL REFERENCES app.disclosures(id)
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_disclosures_product_version on (product_id, jurisdiction, disclosure_type, effective_from DESC) WHERE superseded_at IS NULL - idx_disclosures_checksum on (content_checksum_sha256)

---

app.disclosure_acks

Acknowledgement ledger — one row per customer disclosure event. ADR-048 Cat 1 immutable. The gate check reads this table to verify the customer has acknowledged the current version before allowing product/feature access.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id)
disclosure_id	uuid	NOT NULL REFERENCES app.disclosures(id)	Specific versioned document acknowledged
disclosure_type	text	NOT NULL	Denormalised from app.disclosures for fast gate queries
product_id	text	NOT NULL
jurisdiction	char(2)	NOT NULL
channel	text	NOT NULL CHECK (channel IN ('APP','WEB','BRANCH','API'))
session_id	text	NULL	Cognito/MOD-068 session ID if APP/WEB channel
content_checksum_sha256	text	NOT NULL	SHA-256 of the document the customer was shown — ties the ack to the exact content
idempotency_key	text	NOT NULL UNIQUE	Prevents duplicate ack rows from retried requests
trace_id	uuid	NULL	ADR-031 propagation
acknowledged_at	timestamptz	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - (party_id, disclosure_type, product_id, jurisdiction, acknowledged_at DESC) — gate lookup - (disclosure_id, acknowledged_at DESC) — version-level audit

Immutability: trg_disclosure_acks_immutable — BEFORE UPDATE OR DELETE, calls app.fn_immutable_row(). ADR-048 Cat 1. CON-004 / CON-005 GATE evidence.

---

app.dcs_fcs_disclosures

NZ-only Key Information Summary (KIS) acknowledgement ledger for Designated Credit Suitability / First Charge Security products under CCCFA. ADR-048 Cat 1 immutable. One row per KIS presentation event.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id)
product_id	text	NOT NULL
kis_version	text	NOT NULL	Version of the KIS template used
content_checksum_sha256	text	NOT NULL	SHA-256 of the rendered KIS content shown
application_id	uuid	NULL	Credit application context if applicable
presented_at	timestamptz	NOT NULL
acknowledged_at	timestamptz	NULL	NULL = presented but not yet acknowledged (STEP_UP state)
channel	text	NOT NULL CHECK (channel IN ('APP','WEB','BRANCH'))
session_id	text	NULL	MOD-068 session ID
idempotency_key	text	NOT NULL UNIQUE
trace_id	uuid	NULL	ADR-031 propagation
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - (party_id, product_id, acknowledged_at DESC) — current KIS ack gate - (application_id) WHERE application_id IS NOT NULL

Immutability: trg_dcs_fcs_disclosures_immutable — BEFORE UPDATE OR DELETE, calls app.fn_immutable_row(). ADR-048 Cat 1. NZ CCCFA KIS compliance evidence.

Note: NZ-only. MOD-050 passes silently for AU jurisdiction (no KIS requirement). UNKNOWN jurisdiction blocks at the handler edge.

---

app.product_configurations

Product configuration stub, provisioned by MOD-050 (AD-4). MOD-127 (product configuration engine, not yet built) will assume ownership of this table when it ships. v1 contains only the columns needed by MOD-050's gate logic. Mutable.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
product_id	text	NOT NULL UNIQUE	e.g. PRD-001
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
disclosure_required	boolean	NOT NULL DEFAULT true	Whether MOD-050 gate applies
kis_required	boolean	NOT NULL DEFAULT false	NZ KIS requirement flag
config_version	text	NOT NULL	Configuration schema version
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: (product_id) UNIQUE.

Ownership: Forward-owned by MOD-050 v1; MOD-127 takes ownership via a follow-on migration when it ships.

---

app.product_parameter_metadata (MOD-127)

Startup-time seed table (V004). Defines, per parameter_key, whether a change in a given direction is unfavourable to customers, the required advance-notice period, and a JSON Schema for validating proposed_value. Seeded at startup; readable by classify-unfavourable.ts. Mutable (admin can update metadata rows to adjust notice periods without a code deploy).

Column	Type	Constraints	Description
parameter_key	text	PK	Canonical key e.g. rate.lending.variable, fee.account.monthly
unfavourable_direction	text	NOT NULL CHECK (unfavourable_direction IN ('UP','DOWN','DOWN_TO_FALSE'))	UP = higher value is worse for customer; DOWN = lower is worse; DOWN_TO_FALSE = boolean flag removal
advance_notice_days	int	NOT NULL CHECK (advance_notice_days > 0)	Required advance notice before unfavourable change can take effect (v1: jurisdiction-agnostic conservative value; v2 adds jurisdiction column)
value_schema	jsonb	NOT NULL	JSON Schema used to validate proposed_value in proposals; e.g. {"type":"number","minimum":0}
display_name	text	NOT NULL	Human-readable label for back-office UI
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger

Seed rows (V004):

parameter_key	unfavourable_direction	advance_notice_days
rate.lending.variable	UP	14
rate.lending.fixed_1y	UP	14
rate.deposit.savings	DOWN	14
rate.deposit.term_3m	DOWN	14
fee.account.monthly	UP	30
fee.transaction.foreign	UP	30
threshold.minimum_balance	UP	14
threshold.lvr_cap	DOWN	30
feature.joint_accounts.enabled	DOWN_TO_FALSE	30
feature.overdraft.enabled	DOWN_TO_FALSE	30

---

app.product_parameters (MOD-127)

EAV current-state table. One row per (product_id, jurisdiction, parameter_key) — the live effective value after the most recent approved proposal has been applied. Updated atomically by the apply-due-proposals Lambda when a proposal transitions to live. Mutable. Managed by MOD-127.

Column	Type	Constraints	Description
product_id	text	NOT NULL	e.g. PRD-001
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
parameter_key	text	NOT NULL REFERENCES app.product_parameter_metadata(parameter_key)
current_value	jsonb	NOT NULL	Current effective value; validated against parameter_key's value_schema at write time
last_proposal_id	uuid	NULL REFERENCES app.product_config_proposals(id)	The proposal that last updated this row; NULL for seed values
updated_at	timestamptz	NOT NULL	Touch trigger
created_at	timestamptz	NOT NULL DEFAULT now()

Primary key: (product_id, jurisdiction, parameter_key). Index: (product_id, jurisdiction) for getCurrentParameter library lookups.

---

app.product_config_proposals (MOD-127)

Maker/checker workflow table for product configuration changes. Append-only by application logic — no UPDATE or DELETE on terminal-state rows (approved, rejected, live, superseded). ADR-048 Cat 2: immutability trigger prevents tampering with audit trail for terminal rows. Mutable for pending rows (status transitions to under_review and then terminal states). Managed by MOD-127.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
product_id	text	NOT NULL	e.g. PRD-001
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
parameter_key	text	NOT NULL REFERENCES app.product_parameter_metadata(parameter_key)
previous_value	jsonb	NOT NULL	Snapshot of current_value at proposal creation — CON-005 before-state
proposed_value	jsonb	NOT NULL	Requested new value; validated against value_schema
status	text	NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','under_review','approved','rejected','live','superseded'))
proposed_by	text	NOT NULL	Staff user ID (UUID string; cross-domain ref to access.user_identities)
reviewed_by	text	NULL	Staff user ID of approver/rejecter; GOV-007 CHECK ensures ≠ proposed_by
review_comment	text	NULL
effective_date	date	NOT NULL	Date on or after which the change may be applied; must be ≥ advance_notice_days from proposal approval for unfavourable changes
notification_required	boolean	NOT NULL DEFAULT false	True if change is unfavourable per app.product_parameter_metadata
notification_confirmed_at	timestamptz	NULL	Set to now() when MOD-063 dispatch is triggered (OPTIMISTIC_V1); updated to actual dispatch timestamp once MOD-063 emits bank.platform.notification_dispatched
notification_method	text	NULL CHECK (notification_method IN ('OPTIMISTIC_V1','MOD_063_CONFIRMED'))	Compliance audit trail: OPTIMISTIC_V1 = optimistic stop-gap; MOD_063_CONFIRMED = real dispatch confirmed
superseded_by	uuid	NULL REFERENCES app.product_config_proposals(id)	Set when a newer proposal supersedes this one
idempotency_key	text	NOT NULL UNIQUE	Caller-supplied
trace_id	uuid	NULL	ADR-031 propagation
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger

DB-level CHECK (GOV-007 GATE):

CONSTRAINT proposed_by_neq_reviewed_by
  CHECK (reviewed_by IS NULL OR reviewed_by != proposed_by)

Immutability (ADR-048 Cat 2): trg_product_config_proposals_terminal_immutable — BEFORE UPDATE, raises exception if OLD.status IN ('approved','rejected','live','superseded'). Application can transition pending → under_review → approved|rejected and approved → live|superseded. Terminal states are write-once.

Indexes: - (product_id, jurisdiction, status) — apply-due-proposals query - (status, effective_date) WHERE status = 'approved' — daily sweep filter - (proposed_by, created_at DESC) — audit view - (idempotency_key) UNIQUE

---

app.cases

Case and complaint records managed by MOD-053. Covers service requests, complaints, escalations, and regulatory-required case types (hardship, breach). One row per case; linked complaint events track the lifecycle.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
case_reference	text	NOT NULL UNIQUE	Human-readable reference (e.g. CAS-2026-009182)
party_id	uuid	NOT NULL	cross-domain ref to SD02 party.parties(party_id)
case_type	text	NOT NULL CHECK (case_type IN ('COMPLAINT','SERVICE_REQUEST','HARDSHIP','DISPUTE','FRAUD_REPORT','REGULATORY_ENQUIRY','INTERNAL_REVIEW','AML_REFER'))
case_status	text	NOT NULL CHECK (case_status IN ('OPEN','UNDER_REVIEW','PENDING_CUSTOMER','PENDING_THIRD_PARTY','ESCALATED','RESOLVED','CLOSED','WITHDRAWN'))
priority	text	NOT NULL CHECK (priority IN ('LOW','MEDIUM','HIGH','URGENT'))
subject	text	NOT NULL
description	text	NOT NULL
channel	text	NOT NULL CHECK (channel IN ('APP','WEB','PHONE','EMAIL','BRANCH','REGULATOR'))
assigned_to	text		Staff ID
team	text		Team assignment
related_account_id	uuid		cross-domain bare UUID ref to accounts.accounts(id) — no REFERENCES constraint
related_payment_id	uuid		cross-domain bare UUID ref to payments.payments(id) — no REFERENCES constraint
vulnerability_flag	boolean	NOT NULL DEFAULT false	Set by staff or auto-flagged by MOD-053
vulnerability_notes	jsonb		Structured notes on vulnerability type/circumstances; NULL if not flagged
opened_at	timestamptz	NOT NULL DEFAULT now()
sla_deadline	timestamptz		Computed from case_type and jurisdiction SLA rules
resolved_at	timestamptz
closed_at	timestamptz
resolution_summary	text		Required before closure
regulatory_reference	text		External reference if regulator-initiated
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_cases_party_id on (party_id) - idx_cases_status on (case_status) WHERE case_status NOT IN ('CLOSED','WITHDRAWN') - idx_cases_sla_deadline on (sla_deadline) WHERE case_status NOT IN ('RESOLVED','CLOSED','WITHDRAWN') - idx_cases_case_reference on (case_reference)

---

app.case_reference_sequence

Per-year atomic counter backing the human-readable CAS-YYYY-NNNNNN case reference format. Uses INSERT … ON CONFLICT DO UPDATE (not a Postgres sequence) so the counter survives schema recreates and remains portable across Neon branches.

Column	Type	Constraints	Description
year	integer	PK	Calendar year (e.g. 2026)
seq	bigint	NOT NULL DEFAULT 0	Last sequence number issued for this year
updated_at	timestamptz	NOT NULL DEFAULT now()	Set on every upsert

Access pattern: INSERT INTO app.case_reference_sequence (year, seq) VALUES ($1, 1) ON CONFLICT (year) DO UPDATE SET seq = app.case_reference_sequence.seq + 1, updated_at = now() RETURNING seq — returns the next seq value atomically. The caller formats CAS-{year}-{LPAD(seq, 6, '0')}.

---

app.complaint_events

Append-only event log for case/complaint lifecycle, linked to app.cases. Every status change, contact, action, and note creates a new event row. Provides the full case audit trail.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
case_id	uuid	NOT NULL REFERENCES app.cases(id)
event_type	text	NOT NULL CHECK (event_type IN ('OPENED','NOTE_ADDED','STATUS_CHANGED','ASSIGNED','CONTACT_ATTEMPTED','CONTACT_MADE','DOCUMENT_ATTACHED','ESCALATED','RESOLVED','CLOSED','REOPENED','ACCEPTANCE_REFERRED'))
previous_status	text		For STATUS_CHANGED events
new_status	text		For STATUS_CHANGED events
note	text		Free-text note
actor	text	NOT NULL	Staff ID or system module
actor_type	text	NOT NULL CHECK (actor_type IN ('STAFF','CUSTOMER','SYSTEM','REGULATOR'))
trace_id	text		Propagated from originating request or Lambda context
event_timestamp	timestamptz	NOT NULL DEFAULT now()
created_at	timestamptz	NOT NULL DEFAULT now()

Constraints: - chk_status_changed_has_states: CHECK (event_type != 'STATUS_CHANGED' OR (previous_status IS NOT NULL AND new_status IS NOT NULL)) - chk_note_added_has_note: CHECK (event_type != 'NOTE_ADDED' OR note IS NOT NULL)

Indexes: - idx_complaint_events_case_id on (case_id, event_timestamp) - idx_complaint_events_timestamp on (event_timestamp DESC)

Notes: Append-only. No updated_at column. Immutability is enforced by trg_complaint_events_immutable (ADR-048 Category 1).

---

app.document_metadata

Metadata for documents stored in S3, managed by MOD-073. Documents include identity verification files, disclosure PDFs, credit contracts, and customer-uploaded supporting documents. Actual files are in S3; this table is the index.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	cross-domain ref to SD02 party.parties(party_id)
document_category	text	NOT NULL CHECK (document_category IN ('IDENTITY','DISCLOSURE','CREDIT_CONTRACT','SUPPORTING_EVIDENCE','CALL_RECORDING','STATEMENT','OTHER'))
document_type	text	NOT NULL	More specific type within category
s3_key	text	NOT NULL	S3 object key
s3_bucket	text	NOT NULL	S3 bucket name
file_name	text	NOT NULL	Original filename
mime_type	text	NOT NULL
file_size_bytes	bigint	NOT NULL CHECK (file_size_bytes > 0 AND file_size_bytes <= 26214400)	25 MB cap per FR-361
checksum_sha256	text	NOT NULL CHECK (checksum_sha256 ~ '^[0-9a-f]{64}$')	SHA-256 hex; verified on finalize
upload_status	text	NOT NULL CHECK (upload_status IN ('PENDING','COMPLETE','FAILED'))	PENDING = pre-signed URL issued; COMPLETE = S3 object confirmed
related_entity_type	text		e.g. CASE, KYC_CHECK, CREDIT_APPLICATION
related_entity_id	uuid		FK value for the related entity
retention_delete_at	date		Date this document must be deleted per policy
uploaded_by	text	NOT NULL	Customer, staff ID, or module
uploaded_at	timestamptz	NOT NULL DEFAULT now()
created_at	timestamptz	NOT NULL DEFAULT now()
deleted_at	timestamptz		Soft-delete; S3 object deleted by retention sweeper

Indexes: - idx_document_metadata_party_id on (party_id) - idx_document_metadata_related on (related_entity_type, related_entity_id) WHERE related_entity_id IS NOT NULL - idx_document_metadata_retention on (retention_delete_at) WHERE retention_delete_at IS NOT NULL AND deleted_at IS NULL

---

app.document_audit_log (Cat 1 immutable)

Append-only vault audit log per FR-364 and NFR-024 (zero mutations). Every upload, download, denial, and retention purge writes one row. Immutable via trg_document_audit_log_immutable (BEFORE UPDATE OR DELETE, calls fn_immutable_row()). 7-year retention per FR-364.

Column	Type	Notes
id	uuid	PK DEFAULT gen_random_uuid()
event_type	text NOT NULL	CHECK (event_type IN ('UPLOAD_INITIATED','UPLOAD_COMPLETED','DOWNLOAD','DENIED','RETENTION_PURGE'))
document_id	uuid NULL	NULL for UPLOAD_INITIATED (pre-metadata) or DENIED with no resolved doc
party_id	uuid NOT NULL	Customer the document belongs to
actor_user_id	uuid NULL	REFERENCES access.user_identities(user_id); NULL for SYSTEM actor
actor_type	text NOT NULL	CHECK (actor_type IN ('CUSTOMER','STAFF','SYSTEM'))
actor_justification	text NULL	CHECK (actor_type <> 'STAFF' OR (justification IS NOT NULL AND length(trim(justification)) > 0)) — staff access requires non-empty justification
document_category	text NULL	Category at time of event
session_id	uuid NULL	REFERENCES app.customer_sessions(id); NULL for SYSTEM events (e.g. retention purge)
s3_key	text NULL	Object key at time of event
ip_address	text NULL
trace_id	uuid NOT NULL	ADR-031 propagation
recorded_at	timestamptz NOT NULL DEFAULT now()

Constraints: - chk_staff_actor_has_justification: STAFF actor_type requires non-empty justification (see above) - chk_human_actor_has_user_id: CHECK (actor_type = 'SYSTEM' OR actor_user_id IS NOT NULL)

---

app.automation_rules

Customer-defined financial automation rules managed by MOD-051 (savings rules, scheduled transfers, payment rules). Each row is a single rule belonging to one customer.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	cross-domain ref to SD02 party.parties(party_id)
rule_name	text	NOT NULL	Customer-given label
rule_type	text	NOT NULL CHECK (rule_type IN ('ROUND_UP','SCHEDULED_TRANSFER','SAVE_PERCENT','BILL_ALERT','MERCHANT_BLOCK','SPEND_LIMIT','IDLE_BALANCE_SWEEP','SAFETY_NET','FX_RATE_SWEEP'))	FX_RATE_SWEEP trigger handler deferred to v2 (awaiting MOD-006 FX rate stream)
source_account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)
destination_account_id	uuid	REFERENCES accounts.accounts(id)	NULL for rule types that don't transfer funds
rule_config	jsonb	NOT NULL	Rule parameters (amount, schedule, threshold, etc.)
rule_status	text	NOT NULL CHECK (rule_status IN ('ACTIVE','PAUSED','EXPIRED','DELETED'))
trigger_count	int	NOT NULL DEFAULT 0	Number of times the rule has fired
last_triggered_at	timestamptz
effective_from	date	NOT NULL DEFAULT CURRENT_DATE
effective_to	date		NULL = no expiry
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL
deleted_at	timestamptz

Indexes: - idx_automation_rules_party_id_active on (party_id) WHERE rule_status = 'ACTIVE' - idx_automation_rules_source_account on (source_account_id) WHERE rule_status = 'ACTIVE'

---

app.automation_rule_executions

Immutable append-only audit log of every rule evaluation and its outcome. ADR-048 Cat 1. One row per evaluation — including evaluations where the condition was not met (NOT_APPLICABLE rows are only written on FAILED outcomes, not on every no-op evaluation, to keep the log focused on customer-visible events). Provides the FR-323 execution history visible to the customer and the NFR-024 audit trail.

Column	Type	Constraints	Description
execution_id	uuid	PK DEFAULT gen_random_uuid()
rule_id	uuid	NOT NULL REFERENCES app.automation_rules(id)
party_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id)
trigger_source	text	NOT NULL CHECK (trigger_source IN ('EVENT','SCHEDULE'))	Whether fired by an EventBridge event or the daily cron
trigger_event_id	text		EventBridge event_id of the triggering event; NULL for SCHEDULE triggers
condition_result	text	NOT NULL CHECK (condition_result IN ('PASSED','FAILED','NOT_APPLICABLE'))
action_outcome	text	NOT NULL CHECK (action_outcome IN ('EXECUTED','SKIPPED_INSUFFICIENT_FUNDS','SKIPPED_RULE_PAUSED','MOD_020_DECLINED','FAILED','NOTIFICATION_ONLY'))
amount	numeric(18,4)		Sweep or round-up amount; NULL for non-monetary actions
currency	char(3)		ISO 4217; NULL for non-monetary actions
source_account_id	uuid		Account funds moved from
dest_account_id	uuid		Account funds moved to
mod_020_payment_id	uuid		payments.payments(id) — populated when action_outcome = EXECUTED and a real payment was produced
failure_reason	text		Populated on action_outcome IN ('FAILED','MOD_020_DECLINED','SKIPPED_INSUFFICIENT_FUNDS')
idempotency_key	text	NOT NULL UNIQUE	rule:{rule_id}:event:{trigger_event_id} for EVENT triggers; rule:{rule_id}:schedule:{date} for SCHEDULE triggers. Prevents duplicate evaluation on EB redelivery
trace_id	uuid		ADR-031 trace propagation from triggering event
occurred_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_automation_rule_executions_rule_id on (rule_id, occurred_at DESC) — customer-facing execution history - idx_automation_rule_executions_party_id on (party_id, occurred_at DESC) - idx_automation_rule_executions_mod020_payment on (mod_020_payment_id) WHERE mod_020_payment_id IS NOT NULL

Immutability: trg_automation_rule_executions_immutable — BEFORE UPDATE OR DELETE OR TRUNCATE, calls fn_immutable_row(). FR-323 + CON-005 LOG + NFR-024; 7-year retention.

GRANTs: SELECT, INSERT only to bank_app_lambda_role. No UPDATE, DELETE, or TRUNCATE to any role.

---

app.work_queue_items

Operational work queue for staff task routing, managed by MOD-064. Covers payment exceptions, KYC review tasks, fraud alerts requiring human review, and case escalations. The queue feeds the back-office workflow.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
queue_type	text	NOT NULL CHECK (queue_type IN ('KYC_REVIEW','FRAUD_ALERT','PAYMENT_EXCEPTION','SANCTIONS_ADJUDICATION','CASE_ESCALATION','AML_ALERT','COLLECTIONS','CREDIT_REFERRAL','DISCLOSURE_EXCEPTION'))
priority	text	NOT NULL CHECK (priority IN ('LOW','MEDIUM','HIGH','URGENT'))
item_status	text	NOT NULL CHECK (item_status IN ('QUEUED','ASSIGNED','IN_PROGRESS','COMPLETED','CANCELLED','ESCALATED'))
source_entity_type	text	NOT NULL	Entity that generated this work item
source_entity_id	uuid	NOT NULL
party_id	uuid		cross-domain ref to SD02 party.parties(party_id)
sla_deadline	timestamptz		Work must be completed by this time
assigned_to	text		Staff ID
assigned_at	timestamptz
completed_at	timestamptz
completion_outcome	text		Result of the work
created_by	text	NOT NULL	Module or staff that created this item
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_work_queue_status_priority on (item_status, priority) WHERE item_status IN ('QUEUED','ASSIGNED','IN_PROGRESS') - idx_work_queue_queue_type on (queue_type, item_status) - idx_work_queue_sla_deadline on (sla_deadline) WHERE item_status IN ('QUEUED','ASSIGNED') - idx_work_queue_party_id on (party_id) WHERE party_id IS NOT NULL

---

app.call_recordings

Metadata for call recordings captured by MOD-054. Audio files are stored in S3; this table holds metadata, transcript references, and case linkage.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid		cross-domain ref to SD02 party.parties(party_id) — NULL if party unidentified at call start
case_id	uuid	REFERENCES app.cases(id)	Populated after call is linked to a case
agent_id	text	NOT NULL	Staff ID of the agent who handled the call
call_direction	text	NOT NULL CHECK (call_direction IN ('INBOUND','OUTBOUND'))
call_duration_seconds	int
s3_key_audio	text	NOT NULL	S3 key for the audio recording
s3_key_transcript	text		S3 key for the transcript (populated after transcription)
transcript_status	text	NOT NULL CHECK (transcript_status IN ('PENDING','PROCESSING','COMPLETED','FAILED'))
recording_consent_given	boolean	NOT NULL DEFAULT false	Whether customer consented to recording
started_at	timestamptz	NOT NULL
ended_at	timestamptz
retention_delete_at	date	NOT NULL	Retention period end date
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_call_recordings_party_id on (party_id) WHERE party_id IS NOT NULL - idx_call_recordings_case_id on (case_id) WHERE case_id IS NOT NULL - idx_call_recordings_agent_id on (agent_id, started_at DESC) - idx_call_recordings_retention on (retention_delete_at) WHERE retention_delete_at IS NOT NULL

---

app.transaction_view

Per-party projection of the canonical transaction log. Populated by the posting_completed EventBridge consumer (INSERT/refresh) and updated by the transactions_categorised EventBridge consumer (enrichment UPDATE). Owned by MOD-070. This is a projection — SD01 accounts.postings is the source of truth.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
posting_id	uuid	NOT NULL UNIQUE	Application-layer ref to SD01 accounts.postings(id); no FK — cross-DB. Dedup key for projection replay
party_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id)
account_id	uuid	NOT NULL	Cross-domain ref to SD01 accounts.accounts(id)
posting_date	timestamptz	NOT NULL	Timestamp from posting_completed event
amount	numeric(18,2)	NOT NULL
currency	char(3)	NOT NULL	ISO 4217
fx_rate	numeric(16,8)		NULL for same-currency postings
fee	numeric(18,2)		NULL when no fee applied
base_currency_amount	numeric(18,2)		NZD or AUD base amount after FX; NULL if same-currency
running_balance	numeric(18,2)		Balance after this posting; updated by consumer
raw_description	text	NOT NULL	Verbatim description from posting_completed event
merchant_name	text		Normalised merchant name from transactions_categorised; NULL until enriched
merchant_logo_url	text		CDN URL from transactions_categorised; NULL until enriched
merchant_map_url	text		Per-location static map URL; NULL until enriched
mcc	char(4)		ISO 18245 MCC code; NULL until enriched
category_l1	text		Top-level spend category (e.g. GROCERIES); NULL until enriched
category_l2	text		Sub-category (e.g. SUPERMARKET); NULL when not available
categorisation_confidence	numeric(5,4)		0.0000–1.0000; NULL until enriched
source_module	text	NOT NULL	Module that inserted this row (e.g. MOD-070)
fts_vector	tsvector	GENERATED ALWAYS AS (to_tsvector('english', coalesce(raw_description,'') || ' ' || coalesce(merchant_name,''))) STORED	Full-text search vector for FR-350 GIN index
last_enriched_at	timestamptz		NULL until first enrichment; updated on each transactions_categorised event
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger maintains

Indexes: - UNIQUE (posting_id) — projection dedup - idx_transaction_view_party_date on (party_id, posting_date DESC) — FR-349 keyset pagination (covering index: includes account_id, amount, currency) - idx_transaction_view_account_date on (account_id, posting_date DESC) — account-filtered history - idx_transaction_view_fts GIN on (fts_vector) — FR-350 full-text search - idx_transaction_view_party_pending_enrich on (party_id) WHERE last_enriched_at IS NULL — enrichment sweep

Notes: Projection semantics — rows are INSERT on posting_completed, UPDATE on transactions_categorised. Not immutable (enrichment columns are mutable); only posting_id, party_id, account_id, posting_date, amount, currency, and raw_description are effectively immutable (sourced from the ledger). Keyset pagination uses opaque base64-encoded (posting_date, posting_id) cursor per FR-349. fts_vector GENERATED column covers raw_description and merchant_name for FR-350 full-text search across up to 12 months.

---

app.transaction_exports (Cat 1 immutable)

Immutable audit log of every customer-initiated export request. One row per export. Satisfies GOV-006 LOG obligation. Owned by MOD-070. Immutability enforced by trg_transaction_exports_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row() — ADR-048 Category 1.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Customer who requested the export
user_id	uuid	NOT NULL	Authenticated session user (app user)
filters	jsonb	NOT NULL DEFAULT '{}'	Filter params applied (date_from, date_to, account_ids, categories)
format	text	NOT NULL CHECK (format IN ('CSV','PDF'))	Export format requested
row_count	integer	NOT NULL	Number of transaction rows included
s3_key	text	NOT NULL	S3 object key under mod070/exports/ prefix
trace_id	uuid	NOT NULL	ADR-031 trace propagation
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_transaction_exports_party_id on (party_id, created_at DESC)

Notes: Append-only — trg_transaction_exports_immutable blocks UPDATE/DELETE/TRUNCATE (ADR-048 Category 1, same pattern as app.document_audit_log). No updated_at column. s3_key uses format mod070/exports/{party_id}/{yyyy-mm-dd}/{id}.{csv|pdf}.

---

app.account_summary (MOD-077)

Balance projection per account, maintained by the bank.core.balance_updated EventBridge consumer. Created by MOD-077 V001. This is a mutable projection — SD01 accounts.accounts is the source of truth. Upserted on every balance_updated event; idempotent on last_event_id.

Column	Type	Constraints	Description
account_id	uuid	PRIMARY KEY	Cross-domain ref to SD01 accounts.accounts(id); no FK — cross-DB
party_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id)
account_type	text	NOT NULL	e.g. EVERYDAY, SAVINGS, TERM_DEPOSIT
currency	char(3)	NOT NULL	ISO 4217
balance	numeric(18,2)	NOT NULL	Ledger balance at as_at
available_balance	numeric(18,2)	NOT NULL	Available balance (balance minus holds)
accrued_interest	numeric(18,2)	NOT NULL DEFAULT 0	Accrued but unposted interest
jurisdiction	text	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
last_event_id	uuid	NOT NULL	event_id from the most recent balance_updated event — dedup key for idempotent upserts
as_at	timestamptz	NOT NULL	Balance snapshot timestamp from the event
trace_id	text	NOT NULL	ADR-031 trace propagation
updated_at	timestamptz	NOT NULL DEFAULT current_timestamp	Touch-updated by consumer

Indexes: - PRIMARY KEY (account_id) - idx_account_summary_party on (party_id) — dashboard load fetches all accounts for a party

Notes: Mutable projection — no immutability trigger. UPSERT ON CONFLICT (account_id) DO UPDATE is the consumer pattern. last_event_id guards against out-of-order or duplicate event delivery. No DELETE grant — stale rows are acceptable; the consumer refreshes on each event.

---

app.dashboard_insight_card (MOD-077)

Local cache of computed insight cards surfaced on the customer home screen. Refreshed on dashboard load and on qualifying event triggers (balance change, new categorised transaction, health score update). Created by MOD-077 V002.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Customer this card belongs to
card_type	text	NOT NULL CHECK (card_type IN ('LOW_BALANCE','RECURRING_FORECAST','IDLE_CASH','SPEND_ANOMALY','HEALTH_INSIGHT'))	Insight category
title	text	NOT NULL	Short display title
body	text	NOT NULL	Card body copy
cta_type	text	CHECK (cta_type IN ('MOVE_TO_SAVINGS','CHECK_LOAN_TERMS','REVIEW_TRANSACTION','INITIATE_TRANSFER','NONE'))	One-tap action type; NULL = no CTA
cta_payload	jsonb		Action parameters (e.g. account_id for MOVE_TO_SAVINGS)
priority	integer	NOT NULL DEFAULT 5	Render order — lower integer = higher priority
expires_at	timestamptz		Card becomes stale after this time; NULL = no expiry
displayed_at	timestamptz		Set when card is rendered to the customer
dismissed_at	timestamptz		Set when customer dismisses; excluded from active feed
trace_id	text	NOT NULL	ADR-031 trace propagation
created_at	timestamptz	NOT NULL DEFAULT current_timestamp
updated_at	timestamptz	NOT NULL DEFAULT current_timestamp

Indexes: - idx_insight_card_party_active on (party_id, priority) WHERE dismissed_at IS NULL — active card feed - idx_insight_card_party_displayed on (party_id, displayed_at DESC) — display history

Notes: Mutable — cards are refreshed (UPDATE) and dismissed (UPDATE). Not immutable; no audit-log requirement (insight cards are derived, not authoritative). card_type and cta_type CHECK constraints ensure v2 additions require an explicit migration.

---

---

Schema: access

The access schema is owned by SD08. It is the authentication and authorisation layer — linking a stable digital identity to the party model and expressing what that identity can see and do across all party relationships.

access.user_identities

An authenticated digital identity. One human = one user_id. The user_id persists across all party relationships, context changes, and device changes. This is the stable anchor for sessions, audit, and access grants.

Column	Type	Constraints	Description
user_id	uuid	PK DEFAULT gen_random_uuid()	Stable identifier — never changes, never reused
person_party_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id) — must be a NATURAL_PERSON party
login_status	text	NOT NULL CHECK (login_status IN ('ACTIVE','LOCKED','SUSPENDED','PENDING_VERIFICATION'))
mfa_status	text	NOT NULL CHECK (mfa_status IN ('ENROLLED','NOT_ENROLLED','BYPASSED'))
preferred_notification_channel	text	CHECK (preferred_notification_channel IN ('PUSH','SMS','EMAIL'))
last_login_at	timestamptz
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_user_identities_person_party_id on (person_party_id) - idx_user_identities_login_status on (login_status) WHERE login_status = 'ACTIVE'

Notes: person_party_id is a UUID convention cross-domain reference to SD02 party.parties(party_id); it is not a Postgres FK (different Neon databases). Application code enforces the constraint at write time. app.customer_sessions references access.user_identities(user_id) via a real Postgres FK — both tables are in the app database.

---

access.access_grants

What a user can see and do. A user has one or more access grants, each scoping access to a specific party, account, operating context, or portfolio. This is how a single login sees personal accounts, sole trader accounts, and charity treasurer access under one authenticated session.

Column	Type	Constraints	Description
grant_id	uuid	PK DEFAULT gen_random_uuid()
user_id	uuid	NOT NULL REFERENCES access.user_identities(user_id)
scope_type	text	NOT NULL CHECK (scope_type IN ('PARTY','ACCOUNT','OPERATING_CONTEXT','PRODUCT','PORTFOLIO'))	What kind of thing access is granted to
scope_id	uuid	NOT NULL	The party_id, account_id, context_id, etc. — cross-domain UUID convention
access_role	text	NOT NULL CHECK (access_role IN ('OWNER','VIEWER','OPERATOR','ACCOUNTANT','TREASURER','ADMIN','READ_ONLY'))
permissions_mask	jsonb	NOT NULL DEFAULT '{}'	Fine-grained flags:
granted_by_party_id	uuid		Cross-domain ref to SD02 party.parties(party_id) — who granted access; NULL = self/system for OWNER
authority_basis	text	NOT NULL CHECK (authority_basis IN ('SELF','BOARD_RESOLUTION','MANDATE','DELEGATION','SYSTEM'))
start_date	date	NOT NULL
end_date	date	CHECK (end_date IS NULL OR end_date >= start_date)	NULL = open-ended
created_at	timestamptz	NOT NULL DEFAULT now()
revoked_at	timestamptz		Populated on explicit revocation

Indexes: - idx_access_grants_user_active on (user_id, scope_type, scope_id) WHERE revoked_at IS NULL AND (end_date IS NULL OR end_date >= CURRENT_DATE) - idx_access_grants_scope on (scope_type, scope_id) WHERE revoked_at IS NULL

Example — the charity treasurer: Ross (user_id = uuid-ross-user) has an access grant with scope_type=PARTY, scope_id=uuid-wellington-community-trust, access_role=TREASURER. Ross's session resolves all accounts, contexts, and data associated with the trust. The trust remains the separate depositor under DCS. Ross's personal accounts are separate access grants.

---

access.user_roles

Back-office staff role assignments. One active role per staff user at a time — enforced by a partial unique index on (user_id) WHERE revoked_at IS NULL. Role history is retained (rows are never deleted; revocation stamps revoked_at). Created by MOD-052 V001.

Column	Type	Constraints	Description
role_id	uuid	PK DEFAULT gen_random_uuid()
user_id	uuid	NOT NULL REFERENCES access.user_identities(user_id)	Staff member being assigned the role
role	text	NOT NULL CHECK (role IN ('customer-facing','compliance','operations','senior'))	FR-325 role names; matches Cognito group name
granted_by_user_id	uuid	NOT NULL REFERENCES access.user_identities(user_id)	Staff admin who made the assignment
granted_at	timestamptz	NOT NULL DEFAULT now()
revoked_at	timestamptz		NULL = active assignment
revoked_by_user_id	uuid	REFERENCES access.user_identities(user_id)
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - Partial UNIQUE on (user_id) WHERE revoked_at IS NULL — one active role per staff user - idx_user_roles_role_active on (role) WHERE revoked_at IS NULL

Notes: Mutable by design — revoked_at is stamped on revocation. Inserts and revocations publish bank.app.role_assigned / bank.app.role_revoked events to the bank-app bus (FR-328, NFR-023). Role propagation to warm Lambdas occurs via 60s TTL cache expiry; cold-start Lambdas always load fresh from DB.

---

access.role_permissions

Column-level permission matrix: which attributes each role may access on each entity. One row per (role, entity, attribute) triple. Seeded at V002 with the FR-325 role specifications. Updated at runtime via MOD-052 admin endpoints. Mutable.

Column	Type	Constraints	Description
permission_id	uuid	PK DEFAULT gen_random_uuid()
role	text	NOT NULL CHECK (role IN ('customer-facing','compliance','operations','senior'))
entity	text	NOT NULL	Logical entity path e.g. customer.profile, aml.alerts, app.cases
attribute	text	NOT NULL	Column-level attribute name; * = all attributes on the entity
granted	boolean	NOT NULL DEFAULT true	True = attribute accessible; false = explicit deny
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - UNIQUE on (role, entity, attribute) - idx_role_permissions_role on (role)

Notes: Roles are parallel and explicit — senior has no implicit inheritance from operations. All elevated access for senior is expressed as explicit granted = true rows. The RESTRICTED_TO_COMPLIANCE_AND_LEGAL override in src/lib/restricted.ts (AML-006) short-circuits this matrix for SAR-flagged entities regardless of role_permissions rows.

---

access.access_log

Immutable audit log of every enforcement decision made by the MOD-052 library. One row per enforce() call. Every allowed and denied attribute request is recorded. Retained for 7 years per FR-327. Created by MOD-052 V003.

Column	Type	Constraints	Description
log_id	uuid	PK DEFAULT gen_random_uuid()
requested_at	timestamptz	NOT NULL DEFAULT now()
staff_user_id	uuid	NOT NULL REFERENCES access.user_identities(user_id)
role	text	NOT NULL	Role at time of request (snapshot — not FK to current assignment)
entity	text	NOT NULL	Entity path evaluated
attributes_requested	text[]	NOT NULL DEFAULT '{}'
attributes_allowed	text[]	NOT NULL DEFAULT '{}'
attributes_denied	text[]	NOT NULL DEFAULT '{}'
decision	text	NOT NULL CHECK (decision IN ('ALLOWED','DENIED','PARTIAL'))	PARTIAL = some attributes allowed, some denied
deny_reason	text		e.g. INSUFFICIENT_ROLE_SCOPE, RESTRICTED_COMPLIANCE_AND_LEGAL
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
trace_id	text		ADR-031 X-Ray propagation
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_access_log_staff_user on (staff_user_id, requested_at DESC) - idx_access_log_entity on (entity, requested_at DESC) - idx_access_log_denied on (requested_at DESC) WHERE decision = 'DENIED'

Immutability (ADR-048 Cat 1): trg_access_log_immutable — BEFORE UPDATE OR DELETE OR TRUNCATE, calls fn_immutable_row(). This is the durable compliance record. The CloudWatch Logs write to /aws/bank-app/access-log-{env} is a secondary SIEM feed; a CW Logs write failure does not compromise the compliance obligation, which is met by the Postgres row.

---

Payment initiation tables (MOD-071)

MOD-071 manages the two-step preview→confirm payment initiation flow, the payee address book, and scheduled payment instructions. All tables are in the app schema (ADR-064). Migrations land in bank-app as V00N__app_payment_*.sql.

---

app.payment_previews

One row per payment preview session (CAP-004/005/007/008/114). Carries the full ValidatePaymentRequest payload, the MOD-071-minted payment_id that will be passed to MOD-020 on confirmation, FX/fee disclosure fields, and step-up flags. Rows expire after 15 minutes.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()	Public preview_id returned to the client and required on POST /payments/confirm
party_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id)
payment_type	text	NOT NULL CHECK (payment_type IN ('DOMESTIC_TRANSFER','INTERNATIONAL_WIRE','BPAY','INTERBANK_NZ','OSKO'))	Determines rail and MOD-020 fields
payment_id	uuid	NOT NULL	Minted by MOD-071 at preview creation; passed to MOD-020 and MOD-001 for audit linkage. No FK — payments.payments row created by MOD-020 in same chain
step_up_required	boolean	NOT NULL DEFAULT false	True if amount > threshold or first-time payee (FR-355)
step_up_reason	text	CHECK (step_up_reason IN ('AMOUNT_THRESHOLD','FIRST_TIME_PAYEE','BOTH'))	Populated when step_up_required = true
disclosure_required	boolean	NOT NULL DEFAULT false	True for FX payments; MOD-050 check result
request_payload	jsonb	NOT NULL	Full ValidatePaymentRequest payload
fx_rate	numeric(18,8)		Spot rate at preview time (FX payments only)
fx_markup	numeric(18,8)		Markup applied on top of spot rate
fee_amount	numeric(18,2)		Total fee in fee_currency
fee_currency	char(3)		ISO-4217
base_currency_amount	numeric(18,2)		Amount in source currency before conversion
total_to_debit	numeric(18,2)		Total debited including fees (source currency)
mod_020_decision	text	CHECK (mod_020_decision IN ('AUTHORISED','VALIDATION_FAILED','PENDING_AUTH'))	Populated after MOD-020 response on confirmation
mod_020_failure_reason	text		MOD-020 failure_reason if VALIDATION_FAILED
status	text	NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','CONFIRMED','EXPIRED','CANCELLED'))
idempotency_key	text	NOT NULL UNIQUE	sha256(id::text || ':' || party_id::text); passed to MOD-020 as idempotency_key
expires_at	timestamptz	NOT NULL DEFAULT now() + interval '15 minutes'
trace_id	text	NOT NULL	OTel trace ID from preview request
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_payment_previews_party_status on (party_id, status) WHERE status = 'PENDING' - idx_payment_previews_expires_at on (expires_at) WHERE status = 'PENDING' — TTL sweeper - idx_payment_previews_payment_id on (payment_id)

Notes: id is the public preview_id. payment_id is minted here — established pattern (same as MOD-141/MOD-119/MOD-120/MOD-122). No FK to payments.payments because the payments row is created by MOD-020 in the same synchronous confirmation chain. step_up_required is evaluated from the STEP_UP_THRESHOLD_DEFAULT env var and the payee's first_use_completed flag.

---

app.payment_initiation_events

ADR-048 Cat 1 immutable. One row per state transition in the payment initiation lifecycle. Provides the FR-356 + NFR-024 audit trail (session_id, device_fingerprint, timestamps). State sequence: PREVIEWED → CONFIRMED → SUBMITTED → RESULTED | EXPIRED | CANCELLED.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
preview_id	uuid	NOT NULL REFERENCES app.payment_previews(id)
party_id	uuid	NOT NULL	Denormalised from preview — avoids JOIN
event_type	text	NOT NULL CHECK (event_type IN ('PREVIEWED','CONFIRMED','SUBMITTED','RESULTED','EXPIRED','CANCELLED'))	State transition label
session_id	uuid	NOT NULL REFERENCES app.customer_sessions(id)	Active session at event time
device_fingerprint	text		sha256 hex from x-device-fingerprint header (MOD-069 mobile shell)
payment_id	uuid		MOD-071-minted payment_id — present from CONFIRMED onwards
mod_020_decision	text	CHECK (mod_020_decision IN ('AUTHORISED','VALIDATION_FAILED','PENDING_AUTH'))	Present on RESULTED rows
mod_020_failure_reason	text		Present on RESULTED + VALIDATION_FAILED rows
trace_id	text	NOT NULL	OTel trace ID
event_timestamp	timestamptz	NOT NULL DEFAULT now()

Immutability: trg_payment_initiation_events_immutable — BEFORE UPDATE OR DELETE OR TRUNCATE, calls fn_immutable_row(). FR-356 + NFR-024 audit record; 7-year retention.

Indexes: - idx_payment_initiation_events_preview_id on (preview_id, event_type) - idx_payment_initiation_events_party_id on (party_id, event_timestamp DESC) - idx_payment_initiation_events_payment_id on (payment_id) WHERE payment_id IS NOT NULL

---

app.payees

Mutable payee address book (CAP-114). Each row is a saved recipient for a party. Soft-deleted on removal; first_use_completed drives the FR-355 first-time-payee step-up gate.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id)
payment_type	text	NOT NULL CHECK (payment_type IN ('DOMESTIC_TRANSFER','INTERNATIONAL_WIRE','BPAY','INTERBANK_NZ','OSKO'))
payee_name	text	NOT NULL	Display name
destination_account_number	text	NOT NULL	Account number, IBAN, PayID, or BPAY biller code depending on payment_type
swift_bic	text		For INTERNATIONAL_WIRE
bank_code	text		BSB (AU) or routing number
currency	char(3)		ISO-4217; populated for cross-currency payees
bpay_crn	text		Customer Reference Number for BPAY payees
first_use_completed	boolean	NOT NULL DEFAULT false	Set true after first successful payment; clears FR-355 step-up gate for repeat payments
deleted_at	timestamptz		Soft-delete
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Unique constraint: UNIQUE (party_id, payment_type, destination_account_number, swift_bic) WHERE deleted_at IS NULL

Indexes: - idx_payees_party_active on (party_id) WHERE deleted_at IS NULL - idx_payees_party_type_active on (party_id, payment_type) WHERE deleted_at IS NULL

Notes: Soft-delete preserves audit record after customer removes a payee. first_use_completed = false on the matched payee record causes the next preview to set step_up_required = true with step_up_reason = 'FIRST_TIME_PAYEE'.

---

app.scheduled_payments

Mutable scheduled payment instructions (CAP-007). Supports one-off and recurring payments. The scheduled-payment-sweeper Lambda reads ACTIVE rows WHERE next_run_at <= now() on its daily cron (cron(0 19 ? * * *) = 07:00 NZST).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id)
payment_type	text	NOT NULL CHECK (payment_type IN ('DOMESTIC_TRANSFER','INTERNATIONAL_WIRE','BPAY','INTERBANK_NZ'))	OSKO excluded — real-time only, not schedulable
frequency	text	NOT NULL CHECK (frequency IN ('ONE_OFF','WEEKLY','FORTNIGHTLY','MONTHLY'))
request_payload	jsonb	NOT NULL	Base ValidatePaymentRequest template — amount, recipient, reference
status	text	NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE','PAUSED','COMPLETED','CANCELLED','FAILED'))
start_date	date	NOT NULL	First execution date
end_date	date		NULL = recurring indefinitely
next_run_at	timestamptz	NOT NULL	Next scheduled execution; updated by sweeper after each run
last_run_at	timestamptz		Most recent execution timestamp
last_payment_id	uuid		payment_id minted on most recent sweep run
run_count	integer	NOT NULL DEFAULT 0	Total successful executions
idempotency_prefix	text	NOT NULL	sha256(id::text || ':' || party_id::text); sweeper appends run_count to derive per-run idempotency_key
trace_id	text	NOT NULL	OTel trace ID from creation request
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_scheduled_payments_sweeper on (next_run_at) WHERE status = 'ACTIVE' — sweeper query - idx_scheduled_payments_party_active on (party_id) WHERE status IN ('ACTIVE','PAUSED')

Notes: OSKO is excluded — real-time payments are not schedulable in v1. Recurring payments with end_date IS NULL must display a UI warning ("This payment will continue indefinitely — set an end date or cancel to stop it"). idempotency_prefix || ':' || run_count produces a unique per-run idempotency_key, preventing duplicate MOD-020 submissions on sweeper retry.

---

DB-enforced invariants (ADR-048)

Registers the Postgres constraints and triggers applied to this domain. All constraints and triggers are implemented in numbered Flyway migrations in bank-app.

Immutability triggers

Table	Trigger	Category
app.complaint_events	trg_complaint_events_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row()	Cat 1
access.access_log	trg_access_log_immutable — BEFORE UPDATE OR DELETE OR TRUNCATE, calls fn_immutable_row() (MOD-052 V003). 7-year retention per FR-327; durable compliance record regardless of CW Logs availability.	Cat 1
app.ob_consent_events	trg_ob_consent_events_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row() (MOD-049 V003). Consent audit trail per FR-314 and CON-007; 7-year retention.	Cat 1
app.staff_training_acks	trg_staff_training_acks_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row() (MOD-049 V005). AML-010 LOG evidence; 7-year retention.	Cat 1
app.document_audit_log	trg_document_audit_log_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row() (MOD-073 V002). FR-364 + NFR-024 vault audit log; 7-year retention.	Cat 1
app.payment_initiation_events	trg_payment_initiation_events_immutable — BEFORE UPDATE OR DELETE OR TRUNCATE, calls fn_immutable_row() (MOD-071 V002). FR-356 + NFR-024 payment audit trail; 7-year retention.	Cat 1
app.automation_rule_executions	trg_automation_rule_executions_immutable — BEFORE UPDATE OR DELETE OR TRUNCATE, calls fn_immutable_row() (MOD-051 V002). FR-323 + CON-005 LOG + NFR-024 rule execution audit log; 7-year retention.	Cat 1

| app.product_config_proposals | trg_product_config_proposals_terminal_immutable — BEFORE UPDATE, raises exception if OLD.status IN ('approved','rejected','live','superseded') (MOD-127 V002). GOV-007 GATE + GOV-006 LOG evidence; 7-year audit retention. | Cat 2 | | app.card_control_audit | trg_card_control_audit_immutable — BEFORE UPDATE OR DELETE OR TRUNCATE, calls fn_immutable_row() (MOD-078 V002). FR-376 + NFR-024 controls audit log; 7-year retention. | Cat 1 |

Other tables in the app and access schemas are mutable by design — sessions expire, consents are withdrawn, cases progress through status transitions, access grants are revoked, role_permissions are updated by admins, and app.product_parameters, app.product_parameter_metadata reflect current live product configuration.

CHECK constraints

Table	Column	Constraint
app.customer_sessions	expires_at	CHECK (expires_at > initiated_at)
app.document_metadata	file_size_bytes	CHECK (file_size_bytes > 0 AND file_size_bytes <= 26214400) — 25 MB cap per FR-361
app.document_metadata	checksum_sha256	CHECK (checksum_sha256 ~ '^[0-9a-f]{64}$') — SHA-256 hex format
app.document_metadata	upload_status	CHECK (upload_status IN ('PENDING','COMPLETE','FAILED'))
app.document_audit_log	event_type	CHECK (event_type IN ('UPLOAD_INITIATED','UPLOAD_COMPLETED','DOWNLOAD','DENIED','RETENTION_PURGE'))
app.document_audit_log	actor_type	CHECK (actor_type IN ('CUSTOMER','STAFF','SYSTEM'))
app.document_audit_log	actor_justification (chk_staff_actor_has_justification)	CHECK (actor_type <> 'STAFF' OR (justification IS NOT NULL AND length(trim(justification)) > 0))
app.document_audit_log	actor_user_id (chk_human_actor_has_user_id)	CHECK (actor_type = 'SYSTEM' OR actor_user_id IS NOT NULL)
access.access_grants	end_date	CHECK (end_date IS NULL OR end_date >= start_date)
app.product_config_proposals	reviewed_by	CHECK (reviewed_by IS NULL OR reviewed_by != proposed_by) — GOV-007 four-eyes
app.product_config_proposals	status	CHECK (status IN ('pending','under_review','approved','rejected','live','superseded'))
app.product_parameter_metadata	advance_notice_days	CHECK (advance_notice_days > 0)
app.card_controls	freeze_status	CHECK (freeze_status IN ('ACTIVE','FROZEN'))
app.card_controls	channels_disabled	Array element check — valid values: 'ONLINE','INTERNATIONAL','CONTACTLESS'
app.card_control_audit	control_type	CHECK (control_type IN ('FREEZE','UNFREEZE','LIMIT_SET','CHANNEL_TOGGLE','VIRTUAL_CARD'))
app.card_control_audit	actor_type	CHECK (actor_type IN ('CUSTOMER','SYSTEM','AGENT'))
app.automation_rules	rule_type	CHECK (rule_type IN ('ROUND_UP','SCHEDULED_TRANSFER','SAVE_PERCENT','BILL_ALERT','MERCHANT_BLOCK','SPEND_LIMIT','IDLE_BALANCE_SWEEP','SAFETY_NET','FX_RATE_SWEEP'))
app.automation_rule_executions	trigger_source	CHECK (trigger_source IN ('EVENT','SCHEDULE'))
app.automation_rule_executions	condition_result	CHECK (condition_result IN ('PASSED','FAILED','NOT_APPLICABLE'))
app.automation_rule_executions	action_outcome	CHECK (action_outcome IN ('EXECUTED','SKIPPED_INSUFFICIENT_FUNDS','SKIPPED_RULE_PAUSED','MOD_020_DECLINED','FAILED','NOTIFICATION_ONLY'))

Not DB-enforced (Category 3 — cross-service or config-driven)

Session expiry enforcement, consent verification at product onboarding, and SLA deadline computation all depend on AppConfig, KYC state from SD02, or runtime policy. These live in their respective Lambda modules.

---

Card & account controls tables (MOD-078)

app.card_controls

Current freeze and channel-toggle state per card. Mutable — one row per card_id, upserted on each customer control action. This table is the authoritative read source for card authorization decisions (FR-557: ATM toggle status reads from MOD-078's controls API, which queries this table). Owned by MOD-078 V001.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id) — no FK
card_id	uuid	NOT NULL UNIQUE	Soft FK to SD01 card-management system (not yet deployed)
freeze_status	text	NOT NULL DEFAULT 'ACTIVE' CHECK (freeze_status IN ('ACTIVE','FROZEN'))	Current freeze state
channels_disabled	text[]	NOT NULL DEFAULT '{}'	Array of disabled channel flags: 'ONLINE', 'INTERNATIONAL', 'CONTACTLESS'
per_transaction_limit	numeric(15,2)		NULL = no customer-set limit (MOD-021 platform default applies)
daily_limit	numeric(15,2)		NULL = no customer-set limit (MOD-021 platform default applies)
limit_currency	char(3)	CHECK (limit_currency IS NULL OR length(limit_currency) = 3)	ISO 4217 — required when limits are set
updated_at	timestamptz	NOT NULL DEFAULT now()	Last control change; updated on every upsert
updated_by_session_id	uuid	REFERENCES app.customer_sessions(id)	Session that made the last change

Indexes: - idx_card_controls_party_id on (party_id) - idx_card_controls_card_id — unique index enforcing one row per card

Notes: channels_disabled array is the authoritative toggle state read by the ATM auth module (FR-557) and future card network auth handlers. Spending limits here are denormalised for display; the enforcing values live in payments.payment_limits (MOD-021) which MOD-078 updates via the admin API on each customer change. On freeze: freeze_status = 'FROZEN' is read by authorization handlers; simultaneously MOD-078 writes a limit_type = 'FREEZE' row to MOD-021 for the payments-channel enforcement path.

---

app.card_control_audit (Cat 1 immutable)

Append-only audit log of every card control action. One row per control event. Satisfies FR-376 and NFR-024 (zero mutations, 7-year retention). Immutable via trg_card_control_audit_immutable (BEFORE UPDATE OR DELETE OR TRUNCATE, calls fn_immutable_row()). Owned by MOD-078 V002.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id)
card_id	uuid	NOT NULL	Soft FK to SD01 card-management (no Postgres FK)
control_type	text	NOT NULL CHECK (control_type IN ('FREEZE','UNFREEZE','LIMIT_SET','CHANNEL_TOGGLE','VIRTUAL_CARD'))
prev_state	jsonb	NOT NULL	Snapshot of app.card_controls row before the change
new_state	jsonb	NOT NULL	Snapshot of app.card_controls row after the change
session_id	uuid	REFERENCES app.customer_sessions(id)	Initiating session — NULL only for SYSTEM actor
actor_type	text	NOT NULL CHECK (actor_type IN ('CUSTOMER','SYSTEM','AGENT'))
trace_id	text	NOT NULL	AWS X-Ray trace ID for end-to-end correlation
created_at	timestamptz	NOT NULL DEFAULT now()	Immutable timestamp

Indexes: - idx_card_control_audit_party_id_created on (party_id, created_at DESC) - idx_card_control_audit_card_id_created on (card_id, created_at DESC)

Notes: Append-only — no updated_at column. prev_state / new_state are JSONB snapshots of the app.card_controls row, ensuring the full before/after delta is captured even if the controls table schema changes in future. 7-year row-level retention per FR-376.

---

Cross-domain references

This table	Column	References	Notes
app.customer_sessions	user_id	access.user_identities(user_id)	Real Postgres FK within app database
app.consents	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.disclosures	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.cases	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.cases	related_account_id	SD01 accounts.accounts(id)	Optional — case linked to an account
app.cases	related_payment_id	SD04 payments.payments(id)	Optional — case linked to a payment
app.transaction_view	posting_id	SD01 accounts.postings(id)	Soft reference — no FK; cross-DB projection
app.transaction_view	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.transaction_view	account_id	SD01 accounts.accounts(id)	Cross-domain UUID convention
app.transaction_exports	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.account_summary	account_id	SD01 accounts.accounts(id)	Soft reference — no FK; cross-DB projection
app.account_summary	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.dashboard_insight_card	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.automation_rules	source_account_id	SD01 accounts.accounts(id)	Rules operate on accounts
app.automation_rules	destination_account_id	SD01 accounts.accounts(id)
app.automation_rule_executions	rule_id	app.automation_rules(id)	Real FK — within same database
app.automation_rule_executions	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.automation_rule_executions	source_account_id	SD01 accounts.accounts(id)	Soft ref — no FK (cross-domain)
app.automation_rule_executions	dest_account_id	SD01 accounts.accounts(id)	Soft ref — no FK (cross-domain)
app.automation_rule_executions	mod_020_payment_id	SD04 payments.payments(id)	Soft ref — no FK; populated when execution produced a real payment
app.work_queue_items	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
access.user_identities	person_party_id	SD02 party.parties(party_id)	Cross-domain UUID convention; must be a NATURAL_PERSON party
access.access_grants	scope_id	SD02 party.parties(party_id) (where scope_type=PARTY)	Cross-domain UUID convention
access.access_grants	scope_id	SD01 accounts.accounts(id) (where scope_type=ACCOUNT)	Cross-domain UUID convention
access.access_grants	scope_id	SD01 contexts.operating_contexts(context_id) (where scope_type=OPERATING_CONTEXT)	Cross-domain UUID convention
app.payment_previews	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.payment_previews	payment_id	SD04 payments.payments(id)	Soft ref — no FK; minted by MOD-071, row created by MOD-020 in same chain
app.payment_initiation_events	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.payment_initiation_events	payment_id	SD04 payments.payments(id)	Soft ref — no FK
app.payees	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.scheduled_payments	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.card_controls	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.card_controls	card_id	SD01 card-management system (not yet deployed)	Soft reference — no FK; UUID convention
app.card_control_audit	party_id	SD02 party.parties(party_id)	Cross-domain UUID convention
app.card_control_audit	card_id	SD01 card-management system (not yet deployed)	Soft reference — no FK; UUID convention

Consumers of SD08 data: SD06 Snowflake receives app.consents, app.cases, and access.access_grants via CDC (MOD-042) for governance, consent audit, and access analytics. SD07 MOD-047 (agent action log) captures all staff access to SD08 back-office screens. The work_queue_items table drives back-office operational SLAs tracked in SD06 analytics. The access.* tables are the authoritative source for any audit of who had access to what and when.