MOD-079 — Snowflake decision publication (operational apply service)¶

Purpose¶

Per ADR-036 the only governed Snowflake → Neon write-back path. Receives versioned, idempotent decision payloads on decision_inbox.decision_result_inbox (Neon), validates the contract (DT-001 GATE), dedups on (decision_id, idempotency_key), routes to the correct operational target table, and writes an immutable row to decision_delivery_log (GOV-006 LOG).

FR scope: FR-309 (≤60s publication), FR-310 (validator + DLQ), FR-311 (replay-safe via idempotency), FR-312 (90d publication log), NFR-014 (≤60s budget), NFR-015 (CDC complement), NFR-024 (immutability).

Architecture¶

   Snowflake decision_curated.decision_result
              │
   (publication path — operator wires Snowflake task → Neon write;
    out of MOD-079 scope V1; integration tests insert directly)
              ▼
   Neon  decision_inbox.decision_result_inbox  (status=pending)
              │
   ┌──────────┴──────────┐
   │ EventBridge schedule│  rate(1 minute)
   │  → applier Lambda   │
   └──────────┬──────────┘
              ▼
   ┌─────────────────────────────────────────┐
   │ decision-applier Lambda                 │
   │   1. ensureSchema (DDL)                 │
   │   2. SELECT pending FOR UPDATE          │
   │      SKIP LOCKED LIMIT 50               │
   │   3. for each:                          │
   │      a. validate (DT-001 GATE)          │
   │      b. dedup (delivery_log lookup)     │
   │      c. route → target apply()          │
   │      d. UPDATE inbox status             │
   │      e. INSERT delivery_log (immutable) │
   └────────┬─────────────────────────────┬──┘
            ▼                             ▼
  bank_core.accounts.accounts.status   decision_inbox.decision_delivery_log
  (V1 — only registered target)        (UPDATE/DELETE revoked)

What MOD-079 owns¶

Resource	Purpose
`decision_inbox.decision_result_inbox`	Snowflake-writable inbox; PK `(decision_id, idempotency_key)`; status field tracks pending → applied/duplicate/rejected/failed
`decision_inbox.decision_delivery_log`	Append-only audit; UPDATE/DELETE revoked from app_user (FR-312, NFR-024, GOV-006)
`decision-applier` Lambda	VPC-attached; polls inbox; validates; routes to target; logs
EventBridge schedule (rate 1 min)	Triggers the applier
SQS DLQ (KMS, 14d)	EB target DLQ for unexpected Lambda panics
3× CloudWatch alarms	error rate, p99 latency, DLQ depth
7× SSM downstream contract paths

Decision contract (ADR-036 §Data contract)¶

Field	Type	Required
`decision_id`	uuid	✓
`idempotency_key`	string	✓
`entity_type`	enum CUSTOMER\|APPLICATION\|PAYMENT\|ACCOUNT	✓
`entity_id`	string	✓
`decision_type`	string (router lookup)	✓
`decision_status`	enum ACCEPT\|REJECT\|REFER\|HOLD\|CLEAR	✓
`decision_summary`	string	✓
`score_summary`	jsonb	optional
`reasons[]`	array of	optional
`produced_by`	string	✓
`policy_refs[]`	array of policy codes	optional
`schema_version`	string (1.0, 1.1)	✓ — DT-001 GATE rejects unknown
`effective_at`	timestamptz ISO	✓
`expires_at`	timestamptz ISO	optional

Target registry (`src/config/decision-targets.ts`)¶

V1 wires only FRAUD_ACTION → accounts.accounts.status because that's the only operational target table that exists today (bank-core MOD-001).

Mapping (src/shared/targets/fraud-action-account.ts): - decision_status=REJECT|HOLD → status='RESTRICTED' - decision_status=CLEAR → status='ACTIVE' (lift restriction) - decision_status=ACCEPT|REFER → no-op (recorded as applied; no status change)

Future targets (per ADR-036 §"Architecture") light up with a small registry addition + corresponding owning-module Flyway:

decision_type	Target table	Owning module	Status
`ONBOARDING`	`customers.onboarding_status`	SD02	not built
`RISK_TIER`	`customers.cdd_tier`	SD02	not built
`SCREENING_ACTION`	`aml_cases.status`	SD03	unknown
`CREDIT_DECISION`	`credit_decisions`	SD05	not built

SSM contract¶

Read¶

Path	Owner
`/bank/{env}/network/vpc-id`, `/private-subnet-ids`	MOD-104
`/bank/{env}/kms/operational/arn`	MOD-104
`/bank/{env}/sns/alerts/arn`	MOD-104
`/bank/{env}/neon/`, `bank-neon/{env}/{database}/`	MOD-103

Write¶

Path	Value
`/bank/{env}/mod079/lambda-arn`, `/lambda-name`	Applier pointers
`/bank/{env}/mod079/inbox-table`	`decision_inbox.decision_result_inbox`
`/bank/{env}/mod079/delivery-log-table`	`decision_inbox.decision_delivery_log`
`/bank/{env}/mod079/dlq-arn`, `/dlq-url`	DLQ for ops drain
`/bank/{env}/mod079/schedule-name`	EventBridge schedule name

FR / Policy coverage¶

FR / Policy	How
FR-309 / NFR-014 (≤60s)	1-minute scheduled poll; ≤50-row batch fits comfortably in the 60s timeout window
FR-310 (validate + DLQ)	`decision-result.ts validate()` enforces ADR-036 §Data contract; failures → status='rejected' + delivery log; Lambda panics → SQS DLQ
FR-311 (replay-safe)	PK on `(decision_id, idempotency_key)` + delivery-log lookup before apply → reapply is silent duplicate
FR-312 / NFR-024 (90d log, immutable)	`decision_delivery_log` UPDATE/DELETE revoked; retention 90d operational (cleanup task TBD)
DT-001 GATE	Only schema-version-validated, contract-conformant rows reach apply. Validator drops 9 invariants.
GOV-006 LOG	Every applied / duplicate / rejected / failed row in delivery_log; immutable; carries decision_id + schema_version + policy_refs + produced_by + apply_target

V1 deferrals¶

Item	Why	Path forward
Snowflake-side publication path	Out of scope per design — Snowflake pushes rows via Snowflake task / external function	Add to MOD-102's runner: SQL migration that creates a Snowflake task writing to the inbox via Snowflake's external function
`customers.cdd_tier` / `aml_cases.status` / `credit_decisions` targets	Owning modules not built	Add target to `decision-targets.ts` + corresponding `apply()` function when target table ships
`notification.dispatched/failed/bounced` outbound EB events	No consumer yet	Wire when MOD-076 dashboards or another consumer surfaces
90-day cleanup task	Volume not yet relevant	Scheduled Lambda or pg_cron job

Tests¶

26 unit — decision-result validator (10 cases covering missing fields, schema-version gate, enum gates, malformed timestamp), decision-targets registry invariants (4 cases), fraud-action target router (5 cases including REJECT/HOLD/CLEAR/ACCEPT/non-ACCOUNT/missing-row).
20 live verification via scripts/verify-deployment.mjs.

FR text vs ADR-036 reconciliation¶

The wiki's FR-309 / FR-310 / FR-311 / FR-312 use Neon → Snowflake wording (predates ADR-036). ADR-036 is signed-off (2026-04-10) and unambiguously defines this module as Snowflake → Neon. The implementation honours ADR-036; FR text should be re-aligned in a wiki amendment. Mapping captured in the FR coverage table above.