# Database Migrations

This directory contains SQL migration files for the Feuerwehr Dashboard database schema.

## Overview

Migrations are automatically executed when the application starts. Each migration file is tracked in the `migrations` table to ensure it only runs once.

## Migration Files

Migration files follow the naming convention: `{number}_{description}.sql`

Example:
- `001_create_users_table.sql`
- `002_add_roles_table.sql`

The numeric prefix determines the execution order. Always use sequential numbering.

## How Migrations Work

### Automatic Execution (Docker)

When running the application with Docker, migrations are automatically executed during startup:

1. Application starts
2. Database connection is established
3. `runMigrations()` function is called
4. Migration tracking table (`migrations`) is created if it doesn't exist
5. Each `.sql` file in this directory is checked:
   - If already executed (recorded in `migrations` table): **skipped**
   - If new: **executed within a transaction**
6. Successfully executed migrations are recorded in the tracking table

### Migration Tracking

The system creates a `migrations` table to track which migrations have been executed:

```sql
CREATE TABLE migrations (
    id SERIAL PRIMARY KEY,
    filename VARCHAR(255) UNIQUE NOT NULL,
    executed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
```

This ensures each migration runs exactly once, even across multiple deployments.

## Manual Migration Execution

If you need to run migrations manually (development or troubleshooting):

### Using psql (PostgreSQL CLI)

```bash
# Connect to the database
docker exec -it feuerwehr-postgres psql -U feuerwehr_user -d feuerwehr_db

# Run a specific migration
\i /path/to/migration/001_create_users_table.sql

# Or if inside the container
\i /docker-entrypoint-initdb.d/migrations/001_create_users_table.sql
```

### Using npm/node script

Create a migration runner script:

```bash
# From backend directory
npm run migrate
```

You can add this to `package.json`:

```json
{
  "scripts": {
    "migrate": "ts-node src/scripts/migrate.ts"
  }
}
```

## Creating New Migrations

1. **Determine the next migration number**
   - Check existing files in this directory
   - Use the next sequential number (e.g., if `001_` exists, create `002_`)

2. **Create a new `.sql` file**
   ```bash
   touch src/database/migrations/002_add_new_feature.sql
   ```

3. **Write your SQL schema changes**
   ```sql
   -- Always include IF EXISTS checks for safety
   CREATE TABLE IF NOT EXISTS my_table (
       id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
       name VARCHAR(255) NOT NULL,
       created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
   );

   -- Add indexes
   CREATE INDEX IF NOT EXISTS idx_my_table_name ON my_table(name);
   ```

4. **Restart the application**
   - Docker will automatically detect and run the new migration
   - Or call `runMigrations()` programmatically

## Best Practices

### 1. Make Migrations Idempotent

Always use `IF EXISTS` or `IF NOT EXISTS` clauses:

```sql
CREATE TABLE IF NOT EXISTS users (...);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
ALTER TABLE users ADD COLUMN IF NOT EXISTS new_column VARCHAR(255);
```

### 2. Use Transactions

Each migration runs within a transaction automatically. If any part fails, the entire migration is rolled back.

### 3. Never Modify Existing Migrations

Once a migration has been deployed to production:
- **Never modify it**
- Create a new migration for changes
- This ensures consistency across all environments

### 4. Test Migrations Locally

Before deploying:
```bash
# Start fresh database
docker-compose down -v
docker-compose up -d postgres

# Run migrations
npm run dev
# or
npm run migrate
```

### 5. Include Rollback Instructions

Document how to revert changes in comments:

```sql
-- Migration: Add user preferences column
-- Rollback: ALTER TABLE users DROP COLUMN preferences;

ALTER TABLE users ADD COLUMN preferences JSONB DEFAULT '{}';
```

## Troubleshooting

### Migration Failed

If a migration fails:

1. **Check the logs** for error details
2. **Fix the SQL** in the migration file
3. **Remove the failed entry** from the tracking table:
   ```sql
   DELETE FROM migrations WHERE filename = '002_failed_migration.sql';
   ```
4. **Restart the application** or re-run migrations

### Reset All Migrations

For development only (destroys all data):

```bash
# Drop and recreate database
docker-compose down -v
docker-compose up -d postgres

# Migrations will run automatically on next start
docker-compose up backend
```

### Check Migration Status

```sql
-- See which migrations have been executed
SELECT * FROM migrations ORDER BY executed_at DESC;

-- Check if a specific table exists
SELECT EXISTS (
    SELECT FROM information_schema.tables
    WHERE table_schema = 'public'
    AND table_name = 'users'
);
```

## Current Migrations

### 001_create_users_table.sql

Creates the main `users` table for storing authenticated user data:

- **UUID primary key** with automatic generation
- **Authentik integration** fields (sub, email, profile data)
- **Token management** (refresh_token, expiration)
- **Audit fields** (last_login, created_at, updated_at)
- **User preferences** as JSONB
- **Active status** flag
- **Indexes** for performance:
  - `authentik_sub` (unique identifier from OIDC)
  - `email` (for lookups)
  - `last_login_at` (for activity tracking)
- **Automatic timestamp updates** via trigger

## References

- [PostgreSQL Documentation](https://www.postgresql.org/docs/)
- [pg (node-postgres) Library](https://node-postgres.com/)
- Application database config: `src/config/database.ts`
