[FEAT] Add Supabase persistence and real-time sync

[divideby0]

[FEAT] Add Supabase persistence and real-time sync

Context

This would ship as a separate package (@decisioncc/storage-supabase) — the core @decisioncc/react-pugh-matrix component has no Supabase dependency. The core ships with memory (default, no persistence) and localStorage adapters.

Eventual plans include a @decisioncc/storage-convex adapter, but that's lower priority.

What Supabase Enables

1. Persistent storage

Events and commits stored in Postgres. Matrices survive across devices and sessions.

2. User authentication

Supabase Auth provides user identity. Events are attributed to authenticated users (not 'anonymous'). Enables:

• Per-user score attribution
• Access control (viewer/editor/admin per matrix)
• Audit trail of who changed what

3. Real-time collaboration

Supabase Realtime broadcasts changes. When one user scores a cell, all connected clients see the update immediately. Enables:

• Live co-editing
• Presence indicators (who's viewing)
• Conflict detection (same cell scored simultaneously)

Storage Schema

Uses the ObjectStore + RefStore interfaces from the branching refactor:

create table matrices (
  id uuid primary key default gen_random_uuid(),
  slug text unique,
  owner_id uuid references auth.users,
  allow_negative boolean default false,
  default_scale jsonb default '{"kind":"numeric","min":1,"max":10,"step":1}',
  created_at timestamptz default now()
);

create table matrix_objects (
  id text primary key,                    -- UUIDv7
  matrix_id uuid references matrices,
  kind text not null,                     -- 'event' | 'commit'
  branch_id text not null,               -- partition events by branch
  data jsonb not null,
  created_at timestamptz default now()
);

create table matrix_refs (
  matrix_id uuid references matrices,
  name text not null,
  commit_id text references matrix_objects(id),
  type text default 'branch',
  primary key (matrix_id, name)
);

create table matrix_collaborators (
  matrix_id uuid references matrices,
  user_id uuid references auth.users,
  role text check (role in ('viewer', 'editor', 'admin')),
  primary key (matrix_id, user_id)
);

Real-Time Flow

1. User dispatches event → committed locally (optimistic)
2. Supabase persister writes to matrix_objects
3. Supabase Realtime broadcasts insert to other clients
4. Other clients append event → re-project domain state

Conflict handling

With event sourcing, conflicts = same cell scored by different users at ~the same time. Resolution:

• Default: Last-write-wins by UUIDv7 ordering (timestamp-embedded)
• Future: Merge UI showing conflicting scores for manual resolution

Offline support

Queue events in memory/localStorage → flush to Supabase on reconnect. The ObjectStore interface supports this naturally — local writes succeed immediately, sync is async.

Acceptance Criteria

• @decisioncc/storage-supabase package with ObjectStore + RefStore implementations
• Supabase Auth integration — events attributed to authenticated users
• Real-time sync via Supabase Realtime subscriptions
• Optimistic local updates with async persistence
• Access control: viewer (read-only), editor (score + comment), admin (all)
• Works without Supabase — core component uses memory store by default
• Offline event queuing with reconnect flush

Labels

enhancement, infrastructure

[eitah]

proceeding using the flo@spantree.net account configured on supabase and saved in 1pass

[eitah]

I POCed this work on this branch but didn't have time to finish before my last day. I am leaving it in the git but closing the pr

#34