Drizzle Ledger

Spread softDeleteColumns into any table definition. Adds deleted_at (timestamp) and deleted_by (text). Dialect-specific types: integer timestamp for SQLite, timestamptz for Postgres, timestamp for MySQL.

Add to a Table

import { sqliteTable, text } from "drizzle-orm/sqlite-core";
import { softDeleteColumns } from "@ezmode-games/drizzle-ledger/soft-delete/sqlite";

export const users = sqliteTable("users", {
  id: text("id").primaryKey(),
  name: text("name"),
  email: text("email").unique(),
  ...softDeleteColumns,
});

// Adds: deleted_at (timestamp), deleted_by (text)

Query Helpers

import { notDeleted, onlyDeleted } from "@ezmode-games/drizzle-ledger/soft-delete";

// Active records only
const active = await db
  .select()
  .from(users)
  .where(notDeleted(users));

// Trash view / admin dashboard
const deleted = await db
  .select()
  .from(users)
  .where(onlyDeleted(users));

Dialect Imports

Also Available

createAuditedDb(db) intercepts db.delete() and rewrites it to a soft-delete UPDATE for any table with a deleted_at column. Tables without it pass through to a normal hard delete. The right thing happens by default.

import { createAuditedDb } from "@ezmode-games/drizzle-ledger/db";

const db = createAuditedDb(drizzle(env.DB), {
  hardDeleteTables: ["session", "verification"],
});

// This becomes a soft-delete (users has deleted_at)
await db.delete(users).where(eq(users.id, userId));

// This is a real delete (session is in hardDeleteTables)
await db.delete(session).where(eq(session.id, sessionId));

How It Works

• Table has deleted_at? Soft-delete (UPDATE with timestamp)
• Table in hardDeleteTables? Hard delete (normal DELETE)
• No deleted_at column? Hard delete (pass-through)
• .where(), .returning(), .execute() all work as normal

Every mutation records who changed what record in which table, when, from where (IP, user agent, endpoint), with before and after state as JSON snapshots.

Context Propagation

Set context once in middleware via AsyncLocalStorage. Every audit entry downstream automatically inherits userId, IP, user agent, endpoint, and request ID. No prop drilling.

import { createLedgerContext, runWithLedgerContext } from "@ezmode-games/drizzle-ledger/context";

app.use(async (c, next) => {
  const context = createLedgerContext({
    userId: c.get("user")?.id,
    ip: c.req.header("x-forwarded-for"),
    userAgent: c.req.header("user-agent"),
    endpoint: `${c.req.method} ${c.req.path}`,
  });
  return runWithLedgerContext(context, next);
});

Works in Node.js, Bun, Deno, and Cloudflare Workers.

Manual Logging

import { logInsert, logUpdate } from "@ezmode-games/drizzle-ledger/audit";

// After inserting
const [user] = await db.insert(users).values(data).returning();
await logInsert(db, auditLog, "users", user.id, user);

// After updating
const [oldUser] = await db.select().from(users).where(eq(users.id, id));
const [newUser] = await db.update(users).set(changes)
  .where(eq(users.id, id)).returning();
await logUpdate(db, auditLog, "users", id, oldUser, newUser);

Automatic Logging

Plug into Drizzle's logger interface. Intercepts INSERT/UPDATE/DELETE queries automatically.

import { AuditLogger } from "@ezmode-games/drizzle-ledger/logger";

const logger = new AuditLogger(
  async (entry) => {
    await db.insert(auditLog).values({ ...entry, id: uuidv7() });
  },
  { excludeTables: ["audit_log", "session"] }
);

const db = drizzle(env.DB, { logger });

SOC 2 Coverage

A user exercises their right to erasure (Article 17). You need to remove their PII from the audit trail without destroying the trail itself. purgeUserData anonymizes their entries. The structure stays intact. The PII goes away.

import { purgeUserData, isUserDataPurged } from "@ezmode-games/drizzle-ledger/gdpr";

const result = await purgeUserData(db, auditLog, "user-123", {
  piiFields: ["email", "name", "phone", "address", "ip"],
  anonymizedUserId: "PURGED_USER",
});
// { entriesAnonymized: 47, tablesProcessed: ["users", "accounts"] }

// Idempotency check
const purged = await isUserDataPurged(db, auditLog, "user-123");

What It Does

• Finds all audit entries for the user (by userId and recordId)
• Recursively strips configured PII fields from JSON snapshots
• Replaces userId with PURGED_USER on user's own entries
• Nullifies IP and userAgent on user's own entries
• Preserves admin entries untouched - admin userId/IP stays
• Idempotent - safe to run multiple times

GDPR Coverage

Hooks into Better Auth's databaseHooks to automatically log user/account operations. The soft-delete callback intercepts deleteUser to perform soft-delete instead of hard delete.

import { ledgerPlugin, createSoftDeleteCallback } from "@ezmode-games/drizzle-ledger/better-auth";

export const auth = betterAuth({
  user: {
    deleteUser: {
      enabled: true,
      beforeDelete: createSoftDeleteCallback({
        db,
        userTable: users,
        whereUserId: (userId) => eq(users.id, userId),
        writeAuditEntry: async (entry) => {
          await db.insert(auditLog).values({ ...entry, id: uuidv7() });
        },
      }),
    },
  },
  plugins: [
    ledgerPlugin({
      writeAuditEntry: async (entry) => {
        await db.insert(auditLog).values({ ...entry, id: uuidv7() });
      },
      auditTables: ["user", "account"],
    }),
  ],
});

Detection

createSoftDeleteCallback performs the soft-delete, logs an audit entry, then throws to prevent the hard delete. Use isSoftDeletePerformed(error) to detect success in your client code.