Database Configuration - Zou (Kitsu API)

Zou uses PostgreSQL as its database backend with SQLAlchemy as the ORM. This guide covers database setup, configuration, and maintenance.

Prerequisites

Zou requires:

PostgreSQL 10 or higher
A dedicated database and user account
Network connectivity between Zou and PostgreSQL

Database Setup

1. Create Database and User

Connect to PostgreSQL and create a database and user:

CREATE DATABASE zoudb;
CREATE USER zouuser WITH ENCRYPTED PASSWORD 'secure-password';
GRANT ALL PRIVILEGES ON DATABASE zoudb TO zouuser;

2. Configure Environment Variables

Set the database connection parameters:

export DB_HOST="localhost"
export DB_PORT="5432"
export DB_USERNAME="zouuser"
export DB_PASSWORD="secure-password"
export DB_DATABASE="zoudb"
export DB_DRIVER="postgresql+psycopg"  # Default, psycopg3

3. Initialize Database Schema

Run the database initialization command:

zou init-db

This command:

Creates all required tables using Flask-Migrate (Alembic)
Applies all migration files from zou/migrations/versions/
Sets up initial database schema

4. Initialize Base Data

Create the minimal required data:

zou init-data

This creates essential records like project statuses and task types.

5. Create Admin User

Create an admin user to access the API:

zou create-admin admin@example.com --password MySecurePassword123

Connection Configuration

Basic Connection Parameters

DB_DRIVER

string

default:"postgresql+psycopg"

SQLAlchemy database driver.

postgresql+psycopg - psycopg3 (recommended, default)
postgresql+psycopg2 - psycopg2 (legacy)

DB_HOST

string

default:"localhost"

Database server hostname or IP address.

DB_PORT

string

default:"5432"

Database server port.

DB_USERNAME

string

default:"postgres"

Database username for authentication.

DB_PASSWORD

string

default:"mysecretpassword"

Database password for authentication.

DB_DATABASE

string

default:"zoudb"

Name of the database to connect to.

Connection URI

The connection URI is automatically generated from the configuration:

# From zou/app/utils/dbhelpers.py
from sqlalchemy.engine.url import URL

DATABASE = {
    "drivername": "postgresql+psycopg",
    "host": "localhost",
    "port": "5432",
    "username": "zouuser",
    "password": "secure-password",
    "database": "zoudb",
}

SQLALCHEMY_DATABASE_URI = URL.create(**DATABASE).render_as_string(hide_password=False)
# Result: postgresql+psycopg://zouuser:secure-password@localhost:5432/zoudb

Connection Pool Settings

Zou uses SQLAlchemy’s connection pooling to manage database connections efficiently. These settings are critical for production performance.

Pool Configuration

DB_POOL_SIZE

integer

default:"30"

Number of connections to maintain in the pool.Recommendations:

Development: 5-10
Production (small): 20-30
Production (large): 50-100

Consider: concurrent users × average requests per user

DB_MAX_OVERFLOW

integer

default:"60"

Maximum connections beyond pool_size.Total max connections = DB_POOL_SIZE + DB_MAX_OVERFLOWTypically set to 2× pool_size for burst traffic.

DB_POOL_PRE_PING

boolean

default:"True"

Test connections before use to prevent stale connection errors.Adds slight overhead but prevents errors. Recommended: enabled

DB_POOL_RECYCLE

integer

default:"3600"

Recycle connections after N seconds (default: 1 hour).Prevents issues with:

Database connection timeouts
Network firewalls closing idle connections
Long-lived connection problems

Set lower than your database’s connection timeout.

DB_POOL_RESET_ON_RETURN

string

default:"commit"

How to reset connections when returned to pool.Options:

commit - Commit any pending transactions (recommended)
rollback - Roll back any pending transactions
none - No reset (not recommended)

Ensures clean state between requests.

Example Pool Configuration

# From zou/app/config.py
SQLALCHEMY_ENGINE_OPTIONS = {
    "pool_size": 30,
    "max_overflow": 60,
    "pool_pre_ping": True,
    "pool_recycle": 3600,
    "pool_reset_on_return": "commit",
}

Production Pool Sizing

Formula for pool sizing:

pool_size = (concurrent_users × requests_per_second) / query_time

Example:

100 concurrent users
2 requests per second per user
50ms average query time

pool_size = (100 × 2) / (1000ms / 50ms) = 10 connections

Add headroom: set DB_POOL_SIZE=30 and DB_MAX_OVERFLOW=60

Make sure PostgreSQL’s max_connections is higher than:

(DB_POOL_SIZE + DB_MAX_OVERFLOW) × number_of_zou_instances

Default PostgreSQL max_connections is 100.

Database Migrations

Zou uses Flask-Migrate (built on Alembic) for database schema migrations.

Migration Commands

Check Database Status

zou is-db-ready

Returns whether the database is initialized.

Initialize Database

zou init-db

Creates all tables by applying migrations.

Upgrade Database Schema

zou upgrade-db

Applies any new migrations. Run after upgrading Zou.

# Disable telemetry
zou upgrade-db --no-telemetry

Downgrade Database (Development)

# Downgrade to previous revision
zou downgrade-db --revision -1

# Downgrade to specific revision
zou downgrade-db --revision abc123def456

Downgrading can cause data loss. Only use in development.

Create Migration (Development)

# Auto-generate migration from model changes
zou migrate-db --message "Add new field to tasks"

This generates a new migration file in zou/migrations/versions/.

Reset Database

# Drop all tables and recreate
zou reset-db

This deletes ALL data. Only use in development.

# Drop tables only
zou clear-db

Migration Files Location

Migrations are stored in:

zou/migrations/
├── env.py              # Alembic environment configuration
├── versions/           # Migration files
│   ├── 003be8a91001_add_start_and_end_dates_to_projects.py
│   ├── 0596674df51d_add_department_mentions_to_comments.py
│   └── ...
└── utils/              # Migration utilities

Migration File Structure

# Example migration file
"""Add start and end dates to projects

Revision ID: 003be8a91001
Revises: previous_revision_id
Create Date: 2023-01-15 10:30:00
"""

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('project', 
        sa.Column('start_date', sa.Date(), nullable=True))
    op.add_column('project', 
        sa.Column('end_date', sa.Date(), nullable=True))

def downgrade():
    op.drop_column('project', 'end_date')
    op.drop_column('project', 'start_date')

Custom Migration Configuration

The migration environment is configured in zou/migrations/env.py:

def run_migrations_online():
    connectable = create_engine(url, poolclass=pool.NullPool)
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=db.metadata,
            process_revision_directives=process_revision_directives,
            render_item=render_item,
            include_object=include_object,
        )
        with context.begin_transaction():
            context.run_migrations()

Database Maintenance

Backup Database

# Dump database to file
zou dump-database

# Dump and store to object storage
zou dump-database --store

Clear Old Data

# Remove old events, notifications, and login logs (>90 days)
zou remove-old-data --days 90

Verify Database Connection

From zou/app/utils/dbhelpers.py:587:

def is_init():
    """Check if database is initialized."""
    from zou.app import db
    from zou.app.models.project_status import ProjectStatus
    
    return (
        inspect(db.engine).has_table("person") and 
        db.session.query(ProjectStatus).count() == 2
    )

Troubleshooting

Connection Refused

Error: psycopg.OperationalError: connection refused Solutions:

Check PostgreSQL is running: systemctl status postgresql
Verify host and port: psql -h localhost -p 5432 -U zouuser -d zoudb
Check firewall rules
Verify postgresql.conf listen_addresses

Too Many Connections

Error: FATAL: remaining connection slots are reserved Solutions:

Increase PostgreSQL max_connections in postgresql.conf
Reduce DB_POOL_SIZE and DB_MAX_OVERFLOW
Check for connection leaks in application code

Stale Connections

Error: server closed the connection unexpectedly Solutions:

Enable DB_POOL_PRE_PING=True
Lower DB_POOL_RECYCLE (try 300-600 seconds)
Increase PostgreSQL timeout settings

Slow Queries

Solutions:

Enable PostgreSQL query logging:

ALTER DATABASE zoudb SET log_min_duration_statement = 1000;

Check missing indexes:

SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE schemaname = 'public'
ORDER BY abs(correlation) DESC;

Run ANALYZE to update statistics:
```
ANALYZE;
```

Migration Conflicts

Error: Target database is not up to date Solutions:

# Check current revision
zou stamp-db

# Force to specific revision (if needed)
zou stamp-db --revision abc123def456

# Then upgrade
zou upgrade-db

Performance Tips

Use connection pooling - Default settings work well for most deployments
Enable pre-ping - Prevents stale connection errors
Monitor pool usage - Watch for pool exhaustion in logs
Regular vacuuming - PostgreSQL autovacuum should be enabled
Add indexes - Zou includes performance indexes in migrations
Optimize queries - Use query profiling for slow endpoints

Security Best Practices

Use strong passwords - Never use default passwords in production
Restrict network access - Firewall PostgreSQL to only Zou servers
Use SSL connections - Configure PostgreSQL SSL and use sslmode=require
Regular backups - Automate database backups
Principle of least privilege - Database user should only have necessary permissions
Update regularly - Keep PostgreSQL updated with security patches

Get Started

Core Concepts

Configuration

CLI Reference

Documentation Index

​Prerequisites

​Database Setup

​1. Create Database and User

​2. Configure Environment Variables

​3. Initialize Database Schema

​4. Initialize Base Data

​5. Create Admin User

​Connection Configuration

​Basic Connection Parameters

​Connection URI

​Connection Pool Settings

​Pool Configuration

​Example Pool Configuration

​Production Pool Sizing

​Database Migrations

​Migration Commands

​Check Database Status

​Initialize Database

​Upgrade Database Schema

​Downgrade Database (Development)

​Create Migration (Development)

​Reset Database

​Migration Files Location

​Migration File Structure

​Custom Migration Configuration

​Database Maintenance

​Backup Database

​Clear Old Data

​Verify Database Connection

​Troubleshooting

​Connection Refused

​Too Many Connections

​Stale Connections

​Slow Queries

​Migration Conflicts

​Performance Tips

​Security Best Practices

Build docs developers (and LLMs) love

Prerequisites

Database Setup

1. Create Database and User

2. Configure Environment Variables

3. Initialize Database Schema

4. Initialize Base Data

5. Create Admin User

Connection Configuration

Basic Connection Parameters

Connection URI

Connection Pool Settings

Pool Configuration

Example Pool Configuration

Production Pool Sizing

Database Migrations

Migration Commands

Check Database Status

Initialize Database

Upgrade Database Schema

Downgrade Database (Development)

Create Migration (Development)

Reset Database

Migration Files Location

Migration File Structure

Custom Migration Configuration

Database Maintenance

Backup Database

Clear Old Data

Verify Database Connection

Troubleshooting

Connection Refused

Too Many Connections

Stale Connections

Slow Queries

Migration Conflicts

Performance Tips

Security Best Practices