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, 
);