Localizing an existing site

This is a manual, one-time migration for an existing Payload site that is changing fields from unlocalized to localized. New sites configured with locales before content is created do not need it. The helper currently supports Payload 3.85's SQLite and PostgreSQL adapters; it does not support MongoDB or automatically rewrite a generated schema migration.

Take a verified, restorable backup and rehearse the complete procedure against a staging copy. Stop writes while the production migration and verification run. For SQLite, checkpoint WAL and preserve the database together with any -wal and -shm files. For PostgreSQL, use a consistent dump or storage snapshot.

Why the plan is explicitLink to this section

Payload does not use one storage shape for every localized field:

• scalar, group, relationship, upload, global, and version scalar values move into generated *_locales tables;
• localized arrays and blocks remain in their row tables and gain an _locale column, including their draft/version row tables; and
• version base tables also change independently as Payload adds localization metadata.

Consequently, Systhema does not guess tables from names or copy every column it finds. You must review Payload's generated migration and declare every data move in a LocalizationMigrationPlan. This avoids silently omitting nested/version data or localizing a consumer field that was meant to stay shared.

Each localeTable operation maps a target column to its legacy source column. Each rowLocale operation fills only a null locale discriminator. A representative plan is:

import type { LocalizationMigrationPlan } from '@systhemaui/payload'

export const localizationPlan = {
  operations: [
    {
      kind: 'localeTable',
      sourceTable: 'pages',
      targetTable: 'pages_locales',
      columns: {
        title: 'title',
        slug: 'slug',
        seo_description: 'seo_description',
        related_id: 'related_id',
        _status: '_status',
      },
    },
    { kind: 'rowLocale', table: 'pages_sections' },
    { kind: 'rowLocale', table: 'pages_blocks_hero' },
    {
      kind: 'localeTable',
      sourceTable: '_pages_v',
      targetTable: '_pages_v_locales',
      columns: {
        version_title: 'version_title',
        version_slug: 'version_slug',
        version_seo_description: 'version_seo_description',
        version_related_id: 'version_related_id',
        version__status: 'version__status',
      },
    },
    { kind: 'rowLocale', table: '_pages_v_version_sections' },
    { kind: 'rowLocale', table: '_pages_v_blocks_hero' },
    {
      kind: 'localeTable',
      sourceTable: 'settings',
      targetTable: 'settings_locales',
      columns: { title: 'title' },
    },
    { kind: 'rowLocale', table: 'settings_links' },
  ],
} satisfies LocalizationMigrationPlan

_status and version__status are in that list because per-locale publishing is on by default once locales is configured, so the publication state moves into the locale tables along with the content. Copying the one shared value into every locale is the right answer on this path, because the site had one publication history and every locale starts from the state that history left it in. The separate systhemaLocalizeStatus helper is for the other path, where locale tables already exist and each locale's status has to be derived from the version history. Do not use it here. Omit both columns if you set localization: { localizeStatus: false }.

These names are examples, not a Systhema default. Include the generated upload locale table, globals, collection/global versions, every localized array/block table, and consumer collections as applicable. Do not include fields that remain shared. A rename or custom database suffix must be written explicitly in the plan.

Prepare the generated migrationLink to this section

1. Enable the final Systhema locale configuration and field policy in source control. Confirm which locale receives existing content.
2. Run pnpm payload migrate:create with the production adapter.
3. Review the generated SQL and split its up migration into three ordered phases:
   • additive schema: create the exact Payload-generated locale tables and add nullable _locale columns to localized array/block row tables;
   • data seed: call seedSysthemaLocalization with the reviewed plan; and
   • destructive/final schema: make row locale columns non-null, add final constraints/indexes, and only then remove legacy scalar columns or obsolete structures.
4. Review the resulting migration again. The additive phase must retain both the old and new data shapes at the same time. SQLite table rebuilds and PostgreSQL enum/sequence ownership require particular care; preserve the exact structures Payload generated.

Do not run the unedited generated migration first. It may drop the legacy columns before they can be copied. Systhema's helper copies data only — it deliberately does not create, alter, or drop schema.

Derive the plan by diffing, not by reading the config. Point the app at an empty database with the final configuration and let it create the schema; that is a reference of exactly what the new shape should be. Diff the live database against it for every generated object — tables, columns, indexes, constraints, foreign keys, sequences, enums and locale discriminators — and turn each difference into an operation or a line of DDL. information_schema and PRAGMA queries are adapter-specific inputs to that comparison; a column-only diff misses the constraints Payload expects. A plan derived this way cannot drift from the field policy, and it surfaces the structural columns — array/blocks _locale discriminators, _rels.locale, and the _v_version_* twins — that no list of FIELDS mentions.

Ship add-and-copy separately from the drop. The additive DDL and the data copy can go out in one deploy and the destructive cleanup in a later one. Both schema shapes then coexist while the site is serving traffic and no request ever sees a half-migrated table. A code revert is a safe rollback only until localized writes begin; after that, roll forward with a fix or restore a verified backup.

Call the helper inside Payload's transactionLink to this section

Payload supplies MigrateUpArgs.db as the current Drizzle migration transaction. Pass that exact handle to the adapter-specific bridge. The helper does not start a nested transaction and never reaches through payload.db.drizzle.

SQLite:

import type { MigrateDownArgs, MigrateUpArgs } from '@payloadcms/db-sqlite'
import { sql } from '@payloadcms/db-sqlite'
import {
  createPayloadSQLiteLocalizationSqlClient,
  localizationDownIsUnsafe,
  seedSysthemaLocalization,
} from '@systhemaui/payload'

import systhemaConfig from '../../systhema.config'
import { localizationPlan } from './localization-plan'

export async function up({ db }: MigrateUpArgs): Promise<void> {
  // 1. Payload-generated additive SQL.

  const report = await seedSysthemaLocalization(
    createPayloadSQLiteLocalizationSqlClient(db, sql),
    'sqlite',
    localizationPlan,
    {
      locales: systhemaConfig.locales,
      acknowledgeDefaultLocale: 'en',
    },
  )
  console.log('[systhema] localized legacy rows:', report.rows)

  // 3. Payload-generated constraints and destructive cleanup.
}

export async function down(_args: MigrateDownArgs): Promise<void> {
  localizationDownIsUnsafe()
}

PostgreSQL uses the same plan and ordering:

import type { MigrateUpArgs } from '@payloadcms/db-postgres'
import { sql } from '@payloadcms/db-postgres'
import {
  createPayloadPostgresLocalizationSqlClient,
  seedSysthemaLocalization,
} from '@systhemaui/payload'

import systhemaConfig from '../../systhema.config'
import { localizationPlan } from './localization-plan'

export async function up({ db }: MigrateUpArgs): Promise<void> {
  // Additive SQL first.
  await seedSysthemaLocalization(
    createPayloadPostgresLocalizationSqlClient(db, sql),
    'postgres',
    localizationPlan,
    {
      locales: systhemaConfig.locales,
      acknowledgeDefaultLocale: 'en',
      postgresSchema: 'public',
    },
  )
  // Constraints and destructive cleanup last.
}

Pass the canonical SysthemaLocales object returned by defineSysthemaLocales() in the default-exported systhema.config.ts (adjust the relative import for the project's migration directory). The explicit acknowledgeDefaultLocale guard must exactly repeat its default locale because choosing the wrong one is a semantic data error that SQL cannot detect.

Safety, reruns, and verificationLink to this section

Before any write, the helper preflights every declared source/target table and column. For each parent it refuses an existing default-locale row whose declared values differ from legacy data. It never overwrites a translation. Matching rows are skipped, missing locale rows are inserted, and only null array/block locale values are filled. This makes an interrupted migration resumable and a completed seed idempotent while the legacy columns still exist.

Treat the reviewed plan as immutable once a seed has run. Idempotence is keyed by the parent row, so adding more columns to an already-seeded localeTable operation does not backfill those new columns on a rerun; create and review a separate follow-up data migration instead. Keep every additive _locale column nullable and without a default until after the helper runs. A generated NOT NULL DEFAULT ... column already contains no null rows for the helper to seed and can conceal an unreviewed locale assignment.

Set dryRun: true to run schema/conflict checks and obtain operation row counts without writes. Rehearse this on a staging copy; do not mark a production migration as applied merely to obtain a dry-run report.

The Payload migration runner owns commit and rollback. If the helper or later SQL throws, its writes roll back with the surrounding migration transaction. Once the migration succeeds and editors can change localized content, an automatic down migration is destructive and ambiguous. Restore the verified pre-migration backup instead; localizationDownIsUnsafe() makes that policy explicit.

Before reopening writes, verify at least:

• collection and global counts, including drafts and version histories;
• default-locale scalar/group values, nulls, empty strings, slugs, and routes;
• array/block order, IDs, paths, upload/relationship IDs, and version row content;
• representative Admin and frontend reads for each collection/global; and
• regenerated Payload types and import map after the final schema is in place.

Keep the reviewed plan with the migration. It documents exactly which existing consumer-owned data was intentionally localized and is the audit trail for a retry or restore.