SD03 — AML Monitoring data model

Database: bank_aml (Neon Postgres, ap-southeast-2) Repo: bank-aml 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-035 (Snowflake data residency), ADR-048 (database-enforced invariants) Schema owner: SD03 AML Transaction Monitoring Platform

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

The aml schema is the primary write domain for MOD-016 (rule-based typology engine), MOD-017 (ML behavioural scoring), MOD-018 (alert case management), and MOD-019 (regulatory report submission). All alert and case records are immutable once created — status transitions are tracked by inserting new event rows, not updating the original record. Regulatory submissions, once filed, are never edited.

---

Schema: aml

aml.aml_alerts

One row per alert generated by MOD-016 (rule engine) or MOD-017 (ML model). Alerts may be dismissed or escalated into cases. An alert that does not result in a case still retains its record for audit.

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)
alert_type	text	NOT NULL CHECK (alert_type IN ('RULE','ML_MODEL','COMBINED'))	Generating source
typology_code	text	NOT NULL	Typology identifier (e.g. STRUCT_001, RAPID_MOV_003)
rule_version	text		Rule set version; NULL for ML-only alerts
model_version	text		ML model version; NULL for rule-only alerts
risk_score	numeric(5,2)	CHECK (risk_score IS NULL OR (risk_score >= 0 AND risk_score <= 100))	Composite alert risk score 0–100
alert_status	text	NOT NULL CHECK (alert_status IN ('OPEN','UNDER_REVIEW','DISMISSED','ESCALATED_TO_CASE','CLOSED'))
triggered_at	timestamptz	NOT NULL DEFAULT now()
reviewed_at	timestamptz		When analyst first opened the alert
closed_at	timestamptz
assigned_to	text		Staff ID of assigned analyst
trigger_transactions	jsonb	NOT NULL DEFAULT '[]'	Array of payment_ids that triggered the alert
trigger_window_start	timestamptz		Start of the transaction window assessed
trigger_window_end	timestamptz		End of the transaction window assessed
case_id	uuid	REFERENCES aml.aml_cases(id) DEFERRABLE INITIALLY DEFERRED	Populated when alert is escalated; FK retro-added by MOD-018 V001 (was a plain uuid in MOD-016 V001 because aml_cases did not yet exist)
policy_refs	jsonb	NOT NULL DEFAULT '["AML-005"]'
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_aml_alerts_party_id on (party_id) - idx_aml_alerts_status on (alert_status) WHERE alert_status IN ('OPEN','UNDER_REVIEW') - idx_aml_alerts_triggered_at on (triggered_at DESC) - idx_aml_alerts_typology_code on (typology_code)

Notes: trigger_transactions stores payment IDs for traceability but is not a FK-enforced array. Payment records may exist in SD04 at the time of alert creation. Analysts use the case management UI (MOD-018) to navigate from alert to payment details.

---

aml.aml_cases

A case groups one or more related alerts for consolidated analyst review. Cases may result in a Suspicious Activity Report (SAR) / Suspicious Matter Report (SMR). Case creation is initiated by MOD-018 when an analyst escalates an alert.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
case_reference	text	NOT NULL UNIQUE	Human-readable case reference (e.g. CASE-2026-004821)
party_id	uuid	NOT NULL	cross-domain ref to SD02 party.parties(party_id)
case_type	text	NOT NULL CHECK (case_type IN ('SUSPICIOUS_ACTIVITY','STRUCTURING','SANCTIONS_BREACH','PEP_REVIEW','FRAUD_AML','OTHER'))
case_status	text	NOT NULL CHECK (case_status IN ('OPEN','UNDER_REVIEW','PENDING_SAR','SAR_FILED','CLOSED_NO_ACTION','CLOSED_REFERRED'))
risk_level	text	NOT NULL CHECK (risk_level IN ('LOW','MEDIUM','HIGH','CRITICAL'))
opened_at	timestamptz	NOT NULL DEFAULT now()
closed_at	timestamptz
assigned_to	text		Primary analyst staff ID
supervisor_id	text		Supervisor staff ID for CRITICAL cases
narrative	text		Case summary narrative; required before closure
sar_required	boolean	NOT NULL DEFAULT false	Whether case will result in a SAR/SMR filing
submission_id	uuid	REFERENCES aml.regulatory_submissions(id)	Populated after SAR is filed
thirty_day_deadline	date		Statutory SAR filing deadline
max_alert_risk_score	numeric(5,2)	NOT NULL DEFAULT 0	Highest risk_score across alerts attached to this case; updated by MOD-018 whenever an alert is attached. Drives the SAR threshold gate (FR-116)
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))	Jurisdiction of the case — required for jurisdictional reporting and event payloads
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL

Indexes: - idx_aml_cases_party_id on (party_id) - idx_aml_cases_status on (case_status) WHERE case_status NOT IN ('CLOSED_NO_ACTION','CLOSED_REFERRED') - idx_aml_cases_sar_deadline on (thirty_day_deadline) WHERE sar_required = true AND case_status = 'PENDING_SAR' - idx_aml_cases_case_reference on (case_reference) - idx_aml_cases_jurisdiction on (jurisdiction)

---

aml.case_events

Append-only audit ledger for every action taken on a case, written by MOD-018. One row per event — case opened, alert attached, status changed, note added, escalation, supervisor approval, closure. Required for FR-115 evidence; satisfies AML-005, AML-006, and GOV-006 in LOG mode.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
case_id	uuid	NOT NULL REFERENCES aml.aml_cases(id)
event_type	text	NOT NULL CHECK (event_type IN ('CASE_OPENED','ALERT_ATTACHED','CASE_ASSIGNED','CASE_ACCEPTED','CASE_DECLINED','CASE_REASSIGNED','NOTE_ADDED','STATUS_CHANGED','CASE_ESCALATED','CASE_SUPERVISOR_APPROVED','CASE_CLOSED'))
occurred_at	timestamptz	NOT NULL DEFAULT now()
actor_staff_id	text		NULL for SYSTEM-generated events
actor_kind	text	NOT NULL CHECK (actor_kind IN ('staff','system'))
detail	jsonb	NOT NULL DEFAULT '{}'	Structured event payload; PII must be redacted before insert
trace_id	uuid	NOT NULL	Correlation ID for distributed tracing
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_case_events_case_id_occurred_at on (case_id, occurred_at DESC) - idx_case_events_event_type on (event_type) - idx_case_events_actor_staff_id on (actor_staff_id) WHERE actor_staff_id IS NOT NULL

Notes: Append-only — enforced by BEFORE-row trigger (re-uses MOD-016's aml.reject_mutation() function, ADR-048 Category 1). No updated_at column.

---

aml.analyst_pool

Ops-managed registry of AML compliance analysts and supervisors. The is_supervisor flag drives the FR-116 NO_ACTION close gate: only a supervisor may approve a case closure with no SAR filed when max_alert_risk_score exceeds the AppConfig threshold. Managed via the MOD-018 internal admin API.

Column	Type	Constraints	Description
staff_id	text	PK	Staff identifier (matches SSO/Cognito username)
display_name	text	NOT NULL
email	text	NOT NULL
is_supervisor	boolean	NOT NULL DEFAULT false	Whether this analyst can approve NO_ACTION closures above the SAR threshold
active	boolean	NOT NULL DEFAULT true	Inactive analysts are removed from the round-robin queue
last_assigned_at	timestamptz		Updated on each round-robin assignment; NULLS FIRST in ordering
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_analyst_pool_active on (active) WHERE active = true - idx_analyst_pool_supervisors on (is_supervisor) WHERE is_supervisor = true AND active = true - idx_analyst_pool_round_robin on (last_assigned_at NULLS FIRST) WHERE active = true AND is_supervisor = false

---

aml.case_assignments

Per-attempt assignment record tracking every offer, acceptance, decline, and supersession in the case workload balancer. Prevents the round-robin balancer from re-offering a declined case to the same analyst. One row per assignment attempt; a case may have multiple rows (e.g. declined then re-assigned).

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
case_id	uuid	NOT NULL REFERENCES aml.aml_cases(id)
staff_id	text	NOT NULL REFERENCES aml.analyst_pool(staff_id)
assigned_at	timestamptz	NOT NULL DEFAULT now()
accepted_at	timestamptz		NULL until analyst accepts
declined_at	timestamptz		NULL unless analyst declines
decline_reason	text
superseded_at	timestamptz		Set when a later assignment supersedes this one
created_at	timestamptz	NOT NULL DEFAULT now()
updated_at	timestamptz	NOT NULL DEFAULT now()

CHECK (accepted_at IS NULL OR declined_at IS NULL) — a single assignment attempt cannot be both accepted and declined.

Indexes: - idx_case_assignments_case_id on (case_id) - idx_case_assignments_staff_active on (staff_id) WHERE accepted_at IS NULL AND declined_at IS NULL AND superseded_at IS NULL

---

aml.typology_matches

Detailed record of each rule or model pattern match that contributed to an alert. One alert can have multiple typology matches if multiple rules fired. Used for pattern analysis and rule refinement in Snowflake.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
alert_id	uuid	NOT NULL REFERENCES aml.aml_alerts(id)
typology_code	text	NOT NULL	Specific typology pattern code
typology_description	text	NOT NULL	Human-readable typology description
match_type	text	NOT NULL CHECK (match_type IN ('RULE','ML_FEATURE','GRAPH_PATTERN'))
rule_id	text		Rule identifier if match_type = RULE
feature_name	text		ML feature if match_type = ML_FEATURE
observed_value	numeric(18,4)		Numeric value that triggered the match
threshold_value	numeric(18,4)		Threshold the observed value crossed
confidence	numeric(5,2)	CHECK (confidence IS NULL OR (confidence >= 0 AND confidence <= 100))	Match confidence 0–100 for ML matches
transaction_ids	jsonb	NOT NULL DEFAULT '[]'	Specific transactions involved in this match
matched_at	timestamptz	NOT NULL DEFAULT now()
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_typology_matches_alert_id on (alert_id) - idx_typology_matches_typology_code on (typology_code) - idx_typology_matches_matched_at on (matched_at DESC)

Notes: Rows are immutable after insertion — enforced by trg_typology_matches_immutable (ADR-048 Category 1). Pattern match evidence must not be altered after an alert is created.

---

aml.regulatory_submissions

Immutable record of every regulatory report filed — IFTI (AU, AUSTRAC), CMIR (NZ, RBNZ), SAR/SMR. Submissions are never edited after filing. Amendments create a new row with amends_submission_id set.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
submission_reference	text	NOT NULL UNIQUE	Bank's internal reference for the submission
report_type	text	NOT NULL CHECK (report_type IN ('IFTI','CMIR','SAR','SMR','TTR','SUSPICIOUS_MATTER'))
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))
regulator	text	NOT NULL	e.g. AUSTRAC, RBNZ
subject_party_id	uuid		cross-domain ref to SD02 party.parties(party_id) — NULL for bulk threshold reports
related_case_id	uuid	REFERENCES aml.aml_cases(id)	For SAR/SMR submissions
payment_ids	jsonb	NOT NULL DEFAULT '[]'	Payment IDs included in this report
submitted_at	timestamptz		NULL until confirmed submitted
submission_status	text	NOT NULL CHECK (submission_status IN ('DRAFT','PENDING_REVIEW','APPROVED','SUBMITTED','ACKNOWLEDGED','REJECTED'))
regulator_reference	text		Reference number returned by the regulator
regulator_acknowledged_at	timestamptz		When regulator confirmed receipt
prepared_by	text	NOT NULL	Module or staff ID that prepared the report
approved_by	text		Staff ID of approver (required before submission)
report_payload	jsonb	NOT NULL DEFAULT '{}'	Full structured report payload as submitted
content_hash	text	NOT NULL	SHA-256 of report_payload serialised as canonical UTF-8 JSON, computed at INSERT time — FR-119 tamper-evidence requirement
amends_submission_id	uuid	REFERENCES aml.regulatory_submissions(id)	For amendment submissions
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_regulatory_submissions_report_type_jurisdiction on (report_type, jurisdiction) - idx_regulatory_submissions_submitted_at on (submitted_at DESC) WHERE submitted_at IS NOT NULL - idx_regulatory_submissions_party_id on (subject_party_id) WHERE subject_party_id IS NOT NULL - idx_regulatory_submissions_status on (submission_status) WHERE submission_status IN ('DRAFT','PENDING_REVIEW','APPROVED')

Notes: This table captures the filed artifact. MOD-019 is responsible for generating the structured report_payload in the regulator-specific format (AUSTRAC GoAML schema for IFTI; RBNZ submission schema for CMIR). The payload is stored here for evidence of what was submitted, not for re-processing. Immutability is enforced by trg_regulatory_submissions_immutable (ADR-048 Category 1).

---

aml.posting_history_cache

Local materialised cache of bank.core.posting_completed EventBridge events. Written by the MOD-016 ingest Lambda on every posting event received from the bank-core bus. Provides the lookback transaction history that pattern rules (structuring, rapid-movement, round-trip) need for multi-transaction window analysis. This is the authoritative hot-path data source for MOD-016 rule evaluation — Snowflake/Iceberg is not on the real-time path.

Rows are insert-once and never updated. A scheduled Lambda prunes rows older than the configurable retention window (default 90 days, stored in AppConfig). payment_id has a UNIQUE constraint to prevent duplicate entries from event replay.

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)
account_id	uuid	NOT NULL	Account posted to in SD01
payment_id	uuid	NOT NULL UNIQUE	From SD04 payments — dedup key on ingest
direction	text	NOT NULL CHECK (direction IN ('DEBIT','CREDIT'))
amount_nzd	numeric(18,4)	NOT NULL	Amount normalised to NZD for cross-currency pattern rules
original_amount	numeric(18,4)	NOT NULL	Original transaction amount
original_currency	char(3)	NOT NULL	ISO 4217 currency code
counterparty_account_id	uuid		SD01 account ID for internal transfers; NULL for external
counterparty_bsb	text		AU BSB or NZ bank number for external counterparties
channel	text		SWIFT / OSKO / RTGS / INTERNAL / DIRECT_DEBIT
jurisdiction	char(2)	NOT NULL CHECK (jurisdiction IN ('NZ','AU'))	Jurisdiction from the source posting event — required for jurisdiction-aware typology rules (HIRISK_GEO_001, CASH_THR_001)
posted_at	timestamptz	NOT NULL	When posting was committed in SD01
cached_at	timestamptz	NOT NULL DEFAULT now()	When this cache row was written

Indexes: - idx_phc_party_posted_at on (party_id, posted_at DESC) — primary lookback query pattern - idx_phc_payment_id on (payment_id) — dedup check on ingest - idx_phc_posted_at on (posted_at DESC) — retention pruning sweep

---

aml.rule_definitions

Registry of all configured typology rules. Each rule has a stable rule_id, a typology code, and a JSONB default parameters blob. Live rule parameters are stored in AppConfig (FR-107, ADR-033) for sub-5-minute hot reload; this table records the rule's identity, description, and the AppConfig profile key where live parameters live.

This table is the source-of-truth for which rules are registered. AppConfig is the source-of-truth for current live parameter values.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
rule_id	text	NOT NULL UNIQUE	Stable machine identifier (e.g. STRUCT_001, RAPID_MOV_003)
typology_code	text	NOT NULL	AML typology code; one typology may have multiple rules
rule_name	text	NOT NULL	Human-readable rule name
description	text	NOT NULL	What pattern this rule detects
enabled	boolean	NOT NULL DEFAULT true	Soft-disable without deleting the rule definition
default_parameters	jsonb	NOT NULL DEFAULT '{}'	Default thresholds and window sizes — not the live values
appconfig_profile_key	text	NOT NULL	Key in AppConfig where live parameters are stored
created_at	timestamptz	NOT NULL DEFAULT now()
created_by	text	NOT NULL	Staff ID or module ID that registered the rule
retired_at	timestamptz		Set when rule is permanently retired

Indexes: - idx_rule_definitions_typology_code on (typology_code) - idx_rule_definitions_enabled on (enabled) WHERE enabled = true

---

aml.rule_executions

Append-only audit log of every rule evaluation run. Records whether each rule fired, passed, or errored for a given transaction. Required for FR-108 — the audit trail must cover non-alert outcomes (passes and errors), not only alerts. High-volume table: approximately one row per rule per transaction evaluated. Rows are immutable.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
payment_id	uuid	NOT NULL	Payment that triggered evaluation
party_id	uuid	NOT NULL	Party whose history was evaluated
rule_id	text	NOT NULL REFERENCES aml.rule_definitions(rule_id)
rule_version	text	NOT NULL	AppConfig deployment version at time of evaluation
evaluated_at	timestamptz	NOT NULL DEFAULT now()
window_start	timestamptz		Start of transaction history window assessed (if applicable)
window_end	timestamptz		End of transaction history window assessed
result	text	NOT NULL CHECK (result IN ('ALERT','PASS','ERROR'))
alert_id	uuid	REFERENCES aml.aml_alerts(id)	Populated when result = ALERT
execution_ms	integer		Rule evaluation duration for this row
error_detail	text		Error message when result = ERROR
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_rule_executions_payment_id on (payment_id) - idx_rule_executions_party_evaluated_at on (party_id, evaluated_at DESC) - idx_rule_executions_rule_id_evaluated_at on (rule_id, evaluated_at DESC) - idx_rule_executions_result on (result) WHERE result IN ('ALERT','ERROR')

Notes: If row volume exceeds 50M/month, partition by evaluated_at month. Rows are never updated or deleted within the regulatory retention period. Immutability is enforced by trg_rule_executions_immutable (ADR-048 Category 1).

---

aml.rule_config_history

Bank-internal audit log of every change to rule parameters. Every time live parameters change in AppConfig, MOD-016 writes an immutable row capturing the previous and new values, who made the change, and a mandatory justification. AppConfig's own version history is the primary operational log; this table provides a bank-native queryable audit trail for regulatory examination.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
rule_id	text	NOT NULL REFERENCES aml.rule_definitions(rule_id)
changed_at	timestamptz	NOT NULL DEFAULT now()
changed_by	text	NOT NULL	Staff ID of person who deployed the config change
previous_config	jsonb	NOT NULL	Full parameter values before the change
new_config	jsonb	NOT NULL	Full parameter values after the change
appconfig_version	text	NOT NULL	AppConfig deployment version identifier
change_reason	text	NOT NULL	Mandatory documented justification for the change
effective_from	timestamptz	NOT NULL	When the new config became live in AppConfig
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_rule_config_history_rule_id_changed_at on (rule_id, changed_at DESC)

Notes: change_reason NOT NULL enforces documented justification for every rule parameter change. Rows are immutable — never updated or deleted. Immutability is enforced by trg_rule_config_history_immutable (ADR-048 Category 1).

---

aml.behavioural_scores

Pre-computed ML behavioural risk scores for each customer, written by MOD-017's write-back Lambda following the ADR-047 pattern (Snowflake Cortex → Neon). MOD-016 reads this table on every posting event to produce COMBINED or ML_MODEL alerts. The lookup replaces inline inference and satisfies NFR-021 (≤200ms) with a sub-10ms Postgres read.

Score freshness: the nightly Snowflake Task re-scores all customers active in the last 90 days. valid_until is derived by the write-back Lambda from the /bank/{stage}/mod-017/model/validity-hours SSM parameter (default 24h if unset) — the Snowflake Task does not include valid_until in the payload, so the freshness window can be retuned without redeploying Snowflake. In practice the Lambda sets valid_until = scored_at + validity_hours, yielding approximately 26 hours ahead under the default. Event-triggered incremental re-scores (on significant posting events) refresh within 15 minutes. MOD-016 checks valid_until before consuming the score — if stale it runs rule-only and records alert_type = 'RULE' rather than 'COMBINED'. Rows are immutable; score supersession inserts a new row. Old rows are retained for FR-111 retrospective model performance analysis. Champion/challenger governance (DT-005) is supported via model_role.

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)
model_version	text	NOT NULL	Cortex model version identifier (e.g. v1.2-champion)
model_role	text	NOT NULL CHECK (model_role IN ('CHAMPION','CHALLENGER'))	Champion is the operative scoring model; challenger is evaluated alongside for performance comparison
score	numeric(5,0)	NOT NULL CHECK (score >= 0 AND score <= 1000)	Behavioural risk score 0–1000 (FR-109)
risk_tier	text	NOT NULL CHECK (risk_tier IN ('LOW','MEDIUM','HIGH','CRITICAL'))	Derived tier — aligns with AML alert risk levels
feature_vector_hash	text	NOT NULL	SHA256 of the feature vector used; enables retrospective model analysis without reprocessing raw data (FR-111)
score_reasons	jsonb	NOT NULL DEFAULT '[]'	Top contributing features and their weights
scored_at	timestamptz	NOT NULL	When Snowflake Cortex ran the inference
valid_until	timestamptz	NOT NULL	Freshness boundary — MOD-016 treats scores past this timestamp as stale and falls back to rule-only
triggered_by	text	NOT NULL CHECK (triggered_by IN ('SCHEDULED','EVENT'))	Whether produced by the nightly batch or an event-triggered incremental run
source_event_id	text		Posting event ID that triggered an EVENT re-score; NULL for SCHEDULED runs
created_at	timestamptz	NOT NULL DEFAULT now()

UNIQUE (party_id, model_version, scored_at) — retry-safe idempotency key. The write-back Lambda uses INSERT ... ON CONFLICT (party_id, model_version, scored_at) DO NOTHING. Multiple scored_at rows for the same party and model version are legitimate (successive re-scores retained for FR-111 retrospective analysis).

Indexes: - idx_behavioural_scores_party_champion on (party_id, scored_at DESC) WHERE model_role = 'CHAMPION' — primary MOD-016 read pattern - idx_behavioural_scores_valid_until_champion on (valid_until) WHERE model_role = 'CHAMPION' — freshness sweeper - idx_behavioural_scores_model_version on (model_version, scored_at DESC) — champion/challenger management and rollback

Notes: Rows are immutable after insertion — enforced by trg_behavioural_scores_immutable (ADR-048 Category 1). Write-back follows ADR-047: MOD-017 owns the Snowflake Task and write-back Lambda that delivers scores from risk_aml.behavioural_model_scores in SD06 Snowflake to this table. FR-112 rollback: reconfigure the Snowflake Task to the previous model version and trigger one full write-back run — achievable within 30 minutes.

---

aml.risk_scores_mirror

Local mirror of MOD-039's customer composite risk scores, populated by the MOD-016 risk-score-consumer Lambda from the bank.risk-platform / customer_risk_score_updated EventBridge event. Read by AML typology rules (MOD-016) and ML features (MOD-017) — avoids cross-bus or Snowflake calls on the hot path.

Column	Type	Constraint	Notes
party_id	uuid	NOT NULL	Cross-domain ref to SD02 party.parties.party_id — no FK (cross-database; application-enforced)
composite_risk_score	double precision	NOT NULL	MOD-039 XGBoost composite score, 0–100
risk_tier	varchar(10)	NOT NULL CHECK (LOW|MEDIUM|HIGH|CRITICAL)
previous_risk_tier	varchar(10)	nullable, CHECK same enum	Null on first-ever score for the party
tier_changed	boolean	NOT NULL
score_version	varchar(50)	NOT NULL	Producer's model version label
scored_at	timestamptz	NOT NULL	From event detail (not envelope event_time)
idempotency_key	varchar(200)	NOT NULL	From event detail; trace-correlation
mirror_updated_at	timestamptz	NOT NULL DEFAULT now()	Local write timestamp
PRIMARY KEY		(party_id, scored_at)	Natural dedup key

Indexes: - idx_risk_scores_mirror_party_id_scored_at on (party_id, scored_at DESC) — primary read pattern (getLatestRiskScore)

Invariants (ADR-048): - trg_risk_scores_mirror_immutable — append-only via aml.fn_immutable_row() SECURITY DEFINER - ON CONFLICT (party_id, scored_at) DO NOTHING at write time — EventBridge replays are idempotent

Grants: bank_aml_app_user: SELECT, INSERT — bank_aml_readonly: SELECT

Producer: SD06 / MOD-039 on the bank-risk-platform bus.

---

aml.model_deployment_history

Append-only audit log of all model lifecycle events — promotions to champion, rollbacks, challenger deployments, and retirements. Written by MOD-017. Required for DT-005 LOG mode (champion/challenger governance). One row per lifecycle event; rows are never updated or deleted.

Column	Type	Constraints	Description
id	uuid	PK DEFAULT gen_random_uuid()
model_version	text	NOT NULL	Model version being acted upon (e.g. v1.2)
previous_model_version	text		Preceding champion version; required on PROMOTED_TO_CHAMPION and ROLLED_BACK rows (enforced by chk_mdh_previous_version_required)
model_role	text	NOT NULL CHECK (model_role IN ('CHAMPION','CHALLENGER'))	The model's role at the time of the event. Orthogonal to event_type: a ROLLED_BACK row may carry CHAMPION or CHALLENGER depending on which version was being rolled back. Required for DT-005 audit queries.
trace_id	uuid	NOT NULL	Per ADR-031, every audit-class row carries a trace_id propagated from the originating runbook invocation
event_type	text	NOT NULL CHECK (event_type IN ('PROMOTED_TO_CHAMPION','ROLLED_BACK','RETIRED','CHALLENGER_DEPLOYED'))
effective_at	timestamptz	NOT NULL	When the model lifecycle change took effect
deployed_by	text	NOT NULL	Staff ID or automation principal that triggered the change
change_reason	text	NOT NULL	Required human-readable rationale
champion_metrics	jsonb		Precision, recall, and AUC. Required on PROMOTED_TO_CHAMPION rows; must be NULL on all other event types (enforced by chk_mdh_promotion_carries_metrics)
created_at	timestamptz	NOT NULL DEFAULT now()

Indexes: - idx_model_deployment_history_effective_at on (effective_at DESC) - idx_model_deployment_history_model_version on (model_version) - idx_model_deployment_history_model_version_effective_at on (model_version, effective_at DESC) — ordered deployment timeline per model version - idx_model_deployment_history_event_type_effective_at on (event_type, effective_at DESC) — audit dashboard filter by event type - idx_model_deployment_history_rolled_back_effective_at on (effective_at DESC) WHERE event_type = 'ROLLED_BACK' — FR-112 hot path for most-recent rollback query

Notes: Rows are immutable after insertion — enforced by trg_model_deployment_history_immutable (ADR-048 Category 1). No updated_at column. The effective_at / created_at split supports audit queries that need to distinguish when the system recorded the event vs. when it took effect.

---

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

Immutability triggers

Table	Trigger	Category
aml.rule_executions	trg_rule_executions_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row()	Cat 1
aml.rule_config_history	trg_rule_config_history_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row()	Cat 1
aml.typology_matches	trg_typology_matches_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row()	Cat 1
aml.regulatory_submissions	trg_regulatory_submissions_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row()	Cat 1
aml.case_events	BEFORE UPDATE OR DELETE, calls aml.reject_mutation() (MOD-016's shared immutability function — same semantics as fn_immutable_row())	Cat 1
aml.behavioural_scores	trg_behavioural_scores_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row()	Cat 1
aml.model_deployment_history	trg_model_deployment_history_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row()	Cat 1
aml.risk_scores_mirror	trg_risk_scores_mirror_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row()	Cat 1

Exception — aml.posting_history_cache: Not covered by an immutability trigger. The scheduled retention sweeper intentionally hard-deletes rows older than the AppConfig-configured retention window (default 90 days). Application code enforces insert-only semantics on the write path; the sweeper is the only permitted deletion path.

CHECK constraints

Table	Column	Constraint
aml.aml_alerts	risk_score	CHECK (risk_score IS NULL OR (risk_score >= 0 AND risk_score <= 100))
aml.typology_matches	confidence	CHECK (confidence IS NULL OR (confidence >= 0 AND confidence <= 100))
aml.model_deployment_history	chk_mdh_previous_version_required	(event_type IN ('ROLLED_BACK','PROMOTED_TO_CHAMPION') AND previous_model_version IS NOT NULL) OR event_type IN ('CHALLENGER_DEPLOYED','RETIRED') — Cat 1: promotions and rollbacks must name the version they replace
aml.model_deployment_history	chk_mdh_created_at_after_effective	created_at >= effective_at - interval '1 minute' — Cat 1: audit row cannot be inserted significantly before the event it records; 1-minute clock-skew allowance
aml.model_deployment_history	chk_mdh_promotion_carries_metrics	event_type != 'PROMOTED_TO_CHAMPION' OR champion_metrics IS NOT NULL — Cat 2: champion promotions must carry real metrics; NULL on non-promotion rows

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

Rule	Reason	Owner
SAR threshold gate	Threshold (default 70) is AppConfig-configurable — same inputs, different outcome at different config values	MOD-018 Lambda
Escalation timer	Depends on wall-clock and aml_cases.created_at; triggers supervisor escalation after configurable inactivity window	MOD-018 Lambda
ML confidence gate	Model version and confidence floor are config-driven	MOD-017 Lambda

---

Cross-domain references

This table	Column	References	Notes
aml.aml_alerts	party_id	SD02 party.parties(party_id)	Alerts are always linked to a known party
aml.aml_cases	party_id	SD02 party.parties(party_id)	Cases are anchored to a party record
aml.regulatory_submissions	subject_party_id	SD02 party.parties(party_id)	SAR subjects must be registered parties
aml.aml_alerts	trigger_transactions	SD04 payments.payments(id)	Stored in JSONB array (not FK-enforced); payments may precede alert creation
aml.posting_history_cache	party_id	SD02 party.parties(party_id)	Denormalised from posting_completed event for query performance
aml.posting_history_cache	account_id	SD01 accounts.accounts(id)	Denormalised from posting_completed event
aml.posting_history_cache	payment_id	SD04 payments.payments(id)	Stored as reference; not FK-enforced (cross-database)
aml.rule_executions	rule_id	aml.rule_definitions(rule_id)	FK-enforced; rule must exist before executions are logged
aml.rule_config_history	rule_id	aml.rule_definitions(rule_id)	FK-enforced; rule must exist before config history can be written
aml.case_events	case_id	aml.aml_cases(id)	FK-enforced; every event must belong to an existing case
aml.case_assignments	case_id	aml.aml_cases(id)	FK-enforced
aml.case_assignments	staff_id	aml.analyst_pool(staff_id)	FK-enforced; assignment target must be a registered analyst
aml.behavioural_scores	party_id	SD02 party.parties(party_id)	Write-back target; every score row belongs to a registered party
aml.risk_scores_mirror	party_id	SD02 party.parties(party_id)	Cross-domain ref; no FK (cross-database)

Consumers of SD03 data: SD06 Snowflake receives all aml schema tables via CDC (MOD-042) for the AUSTRAC/RBNZ AML reporting pipeline (MOD-037) and AML typology dashboard. SD08 back-office (MOD-074) reads aml_cases for customer 360 views visible to compliance staff. The regulatory_submissions table provides evidence for prudential and AML regulatory examinations.