A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- Update OAuth tables to match indigo's ClientAuthStore interface requirements
3-- This migration adds columns needed for OAuth client sessions and auth requests
4
5-- Update oauth_requests table
6-- Add columns for request URI, auth server endpoints, scopes, and DPoP key
7ALTER TABLE oauth_requests
8 ADD COLUMN request_uri TEXT,
9 ADD COLUMN auth_server_token_endpoint TEXT,
10 ADD COLUMN auth_server_revocation_endpoint TEXT,
11 ADD COLUMN scopes TEXT[],
12 ADD COLUMN dpop_private_key_multibase TEXT;
13
14-- Make original dpop_private_jwk nullable (we now use dpop_private_key_multibase)
15ALTER TABLE oauth_requests ALTER COLUMN dpop_private_jwk DROP NOT NULL;
16
17-- Make did nullable (indigo's AuthRequestData.AccountDID is a pointer - optional)
18ALTER TABLE oauth_requests ALTER COLUMN did DROP NOT NULL;
19
20-- Make handle and pds_url nullable too (derived from DID resolution, not always available at auth request time)
21ALTER TABLE oauth_requests ALTER COLUMN handle DROP NOT NULL;
22ALTER TABLE oauth_requests ALTER COLUMN pds_url DROP NOT NULL;
23
24-- Update existing oauth_requests data
25-- Convert dpop_private_jwk (JSONB) to multibase format if needed
26-- Note: This will leave the multibase column NULL for now since conversion requires crypto logic
27-- The application will need to handle NULL values or regenerate keys on next auth flow
28UPDATE oauth_requests
29SET
30 auth_server_token_endpoint = auth_server_iss || '/oauth/token',
31 scopes = ARRAY['atproto']::TEXT[]
32WHERE auth_server_token_endpoint IS NULL;
33
34-- Add indexes for new columns
35CREATE INDEX idx_oauth_requests_request_uri ON oauth_requests(request_uri) WHERE request_uri IS NOT NULL;
36
37-- Update oauth_sessions table
38-- Add session_id column (will become part of composite key)
39ALTER TABLE oauth_sessions
40 ADD COLUMN session_id TEXT,
41 ADD COLUMN host_url TEXT,
42 ADD COLUMN auth_server_token_endpoint TEXT,
43 ADD COLUMN auth_server_revocation_endpoint TEXT,
44 ADD COLUMN scopes TEXT[],
45 ADD COLUMN dpop_private_key_multibase TEXT;
46
47-- Make original dpop_private_jwk nullable (we now use dpop_private_key_multibase)
48ALTER TABLE oauth_sessions ALTER COLUMN dpop_private_jwk DROP NOT NULL;
49
50-- Populate session_id for existing sessions (use DID as default for single-session per account)
51-- In production, you may want to generate unique session IDs
52UPDATE oauth_sessions
53SET
54 session_id = 'default',
55 host_url = pds_url,
56 auth_server_token_endpoint = auth_server_iss || '/oauth/token',
57 scopes = ARRAY['atproto']::TEXT[]
58WHERE session_id IS NULL;
59
60-- Make session_id NOT NULL after populating existing data
61ALTER TABLE oauth_sessions
62 ALTER COLUMN session_id SET NOT NULL;
63
64-- Drop old unique constraint on did only
65ALTER TABLE oauth_sessions
66 DROP CONSTRAINT IF EXISTS oauth_sessions_did_key;
67
68-- Create new composite unique constraint for (did, session_id)
69-- This allows multiple sessions per account
70-- Note: UNIQUE constraint automatically creates an index, so no separate index needed
71ALTER TABLE oauth_sessions
72 ADD CONSTRAINT oauth_sessions_did_session_id_key UNIQUE (did, session_id);
73
74-- Add comment explaining the schema change
75COMMENT ON COLUMN oauth_sessions.session_id IS 'Session identifier to support multiple concurrent sessions per account';
76COMMENT ON CONSTRAINT oauth_sessions_did_session_id_key ON oauth_sessions IS 'Composite key allowing multiple sessions per DID';
77
78-- +goose Down
79-- Rollback: Remove added columns and restore original unique constraint
80
81-- oauth_sessions rollback
82-- Drop composite unique constraint (this also drops the associated index)
83ALTER TABLE oauth_sessions
84 DROP CONSTRAINT IF EXISTS oauth_sessions_did_session_id_key;
85
86-- Delete all but the most recent session per DID before restoring unique constraint
87-- This ensures the UNIQUE (did) constraint can be added without conflicts
88DELETE FROM oauth_sessions a
89USING oauth_sessions b
90WHERE a.did = b.did
91 AND a.created_at < b.created_at;
92
93-- Restore old unique constraint
94ALTER TABLE oauth_sessions
95 ADD CONSTRAINT oauth_sessions_did_key UNIQUE (did);
96
97-- Restore NOT NULL constraint on dpop_private_jwk
98ALTER TABLE oauth_sessions
99 ALTER COLUMN dpop_private_jwk SET NOT NULL;
100
101ALTER TABLE oauth_sessions
102 DROP COLUMN IF EXISTS dpop_private_key_multibase,
103 DROP COLUMN IF EXISTS scopes,
104 DROP COLUMN IF EXISTS auth_server_revocation_endpoint,
105 DROP COLUMN IF EXISTS auth_server_token_endpoint,
106 DROP COLUMN IF EXISTS host_url,
107 DROP COLUMN IF EXISTS session_id;
108
109-- oauth_requests rollback
110DROP INDEX IF EXISTS idx_oauth_requests_request_uri;
111
112-- Restore NOT NULL constraints
113ALTER TABLE oauth_requests
114 ALTER COLUMN dpop_private_jwk SET NOT NULL,
115 ALTER COLUMN did SET NOT NULL,
116 ALTER COLUMN handle SET NOT NULL,
117 ALTER COLUMN pds_url SET NOT NULL;
118
119ALTER TABLE oauth_requests
120 DROP COLUMN IF EXISTS dpop_private_key_multibase,
121 DROP COLUMN IF EXISTS scopes,
122 DROP COLUMN IF EXISTS auth_server_revocation_endpoint,
123 DROP COLUMN IF EXISTS auth_server_token_endpoint,
124 DROP COLUMN IF EXISTS request_uri;