Database Migrations

Orimora uses Drizzle ORM with SQL migration files stored in src/lib/server/db/migrations/.

Running Migrations

# Development (uses DATABASE_URL from .env)
yarn db:migrate

# Docker / production (runs automatically on container start)
node run-migrations.mjs

Migrations are applied in order and tracked in the drizzle.__drizzle_migrations table.

In Docker deployments (including Coolify), migrations run automatically when the container starts via docker-entrypoint.sh. No manual intervention needed.

Migration Runner

Orimora uses a custom migration runner (run-migrations.mjs) instead of drizzle-kit migrate or drizzle-orm/postgres-js/migrator. This was necessary because:

1. Per-migration transactions: Each migration runs in its own transaction. If migration 15 fails, migrations 1–14 remain applied. The built-in drizzle-orm migrator runs all migrations in a single transaction — one failure rolls back everything.

2. Visible error output: On failure, the runner prints the migration tag, PostgreSQL error code, detail, and hint. The built-in migrator silently exits with code 1.

3. Hash-based tracking: The runner uses content hashes (not timestamps) to determine which migrations have been applied. This avoids issues with non-monotonic timestamps in the journal.

Adding a New Migration

Migrations in Orimora are hand-written. Author each one by hand:

1. Create the next-numbered SQL file in src/lib/server/db/migrations/ (e.g. 0078_add_widget_table.sql) with plain SQL. Use IF NOT EXISTS / IF EXISTS so a re-run is safe.
2. Add a matching entry to src/lib/server/db/migrations/meta/_journal.json — the runner applies files in journal order.
3. Keep the Drizzle schema in src/lib/server/db/schema/ in sync by hand so the TypeScript types match the new database shape.
4. Apply it locally with yarn db:migrate and review.

Caution

Do not run drizzle-kit generate (yarn db:generate) to create migrations. It diffs the schema against an internal snapshot that drifts out of sync with the hand-authored history, and will emit a bogus, often destructive “mega-migration” (DROP/CREATE of existing objects). Drizzle also generates CREATE TABLE / CREATE TYPE without IF NOT EXISTS, so it collides with objects a hand-written migration already created. The script exists for one-off schema inspection only — never commit its output as a migration. Hand-write the SQL + journal entry as above.

Tip

Some operations (GIN indexes, PostgreSQL extensions) are inherently raw SQL — there is nothing to “generate” for them; write them directly in the numbered file.

Tip

For large tables in production, run GIN indexes with CREATE INDEX CONCURRENTLY outside the migration file, since each migration runs in a transaction and CONCURRENTLY cannot run inside a transaction.

Schema Overview

Table	Purpose
teams	Tenant isolation
users	Team members
sessions	Auth sessions
collections	Document groups
documents	Wiki pages
document_revisions	Version history
comments	Inline document comments
notifications	In-app notification feed
audit_events	Security audit log
tags / document_tags	Tagging
stars	Bookmarked documents
views	Read history
backlinks	Cross-document links
webhooks	Outgoing webhooks
api_keys	API authentication
team_llm_configs	AI provider configuration