Admin activity log

The admin.admin_activity_log table captures admin-tier actions: alert acknowledgements, health-endpoint changes, deployment markers, organization edits, user-role transitions, and other operations that affect platform-level state. It is intentionally narrow in scope — it is not the change-tracking table for every inventory edit (see Cut history for per-cut records).

What gets logged

Action strings are free-form (so the team can introduce new action types without a migration), but every row’s resource_type is constrained at the schema level via CHECK:

`resource_type`	Example actions
`user`	`user.role.changed`, `user.invited`, `user.demoted`
`org`	`org.settings.updated`, `org.branding.updated`
`alert`	`alert.acknowledged`, `alert.muted`
`deployment`	`deployment.marked`
`health_endpoint`	`health_endpoint.created`, `health_endpoint.disabled`
`health_check`	`health_check.acknowledged`
`system`	Workspace-level system actions

The schema

admin.admin_activity_log:

Column	Type	Notes
`id`	`BIGSERIAL`	Primary key
`actor_id`	`UUID`	FK → `auth.users(id)` (`ON DELETE SET NULL` so deleted users keep their audit trail)
`actor_email`	`TEXT NOT NULL`	Snapshotted at insert time, survives user deletion
`action`	`TEXT NOT NULL`	The action string (`user.role.changed` etc.)
`resource_type`	`TEXT NOT NULL`	Constrained via `CHECK` (see above)
`resource_id`	`TEXT`	The affected entity’s id (UUID or composite)
`metadata`	`JSONB NOT NULL DEFAULT '{}'`	Action-specific context — the row that was changed, which fields, etc.
`ip_address`	`INET`	Request IP at insert time (nullable; not all call sites populate it today)
`user_agent`	`TEXT`	Request UA at insert time (nullable; not all call sites populate it today)
`created_at`	`TIMESTAMPTZ NOT NULL DEFAULT NOW()`	UTC

There are GIN + B-tree indexes on (actor_id, created_at), (resource_type, resource_id, created_at), created_at, and metadata so audit queries by actor, resource, time, or metadata field are all fast.

metadata is a single JSONB blob today, not split into before_state / after_state columns. Splitting that — and ensuring every call site populates ip_address / user_agent — is on the SOC 2 roadmap under Q2. The columns exist; the inserter discipline is the gap.

RLS-protected

Two policies gate the table:

CREATE POLICY "admin_activity_log: admins select"
  ON admin.admin_activity_log FOR SELECT USING (admin.is_admin());
 
CREATE POLICY "admin_activity_log: admins insert"
  ON admin.admin_activity_log FOR INSERT TO authenticated
  WITH CHECK (admin.is_admin() AND actor_id = auth.uid());

Only admin users can read or insert. The insert policy also enforces that actor_id matches the calling user’s JWT — an admin cannot impersonate a different actor.

This is intentional. The audit log is a defense against bad actors, including bad-actor admins. An admin removing entries from the audit log generates its own audit-log entry that another admin can see — there is no clean way to cover tracks.

Querying

-- All role changes in the last 90 days
SELECT a.created_at, a.actor_email, a.resource_id AS target_user_id,
       a.metadata->>'old_role' AS old_role,
       a.metadata->>'new_role' AS new_role,
       a.ip_address
FROM admin.admin_activity_log a
WHERE a.action = 'user.role.changed'
  AND a.created_at > NOW() - INTERVAL '90 days'
ORDER BY a.created_at DESC;

-- All actions on a specific resource
SELECT a.created_at, a.actor_email, a.action, a.metadata
FROM admin.admin_activity_log a
WHERE a.resource_type = 'org' AND a.resource_id = $1
ORDER BY a.created_at DESC;

Retention

Rows are never auto-deleted. SOC 2 will require a documented retention policy; the planned policy is “indefinite for compliance, with archive-tiered storage after 2 years.” See SOC 2 roadmap.

⚠️

Don’t try to use this log as a general-purpose change-tracking table. The resource_type CHECK rejects rows that aren’t admin-tier. Use database history extensions (e.g., pgaudit) or cut_history for broader use cases.