Schema

All tables live in the public schema of the Supabase PostgreSQL project. The schema is versioned by the migration chain in dps-code-api/migrations/ (000–034); 000_base_schema.sql reconstructs the pre-migration baseline so a fresh project can be provisioned from the repository alone.

General security conventions applied across tables:

• Least privilege: public/anon/authenticated are revoked from everything; service_role (the API's client) receives only the privileges the application actually needs.
• RLS: row level security is enabled with zero policies on sensitive tables, so a misconfigured grant still denies browser roles.
• Triggers: append-only or delete-blocking triggers protect audit and retention records as a second independent layer.
• No browser data access: browser JWTs never receive table access; all data flows through the API.

Working-data tables

reports

The docket/case table. PK: report_id (text).

Column	Notes
report_id	text PK; bounded (≤64 chars, [A-Za-z0-9_\-:.#]) at the API
reporter, reported	Discord IDs of the parties (identity keys)
reporter_name, reported_name	Display-name snapshots (migration 003)
reason, notes, evidence	Case content; notes/reason are bot-only patchable
status	Constrained to the 8 statuses (migration 017)
assigned_agent	Assignee identifier (Name (userid) or bare identifier)
is_supervisor	Supervisor-flag; bot-only patchable (de-escalation guard)
thread_id	Discord thread id for the bot
contact_closed, contact_closed_at, contact_closed_by	Contact-thread close state (migration 004)
created_at, updated_at	Timestamps (indexed)

Relationships: parent of notes, evidence, contact_messages, pending_actions (all ON DELETE CASCADE — working data); timeline and events deliberately have no FK (audit retention).

Mutation authority: rpc_create_report, rpc_report_action, rpc_delete_report, rpc_admin_report_correct, and the API's PATCH path (field whitelist). Direct table writes by the API are limited to simple whitelisted field updates.

notes

Report-scoped working notes. PK: id (bigint identity). Columns: report_id (FK cascade), note, author, created_at. Written only by rpc_add_note (note + timeline + event atomically). Indexed on (report_id, created_at).

evidence

External media links. PK: id (bigint identity). Columns: report_id (FK cascade), description, url (http(s)-only, validated), submitted_by, created_at. Written by rpc_create_report (initial item) and rpc_add_evidence. See Evidence.

pending_actions

The report bot-work queue. PK: id (bigint identity). Columns: report_id (FK cascade), action (validate | invalidate | investigate | contact_reporter | contact_closed), requested_by, reason, status (pending | processing | success | failed), result_note, attempts (migration 005), started_at, completed_at, created_at.

Mutation authority: only the queue RPCs: rpc_claim_action, rpc_requeue_stale_actions, rpc_complete_action, rpc_action_requeue, and the admin queue RPCs. Indexes support claim (status, created_at) and stale recovery (status, started_at partial).

contact_messages

Agent ↔ reporter conversation. PK: id (bigint identity). Columns: report_id (FK cascade), sender (agent|reporter), sender_name, body, created_at. Written by rpc_contact_reply, rpc_contact_respond, and rpc_report_action (opening message). One open thread per report is enforced by the RPC layer (CONTACT_ACTIVE, DUPLICATE_ACTION).

Identity and directory

agents

DPS personnel. PK: discord_id (text — the canonical identity).

Column	Notes
name	Display name
status	active | inactive | onboarding
agent_rank	Fixed rank set; Director-gated writes
security_id, agreement_id, agent_id, clearance_id	Bot-generated onboarding identifiers
clearance_level	1–5; Director-gated changes
is_trainer	Training eligibility (migration 023; admin-editable via 028)
created_at, updated_at	Timestamps

Mutation authority: rpc_admin_agent_update (single authoritative path, audited) and rpc_agent_set_onboarding_fields (bot lane through the same RPC). The API never writes agents directly.

admin_users

System Administration allowlist. PK: discord_id. Columns: name, granted_by, granted_at, active (soft revoke). Grants/revokes go through rpc_admin_access_grant/revoke/remove (Director-only, audited). Migration 011 stripped service_role back to SELECT/INSERT/UPDATE (no DELETE/TRUNCATE).

discord_profiles

Stored Discord profile snapshots captured by the bot. PK: discord_id. Columns: username, display_name, avatar, banner, is_bot, account_created_at, server_nickname, server_joined_at, server_member, roles (jsonb), highest_role, public_flags, last_updated, created_at.

Mutation authority: the API upserts on discord_id (on_conflict="discord_id") from bot-supplied payloads only; field whitelist enforced in db_access._normalise_discord_profile. service_role gets SELECT/INSERT/UPDATE only.

Audit and event tables

timeline

The global per-report audit stream (legacy, retained). PK: id (bigint identity). Columns: report_id (no FK — rows survive report deletion), event, by, created_at. Read via rpc_audit_log / rpc_audit_log_v2 (/audit). Written by the transactional RPCs alongside the canonical events. Append-only by convention (no trigger — see Audit findings).

events

The canonical, append-only, machine-readable activity stream (migration 033). PK: id (bigint identity). Columns: event_type, category (CHECK: report | contact | punishment | delivery | queue | agent | admin | legacy), actor_id (canonical Discord ID), actor_name (display snapshot), actor_type (agent | system | reporter), target_type, target_id, report_id (no FK), metadata (jsonb), before_state/after_state (jsonb, admin mutations only), correlation_id, created_at.

Protections: revoke all from public/anon/authenticated/service_role then grant select, insert (plus sequence usage) to service_role; RLS enabled with zero policies; append-only trigger blocks UPDATE/DELETE/TRUNCATE. Written only via rpc_write_event (same transaction as the mutations).

admin_audit_log

Forensic trail for System Administration mutations. PK: id (bigserial). Columns: agent_id, agent_name, action, target, reason, before_state, after_state (jsonb), correlation_id, created_at. Append-only trigger; service_role SELECT+INSERT only. Written by admin_write_audit, which dual-writes the canonical event.

admin_health_history

Sparse incident log for the admin health page (migration 015). PK: id (bigserial). Columns: recorded_at, overall_status, sections (jsonb), notes. Append-only trigger; written by rpc_admin_health_record when the overall status changes.

schema_migrations

Applied-migration ledger (migration 009). PK: file (text). Columns: applied_at, checksum. Written by apply_rpc_migrations.py; read by the admin Schema tab (rpc_admin_schema_status).

Discipline tables

punishments

Retained discipline records (migration 029). PK: punishment_id (text, CHECK AA0AA0 format). See Punishments for the full field table. Protections: service_role SELECT/INSERT/UPDATE only (never DELETE); RLS with zero policies; DELETE/TRUNCATE-blocking trigger; report_id has no FK (survives report deletion).

discipline_queue

Discord-side delivery work for punishments (migration 030). PK: id (bigint identity). Columns: punishment_id (FK → punishments), user_id, action (CHECK: issue | revoke; user_lookup added by 031 via the same table with a nullable FK on the 031 revision), payload (jsonb — full punishment record from 031), status (pending | processing | completed | failed), attempts, available_at (retry pacing), started_at, completed_at, last_error, completed_ref, created_at.

Protections: unique partial index on (punishment_id, action) for at most one outstanding delivery per transition; partial indexes for claim and stale recovery. Mutated only by the discipline RPCs.

Training tables

training_scenarios

Data-driven scenario templates. PK: id (bigint identity). Columns: slug (unique), title, description, difficulty (basic|intermediate|advanced), category, version, prologue, summary (jsonb), definition (jsonb — initial report, profiles, evidence, objectives, evaluation metadata), published, timestamps.

training_sessions

Parent training session. PK: id (uuid, default gen_random_uuid()). Columns: scenario_id (nullable since 025 — the current/active pointer; children live in training_session_scenarios), trainer_id, trainee_id (agents.discord_id), status (CHECK: pending|active|paused|completed|aborted), started_at, active_since, paused_at, ended_at, elapsed_seconds, score (numeric(5,2)), result (pass|fail), evaluation_comments, released_feedback, trainer_notes, current_state (jsonb — synchronized with the active child), metadata, timestamps.

training_session_scenarios

Scenario instances within a session (migration 024). PK: id (bigint identity). Columns: session_id (FK cascade), scenario_id (FK), status (waiting|active|completed), state (jsonb — per-scenario engine state), activated_at, completed_at, timestamps.

training_events

Append-only action/system/trainer event log. PK: id (bigint identity). Columns: session_id (FK cascade), actor_id, actor_type (CHECK: trainer|trainee|system), event_type, target, client_id (idempotency key), payload (jsonb), created_at. Unique partial index on (session_id, client_id) where client_id is not null makes retried client actions idempotent.

training_notes

Trainer private/releasable notes and trainee investigation notes. PK: id. Columns: session_id (FK cascade), author_id, author_type (trainer|trainee), body, released (trainer notes start private), timestamps.

training_injections

Trainer-sent supplemental information. PK: id. Columns: session_id (FK cascade), sender_id, injection_type, title, content, payload, created_at.

training_session_members

Membership backing Realtime channel authorization. PK:(session_id, agent_id). Columns: role (trainer|trainee), created_at. The only training table with a narrow authenticated SELECT policy (a user's own membership rows).

Mutation authority summary

Table	Authoritative writers
reports	rpc_create_report, rpc_report_action, rpc_delete_report, rpc_admin_report_correct, API PATCH (whitelist)
notes, evidence	rpc_add_note, rpc_add_evidence, rpc_create_report (initial evidence)
pending_actions	queue RPCs + rpc_report_action/contact RPCs (enqueue)
contact_messages	contact RPCs + rpc_report_action (opening message)
agents	rpc_admin_agent_update (all paths)
admin_users	rpc_admin_access_*
discord_profiles	API upsert (bot-supplied, whitelisted)
timeline	transactional RPCs
events	rpc_write_event only
admin_audit_log	admin_write_audit only
punishments	rpc_punishment_create/update/revoke
discipline_queue	discipline RPCs
training tables	the API (service role) — no RPCs, no browser access

Related reading

• Migrations
• RPC layer
• Row level security
• Reference — schema