Database Setup¶

AMP uses PostgreSQL for persistent storage.

Requirements¶

PostgreSQL 12+ (15 recommended)
Standard extensions (no special requirements)

Setup¶

Create Database¶

CREATE USER amp WITH PASSWORD 'your-secure-password';
CREATE DATABASE amp OWNER amp;
GRANT ALL PRIVILEGES ON DATABASE amp TO amp;

Connection String¶

postgres://amp:your-secure-password@localhost:5432/amp?sslmode=require

Running Migrations¶

# Apply all migrations
./bin/api migrate up

# Check status
./bin/api migrate status

# Rollback last migration
./bin/api migrate down

# Reset database (development only)
./bin/api migrate reset

Schema Overview¶

Core Tables¶

Table	Description
`users`	User accounts (synced from Clerk)
`tenants`	Organizations
`user_tenants`	User-tenant membership
`api_keys`	API key credentials

Content Tables¶

Table	Description
`missions`	Content missions
`content`	Generated content
`published_posts`	Publishing history
`brand_context`	Brand configuration

Pipeline Tables¶

Table	Description
`pipeline_jobs`	Job tracking
`job_logs`	Execution logs

Backup¶

pg_dump¶

pg_dump -U amp -h localhost amp > backup.sql

Scheduled Backups¶

# crontab entry
0 2 * * * pg_dump -U amp amp | gzip > /backups/amp-$(date +\%Y\%m\%d).sql.gz

Point-in-Time Recovery¶

Enable WAL archiving:

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

Performance Tuning¶

Connection Pooling¶

Use PgBouncer for connection pooling:

# pgbouncer.ini
[databases]
amp = host=localhost dbname=amp

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

PostgreSQL Configuration¶

# postgresql.conf
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 16MB
maintenance_work_mem = 128MB
max_connections = 100

Indexes¶

AMP creates necessary indexes via migrations. Additional indexes for high-volume queries:

-- Content by mission and status
CREATE INDEX idx_content_mission_status ON content(mission_id, status);

-- Jobs by status
CREATE INDEX idx_jobs_status ON pipeline_jobs(status);

Monitoring¶

Query Performance¶

-- Slow queries
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Connection Usage¶

SELECT count(*), state
FROM pg_stat_activity
GROUP BY state;

Table Sizes¶

SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

High Availability¶

Streaming Replication¶

Primary:

wal_level = replica
max_wal_senders = 3

Replica:

primary_conninfo = 'host=primary port=5432 user=replicator'

Failover¶

Use Patroni or pg_auto_failover for automatic failover.

Troubleshooting¶

Connection Issues¶

# Test connection
psql $DATABASE_URL -c "SELECT 1"

# Check max connections
psql $DATABASE_URL -c "SHOW max_connections"

Lock Issues¶

-- View locks
SELECT pid, query, wait_event_type, wait_event
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

-- Kill stuck query
SELECT pg_terminate_backend(pid);

Disk Space¶

-- Reclaim space
VACUUM FULL;

-- Check bloat
SELECT schemaname, relname, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;