Row-level triggers for audit logs in Supabase - Deep Dive

Overview - Row-level triggers for audit logs

What is it?

Row-level triggers for audit logs are automatic actions in a database that record changes made to individual rows in tables. They track when data is added, changed, or deleted, and save details like who made the change and when. This helps keep a history of data changes without manual effort. Supabase uses PostgreSQL, which supports these triggers natively.

Why it matters

Without row-level triggers for audit logs, it is hard to know who changed data and when, which can cause problems in tracking errors, security breaches, or data mistakes. These triggers provide a reliable, automatic way to keep a detailed history of data changes, helping teams fix issues faster and meet compliance rules. Without them, auditing would be slow, error-prone, and incomplete.

Where it fits

Before learning about row-level triggers, you should understand basic database tables and SQL commands like INSERT, UPDATE, and DELETE. After this, you can learn about advanced database features like stored procedures, security policies, and how to analyze audit logs for insights.

Mental Model

Core Idea

Row-level triggers automatically watch each row change in a database table and record details to an audit log without manual intervention.

Think of it like...

It's like having a security camera on every drawer in a filing cabinet that records who opens it and what they change, so you always have a video history of every action.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ User changes  │──────▶│ Row-level     │──────▶│ Audit log     │
│ data in table │       │ trigger fires │       │ records event │
└───────────────┘       └───────────────┘       └───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding database tables and rows

Concept: Learn what tables and rows are in a database and how data is stored.

A database table is like a spreadsheet with columns and rows. Each row holds one record of data, like a single person's information. Columns define the type of data, such as name or email. You can add, change, or delete rows using commands.

Result

You know how data is organized in tables and how rows represent individual records.

Understanding tables and rows is essential because triggers act on changes to these rows.

2

Foundation

Basics of SQL data modification commands

Concept: Learn the commands to add, update, and delete rows in a table.

SQL uses INSERT to add new rows, UPDATE to change existing rows, and DELETE to remove rows. For example, INSERT adds a new user, UPDATE changes their email, and DELETE removes them from the table.

Result

You can modify data in tables using SQL commands.

Knowing these commands helps you understand when triggers will activate.

3

Intermediate

What are row-level triggers?

🤔Before reading on: do you think triggers run once per table change or once per row changed? Commit to your answer.

Concept: Row-level triggers run once for every row affected by a data change, allowing detailed tracking.

A row-level trigger is a special database function that runs automatically whenever a row is inserted, updated, or deleted. It can access the old and new data of that row and perform actions like logging the change.

Result

You understand that triggers can watch every single row change individually.

Knowing triggers run per row means audit logs can capture precise details for each record change.

4

Intermediate

Creating audit log tables

🤔Before reading on: do you think audit logs store full row data or just change summaries? Commit to your answer.

Concept: Audit log tables store details about changes, including old and new data, timestamps, and user info.

To keep audit logs, you create a separate table with columns like table name, operation type (insert/update/delete), changed data, who made the change, and when. This table collects all change events for review.

Result

You have a dedicated place to store detailed change history.

Separating audit logs from main data keeps history safe and organized.

5

Intermediate

Writing row-level triggers for audit logs

🤔Before reading on: do you think triggers can access who made the change automatically? Commit to your answer.

Concept: Triggers can capture data changes and user info, then insert audit records automatically.

In Supabase (PostgreSQL), you write a trigger function in SQL or PL/pgSQL that runs on INSERT, UPDATE, or DELETE. It reads the old and new row data and inserts a record into the audit log table. You can get the current user from session variables.

Result

Your database automatically records every row change with details.

Automating audit logs with triggers reduces errors and ensures complete history.

6

Advanced

Handling bulk changes and performance

🤔Before reading on: do you think triggers slow down bulk data loads significantly? Commit to your answer.

Concept: Triggers run per row, so bulk operations can impact performance; strategies exist to manage this.

When many rows change at once, triggers fire for each row, which can slow down operations. To handle this, you can batch audit logs, disable triggers temporarily during large imports, or use asynchronous logging methods.

Result

You can balance audit detail with system speed in real scenarios.

Understanding performance tradeoffs helps design scalable audit logging.

7

Expert

Security and compliance with audit triggers

🤔Before reading on: do you think audit logs can be altered by users with write access? Commit to your answer.

Concept: Audit logs must be protected from tampering and meet legal rules; triggers help enforce this.

Audit log tables should have restricted permissions so only trusted roles can read or write. Triggers ensure logs are created automatically and cannot be skipped. Combined with policies and encryption, this supports compliance with laws like GDPR or HIPAA.

Result

Your audit logs are trustworthy and legally compliant.

Securing audit logs is critical to maintain data integrity and trust.

Under the Hood

Row-level triggers are special functions stored in the database that execute automatically for each row affected by data changes. When a row is inserted, updated, or deleted, the database engine calls the trigger function with access to the old and new row data. The function can then perform actions like inserting a record into an audit log table. This happens inside the same transaction, ensuring consistency.

Why designed this way?

Triggers were designed to automate reactions to data changes without requiring application code changes. By running inside the database, they guarantee that audit logs are created even if multiple applications or users modify data. This centralizes control and reduces human error. Alternatives like manual logging are unreliable and inconsistent.

┌───────────────┐
│ Data Change   │
│ (INSERT/UPDATE/│
│ DELETE)       │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Database      │
│ Engine        │
│ detects row   │
│ change        │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Trigger       │
│ Function      │
│ runs per row  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Audit Log     │
│ Table updated │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do row-level triggers run once per table change or once per row changed? Commit to your answer.

Common Belief: Triggers run once per table change, no matter how many rows are affected.

Reality: Row-level triggers run once for every individual row that changes.

Why it matters: Thinking triggers run once per table can cause missed audit entries or performance surprises.

Quick: Can audit logs created by triggers be easily modified by users with write access? Commit to your answer.

Common Belief: Audit logs are just tables and can be changed by anyone with write access.

Reality: If permissions are not set properly, audit logs can be altered, but best practice is to restrict access so logs are tamper-proof.

Why it matters: If audit logs can be changed, they lose trustworthiness and fail compliance requirements.

Quick: Do triggers slow down bulk data loads significantly? Commit to your answer.

Common Belief: Triggers have negligible impact on performance, even with large data changes.

Reality: Triggers run per row and can slow down bulk operations unless optimized or temporarily disabled.

Why it matters: Ignoring performance impact can cause slow systems and frustrated users.

Quick: Do triggers automatically know who made the change without extra setup? Commit to your answer.

Common Belief: Triggers always know the user who made the change without any configuration.

Reality: Triggers can access user info only if the application or database session passes that info, such as via session variables.

Why it matters: Without passing user context, audit logs miss critical information about who made changes.

Expert Zone

1

Triggers run inside the same transaction as the data change, so if the transaction rolls back, audit logs do too, ensuring consistency.

2

Using JSON columns in audit logs allows flexible storage of old and new row states without schema changes.

3

Supabase's Row Level Security (RLS) policies can be combined with triggers to control who can see audit logs, enhancing security.

When NOT to use

Row-level triggers are not ideal for extremely high-volume tables where performance is critical; in such cases, asynchronous logging or external change data capture tools are better. Also, if audit requirements are minimal, simple timestamp columns might suffice.

Production Patterns

In production, teams often create generic audit trigger functions reusable across tables, store user info via session variables, and archive audit logs periodically. They also monitor trigger performance and use database extensions for advanced auditing.

Connections

Change Data Capture (CDC)

Builds-on

Understanding row-level triggers helps grasp CDC, which captures and streams database changes for analytics or replication.

Event-driven architecture

Similar pattern

Triggers act like event listeners in software, automatically reacting to data changes, a core idea in event-driven systems.

Forensic accounting

Cross-domain analogy

Audit logs in databases serve a similar role to forensic accounting records, tracking every transaction to detect fraud or errors.

Common Pitfalls

#1 Audit logs missing user information

Wrong approach:

CREATE FUNCTION audit_trigger() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log(user_id, action) VALUES (NULL, TG_OP); RETURN NEW; END; $$ LANGUAGE plpgsql;

Correct approach:

CREATE FUNCTION audit_trigger() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log(user_id, action) VALUES (current_setting('app.current_user', true), TG_OP); RETURN NEW; END; $$ LANGUAGE plpgsql;

Root cause:

Not passing or retrieving the current user context causes audit logs to miss who made changes.

#2 Trigger causes slow bulk inserts

Wrong approach:

Leaving triggers enabled during large data imports without optimization.

Correct approach:

Temporarily disable triggers during bulk loads or batch audit inserts asynchronously.

Root cause:

Triggers firing per row add overhead that accumulates in bulk operations.

#3 Audit log table accessible to all users

Wrong approach:

GRANT SELECT, INSERT ON audit_log TO public;

Correct approach:

REVOKE ALL ON audit_log FROM public; GRANT SELECT ON audit_log TO admin_role;

Root cause:

Not restricting permissions allows unauthorized users to view or modify audit logs.

Key Takeaways

Row-level triggers automatically record every change to individual rows, creating a detailed audit trail.

They run inside the database, ensuring audit logs are consistent and cannot be bypassed by applications.

Properly designed audit logs include who made the change, what changed, and when, supporting security and compliance.

Triggers can impact performance on bulk operations, so strategies like batching or disabling during imports are important.

Securing audit logs with permissions and combining with session user info makes them trustworthy and useful.

Practice

1. What is the main purpose of a row-level trigger in Supabase for audit logs?

easy

A. To automatically record changes made to individual rows in a table

B. To speed up database queries by caching results

C. To create new tables automatically when data is inserted

D. To delete old data from the database periodically

Solution

1. Step 1: Understand what row-level triggers do

   Row-level triggers activate for each row affected by an insert, update, or delete operation.

2. Step 2: Connect triggers to audit logs

   They are used to record changes to rows, capturing who changed what and when.

3. Final Answer:

   To automatically record changes made to individual rows in a table -> Option A

4. Quick Check:

   Row-level trigger purpose = record row changes [OK]

Hint: Triggers log row changes automatically for audit [OK]

Common Mistakes:

• Thinking triggers speed up queries
• Confusing triggers with table creation
• Assuming triggers delete old data

2. Which of the following is the correct syntax to create a row-level trigger in Supabase that fires AFTER an UPDATE on a table named users?

easy

A. CREATE TRIGGER user_update AFTER INSERT ON users FOR EACH ROW EXECUTE FUNCTION audit_log();

B. CREATE TRIGGER user_update BEFORE UPDATE ON users FOR EACH STATEMENT EXECUTE FUNCTION audit_log();

C. CREATE TRIGGER user_update AFTER UPDATE ON users FOR EACH ROW EXECUTE FUNCTION audit_log();

D. CREATE TRIGGER user_update AFTER UPDATE ON users FOR EACH STATEMENT EXECUTE FUNCTION audit_log();

Solution

1. Step 1: Identify trigger timing and event

   The question asks for AFTER UPDATE trigger on table users.

2. Step 2: Confirm row-level execution

   Row-level triggers use FOR EACH ROW, not FOR EACH STATEMENT.

3. Final Answer:

   CREATE TRIGGER user_update AFTER UPDATE ON users FOR EACH ROW EXECUTE FUNCTION audit_log(); -> Option C

4. Quick Check:

   AFTER UPDATE + FOR EACH ROW = CREATE TRIGGER user_update AFTER UPDATE ON users FOR EACH ROW EXECUTE FUNCTION audit_log(); [OK]

Hint: Use AFTER UPDATE and FOR EACH ROW for row-level update triggers [OK]

Common Mistakes:

• Using BEFORE instead of AFTER
• Using FOR EACH STATEMENT instead of FOR EACH ROW
• Using INSERT event instead of UPDATE

3. Given this trigger function snippet in Supabase SQL:

CREATE OR REPLACE FUNCTION audit_log() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table(user_id, action, changed_at)
  VALUES (NEW.id, TG_OP, NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

What will happen when a row in the users table is updated?

medium

A. A new row is inserted into audit_table with the user's id, action type, and timestamp

B. The updated row in users is deleted

C. The trigger function will cause a syntax error and fail

D. Nothing happens because OLD is not used

Solution

1. Step 1: Analyze the trigger function logic

   The function inserts a row into audit_table with NEW.id, operation type TG_OP, and current time.

2. Step 2: Understand trigger behavior on update

   On update, NEW holds the new row data, so the insert logs the change.

3. Final Answer:

   A new row is inserted into audit_table with the user's id, action type, and timestamp -> Option A

4. Quick Check:

   Trigger inserts audit record on update = A new row is inserted into audit_table with the user's id, action type, and timestamp [OK]

Hint: Trigger inserts audit row using NEW and TG_OP on update [OK]

Common Mistakes:

• Thinking OLD is required for insert
• Assuming trigger deletes rows
• Confusing syntax causing errors

4. You wrote this trigger function to log deletes:

CREATE OR REPLACE FUNCTION audit_log_delete() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table(user_id, action, changed_at)
  VALUES (NEW.id, TG_OP, NOW());
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

But it fails when deleting rows. What is the error?

medium

A. TG_OP cannot be used in trigger functions

B. RETURN OLD is invalid in DELETE triggers

C. NOW() is not allowed in trigger functions

D. Using NEW.id on DELETE triggers causes an error because NEW is NULL

Solution

1. Step 1: Understand DELETE trigger context

   On DELETE, NEW is NULL because the row is removed; OLD holds the deleted row.

2. Step 2: Identify the error in the function

   The function uses NEW.id which is NULL on DELETE, causing failure.

3. Final Answer:

   Using NEW.id on DELETE triggers causes an error because NEW is NULL -> Option D

4. Quick Check:

   DELETE triggers use OLD, not NEW [OK]

Hint: Use OLD, not NEW, in DELETE triggers to avoid errors [OK]

Common Mistakes:

• Using NEW in DELETE triggers
• Thinking RETURN OLD is invalid
• Believing TG_OP or NOW() cause errors

5. You want to create an audit log that records who updated a row in the orders table, including old and new values. Which approach correctly combines row-level triggers and audit logging in Supabase?

hard

A. Create a trigger function that only logs NEW data and create a BEFORE INSERT trigger on orders

B. Create a trigger function that inserts into audit_orders using both OLD and NEW row data, then create AFTER UPDATE trigger FOR EACH ROW on orders

C. Create a trigger function that deletes old rows from orders and logs the deletion

D. Create a trigger function that logs changes only when rows are inserted, ignoring updates

Solution

1. Step 1: Identify audit log needs

   You want to log who updated rows and capture old and new values for changes.

2. Step 2: Choose correct trigger timing and data

   Use AFTER UPDATE trigger FOR EACH ROW to access OLD and NEW data and insert audit record.

3. Step 3: Confirm audit table insert logic

   The trigger function should insert both OLD and NEW values into audit_orders for full change history.

4. Final Answer:

   Create a trigger function that inserts into audit_orders using both OLD and NEW row data, then create AFTER UPDATE trigger FOR EACH ROW on orders -> Option B

5. Quick Check:

   Use AFTER UPDATE + OLD/NEW + FOR EACH ROW for full audit [OK]

Hint: Use AFTER UPDATE trigger with OLD and NEW for full audit logs [OK]

Common Mistakes:

• Logging only NEW data misses old values
• Using BEFORE INSERT triggers for updates
• Deleting rows instead of logging changes