Data Pipeline & Design Console — Plan¶

Document Type: Technical Plan Version: 0.2 Status: Partly built, and not the way this plan first described Last Updated: 2026-08-31 Vault destination: 02_Technical Documents Related: [04_Factory & Resource System Design](<../01_Design Documents/Systems/04_Factory & Resource System Design.md>) · [08_Research Tree Design](<../01_Design Documents/Content/08_Research Tree Design.md>) · [01_Technical Architecture Document](<./01_Technical Architecture Document.md>)

1. The Problem¶

Buildings, items and recipes link everything together. Once ~47 buildings and their recipes exist, the chain becomes impossible to hold in your head or verify by hand.

Recipe data is currently scattered across 04_Factory & Resource System Design and 08_Research Tree Design, with no single place to check consistency.

2. Core Principle¶

Never draw the production chain by hand.

A hand-drawn diagram desyncs from the data the first time a recipe changes, leaving two sources of truth and no way to tell which is right.

The graph must be generated from the data, every time.

This rules out Obsidian Canvas, Miro, draw.io, and plain markdown as the source. They are fine as outputs, never as the master.

3. Architecture — `.tres` Is The Source¶

⚠️ Superseded 2026-08-31. This section originally put SQLite at the top as the single source of truth, with .tres as one of four exports. That is not what was built, and it is no longer the intent. The original diagram is kept below the current one, because the reasoning that produced it is still worth reading.

              ┌────────────────────────┐
              │  data/**/*.tres        │  ← single source of truth
              │  hand-authored         │     versioned, diffable, Godot-native
              └───────────┬────────────┘
                          │  scripts/build-data.js
        ┌─────────────────┼──────────────────┐
        ▼                 ▼                  ▼
  docs/data/*.json   Substances page    (validator — not built)
  (visualiser)       (vault wiki)        see section 6

              ┌────────────────────────┐
              │  docs/data/            │  global scalars that belong to
              │  power-tuning.json     │  no single building. Hand-authored.
              └────────────────────────┘

What decided it. .tres was already the source the game reads, already versioned, already diffable, and already had the Godot inspector for free. A database would have had to earn its place by displacing all four of those, and the thing it was going to buy — foreign-key validation — turned out not to need it (§6).

The split, stated once:

Data	Lives in	Authored
Per-building values — power draw, footprint, category, capacity	`data/buildings/*.tres`	By hand
Per-resource values — substance, category, type, phase	`data/resources/*.tres`	By hand
Recipes	`data/recipes/*.tres`	By hand
Global scalars that belong to no single building	`docs/data/power-tuning.json`	By hand
Everything a browser or the wiki needs	`docs/data/*.json`, `05_Reference/09_Substances.md`	Generated. Never hand-edited

Per-building values are properties of the building and belong beside its footprint and recipes. Pulling them into a global tuning file would separate them from everything that gives them meaning. Global scalars are the opposite case and get their own file.

The original SQLite-first architecture, superseded

                    ┌──────────────┐
                    │   SQLite     │   ← single source of truth
                    │  (design DB) │      authored offline
                    └──────┬───────┘
                           │  export scripts
        ┌──────────┬───────┴────────┬─────────────┐
        ▼          ▼                ▼             ▼
    .tres       JSON            Markdown      In-game
  resources  (visualiser)      (vault wiki)  recipe browser
   (game)                                    (from .tres)

**Why SQL over a spreadsheet:** foreign keys catch "this recipe references an item that does not exist" automatically. That class of error is invisible in a spreadsheet and painful to find later. Constraints do validation work that would otherwise be written by hand. **Why this was dropped:** it would have made `.tres` a *generated* artefact, inverting a pipeline that already worked in the other direction. §6 is the reason it was proposed, and §6 is now reachable without it.

4. Why `.tres` Ships, And Nothing Else Would¶

The game reads .tres resources. Nothing else is loaded at runtime.

This section was originally "The Database Does NOT Ship", arguing that SQLite must stay a design-time tool. The argument outlived the database. Every row below is a reason .tres is the right runtime format, and together they are most of why §3 eventually put .tres at the top rather than one step down from it. The first row is the load-bearing one and is unaffected by dropping the database.

Reason	Detail
Desync risk ⚠️	The architecture is authoritative host networking. Every client must hold byte-identical recipe data. Baked resources guarantee that. A database that can differ between machines, or be edited at runtime, is a deliberate desync vector.
Native dependency	SQLite in Godot needs the `godot-sqlite` GDExtension, with export templates per platform
Version control	A binary file produces useless git diffs. Design data is currently versioned and readable.
Editor integration	`.tres` gives the resource picker and inspector for free. A database gives neither.
No runtime need	A few hundred recipes loaded once at startup is a dictionary, not a database problem.

5. Schema Sketch — kept as a shape, not as a plan¶

No database is being built (§3), so this is no longer a schema to implement. It is kept because it is still an accurate description of what relates to what, and the validator in §6 has to walk exactly these relationships whatever it reads them from. Read table as the set of .tres files, and join table as a field naming ids in another set.

Three core entities plus the links between them:

items — id, display name, tier, stack size, icon, description
buildings — id, display name, footprint w/h, category, base power draw, base Mk range, machine Mk range
recipes — id, building id, required machine Mk, craft time, power draw
recipe_inputs — recipe id, item id, quantity
recipe_outputs — recipe id, item id, quantity
research_nodes — id, branch, credit cost, prerequisites
research_unlocks — node id, recipe id or building id

Detail to be worked out at implementation time.

6. Validation — The Real Value¶

Errors that are invisible in a spreadsheet and obvious in a graph:

Items with no producer (unobtainable)
Items nothing consumes (dead end)
Circular dependencies in the chain
Buildings with no recipe
Recipes referencing items that do not exist
Recipes requiring a Mk level the building cannot reach
Recipes unlocked by no research node (unreachable)
Win-critical recipes behind optional research branches (see 25_Power System Design §8a.3 — this must never happen)

6a. Seven of these eight need no database¶

Checked 2026-08-31, and it is the finding that changed §3. scripts/build-data.js already parses every .tres into plain JavaScript objects, so the graph they describe is already in memory. Every check above except one is a query over that graph, and none of them needs foreign keys, constraints or SQL.

Check	Needs
No producer · nothing consumes · circular · building with no recipe · missing item reference · unreachable Mk	The existing parse. Nothing more
Recipes unlocked by no research node	The research tree as data, which does not exist — `[08_Research Tree Design](<../01_Design Documents/Content/08_Research Tree Design.md>)` is prose
Win-critical recipe behind an optional branch	The same research data, plus the win path

The last one was verified by hand on 2026-08-30 while closing OI-081, and it held. A validator would make that permanent rather than a check someone remembered to do once.

The research tree is therefore the real blocker on full validation, not the absence of a database. Making it machine-readable is a separate and larger question than this plan, and turning ~90 prose nodes into data should not be done casually — the prose currently carries the reasoning alongside the structure, and that is worth more than the two checks it would buy.

7. The Wiki — Nearly Free¶

Game wikis go stale because they are maintained separately from the game. This one cannot, because both are generated from the same rows.

Obsidian trick: if the generator writes one markdown file per item and building into the vault, using [[wikilinks]] for every input and output, then Obsidian's own graph view visualises the production chain with zero additional work.

Limitations: Obsidian's graph is undirected and clusters oddly, so it will not replace a proper directed graph eventually. But it is immediate and costs nothing.

The same generated pages become the in-game recipe browser content, and the basis of any public wiki later.

8. Console — Staged, and NOT UI-First¶

Do not build the editing UI first. CRUD screens are the most work and the least value here. DB Browser for SQLite already edits tables perfectly well, for free.

What cannot be bought off the shelf is the validation and the exports.

Stage	Contents	Priority	Status
1	~~Schema, seed data~~, validator, exporters. No UI at all.	Highest value	Exporters done. Validator is what remains — OI-136
2	Read-only console: browse, visualise the chain, run validation, see balance ratios	High	Not started — OI-137
3	~~Editing UI — only if DB Browser proves annoying~~	—	Dropped. There is no database to browse; `.tres` is edited in Godot or a text editor
4	Wiki generation into the vault	Medium	Partly done. `09_Substances.md` is generated; buildings and recipes are not — OI-139

Stack: Node, already in use for scripts/generate-icons.js and scripts/build-data.js. No better-sqlite3, and no framework — the validator runs against the parse that build-data.js already produces.

Stage 1 shrank rather than grew. Schema and seed data are gone with the database, and both exporters exist. What is left is the validator, which is the part §6 calls the real value and the only part that was never buyable off the shelf.

9. Separate Tool vs In-Game — Both, Unequally¶

Tool	Where	Why
Chain visualiser and validator	Standalone web tool	A design-time activity. Wants to be open beside Obsidian, not inside a running game.
Recipe inspection while debugging	Extend the existing F3 debug overlay	Show a selected building's recipe, its inputs, and where they come from. An addition to existing tooling, not new software.

10. Balance Work This Enables¶

The arithmetic that is currently impossible by hand:

How many Smelters feed one Assembly Machine at a given rate
Total power draw of a chain running at N items per minute
Where ratios come out non-integer and irritate players
Whether a Mk3 building's superlinear power draw (25_Power System Design §8a.1) actually balances

11. Status¶

Partly built, and not the way this plan first described.

Built	Where
`.tres` → JSON exporter	`scripts/build-data.js` → `docs/data/buildings.json`
`.tres` → wiki exporter	`scripts/build-data.js` → `05_Reference/09_Substances.md`
Global scalars, hand-authored	`docs/data/power-tuning.json`
A first consumer of the JSON	`05_Reference/07_Power Explorer.html`
One ad-hoc check	`build-data.js` warns on generators shipping `power_output = 0`

Not built: the validator (§6), the console (§8 stage 2), building and recipe wiki pages.

Not going to be built: the SQLite database, and the editing UI that existed only to edit it.

The one warning build-data.js already emits is the shape the validator should take: it runs on every build, names the file, and points at the open item that owns the fix. Eight more checks of that kind is the whole of what remains in stage 1.

12. Revision History¶

Version	Date	Changes
0.1	2026-08-25	Initial plan. SQLite as the single source of truth, exporting to `.tres`, JSON and markdown.
0.2	2026-08-31	`.tres` recorded as the source of truth; the SQLite database dropped. §3 rewritten, with the original architecture kept collapsed beneath it because its reasoning is still worth reading. The database was proposed for foreign-key validation, and §6a records the finding that changed it: seven of §6's eight checks need no database, since `build-data.js` already parses every `.tres` into memory. The eighth needs the research tree as data, which does not exist and is a larger question than this plan. §5 kept as a description of what relates to what rather than a schema to implement. §8 restaged: stage 1 shrank to the validator alone, stage 3 dropped with the database, stages 1 and 4 marked partly done. §11 replaced — "Plan only, nothing built" had been false since the exporters landed.