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

# Design — migrate-canvas-realtime-to-broadcast

> Companion to proposal.md. Records `D-BR-N` and the contracts the
> implementation must honour.

## D-BR-1 — Split P7a prototype + P7b rollout, hard soak gate

```
P7a — canvas_messages only — ships in one PR
   ↓ one week of synthetic-monitor soak
   ↓ p95 ≤ 2s on bridge-latency metric
P7b — remaining 5 tables — ships in a second PR
```

**Why split?** The trigger pattern is identical across all six tables;
the risk is in the bridge's broadcast-path implementation. P7a derisks
the bridge in isolation; if it fails, only one table is rolled back.

**Soak gate hard rule:** P7b's PR cannot open until the
synthetic-monitor's `bridge_latency_p95_ms` metric has been ≤ 2000 for
seven consecutive days. If the rule trips, P7a's trigger or bridge logic
is audited before retry.

## D-BR-2 — Channel naming: `canvas:<id>:<table>`

```ts
// packages/state/src/actors/supabase-realtime-bridge.ts
export function canvasChannelName(canvasId: string, table: string): string {
  return `canvas:${canvasId}:${table}`;
}
```

The same helper is referenced from the SQL trigger via a generated
constant (or hard-coded with a documented coupling — see D-BR-5).

**Why include the table in the topic?** Per-table topics let the bridge
subscribe selectively (e.g., a chat-only client doesn't need
`canvas_files` events). Per-canvas topics (without table) would force
every subscriber to receive every table's events.

**Why not include the operation?** INSERT vs UPDATE vs DELETE is part
of the event payload; splitting topics by operation would multiply
subscriptions 3×.

## D-BR-3 — Bridge conditional path, keyed by channel name

```ts
// packages/state/src/actors/supabase-realtime-bridge.ts
function subscribeToCanvas(canvasId: string) {
  const channels = HOT_TABLES.map((table) => ({
    name: canvasChannelName(canvasId, table),
    handler: handleBroadcastEvent,
  }));
  // Non-HOT tables continue via postgres_changes
  return supabase.channel('canvas:' + canvasId)
    .on('broadcast', { event: '*' }, handleBroadcastEvent)  // HOT (D-BR-1, P7a+P7b)
    .on('postgres_changes', { event: '*', schema: 'public', filter: 'canvas_id=eq.' + canvasId }, handleLegacyEvent)
    .subscribe();
}

const HOT_TABLES = [
  'canvas_messages',         // P7a
  'canvas_files',            // P7b
  'canvas_project_instructions',  // P7b
  'canvas_invitees',         // P7b
  'canvas_sessions',         // P7b
  'research_runs',           // P7b
] as const;
```

P7a ships with only `canvas_messages` in `HOT_TABLES`; P7b appends the
remaining 5. The bridge supports both paths during the soak — the
postgres_changes path stays as the legacy listener for any HOT table
not yet broadcast-migrated.

## D-BR-4 — Visibility refetch wires into broadcast topic

```ts
// packages/state/src/actors/supabase-realtime-bridge.ts
document.addEventListener('visibilitychange', () => {
  if (document.visibilityState === 'visible') {
    HOT_TABLES.forEach((table) => {
      void resyncBroadcastTopic(canvasChannelName(currentCanvasId, table));
    });
  }
});
```

`resyncBroadcastTopic` fires a single request to the broadcast channel
asking for the recent-events window since the last seen event-id; events
are replayed to the local store deterministically.

**Why on every HOT table not just the chat one?** A tab-switch can
miss events on any HOT table; refetching only `canvas_messages` leaves
the file-list / invitee-list stale. The cost is one extra request per
table per visibility transition — negligible.

**Why only on `visible`, not `hidden`?** `hidden` doesn't need a
refetch — no events are missed, the page just goes idle. The
`L335/L561` gap was specifically about the visible-transition path.

## D-BR-5 — Trigger SQL ↔ JS helper coupling: documented, not generated

```sql
-- supabase/migrations/<ts>_canvas_messages_broadcast.sql
-- Topic name MUST stay in lockstep with
-- packages/state/src/actors/supabase-realtime-bridge.ts::canvasChannelName
PERFORM realtime.broadcast_changes(
  topic := 'canvas:' || NEW.canvas_id::text || ':messages',
  ...
);
```

The string `canvas:<id>:<table>` lives in two places: the SQL trigger
and the JS helper. They MUST match. Options considered:

1. **Code-generate the trigger SQL** from the JS helper. Rejected:
   build-time dependency from `supabase/migrations/` on `packages/state/`
   crosses our boundary table (CLAUDE.md § Package boundaries).
2. **Read the topic from a config table.** Rejected: adds a query to
   the hot path; reading on every event is wasteful.
3. **Document the coupling + a conformance test that asserts
   round-trip.** Selected. Comments at both sites cross-reference.

**Conformance assertion:** the bridge subscribes to the SQL-defined
topic and asserts an INSERT-then-receive round-trip succeeds; if the
topic name drifts, the round-trip fails.

## D-BR-6 — Migration safety: `CREATE OR REPLACE` triggers, no destructive drops

```sql
-- Idempotent migration; safe to re-apply
DROP TRIGGER IF EXISTS canvas_messages_broadcast_trigger ON canvas_messages;
CREATE TRIGGER canvas_messages_broadcast_trigger
  AFTER INSERT OR UPDATE OR DELETE ON canvas_messages
  FOR EACH ROW EXECUTE FUNCTION realtime_canvas_messages_broadcast();
```

* `DROP TRIGGER IF EXISTS` first — re-apply safety.
* No `DROP FUNCTION` — function is `CREATE OR REPLACE` to avoid breaking
  any in-flight transaction holding a reference.
* No table mutations — the migration is trigger-only.

**Rollback path:**

```sql
DROP TRIGGER IF EXISTS canvas_messages_broadcast_trigger ON canvas_messages;
DROP FUNCTION IF EXISTS realtime_canvas_messages_broadcast();
```

Then revert the bridge to the postgres_changes-only path. No DB-side
state to clean up; broadcast topics are ephemeral.

## D-BR-7 — Conformance test SLOs: bridge-latency p95/p99

```ts
// apps/studio/src/__tests__/realtime-conformance.test.ts
const SLOS = {
  p95Ms: 2_000,
  p99Ms: 5_000,
} as const;

test.each(HOT_TABLES)('%s broadcast latency p95 ≤ 2s, p99 ≤ 5s', async (table) => {
  const samples = await measureBroadcastLatency({
    table,
    operationCount: 1_000,
  });
  expect(percentile(samples, 95)).toBeLessThanOrEqual(SLOS.p95Ms);
  expect(percentile(samples, 99)).toBeLessThanOrEqual(SLOS.p99Ms);
});
```

* p95 ≤ 2s — the chat-readiness 10/10 target.
* p99 ≤ 5s — tolerates one-in-a-hundred Cloudflare cold-start tail.
* 1000 operations per table — enough samples for stable percentile.

The test runs against a staging Supabase instance; CI seeds a fresh
canvas, hammers the table, measures.

## Open questions — all closed at proposal time

* **Q-BR-1 (closed):** Migrate all 6 tables in one PR or split? **Split
  P7a + P7b with a soak gate.** D-BR-1 — derisks the bridge before the
  rollout.
* **Q-BR-2 (closed):** Per-canvas topic or per-table topic? **Per-table
  inside per-canvas.** D-BR-2 — selective subscription.
* **Q-BR-3 (closed):** Drop postgres_changes path on P7b or keep dual?
  **Keep dual for HOT tables not yet on broadcast.** During soak the
  bridge tolerates both paths; after P7b the dual path simplifies.
* **Q-BR-4 (closed):** Visibility refetch on every HOT table or only
  chat? **Every HOT table.** D-BR-4 — partial refetch leaves
  file-list / invitee-list stale.
* **Q-BR-5 (closed):** Code-generate trigger SQL from JS helper?
  **No.** D-BR-5 — boundary violation; documented coupling + round-trip
  conformance is the right tradeoff.
