SD05 — Credit data model¶

Database: credit (Neon Postgres, ap-southeast-2) Repo: bank-credit 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: SD05 Credit Decisioning & Loan Platform

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

The credit schema is the primary write domain for MOD-027 (affordability calculator), MOD-028 (credit score and risk rating), MOD-029 (pre-approval engine), MOD-030 (IFRS 9 stage allocation), MOD-031 (ECL calculation and GL posting), MOD-059 (credit bureau submission), MOD-065 (credit servicing and collections), and MOD-066 (collateral and security management). Credit decisions are immutable once recorded. Stage allocations are updated monthly or on deterioration triggers, with each change creating a new allocation row. ECL provisions are posted to the GL via SD01.

Schema: `credit`¶

`credit.credit_applications`¶

One row per credit application submitted by a customer. Covers personal loans, credit lines, overdraft facilities, and mortgages. The application progresses through decisioning stages before a final credit decision is recorded in credit_decisions.

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)`
application_reference	text	NOT NULL UNIQUE	Human-readable reference (e.g. APP-2026-084321)
product_type	text	NOT NULL CHECK (product_type IN ('PERSONAL_LOAN','CREDIT_LINE','OVERDRAFT','MORTGAGE','BUSINESS_LOAN','FLEXIBLE_FACILITY'))	Extended from 5 → 6 values by MOD-162 V001 migration
requested_amount	numeric(18,2)	NOT NULL
requested_currency	char(3)	NOT NULL
requested_term_months	int		Loan term; NULL for revolving facilities
purpose	text	NOT NULL	Declared purpose of credit
application_status	text	NOT NULL CHECK (application_status IN ('DRAFT','SUBMITTED','UNDER_ASSESSMENT','PRE_APPROVED','APPROVED','CONDITIONALLY_APPROVED','DECLINED','WITHDRAWN','EXPIRED'))
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
submitted_at	timestamptz		NULL while in DRAFT
decision_at	timestamptz		When a final decision was recorded
channel	text	NOT NULL CHECK (channel IN ('APP','BRANCH','API','PRE_APPROVAL'))
cdd_tier_at_application	text	NOT NULL	Customer's CDD tier at application time
credit_score_at_application	int		Credit bureau score at application
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL
deleted_at	timestamptz

Indexes: - idx_credit_applications_party_id on (party_id) - idx_credit_applications_status on (application_status) WHERE application_status NOT IN ('DECLINED','WITHDRAWN','EXPIRED') - idx_credit_applications_submitted_at on (submitted_at DESC) WHERE submitted_at IS NOT NULL - idx_credit_applications_reference on (application_reference)

`credit.credit_decisions`¶

Immutable record of each credit decision event. One application may have multiple decisions (e.g. initial decline, reconsideration approval). The most recent decision with is_current = true is the operative one.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
application_id	uuid	NOT NULL REFERENCES credit.credit_applications(id)
party_id	uuid	NOT NULL	cross-domain ref to SD02 `party.parties(party_id)`
decision_type	text	NOT NULL CHECK (decision_type IN ('APPROVE','CONDITIONALLY_APPROVE','DECLINE','REFER','PRE_APPROVE','COUNTER_OFFER'))
decision_source	text	NOT NULL CHECK (decision_source IN ('AUTO','MANUAL','PRE_APPROVAL_ENGINE','RECONSIDERATION'))
approved_amount	numeric(18,2)		NULL for declines
approved_currency	char(3)
approved_term_months	int
interest_rate	numeric(8,6)		Approved rate as decimal
conditions	jsonb	NOT NULL DEFAULT '[]'	Any conditions attached (e.g. document requirements)
decline_reasons	jsonb	NOT NULL DEFAULT '[]'	Reason codes for declined applications
risk_rating	text	NOT NULL CHECK (risk_rating IN ('A1','A2','B1','B2','C1','C2','D','E'))	Internal credit risk grade
credit_score	int		Score used in decision
affordability_assessment_id	uuid	REFERENCES credit.affordability_assessments(id)
policy_refs	jsonb	NOT NULL DEFAULT '["CRE-002","CRE-003"]'
decided_at	timestamptz	NOT NULL DEFAULT now()
decided_by	text	NOT NULL	Module or staff ID
is_current	boolean	NOT NULL DEFAULT true	Only the operative decision is true
superseded_by	uuid	REFERENCES credit.credit_decisions(id)
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_credit_decisions_application_id on (application_id) - idx_credit_decisions_party_current on (party_id) WHERE is_current = true - idx_credit_decisions_decided_at on (decided_at DESC)

Notes: Rows are immutable once written — enforced by trg_credit_decisions_immutable (ADR-048 Category 1). Reconsiderations and counter-offers create new rows; the superseded decision retains its original state.

`credit.affordability_assessments`¶

Results of the affordability calculation run by MOD-027. One row per assessment run. Multiple assessments may exist for a single application (e.g. if the customer updates their income information).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
application_id	uuid	REFERENCES credit.credit_applications(id)	Nullable today. FK added by MOD-029 V002. NOT NULL deferred to a follow-on MOD-029 V003 once MOD-027 historical NULL rows from dev/test runs are backfilled or aged out of retention.
party_id	uuid	NOT NULL	cross-domain ref to SD02 `party.parties(party_id)`
gross_income	numeric(18,2)	NOT NULL	Declared gross annual income
income_currency	char(3)	NOT NULL
income_verification_method	text	NOT NULL CHECK (income_verification_method IN ('PAYSLIP','TAX_RETURN','BANK_STATEMENT','OPEN_BANKING','DECLARED'))
net_income	numeric(18,2)	NOT NULL	Post-tax, post-obligations income
hem_benchmark	numeric(18,2)	NOT NULL	Household Expenditure Measure benchmark applied
declared_expenses	numeric(18,2)	NOT NULL DEFAULT 0.00	Customer-declared monthly expenses
assessed_expenses	numeric(18,2)	NOT NULL	Higher of declared and HEM
existing_debt_commitments	numeric(18,2)	NOT NULL DEFAULT 0.00	Existing monthly debt repayments
net_disposable_income	numeric(18,2)	NOT NULL	net_income - assessed_expenses - existing_debt_commitments
proposed_repayment	numeric(18,2)	NOT NULL	Monthly repayment for the requested loan
ndi_after_repayment	numeric(18,2)	NOT NULL	net_disposable_income - proposed_repayment
dti_ratio	numeric(8,4)	NOT NULL	Debt-to-income ratio at assessment time (FR-171)
stress_rate_applied	numeric(8,6)	NOT NULL CHECK (stress_rate_applied >= 0)	Actual rate used in serviceability calc — self-contained audit, not reconstructable from AppConfig history (CRE-002)
buffer_applied_bps	int	NOT NULL CHECK (buffer_applied_bps >= 0)	Buffer applied in basis points (e.g. 300 = 3.00% for AU NCCP) — self-contained audit
regulatory_framework	text	NOT NULL CHECK (regulatory_framework IN ('CCCFA','NCCP'))	Jurisdiction-derived framework label
proposed_repayment_total_interest	numeric(18,2)	NOT NULL CHECK (proposed_repayment_total_interest >= 0)	CON-004 LOG — total interest payable over loan term; captured for caller MOD-029 to pass to MOD-050 disclosure
proposed_repayment_total_cost	numeric(18,2)	NOT NULL CHECK (proposed_repayment_total_cost >= 0)	CON-004 LOG — total cost of credit; captured for caller MOD-029 to pass to MOD-050 disclosure
dti_threshold	numeric(8,4)	NOT NULL CHECK (dti_threshold >= 0)	Active DTI threshold used (per product_type × jurisdiction config) — self-contained audit
affordability_result	text	NOT NULL CHECK (affordability_result IN ('PASS','MARGINAL','FAIL'))
decline_reason_codes	jsonb	NOT NULL DEFAULT '[]'	Populated when affordability_result = 'FAIL'
hem_source_version	text	NOT NULL	HEM table version used
policy_refs	jsonb	NOT NULL DEFAULT '["CRE-002","CRE-003","CON-004","REP-005"]'	Policy codes satisfied by this row
assessed_at	timestamptz	NOT NULL DEFAULT now()
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_affordability_application_id on (application_id) WHERE application_id IS NOT NULL - idx_affordability_party_id on (party_id) - idx_affordability_assessed_at on (assessed_at DESC)

Notes: Rows are immutable after insertion — enforced by trg_affordability_assessments_immutable (ADR-048 Category 1), which reuses credit.fn_immutable_row() provisioned by MOD-128 V001. Updated income information results in a new assessment row rather than an update to the existing one.

`credit.credit_scores`¶

Composite credit score and internal risk rating produced by MOD-028 for each scoring run. One row per score computation. Multiple rows may exist for the same party across different applications or periodic review runs. Append-only — score history is never overwritten.

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)`
application_id	uuid	REFERENCES credit.credit_applications(id)	Nullable today. FK added by MOD-029 V002. NOT NULL deferred to a follow-on MOD-029 V003 once MOD-028 historical NULL rows from dev/test runs are backfilled or aged out of retention.
affordability_assessment_id	uuid	REFERENCES credit.affordability_assessments(id)	Source affordability assessment used as scoring input
bureau_enquiry_id	uuid	REFERENCES credit.bureau_enquiries(enquiry_id)	NULL when bureau data unavailable and cdd_soft_fallback = true
behavioural_score	int	NOT NULL	Composite internal score (0–1000 scale); derived from bureau, affordability, and CDD components
bureau_score	int		Bureau-supplied score at time of scoring; NULL if bureau unavailable
bureau_used	text		Bureau name when bureau_score is populated (e.g. EQUIFAX_AU, CENTRIX)
bureau_staleness_days	int		Days since bureau file date at time of scoring (AD-4 staleness audit)
bureau_stale_flag	boolean	NOT NULL DEFAULT false	True when bureau_staleness_days > 30 (AD-4 threshold)
cdd_tier	text	NOT NULL	CDD tier at time of scoring (from MOD-010)
cdd_soft_fallback	boolean	NOT NULL DEFAULT false	AD-6: true when CDD tier unavailable and a fallback tier was applied — DT-005 deviation audit
risk_rating	text	NOT NULL CHECK (risk_rating IN ('A1','A2','B1','B2','C1','C2','D','E'))	Internal credit risk grade; maps to `credit_decisions.risk_rating`
basel_risk_weight	numeric(8,6)	NOT NULL DEFAULT 0.750000	AD-3: Basel standardised retail risk weight (75% baseline); overridable by product config; read by MOD-033 RWA calculation
score_components	jsonb	NOT NULL DEFAULT '{}'	Weighted component breakdown and input values for model audit (DT-005 model inventory)
model_version	text	NOT NULL	Scorecard model version (CRE-003 governance log)
policy_refs	jsonb	NOT NULL DEFAULT '["CRE-001","CRE-003","DT-005","CLQ-001"]'
scored_at	timestamptz	NOT NULL DEFAULT now()
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_credit_scores_party_id on (party_id) - idx_credit_scores_application_id on (application_id) WHERE application_id IS NOT NULL - idx_credit_scores_scored_at on (scored_at DESC) - idx_credit_scores_risk_rating on (risk_rating)

Notes: Rows are immutable after insertion — enforced by trg_credit_scores_immutable (ADR-048 Category 1), which reuses credit.fn_immutable_row() provisioned by MOD-128 V001. Each scoring run always inserts a new row regardless of outcome.

`credit.risk_scores_mirror`¶

Append-only mirror of SD06 customer risk scores written by the MOD-028 mirror-writer Lambda, which consumes bank.risk-platform/customer_risk_score_updated cross-bus EventBridge events. Provides SD05 modules with local read access to SD06 risk scores without crossing the domain boundary at query time (ADR-038 hot-path constraint). One row per event received.

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)`
source_event_id	text	NOT NULL UNIQUE	EB event ID from `bank.risk-platform/customer_risk_score_updated`; UNIQUE constraint prevents duplicate event processing
sd06_risk_score	int	NOT NULL	Numeric risk score as published by SD06
sd06_risk_tier	text	NOT NULL	Tier label from SD06 model (e.g. LOW, MEDIUM, HIGH, VERY_HIGH)
model_version	text	NOT NULL	SD06 model version at event time
score_date	date	NOT NULL	Date the score was computed by SD06
payload	jsonb	NOT NULL DEFAULT '{}'	Full EB event payload retained for audit
mirrored_at	timestamptz	NOT NULL DEFAULT now()	When the mirror-writer Lambda consumed the event
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_risk_scores_mirror_party_id on (party_id) - idx_risk_scores_mirror_source_event_id on (source_event_id) — enforces UNIQUE, also lookup path for idempotency check - idx_risk_scores_mirror_score_date on (score_date DESC)

Notes: Rows are immutable after insertion — enforced by trg_risk_scores_mirror_immutable (ADR-048 Category 1). Mirror-writer Lambda uses source_event_id UNIQUE constraint as the idempotency gate; SQS DLQ captures failed writes for operational alerting (AD-8). BankCreditRole requires events:PutRule + events:PutTargets on the bank-risk-platform bus, provisioned by MOD-104 cross-bus grant handoff.

`credit.hem_benchmarks` (temporary stand-in)¶

Stand-in table — temporary. Provisioned by MOD-027 V001 as a local substitute until the dedicated SD06 HEM ingestion module ships and publishes a cross-domain risk.hem_benchmarks_readable view (AP-010 pattern 1). Once that view is available, MOD-027 will cut over its HemLookup adapter and a follow-up migration will drop this table.

Household Expenditure Measure benchmarks per (jurisdiction, household_type, income_band). MOD-027 uses the benchmark to substitute assessed_expenses when declared expenses fall below the regulatory floor (CRE-002, APRA RG 209.60).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
household_type	text	NOT NULL CHECK (household_type IN ('SINGLE_NO_DEPENDENTS','COUPLE_NO_DEPENDENTS','SINGLE_WITH_DEPENDENTS','COUPLE_WITH_DEPENDENTS'))
income_band_lower	numeric(18,2)	NOT NULL	Annual gross income lower bound (AUD/NZD)
income_band_upper	numeric(18,2)		NULL = no upper bound
monthly_benchmark	numeric(18,2)	NOT NULL CHECK (monthly_benchmark > 0)	Monthly HEM amount substituted into assessed_expenses when declared < benchmark
source	text	NOT NULL CHECK (source IN ('ABS','STATS_NZ','STAND_IN'))	Data source
source_version	text	NOT NULL	e.g. `2026-Q1` or `STAND-IN-v1`
effective_from	date	NOT NULL
effective_to	date		NULL = current row
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

Indexes: partial (jurisdiction, household_type, effective_from DESC) WHERE effective_to IS NULL for active-row lookup.

V002 seed: stage-gated dev seed (skipped on prod) provides AU + NZ values for all four household_types across two-to-three income bands with source='STAND_IN', source_version='STAND-IN-v1'.

`credit.credit_bureau_requests`¶

Outbound CCR submission log owned by MOD-059. One row per bureau submission attempt (SUBMISSION, UPDATE, or CORRECTION). Inbound bureau enquiries (soft pull / hard enquiry) are logged separately in credit.bureau_enquiries (MOD-128).

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)`
application_id	uuid	REFERENCES credit.credit_applications(id)	NULL for background checks
bureau	text	NOT NULL CHECK (bureau IN ('EQUIFAX','ILLION','CENTRIX','EXPERIAN'))
inquiry_type	text	NOT NULL CHECK (inquiry_type IN ('SUBMISSION','UPDATE','CORRECTION'))	MOD-059 outbound types only. SOFT_PULL/HARD_ENQUIRY belong to MOD-128's `credit.bureau_enquiries`.
credit_score	int		Populated by MOD-128 inbound path only; NULL on MOD-059 outbound rows.
credit_file_payload	jsonb	NOT NULL DEFAULT '{}'	Redacted bureau response; full payload in S3
s3_key	text		S3 object key for the full credit file (inbound path)
request_status	text	NOT NULL CHECK (request_status IN ('PENDING','SUCCESS','FAILED','TIMEOUT','HALTED'))	HALTED = validation failure (FR-191 halt path; AD k-8)
requested_at	timestamptz	NOT NULL DEFAULT now()
responded_at	timestamptz
policy_ref	text	NOT NULL DEFAULT 'REP-010'
submission_file_hash	text		SHA-256 of the assembled outbound file (FR-192)
bureau_ack_reference	text		Bureau-supplied acknowledgement reference (FR-192)
rejection_reason	text		Bureau-supplied rejection text (FR-192)
submitted_at	timestamptz		When the file was actually sent (distinct from requested_at)
s3_payload_key	text		S3 key of the assembled outbound file
submission_batch_id	uuid	REFERENCES credit.bureau_submission_batches(id) ON DELETE SET NULL	Links to the monthly batch when applicable (FR-190 batch submissions)
trace_id	uuid		ADR-031 trace propagation
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_credit_bureau_party_id on (party_id) - idx_credit_bureau_application_id on (application_id) WHERE application_id IS NOT NULL - idx_credit_bureau_requested_at on (requested_at DESC)

Notes: Rows are immutable after insertion — enforced by trg_credit_bureau_requests_immutable (ADR-048 Category 1). Required for credit reporting compliance; bureau inquiry records must not be altered.

`credit.bureau_submission_batches`¶

Monthly FR-190 batch metadata. One row per (bureau, reporting_month, jurisdiction) — the batch header for the full account performance file sent to each bureau each month. Owned by MOD-059. Mutable (status transitions).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
bureau	text	NOT NULL CHECK (bureau IN ('CENTRIX','EQUIFAX_AU'))
reporting_month	date	NOT NULL CHECK (reporting_month = date_trunc('month', reporting_month)::date)	First calendar day of the reporting month (e.g. 2026-05-01)
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
account_count	int	NOT NULL DEFAULT 0 CHECK (account_count >= 0)	Number of accounts included in the batch
file_hash	text		SHA-256 of assembled file; NULL until BUILDING completes
file_s3_key	text
status	text	NOT NULL DEFAULT 'BUILDING' CHECK (status IN ('BUILDING','VALIDATED','TRANSMITTED','REJECTED','HALTED'))
validation_outcome	jsonb	NOT NULL DEFAULT '{}'	AJV error list when status=HALTED or REJECTED
bureau_ack_reference	text
rejection_reason	text
started_at	timestamptz	NOT NULL DEFAULT now()
completed_at	timestamptz
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

Constraints: UNIQUE on (bureau, reporting_month, jurisdiction).

Indexes: idx_bureau_batches_status partial WHERE status IN ('BUILDING','VALIDATED','HALTED'); idx_bureau_batches_reporting_month on (reporting_month DESC).

Triggers: trg_bureau_submission_batches_touch_updated_at (touch trigger). Mutable table — no immutability trigger.

`credit.bureau_disputes`¶

Customer dispute log and CCCFA / Privacy Code dispute lifecycle. Mutable (status transitions). Owned by MOD-059.

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)`
application_id	uuid	REFERENCES credit.credit_applications(id)
bureau	text	NOT NULL CHECK (bureau IN ('CENTRIX','EQUIFAX_AU'))
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
dispute_reference	text	NOT NULL UNIQUE	Human-readable reference (e.g. DSP-2026-00123)
lodged_at	timestamptz	NOT NULL
lodged_by_channel	text	NOT NULL CHECK (lodged_by_channel IN ('APP','BRANCH','API','EMAIL'))
description	text	NOT NULL
status	text	NOT NULL DEFAULT 'LODGED' CHECK (status IN ('LODGED','INVESTIGATING','UPHELD','REJECTED','WITHDRAWN','RESOLVED'))
resolution_notes	text
resolved_at	timestamptz
correction_submission_id	uuid	REFERENCES credit.credit_bureau_requests(id)	Auto-populated on UPHELD when a CORRECTION submission is generated
regulatory_deadline	timestamptz	NOT NULL	Statutory resolution deadline calculated at lodge time (NZ: +28 calendar days per Privacy Act 2020; AU: +30 calendar days per Privacy Act 1988)
trace_id	uuid
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

Indexes: (party_id, lodged_at DESC); (status) partial WHERE status IN ('LODGED','INVESTIGATING'); (regulatory_deadline ASC) partial WHERE status IN ('LODGED','INVESTIGATING').

Triggers: trg_bureau_disputes_touch_updated_at (touch trigger). Mutable — no immutability trigger.

`credit.ecl_provisions`¶

Monthly IFRS 9 Expected Credit Loss (ECL) provision records produced by MOD-031. One row per loan account per provision run. ECL amounts are posted to the general ledger via SD01 (MOD-001).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
loan_account_id	uuid	NOT NULL REFERENCES credit.loan_accounts(id)
party_id	uuid	NOT NULL	cross-domain ref to SD02 `party.parties(party_id)`
provision_date	date	NOT NULL	Month-end date of this provision run
ifrs9_stage	int	NOT NULL CHECK (ifrs9_stage IN (1,2,3))	IFRS 9 stage at provision date
outstanding_balance	numeric(18,2)	NOT NULL
pd_12m	numeric(10,8)	NOT NULL CHECK (pd_12m >= 0 AND pd_12m <= 1)	12-month probability of default
pd_lifetime	numeric(10,8)	NOT NULL CHECK (pd_lifetime >= 0 AND pd_lifetime <= 1)	Lifetime probability of default
lgd	numeric(10,8)	NOT NULL CHECK (lgd >= 0 AND lgd <= 1)	Loss given default
ead	numeric(18,2)	NOT NULL	Exposure at default
ecl_12m	numeric(18,2)	NOT NULL	12-month ECL
ecl_lifetime	numeric(18,2)	NOT NULL	Lifetime ECL
ecl_recognised	numeric(18,2)	NOT NULL	ECL amount actually recognised (stage-dependent)
model_version	text	NOT NULL	ECL model version used
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))	FR-186 portfolio split by jurisdiction
currency	char(3)	NOT NULL	Needed for GL posting entries and outbound event
run_type	text	NOT NULL CHECK (run_type IN ('MONTHLY','DAILY_INCREMENTAL','EVENT_TRIGGERED'))	Distinguishes the three ECL calculation paths
ecl_run_id	uuid	NOT NULL REFERENCES credit.ecl_runs(id)	Links to the run-level audit record (FR-187)
previous_ecl_recognised	numeric(18,2)	NOT NULL DEFAULT 0.00	Prior recognised amount for the same loan — captures the delta for movement-zero skip logic
gl_posting_id	uuid	NULL — cross-domain ref to bank_core.accounts.postings(id); no FK enforced	Posting in SD01 for this provision movement
calculated_at	timestamptz	NOT NULL DEFAULT now()
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_ecl_provisions_loan_account_date on (loan_account_id, provision_date DESC) - idx_ecl_provisions_provision_date on (provision_date) - idx_ecl_provisions_stage on (ifrs9_stage, provision_date)

Constraints: - uq_ecl_provisions_loan_date_runtype UNIQUE (loan_account_id, provision_date, run_type) — one row per loan per provision date per run type; a MONTHLY snapshot and an EVENT_TRIGGERED row may co-exist on the same date.

Notes: Rows are immutable after insertion — enforced by trg_ecl_provisions_immutable (ADR-048 Category 1). Each calculation path inserts a new row; prior ECL figures must remain unaltered for IFRS 9 audit purposes.

`credit.ecl_runs` (MOD-031)¶

Run-level audit record for each ECL calculation batch. One row per run invocation. Mutable during execution (status transitions); application-layer immutability enforced for terminal states (COMPLETE, FAILED) — no further updates permitted once the run reaches either terminal state. FR-187 7-year retention via Snowflake CDC.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()	= ecl_run_id referenced by ecl_provisions and outbound events
run_type	text	NOT NULL CHECK (run_type IN ('MONTHLY','DAILY_INCREMENTAL','EVENT_TRIGGERED'))	Which calculation path triggered this run
provision_date	date	NOT NULL	Last calendar day of the preceding month for MONTHLY; sweep/event date for other types
status	text	NOT NULL DEFAULT 'RUNNING' CHECK (status IN ('RUNNING','PENDING_APPROVAL','APPROVED','COMPLETE','FAILED'))	State machine — RUNNING→PENDING_APPROVAL or COMPLETE; PENDING_APPROVAL→APPROVED→COMPLETE; any state→FAILED
trigger_loan_account_id	uuid	NULL REFERENCES credit.loan_accounts(id)	Populated for EVENT_TRIGGERED runs only
loans_in_run	int	NOT NULL DEFAULT 0	Count of loan accounts processed
total_provision_movement	numeric(18,2)	NULL	Signed net ECL movement across all loans in the run
previous_run_total	numeric(18,2)	NULL	Total recognised ECL from the prior MONTHLY run — used by FR-188 materiality gate
materiality_breach	boolean	NOT NULL DEFAULT false	FR-188: true when total_provision_movement exceeds the configured materiality threshold
approved_by_staff_id	text	NULL	Staff ID of approver; `'SYSTEM_FIRST_RUN'` for the first-ever monthly run (FR-188 first-run edge case)
approved_at	timestamptz	NULL
approval_committee_ref	text	NULL	Governance ticket reference for audit
model_version	text	NOT NULL	ECL model version — recorded on every run for FR-187 model audit
parameter_set_version	text	NOT NULL	Combined PD+LGD parameter set version (e.g. `pd-v1.0.0+lgd-v1.0.0`)
trace_id	uuid	NOT NULL	ADR-031 trace propagation
started_at	timestamptz	NOT NULL DEFAULT now()
completed_at	timestamptz	NULL	Populated on COMPLETE or FAILED
failure_reason	text	NULL	Populated on FAILED — human-readable failure description
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()	Touch trigger maintains updated_at

Indexes: - (run_type, provision_date DESC) — month-over-month comparisons and RWA engine queries - (status) WHERE status NOT IN ('COMPLETE','FAILED') — operational dashboard for in-flight runs - (provision_date DESC) — chronological audit queries

Notes: Mutable table — status transitions are legitimate writes. Application-layer immutability is enforced in the Lambda handler: once status is COMPLETE or FAILED, the row is not updated further. Not an ADR-048 Cat 1 trigger (status progression is a valid mutation); the terminal-state lock is enforced by the approve-ecl-run handler's state machine.

`credit.ifrs9_stage_allocations` (MOD-030)¶

Append-only IFRS 9 stage allocation log. One row per staging event — initial allocation, periodic reassessment, SICR trigger, or cure. The latest row per (loan_account_id, effective_date DESC, allocated_at DESC) is the current stage. MOD-031 reads this table to compute ECL. ADR-048 Cat 1 immutable.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
loan_account_id	uuid	NOT NULL REFERENCES credit.loan_accounts(id)
party_id	uuid	NOT NULL	Cross-domain ref to SD02 `party.parties(party_id)`
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))	Denormalised so MOD-031 reads without a join (AD k-1)
ifrs9_stage	smallint	NOT NULL CHECK (ifrs9_stage IN (1,2,3))
previous_ifrs9_stage	smallint	NULL CHECK (previous_ifrs9_stage IN (1,2,3))	For transition logging
trigger_reason	text	NOT NULL CHECK (trigger_reason IN ('INITIAL_ALLOCATION','DPD_THRESHOLD','WATCHLIST_FLAG','PD_INCREASE','CREDIT_IMPAIRED','CURE_TO_STAGE_1','MANUAL_OVERRIDE','MONTHLY_REASSESSMENT'))
arrears_days_at_allocation	int	NOT NULL CHECK (arrears_days_at_allocation >= 0)
risk_rating	text	NULL CHECK (risk_rating IN ('A1','A2','B1','B2','C1','C2','D','E'))	Sourced from `credit.credit_scores`
pd_origination	numeric(8,6)	NULL CHECK (pd_origination BETWEEN 0 AND 1)
pd_lifetime	numeric(8,6)	NULL CHECK (pd_lifetime BETWEEN 0 AND 1)
exposure_at_default	numeric(18,2)	NOT NULL CHECK (exposure_at_default >= 0)
loan_status_at_allocation	text	NOT NULL
effective_date	date	NOT NULL
allocated_at	timestamptz	NOT NULL DEFAULT now()
source_event_id	uuid	NULL	Inbound EventBridge event_id
source	text	NOT NULL CHECK (source IN ('FACILITY_EVENT','DAILY_SWEEP','MANUAL_OVERRIDE','MONTHLY_REASSESSMENT'))
committee_approval_id	text	NULL	FR-184 GATE: required for MANUAL_OVERRIDE (biconditional CHECK)
override_reason	text	NULL	Populated on MANUAL_OVERRIDE
override_actor	text	NULL	Populated on MANUAL_OVERRIDE
trace_id	uuid	NOT NULL	ADR-031 propagation

Indexes: - (loan_account_id, effective_date DESC, allocated_at DESC) — current stage lookup - (ifrs9_stage, effective_date DESC) — portfolio reporting / capital engine - (party_id) — customer-360 view

Constraints: - chk_manual_override_governance (biconditional): trigger_reason = 'MANUAL_OVERRIDE' AND committee_approval_id IS NOT NULL AND override_actor IS NOT NULL OR trigger_reason <> 'MANUAL_OVERRIDE' AND committee_approval_id IS NULL

Immutability: trg_ifrs9_stage_allocations_immutable — BEFORE UPDATE OR DELETE, calls credit.fn_immutable_row(). ADR-048 Cat 1.

Grants: bank_credit_app_user: SELECT, INSERT (no UPDATE/DELETE). bank_credit_readonly: SELECT.

`credit.loan_accounts`¶

Operational loan account records for approved and active loans, maintained by MOD-065. One row per disbursed loan. Links to both the credit application (origination) and the SD01 account (ledger).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
application_id	uuid	NOT NULL REFERENCES credit.credit_applications(id)
party_id	uuid	NOT NULL	cross-domain ref to SD02 `party.parties(party_id)`
account_id	uuid	NOT NULL REFERENCES accounts.accounts(id)	Linked SD01 account for fund disbursement and repayment
loan_reference	text	NOT NULL UNIQUE	Human-readable loan reference
product_type	text	NOT NULL CHECK (product_type IN ('PERSONAL_LOAN','CREDIT_LINE','OVERDRAFT','MORTGAGE','BUSINESS_LOAN','FLEXIBLE_FACILITY'))	Extended from 5 → 6 values by MOD-162 V001 migration
principal_amount	numeric(18,2)	NOT NULL	Original disbursed principal
outstanding_principal	numeric(18,2)	NOT NULL DEFAULT 0.00	Current outstanding principal
currency	char(3)	NOT NULL
interest_rate	numeric(8,6)	NOT NULL CHECK (interest_rate >= 0)	Agreed rate as decimal
term_months	int		NULL for revolving facilities
repayment_frequency	text	NOT NULL CHECK (repayment_frequency IN ('WEEKLY','FORTNIGHTLY','MONTHLY'))
repayment_amount	numeric(18,2)	NOT NULL	Scheduled repayment amount
next_repayment_date	date
loan_status	text	NOT NULL CHECK (loan_status IN ('PENDING_DISBURSEMENT','ACTIVE','ARREARS','DEFAULT','WRITE_OFF_PENDING','PAID_OFF','WRITTEN_OFF','RESTRUCTURED'))	`PENDING_DISBURSEMENT` = post-MOD-001-account-create pre-disbursement (AD k-6); `WRITE_OFF_PENDING` = write-off proposed awaiting CFO approval (AD k-7)
arrears_days	int	NOT NULL DEFAULT 0 CHECK (arrears_days >= 0)	Days past due
arrears_amount	numeric(18,2)	NOT NULL DEFAULT 0.00
last_alerted_threshold	int	NOT NULL DEFAULT 0	AD k-8: highest arrears threshold for which a boundary-crossing alert has been sent; prevents duplicate alerts on the same boundary
disbursed_at	timestamptz
maturity_date	date
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_loan_accounts_party_id on (party_id) - idx_loan_accounts_loan_status on (loan_status) WHERE loan_status NOT IN ('PAID_OFF','WRITTEN_OFF') - idx_loan_accounts_arrears on (arrears_days) WHERE arrears_days > 0 - idx_loan_accounts_account_id on (account_id)

`credit.collateral_register`¶

Register of security interests and collateral held against credit facilities, maintained by MOD-066. Required for LGD calculation in ECL provisioning and enforcement proceedings.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
loan_account_id	uuid	NOT NULL REFERENCES credit.loan_accounts(id)
party_id	uuid	NOT NULL	cross-domain ref to SD02 `party.parties(party_id)`
collateral_type	text	NOT NULL CHECK (collateral_type IN ('REAL_PROPERTY','VEHICLE','TERM_DEPOSIT','GUARANTEE','BUSINESS_ASSETS','OTHER'))
description	text	NOT NULL
estimated_value	numeric(18,2)	NOT NULL
currency	char(3)	NOT NULL
valuation_date	date	NOT NULL
valuation_source	text	NOT NULL	Valuer or estimation method
security_interest_type	text	NOT NULL CHECK (security_interest_type IN ('MORTGAGE','PPSR','GUARANTEE','PLEDGE','LIEN'))
registration_reference	text	NULL	Registration number; authority qualified via registration_authority
collateral_status	text	NOT NULL CHECK (collateral_status IN ('ACTIVE','RELEASED','ENFORCED','PENDING_RELEASE'))
registered_at	timestamptz	NULL	When security interest was registered
released_at	timestamptz	NULL
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))	Regulatory split
haircut_pct	numeric(5,4)	NOT NULL CHECK (haircut_pct >= 0 AND haircut_pct < 1)	Strict upper bound — 1.0 would zero net value
net_collateral_value	numeric(18,2)	NOT NULL CHECK (net_collateral_value >= 0)	Denormalised: estimated_value × (1 − haircut_pct)
product_policy_max_ltv	numeric(8,6)	NOT NULL CHECK (product_policy_max_ltv > 0 AND product_policy_max_ltv <= 1)	Locked at registration from LTV policy
ltv_ratio	numeric(8,6)	NULL CHECK (ltv_ratio >= 0)	Denormalised from latest valuation
ltv_breach	boolean	NOT NULL DEFAULT false	true when ltv_ratio > product_policy_max_ltv
registration_expiry_date	date	NULL	FR-198 alerting; drives renewal_tasks
registration_authority	text	NULL	e.g. PPSR-NZ, PPSR-AU, LINZ
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger maintains

Indexes: - idx_collateral_register_loan_account_id on (loan_account_id) - idx_collateral_register_party_id on (party_id) - idx_collateral_register_status on (collateral_status) WHERE collateral_status = 'ACTIVE'

Column rename (MOD-066 V001): ppsr_registration_number was renamed to registration_reference — the PPSR name was too narrow; the authority is now captured in registration_authority.

`credit.collateral_valuations`¶

Append-only Cat 1 immutable revaluation log. One row per valuation event; latest row per collateral_id is the current valuation. The credit.collateral_register denormalised fields are kept in sync by the record-valuation handler. Managed by MOD-066.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
collateral_id	uuid	NOT NULL REFERENCES credit.collateral_register(id)
valuation_date	date	NOT NULL
estimated_value	numeric(18,2)	NOT NULL CHECK (estimated_value >= 0)
currency	char(3)	NOT NULL	Snapshot self-contained (k-2 amendment)
valuation_source	text	NOT NULL	Valuer or estimation method
haircut_pct_applied	numeric(5,4)	NOT NULL CHECK (haircut_pct_applied >= 0 AND haircut_pct_applied < 1)
net_collateral_value	numeric(18,2)	NOT NULL CHECK (net_collateral_value >= 0)
outstanding_principal_at_valuation	numeric(18,2)	NOT NULL CHECK (outstanding_principal_at_valuation >= 0)	Captured at-valuation moment
ltv_ratio	numeric(8,6)	NULL CHECK (ltv_ratio >= 0)
ltv_breach	boolean	NOT NULL DEFAULT false
product_policy_max_ltv	numeric(8,6)	NOT NULL	LTV policy at time of valuation
model_version	text	NOT NULL	e.g. `haircut-v1.0.0+ltv-policy-v1.0.0`
valued_by_staff_id	text	NULL	NULL for SYSTEM-recorded valuations
trace_id	uuid	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - UNIQUE (collateral_id, valuation_date, valuation_source) — dedup guard

Immutability: Cat 1 — trg_collateral_valuations_immutable BEFORE UPDATE OR DELETE raises insufficient_privilege. Grants: SELECT, INSERT to app_user (no UPDATE/DELETE).

`credit.collateral_release_requests`¶

Mutable release governance table. Application-layer immutability for terminal states (APPROVED, REJECTED). 3-state machine: PENDING → APPROVED | REJECTED. Managed by MOD-066.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
collateral_id	uuid	NOT NULL REFERENCES credit.collateral_register(id)
status	text	NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','APPROVED','REJECTED'))
requested_by_staff_id	text	NOT NULL
requested_reason	text	NOT NULL
approved_by_staff_id	text	NULL	Populated on terminal state
approval_committee_ref	text	NULL	Governance ticket reference
decision_reason	text	NULL	Populated on terminal state
requested_at	timestamptz	NOT NULL DEFAULT now()
decided_at	timestamptz	NULL	Populated on terminal state
trace_id	uuid	NOT NULL

Biconditional CHECK chk_release_request_governance:

(status = 'PENDING' AND approved_by_staff_id IS NULL AND decided_at IS NULL)
OR
(status IN ('APPROVED','REJECTED') AND approved_by_staff_id IS NOT NULL AND decided_at IS NOT NULL)

`credit.collateral_renewal_tasks`¶

Mutable operations work queue for collateral registration expiry tracking. Daily-expiry-sweep Lambda creates tasks at 90/30/7 day windows. Managed by MOD-066.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
collateral_id	uuid	NOT NULL REFERENCES credit.collateral_register(id)
days_until_expiry	int	NOT NULL CHECK (days_until_expiry IN (90, 30, 7))	FR-198 alerting windows
expiry_date	date	NOT NULL	Included in UNIQUE to allow multiple expiry cycles
status	text	NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','IN_PROGRESS','COMPLETED','CANCELLED'))
assigned_to_staff_id	text	NULL
trace_id	uuid	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger maintains
completed_at	timestamptz	NULL

Unique constraint: (collateral_id, days_until_expiry, expiry_date) — prevents duplicate tasks per expiry cycle (k-4 amendment; expiry_date included so prior-cycle COMPLETED rows don't block the next cycle's tasks).

`credit.property_security_details`¶

Property-specific extension sidecar for credit.collateral_register rows where collateral_type = 'REAL_PROPERTY'. One-to-one with the MOD-066 row via collateral_id PK. Holds LINZ/LRS title reference, address, property subtype, and borrower intent — data that is meaningful only for real property and not appropriate in the generic collateral register. Managed by MOD-115.

Column	Type	Constraints	Description
collateral_id	uuid	PK REFERENCES credit.collateral_register(id)	One-to-one with the MOD-066 row
loan_account_id	uuid	NOT NULL REFERENCES credit.loan_accounts(id)	Denormalised for fast FR-521 lookup
title_reference	text	NOT NULL	LINZ / LRS title reference
property_address	jsonb	NOT NULL	`{street, suburb, city, postcode, country}`
property_subtype	text	NOT NULL CHECK (property_subtype IN ('RESIDENTIAL','RURAL_RESIDENTIAL','APARTMENT','TOWNHOUSE'))
borrower_intent	text	NOT NULL CHECK (borrower_intent IN ('OWNER_OCCUPIER','INVESTOR'))	RBNZ BS19 distinguishes max LVR by intent
registration_number_lr	text	NULL	LR-specific registration number; separate from `collateral_register.registration_reference` which holds the PPSR ref
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger maintains

Notes: Mutable. Discharge updates credit.collateral_register.collateral_status = 'RELEASED' in MOD-066's table (same Neon DB, direct write by MOD-115's discharge handler — no staff approval flow, payoff-driven discharge is distinct from the MOD-066 committee release path).

`credit.lvr_snapshots`¶

Append-only Cat 1 immutable daily LVR snapshot per loan. Regulatory snapshots must not change after taking (RBNZ BS19 / APRA APS 220 audit requirement). One row per (loan_account_id, snapshot_date) — v1 assumes one primary property security per mortgage. Managed by MOD-115.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
loan_account_id	uuid	NOT NULL REFERENCES credit.loan_accounts(id)
collateral_id	uuid	NOT NULL REFERENCES credit.collateral_register(id)
snapshot_date	date	NOT NULL
outstanding_balance	numeric(18,2)	NOT NULL	From `credit.loan_accounts.outstanding_principal` at snapshot time
current_valuation	numeric(18,2)	NOT NULL	From latest `credit.collateral_valuations` at snapshot time
lvr_pct	numeric(7,4)	NOT NULL	Unhaircut raw LVR = outstanding_balance / current_valuation; used for prudential reporting
lvr_band	text	NOT NULL CHECK (lvr_band IN ('<60','60-70','70-80','80-90','>90'))
policy_max_lvr	numeric(7,4)	NOT NULL	Locked at snapshot time from LVR policy table (jurisdiction + borrower_intent)
policy_breach	boolean	NOT NULL DEFAULT false	true when lvr_pct > policy_max_lvr
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
borrower_intent	text	NOT NULL CHECK (borrower_intent IN ('OWNER_OCCUPIER','INVESTOR'))	Denormalised so portfolio reports avoid JOIN to property_security_details
trigger_reason	text	NOT NULL CHECK (trigger_reason IN ('DAILY_SWEEP','REVALUATION','BALANCE_CHANGE','REGISTRATION'))	What caused this snapshot to be written
trace_id	uuid	NOT NULL
created_at	timestamptz	NOT NULL DEFAULT now()

Unique constraint: (loan_account_id, snapshot_date) — DB-level idempotency for daily sweep replays. v1 assumes one primary property security per mortgage; a future migration would extend to (loan_account_id, collateral_id, snapshot_date) to support multiple securities per loan.

Immutability: Cat 1 — trg_lvr_snapshots_immutable BEFORE UPDATE OR DELETE raises insufficient_privilege.

`credit.collections_cases`¶

Collections workflow records for loans in arrears or default, managed by MOD-065. Tracks customer contact history, payment arrangements, and hardship assessments.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
loan_account_id	uuid	NOT NULL REFERENCES credit.loan_accounts(id)
party_id	uuid	NOT NULL	cross-domain ref to SD02 `party.parties(party_id)`
case_reference	text	NOT NULL UNIQUE
arrears_days	int	NOT NULL	Days past due at case opening
arrears_amount	numeric(18,2)	NOT NULL
case_status	text	NOT NULL CHECK (case_status IN ('OPEN','HARDSHIP_REVIEW','PAYMENT_ARRANGEMENT','LEGAL_REFERRAL','WRITTEN_OFF','RESOLVED','CLOSED'))
hardship_declared	boolean	NOT NULL DEFAULT false	Customer has declared financial hardship
hardship_type	text	CHECK (hardship_type IN ('TEMPORARY','ONGOING','SERIOUS'))
payment_arrangement_amount	numeric(18,2)		Agreed reduced payment under arrangement
payment_arrangement_expiry	date
assigned_to	text		Collections staff ID
opened_at	timestamptz	NOT NULL DEFAULT now()
resolved_at	timestamptz
resolution_type	text	CHECK (resolution_type IN ('PAID_IN_FULL','ARRANGEMENT','WRITE_OFF','LEGAL','HARDSHIP_VARIATION'))
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_collections_cases_loan_account_id on (loan_account_id) - idx_collections_cases_status on (case_status) WHERE case_status NOT IN ('CLOSED','RESOLVED','WRITTEN_OFF') - idx_collections_cases_arrears_days on (arrears_days DESC) WHERE case_status = 'OPEN'

`credit.repayment_schedules` (MOD-065)¶

Amortisation schedule for each loan account. Mutable — MOD-065 restructure handler marks old PENDING/PARTIAL rows as RESCHEDULED and appends new rows at higher sequence numbers (AD k-3 rescheduling pattern).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
loan_account_id	uuid	NOT NULL REFERENCES credit.loan_accounts(id)
sequence_number	int	NOT NULL CHECK (sequence_number > 0)	Monotone within a loan; rescheduling appends at higher numbers
scheduled_date	date	NOT NULL
scheduled_principal	numeric(18,2)	NOT NULL CHECK (scheduled_principal >= 0)
scheduled_interest	numeric(18,2)	NOT NULL CHECK (scheduled_interest >= 0)
scheduled_total	numeric(18,2)	NOT NULL CHECK (scheduled_total >= 0)
paid_amount	numeric(18,2)	NOT NULL DEFAULT 0
paid_at	timestamptz	NULL
status	text	NOT NULL CHECK (status IN ('PENDING','PAID','PARTIAL','MISSED','RESCHEDULED'))
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger

Constraints: UNIQUE (loan_account_id, sequence_number). Indexes: (loan_account_id, scheduled_date); partial (status, scheduled_date) WHERE status IN ('PENDING','PARTIAL'). Trigger: trg_repayment_schedules_touch_updated_at (touch only — mutable table).

`credit.collections_actions` (MOD-065)¶

Append-only Cat 1 immutable audit log of all collections actions taken on a case. Every contact attempt, hardship declaration, arrangement agreement, and escalation creates a new row. ADR-048 Cat 1 immutable.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
case_id	uuid	NOT NULL REFERENCES credit.collections_cases(id)
action_type	text	NOT NULL CHECK (action_type IN ('REMINDER_SENT','CONTACT_ATTEMPTED','HARDSHIP_DECLARED','HARDSHIP_OUTCOME','ARRANGEMENT_AGREED','ESCALATION','WRITE_OFF_PROPOSED','RESTRUCTURE_APPLIED'))
channel	text	NOT NULL CHECK (channel IN ('EMAIL','SMS','APP','PHONE','LETTER','SYSTEM'))
staff_id	text	NULL	NULL on system-generated actions (channel='SYSTEM'; AD k-4)
outcome	text	NULL
notes	text	NULL
policy_refs	jsonb	NOT NULL DEFAULT '["CRE-002","CRE-006","CON-003"]'
action_at	timestamptz	NOT NULL DEFAULT now()
trace_id	uuid	NULL	ADR-031 propagation
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: (case_id, action_at DESC); (action_type, action_at DESC). Immutability: trg_collections_actions_immutable — BEFORE UPDATE OR DELETE, calls credit.fn_immutable_row(). ADR-048 Cat 1. 7-year retention via Snowflake-side CDC (AD k-9).

`credit.bureau_enquiries`¶

Inbound credit bureau enquiry log (MOD-128). One row per bureau call — including duplicate calls suppressed by FR-578's cache and primary-bureau failures with fallback used (FR-579 audit trail). Distinct from credit.credit_bureau_requests (MOD-059 outbound CCR submissions).

Column	Type	Constraints	Description
`enquiry_id`	uuid	PK DEFAULT gen_random_uuid()
`customer_id`	uuid	NOT NULL	Cross-domain ref to SD02 `party.parties(party_id)`
`application_id`	uuid	nullable	NULL for `account_review` and `collection` enquiries
`jurisdiction`	text	NOT NULL CHECK (NZ, AU)
`bureau`	text	NOT NULL CHECK (equifax_au, experian_au, illion, centrix, equifax_nz)
`enquiry_purpose`	text	NOT NULL CHECK (credit_assessment, account_review, collection)
`consent_reference`	uuid	NOT NULL	FK-by-convention to `credit.bureau_consents(consent_id)` until MOD-049 ships
`request_at`	timestamptz	NOT NULL DEFAULT now()
`response_at`	timestamptz	nullable
`response_status`	text	nullable CHECK (success, no_file, bureau_error, timeout)
`credit_score`	int	nullable	Bureau-supplied; NULL on no_file/error/suppressed
`bureau_reference`	text	nullable	Bureau's opaque reference for audit
`report_payload`	jsonb	NOT NULL DEFAULT '{}'	Redacted inline; full payload accessible via `s3_key`
`s3_key`	text	nullable	Encrypted-at-rest S3 object key for the full credit file
`adverse_flags`	text[]	NOT NULL DEFAULT '{}'	Normalised via MOD-128's adverse-flag mapping
`suppressed`	boolean	NOT NULL DEFAULT false	FR-578: true if returned from 90-day cache
`cached_from_enquiry_id`	uuid	nullable	When `suppressed=true`, points to the source enquiry
`primary_bureau_failed`	boolean	NOT NULL DEFAULT false	FR-579 audit
`fallback_bureau_used`	text	nullable	Bureau used on fallback (FR-579 audit)
`policy_ref`	text	NOT NULL DEFAULT 'REP-010'
`trace_id`	uuid	nullable	ADR-031 propagation
`created_at`	timestamptz	NOT NULL DEFAULT now()

Indexes: customer_id; (application_id) WHERE NOT NULL; request_at DESC; partial index on (customer_id, enquiry_purpose, bureau, request_at DESC) for FR-578 duplicate-suppression lookup.

Immutability (ADR-048 Cat 1): trg_credit_bureau_enquiries_immutable — BEFORE UPDATE OR DELETE, calls credit.fn_immutable_row().

`credit.bureau_consents` (temporary stand-in)¶

Stand-in for MOD-049 (SD08 open-banking consent management, Not started). Replaced by an AP-010 pattern 1 cross-domain read of MOD-049's published consent view once MOD-049 ships. Mutable (supports revocations).

Column	Type	Constraints
`consent_id`	uuid	PK DEFAULT gen_random_uuid()
`customer_id`	uuid	NOT NULL
`purpose`	text	NOT NULL CHECK ('credit_bureau_enquiry')
`granted_at`	timestamptz	NOT NULL
`expires_at`	timestamptz	nullable
`revoked_at`	timestamptz	nullable
`source_channel`	text	NOT NULL CHECK (CREDIT_APPLICATION, PRE_APPROVAL, BACK_OFFICE)
`created_at`	timestamptz	NOT NULL DEFAULT now()
`updated_at`	timestamptz	NOT NULL

Lifecycle: Dropped via a new MOD-128 V003 migration after MOD-049 ships and existing rows are migrated into MOD-049's consent store. See bank-credit/docs/handoffs/MOD-128-mod049-consent-migration.handoff.md.

`credit.disclosure_acknowledgements` (temporary stand-in)¶

Stand-in for MOD-050 (SD08 disclosure enforcement, Not started). Records that a customer was shown the full pre-approval offer terms (rate, term, total interest, total cost) and acknowledged them before accepting. ADR-048 Cat 1 — immutable once written. Replaced by a cross-domain read of MOD-050's disclosure store once MOD-050 ships.

Created by MOD-029 V001. See bank-credit/docs/handoffs/MOD-029-mod050-disclosure-migration.handoff.md for the retirement plan.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
application_id	uuid	NOT NULL REFERENCES credit.credit_applications(id)
party_id	uuid	NOT NULL	Cross-domain ref to SD02 `party.parties(party_id)`
disclosure_type	text	NOT NULL CHECK (disclosure_type IN ('PRE_APPROVAL_OFFER'))	Extended when MOD-050 takes over
content_version	text	NOT NULL	Version of the offer disclosure template shown
content_hash	text	NOT NULL	SHA-256 of the exact offer terms presented — rate, term, total interest, total cost
acknowledged_at	timestamptz	NOT NULL	Timestamp of customer acknowledgement
channel	text	NOT NULL CHECK (channel IN ('APP','BRANCH','API','WEB'))
customer_session_id	text		Cognito session ID if APP channel
created_at	timestamptz	NOT NULL DEFAULT now()

Immutability: ADR-048 Cat 1 — trg_disclosure_acknowledgements_immutable rejects all UPDATE and DELETE. The row is the CON-004 GATE evidence for the acceptance path.

Indexes: - idx_disclosure_acks_application_id on (application_id) - idx_disclosure_acks_party_id on (party_id, acknowledged_at DESC)

`credit.bureau_config`¶

Operator-managed bureau routing per (jurisdiction, product_type, enquiry_purpose). UNIQUE constraint on those three columns. Seed-loaded for AU credit_assessment (Equifax AU primary, illion fallback) and NZ credit_assessment (Centrix primary). Mutable.

Column	Type	Constraints	Description
`id`	uuid	PK DEFAULT gen_random_uuid()
`jurisdiction`	text	NOT NULL CHECK (NZ, AU)
`product_type`	text	NOT NULL	e.g. PERSONAL_LOAN, HOME_LOAN
`enquiry_purpose`	text	NOT NULL
`primary_bureau`	text	NOT NULL
`fallback_bureau`	text	nullable	NULL = no fallback
`updated_at`	timestamptz	NOT NULL

`credit.idempotency_keys`¶

Shared SD05 idempotency store. Prevents duplicate Lambda invocations from producing duplicate side-effects across all SD05 modules. PK is (key, module_id). 24-hour TTL enforced by opportunistic DELETE at write time. First created by MOD-128; reused by MOD-027, MOD-028, MOD-029, MOD-031, MOD-059, MOD-065, and others.

Column	Type	Constraints	Description
`key`	text	NOT NULL	Caller-supplied idempotency key (e.g. request UUID)
`module_id`	text	NOT NULL	e.g. MOD-128
`response_payload`	jsonb	NOT NULL	Cached response body
`created_at`	timestamptz	NOT NULL DEFAULT now()	TTL anchor

PK: (key, module_id) — allows the same key to be used by different modules without collision.

`credit.mortgage_rate_periods` (MOD-116)¶

Fixed and variable rate period history per mortgage loan. One active row per loan at any time (PARTIAL UNIQUE). State machine: VARIABLE → FIXED → EXPIRING → EXPIRED. Mutable — status transitions are updated in place; terminal rows (EXPIRED, VARIABLE) are immutable by application logic. Managed by MOD-116.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
loan_account_id	uuid	NOT NULL REFERENCES credit.loan_accounts(id)
rate_type	text	NOT NULL CHECK (rate_type IN ('VARIABLE','FIXED'))
status	text	NOT NULL CHECK (status IN ('VARIABLE','FIXED','EXPIRING','EXPIRED'))	Current state machine position
interest_rate	numeric(8,6)	NOT NULL CHECK (interest_rate >= 0)	Rate applicable for this period
fixed_term_months	int	NULL	NULL for VARIABLE periods
start_date	date	NOT NULL	Period start
end_date	date	NULL	NULL until fixed term is set; set at election
elected_by	text	NULL CHECK (elected_by IN ('CUSTOMER','AUTO_REVERSION'))	NULL for initial VARIABLE row
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL	Touch trigger

Unique constraint: PARTIAL UNIQUE (loan_account_id) WHERE status IN ('VARIABLE','FIXED','EXPIRING') — only one active rate period per loan at a time. Indexes: (loan_account_id, start_date DESC); partial (status, end_date) WHERE status = 'EXPIRING' for daily-fixed-rate-sweep boundary detection.

`credit.mortgage_notifications` (MOD-116)¶

Notification log for all mortgage lifecycle notifications. One row per dispatched notification; idempotent per (loan_account_id, notification_type, rate_period_id). In v1 audit-only mode (MOD-063 not yet built): row is written with dispatched_at = now() but no customer message is sent — SNS alarm publishes to MOD-076 observability intake only. Mutable (status can transition from PENDING to DISPATCHED). Managed by MOD-116.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
loan_account_id	uuid	NOT NULL REFERENCES credit.loan_accounts(id)
notification_type	text	NOT NULL CHECK (notification_type IN ('FIXED_RATE_EXPIRY_90','FIXED_RATE_EXPIRY_60','FIXED_RATE_EXPIRY_30','RATE_REVERTED','BREAK_COST_QUOTE','DISCHARGE_CONFIRMATION','ARREARS_DAY1','ARREARS_DAY7','ARREARS_DAY30'))
rate_period_id	uuid	NULL REFERENCES credit.mortgage_rate_periods(id)	NULL for notifications not tied to a specific rate period
channel	text	NOT NULL DEFAULT 'STUB' CHECK (channel IN ('STUB','EMAIL','SMS','PUSH','LETTER'))	STUB = v1 audit-only mode until MOD-063 ships
status	text	NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','DISPATCHED','FAILED'))
dispatched_at	timestamptz	NULL	Set when status transitions to DISPATCHED
trace_id	uuid	NULL	ADR-031 propagation
created_at	timestamptz	NOT NULL DEFAULT now()

Unique constraint: UNIQUE (loan_account_id, notification_type, rate_period_id) — at most one of each notification type per rate period; prevents duplicate 90/60/30-day alerts. Indexes: (loan_account_id, created_at DESC); partial (status, created_at) WHERE status = 'PENDING' for retry sweep.

`credit.break_cost_disclosures` (MOD-116)¶

Per-disclosure record for fixed rate break cost quotes. Append-only by application logic — new quote = new row. CON-005 GATE evidence: accepted_at must be non-null before any early repayment posting is submitted to MOD-001. Mutable (only accepted_at is written post-creation). Managed by MOD-116.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
idempotency_key	text	NOT NULL	Caller-supplied; typically customer discharge reference
loan_account_id	uuid	NOT NULL REFERENCES credit.loan_accounts(id)
rate_period_id	uuid	NOT NULL REFERENCES credit.mortgage_rate_periods(id)	The active FIXED period at calculation time
outstanding_balance	numeric(18,2)	NOT NULL	Loan balance at calculation time
remaining_term_months	int	NOT NULL	Months remaining in fixed period at calculation time
reinvestment_rate	numeric(8,6)	NOT NULL	Swap rate used (tenor-matched; see CRE-006 §9)
break_cost_amount	numeric(18,2)	NOT NULL CHECK (break_cost_amount >= 0)	Calculated break cost; 0 if reinvestment_rate >= contract_rate
contract_rate	numeric(8,6)	NOT NULL	The loan's fixed interest_rate at calculation time
currency	text	NOT NULL CHECK (currency IN ('NZD','AUD'))
expires_at	timestamptz	NOT NULL	Quote validity window; v1 default: created_at + interval '5 days', AppConfig-overridable
disclosed_at	timestamptz	NOT NULL DEFAULT now()	Timestamp of quote publication to customer
accepted_at	timestamptz	NULL	Set when customer formally accepts the break cost — CON-005 GATE
accepted_by_customer_id	uuid	NULL	Customer party_id on acceptance; cross-domain ref, no FK
created_at	timestamptz	NOT NULL DEFAULT now()

Unique constraint: UNIQUE (idempotency_key) — quote requests are idempotent. CON-005 GATE: Application logic in discharge-mortgage Lambda checks accepted_at IS NOT NULL AND now() < expires_at before proceeding. Expired quotes must be recalculated. Indexes: (loan_account_id, created_at DESC); (idempotency_key).

`credit.overdraft_facilities` (MOD-117)¶

Master record for each linked overdraft facility. One active facility per transaction account (UNIQUE on account_id). Paired with a credit.loan_accounts row (product_type = 'OVERDRAFT') so that MOD-031 ECL, MOD-059 CCR, and MOD-030 IFRS 9 staging see overdraft balances without special casing. current_drawn_balance mirrors the drawn balance (v1 stub pending MOD-003 integration); any write to this column MUST also update loan_accounts.outstanding_principal in the same transaction.

Column	Type	Constraints	Description
facility_id	uuid	PK DEFAULT gen_random_uuid()
account_id	uuid	NOT NULL	cross-domain ref to bank_core `core.accounts(id)` — app-layer enforced, no FK
loan_account_id	uuid	NOT NULL REFERENCES credit.loan_accounts(id)	Paired OVERDRAFT product_type row; kept in sync for ECL/CCR/IFRS 9 pipeline
approved_limit	numeric(18,2)	NOT NULL	Board/credit-approved limit ceiling
current_limit	numeric(18,2)	NOT NULL	Active limit; may differ from approved_limit during reduction notice period
current_drawn_balance	numeric(18,2)	NOT NULL DEFAULT 0	v1 stub mirror — updated by update-drawn-balance Lambda and atomically with loan_accounts.outstanding_principal
consecutive_drawn_days	int	NOT NULL DEFAULT 0	FR-532 counter: calendar days the balance has been continuously negative; reset after 5 consecutive positive-balance days (AppConfig: OVERDRAFT_HARDSHIP_RESET_GRACE_DAYS default 5)
interest_rate_pct	numeric(8,5)	NOT NULL	Annual interest rate as percentage (see CRE-006 §10 for v1 defaults)
facility_fee	numeric(18,2)	NOT NULL	Monthly facility fee if any day in month had negative balance (see CRE-006 §10)
status	text	NOT NULL DEFAULT 'active'	CHECK (status IN ('active','suspended','closed'))
review_date	date	NOT NULL	Next scheduled affordability review
last_assessment_id	uuid	NULL	FK-by-convention to credit.affordability_assessments; cross-domain ref, no FK
activated_at	timestamptz	NOT NULL
closed_at	timestamptz	NULL
created_at	timestamptz	NOT NULL DEFAULT now()

Constraints: UNIQUE (account_id) — one active facility per transaction account. Indexes: (account_id) WHERE status = 'active'; (loan_account_id).

`credit.overdraft_daily_accruals` (MOD-117)¶

One row per facility per calendar day on which the balance was negative. Populated by the daily-accrual-sweep Lambda. posted flag flips to true when the accrual is included in a monthly interest charge (mutable on that single column only). UNIQUE on (facility_id, accrual_date) prevents duplicate sweep runs.

Column	Type	Constraints	Description
accrual_id	uuid	PK DEFAULT gen_random_uuid()
facility_id	uuid	NOT NULL REFERENCES credit.overdraft_facilities(facility_id)
accrual_date	date	NOT NULL	Calendar date of accrual
drawn_balance	numeric(18,2)	NOT NULL	Absolute drawn balance at end of day
daily_interest	numeric(18,6)	NOT NULL	`drawn_balance × (interest_rate_pct / 100 / 365)`
posted	boolean	NOT NULL DEFAULT false	Set to true when included in monthly-close charge
created_at	timestamptz	NOT NULL DEFAULT now()

Constraints: UNIQUE (facility_id, accrual_date).

`credit.overdraft_events` (MOD-117)¶

Cat 1 append-only audit log of all significant lifecycle events on the facility. Immutability trigger prevents any UPDATE or DELETE (same pattern as credit.collateral_valuations). Retained for 7 years via Snowflake CDC.

Column	Type	Constraints	Description
event_id	uuid	PK DEFAULT gen_random_uuid()
facility_id	uuid	NOT NULL REFERENCES credit.overdraft_facilities(facility_id)
event_type	text	NOT NULL CHECK (event_type IN ('facility_created','limit_increased','limit_reduced','limit_suspended','interest_charged','fee_assessed','fee_waived','hardship_flagged','unarranged_detected','facility_closed'))
event_data	jsonb	NULL	Structured payload per event_type (e.g. old/new limit for limit changes, consecutive_drawn_days for hardship_flagged)
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: (facility_id, created_at DESC).

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-credit.

Immutability triggers¶

Table	Trigger	Category
`credit.credit_decisions`	`trg_credit_decisions_immutable` — BEFORE UPDATE OR DELETE, calls `fn_immutable_row()`	Cat 1
`credit.credit_bureau_requests`	`trg_credit_bureau_requests_immutable` — BEFORE UPDATE OR DELETE, calls `fn_immutable_row()`	Cat 1
`credit.affordability_assessments`	`trg_affordability_assessments_immutable` — BEFORE UPDATE OR DELETE, calls `fn_immutable_row()`	Cat 1
`credit.ecl_provisions`	`trg_ecl_provisions_immutable` — BEFORE UPDATE OR DELETE, calls `fn_immutable_row()`	Cat 1
`credit.bureau_enquiries`	`trg_credit_bureau_enquiries_immutable` — BEFORE UPDATE OR DELETE, calls `credit.fn_immutable_row()`	Cat 1
`credit.credit_scores`	`trg_credit_scores_immutable` — BEFORE UPDATE OR DELETE, calls `credit.fn_immutable_row()`	Cat 1
`credit.risk_scores_mirror`	`trg_risk_scores_mirror_immutable` — BEFORE UPDATE OR DELETE, calls `credit.fn_immutable_row()`	Cat 1
`credit.disclosure_acknowledgements`	`trg_disclosure_acknowledgements_immutable` — BEFORE UPDATE OR DELETE, calls `credit.fn_immutable_row()` (MOD-029 V001). CON-004 GATE evidence for pre-approval acceptance; 7-year retention.	Cat 1
`credit.ifrs9_stage_allocations`	`trg_ifrs9_stage_allocations_immutable` — BEFORE UPDATE OR DELETE, calls `credit.fn_immutable_row()` (MOD-030 V001). IFRS 9 stage history; CRE-006 compliance.	Cat 1
`credit.collections_actions`	`trg_collections_actions_immutable` — BEFORE UPDATE OR DELETE, calls `credit.fn_immutable_row()` (MOD-065 V001). Collections audit log; CRE-002 / CON-003 compliance; 7-year retention via Snowflake CDC.	Cat 1
`credit.collateral_valuations`	`trg_collateral_valuations_immutable` — BEFORE UPDATE OR DELETE, calls `credit.fn_immutable_row()` (MOD-066 V001). Per-revaluation LTV snapshot; CRE-001 / CRE-005 compliance.	Cat 1
`credit.lvr_snapshots`	`trg_lvr_snapshots_immutable` — BEFORE UPDATE OR DELETE, calls `credit.fn_immutable_row()` (MOD-115 V001). Daily LVR snapshot; RBNZ BS19 / APRA APS 220 audit; REP-002 compliance.	Cat 1
`credit.overdraft_events`	`trg_overdraft_events_immutable` — BEFORE UPDATE OR DELETE, calls `credit.fn_immutable_row()` (MOD-117 V001). Overdraft lifecycle audit log; CON-008 / CRE-002 compliance; 7-year retention via Snowflake CDC.	Cat 1
`credit.break_cost_calculations`	`trg_break_cost_calculations_immutable` — BEFORE UPDATE OR DELETE, calls `credit.fn_break_cost_immutable()` (MOD-163 V001). DELETE always blocked; UPDATE whitelisted on `status`, `disclosure_acknowledgement_id`, `disclosure_content_hash`, `acknowledged_at` only. CRE-009 CALC + CON-005 GATE evidence; break-cost quote history.	Cat 1
`credit.loan_variation_events`	`trg_loan_variation_events_immutable` — BEFORE UPDATE OR DELETE, calls `credit.fn_immutable_row()` (MOD-132 V001). REP-004 LOG evidence; responsible lending audit; 7-year retention.	Cat 1

Other tables (credit.credit_applications, credit.loan_accounts, credit.collateral_register, credit.collateral_release_requests, credit.collateral_renewal_tasks, credit.property_security_details, credit.collections_cases, credit.repayment_schedules, credit.ecl_runs, credit.bureau_consents, credit.bureau_config, credit.idempotency_keys, credit.bureau_submission_batches, credit.bureau_disputes, credit.mortgage_rate_periods, credit.mortgage_notifications, credit.break_cost_disclosures, credit.overdraft_facilities, credit.overdraft_daily_accruals, credit.loan_variations) are mutable by design. credit.ecl_runs has application-layer immutability for terminal states (COMPLETE, FAILED) enforced by the Lambda handler, not a DB trigger. — application status, arrears state, loan lifecycle, bureau submission/dispute status transitions, and repayment schedule restructures are updated by the owning modules.

CHECK constraints¶

Table	Column	Constraint
`credit.loan_accounts`	`interest_rate`	`CHECK (interest_rate >= 0)`
`credit.loan_accounts`	`arrears_days`	`CHECK (arrears_days >= 0)`
`credit.ecl_provisions`	`pd_12m`	`CHECK (pd_12m >= 0 AND pd_12m <= 1)`
`credit.ecl_provisions`	`pd_lifetime`	`CHECK (pd_lifetime >= 0 AND pd_lifetime <= 1)`
`credit.ecl_provisions`	`lgd`	`CHECK (lgd >= 0 AND lgd <= 1)`
`credit.break_cost_disclosures`	`break_cost_amount`	`CHECK (break_cost_amount >= 0)`
`credit.break_cost_disclosures`	`reinvestment_rate`	`CHECK (reinvestment_rate >= 0)`
`credit.mortgage_rate_periods`	`interest_rate`	`CHECK (interest_rate >= 0)`
`credit.overdraft_facilities`	`current_drawn_balance`	`CHECK (current_drawn_balance >= 0)`
`credit.overdraft_facilities`	`consecutive_drawn_days`	`CHECK (consecutive_drawn_days >= 0)`
`credit.overdraft_facilities`	`interest_rate_pct`	`CHECK (interest_rate_pct >= 0)`
`credit.overdraft_facilities`	`facility_fee`	`CHECK (facility_fee >= 0)`
`credit.overdraft_daily_accruals`	`drawn_balance`	`CHECK (drawn_balance >= 0)`
`credit.overdraft_daily_accruals`	`daily_interest`	`CHECK (daily_interest >= 0)`
`credit.break_cost_calculations`	`chk_binding_has_component_id`	`CHECK (calculation_type = 'INDICATIVE' OR component_id IS NOT NULL)`
`credit.break_cost_calculations`	`chk_binding_has_valid_until`	`CHECK (calculation_type = 'INDICATIVE' OR valid_until IS NOT NULL)`
`credit.break_cost_calculations`	`chk_acknowledged_has_disclosure_id`	`CHECK (status NOT IN ('ACKNOWLEDGED','APPLIED') OR disclosure_acknowledgement_id IS NOT NULL)`
`credit.break_cost_calculations`	`chk_acknowledged_has_timestamp`	`CHECK (status NOT IN ('ACKNOWLEDGED','APPLIED') OR acknowledged_at IS NOT NULL)`

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

Rule	Reason	Owner
Credit score threshold gates	Score floor and risk-grade bands are AppConfig-configurable and depend on external bureau model outputs	MOD-029 Lambda
KYC gate for credit	Requires CDD tier from SD02 (different Neon database)	MOD-029 Lambda
HEM benchmark lookup	HEM data is sourced from bank-risk-platform Snowflake at assessment time	MOD-027 Lambda

Construction loan tables (MOD-121)¶

Added by MOD-121 (construction loan drawdown engine). All three tables live in the credit schema on the same Neon cluster as the rest of SD05.

`credit.construction_schedules` (mutable)¶

One schedule per mortgage. The total_drawn column is the authoritative source of truth for drawn principal; it is atomically mirrored to loan_accounts.outstanding_principal on every drawdown (AD k-6).

Column	Type	Notes
id	uuid PK
loan_account_id	uuid NOT NULL UNIQUE FK `loan_accounts(id)`	One schedule per mortgage
total_facility	numeric(18,2)	`> 0`
total_drawn	numeric(18,2)	`0 ≤ v ≤ total_facility`; mirrored to `loan_accounts.outstanding_principal`
construction_end_date	date NOT NULL
conversion_date	date NULL	Set on completion
status	text	`CHECK (active, complete, defaulted)`
jurisdiction	char(2)	`CHECK (NZ, AU)`
currency	char(3)
source_application_id	uuid NOT NULL
trace_id	uuid NOT NULL
created_at	timestamptz
updated_at	timestamptz

`credit.construction_tranches` (mutable)¶

Up to 10 tranches per schedule (tranche_number is UNIQUE with schedule_id). The sum of all tranche_amount values must equal schedule.total_facility.

Column	Type	Notes
id	uuid PK
schedule_id	uuid NOT NULL FK `construction_schedules(id)`
tranche_number	int	`1..10`; UNIQUE with `schedule_id`
tranche_amount	numeric(18,2)	`> 0`; sum = `schedule.total_facility`
milestone_description	text NOT NULL
status	text	`CHECK (pending, inspection_requested, certified, drawn, lapsed)`
certification_date	date NULL
certifier_reference	text NULL
drawdown_date	date NULL
posting_id	uuid NULL	MOD-001 `posting_id` when drawn
trace_id	uuid
created_at	timestamptz
updated_at	timestamptz

Cross-column constraints:

Constraint	Rule
`chk_construction_tranches_drawn_state`	`drawdown_date` + `posting_id` only when `status = 'drawn'` (DB-layer enforcement of FR-545)
`chk_construction_tranches_certified_state`	Certification fields (`certification_date`, `certifier_reference`) only when `status ∈ {certified, drawn, lapsed}`

`credit.construction_events` (Cat 1 immutable)¶

Append-only audit log via the shared credit.fn_immutable_row() trigger. 7-year retention via Snowflake CDC (MOD-042).

`event_type` enum value	Trigger
`schedule_created`	`create-schedule` handler
`milestone_inspection_requested`	`certify-tranche` handler (inspection_requested state)
`milestone_certified`	`certify-tranche` handler (certified state)
`milestone_lapsed`	`daily-expiry-sweep` handler
`drawdown_posted`	`request-drawdown` handler
`lvr_refreshed`	`loan_balance_updated` consumer
`amortisation_triggered`	`construction_phase_completed` consumer
`schedule_completed`	`request-drawdown` / `daily-expiry-sweep` (all tranches drawn or end date reached)
`schedule_defaulted`	`daily-expiry-sweep` (status transition)

Flexible Loan Facility tables (MOD-162)¶

Added by MOD-162 (Loan facility & component manager). Both tables live in the credit schema. They implement the layered model: facility envelopes wrap components, each component is backed by a loan_accounts row (k-1 ruling).

`credit.loan_facilities` (mutable)¶

Facility envelope per Flexible Loan Facility (PRD-024). One record per approved flexible credit decision.

Column	Type	Notes
id	uuid PK
customer_id	uuid NOT NULL	cross-domain ref to SD02 `party.parties(party_id)`
credit_decision_id	uuid NOT NULL UNIQUE FK `credit_decisions(id)`	One facility per decision
application_id	uuid NOT NULL FK `credit_applications(id)`
facility_limit	numeric(18,2) NOT NULL CHECK (> 0)
currency	char(3) NOT NULL
expiry_date	date NOT NULL
jurisdiction	char(2) NOT NULL CHECK (NZ, AU)
effective_interest_rate	numeric(8,6) NOT NULL	Σ(principal × rate) / Σ(principal) — recomputed on every component event (k-6)
status	text NOT NULL CHECK (ACTIVE, EXPIRED, CANCELLED)
master_agreement_ref	text NULL
trace_id	uuid NOT NULL
created_at	timestamptz NOT NULL
last_updated	timestamptz NOT NULL

`credit.loan_facility_components` (append-only; terminal-state Cat 1 immutable)¶

One row per component state transition (k-5 append-only state machine). The latest row at (facility_id, component_seq, created_at DESC) is the current state of that component. Terminal-state rows (MATURED, PREPAID, CANCELLED) are immutable via credit.fn_component_terminal_immutable() trigger (BEFORE UPDATE OR DELETE).

Column	Type	Notes
id	uuid PK
facility_id	uuid NOT NULL FK `loan_facilities(id)`
component_seq	int NOT NULL	Sequence number within facility
loan_account_id	uuid NOT NULL FK `loan_accounts(id)`	k-1 layered model: one loan_account per component
component_type	text NOT NULL CHECK (FIXED, FLOATING)
status	text NOT NULL CHECK (PENDING, ACTIVE, MATURED, PREPAID, CANCELLED)
rate_benchmark	text NULL CHECK (BKBM, BBSY)	FLOATING only; NULL for FIXED
benchmark_margin	numeric(8,6) NULL	FLOATING only; NULL for FIXED
trigger_reason	text NOT NULL	7 values including RATE_REPRICING, MATURITY, FULL_PREPAYMENT, CANCELLED
previous_component_id	uuid NULL FK self	Prior state row
model_version	text NOT NULL	Schema version for event replay
trace_id	uuid NOT NULL
created_at	timestamptz NOT NULL

Cross-column constraint:

Constraint	Rule
`chk_component_floating_metadata`	FLOATING requires `rate_benchmark` and `benchmark_margin`; FIXED forbids both

`credit.break_cost_calculations` (Cat 1 immutable — frozen columns; whitelisted status transitions)¶

Break-cost quote history for MOD-163. One row per indicative or binding calculation. Binding rows gate fixed-rate component prepayment via CON-005.

Column	Type	Notes
id	uuid PK	gen_random_uuid()
calculation_type	text NOT NULL	`'INDICATIVE'` \| `'BINDING'`
loan_facility_id	uuid NOT NULL FK `loan_facilities(id)`
component_id	uuid NULL FK `loan_facility_components(id)`	Required for BINDING (k-8); NULL = whole-facility indicative
party_id	text NOT NULL
contracted_rate	numeric(7,4) NOT NULL	Annual decimal e.g. `0.0550`
current_market_rate	numeric(7,4) NOT NULL	Annual decimal
market_rate_as_of	timestamptz NOT NULL	When MOD-085 last priced
market_rate_source	text NOT NULL	e.g. `"MOD-085:tenor_24m:rbnz_overnight"`
outstanding_principal	numeric(15,2) NOT NULL
remaining_term_months	int NOT NULL	Non-negative
discount_rate	numeric(7,4) NOT NULL	Annual decimal; v1 = current_market_rate
annuity_factor	numeric(20,10) NOT NULL	Audit field
break_cost	numeric(15,2) NOT NULL	Signed: positive = customer pays; negative = break benefit (k-7)
formula_version	text NOT NULL DEFAULT `'v1.0.0'`	k-1
valid_until	timestamptz NULL	NULL for INDICATIVE; BINDING = now + 5 NZ business days (k-9)
disclosure_acknowledgement_id	uuid NULL	MOD-050 disclosure id
disclosure_content_hash	text NULL	SHA-256 of canonical JSON incl. `formula_version` (k-4 amendment)
acknowledged_at	timestamptz NULL
status	text NOT NULL DEFAULT `'ACTIVE'`	`ACTIVE` \| `EXPIRED` \| `SUPERSEDED` \| `ACKNOWLEDGED` \| `APPLIED` \| `CANCELLED` (k-11)
idempotency_key	text NOT NULL
trace_id	text NOT NULL
created_at	timestamptz NOT NULL DEFAULT now()

Immutability: trg_break_cost_calculations_immutable (BEFORE UPDATE OR DELETE, MOD-163 V001) — DELETE always blocked; UPDATE permitted only on status, disclosure_acknowledgement_id, disclosure_content_hash, acknowledged_at. Status machine: ACTIVE → EXPIRED (nightly cron) | SUPERSEDED (k-12: atomic override when new binding for same component) | ACKNOWLEDGED (CON-005 GATE) | APPLIED (MOD-065 at prepayment) | CANCELLED. ADR-048 Cat 1.

Loan variation tables (MOD-132)¶

Added by MOD-132 (loan restructure & variation workflow). Both tables live in the credit schema on the same Neon cluster. MOD-132 V001 creates both tables.

`credit.loan_variations` (mutable)¶

Mutable workflow state machine record for each loan variation request. One row per variation request; status transitions are updated in place via UPDATE. The full audit trail lives in credit.loan_variation_events (Cat 1 immutable). Reuses credit.idempotency_keys (module_id='MOD-132', 24h TTL) for duplicate-submission protection.

Column	Type	Constraints	Description
variation_id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
loan_account_id	uuid	NOT NULL REFERENCES credit.loan_accounts(id)
variation_type	text	NOT NULL CHECK (variation_type IN ('term_extension','frequency_change','rate_type_switch','repayment_restructure','capitalisation_of_arrears','early_repayment'))
previous_terms	jsonb	NOT NULL	Full snapshot of loan terms before variation (rate, term, frequency, repayment amount, rate type, term end date)
proposed_terms	jsonb	NOT NULL	Requested new terms
status	text	NOT NULL DEFAULT 'requested' CHECK (status IN ('requested','assessing','assessed','disclosed','confirmed','rejected','expired'))	Workflow state machine
assessment_required	bool	NOT NULL DEFAULT false	FR-589: true for material variations (term extension >12m, capitalisation of arrears, repayment restructure)
break_cost_required	bool	NOT NULL DEFAULT false	True when variation involves fixed-rate period exit
credit_check_id	uuid		MOD-029 credit_decisions ref; populated when assessment_required = true
disclosure_id	uuid		MOD-050 disclosure ref; populated when disclosure dispatched
break_cost_calculation_id	uuid		MOD-163 break_cost_calculations ref; populated when break_cost_required = true
break_cost_acknowledgement_id	uuid		MOD-050 acknowledgement ref; required on CONFIRMED path when break_cost_required = true (CON-005 GATE)
acknowledged_at	timestamptz		Set when break cost acknowledged
requested_by_party_id	text	NOT NULL	party_id of customer or agent submitting the variation
requested_by_type	text	NOT NULL CHECK (requested_by_type IN ('CUSTOMER','AGENT'))
agent_id	text		Populated when requested_by_type = 'AGENT'; NULL for customer-initiated variations
case_id	text		MOD-053 case ref; populated on agent escalation
materiality_rules_version	text	NOT NULL	Version of the materiality table applied at request time (e.g. 'v1.0.0') — audit immutability of the decision
rejection_reason	text		Human-readable rejection detail
rejection_source	text	CHECK (rejection_source IN ('CREDIT_DECLINED','CUSTOMER_DECLINED','AGENT_REJECTED','EXPIRED'))
idempotency_key	text	NOT NULL	Client-supplied key; deduplicated via credit.idempotency_keys
trace_id	text	NOT NULL	ADR-031 trace propagation
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()	Touch-updated on every status transition
expires_at	timestamptz		TTL for disclosed state (5 business days); swept daily by expire-disclosed-sweep Lambda

Indexes: - PRIMARY KEY (variation_id) - idx_loan_variations_loan_account_id on (loan_account_id) - idx_loan_variations_status_active partial WHERE status NOT IN ('confirmed','rejected','expired') - Partial UNIQUE uniq_loan_variations_one_inflight on (loan_account_id, status) WHERE status IN ('requested','assessing','assessed','disclosed') — enforces one in-flight variation per loan account; second concurrent submission returns 409 IN_FLIGHT_VARIATION_EXISTS (k-8)

State machine:

requested
  ├─ assessment_required + break_cost_required:  → assessing → assessed → disclosed → confirmed
  ├─ assessment_required only:                   → assessing → assessed → disclosed → confirmed
  ├─ break_cost_required only:                   → disclosed → confirmed
  └─ neither:                                    → disclosed → confirmed
any non-terminal → rejected (terminal)
disclosed → expired (terminal, TTL 5 business days, set by expire-disclosed-sweep)

`credit.loan_variation_events` (Cat 1 immutable)¶

Append-only audit log of all loan variation lifecycle events. FR-592 REP-004 LOG evidence — available for regulatory examination without reconstruction. Every status transition and gate event creates a row. ADR-048 Cat 1 — UPDATE and DELETE blocked by trigger.

Column	Type	Constraints	Description
event_id	uuid	PRIMARY KEY DEFAULT gen_random_uuid()
variation_id	uuid	NOT NULL REFERENCES credit.loan_variations(variation_id)
event_type	text	NOT NULL CHECK (event_type IN ('REQUESTED','ASSESSMENT_INVOKED','ASSESSMENT_APPROVED','ASSESSMENT_DECLINED','DISCLOSURE_DISPATCHED','BREAK_COST_CALCULATED','BREAK_COST_ACKNOWLEDGED','CONFIRMED','REJECTED','EXPIRED','CASE_ESCALATED'))
actor_party_id	text	NOT NULL	party_id of the actor (customer, agent, or system module ID)
actor_type	text	NOT NULL CHECK (actor_type IN ('CUSTOMER','AGENT','SYSTEM'))
detail	jsonb	NOT NULL	Event-specific payload: before/after term deltas, reason codes, referenced IDs
trace_id	text	NOT NULL	ADR-031 trace propagation
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_loan_variation_events_variation_id on (variation_id, created_at DESC) - idx_loan_variation_events_loan_account on (variation_id) — via JOIN to loan_variations for account-level audit sweep

Immutability: trg_loan_variation_events_immutable — BEFORE UPDATE OR DELETE, calls credit.fn_immutable_row(). ADR-048 Cat 1. 7-year retention via Snowflake CDC (REP-004 responsible lending audit requirement).

Cross-domain references¶

This table	Column	References	Notes
credit.credit_applications	party_id	SD02 `party.parties(party_id)`	Applications require a verified party
credit.credit_decisions	party_id	SD02 `party.parties(party_id)`
credit.loan_accounts	account_id	SD01 `accounts.accounts(id)`	Loan funds flow through the core banking account
credit.ecl_provisions	gl_posting_id	SD01 `accounts.postings(id)`	Cross-domain reference — no FK enforced (different Neon databases). ECL movements posted to GL via MOD-001.
credit.overdraft_facilities	account_id	SD01 `accounts.accounts(id)`	Cross-domain reference — no FK enforced (different Neon databases). App-layer enforced.
credit.loan_facility_components	loan_account_id	`loan_accounts(id)`	k-1 layered model FK — each component is backed by a loan_account row
credit.break_cost_calculations	loan_facility_id	`loan_facilities(id)`	FK within SD05 credit schema
credit.break_cost_calculations	component_id	`loan_facility_components(id)`	Nullable; required for BINDING quotes (k-8)

Consumers of SD05 data: SD06 Snowflake receives all credit tables via CDC (MOD-042) for IFRS 9 portfolio-level ECL modelling (MOD-030, MOD-031), capital ratio calculations (MOD-033), and prudential reporting (MOD-036). SD08 back-office (MOD-074) reads credit applications and loan accounts for customer 360 views. Credit bureau submissions (MOD-059) write back to credit_bureau_requests from bank-credit.

SD05 — Credit data model¶

Schema: credit¶

credit.credit_applications¶

credit.credit_decisions¶

credit.affordability_assessments¶

credit.credit_scores¶

credit.risk_scores_mirror¶

credit.hem_benchmarks (temporary stand-in)¶

credit.credit_bureau_requests¶

credit.bureau_submission_batches¶

credit.bureau_disputes¶

credit.ecl_provisions¶

credit.ecl_runs (MOD-031)¶

credit.ifrs9_stage_allocations (MOD-030)¶

credit.loan_accounts¶

credit.collateral_register¶

credit.collateral_valuations¶

credit.collateral_release_requests¶

credit.collateral_renewal_tasks¶

credit.property_security_details¶

credit.lvr_snapshots¶

credit.collections_cases¶

credit.repayment_schedules (MOD-065)¶

credit.collections_actions (MOD-065)¶

credit.bureau_enquiries¶

credit.bureau_consents (temporary stand-in)¶

credit.disclosure_acknowledgements (temporary stand-in)¶

credit.bureau_config¶

credit.idempotency_keys¶

credit.mortgage_rate_periods (MOD-116)¶

credit.mortgage_notifications (MOD-116)¶

credit.break_cost_disclosures (MOD-116)¶

credit.overdraft_facilities (MOD-117)¶

credit.overdraft_daily_accruals (MOD-117)¶

credit.overdraft_events (MOD-117)¶