Massive SQL cleanup.

22 files changed, 1365 insertions, 1398 deletions

diff --git a/migrate.sql b/migrate.sql
new file mode 100644
index 0000000..1d218b3
--- /dev/null
+++ b/migrate.sql
@@ -0,0 +1,111 @@
+attach database 'old.db' as old;
+
+pragma foreign_keys = on;
+
+.read tools/sql/schema.txt
+
+-- Drop triggers while migrating data:
+drop trigger no_join_on_active_game;
+
+BEGIN;
+
+.read tools/sql/data-300-ew.txt
+.read tools/sql/data-caesar.txt
+.read tools/sql/data-crusader.txt
+.read tools/sql/data-hammer.txt
+.read tools/sql/data-richard.txt
+.read tools/sql/data-tripoli.txt
+
+insert into users
+        (user_id,name,mail,notify,password,salt,ctime,about)
+values
+	(0, 'Deleted', 'deleted@rally-the-troops.com', 0, '', '', '1970-01-01 00:00:00', 'Deleted user.');
+
+-- Users
+
+insert into users (
+        user_id,name,mail,notify,password,salt,ctime,about
+) select
+        user_id,name,mail,notifications,password,salt,ctime,about
+from old.users;
+
+insert into user_last_seen (
+        user_id,atime,aip
+) select
+        user_id,atime,aip
+from old.users;
+
+insert into tokens (
+        user_id,token,time
+) select
+        user_id,token,time
+from old.tokens;
+
+-- Messages and Forum
+
+insert into messages (
+        message_id,from_id,to_id,time,subject,body,read,deleted_from_inbox,deleted_from_outbox
+) select
+        message_id,from_id,to_id,time,subject,body,read,deleted_from_inbox,deleted_from_outbox
+from old.messages;
+
+insert into threads (
+	thread_id,author_id,subject,locked
+) select
+	thread_id,author_id,subject,locked
+from old.threads;
+
+insert into posts (
+	post_id,thread_id,author_id,ctime,mtime,body
+) select
+	post_id,thread_id,author_id,ctime,mtime,body
+from old.posts;
+
+-- Games
+
+insert into games (
+	game_id,title_id,scenario,options,owner_id,ctime,private,random,description,status,result
+) select
+	game_id,title_id,scenario,options,owner_id,ctime,private,random,description,status,result
+from old.games;
+
+insert into game_state (
+	game_id,mtime,active,state
+) select
+	game_id,mtime,active,state
+from old.games;
+
+insert into game_chat (
+	game_id,time,user_id,message
+) select
+	game_id
+	, datetime(json_extract(value,'$[0]')) AS time
+	, (select user_id from old.users where name=json_extract(value,'$[1]'))
+	, json_extract(value,'$[2]')
+from old.chats, json_each(chat,'$')
+order by time;
+
+insert into game_replay (
+	game_id,time,role,action,arguments
+) select
+	game_id,time,role,action,arguments
+from old.replay;
+
+insert into players (
+	user_id,game_id,role
+) select
+	user_id,game_id,role
+from old.players;
+
+-- Foo
+
+insert into last_notified (
+        game_id,user_id,time
+) select
+        game_id,user_id,time
+from old.notifications;
+
+COMMIT;
+
+-- re-enable triggers
+.read tools/sql/schema.txt
diff --git a/public/common/client.js b/public/common/client.js
index 9ef89e5..c516880 100644
--- a/public/common/client.js
+++ b/public/common/client.js
@@ -11,7 +11,8 @@ let chat_is_visible = false;
 let chat_text = null;
 let chat_key = null;
 let chat_last_day = null;
-let chat_log = null;
+let chat_log = 0;
+let chat_seen = 0;
 
 function scroll_with_middle_mouse(panel_sel, multiplier) {
 	let panel = document.querySelector(panel_sel);
@@ -104,7 +105,19 @@ function stop_blinker() {
 
 window.addEventListener("focus", stop_blinker);
 
-function add_chat_lines(log) {
+function load_chat(game_id) {
+	chat_key = "chat/" + game_id;
+	chat_text = document.querySelector(".chat_text");
+	chat_last_day = null;
+	chat_log = 0;
+	chat_seen = window.localStorage.getItem(chat_key) | 0;
+}
+
+function save_chat() {
+	window.localStorage.setItem(chat_key, chat_log);
+}
+
+function update_chat(chat_id, utc_date, user, message) {
 	function format_time(date) {
 		let mm = date.getMinutes();
 		let hh = date.getHours();
@@ -124,10 +137,9 @@ function add_chat_lines(log) {
 		chat_text.appendChild(line);
 		chat_text.scrollTop = chat_text.scrollHeight;
 	}
-	for (let entry of log) {
-		chat_log.push(entry);
-		let [date, user, message] = entry;
-		date = new Date(date);
+	if (chat_id > chat_log) {
+		chat_log = chat_id;
+		let date = new Date(utc_date + "Z");
 		let day = date.toDateString();
 		if (day !== chat_last_day) {
 			add_date_line(day);
@@ -135,37 +147,14 @@ function add_chat_lines(log) {
 		}
 		add_chat_line(format_time(date), user, message);
 	}
-}
-
-function load_chat(game_id) {
-	chat_key = "chat/" + game_id;
-	chat_text = document.querySelector(".chat_text");
-	chat_last_day = null;
-	chat_log = [];
-	let save = JSON.parse(window.localStorage.getItem(chat_key));
-	if (save) {
-		if (Date.now() < save.expires)
-			add_chat_lines(save.chat);
+	if (chat_id > chat_seen) {
+		let button = document.querySelector(".chat_button");
+		start_blinker("NEW MESSAGE");
+		if (!chat_is_visible)
+			button.classList.add("new");
 		else
-			window.localStorage.removeItem(chat_key);
-	}
-	return chat_log.length;
-}
-
-function save_chat() {
-	const DAY = 86400000;
-	let save = { expires: Date.now() + 7 * DAY, chat: chat_log };
-	window.localStorage.setItem(chat_key, JSON.stringify(save));
-}
-
-function update_chat(log_start, log) {
-	if (log_start === 0) {
-		chat_last_day = null;
-		chat_log = [];
-		while (chat_text.firstChild)
-			chat_text.removeChild(chat_text.firstChild);
+			save_chat();
 	}
-	add_chat_lines(log);
 }
 
 function init_client(roles) {
@@ -209,7 +198,7 @@ function init_client(roles) {
 	socket.on('connect', () => {
 		console.log("CONNECTED");
 		document.querySelector(".grid_top").classList.remove('disconnected');
-		socket.emit('getchat', chat_log.length); // only send new messages when we reconnect!
+		socket.emit('getchat', chat_log); // only send new messages when we reconnect!
 	});
 
 	socket.on('disconnect', () => {
@@ -226,7 +215,7 @@ function init_client(roles) {
 		document.querySelector("body").classList.add(player);
 		for (let i = 0; i < roles.length; ++i) {
 			let pr = players.find(p => p.role === roles[i]);
-			document.querySelector(USER_SEL[i]).textContent = pr ? pr.user_name : "NONE";
+			document.querySelector(USER_SEL[i]).textContent = pr ? pr.name : "NONE";
 		}
 	});
 
@@ -261,15 +250,9 @@ function init_client(roles) {
 		document.getElementById("prompt").textContent = msg;
 	});
 
-	socket.on('chat', function (log_start, log) {
-		console.log("CHAT UPDATE", log_start, log.length);
-		update_chat(log_start, log);
-		let button = document.querySelector(".chat_button");
-		start_blinker("NEW MESSAGE");
-		if (!chat_is_visible)
-			button.classList.add("new");
-		else
-			save_chat();
+	socket.on('chat', function (item) {
+		console.log("CHAT MESSAGE", JSON.stringify(item));
+		update_chat(item[0], item[1], item[2], item[3]);
 	});
 
 	document.querySelector(".chat_form").addEventListener("submit", e => {
diff --git a/public/join.js b/public/join.js
index a5a2e3a..ce3d0d0 100644
--- a/public/join.js
+++ b/public/join.js
@@ -76,8 +76,12 @@ function start_event_source() {
 	}
 }
 
-function is_your_turn(player, role) {
-	return (game.active_role === role || game.active_role === "Both" || game.active_role === "All");
+function is_active(player, role) {
+	return (game.active === role || game.active === "Both" || game.active === "All");
+}
+
+function is_solo() {
+	return players.every(p => p.user_id === players[0].user_id);
 }
 
 function update() {
@@ -94,19 +98,19 @@ function update() {
 		let element = document.getElementById(role_id);
 		if (player) {
 			if (game.status > 0) {
-				if (is_your_turn(player, role))
-					element.className = "is_your_turn";
+				if (is_active(player, role))
+					element.className = "is_active";
 				else
 					element.className = "";
 				if (player.user_id === user_id)
 					element.innerHTML = `<a href="/play/${game.game_id}/${role}">Play</a>`;
 				else
-					element.innerHTML = player.user_name;
+					element.innerHTML = player.name;
 			} else {
 				if ((player.user_id === user_id) || (game.owner_id === user_id))
-					element.innerHTML = `<a class="red" href="javascript:send('/part/${game.game_id}/${role}')">\u274c</a> ${player.user_name}`;
+					element.innerHTML = `<a class="red" href="javascript:send('/part/${game.game_id}/${role}')">\u274c</a> ${player.name}`;
 				else
-					element.innerHTML = player.user_name;
+					element.innerHTML = player.name;
 			}
 		} else {
 			if (game.status === 0)
@@ -131,7 +135,7 @@ function update() {
 	if (game.owner_id === user_id) {
 		window.start_button.disabled = !ready;
 		window.start_button.classList = (game.status === 0) ? "" : "hide";
-		window.delete_button.classList = (game.status === 0 || game.is_solo) ? "" : "hide";
+		window.delete_button.classList = (game.status === 0 || is_solo()) ? "" : "hide";
 		if (game.status === 0 && ready)
 			start_blinker("READY TO START");
 		else
diff --git a/public/style.css b/public/style.css
index 20865a4..f72cdb7 100644
--- a/public/style.css
+++ b/public/style.css
@@ -24,22 +24,51 @@ h2 { margin-left: -1px; }
 .main hr { max-width: 50rem; margin-right: auto; margin-left: 0; }
 .main hr { border: none; border-top: 2px dotted brown; }
 .main hr + p { font-style: italic; }
-table.wide { min-width: 50rem; }
+.is_active { background-color: lemonchiffon; }
 .error { color: brown; font-style: italic; white-space: pre-wrap; }
 .warning { color: brown; }
 .warning::before { content: "\26a0"; }
-.logo { float: left; margin: 0 20px 5px 0; box-shadow: 2px 2px 4px 0px rgba(0,0,0,0.5); height: 200px; }
+img.logo {
+	float: left;
+	margin: 0 20px 5px 0;
+	box-shadow: 2px 2px 4px 0px rgba(0,0,0,0.5);
+	height: 200px;
+}
+img.avatar {
+	float: left;
+	margin: 0 20px 5px 0;
+	box-shadow: 2px 2px 4px 0px rgba(0,0,0,.5);
+	width: 80px; height: 80px;
+}
+
 table { border-collapse: collapse; }
+tfoot td { background-color: gainsboro; }
 th { text-align: left; background-color: gainsboro; }
 th, td { border: 1px solid black; }
 th, td { padding: 3px 1ex; }
+
+table.game { min-width: min(50rem,100%); }
+table.game .title { white-space: nowrap; }
+table.game .scenario { white-space: nowrap; }
+table.game .role { white-space: nowrap; }
+table.game .time { white-space: nowrap; }
+table.game td a { text-decoration: none; color: black; }
+table.game td.command a { text-decoration: underline; color: blue; }
+
+table.post { min-width: min(50rem,100%); }
+table.post .author { white-space: nowrap; width: 10rem; }
+table.post .time { white-space: nowrap; text-align: right; width: 5rem; }
+table.post .replies { width: 0; }
+table.post .unread { background-color: lightyellow; }
+table.post .body { white-space: pre-wrap; padding: 10px 10px; }
+table.post th a { text-decoration: none; color: black; }
+table.post td:not(.body):not(.edit) a { text-decoration: none; color: black; }
+
 label { user-select: none; }
 button, input, select { font-size: 1rem; margin: 5px 0; }
 input[type="text"], input[type="password"], textarea { padding: 5px; }
 select { padding-right: 20px; }
 form { display: inline; }
-.nowrap { white-space: nowrap; }
-.is_your_turn { background-color: lemonchiffon; }
 button, select {
 	margin: 5px 10px 5px 0;
 	padding: 1px 10px;
diff --git a/server.js b/server.js
index ef2684f..e08a1df 100644
--- a/server.js
+++ b/server.js
@@ -29,6 +29,7 @@ let session_store = new SQLiteStore();
 let db = new sqlite3(process.env.DATABASE || "./db");
 db.pragma("journal_mode = WAL");
 db.pragma("synchronous = NORMAL");
+db.pragma("foreign_keys = ON");
 
 let app = express();
 
@@ -88,6 +89,14 @@ io.use(passport_socket.authorize({
 
 app.use(express.static('public'));
 
+/*
+ * MISC FUNCTIONS
+ */
+
+function SQL(s) {
+	return db.prepare(s);
+}
+
 function LOG(req, ...msg) {
 	let name;
 	if (req.isAuthenticated())
@@ -122,30 +131,6 @@ function human_date(time) {
 	return date.toISOString().substring(0,10);
 }
 
-function humanize(rows) {
-	for (let row of rows) {
-		row.ctime = human_date(row.ctime);
-		row.mtime = human_date(row.mtime);
-	}
-}
-
-function linkify_player_names(games) {
-	for (let i = 0; i < games.length; ++i) {
-		let game = games[i];
-		if (game.player_names) {
-			game.player_names = game.player_names
-				.split(", ")
-				.map(x => `<a href="/user/${x}">${x}</a>`)
-				.join(", ");
-		}
-	}
-}
-
-function humanize_one(row) {
-	row.ctime = human_date(row.ctime);
-	row.mtime = human_date(row.mtime);
-}
-
 function is_email(email) {
 	return email.match(/^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)*$/);
 }
@@ -182,42 +167,56 @@ function get_avatar(mail) {
 }
 
 /*
- * USER PROFILES
+ * USER AUTHENTICATION
  */
 
-const sql_blacklist_ip = db.prepare("SELECT COUNT(*) FROM blacklist_ip WHERE ip = ?").raw();
-const sql_blacklist_mail = db.prepare("SELECT COUNT(*) AS count FROM blacklist_mail WHERE ? LIKE mail").raw();
+const SQL_BLACKLIST_IP = SQL("SELECT EXISTS ( SELECT 1 FROM blacklist_ip WHERE ip=? )").pluck();
+const SQL_BLACKLIST_MAIL = SQL("SELECT EXISTS ( SELECT 1 FROM blacklist_mail WHERE ? LIKE mail )").pluck();
+
+const SQL_EXISTS_USER_NAME = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE name=? )").pluck();
+const SQL_EXISTS_USER_MAIL = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE mail=? )").pluck();
+
+const SQL_INSERT_USER = SQL("INSERT INTO users (name,mail,password,salt) VALUES (?,?,?,?) RETURNING user_id,name,mail,notify");
+
+const SQL_SELECT_USER = SQL("SELECT * FROM user_view WHERE user_id=?");
+const SQL_SELECT_USER_BY_NAME = SQL("SELECT * FROM user_view WHERE name=?");
+const SQL_SELECT_LOGIN_BY_MAIL = SQL("SELECT * FROM user_login_view WHERE mail=?");
+const SQL_SELECT_LOGIN_BY_NAME = SQL("SELECT * FROM user_login_view WHERE name=?");
+const SQL_SELECT_USER_PROFILE = SQL("SELECT * FROM user_profile_view WHERE name=?");
+
+const SQL_OFFLINE_USER = SQL("SELECT * FROM user_view NATURAL JOIN user_last_seen WHERE user_id=? AND datetime('now') > datetime(atime,?)");
+
+const SQL_UPDATE_USER_NOTIFY = SQL("UPDATE users SET notify=? WHERE user_id=?");
+const SQL_UPDATE_USER_NAME = SQL("UPDATE users SET name=? WHERE user_id=?");
+const SQL_UPDATE_USER_MAIL = SQL("UPDATE users SET mail=? WHERE user_id=?");
+const SQL_UPDATE_USER_ABOUT = SQL("UPDATE users SET about=? WHERE user_id=?");
+const SQL_UPDATE_USER_PASSWORD = SQL("UPDATE users SET password=? WHERE user_id=?");
+const SQL_UPDATE_USER_LAST_SEEN = SQL("INSERT OR REPLACE INTO user_last_seen (user_id,atime,aip) VALUES (?,datetime('now'),?)");
+
+const SQL_FIND_TOKEN = SQL("SELECT token FROM tokens WHERE user_id=? AND datetime('now') < datetime(time, '+5 minutes')").pluck();
+const SQL_CREATE_TOKEN = SQL("INSERT OR REPLACE INTO tokens (user_id,token,time) VALUES (?, lower(hex(randomblob(16))), datetime('now')) RETURNING token").pluck();
+const SQL_VERIFY_TOKEN = SQL("SELECT EXISTS ( SELECT 1 FROM tokens WHERE user_id=? AND datetime('now') < datetime(time, '+20 minutes') AND token=? )").pluck();
+
+const SQL_COUNT_INBOX = SQL("SELECT COUNT(*) FROM messages WHERE to_id=? AND read=0 AND deleted_from_inbox=0").pluck();
 
 function is_blacklisted(ip, mail) {
-	if (sql_blacklist_ip.get(ip)[0] !== 0)
+	if (SQL_BLACKLIST_IP.get(ip) === 1)
 		return true;
-	if (sql_blacklist_mail.get(mail)[0] !== 0)
+	if (SQL_BLACKLIST_MAIL.get(mail) === 1)
 		return true;
 	return false;
 }
 
-const sql_deserialize_user = db.prepare("SELECT user_id, name, mail, notifications FROM users WHERE user_id = ?");
-const sql_update_last_seen = db.prepare("UPDATE users SET aip = ?, atime = datetime('now') WHERE user_id = ?");
-const sql_login_select = db.prepare("SELECT user_id, name, mail, password, salt FROM users WHERE name = ? OR mail = ?");
-
-const sql_subscribe = db.prepare("UPDATE users SET notifications = 1 WHERE user_id = ?");
-const sql_unsubscribe = db.prepare("UPDATE users SET notifications = 0 WHERE user_id = ?");
-
-const sql_count_unread_messages = db.prepare("SELECT COUNT(*) FROM messages WHERE to_id = ? AND read = 0 AND deleted_from_inbox = 0").pluck();
-const sql_fetch_user_by_name = db.prepare("SELECT * FROM users WHERE user_id = ? OR name = ?");
-const sql_fetch_user_by_id = db.prepare("SELECT * FROM users WHERE user_id = ?");
-
 passport.serializeUser(function (user, done) {
 	return done(null, user.user_id);
 });
 
 passport.deserializeUser(function (user_id, done) {
 	try {
-		let row = sql_deserialize_user.get(user_id);
-		if (!row)
+		let user = SQL_SELECT_USER.get(user_id);
+		if (!user)
 			return done(null, false);
-		row.unread = () => sql_count_unread_messages.get(user_id);
-		return done(null, row);
+		return done(null, user);
 	} catch (err) {
 		console.log(err);
 		return done(null, false);
@@ -229,25 +228,22 @@ function local_login(req, name_or_mail, password, done) {
 		if (!is_email(name_or_mail))
 			name_or_mail = clean_user_name(name_or_mail);
 		LOG(req, "POST /login", name_or_mail);
-		let row = sql_login_select.get(name_or_mail, name_or_mail);
-		if (!row)
+		let user = SQL_SELECT_LOGIN_BY_NAME.get(name_or_mail);
+		if (!user)
+			user = SQL_SELECT_LOGIN_BY_MAIL.get(name_or_mail);
+		if (!user)
 			return setTimeout(() => done(null, false, req.flash('message', "User not found.")), 1000);
-		if (is_blacklisted(req.connection.remoteAddress, row.mail))
+		if (is_blacklisted(req.connection.remoteAddress, user.mail))
 			return setTimeout(() => done(null, false, req.flash('message', "Sorry, but this IP or account has been banned.")), 1000);
-		let hash = hash_password(password, row.salt);
-		if (hash !== row.password)
+		let hash = hash_password(password, user.salt);
+		if (hash !== user.password)
 			return setTimeout(() => done(null, false, req.flash('message', "Wrong password.")), 1000);
-		sql_update_last_seen.run(req.connection.remoteAddress, row.user_id);
-		done(null, row);
+		done(null, user);
 	} catch (err) {
 		done(null, false, req.flash('message', err.toString()));
 	}
 }
 
-const sql_signup_check = db.prepare("SELECT user_id, name FROM users WHERE name = ? OR mail = ?");
-const sql_signup_insert = db.prepare("INSERT INTO users (name, mail, password, salt, ctime, cip, atime, aip, notifications) VALUES (?,?,?,?,datetime('now'),?,datetime('now'),?,0)");
-const sql_signup_login = db.prepare("SELECT user_id, name FROM users WHERE name = ? AND password = ?");
-
 function local_signup(req, name, password, done) {
 	try {
 		let mail = req.body.mail;
@@ -263,15 +259,14 @@ function local_signup(req, name, password, done) {
 			return done(null, false, req.flash('message', "Password is too long!"));
 		if (!is_email(mail))
 			return done(null, false, req.flash('message', "Invalid mail address!"));
-		let row = sql_signup_check.get(name, mail);
-		if (row)
-			return done(null, false, req.flash('message', "User name or mail is already taken."));
+		if (SQL_EXISTS_USER_NAME.get(name))
+			return done(null, false, req.flash('message', "That name is already taken."));
+		if (SQL_EXISTS_USER_MAIL.get(mail))
+			return done(null, false, req.flash('message', "That mail is already taken."));
 		let salt = crypto.randomBytes(32).toString('hex');
 		let hash = hash_password(password, salt);
-		let ip = req.connection.remoteAddress;
-		sql_signup_insert.run(name, mail, hash, salt, ip, ip);
-		row = sql_signup_login.get(name, hash);
-		done(null, row);
+		let user = SQL_INSERT_USER.get(name, mail, hash, salt);
+		done(null, user);
 	} catch (err) {
 		done(null, false, req.flash('message', err.toString()));
 	}
@@ -283,24 +278,46 @@ passport.use('local-signup', new passport_local.Strategy({ passReqToCallback: tr
 app.use(passport.initialize());
 app.use(passport.session());
 
-function update_last_seen(req) {
-	sql_update_last_seen.run(req.connection.remoteAddress, req.user.user_id);
+function touch_user(req) {
+	req.user.unread = SQL_COUNT_INBOX.get(req.user.user_id);
+	SQL_UPDATE_USER_LAST_SEEN.run(req.user.user_id, req.connection.remoteAddress);
+}
+
+function must_not_be_logged_in(req, res, next) {
+	if (SQL_BLACKLIST_IP.get(req.connection.remoteAddress) === 1)
+		return res.redirect('/banned');
+	return next();
 }
 
 function must_be_logged_in(req, res, next) {
+	if (SQL_BLACKLIST_IP.get(req.connection.remoteAddress) === 1)
+		return res.redirect('/banned');
 	if (!req.isAuthenticated()) {
 		req.session.redirect = req.originalUrl;
 		return res.redirect('/login');
 	}
-	if (sql_blacklist_ip.get(req.connection.remoteAddress)[0] !== 0)
-		return res.redirect('/banned');
-	if (sql_blacklist_mail.get(req.user.mail)[0] !== 0)
+	touch_user(req);
+	return next();
+}
+
+function may_be_logged_in(req, res, next) {
+	if (SQL_BLACKLIST_IP.get(req.connection.remoteAddress) === 1)
 		return res.redirect('/banned');
-	update_last_seen(req);
+	if (req.isAuthenticated())
+		touch_user(req);
 	return next();
 }
 
-app.get('/about', function (req, res) {
+app.get('/', may_be_logged_in, function (req, res) {
+	res.render('index.ejs', { user: req.user, flash: req.flash('message') });
+});
+
+app.get('/banned', function (req, res) {
+	LOG(req, "GET /banned");
+	res.render('banned.ejs', { user: req.user });
+});
+
+app.get('/about', may_be_logged_in, function (req, res) {
 	res.render('about.ejs', { user: req.user });
 });
 
@@ -310,19 +327,18 @@ app.get('/logout', function (req, res) {
 	res.redirect('/login');
 });
 
-app.get('/banned', function (req, res) {
-	LOG(req, "GET /banned");
-	res.render('banned.ejs', { user: req.user, message: req.flash('message') });
-});
-
 app.get('/login', function (req, res) {
+	if (req.isAuthenticated())
+		return res.redirect('/');
 	LOG(req, "GET /login");
-	res.render('login.ejs', { user: req.user, message: req.flash('message') });
+	res.render('login.ejs', { user: req.user, flash: req.flash('message') });
 });
 
 app.get('/signup', function (req, res) {
+	if (req.isAuthenticated())
+		return res.redirect('/');
 	LOG(req, "GET /signup");
-	res.render('signup.ejs', { user: req.user, message: req.flash('message') });
+	res.render('signup.ejs', { user: req.user, flash: req.flash('message') });
 });
 
 app.post('/login',
@@ -345,395 +361,666 @@ app.post('/signup',
 	})
 );
 
+app.get('/forgot_password', must_not_be_logged_in, function (req, res) {
+	LOG(req, "GET /forgot_password");
+	res.render('forgot_password.ejs', { user: req.user, flash: req.flash('message') });
+});
+
+app.post('/forgot_password', must_not_be_logged_in, function (req, res) {
+	LOG(req, "POST /forgot_password");
+	let mail = req.body.mail;
+	let user = SQL_SELECT_LOGIN_BY_MAIL.get(mail);
+	if (user) {
+		let token = SQL_FIND_TOKEN.get(user.user_id);
+		if (!token) {
+			token = SQL_CREATE_TOKEN.run(user.user_id);
+			mail_password_reset_token(user, token);
+		}
+		req.flash('message', "A password reset token has been sent to " + mail + ".");
+		return res.redirect('/reset_password/' + mail);
+	}
+	req.flash('message', "User not found.");
+	return res.redirect('/forgot_password');
+});
+
+app.get('/reset_password', must_not_be_logged_in, function (req, res) {
+	LOG(req, "GET /reset_password");
+	res.render('reset_password.ejs', { user: null, mail: "", token: "", flash: req.flash('message') });
+});
+
+app.get('/reset_password/:mail', must_not_be_logged_in, function (req, res) {
+	let mail = req.params.mail;
+	LOG(req, "GET /reset_password", mail);
+	res.render('reset_password.ejs', { user: null, mail: mail, token: "", flash: req.flash('message') });
+});
+
+app.get('/reset_password/:mail/:token', must_not_be_logged_in, function (req, res) {
+	let mail = req.params.mail;
+	let token = req.params.token;
+	LOG(req, "GET /reset_password", mail, token);
+	res.render('reset_password.ejs', { user: null, mail: mail, token: token, flash: req.flash('message') });
+});
+
+app.post('/reset_password', must_not_be_logged_in, function (req, res) {
+	let mail = req.body.mail;
+	let token = req.body.token;
+	let password = req.body.password;
+	LOG(req, "POST /reset_password", mail, token);
+	let user = SQL_SELECT_LOGIN_BY_MAIL.get(mail);
+	if (!user) {
+		req.flash('message', "User not found.");
+		return res.redirect('/reset_password/'+mail+'/'+token);
+	}
+	if (password.length < 4) {
+		req.flash('message', "Password is too short!");
+		return res.redirect('/reset_password/'+mail+'/'+token);
+	}
+	if (!SQL_VERIFY_TOKEN.get(user.user_id, token)) {
+		req.flash('message', "Invalid or expired token!");
+		return res.redirect('/reset_password/'+mail);
+	}
+	let hash = hash_password(password, user.salt);
+	SQL_UPDATE_USER_PASSWORD.run(hash, user.user_id);
+	return res.redirect('/login');
+});
+
 app.get('/change_password', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /change_password");
-	res.render('change_password.ejs', { user: req.user, message: req.flash('message') });
+	res.render('change_password.ejs', { user: req.user, flash: req.flash('message') });
+});
+
+app.post('/change_password', must_be_logged_in, function (req, res) {
+	let oldpass = req.body.password;
+	let newpass = req.body.newpass;
+	LOG(req, "POST /change_password", req.user.name);
+	// Get full user record including password and salt
+	let user = SQL_SELECT_LOGIN_BY_MAIL.get(req.user.mail);
+	if (newpass.length < 4) {
+		req.flash('message', "Password is too short!");
+		return res.redirect('/change_password');
+	}
+	let oldhash = hash_password(oldpass, user.salt);
+	if (oldhash !== user.password) {
+		req.flash('message', "Wrong password.");
+		return res.redirect('/change_password');
+	}
+	let newhash = hash_password(newpass, user.salt);
+	SQL_UPDATE_USER_PASSWORD.run(newhash, user.user_id);
+	req.flash('message', "Your password has been updated.");
+	return res.redirect('/profile');
+});
+
+/*
+ * USER PROFILE
+ */
+
+app.get('/subscribe', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /subscribe");
+	SQL_UPDATE_USER_NOTIFY.run(1, req.user.user_id);
+	res.redirect('/profile');
+});
+
+app.get('/unsubscribe', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /unsubscribe");
+	SQL_UPDATE_USER_NOTIFY.run(0, req.user.user_id);
+	res.redirect('/profile');
 });
 
 app.get('/change_name', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /change_name");
-	res.render('change_name.ejs', { user: req.user, message: req.flash('message') });
+	res.render('change_name.ejs', { user: req.user, flash: req.flash('message') });
+});
+
+app.post('/change_name', must_be_logged_in, function (req, res) {
+	let newname = clean_user_name(req.body.newname);
+	LOG(req, "POST /change_name", req.user, req.body, newname);
+	if (!is_valid_user_name(newname)) {
+		req.flash('message', "Invalid user name!");
+		return res.redirect('/change_name');
+	}
+	if (SQL_EXISTS_USER_NAME.get(newname)) {
+		req.flash('message', "That name is already taken!");
+		return res.redirect('/change_name');
+	}
+	SQL_UPDATE_USER_NAME.run(newname, req.user.user_id);
+	return res.redirect('/profile');
 });
 
 app.get('/change_mail', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /change_mail");
-	res.render('change_mail.ejs', { user: req.user, message: req.flash('message') });
+	res.render('change_mail.ejs', { user: req.user, flash: req.flash('message') });
+});
+
+app.post('/change_mail', must_be_logged_in, function (req, res) {
+	let newmail = req.body.newmail;
+	LOG(req, "POST /change_mail", req.user, req.body);
+	if (!is_email(newmail)) {
+		req.flash('message', "Invalid mail address!");
+		return res.redirect('/change_mail');
+	}
+	if (SQL_EXISTS_USER_MAIL.get(newmail)) {
+		req.flash('message', "That mail address is already taken!");
+		return res.redirect('/change_mail');
+	}
+	SQL_UPDATE_USER_MAIL.run(newmail, req.user.user_id);
+	return res.redirect('/profile');
 });
 
 app.get('/change_about', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /change_about");
-	let about = sql_fetch_user_by_id.get(req.user.user_id).about;
-	res.render('change_about.ejs', { user: req.user, about: about || "", message: req.flash('message') });
+	let about = SQL_SELECT_USER_PROFILE.get(req.user.name).about;
+	res.render('change_about.ejs', { user: req.user, about: about || "" });
 });
 
-app.get('/subscribe', must_be_logged_in, function (req, res) {
-	LOG(req, "GET /subscribe");
-	sql_subscribe.run(req.user.user_id);
-	res.redirect('/profile');
+app.post('/change_about', must_be_logged_in, function (req, res) {
+	LOG(req, "POST /change_about", req.user.name);
+	SQL_UPDATE_USER_ABOUT.run(req.body.about, req.user.user_id);
+	return res.redirect('/profile');
 });
 
-app.get('/unsubscribe', must_be_logged_in, function (req, res) {
-	LOG(req, "GET /unsubscribe");
-	sql_unsubscribe.run(req.user.user_id);
-	res.redirect('/profile');
+app.get('/user/:who_name', may_be_logged_in, function (req, res) {
+	LOG(req, "GET /user/" + req.params.who_name);
+	let who = SQL_SELECT_USER_PROFILE.get(req.params.who_name);
+	if (who) {
+		who.avatar = get_avatar(who.mail);
+		who.ctime = human_date(who.ctime);
+		who.atime = human_date(who.atime);
+		res.render('user.ejs', { user: req.user, who: who });
+	} else {
+		return res.status(404).send("Invalid user name.");
+	}
+});
+
+app.get('/users', may_be_logged_in, function (req, res) {
+	LOG(req, "GET /users");
+	let rows = db.prepare("SELECT * FROM user_profile_view ORDER BY atime DESC").all();
+	rows.forEach(row => {
+		row.avatar = get_avatar(row.mail);
+		row.ctime = human_date(row.ctime);
+		row.atime = human_date(row.atime);
+	});
+	res.render('users.ejs', { user: req.user, userList: rows });
 });
 
 /*
- * FORGOT AND CHANGE PASSWORD
+ * MESSAGES
  */
 
-const sql_select_salt = db.prepare("SELECT salt FROM users WHERE user_id = ?").pluck();
-const sql_find_user_by_mail = db.prepare("SELECT * FROM users WHERE mail = ?");
-
-const sql_find_token = db.prepare(`
-	SELECT token FROM tokens WHERE user_id = ? AND datetime('now') < datetime(time, '+5 minutes')
-	`).pluck();
-const sql_verify_token = db.prepare(`
-	SELECT COUNT(*) FROM tokens WHERE user_id = ? AND datetime('now') < datetime(time, '+20 minutes') AND token = ?
-	`).pluck();
-const sql_create_token = db.prepare(`
-	INSERT OR REPLACE INTO tokens VALUES ( ?, lower(hex(randomblob(16))), datetime('now') )
-	`);
+const MESSAGE_LIST_INBOX = db.prepare(`
+	SELECT message_id, from_name, subject, time, read
+	FROM message_view
+	WHERE to_id=? AND deleted_from_inbox=0
+	ORDER BY message_id DESC`);
 
-app.get('/forgot_password', function (req, res) {
-	LOG(req, "GET /forgot_password");
-	res.render('forgot_password.ejs', { user: req.user, message: req.flash('message') });
+const MESSAGE_LIST_OUTBOX = db.prepare(`
+	SELECT message_id, to_name, subject, time, 1 as read
+	FROM message_view
+	WHERE from_id=? AND deleted_from_outbox=0
+	ORDER BY message_id DESC`);
+
+const MESSAGE_FETCH = db.prepare("SELECT * FROM message_view WHERE message_id=? AND ( from_id=? OR to_id=? )");
+const MESSAGE_SEND = db.prepare("INSERT INTO messages (from_id,to_id,subject,body) VALUES (?,?,?,?)");
+const MESSAGE_MARK_READ = db.prepare("UPDATE messages SET read=1 WHERE message_id=? AND read = 0");
+const MESSAGE_DELETE_INBOX = db.prepare("UPDATE messages SET deleted_from_inbox=1 WHERE message_id=? AND to_id=?");
+const MESSAGE_DELETE_OUTBOX = db.prepare("UPDATE messages SET deleted_from_outbox=1 WHERE message_id=? AND from_id=?");
+const MESSAGE_DELETE_ALL_OUTBOX = db.prepare("UPDATE messages SET deleted_from_outbox=1 WHERE from_id=?");
+
+app.get('/inbox', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /inbox");
+	let messages = MESSAGE_LIST_INBOX.all(req.user.user_id);
+	for (let i = 0; i < messages.length; ++i)
+		messages[i].time = human_date(messages[i].time);
+	res.set("Cache-Control", "no-store");
+	res.render('message_inbox.ejs', {
+		user: req.user,
+		messages: messages,
+	});
 });
 
-app.get('/reset_password', function (req, res) {
-	LOG(req, "GET /reset_password");
-	res.render('reset_password.ejs', { user: null, mail: "", token: "", message: req.flash('message') });
+app.get('/outbox', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /outbox");
+	let messages = MESSAGE_LIST_OUTBOX.all(req.user.user_id);
+	for (let i = 0; i < messages.length; ++i)
+		messages[i].time = human_date(messages[i].time);
+	res.set("Cache-Control", "no-store");
+	res.render('message_outbox.ejs', {
+		user: req.user,
+		messages: messages,
+	});
 });
 
-app.get('/reset_password/:mail', function (req, res) {
-	let mail = req.params.mail;
-	LOG(req, "GET /reset_password", mail);
-	res.render('reset_password.ejs', { user: null, mail: mail, token: "", message: req.flash('message') });
+app.get('/message/read/:message_id', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /message/" + req.params.message_id);
+	let message_id = req.params.message_id | 0;
+	let message = MESSAGE_FETCH.get(message_id, req.user.user_id, req.user.user_id);
+	if (!message)
+		return res.status(404).send("Invalid message ID.");
+	if (message.to_id === req.user.user_id && message.read === 0) {
+		MESSAGE_MARK_READ.run(message_id);
+		req.user.unread --;
+	}
+	message.time = human_date(message.time);
+	message.body = linkify_post(message.body);
+	res.render('message_read.ejs', {
+		user: req.user,
+		message: message,
+	});
 });
 
-app.get('/reset_password/:mail/:token', function (req, res) {
-	let mail = req.params.mail;
-	let token = req.params.token;
-	LOG(req, "GET /reset_password", mail, token);
-	res.render('reset_password.ejs', { user: null, mail: mail, token: token, message: req.flash('message') });
+app.get('/message/send', must_be_logged_in, function (req, res) {
+	res.render('message_send.ejs', {
+		user: req.user,
+		to_name: "",
+		subject: "",
+		body: "",
+	});
 });
 
-app.post('/forgot_password', function (req, res) {
-	LOG(req, "POST /forgot_password");
-	try {
-		if (sql_blacklist_ip.get(req.connection.remoteAddress)[0] !== 0)
-			return res.redirect('/banned');
-		let mail = req.body.mail;
-		let user = sql_find_user_by_mail.get(mail);
-		if (user) {
-			let token = sql_find_token.get(user.user_id);
-			if (!token) {
-				sql_create_token.run(user.user_id);
-				token = sql_find_token.get(user.user_id);
-				mail_password_reset_token(user, token);
-			}
-			req.flash('message', "A password reset token has been sent to " + mail + ".");
-			if (is_email(mail))
-				return res.redirect('/reset_password/' + mail);
-			return res.redirect('/reset_password/');
-		}
-		req.flash('message', "User not found.");
-		return res.redirect('/forgot_password');
-	} catch (err) {
-		console.log(err);
-		req.flash('message', err.message);
-		return res.redirect('/forgot_password');
-	}
+app.get('/message/send/:to_name', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /message/send/" + req.params.to_name);
+	let to_name = req.params.to_name;
+	res.render('message_send.ejs', {
+		user: req.user,
+		to_name: to_name,
+		subject: "",
+		body: "",
+	});
 });
 
-app.post('/reset_password', function (req, res) {
-	let mail = req.body.mail;
-	let token = req.body.token;
-	let password = req.body.password;
-	try {
-		LOG(req, "POST /reset_password", mail, token);
-		let user = sql_find_user_by_mail.get(mail);
-		if (!user) {
-			req.flash('message', "User not found.");
-			return res.redirect('/reset_password/'+mail+'/'+token);
-		}
-		if (password.length < 4) {
-			req.flash('message', "Password is too short!");
-			return res.redirect('/reset_password/'+mail+'/'+token);
-		}
-		if (!sql_verify_token.get(user.user_id, token)) {
-			req.flash('message', "Invalid or expired token!");
-			return res.redirect('/reset_password/'+mail);
-		}
-		let salt = sql_select_salt.get(user.user_id);
-		if (!salt) {
-			req.flash('message', "User not found.");
-			return res.redirect('/reset_password/'+mail+'/'+token);
-		}
-		let hash = hash_password(password, salt);
-		db.prepare("UPDATE users SET password = ? WHERE user_id = ?").run(hash, user.user_id);
-		req.flash('message', "Your password has been updated.");
-		return res.redirect('/login');
-	} catch (err) {
-		console.log(err);
-		req.flash('message', err.message);
-		return res.redirect('/reset_password/'+mail+'/'+token);
+app.post('/message/send', must_be_logged_in, function (req, res) {
+	LOG(req, "POST /message/send/");
+	let to_name = req.body.to.trim();
+	let subject = req.body.subject.trim();
+	let body = req.body.body.trim();
+	let to_user = SQL_SELECT_USER_BY_NAME.get(to_name);
+	if (!to_user) {
+		return res.render('message_send.ejs', {
+			user: req.user,
+			to_id: 0,
+			to_name: to_name,
+			subject: subject,
+			body: body,
+			flash: "Cannot find that user."
+		});
 	}
+	let info = MESSAGE_SEND.run(req.user.user_id, to_user.user_id, subject, body);
+	if (to_user.notify)
+		mail_new_message(to_user, info.lastInsertRowid, req.user.name, subject, body)
+	res.redirect('/inbox');
 });
 
-app.post('/change_password', must_be_logged_in, function (req, res) {
-	try {
-		let name = req.user.name;
-		let password = req.body.password;
-		let newpass = req.body.newpass;
-		LOG(req, "POST /change_password", name);
-		if (newpass.length < 4) {
-			req.flash('message', "Password is too short!");
-			return res.redirect('/change_password');
-		}
-		let salt = sql_select_salt.get(req.user.user_id);
-		if (!salt) {
-			req.flash('message', "User not found.");
-			return res.redirect('/change_password');
-		}
-		let hash = hash_password(password, salt);
-		let user_row = db.prepare("SELECT user_id, name FROM users WHERE name = ? AND password = ?").get(name, hash);
-		if (!user_row) {
-			req.flash('message', "Wrong password.");
-			return res.redirect('/change_password');
-		}
-		hash = hash_password(newpass, salt);
-		db.prepare("UPDATE users SET password = ? WHERE user_id = ?").run(hash, user_row.user_id);
-		req.flash('message', "Your password has been updated.");
-		return res.redirect('/profile');
-	} catch (err) {
-		console.log(err);
-		req.flash('message', err.message);
-		return res.redirect('/change_password');
-	}
+function quote_body(message) {
+	let when = new Date(message.time).toDateString();
+	let who = message.from_name;
+	let what = message.body.split("\n").join("\n> ");
+	return "\n\n" + "On " + when + " " + who + " wrote:\n> " + what + "\n";
+}
+
+app.get('/message/reply/:message_id', must_be_logged_in, function (req, res) {
+	LOG(req, "POST /message/reply/" + req.params.message_id);
+	let message_id = req.params.message_id | 0;
+	let message = MESSAGE_FETCH.get(message_id, req.user.user_id, req.user.user_id);
+	if (!message)
+		return res.status(404).send("Invalid message ID.");
+	return res.render('message_send.ejs', {
+		user: req.user,
+		to_id: message.from_id,
+		to_name: message.from_name,
+		subject: message.subject.startsWith("Re: ") ? message.subject : "Re: " + message.subject,
+		body: quote_body(message),
+	});
 });
 
-const sql_is_name_taken = db.prepare("SELECT EXISTS ( SELECT 1 FROM users WHERE name = ? )").pluck();
-const sql_change_name = db.prepare("UPDATE users SET name = ? WHERE user_id = ?");
+app.get('/message/delete/:message_id', must_be_logged_in, function (req, res) {
+	LOG(req, "POST /message/delete/" + req.params.message_id);
+	let message_id = req.params.message_id | 0;
+	MESSAGE_DELETE_INBOX.run(message_id, req.user.user_id);
+	MESSAGE_DELETE_OUTBOX.run(message_id, req.user.user_id);
+	res.redirect('/inbox');
+});
 
-const sql_is_mail_taken = db.prepare("SELECT EXISTS ( SELECT 1 FROM users WHERE mail = ? )").pluck();
-const sql_change_mail = db.prepare("UPDATE users SET mail = ? WHERE user_id = ?");
-const sql_change_about = db.prepare("UPDATE users SET about = ? WHERE user_id = ?");
+app.get('/outbox/delete', must_be_logged_in, function (req, res) {
+	LOG(req, "POST /outbox/delete");
+	MESSAGE_DELETE_ALL_OUTBOX.run(req.user.user_id);
+	res.redirect('/outbox');
+});
 
-app.post('/change_name', must_be_logged_in, function (req, res) {
-	try {
-		let newname = clean_user_name(req.body.newname);
-		LOG(req, "POST /change_name", req.user, req.body, newname);
-		if (!is_valid_user_name(newname)) {
-			req.flash('message', "Invalid user name!");
-			return res.redirect('/change_name');
-		}
-		if (sql_is_name_taken.get(newname)) {
-			req.flash('message', "That name is already taken!");
-			return res.redirect('/change_name');
-		}
-		sql_change_name.run(newname, req.user.user_id);
-		req.flash('message', "Your name has been changed.");
-		return res.redirect('/profile');
-	} catch (err) {
-		console.log(err);
-		req.flash('message', err.message);
-		return res.redirect('/change_name');
+/*
+ * FORUM
+ */
+
+const FORUM_PAGE_SIZE = 15;
+
+const FORUM_COUNT_THREADS = db.prepare("SELECT COUNT(*) FROM threads").pluck();
+const FORUM_LIST_THREADS = db.prepare("SELECT * FROM thread_view ORDER BY mtime DESC LIMIT ? OFFSET ?");
+const FORUM_GET_THREAD = db.prepare("SELECT * FROM thread_view WHERE thread_id=?");
+const FORUM_LIST_POSTS = db.prepare("SELECT * FROM post_view WHERE thread_id=?");
+const FORUM_GET_POST = db.prepare("SELECT * FROM post_view WHERE post_id=?");
+const FORUM_NEW_THREAD = db.prepare("INSERT INTO threads (author_id,subject) VALUES (?,?)");
+const FORUM_NEW_POST = db.prepare("INSERT INTO posts (thread_id,author_id,body) VALUES (?,?,?)");
+const FORUM_EDIT_POST = db.prepare("UPDATE posts SET body=?, mtime=datetime('now') WHERE post_id=? AND author_id=? RETURNING thread_id").pluck();
+
+function show_forum_page(req, res, page) {
+	let thread_count = FORUM_COUNT_THREADS.get();
+	let page_count = Math.ceil(thread_count / FORUM_PAGE_SIZE);
+	let threads = FORUM_LIST_THREADS.all(FORUM_PAGE_SIZE, FORUM_PAGE_SIZE * (page - 1));
+	for (let thread of threads) {
+		thread.ctime = human_date(thread.ctime);
+		thread.mtime = human_date(thread.mtime);
 	}
+	res.set("Cache-Control", "no-store");
+	res.render('forum_view.ejs', {
+		user: req.user,
+		threads: threads,
+		current_page: page,
+		page_count: page_count,
+	});
+}
+
+function linkify_post(text) {
+	text = text.replace(/&/g, "&amp;").replace(/</g, "&lt;").replace(/>/g, "&gt;");
+	text = text.replace(/https?:\/\/\S+/, (match) => {
+		if (match.endsWith(".jpg") || match.endsWith(".png") || match.endsWith(".svg"))
+			return `<a href="${match}"><img src="${match}"></a>`;
+		return `<a href="${match}">${match}</a>`;
+	});
+	return text;
+}
+
+app.get('/forum', may_be_logged_in, function (req, res) {
+	LOG(req, "GET /forum");
+	show_forum_page(req, res, 1);
 });
 
-app.post('/change_mail', must_be_logged_in, function (req, res) {
-	try {
-		let newmail = req.body.newmail;
-		LOG(req, "POST /change_mail", req.user, req.body);
-		if (!is_email(newmail)) {
-			req.flash('message', "Invalid mail address!");
-			return res.redirect('/change_mail');
-		}
-		if (sql_is_mail_taken.get(newmail)) {
-			req.flash('message', "That mail address is already taken!");
-			return res.redirect('/change_mail');
-		}
-		sql_change_mail.run(newmail, req.user.user_id);
-		req.flash('message', "Your mail address has been changed.");
-		return res.redirect('/profile');
-	} catch (err) {
-		console.log(err);
-		req.flash('message', err.message);
-		return res.redirect('/change_mail');
-	}
+app.get('/forum/page/:page', may_be_logged_in, function (req, res) {
+	LOG(req, "GET /forum/page/" + req.params.page);
+	show_forum_page(req, res, req.params.page | 0);
 });
 
-app.post('/change_about', must_be_logged_in, function (req, res) {
-	try {
-		LOG(req, "POST /change_about", req.user.name);
-		sql_change_about.run(req.body.about, req.user.user_id);
-		return res.redirect('/profile');
-	} catch (err) {
-		console.log(err);
-		req.flash('message', err.message);
-		return res.redirect('/change_about');
+app.get('/forum/thread/:thread_id', may_be_logged_in, function (req, res) {
+	LOG(req, "GET /forum/thread/" + req.params.thread_id);
+	let thread_id = req.params.thread_id | 0;
+	let thread = FORUM_GET_THREAD.get(thread_id);
+	let posts = FORUM_LIST_POSTS.all(thread_id);
+	if (!thread)
+		return res.status(404).send("Invalid thread ID.");
+	for (let i = 0; i < posts.length; ++i) {
+		posts[i].body = linkify_post(posts[i].body);
+		posts[i].edited = posts[i].mtime !== posts[i].ctime;
+		posts[i].ctime = human_date(posts[i].ctime);
+		posts[i].mtime = human_date(posts[i].mtime);
 	}
+	res.set("Cache-Control", "no-store");
+	res.render('forum_thread.ejs', {
+		user: req.user,
+		thread: thread,
+		posts: posts,
+	});
+});
+
+app.get('/forum/post', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /forum/post");
+	res.render('forum_post.ejs', {
+		user: req.user,
+	});
+});
+
+app.post('/forum/post', must_be_logged_in, function (req, res) {
+	LOG(req, "POST /forum/post");
+	let user_id = req.user.user_id;
+	let subject = req.body.subject.trim();
+	let body = req.body.body;
+	if (subject.length === 0)
+		subject = "Untitled";
+	let thread_id = FORUM_NEW_THREAD.run(user_id, subject).lastInsertRowid;
+	FORUM_NEW_POST.run(thread_id, user_id, body);
+	res.redirect('/forum/thread/'+thread_id);
+});
+
+app.get('/forum/edit/:post_id', must_be_logged_in, function (req, res) {
+	// TODO: edit subject if editing first post
+	LOG(req, "GET /forum/edit/" + req.params.post_id);
+	let post_id = req.params.post_id | 0;
+	let post = FORUM_GET_POST.get(post_id);
+	if (!post || post.author_id != req.user.user_id)
+		return res.status(404).send("Invalid post ID.");
+	post.ctime = human_date(post.ctime);
+	post.mtime = human_date(post.mtime);
+	res.render('forum_edit.ejs', {
+		user: req.user,
+		post: post,
+	});
+});
+
+app.post('/forum/edit/:post_id', must_be_logged_in, function (req, res) {
+	LOG(req, "POST /forum/edit/" + req.params.post_id);
+	let user_id = req.user.user_id;
+	let post_id = req.params.post_id | 0;
+	let body = req.body.body;
+	let thread_id = FORUM_EDIT_POST.get(body, post_id, user_id);
+	res.redirect('/forum/thread/'+thread_id);
+});
+
+app.get('/forum/reply/:post_id', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /forum/reply/" + req.params.post_id);
+	let post_id = req.params.post_id | 0;
+	let post = FORUM_GET_POST.get(post_id);
+	if (!post)
+		return res.status(404).send("Invalid post ID.");
+	let thread = FORUM_GET_THREAD.get(post.thread_id);
+	post.body = linkify_post(post.body);
+	post.edited = post.mtime !== post.ctime;
+	post.ctime = human_date(post.ctime);
+	post.mtime = human_date(post.mtime);
+	res.render('forum_reply.ejs', {
+		user: req.user,
+		thread: thread,
+		post: post,
+	});
+});
+
+app.post('/forum/reply/:thread_id', must_be_logged_in, function (req, res) {
+	LOG(req, "POST /forum/reply/" + req.params.thread_id);
+	let thread_id = req.params.thread_id | 0;
+	let user_id = req.user.user_id;
+	let body = req.body.body;
+	FORUM_NEW_POST.run(thread_id, user_id, body);
+	res.redirect('/forum/thread/'+thread_id);
 });
 
 /*
  * GAME LOBBY
  */
 
-const QUERY_LIST_GAMES_OF_TITLE = db.prepare(`
-	SELECT *,
-		EXISTS (
-			SELECT 1 FROM players
-			WHERE players.game_id = game_view.game_id
-			AND user_id = $user_id
-			AND active_role IN ( 'All', 'Both', role )
-		) AS is_your_turn
-	FROM game_view
-	WHERE title_id = $title_id AND private = 0
-		AND EXISTS (
-			SELECT 1 FROM players
-			WHERE players.game_id = game_view.game_id
-			AND user_id = game_view.owner_id
-		)
-	ORDER BY status ASC, mtime DESC
-`);
+let TITLES = {};
+let RULES = {};
+let ROLES = {};
 
-const QUERY_LIST_GAMES_OF_USER = db.prepare(`
-	SELECT *,
-		EXISTS (
-			SELECT 1 FROM players
-			WHERE players.game_id = game_view.game_id
-			AND user_id = $user_id
-			AND active_role IN ( 'All', 'Both', role )
-		) AS is_your_turn
-	FROM game_view
-	WHERE owner_id = $user_id
-		OR EXISTS (
-			SELECT 1 FROM players
-			WHERE players.game_id = game_view.game_id
-			AND user_id = $user_id
-		)
-	ORDER BY status ASC, mtime DESC
-`);
+function load_rules() {
+	const SQL_SELECT_TITLES = SQL("SELECT * FROM titles");
+	const SQL_SELECT_TITLE_ROLES = SQL("SELECT role FROM roles WHERE title_id=?").pluck();
+	for (let title of SQL_SELECT_TITLES.all()) {
+		let title_id = title.title_id;
+		if (fs.existsSync(__dirname + "/public/" + title_id + "/rules.js")) {
+			console.log("Loading rules for " + title_id);
+			try {
+				TITLES[title_id] = title;
+				RULES[title_id] = require("./public/" + title_id + "/rules.js");
+				ROLES[title_id] = SQL_SELECT_TITLE_ROLES.all(title_id);
+			} catch (err) {
+				console.log(err);
+			}
+		} else {
+			console.log("Cannot find rules for " + title_id);
+		}
+	}
+}
 
-const QUERY_PLAYERS = db.prepare("SELECT role, user_id, user_name FROM player_view WHERE game_id = ?");
-const QUERY_PLAYERS_FULL = db.prepare(`
-	SELECT
-		players.user_id,
-		players.role,
-		users.name,
-		users.mail,
-		users.notifications
-	FROM players
-	JOIN users ON players.user_id = users.user_id
-	WHERE players.game_id = ?
-`);
+load_rules();
 
-const QUERY_GAME = db.prepare("SELECT * FROM game_view WHERE game_id = ?");
-const QUERY_TITLE = db.prepare("SELECT * FROM titles WHERE title_id = ?");
-const QUERY_ROLES = db.prepare("SELECT role FROM roles WHERE title_id = ? ORDER BY rowid").pluck();
-const QUERY_GAME_OWNER = db.prepare("SELECT * FROM games WHERE game_id = ? AND owner_id = ?");
-const QUERY_TITLE_FROM_GAME = db.prepare("SELECT title_id FROM games WHERE game_id = ?").pluck();
-const QUERY_ROLE_FROM_GAME_AND_USER = db.prepare("SELECT role FROM players WHERE game_id = ? AND user_id = ?").pluck();
-const QUERY_IS_SOLO = db.prepare("SELECT COUNT(DISTINCT user_id) = 1 FROM players WHERE game_id = ?").pluck();
-const QUERY_IS_RANDOM = db.prepare("SELECT random FROM games WHERE game_id = ?").pluck();
-
-const QUERY_JOIN_GAME_TRY = db.prepare("INSERT OR IGNORE INTO players (user_id, game_id, role) VALUES (?,?,?)");
-const QUERY_JOIN_GAME = db.prepare("INSERT INTO players (user_id, game_id, role) VALUES (?,?,?)");
-const QUERY_PART_GAME = db.prepare("DELETE FROM players WHERE game_id = ? AND role = ?");
-const QUERY_START_GAME = db.prepare("UPDATE games SET status = 1, state = ?, active = ?, mtime = datetime('now') WHERE game_id = ?");
-const QUERY_CREATE_GAME = db.prepare(`
-	INSERT INTO games
-	(owner_id,title_id,scenario,options,private,random,ctime,mtime,description,status,state)
-	VALUES
-	(?,?,?,?,?,?,datetime('now'),datetime('now'),?,0,NULL)
-`);
-const QUERY_UPDATE_GAME_SET_PRIVATE = db.prepare("UPDATE games SET private = 1 WHERE game_id = ?");
-const QUERY_ASSIGN_ROLE = db.prepare("UPDATE players SET role = ? WHERE game_id = ? AND user_id = ? AND role = ?");
+const SQL_INSERT_GAME = SQL("INSERT INTO games (owner_id,title_id,scenario,options,private,random,description) VALUES (?,?,?,?,?,?,?)");
+const SQL_DELETE_GAME = SQL("DELETE FROM games WHERE game_id=? AND owner_id=?");
+
+const SQL_SELECT_GAME_CHAT = SQL("SELECT chat_id,time,name,message FROM game_chat_view WHERE game_id=? AND chat_id>?").raw();
+const SQL_INSERT_GAME_CHAT = SQL("INSERT INTO game_chat (game_id,user_id,message) VALUES (?,?,?) RETURNING chat_id,time,'',message").raw();
+
+const SQL_SELECT_GAME_STATE = SQL("SELECT state FROM game_state WHERE game_id=?").pluck();
+const SQL_UPDATE_GAME_STATE = SQL("INSERT OR REPLACE INTO game_state (game_id,state,active,mtime) VALUES (?,?,?,datetime('now'))");
+const SQL_UPDATE_GAME_RESULT = SQL("UPDATE games SET status=?, result=? WHERE game_id=?");
+const SQL_UPDATE_GAME_PRIVATE = SQL("UPDATE games SET private=1 WHERE game_id=?");
+const SQL_INSERT_REPLAY = SQL("INSERT INTO game_replay (game_id,role,action,arguments) VALUES (?,?,?,?)");
 
-const QUERY_COUNT_OPEN_GAMES = db.prepare("SELECT COUNT(*) FROM games WHERE owner_id = ? AND status = 0").pluck();
-const QUERY_DELETE_GAME = db.prepare("DELETE FROM games WHERE game_id = ?");
+const SQL_SELECT_GAME = SQL("SELECT * FROM games WHERE game_id=?");
+const SQL_SELECT_GAME_VIEW = SQL("SELECT * FROM game_view WHERE game_id=?");
+const SQL_SELECT_GAME_FULL_VIEW = SQL("SELECT * FROM game_full_view WHERE game_id=?");
+const SQL_SELECT_GAME_TITLE = SQL("SELECT title_id FROM games WHERE game_id=?").pluck();
+const SQL_SELECT_GAME_RANDOM = SQL("SELECT random FROM games WHERE game_id=?").pluck();
 
-const QUERY_REMATCH_FIND = db.prepare(`
-	SELECT game_id FROM games WHERE status<3 AND description=?
-`).pluck();
+const SQL_SELECT_PLAYERS = SQL("SELECT * FROM players NATURAL JOIN user_view WHERE game_id=?");
+const SQL_SELECT_PLAYERS_JOIN = SQL("SELECT role, user_id, name FROM players NATURAL JOIN users WHERE game_id=?");
+const SQL_SELECT_PLAYER_ROLE = SQL("SELECT role FROM players WHERE game_id=? AND user_id=?").pluck();
+const SQL_INSERT_PLAYER_ROLE = SQL("INSERT OR IGNORE INTO players (game_id,role,user_id) VALUES (?,?,?)");
+const SQL_DELETE_PLAYER_ROLE = SQL("DELETE FROM players WHERE game_id=? AND role=?");
+const SQL_UPDATE_PLAYER_ROLE = db.prepare("UPDATE players SET role=? WHERE game_id=? AND role=? AND user_id=?");
 
-const QUERY_REMATCH_CREATE = db.prepare(`
+const SQL_SELECT_OPEN_GAMES = db.prepare("SELECT * FROM games WHERE status=0");
+const SQL_COUNT_OPEN_GAMES = SQL("SELECT COUNT(*) FROM games WHERE owner_id=? AND status=0").pluck();
+
+const SQL_SELECT_REMATCH = SQL("SELECT game_id FROM games WHERE status < 3 AND description=?").pluck();
+const SQL_INSERT_REMATCH = SQL(`
 	INSERT INTO games
-		(owner_id, title_id, scenario, options, private, random, ctime, mtime, description, status, state)
+		(owner_id, title_id, scenario, options, private, random, description)
 	SELECT
-		$user_id, title_id, scenario, options, private, random, datetime('now'), datetime('now'), $magic, 0, NULL
+		$user_id, title_id, scenario, options, private, random, $magic
 	FROM games
 	WHERE game_id = $game_id AND NOT EXISTS (
 		SELECT * FROM games WHERE description=$magic
 	)
 `);
 
-let RULES = {};
-let ROLES = {};
-for (let title_id of db.prepare("SELECT * FROM titles").pluck().all()) {
-	if (fs.existsSync(__dirname + "/public/" + title_id + "/rules.js")) {
-		console.log("Loading rules for " + title_id);
-		try {
-			RULES[title_id] = require("./public/" + title_id + "/rules.js");
-			ROLES[title_id] = QUERY_ROLES.all(title_id);
-		} catch (err) {
-			console.log(err);
-		}
-	} else {
-		console.log("Cannot find rules for " + title_id);
+const QUERY_LIST_GAMES = SQL(`
+	SELECT * FROM game_view
+	WHERE private=0 AND status < 2
+	ORDER BY status ASC, mtime DESC
+	`);
+
+const QUERY_LIST_GAMES_OF_TITLE = SQL(`
+	SELECT * FROM game_view
+	WHERE private=0 AND title_id=$title_id
+	ORDER BY status ASC, mtime DESC
+	`);
+
+const QUERY_LIST_GAMES_OF_USER = SQL(`
+	SELECT * FROM game_view
+	WHERE owner_id=$user_id OR game_id IN ( SELECT game_id FROM players WHERE players.user_id=$user_id )
+	ORDER BY status ASC, mtime DESC
+	`);
+
+function is_active(game, players, user_id) {
+	if (game.status !== 1 || user_id === 0)
+		return false;
+	let active = game.active;
+	for (let i = 0; i < players.length; ++i) {
+		let p = players[i];
+		if ((p.user_id === user_id) && (active === 'All' || active === 'Both' || active === p.role))
+			return true;
+	}
+	return false;
+}
+
+function is_shared(game, players, user_id) {
+	let n = 0;
+	for (let i = 0; i < players.length; ++i)
+		if (players[i].user_id === user_id)
+			++n;
+	return n > 1;
+}
+
+function is_solo(players) {
+	return players.every(p => p.user_id === players[0].user_id)
+}
+
+function annotate_games(games, user_id) {
+	for (let i = 0; i < games.length; ++i) {
+		let game = games[i];
+		let players = SQL_SELECT_PLAYERS_JOIN.all(game.game_id);
+		game.player_names = players.map(p => {
+			let name = p.name.replace(/ /g, '\xa0');
+			return p.user_id > 0 ? `<a href="/user/${p.name}">${name}</a>` : name;
+		}).join(", ");
+		game.is_active = is_active(game, players, user_id);
+		game.is_shared = is_shared(game, players, user_id);
+		game.ctime = human_date(game.ctime);
+		game.mtime = human_date(game.mtime);
 	}
 }
 
-app.get('/', function (req, res) {
-	res.render('index.ejs', { user: req.user, message: req.flash('message') });
+app.get('/games', may_be_logged_in, function (req, res) {
+	LOG(req, "GET /join");
+	let games;
+	if (req.isAuthenticated()) {
+		games = QUERY_LIST_GAMES.all();
+		annotate_games(games, req.user.user_id);
+	} else {
+		games = QUERY_LIST_GAMES.all({user_id: 0});
+		annotate_games(games, 0);
+	}
+	let open_games = games.filter(game => game.status === 0);
+	let active_games = games.filter(game => game.status === 1);
+	res.set("Cache-Control", "no-store");
+	res.render('games.ejs', {
+		user: req.user,
+		open_games: open_games,
+		active_games: active_games,
+	});
 });
 
 app.get('/profile', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /profile");
 	let avatar = get_avatar(req.user.mail);
 	let games = QUERY_LIST_GAMES_OF_USER.all({user_id: req.user.user_id});
-	humanize(games);
-	linkify_player_names(games);
+	annotate_games(games, req.user.user_id);
 	let open_games = games.filter(game => game.status === 0);
 	let active_games = games.filter(game => game.status === 1);
 	let finished_games = games.filter(game => game.status === 2);
 	res.set("Cache-Control", "no-store");
-	res.render('profile.ejs', { user: req.user, avatar: avatar,
+	res.render('profile.ejs', {
+		user: req.user,
+		avatar: avatar,
 		open_games: open_games,
 		active_games: active_games,
 		finished_games: finished_games,
-		message: req.flash('message')
 	});
 });
 
-app.get('/info/:title_id', function (req, res) {
+app.get('/info/:title_id', may_be_logged_in, function (req, res) {
 	LOG(req, "GET /info/" + req.params.title_id);
 	let title_id = req.params.title_id;
-	let title = QUERY_TITLE.get(title_id);
+	let title = TITLES[title_id];
 	if (!title)
-		return res.status(404).send("That title doesn't exist.");
+		return res.status(404).send("Invalid title.");
 	if (req.isAuthenticated()) {
-		let games = QUERY_LIST_GAMES_OF_TITLE.all({user_id: req.user.user_id, title_id: title_id});
-		humanize(games);
-		linkify_player_names(games);
+		let games = QUERY_LIST_GAMES_OF_TITLE.all({title_id: title_id});
+		annotate_games(games, req.user.user_id);
 		let open_games = games.filter(game => game.status === 0);
 		let active_games = games.filter(game => game.status === 1);
 		let finished_games = games.filter(game => game.status === 2);
 		res.set("Cache-Control", "no-store");
-		res.render('info.ejs', { user: req.user, title: title,
+		res.render('info.ejs', {
+			user: req.user,
+			title: title,
 			open_games: open_games,
 			active_games: active_games,
 			finished_games: finished_games,
-			message: req.flash('message')
 		});
 	} else {
 		res.set("Cache-Control", "no-store");
-		res.render('info.ejs', { user: req.user, title: title,
+		res.render('info.ejs', {
+			user: req.user,
+			title: title,
 			open_games: [],
 			active_games: [],
 			finished_games: [],
-			message: req.flash('message')
 		});
 	}
 });
@@ -741,10 +1028,15 @@ app.get('/info/:title_id', function (req, res) {
 app.get('/create/:title_id', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /create/" + req.params.title_id);
 	let title_id = req.params.title_id;
-	let title = QUERY_TITLE.get(title_id);
+	let title = TITLES[title_id];
 	if (!title)
-		return res.status(404).send("That title doesn't exist.");
-	res.render('create.ejs', { user: req.user, message: req.flash('message'), title: title, scenarios: RULES[title_id].scenarios });
+		return res.status(404).send("Invalid title.");
+	res.render('create.ejs', {
+		user: req.user,
+		title: title,
+		scenarios: RULES[title_id].scenarios,
+		flash: req.flash('message')
+	});
 });
 
 function options_json_replacer(key, value) {
@@ -767,49 +1059,38 @@ app.post('/create/:title_id', must_be_logged_in, function (req, res) {
 	let scenario = req.body.scenario;
 	let options = JSON.stringify(req.body, options_json_replacer);
 	LOG(req, "POST /create/" + req.params.title_id, scenario, options, priv, JSON.stringify(descr));
-	try {
-		let count = QUERY_COUNT_OPEN_GAMES.get(user_id);
-		if (count >= MAX_OPEN_GAMES) {
-			req.flash('message', "You have too many open games!");
-			return res.redirect('/create/'+title_id);
-		}
-		if (!(title_id in RULES)) {
-			return res.status(404).send("That title doesn't exist.");
-		}
-		if (!RULES[title_id].scenarios.includes(scenario)) {
-			return res.status(404).send("That scenario doesn't exist.");
-		}
-		let info = QUERY_CREATE_GAME.run(user_id, title_id, scenario, options, priv ? 1 : 0, rand ? 1 : 0, descr);
-		res.redirect('/join/'+info.lastInsertRowid);
-	} catch (err) {
-		req.flash('message', err.toString());
+	let count = SQL_COUNT_OPEN_GAMES.get(user_id);
+	if (count >= MAX_OPEN_GAMES) {
+		req.flash('message', "You have too many open games!");
 		return res.redirect('/create/'+title_id);
 	}
+	if (!(title_id in RULES)) {
+		return res.send("Invalid title.");
+	}
+	if (!RULES[title_id].scenarios.includes(scenario)) {
+		return res.send("Invalid scenario.");
+	}
+	let info = SQL_INSERT_GAME.run(user_id, title_id, scenario, options, priv ? 1 : 0, rand ? 1 : 0, descr);
+	res.redirect('/join/'+info.lastInsertRowid);
 });
 
 app.get('/delete/:game_id', must_be_logged_in, function (req, res) {
 	let game_id = req.params.game_id;
 	LOG(req, "GET /delete/" + game_id);
-	try {
-		let game = QUERY_GAME_OWNER.get(game_id, req.user.user_id);
-		if (!game) {
-			req.flash('message', "Only the game owner can delete the game!");
-			return res.redirect('/join/'+game_id);
-		}
-		QUERY_DELETE_GAME.run(game_id);
+	let title_id = SQL_SELECT_GAME_TITLE.get(game_id);
+	let info = SQL_DELETE_GAME.run(game_id, req.user.user_id);
+	if (info.changes === 0)
+		return res.send("Not authorized to delete that game ID.");
+	if (info.changes === 1)
 		update_join_clients_deleted(game_id);
-		res.redirect('/info/'+game.title_id);
-	} catch (err) {
-		req.flash('message', err.toString());
-		return res.redirect('/join/'+game_id);
-	}
+	res.redirect('/info/'+title_id);
 });
 
 function join_rematch(req, res, game_id, role) {
-	let is_random = QUERY_IS_RANDOM.get(game_id);
+	let is_random = SQL_SELECT_GAME_RANDOM.get(game_id);
 	if (is_random) {
 		for (let i = 1; i <= 6; ++i) {
-			let info = QUERY_JOIN_GAME_TRY.run(req.user.user_id, game_id, 'Random ' + i);
+			let info = SQL_INSERT_PLAYER_ROLE.run(game_id, 'Random ' + i, req.user.user_id);
 			if (info.changes === 1) {
 				update_join_clients_players(game_id);
 				break;
@@ -817,7 +1098,7 @@ function join_rematch(req, res, game_id, role) {
 		}
 		return res.redirect('/join/'+game_id);
 	} else {
-		let info = QUERY_JOIN_GAME_TRY.run(req.user.user_id, game_id, role);
+		let info = SQL_INSERT_PLAYER_ROLE.run(game_id, role, req.user.user_id);
 		if (info.changes === 1)
 			update_join_clients_players(game_id);
 		return res.redirect('/join/'+game_id);
@@ -828,22 +1109,17 @@ app.get('/rematch/:old_game_id/:role', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /rematch/" + req.params.old_game_id);
 	let old_game_id = req.params.old_game_id | 0;
 	let role = req.params.role;
-	try {
-		let magic = "\u{1F503} " + old_game_id;
-		let new_game_id = 0;
-		let info = QUERY_REMATCH_CREATE.run({user_id: req.user.user_id, game_id: old_game_id, magic: magic});
-		if (info.changes === 1)
-			new_game_id = info.lastInsertRowid;
-		else
-			new_game_id = QUERY_REMATCH_FIND.get(magic);
-		if (new_game_id)
-			return join_rematch(req, res, new_game_id, role);
-		req.flash('message', "Can't create or find rematch game!");
-		return res.redirect('/join/'+old_game_id);
-	} catch (err) {
-		req.flash('message', err.toString());
-		return res.redirect('/join/'+old_game_id);
-	}
+	let magic = "\u{1F503} " + old_game_id;
+	let new_game_id = 0;
+	let info = SQL_INSERT_REMATCH.run({user_id: req.user.user_id, game_id: old_game_id, magic: magic});
+	if (info.changes === 1)
+		new_game_id = info.lastInsertRowid;
+	else
+		new_game_id = SQL_SELECT_REMATCH.get(magic);
+	if (new_game_id)
+		return join_rematch(req, res, new_game_id, role);
+	req.flash('message', "Can't create or find rematch game!");
+	return res.redirect('/join/'+old_game_id);
 });
 
 let join_clients = {};
@@ -862,7 +1138,7 @@ function update_join_clients_deleted(game_id) {
 function update_join_clients_game(game_id) {
 	let list = join_clients[game_id];
 	if (list && list.length > 0) {
-		let game = QUERY_GAME.get(game_id);
+		let game = SQL_SELECT_GAME_VIEW.get(game_id);
 		for (let res of list) {
 			res.write("retry: 15000\n");
 			res.write("event: game\n");
@@ -874,7 +1150,7 @@ function update_join_clients_game(game_id) {
 function update_join_clients_players(game_id) {
 	let list = join_clients[game_id];
 	if (list && list.length > 0) {
-		let players = QUERY_PLAYERS.all(game_id);
+		let players = SQL_SELECT_PLAYERS_JOIN.all(game_id);
 		let ready = RULES[list.title_id].ready(list.scenario, list.options, players);
 		for (let res of list) {
 			res.write("retry: 15000\n");
@@ -889,11 +1165,11 @@ function update_join_clients_players(game_id) {
 app.get('/join/:game_id', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /join/" + req.params.game_id);
 	let game_id = req.params.game_id | 0;
-	let game = QUERY_GAME.get(game_id);
+	let game = SQL_SELECT_GAME_VIEW.get(game_id);
 	if (!game)
-		return res.status(404).send("That game doesn't exist.");
-	let roles = QUERY_ROLES.all(game.title_id);
-	let players = QUERY_PLAYERS.all(game_id);
+		return res.status(404).send("Invalid game ID.");
+	let roles = ROLES[game.title_id];
+	let players = SQL_SELECT_PLAYERS_JOIN.all(game_id);
 	let ready = (game.status === 0) && RULES[game.title_id].ready(game.scenario, game.options, players);
 	res.set("Cache-Control", "no-store");
 	res.render('join.ejs', {
@@ -902,15 +1178,15 @@ app.get('/join/:game_id', must_be_logged_in, function (req, res) {
 		roles: roles,
 		players: players,
 		ready: ready,
-		message: req.flash('message')
+		flash: req.flash('message')
 	});
 });
 
 app.get('/join-events/:game_id', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /join-events/" + req.params.game_id);
 	let game_id = req.params.game_id | 0;
-	let players = QUERY_PLAYERS.all(game_id);
-	let game = QUERY_GAME.get(game_id);
+	let game = SQL_SELECT_GAME_VIEW.get(game_id);
+	let players = SQL_SELECT_PLAYERS_JOIN.all(game_id);
 
 	res.setHeader("Cache-Control", "no-store");
 	res.setHeader("Content-Type", "text/event-stream");
@@ -945,13 +1221,12 @@ app.get('/join/:game_id/:role', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /join/" + req.params.game_id + "/" + req.params.role);
 	let game_id = req.params.game_id | 0;
 	let role = req.params.role;
-	try {
-		QUERY_JOIN_GAME.run(req.user.user_id, game_id, role);
+	let info = SQL_INSERT_PLAYER_ROLE.run(game_id, role, req.user.user_id);
+	if (info.changes === 1) {
 		update_join_clients_players(game_id);
 		res.send("SUCCESS");
-	} catch (err) {
-		console.log(err);
-		res.send(err.toString());
+	} else {
+		res.send("Could not join game.");
 	}
 });
 
@@ -959,14 +1234,9 @@ app.get('/part/:game_id/:role', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /part/" + req.params.game_id + "/" + req.params.role);
 	let game_id = req.params.game_id | 0;
 	let role = req.params.role;
-	try {
-		QUERY_PART_GAME.run(game_id, role);
-		update_join_clients_players(game_id);
-		res.send("SUCCESS");
-	} catch (err) {
-		console.log(err);
-		res.send(err.toString());
-	}
+	SQL_DELETE_PLAYER_ROLE.run(game_id, role);
+	update_join_clients_players(game_id);
+	res.send("SUCCESS");
 });
 
 function assign_random_roles(game, players) {
@@ -976,77 +1246,63 @@ function assign_random_roles(game, players) {
 		list.splice(k, 1);
 		return r;
 	}
-	let roles = QUERY_ROLES.all(game.title_id);
+	let roles = ROLES[game.title_id];
 	for (let p of players) {
 		let old_role = p.role;
 		p.role = pick_random_item(roles);
-		console.log("ASSIGN ROLE", "(" + p.user_name + ")", old_role, "->", p.role);
-		QUERY_ASSIGN_ROLE.run(p.role, game.game_id, p.user_id, old_role);
+		console.log("ASSIGN ROLE", "(" + p.name + ")", old_role, "->", p.role);
+		SQL_UPDATE_PLAYER_ROLE.run(p.role, game.game_id, old_role, p.user_id);
 	}
 }
 
 app.get('/start/:game_id', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /start/" + req.params.game_id);
 	let game_id = req.params.game_id | 0;
-	try {
-		let game = QUERY_GAME_OWNER.get(game_id, req.user.user_id);
-		if (!game)
-			return res.send("Only the game owner can start the game!");
-		if (game.status !== 0)
-			return res.send("The game is already started!");
-		let players = QUERY_PLAYERS.all(game_id);
-		if (!RULES[game.title_id].ready(game.scenario, game.options, players))
-			return res.send("Invalid player configuration!");
-		if (game.random) {
-			assign_random_roles(game, players);
-			update_join_clients_players(game_id);
-		}
-		let seed = random_seed();
-		let state = RULES[game.title_id].setup(seed, game.scenario, game.options, players);
-		put_replay(game_id, null, 'setup', [seed, game.scenario, game.options, players]);
-		QUERY_START_GAME.run(JSON.stringify(state), state.active, game_id);
-		let is_solo = players.every(p => p.user_id === players[0].user_id);
-		if (is_solo)
-			QUERY_UPDATE_GAME_SET_PRIVATE.run(game_id);
-		update_join_clients_game(game_id);
-		res.send("SUCCESS");
-	} catch (err) {
-		console.log(err);
-		res.send(err.toString());
+	let game = SQL_SELECT_GAME.get(game_id);
+	if (game.owner_id !== req.user.user_id)
+		return res.send("Not authorized to start that game ID.");
+	if (game.status !== 0)
+		return res.send("The game is already started.");
+	let players = SQL_SELECT_PLAYERS.all(game_id);
+	if (!RULES[game.title_id].ready(game.scenario, game.options, players))
+		return res.send("Invalid scenario/options/player configuration!");
+	if (game.random) {
+		assign_random_roles(game, players);
+		players = SQL_SELECT_PLAYERS.all(game_id);
+		update_join_clients_players(game_id);
 	}
+	let seed = random_seed();
+	let state = RULES[game.title_id].setup(seed, game.scenario, game.options, players);
+	put_replay(game_id, null, 'setup', [seed, game.scenario, game.options, players]);
+	SQL_UPDATE_GAME_RESULT.run(1, null, game_id);
+	SQL_UPDATE_GAME_STATE.run(game_id, JSON.stringify(state), state.active);
+	if (is_solo(players))
+		SQL_UPDATE_GAME_PRIVATE.run(game_id);
+	update_join_clients_game(game_id);
+	res.send("SUCCESS");
 });
 
 app.get('/play/:game_id/:role', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /play/" + req.params.game_id + "/" + req.params.role);
 	let game_id = req.params.game_id | 0;
 	let role = req.params.role;
-	try {
-		let title = QUERY_TITLE_FROM_GAME.get(game_id);
-		if (!title)
-			return res.redirect('/join/'+game_id);
-		res.redirect('/'+title+'/play.html?game='+game_id+'&role='+role);
-	} catch (err) {
-		req.flash('message', err.toString());
+	let title = SQL_SELECT_GAME_TITLE.get(game_id);
+	if (!title)
 		return res.redirect('/join/'+game_id);
-	}
+	res.redirect('/'+title+'/play.html?game='+game_id+'&role='+role);
 });
 
 app.get('/play/:game_id', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /play/" + req.params.game_id);
 	let game_id = req.params.game_id | 0;
 	let user_id = req.user.user_id | 0;
-	try {
-		let title = QUERY_TITLE_FROM_GAME.get(game_id);
-		if (!title)
-			return res.redirect('/join/'+game_id);
-		let role = QUERY_ROLE_FROM_GAME_AND_USER.get(game_id, user_id);
-		if (!role)
-			return res.redirect('/'+title+'/play.html?game='+game_id+'&role=Observer');
-		return res.redirect('/'+title+'/play.html?game='+game_id+'&role='+role);
-	} catch (err) {
-		req.flash('message', err.toString());
+	let title = SQL_SELECT_GAME_TITLE.get(game_id);
+	if (!title)
 		return res.redirect('/join/'+game_id);
-	}
+	let role = SQL_SELECT_PLAYER_ROLE.get(game_id, user_id);
+	if (!role)
+		return res.redirect('/'+title+'/play.html?game='+game_id+'&role=Observer');
+	return res.redirect('/'+title+'/play.html?game='+game_id+'&role='+role);
 });
 
 /*
@@ -1056,20 +1312,11 @@ app.get('/play/:game_id', must_be_logged_in, function (req, res) {
 const MAIL_FROM = process.env.MAIL_FROM || "Rally the Troops! <notifications@rally-the-troops.com>";
 const MAIL_FOOTER = "You can unsubscribe from notifications on your profile page:\n\nhttps://rally-the-troops.com/profile\n";
 
-const sql_notify_too_soon = db.prepare("SELECT datetime('now') < datetime(time, ?) FROM notifications WHERE user_id = ? AND game_id = ?").pluck();
-const sql_notify_update = db.prepare("INSERT OR REPLACE INTO notifications VALUES ( ?, ?, datetime('now') )");
-const sql_notify_delete = db.prepare("DELETE FROM notifications WHERE user_id = ? AND game_id = ?");
-const sql_offline_user = db.prepare("SELECT * FROM users WHERE user_id = ? AND datetime('now') > datetime(atime, ?)");
-
-const QUERY_LIST_YOUR_TURN = db.prepare(`
-	SELECT games.game_id, games.title_id, games.active, players.user_id, users.name, users.mail
-	FROM games
-	JOIN players ON games.game_id = players.game_id AND ( games.active = players.role OR games.active = 'Both' OR games.active = 'All' )
-	JOIN users ON users.user_id = players.user_id AND users.notifications = 1
-	WHERE games.status = 1 AND datetime('now') > datetime(games.mtime, '+1 hour')
-`);
+const SQL_SELECT_NOTIFIED = SQL("SELECT datetime('now') < datetime(time,?) FROM last_notified WHERE game_id=? AND user_id=?").pluck();
+const SQL_INSERT_NOTIFIED = SQL("INSERT OR REPLACE INTO last_notified (game_id,user_id,time) VALUES (?,?,datetime('now'))");
+const SQL_DELETE_NOTIFIED = SQL("DELETE FROM last_notified WHERE game_id=? AND user_id=?");
 
-const QUERY_LIST_UNSTARTED_GAMES = db.prepare("SELECT * FROM game_view WHERE status = 0");
+const QUERY_LIST_YOUR_TURN = SQL("SELECT * FROM your_turn_reminder");
 
 function mail_callback(err, info) {
 	if (err)
@@ -1096,59 +1343,58 @@ function mail_password_reset_token(user, token) {
 		"https://rally-the-troops.com/reset_password/" + user.mail + "/" + token + "\n\n" +
 		"If you did not request a password reset you can ignore this mail.\n";
 	console.log("SENT MAIL:", mail_addr(user), subject);
-	mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback);
+	if (mailer)
+		mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback);
 }
 
 function mail_new_message(user, msg_id, msg_from, msg_subject, msg_body) {
-	if (!mailer)
-		return;
 	let subject = "You have a new message from " + msg_from + ".";
 	let body = "Subject: " + msg_subject + "\n\n" +
 		msg_body + "\n\n" +
 		"https://rally-the-troops.com/message/read/" + msg_id + "\n\n" +
 		MAIL_FOOTER;
 	console.log("SENT MAIL:", mail_addr(user), subject);
-	mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback);
+	if (mailer)
+		mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback);
 }
 
 function mail_your_turn_notification(user, game_id, interval) {
-	let too_soon = sql_notify_too_soon.get(interval, user.user_id, game_id);
+	let too_soon = SQL_SELECT_NOTIFIED.get(interval, game_id, user.user_id);
 	if (!too_soon) {
-		sql_notify_update.run(user.user_id, game_id);
-		let game = QUERY_GAME.get(game_id);
+		SQL_INSERT_NOTIFIED.run(game_id, user.user_id);
+		let game = SQL_SELECT_GAME_FULL_VIEW.get(game_id);
 		let subject = game.title_name + " - " + game_id + " - Your turn!";
 		let body = mail_describe(game) +
 			"It's your turn.\n\n" +
 			"https://rally-the-troops.com/play/" + game_id + "\n\n" +
 			MAIL_FOOTER;
 		console.log("SENT MAIL:", mail_addr(user), subject);
-		mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback);
+		if (mailer)
+			mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback);
 	}
 }
 
 function reset_your_turn_notification(user, game_id) {
-	sql_notify_delete.run(user.user_id, game_id);
+	SQL_DELETE_NOTIFIED.run(game_id, user.user_id);
 }
 
 function mail_ready_to_start_notification(user, game_id, interval) {
-	let too_soon = sql_notify_too_soon.get(interval, user.user_id, game_id);
+	let too_soon = SQL_SELECT_NOTIFIED.get(interval, game_id, user.user_id);
 	if (!too_soon) {
-		sql_notify_update.run(user.user_id, game_id);
-		let game = QUERY_GAME.get(game_id);
+		SQL_INSERT_NOTIFIED.run(game_id, user.user_id);
+		let game = SQL_SELECT_GAME_FULL_VIEW.get(game_id);
 		let subject = game.title_name + " - " + game_id + " - Ready to start!";
 		let body = mail_describe(game) +
 			"Your game is ready to start.\n\n" +
 			"https://rally-the-troops.com/join/" + game_id + "\n\n" +
 			MAIL_FOOTER;
 		console.log("SENT MAIL:", mail_addr(user), subject);
-		mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback);
+		if (mailer)
+			mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback);
 	}
 }
 
 function mail_your_turn_notification_to_offline_users(game_id, old_active, active) {
-	if (!mailer)
-		return;
-
 	function is_online(game_id, user_id) {
 		for (let other of clients[game_id])
 			if (other.user_id === user_id)
@@ -1160,9 +1406,9 @@ function mail_your_turn_notification_to_offline_users(game_id, old_active, activ
 	if (old_active === active && active !== 'Both' && active !== 'All')
 		return;
 
-	let players = QUERY_PLAYERS_FULL.all(game_id);
+	let players = SQL_SELECT_PLAYERS.all(game_id);
 	for (let p of players) {
-		if (p.notifications) {
+		if (p.notify) {
 			if (active === p.role || active === 'Both' || active === 'All') {
 				if (is_online(game_id, p.user_id)) {
 					reset_your_turn_notification(p, game_id);
@@ -1177,24 +1423,18 @@ function mail_your_turn_notification_to_offline_users(game_id, old_active, activ
 }
 
 function notify_your_turn_reminder() {
-	if (!mailer)
-		return;
 	for (let item of QUERY_LIST_YOUR_TURN.all()) {
-		if (!QUERY_IS_SOLO.get(item.game_id)) {
-			mail_your_turn_notification(item, item.game_id, '+25 hours');
-		}
+		mail_your_turn_notification(item, item.game_id, '+25 hours');
 	}
 }
 
 function notify_ready_to_start_reminder() {
-	if (!mailer)
-		return;
-	for (let game of QUERY_LIST_UNSTARTED_GAMES.all()) {
-		let players = QUERY_PLAYERS.all(game.game_id);
+	for (let game of SQL_SELECT_OPEN_GAMES.all()) {
+		let players = SQL_SELECT_PLAYERS.all(game.game_id);
 		if (RULES[game.title_id].ready(game.scenario, game.options, players)) {
-			let owner = sql_offline_user.get(game.owner_id, '+3 minutes');
+			let owner = SQL_OFFLINE_USER.get(game.owner_id, '+3 minutes');
 			if (owner) {
-				if (owner.notifications)
+				if (owner.notify)
 					mail_ready_to_start_notification(owner, game.game_id, '+25 hours');
 			}
 		}
@@ -1208,16 +1448,9 @@ setInterval(notify_your_turn_reminder, 15 * 60 * 1000);
 setInterval(notify_ready_to_start_reminder, 5 * 60 * 1000);
 
 /*
- * GAME PLAYING
+ * GAME SERVER
  */
 
-const QUERY_SELECT_CHAT = db.prepare("SELECT chat FROM chats WHERE game_id = ?").pluck();
-const QUERY_UPDATE_CHAT = db.prepare("INSERT OR REPLACE INTO chats ( game_id, time, chat ) VALUES ( ?, datetime('now'), ? )");
-const QUERY_SELECT_GAME_STATE = db.prepare("SELECT state FROM games WHERE game_id = ?");
-const QUERY_UPDATE_GAME_STATE = db.prepare("UPDATE games SET state = ?, active = ?, status = ?, result = ?, mtime = datetime('now') WHERE game_id = ?");
-const QUERY_CONNECT_GAME = db.prepare("SELECT title_id, state FROM games WHERE title_id = ? AND game_id = ?");
-const QUERY_RESTART_GAME = db.prepare("UPDATE games SET state = ?, mtime = datetime('now') WHERE game_id = ?");
-
 let clients = {};
 
 function send_state(socket, state) {
@@ -1237,32 +1470,27 @@ function send_state(socket, state) {
 }
 
 function get_game_state(game_id) {
-	let row = QUERY_SELECT_GAME_STATE.get(game_id);
-	if (!row)
+	let game_state = SQL_SELECT_GAME_STATE.get(game_id);
+	if (!game_state)
 		throw new Error("No game with that ID");
-	return JSON.parse(row.state);
+	return JSON.parse(game_state);
 }
 
 function put_game_state(game_id, state, old_active) {
-	let status = 1;
-	let result = null;
 	if (state.state === 'game_over') {
-		status = 2;
-		result = state.result;
+		SQL_UPDATE_GAME_RESULT.run(2, state.result, game_id);
 	}
-	QUERY_UPDATE_GAME_STATE.run(JSON.stringify(state), state.active, status, result, game_id);
+	SQL_UPDATE_GAME_STATE.run(game_id, JSON.stringify(state), state.active);
 	for (let other of clients[game_id])
 		send_state(other, state);
 	update_join_clients_game(game_id);
 	mail_your_turn_notification_to_offline_users(game_id, old_active, state.active);
 }
 
-const QUERY_INSERT_REPLAY = db.prepare("INSERT INTO replay ( game_id, time, role, action, arguments ) VALUES ( ?, datetime('now'), ?, ?, ? )");
-
 function put_replay(game_id, role, action, args) {
 	if (args !== undefined && args !== null)
 		args = JSON.stringify(args);
-	QUERY_INSERT_REPLAY.run(game_id, role, action, args);
+	SQL_INSERT_REPLAY.run(game_id, role, action, args);
 }
 
 function on_action(socket, action, arg) {
@@ -1293,25 +1521,12 @@ function on_resign(socket) {
 	}
 }
 
-function send_chat(socket, chat) {
-	if (socket.role === "Observer")
-		return;
-	if (chat && socket.chat_length < chat.length) {
-		SLOG(socket, "<-- CHAT LOG", socket.chat_length, "..", chat.length);
-		socket.emit('chat', socket.chat_length, chat.slice(socket.chat_length));
-		socket.chat_length = chat.length;
-	}
-}
-
-function on_getchat(socket, old_len) {
+function on_getchat(socket, seen) {
 	try {
-		socket.chat_length = old_len;
-		let chat = QUERY_SELECT_CHAT.get(socket.game_id);
-		if (!chat)
-			chat = [];
-		else
-			chat = JSON.parse(chat);
-		send_chat(socket, chat);
+		let chat = SQL_SELECT_GAME_CHAT.all(socket.game_id, seen);
+		SLOG(socket, "<-- CHAT", seen, chat.length);
+		for (let i = 0; i < chat.length; ++i)
+			socket.emit('chat', chat[i]);
 	} catch (err) {
 		console.log(err);
 		return socket.emit('error', err.toString());
@@ -1319,18 +1534,14 @@ function on_getchat(socket, old_len) {
 }
 
 function on_chat(socket, message) {
-	message = message.substring(0,4096);
-	SLOG(socket, "--> CHAT");
+	message = message.substring(0,4000);
 	try {
-		let chat = QUERY_SELECT_CHAT.get(socket.game_id);
-		if (!chat)
-			chat = [];
-		else
-			chat = JSON.parse(chat);
-		chat.push([new Date(), socket.user_name, message]);
-		QUERY_UPDATE_CHAT.run(socket.game_id, JSON.stringify(chat));
+		let chat = SQL_INSERT_GAME_CHAT.get(socket.game_id, socket.user_id, message);
+		chat[2] = socket.user_name;
+		SLOG(socket, "--> CHAT", chat);
 		for (let other of clients[socket.game_id])
-			send_chat(other, chat);
+			if (other.role !== "Observer")
+				other.emit('chat', chat);
 	} catch (err) {
 		console.log(err);
 		return socket.emit('error', err.toString());
@@ -1340,10 +1551,10 @@ function on_chat(socket, message) {
 function on_debug(socket) {
 	SLOG(socket, "<-- DEBUG");
 	try {
-		let row = QUERY_SELECT_GAME_STATE.get(socket.game_id);
-		if (!row)
+		let game_state = SQL_SELECT_GAME_STATE.get(socket.game_id);
+		if (!game_state)
 			return socket.emit('error', "No game with that ID.");
-		socket.emit('debug', row.state);
+		socket.emit('debug', game_state);
 	} catch (err) {
 		console.log(err);
 		return socket.emit('error', err.toString());
@@ -1353,10 +1564,10 @@ function on_debug(socket) {
 function on_save(socket) {
 	SLOG(socket, "<-- SAVE");
 	try {
-		let row = QUERY_SELECT_GAME_STATE.get(socket.game_id);
-		if (!row)
+		let game_state = SQL_SELECT_GAME_STATE.get(socket.game_id);
+		if (!game_state)
 			return socket.emit('error', "No game with that ID.");
-		socket.emit('save', row.state);
+		socket.emit('save', game_state);
 	} catch (err) {
 		console.log(err);
 		return socket.emit('error', err.toString());
@@ -1367,7 +1578,8 @@ function on_restore(socket, state_text) {
 	SLOG(socket, '--> RESTORE', state_text);
 	try {
 		let state = JSON.parse(state_text);
-		QUERY_UPDATE_GAME_STATE.run(state_text, state.active, 1, null, socket.game_id);
+		SQL_UPDATE_GAME_RESULT.run(1, null, game_id);
+		SQL_UPDATE_GAME_STATE.run(game_id, state_text, state.active);
 		for (let other of clients[socket.game_id])
 			send_state(other, state);
 	} catch (err) {
@@ -1385,23 +1597,22 @@ function broadcast_presence(game_id) {
 }
 
 io.on('connection', (socket) => {
-	socket.title_id = socket.handshake.query.title;
+	socket.title_id = socket.handshake.query.title || "unknown";
 	socket.game_id = socket.handshake.query.game | 0;
 	socket.user_id = socket.request.user.user_id | 0;
 	socket.user_name = socket.request.user.name;
 	socket.role = socket.handshake.query.role;
 	socket.log_length = 0;
-	socket.chat_length = 0;
 	socket.rules = RULES[socket.title_id];
 
 	SLOG(socket, "CONNECT");
 
 	try {
-		let game = QUERY_CONNECT_GAME.get(socket.title_id, socket.game_id);
-		if (!game)
-			return socket.emit('error', "That game does not exist.");
+		let title_id = SQL_SELECT_GAME_TITLE.get(socket.game_id);
+		if (title_id !== socket.title_id)
+			return socket.emit('error', "Invalid game ID.");
 
-		let players = QUERY_PLAYERS.all(socket.game_id);
+		let players = SQL_SELECT_PLAYERS.all(socket.game_id);
 
 		if (socket.role !== "Observer") {
 			let me;
@@ -1434,7 +1645,7 @@ io.on('connection', (socket) => {
 		if (socket.role !== "Observer") {
 			socket.on('action', (action, arg) => on_action(socket, action, arg));
 			socket.on('resign', () => on_resign(socket));
-			socket.on('getchat', (old_len) => on_getchat(socket, old_len));
+			socket.on('getchat', (seen) => on_getchat(socket, seen));
 			socket.on('chat', (message) => on_chat(socket, message));
 
 			socket.on('debug', () => on_debug(socket));
@@ -1450,7 +1661,8 @@ io.on('connection', (socket) => {
 						send_state(other, state);
 					}
 					let state_text = JSON.stringify(state);
-					QUERY_RESTART_GAME.run(state_text, socket.game_id);
+					SQL_UPDATE_GAME_RESULT.run(1, null, socket.game_id);
+					SQL_UPDATE_GAME_STATE.run(socket.game_id, state_text, state.active);
 				} catch (err) {
 					console.log(err);
 					return socket.emit('error', err.toString());
@@ -1460,7 +1672,7 @@ io.on('connection', (socket) => {
 
 		broadcast_presence(socket.game_id);
 
-		send_state(socket, JSON.parse(game.state));
+		send_state(socket, get_game_state(socket.game_id));
 
 	} catch (err) {
 		console.log(err);
@@ -1468,374 +1680,23 @@ io.on('connection', (socket) => {
 	}
 });
 
-// EXTRAS
+/*
+ * HIDDEN EXTRAS
+ */
 
-const QUERY_TITLES = db.prepare("SELECT * FROM titles ORDER BY title_name");
 const QUERY_STATS = db.prepare(`
 	SELECT title_id, scenario, result, count(*) AS count
-	FROM game_view
-	WHERE status=2 AND is_solo=0
+	FROM game_full_view
+	WHERE status=2 AND private=0
 	GROUP BY title_name, scenario, result
 	`);
 
-app.get('/stats', function (req, res) {
+app.get('/stats', may_be_logged_in, function (req, res) {
 	LOG(req, "GET /stats");
 	let stats = QUERY_STATS.all();
-	let titles = Object.fromEntries(QUERY_TITLES.all().map(t => [t.title_id, t.title_name]));
 	res.render('stats.ejs', {
 		user: req.user,
-		message: req.flash('message'),
 		stats: stats,
-		title_role_map: ROLES, title_name_map: titles, title_rule_map: RULES
-	});
-});
-
-app.get('/users', function (req, res) {
-	LOG(req, "GET /users");
-	let rows = db.prepare("SELECT user_id, name, mail, ctime, atime FROM users ORDER BY atime DESC").all();
-	rows.forEach(row => {
-		row.avatar = get_avatar(row.mail);
-		row.ctime = human_date(row.ctime);
-		row.atime = human_date(row.atime);
-	});
-	res.render('users.ejs', { user: req.user, message: req.flash('message'), userList: rows });
-});
-
-const QUERY_LIST_GAMES = db.prepare(`
-	SELECT *,
-		EXISTS (
-			SELECT 1 FROM players
-			WHERE players.game_id = game_view.game_id
-			AND user_id = $user_id
-			AND active_role IN ( 'All', 'Both', role )
-		) AS is_your_turn
-	FROM game_view
-	WHERE private = 0 AND status < 2
-		AND EXISTS (
-			SELECT 1 FROM players
-			WHERE players.game_id = game_view.game_id
-			AND user_id = game_view.owner_id
-		)
-	ORDER BY status ASC, mtime DESC
-`);
-
-app.get('/games', must_be_logged_in, function (req, res) {
-	LOG(req, "GET /join");
-	let games = QUERY_LIST_GAMES.all({user_id: req.user.user_id});
-	humanize(games);
-	let open_games = games.filter(game => game.status === 0);
-	let active_games = games.filter(game => game.status === 1);
-	res.set("Cache-Control", "no-store");
-	res.render('games.ejs', { user: req.user,
-		open_games: open_games,
-		active_games: active_games,
-		message: req.flash('message')
-	});
-});
-
-app.get('/user/:who_id', function (req, res) {
-	LOG(req, "GET /user/" + req.params.who_id);
-	let who = sql_fetch_user_by_name.get(req.params.who_id, req.params.who_id);
-	if (who) {
-		who.avatar = get_avatar(who.mail);
-		who.ctime = human_date(who.ctime);
-		who.atime = human_date(who.atime);
-		res.render('user.ejs', { user: req.user, who: who, message: req.flash('message') });
-	} else {
-		req.flash('message', "Cannot find that user.");
-		return res.redirect('/');
-	}
-});
-
-// FORUM
-
-const FORUM_PAGE_SIZE = 15;
-const FORUM_COUNT_THREADS = db.prepare("SELECT COUNT(*) FROM threads").pluck();
-const FORUM_LIST_THREADS = db.prepare("SELECT * FROM thread_view ORDER BY mtime DESC LIMIT ? OFFSET ?");
-const FORUM_GET_THREAD = db.prepare("SELECT * FROM thread_view WHERE thread_id = ?");
-const FORUM_LIST_POSTS = db.prepare("SELECT * FROM post_view WHERE thread_id = ?");
-const FORUM_GET_POST = db.prepare("SELECT * FROM post_view WHERE post_id = ?");
-const FORUM_NEW_THREAD = db.prepare("INSERT INTO threads ( author_id, subject ) VALUES ( ?, ? )");
-const FORUM_NEW_POST = db.prepare("INSERT INTO posts ( thread_id, author_id, body ) VALUES ( ?, ?, ? )");
-const FORUM_EDIT_POST = db.prepare("UPDATE posts SET body = ?, mtime = datetime('now') WHERE post_id = ? AND author_id = ? RETURNING thread_id").pluck();
-
-function show_forum_page(req, res, page) {
-	let thread_count = FORUM_COUNT_THREADS.get();
-	let page_count = Math.ceil(thread_count / FORUM_PAGE_SIZE);
-	let threads = FORUM_LIST_THREADS.all(FORUM_PAGE_SIZE, FORUM_PAGE_SIZE * (page - 1));
-	humanize(threads);
-	res.set("Cache-Control", "no-store");
-	res.render('forum_view.ejs', {
-		user: req.user,
-		threads: threads,
-		current_page: page,
-		page_count: page_count,
-		message: req.flash('message'),
-	});
-}
-
-function linkify(text) {
-	text = text.replace(/&/g, "&amp;").replace(/</g, "&lt;").replace(/>/g, "&gt;");
-	text = text.replace(/https?:\/\/\S+/, (match) => {
-		if (match.endsWith(".jpg") || match.endsWith(".png") || match.endsWith(".svg"))
-			return `<a href="${match}"><img src="${match}"></a>`;
-		return `<a href="${match}">${match}</a>`;
-	});
-	return text;
-}
-
-app.get('/forum', function (req, res) {
-	LOG(req, "GET /forum");
-	show_forum_page(req, res, 1);
-});
-
-app.get('/forum/page/:page', function (req, res) {
-	LOG(req, "GET /forum/page/" + req.params.page);
-	show_forum_page(req, res, req.params.page | 0);
-});
-
-app.get('/forum/thread/:thread_id', function (req, res) {
-	LOG(req, "GET /forum/thread/" + req.params.thread_id);
-	let thread_id = req.params.thread_id | 0;
-	let thread = FORUM_GET_THREAD.get(thread_id);
-	let posts = FORUM_LIST_POSTS.all(thread_id);
-	for (let i = 0; i < posts.length; ++i) {
-		posts[i].body = linkify(posts[i].body);
-		posts[i].edited = posts[i].mtime !== posts[i].ctime;
-		humanize_one(posts[i]);
-	}
-	res.set("Cache-Control", "no-store");
-	res.render('forum_thread.ejs', {
-		user: req.user,
-		thread: thread,
-		posts: posts,
-		message: req.flash('message'),
-	});
-});
-
-app.get('/forum/post', must_be_logged_in, function (req, res) {
-	LOG(req, "GET /forum/post");
-	res.render('forum_post.ejs', {
-		user: req.user,
-		message: req.flash('message'),
-	});
-});
-
-app.post('/forum/post', must_be_logged_in, function (req, res) {
-	LOG(req, "POST /forum/post");
-	let user_id = req.user.user_id;
-	let subject = req.body.subject.trim();
-	let body = req.body.body;
-	if (subject.length === 0)
-		subject = "Untitled";
-	let thread_id = FORUM_NEW_THREAD.run(user_id, subject).lastInsertRowid;
-	FORUM_NEW_POST.run(thread_id, user_id, body);
-	res.redirect('/forum/thread/'+thread_id);
-});
-
-app.get('/forum/edit/:post_id', must_be_logged_in, function (req, res) {
-	// TODO: edit subject if editing first post
-	LOG(req, "GET /forum/edit/" + req.params.post_id);
-	let post_id = req.params.post_id | 0;
-	let post = FORUM_GET_POST.get(post_id);
-	humanize_one(post);
-	res.render('forum_edit.ejs', {
-		user: req.user,
-		post: post,
-		message: req.flash('message'),
-	});
-});
-
-app.post('/forum/edit/:post_id', must_be_logged_in, function (req, res) {
-	LOG(req, "POST /forum/edit/" + req.params.post_id);
-	let user_id = req.user.user_id;
-	let post_id = req.params.post_id | 0;
-	let body = req.body.body;
-	let thread_id = FORUM_EDIT_POST.get(body, post_id, user_id);
-	res.redirect('/forum/thread/'+thread_id);
-});
-
-app.get('/forum/reply/:post_id', must_be_logged_in, function (req, res) {
-	LOG(req, "GET /forum/reply/" + req.params.post_id);
-	let post_id = req.params.post_id | 0;
-	let post = FORUM_GET_POST.get(post_id);
-	let thread = FORUM_GET_THREAD.get(post.thread_id);
-	post.body = linkify(post.body);
-	post.edited = post.mtime !== post.ctime;
-	humanize_one(post);
-	humanize_one(thread);
-	res.render('forum_reply.ejs', {
-		user: req.user,
-		thread: thread,
-		post: post,
-		message: req.flash('message'),
-	});
-});
-
-app.post('/forum/reply/:thread_id', must_be_logged_in, function (req, res) {
-	LOG(req, "POST /forum/reply/" + req.params.thread_id);
-	let thread_id = req.params.thread_id | 0;
-	let user_id = req.user.user_id;
-	let body = req.body.body;
-	FORUM_NEW_POST.run(thread_id, user_id, body);
-	res.redirect('/forum/thread/'+thread_id);
-});
-
-// MESSAGES
-
-const MESSAGE_GET_USER = db.prepare("SELECT user_id, name, mail, notifications FROM users WHERE name = ?");
-const MESSAGE_GET_USER_ID = db.prepare("SELECT user_id FROM users WHERE name = ?").pluck();
-const MESSAGE_GET_USER_NAME = db.prepare("SELECT name FROM users WHERE user_id = ?").pluck();
-
-const MESSAGE_LIST_INBOX = db.prepare(`
-	SELECT message_id, from_name, subject, time, read
-	FROM message_view
-	WHERE to_id = ? AND deleted_from_inbox = 0
-	ORDER BY time DESC`);
-
-const MESSAGE_LIST_OUTBOX = db.prepare(`
-	SELECT message_id, to_name, subject, time, 1 as read
-	FROM message_view
-	WHERE from_id = ? AND deleted_from_outbox = 0
-	ORDER BY time DESC`);
-
-const MESSAGE_FETCH = db.prepare("SELECT * FROM message_view WHERE message_id = ? AND ( from_id = ? OR to_id = ? )");
-const MESSAGE_SEND = db.prepare("INSERT INTO messages ( from_id, to_id, subject, body ) VALUES ( ?, ?, ?, ? )");
-const MESSAGE_MARK_READ = db.prepare("UPDATE messages SET read = 1 WHERE message_id = ?");
-const MESSAGE_DELETE_INBOX = db.prepare("UPDATE messages SET deleted_from_inbox = 1 WHERE message_id = ? AND to_id = ?");
-const MESSAGE_DELETE_OUTBOX = db.prepare("UPDATE messages SET deleted_from_outbox = 1 WHERE message_id = ? AND from_id = ?");
-const MESSAGE_DELETE_ALL_OUTBOX = db.prepare("UPDATE messages SET deleted_from_outbox = 1 WHERE from_id = ?");
-
-app.get('/inbox', must_be_logged_in, function (req, res) {
-	LOG(req, "GET /inbox");
-	let messages = MESSAGE_LIST_INBOX.all(req.user.user_id);
-	for (let i = 0; i < messages.length; ++i) {
-		messages[i].time = human_date(messages[i].time);
-	}
-	res.set("Cache-Control", "no-store");
-	res.render('message_inbox.ejs', {
-		user: req.user,
-		messages: messages,
-		message: req.flash('message'),
-	});
-});
-
-app.get('/outbox', must_be_logged_in, function (req, res) {
-	LOG(req, "GET /outbox");
-	let messages = MESSAGE_LIST_OUTBOX.all(req.user.user_id);
-	for (let i = 0; i < messages.length; ++i) {
-		messages[i].time = human_date(messages[i].time);
-	}
-	res.set("Cache-Control", "no-store");
-	res.render('message_outbox.ejs', {
-		user: req.user,
-		messages: messages,
-		message: req.flash('message'),
-	});
-});
-
-app.get('/message/read/:message_id', must_be_logged_in, function (req, res) {
-	LOG(req, "GET /message/" + req.params.message_id);
-	let message_id = req.params.message_id | 0;
-	let mail = MESSAGE_FETCH.get(message_id, req.user.user_id, req.user.user_id);
-	if (!mail) {
-		req.flash('message', "Cannot find that message.");
-		return res.redirect('/inbox');
-	}
-	if (mail.to_id === req.user.user_id)
-		MESSAGE_MARK_READ.run(message_id);
-	mail.time = human_date(mail.time);
-	res.render('message_read.ejs', {
-		user: req.user,
-		mail: mail,
-		message: req.flash('message'),
-	});
-});
-
-app.get('/message/send', must_be_logged_in, function (req, res) {
-	res.render('message_send.ejs', {
-		user: req.user,
-		to_id: 0,
-		to_name: "",
-		subject: "",
-		body: "",
-		message: req.flash('message'),
-	});
-});
-
-app.get('/message/send/:to_id', must_be_logged_in, function (req, res) {
-	LOG(req, "GET /message/send/" + req.params.to_id);
-	let to_id = req.params.to_id | 0;
-	if (to_id === 0)
-		to_id = MESSAGE_GET_USER_ID.get(req.params.to_id);
-	let to_name = MESSAGE_GET_USER_NAME.get(to_id);
-	if (!to_name)
-		to_name = "";
-	res.render('message_send.ejs', {
-		user: req.user,
-		to_id: to_id,
-		to_name: to_name,
-		subject: "",
-		body: "",
-		message: req.flash('message'),
+		title_role_map: ROLES, title_name_map: TITLES, title_rule_map: RULES
 	});
 });
-
-app.post('/message/send', must_be_logged_in, function (req, res) {
-	LOG(req, "POST /message/send/" + req.params.to_id);
-	let to_name = req.body.to;
-	let subject = req.body.subject;
-	let body = req.body.body;
-	let to_user = MESSAGE_GET_USER.get(to_name);
-	if (!to_user) {
-		return res.render('message_send.ejs', {
-			user: req.user,
-			to_id: 0,
-			to_name: to_name,
-			subject: subject,
-			body: body,
-			message: "Cannot find that user."
-		});
-	}
-	let info = MESSAGE_SEND.run(req.user.user_id, to_user.user_id, subject, body);
-	if (to_user.notifications) {
-		mail_new_message(to_user, info.lastInsertRowid, req.user.name, subject, body)
-	}
-	res.redirect('/inbox');
-});
-
-function quote_body(text) {
-	return "> " + text.split("\n").join("\n> ") + "\n\n";
-}
-
-app.get('/message/reply/:message_id', must_be_logged_in, function (req, res) {
-	LOG(req, "POST /message/reply/" + req.params.message_id);
-	let message_id = req.params.message_id | 0;
-	let mail = MESSAGE_FETCH.get(message_id, req.user.user_id, req.user.user_id);
-	if (!mail) {
-		req.flash('message', "Cannot find that message.");
-		return res.redirect('/inbox');
-	}
-	return res.render('message_send.ejs', {
-		user: req.user,
-		to_id: mail.from_id,
-		to_name: mail.from_name,
-		subject: mail.subject.startsWith("Re: ") ? mail.subject : "Re: " + mail.subject,
-		body: quote_body(mail.body),
-		message: req.flash('message')
-	});
-});
-
-app.get('/message/delete/:message_id', must_be_logged_in, function (req, res) {
-	LOG(req, "POST /message/delete/" + req.params.message_id);
-	let message_id = req.params.message_id | 0;
-	MESSAGE_DELETE_INBOX.run(message_id, req.user.user_id);
-	MESSAGE_DELETE_OUTBOX.run(message_id, req.user.user_id);
-	res.redirect('/inbox');
-});
-
-app.get('/outbox/delete', must_be_logged_in, function (req, res) {
-	LOG(req, "POST /outbox/delete");
-	MESSAGE_DELETE_ALL_OUTBOX.run(req.user.user_id);
-	res.redirect('/outbox');
-});
diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt
index 6344470..5e80ec6 100644
--- a/tools/sql/schema.txt
+++ b/tools/sql/schema.txt
@@ -1,272 +1,292 @@
+CREATE TABLE IF NOT EXISTS blacklist_ip ( ip TEXT NOT NULL PRIMARY KEY ) WITHOUT ROWID;
+CREATE TABLE IF NOT EXISTS blacklist_mail ( mail TEXT NOT NULL PRIMARY KEY ) WITHOUT ROWID;
+
+CREATE TABLE IF NOT EXISTS titles (
+	title_id TEXT NOT NULL PRIMARY KEY,
+	title_name TEXT NOT NULL,
+	bgg INTEGER
+) WITHOUT ROWID;
+
+CREATE TABLE IF NOT EXISTS roles (
+	title_id TEXT NOT NULL REFERENCES titles(title_id) ON DELETE CASCADE,
+	role TEXT NOT NULL,
+	UNIQUE ( title_id, role )
+);
+
+-- Users --
+
 CREATE TABLE IF NOT EXISTS users (
-	user_id INTEGER PRIMARY KEY,
-	name TEXT UNIQUE COLLATE NOCASE,
-	mail TEXT UNIQUE COLLATE NOCASE,
-	about TEXT,
-	password TEXT,
-	salt TEXT,
-	ctime TIMESTAMP,
-	cip TEXT,
-	atime TIMESTAMP,
-	aip TEXT,
-	notifications INTEGER
+	user_id	INTEGER NOT NULL PRIMARY KEY,
+	name TEXT NOT NULL UNIQUE COLLATE NOCASE,
+	mail TEXT NOT NULL UNIQUE COLLATE NOCASE,
+	notify BOOLEAN NOT NULL DEFAULT 0,
+	banned BOOLEAN NOT NULL DEFAULT 0,
+	ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	password TEXT NOT NULL,
+	salt TEXT NOT NULL,
+	about TEXT
 );
 
-CREATE TABLE IF NOT EXISTS notifications (
-	user_id INTEGER,
-	game_id INTEGER,
-	time TIMESTAMP,
-	UNIQUE ( user_id, game_id )
+CREATE TABLE IF NOT EXISTS user_last_seen (
+	user_id INTEGER NOT NULL PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE,
+	atime TIMESTAMP NOT NULL,
+	aip TEXT NOT NULL
 );
 
 CREATE TABLE IF NOT EXISTS tokens (
-	user_id INTEGER PRIMARY KEY,
-	token TEXT,
-	time TIMESTAMP
+	user_id INTEGER NOT NULL PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE,
+	token TEXT NOT NULL,
+	time TIMESTAMP NOT NULL
 );
 
-CREATE TABLE IF NOT EXISTS blacklist_ip ( ip TEXT PRIMARY KEY );
-CREATE TABLE IF NOT EXISTS blacklist_mail ( mail TEXT PRIMARY KEY );
+CREATE TABLE IF NOT EXISTS last_notified (
+	game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE,
+	user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
+	time TIMESTAMP NOT NULL,
+	PRIMARY KEY ( game_id, user_id )
+) WITHOUT ROWID;
 
-CREATE TABLE IF NOT EXISTS titles (
-	title_id TEXT UNIQUE,
-	title_name TEXT,
-	bgg INTEGER
+DROP VIEW IF EXISTS user_view;
+CREATE VIEW user_view AS
+	SELECT
+		user_id, name, mail, notify
+	FROM users
+	;
+
+DROP VIEW IF EXISTS user_login_view;
+CREATE VIEW user_login_view AS
+	SELECT
+		user_id, name, mail, notify, password, salt
+	FROM users
+	;
+
+DROP VIEW IF EXISTS user_profile_view;
+CREATE VIEW user_profile_view AS
+	SELECT
+		user_id, name, mail, notify, ctime, atime, about
+	FROM users
+	NATURAL LEFT JOIN user_last_seen
+	;
+
+-- Messages --
+
+CREATE TABLE IF NOT EXISTS messages (
+	message_id INTEGER NOT NULL PRIMARY KEY,
+	deleted_from_inbox BOOLEAN NOT NULL DEFAULT 0,
+	deleted_from_outbox BOOLEAN NOT NULL DEFAULT 0,
+	from_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT,
+	to_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT,
+	time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	read BOOLEAN NOT NULL DEFAULT 0,
+	subject TEXT NOT NULL,
+	body TEXT NOT NULL
 );
 
-CREATE TABLE IF NOT EXISTS roles (
-	title_id TEXT,
-	role TEXT,
-	UNIQUE ( title_id, role )
+DROP VIEW IF EXISTS message_view;
+CREATE VIEW message_view AS
+	SELECT *
+		, users_from.name AS from_name
+		, users_to.name AS to_name
+	FROM messages
+	JOIN users AS users_from ON messages.from_id = users_from.user_id
+	JOIN users AS users_to ON messages.to_id = users_to.user_id
+	;
+
+DROP INDEX IF EXISTS messages_inbox_idx;
+CREATE INDEX messages_inbox_idx ON messages(to_id) WHERE deleted_from_inbox=0;
+
+DROP INDEX IF EXISTS messages_inbox_unread_idx;
+CREATE INDEX messages_inbox_unread_idx ON messages(to_id) WHERE read=0 AND deleted_from_inbox=0;
+
+-- Forum --
+
+CREATE TABLE IF NOT EXISTS threads (
+	thread_id INTEGER NOT NULL PRIMARY KEY,
+	author_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT,
+	subject TEXT NOT NULL,
+	locked BOOLEAN NOT NULL DEFAULT 0
 );
 
+CREATE TABLE IF NOT EXISTS posts (
+	post_id INTEGER NOT NULL PRIMARY KEY,
+	thread_id INTEGER NOT NULL REFERENCES threads(thread_id) ON DELETE CASCADE,
+	author_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT,
+	ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	mtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	body TEXT NOT NULL
+);
+
+DROP VIEW IF EXISTS thread_view;
+CREATE VIEW thread_view AS
+	SELECT *
+		, author.name AS author_name
+		, ( SELECT COUNT(*) FROM posts WHERE posts.thread_id = threads.thread_id ) - 1 AS replies
+		, ( SELECT MAX(posts.mtime) FROM posts WHERE posts.thread_id = threads.thread_id ) AS mtime
+	FROM threads
+	JOIN users AS author ON threads.author_id = author.user_id
+	;
+
+DROP VIEW IF EXISTS post_view;
+CREATE VIEW post_view AS
+	SELECT *
+		, author.name AS author_name
+	FROM posts
+	JOIN users AS author ON posts.author_id = author.user_id
+	;
+
+DROP INDEX IF EXISTS posts_thread_idx;
+CREATE INDEX posts_thread_idx ON posts(thread_id);
+
+-- Games --
+
 CREATE TABLE IF NOT EXISTS games (
-	game_id INTEGER PRIMARY KEY,
-	title_id TEXT,
+	game_id INTEGER NOT NULL PRIMARY KEY,
+	title_id TEXT NOT NULL REFERENCES titles(title_id) ON DELETE CASCADE,
 	scenario TEXT,
 	options TEXT,
-	owner_id INTEGER,
-	private BOOLEAN,
-	random BOOLEAN,
-	description TEXT,
-	ctime TIMESTAMP,
-	mtime TIMESTAMP,
-	status INTEGER,
-	result TEXT,
+	owner_id INTEGER DEFAULT 0 REFERENCES users(user_id) ON DELETE SET NULL,
+	ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	private BOOLEAN NOT NULL DEFAULT 0,
+	random BOOLEAN NOT NULL DEFAULT 0,
+	description TEXT NOT NULL DEFAULT '',
+	status INTEGER NOT NULL DEFAULT 0,
+	result TEXT
+);
+
+DROP INDEX IF EXISTS games_title_idx;
+CREATE INDEX games_title_idx ON games(title_id);
+
+DROP INDEX IF EXISTS games_status_idx;
+CREATE INDEX games_status_idx ON games(status);
+
+CREATE TABLE IF NOT EXISTS game_state (
+	game_id INTEGER NOT NULL PRIMARY KEY REFERENCES games(game_id) ON DELETE CASCADE,
+	mtime TIMESTAMP NOT NULL,
 	active TEXT,
 	state TEXT
 );
 
-CREATE TABLE IF NOT EXISTS replay (
-	game_id INTEGER,
-	time TIMESTAMP,
-	role TEXT,
-	action TEXT,
-	arguments TEXT
+CREATE TABLE IF NOT EXISTS game_chat (
+	chat_id INTEGER NOT NULL PRIMARY KEY,
+	game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE,
+	time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	user_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT,
+	message TEXT
 );
 
-CREATE TABLE IF NOT EXISTS chats (
-	game_id INTEGER PRIMARY KEY,
-	time TIMESTAMP,
-	chat TEXT
-);
+DROP VIEW IF EXISTS game_chat_view;
+CREATE VIEW game_chat_view AS
+	SELECT chat_id, game_id, time, name, message
+	FROM game_chat
+	NATURAL JOIN users
+	;
 
-CREATE TABLE IF NOT EXISTS players (
-	user_id INTEGER,
-	game_id INTEGER,
+DROP INDEX IF EXISTS game_chat_idx;
+CREATE INDEX game_chat_idx ON game_chat(game_id);
+
+CREATE TABLE IF NOT EXISTS game_replay (
+	game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE,
+	time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 	role TEXT,
-	UNIQUE ( game_id, role )
+	action TEXT,
+	arguments TEXT
 );
 
-CREATE TABLE IF NOT EXISTS forums (
-	forum_id INTEGER PRIMARY KEY,
-	title TEXT
-);
+CREATE TABLE IF NOT EXISTS players (
+	game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE,
+	role TEXT NOT NULL,
+	user_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT,
+	PRIMARY KEY ( game_id, role )
+) WITHOUT ROWID;
 
-CREATE TABLE IF NOT EXISTS threads (
-	thread_id INTEGER PRIMARY KEY,
-	forum_id INTEGER DEFAULT 1,
-	author_id INTEGER,
-	subject TEXT,
-	ctime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-	mtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-	post_count INTEGER DEFAULT 0,
-	locked BOOLEAN DEFAULT 0
-	-- or first_post_id and last_post_id ?
-);
+DROP INDEX IF EXISTS player_user_idx;
+CREATE INDEX player_user_idx ON players(user_id);
 
-CREATE TABLE IF NOT EXISTS posts (
-	post_id INTEGER PRIMARY KEY,
-	thread_id INTEGER,
-	author_id INTEGER,
-	ctime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-	mtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-	body TEXT
-);
+DROP INDEX IF EXISTS player_game_user_idx;
+CREATE INDEX player_game_user_idx ON players(game_id,user_id);
 
-CREATE TABLE IF NOT EXISTS messages (
-	message_id INTEGER PRIMARY KEY,
-	from_id INTEGER,
-	to_id INTEGER,
-	time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-	subject TEXT,
-	body TEXT,
-	read BOOLEAN DEFAULT 0,
-	deleted_from_inbox BOOLEAN DEFAULT 0,
-	deleted_from_outbox BOOLEAN DEFAULT 0
-);
+DROP VIEW IF EXISTS game_view;
+CREATE VIEW game_view AS
+	SELECT games.*
+		, titles.title_name
+		, owner.name AS owner_name
+		, game_state.mtime
+		, game_state.active
+	FROM games
+	NATURAL LEFT JOIN game_state
+	NATURAL JOIN titles
+	JOIN users AS owner ON owner.user_id=games.owner_id
+	;
 
-DROP TRIGGER IF EXISTS update_reply_count;
-CREATE TRIGGER update_reply_count AFTER INSERT ON posts
-BEGIN
-	UPDATE threads
-		SET
-			post_count = ( SELECT COUNT(*) FROM posts WHERE posts.thread_id = new.thread_id ),
-			mtime = datetime('now')
-		WHERE threads.thread_id = new.thread_id
+DROP VIEW IF EXISTS game_full_view;
+CREATE VIEW game_full_view AS
+	SELECT *,
+		( SELECT group_concat(name, ', ')
+			FROM players
+			NATURAL JOIN users
+			WHERE players.game_id=game_view.game_id
+		) AS player_names,
+		( SELECT COUNT(DISTINCT user_id) = 1
+			FROM players
+			WHERE players.game_id=game_view.game_id
+		) AS is_solo
+	FROM game_view
 	;
-END;
 
-DROP TRIGGER IF EXISTS update_reply_time;
-CREATE TRIGGER update_reply_time AFTER UPDATE ON posts
-BEGIN
-	UPDATE threads SET mtime = datetime('now') WHERE threads.thread_id = new.thread_id ;
-END;
+DROP VIEW IF EXISTS your_turn_reminder;
+CREATE VIEW your_turn_reminder AS
+	SELECT
+		game_id, role, user_id, name, mail, notify
+	FROM game_full_view
+	JOIN players USING(game_id)
+	JOIN users USING(user_id)
+	WHERE status = 1 AND is_solo = 0 AND notify = 1
+		AND active IN ( 'All', 'Both', role )
+		AND datetime('now') > datetime(mtime, '+1 hour')
+	;
 
 DROP TRIGGER IF EXISTS no_part_on_active_game;
 CREATE TRIGGER no_part_on_active_game BEFORE DELETE ON players
 BEGIN
-	SELECT CASE
-	WHEN ( SELECT status FROM games WHERE game_id = old.game_id ) > 0
-	THEN RAISE(ABORT, "Cannot remove players from started games.")
-	END;
+	SELECT RAISE(ABORT, "Cannot remove players from started games.")
+	WHERE ( SELECT status FROM games WHERE games.game_id = old.game_id ) > 0 ;
 END;
 
 DROP TRIGGER IF EXISTS no_join_on_active_game;
 CREATE TRIGGER no_join_on_active_game BEFORE INSERT ON players
 BEGIN
-	SELECT CASE
-	WHEN ( SELECT status FROM games WHERE game_id = new.game_id ) > 0
-	THEN RAISE(ABORT, "Cannot add players to started games.")
-	END;
+	SELECT RAISE(ABORT, "Cannot add players to started games.")
+	WHERE ( SELECT status FROM games WHERE games.game_id = new.game_id ) > 0 ;
 END;
 
 DROP TRIGGER IF EXISTS must_be_valid_role;
 CREATE TRIGGER must_be_valid_role BEFORE INSERT ON players
 BEGIN
-	SELECT CASE
-	WHEN ( SELECT COUNT(*) FROM roles, games WHERE
-		roles.title_id = games.title_id AND
-		games.game_id = new.game_id AND
-		roles.role = new.role ) <> 1
-	AND new.role <> 'Random 1'
-	AND new.role <> 'Random 2'
-	AND new.role <> 'Random 3'
-	AND new.role <> 'Random 4'
-	AND new.role <> 'Random 5'
-	AND new.role <> 'Random 6'
-	THEN RAISE(ABORT, "Invalid role for that title.")
-	END;
+	SELECT
+		RAISE(ABORT, "Invalid role.")
+	WHERE
+		NOT EXISTS (
+			SELECT 1
+			FROM roles
+			WHERE roles.title_id = ( SELECT title_id FROM games WHERE games.game_id = new.game_id )
+			AND roles.role = new.role
+		)
+		AND new.role <> 'Random 1'
+		AND new.role <> 'Random 2'
+		AND new.role <> 'Random 3'
+		AND new.role <> 'Random 4'
+		AND new.role <> 'Random 5'
+		AND new.role <> 'Random 6'
+	;
 END;
 
-DROP TRIGGER IF EXISTS purge_players;
-CREATE TRIGGER purge_players AFTER DELETE ON games
+-- Manual key management if pragma foreign_keys = OFF
+DROP TRIGGER IF EXISTS trigger_delete_on_games;
+CREATE TRIGGER trigger_delete_on_games AFTER DELETE ON games
 BEGIN
+	DELETE FROM game_state WHERE game_id = old.game_id;
+	DELETE FROM game_chat WHERE game_id = old.game_id;
+	DELETE FROM game_replay WHERE game_id = old.game_id;
+	DELETE FROM last_notified WHERE game_id = old.game_id;
 	DELETE FROM players WHERE game_id = old.game_id;
-	DELETE FROM notifications WHERE game_id = old.game_id;
-	DELETE FROM chats WHERE game_id = old.game_id;
-	DELETE FROM replay WHERE game_id = old.game_id;
 END;
-
-DROP VIEW IF EXISTS player_view;
-DROP VIEW IF EXISTS player_list_view;
-DROP VIEW IF EXISTS game_view;
-
-CREATE VIEW player_view AS
-	SELECT players.game_id
-		, players.role AS role
-		, players.user_id AS user_id
-		, users.name AS user_name
-	FROM players
-	JOIN users ON players.user_id = users.user_id
-	;
-
-CREATE VIEW player_list_view AS
-	SELECT game_id
-		, group_concat(name, ', ') AS player_names
-		, COUNT(DISTINCT user_id) AS user_count
-		, COUNT(user_id) AS role_count
-	FROM players
-	JOIN users USING ( user_id )
-	GROUP BY game_id
-	;
-
-CREATE VIEW game_view AS
-	SELECT games.game_id
-		, games.title_id
-		, titles.title_name
-		, games.scenario
-		, games.options
-		, games.owner_id
-		, owner.name AS owner_name
-		, players.player_names
-		, players.user_count = 1 AS is_solo
-		, players.user_count <> players.role_count AS is_shared
-		, games.private
-		, games.random
-		, games.description
-		, games.ctime
-		, games.mtime
-		, games.status
-		, games.result
-		, games.active AS active_role
-		-- , games.state
-	FROM games
-	JOIN titles USING ( title_id )
-	JOIN users AS owner ON games.owner_id = owner.user_id
-	LEFT JOIN player_list_view AS players USING ( game_id )
-	;
-
-DROP VIEW IF EXISTS thread_view;
-CREATE VIEW thread_view AS
-	SELECT threads.thread_id
-		, threads.author_id
-		, author.name AS author_name
-		, threads.post_count - 1 AS reply_count
-		, threads.ctime
-		, threads.mtime
-		, threads.subject
-	FROM threads
-	JOIN users AS author ON threads.author_id = author.user_id
-	;
-
-DROP VIEW IF EXISTS post_view;
-CREATE VIEW post_view AS
-	SELECT posts.post_id
-		, posts.thread_id
-		, posts.author_id
-		, author.name AS author_name
-		, posts.ctime
-		, posts.mtime
-		, posts.body
-	FROM posts
-	JOIN users AS author ON posts.author_id = author.user_id
-	;
-
-DROP VIEW IF EXISTS message_view;
-CREATE VIEW message_view AS
-	SELECT messages.message_id
-		, messages.from_id
-		, users_from.name AS from_name
-		, messages.to_id
-		, users_to.name AS to_name
-		, messages.time
-		, messages.subject
-		, messages.body
-		, messages.read
-		, messages.deleted_from_inbox
-		, messages.deleted_from_outbox
-	FROM messages
-	JOIN users AS users_from ON messages.from_id = users_from.user_id
-	JOIN users AS users_to ON messages.to_id = users_to.user_id
-	;
diff --git a/views/change_about.ejs b/views/change_about.ejs
index 249f9be..c87fc55 100644
--- a/views/change_about.ejs
+++ b/views/change_about.ejs
@@ -1,4 +1,4 @@
-<%- include('header', { title: "Change Profile" }) %>
+<%- include('header', { title: "Change profile text" }) %>
 <style>
 textarea { width: 100%; max-width: 45em; }
 </style>
diff --git a/views/forum_reply.ejs b/views/forum_reply.ejs
index bf27492..85bafad 100644
--- a/views/forum_reply.ejs
+++ b/views/forum_reply.ejs
@@ -1,16 +1,13 @@
 <%- include('header', { title: thread.subject }) %>
 <style>
 input, textarea { width: 100%; max-width: 45em; }
-table { width: 100%; max-width: 50em; }
-td.body { white-space: pre-wrap; padding: 10px 10px; }
-th.author { border-right: none; }
-th.time { text-align: right; border-left: none; font-weight: normal; }
+table .author { border-right: none; }
+table .time { border-left: none; font-weight: normal; }
 </style>
-<table>
+<table class="post">
 <tr>
-<th class="nowrap author"><%= post.author_name %>
-<th class="nowrap time"><%= post.ctime %>
-<%= post.edited ? "(edited " + post.mtime + ")" : "" %>
+<th class="author"><%= post.author_name %>
+<th class="time"><%= post.ctime %> <%= post.edited ? "(edited " + post.mtime + ")" : "" %>
 <tr>
 <td class="body" colspan="2"><%- post.body %></td>
 </table>
diff --git a/views/forum_thread.ejs b/views/forum_thread.ejs
index 0041f20..7232bce 100644
--- a/views/forum_thread.ejs
+++ b/views/forum_thread.ejs
@@ -1,18 +1,15 @@
 <%- include('header', { title: thread.subject }) %>
 <style>
-table { width: 100%; max-width: 50em; }
-td.body { white-space: pre-wrap; padding: 10px 10px; }
-th a { color: black; text-decoration: none; }
-th.author { border-right: none; }
-th.time { text-align: right; border-left: none; font-weight: normal; }
-td.edit { text-align: right; border: none; }
+table .author { border-right: none; }
+table .time { border-left: none; font-weight: normal; }
+table .edit { text-align: right; border: none; }
 </style>
 <% posts.forEach((row) => { %>
 <p>
-<table>
+<table class="post">
 <tr>
-<th class="nowrap author"><a href="/user/<%- row.author_name %>"><%= row.author_name %></a>
-<th class="nowrap time"><%= row.ctime %>
+<th class="author"><a href="/user/<%- row.author_name %>"><%= row.author_name %></a>
+<th class="time"><%= row.ctime %>
 <%= row.edited ? "(edited " + row.mtime + ")" : "" %>
 <tr>
 <td class="body" colspan="2"><%- row.body %></td>
diff --git a/views/forum_view.ejs b/views/forum_view.ejs
index 5f04f3f..6838803 100644
--- a/views/forum_view.ejs
+++ b/views/forum_view.ejs
@@ -1,23 +1,18 @@
 <%- include('header', { title: "Forum", refresh: 900 }) %>
-<style>
-table { width: 100%; max-width: 60em; }
-td a { color: black; text-decoration: none; }
-tfoot td { background-color: gainsboro; }
-th.replies { width: 3em; }
-th.time { width: 5em; }
-th.author { width: 10em; }
-</style>
-<table>
+<table class="post">
 <thead>
-<tr><th class="subject">Subject<th class="author">Author
-<th class="replies">Replies<th class="time">Time
+<tr>
+<th class="subject">Subject
+<th class="author">Author
+<th class="replies">Replies
+<th class="time">Time
 </thead>
 <% threads.forEach((row) => { %>
 <tr>
-<td class="ellipsis"><a href="/forum/thread/<%- row.thread_id %>"><%= row.subject %></a>
-<td class="nowrap"><a href="/user/<%- row.author_name %>"><%= row.author_name %></a>
-<td><%= row.reply_count %>
-<td class="nowrap"><%= row.mtime %>
+<td class="subject"><a href="/forum/thread/<%- row.thread_id %>"><%= row.subject %></a>
+<td class="author"><a href="/user/<%- row.author_name %>"><%= row.author_name %></a>
+<td class="replies"><%= row.replies %>
+<td class="time"><%= row.mtime %>
 <% }); %>
 <tfoot>
 <tr>
diff --git a/views/games.ejs b/views/games.ejs
index b74dbba..acb94b6 100644
--- a/views/games.ejs
+++ b/views/games.ejs
@@ -1,40 +1,37 @@
 <%- include('header', { title: "All Public Games", refresh: (user ? 300 : 0) }) %>
-<style>
-td.nowrap a { color: black; text-decoration: none; }
-</style>
 
 <h2>Open</h2>
-<table class="wide">
+<table class="game">
 <tr><th>ID<th>Title<th>Scenario<th>Players<th>Description<th>Created<th>
 <% if (open_games.length > 0) { %>
 <% open_games.forEach((row) => { %>
 <tr>
-<td><%= row.game_id %>
-<td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
-<td><%= row.scenario %>
-<td><%- row.player_names || row.owner_name %>
-<td><%= row.description %>
-<td class="nowrap"><%= row.ctime %>
-<td><a href="/join/<%= row.game_id %>">Join</a>
+<td class="id"><%= row.game_id %>
+<td class="title"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
+<td class="scenario"><%= row.scenario %>
+<td class="players"><%- row.player_names || row.owner_name %>
+<td class="description"><%= row.description %>
+<td class="time"><%= row.ctime %>
+<td class="command"><a href="/join/<%= row.game_id %>">Join</a>
 <% }); } else { %>
 <tr><td colspan="7">No open games.
 <% } %>
 </table>
 
 <h2>Active</h2>
-<table class="wide">
+<table class="game">
 <tr><th>ID<th>Title<th>Scenario<th>Players<th>Description<th>Changed<th>Active<th>
 <% if (active_games.length > 0) { %>
 <% active_games.forEach((row) => { %>
 <tr>
-<td><%= row.game_id %>
-<td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
-<td><%= row.scenario %>
-<td><%- row.player_names %>
-<td><%= row.description %>
-<td class="nowrap"><%= row.mtime %>
-<td class="<%= row.is_your_turn ? "is_your_turn" : "" %>"><%= row.active_role %>
-<td><a href="/join/<%= row.game_id %>">Enter</a>
+<td class="id"><%= row.game_id %>
+<td class="title"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
+<td class="scenario"><%= row.scenario %>
+<td class="players"><%- row.player_names %>
+<td class="description"><%= row.description %>
+<td class="time"><%= row.mtime %>
+<td class="role <%= row.is_active ? "is_active" : "" %>"><%= row.active %>
+<td class="command"><a href="/join/<%= row.game_id %>">Enter</a>
 <% }); } else { %>
 <tr><td colspan="8">No active games.
 <% } %>
diff --git a/views/header.ejs b/views/header.ejs
index aed34d0..337290a 100644
--- a/views/header.ejs
+++ b/views/header.ejs
@@ -16,7 +16,7 @@
 <span><a href="/forum">Forum</a></span>
 <%
 	if (user) {
-		let unread = user.unread();
+		let unread = user.unread | 0;
 		if (unread > 0) {
 			%><span><a href="/inbox">Inbox (<%= unread %>)</a></span><%
 		} else {
@@ -33,7 +33,7 @@
 <div class="main">
 <h1><%= title %></h1>
 <%
-	if (typeof message !== 'undefined' && message.length > 0) {
-		%><p class="error"><%= Array.isArray(message) ? message.join("\n") : message %></p><%
+	if (typeof flash !== 'undefined' && flash.length > 0) {
+		%><p class="error"><%= Array.isArray(flash) ? flash.join("\n") : flash %></p><%
 	}
 %>
diff --git a/views/info.ejs b/views/info.ejs
index 159e732..f97b04e 100644
--- a/views/info.ejs
+++ b/views/info.ejs
@@ -1,7 +1,4 @@
 <%- include('header', { title: title.title_name, refresh: (user ? 300 : 0) }) %>
-<style>
-td.names a { color: black; text-decoration: none; }
-</style>
 <img class="logo" src="/<%= title.title_id %>/cover.jpg">
 <%- include('../public/' + title.title_id + '/about.html') %>
 <p>
@@ -11,17 +8,17 @@ Read more about the game on
 <% if (user) { %>
 
 <h2>Open Games</h2>
-<table class="wide">
+<table class="game">
 <tr><th>ID<th>Scenario<th>Players<th>Description<th>Created<th>
 <% if (open_games.length > 0) { %>
 <% open_games.forEach((row) => { %>
 <tr>
-<td><%= row.game_id %>
+<td class="id"><%= row.game_id %>
 <td><%= row.scenario %>
-<td class="names"><%- row.player_names || `<a href="/user/${row.owner_name}">${row.owner_name}</a>` %>
-<td><%= row.description %>
-<td class="nowrap"><%= row.ctime %>
-<td><a href="/join/<%= row.game_id %>">Join</a>
+<td class="players"><%- row.player_names || `<a href="/user/${row.owner_name}">${row.owner_name}</a>` %>
+<td class="description"><%= row.description %>
+<td class="time"><%= row.ctime %>
+<td class="command"><a href="/join/<%= row.game_id %>">Join</a>
 <% }); } else { %>
 <tr><td colspan="6">No open games.
 <% } %>
@@ -32,34 +29,34 @@ Read more about the game on
 
 <% if (active_games.length > 0) { %>
 <h2>Active Games</h2>
-<table class="wide">
+<table class="game">
 <tr><th>ID<th>Scenario<th>Players<th>Description<th>Changed<th>Turn<th>
 <% active_games.forEach((row) => { %>
 <tr>
-<td><%= row.game_id %>
-<td><%= row.scenario %>
-<td class="names"><%- row.player_names %>
-<td><%= row.description %>
-<td class="nowrap"><%= row.mtime %>
-<td class="<%= row.is_your_turn ? "is_your_turn" : "" %>"><%= row.active_role %>
-<td><a href="/join/<%= row.game_id %>">Enter</a>
+<td class="id"><%= row.game_id %>
+<td class="scenario"><%= row.scenario %>
+<td class="players"><%- row.player_names %>
+<td class="description"><%= row.description %>
+<td class="time"><%= row.mtime %>
+<td class="<%= row.is_active ? "role is_active" : "role" %>"><%= row.active %>
+<td class="command"><a href="/join/<%= row.game_id %>">Enter</a>
 <% }); %>
 </table>
 <% } %>
 
 <% if (finished_games.length > 0) { %>
 <h2>Finished Games</h2>
-<table class="wide">
+<table class="game">
 <tr><th>ID<th>Scenario<th>Players<th>Description<th>Finished<th>Result<th>
 <% finished_games.forEach((row) => { %>
 <tr>
-<td><%= row.game_id %>
-<td><%= row.scenario %>
-<td class="names"><%- row.player_names %>
-<td><%= row.description %>
-<td class="nowrap"><%= row.mtime %>
-<td><%= row.result %>
-<td><a href="/join/<%= row.game_id %>">View</a>
+<td class="id"><%= row.game_id %>
+<td class="scenario"><%= row.scenario %>
+<td class="players"><%- row.player_names %>
+<td class="description"><%= row.description %>
+<td class="time"><%= row.mtime %>
+<td class="result"><%= row.result %>
+<td class="command"><a href="/join/<%= row.game_id %>">View</a>
 <% }); %>
 </table>
 <% } %>
diff --git a/views/join.ejs b/views/join.ejs
index b9cf824..fa33d5c 100644
--- a/views/join.ejs
+++ b/views/join.ejs
@@ -12,6 +12,7 @@ function format_options(options) {
 <style>
 th, td { min-width: 10em; font-size: 16px; }
 a.red { text-decoration: none; color: brown; font-size: 15px; }
+td a { text-decoration: underline; color: blue; }
 .hide { display: none; }
 </style>
 <script>
@@ -44,7 +45,7 @@ Result: <span id="game_result"></span>
 
 <br clear=left>
 
-<table>
+<table class="small">
 <tr>
 <% roles.forEach((role) => { %><th id="role_<%= role.replace(/ /g, '_') %>_name"><%= role %></th><% }); %>
 <tr>
diff --git a/views/message_inbox.ejs b/views/message_inbox.ejs
index 16b0c46..e70e156 100644
--- a/views/message_inbox.ejs
+++ b/views/message_inbox.ejs
@@ -1,19 +1,12 @@
 <%- include('header', { title: "Inbox" }) %>
-<style>
-table { width: 100%; max-width: 50em; }
-tr.unread { background-color: lightyellow; }
-td.from { width: 5em; }
-td.time { text-align: right; width: 5em; }
-td a { color:black; text-decoration: none; }
-</style>
 <p><a href="/message/send">Send message</a>
-<table>
+<table class="post">
 <tr><th>From<th>Subject<th>Date
 <% if (messages.length > 0) { messages.forEach((row) => { %>
 <tr class="<%- row.read ? "read" : "unread" %>">
-<td class="nowrap from"><a href="/user/<%- row.from_name %>"><%= row.from_name %></a>
+<td class="author"><a href="/user/<%- row.from_name %>"><%= row.from_name %></a>
 <td class="subject"><a href="/message/read/<%- row.message_id %>"><%= row.subject %></a>
-<td class="nowrap time"><%= row.time %>
+<td class="time"><%= row.time %>
 <% }); } else { %>
 <tr><td colspan="3">No messages</td>
 <% } %>
diff --git a/views/message_outbox.ejs b/views/message_outbox.ejs
index e5c53e3..c7ff379 100644
--- a/views/message_outbox.ejs
+++ b/views/message_outbox.ejs
@@ -6,20 +6,13 @@ function delete_all() {
 		window.location.href = "/outbox/delete";
 }
 </script>
-<style>
-table { width: 100%; max-width: 50em; }
-tr.unread { background-color: lemonchiffon; }
-td.to { width: 5em; }
-td.time { text-align: right; width: 5em; }
-td a { color:black; text-decoration: none; }
-</style>
-<table>
+<table class="post">
 <tr><th>To<th>Subject<th>Date
 <% if (messages.length > 0) { messages.forEach((row) => { %>
 <tr class="<%- row.read ? "read" : "unread" %>">
-<td class="nowrap to"><a href="/user/<%- row.to_name %>"><%= row.to_name %></a>
+<td class="author"><a href="/user/<%- row.to_name %>"><%= row.to_name %></a>
 <td class="subject"><a href="/message/read/<%- row.message_id %>"><%= row.subject %></a>
-<td class="nowrap time"><%= row.time %>
+<td class="time"><%= row.time %>
 <% }); } else { %>
 <tr><td colspan="3">No messages</td>
 <% } %>
diff --git a/views/message_read.ejs b/views/message_read.ejs
index 28762cf..7b32eda 100644
--- a/views/message_read.ejs
+++ b/views/message_read.ejs
@@ -1,10 +1,4 @@
-<%- include('header', { title: mail.subject }) %>
-<style>
-table { width: 100%; max-width: 50em; }
-th { width: 5em; font-weight: normal; }
-td.body { white-space: pre-wrap; padding: 10px 10px; }
-td a { color: black; text-decoration: none; }
-</style>
+<%- include('header', { title: message.subject }) %>
 <script>
 function delete_message(id) {
 	let warning = "Are you sure you want to DELETE this message?";
@@ -15,14 +9,14 @@ function reply_message(id) {
 	window.location.href = "/message/reply/" + id;
 }
 </script>
-<table>
-<tr><th>From:<td> <a href="/user/<%- mail.from_name %>"><%= mail.from_name %></a>
-<tr><th>To:<td> <a href="/user/<%- mail.to_name %>"><%= mail.to_name %></a>
-<tr><th>Date:<td> <%= mail.time %>
-<tr><td colspan="2" class="body"><%= mail.body %></td>
+<table class="post">
+<tr><th>From:<td> <a href="/user/<%- message.from_name %>"><%= message.from_name %></a>
+<tr><th>To:<td> <a href="/user/<%- message.to_name %>"><%= message.to_name %></a>
+<tr><th>Date:<td> <%= message.time %>
+<tr><td colspan="2" class="body"><%- message.body %></td>
 </table>
 <p>
-<% if ( mail.from_id !== user.user_id ) { %>
-<button onclick="reply_message(<%- mail.message_id %>)">Reply</button>
+<% if ( 1 || message.from_id !== user.user_id ) { %>
+<button onclick="reply_message(<%- message.message_id %>)">Reply</button>
 <% } %>
-<button onclick="delete_message(<%- mail.message_id %>)">Delete</button>
+<button onclick="delete_message(<%- message.message_id %>)">Delete</button>
diff --git a/views/message_send.ejs b/views/message_send.ejs
index 6f72d74..4dafa3a 100644
--- a/views/message_send.ejs
+++ b/views/message_send.ejs
@@ -1,6 +1,6 @@
 <%- include('header', { title: "Send Message" }) %>
 <style>
-input, textarea { width: 100%; max-width: 45em; }
+input, textarea { width: 100%; max-width: 45rem; }
 </style>
 <form action="/message/send" method="post">
 <p>
@@ -8,7 +8,7 @@ To:<br>
 <input id="to" type="text" name="to" size="80" maxlength="80"
 	onkeypress="if(event.keyCode===13){document.querySelector('#subject').focus();return false;}"
 	value="<%= to_name %>"
-	<%= to_id === 0 ? "autofocus" : "" %>
+	<%= (to_name === "") ? "autofocus" : "" %>
 	required>
 
 <p>
@@ -17,14 +17,17 @@ Subject:
 <input id="subject" type="text" name="subject" size="80" maxlength="80"
 	onkeypress="if(event.keyCode===13){document.querySelector('#body').focus();return false;}"
 	value="<%= subject %>"
-	<%= to_id > 0 ? "autofocus" : "" %>
+	<%= (to_name !== "" && subject === "") ? "autofocus" : "" %>
 	pattern=".*\S+.*"
 	required>
 
 <p>
 Body:
 <br>
-<textarea id="body" name="body" rows="20" cols="80" maxlength="32000" required><%= body %></textarea>
+<textarea id="body" name="body" rows="20" cols="80" maxlength="32000"
+	<%= (to_name !== "" && subject !== "") ? "autofocus" : "" %>
+	required>
+<%= body %></textarea>
 <p>
 <button type="submit">Send</button>
 </form>
diff --git a/views/profile.ejs b/views/profile.ejs
index 3c49014..1dbc1f5 100644
--- a/views/profile.ejs
+++ b/views/profile.ejs
@@ -1,10 +1,5 @@
 <%- include('header', { title: "Rally the Troops!", refresh: (active_games.length > 0 ? 300 : 0) }) %>
-<style>
-td.game a, td.names a { color: black; text-decoration: none; }
-.logo { width: 80px; height: 80px; }
-</style>
-
-<img class="logo" src="<%= avatar %>" width="80" height="80">
+<img class="logo avatar" src="<%= avatar %>" width="80" height="80">
 <p>
 Welcome, <%= user.name %>!
 <p>
@@ -12,60 +7,62 @@ Your mail address is <%= user.mail %>.
 
 <br clear=left>
 
-<ul style="list-style:'\bb  '">
-<li><%
-	if (user.notifications) {
+<p>&#xbb;
+<%
+	if (user.notify) {
 		%><a href="/unsubscribe">Disable mail notifications</a><%
 	} else {
 		%><a href="/subscribe">Enable mail notifications</a><%
 	}
 %>
-<li><a href="/change_password">Change password</a>
-<li><a href="/change_mail">Change mail address</a>
-<li><a href="/change_name">Change name</a>
-<li><a href="/change_about">Change profile text</a>
-<li><a href="/logout">Logout</a>
-</ul>
+<br>&#xbb;
+Change
+<a href="/change_password">password</a>,
+<a href="/change_mail">mail address</a>,
+<a href="/change_name">name</a>,
+or <a href="/change_about">profile text</a>.
+<br>&#xbb;
+<a href="/logout">Logout</a>
 
 <% if (open_games.length > 0) { %>
 <h2>Open Games</h2>
-<table class="wide">
+<table class="game">
 <tr><th>ID<th>Game<th>Scenario<th>Players<th>Description<th>Created<th>
 <% open_games.forEach((row) => { %>
 <tr>
-<td><%= row.game_id %>
-<td class="nowrap game"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
-<td><%= row.scenario %>
-<td class="names"><%- row.player_names %>
-<td><%= row.description %>
-<td class="nowrap"><%= row.ctime %>
-<td><a href="/join/<%= row.game_id %>">Join</a>
+<td class="id"><%= row.game_id %>
+<td class="name"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
+<td class="scenario"><%= row.scenario %>
+<td class="players"><%- row.player_names %>
+<td class="description"><%= row.description %>
+<td class="time"><%= row.ctime %>
+<td class="command"><a href="/join/<%= row.game_id %>">Join</a>
 <% }); %>
 </table>
 <% } %>
 
 <% if (active_games.length > 0) { %>
 <h2>Active Games</h2>
-<table class="wide">
+<table class="game">
 <tr><th>ID<th>Game<th>Scenario<th>Players<th>Description<th>Changed<th>Turn<th>
 <% active_games.forEach((row) => { %>
 <tr>
-<td><%= row.game_id %>
-<td class="nowrap game"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
-<td><%= row.scenario %>
-<td class="names"><%- row.player_names %>
-<td><%= row.description %>
-<td class="nowrap"><%= row.mtime %>
+<td class="id"><%= row.game_id %>
+<td class="title"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
+<td class="scenario"><%= row.scenario %>
+<td class="players"><%- row.player_names %>
+<td class="description"><%= row.description %>
+<td class="time"><%= row.mtime %>
 <%
-	if (row.is_your_turn) {
-		%><td class="is_your_turn"><%= row.active_role %><%
+	if (row.is_active) {
+		%><td class="role is_active"><%= row.active %><%
 	} else {
-		%><td><%= row.active_role %><%
+		%><td class="role"><%= row.active %><%
 	}
 	if (row.is_shared) {
-		%><td><a href="/join/<%= row.game_id %>">Enter</a><%
+		%><td class="command"><a href="/join/<%= row.game_id %>">Enter</a><%
 	} else {
-		%><td><a href="/play/<%= row.game_id %>">Play</a><%
+		%><td class="command"><a href="/play/<%= row.game_id %>">Play</a><%
 	}
 %>
 <% }); %>
@@ -74,18 +71,18 @@ Your mail address is <%= user.mail %>.
 
 <% if (finished_games.length > 0) { %>
 <h2>Finished Games</h2>
-<table class="wide">
+<table class="game">
 <tr><th>ID<th>Game<th>Scenario<th>Players<th>Description<th>Finished<th>Result<th>
 <% finished_games.forEach((row) => { %>
 <tr>
-<td><%= row.game_id %>
-<td class="nowrap game"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
-<td><%= row.scenario %>
-<td class="names"><%- row.player_names %>
-<td><%= row.description %>
-<td class="nowrap"><%= row.mtime %>
-<td><%= row.result %>
-<td><a href="/join/<%= row.game_id %>">View</a>
+<td class="id"><%= row.game_id %>
+<td class="title"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
+<td class="scenario"><%= row.scenario %>
+<td class="players"><%- row.player_names %>
+<td class="description"><%= row.description %>
+<td class="time"><%= row.mtime %>
+<td class="result"><%= row.result %>
+<td class="command"><a href="/join/<%= row.game_id %>">View</a>
 <% }); %>
 </table>
 <% } %>
diff --git a/views/stats.ejs b/views/stats.ejs
index 64a6303..9e07aaf 100644
--- a/views/stats.ejs
+++ b/views/stats.ejs
@@ -17,7 +17,7 @@
 	for (let title_id in title_name_map) {
 		let scenarios = title_rule_map[title_id].scenarios;
 		let roles = title_role_map[title_id].concat(['Draw']);
-		%><tr><th><%= title_name_map[title_id] %><%
+		%><tr><th><%= title_name_map[title_id].title_name %><%
 		roles.forEach(role => {
 			%><th><%= role %><%
 		});
diff --git a/views/user.ejs b/views/user.ejs
index 9da706c..8fd1d08 100644
--- a/views/user.ejs
+++ b/views/user.ejs
@@ -1,8 +1,5 @@
 <%- include('header', { title: who.name }) %>
-<style>
-.logo { width: 80px; height: 80px; }
-</style>
-<img class="logo" src="<%= who.avatar %>" width="80" height="80">
+<img class="avatar" src="<%= who.avatar %>" width="80" height="80">
 <% if (who.about) { %>
 <p style="white-space:pre-wrap;font-style:italic"><%= who.about %></p>
 <% } else { %>
@@ -11,7 +8,7 @@
 <p>
 Member since <%= who.ctime %>.
 <p>
-Was last seen <%= who.atime %>.
+Last seen <%= who.atime %>.
 <% if (user) { %>
 <p>
 <a href="/message/send/<%- who.name %>">Send message</a>
diff --git a/views/users.ejs b/views/users.ejs
index d6e4d4a..9255d04 100644
--- a/views/users.ejs
+++ b/views/users.ejs
@@ -3,14 +3,12 @@
 td.avatar{padding:0;width:80px;}
 td.avatar img{display:block;width:80px;height:80px;}
 </style>
-
-<table class="wide">
+<table class="post">
 <tr><th>Avatar<th>Name<th>Member since<th>Last seen
-
 <% userList.forEach((row) => { %>
 <tr>
 <td class="avatar"><img src="<%= row.avatar %>">
-<td><a href="/user/<%= row.name %>"><%= row.name %></a>
+<td class="name"><a href="/user/<%= row.name %>"><%= row.name %></a>
 <td><%= row.ctime %>
 <td><%= row.atime %>
 <% }); %>