max / makenotwork

1.6 KB · 36 lines History Blame Raw

1	-- SyncKit key rotation support.
2	--
3	-- Adds key_id tracking so devices know which encryption key was used for each
4	-- sync log entry, and a rotation state table for coordinating client-driven
5	-- key rotation (the server never sees plaintext keys).
6
7	-- key_id on sync_log: NULL = key_id 1 (backward compat, no backfill needed).
8	ALTER TABLE sync_log ADD COLUMN key_id INT;
9
10	-- key_id on sync_keys: tracks the current active key identifier.
11	ALTER TABLE sync_keys ADD COLUMN key_id INT NOT NULL DEFAULT 1;
12
13	-- At most one active rotation per (app_id, user_id) at a time.
14	CREATE TABLE sync_key_rotations (
15	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
16	app_id UUID NOT NULL,
17	user_id UUID NOT NULL,
18	device_id UUID NOT NULL,
19	-- The new master key, password-wrapped as a KeyEnvelope JSON string.
20	new_encrypted_key TEXT NOT NULL,
21	-- key_version of sync_keys at rotation start (optimistic concurrency).
22	old_key_version INT NOT NULL,
23	-- The key_id assigned to re-encrypted entries.
24	new_key_id INT NOT NULL,
25	-- Re-encryption progress: highest seq processed so far.
26	re_encrypted_through_seq BIGINT NOT NULL DEFAULT 0,
27	-- The max seq at rotation start. Must re-encrypt through this point.
28	target_seq BIGINT NOT NULL,
29	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
30	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
31	UNIQUE (app_id, user_id),
32	FOREIGN KEY (app_id) REFERENCES sync_apps(id) ON DELETE CASCADE,
33	FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
34	FOREIGN KEY (device_id) REFERENCES sync_devices(id) ON DELETE CASCADE
35	);
36