A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- Create OAuth tables for managing OAuth flow state and user sessions
3
4-- Temporary state during OAuth authorization flow (30 min TTL)
5-- This stores the intermediate state between redirect to auth server and callback
6CREATE TABLE oauth_requests (
7 id SERIAL PRIMARY KEY,
8 state TEXT UNIQUE NOT NULL, -- OAuth state parameter (CSRF protection)
9 did TEXT NOT NULL, -- User's DID (resolved from handle)
10 handle TEXT NOT NULL, -- User's handle (e.g., alice.bsky.social)
11 pds_url TEXT NOT NULL, -- User's PDS URL
12 pkce_verifier TEXT NOT NULL, -- PKCE code verifier
13 dpop_private_jwk JSONB NOT NULL, -- DPoP private key (ES256) for this session
14 dpop_authserver_nonce TEXT, -- DPoP nonce from authorization server
15 auth_server_iss TEXT NOT NULL, -- Authorization server issuer
16 return_url TEXT, -- Optional return URL after login
17 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
18);
19
20-- Long-lived user sessions (7 day TTL, auto-refreshed)
21-- This stores authenticated user sessions with OAuth tokens
22CREATE TABLE oauth_sessions (
23 id SERIAL PRIMARY KEY,
24 did TEXT UNIQUE NOT NULL, -- User's DID (primary identifier)
25 handle TEXT NOT NULL, -- User's handle (can change)
26 pds_url TEXT NOT NULL, -- User's PDS URL
27 access_token TEXT NOT NULL, -- OAuth access token (DPoP-bound)
28 refresh_token TEXT NOT NULL, -- OAuth refresh token
29 dpop_private_jwk JSONB NOT NULL, -- DPoP private key for this session
30 dpop_authserver_nonce TEXT, -- DPoP nonce for auth server token endpoint
31 dpop_pds_nonce TEXT, -- DPoP nonce for PDS requests
32 auth_server_iss TEXT NOT NULL, -- Authorization server issuer
33 expires_at TIMESTAMP WITH TIME ZONE NOT NULL, -- Token expiration time
34 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
35 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
36);
37
38-- Indexes for efficient lookups
39CREATE INDEX idx_oauth_requests_state ON oauth_requests(state);
40CREATE INDEX idx_oauth_requests_created_at ON oauth_requests(created_at);
41CREATE INDEX idx_oauth_sessions_did ON oauth_sessions(did);
42CREATE INDEX idx_oauth_sessions_expires_at ON oauth_sessions(expires_at);
43
44-- Function to update updated_at timestamp
45-- +goose StatementBegin
46CREATE OR REPLACE FUNCTION update_oauth_session_updated_at()
47RETURNS TRIGGER AS $$
48BEGIN
49 NEW.updated_at = CURRENT_TIMESTAMP;
50 RETURN NEW;
51END;
52$$ LANGUAGE plpgsql;
53-- +goose StatementEnd
54
55-- Trigger to automatically update updated_at
56CREATE TRIGGER oauth_sessions_updated_at
57 BEFORE UPDATE ON oauth_sessions
58 FOR EACH ROW
59 EXECUTE FUNCTION update_oauth_session_updated_at();
60
61-- +goose Down
62DROP TRIGGER IF EXISTS oauth_sessions_updated_at ON oauth_sessions;
63DROP FUNCTION IF EXISTS update_oauth_session_updated_at();
64DROP INDEX IF EXISTS idx_oauth_sessions_expires_at;
65DROP INDEX IF EXISTS idx_oauth_sessions_did;
66DROP INDEX IF EXISTS idx_oauth_requests_created_at;
67DROP INDEX IF EXISTS idx_oauth_requests_state;
68DROP TABLE IF EXISTS oauth_sessions;
69DROP TABLE IF EXISTS oauth_requests;