Auditing

Record who changed what, in the same transaction as the change, so a change is never written without its audit entry or the other way round.

Setup

The code of this recipe depends on these declarations: the imports, the schema classes, the repo, and the migration that creates the tables. Run the recipe in an empty database of its own: each recipe creates its own tables, and some have the same names (posts) with other columns.

import { postgres } from "@egon-orm/postgres";
import { type Changeset, cast, change, createBatch, createRepo, from, Schema, t, timestamps } from "egon-orm";
import { createMigrator, defineMigration } from "egon-orm/migration";

const repo = createRepo({ adapter: postgres({ connectionString: process.env.DATABASE_URL }) });

class Account extends Schema("accounts", {
  id: t.integer().primaryKey().dbGenerated(),
  email: t.string(),
  plan: t.enum(["free", "pro"]).default("free"),
}) {
  static changeset(account: Account | typeof Account, params: unknown) {
    return cast(account, params, ["email", "plan"]).validateRequired(["email"]);
  }
}

// The tables, created by a migration.
const tables = defineMigration({
  up(m) {
    m.createTable("accounts", (table) => {
      table.id({ type: "integer" });
      table.text("email").notNull();
      table.text("plan").notNull().default("free");
    });
    m.createTable("audit_entries", (table) => {
      table.id({ type: "integer" });
      table.integer("actor_id").notNull();
      table.text("action").notNull();
      table.text("table_name").notNull();
      table.integer("row_id").notNull();
      table.jsonb("changes").notNull();
      table.timestamps();
    });
  },
  down(m) {
    m.dropTable("audit_entries");
    m.dropTable("accounts");
  },
});

/** Creates the tables, in the database that DATABASE_URL names. */
export async function createTables(): Promise<void> {
  const admin = postgres({ connectionString: process.env.DATABASE_URL });
  await createMigrator({
    adapter: admin,
    migrations: [{ version: 20261021000101, name: "auditing_tables", migration: tables }],
  }).up();
  await admin.close();
}

The audit table

class AuditEntry extends Schema("audit_entries", {
  id: t.integer().primaryKey().dbGenerated(),
  actorId: t.integer(),
  action: t.string(),
  table: t.string().column("table_name"),
  rowId: t.integer(),
  changes: t.json<Record<string, unknown>>(), // what the changeset changed
  ...timestamps(),
}) {}

A write and its entry, as one batch

// An update and its audit entry, as one batch: both are written, or neither.
export function auditedUpdate(actorId: number, changeset: Changeset<typeof Account, false>) {
  return createBatch()
    .update("row", changeset)
    .insert("audit", ({ row }) =>
      change(AuditEntry, {
        actorId,
        action: "update",
        table: "accounts",
        rowId: row.id,
        changes: { ...changeset.changes },
      }),
    );
}

• The second step receives the first one's result by name (row), fully typed.
• changeset.changes holds only what the changeset changed, already converted to the fields' types, so the entry records the change and not the whole row.
• If either step fails, both are rolled back.

The examples below start from these rows, after creating the tables:

await createTables();
const account = await repo.insertOrThrow(Account.changeset(Account, { email: "ada@example.com" }));
const adminId = 7;

const result = await repo.transaction(auditedUpdate(adminId, Account.changeset(account, { plan: "pro" })));

if (!result.success) {
  result.error.step; // "row" | "audit": which write failed, and nothing was written
}

The batch is a value: build it in one place (a service function) and run it with repo.transaction, or append it to a larger batch with append.

Reading the history

const history = await repo.all(
  from(AuditEntry, "e")
    .where({ table: "accounts", rowId: account.id })
    .orderBy(({ e }) => e.createdAt.desc()),
);

Good to know

• Redacted fields are in changes like any other: leave them out of the entry (copy the keys you want) if the audit table must not hold them.
• Bulk writes (updateAll, deleteAll) take no changeset; audit them with a run step that records the query's parameters, or with a database trigger.
• A database trigger is the other way to audit: it also sees writes that do not go through the application. This recipe records the actor, which a trigger does not know.

See also

• Batch
• Changesets