Database Setup

Overview

Support Bot uses three database systems:

PostgreSQL - Main application database (port 5434)
pgvector - Vector embeddings storage (port 5433)
Qdrant - Vector search engine (ports 6333, 6334)

Database migrations are managed using Alembic, which handles schema changes and versioning.

Database Services

Main Database (PostgreSQL)

Stores application data including:

User accounts and authentication
Chat conversations and messages
LangGraph checkpoints
Application metadata

Configuration:

image: postgres:18-alpine
environment:
  POSTGRES_USER: postgres
  POSTGRES_PASSWORD: admin
  POSTGRES_DB: bot_db
port: 5434

Connection String:

DATABASE_URL=postgresql+asyncpg://postgres:admin@localhost:5434/bot_db
DATABASE_URL_SYNC=postgresql://postgres:admin@localhost:5434/bot_db

Vector Database (pgvector)

Stores vector embeddings for semantic search and retrieval:

Document embeddings
Sentence transformers output
RAG (Retrieval-Augmented Generation) data

Configuration:

image: ankane/pgvector
environment:
  POSTGRES_USER: postgres
  POSTGRES_PASSWORD: admin
  POSTGRES_DB: vector_db
port: 5433

Connection String:

VECTOR_DATABASE_URL=postgresql://postgres:admin@localhost:5433/vector_db

Qdrant

Vector similarity search engine for:

Fast nearest neighbor search
Document retrieval
Semantic search operations

Configuration:

image: qdrant/qdrant
ports:
  - 6333 (HTTP API)
  - 6334 (gRPC)

URL:

QDRANT_URL=http://localhost:6333

Database Initialization

First-Time Setup

Start database services

docker-compose up -d db vector_db qdrant

Verify databases are running

docker ps | grep -E 'postgres|qdrant'

Expected output:

postgres-db (port 5434)
vector-db (port 5433)
qdrant-db (ports 6333, 6334)

Run initial migrations

Navigate to the Alembic directory and upgrade to the latest schema:

cd src/api/db
alembic upgrade head

This creates all necessary tables and indexes.

Verify migration status

alembic current

Shows the current migration revision.

Production Deployment

In production, the Docker container automatically runs migrations on startup:

# From Dockerfile.backend.prod
uv run alembic -c src/api/db/alembic.ini upgrade head

No manual intervention required.

Alembic Migrations

Alembic manages database schema versions and changes.

Migration Commands

Upgrade to latest schema

cd src/api/db
alembic upgrade head

Applies all pending migrations.

Check current migration

alembic current

Shows the current revision ID.

View migration history

alembic history --verbose

Lists all migrations with details.

Downgrade to previous version

alembic downgrade -1

Rolls back the last migration.

Creating New Migrations

When you modify database models in src/api/db/models.py, create a migration:

Navigate to Alembic directory

cd src/api/db

Generate migration

alembic revision --autogenerate -m "describe changes"

Alembic automatically detects model changes and generates migration code.Example:

alembic revision --autogenerate -m "add user role column"

Review generated migration

Check the generated file in src/api/db/versions/:

def upgrade() -> None:
    # Review auto-generated changes
    op.add_column('users', sa.Column('role', sa.String(), nullable=True))

def downgrade() -> None:
    # Review rollback logic
    op.drop_column('users', 'role')

Verify the changes are correct before applying.

Apply migration

alembic upgrade head

Migration Best Practices

Always review auto-generated migrations

Alembic’s autogenerate is powerful but not perfect. Review:

Column types and constraints
Index definitions
Data migration needs
Downgrade logic

Test migrations on a copy of production data

# Backup production
pg_dump -h localhost -p 5434 -U postgres bot_db > backup.sql

# Restore to test database
psql -h localhost -p 5434 -U postgres test_db < backup.sql

# Test migration
alembic upgrade head

Write descriptive migration messages

Use clear, action-oriented descriptions:

Good: "add email verification to users"
Good: "create conversations table with indexes"
Bad: "update db"
Bad: "changes"

Keep migrations small and focused

One logical change per migration:

Add a table
Modify a column
Create an index

Avoid bundling unrelated changes.

Database Management

Using Adminer

Adminer provides a web UI for database management (development only):

Open http://localhost:8080
Login credentials:
- System: PostgreSQL
- Server: db:5432 (for main DB) or vector_db:5432
- Username: postgres
- Password: admin
- Database: bot_db or vector_db

Using psql

Connect directly via command line:

# Main database
docker exec -it postgres-db psql -U postgres -d bot_db

# Vector database
docker exec -it vector-db psql -U postgres -d vector_db

Common SQL Operations

List Tables
Describe Table
Check Connections
Database Size

\dt

\d table_name

SELECT * FROM pg_stat_activity;

SELECT pg_size_pretty(pg_database_size('bot_db'));

Backup and Restore

Backup Database

Using pg_dump
Using Docker volumes

# Backup main database
docker exec postgres-db pg_dump -U postgres bot_db > bot_db_backup.sql

# Backup vector database
docker exec vector-db pg_dump -U postgres vector_db > vector_db_backup.sql

# Backup volume as tar
docker run --rm -v bot_db_data:/data -v $(pwd):/backup \
  alpine tar czf /backup/bot_db.tar.gz -C /data .

Restore Database

From SQL dump
From volume backup

# Restore main database
docker exec -i postgres-db psql -U postgres bot_db < bot_db_backup.sql

# Restore vector database
docker exec -i vector-db psql -U postgres vector_db < vector_db_backup.sql

# Stop containers
docker-compose down

# Restore volume
docker run --rm -v bot_db_data:/data -v $(pwd):/backup \
  alpine tar xzf /backup/bot_db.tar.gz -C /data

# Start containers
docker-compose up -d

Troubleshooting

Migration Errors

'current' revision not found

Initialize Alembic:

alembic stamp head

Constraint violation during migration

Check existing data compatibility
Add data migration step:

def upgrade() -> None:
    # Update existing data first
    op.execute("UPDATE users SET role = 'user' WHERE role IS NULL")
    # Then add constraint
    op.alter_column('users', 'role', nullable=False)

Multiple heads detected

Merge migration branches:

alembic merge heads -m "merge migrations"
alembic upgrade head

Connection Issues

Connection refused

Verify container is running:
```
docker ps | grep postgres
```
Check port mapping:
```
docker port postgres-db
```
Test connection:
```
telnet localhost 5434
```

Authentication failed

Check credentials in environment variables:

echo $DATABASE_URL

Ensure they match Docker Compose configuration.

Next Steps

Environment Variables

Configure database connection strings

Docker Deployment

Deploy with Docker Compose

Get Started

Core Features

Administration

Deployment

Overview

Database Services

Main Database (PostgreSQL)

Vector Database (pgvector)

Qdrant

Database Initialization

First-Time Setup

Production Deployment

Alembic Migrations

Migration Commands

Creating New Migrations

Migration Best Practices

Database Management

Using Adminer

Using psql

Common SQL Operations

Backup and Restore

Backup Database

Restore Database

Troubleshooting

Migration Errors

Connection Issues

Next Steps

Environment Variables

Docker Deployment

Build docs developers (and LLMs) love

Get Started

Core Features

Administration

Deployment

​Overview

​Database Services

​Main Database (PostgreSQL)

​Vector Database (pgvector)

​Qdrant

​Database Initialization

​First-Time Setup

​Production Deployment

​Alembic Migrations

​Migration Commands

​Creating New Migrations

​Migration Best Practices

​Database Management

​Using Adminer

​Using psql

​Common SQL Operations

​Backup and Restore

​Backup Database

​Restore Database

​Troubleshooting

​Migration Errors

​Connection Issues

​Next Steps

Environment Variables

Docker Deployment

Build docs developers (and LLMs) love

Overview

Database Services

Main Database (PostgreSQL)

Vector Database (pgvector)

Qdrant

Database Initialization

First-Time Setup

Production Deployment

Alembic Migrations

Migration Commands

Creating New Migrations

Migration Best Practices

Database Management

Using Adminer

Using psql

Common SQL Operations

Backup and Restore

Backup Database

Restore Database

Troubleshooting

Migration Errors

Connection Issues

Next Steps