SP3.5 — Cloud Realtime (cross-device live watch*) design spec

Date: 2026-07-20 · Status: DRAFT for user review · Branch: feat/mvp1-personas-authz Type: Feature design → hands off to an implementation plan (superpowers:writing-plans).

Direction pre-decided (SP3 locked decision 5): "Realtime is OUT of SP3 — watch* streams are hydrate-once-per-session; Supabase Realtime cross-device becomes SP3.5 (immediate follow-up)." This spec builds exactly that. No implementation until this written spec is signed off.

1. Summary

SP3 shipped the cloud data path: in DataMode.cloud, CachedStorageAdapter(durable: SupabaseStorageAdapter, cache: InMemoryStorageAdapter) hydrates the household once per session, then serves every read and watch* stream from the in-memory cache. The cache's five broadcast streams (watchMembers / watchChores / watchApprovals / watchLedgerEntries / watchRedemptions) re-emit on this device's writes only — a change committed on another device never reaches this device until the next cold hydrate.

SP3.5 layers Supabase Realtime (postgres_changes) on top so an other-device commit drives the same watch* emissions. The mechanism is deliberately coarse and correctness-first: a change event is a signal to re-read the affected aggregate from PostgREST (under RLS) into the cache, and the cache's existing change-controller re-emits to every watcher. No row payload is trusted; the RLS-scoped re-read is the authority.

This unblocks approval Beat 2: a child, on their own device, sees the celebration when a parent approves their submission remotely — because the child's watchApprovals now re-emits the approval in approved status without a manual refresh.

Zero change to local DataMode (the default). Realtime is constructed only on the cloud branch of createClient; the offline Drift path is untouched — no new cost, no new risk to the free tier.

2. Goals & non-goals

Goals (SP3.5):

• Cross-device live updates for the five shipped watch* streams — the only reactive surfaces the app consumes today.
• A single RLS-scoped realtime channel per household; events re-hydrate the affected aggregate into the cache; the cache re-emits (no watch* signature changes, no new facade methods for consumers).
• A RealtimePort seam so a fake can push synthetic cross-device events and the whole event→re-hydrate→re-emit path is provable headlessly.
• Automatic reconnect/backoff and a post-reconnect gap-closing resync, both from what supabase/realtime_client already give for free plus one resync hook.
• A file-only migration adding the watched tables to the supabase_realtime publication (the controller applies it live).

Non-goals (explicitly OUT, so scope cannot creep):

• Realtime for anything not already watched: companion (member_companion / companion_ledger are read via getCompanion, not a stream), submissions, completions, catalog (rewards/activities/gates), goals, places, entitlements, consents. Adding a table is a one-line follow-up only after a watch* stream exists for it.
• Row-level cache patching from the event payload (the fine/fast/drift-prone option — rejected, see decision R2).
• Offline write queue / cross-device delete reconciliation (a coarse insert re-hydrate does not evict rows deleted on another device; those reconcile on the next cold hydrate — see decision R2 note).
• Any change to local DataMode, to the 50 StoragePort signatures, or to the cache's emission mechanism.
• Presence / broadcast channels, typing indicators, or a bespoke WebSocket.

3. Load-bearing decisions

#	Decision	Rationale
R1	One channel per household, household:<id>, with one onPostgresChanges registration per watched table, each filtered household_id=eq.<id>. Not channel-per-table.	One WebSocket, minimal overhead, uniform teardown. The five tables all carry household_id, so one filter shape scopes every registration.
R2	Coarse re-hydrate per affected table, not row-patching the cache. On an event we re-read that aggregate from PostgREST under RLS and re-insert into the cache; the cache's change-controller re-emits.	Correctness-over-cleverness + modest data volumes (one household). The re-read reuses SP3's proven hydrate path and can never drift from the row payload. Trade-off: a coarse insert-merge does not evict rows deleted on another device — acceptable for MVP (approvals/ledger/redemptions are append/update-only; member/chore deletes reconcile on next cold hydrate).
R3	RLS is the guard on the wire too. SupabaseClient calls realtime.setAuth(userJwt) on every auth change (verified in supabase_client.dart _listenForAuthEvents → _handleTokenChanged → realtime.setAuth). postgres_changes therefore delivers only rows the member's JWT is allowed to see; the re-read is also RLS-scoped. Dual gate holds end-to-end.	The house DUAL GATE rule: realtime must not widen visibility. Even a spoofed event only triggers an RLS-scoped re-read, which returns nothing the member cannot already see.
R4	The CachedStorageAdapter owns the subscription (it owns both the durable it re-reads from and the cache it re-emits into). Presentation never sees realtime.	The house ONE-data-path rule + "presentation never imports supabase". Realtime lives entirely behind the storage seam.
R5	Subscription starts after the first successful hydrate (household id known), idempotently. Teardown via CachedStorageAdapter.dispose() → RealtimePort.dispose() → removeChannel, fanned out from a new Client.dispose().	The household id is the channel key and the RLS scope; it is only known post-hydrate. Reconnect/backoff is realtime_client's built-in RetryTimer (free); a resync on every (re)subscribe closes gaps of events missed while disconnected.
R6	Event bursts are coalesced into a per-adapter pending-table Set, flushed on a short debounce (default 250 ms; injectable Duration.zero for tests).	A single approval commit can fire changes on approvals + ledger_entries at once; coalescing collapses N events into one re-hydrate per affected table per window.
R7	Provable = fake-port-driven; live = manual. A headless publishable-key test cannot attach a second identity's JWT (documented in test/cloud/*_live_test.dart), so true two-device cross-visibility is a manual checklist. What IS automated: a FakeRealtimePort drives the full event→re-hydrate→re-emit path over real services.	Matches the SP3 testing posture: fakes prove the adapter logic; a guarded live smoke + a manual two-identity checklist prove the wire.

4. Architecture

4.1 The seam (RealtimePort)

A pure-Dart interface in adapters/realtime_port.dart (no supabase import, so the cache decorator stays transport-agnostic):

abstract interface class RealtimePort {
  void start({
    required String householdId,
    required void Function(String table) onChange, // coarse: table name only
    required void Function() onResync,             // fires on every (re)subscribe
  });
  Future<void> dispose();
}

• Production SupabaseRealtimePort(SupabaseClient) — one channel household:<id>, five onPostgresChanges(event: all, schema: 'public', table: T, filter: household_id=eq.<id>, callback: (p) => onChange(p.table)) registrations, then .subscribe((status, _) { if subscribed → onResync(); }). dispose() → client.removeChannel(channel).
• Fake FakeRealtimePort (test support) — records the subscription; emit(table) and resync() push synthetic events.

4.2 Data flow (other-device commit → this device's UI)

Device A: parent approves  ──► approvals/ledger_entries rows change (cloud)
                                        │ Postgres logical replication
                                        ▼
Device B: supabase_realtime publication ──► RLS-filtered postgres_changes
                                        ▼
      SupabaseRealtimePort.onChange('approvals')
                                        ▼
      CachedStorageAdapter: coalesce → debounce → _rehydrateTable('approvals')
        = re-read _durable.watchApprovals(id).first  (PostgREST, RLS-scoped)
          → _cache.insertApproval(row)  ──► fires _approvalChanges controller
                                        ▼
      cache.watchApprovals re-emits ──► ApprovalService/Bloc ──► Beat 2 celebration

The re-emission mechanism is unchanged SP3 machinery: the in-memory cache's insert* fires its broadcast StreamController, and watch* asyncMaps a fresh read onto it. SP3.5 only adds a new cause of insert* on the cache (a remote event) alongside the existing one (a local write).

4.3 Table → re-hydrate map (the five watched aggregates)

Publication table	Re-read from durable	Cache write (fires)	Watch stream
household_members	getMembers(id)	insertMember (_memberChanges)	watchMembers
chores	getChores(id)	insertChore (_choreChanges)	watchChores
approvals	watchApprovals(id).first	insertApproval (_approvalChanges)	watchApprovals
ledger_entries	getLedgerEntries(id)	insertLedgerEntry (_ledgerChanges)	watchLedgerEntries
redemptions	getRedemptions(id)	insertRedemption (_redemptionChanges)	watchRedemptions

(watchApprovals has no list getter on the port — approvals are watch-only — so the coarse re-read drains the durable watch's first emission, exactly as SP3's _hydrate already does at line 104 of cached_storage_adapter.dart.)

4.4 Lifecycle wiring (createClient, Client.dispose)

• createClient cloud branch builds the data port with a realtime port: CachedStorageAdapter(durable: SupabaseStorageAdapter(supabaseClient), cache: InMemoryStorageAdapter(), realtime: SupabaseRealtimePort(supabaseClient)). The same supabaseClient that carries the auth JWT is reused (so realtime.setAuth is already wired) — not a second client.
• Local branch passes no realtime (null) → _startRealtime no-ops → identical behaviour to today.
• A new Disposable marker interface (Future<void> dispose()) is implemented by CachedStorageAdapter; a new Client.dispose() fans out to the storage if it is Disposable. The app calls client.dispose() before dropping a cloud client (sign-out / data-mode switch); the local/NoopAuth path's storage is not Disposable, so dispose() is a no-op there.

4.5 Server-side prerequisite (file-only migration)

supabase_realtime publication must include the five watched tables, and each gets REPLICA IDENTITY FULL so the household_id=eq filter matches on UPDATE and DELETE payloads (default replica identity carries only the PK). The migration is file-only (infra/supabase/migrations/); the controller applies it live after review and verifies which tables are already published (pg_publication_tables).

5. Domain rules

• No new domain logic. SP3.5 is transport plumbing behind the storage seam; services, the facade, and every watch* signature are untouched.
• RLS is not widened. The realtime channel and every re-read run under the member's JWT. A cross-household event is impossible (the household_id filter plus RLS), and even an injected event only triggers an RLS-scoped re-read.
• Idempotent, monotone re-emit. Re-hydrating an aggregate re-inserts current rows; the fold-based projections (wallet balance, approval status) recompute from the same rows, so a duplicate event is harmless.
• Append-only surfaces stay append-only. The re-hydrate path only ever calls insert* on the cache (never a delete), so the ledger/redemption invariants are preserved on the realtime path too.

6. Testing

Adapter unit (FakeRealtimePort, no network):

• other-device write → emit('household_members') → after debounce, watchMembers re-emits the new roster (the core proof);
• burst coalescing: two emits within the window → one re-hydrate per table;
• resync() (reconnect) re-hydrates all five tables and re-emits;
• dispose() tears down the fake and cancels the debounce timer;
• local mode (realtime: null) → no subscription, behaviour unchanged.

End-to-end SDK (real services over CachedStorageAdapter, Duration.zero debounce):

• two "devices" share one durable (the cloud); device A approves a submission (approval → approved, ledger entry written) directly on the shared durable; fakeRealtime.emit('approvals'); emit('ledger_entries') → device B's watchApprovals emits the approved approval and B's wallet reflects the new ledger entry — Beat 2, headless.

Guarded live smoke (@Tags(['live']), skipped without creds): a single-process subscribe-and-round-trip against bgedvvmihygwxhjxlvfu proving the real postgres_changes wire delivers an event after a self-write.

Manual two-identity checklist: two real devices/sessions, parent approves on A, child sees Beat 2 on B — the cross-visibility a headless publishable key cannot attach a second JWT to prove.

Baselines: SDK 1114 / app 703 / DS 287 must not drop — new tests add on top.

7. File map

New:

• infra/supabase/migrations/20260720000100_realtime_publication.sql — publication + replica identity.
• packages/client_sdk/lib/src/adapters/realtime_port.dart — RealtimePort interface.
• packages/client_sdk/lib/src/adapters/cloud/supabase_realtime_port.dart — SupabaseRealtimePort + kRealtimeTables.
• packages/client_sdk/test/support/fake_realtime_port.dart — the fake.
• packages/client_sdk/test/cached_realtime_test.dart — adapter unit suite.
• packages/client_sdk/test/cloud/realtime_beat2_test.dart — end-to-end Beat 2.
• packages/client_sdk/test/cloud/realtime_live_test.dart — guarded live smoke.
• docs/decisions/2026-07-20-sp35-cloud-realtime-manual-checklist.md — two-identity manual proof.

Modified:

• packages/client_sdk/lib/src/adapters/adapter.dart — add Disposable marker.
• packages/client_sdk/lib/src/adapters/cached/cached_storage_adapter.dart — realtime field, start/coalesce/debounce/re-hydrate/resync/dispose.
• packages/client_sdk/lib/src/client/create_client.dart — cloud branch injects SupabaseRealtimePort.
• packages/client_sdk/lib/src/client/client.dart + client_impl.dart — Client.dispose() → storage Disposable.
• packages/client_sdk/lib/ports.dart — export RealtimePort (adapter-implementer surface).

8. Global constraints (inherited by every implementation task)

• Dart/Flutter monorepo, FVM only (fvm flutter / fvm dart, never bare, never node for Dart tooling).
• ONE data path: Bloc → Repository → Client facade → Service → Adapter. Realtime lives inside the Adapter layer; presentation imports only the facade and never supabase.
• Migrations are FILE-ONLY in infra/supabase/migrations/; the controller applies them live after review. Never call a Supabase apply tool.
• Anon/publishable key only in the app; service-role only inside Edge Functions.
• DUAL GATE: RLS is the real guard — realtime must not widen visibility; every event and re-read is RLS-scoped to the household (the channel filter is belt-and-suspenders).
• No behaviour change for local DataMode (the default): realtime is cloud-only, zero cost/risk to the offline path.
• Explicit git add <files> — never -A; never stage graphify-out/, .superpowers/, .claude/.
• Suite baselines SDK 1114 / app 703 / DS 287 must not drop; new tests add on top.
• After code changes, run graphify update . (final task).
• Codegen: no Drift table is added by SP3.5 (no watched table changes shape), so no build_runner run is required; if one is ever needed, scope it with --build-filter and restore clobbered .g.dart siblings from HEAD.

9. Sequencing

Focused phase, not an epic. Order: (1) publication migration → (2) RealtimePort

• fake + SupabaseRealtimePort → (3) CachedStorageAdapter event→re-hydrate→emit
• debounce → (4) createClient lifecycle + Client.dispose teardown → (5) fake-driven end-to-end Beat 2 test → (6) guarded live smoke + manual checklist → (7) docs refresh + graphify update . + baseline verification. Each task is TDD (red → green) and self-contained; the plan carries complete code.