An Append-Only Schema for Prompts, Runs and Outputs

An audit log designed forwards records what was convenient to record. An audit log designed backwards from the questions it will be asked records what it must. This page starts from six questions, derives the schema that answers them, makes it append-only in a way a database user cannot undo, and is straight about the limits of the tamper-evidence everybody bolts on afterwards.

The questions this schema answers

These are the ones that come up in a security review, a customer due-diligence questionnaire, or an incident. Each drives at least one column.

1. Who caused this output? Which authenticated principal, on whose behalf, from which application.
2. What exactly was sent to the model? Not the template — the rendered prompt, including retrieved context and tool definitions.
3. Which model and which version? Including the provider and the deployment, because “the same model” at two providers is not the same model.
4. Which documents influenced it? Retrieval is part of the input, and an answer that cited a document the user should not have seen is the thing the audit is usually about.
5. What did a human do with it? Accepted, edited, rejected, escalated. For any system with a human in the loop this is the question a regulator asks first.
6. Can you show this record was not altered? The hard one, and the one with the most honest answer.

The schema

-- One row per model invocation. Immutable by construction.
CREATE TABLE runs (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at     timestamptz NOT NULL DEFAULT clock_timestamp(),

  -- Q1: who
  tenant_id       uuid NOT NULL,
  actor_type      text NOT NULL,        -- 'user' | 'service' | 'schedule'
  actor_id        text NOT NULL,
  on_behalf_of    uuid,                 -- the end user, when an agent acted
  application     text NOT NULL,
  request_id      text NOT NULL,        -- joins to llm_requests telemetry

  -- Q2: what was sent. Hash always; body subject to retention policy.
  prompt_sha256   bytea NOT NULL,
  prompt_body     jsonb,                -- nullable: erasable, see below
  prompt_template text NOT NULL,        -- 'answer-with-context@v7'
  tool_schema_sha256 bytea,

  -- Q3: which model
  provider        text NOT NULL,
  model           text NOT NULL,
  model_version   text,                 -- provider's version string, verbatim
  params          jsonb NOT NULL,       -- temperature, top_p, max_tokens, seed

  -- the output
  output_sha256   bytea NOT NULL,
  output_body     jsonb,
  finish_reason   text NOT NULL,
  input_tokens    int NOT NULL,
  output_tokens   int NOT NULL,

  -- Q6: tamper evidence
  prev_hash       bytea NOT NULL,
  row_hash        bytea NOT NULL
);

-- Q4: which documents influenced it. One row per retrieved chunk.
CREATE TABLE run_retrievals (
  run_id        bigint NOT NULL REFERENCES runs(id),
  rank          int    NOT NULL,
  chunk_id      bigint NOT NULL,
  document_id   uuid   NOT NULL,
  score         real   NOT NULL,
  index_version text   NOT NULL,
  PRIMARY KEY (run_id, rank)
);

-- Q5: what a human did next. Also append-only; a change of mind is a
-- new row, never an update.
CREATE TABLE run_dispositions (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  run_id       bigint NOT NULL REFERENCES runs(id),
  occurred_at  timestamptz NOT NULL DEFAULT clock_timestamp(),
  actor_id     text NOT NULL,
  action       text NOT NULL,      -- 'accepted' | 'edited' | 'rejected' | 'escalated'
  edited_sha256 bytea,
  note         text
);

Two design points to defend. prompt_sha256 is NOT NULL while prompt_body is nullable: the hash is the record, the body is the convenience, and the split is what makes erasure possible without destroying the audit trail. And run_retrievals stores index_version alongside the chunk id, because a chunk id means nothing a year later if the index has been rebuilt — the version tells you which corpus state produced this answer, and connects to the version columns in a documents table that survives re-indexing.

Making it genuinely append-only

“We never update that table” is a convention. Two mechanisms make it a property, and you want both, because they fail differently.

-- 1. Privileges. The application literally cannot issue the statement.
REVOKE UPDATE, DELETE, TRUNCATE ON runs, run_retrievals, run_dispositions
  FROM app_user;
GRANT  INSERT, SELECT ON runs, run_retrievals, run_dispositions TO app_user;

-- 2. A trigger, which also binds roles that were granted more later,
--    including the table owner.
CREATE OR REPLACE FUNCTION deny_mutation() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'table % is append-only (attempted %)',
                  TG_TABLE_NAME, TG_OP
    USING ERRCODE = 'restrict_violation';
END $$;

CREATE TRIGGER runs_append_only
  BEFORE UPDATE OR DELETE ON runs
  FOR EACH ROW EXECUTE FUNCTION deny_mutation();

Be honest about the ceiling: a superuser can drop the trigger, restore the privilege and rewrite any row, and nothing inside the database can prevent that. What these two mechanisms genuinely provide is that no accident, no ORM cascade, no careless migration and no compromised application credential can alter the log. That is most of the risk, and it is worth having even though it is not all of it.

The rest requires putting evidence outside the database, which is the next section, and constraining who holds superuser, which is an organisational control rather than a schema one.

Hash chains, and what they prove

Each row’s hash covers its own content and the previous row’s hash, so altering any row invalidates every hash after it.

row_hash = sha256( prev_hash
                 || canonical_json(id, occurred_at, tenant_id, actor_id,
                                   prompt_sha256, model, model_version,
                                   params, output_sha256) )

canonical_json: keys sorted, no insignificant whitespace, timestamps
in UTC to microsecond precision, numbers in a fixed representation.
Two implementations that disagree about any of these produce
different hashes for identical data, which makes the chain
unverifiable — pin the canonicalisation in a test.

-- Verification is a single window function over the table.
SELECT id,
       row_hash = sha256(
         lag(row_hash) OVER (ORDER BY id) ||
         convert_to(canonical_payload, 'UTF8')
       ) AS ok
FROM runs
ORDER BY id;
-- Any false, or any gap in id, is the finding.

Now the part that is usually left out. A hash chain stored entirely inside the database it protects proves nothing against an attacker who can write to that database. Whoever can alter a row can recompute every subsequent hash. The chain detects accidental corruption and partial tampering; it does not detect a competent insider, and describing it to an auditor as if it does is the kind of claim that goes badly when examined.

It becomes real evidence when the head of the chain is published somewhere the database administrator cannot rewrite. In rough order of cost:

• Ship it off-box. Write the current head hash and row count hourly to an object store bucket with object lock enabled, or to a log service with immutable retention. Cheap, and it bounds any undetected tampering to one hour.
• Sign it. Sign the head with a key held in an HSM or KMS that the database role cannot use. Now forging the chain requires compromising two systems.
• Send it to a third party. A timestamping authority, a customer’s own system, or a public ledger. Strongest, most operational overhead, and only worth it when the adversary in your threat model is you.

Pick one and write down which. “Append-only with a hash chain” without an external anchor is a sentence that sounds like a control and is not one. The general design that regulators have accepted is discussed in audit logs regulators will accept.

Append-only against the right to erasure

These two obligations genuinely conflict. Your audit policy says the record is immutable; a data subject has a right to have their personal data erased. Both are real and neither yields.

The resolution is to separate the record from the content, which the schema above already does. Encrypt prompt_body and output_body with a per-subject key; on an erasure request, destroy the key. The row remains, its hashes still verify, the chain is intact, the timestamps and the model version and the token counts are still auditable — and the personal data is unrecoverable.

CREATE TABLE subject_keys (
  subject_id   uuid PRIMARY KEY,
  wrapped_key  bytea,                    -- wrapped by your KMS
  destroyed_at timestamptz               -- set on erasure; wrapped_key nulled
);

-- Erasure: one statement, and every ciphertext for that subject
-- becomes permanently undecryptable.
UPDATE subject_keys
SET wrapped_key = NULL, destroyed_at = now()
WHERE subject_id = $1;

Two caveats that must be documented alongside it. Backups taken before the erasure still contain the key, so the guarantee only fully takes effect when those backups expire — state the retention period in your erasure procedure, because that is the honest answer and the one supervisory guidance generally expects to see. And the columns you left in clear text for auditability must genuinely contain no personal data: if actor_id is an email address, you have not solved the problem, you have moved it. GDPR and AI APIs covers the surrounding obligations.

Answering the six questions

-- Q1/Q2/Q3: everything about one output.
SELECT r.occurred_at, r.actor_type, r.actor_id, r.on_behalf_of,
       r.provider, r.model, r.model_version, r.params,
       r.prompt_template, encode(r.prompt_sha256, 'hex') AS prompt_hash
FROM runs r WHERE r.request_id = $1;

-- Q4: which documents influenced it, in rank order.
SELECT rr.rank, rr.document_id, rr.chunk_id, rr.score, rr.index_version
FROM run_retrievals rr
JOIN runs r ON r.id = rr.run_id
WHERE r.request_id = $1
ORDER BY rr.rank;

-- Q4 inverted, which is the query an incident actually needs:
-- every answer that was ever influenced by this document.
SELECT r.occurred_at, r.tenant_id, r.actor_id, r.request_id
FROM run_retrievals rr
JOIN runs r ON r.id = rr.run_id
WHERE rr.document_id = $1
ORDER BY r.occurred_at DESC;

-- Q5: human review outcomes for a model version, over a period.
SELECT d.action, count(*)
FROM run_dispositions d
JOIN runs r ON r.id = d.run_id
WHERE r.model_version = $1 AND r.occurred_at >= $2
GROUP BY d.action;

The inverted retrieval query is the one that justifies the whole design. When a document turns out to have been wrong, or to have been visible to people it should not have been, the question is which answers it contaminated — and that question is unanswerable unless you recorded the retrieval at the time. Nothing reconstructs it afterwards.

One thing this schema deliberately does not do is log everything. An audit log that grows without discrimination becomes a table nobody queries, a retention liability, and — because the bodies contain whatever users typed — a large concentration of personal data with no business purpose. Three rules keep it proportionate.

• Hash always, store bodies selectively. The hashes are small, permanent and sufficient to prove that a given prompt produced a given output. Store the bodies only for the categories of request where you have a stated reason — regulated decisions, human review flows, anything a customer contract requires — and set a shorter retention on them than on the rows.
• Do not log secrets, and check rather than assume. A rendered prompt can contain an API key a user pasted, a tool definition can contain a connection string, and a retrieved chunk can contain anything at all in the corpus. Redact at capture time, which PII redaction covers, and treat the audit log as a system that holds production secrets until proven otherwise.
• Separate this table from your telemetry. They have different retention rules, different access controls and different query patterns, and merging them means the strictest rule governs both — so your thirty-day metrics table inherits a seven-year retention obligation. Storing telemetry from AI calls is the other half, joined by request_id and nothing else.

The retention period itself is a decision to take with whoever owns the obligation, written down with its justification, and enforced by a partition drop rather than by intention. An audit log with no expiry is not more compliant than one with a documented seven-year window; it is less, because it holds data past the point where you can explain why.