{
    "name": "n8n-db-migrations",
    "version": "1.0.0",
    "description": "Authors n8n database migrations. Use when creating or modifying files under packages/@n8n/db/src/migrations/, when the user asks to add a column, table, index, foreign key, or backfill, or when the user mentions DB migrations or TypeORM migrations.",
    "system_prompt": "name n8n:db-migrations description Authors n8n database migrations. Use when creating or modifying files under packages/@n8n/db/src/migrations/, when the user asks to add a column, table, index, foreign key, or backfill, or when the user mentions DB migrations or TypeORM migrations. n8n Migration Guidelines Rule of thumb: the @n8n-io/migrations-review team gates every migration PR. The fixes they ask for are predictable — work through the Pre-flight checklist before requesting review. The rest of this document explains the why for each item and covers deeper topics. Table of Contents Overview Pre-flight checklist Common Guidance Schema Migrations Data Migrations Cross-database Compatibility Tests General Design Guidance Schema documentation Overview Directory Structure packages/@n8n/db/src/migrations/ ├── common/ # Default — DSL handles SQLite + Postgres ├── postgresdb/ # PostgreSQL-specific migrations ├── sqlite/ # SQLite-specific migrations ├── dsl/ # Schema builder DSL (table, column, indices) ├── __tests__/ # Migration tests ├── migration-types.ts └── migration-helpers.ts Migration Types Interface When to use ReversibleMigration Schema changes that can be cleanly undone (add/drop column, create/drop table). Requires a working down() . IrreversibleMigration Data transformations, destructive changes, or anything where down() would lose data. No down() allowed. MigrationContext API Source of truth: packages/@n8n/db/src/migrations/migration-types.ts . Check the source for exact signatures when in doubt. interface MigrationContext { // Database info dbType : 'postgresdb' | 'sqlite' ; isSqlite : boolean ; isPostgres : boolean ; tablePrefix : string ; dbName : string ; // Schema DSL schemaBuilder : { createTable, dropTable, addColumns, dropColumns, column, createIndex, dropIndex, addForeignKey, dropForeignKey, addNotNull, dropNotNull }; // Query execution runQuery<T>( sql : string , namedParameters ?: object ): Promise <T>; runInBatches<T>( query : string , operation : ( rows : T[] ) => Promise < void >, limit ?: number ): Promise < void >; copyTable ( from : string , to : string , fromFields ?: string [], toFields ?: string [], batchSize ?: number ): Promise < void >; // Utilities escape : { tableName ( n : string ): string ; columnName ( n : string ): string ; indexName ( n : string ): string }; parseJson<T>( data : string | T): T; loadSurveyFromDisk (): string | null ; logger : Logger ; migrationName : string ; queryRunner : QueryRunner ; // Avoid direct use — prefer runQuery() } DSL Type Mapping Reference Source of truth: packages/@n8n/db/src/migrations/dsl/column.ts . DSL type PostgreSQL SQLite int int integer bigint bigint integer smallint smallint integer varchar(N) varchar(N) varchar(N) (length not enforced) text text text json json text uuid uuid varchar bool boolean boolean double double precision real binary bytea blob timestampTimezone timestamptz datetime timestampNoTimezone timestamp datetime timestamp (deprecated) timestamp datetime Default precision for the timestamp variants is 3 ms; override with .timestampTimezone(6) . Pre-flight checklist Run through this before requesting review. Each item is a real, recurring reviewer flag; the link points to the section that explains the rule. Migration was scaffolded with pnpm --filter=@n8n/db migration:new (timestamp + registration are automatic; the migration-timestamp lint rule catches drift). — Creating Migrations Identifiers go through escape.tableName(...) / escape.columnName(...) . Never hand-write n8n_table prefixes. — Always escape identifiers Match column type to value semantics. Native uuid for UUIDs, timestampTimezone() for timestamps, a numeric type for numbers, bool for booleans, json for structured data. Never varchar as a catch-all. — Column types Pick the narrowest sane type within that category: int / smallint not bigint when range allows; text not varchar(255) for unbounded strings; never double for version numbers. — Column types Default notNull , relax only when justified. PK is implicitly NOT NULL. Migration's notNull matches the entity's nullability. — NOT NULL and entity parity Enum-like columns carry .withEnumCheck([...]) AND .comment('explains values') . Opaque IDs / unix timestamps / JSON shapes also get .comment() . — Constrain enum-like strings , Add comments on columns Every reference column has an explicit FK with deliberate onDelete . Name FKs explicitly when SQLite recreate cycles risk duplicating them. Avoid polymorphic (typeCol, idCol) patterns. — Foreign Key Constraints , General Design Guidance Indexes match real query patterns. A unique constraint already creates an index; a composite PK indexes its prefix. Mirror withIndexOn(...) to entity @Index(...) . — Index Management Sparse-unique columns: use a partial index WHERE col IS NOT NULL . — Index Management Composite index column order matches your actual WHERE / ORDER BY usage. — Index Management Entity ↔ migration parity : column types, notNull , defaults, FKs, @Index decorators all match. — Schema/Entity Drift If using addColumns , dropColumns , addNotNull , dropNotNull , addEnumCheck , or dropEnumCheck : verified whether the target table has incoming FKs. If so, either set withFKsDisabled = true as const (in a sqlite/ subclass if this is a common/ migration) or use raw ALTER TABLE ADD COLUMN for nullable/defaulted columns. — SQLite table recreation risk No live-app value imports in the migration body. Inline types/utility code locally. — Never import entities as values async down() was tested locally : pnpm start && pnpm start -- db:revert && pnpm start on both SQLite and Postgres. — Reversibility One logical change per migration ; split unrelated table changes into separate files. — Don't combine independent schema changes up() / down() reads as a list of intentions. If either body grows past a screen or mixes schema operations with a multi-statement raw-SQL data move, extract the data move into a private async method on the same class (e.g. private async backfillFromX(ctx) ). The top-level should orchestrate, not implement. Precedent is the bar to fix, not perpetuate. When the checklist conflicts with what an older migration does (e.g. redundant .primary.notNull , hand-quoted identifiers, missing .comment() ), the checklist wins for new code — don't copy the violation forward. Note the old occurrences in the PR if you spotted them. Regenerated the schema docs with pnpm db:schema:docs and committed the docs/generated/ changes. The DB Tests CI job fails on stale docs. — Schema documentation Treat the checklist as a floor, not a ceiling. If any item fails, fix it before opening review. Common Guidance Rules that apply to every migration — schema or data, common or DB-specific. Read this section before writing anything. Creating Migrations Temporary timestamp workaround: This repository currently has future-dated migrations, with the head at 1784000000008 ( 2026-07-14T03:33:20.008Z ). Until real time passes that timestamp, a migration created with Date.now() would sort before the deployed head and can run out of order on databases that already applied later migrations. Use the generator during this window — it picks max + 1 when needed. See PR #30511 for context. Migration files are named {TIMESTAMP}-{DescriptiveName}.ts . The timestamp must be strictly greater than every existing migration timestamp in this package (across common/ , postgresdb/ , and sqlite/ ). TypeORM runs unrecorded migrations in timestamp order, so inserting a value below the current max corrupts ordering on databases that have already executed the later migrations. Use the generator — it picks a safe timestamp, writes the scaffold, and regenerates the migration index files ( sqlite/index.ts and postgresdb/index.ts are gitignored build artifacts, generated from the files on disk by scripts/generate-migration-index.mjs — never edit or commit them): pnpm --filter=@n8n/db migration:new <Name> [--folder=common|postgresdb|sqlite] <Name> is PascalCase and describes the change (e.g. AddTracingToExecution ). --folder defaults to common ; use postgresdb or sqlite only for dialect-specific migrations. The generator picks Date.now() when it's greater than the current head, otherwise max + 1 . The migration-timestamp rule in @n8n/code-health enforces both invariants (strict ordering and no far-future fabrication) at lint time; the generator is the easy path, the rule is the safety net. Applying and Reverting Migrations Pending migrations are applied during normal n8n startup. In a local checkout, run pnpm start with the target code version to apply them manually. To revert the most recently applied reversible migration, use the CLI command: n8n db:revert In a local checkout, run the same command through the package script: pnpm start -- db:revert Do not revert migrations by editing the migrations table or running hand-written SQL. db:revert runs the migration's down() method and preserves TypeORM's migration bookkeeping. Which directory to choose single schema change, DSL covers it → common/ Postgres-only feature (gen_random_uuid, ALTER COLUMN TYPE, partial expr index) → postgresdb/ SQLite needs different recipe or to skip CASCADE on table recreate → sqlite/ (subclass common/, set withFKsDisabled = true as const) If only Postgres needs the change, put the file under postgresdb/ only — don't write a no-op SQLite migration with if (isPostgres) guards. See Cross-database Compatibility for when to split per-DB. Class shape import type { MigrationContext , ReversibleMigration } from '../migration-types' ; export class AddFooBar1700000000000 implements ReversibleMigration { async up ( { schemaBuilder: { addColumns, column, createIndex }, escape }: MigrationContext ) { // ... } async down ( { schemaBuilder: { dropIndex, dropColumns } }: MigrationContext ) { // ... } } ReversibleMigration (default) requires both up and down . IrreversibleMigration only when down() would lose data unrecoverably — see Reversibility . withFKsDisabled = true as const only in sqlite/ subclasses that recreate FK-referenced tables (otherwise SQLite's CASCADE eats data). Follow good code hygiene A migration class is still a class — up() shouldn't be a 200-line procedure. Break long logical steps into private methods with a name that describes what they do ( backfillSlugs ). up() then reads as a short list of step calls. Don't extract single-line steps. A method whose body is one DSL call adds no information — the call site is already self-documenting. // 🚫: everything inline in up() export class MigrateThing1234567890000 implements IrreversibleMigration { async up ( ctx : MigrationContext ) { // 80 lines of mixed DDL, raw SQL, batched updates, logging... } } // ✅: up() is a table of contents; only multi-step work gets its own method export class MigrateThing1234567890000 implements IrreversibleMigration { async up ( ctx : MigrationContext ) { const { schemaBuilder : { addColumns, column, createIndex } } = ctx; // One-liner DSL calls stay inline — naming them adds no information. await addColumns ( 'my_table' , [ column ( 'slug' ). varchar ( 255 )], { recreatesOnSqlite : true }); // The non-trivial step gets a named method. await this . backfillSlugs (ctx); await createIndex ( 'my_table' , [ 'slug' ], true ); } private async backfillSlugs ( { escape , runQuery, runInBatches, logger, migrationName }: MigrationContext ) { const table = escape . tableName ( 'my_table' ); await runInBatches<{ id : string ; name : string }>( `SELECT id, name FROM ${table} WHERE slug IS NULL` , async (rows) => { for ( const row of rows) { try { const slug = row. name . toLowerCase (). replace ( /\\s+/g , '-' ); await runQuery ( `UPDATE ${table} SET slug = :slug WHERE id = :id` , { slug, id : row. id }); } catch (error) { logger. warn ( `[ ${migrationName} ] Failed to backfill row ${row.id} : ${(error as Error ).message} ` ); } } }, ); } } Why: A migration is read more often than it's written — during review, during incident response, and years later when someone has to understand why a column exists. Named steps double as documentation. They also make it easier to skim a diff: a reviewer can tell at a glance whether the change is \"added a new step\" or \"rewrote an existing one.\" Reversible migrations benefit even more — down() can call the same private helpers in reverse. Prefer runQuery() over queryRunner Run SQL through runQuery() from MigrationContext . Never call queryRunner.query() or queryRunner.manager.* from a migration.",
    "model_config": {
        "provider": "deepseek",
        "model": "deepseek-chat",
        "temperature": 0.7,
        "max_tokens": 4096,
        "top_p": 0.9
    },
    "trigger_words": [],
    "source": "DeepseekModel",
    "source_url": "https://deepseekmodel.com/skill?id=n8n-io-n8n-agents-skills-db-migrations-skill-md"
}