Migration decision check

#!/usr/bin/env node
/**
 * Migration check: a drop or a type change is a written decision.
 *
 * A migration that drops a table or a column, narrows a type or edits history
 * is the one change to a database that cannot be undone by reverting the
 * commit: the rows are gone wherever the migration has run. An agent writing
 * the migration has no reason to stop and ask. This compares the schema the
 * migrations build on this branch with the schema on the base branch, and
 * fails on every change that loses data unless DECISIONS.md says, for that
 * migration file and that object, that it was meant.
 *
 * HOW IT COMPARES. Two schema snapshots, built by `check-backend.mjs
 * --print-snapshot`, one per side, each in a child process with
 * check-backend's hard timeout. This file never boots PGlite itself.
 *
 *   base  The merge-base of HEAD and the default branch, not HEAD. A branch
 *         is judged by what it adds to the base, so a base branch that moved
 *         on is not read as a drop. On the base branch itself the base is
 *         HEAD: the commit being made is compared with the last one.
 *   head  The index under --staged, else the worktree.
 *
 * Both sides are copied to a temp directory first, the whole install folder
 * (not only the watched files: an Alembic env.py imports the app's own code),
 * with node_modules linked in, so the snapshot builder reads the same files the
 * commit will contain.
 *
 * THE ROOT is the folder that holds scripts/ and contract.json: --root, else
 * the folder above this script's own scripts/, as for check-backend. When ntent
 * is installed in a package of a larger repository that is the package, not the
 * git top level. Watch paths, waivers and the decision log are all relative to
 * it. A run that finds no migration file on either side is exit 2, never a
 * pass, unless --allow-empty makes it a loud note: the hook uses that, because
 * a repo with no migration yet must still be able to commit.
 *
 * THE BASE IS BUILT BY THE HEAD'S CHECKER. The base copy takes, from the head
 * tree, scripts/check-*.mjs and scripts/lib/, and any helper file CONFIG.sources
 * names that the base does not have (a script a `run` source calls, a base
 * schema file listed first). Never a migration. A helper added this way is
 * named in the note BASE-OVERLAY. A base from before the schema existed (its
 * command printed no SQL) is BASE-EMPTY, not exit 2.
 *
 * ATTRIBUTION. A finding is credited to the migration that introduces it. Only
 * the changed files that name the finding's table are searched, so a repo with
 * a long history is not rebuilt once per finding.
 *
 * Files that are meant to change after they are committed (declarative
 * schemas, structure.sql, Drizzle's journal) are listed in CONFIG.mutable and
 * are never flagged as edits.
 *
 * THE WAIVER. One entry in the decision log (the text between two `## `
 * headings) that holds, in backticks, both the repo path of the migration
 * that introduces the change and the object: `t` for a dropped table, `t.c`
 * otherwise (the schema may be left off for `public`). The migration is the
 * first input file, in load order, whose addition makes the head schema
 * differ. An entry that names only the object does not count: a decision
 * about a table last year does not cover a different migration today.
 *
 * COMMAND SOURCES AND SQLITE. When check-backend builds the schema by running
 * a tool (Prisma, drizzle-kit, Alembic, a `run` command), there is no load
 * order, so the migration is any watched file that changed, and an entry
 * naming any of them counts. Both sides are still built from copies, so a
 * command with cwd 'root' reads the base revision for the base side and the
 * index for the head side, never the worktree of the one checkout. The copy is
 * the whole install folder, so a tool that imports app code outside its watch
 * list finds it; only the cache key (check-backend's) hashes the watched files
 * alone, so a change outside them needs --no-cache. A tool that is not on the
 * copy at all (a binary outside the repo, a missing dependency) fails there and
 * is exit 2, not a pass. Only history counts as a migration that can be
 * edited: Alembic's versions/ and a `run` source's migrations/ folder. A Prisma
 * or Drizzle schema is meant to change. SQLite columns compare by affinity,
 * because SQLite does not enforce a declared length.
 *
 * EXIT CODES, and exit 2 is never a pass.
 *
 *   0  nothing to report, or every change is waived, or CONFIG.stage is
 *      'pre-production' (findings print as notes), or there is no commit yet.
 *   1  at least one finding.
 *   2  could not check: git missing, not a repository, the base does not
 *      resolve, no merge-base, a side could not be copied, a snapshot failed
 *      to build or came back unreadable (the underlying code is printed), no
 *      migration file on either side, a base that does not build when the head
 *      does, or a malformed CONFIG. A failed snapshot is never "no changes".
 *      Each says the root, the base ref and the paths it looked at.
 *
 * WHAT IT DOES NOT PROVE
 *
 *   That a person made the decision. An agent can write its own DECISIONS
 *   entry; the waiver proves a decision was written down, not who made it.
 *   That the migration is safe to run: locks, backfills, long rewrites.
 *   That the live database is at the base branch's schema.
 *   Renames. A rename reads as a drop and an add, and needs the same entry.
 *   Data changes: an UPDATE or DELETE inside a migration is not read, except
 *   that an UPDATE of a column in the migration that makes it NOT NULL counts
 *   as a backfill (a note, not a finding). Whether it reaches every row is not
 *   checked.
 *   Enum labels, constraints, indexes and policies. Only tables, columns,
 *   column types and NOT NULL are compared.
 *   Anything, when it exits 2.
 *
 * Usage:
 *   node scripts/check-migrations.mjs                  full report
 *   node scripts/check-migrations.mjs --check          hook and CI: one line on a pass
 *   node scripts/check-migrations.mjs --staged         the head side is the index (the hook)
 *   node scripts/check-migrations.mjs --against <ref>  override the base (CI on the base branch: HEAD~1)
 *   node scripts/check-migrations.mjs --root <dir>     the folder that holds scripts/ (default: this script's)
 *   node scripts/check-migrations.mjs --no-cache       rebuild both snapshots
 *   node scripts/check-migrations.mjs --allow-empty    no migration file on either side is a note, not exit 2 (the hook)
 *   node scripts/check-migrations.mjs --json           machine output on stdout
 *   node scripts/check-migrations.mjs --timing         phase durations on stderr
 *   node scripts/check-migrations.mjs --print-config   what the hook needs to wire it, no snapshot
 *
 * Run it from anywhere inside the repository. In the hook it is run from the
 * repo root with --root, with this script taken from the hook's snapshot of the
 * index, because the snapshot has no .git.
 */
import { spawn, spawnSync } from 'node:child_process'
import {
  cpSync,
  existsSync,
  mkdirSync,
  mkdtempSync,
  readFileSync,
  readdirSync,
  realpathSync,
  rmSync,
  statSync,
  symlinkSync,
} from 'node:fs'
import { availableParallelism, tmpdir } from 'node:os'
import { basename, dirname, isAbsolute, join, relative, resolve } from 'node:path'
import { fileURLToPath } from 'node:url'
import { CONFIG as BACKEND_CONFIG } from './check-backend.mjs'

// ---------------------------------------------------------------- CONFIG ---

export const CONFIG = {
  // 'production': findings fail. 'pre-production': nothing is live, so
  // findings print as notes and the check exits 0.
  stage: 'production',

  // The branch to compare against, e.g. 'origin/main'. null tries, in order,
  // origin/HEAD, origin/main, origin/master, main, master.
  baseRef: null,

  // Where decisions are written. Read from the same side as the head.
  decisionsFile: 'DECISIONS.md',

  // Files meant to change after they are committed. Edits and deletions are
  // not flagged. Globs, one level.
  mutable: [
    'supabase/schemas/*.sql',
    'db/structure.sql',
    'db/schema.rb',
    'drizzle/meta/_journal.json',
    'drizzle/meta/*_snapshot.json',
    'prisma/schema.prisma',
  ],

  // Type changes that cannot lose data are not findings.
  wideningAllowed: true,
}

const deepFreeze = (value) => {
  if (value && typeof value === 'object') {
    for (const v of Object.values(value)) deepFreeze(v)
    Object.freeze(value)
  }
  return value
}

/**
 * CONFIG as shipped, frozen, written out again on purpose: the install steps
 * tell a consumer to edit CONFIG, and a copy of it would follow the edit. Tests
 * build theirs from this, never from CONFIG. Keep it equal to CONFIG above; the
 * package's own tests check that.
 */
export const DEFAULT_CONFIG = deepFreeze({
  stage: 'production',
  baseRef: null,
  decisionsFile: 'DECISIONS.md',
  mutable: [
    'supabase/schemas/*.sql',
    'db/structure.sql',
    'db/schema.rb',
    'drizzle/meta/_journal.json',
    'drizzle/meta/*_snapshot.json',
    'prisma/schema.prisma',
  ],
  wideningAllowed: true,
})

// ------------------------------------------------------------- CONSTANTS ---

const SELF = fileURLToPath(import.meta.url)
const BACKEND = join(dirname(SELF), 'check-backend.mjs')
const STAGES = ['production', 'pre-production']
const DEFAULT_BASES = ['origin/HEAD', 'origin/main', 'origin/master', 'main', 'master']

/** Never copied into a temp directory, and never read as a watched file. */
const NOT_WATCHED = (p) => p === 'contract.json' || p.endsWith('check-backend.mjs') || p.endsWith('check-migrations.mjs')

const FIX = {
  'DROP-TABLE': (o, file) =>
    `Dropping ${o.object} deletes its rows. If that is intended, add an entry to DECISIONS.md naming ${file ? `\`${file}\`` : 'this migration file'} and \`${o.object}\` in backticks.`,
  'DROP-COLUMN': (o, file) =>
    `Dropping ${o.object} deletes its data. If that is intended, add an entry to DECISIONS.md naming ${file ? `\`${file}\`` : 'this migration file'} and \`${o.object}\` in backticks.`,
  'TYPE-CHANGED': (o, file) =>
    `${o.object} changes from ${o.from} to ${o.to}, which can lose or reject data. If that is intended, add an entry to DECISIONS.md naming ${file ? `\`${file}\`` : 'this migration file'} and \`${o.object}\` in backticks.`,
  'NOT-NULL-ADDED': (o, file) =>
    `${o.object} becomes NOT NULL${o.hasDefault ? ', and a default does not fill the rows that are already NULL' : ' with no default'}, so a table that already has rows cannot take it. ${o.hasDefault ? 'Backfill the NULLs first' : 'Add a default or backfill first'}, or, if the table is empty everywhere it is deployed, add an entry to DECISIONS.md naming ${file ? `\`${file}\`` : 'this migration file'} and \`${o.object}\` in backticks.`,
  'MIGRATION-EDITED': () => 'This migration already ran wherever the base branch is deployed. Revert the edit and write a new migration.',
}

// ---------------------------------------------------------------- ERRORS ---

/** Could not check. Always exit 2. */
export class CannotCheck extends Error {
  constructor(code, { object = '', message, fix = '' } = {}) {
    super(message)
    this.code = code
    this.object = object
    this.fix = fix
  }

  toJSON() {
    return { code: this.code, object: this.object, message: this.message, fix: this.fix }
  }
}

// ------------------------------------------------------------------- CLI ---

const FLAGS = new Set(['--check', '--staged', '--json', '--timing', '--print-config', '--no-cache', '--allow-empty'])

export function parseArgs(argv) {
  const opts = { against: null }
  const rest = [...argv]
  while (rest.length) {
    const arg = rest.shift()
    if (arg === '--against') {
      const value = rest.shift()
      if (value === undefined || value.startsWith('-')) {
        throw new CannotCheck('USAGE', { message: '--against needs a ref.', fix: 'Pass a branch, tag or commit: --against origin/main.' })
      }
      opts.against = value
    } else if (arg === '--root') {
      const value = rest.shift()
      if (value === undefined || value.startsWith('-')) {
        throw new CannotCheck('USAGE', { message: '--root needs a directory.', fix: 'Pass the folder that holds scripts/ and contract.json: --root <dir>.' })
      }
      opts.root = value
    } else if (FLAGS.has(arg)) {
      opts[arg.slice(2).replace(/-(\w)/g, (_, c) => c.toUpperCase())] = true
    } else {
      throw new CannotCheck('USAGE', {
        object: arg,
        message: `unknown argument: ${arg}`,
        fix: 'See the usage at the top of scripts/check-migrations.mjs.',
      })
    }
  }
  return opts
}

// ------------------------------------------------------------ VALIDATION ---

export function validateConfig(config) {
  const problems = []
  const bad = (object, message, fix) => problems.push({ code: 'CONFIG-INVALID', object, message, fix })
  if (!STAGES.includes(config?.stage)) bad('stage', `is ${JSON.stringify(config?.stage)}.`, `Set it to 'production' or 'pre-production'.`)
  if (config?.baseRef !== null && typeof config?.baseRef !== 'string') bad('baseRef', 'is not null or a string.', `Set it to null, or a ref such as 'origin/main'.`)
  if (typeof config?.decisionsFile !== 'string' || !config.decisionsFile.trim()) bad('decisionsFile', 'is not a file path.', `Set it to the decision log's path, e.g. 'DECISIONS.md'.`)
  if (!Array.isArray(config?.mutable) || config.mutable.some((g) => typeof g !== 'string' || !g)) bad('mutable', 'is not a list of globs.', `Set it to a list of paths or one-level globs.`)
  if (typeof config?.wideningAllowed !== 'boolean') bad('wideningAllowed', 'is not true or false.', 'Set it to true or false.')
  return problems
}

// ------------------------------------------------------------------- GIT ---

const posix = (p) => p.split('\\').join('/')

function exec(cmd, args, { cwd, input, encoding = 'utf8' } = {}) {
  const r = spawnSync(cmd, args, { cwd, input, encoding, maxBuffer: 512 * 1024 * 1024 })
  if (r.error?.code === 'ENOENT') {
    throw new CannotCheck(cmd === 'git' ? 'GIT-MISSING' : 'TOOL-MISSING', {
      object: cmd,
      message: `${cmd} is not installed or not on PATH.`,
      fix: `Install ${cmd}. The migration check compares against git history and cannot run without it.`,
    })
  }
  if (r.error) throw new CannotCheck('EXEC-FAILED', { object: cmd, message: r.error.message, fix: `Check that ${cmd} runs.` })
  return r
}

const git = (root, args, o = {}) => exec('git', args, { cwd: root, ...o })

const gitOut = (root, args) => {
  const r = git(root, args)
  return r.status === 0 ? r.stdout.trim() : null
}

/** One-level glob to a RegExp: * and ? do not cross a slash. */
const globRe = (glob) => new RegExp(`^${glob.replace(/[.+^${}()|[\]\\]/g, '\\$&').replace(/\*/g, '[^/]*').replace(/\?/g, '[^/]')}$`)

// ------------------------------------------------------------ MATERIALISE ---

/**
 * Where the install is. `top` is the git top level, `root` the folder that
 * holds scripts/ and contract.json (the same as `top` unless ntent is
 * installed in a package), `prefix` is root's path inside the repository
 * ('' or 'backend/'). Git paths are `prefix + path`; everything this file
 * prints and matches is relative to `root`, as check-backend's watch paths are.
 */
function locate(start) {
  const top = git(start, ['rev-parse', '--show-toplevel'])
  if (top.status !== 0 || !top.stdout.trim()) return { top: null, root: start, prefix: '' }
  const real = realpathSync(top.stdout.trim())
  const root = realpathSync(start)
  const rel = posix(relative(real, root))
  return { top: real, root, prefix: rel && !rel.startsWith('..') ? `${rel}/` : '' }
}

/** The nearest folder, from `from` up to the git top, that holds an install. Else the top. */
function installRootFrom(from) {
  const here = locate(from)
  if (!here.top) return from
  let dir = here.root
  for (;;) {
    if (existsSync(join(dir, 'scripts', 'check-backend.mjs')) || existsSync(join(dir, 'contract.json'))) return dir
    if (dir === here.top || dirname(dir) === dir) return here.top
    dir = dirname(dir)
  }
}

/** What a copy links back from the real tree: every install of the tools, wherever it sits. */
const LINKED = ['node_modules', '.venv', 'venv']
// Folders that hold no install and can be big. Not descended into.
const NO_DESCENT = new Set(['.git', '.next', '.nuxt', '.turbo', '.cache', 'dist', 'build', 'target', '__pycache__', 'coverage', '.pnpm-store'])

/** A virtualenv is a folder with pyvenv.cfg; `venv` is a common name for other things. */
const isInstall = (dir, name) => (name === 'venv' ? existsSync(join(dir, name, 'pyvenv.cfg')) : true)

/**
 * node_modules and Python virtualenvs linked into the copy `to`, at the paths
 * the real tree `from` has them, nested ones included: a workspace keeps
 * prisma in apps/web/node_modules, and a backend its alembic in
 * backend/.venv, and both are gitignored so a copy of the tree lacks them.
 * A link is made only where the copy has the folder that would hold it.
 * The Alembic preset looks for .venv/bin/alembic in the directory it runs in.
 */
function linkModules(from, to, depth = 0) {
  let entries
  try {
    entries = readdirSync(from, { withFileTypes: true })
  } catch {
    return
  }
  for (const entry of entries) {
    if (!entry.isDirectory() && !entry.isSymbolicLink()) continue
    if (LINKED.includes(entry.name)) {
      if (!isInstall(from, entry.name) || existsSync(join(to, entry.name))) continue
      try {
        symlinkSync(realpathSync(join(from, entry.name)), join(to, entry.name), 'dir')
      } catch {
        // A link that cannot be made leaves the tool unfound, which the failure then says.
      }
    } else if (entry.isDirectory() && depth < 8 && !NO_DESCENT.has(entry.name) && !entry.name.startsWith('.') && existsSync(join(to, entry.name))) {
      linkModules(join(from, entry.name), join(to, entry.name), depth + 1)
    }
  }
}

/** Both the install root and the git top: a workspace hoists node_modules to the top. */
function linkAll(where, tmpTop, tmpRoot) {
  linkModules(where.root, tmpRoot)
  if (where.top && where.top !== where.root) {
    // Only the top's own folders; the install root's subtree was walked above.
    for (const name of LINKED) {
      const from = join(where.top, name)
      if (isInstall(where.top, name) && existsSync(from) && !existsSync(join(tmpTop, name))) symlinkSync(realpathSync(from), join(tmpTop, name), 'dir')
    }
  }
}

/**
 * contract.json is not an input, and an old one can fail check-backend's
 * validation for a reason that has nothing to do with the schema.
 */
const dropContract = (tmpRoot) => rmSync(join(tmpRoot, 'contract.json'), { force: true })

/** Every file under a watch path, relative to dir. A trailing slash is a directory. */
function walkWatch(dir, watch) {
  const out = []
  const walk = (rel) => {
    for (const entry of readdirSync(join(dir, rel), { withFileTypes: true })) {
      if (entry.name === 'node_modules' || entry.name === '.git') continue
      const child = rel ? `${rel}/${entry.name}` : entry.name
      if (entry.isDirectory()) walk(child)
      else if (entry.isFile()) out.push(child)
    }
  }
  for (const w of watch) {
    const path = w.replace(/\/$/, '')
    let st
    try {
      st = statSync(join(dir, path))
    } catch {
      continue
    }
    if (st.isDirectory()) walk(path)
    else if (st.isFile()) out.push(path)
  }
  return [...new Set(out)].sort()
}

/**
 * The base's whole install tree into tmpTop: `git archive <sha> -- <prefix> | tar -x`.
 * Not only the watched files: an Alembic env.py or a Prisma config imports
 * the app's own code, and a copy without it fails for a reason that is not
 * the schema. A base that does not have the install folder yet is an empty copy.
 */
function materialiseBase(where, sha, watch, tmpTop) {
  const tmpRoot = join(tmpTop, where.prefix.replace(/\/$/, ''))
  mkdirSync(tmpRoot, { recursive: true })
  const there = !where.prefix || git(where.top, ['cat-file', '-e', `${sha}:${where.prefix.replace(/\/$/, '')}`]).status === 0
  if (there) {
    const archive = git(where.top, ['archive', '--format=tar', sha, ...(where.prefix ? ['--', where.prefix] : [])], { encoding: 'buffer' })
    if (archive.status !== 0) {
      throw new CannotCheck('MATERIALISE-FAILED', { object: sha, message: `git archive failed: ${archive.stderr.toString().trim()}`, fix: 'Check that the base commit exists and is not a shallow gap.' })
    }
    const tar = exec('tar', ['-x', '-C', tmpTop], { input: archive.stdout, encoding: 'buffer' })
    if (tar.status !== 0) throw new CannotCheck('MATERIALISE-FAILED', { object: sha, message: `tar failed: ${tar.stderr.toString().trim()}`, fix: 'Check that tar is installed.' })
  }
  dropContract(tmpRoot)
  linkAll(where, tmpTop, tmpRoot)
  return { dir: tmpRoot, files: walkWatch(tmpRoot, watch) }
}

/** The index's whole install tree into tmpTop. checkout-index takes files, not directories. */
function materialiseIndex(where, watch, tmpTop) {
  const tmpRoot = join(tmpTop, where.prefix.replace(/\/$/, ''))
  mkdirSync(tmpRoot, { recursive: true })
  const list = git(where.top, ['ls-files', '-z', ...(where.prefix ? ['--', where.prefix] : [])], { encoding: 'buffer' })
  if (list.status !== 0) throw new CannotCheck('MATERIALISE-FAILED', { object: 'index', message: `git ls-files failed: ${list.stderr.toString().trim()}`, fix: 'Run it inside the repository.' })
  if (list.stdout.length) {
    const out = git(where.top, ['checkout-index', '-z', '--stdin', `--prefix=${tmpTop}/`], { input: list.stdout, encoding: 'buffer' })
    if (out.status !== 0) throw new CannotCheck('MATERIALISE-FAILED', { object: 'index', message: `git checkout-index failed: ${out.stderr.toString().trim()}`, fix: 'Resolve merge conflicts in the index first.' })
  }
  dropContract(tmpRoot)
  linkAll(where, tmpTop, tmpRoot)
  return { dir: tmpRoot, files: walkWatch(tmpRoot, watch) }
}

/** The worktree's whole install tree into tmpTop: what git tracks, and what it would not ignore. */
function materialiseWorktree(where, watch, tmpTop) {
  const tmpRoot = join(tmpTop, where.prefix.replace(/\/$/, ''))
  mkdirSync(tmpRoot, { recursive: true })
  const list = git(where.top, ['ls-files', '-z', '--cached', '--others', '--exclude-standard', ...(where.prefix ? ['--', where.prefix] : [])], { encoding: 'buffer' })
  if (list.status !== 0) throw new CannotCheck('MATERIALISE-FAILED', { object: 'worktree', message: `git ls-files failed: ${list.stderr.toString().trim()}`, fix: 'Run it inside the repository.' })
  for (const f of list.stdout.toString().split('\0').filter(Boolean)) {
    const from = join(where.top, f)
    // A tracked file deleted in the worktree is not there to copy; a link that dangles or a submodule is not a schema file.
    if (!existsSync(from) || statSync(from).isDirectory()) continue
    mkdirSync(dirname(join(tmpTop, f)), { recursive: true })
    cpSync(from, join(tmpTop, f))
  }
  dropContract(tmpRoot)
  linkAll(where, tmpTop, tmpRoot)
  return { dir: tmpRoot, files: walkWatch(tmpRoot, watch) }
}

// -------------------------------------------------------------- SNAPSHOT ---

/** Run check-backend and collect its output. The timeout is the caller's. */
function runBackend(backend, args, timeoutMs) {
  return new Promise((done) => {
    const child = spawn(process.execPath, [backend, ...args], { stdio: ['ignore', 'pipe', 'pipe'] })
    let out = ''
    let err = ''
    let timedOut = false
    const timer = setTimeout(() => {
      timedOut = true
      child.kill('SIGKILL')
    }, timeoutMs)
    child.stdout.on('data', (d) => (out += d))
    child.stderr.on('data', (d) => (err += d))
    child.on('error', (e) => {
      clearTimeout(timer)
      done({ status: null, out, err: e.message, timedOut })
    })
    child.on('close', (status) => {
      clearTimeout(timer)
      done({ status, out, err, timedOut })
    })
  })
}

const backendFailure = (r, dir, what) => {
  const cause = r.timedOut ? 'timed out' : `exited ${r.status}`
  const text = (r.err || r.out).trim().split('\n').slice(-25).join('\n')
  const failure = new CannotCheck('SNAPSHOT-FAILED', {
    object: what,
    message: `check-backend ${cause} while building the ${what} snapshot, so nothing was compared. It said:\n${text}`,
    fix: `Run \`node scripts/check-backend.mjs --print-snapshot --root ${dir}\` to see it, and fix that first.`,
  })
  // The migrations did not load into the database: not a missing tool or a bad config.
  failure.loadClass = (what === 'head' || what === 'base') && !r.timedOut && LOAD_CLASS.test(`${r.out}\n${r.err}`)
  return failure
}

const LOAD_CLASS = /^\s*(?:LOAD-FAILED|EXTENSION-UNAVAILABLE)\b/m

/** key\tvalue lines from --print-config. */
const parseLines = (text) => {
  const out = {}
  for (const line of text.split('\n')) {
    const i = line.indexOf('\t')
    if (i > 0) (out[line.slice(0, i)] ??= []).push(line.slice(i + 1))
  }
  return out
}

const EMPTY_SNAPSHOT = (by = 'files') => ({ format: 'ntent-schema/1', source: { kind: 'sql', inputs: [] }, tables: {}, emptyBy: by })

/**
 * The snapshot of the watched files in dir. A side with no inputs is empty,
 * never an error: check-backend says so with NO-INPUT, and only that code.
 */
async function snapshotOf(dir, what, ctx) {
  const r = await runBackend(ctx.backend, ['--print-snapshot', '--root', dir, ...(ctx.noCache ? ['--no-cache'] : [])], ctx.timeoutMs)
  if (r.status === 2 && /^\s*NO-INPUT\b/m.test(r.out)) return EMPTY_SNAPSHOT()
  // A base from before the schema existed: its command ran and printed no SQL. That is a new schema, not a broken base.
  if (what === 'base' && r.status === 2 && /^\s*COMMAND-EMPTY\b/m.test(`${r.out}\n${r.err}`)) return EMPTY_SNAPSHOT('command')
  if (r.status !== 0) throw backendFailure(r, dir, what)
  let snapshot
  try {
    snapshot = JSON.parse(r.out)
  } catch (e) {
    throw new CannotCheck('SNAPSHOT-UNREADABLE', { object: what, message: `the ${what} snapshot is not JSON: ${e.message}`, fix: 'Run check-backend --print-snapshot and read its output.' })
  }
  const ok = snapshot && typeof snapshot === 'object' && typeof snapshot.format === 'string' && snapshot.format.startsWith('ntent-schema/') && snapshot.tables && typeof snapshot.tables === 'object'
  if (!ok) {
    throw new CannotCheck('SNAPSHOT-UNREADABLE', { object: what, message: `the ${what} snapshot has no tables, or an unknown format. Nothing was compared.`, fix: 'Check that scripts/check-backend.mjs and scripts/check-migrations.mjs are from the same version.' })
  }
  return snapshot
}

// ------------------------------------------------------------------ DIFF ---

const isTable = (t) => Boolean(t) && !t.platform && (t.kind === undefined || t.kind === 'table' || t.kind === 'partitioned_table')

/** Same matching as check-backend's ignoreTables: exact, or a trailing * prefix. */
const ignored = (key, patterns) => patterns.some((p) => (p.endsWith('*') ? key.startsWith(p.slice(0, -1)) : key === p))

// SQLite keeps whatever declared type it was given and stores by affinity, so
// `varchar(10)` to `varchar(20)` changes nothing, and `integer` to `text`
// changes what a value is. The snapshot gives every SQLite column its affinity.
const AFFINITY_WIDENS = { INTEGER: ['REAL', 'NUMERIC'], REAL: ['NUMERIC'] }

const VARCHAR = /^(?:character varying|varchar)(?:\((\d+)\))?$/
const NUMERIC = /^numeric\((\d+),(\d+)\)$/
const INT_RANK = { smallint: 1, integer: 2, bigint: 3 }

/** A change that cannot lose or reject a value that fit before. */
export function isWidening(from, to) {
  const f = VARCHAR.exec(from)
  if (f) {
    if (to === 'text') return true
    const t = VARCHAR.exec(to)
    if (!t) return false
    if (t[1] === undefined) return true
    return f[1] !== undefined && Number(t[1]) >= Number(f[1])
  }
  if (INT_RANK[from] && INT_RANK[to]) return INT_RANK[to] > INT_RANK[from]
  const fn = NUMERIC.exec(from)
  const tn = NUMERIC.exec(to)
  return Boolean(fn && tn && Number(tn[1]) >= Number(fn[1]) && tn[2] === fn[2])
}

/** True when a column's type changed in a way that can lose or reject a value. */
function typeChanged(bc, hc, wideningAllowed) {
  if (bc.affinity && hc.affinity) {
    if (bc.affinity === hc.affinity) return false
    return !(wideningAllowed && AFFINITY_WIDENS[bc.affinity]?.includes(hc.affinity))
  }
  return bc.type !== hc.type && !(wideningAllowed && isWidening(bc.type, hc.type))
}

/** What the head drops or narrows relative to the base. */
export function diffSnapshots(base, head, { ignoreTables = [], wideningAllowed = true } = {}) {
  const findings = []
  for (const [key, bt] of Object.entries(base.tables)) {
    if (!isTable(bt) || ignored(key, ignoreTables)) continue
    const ht = head.tables[key]
    if (!isTable(ht)) {
      findings.push({ code: 'DROP-TABLE', object: key })
      continue
    }
    for (const [name, bc] of Object.entries(bt.columns)) {
      const object = `${key}.${name}`
      const hc = ht.columns[name]
      if (!hc) {
        findings.push({ code: 'DROP-COLUMN', object })
        continue
      }
      if (typeChanged(bc, hc, wideningAllowed)) findings.push({ code: 'TYPE-CHANGED', object, from: bc.type, to: hc.type })
      if (bc.nullable && !hc.nullable && !hc.identity && !hc.generated) findings.push({ code: 'NOT-NULL-ADDED', object, ...(hc.default != null ? { hasDefault: true } : {}) })
    }
    for (const [name, hc] of Object.entries(ht.columns)) {
      if (bt.columns[name] || hc.nullable || hc.default != null || hc.identity || hc.generated) continue
      findings.push({ code: 'NOT-NULL-ADDED', object: `${key}.${name}` })
    }
  }
  return findings
}

// ------------------------------------------------------------- DECISIONS ---

const keepLines = (s) => s.replace(/[^\n]/g, '')

/** Entries of the decision log: the text between two `## ` headings. Fenced blocks and comments are examples, not decisions. */
export function decisionEntries(text) {
  const clean = text
    .replace(/<!--[\s\S]*?-->/g, keepLines)
    .replace(/^(```|~~~)[^\n]*\n[\s\S]*?^\1[^\n]*$/gm, keepLines)
  const entries = []
  let cur = null
  for (const line of clean.split('\n')) {
    if (/^## /.test(line)) {
      cur = { title: line.trim(), lines: [] }
      entries.push(cur)
    } else if (cur) cur.lines.push(line)
  }
  return entries.map((e) => ({
    title: e.title,
    spans: new Set([...e.lines.join('\n').matchAll(/`([^`\n]+)`/g)].map((m) => m[1].trim().replace(/^\.\//, ''))),
  }))
}

/** `invoices` for public.invoices, `invoices.pdf_url` for public.invoices.pdf_url. */
const objectForms = (object) => (object.startsWith('public.') ? [object, object.slice('public.'.length)] : [object])

/** The entry that names both the migration file and the object, or null. */
export function findWaiver(entries, file, object) {
  if (!file) return null
  const forms = objectForms(object)
  return entries.find((e) => e.spans.has(file) && forms.some((f) => e.spans.has(f))) ?? null
}

// ----------------------------------------------------------------- RUN ---

const sha = (root, ref) => gitOut(root, ['rev-parse', '--verify', '--quiet', `${ref}^{commit}`])

/** The commit to compare against, or a note that there is nothing to compare with. */
function resolveBase(root, opts, config) {
  const inside = git(root, ['rev-parse', '--is-inside-work-tree'])
  if (inside.status !== 0 || inside.stdout.trim() !== 'true') {
    throw new CannotCheck('NOT-A-REPO', { object: root, message: `${root} is not inside a git repository, so there is no base to compare with.`, fix: 'Run it inside the repository, or pass --root <dir> for the folder that holds scripts/.' })
  }
  const head = sha(root, 'HEAD')
  if (!head) return { empty: true }

  const wanted = opts.against ?? config.baseRef
  let ref = null
  if (wanted) {
    if (!sha(root, wanted)) {
      throw new CannotCheck('BASE-UNRESOLVED', { object: wanted, message: `The base ${wanted} does not resolve to a commit.`, fix: 'Fetch it (CI checkouts are often shallow), or pass --against with a ref that exists.' })
    }
    ref = wanted
  } else {
    ref = DEFAULT_BASES.find((r) => sha(root, r)) ?? null
    if (!ref) {
      throw new CannotCheck('BASE-UNRESOLVED', { object: DEFAULT_BASES.join(', '), message: `None of ${DEFAULT_BASES.join(', ')} resolves, so there is no base branch to compare with.`, fix: 'Set CONFIG.baseRef, or pass --against <ref>.' })
    }
  }
  const mb = git(root, ['merge-base', 'HEAD', ref])
  if (mb.status !== 0 || !mb.stdout.trim()) {
    throw new CannotCheck('NO-MERGE-BASE', { object: ref, message: `HEAD and ${ref} share no history that git can see.`, fix: 'In CI, fetch full history (fetch-depth: 0), or pass --against with a nearer commit.' })
  }
  const mergeBase = mb.stdout.trim()
  // Committing on the base branch: the base is the last commit.
  return { ref, head, base: mergeBase === head ? head : mergeBase, onBase: mergeBase === head }
}

const bytesOf = (dir, path) => {
  try {
    return readFileSync(join(dir, path))
  } catch {
    return null
  }
}

/** The watch paths check-backend says to watch, less what is not a migration input. */
async function watchedPaths(root, backend) {
  const r = await runBackend(backend, ['--print-config', '--root', root], 30000)
  if (r.status !== 0) throw backendFailure(r, root, 'config')
  const lines = parseLines(r.out)
  const watch = (lines.watch ?? []).filter((p) => !NOT_WATCHED(p))
  // The checker's own CONFIG.sources, one JSON object a line. An older checker prints none.
  const sources = (lines.source ?? []).flatMap((l) => {
    try {
      return [JSON.parse(l)]
    } catch {
      return []
    }
  })
  // `warn` while the migrations cannot rebuild the database yet; `gate` or nothing: a snapshot that does not build blocks.
  return { watch, sources, platform: lines.platform?.[0] ?? null, loadGate: lines.loadGate?.[0] === 'warn' ? 'warn' : 'gate' }
}

const GLOB = /[*?[\]{}]/

/**
 * What the base copy takes from the head tree, so the base is built by the
 * same checker and config as the head and not by whatever the base commit
 * held. Two things, never the migrations:
 *
 *   1. The install's own scripts: scripts/check-*.mjs and scripts/lib/. They
 *      replace the base's copies.
 *   2. Helper files CONFIG.sources names that the base does not have: a script
 *      a `run` source calls (scripts/print_schema.py), and a base schema file
 *      listed first in front of the migrations. A helper the base already has
 *      stays as the base had it.
 *
 * Returns the helper paths it added (1 is not listed: it is the checker itself).
 */
function overlayInstall(headDir, baseDir, sources) {
  const copy = (rel) => {
    mkdirSync(dirname(join(baseDir, rel)), { recursive: true })
    cpSync(join(headDir, rel), join(baseDir, rel))
  }
  const scripts = join(headDir, 'scripts')
  if (existsSync(scripts)) {
    for (const f of walkWatch(headDir, ['scripts/lib/'])) copy(f)
    for (const e of readdirSync(scripts, { withFileTypes: true })) if (e.isFile() && /^check-.+\.mjs$/.test(e.name)) copy(`scripts/${e.name}`)
  }
  const wanted = []
  for (const [i, source] of sources.entries()) {
    if (source?.kind === 'command' && Array.isArray(source.run)) {
      for (const arg of source.run) wanted.push(arg)
    } else if (source?.kind === 'sql' && i === 0 && sources.length > 1 && Array.isArray(source.paths) && source.paths.length === 1) {
      wanted.push(source.paths[0])
    }
  }
  const added = []
  for (const arg of new Set(wanted)) {
    if (typeof arg !== 'string' || !arg || arg.startsWith('-') || isAbsolute(arg) || GLOB.test(arg)) continue
    const rel = posix(join(arg))
    if (rel.startsWith('..') || /(^|\/)(node_modules|\.venv|\.git)(\/|$)/.test(rel)) continue
    let st
    try {
      st = statSync(join(headDir, rel))
    } catch {
      continue
    }
    if (!st.isFile() || existsSync(join(baseDir, rel))) continue
    copy(rel)
    added.push(rel)
  }
  return added.sort()
}

/** The decision log as the head sees it: the index under --staged, else the worktree. */
function readDecisions(root, file, staged) {
  if (staged) {
    const r = git(root, ['show', `:./${file}`])
    return r.status === 0 ? r.stdout : ''
  }
  try {
    return readFileSync(join(root, file), 'utf8')
  } catch {
    return ''
  }
}

const plural = (n, word) => `${n} ${word}${n === 1 ? '' : 's'}`

const block = (p) => `  ${p.code}${p.object ? `  ${p.object}` : ''}\n    ${String(p.message ?? '').split('\n').join('\n    ')}${p.fix ? `\n    fix: ${p.fix}` : ''}`

const findingLine = (f) => `  ${f.code}  ${f.object}${f.file ? `  in ${f.file}` : ''}`

const waiverLine = (f) => `decided  ${f.code} ${f.object}  in ${f.file}  (${f.waivedBy})`

function report({ result, opts, log }) {
  const { exit, findings, waived, notes, problems, meta } = result
  if (opts.json) {
    log(JSON.stringify({ exit, stage: meta.stage, base: meta.base, head: meta.head, scope: meta.scope ?? null, findings, waived, notes, problems }, null, 2))
    return exit
  }
  const out = []
  if (opts.check && exit === 0) {
    for (const w of waived) out.push(waiverLine(w))
    for (const n of notes) out.push(`  note  ${n.code}  ${n.object ? `${n.object}: ` : ''}${n.message}`)
    out.push(`migrations: ${meta.summary}, ${plural(findings.length + waived.length, 'change')} needing a decision${waived.length ? ` (${waived.length} decided)` : ''}`)
    log(out.join('\n'))
    return exit
  }
  out.push(`check-migrations: ${meta.summary}`)
  if (findings.length) out.push('', `  ${plural(findings.length, 'finding')}:`, ...findings.flatMap((f) => ['', `${findingLine(f)}\n    ${f.message}\n    fix: ${f.fix}`]))
  if (problems.length) out.push('', `  could not check ${plural(problems.length, 'thing')}. Exit 2 is never a pass:`, ...problems.flatMap((p) => ['', block(p)]), ...scopeLines(meta.scope))
  if (waived.length) out.push('', ...waived.map(waiverLine))
  if (notes.length) {
    out.push('')
    for (const n of notes) out.push(`  note  ${n.code}  ${n.object ? `${n.object}: ` : ''}${n.message}${n.fix ? `\n        fix: ${n.fix}` : ''}`)
  }
  if (exit === 0) out.push('', '  migrations: ok.')
  else if (exit === 1) out.push('', `  migrations: ${plural(findings.length, 'finding')}.`)
  log(out.join('\n'))
  return exit
}

/** Where it looked, so an exit 2 can be traced to the folder, the ref and the paths. */
const scopeLines = (scope) => {
  if (!scope) return []
  const lines = ['', `  where it looked:`, `    root   ${scope.root}${scope.gitRoot && scope.gitRoot !== scope.root ? `  (inside the git repository at ${scope.gitRoot})` : ''}`]
  if (scope.ref) lines.push(`    base   ${scope.ref} @ ${scope.base.slice(0, 7)}`)
  if (scope.paths) lines.push(`    paths  ${scope.paths.length ? scope.paths.join(', ') : '(none: CONFIG.sources names nothing)'}`)
  return lines
}

const tidy = (dir) => {
  try {
    rmSync(dir, { recursive: true, force: true })
  } catch {
    // A temp directory that will not delete is not worth failing the check for.
  }
}

/**
 * The whole check, as a function returning its exit code. The CLI below is a
 * thin wrapper. `backend` is a test seam: the script to spawn for snapshots.
 */
export async function run({
  argv = [],
  cwd = process.cwd(),
  config = CONFIG,
  backend = BACKEND,
  ownRoot = null,
  log = console.log,
  err = (s) => process.stderr.write(`${s}\n`),
} = {}) {
  const started = performance.now()
  const timing = {}
  const lap = (name, since) => (timing[name] = Math.round(performance.now() - since))
  let opts = {}
  // Filled in as the run learns it, and printed with every exit 2.
  let scope = null
  const stop = (problem) =>
    report({ result: { exit: 2, findings: [], waived: [], notes: [], problems: [problem.toJSON ? problem.toJSON() : problem], meta: { stage: config?.stage ?? null, base: null, head: null, scope, summary: 'could not check' } }, opts, log })

  try {
    opts = parseArgs(argv)
  } catch (e) {
    return stop(e)
  }
  const configProblems = validateConfig(config)
  if (configProblems.length) {
    if (opts.printConfig) {
      for (const p of configProblems) err(block(p))
      return 2
    }
    opts = { ...opts, json: opts.json }
    const first = configProblems[0]
    return report({ result: { exit: 2, findings: [], waived: [], notes: [], problems: configProblems, meta: { stage: null, base: null, head: null, summary: `${first.object} is invalid` } }, opts, log })
  }

  const timeoutMs = (BACKEND_CONFIG.timeoutMs ?? 60000) + 30000
  const ctx = { backend, timeoutMs, noCache: Boolean(opts.noCache) }
  const tmps = []
  const tmp = () => {
    const dir = mkdtempSync(join(tmpdir(), 'ntent-migrations-'))
    tmps.push(dir)
    return dir
  }

  let loadGate = 'gate'
  try {
    // The folder that holds scripts/ and contract.json, the way check-backend
    // has it: --root, else this script's own folder, else the nearest install
    // above where it was run. In a package inside a repository that is not the
    // git top level, and taking the top level made both sides empty.
    const start = opts.root ? resolve(cwd, opts.root) : ownRoot ? resolve(ownRoot) : installRootFrom(resolve(cwd))
    if (!existsSync(start) || !statSync(start).isDirectory()) {
      throw new CannotCheck('ROOT-MISSING', { object: start, message: `${start} is not a directory, so there is nothing to check there.`, fix: 'Pass --root <dir> with the folder that holds scripts/ and contract.json.' })
    }
    const where = locate(start)
    const root = where.root
    scope = { root, gitRoot: where.top, ref: null, base: null, paths: null }

    if (opts.printConfig) {
      const { watch } = await watchedPaths(root, backend)
      log(`stage\t${config.stage}`)
      log(`baseRef\t${config.baseRef ?? ''}`)
      log(`decisionsFile\t${config.decisionsFile}`)
      for (const m of config.mutable) log(`mutable\t${m}`)
      for (const w of watch) log(`watch\t${w}`)
      return 0
    }

    const side = resolveBase(where.top ?? root, opts, config)
    if (!side.empty) scope = { ...scope, ref: side.ref, base: side.base }
    if (side.empty) {
      const note = { code: 'NO-COMMITS', object: '', message: 'No commits yet, so there is no base and nothing can be dropped. Nothing was compared.', fix: '', severity: 'note' }
      return report({ result: { exit: 0, findings: [], waived: [], notes: [note], problems: [], meta: { stage: config.stage, base: null, head: null, summary: 'no commits yet' } }, opts, log })
    }

    const t0 = performance.now()
    const { watch, sources, loadGate: gate } = await watchedPaths(root, backend)
    loadGate = gate
    scope.paths = watch
    lap('config', t0)

    // Copy both sides, so the snapshot builder reads exactly the files the commit holds.
    // The whole install folder, not just the watched files: a config that
    // imports the app's own code fails on a copy that lacks it.
    const t1 = performance.now()
    const baseCopy = materialiseBase(where, side.base, watch, tmp())
    const headCopy = opts.staged ? materialiseIndex(where, watch, tmp()) : materialiseWorktree(where, watch, tmp())
    const baseDir = baseCopy.dir
    const headDir = headCopy.dir
    // A base that holds none of the watched inputs has no schema to build: the
    // helpers the overlay would add only read those inputs, and would fail on them.
    const baseBare = baseCopy.files.length === 0
    const overlaid = overlayInstall(headDir, baseDir, sources.length ? sources : (BACKEND_CONFIG.sources ?? []))
    const baseFiles = baseCopy.files
    const headFiles = headCopy.files
    lap('copy', t1)

    const t2 = performance.now()
    // Both at once: they only share check-backend's cache directory, and its
    // cache writes are atomic and its prune skips what another run deleted.
    // Both are settled before either failure is raised, so the temp copies are
    // never removed under a snapshot that is still building.
    const [base, head] = await Promise.allSettled([baseBare ? EMPTY_SNAPSHOT() : snapshotOf(baseDir, 'base', ctx), snapshotOf(headDir, 'head', ctx)])
    // The head first: a head that does not build is the thing to fix before
    // the base matters.
    if (head.status === 'rejected') throw head.reason
    if (base.status === 'rejected') throw baseUnbuildable(base.reason, side)
    const baseSnap = base.value
    const headSnap = head.value
    lap('snapshots', t2)
    const notes = []
    const baseEmpty = hasNoInputs(baseSnap)
    if (baseEmpty && hasNoInputs(headSnap)) {
      const nothing = {
        code: 'NOTHING-TO-COMPARE',
        object: root,
        message: `No migration files were found on the base (${side.ref ?? 'HEAD'} @ ${side.base.slice(0, 7)}) or on the head under ${root}, so nothing was compared. Looked at: ${watch.join(', ') || '(no paths)'}.`,
        fix: 'Run it from the folder that holds scripts/ and contract.json, or pass --root <dir>. If the migrations live elsewhere, point CONFIG.sources in scripts/check-backend.mjs at them.',
      }
      // The hook runs this whenever the checker itself is staged, and a repo
      // that has not written its first migration yet must still commit.
      if (!opts.allowEmpty) throw new CannotCheck(nothing.code, nothing)
      notes.push({ ...nothing, object: '', severity: 'note' })
    } else if (baseEmpty) {
      const none = baseSnap.emptyBy === 'command' ? 'prints no schema (its command gave no SQL)' : `has no migration files under ${root}`
      notes.push({ code: 'BASE-EMPTY', object: '', message: `The base (${side.ref ?? 'HEAD'} @ ${side.base.slice(0, 7)}) ${none}, so this is a new schema and there is nothing to lose. Nothing was compared.`, fix: '', severity: 'note' })
    }
    if (overlaid.length && !baseBare) {
      notes.push({ code: 'BASE-OVERLAY', object: '', message: `The base ${side.ref ?? 'HEAD'} @ ${side.base.slice(0, 7)} does not have ${overlaid.join(', ')}, which CONFIG.sources needs, so the head's copy was used to build the base.`, fix: '', severity: 'note' })
    }
    const formats = new Set([baseSnap.format, headSnap.format])
    if (formats.size > 1) {
      throw new CannotCheck('SNAPSHOT-UNREADABLE', { object: [...formats].join(' vs '), message: 'The two snapshots are in different formats, so they cannot be compared.', fix: 'Both come from scripts/check-backend.mjs. Reinstall the scripts together.' })
    }

    const mutable = config.mutable.map(globRe)
    const isMutable = (p) => mutable.some((re) => re.test(p))
    // SQL files load in order, so the migration that introduces a change can
    // be found by bisection. A command source has no such order.
    const perFile = headSnap.source?.kind === 'sql' && baseSnap.source?.kind === 'sql'
    const baseInputs = migrationInputs(baseSnap, baseFiles)
    const headInputs = migrationInputs(headSnap, headFiles)

    // Edits to a migration that already ran wherever the base is deployed.
    const touched = []
    const findings = []
    for (const path of baseInputs) {
      if (isMutable(path)) continue
      const was = bytesOf(baseDir, path)
      const now = bytesOf(headDir, path)
      if (now !== null && was !== null && was.equals(now)) continue
      touched.push(path)
      const how = now === null ? 'was deleted' : 'was edited'
      findings.push({ code: 'MIGRATION-EDITED', object: path, file: path, message: `${path} ${how} after it reached the base branch.`, fix: FIX['MIGRATION-EDITED']() })
    }

    const changed = perFile ? headInputs.filter((p) => !isMutable(p) && !(bytesOf(baseDir, p)?.equals(bytesOf(headDir, p) ?? Buffer.alloc(0)))) : []
    const changedText = changed.map((p) => stripSqlComments((bytesOf(headDir, p) ?? Buffer.alloc(0)).toString('utf8')))
    const diff = []
    for (const d of diffSnapshots(baseSnap, headSnap, { ignoreTables: BACKEND_CONFIG.ignoreTables ?? [], wideningAllowed: config.wideningAllowed })) {
      const file = d.code === 'NOT-NULL-ADDED' ? changed.find((_, i) => backfills(changedText[i], d.object)) : null
      if (!file) diff.push(d)
      else {
        notes.push({
          code: 'NOT-NULL-BACKFILLED',
          object: d.object,
          message: `${file} makes ${d.object} NOT NULL after an UPDATE that sets it, so it was not flagged. Whether the UPDATE reaches every row is not checked.`,
          severity: 'note',
        })
      }
    }

    // Which migration introduces each change. Only needed to word the fix and to match a waiver.
    const t3 = performance.now()
    const introducers = diff.length ? await introducersOf({ diff, baseSnap, baseDir, headDir, headInputs, perFile, headFiles, baseFiles, touched, isMutable, config, ctx, tmp, root }) : new Map()
    lap('attribution', t3)

    const entries = diff.length ? decisionEntries(readDecisions(root, config.decisionsFile, Boolean(opts.staged))) : []
    const waived = []
    for (const d of diff) {
      // Several files can be the one that changed, for a command source. A
      // decision naming any of them counts.
      const candidates = introducers.get(`${d.code} ${d.object}`) ?? []
      let entry = null
      let file = candidates[0] ?? null
      for (const c of candidates) {
        entry = findWaiver(entries, c, d.object)
        if (entry) {
          file = c
          break
        }
      }
      // SQLite has no schemas: the snapshot keys its tables under public, and the findings should not say so.
      const shown = headSnap.source?.platform === 'sqlite' ? { ...d, object: d.object.replace(/^(?:public|main)\./, '') } : d
      const item = { ...shown, file: file ?? '(unknown migration)', message: '', fix: FIX[d.code](shown, file) }
      if (entry) waived.push({ ...item, waivedBy: `${config.decisionsFile}: "${entry.title}"` })
      else {
        item.message = describe(shown)
        findings.push(item)
      }
    }

    let exit = findings.length ? 1 : 0
    if (findings.length && config.stage === 'pre-production') {
      for (const f of findings) notes.push({ code: f.code, object: f.object, message: `${f.message} (pre-production: a note, not a failure)`, fix: f.fix, severity: 'note' })
      findings.length = 0
      exit = 0
    }
    const tables = Object.values(headSnap.tables).filter(isTable).length
    const summary = `${config.stage}, ${inputsSummary(headSnap, headInputs)}, ${plural(tables, 'table')}, base ${side.ref ?? 'HEAD'} @ ${side.base.slice(0, 7)}${side.onBase ? ' (HEAD)' : ''}, head ${opts.staged ? 'index' : 'worktree'}`
    if (opts.timing) err(`timing ${Object.entries(timing).map(([k, v]) => `${k}=${v}ms`).join(' ')} total=${((performance.now() - started) / 1000).toFixed(2)}s`)
    return report({ result: { exit, findings, waived, notes, problems: [], meta: { stage: config.stage, base: side.base, head: opts.staged ? 'index' : 'worktree', scope, summary } }, opts, log })
  } catch (e) {
    // While the repo's migrations cannot rebuild its database, the gate only warns (check-backend says so too). A tool or config problem still blocks.
    if (e instanceof CannotCheck && loadGate === 'warn' && e.loadClass) {
      const note = { code: 'NOT-COMPARED', object: e.object ?? '', message: `The migrations do not load into a database yet, so the schema was not compared.\n${e.message}`, fix: 'Fix the load failure (check-backend names it); until it loads this check only warns.', severity: 'note' }
      return report({ result: { exit: 0, findings: [], waived: [], notes: [note], problems: [], meta: { stage: config.stage, base: null, head: null, scope, summary: 'migrations do not load, not compared (warn until it loads)' } }, opts, log })
    }
    if (e instanceof CannotCheck) return stop(e)
    throw e
  } finally {
    for (const dir of tmps) tidy(dir)
  }
}

const stripSqlComments = (sql) => sql.replace(/\/\*[\s\S]*?\*\//g, ' ').replace(/--[^\n]*/g, ' ')

/**
 * True when one migration's text backfills a column and then makes it NOT NULL:
 * an UPDATE that sets it, and ALTER COLUMN ... SET NOT NULL. `object` is
 * schema.table.column. A column added NOT NULL with a default is not flagged
 * to begin with, and one added NOT NULL with neither fails on any table that
 * has rows.
 */
export function backfills(sql, object) {
  const parts = object.split('.')
  const column = parts.pop()
  const table = parts.pop()
  const esc = (x) => x.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')
  const name = (x) => `"?${esc(x)}"?`
  const t = `(?:"?\\w+"?\\s*\\.\\s*)?${name(table)}`
  const update = new RegExp(`\\bupdate\\s+(?:only\\s+)?${t}\\s+(?:(?:as\\s+)?(?!set\\b)"?\\w+"?\\s+)?set\\b[^;]*?(?:^|[\\s,(])${name(column)}\\s*=`, 'is')
  const alter = new RegExp(`\\balter\\s+table\\s+(?:if\\s+exists\\s+)?(?:only\\s+)?${t}\\b[^;]*?\\balter\\s+(?:column\\s+)?${name(column)}\\s+set\\s+not\\s+null`, 'is')
  return update.test(sql) && alter.test(sql)
}

/** A side with no migration files: check-backend's NO-INPUT, as snapshotOf turns it. */
const hasNoInputs = (snap) => (snap.source?.inputs ?? []).length === 0 && Object.keys(snap.tables).length === 0

/** The base cannot be built but the head can: say which ref, why, and how to go on. */
function baseUnbuildable(reason, side) {
  if (!(reason instanceof CannotCheck) || reason.code !== 'SNAPSHOT-FAILED') return reason
  const ref = `${side.ref ?? 'HEAD'} @ ${side.base.slice(0, 7)}`
  const unbuildable = new CannotCheck('BASE-UNBUILDABLE', {
    object: ref,
    message: `The base ${ref} does not build a schema, though the head does, so there is nothing to compare the head with.\n${reason.message}`,
    fix: `Compare with a base that builds: pass --against <a newer ref> (CI), or set CONFIG.baseRef in scripts/check-migrations.mjs to one (the pre-commit hook). A migration that was broken at the base and repaired since is the usual cause. This stays exit 2 until a comparison can run; it is never skipped.`,
  })
  unbuildable.loadClass = reason.loadClass
  return unbuildable
}

/** Snapshot builds attribution runs together. Each is a PGlite in memory, about 0.8 GB at peak. */
const BUILDS_AT_ONCE = Math.max(1, Math.min(3, availableParallelism() - 1))

const isCommandInput = (input) => typeof input === 'string' && input.startsWith('command:')

// Which files of a command source are history, not the schema it is built from.
// Prisma and Drizzle print the schema from a declarative file, which is edited
// freely (and the Prisma one is in CONFIG.mutable). Alembic's versions are
// history. A generic `run` source is history where it watches a migrations folder.
const HISTORY = { 'command:alembic': /(^|\/)versions\/[^/]+\.py$/, 'command:run': /(^|\/)migrations?\// }

/**
 * The files that are migrations: the SQL files a snapshot was loaded from, and
 * the history files a command source read. `files` is every watched file on
 * that side.
 */
function migrationInputs(snap, files) {
  const inputs = snap.source?.inputs ?? []
  const sql = inputs.filter((i) => !isCommandInput(i))
  const history = inputs.filter(isCommandInput).flatMap((label) => (HISTORY[label] ? files.filter((f) => HISTORY[label].test(f)) : []))
  return [...new Set([...sql, ...history])]
}

const inputsSummary = (snap, migrations) => {
  const commands = (snap.source?.inputs ?? []).filter(isCommandInput)
  const parts = [plural(migrations.length, 'migration')]
  if (commands.length) parts.push(`via ${commands.map((c) => c.slice('command:'.length)).join(', ')}`)
  if (snap.source?.platform === 'sqlite') parts.push('sqlite')
  return parts.join(', ')
}

const describe = (d) => {
  if (d.code === 'DROP-TABLE') return `${d.object} exists on the base branch and is gone here.`
  if (d.code === 'DROP-COLUMN') return `${d.object} exists on the base branch and is gone here.`
  if (d.code === 'TYPE-CHANGED') return `${d.object} is ${d.from} on the base branch and ${d.to} here.`
  if (d.code === 'NOT-NULL-ADDED') return `${d.object} is NOT NULL here ${d.hasDefault ? '(it has a default, which does not fill rows that are already NULL)' : 'with no default'}, and was nullable or absent on the base branch.`
  return ''
}

/**
 * The migration file that introduces each change: the first input, in load
 * order, whose addition makes the head schema differ. Builds prefix snapshots
 * only when more than one input was added or changed, by bisection. For a
 * command source it is every watched file that changed, most likely first. A change caused
 * by a deleted migration is credited to the deleted file.
 */
async function introducersOf({ diff, baseSnap, baseDir, headDir, headInputs, perFile, headFiles, baseFiles, touched, isMutable, config, ctx, tmp, root }) {
  const out = new Map()
  const key = (d) => `${d.code} ${d.object}`
  const differs = (path) => {
    const now = bytesOf(headDir, path)
    const was = bytesOf(baseDir, path)
    return now !== null && (was === null || !was.equals(now))
  }

  // A command source: no per-file load order to bisect. The files that changed,
  // history first, then schema files, then the tool's own config.
  if (!perFile) {
    const CONFIG_LIKE = /(^|\/)(prisma|drizzle)\.config\.[cm]?[jt]s$|(^|\/)alembic\.ini$/
    const history = new Set(headInputs)
    const rank = (p) => (history.has(p) ? 0 : CONFIG_LIKE.test(p) ? 2 : 1)
    const changed = [...new Set([...headFiles, ...baseFiles])]
      .filter((p) => {
        const now = bytesOf(headDir, p)
        const was = bytesOf(baseDir, p)
        return now === null ? was !== null : was === null || !was.equals(now)
      })
      .sort((a, b) => rank(a) - rank(b) || (a < b ? -1 : 1))
    for (const d of diff) out.set(key(d), changed)
    return out
  }

  const positions = headInputs.map((p, i) => (differs(p) ? i : -1)).filter((i) => i >= 0)
  if (positions.length === 0) {
    for (const d of diff) out.set(key(d), touched.slice(0, 1))
    return out
  }
  if (positions.length === 1) {
    for (const d of diff) out.set(key(d), [headInputs[positions[0]]])
    return out
  }

  // A file that never names a finding's table cannot be what changed it, so
  // the search for that finding looks only at the files that do. Files that
  // build SQL at run time or drop a schema could touch any table, so they
  // always count. One name and no deleted or edited base file: no build at all.
  const texts = new Map()
  const textOf = (i) => {
    if (!texts.has(i)) texts.set(i, (bytesOf(headDir, headInputs[i]) ?? Buffer.alloc(0)).toString('utf8'))
    return texts.get(i)
  }
  const anywhere = (i) => /\bexecute\b|\bdo\s+(\$|')|\bdrop\s+schema\b/i.test(textOf(i))
  const namedIn = (d) => {
    const parts = d.object.split('.')
    const table = parts.length > 2 ? parts[parts.length - 2] : parts[parts.length - 1]
    const re = new RegExp(`(^|[^\\w$])${table.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')}($|[^\\w$])`, 'i')
    const named = positions.filter((i) => anywhere(i) || re.test(textOf(i)))
    return named.length ? named : positions
  }

  // One build per distinct prefix, shared by every finding, and at most
  // BUILDS_AT_ONCE running together (each is a database in memory).
  const cache = new Map()
  let running = 0
  const waiting = []
  const slot = async () => {
    if (running >= BUILDS_AT_ONCE) await new Promise((go) => waiting.push(go))
    running++
  }
  const free = () => {
    running--
    waiting.shift()?.()
  }
  const prefixDiff = (last) => {
    if (!cache.has(last)) {
      cache.set(
        last,
        (async () => {
          await slot()
          try {
            const dir = tmp()
            const drop = new Set(headInputs.slice(last + 1))
            const keep = headFiles.filter((p) => !drop.has(p))
            for (const f of keep) {
              mkdirSync(dirname(join(dir, f)), { recursive: true })
              cpSync(join(headDir, f), join(dir, f))
            }
            linkModules(root, dir)
            const snap = await snapshotOf(dir, `head up to ${headInputs[last]}`, ctx)
            return new Set(diffSnapshots(baseSnap, snap, { ignoreTables: BACKEND_CONFIG.ignoreTables ?? [], wideningAllowed: config.wideningAllowed }).map(key))
          } finally {
            free()
          }
        })(),
      )
    }
    return cache.get(last)
  }

  // Every finding is searched for together: one probe at the middle of the
  // files still in doubt splits all the findings that have candidates on both
  // sides of it, so findings that name the same files share their builds.
  // Each carries its candidates (sorted); it is known to be there once the
  // last of them is loaded, and the question is which candidate made it so.
  const search = async (items) => {
    const open = items.filter((it) => it.cands.length > 1)
    for (const it of items) if (it.cands.length === 1) out.set(it.key, [headInputs[it.cands[0]]])
    for (const it of items) if (it.cands.length === 0) out.set(it.key, [headInputs[it.fallback]])
    if (!open.length) return
    const all = [...new Set(open.flatMap((it) => it.cands))].sort((a, b) => a - b)
    const m = all[(all.length - 1) >> 1]
    const found = await prefixDiff(m)
    const yes = open.filter((it) => found.has(it.key)).map((it) => ({ ...it, cands: it.cands.filter((c) => c <= m), fallback: m }))
    const no = open.filter((it) => !found.has(it.key)).map((it) => ({ ...it, cands: it.cands.filter((c) => c > m) }))
    await Promise.all([search(yes), search(no)])
  }
  const items = []
  for (const d of diff) {
    const cands = namedIn(d)
    if (cands.length === 1 && !touched.length) {
      out.set(key(d), [headInputs[cands[0]]])
      continue
    }
    items.push({ key: key(d), cands, fallback: cands[cands.length - 1] })
  }
  // Is it there once the last candidate is loaded? If not, an edited or deleted base file did it.
  const checked = await Promise.all(items.map(async (it) => ((await prefixDiff(it.cands[it.cands.length - 1])).has(it.key) ? it : null)))
  for (const [i, it] of items.entries()) if (!checked[i]) out.set(it.key, [touched[0] ?? headInputs[it.cands[it.cands.length - 1]]])
  await search(checked.filter(Boolean))
  return out
}

// ------------------------------------------------------------ ENTRYPOINT ---

const invokedDirectly = (() => {
  try {
    return realpathSync(process.argv[1] ?? '') === realpathSync(SELF)
  } catch {
    return false
  }
})()

if (invokedDirectly) {
  // A crash is exit 2, never 1: a stack trace is not a finding about a migration.
  // The root is the folder above this script's own, as for check-backend, so
  // where it is run from does not matter. --root overrides.
  const ownRoot = basename(dirname(SELF)) === 'scripts' ? dirname(dirname(SELF)) : null
  const code = await run({ argv: process.argv.slice(2), cwd: process.cwd(), config: CONFIG, ownRoot }).catch((e) => {
    console.error(e?.stack ?? e)
    return 2
  })
  // Exit once stdout has drained, or a long report on a pipe is cut off.
  process.stdout.write('', () => process.exit(code))
}