Designing an activity event schema (actor / action / target)

Every activity feed, audit trail, and "what happened in my account" page renders the same sentence: someone did something to something. Ari invited Taylor to the workspace. The owner rotated an API key. Billing upgraded from Free to Growth.

That sentence is your schema. Get the grammar right — actor / action / target — and everything downstream (querying, rendering, filtering, compliance exports) falls out naturally. Get it wrong and you'll be migrating an events table with millions of rows while support tickets pile up. This post is the set of decisions we'd make again, whether you use our activity feed API or build your own.

The shape

{
  "actor":  { "id": "user_8f2", "name": "Ari Patel", "email": "ari@acme.co" },
  "action": "workspace.member.invited",
  "target": { "type": "workspace", "id": "ws_c41", "name": "Acme Production" },
  "metadata": { "invited_email": "taylor@acme.co", "role": "admin" },
  "occurred_at": "2026-08-27T14:09:31Z",
  "session_id": "0f6d5c1e-…"
}

• actor — who did it. Your user ID, plus a snapshot of name and email at event time.
• action — what happened, as a constrained string (rules below).
• target — what it happened to. Optional: login.succeeded has no target.
• metadata — the details specific to this action, small and flat.
• occurred_at vs ingested_at — client time and server time diverge; store both, sort by occurred_at.

Snapshot the actor. Don't join to it.

The single most common schema mistake: storing only actor_id and joining to your users table at read time. It's normalized, it's tidy, and it's wrong — because an audit trail's whole job is to describe the past. When the user is deleted, renamed, or transferred, the join either breaks or silently rewrites history ("Deleted User invited Taylor" tells an auditor nothing).

Denormalize name and email onto the event (or an actor-snapshot table). Same logic for target.name: "Ari deleted Q3 Board Deck" is a useful audit line; "Ari deleted file_9c2e" is not, once the file is gone.

Action naming: resource.verb, past tense, enforced

# Pattern: resource.verb — verb in past tense, resource from YOUR domain

member.invited          # good
member.removed          # good
billing.plan.upgraded   # good — nesting is fine, keep it consistent
api_key.rotated         # good

UserInvited             # bad — casing will drift across services
invite                  # bad — verb without resource is unqueryable
member.invite           # bad — present tense reads wrong in a feed
clicked_button_3        # bad — UI concern, not a domain event

The rules matter less than the enforcement. If action names are free-form strings, five services will invent five spellings within a month and your feed queries become LIKE soup. Validate at ingest with a regex — ours is ^[a-z][a-z0-9_]*(\.[a-z][a-z0-9_]*)+$ and anything else is rejected with a 422. A rejected event at dev time is annoying; a corrupted event vocabulary in production is forever.

Metadata: the junk-drawer problem

// Anti-pattern: the metadata junk drawer
log.track({
  actor: { id: user.id },
  action: 'event',                    // ❌ one action for everything
  metadata: {
    kind: 'invite',                   // ❌ the action, hiding in metadata
    user: JSON.stringify(user),       // ❌ whole objects, PII included
    workspace: workspace,             // ❌ unbounded nested payload
  },
});

Three rules keep metadata useful:

1. If you'd ever filter by it, it's not metadata. Promote it to action or target.
2. Cap the size at ingest. We cap at 4 KB. Uncapped JSONB is how one debug log ends up dominating your storage bill.
3. No secrets, no full objects. Metadata gets rendered to end users in a customer-facing feed. Write each key deliberately.

// The same event, structured so it can be queried and rendered
log.track({
  actor: { id: user.id, name: user.name, email: user.email },
  action: 'workspace.member.invited',
  target: { type: 'workspace', id: workspace.id, name: workspace.name },
  metadata: { invited_email: invitee.email, role: invite.role },
});

Decisions that look small and aren't

• Session ID on every event. A nullable session_id costs nothing at write time and gives support a "show me everything around 2pm" timeline for free.
• String target IDs. Your IDs are UUIDs today; your Stripe IDs and GitHub repo slugs aren't. target.id should be text.
• Never mutate, never delete individually. Corrections are new events (member.invite.revoked), deletions happen only via retention policy. An editable audit log is a diary, not an audit log.
• Design the feed sentence first. Before adding an event, write the sentence the customer will read. If you can't write it from actor + action + target + metadata, the schema for that event is wrong.

Start with ten events, not a hundred

Instrument the events customers ask about: invites and removals, role changes, billing changes, exports, deletions, API key operations, login security events. That's usually under ten track() calls, shippable in an afternoon, and it covers the majority of "who did what" questions you'll ever get. Expand from real support tickets, not from speculation.

This schema is exactly what Softechlog's API speaks natively — but it's also just a good schema. If you build your own, steal it.