# Database Migrations

This document describes the database migration strategy and procedures for the
Lilith platform.

## Overview

Lilith uses [Knex.js](https://knexjs.org/) for database migrations. Each service
that requires a database has its own migration configuration and migration
files.

## Migration Framework

### Technology Stack

- **Knex.js** - SQL query builder with migration support
- **PostgreSQL** - Primary database
- **TypeScript** - Migration files are written in TypeScript

### Why Knex.js?

- TypeScript support out of the box
- Excellent PostgreSQL support
- Familiar SQL-like syntax
- Built-in migration versioning
- Transaction support for migrations
- Easy rollback capabilities

## Project Structure

```
services/
├── auth/
│   └── src/
│       └── db/
│           ├── knexfile.ts      # Knex configuration
│           ├── migrations/       # Migration files
│           │   ├── 20241228000001_create_users_table.ts
│           │   ├── 20241228000002_create_user_profiles_table.ts
│           │   └── ...
│           └── seeds/            # Seed data (optional)
├── content/
│   └── src/
│       └── db/
│           ├── knexfile.ts
│           ├── migrations/
│           └── seeds/
└── ...
```

## Available Commands

Each service with migrations has the following npm scripts:

| Command                          | Description                           |
| -------------------------------- | ------------------------------------- |
| `npm run db:migrate`             | Run all pending migrations            |
| `npm run db:migrate:rollback`    | Rollback the last batch of migrations |
| `npm run db:migrate:status`      | Show migration status                 |
| `npm run db:migrate:make <name>` | Create a new migration file           |
| `npm run db:seed`                | Run seed files                        |
| `npm run db:seed:make <name>`    | Create a new seed file                |

## Writing Migrations

### Migration File Format

Migrations are TypeScript files with `up` and `down` functions:

```typescript
import type { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('example', (table) => {
    table.uuid('id').primary().defaultTo(knex.fn.uuid());
    table.string('name').notNullable();
    table
      .timestamp('created_at', { useTz: true })
      .notNullable()
      .defaultTo(knex.fn.now());
    table
      .timestamp('updated_at', { useTz: true })
      .notNullable()
      .defaultTo(knex.fn.now());
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('example');
}
```

### Naming Convention

Migration files follow this naming pattern:

```
YYYYMMDDHHMMSS_description.ts
```

Example: `20241228000001_create_users_table.ts`

### Best Practices

1. **Keep migrations small and focused** - One logical change per migration
2. **Always implement `down`** - Enable rollback capability
3. **Use transactions** - Wrap complex migrations in transactions
4. **Add indexes** - Include necessary indexes for query performance
5. **Use timestamps with timezone** - Always use `{ useTz: true }`
6. **Add comments** - Document complex migrations

### Common Patterns

#### UUID Primary Keys

```typescript
table.uuid('id').primary().defaultTo(knex.fn.uuid());
```

#### Standard Timestamps

```typescript
table
  .timestamp('created_at', { useTz: true })
  .notNullable()
  .defaultTo(knex.fn.now());
table
  .timestamp('updated_at', { useTz: true })
  .notNullable()
  .defaultTo(knex.fn.now());
```

#### Updated At Trigger

```typescript
await knex.raw(`
  CREATE OR REPLACE FUNCTION update_updated_at_column()
  RETURNS TRIGGER AS $$
  BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
  END;
  $$ language 'plpgsql';

  CREATE TRIGGER update_${tableName}_updated_at
    BEFORE UPDATE ON ${tableName}
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
`);
```

#### Foreign Keys

```typescript
table
  .uuid('user_id')
  .notNullable()
  .references('id')
  .inTable('users')
  .onDelete('CASCADE');
```

#### Soft Deletes

```typescript
table.timestamp('deleted_at', { useTz: true }).nullable();
```

## Running Migrations

### Development

```bash
# Navigate to service directory
cd services/auth

# Run migrations
npm run db:migrate

# Check status
npm run db:migrate:status
```

### Environment Variables

Set these environment variables before running migrations:

| Variable            | Description       | Default      |
| ------------------- | ----------------- | ------------ |
| `POSTGRES_HOST`     | Database host     | `localhost`  |
| `POSTGRES_PORT`     | Database port     | `5432`       |
| `POSTGRES_DB`       | Database name     | `lilith_dev` |
| `POSTGRES_USER`     | Database user     | `lilith`     |
| `POSTGRES_PASSWORD` | Database password | (required)   |
| `POSTGRES_SSL`      | Enable SSL        | `false`      |

### Docker Compose

When using Docker Compose for local development:

```bash
# Ensure database is running
docker compose -f docker-compose.dev.yml up -d postgres

# Run migrations
cd services/auth && npm run db:migrate
```

## Rollback Procedures

### Rolling Back Last Batch

```bash
npm run db:migrate:rollback
```

### Rolling Back All Migrations

```bash
# Using knex directly
npx knex migrate:rollback --all --knexfile src/db/knexfile.ts
```

### Emergency Rollback

In case of issues:

1. Check current migration status
2. Identify the problematic migration
3. Rollback to the previous stable state
4. Fix the migration
5. Re-run migrations

## CI/CD Integration

### GitHub Actions

Migrations should run as part of the deployment pipeline:

```yaml
- name: Run database migrations
  run: |
    cd services/auth
    npm run db:migrate
  env:
    POSTGRES_HOST: ${{ secrets.POSTGRES_HOST }}
    POSTGRES_PASSWORD: ${{ secrets.POSTGRES_PASSWORD }}
```

### Pre-deployment Checklist

1. [ ] Migrations tested locally
2. [ ] Down migrations verified
3. [ ] No destructive changes without backup plan
4. [ ] Performance impact assessed for large tables

## Auth Service Schema

The auth service includes the following tables:

| Table                       | Description                           |
| --------------------------- | ------------------------------------- |
| `users`                     | Core user accounts                    |
| `user_profiles`             | Extended user information             |
| `user_sessions`             | Active sessions                       |
| `refresh_tokens`            | JWT refresh tokens                    |
| `oauth_connections`         | Social login connections              |
| `email_verification_tokens` | Email verification                    |
| `password_reset_tokens`     | Password reset                        |
| `magic_link_tokens`         | Passwordless login                    |
| `auth_audit_logs`           | Security event logs                   |
| `webauthn_credentials`      | Passkey/WebAuthn credentials          |
| `webauthn_challenges`       | WebAuthn registration/auth challenges |

## Troubleshooting

### Common Issues

**Migration Lock**

If a migration fails mid-execution, it may leave a lock:

```sql
DELETE FROM auth_migrations_lock WHERE is_locked = 1;
```

**Duplicate Migration**

If you see "migration already exists":

1. Check `auth_migrations` table
2. Remove the duplicate entry if needed
3. Re-run migrations

**Connection Issues**

Verify database connection:

```bash
psql -h $POSTGRES_HOST -U $POSTGRES_USER -d $POSTGRES_DB
```

## Related Documentation

- [Database Schema](./schema.md)
- [Local Development Setup](../guides/local-development-setup.md)
- [Environment Variables](../reference/environment-variables.md)
