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

```bash
# 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

```bash
# 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:

```bash
# 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:

```bash
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`