migrations/README.md

Database Migrations

This directory contains database migration scripts for the chat agent application.

Overview

The chat agent uses PostgreSQL as the primary database with the following tables:

• chat_sessions: Stores user chat sessions with language context and activity tracking
• messages: Stores individual chat messages with role, content, and language information
• language_contexts: Stores session-specific language settings and prompt templates
• supported_languages: Reference table for supported programming languages

Migration Files

• 001_initial_schema.sql: Initial database schema with all core tables
• migrate.py: Python migration runner script

Usage

Using the Python Migration Script

# Run all pending migrations
python migrations/migrate.py migrate

# Check migration status
python migrations/migrate.py status

# Run migrations for specific environment
python migrations/migrate.py migrate --config production

# Run migrations with custom database URL
python migrations/migrate.py migrate --database-url postgresql://user:pass@localhost/mydb

Using the Database Management CLI

# Initialize database (create tables)
python manage_db.py init

# Reset database (drop and recreate all tables)
python manage_db.py reset

# Get database information
python manage_db.py info

# Get database statistics
python manage_db.py stats

# Create sample data for testing
python manage_db.py sample

# Clean up old sessions
python manage_db.py cleanup

# Check database connection
python manage_db.py check

Manual SQL Execution

You can also run the SQL migration files directly:

# Connect to PostgreSQL and run the migration
psql -d your_database -f migrations/001_initial_schema.sql

Database Schema

chat_sessions

• id: UUID primary key
• user_id: UUID of the session owner
• language: Current programming language (default: python)
• last_active: Timestamp of last activity
• message_count: Total messages in session
• is_active: Whether session is active
• metadata: Additional session data (JSON)
• created_at, updated_at: Timestamps

messages

• id: UUID primary key
• session_id: Reference to chat_sessions
• role: 'user' or 'assistant'
• content: Message text content
• language: Programming language context
• timestamp: When message was created
• metadata: Additional message data (JSON)
• created_at, updated_at: Timestamps

language_contexts

• id: UUID primary key
• session_id: Reference to chat_sessions (unique)
• language: Programming language code
• prompt_template: Custom prompt for the language
• syntax_highlighting: Syntax highlighting scheme
• updated_at: When context was last modified
• created_at: Creation timestamp

supported_languages

• code: Language code (primary key)
• name: Display name
• syntax_highlighting: Highlighting scheme
• file_extensions: Array of file extensions
• prompt_template: Default prompt template
• created_at: Creation timestamp

Environment Variables

Make sure these environment variables are set:

DATABASE_URL=postgresql://username:password@localhost:5432/chat_agent_db
REDIS_URL=redis://localhost:6379/0

Development Setup

1. Install PostgreSQL and Redis
2. Create a database: createdb chat_agent_db
3. Set environment variables in .env file
4. Run migrations: python manage_db.py init
5. Optionally create sample data: python manage_db.py sample

Production Deployment

1. Set up PostgreSQL database
2. Configure environment variables
3. Run migrations: python migrations/migrate.py migrate --config production
4. Verify setup: python manage_db.py check --config production