1. My goal:
I'm building a comprehensive audit logging system for my vehicle inventory management platform. I need to track every database change (INSERT, UPDATE, DELETE) with the correct user identity in a PostgreSQL history table. The audit trail must be tamper-proof - users should not be able to manipulate or fake who performed an action.
My PostgreSQL trigger function uses current_setting('app.current_user') to capture the user who made each change, but I need to set this session variable from Retool before each query executes.
2. Issue:
Retool SQL queries don't support multiple statements separated by semicolons, so I cannot do this in a single query:
This doesn't work in Retool
SET LOCAL app.current_user = 'John Smith';
UPDATE vehicles SET status = 'Sold' WHERE id = 123;
I've tried using CTEs (Common Table Expressions) to set the user context and run the query:
WITH _user AS (
SELECT set_config('app.current_user', '{{ current_user.fullName }}', false)
)
UPDATE vehicles SET status = {{ newStatus }} WHERE id = {{ vehicleId }}
RETURNING *;
However, the PostgreSQL trigger fires BEFORE the session variable is set by the CTE, resulting in "User context not set" errors.
3. Steps I've taken to troubleshoot:
Created a separate setUserContext query that I call before each update:
SELECT set_config('app.current_user', {{ author }}, true) as user_set;
Modified my event handlers to chain queries:
await setUserContext.trigger({ author: current_user.fullName });
await updateVehicleStatus.trigger({ vehicleId, newStatus });
This works, but I have 50+ queries across my app that modify data, and manually chaining setUserContext before each one is:
- Error-prone (easy to forget)
- Repetitive code
- Hard to maintain
4. Additional info:
Environment: Self hosted Retool
Subscription: Business
DB: Postgres 17
Retool version: 3.284.0
What I'm looking for:
-
Is there a way to configure PostgreSQL resource in Retool to automatically execute a statement before EVERY query? (Like a connection-level hook or interceptor)
-
Does Retool have query middleware or pre-query execution hooks that run automatically before database operations?
-
Are there resource-level configuration options that could inject user context automatically (like connection string parameters)?
-
How do other teams handle audit logging with user context in Retool + PostgreSQL? What's the recommended pattern?
UPDATE
Current Issues:
-
Connection pooling makes session variables unreliable, “SET LOCAL app.current_user = 'John Smith';” this cant be considered as a solution
-
No consistent way to ensure user context is set before every database operation
