This document describes the database migration strategy and procedures for the Lilith platform.
Overview#
Lilith uses Knex.js 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:
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#
- Keep migrations small and focused - One logical change per migration
- Always implement
down- Enable rollback capability - Use transactions - Wrap complex migrations in transactions
- Add indexes - Include necessary indexes for query performance
- Use timestamps with timezone - Always use
{ useTz: true } - Add comments - Document complex migrations
Common Patterns#
UUID Primary Keys#
table.uuid('id').primary().defaultTo(knex.fn.uuid());
Standard Timestamps#
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#
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#
table
.uuid('user_id')
.notNullable()
.references('id')
.inTable('users')
.onDelete('CASCADE');
Soft Deletes#
table.timestamp('deleted_at', { useTz: true }).nullable();
Running Migrations#
Development#
# 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:
# 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#
npm run db:migrate:rollback
Rolling Back All Migrations#
# Using knex directly
npx knex migrate:rollback --all --knexfile src/db/knexfile.ts
Emergency Rollback#
In case of issues:
- Check current migration status
- Identify the problematic migration
- Rollback to the previous stable state
- Fix the migration
- Re-run migrations
CI/CD Integration#
GitHub Actions#
Migrations should run as part of the deployment pipeline:
- 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#
- Migrations tested locally
- Down migrations verified
- No destructive changes without backup plan
- 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:
DELETE FROM auth_migrations_lock WHERE is_locked = 1;
Duplicate Migration
If you see "migration already exists":
- Check
auth_migrationstable - Remove the duplicate entry if needed
- Re-run migrations
Connection Issues
Verify database connection:
psql -h $POSTGRES_HOST -U $POSTGRES_USER -d $POSTGRES_DB