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
/**
* 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)
})