Audit Logs on Retool Applications

🤝 Community Show & Tell
1

For the majority of SaaS applications, there is an intrinsic requirement to identify who has performed a specific action for audit purposes. Because of that, I have defined a standard configuration to be reused across applications whenever it is requested.

The first part of this configuration involves creating an audit logs table, which will be used to store any action performed on the applications. The following command describes how to build it:

CREATE TABLE audit_logs (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name TEXT NOT NULL,
    operation_type TEXT NOT NULL,
    record_id TEXT NOT NULL,
    old_value JSON,
    new_value JSON,
    changed_by TEXT NOT NULL,
    change_timestamp TIMESTAMP NOT NULL,
);

Next, for each table to be audited, an additional column called 'changed_by' is needed. This column will be used to identify the last user who performed changes on the row. In the example below, the table customers will be configured as an audited table in the application:

ALTER TABLE customers ADD COLUMN changed_by TEXT;

After adding the column changed_by on the table customers, it is necessary to create a PostgreSQL function that will be used to store the audited row whenever the table customers is changed:

CREATE OR REPLACE FUNCTION trg_audit_customers_update_func()
RETURNS TRIGGER LANGUAGE PLPGSQL AS $$
BEGIN
    INSERT INTO audit_logs (table_name, operation_type, record_id, old_value, new_value, changed_by, change_timestamp)
    VALUES (
        'customers',
        CASE
	  WHEN TG_OP = 'INSERT' THEN 'INSERT'
	  WHEN NEW.deleted_at IS NOT NULL THEN 'DELETE'
	  ELSE 'UPDATE'
	END,
        NEW.id,
        to_jsonb(OLD),
        to_jsonb(NEW),
        NEW.changed_by,
        NOW()
    );
    RETURN NEW; -- For AFTER triggers, you can return NEW or NULL.
END;
$$;

Finally, we need to create a trigger to execute the function above whenever a change is performed on the table customers:

CREATE OR REPLACE TRIGGER trg_audit_customers_update
AFTER UPDATE OR INSERT ON customers
FOR EACH ROW
EXECUTE FUNCTION trg_audit_customers_update_func();

As noted, you’ll need to create one function and one trigger for every table to be audited on the application.

Once we have all this configuration for auditing, it is possible to create a page in your Retool Apps to show details about the changes made to the records. Here’s a video to illustrate:

Note: This post assumes that the database being used is a Postgres database. For auditing on any other kind of database, you should review the function and the trigger syntax.

8 Likes
2

Amazing!!!