Schema

Users

name	type	default	constraints	references	description
user_id	UUID	gen_random_uuid()	PRIMARY KEY		unique identifier for all users
username	TEXT		UNIQUE NOT NULL		username in plain text
password	TEXT		NOT NULL		hashed password
verification_code	TEXT		NOT NULL		hashed verification code
admin	BOOLEAN	FALSE			flag is true if user is administrator
verified	BOOLEAN	FALSE			flag is true if user has verified email
disabled	BOOLEAN	FALSE			flag is true if user is disabled
created	INT	EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)			timestamp of creation, in unix format
locked	INT				timestamp of locking, in unix format

Auth_events

name	type	default	constraints	references	description
event_id	SERIAL		PRIMARY KEY		unique id for each event
user_id	UUID			users(user_id)	user related to this event
event_time	INT	EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)			timestamp of action, in unix format
event_type	TEXT		NOT NULL		either 'login' or 'verification'
success	BOOLEAN		NOT NULL		flag is true if authentication was successful
remote_ip	TEXT		NOT NULL		remote IP address from which the authentication originated from
reason	TEXT				description of why action failed

Account_events

name	type	default	constraints	references	description
event_id	SERIAL		PRIMARY KEY		unique id for each event
user_id	UUID			users(user_id)	user related to this event
event_time	INT	EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)			timestamp of action, in unix format
event_type	TEXT		NOT NULL		always 'create'
success	BOOLEAN		NOT NULL		flag is true if action was successful
remote_ip	TEXT		NOT NULL		remote IP address from which the action originated from
reason	TEXT				description of why action failed

Functions

name	type	default	constraints	references	description
function_id	SERIAL		PRIMARY KEY		unique id for each function
user_id	UUID		NOT NULL	users(user_id)	user who created to this function
name	TEXT		NOT NULL		name of the function
code	TEXT		NOT NULL		source code of the function
tests	TEXT		NOT NULL		source code of the unit tests
usecase	TEXT		NOT NULL		description of function's use case
keywords	TEXT		NOT NULL		comma separated list of keywords

Sessions

name	type	default	constraints	references	description
session_id	UUID	gen_random_uuid()	PRIMARY KEY		unique id for each session
user_id	UUID		NOT NULL	users(user_id)	user who is associated with this session
created	INT	EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)			timestamp of creation, in unix format
data	BYTEA				session data, LZMA compressed JSON

Ratings

name	type	default	constraints	references	description
rating_id	SERIAL				unique id for each rating
user_id	UUID		NOT NULL	users(user_id)	user who created this rating
function_id	INT		NOT NULL	functions(function_id)	function, that is being rated
value	INT				integer from 1 to 5
			PRIMARY KEY(user_id, function_id)