Only system_admin Hits The Audit Log Foreign Key Constraint

The short version

Make an audit log’s actor reference a foreign key into one table, and it fails at the exact moment an admin of a kind not in that table performs an action.

What it looks like

When an operator-side administrator (system_admin) deletes an ad, the response is HTTP 500. In production the error message is masked and all that is visible is a generic error with a digest.

Creating an ad from the same screen does not 500. Instead it returns 「他の操作と競合しました。最新の内容を読み込み直してください」 — “this conflicted with another operation; please reload the latest content” — a message that is not true. The creation itself is failing, and it is swallowed to look normal.

Delete fails loudly and gets noticed; create fails quietly and does not. Two operations broken by the same cause, with asymmetric symptoms.

Why

The system has two kinds of administrator role kept in different tables: the tenant-side school administrator (school_admin) and the operator-side administrator who works across all schools (system_admin).

The audit log has a column recording who performed an action, with a foreign key to users(id). A school administrator is a row in users, so that reference resolves fine. But system_admin is managed in a different table and does not exist in users.

Both the delete and the create Server Actions passed the operator’s uid straight into the actor reference when writing the audit log. A school administrator’s action points at a real row in users and goes through; a system_admin’s action points at a users row that does not exist, and the insert fails with a foreign key violation (PostgreSQL error code 23503).

The violation itself occurred for the same reason on both operations. What differed is the exception handling after it.

• The create path already had handling that catches a constraint violation and converts it into a “conflicted” message, so a foreign key violation came back in that wording. The cause being a constraint violation is right as far as it goes, but what is actually happening is not contention with another user — it is an inconsistency in this reference design.
• The delete path had no handling for constraint violations, so the exception rethrew. Production masks error details, so the operator saw a bare HTTP 500.

The existing unit tests mocked the DB connection. The code passing system_admin’s uid runs and the test passes. But a foreign key constraint only fires once something is written to a real database, so a mocked test could not verify it. CI stayed green and the inconsistency went on being missed.

The create-side failure was also hard to surface. The test ad data was inserted by a route other than this screen, and system_admin itself rarely tries creating from here. A plausible-sounding error message — “conflicted” — was part of why it was never recognised as real damage.

Fixing it

We changed the audit log’s actor reference to null when the operator is a system_admin.

const isSystemAdmin = params.actor.role === "system_admin";
const actorRef = isSystemAdmin ? null : params.actor.uid;

The information identifying the operator is not discarded; it is held in a separate column with no foreign key (actorIdentityUid). It can no longer be used for aggregations or joins that need a reference into users, but the means to trace “who did this” remains.

The delete path’s exception handling gained exactly the same constraint-violation check the create path already had. The asymmetry — the same failure coming back as a “500” on one path and a “conflict” on another — is gone, and both now return a message that means something.

The regression tests pin that a system_admin action makes the actor reference null while the operator’s identity survives in the separate column. They also confirm that other tables outside the foreign key’s scope (ads.created_by and similar) are not being nulled unnecessarily. What needed fixing was only “places referencing a role managed in a different table through a foreign key into users”; nothing beyond that needed loosening.

Making it not recur

What this incident teaches is that the same error code — “foreign key constraint violation” — calls for different responses depending on which reference is violating it. The create path’s exception handling lumped constraint violations together as “conflict”, so it could not tell two 23503s with different causes apart.

Two things were done to prevent a repeat. One is the design fix above: do not put system_admin on a foreign key into users. The other is making delete and create use the same decision function (isConstraintViolation), so exception handling does not vary by path. Fix only one side and the same asymmetry eventually reappears on another operation.

The design of handling a tenant-scoped role and a cross-tenant role in the same audit log can exist in other operator-side writes too. Places of the same kind need to be enumerated with the same question: does this audit-log reference assume users?

On audit-log actor design, there is also an incident where an RLS policy allowed actor_user_id=NULL. There, allowing NULL was the problem; here, setting NULL was the solution. On the same column, the answer reverses depending on what you are trying to protect.