KytheraDB -- Deployment Guide

Instructions for deploying KytheraDB using Docker Compose or running locally for development.

---

Table of Contents

1. Prerequisites
2. Docker Compose Setup
3. Environment Variables
4. Database Initialization
5. pg_stat_statements Setup
6. Building for Production
7. Frontend Build
8. Backend Configuration

---

Prerequisites

Docker Deployment

• Docker Engine 24.0+
• Docker Compose v2.20+
• Git

Local Development

• Python 3.13+
• Node.js 20+ (with npm 11+)
• PostgreSQL 16 (for the platform database)

---

Docker Compose Setup

The project includes a docker-compose.yml that provisions all three services:

services:
  db:        # PostgreSQL 16 (platform database)
  backend:   # FastAPI application
  frontend:  # Angular SPA served by Nginx
  mailpit:   # Email capture (dev/test)

Quick Start

git clone <repository-url> auralith
cd auralith
docker compose up -d

Service Ports

Service	Port	URL
Frontend	4200	http://localhost:4200
Backend	8000	http://localhost:8000
PostgreSQL	5432	localhost:5432
Mailpit UI	18025	http://localhost:18025
Mailpit SMTP	1025 (internal)	mailpit:1025 (docker network)

Database Credentials (Default)

Parameter	Value
User	auralith
Password	auralith
Database	auralith_db

Health Check

The PostgreSQL service includes a health check. The backend waits for the database to be healthy before starting:

healthcheck:
  test: ["CMD-SHELL", "pg_isready -U auralith -d auralith_db"]
  interval: 5s
  timeout: 3s
  retries: 5

Persistent Storage

Database data is stored in a Docker volume:

volumes:
  pgdata:

To reset the database completely:

docker compose down -v
docker compose up -d

---

Environment Variables

Backend

Variable	Default	Description
DATABASE_URL	postgresql+asyncpg://auralith:auralith@localhost:5432/auralith_db	Platform database connection string
SECRET_KEY	change-me-in-production	JWT signing key (change in production!)
ENCRYPTION_KEY	(empty -- derived from SECRET_KEY)	Fernet key for connection credential encryption
CORS_ORIGINS	["http://localhost:4200"]	Allowed CORS origins (JSON array)
APP_NAME	KytheraDB	Application name
DEBUG	false	Debug mode
SMTP_HOST	localhost	SMTP server (Mailpit in dev)
SMTP_PORT	1025	SMTP port
SMTP_USER	(empty)	SMTP username (none for Mailpit)
SMTP_PASSWORD	(empty)	SMTP password
SMTP_FROM	noreply@auralith.dev	Sender email address
STRIPE_SECRET_KEY	(empty)	Stripe secret key (sk_test_...)
STRIPE_PUBLISHABLE_KEY	(empty)	Stripe publishable key (pk_test_...)
STRIPE_WEBHOOK_SECRET	(empty)	Stripe webhook signing secret
APP_URL	http://localhost:4200	Frontend URL (for email links)

Setting Environment Variables

Docker Compose: Set in docker-compose.yml under the backend.environment section or use a .env file.

Local development: Create a .env file in the backend/ directory:

DATABASE_URL=postgresql+asyncpg://auralith:auralith@localhost:5432/auralith_db
SECRET_KEY=your-secure-random-key-here
CORS_ORIGINS=["http://localhost:4200"]

Production Considerations

• SECRET_KEY: Use a cryptographically random string of at least 32 characters. This key signs all JWT tokens and derives the encryption key for stored credentials.
• ENCRYPTION_KEY: Optionally provide an explicit Fernet key. If omitted, it is derived from SECRET_KEY via SHA-256.
• CORS_ORIGINS: Restrict to your actual frontend domain(s).
• DATABASE_URL: Use a production PostgreSQL instance with proper authentication and SSL.

---

Database Initialization

Automatic Migration

On startup, the backend application automatically:

1. Creates all required tables using SQLAlchemy Base.metadata.create_all().
2. Runs incremental ALTER TABLE statements to add columns for newer features (POC-style migration).
3. Creates the jobs table if it does not exist.

No manual migration steps are required for the initial setup.

Manual Database Setup

If running PostgreSQL outside Docker:

# Create database
createdb -U postgres auralith_db

# Create user
psql -U postgres -c "CREATE USER auralith WITH PASSWORD 'auralith';"
psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE auralith_db TO auralith;"
psql -U postgres -c "ALTER DATABASE auralith_db OWNER TO auralith;"

---

pg_stat_statements Setup

The Performance Monitoring feature requires the pg_stat_statements PostgreSQL extension.

Docker Compose (Pre-configured)

The Docker Compose configuration already enables this:

command: postgres -c shared_preload_libraries='pg_stat_statements' -c pg_stat_statements.track=all

Manual Setup

1. Edit postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

2. Restart PostgreSQL.

3. Enable the extension in each managed database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verification

The application provides a pre-flight check endpoint:

GET /projects/{project_id}/performance/check/{connection_id}

This verifies that pg_stat_statements is properly installed and accessible.

---

Building for Production

Backend

The backend uses a Dockerfile in the backend/ directory:

cd backend
docker build -t auralith-backend .

For running without Docker:

cd backend
python -m venv venv
source venv/bin/activate    # Linux/Mac
# or: venv\Scripts\activate  # Windows
pip install -r requirements.txt
uvicorn app.main:app --host 0.0.0.0 --port 8000 --workers 4

Production Settings

• Use multiple workers: --workers 4 (adjust based on CPU cores)
• Set DEBUG=false
• Use a proper SECRET_KEY
• Configure a production DATABASE_URL with SSL

Frontend

The frontend uses a Dockerfile in the frontend/ directory that builds the Angular application and serves it with Nginx:

cd frontend
docker build -t auralith-frontend .

For building without Docker:

cd frontend
npm install
npm run build

The build output is in frontend/dist/. Serve with any static file server or Nginx.

---

Frontend Build

Development Server

cd frontend
npm install
npm start

Serves at http://localhost:4200 with hot reload.

Production Build

npm run build

Key Dependencies

Package	Version	Purpose
@angular/core	^21.0.0	Angular framework
@codemirror/lang-sql	^6.10.0	SQL editor support
cytoscape	^3.33.1	Graph visualization
tailwindcss	^4.2.2	CSS framework
typescript	~5.9.2	TypeScript compiler

---

Backend Configuration

Configuration File

The backend uses pydantic-settings which reads configuration from:

1. Environment variables (highest priority)
2. .env file in the backend/ directory
3. Default values in app/core/config.py

Configuration Class

class Settings(BaseSettings):
    DATABASE_URL: str = "postgresql+asyncpg://auralith:auralith@localhost:5432/auralith_db"
    SECRET_KEY: str = "change-me-in-production"
    ENCRYPTION_KEY: str = ""
    CORS_ORIGINS: list[str] = ["http://localhost:4200"]
    APP_NAME: str = "KytheraDB"
    DEBUG: bool = False

CORS Configuration

The backend configures CORS middleware to allow:

• Origins: defined by CORS_ORIGINS
• Credentials: enabled
• Methods: all
• Headers: all

For production, restrict CORS_ORIGINS to your frontend domain only.

Key Backend Dependencies

Package	Version	Purpose
fastapi	0.115.0	Web framework
uvicorn	0.30.0	ASGI server
sqlalchemy	2.0.35	ORM (async mode)
asyncpg	0.30.0	PostgreSQL async driver
psycopg	3.3.3	PostgreSQL sync driver (introspection)
pymysql	1.1.1	MySQL/MariaDB driver
pydantic	2.9.0	Data validation
argon2-cffi	23.1.0	Password hashing
PyJWT	2.9.0	JWT tokens
sqlglot	30.1.0	SQL parsing and analysis
faker	30.0.0	Test data generation
cryptography	43.0.0	Connection credential encryption
sshtunnel	0.4.0	SSH tunnel support
stripe	8.0.0	Payment processing
aiosmtplib	3.0.1	Async email sending
slowapi	0.1.9	Rate limiting