Row-level triggers for audit logs in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

What happens when a row-level trigger logs an update in Supabase?

Consider a Supabase table users with a row-level trigger that logs updates to an audit_logs table. What is the expected behavior when a user record is updated?

Supabase

CREATE OR REPLACE FUNCTION log_user_update() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_logs(table_name, operation, changed_data, changed_at)
  VALUES ('users', 'UPDATE', row_to_json(NEW), now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_update_trigger
AFTER UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION log_user_update();

A The trigger prevents the update and logs the old data instead.

B The trigger logs the update but only if the updated row has a specific column set to true.

C No entry is added to <code>audit_logs</code> because AFTER triggers do not run on UPDATE.

D An entry is added to <code>audit_logs</code> with the new row data every time a user is updated.

💡 Hint

Think about what an AFTER UPDATE trigger does and what row_to_json(NEW) returns.

The AFTER UPDATE trigger runs after a row is updated and inserts a new record into audit_logs with the new row data. This captures the latest state of the row after the update.

❓ Architecture

intermediate

Which design best ensures audit logs capture all changes without blocking user operations?

You want to track all INSERT, UPDATE, and DELETE operations on a payments table in Supabase. Which design best captures all changes while minimizing impact on user experience?

A Use AFTER row-level triggers that write audit logs synchronously, causing user operations to wait.

B Use BEFORE statement-level triggers that block the operation until audit logs are written synchronously.

C Use AFTER row-level triggers for INSERT, UPDATE, DELETE that insert audit records asynchronously into <code>payment_audit</code> table.

D Use a scheduled job that queries <code>payments</code> every minute and writes audit logs for changes.

💡 Hint

Consider how AFTER triggers and asynchronous inserts affect user operation latency.

AFTER row-level triggers run after the operation and can insert audit logs without blocking the main operation. Writing asynchronously reduces user wait time while ensuring all changes are logged.

❓ security

advanced

How to secure audit log triggers against tampering in Supabase?

You want to ensure that audit logs created by row-level triggers cannot be tampered with by users. Which approach best secures the audit logs?

A Grant users only INSERT permission on the main table; audit log table is owned by a separate role with restricted access.

B Allow users to update audit logs so they can correct mistakes in logs.

C Store audit logs in the same table with a flag column to mark changes.

D Disable triggers for users with admin roles to prevent accidental logs.

💡 Hint

Think about how permissions and ownership affect data integrity.

Restricting audit log table access to a separate role and granting users only necessary permissions on the main table prevents users from modifying audit logs, preserving their integrity.

❓ Configuration

advanced

What error occurs if a row-level trigger function returns NULL in Supabase?

Given a row-level trigger function in Supabase that returns NULL instead of NEW or OLD, what happens when the trigger fires?

Supabase

CREATE OR REPLACE FUNCTION faulty_trigger() RETURNS TRIGGER AS $$
BEGIN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_trigger
BEFORE INSERT ON test_table
FOR EACH ROW EXECUTE FUNCTION faulty_trigger();

A The trigger is ignored and the insert proceeds normally.

B The insert succeeds but the row is discarded silently.

C The insert operation fails with a runtime error because the trigger returned NULL.

D The database logs a warning but inserts the row anyway.

💡 Hint

Consider what returning NULL means in a BEFORE row-level trigger.

In BEFORE row-level triggers, returning NULL skips the row operation silently without raising an error. The insert succeeds but the row is not inserted.

✅ Best Practice

expert

How to design audit log triggers to handle bulk operations efficiently in Supabase?

You expect bulk INSERT, UPDATE, and DELETE operations on a large table. How should you design row-level audit log triggers to handle this efficiently without causing performance issues?

A Use row-level triggers that write audit logs asynchronously with a queue system.

B Keep row-level triggers that insert audit logs synchronously for every row to ensure detailed logs.

C Disable triggers during bulk operations and run a separate audit job afterward.

D Use statement-level triggers to log bulk operations once instead of row-level triggers for each row.

💡 Hint

Think about balancing detailed logging with performance during bulk changes.

Row-level triggers with asynchronous logging (e.g., using a queue) allow capturing detailed audit logs without blocking bulk operations, improving performance and reliability.

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