database.md

Database

This document lays out the database information for the game and it's format

Game data

Events - events

name	type	nullable	notes
id	text	false	primary key
serial	bigint	false
type	text	false
user_id	text	false	foreign key to proj_users
game_id	text	true	foreign key to proj_games
payload	jsonb	false
created_at	datetime	false

Users - proj_users

name	type	nullable	notes
id	text	false	primary key
handle	text	false
numeric	text	false
pin	text	true	hashed
score	int	false
created_at	datetime	false

Games - proj_games

name	type	nullable	notes
id	text	false	primary key
user_id	text	false	foreign key to proj_users
difficulty_options	jsonb	false
game_options	jsonb	false
score	int	false
created_at	datetime	false
finished_at	datetime	true

Prompts - proj_prompts

name	type	nullable	notes
id	text	false	primary key
user_id	text	false	foreign key to proj_users
game_id	text	false	foreign key to proj_games
prompt	text	false
answer	text	false
answer_given	text	true
correct	boolean	false
created_at	datetime	false
answered_at	datetime	true
hint_given_at	datetime	true

TFL Data

Modes - tfl_modes

name	type	nullable	notes
id	text	false	primary key
name	text	false

Lines - tfl_lines

name	type	nullable	notes
id	text	false	primary key
name	text	false
mode_name	text	false
created_at	datetime	false
modified_at	datetime	false

Stops - tfl_stops

name	type	nullable	notes
id	text	false	primary key
name	text	false
ics_code	text	false
station_naptan	text	false
status	boolean	false
lat	float	false
lon	float	false

Lines stops (pivot) - tfl_lines_stops

name	type	nullable	notes
line_id	text	false	primary key
stop_id	text	false	primary key
mode	text	false