Audit Logs

Overview

Supasheet ships a built-in audit-logging system that records every INSERT, UPDATE, and DELETE against any table you opt in. The trail is stored in supasheet.audit_logs and surfaced in two places:

Core → Audit Logs — A global, filterable view of all activity
Resource detail → Audit tab — Per-record history (requires the schema.table:audit permission)

The schema is defined in supabase/migrations/20250928062812_audit_logs.sql.

What Gets Tracked

For every audited mutation, a row is appended to supasheet.audit_logs with:

Column	Meaning
`id` (uuid)	Audit entry id
`created_at` (timestamptz)	When the change happened
`operation`	`INSERT` / `UPDATE` / `DELETE`
`schema_name`, `table_name`	Where the change happened
`record_id`	The primary key of the affected row
`created_by` (uuid)	The user who made the change (resolved from `auth.uid()`)
`role` (`app_role`)	The user's role at the time of the change
`user_type`	`'real_user'` or `'system'` (background / service-role writes)
`old_data` (jsonb)	Snapshot before the change (for UPDATE / DELETE)
`new_data` (jsonb)	Snapshot after the change (for INSERT / UPDATE)
`changed_fields` (text[])	Columns whose values differ (UPDATE only)
`is_error`, `error_message`, `error_code`	Optional error context
`metadata` (jsonb)	Extra context — open for your own use

A set of indexes (created_at, created_by, role, operation, schema_name + table_name, record_id, partial on is_error, GIN on metadata) keeps queries fast even with large volumes.

Enabling Audit Logging on a Table

Attach the built-in trigger function supasheet.audit_trigger_function() for the operations you care about:

CREATE TRIGGER audit_tasks
  AFTER INSERT OR UPDATE OR DELETE
  ON desk.tasks
  FOR EACH ROW
  EXECUTE FUNCTION supasheet.audit_trigger_function();

The generic single-trigger form above is the recommended pattern (matches the example domains in supabase/examples/). You can also create three separate triggers if you only want a subset.

For DELETE, the trigger captures the row state in old_data automatically using the standard OLD record exposed to row-level triggers.

Granting Access

Two permissions control who can see what:

supasheet.audit_logs:select — required for the global Core → Audit Logs page (seeded for x-admin)
schema.table:audit — required to see the Audit tab on a record's detail page

-- Allow regular users to see audit history for tasks
ALTER TYPE supasheet.app_permission ADD VALUE 'desk.tasks:audit';
INSERT INTO supasheet.role_permissions (role, permission) VALUES
  ('user', 'desk.tasks:audit');

Reading the Trail from SQL

Supasheet provides a helper supasheet.get_audit_logs(p_schema, p_table, p_record_id) that joins creator details:

SELECT * FROM supasheet.get_audit_logs('desk', 'tasks', 'a1b2…');

Direct queries work too:

-- Everything I changed today
SELECT *
FROM supasheet.audit_logs
WHERE created_by = auth.uid()
  AND created_at >= date_trunc('day', now())
ORDER BY created_at DESC;

-- Updates to a single record with the changed columns
SELECT created_at, changed_fields, old_data, new_data
FROM supasheet.audit_logs
WHERE schema_name = 'desk'
  AND table_name = 'tasks'
  AND record_id = 'a1b2…'
  AND operation = 'UPDATE'
ORDER BY created_at;

-- Errors that hit any audited table
SELECT * FROM supasheet.audit_logs WHERE is_error;

Adding an Audit Log Link to the UI

Audit history isn't a metadata-configured tab — it's a dedicated page (/$schema/resource/$resource/$resourceId/audit) that Supasheet automatically links to from a record's actions menu whenever the current user holds schema.table:audit. There's nothing to declare in the table's JSON comment for this; granting the permission is enough:

ALTER TYPE supasheet.app_permission ADD VALUE 'desk.tasks:audit';

INSERT INTO supasheet.role_permissions (role, permission) VALUES
  ('user', 'desk.tasks:audit');

Don't confuse this with the tabs metadata field documented in Metadata — that field only filters which related-table tabs show on the detail page (foreign key relationships). It has no effect on audit or comment access, which are controlled purely by permissions.

Complete Example

-- Table
CREATE TABLE store.products (
  id UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v4(),
  name TEXT NOT NULL,
  price NUMERIC(10, 2),
  status product_status NOT NULL DEFAULT 'draft',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Audit trigger
CREATE TRIGGER audit_products
  AFTER INSERT OR UPDATE OR DELETE
  ON store.products
  FOR EACH ROW
  EXECUTE FUNCTION supasheet.audit_trigger_function();

-- Permissions
ALTER TYPE supasheet.app_permission ADD VALUE 'store.products:select';
ALTER TYPE supasheet.app_permission ADD VALUE 'store.products:insert';
ALTER TYPE supasheet.app_permission ADD VALUE 'store.products:update';
ALTER TYPE supasheet.app_permission ADD VALUE 'store.products:delete';
ALTER TYPE supasheet.app_permission ADD VALUE 'store.products:audit';

INSERT INTO supasheet.role_permissions (role, permission) VALUES
  ('user', 'store.products:select'),
  ('user', 'store.products:insert'),
  ('user', 'store.products:update'),
  ('user', 'store.products:audit');

-- Metadata (audit access needs no entry here — it's driven entirely by the :audit permission above)
COMMENT ON TABLE store.products IS '{
  "icon": "Package"
}';

Next Steps

Authorization — How :audit slots into the permission model
Comments — Pair audit with discussions on each record
Notifications — Notify users when audited events happen

Audit Logs

On this page