TL;DR: Application-layer logging is insufficient for SOC2 compliance because it misses manual database modifications and batch updates. This post demonstrates how to build a tamper-proof, append-only audit trail directly in PostgreSQL using PL/pgSQL triggers and a dedicated
auditschema. You will also learn how to inject Node.js user IDs into PostgreSQL session variables to maintain application-level context for every database mutation.
⚡ Key Takeaways
- Stop relying on ORM lifecycle hooks or Express controllers for audit logs, as they fail to capture manual database interventions and schema migrations.
- Isolate your audit trail by creating a dedicated
auditschema with revoked public access to enforce strict security policies and prevent data pollution. - Use PostgreSQL's
JSONBcolumn type to store genericoriginal_dataandchanged_datastates, allowing a single table to log mutations from any table in your database. - Pass the Node.js API user ID into the database transaction using
current_setting('app.current_user_id', true)to track the exact end-user responsible for the change. - Implement a generic PL/pgSQL trigger function (
audit.if_modified_func()) to automatically captureINSERT,UPDATE, andDELETEoperations at the disk level. - Always use parameterized queries in Node.js to prevent sensitive PII from leaking into the raw
client_querycolumn of your audit logs.
You are preparing for your SOC2 Type II audit, and the auditor asks a terrifying question: "Can you prove that a developer with database access didn't manually modify this customer's financial record?"
If your audit logging system lives entirely within your Node.js application logic, the answer is no.
Relying on application-layer logging—like adding await auditService.log(change) to your Express controllers or using ORM lifecycle hooks—is a fundamentally flawed approach to compliance. It misses manual database interventions, schema migrations, and batch updates. It is susceptible to application bugs, race conditions, and bypasses. Furthermore, it clutters your business logic, bloating your controllers with cross-cutting concerns that slow down API response times.
The solution is to push the responsibility of generating an append-only audit trail down to the data layer itself. By leveraging PostgreSQL triggers and JSONB, we can capture every single INSERT, UPDATE, and DELETE at the exact moment it hits the disk. To maintain application-level context (like who made the API request), we can inject the Node.js user ID directly into the PostgreSQL transaction session.
When architecting custom backend development and API services for compliance-bound startups, this database-first approach is our gold standard. Here is exactly how to build it.
The Architecture of a Tamper-Proof Audit Schema
To keep our database organized, we will isolate our audit logs from our application data by creating a dedicated audit schema. This prevents accidental pollution of the public schema and allows us to apply distinct security policies to the audit data.
Our primary table, logged_actions, needs to be generic enough to store changes from any table in our database. We achieve this by relying heavily on JSONB to store the row state before and after the mutation.
-- 1. Create a dedicated schema
CREATE SCHEMA audit;
REVOKE ALL ON SCHEMA audit FROM public;
-- 2. Create the audit table
CREATE TABLE audit.logged_actions (
event_id BIGSERIAL PRIMARY KEY,
schema_name TEXT NOT NULL,
table_name TEXT NOT NULL,
relid OID NOT NULL,
session_user_name TEXT NOT NULL,
action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL,
action TEXT NOT NULL CHECK (action IN ('I', 'D', 'U', 'T')),
application_user_id TEXT, -- Injected from Node.js
original_data JSONB,
changed_data JSONB,
statement_only BOOLEAN NOT NULL,
client_query TEXT
);
-- 3. Add indexing for fast querying by table and time
CREATE INDEX logged_actions_relid_idx ON audit.logged_actions(relid);
CREATE INDEX logged_actions_action_tstamp_tx_idx ON audit.logged_actions(action_tstamp_tx);
CREATE INDEX logged_actions_app_user_idx ON audit.logged_actions(application_user_id);
Notice the application_user_id column. PostgreSQL knows which database role is executing the query (session_user_name), but an auditor needs to know which end-user triggered the API request. We will populate this later using session variables.
Production Note: Storing the
client_queryis excellent for debugging, but it can leak sensitive PII if raw values are passed in the SQL string. Ensure you are always using parameterized queries in your Node.js application to keep PII out of theclient_querylogs.
Crafting the PostgreSQL Audit Trigger Function
Next, we write the PL/pgSQL trigger function. This function will be executed automatically by PostgreSQL immediately after a row is modified.
Our function needs to determine the operation type (TG_OP), capture the row data, and read our custom Node.js session variable to identify the user.
CREATE OR REPLACE FUNCTION audit.if_modified_func()
RETURNS TRIGGER AS $body$
DECLARE
audit_row audit.logged_actions;
app_user text;
BEGIN
-- Extract the application user ID injected by Node.js
-- The 'true' parameter prevents an error if the setting is missing
BEGIN
app_user := current_setting('app.current_user_id', true);
EXCEPTION WHEN OTHERS THEN
app_user := NULL;
END;
audit_row = ROW(
nextval('audit.logged_actions_event_id_seq'), -- event_id
TG_TABLE_SCHEMA::text, -- schema_name
TG_TABLE_NAME::text, -- table_name
TG_RELID, -- relid
session_user::text, -- session_user_name
current_timestamp, -- action_tstamp_tx
substr(TG_OP, 1, 1), -- action (I, U, D)
app_user, -- application_user_id
NULL, NULL, -- original_data, changed_data
'f', -- statement_only
current_query() -- client_query
);
IF (TG_OP = 'UPDATE') THEN
audit_row.original_data = to_jsonb(OLD.*);
audit_row.changed_data = to_jsonb(NEW.*);
-- Prevent log bloat by ignoring updates that didn't change any values
IF audit_row.original_data = audit_row.changed_data THEN
RETURN NEW;
END IF;
INSERT INTO audit.logged_actions VALUES (audit_row.*);
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
audit_row.original_data = to_jsonb(OLD.*);
INSERT INTO audit.logged_actions VALUES (audit_row.*);
RETURN OLD;
ELSIF (TG_OP = 'INSERT') THEN
audit_row.changed_data = to_jsonb(NEW.*);
INSERT INTO audit.logged_actions VALUES (audit_row.*);
RETURN NEW;
END IF;
RETURN NULL;
END;
$body$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public;
To apply this trigger to a table (e.g., users), we run:
CREATE TRIGGER audit_users_changes
AFTER INSERT OR UPDATE OR DELETE ON public.users
FOR EACH ROW EXECUTE FUNCTION audit.if_modified_func();
By using FOR EACH ROW, the trigger executes for every single record modified, even during massive bulk UPDATE operations. By making the function SECURITY DEFINER, it executes with the privileges of the user who created it. This means standard application database roles can log audits without needing direct write access to the audit schema.
Injecting Node.js Context into PostgreSQL Sessions
Here lies the most critical integration point. PostgreSQL knows nothing about your Express.js authentication middleware or JWTs. To link a database mutation to an API user, we must utilize PostgreSQL's set_config function within the same transaction that executes our queries.
When utilizing Connection Pooling (via the pg driver or ORMs like Prisma and TypeORM), connections are reused across multiple HTTP requests. If you set a session variable globally, it will leak into other requests.
We must wrap our queries in a transaction and set the configuration to LOCAL. This ensures the variable is scoped strictly to the current transaction and is cleared immediately upon COMMIT or ROLLBACK.
// Using the standard 'pg' library
const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
/**
* Wraps a database operation in a transaction and injects the user ID
* into the PostgreSQL session context for audit logging.
*/
async function executeWithAuditContext(userId, queryFunc) {
const client = await pool.connect();
try {
await client.query('BEGIN');
// Inject the application context safely as a LOCAL variable.
// The 'true' argument sets is_local, ensuring it only lasts for this transaction.
await client.query(`SELECT set_config('app.current_user_id', $1, true)`, [userId]);
// Execute the actual business logic
const result = await queryFunc(client);
await client.query('COMMIT');
return result;
} catch (error) {
await client.query('ROLLBACK');
throw error;
} finally {
// Release the client back to the pool
client.release();
}
}
// Example Usage in an Express Controller
app.post('/api/users/:id', authenticate, async (req, res) => {
const adminUserId = req.user.id; // Extracted from JWT middleware
const targetUser = req.params.id;
const { newStatus } = req.body;
try {
await executeWithAuditContext(adminUserId, async (client) => {
await client.query('UPDATE users SET status = $1 WHERE id = $2', [newStatus, targetUser]);
});
res.json({ success: true });
} catch (err) {
console.error('Update failed:', err);
res.status(500).json({ error: 'Update failed' });
}
});
Warning: Never use
SET app.current_user_id = '...'without making itLOCAL(which thetrueparameter inset_configrepresents). Doing so will permanently attach the user ID to the pooled connection, causing subsequent requests from completely different users to be audited under the wrong identity.
Securing the Audit Trail Against Tampering
A core tenet of SOC2 compliance is proving that audit logs cannot be altered after the fact. If a rogue administrator or a compromised application container gains access to the database, they might try to cover their tracks by running DELETE FROM audit.logged_actions.
We must explicitly strip mutation rights from the audit table:
-- Ensure the public and standard application roles cannot modify the table
REVOKE ALL ON audit.logged_actions FROM public;
-- Grant only INSERT privileges to the role your Node app uses
GRANT INSERT ON audit.logged_actions TO application_role;
-- Grant SELECT to your reporting/admin roles
GRANT SELECT ON audit.logged_actions TO admin_role;
To take it a step further, we can apply a hard table-level constraint to prevent anyone (even the table owner) from issuing UPDATE, DELETE, or TRUNCATE commands. We do this by creating a trigger that strictly raises an exception:
CREATE OR REPLACE FUNCTION audit.prevent_tampering()
RETURNS TRIGGER AS $$
BEGIN
RAISE EXCEPTION 'Audit logs are append-only. Tampering is strictly prohibited.';
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER enforce_append_only
BEFORE UPDATE OR DELETE OR TRUNCATE ON audit.logged_actions
FOR EACH STATEMENT EXECUTE FUNCTION audit.prevent_tampering();
With this in place, even a user with SUPERUSER privileges would have to explicitly drop the trigger to modify the logs—an action that would itself be recorded in PostgreSQL's internal server logs, creating an unavoidable paper trail for your auditor.
Querying the Revision History
One of the secondary benefits of this architecture is the ability to expose "Revision History" or "Time Travel" features in your frontend application. Because we store the full original_data and changed_data payloads as JSONB, querying the historical state of a record is trivial.
To find exactly when a specific user's email address was changed and who changed it, we can leverage PostgreSQL's JSONB extraction operators (->>):
SELECT
action_tstamp_tx AS changed_at,
application_user_id AS changed_by_user,
original_data->>'email' AS old_email,
changed_data->>'email' AS new_email
FROM
audit.logged_actions
WHERE
table_name = 'users'
AND action = 'U'
AND changed_data->>'id' = 'uuid-of-the-target-user'
AND original_data->>'email' IS DISTINCT FROM changed_data->>'email'
ORDER BY
action_tstamp_tx DESC;
This query cleanly extracts only the events where the email field was modified, completely bypassing the need for complex application-layer diffing libraries.
Managing Audit Data Growth with Native Partitioning
The most common objection to database-level audit logging is storage bloat. If your application processes 500 transactions per second, your audit.logged_actions table will grow by millions of rows a day. Index bloat will eventually degrade database performance.
To mitigate this, we utilize native PostgreSQL Table Partitioning. By partitioning the audit table by date, we can easily drop or archive older partitions without locking the main table or executing expensive DELETE queries.
-- 1. Create a partitioned version of our table
-- Note: When partitioning, the partition key (action_tstamp_tx) must be part of the Primary Key.
CREATE TABLE audit.logged_actions_partitioned (
event_id BIGSERIAL,
schema_name TEXT NOT NULL,
table_name TEXT NOT NULL,
relid OID NOT NULL,
session_user_name TEXT NOT NULL,
action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL,
action TEXT NOT NULL,
application_user_id TEXT,
original_data JSONB,
changed_data JSONB,
statement_only BOOLEAN NOT NULL,
client_query TEXT,
PRIMARY KEY (event_id, action_tstamp_tx)
) PARTITION BY RANGE (action_tstamp_tx);
-- 2. Create partitions for current and upcoming months
CREATE TABLE audit.logged_actions_y2026m04
PARTITION OF audit.logged_actions_partitioned
FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE audit.logged_actions_y2026m05
PARTITION OF audit.logged_actions_partitioned
FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
When an auditor requests data from last year, or when your compliance policy dictates that logs must be moved to cold storage after 90 days, you can simply detach the oldest partition using ALTER TABLE ... DETACH PARTITION, dump it to a CSV or Parquet file, push it to AWS S3, and drop the table entirely from PostgreSQL.
This workflow keeps your primary database lean, performant, and 100% compliant.
Need help building this in production?
SoftwareCrafting is a full-stack dev agency — we ship fast, scalable React, Next.js, Node.js, React Native & Flutter apps for global clients.
Get a Free ConsultationFrequently Asked Questions
What is the main advantage of migrating to this new architectural pattern?
Adopting this architecture decouples your frontend from your backend services, allowing your teams to deploy independently and scale specific system components based on traffic. It also significantly improves fault tolerance, as a failure in one microservice won't necessarily bring down your entire application.
How do we handle data consistency across distributed services?
Managing data consistency requires shifting from traditional ACID transactions to eventual consistency models using event-driven communication. Implementing patterns like the Saga pattern or outbox pattern ensures that all services eventually synchronize. If your team is struggling with distributed data management, SoftwareCrafting services can help design and implement robust event-driven architectures tailored to your needs.
Will this approach increase our infrastructure costs?
Initially, there may be a slight increase in infrastructure overhead due to the need for API gateways, message brokers, and container orchestration. However, in the long run, it reduces costs by allowing you to allocate compute resources only to the specific services that experience heavy load, rather than scaling a massive monolith.
How do we secure communication between internal services?
Internal service-to-service communication should never be implicitly trusted, even within a private network. You should implement mutual TLS (mTLS) using a service mesh to encrypt traffic and enforce strict identity-based access control policies between your microservices.
What is the best way to monitor and debug issues in this setup?
You must implement centralized logging and distributed tracing (using tools like OpenTelemetry) to track requests as they travel across multiple service boundaries. Establishing comprehensive observability is a foundational step we prioritize when delivering SoftwareCrafting services, ensuring you can quickly pinpoint latency bottlenecks and errors.
How do we manage database migrations without causing downtime?
Zero-downtime migrations require a multi-step approach, typically utilizing the expand-and-contract pattern. You first add the new database schema alongside the old one, update your application to write to both, migrate the historical data, and finally deprecate the old schema once everything is stable.
📎 Full Code on GitHub Gist: The complete
retry-handler.tsfrom this post is available as a standalone GitHub Gist — copy, fork, or embed it directly.