Skip to content

Supabase Voting

Jump to bottom
mdeguzis edited this page Jul 12, 2026 · 7 revisions

Supabase Schema

This page documents the current Supabase tables used by Proton Pulse.

Tables

public.user_configs

Pulse-native compatibility reports submitted via the web app. One report per user per game (enforced by unique constraint on client_id + app_id).

Migration: supabase/migrations/20260410_create_user_configs.sql

Column Type Notes
id bigint (identity PK) Auto-incrementing report ID
client_id text Anonymous browser client ID (hashed)
app_id text Steam App ID
title text Game title
cpu text CPU model string
gpu text GPU model string
gpu_driver text GPU driver version
gpu_vendor text nvidia / amd / intel / other
ram text e.g. 16 GB
os text Constrained enum (see below)
kernel text Kernel version string
proton_version text e.g. Proton 9.0-4, GE-Proton9-15
duration text Play duration; default unreported
rating text platinum / gold / silver / bronze / borked
notes text Free-text notes
launch_options text Steam launch options string
enabled_vars jsonb Map of enabled environment variables
confidence_score smallint 0–200; null if not computed
source text proton-pulse (web) or web-linux / web-windows / web-macos / web-steamdeck
created_at timestamptz Insert time

Check constraints: rating, os, proton_version (regex), ram (regex), gpu_vendor, source, confidence_score range.

Unique constraint: (client_id, app_id) — one report per browser per game.

public.user_proton_configs

Proton configs saved and synced by the Decky Plugin. One config per voter_id + app_id, upserted on conflict.

Column Type Notes
id bigint (BIGSERIAL) Auto-incrementing config ID
voter_id text Anonymous plugin voter ID (hashed device ID)
app_id integer Steam App ID
app_name text Game title at sync time
config jsonb Full TrackedConfig blob (see below)
updated_at timestamptz Last upsert time

Primary key: (voter_id, app_id)

config JSON blob (TrackedConfig)

Defined in src/lib/trackedConfigs.ts:

Field Type Notes
appId number Steam App ID
appName string Game title
profileName string Config profile name (empty = unnamed)
protonVersion string Applied Proton version
launchOptions string Steam launch options
enabledVars Record<string, string> Enabled env vars
appliedAt number Unix timestamp of last apply
source string protondb / protondb-local / user
isEdited boolean? true if a ProtonDB config was modified
cpu string? CPU at apply time
gpu string? GPU at apply time
gpuVendor string? nvidia / amd / intel / other
gpuDriver string? GPU driver at apply time
ram string? RAM at apply time
os string? OS at apply time
kernel string? Kernel at apply time
isNonSteam boolean? true for non-Steam games

public.user_plugin_settings

Per-user plugin settings synced by the Decky Plugin (bookmarks, preferences, etc.).

SQL: docs/dev/user_plugin_settings.sql

Column Type Notes
voter_id text Anonymous plugin voter ID
plugin_id text Default proton-pulse
payload jsonb LocalDataBackupPayload blob
updated_at timestamptz Last upsert time

Primary key: (voter_id, plugin_id)

public.report_moderation

Suppression list for ProtonDB mirror reports an admin has acted on. Pulse reports use user_configs.is_hidden, but ProtonDB reports come from the static mirror and have no database row, so we record the moderation here and the game page filters them out at render time.

Migration: supabase/migrations/20260618040000_report_moderation.sql

Column Type Notes
id bigint (identity PK)
flag_id bigint FK to flagged_reports(id), ON DELETE SET NULL
app_id text Steam App ID
report_key text timestamp:gpu(20):protonVersion(15), matches the key the game page computes
source text Report source (e.g. protondb)
action text shadowban or deleted (both hide it; kept for audit)
reason text Optional
moderated_by uuid
flagged_at timestamptz Original flag time, for ordering
moderated_at timestamptz Default now()

Unique: (app_id, report_key, source)

Functions

public.submit_flag(...)

SECURITY DEFINER upsert used by the site to flag a report. flagged_reports has a unique constraint on (app_id, report_key), so a plain insert 409s on re-flag. This function inserts or, on conflict, re-opens the existing flag (status = 'open', fresh flagged_at). Granted to anon and authenticated.

Migration: supabase/migrations/20260618050000_submit_flag_reopen.sql

public.admin_erase_user(p_user_id uuid, p_client_id text default null)

SECURITY DEFINER, callable only by super_admin. GDPR right to erasure. Instead of hard-deleting community reports, it anonymizes them in place so aggregate tier data survives:

  • user_configs, user_proton_configs, config_playtime: proton_pulse_user_id and installation_id are nulled, the NOT NULL identity columns (client_id, voter_id) are rewritten to a one-time random token (anon_<uuid>), and anonymized_at is stamped. The report body, including the free-form notes field, is preserved. Users are warned on the submit form, the edit modal, privacy.html, and about.html that notes are public and permanent.
  • A per-erasure random token is required because user_configs has UNIQUE (client_id, app_id) and user_proton_configs has PK (voter_id, app_id); a shared sentinel would collide across erased users who reported the same game.
  • Fully deleted: auth.users, author_avatars, admins, plugin_links, claimed_client_ids, site_events, report_votes, user_configs_history, and user_systems. user_systems is private, owner-only prefill data (not community aggregate; the report hardware is denormalized into user_configs), so it is deleted rather than anonymized.
  • Returns a JSON summary split into anonymized and deleted counts.

Migrations: 20260712140000_admin_erase_user_anonymize.sql (anonymize-in-place), 20260712160000_erase_scrub_user_systems.sql (scrub + keep user_systems)

public.admin_audit_log

Durable record of privileged actions, kept for GDPR accountability without reintroducing identifiable data. Columns: action, actor_user_id (the admin, not the target), target_hash (md5 of the erased user id, so "did you erase me?" is answerable without storing the raw id), anon_token, details (jsonb counts), created_at. Each admin_erase_user call inserts one erase_user row. RLS restricts SELECT to super_admin; there are no client insert grants, so only the security-definer function writes to it.

Migration: supabase/migrations/20260712150000_admin_audit_log.sql

user_systems access and field validation

user_systems is owner-only: SELECT / INSERT / UPDATE / DELETE each have a single policy scoped to proton_pulse_user_id = auth.uid() (authenticated). The earlier wide-open public read/insert/update/delete systems policies (all USING (true) for anon) were dropped, so a user can only ever touch their own saved systems. The plugin upload path uses a service-role client and bypasses RLS.

sysinfo_text is hardware info (the Steam copy-system-info format), not PII. Writes are gated by CHECK constraints so the field cannot be abused: sysinfo_text length 1 to 16384 and printable characters only (no control bytes except tab/newline/carriage return), device_id length 1 to 128 matching ^[A-Za-z0-9._:-]+$, label up to 160 chars. The constraints are the authoritative gate across every writer. The user-system-upload edge function and the web write paths mirror the same rules for clearer errors, but the DB is the source of truth.

Migrations: 20260712170000_user_systems_field_validation.sql (CHECK constraints), 20260712180000_user_systems_lockdown.sql (owner-only RLS)

Grants & RLS

All tables use the Supabase publishable (anon) key — no server-side secret is ever embedded in the plugin or web app.

user_configs

  • anon, authenticated: SELECT, INSERT
  • RLS enabled; SELECT is public read non-hidden configs (is_hidden = false OR owner) plus moderators read all configs for admins with manage_reports. There are no open USING (true) read policies, so is_hidden genuinely hides a row.
  • Moderation writes are permission-scoped: manage_reports update configs (UPDATE) and delete_reports delete configs (DELETE), via current_user_has_permission(...)

report_moderation

  • anon, authenticated: SELECT (so the game page can filter suppressed reports)
  • Admins (EXISTS in public.admins): full INSERT/UPDATE/DELETE

user_proton_configs

  • anon, authenticated: SELECT, INSERT, UPDATE
  • Upserted via Prefer: resolution=merge-duplicates on voter_id,app_id

user_plugin_settings

  • anon, authenticated: SELECT, INSERT, UPDATE
  • DELETE explicitly denied by RLS policy

Identity Model

Both the plugin and anonymous web submissions use a hashed anonymous ID stored in localStorage / Decky settings. No personally-identifying information is stored. Steam-authenticated web users are identified by their Supabase auth.uid() (stored as voter_id / client_id after login).

Clone this wiki locally