The log that cannot be edited

Ban a user. Then lift the ban.

curl -sX POST  -H "authorization: Bearer $KEY" "$API/v1/users/ana/ban"
curl -sX DELETE -H "authorization: Bearer $KEY" "$API/v1/users/ana/ban"

Now go and find out that it happened.

relay=> select external_id, banned_at from users where external_id = 'ana';
 external_id | banned_at
-------------+-----------
 ana         | <NULL>

The column is back where it started. There is no event, because Ana was in no channel and a membership change with no members to tell publishes nothing. The request log — the one chapter 4.8 built — holds the two calls for thirty days, with three of the five fields this clause names and no notion of what either call did.

Two moderation actions happened, and the platform holds no evidence that either one did.

flowchart TB
    subgraph before["before this chapter — a ban, then the ban lifted"]
      direction LR
      b1["POST /v1/users/ana/ban"] --> b2["users.banned_at = now()"]
      b2 --> b3["DELETE /v1/users/ana/ban"] --> b4["users.banned_at = NULL"]
    end
    before --> q["what is left?"]
    q --> c1["the column<br/>back where it started"]
    q --> c2["no event — a user in no channel<br/>publishes nothing"]
    q --> c3["the request log<br/>3 of 5 fields, TTL 30 days"]
    c1 --> none["two moderation actions happened<br/>and nothing records that either did"]
    c2 --> none
    c3 --> none

That is FR-MOD-03's job:

Every moderation action shall be recorded in an immutable audit log with actor, action, target, timestamp, and request ID, retained for 1 year.

It reads like one sentence and it is ten. Two of them are the chapter.

The clause names a population and does not say who is in it

"Every moderation action" — which ones are those?

The platform has no list. Nothing in the schema, nothing in the router, nothing in the specification. There is a shape of an answer — mutating, reachable by a tenant, and acting on somebody other than the caller — and shapes of answers are the thing this series has learned to distrust, because the only way to find out whether one is right is to apply it to every case and look at what comes out.

So that is what the chapter does first. The routes come from a booted application rather than from reading the source, which is the mechanism chapter 3.4's isolation harness already uses and for the same reason: only the router knows what exists.

gauntlet targets: 48 derived, 40 attacked, 8 exempt

Of those 48, thirty-three mutate. Nine are /internal/, and those are outside the set by construction rather than by decision — a platform principal carries no environment at all (chapter 4.4), so its action cannot be scoped to a tenant and could never appear in a tenant's log. Outside because we decided and outside because it cannot be inside are different claims, and only one of them needs a reason.

That leaves 24 routes, each owing a decision. Not an entry each — a decision each, which is the distinction the whole exercise exists to make.

flowchart LR
    r["the router, booted<br/>48 routes derived"] --> m["33 mutating"]
    m --> i["9 /internal/<br/>outside BY CONSTRUCTION:<br/>no environment, so no<br/>tenant's log to appear in"]
    m --> t["24 tenant-reachable<br/>— each owes a DECISION"]
    t --> mod["7 moderation<br/>ban · unban · delete a user<br/>remove a member · change a role<br/>archive · unarchive"]
    t --> when["1 moderation-when-application<br/>DELETE a message"]
    t --> not["16 not-moderation<br/>each with a reason"]
    mod --> line["the line is STANDING, not data"]
    when --> line
    not --> line

What the rule got wrong, which is the interesting part

Apply mutating, tenant-reachable, acting on another to all 24 and it admits two routes the set excludes: adding a member to a channel, and editing a user's profile. Both are genuinely acting on somebody else. Both are excluded.

Adding members is what a tenant's onboarding does all day. A log that records it is a request log with extra columns and a worse retention policy. Editing a display name is the same argument one table over.

Putting those two beside the seven that are in named the line the set actually draws, and the rule never said it:

Standing, not data. A ban, a deletion, a role change and a removal change what a person may do. A display name and a membership row do not.

That sentence was not available before the rule had been applied to every case and seen to be wrong twice in the same direction. It is worth more than the rule it corrects.

And the ninth action does not exist

The specification predicted nine inclusions: ban, unban, delete a user, delete another author's message, edit another author's message, remove a member, change a role, archive, unarchive.

The set is eight. Edit another author's message is not in it, and not because it was excluded:

PATCH /v1/channels/:channelId/messages/:messageId   accepts: "user"

A tenant key cannot reach that route at all. FR-MOD-02 grants a key deletion of any message and says nothing about editing, and chapter 3.23 read that silence as absence of permission — so the route accepts a user token and the edit accepts one credential class. The action a reasonable reader expects to find in a moderation log is an action this platform does not offer.

The specification said in advance that the routes the rule classifies wrongly are the chapter's findings. This one the rule classified correctly and the expectation was wrong, which is the same prediction arriving from the other side.

immutable was the word that cost something

Here is what a reader reaches for:

REVOKE UPDATE, DELETE ON audit_log FROM relay;

And here is what it does:

relay=> select usesuper from pg_user where usename = 'relay';
 usesuper
----------
 t
 
relay=> revoke update, delete on probe_immutable from relay;
REVOKE
relay=> update probe_immutable set v = 'b' where id = 1;
UPDATE 1

The api connects as a superuser, and a superuser bypasses privilege checks. The grant would be in the migration, the schema would read exactly as intended, and the table would be mutable.

A trigger fires for a superuser:

CREATE FUNCTION audit_log_refuse_write() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'audit entries are append-only (FR-MOD-03)'
    USING ERRCODE = 'restrict_violation';
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER audit_log_append_only
  BEFORE UPDATE OR DELETE ON audit_log
  FOR EACH ROW EXECUTE FUNCTION audit_log_refuse_write();

relay=> update audit_log set actor_id = 'tampered' where target_id = 'ana';
ERROR:  audit entries are append-only (FR-MOD-03)
relay=> delete from audit_log where target_id = 'ana';
ERROR:  audit entries are append-only (FR-MOD-03)
relay=> select actor_id from audit_log where target_id = 'ana';
 actor_id
-----------
 probe-key

Both verbs refused, and the row is still there — a BEFORE DELETE trigger that raises leaves the row rather than leaving a hole, which the refusal alone does not tell you.

The one trigger this repository had forbidden

No migration in services/api/migrations/ had ever created a trigger, across twenty of them, and that zero is not an accident of style. It is enforced: a unit test asserts it over every .sql file in the directory.

Its own comment says what it is protecting against. The test harness installs a sentinel guard on test databases — a trigger that refuses writes touching another test's rows — and the way that promise breaks is somebody moving the SQL into the migrations directory because that is where SQL lives, "at which point the api ships a trigger whose only purpose is to reject its own legitimate sweeps, in production."

The rule was wider than its reason, and this chapter is the first thing to need the difference. The guard refuses writes the api makes constantly. This one refuses writes the api must never make. They are opposites wearing the same syntax.

So the test is narrowed to the guard by name, the sentinel check is left exactly as it was, and a fourth test asserts the narrowing itself — because without it a later edit could widen the pattern back to every trigger and only a comment would record that the rule had changed.

What the claim is, exactly

relay=> set session_replication_role = replica;
SET
relay=> update audit_log set actor_id = 'bypassed' where target_id = 'ana';
UPDATE 1
 
relay=> drop trigger audit_log_append_only on audit_log;
DROP TRIGGER
relay=> delete from audit_log where target_id = 'ana';
DELETE 1

One SET disables every trigger in the session. DROP TRIGGER is one statement. Both succeed, and both are published here rather than left out, because a security sentence with no attack against it is a comment.

The log is immutable to the application and to accident. It is not immutable to somebody holding the database password, and this api holds the database password.

flowchart TB
    w["UPDATE audit_log SET actor_id = '...'"] --> g1
    subgraph g1["REVOKE UPDATE, DELETE — what a reader reaches for"]
      direction LR
      rv["privilege check"] --> su["the api connects as a SUPERUSER"]
      su --> pass["UPDATE 1 — the value changed"]
    end
    g1 --> g2
    subgraph g2["BEFORE UPDATE OR DELETE trigger — what fires"]
      direction LR
      tg["the trigger runs for a superuser too"] --> err["ERROR: audit entries are append-only"]
    end
    g2 --> g3
    subgraph g3["and what still gets through"]
      direction LR
      by1["SET session_replication_role = replica"] --> ok1["UPDATE 1"]
      by2["DROP TRIGGER"] --> ok2["DELETE 1"]
    end
    g3 --> claim["immutable to the application and to accident<br/>NOT to somebody holding the database password"]

That sentence has a consequence the chapter states rather than leaves for a reader to notice. NFR-SEC-10 asks for an immutable audit trail too — "administrative access to production data shall … be logged to an immutable audit trail" — and its actor is an operator with a database password, which is exactly the actor this trigger cannot refuse. The two clauses want the same words and are not the same artifact. One of them ships here.

Writing the entry

Eight actions record. Each writes inside the transaction it already runs in, after whatever guard already tells it whether anything changed:

if (banned.length === 0) return [];
const externalId = banned[0]!.externalId;
 
await this.recordAction(tx, {
  action: ACTION.ban,
  targetKind: "user",
  targetId: externalId,
});

After the guard, because an action that changed nothing earns no entry and isNull(bannedAt) in the UPDATE above is already what makes a re-ban change nothing. Inside the transaction, because an entry committed separately can disagree with the platform in both directions: an action with no entry if the second write fails, and an entry for an action that rolled back.

Four of the eight had no transaction to write inside. unbanUser, setMemberRole, archiveChannel and unarchiveChannel were each a bare UPDATE, and unbanUser was worse than that — no isNull guard either, so lifting a real ban and unbanning somebody who was never banned were the same call returning the same void. It could not tell you whether it had done anything, which is the one question the no-op rule asks.

So the chapter adds a transaction to four methods, which is a change to existing behaviour, and says so rather than letting it be discovered. What it costs was measured, 200 samples a side, interleaved so that a warming cache does not get attributed to the change:

the entry alone                        p50 delta
  banUser (already had a transaction)    0.439 ms   22.2%
  archiveChannel                         0.431 ms   33.6%
 
archiveChannel, against the pre-chapter shape
  as it ships      min 1.552 · p50 1.857 · p95 2.305 ms
  bare UPDATE      min 0.782 · p50 0.954 · p95 1.155 ms
  p50 delta        0.903 ms   94.6%

The entry costs 0.43 ms wherever it goes — it is one INSERT and costs what one INSERT costs — and the percentage differs only because the baseline does. The transaction costs about as much again, so an archive is nearly twice as slow as it was. Adds an insert would have been true and would not have been the number.

Reading it back

GET /v1/audit-log is a keyset page, newest first, scoped to the caller's environment with no parameter that names one.

The column is declared timestamptz(3) for a related reason. Postgres stores microseconds and the platform serialises with toISOString, which emits milliseconds, so a cursor minted from a value that came back over the wire sits before every row inside the lost fraction. In descending order that silently skips rows at every page boundary. This is the platform's first keyset cursor over a Postgres timestamp, which is why nothing had met it — and it is unfixable once entries exist, because changing a column's precision rewrites every row.

The probes, and what they found about the guards

Three places scope this feature to a tenant. Each was deleted on its own and both suites re-run, which is the only way to find out whether a scope is doing anything:

the page read's environment predicate      audit 3 of 21 RED · gauntlet 1 RED
the vocabulary read's predicate            audit 21 of 21 GREEN · gauntlet 62 of 62
the controller's 403 for no environment    audit 22 of 22 GREEN · gauntlet 62 of 62

The second was invisible because no test had two tenants whose action vocabularies differ — a smaller leak than a row, and still a leak: the set of actions a competitor's moderators perform is information about how they run their product. It has a test now.

The third is the one worth the page. That 403 cannot fire: the route declares @Accepts("application"), so the credential guard refuses a platform principal before the handler runs, and an application principal always resolves to an environment. The branch defends a case that cannot arise.

And here is what happens if you delete the thing that actually protects the route:

a user token on GET /v1/audit-log, decorator present   403 wrong_credential_type
the same request, decorator deleted                    200, with the entries

Drop the decorator and the guard falls back to accepting either credential class. A user principal has an environment, so the 403 would not fire — and every person signed into a customer's product could read that customer's entire moderation history. The branch is aimed at the wrong case, the decorator is the decision, and nothing in the repository had tested it. That test exists now, and the route suite that holds it exists because of this probe.

Adding an action later

This chapter is first in its movement and the three after it all write to this log. So the last thing it publishes is the procedure, and the procedure is three steps with a check behind the first two.

Classify the route, in audit/moderation-routes.ts, with a reason. A booted application is compared against that list in both directions: a route with no entry fails, and an entry naming no route fails too. The second direction is the one that catches a stale entry after a rename, and it is the half a new mechanism usually forgets.

Name it in ACTION and write the entry at the write site, inside the transaction the action already uses, after whatever guard tells the method it changed something. A name that is not a route in the list is a compile error.

Assert it. The first step proves the route was classified. Nothing structural can prove it was recorded, because an entry is written by code and not declared by a list — chapter 4.8 learned the same thing one registry over, where adding a route to the attack list turned the suite red with classified but never attacked.

The check was run red before being believed. A throwaway mutating route added to a controller fails the suite by name. And then the same route classified in the security list alone:

targets.itest.ts             9 of 9 GREEN      the security list is satisfied
moderation-routes.itest.ts   still RED         the compliance list is not

flowchart TB
    router["the routes a booted application reports<br/>— the one source both lists check against"]
    router --> t1["isolation/targets.ts<br/>WHICH ATTACK APPLIES<br/>read · write · list · credential · exempt"]
    router --> t2["audit/moderation-routes.ts<br/>WHICH ACTIONS OWE AN ENTRY<br/>moderation · when-application · not"]
    t1 --> p["a throwaway POST /v1/channels/probe-unclassified"]
    t2 --> p
    p --> s1["classified in NEITHER<br/>both suites red"]
    p --> s2["classified in targets.ts ONLY<br/>targets 9 of 9 GREEN<br/>moderation-routes STILL RED"]
    s2 --> why["a route can be covered for attack<br/>and undecided for audit —<br/>which one list cannot say"]

A route can be covered for attack and undecided for audit. One list cannot say that.

What this chapter did not do

No year. FR-MOD-03 says the log is retained for one year. Nothing prunes this table and nothing enforces a year, because this platform has no scheduler of any kind — the same absence chapter 4.9 recorded for the reconciliation's daily job and 4.16 for the storage sweep's weekly one. Five clauses now wait on one missing thing. What the chapter refused to do is publish a retention_edge beside the read the way the request log does; that route publishes one because its table has a TTL, and announcing a boundary nothing enforces is worse than announcing none.

No person. The actor is a credential. actor_kind is application, user or platform and actor_id is a key id or an end-user external id, because this platform has no notion of a human operator. A tenant running its support tool on one key will see that key on every row.

No history. The log begins at the migration. Every moderation action before it is gone, and the chapter that opened by showing you a ban with no trace is also the chapter after which that is no longer recoverable for the ones that already happened.

No reason. The journey this requirement comes from has Priya "noting which messages were removed and why", and the why is hers — she writes it in her own tool. FR-MOD-03 asks for five fields and none of them is a reason. It is the first question a reader of an entry will ask, so it is answered here rather than left to be discovered: the log records what was done, by which credential, to what, when, and under which request. Not why.

And one collision handed forward. The erasure endpoint deletes a user's data; an entry naming that user is a moderator's action, not the user's. The table takes no ON DELETE on its tenant key and this chapter takes no position on the user case, which belongs to the chapter that builds erasure.