Files

956 lines
40 KiB
TypeScript

// @vitest-environment node
import pg from "pg";
import { afterAll, beforeAll, describe, expect, it } from "vitest";
import { disconnectDbClients } from "@hivegrid/db/client.server";
import { runWithRlsContext } from "@hivegrid/db/rls-context.server";
import { hashWorkspaceInviteToken } from "@hivegrid/db/workspace-invites.server";
import { closeSharedStores } from "~/shared-store.server";
import { hashPassword } from "~/utils/auth/passwordHasher.server";
import { db } from "~/utils/db.server";
import { TenantSlugCollisionError } from "~/utils/db/tenants.db.server";
import { SHK_V2_TENANT_BOOTSTRAP_CONFIG, SHK_V2_TENANT_BOOTSTRAP_CONFIG_DIGEST } from "./shk-v2.config";
import { SHK_FIRST_STEPS, SHK_FIRST_STEPS_CHECKLIST_CODE, SHK_FIRST_STEPS_CHECKLIST_ID } from "./tenantBootstrap.constants";
import {
claimReservedTenantWithShkDefaults,
createSignupTenantWithShkDefaults,
reserveTenantWithoutOwner,
SHK_V2_BOOTSTRAP_SCOPE,
type TenantBootstrapResult,
} from "./tenantBootstrapService.server";
const ADMIN_URL = process.env.RLS_TEST_DATABASE_URL;
const APP_DATABASE_URL = process.env.DATABASE_URL;
const APP_ROLE = "hivegrid_app";
const SUCCESS_EMAIL = "rls-product-bootstrap-live@example.test";
const SUCCESS_SLUG = "rls-product-bootstrap-live";
const ROLLBACK_EMAIL = "rls-product-bootstrap-rollback@example.test";
const ROLLBACK_SLUG = "rls-product-bootstrap-rollback";
const SUCCESS_CREATION_REQUEST_KEY = "484ac2c0-0e91-4fb8-92ac-b0056cdb822c";
const COLLISION_EMAIL = "rls-product-bootstrap-collision@example.test";
const COLLISION_CREATION_REQUEST_KEY = "48dd961f-f270-41f5-b99e-f256a860e031";
const ROLLBACK_CREATION_REQUEST_KEY = "d9291bd7-e1fc-46af-8f6c-92901934af48";
const FOREIGN_USER_ID = "rls_product_bootstrap_foreign_user";
const FOREIGN_TENANT_ID = "rls_product_bootstrap_foreign_tenant";
const FOREIGN_MEMBERSHIP_ID = "rls_product_bootstrap_foreign_membership";
const RESERVATION_REQUEST_KEY = "72b24df6-d97c-4bed-b349-278107253b36";
const RESERVATION_SLUG = "rls-product-ownerless-reservation";
const RESERVATION_OWNER_ID = "rls_product_reservation_owner";
const RESERVATION_OWNER_EMAIL = "rls-product-reservation-owner@example.test";
const RESERVATION_OWNER_INVITE_TOKEN = "rls-product-reservation-owner-token-7w0UbHBUwhI";
const CLAIM_ROLLBACK_REQUEST_KEY = "a9f5e8bc-c418-4ba0-bb80-0393dd64c27d";
const CLAIM_ROLLBACK_SLUG = "rls-product-ownerless-claim-rollback";
const CLAIM_ROLLBACK_USER_ID = "rls_product_claim_rollback_owner";
const CLAIM_ROLLBACK_EMAIL = "rls-product-claim-rollback@example.test";
const CLAIM_ROLLBACK_TOKEN = "rls-product-claim-rollback-token-6xDPsbBfJ9p";
const CLAIM_RACE_REQUEST_KEY = "8281ae1e-fab4-4282-8cde-57b09286b729";
const CLAIM_RACE_SLUG = "rls-product-ownerless-claim-race";
const CLAIM_RACE_USER_IDS = ["rls_product_claim_race_owner_a", "rls_product_claim_race_owner_b"] as const;
const CLAIM_RACE_EMAIL = "rls-product-claim-race@example.test";
const CLAIM_RACE_TOKEN = "rls-product-claim-race-token-2drxMzUCD8T";
const TEST_ADMIN_ROLE_ID = "rls_product_bootstrap_global_admin";
const TRACE_TABLE = "rls_product_bootstrap_write_trace";
const TRACE_FUNCTION = "rls_product_bootstrap_trace_write";
const isRestrictedAppDatabase = (() => {
if (!ADMIN_URL || !APP_DATABASE_URL) return false;
try {
return new URL(APP_DATABASE_URL).username === APP_ROLE;
} catch {
return false;
}
})();
const describeIfLiveDatabase = isRestrictedAppDatabase ? describe : describe.skip;
const translate = (key: string) => `Live: ${key}`;
let admin: pg.Client;
let successfulBootstrap: TenantBootstrapResult | undefined;
let reservedTenantId: string | undefined;
let reservedOwnerInviteId: string | undefined;
let claimRollbackTenantId: string | undefined;
let claimRaceTenantId: string | undefined;
let insertedAdminRole = false;
let insertedChecklist = false;
let originalChecklistActive = true;
const insertedPermissionIds: string[] = [];
describeIfLiveDatabase("SHK v2 product signup under FORCE RLS", () => {
beforeAll(async () => {
admin = new pg.Client({ connectionString: ADMIN_URL });
await admin.connect();
await dropTraceInfrastructure();
await cleanupStaleProductFixtures();
await provisionGlobalPrerequisites();
await createTraceInfrastructure();
await provisionForeignTenant();
});
afterAll(async () => {
try {
if (successfulBootstrap) {
await cleanupTenantRows(successfulBootstrap.tenant.id, successfulBootstrap.user.id);
}
if (reservedTenantId) {
await cleanupTenantRows(reservedTenantId, RESERVATION_OWNER_ID);
}
if (claimRollbackTenantId) {
await cleanupTenantRows(claimRollbackTenantId, CLAIM_ROLLBACK_USER_ID);
}
if (claimRaceTenantId) {
await cleanupTenantRows(claimRaceTenantId, CLAIM_RACE_USER_IDS[0]);
await admin?.query('DELETE FROM "User" WHERE id = ANY($1::text[])', [CLAIM_RACE_USER_IDS]);
}
await cleanupTenantRows(FOREIGN_TENANT_ID, FOREIGN_USER_ID);
await admin?.query('DELETE FROM "User" WHERE email IN ($1, $2, $3)', [SUCCESS_EMAIL, ROLLBACK_EMAIL, COLLISION_EMAIL]);
await admin?.query('DELETE FROM "Tenant" WHERE slug LIKE $1 OR slug LIKE $2', [`${SUCCESS_SLUG}%`, `${ROLLBACK_SLUG}%`]);
if (insertedChecklist) {
await admin?.query('DELETE FROM "Onboarding" WHERE id = $1', [SHK_FIRST_STEPS_CHECKLIST_ID]);
} else {
await admin?.query('UPDATE "Onboarding" SET active = $2 WHERE id = $1', [SHK_FIRST_STEPS_CHECKLIST_ID, originalChecklistActive]);
}
if (insertedAdminRole) {
await admin?.query('DELETE FROM "Role" WHERE id = $1', [TEST_ADMIN_ROLE_ID]);
}
if (insertedPermissionIds.length > 0) {
await admin?.query('DELETE FROM "Permission" WHERE id = ANY($1::text[])', [insertedPermissionIds]);
}
await dropTraceInfrastructure();
} finally {
await disconnectDbClients();
await closeSharedStores();
await admin?.end();
}
});
it("creates the complete baseline atomically through the product signup service", async () => {
const runtimeIdentity = await db.$queryRawUnsafe<Array<{ current_user: string }>>("SELECT current_user");
expect(runtimeIdentity).toEqual([{ current_user: APP_ROLE }]);
successfulBootstrap = await createSignupTenantWithShkDefaults({
email: SUCCESS_EMAIL,
password: "Live-RLS-password-42!",
firstName: "Mara",
lastName: "König",
company: "RLS Product Bootstrap Live",
slug: SUCCESS_SLUG,
locale: "de",
creationRequestKey: SUCCESS_CREATION_REQUEST_KEY,
t: translate,
});
expect(successfulBootstrap.replayed).toBe(false);
expect(successfulBootstrap.tenant).toMatchObject({ active: true, deactivatedReason: null, slug: SUCCESS_SLUG });
const snapshot = await readBootstrapSnapshot(successfulBootstrap.tenant.id, successfulBootstrap.user.id);
expect(snapshot.tenant).toEqual({ id: successfulBootstrap.tenant.id, active: true, deactivatedReason: null });
expect(snapshot.user).toEqual({ id: successfulBootstrap.user.id, active: true, defaultTenantId: successfulBootstrap.tenant.id });
expect(snapshot.memberships).toEqual([
{
tenantId: successfulBootstrap.tenant.id,
userId: successfulBootstrap.user.id,
type: 0,
joined: 0,
status: 2,
},
]);
expect(snapshot.assignments).toEqual([
{
tenantId: successfulBootstrap.tenant.id,
userId: successfulBootstrap.user.id,
role: { tenantId: null, name: "Admin", type: "app" },
},
]);
expect(snapshot.roles).toHaveLength(5);
expect(snapshot.roles.map((role) => role.name)).toEqual(SHK_V2_TENANT_BOOTSTRAP_CONFIG.tenantRoles.map((role) => role.name));
expect(snapshot.roles.every((role) => role.type === "app" && role.isTemplateOrigin && !role.assignToNewUsers && !role.isDefault)).toBe(true);
expect(snapshot.pipelines).toHaveLength(3);
expect(snapshot.pipelines.map((pipeline) => pipeline.name)).toEqual(SHK_V2_TENANT_BOOTSTRAP_CONFIG.salesPipelines.map((pipeline) => pipeline.name));
expect(snapshot.pipelines.flatMap((pipeline) => pipeline.stages)).toHaveLength(15);
expect(snapshot.pipelines.filter((pipeline) => pipeline.isDefault).map((pipeline) => pipeline.name)).toEqual(["Vertrieb Wärmepumpe"]);
for (const pipeline of snapshot.pipelines) {
expect(pipeline.stages.map((stage) => stage.name)).toEqual(["Neu", "Qualifiziert", "Angebot", "Gewonnen", "Verloren"]);
}
expect(snapshot.forms).toEqual([
{ name: "SHK Abnahmeprotokoll", slug: "shk-abnahmeprotokoll", subtype: "acceptance_form", status: "draft", isDemoData: false },
{ name: "SHK Angebotsanfrage", slug: "shk-angebotsanfrage", subtype: "standard", status: "draft", isDemoData: false },
]);
expect(snapshot.acceptanceTemplates).toEqual([{ name: "SHK Standard-Abnahme", isDefault: true, isDemoData: false }]);
expect(snapshot.projectTemplates).toEqual([{ name: "SHK Standard-Projekt", isDemoData: false }]);
expect(snapshot.checklistSessions).toHaveLength(1);
expect(snapshot.checklistSessions[0]).toMatchObject({ status: "active", onboarding: { id: SHK_FIRST_STEPS_CHECKLIST_ID } });
expect(snapshot.checklistSessions[0]?.sessionSteps.map(({ step }) => step)).toEqual(SHK_FIRST_STEPS.map((step) => ({ ...step })));
expect(snapshot.automations).toHaveLength(3);
expect(snapshot.automations.map((automation) => automation.name)).toEqual(
SHK_V2_TENANT_BOOTSTRAP_CONFIG.automationPlaceholders.map((automation) => automation.templateKey).sort()
);
for (const automation of snapshot.automations) {
expect(automation.isPublished).toBe(false);
expect(automation.flowData).toMatchObject({ isExecutable: false, nodes: [] });
}
expect(snapshot.markers).toHaveLength(1);
expect(snapshot.markers[0]).toMatchObject({
scope: SHK_V2_BOOTSTRAP_SCOPE,
key: SHK_V2_TENANT_BOOTSTRAP_CONFIG.templateKey,
requestHash: SHK_V2_TENANT_BOOTSTRAP_CONFIG_DIGEST,
responseStatus: 201,
responseBody: { state: "completed", templateKey: "shk-v2", schemaVersion: 1 },
});
const trace = await admin.query<{ event: string }>(`SELECT event FROM public.${TRACE_TABLE} WHERE tenant_id = $1 ORDER BY seq`, [
successfulBootstrap.tenant.id,
]);
expect(trace.rows.at(-2)?.event).toBe("marker.complete");
expect(trace.rows.at(-1)?.event).toBe("tenant.activate");
const foreignSnapshot = await readForeignSnapshot(successfulBootstrap.tenant.id, successfulBootstrap.user.id);
expect(foreignSnapshot).toEqual({
tenants: [],
users: [],
memberships: [],
assignments: [],
roles: [],
pipelines: [],
stages: [],
forms: [],
acceptanceTemplates: [],
projectTemplates: [],
checklistSessions: [],
automations: [],
markers: [],
});
});
it("rejects a globally occupied explicit slug that pre-auth RLS cannot enumerate", async () => {
expect(successfulBootstrap).toBeDefined();
await expect(
createSignupTenantWithShkDefaults({
email: COLLISION_EMAIL,
password: "Live-RLS-password-44!",
firstName: "Rita",
lastName: "Collision",
company: "RLS Product Bootstrap Collision",
slug: SUCCESS_SLUG,
locale: "de",
creationRequestKey: COLLISION_CREATION_REQUEST_KEY,
t: translate,
})
).rejects.toBeInstanceOf(TenantSlugCollisionError);
const userRows = await admin.query<{ id: string }>('SELECT id FROM "User" WHERE email = $1', [COLLISION_EMAIL]);
const tenantRows = await admin.query<{ id: string }>('SELECT id FROM "Tenant" WHERE name = $1', ["RLS Product Bootstrap Collision"]);
expect(userRows.rows).toEqual([]);
expect(tenantRows.rows).toEqual([]);
});
it("creates and exactly replays an ownerless reservation under restricted RLS", async () => {
const input = {
name: "RLS Product Ownerless Reservation",
slug: RESERVATION_SLUG,
operatorUserId: FOREIGN_USER_ID,
reservationRequestKey: RESERVATION_REQUEST_KEY,
ownerInvitation: {
issuanceKey: RESERVATION_REQUEST_KEY,
email: RESERVATION_OWNER_EMAIL,
firstName: "Olivia",
lastName: "Owner",
fromUserId: FOREIGN_USER_ID,
token: RESERVATION_OWNER_INVITE_TOKEN,
mail: {
subject: "RLS product reservation owner invitation",
html: `<a href="https://app.example.test/invitation/${RESERVATION_OWNER_INVITE_TOKEN}">Accept invitation</a>`,
},
},
};
const first = await runWithRlsContext({ tenantId: FOREIGN_TENANT_ID, userId: FOREIGN_USER_ID }, () => reserveTenantWithoutOwner(input));
reservedTenantId = first.id;
const replay = await runWithRlsContext({ tenantId: FOREIGN_TENANT_ID, userId: FOREIGN_USER_ID }, () => reserveTenantWithoutOwner(input));
expect(replay).toMatchObject({
id: first.id,
name: input.name,
slug: input.slug,
active: false,
deactivatedReason: "pending_owner",
});
const persisted = await admin.query<{
active: boolean;
deactivatedReason: string | null;
requestHash: string;
responseStatus: number;
responseBody: { state?: string } | null;
}>(
`SELECT tenant.active,
tenant."deactivatedReason",
marker."requestHash",
marker."responseStatus",
marker."responseBody"
FROM "Tenant" AS tenant
JOIN "IdempotencyKey" AS marker ON marker."tenantId" = tenant.id
WHERE tenant.id = $1 AND marker.scope = 'tenant-reservation' AND marker.key = $2`,
[first.id, RESERVATION_REQUEST_KEY]
);
expect(persisted.rows).toEqual([
expect.objectContaining({
active: false,
deactivatedReason: "pending_owner",
requestHash: expect.stringMatching(/^[a-f0-9]{64}$/),
responseStatus: 201,
responseBody: expect.objectContaining({ state: "reserved" }),
}),
]);
const invitation = await admin.query<{
id: string;
tokenHash: string | null;
status: string;
outboxCount: string;
}>(
`SELECT invitation.id,
invitation."tokenHash",
invitation.status,
count(outbox.id)::text AS "outboxCount"
FROM "TenantUserInvitation" AS invitation
LEFT JOIN "OutboxEvent" AS outbox
ON outbox."dispatchKey" = 'mail:workspace-owner-invite:' || invitation.id
WHERE invitation."tenantId" = $1 AND invitation.type = 0
GROUP BY invitation.id, invitation."tokenHash", invitation.status`,
[first.id]
);
expect(invitation.rows).toEqual([
expect.objectContaining({
tokenHash: hashWorkspaceInviteToken(RESERVATION_OWNER_INVITE_TOKEN),
status: "pending",
outboxCount: "1",
}),
]);
reservedOwnerInviteId = invitation.rows[0]?.id;
await expect(
runWithRlsContext({ tenantId: FOREIGN_TENANT_ID, userId: FOREIGN_USER_ID }, () => reserveTenantWithoutOwner({ ...input, name: "Changed Reservation" }))
).rejects.toMatchObject({ code: "foundation.idempotency_conflict" });
});
it("claims the ownerless reservation atomically through a verified OWNER invite under restricted RLS", async () => {
expect(reservedTenantId).toBeDefined();
expect(reservedOwnerInviteId).toBeDefined();
const tenantId = reservedTenantId!;
const claimInput = {
tenantId,
userId: RESERVATION_OWNER_ID,
t: translate,
createUser: {
id: RESERVATION_OWNER_ID,
email: RESERVATION_OWNER_EMAIL,
passwordHash: await hashPassword("Live-RLS-owner-password-45!"),
firstName: "Olivia",
lastName: "Owner",
locale: "de",
},
claim: {
kind: "workspace-invite" as const,
inviteId: reservedOwnerInviteId!,
token: RESERVATION_OWNER_INVITE_TOKEN,
createdUserId: RESERVATION_OWNER_ID,
},
};
const claimed = await runWithRlsContext({ userId: RESERVATION_OWNER_ID }, () => claimReservedTenantWithShkDefaults(claimInput));
expect(claimed).toMatchObject({
replayed: false,
tenant: { id: tenantId, active: true, deactivatedReason: null },
user: { id: RESERVATION_OWNER_ID, email: RESERVATION_OWNER_EMAIL, defaultTenantId: tenantId },
});
const replay = await runWithRlsContext({ userId: RESERVATION_OWNER_ID }, () =>
claimReservedTenantWithShkDefaults({
tenantId,
userId: RESERVATION_OWNER_ID,
t: translate,
claim: {
kind: "workspace-invite",
inviteId: reservedOwnerInviteId!,
token: RESERVATION_OWNER_INVITE_TOKEN,
createdUserId: RESERVATION_OWNER_ID,
},
})
);
expect(replay).toMatchObject({ replayed: true, tenant: { id: tenantId, active: true }, user: { id: RESERVATION_OWNER_ID } });
const snapshot = await readBootstrapSnapshot(tenantId, RESERVATION_OWNER_ID);
expect(snapshot.memberships).toEqual([
{
tenantId,
userId: RESERVATION_OWNER_ID,
type: 0,
joined: 0,
status: 2,
},
]);
expect(snapshot.assignments).toEqual([
{
tenantId,
userId: RESERVATION_OWNER_ID,
role: { tenantId: null, name: "Admin", type: "app" },
},
]);
expect(snapshot.roles).toHaveLength(5);
expect(snapshot.pipelines).toHaveLength(3);
expect(snapshot.markers).toHaveLength(1);
const invitation = await admin.query<{
status: string;
pending: boolean;
acceptedByUserId: string | null;
}>('SELECT status, pending, "acceptedByUserId" FROM "TenantUserInvitation" WHERE id = $1', [reservedOwnerInviteId]);
expect(invitation.rows).toEqual([{ status: "accepted", pending: false, acceptedByUserId: RESERVATION_OWNER_ID }]);
});
it("rolls back a newly created owner, invite binding, and defaults when the late claim bootstrap fails", async () => {
const fixture = await reserveOwnerlessFixture({
requestKey: CLAIM_ROLLBACK_REQUEST_KEY,
slug: CLAIM_ROLLBACK_SLUG,
email: CLAIM_ROLLBACK_EMAIL,
token: CLAIM_ROLLBACK_TOKEN,
});
claimRollbackTenantId = fixture.tenantId;
const rollbackPasswordHash = await hashPassword("Live-RLS-rollback-owner-46!");
await admin.query('UPDATE "Onboarding" SET active = false WHERE id = $1', [SHK_FIRST_STEPS_CHECKLIST_ID]);
try {
await expect(
runWithRlsContext({ userId: CLAIM_ROLLBACK_USER_ID }, () =>
claimReservedTenantWithShkDefaults({
tenantId: fixture.tenantId,
userId: CLAIM_ROLLBACK_USER_ID,
t: translate,
createUser: {
id: CLAIM_ROLLBACK_USER_ID,
email: CLAIM_ROLLBACK_EMAIL,
passwordHash: rollbackPasswordHash,
firstName: "Romy",
lastName: "Rollback",
locale: "de",
},
claim: {
kind: "workspace-invite",
inviteId: fixture.inviteId,
token: CLAIM_ROLLBACK_TOKEN,
createdUserId: CLAIM_ROLLBACK_USER_ID,
},
})
)
).rejects.toThrow();
} finally {
await admin.query('UPDATE "Onboarding" SET active = true WHERE id = $1', [SHK_FIRST_STEPS_CHECKLIST_ID]);
}
const state = await admin.query<{
active: boolean;
deactivatedReason: string | null;
status: string;
pending: boolean;
createdUserId: string | null;
users: string;
memberships: string;
markers: string;
}>(
`SELECT tenant.active,
tenant."deactivatedReason",
invitation.status,
invitation.pending,
invitation."createdUserId",
(SELECT count(*)::text FROM "User" WHERE id = $2) AS users,
(SELECT count(*)::text FROM "TenantUser" WHERE "tenantId" = tenant.id) AS memberships,
(SELECT count(*)::text FROM "IdempotencyKey" WHERE "tenantId" = tenant.id AND scope = $3) AS markers
FROM "Tenant" AS tenant
JOIN "TenantUserInvitation" AS invitation ON invitation."tenantId" = tenant.id
WHERE tenant.id = $1 AND invitation.id = $4`,
[fixture.tenantId, CLAIM_ROLLBACK_USER_ID, SHK_V2_BOOTSTRAP_SCOPE, fixture.inviteId]
);
expect(state.rows).toEqual([
{
active: false,
deactivatedReason: "pending_owner",
status: "pending",
pending: true,
createdUserId: null,
users: "0",
memberships: "0",
markers: "0",
},
]);
});
it("allows exactly one newly created owner when parallel claims race under restricted RLS", async () => {
const fixture = await reserveOwnerlessFixture({
requestKey: CLAIM_RACE_REQUEST_KEY,
slug: CLAIM_RACE_SLUG,
email: CLAIM_RACE_EMAIL,
token: CLAIM_RACE_TOKEN,
});
claimRaceTenantId = fixture.tenantId;
const passwordHash = await hashPassword("Live-RLS-race-owner-47!");
const attempts = await Promise.allSettled(
CLAIM_RACE_USER_IDS.map((userId) =>
runWithRlsContext({ userId }, () =>
claimReservedTenantWithShkDefaults({
tenantId: fixture.tenantId,
userId,
t: translate,
createUser: {
id: userId,
email: CLAIM_RACE_EMAIL,
passwordHash,
firstName: "Raya",
lastName: "Race",
locale: "de",
},
claim: {
kind: "workspace-invite",
inviteId: fixture.inviteId,
token: CLAIM_RACE_TOKEN,
createdUserId: userId,
},
})
)
)
);
const fulfilled = attempts.filter((attempt): attempt is PromiseFulfilledResult<TenantBootstrapResult> => attempt.status === "fulfilled");
const rejected = attempts.filter((attempt): attempt is PromiseRejectedResult => attempt.status === "rejected");
expect(fulfilled).toHaveLength(1);
expect(rejected).toHaveLength(1);
const winnerId = fulfilled[0]?.value.user.id;
expect(CLAIM_RACE_USER_IDS).toContain(winnerId);
const state = await admin.query<{
active: boolean;
status: string;
acceptedByUserId: string | null;
userIds: string[];
membershipUserIds: string[];
}>(
`SELECT tenant.active,
invitation.status,
invitation."acceptedByUserId",
ARRAY(SELECT id FROM "User" WHERE id = ANY($2::text[]) ORDER BY id) AS "userIds",
ARRAY(SELECT "userId" FROM "TenantUser" WHERE "tenantId" = tenant.id ORDER BY "userId") AS "membershipUserIds"
FROM "Tenant" AS tenant
JOIN "TenantUserInvitation" AS invitation ON invitation."tenantId" = tenant.id
WHERE tenant.id = $1 AND invitation.id = $3`,
[fixture.tenantId, CLAIM_RACE_USER_IDS, fixture.inviteId]
);
expect(state.rows).toEqual([
{
active: true,
status: "accepted",
acceptedByUserId: winnerId,
userIds: [winnerId],
membershipUserIds: [winnerId],
},
]);
});
it("rolls back the late bootstrap failure without leaving a tenant or user", async () => {
const traceCountBefore = await admin.query<{ count: string }>(`SELECT count(*)::text AS count FROM public.${TRACE_TABLE}`);
await admin.query('UPDATE "Onboarding" SET active = false WHERE id = $1', [SHK_FIRST_STEPS_CHECKLIST_ID]);
try {
await expect(
createSignupTenantWithShkDefaults({
email: ROLLBACK_EMAIL,
password: "Live-RLS-password-43!",
firstName: "Nina",
lastName: "Rollback",
company: "RLS Product Bootstrap Rollback",
slug: ROLLBACK_SLUG,
locale: "de",
creationRequestKey: ROLLBACK_CREATION_REQUEST_KEY,
t: translate,
})
).rejects.toThrow();
} finally {
await admin.query('UPDATE "Onboarding" SET active = true WHERE id = $1', [SHK_FIRST_STEPS_CHECKLIST_ID]);
}
const tenantRows = await admin.query<{ id: string }>('SELECT id FROM "Tenant" WHERE slug LIKE $1', [`${ROLLBACK_SLUG}%`]);
const userRows = await admin.query<{ id: string }>('SELECT id FROM "User" WHERE email = $1', [ROLLBACK_EMAIL]);
const traceCountAfter = await admin.query<{ count: string }>(`SELECT count(*)::text AS count FROM public.${TRACE_TABLE}`);
expect(tenantRows.rows).toEqual([]);
expect(userRows.rows).toEqual([]);
expect(traceCountAfter.rows[0]?.count).toBe(traceCountBefore.rows[0]?.count);
});
});
async function reserveOwnerlessFixture(input: {
requestKey: string;
slug: string;
email: string;
token: string;
}): Promise<{ tenantId: string; inviteId: string }> {
const tenant = await runWithRlsContext({ tenantId: FOREIGN_TENANT_ID, userId: FOREIGN_USER_ID }, () =>
reserveTenantWithoutOwner({
name: `RLS Ownerless ${input.slug}`,
slug: input.slug,
operatorUserId: FOREIGN_USER_ID,
reservationRequestKey: input.requestKey,
ownerInvitation: {
issuanceKey: input.requestKey,
email: input.email,
firstName: input.slug === CLAIM_ROLLBACK_SLUG ? "Romy" : "Raya",
lastName: input.slug === CLAIM_ROLLBACK_SLUG ? "Rollback" : "Race",
fromUserId: FOREIGN_USER_ID,
token: input.token,
mail: {
subject: "RLS product owner invitation",
html: `<a href="https://app.example.test/invitation/${input.token}">Accept invitation</a>`,
},
},
})
);
const invitation = await admin.query<{ id: string }>(
`SELECT id FROM "TenantUserInvitation" WHERE "tenantId" = $1 AND type = 0 AND status = 'pending' ORDER BY id`,
[tenant.id]
);
const inviteId = invitation.rows[0]?.id;
if (!inviteId || invitation.rows.length !== 1) {
throw new Error("Expected exactly one pending owner invitation fixture");
}
return { tenantId: tenant.id, inviteId };
}
async function readBootstrapSnapshot(tenantId: string, userId: string) {
return runWithRlsContext({ tenantId, userId }, () =>
db.$transaction(async (tx) => ({
tenant: await tx.tenant.findUnique({ where: { id: tenantId }, select: { id: true, active: true, deactivatedReason: true } }),
user: await tx.user.findUnique({ where: { id: userId }, select: { id: true, active: true, defaultTenantId: true } }),
memberships: await tx.tenantUser.findMany({
where: { tenantId, userId },
select: { tenantId: true, userId: true, type: true, joined: true, status: true },
}),
assignments: await tx.userRole.findMany({
where: { tenantId, userId },
select: { tenantId: true, userId: true, role: { select: { tenantId: true, name: true, type: true } } },
}),
roles: await tx.role.findMany({
where: { tenantId },
orderBy: { order: "asc" },
select: { name: true, type: true, assignToNewUsers: true, isDefault: true, isTemplateOrigin: true },
}),
pipelines: await tx.pipeline.findMany({
where: { tenantId },
orderBy: { position: "asc" },
select: {
name: true,
isDefault: true,
stages: { orderBy: { position: "asc" }, select: { name: true, stageKind: true, winProbability: true } },
},
}),
forms: await tx.form.findMany({
where: { tenantId },
orderBy: { name: "asc" },
select: { name: true, slug: true, subtype: true, status: true, isDemoData: true },
}),
acceptanceTemplates: await tx.acceptanceFormTemplate.findMany({
where: { tenantId },
orderBy: { name: "asc" },
select: { name: true, isDefault: true, isDemoData: true },
}),
projectTemplates: await tx.projectTemplate.findMany({
where: { tenantId },
orderBy: { name: "asc" },
select: { name: true, isDemoData: true },
}),
checklistSessions: await tx.onboardingSession.findMany({
where: { tenantId, userId },
select: {
status: true,
onboarding: { select: { id: true } },
sessionSteps: {
orderBy: { step: { order: "asc" } },
select: { step: { select: { id: true, order: true, block: true } } },
},
},
}),
automations: await tx.automationTemplate.findMany({
where: { tenantId },
orderBy: { name: "asc" },
select: { name: true, isPublished: true, flowData: true },
}),
markers: await tx.idempotencyKey.findMany({
where: { tenantId, scope: SHK_V2_BOOTSTRAP_SCOPE, key: SHK_V2_TENANT_BOOTSTRAP_CONFIG.templateKey },
select: { scope: true, key: true, requestHash: true, responseStatus: true, responseBody: true },
}),
}))
);
}
async function readForeignSnapshot(targetTenantId: string, targetUserId: string) {
return runWithRlsContext({ tenantId: FOREIGN_TENANT_ID, userId: FOREIGN_USER_ID }, () =>
db.$transaction(async (tx) => ({
tenants: await tx.tenant.findMany({ where: { id: targetTenantId }, select: { id: true } }),
users: await tx.user.findMany({ where: { id: targetUserId }, select: { id: true } }),
memberships: await tx.tenantUser.findMany({ where: { tenantId: targetTenantId }, select: { id: true } }),
assignments: await tx.userRole.findMany({ where: { tenantId: targetTenantId }, select: { id: true } }),
roles: await tx.role.findMany({ where: { tenantId: targetTenantId }, select: { id: true } }),
pipelines: await tx.pipeline.findMany({ where: { tenantId: targetTenantId }, select: { id: true } }),
stages: await tx.pipelineStage.findMany({ where: { tenantId: targetTenantId }, select: { id: true } }),
forms: await tx.form.findMany({ where: { tenantId: targetTenantId }, select: { id: true } }),
acceptanceTemplates: await tx.acceptanceFormTemplate.findMany({ where: { tenantId: targetTenantId }, select: { id: true } }),
projectTemplates: await tx.projectTemplate.findMany({ where: { tenantId: targetTenantId }, select: { id: true } }),
checklistSessions: await tx.onboardingSession.findMany({ where: { tenantId: targetTenantId }, select: { id: true } }),
automations: await tx.automationTemplate.findMany({ where: { tenantId: targetTenantId }, select: { id: true } }),
markers: await tx.idempotencyKey.findMany({ where: { tenantId: targetTenantId }, select: { id: true } }),
}))
);
}
async function provisionGlobalPrerequisites(): Promise<void> {
const adminRoles = await admin.query<{ id: string }>('SELECT id FROM "Role" WHERE "tenantId" IS NULL AND name = $1 AND type = $2 ORDER BY id', [
"Admin",
"app",
]);
if (adminRoles.rows.length === 0) {
await admin.query(
`INSERT INTO "Role" (id, "updatedAt", "tenantId", name, description, type, "assignToNewUsers", "isDefault", "order")
VALUES ($1, now(), NULL, 'Admin', 'RLS product bootstrap global Admin app role', 'app', false, false, 1)`,
[TEST_ADMIN_ROLE_ID]
);
insertedAdminRole = true;
} else if (adminRoles.rows.length !== 1) {
throw new Error(`Expected exactly one global Admin app role, found ${adminRoles.rows.length}`);
}
const permissionKeys = [...new Set(SHK_V2_TENANT_BOOTSTRAP_CONFIG.tenantRoles.flatMap((role) => role.permissionKeys))];
for (const [index, name] of permissionKeys.entries()) {
const id = `rls_product_bootstrap_permission_${String(index).padStart(3, "0")}`;
const inserted = await admin.query<{ id: string }>(
`INSERT INTO "Permission" (id, "updatedAt", name, description, type, "isDefault", "order")
VALUES ($1, now(), $2, 'RLS product bootstrap permission fixture', 'app', false, $3)
ON CONFLICT (name) DO NOTHING
RETURNING id`,
[id, name, 20_000 + index]
);
if (inserted.rows[0]?.id === id) insertedPermissionIds.push(id);
}
const checklist = await admin.query<{ title: string; active: boolean }>('SELECT title, active FROM "Onboarding" WHERE id = $1', [
SHK_FIRST_STEPS_CHECKLIST_ID,
]);
if (checklist.rows.length === 0) {
await admin.query(
`INSERT INTO "Onboarding" (id, "updatedAt", title, type, realtime, active, "canBeDismissed")
VALUES ($1, now(), $2, 'page', false, true, true)`,
[SHK_FIRST_STEPS_CHECKLIST_ID, SHK_FIRST_STEPS_CHECKLIST_CODE]
);
for (const step of SHK_FIRST_STEPS) {
await admin.query('INSERT INTO "OnboardingStep" (id, "onboardingId", "order", block) VALUES ($1, $2, $3, $4)', [
step.id,
SHK_FIRST_STEPS_CHECKLIST_ID,
step.order,
step.block,
]);
}
insertedChecklist = true;
} else {
if (checklist.rows.length !== 1 || checklist.rows[0]?.title !== SHK_FIRST_STEPS_CHECKLIST_CODE) {
throw new Error("The canonical SHK first-steps checklist has an incompatible identity");
}
originalChecklistActive = checklist.rows[0].active;
const steps = await admin.query<{ id: string; order: number; block: string }>(
'SELECT id, "order", block FROM "OnboardingStep" WHERE "onboardingId" = $1 ORDER BY "order"',
[SHK_FIRST_STEPS_CHECKLIST_ID]
);
expect(steps.rows).toEqual(SHK_FIRST_STEPS.map((step) => ({ ...step })));
await admin.query('UPDATE "Onboarding" SET active = true WHERE id = $1', [SHK_FIRST_STEPS_CHECKLIST_ID]);
}
}
async function provisionForeignTenant(): Promise<void> {
await admin.query(
`INSERT INTO "User" (id, "updatedAt", email, "firstName", "lastName", active, "defaultTenantId")
VALUES ($1, now(), 'rls-product-bootstrap-foreign@example.test', 'RLS', 'Foreign', true, $2)`,
[FOREIGN_USER_ID, FOREIGN_TENANT_ID]
);
await admin.query(
`INSERT INTO "Tenant" (id, "updatedAt", slug, name, active)
VALUES ($1, now(), 'rls-product-bootstrap-foreign', 'RLS Product Bootstrap Foreign', true)`,
[FOREIGN_TENANT_ID]
);
await admin.query(
`INSERT INTO "TenantUser" (id, "tenantId", "userId", type, joined, status)
VALUES ($1, $2, $3, 2, 0, 2)`,
[FOREIGN_MEMBERSHIP_ID, FOREIGN_TENANT_ID, FOREIGN_USER_ID]
);
}
async function cleanupStaleProductFixtures(): Promise<void> {
const staleReservations = await admin.query<{ id: string }>('SELECT id FROM "Tenant" WHERE slug = ANY($1::text[])', [
[RESERVATION_SLUG, CLAIM_ROLLBACK_SLUG, CLAIM_RACE_SLUG],
]);
for (const tenant of staleReservations.rows) {
await cleanupTenantRows(tenant.id, RESERVATION_OWNER_ID);
}
const staleTenants = await admin.query<{ id: string; user_id: string | null }>(
`SELECT tenant.id, owner.id AS user_id
FROM "Tenant" AS tenant
LEFT JOIN "User" AS owner ON owner."defaultTenantId" = tenant.id
WHERE tenant.slug LIKE $1 OR tenant.slug LIKE $2 OR tenant.id = $3`,
[`${SUCCESS_SLUG}%`, `${ROLLBACK_SLUG}%`, FOREIGN_TENANT_ID]
);
for (const tenant of staleTenants.rows) {
await cleanupTenantRows(tenant.id, tenant.user_id ?? FOREIGN_USER_ID);
}
await admin.query('DELETE FROM "User" WHERE email IN ($1, $2, $3) OR id = $4', [
SUCCESS_EMAIL,
ROLLBACK_EMAIL,
"rls-product-bootstrap-foreign@example.test",
FOREIGN_USER_ID,
]);
await admin.query('DELETE FROM "User" WHERE id = ANY($1::text[]) OR email = ANY($2::text[])', [
[RESERVATION_OWNER_ID, CLAIM_ROLLBACK_USER_ID, ...CLAIM_RACE_USER_IDS],
[RESERVATION_OWNER_EMAIL, CLAIM_ROLLBACK_EMAIL, CLAIM_RACE_EMAIL],
]);
await admin.query('DELETE FROM "Role" WHERE id = $1', [TEST_ADMIN_ROLE_ID]);
await admin.query('DELETE FROM "Permission" WHERE id LIKE $1', ["rls_product_bootstrap_permission_%"]);
}
async function cleanupTenantRows(tenantId: string, userId: string): Promise<void> {
await admin.query('DELETE FROM "OnboardingSessionStep" WHERE "onboardingSessionId" IN (SELECT id FROM "OnboardingSession" WHERE "tenantId" = $1)', [
tenantId,
]);
await admin.query('DELETE FROM "OnboardingSession" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "RolePermission" WHERE "roleId" IN (SELECT id FROM "Role" WHERE "tenantId" = $1)', [tenantId]);
await admin.query('DELETE FROM "UserRole" WHERE "tenantId" = $1 OR "userId" = $2', [tenantId, userId]);
await admin.query('DELETE FROM "PipelineStage" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "Pipeline" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "LostReason" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "AutomationTemplate" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "AcceptanceFormTemplate" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "ProjectTemplate" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "CustomFieldDefinition" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "Form" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "OutboxEvent" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "IdempotencyKey" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "TenantUser" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "Role" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "TenantInboundAddress" WHERE "tenantId" = $1', [tenantId]);
await admin.query('DELETE FROM "Tenant" WHERE id = $1', [tenantId]);
await admin.query('DELETE FROM "User" WHERE id = $1', [userId]);
}
async function createTraceInfrastructure(): Promise<void> {
await admin.query(`
CREATE TABLE public.${TRACE_TABLE} (
seq bigserial PRIMARY KEY,
tenant_id text NOT NULL,
event text NOT NULL
);
CREATE FUNCTION public.${TRACE_FUNCTION}()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
target_tenant_id text;
event_name text;
BEGIN
IF TG_TABLE_NAME = 'Tenant' THEN
target_tenant_id := NEW.id;
ELSIF TG_TABLE_NAME = 'User' THEN
target_tenant_id := NEW."defaultTenantId";
ELSIF TG_TABLE_NAME = 'OnboardingSessionStep' THEN
SELECT session."tenantId" INTO target_tenant_id
FROM public."OnboardingSession" AS session
WHERE session.id = NEW."onboardingSessionId";
ELSE
target_tenant_id := to_jsonb(NEW)->>'tenantId';
END IF;
IF target_tenant_id IS NULL OR NOT EXISTS (
SELECT 1 FROM public."Tenant" AS tenant
WHERE tenant.id = target_tenant_id
AND tenant.slug LIKE 'rls-product-bootstrap-%'
) THEN
RETURN NEW;
END IF;
event_name := TG_ARGV[0] || '.' || lower(TG_OP);
IF TG_TABLE_NAME = 'Tenant' AND TG_OP = 'UPDATE' THEN
IF NEW.active AND NOT OLD.active THEN
event_name := 'tenant.activate';
END IF;
ELSIF TG_TABLE_NAME = 'IdempotencyKey' AND TG_OP = 'UPDATE' THEN
IF NEW."responseStatus" = 201 THEN
event_name := 'marker.complete';
END IF;
END IF;
INSERT INTO public.${TRACE_TABLE} (tenant_id, event)
VALUES (target_tenant_id, event_name);
RETURN NEW;
END;
$$;
REVOKE ALL ON TABLE public.${TRACE_TABLE} FROM PUBLIC;
REVOKE ALL ON FUNCTION public.${TRACE_FUNCTION}() FROM PUBLIC;
`);
const tracedTables = [
["Tenant", "tenant"],
["User", "user"],
["TenantUser", "membership"],
["UserRole", "assignment"],
["Role", "role"],
["Pipeline", "pipeline"],
["PipelineStage", "stage"],
["LostReason", "lost-reason"],
["Form", "form"],
["AcceptanceFormTemplate", "acceptance"],
["ProjectTemplate", "project-template"],
["OnboardingSession", "checklist-session"],
["OnboardingSessionStep", "checklist-step"],
["AutomationTemplate", "automation"],
["IdempotencyKey", "marker"],
] as const;
for (const [table, event] of tracedTables) {
await admin.query(
`CREATE TRIGGER rls_product_bootstrap_trace
AFTER INSERT OR UPDATE ON public."${table}"
FOR EACH ROW EXECUTE FUNCTION public.${TRACE_FUNCTION}('${event}')`
);
}
}
async function dropTraceInfrastructure(): Promise<void> {
if (!admin) return;
const tracedTables = [
"Tenant",
"User",
"TenantUser",
"UserRole",
"Role",
"Pipeline",
"PipelineStage",
"LostReason",
"Form",
"AcceptanceFormTemplate",
"ProjectTemplate",
"OnboardingSession",
"OnboardingSessionStep",
"AutomationTemplate",
"IdempotencyKey",
];
for (const table of tracedTables) {
await admin.query(`DROP TRIGGER IF EXISTS rls_product_bootstrap_trace ON public."${table}"`);
}
await admin.query(`DROP FUNCTION IF EXISTS public.${TRACE_FUNCTION}()`);
await admin.query(`DROP TABLE IF EXISTS public.${TRACE_TABLE}`);
}