Database Migrations

import { Aside } from ‘@astrojs/starlight/components’;

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.

Generating a New Migration

After changing a schema file in src/lib/server/db/schema/:

yarn db:generate

This produces a new SQL file in src/lib/server/db/migrations/. Review it before committing.

Manual Migrations

Some operations (e.g. adding GIN indexes, PostgreSQL extensions) cannot be generated automatically and are written as raw SQL files.

**Never create hand-written migrations for tables/types that drizzle-kit will also generate.** Drizzle generates `CREATE TABLE` / `CREATE TYPE` without `IF NOT EXISTS`. If a hand-written migration already created the same object, the generated migration will fail. If you need hand-written migrations for extensions or indexes, keep them separate from drizzle-managed objects.

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