[Gastown] PR 24: Postgres Replication Layer

[jrf0110]

Parent: #204 | Phase 4: Hardening

No major architectural changes — sync-on-write from DOs to Postgres works the same regardless of execution model.

Goal

Add Postgres as a read replica for efficient cross-rig queries, analytics, and dashboard performance at scale. DO SQLite remains the authoritative store.

Schema

Postgres tables mirroring DO state:

• gastown_towns — town metadata with owner (user or org)
• gastown_rigs — rig metadata with git URL, config
• gastown_agents — agent state, role, identity, container process info
• gastown_beads — bead state, assignments, convoy membership
• gastown_convoys — convoy progress tracking
• gastown_mail — mail history
• gastown_bead_events — append-only event ledger

Sync Strategy

• Sync-on-write from DOs to Postgres (on each state mutation in Rig/Town DO)
• Postgres is eventually consistent (acceptable for dashboard reads)
• DOs remain authoritative for all agent-facing operations

Migration

• Migrate dashboard tRPC reads from Gastown worker HTTP API to direct Postgres queries
• Enables efficient cross-rig aggregations, analytics, and search
• Required for Agent CVs & Performance Analytics at scale

Dependencies

• All Phase 1–3 PRs (system must be validated before adding Postgres)

Acceptance Criteria

• Postgres schema and drizzle migration
• Sync-on-write logic in Rig DO and Town DO
• tRPC routes migrated to read from Postgres
• Backfill script for existing DO data
• Consistency verification tooling (DO vs Postgres spot checks)