Approval workflow — implementation¶

The exact DDL + Python for the state machine from the overview. Every transition is a single atomic UPDATE ... WHERE ... RETURNING; every audit event is written by a SECURITY DEFINER trigger the app cannot bypass.

Verified 2026-08-09

PostgreSQL READ COMMITTED re-checks predicates after row-lock acquisition on UPDATE: postgresql.org/docs/current/transaction-iso.html
SELECT ... FOR UPDATE SKIP LOCKED: postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE
SECURITY DEFINER functions: postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY

The schema¶

Three tables — approval, approval_event, approval_grant — plus one enum. Composes cleanly on top of the audit-column DDL from the surface intro.

CREATE TYPE approval_status AS ENUM (
    'proposed', 'pending', 'approved', 'rejected',
    'withdrawn', 'expired', 'executed'
);

CREATE TABLE approval (
    id             bigserial PRIMARY KEY,
    action_kind    text NOT NULL,
    payload        jsonb NOT NULL,
    proposer_oid   text NOT NULL,
    status         approval_status NOT NULL DEFAULT 'pending',
    approver_oid   text,
    decided_at     timestamptz,
    executed_at    timestamptz,
    expires_at     timestamptz NOT NULL DEFAULT (now() + interval '24 hours'),
    created_at     timestamptz NOT NULL DEFAULT now(),

    -- SEPARATION OF DUTIES enforced by the DB, not the app.
    CONSTRAINT approver_distinct_from_proposer
        CHECK (approver_oid IS NULL OR approver_oid <> proposer_oid),

    -- Approver + decided_at travel together — one can't exist without the other.
    CONSTRAINT approver_and_decided_at_together
        CHECK ((approver_oid IS NULL) = (decided_at IS NULL))
);

-- Grants — who may approve which action kinds. Itself audited.
CREATE TABLE approval_grant (
    approver_oid text NOT NULL,
    action_kind  text NOT NULL,
    granted_by   text NOT NULL,
    granted_at   timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (approver_oid, action_kind)
);

-- Append-only ledger. See the SECURITY DEFINER trigger below —
-- the workload role has INSERT via the trigger only, no direct DML.
CREATE TABLE approval_event (
    id             bigserial PRIMARY KEY,
    approval_id    bigint NOT NULL REFERENCES approval(id),
    at             timestamptz NOT NULL DEFAULT now(),
    from_status    approval_status,
    to_status      approval_status NOT NULL,
    actor_oid      text NOT NULL,                -- the human/agent whose action caused this event
    reason         text
);

The approver_distinct_from_proposer check is the load-bearing separation-of-duties rule from the overview — expressed once in DDL, enforced regardless of what the app code does.

The atomic transition¶

The one query the approve endpoint runs. Every guard sits in the WHERE — no client-side "check then update":

UPDATE approval
   SET status       = 'approved',
       approver_oid = $1,          -- the verified end-user oid
       decided_at   = now()
 WHERE id           = $2
   AND status       = 'pending'
   AND proposer_oid <> $1
   AND EXISTS (SELECT 1 FROM approval_grant
                WHERE approver_oid = $1
                  AND action_kind  = approval.action_kind)
RETURNING id, action_kind, payload, proposer_oid;

Why this is safe under concurrency (from the PG READ COMMITTED docs):

"The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row."

Two concurrent approvals ⇒ Postgres serializes on the row lock; the second re-checks status = 'pending' after the first commit, sees status = 'approved', and the WHERE is falsy. RETURNING returns zero rows. The caller's contract is: zero rows ⇒ refuse. No double-approval, no double-audit-event, no double-execute.

Same shape for reject, withdraw, expire, execute — each with its own guard predicate:

-- Reject: also requires distinct + granted, mirrors approve.
UPDATE approval SET status='rejected', approver_oid=$1, decided_at=now()
 WHERE id=$2 AND status='pending' AND proposer_oid <> $1
   AND EXISTS (SELECT 1 FROM approval_grant
                WHERE approver_oid=$1 AND action_kind=approval.action_kind)
RETURNING id;

-- Withdraw: only the proposer can withdraw, and only while pending.
UPDATE approval SET status='withdrawn'
 WHERE id=$2 AND status='pending' AND proposer_oid=$1
RETURNING id;

-- Expire: batched sweeper; NO oid check because it's a system transition.
UPDATE approval SET status='expired'
 WHERE status='pending' AND expires_at < now()
RETURNING id;

-- Execute: only from approved -> executed, exactly once.
UPDATE approval SET status='executed', executed_at=now()
 WHERE id=$1 AND status='approved'
RETURNING id, action_kind, payload;

The audit trigger — append-only, app can't forge¶

CREATE OR REPLACE FUNCTION trust.log_approval_event() RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER                -- runs as the trigger owner (a dedicated audit role)
SET search_path = trust, pg_catalog
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO approval_event (approval_id, from_status, to_status, actor_oid)
        VALUES (NEW.id, NULL, NEW.status, NEW.proposer_oid);
        RETURN NEW;
    END IF;

    IF OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO approval_event (approval_id, from_status, to_status, actor_oid)
        VALUES (
            NEW.id,
            OLD.status,
            NEW.status,
            COALESCE(NEW.approver_oid, NEW.proposer_oid,
                     current_setting('chiron.actor_oid', true))
        );
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER approval_audit
    AFTER INSERT OR UPDATE OF status ON approval
    FOR EACH ROW EXECUTE FUNCTION trust.log_approval_event();

-- Lock down direct writes to approval_event.
REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON approval_event FROM PUBLIC;
-- Grant SELECT + INSERT to the trigger owner only.
ALTER FUNCTION trust.log_approval_event() OWNER TO trust_audit;
GRANT INSERT ON approval_event TO trust_audit;
GRANT SELECT ON approval_event TO chiron_app;
-- The trigger inserts as `trust_audit` (SECURITY DEFINER) even when
-- called by chiron_app. Direct INSERT/UPDATE/DELETE from chiron_app fails.

The SECURITY DEFINER line is the load-bearing property: the trigger inserts into approval_event as its owner, not as the caller. Grants can therefore forbid the app role from writing to approval_event directly — the only path to insert is through an audited state change on approval.

The SET search_path in the trigger is per the PG SECURITY DEFINER docs — pin it so a malicious search-path can't shadow approval_event.

The Python side¶

import psycopg

def propose(proposer_oid: str, action_kind: str, payload: dict) -> int:
    """Agent (or human) proposes an action. Returns the approval id."""
    with psycopg.connect(DATABASE_URL) as conn, conn.transaction():
        with conn.cursor() as cur:
            # Stamp actor for the audit-column pattern too (see attributed-writes).
            cur.execute("SELECT set_config('chiron.actor_oid', %s, true)",
                        (proposer_oid,))
            cur.execute(
                """INSERT INTO approval (action_kind, payload, proposer_oid, status)
                   VALUES (%s, %s, %s, 'pending') RETURNING id""",
                (action_kind, psycopg.types.json.Json(payload), proposer_oid),
            )
            return cur.fetchone()[0]


def approve(approver_oid: str, approval_id: int) -> dict | None:
    """One atomic transition. Returns the approved row, or None on refusal.

    None means: already decided, or self-approval, or not granted for
    this action kind. Caller maps None -> HTTP 409/403.
    """
    with psycopg.connect(DATABASE_URL) as conn, conn.transaction():
        with conn.cursor() as cur:
            cur.execute("SELECT set_config('chiron.actor_oid', %s, true)",
                        (approver_oid,))
            cur.execute(
                """UPDATE approval
                      SET status='approved', approver_oid=%s, decided_at=now()
                    WHERE id=%s
                      AND status='pending'
                      AND proposer_oid <> %s
                      AND EXISTS (SELECT 1 FROM approval_grant
                                   WHERE approver_oid=%s
                                     AND action_kind=approval.action_kind)
                   RETURNING id, action_kind, payload, proposer_oid""",
                (approver_oid, approval_id, approver_oid, approver_oid),
            )
            row = cur.fetchone()
            if not row:
                return None            # refused — see docstring
            cols = [c.name for c in cur.description]
            return dict(zip(cols, row))

The approve function's contract is the important surface: it returns None or the approved row. The three refusal reasons collapse to one caller signal; the exact reason lives in the audit trail (or, if the caller needs to distinguish, an additional pre-check SELECT — but the decision stays atomic).

The executor — idempotent by construction¶

Approved rows still need to run their effect. Two failure modes to defend against:

Double-execute — a worker crashes after running the side effect but before writing status='executed'; a retry runs the effect again.
Lost-execute — a worker claims a row but crashes without committing; nothing else picks it up.

The pattern:

-- Claim-and-mark, one atomic query using SKIP LOCKED so workers don't block.
WITH claimed AS (
    SELECT id FROM approval
     WHERE status = 'approved'
     ORDER BY decided_at
     FOR UPDATE SKIP LOCKED
     LIMIT 1
)
UPDATE approval
   SET status='executed', executed_at=now()
  FROM claimed
 WHERE approval.id = claimed.id
   AND approval.status = 'approved'
RETURNING approval.id, approval.action_kind, approval.payload;

Handling:

Zero rows returned ⇒ nothing to do; sleep and retry.
One row returned ⇒ this worker owns the row for this transaction. Run the effect; commit. If the effect fails, ROLLBACK — the status='executed' transition rolls back with it and the row goes back to approved. Another worker retries.
The effect must itself be idempotent — a natural key (a slugified payload.hash, a client-provided Idempotency-Key) so a crash between "effect done" and "commit" doesn't create two effects. This is the one thing the DB can't guarantee for you; make it a contract on every registered action_kind.

FOR UPDATE SKIP LOCKED lets N workers process the queue in parallel with no worker blocking another (PG docs — the SKIP LOCKED variant "skips over any locked rows").

What CANNOT go wrong (and why)¶

Two humans approve simultaneously → one wins by row-lock serialization; the other sees zero rows returned and gets a "refused: already decided" response. No double-audit-event.
Self-approval attempt → CHECK constraint refuses at the DB. The client-side error message can be nice, but the DB is the enforcer.
Unauthorized approver → EXISTS subquery in the WHERE returns false; UPDATE returns zero rows; caller gets refusal.
Approver becomes ungranted between propose and approve → the WHERE re-check catches it. Deletions from approval_grant invalidate future approvals immediately.
Compromised app forges an audit row → app has no direct INSERT/UPDATE/DELETE on approval_event; the only path is through the trigger, which runs as a different owner and always records the true NEW.status transition.
Approver clicks Approve twice → both requests fire the same UPDATE; only one matches the status='pending' guard.
Executor crashes mid-effect → the surrounding transaction rolls back; row returns to approved; another worker picks it up. If the effect had a partial side-effect, the effect's own idempotency key catches the replay.

What CAN go wrong (that this layer doesn't solve)¶

The effect itself is not idempotent. DB gives you at-least-once execution; the effect has to make that safe. Give every action_kind an idempotency key contract.
A hostile approver with a valid grant. This layer says "who did it and when"; it doesn't say "were they right to approve". Grants are the policy surface; audit is your after-the-fact catch.
The proposal's payload is wrong. Approval says the human accepted this specific payload. If the executor re-reads the world and does something different, the approval means less than it looks. Prefer effects that are fully specified by the payload alone.

Verify (local, no cloud)¶

docker run --rm -d --name pg -e POSTGRES_PASSWORD=chiron -p 5432:5432 pgvector/pgvector:pg16

psql postgresql://postgres:chiron@127.0.0.1/postgres -f examples/trust/sql/schema.sql
psql postgresql://postgres:chiron@127.0.0.1/postgres -f examples/trust/approval/sql/schema.sql

python -m compileall examples/trust/approval/service

The example includes an if __name__ == "__main__": smoke test that proposes, tries to self-approve (refused by the CHECK), approves as a distinct authorized user (accepted), then approves again (refused as already-decided). Each transition writes exactly one row into approval_event.

Where the arc goes next¶

Federated approvals across accounts / tenants (approver in one org, workload in another).
Quorum-N (M-of-N required approvals) — a generalization of the pattern here, one additional pending_approvals count column and an INSERT ON CONFLICT DO NOTHING on a per-approver join table.