Skip to content

Where agent teams gather. Cross-platform team chat where AI agents are first-class teammates — with cloud or bring-your-own (Claude Code / Codex) brains.

TypeScript
9.3 KB
Raw Blame
/**
 * Database migration transaction atomicity.
 *
 * Migration DDL and ledger record must be atomic. If a migration is interrupted
 * or fails midway, PostgreSQL must roll back all partial schema changes and
 * the schema_migrations ledger must have no record, allowing clean recovery
 * on rerun.
 *
 * Run: node --import tsx --test server/src/__tests__/migration-transaction-atomicity.test.ts
 */
import { test } from 'node:test'
import assert from 'node:assert/strict'
import type { PoolClient } from 'pg'

process.env.CUMORA_RUNTIME_CLIENT = 'http'
process.env.OPENAI_API_KEY ??= 'test-key'

const { applyPendingMigration } = await import('../db/migrate.js')
type VersionedMigration = import('../db/migrate.js').VersionedMigration

function fakeClient(opts: { failRollback?: boolean } = {}) {
  const calls: { sql: string; params?: unknown[] }[] = []
  const client = {
    calls,
    query: async (sql: string, params?: unknown[]) => {
      calls.push({ sql: sql.trim(), params })
      if (sql.trim() === 'ROLLBACK' && opts.failRollback) {
        throw new Error('rollback network error')
      }
      return { rows: [] }
    },
    release: () => {},
  } as unknown as PoolClient
  return { client, calls }
}

test('transactional migration executes within BEGIN and COMMIT', async () => {
  const { client, calls } = fakeClient()
  let upCalled = false

  const migration: VersionedMigration = {
    version: 2,
    name: '0002-test',
    checksum: 'a'.repeat(64),
    sourceChecksum: 'a'.repeat(64),
    transactional: true,
    up: async () => {
      upCalled = true
      calls.push({ sql: 'CREATE TABLE test_table (id INT)' })
    },
  }

  await applyPendingMigration(client, migration)

  assert.ok(upCalled, 'migration.up should be called')
  assert.equal(calls[0].sql, 'BEGIN')
  assert.equal(calls[1].sql, 'CREATE TABLE test_table (id INT)')
  assert.match(calls[2].sql, /^INSERT INTO schema_migrations/i)
  assert.equal(calls[3].sql, 'COMMIT')
})

test('transactional migration defaults to true when transactional is omitted for version >= 2', async () => {
  const { client, calls } = fakeClient()

  const migration: VersionedMigration = {
    version: 5,
    name: '0005-test-default',
    checksum: 'b'.repeat(64),
    sourceChecksum: 'b'.repeat(64),
    up: async () => {
      calls.push({ sql: 'ALTER TABLE test ADD COLUMN foo TEXT' })
    },
  }

  await applyPendingMigration(client, migration)

  assert.equal(calls[0].sql, 'BEGIN')
  assert.equal(calls[1].sql, 'ALTER TABLE test ADD COLUMN foo TEXT')
  assert.match(calls[2].sql, /^INSERT INTO schema_migrations/i)
  assert.equal(calls[3].sql, 'COMMIT')
})

test('failing migration issues ROLLBACK and never records ledger entry', async () => {
  const { client, calls } = fakeClient()

  const migration: VersionedMigration = {
    version: 3,
    name: '0003-failing',
    checksum: 'c'.repeat(64),
    sourceChecksum: 'c'.repeat(64),
    transactional: true,
    up: async () => {
      calls.push({ sql: 'ALTER TABLE bad ADD COLUMN bar INT' })
      throw new Error('relation bad does not exist')
    },
  }

  await assert.rejects(
    () => applyPendingMigration(client, migration),
    /relation bad does not exist/,
  )

  const statements = calls.map((c) => c.sql)
  assert.equal(statements[0], 'BEGIN')
  assert.equal(statements[1], 'ALTER TABLE bad ADD COLUMN bar INT')
  assert.equal(statements[2], 'ROLLBACK')
  assert.ok(!statements.includes('COMMIT'), 'COMMIT must never be called on failure')
  assert.ok(
    !statements.some((s) => s.startsWith('INSERT INTO schema_migrations')),
    'schema_migrations record must never be written on failure',
  )
})

test('non-transactional migration (transactional: false) runs without BEGIN/COMMIT', async () => {
  const { client, calls } = fakeClient()

  const migration: VersionedMigration = {
    version: 1,
    name: '0001-baseline',
    checksum: 'd'.repeat(64),
    sourceChecksum: 'd'.repeat(64),
    transactional: false,
    up: async () => {
      calls.push({ sql: 'CREATE INDEX CONCURRENTLY idx_foo ON foo(id)' })
    },
  }

  await applyPendingMigration(client, migration)

  const statements = calls.map((c) => c.sql)
  assert.ok(!statements.includes('BEGIN'), 'BEGIN must not be called')
  assert.ok(!statements.includes('COMMIT'), 'COMMIT must not be called')
  assert.equal(statements[0], 'CREATE INDEX CONCURRENTLY idx_foo ON foo(id)')
  assert.match(statements[1], /^INSERT INTO schema_migrations/i)
})

test('rollback failure still preserves original migration error', async () => {
  const { client } = fakeClient({ failRollback: true })

  const migration: VersionedMigration = {
    version: 4,
    name: '0004-rollback-failure',
    checksum: 'e'.repeat(64),
    sourceChecksum: 'e'.repeat(64),
    transactional: true,
    up: async () => {
      throw new Error('primary migration error')
    },
  }

  await assert.rejects(
    () => applyPendingMigration(client, migration),
    /primary migration error/,
  )
})

/** A PostgreSQL error as node-postgres surfaces it: the SQLSTATE is on `code`. */
function pgError(code: string, message: string): Error & { code: string } {
  return Object.assign(new Error(message), { code })
}

test('a migration that loses a lock race is retried after a clean rollback', async () => {
  const { client, calls } = fakeClient()
  let attempts = 0
  const slept: number[] = []

  const migration: VersionedMigration = {
    version: 4,
    name: '0004-lock-contention',
    checksum: 'f'.repeat(64),
    sourceChecksum: 'f'.repeat(64),
    transactional: true,
    up: async () => {
      attempts += 1
      calls.push({ sql: `ALTER TABLE participants ADD COLUMN x TEXT -- attempt ${attempts}` })
      // `ensureSchema` pins lock_timeout at 5s, so DDL on a hot table under
      // live traffic aborts with 55P03 long before it does any work.
      if (attempts < 3) throw pgError('55P03', 'canceling statement due to lock timeout')
    },
  }

  await applyPendingMigration(client, migration, { sleep: async (ms) => { slept.push(ms) } })

  assert.equal(attempts, 3)
  const statements = calls.map((c) => c.sql)
  // Each failed attempt rolls back completely, so attempt N+1 starts from
  // exactly the state attempt N saw. No partial schema, no partial ledger.
  assert.equal(statements.filter((s) => s === 'BEGIN').length, 3)
  assert.equal(statements.filter((s) => s === 'ROLLBACK').length, 2)
  assert.equal(statements.filter((s) => s === 'COMMIT').length, 1)
  assert.equal(statements.filter((s) => s.startsWith('INSERT INTO schema_migrations')).length, 1)
  assert.equal(slept.length, 2, 'each retry must back off')
  assert.ok(slept[1] > slept[0], `backoff must grow, got ${slept.join(', ')}`)
})

test('deadlock and serialization failures retry too; a real error never does', async () => {
  for (const code of ['40P01', '55P03', '40001']) {
    const { client } = fakeClient()
    let attempts = 0
    const migration: VersionedMigration = {
      version: 4,
      name: `0004-${code}`,
      checksum: 'f'.repeat(64),
      sourceChecksum: 'f'.repeat(64),
      transactional: true,
      up: async () => {
        attempts += 1
        if (attempts < 2) throw pgError(code, `transient ${code}`)
      },
    }
    await applyPendingMigration(client, migration, { sleep: async () => {} })
    assert.equal(attempts, 2, `${code} should have been retried once`)
  }

  // A missing relation, a failed precheck, a constraint violation: the deploy
  // has to stop on the first attempt and say so.
  const { client } = fakeClient()
  let realAttempts = 0
  await assert.rejects(
    () => applyPendingMigration(client, {
      version: 4,
      name: '0004-real-error',
      checksum: 'f'.repeat(64),
      sourceChecksum: 'f'.repeat(64),
      transactional: true,
      up: async () => {
        realAttempts += 1
        throw pgError('42P01', 'relation "nope" does not exist')
      },
    }, { sleep: async () => {} }),
    /relation "nope" does not exist/,
  )
  assert.equal(realAttempts, 1, 'a real error must not be retried')
})

test('sustained lock contention gives up and fails the deploy', async () => {
  const { client } = fakeClient()
  let attempts = 0

  await assert.rejects(
    () => applyPendingMigration(client, {
      version: 4,
      name: '0004-always-locked',
      checksum: 'f'.repeat(64),
      sourceChecksum: 'f'.repeat(64),
      transactional: true,
      up: async () => {
        attempts += 1
        throw pgError('55P03', 'canceling statement due to lock timeout')
      },
    }, { sleep: async () => {}, maxAttempts: 4 }),
    /lock timeout/,
  )
  assert.equal(attempts, 4, 'the budget is finite — a wedged table must not hang the Job forever')
})

test('a non-transactional migration is never retried wholesale', async () => {
  // 0001 retries per statement internally and 0005/0006 build indexes
  // CONCURRENTLY; re-running either from here could re-execute work that
  // already committed, because there is no transaction to roll back.
  const { client } = fakeClient()
  let attempts = 0

  await assert.rejects(
    () => applyPendingMigration(client, {
      version: 5,
      name: '0005-concurrent',
      checksum: 'f'.repeat(64),
      sourceChecksum: 'f'.repeat(64),
      transactional: false,
      up: async () => {
        attempts += 1
        throw pgError('55P03', 'canceling statement due to lock timeout')
      },
    }, { sleep: async () => {} }),
    /lock timeout/,
  )
  assert.equal(attempts, 1)
})