A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- Enable pgcrypto extension for encryption at rest
3CREATE EXTENSION IF NOT EXISTS pgcrypto;
4
5-- Create encryption key table (single-row config table)
6-- SECURITY: In production, use environment variable or external key management
7CREATE TABLE encryption_keys (
8 id INTEGER PRIMARY KEY CHECK (id = 1),
9 key_data BYTEA NOT NULL,
10 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
11 rotated_at TIMESTAMP WITH TIME ZONE
12);
13
14-- Insert default encryption key
15INSERT INTO encryption_keys (id, key_data)
16VALUES (1, gen_random_bytes(32))
17ON CONFLICT (id) DO NOTHING;
18
19-- Add encrypted columns
20ALTER TABLE communities
21 ADD COLUMN pds_access_token_encrypted BYTEA,
22 ADD COLUMN pds_refresh_token_encrypted BYTEA;
23
24-- Add index for communities with credentials
25CREATE INDEX idx_communities_encrypted_tokens ON communities(did) WHERE pds_access_token_encrypted IS NOT NULL;
26
27-- Security comments
28COMMENT ON TABLE encryption_keys IS 'Encryption keys for sensitive data - RESTRICT ACCESS';
29COMMENT ON COLUMN communities.pds_access_token_encrypted IS 'Encrypted JWT - decrypt with pgp_sym_decrypt';
30COMMENT ON COLUMN communities.pds_refresh_token_encrypted IS 'Encrypted refresh token - decrypt with pgp_sym_decrypt';
31
32-- +goose Down
33DROP INDEX IF EXISTS idx_communities_encrypted_tokens;
34
35ALTER TABLE communities
36 DROP COLUMN IF EXISTS pds_access_token_encrypted,
37 DROP COLUMN IF EXISTS pds_refresh_token_encrypted;
38
39DROP TABLE IF EXISTS encryption_keys;