Which layer the gate sits in decides whether it holds

The decision chain: 4 gates, per statement

A statement entering the gateway passes 4 layers in order. Menu permissions decide whether the role opens this door at all. The capability matrix returns one of three verdicts — allow, send to approval, deny — across role × capability × tier. The high-risk dictionary sets the effective level of that command on that tier. The last layer catches DELETE and UPDATE without a WHERE clause. These 4 layers do not live in one place: the first is a guard mounted on every route, the other three sit inside one judgement function.

The dictionary level is the part most often read backwards. high does not mean "forbidden": it means stopped and forced through approval. mid also routes to approval. off means this layer returns no verdict and the capability matrix decides. The shipped dictionary holds DROP · TRUNCATE · DELETE · ALTER · RENAME · GRANT · REVOKE; UPDATE / INSERT / CREATE are not in it and pass through the capability matrix alone. Operators edit the dictionary, so what ships is a default, not a ceiling.

All 4 layers are stored per tier, across the 5 built-in tiers: PROD · GLI · STAGING · UAT · DEV. The missing-WHERE check used to be a single global switch, so letting a developer practise a DELETE meant dismantling the same guard in production. ADR 0013 moved it per tier: the same DROP stopped for approval on PROD and allowed on DEV is two rows in one table, not two configurations.

Two deliberate short-circuits belong in the open. Session-scoped settings (SET search_path …, Oracle's ALTER SESSION SET …) consult only the "Read data" dimension and skip both the dictionary and the missing-WHERE check. A plan-only EXPLAIN skips the dictionary and the missing-WHERE check as well. The forms that genuinely execute the wrapped statement — EXPLAIN ANALYZE, EXPLAIN PERFORMANCE on DWS/GaussDB — are judged on their real verb, through the whole chain.

Approved, and the gateway still will not run it for you

For a terminal ticket, AegisDB does exactly one thing on approval: it notifies the requester. The approval itself sends no SQL, and the audit row reads "approved, not yet executed". Whoever presses Execute need not be the requester — anyone who can reach that instance may — and every gate is recomputed against the person actually pressing the button: tag authorization, instance maintenance state, a fresh capability-matrix verdict, all against the actor rather than the requester.

"Approved is not executed" holds for terminal tickets only; the other three kinds each work differently. A release ticket: execution belongs to the pipeline stage, and queue-jumping would apply the same change twice. An execution window: approval is what makes it live, and from then on that door opens on schedule by itself. An export request: approval queues it, and the export worker runs it. The last two have no executable command at all — their Command field is a sentence of prose, and sending it down the execution path would hand that prose to the database as SQL.

The product owns the side effect: an approver may execute a ticket they approved themselves — that piece of two-person control was deliberately given up in the closing section of ADR 0010. The rules are evaluated again before the statement goes out, and only a verdict that has turned into "deny" stops it. But re-judging catches only "the rules got stricter"; it cannot catch "the basis for the original judgement no longer holds" — a changed table shape, a row count an order of magnitude larger.

ALTER TABLE orders ADD COLUMN note varchar(64)

Execution windows: a verbal agreement the system closes on time

Dozens of DDL statements in one maintenance run cannot each wake an approver. In practice teams solve this by loosening the rules for the night and tightening them in the morning — which is worse than having no approval at all, because nothing in the system remembers to tighten them, and nobody knows the door is still open.

Execution windows move that agreement into the system. Anyone may request one; submitting creates an approval ticket, and it takes effect only once approved. Windows are scoped to a named database, the name is required, and the form deliberately does not preselect one — an empty database name would let a window quietly cover the whole instance. Two time models: a recurring window in an IANA timezone, or a one-off window with absolute start and end.

"Closes on time" is the accurate phrasing; "the request expires by itself" is not. There is no sweeper job for windows at all: expiry is only the computed observation that the schedule no longer covers the current moment — the row stays, the approval stays valid, and the verdict simply goes back to normal.

Masking happens before results leave the gateway

In the JSON that reaches the browser, the national ID is already gone. Only two places in the whole gateway read result rows out of a user's SQL: terminal results and data exports. Masking hangs on those two, so new call paths are covered by construction. The end-to-end test asserts directly that not one byte of plaintext appears in the raw response body, rather than asserting that the screen shows asterisks.

Rules are configured as table name plus column name (an empty table name means all tables), with three modes: keep head and tail, mask entirely, or hash consistently — the third is for reconciliation, where you can tell whether two rows are the same person without learning who. The terminal and exports compute one shared masking plan; computing it twice would let the downloaded file differ from what the screen showed.

The audit chain: written once, immovable after

Each record's hash = SHA256(previous hash + payload), and the genesis row's predecessor is the empty string. A single-column unique index on prev_hash means two records can never chain onto the same predecessor — the database refuses a fork, rather than the application being trusted not to create one. Alter any record in the middle and every hash after it stops matching.

Verification is a manually triggered action in the console. It recomputes from the genesis row to the tail and reports the first row that does not match. It blocks no writes, stops no service and raises no alert — it is an explicit full-table scan that produces a report with a conclusion. The report says what it cannot cover: truncating the chain from the tail is undetectable here, and catching that requires anchoring the tail outside the gateway periodically.

The release pipeline: why the execute stage always stops and waits

Before the gateway existed, a database change meant one person doing several things in the right order in a terminal: eyeball the SQL for conformance, raise an approval, come back and execute, then check the result by hand. Every step happened, but the order, whether a step happened at all, and what it produced lived only in the memory of the person doing it. The pipeline writes that route down: a flow is an ordered list of stages. (ADR 0005)

Stage types are a closed set of seven: conformance review, human approval, backup / rollback point, apply the change, post-execution verification (must be read-only), human confirmation (the requester cannot clear their own), and result notification. Closed, because each type needs an executor: an unknown type either gets skipped — that is a review nobody ran — or crashes the scheduler.

At the execute stage a release unconditionally stops and waits for confirmation: there is no conditional branch on that gate in the code. Not because "one more confirmation is safer" — that sentence is empty. There are four independent reasons, each going deeper than the last.

One. Approval answers "may this be done"; execution confirmation answers "do it now" — two questions, and the answers sit with two different people. Whether traffic is low, whether the application is stopped, whether the backup is ready: only the requester and whoever is on shift know.

Two. Nobody is watching it. A DROP approved and applied automatically at three in the morning runs with the requester nowhere near it, and if it goes wrong they are the last to find out — while DDL is exactly the category that most needs someone watching the rollback window.

Three. This gate is the "then" in "re-judged against the rules as they are then". A release can sit in approval for hours, and in that time the high-risk dictionary can change, the instance can be moved to a stricter tier, and the requester's roles can change. With automatic application, that moment is one nobody chose; with a confirmation gate it is a moment somebody is answerable for — and the first thing the code does after confirmation is re-judge.

Four. The release pipeline is the most attractive execution bypass in this gateway, and every new execution channel in this repository has failed in the same place. ER6 was one of them — the async channel once let anyone blocked in the terminal resubmit the identical statement as a background job and have it run. And a pipeline is asynchronous, it runs as a service, and its whole purpose is to apply changes to production.

Whose roles the re-judgement uses has to be stated precisely here, because it is the exact opposite of a terminal ticket. On the terminal path, every gate is computed against the person pressing the button. A release is not: confirming execution checks only the confirmer's role, and it does not re-check whether they can reach this instance. The re-judgement that follows confirmation runs against the requester's roles. Both designs make sense on their own, but no single sentence describes them both.

The last point matters most: this pipeline has no automatic rollback, and it does not pretend to. The backup stage runs only the backup statements an operator configured explicitly; with nothing configured it is skipped and says so — reporting a green "backup complete" while nothing happened underneath is the most dangerous line in the whole pipeline. There is no transaction across statements: on failure, the ones that already succeeded are not undone.