Database Configuration

Overview

VoicePact uses SQLAlchemy as its ORM with support for both SQLite (development) and PostgreSQL (production). The database stores contracts, parties, signatures, payments, audit logs, and session data.

Database Architecture

Core Tables

VoicePact’s database schema includes:

contracts - Voice-powered contract records
contract_parties - Parties involved in contracts (buyer, seller, mediator, witness)
contract_signatures - Digital signatures and confirmation status
payments - Escrow payment transactions
audit_logs - Immutable event trail for compliance
voice_recordings - Audio file metadata and processing status
ussd_sessions - USSD interaction state management
sms_logs - SMS delivery tracking

Entity Relationships

contracts (1) ──── (N) contract_parties
    │
    ├──── (N) contract_signatures
    │
    ├──── (N) payments
    │
    └──── (N) audit_logs

SQLite Configuration (Development)

SQLite is the default database for development and small deployments.

Advantages

Zero configuration
No separate server required
Perfect for development and testing
Single file database

Limitations

Limited concurrency
Not recommended for production with >10 concurrent users
No built-in replication

Setup SQLite

Configure environment variable:

# In server/.env
DATABASE_URL=sqlite:///./voicepact.db

Initialize database:

cd server
source venv/bin/activate
python -c "from app.core.database import init_database; import asyncio; asyncio.run(init_database())"

Or start the application (database is auto-initialized):

uvicorn main:app --reload

Verify database creation:

ls -lh voicepact.db
sqlite3 voicepact.db "SELECT name FROM sqlite_master WHERE type='table';"

SQLite Optimizations

VoicePact automatically applies performance optimizations:

PRAGMA journal_mode=WAL;      -- Write-Ahead Logging for concurrency
PRAGMA synchronous=NORMAL;    -- Balanced durability/performance
PRAGMA cache_size=-64000;     -- 64MB cache
PRAGMA temp_store=MEMORY;     -- In-memory temporary tables
PRAGMA mmap_size=268435456;   -- 256MB memory-mapped I/O

These are applied automatically in app/core/database.py:46-52.

PostgreSQL Configuration (Production)

For production deployments, PostgreSQL is strongly recommended.

Prerequisites

PostgreSQL 13+ installed
Database user with CREATE privileges
Network connectivity from application server

Installation

Ubuntu/Debian
macOS (Homebrew)
Docker

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

brew install postgresql@15
brew services start postgresql@15

docker run -d \
  --name voicepact-postgres \
  -e POSTGRES_PASSWORD=secure_password \
  -e POSTGRES_USER=voicepact \
  -e POSTGRES_DB=voicepact \
  -p 5432:5432 \
  -v voicepact-data:/var/lib/postgresql/data \
  postgres:15-alpine

Database Setup

Create database and user:

sudo -u postgres psql

-- Create user
CREATE USER voicepact WITH PASSWORD 'your_secure_password';

-- Create database
CREATE DATABASE voicepact OWNER voicepact;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE voicepact TO voicepact;

-- Connect to database
\c voicepact

-- Grant schema privileges
GRANT ALL ON SCHEMA public TO voicepact;

-- Exit
\q

Install Python PostgreSQL driver:

pip install asyncpg

Configure environment:

# In server/.env
DATABASE_URL=postgresql+asyncpg://voicepact:your_secure_password@localhost:5432/voicepact

Connection String Format:

postgresql+asyncpg://[user]:[password]@[host]:[port]/[database]

Initialize database:

cd server
source venv/bin/activate
uvicorn main:app --reload

The application will automatically create all tables on startup.

PostgreSQL Tuning

Edit postgresql.conf for production optimization:

# Memory Settings
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
work_mem = 16MB

# Write Performance
wal_buffers = 16MB
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

# Query Planner
random_page_cost = 1.1  # For SSD
effective_io_concurrency = 200

# Connections
max_connections = 100

Restart PostgreSQL after changes:

sudo systemctl restart postgresql

Database Migrations

VoicePact uses SQLAlchemy’s declarative base for schema management. The database schema is automatically created on application startup.

Manual Migration Process

For production environments where you want control over schema changes:

Install Alembic:

pip install alembic

Initialize Alembic:

cd server
alembic init alembic

Configure Alembic:

Edit alembic/env.py:

from app.core.database import Base
from app.core.config import get_settings
from app.models import *  # Import all models

settings = get_settings()
target_metadata = Base.metadata

config.set_main_option('sqlalchemy.url', settings.database_url)

Generate initial migration:

alembic revision --autogenerate -m "Initial schema"

Apply migration:

alembic upgrade head

Schema Verification

Verify all tables are created: SQLite:

sqlite3 voicepact.db "SELECT name FROM sqlite_master WHERE type='table';"

PostgreSQL:

psql -U voicepact -d voicepact -c "\dt"

Expected tables:

contracts
contract_parties
contract_signatures
payments
audit_logs
voice_recordings
ussd_sessions
sms_logs

Redis Configuration

Redis is required for caching and session management.

Installation

Ubuntu/Debian
macOS (Homebrew)
Docker

sudo apt install redis-server
sudo systemctl start redis-server
sudo systemctl enable redis-server

brew install redis
brew services start redis

docker run -d \
  --name voicepact-redis \
  -p 6379:6379 \
  -v voicepact-redis:/data \
  redis:7-alpine redis-server --appendonly yes

Redis Configuration

Edit redis.conf for production:

# Security
requirepass your_secure_redis_password
bind 127.0.0.1

# Persistence
save 900 1
save 300 10
save 60 10000
appendonly yes
appendfsync everysec

# Memory
maxmemory 256mb
maxmemory-policy allkeys-lru

# Performance
tcp-backlog 511
timeout 0
tcp-keepalive 300

Environment Configuration

# In server/.env
REDIS_URL=redis://:your_secure_redis_password@localhost:6379/0
REDIS_MAX_CONNECTIONS=20
REDIS_SOCKET_TIMEOUT=30

Verify Redis Connection

redis-cli -a your_secure_redis_password ping
# Should return: PONG

Data Models Reference

Contract Model

Key fields from app/models/contract.py:65-140:

class Contract(Base):
    id: str                    # Primary key
    audio_url: str            # Recording URL
    transcript: str           # Transcription
    contract_type: ContractType
    terms: dict               # JSON terms
    contract_hash: str        # Integrity hash
    total_amount: Decimal
    currency: str             # Default: KES
    status: ContractStatus
    created_at: datetime
    expires_at: datetime
    confirmed_at: datetime
    completed_at: datetime

Status Flow:

PENDING → CONFIRMED → ACTIVE → COMPLETED
    ↓         ↓          ↓
CANCELLED  EXPIRED   DISPUTED

Payment Model

Key fields from app/models/contract.py:219-268:

class Payment(Base):
    id: int
    contract_id: str
    transaction_id: str
    payer_phone: str
    recipient_phone: str
    amount: Decimal
    currency: str
    status: PaymentStatus
    payment_method: str
    created_at: datetime
    confirmed_at: datetime
    released_at: datetime

Payment Status Flow:

PENDING → LOCKED → RELEASED
    ↓       ↓         ↓
  FAILED  FAILED  REFUNDED

Audit Log Model

Immutable event trail from app/models/contract.py:271-309:

class AuditLog(Base):
    id: int
    contract_id: str
    action: str              # Event type
    actor_phone: str
    actor_role: str
    old_values: dict         # Before state
    new_values: dict         # After state
    details: str
    created_at: datetime     # Immutable timestamp
    ip_address: str
    user_agent: str

Database Operations

Connection Management

The DatabaseManager class (in app/core/database.py:25-143) handles:

Async database engine initialization
Connection pooling
Redis connection management
Automatic connection testing
Graceful shutdown

Session Management

Use dependency injection for database sessions:

from app.core.database import get_db
from fastapi import Depends
from sqlalchemy.ext.asyncio import AsyncSession

@app.get("/contracts/{contract_id}")
async def get_contract(
    contract_id: str,
    db: AsyncSession = Depends(get_db)
):
    result = await db.execute(
        select(Contract).where(Contract.id == contract_id)
    )
    return result.scalar_one_or_none()

Cache Management

From app/core/database.py:166-240:

from app.core.database import get_cache

cache = await get_cache()

# Store contract in cache
await cache.set_json(
    f"contract:{contract_id}",
    contract_data,
    expire=3600  # 1 hour
)

# Retrieve from cache
cached_contract = await cache.get_json(f"contract:{contract_id}")

Backup and Recovery

SQLite Backup

# Create backup
sqlite3 voicepact.db ".backup 'voicepact_backup_$(date +%Y%m%d).db'"

# Or using cp (safer with WAL mode)
cp voicepact.db voicepact_backup_$(date +%Y%m%d).db
cp voicepact.db-wal voicepact_backup_$(date +%Y%m%d).db-wal
cp voicepact.db-shm voicepact_backup_$(date +%Y%m%d).db-shm

# Restore
cp voicepact_backup_20260306.db voicepact.db

PostgreSQL Backup

# Full database backup
pg_dump -U voicepact -d voicepact -F c -f voicepact_backup_$(date +%Y%m%d).dump

# Backup with compression
pg_dump -U voicepact -d voicepact | gzip > voicepact_backup_$(date +%Y%m%d).sql.gz

# Restore
pg_restore -U voicepact -d voicepact -c voicepact_backup_20260306.dump

# Or from SQL
gunzip -c voicepact_backup_20260306.sql.gz | psql -U voicepact -d voicepact

Automated Backup Script

#!/bin/bash
# backup_voicepact.sh

BACKUP_DIR="/var/backups/voicepact"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30

mkdir -p "$BACKUP_DIR"

# PostgreSQL backup
pg_dump -U voicepact -d voicepact -F c -f "$BACKUP_DIR/voicepact_$DATE.dump"

# Redis backup
redis-cli --rdb "$BACKUP_DIR/redis_$DATE.rdb"

# Remove old backups
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete
find "$BACKUP_DIR" -name "*.rdb" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: $DATE"

Schedule with cron:

crontab -e
# Add: Daily backup at 2 AM
0 2 * * * /path/to/backup_voicepact.sh

Performance Monitoring

Database Health Check

VoicePact includes built-in health checks:

curl http://localhost:8000/health

Response:

{
  "status": "healthy",
  "services": {
    "database": "healthy",
    "redis": "healthy"
  },
  "timestamp": 1234567890.123
}

PostgreSQL Query Monitoring

Identify slow queries:

-- Enable query logging
ALTER DATABASE voicepact SET log_min_duration_statement = 100;

-- View current connections
SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity
WHERE datname = 'voicepact';

-- Find slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Redis Monitoring

# Connection info
redis-cli INFO stats

# Memory usage
redis-cli INFO memory

# Monitor commands in real-time
redis-cli MONITOR

# Check key count
redis-cli DBSIZE

Troubleshooting

Connection Errors

“Unable to open database file”

Check file permissions: chmod 644 voicepact.db
Verify directory is writable: chmod 755 .
Check disk space: df -h

PostgreSQL “could not connect to server”

Verify PostgreSQL is running: sudo systemctl status postgresql
Check pg_hba.conf for authentication rules
Verify connection string in .env
Check firewall: sudo ufw status

Redis “Connection refused”

Verify Redis is running: sudo systemctl status redis
Check bind address in redis.conf
Verify password if configured

Migration Issues

“Table already exists”

# Drop and recreate (CAUTION: destroys data)
from app.core.database import Base, db_manager
import asyncio

async def reset_db():
    async with db_manager.engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)

asyncio.run(reset_db())

Performance Issues

High CPU usage on SQLite:

Consider upgrading to PostgreSQL
Check for missing indexes
Review query patterns
Enable DATABASE_ECHO=true to log queries

PostgreSQL slow queries:

Run VACUUM ANALYZE regularly
Check indexes: \di in psql
Review connection pool settings
Adjust work_mem and shared_buffers

Next Steps

Environment Setup - Configure environment variables
Production Deployment - Deploy to production
API Reference - Explore database-backed endpoints

Deployment

Development

​Overview

​Database Architecture

​Core Tables

​Entity Relationships

​SQLite Configuration (Development)

​Advantages

​Limitations

​Setup SQLite

​SQLite Optimizations

​PostgreSQL Configuration (Production)

​Prerequisites

​Installation

​Database Setup

​PostgreSQL Tuning

​Database Migrations

​Manual Migration Process

​Schema Verification

​Redis Configuration

​Installation

​Redis Configuration

​Environment Configuration

​Verify Redis Connection

​Data Models Reference

​Contract Model

​Payment Model

​Audit Log Model

​Database Operations

​Connection Management

​Session Management

​Cache Management

​Backup and Recovery

​SQLite Backup

​PostgreSQL Backup

​Automated Backup Script

​Performance Monitoring

​Database Health Check

​PostgreSQL Query Monitoring

​Redis Monitoring

​Troubleshooting

​Connection Errors

​Migration Issues

​Performance Issues

​Next Steps

Build docs developers (and LLMs) love

Overview

Database Architecture

Core Tables

Entity Relationships

SQLite Configuration (Development)

Advantages

Limitations

Setup SQLite

SQLite Optimizations

PostgreSQL Configuration (Production)

Prerequisites

Installation

Database Setup

PostgreSQL Tuning

Database Migrations

Manual Migration Process

Schema Verification

Redis Configuration

Installation

Redis Configuration

Environment Configuration

Verify Redis Connection

Data Models Reference

Contract Model

Payment Model

Audit Log Model

Database Operations

Connection Management

Session Management

Cache Management

Backup and Recovery

SQLite Backup

PostgreSQL Backup

Automated Backup Script

Performance Monitoring

Database Health Check

PostgreSQL Query Monitoring

Redis Monitoring

Troubleshooting

Connection Errors

Migration Issues

Performance Issues

Next Steps