TypeORM Database Migrations

Golden Rule

Never write migration files by hand. Always auto-generate them from TypeORM entity changes. Never edit a generated migration — if it's wrong, fix the entity and regenerate.

Entity Rules — The Source of Truth

TypeORM entities (Records) are the single source of truth for the database schema. Everything must be expressed through decorators. If a constraint, index, or FK exists in the database but not in an entity decorator, TypeORM will try to drop it on the next migration:generate.

Relations define constraints, not loading behavior

@ManyToOne creates a foreign key constraint in the database. It does NOT mean the relation is eagerly loaded — relations are lazy by default. You control loading per-query via relations: { ... }.

Always define @ManyToOne when a column references another table, even if you never load the relation object:

// CORRECT — column + relation = FK constraint in DB
@Column({ name: 'integration_id', type: 'varchar' })
integrationId: string;

@ManyToOne(() => McpIntegrationRecord, { onDelete: 'CASCADE' })
@JoinColumn({ name: 'integration_id' })
integration: McpIntegrationRecord;

// WRONG — plain column = no FK, no referential integrity
@Column({ name: 'integration_id', type: 'varchar' })
integrationId: string;

All constraints must use decorators

DB concept	Decorator
Foreign key	@ManyToOne / @OneToOne + @JoinColumn
Unique constraint (full)	@Unique([...]) on class
Unique constraint (partial)	@Index([...], { unique: true, where: '...' }) on class
Check constraint	@Check('name', 'expression') on class
Index	@Index() on property or @Index([...]) on class
Enum column	@Column({ type: 'enum', enum: MyEnum })

Partial unique indexes (with WHERE clause) cannot use @Unique — use @Index with unique: true and where instead.

If you add a value to a TypeScript enum used in @Column({ type: 'enum' }), you must generate a migration.

Never hand-write constraint names

TypeORM generates deterministic constraint/index names. Hand-written names (e.g. FK_my_custom_name) will mismatch, causing perpetual drift.

Workflow

1. Modify the Entity

Change the record file. Extend BaseRecord for id, createdAt, updatedAt:

@Entity('agents')
export class AgentRecord extends BaseRecord {
  @Column({ type: 'varchar', length: 255, nullable: true })
  marketplaceSlug: string | null;
}

2. Generate the Migration

The dev stack must be running (./dev status).

pnpm run migration:generate:dev src/db/migrations/DescriptiveMigrationName

No -- before the path. pnpm forwards the trailing path straight to the script. Adding a -- separator makes pnpm swallow the path, so TypeORM sees zero arguments and aborts with Nicht genügend Argumente ohne Optionen: 0 vorhanden, mindestens 1 benötigt (ELIFECYCLE Command failed with exit code 1). Pass the path with no --.

3. Review the Generated Migration

• Contains only the expected changes (no unrelated drift)
• up() and down() are symmetric
• No hand-written constraint or index names

If the migration contains unexpected drift, the local DB is out of sync. Rebuild it — do not hand-author a migration to reconcile. See "Fixing Migration Drift" below.

4. Run the Migration

pnpm run migration:run:dev

5. Verify Zero Drift

pnpm run migration:generate:dev src/db/migrations/VerifyNoDrift

This must print No changes in database schema were found. If it generates a file, entities and migrations are still out of sync — investigate before committing.

6. Validate

pnpm exec tsc --noEmit
pnpm run test

Naming Convention

PascalCase describing the change:

Change	Migration Name
Add a column	AddMarketplaceSlugToAgents
Create a table	CreateTeamSharesTable
Add an index	AddIndexOnThreadCreatedAt
Remove a column	RemoveUserShareScope
Add a constraint	CascadeDeleteSharesOnScopeDelete

Fixing Migration Drift

"Drift" here means: entities on the branch, the migration files, and the local Postgres schema no longer agree. Typical triggers:

• Switching between branches whose migration sets diverge (slot's Postgres volume was written by branch A, you're now on branch B).
• pnpm run migration:generate:dev produces surprise changes (removes/renames on things you never touched, or resurrects columns).
• Backend refuses to start with errors like error: relation "<table>" already exists (PG code 42P07) or column "..." of relation "..." does not exist.

Rule: rebuild the database from scratch. Do not hand-author a reconciliation migration. Hand-written reconciliation migrations poison the migration history for every teammate and every future deploy — they are worse than the drift they were meant to fix.

The rebuild

Shut the stack down and spin the DB up on an empty volume, then let migrations replay from zero:

# 1. Shut the stack down completely (from any worktree)
./dev down

# 2. Wipe the slot's Postgres volume so ./dev up starts from an empty DB.
#    Without -v the volume survives and the drift returns immediately.
docker compose -p ayunis-dev-<SLOT> down -v

# 3. Bring the stack back up — ./dev up replays every migration from scratch
./dev up --slot <SLOT>

# 4. Optional: reseed fixtures (see seed-database skill)
cd ayunis-core-backend && pnpm run seed:minimal:ts

<SLOT> is the slot number this worktree uses — the same one you passed to ./dev up. See the dev-environment skill for slot conventions.

After the rebuild

Verify zero drift before making further changes:

cd ayunis-core-backend
pnpm run migration:generate:dev src/db/migrations/VerifyNoDrift
# Must print: "No changes in database schema were found"

If a VerifyNoDrift file is produced, entities and migrations are genuinely out of sync — that's a legitimate migration to generate (via TypeORM), not to hand-write. Investigate which entity changed and, if the fix belongs in an earlier migration, edit that migration's entity source, not the reconciliation file.

Anti-Patterns

Don't	Why	Instead
Write migration SQL by hand	Drift between entities and schema	Modify the entity, then auto-generate
Hand-author a migration to reconcile drift	Poisons migration history for teammates and deploys	Rebuild the DB (./dev down + wipe volume + ./dev up)
Edit a generated migration	Constraint names will mismatch	Fix the entity and regenerate
Hand-write FK/index/constraint names	Perpetual drift on future generates	Let TypeORM name everything
Use @Column for a FK without @ManyToOne	No FK in the database, no referential integrity	Add @ManyToOne + @JoinColumn
Add enum values without a migration	DB enum won't match TypeScript enum	Generate a migration after adding values
Use @Unique for partial constraints	Produces full constraint, not partial	Use @Index({ unique: true, where: '...' })
Skip zero-drift verification	Entities and migrations may still be misaligned	Always verify after running
Commit without running	May fail at runtime	Always migration:run:dev first