petpicturequeue/CLAUDE.md

Claude Development Guidelines

Database Migrations

⚠️ IMPORTANT: When making changes to database models or schema, you MUST create migration files.

When to Create Migrations

ALWAYS create a migration when:

• Adding new columns to existing tables
• Creating new tables
• Modifying column types or constraints
• Adding or removing indexes
• Changing default values
• Adding foreign keys or relationships

Examples that require migrations:

# Adding a new field to PetPicture model
class PetPicture:
    # ... existing fields ...
    tags = models.TextField()  # ❌ Requires migration!

# Changing database schema in init_db()
def init_db():
    db.execute("""
        CREATE TABLE users (  -- ❌ New table requires migration!
            id INTEGER PRIMARY KEY,
            username TEXT NOT NULL
        )
    """)

How to Create Migrations

1. Navigate to migrations directory:

   cd migrations/
   

2. Create new migration file:

   # Use next sequential number (002, 003, etc.)
   touch XXX_descriptive_name.py
   

3. Use the migration template:

   #!/usr/bin/env python3
   """
   Migration XXX: Brief description of changes
   Date: YYYY-MM-DD
   Description: Detailed explanation of what this migration does
   """
   
   import sqlite3
   import sys
   from datetime import datetime
   
   def check_column_exists(cursor, table_name, column_name):
       """Check if a column exists in a table"""
       cursor.execute(f"PRAGMA table_info({table_name})")
       columns = [column[1] for column in cursor.fetchall()]
       return column_name in columns
   
   def migrate_up(cursor):
       """Apply the migration"""
       # Check if already applied
       if check_column_exists(cursor, 'pet_pictures', 'new_column'):
           print("Migration already applied, skipping...")
           return False
   
       # Apply the migration
       print("Adding new_column to pet_pictures table...")
       cursor.execute("ALTER TABLE pet_pictures ADD COLUMN new_column TEXT")
       return True
   
   def migrate_down(cursor):
       """Rollback the migration (optional)"""
       print("Rollback not implemented for SQLite column drops")
       return False
   
   def main():
       """Run migration standalone"""
       # Standard migration execution code
       pass
   
   if __name__ == "__main__":
       sys.exit(main())
   

4. Test the migration:

   # Test individual migration
   python migrations/XXX_descriptive_name.py
   
   # Or run all pending migrations
   python migrations/migrate.py
   

5. Commit the migration file:

   git add migrations/XXX_descriptive_name.py
   git commit -m "feat: add migration for [description]"
   

Migration Naming Convention

• Sequential numbering: 001_, 002_, 003_, etc.
• Descriptive names: Explain what the migration does
• Examples:
  • 002_add_user_table.py
  • 003_add_tags_column_to_pictures.py
  • 004_create_comments_table.py

DO NOT Modify Database Schema Without Migrations

❌ NEVER do this:

# Directly modifying init_db() or model schema
def init_db():
    db.execute("""
        CREATE TABLE pet_pictures (
            id INTEGER PRIMARY KEY,
            filename TEXT NOT NULL,
            new_field TEXT  -- ❌ Added without migration!
        )
    """)

✅ ALWAYS do this:

1. Create migration file first
2. Run migration to update schema
3. Then update model code if needed

Model Changes Workflow

1. Identify needed schema change
2. Create migration file
3. Test migration locally
4. Update model/application code (if needed)
5. Test full application
6. Commit migration + code changes together

Migration Best Practices

• One logical change per migration
• Make migrations idempotent (safe to run multiple times)
• Test on development data first
• Include rollback logic when possible
• Document breaking changes clearly
• Never edit applied migrations

Integration Points

Development:

# Apply migrations before starting app
python migrations/migrate.py && python main.py

Docker:

# Migrations run automatically via docker-compose
docker compose --profile migrate up migrate

Production Deployment:

# Run migrations as part of deployment
python migrations/migrate.py
systemctl restart pet-picture-queue

---

Database Schema Reference

Current Schema (after migrations):

pet_pictures table:

• id - INTEGER PRIMARY KEY AUTOINCREMENT
• filename - TEXT NOT NULL
• subscriber_name - TEXT NOT NULL
• description - TEXT NULL (added via migration 001)
• uploaded_at - TIMESTAMP NOT NULL
• posted - BOOLEAN DEFAULT 0
• likes - INTEGER DEFAULT 0

migrations table (auto-created):

• id - INTEGER PRIMARY KEY AUTOINCREMENT
• migration_name - TEXT NOT NULL UNIQUE
• applied_at - TIMESTAMP NOT NULL

---

Remember: Schema changes without migrations will cause deployment issues and data inconsistencies!

Always follow the migration workflow for database changes.