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.

Core Schema

-- This is a meta-table, which specifies the versions of database tables
-- Every plugin that includes tables in the core database must add itself to the table
CREATE TABLE heedy (
	name 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 heedy 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,
	last_modified 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 Login Tokens
------------------------------------------------------------------
-- These are used to control manually logged in devices,
-- so that we don't need to put passwords in cookies

CREATE TABLE user_logintokens (
	username VARCHAR(36) 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 fk_user
		FOREIGN KEY(username) 
		REFERENCES users(username)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

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

------------------------------------------------------------------
-- 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,
	timestamp REAL NOT NULL,
	duration REAL NOT NULL DEFAULT 0,
	data BLOB,

	PRIMARY KEY (tsid,timestamp),
	CONSTRAINT valid_duration CHECK (duration >= 0),
	CONSTRAINT valid_data CHECK (json_valid(data)),

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

);
CREATE INDEX timeseries_duration ON timeseries(tsid,timestamp+duration) WHERE duration > 0;

CREATE TABLE timeseries_actions (
	tsid VARCHAR(36) NOT NULL,
	timestamp REAL NOT NULL,
	duration REAL NOT NULL DEFAULT 0,
	actor VARCHAR DEFAULT NULL,
	data BLOB,

	PRIMARY KEY (tsid,timestamp),
	CONSTRAINT valid_duration CHECK (duration >= 0),
	CONSTRAINT valid_data CHECK (json_valid(data)),

	CONSTRAINT object_fk
		FOREIGN KEY(tsid)
		REFERENCES objects(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);
CREATE INDEX timeseries_actions_duration ON timeseries_actions(tsid,timestamp+duration) WHERE duration > 0;

CREATE TRIGGER timeseries_overlap_check 
	BEFORE INSERT ON timeseries FOR EACH ROW
	WHEN (
		SELECT COALESCE(max(timestamp)+duration>new.timestamp,FALSE) FROM timeseries INDEXED BY timeseries_duration WHERE tsid=new.tsid AND timestamp<new.timestamp AND timestamp+duration>new.timestamp AND duration > 0
	) OR (
		SELECT COALESCE(new.timestamp+new.duration>MIN(timestamp),FALSE) FROM timeseries WHERE tsid=new.tsid AND timestamp > new.timestamp AND new.duration > 0
	)
	BEGIN
		SELECT RAISE(FAIL,'Datapoint time range conflicts with existing data');
	END;

CREATE TRIGGER timeseries_actions_overlap_check 
	BEFORE INSERT ON timeseries_actions FOR EACH ROW
	WHEN (
		SELECT COALESCE(max(timestamp)+duration>new.timestamp,FALSE) FROM timeseries_actions INDEXED BY timeseries_actions_duration WHERE tsid=new.tsid AND timestamp<new.timestamp AND timestamp+duration>new.timestamp AND duration > 0
	) OR (
		SELECT COALESCE(new.timestamp+new.duration>MIN(timestamp),FALSE) FROM timeseries_actions WHERE tsid=new.tsid AND timestamp > new.timestamp AND new.duration > 0
	)
	BEGIN
		SELECT RAISE(FAIL,'Datapoint time range conflicts with existing data');
	END;

CREATE TRIGGER timeseries_overlap_check_update
	BEFORE UPDATE ON timeseries FOR EACH ROW
	WHEN (
		SELECT COALESCE(max(timestamp)+duration>new.timestamp,FALSE) FROM timeseries INDEXED BY timeseries_duration WHERE tsid=new.tsid AND timestamp<new.timestamp AND timestamp+duration>new.timestamp AND duration > 0
	) OR (
		SELECT COALESCE(new.timestamp+new.duration>MIN(timestamp),FALSE) FROM timeseries WHERE tsid=new.tsid AND timestamp > new.timestamp AND new.duration > 0
	)
	BEGIN
		SELECT RAISE(FAIL,'Datapoint time range conflicts with existing data');
	END;

CREATE TRIGGER timeseries_actions_overlap_check_update
	BEFORE UPDATE ON timeseries_actions FOR EACH ROW
	WHEN (
		SELECT COALESCE(max(timestamp)+duration>new.timestamp,FALSE) FROM timeseries_actions INDEXED BY timeseries_actions_duration WHERE tsid=new.tsid AND timestamp<new.timestamp AND timestamp+duration>new.timestamp AND duration > 0
	) OR (
		SELECT COALESCE(new.timestamp+new.duration>MIN(timestamp),FALSE) FROM timeseries_actions WHERE tsid=new.tsid AND timestamp > new.timestamp AND new.duration > 0
	)
	BEGIN
		SELECT RAISE(FAIL,'Datapoint time range conflicts with existing data');
	END;

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