ADR-177: Database-Driven Financial Model Engine

Status

Proposed — 2026-02-12

Context

The CODITECT financial model has evolved through 4 iterations (v2.0 → v2.1 → v3.0 → v3.1), each requiring direct Python script modifications to change assumptions, add funding rounds, or adjust scenarios. Key problems:

Assumptions are scattered — pricing lives in JSON, growth rates are hardcoded in Python dicts, funding is parsed from CLI strings or overridden in code
No persistence — each run starts from scratch; there's no history of what-if scenarios or assumption changes over time
Tight coupling — the calculation engine, data loading, and output formatting are interleaved in a single 1600+ line script
No reusability — the model is CODITECT-specific; the same SaaS financial modeling patterns apply to any startup but can't be reused
Formula model is separate — the --formula mode duplicates calculation logic as Excel formula strings, diverging from the Python engine

The v3.1 CLI flags (--funding, --formula) improved ergonomics but didn't solve the architectural issues. A general-purpose solution requires separating data, computation, and presentation.

Decision

Build a database-driven financial model engine with three cleanly separated layers:

Architecture

┌─────────────────────────────────────────────────────────┐
│                    CLI / API Layer                        │
│  coditect-fm add-round seed 2M M6                       │
│  coditect-fm set-growth M1-M3 1.0                       │
│  coditect-fm scenario create "aggressive-seed"          │
│  coditect-fm build --format xlsx --scenario base        │
│  coditect-fm build --format formula                     │
│  coditect-fm compare scenario-A scenario-B              │
└────────────────────┬────────────────────────────────────┘
                     │
┌────────────────────▼────────────────────────────────────┐
│              Calculation Engine (Python)                  │
│  - 60-month SaaS model: customers, revenue, expenses    │
│  - Working capital, cash flow, funding tranches          │
│  - Unit economics: CAC, LTV, NRR, Quick Ratio           │
│  - Valuation: DCF, revenue multiples, dilution           │
│  - All reads from DB, all writes to output formatter     │
└────────────────────┬────────────────────────────────────┘
                     │
┌────────────────────▼────────────────────────────────────┐
│              Output Formatters (Pluggable)                │
│  StaticXlsxFormatter  → baked-value 13-sheet workbook   │
│  FormulaXlsxFormatter → live-formula Excel model        │
│  JsonFormatter        → companion JSON export            │
│  CsvFormatter         → flat CSV for BI tools            │
│  DashboardFormatter   → HTML/React dashboard data        │
└────────────────────┬────────────────────────────────────┘
                     │
┌────────────────────▼────────────────────────────────────┐
│              Database Layer (SQLite)                      │
│                                                          │
│  scenarios        → named scenario configurations        │
│  assumptions      → pricing, growth, churn, expenses     │
│  funding_rounds   → name, amount, month, tranche split   │
│  headcount_plans  → department, period, count, salary    │
│  computed_months  → cached 60-month output per scenario  │
│  comparables      → peer company benchmarks              │
│  scenario_history → audit trail of assumption changes    │
└──────────────────────────────────────────────────────────┘

Database Schema

-- Core assumptions (one row per scenario)
CREATE TABLE scenarios (
    id          TEXT PRIMARY KEY,      -- e.g., 'base', 'aggressive-seed'
    name        TEXT NOT NULL,
    description TEXT,
    start_date  TEXT NOT NULL,         -- ISO 8601
    months      INTEGER DEFAULT 60,
    created_at  TEXT NOT NULL,
    updated_at  TEXT NOT NULL
);

-- Pricing tiers
CREATE TABLE pricing (
    scenario_id   TEXT REFERENCES scenarios(id),
    tier          TEXT NOT NULL,        -- 'individual', 'team', 'enterprise'
    monthly_price REAL NOT NULL,
    cac           REAL NOT NULL,
    annual_churn  REAL NOT NULL,
    mix_pct       REAL NOT NULL,        -- 0.60, 0.30, 0.10
    PRIMARY KEY (scenario_id, tier)
);

-- Growth rates by period
CREATE TABLE growth_rates (
    scenario_id TEXT REFERENCES scenarios(id),
    month_start INTEGER NOT NULL,
    month_end   INTEGER NOT NULL,
    rate        REAL NOT NULL,          -- monthly growth rate
    PRIMARY KEY (scenario_id, month_start)
);

-- Funding rounds
CREATE TABLE funding_rounds (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    scenario_id TEXT REFERENCES scenarios(id),
    name        TEXT NOT NULL,          -- 'seed', 'series_a'
    amount      REAL NOT NULL,
    month       INTEGER NOT NULL,
    tranche_1   REAL DEFAULT 0.70,
    tranche_2   REAL DEFAULT 0.20,
    tranche_3   REAL DEFAULT 0.10
);

-- Expense ratios by stage
CREATE TABLE expense_ratios (
    scenario_id TEXT REFERENCES scenarios(id),
    category    TEXT NOT NULL,          -- 'cogs', 'marketing', 'rd', 'ga', 'cloud'
    stage       TEXT NOT NULL,          -- 'pre_revenue', 'growth', 'scale'
    ratio       REAL NOT NULL,
    PRIMARY KEY (scenario_id, category, stage)
);

-- Headcount plan
CREATE TABLE headcount (
    scenario_id    TEXT REFERENCES scenarios(id),
    period_start   INTEGER NOT NULL,
    period_end     INTEGER NOT NULL,
    count          INTEGER NOT NULL,
    avg_salary     REAL DEFAULT 12000,
    PRIMARY KEY (scenario_id, period_start)
);

-- Working capital assumptions
CREATE TABLE working_capital (
    scenario_id TEXT REFERENCES scenarios(id),
    parameter   TEXT NOT NULL,          -- 'dso_individual', 'dso_team', 'dso_enterprise', 'dpo'
    value       REAL NOT NULL,
    PRIMARY KEY (scenario_id, parameter)
);

-- Comparable companies
CREATE TABLE comparables (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    name     TEXT NOT NULL,
    arr      REAL,
    revenue_growth REAL,
    nrr      REAL,
    gross_margin   REAL,
    rule_of_40     REAL,
    ev_revenue     REAL,
    updated_at     TEXT
);

-- Cached computed output (invalidated on assumption change)
CREATE TABLE computed_months (
    scenario_id TEXT REFERENCES scenarios(id),
    month       INTEGER NOT NULL,
    data_json   TEXT NOT NULL,          -- full month row as JSON
    computed_at TEXT NOT NULL,
    PRIMARY KEY (scenario_id, month)
);

-- Audit trail
CREATE TABLE scenario_history (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    scenario_id TEXT REFERENCES scenarios(id),
    changed_at  TEXT NOT NULL,
    table_name  TEXT NOT NULL,
    change_type TEXT NOT NULL,          -- 'insert', 'update', 'delete'
    old_value   TEXT,                   -- JSON
    new_value   TEXT,                   -- JSON
    changed_by  TEXT DEFAULT 'cli'
);

CLI Design

# Scenario management
coditect-fm scenario list
coditect-fm scenario create "base" --start-date 2026-02-12 --months 60
coditect-fm scenario clone "base" "aggressive"
coditect-fm scenario delete "old-scenario"

# Assumptions (CRUD)
coditect-fm pricing set base --tier individual --price 29 --cac 50 --churn 0.333 --mix 0.60
coditect-fm growth set base --range M1-M3 --rate 1.0
coditect-fm growth set base --range M4-M6 --rate 0.50
coditect-fm headcount set base --period M0-M5 --count 8 --salary 12000

# Funding rounds
coditect-fm funding add base seed 2M M6
coditect-fm funding add base series-a 15M M18 --tranches 0.70 0.20 0.10
coditect-fm funding remove base seed
coditect-fm funding list base

# Build outputs
coditect-fm build base                          # default: static XLSX + JSON
coditect-fm build base --format formula         # formula-based XLSX
coditect-fm build base --format csv             # flat CSV
coditect-fm build base --format all             # all formats

# Compare scenarios
coditect-fm compare base aggressive --metrics arr,cash,nrr

# Import/export
coditect-fm import base assumptions.json        # bulk import
coditect-fm export base --format json           # export assumptions
coditect-fm seed-defaults base                  # load CODITECT defaults

Key Design Principles

Database is SSOT — all assumptions live in SQLite, never in Python dicts or JSON files
Computed cache with invalidation — computed_months caches results; any assumption change clears the cache for that scenario
Audit trail — every change is logged with old/new values via SQLite triggers
Pluggable formatters — adding a new output format means implementing one class with a render(scenario_id, computed_months) method
Scenario isolation — each scenario is fully independent; clone to create variants
Import/export — bulk operations via JSON for portability and backup
Reusable — the engine works for any SaaS startup; CODITECT-specific data is just seed data

Consequences

Positive

One command to model any scenario — coditect-fm funding add base seed 2M M6 && coditect-fm build base
Persistent history — see how assumptions evolved over time
Scenario comparison — side-by-side analysis without generating multiple files
Formula model stays in sync — both static and formula outputs read from the same DB
Extensible — add Monte Carlo (N.6.8.1) as a formatter that samples from assumption distributions
Investor-ready — quickly generate models for different pitch scenarios (bootstrapped, seed, series A)
Foundation for web dashboard — DB layer can back a future React/Next.js dashboard

Negative

Migration effort — existing v3.1 script (1600+ lines) needs refactoring
Additional dependency — SQLite (already used extensively in CODITECT)
Learning curve — CLI commands instead of direct script editing
Over-engineering risk — if only used for CODITECT, the generalization may not pay off

Mitigations

Keep v3.1 script working alongside the new engine during transition
Use the same SQLite patterns already established in CODITECT (ADR-118)
Seed defaults from existing CODITECT_Model_Export.json
Build incrementally: DB + CLI first, formatters second

Alternatives Considered

Keep improving the script — rejected; the script is at complexity limits and can't scale
Use a spreadsheet as the database — rejected; no audit trail, no CLI, no programmatic access
PostgreSQL — rejected; SQLite is sufficient and portable, no server needed
Full web app — deferred; the CLI+DB foundation enables this later without rework

Implementation Plan

See TRACK-N Phase N.6.10 for detailed task breakdown.

Estimated effort: 40 hours across 5 sub-phases

References

Predecessor: build_financial_model_v2.py (v3.1, 1600+ lines)
Database patterns: ADR-118 (Database Architecture)
Financial model analysis: internal/analysis/financial-model/financial-model-v2-planning-analysis-2026-02-11.md
Track: N.6.10 in TRACK-N-GTM-LAUNCH.md

Decision Date: 2026-02-12 Decision Maker: Hal Casteel Author: Claude (Opus 4.6)

Status​

Context​

Decision​

Architecture​

Database Schema​

CLI Design​

Key Design Principles​

Consequences​

Positive​

Negative​

Mitigations​

Alternatives Considered​

Implementation Plan​

References​