willhaben-tracker/docs/phase-0/task-auto-migration.md

# Task: Auto-migration on container startup

## Description

The worker currently fails to start when the database schema is missing or outdated (as happened with the `UndefinedTableError` incident). There is no automatic migration mechanism — migrations must be applied manually via `docker cp` + Python scripts.

This task introduces an **automatic, idempotent migration runner** that executes on every container start *before* the worker process begins. It tracks which migrations have been applied in a `migrations` table and applies only the missing ones.

## Architecture

```
Container lifecycle:

  ┌──────────────┐     ┌──────────────────┐     ┌──────────────┐
  │ entrypoint.sh│────►│ python migrate.py│────►│ python       │
  │              │     │                  │     │ main.py      │
  │ - copy SQLs  │     │ 1. Create        │     │ (normal app) │
  │ - call Python│     │   migrations tbl │     │              │
  └──────────────┘     │ 2. Apply pending │     └──────────────┘
                       │ 3. Seed data     │
                       └──────────────────┘

migrations table:
  CREATE TABLE IF NOT EXISTS willhaben_migrations (
      name    text PRIMARY KEY,   -- e.g. "01-schema.sql"
      applied_at timestamptz DEFAULT now()
  );

Migration directory layout:
  worker/src/migrations/
    └── 01-schema.sql   (the current supabase-migration.sql content)
```

### Key design decisions

- **File-based migrations** — each `.sql` file in `worker/src/migrations/` is a migration. Name = version key.
- **No rollback support** — forward-only, consistent with the simplicity of the project. Rollback means restore from DB backup.
- **Run on every start** — idempotent by checking `willhaben_migrations`. Fast (no-op if all applied).
- **Seed data** included in a dedicated `zz-seed.sql` migration that runs last.

## Implementation Details

### 1. Create `worker/src/migrate.py`

```python
#!/usr/bin/env python3
"""Run pending SQL migrations against the configured PostgreSQL database."""

import os
import glob
import logging
import asyncpg

logger = logging.getLogger(__name__)

MIGRATIONS_DIR = os.path.join(os.path.dirname(__file__), "migrations")

async def run_migrations():
    dsn_parts = {
        "host": os.getenv("POSTGRES_HOST", "db"),
        "port": int(os.getenv("POSTGRES_PORT", "5432")),
        "user": os.getenv("POSTGRES_USER", "postgres"),
        "password": os.getenv("POSTGRES_PASSWORD"),
        "database": os.getenv("POSTGRES_DB", "postgres"),
    }

    conn = await asyncpg.connect(**dsn_parts)
    
    try:
        # Create tracking table (always in public schema for simplicity)
        await conn.execute("""
            CREATE TABLE IF NOT EXISTS willhaben_migrations (
                name text PRIMARY KEY,
                applied_at timestamptz DEFAULT now()
            );
        """)

        # Find and sort migration files
        sql_files = sorted(glob.glob(os.path.join(MIGRATIONS_DIR, "*.sql")))

        for fpath in sql_files:
            name = os.path.basename(fpath)
            
            # Check if already applied
            exists = await conn.fetchval(
                "SELECT 1 FROM willhaben_migrations WHERE name = $1", name
            )
            
            if exists:
                logger.info("Skipping already-applied migration: %s", name)
                continue

            logger.info("Applying migration: %s", name)
            
            with open(fpath) as f:
                sql = f.read()
            
            # Execute within a transaction (DDL auto-commits in some cases)
            await conn.execute(sql)
            
            # Record as applied
            await conn.execute(
                "INSERT INTO willhaben_migrations (name) VALUES ($1)", name
            )
            logger.info("Applied migration: %s", name)

    finally:
        await conn.close()

if __name__ == "__main__":
    logging.basicConfig(level=logging.INFO, format="%(asctime)s %(message)s")
    import asyncio
    asyncio.run(run_migrations())
```

### 2. Copy SQL migrations into `worker/src/migrations/`

- Copy the content of `supabase/migrations/supabase-migration.sql` to `worker/src/migrations/01-schema.sql`
- **Remove** the `SET search_path TO willhaben_tracker;` line (Phase 0 task: search_path fix)
- Create `worker/src/migrations/zz-seed.sql` with just the admin user seed

### 3. Update `worker/Dockerfile` to copy migration files

```dockerfile
FROM python:3.12-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY src/ .
ENTRYPOINT ["/app/entrypoint.sh"]
CMD ["python", "main.py"]
```

### 4. Create `worker/src/entrypoint.sh`

```bash
#!/bin/bash
set -e

echo "[entrypoint] Running migrations..."
python /app/migrate.py
echo "[entrypoint] Migrations complete."

# Execute the CMD (main.py)
exec "$@"
```

Make executable: `chmod +x worker/src/entrypoint.sh`

### 5. Update `.env.example` documentation to reflect auto-migration

Remove manual migration instructions; add note that migrations run automatically on container start.

## Acceptance Criteria

- [ ] Starting a fresh container against an empty Supabase database creates all required tables and seeds the admin user — zero manual steps
- [ ] Restarting the same container does NOT re-apply already-applied migrations (logged as "Skipping")
- [ ] Adding a new `02-something.sql` to `worker/src/migrations/` and restarting the container applies only that file
- [ ] The `willhaben_migrations` table accurately tracks all applied migrations with timestamps
- [ ] Migration failures log an error and exit with non-zero code (container restarts via Docker policy)
- [ ] No changes to the existing `main.py` scheduler/bot logic are required