Add database tables for matchmaking

[seanmturley]

Description

The matchmaking system is going to require 3 tables. Keeping these to the basics for now, they should be composed as follows:

players

This can adapt the existing profiles table (see #22 to understand its current implementation), and should contain the following columns:

• player_id (UUID): Primary key, foreign key (linked to auth.users.id)
• mtga_account_id (string): The user's MTGA account ID e.g. DisplayName#12345
• status (enum): idle, searching, in_match

Additional columns will be added for recording match results, and potentially different rating and results columns for each supported combination of format and match_type. For example, the Glicko-2 system would require at least:

• rating (float): The player's current Glicko-2 rating
• deviation (float): A measure of confidence in the rating
• volatility (float): A measure of the degree of expected fluctuation in rating

Initially, the implementation will not include player ratings.

matchmaking_queue

A dynamic table the records those players currently queuing - once players are matched, they will be removed from this table.

• player_id (UUID): Primary key, foreign key (linked to players)
• created_at (timestamp): Time of request
• is_matched (boolean): Whether or not a match has been found for the player
• opponent_id (UUID): The ID of the opponent the player has been matched with
• is_ready (boolean): Whether or not the player has confirmed their readiness via the "ready check"

Ultimately one matchmaking_queue would exist for each supported combination of format and match_type, but to start with, everything will be restricted to a single queue (Historic Pauper best of 1).

When/if player rating are taken into account during matchmaking, this will also need to include a corresponding column e.g.:

• rating (float): Player's matchmaking rating (taken from the players table, and included here for easy querying)

matches

A dynamic table recording active matches - once matches are complete (and the result recorded), the match would be removed from this table.

• match_id (UUID): Primary key
• player1_id (UUID): Foreign key (linked to players)
• player2_id (UUID): Foreign key (linked to players)
• created_at (timestamp): Time of match creation

Additional columns may be required for the reported result from each player, which (assuming agreement), would be used to calculate a new rating (updated in the players table).

There could be a separate matches table for each supported combination of format and match_type, or this information could just be recorded as additional columns.

[seanmturley]

The players table has been created as a replacement for the similar, pre-existing profiles table (created as part of #22). This initial implementation excludes player rating data.

For reference, here are the SQL snippets that were run in the Supabase SQL Editor (these have also been saved in the Supabase project for easy access later):

-- Creates the enum type for player status
create type public.player_status as enum('idle', 'searching', 'in_match');

-- Creates the public.players table and enables row level security
create table public.players (
  player_id uuid not null references auth.users on delete cascade,
  mtga_account_id text not null unique,
  status public.player_status not null default 'idle',

  primary key (player_id)
);

alter table public.players enable row level security;

-- Inserts a row into public.players
create function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
  insert into public.players (player_id, mtga_account_id, status)
  values (new.id, new.raw_user_meta_data ->> 'mtga_account_id', 'idle');
  return new;
end;
$$;

-- Triggers the function every time a user is created
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

[seanmturley]

The matchmaking_queue table has been created. This initial implementation excludes player rating data.

For reference, here's the SQL snippet that was run in the Supabase SQL Editor (also saved in the Supabase project for easy access later):

-- Creates the public.matchmaking_queue table and enables row level security
create table public.matchmaking_queue (
  player_id uuid not null references public.players on delete cascade,
  created_at timestamp with time zone not null default now(),
  is_matched boolean not null default false,
  opponent_id uuid references public.players on delete cascade,
  is_ready boolean not null default false,

  primary key (player_id)
);

alter table public.matchmaking_queue enable row level security;

[seanmturley]

The matches table has been created.

For reference, here's the SQL snippet that was run in the Supabase SQL Editor (also saved in the Supabase project for easy access later):

-- Ensures the uuid-ossp extension is enabled for UUID generation
create extension if not exists "uuid-ossp";

-- Creates the public.matches table and enables row level security
create table public.matches (
  match_id uuid not null default uuid_generate_v4 (),
  player1_id uuid not null references public.players on delete cascade,
  player2_id uuid not null references public.players on delete cascade,
  created_at timestamp with time zone not null default now(),

  primary key (match_id)
);

alter table public.matches enable row level security;

[seanmturley]

All of the above tables are created in the public schema, with Row Level Security enabled. In order to query the data, each table must have at least one Row Level Security policy set - see the documentation here.

[seanmturley]

The matchmaking_queue schema has been updated to reflect the approach described in https://github.com/seanmturley/common-ground/issues/33#issuecomment-2497679165, with the addition of the following:

• is_matched
• opponent_id
• is_ready

The matchmaking_queue SQL snippet and table have been updated to add the new columns above.

[seanmturley]

All tables and SQL snippets have been updated to specify not null wherever appropriate.