A 23503 FK Violation Disguised as a Conflict

The short version

A foreign key violation (23503) was judged as “a constraint violation” in the same bucket as a unique violation (23505), so failures with different causes collapsed into the same message: 「他の操作と競合しました」 — “this conflicted with another operation”. No other operation is actually conflicting, and the screen keeps saying so anyway.

What it looks like

• One operation (submitting an advertiser’s ad from the operator side) fails every single time it runs in production
• What appears on screen is 「他の操作と競合しました。最新の内容を読み込み直してください。」 — “This conflicted with another operation. Please reload the latest content.”
• Reloading changes nothing, and neither does waiting and resubmitting. There is no other operation contending for it
• The server throws no exception. It returns the app’s common response shape, { ok: false, error: { code: "conflict" } }, normally. Nothing is logged as an “unexpected exception”; it looks like a deliberately provided branch
• CI and the existing tests were all green

Why

The cause has two layers, and fixing only one leaves the symptom.

Layer 1: why the insert fails

The ad table’s created_by / updated_by gained a foreign key to users(id) in one migration. That migration re-adds foreign keys in bulk across five tables carrying the same audit columns (grades / departments / school_configs / daily_data / ads).

The operator role executing this operation (system_admin), meanwhile, has no row in the users table. It is an account managed independently in a different table. The creation path nevertheless wrote the operator role’s uid straight into created_by / updated_by, so the referenced row does not exist on every INSERT and it is always a foreign key violation (SQLSTATE 23503).

Within the same action, the audit-log write already had the mitigation “null the actor if this is an operator role”. Only the ad write itself had skipped it — an asymmetric state.

Layer 2: why it turns into “conflicted”

The app has a helper that judges PostgreSQL constraint violations collectively.

/** PostgreSQL unique / check / FK constraint violations (SQLSTATE 23505 / 23514 / 23503). */
function isConstraintViolation(error: unknown): boolean {
  return isPgErrorCode(error, "23505", "23514", "23503");
}

Three entirely different error kinds — unique violation (23505), check violation (23514) and foreign key violation (23503) — are flattened by one function into a single boolean, “is this a constraint violation”. The caller’s catch returns the same message when that function returns true, without distinguishing the kind.

if (isConstraintViolation(error)) {
  return conflict("他の操作と競合しました。最新の内容を読み込み直してください。");
}

The kind of cause does reach the code properly, in the form of SQLSTATE, and rounding it to a uniform message in the app layer is what planted the false hypothesis “maybe something really is colliding with another operation”.

That this function picks SQLSTATE up correctly at all is itself the result of an earlier, different incident. Drizzle wraps the postgres driver’s original error in DrizzleQueryError, moving SQLSTATE from the top-level code to cause.code. The old predicate, reading only error.code, could not follow that move: it dropped foreign key violations, re-throwed, and they reached the route’s error boundary as a full-screen error. It now walks up to five links of the cause chain looking for code, so at least “fails to catch and takes the whole page down” is prevented. The symptom here is one step removed: catching worked, and the classification after catching was too coarse.

Why the tests did not catch it

The old test carried a comment like this.

ads has no FK on created_by (not covered by migration 0004), so leaving uid is fine

That comment became wrong the moment the foreign key was added. Because the tests run with the DB mocked, the real foreign key constraint never applies. Inside a mock you cannot verify “does the constraint actually exist”, so the test could not notice the schema had changed underneath it and stayed green on a false premise.

Fixing it

• Stop writing the operator role’s uid directly and route it through user.role === "system_admin" ? null : user.uid. Bring the ad write into the same shape already used on the audit-log side
• Delete the incorrect comment and the assertions built on it, and re-verify that createdBy / updatedBy become null, in line with the real constraint
• Verify separately, with a test that passes an error mimicking cause.code = 23503 directly, whether SQLSTATE maps correctly onto conflict. With the DB mocked you cannot verify the existence of the constraint here, so isolate and test only “how a given error code is classified”

Preventing a repeat

When you apply a fix in one place, you have to check whether the same condition holds for the neighbouring code writing the same data. Here, the moment the mitigation landed on the audit-log side it should have been clear that the same condition applied to the ad write too, and it was passed over.

Also, a comment pinning a test’s expected value on the grounds that “this column has no constraint” carries no mechanism for following schema changes. It is worth holding the premise that a green mocked test is not proof the constraint does not exist in reality.