Add bridge table to db; introduce player ids from oauth subject ids

2 files changed, 20 insertions, 2 deletions

diff --git a/server/migrations/20221008120534_init.down.sql b/server/migrations/20221008120534_init.down.sql
index c855f63..ec1abe4 100644
--- a/server/migrations/20221008120534_init.down.sql
+++ b/server/migrations/20221008120534_init.down.sql
@@ -1,3 +1,5 @@
 -- Add down migration script here
-drop table if exists users;
+drop table if exists players;
 drop table if exists sessions;
+drop table if exists table_players;
+drop table if exists active_tables;
diff --git a/server/migrations/20221008120534_init.up.sql b/server/migrations/20221008120534_init.up.sql
index b3527eb..6a46f84 100644
--- a/server/migrations/20221008120534_init.up.sql
+++ b/server/migrations/20221008120534_init.up.sql
@@ -1,8 +1,24 @@
 -- Add up migration script here
+create table players (
+  id                         varchar(64) primary key not null
+);
+
 create table sessions (
-  id   	     	             uuid primary key,
+  id   	     	             uuid primary key not null,
+  player_id                  varchar(64) references players (id) not null,
   access_token	             varchar(2048) not null,
   access_token_expiration    timestamp with time zone not null,
   refresh_token		     varchar(1024) not null,
   last_refresh		     timestamp with time zone not null default now()
 );
+
+create table active_tables (
+  id			     uuid primary key not null
+);
+
+create table table_players (
+  active_tables_id	     uuid not null references active_tables (id),
+  player_id                  varchar(64) not null references players (id),
+  primary key(active_tables_id, player_id)
+);
+create unique index player_table on table_players (player_id);