max / makenotwork

1.2 KB · 28 lines History Blame Raw

1	-- SyncKit security hardening (ultra-fuzz --deep Security A+).
2	--
3	-- 1. Per-user sync-token revocation, separate from the web `jwt_invalidated_at`.
4	-- Bumping this invalidates a user's outstanding SyncKit JWTs (the extractor
5	-- rejects `iat <= sync_jwt_invalidated_at`) WITHOUT logging them out of the
6	-- website. Set when a sync device is removed, so the removed device's token
7	-- dies immediately instead of surviving until expiry.
8	-- 2. A security audit log for sync auth failures, device removals, and key
9	-- rotations — the events an operator needs to investigate account abuse.
10	--
11	-- Additive and idempotent.
12
13	ALTER TABLE users ADD COLUMN IF NOT EXISTS sync_jwt_invalidated_at TIMESTAMPTZ;
14
15	CREATE TABLE IF NOT EXISTS sync_security_events (
16	id BIGSERIAL PRIMARY KEY,
17	app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
18	-- NULL after the user row is removed; the event is still worth retaining.
19	user_id UUID REFERENCES users(id) ON DELETE SET NULL,
20	event_type VARCHAR(40) NOT NULL,
21	detail JSONB,
22	ip TEXT,
23	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
24	);
25
26	CREATE INDEX IF NOT EXISTS idx_sync_security_events_app_user_time
27	ON sync_security_events (app_id, user_id, created_at DESC);
28