Database Scripts#

This directory contains database schema and utility scripts for the ATlast migration.

Files#

init-db.sql - PostgreSQL schema definition (tables, indexes, functions)
seed-test-data.sql - Test data for local development
generate-encryption-key.ts - OAuth key generation utility
keygen.js - Legacy key generation script

Database Setup#

Local Development (Docker)#

Start PostgreSQL container:

cd docker
docker compose up -d database

Initialize schema:

docker compose exec database psql -U atlast -d atlast -f /docker-entrypoint-initdb.d/init.sql

Or connect and run manually:

docker compose exec -i database psql -U atlast -d atlast < scripts/init-db.sql

Seed test data (optional):

docker compose exec -i database psql -U atlast -d atlast < scripts/seed-test-data.sql

Verify setup:

cd packages/api
DATABASE_URL=postgresql://atlast:password@localhost:5432/atlast pnpm run test:db

Production Setup#

The 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.

Schema Overview#

Transient Tables (Session Data)#

oauth_states - OAuth flow state storage
oauth_sessions - OAuth session data
user_sessions - User authentication sessions
notification_queue - Pending notifications (Phase 2)

Note: Transient data is cleaned up daily via the cleanup_transient_data() function.

Persistent Tables (User Data)#

user_uploads - Upload history and metadata
source_accounts - Usernames from source platforms (Instagram, TikTok, etc.)
user_source_follows - Links users to their source account follows
atproto_matches - Matched AT Protocol accounts
user_match_status - User interaction with matches (viewed, followed, etc.)
partner_api_keys - API keys for partner integrations (Phase 2)

Key Features#

Fuzzy Matching#

The schema includes the pg_trgm extension for fuzzy username matching. This enables:

Similarity-based searches (% operator)
Trigram GIN indexes for fast fuzzy lookups
Essential for Phase 2 Tap server matching

Indexes#

All tables are indexed for common query patterns:

Foreign key indexes for joins
Partial indexes for filtered queries (e.g., unnotified matches)
GIN indexes for fuzzy text matching

Cleanup Function#

The cleanup_transient_data() function automatically removes:

Expired OAuth states (>1 hour old)
Expired user sessions
Old notification records (>7 days sent, >30 days failed)

This runs daily via BullMQ worker in production.

Testing#

Test Connection#

cd packages/api
DATABASE_URL=postgresql://atlast:password@localhost:5432/atlast pnpm run test:db

This script verifies:

Database connectivity
Required extensions are installed
All tables exist
Indexes are created
Fuzzy matching works
Displays record counts

Manual Testing#

# Connect to database
docker compose exec database psql -U atlast

# List tables
\dt

# List indexes
\di

# Check extensions
SELECT * FROM pg_extension WHERE extname IN ('uuid-ossp', 'pg_trgm');

# Test fuzzy matching
SELECT similarity('johndoe', 'john_doe');

# Run cleanup function
SELECT cleanup_transient_data();

Migration Notes#

Phase 1 (Current)#

No periodic checking features
Notification queue exists but is not used until Phase 2
Partner API keys table exists but is not used until Phase 2

Phase 2 (Future)#

Tap server will use fuzzy matching to detect new accounts
Notification system will use the notification_queue table
Partner integrations will use the partner_api_keys table

Troubleshooting#

Extensions Not Found#

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

Permission Issues#

Ensure the database user has necessary permissions:

GRANT ALL PRIVILEGES ON DATABASE atlast TO atlast;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO atlast;

Connection Refused#

Check that:

PostgreSQL is running: docker compose ps database
Port is exposed: docker compose port database 5432
DATABASE_URL is correct: postgresql://atlast:password@localhost:5432/atlast

Configure Feed