ClinSupplyCompass

ClinSupplyCompass began with a problem I have seen many times in enterprise systems.

The business already had working software.

It just happened to be an Excel workbook.

The workbook had years of clinical-supply knowledge embedded in formulas, hidden sheets, cross-sheet references, planning conventions, manual overrides, and assumptions understood by the people who used it. Replacing Excel was therefore not a matter of rebuilding some screens around a database.

First I had to determine what the workbook actually meant.

Then I had to prove that software could reproduce it before asking anyone to trust the software instead.

That shaped nearly every architectural decision that followed.

Architecture at a Glance

                      Clinical Supply Planner
                               │
                               ▼
                    app.clinsupplycompass.com
                               │
                    FastAPI + Jinja2 + HTMX
                               │
             ┌─────────────────┼─────────────────┐
             │                 │                 │
             ▼                 ▼                 ▼
       Planning Service   Closed-Loop S&OP   AI Assistance
             │                 │
             └────────┬────────┘
                      ▼
             Pure Python Planning Engine
                      │
          enrollment → demand → inventory
                 → MOS/MFC → upload
                      │
                      ▼
             PostgreSQL / Supabase
             Auth + RLS + persistence

The most important boundary is in the middle.

The calculation engine does not know about HTTP, authentication, PostgreSQL, HTML, AI, or even Excel.

It accepts study parameters and produces a planning result.

Everything else is an adapter around that core.

That separation became important later, because ClinSupplyCompass grew from a forecasting engine into a multi-tenant planning application without requiring the underlying clinical-supply mathematics to become an application framework.

---

Case Study 1: Replacing a 130-Sheet Workbook Without Replacing Its Knowledge

The problem

The source planning model was a large clinical-trial supply workbook with roughly 130 worksheets.

Underneath those sheets was a reusable MRP-style pipeline:

study assumptions
        ↓
patient enrollment
        ↓
drug demand
        ↓
inventory movement
        ↓
MOS / forward coverage
        ↓
country / depot / finished-good rollups
        ↓
SAP IBP Demand Upload

The visible spreadsheet made this look procedural.

It wasn’t.

The same forecasting template was effectively being instantiated repeatedly across combinations of depots and investigational products.

The real problem was therefore not:

How do I convert Excel formulas into Python?

It was:

What is the underlying domain model that all these formulas are expressing?

The constraint

The workbook was the business specification.

I could not improve an equation simply because another equation looked cleaner.

I could not silently reinterpret a date offset, dosing factor, treatment ratio, global depot pool, or hand-entered value.

And I could not declare the new engine correct because its totals looked approximately right.

The replacement had to demonstrate parity against the system people already trusted.

The decision

I treated the workbook as an executable specification and separated study data from planning mechanics.

The resulting core became a pure deterministic Python engine:

StudyContext
     │
     ▼
Planning Engine
     │
     ├── enrollment
     ├── treatment demand
     ├── site stocking
     ├── inventory
     ├── overage
     ├── MOS
     ├── forward coverage
     └── upload demand
     │
     ▼
PlanningResult

Study-specific facts such as countries, depots, drugs, enrollment curves, dosing, treatment ratios, routing, shelf life, and overage remain data.

The engine remains reusable.

That distinction became one of the project’s governing rules:

The engine is universal. The study parameters are data.

The proof

I built golden-master tests against the original workbook rather than relying on hand-written expected values.

The software reproduces the source Demand Upload output across all 484 non-zero rows in the reference study within the defined numeric tolerance.

That process also uncovered distinctions that could easily have disappeared in a conventional rewrite.

For example, the workbook contains two different meanings of demand.

One drives inventory and coverage calculations.

Another drives the SAP upload.

They are related, but they are not interchangeable.

A naïve rewrite could produce plausible numbers while being operationally wrong.

The parity work forced those distinctions into the domain model.

Going further: round-trip verification

Importing the workbook was not enough.

ClinSupplyCompass can ingest the workbook, reconstruct a runnable study, recalculate derived values, export the workbook structure again, and verify the result.

For the data-bearing sheets, preserved value/parameter cells are checked using SHA-256 evidence while calculated cells are compared numerically.

That gives me a much stronger statement than:

“The new application seems to match Excel.”

It gives me a repeatable verification boundary between the legacy planning artifact and the software replacing it.

The outcome

The spreadsheet stopped being the architecture without losing its business knowledge.

The planning model became testable independently of the user interface, database, and deployment platform.

New study types can now be expressed through parameters and controlled engine seams rather than by copying another worksheet and changing formulas.

And the original workbook still serves an important purpose:

It is a golden reference against which the software can prove itself.

---

Case Study 2: Turning a Forecast Calculator Into a Closed-Loop Planning System Without Polluting the Engine

Once the calculation engine worked, another problem appeared.

A forecast by itself is not a planning system.

Real clinical supply work continues after somebody presses Run.

A plan gets reviewed.

A baseline is approved.

Supply is exported.

Actual events arrive.

Assumptions change.

The team needs to understand what changed and why.

Then they plan again.

The problem

It would have been easy to put all of that lifecycle behavior inside the forecasting engine.

That would also have been the wrong architecture.

The mathematical engine answers:

Given these inputs, what is the resulting plan?

A planning application has to answer very different questions:

Which plan was approved?

Who changed it?

What actuals arrived afterward?

How accurate was the original baseline?

What changed during re-planning?

Those are state, workflow, provenance, and audit questions.

They do not belong inside the calculation model.

The decision

I kept the engine deterministic and built the operational loop around it.

Plan
  ↓
Review
  ↓
Approve Baseline
  ↓
Export
  ↓
Load Actuals
  ↓
KPI Snapshot
  ↓
Re-plan
  ↓
new version

The approved baseline remains the reference point.

A re-plan creates a new version rather than rewriting history.

Actuals are stored as facts.

Planning-cycle events form an audit trail.

Forecast observations are append-only.

The calculation engine receives only the information needed to calculate the next plan.

Protecting the engine

Some real-world facts eventually do need to affect the mathematics.

Patient discontinuations are a good example.

Instead of teaching the entire engine about planning-cycle databases and actuals, I added a narrow data-driven seam: a monthly demand scaling input.

When the feature is unused, that value is None and the original engine behavior remains unchanged.

The same pattern is used for country approval windows.

That gives the system an important property:

new operational capability
          ≠
rewrite calculation engine

Regression tests prove that the new seams are no-ops when not supplied.

The golden reference remains intact.

Being honest about missing data

One of my favorite decisions in this system is something the UI does not calculate.

ClinSupplyCompass can measure forecast accuracy only when the corresponding actual facts exist.

If dispensing-demand actuals have not been loaded, the system does not quietly turn missing observations into zero and publish a misleading demand-accuracy percentage.

It reports the metric as:

unavailable

and explains why.

That sounds like a small implementation detail.

I consider it a core engineering requirement for analytical software.

A polished KPI based on invented evidence is worse than no KPI.

Multi-tenant trust

As the application became SaaS, isolation also had to become more than an application convention.

Tenant boundaries are enforced twice.

The application scopes every operation to an organization.

PostgreSQL Row Level Security independently enforces the same boundary using the request’s organization context.

Authenticated request
        │
        ▼
Application authorization
        │
        ▼
organization-scoped query
        │
        ▼
PostgreSQL RLS
        │
        ▼
tenant data

Cross-organization access therefore has to defeat two separate controls rather than finding one forgotten query.

The system also deliberately returns 404 for several protected operational surfaces rather than advertising their existence to unauthorized users.

The outcome

ClinSupplyCompass became more than a calculation service without turning the calculation engine into a monolith.

The same deterministic core now supports persistent studies, versioning, approved baselines, actuals, re-planning, supply recommendations, KPI snapshots, forecast history, audit trails, multi-tenant authorization, dashboards, and AI-assisted explanation.

The engine did not have to own any of those concerns.

That was the architectural win.

---

Where AI Fits

ClinSupplyCompass does use AI, but I deliberately kept it away from the calculation authority.

AI Study Pulse and organization-level Ask AI operate over data already produced or authorized by the application.

They explain.

They summarize.

They help a planner interrogate what the system knows.

They do not manufacture the underlying planning numbers.

Planning Engine
      │
      ▼
verified business facts
      │
      ▼
authorized context assembly
      │
      ▼
LLM
      │
      ▼
business-language explanation

The distinction matters.

For a regulated or operational planning system, I want deterministic software producing the numbers and probabilistic software helping humans understand them.

---

Why I Built It This Way

ClinSupplyCompass could have started as a collection of APIs, services, queues, data pipelines, vector stores, and infrastructure.

I started with the hardest question instead:

Can I prove the business calculation?

Only after that answer was yes did I build outward.

That led to a fairly deliberate architecture.

The planning engine is pure Python because calculations should be deterministic and independently testable.

PostgreSQL owns durable business state.

FastAPI and the web layer orchestrate workflows around the engine rather than becoming part of it.

RLS reinforces application authorization.

Historical planning evidence is append-only where provenance matters.

AI sits above verified facts instead of underneath them.

And Excel remains a reference artifact rather than an embarrassment to be discarded.

The system still has work ahead of it. Lot-level expiry and FEFO behavior, deeper distribution planning, richer retention models, and later optimization capabilities belong further down the roadmap.

I prefer that visible boundary.

Architecture should show not only what a system can do, but also what it cannot yet do.

For me, ClinSupplyCompass is less a story about replacing Excel than about extracting durable engineering structure from something the business already knew worked, and then proving, one boundary at a time, that the new system deserved to be trusted.