Append-only audit log design

Append-only as a property, not a promise

The invariant is stated where every reader of the schema will trip over it:

/**
 * Append-only. No UPDATE/DELETE path exists anywhere in the codebase by design;
 * orgId/actorUserId are plain indexed text (no FK) so history survives member/org removal.
 */
export const auditLog = sqliteTable(
	'audit_log',
	{
		seq: integer('seq').primaryKey({ autoIncrement: true }),
		orgId: text('org_id'),
		actorUserId: text('actor_user_id'),
		action: text('action').notNull(),
		targetType: text('target_type'),
		targetId: text('target_id'),
		metadataJson: text('metadata_json'),
		createdAtMs: integer('created_at_ms').notNull()
	},
	(t) => [index('audit_org_seq_idx').on(t.orgId, t.seq)]
);

Two decisions in those lines deserve attention. First, the primary key breaks the codebase’s own convention: every entity table uses random UUIDs generated application-side, while the audit log alone uses an autoincrement sequence — the schema header comment calls this out as deliberate. A sequence orders rows by insertion, uniquely and monotonically, which is precisely what a history needs and what two events sharing a millisecond would defeat if you ordered by timestamp alone. Second, the writer’s docblock names the commercial reason the property exists at all: there is deliberately no update/delete API, because compliance buyers ask for exactly this. The guarantee lives in the shape of the codebase — one insert function, nothing else touches the table — rather than in a convention nobody enforces.

What this does not give you: anyone with direct SQL access to the database file can still modify rows. Application-layer append-only protects against your own code drifting, against bugs that rewrite history through ordinary endpoints, and against “temporary” admin edit features. If your threat model includes database administrators or host compromise, add the database-level controls (privileges, write-once views, or shipping entries to an external sink) on top — this table is the foundation of that, not a substitute for it.

What one row captures

Each entry records the full sentence of a change — actor, verb, object, detail, moment:

export type AuditEntry = {
	orgId?: string | null;
	actorUserId?: string | null;
	action: string;
	targetType?: string | null;
	targetId?: string | null;
	metadata?: Record<string, unknown>;
};

export async function audit(db: Db, entry: AuditEntry): Promise<void> {
	await db.insert(auditLog).values({
		orgId: entry.orgId ?? null,
		actorUserId: entry.actorUserId ?? null,
		action: entry.action,
		targetType: entry.targetType ?? null,
		targetId: entry.targetId ?? null,
		metadataJson: entry.metadata ? JSON.stringify(entry.metadata) : null,
		createdAtMs: Date.now()
	});
}

The nullable org and actor columns are load-bearing. They carry no foreign keys on purpose, while the membership tables cascade on user and org deletion — so deleting a member removes their access but leaves every entry they ever generated intact. History describes people who no longer exist as rows, which is the only way history can work.

The shipped vocabulary has eight verbs, and reading them together shows the naming convention writing itself — object, dot, past tense: org.created, invite.created, invite.accepted, invite.revoked, member.role_changed, member.removed, member.left, ownership.transferred. A closed, greppable action list means the review UI’s filter dropdown can be generated from the codebase instead of maintained by hand.

Metadata that stays safe to expose

Metadata is a free-form JSON bag, serialized into a text column by the writer — flexible enough for any event, which makes it the natural place for secrets to leak if nobody sets the rule. The rule here is explicit, in a comment on the one call most likely to violate it:

// NOTE: the raw token is never written to the audit log.
await audit(db, {
	orgId: input.orgId,
	actorUserId: input.actorUserId,
	action: 'invite.created',
	targetType: 'invite',
	targetId: invite.id,
	metadata: { role: input.role, email }
});

An invite creation could easily justify logging the link “for support”. Instead the entry stores the role and the optional recipient note, the token itself stays hashed in its own table (shown once, never stored raw — the same discipline the sessions table applies to login tokens), and revocation finds the invite by its stable ID. The principle: metadata carries identifiers and state deltas, never credentials. Assume the log will be screenshotted, exported, and read by everyone who later holds an admin role — because it will be.

The display side defends too. Stored JSON is parsed behind a try/catch that falls back to showing the raw string, so a malformed entry degrades visibly instead of blanking a row or crashing the page:

function meta(json: string | null): string {
	if (!json) return '';
	try {
		return JSON.stringify(JSON.parse(json));
	} catch {
		return json;
	}
}

Write the row in the same service call

The failure mode this pattern kills is the audit call that lives three components away from the mutation and gets forgotten in exactly the code path that needed it most. Here, every mutating service function performs its write and then awaits audit() before returning — same function, same call stack, impossible to skip without editing the function itself:

const targetRole = target.role as Role;
if (!mayGrant(input.actorRole, input.role)) {
	throw new MemberError('hierarchy_violation', `A ${input.actorRole} cannot grant the '${input.role}' role.`);
}
await db.update(memberships).set({ role: input.role }).where(eq(memberships.id, target.id));
await audit(db, {
	orgId: input.orgId,
	actorUserId: input.actorUserId,
	action: 'member.role_changed',
	targetType: 'user',
	targetId: input.targetUserId,
	metadata: { from: targetRole, to: input.role }
});

Look at where from comes from: the target’s role is read into a variable before the UPDATE runs, because afterward the old value exists nowhere. Removal captures the departed member’s role the same way, before the DELETE. Ordering throughout the services is consistent: mutate first, then audit, so a failed or refused operation leaves no entry — the log records what happened, not what was attempted. That is a defensible choice, but know you are making it: denied attempts appear nowhere, and if you need them logged, that is a separate decision with its own volume implications.

A test pins the whole contract — actor, action, and exact metadata — so refactors cannot quietly thin the trail:

it('records every mutation with actor and metadata', async () => {
	await setMemberRole(db, { orgId, actorUserId: owner.id, actorRole: 'owner', targetUserId: member.id, role: 'admin' });
	const log = await listAudit(db, orgId);
	const entry = log.find((e) => e.action === 'member.role_changed');
	expect(entry).toBeDefined();
	expect(entry!.actorUserId).toBe(owner.id);
	expect(JSON.parse(entry!.metadataJson!)).toEqual({ from: 'member', to: 'admin' });
});

Even organization creation joins the scheme: the creator lands as owner and org.created is written in the same call, so every org is born with a first entry and no timeline starts in medias res.

Querying for a review screen

Review interfaces need exactly one query shape: newest N entries for one tenant. Everything about the read path serves that shape — the composite index on (org_id, seq), the descending order, the explicit projection:

export async function listAudit(db: Db, orgId: string, limit = 100, offset = 0): Promise<AuditRow[]> {
	return db
		.select({
			seq: auditLog.seq,
			actorUserId: auditLog.actorUserId,
			action: auditLog.action,
			targetType: auditLog.targetType,
			targetId: auditLog.targetId,
			metadataJson: auditLog.metadataJson,
			createdAtMs: auditLog.createdAtMs
		})
		.from(auditLog)
		.where(eq(auditLog.orgId, orgId))
		.orderBy(desc(auditLog.seq))
		.limit(limit)
		.offset(offset);
}

Ordering by seq rather than timestamp is the payoff of the autoincrement key: newest-first paging stays stable even when two events land in the same millisecond, and the composite index lets the database satisfy tenant-scoped scans without touching other orgs’ rows. The shipped review page requests 200 entries per view and keys each rendered row by its sequence number, truncates opaque user and target IDs to eight characters for humans, and formats millisecond timestamps at the edge — storage stays UTC-in-milliseconds everywhere, presentation worries live in one component.

Limits of this design as shipped

• Immutability is application-layer only. Direct SQL against the SQLite file bypasses everything described here. Pair this design with database privileges or an external sink when your threat model demands it.
• Mutation and audit write are sequential awaits, not one transaction. A crash between them loses an entry; the single-process setup makes that window small, and wrapping both statements in one transaction is the first upgrade on a networked database.
• Only successes are recorded. Refused operations throw before any write, so denied attempts leave no trace anywhere.
• Growth is unbounded. No retention or pruning job exists in the shipped code — appropriate until it very much isn’t; plan rotation before the table becomes your largest.
• The read path offers pagination only. Filtering by action, actor, or date range is not implemented yet; the index supports an action filter poorly, so expect to extend the schema when review needs it.
• Actors render as truncated IDs. The review screen shows an eight-character prefix, not a name — joining users at query time is the obvious next step once you accept the tradeoff of re-linking history to mutable profile data.

Production checklist

• Make the table append-only by construction: one insert function, and no update or delete API anywhere in the codebase.
• Use a monotonic sequence as the audit primary key; reserve random UUIDs for entities, seq for history.
• Leave org and actor columns free of foreign keys so entries survive the deletion of everything they describe.
• Name actions object.action, past tense — invite.created, member.role_changed. The vocabulary doubles as your review-UI filter list.
• Log identifiers and state deltas in metadata, never tokens, passwords, or anything a leaked backup would turn into keys.
• Capture prior values before the UPDATE overwrites them; the audit row is the only place “from” will ever exist.
• Await the audit insert inside the same service call as the mutation, after it succeeds — callers cannot forget it.
• Stamp timestamps inside the writer function, not per caller: one clock per table.
• Index (org_id, seq) so the one query every review screen needs — newest N entries for a tenant — stays an index scan.
• Render defensively: parse stored JSON behind try/catch and truncate opaque IDs before showing them to humans.

The audit trail ships alongside the permission system whose changes it records — covered in the guide on SvelteKit RBAC and role hierarchy, with the invite lifecycle it tracks detailed in single-use invite links. Both run in the multi-tenant SvelteKit starter.