Database Setup - Athena ERP

Overview

Athena ERP uses PostgreSQL 16+ as its primary database. This guide covers setup options and migration management.

Database Requirements

PostgreSQL 16 or higher
Support for JSONB columns
UUID extension
Async connection support (asyncpg driver)

Option 1: Supabase (Recommended)

Supabase provides managed PostgreSQL with additional features like authentication, realtime subscriptions, and storage.

Step 1: Create Project

Go to supabase.com and sign in
Click New Project
Configure:
- Name: athena-erp
- Database Password: Generate strong password (save it!)
- Region: Choose closest to your users
- Pricing: Free tier is sufficient for development

Step 2: Get Connection Details

In Supabase Dashboard → Settings → Database:

Connection string (Transaction mode):

postgresql://postgres.xxxx:[password]@aws-0-us-east-1.pooler.supabase.com:6543/postgres

Convert to asyncpg format:

postgresql+asyncpg://postgres.xxxx:[password]@aws-0-us-east-1.pooler.supabase.com:6543/postgres

Use Transaction mode (port 6543) for migrations. Session mode (port 5432) doesn’t support all PostgreSQL features needed by Alembic.

Step 3: Get JWT Secret

In Supabase Dashboard → Settings → API:

Copy JWT Secret (used for JWT_SECRET in backend)
Copy anon public key (used for SUPABASE_ANON_KEY)
Copy service_role key (used for SUPABASE_SERVICE_ROLE_KEY)

Step 4: Update Backend Environment

In your backend .env or hosting platform:

DATABASE_URL=postgresql+asyncpg://postgres.xxxx:[password]@aws-0-us-east-1.pooler.supabase.com:6543/postgres
JWT_SECRET=<jwt-secret-from-supabase>
SUPABASE_URL=https://xxxx.supabase.co
SUPABASE_ANON_KEY=eyJhbGc...
SUPABASE_SERVICE_ROLE_KEY=eyJhbGc...

Option 2: Docker Compose (Development)

For local development, use the included Docker Compose configuration.

Step 1: Start Database

cd athena-api
docker-compose up -d db

This starts:

PostgreSQL 16 on port 5432
Credentials: athena / athena_dev
Database: athena_db

Step 2: Verify Connection

docker-compose ps

Should show db service as healthy.

Step 3: Access Database

# Via psql
psql postgresql://athena:athena_dev@localhost:5432/athena_db

# Or start pgAdmin (optional)
docker-compose --profile tools up -d pgadmin

pgAdmin available at http://localhost:5050:

Email: [email protected]
Password: admin

Step 4: Backend Configuration

DATABASE_URL=postgresql+asyncpg://athena:athena_dev@localhost:5432/athena_db

Option 3: Self-Hosted PostgreSQL

For production self-hosting.

Installation (Ubuntu/Debian)

# Add PostgreSQL repository
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Install PostgreSQL 16
sudo apt update
sudo apt install postgresql-16 postgresql-contrib-16 -y

Create Database and User

# Switch to postgres user
sudo -u postgres psql

-- Create user
CREATE USER athena WITH PASSWORD 'strong-secure-password';

-- Create database
CREATE DATABASE athena_db OWNER athena;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE athena_db TO athena;

-- Enable UUID extension
\c athena_db
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Exit
\q

Configure Access

Edit /etc/postgresql/16/main/pg_hba.conf:

# Allow local connections
local   all             athena                                  md5
host    all             athena          127.0.0.1/32            md5
host    all             athena          ::1/128                 md5

# For remote connections (production)
host    athena_db       athena          0.0.0.0/0               md5

Restart PostgreSQL:

sudo systemctl restart postgresql

Connection String

DATABASE_URL=postgresql+asyncpg://athena:strong-secure-password@localhost:5432/athena_db

Database Migrations

Athena uses Alembic for schema migrations.

Migration Files

Located in athena-api/alembic/versions/. Each file represents a database schema change.

Run Migrations

cd athena-api

# Upgrade to latest
alembic upgrade head

# Check current version
alembic current

# View migration history
alembic history

Initial Schema

The first migration creates:

users - User accounts
schools - Educational institutions
school_memberships - User-school relationships
academic_years - School years
academic_periods - Terms/semesters
grades - Grade levels
sections - Class sections
subjects - Courses/subjects
enrollments - Student enrollments
grade_components - Grade calculation components
student_grades - Individual grades

See athena-api/alembic/versions/ for detailed schema.

Create New Migration

When you modify models:

# Auto-generate migration
alembic revision --autogenerate -m "Description of changes"

# Review generated file in alembic/versions/

# Apply migration
alembic upgrade head

Rollback Migration

# Rollback one version
alembic downgrade -1

# Rollback to specific revision
alembic downgrade <revision_id>

# Rollback all
alembic downgrade base

Database Seeding

After migrations, seed initial data.

Create Superadmin

Required for first login:

cd athena-api

# Method 1: Direct script
python scripts/create_superadmin.py \
  --id <supabase-user-uuid> \
  --email [email protected] \
  --full-name "Admin User"

# Method 2: Environment variables
export SUPERADMIN_ID="<uuid>"
export SUPERADMIN_EMAIL="[email protected]"
export SUPERADMIN_FULL_NAME="Admin User"
python scripts/create_superadmin.py

The --id must match the UUID from Supabase auth.users table if using Supabase authentication.

Seed Sample Data (Optional)

For testing: Use the superadmin creation script to set up your first user:

cd athena-api
PYTHONPATH=. python scripts/create_superadmin.py \
  --id $(uuidgen) \
  --email [email protected] \
  --full-name "System Administrator" \
  --membership-roles superadmin

Backup & Restore

Backup Database

Supabase:

Dashboard → Database → Backups
Daily automatic backups (retained based on plan)
Manual backups available

Self-Hosted:

# Full backup
pg_dump -U athena -h localhost athena_db > backup_$(date +%Y%m%d).sql

# Compressed backup
pg_dump -U athena -h localhost athena_db | gzip > backup_$(date +%Y%m%d).sql.gz

# Schema only
pg_dump -U athena -h localhost athena_db --schema-only > schema.sql

Docker:

docker-compose exec db pg_dump -U athena athena_db > backup_$(date +%Y%m%d).sql

Restore Database

Self-Hosted:

# Drop and recreate
sudo -u postgres psql -c "DROP DATABASE athena_db;"
sudo -u postgres psql -c "CREATE DATABASE athena_db OWNER athena;"

# Restore
psql -U athena -h localhost athena_db < backup.sql

Docker:

docker-compose exec -T db psql -U athena athena_db < backup.sql

Monitoring

Supabase

Built-in monitoring:

Dashboard → Database → Usage
Connection pooling stats
Query performance insights

Self-Hosted

Check Connections

SELECT * FROM pg_stat_activity WHERE datname = 'athena_db';

Database Size

SELECT pg_size_pretty(pg_database_size('athena_db'));

Table Sizes

SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Slow Queries

Enable pg_stat_statements:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Performance Optimization

Connection Pooling

Supabase: Built-in pooling (use Transaction or Session mode) Self-Hosted: Use pgBouncer

sudo apt install pgbouncer -y

Configure /etc/pgbouncer/pgbouncer.ini:

[databases]
athena_db = host=localhost port=5432 dbname=athena_db

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Update connection string:

DATABASE_URL=postgresql+asyncpg://athena:password@localhost:6432/athena_db

Indexes

Critical indexes are created in migrations. Monitor with:

-- Unused indexes
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%_pkey';

-- Missing indexes
SELECT
  schemaname,
  tablename,
  seq_scan,
  idx_scan,
  seq_scan - idx_scan AS too_many_seq
FROM pg_stat_user_tables
WHERE seq_scan - idx_scan > 1000
ORDER BY too_many_seq DESC;

Vacuum

Automatic in PostgreSQL 10+, but manual vacuum helps:

# Analyze all tables
vacuumdb -U athena -d athena_db --analyze

# Full vacuum (requires downtime)
vacuumdb -U athena -d athena_db --full

Troubleshooting

”Connection refused”

Check:

PostgreSQL is running: sudo systemctl status postgresql
Port is open: sudo ss -tulpn | grep 5432
Firewall allows connection

”Password authentication failed”

Check:

Credentials are correct
User exists: psql -U postgres -c "\du"
pg_hba.conf allows connection method

”Too many connections”

Check:

Current connections: SELECT count(*) FROM pg_stat_activity;
Max connections: SHOW max_connections;
Consider connection pooling

”FATAL: sorry, too many clients already”

Increase max_connections in /etc/postgresql/16/main/postgresql.conf:

max_connections = 200

Restart: sudo systemctl restart postgresql

Migration fails

# Check current state
alembic current

# Check if manual changes were made
alembic check

# Force to specific version (DANGEROUS)
alembic stamp head

Security

Best Practices

Strong passwords: Use 32+ character random passwords
Limited access: Only allow necessary IPs in pg_hba.conf
SSL/TLS: Enable for production (Supabase does this automatically)
Principle of least privilege: Don’t use superuser for application
Regular backups: Automate daily backups
Update regularly: Keep PostgreSQL patched

SSL Configuration (Self-Hosted)

Generate certificates:

sudo -u postgres openssl req -new -x509 -days 365 -nodes \
  -out /var/lib/postgresql/16/main/server.crt \
  -keyout /var/lib/postgresql/16/main/server.key

sudo chmod 600 /var/lib/postgresql/16/main/server.key
sudo chown postgres:postgres /var/lib/postgresql/16/main/server.*

Enable in postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Update connection string:

DATABASE_URL=postgresql+asyncpg://athena:password@localhost:5432/athena_db?ssl=require

Next Steps

Backend Deployment

Deploy the API after database is ready

Environment Variables

Configure all database-related variables

Data Models

Understand the database schema

API Reference

Explore database-backed endpoints

Getting Started

Services

​Overview

​Database Requirements

​Option 1: Supabase (Recommended)

​Step 1: Create Project

​Step 2: Get Connection Details

​Step 3: Get JWT Secret

​Step 4: Update Backend Environment

​Option 2: Docker Compose (Development)

​Step 1: Start Database

​Step 2: Verify Connection

​Step 3: Access Database

​Step 4: Backend Configuration

​Option 3: Self-Hosted PostgreSQL

​Installation (Ubuntu/Debian)

​Create Database and User

​Configure Access

​Connection String

​Database Migrations

​Migration Files

​Run Migrations

​Initial Schema

​Create New Migration

​Rollback Migration

​Database Seeding

​Create Superadmin

​Seed Sample Data (Optional)

​Backup & Restore

​Backup Database

​Restore Database

​Monitoring

​Supabase

​Self-Hosted

​Check Connections

​Database Size

​Table Sizes

​Slow Queries

​Performance Optimization

​Connection Pooling

​Indexes

​Vacuum

​Troubleshooting

​”Connection refused”

​”Password authentication failed”

​”Too many connections”

​”FATAL: sorry, too many clients already”

​Migration fails

​Security

​Best Practices

​SSL Configuration (Self-Hosted)

​Next Steps

Backend Deployment

Environment Variables

Data Models

API Reference

Build docs developers (and LLMs) love

Overview

Database Requirements

Option 1: Supabase (Recommended)

Step 1: Create Project

Step 2: Get Connection Details

Step 3: Get JWT Secret

Step 4: Update Backend Environment

Option 2: Docker Compose (Development)

Step 1: Start Database

Step 2: Verify Connection

Step 3: Access Database

Step 4: Backend Configuration

Option 3: Self-Hosted PostgreSQL

Installation (Ubuntu/Debian)

Create Database and User

Configure Access

Connection String

Database Migrations

Migration Files

Run Migrations

Initial Schema

Create New Migration

Rollback Migration

Database Seeding

Create Superadmin

Seed Sample Data (Optional)

Backup & Restore

Backup Database

Restore Database

Monitoring

Supabase

Self-Hosted

Check Connections

Database Size

Table Sizes

Slow Queries

Performance Optimization

Connection Pooling

Indexes

Vacuum

Troubleshooting

”Connection refused”

”Password authentication failed”

”Too many connections”

”FATAL: sorry, too many clients already”

Migration fails

Security

Best Practices

SSL Configuration (Self-Hosted)

Next Steps