# Taskboard SQL: dezelfde applicatie op SQLite met echte SQL en een transactie

> Houd de Taskboard sessies, rollen en CSRF op, bewaar de taken in een SQLite-bestand via de native QueryBuilder en verplaats de voltooide taken naar een archief in één atoom SQL-transactie.

Framework: total5
Product: Total.js 5 (total5@0.0.19)
Language: nl
Content-SHA256: d221efe43b531a9e69afcf39a5c64d01843f2203d102b9ea967b772ac870eab5
Snapshot: c0700e321579725c5cf4f5ace1ad7d2866d1b4f93c24a63c0b41b6b7998e976c
Sources: total5-installed@total5@0.0.19
Review: not-recorded

Deze gids verplaatst de [Taskboard-referentie](/en/docs/taskboard-reference/) van ingebouwde NoSQL naar **echte SQL**. De sessies, de drie gebruikers, de CSRF-bescherming en de eigenaarsregels blijven gelijk. De taken leven nu in een SQLite-bestand en de applicatie praat ermee via de native QueryBuilder `DB()` met een klein driver dat eigendom is van de applicatie, precies zoals in het [SQLite-recept](/en/total5/query-builder-sqlite/).

Het nieuwe onderdeel is een **transactie**: *Voltooide archiveren* kopieert de voltooide taken naar een archieftabel en verwijdert ze in één atomaire stap uit de taaktabel, zodat een taak nooit op beide plekken staat en nooit nergens. Alles zijn gebonden parameters en toegestane lijsten; geen enkele waarde uit een verzoek komt ooit in de SQL-tekst.

SQLite komt van Node zelf (`node:sqlite`, hier getest met Node 24), dus er is geen npm-afhankelijkheid. De driver is synchroon, wat prima is voor een kleine applicatie en deze referentie; een drukke productieservice zou een asynchrone driver voor zijn databaseserver en dezelfde QueryBuilder-aanroepen gebruiken. Kopieer de bestanden naar een nieuwe map zoals in de eerste gids (`demo: true`, de drie lokale gebruikers, `total5@0.0.19`, alleen loopback); verwijder `taskboard.sqlite` om met een lege database te beginnen.

## Serverbestanden

```text config
name : Taskboard SQL
demo : true
```

```js definitions/taskboard.js
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);
});
```

```js definitions/sqlite.js
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] : []));
};
```

```js actions/tasks.js
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));
	}
});
```

```js controllers/taskboard.js
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 });
	});
}
```

```html public/index.html
<!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>
```

```js public/app.js
(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(); });
})();
```

## Wat wordt getest


Verwante recepten: [SQLite en de QueryBuilder](/en/total5/query-builder-sqlite/), [QueryBuilder-contract](/en/total5/query-builder-contract/), [CSRF](/en/total5/csrf-protection/) en [Taskboard Live](/en/docs/taskboard-live/).