f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
223 lines
8 KiB
JavaScript
223 lines
8 KiB
JavaScript
// ADR-0034 §7 — DB exclusion constraint for booking widgets.
|
|
//
|
|
// Postgres GiST EXCLUDE on (lane = AND tstzrange(start,end) &&), scoped per
|
|
// universal_tables.table_id via WHERE clause. Single CRM-wide btree_gist
|
|
// extension + one IMMUTABLE wrapper (booking_tstzrange_iso). Constraint
|
|
// definitions are tracked in `booking_constraints` registry table (lazily
|
|
// created on first enable).
|
|
//
|
|
// Identifier safety: column names are validated against a strict regex before
|
|
// being interpolated into DDL. Numeric inputs go through Number() coercion.
|
|
// Range payloads are user-supplied via JSONB (data->>col) — the helper never
|
|
// interpolates row data into SQL.
|
|
//
|
|
// IMMUTABLE wrapper note: text::timestamptz is STABLE in Postgres because it
|
|
// honours session TimeZone. We label the wrapper IMMUTABLE because callers
|
|
// MUST pass ISO-8601 strings carrying an explicit offset (Z or ±HH:MM); for
|
|
// such inputs the cast result is deterministic. This lie is required —
|
|
// EXCLUDE/GiST expressions reject non-IMMUTABLE functions.
|
|
|
|
import { dbAll, dbGet, dbRun } from '../database/connection.js';
|
|
|
|
const SAFE_IDENT = /^[a-zA-Z_][a-zA-Z0-9_]{0,62}$/;
|
|
const REGISTRY_TABLE = 'booking_constraints';
|
|
const WRAPPER_FN = 'booking_tstzrange_iso';
|
|
const TABLE_ROWS = 'table_rows';
|
|
|
|
function constraintNameFor(tableId) {
|
|
return `booking_excl_${Number(tableId)}`;
|
|
}
|
|
|
|
function assertTableId(tableId) {
|
|
const n = Number(tableId);
|
|
if (!Number.isInteger(n) || n <= 0) {
|
|
throw new Error('booking-constraint: table_id must be a positive integer');
|
|
}
|
|
return n;
|
|
}
|
|
|
|
function assertSafeColumn(label, value) {
|
|
if (typeof value !== 'string' || !SAFE_IDENT.test(value)) {
|
|
throw new Error(`booking-constraint: ${label} must match /^[a-zA-Z_][a-zA-Z0-9_]{0,62}$/`);
|
|
}
|
|
return value;
|
|
}
|
|
|
|
async function ensureExtension() {
|
|
await dbRun(`CREATE EXTENSION IF NOT EXISTS btree_gist`);
|
|
}
|
|
|
|
async function ensureWrapper() {
|
|
// Skip CREATE OR REPLACE when the wrapper is already there — avoids
|
|
// "tuple concurrently updated" when parallel callers (vitest concurrency,
|
|
// overlapping HTTP requests) race on pg_proc. CREATE OR REPLACE FUNCTION
|
|
// is NOT internally serialised the way CREATE EXTENSION IF NOT EXISTS is.
|
|
const existing = await dbGet(
|
|
`SELECT 1 AS hit FROM pg_proc WHERE proname = ? LIMIT 1`,
|
|
[WRAPPER_FN]
|
|
);
|
|
if (existing) return;
|
|
try {
|
|
await dbRun(`
|
|
CREATE OR REPLACE FUNCTION ${WRAPPER_FN}(s text, e text)
|
|
RETURNS tstzrange
|
|
LANGUAGE sql
|
|
IMMUTABLE
|
|
PARALLEL SAFE
|
|
AS $$ SELECT tstzrange(s::timestamptz, e::timestamptz, '[)') $$
|
|
`);
|
|
} catch (err) {
|
|
// Concurrent creation: another session won the race. If the wrapper now
|
|
// exists, treat as success — definitions are identical.
|
|
const recheck = await dbGet(
|
|
`SELECT 1 AS hit FROM pg_proc WHERE proname = ? LIMIT 1`,
|
|
[WRAPPER_FN]
|
|
);
|
|
if (recheck) return;
|
|
throw err;
|
|
}
|
|
}
|
|
|
|
async function ensureRegistryTable() {
|
|
await dbRun(`
|
|
CREATE TABLE IF NOT EXISTS ${REGISTRY_TABLE} (
|
|
id SERIAL PRIMARY KEY,
|
|
table_id INTEGER NOT NULL UNIQUE,
|
|
lane_column TEXT NOT NULL,
|
|
start_column TEXT NOT NULL,
|
|
end_column TEXT NOT NULL,
|
|
constraint_name TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
)
|
|
`);
|
|
}
|
|
|
|
export async function enableBookingConstraint({ table_id, lane_column, start_column, end_column }) {
|
|
const tid = assertTableId(table_id);
|
|
const lane = assertSafeColumn('lane_column', lane_column);
|
|
const start = assertSafeColumn('start_column', start_column);
|
|
const end = assertSafeColumn('end_column', end_column);
|
|
|
|
await ensureExtension();
|
|
await ensureWrapper();
|
|
await ensureRegistryTable();
|
|
|
|
const cname = constraintNameFor(tid);
|
|
|
|
// Idempotent: drop prior incarnation, then re-add. ALTER validates existing
|
|
// rows against the new EXCLUDE predicate; if ANY pair already overlaps,
|
|
// Postgres raises 23P01 from the ALTER itself. Surface that as a
|
|
// structured `BOOKING_CONSTRAINT_EXISTING_OVERLAP` error so callers can
|
|
// return 400 with a clear message instead of a 500.
|
|
await dbRun(`ALTER TABLE ${TABLE_ROWS} DROP CONSTRAINT IF EXISTS "${cname}"`);
|
|
// NOTE: We deliberately use `(data->>'col') IS NOT NULL` instead of the JSONB
|
|
// existence operator `data ? 'col'`. connection.js's convertPlaceholders does
|
|
// a global `?` → `$N` rewrite and would corrupt the JSONB `?` operator.
|
|
try {
|
|
await dbRun(`
|
|
ALTER TABLE ${TABLE_ROWS} ADD CONSTRAINT "${cname}"
|
|
EXCLUDE USING GIST (
|
|
((data->>'${lane}')) WITH =,
|
|
${WRAPPER_FN}(data->>'${start}', data->>'${end}') WITH &&
|
|
)
|
|
WHERE (
|
|
table_id = ${tid}
|
|
AND (data->>'${lane}') IS NOT NULL
|
|
AND (data->>'${start}') IS NOT NULL
|
|
AND (data->>'${end}') IS NOT NULL
|
|
)
|
|
`);
|
|
} catch (alterErr) {
|
|
if (alterErr && alterErr.code === '23P01') {
|
|
const wrapped = new Error(
|
|
`Cannot enable booking constraint on table ${tid}: existing rows already overlap`
|
|
);
|
|
wrapped.code = 'BOOKING_CONSTRAINT_EXISTING_OVERLAP';
|
|
wrapped.cause = alterErr;
|
|
throw wrapped;
|
|
}
|
|
throw alterErr;
|
|
}
|
|
|
|
await dbRun(`
|
|
INSERT INTO ${REGISTRY_TABLE} (table_id, lane_column, start_column, end_column, constraint_name, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, NOW(), NOW())
|
|
ON CONFLICT (table_id) DO UPDATE SET
|
|
lane_column = EXCLUDED.lane_column,
|
|
start_column = EXCLUDED.start_column,
|
|
end_column = EXCLUDED.end_column,
|
|
constraint_name = EXCLUDED.constraint_name,
|
|
updated_at = NOW()
|
|
`, [tid, lane, start, end, cname]);
|
|
|
|
return {
|
|
table_id: tid,
|
|
lane_column: lane,
|
|
start_column: start,
|
|
end_column: end,
|
|
constraint_name: cname,
|
|
};
|
|
}
|
|
|
|
export async function disableBookingConstraint(table_id) {
|
|
const tid = assertTableId(table_id);
|
|
await ensureRegistryTable();
|
|
const cname = constraintNameFor(tid);
|
|
await dbRun(`ALTER TABLE ${TABLE_ROWS} DROP CONSTRAINT IF EXISTS "${cname}"`);
|
|
await dbRun(`DELETE FROM ${REGISTRY_TABLE} WHERE table_id = ?`, [tid]);
|
|
}
|
|
|
|
export async function getBookingConstraint(table_id) {
|
|
const tid = assertTableId(table_id);
|
|
// Don't auto-create the registry on read — return null when no helper has
|
|
// ever enabled a constraint on this DB.
|
|
const exists = await dbGet(`
|
|
SELECT 1 AS hit FROM information_schema.tables
|
|
WHERE table_schema = current_schema() AND table_name = ?
|
|
`, [REGISTRY_TABLE]);
|
|
if (!exists) return null;
|
|
const row = await dbGet(`
|
|
SELECT table_id, lane_column, start_column, end_column, constraint_name
|
|
FROM ${REGISTRY_TABLE} WHERE table_id = ?
|
|
`, [tid]);
|
|
return row || null;
|
|
}
|
|
|
|
export function isBookingConflictError(err) {
|
|
if (!err || err.code !== '23P01') return false;
|
|
const cname = err.constraint || '';
|
|
return /^booking_excl_\d+$/.test(cname);
|
|
}
|
|
|
|
export async function findConflictingRowId({ table_id, data, exclude_row_id = null }) {
|
|
const tid = assertTableId(table_id);
|
|
const cfg = await getBookingConstraint(tid);
|
|
if (!cfg) return null;
|
|
|
|
const laneVal = data?.[cfg.lane_column];
|
|
const startVal = data?.[cfg.start_column];
|
|
const endVal = data?.[cfg.end_column];
|
|
if (laneVal == null || !startVal || !endVal) return null;
|
|
|
|
// Same `?`-operator avoidance as enableBookingConstraint — the placeholder
|
|
// converter would otherwise mangle `data ? 'col'`.
|
|
const params = [tid, String(laneVal), String(startVal), String(endVal)];
|
|
let sql = `
|
|
SELECT id FROM ${TABLE_ROWS}
|
|
WHERE table_id = ?
|
|
AND (data->>'${cfg.lane_column}') IS NOT NULL
|
|
AND (data->>'${cfg.start_column}') IS NOT NULL
|
|
AND (data->>'${cfg.end_column}') IS NOT NULL
|
|
AND (data->>'${cfg.lane_column}') = ?
|
|
AND ${WRAPPER_FN}(data->>'${cfg.start_column}', data->>'${cfg.end_column}')
|
|
&& tstzrange((?)::timestamptz, (?)::timestamptz, '[)')
|
|
`;
|
|
if (exclude_row_id != null) {
|
|
sql += ` AND id <> ?`;
|
|
params.push(Number(exclude_row_id));
|
|
}
|
|
sql += ` ORDER BY id ASC LIMIT 1`;
|
|
const row = await dbGet(sql, params);
|
|
return row ? Number(row.id) : null;
|
|
}
|