Local-first analytics platform for a national retail network

Consulting deliverable for a national retail network in France, with hundreds of stores organized in dozens of regional groupings. The end users are a business and data team, not developers, so the whole product ships as a local application launched from a single file: a FastAPI backend and a React/TypeScript frontend running side by side on the user's machine.

One hard constraint drove the entire design: the data never leaves that machine. No cloud, no external calls. The client drops a single sales file of about 10 million rows (2.6 GB) into the app, and the software produces the full network analysis, 36-month agent-based simulations, and AI-written interpretations of the results, on its own.

The problem: an LLM narrating business figures for executives is one hallucination away from killing the product. During testing, the built-in assistant once answered a question about one store with a correct number for the entire network: real figure, right calculation, wrong scope, no error raised.

Almost right with total confidence is worse than wrong. The system had to be designed so that this class of failure is structurally impossible, not just unlikely.

The system is built around the things the AI is not allowed to do. The LLM never computes a number: deterministic engines calculate everything, and results are frozen at generation time with their uncertainty ranges. Display always reads the frozen artifacts; nothing is ever regenerated at render time, so the same question can never quietly return two different answers. Every displayed KPI maps to a dedicated backend field, and the frontend never re-aggregates, filters or derives figures on its own. Business constants live in a single Python source of truth mirrored to TypeScript, with a CI test that fails the build if a magic number appears anywhere else. Every AI interpretation passes an automated audit before it reaches the screen, checking figure fidelity against the frozen aggregates, coherence of the recommendations against the deterministic scoring model, a confidence threshold, and output structure validation. The gate returns PASS, WARN or FAIL: on FAIL, the user sees a plain deterministic summary instead of the AI text. Scenario identifiers and the JSON output schema are treated as frontend compatibility invariants: the AI layer can change, the contract cannot.

The 2.6 GB sales file made every naive path physically impossible: spreadsheet tooling caps out around one million rows, and a full in-memory load does not fit. Ingestion runs through DuckDB streaming directly from the file path on disk, with zero transfer of the data itself, and completes in about 5 seconds including validation against a strict 22-column schema contract. The rawest real-world problem was upstream: a critical product-range classification column arrived 84% null. Instead of imputing or silently degrading, the pipeline hard-gated the affected flagship KPI, displayed as unavailable with the reason, and quantified the gap precisely enough for the client's data team to rebuild the classification at the source and re-export a complete file. Measuring a data hole rigorously turned out to be the fastest way to get it fixed.

Scenario projection runs on a Mesa agent-based simulation: a stratified sample of client agents (2,000 in the baseline configuration, stress-tested up to 30,000) simulated over 36 months, replicated Monte Carlo style with an explicit convergence criterion; the flagship scenario converged at 160 replications. Every headline figure ships as a range with its 95% confidence interval, never as a lone point. The initial pipeline loaded all 10 million rows before sampling agents; inverting the order, sampling the agent population first and loading only their history, cut a full simulation run from 27 minutes to about 2 minutes, and the optimization was accepted only after byte-for-byte reproduction of a frozen baseline run proved it changed nothing in the results. Calibration set the honesty boundary: aggregate-level projections were statistically solid, but at sub-network granularity, variance imposed a hard ceiling on how many groupings could be certified, and adding agents did not lift it. Rather than publish per-grouping deltas the statistics could not guarantee, the product exposes results only at the granularity it can defend, and says so on screen.

The assistant follows the same doctrine as the rest of the system. A question goes through intent parsing and perimeter resolution, the backend runs a deterministic query and computes the exact figure, and the LLM only phrases the answer. Out-of-perimeter questions get an explicit refusal, not a guess. The perimeter logic was tested in both directions: not just whether it ever gives a false answer, but whether it ever refuses a legitimate question. The final sweep of 33 questions came back clean on both counts: zero false answers, zero wrongful refusals. One without the other gives you either a liar or a useless assistant.

Stack: Python, FastAPI, React, TypeScript, Vite, DuckDB, Mesa, scikit-learn, [LLM RUNTIME : À COMPLÉTER PAR BAPTISTE]