f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
298 lines
10 KiB
JavaScript
298 lines
10 KiB
JavaScript
// ADR-0034 §7 — Unit tests for booking-constraint helper.
|
|
//
|
|
// Runs against godcrm_test (ADR-0009 boot guard enforces this). Each test
|
|
// allocates a fresh project + universal_table so the EXCLUDE constraint is
|
|
// scoped to a private table_id and never collides with seeded data.
|
|
|
|
import { describe, test, expect, beforeAll, beforeEach, afterEach } from 'vitest';
|
|
import { dbAll, dbGet, dbRun } from '../database/connection.js';
|
|
import {
|
|
enableBookingConstraint,
|
|
disableBookingConstraint,
|
|
getBookingConstraint,
|
|
findConflictingRowId,
|
|
isBookingConflictError,
|
|
} from '../lib/booking-constraint.js';
|
|
import { generateBaseId } from '../utils/baseId.js';
|
|
|
|
async function makeFreshTable() {
|
|
const project = await dbRun(
|
|
`INSERT INTO projects (name, type, owner_id, created_at, updated_at)
|
|
VALUES ($1, 'default', 1, NOW(), NOW())`,
|
|
[`bcx_proj_${Date.now()}_${Math.random().toString(36).slice(2, 8)}`]
|
|
);
|
|
const projectId = project.lastInsertRowid;
|
|
const table = await dbRun(
|
|
`INSERT INTO universal_tables (project_id, name, display_name, is_system, created_at, updated_at)
|
|
VALUES ($1, $2, 'Bookings', 0, NOW(), NOW())`,
|
|
[projectId, `bcx_t_${Date.now()}_${Math.random().toString(36).slice(2, 8)}`]
|
|
);
|
|
return { projectId, tableId: table.lastInsertRowid };
|
|
}
|
|
|
|
async function dropTable({ projectId, tableId }) {
|
|
await dbRun(`DELETE FROM table_rows WHERE table_id = $1`, [tableId]);
|
|
await dbRun(`DELETE FROM universal_tables WHERE id = $1`, [tableId]);
|
|
await dbRun(`DELETE FROM projects WHERE id = $1`, [projectId]);
|
|
}
|
|
|
|
async function insertRow(tableId, data) {
|
|
const r = await dbRun(
|
|
`INSERT INTO table_rows (table_id, base_id, data, created_at, updated_at)
|
|
VALUES ($1, $2, $3, NOW(), NOW())`,
|
|
[tableId, generateBaseId(), JSON.stringify(data)]
|
|
);
|
|
return r.lastInsertRowid;
|
|
}
|
|
|
|
describe('booking-constraint helper (ADR-0034 §7)', () => {
|
|
let ctx;
|
|
|
|
beforeEach(async () => {
|
|
ctx = await makeFreshTable();
|
|
});
|
|
|
|
afterEach(async () => {
|
|
if (!ctx) return;
|
|
try { await disableBookingConstraint(ctx.tableId); } catch {}
|
|
await dropTable(ctx);
|
|
ctx = null;
|
|
});
|
|
|
|
test('enableBookingConstraint creates the registry row + GiST constraint', async () => {
|
|
const cfg = await enableBookingConstraint({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
});
|
|
expect(cfg).toEqual({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
constraint_name: `booking_excl_${ctx.tableId}`,
|
|
});
|
|
|
|
const stored = await getBookingConstraint(ctx.tableId);
|
|
expect(stored).toMatchObject({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
constraint_name: `booking_excl_${ctx.tableId}`,
|
|
});
|
|
|
|
const con = await dbGet(
|
|
`SELECT 1 AS hit FROM pg_constraint WHERE conname = $1`,
|
|
[`booking_excl_${ctx.tableId}`]
|
|
);
|
|
expect(con).toBeTruthy();
|
|
});
|
|
|
|
test('CREATE EXTENSION btree_gist is installed and re-enabling is idempotent', async () => {
|
|
await enableBookingConstraint({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
});
|
|
const ext = await dbGet(`SELECT 1 AS hit FROM pg_extension WHERE extname = 'btree_gist'`);
|
|
expect(ext).toBeTruthy();
|
|
|
|
// Calling enable again must not error (CREATE EXTENSION IF NOT EXISTS).
|
|
await expect(enableBookingConstraint({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
})).resolves.toBeTruthy();
|
|
});
|
|
|
|
test('IMMUTABLE wrapper booking_tstzrange_iso exists with correct volatility', async () => {
|
|
await enableBookingConstraint({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
});
|
|
const fn = await dbGet(`
|
|
SELECT provolatile FROM pg_proc
|
|
WHERE proname = 'booking_tstzrange_iso'
|
|
LIMIT 1
|
|
`);
|
|
expect(fn).toBeTruthy();
|
|
expect(fn.provolatile).toBe('i');
|
|
});
|
|
|
|
test('overlapping inserts inside same lane are rejected with 23P01', async () => {
|
|
await enableBookingConstraint({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
});
|
|
const id1 = await insertRow(ctx.tableId, {
|
|
lane_id: 'L1',
|
|
start_at: '2026-05-09T14:00:00Z',
|
|
end_at: '2026-05-09T15:00:00Z',
|
|
});
|
|
expect(id1).toBeGreaterThan(0);
|
|
|
|
let pgErr;
|
|
try {
|
|
await insertRow(ctx.tableId, {
|
|
lane_id: 'L1',
|
|
start_at: '2026-05-09T14:30:00Z',
|
|
end_at: '2026-05-09T15:30:00Z',
|
|
});
|
|
} catch (e) {
|
|
pgErr = e;
|
|
}
|
|
expect(pgErr).toBeTruthy();
|
|
expect(pgErr.code).toBe('23P01');
|
|
expect(pgErr.constraint).toBe(`booking_excl_${ctx.tableId}`);
|
|
});
|
|
|
|
test('non-overlapping inserts in same lane succeed', async () => {
|
|
await enableBookingConstraint({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
});
|
|
await insertRow(ctx.tableId, { lane_id: 'L1', start_at: '2026-05-09T14:00:00Z', end_at: '2026-05-09T15:00:00Z' });
|
|
const id2 = await insertRow(ctx.tableId, { lane_id: 'L1', start_at: '2026-05-09T15:00:00Z', end_at: '2026-05-09T16:00:00Z' });
|
|
expect(id2).toBeGreaterThan(0);
|
|
});
|
|
|
|
test('overlapping inserts in different lanes succeed', async () => {
|
|
await enableBookingConstraint({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
});
|
|
await insertRow(ctx.tableId, { lane_id: 'L1', start_at: '2026-05-09T14:00:00Z', end_at: '2026-05-09T15:00:00Z' });
|
|
const id2 = await insertRow(ctx.tableId, { lane_id: 'L2', start_at: '2026-05-09T14:30:00Z', end_at: '2026-05-09T15:30:00Z' });
|
|
expect(id2).toBeGreaterThan(0);
|
|
});
|
|
|
|
test('constraint is scoped — overlap in another table_id is allowed', async () => {
|
|
await enableBookingConstraint({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
});
|
|
await insertRow(ctx.tableId, { lane_id: 'L1', start_at: '2026-05-09T14:00:00Z', end_at: '2026-05-09T15:00:00Z' });
|
|
|
|
const otherCtx = await makeFreshTable();
|
|
try {
|
|
const id = await insertRow(otherCtx.tableId, { lane_id: 'L1', start_at: '2026-05-09T14:30:00Z', end_at: '2026-05-09T15:30:00Z' });
|
|
expect(id).toBeGreaterThan(0);
|
|
} finally {
|
|
await dropTable(otherCtx);
|
|
}
|
|
});
|
|
|
|
test('disableBookingConstraint drops the GiST constraint and registry row', async () => {
|
|
await enableBookingConstraint({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
});
|
|
await disableBookingConstraint(ctx.tableId);
|
|
|
|
const cfg = await getBookingConstraint(ctx.tableId);
|
|
expect(cfg).toBeNull();
|
|
|
|
const con = await dbGet(
|
|
`SELECT 1 AS hit FROM pg_constraint WHERE conname = $1`,
|
|
[`booking_excl_${ctx.tableId}`]
|
|
);
|
|
expect(con).toBeFalsy();
|
|
|
|
const id = await insertRow(ctx.tableId, { lane_id: 'L1', start_at: '2026-05-09T14:00:00Z', end_at: '2026-05-09T15:00:00Z' });
|
|
const id2 = await insertRow(ctx.tableId, { lane_id: 'L1', start_at: '2026-05-09T14:30:00Z', end_at: '2026-05-09T15:30:00Z' });
|
|
expect(id).toBeGreaterThan(0);
|
|
expect(id2).toBeGreaterThan(0);
|
|
});
|
|
|
|
test('isBookingConflictError narrows by code AND constraint name', () => {
|
|
expect(isBookingConflictError({ code: '23P01', constraint: 'booking_excl_42' })).toBe(true);
|
|
expect(isBookingConflictError({ code: '23P01', constraint: 'unrelated_excl' })).toBe(false);
|
|
expect(isBookingConflictError({ code: '23505', constraint: 'booking_excl_42' })).toBe(false);
|
|
expect(isBookingConflictError({ code: '23P01' })).toBe(false);
|
|
expect(isBookingConflictError(null)).toBe(false);
|
|
expect(isBookingConflictError(undefined)).toBe(false);
|
|
});
|
|
|
|
test('findConflictingRowId returns the existing row when overlap is intended', async () => {
|
|
await enableBookingConstraint({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
});
|
|
const existingId = await insertRow(ctx.tableId, {
|
|
lane_id: 'L1',
|
|
start_at: '2026-05-09T14:00:00Z',
|
|
end_at: '2026-05-09T15:00:00Z',
|
|
});
|
|
const conflictId = await findConflictingRowId({
|
|
table_id: ctx.tableId,
|
|
data: { lane_id: 'L1', start_at: '2026-05-09T14:30:00Z', end_at: '2026-05-09T15:30:00Z' },
|
|
});
|
|
expect(conflictId).toBe(existingId);
|
|
});
|
|
|
|
test('findConflictingRowId honours exclude_row_id (PUT self-skip)', async () => {
|
|
await enableBookingConstraint({
|
|
table_id: ctx.tableId,
|
|
lane_column: 'lane_id',
|
|
start_column: 'start_at',
|
|
end_column: 'end_at',
|
|
});
|
|
const selfId = await insertRow(ctx.tableId, {
|
|
lane_id: 'L1',
|
|
start_at: '2026-05-09T14:00:00Z',
|
|
end_at: '2026-05-09T15:00:00Z',
|
|
});
|
|
const conflict = await findConflictingRowId({
|
|
table_id: ctx.tableId,
|
|
data: { lane_id: 'L1', start_at: '2026-05-09T14:30:00Z', end_at: '2026-05-09T15:30:00Z' },
|
|
exclude_row_id: selfId,
|
|
});
|
|
expect(conflict).toBeNull();
|
|
});
|
|
|
|
test('enableBookingConstraint rejects unsafe identifiers', async () => {
|
|
await expect(enableBookingConstraint({
|
|
table_id: ctx.tableId, lane_column: 'lane; DROP TABLE', start_column: 'start_at', end_column: 'end_at',
|
|
})).rejects.toThrow(/lane_column must match/);
|
|
await expect(enableBookingConstraint({
|
|
table_id: ctx.tableId, lane_column: 'lane_id', start_column: '', end_column: 'end_at',
|
|
})).rejects.toThrow(/start_column must match/);
|
|
await expect(enableBookingConstraint({
|
|
table_id: 'oops', lane_column: 'lane_id', start_column: 'start_at', end_column: 'end_at',
|
|
})).rejects.toThrow(/table_id must be a positive integer/);
|
|
});
|
|
|
|
test('enableBookingConstraint is idempotent (re-enable replaces)', async () => {
|
|
await enableBookingConstraint({
|
|
table_id: ctx.tableId, lane_column: 'lane_id', start_column: 'start_at', end_column: 'end_at',
|
|
});
|
|
const cfg2 = await enableBookingConstraint({
|
|
table_id: ctx.tableId, lane_column: 'lane_id', start_column: 'start_at', end_column: 'end_at',
|
|
});
|
|
expect(cfg2.constraint_name).toBe(`booking_excl_${ctx.tableId}`);
|
|
|
|
const dupes = await dbAll(
|
|
`SELECT COUNT(*)::int AS n FROM booking_constraints WHERE table_id = $1`,
|
|
[ctx.tableId]
|
|
);
|
|
expect(dupes[0].n).toBe(1);
|
|
});
|
|
});
|