SD02 — KYC Platform data model¶
Database: kyc (Neon Postgres, ap-southeast-2)
Repo: bank-kyc
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: SD02 Customer Identity & KYC Platform
This is the authoritative schema reference for all modules in bank-kyc. Use exact column names and types from this document — do not invent alternatives.
The kyc schema is the primary write domain for MOD-009 (eIDV and document verification), MOD-010 (CDD tier assignment), MOD-011 (periodic review scheduler), MOD-012 (KYC audit trail), MOD-013 (real-time sanctions screener), MOD-014 (list change propagation), and MOD-015 (false positive management). The party.parties table is the canonical identity anchor referenced cross-domain by all system domains via stable UUID. All KYC checks are immutable once written — corrections create new check rows; old rows are never edited.
Schema: kyc¶
Note: The
kyc.customerstable has been replaced by the party model. The canonical customer identity isparty.parties+party.person_profiles+banking.customer_relationships. See schemas below.
kyc.kyc_checks¶
One row per KYC check event (eIDV run, periodic review, re-screen). Immutable — each check attempt creates a new row; the DB-level immutability trigger (trg_kyc_checks_immutable) rejects UPDATE and DELETE unconditionally. Used by MOD-009 (eIDV) and MOD-011 (periodic review) as their primary output record.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| party_id | uuid | NOT NULL REFERENCES party.parties(party_id) | |
| check_type | text | NOT NULL CHECK (check_type IN ('INITIAL_EIDV','PERIODIC_REVIEW','RE_SCREEN','ENHANCED_CDD','DOCUMENT_REVERIFY')) | |
| check_source | text | NOT NULL | Initiating module (MOD-009, MOD-011, etc.) |
| status | text | NOT NULL CHECK (status IN ('PENDING','PASS','FAIL','REFER','EXPIRED')) | |
| provider | text | External provider used (e.g. DVS, DIA, illion) | |
| provider_reference | text | External check reference number | |
| score | numeric(5,2) | CHECK (score IS NULL OR (score >= 0 AND score <= 100)) | Provider confidence score where applicable |
| result_payload | jsonb | NOT NULL DEFAULT '{}' | Structured provider response; no PII outside defined columns |
| policy_refs | jsonb | NOT NULL DEFAULT '[]' | Policies satisfied by this check (e.g. ["AML-003"]) |
| performed_at | timestamptz | NOT NULL DEFAULT now() | |
| performed_by | text | NULL = automated; staff ID for manual checks | |
| expires_at | timestamptz | When this check result becomes stale for review purposes | |
| created_at | timestamptz | NOT NULL DEFAULT now() |
Indexes:
- idx_kyc_checks_party_id_performed on (party_id, performed_at DESC)
- idx_kyc_checks_check_type_status on (check_type, status)
- idx_kyc_checks_expires_at on (expires_at) WHERE expires_at IS NOT NULL
- idx_kyc_checks_provider_reference on (provider_reference) WHERE provider_reference IS NOT NULL
kyc.identity_documents¶
Records of identity documents submitted and verified as part of KYC. Linked to a kyc_check row. Document files are stored in S3; this table holds metadata only.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| party_id | uuid | NOT NULL REFERENCES party.parties(party_id) | |
| kyc_check_id | uuid | NOT NULL REFERENCES kyc.kyc_checks(id) | The check that captured this document |
| document_type | text | NOT NULL CHECK (document_type IN ('PASSPORT','DRIVERS_LICENCE','NATIONAL_ID','BIRTH_CERTIFICATE','UTILITY_BILL','BANK_STATEMENT')) | |
| issuing_country | char(2) | NOT NULL | ISO 3166-1 alpha-2 |
| document_number | text | Encrypted at rest | |
| expiry_date | date | ||
| verified | boolean | NOT NULL DEFAULT false | Set to true after successful document verification |
| verification_method | text | CHECK (verification_method IN ('DVS','DIA','BIOMETRIC','MANUAL','OCR')) | |
| s3_key | text | S3 object key for the document image | |
| retention_delete_at | date | Date document image must be deleted per retention policy | |
| created_at | timestamptz | NOT NULL DEFAULT now() | |
| updated_at | timestamptz | NOT NULL |
Indexes:
- idx_identity_documents_party_id on (party_id)
- idx_identity_documents_kyc_check_id on (kyc_check_id)
- idx_identity_documents_retention on (retention_delete_at) WHERE retention_delete_at IS NOT NULL
kyc.cdd_tier_assignments¶
Audit log of all CDD tier changes. Every time MOD-010 assigns or changes a customer's CDD tier, a new row is appended. DB-level immutability trigger (trg_cdd_tier_assignments_immutable) rejects UPDATE and DELETE unconditionally. The current tier is reflected on banking.customer_relationships.cdd_tier but history is preserved here.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| party_id | uuid | NOT NULL REFERENCES party.parties(party_id) | |
| previous_tier | text | CHECK (previous_tier IN ('SIMPLIFIED','STANDARD','ENHANCED')) | NULL on initial assignment |
| new_tier | text | NOT NULL CHECK (new_tier IN ('SIMPLIFIED','STANDARD','ENHANCED')) | |
| assignment_reason | text | NOT NULL | Human-readable reason for tier change |
| risk_factors | jsonb | NOT NULL DEFAULT '[]' | List of scored risk factor codes that drove this assignment |
| policy_ref | text | NOT NULL DEFAULT 'AML-002' | Policy under which assignment was made |
| assigned_by | text | NOT NULL | MOD-010 or staff ID for manual override |
| assigned_at | timestamptz | NOT NULL DEFAULT now() | |
| created_at | timestamptz | NOT NULL DEFAULT now() |
Indexes:
- idx_cdd_tier_assignments_party_id on (party_id, assigned_at DESC)
- idx_cdd_tier_assignments_new_tier on (new_tier)
kyc.sanctions_lists (MOD-014)¶
Header row for each version of a sanctions list ingested by MOD-014. Append-only at the provider-payload level — kyc.fn_sanctions_lists_append_only trigger permits only status-field mutations (status, activated_at, retired_at, rollback_window_expires_at, ingestion_status); all other columns are immutable, DELETE refused absolutely.
| Column | Type | Description |
|---|---|---|
| id | uuid PK | Row identifier — emitted as list_id in bank.kyc.list_updated events |
| list_source | varchar(32) | OFAC / UN / MFAT / DFAT / REFINITIV / DOW_JONES |
| list_version | varchar(64) | Provider-stamped version identifier |
| previous_version | varchar(64) | Previous active version; NULL on first ingest |
| status | varchar(16) | PENDING / ACTIVE / RETIRED / REJECTED |
| entry_count | integer | Rows persisted in this version |
| added_count | integer | Diff vs previous version |
| removed_count | integer | Diff vs previous version |
| modified_count | integer | Diff vs previous version |
| signature_status | varchar(16) | UNVERIFIED / VALID / INVALID / SKIPPED |
| signature_algorithm | varchar(32) | Provider-supplied algorithm; nullable |
| signature_value | text | Signature bytes / hex; nullable |
| payload_sha256 | varchar(64) | Canonical-payload digest; nullable |
| fetched_at | timestamptz | Wall-clock at provider fetch |
| activated_at | timestamptz | Populated on PENDING → ACTIVE transition; nullable |
| retired_at | timestamptz | Populated on ACTIVE → RETIRED transition; nullable |
| rollback_window_expires_at | timestamptz | retired_at + 48h; nullable |
| ingestion_status | varchar(16) | ACTIVATED / ROLLED_BACK; nullable |
| ingested_by | varchar(32) | Always 'MOD-014' |
| trigger_event_id | varchar(128) | Propagation root for idempotency |
| trace_id | varchar(128) | OpenTelemetry trace identifier |
| created_at | timestamptz | Row insert wall-clock |
Indexes:
- idx_sanctions_lists_source_status on (list_source, status)
- idx_sanctions_lists_created_at on (created_at DESC)
kyc.sanctions_list_entries (MOD-014)¶
Individual entries within a sanctions list version. Fully append-only — both UPDATE and DELETE refused by trigger kyc.fn_sanctions_list_entries_append_only.
| Column | Type | Description |
|---|---|---|
| id | uuid PK | Row identifier |
| list_id | uuid NOT NULL REFERENCES kyc.sanctions_lists(id) | Parent list version |
| entry_id | varchar(128) | Provider-stamped stable identifier (e.g. OFAC SDN-12345) |
| primary_name | text NOT NULL | Canonical name screened against |
| aliases | jsonb NOT NULL | Array of alias / AKA strings |
| date_of_birth | date | YYYY-MM-DD when known; nullable |
| citizenship | varchar(8) | ISO-3166-1 alpha-2/3; nullable |
| raw_payload | jsonb NOT NULL | Full provider payload for audit trail |
| created_at | timestamptz NOT NULL | Row insert wall-clock |
Indexes:
- idx_sanctions_list_entries_list_id on (list_id)
- idx_sanctions_list_entries_entry_id on (entry_id)
kyc.sanctions_results¶
Immutable record of every sanctions screening run, whether triggered at onboarding (MOD-013) or via list change re-screen (MOD-014). DB-level immutability trigger (trg_sanctions_results_immutable) rejects UPDATE and DELETE unconditionally. Matches requiring adjudication link to kyc.false_positive_decisions.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| party_id | uuid | NOT NULL REFERENCES party.parties(party_id) | |
| screen_trigger | text | NOT NULL CHECK (screen_trigger IN ('ONBOARDING','PAYMENT','LIST_UPDATE','PERIODIC','MANUAL')) | What event triggered this screen |
| list_version | text | NOT NULL | Version identifier of the sanctions list screened against |
| lists_screened | jsonb | NOT NULL | Array of list codes (e.g. ["OFAC_SDN","UN_CONSOLIDATED","MFAT","DFAT"]) |
| match_found | boolean | NOT NULL | Whether any candidate match was returned |
| match_count | int | NOT NULL DEFAULT 0 | Number of candidate matches returned |
| match_details | jsonb | NOT NULL DEFAULT '[]' | Structured match payloads from screening provider |
| result_status | text | NOT NULL CHECK (result_status IN ('CLEAR','MATCH_PENDING','CONFIRMED_MATCH','FALSE_POSITIVE')) | |
| screened_at | timestamptz | NOT NULL DEFAULT now() | |
| screened_by | text | NOT NULL | Initiating module |
| payment_id | uuid | If triggered by a payment, FK to payments.payments(id) | |
| created_at | timestamptz | NOT NULL DEFAULT now() |
Indexes:
- idx_sanctions_results_party_id_screened on (party_id, screened_at DESC)
- idx_sanctions_results_result_status on (result_status) WHERE result_status IN ('MATCH_PENDING','CONFIRMED_MATCH')
- idx_sanctions_results_payment_id on (payment_id) WHERE payment_id IS NOT NULL
kyc.periodic_review_schedule¶
Tracks when each customer is next due for KYC periodic review, maintained by MOD-011. The scheduler queries this table to generate review work items.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| party_id | uuid | NOT NULL UNIQUE REFERENCES party.parties(party_id) | One schedule row per party |
| cdd_tier | text | NOT NULL | Tier at time of last review (determines cadence) |
| last_review_at | timestamptz | NULL if never reviewed (recently onboarded) | |
| next_review_due | date | NOT NULL | Calculated from last_review_at + tier cadence |
| review_cadence_days | int | NOT NULL CHECK (review_cadence_days > 0) | 365 for Enhanced, 730 for Standard, 1095 for Simplified (FR-085) |
| status | text | NOT NULL CHECK (status IN ('SCHEDULED','OVERDUE','IN_PROGRESS','COMPLETED','SUPPRESSED')) | |
| last_kyc_check_id | uuid | REFERENCES kyc.kyc_checks(id) | Last completed check |
| created_at | timestamptz | NOT NULL DEFAULT now() | |
| updated_at | timestamptz | NOT NULL |
Indexes:
- idx_periodic_review_next_due on (next_review_due) WHERE status IN ('SCHEDULED','OVERDUE')
- idx_periodic_review_status on (status)
- idx_periodic_review_party_id on (party_id)
kyc.false_positive_decisions¶
Adjudication records for sanctions match false positives, maintained by MOD-015. Each decision is immutable once written — DB-level immutability trigger (trg_false_positive_decisions_immutable) rejects UPDATE and DELETE unconditionally. Confirmed false positives suppress future match alerts for the same entity/list combination.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| sanctions_result_id | uuid | NOT NULL REFERENCES kyc.sanctions_results(id) | The match that was adjudicated |
| party_id | uuid | NOT NULL REFERENCES party.parties(party_id) | |
| match_entry_id | text | NOT NULL | Identifier of the sanctions list entry matched against |
| decision | text | NOT NULL CHECK (decision IN ('FALSE_POSITIVE','CONFIRMED_MATCH','ESCALATED')) | |
| decision_rationale | text | NOT NULL | Required written rationale |
| decided_by | text | NOT NULL | Staff ID of the analyst |
| decided_at | timestamptz | NOT NULL DEFAULT now() | |
| suppress_until | date | If FALSE_POSITIVE, suppress re-alert until this date | |
| policy_ref | text | NOT NULL DEFAULT 'AML-007' | |
| created_at | timestamptz | NOT NULL DEFAULT now() |
Indexes:
- idx_false_positive_decisions_party_id on (party_id)
- idx_false_positive_decisions_sanctions_result_id on (sanctions_result_id)
- idx_false_positive_decisions_suppress on (party_id, match_entry_id, suppress_until) WHERE decision = 'FALSE_POSITIVE'
kyc.fp_review_queue¶
Analyst work queue for sanctions matches requiring adjudication. Populated by MOD-015's EventBridge consumer of bank.kyc.sanctions_match_found (MATCH_PENDING status only). Idempotent on source_event_id. Append-only with a status-mutation-only trigger (NFR-024) — the trigger permits updates to status and updated_at only; INSERT and DELETE are refused. Maintained by MOD-015 V001.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| source_event_id | text | NOT NULL UNIQUE | event_id from sanctions_match_found — idempotency key |
| screening_id | uuid | NOT NULL REFERENCES kyc.sanctions_results(id) | Originating sanctions result |
| party_id | uuid | NULL for counterparty matches; party reference for customer matches | |
| match_entry_id | text | NOT NULL | Sanctions list entry identifier |
| list_source | text | NOT NULL | e.g. OFAC, UN, MFAT, DFAT, REFINITIV, DOW_JONES |
| source_module | text | NOT NULL | Module that produced the match (e.g. MOD-013, MOD-014) |
| status | text | NOT NULL CHECK (status IN ('PENDING','RESOLVED','ESCALATED')) | |
| ingested_at | timestamptz | NOT NULL DEFAULT now() | When the queue row was created |
| updated_at | timestamptz | NOT NULL | Set on status flip |
| created_at | timestamptz | NOT NULL DEFAULT now() |
Indexes:
- idx_fp_review_queue_status on (status, ingested_at) WHERE status = 'PENDING'
- idx_fp_review_queue_party_id on (party_id) WHERE party_id IS NOT NULL
- idx_fp_review_queue_source_event on (source_event_id)
kyc.fp_metrics_monthly¶
Monthly false-positive rate metrics per (year_month, list_source, source_module), computed by MOD-015's monthly cron (FR-104). One row per bucket per run; unique on (year_month, list_source, source_module). Used for compliance trend reporting and list-quality monitoring. Maintained by MOD-015 V001.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| year_month | char(7) | NOT NULL | YYYY-MM format |
| list_source | text | NOT NULL | Sanctions list source (e.g. OFAC, UN) |
| source_module | text | NOT NULL | Module that produced the matches |
| total_matches | int | NOT NULL | Total MATCH_PENDING ingested in the period |
| false_positive_count | int | NOT NULL | Count adjudicated FALSE_POSITIVE |
| confirmed_match_count | int | NOT NULL | Count adjudicated CONFIRMED_MATCH |
| escalated_count | int | NOT NULL | Count escalated (unresolved > 24h at month-end) |
| fp_rate | numeric(8,4) | NOT NULL | false_positive_count / NULLIF(total_matches, 0) |
| computed_at | timestamptz | NOT NULL DEFAULT now() | When the row was written |
Unique index: (year_month, list_source, source_module)
kyc.kyc_audit_events¶
Append-only immutable record of every bank.kyc.* EventBridge event. Populated by MOD-012 via a catch-all rule on the bank-kyc bus. A SHA-256 hash chain links each row to the previous row for the same party_id, making any insertion, modification, or deletion detectable. Supports AML-001, AML-002, GOV-006, and PRI-005 compliance obligations.
Trigger kyc.fn_kyc_audit_events_append_only blanket-denies UPDATE and DELETE on this table. Rows are immutable from INSERT onwards.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| party_id | uuid | NOT NULL | Customer party UUID; 00000000-... system pseudo-party for non-customer events (e.g. list_updated) |
| sequence_no | bigint | NOT NULL | Monotonic per party_id; allocated inside per-party advisory lock |
| event_type | varchar(128) | NOT NULL | Upstream EventBridge detail-type |
| source_module | varchar(32) | NOT NULL | Derived owning module (MOD-009..015 + UNKNOWN for forward-compat events) |
| source_event_id | varchar(128) | NOT NULL | Upstream EventBridge id |
| payload | jsonb | NOT NULL | Upstream detail payload (verbatim, unmodified) |
| canonical_payload | text | NOT NULL | Sorted-key no-whitespace serialisation of payload — hash chain input |
| prev_hash | varchar(64) | NOT NULL | Previous row's this_hash for same party_id; empty string for first row |
| this_hash | varchar(64) | NOT NULL | sha256(prev_hash || canonical_payload || sequence_no || occurred_at_iso) |
| occurred_at | timestamptz | NOT NULL | Upstream EventBridge time |
| recorded_at | timestamptz | NOT NULL DEFAULT now() | Row insert wall-clock |
| trace_id | varchar(128) | Propagated from upstream detail.trace_id |
|
| correlation_id | varchar(128) | Propagated from upstream detail.correlation_id |
Indexes:
- uniq_audit_events_party_sequence UNIQUE on (party_id, sequence_no) — no duplicate sequence_no per party
- uniq_audit_events_source UNIQUE on (source_module, source_event_id) — idempotency constraint
- idx_audit_events_party_occurred on (party_id, occurred_at DESC) — primary query path (FR-091)
- idx_audit_events_type_occurred on (event_type, occurred_at DESC) — event-type filtered queries
Notes: 7-year retention (FR-090) enforced by append-only trigger — no delete path exists at any layer. Events without a customer scope are recorded under 00000000-0000-0000-0000-000000000000 system pseudo-party so the per-party hash chain stays per-key consistent.
Schema: party¶
The party schema is the canonical identity layer for every real-world legal subject the bank knows about. It is owned by SD02 and referenced cross-domain via UUID. The party.* tables are the canonical identity layer. All KYC operational tables (kyc.*) reference party.parties(party_id) directly.
party.addresses¶
Normalised postal and physical address records shared by party.person_profiles and party.organisation_profiles.
| Column | Type | Constraints | Description |
|---|---|---|---|
| address_id | uuid | PK DEFAULT gen_random_uuid() | |
| address_type | text | NOT NULL CHECK (address_type IN ('RESIDENTIAL','REGISTERED','POSTAL','BRANCH')) | |
| line1 | text | NOT NULL | |
| line2 | text | ||
| suburb | text | ||
| city | text | NOT NULL | |
| postcode | text | NOT NULL | |
| country_code | char(2) | NOT NULL | ISO 3166-1 alpha-2 |
| dpid | text | NZ DPID for mail delivery validation | |
| gnaf_pid | text | AU GNAF persistent identifier | |
| validated_at | timestamptz | Populated after address validation pass | |
| created_at | timestamptz | NOT NULL DEFAULT now() | |
| updated_at | timestamptz | NOT NULL |
Indexes:
- idx_addresses_country_city on (country_code, city)
party.parties¶
The master anchor for every real-world subject the bank knows about. One row per distinct legal subject.
| Column | Type | Constraints | Description |
|---|---|---|---|
| party_id | uuid | PK DEFAULT gen_random_uuid() | Cross-domain stable identifier — never reused |
| party_type | text | NOT NULL CHECK (party_type IN ('NATURAL_PERSON','ORGANISATION','ARRANGEMENT')) | AML/KYC legal classification — ARRANGEMENT covers trusts, partnerships, unincorporated bodies |
| party_subtype | text | CHECK (party_subtype IN ('INDIVIDUAL','SOLE_TRADER','LIMITED_COMPANY','TRUST','PROPERTY_CONTEXT')) | EIP operational sub-classification (MOD-096); NULL for all pre-EIP rows |
| legal_name | text | NOT NULL | Registered or full legal name |
| display_name | text | NOT NULL | Short name for UI |
| status | text | NOT NULL CHECK (status IN ('ACTIVE','SUSPENDED','EXITED','DECEASED')) | |
| country_of_formation | char(2) | NOT NULL | ISO 3166-1 alpha-2 |
| created_at | timestamptz | NOT NULL DEFAULT now() | |
| closed_at | timestamptz | Populated on exit / dissolution |
Indexes:
- idx_parties_type_status on (party_type, status)
party_type / party_subtype mapping:
| party_type | party_subtype | Meaning |
|---|---|---|
| NATURAL_PERSON | INDIVIDUAL | Standard individual customer (default; backwards-compatible with pre-EIP rows) |
| NATURAL_PERSON | SOLE_TRADER | Individual acting in a business capacity — same legal person, distinct EIP operating context |
| ORGANISATION | LIMITED_COMPANY | Ltd company — separate legal entity |
| ARRANGEMENT | TRUST | Family trust or similar — separate legal arrangement |
| ARRANGEMENT | PROPERTY_CONTEXT | Virtual operating context for a rental property — no legal standing; created by MOD-094 via MOD-096 |
Notes: party_type drives AML/KYC rule selection (MOD-009 for NATURAL_PERSON; MOD-010 for ORGANISATION/ARRANGEMENT) and must not be expanded for operational purposes. party_subtype is the EIP-specific sub-classification used by MOD-096 for context-switching; it is invisible to the CDD pipeline. Pre-EIP rows have party_subtype = NULL which is treated as INDIVIDUAL by MOD-096. Added by Flyway migration V_MOD096_001 (bank-kyc).
party.party_identifiers¶
Legal and regulatory identifiers per party. A party may have multiple identifiers across jurisdictions.
| Column | Type | Constraints | Description |
|---|---|---|---|
| identifier_id | uuid | PK DEFAULT gen_random_uuid() | |
| party_id | uuid | NOT NULL REFERENCES party.parties(party_id) | |
| identifier_type | text | NOT NULL CHECK (identifier_type IN ('NZBN','NZ_COMPANY_NO','IRD_NO','NZ_CHARITIES_NO','AU_ACN','AU_ABN','AU_TFN','PASSPORT','DRIVER_LICENCE','FOREIGN_REG_NO','TRUST_INTERNAL_REF')) | |
| identifier_value | text | NOT NULL | Encrypted at rest for tax identifiers (IRD_NO, AU_TFN) |
| issuing_jurisdiction | char(2) | NOT NULL | |
| valid_from | date | NOT NULL | |
| valid_to | date | CHECK (valid_to IS NULL OR valid_to > valid_from) | NULL = currently valid |
| verification_status | text | NOT NULL CHECK (verification_status IN ('UNVERIFIED','PENDING','VERIFIED','EXPIRED','REJECTED')) | |
| verified_at | timestamptz | ||
| created_at | timestamptz | NOT NULL DEFAULT now() |
Indexes:
- idx_party_identifiers_party_type on (party_id, identifier_type)
- idx_party_identifiers_value_type on (identifier_type, identifier_value) — for reverse lookup (e.g. find party by IRD number)
party.person_profiles¶
Extended detail for natural persons only. One row per natural person party.
| Column | Type | Constraints | Description |
|---|---|---|---|
| party_id | uuid | PK REFERENCES party.parties(party_id) | Must be a NATURAL_PERSON party |
| date_of_birth | date | NOT NULL | |
| residential_address_id | uuid | REFERENCES party.addresses(address_id) | |
| tax_residency_country | char(2) | NOT NULL | Primary tax residency (ISO 3166-1) |
| additional_tax_residencies | jsonb | [{country, tin}] array for CRS multi-residency reporting | |
| citizenship_country | char(2) | ||
| deceased_flag | boolean | NOT NULL DEFAULT false | |
| deceased_at | date | ||
| updated_at | timestamptz | NOT NULL |
party.organisation_profiles¶
Extended detail for organisations and arrangements only. One row per organisation party.
| Column | Type | Constraints | Description |
|---|---|---|---|
| party_id | uuid | PK REFERENCES party.parties(party_id) | Must be an ORGANISATION or ARRANGEMENT party |
| organisation_type | text | NOT NULL CHECK (organisation_type IN ('LIMITED_COMPANY','CHARITY','INCORPORATED_SOCIETY','PARTNERSHIP','TRUSTEE_COMPANY','OTHER')) | |
| registered_address_id | uuid | REFERENCES party.addresses(address_id) | |
| formation_date | date | ||
| dissolution_date | date | ||
| tax_residency_country | char(2) | NOT NULL | |
| gst_registered | boolean | NOT NULL DEFAULT false | |
| gst_number | text | ||
| updated_at | timestamptz | NOT NULL |
party.party_roles¶
Governance, control, and authority relationships between parties. This is the critical table for AML/CFT beneficial ownership identification and CRS controlling-person determination.
| Column | Type | Constraints | Description |
|---|---|---|---|
| party_role_id | uuid | PK DEFAULT gen_random_uuid() | |
| subject_party_id | uuid | NOT NULL REFERENCES party.parties(party_id) | The person or org acting in the role |
| object_party_id | uuid | NOT NULL REFERENCES party.parties(party_id) | The entity they act in relation to |
| role_type | text | NOT NULL CHECK (role_type IN ('DIRECTOR','SHAREHOLDER','BENEFICIAL_OWNER','AUTHORISED_SIGNATORY','TRUSTEE_OF','PARTNER_OF','TREASURER','SECRETARY','SETTLOR','GUARDIAN','POWER_OF_ATTORNEY','ACCOUNTANT','NOMINEE')) | |
| ownership_pct | numeric(7,4) | For SHAREHOLDER and BENEFICIAL_OWNER — percentage holding. Enables indirect cascade calculations. | |
| start_date | date | NOT NULL | |
| end_date | date | NULL = current | |
| source_of_authority | text | e.g. "Company constitution", "Board resolution", "Trust deed" | |
| evidence_ref | text | Document ID in document vault | |
| created_at | timestamptz | NOT NULL DEFAULT now() | |
| updated_at | timestamptz | NOT NULL |
Indexes:
- idx_party_roles_subject_object on (subject_party_id, object_party_id, role_type)
- idx_party_roles_object_active on (object_party_id) WHERE end_date IS NULL
AML/CFT note: Under the AML/CFT Act, beneficial owners with >25% direct or indirect ownership must be identified and verified. Filter: role_type = 'BENEFICIAL_OWNER' AND ownership_pct >= 25 AND end_date IS NULL.
CRS note: Controlling persons for passive NFEs and investment entities are identified from this table filtered on BENEFICIAL_OWNER, DIRECTOR, and SETTLOR/TRUSTEE_OF roles where crs_controlling_person = true on the corresponding account_party_relationship.
Schema: banking¶
banking.customer_relationships¶
Represents the bank's served relationship with a party. A party can have multiple relationships (e.g., a person holds a personal relationship and a business relationship).
| Column | Type | Constraints | Description |
|---|---|---|---|
| relationship_id | uuid | PK DEFAULT gen_random_uuid() | |
| party_id | uuid | NOT NULL REFERENCES party.parties(party_id) | |
| relationship_type | text | NOT NULL CHECK (relationship_type IN ('PERSONAL','BUSINESS','TRUST','JOINT')) | |
| onboarding_status | text | NOT NULL | |
| kyc_status | text | NOT NULL | |
| aml_risk_rating | text | NOT NULL CHECK (aml_risk_rating IN ('LOW','MEDIUM','HIGH','VERY_HIGH')) | |
| text | NOT NULL | Primary contact email for this relationship | |
| phone | text | Primary contact phone | |
| jurisdiction | char(2) | NOT NULL CHECK (jurisdiction IN ('NZ','AU')) | |
| cdd_tier | text | NOT NULL CHECK (cdd_tier IN ('SIMPLIFIED','STANDARD','ENHANCED')) | Current CDD tier — set by MOD-010 |
| cdd_tier_set_at | timestamptz | NOT NULL | When cdd_tier was last changed |
| edd_completed_at | timestamptz | When EDD was approved and completed. NULL until EDD is finished. Set by back-office EDD workflow. Gated by MOD-010 to determine activation_permitted. | |
| onboarding_application_id | uuid | Links to originating onboarding application | |
| source_of_funds | text | Declared source of funds (Enhanced CDD) | |
| relationship_manager | text | For business relationships | |
| customer_since | date | NOT NULL | |
| last_review_date | date | ||
| exit_date | date | ||
| exit_reason | text | ||
| created_at | timestamptz | NOT NULL DEFAULT now() | |
| updated_at | timestamptz | NOT NULL |
Unique constraint:
- uq_customer_relationships_party_type on (party_id, relationship_type) — a party holds at most one relationship of each type with the bank
Indexes:
- idx_customer_relationships_party_id on (party_id)
- idx_customer_relationships_type_status on (relationship_type, kyc_status)
- idx_customer_relationships_cdd_tier on (cdd_tier)
- idx_customer_relationships_jurisdiction on (jurisdiction)
Schema: regulatory¶
regulatory.party_regulatory_profiles¶
Regulatory classification per party, per jurisdiction, per period. Stored separately from the party record because classifications change over time and differ by jurisdiction.
| Column | Type | Constraints | Description |
|---|---|---|---|
| profile_id | uuid | PK DEFAULT gen_random_uuid() | |
| party_id | uuid | NOT NULL REFERENCES party.parties(party_id) | |
| jurisdiction | char(2) | NOT NULL | |
| dcs_eligible | boolean | NOT NULL | Eligible for DCS depositor compensation |
| dcs_depositor_category | text | INDIVIDUAL, SMALL_BUSINESS, GOVERNMENT, EXCLUDED | |
| crs_entity_classification | text | NULL for natural persons; ACTIVE_NFE, PASSIVE_NFE, FINANCIAL_INSTITUTION, etc. | |
| fatca_entity_classification | text | SPECIFIED_US_PERSON, NPFFI, PFFI, etc. | |
| aml_customer_type | text | INDIVIDUAL, CORPORATE, TRUST, etc. | |
| pep_flag | boolean | NOT NULL DEFAULT false | Politically Exposed Person |
| pep_review_date | date | ||
| government_agency_flag | boolean | NOT NULL DEFAULT false | |
| effective_from | date | NOT NULL | |
| effective_to | date | CHECK (effective_to IS NULL OR effective_to > effective_from) | NULL = currently effective |
| created_at | timestamptz | NOT NULL DEFAULT now() |
Indexes:
- idx_party_regulatory_party_jurisdiction on (party_id, jurisdiction, effective_to) WHERE effective_to IS NULL
- idx_party_regulatory_pep on (party_id) WHERE pep_flag = true AND effective_to IS NULL
Published cross-domain contracts¶
Per AP-010 pattern 1, SD02 publishes scoped views to satisfy synchronous read needs from other system domains. The owning domain controls the column set; the consumer reads via the appropriate *_readonly Secrets Manager credential. Each view name is published via SSM so consumers can wire fail-closed when the contract has not yet shipped.
banking.customer_relationships_identity_readable¶
Read-only view exposing identity-verification status to credit-domain consumers without granting access to the full banking.customer_relationships row (which carries PII the consumer has no need for). Scoped to natural persons only — legal-entity identity verification follows a separate contract (TBD when the first organisation-side consumer lands).
Added by: MOD-009 V009 (V009__create_identity_readable_view.sql)
Granted role: bank_kyc_readonly — cross-domain reads use the bank-neon/{env}/bank_kyc/readonly Secrets Manager credential (BankCreditRole already has secretsmanager:GetSecretValue on bank-neon/{env}/bank_kyc/* per MOD-104)
SSM contract: /bank/{env}/kyc/views/identity-readable/name → banking.customer_relationships_identity_readable
| Column | Type | Source | Notes |
|---|---|---|---|
party_id |
uuid | banking.customer_relationships.party_id |
Cross-domain stable identifier |
kyc_status |
text | banking.customer_relationships.kyc_status |
VERIFIED | PENDING | PENDING_EDD | FAILED | EXPIRED |
last_verified_at |
timestamptz | banking.customer_relationships.last_verified_at |
Nullable — null when no PASS check on file |
cdd_tier |
text | banking.customer_relationships.cdd_tier |
SIMPLIFIED | STANDARD | ENHANCED. Included to avoid a second bank-kyc round-trip when MOD-029 gates credit decisioning on KYC tier. |
Filter: WHERE party_type = 'NATURAL_PERSON'
Consumer modules:
- MOD-128 (credit bureau enquiry gate, SD05) — FR-577 PRI-001 GATE. Ships with a fail-closed adapter; live mode wires up at cold start when the SSM path is populated.
- MOD-029 (pre-approval engine, SD05) — pending; will gate credit decisioning on cdd_tier via the same view.
kyc.acceptance_decisions (MOD-153 V001)¶
Formal acceptance engine decision record per (party, product). Append-only per AML-012 + ADR-048. Each evaluation run appends a new row; the latest decision (by decision_at) is the authoritative status for activation gating and regulatory examination.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| party_id | uuid | NOT NULL | Cross-domain ref to party.parties(party_id) (soft FK — UUID convention) |
| product_id | text | NOT NULL | Product under evaluation |
| decision | text | NOT NULL CHECK (decision IN ('ACCEPT','DECLINE','REFER','HOLD_FOR_EDD')) | FR-710 outcome enum |
| decision_at | timestamptz | NOT NULL DEFAULT now() | |
| methodology_version | text | NOT NULL | Rule-set release identifier e.g. "v1.0.0" — AML-012 CALC requirement |
| input_snapshot | jsonb | NOT NULL | Full rule-input capture at evaluation time — AML-012 audit requirement |
| applied_rules | text[] | NOT NULL | Ordered list of rule names evaluated |
| triggered_rules | text[] | NOT NULL | Subset of applied_rules that contributed to the outcome |
| reason_codes | text[] | NOT NULL | Machine-readable reason codes e.g. ["CDD_TIER_INSUFFICIENT","PEP_EDD_INCOMPLETE"] |
| decision_officer | text | NULL = automated decision; non-null = staff override | |
| product_category | text | NOT NULL CHECK (product_category IN ('CREDIT','DEPOSIT','PAYMENT','OTHER')) | Used by MOD-063 FR-714 adverse action routing |
| jurisdiction | char(2) | NOT NULL CHECK (jurisdiction IN ('NZ','AU')) | |
| re_evaluation_trigger | text | Cause of re-evaluation e.g. "CDD_TIER_CHANGE", "SANCTIONS_CHANGE" — null on initial evaluation |
|
| trace_id | uuid | ||
| idempotency_key | text | NOT NULL UNIQUE | (party_id, product_id, triggering_event_id) — replay-safe |
| created_at | timestamptz | NOT NULL DEFAULT now() |
Indexes:
- UNIQUE (party_id, product_id, decision_at) — latest-decision lookup
- idx_acceptance_decisions_party_product on (party_id, product_id, decision_at DESC) — activation gate query
- idx_acceptance_decisions_pending_edd on (party_id) WHERE decision = 'HOLD_FOR_EDD' AND re_evaluation_trigger IS NULL — PEP/EDD queue scan
DB-enforced invariants (ADR-048)¶
The following constraints and triggers are enforced at the database layer per ADR-048. All are implemented in numbered Flyway migrations (V0NN__describe.sql). Lambda validation is not removed — it provides the user-facing error path. The DB layer is the hard floor that no bypass path can circumvent.
Immutability triggers¶
| Table | Trigger | Behaviour |
|---|---|---|
kyc.kyc_checks |
trg_kyc_checks_immutable |
BEFORE UPDATE OR DELETE: raises exception unconditionally |
kyc.sanctions_results |
trg_sanctions_results_immutable |
Same pattern |
kyc.cdd_tier_assignments |
trg_cdd_tier_assignments_immutable |
Same pattern |
kyc.false_positive_decisions |
trg_false_positive_decisions_immutable |
Same pattern |
kyc.kyc_audit_events |
fn_kyc_audit_events_append_only |
Already deployed by MOD-012 ✅ |
kyc.fp_review_queue |
(status-mutation-only trigger) | Permits UPDATE on status and updated_at only; rejects INSERT and DELETE. Already deployed by MOD-015 ✅ |
kyc.sanctions_lists |
kyc.fn_sanctions_lists_append_only |
Permits only status-field mutations (status, activated_at, retired_at, rollback_window_expires_at, ingestion_status); all other columns immutable; DELETE refused absolutely. MOD-014. |
kyc.sanctions_list_entries |
kyc.fn_sanctions_list_entries_append_only |
Both UPDATE and DELETE refused unconditionally. MOD-014. |
kyc.acceptance_decisions |
trg_acceptance_decisions_immutable |
BEFORE UPDATE OR DELETE OR TRUNCATE: raises exception unconditionally (ADR-048 Cat 1). MOD-153 V001. |
All four new immutability triggers share the same function body (fn_immutable_row()) per ADR-048 — create the function once in the schema setup migration and reference it from each table trigger.
CHECK constraints¶
| Table | Column | Constraint |
|---|---|---|
kyc.kyc_checks |
check_type |
CHECK (check_type IN ('INITIAL_EIDV','PERIODIC_REVIEW','RE_SCREEN','ENHANCED_CDD','DOCUMENT_REVERIFY')) |
kyc.kyc_checks |
status |
CHECK (status IN ('PENDING','PASS','FAIL','REFER','EXPIRED')) |
kyc.kyc_checks |
score |
CHECK (score IS NULL OR (score >= 0 AND score <= 100)) |
kyc.identity_documents |
document_type |
CHECK (document_type IN ('PASSPORT','DRIVERS_LICENCE','NATIONAL_ID','BIRTH_CERTIFICATE','UTILITY_BILL','BANK_STATEMENT')) |
kyc.cdd_tier_assignments |
new_tier |
CHECK (new_tier IN ('SIMPLIFIED','STANDARD','ENHANCED')) |
kyc.sanctions_results |
result_status |
CHECK (result_status IN ('CLEAR','MATCH_PENDING','CONFIRMED_MATCH','FALSE_POSITIVE')) |
kyc.false_positive_decisions |
decision |
CHECK (decision IN ('FALSE_POSITIVE','CONFIRMED_MATCH','ESCALATED')) |
kyc.periodic_review_schedule |
status |
CHECK (status IN ('SCHEDULED','OVERDUE','IN_PROGRESS','COMPLETED','SUPPRESSED')) |
kyc.periodic_review_schedule |
review_cadence_days |
CHECK (review_cadence_days > 0) |
kyc.fp_review_queue |
status |
CHECK (status IN ('PENDING','RESOLVED','ESCALATED')) |
kyc.sanctions_lists |
status |
CHECK (status IN ('PENDING','ACTIVE','RETIRED','REJECTED')) |
kyc.sanctions_lists |
signature_status |
CHECK (signature_status IN ('UNVERIFIED','VALID','INVALID','SKIPPED')) |
kyc.sanctions_lists |
ingestion_status |
CHECK (ingestion_status IN ('ACTIVATED','ROLLED_BACK')) |
party.parties |
party_type |
CHECK (party_type IN ('NATURAL_PERSON','ORGANISATION','ARRANGEMENT')) |
party.parties |
party_subtype |
CHECK (party_subtype IN ('INDIVIDUAL','SOLE_TRADER','LIMITED_COMPANY','TRUST','PROPERTY_CONTEXT')) — nullable; NULL = pre-EIP row |
party.parties |
status |
CHECK (status IN ('ACTIVE','SUSPENDED','EXITED','DECEASED')) |
party.party_identifiers |
valid_to |
CHECK (valid_to IS NULL OR valid_to > valid_from) |
regulatory.party_regulatory_profiles |
effective_to |
CHECK (effective_to IS NULL OR effective_to > effective_from) |
banking.customer_relationships |
jurisdiction |
CHECK (jurisdiction IN ('NZ','AU')) |
banking.customer_relationships |
cdd_tier |
CHECK (cdd_tier IN ('SIMPLIFIED','STANDARD','ENHANCED')) |
kyc.acceptance_decisions |
decision |
CHECK (decision IN ('ACCEPT','DECLINE','REFER','HOLD_FOR_EDD')) — FR-710 outcome enum |
kyc.acceptance_decisions |
product_category |
CHECK (product_category IN ('CREDIT','DEPOSIT','PAYMENT','OTHER')) |
kyc.acceptance_decisions |
jurisdiction |
CHECK (jurisdiction IN ('NZ','AU')) |
kyc.acceptance_decisions |
idempotency_key |
UNIQUE NOT NULL — replay-safe on event replay |
Testing requirement¶
Every new trigger and constraint requires a negative integration test: attempt the violating operation inside a test transaction, assert the expected Postgres exception, ROLLBACK. Tests live in tests/integration/ for the owning module.
Cross-domain references¶
| This table | Column | References | Notes |
|---|---|---|---|
| kyc.kyc_checks | party_id | party.parties(party_id) |
KYC checks are anchored to the party |
| kyc.identity_documents | party_id | party.parties(party_id) |
|
| kyc.cdd_tier_assignments | party_id | party.parties(party_id) |
|
| kyc.sanctions_results | party_id | party.parties(party_id) |
|
| kyc.periodic_review_schedule | party_id | party.parties(party_id) |
|
| kyc.false_positive_decisions | party_id | party.parties(party_id) |
|
| kyc.sanctions_results | payment_id | SD04 payments.payments(id) |
Payment-triggered sanctions screens reference the originating payment |
| kyc.sanctions_list_entries | list_id | kyc.sanctions_lists(id) |
Parent list version for each entry |
| kyc.acceptance_decisions | party_id | party.parties(party_id) |
Soft FK — cross-domain UUID convention; no Postgres FK |
| party.parties | party_id | SD01 accounts.account_party_relationships(party_id) |
Cross-domain UUID convention; not a Postgres FK |
| party.parties | party_id | SD01 contexts.operating_contexts(primary_party_id) |
Cross-domain UUID convention |
| party.parties | party_id | SD01 assets.asset_party_relationships(party_id) |
Cross-domain UUID convention |
| party.parties | party_id | SD03 aml.aml_alerts(party_id) |
Cross-domain UUID convention |
| party.parties | party_id | SD03 aml.aml_cases(party_id) |
Cross-domain UUID convention |
| party.parties | party_id | SD04 payments.payments(party_id) |
Cross-domain UUID convention |
| party.parties | party_id | SD05 credit.credit_applications(party_id) |
Cross-domain UUID convention |
| party.parties | party_id | SD08 access.user_identities(person_party_id) |
Cross-domain UUID convention |
| party.parties | party_id | SD08 app.consents(party_id) |
Cross-domain UUID convention |
| party.parties | party_id | SD08 app.cases(party_id) |
Cross-domain UUID convention |
Consumers of SD02 data: party.parties.party_id is the canonical cross-domain customer identifier. SD01 MOD-007 gates account activation on banking.customer_relationships.kyc_status = 'VERIFIED' (AML-002 gate) and — via a v2 follow-on — will also gate on kyc.acceptance_decisions latest outcome = ACCEPT (AML-011 gate, MOD-153). SD03 AML monitoring enriches alerts with cdd_tier and pep_flag from banking.customer_relationships and regulatory.party_regulatory_profiles. SD06 Snowflake risk models consume customer risk factors via CDC. The kyc.*, party.*, banking.*, and regulatory.* tables are all replicated to Snowflake via MOD-042.