Consolidate state.db tables into global.db (single user-global database) to eliminate the cwd-dependent fragmentation that causes "missing architect state after restart"

[amrmelsayed]

Problem

.agent-farm/state.db is named and located as if it's workspace-local, but it actually holds rows from every workspace Tower has ever interacted with while parked in that directory. Combined with Tower's start-cwd determining which state.db file is the "active" one for a session, this produces a fragmentation pattern where:

• Tower running from workspace A → reads/writes A/.agent-farm/state.db
• During that session, any cross-workspace interaction (afx send from workspace B, dashboard switching to workspace C, VS Code extension calling Tower from workspace D) lands its rows in A's state.db.
• Reboot. Tower next starts from workspace B → reads/writes B/.agent-farm/state.db. Rows from previous session in A's file are now invisible to the running Tower, even though they're intact on disk.

User-facing symptom (verified against this user's machine): "after a computer restart, some architects in my workspaces are missing their state / session data." The architects whose rows happened to land in Tower's previous start-cwd's state.db are stranded; the architects whose rows are in the current Tower's state.db work fine. Hence "some" architects missing, not "all."

Audit confirming the pattern

8 state.db files exist across a multi-workspace user's local checkouts:

File	architect rows	distinct workspaces represented
~/.agent-farm/state.db	1	1 (a builder worktree)
~/repos/cluesmith/codev/.agent-farm/state.db	40	38 (codev + multiple sibling projects + 11 builder worktrees + 17 ephemeral test workspaces)
~/repos/cluesmith/shannon/.agent-farm/state.db	4	3 (codev + shannon + a codev builder worktree)
~/repos/insighttrail/kidscanspell/.agent-farm/state.db	4	4 (multiple unrelated workspaces)
~/repos/bb/MPPS2/.agent-farm/state.db	2	2 (codev + shannon builder worktrees)
~/repos/amrmelsayed/codev/.agent-farm/state.db	1	0 (pre-Migration-v11 row, no workspace_path)
~/repos/insighttrail/autotoggl/.agent-farm/state.db	0	0 (empty file, lazy-init from a CLI command run there)
~/repos/cluesmith/codev/worktrees/changelog/.agent-farm/state.db	0	0 (empty file, lazy-init from a CLI command run there)

Workspaces appearing in multiple state.db files (the fragmentation symptom):

• cluesmith/codev → codev's DB + shannon's DB
• cluesmith/shannon → codev's DB + shannon's DB
• cluesmith/codev-streamdeck → codev's DB + kidscanspell's DB
• Multiple builder worktrees scattered across 3-4 different files

Population affected

Verified against the spawn paths:

User class	Affected?
Single-workspace user with manual afx tower start from project root	No
Single-workspace user with VS Code extension auto-start	No
Multi-workspace user with manual afx tower start from "wherever they happen to be"	Yes
Multi-workspace user with VS Code extension auto-start	Yes (the extension passes cwd: workspacePath to spawn, so whichever Codev project is opened first after a Tower-restart determines the location)
Local-install dev user	Yes (every pnpm -w run local-install runs afx tower start from the script's invocation cwd)

This is a default-behaviour issue for any user with more than one Codev project on disk and any Tower-restart history (machine reboot, pnpm -w run local-install, manual afx tower stop/start, VS Code switching projects). It is not a developer-environment-only issue.

Verified spawn-path code locations

• packages/codev/src/agent-farm/commands/tower.ts — afx tower start spawns with cwd: process.cwd(). Inherits the shell's cwd.
• packages/codev/src/agent-farm/utils/config.ts — findWorkspaceRoot() walks up from cwd looking for .git + codev/; falls back to cwd if not found (which is how ~/.agent-farm/state.db got created when Tower was started from $HOME once).
• packages/vscode/src/tower-starter.ts:47-51 — VS Code extension's autoStartTower spawns Tower with cwd: workspacePath ?? undefined. The VS Code workspace folder becomes Tower's cwd.
• packages/codev/src/agent-farm/db/index.ts — getDb() lazy-initialises state.db at <workspaceRoot>/.agent-farm/state.db, creating an empty file if absent. This is why workspaces where Tower never ran still have state.db files (CLI commands run from inside them lazy-created the file).

Why two databases at all?

state.db and global.db exist as separate files for archaeological reasons, not principled design. state.db predates the system-wide Tower architecture: back when Tower was per-workspace, each workspace had its own daemon and its own genuinely-workspace-local file. When Tower's architecture shifted to a system-wide singleton, the right move would have been to relocate state.db to a user-global location to match the new scope. Instead, Bugfix #826 added a workspace_path column (Migration v11) so rows from different workspaces could coexist in the same file — a symptom-patching fix that left state.db's location workspace-local while making its scope effectively global.

global.db was added separately around Spec 0090 / TICK-001 for inherently-cross-workspace tables (terminal_sessions, port_allocations, file_tabs, cron_tasks, known_workspaces). The split has no principled boundary today — it's "tables added before the singleton transition" vs "tables added after." Both files are functionally user-global; only state.db lies about its scope via its file location.

Proposed approach: collapse state.db tables into global.db (single user-global database)

All state.db tables (architect, builders, utils, annotations) move into ~/.agent-farm/global.db. The per-workspace state.db file is retired. Tower keeps one connection to one file regardless of which workspace it's serving; rows are disambiguated by the existing workspace_path column (already in place since Migration v11, Bugfix #826).

What changes

1. All state.db tables migrate into global.db in a new migration. global.db's migration system absorbs the schemas; a new global.db migration version becomes "absorb state.db tables (architect, builders, utils, annotations)".
2. getDb() and getGlobalDb() consolidate — getDb() always returns the global.db connection. The cwd-dependent state.db creation logic at db/index.ts is removed.
3. findWorkspaceRoot() is no longer load-bearing for db location — it stays for protocol/template resolution (the four-tier file resolver, unchanged), but the db path is now always ~/.agent-farm/global.db.
4. state.ts function signatures stay — they already take workspace_path as their first argument (post-Bugfix-CRITICAL: Sibling architects leak across workspaces — launchInstance reconcile reads global state.db.architect without workspace filtering #826), which is now the row-disambiguator within the single shared DB rather than the path-resolver.
5. Spawn-time writes go to the right workspace's rows regardless of where Tower was started — no more cwd dependence at all.
6. terminal_sessions ↔ architect/builder is a natural single-file join going forward. Today it's a cross-file lookup; after the merge it's a single SELECT JOIN. The afx: status builders table should include PID + terminal session id (parity with the architects section) #1115 PID-parity follow-up benefits directly.

What doesn't change

• architect, builders, utils, annotations table schemas (workspace_path column stays as the row-disambiguator; same role it has today, just inside the single shared DB).
• Migration v12's session_id column work — independent.
• ~/.agent-farm/global.db location.
• The known_workspaces table in global.db (still the registry of workspaces Tower has ever touched).
• The per-workspace .agent-farm/ directory — left in place (forward-compat for future per-workspace files; don't actively delete during migration).

Migration strategy

One-time on-disk migration during the Tower restart that follows the upgrade:

1. Discover all existing state.db files by reading known_workspaces and checking each workspace's .agent-farm/state.db. Also include ~/.agent-farm/state.db (the home-fallback that gets created when Tower was started from $HOME).
2. Per file: union its architect, builders, utils, annotations rows into global.db using INSERT OR REPLACE keyed on the primary key. Conflict resolution: latest-started_at wins. The workspace_path column preserves identity of rows from different workspaces.
3. Dry-run available: afx tower start --dry-run-migration lists every row that would be merged + flags conflicts (same row written to multiple state.db files with different content). --apply-migration (or running plain afx tower start after the upgrade) commits.
4. Source files preserved: do not delete state.db files during migration. Rename them to state.db.pre-merge-<timestamp> so users can recover if needed.
5. Idempotent: a marker row in global.db's _migrations table tracks completion. Subsequent Tower starts are no-ops on the migration check.

Stale-row hygiene

The "self-cleaning on workspace deletion" benefit of per-workspace state.db files is replaced by an explicit prune command:

afx prune-state — removes rows from architect, builders, utils, annotations whose workspace_path is not in known_workspaces. Dry-run by default; --apply to commit. Handles the stale-row accumulation case (the 17 test-workspace rows in this user's audit are exactly what it removes). Run on user demand, not automatically — the per-workspace "free cleanup via rm" pattern was always opt-in in practice.

afx workspace forget <path> — removes a workspace from known_workspaces AND prunes its associated state rows in one command. The clean way to retire a workspace whose directory has been deleted or moved.

Acceptance criteria

• architect, builders, utils, annotations tables exist in global.db (added via a new migration).
• getDb() returns the global.db connection from all callsites in state.ts.
• A spawn flow in workspace A writes its architect row to ~/.agent-farm/global.db regardless of where Tower was started.
• Reboot scenario: stop Tower from workspace A, start Tower from workspace B, verify workspace A's architects (now in global.db) are readable from Tower running at B.
• Migration: existing state.db files are scanned, rows merged via INSERT OR REPLACE with latest-started_at-wins conflict resolution; source files renamed (not deleted).
• Migration is idempotent: re-running doesn't re-migrate.
• The cwd-dependent state.db creation logic is removed; findWorkspaceRoot() no longer drives db location.
• afx prune-state removes rows whose workspace_path is not in known_workspaces; dry-run by default, --apply to commit.
• afx workspace forget <path> removes a workspace from known_workspaces and prunes its rows in one command.
• Unit tests cover: cross-workspace row isolation via workspace_path; migration row-routing correctness on multi-source merge with conflicts; migration idempotency; prune-state correctness against known_workspaces; workspace forget end-to-end.

Alternatives considered

1. Per-workspace state.db (each <workspace>/.agent-farm/state.db holds only its own rows; Tower opens N connections lazily, LRU-bounded). Gains: free cleanup on workspace deletion (rm the dir, state goes with it); portability (move the dir, state moves with it); self-cleaning of stale rows. Costs: N database handles requiring an LRU pool; per-callsite audit to thread workspace_path through every state.ts function; cross-workspace queries (dashboard's all-workspaces view, reconciliation passes) become iterations over known_workspaces opening each state.db on demand; the terminal_sessions ↔ architect/builder join stays cross-file; adds an architectural decision for future tables ("which workspace's DB?"). The cleanup and portability gains assume workflows (frequent rm of workspaces, moving workspace dirs) that aren't dominant; the self-cleaning gain is achievable cheaper via the prune-state command above. Rejected as more complex than the problem warrants.

2. Just-move state.db to ~/.agent-farm/state.db (keep the two-DB split, just relocate the path). Fixes the cwd-dependence; doesn't address the deeper architectural debt that the state.db / global.db split was always arbitrary. New table additions would still face the "which DB?" question. Rejected as a half-measure.

3. Force-migration on Tower startup with no opt-out / no dry-run. The migration is straightforward and well-defined, but a hard-blocking migration on first launch under new code feels coarse. The proposed approach makes migration automatic with a dry-run preview available and sources preserved as *.pre-merge-<timestamp> files. Same net effect, gentler upgrade story. Adopted in the proposal above.

4. Hybrid: per-workspace state.db AND user-global state.db with sync. Two sources of truth; sync correctness becomes its own problem. Rejected.

Out of scope

• Moving terminal_sessions, port_allocations, cron_tasks, file_tabs out of global.db. They're already correctly user-global; no change needed.
• Removing the workspace_path column from architect / builders / etc. It's still the row-disambiguator within the single shared DB; could be revisited as a v2 cleanup once the merge has lived for a while, but not for v1.
• Auto-pruning stale rows on every Tower start. afx prune-state is opt-in.
• Touching Migration v12's session_id work or any other in-flight migration.
• Per-workspace .agent-farm/ directory cleanup. The directory stays for forward compat; let it die naturally if unused.

Protocol

PIR. This is a significant infrastructure change with multiple design decisions (migration dry-run UX, prune-state semantics, conflict-resolution policy on merge, the workspace forget command shape) that benefit from plan-gate validation, plus dev-gate verification of:

• the multi-workspace reboot scenario on a running Tower
• the dry-run migration preview against a real machine with fragmented state.db files
• the prune-state command against synthetic known_workspaces/stale-row fixtures

before opening the PR.

Related

• Bugfix CRITICAL: Sibling architects leak across workspaces — launchInstance reconcile reads global state.db.architect without workspace filtering #826 — Migration v11 added workspace_path to the architect table primary key when cross-workspace contamination was first discovered. That was the symptom-patching response; this issue is the structural fix that retires the now-redundant per-workspace file location.
• Spec 0090 / TICK-001 — added global.db for inherently-cross-workspace tables; this issue extends the same principle to the remaining state.db tables, which are also effectively user-global since Bugfix CRITICAL: Sibling architects leak across workspaces — launchInstance reconcile reads global state.db.architect without workspace filtering #826.
• PR Multi-architect conversation resume via persisted per-architect session ID (#832) #1116 / Issue Multi-architect conversation resume: disambiguate via per-architect session UUID #832 — per-architect session UUID. Independent; layers cleanly on top of the single-DB consolidation.
• afx: status builders table should include PID + terminal session id (parity with the architects section) #1115 — afx status builders PID parity. Benefits directly from the consolidation: the terminal_sessions ↔ builders join becomes a single-file query.
• Unify builder conversation resume onto persisted session IDs (harness.session), retiring the jsonl-discovery heuristic #1112 — Unify builder conversation resume on persisted session IDs. Independent but adjacent; both touch the same per-builder state surface.
• Architect-row residue — the 17 test-workspace rows + 11 builder-worktree rows in this user's codev state.db (audited in the conversation that spawned this issue) are what afx prune-state is designed to remove cleanly.

[amrmelsayed]

On it! Working on this with the PIR protocol (plan + dev-approval gates before PR).