ClinSupplyCompass

A multi-tenant planning platform that replaced a roughly 130-sheet workbook with a deterministic engine, and keeps every trial's plan honest against what actually happens.

Status: In production
Replaced: ~130-sheet planning workbook
Engine: Deterministic, pure, golden-master tested
Isolation: App filter plus Postgres RLS
Stack: Python 3.11, FastAPI, PostgreSQL (Supabase, RLS), Jinja2 + HTMX, Alembic, pytest, Playwright

The problem

Clinical trials have to get investigational drug to patients in many countries, through regional depots, without a stock-out that harms a patient and without over-producing an expensive, expiring product. Teams were planning this in a spreadsheet with around 130 sheets that could model exactly one trial, and that broke quietly every time reality drifted from the plan.

What we built

A pure Python planning engine: enrollment curves, dosing, overage, site stocking, inventory roll-forward and months-of-supply coverage, computed as a fixed pipeline with no I/O, so every trial reuses the same calculation and only the parameters differ.
Golden-master parity tests that reproduce the original reference workbook within rounding tolerance, so planners could trust the migration before they trusted the software.
A closed-loop planning cycle: run the plan, approve a baseline, export the demand-upload file the supply system expects, load actuals, re-plan, snapshot KPIs and surface supply recommendations, with an audit trail of every step.
A guided web application for non-technical planners (server-rendered, no JavaScript build step), multi-tenant organizations, invite-only membership, and a monthly portfolio check-in across studies.

Where AI fits

AI Study Pulse writes a plain-language operational assessment of a study from its recorded plan, coverage, cycles and actuals, and answers questions about that study.
Ask AI gives an organization owner grounded answers about their own studies, cycles, measurements and team. Off-scope questions are refused server-side with a fixed sentence, by construction rather than by model cooperation.

Engineering discipline

Tenant isolation enforced twice: every query is filtered by the signed-in organization, and Postgres row-level security enforces the same boundary underneath it.
The engine is pure; only golden-master tests touch the source workbook. Re-planning produces a new study version that the unchanged engine runs.
Versioned studies, approved baselines, cycle events and KPI snapshots are all first-class records, so a plan-versus-actual comparison is a query, not an argument.

What it is worth

Any trial fits the same engine. Adding a study is data entry, not a new spreadsheet.
The loop between plan and actuals turns a static forecast into an operating rhythm the whole portfolio can be reviewed against.
Planners keep the file format their downstream supply system already accepts.