this repo has no description
lewis.moe
1CREATE TYPE account_type AS ENUM ('personal', 'delegated');
2
3ALTER TABLE users ADD COLUMN account_type account_type NOT NULL DEFAULT 'personal';
4
5CREATE TYPE delegation_action_type AS ENUM (
6 'grant_created',
7 'grant_revoked',
8 'scopes_modified',
9 'token_issued',
10 'repo_write',
11 'blob_upload',
12 'account_action'
13);
14
15CREATE TABLE account_delegations (
16 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
17 delegated_did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
18 controller_did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
19 granted_scopes TEXT NOT NULL,
20 granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
21 granted_by TEXT NOT NULL REFERENCES users(did),
22 revoked_at TIMESTAMPTZ,
23 revoked_by TEXT REFERENCES users(did),
24 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
25);
26
27CREATE UNIQUE INDEX unique_active_delegation ON account_delegations(delegated_did, controller_did)
28 WHERE revoked_at IS NULL;
29CREATE INDEX idx_delegations_delegated ON account_delegations(delegated_did) WHERE revoked_at IS NULL;
30CREATE INDEX idx_delegations_controller ON account_delegations(controller_did) WHERE revoked_at IS NULL;
31
32CREATE TABLE delegation_audit_log (
33 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
34 delegated_did TEXT NOT NULL,
35 actor_did TEXT NOT NULL,
36 controller_did TEXT,
37 action_type delegation_action_type NOT NULL,
38 action_details JSONB,
39 ip_address TEXT,
40 user_agent TEXT,
41 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
42);
43
44CREATE INDEX idx_delegation_audit_delegated ON delegation_audit_log(delegated_did, created_at DESC);
45CREATE INDEX idx_delegation_audit_controller ON delegation_audit_log(controller_did, created_at DESC) WHERE controller_did IS NOT NULL;
46
47ALTER TABLE oauth_authorization_request ADD COLUMN controller_did TEXT;
48
49ALTER TABLE oauth_token ADD COLUMN controller_did TEXT;
50CREATE INDEX idx_oauth_token_controller ON oauth_token(controller_did) WHERE controller_did IS NOT NULL;
51
52ALTER TABLE app_passwords ADD COLUMN created_by_controller_did TEXT REFERENCES users(did) ON DELETE SET NULL;
53CREATE INDEX idx_app_passwords_controller ON app_passwords(created_by_controller_did) WHERE created_by_controller_did IS NOT NULL;
54
55ALTER TABLE session_tokens ADD COLUMN controller_did TEXT;