CREATE TABLE version ( version INTEGER NOT NULL DEFAULT 0, dirty BOOL NOT NULL DEFAULT false, _only_row BOOL NOT NULL UNIQUE CHECK(_only_row) DEFAULT true ); INSERT INTO version (version, dirty) VALUES (0, false); CREATE TABLE person( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, firstname TEXT NOT NULL, lastname TEXT NOT NULL, username TEXT NOT NULL, email TEXT NOT NULL DEFAULT '', password TEXT NOT NULL DEFAULT '', -- CHECK (CASE when email is null then password is not null when password is null then email is not null), password_attempts_remaining INT, email_confirmed BOOL NOT NULL DEFAULT false, approved BOOL NOT NULL DEFAULT false, max_businesses INT ); CREATE TRIGGER IF NOT EXISTS person_upd_trig AFTER UPDATE ON person BEGIN update person SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE person_session( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, token TEXT NOT NULL, expires_at INTEGER NOT NULL, person_id INTEGER REFERENCES person(id) ); CREATE TRIGGER IF NOT EXISTS person_session_upd_trig AFTER UPDATE ON person_session BEGIN update person_session SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE person_request( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- {user_approve|password_reset|change_email|confirm_email} request_type TEXT NOT NULL, request_value TEXT NOT NULL DEFAULT '', request_text TEXT NOT NULL DEFAULT '', response_text TEXT NOT NULL DEFAULT '', -- e.g. for confirm email challenge_code TEXT NOT NULL DEFAULT '', -- {'requested'|'granted'|'denied'|'more_info_needed'} response_status TEXT NOT NULL DEFAULT 'requested', person_id INTEGER REFERENCES person(id) ); CREATE TRIGGER IF NOT EXISTS person_request_upd_trig AFTER UPDATE ON person_request BEGIN update person_request SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE role( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- business_owner|new_person_approver|site_admin name TEXT NOT NULL ); INSERT INTO role (name) VALUES ('business_owner'), ('new_person_approver'), ('site_admin'); CREATE TRIGGER IF NOT EXISTS role_upd_trig AFTER UPDATE ON role BEGIN update role SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE person_role( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, person_id INTEGER REFERENCES person(id), role_id INTEGER REFERENCES role(id) ); CREATE TABLE site_config ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, title TEXT NOT NULL DEFAULT 'Lx', support_email TEXT NOT NULL DEFAULT '', support_phone TEXT NOT NULL DEFAULT '', purpose TEXT NOT NULL DEFAULT '', disclaimer TEXT NOT NULL DEFAULT '', max_default_businesses INTEGER NOT NULL DEFAULT 5, listen_address TEXT NOT NULL DEFAULT '0.0.0.0', port INTEGER NOT NULL DEFAULT 8080, signup_needs_approve BOOL NOT NULL DEFAULT true, session_length_max INTEGER NOT NULL DEFAULT 86400, email_confirm BOOL NOT NULL DEFAULT false, email_required BOOL NOT NULL DEFAULT false, email_regex TEXT NOT NULL DEFAULT '.@.', -- fips140 requires 16 bytes or more password_salt_length INTEGER NOT NULL DEFAULT 32 CHECK(password_salt_length >= 16), -- fips140 requires 14 bytes or more password_key_length INTEGER NOT NULL DEFAULT 32 CHECK(password_key_length >= 14), password_rounds INTEGER NOT NULL DEFAULT 1000, -- 'SHA2_256', 'SHA3_256', 'SHA3_512' supported -- (32 bytes), (32 bytes), (64 bytes) password_hash_strategy TEXT NOT NULL DEFAULT 'SHA2_256', password_attempts INTEGER NOT NULL DEFAULT 20, password_min INTEGER NOT NULL DEFAULT 8, password_regex TEXT NOT NULL DEFAULT '[A-Za-z]{1,}@@@[0-9]{1,}@@@[!@#$%^&*().,<>;:{}?]{1,}', password_help TEXT NOT NULL DEFAULT 'password must have 8 characters, one uppercase, one lowercase, one number, and one special character ("!", "@", "#", "$", "%", "^", "&", "*", "(", ")", ".", ",", "<", ">", ";", ":", "{", "}", "?")', password_required BOOL NOT NULL DEFAULT false, log_level TEXT NOT NULL DEFAULT 'info', log_level_key TEXT NOT NULL DEFAULT 'lvl', log_level_overrides TEXT NOT NULL DEFAULT '', log_time_format TEXT NOT NULL DEFAULT '', log_time_key TEXT NOT NULL DEFAULT '', log_add_source BOOL NOT NULL DEFAULT false, person_style_text_color TEXT NOT NULL DEFAULT '#000000', person_style_text_dark_color TEXT NOT NULL DEFAULT '#FFFFFF', person_style_background_color TEXT NOT NULL DEFAULT '#FFFFFF', person_style_background_dark_color TEXT NOT NULL DEFAULT '#000000', -- disable analytics for all site, e.g. -- because emergency performance problem or -- because instance operator wants no analytics, ever analytics_disable BOOL NOT NULL DEFAULT false, analytics_write_timeout INT NOT NULL DEFAULT 15, analytics_read_timeout INT NOT NULL DEFAULT 60, -- merchant details merchant_default TEXT NOT NULL DEFAULT 'dummy', -- once daily to minimize load config_reload_seconds INTEGER NOT NULL DEFAULT 86400, _only_row BOOL NOT NULL UNIQUE CHECK(_only_row) DEFAULT true ); INSERT INTO site_config (_only_row) VALUES (true); CREATE TRIGGER IF NOT EXISTS site_config_upd_trig AFTER UPDATE ON site_config BEGIN update site_config SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE site_usage_limits ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name TEXT NOT NULL DEFAULT '', noun TEXT NOT NULL DEFAULT '', verb TEXT NOT NULL DEFAULT '', subject TEXT NOT NULL DEFAULT '', -- e.g. -- drop_blocks: '45.45.45.0/24,86.86.0.0/16' extra TEXT NOT NULL DEFAULT '', -- e.g. {drop_blocks|allow_blocks} extra_type TEXT NOT NULL DEFAULT '', -- form tbd, possibly a json string windows TEXT NOT NULL DEFAULT '', UNIQUE(name) ); CREATE TRIGGER IF NOT EXISTS site_usage_limits_upd_trig AFTER UPDATE ON site_usage_limits BEGIN update site_usage_limits SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE business_tier( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name TEXT NOT NULL DEFAULT '', display_name TEXT NOT NULL DEFAULT '', feature_level INT NOT NULL DEFAULT -1, backhalves_anonymous INT NOT NULL DEFAULT 0, backhalves_named INT NOT NULL DEFAULT 0, folders INT NOT NULL DEFAULT 0, analytics_retention INT NOT NULL DEFAULT 0, analytics_max_granularity INT NOT NULL DEFAULT 0, analytics_view_limit INT NOT NULL DEFAULT 0, price_by_year INT NOT NULL DEFAULT 0, price_by_month INT NOT NULL DEFAULT 0, UNIQUE(name) ); CREATE TRIGGER IF NOT EXISTS business_tier_upd_trig AFTER UPDATE ON business_tier BEGIN update business_tier SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; INSERT INTO business_tier (name, display_name, feature_level) VALUES ('zero', 'Zero', 0), ('start', 'Starter',10), ('medium', 'Medium',20), ('high', 'Advanced',30), ('unbounded', 'Unbounded', 9999); CREATE TABLE merchant( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name TEXT NOT NULL DEFAULT '', api_url TEXT NOT NULL DEFAULT '', UNIQUE(name) ); CREATE TRIGGER IF NOT EXISTS merchant_upd_trig AFTER UPDATE ON merchant BEGIN update merchant SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; INSERT INTO merchant (name) VALUES ('dummy'); CREATE TABLE merchant_business_tier( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, merchant_business_tier_id_string TEXT NOT NULL, merchant_id INTEGER REFERENCES business_tier(id) NOT NULL, business_tier_id INTEGER REFERENCES business_tier(id) NOT NULL, UNIQUE(merchant_id, merchant_business_tier_id_string) ); CREATE TRIGGER IF NOT EXISTS merchant_business_tier_upd_trig AFTER UPDATE ON merchant_business_tier BEGIN update merchant_business_tier SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; INSERT INTO merchant_business_tier ( merchant_business_tier_id_string, merchant_id, business_tier_id ) VALUES ( 'dummy-plan-id-high', (SELECT id FROM merchant WHERE name = 'dummy'), (SELECT id FROM business_tier WHERE name = 'high') ), ( 'dummy-plan-id-medium', (SELECT id FROM merchant WHERE name = 'dummy'), (SELECT id FROM business_tier WHERE name = 'medium') ), ( 'dummy-plan-id-start', (SELECT id FROM merchant WHERE name = 'dummy'), (SELECT id FROM business_tier WHERE name = 'start') ), ( 'dummy-plan-id-unbounded', (SELECT id FROM merchant WHERE name = 'dummy'), (SELECT id FROM business_tier WHERE name = 'unbounded') ), ( 'dummy-plan-id-zero', (SELECT id FROM merchant WHERE name = 'dummy'), (SELECT id FROM business_tier WHERE name = 'zero') ) ; CREATE TABLE business ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name TEXT NOT NULL DEFAULT '', display_name TEXT NOT NULL DEFAULT '', config_collect_stats BOOL NOT NULL DEFAULT true, -- intended for an admin account, e.g. could do promotional links -- for community events, e.g. -- mydomain.com/community-storytelling-event -- or preregister links to known public institutions, e.g. -- mydomain.com/library is_free BOOL NOT NULL DEFAULT false, analytics_request_window TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, analytics_request_count INTEGER NOT NULL DEFAULT 0, merchant TEXT NOT NULL, plan_id_str TEXT NOT NULL, business_tier_id INTEGER REFERENCES business_tier(id) NOT NULL, address_city TEXT NOT NULL, address_country TEXT NOT NULL, address_line1 TEXT NOT NULL, address_line2 TEXT NOT NULL, address_postal_code TEXT NOT NULL, address_state TEXT NOT NULL, UNIQUE(name) ); CREATE TRIGGER IF NOT EXISTS business_upd_trig AFTER UPDATE ON business BEGIN update business SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE person_business ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, person_id INTEGER REFERENCES person(id) NOT NULL, business_id INTEGER REFERENCES business(id) NOT NULL ); CREATE TRIGGER IF NOT EXISTS business_upd_trig AFTER UPDATE ON business BEGIN update business SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; -- expose as part of config, -- these are domains the lx instance knows about administering for CREATE TABLE domain ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name TEXT NOT NULL DEFAULT '', UNIQUE(name) ); CREATE TRIGGER IF NOT EXISTS domain_upd_trig AFTER UPDATE ON domain BEGIN update domain SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; -- folders only go one deep. -- they are a path segment on a slug -- e.g. mydomain.com/my-biz-org/backhalf-a -- mydomain.com/my-biz-org/backhalf-b -- ^ folder ^ CREATE TABLE folder ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name TEXT NOT NULL DEFAULT '', allow_sublease BOOL NOT NULL DEFAULT FALSE, business_id INTEGER REFERENCES business(id) NOT NULL, domain_id INTEGER REFERENCES domain(id) NOT NULL, UNIQUE(name) ); CREATE TRIGGER IF NOT EXISTS folder_upd_trig AFTER UPDATE ON folder BEGIN update folder SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; -- back half is the route part of the short link -- -- store config on each back_half, -- and store config defaults at a business level -- store config default templates either in code or in -- yet another table that gets presented in the Config struct CREATE TABLE back_half ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name TEXT NOT NULL DEFAULT '', description TEXT NOT NULL DEFAULT '', business_id INTEGER REFERENCES business(id) NOT NULL, domain_id INTEGER REFERENCES domain(id) NOT NULL, folder_id INTEGER REFERENCES folder(id), redirect TEXT NOT NULL DEFAULT '', -- back_half config analytics_collect BOOL NOT NULL DEFAULT true, analytics_granularity TEXT NOT NULL DEFAULT '1w', analytics_retention TEXT NOT NULL DEFAULT '90d', expire_at TIMESTAMP, -- TODO some sort of stats collection. Simplest would be an array of N ints UNIQUE(domain_id, name) ); CREATE TRIGGER IF NOT EXISTS back_half_upd_trig AFTER UPDATE ON back_half BEGIN update back_half SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; UPDATE version SET version=1, dirty=false; -- DOWN: -- DROP TABLE IF EXISTS back_half; -- DROP TABLE IF EXISTS folder; -- DROP TABLE IF EXISTS domain; -- DROP TABLE IF EXISTS person_business; -- DROP TABLE IF EXISTS business; -- DROP TABLE IF EXISTS merchant_business_tier; -- DROP TABLE IF EXISTS merchant; -- DROP TABLE IF EXISTS business_tier; -- DROP TABLE IF EXISTS site_usage_limits; -- DROP TABLE IF EXISTS site_config; -- DROP TABLE IF EXISTS person_role; -- DROP TABLE IF EXISTS role; -- DROP TABLE IF EXISTS person_request; -- DROP TABLE IF EXISTS person_session; -- DROP TABLE IF EXISTS person; -- DROP TABLE IF EXISTS version;