Appearance
Database Schema
Canonical table inventory of public (Supabase PostgreSQL). Full detail in Schema.
| Table | PK | Purpose | Created |
|---|---|---|---|
reports | report_id (text) | Dockets/cases | 000 (baseline) |
agents | discord_id (text) | DPS personnel directory | 000 |
notes | id (identity) | Case working notes | 000 |
evidence | id (identity) | External evidence links | 000 |
timeline | id (identity) | Global per-report audit stream (no FK — retained) | 000 |
pending_actions | id (identity) | Report bot-work queue | 001 (+005 columns) |
contact_messages | id (identity) | Agent ↔ reporter conversation | 002 |
admin_users | discord_id | System Administration allowlist | 009 |
admin_audit_log | id (bigserial) | Append-only forensic admin trail | 009 |
schema_migrations | file | Applied-migration ledger (checksum) | 009 |
admin_health_history | id (bigserial) | Sparse incident log for admin health | 015 |
discord_profiles | discord_id | Bot-captured Discord profile snapshots | 022 |
punishments | punishment_id (AA0AA0) | Retained discipline records (no DELETE) | 029 |
discipline_queue | id (identity) | Discord-side delivery work | 030 (+031 payload) |
events | id (identity) | Canonical append-only activity stream | 033 |
training_scenarios | id (identity) | Data-driven scenario templates | 023 |
training_sessions | id (uuid) | Parent training sessions | 023 (+024/025) |
training_session_scenarios | id (identity) | Scenario instances within a session | 024 |
training_events | id (identity) | Append-only training telemetry (idempotent client_id) | 023 |
training_notes | id (identity) | Trainer/trainee notes (release flag) | 023 |
training_injections | id (identity) | Trainer information injections | 023 |
training_session_members | (session_id, agent_id) | Realtime channel membership | 023 |
Key columns
reports
report_id · reporter · reported · reporter_name · reported_name · reason · notes · evidence · status (8-value CHECK) · assigned_agent · is_supervisor · thread_id · contact_closed · contact_closed_at · contact_closed_by · created_at · updated_at
agents
discord_id · name · status (active|inactive|onboarding) · agent_rank (8-rank set) · security_id · agreement_id · agent_id · clearance_level (1–5) · clearance_id · is_trainer · created_at · updated_at
events
id · event_type · category (8-value CHECK) · actor_id · actor_name · actor_type (agent|system|reporter) · target_type · target_id · report_id · metadata (jsonb) · before_state (jsonb) · after_state (jsonb) · correlation_id · created_at
punishments
punishment_id (CHECK ^[A-Z]{2}[0-9][A-Z]{2}[0-9]$) · user_id · username · reason · detailed_reason · issued_by · issued_at · duration · expires_at · status (Active|Ongoing|Appealed|Revoked|Expired) · evidence · notes · report_id (no FK) · created_at · updated_at
discipline_queue
id · punishment_id (FK) · user_id · action (issue|revoke|user_lookup) · payload (jsonb — full punishment record) · status (pending|processing|completed|failed) · attempts · available_at · started_at · completed_at · last_error · completed_ref · created_at
training_sessions
id (uuid) · scenario_id (nullable pointer) · trainer_id · trainee_id · status (pending|active|paused|completed|aborted) · started_at · active_since · paused_at · ended_at · elapsed_seconds · score · result (pass|fail) · evaluation_comments · released_feedback · trainer_notes · current_state (jsonb) · metadata (jsonb) · timestamps
Relationships
text
reports ──┬── notes (cascade)
├── evidence (cascade)
├── contact_messages (cascade)
├── pending_actions (cascade)
├── timeline (NO FK — retained)
└── events (NO FK — retained)
punishments ── discipline_queue (delivery; no cascade deletion of the record)
training_sessions ──┬── training_session_scenarios (cascade)
├── training_events (cascade)
├── training_notes (cascade)
├── training_injections (cascade)
└── training_session_members (cascade)Privilege summary
public,anon,authenticated: revoked from all tables and functions.service_role: narrow grants per table (never DELETE on retention tables); EXECUTE on the named RPCs only.- RLS: enabled with zero policies except
training_session_members(own-membership SELECT) and therealtime.messageschannel policies.