max / makenotwork

3.0 KB · 55 lines History Blame Raw

1	-- SyncKit Groups: shared, end-to-end-encrypted changelogs across multiple users.
2	--
3	-- Schema only (phase 2 / p2-tables). The group-scoped push/pull and membership
4	-- gating that USE the `group_id` column below land in p2-changelog. The server
5	-- never sees the Group Content Key (GCK) or any plaintext; it stores opaque
6	-- sealed grants and ciphertext only. Design: wiki synckit-groups-design.
7
8	-- A group: a shared changelog owned by one admin, whose members each hold a
9	-- grant of the same GCK sealed to their identity public key.
10	CREATE TABLE IF NOT EXISTS sync_groups (
11	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
12	app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
13	-- The admin who mints the GCK and manages membership. The group's changelog
14	-- and blob storage bill against this user's slot (admin's slot, 2026-07-23).
15	admin_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
16	name VARCHAR(100) NOT NULL,
17	-- Current GCK generation. Bumped when a member is removed; rotation re-seals
18	-- the new GCK to the remaining members. Grants carry the version they were
19	-- sealed for, so a stale grant is detectable.
20	gck_version INT NOT NULL DEFAULT 1,
21	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
22	);
23
24	CREATE INDEX IF NOT EXISTS idx_sync_groups_app_admin ON sync_groups (app_id, admin_user_id);
25
26	-- Membership + the per-member sealed GCK grant. One row per (group, user).
27	CREATE TABLE IF NOT EXISTS sync_group_members (
28	group_id UUID NOT NULL REFERENCES sync_groups(id) ON DELETE CASCADE,
29	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
30	-- Reserved for the later per-key permission system. MVP is admin \| member;
31	-- every member reads and writes.
32	role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('admin', 'member')),
33	-- The GCK sealed to this member's X25519 public key (base64), opaque to the
34	-- server. Produced by the admin via seal_gck_to_member (synckit-client).
35	sealed_gck TEXT NOT NULL,
36	-- The GCK generation `sealed_gck` was sealed under. Matches
37	-- sync_groups.gck_version while current; a lower value marks a stale grant.
38	gck_version INT NOT NULL,
39	added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
40	PRIMARY KEY (group_id, user_id)
41	);
42
43	-- "Which groups am I in" is a per-user lookup on pull/list.
44	CREATE INDEX IF NOT EXISTS idx_sync_group_members_user ON sync_group_members (user_id);
45
46	-- Group scope on the changelog. NULL = personal: the existing per-user semantics
47	-- are untouched, so no backfill is needed. A group entry is scoped by
48	-- (app_id, group_id); user_id still records the pushing user for provenance.
49	-- Consumed by p2-changelog.
50	ALTER TABLE sync_log ADD COLUMN group_id UUID REFERENCES sync_groups(id) ON DELETE CASCADE;
51
52	-- Partial index for the group pull path; personal pulls keep using the existing
53	-- (app_id, user_id, seq) access pattern and never touch this index.
54	CREATE INDEX IF NOT EXISTS idx_sync_log_group ON sync_log (app_id, group_id, seq) WHERE group_id IS NOT NULL;
55