Building PrismAnalyst: A Privacy-First, Multi-Agent AI Analyst for Business Data

A LangGraph StateGraph opens every conversation at a Supervisor node, which classifies the question against five labels (SALES, ASSORTMENT, ANALYST, BOTH, CLARIFY) using structured (Zod-validated) output from the LLM. If structured output fails, a regex-based fallback parser extracts the route from raw text, and if that fails too, the system defaults to SALES rather than going silent: a real question should never be dropped on a parsing error. BOTH triggers a chained hand-off: the Sales agent runs first and passes its findings as context into the Assortment agent, so a question like "what's driving the revenue drop, and which SKUs are at risk" gets a coordinated answer from two specialists instead of one shallow one.

Every specialist agent (Sales, Assortment, and Analyst) is given exactly two tools: query_dataset_sql and generate_chart. There is no separate hardcoded statistics, forecasting, or recommendations engine; those existed in an earlier iteration and were deliberately removed once the team proved the LLM could write the SQL itself. Forecasting runs as a REGR_SLOPE/REGR_INTERCEPT regression, anomaly detection as an IQR calculation via PERCENTILE_CONT, correlation via CORR, and period comparisons via window functions (LAG/LEAD), all computed live against the uploaded data, not recalled from the model's memory.

The analysis engine, DuckDB, runs in-process and never exposes raw rows to the LLM. The model only ever receives the dataset's column names and types, plus aggregated query results: sums, counts, averages, capped result sets. A strict-mode layer sits in front of every query result: it caps output to 50 rows and 5 columns by default, and auto-detects and redacts likely PII columns (email, phone, address, SSN, date of birth, and more) by name pattern before anything reaches the model.

Because agents write their own SQL, that SQL has to be untrusted input. A validator requires every query to start with SELECT or WITH, rejects a blocklist of mutating keywords and file/network functions (INSERT, DROP, ATTACH, COPY, read_csv, httpfs, and others), and blocks multiple chained statements. A LIMIT is auto-injected on any query that lacks one, and an existing LIMIT is capped rather than trusted outright, with nesting-aware parsing so a LIMIT inside a subquery isn't mistaken for the outer one. Every query also runs under a hard timeout.

Real spreadsheets exported from enterprise systems rarely start with a clean header row. The ingestion pipeline unmerges merged cells (propagating the top-left value across the range), then, for anything larger than 20 rows, asks the LLM to look at the first and last 20 rows and identify where the actual header row and any footer/summary rows are, so title rows, generated-on timestamps, and "Grand Total" rows get stripped before the data ever reaches DuckDB. Once ingested, a separate pass samples each text column and auto-casts it to a proper DATE type when 90%+ of sampled values parse cleanly, correcting for cases where DuckDB's own CSV auto-detection misses non-ISO date formats.