SD07 — Data Platform data model¶
Database: platform (Neon Postgres, ap-southeast-2) + BANK_{ENV}_PLATFORM (Snowflake — REPORTS schema, MOD-099)
Repo: bank-platform
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 pipeline), ADR-036 (decision result publication), ADR-029 (EventBridge governance; superseded by ADR-051 — see ADR-051 for current EventBridge bus naming convention), ADR-030 (secrets management), ADR-031 (observability), ADR-048 (database-enforced invariants)
Schema owner: SD07 Data Platform & Governance Infrastructure
This is the authoritative schema reference for platform-level tables in bank-platform. Use exact column names and types from this document — do not invent alternatives.
SD07 concerns are primarily infrastructure, not business-domain tables. The Postgres tables here support event delivery, JWT RBAC, secrets references, and the decision inbox — the operational plumbing that other domains depend on. Many SD07 capabilities (CDC pipeline, EventBridge routing, PAM, observability) do not have primary Postgres tables because they write to S3, CloudWatch, or Snowflake directly.
The decision_inbox, decision_state, and integration schemas are documented in full in Neon and Snowflake physical storage. This document covers the remaining platform tables and provides cross-references to avoid duplication.
Modules writing to these schemas: MOD-042 (CDC pipeline), MOD-043 (EventBridge governance), MOD-044 (JWT RBAC), MOD-045 (secrets management), MOD-046 (PAM), MOD-047 (agent action logger), MOD-048 (system decision log), MOD-062 (workflow orchestration), MOD-063 (notification orchestration), MOD-075 (internal API gateway), MOD-076 (observability), MOD-079 (Snowflake decision publication).
Schema: decision_inbox (reference — defined elsewhere)¶
The decision_inbox.decision_result_inbox, decision_state.customer_decision_state, decision_state.application_decision_state, and integration.decision_delivery_log tables are fully defined in Neon and Snowflake physical storage. Do not redefine them here. bank-platform owns these schemas and manages migrations for them.
Schema: platform¶
platform.event_delivery_log¶
Tracks domain event publication through EventBridge / Kafka, managed by MOD-043. One row per event publication attempt. Used to detect duplicate events, replay failures, and schema validation errors. This table is the observability store for the event bus — it is not the event store itself (which is S3-backed).
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| event_id | text | NOT NULL UNIQUE | Globally unique event identifier (set by the producing service) |
| event_type | text | NOT NULL | e.g. kyc.customer.verified, payments.payment.settled |
| event_schema_version | text | NOT NULL | Semantic version of the event schema |
| source_service | text | NOT NULL | Producing service (e.g. bank-kyc, bank-payments) |
| source_module | text | NOT NULL | Producing module (e.g. MOD-009, MOD-022) |
| entity_type | text | NOT NULL | Entity the event is about (CUSTOMER, PAYMENT, ACCOUNT, etc.) |
| entity_id | text | NOT NULL | Entity identifier |
| bus_target | text | NOT NULL CHECK (bus_target IN ('EVENTBRIDGE','KAFKA','INTERNAL')) | |
| delivery_status | text | NOT NULL CHECK (delivery_status IN ('PUBLISHED','FAILED','RETRYING','DEAD_LETTER')) | |
| published_at | timestamptz | When successfully published to the bus | |
| failed_at | timestamptz | ||
| retry_count | int | NOT NULL DEFAULT 0 | |
| failure_reason | text | ||
| payload_checksum | text | SHA256 of the event payload | |
| created_at | timestamptz | NOT NULL DEFAULT now() | |
| updated_at | timestamptz | NOT NULL |
Indexes:
- idx_event_delivery_log_event_id on (event_id)
- idx_event_delivery_log_entity on (entity_type, entity_id)
- idx_event_delivery_log_status on (delivery_status) WHERE delivery_status IN ('FAILED','RETRYING','DEAD_LETTER')
- idx_event_delivery_log_created_at on (created_at DESC)
Notes: The event_id uniqueness constraint supports idempotent re-publication. Consumers should treat duplicate event_id values as safe to ignore.
platform.jwt_key_register¶
Register of active and rotated JWT signing keys used by MOD-044 for RBAC token issuance. Keys are referenced by kid (key ID) in JWT headers. Key material is stored in AWS Secrets Manager (not here); this table holds only the metadata.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| kid | text | NOT NULL UNIQUE | Key identifier — embedded in JWT header |
| algorithm | text | NOT NULL CHECK (algorithm IN ('RS256','RS384','RS512','ES256','ES384')) | |
| key_type | text | NOT NULL CHECK (key_type IN ('SIGNING','VERIFICATION','ROTATION')) | |
| secrets_ref | text | NOT NULL | AWS Secrets Manager ARN for the key material |
| status | text | NOT NULL CHECK (status IN ('ACTIVE','PENDING_ROTATION','ROTATED','REVOKED')) | |
| issued_at | timestamptz | NOT NULL DEFAULT now() | |
| rotate_after | timestamptz | Scheduled rotation date | |
| revoked_at | timestamptz | ||
| revocation_reason | text | ||
| created_by | text | NOT NULL | Staff ID or automation that registered this key |
| created_at | timestamptz | NOT NULL DEFAULT now() | |
| updated_at | timestamptz | NOT NULL |
Indexes:
- idx_jwt_key_register_kid on (kid)
- idx_jwt_key_register_status_active on (status) WHERE status = 'ACTIVE'
- idx_jwt_key_register_rotate_after on (rotate_after) WHERE status = 'ACTIVE'
platform.secrets_refs¶
Catalogue of secret references managed by MOD-045. Stores the metadata and location of secrets in AWS Secrets Manager — never the secret values themselves. Used for auditing secret access patterns and enforcing rotation schedules.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| secret_name | text | NOT NULL UNIQUE | Logical name (e.g. bank-kyc/dvs-api-key) |
| secret_arn | text | NOT NULL | AWS Secrets Manager ARN |
| secret_type | text | NOT NULL CHECK (secret_type IN ('API_KEY','DATABASE_CREDENTIAL','JWT_KEY','ENCRYPTION_KEY','OAUTH_SECRET','WEBHOOK_SECRET')) | |
| owner_service | text | NOT NULL | Service that owns this secret |
| owner_module | text | Module responsible | |
| environment | text | NOT NULL CHECK (environment IN ('PRODUCTION','STAGING','DEVELOPMENT')) | |
| rotation_enabled | boolean | NOT NULL DEFAULT true | Whether automatic rotation is configured |
| rotation_days | int | Rotation interval in days (NULL if manual) | |
| last_rotated_at | timestamptz | ||
| next_rotation_due | timestamptz | ||
| last_accessed_at | timestamptz | Updated on each access (from CloudTrail hook) | |
| access_count_30d | int | NOT NULL DEFAULT 0 | Rolling 30-day access count |
| status | text | NOT NULL CHECK (status IN ('ACTIVE','ROTATION_PENDING','DEPRECATED','REVOKED')) | |
| created_at | timestamptz | NOT NULL DEFAULT now() | |
| updated_at | timestamptz | NOT NULL |
Indexes:
- idx_secrets_refs_owner_service on (owner_service)
- idx_secrets_refs_rotation_due on (next_rotation_due) WHERE status = 'ACTIVE' AND rotation_enabled = true
- idx_secrets_refs_status on (status) WHERE status IN ('ACTIVE','ROTATION_PENDING')
platform.agent_action_log¶
Immutable log of all staff interactions with customer data, written by MOD-047. Required for GOV-006 and DT-001 compliance. Append-only — rows are never updated or deleted. Retained for regulatory audit period.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| agent_id | text | NOT NULL | Staff identifier |
| agent_role | text | NOT NULL | Role at time of access |
| session_id | text | NOT NULL | Staff session identifier |
| action_type | text | NOT NULL CHECK (action_type IN ('VIEW','EXPORT','UPDATE','OVERRIDE','SEARCH','DOWNLOAD','BULK_ACTION')) | |
| entity_type | text | NOT NULL | e.g. CUSTOMER, ACCOUNT, PAYMENT, CASE |
| entity_id | text | NOT NULL | |
| customer_id | text | If action is on a customer-related entity | |
| action_detail | jsonb | NOT NULL DEFAULT '{}' | Structured details of the action performed |
| justification | text | Required for OVERRIDE and BULK_ACTION types | |
| ip_address | text | Staff source IP | |
| user_agent | text | ||
| acted_at | timestamptz | NOT NULL DEFAULT now() | |
| created_at | timestamptz | NOT NULL DEFAULT now() |
Indexes:
- idx_agent_action_log_agent_id on (agent_id, acted_at DESC)
- idx_agent_action_log_entity on (entity_type, entity_id)
- idx_agent_action_log_customer_id on (customer_id) WHERE customer_id IS NOT NULL
- idx_agent_action_log_acted_at on (acted_at DESC)
Notes: No updated_at column — rows are immutable after insertion. Partitioned by acted_at month in Neon for query performance on large audit exports. Immutability is enforced by trg_agent_action_log_immutable (ADR-048 Category 1).
platform.system_decision_log¶
Immutable log of all automated system decisions, written by MOD-048. Every policy-gating decision (GATE mode), automated action (AUTO mode), and calculation result (CALC mode) that affects a customer or transaction must produce a row here. This is the system-side counterpart to agent_action_log.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PK DEFAULT gen_random_uuid() | |
| decision_id | text | NOT NULL UNIQUE | Idempotency key for the decision event |
| source_module | text | NOT NULL | Module that produced the decision |
| decision_type | text | NOT NULL CHECK (decision_type IN ('GATE','AUTO','CALC','ALERT','LOG')) | Satisfaction mode |
| entity_type | text | NOT NULL | |
| entity_id | text | NOT NULL | |
| customer_id | text | ||
| policy_refs | jsonb | NOT NULL DEFAULT '[]' | Policies this decision satisfies |
| decision_outcome | text | NOT NULL CHECK (decision_outcome IN ('PASS','FAIL','REFER','HOLD','SKIP','CALC_RESULT')) | |
| decision_payload | jsonb | NOT NULL DEFAULT '{}' | Structured outcome details; must not contain PII beyond entity references |
| model_version | text | If ML model was involved | |
| decided_at | timestamptz | NOT NULL DEFAULT now() | |
| created_at | timestamptz | NOT NULL DEFAULT now() |
Indexes:
- idx_system_decision_log_entity on (entity_type, entity_id, decided_at DESC)
- idx_system_decision_log_source_module on (source_module, decided_at DESC)
- idx_system_decision_log_customer_id on (customer_id) WHERE customer_id IS NOT NULL
- idx_system_decision_log_policy on USING GIN (policy_refs)
Notes: No updated_at column — immutable. The decision_id uniqueness constraint prevents duplicate log entries for the same decision event (important for idempotent event replay). Immutability is enforced by trg_system_decision_log_immutable (ADR-048 Category 1).
platform.enrichment_merchants (MOD-087)¶
Merchant identity cache. Maps raw acquirer merchant names to canonical merchant identity (name, MCC, logo, geolocation). Populated by the MOD-087 enrichment Lambda; read by the same Lambda on every enrichment to avoid repeat resolution. Owned by MOD-087 V001. Mutable — records are updated as enrichment quality improves.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | PRIMARY KEY DEFAULT gen_random_uuid() | |
| raw_name | text | NOT NULL UNIQUE | Raw acquirer merchant name from bank.core.posting_completed; dedup and cache lookup key |
| canonical_name | text | NOT NULL | Normalised merchant name |
| mcc | char(4) | ISO 18245 Merchant Category Code; NULL when unknown | |
| logo_url | text | CDN URL for merchant logo; NULL in v1 (requires external API) | |
| lat | numeric(9,6) | Merchant latitude; NULL in v1 (FR-763 "where available") | |
| lng | numeric(9,6) | Merchant longitude; NULL in v1 | |
| source | text | NOT NULL CHECK (source IN ('DICTIONARY','MCC_INFERENCE','MANUAL')) | How this record was resolved |
| created_at | timestamptz | NOT NULL DEFAULT current_timestamp | |
| updated_at | timestamptz | NOT NULL DEFAULT current_timestamp |
Indexes:
- UNIQUE (raw_name) — cache lookup by raw acquirer name
- idx_enrichment_merchants_mcc on (mcc) WHERE mcc IS NOT NULL — MCC-based analytics
Notes: Mutable cache — no immutability trigger. Records are upserted (INSERT ... ON CONFLICT (raw_name) DO UPDATE) on enrichment. No DELETE grant — stale records are acceptable; the cache is a read-performance optimisation, not a source of truth. logo_url, lat, lng are null in v1 and populated in v2 once the external enrichment API is wired.
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-platform.
Immutability triggers¶
| Table | Trigger | Category |
|---|---|---|
platform.agent_action_log |
trg_agent_action_log_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row() |
Cat 1 |
platform.system_decision_log |
trg_system_decision_log_immutable — BEFORE UPDATE OR DELETE, calls fn_immutable_row() |
Cat 1 |
Other tables (platform.event_delivery_log, platform.jwt_key_register, platform.secrets_refs) are mutable by design — delivery status, key rotation state, and secret lifecycle are updated by the owning modules.
CHECK constraints¶
| Table | Column | Constraint |
|---|---|---|
platform.event_delivery_log |
retry_count |
CHECK (retry_count >= 0) |
Not DB-enforced (Category 3 — cross-service or config-driven)¶
Event retry policy, JWT key rotation scheduling, and secret rotation thresholds all depend on AppConfig or external timing. These live in their respective Lambda modules.
Cross-domain references¶
SD07 tables do not carry FK references to other domain schemas by design — the platform layer must not create coupling into business domain tables. References are by value (text entity IDs), not by FK constraint.
| Table | Column | Logical reference | Notes |
|---|---|---|---|
| platform.event_delivery_log | entity_id | Any domain entity | Text reference; no FK enforced |
| platform.agent_action_log | party_id | SD02 party.parties.party_id |
Text reference for query joins |
| platform.system_decision_log | party_id | SD02 party.parties.party_id |
Text reference for query joins |
Consumers of SD07 data: SD06 Snowflake receives platform.agent_action_log and platform.system_decision_log via CDC (MOD-042) for governance analytics and regulatory audit trails. The decision_inbox schema is consumed by all operational domains (SD01–SD05, SD08) via MOD-079. The event_delivery_log supports operational support tooling in SD08 back-office.
Snowflake schema: REPORTS (BANK_{ENV}_PLATFORM)¶
Database: BANK_{ENV}_PLATFORM (Snowflake, ap-southeast-2)
Module: MOD-099
Source data: BANK_{ENV}_RISK.METERING.* (cross-database read — owned by MOD-098, SD06)
ADR-048 exemption: Snowflake DDL only — no Neon Postgres tables. ADR-048 does not apply.
Read-only cost-reporting data product. All objects here are Snowflake views or Dynamic Tables on top of MOD-098's metering data. No raw data is stored in this schema — it is a reporting projection of BANK_{ENV}_RISK.METERING. Downstream consumers (Snowsight dashboards, Streamlit applications) query this schema directly without needing access to BANK_{ENV}_RISK.
The cross-database SELECT grant on BANK_{ENV}_RISK.METERING.* is applied by MOD-102's setup runner and held by the REPORTS deploying service user only.
REPORTS.V_AWS_COST_DAILY¶
Type: View on BANK_{ENV}_RISK.metering.aws_cost_daily
Daily AWS spend by service and environment, sourced from AWS Cost Explorer via MOD-098. One row per (cost_date, service_name, environment).
| Column | Type | Description |
|---|---|---|
| cost_date | DATE | Calendar day |
| service_name | VARCHAR | AWS service name (e.g. Lambda, S3, EventBridge, RDS) |
| environment | VARCHAR | prod / uat / dev |
| tenant_id | VARCHAR | Tenant tag where present; NULL for shared/unattributed resources |
| cost_usd | NUMBER(14,4) | Total cost in USD for the day |
| usage_quantity | NUMBER(18,4) | Service-reported usage quantity |
| usage_unit | VARCHAR | Unit of measure (GB-month, requests, hours, etc.) |
REPORTS.V_SNOWFLAKE_CREDITS_DAILY¶
Type: View on BANK_{ENV}_RISK.metering.snowflake_credit_daily
Daily Snowflake credit consumption by warehouse, sourced from SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY via MOD-098.
| Column | Type | Description |
|---|---|---|
| usage_date | DATE | Calendar day |
| warehouse_name | VARCHAR | Snowflake warehouse (ETL_WH, ANALYTICS_WH, RISK_WH, DECISIONS_WH, METERING_WH) |
| credits_used | NUMBER(14,6) | Total credits consumed |
| credits_used_usd | NUMBER(14,4) | Estimated USD cost at Snowflake credit price |
| primary_database | VARCHAR | Database context where attributable |
REPORTS.V_INFRASTRUCTURE_COST_MONTHLY¶
Type: Snowflake Dynamic Table | Refresh: FULL | Target lag: 24 hours | Cluster key: (period_year, period_month)
Combined monthly infrastructure cost totals across all AWS services and Snowflake credits. Primary view for month-on-month trend analysis and finance reporting.
| Column | Type | Description |
|---|---|---|
| period_year | NUMBER(4,0) | Calendar year |
| period_month | NUMBER(2,0) | Calendar month (1–12) |
| environment | VARCHAR | prod / uat / dev |
| aws_cost_usd | NUMBER(14,4) | Total AWS spend for the month |
| snowflake_credits_usd | NUMBER(14,4) | Total Snowflake credit cost for the month |
| total_infrastructure_usd | NUMBER(14,4) | aws_cost_usd + snowflake_credits_usd |
| refreshed_at | TIMESTAMP_LTZ | When this Dynamic Table was last refreshed |
REPORTS.V_COST_TREND_90D¶
Type: View on V_INFRASTRUCTURE_COST_MONTHLY and V_AWS_COST_DAILY
Trailing 90 calendar days of daily infrastructure costs, shaped for Streamlit charting.
| Column | Type | Description |
|---|---|---|
| cost_date | DATE | Calendar day |
| environment | VARCHAR | prod / uat / dev |
| aws_cost_usd | NUMBER(14,4) | AWS spend for the day |
| snowflake_credits_usd | NUMBER(14,4) | Snowflake credit cost for the day |
| total_usd | NUMBER(14,4) | Combined daily total |
REPORTS.V_TENANT_COST_SUMMARY¶
Type: View on BANK_{ENV}_RISK.metering.billing_period_summary and metering.unit_economics
Per-tenant attributed cost summary for current and trailing billing periods. Shaped to provide invoice-ready data for a future billing module without containing any invoicing logic. Consumed by finance for gross margin review.
| Column | Type | Description |
|---|---|---|
| billing_period_start | DATE | First day of the billing period |
| billing_period_end | DATE | Last day of the billing period (last day of month) |
| tenant_id | VARCHAR | Licensee tenant identifier |
| module_id | VARCHAR | Module incurring cost |
| customer_levy_usd | NUMBER(14,4) | Per-customer levy component (from MOD-098 rate card) |
| facility_fee_usd | NUMBER(14,4) | Fixed facility fee component |
| variable_cost_usd | NUMBER(14,4) | Variable consumption cost component |
| infra_passthrough_usd | NUMBER(14,4) | Direct infrastructure cost passthrough |
| total_attributed_usd | NUMBER(14,4) | Sum of all cost components |
| gross_margin_usd | NUMBER(14,4) | Gross margin (from metering.unit_economics) |
Notes: This view is read-only and contains no billing workflow logic. A future billing module (not yet designed) will read this view to generate invoices when the billing model is defined.