TECHNICAL ARCHITECTURE DEEP DIVEBuilt by Rudy Martinez, Principal Analytics & AI Architect at Database Marketer

Engineering AI Nutrition & Ingredient Intelligence: Conversational State Machines, Data Contracts & Prior-Window Analytics

Direct Architecture Summary

Unstructured conversational AI pipelines often degrade analytical tables through incomplete free-text writes, JSON-to-SQL schema drift, same-timestamp window overlap, and correlated-feature misinterpretation. AI Nutrition & Ingredient Intelligence addresses these failure modes with designed safeguards: a three-stage LLM Gatekeeper state machine, shared Zod contracts verified at 410/410 row parity against node:sqlite in the synthetic test fixture, tenant-partitioned 1 PRECEDING Medallion windows (Bronze -> Silver -> Gold), and explicit statistical interpretation checks.

1. How are trust boundaries organized across ingestion, orchestration, and BigQuery?

Direct Answer: The architecture separates untrusted multimodal client inputs from warehouse feature tables across three explicit zones: client and webhook channels, serverless contract orchestration, and a dbt/BigQuery analytical warehouse.
  • Zone 1 - Multimodal Client & Webhook Channels: React 19, Vite, and Tailwind CSS v4 SPA alongside a serverless messaging webhook accepting free text, voice transcripts, plate photos, and GTIN-13 barcodes.
  • Zone 2 - Serverless Orchestration & Contract Core: Node.js Cloud Functions managing tenant-scoped conversational holds, three-stage Gemini structured routing, OAuth 2.0 FatSecret catalog lookups with token caching, deterministic additive rules, and runtime Zod validation.
  • Zone 3 - Governed Analytical Warehouse & ML: Change-stream export into Google BigQuery, three-layer dbt Medallion models (Bronze -> Silver -> Gold), BigQuery ML (BOOSTED_TREE_CLASSIFIER and ARIMA_PLUS), and an optional Semantic Scholar Graph API boundary.
Conceptual Architecture diagram illustrating Zone 1 client channels, Zone 2 contract orchestration, and Zone 3 BigQuery dbt Medallion marts
Conceptual Architecture - Service boundaries and offline synthetic showcase adapters.

2. How does the three-stage LLM Gatekeeper hold incomplete submissions?

Direct Answer: Separating orchestration into a low-temperature Ambiguity Gatekeeper (0.1), a zero-temperature Event Extractor (0.0), and a deterministic Taxonomy Auditor holds incomplete composite meals in ephemeral session state before canonical events are written.

When status === 'NEEDS_CLARIFICATION' (triggered by missing_gluten_verification, missing_dressing_or_sauce, or missing_prep_state_raw_vs_cooked), the orchestrator stores pending_text in a tenant-scoped session map and prompts once. Resolved food items are checked against deterministic additive classifications grounded in FDA Food Additive standards and Monash FODMAP polyol criteria (distinguishing Sorbitol E420, Maltitol E965, and dairy-derived Lactitol E966 from Erythritol).

Conceptual Architecture diagram of Stage 1 Gatekeeper, Stage 2 Extractor, and Stage 3 Taxonomy Auditor
Conceptual Architecture - Multi-stage validation and deterministic additive taxonomy pipeline.

3. How do shared Zod contracts and dbt Medallion windows improve reliability?

Direct Answer: Shared Zod schemas validate ingestion, Silver, and Gold structures consistently, while Gold SQL models partition by tenant_id and end epoch ranges at 1 PRECEDING, verified across all 410 synthetic fixture records in node:sqlite.
sql— fct_ml_feature_store.sql - Strictly Prior Tenant Window Specification
WINDOW
  w24 AS (
    PARTITION BY tenant_id
    ORDER BY event_epoch_sec ASC
    RANGE BETWEEN 86400 PRECEDING AND 1 PRECEDING
  ),
  w48 AS (
    PARTITION BY tenant_id
    ORDER BY event_epoch_sec ASC
    RANGE BETWEEN 172800 PRECEDING AND 1 PRECEDING
  )
Synthetic Demo Screenshot showing Bronze, Silver, and Gold Medallion table inspection and SQLite parity verification on synthetic records
Synthetic Demo Screenshot - Bronze, Silver, and Gold Medallion lineage inspector (synthetic records).

4. Why must predictive feature attribution be separated from causal inference, and what tradeoffs shaped the design?

Direct Answer: In our 60-day synthetic demonstration, two ingredient categories are intentionally constructed to appear together, yielding a synthetic risk ratio of 7.29x while boosted stumps assign 85.3% attribution to one feature and 0.0% to the other. These synthetic figures illustrate why model importance is not causal evidence on a real person, and why abs(r) >= 0.35 correlation warnings and uncertainty intervals accompany exploratory outputs.
Architectural DecisionChosen DesignTradeoff AcceptedEngineering Rationale
3-Stage LLM PipelineGatekeeper + Extractor + Taxonomy~2 sequential calls on ambiguous logsHolds incomplete composite entries before Bronze storage
Hybrid Additive LexiconDeterministic E-numbers + LLM parsingRequires curating additive rule tablesConsistent Lactitol E966 vs. Erythritol classification
Offline Literature InterfaceDocument contract; block live calls in demoContract spec shown instead of live cardsNever fabricates mock academic citations in public code
Non-Clinical Disclaimer: This architecture showcase and its synthetic dataset (seed = 20261114) demonstrate AI orchestration, data engineering contracts, and statistical modeling patterns only. It is not a medical device, clinical diagnostic tool, or causal treatment recommender.

Explore the Synthetic Demonstration or Connect With DBMarketer

● DIRECT PRINCIPAL CONSULTATION

Contact Database Marketer

Inquire about telemetry audits, modern data stack migrations, enterprise AI agent skills, or fractional principal architecture retainers.

Bot-protected & encryptedResponse within 1 business day