max / makenotwork

2.1 KB · 40 lines History Blame Raw

1	-- SyncKit Groups: give the group changelog its own table.
2	--
3	-- Supersedes the sync_log.group_id column added in 171. Keeping group entries in
4	-- sync_log meant every personal-scope query (pull, key rotation, status, cleanup)
5	-- had to remember `AND group_id IS NULL` or silently leak GCK-encrypted group rows
6	-- into a user's personal sync, and let personal key rotation re-encrypt them with
7	-- the wrong key. A dedicated table makes that isolation structural: sync_log stays
8	-- purely personal and unchanged, and the group changelog evolves on its own.
9	-- Design: wiki synckit-groups-design.
10
11	DROP INDEX IF EXISTS idx_sync_log_group;
12	ALTER TABLE sync_log DROP COLUMN IF EXISTS group_id;
13
14	CREATE TABLE IF NOT EXISTS sync_group_log (
15	seq BIGSERIAL PRIMARY KEY,
16	app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
17	group_id UUID NOT NULL REFERENCES sync_groups(id) ON DELETE CASCADE,
18	-- The member who pushed this change (provenance/audit). The group, not the
19	-- user, owns the entry, so pulls are scoped by group_id, not user_id.
20	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
21	device_id UUID NOT NULL REFERENCES sync_devices(id) ON DELETE CASCADE,
22	-- Client-generated batch id for idempotent push (same role as sync_log).
23	batch_id UUID,
24	table_name VARCHAR(100) NOT NULL,
25	operation VARCHAR(10) NOT NULL
26	CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
27	row_id VARCHAR(255) NOT NULL,
28	client_timestamp TIMESTAMPTZ NOT NULL,
29	-- Ciphertext sealed under the group's GCK (generation = sync_groups.gck_version).
30	-- No key_id column: group entries key off the GCK, not the per-user
31	-- sync_keys.key_id that personal rotation tracks.
32	data JSONB,
33	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
34	);
35
36	-- Group pull: (app_id, group_id, seq > cursor) ordered by seq.
37	CREATE INDEX IF NOT EXISTS idx_sync_group_log_pull ON sync_group_log (app_id, group_id, seq);
38	-- Group push idempotency: MAX(seq) for a (group, batch).
39	CREATE INDEX IF NOT EXISTS idx_sync_group_log_batch ON sync_group_log (group_id, batch_id);
40