When a user says "I didn't delete that," you want to be able to prove them right or wrong. An audit log solves that -- and it covers compliance requirements, debugging production issues, and building a user-visible activity feed.
This post shows the full pattern: schema, repository, service helper, and a server component that renders the log.
Add an audit_logs table. Each row records who did what to what, and when.
// modules/auditLog/auditLog.schema.ts
import { pgTable, uuid, text, jsonb, timestamp, index } from 'drizzle-orm/pg-core';
import { userTable } from '../user/user.schema';
export const auditLogTable = pgTable(
'audit_logs',
{
id: uuid('id').defaultRandom().primaryKey(),
actorId: uuid('actor_id').references(() => userTable.id, { onDelete: 'set null' }),
action: text('action').notNull(),
resourceType: text('resource_type').notNull(),
resourceId: text('resource_id').notNull(),
metadata: jsonb('metadata'),
createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
},
(t) => [
index('audit_logs_actor_id_idx').on(t.actorId),
index('audit_logs_resource_idx').on(t.resourceType, t.resourceId),
index('audit_logs_created_at_idx').on(t.createdAt),
]
);The metadata column holds arbitrary JSON -- useful for recording before/after values without designing a separate column for every case.
Register the table in db/drizzle.ts and run the migration:
npm run db:generate
npm run db:migrateInsert and query -- nothing else.
// modules/auditLog/auditLog.repo.ts
import { db } from '@/db/drizzle';
import { auditLogTable } from './auditLog.schema';
import { desc, eq, and } from 'drizzle-orm';
import type { AuditLogInsert, AuditLogFilter } from './auditLog.types';
export const auditLogRepo = {
async create(data: AuditLogInsert) {
const [row] = await db.insert(auditLogTable).values(data).returning();
return row;
},
async findByResource(filter: AuditLogFilter) {
return db
.select()
.from(auditLogTable)
.where(
and(
eq(auditLogTable.resourceType, filter.resourceType),
eq(auditLogTable.resourceId, filter.resourceId)
)
)
.orderBy(desc(auditLogTable.createdAt))
.limit(filter.limit ?? 50);
},
async findByActor(actorId: string) {
return db
.select()
.from(auditLogTable)
.where(eq(auditLogTable.actorId, actorId))
.orderBy(desc(auditLogTable.createdAt))
.limit(100);
},
};// modules/auditLog/auditLog.types.ts
export interface AuditLogInsert {
actorId?: string | null;
action: string;
resourceType: string;
resourceId: string;
metadata?: Record<string, unknown>;
}
export interface AuditLogFilter {
resourceType: string;
resourceId: string;
limit?: number;
}A single log() function that every other service can call. It never throws -- a failed audit write should not break the operation being logged.
// modules/auditLog/auditLog.service.ts
import { auditLogRepo } from './auditLog.repo';
import type { AuditLogInsert } from './auditLog.types';
export const auditLogService = {
async log(entry: AuditLogInsert): Promise<void> {
try {
await auditLogRepo.create(entry);
} catch (err) {
console.error('[audit] failed to write log entry', err);
}
},
async getForResource(resourceType: string, resourceId: string) {
return auditLogRepo.findByResource({ resourceType, resourceId });
},
async getForActor(actorId: string) {
return auditLogRepo.findByActor(actorId);
},
};The try/catch is intentional. An audit write failure is a logging concern, not a user-facing error.
Add audit calls after the primary operation succeeds. The actor comes from the route via getUserFromRequest -- services should never call that themselves.
// modules/post/post.service.ts (excerpt)
import { auditLogService } from '../auditLog/auditLog.service';
export const postService = {
async deletePost(postId: string, actorId: string) {
const post = await postRepo.findById(postId);
if (!post) throw new HttpError(404, 'Post not found');
if (post.authorId !== actorId) throw new HttpError(403, 'Forbidden');
await postRepo.delete(postId);
await auditLogService.log({
actorId,
action: 'post.deleted',
resourceType: 'post',
resourceId: postId,
metadata: { title: post.title },
});
},
};Use dot-separated action names: post.deleted, user.role_changed, billing.subscription_cancelled. They are easy to filter and read in the UI without parsing.
A server component that renders the log for a given resource -- no client-side JavaScript needed.
// components/AuditLogList.tsx
import { auditLogService } from '@/modules/auditLog/auditLog.service';
interface Props {
resourceType: string;
resourceId: string;
}
export async function AuditLogList({ resourceType, resourceId }: Props) {
const entries = await auditLogService.getForResource(resourceType, resourceId);
if (entries.length === 0) {
return <p className="text-muted-foreground text-sm">No activity yet.</p>;
}
return (
<ul className="space-y-2">
{entries.map((entry) => (
<li key={entry.id} className="flex items-start gap-3 text-sm">
<span className="text-muted-foreground shrink-0 tabular-nums">
{new Date(entry.createdAt).toLocaleString()}
</span>
<span className="font-mono text-xs bg-muted px-1.5 py-0.5 rounded">
{entry.action}
</span>
{entry.metadata && (
<span className="text-muted-foreground truncate">
{JSON.stringify(entry.metadata)}
</span>
)}
</li>
))}
</ul>
);
}Drop it into any server component page with two props:
<AuditLogList resourceType="post" resourceId={post.id} />Log the actions that matter for support and compliance:
Skip high-frequency reads -- they produce noise without signal and inflate the table fast.
One auditLogService.log() call at the end of each mutating service method, actor passed in from the route, action name in dot notation. The try/catch in the service means a write failure is logged but never surfaces to the user. From there, AuditLogList drops into any server component page with two props -- no extra API route, no client hooks, no loading state to manage.