Approval Decision Audit-Trail and Atomicity Automation: An Indexed Column No Write Path Ever Filled

Building a dedicated database index for a specific column is not a neutral, cost-free decision — it is a deliberate signal that someone expects that column to be queried, looked up, filtered, or reported on later, and is willing to pay the ongoing cost of maintaining that index to make those future queries fast. An index on an empty column is a promise nobody kept: the infrastructure to answer a question was built and maintained, while the one thing that would let it actually answer that question — a value ever being written into the column at all — never happened.

The client-approvals table's own schema — captured in a dedicated reference snapshot specifically because the table itself predates the project's migration history — has carried a column recording who responded to a given decision, with its own purpose-built index, for the table's entire life. Confirming whether that column was actually ever populated meant checking every place that ever wrote a decision into the table, not assuming the most obviously relevant one had it covered: the approval screen's own direct database write (which set only the decision's status and the time it was made), the edge function handling calendar and deliverable decisions (thirteen hundred lines, searched directly for the column's own name — zero matches), and the separate service handling batch planning-day approvals (searched the same way — zero matches). Three independently written, independently maintained code paths, spanning years of the table's use, all wrote decisions into the identical table, and not one of them had ever set the one column specifically built and indexed to record who made the decision.

The same investigation, tracing exactly how a single approve or reject action actually executed, found the decision itself was never a single atomic operation. Approving one post ran as an ordered sequence of database calls from application code: read the approval record to find the related task, write the approval as approved (protected only by a check that it was still pending, at the time), read the workspace's own configured service-stage name, read the related task's own details, write the task's stage and status, and — separately, best-effort — insert a notification for the internal team. Six steps, two tables, zero of it wrapped in a shared database transaction, and no row lock held across any of it beyond the single conditional update in step two. That specific update genuinely protected against the one race it was built for — two people deciding the same item within the same instant — but nothing protected the OTHER five steps from running inconsistently with each other if any one of them behaved unexpectedly.

The fix addresses both findings with the same structural change: one dedicated, security-scoped database function per decision type (a standalone post, a planning day, a batch of planning days, a finished deliverable, an internal review). Each function opens by taking an explicit lock on the specific approval row before evaluating anything else — nothing else can act on that same row until the function finishes. Every related write for that one decision — the approval's own status, the connected task or content item, and now the identity of whoever actually made the call — happens inside that same function, as a single transaction: it either all commits together, or none of it does, closing the six-step, two-table sequence into one atomic unit. And because the responding identity is available directly, inside the database, at the exact moment the decision is being recorded, the column that had sat indexed and empty since the table's own creation is finally set — not as a separate afterthought bolted onto the old flow, but as one more field written by the same atomic operation that decides everything else.

A team lead reviews a client's approval history to understand a recent dispute over who signed off on a specific post. The approval record clearly shows the item was approved, and when — the column built specifically to record who is sitting right there, indexed for exactly this kind of lookup. It is empty, and it has been empty for every decision ever recorded in that table, regardless of which of the three code paths made it, because none of them had ever set it. After the fix, the identical lookup on a new decision returns a real answer, because the column is now set by the same atomic operation that records everything else about the decision.

Every client approval decision in Centriu Axis now runs as a single, row-locked, all-or-nothing transaction instead of an unprotected multi-step sequence across two tables — and, as part of that same write, a column that had been present and indexed on the approvals table since before this fix, but never once populated by any of its three independent write paths, is finally set on every decision going forward.

This automation governs how a single approval decision is recorded internally — its atomicity and who it attributes the decision to — it does not change what an approver is shown, what they are asked to decide, or who is authorized to make a given decision (a separate, independently fixed mechanism in the same commit).

A database column and its own dedicated index represent a real, ongoing cost — every write to the table maintains that index whether or not the column itself is ever filled in. Carrying that cost while never actually writing the value is the specific, quiet failure mode this fix closes: not a missing feature nobody thought to build, but infrastructure that was built, deliberately, and then silently bypassed by every single code path that could have used it, for as long as the table existed. Closing that gap took checking each of the three write paths individually against the column's own literal name, not trusting that a column with an index this specific must already be in reliable use somewhere.