CLOSED 2026-09-03. The backfill is no longer a manual step: it runs as the first statement of migration 20260903100000_composition_zones_drop_legacy, immediately before the instances column is dropped, so no environment can drop ahead of its data. Nothing below needs running by hand — it is kept as the record of what the shape conversion was and why it was done this way.

Purpose: derive canonical screen_composition.zones from legacy instances for rows written before ADR 0027 rollout (where zones IS NULL).

When to run: once per environment, before dropping the legacy instances column at the end of the ADR 0027 soak window (~30 days after 2026-05-27). Not urgent before then — the frontend hydrate path falls back to instances via migrateScreen while both columns coexist.

Idempotent: the WHERE zones IS NULL OR zones = 'null'::jsonb guard means re-running is safe (no double-migration).

Status

Why it became a Drizzle migration after all

A backfill migration was prototyped in May and backed out: the worktree then had an uncommitted 0012_premium_jazinda (entry_mode) migration from parallel work, and sequencing on top of uncommitted schema work would have tangled the journal. That blocker is long gone, and the manual-script approach had a worse failure mode — the drop and the backfill were two separate human steps, and the runbook table above shows prod's step sitting unticked for three months. Coupling them in one migration makes the ordering impossible to get wrong.

Procedure

Prerequisite: screen_composition.zones column exists (migration 0011 applied).

Run from tools/migrate/ (has @neondatabase/serverless installed):

cd tools/migrate
DATABASE_URL='<target connection string>' node --input-type=module -e "
import { Pool, neonConfig } from '@neondatabase/serverless';
import ws from 'ws';
neonConfig.webSocketConstructor = ws;
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const sql = \`
UPDATE screen_composition
SET zones = jsonb_build_object(
  'sidebarLeft', null,
  'header', null,
  'body', jsonb_build_object(
    'id', 'inst-bf-' || substr(md5(random()::text), 1, 8),
    'componentId', 'layout-grid:container',
    'props', jsonb_build_object('layout', 'vertical', 'padding', '0px', 'gap', '0px'),
    'children', jsonb_build_object('children', COALESCE(instances, '[]'::jsonb))
  ),
  'footer', null,
  'sidebarRight', null
)
WHERE zones IS NULL OR zones = 'null'::jsonb\`;
const r = await pool.query(sql);
console.log('Backfilled rows:', r.rowCount);
const check = await pool.query(\"SELECT COUNT(*)::int AS n FROM screen_composition WHERE zones IS NULL OR zones = 'null'::jsonb\");
console.log('Remaining null/empty zones:', check.rows[0].n);
await pool.end();
"

Expected output: Remaining null/empty zones: 0.

The migrated shape mirrors migrateInstancesToZones in apps/api/src/deploy.ts and migrateScreen in packages/shared/src/composition.ts: legacy flat instances become zones.body.children.children, all other zones null.

After backfill (eventual)

Once prod backfill is verified and the soak window has elapsed:

1. Confirm no API writes with { instances }-only shape (check logs / there are no third-party clients)
2. Drop the instances column via a new Drizzle migration
3. Remove the legacy-fallback branches in apps/api/src/index.ts (PUT/GET compositions), apps/api/src/deploy.ts (migrateInstancesToZones), and persistence-loader.tsx hydrate
4. Update ADR 0027 status to "contract phase complete"