Building Automatic, Contextual Clinical Trial Audit Logging in Postgres Without Triggers

Software subject to 21 CFR Part 11 requirements cannot afford best-effort audit logging: missing even a few records is a compliance failure. We needed audit trails that triggered automatically on every write to Postgres and carried rich business context. Here's how we built it in an ergonomic, maintainable, and secure way — using app-native TypeScript, without relying on baroque PL/pgSQL logic.

Clinical trial software is famously subject to the stringent requirements of 21 CFR Part 11. In short, the regulation requires that all changes to clinical trial data be recorded in a secure, tamper-evident audit trail. This means that every time our application writes clinical data to the database, it must also create an audit log entry that captures who made the change, when it happened, what the before and after values were, and why the change was made. Missing even a few audit records is a compliance failure — absolutely unacceptable in our domain.

So how did we do it? By adding a very carefully-written ESLint rule that ensured that every single db.update(...) or db.insert(...) call was accompanied by a corresponding audit log insert, we were able to achieve full compliance quickly:

await db
  .update(subjects)
  .set({ humanReadableId: "SUBJECT-001A" })
  .where(eq(subjects.id, subjectId));

// If not present, ESLint throws an error!
await db.insert(subjectsAuditLogs).values({
  rowId: subjectId,
  rowAction: "update",
  performedByUserId: userId,

  column: "humanReadableId",
  previousValue: oldSubjectId,
  newValue: "SUBJECT-001A"
});

Just kidding... I cannot imagine how I'd even begin to write a rule that would catch every possible path for data mutation — especially across subqueries, CTEs, nested transactions, and other complex query patterns.

And since no lint rule exists, that's exactly why this approach is a non-starter: it makes the compliance behavior optional and easy to forget. Can we really trust that every engineer will remember to write to the audit log appropriately, every time, in every function that mutates data? Even if we could, how do we ensure that calls to write to the log are kept after big refactors or rapid hotfixes? The risk of human error is just too high.

If we wanted to be truly confident in Harbor's compliance story, we couldn't "just add audit logs" as an afterthought — we needed to make it impossible to mutate data without also generating the necessary audit trail. That meant we needed to look a little deeper than the business logic itself...

Initial Options: pgAudit or PL/pgSQL triggers

The obvious first approach was pure database-level audit logging. In fact, we do enable pgAudit on our Cloud SQL instances, and it logs every SQL statement that hits our database.

But SQL statement logging is not the same thing as application audit history. For changes to clinical data, especially for 21 CFR Part 11 compliance, we need to answer questions like:

1. Which user performed the action?
2. What study role were they acting under?
3. Within that role, which permission authorized the action?
4. Was there a human-readable reason message attached?
5. What was the IP address and user agent involved in the request?
6. What did the row look like before and after the change?

pgAudit is very good at telling you what database statement ran. It is not, by itself, a great place to encode all of the application context needed to make a clinical audit trail useful to study staff, compliance teams, or regulators. So we needed to look elsewhere.

Moving up one level of abstraction, database triggers were the next obvious option. Postgres has robust support for triggers; they enable logic to be run after every INSERT, UPDATE, and DELETE statement executed against a table. The downside is that triggers need to be written in Postgres's (relatively) obscure internal procedural language, PL/pgSQL, and taking this approach would mean we'd be pushing a concerning amount of Harbor-specific application semantics — like row diffing, permissions logic, etc. — into code written in this unfamiliar language.

Moreover, the ability to get application context into the trigger in the first place seemed unergonomic. A trigger can very easily see the row being inserted or updated (e.g., via the NEW and OLD variables injected into the trigger function); it cannot naturally see the authenticated Harbor user, the active study role, the permission that authorized the operation, or the written reason the user may have submitted for the change. To make that work, we would likely need to serialize large amounts of application data and use something like SET LOCAL before every relevant transaction.¹ That is definitely plausible, but it introduces more logic and complexity into a runtime we're decidedly less familiar with and is much harder to test and introspect than normal TypeScript code. And similarly to the opening example, there's nothing stopping an engineer from forgetting to SET LOCAL in the first place.

In short:

Database-level logging with pgAudit is an important part of our overall auditing strategy. However, in assessing the suitability of using database triggers to satisfy Part 11-specific logging requirements, we did not want our primary audit trail story to depend on complex, unfamiliar database machinery² that lacked first-class knowledge of the application...

What about the ORM?

So, we moved up another level, to the TypeScript ORM. At Harbor, we specifically use Drizzle. Drizzle has been awesome — we love defining our schemas in pure TypeScript and taking advantage of the strong table and column typings that are subsequently inferred — but at the end of the day, it is still a general-purpose ORM.

That is, Drizzle does not know that Harbor is a regulated clinical trial system. It will happily let code mutate a table containing clinical data without logging permission metadata, without logging before/after row diffs, and without saving a tamper-evident digest. From Drizzle's perspective, this is fine:

await db
  .update(subjects)
  .set({ humanReadableId: "SUBJECT-001A" })
  .where(eq(subjects.id, subjectId));

From our perspective, it is a compliance failure.

That said, we could have potentially written application-layer helpers that automatically wrote the appropriate audit logs and asked engineers to use them consistently:

await updateSubject(...);

// Could even be called inside of `updateSubject(...)` itself
await insertSubjectAuditLog(...);

But this is repetitive and leaves the critical compliance invariant distributed across every service method.

Instead, we decided to wrap the ORM itself — so product code could keep using the ORM-shaped API it was used to, while the platform made compliant behavior the default.

This was the API we aimed for:

// `auditLogCtx` contains all of the user, permission,
// and request context needed for a meaningful audit log
await db
  .withAuditLogCtx(auditLogCtx)
  .update(db.schema.subjects)
  .set({ humanReadableId: "SUBJECT-001A" })
  .where(eq(db.schema.subjects.id, subjectId));

Reads (i.e., db.select(...) calls) would stay effectively the same, but every other mutative database method would require .withAuditLogCtx(...) up front before it could be used.

That is: our db client would only expose the .select(...) method directly. Only after the call to .withAuditLogCtx(...) would the .insert(...), .update(...), etc. methods become available. These methods would be audited versions of the underlying Drizzle operations — i.e., they would automatically generate audit logs from the passed audit log context after the requested operation was completed.

By structuring our API like this, compliance is enforced by the type system yet the API remains familiar, making it natural for product code to stay compliant by default.

So, how do we actually implement this?

Start with the Schema

The first piece is the schema definition. Instead of defining audited tables with Drizzle's normal pgTable(...) helper...

export const subject = pgTable('subjects', {
  id: text().notNull().primaryKey(),
  studyId: text(),
  createdAt: timestamp(),
});

...we created a higher-level pgAuditedTable(...) helper that replaced all of our existing table definitions:

export const [
  subjects,
  subjectsAuditLogs,
] = pgAuditedTable({
  name: "subjects",
  columns: {
    id: text().notNull().primaryKey(),
    studyId: text(),
    createdAt: timestamp(),
  },
});

The helper creates the base table plus a companion audit log table with an *_audit_logs suffix in the name.

The audit log table contains consistent, standard columns needed for a meaningful audit log: the ID of the row being modified, the type of the modification (e.g. create, update), names of changed columns, before/after row JSON, the user ID behind the log, study permission IDs, a cryptographically-signed digest to ensure tamper-evidence, etc.

Rows in the audit logs table end up looking something like this:

Since the audit log table's columns are the same for every table regardless of the base table's underlying columns, we are able to write generic code that inserts logs into any audit log table on INSERT or UPDATE without needing to know the specific columns of the base table.

Then Proxy the ORM

After setting up a consistent audit log schema, we needed to wrap the ORM and write audit logs accordingly. To do this, we wrote a function createAuditedDb(...) that accepted a standard Drizzle database config, but instead of returning the raw Drizzle database client, returned a JavaScript Proxy.

The proxy intentionally hides mutable methods like .insert(...), .update(...), and .delete(...) from normal callers, and forces .withAuditLogCtx(...) to be called first:

const DRIZZLE_MUTABLE_METHODS = [
  "insert",
  "update",
  "delete",
  "transaction",
  "execute",
  "with",
  "$client",
];

export const db = new Proxy(rawDrizzleDbClient, {
  get(target_, prop, receiver) {
    if (
      typeof prop === "string" &&
      DRIZZLE_MUTABLE_METHODS.includes(prop)
    ) {
      throw new Error("Call .withAuditLogCtx(...) before calling mutable methods");
    }

    if (prop === "withAuditLogCtx") {
      return (ctx: AuditLogContext): AuditedMethods => {
        return {
          insert() {
            // implementation that writes an audit log
            // after the `INSERT`...
          },

          update() {
            // implementation that writes an audit log
            // after the `UPDATE`...
          },

          // remaining audited methods...

        }
      };
    }

    return Reflect.get(target_, prop, receiver);
  },

  // remaining `Proxy` traps...

}) as Omit<
  typeof rawDrizzleDbClient,
  (typeof DRIZZLE_MUTABLE_METHODS)[number]
> & { withAuditLogCtx: (ctx: AuditLogContext) => AuditedMethods };

Instead, callers see a narrower interface:

1. normal .select(...)
2. .withAuditLogCtx(...) for audited writes
3. audited .insert(...), .update(...), .delete(...), and .transaction(...) only available after calling .withAuditLogCtx(...)

Why a Proxy? Ideally, we wouldn't have used one — I'm generally skeptical of language features that aren't widely used in the ecosystem, and using a Proxy means that there's no getting around an unsafe as cast,³ which we use extremely sparingly across the codebase otherwise.

Unfortunately, though, Drizzle database clients appear to carry complex internal state. While the cleanest "proxy" may have been to create a brand-new object and re-expose a subset of methods, it did not seem to work, even after re-binding the internal method calls to the original instance (e.g., insert.bind(rawDrizzleDbClient)).

At a certain point, we had to make a decision between (a) digging deeper into Drizzle internals or (b) just Proxying the original instance, gating access to the mutable methods documented in the public API, and enforcing that access gate via the type system. The latter was ultimately more pragmatic.

With the audited wrapper written, the following noncompliant code no longer passed typechecking:

await db.update(db.schema.subjects).set({
  humanReadableId: "SUBJECT-001A",
});

And the developer is required to provide audit log context first:

await db
  .withAuditLogCtx({
    performedByUserId: user.id,
    studyPermissionId: permission.id,
    ipAddress,
    userAgent,
    reasonMessage: "Corrected subject identifier",
  })
  .update(db.schema.subjects)
  .set({ humanReadableId: "SUBJECT-001A" })
  .where(eq(db.schema.subjects.id, subjectId));

Wrapped Method Implementation

So what do the wrapped .insert(...), .update(...), etc. methods do, exactly? They "look" like the original Drizzle methods, but they add the missing Part-11-compliant audit logging behavior transparently. Importantly, writes to the audit log are wrapped in a transaction: either the data change and the audit row commit together, or neither does. There is no intermediate state where e.g., a subject is updated but the audit record fails to insert afterwards.

As a concrete example, the wrapped .update(...) method essentially does the following:

1. BEGINs a new transaction.
2. Selects (FOR UPDATE, to make sure we're getting a consistent snapshot) the current version of the rows.⁴
3. Runs the requested UPDATE.
4. Captures the updated rows.
5. Creates a standardized audit log payload from (a) the "before" and "after" row snapshots and (b) the audit log context (e.g., IP address, user permissions, etc.).
6. Cryptographically signs the audit log payload with a secret key for tamper-evidence.
7. Inserts the payload and the signature together as the final "audit log row".
8. COMMITs the overall transaction.

INSERTs work similarly. DELETEs are more complex, given regulatory requirements surrounding data retention and the technical challenge of dealing with foreign keys and cascading⁵ — but the broad strokes are similar.⁶

So we're now Part 11 Compliant?

This audit logging system does not make Harbor Part-11-compliant by itself. No clever programming abstraction replaces validation, SOPs, backups, or the rest of a compliant system (and we have written separately about how Harbor approaches software compliance more generally if you want more details there).

But what our audit logging abstraction does do is make several Part 11-critical behaviors systemic...

1. Data changes produce audit rows automatically.
2. Audit rows include actor and permission context.
3. Before and after values are preserved for row mutations.
4. Audit rows are inserted transactionally with the underlying data change.
5. Audit payloads are signed for later integrity verification.

...which makes it that much easier for us to meet the requirements of Part 11 in the aggregate.

Tradeoffs

With respect to tradeoffs:

• For one, we now own a very custom, arguably arcane abstraction layer over our ORM. That is not free. Proxy is an obscure feature of modern JavaScript and dealing with Drizzle internals does get quite complex.
• We also need to ensure that all writes go through this audited layer; while we do lock down database roles (e.g., the default application user only has SELECT and INSERT on the audit log tables) — ultimately, triggers provide the strongest, lowest-level guarantee.
• There are also performance considerations: concurrent writes are less performant and there are more round trips between the application and the database when updating data in general.

But the alternatives — at least at our stage, with such a lean team — feel worse. We could've scattered compliance-critical behavior across hundreds of service methods and hoped code review caught every omission. Or we could've pushed this whole thing into database triggers and made our primary compliance story depend on a thicket of PL/pgSQL, application context plumbing, and complex migration tooling — faster in production, but much, much slower for our engineers to develop and debug. Neither option felt like the right approach for a small, product-focused team building a mission-critical, regulated product quickly.

Still, at a certain point, it might actually make sense to move some logic into database-level triggers, especially as the company grows and scales:

• Using triggers will allow us to avoid SELECT FOR UPDATE and use the OLD variable instead, which will unlock some performance gains.⁷
• We might be able to use pgcrypto to do the audit log signing inside the database itself, eliminating another (relatively fast, but still compute-bound) round trip.
• Ideally, the audit log API will be stable enough by then that we won't need to worry about changing the triggers too often, but if we do, we'll (hopefully) have a team owning the operational tooling to automatically and reliably deploy the most up-to-date logic across all study databases.

For now, though, keeping the database audit logging logic in TypeScript concentrates the complexity in a place where we have strong ecosystem familiarity, excellent tooling and test infrastructure, and granular operational control. While it may not be as performant or low-level as triggers, it does allow us to assert with much, much higher confidence that our audit logging system is complete and compliant from day one, which is ultimately the most important thing for our customers.

Conclusion

The best compliance abstractions are the ones product engineers do not have to think about every day: the ones that make doing the right thing easy. For Harbor, that meant treating audit logging as a platform-level infrastructure concern rather than as an application-layer afterthought and thinking deeply about the right API surface. Moreover, by integrating audit logging into tooling and systems that we are intimately familiar with, we not only make compliance easier for our engineers, but also give our customers and partners the utmost confidence in the correctness of our compliance architecture as a whole.

In normal SaaS, this might be overengineering. In regulated clinical software, it is just engineering.

Reach out if you are interested in engineering.

Footnotes

1. We're also using PgBouncer (in transaction pooling mode) to manage database connections, which makes me even more wary about using less common Postgres features.
   
   While SET LOCAL should work fine in transaction pooling mode, in general, my bias is to keep my queries and database access as simple and standard as possible if I can help it, to reduce the possible surface area of where strange bugs or edge cases could hide. ↩

2. Another consideration, more specific to our architecture itself, is that we create logically separate Postgres databases for each study. This helps us achieve a significantly higher level of data isolation and security for our customers than a traditional multitenant SaaS approach, but it also means any trigger-based logic would need to be duplicated across every study database.
   
   This is not insurmountable with the right migration tooling, but it is another layer of operational complexity to manage and reason about that ultimately pushed us away from the trigger-based approach. ↩

3. Unless in some strange future version of TypeScript, they embed a JavaScript engine into the compiler itself and evaluate Proxy traps at compile time? ↩

4. The FOR UPDATE clause blocks other writers, but does not block readers. We are comfortable with this behavior as we do not expect high contention on individual rows in our system (typically it will be just one coordinator at a time handling data entry for one subject), but it is something to be aware of. ↩

5. We actually mostly "archive" internally. The setup is similar to the audited tables setup in that our custom pgTable wrapper also creates an *_archive table and the .delete(...) method (actually called .archive(...)) transactionally writes an audit log and moves rows to the archive table instead of deleting them. ↩

6. Explaining how we made more complicated query patterns work would make this post too long, but we do also support automatic audit logging with nested transactions and CTEs! ↩

7. To maintain type-level enforcement of audit log context at the application layer, this might involve keeping the external surface of our TypeScript Drizzle wrapper mostly the same, but instead of running the whole audit log lifecycle in TypeScript, only doing SET LOCAL at the ORM/TypeScript level and then moving the actual audit log insert into PL/pgSQL code. ↩