SD01 — Core Banking data model

Database: core (Neon Postgres, ap-southeast-2) Repo: bank-core 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-003 (CDC to Snowflake), ADR-048 (database-enforced invariants) Schema owner: SD01 Core Banking

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

The accounts schema is the primary write domain for MOD-001 (double-entry posting), MOD-002 (immutable transaction log), MOD-003 (real-time balance engine), MOD-004 (multi-currency ledger), MOD-005 (daily accrual calculator), MOD-006 (rate change propagation), MOD-007 (account state machine), and MOD-008 (dormancy and escheatment engine). All postings are immutable — corrections are made via reversal entries, never edits.

---

Schema: accounts

accounts.accounts

The primary account record. Every customer account — savings, transaction, and multi-currency — has one row here. Account lifecycle transitions are managed exclusively by MOD-007.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()	Surrogate account identifier
account_number	text	NOT NULL UNIQUE	Bank-issued account number (BSB+account for AU, account number for NZ)
product_code	text	NOT NULL	References account_products.product_code
currency	char(3)	NOT NULL	ISO 4217 currency code (NZD, AUD)
status	text	NOT NULL	CHECK (status IN ('PENDING','ACTIVE','RESTRICTED','DORMANT','CLOSED'))
balance	numeric(18,2)	NOT NULL DEFAULT 0.00	Current cleared balance (denormalised from postings for read speed)
available_balance	numeric(18,2)	NOT NULL DEFAULT 0.00	Balance minus pending holds
overdraft_limit	numeric(18,2)	NOT NULL DEFAULT 0.00	Approved overdraft facility amount
jurisdiction	char(2)	NOT NULL	CHECK (jurisdiction IN ('NZ','AU'))
opened_at	timestamptz	NOT NULL DEFAULT now()	When account moved to ACTIVE
closed_at	timestamptz		Populated on closure
dormancy_flagged_at	timestamptz		Set by MOD-008 when inactivity threshold crossed
last_transaction_at	timestamptz		Updated on each posting
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL
deleted_at	timestamptz		Soft-delete for closed accounts awaiting escheatment
version	int	NOT NULL DEFAULT 0 CHECK (version >= 0)	Optimistic-lock counter (FR-430). MOD-001 increments inside its posting transaction; MOD-003 hold-management writers do UPDATE … WHERE version = $expected and retry on row-count 0. Added by MOD-003 V002 migration.
is_internal	bool	NOT NULL DEFAULT false	True for bank-owned internal accounts (FX nostro, P&L collectors). Added by MOD-004 V007 migration. Internal accounts are excluded from customer-facing balance aggregations.
restriction_reason	text	CHECK (restriction_reason IN ('SANCTIONS','FRAUD_INVESTIGATION','HARDSHIP_ARRANGEMENT','ADMIN','INSUFFICIENT_SIGNATORIES','NOTICE_PENDING'))	Populated when status = RESTRICTED; NULL otherwise. Discriminates the cause so downstream modules can determine which RESTRICTED accounts to skip. Set by MOD-007. INSUFFICIENT_SIGNATORIES added by MOD-134 V001. NOTICE_PENDING added by MOD-130 V001 (lockstep with accounts.account_state_history per MOD-134 precedent).
obr_frozen_amount	numeric(18,2)	NOT NULL DEFAULT 0	OBR partition: amount frozen by haircut. 0 in normal/pre_positioned state; set atomically by MOD-143 activate handler. Added by MOD-143 V001.
obr_available_amount	numeric(18,2)	NOT NULL DEFAULT 0	OBR partition: amount available to depositor after haircut. Equals balance in normal/pre_positioned state; set atomically by MOD-143 V001 activation. Channel-restriction trigger (trg_posting_obr_channel_gate) rejects DEBIT postings that would breach this floor during activated state. Added by MOD-143 V001; existing rows initialised to balance in the same migration.

Indexes: - idx_accounts_status on (status) WHERE status IN ('ACTIVE','RESTRICTED') - idx_accounts_dormancy_flagged on (dormancy_flagged_at) WHERE dormancy_flagged_at IS NOT NULL - idx_accounts_account_number on (account_number)

Notes: balance is a denormalised read-cache derived from summing postings.amount. The canonical balance is always re-derivable from the postings table. The balance column is updated atomically within the same transaction as each posting by MOD-001. Account-to-party relationships are expressed exclusively via accounts.account_party_relationships — there is no customer_id column on the account itself.

---

accounts.postings

Immutable double-entry ledger entries. Every financial movement — debit or credit — is a posting row. Rows are never updated or deleted. Corrections are effected by reversal postings referencing reverses_posting_id.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT uuidv7()	Surrogate posting identifier — UUIDv7 (time-ordered). Postgres 17 built-in function is uuidv7() (no gen_ prefix). Satisfies FR-047 monotonic ordering requirement for deterministic replay. If uuidv7() is not available on the target Neon instance, install the pg_uuidv7 extension or define a wrapper: CREATE FUNCTION uuidv7() RETURNS uuid LANGUAGE sql AS $$ SELECT gen_random_uuid() $$ as a temporary fallback (revert to true v7 once available).
account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)	Target account
transaction_id	uuid	NOT NULL	Groups the two legs of a double-entry pair
entry_type	text	NOT NULL CHECK (entry_type IN ('DEBIT','CREDIT'))	Direction of movement
amount	numeric(18,2)	NOT NULL CHECK (amount > 0)	Absolute value; always positive
currency	char(3)	NOT NULL	ISO 4217; must match account currency or be a cross-currency leg
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))	Must match the destination (credit-leg) account's registered jurisdiction per FR-424
value_date	date	NOT NULL	Settlement date for interest accrual
posting_date	timestamptz	NOT NULL DEFAULT now()	Wall-clock time of posting
payment_id	uuid		FK to payments.payments(id) if originated by SD04
fx_conversion_id	uuid	REFERENCES accounts.fx_conversions(id)	Set on all 4 legs of a multi-currency conversion. Added by MOD-004 V003 migration. NULL for non-FX postings.
source_module	text	NOT NULL	Module that initiated the posting (e.g. MOD-001, MOD-005)
narrative	text	NOT NULL	Human-readable transaction description
reverses_posting_id	uuid	REFERENCES accounts.postings(id)	Populated on reversal/cancellation postings — the cancels pointer per FR-422. Reverse lookup (find the cancellation of a given posting) uses idx_postings_reverses_posting_id.
metadata	jsonb	NOT NULL DEFAULT '{}'	Extensible structured metadata (FX rate, reference, etc.)
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_postings_account_id_posting_date on (account_id, posting_date DESC) - idx_postings_transaction_id on (transaction_id) - idx_postings_payment_id on (payment_id) WHERE payment_id IS NOT NULL - idx_postings_value_date on (value_date) - idx_postings_reverses_posting_id on (reverses_posting_id) WHERE reverses_posting_id IS NOT NULL

Notes: This table is append-only enforced at the DB layer by trg_postings_immutable (BEFORE UPDATE OR DELETE) and at the application layer by MOD-001 — the DB trigger is the hard floor. No updated_at column — immutable rows do not change. The transaction_id groups debit and credit legs; every valid transaction has exactly two postings with opposite entry_type and equal absolute amount per currency — enforced at COMMIT time by the deferred trg_postings_double_entry constraint trigger. UUIDv7 IDs are time-ordered — the id column is sortable in creation order without a separate sequence column. See the DB-enforced invariants section for the full trigger register.

---

accounts.account_products

Defines the rate and feature configuration for each account product type. MOD-006 reads this table to propagate rate changes across affected accounts.

Column	Type	Constraints	Description
product_code	text	PK	Unique product identifier (e.g. NZ_SAVINGS_01)
product_name	text	NOT NULL	Human-readable name
product_type	text	NOT NULL CHECK (product_type IN ('SAVINGS','TRANSACTION','TERM_DEPOSIT','LOAN'))
currency	char(3)	NOT NULL
base_interest_rate	numeric(8,6)	NOT NULL DEFAULT 0.000000	Annual rate as a decimal (0.045 = 4.5%)
overdraft_rate	numeric(8,6)	NOT NULL DEFAULT 0.000000
jurisdiction	char(2)	NOT NULL
effective_from	date	NOT NULL	Rate effective date
effective_to	date	CHECK (effective_to IS NULL OR effective_to > effective_from)	NULL = currently active rate
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_account_products_product_code_effective on (product_code, effective_from DESC) - idx_account_products_active on (product_code) WHERE effective_to IS NULL

---

accounts.interest_rates

Historical rate schedule for all product types. MOD-005 (daily accrual) reads the rate active on accrual_date. For lending products, the published rate is tp_rate_bps + margin_bps; tp_rate_bps is written by MOD-086 write-back and margin_bps is the product manager's configured overlay. Separate from account_products to allow point-in-time rate reconstruction for audit.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
product_code	text	NOT NULL REFERENCES accounts.account_products(product_code)
rate_type	text	NOT NULL CHECK (rate_type IN ('BASE','BONUS','PENALTY','OVERDRAFT','FIXED_LENDING','VARIABLE_LENDING'))
annual_rate	numeric(8,6)	NOT NULL CHECK (annual_rate >= 0)	Decimal fraction (not percentage) — the all-in published rate
tp_rate_bps	int		TP base rate in basis points at time of set (from treasury.tp_rates); NULL for non-lending products
margin_bps	int		Product manager margin over TP in basis points; NULL for non-lending products
lvr_min	numeric(5,4)		Minimum LVR for this rate band (lending only; e.g. 0.8000 = 80%)
lvr_max	numeric(5,4)		Maximum LVR for this rate band (NULL = no upper bound)
credit_tier	text		Credit tier this band applies to (e.g. 'PRIME','NEAR_PRIME','STANDARD'); NULL = all tiers
effective_from	date	NOT NULL	Inclusive start date
effective_to	date	CHECK (effective_to IS NULL OR effective_to > effective_from)	NULL = current rate
authorised_by	text	NOT NULL	Role that approved the rate change
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_interest_rates_product_effective on (product_code, effective_from DESC) - idx_interest_rates_active on (product_code, rate_type) WHERE effective_to IS NULL - idx_interest_rates_lending_band on (product_code, lvr_min, lvr_max, credit_tier) WHERE rate_type IN ('FIXED_LENDING','VARIABLE_LENDING')

---

accounts.pending_holds

Temporary holds that reduce available_balance pending payment settlement or authorisation. Holds are released either by settlement (posting) or expiry.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)
payment_id	uuid		Originating payment reference
hold_type	text	NOT NULL CHECK (hold_type IN ('PAYMENT_AUTH','LEGAL_HOLD','ADMIN_HOLD','CARD_AUTH'))
amount	numeric(18,2)	NOT NULL
currency	char(3)	NOT NULL
expires_at	timestamptz	NOT NULL	After this time the hold is auto-released
released_at	timestamptz		Set when hold is explicitly released
release_reason	text
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_pending_holds_account_id_active on (account_id) WHERE released_at IS NULL - idx_pending_holds_expires_at on (expires_at) WHERE released_at IS NULL - idx_pending_holds_payment_id on (payment_id) WHERE payment_id IS NOT NULL

---

accounts.daily_balance_snapshots

Append-only end-of-day balance snapshot per account, captured by MOD-003's Mod003EodSnapshotJob Lambda at jurisdiction-local 23:55 (separate cron per jurisdiction). Retained 7 years to support regulatory point-in-time balance queries (FR-431). Immutable by design — corrections are new rows, not updates.

Column	Type	Constraints	Description
snapshot_date	date	NOT NULL, PK component	Business date of the snapshot
account_id	uuid	NOT NULL REFERENCES accounts.accounts(id), PK component
ledger_balance	numeric(18,2)	NOT NULL	Cleared balance at end of day
available_balance	numeric(18,2)	NOT NULL	Ledger balance minus active holds
currency	char(3)	NOT NULL	ISO 4217
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
captured_at	timestamptz	NOT NULL DEFAULT now()	Exact time the snapshot was written

Primary key: (snapshot_date, account_id)

Indexes: - idx_daily_balance_snapshots_account_date on (account_id, snapshot_date DESC) - idx_daily_balance_snapshots_jurisdiction_date on (jurisdiction, snapshot_date DESC)

Immutability: UPDATE/DELETE are blocked via RLS policies (FOR UPDATE USING (false), FOR DELETE USING (false)). bank_core_app_user has INSERT + SELECT only. Consumed by MOD-036 (prudential return builder) for regulatory capital reporting.

---

accounts.dormancy_records

Audit log of dormancy lifecycle events processed by MOD-008. Tracks each stage of the inactivity → dormancy → escheatment pipeline with statutory reference dates for NZ Unclaimed Money Act and AU unclaimed money legislation.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)
event_type	text	NOT NULL CHECK (event_type IN ('INACTIVITY_FLAGGED','DORMANCY_NOTICE_SENT','DORMANCY_CONFIRMED','ESCHEATMENT_NOTIFIED','ESCHEATMENT_SUBMITTED','REACTIVATED'))
event_date	timestamptz	NOT NULL DEFAULT now()
statutory_deadline	date		Calculated deadline under relevant legislation
jurisdiction	char(2)	NOT NULL
notified_at	timestamptz		When customer notification was sent
submitted_to_regulator	timestamptz		When escheatment filing was made
amount_at_event	numeric(18,2)		Balance at time of event
notes	text
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_dormancy_records_account_id on (account_id) - idx_dormancy_records_event_type_date on (event_type, event_date) - idx_dormancy_records_statutory_deadline on (statutory_deadline) WHERE event_type IN ('DORMANCY_CONFIRMED','ESCHEATMENT_NOTIFIED')

Note: statutory_deadline is computed as last_transaction_at + statutory_threshold — anchored to the account's last transaction, not to dormant_at. This ensures the deadline is immune to changes in the configurable dormancy threshold. NZ threshold: 12 months (Unclaimed Money Act 1971 — filed with IRD). AU threshold: 7 years (Banking Act 1959 — filed with ASIC).

---

accounts.escheatment_submissions

One row per jurisdiction-period submission file generated by MOD-008. Tracks the S3 artefact and submission lifecycle. Append-only once submitted. Maintained by MOD-008.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
period_end	date	NOT NULL	Last day of the period covered by this submission
regulator	text	NOT NULL	IRD (NZ) or ASIC (AU)
account_count	int	NOT NULL	Number of accounts included
total_amount	numeric(18,2)	NOT NULL	Total funds escheated in this submission
currency	char(3)	NOT NULL
s3_key	text	NOT NULL UNIQUE	S3 object key where the submission file was written
submission_status	text	NOT NULL CHECK (submission_status IN ('PENDING_OPS','SUBMITTED','ACKNOWLEDGED','REJECTED'))
submitted_at	timestamptz		When ops confirmed manual submission to regulator
regulator_reference	text		Reference returned by regulator on acknowledgement
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_escheatment_submissions_jurisdiction_period on (jurisdiction, period_end DESC) - idx_escheatment_submissions_status on (submission_status) WHERE submission_status = 'PENDING_OPS'

---

accounts.account_party_relationships

The regulated relationship between an account and the parties associated with it. This table makes the Single Depositor View (DCS/SDV), CRS account-holder reporting, and AML beneficial owner identification structurally possible. Do not put a single customer_id on an account for these purposes — joint accounts, trustee holdings, and authorised individuals all require rows here.

Written by MOD-012 (digital onboarding), MOD-007 (account state machine), and MOD-133 (trust account management) when accounts are opened or party relationships change.

Column	Type	Constraints	Description
relationship_id	uuid	PK DEFAULT gen_random_uuid()
account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)
party_id	uuid	NOT NULL	Cross-domain FK to SD02 party.parties(party_id)
relationship_type	text	NOT NULL CHECK (relationship_type IN ('ACCOUNT_HOLDER','JOINT_HOLDER','DEPOSITOR','BENEFICIAL_OWNER','TRUSTEE','NOMINEE','AUTHORISED_INDIVIDUAL','SIGNATORY','CARDHOLDER','APPOINTOR','PROTECTOR','SETTLOR'))
ownership_share_pct	numeric(7,4)		For joint accounts and beneficial ownership; shares must sum to 100 where applicable
can_transact	boolean	NOT NULL DEFAULT false	Whether this party can initiate transactions
can_view	boolean	NOT NULL DEFAULT true	Whether this party can view balances and statements
dcs_relevant	boolean	NOT NULL DEFAULT false	Include in Single Depositor View (SDV) computation
crs_account_holder	boolean	NOT NULL DEFAULT false	CRS-reportable account holder
crs_controlling_person	boolean	NOT NULL DEFAULT false	CRS-reportable controlling person (passive NFEs)
start_date	date	NOT NULL
end_date	date		NULL = current relationship
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_acct_party_rel_account_id on (account_id) WHERE end_date IS NULL - idx_acct_party_rel_party_id on (party_id) WHERE end_date IS NULL - idx_acct_party_rel_dcs on (party_id) WHERE dcs_relevant = true AND end_date IS NULL

DCS note: The Single Depositor View is produced by joining accounts to account_party_relationships filtered on dcs_relevant = true, then aggregating balances per party_id. Joint accounts appear under both holders. Trustee accounts appear under the trustee party. Authorised individuals appear as non-depositor participants.

---

accounts.currency_register

Active ISO 4217 codes permitted on postings (FR-433). Seeded by MOD-004 V001/V006 migrations. Only currencies listed here may appear in accounts.postings.currency or accounts.accounts.currency.

Column	Type	Constraints	Description
code	char(3)	PK	ISO 4217 currency code (e.g. NZD, AUD)
name	text	NOT NULL	Display name
minor_units	int	NOT NULL	Decimal places (2 for NZD/AUD)
is_active	bool	NOT NULL DEFAULT true	Gates posting validation
created_at	timestamptz	NOT NULL DEFAULT now()

---

accounts.fx_rates

Append-only price history for all supported currency pairs. Written by MOD-004's Mod004FxRateIngest Lambda on bank.core.fx_rate_received. Immutable — corrections are new rows (append-only trigger V005).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
source_currency	char(3)	NOT NULL	ISO 4217 source
target_currency	char(3)	NOT NULL	ISO 4217 target
mid_rate	numeric(18,8)	NOT NULL	Mid-market rate (target per 1 source unit)
spread_pct	numeric(8,6)	NOT NULL	Applied spread percentage
provider_id	text	NOT NULL	Rate provider identifier
rate_timestamp	timestamptz	NOT NULL	Point-in-time the rate was valid
received_at	timestamptz	NOT NULL DEFAULT now()	When MOD-004 ingested the rate
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_fx_rates_pair_ts on (source_currency, target_currency, rate_timestamp DESC)

---

accounts.fx_conversions

Audit row per committed 4-leg multi-currency conversion (FR-434, PAY-004 LOG). Immutable — append-only trigger V005. The idempotency_key UNIQUE constraint enables safe replay.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Customer party reference
source_account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)
target_account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)
source_amount	numeric(18,2)	NOT NULL	Amount debited from source account
target_amount	numeric(18,2)	NOT NULL	Amount credited to target account
source_currency	char(3)	NOT NULL
target_currency	char(3)	NOT NULL
applied_rate	numeric(18,8)	NOT NULL	Rate used for this conversion
spread_pct	numeric(8,6)	NOT NULL	Spread applied
rate_timestamp	timestamptz	NOT NULL	Timestamp of the rate row used
cross_border	bool	NOT NULL	True when source_currency ≠ target_currency OR source_jurisdiction ≠ target_jurisdiction (AML-008 AUTO)
jurisdiction	char(2)	NOT NULL	Customer's jurisdiction
idempotency_key	text	UNIQUE NOT NULL	Caller-supplied; UNIQUE prevents double-conversion
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_fx_conversions_party_id on (party_id) - idx_fx_conversions_created_at on (created_at DESC)

---

accounts.daily_trial_balance

Daily per-currency reconciliation snapshot produced by MOD-004's Mod004TrialBalance cron (FR-060, REP-002 CALC). Append-only (V005 trigger). One row per (date, currency, jurisdiction) per run.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
balance_date	date	NOT NULL	Business date
currency	char(3)	NOT NULL
jurisdiction	char(2)	NOT NULL
total_debits	numeric(18,2)	NOT NULL	Sum of all debit postings for the day
total_credits	numeric(18,2)	NOT NULL	Sum of all credit postings for the day
net_position	numeric(18,2)	NOT NULL	Credits minus debits
is_balanced	bool	NOT NULL	True when net_position = 0 (excluding revaluation P&L rows)
run_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_daily_trial_balance_date_ccy on (balance_date, currency, jurisdiction)

---

accounts.fx_revaluation_snapshots

One mutable row per (jurisdiction) holding the most recent EOD revaluation state. Replaced in-place on each revaluation run (not append-only — this is a state table, not an audit table). MOD-004 uses this to compute the P&L delta on each revaluation run.

Column	Type	Constraints	Description
jurisdiction	char(2)	PK CHECK (jurisdiction IN ('NZ','AU'))
last_rate_timestamp	timestamptz	NOT NULL	Timestamp of the rate row used in the last revaluation
last_run_at	timestamptz	NOT NULL	Wall-clock of last revaluation run
open_position_nzd	numeric(18,2)		NZD-equivalent open position at last revaluation
open_position_aud	numeric(18,2)		AUD-equivalent open position
cumulative_pnl	numeric(18,2)	NOT NULL DEFAULT 0.00	Running FX translation gain/loss (FR-435)

Note: Revaluation P&L postings to accounts.postings carry metadata.revaluation_pnl = 'true' and are excluded from the FR-060 trial-balance reconciliation check.

---

accounts.transaction_overrides

Append-only register of customer and back-office corrections to transaction categorisation. Owned by MOD-166. Each row records a single correction event — when a customer or agent overrides the category assigned by MOD-041's ML model. Append-only Cat 1 immutable per ADR-048; a customer changing their mind writes a new row (the latest created_at wins for retrain purposes). Flows to Snowflake via the MOD-042 CDC pipeline as BANK_{ENV}_CORE.RAW.TRANSACTION_OVERRIDES (a TRANSFORM-layer view projecting from the per-domain cdc_events Iceberg union table — see MOD-042 § "Snowflake operator workflow"), where MOD-041's stg_customer_corrections dbt model reads it for weekly retrain.

Column	Type	Constraints	Description
override_id	uuid	PK DEFAULT gen_random_uuid()
posting_id	uuid	NOT NULL REFERENCES accounts.postings(id)	The posting being re-categorised
customer_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties(party_id) — validated by handler against accounts.account_party_relationships; not trusted from caller
override_category_l1	text	NOT NULL	Corrected top-level category
override_category_l2	text		Corrected sub-category (nullable)
override_source	text	NOT NULL CHECK (override_source IN ('customer_app','back_office_agent'))	Origin of the correction
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_transaction_overrides_posting_id on (posting_id) - idx_transaction_overrides_customer_id on (customer_id)

Notes: No updated_at. Immutability enforced by trg_transaction_overrides_immutable (ADR-048 Cat 1 — BEFORE UPDATE OR DELETE raises insufficient_privilege). CDC path: cdc_core_pub is FOR ALL TABLES — this table is auto-included. The Snowflake projection (BANK_{ENV}_CORE.RAW.TRANSACTION_OVERRIDES) is operator-applied via MOD-042-cdc-pipeline-neon-to-iceberg/snowflake/transaction-overrides-projection.sql after MOD-166 deploys and the first Iceberg snapshot lands.

---

Internal accounts register

The following is_internal = true accounts are seeded by migrations and must never appear in customer-facing balance aggregations or Single Depositor View calculations.

account_number	product_code	currency	jurisdiction	Seeded by	Purpose
INT-NZ-NZD-NOSTRO	INTERNAL_FX_NOSTRO_NZD	NZD	NZ	MOD-004 V006	NZD leg of FX nostro pair
INT-AU-AUD-NOSTRO	INTERNAL_FX_NOSTRO_AUD	AUD	AU	MOD-004 V006	AUD leg of FX nostro pair
INT-NZ-FX-PL	INTERNAL_FX_PL_NZD	NZD	NZ	MOD-004 V006	FR-435 FX P&L collector (NZ)
INT-AU-FX-PL	INTERNAL_FX_PL_AU	AUD	AU	MOD-004 V006	FR-435 FX P&L collector (AU)
INT-NZ-UNCLAIMED	INTERNAL_UNCLAIMED_MONEY_NZ	NZD	NZ	MOD-008 V004	Escheated NZD funds (IRD lodgement)
INT-AU-UNCLAIMED	INTERNAL_UNCLAIMED_MONEY_AU	AUD	AU	MOD-008 V004	Escheated AUD funds (ASIC lodgement)
INT-NZ-INTEREST-EXPENSE	INTERNAL_INTEREST_EXPENSE_NZ	NZD	NZ	MOD-005 V004	DEBIT leg for savings interest credits (bank's interest expense)
INT-AU-INTEREST-EXPENSE	INTERNAL_INTEREST_EXPENSE_AU	AUD	AU	MOD-005 V004	DEBIT leg for savings interest credits (bank's interest expense)
INT-NZ-INTEREST-INCOME	INTERNAL_INTEREST_INCOME_NZ	NZD	NZ	MOD-005 V004	CREDIT leg for overdraft interest charges (bank's interest income)
INT-AU-INTEREST-INCOME	INTERNAL_INTEREST_INCOME_AU	AUD	AU	MOD-005 V004	CREDIT leg for overdraft interest charges (bank's interest income)
GL-ECL-EXP-9100-NZ	GL_ECL_EXPENSE_NZD	NZD	NZ	MOD-001 V008	DEBIT leg for ECL expense recognition (NZD) — deterministic UUID 00000000-0000-0000-0000-00000000ec01
GL-ECL-PRV-9200-NZ	GL_ECL_PROVISION_NZD	NZD	NZ	MOD-001 V008	CREDIT leg for ECL provision balance (NZD) — deterministic UUID 00000000-0000-0000-0000-00000000ec02
GL-ECL-EXP-9100-AU	GL_ECL_EXPENSE_AUD	AUD	AU	MOD-001 V008	DEBIT leg for ECL expense recognition (AUD) — deterministic UUID 00000000-0000-0000-0000-00000000ec03
GL-ECL-PRV-9200-AU	GL_ECL_PROVISION_AUD	AUD	AU	MOD-001 V008	CREDIT leg for ECL provision balance (AUD) — deterministic UUID 00000000-0000-0000-0000-00000000ec04

---

Declared pattern: ledger-direct-write

An SD01 module MAY write directly to accounts.postings outside MOD-001's handler when all of the following hold: (1) atomicity is required — multi-leg journal that needs single-transaction guarantees; (2) writes as bank_core_app_user; (3) populates every NOT NULL column including source_module; (4) updates accounts.accounts.{balance, available_balance, version} for each affected account inside the same transaction with pessimistic SELECT … FOR UPDATE in deterministic ID order; (5) emits bank.core.posting_completed or a domain event explicitly subscribed to by MOD-002's ingest rule; (6) caller-supplied idempotency key on a UNIQUE constraint; (7) integration tests include row-hash equivalence check against core.transaction_log. Outside these conditions, modules call MOD-001's posting handler. First adopted by MOD-004 (FX conversion 4-leg journal). Candidate consumer modules: MOD-005, MOD-008, MOD-110.

---

Schema: treasury

treasury.tp_rates

Daily funds transfer pricing rate grid, written by the MOD-086 write-back Lambda after end-of-day Snowflake computation. SD05 credit decisioning and SD01 product rate configuration read from this table — neither system queries Snowflake inline.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
rate_date	date	NOT NULL	Business date this grid applies to
tenor_bucket	text	NOT NULL CHECK (tenor_bucket IN ('ON','1M','3M','6M','1Y','2Y','3Y','5Y','10Y'))	Standard tenor bucket
tp_rate_bps	int	NOT NULL	TP rate in basis points (swap/OIS curve + liquidity premium)
base_curve_bps	int	NOT NULL	Raw swap/OIS curve rate before liquidity premium
liquidity_premium_bps	int	NOT NULL	Liquidity premium overlay applied by Treasury
curve_source_version	text	NOT NULL	Snowflake market.* provider data version identifier
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))	NZ uses NZD swap curve; AU uses AUD swap curve
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_tp_rates_date_tenor on (rate_date DESC, tenor_bucket, jurisdiction) UNIQUE - idx_tp_rates_current on (rate_date DESC) — supports efficient latest-day lookup

Notes: This table is append-only at the application layer. Each business day adds one row per tenor bucket per jurisdiction (18 rows per day). The curve_source_version links back to the Snowflake market.* provider snapshot used, enabling full audit traceability for any rate applied to a product.

---

Schema: loans

Loan accounting objects tightly coupled to the core banking ledger. This schema lives in bank_core (SD01) — distinct from SD05's credit schema in bank_credit — because amortisation schedules require direct write access to accounts.postings (FR-501 revolving auto-post) and direct read access to accounts.accounts, accounts.interest_rates, and accounts.postings. Cross-domain writes from SD05 are prohibited by AP-010.

This schema is for loan accounting modules that require direct ledger access. Credit decisioning, origination, bureau, and scoring remain in SD05's credit schema.

Owned by MOD-112.

loans.amortisation_schedules

Versioned amortisation schedules for instalment (PRD-009) and revolving (PRD-008) loan accounts. One current schedule per account at all times; prior versions retained for audit.

Column	Type	Constraints	Description
schedule_id	uuid	PK DEFAULT gen_random_uuid()
account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)
version	int	NOT NULL	Increments on every recalculation
schedule_type	text	NOT NULL CHECK (schedule_type IN ('PI','IO','REVOLVING'))	Principal+interest, interest-only, or revolving minimum
generated_at	timestamptz	NOT NULL DEFAULT now()
generated_by	text	NOT NULL	Handler identifier that produced this version
rate_at_generation	numeric(8,6)	NOT NULL	Effective annual rate used for this schedule calculation
is_current	boolean	NOT NULL DEFAULT true	Exactly one row per account_id is true at any time

Indexes: - Partial unique index on (account_id) WHERE is_current = true — DB-enforced single-current-schedule invariant - idx_amortisation_schedules_account on (account_id, version DESC)

ADR-048: trg_amortisation_schedules_guard — semi-permissive supersession-only: the only permitted UPDATE is the supersession transition (is_current true → false) applied to the previous current schedule when a new version is INSERTed. All other UPDATE columns, DELETE, and TRUNCATE are blocked unconditionally. New current schedules are always new INSERT rows (never false → true on an existing row).

---

loans.schedule_instalments

Per-payment-period rows for each schedule version. Produced at schedule generation; status updated as payments are received.

Column	Type	Constraints	Description
instalment_id	uuid	PK DEFAULT gen_random_uuid()
schedule_id	uuid	NOT NULL REFERENCES loans.amortisation_schedules(schedule_id)
payment_number	int	NOT NULL	1-based ordinal within the schedule
due_date	date	NOT NULL
opening_balance	numeric(18,2)	NOT NULL	Outstanding principal at start of period
principal_component	numeric(18,2)	NOT NULL	Portion reducing outstanding principal
interest_component	numeric(18,2)	NOT NULL	Interest accrued for the period
instalment_amount	numeric(18,2)	NOT NULL	principal_component + interest_component
closing_balance	numeric(18,2)	NOT NULL	opening_balance - principal_component
status	text	NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','MISSED','PARTIAL'))
paid_at	timestamptz		Set when status transitions to PAID or PARTIAL
paid_amount	numeric(18,2)		Actual amount received

Indexes: - idx_schedule_instalments_schedule on (schedule_id, payment_number) - idx_schedule_instalments_due_date on (due_date) WHERE status = 'PENDING'

ADR-048: trg_schedule_instalments_guard — semi-permissive status-graph trigger: permits only legally valid status transitions: PENDING → PAID, PENDING → MISSED, PENDING → PARTIAL, PARTIAL → PAID, PARTIAL → MISSED, MISSED → PAID. All other mutations (UPDATE on non-status columns, DELETE, TRUNCATE) are blocked unconditionally.

---

loans.amortisation_events

Per-module governance log (append-only). One row per significant schedule lifecycle event for audit traceability.

Column	Type	Constraints	Description
event_id	uuid	PK DEFAULT gen_uuidv7()	Time-ordered
account_id	uuid	NOT NULL
schedule_id	uuid		Associated schedule version (NULL for revolving fee events)
event_type	text	NOT NULL CHECK (event_type IN ('SCHEDULE_GENERATED','SCHEDULE_RECALCULATED','EXTRA_REPAYMENT_OFFERED','EXTRA_REPAYMENT_ACCEPTED','IO_EXPIRY_TRANSITION','HARDSHIP_RESTRUCTURED','REVOLVING_MIN_REPAYMENT_POSTED','REVOLVING_LATE_FEE_ASSESSED'))
triggered_by	text	NOT NULL	Source event or handler (e.g. rate_change_propagated, originate-schedule)
idempotency_key	text	UNIQUE NOT NULL
payload	jsonb	NOT NULL DEFAULT '{}'	Event-specific detail
occurred_at	timestamptz	NOT NULL DEFAULT now()
trace_id	text		X-Ray trace correlation

ADR-048 Cat 1: amortisation_events_reject_mutation — full BEFORE UPDATE OR DELETE OR TRUNCATE block. Raises exception unconditionally; REVOKE UPDATE/DELETE/TRUNCATE on bank_core_app_user is the application-layer complement.

---

Schema: contexts

Non-legal but economically meaningful units. The mechanism for sole trader business views, rental property tracking, and operating context separation without creating fake legal entities. See party and user schema for design rationale.

contexts.operating_contexts

Column	Type	Constraints	Description
context_id	uuid	PK DEFAULT gen_random_uuid()
context_type	text	NOT NULL CHECK (context_type IN ('PERSONAL','SOLE_TRADER_BUSINESS','RENTAL_PROPERTY','RENTAL_PORTFOLIO','COMPANY_VIEW','TRUST_VIEW','COST_CENTRE','PROJECT'))
display_name	text	NOT NULL	e.g. "Ross's Consulting", "12 Smith St Investment"
primary_party_id	uuid	NOT NULL	Cross-domain FK to SD02 party.parties(party_id) — the legal owner
legal_party_id	uuid		Cross-domain FK to SD02 party.parties(party_id) — set only when context IS a registered legal entity
tax_profile_id	uuid	REFERENCES contexts.tax_profiles(tax_profile_id)
status	text	NOT NULL CHECK (status IN ('ACTIVE','SUSPENDED','CLOSED'))
created_at	timestamptz	NOT NULL DEFAULT now()
closed_at	timestamptz

Rules: SOLE_TRADER_BUSINESS: legal_party_id = NULL (the natural person IS the sole trader — no separate entity). COMPANY_VIEW: legal_party_id = the company's party_id. RENTAL_PROPERTY: legal_party_id = NULL unless property is held in a company.

Indexes: - idx_operating_contexts_primary_party on (primary_party_id) WHERE status = 'ACTIVE'

---

contexts.operating_context_memberships

Column	Type	Constraints	Description
membership_id	uuid	PK DEFAULT gen_random_uuid()
context_id	uuid	NOT NULL REFERENCES contexts.operating_contexts(context_id)
party_id	uuid	NOT NULL	Cross-domain FK to SD02 party.parties(party_id)
role_type	text	NOT NULL CHECK (role_type IN ('OWNER','CO_OWNER','MANAGER','ACCOUNTANT','PROPERTY_MANAGER'))
economic_interest_pct	numeric(7,4)		For co-ownership
start_date	date	NOT NULL
end_date	date
created_at	timestamptz	NOT NULL DEFAULT now()

---

contexts.account_context_links

Links an account to an operating context. Enables sole trader accounts to be attributed to a sole trader context, property income accounts to a property context, and virtual allocation of a personal account across contexts.

Column	Type	Constraints	Description
link_id	uuid	PK DEFAULT gen_random_uuid()
account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)
context_id	uuid	NOT NULL REFERENCES contexts.operating_contexts(context_id)
link_type	text	NOT NULL CHECK (link_type IN ('REAL_ACCOUNT','VIRTUAL_ACCOUNT','PRIMARY_SETTLEMENT','REPORTING_ONLY'))
allocation_method	text	NOT NULL CHECK (allocation_method IN ('DIRECT','PERCENT_SPLIT','RULE_BASED'))
default_allocation_pct	numeric(7,4)		For PERCENT_SPLIT — must sum to 100 across all links for an account
effective_from	date	NOT NULL
effective_to	date
created_at	timestamptz	NOT NULL DEFAULT now()

---

contexts.tax_profiles

Tax treatment configuration per operating context. Owns the GST registration, filing frequency, ring-fencing elections, and provisional tax method for each economic unit.

Column	Type	Constraints	Description
tax_profile_id	uuid	PK DEFAULT gen_random_uuid()
context_type	text	NOT NULL	Mirrors operating_contexts.context_type
jurisdiction	char(2)	NOT NULL
gst_registered	boolean	NOT NULL DEFAULT false
gst_number	text
gst_filing_frequency	text		CHECK (IN ('MONTHLY','TWO_MONTHLY','SIX_MONTHLY'))
gst_basis	text		CHECK (IN ('INVOICE','PAYMENTS','HYBRID'))
income_tax_treatment	text		CHECK (IN ('SCHEDULAR','STANDARD','COMPANY','TRUST'))
ring_fencing_flag	boolean	NOT NULL DEFAULT false	True for NZ residential rental (ss EE 1–8 ITA 2007)
ring_fencing_group_id	uuid		Groups properties for portfolio ring-fence offset calculations
provisional_tax_method	text		CHECK (IN ('STANDARD','ESTIMATION','RATIO'))
effective_from	date	NOT NULL
effective_to	date		NULL = currently active
created_at	timestamptz	NOT NULL DEFAULT now()

---

Schema: assets

External and physical assets owned by parties — primarily properties and investment accounts. Not accounts. Not customers. Written by MOD-100 (external asset connector) for financial assets (KiwiSaver/super) and by customer-facing modules for property assets.

assets.assets

Column	Type	Constraints	Description
asset_id	uuid	PK DEFAULT gen_random_uuid()
asset_type	text	NOT NULL CHECK (asset_type IN ('PROPERTY','KIWISAVER','SUPERANNUATION','VEHICLE','INVESTMENT','OTHER'))
display_name	text	NOT NULL	e.g. "12 Smith St, Wellington", "Fisher Funds KiwiSaver"
jurisdiction	char(2)	NOT NULL
property_use_type	text		CHECK (IN ('OWNER_OCCUPIED','RENTAL_RESIDENTIAL','RENTAL_COMMERCIAL','MIXED_USE','HOLIDAY_HOME')) — PROPERTY only
status	text	NOT NULL CHECK (status IN ('ACTIVE','SOLD','TRANSFERRED','CLOSED'))
estimated_value	numeric(18,2)		Customer-provided estimate; not a valuation
current_balance	numeric(18,2)		For financial assets (KiwiSaver/super): current balance from provider
provider_name	text		For financial assets: provider or fund manager name
provider_fund_type	text		For KiwiSaver/super: CONSERVATIVE, BALANCED, GROWTH, AGGRESSIVE
last_refreshed_at	timestamptz		When external data was last retrieved (financial assets)
acquired_date	date
disposed_date	date
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_assets_type_status on (asset_type, status) - idx_assets_last_refreshed on (last_refreshed_at) WHERE asset_type IN ('KIWISAVER','SUPERANNUATION')

---

assets.asset_party_relationships

Column	Type	Constraints	Description
asset_relationship_id	uuid	PK DEFAULT gen_random_uuid()
asset_id	uuid	NOT NULL REFERENCES assets.assets(asset_id)
party_id	uuid	NOT NULL	Cross-domain FK to SD02 party.parties(party_id)
relationship_type	text	NOT NULL CHECK (relationship_type IN ('LEGAL_OWNER','BENEFICIAL_OWNER','CO_OWNER','PROPERTY_MANAGER','MORTGAGEE','MEMBER'))	MEMBER for KiwiSaver/super account holder
ownership_share_pct	numeric(7,4)		For co-ownership
start_date	date	NOT NULL
end_date	date
created_at	timestamptz	NOT NULL DEFAULT now()

---

assets.property_contexts

Connects a property asset to its operating context for tax reporting. One row per property asset.

Column	Type	Constraints	Description
property_context_id	uuid	PK DEFAULT gen_random_uuid()
asset_id	uuid	NOT NULL REFERENCES assets.assets(asset_id) UNIQUE
context_id	uuid	NOT NULL REFERENCES contexts.operating_contexts(context_id)
ring_fencing_group_id	uuid		Groups properties for NZ portfolio ring-fence offset
tax_profile_id	uuid	NOT NULL REFERENCES contexts.tax_profiles(tax_profile_id)
created_at	timestamptz	NOT NULL DEFAULT now()

---

Schema: core

Shared tables used across bank-core modules — fee configuration (MOD-110), trust account governance (MOD-133), and supplementary account metadata. Written by the owning module's migrations; read by multiple SD01 modules.

core.fee_schedule

Fee configuration for product-linked charges. Written by MOD-110. notice_days >= 14 is a CON-005 statutory floor enforced at DB layer (ADR-048).

(Detailed column listing owned by MOD-110 design doc.)

---

core.fee_events

Append-only log of assessed fee events. Written by MOD-110. idempotency_key UNIQUE prevents double-charge. (Detailed column listing owned by MOD-110 design doc.)

---

core.fee_config_propagation_events (MOD-110 V007)

Append-only audit log of fee-parameter changes propagated from MOD-127 (product config panel) via the bank-app.product_config_applied EventBridge event. Cat 1 immutable (ADR-048 trigger core.fee_config_propagation_events_reject_mutation). Written by MOD-110's consume-product-config-applied Lambda.

Column	Type	Constraints	Description
event_id	uuid	PK DEFAULT gen_random_uuid()
proposal_id	uuid	NOT NULL	MOD-127 proposal that triggered the propagation
product_id	text	NOT NULL
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
parameter_key	text	NOT NULL	e.g. fee.account.monthly
previous_value	jsonb		Null on first-ever set
current_value	jsonb	NOT NULL
applied_at	timestamptz	NOT NULL	From MOD-127 event payload
idempotency_key	text	NOT NULL UNIQUE	Keyed on EventBridge event_id — replay-safe
trace_id	uuid
correlation_id	uuid
received_at	timestamptz	NOT NULL DEFAULT now()

v1 is audit-only — records the propagation but does not bridge to core.fee_schedule. The full bridge (syncing parameter values into fee-schedule rows) is a v2 scope item requiring explicit design.

---

core.trust_accounts (MOD-133 V001)

One row per trust account — 1:1 with accounts.accounts. Contains trust-specific metadata not appropriate in the generic accounts table. Created by MOD-133 V001 migration.

Column	Type	Constraints	Description
trust_account_id	uuid	PK DEFAULT gen_random_uuid()
account_id	uuid	NOT NULL UNIQUE REFERENCES accounts.accounts(id)
trust_name	text	NOT NULL	Display name for the trust
trust_type	text	NOT NULL CHECK (trust_type IN ('family_discretionary','testamentary','charitable','commercial_unit'))
trust_deed_document_id	uuid		Cross-domain reference to MOD-073 document store; nullable in DDL, required at activation Lambda gate
established_date	date	NOT NULL	Date the trust was legally established
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
enhanced_dd_completed	boolean	NOT NULL DEFAULT false	AML-004 enhanced CDD gate — must be true before activation
enhanced_dd_completed_at	timestamptz		Stamped when enhanced_dd_completed set to true
review_interval_months	int	CHECK (review_interval_months IS NULL OR review_interval_months > 0)	Periodic CDD review cadence; null = no periodic review configured
next_review_date	date		Computed from review_interval_months when set
activated_at	timestamptz		Stamped by the activate Lambda on successful gate pass
closed_at	timestamptz	CHECK (closed_at IS NULL OR closed_at > activated_at)	Stamped by close Lambda (ADR-048 Cat 2, V004)
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_trust_accounts_account_id on (account_id) UNIQUE - idx_trust_accounts_jurisdiction on (jurisdiction) WHERE closed_at IS NULL

---

core.trust_party_metadata (MOD-133 V002)

Overlay table keyed 1:1 by relationship_id from accounts.account_party_relationships. Holds trust-role semantics and ownership percentages that are distinct from the SDV/CRS ownership_share_pct on the parent relationship row.

Column	Type	Constraints	Description
relationship_id	uuid	PK REFERENCES accounts.account_party_relationships(relationship_id)	1:1 with the relationship row
trust_account_id	uuid	NOT NULL REFERENCES core.trust_accounts(trust_account_id)	Denormalised header reference
trust_role	text	NOT NULL CHECK (trust_role IN ('TRUSTEE','BENEFICIAL_OWNER','APPOINTOR','PROTECTOR','SETTLOR'))
ownership_pct	numeric(5,2)	CHECK (ownership_pct IS NULL OR (ownership_pct >= 0 AND ownership_pct <= 100))	Trust ownership percentage; NULL for discretionary BOs and non-ownership roles
kyc_status	text		Denormalised cache of accounts.kyc_status_mirror.status; activation gate reads mirror for source-of-truth
created_at	timestamptz	NOT NULL DEFAULT now()

Note: accounts.account_party_relationships.ownership_share_pct is the SDV/CRS aggregation field for joint accounts and is NOT used for trust ownership — trust ownership semantics live in this overlay table.

---

core.trust_governance_events (MOD-133 V003)

Append-only immutable log of all trust governance events. Immutability enforced by ADR-048 triggers (V004). One row per lifecycle or review event. idempotency_key UNIQUE prevents duplicate writes on handler retry.

Column	Type	Constraints	Description
event_id	uuid	PK DEFAULT gen_random_uuid()
trust_account_id	uuid	NOT NULL REFERENCES core.trust_accounts(trust_account_id)
account_id	uuid	NOT NULL	Denormalised for read scans
party_id	uuid		Nullable; populated for per-party events (TRUSTEE_ADDED etc.)
relationship_id	uuid		Nullable; populated when event relates to a specific relationship row
event_type	text	NOT NULL CHECK (event_type IN ('TRUST_OPENED','TRUSTEE_ADDED','TRUSTEE_REMOVED','BENEFICIAL_OWNER_ADDED','BENEFICIAL_OWNER_CHANGED','BENEFICIAL_OWNER_REMOVED','APPOINTOR_ADDED','PROTECTOR_ADDED','SETTLOR_RECORDED','TRUST_ACTIVATED','DEED_UPLOADED','DEED_UPDATED','CDD_REVIEW_TRIGGERED','CDD_REVIEW_COMPLETED','TRUST_CLOSED'))	15 event types
due_at	timestamptz		Populated iff event_type = CDD_REVIEW_TRIGGERED (FR-595 30-day SLA)
review_trigger	text	CHECK (review_trigger IN ('TRUSTEE_CHANGE','BENEFICIARY_CHANGE','RISK_ELEVATION','PERIODIC'))	Populated iff event_type = CDD_REVIEW_TRIGGERED
completes_event_id	uuid	REFERENCES core.trust_governance_events(event_id)	Populated iff event_type = CDD_REVIEW_COMPLETED; links back to the TRIGGERED row
actor_kind	text	NOT NULL CHECK (actor_kind IN ('agent','staff','system'))	MOD-047 producer contract
actor_id	text	NOT NULL	Sub of the JWT or system identifier
metadata	jsonb		Arbitrary additional context
idempotency_key	text	UNIQUE NOT NULL	Caller-supplied; UNIQUE prevents duplicate event rows on handler retry
trace_id	text		OpenTelemetry trace identifier
correlation_id	text		Request correlation identifier
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_trust_governance_events_trust_account_id on (trust_account_id, created_at DESC) - idx_trust_governance_events_open_reviews on (trust_account_id, due_at) WHERE event_type = 'CDD_REVIEW_TRIGGERED' AND completes_event_id IS NULL

---

core.community_accounts (MOD-134 V001)

One row per community entity account — 1:1 with accounts.accounts. Holds community-specific metadata. The signing rule is frozen at account opening and governs all subsequent authorisation requests.

Column	Type	Constraints	Description
community_account_id	uuid	PK DEFAULT gen_random_uuid()
account_id	uuid	NOT NULL UNIQUE REFERENCES accounts.accounts(id)
entity_name	text	NOT NULL	Legal name of the community entity (sports club, residents' association, etc.)
entity_type	text	NOT NULL CHECK (entity_type IN ('sports_club','residents_association','incorporated_society','charitable_trust','body_corporate','other'))
constitution_document_id	uuid		Cross-domain reference to MOD-073 document store; nullable in DDL, required at activation Lambda gate (FR-597)
signing_rule	text	NOT NULL CHECK (signing_rule IN ('any_one','any_two','all'))	FR-598 signing rule; frozen at opening; changes require a new governance event
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
enhanced_dd_completed	boolean	NOT NULL DEFAULT false	AML-002 gate — all signatories eIDV-verified before activation
enhanced_dd_completed_at	timestamptz		Stamped when enhanced_dd_completed set to true
activated_at	timestamptz		Stamped by activate Lambda on gate pass
closed_at	timestamptz	CHECK (closed_at IS NULL OR closed_at > activated_at)	Stamped by close Lambda (ADR-048 Cat 2, V005)
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_community_accounts_account_id on (account_id) UNIQUE - idx_community_accounts_jurisdiction on (jurisdiction) WHERE closed_at IS NULL

---

core.community_signatory_metadata (MOD-134 V002)

Overlay table keyed 1:1 by relationship_id from accounts.account_party_relationships. All community entity signatories use relationship_type = 'SIGNATORY' on the parent row. Committee role lives on this overlay.

Column	Type	Constraints	Description
relationship_id	uuid	PK REFERENCES accounts.account_party_relationships(relationship_id)	1:1 with the relationship row
community_account_id	uuid	NOT NULL REFERENCES core.community_accounts(community_account_id)	Denormalised header reference
committee_role	text	NOT NULL CHECK (committee_role IN ('president','treasurer','secretary','authorised_signatory'))
valid_from	date	NOT NULL	Shadows relationship start_date for community semantics
valid_until	date		Shadows relationship end_date; NULL = current signatory
kyc_status	text		Denormalised cache of accounts.kyc_status_mirror.status; activation gate reads mirror for source-of-truth
created_at	timestamptz	NOT NULL DEFAULT now()

---

core.community_authorisations (MOD-134 V003)

One row per outbound transaction-authorisation request. The signing rule and signatory roster are frozen at create time (snapshot semantics). An authorisation reaches COMPLETE when count(distinct approver) >= rule_minimum. FR-598's "not UI-only" enforcement: callers must present a COMPLETE authorisation before a payment posting is accepted.

Column	Type	Constraints	Description
authorisation_id	uuid	PK DEFAULT gen_random_uuid()
community_account_id	uuid	NOT NULL REFERENCES core.community_accounts(community_account_id)
account_id	uuid	NOT NULL	Denormalised for query performance
signing_rule	text	NOT NULL CHECK (signing_rule IN ('any_one','any_two','all'))	Frozen copy of community_accounts.signing_rule at create time
signatory_snapshot	jsonb	NOT NULL	Array of {relationship_id, party_id} active at create time; defines who may approve
required_approvals	int	NOT NULL CHECK (required_approvals > 0)	Derived from signing_rule: 1 / 2 / count(signatories)
status	text	NOT NULL CHECK (status IN ('PENDING','COMPLETE','EXPIRED','CANCELLED')) DEFAULT 'PENDING'
expires_at	timestamptz	NOT NULL CHECK (expires_at > created_at)	Default: created_at + AUTHORISATION_DEFAULT_EXPIRY_SECONDS SSM param (default 72 h)
completed_at	timestamptz	CHECK (completed_at IS NULL OR completed_at >= created_at)	Stamped when final required approval is recorded
idempotency_key	text	UNIQUE NOT NULL	Caller-supplied
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_community_authorisations_account_id_status on (account_id, status) WHERE status = 'PENDING' - idx_community_authorisations_expires_at on (expires_at) WHERE status = 'PENDING'

---

core.community_authorisation_approvals (MOD-134 V003)

One row per signatory's approval of a given authorisation. UNIQUE (authorisation_id, signatory_relationship_id) prevents one signatory counting twice. Only signatories present in the parent authorisation's signatory_snapshot may approve.

Column	Type	Constraints	Description
approval_id	uuid	PK DEFAULT gen_random_uuid()
authorisation_id	uuid	NOT NULL REFERENCES core.community_authorisations(authorisation_id)
signatory_relationship_id	uuid	NOT NULL REFERENCES accounts.account_party_relationships(relationship_id)	Must be present in parent authorisation's signatory_snapshot
party_id	uuid	NOT NULL	Denormalised for audit queries
approved_at	timestamptz	NOT NULL DEFAULT now()
idempotency_key	text	UNIQUE NOT NULL	Caller-supplied; prevents duplicate approvals on handler retry

Constraints: - UNIQUE (authorisation_id, signatory_relationship_id) — one approval per signatory per authorisation

---

core.community_governance_events (MOD-134 V004)

Append-only immutable log of all community account governance events. Immutability enforced by ADR-048 triggers (V005). idempotency_key UNIQUE NOT NULL ensures replay-safe handler retries.

Column	Type	Constraints	Description
event_id	uuid	PK DEFAULT gen_random_uuid()
community_account_id	uuid	NOT NULL REFERENCES core.community_accounts(community_account_id)
account_id	uuid	NOT NULL	Denormalised for read scans
party_id	uuid		Nullable; populated for per-signatory events
relationship_id	uuid		Nullable; populated when event relates to a specific relationship row
event_type	text	NOT NULL CHECK (event_type IN ('COMMUNITY_OPENED','SIGNATORY_ADDED','SIGNATORY_REMOVED','SIGNING_RULE_SET','SIGNING_RULE_CHANGED','COMMUNITY_ACTIVATED','CONSTITUTION_UPLOADED','CONSTITUTION_UPDATED','AUTHORISATION_CREATED','AUTHORISATION_COMPLETED','AUTHORISATION_EXPIRED','AUTHORISATION_CANCELLED','COMMITTEE_REFRESHED','KYC_DEGRADATION_DETECTED','RESTRICTION_APPLIED','RESTRICTION_LIFTED','COMMUNITY_CLOSED'))	17 event types
actor_kind	text	NOT NULL CHECK (actor_kind IN ('agent','staff','system'))	MOD-047 producer contract
actor_id	text	NOT NULL	Sub of the JWT or system identifier
metadata	jsonb		Arbitrary additional context
idempotency_key	text	UNIQUE NOT NULL	Caller-supplied; UNIQUE prevents duplicate event rows on handler retry
trace_id	text		OpenTelemetry trace identifier
correlation_id	text		Request correlation identifier
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_community_governance_events_account_id on (community_account_id, created_at DESC)

---

core.accrual_runs (MOD-005 V002)

One row per accrual run. Identity fields are immutable (V003 trigger rejects UPDATE on identity columns and rejects all DELETE/TRUNCATE); completion fields are mutable (set by completeRun on run finish).

Column	Type	Constraints	Description
run_id	uuid	PK DEFAULT gen_random_uuid()
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))	Run jurisdiction
accrual_date	date	NOT NULL	Business date the accrual is calculated for
run_type	text	NOT NULL CHECK (run_type IN ('DAILY','CORRECTION'))	DAILY = scheduled EOD run; CORRECTION = manual retroactive correction
period_start	date	NOT NULL	Inclusive start of interest period (= accrual_date for DAILY)
period_end	date	NOT NULL	Inclusive end; chk_accrual_run_dates requires period_end >= period_start
correction_of_run_id	uuid	REFERENCES core.accrual_runs(run_id)	Populated for CORRECTION runs; FK to the original DAILY run
status	text	NOT NULL CHECK (status IN ('PENDING','IN_PROGRESS','COMPLETED','FAILED'))	Mutable — updated by completeRun
started_at	timestamptz	NOT NULL DEFAULT now()	Wall-clock start — identity field, immutable
completed_at	timestamptz		Mutable — populated when status reaches terminal state
accounts_processed	int		Mutable — total accounts evaluated
accounts_posted	int		Mutable — accounts with a posted accrual row
accounts_skipped	int		Mutable — accounts skipped (eligibility or zero-cent)
accounts_errored	int		Mutable — accounts that errored
interest_credited	numeric(18,8)		Mutable — total interest credited (savings)
interest_charged	numeric(18,8)		Mutable — total interest charged (overdraft)
by_product	jsonb		Mutable — per product_code breakdown {count, amount}
variance_flags	jsonb		Mutable — accounts where posted amount deviated from expected formula
idempotency_key	text	UNIQUE NOT NULL	Caller-supplied; prevents duplicate run creation on handler retry
actor_kind	text	NOT NULL CHECK (actor_kind IN ('agent','staff','system'))	Who initiated the run
actor_id	text	NOT NULL	Sub of the JWT or system identifier
trace_id	text		OpenTelemetry trace identifier
correlation_id	text		Request correlation identifier
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_accrual_runs_jurisdiction_date on (jurisdiction, accrual_date, run_type) — run-status lookup by date - idx_accrual_runs_correction_of_run_id on (correction_of_run_id) WHERE correction_of_run_id IS NOT NULL

CHECKs: chk_accrual_run_dates (period_end ≥ period_start); chk_accrual_run_completion_coherence (completed_at populated iff status terminal); chk_accrual_run_correction_link_iff_correction (correction_of_run_id IS NOT NULL iff run_type = 'CORRECTION'). All added by V002.

Partial immutability (V003): trg_accrual_runs_immutable_identity — BEFORE UPDATE, rejects changes to identity fields (run_id, jurisdiction, accrual_date, run_type, period_start, period_end, correction_of_run_id, started_at, idempotency_key, actor_kind, actor_id). Completion fields (status, completed_at, accounts_*, interest_*, by_product, variance_flags) remain mutable for completeRun. trg_accrual_runs_no_delete_truncate — blocks all DELETE and TRUNCATE.

---

core.accrual_postings (MOD-005 V002)

One row per interest accrual calculation per (account, accrual_date). Append-only (V003 immutability triggers — Cat 1 full immutability). For DAILY runs a partial UNIQUE index on (account_id, accrual_date) WHERE corrects_accrual_posting_id IS NULL prevents double-accrual; CORRECTION rows are exempt because they reference the original.

Column	Type	Constraints	Description
accrual_posting_id	uuid	PK DEFAULT gen_random_uuid()
run_id	uuid	NOT NULL REFERENCES core.accrual_runs(run_id)	Parent run
account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)	Target account
product_code	text	NOT NULL	Product code at accrual time — captured for audit
accrual_date	date	NOT NULL	Business date this accrual was calculated for
principal_cents	bigint	NOT NULL	Balance at accrual time in minor units — captured for audit
annual_rate	numeric(8,6)	NOT NULL	Rate applied — captured for audit
rate_type	text	NOT NULL	Rate band (BASE, OVERDRAFT, etc.)
day_count_basis	text	NOT NULL DEFAULT 'ACT365'	Day-count convention — ACT/365 in v1; configurable for future per-product override
amount	numeric(18,2)	NOT NULL CHECK (amount <> 0)	Signed — positive for savings credits, negative for overdraft debits. SUM(amount) reconciles to net interest paid.
currency	char(3)	NOT NULL	ISO 4217 — matches account currency
residual_micros	bigint	NOT NULL DEFAULT 0	Sub-cent carry-forward balance in micros. Signed — can be negative ("borrow forward" when banker's rounding pushes the posted cent beyond the total). Carried into the next day's accrual calculation.
posting_id	uuid	NOT NULL REFERENCES accounts.postings(id)	The ledger posting row this accrual produced
corrects_accrual_posting_id	uuid	REFERENCES core.accrual_postings(accrual_posting_id)	Self-FK. NULL for DAILY rows; populated for CORRECTION rows — links correction to the original posting.
metadata	jsonb	NOT NULL DEFAULT '{}'	kind ∈ {CORRECTED, REVERSAL} for correction rows
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - UNIQUE (account_id, accrual_date) WHERE corrects_accrual_posting_id IS NULL — prevents double-accrual on daily path - idx_accrual_postings_run_id on (run_id) - idx_accrual_postings_account_date on (account_id, accrual_date DESC)

---

core.joint_accounts (MOD-125 V001)

Overlay table 1:1 with accounts.accounts. Presence of a row is the discriminator for joint account behaviour; single-holder accounts have no corresponding row. Re-uses existing product codes (NZ_TRANSACTION_01, NZ_SAVINGS_01, AU_TRANSACTION_01, AU_SAVINGS_01).

Column	Type	Constraints	Description
joint_account_id	uuid	PK REFERENCES accounts.accounts(id)	1:1 with accounts.accounts
signing_authority	text	NOT NULL CHECK (signing_authority IN ('any_one','any_two','all'))	Default signing rule for PAYMENT authorisations
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
death_documentation_status	text	NOT NULL DEFAULT 'none' CHECK (death_documentation_status IN ('none','frozen','accepted'))	'none' = no holder death recorded; 'frozen' = death recorded, account frozen pending documentation; 'accepted' = documentation accepted, account unfrozen
death_documentation_id	uuid		Opaque MOD-073 document UUID; populated by acceptDeathDocumentation
activated_at	timestamptz		Stamped when activation gate passes
closed_at	timestamptz	CHECK (closed_at IS NULL OR closed_at >= activated_at)	Temporal constraint — V005 (MOD-125)
idempotency_key	text	UNIQUE NOT NULL	Handler retry safety
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

---

core.joint_holder_metadata (MOD-125 V002)

1:1 overlay on accounts.account_party_relationships rows where relationship_type = 'JOINT_HOLDER'. Carries per-holder lifecycle state that is distinct from the party relationship itself.

Column	Type	Constraints	Description
holder_relationship_id	uuid	PK REFERENCES accounts.account_party_relationships(relationship_id)	1:1 with the JOINT_HOLDER relationship row
is_primary	boolean	NOT NULL DEFAULT false	Primary holder designation (informational only; all holders have equal rights in v1)
holder_status	text	NOT NULL DEFAULT 'active' CHECK (holder_status IN ('active','deceased','removed'))
deceased_at	timestamptz	CHECK (deceased_at IS NOT NULL OR holder_status != 'deceased') — chk_joint_holder_deceased_at_iff_deceased	Populated by recordDeath; NULL unless status = 'deceased'
removed_at	timestamptz	CHECK (removed_at IS NOT NULL OR holder_status != 'removed') — chk_joint_holder_removed_at_iff_removed	Populated by removeHolder; NULL unless status = 'removed'
consent_given	boolean	NOT NULL DEFAULT false	Holder has confirmed consent to be a joint account holder
consent_given_at	timestamptz	CHECK (consent_given_at IS NOT NULL OR NOT consent_given) — chk_joint_holder_consent_at_iff_given	Populated when consent_given flips to true
kyc_status	text	NOT NULL DEFAULT 'PENDING'	Mirror of KYC status from accounts.kyc_status_mirror; read by activation gate
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

---

core.joint_authorisations (MOD-125 V003)

FR-566 multi-holder authorisation service. Standalone table (parallel to core.community_authorisations and core.trust_authorisations) — the signing rule and active holder roster are frozen at create-time (snapshot semantics). An authorisation against a joint account requires all applicable holders to approve before the action is executed.

Column	Type	Constraints	Description
authorisation_id	uuid	PK DEFAULT gen_random_uuid()
joint_account_id	uuid	NOT NULL REFERENCES core.joint_accounts(joint_account_id)
action_type	text	NOT NULL CHECK (action_type IN ('PAYMENT','ADD_HOLDER','REMOVE_HOLDER','CHANGE_SIGNING'))
signing_rule	text	NOT NULL CHECK (signing_rule IN ('any_one','any_two','all'))	Frozen at create-time from the account's signing_authority. ADD/REMOVE/CHANGE_SIGNING always freeze as 'all'.
signatory_snapshot	jsonb	NOT NULL	Array of {holder_relationship_id, party_id, is_primary} — frozen roster at create-time. Approvals are validated against this snapshot; new approvals from holders not in the snapshot are rejected.
status	text	NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','COMPLETE','EXPIRED','CANCELLED'))
expires_at	timestamptz	NOT NULL CHECK (expires_at > created_at) — chk_joint_auth_expires_after_created	Default 24h from SSM authorisation-default-expiry-seconds (vs MOD-134's 72h — joint payments are personal cashflow)
completed_at	timestamptz	CHECK (completed_at IS NULL OR completed_at >= created_at)
idempotency_key	text	UNIQUE NOT NULL
trace_id	text
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_joint_authorisations_account_status on (joint_account_id, status) WHERE status = 'PENDING' - idx_joint_authorisations_expires_at on (expires_at) WHERE status = 'PENDING' — expiry sweeper

---

core.joint_authorisation_approvals (MOD-125 V003)

One row per holder approval within an authorisation. UNIQUE (authorisation_id, holder_relationship_id) prevents double-approval; UNIQUE idempotency_key prevents duplicate writes on retry.

Column	Type	Constraints	Description
approval_id	uuid	PK DEFAULT gen_random_uuid()
authorisation_id	uuid	NOT NULL REFERENCES core.joint_authorisations(authorisation_id)
holder_relationship_id	uuid	NOT NULL REFERENCES accounts.account_party_relationships(relationship_id)	Must be a member of the authorisation's signatory_snapshot
approved_at	timestamptz	NOT NULL DEFAULT now()
idempotency_key	text	UNIQUE NOT NULL
trace_id	text
created_at	timestamptz	NOT NULL DEFAULT now()

Constraints: - UNIQUE (authorisation_id, holder_relationship_id) — one approval per holder per authorisation

---

core.joint_governance_events (MOD-125 V004–V005)

Append-only immutable log of all joint account governance events. Immutability enforced by ADR-048 triggers (V005). idempotency_key UNIQUE NOT NULL ensures replay-safe handler retries.

Column	Type	Constraints	Description
event_id	uuid	PK DEFAULT gen_random_uuid()
joint_account_id	uuid	NOT NULL REFERENCES core.joint_accounts(joint_account_id)
event_type	text	NOT NULL CHECK (event_type IN ('JOINT_ACCOUNT_OPENED','JOINT_ACCOUNT_ACTIVATED','JOINT_ACCOUNT_CLOSED','HOLDER_ADDED','HOLDER_REMOVED','HOLDER_CONSENT_RECORDED','HOLDER_DEATH_RECORDED','DEATH_DOCUMENTATION_ACCEPTED','SIGNING_AUTHORITY_CHANGED','AUTHORISATION_CREATED','AUTHORISATION_COMPLETED','AUTHORISATION_EXPIRED','AUTHORISATION_CANCELLED','SHARE_ADJUSTED'))	14-value CHECK
actor_kind	text	NOT NULL CHECK (actor_kind IN ('agent','staff','system'))
actor_id	text	NOT NULL
detail	jsonb	NOT NULL DEFAULT '{}'
idempotency_key	text	UNIQUE NOT NULL	Prevents duplicate governance event writes on handler retry
trace_id	text
correlation_id	text
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_joint_governance_events_account_id on (joint_account_id, created_at DESC)

---

core.rate_change_proposals (MOD-006 V001)

Maker/checker workflow records for interest rate changes. One row per proposal. Reviewed by a second authorised user (four-eyes rule enforced by no_self_approval CHECK). Rate increases on retail variable products also record the 14-day advance-notice obligation (FR-516). The owning module (MOD-006) exclusively writes accounts.interest_rates on activation; MOD-005 is read-only from this point.

Column	Type	Constraints	Description
proposal_id	uuid	PK DEFAULT gen_uuidv7()
product_code	text	NOT NULL REFERENCES accounts.account_products(product_code)
rate_type	text	NOT NULL CHECK (BASE, BONUS, PENALTY, OVERDRAFT, FIXED_LENDING, VARIABLE_LENDING)
new_annual_rate	numeric(8,6)	NOT NULL CHECK (≥ 0)
previous_annual_rate	numeric(8,6)		NULL on first-ever rate for this product+type
change_kind	text	NOT NULL CHECK (INCREASE, DECREASE)
tp_rate_bps	int		Lending only
margin_bps	int		Lending only
lvr_min	numeric(5,4)		Lending only
lvr_max	numeric(5,4)		Lending only
credit_tier	text		Lending only
change_reason	text	NOT NULL
is_retroactive	boolean	NOT NULL DEFAULT false
proposed_by	uuid	NOT NULL	Cross-domain staff UUID (no FK)
proposed_at	timestamptz	NOT NULL DEFAULT now()
status	text	NOT NULL DEFAULT 'pending' CHECK (pending, approved, rejected, live, withdrawn)
reviewed_by	uuid		Cross-domain staff UUID; populated after review
reviewed_at	timestamptz
review_comment	text
effective_from	date	NOT NULL
applied_at	timestamptz		Populated when rate goes live
rate_id	uuid		accounts.interest_rates.id created at activation
customer_notice_required	boolean	NOT NULL DEFAULT false	True for retail variable-rate increases (FR-516)
customer_notice_published_at	timestamptz		Set when bank.core.rate_change_notified is emitted
before_state	jsonb		Pre-change snapshot for FR-066 audit
idempotency_key	text	NOT NULL
trace_id	uuid	NOT NULL
correlation_id	uuid	NOT NULL

CHECK constraints: no_self_approval (reviewed_by IS NULL OR reviewed_by <> proposed_by); chk_reviewed_at_iff_reviewed_by (reviewed_at and reviewed_by populated together); chk_applied_at_iff_live (applied_at populated iff status='live'); chk_rate_id_iff_live (rate_id populated iff status='live').

---

core.rate_change_events (MOD-006 V002)

Append-only audit log of every governance event in the rate change lifecycle. idempotency_key UNIQUE NOT NULL ensures replay-safe writes. Immutable per ADR-048 Cat 1 — UPDATE/DELETE/TRUNCATE blocked by V003 trigger.

Column	Type	Constraints	Description
event_id	uuid	PK DEFAULT gen_uuidv7()
event_type	text	NOT NULL CHECK (PROPOSAL_CREATED, PROPOSAL_APPROVED, PROPOSAL_REJECTED, PROPOSAL_WITHDRAWN, RATE_CHANGE_NOTIFIED, RATE_CHANGE_ACTIVATED)
proposal_id	uuid		References core.rate_change_proposals
product_code	text
rate_type	text
actor_kind	text	NOT NULL CHECK (agent, staff, system)
actor_id	uuid
metadata	jsonb	NOT NULL DEFAULT '{}'
before_state	jsonb
after_state	jsonb
idempotency_key	text	NOT NULL UNIQUE	ADR-048 duplicate-safe write
trace_id	uuid	NOT NULL
correlation_id	uuid	NOT NULL
occurred_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - uniq_rate_change_events_idempotency_key on (idempotency_key) — UNIQUE - idx_rate_change_events_proposal on (proposal_id, occurred_at DESC) WHERE proposal_id IS NOT NULL - idx_rate_change_events_product on (product_code, rate_type, occurred_at DESC) WHERE product_code IS NOT NULL

Immutability: trg_rate_change_events_no_update, trg_rate_change_events_no_delete, trg_rate_change_events_no_truncate — ADR-048 Cat 1. MOD-006 V003.

---

core.term_deposits (MOD-111 V001)

One row per term deposit instance. Terminal states (MATURED, ROLLED, BROKEN_EARLY) are frozen by DB trigger — no further UPDATEs permitted. Rollover chains are navigable via the previous_deposit_id self-FK.

Column	Type	Constraints	Notes
deposit_id	uuid	PK DEFAULT gen_uuidv7()
account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)	Underlying transactional account
amount	numeric(18,2)	NOT NULL CHECK (amount > 0)	Principal at opening
currency	char(3)	NOT NULL	ISO 4217
term_months	int	NOT NULL CHECK (term_months > 0)	Term in months
annual_interest_rate	numeric(8,6)	NOT NULL	Rate at opening (snapshot)
opened_at	timestamptz	NOT NULL DEFAULT now()
maturity_date	date	NOT NULL
lifecycle_state	text	NOT NULL DEFAULT 'PRE_MATURITY' CHECK (lifecycle_state IN ('PRE_MATURITY','MATURING','MATURED','ROLLED','BROKEN_EARLY'))
default_instruction	text	NULL CHECK (default_instruction IN ('ROLLOVER','WITHDRAW'))	NULL until customer sets or auto-default fires
default_instruction_applied_at	timestamptz	NULL	Set when auto-default stamped (FR-495)
previous_deposit_id	uuid	NULL REFERENCES core.term_deposits(deposit_id)	Rollover chain back-pointer
rolled_into_deposit_id	uuid	NULL REFERENCES core.term_deposits(deposit_id)	Forward pointer (set when ROLLED)
matured_at	timestamptz	NULL	Populated on terminal transition
accrued_interest	numeric(18,2)	NULL	Computed at maturity from core.accrual_postings
final_proceeds	numeric(18,2)	NULL	Principal + accrued interest at maturity
idempotency_key	text	NOT NULL UNIQUE
trace_id	text	NULL
correlation_id	text	NULL
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: idx_term_deposits_account_id on (account_id); idx_term_deposits_maturity_date on (maturity_date) WHERE lifecycle_state IN ('PRE_MATURITY','MATURING'); idx_term_deposits_lifecycle on (lifecycle_state, maturity_date)

CHECK constraints: chk_maturity_after_open (maturity_date > opened_at::date); chk_matured_at_iff_terminal (matured_at IS NOT NULL = lifecycle_state IN ('MATURED','ROLLED','BROKEN_EARLY')); chk_rolled_into_iff_rolled (rolled_into_deposit_id IS NOT NULL = lifecycle_state = 'ROLLED'); chk_proceeds_iff_terminal (final_proceeds IS NOT NULL = lifecycle_state IN ('MATURED','ROLLED','BROKEN_EARLY'))

Immutability: trg_term_deposits_no_update_after_terminal — BEFORE UPDATE, raises when OLD.lifecycle_state IN ('MATURED','ROLLED','BROKEN_EARLY'). trg_term_deposits_no_delete / _no_truncate — unconditional. MOD-111 V003.

---

core.term_deposit_instructions (MOD-111 V002)

Append-only record of every maturity instruction captured for a term deposit (customer-set or auto-defaulted). Immutable once written.

Column	Type	Constraints	Notes
instruction_id	uuid	PK DEFAULT gen_uuidv7()
deposit_id	uuid	NOT NULL REFERENCES core.term_deposits(deposit_id)
instruction_type	text	NOT NULL CHECK (instruction_type IN ('ROLLOVER','WITHDRAW'))
source	text	NOT NULL CHECK (source IN ('CUSTOMER','AUTO_DEFAULT'))	FR-495 auto-default vs customer-captured
term_months	int	NULL	Present for ROLLOVER — term of the new deposit
nominated_account_id	uuid	NULL REFERENCES accounts.accounts(id)	Present for WITHDRAW (internal account only in v1)
idempotency_key	text	NOT NULL UNIQUE
captured_at	timestamptz	NOT NULL DEFAULT now()
trace_id	text	NULL

Immutability: trg_term_deposit_instructions_no_{update,delete,truncate} — raises unconditionally. MOD-111 V002.

---

core.break_cost_disclosures (MOD-111 V002)

One row per early-withdrawal break-cost disclosure. Semi-permissive immutability: accepted_at may be stamped exactly once (NULL → timestamp); all other fields and subsequent mutations are rejected by trigger.

Column	Type	Constraints	Notes
disclosure_id	uuid	PK DEFAULT gen_uuidv7()
deposit_id	uuid	NOT NULL REFERENCES core.term_deposits(deposit_id)
break_cost_amount	numeric(18,2)	NOT NULL CHECK (break_cost_amount >= 0)	Calculated by MOD-111 break-cost service
calculation_basis	text	NOT NULL	Human-readable formula snapshot
remaining_term_days	int	NOT NULL	Days remaining at time of disclosure
annual_interest_rate	numeric(8,6)	NOT NULL	Rate at time of disclosure
currency	char(3)	NOT NULL
disclosed_at	timestamptz	NOT NULL DEFAULT now()
accepted_at	timestamptz	NULL	One-shot acceptance stamp (FR-496)
idempotency_key	text	NOT NULL UNIQUE
trace_id	text	NULL

Immutability: trg_break_cost_disclosures_guard — semi-permissive. Allows exactly one UPDATE: OLD.accepted_at IS NULL AND NEW.accepted_at IS NOT NULL with all other fields unchanged. Rejects re-acceptance, mutation of immutable fields, DELETE, and TRUNCATE. MOD-111 V002.

---

core.term_deposit_events (MOD-111 V003)

Append-only audit log of every lifecycle event on a term deposit. Nine event types. Immutable.

Column	Type	Constraints	Notes
event_id	uuid	PK DEFAULT gen_uuidv7()
deposit_id	uuid	NOT NULL REFERENCES core.term_deposits(deposit_id)
event_type	text	NOT NULL CHECK (event_type IN ('OPENED','INSTRUCTION_CAPTURED','DEFAULT_INSTRUCTION_APPLIED','BREAK_COST_DISCLOSED','BREAK_COST_ACCEPTED','PRE_MATURITY_NOTICE_SENT','ROLLED_OVER','PROCEEDS_DISBURSED','BROKEN_EARLY'))
occurred_at	timestamptz	NOT NULL DEFAULT now()
payload	jsonb	NULL	Event-type-specific data snapshot
idempotency_key	text	NOT NULL UNIQUE
trace_id	text	NULL
correlation_id	text	NULL

Immutability: trg_term_deposit_events_no_{update,delete,truncate} — raises unconditionally. MOD-111 V003. Satisfies NFR-024.

---

core.notice_lodgements (MOD-130 V001)

One row per notice period lodgement. The annual_interest_rate column is snapshotted at lodgement time — never re-read from accounts.interest_rates at withdrawal time (correction §2). amount = NULL means full account balance is released at maturity.

Column	Type	Constraints	Description
lodgement_id	uuid	PK DEFAULT gen_random_uuid()
account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)
product_code	text	NOT NULL REFERENCES accounts.account_products(product_code)	Identifies the notice product (NZ_NOTICE_30, NZ_NOTICE_90, AU_NOTICE_30, AU_NOTICE_90)
notice_period_days	int	NOT NULL	Product-level config; stored at lodgement time
annual_interest_rate	numeric(8,6)	NOT NULL	Rate snapshot at lodgement time. Used for all subsequent penalty calculations — never the current rate.
amount	numeric(18,2)	nullable	NULL = full balance at release
currency	text	NOT NULL
jurisdiction	text	NOT NULL CHECK (NZ, AU)
lodged_at	timestamptz	NOT NULL DEFAULT now()
withdrawal_available_date	date	NOT NULL	lodged_at::date + notice_period_days
status	text	NOT NULL CHECK (pending, withdrawn, cancelled, lapsed)
withdrawn_at	timestamptz	nullable	Set on successful early or maturity withdrawal
cancelled_at	timestamptz	nullable	Set on cancellation (pre-date, if permitted by product)
posting_id	uuid	nullable	MOD-001 posting ID for the release debit
penalty_amount	numeric(18,2)	nullable	Populated on early withdrawal
final_proceeds	numeric(18,2)	nullable	Net amount released to customer
idempotency_key	text	NOT NULL UNIQUE
trace_id	text	nullable	ADR-031 propagation
correlation_id	text	nullable

Indexes: account_id; withdrawal_available_date WHERE status = 'pending'; lodged_at DESC.

CHECK constraints (V001): chk_withdrawal_after_lodged (withdrawal_available_date > lodged_at::date); chk_withdrawn_at_iff_withdrawn; chk_cancelled_at_iff_cancelled.

Immutability (MOD-130 V003): trg_notice_lodgements_no_update_after_terminal — BEFORE UPDATE raises when OLD.status IN ('withdrawn','cancelled','lapsed').

---

core.notice_early_withdrawals (MOD-130 V002)

One-shot penalty disclosure and acceptance record. Semi-permissive immutability trigger (same pattern as core.break_cost_disclosures): accepted_at may transition NULL → timestamp exactly once; all other fields are immutable after insert.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
lodgement_id	uuid	NOT NULL REFERENCES core.notice_lodgements(lodgement_id)
penalty_amount	numeric(18,2)	NOT NULL	Calculated from notice_lodgements.annual_interest_rate snapshot
calculation_basis	jsonb	NOT NULL	{snapshot_annual_interest_rate, notice_period_days, lodgement_amount, formula_version: "v1"}
disclosed_at	timestamptz	NOT NULL DEFAULT now()
accepted_at	timestamptz	nullable	NULL → timestamp transition triggers the actual posting
accepted_via	text	nullable CHECK (app, agent)

Immutability (MOD-130 V002): trg_notice_early_withdrawals_guard — semi-permissive. Permits exactly one UPDATE (accepted_at NULL → timestamp, all other fields verified unchanged). Rejects re-acceptance, any field mutation, DELETE, TRUNCATE. Satisfies CON-005 GATE.

---

core.notice_events (MOD-130 V003)

Per-module governance log. Append-only. One row per lifecycle event on a notice lodgement. Satisfies NFR-024.

Column	Type	Constraints	Description
event_id	uuid	PK DEFAULT gen_uuidv7()
lodgement_id	uuid	NOT NULL REFERENCES core.notice_lodgements(lodgement_id)
event_type	text	NOT NULL CHECK (LODGED, REMINDER_SENT, FUNDS_RELEASED, EARLY_WITHDRAWAL_DISCLOSED, EARLY_WITHDRAWAL_ACCEPTED, CANCELLED, LAPSED)
occurred_at	timestamptz	NOT NULL DEFAULT now()
payload	jsonb	nullable	Event-type-specific snapshot
idempotency_key	text	NOT NULL UNIQUE
trace_id	text	nullable

Immutability (MOD-130 V003): trg_notice_events_no_{update,delete,truncate} — raises unconditionally. Satisfies NFR-024.

---

core.obr_resolution_state (MOD-143 V002 + V004 seed)

Single-row table tracking the bank's current Open Bank Resolution state. The sentinel-PK constraint ensures exactly one row exists at all times. The activate and unwind handlers hold a SELECT … FOR UPDATE lock during state transitions to guarantee atomicity with core.obr_partition_events.

Column	Type	Constraints	Description
id	uuid	PK CHECK (id = '00000000-0000-0000-0000-000000000001'::uuid)	Sentinel constant — enforces single-row invariant
resolution_state	text	NOT NULL CHECK (normal, pre_positioned, activated)	Current state. Seeded as pre_positioned in production by V004.
haircut_pct	numeric(5,4)	nullable	NULL in non-activated states; set at activation (e.g. 0.2000 = 20%)
rbnz_instruction_ref	text	nullable	RBNZ instruction reference; set at activation, cleared on unwind
activated_at	timestamptz	nullable
activated_by	text	nullable	Cognito staff-identity UUID of resolution officer (v1: recorded; cryptographic verification is follow-up)
updated_at	timestamptz	NOT NULL

Mutable — updated via FOR UPDATE lock in activate/unwind handlers. Not subject to immutability triggers; state transitions are the point.

---

core.obr_partition_events (MOD-143 V003)

Append-only audit log of OBR resolution events — activation, unwind, and state verification calls. One row per event. Satisfies FR-633 ("all transitions audited") and REP-001 LOG.

Column	Type	Constraints	Description
event_id	uuid	PK DEFAULT gen_uuidv7()
event_type	text	NOT NULL CHECK (ACTIVATION, UNWIND, STATE_VERIFIED)
resolution_state_after	text	NOT NULL CHECK (normal, pre_positioned, activated)	State at the time of event
rbnz_instruction_ref	text	nullable	Present for ACTIVATION and UNWIND events
haircut_pct	numeric(5,4)	nullable	Snapshot at ACTIVATION
accounts_affected	int	nullable	Count of accounts partitioned (ACTIVATION) or unwound (UNWIND)
total_frozen_amount	numeric(18,2)	nullable	Aggregate at ACTIVATION
total_available_amount	numeric(18,2)	nullable	Aggregate at ACTIVATION
total_writeoff_amount	numeric(18,2)	nullable	Aggregate credit-loss postings at UNWIND
performed_by	text	NOT NULL	Cognito staff-identity UUID
idempotency_key	text	NOT NULL UNIQUE	Prevents duplicate events on Lambda retry
occurred_at	timestamptz	NOT NULL DEFAULT now()
trace_id	text	nullable	ADR-031 propagation

Immutability (MOD-143 V003): trg_obr_partition_events_no_{update,delete,truncate} — raises unconditionally (ADR-048 Cat 1). Satisfies NFR-024 and FR-633.

---

Member equity tables (MOD-118)

Mutual-institution only (tenant flag /bank/{stage}/tenant/institution-type = mutual). All tables in the core schema. party_id fields are soft cross-domain references — no Postgres FK (cross-domain boundary; same pattern as accounts.account_party_relationships).

core.member_register (mutable — V001)

Definitive record of member shareholdings. One row per member. shares_held is updated on every share transaction.

Column	Type	Constraints	Notes
member_id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Soft ref to SD02 party.parties(party_id) — no FK (cross-domain)
membership_number	text	NOT NULL UNIQUE	System-assigned sequential identifier
shares_held	numeric(18,4)	NOT NULL DEFAULT 0	Running balance; updated on each SETTLED share transaction
share_class	text	NOT NULL DEFAULT 'ORDINARY'	ORDINARY only in v1
status	text	NOT NULL DEFAULT 'MEMBER' CHECK (status IN ('APPLICANT','MEMBER','SUSPENDED','FORMER'))
jurisdiction	text	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
joined_at	timestamptz	NOT NULL
resigned_at	timestamptz		Set when status → FORMER
last_voted_at	date		Updated on each AGM vote recorded
idempotency_key	text	NOT NULL UNIQUE
trace_id	text	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

Grants: SELECT, INSERT, UPDATE to bank_core_app_user. No DELETE.

Indexes: (party_id), (status).

core.share_transactions (Cat 1 immutable — V002)

Append-only ledger of share capital movements. One row per transaction in its terminal state. Reversals are new CORRECTION rows (negative shares), not updates. Status at INSERT is final. Satisfies GOV-001 LOG and FR-533.

Column	Type	Constraints	Notes
tx_id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
member_id	uuid	NOT NULL REFERENCES core.member_register(member_id)
tx_type	text	NOT NULL CHECK (tx_type IN ('PURCHASE','REDEMPTION','TRANSFER_IN','TRANSFER_OUT','DIVIDEND_REINVESTMENT','CORRECTION'))
shares	numeric(18,4)	NOT NULL	Negative for REDEMPTION, TRANSFER_OUT, CORRECTION
par_value	numeric(18,2)	NOT NULL	Per-share par value at transaction time
total_amount	numeric(18,2)	NOT NULL	shares × par_value; negative for deductions
posting_id	uuid		Cross-domain ref to SD01 posting — no FK; populated after MOD-001 credit/debit
status	text	NOT NULL CHECK (status IN ('SETTLED','BLOCKED','REVERSED'))	Terminal at INSERT; BLOCKED rows have a corresponding redemption_queue row
blocked_reason	text		capital_gate when CLQ-001 fires
idempotency_key	text	NOT NULL UNIQUE
trace_id	text	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()

Grants: SELECT, INSERT to bank_core_app_user. No UPDATE or DELETE.

Trigger: trg_share_transactions_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row() (ADR-048 Cat 1).

Indexes: (member_id), (created_at).

core.dividend_declarations (mutable — V003)

One row per board-declared dividend. Transitions: DECLARED → PROCESSING (batch start) → PAID (all member payments settled).

Column	Type	Constraints	Notes
declaration_id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
declaration_date	date	NOT NULL
record_date	date	NOT NULL	Shareholding snapshot date
payment_date	date	NOT NULL
rate_per_share	numeric(10,6)	NOT NULL
total_declared	numeric(18,2)	NOT NULL	SUM(shares_held at record_date) × rate_per_share
board_resolution	text	NOT NULL	Board resolution reference
jurisdiction	text	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
status	text	NOT NULL DEFAULT 'DECLARED' CHECK (status IN ('DECLARED','PROCESSING','PAID','REVERSED'))
idempotency_key	text	NOT NULL UNIQUE
trace_id	text	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

Grants: SELECT, INSERT, UPDATE to bank_core_app_user. No DELETE.

core.dividend_payments (mutable — V004)

One row per member per dividend declaration. Populated by the dividend batch Lambda. paid_at is set when MOD-001 confirms the credit posting.

Column	Type	Constraints	Notes
payment_id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
declaration_id	uuid	NOT NULL REFERENCES core.dividend_declarations(declaration_id)
member_id	uuid	NOT NULL REFERENCES core.member_register(member_id)
shares_at_record	numeric(18,4)	NOT NULL	Snapshot at record date
gross_dividend	numeric(18,2)	NOT NULL	shares_at_record × rate_per_share
tax_withheld	numeric(18,2)	NOT NULL DEFAULT 0	Per-jurisdiction withholding rate
net_dividend	numeric(18,2)	NOT NULL	gross_dividend − tax_withheld
posting_id	uuid		Cross-domain ref to SD01 posting — no FK
paid_at	timestamptz		Set on confirmed MOD-001 credit
idempotency_key	text	NOT NULL UNIQUE
trace_id	text	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()
UNIQUE		(declaration_id, member_id)	One payment row per member per declaration

Grants: SELECT, INSERT, UPDATE to bank_core_app_user. No DELETE.

Indexes: (declaration_id), (member_id).

core.redemption_queue (mutable — V005)

FIFO queue for redemptions blocked by the CLQ-001 capital gate. The blocked-redemption-replayer processes this daily in created_at order when capital headroom allows.

Column	Type	Constraints	Notes
queue_id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
tx_id	uuid	NOT NULL REFERENCES core.share_transactions(tx_id)	The BLOCKED share transaction row
member_id	uuid	NOT NULL REFERENCES core.member_register(member_id)
shares_requested	numeric(18,4)	NOT NULL
redemption_amount	numeric(18,2)	NOT NULL
blocked_cet1_ratio	numeric(6,4)	NOT NULL	CET1 ratio at time of block
required_cet1_ratio	numeric(6,4)	NOT NULL	Configured minimum floor at time of block
status	text	NOT NULL DEFAULT 'QUEUED' CHECK (status IN ('QUEUED','PROCESSING','PROCESSED','EXPIRED'))
replayed_at	timestamptz		Set when the replayer first attempts processing
resolved_at	timestamptz		Set when redemption completes
idempotency_key	text	NOT NULL UNIQUE
trace_id	text	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

Grants: SELECT, INSERT, UPDATE to bank_core_app_user. No DELETE.

Indexes: (member_id), (status) WHERE status = 'QUEUED', (created_at) WHERE status = 'QUEUED' (FIFO order for replayer).

---

DB-enforced invariants (ADR-048)

The following constraints and triggers are enforced at the database layer per ADR-048. All are implemented in numbered Flyway migrations (V0NN__describe.sql). Lambda validation is not removed — it provides the user-facing error path and handles config-driven thresholds. The DB layer is the hard floor that no bypass path can circumvent.

Immutability triggers

Table	Migration	Behaviour
accounts.postings	V0NN (MOD-001)	BEFORE UPDATE OR DELETE: raises exception unconditionally
accounts.fx_rates	V005 (MOD-004)	Same pattern
accounts.fx_conversions	V005 (MOD-004)	Same pattern
accounts.daily_trial_balance	V005 (MOD-004)	Same pattern
accounts.daily_balance_snapshots	(RLS)	FOR UPDATE USING (false), FOR DELETE USING (false) — app user has INSERT + SELECT only
core.trust_governance_events	V004 (MOD-133)	BEFORE UPDATE OR DELETE OR TRUNCATE: raises exception unconditionally (ADR-048 Cat 1)
core.community_governance_events	V005 (MOD-134)	Same pattern
core.accrual_postings	V003 (MOD-005)	trg_accrual_postings_no_{update,delete,truncate}: raises exception unconditionally (Cat 1 — full immutability)
core.accrual_runs	V003 (MOD-005)	trg_accrual_runs_no_{delete,truncate}: blocks all DELETE and TRUNCATE. trg_accrual_runs_immutable_identity: BEFORE UPDATE — rejects changes to identity fields; completion fields remain mutable for completeRun.
core.joint_governance_events	V005 (MOD-125)	trg_joint_governance_events_no_{update,delete,truncate}: raises exception unconditionally (ADR-048 Cat 1)
core.rate_change_events	V003 (MOD-006)	trg_rate_change_events_no_{update,delete,truncate}: raises exception unconditionally (ADR-048 Cat 1)
core.term_deposit_events	V003 (MOD-111)	trg_term_deposit_events_no_{update,delete,truncate}: raises exception unconditionally (ADR-048 Cat 1). Satisfies NFR-024.
core.term_deposit_instructions	V002 (MOD-111)	trg_term_deposit_instructions_no_{update,delete,truncate}: raises exception unconditionally (ADR-048 Cat 1)
core.break_cost_disclosures	V002 (MOD-111)	trg_break_cost_disclosures_guard: semi-permissive — permits exactly one UPDATE (accepted_at NULL → timestamp, all other fields unchanged); rejects re-acceptance, field mutation, DELETE, TRUNCATE
core.term_deposits	V003 (MOD-111)	trg_term_deposits_no_update_after_terminal: BEFORE UPDATE raises when OLD.lifecycle_state IN ('MATURED','ROLLED','BROKEN_EARLY'). trg_term_deposits_no_delete / _no_truncate: unconditional block.
core.notice_events	V003 (MOD-130)	trg_notice_events_no_{update,delete,truncate}: raises unconditionally (ADR-048 Cat 1). Satisfies NFR-024.
core.notice_early_withdrawals	V002 (MOD-130)	trg_notice_early_withdrawals_guard: semi-permissive — permits exactly one UPDATE (accepted_at NULL → timestamp, all other fields unchanged); rejects re-acceptance, field mutation, DELETE, TRUNCATE. Satisfies CON-005 GATE.
core.notice_lodgements	V003 (MOD-130)	trg_notice_lodgements_no_update_after_terminal: BEFORE UPDATE raises when OLD.status IN ('withdrawn','cancelled','lapsed').
core.obr_partition_events	V003 (MOD-143)	trg_obr_partition_events_no_{update,delete,truncate}: raises unconditionally (ADR-048 Cat 1). Satisfies NFR-024 and FR-633.
core.share_transactions	V002 (MOD-118)	trg_share_transactions_immutable: BEFORE UPDATE OR DELETE, calls fn_immutable_row() (ADR-048 Cat 1). Satisfies GOV-001 LOG and FR-533.
loans.amortisation_schedules	V003 (MOD-112)	trg_amortisation_schedules_guard — semi-permissive supersession-only: permits ONLY is_current true → false on superseded schedule rows; all other UPDATE columns, DELETE, TRUNCATE blocked unconditionally.
loans.schedule_instalments	V003 (MOD-112)	trg_schedule_instalments_guard — semi-permissive status graph: permits only PENDING → PAID/MISSED/PARTIAL, PARTIAL → PAID/MISSED, MISSED → PAID; all other UPDATE columns, DELETE, TRUNCATE blocked unconditionally.
loans.amortisation_events	V003 (MOD-112)	amortisation_events_reject_mutation — full Cat 1: BEFORE UPDATE OR DELETE OR TRUNCATE raises unconditionally; REVOKE UPDATE/DELETE/TRUNCATE ON bank_core_app_user is the application-layer complement.
core.fee_config_propagation_events	V007 (MOD-110)	core.fee_config_propagation_events_reject_mutation — Cat 1: BEFORE UPDATE OR DELETE OR TRUNCATE raises unconditionally. REVOKE UPDATE/DELETE/TRUNCATE ON bank_core_app_user.

Posting validation triggers

Trigger	Timing	Rule	Notes
trg_posting_account_status	BEFORE INSERT on accounts.postings	Rejects INSERT when accounts.accounts.status IN ('CLOSED','DORMANT')	DB-layer defence; Lambda also checks pre-INSERT as the user-facing error path
trg_posting_obr_channel_gate	BEFORE INSERT on accounts.postings (MOD-143 V001)	Short-circuits when core.obr_resolution_state.resolution_state != 'activated'. When activated: rejects DEBIT legs where NEW.amount > accounts.obr_available_amount. Credits pass unconditionally (FR-635). Returns error code OBR_FROZEN_BALANCE.	Fires after trg_posting_account_status (alphabetical ordering). Hot-path cost minimal — single 1-row lookup on a table permanently in Postgres buffer pool; non-activated short-circuit avoids the amount check.
trg_postings_double_entry	AFTER INSERT on accounts.postings — DEFERRABLE INITIALLY DEFERRED	Per currency: SUM(DEBIT amounts) = SUM(CREDIT amounts) within the same transaction_id	Fires at COMMIT so both legs are visible. Per-currency check handles MOD-004's 4-leg multi-currency conversions correctly.

CHECK constraints

Table	Column	Constraint
accounts.postings	amount	CHECK (amount > 0)
accounts.postings	entry_type	CHECK (entry_type IN ('DEBIT','CREDIT'))
accounts.postings	jurisdiction	CHECK (jurisdiction IN ('NZ','AU'))
accounts.accounts	status	CHECK (status IN ('PENDING','ACTIVE','RESTRICTED','DORMANT','CLOSED'))
accounts.accounts	jurisdiction	CHECK (jurisdiction IN ('NZ','AU'))
accounts.account_products	effective_to	CHECK (effective_to IS NULL OR effective_to > effective_from)
accounts.interest_rates	effective_to	CHECK (effective_to IS NULL OR effective_to > effective_from)
accounts.interest_rates	annual_rate	CHECK (annual_rate >= 0)
treasury.tp_rates	tenor_bucket	CHECK (tenor_bucket IN ('ON','1M','3M','6M','1Y','2Y','3Y','5Y','10Y'))
treasury.tp_rates	jurisdiction	CHECK (jurisdiction IN ('NZ','AU'))
core.fee_schedule	notice_days	CHECK (notice_days >= 14) — CON-005 statutory floor
core.fee_schedule	effective_to	CHECK (effective_to IS NULL OR effective_to > effective_from)
core.fee_events	idempotency_key	UNIQUE — duplicate-safe fee assessment
core.fee_config_propagation_events	idempotency_key	UNIQUE NOT NULL — EventBridge event_id; replay-safe (V007, MOD-110)
core.fee_config_propagation_events	jurisdiction	CHECK (jurisdiction IN ('NZ','AU'))
core.trust_accounts	closed_at	CHECK (closed_at IS NULL OR closed_at > activated_at) — V004 (MOD-133)
core.trust_accounts	review_interval_months	CHECK (review_interval_months IS NULL OR review_interval_months > 0) — V001 column-level
core.trust_party_metadata	ownership_pct	CHECK (ownership_pct IS NULL OR (ownership_pct >= 0 AND ownership_pct <= 100)) — V002 column-level
core.trust_governance_events	event_type	CHECK (event_type IN (15 values)) — see table definition
core.trust_governance_events	idempotency_key	UNIQUE — duplicate-safe governance event write
accounts.accounts	restriction_reason	CHECK (restriction_reason IN ('SANCTIONS','FRAUD_INVESTIGATION','HARDSHIP_ARRANGEMENT','ADMIN','INSUFFICIENT_SIGNATORIES','NOTICE_PENDING')) — INSUFFICIENT_SIGNATORIES added MOD-134 V001; NOTICE_PENDING added MOD-130 V001
accounts.account_state_history	restriction_reason	Same CHECK domain as accounts.accounts.restriction_reason — extended in lockstep by MOD-134 V001 and MOD-130 V001
core.community_accounts	closed_at	CHECK (closed_at IS NULL OR closed_at > activated_at) — V005 (MOD-134)
core.community_accounts	entity_type	CHECK (entity_type IN (6 values)) — see table definition
core.community_accounts	signing_rule	CHECK (signing_rule IN ('any_one','any_two','all')) — column-level
core.community_signatory_metadata	committee_role	CHECK (committee_role IN ('president','treasurer','secretary','authorised_signatory')) — Cat 1 column-level
core.community_authorisations	expires_at	CHECK (expires_at > created_at) — V003 column-level
core.community_authorisations	completed_at	CHECK (completed_at IS NULL OR completed_at >= created_at) — V003 column-level
core.community_authorisation_approvals	(authorisation_id, signatory_relationship_id)	UNIQUE — one approval per signatory per authorisation
core.community_authorisation_approvals	idempotency_key	UNIQUE NOT NULL — duplicate-safe approval write
core.community_governance_events	event_type	CHECK (event_type IN (17 values)) — see table definition
core.community_governance_events	idempotency_key	UNIQUE NOT NULL — duplicate-safe governance event write (orchestrator Q6 addition)
core.accrual_runs	period_start, period_end	chk_accrual_run_dates: period_end >= period_start — V002 (MOD-005)
core.accrual_runs	completed_at, status	chk_accrual_run_completion_coherence: completed_at populated iff status IN ('COMPLETED','FAILED') — V002 (MOD-005)
core.accrual_runs	correction_of_run_id, run_type	chk_accrual_run_correction_link_iff_correction: correction_of_run_id IS NOT NULL iff run_type = 'CORRECTION' — V002 (MOD-005)
core.accrual_runs	idempotency_key	UNIQUE NOT NULL — duplicate-safe run creation on handler retry
core.accrual_postings	amount	chk_accrual_posting_amount_nonzero: amount <> 0 — V002 (MOD-005). amount is signed; positive = savings credit, negative = overdraft debit.
accounts.account_party_relationships	ownership_share_pct	chk_ownership_share_pct_range: ownership_share_pct IS NULL OR (ownership_share_pct >= 0 AND ownership_share_pct <= 100) — V001 (MOD-125). Defensive constraint for the SDV/CRS-shared column; structural underpinning for FR-567 / REP-007.
core.joint_accounts	closed_at	CHECK (closed_at IS NULL OR closed_at >= activated_at) — V005 (MOD-125)
core.joint_accounts	signing_authority	CHECK (signing_authority IN ('any_one','any_two','all')) — column-level
core.joint_accounts	death_documentation_status	CHECK (death_documentation_status IN ('none','frozen','accepted')) — column-level
core.joint_holder_metadata	holder_status	CHECK (holder_status IN ('active','deceased','removed')) — column-level
core.joint_holder_metadata	deceased_at, holder_status	chk_joint_holder_deceased_at_iff_deceased — V002 column-level
core.joint_holder_metadata	removed_at, holder_status	chk_joint_holder_removed_at_iff_removed — V002 column-level
core.joint_holder_metadata	consent_given_at, consent_given	chk_joint_holder_consent_at_iff_given — V002 column-level
core.joint_authorisations	expires_at	chk_joint_auth_expires_after_created: expires_at > created_at — V003 column-level
core.joint_authorisations	completed_at	CHECK (completed_at IS NULL OR completed_at >= created_at) — V003 column-level
core.joint_authorisation_approvals	(authorisation_id, holder_relationship_id)	UNIQUE — one approval per holder per authorisation
core.joint_authorisation_approvals	idempotency_key	UNIQUE NOT NULL — duplicate-safe approval write
core.joint_governance_events	event_type	CHECK (event_type IN (14 values)) — see table definition
core.joint_governance_events	idempotency_key	UNIQUE NOT NULL — duplicate-safe governance event write (ADR-048 requirement, V004)

Testing requirement

Every constraint and trigger above requires a negative integration test: attempt the violating INSERT/UPDATE/DELETE inside a test transaction, assert the expected Postgres exception code or message, ROLLBACK. These tests live in tests/integration/ for the owning module.

---

Cross-domain references

This table	Column	References	Notes
accounts.account_party_relationships	party_id	SD02 party.parties(party_id)	Cross-domain UUID FK; enforced at application layer
accounts.postings	payment_id	SD04 payments.payments(id)	Optional; populated when a payment originates the posting
accounts.pending_holds	payment_id	SD04 payments.payments(id)	Optional; links hold to originating payment authorisation
treasury.tp_rates	(written by)	SD06 MOD-086 write-back Lambda	Read by SD05 credit decisioning and SD01 product rate config
contexts.operating_contexts	primary_party_id	SD02 party.parties(party_id)	Cross-domain UUID FK
assets.asset_party_relationships	party_id	SD02 party.parties(party_id)	Cross-domain UUID FK
assets.assets	(written by)	SD07 MOD-100 external asset connector	KiwiSaver and super balance refreshed daily
core.trust_accounts	trust_deed_document_id	SD08/MOD-073 document store	Cross-domain UUID reference; nullable in DDL, required at activation gate
core.trust_party_metadata	(reads via)	accounts.kyc_status_mirror	Activation gate reads KYC status mirror for trustee and BO verification; mirror populated by MOD-007's bank.kyc.identity_verified consumer
core.community_accounts	constitution_document_id	SD08/MOD-073 document store	Cross-domain UUID reference; nullable in DDL, required at activation gate (FR-597)
core.community_signatory_metadata	(reads via)	accounts.kyc_status_mirror	Activation gate reads KYC mirror for each signatory; same pattern as MOD-133
core.joint_accounts	death_documentation_id	SD08/MOD-073 document store	Cross-domain UUID reference; validated opaquely in v1, replaced with vault lookup once MOD-073 ships
core.joint_holder_metadata	(reads via)	accounts.kyc_status_mirror	Activation gate reads KYC mirror for each joint holder; same pattern as MOD-133/MOD-134

Account ownership note: Account ownership is expressed via accounts.account_party_relationships.party_id — a cross-domain UUID reference to SD02 party.parties(party_id).

Consumers of SD01 data: SD04 reads accounts.accounts for balance and status checks. SD05 reads accounts.accounts and treasury.tp_rates. SD08 reads accounts.postings for transaction history (MOD-070). SD06 reads assets.assets for wealth analytics (MOD-101). Snowflake receives all tables via CDC (MOD-042). SD02 KYC mirror (accounts.kyc_status_mirror) is read by MOD-133 activation gate to verify trustee and beneficial owner eIDV status.