<!-- source: openspec/changes/migrate-canvas-realtime-to-broadcast/proposal.md -->

# Migrate canvas realtime to broadcast_changes — HOT-tier transport upgrade

> **⚠ Schema-rename overlay — RAP-1170 (Wave 1, 2026-05-22).** This change was drafted against the pre-rename `canvas_*` table names. Before implementing, apply the rename map from `openspec/current/canvas.md § Naming clarification`: `canvas_sessions → ideas`, `canvas_messages → messages`, `canvas_files → files`, `canvas_invitees → spec_invitees`, and `canvas_ideas` / `canvas_attachments` / `canvas_instructions` / `canvas_tool_uses` / `canvas_stage_events` / `canvas_daily_costs` dropped — see the post-rename schema in `openspec/current/db/entities.md § Project → spec → idea hierarchy`. The six tables this proposal lists for migration map post-rename to `messages`, `files`, `spec_instructions` (if retained — verify), `spec_invitees`, `ideas`, and `research_runs` (unchanged). Inline `canvas_*` references in this folder's `proposal.md` / `design.md` / `tasks.md` / `verification.md` are deliberately left unmodified to preserve drafting context.

> **Status:** proposed.
> **Owner:** Pavel Rapoport <pavel@rapoport.studio>.
> **Linear issue:** RAP-1098 (epic RAP-1081 — chat readiness 10/10, phase P7; split P7a prototype + P7b rollout).
> **Opened:** 2026-05-20.
> **Blocked by:** RAP-1091 (P4 — a11y matrix), RAP-1096 (P6 — synthetic monitor; required for the P7a soak gate).
> **Blocks:** RAP-1099 (P8 — polish; virtualization perf assumes broadcast-tier message delivery).
> **Affects capability specs (at archive):** `openspec/current/canvas.md` § Realtime transport; `openspec/current/frontend-runtime.md` § visibility-refetch path (L335/L561 — currently 5/6 conforming).
> **Affects code:** new `supabase/migrations/<ts>_canvas_messages_broadcast.sql` (P7a) + `<ts>_canvas_remaining_broadcast.sql` (P7b); `packages/state/src/actors/supabase-realtime-bridge.ts` (conditional broadcast path + visibility-refetch wiring); new `apps/studio/src/__tests__/realtime-conformance.test.ts`.
> **Blast radius:** medium-high. Migrates the chat hot path from `postgres_changes` to `broadcast_changes`. P7a derisks via a single-table prototype + one-week soak before P7b touches the remaining 5 tables. Rollback path: revert the bridge's conditional channel path and drop the new trigger; no schema removal needed.
> **Capability prefix:** `BR` (Broadcast). Decisions ratify as `D-BR-N`.

---

## Why

`frontend-runtime.md` Open Q5 mandates a prototype-first migration to `broadcast_changes`. The HOT-tier transport currently rides `postgres_changes`, which has three documented frailties on six canvas tables (`canvas_messages`, `canvas_files`, `canvas_project_instructions`, `canvas_invitees`, `canvas_sessions`, `research_runs`):

1. **Per-row WAL pressure.** Every UPDATE on `canvas_messages` (token-by-token status flips during streaming) writes a WAL record. At studio scale this is fine; at 100× scale the WAL is the bottleneck before the message bus is.
2. **No replay semantics.** `postgres_changes` delivers each row once; if the client misses an event during a reconnect, the bridge must do a full table refetch. `broadcast_changes` keeps an in-memory recent-events window per channel.
3. **Higher tail latency.** Supabase Realtime's `broadcast_changes` is benchmarked at ~2× lower p99 vs `postgres_changes` under contention — the chat-readiness 10/10 target requires the lower number.

P7 closes this. P7a derisks with a single-table prototype (`canvas_messages` — the highest-traffic table). One week of P6's synthetic monitor against the prototype channel must show p95 ≤2s before P7b touches the remaining 5 tables in a single consolidated migration.

The same change also lands the missing `visibilitychange → 'visible'` refetch path (`frontend-runtime.md` L335/L561). Today 5/6 paths conform; this change brings the sixth into compliance.

## Scope

Two sub-changes shipped behind one OpenSpec proposal.

### P7a — `canvas_messages` prototype + visibility-refetch

**Migration** — `supabase/migrations/<ts>_canvas_messages_broadcast.sql`:

```sql
-- Trigger that fires realtime.broadcast_changes on canvas_messages mutations
CREATE OR REPLACE FUNCTION realtime_canvas_messages_broadcast()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  PERFORM realtime.broadcast_changes(
    topic       := 'canvas:' || COALESCE(NEW.canvas_id, OLD.canvas_id)::text || ':messages',
    operation   := TG_OP,
    table_name  := TG_TABLE_NAME,
    schema_name := TG_TABLE_SCHEMA,
    new_record  := NEW,
    old_record  := OLD
  );
  RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER canvas_messages_broadcast_trigger
AFTER INSERT OR UPDATE OR DELETE ON canvas_messages
FOR EACH ROW EXECUTE FUNCTION realtime_canvas_messages_broadcast();
```

**Bridge** — `packages/state/src/actors/supabase-realtime-bridge.ts`:

* Conditional channel path keyed by channel name. `canvas:${id}:messages` → broadcast path; all other channels → existing `postgres_changes` path.
* On `visibilitychange → 'visible'`, fire a single resync against the broadcast topic to drain any missed events from the recent-events window. This closes the L335/L561 conformance gap.

**Conformance test** — `apps/studio/src/__tests__/realtime-conformance.test.ts`:

* Asserts p95 ≤ 2s on the prototype channel under sustained INSERT/UPDATE traffic.
* Asserts the visibility-refetch path fires exactly once per `visible` transition.

### P7b — remaining 5 HOT tables (gated on P7a soak)

**Soak gate:** P7a must hold p95 ≤ 2s for one week on the synthetic monitor (P6 / RAP-1096) before P7b ships. If the soak fails, P7b is held and the prototype's contract is audited.

**Migration** — `supabase/migrations/<ts>_canvas_remaining_broadcast.sql`:

* Identical trigger pattern across `canvas_files`, `canvas_project_instructions`, `canvas_invitees`, `canvas_sessions`, `research_runs`.
* Topic naming: `canvas:${id}:<table>` (e.g., `canvas:abc-123:files`).

**Bridge consolidation:**

* The conditional path collapses — all six tables now route through the broadcast channel. The legacy `postgres_changes` path remains for non-HOT tables (`brands`, `audit_log`, etc.) which stay on the existing transport.

**Conformance test extension:**

* `realtime-conformance.test.ts` extends assertions to all 6 tables.
* Asserts p95 ≤ 2s and p99 ≤ 5s across all six.

## Out of scope

* Migrating non-HOT tables (`brands`, `audit_log`, `consent_events`, etc.) — those stay on `postgres_changes`; cost/benefit doesn't justify a migration.
* Replacing the bridge wholesale with a different state library — bridge stays; only the channel-selection logic flips.
* Cross-canvas pub/sub (e.g., a notification topic per user) — separate scope, not gated on broadcast adoption.
* Service-role broadcasts for system events (build completion, etc.) — separate scope, separate audit surface.

## Risks

* **Trigger overhead.** `AFTER` row triggers run inside the writing transaction. Mitigation: benchmark INSERT throughput before/after the trigger lands on staging; if the trigger adds >10% INSERT latency, switch to a `DEFERRED` trigger or batch via NOTIFY.
* **Channel-name drift.** The bridge derives channel names from canvas_id; any mismatch silently drops events. Mitigation: a single helper `canvasChannelName(canvasId, table)` shared between client + trigger lookup; conformance test asserts round-trip.
* **Soak misread.** Synthetic monitor measures TTFT/throughput/settle (P6 metrics), but the realtime-bridge latency is a different signal. Mitigation: P7a's conformance test adds bridge-latency to the synthetic monitor's metric set; the soak gate reads bridge-latency, not just chat metrics.
* **Migration partial-apply on rollback.** If P7b's migration applies the trigger before the bridge consolidation lands, events fire on broadcast but the bridge listens on postgres_changes. Mitigation: deploy order is bridge → trigger (bridge tolerates both paths; trigger goes live after the bridge already listens on broadcast).

## Verification

See `verification.md` — file-existence gates + lint/test gates + reviewer-confirms for the four risk points above + the soak gate.

## Lessons consulted

* `supabase_mcp_timestamp_reconcile.md` — `schema_migrations.version` reconcile after MCP apply.
* `supabase_drift_recovery_2026_05_15.md` — verify drift-guard truth post-migration.
* `ci_monitor_noise_patterns.md` — P7a soak signal must be statistically meaningful, not single-run noise.
* `forge_plan_staleness_on_issue_update.md` — Linear edits before forge plan.
