## USERS

```
CREATE TABLE roles (
	role_id TINYINT(2) UNSIGNED NOT NULL,
	description CHAR(15) NOT NULL,
	visibility CHAR(50) NOT NULL,
	PRIMARY KEY (role_id)
);
CREATE TABLE users (
	user_id SMALLINT(3) UNSIGNED NOT NULL,
	name CHAR(15) NOT NULL,
	email CHAR(75) NOT NULL,
	role TINYINT(1) UNSIGNED NOT NULL COMMENT 'see roles.role_id',
	venue_id TINYINT(2) UNSIGNED NOT NULL COMMENT 'see datasets.venues.venue_id, [0] = multiple venues',
	pwd_hash CHAR(60) NOT NULL,
	PRIMARY KEY (user_id),
	UNIQUE INDEX (name),
	UNIQUE INDEX (pwd_hash),
	INDEX (venue_id)
);
CREATE TABLE users_venues (
	user_id SMALLINT(3) UNSIGNED NOT NULL COMMENT 'see users.user_id',
	venue_id TINYINT(2) UNSIGNED NOT NULL COMMENT 'see datasets.venues.venue_id',
	PRIMARY KEY (user_id, venue_id),
	INDEX (user_id),
	INDEX (venue_id)
);
CREATE TABLE actions (
	action_id TINYINT(2) UNSIGNED NOT NULL,
	description CHAR(15) NOT NULL, 
	PRIMARY KEY (action_id)
);
CREATE TABLE users_actions (
	user_action_id INT AUTO_INCREMENT PRIMARY KEY,
	app_id TINYINT(2) UNSIGNED NOT NULL,
	user_id SMALLINT(3) UNSIGNED NOT NULL,
	action_id TINYINT(2) UNSIGNED NOT NULL,
	happened_at TIMESTAMP NOT NULL,
	INDEX (app_id),
	INDEX (user_id),
	INDEX (action_id),
	INDEX (happened_at)
);
CREATE TABLE users_sessions (
	user_id smallint(3) NOT NULL,
	session_id mediumint(6) NOT NULL,
	name char(25) NOT NULL,
	saved_at TIMESTAMP NOT NULL,
	PRIMARY KEY (session_id)
);
CREATE TABLE sessions_inputs (
	session_id mediumint(6) NOT NULL,
	ui_id mediumint(6) NOT NULL,
	value char(25) NOT NULL,
	saved_at TIMESTAMP NOT NULL,
	PRIMARY KEY (session_id,ui_id)
);
```

# SHINYAPPS

```
CREATE TABLE apps (
	app_id TINYINT(2) UNSIGNED NOT NULL,
	name CHAR(25) NOT NULL, 
	description CHAR(255) NULL DEFAULT NULL, 
	PRIMARY KEY (app_id),
	UNIQUE INDEX (name)
);
CREATE TABLE menu_items (
	item_code CHAR(3) NOT NULL,
	name CHAR(10) NOT NULL, 
	description CHAR(255) NULL DEFAULT NULL, 
	PRIMARY KEY (item_code)

CREATE TABLE sections (
	section_code CHAR(3) NOT NULL,
	name CHAR(15) NOT NULL, 
	description CHAR(20) NOT NULL,
	PRIMARY KEY (section_code)
);
CREATE TABLE tabs (
	tab_code CHAR(3) NOT NULL,
	name CHAR(15) NOT NULL, 
	description CHAR(20) NOT NULL,
	nature CHAR(1) NOT NULL COMMENT '(A)ttribute, (M)etric, (B)oth',
	PRIMARY KEY (tab_code)
	INDEX (nature)
);
CREATE TABLE ui_inputs (
	ui_code CHAR(20) NOT NULL, 
	name CHAR(15) NOT NULL, 
	description CHAR(50) NOT NULL,
	PRIMARY KEY (ui_id),
	INDEX (ui_code)
);
CREATE TABLE srv_outputs (
	out_code CHAR(20) NOT NULL, 
	name CHAR(15) NOT NULL, 
	description CHAR(50) NOT NULL,
	PRIMARY KEY (ui_id),
	INDEX (ui_code)
);
CREATE TABLE apps_inputs (
	app_input_id INT AUTO_INCREMENT PRIMARY KEY,
	app_id TINYINT(2) UNSIGNED NOT NULL COMMENT 'see shinyapps.apps.app_id',
	item_code CHAR(3) UNSIGNED NOT NULL,
	section_code CHAR(3) UNSIGNED NOT NULL,
	ui_code CHAR(3) NOT NULL,
	`default` CHAR(50) NOT NULL,
	is_numeric TINYINT(1) UNSIGNED NOT NULL,
	has_tracking TINYINT(1) UNSIGNED NOT NULL DEFAULT 0
);
CREATE TABLE apps_outputs (
	app_output_id INT AUTO_INCREMENT PRIMARY KEY,
	app_id TINYINT(2) UNSIGNED NOT NULL COMMENT 'see shinyapps.apps.app_id',
	item_code CHAR(3) UNSIGNED NOT NULL,
	tab_code CHAR(3) NOT NULL,
	out_code CHAR(3) NOT NULL,
	has_tracking TINYINT(1) UNSIGNED NOT NULL DEFAULT 0
);
CREATE TABLE attributes (
	app_id TINYINT(2) UNSIGNED NOT NULL,
	code CHAR(20) NOT NULL, 
	name CHAR(50) NOT NULL, 
	ntitle CHAR(30) NOT NULL, 
	`group` CHAR(30) NOT NULL, 
	gtitle CHAR(20) NOT NULL, 
	title CHAR(40) NOT NULL, 
	with_cols TINYINT(1) UNSIGNED NOT NULL COMMENT 'Flag for ',
	slider TINYINT(1) UNSIGNED NOT NULL COMMENT 'Flag for ',
	is_active TINYINT(1) UNSIGNED NOT NULL COMMENT 'Flag for ',
	in_filter TINYINT(1) UNSIGNED NOT NULL COMMENT 'Flag for ',
	in_theme TINYINT(1) UNSIGNED NOT NULL COMMENT 'Flag for ',
	in_popup TINYINT(1) UNSIGNED NOT NULL COMMENT 'Flag for ',
	in_download TINYINT(1) UNSIGNED NOT NULL COMMENT 'Flag for ',
	has_cumulates TINYINT(1) UNSIGNED NOT NULL COMMENT 'Flag for ',
	has_pct TINYINT(1) UNSIGNED NOT NULL COMMENT 'Flag for ',
	PRIMARY KEY (app_id, code)
);
CREATE TABLE metrics (
	app_id TINYINT(2) UNSIGNED NOT NULL,
	code CHAR(20) NOT NULL, 
	name CHAR(50) NOT NULL, 
	title CHAR(50) NOT NULL, 
	formula CHAR(100) NOT NULL, 
	is_active TINYINT(1) UNSIGNED NOT NULL COMMENT 'Flag for ',
	has_pct TINYINT(1) UNSIGNED NOT NULL COMMENT 'Flag for ',
	PRIMARY KEY (app_id, code)
);
CREATE TABLE vars_tabs (
	app_id TINYINT(2) UNSIGNED NOT NULL,
	var_type CHAR(1) COMMENT '(A)ttribute or (M)etric'
	var_code CHAR(20) NOT NULL, 
	in_tab CHAR(3) NOT NULL, 
);
```