bretton.dev
A community based topic aggregation platform built on atproto
1-- +goose Up
2-- Add API key authentication and OAuth credential storage for aggregators
3-- This enables aggregators to authenticate using API keys backed by OAuth sessions
4
5-- ============================================================================
6-- Add API key columns to aggregators table
7-- ============================================================================
8ALTER TABLE aggregators
9 -- API key identification (prefix for log correlation, hash for auth)
10 ADD COLUMN api_key_prefix VARCHAR(12),
11 ADD COLUMN api_key_hash VARCHAR(64) UNIQUE,
12
13 -- OAuth credentials (encrypted at application layer before storage)
14 -- SECURITY: These columns contain sensitive OAuth tokens
15 ADD COLUMN oauth_access_token TEXT,
16 ADD COLUMN oauth_refresh_token TEXT,
17 ADD COLUMN oauth_token_expires_at TIMESTAMPTZ,
18
19 -- OAuth session metadata for token refresh
20 ADD COLUMN oauth_pds_url TEXT,
21 ADD COLUMN oauth_auth_server_iss TEXT,
22 ADD COLUMN oauth_auth_server_token_endpoint TEXT,
23
24 -- DPoP keys and nonces for token refresh (multibase encoded)
25 -- SECURITY: Contains private key material
26 ADD COLUMN oauth_dpop_private_key_multibase TEXT,
27 ADD COLUMN oauth_dpop_authserver_nonce TEXT,
28 ADD COLUMN oauth_dpop_pds_nonce TEXT,
29
30 -- API key lifecycle timestamps
31 ADD COLUMN api_key_created_at TIMESTAMPTZ,
32 ADD COLUMN api_key_revoked_at TIMESTAMPTZ,
33 ADD COLUMN api_key_last_used_at TIMESTAMPTZ;
34
35-- Index for API key lookup during authentication
36-- Partial index excludes NULL values since not all aggregators have API keys
37CREATE INDEX idx_aggregators_api_key_hash
38 ON aggregators(api_key_hash)
39 WHERE api_key_hash IS NOT NULL;
40
41-- ============================================================================
42-- Security comments on sensitive columns
43-- ============================================================================
44COMMENT ON COLUMN aggregators.api_key_prefix IS 'First 12 characters of API key for identification in logs (not secret)';
45COMMENT ON COLUMN aggregators.api_key_hash IS 'SHA-256 hash of full API key for authentication lookup';
46COMMENT ON COLUMN aggregators.oauth_access_token IS 'SENSITIVE: Encrypted OAuth access token for PDS operations';
47COMMENT ON COLUMN aggregators.oauth_refresh_token IS 'SENSITIVE: Encrypted OAuth refresh token for session renewal';
48COMMENT ON COLUMN aggregators.oauth_token_expires_at IS 'When the OAuth access token expires (triggers refresh)';
49COMMENT ON COLUMN aggregators.oauth_pds_url IS 'PDS URL for this aggregators OAuth session';
50COMMENT ON COLUMN aggregators.oauth_auth_server_iss IS 'OAuth authorization server issuer URL';
51COMMENT ON COLUMN aggregators.oauth_auth_server_token_endpoint IS 'OAuth token refresh endpoint URL';
52COMMENT ON COLUMN aggregators.oauth_dpop_private_key_multibase IS 'SENSITIVE: DPoP private key in multibase format for token refresh';
53COMMENT ON COLUMN aggregators.oauth_dpop_authserver_nonce IS 'Latest DPoP nonce from authorization server';
54COMMENT ON COLUMN aggregators.oauth_dpop_pds_nonce IS 'Latest DPoP nonce from PDS';
55COMMENT ON COLUMN aggregators.api_key_created_at IS 'When the API key was generated';
56COMMENT ON COLUMN aggregators.api_key_revoked_at IS 'When the API key was revoked (NULL = active)';
57COMMENT ON COLUMN aggregators.api_key_last_used_at IS 'Last successful authentication using this API key';
58
59-- +goose Down
60-- Remove API key columns from aggregators table
61DROP INDEX IF EXISTS idx_aggregators_api_key_hash;
62
63ALTER TABLE aggregators
64 DROP COLUMN IF EXISTS api_key_prefix,
65 DROP COLUMN IF EXISTS api_key_hash,
66 DROP COLUMN IF EXISTS oauth_access_token,
67 DROP COLUMN IF EXISTS oauth_refresh_token,
68 DROP COLUMN IF EXISTS oauth_token_expires_at,
69 DROP COLUMN IF EXISTS oauth_pds_url,
70 DROP COLUMN IF EXISTS oauth_auth_server_iss,
71 DROP COLUMN IF EXISTS oauth_auth_server_token_endpoint,
72 DROP COLUMN IF EXISTS oauth_dpop_private_key_multibase,
73 DROP COLUMN IF EXISTS oauth_dpop_authserver_nonce,
74 DROP COLUMN IF EXISTS oauth_dpop_pds_nonce,
75 DROP COLUMN IF EXISTS api_key_created_at,
76 DROP COLUMN IF EXISTS api_key_revoked_at,
77 DROP COLUMN IF EXISTS api_key_last_used_at;