Track all prompts and outputs in a secure database for compliance

Logging your LLM prompts and responses takes an afternoon. Making those records hold up when an auditor asks who saw what, when, and under whose authority is a database design problem, and it usually gets deferred until a questionnaire lands.

This article covers that design: an append-only Postgres table you can defend under audit, retention you can enforce by dropping partitions instead of scanning rows, access control that stops your own engineers from reading raw prompt text, and a deletion path that satisfies the right to erasure without punching holes in the audit trail.

If your goal is to spot abuse, jailbreaks, and PII leakage in the traffic itself, that's a different job with a different architecture. Start with monitoring prompt inputs and outputs for safety, which covers what to capture, PII redaction, and out-of-band abuse analysis. That article decides your retention policy and your redaction rules. This one enforces them in the database, and assumes you already log the fields it describes.

The design is plain PostgreSQL: declarative partitioning, column-level grants, row-level security, and encryption keys your application holds rather than the database. It works on any Postgres you can run migrations against. Where a detail depends on the platform, the examples use Render Postgres, and you can create a free instance to follow along.

Making an audit trail defensible

A log becomes evidence when you can prove three things about it: no one altered it after the fact, it is complete for the period under review, and access to it was controlled. Ordinary application logging gives you none of these. A table your web service can UPDATE is a table an auditor must treat as editable.

Permissions get you controlled access, and most of the requirements for completeness. They do not get you immutability, because the role that owns a table can always regrant itself DELETE. For that you need evidence the database cannot reach. Hash the rows, sign the digest, and store it outside the database under credentials no database role holds. A retroactive edit then fails verification even though nothing stopped it, and the FAQ below covers what to hash and how often to check it.

Two regulatory requirements pull against each other here, and the tension shapes every decision below.

GDPR Article 17 gives data subjects a right to erasure, and Article 12 generally requires you to act within one month, extendable by two further months for complex or numerous requests. So prompts containing personal data have to be findable and destroyable per user.

HIPAA pushes the other way. The Security Rule's documentation requirement at 45 CFR 164.316(b)(2)(i) obliges covered entities to retain required policies, procedures, and records of actions and assessments for six years from the date of creation or the date the document was last in effect, whichever is later. That six years covers your compliance documentation rather than every application log you happen to keep.

That leaves audit log retention as a decision you make and defend rather than a number the regulation hands you. Pick a period, write down your reasoning, and enforce it in the database.

You resolve the tension by separating the record from its contents. Keep the metadata that proves an interaction happened and was governed, and make the sensitive payload independently destroyable through encryption. Cryptographic erasure, covered below, is how you get both.

Prerequisites if prompts might contain PHI

PHI has to stay out of everything surrounding the database. Render's docs draw that line explicitly, and it holds on any platform: service logs, build artifacts, render.yaml and Terraform config, and resource names must never contain PHI, and that last list runs all the way down to table and column names in your database. That rules out putting prompt text in a log line, which is why the prompt belongs in an encrypted column. It also rules out naming a column after the condition it holds, since object names surface in query errors and stack traces.

Your provider also has to sign a BAA, and what that costs varies. On Render, the database has to live in a HIPAA-enabled workspace, which requires a Scale or Enterprise plan. You designate the workspace in the BAA when you sign, enablement is irreversible, and it adds a 20% fee on all usage while excluding free instances, the Singapore region, and Render Workflows. Postgres primaries, read replicas, and high availability standbys all support PHI. See building HIPAA-compliant apps on Render for the control-by-control split between what Render handles and what you implement.

Designing an append-only audit table

Give the audit log its own database instance before you write the first migration. Everything below depends on the audit table having a different owner than the one your application uses, and how you get a second role differs by platform. On Render, you add a database user, which changes the default user for every service wired to that database. On a shared database that breaks your application.

Partition by time in that same first migration. Retrofitting partitioning onto a table holding a year of prompt text means a full rewrite under lock, and time-based partitions enable cheap retention later.

The composite primary key includes logged_at because PostgreSQL requires a unique or primary key constraint on a partitioned table to include all of the partition key columns. This is the most common reason a first attempt at partitioning fails.

Prompt and response are BYTEA ciphertext, not TEXT. Storing them encrypted is what makes per-user destruction possible without deleting the surrounding record, as explained in a later section.

Store the full rendered input in prompt_ciphertext, not just the user's message. The system prompt, the template wrapped around it, and any context a retrieval step injected are all visible to the model, and fair game for auditor questions. Nobody wants to reconstruct that context later from a template version and a retrieval log. If your system prompt is long and stable across millions of rows, store a hash of it in its own column and keep the text in a versioned table you join against.

model_version on its own does not tell you whether the same input would produce the same output. Record the decoding parameters alongside it, either inside compliance_flags or in a separate params JSONB column: temperature, top_p, seed, and the token limit. Without them, a question about a specific output has no reproducible answer.

status is what lets the table hold an interaction that did not produce a response. A refusal, a guardrail block, a timeout, and a stream that died halfway are the interactions an auditor is most likely to ask about, and a NOT NULL response column cannot store any of them. response_ciphertext is nullable for the same reason. Without this column, an empty response and a blocked request look identical, and the rows that prove your acceptable-use policy was enforced are the rows you never wrote.

request_id correlates a row with your application logs and traces, which is the first thing anyone reconstructing an incident reaches for. turn_index orders the turns within a session, because logged_at is a timestamp rather than a sequence and concurrent turns or retries will collide on it. A retry gets its own row with a new request_id and the same session_id, so the sequence of attempts stays visible, and tool-call turns work the same way: one row per model invocation, ordered by turn_index. Note that you cannot put a unique constraint on (session_id, turn_index) without dragging the partition key into it, so your application owns that invariant.

user_id is VARCHAR rather than UUID because it usually arrives from an external identity provider as an opaque subject claim. Store it in whatever form your IdP issues rather than coercing it.

dek_id points at the data encryption key used for that row, which is the hook cryptographic erasure pulls on.

compliance_flags carries the policy context: the version of the acceptable-use policy in force, whether redaction ran before storage, the jurisdiction the request was processed under. Together with session_id, status, and model_version, this is what makes the metadata answerable on its own. You can show which model handled a conversation under which policy and how it ended without touching a key, which is the only kind of answer available once the payload is encrypted.

client_ip is personal data under GDPR, so it falls under the same minimization and erasure obligations as the prompt text. Include it only if you actually use it.

Nothing in a CREATE TABLE statement makes a table append-only. That comes from permissions, which the next section covers.

Restricting access to the audit log

Immutability is a grant problem. Your application role gets INSERT and nothing else, and critically, that role must not own the table. A table owner can always DELETE, and owners bypass row-level security by default.

FORCE ROW LEVEL SECURITY is the crucial line here. Without it, the role that owns a table bypasses row-level security entirely and can read every tenant's rows regardless of policy.

Both row-security flags are properties of a single relation, though, and CREATE TABLE ... PARTITION OF does not copy them from the parent. A partition created without them is a table where the owner faces no policy at all, reachable by name. That makes the two ALTER TABLE lines part of partition creation rather than one-time setup.

The split is worth stating precisely, because it is easy to get backwards. A query that reaches rows through the parent is checked against the parent's flags and policies no matter what its partitions carry, so tenant_isolation holds from day one. A query that names a partition is checked against that partition alone, and a partition with the flags off has nothing to apply. \d+ on a partition reports that partition's own setting rather than the parent's, which is how you check.

Each of the remaining details fails in a way that looks like something else.

Row-level security is default-deny, and that applies to writes. Enabling RLS with only a SELECT policy means PostgreSQL rejects every INSERT your application attempts, which reads as a broken writer rather than a missing policy. writer_append is not optional.

The view is declared security_invoker = true so it runs with the auditor's privileges rather than the view owner's. Leave that off and the query is evaluated as the view owner, no policy matches that role, and default-deny returns zero rows to an auditor who has every grant they need. The option needs PostgreSQL 15 or later, one of several version floors this design stacks up.

Because the auditor's own privileges are checked against the base table, the column-level SELECT grant is what keeps ciphertext unreadable, and a blanket GRANT SELECT ON llm_audit_log would let them bypass the view and read the payload directly. The view is therefore a convenience that allows an auditor to run SELECT *, not protection. The cost is a column list in two places with nothing keeping them aligned, so generate both from one list in your migrations.

Grant on the parent, never on individual partitions. Access through the parent is checked against the parent's grants and policies, but a role holding privileges on a partition can query that partition directly and skip tenant_isolation altogether.

Proving the boundary holds

Assert the grants rather than trusting them. This query answers the four questions an auditor will ask:

The last column is important because table ownership grants implicit privileges that don't show up as a clean pass elsewhere. An owning role will already fail checks 1–2, but ownership also means it can alter the table's grants, structure, or ownership itself, which no single privilege check captures.

Getting ownership right

Everything above depends on your application not owning the table, and managed Postgres makes that easy to get wrong. The provider hands you one role, you run your migration as it, you connect your app as it, and now your app owns the audit table and the grants above are decoration. On Render that role is the instance's original user.

The order that works on Render:

1. Run the migration that creates llm_audit_log as the original user. That user now owns the parent table and will own its partitions.
2. Add a second Render-managed user through database credentials, giving it the custom username audit_writer so it matches the grants above. Grant it INSERT on the parent and nothing else. Users you create with a bare CREATE USER are not Render-managed and will not appear in the Dashboard or API.
3. Redeploy the services that read the database's connection string. Blueprint-managed services need a manual Blueprint sync first, because that is what refreshes their fromDatabase values.
4. Confirm with \dt that the Owner column on llm_audit_log shows the migration role rather than the application role.

Step 3 is the one that bites. Adding a user makes it the database's new default user, so your fromDatabase environment variables and the connection strings in your Dashboard now resolve to audit_writer. The switch is per-database rather than per-service, which means every service wired to that database picks up the INSERT-only user on its next deploy and loses the access it had.

If the audit log shares a database with your application, that breaks the application. It's the strongest argument for the dedicated instance. The switch also maintains the ownership split, since the owner's credentials stop appearing in connection URLs entirely once audit_writer is the default.

Managed credentials also get you zero-downtime rotation: create a replacement user, sync and redeploy the services that use it, confirm the old user has no connections left, then delete it.

Rotate the writer as often as you like, but leave the original user alone. Deleting it revokes its login privileges without removing the role, so it goes on owning your partitions while no longer being able to connect. That breaks the retention job in the next section while leaving the ownership split looking correct.

Enforcing retention with partitions

Automated retention is where partitioning pays off. Dropping a partition removes a month of records in one metadata operation, while a DELETE over the same rows generates dead tuples, drives autovacuum work, and can bloat a text-heavy table badly enough to need pg_repack. That is a tool you want to avoid needing, and a managed instance makes it more fraught. On Render it requires PostgreSQL 16 or later, a client compiled on your own machine at a version matching the extension, and free storage exceeding twice the size of the table and its indexes being repacked.

Schedule partition creation and expiry on whatever runs your recurring work, a Render cron job or equivalent, running CREATE TABLE ... PARTITION OF for the upcoming month and DROP TABLE for partitions past your retention boundary. Enable and force row-level security on each new partition in the same transaction that creates it, or the guardrail described below applies to the parent and to nothing else.

The pg_partman extension replaces that DDL with its own. Render's comes with three conditions: PostgreSQL 14 or later, no background worker enabled, and a database created after 5 February 2026.

Read the middle condition carefully. pg_partman_bgw is how pg_partman normally maintains partitions unattended, so without it you still schedule a cron job, this time to call run_maintenance_proc(). You are trading your DDL for its DDL rather than getting rid of the cron job. Its template table will not carry the row-security settings for you, because it only propagates primary keys and unique indexes on non-partition columns, tablespaces, relation options, and unlogged state. Have the same job run ALTER TABLE ... ENABLE ROW LEVEL SECURITY and ALTER TABLE ... FORCE ROW LEVEL SECURITY on each partition it creates, similarly to the hand-rolled path above.

Alert on that job, because its failure mode is silent and it points the wrong way. A range-partitioned table has nowhere to put a row outside every defined range, so once you pass the end of your last partition, INSERT fails with no partition of relation "llm_audit_log" found for row and your audit trail stops. Create several months ahead and alert on the absence of next month's partition rather than on the job's exit code, since a job that succeeds while creating nothing looks healthy.

A DEFAULT partition catches those rows, but it fights the rest of this design. Attaching a real partition later forces PostgreSQL to scan the default and refuse if any row in it belongs to the new range, and clearing those rows means deleting them from a table whose entire purpose is to be undeletable.

Creating and dropping partitions is an ownership operation rather than a DELETE, so you can't choose the role for the retention job. PostgreSQL requires you to own the parent table to attach a partition to it, which means the retention job has to run as the role that owns llm_audit_log, the same role that ran your migration. That role therefore holds DELETE on the table.

FORCE ROW LEVEL SECURITY narrows the gap, because with no UPDATE or DELETE policy defined, no row is visible for the owner to modify. A stray DELETE in a retention script does not error. It reports zero rows affected, which looks exactly like a DELETE that had nothing to remove. And it narrows the gap only on relations that carry the flag, which is why the partitions need it as much as the parent does.

Even then the owner could drop the policies or turn FORCE off, so treat this as a guardrail rather than a control, and keep the owner's credentials scoped to the retention job and nothing else. The signed digests are the real proof.

Build two things before you need them. First, a legal hold flag that your retention job checks and respects, because discovering mid-run that legal needed the data is expensive. Second, an export step that captures evidence of processing lawfulness for a user before their content is destroyed, since once the content is gone you cannot reconstruct what it contained.

Point-in-time recovery (PITR) is disaster recovery, not retention. Managed recovery windows are measured in days. Render's is the past 3 days on Hobby workspaces and the past 7 days on Pro or higher, with its own logical backups retained for 7 days. No window on that scale will satisfy a multi-year audit obligation. Render's free instances support neither PITR nor logical backups, which is why an audit database belongs on a paid instance.

For long-term retention, run your own pg_dump on a schedule and ship the output to storage you control. The Postgres to Amazon S3 guide walks through the cron job, and warns against giving it a PgBouncer connection string, so point it at the direct connection on port 5432 rather than the pooled one on 6432.

If prompts contain PHI, that export leaves your database platform and needs its own BAA with whoever stores it. The prompt ciphertext is the safe part of the dump. user_id, client_ip, and the timestamps travel in plaintext alongside it.

Encrypting content and erasing it cryptographically

Encrypt the prompt and response in your application under a per-user or per-tenant key, and erase a user by destroying that key rather than deleting their rows. The rest of this section explains why the encryption your platform gives you doesn't cover this, and what the key hierarchy has to look like.

Managed Postgres normally arrives encrypted at rest and in transit. Render's is a minimum of AES-256 on disk, with Render-managed TLS certificates on external connections. That protects you if the drives are stolen, but it doesn't protect a running database.

PostgreSQL has no built-in transparent data encryption, so at-rest encryption sits below the database. PostgreSQL's documentation on encryption options is blunt about the consequence: once the file system is mounted, the operating system provides an unencrypted view of the data. Any client that can connect reads plaintext. Render's own HIPAA guidance makes the same point from the other side, recommending application-level encryption so that a breach of one component doesn't expose the data.

This is why erasure needs its own mechanism. Without one, removing a user's content means mutating the audit trail, which is exactly what the append-only design above exists to prevent.

For that you need finer-grained keys. Envelope encryption is the standard shape: encrypt each row's prompt and response with a data encryption key, then encrypt the DEK with a key encryption key held in a KMS such as AWS KMS or HashiCorp Vault. Scope DEKs per user or per tenant, and record which one you used in dek_id.

Every AES-GCM encryption also produces a nonce and an authentication tag, and both have to survive alongside the ciphertext or the value is undecryptable. Pack them into the BYTEA column rather than splitting them into their own columns, so one field is all a decrypt needs.

Writing a row

All of that happens in your application, before the INSERT. The database never holds a key and never sees plaintext, which is the entire point.

Three things in there are worth being deliberate about.

Pass logged_at explicitly instead of letting the column default fire. NOW() only tells you when the row was inserted into the database. If writes go through a queue, that insert happens whenever a worker drains that message, which can be minutes after the actual interaction and in a different order than the interactions themselves occurred. An audit trail whose timestamps reflect your infrastructure's processing delays, rather than when things actually happened, fails at its job.

The row stores dek.id and never the key or its wrapped form. Wrap the DEK into the row it protects and you cannot destroy the key without updating the row, which your grants forbid. The pointer is what keeps erasure and immutability compatible.

There is no ON CONFLICT clause, because audit_writer holds no UPDATE privilege and an upsert that takes the update path would fail. A retried request appends a new row, which is the behavior you want anyway.

Rotating keys and destroying them

Plan for DEK rotation while you design this rather than afterward, because a key you hold across a multi-year retention window is a long-lived commitment. Re-encrypting existing rows under a new DEK means updating them, which your grants deliberately forbid. The clean answer is to rotate forward only: new rows get the new DEK, and older rows keep theirs until their partition ages out.

Erasure then becomes key destruction. Delete the DEK for a user and their ciphertext is unrecoverable while every surrounding metadata row stays intact, which is exactly the outcome demanded by the GDPR and HIPAA tension described above. This is also why partition drops are not a substitute: they enforce your retention schedule on a calendar, while key destruction answers an individual request that arrives mid-window and spans many partitions. You need both mechanisms.

Two caveats apply. Crypto-shredding is widely used but not universally accepted by regulators as equivalent to deletion, so document your reasoning and pair it with real deletion where the risk is high. And your key destruction has to reach backups, or a restore quietly resurrects the data you promised to erase.

Encrypting the payload costs you the ability to query it. You cannot search prompt text, aggregate over it, or run analytics on it in the database, and no index will help. That loss is why the metadata columns carry so much weight here. session_id, turn_index, status, model_version, compliance_flags, and the token counts are what you query instead, and they answer most audit questions without touching a key. Content-level analysis belongs in the separate safety-monitoring pipeline, over redacted copies, not in your evidentiary record.

If you would rather keep encryption inside the database, the pgcrypto extension, available on Render, provides pgp_sym_encrypt and related functions. The tradeoff is that keys pass through the database, so a compromised instance compromises both.

Sizing storage for an append-only table

An append-only table only grows, so size it deliberately rather than discovering the ceiling later. Estimate from records per day, average prompt and response size, and index overhead, then expect ciphertext to cost more than the equivalent plaintext would. PostgreSQL compresses large values automatically through TOAST, but encrypted data does not compress, so an encrypted prompt column costs roughly its plaintext size plus overhead rather than the reduction a TEXT column would give you.

Storage autoscaling, where your platform offers it, suits this workload, because the growth is monotonic and predictable rather than spiky. Know the rules before you lean on it. On Render, autoscaling fires at 90% full and permanently raises storage by 50%, rounded up to the nearest 5 GB, any increase locks out further increases for 12 hours, and storage can never be reduced. Treat every increase as permanent and pick sizes accordingly.

Operating the audit database

You also want a durable record of who queried the audit log, and the obvious candidate does not work here. pgaudit, available on Render for PostgreSQL 13 and later, logs nothing on a bare CREATE EXTENSION pgaudit;. It would be the wrong tool anyway, because pgaudit's read logging writes statement text into your database logs, and logs are the last place prompt content should land.

Log audit reads from your application instead. Every decrypt and every export is an event worth its own row, which keeps that record in a table you control and out of your log stream.

Keep audit reads off the write path. A read replica keeps a long compliance query away from the instance taking your INSERT traffic. On Render, one needs a primary with at least 10 GB of storage on a Basic-1gb instance or higher. Replicas lag the primary by a delay that tracks its load, which you can watch as Replication Lag on the primary's metrics dashboard, so run subject access requests against a replica and verify recent writes against the primary.

Pool your connections, but watch how pooling interacts with tenant_isolation. Opening a connection per audit write will exhaust your instance's connection limit under real LLM traffic. Render's pooler runs PgBouncer on the database host at no extra cost on port 6432, and is unavailable on free instances. PgBouncer uses transaction-level pooling, though, and Render's docs name custom session variables as one of the things that breaks under it. A plain SET app.tenant_id will not reliably survive to the next statement. Use SET LOCAL inside the same transaction as the query, or point auditor connections at the direct port and pool only your writers.

Frequently asked questions