A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- +goose StatementBegin
3-- V2.0: Add encrypted password column for PDS account recovery
4-- CRITICAL FIX: Password must be encrypted (not hashed) for session recovery
5-- When access/refresh tokens expire (90-day window), we need the plaintext password
6-- to call com.atproto.server.createSession - bcrypt hashing prevents this
7
8-- Add encrypted password column
9ALTER TABLE communities ADD COLUMN pds_password_encrypted BYTEA;
10
11-- Drop legacy plaintext token columns (we now use *_encrypted versions from migration 006)
12ALTER TABLE communities DROP COLUMN IF EXISTS pds_access_token;
13ALTER TABLE communities DROP COLUMN IF EXISTS pds_refresh_token;
14
15-- Drop legacy password_hash column from migration 005 (never used in production)
16ALTER TABLE communities DROP COLUMN IF EXISTS pds_password_hash;
17
18-- Add comment
19COMMENT ON COLUMN communities.pds_password_encrypted IS 'Encrypted community PDS password (pgp_sym_encrypt) - required for session recovery when tokens expire';
20
21-- +goose StatementEnd
22
23-- +goose Down
24-- +goose StatementBegin
25-- Restore legacy columns (for rollback compatibility)
26ALTER TABLE communities ADD COLUMN pds_access_token TEXT;
27ALTER TABLE communities ADD COLUMN pds_refresh_token TEXT;
28ALTER TABLE communities ADD COLUMN pds_password_hash TEXT;
29
30-- Drop encrypted password
31ALTER TABLE communities DROP COLUMN IF EXISTS pds_password_encrypted;
32
33-- Restore old comment
34COMMENT ON COLUMN communities.pds_password_hash IS 'bcrypt hash of community PDS password (DEPRECATED - cannot recover plaintext)';
35-- +goose StatementEnd