TL;DR: A reproducible SQL query is not automatically an auditable fraud decision. At billion scale, control ANN retrieval and persist its complete evidence chain in SQL. Compare each release against exact search before it can block customers.
Key Takeaways: - Re-running SQL proves operational repeatability, not preservation of the original evidence and versions. - Controlled retrieval fixes inputs, versions, candidate limits, ranking rules, and durable manifests. - ANN stays approximate, so release gates must measure behavior against exact search before enforcement.
A SQL Query Is Not an Audit Trail
The most dangerous assumption in fraud systems is that a reproducible SQL query is automatically an auditable decision. Billion-scale vector retrieval adds approximate matches, model versions, and hybrid evidence.
The query alone no longer explains why a customer was blocked.
Operational reproducibility asks whether the same query runs again. Evidentiary reproducibility asks whether the replay used the same evidence and versions.
That distinction is the core of operational reproducibility versus evidentiary reproducibility.
1SELECT customer_id, blocked_reason, scored_at2FROM fraud_decisions3WHERE decision_id = :decision_id;
A conventional predicate exposes the row and condition used. An ANN result exposes only the selected candidates.
Those candidates depend on the metric, index build, filters, and traversal path. The top-k list does not expose that dependency.
Exact nearest-neighbor search compares every eligible vector. It guarantees the closest set, but exhaustive distance calculations make it resource-intensive.
At billion scale, approximate retrieval becomes a practical dependency, not an optional optimization. SQL still helps. It can join structured attributes and vectors while reducing coordination with a separate search service.
But co-location does not make an ANN decision self-explaining. It only puts more evidence near the transaction.
Reframe the Audit Question
A reviewer must be able to answer four questions: - What evidence was available at decision time? - Which retrieval path and filter scope ran? - How were candidates ranked and fused? - Which versions produced the final action?
Use the auditability checklist for vector-based fraud decisions to test those questions.
Run this test against one recent block. Replay it with only the stored query, table snapshot, and documented configuration.
The test reveals a hard truth. The evidence chain becomes harder to reconstruct once approximate retrieval and multiple data sources enter the path. Which hidden dependency will a reviewer find hardest to reconstruct?
ANN, Semantics, and Hybrid Retrieval Multiply the Evidence
Exact search and ANN answer the same question with different guarantees. Exact search compares every eligible vector and returns the nearest set for the declared metric. ANN traverses an index and returns an approximate set.
Cosine, dot-product, and Euclidean distance can produce different orderings. The audit record must bind the chosen metric to the model and dimension.
1retrieval_contract:2 embedding_model: ${EMBEDDING_MODEL}3 vector_dimension: ${VECTOR_DIMENSION}4 distance_metric: ${DISTANCE_METRIC}5 top_k: ${APPROVED_TOP_K}6 index_build_id: ${INDEX_BUILD_ID}7 filters: ${APPROVED_FILTER_SCOPE}8 tie_break: ${APPROVED_TIE_BREAK}
Approximate traversal favors graph or partition paths. Those paths can shift close rankings when queries change slightly.
Hybrid retrieval adds another layer. Vector scores, rules, keyword matches, and rerankers can all influence one fused result.
A final score can hide that influence. If reviewers see only the score, they can't assign responsibility to a source or rule.
That is the governance gap described in how hybrid retrieval creates governance gaps.
Inventory the Hidden Degrees of Freedom
Record every dependency for each decision path: - Embedding model and vector dimension - Distance metric and top-k - Filters and candidate source - Index generation and build ID - Reranker, threshold, and tie-break rule - Data snapshot and application release
Vector count alone can't determine cost. Dimensionality, top-k, filter selectivity, concurrency, write rate, retention, and memory all reshape the workload.
Use capacity planning for measured vector workloads before choosing infrastructure.
Moving a managed vector service into exploratory SQL reads does not remove that work; it can hide scale costs inside a larger database bill.
Separate retrieval speed from evidence quality. Vector speed loses to AI data provenance explains why.
Exact search for every request would waste resources at billion scale. The real task is setting a boundary that keeps approximate retrieval controlled, bounded, and reconstructable. How should that boundary be enforced?
Controlled Retrieval Is the Auditability Boundary
Controlled retrieval turns that boundary into a contract. A controlled run has fixed inputs, explicit filters, a declared metric, a known candidate limit, versioned ranking logic, and durable evidence.
ANN remains approximate. Reviewers can establish the eligible scope, returned candidates, versions, and final action.
That isn't perfection. It is a defensible explanation of a bounded process.
The controlled retrieval design principles make those boundaries explicit:
1decision_id: ${DECISION_ID}2retrieval_mode: ${APPROVED_MODE}3query_vector_ref: ${QUERY_VECTOR_REF}4embedding_model: ${EMBEDDING_MODEL}5distance_metric: ${DISTANCE_METRIC}6filters: ${APPROVED_FILTER_SCOPE}7top_k: ${APPROVED_TOP_K}8index_build_id: ${INDEX_BUILD_ID}9data_snapshot_id: ${DATA_SNAPSHOT_ID}10code_release: ${CODE_RELEASE}11tie_break: entity_id
Separate Retrieval Modes by Purpose - Broad ANN discovers candidates across an approved scope. - Scoped exact search validates high-impact matches or adjudicates bounded cases. - Deterministic rules enforce hard exclusions before semantic ranking.
Fixed top-k values, documented filters, and stable entity-ID tie-breakers improve inspectability. They work only when the engine preserves candidate ordering and state.
See why deterministic tie-breakers matter for the dependency. A tie-breaker can't repair a different candidate set.
Filters show what policy excluded. A scoped exact comparison can reveal missed near neighbors. ANN alone can't prove global absence.
Create an immutable manifest for every decision. It should include candidate IDs, scores, ranks, source versions, and the final action.
Persist each hybrid source separately before fusion. Keep vector candidates, rule outcomes, keyword matches, and reranker outputs beside the fusion version.
Finally, separate investigation from enforcement. Exploratory agent queries guide analysts. Only an approved retrieval contract can trigger a customer-facing action.
This also closes the gap identified in Your RAG citations aren't audit trails. Citations point backward. Decision manifests capture the active evidence chain.
A boundary only becomes useful when engineering turns it into a transactionally complete evidence chain. That chain must survive index rebuilds and later data corrections. How should those requirements become linked SQL records?
Build a Replayable Decision Record in SQL
Define the Minimum Audit Model
Create five linked record types around a shared `decision_id`: - The customer decision and final action - The retrieval run and its configuration - Candidates before fusion - Contributions from each retrieval source - An immutable audit manifest
Use the SQL schema for replayable vector decisions as a starting point.
1CREATE EXTENSION IF NOT EXISTS vector;23CREATE TABLE fraud_decision (4 decision_id uuid PRIMARY KEY,5 customer_id text NOT NULL,6 final_action text NOT NULL,7 final_score double precision,8 created_at timestamptz NOT NULL DEFAULT now()9);1011CREATE TABLE fraud_retrieval_run (12 decision_id uuid PRIMARY KEY REFERENCES fraud_decision(decision_id),13 query_vector vector,14 embedding_model text NOT NULL,15 vector_dimension integer NOT NULL,16 distance_metric text NOT NULL,17 top_k integer NOT NULL,18 filters jsonb NOT NULL,19 index_name text NOT NULL,20 index_build_id text NOT NULL,21 data_snapshot_id text NOT NULL,22 code_release text NOT NULL,23 reranker_version text,24 threshold double precision25);2627CREATE TABLE fraud_retrieval_candidate (28 decision_id uuid NOT NULL REFERENCES fraud_decision(decision_id),29 source_type text NOT NULL,30 entity_id text NOT NULL,31 distance double precision,32 rank integer NOT NULL,33 PRIMARY KEY (decision_id, source_type, entity_id)34);3536CREATE TABLE fraud_source_contribution (37 decision_id uuid NOT NULL REFERENCES fraud_decision(decision_id),38 source_type text NOT NULL,39 source_key text NOT NULL,40 contribution jsonb NOT NULL,41 fusion_version text NOT NULL,42 PRIMARY KEY (decision_id, source_type, source_key)43);4445CREATE TABLE fraud_audit_manifest (46 decision_id uuid PRIMARY KEY REFERENCES fraud_decision(decision_id),47 manifest jsonb NOT NULL,48 created_at timestamptz NOT NULL DEFAULT now()49);
Store the query vector or a secure, reproducible reference. Bind the model, dimension, metric, top-k, filters, index build, snapshot, release, reranker, threshold, and action.
Store candidate IDs, distances, ranks, and source types before fusion. This preserves how the top-k formed even if application ranking changes later. How should retrieval populate those records without breaking the chain?
Illustrate the Aurora PostgreSQL Query
The following application query uses pgvector's cosine-distance operator:
1SELECT entity_id,2 embedding <=> :query_vector AS distance3FROM fraud_entity_vectors4WHERE customer_id = :customer_id5 AND event_ts >= :window_start6ORDER BY embedding <=> :query_vector7LIMIT :top_k;
Adapt the expression to the validated extension version. Use the operator that matches the approved metric, then confirm the planner behavior in the deployed environment.
The Aurora PostgreSQL vector-search implementation notes cover the extension and index choices that need validation.
Run `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)` on sampled controlled runs. Record whether the ANN index ran and how filters affected candidate selection. Don't assume one operator query will keep the same plan.
Write candidates and the decision manifest in the same transaction when possible. Make history records append-only through permissions or triggers. A working query is not enough. Which SQL dialect and index generation actually ran?
Treat Dialect and Index Generation as Evidence
Aurora PostgreSQL uses pgvector operators and indexes. SQL Server exposes `VECTOR_SEARCH` with DiskANN.
They aren't interchangeable SQL behavior. Store the dialect, extension version, index generation, and build ID with each decision.
Give auditors a read-only view:
1CREATE VIEW v_fraud_decision_evidence AS2SELECT d.decision_id,3 d.customer_id,4 d.final_action,5 r.embedding_model,6 r.distance_metric,7 r.top_k,8 r.index_build_id,9 c.source_type,10 c.entity_id,11 c.distance,12 c.rank,13 s.contribution,14 s.fusion_version,15 a.manifest16FROM fraud_decision d17JOIN fraud_retrieval_run r USING (decision_id)18LEFT JOIN fraud_retrieval_candidate c USING (decision_id)19LEFT JOIN fraud_source_contribution s USING (decision_id)20JOIN fraud_audit_manifest a USING (decision_id);
A complete record proves what ran, not whether it found the right candidates. ANN behavior must be tested against exact search before it can block a customer. What failures should the release corpus be designed to expose?
Test ANN Behavior Against Exact Search Before It Can Block Fraud
Build a Regression Corpus
Include confirmed fraud, legitimate lookalikes, disputed outcomes, low-volume accounts, and cases near the approved distance threshold.
Easy matches make ANN look better than it is. The hard cases expose ranking drift.
1release_gate:2 mode: shadow3 compare: - ann_candidates - exact_candidates4 metrics: - candidate_overlap_at_k - rank_displacement - score_drift - final_action_change5 enforce_only_after_approval: true
Compare ANN and exact results on representative, bounded scopes. Measure four things: - Candidate overlap at the approved k - Rank displacement - Score drift - Final-action changes
Use exact search as a validation instrument, not the default path for every billion-scale request. Its exhaustive comparison is precise but resource-intensive.
Risk owners should set stronger agreement requirements for higher-impact actions. Document broader ANN variation for lower-risk scoring.
New indexes, embedding versions, and ranking changes should run in shadow mode first. Compare them with the current production contract.
The shadow-mode gates for fraud ranking explain how to make that comparison operational.
Replay sampled incidents against archived snapshots and manifests. If the original vector, candidate set, or source version is missing, label the replay incomplete.
An approximation isn't an exact replay. The ANN versus exact-search regression harness should distinguish those states explicitly.
Re-run comparisons after changes to embeddings, metrics, indexes, filters, or fusion. Previous approval doesn't cover new dependencies.
Separate retrieval drift from semantic drift. Record whether changing embeddings caused the decision change, rather than blaming the ANN index.
Use a fraud model error budget to connect retrieval variation with the harm your action policy permits.
Once ANN behavior is measurable and releases are gated, the remaining CTO decision is capacity and control ownership. What will production-shaped measurements reveal?
Scale From Measured Workload, Not Vector Count
Build the Capacity Model From Production-Shaped Measurements
Capacity cannot be inferred from a headline vector total. Dimensionality, filtering, concurrency, writes, retention, and memory all reshape the cost.
1capacity_observation:2 vector_dimension: ${VECTOR_DIMENSION}3 distance_metric: ${DISTANCE_METRIC}4 ingestion_rate: ${INGESTION_RATE}5 retention: ${RETENTION}6 concurrency: ${CONCURRENCY}7 top_k: ${TOP_K}8 filter_selectivity: ${FILTER_SELECTIVITY}9 audit_replay_rate: ${AUDIT_REPLAY_RATE}10 memory_headroom: ${MEMORY_HEADROOM}
Treat index memory, rebuild behavior, exploratory reads, exact replay, and audit-log growth as one operating model.
An operating model should produce four concrete outcomes: - Investigators can reconstruct the evidence. - Engineers can replay sampled decisions. - Release owners can compare retrieval behavior. - Capacity owners can forecast cost from observed workload.
Those outcomes only work when responsibility is clear. Who owns each control?
Convert Governance Into Decision Rights
Name owners for embedding models, filters, top-k, thresholds, rerankers, and index generations. Require an auditable approval for every production change.
Vendor operating history is a screening signal. It can't replace validation against your data, risk thresholds, and recovery objectives.
Also review the documented enterprise vector-search delivery experience. Treat it as a screening signal, not proof; it can't replace validation against your data, risk thresholds, and recovery objectives.
Before the next release, can the named owners approve the plan against the evidence this model produces?
About the author
Mayank Singh is a software developer at Levitation Infotech, where he builds web and AI-powered applications across the company’s fintech, healthcare, and enterprise projects.