Taskboard SQL: the same application on SQLite with real SQL and a transaction

name : Taskboard SQL
demo : true

var crypto = Total.Crypto;
var demo = CONF.demo === true;
if (demo && (process.argv.includes('--release') || process.env.NODE_ENV === 'production'))
	throw new Error('Disable demo mode before a release deployment');
var secret = process.env.TASKBOARD_SECRET || (demo ? 'local-taskboard-demo-secret-not-for-public-use' : '');
if (secret.length < 32)
	throw new Error('TASKBOARD_SECRET needs at least 32 characters');
CONF.secret_csrf = secret;

MAIN.taskUsers = Object.create(null);
for (var name of ['alice', 'bob', 'admin']) {
	var password = process.env['TASKBOARD_' + name.toUpperCase() + '_PASSWORD'] || (demo ? name + '-demo-password' : '');
	if (password.length < 12)
		throw new Error('Configure a password of at least 12 characters for ' + name);
	var salt = crypto.randomBytes(16).toString('hex');
	MAIN.taskUsers[name] = { id: name, name: name, role: name === 'admin' ? 'admin' : 'member', salt: salt, hash: crypto.scryptSync(password, salt, 32) };
}

MAIN.taskAuth = {
	secret: secret,
	cookie: 'taskboard_session',
	expire: '30 minutes',
	options: { httponly: true, samesite: 'Strict', secure: !demo },
	onread: function(meta, next) {
		NOSQL('task-sessions').read().where('id', meta.sessionid).callback(function(err, session) {
			var user = !err && session && session.expire > Date.now() && MAIN.taskUsers[session.userid];
			user ? next(null, { id: user.id, name: user.name, role: user.role }) : next('Session not found');
		});
	},
	onlogout: function(session) {
		NOSQL('task-sessions').remove().where('id', session.sessionid).callback(NOOP);
	},
	onremove: function(id) {
		NOSQL('task-sessions').remove().where('id', id).callback(NOOP);
	}
};
AUTH(MAIN.taskAuth);

MIDDLEWARE('taskcsrf', function($, next) {
	DEF.onCSRFcheck($) ? next() : $.invalid(403);
});

var DatabaseSync = require('node:sqlite').DatabaseSync;
var db = new DatabaseSync(PATH.root('taskboard.sqlite'));

db.exec('CREATE TABLE IF NOT EXISTS tasks (id TEXT PRIMARY KEY, title TEXT NOT NULL CHECK(length(title) BETWEEN 1 AND 80), done INTEGER NOT NULL DEFAULT 0 CHECK(done IN (0, 1)), owner TEXT NOT NULL, created INTEGER NOT NULL) STRICT');
db.exec('CREATE TABLE IF NOT EXISTS task_archive (id TEXT PRIMARY KEY, title TEXT NOT NULL, owner TEXT NOT NULL, created INTEGER NOT NULL, archived INTEGER NOT NULL) STRICT');

var COLUMNS = ['id', 'title', 'done', 'owner', 'created'];

function column(name) {
	if (!COLUMNS.includes(name))
		throw new Error('Column not allowed');
	return '"' + name + '"';
}

function where(options, params) {
	var filters = options.filter.map(function(f) {
		if (f.type !== 'where' || f.comparer !== '=')
			throw new Error('Filter not supported');
		params.push(f.name === 'done' ? (f.value ? 1 : 0) : f.value);
		return column(f.name) + ' = ?';
	});
	return filters.length ? ' WHERE ' + filters.join(' AND ') : '';
}

function row(item) {
	item.done = item.done === 1;
	return item;
}

// The only table the QueryBuilder can reach is tasks; values are always bound parameters
NEWDB('sqlite', function(options, callback) {
	try {
		if (options.table !== 'tasks')
			throw new Error('Table not allowed');
		var params = [];
		var condition = where(options, params);
		var result;
		if (['find', 'read'].includes(options.exec)) {
			var sort = (options.sort || ['created_asc']).map(function(s) {
				var match = /^(title|created)_(asc|desc)$/.exec(s);
				if (!match)
					throw new Error('Sort not allowed');
				return column(match[1]) + ' ' + match[2];
			}).join(',');
			var rows = db.prepare('SELECT id, title, done, owner, created FROM tasks' + condition + ' ORDER BY ' + sort + ' LIMIT 200').all(...params).map(row);
			result = options.exec === 'read' ? rows[0] || null : rows;
		} else if (options.exec === 'insert') {
			var p = options.payload;
			db.prepare('INSERT INTO tasks (id, title, done, owner, created) VALUES (?, ?, ?, ?, ?)').run(p.id, p.title, p.done ? 1 : 0, p.owner, p.created);
			result = { id: p.id };
		} else if (options.exec === 'update') {
			if (!condition)
				throw new Error('Filter required');
			var keys = Object.keys(options.payload);
			var set = keys.map(key => column(key) + ' = ?').join(',');
			result = db.prepare('UPDATE tasks SET ' + set + condition).run(...keys.map(key => key === 'done' ? (options.payload[key] ? 1 : 0) : options.payload[key]), ...params).changes;
		} else if (options.exec === 'remove') {
			if (!condition)
				throw new Error('Filter required');
			result = db.prepare('DELETE FROM tasks' + condition).run(...params).changes;
		} else
			throw new Error('Operation not supported');
		callback(null, result);
	} catch (e) {
		callback(e);
	}
});

// One atomic step: the completed tasks (of one owner, or of everybody for the administrator) move to the archive.
// Either both statements are applied or none is.
MAIN.archiveTasks = function(owner) {
	var params = owner ? [owner] : [];
	var filter = owner ? ' AND owner = ?' : '';
	db.exec('BEGIN IMMEDIATE');
	try {
		db.prepare('INSERT INTO task_archive (id, title, owner, created, archived) SELECT id, title, owner, created, ? FROM tasks WHERE done = 1' + filter).run(Date.now(), ...params);
		var moved = db.prepare('DELETE FROM tasks WHERE done = 1' + filter).run(...params).changes;
		db.exec('COMMIT');
		return moved;
	} catch (e) {
		db.exec('ROLLBACK');
		throw e;
	}
};

MAIN.archivedTasks = function(owner) {
	var filter = owner ? ' WHERE owner = ?' : '';
	return db.prepare('SELECT id, title, owner FROM task_archive' + filter + ' ORDER BY archived DESC, id LIMIT 200').all(...(owner ? [owner] : []));
};

function owned(builder, user) {
	return user.role === 'admin' ? builder : builder.where('owner', user.id);
}

NEWACTION('Tasks|list', {
	user: true,
	action: function($) {
		owned(DB().find('sqlite/tasks').sort('title'), $.user).callback(function(err, tasks) {
			err ? $.invalid(500) : $.callback(tasks);
		});
	}
});

NEWACTION('Tasks|create', {
	user: true,
	input: '*title:String(80)',
	action: function($, model) {
		var task = { id: UID(), title: model.title, done: false, owner: $.user.id, created: Date.now() };
		DB().insert('sqlite/tasks', task).callback(function(err) {
			err ? $.invalid(500) : $.callback(task);
		});
	}
});

NEWACTION('Tasks|update', {
	user: true,
	params: '*id:UID',
	input: '*done:Boolean',
	action: function($, model) {
		owned(DB().update('sqlite/tasks', { done: model.done }).id($.params.id), $.user).callback(function(err, count) {
			if (err)
				return $.invalid(500);
			count ? $.success() : $.invalid(404);
		});
	}
});

NEWACTION('Tasks|remove', {
	user: true,
	params: '*id:UID',
	action: function($) {
		if ($.user.role !== 'admin')
			return $.invalid(403);
		DB().remove('sqlite/tasks').id($.params.id).callback(function(err, count) {
			if (err)
				return $.invalid(500);
			count ? $.success() : $.invalid(404);
		});
	}
});

NEWACTION('Tasks|archive', {
	user: true,
	action: function($) {
		try {
			$.callback({ moved: MAIN.archiveTasks($.user.role === 'admin' ? null : $.user.id) });
		} catch (e) {
			$.invalid(500);
		}
	}
});

NEWACTION('Tasks|archived', {
	user: true,
	action: function($) {
		$.callback(MAIN.archivedTasks($.user.role === 'admin' ? null : $.user.id));
	}
});

var attempts = Object.create(null);

exports.install = function() {
	ROUTE('GET /', function($) { $.file(PATH.public('index.html')); });
	ROUTE('GET /session/', session);
	ROUTE('POST /login/ @json <2kb #taskcsrf', login);
	ROUTE('+POST /logout/ @json #taskcsrf', logout);
	ROUTE('+GET /tasks/ --> *Tasks|list');
	ROUTE('+POST /tasks/ <2kb #taskcsrf --> *Tasks|create');
	ROUTE('+PATCH /tasks/{id}/ <2kb #taskcsrf --> *Tasks|update');
	ROUTE('+DELETE /tasks/{id}/ #taskcsrf --> *Tasks|remove');
	ROUTE('+POST /archive/ #taskcsrf --> *Tasks|archive');
	ROUTE('+GET /archive/ --> *Tasks|archived');
};

function session($) {
	$.response.headers['cache-control'] = 'no-store';
	$.json({ user: $.user || null, csrf: $.csrf() });
}

function login($) {
	var key = $.ip;
	var recent = (attempts[key] || []).filter(time => time > Date.now() - 600000);
	attempts[key] = recent;
	if (recent.length >= 5) return $.invalid(429);
	var user = MAIN.taskUsers[String($.body.name || '').toLowerCase()];
	var salt = user ? user.salt : 'dummy-salt';
	var actual = Total.Crypto.scryptSync(String($.body.password || ''), salt, 32);
	var valid = user && Total.Crypto.timingSafeEqual(actual, user.hash);
	if (!valid) {
		recent.push(Date.now());
		return $.invalid(401);
	}
	var id = UID();
	NOSQL('task-sessions').insert({ id: id, userid: user.id, expire: Date.now() + 1800000 }).callback(function(err) {
		if (err) return $.invalid(500);
		MAIN.taskAuth.authcookie($, id, user.id, '30 minutes');
		$.json({ user: { id: user.id, name: user.name, role: user.role }, csrf: $.csrf() });
	});
}

function logout($) {
	NOSQL('task-sessions').remove().where('id', $.sessionid).callback(function(err) {
		if (err) return $.invalid(500);
		MAIN.taskAuth.logout($);
		$.cookie(MAIN.taskAuth.cookie, '', '-1 year', MAIN.taskAuth.options);
		$.json({ ok: true });
	});
}

<!DOCTYPE html>
<html lang="en"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1">
<title>Taskboard SQL · Total.js</title>
<style>
body{font:16px system-ui;background:#f4f5f7;color:#172033;margin:0}main{max-width:720px;margin:8vh auto;padding:30px;background:white;border-radius:18px}header{display:flex;align-items:center;justify-content:space-between;gap:15px}h1{margin:0}p{color:#536078}label{display:block;margin-top:14px}input,button{font:inherit;padding:10px;border:1px solid #bbc4d1;border-radius:8px}input[type=text],input[type=password]{box-sizing:border-box;width:100%;margin-top:5px}button{cursor:pointer;background:#152d57;color:white}form button{margin-top:15px}ul{padding:0;list-style:none}li{display:flex;align-items:center;gap:10px;padding:14px 0;border-bottom:1px solid #eee}.task-title{flex:1;overflow-wrap:anywhere}.muted{font-size:14px;color:#536078}#message{min-height:24px;color:#9d251e}[hidden]{display:none!important}
</style></head><body><main>
<header><h1>Taskboard SQL</h1><span class="muted">Total.js reference</span></header>
<p>Private tasks stored in SQLite, with an atomic archive of the completed ones.</p>
<p id="message" role="status" aria-live="polite"></p>
<form id="login"><h2>Sign in</h2><label for="name">User</label><input id="name" autocomplete="username" required><label for="password">Password</label><input id="password" type="password" autocomplete="current-password" required><button>Sign in</button></form>
<section id="workspace" hidden><header><p id="identity"></p><button id="logout" type="button">Sign out</button></header>
<form id="create"><label for="title">New task</label><input id="title" type="text" maxlength="80" required><button>Add task</button></form>
<ul id="tasks" aria-label="Tasks"></ul><p id="empty">No tasks yet.</p>
<button id="archive" type="button">Archive completed</button>
<h2>Archive</h2><ul id="archived" aria-label="Archived tasks"></ul></section>
</main><script src="/app.js"></script></body></html>

(function() {
	var user = null;
	var csrf = '';
	var busy = false;
	var byid = id => document.getElementById(id);
	async function request(path, method, body) {
		var response = await fetch(path, { method: method || 'GET', credentials: 'same-origin', headers: { 'content-type': 'application/json', 'x-csrf-token': csrf }, body: body === undefined ? undefined : JSON.stringify(body) });
		var data = await response.json().catch(() => null);
		if (!response.ok) {
			if (response.status === 401) { user = null; show(); }
			throw new Error('Request refused (' + response.status + ')');
		}
		if (data === null) throw new Error('Unexpected server response');
		return data;
	}
	function show() {
		byid('login').hidden = !!user;
		byid('workspace').hidden = !user;
		byid('identity').textContent = user ? user.name + ' · ' + user.role : '';
	}
	async function load() {
		var tasks = await request('/tasks/');
		var archived = await request('/archive/');
		byid('tasks').replaceChildren();
		byid('empty').hidden = tasks.length > 0;
		tasks.forEach(function(task) {
			var row = document.createElement('li');
			var checkbox = document.createElement('input');
			checkbox.type = 'checkbox'; checkbox.checked = task.done; checkbox.setAttribute('aria-label', 'Complete ' + task.title);
			checkbox.addEventListener('change', () => work(async () => { await request('/tasks/' + task.id + '/', 'PATCH', { done: checkbox.checked }); await load(); }));
			var title = document.createElement('span'); title.className = 'task-title'; title.textContent = task.title;
			row.append(checkbox, title);
			if (user.role === 'admin') {
				var remove = document.createElement('button'); remove.type = 'button'; remove.textContent = 'Delete';
				remove.addEventListener('click', () => work(async () => { await request('/tasks/' + task.id + '/', 'DELETE'); await load(); }));
				row.append(remove);
			}
			byid('tasks').append(row);
		});
		byid('archived').replaceChildren();
		archived.forEach(function(task) {
			var row = document.createElement('li');
			row.textContent = task.title;
			byid('archived').append(row);
		});
	}
	async function work(fn) {
		if (busy) return;
		busy = true; byid('message').textContent = '';
		document.querySelectorAll('input,button').forEach(control => control.disabled = true);
		try { await fn(); } catch (err) { byid('message').textContent = err.message; }
		finally { busy = false; document.querySelectorAll('input,button').forEach(control => control.disabled = false); }
	}
	byid('login').addEventListener('submit', function(event) {
		event.preventDefault(); work(async function() {
			var result = await request('/login/', 'POST', { name: byid('name').value, password: byid('password').value });
			user = result.user; csrf = result.csrf; byid('password').value = ''; show(); await load();
		});
	});
	byid('create').addEventListener('submit', function(event) {
		event.preventDefault(); work(async function() { await request('/tasks/', 'POST', { title: byid('title').value }); byid('title').value = ''; await load(); });
	});
	byid('archive').addEventListener('click', () => work(async function() { await request('/archive/', 'POST', {}); await load(); }));
	byid('logout').addEventListener('click', () => work(async function() { await request('/logout/', 'POST', {}); user = null; byid('tasks').replaceChildren(); show(); }));
	work(async function() { var result = await request('/session/'); user = result.user; csrf = result.csrf; show(); if (user) await load(); });
})();