ATlast — you'll never need to find your favorites on another platform again. Find your favs in the ATmosphere.
atproto
1# Database Scripts
2
3This directory contains database schema and utility scripts for the ATlast migration.
4
5## Files
6
7- **`init-db.sql`** - PostgreSQL schema definition (tables, indexes, functions)
8- **`seed-test-data.sql`** - Test data for local development
9- **`generate-encryption-key.ts`** - OAuth key generation utility
10- **`keygen.js`** - Legacy key generation script
11
12## Database Setup
13
14### Local Development (Docker)
15
161. **Start PostgreSQL container:**
17 ```bash
18 cd docker
19 docker compose up -d database
20 ```
21
222. **Initialize schema:**
23 ```bash
24 docker compose exec database psql -U atlast -d atlast -f /docker-entrypoint-initdb.d/init.sql
25 ```
26
27 Or connect and run manually:
28 ```bash
29 docker compose exec -i database psql -U atlast -d atlast < scripts/init-db.sql
30 ```
31
323. **Seed test data (optional):**
33 ```bash
34 docker compose exec -i database psql -U atlast -d atlast < scripts/seed-test-data.sql
35 ```
36
374. **Verify setup:**
38 ```bash
39 cd packages/api
40 DATABASE_URL=postgresql://atlast:password@localhost:5432/atlast pnpm run test:db
41 ```
42
43### Production Setup
44
45The database will be automatically initialized when the Docker container starts, as `init-db.sql` is mounted to `/docker-entrypoint-initdb.d/init.sql` in the compose file.
46
47## Schema Overview
48
49### Transient Tables (Session Data)
50- `oauth_states` - OAuth flow state storage
51- `oauth_sessions` - OAuth session data
52- `user_sessions` - User authentication sessions
53- `notification_queue` - Pending notifications (Phase 2)
54
55**Note:** Transient data is cleaned up daily via the `cleanup_transient_data()` function.
56
57### Persistent Tables (User Data)
58- `user_uploads` - Upload history and metadata
59- `source_accounts` - Usernames from source platforms (Instagram, TikTok, etc.)
60- `user_source_follows` - Links users to their source account follows
61- `atproto_matches` - Matched AT Protocol accounts
62- `user_match_status` - User interaction with matches (viewed, followed, etc.)
63- `partner_api_keys` - API keys for partner integrations (Phase 2)
64
65## Key Features
66
67### Fuzzy Matching
68The schema includes the `pg_trgm` extension for fuzzy username matching. This enables:
69- Similarity-based searches (`%` operator)
70- Trigram GIN indexes for fast fuzzy lookups
71- Essential for Phase 2 Tap server matching
72
73### Indexes
74All tables are indexed for common query patterns:
75- Foreign key indexes for joins
76- Partial indexes for filtered queries (e.g., unnotified matches)
77- GIN indexes for fuzzy text matching
78
79### Cleanup Function
80The `cleanup_transient_data()` function automatically removes:
81- Expired OAuth states (>1 hour old)
82- Expired user sessions
83- Old notification records (>7 days sent, >30 days failed)
84
85This runs daily via BullMQ worker in production.
86
87## Testing
88
89### Test Connection
90```bash
91cd packages/api
92DATABASE_URL=postgresql://atlast:password@localhost:5432/atlast pnpm run test:db
93```
94
95This script verifies:
96- Database connectivity
97- Required extensions are installed
98- All tables exist
99- Indexes are created
100- Fuzzy matching works
101- Displays record counts
102
103### Manual Testing
104```bash
105# Connect to database
106docker compose exec database psql -U atlast
107
108# List tables
109\dt
110
111# List indexes
112\di
113
114# Check extensions
115SELECT * FROM pg_extension WHERE extname IN ('uuid-ossp', 'pg_trgm');
116
117# Test fuzzy matching
118SELECT similarity('johndoe', 'john_doe');
119
120# Run cleanup function
121SELECT cleanup_transient_data();
122```
123
124## Migration Notes
125
126### Phase 1 (Current)
127- No periodic checking features
128- Notification queue exists but is not used until Phase 2
129- Partner API keys table exists but is not used until Phase 2
130
131### Phase 2 (Future)
132- Tap server will use fuzzy matching to detect new accounts
133- Notification system will use the notification_queue table
134- Partner integrations will use the partner_api_keys table
135
136## Troubleshooting
137
138### Extensions Not Found
139```sql
140CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
141CREATE EXTENSION IF NOT EXISTS "pg_trgm";
142```
143
144### Permission Issues
145Ensure the database user has necessary permissions:
146```sql
147GRANT ALL PRIVILEGES ON DATABASE atlast TO atlast;
148GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO atlast;
149```
150
151### Connection Refused
152Check that:
153- PostgreSQL is running: `docker compose ps database`
154- Port is exposed: `docker compose port database 5432`
155- DATABASE_URL is correct: `postgresql://atlast:password@localhost:5432/atlast`