Skip to content

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.

11.0 KB
Raw Blame
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);
  });
});