1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
-- Add up migration script here
create table players (
id varchar(64) primary key not null
);
create table sessions (
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 bridge_table (
id uuid primary key not null
);
create type player_position as enum ('west', 'north', 'east', 'south');
create type suit as enum ('club', 'diamond', 'heart', 'spade');
create domain rank smallint check (value between 2 and 14);
-- TODO: Remove this.
create table object_journal (
id uuid not null,
seq bigint not null,
payload jsonb not null
);
create unique index journal_entry on object_journal (id, seq);
create table table_players (
table_id uuid not null references bridge_table (id),
player_id varchar(64) not null references players (id),
position player_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)
);
|
