A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- Phone verification tables (privacy-first: only hashed phones stored)
3
4-- Stores phone verification status (hashed phone only, no plaintext)
5CREATE TABLE phone_verifications (
6 did TEXT PRIMARY KEY REFERENCES users(did) ON DELETE CASCADE,
7 phone_hash TEXT UNIQUE NOT NULL, -- HMAC-SHA256(phone, pepper) for duplicate detection
8 verified_at TIMESTAMP WITH TIME ZONE NOT NULL,
9 expires_at TIMESTAMP WITH TIME ZONE NOT NULL, -- Annual re-verification (365 days)
10 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
11 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
12);
13
14-- Index for checking if phone is already verified (duplicate prevention)
15CREATE INDEX idx_phone_verifications_phone_hash ON phone_verifications(phone_hash);
16CREATE INDEX idx_phone_verifications_expires_at ON phone_verifications(expires_at);
17
18-- Stores pending verification requests (short-lived, auto-cleanup)
19CREATE TABLE phone_verification_requests (
20 request_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
21 did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
22 phone_hash TEXT NOT NULL, -- Same hash as phone_verifications for duplicate check
23 otp_code_hash TEXT NOT NULL, -- Bcrypt hash of OTP code
24 attempts INT DEFAULT 0, -- Track failed attempts (max 3)
25 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
26 expires_at TIMESTAMP WITH TIME ZONE NOT NULL -- OTP expires in 10 minutes
27);
28
29CREATE INDEX idx_phone_verification_requests_did ON phone_verification_requests(did);
30CREATE INDEX idx_phone_verification_requests_expires_at ON phone_verification_requests(expires_at);
31
32-- Rate limiting table (prevent SMS abuse)
33CREATE TABLE phone_verification_rate_limits (
34 identifier TEXT PRIMARY KEY, -- Could be DID or phone_hash
35 request_count INT DEFAULT 1,
36 window_start TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
37 last_request_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
38);
39
40CREATE INDEX idx_phone_verification_rate_limits_window ON phone_verification_rate_limits(window_start);
41
42-- Audit log for security monitoring
43CREATE TABLE phone_verification_audit_log (
44 id BIGSERIAL PRIMARY KEY,
45 did TEXT REFERENCES users(did) ON DELETE SET NULL,
46 event_type TEXT NOT NULL, -- 'request_sent', 'verification_success', 'verification_failed', 'rate_limit_hit'
47 phone_hash TEXT, -- Optional, for tracking abuse patterns
48 ip_address INET, -- Track source IP for fraud detection
49 user_agent TEXT,
50 metadata JSONB, -- Additional context (error details, etc.)
51 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
52);
53
54CREATE INDEX idx_phone_verification_audit_log_did ON phone_verification_audit_log(did);
55CREATE INDEX idx_phone_verification_audit_log_event_type ON phone_verification_audit_log(event_type);
56CREATE INDEX idx_phone_verification_audit_log_created_at ON phone_verification_audit_log(created_at);
57
58-- Cleanup function for expired requests (run via cron)
59CREATE OR REPLACE FUNCTION cleanup_expired_verification_requests()
60RETURNS void AS $$
61BEGIN
62 DELETE FROM phone_verification_requests WHERE expires_at < NOW();
63 DELETE FROM phone_verification_rate_limits WHERE window_start < NOW() - INTERVAL '1 hour';
64END;
65$$ LANGUAGE plpgsql;
66
67-- +goose Down
68DROP FUNCTION IF EXISTS cleanup_expired_verification_requests();
69DROP TABLE IF EXISTS phone_verification_audit_log;
70DROP TABLE IF EXISTS phone_verification_rate_limits;
71DROP TABLE IF EXISTS phone_verification_requests;
72DROP TABLE IF EXISTS phone_verifications;