- -

-Database Overview -

- -

-The database uses the following schemas, somewhat simplified and redacted to highlight the important bits. - -

Users

- -

-The user table is pretty simple, it just holds the user account information. -Passwords are hashed and salted with SHA-256. - -

-create table users (
-	user_id integer primary key,
-	name text unique collate nocase,
-	mail text unique collate nocase,
-	notify integer, -- email notifications
-	password text, -- hashed & salted
-	salt text
-);
-

- -

-The login session cookie is a 48-bit random number. - -

-create table logins (
-	sid integer primary key,
-	user_id integer,
-	expires real -- julianday
-);
-

- -

-Webhook notification settings are kept in the webhooks table. -The error column keeps any error message such as timeouts or HTTP failure statuses. -It is null if the hook is operational. - -

-create table webhooks (
-	user_id integer primary key,
-	url text,
-	format text,
-	prefix text,
-	error text
-);
-

- -

-The contact list keeps track of friends (positive relation) and blacklisted users (negative relation). - -

-create table contacts (
-	me integer,
-	you integer,
-	relation integer,
-	primary key (me, you)
-);
-

- - -

Modules

- -

-The game modules to load are registered in the titles table. The title_id must match the directory name for the module. - -

-create table titles (
-	title_id text primary key,
-	title_name text,
-	bgg integer,
-	is_symmetric boolean
-);
-

- -

Games

- -

-Each game session uses a handful of tables. -The main table holds the status (open/active/finished), setup information (scenario, options), whose turn it is (active), and the final result. - -

-create table games (
-	game_id integer primary key,
-	status integer,
-
-	title_id text,
-	scenario text,
-	options text,
-
-	player_count integer,
-	join_count integer,
-	invite_count integer,
-	user_count integer,
-
-	owner_id integer,
-	notice text,
-	pace integer,
-	is_private boolean,
-	is_random boolean,
-	is_match boolean,
-
-	ctime datetime,
-	mtime datetime,
-	moves integer,
-	active text,
-	result text,
-
-	is_opposed boolean generated as ( user_count = join_count and join_count > 1 ),
-	is_ready boolean generated as ( player_count = join_count and invite_count = 0 )
-);
-

- -

-The players table connects users to the games they play. - -

-create table players (
-	game_id integer,
-	role text,
-	user_id integer,
-	is_invite integer,
-	primary key (game_id, role)
-);
-

- -

-The game state is represented by a JSON blob. - -

-create table game_state (
-	game_id integer primary key,
-	state text
-);
-

- -

-Each action taken in stored in the game_replay log. This is primarily used for -the detailed "Sherlock" replay view, but is also used to patch running games when -fixing bugs. - -

-create table game_replay (
-	game_id integer,
-	replay_id integer,
-	role text,
-	action text,
-	arguments json,
-	primary key (game_id, replay_id)
-);
-

- -

-Whenever who is active changes, we take a snapshot of the game state -so we can provide the coarse turn-by-turn rewind view that is available when -playing. This table is also used when rewinding games. The replay_id syncs each -snapshot with the corresponding action in the game_replay table. - -

-create table game_snap (
-	game_id integer,
-	snap_id integer,
-	replay_id integer,
-	state text,
-	primary key (game_id, snap_id)
-);
-

- -

-Game chat is kept in another table, and there's also a table to track whether a -user has any unread in-game chat messages. - -

-create table game_chat (
-	game_id integer,
-	chat_id integer,
-	user_id integer,
-	time datetime,
-	message text,
-	primary key (game_id, chat_id)
-);
-
-create table unread_chats (
-	user_id integer,
-	game_id integer,
-	primary key (user_id, game_id)
-);
-

- -

Other tables

- -

-There are several other tables to deal with password reset tokens, email -notifications, messages, and the forum. - -See the full -schema.sql -for more details on these. -- cgit v1.2.3