# 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:**
```python
# Adding a new field to PetPicture model
class PetPicture:
    # ... existing fields ...
    tags = models.TextField()  # ❌ Requires migration!
```

```python
# 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:**
   ```bash
   cd migrations/
   ```

2. **Create new migration file:**
   ```bash
   # Use next sequential number (002, 003, etc.)
   touch XXX_descriptive_name.py
   ```

3. **Use the migration template:**
   ```python
   #!/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:**
   ```bash
   # Test individual migration
   python migrations/XXX_descriptive_name.py
   
   # Or run all pending migrations
   python migrations/migrate.py
   ```

5. **Commit the migration file:**
   ```bash
   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:**
```python
# 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:**
```bash
# Apply migrations before starting app
python migrations/migrate.py && python main.py
```

**Docker:**
```bash
# Migrations run automatically via docker-compose
docker compose --profile migrate up migrate
```

**Production Deployment:**
```bash
# 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.