schema/migrated-storage-invariants.integration.test.ts
Rama main · réplica de lectura del repositorio original.
/// <reference types="node" />
/**
* Live storage invariants of the bootstrapped schema. Each test drives the
* real interface, inserting a violating row against the installed database,
* with an independently derived expectation, or reads the live catalog for a
* shape the product depends on. Where an engine or integration suite already
* proves a behaviour, no test is added here.
*
* Runs without a self-skip: the integration partition always provides a
* database, so a missing one must fail loudly, never pass silently.
*/
import assert from "node:assert/strict";
import { randomUUID } from "node:crypto";
import test from "node:test";
import { pool } from "@openbooks/engine/platform/db";
import {
createScratchOrg,
createScratchUser,
dropScratchOrg,
} from "@openbooks/engine/testing/fixtures";
function isConstraintViolation(error: unknown, pattern: RegExp): boolean {
if (!(error instanceof Error)) return false;
return pattern.test(error.message);
}
async function insertPayComponent(orgId: string): Promise<string> {
const id = randomUUID();
await pool.query(
`insert into pay_components (id, org_id, code, name, kind)
values ($1, $2, 'T8BASE', 'T8 base component', 'earning')`,
[id, orgId],
);
return id;
}
async function insertPaySchedule(orgId: string): Promise<string> {
const id = randomUUID();
await pool.query(
`insert into pay_schedules (id, org_id, name, frequency, periods_per_year, anchor_period_end)
values ($1, $2, 'T8 schedule', 'biweekly', 26, '2026-01-02')`,
[id, orgId],
);
return id;
}
test("overlapping active pay-derived windows for one rule identity are refused", async () => {
// Concurrent effective windows for the same (org, code) must not
// both be active — payroll would price the same earnings twice.
const org = await createScratchOrg();
try {
const componentId = await insertPayComponent(org.orgId);
const first = randomUUID();
await pool.query(
`insert into pay_derived_rules
(id, org_id, code, name, component_id, trigger, rate_value, effective_from, effective_to, is_active)
values ($1, $2, 'T8RULE', 'T8 rule', $3, 'time_entry', 10, '2026-01-01', '2026-12-31', true)`,
[first, org.orgId, componentId],
);
const overlapping = randomUUID();
const refused = await pool
.query(
`insert into pay_derived_rules
(id, org_id, code, name, component_id, trigger, rate_value, effective_from, effective_to, is_active)
values ($1, $2, 'T8RULE', 'T8 rule', $3, 'time_entry', 10, '2026-06-01', null, true)`,
[overlapping, org.orgId, componentId],
)
.then(
() => null,
(error: unknown) => error,
);
assert.ok(
isConstraintViolation(refused, /pay_derived_rules_no_active_overlap|exclusion/i),
`expected the overlap exclusion to refuse, got: ${String(refused)}`,
);
// An inactive twin is not a second live rule and must commit.
const inactive = randomUUID();
await pool.query(
`insert into pay_derived_rules
(id, org_id, code, name, component_id, trigger, rate_value, effective_from, effective_to, is_active)
values ($1, $2, 'T8RULE', 'T8 rule', $3, 'time_entry', 10, '2026-06-01', null, false)`,
[inactive, org.orgId, componentId],
);
} finally {
await dropScratchOrg(org.orgId);
}
});
test("tax rates refuse negative percentages and setup codes refuse duplicates", async () => {
// A negative rate would post inverted tax; a duplicate setup code
// would make pack resolution ambiguous.
const org = await createScratchOrg();
try {
const codeId = randomUUID();
await pool.query(
`insert into tax_codes (id, org_id, code, name) values ($1, $2, 'T8GST', 'T8 tax')`,
[codeId, org.orgId],
);
const negative = await pool
.query(
`insert into tax_rates (id, org_id, tax_code_id, rate_percent, effective_from)
values ($1, $2, $3, -5, '2026-01-01')`,
[randomUUID(), org.orgId, codeId],
)
.then(
() => null,
(error: unknown) => error,
);
assert.ok(
isConstraintViolation(negative, /tax_rates_rate_percent_domain|check/i),
`expected the rate domain check to refuse, got: ${String(negative)}`,
);
const duplicate = await pool
.query(`insert into tax_codes (id, org_id, code, name) values ($1, $2, 'T8GST', 'T8 copy')`, [
randomUUID(),
org.orgId,
])
.then(
() => null,
(error: unknown) => error,
);
assert.ok(
isConstraintViolation(duplicate, /tax_codes_org_code_unique|unique/i),
`expected the setup-code unique to refuse, got: ${String(duplicate)}`,
);
} finally {
await dropScratchOrg(org.orgId);
}
});
test("subscription plans refuse negative amounts", async () => {
// A negative plan amount would bill customers to receive money.
const org = await createScratchOrg();
try {
const refused = await pool
.query(
`insert into subscription_plans (id, org_id, name, amount) values ($1, $2, 'T8 plan', -10)`,
[randomUUID(), org.orgId],
)
.then(
() => null,
(error: unknown) => error,
);
assert.ok(
isConstraintViolation(refused, /subscription_plans_amount_nonnegative|check/i),
`expected the amount check to refuse, got: ${String(refused)}`,
);
} finally {
await dropScratchOrg(org.orgId);
}
});
test("forecast snapshots take at most one target and commit org-wide", async () => {
// A snapshot forecasting for both an owner and a team double-counts
// the pipeline; an org-wide snapshot is the honest default.
const org = await createScratchOrg();
const userId = randomUUID();
const teamId = randomUUID();
try {
// An active user must carry a role assignment; createScratchUser
// inserts user and assignment atomically.
await createScratchUser(org.orgId, "T8 owner", "t8_owner", userId);
await pool.query(
`insert into crm_sales_teams (id, org_id, key, name) values ($1, $2, 'T8', 'T8 team')`,
[teamId, org.orgId],
);
const snapshot = (id: string, owner: string | null, team: string | null) =>
pool.query(
`insert into crm_forecast_snapshots
(id, org_id, owner_user_id, sales_team_id, period_start, period_end,
snapshot_kind, currency, pipeline_amount, weighted_amount, worst_case_amount,
most_likely_amount, upside_amount, closed_amount)
values ($1, $2, $3, $4, '2026-01-01', '2026-03-31', 'commit', 'CAD', 0, 0, 0, 0, 0, 0)`,
[id, org.orgId, owner, team],
);
const both = await snapshot(randomUUID(), userId, teamId).then(
() => null,
(error: unknown) => error,
);
assert.ok(
isConstraintViolation(both, /crm_forecast_snapshot_target|check/i),
`expected the single-target check to refuse, got: ${String(both)}`,
);
await snapshot(randomUUID(), null, null);
await snapshot(randomUUID(), userId, null);
} finally {
await dropScratchOrg(org.orgId);
}
});
test("payroll profiles refuse an omitted country instead of defaulting one", async () => {
// No country built-in exists, so an unset country must fail NOT NULL
// rather than silently becoming Canada.
const org = await createScratchOrg();
try {
const scheduleId = await insertPaySchedule(org.orgId);
const refused = await pool
.query(
`insert into employee_payroll_profiles (id, org_id, employee_party_id, pay_schedule_id, province)
values ($1, $2, $3, $4, 'Ontario')`,
[randomUUID(), org.orgId, org.customerId, scheduleId],
)
.then(
() => null,
(error: unknown) => error,
);
assert.ok(
isConstraintViolation(refused, /null value in column "country"|not-null/i),
`expected NOT NULL on country to refuse, got: ${String(refused)}`,
);
} finally {
await dropScratchOrg(org.orgId);
}
});
test("payroll pack facts refuse out-of-range values and accept unknown", async () => {
// Pack-declared facts are nullable (null = unknown, never guessed)
// but a present value outside the pack's bounds is refused.
const org = await createScratchOrg();
try {
const scheduleId = await insertPaySchedule(org.orgId);
const base = (id: string, country: string) =>
pool.query(
`insert into employee_payroll_profiles
(id, org_id, employee_party_id, pay_schedule_id, province, country)
values ($1, $2, $3, $4, 'Ontario', $5)`,
[id, org.orgId, org.customerId, scheduleId, country],
);
const profileId = randomUUID();
await base(profileId, "ES");
const outOfRange = await pool
.query(`update employee_payroll_profiles set es_ano_nacimiento = 1800 where id = $1`, [
profileId,
])
.then(
() => null,
(error: unknown) => error,
);
assert.ok(
isConstraintViolation(outOfRange, /employee_payroll_profiles_es_ano|check/i),
`expected the birth-year check to refuse, got: ${String(outOfRange)}`,
);
await pool.query(
`update employee_payroll_profiles set es_ano_nacimiento = null where id = $1`,
[profileId],
);
} finally {
await dropScratchOrg(org.orgId);
}
});
test("sync runs detach from a deleted connection instead of vanishing", async () => {
// Run history is evidence; deleting a connection must null the link
// (ON DELETE SET NULL over a nullable column), not cascade the runs.
const org = await createScratchOrg();
try {
const connectionId = randomUUID();
await pool.query(
`insert into connections (id, org_id, source, display_name) values ($1, $2, 't8', 'T8 connection')`,
[connectionId, org.orgId],
);
const runId = randomUUID();
await pool.query(
`insert into sync_runs (id, org_id, source, connection_id) values ($1, $2, 't8', $3)`,
[runId, org.orgId, connectionId],
);
await pool.query(`delete from connections where id = $1`, [connectionId]);
const stored = await pool.query(`select connection_id, status from sync_runs where id = $1`, [
runId,
]);
assert.equal(stored.rows[0]?.connection_id, null);
assert.equal(stored.rows[0]?.status, "running");
} finally {
await dropScratchOrg(org.orgId);
}
});
test("sftp prefixes refuse escape shapes and accept tenant folders", async () => {
// A tenant names folders, never physical locations — dot segments,
// absolute paths, and backslashes must not reach storage.
const org = await createScratchOrg();
try {
// Usernames are globally unique: scope the probe name to this run.
const username = `t8user-${randomUUID().slice(0, 8)}`;
const attempt = (prefix: string) =>
pool.query(
`insert into sftp_servers (id, org_id, name, username, root_prefix)
values ($1, $2, 'T8 sftp', $4, $3)`,
[randomUUID(), org.orgId, prefix, username],
);
const escape = await attempt("../escape").then(
() => null,
(error: unknown) => error,
);
assert.ok(
isConstraintViolation(escape, /sftp_servers_root_prefix_safe|check/i),
`expected the prefix guard to refuse, got: ${String(escape)}`,
);
await attempt("t8-ok/sub");
} finally {
await dropScratchOrg(org.orgId);
}
});
test("recognition rules refuse a second live version of one code", async () => {
// Editing a rule in use must create a successor, never mutate the
// version obligations already pinned — so only one live row per code.
const org = await createScratchOrg();
try {
await pool.query(
`insert into recognition_rules (id, org_id, code, name, method)
values ($1, $2, 'T8RULE', 'T8 rule', 'straight_line_even')`,
[randomUUID(), org.orgId],
);
const duplicate = await pool
.query(
`insert into recognition_rules (id, org_id, code, name, method)
values ($1, $2, 'T8RULE', 'T8 twin', 'straight_line_even')`,
[randomUUID(), org.orgId],
)
.then(
() => null,
(error: unknown) => error,
);
assert.ok(
isConstraintViolation(duplicate, /recognition_rules_org_code|unique/i),
`expected the live-version unique to refuse, got: ${String(duplicate)}`,
);
} finally {
await dropScratchOrg(org.orgId);
}
});
test("delivery keys refuse malformed values and double-claimed identities", async () => {
// The key is a fixed engine-minted shape, and one logical
// delivery owns exactly one canonical row.
const org = await createScratchOrg();
try {
const malformed = await pool
.query(
`insert into email_log (id, org_id, subject, delivery_key) values ($1, $2, 'T8', 'not-a-key')`,
[randomUUID(), org.orgId],
)
.then(
() => null,
(error: unknown) => error,
);
assert.ok(
isConstraintViolation(malformed, /email_log_delivery_key_format|check/i),
`expected the key format check to refuse, got: ${String(malformed)}`,
);
const key = `obem_${"a".repeat(40)}`;
await pool.query(
`insert into email_log (id, org_id, subject, delivery_key) values ($1, $2, 'T8', $3)`,
[randomUUID(), org.orgId, key],
);
const twin = await pool
.query(
`insert into email_log (id, org_id, subject, delivery_key) values ($1, $2, 'T8 twin', $3)`,
[randomUUID(), org.orgId, key],
)
.then(
() => null,
(error: unknown) => error,
);
assert.ok(
isConstraintViolation(twin, /email_log_delivery_key|unique/i),
`expected the delivery identity unique to refuse, got: ${String(twin)}`,
);
} finally {
await dropScratchOrg(org.orgId);
}
});
test("document revisions advance on every committed update", async () => {
// Two committed revisions must never share a token. The
// collapse shape — an update storing updated_at byte-identical to the
// row — is rewritten strictly forward by documents_revision_monotonic,
// and openbooks_bump_revision_seq counts every UPDATE independently of
// the editable display timestamp. (The web optimistic-concurrency test
// proving the same lives in a skip-guarded plain .test.ts, so it never
// runs; this is the live cover.)
const org = await createScratchOrg();
try {
const id = randomUUID();
// Microsecond-exact display timestamps: node-pg truncates to
// milliseconds, which would never reproduce the collapse shape.
const stamp = "2026-08-24T12:00:00.400001Z";
await pool.query(
`insert into documents (id, org_id, kind, document_number, subsidiary_id, document_date, currency, status,
created_at, updated_at)
values ($1, $2, 'transfer', $3, $4, '2026-01-02', 'CAD', 'draft', $5::timestamptz, $5::timestamptz)`,
[id, org.orgId, `T8REV-${id.slice(0, 8)}`, org.subsidiaryId, stamp],
);
const read = async (): Promise<{ updated_at: Date; revision_seq: string }> =>
(await pool.query(`select updated_at, revision_seq from documents where id = $1`, [id]))
.rows[0] as {
updated_at: Date;
revision_seq: string;
};
const first = await read();
// Collapse-shaped write: store updated_at byte-identical to the row.
await pool.query(
`update documents set memo = 'T8 collapse', updated_at = $2::timestamptz where id = $1`,
[id, stamp],
);
const second = await read();
assert.ok(
second.updated_at > first.updated_at,
`a collapse write must move updated_at strictly forward, got ${String(second.updated_at)}`,
);
assert.equal(
BigInt(second.revision_seq),
BigInt(first.revision_seq) + 1n,
"every committed UPDATE must bump the revision counter exactly once",
);
await pool.query(`update documents set memo = 'T8 second' where id = $1`, [id]);
const third = await read();
assert.ok(
third.updated_at > second.updated_at,
`a second update must advance updated_at again, got ${String(third.updated_at)}`,
);
assert.equal(
BigInt(third.revision_seq),
BigInt(second.revision_seq) + 1n,
"the revision counter must keep counting past the first bump",
);
} finally {
await dropScratchOrg(org.orgId);
}
});
test("bank-feed bookkeeping separates the attempt cursor from the success cursor", async () => {
// Last_attempt_at records every finished attempt; last_sync_at is
// the success-only pull cursor the runner reads. The two must move
// independently — recording an attempt (even a failed one) must not
// advance the success cursor. (Runner-side attempt/success sequencing
// lives in engine banking tests; this is the storage half.)
const org = await createScratchOrg();
try {
const id = randomUUID();
await pool.query(
`insert into bank_feed_connections (id, org_id, name, provider, account_id)
values ($1, $2, 'T8 feed', 'manual', $3)`,
[id, org.orgId, randomUUID()],
);
await pool.query(
`update bank_feed_connections set last_attempt_at = now(), last_error = 'T8 probe failure', status = 'error'
where id = $1`,
[id],
);
const attempted = (
await pool.query(
`select last_attempt_at, last_sync_at from bank_feed_connections where id = $1`,
[id],
)
).rows[0] as { last_attempt_at: Date | null; last_sync_at: Date | null };
assert.ok(
attempted.last_attempt_at !== null,
"a finished attempt must stamp the attempt cursor",
);
assert.equal(attempted.last_sync_at, null, "a failed attempt must not move the success cursor");
await pool.query(
`update bank_feed_connections set last_attempt_at = now(), last_sync_at = now(), last_error = null,
status = 'connected' where id = $1`,
[id],
);
const succeeded = (
await pool.query(
`select last_attempt_at, last_sync_at from bank_feed_connections where id = $1`,
[id],
)
).rows[0] as { last_attempt_at: Date | null; last_sync_at: Date | null };
assert.ok(succeeded.last_sync_at !== null, "a success must stamp the success cursor");
assert.ok(succeeded.last_attempt_at !== null, "a success is also a finished attempt");
} finally {
await dropScratchOrg(org.orgId);
}
});
test("the installed catalog carries the canonical schema shape", async () => {
// Table/function/trigger/index presence, retired-model absence,
// adjustment RLS wiring and source-identity indexes, read from the live
// catalog: no scratch org needed, nothing written.
const tables = await pool.query(
`select tablename from pg_tables where schemaname = 'public' and tablename = any ($1)`,
[
[
"lease_agreements",
"lease_agreement_schedule_lines",
"inventory_writedowns",
"pay_schedules",
"pay_components",
"pay_run_adjustments",
"employee_payroll_profiles",
"employee_pay_components",
"pay_runs",
"pay_stubs",
"pay_stub_lines",
"payroll_opening_balances",
"union_agreements",
"union_classifications",
"union_fringes",
"auth_sessions",
"auth_login_events",
"auth_mfa_factors",
"auth_oidc_identities",
],
],
);
assert.equal(tables.rows.length, 19, "every canonical table must exist in the installed catalog");
const routines = await pool.query(
`select proname from pg_proc where pronamespace = 'public'::regnamespace and proname = any ($1)`,
[["posted_document_financial_guard"]],
);
assert.equal(routines.rows.length, 1, "the posted-document financial guard must exist");
const triggers = await pool.query(
`select tgname from pg_trigger where tgname = any ($1) and not tgisinternal`,
[["documents_posted_financial_guard"]],
);
assert.equal(triggers.rows.length, 1, "the posted-document guard trigger must exist");
const indexes = await pool.query(
`select indexname from pg_indexes where schemaname = 'public' and indexname = any ($1)`,
[
[
"backup_runs_one_inflight_per_org",
"parties_org_source_identity",
"projects_org_source_identity",
],
],
);
assert.equal(
indexes.rows.length,
3,
"one-inflight backup, tenant source-identity indexes must exist",
);
const retired = await pool.query(
`select tablename from pg_tables where schemaname = 'public' and tablename = any ($1)`,
[
[
"orphaned_tax_component_evidence",
"_migration_control_exceptions",
"_migration_schema_convergence",
"selection_source",
"legacy_json_migration",
"validation_replay",
],
],
);
assert.deepEqual(
retired.rows.map((row) => row.tablename),
[],
"no upgrade-only evidence model may survive in the catalog",
);
const policies = await pool.query(
`select policyname, permissive, roles::text, cmd, qual is not null as scoped, with_check is not null as checked
from pg_policies where schemaname = 'public' and tablename = 'pay_run_adjustments' and policyname = 'org_isolation'`,
);
assert.equal(policies.rows.length, 1, "pay_run_adjustments must carry the org_isolation policy");
assert.equal(policies.rows[0]?.scoped, true, "org_isolation must filter reads");
assert.equal(policies.rows[0]?.checked, true, "org_isolation must filter writes");
const relkind = await pool.query(
`select relforcerowsecurity, relrowsecurity from pg_class where relname = 'pay_run_adjustments'`,
);
assert.equal(
relkind.rows[0]?.relrowsecurity,
true,
"pay_run_adjustments must enforce row security",
);
assert.equal(
relkind.rows[0]?.relforcerowsecurity,
true,
"pay_run_adjustments must force row security",
);
});
test("the payroll opening-balances view exposes the statutory columns", async () => {
// The statutory columns of the governed payroll opening-balances view are
// its interface to payroll reporting.
const columns = await pool.query(
`select column_name from information_schema.columns
where table_schema = 'openbooks_query' and table_name = 'payroll_opening_balances'`,
);
const names = new Set(columns.rows.map((row) => row.column_name));
for (const column of ["cpp2_bonus_ytd", "qc_csb_ytd", "fica_withheld_ytd"]) {
assert.ok(names.has(column), `the view must expose ${column}`);
}
});