Archestra Database Migrations

Use this skill for migration work.

Run commands from platform/ unless specifically instructed otherwise.

Large / hot tables

If the migration touches the interactions table (or any other very large, write-hot table), read archestra-dev-interactions-migrations first. A blocking operation on that table stalls the LLM proxy's write path, and its most common trap — rebuilding an index inside a transactional migration — cannot be made concurrent. That skill covers safe vs risky operations and a read-only audit of the staging table.

Merge/rebase conflicts

When git reports merge/rebase conflicts in Drizzle migration metadata or generated clients, follow resolve-conflicts.md instead of the normal migration flow.

That subpage overrides the default working directory rule and runs its procedure from the repo root where needed.

Common commands

pnpm db:generate
pnpm --dir backend exec drizzle-kit check
pnpm --dir backend check:migrations
pnpm db:migrate
pnpm db:studio

Ask before applying migrations locally if the command will modify database state. Generating migrations and checking consistency are safe.

Schema migrations

When creating migrations that include schema changes and data migration logic:

1. Update the Drizzle schema files with the schema changes.
2. Run pnpm db:generate.
3. Add the data migration SQL to the generated file.
4. Run pnpm --dir backend exec drizzle-kit check and pnpm --dir backend check:migrations to verify consistency and journal ordering.

Drizzle creates a migration with a generated name such as 0119_military_alice.sql. Use that generated filename. Never create manually named migration files because Drizzle tracks migrations through backend/src/database/migrations/meta/_journal.json, which references generated file names.

Data migration SQL

Data migration SQL can include statements such as INSERT, UPDATE, and other data-changing statements inside the generated migration file.

Keep schema DDL before data migration statements when the data migration references newly created tables or columns.

Always verify with pnpm --dir backend exec drizzle-kit check and pnpm --dir backend check:migrations after editing generated SQL.

Custom data-only migrations

For pure data migrations with no schema changes, create an empty custom migration tracked by Drizzle:

pnpm --dir backend exec drizzle-kit generate --custom --name=<descriptive-name>

Then add the SQL to the generated file and run:

pnpm --dir backend exec drizzle-kit check

Database connection

PostgreSQL runs in Kubernetes when managed by Tilt.

kubectl exec -n archestra-dev postgresql-0 -- env PGPASSWORD=archestra_dev_password psql -U archestra -d archestra_dev

Useful read-only inspection commands inside psql include \dt, \d table_name, and SELECT COUNT(*) FROM drizzle.__drizzle_migrations;.