Skip to content

Schema Reference

erDiagram
    admin_actions
    bot_ratings
    bot_strength_snapshots
    bot_webhook_setups
    bot_webhook_stats
    bot_webhooks
    bots
    client_reports
    game_archive
    game_results
    games
    leaderboard_lifetime_counts
    nickname_history
    outbox
    rating_epoch_control
    rating_epoch_counts
    rating_epoch_runs
    released_nicknames
    rematch_commands
    rematch_sessions
    rematch_successors
    showcase_claims
    showcase_table
    user_guest_links
    user_identities
    user_ratings
    user_training_ratings
    users
    webhook_admin_authority_generations
    webhook_verification_budgets
    bots ||--o{ bot_ratings : ""
    bots ||--o{ bot_webhook_setups : ""
    bots ||--o{ bot_webhook_stats : ""
    bots ||--o| bot_webhooks : ""
    games ||--o| outbox : ""
    rating_epoch_runs ||--o{ rating_epoch_control : ""
    rematch_sessions ||--o{ rematch_commands : ""
    rematch_sessions ||--o| rematch_successors : ""
    rematch_successors ||--o| rematch_sessions : ""
    users ||--o{ user_guest_links : ""
    users ||--o{ user_identities : ""
    users ||--o{ user_ratings : ""
    users ||--o{ user_training_ratings : ""

Only foreign keys appear as edges. Seventeen tables carry no foreign key on purpose — admin_actions is an audit log: it must keep naming an admin who has since deleted their account, on a bot whose row may be long gone, bot_strength_snapshots holds version-pinned engine strength snapshots and benchmark ratings independently of dynamic bot registrations, bots is the root of the bot identity graph; tokens, incarnation IDs, and webhook revisions are scoped to the bot row directly, client_reports holds browser-submitted reports for games that never had a games row on this server (kept separate from authoritative game data by design), game_archive and game_results must outlive the snapshots they describe, games holds active game state and is unlinked to allow purging ended games without cascading deletes across result archives, leaderboard_lifetime_counts keeps lifetime records by retained result identity, independently of account deletion and bot re-registration; result triggers maintain these counters transactionally, nickname_history must outlive the account a rename describes just as readily as the one it never touched — a foreign key to users would cascade away the audit trail and the hold on exactly the accounts whose history or vacated name matters most, an account that renamed and then vanished, rating_epoch_counts uses public-history participant identities for active epoch baselines; lifecycle triggers remove deleted participants without foreign keys into retained history, rating_epoch_runs preserves immutable migration plans and rollback evidence independently of participant lifecycle changes, released_nicknames must outlive the account a rename describes just as readily as the one it never touched — a foreign key to users would cascade away the audit trail and the hold on exactly the accounts whose history or vacated name matters most, an account that renamed and then vanished, showcase_claims holds short-lived rate-limiting claims that outlive or precede individual games, showcase_table is a singleton table tracking showcase table state without external entity references, users is the root of the account graph the other user tables reference, webhook_admin_authority_generations tracks global authority heartbeat logs independent of individual bot rows — and webhook_verification_budgets tracks rate-limiting verification budgets keyed by actor or IP, independent of persistent entity life cycles.

Column Type Null Default Key
id bigint no nextval('admin_actions_id_seq'::regclass) PK
admin_user_id uuid yes — —
team text no — —
name text no — —
action text no — —
detail text yes — —
created_at timestamp with time zone no now() —
actor_kind text no 'admin'::text —
actor_id text yes — —
request_id text yes — —
before_revision uuid yes — —
after_revision uuid yes — —
before_registration_id uuid yes — —
after_registration_id uuid yes — —
bot_incarnation_id uuid yes — —
metadata jsonb no '{}'::jsonb —

Check constraints:

  • CHECK ((actor_kind = ANY (ARRAY['owner'::text, 'admin'::text, 'bot'::text, 'system'::text])))

Indexes:

  • admin_actions_bot_idx — CREATE INDEX admin_actions_bot_idx ON public.admin_actions USING btree (team, name, created_at)
  • admin_actions_pkey — CREATE UNIQUE INDEX admin_actions_pkey ON public.admin_actions USING btree (id)
  • admin_actions_request_idx — CREATE INDEX admin_actions_request_idx ON public.admin_actions USING btree (request_id) WHERE (request_id IS NOT NULL)
Column Type Null Default Key
team text no — FK → bots(team, name), PK
name text no — FK → bots(team, name), PK
category text no — PK
rating double precision no 1500 —
rd double precision no 350 —
vol double precision no 0.06 —

Check constraints:

  • CHECK ((category = ANY (ARRAY['bullet'::text, 'blitz'::text, 'rapid'::text])))

Indexes:

  • bot_ratings_pkey — CREATE UNIQUE INDEX bot_ratings_pkey ON public.bot_ratings USING btree (team, name, category)
Column Type Null Default Key
id bigint no nextval('bot_strength_snapshots_id_seq'::regclass) PK
category text no — —
epoch integer no — —
anchor_set_version text no — —
cutoff timestamp with time zone no — —
computed_at timestamp with time zone no now() —
is_current boolean no false —
payload jsonb no — —

Check constraints:

  • CHECK ((category = ANY (ARRAY['bullet'::text, 'blitz'::text, 'rapid'::text])))
  • CHECK ((epoch >= 1))

Indexes:

  • bot_strength_snapshots_current_idx — CREATE UNIQUE INDEX bot_strength_snapshots_current_idx ON public.bot_strength_snapshots USING btree (category, epoch) WHERE (is_current = true)
  • bot_strength_snapshots_cutoff_idx — CREATE UNIQUE INDEX bot_strength_snapshots_cutoff_idx ON public.bot_strength_snapshots USING btree (category, epoch, cutoff)
  • bot_strength_snapshots_lookup_idx — CREATE INDEX bot_strength_snapshots_lookup_idx ON public.bot_strength_snapshots USING btree (category, epoch, computed_at DESC)
  • bot_strength_snapshots_pkey — CREATE UNIQUE INDEX bot_strength_snapshots_pkey ON public.bot_strength_snapshots USING btree (id)
Column Type Null Default Key
setup_id uuid no — PK
team text no — FK → bots(team, name, incarnation_id)
name text no — FK → bots(team, name, incarnation_id)
bot_incarnation_id uuid no — FK → bots(team, name, incarnation_id)
kind text yes — —
actor_kind text yes — —
actor_id text yes — —
authority_generation text yes — —
activation_revision uuid yes — —
candidate_url text yes — —
candidate_secret text yes — —
candidate_capabilities text[] yes — —
created_at timestamp with time zone yes — —
expires_at timestamp with time zone yes — —
activation_attempts integer yes 0 —
lease_id uuid yes — —
lease_expires_at timestamp with time zone yes — —
status text no 'pending'::text —
terminated_at timestamp with time zone yes — —

Check constraints:

  • CHECK (((actor_kind IS NULL) OR (actor_kind = ANY (ARRAY['owner'::text, 'admin'::text]))))
  • CHECK (((activation_attempts IS NULL) OR ((activation_attempts >= 0) AND (activation_attempts <= 5))))
  • CHECK (((status <> 'pending'::text) OR ((kind = 'create'::text) AND (candidate_capabilities IS NOT NULL)) OR ((kind = ANY (ARRAY['replaceUrl'::text, 'rotateSecret'::text])) AND (candidate_capabilities IS NULL))))
  • CHECK (((candidate_capabilities IS NULL) OR (candidate_capabilities = ARRAY[]::text[]) OR (candidate_capabilities = ARRAY['draws'::text])))
  • CHECK (((status <> 'pending'::text) OR (expires_at > created_at)))
  • CHECK (((kind IS NULL) OR (kind = ANY (ARRAY['create'::text, 'replaceUrl'::text, 'rotateSecret'::text]))))
  • CHECK (((lease_id IS NULL) = (lease_expires_at IS NULL)))
  • CHECK (((lease_id IS NULL) OR (activation_attempts > 0)))
  • CHECK ((((status = 'pending'::text) AND (kind IS NOT NULL) AND (actor_kind IS NOT NULL) AND (actor_id IS NOT NULL) AND (authority_generation IS NOT NULL) AND (activation_revision IS NOT NULL) AND (candidate_url IS NOT NULL) AND (candidate_secret IS NOT NULL) AND (created_at IS NOT NULL) AND (expires_at IS NOT NULL) AND (activation_attempts IS NOT NULL) AND (terminated_at IS NULL)) OR ((status <> 'pending'::text) AND (kind IS NULL) AND (actor_kind IS NULL) AND (actor_id IS NULL) AND (authority_generation IS NULL) AND (activation_revision IS NULL) AND (candidate_url IS NULL) AND (candidate_secret IS NULL) AND (candidate_capabilities IS NULL) AND (created_at IS NULL) AND (expires_at IS NULL) AND (activation_attempts IS NULL) AND (lease_id IS NULL) AND (lease_expires_at IS NULL) AND (terminated_at IS NOT NULL))))
  • CHECK ((status = ANY (ARRAY['pending'::text, 'activated'::text, 'cancelled'::text, 'expired'::text, 'invalidated'::text, 'attempts_exhausted'::text])))

Indexes:

  • bot_webhook_setups_one_pending_idx — CREATE UNIQUE INDEX bot_webhook_setups_one_pending_idx ON public.bot_webhook_setups USING btree (team, name) WHERE (status = 'pending'::text)
  • bot_webhook_setups_pending_expiry_idx — CREATE INDEX bot_webhook_setups_pending_expiry_idx ON public.bot_webhook_setups USING btree (expires_at) WHERE (status = 'pending'::text)
  • bot_webhook_setups_pkey — CREATE UNIQUE INDEX bot_webhook_setups_pkey ON public.bot_webhook_setups USING btree (setup_id)
  • bot_webhook_setups_tombstone_expiry_idx — CREATE INDEX bot_webhook_setups_tombstone_expiry_idx ON public.bot_webhook_setups USING btree (terminated_at) WHERE (status <> 'pending'::text)
Column Type Null Default Key
team text no — FK → bots(team, name), PK
name text no — FK → bots(team, name), PK
hour timestamp with time zone no — PK
outcome text no — PK
latency_bucket smallint no — PK
count bigint no 0 —

Indexes:

  • bot_webhook_stats_pkey — CREATE UNIQUE INDEX bot_webhook_stats_pkey ON public.bot_webhook_stats USING btree (team, name, hour, outcome, latency_bucket)
  • bot_webhook_stats_recent_idx — CREATE INDEX bot_webhook_stats_recent_idx ON public.bot_webhook_stats USING btree (team, name, hour)
Column Type Null Default Key
team text no — FK → bots(team, name), PK
name text no — FK → bots(team, name), PK
url text no — —
secret text no — —
verified_at timestamp with time zone no — —
created_at timestamp with time zone no now() —
last_failure_at timestamp with time zone yes — —
last_failure_reason text yes — —
capabilities text[] no '{}'::text[] —
registration_id uuid no gen_random_uuid() —

Check constraints:

  • CHECK (((capabilities = ARRAY[]::text[]) OR (capabilities = ARRAY['draws'::text])))

Indexes:

  • bot_webhooks_pkey — CREATE UNIQUE INDEX bot_webhooks_pkey ON public.bot_webhooks USING btree (team, name)
Column Type Null Default Key
team text no — PK, unique
name text no — PK, unique
token_hash text no — unique
created_at timestamp with time zone no now() —
rotated_at timestamp with time zone yes — —
on_ladder boolean no false —
owner_external_id text yes — —
open_to_humans boolean no false —
description text yes — —
max_concurrent_games integer no 1 —
rated_for_humans boolean no false —
incarnation_id uuid no gen_random_uuid() unique
webhook_revision uuid no gen_random_uuid() —
ownership_generation bigint no 0 —

Check constraints:

  • CHECK (((max_concurrent_games >= 1) AND (max_concurrent_games <= 32)))

Indexes:

  • bots_owner_idx — CREATE INDEX bots_owner_idx ON public.bots USING btree (owner_external_id) WHERE (owner_external_id IS NOT NULL)
  • bots_pkey — CREATE UNIQUE INDEX bots_pkey ON public.bots USING btree (team, name)
  • bots_token_hash_key — CREATE UNIQUE INDEX bots_token_hash_key ON public.bots USING btree (token_hash)
  • bots_webhook_incarnation_unique — CREATE UNIQUE INDEX bots_webhook_incarnation_unique ON public.bots USING btree (team, name, incarnation_id)
Column Type Null Default Key
report_id uuid no — PK
payload jsonb no — —
attempts integer no 0 —
next_attempt_at timestamp with time zone no now() —
failed_permanently boolean no false —
last_error text yes — —
created_at timestamp with time zone no now() —
delivered_at timestamp with time zone yes — —

Indexes:

  • client_reports_due_idx — CREATE INDEX client_reports_due_idx ON public.client_reports USING btree (next_attempt_at) WHERE ((delivered_at IS NULL) AND (NOT failed_permanently))
  • client_reports_pkey — CREATE UNIQUE INDEX client_reports_pkey ON public.client_reports USING btree (report_id)
Column Type Null Default Key
game_id uuid no — PK
payload jsonb no — —
finished_at timestamp with time zone no now() —
origin text no 'legacy'::text —
sporting_eligible boolean no true —

Check constraints:

  • CHECK ((origin = ANY (ARRAY['showcase'::text, 'ladder'::text, 'catalog'::text, 'lobby'::text, 'direct'::text, 'legacy'::text])))

Indexes:

  • game_archive_origin_finished_idx — CREATE INDEX game_archive_origin_finished_idx ON public.game_archive USING btree (origin, finished_at DESC)
  • game_archive_pkey — CREATE UNIQUE INDEX game_archive_pkey ON public.game_archive USING btree (game_id)
Column Type Null Default Key
game_id uuid no — PK
white_external_id text no — —
black_external_id text no — —
result smallint yes — —
termination text no — —
rated boolean no — —
time_control text no — —
server_seed text no — —
pairing_id uuid yes — —
finished_at timestamp with time zone no now() —
rating_applied_at timestamp with time zone yes — —
ladder boolean no false —
white_rating_before double precision yes — —
white_rating_after double precision yes — —
black_rating_before double precision yes — —
black_rating_after double precision yes — —
category text yes — —
origin text no 'legacy'::text —
white_kind text yes — —
black_kind text yes — —
rated_requested boolean yes — —
rating_domain text no 'legacy'::text —
rating_policy_version smallint no 0 —
rating_outcome text no 'legacy'::text —
rating_skip_reason text yes — —
training_reference_source text yes — —
training_reference_anchor_set text yes — —
training_reference_anchor_epoch integer yes — —
training_reference_rating double precision yes — —
training_reference_rd double precision yes — —
training_reference_vol double precision yes — —
white_rd_before double precision yes — —
white_rd_after double precision yes — —
black_rd_before double precision yes — —
black_rd_after double precision yes — —
rating_epoch integer yes — —

Check constraints:

  • CHECK (((black_kind IS NULL) OR (black_kind = ANY (ARRAY['human'::text, 'bot'::text, 'guest'::text]))))
  • CHECK ((origin = ANY (ARRAY['showcase'::text, 'ladder'::text, 'catalog'::text, 'lobby'::text, 'direct'::text, 'legacy'::text])))
  • CHECK ((rating_domain = ANY (ARRAY['competitive'::text, 'training'::text, 'casual'::text, 'legacy'::text])))
  • CHECK ((rating_epoch > 0))
  • CHECK ((rating_outcome = ANY (ARRAY['pending'::text, 'applied'::text, 'skipped'::text, 'casual'::text, 'legacy'::text])))
  • CHECK (((rating_outcome = 'skipped'::text) = (rating_skip_reason IS NOT NULL)))
  • CHECK (((training_reference_source IS NULL) OR (rating_domain = 'training'::text)))
  • CHECK ( CASE training_reference_source WHEN 'anchor'::text THEN ((training_reference_anchor_set IS NOT NULL) AND (training_reference_anchor_epoch IS NOT NULL) AND (training_reference_rating IS NOT NULL) AND (training_reference_rd IS NOT NULL) AND (training_reference_vol IS NOT NULL)) WHEN 'bot_rating'::text THEN ((training_reference_anchor_set IS NULL) AND (training_reference_anchor_epoch IS NULL) AND (training_reference_rating IS NOT NULL) AND (training_reference_rd IS NOT NULL) AND (training_reference_vol IS NOT NULL)) ELSE ((training_reference_anchor_set IS NULL) AND (training_reference_anchor_epoch IS NULL) AND (training_reference_rating IS NULL) AND (training_reference_rd IS NULL) AND (training_reference_vol IS NULL)) END)
  • CHECK ((training_reference_source = ANY (ARRAY['anchor'::text, 'bot_rating'::text, 'unavailable'::text])))
  • CHECK (((white_kind IS NULL) OR (white_kind = ANY (ARRAY['human'::text, 'bot'::text, 'guest'::text]))))

Indexes:

  • game_results_black_finished_idx — CREATE INDEX game_results_black_finished_idx ON public.game_results USING btree (black_external_id, finished_at DESC)
  • game_results_epoch_computed_idx — CREATE INDEX game_results_epoch_computed_idx ON public.game_results USING btree (rating_epoch, category, rating_applied_at DESC) WHERE ((rating_outcome = 'applied'::text) AND (rating_domain <> 'training'::text))
  • game_results_epoch_cutoff_idx — CREATE INDEX game_results_epoch_cutoff_idx ON public.game_results USING btree (rating_epoch, category, finished_at DESC) WHERE (rating_epoch IS NOT NULL)
  • game_results_ladder_idx — CREATE INDEX game_results_ladder_idx ON public.game_results USING btree (ladder) WHERE ladder
  • game_results_lifetime_computed_idx — CREATE INDEX game_results_lifetime_computed_idx ON public.game_results USING btree (category, COALESCE(rating_applied_at, finished_at) DESC) WHERE ((rating_outcome = ANY (ARRAY['applied'::text, 'legacy'::text])) AND (rating_domain <> 'training'::text))
  • game_results_lifetime_cutoff_idx — CREATE INDEX game_results_lifetime_cutoff_idx ON public.game_results USING btree (category, finished_at DESC) WHERE ((rating_outcome = ANY (ARRAY['applied'::text, 'legacy'::text])) AND (rating_domain <> 'training'::text))
  • game_results_live_epoch_cutoff_idx — CREATE INDEX game_results_live_epoch_cutoff_idx ON public.game_results USING btree (rating_epoch, category, finished_at DESC) WHERE ((rating_outcome = 'applied'::text) AND (rating_domain <> 'training'::text))
  • game_results_origin_finished_idx — CREATE INDEX game_results_origin_finished_idx ON public.game_results USING btree (origin, finished_at DESC)
  • game_results_outcome_category_idx — CREATE INDEX game_results_outcome_category_idx ON public.game_results USING btree (rating_outcome, category) WHERE (result IS NOT NULL)
  • game_results_pairing_idx — CREATE INDEX game_results_pairing_idx ON public.game_results USING btree (pairing_id) WHERE (pairing_id IS NOT NULL)
  • game_results_pkey — CREATE UNIQUE INDEX game_results_pkey ON public.game_results USING btree (game_id)
  • game_results_rated_category_finished_idx — CREATE INDEX game_results_rated_category_finished_idx ON public.game_results USING btree (category, finished_at) WHERE (rated = true)
  • game_results_rated_finished_idx — CREATE INDEX game_results_rated_finished_idx ON public.game_results USING btree (rated, finished_at)
  • game_results_rating_queue_idx — CREATE INDEX game_results_rating_queue_idx ON public.game_results USING btree (finished_at) WHERE (rated AND (rating_applied_at IS NULL))
  • game_results_training_queue_idx — CREATE INDEX game_results_training_queue_idx ON public.game_results USING btree (finished_at) WHERE ((rating_domain = 'training'::text) AND (rating_applied_at IS NULL))
  • game_results_white_finished_idx — CREATE INDEX game_results_white_finished_idx ON public.game_results USING btree (white_external_id, finished_at DESC)
Column Type Null Default Key
id uuid no — PK
status text no — —
snapshot jsonb no — —
created_at timestamp with time zone no now() —
updated_at timestamp with time zone no now() —
origin text no 'legacy'::text —

Check constraints:

  • CHECK ((origin = ANY (ARRAY['showcase'::text, 'ladder'::text, 'catalog'::text, 'lobby'::text, 'direct'::text, 'legacy'::text])))
  • CHECK ((status = ANY (ARRAY['active'::text, 'ended'::text])))

Indexes:

  • games_active_idx — CREATE INDEX games_active_idx ON public.games USING btree (status) WHERE (status = 'active'::text)
  • games_pkey — CREATE UNIQUE INDEX games_pkey ON public.games USING btree (id)
  • games_showcase_active_idx — CREATE INDEX games_showcase_active_idx ON public.games USING btree (id) WHERE ((origin = 'showcase'::text) AND (status = 'active'::text))
Column Type Null Default Key
participant_external_id text no — PK
category text no — PK
wins bigint no — —
draws bigint no — —
losses bigint no — —

Check constraints:

  • CHECK ((draws >= 0))
  • CHECK ((losses >= 0))
  • CHECK ((wins >= 0))

Indexes:

  • leaderboard_lifetime_counts_pkey — CREATE UNIQUE INDEX leaderboard_lifetime_counts_pkey ON public.leaderboard_lifetime_counts USING btree (participant_external_id, category)
Column Type Null Default Key
id bigint no nextval('nickname_history_id_seq'::regclass) PK
user_id uuid no — —
old_nickname text no — —
new_nickname text no — —
changed_at timestamp with time zone no now() —

Indexes:

  • nickname_history_old_name_idx — CREATE INDEX nickname_history_old_name_idx ON public.nickname_history USING btree (lower(old_nickname))
  • nickname_history_pkey — CREATE UNIQUE INDEX nickname_history_pkey ON public.nickname_history USING btree (id)
  • nickname_history_user_idx — CREATE INDEX nickname_history_user_idx ON public.nickname_history USING btree (user_id, changed_at)
Column Type Null Default Key
game_id uuid no — FK → games(id), PK
payload jsonb no — —
attempts integer no 0 —
next_attempt_at timestamp with time zone no now() —
failed_permanently boolean no false —
last_error text yes — —
created_at timestamp with time zone no now() —
delivered_at timestamp with time zone yes — —

Indexes:

  • outbox_due_idx — CREATE INDEX outbox_due_idx ON public.outbox USING btree (next_attempt_at) WHERE ((delivered_at IS NULL) AND (NOT failed_permanently))
  • outbox_pkey — CREATE UNIQUE INDEX outbox_pkey ON public.outbox USING btree (game_id)
Column Type Null Default Key
singleton boolean no true PK
revision bigint no 0 —
paused boolean no false —
active_epoch integer yes — —
active_run_id uuid yes — FK → rating_epoch_runs(run_id)
changed_at timestamp with time zone no now() —

Check constraints:

  • CHECK ((active_epoch > 0))
  • CHECK (((active_epoch IS NULL) = (active_run_id IS NULL)))
  • CHECK (singleton)

Indexes:

  • rating_epoch_control_pkey — CREATE UNIQUE INDEX rating_epoch_control_pkey ON public.rating_epoch_control USING btree (singleton)
Column Type Null Default Key
participant_external_id text no — PK
category text no — PK
games bigint no — —
wins bigint no — —
draws bigint no — —
losses bigint no — —

Check constraints:

  • CHECK ((category = ANY (ARRAY['bullet'::text, 'blitz'::text, 'rapid'::text])))
  • CHECK ((games = ((wins + draws) + losses)))
  • CHECK ((draws >= 0))
  • CHECK ((games >= 0))
  • CHECK ((losses >= 0))
  • CHECK ((wins >= 0))

Indexes:

  • rating_epoch_counts_pkey — CREATE UNIQUE INDEX rating_epoch_counts_pkey ON public.rating_epoch_counts USING btree (participant_external_id, category)
Column Type Null Default Key
run_id uuid no — PK
plan_sha256 text no — unique
phase text no — —
source_revision bigint no — —
source_state_sha256 text no — —
target_epoch integer no — —
cutoff timestamp with time zone no — —
anchor_set_version text no — —
plan jsonb no — —
before_state jsonb yes — —
before_state_sha256 text yes — —
after_state_sha256 text yes — —
applied_revision bigint yes — —
staged_at timestamp with time zone no now() —
applied_at timestamp with time zone yes — —
rolled_back_at timestamp with time zone yes — —

Check constraints:

  • CHECK ((phase = ANY (ARRAY['staged'::text, 'applied'::text, 'rolled_back'::text])))
  • CHECK ((target_epoch > 0))

Indexes:

  • rating_epoch_runs_pkey — CREATE UNIQUE INDEX rating_epoch_runs_pkey ON public.rating_epoch_runs USING btree (run_id)
  • rating_epoch_runs_plan_sha256_key — CREATE UNIQUE INDEX rating_epoch_runs_plan_sha256_key ON public.rating_epoch_runs USING btree (plan_sha256)
Column Type Null Default Key
nickname_lower text no — —
previous_owner_id uuid no — —
released_at timestamp with time zone no now() —
expires_at timestamp with time zone no — —

Indexes:

  • released_nicknames_lookup_idx — CREATE INDEX released_nicknames_lookup_idx ON public.released_nicknames USING btree (nickname_lower, expires_at)
Column Type Null Default Key
source_game_id uuid no — FK → rematch_sessions(source_game_id), PK
seat text no — PK
request_id uuid no — PK
action text no — —
error_code text yes — —
recorded_at timestamp with time zone no clock_timestamp() —

Check constraints:

  • CHECK ((action = ANY (ARRAY['propose'::text, 'accept'::text, 'decline'::text, 'cancel'::text])))
  • CHECK ((seat = ANY (ARRAY['White'::text, 'Black'::text])))

Indexes:

  • rematch_commands_pkey — CREATE UNIQUE INDEX rematch_commands_pkey ON public.rematch_commands USING btree (source_game_id, seat, request_id)
  • rematch_commands_retention_idx — CREATE INDEX rematch_commands_retention_idx ON public.rematch_commands USING btree (recorded_at)
Column Type Null Default Key
source_game_id uuid no — PK, FK → rematch_successors(source_game_id, game_id)
root_game_id uuid no — —
source jsonb no — —
ended_at timestamp with time zone no — —
phase text no 'available'::text —
version bigint no 0 —
consent_white boolean no false —
consent_black boolean no false —
offered_by text yes — —
deadline_at timestamp with time zone no — —
closed_reason text yes — —
successor_id uuid yes — unique, FK → rematch_successors(source_game_id, game_id)

Check constraints:

  • CHECK (((phase = 'matched'::text) = (successor_id IS NOT NULL)))
  • CHECK (((phase = 'closed'::text) = (closed_reason IS NOT NULL)))
  • CHECK (((successor_id IS NULL) OR ((successor_id <> source_game_id) AND (successor_id <> root_game_id))))
  • CHECK (((phase <> 'available'::text) OR ((NOT consent_white) AND (NOT consent_black) AND (offered_by IS NULL))))
  • CHECK (((phase <> 'offered'::text) OR ((consent_white <> consent_black) AND (offered_by IS NOT NULL) AND (((offered_by = 'White'::text) AND consent_white) OR ((offered_by = 'Black'::text) AND consent_black)))))
  • CHECK (((phase <> ALL (ARRAY['starting'::text, 'matched'::text])) OR (consent_white AND consent_black AND (offered_by IS NOT NULL))))
  • CHECK ((closed_reason = ANY (ARRAY['declined'::text, 'cancelled'::text, 'expired'::text, 'technical_failure'::text, 'restart'::text])))
  • CHECK ((offered_by = ANY (ARRAY['White'::text, 'Black'::text])))
  • CHECK ((phase = ANY (ARRAY['available'::text, 'offered'::text, 'starting'::text, 'matched'::text, 'closed'::text])))
  • CHECK ((version >= 0))

Indexes:

  • rematch_sessions_pending_idx — CREATE INDEX rematch_sessions_pending_idx ON public.rematch_sessions USING btree (source_game_id) WHERE (phase = ANY (ARRAY['offered'::text, 'starting'::text]))
  • rematch_sessions_pkey — CREATE UNIQUE INDEX rematch_sessions_pkey ON public.rematch_sessions USING btree (source_game_id)
  • rematch_sessions_successor_id_key — CREATE UNIQUE INDEX rematch_sessions_successor_id_key ON public.rematch_sessions USING btree (successor_id)
Column Type Null Default Key
game_id uuid no — PK, unique
source_game_id uuid no — FK → rematch_sessions(source_game_id), unique, unique
initial_snapshot jsonb no — —
committed_at timestamp with time zone no — —
join_deadline_at timestamp with time zone no — —
startup_phase text no 'awaiting_joins'::text —
startup_version bigint no 0 —
joined_white boolean no false —
joined_black boolean no false —
activated_at timestamp with time zone yes — —

Check constraints:

  • CHECK ((game_id <> source_game_id))
  • CHECK ((join_deadline_at = (committed_at + '00:00:15'::interval)))
  • CHECK (((startup_phase = 'active'::text) = (activated_at IS NOT NULL)))
  • CHECK (((startup_phase <> 'active'::text) OR (joined_white AND joined_black)))
  • CHECK ((startup_phase = ANY (ARRAY['awaiting_joins'::text, 'active'::text, 'aborted'::text])))
  • CHECK ((startup_version >= 0))

Indexes:

  • rematch_successors_pending_idx — CREATE INDEX rematch_successors_pending_idx ON public.rematch_successors USING btree (game_id) WHERE (startup_phase <> 'active'::text)
  • rematch_successors_pkey — CREATE UNIQUE INDEX rematch_successors_pkey ON public.rematch_successors USING btree (game_id)
  • rematch_successors_source_game_id_game_id_key — CREATE UNIQUE INDEX rematch_successors_source_game_id_game_id_key ON public.rematch_successors USING btree (source_game_id, game_id)
  • rematch_successors_source_game_id_key — CREATE UNIQUE INDEX rematch_successors_source_game_id_key ON public.rematch_successors USING btree (source_game_id)
Column Type Null Default Key
actor_id text no — PK
idempotency_key uuid no — PK
request_hash text no — —
outcome text no — —
game_id uuid yes — —
human_color text yes — —
created_at timestamp with time zone no now() —
expires_at timestamp with time zone no (now() + '24:00:00'::interval) —

Check constraints:

  • CHECK (((outcome <> 'claimed'::text) OR ((game_id IS NOT NULL) AND (human_color IS NOT NULL))))
  • CHECK ((expires_at > created_at))
  • CHECK (((human_color IS NULL) OR (human_color = ANY (ARRAY['white'::text, 'black'::text]))))
  • CHECK ((outcome = ANY (ARRAY['claimed'::text, 'spectating'::text])))

Indexes:

  • showcase_claims_expires_idx — CREATE INDEX showcase_claims_expires_idx ON public.showcase_claims USING btree (expires_at)
  • showcase_claims_pkey — CREATE UNIQUE INDEX showcase_claims_pkey ON public.showcase_claims USING btree (actor_id, idempotency_key)
Column Type Null Default Key
id smallint no 1 PK
next_human_color text no 'white'::text —
current_game_id uuid yes — —
updated_at timestamp with time zone no now() —

Check constraints:

  • CHECK ((next_human_color = ANY (ARRAY['white'::text, 'black'::text])))
  • CHECK ((id = 1))

Indexes:

  • showcase_table_pkey — CREATE UNIQUE INDEX showcase_table_pkey ON public.showcase_table USING btree (id)
Column Type Null Default Key
guest_id uuid no — PK
user_id uuid no — FK → users(id)
linked_at timestamp with time zone no now() —

Indexes:

  • user_guest_links_pkey — CREATE UNIQUE INDEX user_guest_links_pkey ON public.user_guest_links USING btree (guest_id)
  • user_guest_links_user_idx — CREATE INDEX user_guest_links_user_idx ON public.user_guest_links USING btree (user_id)
Column Type Null Default Key
provider text no — PK
subject text no — PK
user_id uuid no — FK → users(id)
email text yes — —
created_at timestamp with time zone no now() —

Indexes:

  • user_identities_pkey — CREATE UNIQUE INDEX user_identities_pkey ON public.user_identities USING btree (provider, subject)
  • user_identities_user_idx — CREATE INDEX user_identities_user_idx ON public.user_identities USING btree (user_id)
Column Type Null Default Key
user_id uuid no — FK → users(id), PK
category text no — PK
rating double precision no 1500 —
rd double precision no 350 —
vol double precision no 0.06 —

Check constraints:

  • CHECK ((category = ANY (ARRAY['bullet'::text, 'blitz'::text, 'rapid'::text])))

Indexes:

  • user_ratings_pkey — CREATE UNIQUE INDEX user_ratings_pkey ON public.user_ratings USING btree (user_id, category)
Column Type Null Default Key
user_id uuid no — FK → users(id), PK
category text no — PK
rating double precision no 1500 —
rd double precision no 350 —
vol double precision no 0.06 —
games integer no 0 —
wins integer no 0 —
draws integer no 0 —
losses integer no 0 —
updated_at timestamp with time zone no now() —
computed_at timestamp with time zone yes — —

Check constraints:

  • CHECK ((category = ANY (ARRAY['bullet'::text, 'blitz'::text, 'rapid'::text])))

Indexes:

  • user_training_ratings_pkey — CREATE UNIQUE INDEX user_training_ratings_pkey ON public.user_training_ratings USING btree (user_id, category)
Column Type Null Default Key
id uuid no — PK
nickname text no — —
created_at timestamp with time zone no now() —
last_login_at timestamp with time zone yes — —
is_active boolean no true —
nickname_changed_at timestamp with time zone yes — —

Indexes:

  • users_nickname_ci_idx — CREATE UNIQUE INDEX users_nickname_ci_idx ON public.users USING btree (lower(nickname))
  • users_pkey — CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)
Column Type Null Default Key
authority_generation text no — PK
heartbeat_at timestamp with time zone no clock_timestamp() —

Check constraints:

  • CHECK ((authority_generation ~ '^[0-9a-f]{64}$'::text))

Indexes:

  • webhook_admin_authority_generations_pkey — CREATE UNIQUE INDEX webhook_admin_authority_generations_pkey ON public.webhook_admin_authority_generations USING btree (authority_generation)
  • webhook_admin_authority_heartbeat_idx — CREATE INDEX webhook_admin_authority_heartbeat_idx ON public.webhook_admin_authority_generations USING btree (heartbeat_at)
Column Type Null Default Key
budget_kind text no — PK
budget_key text no — PK
window_started_at timestamp with time zone no — —
window_expires_at timestamp with time zone no — —
attempts integer no — —

Check constraints:

  • CHECK ((attempts >= 1))
  • CHECK ((budget_kind = ANY (ARRAY['setup_actor_bot'::text, 'activation_actor_bot'::text, 'activation_source_ip'::text])))
  • CHECK ((window_expires_at > window_started_at))

Indexes:

  • webhook_verification_budgets_expiry_idx — CREATE INDEX webhook_verification_budgets_expiry_idx ON public.webhook_verification_budgets USING btree (window_expires_at)
  • webhook_verification_budgets_pkey — CREATE UNIQUE INDEX webhook_verification_budgets_pkey ON public.webhook_verification_budgets USING btree (budget_kind, budget_key)