Skip to content

Neon and Snowflake physical storage

Physical storage specification for the decision_* tables in Neon and the signal_* / decision_* schemas in Snowflake.

Aligned to the one-way CDC model in ADR-003 and the decision publication pattern in ADR-036. Converts the data-contract layer into physical storage structures.

Design principles

  • Neon → Snowflake carries operational facts through CDC. Snowflake does not become the operational system of record.
  • Snowflake → Neon returns only approved, stable decision outcomes. It does not push back raw analytical features or internal model state.
  • Reason codes must be both machine-readable and human-readable. The returned operational decision must show the status, score summary, short labels, and plain-language explanations without exposing the full internal algorithm.
  • All return publications must be versioned, idempotent, and replay-safe.
  • Physical tables must support auditability, lineage, and point-in-time reconstruction of the decision that was applied.

Schema layout

Platform Schema Purpose
Neon decision_inbox Inbound approved decisions from Snowflake awaiting application
Neon decision_state Current operational decision state by customer/application/payment
Neon integration Delivery, replay, and idempotency tracking
Snowflake signal_raw Append-only typed signal events
Snowflake signal_curated Conformed and query-friendly signal layers
Snowflake decision_curated Approved decision results ready for publication
Snowflake governance_meta Contract versions, reason-code catalogues, and publication metadata

Neon physical model

Neon stores operationally relevant results. It mirrors Snowflake's full analytical structures only where operationally necessary. Instead it keeps the current applied decision state, a replay-safe inbound queue, and delivery logs.

decision_inbox.decision_result_inbox

Append-only inbound publication queue from Snowflake. One row per decision publication attempt keyed by decision_id and idempotency_key. Processed asynchronously by operational application services.

Column Type Notes
decision_id uuid Primary identifier for the decision result
idempotency_key text Unique constraint; replay-safe apply key
entity_type text CUSTOMER, APPLICATION, PAYMENT
entity_id text Identifier in the operational domain
decision_type text ONBOARDING, RISK_TIER, FRAUD_ACTION, SCREENING_ACTION
decision_status text ACCEPT, REJECT, REFER, HOLD, CLEAR
risk_score numeric(8,2) Optional score summary
fraud_score numeric(8,2) Optional score summary
risk_tier text LOW, MEDIUM, HIGH
reason_summary jsonb Human-readable and machine-readable reason list
policy_refs jsonb Policy references e.g. AML-011, AML-012, AML-013
model_versions jsonb Risk/fraud model versions used
effective_at timestamptz When the decision becomes active
expires_at timestamptz Optional expiry
source_contract_version text Decision contract version
ingested_at timestamptz Default now()
processing_status text PENDING, APPLIED, FAILED, SKIPPED
processed_at timestamptz When operational system applied it

decision_state.customer_decision_state

Current applied state per customer. Supports fast lookups by onboarding, servicing, fraud operations, and case management. Stores the latest effective decision rather than the full history.

decision_state.application_decision_state

Current applied state per onboarding application. Useful when a customer can have multiple applications or resubmissions.

integration.decision_delivery_log

Tracks publication receipt, validation, apply outcome, retry outcome, and errors. Supports operational support and replay analysis.

Snowflake physical model

Snowflake holds the analytical and decisioning layers. The tables below are designed so the signal and decision contracts map directly into physical storage without each model inventing its own payload shape.

signal_raw.signal_event

Append-only fact table for typed signals. Stores raw but structured risk, fraud, and screening observations. Allows lineage back to producing module, policy, and model version.

See Signal taxonomy v1 for the full signal code catalogue.

signal_curated.customer_signal_current

Materialised latest-state view/table for current customer-facing signal posture. Used by risk, fraud, onboarding, and case operations for quick reads.

decision_curated.decision_result

Approved decisions ready for publication back to Neon. Holds a compact score summary and operator-friendly reason package. Can be published repeatedly without changing decision identity.

governance_meta.reason_code_catalog

Canonical reason-code registry. Each code includes:

Field Purpose
reason_code Stable machine key
reason_label Short operator label
reason_text Plain-language explanation template
score_impact Optional signed summary impact
display_rank Default display order shown to humans

Publication and apply flow

Step Platform Action
1 Neon Operational facts created and replicated outward through CDC
2 Snowflake Signals created, aggregated, and evaluated by risk/fraud/AML logic
3 Snowflake Approved decision_result row created with reason package and scores
4 Snowflake → Neon Decision result published to decision_result_inbox
5 Neon Operational service validates idempotency_key and applies decision
6 Neon Current state updated; apply outcome written to decision_delivery_log

Human-readable reason design

The return contract to Neon always contains two layers of reasoning:

  1. A compact machine-readable reason code list — e.g. PEP_MATCH, DEVICE_CLUSTER_RISK, ADDRESS_MISMATCH.
  2. A customer-service-readable explanation list — e.g. "Application linked to a device already used by multiple identities" or "Additional identity review is required because address information could not be confidently matched."

This gives clear visibility in operational systems without disclosing hidden thresholds, internal weights, or all features used by the scoring model.