Architecture

System Overview

The ETL pipeline is a Next.js application with serverless API routes handling the heavy processing. All state lives in a single Supabase table (etl_jobs), with Realtime subscriptions pushing progress updates to the frontend.

Layers

Data flow

Browser → fetch() → Serverless API → Claude API → Supabase
Browser ← Realtime subscription ← Supabase

The browser sends requests to the serverless functions, which do the heavy lifting (parsing Excel, calling Claude, writing output). Progress updates are written to etl_jobs and pushed to the browser via Supabase Realtime — no polling.

AI Models

The pipeline uses two Anthropic Claude models at different stages, chosen for the cost/capability tradeoff at each step.

Prompt strategy

• System prompts define the canonical schema and expected output format (JSON)
• User prompts include the actual spreadsheet data as formatted text grids
• Temperature: 0 for all extraction calls (reproducible output)
• Structured output: All extraction calls use JSON mode with explicit field-level instructions

Cost profile

Every file goes through the full AI pipeline. LLM extraction consistently produces the highest accuracy across all vendor formats, so the pipeline uses it exclusively. AI calls per job:

1. Structure analysis (one Opus call)
2. Sheet scanning (one Sonnet call per Sheet)
3. Data extraction (up to 8 parallel Sonnet chunk calls)
4. Spot-check (one Sonnet call to audit a sample of extracted Units)

Typical file: 2 + chunk count + 1 API calls (e.g., ~11 calls for a file with 8 extraction chunks).

Module Inventory

Key Design Decisions

LLM-Only Extraction

LLM extraction consistently produces the highest accuracy across all vendor formats, so the pipeline uses it exclusively. Data is chunked intelligently (up to 8 chunks in parallel) and processed via Claude Sonnet. This single-path design eliminates mode-selection logic and quality-gate fallbacks, simplifying the pipeline while maintaining top accuracy.

Two-Phase API Design

Structure analysis and extraction are separate API calls:

• /api/analyze (120s timeout) — reads the spreadsheet, classifies Sheets into Tables, maps columns to schema
• /api/extract (300s timeout) — uses the structure map to pull Units, validate, and generate output

This separation enables the human-in-the-loop review step between phases.

Streaming Progress via Supabase Realtime

Both API phases push granular progress updates to the etl_jobs table. The frontend subscribes via Supabase Realtime and renders milestones in a scrolling timeline. Progress percentages are distributed:

• Upload: 0–5%
• Analysis: 8–40%
• Extraction: 45–95%
• Validation + Output: 95–100%

Spot-Check Verification

After extraction, the system audits a sample of extracted Units against the source spreadsheet. It samples 8 Units using biased selection (first 5, last 5, any vacant Units, random fill) and re-extracts them independently via a separate Sonnet call. Discrepancies are scored and surfaced as a confidence rating.

Constants

const STRUCTURE_MODEL = "claude-opus-4-6";     // Structure analysis (deep reasoning)
const EXTRACTION_MODEL = "claude-sonnet-4-6";  // Data extraction chunks
const SCAN_MODEL = "claude-sonnet-4-6";        // Sheet pre-scanning
const CHUNK_ROWS = 60;                          // Max rows per horizontal chunk
const OVERLAP_ROWS = 5;                         // Overlap between chunks (dedup context)
const PARALLEL_CHUNKS = 8;                      // Max concurrent extraction workers
const UNITS_PER_CHUNK = 18;                     // Max unit blocks per vertical chunk