Full Table Reference

The complete public schema — every column of all 45 tables, the 11 views, and the one enum. The domain pages explain why; this page is the what. Use it as a lookup.

Notation

PK = primary key · FK → = foreign key to table.column · N in the Null column = the column is nullable. Types are the live Postgres types. This page is generated from the database and reflects the schema as deployed; when a migration changes a table, regenerate the affected section.

Row-level security is enabled on all 45 tables

A column being listed here does not mean a browser client can read it. Many tables are service-role-only / deny-all (reachable only via edge functions or the service key) — e.g. crm_user_roles, player_roles, steam_id_allowlist, device_auth_requests, upload_credentials, all four growth tables, lobby_join_events, permissions/role_permissions. The domain pages note the RLS posture per area; treat the relationships here as logical, not as a read grant.

Enums

Enum	Values
game_store	steam · epic · gog · microsoft · other · riot · rockstar

---

Identity & Access

→ domain page: Identity & Access

playroll_users

The hub. Keyed by user_id (Supabase auth UUID).

Column	Type	Null	Notes
user_id	uuid		PK, FK → auth.users.id (ON DELETE CASCADE)
display_name	text	N
avatar_url	text	N
steam_id	text	N	UNIQUE
discord_id	text	N	UNIQUE
discord_username	text	N
contributor_tier	text		Current program tier (value, not FK); default player.
credits_earned	numeric
credits_claimed	numeric
created_at	timestamptz
updated_at	timestamptz
steam_username	text	N
steam_avatar_url	text	N
steam_hours	integer	N	Steam enrichment ↓
steam_games_owned	integer	N
steam_level	integer	N
steam_profile_public	boolean	N
steam_account_created	bigint	N	Unix ts
steam_account_age_days	integer	N	Trigger-computed (playroll_users_fill_steam_age).
steam_vac_banned	boolean	N
steam_vac_bans_count	integer	N
steam_game_bans_count	integer	N
steam_days_since_last_ban	integer	N
steam_enriched_at	timestamptz	N
steam_friend_count	integer
steam_friends_public	boolean	N
friends_fetched_at	timestamptz	N
network_value	integer		Rolled up from shadow_graph.
network_whale_count	integer
country	text	N	Attribution ↓
language	text	N
utm_source	text	N
utm_medium	text	N
utm_campaign	text	N
referrer	text	N
device_type	text	N
referral_code	text	N
referred_by	text	N
referral_count	integer
priority_score	integer

steam_id_allowlist

Column	Type	Null	Notes
steam_id	text		PK
role	text		FK → player_role_catalog.role; default pioneer.
note	text	N
added_at	timestamptz
added_by	text	N
enabled	boolean

auth_user_steam_lookup

Column	Type	Null	Notes
steam_id	text		PK
user_id	uuid		FK → auth.users.id; UNIQUE. Maintained by auth_user_steam_lookup_sync_trg.
created_at	timestamptz
updated_at	timestamptz

steam_openid_nonces

Column	Type	Null	Notes
nonce	text		PK
seen_at	timestamptz

player_role_catalog

Column	Type	Null	Notes
role	text		PK
rank	integer		Higher = more access.
description	text	N
created_at	timestamptz

player_roles

Column	Type	Null	Notes
id	uuid		PK
user_id	uuid		FK → playroll_users.user_id
role	text		FK → player_role_catalog.role
granted_at	timestamptz
granted_by	text	N

crm_user_roles

Staff RBAC, keyed by email.

Column	Type	Null	Notes
email	text		PK
role	text		CHECK admin / cm / vc / sales. Links by value to role_permissions.role.
granted_at	timestamptz
granted_by	text	N
notes	text	N

permissions

Column	Type	Null	Notes
id	integer		PK
resource	text
action	text		CHECK read / write.
description	text	N
created_at	timestamptz

role_permissions

Column	Type	Null	Notes
role	text
permission_id	integer		FK → permissions.id
granted_by	text	N
granted_at	timestamptz

device_auth_requests

Legacy / superseded — 0 rows, deny-all RLS, replaced by the steam-login redirect flow.

Column	Type	Null	Notes
device_code	text		PK
status	text		Default pending.
access_token	text	N
refresh_token	text	N
user_id	uuid	N	No FK.
user_profile	jsonb	N
created_at	timestamptz
completed_at	timestamptz	N
expires_at	timestamptz		Default now() + 10 min.

upload_credentials

Legacy / unused — 0 rows, no writer, deny-all RLS (service-key only).

Column	Type	Null	Notes
mac_hash	text		PK
user_id	text		UNIQUE (text, not a uuid FK to playroll_users).
created_at	timestamptz
last_used_at	timestamptz	N
is_active	boolean

playroll_user_consents

Append-only consent log (no revoked column; withdrawals are new rows).

Column	Type	Null	Notes
id	uuid		PK
user_id	uuid		FK → auth.users.id (not playroll_users).
consent_type	text		CHECK age_confirmation / tos / privacy / eula / edc_gameplay.
version	text
accepted_at	timestamptz
country	text	N
metadata	jsonb		{action:'withdrawal'} marks a revocation.
ip_address	inet	N
user_agent	text	N

contributor_status_projection

App-side projection written by redeem-activation-code. No PII. (The portal owns the source table contributor_app_links.)

Column	Type	Null	Notes
app_user_id	uuid		PK, FK → auth.users.id (ON DELETE CASCADE).
contributor_id	uuid
link_status	text		CHECK verified (only value).
source_link_id	uuid
steam_id	text	N
linked_at	timestamptz
source_updated_at	timestamptz
last_synced_at	timestamptz

---

Recording Spine

→ domain page: Recording Spine

recording_sessions

Keyed by a text session_id (rec-<epoch-ms>).

Column	Type	Null	Notes
session_id	text		PK
user_id	text
game_name	text	N
chunk_count	integer	N
status	text		Default uploading.
client_ended_at	timestamptz	N
server_verified_at	timestamptz	N
created_at	timestamptz	N
updated_at	timestamptz	N	Trigger-maintained.
detected_resolution	jsonb	N
game_slug	text	N
recording_started_at	timestamptz	N
recording_ended_at	timestamptz	N
duration_seconds	bigint	N	Canonical recording clock. CHECK ≥ 0.
required_artifact_mode	text		CHECK mp4_csv_json (default) / mp4_csv.
mode	text		Recording lane: quest (default) / free_play (EC-475). Populated end-to-end once the upload writer carries the lane; see Recording lanes.
uploadability_status	text	N	CHECK uploadable / blocked / NULL.
block_reason	text	N
lobby_id	uuid	N	By value — no FK (lobby binding enforced by trigger).
hw_snapshot_id	uuid	N	FK → device_hw_snapshots.id
core_version	text	N
ui_version	text	N
installer_version	text	N
transcript_expected	boolean		Set true at session end iff _mic.aac exists (EC-407).
transcript_skipped	boolean		Client-writable via scoped GRANT + RLS (EC-409).
dismissed	boolean		Client-writable "hide from Activity" (EC-411).

s3_uploads

One row per uploaded file. Links to a session by value on session_id.

Column	Type	Null	Notes
id	uuid		PK
s3_key	text
s3_bucket	text		Default playroll-captures.
user_id	text
upload_year	integer		Partition key
upload_month	integer		CHECK 1–12.
upload_day	integer		CHECK 1–31.
session_id	text		Joins to recording_sessions.session_id (value).
chunk_dir	text	N
filename	text
file_type	text		mp4 / csv / json / ogg (legacy).
size_bytes	bigint	N
etag	text	N
uploaded_at	timestamptz
status	text		CHECK uploaded (default) / processing / completed / failed.
lobby_id	uuid	N	FK → lobbies.id (NOT VALID).
recording_started_at	timestamptz	N
recording_ended_at	timestamptz	N
game_slug	text	N	Checked by the s3_uploads_enforce_lobby_game_match trigger (lobby-tagged only).
artifact_kind	text	N	No CHECK; derived from suffix: video/input/meta/transcript; NULL = unclassified (EC-289).

device_hw_snapshots

Dedup key (user_id, hw_fingerprint).

Column	Type	Null	Notes
id	uuid		PK
created_at	timestamptz
updated_at	timestamptz
user_id	text
hw_fingerprint	text
cpu_model	text	N
cpu_architecture	text	N
logical_processors	integer	N
physical_memory_mib	bigint	N
os_release	text	N
primary_gpu_vendor	text	N
primary_gpu_name	text	N
primary_gpu_driver	text	N
primary_gpu_driver_nvidia	text	N
snapshot	jsonb		Full HW blob.
source	text		ui_login (login edge fn, dominant) / core_meta_json (upload Lambda); default core_meta_json.

pipeline_sessions

Composite PK (session_id, environment) — one row per session per environment.

Column	Type	Null	Notes
session_id	text		PK (with environment)
environment	text		PK (with session_id); CHECK dev / prod (default prod).
user_id	text
game_slug	text	N
s3_prefix	text
stage	smallint		Default 0.
status	text		CHECK pending (default) / processing / verified / failed.
mp4_key	text	N
csv_key	text	N
ogg_key	text	N
size_bytes_total	bigint	N
error	text	N
created_at	timestamptz
verified_at	timestamptz	N

pipeline_runs

Column	Type	Null	Notes
run_id	uuid		PK
pipeline_name	text
session_id	text	N
user_id	text	N
trigger_type	text		Default manual.
created_by	text		Default prefect-ui.
status	text		CHECK running (default) / pass / fail / skipped.
steps_executed	text[]	N
step_durations_s	jsonb	N
params_used	jsonb	N
output_prefix	text	N
error_detail	text	N
prefect_flow_run_id	text	N
started_at	timestamptz
completed_at	timestamptz	N

nvenc_known_contenders

Column	Type	Null	Notes
process_name	text		PK
first_seen	timestamptz
last_seen	timestamptz
total_events	bigint
total_users	integer
sample_codecs	text[]
sample_resolutions	text[]

nvenc_contender_user_links

Column	Type	Null	Notes
process_name	text		FK → nvenc_known_contenders.process_name
user_id	text
first_seen	timestamptz
last_seen	timestamptz

bug_reports

Column	Type	Null	Notes
id	uuid		PK
created_at	timestamptz
user_id	uuid
steam_id	text	N
hw_config	jsonb
installer_version	text	N
ui_version	text	N
core_version	text	N
title	text		CHECK length 1–200.
description	text		CHECK length 1–10000.
status	text		CHECK logged (default) / in_progress / fixed / verified / wont_fix.
category	text		CHECK ui / recording / performance / onboarding / other.
images	text[]
linear_ticket	text	N	Auto-filled by bug_reports_to_linear trigger (AFTER INSERT).
updated_at	timestamptz
steam_username	text	N
connection	jsonb	N
electron_runtime	jsonb	N

---

Lobby & Catalog

→ domain page: Lobby & Catalog

lobbies

Column	Type	Null	Notes
id	uuid		PK
lobby_code	text
creator_id	text
game_slug	text		FK → eligible_games.slug (ON UPDATE CASCADE / ON DELETE RESTRICT).
status	text		CHECK active (default) / closed / expired.
max_players	integer		CHECK 2–16 (default 16).
created_at	timestamptz
expires_at	timestamptz

lobby_participants

Composite PK (lobby_id, user_id) — current membership.

Column	Type	Null	Notes
lobby_id	uuid		PK (with user_id); FK → lobbies.id
user_id	text		PK (with lobby_id)
joined_at	timestamptz
session_id	text	N	Joins to recording_sessions.
server_recording_started_at	timestamptz	N

lobby_join_events

Append-mostly history. INSERT on join; leave UPDATEs left_at (no new row). Driven by triggers on lobby_participants.

Column	Type	Null	Notes
id	bigint		PK
lobby_id	uuid		FK → lobbies.id
user_id	text
joined_at	timestamptz
left_at	timestamptz	N	Stamped on leave.
source	text		Default participant_insert.

eligible_games

Column	Type	Null	Notes
igdb_id	integer		PK
slug	text
display_name	text
enabled	boolean	N
cover_url	text	N
created_at	timestamptz	N
updated_at	timestamptz	N
tokens	jsonb		Match tokens for library detection; NOT NULL, default {}.

game_store_ids

Column	Type	Null	Notes
id	uuid		PK
igdb_id	integer		FK → eligible_games.igdb_id (ON DELETE CASCADE).
store	game_store		Enum (steam/epic/gog/microsoft/riot/rockstar/other).
store_id	text

game_runtime_profiles

Column	Type	Null	Notes
id	uuid		PK
game_store_id	uuid		FK → game_store_ids.id
exe_name	text
platform	text	N
notes	text	N
engine	text	N	FK → engine_defaults.engine
settings_override	jsonb	N
settings_enforcement	jsonb

engine_defaults

Column	Type	Null	Notes
engine	text		PK
settings_spec	jsonb
created_at	timestamptz
updated_at	timestamptz

user_game_library

Column	Type	Null	Notes
id	uuid		PK
user_id	uuid
playnite_id	uuid
plugin_id	uuid	N
provider	text
provider_game_id	text	N
name	text
is_installed	boolean	N
is_hidden	boolean	N
is_favorite	boolean	N
playtime_seconds	bigint	N
play_count	bigint	N
last_played_at	timestamptz	N
executables	jsonb	N
genres	jsonb	N
developers	jsonb	N
publishers	jsonb	N
release_date	text	N
cover_image_url	text	N
platform	text	N
install_size_bytes	bigint	N
game_registry_igdb_id	integer	N	FK → eligible_games.igdb_id
synced_at	timestamptz	N
created_at	timestamptz	N
updated_at	timestamptz	N

---

Growth & Network

→ domain page: Growth & Network

shadow_graph

Column	Type	Null	Notes
id	uuid		PK
source_user_id	uuid		FK → playroll_users.user_id
source_steam_id	text
target_steam_id	text
target_name	text	N
target_avatar_url	text	N
target_profile_url	text	N
target_hours	integer	N
target_games_owned	integer	N
target_level	integer	N
target_profile_public	boolean	N
target_tier	text		CHECK whale / regular / casual (default casual).
target_yearly_value	integer		Recruitment-value score.
recruitment_status	text		CHECK none (default) / invited / converted.
invited_at	timestamptz	N
converted_at	timestamptz	N
converted_user_id	uuid	N	FK → playroll_users.user_id (no cascade).
friend_since	bigint	N
created_at	timestamptz
updated_at	timestamptz		UNIQUE edge: (source_user_id, target_steam_id).

invite_codes

Column	Type	Null	Notes
code	text		PK
pool	text		Default phase_0.
max_uses	integer		CHECK > 0 (default 1).
uses	integer		CHECK 0 ≤ uses ≤ max_uses.
active	boolean
notes	text	N
created_by	uuid	N	FK → playroll_users.user_id
created_at	timestamptz
expires_at	timestamptz	N

invite_code_consumptions

Composite PK (code, user_id) — a user consumes a given code at most once.

Column	Type	Null	Notes
code	text		PK (with user_id); FK → invite_codes.code
user_id	uuid		PK (with code); FK → playroll_users.user_id
consumed_at	timestamptz
ip_hash	text	N
user_agent	text	N

redeem_attempt_log

Rate-limiter log for the contributor activation-code flow (redeem-activation-code, EC-227) — not invite codes. app_user_id is an unconstrained uuid (soft reference, no FK).

Column	Type	Null	Notes
id	bigint		PK
app_user_id	uuid		No FK.
ip	text	N
code_hash	text	N
success	boolean		Default false.
at	timestamptz

---

Releases

→ domain page: Releases & Notifications

app_releases_v2

Column	Type	Null	Notes
id	bigint		PK
release_key	text
installer_version	text
core_version	text
ui_version	text
download_url	text
installer_sha256	text	N
release_notes	text	N
metadata	jsonb		Default {}.
created_at	timestamptz
release_track	text		CHECK stable / dev / test (no beta).
release_notes_technical	text	N
release_notes_user_facing	text	N

app_rollouts_v2

Column	Type	Null	Notes
id	uuid		PK
name	text
release_id	bigint		FK → app_releases_v2.id
enabled	boolean
priority	integer
track	text
force_update	boolean
percentage	integer
experiment_key	text	N
ab_bucket	text	N
include_user_ids	uuid[]
exclude_user_ids	uuid[]
include_install_ids	text[]
starts_at	timestamptz
ends_at	timestamptz	N
created_at	timestamptz
updated_at	timestamptz

app_rollout_overrides_v2

Column	Type	Null	Notes
id	uuid		PK
target_type	text
target_id	text
release_id	bigint		FK → app_releases_v2.id
enabled	boolean
force_update	boolean
reason	text	N
expires_at	timestamptz	N
created_at	timestamptz

app_update_resolve_audit_v1

One row per resolve call (25 columns).

Column	Type	Null	Notes
id	bigint		PK
created_at	timestamptz
install_id	text	N
user_id	uuid	N
request_track	text	N
installed_installer_version	text	N
installed_core_version	text	N
installed_ui_version	text	N
installed_track	text	N	Source for user_current_track.
track_order	text[]
assignment_source	text		rollout / override / default
assignment_track	text	N
rollout_id	uuid	N
override_id	uuid	N
bucket	text	N
experiment_key	text	N
target_release_id	bigint	N
target_installer_version	text	N
target_core_version	text	N
target_ui_version	text	N
update_available	boolean
update_required	boolean
force_update	boolean
reason	text	N
resolver_version	text

app_update_resolve_rate_limit

Column	Type	Null	Notes
ip_sha256	bytea		PK (with window_start)
window_start	timestamptz		60s-aligned window.
request_count	integer
last_seen_at	timestamptz

---

Notifications

→ domain page: Releases & Notifications

notification_event

Column	Type	Null	Notes
id	uuid		PK
category	text
type	text
urgency_tier	text
payload	jsonb
cta	jsonb	N
source	text
locale	text
allowed_channels	text[]
audience_filter	jsonb		Resolved by resolve_audience().
recipient_count	integer
created_at	timestamptz
expires_at	timestamptz	N

notification_deliveries

Column	Type	Null	Notes
id	uuid		PK
event_id	uuid		FK → notification_event.id
user_id	uuid
channel	text
status	text
error_code	text	N
queued_at	timestamptz
sent_at	timestamptz	N

notification_state

Column	Type	Null	Notes
event_id	uuid		FK → notification_event.id
user_id	uuid
read_at	timestamptz	N
dismissed_at	timestamptz	N
acted_at	timestamptz	N
created_at	timestamptz

notification_groups

Column	Type	Null	Notes
id	uuid		PK
slug	text
name	text
description	text	N
created_by	uuid	N
created_at	timestamptz

notification_group_members

Column	Type	Null	Notes
group_id	uuid		FK → notification_groups.id
user_id	uuid
added_at	timestamptz
added_by	uuid	N

notification_user_prefs

Column	Type	Null	Notes
user_id	uuid		PK
enable_payment_and_validation	boolean
enable_app_update_and_terms	boolean
enable_catalog_and_announcements	boolean
enable_sound	boolean
enable_email	boolean
updated_at	timestamptz

---

Views

Eleven views project the tables above for specific consumers. They are security_invoker where they expose row-scoped data, so RLS on the underlying tables still governs visibility.

View	Backed by	Purpose
playroll_users_view	playroll_users + auth.users	Canonical user read projection (profile + auth email).
investor_daily_recording_kpis_vc_view	recording_sessions	VC/investor-safe daily KPIs from canonical duration. No PII.
daily_upload_stats	s3_uploads	Daily upload volumes.
upload_summary_by_user	s3_uploads + playroll_users	Per-user upload summary, identity-first.
player_recording_sessions	recording_sessions	Per-player session listing.
session_completion_status	recording_sessions	Completion/terminal-state rollup per session.
complete_triplets	s3_uploads	Legacy — requires mp4 + ogg + csv (no JSON term). OGG removed in EC-173 → matches only historical rows, not the modern validated set.
support_recording_sessions	recording_sessions + s3_uploads	Support/tester session view (canonical duration + file evidence).
support_recording_files	s3_uploads	Support file-level view (who uploaded each file, keys, URLs, status).
support_player_upload_summary	recording_sessions + s3_uploads	Support player lookup summary.
user_current_track	app_update_resolve_audit_v1	Per-user current release track; feeds resolve_audience() app_track filter.

The dashboards that consume these views are documented under Dashboards → Supabase Views and the Database → Monitoring Views page.

---

How this page is regenerated

This reference mirrors the live schema. To refresh it after a migration, the shape comes from three queries against the Playroll Supabase project (information_schema / pg_catalog): the table+column list, the foreign-key list, and the enum list. Re-run those, diff against the tables above, and update the affected section in the same change that ships the migration — per the workspace rule that playroll-docs is kept in sync with schema changes.