Skip to content

Content Moderation

Jump to bottom
mdeguzis edited this page Jul 15, 2026 · 6 revisions

Content Moderation

User-submitted report text is scanned automatically on two layers:

  1. Wordlist (naughty-words) - offline multilingual filter, runs first on every row
  2. OpenAI Moderation API - semantic fallback for anything the wordlist misses (requires OPENAI_API_KEY secret in the proton-pulse-data repo; falls back to wordlist-only if absent)

The scan runs every 4 hours via GitHub Actions (content-moderation.yml) with a 5-hour lookback window, and does a full 25-hour sweep daily at 02:00 UTC. Flagged reports are hidden from public views automatically. Report authors see a "Flagged" badge on their profile page with a plain-language explanation and a link to the Discord server for disputes.

Coverage audit (2026-07-15)

Audit against every user-writable text column across the public tables. The scanner in proton-pulse-data/.github/scripts/moderate-content.mjs handles two tiers:

Primary scan (hits auto-remediate via is_flagged + is_hidden + flagged_reason):

  • user_configs.notes
  • user_configs.title
  • user_configs.launch_options
  • user_configs.form_responses (all string values, recursively)

Aux scans (no moderation columns on these tables, so hits open a GitHub issue labeled content-moderation-review for admin triage instead of mutating the row):

  • user_proton_configs.app_name (added 2026-07-15, closes #331)
  • user_systems.label (added 2026-07-15, closes #332)
  • user_systems.sysinfo_text (added 2026-07-15, MEDIUM followup)
  • flagged_reports.reason_text (added 2026-07-15, MEDIUM followup)

Issue creation is rate-limited to MAX_NEW_ISSUES_PER_RUN (currently 10) so a spammer submitting many bad rows in one lookback window cannot flood the issue tracker. Issues that would exceed the cap are logged and counted in the run summary.

Not yet covered:

Table Column Where it shows Risk
user_report_drafts form_data (JSON) Owner-only; becomes user_configs on submit LOW (published version gets scanned)
user_configs hardware fields (cpu, gpu, os, kernel, ram, gpu_driver, proton_version) Public game page LOW (typed into constrained fields with dropdowns) LOW

LOW entries are practically constrained by the edit form and are not on the roadmap.

To trigger a manual scan:

gh workflow run content-moderation.yml --repo mdeguzis/proton-pulse-data \
  -f dry_run=true -f lookback_hours=720

Admin Roles

The public.admins table controls who can access the admin panel at /admin.html. There are two roles.

Moderator (default)

This is the default role when a user is added to the admins table. Moderators can:

  • View and manage flagged reports (hide, unhide, unflag)
  • View and manage the banned users list (add, remove bans)
  • View and search banned phrases

Moderators cannot add or remove other admins, and cannot change anyone's role.

Super Admin

Super admins have all moderator permissions plus:

  • Add new admins (assign moderator or super_admin role)
  • Change an existing admin's role
  • Remove admins from the table
  • Add and remove banned phrases

Only super admins can modify the admins table. The RLS policies on public.admins enforce this at the database level via the is_current_user_super_admin() SECURITY DEFINER function.

How Roles Are Enforced

Role checks happen in two places:

Database (RLS policies)

  • is_current_user_admin() - SECURITY DEFINER function, returns true if the calling user's auth.uid() is present in public.admins regardless of role. Used to gate read access to the admins, banned_users, and banned_phrases tables.
  • is_current_user_super_admin() - same pattern but also checks role = 'super_admin'. Used to gate INSERT/UPDATE/DELETE on the admins table.

These functions bypass RLS internally to avoid infinite recursion (PostgreSQL 42P17).

Frontend (admin panel)

admin.html checks whether the signed-in user has a row in public.admins before revealing the panel. If the check fails (not in the table), a "not authorized" message is shown instead.

The admin nav link in the top bar (topbar.js) does the same check and stays hidden for non-admins.

Adding an Admin

Only a super admin can do this through the admin panel. The panel's "Admins" tab has a form with fields for the user's UUID (from auth.users), their Steam username, and the role to assign.

To find a user's UUID: look it up in the Supabase dashboard under Authentication > Users, or have the user check their profile page on proton-pulse.com.

Banning a User

When a user is added to public.banned_users, a trigger (trg_hide_configs_on_ban) automatically sets is_hidden = true on all their user_configs rows. This removes their reports from public views immediately without needing a separate moderation step.

Banned users are matched by proton_pulse_user_id (for signed-in users) or client_id (for anonymous submissions).

Flagged Report Actions

Reviewing a flagged report in the admin panel (Flagged Reports tab) gives three outcomes, available for any report regardless of source:

  • Release keeps the report and clears its flagged/hidden state.
  • Shadow ban hides it from everyone except the submitter.
  • Delete report removes it from the site.

Plus In Review (a status change) and Delete flag entry (removes only the flag log row, not the report). The action bar appears at both the top and bottom of the review screen.

How each action lands depends on where the report lives:

  • Pulse reports are rows in user_configs. Shadow ban sets is_hidden = true; the SELECT policy public read non-hidden configs (is_hidden = false OR owner) then shows the row only to its owner, and moderators read all configs lets admins still see it. Release clears is_hidden and is_flagged. Delete removes the row. Writes are scoped to the granular permissions: manage_reports update configs (UPDATE) and delete_reports delete configs (DELETE), both via current_user_has_permission(...).
  • ProtonDB mirror reports are not in our database, so we cannot edit them. Instead a row goes into public.report_moderation keyed by (app_id, report_key, source), and the game page filters those reports out at render time. The upstream ProtonDB mirror is never changed. Release deletes the suppression row.

Each report action also marks the originating flag complete.

Re-flagging and re-opening

flagged_reports has a unique constraint on (app_id, report_key), so a report can only ever have one flag row. Re-flagging therefore needs to re-open the existing flag rather than insert a duplicate. The site calls the submit_flag SECURITY DEFINER function, which upserts on that key and resets status to open with a fresh flagged_at. Anonymous visitors can call it (they cannot UPDATE flagged_reports directly), so a report that was reviewed and then abused again resurfaces in the queue.

Clone this wiki locally