---
url: https://docs.tailor.tech/sdk/services/tailordb-migration.md
---
# TailorDB Migrations

> **Beta:** The `tailordb migration` command and the migration runtime are beta features. They may introduce breaking changes in future releases. The CLI emits a beta warning on every invocation.

The migration system tracks changes to your TailorDB table definitions over time and applies them to deployed workspaces with optional data transformation scripts.

For the CLI command reference, see [`tailordb migration`](../cli/tailordb.md#tailordb-migration). This document covers concepts, workflows, and operational guidance.

## Overview

**Key Properties**

* **Local snapshot–based diff detection** — each migration is generated by diffing your current table definitions against the previous snapshot stored in `migrations/<NNNN>/`.
* **Transaction-wrapped data migrations** — each `migrate.ts` script runs inside a database transaction on the platform; if the script throws, all changes in that migration roll back.
* **Automatic execution during `apply`** — `tailor deploy` detects pending migrations, runs the two-stage table update (pre-migration → script → post-migration), and updates the migration checkpoint label.
* **Type-safe scripts** — the generated `db.ts` provides Kysely types that reflect the schema state **before** the migration runs, so transformations are written against the actual data shape.

**Files in `migrations/`**

```
migrations/
├── 0000/                    # Initial schema snapshot
│   └── schema.json
├── 0001/                    # First change
│   ├── diff.json            # Field-level diff from 0000
│   ├── migrate.ts           # Data migration script (auto-generated for breaking changes; can be added manually via `migration script`)
│   └── db.ts                # Kysely types for the script (pre-migration shape)
├── 0002/
│   └── diff.json            # No script — non-breaking changes only
└── ...
```

`0000` always contains a full snapshot. `0001` and onward contain a diff plus, optionally, a script and its types (auto-generated for breaking changes, or added manually via `tailordb migration script` for warning-tier changes). **Commit the entire `migrations/` directory to version control.**

## Initial Setup

### New project

When you start with no `migrations/` directory:

1. Add the `migration` block to `tailor.config.ts` (see [Configuration](#configuration)).
2. Define your initial tables in `tailordb/`.
3. Generate the initial migration:
   ```bash
   tailor tailordb migration generate
   ```
   This creates `migrations/0000/schema.json` from your current tables.
4. Run `tailor deploy`. The migration label is set to `0000` on the deployed namespace.

### Adding migrations to an existing project

If you already have a deployed workspace whose schema matches your local table definitions:

1. Add the `migration` block to `tailor.config.ts`.
2. Run `tailor tailordb migration generate` to create `0000/schema.json` from current local tables.
3. Run `tailor deploy`. Because remote schema already matches, no script runs; only the migration label is set.

If your local tables and remote schema have **diverged**, reconcile them before introducing migrations — either update local tables to match remote, or accept that the first non-`0000` migration will reflect that gap.

### Resetting

`tailor tailordb migration generate --init` deletes the existing `migrations/` directory and creates `0000` from the current local types. Use it only before the project is deployed. For a deployed migration history, use [`migration rebaseline`](#re-baselining-a-deployed-migration-history), which verifies the history and connected workspace before replacing any files.

## Migration Workflow

A typical change cycle:

1. **Modify a table definition.**

   ```typescript
   // tailordb/user.ts
   export const user = db.table("User", {
     name: db.string(),
     email: db.string(), // ← new required field
     ...db.fields.timestamps(),
   });
   ```

2. **Generate the migration.**

   ```bash
   tailor tailordb migration generate --name "add email to user"
   ```

   Output:

   ```
   Generated migration 0001
     Diff file: ./migrations/0001/diff.json
     Migration script: ./migrations/0001/migrate.ts
     DB types: ./migrations/0001/db.ts
   ```

   If `EDITOR` or `VISUAL` is set, `migrate.ts` opens automatically.

3. **Edit `migrate.ts`** to populate data for the new required field:

   ```typescript
   import type { Transaction } from "./db";

   export async function main(trx: Transaction): Promise<void> {
     await trx
       .updateTable("User")
       .set({ email: "default@example.com" })
       .where("email", "is", null)
       .execute();
   }
   ```

4. **Apply.**
   ```bash
   tailor deploy
   ```
   The pre-migration phase relaxes the new field to optional, the script runs and populates values, then the post-migration phase enforces `required: true`.

### Warnings and optional migration scripts

Some non-breaking changes can still cause data loss — most notably removing a field (`field_removed`) or removing a table (`type_removed`). `migration generate` reports these as **warnings**:

```
Warning: data loss possible:

  - User.legacyParentId: Field removed (existing data will no longer be accessible through the schema after the post-migration phase)
```

No `migrate.ts` is generated automatically because the schema change itself is non-breaking, but the existing data is no longer accessible through the active schema after the post-migration phase. The platform may retain a removed field's underlying stored value, so do not rely on removal to clear data before reusing the same field name. If you need to preserve, transform, or clear that data first, add a script with:

```bash
tailor tailordb migration script 0002
```

This writes `migrations/0002/migrate.ts` and `migrations/0002/db.ts` next to the existing `diff.json` (add `--with-test` to also scaffold a `migrate.test.ts` — see [Testing Migrations Locally](#testing-migrations-locally)). The removed field stays readable inside `migrate.ts` because the pre-migration phase keeps it on the table until the script finishes (see [Per-migration phases](#per-migration-phases)). The next `tailor deploy` runs the script automatically — `migrate.ts` is executed whenever the file exists on disk, regardless of whether the diff itself required it.

If the data loss is intentional and no script is needed, record that decision the same way as for breaking changes (see [Breaking changes without a script](#breaking-changes-without-a-script)):

```bash
tailor tailordb migration script 0002 --no-script --reason "column no longer needed, data can be dropped"
```

In an interactive session, `migration generate` offers to record the reason on the spot when it detects warnings. The acknowledgment is stored in `diff.json`, so it is reviewable in the PR, and it satisfies `migration validate --strict` — useful for enforcing in CI that destructive changes are explicitly acknowledged before merge (see [Schema verification](#schema-verification)).

### Renaming a field

Renaming a field in a type definition looks like a removal plus an addition to the diff engine. Left as-is, that combination silently drops the old field's data: the removal is only a warning, so nothing forces a data copy.

To prevent that, when `migration generate` finds a removed field and an added field in the same type whose stored values can be copied without changing their meaning, it asks whether the change is a rename. Serial fields are never rename candidates, and an enum field only qualifies when it keeps every value of the removed field:

```
? User.fullName was removed and displayName was added with a compatible type. Was it renamed to displayName? (Y/n)
```

In non-interactive environments (or with `--yes`), no prompt is shown and the command fails while a rename candidate is left unresolved — writing it as remove + add would silently drop the field's data at deploy. Resolve every candidate explicitly: pass the rename, or confirm a genuine removal with `--drop`:

```bash
tailor tailordb migration generate --rename "User.fullName:displayName"
tailor tailordb migration generate --drop "User.fullName"
```

Repeat `--rename` and `--drop` for multiple fields. A `--rename` that does not match a compatible removed + added pair, or a `--drop` that does not match a removed field, fails with an error.

A confirmed rename is recorded as a single `field_renamed` change and treated as **breaking**, so a migration script is required. The generated `migrate.ts` copies the old field into the new one with a single set-based update, and the generated `db.ts` exposes both the old field (readable) and the new field (writable). The copy intentionally overwrites every row without checking for existing values: values of removed fields are retained in storage, so a stale value could otherwise resurface under the new name later. If the new field adds a unique constraint, the script also includes a duplicate-resolution block to run before the constraint is enforced.

During deploy, the pre-migration phase keeps the old field and adds the new field with its constraints relaxed, the script copies the data, and the post-migration phase drops the old field and enforces the new field's constraints — all within a single `tailor deploy`.

If you decline the prompt (or confirm the removal with `--drop`), the change stays a plain removal + addition with the usual data-loss warning.

Renaming a **type** is not yet detected; it is still a type removal plus a type addition.

Renaming a member inside a **nested field** is not detected either, and it is quieter: `User.address.zip` → `zipCode` becomes a single `field_modified` on `address` with no breaking change, no warning, and no generated script, so the member's values are not carried over. Copy them with a custom `tailordb migration script` if they must survive.

### Breaking changes without a script

Breaking changes require `migrate.ts`. If it is missing at deploy time (for example, the generated script was deleted), `tailor deploy` fails before applying the migration or anything after it. When there is genuinely nothing to migrate — say, the affected table holds no data yet — record an explicit acknowledgment instead of keeping an empty script:

```bash
tailor tailordb migration script 0002 --no-script --reason "no data yet, safe to skip"
```

This stores the reason in `migrations/0002/diff.json` (commit the change). The next `tailor deploy` applies the schema change as usual, skips only the script step, and logs the recorded reason. The command refuses to record a skip while `migrate.ts` exists — delete the script first. If `migrate.ts` is added back later, `tailor deploy` fails rather than choosing between the script and the acknowledgment; run `tailor tailordb migration script 0002` again to clear the now-stale acknowledgment from `diff.json` (the script then runs on the next deploy), or delete `migrate.ts` to keep the skip.

## Configuration

```typescript
// tailor.config.ts
export default defineConfig({
  name: "my-app",
  db: {
    tailordb: {
      files: ["./tailordb/*.ts"],
      migration: {
        directory: "./migrations",
        // Optional. Defaults to the first machine user in auth.machineUsers.
        machineUser: "admin-machine-user",
      },
    },
  },
});
```

| Option                  | Type   | Description                                                                                               |
| ----------------------- | ------ | --------------------------------------------------------------------------------------------------------- |
| `migration.directory`   | string | Directory path for migration files. Required when migrations are enabled for the namespace.               |
| `migration.machineUser` | string | Machine user used to run migration scripts. Optional; defaults to the first entry in `auth.machineUsers`. |

## Generated Files

| File                   | When generated                                                                                               | Description                                                                                                              |
| ---------------------- | ------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------ |
| `0000/schema.json`     | First `migration generate`                                                                                   | Full snapshot of all tables in the namespace.                                                                            |
| `XXXX/diff.json`       | Every subsequent migration                                                                                   | Field-level diff against the previous snapshot.                                                                          |
| `XXXX/migrate.ts`      | Auto-generated for breaking changes; added manually via `tailordb migration script` for warning-tier changes | Data transformation script. The `main` export receives a Kysely `Transaction`.                                           |
| `XXXX/db.ts`           | Generated once when `migrate.ts` is created                                                                  | Kysely types reflecting the schema **before** this migration. Exports `Database`, `Transaction`, and `MigrationContext`. |
| `XXXX/migrate.test.ts` | Added via `tailordb migration script --with-test`                                                            | Unit-test scaffold for `migrate.ts` (see [Testing Migrations Locally](#testing-migrations-locally)). Never deployed.     |

`db.ts` reflects the pre-migration schema because the script runs after the pre-migration phase has temporarily relaxed breaking constraints (e.g., a new `required` field is added as `optional` first), so the data being read still matches the previous shape.

### Migration file format compatibility

Migration files are versioned independently of the SDK package. This SDK writes format version `2` and reads versions `1` through `2`. It normalizes supported older formats in memory; it never rewrites applied migration files on disk.

If a future SDK can no longer replay an old migration format, re-baseline while using an SDK version that still supports the complete history, commit the new baseline, deploy it to every environment, and then upgrade the SDK. A file from a newer unsupported format instead requires upgrading the SDK first. The CLI rejects both cases with guidance rather than attempting a best-effort replay.

There is no migration-file conversion command. Keeping applied files unchanged preserves the record of what ran, while `migration rebaseline` provides the escape hatch when the supported replay window changes.

## Migration Script Anatomy

```typescript
import type { Transaction } from "./db";

export async function main(trx: Transaction): Promise<void> {
  // SELECT is supported.
  const users = await trx.selectFrom("User").select(["id", "name"]).execute();

  // Loop and transform.
  for (const u of users) {
    await trx
      .updateTable("User")
      .set({ displayName: u.name.toUpperCase() })
      .where("id", "=", u.id)
      .execute();
  }
}
```

**Worked example: backfilling a new required enum field**

Adding a required field is a breaking change, so `migration generate` scaffolds `migrate.ts`. Given this table change:

```typescript
// tailordb/user.ts
export const user = db.table("User", {
  name: db.string(),
  email: db.string(),
  role: db.enum(["MANAGER", "STAFF"]), // ← new required field
  ...db.fields.timestamps(),
});
```

existing `User` rows have no `role` value yet, so the script assigns one before the post-migration phase enforces the constraint:

```typescript
import type { Transaction } from "./db";

export async function main(trx: Transaction): Promise<void> {
  await trx.updateTable("User").set({ role: "MANAGER" }).where("role", "is", null).execute();
}
```

The `where("role", "is", null)` guard keeps the script idempotent — rows that already have a value are untouched if the script re-runs.

Reference scripts for other breaking-change patterns live in the repository's [migration fixture templates](https://github.com/tailor-platform/sdk/tree/main/example/tests/migration-fixtures/templates): backfilling fields that become required and migrating rows off a removed enum value ([0005](https://github.com/tailor-platform/sdk/blob/main/example/tests/migration-fixtures/templates/0005/migrate.ts)), and de-duplicating values before a unique constraint is added ([0006](https://github.com/tailor-platform/sdk/blob/main/example/tests/migration-fixtures/templates/0006/migrate.ts)). They show the shape of each migration, not drop-in logic — adapt them to your data (the suffix strategy in `0006`, for example, assumes the suffixed names are not already taken).

**Accessing environment variables**

The migration `main` receives an optional second argument exposing the variables defined in `defineConfig({ env })` — the same values available via `context.env` in resolvers. The `MigrationContext` type is exported from the generated `./db`:

```typescript
import type { Transaction, MigrationContext } from "./db";

export async function main(trx: Transaction, { env }: MigrationContext): Promise<void> {
  // Branch on environment-specific config resolved at deploy time
  if (env.SKIP_BACKFILL) return;

  await trx.updateTable("User").set({ stage: env.ENVIRONMENT }).execute();
}
```

The `env` values are injected at bundle time (the same mechanism as resolvers/executors/workflow jobs); `process.env` and other Node-side environment access remain unavailable at runtime. The second argument is optional — existing `main(trx)` scripts continue to work unchanged.

**Rules**

* Always use the `trx` argument for database access. Anything that bypasses `trx` is not part of the transaction and will not roll back on failure.
* Do not import resolvers, executors, or other SDK runtime services. The migration script runs as a standalone bundle on the platform with only Kysely access; SDK service helpers are not available.
* Standard Node-compatible packages are bundled. Keep dependencies minimal — every import is shipped to the platform.
* `console.log` / `console.error` output is captured and surfaced under `Logs:` in the apply output. Use it sparingly for progress markers on long-running migrations.
* The script is idempotency-friendly by default because it runs inside a transaction, but **plan for re-execution**: if a later migration in the same `apply` fails, the platform may retry the apply, and you may want your script to tolerate already-migrated rows (e.g., `where("email", "is", null)` instead of unconditional updates).

## Supported Schema Changes

| Change Type                       | Breaking? | Migration Script? | Notes                                                                                                                                                                                                                                   |
| --------------------------------- | --------- | ----------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Add optional field                | No        | No                | Schema change only                                                                                                                                                                                                                      |
| Add required field                | Yes       | Yes               | Script populates default values                                                                                                                                                                                                         |
| Remove field                      | No        | Optional          | Warning tier — no script is auto-generated, but you can add one with `tailordb migration script` to preserve or clear data before the field leaves the active schema. The field stays readable from `migrate.ts` during Pre-migration.  |
| Rename field                      | Yes       | Yes               | Confirmed interactively at generate time or via `--rename "Type.old:new"` — see [Renaming a field](#renaming-a-field). Auto-generated script copies values from the old field to the new one; both fields coexist during Pre-migration. |
| Change optional → required        | Yes       | Yes               | Script sets defaults for null values                                                                                                                                                                                                    |
| Change required → optional        | No        | No                | Schema change only                                                                                                                                                                                                                      |
| Add index (non-unique)            | No        | No                | Schema change only                                                                                                                                                                                                                      |
| Add unique index                  | Yes       | Yes               | Script must resolve duplicate value combinations across the index fields                                                                                                                                                                |
| Change unique index fields        | Yes       | Yes               | Treated like adding a new unique constraint over the new field set                                                                                                                                                                      |
| Remove index                      | No        | No                | Schema change only (removing the unique constraint from an index is also non-breaking)                                                                                                                                                  |
| Add unique constraint             | Yes       | Yes               | Script must resolve duplicate values                                                                                                                                                                                                    |
| Remove unique constraint          | No        | No                | Schema change only                                                                                                                                                                                                                      |
| Change decimal scale              | Yes       | Yes               | Auto-generated script re-saves existing rows under the new scale. Decreasing scale rounds values half-up and can lose precision. If the same change adds a unique constraint, duplicate handling runs after re-saving.                  |
| Add enum value                    | No        | No                | Schema change only                                                                                                                                                                                                                      |
| Remove enum value                 | Yes       | Yes               | Script migrates records with removed values                                                                                                                                                                                             |
| Add table                         | No        | No                | Schema change only                                                                                                                                                                                                                      |
| Remove table                      | No        | Optional          | Warning tier — no script is auto-generated, but you can add one with `tailordb migration script` to preserve data before the table leaves the active schema. The table stays readable from `migrate.ts` during Pre-migration.           |
| Change foreign key target table   | Yes       | Yes               | Script updates references to the new target                                                                                                                                                                                             |
| Change field type (verified pair) | Yes       | Yes               | In-place for `uuid` → `string`, `enum` → `string`, `decimal` → `string`, and `integer` → `float`; review the generated normalization scaffold and customize it only when existing values need transformation                            |
| Change field type (other pair)    | -         | -                 | **Not supported** — see [3-step migration](#3-step-migration-for-unsupported-changes)                                                                                                                                                   |
| Change array → single value       | -         | -                 | **Not supported** — see [3-step migration](#3-step-migration-for-unsupported-changes)                                                                                                                                                   |
| Change single value → array       | -         | -                 | **Not supported** — see [3-step migration](#3-step-migration-for-unsupported-changes)                                                                                                                                                   |

### Generated normalization script for field type changes

For example, changing `User.age` from `integer` to `float` generates a `migrate.ts` that scans non-null values in batches of 100:

```typescript
import type { Transaction } from "./db";

export async function main(trx: Transaction): Promise<void> {
  // Normalize User.age from integer to float while the previous type is still active
  {
    let lastId: string | undefined;
    while (true) {
      let query = trx
        .selectFrom("User")
        .select(["id", "age"])
        .where("age", "is not", null)
        .orderBy("id", "asc")
        .limit(100);
      if (lastId) {
        query = query.where("id", ">", lastId);
      }
      const rows = await query.execute();
      if (rows.length === 0) break;

      for (const row of rows) {
        // TODO(tailor-migration-review): Remove this marker and the `never` annotation after reviewing the normalization.
        // Keep the value accepted by the active integer type and castable to float.
        const sourceValue = row.age;
        if (sourceValue === null) continue;
        const normalizedValue: never = sourceValue;
        if (Object.is(normalizedValue, sourceValue)) continue;
        await trx
          .updateTable("User")
          .set({ ["age"]: normalizedValue })
          .where("id", "=", row.id)
          .execute();
      }
      lastId = rows[rows.length - 1]!.id;
    }
  }
}
```

The generated `never` annotation intentionally causes a TypeScript error until you review the normalization. If the existing values are already suitable for the target type, remove the annotation and review marker to accept the identity transformation; it does not write any rows. If values need application-specific normalization, replace the expression and remove the annotation and marker while keeping the result valid for both the active source type and the target type. The source field contract remains active until the script finishes; for example, an `integer` → `float` script cannot write fractional values during this phase.

### 3-step migration for unsupported changes

Field type changes outside the verified in-place pairs (for example, `string` → `integer`) and array-cardinality changes are detected but rejected by the diff engine. Use an expand-contract strategy:

1. **Migration N**: Add an optional field with the desired type (e.g., `fieldName_new`). If the old field is required, make it optional in the same migration. Write a script that copies and converts every non-null old value into the temporary field, then sets the old field to `null` in the same row update.
2. **Migration N+1**: Remove the old field.
3. **Migration N+2**: Add the field back with the original name and the new type. Script copies from the temporary field, then remove the temporary field in migration N+3 (or in the same step if you can express it).

The same pattern works for switching between scalar and array.

> **Do not skip clearing the old field.** Removing a field from the schema does not necessarily remove its stored JSON value. Re-adding the same name with an incompatible type can deploy successfully while leaving stale values that make subsequent reads fail. Verify that every old value is `null` before removing and re-adding the field name.

## Testing Pending Migrations

`tailor tailordb migration test` runs every migration pending in the source workspace against an isolated workspace. The source workspace is selected by `--workspace-id` or the active profile and is never modified.

The command performs the following sequence:

1. Reads each migration-enabled namespace's `sdk-migration` checkpoint from the source workspace and reconstructs that exact snapshot from local migration history.
2. Creates a temporary workspace in the same region, organization, and folder as the source, unless `--target-workspace-id` names an existing throwaway workspace.
3. Deploys the checkpoint snapshots and writes their checkpoint labels.
4. Loads fixture data or clones source records.
5. Runs the normal deployment pipeline, including every pending pre-migration, `migrate.ts`, and post-migration phase.
6. Optionally runs an assertion script against the migrated data.
7. Deletes an automatically-created workspace after success or failure.

Both the pre-migration and final TailorDB schemas come from committed migration snapshots. Ungenerated changes in the current type source are not included in the rehearsal.

Executors are omitted from the baseline deployment so loading fixture or cloned records cannot trigger current event handlers against the older schema. Auth user profiles are also deferred until the pending migrations finish, while configured machine users remain available to run seed and migration scripts. The final deployment restores the configured executors and user profiles. Static websites are deployed so configuration references to their URLs resolve, but their workspace-bound custom domains are omitted from migration-test deployments.

### Seed mode

Seed mode is the default and uses the JSONL files produced by the configured `seedPlugin`. Run `tailor generate` after adding the plugin or changing seed types, then populate its `data/*.jsonl` files:

```bash
tailor tailordb migration test --data seed
```

Rows are loaded only for types present in the deployed pre-migration snapshots (and current schemas without migrations), in foreign-key dependency order. Fields introduced by pending migrations, including timestamp and nested fields, are removed before insertion so current fixtures can be loaded into the baseline schema. Missing type files are treated as empty. IdP `_User` fixtures are not loaded by this command.

Use `--machine-user` to override the seed plugin's `machineUserName`, the namespace migration setting, and the first configured Auth machine user for seed and assertion execution.

### Clone mode

Clone mode copies TailorDB records from the source workspace after the identical application, namespace names, and pre-migration schemas exist in the target. For namespaces without migration history, the command reproduces the deployed source schema rather than uncommitted local type changes:

```bash
tailor tailordb migration test --data clone
```

The platform clone API is feature-gated and requires editor access to both same-region workspaces. It copies TailorDB records only: IdP users, file blobs, and metadata labels are not copied. File fields therefore retain references whose blobs are absent. The command polls the asynchronous operation and reports platform failures; if clone is unavailable, use seed mode.

The source schema is re-verified immediately before cloning; if the source workspace was deployed or otherwise changed after the test started, the command aborts instead of cloning data that no longer matches the deployed baseline.

### Assertions and retained targets

Pass a TypeScript file with `--assert`. Its exported `main` function uses the same Kysely transaction signature as `migrate.ts`, runs after all pending migrations, and must throw when an invariant fails:

```bash
tailor tailordb migration test \
  --data seed \
  --assert ./tests/assert-customer-email.ts \
  --assert-namespace tailordb
```

`--assert-namespace` is inferred when only one namespace has pending migrations and is required otherwise.

To inspect the result after a run, pass `--keep` so the automatically created workspace survives instead of being deleted, on success and on failure:

```bash
tailor tailordb migration test --keep
```

Alternatively, provide an empty designated throwaway workspace. This mode never deletes the target and requires explicit acknowledgment:

```bash
tailor tailordb migration test \
  --target-workspace-id 00000000-0000-4000-8000-000000000000 \
  --yes
```

Do not target a shared development or production workspace: baseline deployment reconciles its managed resources and schemas before the migration test runs.

## Automatic Migration Execution

When you run `tailor deploy`, the SDK detects pending migrations (anything past the current `sdk-migration` label on the deployed namespace) and runs them in order before continuing with the rest of the apply.

### Per-migration phases

For each pending migration:

1. **Pre-migration**: Schema changes that would be breaking are applied in a relaxed form first. A verified in-place field type change keeps its complete previous field contract until Post-migration, including field and type-level hooks or validators changed by the same migration. Newly-required fields are added as optional; fields whose `optional → required` transition is breaking are temporarily kept optional. Fields that are being removed in this migration are temporarily kept on the table so that `migrate.ts` can still read them (for example, to `innerJoin` through a foreign key that is about to be dropped). For a renamed field, the old field is kept and the new field is added with its constraints relaxed, so the script can read the old field and write the new one. Breaking type-level index changes are relaxed the same way: a newly-added unique index is withheld, and an index gaining a unique constraint (or a unique index changing its field set) keeps its previous definition, so `migrate.ts` can resolve duplicates first. Non-breaking changes that are part of the same migration are also applied here.
2. **Script execution**: If `migrate.ts` exists on disk for this migration, it is bundled and sent to the platform via the script execution API and runs as the configured machine user inside a transaction. The script is hard-required for breaking changes (`diff.requiresMigrationScript`) — deploy fails if the file is missing, unless a `--no-script` acknowledgment was recorded (see [Breaking changes without a script](#breaking-changes-without-a-script)). It is also executed when present for warning-tier diffs — see [Warnings and optional migration scripts](#warnings-and-optional-migration-scripts).
3. **Post-migration schema**: Required constraints and the target field definitions are applied. Do not assume that removing a field clears its underlying stored JSON value.
4. **Checkpoint and cleanup**: The `sdk-migration` label is bumped to this migration's number, then removed GQL permissions and tables are deleted. Advancing the checkpoint first prevents a failed checkpoint write from requiring the SDK to recreate irreversibly deleted records.

This split is what allows existing rows to be backfilled before the database starts rejecting nulls, and what lets `migrate.ts` traverse foreign-key fields that the same migration removes.

### Schema verification

Before running migrations, `apply` performs two checks:

1. **Local schema check** — your current table definitions must match the latest snapshot in `migrations/`. If they don't, you forgot to run `migration generate`.
2. **Remote schema check** — the deployed schema is reconstructed from migration history; the actual remote schema must match. Drift here means someone applied a different set of migrations or edited the schema out-of-band.

On drift you'll see something like:

```
✖ Remote schema drift detected:
Namespace: tailordb
  Remote migration: 0007
  Differences:
  Type 'User':
    - Field 'email': required: remote=false, expected=true
```

The error also points you at `migration status`, `migration generate`, `migration sync`, and `migration set` — see [Remote schema drift detected](#remote-schema-drift-detected) for which one applies.

To run the same checks without deploying — plus migration file integrity (numbering, parseable contents, a `migrate.ts` or a recorded `--no-script` acknowledgment for every migration that requires a script, and no unresolved generated normalization review markers):

```bash
tailor tailordb migration validate
```

It reports issues per namespace, exits with a non-zero code when any check fails, and supports `--json` for machine-readable output.

With `--strict`, validation additionally fails when a migration not yet applied to the remote has data-loss warnings (see [Warnings and optional migration scripts](#warnings-and-optional-migration-scripts)) but neither a `migrate.ts` nor a recorded `--no-script` acknowledgment. The failure names the affected type and field and prints the exact command to record the acknowledgment.

To bypass both checks during deploy (not recommended outside of recovery scenarios):

```bash
tailor deploy --no-schema-check
```

### Example output

```
ℹ Found 2 pending migration(s) to execute.
ℹ Executing 2 pending migration(s)...
ℹ Using machine user: admin-machine-user for namespace 'tailordb'

✔ Migration tailordb/0002 completed successfully
✔ Migration tailordb/0003 completed successfully

✔ All migrations completed successfully.
✔ Successfully applied changes.
```

## Re-baselining a deployed migration history

`tailor tailordb migration rebaseline` collapses the complete history into a new `0000/schema.json` in the current migration format. It does not modify the deployed schema or data.

Before running it:

1. Apply the latest migration to every environment. The CLI verifies the connected workspace, but it cannot inspect other workspaces.
2. Commit or otherwise preserve the existing migration history. Files after `0000`, including `migrate.ts` and `db.ts`, disappear from the working tree; Git history retains committed files.
3. Make sure local type changes have been captured with `tailor tailordb migration generate`.

Then re-baseline one namespace:

```bash
tailor tailordb migration rebaseline --namespace tailordb
```

The command validates the migration files, verifies that replaying the latest migration exactly reproduces the local types, and checks that the connected workspace is at that latest migration with no schema drift. After confirmation, it replaces the local history with the reconstructed baseline, records a new migration history ID in both `0000/schema.json` and remote metadata, and resets the connected workspace's `sdk-migration` label to `0000`. Use `--yes` only after arranging the same operational preconditions in non-interactive automation.

Commit the resulting `migrations/` change before generating any new migrations. For another environment still carrying the exact checkpoint and history ID that the new baseline replaced, the next `tailor deploy` checks whether its remote schema exactly matches the new `0000`. If it does, deploy offers to reset the checkpoint to `0000` and move the environment to the new history ID before applying any later local migrations. A markerless history is eligible only for the first rebaseline, at the exact migration recorded as replaced. Any other checkpoint or history ID is rejected without changing remote metadata, even if its schema happens to match the baseline.

Partial squashing is not supported: re-baselining always replaces the full history for one namespace.

## `migration set` Semantics

`tailor tailordb migration set <N>` updates the `sdk-migration` label on the deployed namespace's metadata. **It does not modify any data or schema.** It only changes which migrations the next `apply` will consider pending. The command also aligns the remote migration history ID with the local baseline, removing a stale ID when the local history predates re-baselining.

The migration number is validated before anything is sent to the remote: it must be a 4-digit value (e.g. `0001`) or a bare integer (e.g. `1`) within 0–9999, and must exist in the working tree's migration history, which is itself validated (a gapped history is rejected). `0` is always accepted as the baseline (even when no migrations directory exists yet), provided the history passes validation.

| Movement                         | Effect on next `apply`                                                    | Effect on data                                                |
| -------------------------------- | ------------------------------------------------------------------------- | ------------------------------------------------------------- |
| Forward (e.g., `0001` → `0003`)  | Migrations `0002` and `0003` are skipped — they will not run.             | None.                                                         |
| Backward (e.g., `0003` → `0001`) | Migrations `0002` and `0003` become pending and will re-execute on apply. | None directly — but the re-executed scripts may rewrite data. |

Use cases:

* **Recovery from drift** — you investigated, manually fixed the remote, and want the SDK's bookkeeping to reflect reality.
* **Re-running a faulty migration** in a development workspace — set backward, fix `migrate.ts`, apply.
* **Skipping a migration** that you know was already applied out-of-band.

`migration set` does not perform a true rollback. To undo a schema/data change in production, write a new forward migration that reverses it (see [Rollback Strategy](#rollback-strategy)).

## `migration sync` Semantics

`tailor tailordb migration sync <N>` reconstructs the schema snapshot at migration `N` from the working tree's migration history and **overwrites the remote schema to match it**, then sets the `sdk-migration` label to `N` and aligns the remote migration history ID with the local baseline. Unlike `migration set`, it changes the remote schema as well as the bookkeeping. Like `set`, it never runs `migrate.ts` scripts itself — it only changes what the next `apply` considers pending:

| Movement                         | Effect on next `apply`                                                                    | Effect on data                                                                                              |
| -------------------------------- | ----------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------- |
| Backward (e.g., `0003` → `0001`) | Migrations `0002` and `0003` become pending and re-execute, including their `migrate.ts`. | Tables absent from snapshot `0001` are deleted along with their data; re-executed scripts may rewrite data. |
| Forward (e.g., `0001` → `0003`)  | Migrations `0002` and `0003` are skipped — their `migrate.ts` scripts will not run.       | Data the skipped scripts would have migrated stays as-is.                                                   |

Before anything is sent to the remote, `sync` verifies that replaying the full migration history reproduces the current local table definitions. If it does not — because migration files were edited and no longer match, or because a schema change has not been recorded with `migration generate` yet — the command fails without touching the remote. This means a rewritten migration history is validated before it can overwrite the deployed schema.

Because syncing backward causes already-applied scripts to re-execute on the next deploy, **write `migrate.ts` scripts to be idempotent** (see [Performance and Large Tables](#performance-and-large-tables) for resumable `where` clauses).

The main use case is recovering from drift after a `deploy --no-schema-check` from an older revision: instead of checking out that revision, run `migration sync <N>` to restore the remote to a known snapshot, then `tailor deploy` to apply the remaining migrations from the working tree.

## Team Workflow and CI/CD

### Branch coordination

Migration numbers are assigned sequentially, so two developers branching off the same point and each generating `0005` will collide. Conventions that work:

* **Don't generate migrations on long-lived feature branches.** Generate them just before merge, after rebasing onto main.
* **Resolve collisions by re-generating.** If your branch has `0005` but main now has `0005` from another PR, regenerate yours as `0006` — see [Resolving a migration number conflict](#resolving-a-migration-number-conflict).
* **Treat migration files as merge-conflict-prone.** They are committed JSON and TypeScript, so review them in PRs. The `diff.json` is the source of truth — if review focuses there, regenerating after rebase is straightforward.

### Resolving a migration number conflict

When your branch and main each generated the same number, merging or rebasing stops with an add/add conflict on `migrations/0005/diff.json`. Resolve it by re-generating your migration on top of main's:

1. **Save your script edits aside.** If you customized `0005/migrate.ts`, keep a copy before touching the directory — during a rebase, `git show ORIG_HEAD:migrations/0005/migrate.ts` prints the version from your pre-rebase branch tip.
2. **Take main's `0005/` directory in full.** Accept main's version of every conflicting file. Then check for files only your side added: if your migration has a `migrate.ts` and main's does not, that file never conflicts — it silently stays next to main's `diff.json`. Delete such leftovers explicitly.
3. **Finish the rebase or merge, then re-run `migration generate`.** With main's migration now part of local history, the diff is computed against the correct base — including main's changes — and your migration lands as the next number (`0006`).
4. **Port your script.** Copy the logic saved in step 1 into the newly scaffolded `0006/migrate.ts`. For a warning-tier change, `migration generate` does not scaffold a script — recreate it first with `tailor tailordb migration script 0006`.

**When a plain rename is enough.** If the two migrations touch disjoint tables and fields, renaming your directory to the next free number (keeping main's `0005/`) can be acceptable. Run `tailor tailordb migration validate` after the rename: if it reports a mismatch, the migrations were not disjoint — discard the rename and re-generate as above. A passing check covers only the schema history, not your script: `migrate.ts` now runs after main's migration, so confirm it does not read or write tables that migration touches — when in doubt, re-generate.

### CI / CD

* For non-interactive environments, pass `--yes` to `migration generate` and `--yes` to `apply`. `apply` runs migrations automatically when the `migrations/` directory is configured.
* Run `tailor tailordb migration validate` in CI to catch uncommitted migrations, broken migration files, unreviewed generated normalization logic, and remote schema drift before deploying. It exits with a non-zero code when validation fails and supports `--json`. Add `--strict` to also require an explicit acknowledgment (a `migrate.ts` or a recorded `--no-script` reason) for every pending migration that can drop data, so destructive changes cannot merge unnoticed.
* `tailor tailordb migration status` validates file-format compatibility across the full local history, compares its history ID with the deployed namespace, and shows applied and pending migrations for a human-readable comparison. Its exit code is non-zero on incompatible files, migration history mismatches, and remote read errors, so check the output.
* Avoid running migrations in parallel against the same workspace — there is no locking. Serialize deploys per environment.

### Resetting a deployed project

Use `tailor tailordb migration rebaseline` rather than combining `migration generate --init` with a manual checkpoint change. See [Re-baselining a deployed migration history](#re-baselining-a-deployed-migration-history) for the required cross-environment coordination and verification.

## Failure Recovery

If the pre-migration phase or `migrate.ts` fails:

* **The transaction rolls back** for that migration's script. Database changes the script made are undone.
* **The pre-migration schema changes are rolled back** to the prior checkpoint: tables that already existed are restored to their previous shape, and tables the migration newly introduced are dropped. The workspace is left at its prior checkpoint and prior schema — not half-applied.
* The whole `apply` aborts and the checkpoint label is not bumped. Subsequent migrations in the same run do not execute.

The rollback is best-effort per table; if reverting a table fails, a warning is logged and the original migration error is still reported.

After a failure:

1. Read the `Logs:` block in the apply output to find the cause.
2. Fix `migrate.ts` (or the data it depends on).
3. Re-run `tailor deploy`. The same migration runs again because its label was never bumped, and the prior-checkpoint schema is a clean baseline to retry against.

If a migration **succeeds in script** but its reversible **post-migration schema update** fails (rare; usually a constraint violation the script should have prevented), the SDK makes the same best-effort restoration to the prior-checkpoint schema. The script's committed data changes remain, so write migration scripts to tolerate re-execution.

The checkpoint is advanced only after the reversible post-migration schema updates succeed. If the checkpoint write reports an error, the SDK reads it back: a matching value is treated as committed. Any other observed value leaves the post-migration schema unchanged rather than risk rolling back a concurrent deployment; a value beyond the current migration confirms a concurrent deploy, while an older or missing value means the checkpoint must be repaired before retrying. If read-back also fails, the SDK likewise leaves the post-migration schema unchanged; verify the remote checkpoint before retrying.

Removed tables are deleted only after the checkpoint is committed. If that cleanup fails, the checkpoint remains at the new migration and the SDK fails closed: the leftover table is reported as remote schema drift on the next deploy. Remove the leftover GQL permission and table manually, verify the remote schema, and then retry. The SDK does not automatically ignore or delete a same-named remote table because it cannot distinguish failed cleanup from a table recreated after cleanup completed.

## Rollback Strategy

There is no automatic down-migration. To roll back a schema/data change in production, write a new forward migration that reverses the previous one. For example, to undo a `0005` that added a required `email` field:

1. Edit your table definitions to remove the field.
2. `migration generate --name "rollback 0005 email"` produces `0006` with a removal diff.
3. Apply.

In **development workspaces**, a quicker option is to fix `0005/migrate.ts` in place, run `migration set <previous>` to re-mark it pending, and apply. Do not do this on production — it confuses migration history across environments.

## Machine User and Permissions

Migration scripts execute server-side under a machine user identity. The CLI selects the user in this priority order:

1. `db.<namespace>.migration.machineUser` if set in `tailor.config.ts`.
2. The first entry in `auth.machineUsers` otherwise.

The CLI logs the selected user before running scripts (`Using machine user: ...`).

**Permissions required**

The machine user needs read/write access to every table the migration script touches. If your migrations alter data across multiple tables, the simplest path is to give the migration user broad access (e.g., an `ADMIN` role) and restrict day-to-day machine users separately. If the user lacks permission, the script fails with a permission error in `Logs:`.

If you see `No machine user available for migration execution`, either:

* Add `machineUsers: { ... }` to your auth config and `tailor deploy` it, or
* Set `migration.machineUser` to an existing machine user name in the db config.

## Multi-Namespace Coordination

If your project defines multiple TailorDB namespaces (`db: { ns1: { ... }, ns2: { ... } }`), each has its own `migrations/` directory and its own migration label. During `apply`:

* Migrations are grouped by namespace and executed namespace by namespace.
* Within a namespace, migrations run sequentially in number order.
* There is no cross-namespace ordering guarantee. Do not write a migration in `ns1` that depends on data produced by a migration in `ns2` running first.
* Each namespace can specify its own `migration.machineUser`.

## Performance and Large Tables

The migration script runs in a single transaction. For tables with many rows:

* Prefer set-based SQL (`updateTable(...).set(...).where(...)`) over per-row loops.
* If a per-row loop is unavoidable, batch by primary key range. Avoid `OFFSET`-based pagination — it scans previously-seen rows on every page.
* Long-running transactions can hit platform timeouts and hold locks. For very large backfills, consider splitting the work across multiple migrations, each operating on a subset.
* Add `LIMIT` and resumability (idempotent `where` clauses) so a re-run after a transient failure converges.

## Testing Migrations Locally

### Unit-testing migrate.ts

`main` is a plain function, so you can unit-test it with Vitest before the first deploy ever runs it. `createKyselyMock` from `@tailor-platform/sdk/vitest` compiles queries to the same SQL as the deployed migration, so a test verifies the exact statements the script issues — SQL, parameters, and order. Type the mock with the `Database` interface exported from the generated `db.ts`.

Scaffold a ready-to-fill test next to the script with:

```bash
tailor tailordb migration script 0005 --with-test
```

When `migrate.ts` already exists (the usual case for breaking changes, where `migration generate` creates it), the command adds only `migrate.test.ts`. Or write the test by hand:

```typescript
// migrations/0005/migrate.test.ts
import { createKyselyMock } from "@tailor-platform/sdk/vitest";
import { describe, expect, test } from "vitest";
import type { Database } from "./db";
import { main } from "./migrate";

describe("0005 add required email", () => {
  test("backfills null emails", async () => {
    const mock = createKyselyMock<Database>();

    await mock.withTx((trx) => main(trx));

    expect(mock.updates).toHaveLength(1);
    expect(mock.updates[0]?.updateValues()).toEqual({ email: "unknown@example.com" });
    expect(mock.updates[0]?.sql).toContain('where "email" is null');
  });
});
```

Stage the rows each query returns with `mock.enqueueResult(...)` or `mock.setQueryResolver(...)` when the script reads before writing; call `main(trx, { env: { ... } })` when the script takes a `MigrationContext`. See [Kysely-layer mock](../testing.md#kysely-layer-mock-createkyselymock) for the full mock API.

These tests need no platform connection and no `tailor-runtime` environment — they run in a plain Vitest setup. Vitest's default `include` pattern already picks up `migrations/**/migrate.test.ts`; if your config narrows `include`, add the migrations directory. The test file is ignored by `tailor deploy` and never ships to the platform.

### Executing migrate.ts against a local Postgres (PGlite)

A statement-level test verifies what the script issues, not what it does to data (e.g., whether a `where` clause matches the rows you intended). To run `main` against real rows locally, back Kysely with `@electric-sql/pglite` — an in-memory PostgreSQL — via `createKyselyPGlite` from `@tailor-platform/sdk/vitest`:

```bash
npm install -D @electric-sql/pglite
```

Create the tables the script touches (matching the shape in the generated `db.ts`), stage rows, then run the script in a transaction:

```typescript
// migrations/0005/migrate.pglite.test.ts
import { PGlite } from "@electric-sql/pglite";
import { sql } from "@tailor-platform/sdk/kysely";
import { createKyselyPGlite } from "@tailor-platform/sdk/vitest";
import { afterAll, beforeAll, describe, expect, test } from "vitest";
import type { Database } from "./db";
import { main } from "./migrate";

const db = createKyselyPGlite<Database>(new PGlite());

beforeAll(async () => {
  await sql`
    CREATE TABLE "User" (
      "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      "name" text NOT NULL,
      "email" text
    )
  `.execute(db);
});

afterAll(async () => {
  await db.destroy();
});

describe("0005 add required email", () => {
  test("backfills null emails and keeps existing ones", async () => {
    await sql`
      INSERT INTO "User" ("name", "email")
      VALUES ('a', NULL), ('b', 'b@example.com')
    `.execute(db);

    await db.transaction().execute((trx) => main(trx));

    const rows = await db.selectFrom("User").select(["name", "email"]).orderBy("name").execute();
    expect(rows).toEqual([
      { name: "a", email: "unknown@example.com" },
      { name: "b", email: "b@example.com" },
    ]);
  });
});
```

Two caveats keep this from replacing a scratch workspace:

* PGlite runs full PostgreSQL, while TailorDB supports [a subset of it](/guides/function/accessing-tailordb#supported-sql-queries) — a statement that passes here can still be rejected on deploy.
* The `CREATE TABLE` statements are yours, so they can drift from the schema the platform actually has.

### Beyond unit tests

A unit test verifies which statements the script issues; a PGlite test verifies what they do to the rows you staged. Neither runs against your actual data. To cover that:

* Run `migration generate` on a clean working copy first, review `diff.json`, then run again after editing tables to ensure the diff matches what you intended.
* For non-trivial migrations, apply against a scratch workspace before promoting to staging or production.

## Environment-Specific Strategies

A migration script is a function — branching on environment (e.g., to skip a backfill in dev) is just normal TypeScript. For environment awareness, use the `env` values defined in `defineConfig({ env })`, exposed via the optional second argument `{ env }: MigrationContext` (see [Migration Script Anatomy](#migration-script-anatomy)). These are resolved at deploy time and inlined into the bundle. Do not read `process.env` inside `migrate.ts` — Node-side environment access is unavailable at runtime; only the injected `env` (and the data itself) reflect the target environment.

For genuinely different schemas across environments, prefer separate workspaces with the same migration history rather than divergent `migrations/` directories.

## Troubleshooting

### Remote schema drift detected

**Cause:** Remote schema doesn't match what the migration history says it should be.

**Resolution:**

1. `tailor tailordb migration status` to see local vs remote.
2. Compare with teammates — has someone applied different migrations?
3. If remote was changed manually, decide whether to update local migrations to match or to use `migration set <N>` to align bookkeeping.
4. To force the remote schema back to a known snapshot, use `migration sync <N>` (see [`migration sync` Semantics](#migration-sync-semantics)).
5. As a last resort in non-production environments, `--no-schema-check` skips both checks. Do not use this as a routine workaround.

### "Invalid schema snapshot" or "Invalid migration diff" error

**Cause:** A `schema.json` or `diff.json` file in the `migrations/` directory is corrupted or does not match the expected structure. Merge conflicts left in these files are a common cause.

**Resolution:**

1. Read the error message — it includes the file path and the offending field.
2. Restore the file from version control (`git checkout -- <path>`), or regenerate migration files with `migration generate` / `migration script`.
3. Do not hand-edit `schema.json` or `diff.json`; they are managed by the CLI.

### "Unsupported migration file format version" error

**Cause:** A `schema.json` or `diff.json` file is older or newer than the format versions supported by the installed SDK.

**Resolution:** Follow the ordering in the error message. For an older history, restore an SDK version that can read every file, run `migration rebaseline`, commit and deploy the new baseline everywhere, and then upgrade. For a file produced by a newer SDK, upgrade the SDK that is reading it. Do not hand-edit the version field.

### "No machine user available for migration execution"

**Cause:** Neither `migration.machineUser` is set nor are there any machine users in `auth.machineUsers`.

**Resolution:** Add a machine user to auth, apply auth changes, then re-run.

### "Machine user not found"

**Cause:** `migration.machineUser` references a name that doesn't exist in the deployed auth config.

**Resolution:** Either add the machine user to `auth.machineUsers` and apply, or change `migration.machineUser` to a valid name.

### Migration script execution fails

**Cause:** Runtime error in your `migrate.ts`, a permission error from the machine user, or a constraint violation when post-migration tightens tables.

**Resolution:** Read the `Logs:` block. Fix the script or the data assumption it relies on, and re-run `tailor deploy`. The label is not bumped on failure, so the same migration retries.

### `migrate.ts` not found for a migration that needs one

**Cause:** `diff.requiresMigrationScript` is true but `migrate.ts` is missing from the migration directory.

**Resolution:** Restore the file from version control, or create it with `tailor tailordb migration script <N> --namespace <namespace>`. If the migration intentionally needs no data transformation, record that decision with `tailor tailordb migration script <N> --namespace <namespace> --no-script --reason "<why no data migration is needed>"` instead.