byarielm.fyi
ATlast — you'll never need to find your favorites on another platform again. Find your favs in the ATmosphere.
atproto
1-- ATlast Database Schema
2-- Migration Plan v2.0 - Phase 1
3-- Self-hosted PostgreSQL schema with fuzzy matching support
4
5-- Enable extensions
6CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
7CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- For fuzzy matching
8
9-- OAuth state storage (transient)
10CREATE TABLE oauth_states (
11 state TEXT PRIMARY KEY,
12 data JSONB NOT NULL,
13 created_at TIMESTAMP DEFAULT NOW()
14);
15CREATE INDEX idx_oauth_states_created ON oauth_states(created_at);
16
17-- OAuth sessions (transient)
18CREATE TABLE oauth_sessions (
19 did TEXT PRIMARY KEY,
20 session_data JSONB NOT NULL,
21 updated_at TIMESTAMP DEFAULT NOW()
22);
23
24-- User sessions (transient)
25CREATE TABLE user_sessions (
26 session_id TEXT PRIMARY KEY,
27 did TEXT NOT NULL,
28 fingerprint TEXT NOT NULL,
29 created_at TIMESTAMP DEFAULT NOW(),
30 expires_at TIMESTAMP NOT NULL
31);
32CREATE INDEX idx_user_sessions_did ON user_sessions(did);
33CREATE INDEX idx_user_sessions_expires ON user_sessions(expires_at);
34
35-- User uploads (persistent)
36CREATE TABLE user_uploads (
37 upload_id TEXT PRIMARY KEY,
38 user_did TEXT NOT NULL,
39 source_platform TEXT NOT NULL,
40 created_at TIMESTAMP DEFAULT NOW(),
41 total_users INTEGER DEFAULT 0,
42 matched_users INTEGER DEFAULT 0,
43 unmatched_users INTEGER DEFAULT 0
44);
45CREATE INDEX idx_user_uploads_user_did ON user_uploads(user_did);
46-- Note: check_frequency and last_checked removed - no periodic checking in Phase 1
47
48-- Source accounts (persistent)
49CREATE TABLE source_accounts (
50 id SERIAL PRIMARY KEY,
51 source_platform TEXT NOT NULL,
52 original_username TEXT NOT NULL,
53 normalized_username TEXT NOT NULL,
54 date_on_source TIMESTAMP,
55 created_at TIMESTAMP DEFAULT NOW(),
56 UNIQUE(source_platform, normalized_username)
57);
58CREATE INDEX idx_source_accounts_normalized ON source_accounts
59 USING gin(normalized_username gin_trgm_ops); -- Fuzzy matching!
60CREATE INDEX idx_source_accounts_platform ON source_accounts(source_platform);
61
62-- User-source follows (join table)
63CREATE TABLE user_source_follows (
64 user_did TEXT NOT NULL,
65 upload_id TEXT NOT NULL REFERENCES user_uploads(upload_id) ON DELETE CASCADE,
66 source_account_id INTEGER NOT NULL REFERENCES source_accounts(id),
67 found_at TIMESTAMP DEFAULT NOW(),
68 PRIMARY KEY (upload_id, source_account_id)
69);
70CREATE INDEX idx_user_source_follows_user ON user_source_follows(user_did);
71CREATE INDEX idx_user_source_follows_source ON user_source_follows(source_account_id);
72
73-- AT Protocol matches (persistent)
74CREATE TABLE atproto_matches (
75 id SERIAL PRIMARY KEY,
76 source_account_id INTEGER NOT NULL REFERENCES source_accounts(id),
77 atproto_did TEXT NOT NULL,
78 atproto_handle TEXT NOT NULL,
79 display_name TEXT,
80 match_score INTEGER NOT NULL,
81 post_count INTEGER,
82 follower_count INTEGER,
83 follow_status JSONB DEFAULT '{}',
84 found_at TIMESTAMP DEFAULT NOW(),
85 UNIQUE(source_account_id, atproto_did)
86);
87CREATE INDEX idx_atproto_matches_source ON atproto_matches(source_account_id);
88CREATE INDEX idx_atproto_matches_did ON atproto_matches(atproto_did);
89CREATE INDEX idx_atproto_matches_score ON atproto_matches(match_score DESC);
90
91-- User match status (persistent)
92CREATE TABLE user_match_status (
93 user_did TEXT NOT NULL,
94 match_id INTEGER NOT NULL REFERENCES atproto_matches(id),
95 viewed BOOLEAN DEFAULT FALSE,
96 dismissed BOOLEAN DEFAULT FALSE,
97 followed BOOLEAN DEFAULT FALSE,
98 notified BOOLEAN DEFAULT FALSE,
99 updated_at TIMESTAMP DEFAULT NOW(),
100 PRIMARY KEY (user_did, match_id)
101);
102CREATE INDEX idx_user_match_status_user ON user_match_status(user_did);
103CREATE INDEX idx_user_match_status_notified ON user_match_status(user_did, notified)
104 WHERE notified = FALSE;
105
106-- Notification queue (transient - for Phase 2)
107CREATE TABLE notification_queue (
108 id SERIAL PRIMARY KEY,
109 user_did TEXT NOT NULL,
110 match_id INTEGER NOT NULL REFERENCES atproto_matches(id),
111 notification_type TEXT NOT NULL, -- 'in_app', 'bluesky_dm', 'partner_api'
112 status TEXT DEFAULT 'pending', -- 'pending', 'sent', 'failed'
113 attempts INTEGER DEFAULT 0,
114 last_attempt TIMESTAMP,
115 error_message TEXT, -- Store error details for debugging
116 created_at TIMESTAMP DEFAULT NOW()
117);
118CREATE INDEX idx_notification_queue_status ON notification_queue(status)
119 WHERE status = 'pending';
120CREATE INDEX idx_notification_queue_user ON notification_queue(user_did);
121
122-- Partner API keys (for Phase 2)
123CREATE TABLE partner_api_keys (
124 id SERIAL PRIMARY KEY,
125 partner_name TEXT NOT NULL, -- 'skylight', 'spark', etc.
126 api_key_hash TEXT NOT NULL UNIQUE, -- SHA-256 hashed API key
127 created_at TIMESTAMP DEFAULT NOW(),
128 last_used TIMESTAMP,
129 is_active BOOLEAN DEFAULT TRUE
130);
131CREATE INDEX idx_partner_api_keys_hash ON partner_api_keys(api_key_hash)
132 WHERE is_active = TRUE;
133
134-- Cleanup function for old transient data
135CREATE OR REPLACE FUNCTION cleanup_transient_data() RETURNS void AS $$
136BEGIN
137 -- Clean expired OAuth states (1 hour)
138 DELETE FROM oauth_states WHERE created_at < NOW() - INTERVAL '1 hour';
139
140 -- Clean expired sessions
141 DELETE FROM user_sessions WHERE expires_at < NOW();
142
143 -- Clean old sent notifications (7 days)
144 DELETE FROM notification_queue
145 WHERE status = 'sent' AND created_at < NOW() - INTERVAL '7 days';
146
147 -- Clean old failed notifications (30 days)
148 DELETE FROM notification_queue
149 WHERE status = 'failed' AND created_at < NOW() - INTERVAL '30 days';
150END;
151$$ LANGUAGE plpgsql;