SD04 — Payments data model¶

Database: payments (Neon Postgres, ap-southeast-2) Repo: bank-payments 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: SD04 Payments Processing Platform

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

The payments schema is the primary write domain for MOD-020 (pre-payment validation), MOD-021 (payment limit and velocity controller), MOD-022 (payment audit trail), MOD-023 (transaction fraud scorer), MOD-024 (device and session intelligence), MOD-025 (FX rate lock and conversion), MOD-026 (IFTI/CMIR reporting trigger), MOD-061 (Open Banking API gateway), MOD-067 (trade finance), MOD-081 (payment reconciliation engine), MOD-082 (nostro and FX treasury management), MOD-084 (inbound CDR data retrieval), and MOD-120 (PayID registration and Osko real-time payments). All payment records are immutable once submitted. Payment events provide the full audit trail of state transitions.

Schema: `payments`¶

`payments.payments`¶

The primary payment instruction record. One row per payment instruction submitted, regardless of outcome. Created by MOD-020 during pre-payment validation, before the payment is sent to rails.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	cross-domain ref to SD02 `party.parties(party_id)`
from_account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)	Debit account
to_account_id	uuid	REFERENCES accounts.accounts(id)	Credit account; NULL for outbound external payments
payment_type	text	NOT NULL CHECK (payment_type IN ('INTERNAL','NPP','RTGS','SWIFT','DIRECT_CREDIT','DIRECT_DEBIT','CARD','BPAY','OPEN_BANKING'))
direction	text	NOT NULL CHECK (direction IN ('OUTBOUND','INBOUND','INTERNAL'))
amount	numeric(18,2)	NOT NULL CHECK (amount > 0)	Instructed amount
currency	char(3)	NOT NULL	Instructed currency
settlement_amount	numeric(18,2)		Settled amount (may differ if FX conversion applied)
settlement_currency	char(3)
fx_rate_lock_id	uuid	REFERENCES payments.fx_locks(id)	If FX conversion applied
status	text	NOT NULL CHECK (status IN ('SUBMITTED','VALIDATION_PENDING','VALIDATION_FAILED','PENDING_AUTH','AUTHORISED','PROCESSING','SETTLED','REVERSED','CANCELLED','RETURNED'))
failure_reason	text		Populated when status = VALIDATION_FAILED or RETURNED
payment_reference	text		Customer-supplied reference
end_to_end_reference	text		ISO 20022 end-to-end identifier
beneficiary_name	text		External payee name
beneficiary_account	text		External account number (encrypted at rest)
beneficiary_bank_code	text		BSB (AU) or bank code (NZ)
beneficiary_country	char(2)		For cross-border payments
fraud_score	numeric(5,2)		MOD-023 fraud score at time of submission
sanctions_result_id	uuid	REFERENCES kyc.sanctions_results(id)	Sanctions screen result for this payment
initiated_at	timestamptz	NOT NULL DEFAULT now()
settled_at	timestamptz
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_payments_party_id on (party_id) - idx_payments_from_account_id on (from_account_id) - idx_payments_status on (status) WHERE status NOT IN ('SETTLED','CANCELLED','REVERSED') - idx_payments_initiated_at on (initiated_at DESC) - idx_payments_end_to_end_reference on (end_to_end_reference) WHERE end_to_end_reference IS NOT NULL

`payments.payment_events`¶

Append-only event log of every state transition for a payment. Provides the microsecond-timestamped audit trail required by MOD-022 and PAY-002 / REP-005. Never edited after insertion.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
payment_id	uuid	NOT NULL REFERENCES payments.payments(id)
event_type	text	NOT NULL CHECK (event_type IN ('SUBMITTED','VALIDATION_STARTED','BALANCE_CHECK','LIMIT_CHECK','SANCTIONS_CHECK','FRAUD_CHECK','ACCOUNT_STATUS_CHECK','VALIDATION_PASSED','VALIDATION_FAILED','AUTH_REQUESTED','AUTH_COMPLETED','ROUTING_STARTED','SENT_TO_RAIL','SETTLEMENT_CONFIRMED','REVERSAL_INITIATED','REVERSAL_CONFIRMED','CANCELLATION','RETURN_RECEIVED'))
event_status	text	NOT NULL CHECK (event_status IN ('PASS','FAIL','PENDING','INFO'))
event_timestamp	timestamptz	NOT NULL DEFAULT now()	Wall-clock time with microsecond precision
source_module	text	NOT NULL	Module that recorded this event
actor	text		Staff ID, module ID, or external system for the action
channel	text	NOT NULL DEFAULT 'SYSTEM' CHECK (channel IN ('APP','API','OPEN_BANKING','AGENT','SYSTEM'))	FR-132 — channel on every record; SYSTEM for automated/rail events
ip_address	inet		FR-132 — operator-initiated payments only; NULL for automated/system events
trace_id	uuid		ADR-031 trace propagation; NULL only if no trace context is available
source_event_id	text	NOT NULL	Upstream EventBridge event_id — deduplicates EB redeliveries
details	jsonb	NOT NULL DEFAULT '{}'	Structured event-specific payload
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_payment_events_payment_id_timestamp on (payment_id, event_timestamp) - idx_payment_events_event_type on (event_type) - idx_payment_events_timestamp on (event_timestamp DESC) - idx_payment_events_source_event_id UNIQUE on (source_event_id) — EventBridge redelivery deduplication

Notes: This table is append-only. No updated_at column. Every validation step in MOD-020 records at least one event row. The full payment lifecycle is reconstructable from this table alone. Immutability is enforced by trg_payment_events_immutable (ADR-048 Category 1). FR-132 fields (channel, ip_address) are real columns, not jsonb, to enable NOT NULL enforcement and indexed queries.

`payments.intra_bank_transfers`¶

Rail-specific transfer record for intra-institution payments. One row per transfer attempt. Owned by MOD-141. The audit chain is handled by MOD-020 (payment_events) + MOD-001 (posting_completed) + MOD-022 (SETTLEMENT_CONFIRMED) — MOD-141 emits no bus events directly.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
payment_id	uuid	NOT NULL	Minted by MOD-141 at INSERT; passed to MOD-020 and MOD-001 for audit linkage. No FK constraint — `payments.payments` row is created by MOD-020 concurrently during the same flow
idempotency_key	text	NOT NULL	MOD-141 idempotency key; threads through MOD-020 and MOD-001 unchanged
source_account_id	uuid	NOT NULL	Debit leg account
destination_account_id	uuid	NOT NULL	Credit leg account; caller-supplied as resolved-internal (FR-625 routing detection deferred)
amount	numeric(18,2)	NOT NULL CHECK (amount > 0)
currency	char(3)	NOT NULL	ISO 4217
channel	text	NOT NULL CHECK (channel IN ('APP','API','BACK_OFFICE','BATCH'))	Caller-supplied
status	text	NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','POSTED','FAILED'))
posting_id	uuid		Set on POSTED; FK to SD01 `accounts.postings(id)` at application layer
failure_reason	text		Set on FAILED; e.g. `VALIDATION_FAILED`, `INSUFFICIENT_FUNDS`, `STEP_UP_REQUIRED`, `POSTING_ERROR`
narrative	text		Customer-supplied payment reference
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
trace_id	uuid	NOT NULL	ADR-031
requested_at	timestamptz	NOT NULL	Caller-supplied request timestamp
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger maintains

Indexes: - UNIQUE (idempotency_key) — defence-in-depth replay guard (application-layer idempotency via payments.idempotency_keys is primary) - idx_intra_bank_transfers_payment_id on (payment_id) - idx_intra_bank_transfers_source_account_id on (source_account_id) - idx_intra_bank_transfers_status on (status)

Notes: Uppercase status and channel enums are consistent with the rest of SD04. payment_id is NOT NULL — minted by MOD-141 before the row is inserted, so the value is always known at INSERT time. No FK to payments.payments because the payments row is created by MOD-020 in the same synchronous chain (inserting with a FK would require deferred constraint evaluation or nullable). posting_id is application-layer only for the same reason. STEP_UP_REQUIRED maps to status='FAILED' — the step-up retry loop is v2 (MOD-068 integration).

`payments.bpay_payments`¶

Rail-specific payment record for BPAY bill payments. One row per payment attempt. Owned by MOD-119. AU-only. The audit chain is handled by MOD-020 (payment_events) + MOD-001 (posting_completed) + MOD-022 (SETTLEMENT_CONFIRMED / REVERSAL_CONFIRMED).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
payment_id	uuid	NOT NULL	Minted by MOD-119 at INSERT; passed to MOD-020 and MOD-001 for audit linkage. No FK — `payments.payments` row created by MOD-020 in the same synchronous chain
idempotency_key	text	NOT NULL	MOD-119 idempotency key; threads through MOD-020 and MOD-001 unchanged
trace_id	uuid	NOT NULL	ADR-031
from_account_id	uuid	NOT NULL	Debit account; application-layer only (no FK — cross-DB to SD01)
biller_code	text	NOT NULL	BPAY biller code; looked up in `bpay_biller_cache`
customer_reference	text	NOT NULL	Customer Reference Number (CRN); Luhn or regex validated before MOD-020
amount	numeric(18,2)	NOT NULL CHECK (amount > 0)
currency	char(3)	NOT NULL DEFAULT 'AUD'	BPAY is AU-only
status	text	NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','SUBMITTING','SUBMITTED','SETTLED','RETURNED','DISPUTED','FAILED'))	SUBMITTING = between MOD-001 debit commit and sponsor HTTP call
posting_id	uuid		Set on SUBMITTING; debit posting reference. Application-layer only (no FK)
reversal_posting_id	uuid		Set on RETURNED; credit-back posting reference. Application-layer only
sponsor_reference	text		Set on SUBMITTED; sponsor bank's reference for the submission
batch_id	text		Set by MOD-081 when reconciled against settlement file
failure_reason	text		Set on FAILED; e.g. `SPONSOR_REJECTED`, `VALIDATION_FAILED`, `POSTING_ERROR`
return_reason_code	text		Scheme return code; set on RETURNED
return_reason_text	text		Plain-language description from `reason-mapper.ts`; set on RETURNED
value_date	date		Next AEST business day when submitted past cut-off (17:00 AEST default); NULL if same-day settlement
channel	text	NOT NULL CHECK (channel IN ('APP','API','BACK_OFFICE','BATCH'))
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger maintains

Indexes: - UNIQUE (idempotency_key) — defence-in-depth replay guard - idx_bpay_payments_payment_id on (payment_id) - idx_bpay_payments_biller_code on (biller_code, created_at DESC) - idx_bpay_payments_status on (status) WHERE status NOT IN ('SETTLED','RETURNED','FAILED')

Notes: Uppercase status and channel enums are consistent with the rest of SD04. payment_id is NOT NULL — minted by MOD-119 before the row is inserted. No FK to payments.payments because that row is created by MOD-020 in the same synchronous chain. DISPUTED is in the enum but no v1 code path sets it — reserved for back-office tooling and future MOD-053 case management integration. posting_id and reversal_posting_id are application-layer only for the same cross-DB reason as payments.intra_bank_transfers.

`payments.bpay_biller_cache`¶

Cache of BPAY biller records refreshed daily from the sponsor bank's biller directory. Seeded from V900 migration in dev/uat with known test biller codes (PASS-, FAIL-, RETURN-* patterns per ADR-044). Owned by MOD-119.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
biller_code	text	NOT NULL UNIQUE	BPAY biller code
biller_name	text	NOT NULL	Full biller display name
crn_format	text	NOT NULL CHECK (crn_format IN ('LUHN','REGEX','FIXED_LENGTH','NONE'))	CRN validation algorithm
crn_regex	text		Populated when `crn_format = 'REGEX'` or `'FIXED_LENGTH'`
crn_length	int		Populated when `crn_format = 'FIXED_LENGTH'`
min_amount	numeric(18,2)		Biller-declared minimum payment amount; NULL = no minimum
max_amount	numeric(18,2)		Biller-declared maximum payment amount; NULL = no maximum
accepted_amounts	jsonb		Biller-declared list of accepted specific amounts; NULL = any amount within min/max
is_active	boolean	NOT NULL DEFAULT true	False = biller deregistered; lookup returns 404
source	text	NOT NULL CHECK (source IN ('SPONSOR_DIRECTORY','DEV_SEED'))
last_refreshed_at	timestamptz	NOT NULL	Timestamp of the most recent successful directory refresh for this record
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - UNIQUE (biller_code) — primary lookup key - idx_bpay_biller_cache_active on (biller_code) WHERE is_active = true

Notes: UPSERT on biller_code on daily refresh (INSERT ... ON CONFLICT DO UPDATE). The refresh Lambda tolerates a 404/501 from MOD-157's stub in dev/uat — logs WARN, leaves cache unchanged. accepted_amounts is a jsonb array of numeric(18,2) strings for billers that only accept specific amounts (e.g. council rate instalments).

`payments.payid_registrations`¶

PayID proxy identifier registrations. One row per registered PayID. Customers register a mobile number, email address, or ABN as an NPP proxy identifier via MOD-120. NPP-directory provisioning is synchronous at registration time. AU-only.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
account_id	uuid	NOT NULL	Linked account; application-layer only (no FK — cross-DB to SD01)
party_id	uuid	NOT NULL	Cross-domain ref to SD02 `party.parties(party_id)`
payid_type	text	NOT NULL CHECK (payid_type IN ('MOBILE','EMAIL','ABN','ORGANISATION_ID'))	NPP PayID type
payid_value	text	NOT NULL	Encrypted at rest; the actual mobile number / email address / ABN
display_name	text	NOT NULL	Customer-visible name shown to PayID resolution callers
status	text	NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE','SUSPENDED','DEREGISTERED'))
sponsor_reference	text		Sponsor bank's NPP directory reference; set after successful provisioning
failure_reason	text		Populated when provisioning or deregistration fails
registration_idempotency_key	text	NOT NULL UNIQUE	Idempotency key for FR-541 register endpoint
trace_id	uuid	NOT NULL	ADR-031
jurisdiction	char(2)	NOT NULL DEFAULT 'AU' CHECK (jurisdiction = 'AU')	NPP is AU-only
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger maintains
deregistered_at	timestamptz		Set on DEREGISTERED; per NPP Rule 4.7 deregister ≤ 2 minutes

Indexes: - UNIQUE (payid_type, payid_value) — primary lookup key for inbound PayID resolution; one value per type across the bank - idx_payid_registrations_account_id on (account_id) WHERE status = 'ACTIVE' - idx_payid_registrations_party_id on (party_id)

Notes: Mutable by design — PayID management lifecycle (suspend, reactivate, update display_name, deregister) requires UPDATE. Not append-only. Owned by MOD-120. AU-only (NPP scheme). SUSPENDED rows are retained for audit; DEREGISTERED rows are soft-deleted by status.

`payments.osko_payments`¶

Rail-specific payment record for NPP/Osko real-time payments. One row per payment attempt (OUTBOUND or INBOUND). Owned by MOD-120. AU-only. The audit chain is handled by MOD-020 (payment_events) + MOD-001 (posting_completed) + MOD-022 (SETTLEMENT_CONFIRMED / REVERSAL_CONFIRMED).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
payment_id	uuid	NOT NULL	Minted by MOD-120 at INSERT; passed to MOD-020 and MOD-001 for audit linkage. No FK — `payments.payments` row created by MOD-020 in the same synchronous chain
idempotency_key	text	NOT NULL	MOD-120 idempotency key; threads through MOD-020 and MOD-001 unchanged
trace_id	uuid	NOT NULL	ADR-031
direction	text	NOT NULL CHECK (direction IN ('OUTBOUND','INBOUND'))	OUTBOUND: customer → NPP clearing account. INBOUND: NPP clearing account → customer
from_account_id	uuid	NOT NULL	Debit account; application-layer only (no FK — cross-DB to SD01)
to_account_id	uuid		Credit account; NULL for OUTBOUND to external PayID; application-layer only
payid_value	text		PayID being paid to (OUTBOUND) or received via (INBOUND); NULL for account-number-addressed payments
payid_type	text	CHECK (payid_type IN ('MOBILE','EMAIL','ABN','ORGANISATION_ID'))	NULL when payid_value is NULL
resolved_display_name	text		Name returned by PayID resolution at resolve time
confirmed_display_name	text		Name shown to customer and confirmed; CON-005 evidence field
name_confirmed	boolean	NOT NULL DEFAULT false	FR-542 — must be true before any OUTBOUND SUBMITTING transition; server-enforced
name_confirmed_at	timestamptz		Timestamp when customer confirmed the display name
is_first_time_payee	boolean	NOT NULL DEFAULT false	Scam-Safe Accord — first time this customer has sent to this PayID
acknowledged_high_value	boolean	NOT NULL DEFAULT false	Scam-Safe Accord — customer acknowledged high-value first-time-payee warning; required when is_first_time_payee=true and amount > HIGH_VALUE_THRESHOLD_AUD
end_to_end_id	text	NOT NULL UNIQUE	Server-generated UUID; NPP end-to-end identifier threaded through MOD-020 and sponsor submission
npp_message_id	text		NPP scheme message ID returned by sponsor after submission
amount	numeric(18,2)	NOT NULL CHECK (amount > 0)
currency	char(3)	NOT NULL DEFAULT 'AUD'	NPP is AUD-only
status	text	NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','SUBMITTING','PROCESSING','COMPLETED','RETURNED','DISPUTED','FAILED'))	SUBMITTING = between MOD-001 debit commit and sponsor NPP submission
posting_id	uuid		Set on SUBMITTING; debit posting reference. Application-layer only (no FK)
reversal_posting_id	uuid		Set on RETURNED; credit-back posting reference. Application-layer only
sponsor_reference	text		Sponsor bank's NPP reference; set after sponsor accepts submission
return_reason_code	text		NPP scheme return code; set on RETURNED
return_reason_text	text		Plain-language description from reason-mapper.ts; set on RETURNED
sender_display_name	text		INBOUND only: sender's display name from NPP message
jurisdiction	char(2)	NOT NULL DEFAULT 'AU' CHECK (jurisdiction = 'AU')	NPP is AU-only
channel	text	NOT NULL CHECK (channel IN ('APP','API','BACK_OFFICE'))
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger maintains

Indexes: - UNIQUE (idempotency_key) — defence-in-depth replay guard - UNIQUE (end_to_end_id) — NPP end-to-end identifier uniqueness - idx_osko_payments_payment_id on (payment_id) - idx_osko_payments_from_account_id on (from_account_id) - idx_osko_payments_status on (status) WHERE status NOT IN ('COMPLETED','RETURNED','FAILED') - idx_osko_payments_payid_value on (payid_value) WHERE payid_value IS NOT NULL

Notes: Append-then-update lifecycle (INSERT at PENDING, UPDATE through SUBMITTING → PROCESSING → COMPLETED / RETURNED / FAILED). Not immutable — same pattern as payments.bpay_payments. DISPUTED is in the enum but no v1 code path sets it — reserved for MOD-053 v2 case management integration. posting_id and reversal_posting_id are application-layer only (no FK — cross-DB to SD01). AML-005 LOG surface — full TM-relevant metadata including sender/receiver account IDs, PayID, name-confirmation evidence, and Scam-Safe Accord flags.

`payments.nz_interbank_payments`¶

Rail-specific payment record for NZ interbank payments cleared via the Payments NZ clearing network. One row per payment attempt. Owned by MOD-122. NZ-only. The audit chain is handled by MOD-020 (payment_events) + MOD-001 (posting_completed) + MOD-022 (SETTLEMENT_CONFIRMED / REVERSAL_CONFIRMED).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
payment_id	uuid	NOT NULL	Minted by MOD-122 at INSERT; passed to MOD-020 and MOD-001 for audit linkage. No FK — `payments.payments` row created by MOD-020 in the same synchronous chain
idempotency_key	text	NOT NULL	MOD-122 idempotency key; threads through MOD-020 and MOD-001 unchanged
trace_id	uuid	NOT NULL	ADR-031
from_account_id	uuid		Debit account; application-layer only (no FK — cross-DB to SD01). NULL for INBOUND direction payments
to_account_number	text	NOT NULL	NZ account in `XX-XXXX-XXXXXXX-XX` format (bank-branch-account-suffix); modular-weight checksum validated before MOD-020 pre-payment gate
to_account_name	text	NOT NULL	Beneficiary account name
amount	numeric(18,2)	NOT NULL CHECK (amount > 0)
currency	char(3)	NOT NULL DEFAULT 'NZD' CHECK (currency = 'NZD')	Payments NZ is NZD-only
direction	text	NOT NULL CHECK (direction IN ('OUTBOUND','INBOUND'))
status	text	NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','SUBMITTING','SUBMITTED','SETTLED','RETURNED','FAILED'))	SUBMITTING = between MOD-001 debit commit and Payments NZ sponsor submission
posting_id	uuid		Set on SUBMITTING; debit posting reference. Application-layer only (no FK)
reversal_posting_id	uuid		Set on RETURNED; credit-back posting reference. Application-layer only
clearing_reference	text		Payments NZ clearing network reference; set after scheme accepts the instruction
sponsor_reference	text		Sponsor bank's reference for the submission
return_reason_code	text		Payments NZ scheme return code; set on RETURNED
return_reason_text	text		Plain-language description from `reason-mapper.ts`; set on RETURNED
end_to_end_id	text	UNIQUE	Payments NZ end-to-end identifier threaded through MOD-020 and sponsor submission
cut_off_applied	boolean	NOT NULL DEFAULT false	True when submission was deferred to next business day due to CUT_OFF_TIME_NZST
value_date	date		Business day of settlement; NULL if same-day
channel	text	NOT NULL CHECK (channel IN ('APP','API','BACK_OFFICE'))
jurisdiction	char(2)	NOT NULL DEFAULT 'NZ' CHECK (jurisdiction = 'NZ')
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger maintains

Indexes: - UNIQUE (idempotency_key) — defence-in-depth replay guard - UNIQUE (end_to_end_id) WHERE end_to_end_id IS NOT NULL — Payments NZ end-to-end identifier uniqueness - idx_nz_interbank_payments_payment_id on (payment_id) - idx_nz_interbank_payments_to_account_number on (to_account_number, created_at DESC) - idx_nz_interbank_payments_status on (status) WHERE status NOT IN ('SETTLED','RETURNED','FAILED')

Notes: Append-then-update lifecycle (INSERT at PENDING, UPDATE through SUBMITTING → SUBMITTED → SETTLED / RETURNED / FAILED). NZ account format XX-XXXX-XXXXXXX-XX (bank-branch-account-suffix); modular-weight checksum validated before MOD-020 pre-payment gate. payment_id is NOT NULL — minted by MOD-122 before INSERT. posting_id and reversal_posting_id are application-layer only (no FK — cross-DB to SD01). Cut-off logic controlled by CUT_OFF_TIME_NZST env var (default 15:00 NZST); weekend-only business-day math in v1; NZ public holiday awareness is a v2 gap. direction = 'INBOUND' rows are written on receipt of inbound credit notifications from the Payments NZ sponsor webhook.

`payments.payment_limits`¶

Per-customer, per-payment-type daily and per-transaction limits enforced by MOD-021. A customer may have a set of limits derived from product defaults, CDD tier, and any manual overrides.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	cross-domain ref to SD02 `party.parties(party_id)`
payment_type	text	NOT NULL	Payment type this limit applies to (or 'ALL')
limit_type	text	NOT NULL CHECK (limit_type IN ('PER_TRANSACTION','DAILY','WEEKLY','MONTHLY','ROLLING_30_DAY','APPROVAL_THRESHOLD'))	`ROLLING_30_DAY` = 30-day rolling window velocity limit; `APPROVAL_THRESHOLD` = per-transaction amount above which a multi-party authorisation is required (FR-127). Added by MOD-021 V002.
limit_amount	numeric(18,2)	NOT NULL
currency	char(3)	NOT NULL
jurisdiction	char(2)	NOT NULL
channel	text	NOT NULL DEFAULT 'ALL' CHECK (channel IN ('APP','API','OPEN_BANKING','AGENT','ALL'))	Channel this limit applies to. `ALL` applies to all channels. Added by MOD-021 V002.
limit_source	text	NOT NULL CHECK (limit_source IN ('PRODUCT_DEFAULT','CDD_TIER','MANUAL_OVERRIDE','REGULATORY'))
effective_from	timestamptz	NOT NULL DEFAULT now()
effective_to	timestamptz	CHECK (effective_to IS NULL OR effective_to > effective_from)	NULL = currently active
set_by	text	NOT NULL	Staff ID or module that set this limit
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_payment_limits_party_active on (party_id, payment_type) WHERE effective_to IS NULL - idx_payment_limits_party_id on (party_id)

`payments.limit_change_audit` (MOD-021)¶

Immutable audit record of every limit configuration change. Written by MOD-021 on every successful PUT /limits operation (FR-126). Required for AML-005 compliance — operator changes to limits must be auditable. Append-only.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Party whose limit was changed
payment_type	text	NOT NULL
limit_type	text	NOT NULL
previous_limit_amount	numeric(18,2)		NULL if this is the first limit record for this combination
new_limit_amount	numeric(18,2)	NOT NULL
currency	char(3)	NOT NULL
channel	text	NOT NULL
changed_by	text	NOT NULL	Staff ID or module that made the change
change_reason	text	NOT NULL	Mandatory justification — FR-126
idempotency_key	text	NOT NULL UNIQUE
trace_id	text
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_limit_change_audit_party_id on (party_id, created_at DESC)

`payments.idempotency_keys` (MOD-021, shared SD04 surface)¶

Shared idempotency table for all SD04 handlers that need deduplicated request processing. Populated by the limits-check Lambda and limits-set Lambda in MOD-021. Future SD04 modules should reuse this table rather than creating per-module idempotency tables. DDL owned by MOD-021.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
idempotency_key	text	NOT NULL UNIQUE	Caller-supplied key
module_id	text	NOT NULL	Owning module (e.g. `MOD-021`)
handler	text	NOT NULL	Handler name (e.g. `limits-check`)
response_status	integer	NOT NULL	HTTP status code of the original response
response_body	jsonb	NOT NULL DEFAULT '{}'	Cached response for replay
expires_at	timestamptz	NOT NULL	Key is eligible for sweeper deletion after this timestamp
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_idempotency_keys_key on (idempotency_key) — unique lookup - idx_idempotency_keys_expires_at on (expires_at) — sweeper

`payments.fx_rates`¶

Published FX rates sourced from the bank's treasury feed. MOD-025 reads this table to lock rates for conversions. Rates are inserted by the treasury rate ingestion process; they are never updated.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
from_currency	char(3)	NOT NULL
to_currency	char(3)	NOT NULL
rate	numeric(16,8)	NOT NULL CHECK (rate > 0)	Mid-market rate (buy/sell spread applied by MOD-025 at lock time)
rate_type	text	NOT NULL CHECK (rate_type IN ('MID','BUY','SELL','RETAIL'))
spread_bps	int	NOT NULL DEFAULT 0	Basis points spread applied to derive buy/sell from mid
published_at	timestamptz	NOT NULL	Rate publication timestamp from the treasury feed
valid_until	timestamptz	NOT NULL CHECK (valid_until > published_at)	Rate expiry (typically seconds/minutes ahead)
source	text	NOT NULL	Rate feed source identifier
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_fx_rates_pair_published on (from_currency, to_currency, published_at DESC) - idx_fx_rates_valid_until on (valid_until) WHERE valid_until > now()

Notes: Rate rows are immutable once inserted — enforced by trg_fx_rates_immutable (ADR-048 Category 1). Treasury rate corrections require a new row with an updated published_at; the old row is not deleted.

`payments.fx_locks`¶

A rate lock record created by MOD-025 when a customer initiates an FX conversion. Locks are valid for 30–60 seconds. Once a payment settles using the lock, used_at is populated; unused locks expire.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	cross-domain ref to SD02 `party.parties(party_id)`
from_currency	char(3)	NOT NULL
to_currency	char(3)	NOT NULL
locked_rate	numeric(16,8)	NOT NULL	Exact rate locked for this conversion
from_amount	numeric(18,2)	NOT NULL	Amount to be converted from source currency
to_amount	numeric(18,2)	NOT NULL	Guaranteed to-currency amount at locked rate
locked_at	timestamptz	NOT NULL DEFAULT now()
expires_at	timestamptz	NOT NULL CHECK (expires_at > locked_at)	Lock expiry (locked_at + 30–60 seconds)
used_at	timestamptz		Set when the payment using this lock settles
payment_id	uuid	REFERENCES payments.payments(id)	Linked payment
rate_source_id	uuid	REFERENCES payments.fx_rates(id)	Rate row used to derive this lock
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_fx_locks_party_id on (party_id) - idx_fx_locks_expires_unused on (expires_at) WHERE used_at IS NULL - idx_fx_locks_payment_id on (payment_id) WHERE payment_id IS NOT NULL

`payments.fx_lock_audit` (MOD-025)¶

Append-only lifecycle log for FX rate locks. One row per lifecycle event (ISSUED, CONSUMED, EXPIRED). Immutable per ADR-048 Cat 1 — UPDATE/DELETE/TRUNCATE blocked by V003 trigger.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
fx_lock_id	uuid	NOT NULL	References `payments.fx_locks(id)`
party_id	uuid	NOT NULL	Cross-domain ref to SD02 `party.parties(party_id)`
event_type	text	NOT NULL CHECK (`ISSUED`, `CONSUMED`, `EXPIRED`)	Lifecycle stage
from_currency	char(3)	NOT NULL
to_currency	char(3)	NOT NULL
locked_rate	numeric(16,8)	NOT NULL
mid_rate	numeric(16,8)	NOT NULL	Mid-market rate at lock time
spread_bps	int	NOT NULL
from_amount	numeric(18,2)	NOT NULL
to_amount	numeric(18,2)	NOT NULL
rate_source	text	NOT NULL
rate_source_id	uuid		References `payments.fx_rates(id)`
locked_at	timestamptz		Populated on ISSUED
expires_at	timestamptz		Populated on ISSUED
consumed_at	timestamptz		Populated on CONSUMED
payment_id	uuid		Populated on CONSUMED
conversion_id	uuid		Populated on CONSUMED
source_posting_id	uuid		Populated on CONSUMED
target_posting_id	uuid		Populated on CONSUMED
expired_at	timestamptz		Populated on EXPIRED
trace_id	uuid	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_fx_lock_audit_party_id on (party_id, created_at DESC) - idx_fx_lock_audit_fx_lock_id on (fx_lock_id) - idx_fx_lock_audit_event_type on (event_type, created_at DESC)

Immutability: trg_fx_lock_audit_no_update, trg_fx_lock_audit_no_delete, trg_fx_lock_audit_no_truncate — ADR-048 Cat 1.

`payments.ifti_cmir_queue`¶

Pre-submission queue for IFTI (AU) and CMIR (NZ) threshold reports identified by MOD-026. Records are created when a cross-border payment crosses the reporting threshold. MOD-019 in SD03 consumes this queue to generate the actual regulatory submission.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
payment_id	uuid	NOT NULL REFERENCES payments.payments(id)
party_id	uuid	NOT NULL	cross-domain ref to SD02 `party.parties(party_id)`
report_type	text	NOT NULL CHECK (report_type IN ('IFTI','CMIR'))
jurisdiction	char(2)	NOT NULL
threshold_amount	numeric(18,2)	NOT NULL	Threshold that triggered the report obligation
threshold_currency	char(3)	NOT NULL
queue_status	text	NOT NULL CHECK (queue_status IN ('QUEUED','PROCESSING','SUBMITTED','FAILED'))
triggered_at	timestamptz	NOT NULL DEFAULT now()	When MOD-026 identified the threshold crossing
submission_id	uuid		Soft reference to SD03 `aml.regulatory_submissions(id)`; back-populated by MOD-019 after filing; no FK constraint (cross-domain async write from bank-aml)
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_ifti_cmir_queue_status on (queue_status) WHERE queue_status IN ('QUEUED','PROCESSING') - idx_ifti_cmir_queue_payment_id on (payment_id) - idx_ifti_cmir_queue_triggered_at on (triggered_at DESC)

`payments.settlement_files`¶

Records of settlement file submissions to payment rails (NPP, SWIFT, RTGS, direct entry). One row per batch or individual settlement file sent to a rail. Used by MOD-081 for reconciliation.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
rail	text	NOT NULL CHECK (rail IN ('NPP','RTGS','SWIFT','DIRECT_ENTRY','BPAY','CARD'))
jurisdiction	char(2)	NOT NULL
file_reference	text	NOT NULL UNIQUE	Bank-generated file reference
payment_count	int	NOT NULL	Number of payments in this file
total_amount	numeric(18,2)	NOT NULL
currency	char(3)	NOT NULL
file_status	text	NOT NULL CHECK (file_status IN ('GENERATED','SUBMITTED','ACKNOWLEDGED','SETTLED','PARTIALLY_SETTLED','REJECTED'))
generated_at	timestamptz	NOT NULL DEFAULT now()
submitted_at	timestamptz
settled_at	timestamptz
s3_key	text		S3 object key for the settlement file
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_settlement_files_file_status on (file_status) WHERE file_status NOT IN ('SETTLED','REJECTED') - idx_settlement_files_rail_generated on (rail, generated_at DESC) - idx_settlement_files_file_reference on (file_reference)

`payments.reconciliation_exceptions`¶

Payments or settlement items that MOD-081 could not match to a settlement confirmation, or that settled for an amount different from instructed. Requires analyst resolution.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
payment_id	uuid	REFERENCES payments.payments(id)	NULL for inbound unmatched items
settlement_file_id	uuid	REFERENCES payments.settlement_files(id)
exception_type	text	NOT NULL CHECK (exception_type IN ('UNMATCHED_OUTBOUND','SHORT_SETTLED','OVER_SETTLED','DUPLICATE','MISSING_RETURN','UNMATCHED_INBOUND'))
instructed_amount	numeric(18,2)
settled_amount	numeric(18,2)
variance	numeric(18,2)		instructed_amount - settled_amount
currency	char(3)	NOT NULL
exception_status	text	NOT NULL CHECK (exception_status IN ('OPEN','UNDER_REVIEW','RESOLVED','ESCALATED'))
detected_at	timestamptz	NOT NULL DEFAULT now()
resolved_at	timestamptz
resolved_by	text
resolution_notes	text
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_recon_exceptions_status on (exception_status) WHERE exception_status IN ('OPEN','UNDER_REVIEW') - idx_recon_exceptions_detected_at on (detected_at DESC) - idx_recon_exceptions_payment_id on (payment_id) WHERE payment_id IS NOT NULL

`payments.nostro_positions`¶

Current and intraday positions for correspondent bank (nostro) accounts, maintained by MOD-082. Used to gate high-value payments when nostro funding is insufficient.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
correspondent_bank	text	NOT NULL	BIC or internal code of the correspondent
nostro_account_ref	text	NOT NULL	Account reference at the correspondent
currency	char(3)	NOT NULL
opening_balance	numeric(18,2)	NOT NULL DEFAULT 0.00
current_balance	numeric(18,2)	NOT NULL DEFAULT 0.00	Updated intraday
available_balance	numeric(18,2)	NOT NULL DEFAULT 0.00	After pending outflows
minimum_threshold	numeric(18,2)	NOT NULL DEFAULT 0.00	Below this, auto-funding is triggered
maximum_threshold	numeric(18,2)	NULL	Above this, over-funding alert is raised (FR-162). Added by MOD-082 V001.
as_of	timestamptz	NOT NULL	Timestamp of the most recent position update
position_date	date	NOT NULL	Business date this position belongs to
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_nostro_positions_currency_date on (currency, position_date DESC) - idx_nostro_positions_correspondent on (correspondent_bank, position_date)

`payments.nostro_adjustment_audit` (MOD-082)¶

Append-only log of every manual adjustment to a nostro position. One row per adjustment event. Captures full before/after balance snapshot so the record is self-contained for audit reconstruction without joining the live nostro_positions table. Immutable per ADR-048 Cat 1 — UPDATE/DELETE/TRUNCATE blocked by V003 trigger.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
nostro_position_id	uuid	NOT NULL	References `payments.nostro_positions(id)`
correspondent_bank	text	NOT NULL
nostro_account_ref	text	NOT NULL
currency	char(3)	NOT NULL
adjustment_type	text	NOT NULL CHECK (`FUNDING`, `DRAIN`, `THRESHOLD_CHANGE`, `CORRECTION`, `FX_REVALUATION`)
amount_delta	numeric(18,2)	NOT NULL	Signed delta; positive for FUNDING, negative for DRAIN, 0 for THRESHOLD_CHANGE
previous_current_balance	numeric(18,2)	NOT NULL	Balance before this adjustment
previous_available_balance	numeric(18,2)	NOT NULL
previous_minimum_threshold	numeric(18,2)	NOT NULL
previous_maximum_threshold	numeric(18,2)		NULL if not set before adjustment
new_current_balance	numeric(18,2)	NOT NULL	Balance after this adjustment
new_available_balance	numeric(18,2)	NOT NULL
new_minimum_threshold	numeric(18,2)	NOT NULL
new_maximum_threshold	numeric(18,2)
reason_code	text	NOT NULL	Machine key e.g. `CORR_BANK_TOPUP`, `RECON_BREAK_CORRECTION`
reason_notes	text		Human notes
operator_id	text	NOT NULL	Asserted staff identity (cross-domain; MOD-068 authorisation wired in future)
operator_role	text	NOT NULL	e.g. `TREASURY_SENIOR`
trace_id	uuid	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()

Immutability: trg_nostro_adjustment_audit_immutable — ADR-048 Cat 1.

`payments.nostro_position_eod_snapshots` (MOD-082)¶

Daily end-of-day snapshots for each nostro corridor (FR-163). One row per (correspondent_bank, nostro_account_ref, currency, snapshot_date). Immutable per ADR-048 Cat 1 — UPDATE/DELETE/TRUNCATE blocked by V005 trigger.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
snapshot_date	date	NOT NULL	Business date
jurisdiction	char(2)	NOT NULL CHECK (`NZ`, `AU`)
correspondent_bank	text	NOT NULL
nostro_account_ref	text	NOT NULL
currency	char(3)	NOT NULL
opening_balance	numeric(18,2)	NOT NULL
closing_balance	numeric(18,2)	NOT NULL
closing_available	numeric(18,2)	NOT NULL
minimum_threshold	numeric(18,2)	NOT NULL	Snapshot of threshold at EOD
maximum_threshold	numeric(18,2)
open_settlement_count	int	NOT NULL DEFAULT 0	FR-163 open settlement obligations
open_settlement_total	numeric(18,2)	NOT NULL DEFAULT 0.00
base_currency	char(3)	NOT NULL DEFAULT 'NZD'
unrealised_pnl_base	numeric(18,2)	NOT NULL DEFAULT 0.00	Unrealised P&L in base currency
fx_rate_applied	numeric(16,8)		Rate used for base-currency conversion
rate_source	text		MOD-085 rate source reference
snapshot_taken_at	timestamptz	NOT NULL DEFAULT now()
trace_id	uuid	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()

Unique constraint: (snapshot_date, correspondent_bank, nostro_account_ref, currency)

Indexes: - idx_nostro_eod_snap_date on (snapshot_date DESC, jurisdiction) - idx_nostro_eod_snap_currency on (currency, snapshot_date DESC)

Immutability: trg_nostro_position_eod_snapshots_immutable — ADR-048 Cat 1.

`payments.open_banking_consents`¶

Records of Open Banking / CDR consents granted by customers, managed by MOD-061 (AU CDR) and the NZ Open Banking gateway. Each row represents a granted consent scope for a data recipient.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	cross-domain ref to SD02 `party.parties(party_id)`
data_recipient_id	text	NOT NULL	Accredited data recipient identifier
consent_type	text	NOT NULL CHECK (consent_type IN ('CDR_AU','NZ_OPEN_BANKING'))
scopes	jsonb	NOT NULL	Granted consent scopes (e.g. ["bank:accounts.basic:read","bank:transactions:read"])
consent_status	text	NOT NULL CHECK (consent_status IN ('ACTIVE','WITHDRAWN','EXPIRED','REVOKED'))
granted_at	timestamptz	NOT NULL DEFAULT now()
expires_at	timestamptz	NOT NULL	CDR maximum 12-month consent duration
withdrawn_at	timestamptz
withdrawal_reason	text
sharing_duration_days	int	NOT NULL	Customer-selected sharing period
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_open_banking_consents_party_active on (party_id) WHERE consent_status = 'ACTIVE' - idx_open_banking_consents_expires_at on (expires_at) WHERE consent_status = 'ACTIVE' - idx_open_banking_consents_recipient_party on (data_recipient_id, party_id)

`payments.device_intelligence` (MOD-024)¶

Per-device aggregated fraud-grade profile. One row per device_fingerprint_hash. Mutable — counters, trust score, and flagged status are updated on every observe call and every anomaly event. Distinct from MOD-068's access.device_registry (auth-grade trust-for-login record); both are keyed by the same device_fingerprint_hash for cross-correlation.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
device_fingerprint_hash	text	NOT NULL UNIQUE	SHA-256 of device fingerprint fields; same key used by MOD-068 `access.device_registry`
customer_id	uuid	NOT NULL	First customer to use this device; cross-domain ref to SD02
os_version	text
app_version	text
screen_resolution	text
network_type	text		e.g. WIFI, CELLULAR_4G, CELLULAR_5G
emulator_detected	boolean	NOT NULL DEFAULT false	FR-139 emulator flag
rooted_device	boolean	NOT NULL DEFAULT false	FR-139 rooted/jailbroken flag
trust_score	int	NOT NULL DEFAULT 100 CHECK (trust_score BETWEEN 0 AND 100)	Decremented on anomaly; incremented on clean sessions
flagged_as_fraudulent	boolean	NOT NULL DEFAULT false	Set true when `bank.payments.fraud_alert_raised` received from MOD-023
first_seen_at	timestamptz	NOT NULL DEFAULT now()
last_seen_at	timestamptz	NOT NULL DEFAULT now()
observe_count	int	NOT NULL DEFAULT 1	Cumulative observe API calls from this device
anomaly_count	int	NOT NULL DEFAULT 0	Cumulative anomalies detected
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

Indexes: idx_device_intelligence_hash on (device_fingerprint_hash) — UNIQUE; idx_device_intelligence_customer on (customer_id); idx_device_intelligence_flagged partial WHERE flagged_as_fraudulent = true.

`payments.session_observations` (MOD-024)¶

Per-session device fingerprint snapshot captured on every observe API call. FR-140 7-year retention. Append-only — ADR-048 Cat 1 immutable.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
session_id	uuid	NOT NULL	Cross-reference to MOD-068 session
customer_id	uuid	NOT NULL
device_fingerprint_hash	text	NOT NULL REFERENCES payments.device_intelligence(device_fingerprint_hash)
os_version	text
app_version	text
screen_resolution	text
network_type	text
ip_region	text		Coarse geolocation; from MOD-068 session_created event
emulator_detected	boolean	NOT NULL DEFAULT false
rooted_device	boolean	NOT NULL DEFAULT false
observed_at	timestamptz	NOT NULL DEFAULT now()
trace_id	uuid
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: idx_session_obs_session_id on (session_id); idx_session_obs_device_hash on (device_fingerprint_hash); idx_session_obs_customer_observed on (customer_id, observed_at DESC).

Immutability: trg_session_observations_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row() (MOD-024 V001). ADR-048 Cat 1. FR-140 7-year retention enforced via Snowflake CDC (MOD-042).

`payments.device_anomalies` (MOD-024)¶

Per-anomaly record. AML-005 LOG surface. Append-only — ADR-048 Cat 1 immutable.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()	Referenced as `anomaly_id` in the `device_anomaly_detected` event
session_id	uuid	NOT NULL
customer_id	uuid	NOT NULL
device_fingerprint_hash	text	NOT NULL
anomaly_type	text	NOT NULL CHECK (anomaly_type IN ('NEW_DEVICE','KNOWN_FRAUD_DEVICE','IMPOSSIBLE_TRAVEL','EMULATOR_DETECTED','ROOTED_DEVICE'))
severity	text	NOT NULL CHECK (severity IN ('INFO','WARN','CRITICAL'))
action_recommended	text	NOT NULL CHECK (action_recommended IN ('NONE','STEP_UP','BLOCK'))
detail	jsonb	NOT NULL DEFAULT '{}'	Anomaly-type-specific detail (e.g. distance_km for IMPOSSIBLE_TRAVEL)
detected_at	timestamptz	NOT NULL DEFAULT now()
trace_id	uuid
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: idx_device_anomalies_session on (session_id); idx_device_anomalies_customer_detected on (customer_id, detected_at DESC); idx_device_anomalies_type_severity on (anomaly_type, severity).

Immutability: trg_device_anomalies_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row() (MOD-024 V001). ADR-048 Cat 1. AML-005 LOG surface; AML behavioural model feed via CDC to Snowflake (MOD-042).

`payments.fraud_scores` (MOD-023)¶

Append-only fraud scoring record for every payment submitted. One row per (payment_id, model_version) scoring run. DT-005 LOG surface — captures every input feature, weight snapshot, and output score for model audit. ADR-048 Cat 1 immutable.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
payment_id	uuid	NOT NULL REFERENCES payments.payments(id)
customer_id	uuid	NOT NULL	Cross-domain ref to SD02 `party.parties(party_id)`
score	int	NOT NULL CHECK (score BETWEEN 0 AND 1000)	Composite fraud score
action	text	NOT NULL CHECK (action IN ('PASS','STEP_UP','BLOCK'))	Outcome of threshold evaluation
model_version	text	NOT NULL	Scorer version e.g. `rule-v1.0.0`
feature_weights	jsonb	NOT NULL	Named weight constants snapshot at score time (DT-005 audit)
input_features	jsonb	NOT NULL	Raw feature values used in this scoring run
threshold_warn	int	NOT NULL	AppConfig WARN threshold applied
threshold_block	int	NOT NULL	AppConfig BLOCK threshold applied
idempotency_key	text	NOT NULL UNIQUE	Prevents duplicate scoring of the same payment
trace_id	uuid		ADR-031 propagation
scored_at	timestamptz	NOT NULL DEFAULT now()
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: (payment_id) UNIQUE (one score per payment in v1); (customer_id, scored_at DESC); (action, scored_at DESC) partial WHERE action <> 'PASS'.

Immutability: trg_fraud_scores_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row() (MOD-023 V001). ADR-048 Cat 1. DT-005 LOG compliance requires the full scoring context to be preserved for model audit.

`payments.scam_scored_payees` (MOD-023)¶

Mutable scam-payee cache for the CAP-040 score-payee API. One row per BSB+account number or NZ bank number. v1 is a stub — is_scam=false for everything not in the dev seed. When MOD-149 ships, an EventBridge consumer upserts rows from bank.scam.scam_database_updated.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
account_identifier	text	NOT NULL UNIQUE	Normalised BSB+account or NZ bank number
is_scam	boolean	NOT NULL DEFAULT false	True when confirmed scam payee
reason_code	text	NOT NULL	e.g. `SCAM_DB_CONFIRMED` / `NO_SCAM_DB_AVAILABLE`
source	text	NOT NULL CHECK (source IN ('SCAM_DATABASE','MANUAL_FLAG','DEV_SEED'))
flagged_at	timestamptz		When first flagged
last_updated	timestamptz	NOT NULL DEFAULT now()
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: (account_identifier) UNIQUE; (is_scam) partial WHERE is_scam = true.

DB-enforced invariants (ADR-048)¶

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

Immutability triggers¶

Table	Trigger	Category
`payments.payment_events`	`trg_payment_events_immutable` — BEFORE UPDATE OR DELETE, calls `fn_immutable_row()`	Cat 1
`payments.fx_rates`	`trg_fx_rates_immutable` — BEFORE UPDATE OR DELETE, calls `fn_immutable_row()`	Cat 1
`payments.fx_lock_audit`	`trg_fx_lock_audit_no_update`, `trg_fx_lock_audit_no_delete`, `trg_fx_lock_audit_no_truncate` — MOD-025 V003	Cat 1
`payments.nostro_adjustment_audit`	`trg_nostro_adjustment_audit_immutable` — MOD-082 V003	Cat 1
`payments.nostro_position_eod_snapshots`	`trg_nostro_position_eod_snapshots_immutable` — MOD-082 V005	Cat 1
`payments.session_observations`	`trg_session_observations_immutable` — BEFORE UPDATE OR DELETE, calls `fn_immutable_row()` (MOD-024 V001)	Cat 1
`payments.device_anomalies`	`trg_device_anomalies_immutable` — BEFORE UPDATE OR DELETE, calls `fn_immutable_row()` (MOD-024 V001)	Cat 1
`payments.fraud_scores`	`trg_fraud_scores_immutable` — BEFORE UPDATE OR DELETE, calls `fn_immutable_row()` (MOD-023 V001). DT-005 LOG compliance; FR-136.	Cat 1

Other tables (payments.payments, payments.payment_limits, payments.fx_locks, payments.settlement_files, payments.reconciliation_exceptions, payments.nostro_positions, payments.open_banking_consents, payments.ifti_cmir_queue, payments.device_intelligence, payments.scam_scored_payees, payments.bpay_payments, payments.bpay_biller_cache, payments.payid_registrations, payments.osko_payments) are mutable by design — payment status, limit lifecycle, settlement state, device trust profiles, biller cache, PayID management lifecycle, and Osko payment status transitions are updated by the owning modules.

CHECK constraints¶

Table	Column	Constraint
`payments.payments`	`amount`	`CHECK (amount > 0)`
`payments.fx_rates`	`rate`	`CHECK (rate > 0)`
`payments.fx_rates`	`valid_until`	`CHECK (valid_until > published_at)`
`payments.payment_limits`	`effective_to`	`CHECK (effective_to IS NULL OR effective_to > effective_from)`
`payments.fx_locks`	`expires_at`	`CHECK (expires_at > locked_at)`
`payments.bpay_payments`	`amount`	`CHECK (amount > 0)`
`payments.payid_registrations`	`payid_type`	`CHECK (payid_type IN ('MOBILE','EMAIL','ABN','ORGANISATION_ID'))`
`payments.payid_registrations`	`jurisdiction`	`CHECK (jurisdiction = 'AU')`
`payments.osko_payments`	`amount`	`CHECK (amount > 0)`
`payments.osko_payments`	`direction`	`CHECK (direction IN ('OUTBOUND','INBOUND'))`
`payments.osko_payments`	`jurisdiction`	`CHECK (jurisdiction = 'AU')`

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

Rule	Reason	Owner
Payment limit velocity check	Requires aggregating today's settled amounts across multiple rows — not a single-row invariant	MOD-021 Lambda
Sanctions gate	Depends on real-time sanctions service response; cross-service call	MOD-020 Lambda
Fraud score threshold	AppConfig-configurable; threshold is not constant	MOD-023 Lambda

Direct debit mandate tables (MOD-114)¶

Three new tables owned by MOD-114. All dual-jurisdiction (NZ DDR + AU BECS); jurisdiction is a runtime context per ADR-042. Flyway migrations V001–V003.

`payments.direct_debit_mandates` (mutable — V001)¶

One row per mandate. Status transitions: ACTIVE → CANCELLED | SUSPENDED | EXPIRED. Partial unique index enforces one active mandate per account-biller-jurisdiction combination.

Column	Type	Constraints	Notes
mandate_id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Soft ref to SD02 `kyc.parties(id)` — no FK (cross-DB)
account_id	uuid	NOT NULL	Soft ref to SD01 `accounts.accounts(id)` — no FK (cross-DB)
jurisdiction	text	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))	Runtime context per ADR-042
biller_id	text	NOT NULL	Scheme-assigned biller identifier (NZ DDR biller code or AU APCA ID)
biller_name	text	NOT NULL	Human-readable biller name — PII; never emitted in logs per ADR-031
scheme_ref	text		DDR reference (NZ) or APCA mandate ID (AU); null until confirmed by scheme
max_amount	numeric(18,2)		Customer-set maximum per debit; null = no cap
frequency	text	CHECK (frequency IN ('WEEKLY','FORTNIGHTLY','MONTHLY','VARIABLE'))
status	text	NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE','CANCELLED','SUSPENDED','EXPIRED'))	Uppercase enum
effective_date	date	NOT NULL	First date a debit presentation is valid
expiry_date	date		Null = open-ended
cancelled_at	timestamptz		Set on cancellation
cancel_reason	text		Customer or system reason
trace_id	text	NOT NULL	Propagated from creation request
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - UNIQUE (account_id, biller_id, jurisdiction) WHERE status = 'ACTIVE' — enforces one active mandate per account-biller-jurisdiction - INDEX on (account_id, status) - INDEX on (biller_id, status)

Grants: SELECT, INSERT, UPDATE to payments_app_user. No DELETE grant — status transitions replace deletion.

`payments.direct_debit_events` (immutable — V002, ADR-048 Cat 1)¶

Append-only audit log of all mandate and presentation lifecycle events. Satisfies PAY-002 LOG and NZ DDR / AU BECS scheme record-keeping requirements. Immutability enforced by trg_direct_debit_events_immutable (BEFORE UPDATE OR DELETE trigger calling fn_immutable_row()).

Column	Type	Constraints	Notes
id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
mandate_id	uuid	NOT NULL REFERENCES payments.direct_debit_mandates(mandate_id)
account_id	uuid	NOT NULL	Denormalised for query performance
party_id	uuid	NOT NULL	Denormalised
jurisdiction	text	NOT NULL	NZ or AU
event_type	text	NOT NULL CHECK (event_type IN ('MANDATE_CREATED','MANDATE_AMENDED','MANDATE_CANCELLED','MANDATE_SUSPENDED','MANDATE_EXPIRED','DEBIT_PRESENTED','DEBIT_AUTHORISED','DEBIT_DISHONOURED','DEBIT_DISPUTED'))
before_state	jsonb		Mandate snapshot before the transition; null on MANDATE_CREATED
after_state	jsonb		Mandate snapshot after the transition
presentation_id	uuid		FK to payments.direct_debit_presentations; populated on DEBIT_* events
actor	text	NOT NULL	`customer:{party_id}`, `system:mod-114`, `staff:{staff_id}`, `provider:{biller_id}`
policy_refs	jsonb		`["PAY-001","PAY-002","FR-506"]` — traceability
trace_id	text	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()

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

Trigger: trg_direct_debit_events_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row().

`payments.direct_debit_presentations` (mutable — V003)¶

One row per debit-file entry received from the external provider. Links mandate validation outcome to the posting chain.

Column	Type	Constraints	Notes
presentation_id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
mandate_id	uuid	REFERENCES payments.direct_debit_mandates(mandate_id)	Null if mandate lookup failed (MANDATE_MISSING return)
account_id	uuid	NOT NULL	Soft ref — no FK
party_id	uuid	NOT NULL
jurisdiction	text	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
biller_id	text	NOT NULL
amount	numeric(18,2)	NOT NULL CHECK (amount > 0)
currency	text	NOT NULL CHECK (currency IN ('NZD','AUD'))
status	text	NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','AUTHORISED','SETTLED','RETURNED','DISHONOURED'))
return_code	text		Scheme return code (DDR/BECS) — populated on RETURNED or DISHONOURED
return_reason	text		Plain-language mapped from return_code via reason-mapper
posting_id	text		Application-layer ref to SD01 posting — no FK (cross-DB)
dishonour_fee_posting_id	text		Application-layer ref to MOD-110 fee posting
idempotency_key	text	NOT NULL UNIQUE	Provider file row identifier
trace_id	text	NOT NULL
presented_at	timestamptz	NOT NULL DEFAULT now()
settled_at	timestamptz
returned_at	timestamptz

Grants: SELECT, INSERT, UPDATE to payments_app_user. No DELETE grant.

BPAY inbound tables (MOD-136)¶

AU-only. Handles inbound BPAY — business-banking customers registered as BPAY billers receiving bill payments from payers via the sponsor bank. Distinct from payments.bpay_payments + payments.bpay_biller_cache (MOD-119 outbound).

`payments.bpay_inbound_billers` (mutable — V001)¶

Per-tenant biller registrations. One row per business customer registered as a BPAY biller via the sponsor bank. Status is PENDING_REGISTRATION until the sponsor assigns a BPAY code (typically 3–5 business days). Table name uses inbound_billers prefix to distinguish from MOD-119's bpay_biller_cache (j-2 ruling).

Column	Type	Constraints	Notes
id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Soft ref — no FK (cross-domain)
account_id	uuid	NOT NULL	Soft ref — no FK (cross-domain)
bpay_code	text	UNIQUE	NULL until `ACTIVE`; assigned by sponsor on registration confirmation
biller_name	text	NOT NULL
crn_format	text	NOT NULL CHECK (crn_format IN ('LUHN','REGEX','FIXED_LENGTH','NONE'))	Uppercase per SD04 enum standard
crn_regex_pattern	text		Required when `crn_format = 'REGEX'`
crn_fixed_length	int		Required when `crn_format = 'FIXED_LENGTH'`
status	text	NOT NULL DEFAULT 'PENDING_REGISTRATION' CHECK (status IN ('PENDING_REGISTRATION','ACTIVE','SUSPENDED','CANCELLED'))
sponsor_submission_ref	text		Reference returned when registration submitted to sponsor
jurisdiction	text	NOT NULL DEFAULT 'AU' CHECK (jurisdiction = 'AU')	AU-only module
registered_by	uuid	NOT NULL	Back-office staff user_id
activated_at	timestamptz		Set when sponsor confirmation received and staff PATCH applied
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()

Indexes: (party_id), (bpay_code) WHERE bpay_code IS NOT NULL, (status).

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

State machine: PENDING_REGISTRATION → ACTIVE (staff PATCH on sponsor confirmation; dev/uat admin endpoint advances deterministically — j-10 ruling). ACTIVE → SUSPENDED or CANCELLED by staff. No inbound credits while status is not ACTIVE (FR-605).

`payments.bpay_inbound_payments` (mutable — V002)¶

One row per item in the sponsor's daily BPAY inbound settlement file. Replay-safe: UNIQUE (settlement_batch_reference, scheme_item_id). Idempotency key provides defence-in-depth for Lambda retry.

Column	Type	Constraints	Notes
id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
biller_id	uuid	NOT NULL REFERENCES payments.bpay_inbound_billers(id)
bpay_code	text	NOT NULL	Denormalised from biller record for query performance
crn	text	NOT NULL	Customer reference number as received from payer's bank
amount	numeric(18,2)	NOT NULL CHECK (amount > 0)
currency	char(3)	NOT NULL DEFAULT 'AUD'	BPAY is AU-only
payer_bsb	text	NOT NULL
payer_account_number	text	NOT NULL
settlement_date	date	NOT NULL
settlement_batch_reference	text	NOT NULL	Sponsor's batch reference
scheme_item_id	text	NOT NULL	Per-item identifier from settlement file
status	text	NOT NULL DEFAULT 'RECEIVED' CHECK (status IN ('RECEIVED','CRN_VALID','CRN_INVALID','POSTING_FAILED','POSTED','RETURNED'))
crn_validation_result	text		`PASS` or `FAIL:{reason}`
posting_id	uuid		Application-layer ref to SD01 posting — no FK (cross-DB); populated after MOD-001 credit
payment_id	uuid		FK to payments.payments synthetic row per j-3 ruling
return_reason_code	text		Scheme return reason code; populated when `status = 'RETURNED'`
reconciliation_status	text	CHECK (reconciliation_status IN ('MATCHED','UNMATCHED','ESCALATED'))	Set by reconciliation.ts (FR-608)
reconciled_at	timestamptz
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		(settlement_batch_reference, scheme_item_id)	Replay defence

Indexes: (biller_id), (status), (settlement_date).

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

`payments.bpay_inbound_events` (immutable — V003, ADR-048 Cat 1)¶

Append-only audit log for all biller registration lifecycle and inbound payment state transitions. Satisfies PAY-002 LOG. Immutability enforced by trg_bpay_inbound_events_immutable (BEFORE UPDATE OR DELETE trigger calling fn_immutable_row()). Mirrors the payments.direct_debit_events pattern.

Column	Type	Constraints	Notes
event_id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
entity_type	text	NOT NULL CHECK (entity_type IN ('BILLER','PAYMENT'))
entity_id	uuid	NOT NULL	`biller_id` or `payment_id` depending on `entity_type`
event_type	text	NOT NULL CHECK (event_type IN ('BILLER_REGISTRATION_SUBMITTED','BILLER_ACTIVATED','BILLER_SUSPENDED','BILLER_CANCELLED','PAYMENT_RECEIVED','CRN_VALIDATED','CRN_REJECTED','CREDIT_POSTED','NOTIFICATION_SENT','PAYMENT_RETURNED','RECONCILIATION_MATCHED','RECONCILIATION_UNMATCHED','RECONCILIATION_ESCALATED'))
actor_type	text	NOT NULL CHECK (actor_type IN ('STAFF','SYSTEM','SPONSOR'))
actor_id	uuid		Back-office staff user_id or service identity; null for `SPONSOR` events
detail	jsonb	NOT NULL DEFAULT '{}'	Event-specific payload
trace_id	text	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()

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

Trigger: trg_bpay_inbound_events_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row().

Indexes: (entity_id), (created_at).

Batch payment tables (MOD-135)¶

Three tables owned by MOD-135. Flyway migrations V001–V003. Both jurisdictions (NZ + AU).

`payments.batch_files` (mutable — V001)¶

One row per uploaded batch file. Lifecycle: UPLOADED → VALIDATING → PENDING_APPROVAL → REJECTED | PROCESSING → SETTLED | FAILED.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
party_id	uuid	NOT NULL	Cross-domain ref to SD02 `party.parties(party_id)` — no FK
account_id	uuid	NOT NULL	Cross-domain ref to SD01 `accounts.accounts(id)` — no FK (source debit account)
file_format	text	NOT NULL CHECK (file_format IN ('ABA','CSV'))
jurisdiction	text	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
file_key	text	NOT NULL	S3 object key for the uploaded file
file_size_bytes	integer	NOT NULL CHECK (file_size_bytes > 0)
declared_item_count	integer	NOT NULL CHECK (declared_item_count > 0)	Declared by client at upload
declared_total_amount	numeric(18,2)	NOT NULL CHECK (declared_total_amount > 0)
validated_item_count	integer		Set after validation passes
validated_total_amount	numeric(18,2)		Set after validation passes
shortfall_amount	numeric(18,2)		FR-602 — positive if MOD-020 balance check fails
status	text	NOT NULL DEFAULT 'UPLOADED' CHECK (status IN ('UPLOADED','VALIDATING','PENDING_APPROVAL','REJECTED','PROCESSING','SETTLED','FAILED'))
rejection_reason	text		Structured error summary if REJECTED
idempotency_key	text	NOT NULL UNIQUE	Caller-managed UUID per upload attempt (j-11)
confirmed_at	timestamptz		Customer confirmation timestamp
settled_at	timestamptz
failed_at	timestamptz
trace_id	text	NOT NULL	OTel trace ID
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_batch_files_party_status on (party_id, status) WHERE status NOT IN ('SETTLED','FAILED','REJECTED') - idx_batch_files_account_created on (account_id, created_at DESC)

GRANTs: SELECT, INSERT, UPDATE to bank_payments_lambda_role. No DELETE to any role.

`payments.batch_items` (mutable — V002)¶

One row per payment item within a batch. Status: PENDING → SUBMITTING → SUBMITTED → SETTLED | QUARANTINED | FAILED | RETURNED.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
batch_id	uuid	NOT NULL REFERENCES payments.batch_files(id)
sequence_number	integer	NOT NULL	1-based row number in the source file
payment_id	uuid	NOT NULL	Minted by MOD-135 at INSERT; passed to MOD-020 and MOD-001 per-item. No FK — `payments.payments` row created by MOD-020 in same call chain
beneficiary_account	text	NOT NULL	AU: BSB+account; NZ: 16-digit format
beneficiary_name	text	NOT NULL
amount	numeric(18,2)	NOT NULL CHECK (amount > 0)
currency	char(3)	NOT NULL	ISO-4217
reference	text		Visible to beneficiary (≤ 12 chars)
particulars	text		NZ only; ignored for AU (≤ 12 chars)
posting_id	uuid		Soft ref to SD01 `accounts.postings(id)` — no FK; populated on SETTLED
return_posting_id	uuid		Soft ref — re-credit posting to source account for RETURNED items (FR-604)
mod_020_decision	text	CHECK (mod_020_decision IN ('AUTHORISED','VALIDATION_FAILED','PENDING_AUTH'))	From per-item MOD-020 call
mod_020_failure_reason	text
status	text	NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','SUBMITTING','SUBMITTED','SETTLED','QUARANTINED','FAILED','RETURNED'))
quarantine_reason	text		Populated when QUARANTINED (FR-603)
failure_reason	text		Populated when FAILED
returned_at	timestamptz
trace_id	text	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL
UNIQUE		(batch_id, sequence_number)	Replay-safe per file position

Indexes: - idx_batch_items_batch_status on (batch_id, status) - idx_batch_items_payment_id on (payment_id)

Notes: payment_id is minted by MOD-135 before the per-item MOD-020 call — established pattern (same as MOD-141/MOD-119/MOD-120/MOD-122). No FK to payments.payments (cross-DB boundary: bank_payments vs bank_payments schema — MOD-020 creates the payments row in the same synchronous chain). posting_id and return_posting_id are application-layer soft refs (SD01 cross-DB).

GRANTs: SELECT, INSERT, UPDATE to bank_payments_lambda_role. No DELETE to any role.

`payments.batch_events` (immutable — V003, ADR-048 Cat 1)¶

Append-only audit log for all batch file and item state transitions. Satisfies PAY-002 LOG + NFR-024.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
batch_id	uuid	NOT NULL REFERENCES payments.batch_files(id)
item_id	uuid	REFERENCES payments.batch_items(id)	NULL for batch-level events
entity_type	text	NOT NULL CHECK (entity_type IN ('BATCH','ITEM'))
event_type	text	NOT NULL CHECK (event_type IN ('BATCH_UPLOADED','BATCH_VALIDATING','BATCH_VALIDATED','BATCH_REJECTED','BATCH_CONFIRMED','BATCH_PROCESSING','BATCH_SETTLED','BATCH_FAILED','ITEM_PENDING','ITEM_SUBMITTING','ITEM_SUBMITTED','ITEM_SETTLED','ITEM_QUARANTINED','ITEM_FAILED','ITEM_RETURNED'))
payload	jsonb	NOT NULL DEFAULT '{}'	Event-specific metadata
trace_id	text	NOT NULL
event_timestamp	timestamptz	NOT NULL DEFAULT now()

Immutability: trg_batch_events_immutable — BEFORE UPDATE OR DELETE OR TRUNCATE, calls fn_immutable_row(). PAY-002 LOG + NFR-024; 7-year retention.

Indexes: - idx_batch_events_batch_id on (batch_id, event_timestamp DESC) - idx_batch_events_item_id on (item_id) WHERE item_id IS NOT NULL

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

Agency banking tables (MOD-137)¶

`payments.agency_batch_files` (mutable — V001)¶

CREATE TABLE payments.agency_batch_files (
  id                              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  agency_network                  text NOT NULL CHECK (agency_network IN ('AUSTRALIA_POST','NZ_POST','OTHER')),
  file_reference                  text NOT NULL UNIQUE,        -- idempotency: network-assigned file ID
  file_key                        text NOT NULL,               -- S3 key under agency-files/transactions/
  settlement_file_key             text,                        -- S3 key under agency-files/settlements/; NULL until received
  jurisdiction                    char(2) NOT NULL CHECK (jurisdiction IN ('NZ','AU')),
  batch_date                      date NOT NULL,               -- transaction date from file header
  declared_item_count             int NOT NULL CHECK (declared_item_count > 0),
  declared_total_deposit_amount   numeric(18,2),
  declared_total_withdrawal_amount numeric(18,2),
  processed_item_count            int,
  posted_item_count               int,
  quarantined_item_count          int,
  status                          text NOT NULL DEFAULT 'RECEIVED'
                                    CHECK (status IN ('RECEIVED','PROCESSING','SETTLED','RECONCILED','EXCEPTIONS')),
  trace_id                        text NOT NULL,
  received_at                     timestamptz NOT NULL DEFAULT now(),
  settled_at                      timestamptz,
  reconciled_at                   timestamptz,
  created_at                      timestamptz NOT NULL DEFAULT now(),
  updated_at                      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_agency_batch_files_status   ON payments.agency_batch_files (status, received_at DESC);
CREATE INDEX idx_agency_batch_files_network  ON payments.agency_batch_files (agency_network, batch_date DESC);

GRANTs: SELECT, INSERT, UPDATE to bank_payments_lambda_role. No DELETE to any role.

`payments.agency_transactions` (mutable — V002)¶

CREATE TABLE payments.agency_transactions (
  id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  batch_id             uuid NOT NULL REFERENCES payments.agency_batch_files(id),
  sequence_number      int NOT NULL,
  transaction_type     text NOT NULL CHECK (transaction_type IN ('DEPOSIT','WITHDRAWAL','BALANCE_ENQUIRY')),
  bsb                  text NOT NULL,
  account_number       text NOT NULL,
  account_id           uuid,                      -- resolved at MATCHED; no FK — cross-domain (core.accounts)
  amount               numeric(18,2) NOT NULL CHECK (amount > 0),
  currency             char(3) NOT NULL,
  transaction_date     date NOT NULL,
  terminal_id          text NOT NULL,             -- FR-611 location metadata
  agent_outlet_code    text NOT NULL,             -- FR-611 location metadata
  posting_id           uuid,                      -- soft ref SD01 accounts.postings(id); NULL until POSTED
  aml_threshold_flagged boolean NOT NULL DEFAULT false,
  mod_001_result       text CHECK (mod_001_result IN ('POSTED','FAILED')),
  status               text NOT NULL DEFAULT 'UNMATCHED'
                         CHECK (status IN ('UNMATCHED','MATCHED','POSTED','QUARANTINED')),
  quarantine_reason    text,
  failure_reason       text,
  trace_id             text NOT NULL,
  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now(),
  UNIQUE (batch_id, sequence_number)
);

CREATE INDEX idx_agency_transactions_batch   ON payments.agency_transactions (batch_id, status);
CREATE INDEX idx_agency_transactions_account ON payments.agency_transactions (account_id) WHERE account_id IS NOT NULL;
CREATE INDEX idx_agency_transactions_aml     ON payments.agency_transactions (aml_threshold_flagged, transaction_date)
                                               WHERE aml_threshold_flagged = true;

GRANTs: SELECT, INSERT, UPDATE to bank_payments_lambda_role. No DELETE to any role.

`payments.agency_batch_events` (immutable — V003, ADR-048 Cat 1)¶

CREATE TABLE payments.agency_batch_events (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  batch_id         uuid NOT NULL REFERENCES payments.agency_batch_files(id),
  transaction_id   uuid REFERENCES payments.agency_transactions(id),  -- NULL for BATCH-level events
  entity_type      text NOT NULL CHECK (entity_type IN ('BATCH','TRANSACTION')),
  event_type       text NOT NULL CHECK (event_type IN (
                     'BATCH_RECEIVED','BATCH_PROCESSING','BATCH_SETTLED',
                     'BATCH_RECONCILED','BATCH_EXCEPTIONS',
                     'TRANSACTION_MATCHED','TRANSACTION_POSTED',
                     'TRANSACTION_QUARANTINED','TRANSACTION_AML_FLAGGED'
                   )),
  payload          jsonb NOT NULL,
  trace_id         text NOT NULL,
  event_timestamp  timestamptz NOT NULL DEFAULT now()
);

-- ADR-048 Cat 1: no mutation after insert
CREATE TRIGGER trg_agency_batch_events_immutable
  BEFORE UPDATE OR DELETE OR TRUNCATE ON payments.agency_batch_events
  FOR EACH STATEMENT EXECUTE FUNCTION fn_immutable_row();

CREATE INDEX idx_agency_batch_events_batch ON payments.agency_batch_events (batch_id, event_timestamp DESC);
CREATE INDEX idx_agency_batch_events_tx    ON payments.agency_batch_events (transaction_id)
                                             WHERE transaction_id IS NOT NULL;

Immutability: trg_agency_batch_events_immutable — BEFORE UPDATE OR DELETE OR TRUNCATE, calls fn_immutable_row(). PAY-002 LOG + NFR-024; 7-year retention.

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

Schema: `payments` (continued) — MOD-124 physical card tables¶

`payments.physical_cards`¶

Mutable current-state record for each physical card issued. One row per card (not per customer). ADR-058: card_number_last4 only — full PANs live in the processor's PCI vault, accessed by opaque processor_card_ref.

CREATE TABLE payments.physical_cards (
  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)
  account_id            uuid          NOT NULL REFERENCES accounts.accounts(id),
  card_number_last4     char(4)       NOT NULL,                           -- ADR-058: no full PAN in Neon
  card_scheme           text          NOT NULL CHECK (card_scheme IN ('VISA','MASTERCARD')),
  card_product_type     text          NOT NULL CHECK (card_product_type IN ('DEBIT','CREDIT')),
  expiry_month          smallint      NOT NULL CHECK (expiry_month BETWEEN 1 AND 12),
  expiry_year           smallint      NOT NULL,
  embossed_name         text          NOT NULL,
  jurisdiction          char(2)       NOT NULL CHECK (jurisdiction IN ('NZ','AU')),
  sponsor_bin           text          NOT NULL,                           -- from SSM /bank/{stage}/mod-124/sponsor-bin-range/{nz|au}
  status                text          NOT NULL DEFAULT 'ORDERED' CHECK (status IN (
                          'ORDERED','DISPATCHED','ACTIVE','FROZEN',
                          'CANCELLED','REPLACED','RENEWAL_PENDING')),
  freeze_reason         text          CHECK (freeze_reason IN ('LOST','STOLEN','CUSTOMER_REQUEST','SYSTEM')),
  processor_card_ref    text,                                             -- opaque reference in processor PCI vault
  bureau_order_ref      text,                                             -- bureau's order tracking reference
  bureau_dispatched_at  timestamptz,
  activated_at          timestamptz,
  cancelled_at          timestamptz,
  replacement_card_id   uuid          REFERENCES payments.physical_cards(id),  -- populated when status = REPLACED
  renewed_card_id       uuid          REFERENCES payments.physical_cards(id),  -- populated when renewal card ordered
  -- credit-ready columns — all NULL for debit cards in v1; used by future credit card path
  credit_limit          numeric(18,2),
  credit_utilisation_pct numeric(5,2),
  billing_cycle_day     smallint      CHECK (billing_cycle_day BETWEEN 1 AND 28),
  created_at            timestamptz   NOT NULL DEFAULT now(),
  updated_at            timestamptz   NOT NULL DEFAULT now()
);

CREATE INDEX idx_physical_cards_party_id     ON payments.physical_cards (party_id);
CREATE INDEX idx_physical_cards_account_id   ON payments.physical_cards (account_id);
CREATE INDEX idx_physical_cards_status       ON payments.physical_cards (status)
    WHERE status NOT IN ('CANCELLED','REPLACED');
CREATE INDEX idx_physical_cards_expiry       ON payments.physical_cards (expiry_year, expiry_month)
    WHERE status = 'ACTIVE';  -- 60-day pre-expiry scan (MOD-124 EventBridge Scheduler)

Owner: MOD-124. GRANTs: SELECT, INSERT, UPDATE to bank_payments_lambda_role. No DELETE or TRUNCATE to any role. Deletions are logical (status = CANCELLED).

`payments.card_events`¶

Immutable append-only audit log of every card lifecycle state transition. ADR-048 Cat 1. Provides the complete audit trail required by PAY-006 and DT-001.

CREATE TABLE payments.card_events (
  id              uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  card_id         uuid        NOT NULL REFERENCES payments.physical_cards(id),
  event_type      text        NOT NULL CHECK (event_type IN (
                    'ORDERED','DISPATCHED',
                    'ACTIVATION_REQUESTED','ACTIVATED',
                    'FREEZE_REQUESTED','FROZEN',
                    'UNFREEZE_REQUESTED','UNFROZEN',
                    'CANCELLATION_REQUESTED','CANCELLED',
                    'REPLACEMENT_ORDERED',
                    'PIN_CHANGE_REQUESTED','PIN_CHANGED',
                    'RENEWAL_ORDERED')),
  source_module   text        NOT NULL,
  actor           text,                 -- customer party_id, staff ID, or 'SYSTEM'
  channel         text        NOT NULL DEFAULT 'SYSTEM'
                    CHECK (channel IN ('APP','API','SYSTEM','BUREAU_WEBHOOK')),
  details         jsonb       NOT NULL DEFAULT '{}',
  trace_id        uuid,
  idempotency_key text        NOT NULL,
  event_timestamp timestamptz NOT NULL DEFAULT now(),
  created_at      timestamptz NOT NULL DEFAULT now()
);

-- ADR-048 Cat 1: no mutation after insert
CREATE TRIGGER trg_card_events_immutable
  BEFORE UPDATE OR DELETE OR TRUNCATE ON payments.card_events
  FOR EACH STATEMENT EXECUTE FUNCTION fn_immutable_row();

CREATE INDEX idx_card_events_card_id    ON payments.card_events (card_id, event_timestamp DESC);
CREATE INDEX idx_card_events_event_type ON payments.card_events (event_type);
CREATE UNIQUE INDEX idx_card_events_idempotency ON payments.card_events (idempotency_key);

Immutability: trg_card_events_immutable — BEFORE UPDATE OR DELETE OR TRUNCATE, calls fn_immutable_row(). PAY-006 LOG + DT-001 GATE; 7-year retention per NFR-024.

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

Immutability triggers (SD04)¶

Table	Trigger	Type
`payments.payment_events`	`trg_payment_events_immutable`	ADR-048 Cat 1
`payments.direct_debit_events`	`trg_direct_debit_events_immutable`	ADR-048 Cat 1
`payments.bpay_inbound_events`	`trg_bpay_inbound_events_immutable`	ADR-048 Cat 1
`payments.batch_events`	`trg_batch_events_immutable`	ADR-048 Cat 1
`payments.agency_batch_events`	`trg_agency_batch_events_immutable`	ADR-048 Cat 1
`payments.card_events`	`trg_card_events_immutable`	ADR-048 Cat 1

Cross-domain references¶

This table	Column	References	Notes
payments.payments	party_id	SD02 `party.parties(party_id)`	Every payment is initiated by a verified party
payments.payments	from_account_id	SD01 `accounts.accounts(id)`	Debit account must exist in core banking
payments.payments	to_account_id	SD01 `accounts.accounts(id)`	Internal credit account; NULL for external payments
payments.payments	sanctions_result_id	SD02 `kyc.sanctions_results(id)`	Payment-level sanctions screen
payments.payment_limits	party_id	SD02 `party.parties(party_id)`	Limits are per-party
payments.fx_locks	party_id	SD02 `party.parties(party_id)`
payments.ifti_cmir_queue	submission_id	SD03 `aml.regulatory_submissions(id)`	Soft reference only — no FK constraint; back-populated by MOD-019 asynchronously
payments.open_banking_consents	party_id	SD02 `party.parties(party_id)`
payments.payid_registrations	party_id	SD02 `party.parties(party_id)`	Every PayID registration is associated with a verified party
payments.bpay_inbound_billers	party_id	SD02 `party.parties(party_id)`	Soft ref — no FK (cross-domain)
payments.bpay_inbound_billers	account_id	SD01 `accounts.accounts(id)`	Soft ref — no FK (cross-domain)
payments.bpay_inbound_payments	payment_id	`payments.payments(id)`	Synthetic payments.payments row per j-3 ruling; application-layer only
payments.batch_files	party_id	SD02 `party.parties(party_id)`	Soft ref — no FK (cross-domain)
payments.batch_files	account_id	SD01 `accounts.accounts(id)`	Soft ref — no FK (cross-domain); source debit account
payments.batch_items	payment_id	`payments.payments(id)`	Minted by MOD-135; payments.payments row created by MOD-020 in same chain — application-layer only
payments.batch_items	posting_id	SD01 `accounts.postings(id)`	Soft ref — no FK; populated on SETTLED
payments.batch_items	return_posting_id	SD01 `accounts.postings(id)`	Soft ref — re-credit posting for RETURNED items (FR-604)
payments.agency_batch_files	(no cross-domain cols)	—	Network-scoped; account references live on agency_transactions
payments.agency_transactions	account_id	SD01 `core.accounts(id)`	Soft ref — no FK (cross-domain); resolved at MATCHED via Neon read
payments.agency_transactions	posting_id	SD01 `accounts.postings(id)`	Soft ref — no FK; populated on POSTED
payments.physical_cards	party_id	SD02 `party.parties(party_id)`	Soft ref — no FK (cross-domain); every card is issued to a verified party
payments.physical_cards	account_id	SD01 `accounts.accounts(id)`	Card is linked to a deposit account

Consumers of SD04 data: SD01 MOD-001 creates postings from settled payment records. SD03 MOD-016/017 read payments transaction data for AML typology analysis via CDC. SD06 Snowflake receives all tables via CDC (MOD-042) for fraud analytics and capital calculations. SD08 MOD-071 initiates payments via the pre-validation API and reads payment history via MOD-070. SD08 MOD-072 manages PayID registrations via the MOD-120 API. The ifti_cmir_queue is consumed by SD03 MOD-019.

SD04 — Payments data model¶

Schema: payments¶

payments.payments¶

payments.payment_events¶

payments.intra_bank_transfers¶

payments.bpay_payments¶

payments.bpay_biller_cache¶

payments.payid_registrations¶

payments.osko_payments¶

payments.nz_interbank_payments¶

payments.payment_limits¶

payments.limit_change_audit (MOD-021)¶

payments.idempotency_keys (MOD-021, shared SD04 surface)¶

payments.fx_rates¶

payments.fx_locks¶

payments.fx_lock_audit (MOD-025)¶

payments.ifti_cmir_queue¶

payments.settlement_files¶

payments.reconciliation_exceptions¶

payments.nostro_positions¶

payments.nostro_adjustment_audit (MOD-082)¶

payments.nostro_position_eod_snapshots (MOD-082)¶

payments.open_banking_consents¶

payments.device_intelligence (MOD-024)¶

payments.session_observations (MOD-024)¶

payments.device_anomalies (MOD-024)¶

payments.fraud_scores (MOD-023)¶

payments.scam_scored_payees (MOD-023)¶

DB-enforced invariants (ADR-048)¶

Immutability triggers¶

CHECK constraints¶

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

Direct debit mandate tables (MOD-114)¶

payments.direct_debit_mandates (mutable — V001)¶

payments.direct_debit_events (immutable — V002, ADR-048 Cat 1)¶

payments.direct_debit_presentations (mutable — V003)¶

BPAY inbound tables (MOD-136)¶

payments.bpay_inbound_billers (mutable — V001)¶

payments.bpay_inbound_payments (mutable — V002)¶

payments.bpay_inbound_events (immutable — V003, ADR-048 Cat 1)¶

Batch payment tables (MOD-135)¶

payments.batch_files (mutable — V001)¶

payments.batch_items (mutable — V002)¶

payments.batch_events (immutable — V003, ADR-048 Cat 1)¶

Agency banking tables (MOD-137)¶

payments.agency_batch_files (mutable — V001)¶

payments.agency_transactions (mutable — V002)¶

payments.agency_batch_events (immutable — V003, ADR-048 Cat 1)¶

Schema: payments (continued) — MOD-124 physical card tables¶

payments.physical_cards¶

payments.card_events¶

Immutability triggers (SD04)¶

Cross-domain references¶

Schema: `payments`¶

`payments.payments`¶

`payments.payment_events`¶

`payments.intra_bank_transfers`¶

`payments.bpay_payments`¶

`payments.bpay_biller_cache`¶

`payments.payid_registrations`¶

`payments.osko_payments`¶

`payments.nz_interbank_payments`¶

`payments.payment_limits`¶

`payments.limit_change_audit` (MOD-021)¶

`payments.idempotency_keys` (MOD-021, shared SD04 surface)¶

`payments.fx_rates`¶

`payments.fx_locks`¶

`payments.fx_lock_audit` (MOD-025)¶

`payments.ifti_cmir_queue`¶

`payments.settlement_files`¶

`payments.reconciliation_exceptions`¶

`payments.nostro_positions`¶

`payments.nostro_adjustment_audit` (MOD-082)¶

`payments.nostro_position_eod_snapshots` (MOD-082)¶

`payments.open_banking_consents`¶

`payments.device_intelligence` (MOD-024)¶

`payments.session_observations` (MOD-024)¶

`payments.device_anomalies` (MOD-024)¶

`payments.fraud_scores` (MOD-023)¶

`payments.scam_scored_payees` (MOD-023)¶

`payments.direct_debit_mandates` (mutable — V001)¶

`payments.direct_debit_events` (immutable — V002, ADR-048 Cat 1)¶

`payments.direct_debit_presentations` (mutable — V003)¶

`payments.bpay_inbound_billers` (mutable — V001)¶

`payments.bpay_inbound_payments` (mutable — V002)¶

`payments.bpay_inbound_events` (immutable — V003, ADR-048 Cat 1)¶

`payments.batch_files` (mutable — V001)¶

`payments.batch_items` (mutable — V002)¶

`payments.batch_events` (immutable — V003, ADR-048 Cat 1)¶

`payments.agency_batch_files` (mutable — V001)¶

`payments.agency_transactions` (mutable — V002)¶

`payments.agency_batch_events` (immutable — V003, ADR-048 Cat 1)¶

Schema: `payments` (continued) — MOD-124 physical card tables¶

`payments.physical_cards`¶

`payments.card_events`¶