Add Table to be used with db schema

2 files changed, 22 insertions, 4 deletions

diff --git a/server/migrations/20221008120534_init.down.sql b/server/migrations/20221008120534_init.down.sql
index 3bff171..c57e653 100644
--- a/server/migrations/20221008120534_init.down.sql
+++ b/server/migrations/20221008120534_init.down.sql
@@ -2,8 +2,10 @@
 begin;
 drop table if exists sessions;
 drop table if exists table_players;
+drop table if exists table_moves;
+drop table if exists table_boards;
 drop table if exists object_journal;
-drop table if exists active_tables;
+drop table if exists bridge_table;
 drop table if exists players;
 drop type if exists player_position;
 drop type if exists suit;
diff --git a/server/migrations/20221008120534_init.up.sql b/server/migrations/20221008120534_init.up.sql
index 05b7697..b8e8470 100644
--- a/server/migrations/20221008120534_init.up.sql
+++ b/server/migrations/20221008120534_init.up.sql
@@ -12,7 +12,7 @@ create table sessions (
   last_refresh		     timestamp with time zone not null default now()
 );
 
-create table active_tables (
+create table bridge_table (
   id			     uuid primary key not null
 );
 
@@ -29,9 +29,25 @@ create table object_journal (
 create unique index journal_entry on object_journal (id, seq);
 
 create table table_players (
-  active_tables_id	     uuid not null references active_tables (id),
+  table_id	     uuid not null references bridge_table (id),
   player_id                  varchar(64) not null references players (id),
   position		     player_position,
-  primary key(active_tables_id, player_id, position)
+  primary key(table_id, player_id, position)
 );
 create unique index player_table on table_players (player_id);
+
+create table table_boards (
+   table_id uuid not null references bridge_table (id),
+   board_number  integer not null,
+   deal jsonb not null,
+   primary key(table_id, board_number)
+);
+
+create table table_moves (
+  table_id uuid not null,
+  board_number integer not null,
+  move_number integer not null,
+  move jsonb not null,
+  foreign key (table_id, board_number) references table_boards (table_id, board_number),
+  primary key(table_id, board_number, move_number)
+);