JSONB metadata patterns for SaaS audit logs

Why audit metadata is flexible and structured

The fixed columns on audit_log — org_id, actor_user_id, action, target_type, target_id, created_at_ms — capture the shape shared by every security-relevant event. What distinguishes one event from another is the free-form context: which plan was changed, the seat count before and after, the values of a role change, the reason a membership was removed.

That context is never the same across event types, so baking it into fixed columns would force a schema migration for every new event. Storing it as JSON means the audit writers decide what an event carries, per event, with no table change. That is the core benefit of JSON metadata in an audit log: arbitrary per-event context without schema churn. An event about an ownership transfer, an event about a role change, and an event about a seat-limit failure can each log exactly what they need, in one table, typed as JSON.

How the kit stores audit metadata today

In v0.1, metadata_json on the audit_log table is a plain JSON text column, defined in src/lib/server/db/schema.ts:

// src/lib/server/db/schema.ts (audit_log, abridged)
export const auditLog = pgTable(
  'audit_log',
  {
    seq: bigint('seq', { mode: 'number' }).primaryKey().generatedAlwaysAsIdentity(),
    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: bigint('created_at_ms', { mode: 'number' }).notNull()
  },
  (t) => [index('audit_org_seq_idx').on(t.orgId, t.seq)]
);

Several choices in that definition are deliberate and worth understanding. audit_log.org_id and actor_user_id are plain indexed text, not foreign keys — so the history survives member removal and (future) org deletion even when the referenced user is gone. The seq bigint identity provides total ordering of events. And metadata_json is text rather than jsonb:

That text choice keeps the data model identical to the SQLite core of the Multi-tenant SvelteKit Starter, so the entire service layer, RBAC, billing, and audit code ports unchanged between the two kits — swap the driver, keep the services. JSON as text is the portable baseline; jsonb is the Postgres-specific upgrade you opt into.

The honest tradeoff: text JSON vs native jsonb

• Text JSON (the shipped default): fine for write-once, read-occasionally workloads — exactly the audit-log profile. You can still store structured data and read it back whole. But you cannot query into the JSON efficiently: there is no type-aware index, no containment operator, and no typed document path access. To filter on a field you parse the string per row (or push parsing to the application).
• Native jsonb: Postgres stores a decomposed, type-aware binary representation. It unlocks the containment operator @>, GIN indexes over the structure, and typed document/path access. The cost is that jsonb is a Postgres-only type, so the column is no longer identical to the SQLite starter's text column — an intentional divergence you adopt when the query value outweighs the portability.

The practical framing: if you only ever read the whole metadata blob back (e.g. "show this event's details"), text is sufficient. The moment you want to answer questions like "how many pro-plan organizations transferred ownership this quarter", you are querying inside the JSON — and that is where jsonb earns its keep.

The documented upgrade path to jsonb

docs/deployment.md §7 documents the exact upgrade: change audit_log.metadata_json (and/or a new memberships.metadata column) to jsonb, then write objects directly instead of JSON.stringify:

// schema.ts — switch the column type and the TS type
metadataJson: jsonb('metadata_json').$type<Record<string, unknown>>(),
// audit writer — pass the object, not a string
metadataJson: entry.metadata ?? null,

Two changes happen together. The schema column becomes jsonb and is typed in Drizzle as $type<Record<string, unknown>>(), and every audit writer stops serializing to a JSON string and passes the object directly. In the kit's schema-first workflow, you make that edit in schema.ts, run npm run db:generate to emit a new SQL migration under drizzle/, review it, and apply it (migrations auto-apply at boot and via npm run db:migrate).

Queries you unlock with jsonb

The headline operator is containment, @>, which asks "does this document contain this sub-structure?". The docs give the canonical example — find every audit entry whose metadata records a pro plan:

WHERE metadata_json @> '{"plan":"pro"}'

Because the value on the right is a JSON document (here a JSON string literal), the containment operator checks whether the metadata object contains a plan key equal to "pro" at the top level. This is scalar/jsonb containment — no string parsing per row, and it is GIN-indexable.

If you query the metadata often, back it with a GIN index so the containment lookup does not scan every audit row:

CREATE INDEX ... USING GIN (metadata_json);

GIN is the standard index type for jsonb containment queries. The same family of Postgres-native features appears in the full-text-search pattern documented alongside it in docs/deployment.md §7 — the kit's audit log even ships a btree index (audit_org_seq_idx on org_id, seq) so per-org reads of the event stream stay fast regardless of the metadata column type.

Append-only rows and who decides the metadata

The audit log is append-only by construction — there is no UPDATE or DELETE path anywhere in the code, and no UPDATE/DELETE policy is granted. Writers insert; readers query; nothing mutates an event after the fact. That design interacts with metadata in an important way: because a row is immutable once written, the audit entry's metadata is a frozen snapshot of the event as it happened. You can never "fix" a past entry, so the writer decides, at write time, exactly what the metadata contains.

That write-time decision is a security consideration, not just a data-modeling one. docs/architecture.md states it plainly: writers decide what goes in, and the raw invite token is never audited. Secrets are stored hashed in the kit — invite tokens and session tokens exist raw only at the moment of use — and that discipline extends to audit metadata. When you choose what to log per event, log the context that is safe and useful (an actor id, a target id, a plan, a seat count), and never the raw secret itself, because append-only means you can never scrub it out of old rows.

Recommendation: jsonb from day one, or stay on text

Use jsonb from day one if you will query audit metadata — if you plan ad-hoc investigations, compliance lookups, or dashboards that filter events by metadata fields, the containment operator, GIN indexes, and typed access pay for the portability cost immediately, and adopting it before you have live data is far cheaper than migrating a long audit history later.

Otherwise, keep text. If you read whole metadata blobs back and never filter into them, the shipped text column is honest, portable across the SQLite and Postgres kits, and requires no migration. Either way, the append-only, org-scoped event stream stays unchanged — the column type is an implementation detail beneath it.

In this guide: the shipped audit_log schema in src/lib/server/db/schema.ts, the append-only audit design in docs/architecture.md, and the jsonb + GIN patterns in docs/deployment.md §7. See Drizzle migrations for how the jsonb column change would flow through drizzle-kit.