Appearance
RPC Layer
The RPC layer is the set of named PostgreSQL functions in public that own the database's authoritative mutations. They exist because several operations are multi-step and race-prone: a report action touches status + timeline + event
- queue, a claim must be atomic between two bot ticks, and a deletion must never succeed without its audit record. Doing these in application code with separate table writes would reintroduce partial state, lost updates, and unaudited mutations.
Principle: application code should not recreate authoritative multi-step database operations when an atomic database operation already exists.
Why authoritative database functions exist
- Atomicity. Each RPC is one database transaction: either every write commits or none do. A report action can never leave a status change without its timeline/event/queue row.
- Race prevention. RPCs lock the rows they guard (
SELECT … FOR UPDATE,FOR UPDATE SKIP LOCKED) and re-check preconditions inside the transaction, so two concurrent requests cannot both pass a status guard. - Actor identity. Mutations accept the actor's canonical Discord ID as a dedicated parameter (
p_actor_id/p_requester_id), so audit/event rows record identity from an authoritative source — never from display text. - Authorization in depth. RPCs re-validate field whitelists, statuses, ranks, and Director authority even though the API already checked them. A route bug cannot bypass the database.
- Audit/event creation in the same transaction. Destructive and high-impact operations write their forensic record inside the mutation's transaction — delete-first-log-after is impossible.
- Least privilege. EXECUTE is revoked from
public/anon/authenticatedand granted only toservice_role(migration 016 lockdown), so PostgREST browser roles can never invoke them.
Transaction boundaries and row locking
| RPC | Lock | Guard |
|---|---|---|
rpc_report_action | SELECT … FOR UPDATE on the report row | allowed-status set, duplicate contact, already-claimed |
rpc_claim_action | FOR UPDATE SKIP LOCKED on pending_actions | oldest pending row for the queue |
rpc_discipline_claim | FOR UPDATE SKIP LOCKED on discipline_queue | oldest available (available_at <= now()) |
rpc_complete_action / rpc_action_requeue | FOR UPDATE on the action row | status ∈ pending/processing (or failed for requeue) |
rpc_discipline_complete | FOR UPDATE on the queue row | status ∈ pending/processing |
rpc_punishment_revoke | FOR UPDATE on the punishment row | not already Revoked/Expired |
rpc_delete_report | (pre-image select, then delete) | exists; audit write in same transaction |
rpc_admin_agent_update | (row read) | field whitelist, rank/clearance/Director/self-edit guards |
Which operations are implemented as RPCs
Report domain
| RPC | Operation |
|---|---|
rpc_create_report | Report + timeline + event + optional initial evidence in one transaction |
rpc_report_action | The report action state machine: validate / invalidate / investigate / contact_reporter / claim / conclude — status/assignment + timeline + event + contact message + queue insert |
rpc_add_note | Note + timeline + event + updated_at |
rpc_add_evidence | Evidence + timeline + event + updated_at |
rpc_delete_report | Pre-image + delete + forensic audit + report.deleted event |
rpc_list_reports | Server-side filter/sort/count/pagination (read) |
Contact domain
| RPC | Operation |
|---|---|
rpc_contact_reply | Agent message + timeline + event + bot-DM queue insert |
rpc_contact_respond | Reporter message + timeline + event |
rpc_close_contact_thread | Close state + timeline + event + closing-DM queue insert |
Queue domain (report bot work)
| RPC | Operation |
|---|---|
rpc_claim_action | Atomic oldest-action claim (pending → processing) + queue.action_claimed |
rpc_requeue_stale_actions | Stale-processing recovery (requeue or fail past the cap) + events |
rpc_complete_action | Locked terminal completion + queue.action_completed/failed |
rpc_action_requeue | Locked failed → pending + queue.action_requeued |
Punishment / delivery domain
| RPC | Operation |
|---|---|
rpc_punishment_create | ID generation (collision-retried) + row + audit + punishment.issued + issue delivery enqueue |
rpc_punishment_update | Whitelisted edit + before/after audit + punishment.updated |
rpc_punishment_revoke | Locked revoke + audit + punishment.revoked + revoke delivery |
rpc_discipline_enqueue | Internal enqueue helper + delivery.queued |
rpc_discipline_enqueue_user_lookup | Profile-lookup enqueue (one outstanding per user) |
rpc_discipline_claim | Atomic pickup with live punishment embed + delivery.claimed |
rpc_discipline_complete | Locked outcome recording (success / paced retry / terminal failure) + delivery events |
rpc_discipline_requeue_stale | Stale-processing recovery + delivery events |
Agent / admin domain
| RPC | Operation |
|---|---|
rpc_admin_agent_update | The single audited agent-record mutation (all guards + before/after audit + agent.updated) |
rpc_agent_set_onboarding_fields | Bot lane through the above (narrower whitelist, no rank) |
rpc_admin_access_list / grant / revoke / remove | Allowlist management (Director-only, audited) |
rpc_admin_queue_requeue / cancel / sweep | Queue maintenance (audited) |
rpc_admin_audit_list | Paginated admin audit trail (read) |
rpc_admin_report_correct | Whitelisted report correction (Director-only, audited) |
rpc_admin_schema_status / reload | Schema inventory + PostgREST reload NOTIFY |
rpc_admin_health / record / history | Health metrics + sparse incident history |
Event / audit domain
| RPC | Operation |
|---|---|
rpc_write_event | The single validated event write path (validates type/category/actor_type/metadata) |
admin_write_audit | Forensic audit row + dual-written canonical event (via admin_audit_event_map) |
admin_audit_event_map | Maps admin actions to the event taxonomy |
rpc_audit_log / rpc_audit_log_v2 | Server-side search/filter/sort/pagination of timeline |
Security model of the RPCs
- SECURITY INVOKER is the default for new functions (executed with the caller's privileges — service_role's table grants);
SECURITY DEFINERis used only where the function must own its writes regardless of caller (the report RPCs), always with a fixedsearch_path. - EXECUTE is revoked from
public,anon,authenticatedand granted toservice_role— enforced globally by migration 016 and restated per function by later migrations. - Errors are raised as machine-readable tags (
STATUS_CONFLICT,DIRECTOR_REQUIRED,ACTION_ALREADY_COMPLETED, …) that the API maps to HTTP statuses; routes never parse free-form database text for control flow. - Field-level whitelists are enforced inside the functions (
jsonb_each_textagainst a constant array), so a client can never slip an unlisted field through. - Director/self-edit/hierarchy guards re-derive the requester's live rank from
agentsinside the RPC (admin_requester_is_director,admin_requester_can_manage_rank), so demotions take effect immediately.
When to add an RPC (and when not to)
Add one when the operation: (a) writes multiple rows that must commit together, (b) has a race window that row locking must close, (c) must produce an audit/event record in the same transaction, or (d) must re-check authority the API also checks (defense in depth).
Don't add one when a simple single-row read/write through the existing API surface suffices, or when the operation is training-runtime state that the API already owns exclusively (training deliberately has no RPCs — the API is the only writer, and introducing RPCs there is not a goal; see migration 023).
WARNING
Project rule (from instructions.md): never introduce an RPC merely to solve a problem the existing API/database architecture already solves. Prefer reusing an existing authoritative mutation over layering a new one.