Instruction file imported from genesis-ai-dev/langquest (.cursor/rules/app-database-migration.mdc). Copyright stays with the author.

Database: Drizzle Schema Migration Process

⛔ Hard rule: version bumps come in pairs

APP_SCHEMA_VERSION and get_schema_info() change together, or neither changes. There is no third option.

Run this check on every migration diff:

Default = Path A. If you cannot point to a specific client code change (a Drizzle column the app reads/writes, a sync-rules change clients must satisfy, a renamed table the client queries), do not touch APP_SCHEMA_VERSION or get_schema_info(). Incorrectly bumping either blocks sync for users on current app builds.

⛔ Every APP_SCHEMA_VERSION bump also requires a registered local migration in db/migrations/ — even a no-op. findMigrationPath() (db/migrations/index.ts) demands a registered migration for every version hop. If you bump the version without one, every user with existing local records is sent to the migration screen, which then fails with "No migration path found" — they are permanently stuck until a fixed build ships. If no data transformation is needed, create a no-op migration whose migrate() does nothing (the framework bumps _metadata.schema_version automatically); see db/migrations/2.4-to-2.5.ts for the pattern.

Real incidents this prevents. 20260529120000_rename_project_language_suggestion_to_languoid.sql shipped a get_schema_info() bump to 2.5 / min 2.4 while APP_SCHEMA_VERSION stayed at 2.3 — blocked sync for every user on the current build and required 20260530120000_revert_schema_info_to_2_3.sql to undo. 20260520210000_invite_bounce_type_and_reason.sql made the same mistake (server-only bounce_type/bounce_reason columns, bumped to 2.4 anyway).

Decision: Path A or Path B?

Path A — server-only: Postgres changes the client doesn't see. Examples: nullable columns the app never reads, RLS, triggers, indexes, server functions, backfills, invite bounce-tracking columns. Only output: a Supabase SQL migration. Do not edit db/constants.ts, the Drizzle schemas, or db/migrations/.

Path B — client schema change: App code or the client sync contract changes. Examples: client reads/writes a new column, required field, rename/drop a column the client uses, sync-rules change old clients can't satisfy. Coordinate steps 1–6 below.

PowerSync is schemaless

PowerSync stores synced data as schemaless JSON; the client Drizzle schema is just a view on top.

"Updating this client-side schema is immediate when the new version of the app runs, with no client-side migrations required." — PowerSync: Implementing Schema Changes

Practical consequences:

• Adding a nullable column to a synced table does not need a local SQLite migration. Existing records read undefined, equivalent to NULL.
• Local migrations are needed only when client code actively reads/writes a column with non-null semantics, when removing/renaming a column the client uses, or when existing local data needs transformation/backfill.
• Both synced tables and *_local tables use the same JSON-storage architecture — the difference is sync scope, not storage mechanism.

Path A: Server-only Supabase migration

-- Migration: Add server_only_flag to invite (server-side delivery tracking)
-- NO schema version bump — server-only, nullable, not in client Drizzle schema

alter table public.invite
  add column if not exists server_only_flag text;

comment on column public.invite.server_only_flag is 'Used by Resend webhook; not synced to client.';

Do not include create or replace function public.get_schema_info() in this migration.

Path B: Step-by-step (client schema change)

1. Modify shared schema definitions

Edit db/drizzleSchemaColumns.ts — its functions are reused by both drizzleSchema.ts (synced tables) and drizzleSchemaLocal.ts (*_local tables).

export function createAssetTable(source: TableSource, refs: {...}) {
  return tableCreator(source)('asset', {
    ...getBaseColumns(source),
    new_field: text(),
  });
}

2. Bump APP_SCHEMA_VERSION + update get_schema_info()

These two move together (see hard rule). Format: MAJOR.MINOR.

• Minor bump (2.0 → 2.1): additive client changes (new columns/tables the app uses).
• Major bump (2.0 → 3.0): destructive client changes (removed/renamed columns, type changes, dropped tables).

// db/constants.ts
export const APP_SCHEMA_VERSION = '2.1';

-- in your Supabase migration
create or replace function public.get_schema_info()
returns jsonb
language sql
security invoker
set search_path = public
as $$
  select jsonb_build_object(
    'schema_version', '2.1',
    'min_required_schema_version', '2.0',
    'notes', 'Clients must be at least 2.0 to sync.'
  );
$$;

min_required_schema_version lets you ship the DB migration before the app update is approved: clients with schema_version >= min_required_schema_version keep syncing. After app adoption, raise the minimum to phase out older versions. If unset, falls back to exact-match. See db/schemaVersionService.ts.

3. Update db/drizzleSchemaLocal.ts

drizzleSchemaLocal.ts is hand-maintained. When drizzleSchemaColumns.ts changes, update the corresponding *_local table:

export const asset_local = createAssetTable('local', {
  language: language_local,
  project: project_local,
  profile: profile_local
});

The third argument adds local-only columns (not synced to cloud, no Supabase migration needed):

export const asset_local = createAssetTable(
  'local',
  { language: language_local, project: project_local, profile: profile_local },
  {
    local_cache_flag: text().default('false'),
    offline_metadata: text({ mode: 'json' }).$type<Record<string, unknown>>()
  }
);

For local-only columns: skip the Supabase migration, but still create a local migration if you're adding the column to an existing table. Make sure the name doesn't collide with cloud schema columns.

4. Create the Supabase migration

File: supabase/migrations/YYYYMMDDHHmmss_short_description.sql. See .cursor/rules/supabase/create-migration.mdc.

-- Migration: Add new_field to asset table
-- Path B client change — bumps APP_SCHEMA_VERSION 2.0 → 2.1 in same release

alter table asset add column new_field text;
comment on column asset.new_field is 'Description of the new field';

update asset set new_field = 'default_value' where new_field is null;

For destructive changes, copy data before dropping the old column:

alter table asset add column new_field text;
update asset set new_field = deprecated_field where deprecated_field is not null;
alter table asset drop column deprecated_field;

5. Create the local migration (always, when the version bumps)

A registered migration file is always required when APP_SCHEMA_VERSION bumps (see hard rule above) — the only question is whether it transforms data or is a no-op.

Data transformation needed for: local-only columns on existing tables; synced columns the client actively reads/writes with non-nullable semantics; data backfills/transformations on existing local records. A no-op migration suffices for nullable columns the client doesn't actively use or schema-only changes (PowerSync handles those automatically; see db/migrations/2.4-to-2.5.ts).

File: db/migrations/MAJOR_MINOR-to-MAJOR_MINOR.ts (e.g., 2.0-to-2.1.ts).

import type { Migration } from './index';
import { addColumn } from './utils';

export const migration_2_0_to_2_1: Migration = {
  fromVersion: '2.0',
  toVersion: '2.1',
  description: 'Add new_field to asset_local table',

  async migrate(db, onProgress) {
    onProgress?.(1, 1, 'Adding new_field column');
    await addColumn(db, 'asset_local', 'new_field TEXT DEFAULT NULL');
  }
};

Rules for local migrations:

• Only touch *_local tables. Synced tables are transformed server-side via RPC when local data uploads — see "Server-side RPC transforms" below.
• Idempotent — safe to re-run.
• Destructive pattern: add new column → copy/transform data → drop old column. Use addColumn, copyColumn, transformColumn, dropColumn from db/migrations/utils.ts.
• Never delete user data — transform in place.

6. Register the local migration

// db/migrations/index.ts
import { migration_2_0_to_2_1 } from './2.0-to-2.1';

export const migrations: Migration[] = [
  migration_0_0_to_1_0,
  migration_1_0_to_2_0,
  migration_2_0_to_2_1,
];

Common mistakes

-- ❌ WRONG: server-only column with a version bump — blocks all current app users
alter table invite add column bounce_reason text;
create or replace function public.get_schema_info() ... 'schema_version', '2.4' ...;

-- ✅ RIGHT: server-only — no get_schema_info()
alter table invite add column bounce_reason text;
comment on column invite.bounce_reason is 'Set by Resend webhook; server-only.';

// ❌ WRONG: bumped APP_SCHEMA_VERSION for a column the app doesn't read/write
export const APP_SCHEMA_VERSION = '2.4';

// ✅ RIGHT: leave it alone; only the Postgres migration changes
export const APP_SCHEMA_VERSION = '2.3';

Migration utilities

From db/migrations/utils.ts:

• addColumn(db, table, definition) — add a column
• renameColumn(db, table, oldName, newName) — SQLite-safe rename
• dropColumn(db, table, column) — SQLite 3.35+
• copyColumn(db, table, src, dest) — copy data between columns
• transformColumn(db, table, column, expr, where?) — SQL-expression transform
• updateInBatches(db, table, query, where, batchSize, progress?) — batched updates for large datasets
• updateMetadataVersion(db, version) — updates _metadata.schema_version (called automatically)

Testing checklist

• Confirmed Path A vs Path B — server-only migrations don't touch get_schema_info() or APP_SCHEMA_VERSION
• Tested with realistic data
• Tested migration chain (1.0→1.1→1.2) and direct (1.0→1.2) — Path B only
• Verified _metadata.schema_version is correct — Path B only
• Verified only *_local tables were touched (synced tables go through server RPC)
• Checked performance with large datasets
• Tested on iOS and Android if relevant

Server-side RPC transforms (uploads from older clients)

Separate concern from the schema-migration steps above. Use these when synced tables need data transformed at upload time so older clients can keep writing — e.g., enriching records with fields the new schema requires, splitting one table into many, or backfilling derived values from an older field.

Pattern: a vN_to_vN+1(p_ops mutation_op[], p_meta jsonb) function rewrites operations missing the new field, then apply_table_mutation / apply_table_mutation_transaction chain transforms based on the client's schema_version.

CREATE OR REPLACE FUNCTION public.v1_to_v2(
  p_ops public.mutation_op[],
  p_meta jsonb
) RETURNS public.mutation_op[]
LANGUAGE plpgsql AS $$
DECLARE
  out_ops public.mutation_op[] := '{}';
  op public.mutation_op;
  v_record jsonb;
BEGIN
  FOREACH op IN ARRAY p_ops LOOP
    IF lower(op.table_name) = 'table_name' THEN
      v_record := op.record;
      IF (v_record->>'new_field') IS NULL AND (v_record->>'old_field') IS NOT NULL THEN
        v_record := v_record || jsonb_build_object('new_field', /* derive from old_field */);
      END IF;
      out_ops := out_ops || (row(op.table_name, op.op, v_record))::public.mutation_op;
    ELSE
      out_ops := out_ops || op;  -- passthrough
    END IF;
  END LOOP;
  RETURN out_ops;
END;
$$;

Chain in mutation handlers based on the client's schema version:

-- in apply_table_mutation / apply_table_mutation_transaction
DECLARE
  v_meta text := coalesce(
    p_client_meta->>'schema_version',  -- new format
    p_client_meta->>'metadata',         -- legacy format
    '0'                                 -- default to v0
  );
BEGIN
  IF v_meta = '0' OR v_meta LIKE '0.%' THEN
    ops := public.v0_to_v1(ops, p_client_meta);
    ops := public.v1_to_v2(ops, p_client_meta);
  ELSIF v_meta = '1' OR v_meta LIKE '1.%' THEN
    ops := public.v1_to_v2(ops, p_client_meta);
  END IF;
  -- v2+ passes through unchanged
END;

Key principles:

• Only transform older versions; passthrough current. Don't transform v2 data with v1_to_v2.
• Don't modify ops in place — emit new mutation_op rows.
• Use lookup tables (e.g., language_languoid_map) to cache expensive derivations; back them with SECURITY DEFINER helpers like get_or_create_languoid_for_language(uuid).
• raise log aggressively for debugging.
• Don't use RPC transforms for local-only changes — those are client-side only.
• Transforms only run if you wire them into the mutation handlers above.

References

• Example transform: supabase/migrations/20251128120000_add_v1_to_v2_transform.sql
• Mutation handlers: apply_table_mutation, apply_table_mutation_transaction
• Mutation op type: public.mutation_op (table_name, op, record)
• Local migration helpers: db/migrations/utils.ts
• Local migration README: db/migrations/README.md
• Example local migration: db/migrations/EXAMPLE_1.0-to-1.1.ts
• Schema version constant: db/constants.ts (APP_SCHEMA_VERSION)
• Client-side schema version service: db/schemaVersionService.ts
• Supabase migration rules: .cursor/rules/supabase/create-migration.mdc