@blueforge-studio/dev-db

Per-repo local architecture database — the repo-side counterpart to the forge-control hub. Aggregates everything the federation already declares (sidecar V3 files, arch-graph JSON from registerArch(), user-flows / data-flows, blueforge.tests.yml links) plus repo context (READMEs, docs / plans / superpowers folders, package.json manifests) into one queryable SQLite store, and serves it as an agent-facing CLI and a catalog UI data source ("internal packr").

Specs: blueforge/docs/plans/2026-09-25-dev-db-{vision,design-spec,rollout-plan}.md

Principles

• Derived-only. The store is regenerated by dev-db sync; it is never authoritative. .forge/devdb.sqlite is gitignored.
• Source is identity. Every row carries declared | detected | runtime provenance.
• Audit path is one-directional (decision D10): audits/test-inventory and coverage maps are walker inputs; nothing in this package ever writes audit artifacts.
• Drift-free lifecycle (decision-free by design): the three-way comparison (YAML intent / runtime fact / inferred state) is computed, never hand-edited.
• Offline and fast. No network at boot or sync — container-safe.

Install

pnpm add -D @blueforge-studio/dev-db better-sqlite3

better-sqlite3 is an optional peer, so a package manager is free to install nothing — but nothing works without it, because it is the sqlite driver. Install it explicitly. better-sqlite3 is a native module, so on pnpm 10+ the build has to be approved as well, or the CLI will report every path it searched for better_sqlite3.node and stop:

// package.json
"pnpm": { "onlyBuiltDependencies": ["better-sqlite3"] }

or pnpm config set --location=project onlyBuiltDependencies '["better-sqlite3"]'.

Usage

pnpm exec dev-db sync                 # rebuild .forge/devdb.sqlite
pnpm exec dev-db summarize            # organism overview (repo level)
pnpm exec dev-db app <id>             # drill-down to one app (monorepos)
pnpm exec dev-db flow build-deck      # flow + implementers + tests + docs
pnpm exec dev-db tests <node-ref>     # which tests exercise this node
pnpm exec dev-db impact decks         # blast radius before editing
pnpm exec dev-db docs --type plan     # indexed plans
pnpm exec dev-db packages             # catalog rows
pnpm exec dev-db readme @scope/pkg    # README + scripts + deps
pnpm exec dev-db flags --app <id>     # flag keys the deploy config declares
pnpm exec dev-db flag CHECKOUT_ENABLED  # which apps declare this key
pnpm exec dev-db assets --needs-regen  # per-image asset audit rows
pnpm exec dev-db asset-audits         # asset audit runs, newest first

All commands accept --format json (agents parse, not eyeball) and exit 0 ok / 1 usage / 2 not found / 3 sync walker errors.

History replay — travel through a repo's progression

dev-db sync shows a repo as it is now. dev-db replay rebuilds how it got here: for each first-parent commit it derives the feature specs, user/data flows, routes and screens and keeps them, keyed by commit, in a local store (.forge/replay.sqlite, gitignored). Then you can ask what the repo looked like at any commit, what a PR changed, or how a layer moved over a date range.

dev-db replay --from v1.2.0                 # a tag, a sha, or a date (2026-09-01)
dev-db replay --from 2026-09-01 --routes --screens --arch-sdk <path-to-architecture-sdk>
dev-db fetch-prs <owner>/<repo> --last 100 --out prs.json    # merged PR records, read through fgit
dev-db link --prs prs.json                  # attach each commit to its PR
dev-db history --by-pr                      # one row per PR: what changed in each layer
dev-db at <sha|tag>                         # the state at one commit, with coverage
dev-db diff <a> <b>                         # added / removed / changed between two commits
dev-db lineages                             # snapshot lineages (a force-push orphans the old one, never deletes it)

What a real run looks like (office-platform, the PR that introduced the architecture tables):

f964e7cd5 -     flow +0/-0/~0  routes +185/-0/~0  spec +0/-0/~0  test: make the no-network guard a standing rule (#116)
4c6f5a6c3 -     flow +0/-0/~0  routes +0/-0/~183  spec +0/-0/~0  docs(architecture): declared architecture for ten apps (#117)

185 routes appear (found in the filesystem), then at #117 the same 183 routes are declared: ~183 changed, nothing added or removed. Another (marketing-os): about 20 screens show as changed at each CI commit that promotes visual-regression baselines, with no route change — the evidence moved, not the screens.

Layers

Layer	Source at each commit	Flag
spec	features/*.feature.yml	always
flow	user-flows/, data-flows/	always
routes	literal defineRoutes tables + Next App Router files	--routes
screens	every front-end page route (state default) + audit results the repo committed	--screens

--routes and --screens need an architecture-sdk that ships deriveRoutesStatic (0.6.0+), found in the replayed repo's node_modules or via --arch-sdk <dir>.

Honesty rules

• Read-only and static. Git is only asked questions; the few files a layer reads are extracted to a temp directory and deleted. Nothing in an old commit is imported or run (a route table that is not a plain literal is named in the coverage line, never executed).
• Replayed is not pushed. Everything is derived from what the commit contains. Test runs, audits and screenshots are never reconstructed: a screen has evidence only if the repo committed it.
• Nothing silently disappears. Every layer has a coverage line: files found, parsed, dropped (with the reason). An old-schema spec shows as lenient with the reason it fails today's schema; a pre-spec manifest counts as legacy. 0 never means "not derivable" — those say so (be: not-derivable).
• PR links are verified, not assumed. A PR owns the commits since the previous PR's merge commit; the link is verified only when that range changes as many files as the PR reports. Otherwise it is stored unverified with the reason, and a PR whose merge commit is not on the main line is listed as unlinked.
• A force-push does not corrupt the series. If the commits last replayed are no longer on the branch (neither an ancestor nor a descendant of its head, or gone from the repo), the run opens a NEW lineage: the old snapshots are kept and marked orphaned with the reason, the new line is its own series, and at/diff/history follow the active lineage (--lineage N reads an older one). Replaying an earlier range is not a rewrite.
• Idempotent. A second run changes nothing. Snapshots are keyed by commit and deriver version (plus the layer plugins used), so a better deriver adds rows instead of overwriting history.

Cost

Unchanged layers are stored by reference (inputs are hashed by path + git blob id). office-platform / restaurant-os replay in 1–7 seconds; a 294-commit marketing-os replay with routes and screens took about 2.5 minutes on a heavily loaded machine. Stores are small (restaurant-os: 90 KB for 28 commits).

Not covered yet

Backend (Hono/Express) routes, named UI states, screenshots, interactions, and pushing snapshots to the hub (so other sessions can query them) — see docs/plans/2026-10-08-repo-progression-replay-and-route-screen-graph.md in the blueforge repo.

Flags are a projection, not a control surface

dev-db flags reports which flag-like env keys a repo declares. It cannot change one, and the store is regenerated by sync — a value written here would be erased on the next run. Writes go to the forge-control flag store (decision D15); the answer shows up here on the next sync.

Two deliberate omissions:

• No values. The deploy configs carry real literals (EMAIL_FROM, AUTH_BASE_URL, 13 per app in roomcraft). Only key names are stored, plus has_literal_value. Same rule as blueforge's scripts/wiring-inventory.mjs: the config is in the same checkout as the db, so a copy buys nothing and a leak costs.
• No sidecar flags: block yet. flags: is not in @blueforge-studio/blueforge-sidecar-schemas 0.29.0, so no repo can declare one (decision D16, owned by blueforge-org).

Asset audits

image-audit-results-*.json is read, not the .audit-cache.sqlite twin (decision D18). The SQLite results table has no repo or app column, so a row there is identifiable only by which file it sits in — comparing audits across apps is inexpressible rather than slow. The JSON is also the richer form (audit model, provider, spec hash, summary).

The local .audit-cache.sqlite files are left alone: per-repo caches, not part of this package's business.

Rows partition on the app whose sidecar path contains the audit's directory. A repo whose sidecar declares no matching app still indexes every row — they aggregate at repo level with a null app_id.

Configuration (optional)

devdb.config.yml at the repo root:

docs:
  include: [docs, superpowers, plans]   # folders the docs walker indexes

Agent orientation protocol

Standard first move for a session in an unfamiliar repo:

dev-db summarize            → organism overview
dev-db flow <flow-id>       → molecules implementing the flow
dev-db impact <node-ref>    → blast radius before editing
dev-db tests <node-ref>     → existing coverage for a node

Storage

SQLite (better-sqlite3, optional peer) behind the DevDbDriver interface — a code-index-backed or Postgres driver can replace it without touching walkers, queries or the CLI (decision D11).

The store is .forge/devdb.sqlite by convention. The tool creates .forge/ if it is missing, and the file is derived — gitignore it (.forge/), because sync regenerates it and it is never authoritative.

Changelog

0.10.0 — 2026-10-08

Rewritten history opens a new snapshot lineage (replay_lineages, dev-db lineages, --lineage N) instead of mixing two histories into one series; the previous lineage is kept and marked orphaned. replay reports it.

0.9.0 — 2026-10-08

dev-db replay | at | diff | history | link | fetch-prs: derive spec, flow, routes and screens layers for every first-parent commit, link commits to PRs, and time-travel through them (see History replay). sync() accepts { gitHead, repoId }. New exports: replay, deriveFeatureSpecs, linkPullRequests, history, fetchMergedPrs, routesPlugin, screensPlugin, LayerPlugin.

0.2.1 — 2026-10-02

First-run fixes, reported by the office session after a clean-install adoption in lq-cards. 0.2.0 worked and was never installable by a fresh consumer — every one of these is on the path a new adopter walks first.

• better-sqlite3 missing now says what to do. The peer is declared optional, so a package manager is free to install nothing, and the require let MODULE_NOT_FOUND escape — an adopter saw Cannot find module 'better-sqlite3' and nothing about the native-build gate that comes next. The error now names the install command and the pnpm onlyBuiltDependencies setting.
• .forge/ is created if missing. better-sqlite3 will not create a missing parent, so a first run in a clean repo threw "Cannot open database because the directory does not exist" — a second failure on the same path, after the first.
• README gains an Install section covering both, plus a note to gitignore .forge/: the store is derived, and a repo that commits it is committing what sync regenerates.

0.2.0 — 2026-10-02

• Flags walker — indexes blueforge.config.json env and secrets key names per app, with origin (config.env, config.env.REQUIRED, config.env.OPTIONAL, config.secrets.vault|env|literal) and has_literal_value. No values are stored (see above). Read-only by construction — decision D15: the dev-db is derived, so it can show a flag but never own one.
• Assets walker — ingests image-audit-results-*.json into asset_audit_runs + assets, partitioned by declared app path. Decision D18: the JSON, not the .audit-cache.sqlite twin, whose results table has no repo or app column and therefore cannot express a cross-app comparison at all.
• Additive migration path (ADDITIVE_DDL + migrateAdditive()). migrate() only applies DDL to an empty store, and .forge/devdb.sqlite is gitignored and outlives the package version that wrote it — appending to SCHEMA_DDL alone would have left every existing store failing with "no such table: flags".
• New queries: flagsView, flagMatrix, assetsView, assetAuditsView; new CLI commands flags, flag, assets, asset-audits.
• WalkerContext gains appPaths so later walkers partition by declared app path rather than by directory name.

Known gap (pre-existing, not introduced here): walkSidecars reads a top-level apps: array, so a sidecar written as products: — forge-control is one — contributes no app rows, and flags/assets inherit a null app_id there. Both still index every row at repo level. Closed in 0.2.2: the sidecar walker now also harvests the products: shape (each entry's primaryApp becomes an app row) and registers blueforge.config.json's apps map — the fleet's real deploy surface, and the exact id set walkFlags iterates — so flags and assets partition by app instead of inheriting null app_id on repos whose sidecar lists few or no apps. V3 blueforge.app.yml rows still win where they exist (they overwrite with the more precise row).

0.1.1 — 2026-09-25

• Tests walker detects collocated *.test.ts / *.spec.ts files beside sources (not only dedicated tests//__tests__/ dirs) — found by dogfooding on lq-cards, whose whole unit suite is collocated.

0.1.2 — 2026-09-25

• Flows walker indexes the 0.17.0 YAML flow family (*.user-flows.yml / *.data-flows.yml, top-level flows: array — the shape arch-sdk trace draft emits), unpacking kind / action / selector / expected from the packed step descriptions. JSON convention unchanged.