Database Schema

Heedy uses an sqlite database, which is located at data/heedy.db in the heedy database folder. Any plugins that access or modify the database should have sqlite’s foreign keys on, and be compiled with the json1 extension.

Core Schema

-- This is a meta-table, which specifies the versions of database schema in the database
-- Every plugin that includes tables in the core database must add itself to the table
CREATE TABLE dbversion (
	plugin VARCHAR(36) PRIMARY KEY NOT NULL,
	version INTEGER
);

-- This makes sure that the heedy version is specified, so that future upgrades will know
-- whether a schema modification is necessary
INSERT INTO dbversion VALUES ('heedy',1);

CREATE TABLE users (
	username VARCHAR(36) PRIMARY KEY NOT NULL,
	name VARCHAR NOT NULL DEFAULT '',
	description VARCHAR NOT NULL DEFAULT '',
	icon VARCHAR NOT NULL DEFAULT '',

	-- whether the public or users can read the user
	public_read BOOLEAN NOT NULL DEFAULT FALSE,
	users_read BOOLEAN NOT NULL DEFAULT FALSE,

	-- bcrypt-encoded password hash
	password VARCHAR NOT NULL,

	UNIQUE(username)
);

CREATE INDEX useraccess ON users(public_read,users_read);

CREATE TABLE apps (
	id VARCHAR(36) UNIQUE NOT NULL PRIMARY KEY,

	name VARCHAR NOT NULL,
	description VARCHAR NOT NULL DEFAULT '',
	icon VARCHAR NOT NULL DEFAULT '',

	owner VARACHAR(36) NOT NULL,

	-- Can (but does not have to) have an access token
	access_token VARCHAR UNIQUE DEFAULT NULL,

	created_date DATE NOT NULL DEFAULT CURRENT_DATE,
	-- apps without access tokens don't have access dates
	-- an app that has not yet logged in has this as null
	last_access_date DATE DEFAULT NULL, 

	-- Permissions are granted to a app through scope
	scope VARCHAR NOT NULL DEFAULT '[]',

	settings VARCHAR NOT NULL DEFAULT '{}',
	settings_schema VARCHAR NOT NULL DEFAULT '{}',

	enabled BOOLEAN NOT NULL DEFAULT TRUE,

	-- the "plugin key" of the app if it was generated for a plugin
	plugin VARCHAR DEFAULT NULL,

	CONSTRAINT valid_settings CHECK (json_valid(settings) AND json_type(settings)='object'),
	CONSTRAINT valid_settings_schema CHECK (json_valid(settings_schema)  AND json_type(settings)='object'),

	CONSTRAINT appowner
		FOREIGN KEY(owner) 
		REFERENCES users(username)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);
-- We will want to list apps by owner 
CREATE INDEX appowner ON apps(owner,name);
-- A lot of querying will happen by API key
CREATE INDEX apptoken ON apps(access_token);


CREATE TABLE objects (
	id VARCHAR(36) UNIQUE NOT NULL PRIMARY KEY,
	name VARCHAR NOT NULL,
	description VARCHAR NOT NULL DEFAULT '',
	icon VARCHAR NOT NULL DEFAULT '',
	app VARCHAR(36) DEFAULT NULL,
	owner VARCHAR(36) NOT NULL,
	
	-- key to be used by the app/plugin to reference the object
	-- If not set, it is null, to allow unique constraint
	key VARCHAR(36) DEFAULT NULL,

	-- Tags are used for apps to easily query objects 
	tags VARCHAR NOT NULL DEFAULT '[]',

	type VARCHAR NOT NULL, 	                 -- The object type
	meta VARCHAR NOT NULL DEFAULT '{}',      -- Metadata for the object
	created_date DATE NOT NULL DEFAULT CURRENT_DATE,
	modified_date DATE DEFAULT NULL,		 -- Modification date for the object

	-- Maximal scope that the owner has
	owner_scope VARCHAR NOT NULL DEFAULT '["*"]',

	CONSTRAINT objectapp
		FOREIGN KEY(app) 
		REFERENCES apps(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,

	CONSTRAINT objectowner
		FOREIGN KEY(owner) 
		REFERENCES users(username)
		ON UPDATE CASCADE
		ON DELETE CASCADE,

	CONSTRAINT valid_tags CHECK (json_valid(tags)  AND json_type(tags)='array'),
	CONSTRAINT valid_scope CHECK (json_valid(owner_scope)  AND json_type(owner_scope)='array'),
	CONSTRAINT valid_meta CHECK (json_valid(meta) AND json_type(meta)='object'),

	-- The object key is unique per app. We use key first to have the index on key first
	UNIQUE(key,app),
	CONSTRAINT apps_only_key CHECK (key IS NULL OR app IS NOT NULL)
);

------------------------------------------------------------------------------------
-- SHARING
------------------------------------------------------------------------------------

CREATE TABLE shared_objects (
	username VARCHAR(36) NOT NULL,
	objectid VARCHAR(36) NOT NULL,
	scope VARCHAR NOT NULL DEFAULT '["read"]',

	PRIMARY KEY (username,objectid),
	UNIQUE (username,objectid),

	CONSTRAINT objectuser
		FOREIGN KEY(username)
		REFERENCES users(username)
		ON UPDATE CASCADE
		ON DELETE CASCADE,

	CONSTRAINT sharedobject
		FOREIGN KEY(objectid)
		REFERENCES objects(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,

	CONSTRAINT valid_scope CHECK (json_valid(scope) AND json_type(scope)='array')
);

CREATE INDEX share_objectid on shared_objects(objectid);


------------------------------------------------------------------
-- User Sessions
------------------------------------------------------------------
-- These are used to control manually logged in users,
-- so that we don't need to put passwords in cookies,
-- and can remotely log out from other browsers

CREATE TABLE user_sessions (
	username VARCHAR(36) NOT NULL,
	sessionid VARCHAR NOT NULL,

	token VARCHAR UNIQUE NOT NULL,

	description VARCHAR,
	created_date DATE NOT NULL DEFAULT CURRENT_DATE,
	last_access_date DATE NOT NULL DEFAULT CURRENT_DATE,

	CONSTRAINT pk PRIMARY KEY (username,sessionid),

	CONSTRAINT fk_user
		FOREIGN KEY(username) 
		REFERENCES users(username)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

-- This will be requested on every single query
CREATE INDEX user_login_tokens ON user_sessions(token);

------------------------------------------------------------------
-- User Settings
------------------------------------------------------------------
-- The settings are per-user k/v pairs for each plugin . Heedy settings
-- use the plugin 'heedy'. The schemas are defined in heedy.conf

CREATE TABLE user_settings (
	user VARCHAR NOT NULL,

	plugin VARCHAR NOT NULL,
	key VARCHAR NOT NULL,
	value VARCHAR NOT NULL DEFAULT 'null',

	CONSTRAINT pk PRIMARY KEY (user,plugin,key),
	CONSTRAINT valid_value CHECK(json_valid(value)),

	CONSTRAINT fk
		FOREIGN KEY (user)
		REFERENCES users(username)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);


------------------------------------------------------------------
-- Database Views
------------------------------------------------------------------

CREATE VIEW user_object_scope(user,object,scope) AS
	SELECT objects.owner,objects.id,'*' FROM objects WHERE objects.app IS NULL
	UNION ALL
	SELECT objects.owner,objects.id,value FROM objects,json_each(objects.owner_scope) WHERE objects.app IS NOT NULL
	UNION ALL
	SELECT shared_objects.username,objects.id,ss.value FROM objects,shared_objects,json_each(shared_objects.scope) AS ss WHERE shared_objects.objectid=objects.id AND ss.value<>'*' AND EXISTS (SELECT sss.value FROM json_each(objects.owner_scope) AS sss WHERE sss.value=ss.value OR sss.value='*')
	UNION ALL
	SELECT shared_objects.username,objects.id,sss.value FROM objects,shared_objects,json_each(objects.owner_scope) AS sss WHERE shared_objects.objectid=objects.id AND EXISTS (SELECT 1 FROM json_each(shared_objects.scope) AS ss WHERE ss.value='*')
	;


------------------------------------------------------------------
-- Database Default Users
------------------------------------------------------------------

INSERT INTO users (username,name,description,icon,password) VALUES 
-- The public/users virtual users are created by default, and cannot be deleted,
-- as they represent the database view that someone not logged in will get,
-- and the objects accessible to a user who is logged in
(
	'users',
	'Users',
	'All logged-in users',
	'perm_identity',
	'-'
),(
	'public',
	'Public',
	'Represents everyone - any visitor to this heedy server',
	'share',
	'-'
),
-- The heedy user represents the database internals. It is used as the actor
-- when the software or plugins do something 
(
	"heedy",
	"Heedy",
	"",
	"remove_red_eye",
	"-"
);

Timeseries

CREATE TABLE timeseries (
	tsid VARCHAR(36) NOT NULL,
	tstart REAL NOT NULL,
	tend REAL NOT NULL,
	length INTEGER NOT NULL,

	-- timeseries data comes as zstandard-compressed msgpack array batches
	data BLOB,

	PRIMARY KEY (tsid,tstart),
	CONSTRAINT valid_range CHECK (tstart <= tend AND length > 0),

	CONSTRAINT object_fk
		FOREIGN KEY(tsid)
		REFERENCES objects(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE

);
CREATE INDEX timeseries_duration ON timeseries(tsid,tend,tstart);

Dashboards

CREATE TABLE dashboard_elements (
	object_id VARCHAR(36) NOT NULL,
	element_id VARCHAR(36) NOT NULL,

	element_index INT NOT NULL,

	-- The element type specifies the API call to make for backend data
	type VARCHAR NOT NULL,

	-- To save on computation, dashboards are updated on-demand
	outdated BOOL NOT NULL DEFAULT TRUE,
	on_demand BOOL NOT NULL DEFAULT TRUE,

	title VARCHAR(100) NOT NULL,

	-- The query to run on the backend to update data
	query BLOB NOT NULL,
	-- Saved output of query, compressed with zstandard (can be large)
	data BLOB DEFAULT NULL,
	-- Settings for displaying the data on the frontend
	settings BLOB NOT NULL,

	PRIMARY KEY (object_id,element_id),

	CONSTRAINT all_valid CHECK (json_valid(query) AND json_valid(settings)),

	CONSTRAINT object_updater
		FOREIGN KEY(object_id)
		REFERENCES objects(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE dashboard_events (
	object_id VARCHAR(36) NOT NULL,
	element_id VARCHAR(36) NOT NULL,

	-- The event
	event VARCHAR NOT NULL,
	event_object_id VARCHAR NOT NULL,

	PRIMARY KEY (object_id,element_id,event_object_id,event),

	CONSTRAINT underlying_element
		FOREIGN KEY(object_id,element_id)
		REFERENCES dashboard_elements(object_id,element_id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT event_object_c
		FOREIGN KEY(event_object_id)
		REFERENCES objects(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);
CREATE INDEX events_idx ON dashboard_events(event_object_id,event);

Notifications

-- We split up the schema into 3 tables due to issues with UNIQUE when certain values are NULL.
-- We need apps/objects to be nullable to represent notifications for users/apps
-- https://stackoverflow.com/questions/22699409/sqlite-null-and-unique

CREATE TABLE notifications_user (
	user VARCHAR NOT NULL,
	key VARCHAR NOT NULL,

	title VARCHAR NOT NULL,
	description VARCHAR NOT NULL DEFAULT '',
	type VARCHAR NOT NULL DEFAULT 'info',
	timestamp REAL NOT NULL,
	actions VARCHAR NOT NULL DEFAULT '[]',

	-- User notifications are global=true
	global BOOLEAN NOT NULL DEFAULT true,
	dismissible BOOLEAN NOT NULL DEFAULT true,
	seen BOOLEAN NOT NULL DEFAULT false,

	CONSTRAINT pk PRIMARY KEY (user,key),
	CONSTRAINT valid_actions CHECK(json_valid(actions) AND json_type(actions)=='array'),

	CONSTRAINT user_c
		FOREIGN KEY (user)
		REFERENCES users(username)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE notifications_app (
	user VARCHAR NOT NULL,
	app VARCHAR NOT NULL,
	key VARCHAR NOT NULL,

	title VARCHAR NOT NULL,
	description VARCHAR NOT NULL DEFAULT '',
	type VARCHAR NOT NULL DEFAULT 'info',
	timestamp REAL NOT NULL,
	actions VARCHAR NOT NULL DEFAULT '[]',

	global BOOLEAN NOT NULL DEFAULT false,
	seen BOOLEAN NOT NULL DEFAULT false,
	dismissible BOOLEAN NOT NULL DEFAULT true,

	CONSTRAINT pk PRIMARY KEY (user,app,key),
	CONSTRAINT valid_actions CHECK(json_valid(actions) AND json_type(actions)=='array'),

	CONSTRAINT user_c
		FOREIGN KEY (user)
		REFERENCES users(username)
		ON UPDATE CASCADE
		ON DELETE CASCADE,

	CONSTRAINT app_c
		FOREIGN KEY (app)
		REFERENCES apps(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE notifications_object (
	user VARCHAR NOT NULL,
	app VARCHAR NOT NULL,
	object VARCHAR NOT NULL,
	key VARCHAR NOT NULL,

	title VARCHAR NOT NULL,
	description VARCHAR NOT NULL DEFAULT '',
	type VARCHAR NOT NULL DEFAULT 'info',
	actions VARCHAR NOT NULL DEFAULT '[]',
	timestamp REAL NOT NULL,

	global BOOLEAN NOT NULL DEFAULT false,
	seen BOOLEAN NOT NULL DEFAULT false,
	dismissible BOOLEAN NOT NULL DEFAULT true,

	CONSTRAINT pk PRIMARY KEY (user,app,object,key),
	CONSTRAINT valid_actions CHECK(json_valid(actions) AND json_type(actions)=='array'),

	CONSTRAINT user_c
		FOREIGN KEY (user)
		REFERENCES users(username)
		ON UPDATE CASCADE
		ON DELETE CASCADE,

	CONSTRAINT app_c
		FOREIGN KEY (app)
		REFERENCES apps(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,

	CONSTRAINT object_c
		FOREIGN KEY (object)
		REFERENCES objects(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

Key-Value Storage

CREATE TABLE kv_user (
	user VARCHAR NOT NULL,

	namespace VARCHAR NOT NULL,
	key VARCHAR NOT NULL,
	value VARCHAR NOT NULL DEFAULT 'null',

	CONSTRAINT pk PRIMARY KEY (user,namespace,key),
	CONSTRAINT valid_value CHECK(json_valid(value)),

	CONSTRAINT fk
		FOREIGN KEY (user)
		REFERENCES users(username)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE kv_app (
	app VARCHAR NOT NULL,

	namespace VARCHAR NOT NULL,
	key VARCHAR NOT NULL,
	value VARCHAR NOT NULL DEFAULT 'null',

	CONSTRAINT pk PRIMARY KEY (app,namespace,key),
	CONSTRAINT valid_value CHECK(json_valid(value)),

	CONSTRAINT fk
		FOREIGN KEY (app)
		REFERENCES apps(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE kv_object (
	object VARCHAR NOT NULL,

	namespace VARCHAR NOT NULL,
	key VARCHAR NOT NULL,
	value VARCHAR NOT NULL DEFAULT 'null',

	CONSTRAINT pk PRIMARY KEY (object,namespace,key),
	CONSTRAINT valid_value CHECK(json_valid(value)),

	CONSTRAINT fk
		FOREIGN KEY (object)
		REFERENCES objects(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);