Make Cloud Gastown beads-centric: unify all object types into the beads primitive

[jrf0110]

Note: Cloud Gastown has not yet deployed to production. There is no existing user data to migrate. All schema changes in this issue should be implemented as if writing the schema from scratch — just replace the old table definitions and update the code that references them. No data migration scripts, no manual ALTER TABLE statements, no backward compatibility shims.

Overview

The cloud gastown implementation created separate tables for every object type that local Gastown encodes as beads. In local Gastown, everything is a bead — mail, molecules, convoys, escalations, merge requests, agent identity records, channels, groups, queues. They're all beads with different labels, participating in the same event ledger, dependency system, query interface, and lifecycle model.

The cloud diverged from this by creating dedicated tables: rig_mail, rig_molecules, rig_review_queue, town_convoys, town_convoy_beads, town_escalations, and rig_agents. Each table reinvents a subset of what beads already provides — status tracking, event logging, assignee, priority, labels, dependencies, parent-child hierarchy.

This issue tracks the work to make Cloud Gastown beads-centric: collapse the separate tables into the universal beads table, with type-specific metadata in satellite tables joined via bead_id where needed.

Parent: #204 / #419

Background: How Local Gastown Encodes Everything as Beads

Object Type	Local Gastown	Cloud (current)
Mail	Bead with gt:message label	Separate rig_mail table
Convoy	Bead with type convoy	Separate town_convoys + town_convoy_beads tables
Escalation	Bead with gt:escalation label	Separate town_escalations table
Merge request	Bead with gt:merge-request label	Separate rig_review_queue table
Molecule	Parent bead with child step beads	Separate rig_molecules table with JSON formula column
Agent identity	Bead with gt:agent label	Separate rig_agents table
Channel	Bead with gt:channel label	Not implemented
Group	Bead with gt:group label	Not implemented
Queue	Bead with gt:queue label	Not implemented

Why This Matters

The cost of the divergence compounds as features are added:

• No unified event ledger. Closing a merge request, closing a mail message, closing an escalation — these are all different code paths writing to different tables with different event tracking (or none). In local Gastown, closing anything is bd close <id> and fires the same events.
• No cross-object dependencies. An escalation can't block a merge request. A convoy can't track MRs and issues in the same dependency list. These are natural in beads-as-universal-primitive but require cross-table joins in the current schema.
• No unified query interface. Every table has its own CRUD methods, its own status enum, its own query patterns. Components that need to inspect multiple object types (dashboard activity feed, agent prime context, convoy landing checks) must query N different tables.
• No unified lifecycle. Beads have a consistent open/close/reopen lifecycle with timestamps and audit trails. Each separate table reinvents this partially or not at all.

Target Architecture

Universal beads table

Rename rig_beads → beads (beads belong to the town, not rigs). The rig_id column becomes an optional tag, not an ownership relationship — some beads (mayor messages, cross-rig escalations) have no rig.

CREATE TABLE beads (
  bead_id TEXT PRIMARY KEY,
  type TEXT NOT NULL,          -- 'issue', 'message', 'escalation', 'merge_request', 'convoy', 'molecule', 'agent'
  status TEXT NOT NULL DEFAULT 'open',
  title TEXT NOT NULL,
  body TEXT,
  rig_id TEXT,                 -- optional: which rig this bead is associated with
  parent_bead_id TEXT REFERENCES beads(bead_id),
  assignee_agent_bead_id TEXT, -- for mail: recipient. for issues: assigned worker. references agent bead.
  priority TEXT DEFAULT 'medium',
  labels TEXT DEFAULT '[]',    -- JSON array: ['gt:message', 'from:gastown/witness', 'delivery:pending']
  metadata TEXT DEFAULT '{}',  -- JSON object for type-specific data that doesn't warrant a column
  created_by TEXT,             -- agent identity string (BD_ACTOR equivalent)
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  closed_at TEXT
);

CREATE INDEX idx_beads_type_status ON beads(type, status);
CREATE INDEX idx_beads_parent ON beads(parent_bead_id);
CREATE INDEX idx_beads_rig_status ON beads(rig_id, status);
CREATE INDEX idx_beads_assignee ON beads(assignee_agent_bead_id, type, status);

Satellite metadata tables (one-to-one via bead_id)

Type-specific operational fields that don't belong on a generic bead row live in satellite tables:

-- Agent-specific operational state (container process tracking, checkpoints)
CREATE TABLE agent_metadata (
  bead_id TEXT PRIMARY KEY REFERENCES beads(bead_id),
  role TEXT NOT NULL,           -- 'mayor', 'polecat', 'witness', 'refinery'
  identity TEXT NOT NULL,       -- full identity string
  container_process_id TEXT,
  status TEXT NOT NULL DEFAULT 'idle',  -- 'idle', 'working', 'stalled', 'dead'
  current_hook_bead_id TEXT REFERENCES beads(bead_id),
  checkpoint TEXT,              -- JSON crash-recovery data
  last_activity_at TEXT
);

-- Merge request-specific fields
CREATE TABLE review_metadata (
  bead_id TEXT PRIMARY KEY REFERENCES beads(bead_id),
  branch TEXT NOT NULL,
  target_branch TEXT NOT NULL DEFAULT 'main',
  merge_commit TEXT,
  pr_url TEXT,
  retry_count INTEGER DEFAULT 0
);

-- Escalation-specific fields
CREATE TABLE escalation_metadata (
  bead_id TEXT PRIMARY KEY REFERENCES beads(bead_id),
  severity TEXT NOT NULL,       -- 'low', 'medium', 'high', 'critical'
  category TEXT,
  acknowledged INTEGER NOT NULL DEFAULT 0,
  re_escalation_count INTEGER NOT NULL DEFAULT 0,
  acknowledged_at TEXT
);

-- Convoy-specific fields
CREATE TABLE convoy_metadata (
  bead_id TEXT PRIMARY KEY REFERENCES beads(bead_id),
  total_beads INTEGER NOT NULL DEFAULT 0,
  closed_beads INTEGER NOT NULL DEFAULT 0,
  landed_at TEXT
);

Mail, molecule steps, and regular issues need no metadata table — everything fits in the base bead columns plus labels/metadata JSON.

Dependency table

Encodes the DAG: molecule step ordering, convoy-to-bead tracking, blocking relationships.

CREATE TABLE bead_dependencies (
  bead_id TEXT NOT NULL REFERENCES beads(bead_id),
  depends_on_bead_id TEXT NOT NULL REFERENCES beads(bead_id),
  dependency_type TEXT NOT NULL DEFAULT 'blocks',  -- 'blocks', 'tracks', 'parent-child'
  PRIMARY KEY (bead_id, depends_on_bead_id)
);

CREATE INDEX idx_deps_depends_on ON bead_dependencies(depends_on_bead_id);

How each object type maps

Mail: Bead with type = 'message'. Recipient = assignee_agent_bead_id. Sender = label from:<identity>. Thread/reply-to = labels. Read/unread = open/closed status. No metadata table needed.

Molecule: Parent bead with type = 'molecule'. Steps are child beads with parent_bead_id pointing to the molecule root. Step ordering via bead_dependencies with type = 'blocks'. No metadata table needed — current_step is derived by querying children.

Convoy: Bead with type = 'convoy'. Tracked beads linked via bead_dependencies with type = 'tracks'. Convoy progress is derived from tracked bead statuses. Metadata table holds total_beads/closed_beads counters (denormalized for dashboard performance).

Escalation: Bead with type = 'escalation'. Metadata table holds severity, category, acknowledgment state, re-escalation count.

Merge request: Bead with type = 'merge_request'. Metadata table holds branch, target, merge commit, retry count.

Agent identity: Bead with type = 'agent'. Metadata table holds role, identity string, container state, hook, checkpoint. Agent CVs are derived from SELECT * FROM beads WHERE created_by = <agent-identity> AND status = 'closed'.

Agent scoping note

Agents belong to the town, not to rigs. A single agent identity (e.g., "Toast") can work on one rig for a task and later get scheduled to a different rig. The agent bead has no rig_id — work assignments are rig-scoped (via the hooked bead's rig_id), but the agent identity is town-global.

Graph queries in DO SQLite

The bead graph depth in Gastown is shallow and predictable:

• Molecule steps: 1 level deep (SELECT * FROM beads WHERE parent_bead_id = ?)
• Convoy tracking: 1 level deep (SELECT b.* FROM beads b JOIN bead_dependencies d ON d.bead_id = b.bead_id WHERE d.depends_on_bead_id = ? AND d.dependency_type = 'tracks')
• Dependency DAG: SELECT * FROM bead_dependencies WHERE bead_id = ? + check if blockers are closed. Two queries, no recursion.
• Hook chain: agent_metadata → hooked bead → parent_bead_id → steps. 3 hops, each a PK lookup.

In a DO, ctx.storage.sql.exec() is an in-process function call. Running 3-4 sequential queries costs microseconds. No network round trips.

Key indexes: parent_bead_id, (type, status), (assignee_agent_bead_id, type, status), (rig_id, status).

Implementation Plan

Since we have not deployed to production, there is no data to migrate. Each phase below replaces old table definitions and code with the new beads-centric equivalents. Delete the old tables from initializeDatabase(), add the new ones, and update all handler/DO code that referenced the old tables.

Phase 1: Schema foundation

• Replace rig_beads with beads table (new schema above) in initializeDatabase()
• Replace rig_bead_events with bead_events (update foreign keys to use bead_id)
• Add bead_dependencies table
• Add satellite metadata tables (agent_metadata, review_metadata, escalation_metadata, convoy_metadata)
• Add indexes
• Update all TypeScript types and table definition files

Phase 2: Unify mail into beads

• sendMail → createBead with type 'message', labels ['gt:message', 'from:<sender>']
• checkMail → listBeads with filter type = 'message' AND assignee_agent_bead_id = ? AND status = 'open'
• Update plugin tools (gt_mail_send, gt_mail_check)
• Remove rig_mail table definition and all references

Phase 3: Unify molecules into beads

• Molecule creation → parent bead + child step beads linked by parent_bead_id and bead_dependencies
• gt_mol_current → query children of molecule bead, check dependency status
• gt_mol_advance → close current step bead, find next ready step
• Remove rig_molecules table definition and all references

Phase 4: Unify review queue into beads

• MR submission → createBead with type 'merge_request' + review_metadata row
• Queue processing → listBeads with filter type = 'merge_request' AND status = 'open'
• Remove rig_review_queue table definition and all references

Phase 5: Unify escalations into beads

• Escalation creation → createBead with type 'escalation' + escalation_metadata row
• Routing → query escalation_metadata.severity for routing decisions
• Remove town_escalations table definition and all references

Phase 6: Unify convoys into beads

• Convoy creation → createBead with type 'convoy' + convoy_metadata row
• Tracked beads → bead_dependencies with dependency_type = 'tracks'
• Landing detection → query tracked bead statuses
• Remove town_convoys + town_convoy_beads table definitions and all references

Phase 7: Unify agents into beads

• Agent registration → createBead with type 'agent' + agent_metadata row
• Hook mechanism → agent_metadata.current_hook_bead_id
• Agent queries → listBeads with filter type = 'agent'
• Remove rig_agents table definition and all references (replace with beads + agent_metadata)

Phase 8: Clean up

• Remove all dead table schema files, TypeScript types, and handler code
• Verify no remaining references to old table names

[jrf0110]

Completed in PR #445 (merged). All object types unified into the beads table with satellite metadata tables, discriminated union schema, and bespoke parsers eliminated.