too-many-cooks: server uses one ambient ~/tmc DB shared across all workspaces — foreign projects' idle agents + never-expired messages appear as 'ghosts'

[MelbourneDeveloper]

Summary

The VS Code panel shows agents and messages that don't belong to the current workspace (e.g. Osprey2 → Osprey1, 22 days old, from an unrelated project). This looks like DB corruption / broken referential integrity, but it is not — verified below. The real root cause is database path ambiguity + no message retention/expiry.

Evidence

Investigated as the live Basilisk coordinator agent.

1. The server does NOT use the workspace-local DB. Each project has its own <workspace>/.too_many_cooks/data.db, but the running MCP server + panel actually use an ambient ~/tmc/.too_many_cooks/data.db:

   • Documents/Code/Basilisk/.too_many_cooks/data.db → Basilisk/2/3 absent (stale; last writers were opus-main etc.).
   • ~/tmc/.too_many_cooks/data.db → Basilisk/2/3 present, plus 5 Osprey identities, 191 Osprey messages, 202 messages / 92 identities total.
   • There are 30+ .too_many_cooks/data.db files across the machine; which one the server binds appears to depend on launch CWD, not the workspace.

2. Referential integrity is intact on the real DB — the 'ghosts' are real, existing rows, not orphans:

   • PRAGMA foreign_key_check; → empty (no violations).
   • Orphan senders (from_agent ∉ identity) → none. Orphan non-broadcast recipients → none.
   • Osprey1, Osprey2, OspreyMaster, OspreyServant, OspreyServant2 all exist in identity with active=0, last_active ~22 days ago.

3. Caveat / latent risk: PRAGMA foreign_keys returns 0 (enforcement off — SQLite default per-connection). The messages.from_agent FK is declared but not enforced and there is no ON DELETE CASCADE. If an identity row were ever deleted, its messages would become true orphans silently. Not currently happening, but the schema relies on enforcement that is off.

Actual bugs to fix

1. Workspace isolation: bind the DB deterministically (per-workspace, or a single well-known global path) and surface which DB is in use. Today the same ambient DB is shared across all projects, so unrelated agents/messages cross-contaminate every panel.
2. Message retention: unread broadcasts/direct messages from long-inactive (active=0) agents are never expired or auto-marked-read — they accumulate forever (202 rows, many 22d old) and dominate the panel as ghost noise. Add TTL/cleanup, or filter the panel to active agents + recent messages.
3. Enforce FKs (PRAGMA foreign_keys=ON) + ON DELETE CASCADE on messages/plans/locks so identity deletion can't orphan rows.

Filed by the Basilisk coordinator during a live multi-agent conformance session (companion to #158).