Function classify

scripts/check-migrations-safety.ts:194–326

* Classify one statement. `createdTables` holds tables created in the same * migration — ops against a brand-new table have no old rows and no live * traffic, so they are always safe and skipped. `sawCommit` tracks whether a * `COMMIT;` breakpoint preceded a CONCURRENTLY index (see migrate.ts).

(sql: string, createdTables: Set<string>, sawCommit: boolean)

 * `COMMIT;` breakpoint preceded a CONCURRENTLY index (see migrate.ts).
 */
function classify(sql: string, createdTables: Set<string>, sawCommit: boolean): RawMatch[] {
  const s = sql.replace(/\s+/g, ' ').trim()
  const matches: RawMatch[] = []

  const alterTable = s.match(/\bALTER TABLE (?:IF EXISTS )?(?:ONLY )?("?[.\w]+"?)/i)
  const targetTable = alterTable ? bareName(alterTable[1]) : null
  const onNewTable = targetTable !== null && createdTables.has(targetTable)

  if (/^CREATE (?:UNIQUE )?INDEX\b/i.test(s)) {
    const on = s.match(/\bON ("?[.\w]+"?)/i)
    const indexTable = on ? bareName(on[1]) : null
    const concurrent = /\bCONCURRENTLY\b/i.test(s)
    if (!(indexTable && createdTables.has(indexTable))) {
      if (!concurrent) {
        matches.push({
          kind: 'error',
          rule: 'index-not-concurrent',
          message:
            'CREATE INDEX on an existing table write-locks it for the whole build. Use CREATE INDEX CONCURRENTLY IF NOT EXISTS after a COMMIT; breakpoint (see packages/db/scripts/migrate.ts).',
        })
      } else if (!/\bIF NOT EXISTS\b/i.test(s)) {
        matches.push({
          kind: 'error',
          rule: 'concurrent-index-not-idempotent',
          message:
            'CREATE INDEX CONCURRENTLY must be IF NOT EXISTS — a failed build replays from the top and a partial INVALID index would be skipped forever.',
        })
      } else if (!sawCommit) {
        matches.push({
          kind: 'error',
          rule: 'concurrent-index-no-commit',
          message:
            'CREATE INDEX CONCURRENTLY cannot run inside the migration transaction. Precede it with a COMMIT; breakpoint and SET lock_timeout = 0 (see packages/db/scripts/migrate.ts).',
        })
      }
    }
  }

  if (
    !onNewTable &&
    /\bADD COLUMN\b/i.test(s) &&
    /\bNOT NULL\b/i.test(s) &&
    !/\bDEFAULT\b/i.test(s)
  ) {
    matches.push({
      kind: 'error',
      rule: 'add-not-null-no-default',
      message:
        'ADD COLUMN NOT NULL with no DEFAULT breaks old inserts (and fails on existing rows). Add it nullable or with a DEFAULT, backfill, then SET NOT NULL in a later migration once code populates it.',
    })
  }

  if (/\bRENAME COLUMN\b/i.test(s) || /^ALTER TABLE\b[^;]*\bRENAME TO\b/i.test(s)) {
    matches.push({
      kind: 'error',
      rule: 'rename',
      message:
        'RENAME of a column/table breaks old code reading the old name. Add the new column/table, dual-write in code, then drop the old one in a later deploy.',