Data model

In simple terms

The app remembers your work in two places: a small database file (SQLite) for structured records — your projects, their assumptions, saved snapshots, the site library — and Parquet files on disk for the big hourly timeseries (weather curves and feasibility results), which are too large and too numeric to sit in a database row. The guiding rule is keep the inputs, rebuild the derived: the app stores what you entered and what it solved, and regenerates everything else (Excel exports, charts, reports) on demand.

Why it’s split this way

• Structured, small, queried often → SQLite. Projects, assumptions, snapshots. One local file, no server, perfect for a single-user tool.
• Large, numeric, streamed → Parquet. An 8,760-hour dispatch result is ~20 float columns × 8,760 rows. Parquet (columnar, compressed) reads fast and stays small (~700 KB), where JSON-in-a-row would be wasteful and slow.
• Derived artefacts → nothing. Excel workbooks, the HTML one-pager, and the snapshot .zip are regenerated on request from the stored inputs + results, so they never drift and never need migrating.

Where it lives

• SQLite database: path from .env (PTX_DB_PATH), default under backend/data/.
• Parquet timeseries: backend/data/processed/site_datasets/<dataset_id>.parquet (weather curves) and backend/data/results/<project_id>/dispatch.parquet (feasibility results).
• Table definitions: backend/db/migrations/ (numbered steps; the stores run no DDL).

The tables (verified against backend/db/migrations/)

Workspaces and people — workspaces, users, workspace_members, sessions, invitations

workspaces:         workspace_id TEXT PK · name · created_at
                    · owner_user_id NOT NULL → users (the owner — a pointer, not a role)
users:              user_id TEXT PK · display_name · email (optional, unique, compared
                    lower-cased) · password_hash (salted scrypt, NULL = cannot sign in)
                    · password_changed_at · created_at · disabled
                    · version_seen (the release whose What's new notice was put away)
workspace_members:  (workspace_id, user_id) PK · role admin | editor | viewer · created_at
                    · added_by → users (who added them; NULL = not recorded)
                    · last_visit_at · seen_at · welcome_seen_at   (the projects home)
                    · joined_as_super_admin 0/1 (the super admin joined by themselves)
super_admins:       user_id TEXT PK → users · granted_at · granted_by setup | migration
                    | machine   (who may manage EVERY workspace — since 2026-11-09)
workspace_events:   event_id PK · workspace_id → workspaces ON DELETE CASCADE
                    · kind super_admin_joined | _left | _renamed | _transferred
                    · actor_id → users · role (a join) · detail_json (old/new name or
                    owner) · at
recent_pages:       (workspace_id, user_id, project_id) PK · path · visited_at
                    (workspace_id, user_id) → workspace_members ON DELETE CASCADE
                    (project_id, workspace_id) → projects ON DELETE CASCADE
sessions:           token_hash TEXT PK (SHA-256 of the cookie's token) · user_id
                    · workspace_id · created_at · expires_at · last_seen_at
                    (workspace_id, user_id) → workspace_members ON DELETE CASCADE
invitations:        token_hash TEXT PK (SHA-256 of a one-time link's token)
                    · invitation_id (unique, not secret — how a link is listed and
                    withdrawn) · workspace_id → workspaces ON DELETE CASCADE
                    · user_id → users (the account the link activates) · email
                    · role admin | editor | viewer · created_by · created_at
                    · expires_at (7 days) · accepted_at (NULL until used — once)

The workspace is the tenant boundary: every row of project data belongs to one. A person belongs to workspaces through workspace_members, never through a column on users. A local install starts with one workspace (“Local workspace”) whose one admin is the “Local user”; the first-run set-up gives that user a name, an e-mail and a password, so every row it created stays its own.

Who acts, where: a request carries a session cookie; the server turns it into a principal — user, workspace, role — and every store call passes that workspace (a required argument; backend/db/workspace_context.py holds the principal of the request in progress and raises when there is none). A row of another workspace is simply not there: reads filter on workspace_id, and an id from another workspace answers 404, the same as an id that exists nowhere. The role matrix (who may edit, run, delete) lives in backend/core/access.py.

Users are never deleted, only disabled. Every row names its creator (created_by, a key without ON DELETE), so the database refuses a delete that would orphan that provenance; disabling ends the user’s sessions and sign-in. A session row exists only for a member: removing a membership removes its sessions.

The owner and the members (since 2026-11-04). Each workspace points at its owner (owner_user_id, backfilled with each workspace’s oldest admin — the local user for the default workspace): the one admin who cannot be demoted or removed and the only one who may rename the workspace or hand it on. A member is added by e-mail: an account that can sign in joins at once; anyone else gets an account with no password and a row in invitations — a one-time link whose token is shown to the admin once and stored only as its SHA-256. Using it sets that account’s first password (never one it already has), marks the link accepted and adds the membership, all in one transaction. The rules that keep a workspace usable — the owner stays, there is always an enabled admin — are written into the guarded UPDATE/DELETE statements themselves (SQLiteMemberStore), so two admins acting at once cannot race past them.

The super admin, and deleting a workspace (decided on 2026-11-08, in the app since 2026-11-09). super_admins names the accounts that may see and manage every workspace: the one that set the install up (marked by migration 0011, or by the first-run set-up itself), and any other named from the machine with backend/scripts/super_admin.py — no route writes the table. The super admin reads every workspace’s metadata (name, owner, member and project counts, dates) and opens a workspace’s projects only by joining it: the membership is marked joined_as_super_admin, and each act — joined, left, renamed the workspace, handed it on — is written to workspace_events in the same transaction, so its members are always told, even after the super admin has left. A workspace is deleted by its owner or the super admin, in this order: a backup of the whole database (SQLite backup API, PTX_BACKUP_DIR, checked) → the workspace’s sessions end → each project through the project delete cascade → in one transaction its sites and datasets, the rows a migration set aside for it, and the workspace row, whose delete cascades to its members, invitations, default-book versions, sessions and events → its files (its default-book folder, its datasets’ caches). Users stay; a member left with no workspace cannot sign in until added to one.

Edited by. Versions (book and sizing), snapshots and projects name who made them (created_by); the lists join users.display_name for the name. Saving into a version makes the saver its author; a stage seeded from another keeps each version’s author with its name and date.

What a member has seen (the projects home, since 2026-11-06). Each membership keeps when its owner’s previous visit to the projects home ended (last_visit_at — what “Since your last visit” counts from; a session’s last_seen_at cannot answer it, it moves with every request) and when the home was last open (seen_at): a return within half an hour is the same visit, a later one starts a new visit whose “previous” is the last seen_at. welcome_seen_at records that the one-time welcome was closed in that workspace — the owner’s is set when the workspace is made (or at the upgrade), so only people who were added see it. added_by names who added the member: the maker of the invitation link they used, or the admin who added an existing account (members added directly before the upgrade read “not recorded”). recent_pages keeps, per person and project, the last page of it they opened — what “Continue where you left off” reopens; it goes with the membership and with the project.

What an account has read (the What’s new notice). users.version_seen is the app version whose What’s new notice the person put away (Got it) — per account, not per membership: what changed in the app is the same in every workspace. The projects home shows the notice while the stored version is older than the running one (NULL: nothing put away yet), so each release shows it once; a later version already stored is kept.

projects — one row per project

project_id   TEXT PRIMARY KEY    -- server-generated, filesystem-safe id
name         TEXT NOT NULL
dab_version  TEXT                -- which default-book version seeded this project
created_at   TEXT
updated_at   TEXT
config_json  TEXT NOT NULL       -- the full ProjectConfig (sizing, derivatives, site refs, mode)
workspace_id TEXT NOT NULL       -- → workspaces; UNIQUE (project_id, workspace_id)
created_by   TEXT                -- → users
updated_by   TEXT                -- → users: who made the change that last moved updated_at

updated_at / updated_by move with every edit of the project — a design saved in either stage, an assumption changed, a book version switched in or brought across (since 2026-11-06; before it, an assumption edit did not count). Rows from before the upgrade name the creator only where the project was never changed after its creation; otherwise ”—”.

The config_json blob is the project’s live sizing/topology state (ProjectConfig, sections S1–S9). config_from_record rebuilds a ProjectConfig from it on every solve. workspace_id + created_by replaced the old user_id/org_id columns, which were never enforced (always local): a project lands in the current workspace, created by the current user, and a re-save never moves it. The pair (project_id, workspace_id) is the key every child row points at.

assumption_values — the per-project assumption book (the working book)

project_id TEXT NOT NULL
param_key  TEXT NOT NULL         -- e.g. "capex_electrolyser_usd_per_kw"
value      REAL
unit       TEXT
source     TEXT                  -- dab | user | (future: quote | contract)
updated_at TEXT
note       TEXT                  -- free-text caveat; never affects a solve
sources_json / consolidation_json -- B6 candidate sources + how they resolve
project_ref  TEXT NOT NULL       -- the bare project id of the slot
workspace_id TEXT NOT NULL       -- (project_ref, workspace_id) → projects, ON DELETE CASCADE
created_by   TEXT                -- → users
PRIMARY KEY (project_id, param_key)

project_id here is a SLOT key, not always a bare project id: the bare id is the prefeasibility book, <project_id>@feasibility the feasibility stage’s own book (each stage owns its book). project_ref is the bare id the slot belongs to; a check in the database holds the two together.

This is the EAV (entity-attribute-value) form of the assumption book: one row per parameter, per project. It is book-canonical — every technical/financial number the engines read comes from here (via parameters/book.py). The EAV shape means adding a parameter never needs a schema migration. source records the provenance ladder (dab = inherited default, user = edited); note is commentary that round-trips through Excel and never dirties a solve.

snapshots — named, full results-history

snapshot_id      TEXT PRIMARY KEY
project_id       TEXT NOT NULL
name             TEXT
created_at       TEXT
assumptions_json TEXT NOT NULL   -- the book + config sizing at capture time
results_json     TEXT NOT NULL   -- the full solve bundle (result + financials)
workspace_id     TEXT NOT NULL   -- (project_id, workspace_id) → projects, ON DELETE CASCADE
created_by       TEXT            -- → users

A snapshot is a frozen, structured capture of a scenario: the assumptions, the sizing config, and the solved results. Excel/PDF/.zip artefacts are not stored — they are regenerated from these two JSON blobs on export. A feasibility snapshot also freezes a copy of the hourly frame on disk (dispatch_<snapshot_id>.parquet, see below). Restore writes the book + config back onto the project.

book_versions / sizing_versions — the two version axes

Both use the same table shape from one parametrized store (SQLiteBookVersionStore, table= → book_versions or sizing_versions):

version_id  TEXT PRIMARY KEY
project_id  TEXT NOT NULL
name        TEXT                 -- auto id: v1, v2, …
label       TEXT                 -- optional human label
created_at  TEXT
is_active   INTEGER DEFAULT 0    -- exactly one active per slot
values_json TEXT NOT NULL        -- a saved book (assumptions) OR sizing set
project_ref TEXT NOT NULL        -- the bare project id of the slot in project_id
workspace_id TEXT NOT NULL       -- (project_ref, workspace_id) → projects, ON DELETE CASCADE
created_by  TEXT                 -- → users

Like the book, both version tables are keyed by slot (project_id holds the bare id or <project_id>@<stage>): each stage has its own history.

Two independent axes: book versions = assumption sets; sizing versions = capacities + resource CFs + derivative list/shares. Same working/active/dirty/save/save-as/switch/delete lifecycle. A full scenario across both axes plus results is a snapshot.

stage_state — each later stage’s own design

project_id   TEXT NOT NULL · stage TEXT NOT NULL   -- PRIMARY KEY (project_id, stage)
config_json  TEXT        -- the stage's sizing config (same shape as projects.config_json)
lineage_json TEXT        -- what it was seeded / last pulled from
updated_at   TEXT
workspace_id TEXT NOT NULL  -- (project_id, workspace_id) → projects, ON DELETE CASCADE
created_by   TEXT           -- → users

Prefeasibility’s design stays in projects.config_json; feasibility keeps its own here, so the two stages can differ on purpose.

stage_digest — each stage’s last solve, in brief (since 2026-11-06)

project_id · stage           -- PRIMARY KEY (project_id, stage)
workspace_id TEXT NOT NULL   -- (project_id, workspace_id) → projects, ON DELETE CASCADE
engine                       -- annual | timeseries (prefeasibility) · rule-based | solver
main_kind · main_pathway     -- the main product: the largest hydrogen share, ties → first listed
cost · cost_unit             -- its levelized cost as its page shows it ($/t); NULL, never 0
capex_usd                    -- the plant's total CAPEX (the CAPEX panel's "Total CAPEX")
products_json                -- every product: cost, unit, production (t/yr), hydrogen share
design_json                  -- the capacities it was solved on
inputs_json                  -- fingerprints of the design and the book, the versions, the horizon
solved_at · solved_by → users · source  'solve' | 'backfill'

What the projects home lists and Compare sets side by side. A prefeasibility solve is computed on demand and never stored; this row is its only trace, written by an editor’s or an admin’s own solve of the stage (a viewer’s solve still stores nothing; neither does the feasibility card’s prefeasibility comparison). A feasibility solve keeps its whole result on disk as before (below) and repeats its headline here. The list compares the fingerprints with today’s design and book to say “design changed since”. At start-up the app fills the rows a database lacks, marked backfill and naming nobody: from a stored feasibility result’s own JSON sidecar (never its hourly frame), or by solving the stored prefeasibility design on its stored book. The rows are derived: they go with the project and never travel in a bundle.

dab_versions — the default assumption books, one set per workspace

dab_id       INTEGER PRIMARY KEY
workspace_id TEXT → workspaces    -- NULL ⇔ a book SHIPPED with the app (global, read-only)
version      TEXT NOT NULL        -- unique within its workspace; shipped ones are `shipped-<book>`
label        TEXT
file_path    TEXT                 -- the DAB Excel file holding the values
is_active    INTEGER (0|1)        -- per workspace: the book its new projects seed from;
                                  --   among the shipped rows: the CURRENT shipped book
created_at   TEXT
created_by   TEXT → users         -- NULL for every row from before 2026-11-08
CHECK  (workspace_id IS NULL) = (version starts with 'shipped-')
UNIQUE (workspace, version) · at most one active row per workspace (partial index)

Each workspace has its own default books (since 2026-11-08): its admins make, edit, activate and delete them, and no other workspace sees them — a name is unique only within its workspace, and another workspace’s name answers 404. The book shipped with the app (the one the committed backend/data/dab/ACTIVE marker names) is registered at start-up as a global, read-only row named shipped-<book>: nobody can change, import into or delete it, and it is what a workspace’s new projects seed from while the workspace has no active book of its own. New projects seed their assumption_values from their workspace’s active book (else the shipped one) and record its name in projects.dab_version. Existing projects never auto-update (they were frozen at create) — a manual diff-pull is the opt-in path (GET/POST /projects/{id}/book/dab-pull).

Files. The values live in the Excel file file_path names — a known coupling tracked in the internal known-gaps list (docs/08-maintenance/known-gaps.md). A shipped book’s file is in backend/data/dab/ (committed); a workspace’s own books are in <data root>/dab_workspaces/<workspace_id>/ (gitignored, owner-only files; stored as dab_workspaces/<workspace_id>/<file>). The app writes a default book only in the acting workspace’s own folder: a version whose file is elsewhere — every version registered before 2026-11-08 points at a shipped file — is copied there the first time it is changed, and a delete removes a file only from that folder.

Migration 0010 moved the one global registry: every existing row went to the default workspace local unchanged (the active one stays active); every other workspace received a row of its own for each version its projects (or their stages) record and for the version that was active — same label, same file — so every project’s dab_version still resolves in its own workspace and every workspace keeps seeding from the book it used.

login_failures — the sign-in throttle’s memory

key_hash     TEXT PRIMARY KEY   -- SHA-256 of "email:<address>" | "client:<address>" | "invite:<address>"
failures     INTEGER            -- failed attempts in a row
locked_until REAL               -- epoch seconds: no attempt before this
last_failure REAL               -- epoch seconds; a key quiet for 15 minutes is forgotten

Kept in the database since 2026-11-08, so restarting the server does not hand a guesser a fresh budget; only hashes are stored, never an e-mail or an address. Not workspace-scoped (failures are counted before anyone is signed in); bounded by the code to the most recent 10,000 keys.

sites / site_datasets — the reusable site library

sites:
  site_id  TEXT PK · name · country · lat · lon
  scope    TEXT NOT NULL                   -- global (shipped) | workspace
  workspace_id                             -- NULL ⇔ global (a check holds the pair)
  origin_site_id (fork lineage) · created_by · created_at

site_datasets:
  dataset_id TEXT PK · site_id · carrier ("wind" | "solar")
  label · meta · data_year · source
  series_path TEXT                         -- the series file (repo-relative)
  mean_cf     REAL                         -- cached annual-mean capacity factor
  years · degradation                      -- multi-year datasets
  confidential INTEGER                     -- 1 ⇒ no full-resolution output leaves
  workspace_id · owner_user_id · created_by
  created_at

A global site ships with the app; a workspace site belongs to one workspace. A dataset belongs where its site does — except a confidential series, which always carries the workspace and the user that own it, even under a global site: it is visible to its owner, the admins and the members of that workspace, and to nobody outside it. The database refuses a confidential dataset without an owner.

Two global sites ship with the app (backend/core/shipped_sites.py): the Coastal site (typical year) — site-global-coastal, datasets ds-global-coastal-wind|solar, one hourly year read from coastal_typical_year.xlsx — and the Reference site (30-year, synthetic) — site-global-reference, ds-global-reference-wind|solar, reference_30_year.xlsx. Neither names a place: their country and coordinates are empty. Earlier releases shipped both under a town’s name; migrations 0012 and 0014 renamed the rows, the ids, the series path and every design that links them. The Parquet cache of a series is named by its dataset id, so a new id builds its own on first use, from the same workbook; a result stored before the rename keeps the words it was made with and says the design changed since, until the stage is solved again.

A Site is a first-class, reusable, owner-scoped resource that owns per-carrier datasets (each an 8,760-hour CF curve). A project references (site, wind dataset, solar dataset) inside its config_json; the referenced dataset mean is what the engine solves on, resolved live so fixing a dataset propagates to every linked project. A per-carrier scalar override can win over the dataset mean.

The Parquet timeseries (not in SQLite)

File	What it holds
data/processed/site_datasets/<dataset_id>.parquet	A site dataset’s 8,760-hour CF curve, cached from its source Excel (mtime-invalidated).
data/results/<project_id>/dispatch.parquet	The latest feasibility solve’s hourly frame (~20 float32 series + a binding-constraint column).
data/results/<project_id>/dispatch.json	Sidecar for that solve: annual aggregates, duals, solver metadata — since 2026-11-06 including who solved it (meta.solved_by, left out of every export: a bundle names no person). Saved runs and Design-lab studies record who ran them (run_by) the same way.
data/results/<project_id>/dispatch_<snapshot_id>.parquet	A frozen copy of the frame at snapshot time.

The feasibility solve writes the frame once (SOLVE); every chart, duration curve, heatmap, and KPI is derived on read from it (POST-PROCESS). See the feasibility solve flow.

The storage rule: keep inputs, rebuild derived

• Stored: project config, assumption values, versions, snapshots (inputs + solved results), site datasets, the raw feasibility frame.
• Never stored (regenerated on demand): Excel workbooks, the HTML one-pager, the snapshot .zip bundle, all post-processed charts/curves/KPIs.

This keeps the on-disk footprint small and means a format change to an export is a code change, not a data migration.

Failure & edge cases

• Delete a project cascades: its snapshots, every stage’s assumption_values, both version axes, its stage rows, and its data/results/<project_id>/ files are removed (delete_project). The database enforces the row half itself: every child table points at its project with ON DELETE CASCADE, so a missed call can no longer leave a row behind. Files are deleted by their stores.
• A child row cannot name another workspace than its project’s. The composite key (child project, workspace_id) → projects(project_id, workspace_id) refuses it, whatever the code above it does.
• Rows left by projects deleted before the cascades were complete (old books and versions whose project row is gone) were not dropped by the migration that added the keys: they sit, whole and unchanged, in migration_orphans (source table, the full row as JSON, the reason). The app never reads that table.
• The DB and data/ tree are gitignored — this folder is the only copy. Back them up together (the DAB file coupling means DB-only backups lose default values).
• Migrations: the schema is versioned — backend/db/migrations/ holds numbered, idempotent steps, applied in order and recorded in a schema_version table (latest step: 14). The EAV book still avoids per-parameter migrations.

How to modify this safely

• New parameter? No schema change — it flows through assumption_values by key. Register it in the parameter registry (core/parameters/), not the database.
• New stored field on a project? It belongs inside config_json (ProjectConfig), not a new column, unless it must be queried across projects.
• New table? Add a store in backend/db/ following the existing pattern and a numbered step in backend/db/migrations/. If it holds project data it carries workspace_id NOT NULL + created_by and a composite key to its project with ON DELETE CASCADE — backend/tests/test_workspace_schema.py walks the schema and fails otherwise — and it joins the delete_project cascade and the project bundle (PROJECT_STORES). Its store methods take workspace_id as a REQUIRED keyword and filter on it: backend/tests/test_enforcement.py introspects every SQLite* store and fails on a method callable without its scope.
• New route? Add it to ROUTE_ACTIONS in backend/core/access.py with its action (the least role that may call it); a route missing from the table is refused at run time and fails test_enforcement.py.
• Rebuilding an existing table (SQLite cannot add a foreign key in place): the step declares FOREIGN_KEYS_OFF = True. The runner turns the pragma off between transactions, runs PRAGMA foreign_key_check before committing and refuses the step whole if a row breaks a key. With the pragma on, dropping a parent table would cascade-delete every child row.

Related topics

• System architecture
• Backend architecture
• Feasibility solve flow
• Known gaps — internal, docs/08-maintenance/known-gaps.md