schema-drift.ts

// Copyright (c) 2025 ObjectStack. Licensed under the Apache-2.0 license.

/**
 * Managed-datasource schema drift (issue #2186).
 *
 * The driver's `initObjects` sync is *additive-only*: it creates missing
 * tables and adds missing columns, but never alters or drops existing ones.
 * So a non-additive metadata change (relax `storage.notNull`, change a type/length,
 * drop or rename a field) silently diverges from an existing database — the
 * served metadata says one thing and the physical column enforces another.
 *
 * This module is the single source of truth for *detecting* that divergence
 * (metadata is authoritative on a `managed` datasource) and for *categorising*
 * each divergence by how dangerous it is to reconcile:
 *
 *   - `safe`         — loosening that cannot lose data and cannot fail:
 *                      relax NOT NULL → NULL, widen a varchar. Applied
 *                      automatically by dev auto-reconcile (P2).
 *   - `needs_confirm`— a change a human should eyeball but that does not
 *                      destroy data (e.g. a non-narrowing type change).
 *   - `destructive`  — drops or tightenings that can lose data or fail:
 *                      drop an orphaned column, narrow a varchar, add a
 *                      NOT NULL constraint over possibly-null data. Only
 *                      applied by `os migrate apply --allow-destructive`.
 *
 * The detector reuses {@link SchemaDiffEntry} (the same shape the external /
 * federated validator emits, ADR-0015 §5.2) so CLI / Studio / audit can render
 * managed and external drift uniformly.
 */

import { createHash } from 'node:crypto';

import {
  isAppResolvedDefaultToken,
  isUniqueDeclared,
  STRUCTURED_JSON_TYPES,
  FILE_REFERENCE_TYPES,
  MULTI_OPTION_TYPES,
  isMultiValueField,
} from '@objectstack/spec/data';
import type { SchemaDiffEntry } from '@objectstack/spec/shared';

// ───────────────────────────────────────────────────────────────────────
// Unique-scope vocabulary (ADR-0120)
// ───────────────────────────────────────────────────────────────────────

/**
 * Sentinel naming the NULL-organization ("platform") bucket — ADR-0120 D3.
 *
 * Every organization-scoped unique index materializes its organization key
 * part as `COALESCE(organization_id, '__global__')` instead of the raw column:
 * SQL UNIQUE is NULL-distinct, so the raw column enforced NOTHING on rows
 * whose organization is NULL — which is every row on a single-tenant stack
 * (#5030). The COALESCE folds all NULL-organization rows into one bucket,
 * unique among themselves, without touching the other rows.
 *
 * Three invariants, all deliberate (ADR-0120 D3, maintainer-resolved):
 *  - **Storage stays NULL.** Only the index folds NULL into the bucket; a
 *    `WHERE organization_id = '__global__'` matches nothing, by design.
 *  - **The word is the platform's existing name for this bucket** — the
 *    autonumber sequence table keys global rows by the same sentinel
 *    (`SqlDriver`'s `GLOBAL_TENANT`), so a constraint-violation error reading
 *    `(__global__, a@b.com)` says "platform bucket", not "corrupt data".
 *  - **The token is reserved**: an organization id may never equal it
 *    (guarded at the organization-creation seam in plugin-auth).
 *
 * Since ADR-0131 D1 the tenant column is NOT NULL, so new DDL keys the plain
 * column and no longer emits this form. The COALESCE form stays READ:
 * every database an earlier release built keeps those indexes, and the
 * differ takes them as equivalent to the plain key on a certified database
 * ({@link diffManagedIndexes}'s `tenantKeyCertified`).
 */
export const GLOBAL_TENANT = '__global__';

/**
 * Driver-side unique-scope vocabulary — ADR-0120 D1.
 *
 * `'organization'` is accepted here AHEAD of the spec schema: #4986 lands the
 * spec/lint token separately, and the merge order is deliberately driver first
 * so spec-side acceptance never outruns driver-side enforcement. Until then,
 * spec's `isUniqueDeclared` / `isGlobalUnique` know nothing of
 * `'organization'`, so these wrappers are the single judgment point inside
 * driver-sql — every scope decision in this package reads them, never the
 * spec helpers directly.
 */
export function isUniqueScopeDeclared(unique: unknown): boolean {
  return unique === 'organization' || isUniqueDeclared(unique);
}

/**
 * The organization-scoped spellings of a FIELD-level `unique`: bare `true`
 * (the positional synonym, unchanged since #3696) and the explicit
 * `'organization'` word (ADR-0120 D1). Pass a field's `unique`; do NOT pass a
 * declared index's.
 *
 * This is NOT the scope judgment for a declared index, and it must not be
 * reached for there. {@link normalizeDeclaredIndex} decides with a strict
 * `idx?.unique === 'organization'` instead, so a declared index's bare `true`
 * is taken VERBATIM as global — the `'global'` arm. That the two paths judge
 * the same token differently is the answer to #4986, not an oversight: the
 * spellings were authored under different contracts, and both halves are
 * pinned (`sql-driver-declared-index-organization-respelling.test.ts`).
 *
 * Routing the declared-index branch through this predicate so code and comment
 * agree is REJECTED — maintainer ruling 2026-08-13, option 1 of #8323. It
 * would silently reinterpret every existing declared `unique: true` on
 * deployed databases as organization-scoped: an unannounced index migration,
 * landing a release BEFORE #5082 refuses the bare spelling — the
 * two-migrations-with-contradictory-meanings sequence that ruling exists to
 * avoid. Whether a declared index's bare `true` should be refused at all is
 * PARKED on #5082 (v18 D2: bare `true` → `'global'` plus a loud refusal).
 * Until that lands the divergence stays, surfaced to authors rather than
 * silently repaired: lint `unique/unscoped-declared-index` warns on it
 * (`packages/lint/src/data-model-rules.ts`) and `IndexSchema.unique`'s
 * `describe()` states it (`packages/spec/src/data/object.zod.ts`).
 */
export function isOrganizationScopedUnique(unique: unknown): boolean {
  return unique === true || unique === 'organization';
}

/**
 * The NULL-safe organization key part, spelled once (ADR-0120 D3). Display and
 * signature form — DDL emission quotes the identifier per dialect.
 *
 * Read vocabulary only since ADR-0131 D1: no generator marks the tenant key
 * part NULL-safe any more, so this renders an index an earlier release built,
 * or an op a caller hands in already normalized.
 */
export function organizationKeyPartSql(column: string): string {
  return `COALESCE(${column}, '${GLOBAL_TENANT}')`;
}

export type SqlDialectName = 'sqlite' | 'postgres' | 'mysql' | 'unknown';

export type DriftCategory = 'safe' | 'needs_confirm' | 'destructive';

/**
 * A reconcilable schema operation, machine-readable for the reconciler.
 *
 * Column ops name a single `column`; index ops (#3728) name the index and may
 * span several columns, so their `column` carries only the leading one — set so
 * the sorting / rendering already keyed on it keeps working.
 */
export type DriftOp =
  | { type: 'relax_not_null'; table: string; column: string }
  | { type: 'tighten_not_null'; table: string; column: string }
  | { type: 'widen_varchar'; table: string; column: string; to: number; from?: number }
  | { type: 'narrow_varchar'; table: string; column: string; to: number; from?: number }
  | { type: 'drop_column'; table: string; column: string }
  /**
   * Strip a column DEFAULT metadata never asked for (#4560).
   *
   * Today's only source is a `defaultValue` runtime token that a pre-fix build
   * emitted as a literal (`DEFAULT 'current_user'`), so every insert that
   * omitted the field got the token's own spelling instead of the engine's
   * deliberate "leave it unset". Dropping it cannot fail and cannot lose data —
   * stored rows keep whatever they hold; only FUTURE omitted inserts change,
   * from a bogus literal to NULL. Rows already carrying the bogus value are NOT
   * rewritten: they stay visible to the dangling-reference audit (#4551), whose
   * standing rule is report, never rewrite.
   */
  | { type: 'drop_column_default'; table: string; column: string }
  /**
   * REPORT ONLY (#11535). The column's base type diverges from the one the
   * declaration materialises, and **the platform deliberately does not change
   * it** — an operator must, by hand.
   *
   * ⛔ There is NO reconciler arm for this op, and adding one is not a
   * refactor. Whether ObjectStack should perform the `ALTER TABLE … TYPE …
   * USING …` itself is a **live maintainer decision** (the other half of
   * #11535): it is a migration over existing rows plus an index drop/rebuild,
   * i.e. destructive and hard to roll back. This op exists so the divergence
   * can be SEEN travelling the same plan/report road as every other finding —
   * it is the absence of an automatic migration made explicit, not a
   * placeholder for one.
   *
   * Measured consequence of having no arm, on live Postgres 16.13 (the same on
   * MySQL 8.0.46): `applyDriftOpInPlace` matches no case and returns `false`,
   * so `applyMigrationEntries` reports the entry as **skipped, never applied**,
   * and logs it. That is the intended behaviour, not a gap to close. SQLite
   * reaches the same verdict by a different road (#11722): its arm rebuilds the
   * table and reports as applied only the entries the rebuild actually acted
   * on, so an op with no arm is skipped there too rather than announced as
   * migrated.
   *
   * `from`/`to` are the physical and declared type words, carried so a renderer
   * can show the divergence without re-deriving it from the message.
   */
  | { type: 'manual_column_type_change'; table: string; column: string; to: string; from: string }
  /**
   * REPORT ONLY (#12121). The column is a `varchar(n)` under a TEXT-family
   * field that declared NO usable bound — the shape `createColumn` emits as
   * TEXT — so the column caps writes the declaration allows, and **the platform
   * deliberately does not change it**.
   *
   * ⛔ A DISTINCT op rather than a second use of `manual_column_type_change`,
   * for a measured reason and not a stylistic one: `os migrate
   * multi-value-columns` selects its ENTIRE population by
   * `op.type === 'manual_column_type_change'` (`isStaleMultiValueColumn`, "the
   * only op this command touches") and then recovers the dialect by matching the
   * finding's message against {@link manualJsonConversionSql}. Reusing that op
   * would hand this finding to a command whose remedy converts the column to
   * **json** — the wrong column type for a signature — and, since this message
   * embeds no json statement, the command would file it under
   * `remedy_not_recognized`: one refusal line per finding, on every run, for a
   * divergence that command has no business with.
   *
   * ⛔ There is NO reconciler arm, deliberately. `applyDriftOpInPlace` matches
   * no case, so `applyMigrationEntries` reports the entry as skipped and logs
   * it. Whether ObjectStack should run the `ALTER` itself is a separate decision
   * with hazards this differ must not pre-empt: on MySQL a `MODIFY` restates the
   * WHOLE column definition (silently dropping a NOT NULL or DEFAULT that is not
   * repeated) and a TEXT column cannot carry a plain index without a prefix
   * length, so the conversion can turn a working table into one whose declared
   * index no longer exists.
   *
   * `from` is the physical type word and `to` is always `'text'`, spelled the way
   * `manual_column_type_change` spells them so a renderer showing `from → to`
   * needs no new arm.
   */
  | { type: 'manual_widen_varchar_to_text'; table: string; column: string; to: 'text'; from: string }
  /**
   * Retire the legacy platform-wide UNIQUE index on a now-tenant-scoped field
   * and put the composite `(tenantField, field)` in its place (#3696). The two
   * names differ, so the reconciler creates before it drops — uniqueness is
   * never unenforced in between — and because the old index is strictly
   * stronger than the new one, the replacement can neither fail nor lose data.
   */
  | {
      type: 'replace_unique_index';
      table: string;
      column?: string;
      dropIndexNames: string[];
      createIndexName: string;
      createColumns: string[];
      /** Columns whose key part is the NULL-safe organization form (ADR-0120 D3). */
      nullSafeColumns?: string[];
    }
  /** Materialize a declared index that has no physical counterpart. */
  | {
      type: 'create_index';
      table: string;
      column?: string;
      indexName: string;
      columns: string[];
      unique: boolean;
      /** Columns whose key part is the NULL-safe organization form (ADR-0120 D3). */
      nullSafeColumns?: string[];
    }
  /** Drop an index ObjectStack generated that metadata no longer declares. */
  | { type: 'drop_index'; table: string; column?: string; indexName: string }
  /**
   * An index exists under the declared name but with a different definition.
   * `syncDeclaredIndexes` is name-idempotent and would skip it forever, so the
   * only fix is drop-then-create under the same name.
   */
  | {
      type: 'recreate_index';
      table: string;
      column?: string;
      indexName: string;
      columns: string[];
      unique: boolean;
      /** Columns whose key part is the NULL-safe organization form (ADR-0120 D3). */
      nullSafeColumns?: string[];
      /**
       * ADR-0120 D4: the divergence is EXACTLY the bare organization column
       * tightening into its NULL-safe COALESCE form — same column identities,
       * same uniqueness, physical index fully plain. This is the one recreate
       * whose danger is data-dependent rather than structural, so it goes
       * through the duplicate pre-flight probe: clean → `safe` (dev
       * `autoMigrate: 'safe'` may apply it), duplicates found → blocked with a
       * row report, old index left in place.
       */
      tightenNullSafeOnly?: boolean;
    }
  /**
   * REPORT ONLY. Metadata declares an index that can never be built, because
   * a key column is one no declaration will ever materialize: the name is not
   * a field of the object (a misspelling the Studio save door admits), or it
   * is a virtual `formula` field, which is computed on read and has no column.
   * `SqlDriver.syncDeclaredIndexes` skips the whole index and says so at
   * `error`. For a UNIQUE index, the constraint it declares is NOT enforced
   * while the object keeps working normally, which is the AGENTS.md
   * durability-degradation shape.
   *
   * ⛔ There is NO reconciler arm, and none can exist: there is no column to
   * build the index over. The remedy is a metadata edit (name stored fields in
   * `indexes[].fields`, or drop the index), so `applyMigrationEntries` reports
   * the entry as skipped, on every dialect. The op sits in
   * {@link INDEX_DRIFT_OPS} so that skip takes the index path and never
   * triggers a SQLite table rebuild.
   *
   * This op exists so that `os migrate plan` can show the skip. Before it,
   * {@link expectedIndexes} dropped such an index from the expected set, and
   * the plan reported nothing at all.
   *
   * `missingColumns` lists only the key columns that will never materialize.
   * A declared column that is merely not added YET is pending additive work,
   * not this finding.
   */
  | {
      type: 'unbuildable_index';
      table: string;
      column?: string;
      indexName: string;
      unique: boolean;
      missingColumns: string[];
    };

/**
 * Physical work the boot sync is holding back (#3917).
 *
 * Distinct from {@link DriftOp}: drift is divergence between metadata and an
 * EXISTING column/index that only a deliberate reconcile may resolve, whereas
 * this is the work `initObjects` performs on its own — captured rather than
 * executed while the driver runs with DDL deferred, so `os migrate plan` can
 * show it and `os migrate apply` can gate it behind the confirmation prompt.
 *
 * The plan's promise is that it shows what `apply` will do, so **anything added
 * to `initObjects`' physical path has to be representable here** — otherwise an
 * operator confirms a two-column plan and `apply` additionally rewrites a table.
 * That is the gap #3954 closed for the datetime convergence; keep it closed.
 */
export interface PendingSchemaWork {
  table: string;
  kind: PendingSchemaWorkKind;
  /**
   * Declared columns for a create; the missing ones for an add; the columns
   * being converged for the two datetime steps.
   *
   * The additive kinds name only fields that MATERIALIZE a column
   * ({@link fieldHasColumn}) — a virtual `formula` field never appears. The
   * promise above cuts both ways: a plan may not promise work `apply` cannot
   * deliver either, or the finding can never be cleared (#3978).
   */
  columns: string[];
  /**
   * How much data the step touches, when that is knowable up front and worth
   * knowing — absent for the additive kinds, which touch none.
   *
   * For `normalize_datetime_storage` it is the number of row-writes (summed
   * across `columns`, since each is its own `UPDATE`). For
   * `widen_datetime_columns` it is the table's row count, because MySQL's
   * `ALTER … MODIFY` is a full rebuild holding a metadata lock — which is the
   * number that decides "now" versus "in a maintenance window".
   */
  rows?: number;
}

/**
 * What kind of physical work a {@link PendingSchemaWork} entry represents.
 *
 * The first two are purely additive and never touch existing rows. The rest are
 * NOT: `normalize_datetime_storage` / `normalize_time_storage` rewrite rows in
 * place (the SQLite canonical-text backfills, #3912/#3994) and
 * `widen_datetime_columns` / `widen_time_columns` rebuild a column (the MySQL
 * `TIMESTAMP` → `DATETIME(3)` and `TIME` → `TIME(3)` widenings, #3942/#3994).
 * They are rendered under their own heading for that reason: the additive
 * section tells the operator the work is never data-losing, and that claim must
 * not silently come to cover a row rewrite.
 */
export type PendingSchemaWorkKind =
  | 'create_table'
  | 'add_columns'
  | 'normalize_datetime_storage'
  | 'normalize_time_storage'
  | 'widen_datetime_columns'
  | 'widen_time_columns';

/** True for the kinds that rewrite or rebuild existing data rather than adding to it. */
export function isInPlaceSchemaWork(kind: PendingSchemaWorkKind): boolean {
  return kind !== 'create_table' && kind !== 'add_columns';
}

/** Ops that act on an index rather than a column — reconciled without a table rebuild. */
export const INDEX_DRIFT_OPS: ReadonlySet<DriftOp['type']> = new Set([
  'replace_unique_index',
  'create_index',
  'drop_index',
  'recreate_index',
  'unbuildable_index',
]);

export type IndexDriftOp = Extract<
  DriftOp,
  { type: 'replace_unique_index' | 'create_index' | 'drop_index' | 'recreate_index' | 'unbuildable_index' }
>;
/** Ops that act on a single column — the only ones with a guaranteed `column`. */
export type ColumnDriftOp = Exclude<DriftOp, IndexDriftOp>;

/** True when this op mutates an index rather than a column. */
export function isIndexDriftOp(op: DriftOp): op is IndexDriftOp {
  return INDEX_DRIFT_OPS.has(op.type);
}

/**
 * A managed-schema drift finding: a {@link SchemaDiffEntry} enriched with the
 * owning table, a reconcile {@link DriftOp}, and a {@link DriftCategory}.
 */
export interface ManagedDriftEntry extends SchemaDiffEntry {
  table: string;
  category: DriftCategory;
  op: DriftOp;
  /** Human one-liner with an actionable hint. */
  message: string;
}

/**
 * [#23925] The remedy sentence {@link diffManagedTable} ends each destructive
 * COLUMN entry's message with: `os migrate apply --allow-destructive`, the
 * command that performs the entry wherever the reporting driver's apply door
 * can. Spelled once, here, so the module that writes the sentence is the one
 * that takes it off again ({@link driftFindingWithoutRemedy}).
 *
 * Why it must come off somewhere: the finding is the same on every face (the
 * report is this differ's), but whether that command can perform the entry is
 * the reporting driver's to declare. The Turso REMOTE face runs no SQLite
 * table rebuild, so a `tighten_not_null` there is not applied by the command
 * its message names, and `os migrate plan` shows the finding with the
 * driver's declared limit instead of a remedy that database does not have.
 * Every other reader keeps the message as written, byte for byte.
 */
const DESTRUCTIVE_COLUMN_REMEDY = {
  tighten_not_null: ' Run "os migrate apply --allow-destructive".',
  narrow_varchar: ' "os migrate apply --allow-destructive".',
  drop_column: ' — "os migrate apply --allow-destructive" to drop it.',
} as const satisfies Partial<Record<DriftOp['type'], string>>;

/**
 * [#23925] A drift entry's finding without the `os migrate apply
 * --allow-destructive` remedy sentence {@link diffManagedTable} ends a
 * destructive column entry with ({@link DESTRUCTIVE_COLUMN_REMEDY}); any other
 * entry's message, unchanged. For a renderer whose database's driver declares
 * it cannot apply the entry, so that remedy is never offered there.
 */
export function driftFindingWithoutRemedy(entry: Pick<ManagedDriftEntry, 'message' | 'op'>): string {
  const remedy: string | undefined = (DESTRUCTIVE_COLUMN_REMEDY as Partial<Record<DriftOp['type'], string>>)[entry.op.type];
  return remedy !== undefined && entry.message.endsWith(remedy)
    ? entry.message.slice(0, -remedy.length)
    : entry.message;
}

/**
 * [ADR-0131 D10 item 5] The column whose drop the organization-ownership
 * ceremony owns: on a database it has not certified, the ceremony drops it
 * itself and `os migrate apply --allow-destructive` refuses the run.
 */
const CEREMONY_OWNED_DROP_COLUMN = 'organization_id';

/** Is `entry` the drop of a column the organization-ownership ceremony owns? */
export function isCeremonyOwnedColumnDrop(entry: Pick<ManagedDriftEntry, 'op'>): boolean {
  return entry.op.type === 'drop_column' && entry.op.column === CEREMONY_OWNED_DROP_COLUMN;
}

/**
 * [ADR-0131 D10 item 5] The boot's `[schema-drift]` line for a ceremony-owned
 * column drop ({@link isCeremonyOwnedColumnDrop}) on a database the driver
 * reads as not certified: the finding without its `--allow-destructive`
 * sentence, which that database refuses, and the ceremony in its place, in
 * the words `os migrate plan` shows under the same entry.
 */
export function ceremonyOwnedDropFinding(entry: Pick<ManagedDriftEntry, 'message' | 'op'>): string {
  return (
    `${driftFindingWithoutRemedy(entry)} — a drop the organization-ownership ceremony owns, and it has not ` +
    `verified on this database: run "os migrate organization-ownership", which drops it itself.`
  );
}

/** Columns the driver creates unconditionally — never metadata fields. */
export const BUILTIN_COLUMNS = new Set(['id', 'created_at', 'updated_at']);

/**
 * Suffix of a HASH-SHADOW column — the driver-owned column that carries a
 * declared UNIQUE index MySQL cannot express over the values themselves
 * (#11627). See `SqlDriver.createHashShadowUniqueIndex` for why it exists and
 * what it stores.
 */
export const HASH_SHADOW_SUFFIX = '__hash';

/**
 * Is this physical column a driver-owned hash shadow (#11627)?
 *
 * ⚠️ Load-bearing for the ORPHAN differ below, and the reason this predicate
 * is exported rather than inlined. A hash-shadow column exists in the database
 * and — by construction — in no metadata field, which is the exact shape the
 * orphan pass reports as `unmapped_column` with a `drop_column` op. Dropping it
 * would take the UNIQUE index it carries with it, silently returning the object
 * to "registered but its declared uniqueness unenforced" — the very state
 * #11627 and commit d0e3a885b exist to end, reached this time through the migration tool
 * rather than through a refused DDL.
 *
 * Matched by SUFFIX rather than by a registry of known names, deliberately: the
 * differ runs against a database whose metadata it is comparing to, and a
 * shadow whose declared index has since been removed must still be recognised
 * as driver-owned (it is cleaned up by the index's own removal path, not by a
 * blind column drop).
 *
 * ⚠️ That last clause was an ASSERTION about a path that did not exist, and it
 * is the load-bearing half of this docblock: it is the whole reason this pass
 * may decline to report the column. #13056 built the path it names —
 * `SqlDriver.dropOrphanedHashShadowColumn`, run by the `drop_index` op after
 * the index goes — because until then `dropIndexIfExists` issued one statement
 * family and never touched a column. The shadow of a retired declaration
 * therefore outlived it forever: a `VARBINARY(32)` STORED generated column,
 * recomputed on every INSERT and on every UPDATE touching its sources, keyed by
 * nothing and reported by no pass. Skipping the column here is only correct
 * while some other path really does collect it — so if that method is ever
 * removed or its call site moved, this `continue` becomes a leak again and the
 * skip must go with it.
 */
export function isHashShadowColumn(name: string): boolean {
  return name.endsWith(HASH_SHADOW_SUFFIX);
}

/**
 * The hash-shadow column that carries `indexName` (#11627), capped to MySQL's
 * 64-character identifier limit.
 *
 * ⚠️ Lives HERE, beside {@link isHashShadowColumn}, rather than in the driver:
 * The defect commit cd1348802 fixed was the price of the split. The ORPHAN-column pass knew the shadow
 * vocabulary and the INDEX differ did not, so a healthy shadow-carried UNIQUE
 * had its column protected from a drop while the index that column carries was
 * proposed for a destructive rebuild. Both passes now ask the same module the
 * same question, and `SqlDriver.hashShadowColumnFor` delegates here so the name
 * the sync CREATES and the name the differ LOOKS FOR cannot drift apart.
 *
 * Derived from the INDEX name rather than from the column list, deliberately:
 * one shadow serves one declared index, a composite index has no single column
 * to name it after, and the index name is already the differ's identity for the
 * constraint. The overflow branch keeps a truncated prefix for readability and
 * appends a digest of the FULL name, so two long index names that share a
 * prefix still get different shadows.
 */
export function hashShadowColumnFor(indexName: string): string {
  const direct = `${indexName}${HASH_SHADOW_SUFFIX}`;
  if (direct.length <= 64) return direct;
  const digest = createHash('sha256').update(indexName).digest('hex').slice(0, 8);
  const keep = 64 - HASH_SHADOW_SUFFIX.length - digest.length - 1;
  return `${indexName.slice(0, keep)}_${digest}${HASH_SHADOW_SUFFIX}`;
}

/**
 * One key part a hash shadow hashes: the column identity, and whether the
 * generation expression folds it through the NULL-safe `COALESCE(col, ...)`
 * form (ADR-0120 D3, carried into the shadow by commit df1c75c4b).
 */
export interface HashShadowKeyPart {
  column: string;
  nullSafe: boolean;
}

/**
 * Read the DECLARED key parts back out of a hash shadow's stored
 * `GENERATION_EXPRESSION` (commit cd1348802).
 *
 * This is what makes a shadow-carried key COMPARABLE rather than merely
 * skippable. Since commit df1c75c4b the expression carries the NULL-safe parts in their
 * COALESCE spelling, so the FORM of the key — which columns, and which of them
 * are folded — survives the round trip, and the differ can ask the real
 * question ("does this shadow enforce what metadata declares?") instead of the
 * blind one ("is this a shadow at all?").
 *
 * ⛔ Why the blind question is not good enough: a shadow created BEFORE commit df1c75c4b
 * hashes the RAW columns, so `CONCAT` returns NULL for every NULL-organization
 * row and the rows the COALESCE bucket exists to constrain are constrained by
 * nothing (#5030's shape). It is indistinguishable BY NAME from a healthy one.
 * Skipping every shadow would make that class of drift permanently invisible —
 * trading a false destructive finding for a true silent one.
 *
 * MySQL stores the expression normalized and back-quoted — e.g.
 * `unhex(sha2(concat(coalesce(`org`,_utf8mb4'__global__'),0x1f,`v`),256))` — so
 * the key parts are exactly the back-quoted runs in key order, and the optional
 * `coalesce(` prefix marks the folded ones. The `unhex` / `sha2` / `concat`
 * wrapper and the `_utf8mb4'...'` literal carry no back quotes and contribute
 * nothing.
 */
export function parseHashShadowKeyParts(generationExpression: string): HashShadowKeyPart[] {
  const matches = String(generationExpression ?? '').matchAll(
    /(coalesce\s*\(\s*)?`((?:[^`]|``)+)`/gi,
  );
  return [...matches].map((m) => ({
    column: m[2]!.replace(/``/g, '`'),
    nullSafe: m[1] != null,
  }));
}

/** Minimal shape of an introspected physical column (see SqlDriver.introspectColumns). */
export interface PhysicalColumn {
  name: string;
  type: string;
  nullable: boolean;
  /**
   * Raw as knex `columnInfo()` reports it — a number on some dialects, a
   * STRING on SQLite (measured: `"255"`). The varchar differ below narrows
   * via `typeof` before comparing, which is the pattern for any new reader.
   */
  maxLength?: number | string;
  /**
   * The column's raw DEFAULT as the dialect reports it (knex `columnInfo`), or
   * `null`/`undefined` when it has none. Dialect-decorated — SQLite and Postgres
   * quote a string literal and Postgres appends a `::type` cast — so compare it
   * through {@link physicalDefaultIsToken}, never with `===`.
   */
  defaultValue?: unknown;
}

/** Minimal shape of a metadata field definition. */
export interface FieldDef {
  type?: string;
  required?: boolean;
  multiple?: boolean;
  maxLength?: number;
  /** ADR-0113: the explicit physical constraint — nullability drift reads THIS, not `required`. */
  storage?: { notNull?: boolean };
  /**
   * The declared default. Only consulted for the runtime-token dimension
   * (#4560): a token is an instruction, so it must never appear as a physical
   * column DEFAULT. Literal defaults are deliberately NOT diffed — a hand-edited
   * DEFAULT on a column is a DBA's business, and reporting every one of them
   * would drown the plan the same way undeclared indexes would.
   */
  defaultValue?: unknown;
}

/**
 * Does the physical column DEFAULT literally spell out `token`?
 *
 * Each dialect decorates the literal it reports differently — SQLite
 * `'current_user'`, Postgres `'current_user'::character varying`, MySQL a bare
 * `current_user` — so the raw string is stripped of one layer of quoting and of
 * a trailing cast before comparing. Deliberately EXACT after that: this is the
 * fingerprint of a DEFAULT the platform itself emitted from a token spelling,
 * and matching loosely would let it drop a default that merely resembles one.
 */
export function physicalDefaultIsToken(raw: unknown, token: string): boolean {
  if (typeof raw !== 'string') return false;
  let s = raw.trim();
  const cast = s.indexOf('::');
  if (cast > 0) s = s.slice(0, cast).trim();
  if (s.length >= 2 && ((s.startsWith("'") && s.endsWith("'")) || (s.startsWith('"') && s.endsWith('"')))) {
    s = s.slice(1, -1);
  }
  return s === token;
}

/**
 * Does this metadata field materialise a physical column? Mirrors
 * `SqlDriver.createColumn` exactly: `formula` is virtual (computed, no column);
 * everything else — including a MULTI-VALUED field (a JSON column) — gets one.
 *
 * [#17469] "Multi-valued" is `@objectstack/spec`'s `isMultiValueField`, the
 * same question `createColumn`'s short-circuit now asks. Reading `multiple`
 * raw here would keep a column alive for a declaration the writer no longer
 * gives one.
 */
export function fieldHasColumn(field: FieldDef): boolean {
  if (isMultiValueField({ type: String(field?.type ?? ''), multiple: field?.multiple === true })) return true;
  return (field?.type ?? 'string') !== 'formula';
}

/** Whether the dialect physically enforces varchar length (SQLite does not). */
function enforcesVarcharLength(dialect: SqlDialectName): boolean {
  return dialect === 'postgres' || dialect === 'mysql';
}

/**
 * Is this physical column a `varchar`/`char` — the only kind that HAS a
 * declared length to compare against (#11431)?
 *
 * Without this the length branch below read a MySQL **TEXT** column as a
 * varchar 65535 wide, because that is literally what the server reports for it.
 * Measured on MySQL 8.0.46 and Postgres 16, same two columns:
 *
 *   MySQL     `text`              character_maximum_length = 65535
 *   Postgres  `text`              character_maximum_length = NULL
 *   both      `varchar(30)`       character_maximum_length = 30
 *
 * So the defect was MySQL-only and invisible on Postgres. Every bounded,
 * unkeyed text field — the shape `createColumn` deliberately leaves as TEXT —
 * diffed as "declared 4000, column allows 65535" and produced a
 * `narrow_varchar` op at severity `error`, category **destructive**, against a
 * table the driver had just created and which held no rows. Measured on live
 * MySQL: `sys_email`'s envelope alone accounts for seven such findings, each
 * inviting `os migrate apply --allow-destructive` to rewrite a TEXT column into
 * a varchar for no reason.
 *
 * A TEXT column refuses nothing a `maxLength` allows, so there is no
 * divergence to plan an ALTER for; the bound is enforced at the write seam.
 * Spelled as a substring test rather than an equality because the three
 * dialects disagree on the word — Postgres says `character varying`, MySQL and
 * SQLite say `varchar` — matching the predicate `introspectSchema` already
 * uses for the same question.
 */
function isCharacterColumn(type: string | undefined): boolean {
  return /char/i.test(String(type ?? ''));
}

/**
 * Is this physical column one that ACCEPTS a stringified JSON array without
 * complaint — i.e. `varchar`/`char`/`text` in any dialect's spelling (#11535)?
 *
 * Wider than {@link isCharacterColumn} by exactly the TEXT family, and that
 * width is the point rather than an accident: the write path stringifies a
 * multi-value field's array, and a text column takes the literal as happily as
 * a varchar does. Both are the silent-corruption shape.
 *
 * Deliberately NOT "anything that is not json". A stale `integer` or
 * `timestamp` column under a now-multi-value field is already LOUD — Postgres
 * refuses `'["a","b"]'` with `22P02 invalid input syntax`, MySQL with
 * `ER_TRUNCATED_WRONG_VALUE` — so it needs no finding to become visible, and
 * matching it here would report a divergence the database itself already
 * refuses. The textual family is the one that says yes and corrupts.
 */
function acceptsStringifiedJson(type: string | undefined): boolean {
  return /char|text/i.test(String(type ?? ''));
}

/**
 * The field types whose column is TEXT whenever the field declares no usable
 * `maxLength` — `createColumn`'s text-family case (#11794 / #11875).
 *
 * ⚠️ Read the scope precisely: these are the types for which the emitter's
 * answer is TEXT **regardless of whether an index keys the column**. That
 * independence is what licenses the #12121 branch below to report on this shape
 * without being told which columns are keyed, and it is not an assumption — it
 * falls out of the emitter's own expression, `keyable = keyed ?
 * keyableTextLength(field) : null`, whose keyed arm returns `null` for a field
 * with no positive-integer bound. A text-family field that DID declare a keyable
 * bound takes `varchar(maxLength)` when keyed, which is exactly why that branch
 * is gated on the declaration being ABSENT and never fires for it.
 *
 * ⛔ NOT derived from the spec's `BOUNDED_STRING_FIELD_TYPES`, for the reason
 * `sql-driver-12017-bounded-string-spec-parity.test.ts` argues in full: that set
 * answers "may this type declare a bound?" and carries no varchar/TEXT
 * partition, so deriving would have to INVENT an answer for every future member
 * at the one seam where the maintainer has actually ruled per type. It is a
 * hand-written list and is therefore PINNED rather than trusted —
 * `schema-drift.unbounded-text-column.test.ts` probes the driver's OWN
 * dispatch (`varcharColumnChars`) over every `FieldType` the spec declares and
 * asserts set equality in both directions, plus the keyed-and-unkeyed `null`
 * above for every member. A type entering or leaving `createColumn`'s text
 * family reds there by name.
 *
 * ⚠️ It lives here rather than being imported from `sql-driver.ts` because the
 * dependency runs the other way — `sql-driver.ts` imports this module — so an
 * import would be a cycle. The pin is what stands in for a shared constant.
 *
 * ⛔ Module-exported so this package's own suites can pin it, and deliberately
 * NOT added to `index.ts` — the same call {@link MULTI_VALUE_COLUMN_REMEDY_COMMAND}
 * makes: nothing outside this package has a question this set answers.
 */
export const UNBOUNDED_TEXT_FIELD_TYPES: ReadonlySet<string> = new Set([
  'text', 'textarea', 'html', 'markdown', 'richtext', 'code',
  // #11875 — joined the family once the write seam enforced their declared bound.
  'signature', 'qrcode',
]);

/**
 * The field types the EMITTER materialises as a JSON column — the writer's own
 * predicate, restated on this side of the cycle (#15771).
 *
 * `createColumn`'s catch-all is `JSON_COLUMN_TYPES.has(type) ? jsonColumn(...)
 * : table.string(...)` and `isJsonField` — the read-side deserializer — is
 * `JSON_COLUMN_TYPES.has(type) || isMultiValueField(field)` (#17469 — was
 * `|| !!field.multiple`). So the writer has always asked about the TYPE as well
 * as multi-value, while the base-type branch below asked only
 * `field.multiple === true`. A single-value JSON-class field on a
 * `varchar`/`text` column was therefore written as JSON and did not exist to
 * the differ, permanently and silently — the additive sync never revisits a
 * column. Two halves disagreeing about which declarations get a json column is
 * the defect class this module exists to close; this constant is the deletion
 * of that fork, not a new criterion.
 *
 * ⚠️ Seeded from `@objectstack/spec` — the SAME sets `JSON_COLUMN_TYPES` is
 * seeded from — rather than hand-listed, so a value-shape class added to the
 * spec becomes a JSON column and a DETECTABLE one in one edit. Only the two
 * driver-internal aliases are hand-written, because they are not authorable
 * `FieldType`s at all: `object` / `array` name introspected external columns.
 *
 * ⚠️ The FILE family is NOT here, and its absence is not a narrowing of the
 * #15771 population. Since the ADR-0104 addendum a single-value media field is
 * a json column on a deployment that has not moved its columns and a `varchar`
 * on one that has, so it is not a constant on either side of the cycle. It is
 * asked as `fileColumnsMoved` — a per-CALL input to {@link diffManagedTable},
 * supplied by `SqlDriver.detectTableDrift` from the driver's own arm — and
 * OMITTING it keeps this module's pre-addendum verdicts exactly (see the
 * parameter's own note). ⛔ Re-adding the family to this set would report the
 * RULED end-state column as `type_mismatch` / `severity: 'error'` with a remedy
 * that converts it back to json, i.e. tell every deployment that ran the
 * migration to undo it.
 *
 * ⚠️ A second constant rather than an import, for the reason
 * {@link UNBOUNDED_TEXT_FIELD_TYPES} gives in full — `sql-driver.ts` imports
 * THIS module, so the import would be a cycle. It is therefore PINNED rather
 * than trusted: `schema-drift.json-column-parity.test.ts` asserts set equality
 * against the driver's own `isJsonField`, in both directions, over every
 * `FieldType` the spec declares.
 *
 * ⛔ Module-exported so this package's own suites can pin it, and deliberately
 * NOT added to `index.ts` — the same call {@link UNBOUNDED_TEXT_FIELD_TYPES}
 * and {@link MULTI_VALUE_COLUMN_REMEDY_COMMAND} make.
 */
export const JSON_COLUMN_FIELD_TYPES: ReadonlySet<string> = new Set<string>([
  ...STRUCTURED_JSON_TYPES, ...MULTI_OPTION_TYPES,
  'object', 'array',
]);

/**
 * Does a JSON-class field's column carry its type on THIS dialect — i.e. does a
 * stale textual column silently corrupt the value (#11535 / #15771)?
 *
 * The question is about the DIALECT, so widening the population it is asked
 * about (from `multiple` alone to the emitter's own JSON-class predicate,
 * #15771) changes nothing here: what makes SQLite exempt is that its read path
 * `JSON.parse`s a textual column regardless, which is as true of a single-value
 * `file` id as of an array — re-measured on an in-memory cell, the stale
 * `varchar(2048)` column and the driver's own `text` column store BYTE-IDENTICAL
 * bytes and read back identically.
 *
 * Postgres and MySQL: yes. Measured end to end on live Postgres 16.13 and MySQL
 * 8.0.46 — a field that gained `multiple: true` over a pre-existing
 * `varchar(255)` column round-trips as the LITERAL STRING `["a","b"]`
 * (`typeof === 'string'`, `Array.isArray === false`), because the write path
 * stringifies for a json field on every non-SQLite dialect while the read path
 * relies on the driver's column-type-based decoding, which a stale textual
 * column defeats.
 *
 * SQLite: **no**, and the exclusion is measured rather than assumed. The same
 * stale column reads back as a real `['a','b']` array there (the read path
 * `JSON.parse`s on SQLite regardless of what the column calls itself), so there
 * is no corruption to report — reporting it anyway would put a permanent
 * `error` finding on every long-lived SQLite development database for a
 * divergence that changes no value. SQLite's column type is an affinity label,
 * not an enforced type, which is the same reason
 * {@link enforcesVarcharLength} excludes it.
 */
function jsonColumnTypeIsLoadBearing(dialect: SqlDialectName): boolean {
  return dialect === 'postgres' || dialect === 'mysql';
}

/**
 * The operator-run command that repairs a stale multi-value column (#11535).
 *
 * Named in the finding's `message` because a message that only DESCRIBES a
 * problem leaves the operator to invent the repair. Until `os migrate
 * multi-value-columns` shipped (#11733) there was nothing else to say, so the
 * message told them to run raw SQL by hand — and the sentence it opened with,
 * "ObjectStack will NOT change this column for you", became false the moment
 * that command landed. The hand-run route is still printed below it, because
 * the statement is what the command runs and an operator without the CLI still
 * needs it; it is no longer the FIRST thing offered.
 *
 * ⛔ Module-exported so this package's own suites can pin the spelling, and
 * deliberately NOT added to `index.ts`: naming a CLI command in a warning is a
 * string, and a published export is the step that would let the CLI import its
 * own id back out of the driver it boots. `schema-drift.base-type-mismatch.test.ts`
 * asserts the emitted message contains it; the command id itself lives in
 * `packages/cli/src/commands/migrate/multi-value-columns.ts`.
 *
 * ⚠️ It changes NOTHING about how loud this finding is or what it gates:
 * `severity: 'error'` and `category: 'needs_confirm'` are untouched, so the
 * finding still refuses no boot (see the emission site's comment — the boot
 * gate reads CATEGORY, and `destructive` is the value that would stop an
 * already-serving database from starting).
 */
export const MULTI_VALUE_COLUMN_REMEDY_COMMAND = 'os migrate multi-value-columns';

/**
 * The hand-run statement that converts a stale textual column to `json`,
 * spelled for the dialect the operator is actually on.
 *
 * The Postgres form keeps the SHAPE of the reporter's own production workaround
 * (#11535) — the three-way CASE over the three states a stale column's rows are
 * actually in — with one arm changed, for a reason that was measured rather than
 * reasoned: the reporter's `to_json(col)` turns a legacy single value into the
 * JSON **scalar** `"a"`, under a field the metadata now declares MULTI-VALUE. On
 * live Postgres 16.13 that row read back as a string with `Array.isArray ===
 * false`, i.e. still not the shape the declaration promises, while live MySQL
 * 8.0.46's `JSON_ARRAY(col)` produced `["a"]`. `json_build_array` makes the two
 * dialects hand back the same value for the same row, which is the standing rule
 * here — one declaration with two enforcement answers is the defect class this
 * package's conformance matrices exist to close.
 *
 * Both forms are EXECUTED against live servers by
 * `schema-drift.base-type-mismatch.test.ts`, over rows in every state the column
 * can be in (legacy single value, already-stringified array, empty string,
 * NULL), and the finding is asserted to clear afterwards — an operator-facing
 * remedy nobody runs is a remedy that drifts into being wrong.
 */
export function manualJsonConversionSql(dialect: SqlDialectName, table: string, column: string): string {
  if (dialect === 'mysql') {
    // MySQL will not cast text to json implicitly: rows holding a legacy single
    // value have to become one-element arrays FIRST, or the ALTER fails with
    // `ER_INVALID_JSON_TEXT` on the first non-JSON row.
    return (
      `UPDATE \`${table}\` SET \`${column}\` = JSON_ARRAY(\`${column}\`) ` +
      `WHERE \`${column}\` IS NOT NULL AND \`${column}\` <> '' AND LEFT(\`${column}\`, 1) <> '['; ` +
      `UPDATE \`${table}\` SET \`${column}\` = NULL WHERE \`${column}\` = ''; ` +
      `ALTER TABLE \`${table}\` MODIFY \`${column}\` json;`
    );
  }
  return (
    `ALTER TABLE "${table}" ALTER COLUMN "${column}" TYPE json USING ` +
    // The `IS NULL` arm is not redundant with the `= ''` one and is not
    // decoration: `json_build_array(NULL)` is `[null]`, a one-element array, so
    // without it every NULL row silently gains a value. Measured on live
    // Postgres 16.13 while writing this — the arm exists because the version
    // without it was run and produced `[null]`.
    `(CASE WHEN "${column}" IS NULL THEN NULL WHEN "${column}" = '' THEN NULL ` +
    `WHEN "${column}" LIKE '[%' THEN "${column}"::json ` +
    `ELSE json_build_array("${column}") END);`
  );
}

/**
 * Diff one table's metadata fields against its physical columns and return the
 * set of *drift* findings. Metadata is authoritative.
 *
 * Note: a metadata field with no physical column is NOT reported — the
 * additive sync (`ALTER TABLE ADD COLUMN`) already covers added fields, so by
 * the time this runs every expected column exists. We only surface the
 * non-additive divergences the additive sync can never fix.
 */
export function diffManagedTable(args: {
  table: string;
  fields: Record<string, FieldDef>;
  columns: PhysicalColumn[];
  dialect: SqlDialectName;
  /**
   * Which columns an index KEYS ON (commit d0e3a885b), keyed by field name — the exact
   * map {@link indexedKeyColumns} builds. Consulted ONLY by the varchar-length
   * branch below, through {@link varcharColumnChars}, to answer the same
   * question `createColumn` asks before it sizes a text-family column.
   * Omitted (the default), a field reads as unkeyed — the same default the
   * emitter itself takes when nobody supplies one.
   */
  keyedColumns?: ReadonlyMap<string, { unique: boolean }>;
  /**
   * The emitter's OWN read-only mirror — `SqlDriver.varcharColumnChars`,
   * bound by the caller — asked "what `varchar(n)` would `createColumn`
   * actually build for this field, or `null` if it would not build a varchar
   * at all?" (#12732). `SqlDriver.detectTableDrift` supplies it on every real
   * call; a caller that doesn't (this module cannot reach `sql-driver.ts` —
   * see {@link UNBOUNDED_TEXT_FIELD_TYPES}'s note on the cycle) keeps this
   * branch's pre-#12732 behaviour, unconditional on `declaredMaxLength`
   * alone — an intentionally additive default so no existing direct caller of
   * this exported function changes shape under it.
   */
  varcharColumnChars?: (field: FieldDef, keyed?: { unique: boolean }) => number | null;
  /**
   * Has this deployment completed the ADR-0104 column move for the file family
   * (`SqlDriverConfig.fileColumnsMoved`, resolved from
   * `sys_migration.columns_moved_at`)? `SqlDriver.detectTableDrift` supplies
   * its own arm on every real call.
   *
   * OMITTED — the default — means NOT moved, which reproduces this module's
   * pre-addendum verdicts for the family byte for byte: the same intentionally
   * additive posture {@link varcharColumnChars} above takes, and the same
   * direction the driver's own arm fails toward. A caller that has not been
   * threaded the new input therefore keeps reporting a `varchar` media column
   * as the #15771 corruption it still is on an unmoved deployment.
   */
  fileColumnsMoved?: boolean;
  /**
   * The table's tenant column (`SqlDriver.resolveTenantField`): the one column
   * whose nullability the v18 organization-ownership ceremony owns (ruling
   * record 6269955919 on the v18 epic, ② A). The ceremony applies NOT NULL to
   * it on a database it upgraded, and no declaration says so — the injected
   * tenant field is `required: false` with no `storage` — so a NOT NULL on it
   * is the ceremony's verdict, never drift: no `relax_not_null` is proposed
   * for it, and nothing here undoes the ceremony's work. Omitted (the
   * default), every column is judged against its declaration as before.
   */
  ceremonyOwnedNullability?: string | null;
}): ManagedDriftEntry[] {
  const { table, fields, columns, dialect, keyedColumns, varcharColumnChars } = args;
  const ceremonyOwnedNullability = args.ceremonyOwnedNullability ?? null;
  const fileColumnsMoved = args.fileColumnsMoved === true;
  const out: ManagedDriftEntry[] = [];

  const columnsByName = new Map(columns.map((c) => [c.name, c]));
  // Field name → physical column it should produce. Built only for fields that
  // materialise a column, so orphan detection below treats virtual fields as
  // "no column expected".
  const expectedColumns = new Set<string>();

  for (const [fieldName, field] of Object.entries(fields ?? {})) {
    if (BUILTIN_COLUMNS.has(fieldName)) continue;
    if (!fieldHasColumn(field)) continue;
    expectedColumns.add(fieldName);

    const col = columnsByName.get(fieldName);
    if (!col) continue; // additive sync adds it; not drift

    // ── nullability (ADR-0113: compared against `storage.notNull`, the
    // explicit physical constraint — NOT against `required`, which is the
    // write-time contract and implies nothing about the column) ──────────
    const expectNullable = field.storage?.notNull !== true;
    if (expectNullable && !col.nullable && field.required !== true && fieldName !== ceremonyOwnedNullability) {
      // Column STRICTER than its declaration — and the field is not even
      // write-gated, so an omitting write reaches the DB and dies as a raw
      // driver error instead of a clean validation 400. That surprise is
      // worth a human decision; never auto-applied (a `safe` categorisation
      // would let dev auto-reconcile strip a protection someone added).
      //
      // The `required: true` + NOT NULL + no storage declaration case is
      // deliberately SILENT: that is every pre-protocol-17 source after a
      // runtime upgrade, the write gate makes the column constraint
      // unreachable (harmless belt-and-suspenders), and nagging every legacy
      // required field would bury real drift.
      //
      // ⚠️ Nothing ratifies it later either, and this block used to say the
      // opposite — "`os migrate meta` ratifies it whenever the source is next
      // migrated". That died with the ADR-0087 `field-required-notnull-explicit`
      // conversion, WITHDRAWN in #16693 (maintainer ruling 2026-09-08): no
      // chain step writes `storage.notNull` for anybody, at any protocol floor.
      // So the silence above is permanent until the author declares the
      // constraint themselves — which is the ADR-0113 posture (`required` is
      // the write contract; `storage.notNull` alone binds the column), not a
      // gap. The SILENCE itself is unchanged: this PR corrects the sentence,
      // never the behaviour.
      out.push({
        kind: 'nullability_mismatch',
        remoteName: table,
        table,
        column: fieldName,
        expected: 'NULL',
        actual: 'NOT NULL',
        severity: 'warning',
        category: 'needs_confirm',
        op: { type: 'relax_not_null', table, column: fieldName },
        message:
          `${table}.${fieldName}: the column is NOT NULL but the metadata declares no ` +
          `storage constraint. Ratify it by declaring \`storage: { notNull: true }\` ` +
          `yourself — nothing supplies it for you, and \`required: true\` does not imply ` +
          `it (ADR-0113) — or deliberately relax the column via "os migrate".`,
      });
    } else if (!expectNullable && col.nullable) {
      out.push({
        kind: 'nullability_mismatch',
        remoteName: table,
        table,
        column: fieldName,
        expected: 'NOT NULL',
        actual: 'NULL',
        severity: 'error',
        category: 'destructive',
        op: { type: 'tighten_not_null', table, column: fieldName },
        message:
          `${table}.${fieldName}: metadata declares \`storage.notNull\` but the column is ` +
          `nullable — existing nulls must be backfilled.${DESTRUCTIVE_COLUMN_REMEDY.tighten_not_null}`,
      });
    }

    // ── runtime-token column DEFAULT (#4560) ──────────
    // A `defaultValue` the APPLICATION layer owns (`current_user`) must leave
    // the column with no DEFAULT at all. A build that predated the token family
    // passed it through to `col.defaultTo(...)`, so the database now supplies
    // the token's own spelling — a literal `'current_user'` in a
    // `lookup('sys_user')` column — for exactly the writes the engine
    // deliberately left unset. Detected here rather than fixed inline so it
    // travels the same plan/apply road as every other divergence.
    if (isAppResolvedDefaultToken(field.defaultValue) && physicalDefaultIsToken(col.defaultValue, field.defaultValue)) {
      out.push({
        kind: 'default_mismatch',
        remoteName: table,
        table,
        column: fieldName,
        expected: '(no column default)',
        actual: `DEFAULT '${field.defaultValue}'`,
        severity: 'warning',
        // Pure removal: stored rows are untouched and the statement cannot
        // fail, so dev auto-reconcile is welcome to apply it unattended.
        category: 'safe',
        op: { type: 'drop_column_default', table, column: fieldName },
        message:
          `${table}.${fieldName}: the column carries DEFAULT '${field.defaultValue}', but ` +
          `'${field.defaultValue}' is a runtime token the engine resolves per write — the database ` +
          `has been stamping the literal token into every insert that omitted the field. ` +
          `Dropping the default is non-destructive: run "os migrate apply". Rows already holding ` +
          `'${field.defaultValue}' are NOT rewritten — the dangling-reference audit reports them.`,
      });
    }

    // ── base type: a multi-value field over a stale textual column (#11535) ──
    //
    // `createColumn` materialises a multi-value field as `table.json(name)` —
    // its FIRST branch, taken before the field's type or `maxLength` is read at
    // all. On a database created while the field was single-value the column is
    // `varchar`/`text`, and the additive sync (`ALTER TABLE ADD COLUMN`) can
    // never revisit it: nothing here is missing, so nothing is added.
    //
    // Until this branch existed the divergence was reported by NOTHING.
    // Measured on live Postgres 16.13 and MySQL 8.0.46 on the pre-fix tree: a
    // `lookup` field that gained `multiple: true` over an existing
    // `character varying(255)` column produced `detectManagedDrift() === []` and
    // zero `[schema-drift]` log lines, while the very next write stored the
    // literal string `["user_A","user_B"]` into the column and read it back as a
    // string. Downstream code that copies the value into a single-value column
    // then writes that whole string as one id — the silent corruption reported
    // in #11535, which a display-layer glitch it is not.
    //
    // ## Severity `error`, category `needs_confirm` — both MEASURED, and the
    // ## category is load-bearing in a way the words do not suggest
    //
    // ⛔ Do NOT "correct" this to `destructive` to match how bad it sounds.
    // What consumes `category` was measured, not inferred:
    //
    //   - The artifact-pinned boot gate (`runArtifactBootMigrationGate`, on
    //     `kernel:ready` before the HTTP socket opens) refuses the boot for
    //     `category === 'destructive'` and nothing else. Measured: a
    //     `destructive` entry yields `ok=false`; this entry as written yields
    //     `ok=true`. Every database this finding describes is ALREADY SERVING —
    //     that is the premise of the report — so a `destructive` spelling would
    //     convert a running (if corrupt) deployment into a crash-loop on the
    //     next restart. Reporting a corruption must not be the thing that takes
    //     the app down.
    //   - Dev auto-reconcile takes `category === 'safe'` only, so
    //     `needs_confirm` is never applied unattended either.
    //   - `severity` is read by NO gate: the ordinary boot path warns on every
    //     entry regardless of it. It is the render weight (`✗` in
    //     `os migrate plan`), which is why `error` is both honest and free.
    //
    // The residue is stated rather than hidden: `os migrate apply` hands a
    // `needs_confirm` entry to the reconciler, which — having no arm for this op
    // by design — declines it (`applied=0, skipped=1`) and says so.
    //
    // ## The message NAMES the remedy, because there is now a remedy to name
    //
    // Ruled C on #11700 (maintainer, 2026-08-24): the platform warns and ships
    // an explicit, operator-run migration, and never runs it at boot.
    // ⛔ Quoted verbatim, not translated:
    // 「11700 11693 不需要考虑历史数据，其他按照你的建议继续」
    //
    // That command is `os migrate multi-value-columns` (#11733, landed
    // `0e5bea6`), which is what changes this message's job. Before it existed
    // the finding could only DESCRIBE the problem and hand over raw SQL; it
    // opened its remedy with "ObjectStack will NOT change this column for you",
    // a sentence the command falsified. It now names the command first — the
    // route with a dry run, a confirmation prompt, and a post-run re-detection
    // that exits non-zero if the finding has not cleared — and keeps the
    // hand-run statement after it for an operator without the CLI.
    //
    // ⚠️ The raw statement stays in the message VERBATIM, and that is a
    // contract, not prose: `planStaleColumnTargets`
    // (packages/cli/src/commands/migrate/multi-value-columns.ts) recovers the
    // DIALECT by testing `message.includes(manualJsonConversionSql(d, …))` for
    // each corrupting dialect — a `ManagedDriftEntry` carries no dialect of its
    // own. Reword this message so the statement no longer appears character for
    // character and the command refuses every finding with
    // `remedy_not_recognized`, i.e. the remedy this text points at stops
    // working. Pinned from this side by the `toContain(manualJsonConversionSql(…))`
    // cases in `schema-drift.base-type-mismatch.test.ts`.
    //
    // ── #15771: the predicate is the WRITER'S, not `multiple` alone ─────────
    //
    // See {@link JSON_COLUMN_FIELD_TYPES}: the emitter gives a json column to
    // every JSON-class TYPE, `multiple` or not, so a single-value `file` /
    // `location` / `record` / `vector` field on a `varchar` column was written
    // as JSON and reported by nothing. Measured on the pre-fix tree, one
    // `diffManagedTable` call per type: all fifteen JSON-class types the spec
    // declares returned ZERO entries over a `character varying(2048)` column on
    // `postgres` and `mysql`, while the same column under `{ multiple: true }`
    // returned one — the differ was working and this whole population was
    // invisible to it.
    //
    // ## Why the message SPLITS, and why the array half is byte-identical
    //
    // The remedy named below repairs a stale column by WRAPPING each stored
    // value in a one-element JSON array — `json_build_array(col)` /
    // `JSON_ARRAY(col)`, the ELSE arm of {@link manualJsonConversionSql}. That
    // is the right repair for a field whose value IS an array and the wrong one
    // for a field whose value is a scalar or an object: the write path
    // `JSON.stringify`s a single-value `file` id into the column, so it holds
    // the JSON scalar `"file_01HXYZ"` — measured byte-for-byte on an in-memory
    // SQLite cell — and wrapping that yields `["\"file_01HXYZ\""]`, a shape
    // the field never declared. Measured with the command's OWN planner: a
    // single-value finding carrying this message is accepted as a target and
    // planned with `json_build_array`.
    //
    // So both populations are REPORTED and only one is offered the remedy. The
    // array half keeps its message CHARACTER FOR CHARACTER — the contract note
    // above is why — and the single-value half carries neither the command nor
    // the statement, so the same probe recovers no dialect and the command
    // REFUSES the entry (`remedy_not_recognized`) rather than run array SQL over
    // scalar rows. That refusal is the command's designed branch for a message
    // it cannot read, and it is pinned from both sides.
    const declaredType = field.type || 'string';
    // The FILE family is asked per deployment rather than by set membership —
    // see {@link JSON_COLUMN_FIELD_TYPES}. `multiple: true` media is a list of
    // ids and stays a json column on every deployment, which the second
    // disjunct already covers on its own.
    // [#17469] The `multiple` disjunct asks `@objectstack/spec`'s
    // `isMultiValueField`, the predicate the writer now asks — a differ that
    // read the flag raw would report a conversion to a column shape the
    // platform would never create, which is the `⊆` direction this file's
    // header calls out.
    const declaresMultiValue = isMultiValueField({ type: declaredType, multiple: field.multiple === true });
    const declaresJsonColumn =
      JSON_COLUMN_FIELD_TYPES.has(declaredType)
      || declaresMultiValue
      || (!fileColumnsMoved && FILE_REFERENCE_TYPES.has(declaredType));
    // Is the declared VALUE an array? A multi-valued field, plus the
    // inherently-multi option types, whose value is a list with or without the
    // flag (`MULTI_OPTION_TYPES` — the spec's own class, and already inside
    // `isMultiValueField`). This, and never JSON-class membership (which both
    // populations share), is what decides whether the wrapping remedy is the
    // right repair.
    const declaresArray = declaresMultiValue || MULTI_OPTION_TYPES.has(declaredType);
    if (declaresJsonColumn && jsonColumnTypeIsLoadBearing(dialect) && acceptsStringifiedJson(col.type)) {
      out.push({
        kind: 'type_mismatch',
        remoteName: table,
        table,
        column: fieldName,
        expected: 'json',
        actual: col.type,
        severity: 'error',
        category: 'needs_confirm',
        op: { type: 'manual_column_type_change', table, column: fieldName, to: 'json', from: col.type },
        message: declaresArray
          ? `${table}.${fieldName}: metadata declares a multi-value field (stored as \`json\`) but the ` +
          `column is \`${col.type}\` — the database was created while the field was single-value and the ` +
          `additive sync never migrates a column's type. Arrays are being written as the STRINGIFIED ` +
          `literal (e.g. '["a","b"]') and read back as a string, so anything consuming the value ` +
          `receives one opaque id instead of a list. REMEDY: run ` +
          `"${MULTI_VALUE_COLUMN_REMEDY_COMMAND}" — it is a DRY RUN by default that executes nothing ` +
          `and prints the statements; take a backup, then re-run it with --apply. ObjectStack never ` +
          `migrates this column on its own: the boot path only reports it and "os migrate apply" ` +
          `skips it, so nothing changes until you run that command. To do it by hand instead, in a ` +
          `transaction and with a backup taken first — dropping any index on the column first, since ` +
          `a json column cannot carry a plain btree: ` +
          `${manualJsonConversionSql(dialect, table, fieldName)} ` +
          `Rows written while the column was stale may already hold a stringified array in a RELATED ` +
          `single-value column; neither route repairs those.`
          : `${table}.${fieldName}: metadata declares \`${declaredType}\`, a field ObjectStack stores ` +
            `in a \`json\` column whether or not it is multi-value, but the column is \`${col.type}\` ` +
            `and the additive sync never migrates a column's type. The value is written JSON-ENCODED ` +
            `into that textual column — a single-value \`file\` id lands as the quoted text ` +
            `'"file_01HXYZ"' — while on this dialect the read path decodes a json COLUMN rather than ` +
            `the text inside a textual one, so it comes back with its quotes and every consumer that ` +
            `matches the raw stored form (file resolution, ownership claims) refuses it. ` +
            `The automated column migration ObjectStack ships is NOT offered for this column and will ` +
            `refuse it: it repairs a stale column by wrapping each value in a ONE-ELEMENT JSON ARRAY, ` +
            `which is right for a field declaring \`multiple: true\` and wrong here — it would leave ` +
            `an array where the declaration promises a single value. Convert the column by hand ` +
            `instead, in a transaction and with a backup taken first: CAST the stored text to json ` +
            `rather than wrapping it — rows written through ObjectStack already hold valid JSON — and ` +
            `drop any index on the column first, since a json column cannot carry a plain btree. ` +
            `"os migrate plan" lists this entry; "os migrate apply" reports it as skipped.`,
      });
    }

    // ── varchar length (only where the dialect enforces it) ──────────
    //
    // `maxLength` must be a POSITIVE INTEGER to be a bound (#11431). Without
    // that predicate this branch read a malformed declaration as authoritative
    // and planned DDL no server will accept: `maxLength: 0` took the narrowing
    // arm (`0 > col.maxLength` is false) and asked for `varchar(0)`, and
    // `maxLength: 12.5` asked for `varchar(12.5)` — both reported at severity
    // `error`, category `destructive`, i.e. as work `os migrate apply
    // --allow-destructive` should go do.
    //
    // It is the same predicate the EMITTER applies
    // (`SqlDriver.declaredVarcharLength`, and `keyableTextLength` before it):
    // a malformed bound is treated as no bound at all, and the column keeps
    // its default width. Sharing the predicate is the point — the two halves
    // disagreeing about which declarations count is the defect class #11431
    // exists to close, and a differ that still honoured a malformed
    // `maxLength` would have re-opened it one case to the left.
    //
    // A MULTI-VALUE field is excluded for the same reason and by the same
    // authority: `createColumn` returns at `if (field.multiple) { table.json();
    // return; }` BEFORE `maxLength` is consulted, so the emitter provably never
    // gives such a field a declared width, and a differ that honours one is the
    // two-halves-disagree defect #11431 exists to close — one case to the left
    // again. Measured on the pre-fix tree, `{ multiple: true, maxLength: 50 }`
    // over a stale `varchar(255)` column reported `narrow_varchar` at severity
    // `error`, category **destructive** on both enforcing dialects: a finding
    // that refuses the artifact-pinned boot and invites `os migrate apply
    // --allow-destructive` to rewrite the column to `varchar(50)` — the exact
    // OPPOSITE of the repair the column needs, which is `json`. That shape is
    // now reported once, correctly, by the base-type branch above.
    const declaredMaxLength =
      typeof field.maxLength === 'number' && Number.isInteger(field.maxLength) && field.maxLength > 0
        ? field.maxLength
        : undefined;
    // ── #12732: would `createColumn` even make this a VARCHAR? ───────
    //
    // `declaredMaxLength` alone answers "did the author write a bound?" — it
    // says nothing about whether the emitter honours that bound as a varchar
    // width at all, and it disagreed with `createColumn` in two measured
    // directions: an UNKEYED bounded text-family field (`text` / `richtext` /
    // `signature` / `markdown` / …) is TEXT regardless of its declared bound
    // (`keyableTextLength` returns `null` unkeyed), and a base string-family
    // field (`email` / `url` / `password` / …) bounded PAST the varchar
    // ceiling is TEXT too (`declaredVarcharLength` returns `null` above
    // `MAX_VARCHAR_CHARS`). Both were nonetheless diffed as `varchar(N)`
    // against the physical column — a `narrow_varchar` at `destructive` for
    // the first (refusing the boot of an already-serving deployment over a
    // divergence the write seam already enforces), a `widen_varchar` at
    // `safe` for the second (planning `ALTER … varchar(100000)`, DDL MySQL
    // refuses outright).
    //
    // Asking `varcharColumnChars` — the emitter's own read-only mirror,
    // rather than a second copy of its switch — answers both at once: `null`
    // means "the emitter would not make this a varchar", which is the honest
    // expectation in both directions, so the branch below simply does not
    // fire. See the parameter doc above for why an OMITTED mirror leaves this
    // `true` rather than `false` — additive, not a silent behaviour change
    // for a caller that hasn't been threaded the new input yet.
    const emitterWouldVarchar =
      varcharColumnChars === undefined || declaredMaxLength === undefined
        ? true
        : varcharColumnChars(field, keyedColumns?.get(fieldName)) !== null;
    if (
      enforcesVarcharLength(dialect) &&
      !declaresJsonColumn &&
      declaredMaxLength !== undefined &&
      emitterWouldVarchar &&
      isCharacterColumn(col.type) &&
      typeof col.maxLength === 'number' &&
      declaredMaxLength !== col.maxLength
    ) {
      if (declaredMaxLength > col.maxLength) {
        out.push({
          kind: 'type_mismatch',
          remoteName: table,
          table,
          column: fieldName,
          expected: `varchar(${declaredMaxLength})`,
          actual: `varchar(${col.maxLength})`,
          severity: 'warning',
          category: 'safe',
          op: { type: 'widen_varchar', table, column: fieldName, to: declaredMaxLength, from: col.maxLength },
          message: `${table}.${fieldName}: metadata allows ${declaredMaxLength} chars but the column caps at ${col.maxLength} — widen via "os migrate".`,
        });
      } else {
        out.push({
          kind: 'type_mismatch',
          remoteName: table,
          table,
          column: fieldName,
          expected: `varchar(${declaredMaxLength})`,
          actual: `varchar(${col.maxLength})`,
          severity: 'error',
          category: 'destructive',
          op: { type: 'narrow_varchar', table, column: fieldName, to: declaredMaxLength, from: col.maxLength },
          message: `${table}.${fieldName}: metadata caps at ${declaredMaxLength} chars but the column allows ${col.maxLength} — narrowing may truncate.${DESTRUCTIVE_COLUMN_REMEDY.narrow_varchar}`,
        });
      }
    }

    // ── an UNBOUNDED text-family field over a pre-existing varchar (#12121) ──
    //
    // The exact COMPLEMENT of the branch above: that one REQUIRES
    // `declaredMaxLength !== undefined`, so on a pre-existing table the two
    // partition the text family by whether its author wrote a number.
    //
    // Until this branch existed the undeclared half was reported by NOTHING, and
    // that half is the common case. Measured on the pre-fix tree, one
    // `diffManagedTable` call per type: a `text` / `textarea` / `html` /
    // `markdown` / `richtext` / `code` / `signature` / `qrcode` field with no
    // `maxLength` over a `character varying(255)` column returned **zero**
    // entries on both enforcing dialects, while `{ type: 'signature', maxLength:
    // 4096 }` over the same column returned `widen_varchar` in the same run — so
    // the differ was working and this shape was simply invisible to it.
    //
    // What that silence costs: after #11875/#12119 a NEWLY created column for
    // these types is TEXT and holds a data URI correctly, but the additive sync
    // never revisits an existing column, so a deployment upgrading into that
    // release gets no change AND no diagnostic. The server keeps refusing the
    // same write, and the refusal is a poor substitute for a report: the live
    // probe behind `types`' `driver-fault-redaction.ts` measured Postgres's
    // `22001` as identifier-only and naming the TYPE rather than the column
    // (`value too long for type character varying(255)`), MySQL's `1406` as
    // `Data too long for column 'label' at row 1`. Meanwhile every
    // drift-reporting road in the platform — `os migrate plan`, `os migrate
    // apply`, the artifact-pinned boot gate, the boot-time `[schema-drift]` warn
    // — reads THIS function, so the one place that could have named the column
    // and the cause named nothing at all.
    //
    // ## Why this needs no keyed-column input
    //
    // `createColumn` sizes a text-family column as `keyed ?
    // keyableTextLength(field) : null`, and `keyableTextLength` returns `null`
    // for a field with no positive-integer bound. So for the fields this branch
    // SELECTS the emitter answers TEXT whether or not an index keys them: the
    // differ does not have to know, and cannot be wrong about it. Pinned as such
    // — see {@link UNBOUNDED_TEXT_FIELD_TYPES}.
    //
    // ## Severity `error`, category `needs_confirm` — and the category is the
    // ## load-bearing half, exactly as it is for the base-type branch above
    //
    // ⛔ Do NOT "correct" `needs_confirm` to `destructive` to match how bad it
    // sounds. `runArtifactBootMigrationGate` refuses a boot for `category ===
    // 'destructive'` and for nothing else, and every database this finding
    // describes is ALREADY SERVING — that is the premise of the report. A
    // `destructive` spelling would convert a deployment that merely refuses
    // over-long values into a crash-loop on its next restart. `safe` is wrong in
    // the other direction: dev auto-reconcile applies `safe` entries unattended
    // and there is no arm to apply.
    //
    // `severity` is read by NO gate — it is render weight — and `error` is the
    // honest weight for the same reason the base-type branch takes it: there is
    // no automatic repair, so the operator has to act.
    if (
      enforcesVarcharLength(dialect) &&
      !declaresJsonColumn &&
      declaredMaxLength === undefined &&
      UNBOUNDED_TEXT_FIELD_TYPES.has(field.type || 'string') &&
      isCharacterColumn(col.type) &&
      typeof col.maxLength === 'number'
    ) {
      out.push({
        kind: 'type_mismatch',
        remoteName: table,
        table,
        column: fieldName,
        expected: 'text',
        actual: `varchar(${col.maxLength})`,
        severity: 'error',
        category: 'needs_confirm',
        op: { type: 'manual_widen_varchar_to_text', table, column: fieldName, to: 'text', from: col.type },
        message:
          `${table}.${fieldName}: metadata declares \`${field.type || 'string'}\` with no ` +
          `\`maxLength\`, so ObjectStack creates this column as TEXT — but the existing column is ` +
          `\`varchar(${col.maxLength})\` and the additive sync never changes a column's type. The ` +
          `column still caps at ${col.maxLength} characters, so the server refuses longer values the ` +
          `declaration ALLOWS (Postgres 22001, MySQL ER_DATA_TOO_LONG) — a data URI in a ` +
          `\`signature\`/\`qrcode\` field, or an ordinary rich-text body, is routinely past it. ` +
          `ObjectStack does NOT migrate this column: "os migrate apply" reports this entry ` +
          `as skipped. Two operator routes — declare a \`maxLength\` this dialect can express, which ` +
          `turns this into the widen op "os migrate apply" performs; or convert the column to TEXT by ` +
          `hand, with a backup taken first, restating the FULL column definition on MySQL (MODIFY ` +
          `drops a NOT NULL or DEFAULT you do not repeat) and dropping any index that keys the column ` +
          `first, since MySQL cannot key a TEXT column without a prefix length.`,
      });
    }
  }

  // ── orphaned columns (physical column, no metadata field) ──────────
  for (const col of columns) {
    if (BUILTIN_COLUMNS.has(col.name)) continue;
    // Driver-owned hash shadow (#11627), never a metadata field — see
    // {@link isHashShadowColumn} for why dropping it as an orphan would
    // silently disable the UNIQUE constraint it carries.
    if (isHashShadowColumn(col.name)) continue;
    if (expectedColumns.has(col.name)) continue;
    out.push({
      kind: 'unmapped_column',
      remoteName: table,
      table,
      column: col.name,
      expected: '(absent)',
      actual: col.type,
      severity: 'warning',
      category: 'destructive',
      op: { type: 'drop_column', table, column: col.name },
      message:
        `${table}.${col.name}: column exists in the database but not in metadata (orphaned)` +
        DESTRUCTIVE_COLUMN_REMEDY.drop_column,
    });
  }

  return out;
}

/** Stable de-dup / sort key for a drift entry. */
export function driftKey(d: ManagedDriftEntry): string {
  const op: any = d.op;
  // Several index findings can share a table+column+kind (a table can have more
  // than one index over the same leading column), so the index name has to be
  // part of the key or the boot-time warn de-dup swallows all but the first.
  const idx = op.indexName ?? op.createIndexName ?? '';
  return `${d.table}.${d.column ?? ''}:${d.kind}:${d.op.type}${idx ? `:${idx}` : ''}`;
}

// ───────────────────────────────────────────────────────────────────────
// Index dimension (#3728)
//
// `diffManagedTable` above is column-only, which left one whole class of
// divergence invisible to `os migrate plan`: indexes. The #3696 unique-scope
// migration used to paper over that by executing its DROP + CREATE inline at
// boot — DDL nobody could pre-inspect, in every environment, in violation of
// the #2186 rule that a managed schema is never auto-altered in production.
// Everything below exists so that migration (and declared-index drift
// generally) is *detected* rather than silently performed, and reconciled
// through the same `os migrate plan` / `apply` path as column drift.
// ───────────────────────────────────────────────────────────────────────

/** Identifier budget for generated index names (Postgres caps at 63, MySQL 64). */
const INDEX_NAME_MAX = 60;
/** Chars kept from `<prefix>_<table>` before the `_<hash8>` suffix of a truncated name. */
const INDEX_NAME_HEAD = INDEX_NAME_MAX - 9;

/**
 * Build a deterministic index name so repeated syncs converge on the same
 * identifier (and an already-materialized index is recognisable by name).
 * Long names are hash-suffixed to stay inside the dialect identifier limits.
 *
 * This is the single definition — `SqlDriver.buildIndexName` delegates here, so
 * the names the driver *creates* and the names the differ *looks for* cannot
 * drift apart.
 */
export function buildIndexName(table: string, columns: string[], unique: boolean): string {
  const prefix = unique ? 'uniq' : 'idx';
  const base = `${prefix}_${table}_${columns.join('_')}`;
  if (base.length <= INDEX_NAME_MAX) return base;
  const hash = createHash('sha1').update(base).digest('hex').slice(0, 8);
  return `${`${prefix}_${table}`.slice(0, INDEX_NAME_HEAD)}_${hash}`;
}

/** An index metadata says should exist. */
export interface ExpectedIndex {
  name: string;
  columns: string[];
  unique: boolean;
  /**
   * Columns (by identity, always a subset of {@link columns}) whose key part
   * materializes as the NULL-safe organization form
   * `COALESCE(<column>, '__global__')` rather than the bare column
   * (ADR-0120 D3). Practically this is the table's tenant column on every
   * organization-scoped unique. Absent/empty for plain indexes.
   */
  nullSafeColumns?: string[];
}

// ───────────────────────────────────────────────────────────────────────
// Physical index key parts (#4884)
//
// Introspection used to read an index's key from the dialect's *column* view
// (`PRAGMA index_info`, `pg_attribute`, `STATISTICS.COLUMN_NAME`), which
// reports NOTHING for an expression key. A four-column index whose last key is
// `COALESCE(package_id,'')` therefore read as three columns, and the differ
// reported a mismatch against a four-column declaration on a database that was
// exactly right. Everything below exists so the key is read as written, and so
// an expression that pins one column is recognised as that column.
// ───────────────────────────────────────────────────────────────────────

/** One key part of a physical index, as introspection recovered it. */
export type IndexKeyPart =
  | { kind: 'column'; column: string }
  /** `column` is the identity the expression pins, or null when unattributable. */
  | { kind: 'expression'; sql: string; column: string | null };

const BARE_IDENTIFIER = /^(?:"([^"]+)"|`([^`]+)`|\[([^\]]+)\]|([A-Za-z_][A-Za-z0-9_$]*))$/;
/**
 * A literal any dialect might print inside `COALESCE`, optional `::type` cast
 * included. MySQL's `information_schema.STATISTICS.EXPRESSION` decorates a
 * string literal with a charset introducer and backslash-escaped quotes
 * (`_utf8mb4\'__global__\'`), so both decorations are accepted too — the
 * attribution is deliberately literal-AGNOSTIC (#4884, ADR-0120 D3): what the
 * literal says never changes which column the key part pins.
 */
const SQL_LITERAL =
  /^(?:(?:_[A-Za-z][A-Za-z0-9]*\s*)?\\?'(?:[^'\\]|''|\\.)*\\?'|-?\d+(?:\.\d+)?|null|true|false)(?:::[A-Za-z_][A-Za-z0-9_ ."]*)?$/i;

/** Unwrap `"x"` / `` `x` `` / `[x]`, or null when `s` is not a single identifier. */
function matchIdentifier(s: string): string | null {
  const m = BARE_IDENTIFIER.exec(s.trim());
  if (!m) return null;
  return m[1] ?? m[2] ?? m[3] ?? m[4] ?? null;
}

/** Drop a trailing `::type` cast (Postgres prints them everywhere). */
function stripCast(s: string): string {
  const i = s.indexOf('::');
  return i > 0 ? s.slice(0, i).trim() : s.trim();
}

/** Peel redundant wrapping parens: `(package_id)` → `package_id`. */
function stripWrappingParens(s: string): string {
  let out = s.trim();
  while (out.startsWith('(') && out.endsWith(')')) {
    const inner = out.slice(1, -1).trim();
    if (splitTopLevelList(inner).length !== 1) break;
    out = inner;
  }
  return out;
}

/**
 * Peel casts and redundant parens until stable, then read the identifier.
 * Postgres nests both — a varchar column inside `COALESCE` prints as
 * `(package_id)::text` — so one pass in either order is not enough.
 */
function bareColumnOf(s: string): string | null {
  let cur = s.trim();
  for (let i = 0; i < 4; i++) {
    const next = stripWrappingParens(stripCast(stripWrappingParens(cur)));
    if (next === cur) break;
    cur = next;
  }
  return matchIdentifier(cur);
}

/**
 * Split a comma-separated SQL list at TOP level only — a comma inside nested
 * parens or inside a quoted literal/identifier belongs to the part, not to the
 * list. `a, b, COALESCE(c, '')` → `['a', 'b', "COALESCE(c, '')"]`.
 */
function splitTopLevelList(list: string): string[] {
  const out: string[] = [];
  let depth = 0;
  let quote: string | null = null;
  let start = 0;
  for (let i = 0; i < list.length; i++) {
    const ch = list[i];
    if (quote) {
      // SQL escapes a quote by doubling it; skipping the pair keeps us in-quote.
      if (ch === quote) {
        if (list[i + 1] === quote) i++;
        else quote = null;
      }
      continue;
    }
    if (ch === "'" || ch === '"' || ch === '`') quote = ch;
    else if (ch === '(') depth++;
    else if (ch === ')') depth--;
    else if (ch === ',' && depth === 0) {
      out.push(list.slice(start, i).trim());
      start = i + 1;
    }
  }
  const tail = list.slice(start).trim();
  if (tail) out.push(tail);
  return out;
}

/** Strip the per-key decorations `pg_get_indexdef` prints (`DESC`, `COLLATE …`). */
function stripKeyPartModifiers(part: string): string {
  let s = part.trim();
  s = s.replace(/\s+nulls\s+(?:first|last)$/i, '');
  s = s.replace(/\s+(?:asc|desc)$/i, '');
  s = s.replace(/\s+collate\s+(?:"[^"]+"|[A-Za-z_][A-Za-z0-9_$.]*)$/i, '');
  return s.trim();
}

/**
 * Classify one physical index key part.
 *
 * The single equivalence this recognises is `COALESCE(col, <literal>)` ≡ `col`,
 * and it is recognised deliberately, not liberally. ADR-0048 makes that form
 * the canonical spelling of the overlay key: a plain UNIQUE index treats NULLs
 * as distinct, so package-less globals would not be unique among themselves,
 * and `COALESCE(package_id,'')` is how the runtime pins them. The expression
 * therefore keys on *exactly* `package_id` — for identity purposes it is that
 * column, and a UNIQUE index over it is strictly STRONGER than the same key
 * spelled plainly. Reading it as a missing column is what produced the false
 * "declares 4 columns, has 3" warning in #4884.
 *
 * Nothing else is coerced. An expression this cannot attribute to one column
 * stays `{ column: null }`, which keeps the index out of every claim the
 * differ makes ({@link isSyncReproducibleIndex}) rather than guessing at it.
 */
export function classifyIndexKeyPart(rawPart: string): IndexKeyPart {
  const s = stripKeyPartModifiers(rawPart);
  const bare = matchIdentifier(s);
  if (bare !== null) return { kind: 'column', column: bare };

  const coalesce = /^coalesce\s*\(([\s\S]*)\)$/i.exec(stripWrappingParens(s));
  if (coalesce) {
    const args = splitTopLevelList(coalesce[1]);
    if (args.length >= 2) {
      const column = bareColumnOf(args[0]);
      const restAreLiterals = args.slice(1).every((a) => SQL_LITERAL.test(a.trim()));
      if (column !== null && restAreLiterals) return { kind: 'expression', sql: rawPart, column };
    }
  }
  return { kind: 'expression', sql: rawPart, column: null };
}

/** An index definition recovered from its `CREATE INDEX` text. */
export interface ParsedIndexDdl {
  /** Ordered key parts exactly as written — a column name, or an expression. */
  keyParts: string[];
  /** The definition carries a `WHERE` predicate (a partial index). */
  partial: boolean;
}

/**
 * Parse the key list and partial predicate out of a `CREATE INDEX` statement.
 *
 * Used for the two dialects that hand back the definition verbatim —
 * `sqlite_master.sql` and `pg_get_indexdef()` — because their per-column
 * catalogue views cannot express an expression key at all. Quote-aware, so a
 * comma or paren inside a string literal never splits a key part.
 */
export function parseIndexDdl(sql: string): ParsedIndexDdl | null {
  if (typeof sql !== 'string' || sql.length === 0) return null;
  let depth = 0;
  let quote: string | null = null;
  let open = -1;
  let close = -1;
  for (let i = 0; i < sql.length; i++) {
    const ch = sql[i];
    if (quote) {
      if (ch === quote) {
        if (sql[i + 1] === quote) i++;
        else quote = null;
      }
      continue;
    }
    if (ch === "'" || ch === '"' || ch === '`') quote = ch;
    else if (ch === '(') {
      if (depth === 0) open = i;
      depth++;
    } else if (ch === ')') {
      depth--;
      if (depth === 0) {
        close = i;
        break;
      }
    }
  }
  if (open < 0 || close < 0) return null;
  const keyParts = splitTopLevelList(sql.slice(open + 1, close));
  if (keyParts.length === 0) return null;
  return { keyParts, partial: /(?:^|[\s)])where[\s(]/i.test(sql.slice(close + 1)) };
}

/**
 * Fold parsed key parts into a {@link PhysicalIndex}, in key order: attributable
 * parts land in `columns`, expression parts are additionally recorded verbatim
 * so {@link isSyncReproducibleIndex} can see them.
 */
export function applyIndexKeyParts(index: PhysicalIndex, rawParts: string[]): void {
  for (const raw of rawParts) {
    const part = classifyIndexKeyPart(raw);
    if (part.kind === 'column') {
      index.columns.push(part.column);
      continue;
    }
    (index.expressions ??= []).push(part.sql);
    if (part.column !== null) {
      index.columns.push(part.column);
      // An attributable expression is by construction the COALESCE(col,
      // <literal>) form — record the column so the differ can compare the key
      // part's FORM, not just its identity (ADR-0120 D3: the NULL-safe
      // organization key part vs the bare column are different constraints).
      (index.nullSafeColumns ??= []).push(part.column);
    }
  }
}

/** An index that physically exists (see `SqlDriver.introspectIndexes`). */
export interface PhysicalIndex {
  name: string;
  /**
   * The column identities the index keys on, in key order.
   *
   * An EXPRESSION key part contributes the column it resolves to when
   * {@link classifyIndexKeyPart} can attribute it to one (`COALESCE(pkg,'')` →
   * `pkg`, #4884) — the column IS in the key, it is merely written as an
   * expression, and dropping it here is what made a healthy database read as
   * three-column drift against a four-column declaration. A part that resolves
   * to no single column is omitted (and recorded in {@link expressions}).
   */
  columns: string[];
  unique: boolean;
  /** Backing index of the PRIMARY KEY — never metadata-managed. */
  primary?: boolean;
  /**
   * The index is restricted by a `WHERE` predicate (a SQLite / Postgres partial
   * index). See {@link isSyncReproducibleIndex} for why this is load-bearing.
   */
  partial?: boolean;
  /**
   * Key parts written as SQL EXPRESSIONS rather than bare columns, verbatim and
   * in key order. Empty/absent for an ordinary index. See
   * {@link isSyncReproducibleIndex}.
   */
  expressions?: string[];
  /**
   * The columns that expression key parts ATTRIBUTE to — i.e. every key part
   * of the recognised `COALESCE(col, <literal>)` form contributes its column
   * here (and to {@link columns}). Lets the differ tell the NULL-safe
   * organization key part (ADR-0120 D3) apart from the bare column while
   * staying literal-agnostic. A subset of {@link columns}; absent when every
   * key part is a plain column.
   */
  nullSafeColumns?: string[];
  /**
   * When this index is physically carried by a #11627 hash shadow, the
   * DECLARED key parts that shadow hashes, read back from the generation
   * expression (commit cd1348802 via commit df1c75c4b) by `SqlDriver.introspectIndexes`.
   *
   * Absent both when the index is NOT shadow-carried and when it is but the
   * expression could not be read. {@link isHashShadowCarrier} tells those two
   * apart — the differ compares the resolved key, and treats the unresolved
   * carrier as not-ours-to-reconcile rather than as drift.
   */
  shadowKey?: HashShadowKeyPart[];
}

/**
 * Could the additive index sync have produced this exact physical index — and,
 * decisively, could it RECREATE it after a drop? (#4884)
 *
 * Every remedy this module proposes for an index rests on that second half.
 * `drop_index` is safe only because a still-declared index would be
 * re-materialized on the next sync; `recreate_index` drops before it creates.
 * `syncDeclaredIndexes` builds indexes through knex's `table.unique(fields)` /
 * `table.index(fields)` — plain columns, no predicate, no expressions — so an
 * index carrying either is one it can NEITHER have created NOR rebuild. Naming
 * it drift asserts an authorship we do not have, and pointing
 * `--allow-destructive` at it proposes an unrecoverable drop.
 *
 * That is exactly what a fresh `app-showcase` boot did to
 * `idx_sys_metadata_overlay_draft`: the ADR-0048 partial UNIQUE index the
 * runtime creates for draft-overlay uniqueness matched no declaration, carried
 * ObjectStack's `idx_<table>_` naming, and was therefore reported as an orphan
 * to be dropped — i.e. the boot advised destroying a live data-integrity
 * guarantee the same boot had just created.
 *
 * Since ADR-0120 D3 the sync's own vocabulary includes ONE expression shape:
 * the NULL-safe organization key part `COALESCE(<tenantField>, '__global__')`.
 * An index whose every expression is that form — attributed to the table's
 * OWN tenant column — is therefore reproducible again (pass `tenantField`).
 * The attribution is deliberately column-scoped: `COALESCE(package_id, '')`
 * (the ADR-0048 overlay key) attributes to a non-tenant column and stays out,
 * exactly as before — loosening this to "any attributable COALESCE" would
 * resurrect the #4884 false-orphan on the overlay indexes.
 */
export function isSyncReproducibleIndex(index: PhysicalIndex, tenantField?: string | null): boolean {
  if (index.partial === true) return false;
  const expressions = index.expressions?.length ?? 0;
  if (expressions === 0) return true;
  if (!tenantField) return false;
  const nullSafe = index.nullSafeColumns ?? [];
  return nullSafe.length === expressions && nullSafe.every((c) => c === tenantField);
}

/**
 * Is this index the framework's to manage rather than the additive sync's?
 *
 * Two independent witnesses, either of which is sufficient:
 *  1. `runtimeCreated` — this very process executed the `CREATE INDEX` through
 *     the driver's raw `execute()` seam. A database created seconds ago by this
 *     build cannot be drifted from its own declaration, so a remedy here is
 *     false by construction.
 *  2. The index is not {@link isSyncReproducibleIndex} — durable across
 *     restarts, and the only witness available on the second boot, when the
 *     runtime ledger starts empty again.
 */
export function isRuntimeManagedIndex(
  index: PhysicalIndex,
  runtimeCreated?: ReadonlySet<string>,
  tenantField?: string | null,
): boolean {
  return runtimeCreated?.has(index.name) === true || !isSyncReproducibleIndex(index, tenantField);
}

/**
 * Translate field-level `unique` declarations into concrete index descriptors,
 * applying tenant scoping (#3696).
 *
 * This is the ONLY place field-level uniqueness becomes an index, so the
 * create-table, alter-table, SQLite-rebuild and drift-detection paths cannot
 * disagree about what a `unique: true` field is supposed to produce.
 *
 * Scoping rule (ADR-0120 D1/D3):
 *   - `unique: 'global'` → single-column `(field)`, platform-wide.
 *   - `unique: true` / `unique: 'organization'` on a tenant-scoped table →
 *     composite `(tenantField, field)`: unique *within* the organization,
 *     matching the per-tenant autonumber sequence, the RLS read predicate and
 *     the write-path tenant stamp. The organization key part is the plain
 *     column (ADR-0131 D1: the tenant column is NOT NULL, so no row escapes
 *     the NULL-distinct UNIQUE). ADR-0120 D3's `COALESCE(tenantField,
 *     '__global__')` form, which folded NULL-organization rows into one bucket
 *     (#5030), is what earlier releases built; the differ reads it as
 *     equivalent on a certified database.
 *   - `unique: true` / `'organization'` with no tenant column → single-column
 *     `(field)`.
 *
 * The tenant column comes FIRST in the composite so the index also serves the
 * `WHERE tenant = ?` prefix scans every tenant-scoped read issues.
 */
export function uniqueIndexesFromFields(
  table: string,
  fields: Record<string, any>,
  tenantField: string | null,
): ExpectedIndex[] {
  const out: ExpectedIndex[] = [];
  for (const [name, field] of Object.entries<any>(fields ?? {})) {
    if (!isUniqueScopeDeclared(field?.unique)) continue;
    // A unique declaration ON the tenant column itself ("one row per tenant")
    // cannot be tenant-scoped — `(organization_id, organization_id)` is not a
    // constraint. Keep it single-column.
    const scoped =
      isOrganizationScopedUnique(field.unique) && tenantField != null && tenantField !== name;
    const columns = scoped ? [tenantField, name] : [name];
    out.push({ name: buildIndexName(table, columns, true), columns, unique: true });
  }
  return out;
}

/** The declared-index shape this module normalizes (spec's `IndexSchema` + the driver-side extras). */
export interface DeclaredIndexInput {
  name?: string;
  fields?: string[];
  /** `'organization'` is the ADR-0120 D1 explicit per-organization scope; see {@link isUniqueScopeDeclared}. */
  unique?: boolean | 'global' | 'organization';
  /** Pre-resolved NULL-safe key parts — used by the drift-op apply path, which re-feeds already-normalized shapes. */
  nullSafeColumns?: string[];
}

/**
 * Normalize one entry of an object's declared `indexes[]`, or null if unusable.
 *
 * Scope handling (ADR-0120 D1/D3):
 *   - `unique: true` / `'global'` → the listed columns, VERBATIM — the #3696
 *     contract, now the `'global'` arm of the explicit vocabulary.
 *   - `unique: 'organization'` → the organization key part is PREPENDED to the
 *     listed columns as the plain tenant column (ADR-0131 D1), resolved
 *     against the table's tenant column at registration — the one place
 *     tenancy is knowable. With no tenant column the index degrades to the
 *     listed columns alone, mirroring field-level behavior (S11). A listed
 *     column that IS the tenant column is not prepended again (the
 *     hand-written S6 spelling).
 *   - An input already carrying `nullSafeColumns` (an op normalized before
 *     ADR-0131 D1) is honoured verbatim.
 */
export function normalizeDeclaredIndex(
  table: string,
  idx: DeclaredIndexInput | undefined,
  tenantField?: string | null,
): ExpectedIndex | null {
  const listed = Array.isArray(idx?.fields)
    ? idx.fields.filter((f): f is string => typeof f === 'string' && f.length > 0)
    : [];
  if (listed.length === 0) return null;
  const unique = isUniqueScopeDeclared(idx?.unique);

  let columns = listed;
  let nullSafeColumns: string[] | undefined;
  if (Array.isArray(idx?.nullSafeColumns) && idx.nullSafeColumns.length > 0) {
    // Already-normalized shape (drift-op apply path) — honour it verbatim.
    nullSafeColumns = idx.nullSafeColumns.filter((c) => listed.includes(c));
    if (nullSafeColumns.length === 0) nullSafeColumns = undefined;
  } else if (idx?.unique === 'organization' && tenantField) {
    columns = listed.includes(tenantField) ? listed : [tenantField, ...listed];
  }

  const name =
    typeof idx?.name === 'string' && idx.name.trim()
      ? idx.name.trim()
      : buildIndexName(table, columns, unique);
  return { name, columns, unique, ...(nullSafeColumns ? { nullSafeColumns } : {}) };
}

/**
 * The full index set metadata asks for on a table: field-level `unique`
 * (tenancy-aware) plus the object's declared `indexes[]` — `'global'`/bare
 * `true` taken verbatim, `'organization'` scoped through
 * {@link normalizeDeclaredIndex} (ADR-0120 D1).
 *
 * Indexes referencing a column that is not physically present are left out
 * of the expected set. The sync cannot create them, so a `create_index` for
 * one would name a remedy that `os migrate apply` can never perform. That does
 * NOT make them silent. An index whose missing column will never materialize
 * (a misspelt name, a virtual `formula` field) is reported by
 * {@link diffUnbuildableIndexes} as a report-only `unbuildable_index` entry,
 * and the sync logs its skip at `error`.
 */
export function expectedIndexes(args: {
  table: string;
  fields: Record<string, any>;
  tenantField: string | null;
  declaredIndexes?: DeclaredIndexInput[];
  physicalColumns: Set<string>;
}): ExpectedIndex[] {
  const { physicalColumns } = args;
  return declaredIndexSet(args).filter((i) => i.columns.every((c) => physicalColumns.has(c)));
}

/**
 * Field-level `unique` plus the object's `indexes[]`, both normalized: the one
 * composition that {@link expectedIndexes} and {@link diffUnbuildableIndexes}
 * split between them. It is shared so the two can never disagree about which
 * indexes metadata asks for.
 */
function declaredIndexSet(args: {
  table: string;
  fields: Record<string, any>;
  tenantField: string | null;
  declaredIndexes?: DeclaredIndexInput[];
}): ExpectedIndex[] {
  const { table, fields, tenantField, declaredIndexes } = args;
  const out = uniqueIndexesFromFields(table, fields, tenantField);
  for (const idx of Array.isArray(declaredIndexes) ? declaredIndexes : []) {
    const norm = normalizeDeclaredIndex(table, idx, tenantField);
    if (norm) out.push(norm);
  }
  return out;
}

/**
 * Why a declared index key column has no physical column, in words an
 * operator can act on. The fields are the object's own when they are known.
 * Without them, as on the drift-op apply path, the column is only named.
 *
 * Shared by the sync's `error` line (`SqlDriver.syncDeclaredIndexes`) and the
 * `unbuildable_index` drift message, so the two cannot give different reasons
 * for the same column.
 */
export function describeMissingIndexColumns(missing: string[], fields?: Record<string, any>): string {
  return missing
    .map((column) => {
      if (!fields) return `'${column}'`;
      const field = fields[column];
      if (field == null) return `'${column}' (not a field of the object)`;
      if (!fieldHasColumn(field)) return `'${column}' (a formula field: computed on read, never stored)`;
      return `'${column}' (a declared field whose column the table does not have)`;
    })
    .join(', ');
}

/**
 * Will metadata ever put a physical column under this name? Builtins and the
 * tenant column always exist. Otherwise it must be a declared field that
 * materializes a column ({@link fieldHasColumn}: not a virtual `formula`).
 */
function columnEverMaterializes(column: string, fields: Record<string, any>, tenantField: string | null): boolean {
  if (BUILTIN_COLUMNS.has(column) || column === tenantField) return true;
  const field = fields?.[column];
  return field != null && fieldHasColumn(field);
}

/**
 * Report every declared index that can never be built, as a report-only
 * `unbuildable_index` drift entry: the half of the declared set that
 * {@link expectedIndexes} leaves out and could otherwise stay unseen.
 *
 * An index qualifies when at least one key column is missing from the table
 * AND will never materialize: the name is not a field of the object (a
 * misspelling), or it is a virtual `formula` field. The sync skips such an
 * index on every run, so the declaration and the database stay apart for good,
 * and the only remedy is a metadata edit.
 *
 * ⛔ A column that is merely not added YET is left out on purpose. A declared,
 * column-materializing field that the table lacks is pending additive work
 * (`os migrate plan`'s `add_columns`). The index is created in the same sync
 * that adds the column, so reporting it here would be a false finding.
 *
 * Classified like the other report-only ops (`manual_column_type_change`):
 * `needs_confirm`, so `os migrate apply` reports it skipped and the
 * artifact-pinned boot warns about it without refusing to start. A UNIQUE
 * index is `severity: 'error'`: the constraint it declares is not enforced.
 * A plain index is `warning`, the same split as `recreate_index`.
 */
export function diffUnbuildableIndexes(args: {
  table: string;
  fields: Record<string, any>;
  tenantField: string | null;
  declaredIndexes?: DeclaredIndexInput[];
  physicalColumns: Set<string>;
}): ManagedDriftEntry[] {
  const { table, fields, tenantField, physicalColumns } = args;
  const out: ManagedDriftEntry[] = [];
  const seen = new Set<string>();
  for (const idx of declaredIndexSet(args)) {
    if (seen.has(idx.name)) continue;
    const missing = idx.columns.filter(
      (c) => !physicalColumns.has(c) && !columnEverMaterializes(c, fields, tenantField),
    );
    if (missing.length === 0) continue;
    seen.add(idx.name);
    const signature = indexSignature(idx.columns, idx.unique, idx.nullSafeColumns);
    out.push({
      kind: 'index_mismatch',
      remoteName: table,
      table,
      column: missing[0],
      expected: signature,
      actual: '(absent)',
      severity: idx.unique ? 'error' : 'warning',
      category: 'needs_confirm',
      op: {
        type: 'unbuildable_index',
        table,
        column: missing[0],
        indexName: idx.name,
        unique: idx.unique,
        missingColumns: missing,
      },
      message:
        `${table}: metadata declares index '${idx.name}' ${signature}, but it can never be built: no column for ` +
        `${describeMissingIndexColumns(missing, fields)}. ` +
        (idx.unique
          ? 'The uniqueness it declares is NOT enforced, and duplicate rows are accepted. '
          : 'The index does not exist. ') +
        `"os migrate apply" cannot create it and reports it skipped. Fix the metadata: every column in the ` +
        `index's fields must be a stored field of the object, or remove the index.`,
    });
  }
  return out;
}

/**
 * Every column any declared index on this object will use as a KEY PART, mapped
 * to whether at least one of those indexes is UNIQUE.
 *
 * Computed from the same two normalizers {@link expectedIndexes} composes —
 * field-level `unique` through {@link uniqueIndexesFromFields}, object-level
 * `indexes[]` through {@link normalizeDeclaredIndex} — so "which columns end up
 * in a key" has ONE answer, shared by the index sync that creates them and by
 * the DDL that has to make them keyable in the first place (commit d0e3a885b).
 *
 * ⚠️ Deliberately NOT filtered by `physicalColumns`, unlike `expectedIndexes`:
 * its caller runs BEFORE the columns exist — deciding a column's TYPE is the
 * whole reason it asks — so a filter against the physical set would answer
 * "nothing is indexed" on exactly the CREATE TABLE path that needs the answer.
 *
 * The UNIQUE flag is carried because the two dispositions genuinely differ on
 * MySQL: a bounded key part is merely a storage choice for an ordinary index,
 * but it is the CONSTRAINT itself for a unique one (see
 * `mysqlKeyableTextLength` and the refusal it feeds).
 */
export function indexedKeyColumns(args: {
  table: string;
  fields: Record<string, any>;
  tenantField: string | null;
  declaredIndexes?: DeclaredIndexInput[];
}): Map<string, { unique: boolean }> {
  const { table, fields, tenantField, declaredIndexes } = args;
  const out = new Map<string, { unique: boolean }>();
  const record = (idx: ExpectedIndex) => {
    for (const column of idx.columns) {
      const prev = out.get(column);
      if (prev) prev.unique ||= idx.unique;
      else out.set(column, { unique: idx.unique });
    }
  };
  for (const idx of uniqueIndexesFromFields(table, fields, tenantField)) record(idx);
  for (const idx of Array.isArray(declaredIndexes) ? declaredIndexes : []) {
    const norm = normalizeDeclaredIndex(table, idx, tenantField);
    if (norm) record(norm);
  }
  return out;
}

/**
 * The two names a tenant-scoped field's *legacy* single-column unique index
 * could have been materialized under before #3696:
 *
 *   - `<table>_<column>_unique` — knex's default name for `col.unique()`,
 *     emitted by the old `createColumn`. A real CONSTRAINT on Postgres, a plain
 *     index on SQLite/MySQL.
 *   - `uniq_<table>_<column>` — {@link buildIndexName}, emitted by the old
 *     SQLite rebuild path.
 */
export function legacyUniqueIndexNames(table: string, column: string): string[] {
  return [`${table}_${column}_unique`, buildIndexName(table, [column], true)];
}

/** A tenant-scoped unique field, plus the legacy index names that would supersede it. */
export interface LegacyUniqueReplacement {
  column: string;
  legacyNames: string[];
  replacement: ExpectedIndex;
  /**
   * The EXACT physical key columns the superseded index must have, in key
   * order — the shape the replacement relaxes away from.
   *
   * For a field-level unique this is `[column]`: the pre-#3696 single-column
   * global index. For a DECLARED index respelled from the global spelling to
   * `'organization'` (#8323) it is the index's listed columns, which may be
   * several — `sys_user_preference`'s `(user_id, key)` is the case that put
   * this here. Matching on the name alone is not enough (an unrelated index
   * may collide with the generated spelling), and matching on a single leading
   * column is not enough either: `(user_id, key)` and `(user_id, tenant)` share
   * one, and only one of them is the index being replaced.
   */
  legacyColumns: string[];
}

/**
 * For every field whose `unique` is now tenant-scoped, the legacy global index
 * names to look for and the composite that replaces them. `unique: 'global'`
 * fields are excluded — their single-column index is the declared intent now,
 * not legacy debt.
 *
 * `declaredIndexes` are excluded the same way, and for the same reason (#3955).
 * An object may declare a single-column unique index alongside a tenant-scoped
 * field-level `unique: true` — `email: { unique: true }` plus
 * `indexes: [{ fields: ['email'], unique: true }]`. That declared index
 * materializes under {@link buildIndexName}, which is *also* one of the two
 * spellings {@link legacyUniqueIndexNames} looks for, so without this filter the
 * detector reads an index metadata declares TODAY as pre-#3696 debt and proposes
 * dropping it. The plan then never converges: apply drops the declared index,
 * the next plan reports it missing and recreates it, and the one after that
 * calls it legacy again — an unbounded drop/create cycle on a live unique index.
 * An index the current metadata declares is by definition not legacy.
 */
export function legacyUniqueReplacements(args: {
  table: string;
  fields: Record<string, any>;
  tenantField: string | null;
  physicalColumns: Set<string>;
  declaredIndexes?: DeclaredIndexInput[];
}): LegacyUniqueReplacement[] {
  const { table, fields, tenantField, physicalColumns, declaredIndexes } = args;
  if (!tenantField) return []; // Nothing was ever mis-scoped on a tenant-less table.
  // Without a physical tenant column there is no composite to replace the
  // legacy index with, and dropping it unreplaced would remove the constraint
  // outright rather than relax it. Leave it alone.
  if (!physicalColumns.has(tenantField)) return [];
  // Normalized through the same helper the create path uses, so "what the
  // declared index is named" is answered once, not guessed at twice.
  const declaredNames = new Set(
    (Array.isArray(declaredIndexes) ? declaredIndexes : [])
      .map((idx) => normalizeDeclaredIndex(table, idx, tenantField)?.name)
      .filter((n): n is string => typeof n === 'string'),
  );
  const out: LegacyUniqueReplacement[] = [];
  for (const [name, field] of Object.entries<any>(fields ?? {})) {
    if (!isUniqueScopeDeclared(field?.unique)) continue;
    if (!isOrganizationScopedUnique(field.unique)) continue;
    if (name === tenantField || !physicalColumns.has(name)) continue;
    const legacyNames = legacyUniqueIndexNames(table, name).filter((n) => !declaredNames.has(n));
    if (legacyNames.length === 0) continue;
    const columns = [tenantField, name];
    out.push({
      column: name,
      legacyColumns: [name],
      legacyNames,
      // A pure relaxation to create from under the legacy GLOBAL
      // single-column unique: any two rows colliding in the new key already
      // collided in the old one, so the replacement can neither fail nor lose
      // data.
      replacement: { name: buildIndexName(table, columns, true), columns, unique: true },
    });
  }

  // ── Declared indexes respelled from the global spelling to 'organization' ──
  //
  // #8323: the same retirement, one level up. A declared index's bare
  // `unique: true` is the positional spelling of `'global'` — the listed
  // columns VERBATIM — so respelling it `'organization'` changes the
  // materialized shape from `(…listed)` to `(tenant, …listed)`, and with it
  // the generated NAME. On a deployed database that
  // reads as two unrelated findings: the composite is missing (create, safe)
  // and the old global index is an orphan (drop, DESTRUCTIVE, opt-in). An
  // operator who applies only the safe half keeps the global index — and the
  // global index is the defect, so the migration would look applied while the
  // cross-organization refusal it exists to remove is still enforced.
  //
  // Routing it through the SAME `replace_unique_index` op the field-level
  // retirement uses states it as what it is: one pure relaxation, categorised
  // `safe`, applied CREATE-before-DROP so uniqueness is never unenforced in
  // between, and dropping the old index only once the replacement is confirmed
  // present. Any two rows colliding on `(tenant, …listed)` already collided on
  // `(…listed)`, so the create cannot fail on existing data and no data is lost.
  for (const idx of Array.isArray(declaredIndexes) ? declaredIndexes : []) {
    if (idx?.unique !== 'organization') continue;
    // An EXPLICITLY NAMED index keeps its name across the respelling, so there
    // is no second name to retire — same name, new definition, which is
    // `recreate_index`'s job (drop-then-create under one name). Emitting a
    // replacement here as well would propose dropping the very index the
    // recreate is rebuilding.
    if (typeof idx?.name === 'string' && idx.name.trim()) continue;
    const listed = Array.isArray(idx?.fields)
      ? idx.fields.filter((f): f is string => typeof f === 'string' && f.length > 0)
      : [];
    if (listed.length === 0) continue;
    // Every listed column must exist physically, or there is no index to match
    // and nothing the replacement could be created from.
    if (!listed.every((c) => physicalColumns.has(c))) continue;
    const replacement = normalizeDeclaredIndex(table, idx, tenantField);
    if (!replacement) continue;
    const legacyName = buildIndexName(table, listed, true);
    // The S6 hand-written composite already lists the tenant column, so
    // `normalizeDeclaredIndex` prepends nothing and the "legacy" name IS the
    // current name. Nothing was superseded.
    if (legacyName === replacement.name) continue;
    // An index the CURRENT metadata declares is by definition not legacy
    // (#3955) — the same guard the field-level arm applies.
    if (declaredNames.has(legacyName)) continue;
    out.push({
      column: listed[0],
      legacyColumns: listed,
      legacyNames: [legacyName],
      replacement,
    });
  }
  return out;
}

/**
 * Is this index one ObjectStack generated from metadata?
 *
 * Orphan detection is deliberately restricted to *our* naming conventions. A
 * DBA's hand-rolled covering index is not drift — reporting every undeclared
 * index would drown the plan in false positives and invite `os migrate apply
 * --allow-destructive` to delete someone's carefully tuned index. Only the
 * shapes {@link buildIndexName} and the pre-#3696 `createColumn` could have
 * emitted are candidates.
 */
export function isManagedIndexName(table: string, index: PhysicalIndex): boolean {
  const { name, columns, unique } = index;
  for (const prefix of ['uniq', 'idx']) {
    if (name.startsWith(`${prefix}_${table}_`)) return true;
    // Hash-suffixed long form: `<prefix>_<table>` truncated, then `_<hash8>`.
    if (
      name.length > 9 &&
      /^_[0-9a-f]{8}$/.test(name.slice(-9)) &&
      name.slice(0, -9) === `${prefix}_${table}`.slice(0, INDEX_NAME_HEAD)
    ) {
      return true;
    }
  }
  // knex's default name for the `col.unique()` the pre-#3696 `createColumn` emitted.
  return unique && columns.length === 1 && name === `${table}_${columns[0]}_unique`;
}

/**
 * `UNIQUE (a, b)` vs `(a, b)` — the display form of an index definition. A
 * NULL-safe organization key part (ADR-0120 D3) renders as its COALESCE form
 * so plan/warn messages describe the constraint that will actually exist.
 */
function indexSignature(
  columns: string[],
  unique: boolean,
  nullSafeColumns?: ReadonlyArray<string> | null,
): string {
  const ns = new Set(nullSafeColumns ?? []);
  const parts = columns.map((c) => (ns.has(c) ? organizationKeyPartSql(c) : c));
  return `${unique ? 'UNIQUE ' : ''}(${parts.join(', ')})`;
}

/**
 * Comparison identity of an index key — the SAME normalization for the
 * expected and the physical side (ADR-0120 D3; the #4884 lesson). A key
 * part's FORM matters (`COALESCE(organization_id, …)` is a different
 * constraint from bare `organization_id`), but the COALESCE literal does not:
 * any `COALESCE(col, <literal>)` folds NULL into one bucket, so two spellings
 * of the literal are the same constraint and must not read as drift.
 */
/**
 * Is this physical index carried by a #11627 hash shadow — i.e. does it key
 * exactly the one driver-owned generated column that stands in for the
 * declared key MySQL could not express directly?
 *
 * Answerable from the index alone, by NAME: the shadow is derived from the
 * index name ({@link hashShadowColumnFor}), so a carrier is an index whose sole
 * key column is its own shadow. That is what makes this the FAIL-SAFE half of
 * commit cd1348802 — it holds even when the generation expression cannot be read, and a
 * carrier is never a thing this differ may propose destroying on a guess.
 */
export function isHashShadowCarrier(index: PhysicalIndex): boolean {
  return index.columns.length === 1 && index.columns[0] === hashShadowColumnFor(index.name);
}

/**
 * The key an index ENFORCES, which is not always the key it STORES (commit cd1348802).
 *
 * For an ordinary index the two are the same. For a #11627 shadow-carried
 * UNIQUE the stored key is one VARBINARY(32) generated column and the enforced
 * key is the declared column set the shadow hashes — so every comparison in
 * this module has to run against THIS, or a healthy constraint reads as an
 * index over a column no metadata declares.
 */
export function enforcedIndexKey(index: PhysicalIndex): {
  columns: string[];
  nullSafeColumns?: string[];
} {
  if (!index.shadowKey) {
    return { columns: index.columns, nullSafeColumns: index.nullSafeColumns };
  }
  return {
    columns: index.shadowKey.map((k) => k.column),
    nullSafeColumns: index.shadowKey.filter((k) => k.nullSafe).map((k) => k.column),
  };
}

export function canonicalIndexKey(columns: string[], nullSafeColumns?: ReadonlyArray<string> | null): string {
  const ns = new Set(nullSafeColumns ?? []);
  return columns.map((c) => (ns.has(c) ? `coalesce:${c}` : c)).join(',');
}

/**
 * [ADR-0131 D1] Does any physical index key the tenant column through the
 * NULL-safe `COALESCE` form — directly, or through a #11627 hash shadow's
 * resolved key? Those are the indexes an earlier release built, and the only
 * ones whose verdict {@link diffManagedIndexes}'s `tenantKeyCertified` can
 * change, so a caller asks the (costly) certification question only when this
 * answers yes.
 */
export function carriesNullSafeTenantKeyPart(physical: PhysicalIndex[], tenantField?: string | null): boolean {
  if (!tenantField) return false;
  return physical.some((p) => (enforcedIndexKey(p).nullSafeColumns ?? []).includes(tenantField));
}

/**
 * Diff a table's expected index set against the physical one.
 *
 * Presence is matched by NAME, not by column signature, because that is exactly
 * what `syncDeclaredIndexes` does when deciding whether to skip a create. Using
 * a different rule here would produce findings the reconciler could never
 * clear (or, worse, hide ones it will never fix on its own).
 */
export function diffManagedIndexes(args: {
  table: string;
  expected: ExpectedIndex[];
  legacy: LegacyUniqueReplacement[];
  physical: PhysicalIndex[];
  /**
   * Index names THIS process created through the driver's raw `execute()` DDL
   * (#4884). Honoured as a runtime-managed marker — see
   * {@link isRuntimeManagedIndex}.
   */
  runtimeCreated?: ReadonlySet<string>;
  /**
   * The table's tenant column, when it has one. Lets the differ recognise the
   * NULL-safe organization key part as the sync's OWN vocabulary
   * (ADR-0120 D3) — see {@link isSyncReproducibleIndex}.
   */
  tenantField?: string | null;
  /**
   * [ADR-0131 D1] Does the tenant column hold no NULL on this table — because
   * the column is physically NOT NULL, or because the database is certified
   * (a birth marker or an organization-ownership ceremony marker of the
   * current shape generation)? Then `COALESCE(<tenantField>, …)` and the plain
   * tenant column enforce the same constraint, and a key part in either form
   * matches the other, in both directions: an index an earlier release built
   * in the NULL-safe form is never proposed for a rebuild into the plain form
   * new DDL emits. Absent or false on a database that may still hold
   * NULL-organization rows, where the two forms are different constraints.
   */
  tenantKeyCertified?: boolean;
}): ManagedDriftEntry[] {
  const { table, expected, legacy, physical, runtimeCreated, tenantField, tenantKeyCertified } = args;
  // The comparison identity of a key: the NULL-safe tenant key part reads as
  // the plain column where the two are the same constraint (ADR-0131 D1).
  const comparisonKey = (columns: string[], nullSafeColumns?: ReadonlyArray<string> | null): string =>
    canonicalIndexKey(
      columns,
      tenantKeyCertified === true && tenantField
        ? (nullSafeColumns ?? []).filter((c) => c !== tenantField)
        : nullSafeColumns,
    );
  const out: ManagedDriftEntry[] = [];
  const byName = new Map(physical.map((p) => [p.name, p]));
  /** Physical index names accounted for — either declared, or already reported. */
  const explained = new Set<string>();

  // ── 1. Legacy platform-wide unique superseded by a tenant composite ──
  for (const l of legacy) {
    // Only a *plain unique on exactly those columns, in key order* is the
    // legacy shape. Matching on the name alone would let an unrelated index
    // that happens to collide with the legacy spelling be dropped.
    //
    // `legacyColumns` is `[column]` for the field-level retirement and the
    // declared index's listed columns for the #8323 respelling — the same
    // question either way, asked once. The plainness guards matter for the
    // multi-column arm: an index carrying an expression key part, a NULL-safe
    // organization part or a WHERE predicate is NOT the verbatim global shape
    // being relaxed, whatever its column identities read as.
    const present = l.legacyNames.filter((n) => {
      const p = byName.get(n);
      if (!p || p.primary || isRuntimeManagedIndex(p, runtimeCreated, tenantField)) return false;
      if (!p.unique || p.partial === true) return false;
      if ((p.expressions?.length ?? 0) > 0 || (p.nullSafeColumns?.length ?? 0) > 0) return false;
      // Commit cd1348802: nor is a hash-shadow carrier. Its stored key is one generated
      // column, so the identity comparison below already excludes it — stated
      // outright because the exclusion must survive that comparison changing,
      // and because `replace_unique_index` DROPS the legacy name.
      if (isHashShadowCarrier(p)) return false;
      return (
        p.columns.length === l.legacyColumns.length &&
        p.columns.every((c, i) => c === l.legacyColumns[i])
      );
    });
    if (present.length === 0) continue;
    for (const n of present) explained.add(n);
    out.push({
      kind: 'index_mismatch',
      remoteName: table,
      table,
      column: l.column,
      expected: indexSignature(l.replacement.columns, true, l.replacement.nullSafeColumns),
      actual: indexSignature(l.legacyColumns, true),
      severity: 'warning',
      category: 'safe',
      op: {
        type: 'replace_unique_index',
        table,
        column: l.column,
        dropIndexNames: present,
        createIndexName: l.replacement.name,
        createColumns: l.replacement.columns,
        ...(l.replacement.nullSafeColumns ? { nullSafeColumns: l.replacement.nullSafeColumns } : {}),
      },
      message:
        `${table}.${l.legacyColumns.join('+')}: a legacy platform-wide UNIQUE index (${present.join(', ')}) still enforces ` +
        `uniqueness across ALL tenants, but metadata scopes it per '${l.replacement.columns[0]}' — a second ` +
        `tenant reusing the value is rejected on insert. Replacing it with ${indexSignature(l.replacement.columns, true, l.replacement.nullSafeColumns)} ` +
        `is a pure relaxation: run "os migrate apply".`,
    });
  }

  // ── 2. Declared index missing, or present with the wrong definition ──
  for (const e of expected) {
    explained.add(e.name);
    const p = byName.get(e.name);
    if (!p) {
      out.push({
        kind: 'index_mismatch',
        remoteName: table,
        table,
        column: e.columns[0],
        expected: indexSignature(e.columns, e.unique, e.nullSafeColumns),
        actual: '(absent)',
        severity: 'warning',
        category: 'safe',
        op: {
          type: 'create_index',
          table,
          column: e.columns[0],
          indexName: e.name,
          columns: e.columns,
          unique: e.unique,
          ...(e.nullSafeColumns ? { nullSafeColumns: e.nullSafeColumns } : {}),
        },
        message:
          `${table}: metadata declares index '${e.name}' ${indexSignature(e.columns, e.unique, e.nullSafeColumns)} but the database ` +
          `has no such index — run "os migrate apply" to create it.`,
      });
      continue;
    }
    // Same normalization on BOTH sides (#4884, ADR-0120 D3): column identity
    // AND key-part form, literal-agnostic on the COALESCE literal — asked of
    // the key the index ENFORCES, which for a #11627 shadow-carried UNIQUE is
    // not the column it stores (commit cd1348802). On a certified table the
    // tenant key part's form drops out of the identity (ADR-0131 D1).
    const pk = enforcedIndexKey(p);
    if (p.unique === e.unique && comparisonKey(pk.columns, pk.nullSafeColumns) === comparisonKey(e.columns, e.nullSafeColumns)) {
      continue;
    }
    // The framework's own runtime migrations own some declared names — ADR-0048
    // rebuilds `idx_sys_metadata_overlay_active` as a partial UNIQUE over
    // `COALESCE(package_id,'')`, and `sys-metadata.object.ts` says in so many
    // words that its four-column declaration is "the fallback shape for drivers
    // without the runtime migration". Proposing a rebuild FROM that fallback
    // would replace a stronger index with a weaker one, under a remedy
    // (`recreate_index` → drop first) this differ cannot undo. Not ours to
    // reconcile (#4884).
    if (isRuntimeManagedIndex(p, runtimeCreated, tenantField)) continue;
    // Commit cd1348802, fail-safe half: a hash-shadow carrier whose generation
    // expression could NOT be read (`shadowKey` unresolved). We know by name
    // that the index is driver-owned and that its stored key is a digest, so
    // the identity comparison above is meaningless for it — but we do not know
    // WHAT it hashes, and the remedy below is a DROP. Report nothing rather
    // than propose destroying a constraint on a guess.
    //
    // ⛔ The `!p.shadowKey` half is load-bearing, and was measured: without it
    // this guard swallows the RESOLVED carriers too, which silently demotes the
    // whole fix to the blind skip — every shadow-carried index unreportable,
    // including one from before commit df1c75c4b hashing the RAW columns whose constraint does
    // not cover NULL-organization rows at all. Green, quiet, and the exact
    // trade this fix exists to refuse.
    if (isHashShadowCarrier(p) && !p.shadowKey) continue;
    // Same name, different definition. `syncDeclaredIndexes` skips by name, so
    // this never self-heals: it has to be dropped and rebuilt. Tightening to
    // UNIQUE is destructive — the CREATE can fail on existing duplicates, and
    // by then the old index is already gone.
    //
    // ADR-0120 D4: ONE redefinition is data-dependent rather than structural —
    // the bare organization composite tightening into its NULL-safe COALESCE
    // form (same identities, same uniqueness, physical fully plain). It is
    // marked so the driver can run the duplicate pre-flight probe on it:
    // clean → recategorised `safe` (dev autoMigrate may apply); duplicates →
    // blocked with a row report, the old index left in place.
    //
    // Commit cd1348802: read through the ENFORCED key, so a shadow from before commit df1c75c4b — same
    // columns, hashed RAW instead of through the NULL-safe COALESCE — is
    // recognised as exactly this tightening and gets the same duplicate
    // pre-flight before anything is dropped. The explicit "physical side is
    // bare" clause is what `p.expressions.length === 0` used to imply on its
    // own (`nullSafeColumns` is only ever recorded alongside an expression key
    // part); a resolved shadow key can carry NULL-safe parts with no
    // expressions at all, so the implication no longer holds.
    // [ADR-0131 D1] An earlier release's COALESCE tenant key part against the
    // plain one new DDL declares — the divergence is ONLY that form, on a
    // table where the two are still different constraints (not
    // certified, nullable tenant column). The remedy is the ownership ceremony,
    // which certifies the database and so retires this entry — never a rebuild:
    // `--allow-destructive` would drop and re-key the index over rows that may
    // still lack an organization. The op is unchanged (destructive, reported,
    // never applied unattended); only what it tells the operator differs.
    const uncertifiedTenantKeyOnly =
      tenantKeyCertified !== true &&
      !!tenantField &&
      p.unique === e.unique &&
      (pk.nullSafeColumns ?? []).includes(tenantField) &&
      !(e.nullSafeColumns ?? []).includes(tenantField) &&
      canonicalIndexKey(pk.columns, (pk.nullSafeColumns ?? []).filter((c) => c !== tenantField)) ===
        canonicalIndexKey(e.columns, (e.nullSafeColumns ?? []).filter((c) => c !== tenantField));
    const tightenNullSafeOnly =
      e.unique &&
      p.unique &&
      (e.nullSafeColumns?.length ?? 0) > 0 &&
      (p.expressions?.length ?? 0) === 0 &&
      (pk.nullSafeColumns?.length ?? 0) === 0 &&
      p.partial !== true &&
      pk.columns.join(',') === e.columns.join(',');
    out.push({
      kind: 'index_mismatch',
      remoteName: table,
      table,
      column: e.columns[0],
      expected: indexSignature(e.columns, e.unique, e.nullSafeColumns),
      actual: indexSignature(pk.columns, p.unique, pk.nullSafeColumns),
      severity: e.unique ? 'error' : 'warning',
      category: e.unique ? 'destructive' : 'needs_confirm',
      op: {
        type: 'recreate_index',
        table,
        column: e.columns[0],
        indexName: e.name,
        columns: e.columns,
        unique: e.unique,
        ...(e.nullSafeColumns ? { nullSafeColumns: e.nullSafeColumns } : {}),
        ...(tightenNullSafeOnly ? { tightenNullSafeOnly: true } : {}),
      },
      message: uncertifiedTenantKeyOnly
        ? `${table}: index '${e.name}' is ${indexSignature(pk.columns, p.unique, pk.nullSafeColumns)} and metadata declares ` +
          `${indexSignature(e.columns, e.unique, e.nullSafeColumns)}: the same columns, differing only in how the ` +
          `'${tenantField}' key part treats a row with no organization. This database is not certified for the ` +
          `organization-ownership shape and '${tenantField}' is nullable, so the two are still different constraints. ` +
          `Run "os migrate organization-ownership": once it certifies the database the two read as one index and this ` +
          `entry is gone. Do not rebuild it with "os migrate apply --allow-destructive", which would drop and re-key ` +
          `the index over rows that may still have no organization.`
        : tightenNullSafeOnly
        ? `${table}: index '${e.name}' is ${indexSignature(pk.columns, p.unique, pk.nullSafeColumns)} but metadata declares ` +
          `${indexSignature(e.columns, e.unique, e.nullSafeColumns)} (ADR-0120 D3: the organization key part is NULL-safe, ` +
          `so rows without an organization are constrained too). Pure tightening — eligibility is decided by the ` +
          `duplicate pre-flight probe.`
        : `${table}: index '${e.name}' is ${indexSignature(pk.columns, p.unique, pk.nullSafeColumns)} but metadata declares ` +
          `${indexSignature(e.columns, e.unique, e.nullSafeColumns)} — the additive sync skips it by name, so it must be rebuilt` +
          (e.unique
            ? `. Creating the UNIQUE index can fail on existing duplicates: "os migrate apply --allow-destructive".`
            : ` via "os migrate apply".`),
    });
  }

  // ── 3. Orphans: an index WE generated that metadata no longer declares ──
  for (const p of physical) {
    if (p.primary || p.columns.length === 0 || explained.has(p.name)) continue;
    if (!isManagedIndexName(table, p)) continue;
    // "Generated naming" is a NAME heuristic; it cannot tell an index the sync
    // emitted from one the framework's runtime built under a similar name. The
    // orphan remedy is a DROP, so the claim has to be stronger than a prefix:
    // an index this differ could not recreate is never proposed for deletion
    // (#4884 — the boot advised dropping `idx_sys_metadata_overlay_draft`, the
    // partial UNIQUE enforcing draft-overlay uniqueness, on a healthy fresh DB).
    if (isRuntimeManagedIndex(p, runtimeCreated, tenantField)) continue;
    // Commit cd1348802: an orphaned shadow carrier is still an orphan — its declaration
    // is gone, and `drop_index` is the right remedy — but the report must name
    // the constraint it enforced, not the digest column it stored.
    const po = enforcedIndexKey(p);
    out.push({
      kind: 'unmapped_index',
      remoteName: table,
      table,
      column: p.columns[0],
      expected: '(absent)',
      actual: indexSignature(po.columns, p.unique, po.nullSafeColumns),
      severity: 'warning',
      category: 'destructive',
      op: { type: 'drop_index', table, column: p.columns[0], indexName: p.name },
      message:
        `${table}: index '${p.name}' ${indexSignature(po.columns, p.unique, po.nullSafeColumns)} carries ObjectStack's generated naming ` +
        `but matches no declared index (orphaned) — "os migrate apply --allow-destructive" to drop it.`,
    });
  }

  return out;
}