502 lines
18 KiB
TypeScript
502 lines
18 KiB
TypeScript
/**
|
||
* Hierarchy schema witnesses — contract 1 (docs/requirements/hierarchy-schema.md) §6.
|
||
*
|
||
* Witnesses §6.1 (chain construction, slug scoping, grant CHECKs, grant
|
||
* uniqueness, NOT NULLs), §6.2 (column allowlist), the database-level parts of
|
||
* §6.6 (RESTRICT/cascade deletion behavior), and §6.7's catalog half (no
|
||
* foreign keys from outside the class into class tables).
|
||
*
|
||
* Two legs run the same witness body:
|
||
* - PGlite (WASM Postgres): always runs, so the witnesses execute locally
|
||
* with no database configured.
|
||
* - Real PostgreSQL (§6.8): runs when DATABASE_URL is set — in CI that is
|
||
* the ci-postgres service, migrated by the pipeline before `pnpm test`.
|
||
* This leg is the contract's binding witness; the PGlite leg is the local
|
||
* development signal.
|
||
*/
|
||
import { randomUUID } from 'node:crypto';
|
||
import { mkdtempSync, rmSync } from 'node:fs';
|
||
import { tmpdir } from 'node:os';
|
||
import { join } from 'node:path';
|
||
import { sql } from 'drizzle-orm';
|
||
import { afterAll, beforeAll, describe, expect, it } from 'vitest';
|
||
import { createDb } from './client.js';
|
||
import { createPgliteDb } from './client-pglite.js';
|
||
import { runPgliteMigrations } from './migrate.js';
|
||
import {
|
||
companies,
|
||
estates,
|
||
hierarchyGrants,
|
||
platformProjects,
|
||
workspaces,
|
||
teams,
|
||
users,
|
||
} from './schema.js';
|
||
|
||
type AnyDb = {
|
||
db: {
|
||
insert: (t: unknown) => { values: (v: unknown) => Promise<unknown> };
|
||
delete: (t: unknown) => { where?: unknown } & PromiseLike<unknown>;
|
||
execute: (q: unknown) => Promise<{ rows?: unknown[] } | unknown[]>;
|
||
};
|
||
close: () => Promise<void>;
|
||
};
|
||
|
||
/** Column allowlist — the exact declared sets of §2/§3. Nothing else. */
|
||
const COLUMN_ALLOWLIST: Record<string, string[]> = {
|
||
companies: ['id', 'name', 'slug', 'created_at', 'updated_at'],
|
||
estates: ['id', 'name', 'slug', 'company_id'],
|
||
platform_projects: ['id', 'name', 'slug', 'estate_id'],
|
||
workspaces: ['id', 'name', 'slug', 'platform_project_id'],
|
||
hierarchy_grants: [
|
||
'id',
|
||
'user_id',
|
||
'team_id',
|
||
'company_id',
|
||
'estate_id',
|
||
'platform_project_id',
|
||
'role',
|
||
'granted_by',
|
||
'created_at',
|
||
],
|
||
};
|
||
|
||
const NODE_TABLES = ['companies', 'estates', 'platform_projects', 'workspaces'];
|
||
const CLASS_TABLES = [...NODE_TABLES, 'hierarchy_grants'];
|
||
|
||
/**
|
||
* Drizzle wraps constraint failures ("Failed query: ...") with the driver
|
||
* error attached as `cause`. Match the pattern anywhere along the cause chain.
|
||
*/
|
||
async function expectViolation(p: Promise<unknown>, re: RegExp, label = ''): Promise<void> {
|
||
let err: unknown;
|
||
try {
|
||
await p;
|
||
} catch (e) {
|
||
err = e;
|
||
}
|
||
expect(err, label || 'expected the statement to be refused').toBeDefined();
|
||
const messages: string[] = [];
|
||
let cur: unknown = err;
|
||
while (cur instanceof Error) {
|
||
messages.push(cur.message);
|
||
cur = (cur as { cause?: unknown }).cause;
|
||
}
|
||
expect(messages.join(' | '), label).toMatch(re);
|
||
}
|
||
|
||
function rows(res: { rows?: unknown[] } | unknown[]): Record<string, unknown>[] {
|
||
return (Array.isArray(res) ? res : (res.rows ?? [])) as Record<string, unknown>[];
|
||
}
|
||
|
||
/** Unique per-run prefix so real-PG runs never collide and clean up safely. */
|
||
const T = `hier-w-${randomUUID().slice(0, 8)}`;
|
||
|
||
function witnessSuite(getHandle: () => AnyDb): void {
|
||
const db = () => getHandle().db as unknown as ReturnType<typeof createDb>['db'];
|
||
|
||
const userA = `${T}-user-a`;
|
||
const userB = `${T}-user-b`;
|
||
let teamId: string;
|
||
let companyId: string;
|
||
let company2Id: string;
|
||
let estateId: string;
|
||
let estate2Id: string;
|
||
let ppId: string;
|
||
let workspaceId: string;
|
||
|
||
beforeAll(async () => {
|
||
await db()
|
||
.insert(users)
|
||
.values([
|
||
{ id: userA, name: 'Witness A', email: `${userA}@example.com` },
|
||
{ id: userB, name: 'Witness B', email: `${userB}@example.com` },
|
||
]);
|
||
teamId = randomUUID();
|
||
await db()
|
||
.insert(teams)
|
||
.values({
|
||
id: teamId,
|
||
name: `${T}-team`,
|
||
slug: `${T}-team`,
|
||
ownerId: userA,
|
||
managerId: userA,
|
||
});
|
||
});
|
||
|
||
afterAll(async () => {
|
||
// Bottom-up, fail-closed order; grants cascade with their targets.
|
||
const d = db();
|
||
await d.execute(sql`DELETE FROM hierarchy_grants WHERE granted_by LIKE ${T + '%'}`);
|
||
await d.execute(sql`DELETE FROM workspaces WHERE slug LIKE ${T + '%'}`);
|
||
await d.execute(sql`DELETE FROM platform_projects WHERE slug LIKE ${T + '%'}`);
|
||
await d.execute(sql`DELETE FROM estates WHERE slug LIKE ${T + '%'}`);
|
||
await d.execute(sql`DELETE FROM companies WHERE slug LIKE ${T + '%'}`);
|
||
await d.execute(sql`DELETE FROM teams WHERE slug LIKE ${T + '%'}`);
|
||
await d.execute(sql`DELETE FROM users WHERE id LIKE ${T + '%'}`);
|
||
});
|
||
|
||
// ── §6.1 chain construction ────────────────────────────────────────────────
|
||
|
||
it('accepts a full valid chain: company → estate → platform-project → workspace', async () => {
|
||
companyId = randomUUID();
|
||
estateId = randomUUID();
|
||
ppId = randomUUID();
|
||
workspaceId = randomUUID();
|
||
await db()
|
||
.insert(companies)
|
||
.values({ id: companyId, name: 'Acme', slug: `${T}-acme` });
|
||
await db()
|
||
.insert(estates)
|
||
.values({ id: estateId, name: 'Estate 1', slug: `${T}-e1`, companyId });
|
||
await db()
|
||
.insert(platformProjects)
|
||
.values({ id: ppId, name: 'PP 1', slug: `${T}-pp1`, estateId });
|
||
await db()
|
||
.insert(workspaces)
|
||
.values({ id: workspaceId, name: 'WS 1', slug: `${T}-ws1`, platformProjectId: ppId });
|
||
});
|
||
|
||
it('accepts two siblings under one parent (the §2.5 control)', async () => {
|
||
estate2Id = randomUUID();
|
||
await db()
|
||
.insert(estates)
|
||
.values({ id: estate2Id, name: 'Estate 2', slug: `${T}-e2`, companyId });
|
||
});
|
||
|
||
it('refuses inserts with a null parent FK', async () => {
|
||
await expectViolation(
|
||
db().execute(
|
||
sql`INSERT INTO estates (id, name, slug, company_id) VALUES (${randomUUID()}, 'x', ${T + '-null-e'}, NULL)`,
|
||
),
|
||
/null value|not-null/i,
|
||
);
|
||
await expectViolation(
|
||
db().execute(
|
||
sql`INSERT INTO platform_projects (id, name, slug, estate_id) VALUES (${randomUUID()}, 'x', ${T + '-null-p'}, NULL)`,
|
||
),
|
||
/null value|not-null/i,
|
||
);
|
||
await expectViolation(
|
||
db().execute(
|
||
sql`INSERT INTO workspaces (id, name, slug, platform_project_id) VALUES (${randomUUID()}, 'x', ${T + '-null-w'}, NULL)`,
|
||
),
|
||
/null value|not-null/i,
|
||
);
|
||
});
|
||
|
||
it('refuses inserts with a dangling parent FK', async () => {
|
||
await expectViolation(
|
||
db()
|
||
.insert(estates)
|
||
.values({ id: randomUUID(), name: 'x', slug: `${T}-dangle`, companyId: randomUUID() }),
|
||
/foreign key/i,
|
||
);
|
||
});
|
||
|
||
it('catalog: each child table has exactly one parent-FK column and no parentage edge table exists', async () => {
|
||
const res = rows(
|
||
await db().execute(sql`
|
||
SELECT tc.table_name, kcu.column_name, ccu.table_name AS ref_table
|
||
FROM information_schema.table_constraints tc
|
||
JOIN information_schema.key_column_usage kcu
|
||
ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
|
||
JOIN information_schema.constraint_column_usage ccu
|
||
ON tc.constraint_name = ccu.constraint_name AND tc.table_schema = ccu.table_schema
|
||
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public'
|
||
`),
|
||
);
|
||
const nodeSet = new Set(NODE_TABLES);
|
||
// Exactly one parent FK per child node table.
|
||
for (const [child, parent] of [
|
||
['estates', 'companies'],
|
||
['platform_projects', 'estates'],
|
||
['workspaces', 'platform_projects'],
|
||
] as const) {
|
||
const parentFks = res.filter(
|
||
(r) => r['table_name'] === child && nodeSet.has(String(r['ref_table'])),
|
||
);
|
||
expect(parentFks.map((r) => `${r['column_name']}->${r['ref_table']}`)).toEqual([
|
||
`${{ estates: 'company_id', platform_projects: 'estate_id', workspaces: 'platform_project_id' }[child]}->${parent}`,
|
||
]);
|
||
}
|
||
// No table outside the class references a node table (also §6.7's catalog
|
||
// half for companies/estates/platform_projects/workspaces), and the only
|
||
// multi-FK referencer is hierarchy_grants (grant attachment, not
|
||
// parentage).
|
||
const referencers = new Map<string, number>();
|
||
for (const r of res) {
|
||
if (nodeSet.has(String(r['ref_table']))) {
|
||
const t = String(r['table_name']);
|
||
referencers.set(t, (referencers.get(t) ?? 0) + 1);
|
||
}
|
||
}
|
||
for (const [table, count] of referencers) {
|
||
expect(CLASS_TABLES, `unexpected referencer of a node table: ${table}`).toContain(table);
|
||
if (count > 1) expect(table).toBe('hierarchy_grants');
|
||
}
|
||
// No FK anywhere references hierarchy_grants.
|
||
expect(res.filter((r) => r['ref_table'] === 'hierarchy_grants')).toEqual([]);
|
||
});
|
||
|
||
// ── §6.1 slug scoping ──────────────────────────────────────────────────────
|
||
|
||
it('refuses a duplicate slug under the same parent, accepts it under another parent', async () => {
|
||
company2Id = randomUUID();
|
||
await db()
|
||
.insert(companies)
|
||
.values({ id: company2Id, name: 'Beta', slug: `${T}-beta` });
|
||
await expectViolation(
|
||
db()
|
||
.insert(estates)
|
||
.values({ id: randomUUID(), name: 'dup', slug: `${T}-e1`, companyId }),
|
||
/duplicate key|unique/i,
|
||
);
|
||
// Same slug, different company — accepted.
|
||
await db()
|
||
.insert(estates)
|
||
.values({ id: randomUUID(), name: 'ok', slug: `${T}-e1`, companyId: company2Id });
|
||
// companies.slug is unique per deployment.
|
||
await expectViolation(
|
||
db()
|
||
.insert(companies)
|
||
.values({ id: randomUUID(), name: 'dup', slug: `${T}-acme` }),
|
||
/duplicate key|unique/i,
|
||
);
|
||
});
|
||
|
||
it('scopes platform_projects and workspaces slugs per parent (refuse same-parent duplicate, accept cross-parent)', async () => {
|
||
// Dedicated parent estate so this test leaves estate2 a leaf (the §3.4
|
||
// cascade witness depends on that).
|
||
const estate3Id = randomUUID();
|
||
await db()
|
||
.insert(estates)
|
||
.values({ id: estate3Id, name: 'Estate 3', slug: `${T}-e3`, companyId });
|
||
// platform_projects: (estate_id, slug) unique.
|
||
await expectViolation(
|
||
db()
|
||
.insert(platformProjects)
|
||
.values({ id: randomUUID(), name: 'dup', slug: `${T}-pp1`, estateId }),
|
||
/duplicate key|unique/i,
|
||
);
|
||
const pp2Id = randomUUID();
|
||
await db()
|
||
.insert(platformProjects)
|
||
.values({ id: pp2Id, name: 'ok', slug: `${T}-pp1`, estateId: estate3Id });
|
||
// workspaces: (platform_project_id, slug) unique.
|
||
await expectViolation(
|
||
db()
|
||
.insert(workspaces)
|
||
.values({ id: randomUUID(), name: 'dup', slug: `${T}-ws1`, platformProjectId: ppId }),
|
||
/duplicate key|unique/i,
|
||
);
|
||
await db()
|
||
.insert(workspaces)
|
||
.values({ id: randomUUID(), name: 'ok', slug: `${T}-ws1`, platformProjectId: pp2Id });
|
||
});
|
||
|
||
// ── §6.2 column allowlist ──────────────────────────────────────────────────
|
||
|
||
it('column allowlist: each class table has exactly its declared columns (no payload, no owner_id)', async () => {
|
||
for (const [table, allow] of Object.entries(COLUMN_ALLOWLIST)) {
|
||
const res = rows(
|
||
await db().execute(
|
||
sql`SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = ${table}`,
|
||
),
|
||
);
|
||
const actual = res.map((r) => String(r['column_name'])).sort();
|
||
expect(actual, `column set of ${table}`).toEqual([...allow].sort());
|
||
}
|
||
});
|
||
|
||
// ── §6.1 grant CHECKs ──────────────────────────────────────────────────────
|
||
|
||
it('accepts one valid grant per subject×target form', async () => {
|
||
// All six forms; also the base rows for the §6.1 uniqueness witness below.
|
||
const forms = [
|
||
{ userId: userA, companyId },
|
||
{ userId: userA, estateId },
|
||
{ userId: userA, platformProjectId: ppId },
|
||
{ teamId, companyId },
|
||
{ teamId, estateId },
|
||
{ teamId, platformProjectId: ppId },
|
||
];
|
||
for (const form of forms) {
|
||
await db()
|
||
.insert(hierarchyGrants)
|
||
.values({ ...form, role: 'owner', grantedBy: userA });
|
||
}
|
||
});
|
||
|
||
it('refuses a grant with zero or two subjects (exactly-one-of CHECK)', async () => {
|
||
await expectViolation(
|
||
db().insert(hierarchyGrants).values({ companyId, role: 'viewer', grantedBy: userA }),
|
||
/check constraint/i,
|
||
);
|
||
await expectViolation(
|
||
db()
|
||
.insert(hierarchyGrants)
|
||
.values({ userId: userA, teamId, companyId, role: 'viewer', grantedBy: userA }),
|
||
/check constraint/i,
|
||
);
|
||
});
|
||
|
||
it('refuses a grant with zero or two targets (exactly-one-of CHECK)', async () => {
|
||
await expectViolation(
|
||
db().insert(hierarchyGrants).values({ userId: userA, role: 'viewer', grantedBy: userA }),
|
||
/check constraint/i,
|
||
);
|
||
await expectViolation(
|
||
db()
|
||
.insert(hierarchyGrants)
|
||
.values({ userId: userA, companyId, estateId, role: 'viewer', grantedBy: userA }),
|
||
/check constraint/i,
|
||
);
|
||
});
|
||
|
||
// ── §6.1 grant uniqueness (NULLS NOT DISTINCT) ─────────────────────────────
|
||
|
||
it('refuses a duplicate (subject, target, role) for each of the six forms', async () => {
|
||
const forms = [
|
||
{ userId: userA, companyId },
|
||
{ userId: userA, estateId },
|
||
{ userId: userA, platformProjectId: ppId },
|
||
{ teamId, companyId },
|
||
{ teamId, estateId },
|
||
{ teamId, platformProjectId: ppId },
|
||
];
|
||
for (const form of forms) {
|
||
await expectViolation(
|
||
db()
|
||
.insert(hierarchyGrants)
|
||
.values({ ...form, role: 'owner', grantedBy: userB }),
|
||
/duplicate key|unique/i,
|
||
`duplicate form ${JSON.stringify(form)} must be refused`,
|
||
);
|
||
}
|
||
// Control: same subject and target with a different role is a new grant.
|
||
await db()
|
||
.insert(hierarchyGrants)
|
||
.values({ userId: userA, companyId, role: `${T}-other-role`, grantedBy: userA });
|
||
});
|
||
|
||
// ── §6.1 NOT NULLs ─────────────────────────────────────────────────────────
|
||
|
||
it('refuses null role, granted_by, and null name/slug columns', async () => {
|
||
await expectViolation(
|
||
db().execute(
|
||
sql`INSERT INTO hierarchy_grants (user_id, company_id, role, granted_by) VALUES (${userA}, ${companyId}, NULL, ${userA})`,
|
||
),
|
||
/null value|not-null/i,
|
||
);
|
||
await expectViolation(
|
||
db().execute(
|
||
sql`INSERT INTO hierarchy_grants (user_id, company_id, role, granted_by) VALUES (${userA}, ${companyId}, 'x', NULL)`,
|
||
),
|
||
/null value|not-null/i,
|
||
);
|
||
await expectViolation(
|
||
db().execute(sql`INSERT INTO companies (name, slug) VALUES (NULL, ${T + '-nn'})`),
|
||
/null value|not-null/i,
|
||
);
|
||
await expectViolation(
|
||
db().execute(sql`INSERT INTO companies (name, slug) VALUES ('x', NULL)`),
|
||
/null value|not-null/i,
|
||
);
|
||
await expectViolation(
|
||
db().execute(
|
||
sql`INSERT INTO estates (name, slug, company_id) VALUES ('x', NULL, ${companyId})`,
|
||
),
|
||
/null value|not-null/i,
|
||
);
|
||
});
|
||
|
||
// ── §6.6 deletion (database-level witnesses) ───────────────────────────────
|
||
|
||
it('refuses deleting a node with children (fail-closed bottom-up)', async () => {
|
||
await expectViolation(
|
||
db().execute(sql`DELETE FROM companies WHERE id = ${companyId}`),
|
||
/foreign key/i,
|
||
);
|
||
await expectViolation(
|
||
db().execute(sql`DELETE FROM estates WHERE id = ${estateId}`),
|
||
/foreign key/i,
|
||
);
|
||
await expectViolation(
|
||
db().execute(sql`DELETE FROM platform_projects WHERE id = ${ppId}`),
|
||
/foreign key/i,
|
||
);
|
||
});
|
||
|
||
it('cascades a deleted leaf node’s grants and nothing else', async () => {
|
||
// estate2 is a leaf (no platform-projects). Attach one grant to it.
|
||
await db()
|
||
.insert(hierarchyGrants)
|
||
.values({ userId: userB, estateId: estate2Id, role: 'viewer', grantedBy: userA });
|
||
const grantCount = async () =>
|
||
Number(
|
||
rows(
|
||
await db().execute(
|
||
sql`SELECT count(*)::int AS n FROM hierarchy_grants WHERE granted_by LIKE ${T + '%'}`,
|
||
),
|
||
)[0]!['n'],
|
||
);
|
||
const before = await grantCount();
|
||
await db().execute(sql`DELETE FROM estates WHERE id = ${estate2Id}`);
|
||
// Exactly the one grant on the deleted estate is gone.
|
||
expect(await grantCount()).toBe(before - 1);
|
||
});
|
||
|
||
it('refuses deleting a user or team that is a grant subject or granted_by referent (RESTRICT)', async () => {
|
||
await expectViolation(db().execute(sql`DELETE FROM users WHERE id = ${userA}`), /foreign key/i);
|
||
// userB is only a subject (its estate2 grant cascaded away above, but it
|
||
// still holds no grants — re-create one to witness subject RESTRICT).
|
||
await db()
|
||
.insert(hierarchyGrants)
|
||
.values({ userId: userB, companyId: company2Id, role: 'viewer', grantedBy: userA });
|
||
await expectViolation(db().execute(sql`DELETE FROM users WHERE id = ${userB}`), /foreign key/i);
|
||
await expectViolation(
|
||
db().execute(sql`DELETE FROM teams WHERE id = ${teamId}`),
|
||
/foreign key/i,
|
||
);
|
||
});
|
||
}
|
||
|
||
// ── Leg 1: PGlite (always runs — local witness signal) ───────────────────────
|
||
|
||
describe('hierarchy schema witnesses — PGlite', () => {
|
||
let dir: string;
|
||
let handle: ReturnType<typeof createPgliteDb>;
|
||
|
||
beforeAll(async () => {
|
||
dir = mkdtempSync(join(tmpdir(), 'hier-witness-'));
|
||
handle = createPgliteDb(dir);
|
||
await runPgliteMigrations(handle);
|
||
});
|
||
|
||
afterAll(async () => {
|
||
await handle.close();
|
||
rmSync(dir, { recursive: true, force: true });
|
||
});
|
||
|
||
witnessSuite(() => handle as unknown as AnyDb);
|
||
});
|
||
|
||
// ── Leg 2: real PostgreSQL (§6.8 — binding witness, ci-postgres in CI) ───────
|
||
|
||
const hasPostgres = Boolean(process.env['DATABASE_URL']);
|
||
|
||
describe.skipIf(!hasPostgres)('hierarchy schema witnesses — real PostgreSQL', () => {
|
||
let handle: ReturnType<typeof createDb>;
|
||
|
||
beforeAll(() => {
|
||
handle = createDb(process.env['DATABASE_URL']!);
|
||
});
|
||
|
||
afterAll(async () => {
|
||
await handle.close();
|
||
});
|
||
|
||
witnessSuite(() => handle as unknown as AnyDb);
|
||
});
|