Skip to main content

SP3 — Cloud Data Adapter design spec (2026-06-24)

Status: DRAFT for user review. Design approved in direction 2026-06-24 (the user answered every fork with the choices recorded under "Locked decisions"). No implementation until the user signs off on this written spec. Next step after sign-off: superpowers:writing-plans.

Goal: Deliver full runtime zero-trust. A subscribed household reads and writes its data through Supabase PostgREST under RLS (the household is the tenant key); a free household stays offline on local Drift. One write-through cache decorator hosts both — only the durable port swaps. SP1 built the SDK domain + local adapter; SP2 wired real GoTrue auth (anon/publishable key only, no service-role in the app). SP3 is the last piece that makes the app actually transact against the cloud.

Prerequisites already met: all 16 app migrations + RLS applied and proven on project bgedvvmihygwxhjxlvfu; household-scoped policies via member_household_ids() / parental_household_ids() keyed to auth.uid(); cross-household isolation smoke passed both directions (see RESUME doc + memory rewhaven-rebuild-backend-topology).

Locked decisions (from the user, 2026-06-24)​

  1. Hybrid topology, offline-first for free users. Free / no cloud_sync → Drift durable (offline). Subscribed → Supabase durable (cloud, multi-device).
  2. Identity keys on the member profile, not the account. All wallet/history records FK to member_id; the account link (auth_user_id) lives only on the member profile and is nullable. (Verified against the live schema — see below.)
  3. Free→paid upload migration is IN SCOPE for SP3.
  4. Migration mechanism = client-side under RLS, idempotent (no service-role anywhere; logic stays in the pure-Dart SDK).
  5. Realtime is OUT of SP3 — watch* streams are hydrate-once-per-session; Supabase Realtime cross-device becomes SP3.5 (immediate follow-up).
  6. Two member-profile creation paths (user clarification 2026-06-24): account-first (profile created with auth_user_id set at that moment) and profile-first/shadow (auth_user_id null, claimed later). Both unified by the nullable column.

The identity / linkage model (verified against the live schema)​

  • Every history/wallet record — ledger_entries, goals, chore_submissions, chore_completions, redemptions, token_batches, spend_requests, approvals — has member_id → household_members(id). A wallet is derived, not stored (fold ledger_entries by member_id + bucket).
  • The only account link is household_members.auth_user_id → auth.users(id), nullable. Setting it (at profile creation, or later when a shadow member claims an account) makes that profile's entire history belong to the account with zero data migration — member_id never changes.
  • We deliberately do not add a user/account FK to each record — that would re-couple history to the account and break the kid-gets-an-account case.
  • RLS scopes by household (member_household_ids()), so any linked member sees the whole household — the intended zero-trust tenant model.

Architecture​

A. Topology — one decorator, swappable durable​

CachedStorageAdapter (existing, unchanged) is a write-through, cache-first StoragePort decorator: writes go durable-first then to an in-memory cache; reads and watches serve from the cache; the durable is hydrated once on first use. SP3 changes only which port is injected as durable:

TierdurableCacheNetworkBuilt by
Free / no cloud_syncLocalStorageAdapter (Drift)in-memorynone (offline)SP1 (exists)
SubscribedSupabaseStorageAdapterin-memoryPostgREST + RLSSP3

All 50 StoragePort signatures and the cache layer are untouched.

B. SupabaseStorageAdapter implements StoragePort (the new component)​

  • Lives under packages/client_sdk/lib/src/adapters/cloud/, split by aggregate group to keep files <400 lines (mirrors the local adapter's grouping): households+members, chores+submissions+completions, economy (approvals/ledger/token_batches/budget/ spend_requests/redemptions), rewards+activities+gates, goals+places, entitlements.
  • Each method maps to a PostgREST call on the same SupabaseClient already built for auth in createClient (reused — not a second client; it carries the user JWT, so RLS applies automatically).
  • Row mappers (*_rows.dart): camelCase model ↔ snake_case column (tokenValue↔token_value, homePlaceId↔home_place_id, …). This is the bulk of the work and the main correctness risk; the plan verifies every model field ↔ a column, model-by-model, against the live information_schema (via Supabase MCP).
  • Zero business logic in the adapter (port contract §6). Explicit .eq('household_id', …) filters are belt-and-suspenders; RLS is the real guard.
  • Ledger surface stays append-only — only insertLedgerEntry + reads exist to implement; there is no update/delete signature (spec invariant 2 + §8.9).
  • Watch streams (SP3): watchMembers/Chores/Approvals/LedgerEntries/Redemptions are served by the cache after a one-time hydrate; they reflect this device's writes live. Cross-device live updates are SP3.5 (Realtime). The adapter's watch* returns a single-emission stream seeded from the initial PostgREST read (the cache decorator drives subsequent emissions from local writes).

C. createClient wiring — the data-mode signal​

  • Add ClientConfig.dataMode (enum DataMode { local, cloud }, default local). cloud requires api != null (needs the authed SupabaseClient); asserting otherwise throws an ArgumentError at construction.
  • The app sets dataMode: cloud when the user holds cloud_sync. We cannot derive it from the entitlements row — that row lives in the store we're selecting (chicken-and-egg); the app already knows subscription state from its account/billing layer. (Default stays local, so every existing entrypoint is unaffected.)
  • createClient: when dataMode == cloud, build CachedStorageAdapter(durable: SupabaseStorageAdapter(supabaseClient), cache: InMemoryStorageAdapter()) instead of the local store; auth wiring (SP2) is unchanged.

D. Free→paid upload migration (client-side, RLS, idempotent)​

Triggered when a free/local user subscribes. Runs in the pure-Dart SDK under the user's JWT — no service-role.

  1. User signs up / signs in (SP2 GoTrue) → auth.uid() available.
  2. Upload preserving local UUIDs (the SDK already mints RFC-4122 v4 UUIDs via IdGenerator, and every cloud PK is uuid — explicit-id inserts keep every FK valid, zero reconciliation). Order is FK-topological: households → self as first parent member with auth_user_id = auth.uid() (satisfies the household_members_insert bootstrap clause) → places → chores → rewards → activities → activity_gates → budget_categories → goals → chore_submissions → chore_completions → spend_requests → redemptions → approvals → token_batches → entitlements → ledger_entries last, in created_at order (so the zero-floor trigger never trips — the local store already guaranteed the invariant, replaying chronologically reproduces only valid states). After the self-parent insert, parental_household_ids() covers all later writes.
  3. Idempotent: every insert is upsert-on-id (on conflict (id) do nothing), so a mid-upload crash resumes cleanly on re-run.
  4. Gated cutover: the durable flips to cloud only after a verified-complete upload (row-count parity check per aggregate). The app then persists "this household is cloud" and re-creates the Client in dataMode: cloud.
  5. Not atomic across tables, but resumable + gated cutover make that safe. The upload is encapsulated behind a single SDK seam (CloudMigrationService / client.migrateLocalHouseholdToCloud()), so a transactional Edge Function could replace step 2 later without touching callers.

E. Member-profile creation paths (identity, both supported)​

  • Account-first: when an account is marked a member (the subscriber's own owner profile at migration; a co-parent/adult joining), the member row is created with auth_user_id set at that moment. SP3 adds the SDK path that sets the link on insert.
  • Profile-first / shadow: a child member is created with auth_user_id = null; when they later sign up, client.linkAccountToMember(memberId, authUserId) sets the link on the existing row — history (keyed by member_id) is untouched.

F. Invariants & error mapping​

The SQL twins are already live and are the last line of defence: zero-floor trigger, append-only ledger (no update/delete RLS policies), household RLS. The adapter maps Postgres/PostgREST errors → existing SDK domain exceptions so cloud behaves identically to local:

  • zero-floor check_violation (errcode 23514 / the trigger's message) → the same exception LedgerService throws locally (zero-floor failure).
  • RLS denial / a write that affects zero rows → a controlled AuthorizationFailure (mirrors SP2's _wrap: no raw PostgREST body / PII to the UI).
  • Other PostgREST errors → a generic controlled StorageFailure; raw errors are logged via dart:developer, never surfaced.

Architecture rules preserved (load-bearing)​

  • One data path: Bloc → Repository → Client facade → Service → Adapter. Domain rules stay in services; the cloud adapter has zero logic.
  • client_sdk stays pure Dart — package:supabase is pure-Dart; no flutter_* dep enters the SDK. flutter_secure_storage remains an app-only dep (SP2).
  • Presentation imports only the facade; the cloud adapter stays src-private; the barrel is unchanged.
  • Config-driven construction (createClient({config})) — never inject an adapter.

Testing​

  • Adapter unit tests against a fake PostgREST (an in-memory SupabaseClient double or a thin PostgrestClient fake) — no network; assert each method's query shape + round-trip mapping.
  • Row-mapper tests: every model ↔ row, both directions, including the migration-012–016 fields (tempBonus, estimateMin, assignedMemberIds, stepsPerMember, roomAssignees, home_place_id, watch_only, goal lifecycle/media).
  • Migration tests: seed a local Drift household with every aggregate → run upload → assert cloud parity + derived balances; re-run → assert idempotent (no dupes); inject a mid-upload failure → assert resume completes.
  • Guarded integration smoke against bgedvvmihygwxhjxlvfu (behind the creds flag, extends the SP2 smoke): create a cloud household, round-trip an aggregate, assert RLS isolation across two users.
  • Anti-corruption guardrails on every subagent: no destructive git, explicit git add <file>, no *_test.dart deletions, assert suite count ≥ baseline; scope any build_runner run + restore clobbered .g.dart siblings.

Proposed phasing (for the plan, after sign-off)​

  • P1 — Config seam: ClientConfig.dataMode + createClient branch (still wired to local until P2 lands the adapter); construction-time guard tests.
  • P2 — SupabaseStorageAdapter + row mappers: all aggregates, unit-tested against a fake PostgREST; reads/writes/watch (hydrate-once). The bulk of the work.
  • P3 — Error mapping: Postgres → SDK domain exceptions; behavioural parity tests vs the local adapter.
  • P4 — Member-profile linkage paths: account-first insert + linkAccountToMember; tests.
  • P5 — CloudMigrationService (free→paid upload): ordered idempotent upload + gated cutover; migration + resume tests.
  • P6 — Integration smoke + docs: guarded live smoke; refresh the auth/infrastructure architecture pages (cloud data path now built); graphify update ..

Out of scope (tracked follow-ups)​

  • Realtime / cross-device live updates → DELIVERED by SP3.5 (Supabase Realtime on the five watch* streams — see docs/superpowers/specs/2026-07-20-sp35-cloud-realtime-design.md).
  • Offline support for subscribers (Drift-as-cache-with-sync under cloud-durable) — a later enhancement; SP3 subscribers are cloud-durable (cold start needs network).
  • Transactional Edge-Function migration (the client-side seam leaves room for it).
  • Co-parent invite acceptance UX (its own effort; SP3 supplies the account-first member-link primitive it will use).
  • Billing / subscription detection itself (the app owns it; SP3 only consumes dataMode).

Risks​

  • Row-mapper completeness is the top risk — a missed field silently drops data. The plan verifies every field against the live information_schema and tests both directions. (HIGH, mitigated by per-model tests.)
  • Migration partial failure — mitigated by upsert-on-id idempotency + gated cutover
    • resume test. (MEDIUM.)
  • getHousehold() semantics in cloud — returns the single household the authed user belongs to (RLS-scoped); a no-household account returns null → SetupRoute (SP2 behaviour holds). Confirm the cache hydrate path tolerates a null household. (LOW.)