Gastown: Revisit bead metadata column vs. satellite metadata tables

[jrf0110]

Context

Beads currently have two overlapping mechanisms for storing type-specific data:

1. beads.metadata — a freeform JSON text column (text default '{}') on the beads table. Any bead type can stash arbitrary key/value pairs here (e.g. source_bead_id, pr_url, source_agent_id).

2. Satellite metadata tables — typed tables like review_metadata, agent_metadata, escalation_metadata, convoy_metadata that are joined via bead_id foreign key. Each has its own schema with named, typed columns.

Both patterns are actively used. For example, pr_url exists in both review_metadata.pr_url (typed column) AND beads.metadata JSON (written by setReviewPrUrl). The source_bead_id field only exists in beads.metadata but is read by completeReviewWithResult via untyped JSON access.

Problems

• Dual writes: Some fields (e.g. pr_url) must be written to both locations to be visible from both query paths (bead list endpoint reads beads.metadata, review queue reads the JOIN with review_metadata).
• No schema enforcement on metadata JSON: The metadata column is Record<string, unknown> — no compile-time guarantees on what's stored or retrieved.
• Inconsistent access patterns: Some code reads from the satellite table, other code reads from the JSON column, leading to subtle bugs when one is updated but not the other.

Proposal

Decide on one canonical pattern and migrate to it:

• Option A: Lean into satellite tables — remove beads.metadata or restrict it to truly ad-hoc data. Move all typed fields to their satellite tables.
• Option B: Lean into the JSON column — use Zod schemas per bead type to validate the JSON, remove satellite tables, use SQLite generated columns or views for indexed access.
• Option C: Keep both but establish clear rules — satellite tables for indexed/queryable fields, metadata JSON for display-only context. Document the boundary.

References

• cloudflare-gastown/src/db/tables/beads.table.ts — metadata column definition
• cloudflare-gastown/src/db/tables/review-metadata.table.ts — satellite table example
• cloudflare-gastown/src/dos/town/review-queue.ts — setReviewPrUrl writes to both
• cloudflare-gastown/src/dos/town/beads.ts — logBeadEvent, listBeadEvents

Part of #204 (Phase 4: Hardening)