schema/effective-date-overlap-guards.integration.test.ts
Rama main · réplica de lectura del repositorio original.
import assert from "node:assert/strict";
import { randomUUID } from "node:crypto";
import test from "node:test";
import { sql } from "drizzle-orm";
import type { PoolClient, QueryResult } from "pg";
import { db, pool } from "@openbooks/engine/platform/db";
import { createScratchOrg, dropScratchOrgReporting } from "@openbooks/engine/testing/fixtures";
const DB = Boolean(process.env.OPENBOOKS_DB_URL);
/**
* Regression coverage for the nine effective-date overlap guards that were
* BEFORE-trigger SELECT EXISTS checks (fair_value_prices, field_ticket_
* policies, item_rate_book_assignments, item_rate_versions, labor_cost_rates,
* overhead_rates, subsidiary_ownership_interests, tax_registrations, and
* project_financial_profile_versions). Under READ COMMITTED those triggers
* could not see a concurrent writer's uncommitted row, so two overlapping
* authoritative rows could both commit. Migration 0051 replaces them with
* GiST exclusion constraints, which arbitrate the race in the index.
*
* Every guard gets a controlled two-session proof: the second writer must
* WAIT on the storage guard while the first transaction is open (the exact
* serialization the old triggers never provided) and be rejected once the
* first commits — for both a create/create and an update/create race — plus
* an adjacent non-overlapping two-session commit that must succeed.
*/
interface Statement {
text: string;
params?: unknown[];
}
type StatementResult = PromiseSettledResult<QueryResult>;
interface Session {
client: PoolClient;
pid: number;
}
async function openSession(): Promise<Session> {
const client = await pool.connect();
try {
await client.query("begin");
await client.query("select set_config('app.bypass_rls', 'on', true)");
const backend = await client.query<{ pid: number }>("select pg_backend_pid() as pid");
return { client, pid: Number(backend.rows[0]!.pid) };
} catch (error) {
client.release(error as Error);
throw error;
}
}
const settle = (promise: Promise<QueryResult>): Promise<StatementResult> =>
promise.then(
(value): StatementResult => ({ status: "fulfilled", value }),
(reason): StatementResult => ({ status: "rejected", reason }),
);
/**
* Run the first statement inside an open transaction, then start the second
* in a second session. The second writer must block on the first (the
* exclusion constraint waits on the conflicting uncommitted tuple), and the
* first transaction commits so the loser's outcome is decided. The loser's
* transaction is always rolled back: a rejected write has nothing to keep,
* and keeping a would-be winner would pollute the next scenario.
*/
async function raceConflict(
first: Statement,
second: Statement,
firstPrep: Statement[] = [],
): Promise<{ blocked: boolean; second: StatementResult }> {
const sessionA = await openSession();
const sessionB = await openSession();
let openA = true;
let openB = true;
try {
for (const prep of firstPrep) {
await sessionA.client.query(prep.text, prep.params ?? []);
}
await sessionA.client.query(first.text, first.params ?? []);
let settled: StatementResult | undefined;
const secondPromise = settle(sessionB.client.query(second.text, second.params ?? []));
void secondPromise.then((value) => {
settled = value;
});
let blocked = false;
for (let attempt = 0; attempt < 500 && settled === undefined && !blocked; attempt += 1) {
const state = await pool.query<{ blocked: boolean }>(
"select $1::int = any(pg_blocking_pids($2::int)) as blocked",
[sessionA.pid, sessionB.pid],
);
if (state.rows[0]?.blocked) blocked = true;
else await new Promise((resolve) => setTimeout(resolve, 10));
}
if (!blocked && settled === undefined) {
throw new Error(
`conflict race never reached a decision (blocker ${sessionA.pid}, waiter ${sessionB.pid})`,
);
}
await sessionA.client.query("commit");
openA = false;
const result = settled ?? (await secondPromise);
await sessionB.client.query("rollback").catch(() => undefined);
openB = false;
return { blocked, second: result };
} finally {
if (openA) await sessionA.client.query("rollback").catch(() => undefined);
if (openB) await sessionB.client.query("rollback").catch(() => undefined);
sessionA.client.release();
sessionB.client.release();
}
}
/** Two sessions writing adjacent, non-overlapping windows: both commit. */
async function adjacentBothCommit(first: Statement, second: Statement): Promise<void> {
const sessionA = await openSession();
const sessionB = await openSession();
let openA = true;
let openB = true;
try {
await sessionA.client.query(first.text, first.params ?? []);
await sessionB.client.query(second.text, second.params ?? []);
await sessionA.client.query("commit");
openA = false;
await sessionB.client.query("commit");
openB = false;
} finally {
if (openA) await sessionA.client.query("rollback").catch(() => undefined);
if (openB) await sessionB.client.query("rollback").catch(() => undefined);
sessionA.client.release();
sessionB.client.release();
}
}
function assertConflictRejected(
scenario: string,
outcome: { blocked: boolean; second: StatementResult },
): void {
assert.equal(
outcome.blocked,
true,
`${scenario}: the conflicting write must wait on the storage guard`,
);
assert.equal(
outcome.second.status,
"rejected",
`${scenario}: the conflicting write must be rejected once the blocker commits`,
);
if (outcome.second.status === "rejected") {
assert.match(
String(outcome.second.reason),
/exclusion constraint/,
`${scenario}: rejection must come from an exclusion constraint`,
);
}
}
/** Drizzle wraps driver errors, so the PostgreSQL message lives on `cause`. */
function pgMessage(error: unknown): string {
const cause = (error as { cause?: { message?: string } })?.cause;
return String(cause?.message ?? error);
}
async function assertRejectsWith(
query: Promise<unknown>,
pattern: RegExp,
scenario: string,
): Promise<void> {
try {
await query;
} catch (error) {
assert.match(pgMessage(error), pattern, scenario);
return;
}
assert.fail(`${scenario}: expected a rejection matching ${pattern}`);
}
test("fair-value price windows exclude concurrent overlaps at storage", { skip: !DB }, async () => {
const org = await createScratchOrg();
try {
const base = await db.execute<{ id: string }>(sql`
insert into fair_value_prices (org_id, item_id, currency, unit_price, effective_from, effective_to)
values (${org.orgId}, ${org.items.fifo}, 'CAD', '100.0000', '2026-01-01', '2026-06-30')
returning id`);
const baseId = String(base.rows[0]!.id);
const priceInsert = (from: string, to: string) => ({
text: `insert into fair_value_prices (org_id, item_id, currency, unit_price, effective_from, effective_to)
values ($1, $2, 'CAD', '110.0000', $3, $4)`,
params: [org.orgId, org.items.fifo, from, to] as unknown[],
});
const updateCreate = await raceConflict(
{
text: "update fair_value_prices set effective_to = $1 where id = $2",
params: ["2026-09-30", baseId],
},
priceInsert("2026-08-01", "2026-10-31"),
);
assertConflictRejected("fair_value_prices update/create", updateCreate);
const createCreate = await raceConflict(
priceInsert("2026-10-01", "2026-11-30"),
priceInsert("2026-11-15", "2026-12-31"),
);
assertConflictRejected("fair_value_prices create/create", createCreate);
await adjacentBothCommit(
priceInsert("2027-01-01", "2027-02-28"),
priceInsert("2027-03-01", "2027-04-30"),
);
// Pricing identity includes the currency: another currency never competes.
await db.execute(sql`
insert into fair_value_prices (org_id, item_id, currency, unit_price, effective_from, effective_to)
values (${org.orgId}, ${org.items.fifo}, 'USD', '90.0000', '2026-02-01', '2026-03-31')`);
// A NULL effective_from means -infinity and therefore overlaps every
// other active window for the same item and currency.
await assertRejectsWith(
db.execute(sql`
insert into fair_value_prices (org_id, item_id, currency, unit_price, effective_to)
values (${org.orgId}, ${org.items.fifo}, 'CAD', '160.0000', '2027-06-30')`),
/exclusion constraint/,
"fair_value_prices NULL effective_from must overlap every window",
);
} finally {
await dropScratchOrgReporting(org.orgId);
}
});
test(
"field ticket policy windows exclude concurrent overlaps at storage",
{ skip: !DB },
async () => {
const org = await createScratchOrg();
const otherOrg = await createScratchOrg();
try {
const base = await db.execute<{ id: string }>(sql`
insert into field_ticket_policies (org_id, scope, period, effective_from, effective_to)
values (${org.orgId}, 'organization', 'daily', '2026-01-01', '2026-06-30')
returning id`);
const baseId = String(base.rows[0]!.id);
const policyInsert = (from: string, to: string) => ({
text: `insert into field_ticket_policies (org_id, scope, period, effective_from, effective_to)
values ($1, 'organization', 'daily', $2, $3)`,
params: [org.orgId, from, to] as unknown[],
});
const updateCreate = await raceConflict(
{
text: "update field_ticket_policies set effective_to = $1 where id = $2",
params: ["2026-09-30", baseId],
},
policyInsert("2026-08-01", "2026-10-31"),
);
assertConflictRejected("field_ticket_policies update/create", updateCreate);
const createCreate = await raceConflict(
policyInsert("2026-10-01", "2026-11-30"),
policyInsert("2026-11-15", "2026-12-31"),
);
assertConflictRejected("field_ticket_policies create/create", createCreate);
await adjacentBothCommit(
policyInsert("2027-01-01", "2027-02-28"),
policyInsert("2027-03-01", "2027-04-30"),
);
// Policy identity includes the scope: an overlapping customer-scope
// window is a different resolution lane and commits.
await db.execute(sql`
insert into field_ticket_policies (org_id, scope, customer_party_id, period, effective_from, effective_to)
values (${org.orgId}, 'customer', ${org.customerId}, 'daily', '2026-02-01', '2026-03-31')`);
// The replaced trigger's tenant-integrity duty survives: a customer
// policy may not point at another organization's party.
const foreignParty = randomUUID();
await db.execute(sql`
insert into parties (id, org_id, kind, display_name)
values (${foreignParty}, ${otherOrg.orgId}, 'customer', 'Foreign customer')`);
await assertRejectsWith(
db.execute(sql`
insert into field_ticket_policies (org_id, scope, customer_party_id, period, effective_from)
values (${org.orgId}, 'customer', ${foreignParty}, 'daily', '2026-02-01')`),
/field ticket customer policy must belong to the same organization/,
"field_ticket_policies tenant integrity must survive the guard replacement",
);
} finally {
await dropScratchOrgReporting(org.orgId);
await dropScratchOrgReporting(otherOrg.orgId);
}
},
);
test(
"rate-book assignment windows exclude concurrent overlaps at storage",
{ skip: !DB },
async () => {
const org = await createScratchOrg();
try {
const bookId = randomUUID();
await db.execute(sql`
insert into item_rate_books (id, org_id, code, name, currency, is_default, is_active)
values (${bookId}, ${org.orgId}, ${"RB-" + randomUUID().slice(0, 8)}, 'Overlap book', 'CAD', false, true)`);
const base = await db.execute<{ id: string }>(sql`
insert into item_rate_book_assignments (org_id, rate_book_id, effective_from, effective_to)
values (${org.orgId}, ${bookId}, '2026-01-01', '2026-06-30')
returning id`);
const baseId = String(base.rows[0]!.id);
const assignmentInsert = (from: string, to: string) => ({
text: `insert into item_rate_book_assignments (org_id, rate_book_id, effective_from, effective_to)
values ($1, $2, $3, $4)`,
params: [org.orgId, bookId, from, to] as unknown[],
});
const updateCreate = await raceConflict(
{
text: "update item_rate_book_assignments set effective_to = $1 where id = $2",
params: ["2026-09-30", baseId],
},
assignmentInsert("2026-08-01", "2026-10-31"),
);
assertConflictRejected("item_rate_book_assignments update/create", updateCreate);
const createCreate = await raceConflict(
assignmentInsert("2026-10-01", "2026-11-30"),
assignmentInsert("2026-11-15", "2026-12-31"),
);
assertConflictRejected("item_rate_book_assignments create/create", createCreate);
await adjacentBothCommit(
assignmentInsert("2027-01-01", "2027-02-28"),
assignmentInsert("2027-03-01", "2027-04-30"),
);
// Assignment identity includes the rate book: another book may hold an
// overlapping window for the same organization-wide scope.
const otherBookId = randomUUID();
await db.execute(sql`
insert into item_rate_books (id, org_id, code, name, currency, is_default, is_active)
values (${otherBookId}, ${org.orgId}, ${"RB-" + randomUUID().slice(0, 8)}, 'Second book', 'CAD', false, true)`);
await db.execute(sql`
insert into item_rate_book_assignments (org_id, rate_book_id, effective_from, effective_to)
values (${org.orgId}, ${otherBookId}, '2026-02-01', '2026-03-31')`);
} finally {
await dropScratchOrgReporting(org.orgId);
}
},
);
test(
"active item rate version windows exclude concurrent overlaps at storage",
{ skip: !DB },
async () => {
const org = await createScratchOrg();
try {
const bookId = randomUUID();
await db.execute(sql`
insert into item_rate_books (id, org_id, code, name, currency, is_default, is_active)
values (${bookId}, ${org.orgId}, ${"RB-" + randomUUID().slice(0, 8)}, 'Overlap book', 'CAD', false, true)`);
// A committed active window plus a draft gap-filler: the update/create
// race activates the draft (drafts are freely mutable — the immutability
// guard only pins activated versions), which is exactly the concurrent
// transition the old trigger could not see.
await db.execute(sql`
insert into item_rate_versions (org_id, rate_book_id, effective_from, effective_to, status)
values (${org.orgId}, ${bookId}, '2026-10-01', '2026-11-30', 'active')`);
const draftGap = await db.execute<{ id: string }>(sql`
insert into item_rate_versions (org_id, rate_book_id, effective_from, effective_to, status)
values (${org.orgId}, ${bookId}, '2026-01-01', '2026-06-30', 'draft')
returning id`);
const versionInsert = (from: string, to: string) => ({
text: `insert into item_rate_versions (org_id, rate_book_id, effective_from, effective_to, status)
values ($1, $2, $3, $4, 'active')`,
params: [org.orgId, bookId, from, to] as unknown[],
});
const updateCreate = await raceConflict(
{
text: "update item_rate_versions set status = 'active' where id = $1",
params: [String(draftGap.rows[0]!.id)],
},
versionInsert("2026-02-01", "2026-03-31"),
);
assertConflictRejected("item_rate_versions update/create", updateCreate);
const createCreate = await raceConflict(
versionInsert("2026-12-01", "2027-01-31"),
versionInsert("2027-01-15", "2027-02-28"),
);
assertConflictRejected("item_rate_versions create/create", createCreate);
await adjacentBothCommit(
versionInsert("2027-03-01", "2027-04-30"),
versionInsert("2027-05-01", "2027-06-30"),
);
// Only active versions resolve: a draft may overlap an active window,
// and the constraint re-checks the moment that draft is activated.
const draft = await db.execute<{ id: string }>(sql`
insert into item_rate_versions (org_id, rate_book_id, effective_from, effective_to, status)
values (${org.orgId}, ${bookId}, '2026-05-01', '2026-05-31', 'draft')
returning id`);
await assertRejectsWith(
db.execute(
sql`update item_rate_versions set status = 'active' where id = ${draft.rows[0]!.id}`,
),
/exclusion constraint/,
"item_rate_versions draft activation must re-check the exclusion",
);
} finally {
await dropScratchOrgReporting(org.orgId);
}
},
);
test("labor cost rate windows exclude concurrent overlaps at storage", { skip: !DB }, async () => {
const org = await createScratchOrg();
try {
const base = await db.execute<{ id: string }>(sql`
insert into labor_cost_rates (org_id, currency, rate, basis, effective_from, effective_to)
values (${org.orgId}, 'CAD', '50.0000', 'hour', '2026-01-01', '2026-06-30')
returning id`);
const baseId = String(base.rows[0]!.id);
const rateInsert = (from: string, to: string) => ({
text: `insert into labor_cost_rates (org_id, currency, rate, basis, effective_from, effective_to)
values ($1, 'CAD', '55.0000', 'hour', $2, $3)`,
params: [org.orgId, from, to] as unknown[],
});
const updateCreate = await raceConflict(
{
text: "update labor_cost_rates set effective_to = $1 where id = $2",
params: ["2026-09-30", baseId],
},
rateInsert("2026-08-01", "2026-10-31"),
);
assertConflictRejected("labor_cost_rates update/create", updateCreate);
const createCreate = await raceConflict(
rateInsert("2026-10-01", "2026-11-30"),
rateInsert("2026-11-15", "2026-12-31"),
);
assertConflictRejected("labor_cost_rates create/create", createCreate);
await adjacentBothCommit(
rateInsert("2027-01-01", "2027-02-28"),
rateInsert("2027-03-01", "2027-04-30"),
);
// Wage identity is the full scope: a job-title rate may overlap the
// organization-default window because it resolves independently.
await db.execute(sql`
insert into labor_cost_rates (org_id, job_title, currency, rate, basis, effective_from, effective_to)
values (${org.orgId}, 'Electrician', 'CAD', '60.0000', 'hour', '2026-02-01', '2026-03-31')`);
} finally {
await dropScratchOrgReporting(org.orgId);
}
});
test("overhead rate windows exclude concurrent overlaps at storage", { skip: !DB }, async () => {
const org = await createScratchOrg();
try {
const base = await db.execute<{ id: string }>(sql`
insert into overhead_rates (org_id, category, method, rate_kind, rate_percent, effective_from, effective_to)
values (${org.orgId}, 'Published', 'standard', 'per_hour', '10.0000', '2026-01-01', '2026-06-30')
returning id`);
const baseId = String(base.rows[0]!.id);
const overheadInsert = (from: string, to: string) => ({
text: `insert into overhead_rates (org_id, category, method, rate_kind, rate_percent, effective_from, effective_to)
values ($1, 'Published', 'standard', 'per_hour', '11.0000', $2, $3)`,
params: [org.orgId, from, to] as unknown[],
});
const updateCreate = await raceConflict(
{
text: "update overhead_rates set effective_to = $1 where id = $2",
params: ["2026-09-30", baseId],
},
overheadInsert("2026-08-01", "2026-10-31"),
);
assertConflictRejected("overhead_rates update/create", updateCreate);
const createCreate = await raceConflict(
overheadInsert("2026-10-01", "2026-11-30"),
overheadInsert("2026-11-15", "2026-12-31"),
);
assertConflictRejected("overhead_rates create/create", createCreate);
await adjacentBothCommit(
overheadInsert("2027-01-01", "2027-02-28"),
overheadInsert("2027-03-01", "2027-04-30"),
);
// Rate identity includes the method: a live-method row never competes
// with the published standard card.
await db.execute(sql`
insert into overhead_rates (org_id, category, method, rate_kind, rate_percent, effective_from, effective_to)
values (${org.orgId}, 'Published', 'live', 'per_hour', '12.0000', '2026-02-01', '2026-03-31')`);
} finally {
await dropScratchOrgReporting(org.orgId);
}
});
test(
"subsidiary ownership policy windows exclude concurrent overlaps at storage",
{ skip: !DB },
async () => {
const org = await createScratchOrg();
try {
const childId = randomUUID();
await db.execute(sql`
insert into subsidiaries (id, org_id, parent_id, name, base_currency, country, tax_ids, is_elimination, is_active, custom)
values (${childId}, ${org.orgId}, ${org.subsidiaryId}, 'Owned Co', 'CAD', 'CA', '{}'::jsonb, false, true, '{}'::jsonb)`);
const base = await db.execute<{ id: string }>(sql`
insert into subsidiary_ownership_interests
(org_id, parent_subsidiary_id, subsidiary_id, effective_from, effective_to,
ownership_percent, method, acquisition_date,
investment_account_id, equity_income_account_id)
values (${org.orgId}, ${org.subsidiaryId}, ${childId}, '2026-01-01', '2026-06-30',
'100', 'equity', '2025-12-31', ${org.accounts.bank}, ${org.accounts.revenue})
returning id`);
const baseId = String(base.rows[0]!.id);
const interestInsert = (from: string, to: string) => ({
text: `insert into subsidiary_ownership_interests
(org_id, parent_subsidiary_id, subsidiary_id, effective_from, effective_to,
ownership_percent, method, acquisition_date,
investment_account_id, equity_income_account_id)
values ($1, $2, $3, $4, $5, '100', 'equity', '2025-12-31', $6, $7)`,
params: [
org.orgId,
org.subsidiaryId,
childId,
from,
to,
org.accounts.bank,
org.accounts.revenue,
] as unknown[],
});
const updateCreate = await raceConflict(
{
text: "update subsidiary_ownership_interests set effective_to = $1 where id = $2",
params: ["2026-09-30", baseId],
},
interestInsert("2026-08-01", "2026-10-31"),
);
assertConflictRejected("subsidiary_ownership_interests update/create", updateCreate);
const createCreate = await raceConflict(
interestInsert("2026-10-01", "2026-11-30"),
interestInsert("2026-11-15", "2026-12-31"),
);
assertConflictRejected("subsidiary_ownership_interests create/create", createCreate);
await adjacentBothCommit(
interestInsert("2027-01-01", "2027-02-28"),
interestInsert("2027-03-01", "2027-04-30"),
);
// The replaced trigger's hierarchy duty survives: ownership must follow
// the active tenant consolidation hierarchy.
await assertRejectsWith(
db.execute(sql`
insert into subsidiary_ownership_interests
(org_id, parent_subsidiary_id, subsidiary_id, effective_from,
ownership_percent, method, acquisition_date,
investment_account_id, equity_income_account_id)
values (${org.orgId}, ${childId}, ${childId}, '2027-05-01',
'100', 'equity', '2025-12-31', ${org.accounts.bank}, ${org.accounts.revenue})`),
/ownership interest must follow the active tenant consolidation hierarchy/,
"subsidiary_ownership_interests hierarchy duty must survive the guard replacement",
);
// The replaced trigger's used-policy immutability duty survives: once a
// consolidation run references the policy it can no longer change.
const runId = randomUUID();
await db.execute(sql`
insert into ownership_consolidation_runs (id, org_id, period_id, status)
values (${runId}, ${org.orgId}, ${org.periodId}, 'posted')`);
const entryId = randomUUID();
await db.execute(sql`
insert into journal_entries
(id, org_id, book_id, subsidiary_id, entry_number, posting_date, period_id, memo, status, origin)
values (${entryId}, ${org.orgId}, ${org.bookId}, ${org.subsidiaryId},
${"OBI-" + randomUUID().slice(0, 8)}, ${org.date}, ${org.periodId}, 'used-policy proof', 'draft', 'manual')`);
await db.execute(sql`
insert into ownership_consolidation_entries (org_id, run_id, interest_id, kind, journal_entry_id)
values (${org.orgId}, ${runId}, ${baseId}, 'acquisition', ${entryId})`);
await assertRejectsWith(
db.execute(
sql`update subsidiary_ownership_interests set ownership_percent = '90' where id = ${baseId}`,
),
/used ownership policy is immutable/,
"subsidiary_ownership_interests used-policy immutability must survive the guard replacement",
);
// A used policy still cannot be shadowed by an overlapping successor.
await assertRejectsWith(
db.execute(sql`
insert into subsidiary_ownership_interests
(org_id, parent_subsidiary_id, subsidiary_id, effective_from, effective_to,
ownership_percent, method, acquisition_date,
investment_account_id, equity_income_account_id)
values (${org.orgId}, ${org.subsidiaryId}, ${childId}, '2027-02-01', '2027-02-15',
'100', 'equity', '2025-12-31', ${org.accounts.bank}, ${org.accounts.revenue})`),
/exclusion constraint/,
"subsidiary_ownership_interests used policy must still exclude overlapping successors",
);
} finally {
await dropScratchOrgReporting(org.orgId);
}
},
);
test("tax registration windows exclude concurrent overlaps at storage", { skip: !DB }, async () => {
const org = await createScratchOrg();
try {
const jurisdictionId = randomUUID();
await db.execute(sql`
insert into tax_jurisdictions (id, org_id, code, name, country, level, tax_type)
values (${jurisdictionId}, ${org.orgId}, ${"OV-" + randomUUID().slice(0, 8)}, 'Overlap Province', 'CA', 'country', 'gst')`);
const base = await db.execute<{ id: string }>(sql`
insert into tax_registrations (org_id, jurisdiction_id, effective_from, effective_to)
values (${org.orgId}, ${jurisdictionId}, '2026-01-01', '2026-06-30')
returning id`);
const baseId = String(base.rows[0]!.id);
const registrationInsert = (from: string, to: string) => ({
text: `insert into tax_registrations (org_id, jurisdiction_id, effective_from, effective_to)
values ($1, $2, $3, $4)`,
params: [org.orgId, jurisdictionId, from, to] as unknown[],
});
const updateCreate = await raceConflict(
{
text: "update tax_registrations set effective_to = $1 where id = $2",
params: ["2026-09-30", baseId],
},
registrationInsert("2026-08-01", "2026-10-31"),
);
assertConflictRejected("tax_registrations update/create", updateCreate);
const createCreate = await raceConflict(
registrationInsert("2026-10-01", "2026-11-30"),
registrationInsert("2026-11-15", "2026-12-31"),
);
assertConflictRejected("tax_registrations create/create", createCreate);
await adjacentBothCommit(
registrationInsert("2027-01-01", "2027-02-28"),
registrationInsert("2027-03-01", "2027-04-30"),
);
// Registration identity includes the return form: another form's window
// resolves independently and may overlap.
await db.execute(sql`
insert into tax_registrations (org_id, jurisdiction_id, return_form_code, effective_from, effective_to)
values (${org.orgId}, ${jurisdictionId}, 'CA-GST', '2026-02-01', '2026-03-31')`);
// A NULL effective_from means -infinity and therefore overlaps every
// other active registration for the same jurisdiction and return form.
await assertRejectsWith(
db.execute(sql`
insert into tax_registrations (org_id, jurisdiction_id, effective_to)
values (${org.orgId}, ${jurisdictionId}, '2027-06-30')`),
/exclusion constraint|tax registrations overlap for this legal entity, jurisdiction and return form/,
"tax_registrations NULL effective_from must overlap every window",
);
} finally {
await dropScratchOrgReporting(org.orgId);
}
});
test(
"project financial profile windows exclude concurrent overlaps at storage",
{ skip: !DB },
async () => {
const org = await createScratchOrg();
try {
const typeId = randomUUID();
await db.execute(sql`
insert into project_types (id, org_id, key, name, billing_method, invoicing_profile, backup_profile)
values (${typeId}, ${org.orgId}, ${"OV-" + randomUUID().slice(0, 8)}, 'Overlap type', 'time_and_materials',
'{"billingProcedure":"standard"}'::jsonb, '{}'::jsonb)`);
const base = await db.execute<{ id: string }>(sql`
insert into project_financial_profile_versions
(org_id, project_type_id, effective_from, effective_to, financial_profile, reason)
values (${org.orgId}, ${typeId}, '2026-01-01', '2026-06-30', '{}'::jsonb, 'Overlap guard base fixture')
returning id`);
const baseId = String(base.rows[0]!.id);
const versionInsert = (from: string, to: string) => ({
text: `insert into project_financial_profile_versions
(org_id, project_type_id, effective_from, effective_to, financial_profile, reason)
values ($1, $2, $3, $4, '{}'::jsonb, 'Overlap guard race fixture')`,
params: [org.orgId, typeId, from, to] as unknown[],
});
const updateCreate = await raceConflict(
{
text: "update project_financial_profile_versions set effective_to = $1 where id = $2",
params: ["2026-09-30", baseId],
},
versionInsert("2026-08-01", "2026-10-31"),
[{ text: "select set_config('openbooks.publish_project_profile', 'on', true)" }],
);
assertConflictRejected("project_financial_profile_versions update/create", updateCreate);
const createCreate = await raceConflict(
versionInsert("2026-10-01", "2026-11-30"),
versionInsert("2026-11-15", "2026-12-31"),
);
assertConflictRejected("project_financial_profile_versions create/create", createCreate);
await adjacentBothCommit(
versionInsert("2027-01-01", "2027-02-28"),
versionInsert("2027-03-01", "2027-04-30"),
);
// The replaced trigger's governed-window contracts survive: published
// versions stay immutable outside the publish and correct switches.
await assertRejectsWith(
db.execute(sql`
update project_financial_profile_versions
set financial_profile = '{"overhead":{"method":"none"}}'::jsonb
where id = ${baseId}`),
/published project financial profile versions are immutable/,
"project_financial_profile_versions JSON immutability must survive the guard replacement",
);
await assertRejectsWith(
db.execute(
sql`update project_financial_profile_versions set effective_to = '2027-01-01' where id = ${baseId}`,
),
/published project financial profile versions are immutable/,
"project_financial_profile_versions window immutability must survive the guard replacement",
);
await assertRejectsWith(
db.execute(sql`delete from project_financial_profile_versions where id = ${baseId}`),
/published project financial profile versions are immutable/,
"project_financial_profile_versions delete immutability must survive the guard replacement",
);
await assertRejectsWith(
db.transaction(async (tx) => {
await tx.execute(sql`select set_config('openbooks.correct_project_profile', 'on', true)`);
await tx.execute(sql`
update project_financial_profile_versions
set financial_profile = '{"overhead":{"method":"none"}}'::jsonb
where id = ${baseId}`);
}),
/may change only policy JSON and requires a reason/,
"project_financial_profile_versions correction mode must still demand a reason",
);
} finally {
await dropScratchOrgReporting(org.orgId);
}
},
);