Riferimento completo delle tabelle

Lo schema public completo — ogni colonna di tutte le 45 tabelle, le 11 viste e l'unico enum. Le pagine di dominio spiegano il perché; questa pagina è il cosa. Usala come strumento di consultazione.

Notazione

PK = chiave primaria · FK → = chiave esterna verso table.column · N nella colonna Null = la colonna è nullable. I tipi sono i tipi Postgres reali. Questa pagina è generata dal database e riflette lo schema così come distribuito; quando una migrazione modifica una tabella, rigenera la sezione interessata.

La row-level security è abilitata su tutte le 45 tabelle

Il fatto che una colonna sia elencata qui non significa che un client browser possa leggerla. Molte tabelle sono service-role-only / deny-all (raggiungibili solo tramite edge function o la service key) — ad esempio crm_user_roles, player_roles, steam_id_allowlist, device_auth_requests, upload_credentials, tutte e quattro le tabelle growth, lobby_join_events, permissions/role_permissions. Le pagine di dominio annotano la postura RLS per ciascuna area; tratta le relazioni qui come logiche, non come una concessione di lettura.

Enum

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

---

Identità e accesso

→ pagina di dominio: Identità e accesso

playroll_users

L'hub. Indicizzata su user_id (UUID auth di Supabase).

Colonna	Tipo	Null	Note
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		Tier corrente del programma (valore, non 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	Arricchimento Steam ↓
steam_games_owned	integer	N
steam_level	integer	N
steam_profile_public	boolean	N
steam_account_created	bigint	N	Timestamp Unix
steam_account_age_days	integer	N	Calcolato da trigger (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		Aggregato da shadow_graph.
network_whale_count	integer
country	text	N	Attribuzione ↓
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

Colonna	Tipo	Null	Note
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

Colonna	Tipo	Null	Note
steam_id	text		PK
user_id	uuid		FK → auth.users.id; UNIQUE. Mantenuta da auth_user_steam_lookup_sync_trg.
created_at	timestamptz
updated_at	timestamptz

steam_openid_nonces

Colonna	Tipo	Null	Note
nonce	text		PK
seen_at	timestamptz

player_role_catalog

Colonna	Tipo	Null	Note
role	text		PK
rank	integer		Più alto = più accesso.
description	text	N
created_at	timestamptz

player_roles

Colonna	Tipo	Null	Note
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

RBAC dello staff, indicizzata per email.

Colonna	Tipo	Null	Note
email	text		PK
role	text		CHECK admin / cm / vc / sales. Collegata per valore a role_permissions.role.
granted_at	timestamptz
granted_by	text	N
notes	text	N

permissions

Colonna	Tipo	Null	Note
id	integer		PK
resource	text
action	text		CHECK read / write.
description	text	N
created_at	timestamptz

role_permissions

Colonna	Tipo	Null	Note
role	text
permission_id	integer		FK → permissions.id
granted_by	text	N
granted_at	timestamptz

device_auth_requests

Legacy / superata — 0 righe, RLS deny-all, sostituita dal flusso di redirect steam-login.

Colonna	Tipo	Null	Note
device_code	text		PK
status	text		Default pending.
access_token	text	N
refresh_token	text	N
user_id	uuid	N	Nessuna FK.
user_profile	jsonb	N
created_at	timestamptz
completed_at	timestamptz	N
expires_at	timestamptz		Default now() + 10 min.

upload_credentials

Legacy / inutilizzata — 0 righe, nessun writer, RLS deny-all (solo service-key).

Colonna	Tipo	Null	Note
mac_hash	text		PK
user_id	text		UNIQUE (text, non una FK uuid verso playroll_users).
created_at	timestamptz
last_used_at	timestamptz	N
is_active	boolean

playroll_user_consents

Log dei consensi append-only (nessuna colonna revoked; le revoche sono nuove righe).

Colonna	Tipo	Null	Note
id	uuid		PK
user_id	uuid		FK → auth.users.id (non 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'} segna una revoca.
ip_address	inet	N
user_agent	text	N

contributor_status_projection

Proiezione lato app scritta da redeem-activation-code. Nessun PII. (Il portale possiede la tabella sorgente contributor_app_links.)

Colonna	Tipo	Null	Note
app_user_id	uuid		PK, FK → auth.users.id (ON DELETE CASCADE).
contributor_id	uuid
link_status	text		CHECK verified (unico valore).
source_link_id	uuid
steam_id	text	N
linked_at	timestamptz
source_updated_at	timestamptz
last_synced_at	timestamptz

---

Spina dorsale delle registrazioni

→ pagina di dominio: Spina dorsale delle registrazioni

recording_sessions

Indicizzata su un session_id di tipo text (rec-<epoch-ms>).

Colonna	Tipo	Null	Note
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	Mantenuta da trigger.
detected_resolution	jsonb	N
game_slug	text	N
recording_started_at	timestamptz	N
recording_ended_at	timestamptz	N
duration_seconds	bigint	N	Orologio canonico della registrazione. CHECK ≥ 0.
required_artifact_mode	text		CHECK mp4_csv_json (default) / mp4_csv.
uploadability_status	text	N	CHECK uploadable / blocked / NULL.
block_reason	text	N
lobby_id	uuid	N	Per valore — nessuna FK (il binding alla lobby è imposto da 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		Impostata a true a fine sessione se e solo se esiste _mic.aac (EC-407).
transcript_skipped	boolean		Scrivibile dal client tramite GRANT con scope + RLS (EC-409).
dismissed	boolean		"Nascondi dall'Activity" scrivibile dal client (EC-411).

s3_uploads

Una riga per file caricato. Si collega a una sessione per valore su session_id.

Colonna	Tipo	Null	Note
id	uuid		PK
s3_key	text
s3_bucket	text		Default playroll-captures.
user_id	text
upload_year	integer		Chiave di partizione
upload_month	integer		CHECK 1–12.
upload_day	integer		CHECK 1–31.
session_id	text		Si unisce a recording_sessions.session_id (valore).
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	Verificata dal trigger s3_uploads_enforce_lobby_game_match (solo per i tag di lobby).
artifact_kind	text	N	Nessun CHECK; derivata dal suffisso: video/input/meta/transcript; NULL = non classificato (EC-289).

device_hw_snapshots

Chiave di deduplicazione (user_id, hw_fingerprint).

Colonna	Tipo	Null	Note
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		Blob HW completo.
source	text		ui_login (edge fn di login, predominante) / core_meta_json (Lambda di upload); default core_meta_json.

pipeline_sessions

PK composita (session_id, environment) — una riga per sessione per ambiente.

Colonna	Tipo	Null	Note
session_id	text		PK (con environment)
environment	text		PK (con 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

Colonna	Tipo	Null	Note
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

Colonna	Tipo	Null	Note
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

Colonna	Tipo	Null	Note
process_name	text		FK → nvenc_known_contenders.process_name
user_id	text
first_seen	timestamptz
last_seen	timestamptz

bug_reports

Colonna	Tipo	Null	Note
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 lunghezza 1–200.
description	text		CHECK lunghezza 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	Compilata automaticamente dal trigger bug_reports_to_linear (AFTER INSERT).
updated_at	timestamptz
steam_username	text	N
connection	jsonb	N
electron_runtime	jsonb	N

---

Lobby e catalogo

→ pagina di dominio: Lobby e catalogo

lobbies

Colonna	Tipo	Null	Note
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

PK composita (lobby_id, user_id) — membership corrente.

Colonna	Tipo	Null	Note
lobby_id	uuid		PK (con user_id); FK → lobbies.id
user_id	text		PK (con lobby_id)
joined_at	timestamptz
session_id	text	N	Si unisce a recording_sessions.
server_recording_started_at	timestamptz	N

lobby_join_events

Storia prevalentemente append. INSERT al join; il leave aggiorna (UPDATE) left_at (nessuna nuova riga). Pilotata da trigger su lobby_participants.

Colonna	Tipo	Null	Note
id	bigint		PK
lobby_id	uuid		FK → lobbies.id
user_id	text
joined_at	timestamptz
left_at	timestamptz	N	Marcata al leave.
source	text		Default participant_insert.

eligible_games

Colonna	Tipo	Null	Note
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		Token di match per il rilevamento della libreria; NOT NULL, default {}.

game_store_ids

Colonna	Tipo	Null	Note
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

Colonna	Tipo	Null	Note
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

Colonna	Tipo	Null	Note
engine	text		PK
settings_spec	jsonb
created_at	timestamptz
updated_at	timestamptz

user_game_library

Colonna	Tipo	Null	Note
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 e network

→ pagina di dominio: Growth e network

shadow_graph

Colonna	Tipo	Null	Note
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		Punteggio di valore di reclutamento.
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 (nessun cascade).
friend_since	bigint	N
created_at	timestamptz
updated_at	timestamptz		Edge UNIQUE: (source_user_id, target_steam_id).

invite_codes

Colonna	Tipo	Null	Note
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

PK composita (code, user_id) — un utente consuma un dato codice al massimo una volta.

Colonna	Tipo	Null	Note
code	text		PK (con user_id); FK → invite_codes.code
user_id	uuid		PK (con code); FK → playroll_users.user_id
consumed_at	timestamptz
ip_hash	text	N
user_agent	text	N

redeem_attempt_log

Log del rate-limiter per il flusso di activation-code del contributor (redeem-activation-code, EC-227) — non i codici invito. app_user_id è un uuid senza vincoli (riferimento soft, nessuna FK).

Colonna	Tipo	Null	Note
id	bigint		PK
app_user_id	uuid		Nessuna FK.
ip	text	N
code_hash	text	N
success	boolean		Default false.
at	timestamptz

---

Release

→ pagina di dominio: Release e notifiche

app_releases_v2

Colonna	Tipo	Null	Note
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 (nessun beta).
release_notes_technical	text	N
release_notes_user_facing	text	N

app_rollouts_v2

Colonna	Tipo	Null	Note
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

Colonna	Tipo	Null	Note
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

Una riga per chiamata di resolve (25 colonne).

Colonna	Tipo	Null	Note
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	Sorgente per 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

Colonna	Tipo	Null	Note
ip_sha256	bytea		PK (con window_start)
window_start	timestamptz		Finestra allineata a 60s.
request_count	integer
last_seen_at	timestamptz

---

Notifiche

→ pagina di dominio: Release e notifiche

notification_event

Colonna	Tipo	Null	Note
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		Risolto da resolve_audience().
recipient_count	integer
created_at	timestamptz
expires_at	timestamptz	N

notification_deliveries

Colonna	Tipo	Null	Note
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

Colonna	Tipo	Null	Note
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

Colonna	Tipo	Null	Note
id	uuid		PK
slug	text
name	text
description	text	N
created_by	uuid	N
created_at	timestamptz

notification_group_members

Colonna	Tipo	Null	Note
group_id	uuid		FK → notification_groups.id
user_id	uuid
added_at	timestamptz
added_by	uuid	N

notification_user_prefs

Colonna	Tipo	Null	Note
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

---

Viste

Undici viste proiettano le tabelle qui sopra per consumatori specifici. Sono security_invoker dove espongono dati con scope per riga, quindi la RLS sulle tabelle sottostanti governa comunque la visibilità.

Vista	Basata su	Scopo
playroll_users_view	playroll_users + auth.users	Proiezione di lettura canonica dell'utente (profilo + email auth).
investor_daily_recording_kpis_vc_view	recording_sessions	KPI giornalieri VC/investor-safe dalla durata canonica. Nessun PII.
daily_upload_stats	s3_uploads	Volumi di upload giornalieri.
upload_summary_by_user	s3_uploads + playroll_users	Riepilogo upload per utente, identity-first.
player_recording_sessions	recording_sessions	Elenco delle sessioni per giocatore.
session_completion_status	recording_sessions	Rollup di completamento/stato terminale per sessione.
complete_triplets	s3_uploads	Legacy — richiede mp4 + ogg + csv (nessun termine JSON). OGG rimosso in EC-173 → corrisponde solo a righe storiche, non all'insieme moderno validato.
support_recording_sessions	recording_sessions + s3_uploads	Vista delle sessioni per support/tester (durata canonica + evidenze dei file).
support_recording_files	s3_uploads	Vista a livello di file per il support (chi ha caricato ogni file, chiavi, URL, status).
support_player_upload_summary	recording_sessions + s3_uploads	Riepilogo di lookup del giocatore per il support.
user_current_track	app_update_resolve_audit_v1	Track di release corrente per utente; alimenta il filtro app_track di resolve_audience().

Le dashboard che consumano queste viste sono documentate in Dashboard → Viste Supabase e nella pagina Database → Viste di monitoraggio.

---

Come viene rigenerata questa pagina

Questo riferimento rispecchia lo schema reale. Per aggiornarlo dopo una migrazione, la struttura proviene da tre query contro il progetto Playroll Supabase (information_schema / pg_catalog): l'elenco tabelle+colonne, l'elenco delle chiavi esterne e l'elenco degli enum. Riesegui quelle query, confronta con le tabelle qui sopra e aggiorna la sezione interessata nello stesso change che rilascia la migrazione — secondo la regola del workspace per cui playroll-docs è mantenuto in sync con le modifiche allo schema.