Equilibrium data platform

Game data is built into a queryable local SQLite database rather than maintained as large hand-edited JSON:

data/canonical/*.jsonl          # tracked build input (reviewable mirror)
data/migrations/*.sql           # tracked schema
data/patches/*.jsonl            # tracked content ops
  -> scripts/data rebuild
  -> .cache/equilibrium.sqlite  # local only, never committed
  -> .generated/documents/      # #shard/* build inputs, never committed
  -> reports/data-*.json        # validation / inventory / parity, never committed
  -> docs/data-catalog.md       # generated catalog summary, never committed

There is no hosted database, API, or CMS. The site is static; user progress lives in localStorage. The build reads .cache/equilibrium.sqlite (and a few whole documents via the #shard/* alias). Nothing under public/data/ is shipped — that tree is removed on export when empty.

Source vs generated

Tracked source (authoring surface)

Exactly three roots under data/. scripts/data/audit.mjs fails if anything else appears there.

Path	Role
`data/canonical/`	Deterministic JSONL mirror of the validated database after patches — the only build input
`data/migrations/`	Forward-only SQLite schema (numbered `00N-*.sql`)
`data/patches/`	Immutable JSONL content operations against stable IDs
`data/README.md`	House rules for this tree

scripts/data/ implements the pipeline; it is code, not game data. Do not add a second authoring tree, hosted DB, CMS, or restored per-domain seed JSON.

Generated (local only — never commit)

Path / pattern	Role
`.cache/equilibrium.sqlite`	Built database; regenerate with `npm run data:rebuild`
`.cache/data-changed.json`	Last changed-entity set from apply/rebuild
`.generated/documents/**`	Source-shaped JSON for `#shard/*` imports (build inputs, not payloads)
`reports/data-.json`, `reports/data-.md`	Validation, quarantine, inventory, export manifest, audit
`reports/canonical-*.json`	Canonical structural + parity report
`docs/data-catalog.md`	Domain count summary rewritten by export
`public/data/v*`	Retired frontend shard tree — gitignored; export deletes if empty

.gitignore covers these. npm run audit:data / data:audit fails if generated reports, cache, or public/data/v* become tracked.

Why `data/canonical/` is tracked even though it is generated

Canonical is generated by export, but it is also the only dataset the rebuild ingests. It is committed so that:

Reviewability — a factual change appears as a readable JSONL diff next to its patch file.
Reproducible builds — CI and every clone rebuild the same SQLite without replaying a lost private seed. The compressed seed is retired (Git history only, commit 43c23873); provenance of its 56 documents lives under canonical/provenance/.
Staleness detection — data:canonical:validate byte-compares the tracked mirror to a fresh export from the database and fails while they disagree.

Hand-editing data/canonical/ is forbidden: the next export overwrites it, and the patch ledger no longer explains who changed what. Always patch → apply/rebuild → re-export → validate → commit patch + canonical together.

.gitattributes forces data/canonical/** to LF so Windows checkouts do not break parity.

Schema

A shared entity/source/region core plus domain tables for quests, tasks, training methods, equipment and stats, abilities, prayers, spells, invention perks, activities, unlocks, effects, requirements, relationships, map points, and the research catalog's region entries, skills and training links. Foreign keys, checks, uniqueness constraints, indexes and FTS5 enforce the common invariants. Rare source-specific fields stay in validated JSON columns; regions, sources, requirements, effects and relationships are also materialized relationally.

The research catalog is normalized into those tables and is never written back out as a catalog.json under public/.

Why `node:sqlite`

The implementation uses Node's built-in node:sqlite DatabaseSync. The build targets Node 22 or newer, where node:sqlite is available without an experimental flag and ships with foreign keys and FTS5 enabled. That avoids a native addon install during Windows development and Vercel builds. See the Node SQLite API.

Pipeline

scripts/data/ declares transforms — ingest, relational core, search, validate, export. Each records its version, dependencies, input hash, output count and validation contract in transform_runs.

A clean npm run data:rebuild:

Deletes only the ignored cache database.
Applies data/migrations/.
Imports data/canonical/ in one transaction.
Applies every data/patches/*.jsonl transactionally (content-hash identity; mutating an applied file is an error).
Rebuilds FTS search.
Validates invariants and writes reports.
Rebuilds .generated/documents/ and clears empty public/data/.

Module	Responsibility
`platform.mjs`	CLI entry point and command dispatch
`config.mjs`	Paths, limits, region taxonomy, transform declarations
`utilities.mjs`	Deterministic JSON, hashing, slugs, region taxonomy, atomic writes
`database.mjs`	Connections, transactions, statement cache, migrations
`ingest.mjs`	The ingestion entry: validate, read, insert, search index
`canonical/schema.mjs`	The one declaration of the canonical files and their fields
`canonical/read.mjs`	Canonical JSONL -> records, with declared defaults
`canonical/insert.mjs`	Records -> SQLite rows, in one ordered list of direct inserts
`canonical/validate.mjs`	Structural validation and the parity report
`canonical/export.mjs`	Database -> `data/canonical/`, byte-diffed
`patching/parse.mjs`	Patch file reading, limits, line numbers
`patching/validate.mjs`	Allowed and required fields per operation
`patching/operations.mjs`	One handler per operation, writing canonical columns
`patching/apply.mjs`	Patch identity, transaction, dispatch, ledger, changed entities
`validate.mjs`	Invariant checks and the validation/quarantine reports
`research.mjs`	Research catalog reconstruction, region panels, export parity
`export.mjs`	`#shard` documents, catalog, reports, empty `public/data` prune
`queries.mjs`	Bounded read commands: find, context, query, doctor, stats
`pipeline.mjs`	`rebuild` and single-patch `apply` sequencing
`benchmark.mjs`	Scoped patch and rebuild measurements
`audit.mjs`	Shipped-data gate and the architecture ratchets

See canonical-data.md for the file format itself.

Importing canonical data

ingest.mjs validates data/canonical/, reads it through canonical/read.mjs, and writes it into a freshly migrated database in one transaction. canonical/insert.mjs holds the whole import as a single ordered list, so the dependency order is one readable thing:

entities
sources
tags
regions — carries the region entity's own ID and name, so it follows entities
domain tables (equipment before equipment stats; tasks and quests reference regions)
entity-source links
entity-region links
requirements, then effects
relationships
entity-tag links
aliases and map points
provenance: source files, document skeletons, source records
the research catalog and its orderings
quarantine

Foreign keys are on throughout, so a step that ran too early fails on the row that needed the missing parent; PRAGMA foreign_key_check runs before the transaction commits. A rejected record names the file, line, record key and reason, and leaves no partially built database.

The importer does not infer an entity type from a filename, derive an ID from a name, search arbitrary key paths, accept a second spelling of a field, or classify anything by keyword. Everything it needs is a declared field.

Three columns are recomputed rather than stored twice: entities.slug and regions.entity_id from the ID, and entities.extra_json from the entity's provenance record. source_documents holds each source document's shape with its records removed, which is what lets .generated/documents/** be rebuilt from the database alone.

Applying a patch

patching/ splits the four things a patch does, so each is inspectable on its own:

parse reads the file, enforces the 1 MiB and 1,000-operation limits, and returns the operations exactly as written. It never mutates one — the content hash is the patch's identity, so the file and the applied operation have to say the same thing.
validate holds one table of the fields every operation accepts and requires, and returns a frozen validated copy with defaults applied, regions folded into the taxonomy and URLs normalized. Assignment keys are copied out of a fixed allowlist of column names, which is why the handlers can interpolate them into SET.
operations is one handler per operation. Each writes canonical database columns and returns the entity IDs it changed. Handlers own no transaction and no ledger. The set is upsert, set-record, upsert-source, link/unlink-region, link/unlink-source, relate/unrelate, remove, unlink-research-entry, and add/remove-requirement, add/remove-effect, add/remove-tag. Ordinals are the handler's job, not the author's: a requirement or effect appends after what the entity already has, and re-adding one it already carries is a no-op rather than a duplicate row.
apply owns identity, one transaction per file, dispatch, the changed-entity set, the patch_changes rows and the patch_ledger entry.

Most patches write database columns only. They do not rewrite source_records.raw_json by default: that column is what the source document said. The exception is set-record, which updates a provenance body (and optionally linked entity fields) when a reveal must change the source-shaped record itself — still as a ledgered patch, never a hand edit of data/canonical/.

Patch / migration / baseline workflow

npm run data:find -- --query "Seismic wand" --limit 20
npm run data:context -- --id item:seismic-wand --format markdown
npm run data:impact -- --id item:seismic-wand
# add one data/patches/YYYY-MM-DD-description.jsonl
npm run data:apply -- data/patches/YYYY-MM-DD-description.jsonl
npm run data:validate:changed
npm run data:export:changed
npm run data:diff

Schema or broad taxonomy work uses data:rebuild; normal record work does not.

data/canonical/ mirrors the database after patches, and every rebuild replays every patch, so a new patch is not lost — but the tracked mirror is stale until it is re-exported, and data:canonical:validate fails while it is. Commit the two together:

npm run data:canonical:export && npm run data:canonical:validate

Guard rails: a patch file is capped at 1 MiB and 1,000 operations and applies in a single transaction, so a rejected operation leaves nothing behind. An applied migration or patch whose content later changes is an error rather than a silent re-run. data:query accepts one bounded read-only SELECT or WITH and rejects writes, PRAGMA, attachment, DDL and multiple statements. data:context defaults to a 16 KB output ceiling and reports truncation.

How canonical data changes

The model is an immutable baseline plus ordered immutable patches:

data/canonical/  +  data/patches/*.jsonl  ->  .cache/equilibrium.sqlite  ->  data/canonical/

The loop closes: the database built from the baseline and the patches exports back to a baseline, and data:canonical:validate is what says the two agree. That gives four moves, and which one a change needs is not a judgement call.

Add a patch for any factual change to a record that already exists, or for a record that should: a corrected value, a new source, a region link, a duplicate to retire. This is the normal case and it is the only one that needs no full rebuild if the DB already exists. A patch is immutable once applied — a later correction is a new patch, never an edit to an old one.

Write a migration when the shape changes rather than the content: a new column, table, index or constraint. Migrations are forward-only and numbered, and an applied one whose bytes later change is an error. One migration per schema change, never one per content correction.

Regenerate the baseline — that is, re-export data/canonical/ and commit it — after every patch, because the tracked mirror is stale until you do and data:canonical:validate fails while it is. That is a re-export of what the database already says, not an authoring act, and it never rewrites a record: data:canonical:export writes only the files whose bytes changed. Nothing else may write these files by hand.

Squash the patch history only at a major data version. Replaying every patch on every rebuild costs nothing at this size, and the ledger is how data:context answers which patch changed a record and why. When the replay does become the slow part of a rebuild, or a patch names entities that no longer exist, fold the applied patches into a fresh baseline in one commit: export canonical, delete the folded patch files, rebuild from empty, and confirm the export is byte-identical to the one you started from. Until then, keep them.

The one thing not to do is rewrite the baseline to express an edit. Hand-editing data/canonical/ loses the record of who changed what and why, and the export would overwrite it on the next rebuild anyway.

Required validation commands

When	Commands
After a record patch	`data:apply` → `data:validate:changed` → `data:export:changed` → `data:canonical:export` → `data:canonical:validate`
Schema / pipeline / taxonomy change	`npm run data:rebuild` then `data:canonical:export` + `data:canonical:validate`
Before claiming data is shippable / on main push prep	`npm run audit:data` (`data:rebuild` + `data:audit` + `data:doctor`) and full app `npm run build` / `npm test` as appropriate
Spot-check structure only	`npm run data:canonical:validate` (needs a current local DB)
Bounded investigation	`data:find`, `data:context`, `data:impact`, `data:query`, `data:doctor`

npm run audit:data is the shipped-data gate: rebuild, architecture inventory (no tracked generated trees, no client path into huge documents, complete canonical set, no undocumented data/ roots, duplicate adjudication), then doctor.

Provenance guarantees

Every combat / league / research number carries source identity. Entities keep createdSource / updatedSource (seed path or patch:<file>). Citation rows live in sources + entity_sources (sources.jsonl, entity-sources.jsonl). App types such as SourceReference (src/combat/types.ts) are the TypeScript face of the same rule: never strip URL / family / verifiedAt when surfacing a value.
Source-shaped bodies are retained verbatim in provenance/source-records.jsonl, including records that never became entities. Entity columns reference them via recordRef (sourceFile#recordPath) instead of duplicating the body.
Patches do not silently rewrite provenance. Column patches leave source_records.raw_json alone. Only set-record changes a provenance body, and it still goes through the patch ledger.
Authority order (highest first) when sources disagree — see below. Authority picks the value; the winning record keeps its own source attribution.
Unresolved collisions stay in quarantine.jsonl, not merged away. The sixty stable-ID collisions are kept so the conflict remains auditable.
Never invent numbers to fill unrevealed League stubs. Empty records: [] is correct until a source exists. Never present a stale value as current.

Source authority

When two sources disagree about a value, this is the order, highest first:

Jagex / official League material — official League rules, reveals and the region taxonomy
RuneScape Wiki — general game-data ground truth
Project specialized research — where the project deliberately did work the Wiki does not cover
Project research overlays — snapshots and inference
Clearly labelled project inference — only where no authoritative source exists

Two rules constrain it. Do not replace a verified specialized record with a less-specific Wiki summary — rank 2 beating rank 3 is wrong when rank 3 is verified and more precise. And authority decides which value wins, never which source a surviving value is attributed to: the winning record keeps its own SourceReference.

Within a rank, the document that owns the domain wins — combat/equipment.json for equipment, combat/abilities.json for abilities — and a verified dated snapshot beats a general overlay.

Where the order does not settle it, leave the conflict. A record that needs a human to choose stays unresolved rather than being quietly picked; the sixty entries in quarantine.jsonl are exactly that, kept so the collision stays auditable instead of disappearing into a merge.

What counts as a duplicate

npm run audit:data fails on two live records of one entity type and name when they either come from different source documents or land on the same region page. The second case is the one nothing else catches: a document can duplicate a record on its own, and misthalin:explorers-ring and misthalin:area-tasks-explorers-ring share no ID for anything to key on.

Same name is not always the same record, though, and three shapes are excluded rather than reported:

A prayer that exists in two books. curse:dark-form and seren:dark-form are different prayers; merging them loses one.
A training method listed once per skill it trains. Merging empties a skill's method list.
A League task. Its identity is Jagex's wiki:N, not its label — "Defeat the empowered Barrows Brothers." is legitimately wiki:740 and wiki:741.

The rule is that a group told apart by a domain scope is not a duplicate. entityOverlaps in queries.mjs is where that lives, so a new scope of the same kind is one entry there.

Retiring the losing record is only half of it. Everything it holds that the survivor lacks — requirements, effects, region links, sources, tags — moves across first, each carrying the ID of the record it came from in its patch reason. That is what the requirement, effect and tag operations exist for, and npm run audit:data fails if two documents ever claim one domain again.

Frontend consumption

Server components and route handlers open .cache/equilibrium.sqlite read-only (for example src/research/catalog.ts and app/data/regions/). /data region routes export dynamic = "force-dynamic" (static string only; a NODE_ENV ternary is rejected by Next and 500s the route) and set Cache-Control: private, no-store. Client regionStore also uses cache: "no-store". No browser fetch goes to public/data/.

Content majors (authoring cookbook)

Regional majors on /data are catalog content[] faces authored via data/patches/*.jsonl (set-record is single-home per entity; dual-region sites need two entity IDs; put · Unlocks: … on the face for reward chips). Agent procedure: AGENTS.md section /data majors, skill .grok/skills/equilibrium-data-majors/ (gotchas under references/gotchas.md). Examples: data/patches/2026-08-05 … 2026-08-10 majors patches.

A small set of whole source documents is still needed as module imports. Export rebuilds only the documents something imports through #shard/* (plus a few path-loaded map seeds) into .generated/documents/. Those are build inputs, inlined by the bundler; they live outside public/ because no request ever asks for one. npm run audit:data walks imports transitively from every "use client" file and fails if a document over 250 KiB is reachable from the client.

data:export still writes a bookkeeping manifest under reports/ (record counts, document hashes, region index). Domain browser payloads are not chunked into public shards anymore; research region reconstruction still has to match between database and canonical files under data:canonical:validate.

FilesExpand file tree

data-platform.md

Latest commit

History