Recall

Inspiration

When a food contamination report lands, safety teams ask one question: which shelves have product from this lot, right now? Today they answer by hand — reconciling spreadsheets, supplier EDI, and warehouse exports — and it takes hours to days while contaminated product stays on shelves. FSMA-204, the FDA Food Traceability Final Rule, makes precise traceback a legal deadline: records to the FDA within 24 hours, enforced beginning 2028. The buyer is named, dated, and budgeted: a VP of Food Safety about to be on the hook. We wanted to collapse that 24-hour scramble into one query.

## What it does

Paste a contaminated Traceability Lot Code and, in one SQL statement, Recall:

1. Traces the lot through a foreign-key-constrained supply DAG to every downstream lot,
2. Maps every affected store with recalled-unit counts, and
3. Surfaces semantically-similar past incidents by vector similarity.

The demo lot PRD-OUTBREAK-0001 (Romaine Lettuce) reaches 1,400 affected stores across 38 states, 674,285 units, 81 contaminated lots / 80 edges — in ~300ms over 580,000 rows. An Outbreak Time-Travel scrubber replays the spreading blast radius by shipment time, and a Query Inspector shows the live EXPLAIN plan. Every visible pixel is a query result.

## How we built it

• Database: Amazon Aurora PostgreSQL (Serverless v2, engine 16.6, us-east-1, scale-to-zero) with pgvector HNSW + PostGIS GiST.
• The hero query is one SERIALIZABLE WITH RECURSIVE statement fusing three index paths: a Recursive Union over the lot_links DAG, a GiST geography join for the map, and an HNSW cosine search for similar incidents.
• Frontend: Next.js 16 App Router on Vercel Fluid Compute (iad1, co-located with Aurora), Tailwind v4 + shadcn/ui, MapLibre, react-force-graph.
• DB access: pg module-scope pool + attachDatabasePool, raw parameterized SQL, TLS verified against the Amazon RDS CA bundle.
• Keyless AWS: Bedrock Titan Text Embeddings V2 called with no long-lived keys — Vercel OIDC → AWS STS AssumeRoleWithWebIdentity → least-privilege IAM role. The DB password is RDS-managed in Secrets Manager.

## Challenges we ran into

• Getting the planner to use HNSW at demo volume — at a few thousand incidents Postgres prefers a seq scan; transaction-scoped SET LOCAL random_page_cost = 1.1 / enable_seqscan = off (which also reflects Aurora's real distributed-storage I/O profile) keeps the index paths engaged.
• Keyless OIDC — pinning the STS trust policy to production + preview without a wildcard.
• Serverless connection churn — module-scope pool + attachDatabasePool so idle clients release before the function suspends.
• Recursion safety — a path visited-set and depth < 12 guard so a 250k-edge graph can't cycle or go quadratic.
• Serializable correctness — a bounded 40001 retry loop so an ingest mid-trace can't corrupt the recall scope.

## Accomplishments that we're proud of

• Three fundamentally different index paths — recursive graph, geospatial, and vector — fused in one ~300ms serializable query over 580k rows, with the live EXPLAIN as proof.
• Outbreak Time-Travel replay via an asOf cutoff on the same recursive trace.
• Fully keyless AWS access; scale-to-zero verified at 0.0 ACU (~$0) idle on CloudWatch.
• A provably non-interchangeable database choice — the app literally cannot run on DynamoDB or DSQL.

## What we learned

• The database can be the product, not just storage — the right data model plus extensions turn a recall into one correct statement instead of orchestration code.
• Aurora's distributed storage makes index access genuinely cheaper than on-prem cost defaults assume.
• The Vercel OIDC → STS pattern removes long-lived cloud keys entirely — production-shaped security with less plumbing than key rotation.
• Seeding at real volume (580k rows) is what makes an EXPLAIN honest.

## What's next for Recall

• Upstream (source) trace — flip the recursion to find the originating supplier/ingredient.
• Live shipment ingest via Vercel Cron / SQS, exercising the serializable retry path.
• One-click FDA-ready traceability export plus store shelf-pull notifications.
• Multi-tenant Recall-as-a-Service, billed per facility.

Built With

• amazon-aurora-postgresql
• amazon-bedrock
• aws-iam
• aws-secrets-manager
• maplibre-gl
• next.js
• node-postgres
• node.js
• pgvector
• playwright
• postgis
• react-force-graph
• shadcn/ui
• tailwind-css
• typescript
• vercel
• vitest
• zod