max / makenotwork

782 B · 15 lines History Blame Raw

1	-- Per-(user, app) record of the OAuth scopes a user has interactively consented
2	-- to for an app. The prompt=none silent re-auth path may only mint a code for
3	-- scopes already present here; anything broader returns error=consent_required
4	-- and forces a fresh interactive approval (ultra-fuzz Run 6 R6-Sec-L5). Today's
5	-- scopes are read-only, so this is latent hardening that becomes load-bearing
6	-- the moment a write scope ships.
7	CREATE TABLE oauth_granted_scopes (
8	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
9	app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
10	scopes TEXT NOT NULL,
11	granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
12	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
13	PRIMARY KEY (user_id, app_id)
14	);
15