Audit-Safe Admin Tools with Event Sourcing

On the workflow platform I work on, the delivery team often needs to move many tasks through the same gate at once—marking batches as passed, failed, or sent back for review. For a long time, the practical answer was to ask an engineer to run ad-hoc SQL against production. That pattern is fast until it is not: there is no durable audit trail tied to a human decision, no shared validation of whether a transition is even legal for each row, and no structured way to explain why something changed. When something looks wrong later, you are left grepping logs and guessing. Worse, every bulk change becomes an engineering bottleneck, because only people with database access can safely touch the data.

This post is about replacing that with a single admin API: preview first, execute second, each task updated inside an atomic database transaction alongside an append-only event, identity and authorization enforced with Firebase, and an admin panel UI that makes the operation legible before anyone commits.

The problem in one sentence

Bulk state changes are a product need; raw production SQL is the wrong abstraction for them because it skips validation, authorization, and auditability by default.

One endpoint, two modes

The fix is not “more discipline around scripts.” It is to give operators a first-class tool that behaves like production software. The API exposes one route with an explicit mode flag—typically preview or execute—and both paths run the same validation pipeline.

In preview mode, the handler loads the candidate tasks, checks inputs and state-machine rules, and returns a structured summary of what would happen: current status, proposed status, per-task errors or warnings, and counts. It does not open a write transaction for the actual updates. In execute mode, after the identical validation passes, the server performs the writes. If validation fails, neither mode mutates task rows.

The important invariant is symmetry: preview is not a toy path that drifts from execute. If preview says a task is eligible, execute should accept it under the same code; if preview rejects a task, execute must reject it for the same reason. That is how you earn trust from the delivery team and from future you reading incident notes.

type BulkMode = "preview" | "execute";

async function bulkTaskStatusChange(
  body: BulkTaskStatusBody,
  mode: BulkMode,
  ctx: RequestContext
) {
  const validation = await validateBulkPayload(body, ctx);
  if (!validation.ok) return validation.errorResponse();

  if (mode === "preview") {
    return { mode, dryRun: true, plan: validation.plan };
  }

  const results = [];
  for (const item of validation.plan.items) {
    results.push(await applyTaskChangeInTransaction(prisma, item, ctx));
  }
  return { mode, dryRun: false, results };
}

In practice you may batch internal transaction scope differently depending on load and lock characteristics; the conceptual split—shared validation, conditional commit—stays the same.

Atomic per-task transactions

The scariest failure mode for bulk tools is partial success: half the tasks show a new status in the UI while the other half failed mid-flight, or worse, a row updates but the audit row never lands. Operators cannot reason about that state without deep database forensics.

Each logical unit of work should be atomic: updating the task’s status and inserting the corresponding event succeed together or not at all. With Prisma, wrapping the pair in $transaction gives you that guarantee at the database level for each task.

async function applyTaskChangeInTransaction(
  prisma: PrismaClient,
  item: PlannedTaskChange,
  ctx: RequestContext
) {
  return prisma.$transaction(async (tx) => {
    const updated = await tx.task.update({
      where: { id: item.taskId },
      data: { status: item.nextStatus, updatedAt: new Date() },
    });

    await tx.taskEvent.create({
      data: buildTaskEventRecord({
        taskId: item.taskId,
        previousStatus: item.previousStatus,
        newStatus: item.nextStatus,
        actor: ctx.actor,
        reason: item.reason,
        metadata: item.mergedMetadata,
        source: "admin_bulk_status_change",
      }),
    });

    return updated;
  });
}

If the event insert fails, the status update rolls back for that task. If the update fails, no orphan event appears. That property is what makes the event log trustworthy as a history—not a best-effort side channel.

Event sourcing the task lifecycle

Rather than treating the task row as the only source of truth, every meaningful transition appends a row to task_events (names vary, the shape matters). The event captures enough context to reconstruct narrative and to support compliance-style questions later: who did it, from where in the product, what changed, and why.

Each record includes a stable source string—for bulk admin work, something like admin_bulk_status_change—so analytics and support can filter noise from migrations or system jobs. It stores previousStatus and newStatus, the authenticated actor identity resolved from Firebase (see below), the admin’s free-text reason, and metadata merged from prior events so contextual fields—review identifiers, feedback snippets, routing hints—are not lost when status moves again.

type TaskEventPayload = {
  taskId: string;
  source: string;
  previousStatus: TaskStatus;
  newStatus: TaskStatus;
  actorUid: string;
  actorEmail?: string;
  reason: string;
  metadata: Record<string, unknown>;
  createdAt: Date;
};

function buildTaskEventRecord(input: {
  taskId: string;
  previousStatus: TaskStatus;
  newStatus: TaskStatus;
  actor: ActorClaims;
  reason: string;
  metadata: Record<string, unknown>;
  source: string;
}): TaskEventPayload {
  return {
    taskId: input.taskId,
    source: input.source,
    previousStatus: input.previousStatus,
    newStatus: input.newStatus,
    actorUid: input.actor.uid,
    actorEmail: input.actor.email,
    reason: input.reason,
    metadata: mergeMetadataPreservingHistory(input.metadata),
    createdAt: new Date(),
  };
}

With that append-only stream you can rebuild the full history of any task for debugging, for operator training, or for product analytics on where work gets stuck—without depending on a single mutable column that only shows the latest snapshot.

Firebase RBAC and automatic identity capture

Technical correctness does not matter if the endpoint is world-writable. The route is wrapped in middleware—call it withAudit—that verifies a Firebase ID token, loads custom claims, and requires an admin role (or equivalent) before the handler runs.

Production writes can be gated further: environment checks, allowlists, or feature flags that only enable execute mode where policy says so. The middleware attaches a normalized actor object to the request context so every downstream layer records the same identity in events without each function re-parsing headers.

type Handler = (req: AuthenticatedRequest, res: Response) => Promise<void>;

export function withAudit(requireAdmin: boolean): (handler: Handler) => Handler {
  return (handler) => async (req, res) => {
    const decoded = await verifyFirebaseIdToken(req.headers.authorization);
    if (!decoded) return res.status(401).json({ error: "unauthorized" });

    const actor = claimsToActor(decoded);
    if (requireAdmin && !actor.isAdmin) {
      return res.status(403).json({ error: "forbidden" });
    }

    if (isProductionWrite(req) && !passesProductionGuard(req, actor)) {
      return res.status(403).json({ error: "production_writes_disabled" });
    }

    Object.assign(req, { actor });
    await handler(req as AuthenticatedRequest, res);
  };
}

Because the actor is established once at the edge, your event builder always sees a consistent UID and optional email, and you avoid the class of bugs where “we forgot to thread the user through this code path.”

Input validation before any database work

Bulk endpoints are magnets for accidental footguns. The validation layer should run before you touch the database for planning: cap the batch size so a single request cannot enqueue thousands of rows (for example, max fifty tasks per call), restrict status values to a typed enum on both client and server, and bound the reason field—4096 characters is plenty for human context while blocking abuse.

Enum-checked statuses mean you never persist a typo that turns into a silent logic bug in downstream automation. The batch limit gives operators a predictable UX and protects the database from long transactions. Returning clear per-task errors in preview lets the delivery team fix selection issues without opening a ticket.

The admin panel: preview as the primary interface

API design only wins if people use it. The admin panel wraps the flow in a slide-over panel: a status selector, optional structured failure context where your domain needs it, a required or strongly encouraged reason text area, a live preview table fed by the preview mode response, and a visible indicator of how many tasks are in scope versus the batch limit.

Row selection is keyed by stable task IDs and kept in client state that survives refetches, filter changes, sorting, and pagination. That sounds like a small front-end detail; it is what prevents the all-too-familiar “I selected twenty rows, changed the sort, and three disappeared from the bulk action” failure mode. The preview call always sends the explicit ID list so the server does not guess from volatile page state.

What I would ship earlier next time

I would stand up preview and execute together from day one rather than bolting preview on after the first production scare. I would also document the state machine as data—allowed transitions as a table or graph—so validation code and product copy stay aligned. Event sourcing pays off fastest when every entry point that mutates status goes through the same pipeline; exceptions become obvious in code review.

Bulk admin work is not a database chore. It is a product surface. Treat it like one—with validation, authorization, atomic writes, and an append-only history—and you replace fear and bottlenecks with something operators can trust.