Per-User Logins and an Audit Trail in Supabase: Auth, RLS and Triggers, a Tamper Drill, and a Small Support Portal

When sharing a single login, an audit log cannot differentiate between individual users. This made the author realize the need to build an audit trail using Supabase Auth, Row Level Security (RLS) and database triggers, and attempt to break it for testing purposes. All actions were performed on a Free project on 2026-10-27, and prices were subject to change.

The system consists of two tables - support_requests (the work) and activity_log (who did what). The trigger writes the log, not the page, as the code in the page can be skipped, edited, or forged. The actor's identity comes from the login, using the auth.uid() and auth.jwt() functions, and the email inside the trigger.

To ensure tamper-proofing, the log is read-only for the API, with no insert, update or delete policies. The client cannot claim to be someone else, and the who and when are stamped on the row itself. The function `public.stamp_request()` is a security definer, which runs with the owner's rights, allowing it to write even when the caller cannot. The audit log is secured by revoking insert, update, and delete privileges on the `public.activity_log` table from the 'anon' and 'authenticated' roles.

The author created three fictional demo users, Case A with a shared login (shared-desk) used by two real people, and Case B with each person using their own login. When a shared-login scenario was tested, the audit log showed only the shared-desk@example.com email address for both users. In the case of individual logins, the activity log accurately recorded each user's actions - sato added the request, tanaka moved it to in progress, and sato marked it done.

The log cannot be tampered with as the changes are recorded with who and when they occurred. The author tried to tamper with the log as staff-sato, but all attempts were unsuccessful, resulting in 403 errors. Accessing requests without signing in was allowed, while adding a request without signing in resulted in a 401 error. The author suggests treating error hints as suggestions to check against their intent, not as commands.