Skip to content
play-api Contributor Docs

Schema Reference

Entity relationships

Section titled “Entity relationships”
erDiagram
    admin_actions
    bot_ratings
    bot_webhook_setups
    bot_webhook_stats
    bot_webhooks
    bots
    client_reports
    game_archive
    game_results
    games
    nickname_history
    outbox
    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 : ""
    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. Thirteen 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, 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, nickname_history and 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.

Tables

Section titled “Tables”

admin_actions

Section titled “admin_actions”
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)

bot_ratings

Section titled “bot_ratings”
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)

bot_webhook_setups

Section titled “bot_webhook_setups”
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)

bot_webhook_stats

Section titled “bot_webhook_stats”
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)

bot_webhooks

Section titled “bot_webhooks”
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)

bots

Section titled “bots”
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)

client_reports

Section titled “client_reports”
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)

game_archive

Section titled “game_archive”
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)

game_results

Section titled “game_results”
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 — —

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_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_ladder_idx — CREATE INDEX game_results_ladder_idx ON public.game_results USING btree (ladder) WHERE ladder
  • 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_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)

games

Section titled “games”
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))

nickname_history

Section titled “nickname_history”
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)

outbox

Section titled “outbox”
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)

released_nicknames

Section titled “released_nicknames”
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)

rematch_commands

Section titled “rematch_commands”
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)

rematch_sessions

Section titled “rematch_sessions”
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)

rematch_successors

Section titled “rematch_successors”
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)

showcase_claims

Section titled “showcase_claims”
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)

showcase_table

Section titled “showcase_table”
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)
Section titled “user_guest_links”
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)

user_identities

Section titled “user_identities”
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)

user_ratings

Section titled “user_ratings”
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)

user_training_ratings

Section titled “user_training_ratings”
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() —

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)

users

Section titled “users”
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)

webhook_admin_authority_generations

Section titled “webhook_admin_authority_generations”
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)

webhook_verification_budgets

Section titled “webhook_verification_budgets”
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)