Open-source AI coworkers that each get a computer of their own: a browser, files and tools, with every action decided before it happens and recorded after. Bring any AG-UI agent.
import { afterEach, describe, expect, test } from "bun:test";
import { eq, sql } from "drizzle-orm";
import postgres from "postgres";
import { sweepAuditTrail } from "../src/audit-retention";
import { createDatabase } from "../src/db/client";
import { auditEvents } from "../src/db/schema";
import { TEST_POOL, testDatabaseUrl } from "./support/database";
/**
* The audit trail has to be able to stop growing.
*
* Every click, keystroke, scroll, tool call, refusal, command and sign-in is a row, and nothing in
* the product ever removed one. That is the largest table in the deployment within weeks of real
* use, and it is also a control an enterprise buyer asks about directly: "what is your retention
* policy" had no answer other than "everything, forever, and you cannot change it".
*
* Against a real database because the whole thing is SQL: the advisory lock that decides which
* server sweeps, the batching, and the interval arithmetic.
*/
const databaseUrl = testDatabaseUrl();
const database = createDatabase(databaseUrl, TEST_POOL);
/** The id column is a uuid, so the rows are found by their target rather than by a marker in the id. */
const MARKER = "retention-test";
async function event(daysAgo: number, index: number): Promise<void> {
await database.insert(auditEvents).values({
eventType: "computer.action_allowed",
targetType: MARKER,
targetId: `${MARKER}-${index}`,
payload: {},
createdAt: new Date(Date.now() - daysAgo * 86_400_000),
});
}
async function remaining(): Promise<number> {
const rows = await database
.select({ id: auditEvents.id })
.from(auditEvents)
.where(eq(auditEvents.targetType, MARKER));
return rows.length;
}
/*
* Cleaning up has to obey the same rule as everything else.
*
* A plain delete is refused, which is the guarantee working: the trail is append-only and the
* database enforces it. So the tests only ever create rows old enough for a one-day retention window
* to remove, and clean up by declaring that window the way the sweep does.
*/
afterEach(async () => {
await database.transaction(async (tx) => {
await tx.execute(
sql`select set_config('openbot.audit_retention_days', '1', true)`,
);
await tx.delete(auditEvents).where(eq(auditEvents.targetType, MARKER));
});
});
/**
* Whether a rejection was the append-only trigger.
*
* Drizzle wraps the Postgres error, so the outer message is "Failed query: ..." and the reason is on
* the cause. Asserting only that it rejected would pass on a typo in the table name.
*/
async function refusedAsAppendOnly(
work: () => Promise<unknown>,
): Promise<boolean> {
try {
await work();
return false;
} catch (error) {
let current: unknown = error;
for (let depth = 0; depth < 5 && current; depth += 1) {
if (
String((current as { message?: string }).message ?? "").includes(
"append-only",
)
) {
return true;
}
current = (current as { cause?: unknown }).cause;
}
return false;
}
}
describe("keeping the audit trail to a retention policy", () => {
test("removes what is older than the window and keeps the rest", async () => {
await event(400, 1);
await event(100, 2);
await event(10, 3);
await event(2, 4);
const { deleted } = await sweepAuditTrail(databaseUrl, 90);
expect(deleted).toBe(2);
expect(await remaining()).toBe(2);
});
test("keeps everything when no policy is set", async () => {
// The default, and the safe one. Deleting somebody's trail because a default said so is worse
// than a table that grows, so an unset or nonsense window sweeps nothing at all.
await event(400, 1);
for (const window of [0, -1, Number.NaN]) {
const { deleted } = await sweepAuditTrail(databaseUrl, window);
expect(deleted).toBeNull();
}
expect(await remaining()).toBe(1);
});
test("the database refuses a malformed window even when the caller does not", async () => {
/*
* The test above stops in TypeScript. `sweepAuditTrail` returns `{deleted: null}` before it ever
* opens a connection, so it proves the caller's guard and says nothing about the trigger, which
* has a branch of its own for this that nothing exercised. That is the shape where a guard reads
* as covered and is not: anything reaching the table without going through the sweep meets the
* trigger and only the trigger.
*
* These set the value the sweep would have set and then delete directly.
*
* Reported by @beardthelion.
*/
await event(400, 1);
for (const window of ["0", "-1", "", "garbage"]) {
expect(
await refusedAsAppendOnly(() =>
database.transaction(async (tx) => {
await tx.execute(
sql`select set_config('openbot.audit_retention_days', ${window}, true)`,
);
await tx
.delete(auditEvents)
.where(eq(auditEvents.targetType, MARKER));
}),
),
).toBe(true);
}
expect(await remaining()).toBe(1);
});
test("a row exactly inside the window survives", async () => {
// The boundary. Off by a day here means a deployment promising 90 days keeps 89.
await event(89, 1);
await sweepAuditTrail(databaseUrl, 90);
expect(await remaining()).toBe(1);
});
test("only one server sweeps at a time", async () => {
/*
* N servers running the same delete over the same rows every hour is N times the work for one
* outcome, on the largest table in the deployment and the one every action writes to. The
* advisory lock decides. A second sweep while one holds it reports null rather than zero, so a
* caller can tell "somebody else is doing it" from "there was nothing to do".
*/
await event(400, 1);
/*
* Its own connection, standing in for the other server. A session advisory lock belongs to one
* connection and is re-entrant within it, so taking it through the shared pool would prove
* nothing: the sweep might be handed the same connection and sail through.
*/
const otherServer = postgres(databaseUrl, { max: 1 });
try {
const [lock] = await otherServer`
select pg_try_advisory_lock(4192004) as held
`;
expect(lock?.held).toBe(true);
const { deleted } = await sweepAuditTrail(databaseUrl, 90);
expect(deleted).toBeNull();
expect(await remaining()).toBe(1);
} finally {
await otherServer.end({ timeout: 5 }).catch(() => undefined);
}
});
test("a recent row cannot be deleted, whatever the window says", async () => {
/*
* The line retention must not cross. "We deleted the rows about the incident" has to stay
* impossible, so the trigger refuses any row inside the declared window rather than trusting
* the statement to only ask for old ones.
*/
await event(2, 1);
expect(
await refusedAsAppendOnly(() =>
database.transaction(async (tx) => {
await tx.execute(
sql`select set_config('openbot.audit_retention_days', '3650', true)`,
);
await tx
.delete(auditEvents)
.where(eq(auditEvents.targetType, MARKER));
}),
),
).toBe(true);
expect(await remaining()).toBe(1);
});
test("a row can never be edited, under any setting", async () => {
// Retention removes history; it does not rewrite it. An UPDATE is refused before the window is
// even considered.
await event(400, 1);
expect(
await refusedAsAppendOnly(() =>
database.transaction(async (tx) => {
await tx.execute(
sql`select set_config('openbot.audit_retention_days', '1', true)`,
);
await tx
.update(auditEvents)
.set({ payload: { tampered: true } })
.where(eq(auditEvents.targetType, MARKER));
}),
),
).toBe(true);
});
test("a plain delete with no policy declared is still refused", async () => {
// What anybody reaching the table without going through the sweep gets, which is the case the
// trigger exists for.
await event(400, 1);
expect(
await refusedAsAppendOnly(async () => {
await database
.delete(auditEvents)
.where(eq(auditEvents.targetType, MARKER));
}),
).toBe(true);
expect(await remaining()).toBe(1);
});
/*
* TRUNCATE, which for a while was the one statement that emptied this table and raised nothing.
*
* The append-only guarantee was a row-level trigger, and a row-level trigger cannot fire on a
* statement that removes rows without visiting them. So UPDATE and DELETE were refused while
* `TRUNCATE audit_events` took the lot and reported success.
*
* The transaction always throws, so it always rolls back. That is not tidiness: if this ever
* regresses, a committed truncate here would destroy the audit trail of whatever database the
* suite is pointed at, and the run that discovers the bug must not also be the run that proves
* how bad it is. A truncate that goes through leaves the sentinel on the cause chain instead of
* the refusal, so the assertion fails with the table still whole.
*/
const ROLLED_BACK = "rolled back on purpose, never committed";
test("the whole table cannot be truncated", async () => {
await event(400, 1);
expect(
await refusedAsAppendOnly(() =>
database.transaction(async (tx) => {
await tx.execute(sql`truncate table audit_events`);
throw new Error(ROLLED_BACK);
}),
),
).toBe(true);
expect(await remaining()).toBe(1);
});
test("a declared retention window does not open the door to it", async () => {
/*
* The failure mode of the obvious fix, and the reason the trigger answers TG_OP before it reads
* anything. Pointing a statement-level trigger at a function that tests `OLD.created_at`
* compares against NULL, because a statement trigger has no OLD record, and falls straight
* through to the return that lets the statement proceed.
*
* That path opens exactly while a retention sweep has the window set, so a happy-path check
* with no setting would report the guard working and miss it entirely. Both states are checked
* here, and this is the one that matters.
*/
await event(400, 1);
expect(
await refusedAsAppendOnly(() =>
database.transaction(async (tx) => {
await tx.execute(
sql`select set_config('openbot.audit_retention_days', '30', true)`,
);
await tx.execute(sql`truncate table audit_events`);
throw new Error(ROLLED_BACK);
}),
),
).toBe(true);
expect(await remaining()).toBe(1);
});
test("the lock is released, so the next sweep can run", async () => {
// A sweep that kept the lock would be the last one this deployment ever ran.
await event(400, 1);
await sweepAuditTrail(databaseUrl, 90);
await event(400, 2);
const { deleted } = await sweepAuditTrail(databaseUrl, 90);
expect(deleted).toBe(1);
});
});