20 files changed, 870 insertions, 0 deletions
diff --git a/bin/rtt b/bin/rtt
new file mode 100755
index 0000000..c6b9edc
--- /dev/null
+++ b/bin/rtt
@@ -0,0 +1,31 @@
+#!/bin/bash
+
+cmd=$1
+shift
+
+bindir=$(dirname $(readlink -f $0))
+
+if [ ! -n "$cmd" ]
+then
+	cmd=help
+fi
+
+if [ ! -f ./server.js ]
+then
+	echo "rtt: command must run in the top-level server directory!"
+	exit 1
+fi
+
+if [ ! -f $bindir/rtt-$cmd ]
+then
+	echo "rtt: '$cmd' is not an rtt command"
+	exit 1
+fi
+
+if [ ! -f ./db -a "$cmd" != init -a "$cmd" != "help" ]
+then
+	echo "rtt: the database does not exist!"
+	exit 1
+fi
+
+exec $bindir/rtt-$cmd "$@"
diff --git a/bin/rtt-archive-backup b/bin/rtt-archive-backup
new file mode 100755
index 0000000..f1b63e5
--- /dev/null
+++ b/bin/rtt-archive-backup
@@ -0,0 +1,106 @@
+#!/bin/bash
+sqlite3 <<EOF
+
+-- Make a copy of finished games in a separate archive database.
+
+attach database 'db' as live;
+attach database 'archive.db' as archive;
+
+pragma live.busy_timeout=10000;
+
+-- List finished (and rated) games in live that are not in archive.
+create temporary view candidates as
+	select
+		game_id
+	from
+		live.rated_games_view
+	where
+		game_id not in (select game_id from archive.games)
+;
+
+create table if not exists archive.users (
+	user_id integer primary key,
+	name text unique collate nocase
+);
+
+create table if not exists archive.players (
+	game_id integer,
+	role text,
+	user_id integer,
+	primary key (game_id, role)
+) without rowid;
+
+create table if not exists archive.games (
+	game_id integer primary key,
+	title_id text,
+	scenario text,
+	options text,
+	player_count integer,
+	ctime datetime,
+	mtime datetime,
+	moves integer,
+	result text
+);
+
+create table if not exists archive.game_state (
+	game_id integer primary key,
+	state text
+);
+
+create table if not exists archive.game_replay (
+	game_id integer,
+	replay_id integer,
+	role text,
+	action text,
+	arguments json,
+	primary key (game_id, replay_id)
+) without rowid;
+
+create table if not exists archive.game_chat (
+	game_id integer,
+	chat_id integer,
+	user_id integer,
+	time datetime,
+	message text,
+	primary key (game_id, chat_id)
+) without rowid;
+
+drop trigger if exists archive.trigger_delete;
+create trigger archive.trigger_delete after delete on archive.games
+begin
+	delete from players where game_id = old.game_id;
+	delete from game_state where game_id = old.game_id;
+	delete from game_replay where game_id = old.game_id;
+	delete from game_chat where game_id = old.game_id;
+end;
+
+begin immediate;
+
+select 'ARCHIVING ' || count(*) || ' GAMES' from candidates;
+
+insert or ignore into archive.users (user_id, name) select user_id, name
+	from live.users;
+
+insert into archive.players (game_id, role, user_id)
+	select game_id, role, user_id
+	from candidates join live.players using(game_id);
+
+insert into archive.game_state (game_id, state)
+	select game_id, state
+	from candidates join live.game_state using(game_id);
+
+insert into archive.game_replay (game_id, replay_id, role, action, arguments)
+	select game_id, replay_id, role, action, arguments
+	from candidates join live.game_replay using(game_id);
+
+insert into archive.game_chat (game_id, chat_id, user_id, time, message)
+	select game_id, chat_id, user_id, time, message
+	from candidates join live.game_chat using(game_id);
+
+insert into archive.games (game_id, title_id, scenario, options, player_count, ctime, mtime, moves, result)
+	select game_id, title_id, scenario, options, player_count, ctime, mtime, moves, result
+	from candidates join live.games using(game_id);
+
+commit;
+
+EOF
diff --git a/bin/rtt-archive-prune b/bin/rtt-archive-prune
new file mode 100755
index 0000000..fff7f23
--- /dev/null
+++ b/bin/rtt-archive-prune
@@ -0,0 +1,40 @@
+#!/bin/bash
+sqlite3 <<EOF
+
+-- Prune game snapshot and game state data to save database space.
+
+attach database 'db' as live;
+
+pragma live.busy_timeout=10000;
+
+create temporary view prune_snap_list as
+	select
+		distinct game_id
+	from
+		live.game_snap
+	where
+		game_id in (
+			select game_id from live.games
+			where status=2 and date(mtime) < date('now', '-7 days')
+		)
+	;
+
+create temporary view prune_all_list as
+	select
+		distinct game_id
+	from
+		live.games
+	where
+		game_id in (
+			select game_id from live.games
+			where status=2 and date(mtime) < date('now', '-28 days')
+		)
+	;
+
+select 'PURGE SNAPS FROM ' || count(1) from prune_snap_list;
+delete from live.game_snap where game_id in (select game_id from prune_snap_list);
+
+select 'PURGE ALL FROM ' || count(1) from prune_all_list;
+update live.games set status = 3 where game_id in (select game_id from prune_all_list);
+
+EOF
diff --git a/bin/rtt-archive-restore b/bin/rtt-archive-restore
new file mode 100755
index 0000000..dc4d59e
--- /dev/null
+++ b/bin/rtt-archive-restore
@@ -0,0 +1,35 @@
+#!/bin/bash
+# Restore purged game state from archive.
+
+if [ -z "$1" ]
+then
+        echo "usage: rtt-archive-restore GAME"
+        exit 1
+fi
+
+sqlite3 db << EOF
+
+attach database 'archive.db' as archive;
+
+begin;
+
+select 'RESTORE ' || $1 || ' FROM ARCHIVE';
+
+.mode table
+select * from archive.games where game_id = $1;
+
+insert or replace into game_state (game_id, state)
+	select game_id, state
+	from archive.game_state where game_id = $1;
+
+insert or replace into game_replay (game_id, replay_id, role, action, arguments)
+	select game_id, replay_id, role, action, arguments
+	from archive.game_replay where game_id = $1;
+
+insert or replace into game_chat (game_id, chat_id, user_id, time, message)
+	select game_id, chat_id, user_id, time, message
+	from archive.game_chat where game_id = $1;
+
+commit;
+
+EOF
diff --git a/bin/rtt-backup b/bin/rtt-backup
new file mode 100755
index 0000000..40a0efc
--- /dev/null
+++ b/bin/rtt-backup
@@ -0,0 +1,4 @@
+#!/bin/bash
+FILE=$(date +backup-%Y%m%d-%H%M.db)
+echo $FILE
+sqlite3 db "vacuum into '$FILE'"
diff --git a/bin/rtt-export b/bin/rtt-export
new file mode 100755
index 0000000..b82950c
--- /dev/null
+++ b/bin/rtt-export
@@ -0,0 +1,7 @@
+#!/bin/bash
+if [ -n "$1" ]
+then
+	sqlite3 db "select export from game_export_view where game_id=$1"
+else
+	echo "usage: rtt-export GAME > game.json"
+fi
diff --git a/bin/rtt-foreach b/bin/rtt-foreach
new file mode 100755
index 0000000..22feb77
--- /dev/null
+++ b/bin/rtt-foreach
@@ -0,0 +1,17 @@
+#!/bin/bash
+
+if [ -z "$1" ]
+then
+	echo "usage: rtt-foreach <command>"
+	exit 1
+fi
+
+for M in $(sqlite3 db "select title_id from titles")
+do
+	echo "Entering 'public/$M'"
+	if pushd public/$M >/dev/null
+	then
+		"$@"
+		popd >/dev/null
+	fi
+done
diff --git a/bin/rtt-fuzz b/bin/rtt-fuzz
new file mode 100755
index 0000000..d7f2aef
--- /dev/null
+++ b/bin/rtt-fuzz
@@ -0,0 +1,16 @@
+#!/bin/bash
+
+TITLE=$1
+shift
+
+if [ ! -f ./public/$TITLE/rules.js ]
+then
+	echo usage: rtt-fuzz TITLE
+	exit 1
+fi
+
+mkdir -p fuzzer/corpus-$TITLE
+
+RULES=../public/$TITLE/rules.js \
+	npx jazzer tools/fuzz.js --sync fuzzer/corpus-$TITLE "$@" -- -exact_artifact_path=/dev/null | \
+		tee fuzzer/log-$TITLE.txt
diff --git a/bin/rtt-help b/bin/rtt-help
new file mode 100755
index 0000000..5f6815c
--- /dev/null
+++ b/bin/rtt-help
@@ -0,0 +1,34 @@
+#!/bin/bash
+cat <<EOF
+usage: rtt <subcommand> [ arguments... ]
+
+database management
+	init	-- create database
+	run	-- run server
+	backup	-- backup database
+
+game management
+	export	-- export game
+	import	-- import game
+	patch	-- patch game state (using replay)
+	undo	-- rewind game state (using replay)
+
+module development
+	foreach		-- run a command for each module
+	fuzz		-- fuzz test a module
+
+game debugging
+	show-chat	-- show game chat (for moderation)
+	show-game	-- show game state object
+	show-replay	-- show game replay log
+
+miscellaneous tools
+	update-covers	-- generate cover thumbnails
+	update-elo	-- recalculate Elo ratings
+
+archive database
+	archive-backup	-- backup replay data into archive
+	archive-prune	-- prune replay data from live database
+	archive-restore	-- restore replay data from archive
+
+EOF
diff --git a/bin/rtt-import b/bin/rtt-import
new file mode 100755
index 0000000..2e4295b
--- /dev/null
+++ b/bin/rtt-import
@@ -0,0 +1,80 @@
+#!/usr/bin/env -S node
+
+const fs = require("fs")
+const sqlite3 = require("better-sqlite3")
+
+var options = {}
+var input = []
+
+for (let i = 2; i < process.argv.length; ++i) {
+	let opt = process.argv[i]
+	if (opt.includes("=")) {
+		let [key, val] = opt.split("=", 2)
+		options[key] = val
+	} else {
+		input.push(opt)
+	}
+}
+
+if (input.length < 1) {
+	console.error("usage: rtt-import [title_id=value] [notice=value] game-dump.json")
+	process.exit(1)
+}
+
+for (let file of input) {
+	var game = JSON.parse(fs.readFileSync(file, "utf8"))
+
+	if (options.title_id)
+		game.setup.title_id = options.title_id
+	if (options.notice)
+		game.setup.notice = options.notice
+
+	if (game.setup.notice === undefined)
+		game.setup.notice = ""
+	if (game.setup.options === undefined)
+		game.setup.options = "{}"
+
+	game.setup.active = String(game.state.active)
+	game.setup.moves = game.snaps && game.snaps.length > 0 ? game.snaps.length - 1 : 0
+
+	let db = new sqlite3("db")
+
+	let insert_game = db.prepare("insert into games(status,owner_id,title_id,scenario,options,player_count,active,moves,notice) values (1,1,:title_id,:scenario,:options,:player_count,:active,:moves,:notice) returning game_id").pluck()
+	let insert_player = db.prepare("insert into players(game_id,role,user_id,clock) values (?,?,?,21)")
+	let insert_state = db.prepare("insert into game_state(game_id,state) values (?,?)")
+	let update_active_trigger = db.prepare("update games set active=active where game_id=?")
+
+	let select_user = db.prepare("select user_id from users where name=?").pluck()
+
+	db.exec("begin")
+
+	game.setup.options = JSON.stringify(game.setup.options)
+
+	function find_user(name) {
+		return select_user.get(name) || 1
+	}
+
+	let game_id = insert_game.get(game.setup)
+	for (let p of game.players)
+		insert_player.run(game_id, p.role, find_user(p.name))
+	insert_state.run(game_id, JSON.stringify(game.state))
+	update_active_trigger.run(game_id)
+
+	if (game.replay) {
+		let insert_replay = db.prepare("insert into game_replay(game_id,replay_id,role,action,arguments) values (?,?,?,?,?)")
+		game.replay.forEach(([role, action, args], i) => {
+			insert_replay.run(game_id, i+1, role, action, JSON.stringify(args))
+		})
+	}
+
+	if (game.snaps) {
+		let insert_snap = db.prepare("insert into game_snap(game_id,snap_id,replay_id,state) values (?,?,?,?)")
+		game.snaps.forEach(([replay_id, state], i) => {
+			insert_snap.run(game_id, i+1, replay_id, JSON.stringify(state))
+		})
+	}
+
+	console.log(game_id)
+
+	db.exec("commit")
+}
diff --git a/bin/rtt-init b/bin/rtt-init
new file mode 100755
index 0000000..02b9e9b
--- /dev/null
+++ b/bin/rtt-init
@@ -0,0 +1,13 @@
+#!/bin/bash
+
+echo installing node packages
+npm install -s
+
+echo creating database
+sqlite3 db < schema.sql
+
+for M in public/*/title.sql
+do
+	echo registering module $(basename $(dirname $M))
+	sqlite3 db < $M
+done
diff --git a/bin/rtt-patch b/bin/rtt-patch
new file mode 100755
index 0000000..19e9874
--- /dev/null
+++ b/bin/rtt-patch
@@ -0,0 +1,291 @@
+#!/usr/bin/env -S node
+
+const sqlite3 = require("better-sqlite3")
+
+let db = new sqlite3("db")
+
+let select_game = db.prepare("select * from games where game_id=?")
+
+let select_replay = db.prepare("select * from game_replay where game_id=?")
+let delete_replay = db.prepare("delete from game_replay where game_id=?")
+let insert_replay = db.prepare("insert into game_replay (game_id,replay_id,role,action,arguments) values (?,?,?,?,?)")
+
+let delete_snap = db.prepare("delete from game_snap where game_id=?")
+let insert_snap = db.prepare("insert into game_snap(game_id,snap_id,replay_id,state) values (?,?,?,?)")
+
+let update_state = db.prepare("update game_state set state=? where game_id=?")
+let update_active = db.prepare("update games set active=? where game_id=?")
+
+let update_result = db.prepare("update games set status=?, result=? where game_id=?")
+
+const CRC32C_TABLE = new Int32Array([
+	0x00000000, 0xf26b8303, 0xe13b70f7, 0x1350f3f4, 0xc79a971f, 0x35f1141c, 0x26a1e7e8, 0xd4ca64eb,
+	0x8ad958cf, 0x78b2dbcc, 0x6be22838, 0x9989ab3b, 0x4d43cfd0, 0xbf284cd3, 0xac78bf27, 0x5e133c24,
+	0x105ec76f, 0xe235446c, 0xf165b798, 0x030e349b, 0xd7c45070, 0x25afd373, 0x36ff2087, 0xc494a384,
+	0x9a879fa0, 0x68ec1ca3, 0x7bbcef57, 0x89d76c54, 0x5d1d08bf, 0xaf768bbc, 0xbc267848, 0x4e4dfb4b,
+	0x20bd8ede, 0xd2d60ddd, 0xc186fe29, 0x33ed7d2a, 0xe72719c1, 0x154c9ac2, 0x061c6936, 0xf477ea35,
+	0xaa64d611, 0x580f5512, 0x4b5fa6e6, 0xb93425e5, 0x6dfe410e, 0x9f95c20d, 0x8cc531f9, 0x7eaeb2fa,
+	0x30e349b1, 0xc288cab2, 0xd1d83946, 0x23b3ba45, 0xf779deae, 0x05125dad, 0x1642ae59, 0xe4292d5a,
+	0xba3a117e, 0x4851927d, 0x5b016189, 0xa96ae28a, 0x7da08661, 0x8fcb0562, 0x9c9bf696, 0x6ef07595,
+	0x417b1dbc, 0xb3109ebf, 0xa0406d4b, 0x522bee48, 0x86e18aa3, 0x748a09a0, 0x67dafa54, 0x95b17957,
+	0xcba24573, 0x39c9c670, 0x2a993584, 0xd8f2b687, 0x0c38d26c, 0xfe53516f, 0xed03a29b, 0x1f682198,
+	0x5125dad3, 0xa34e59d0, 0xb01eaa24, 0x42752927, 0x96bf4dcc, 0x64d4cecf, 0x77843d3b, 0x85efbe38,
+	0xdbfc821c, 0x2997011f, 0x3ac7f2eb, 0xc8ac71e8, 0x1c661503, 0xee0d9600, 0xfd5d65f4, 0x0f36e6f7,
+	0x61c69362, 0x93ad1061, 0x80fde395, 0x72966096, 0xa65c047d, 0x5437877e, 0x4767748a, 0xb50cf789,
+	0xeb1fcbad, 0x197448ae, 0x0a24bb5a, 0xf84f3859, 0x2c855cb2, 0xdeeedfb1, 0xcdbe2c45, 0x3fd5af46,
+	0x7198540d, 0x83f3d70e, 0x90a324fa, 0x62c8a7f9, 0xb602c312, 0x44694011, 0x5739b3e5, 0xa55230e6,
+	0xfb410cc2, 0x092a8fc1, 0x1a7a7c35, 0xe811ff36, 0x3cdb9bdd, 0xceb018de, 0xdde0eb2a, 0x2f8b6829,
+	0x82f63b78, 0x709db87b, 0x63cd4b8f, 0x91a6c88c, 0x456cac67, 0xb7072f64, 0xa457dc90, 0x563c5f93,
+	0x082f63b7, 0xfa44e0b4, 0xe9141340, 0x1b7f9043, 0xcfb5f4a8, 0x3dde77ab, 0x2e8e845f, 0xdce5075c,
+	0x92a8fc17, 0x60c37f14, 0x73938ce0, 0x81f80fe3, 0x55326b08, 0xa759e80b, 0xb4091bff, 0x466298fc,
+	0x1871a4d8, 0xea1a27db, 0xf94ad42f, 0x0b21572c, 0xdfeb33c7, 0x2d80b0c4, 0x3ed04330, 0xccbbc033,
+	0xa24bb5a6, 0x502036a5, 0x4370c551, 0xb11b4652, 0x65d122b9, 0x97baa1ba, 0x84ea524e, 0x7681d14d,
+	0x2892ed69, 0xdaf96e6a, 0xc9a99d9e, 0x3bc21e9d, 0xef087a76, 0x1d63f975, 0x0e330a81, 0xfc588982,
+	0xb21572c9, 0x407ef1ca, 0x532e023e, 0xa145813d, 0x758fe5d6, 0x87e466d5, 0x94b49521, 0x66df1622,
+	0x38cc2a06, 0xcaa7a905, 0xd9f75af1, 0x2b9cd9f2, 0xff56bd19, 0x0d3d3e1a, 0x1e6dcdee, 0xec064eed,
+	0xc38d26c4, 0x31e6a5c7, 0x22b65633, 0xd0ddd530, 0x0417b1db, 0xf67c32d8, 0xe52cc12c, 0x1747422f,
+	0x49547e0b, 0xbb3ffd08, 0xa86f0efc, 0x5a048dff, 0x8ecee914, 0x7ca56a17, 0x6ff599e3, 0x9d9e1ae0,
+	0xd3d3e1ab, 0x21b862a8, 0x32e8915c, 0xc083125f, 0x144976b4, 0xe622f5b7, 0xf5720643, 0x07198540,
+	0x590ab964, 0xab613a67, 0xb831c993, 0x4a5a4a90, 0x9e902e7b, 0x6cfbad78, 0x7fab5e8c, 0x8dc0dd8f,
+	0xe330a81a, 0x115b2b19, 0x020bd8ed, 0xf0605bee, 0x24aa3f05, 0xd6c1bc06, 0xc5914ff2, 0x37faccf1,
+	0x69e9f0d5, 0x9b8273d6, 0x88d28022, 0x7ab90321, 0xae7367ca, 0x5c18e4c9, 0x4f48173d, 0xbd23943e,
+	0xf36e6f75, 0x0105ec76, 0x12551f82, 0xe03e9c81, 0x34f4f86a, 0xc69f7b69, 0xd5cf889d, 0x27a40b9e,
+	0x79b737ba, 0x8bdcb4b9, 0x988c474d, 0x6ae7c44e, 0xbe2da0a5, 0x4c4623a6, 0x5f16d052, 0xad7d5351
+])
+
+function crc32c(data) {
+	let x = 0
+	for (let i = 0, n = data.length; i < n; ++i)
+		x = CRC32C_TABLE[(x ^ data.charCodeAt(i)) & 0xff] ^ (x >>> 8)
+	return x ^ -1
+}
+
+function snapshot(state) {
+	let save_undo = state.undo
+	let save_log = state.log
+	state.undo = undefined
+	state.log = save_log.length
+	let snap = JSON.stringify(state)
+	state.undo = save_undo
+	state.log = save_log
+	return snap
+}
+
+function is_role_active(active, role) {
+	return active === role || active === "Both" || active.includes(role)
+}
+
+function is_nobody_active(active) {
+	return !active || active === "None"
+}
+
+function is_multi_active(active) {
+	if (!active)
+		return false
+	if (Array.isArray(active))
+		return true
+	return active === "Both" || active.includes(",")
+}
+
+function is_changed_active(old_active, new_active) {
+	return String(old_active) !== String(new_active)
+}
+
+function is_valid_action(rules, state, role, action, arg) {
+	if (action === "undo") // for jc, hots, r3, and cr compatibility
+		return true
+	if (!is_role_active(state.active, role))
+		return false
+	let view = rules.view(state, role)
+	let va = view.actions[action]
+	if (va) {
+		if (Array.isArray(va) && va.includes(arg))
+			return true
+		if (arg === undefined || arg === null || arg === 1 || Array.isArray(arg))
+			return (va === 1 || va === true || typeof va === "string")
+	}
+	return false
+}
+
+function dont_snap(rules, state, old_active) {
+	if (is_nobody_active(state.active))
+		return true
+	if (is_multi_active(old_active) && is_multi_active(state.active))
+		return true
+	if (!is_changed_active(old_active, state.active))
+		return true
+	if (rules.dont_snap && rules.dont_snap(state))
+		return true
+	return false
+}
+
+function get_game_roles(rules, scenario, options) {
+	let roles = rules.roles
+	if (typeof roles === "function") {
+		if (typeof options === "string")
+			options = JSON.parse(options)
+		return roles(scenario, options)
+	}
+	return roles
+}
+
+function get_resign_result(roles, role) {
+	return roles.filter(r => r !== role).join(", ")
+}
+
+function finish_game(rules, state, result, message) {
+	if (typeof rules.finish === "function") {
+		state = RULES[title_id].finish(state, result, message)
+	} else {
+		state.state = "game_over"
+		state.active = "None"
+		state.result = result
+		state.victory = message
+		state.log.push("")
+		state.log.push(message)
+	}
+	return state
+}
+
+function patch_game(game_id, {validate_actions=true, save_snaps=true, delete_undo=false, delete_invalid=false}, verbose) {
+	let game = select_game.get(game_id)
+	if (!game) {
+		console.error("game not found:", game_id)
+		return
+	}
+
+	let title_id = game.title_id
+	let rules = require("../public/" + title_id + "/rules.js")
+	let roles = get_game_roles(rules, game.scenario, game.options)
+
+	let replay = select_replay.all(game_id)
+	if (replay.length === 0)
+		return
+
+	console.log("processing", game_id, title_id)
+
+	try {
+		let state = null
+		let old_active = null
+		let need_to_rewrite = false
+
+		for (let i = 0; i < replay.length; ++i) {
+			let item = replay[i]
+
+			if (verbose)
+				console.log(item.replay_id, item.role, item.action, item.arguments)
+
+			let args = JSON.parse(item.arguments)
+			switch (item.action) {
+			case ".setup":
+				state = rules.setup(...args)
+				break
+			case ".timeout":
+				finish_game(rules, state, "None", item.role + " timed out.")
+				break
+			case ".abandon":
+				finish_game(rules, state, "None", item.role + " abandoned the game.")
+			case ".resign":
+				finish_game(rules, state, get_resign_result(roles, item.role), item.role + " resigned.")
+				break
+			default:
+				if (validate_actions) {
+					if (!is_valid_action(rules, state, item.role, item.action, args)) {
+						console.error(`invalid action: ${item.role} ${item.action} ${item.arguments}`)
+						console.error("\t", state.state, state.active, JSON.stringify(rules.view(state, item.role).actions))
+						if (i < replay.length) {
+							console.log("BROKEN ENTRIES: %d", replay.length-i)
+							console.log(`sqlite3 db "delete from game_replay where game_id=${game_id} and replay_id>=${replay[i].replay_id}"`)
+						}
+						throw "invalid action"
+					}
+				}
+				state = rules.action(state, item.role, item.action, args)
+				break
+			}
+
+			item.state = snapshot(state)
+			item.checksum = crc32c(item.state)
+			if (!dont_snap(rules, state, old_active))
+				item.save = 1
+			old_active = state.active
+
+			if (delete_undo) {
+				if (item.action === "undo") {
+					for (let k = i-1; k >= 0; --k) {
+						if (replay[k].checksum === item.checksum) {
+							need_to_rewrite = true
+							for (let z = k+1; z <= i; ++z)
+								replay[z].remove = 1
+							break
+						}
+					}
+				}
+			}
+		}
+
+		db.exec("begin")
+
+		if (need_to_rewrite) {
+			delete_replay.run(game_id)
+			for (item of replay)
+				if (!item.remove)
+					insert_replay.run(game_id, item.replay_id, item.role, item.action, item.arguments)
+		}
+
+		if (save_snaps) {
+			delete_snap.run(game_id)
+			let snap_id = 0
+			for (item of replay)
+				if (item.save)
+					insert_snap.run(game_id, ++snap_id, item.replay_id, item.state)
+		}
+
+		update_active.run(String(state.active), game_id)
+		update_state.run(JSON.stringify(state), game_id)
+
+		if (state.state === "game_over")
+			update_result.run(2, state.result, game_id)
+		else
+			update_result.run(1, null, game_id)
+
+		db.exec("commit")
+
+	} catch (err) {
+		if (err !== "invalid action")
+			console.error("ERROR", game_id, title_id, err)
+		if (delete_invalid) {
+			delete_replay.run(game_id)
+			delete_snap.run(game_id)
+		}
+	}
+}
+
+function patch_all(options) {
+	for (let game_id of db.prepare("select game_id from games where status=1").pluck().all())
+		patch_game(game_id, options, false)
+}
+
+function patch_title(title_id, options) {
+	for (let game_id of db.prepare("select game_id from games where status=1 and title_id=?").pluck().all(title_id))
+		patch_game(game_id, options, false)
+}
+
+if (process.argv.length < 3) {
+	process.stderr.write("usage: rtt-patch <game_id> '{options}'\n")
+	process.stderr.write("   or: rtt-patch <title_id> '{options}'\n")
+	process.stderr.write("   or: rtt-patch all '{options}'\n")
+	process.stderr.write('options: { "validate_actions":true, "delete_invalid":false, "save_snaps":true, "delete_undo":false }\n')
+	process.exit(1)
+}
+
+let options = {}
+if (process.argv.length === 4)
+	options = JSON.parse(process.argv[3])
+
+if (process.argv[2] === 'all')
+	patch_all(options)
+else if (isNaN(process.argv[2]))
+	patch_title(process.argv[2], options)
+else
+	patch_game(parseInt(process.argv[2]), options, true)
diff --git a/bin/rtt-run b/bin/rtt-run
new file mode 100755
index 0000000..6873e2f
--- /dev/null
+++ b/bin/rtt-run
@@ -0,0 +1,9 @@
+#!/bin/bash
+while true
+do
+	nodemon --exitcrash server.js
+	echo
+	echo "Restarting soon!"
+	echo "Hit Ctl-C to exit."
+	sleep 3
+done
diff --git a/bin/rtt-show-chat b/bin/rtt-show-chat
new file mode 100755
index 0000000..7d71718
--- /dev/null
+++ b/bin/rtt-show-chat
@@ -0,0 +1,7 @@
+#!/bin/bash
+if [ -n "$1" ]
+then
+	sqlite3 db ".mode column --wrap 40 -ww" "select time,name,message from game_chat_view where game_id=$1" | less -FX
+else
+	echo "usage: rtt-show-chat GAME"
+fi
diff --git a/bin/rtt-show-game b/bin/rtt-show-game
new file mode 100755
index 0000000..ece9e49
--- /dev/null
+++ b/bin/rtt-show-game
@@ -0,0 +1,7 @@
+#!/bin/bash
+if [ -n "$1" ]
+then
+	sqlite3 db "select json_remove(json_remove(state, '$.undo'), '$.log') from game_state where game_id = $1"
+else
+	echo "usage: rtt-show-state GAME"
+fi
diff --git a/bin/rtt-show-replay b/bin/rtt-show-replay
new file mode 100755
index 0000000..4bbbdce
--- /dev/null
+++ b/bin/rtt-show-replay
@@ -0,0 +1,7 @@
+#!/bin/bash
+if [ -n "$1" ]
+then
+	sqlite3 db "select replay_id, role, action, arguments from game_replay where game_id=$1"
+else
+	echo "usage: rtt-show-replay GAME"
+fi
diff --git a/bin/rtt-tm-unban-tick b/bin/rtt-tm-unban-tick
new file mode 100755
index 0000000..7294e81
--- /dev/null
+++ b/bin/rtt-tm-unban-tick
@@ -0,0 +1,32 @@
+#!/bin/bash
+
+# Run this script in a cron job to lift tournament bans periodically.
+
+sqlite3 db <<EOF
+
+begin immediate;
+
+.mode column
+
+create temporary view tm_lift_ban_view as
+	select
+		user_id,
+		name,
+		date(timeout_last),
+		timeout_total,
+		games_since_timeout,
+		(games_since_timeout > timeout_total) and (julianday() > julianday(timeout_last)+14) as lift_ban
+	from
+		user_profile_view
+	where
+		user_id in (select user_id from tm_banned)
+	order by lift_ban desc, timeout_last asc
+;
+
+select * from tm_lift_ban_view;
+
+delete from tm_banned where user_id in (select user_id from tm_lift_ban_view where lift_ban) returning user_id;
+
+commit;
+
+EOF
diff --git a/bin/rtt-undo b/bin/rtt-undo
new file mode 100755
index 0000000..2f69eed
--- /dev/null
+++ b/bin/rtt-undo
@@ -0,0 +1,15 @@
+#!/bin/bash
+if [ -n "$1" ]
+then
+	if [ -n "$2" ]
+	then
+		COUNT=$2
+	else
+		COUNT=$(sqlite3 db "select coalesce(max(replay_id),0) from game_replay where game_id=$1")
+		echo Game has $COUNT actions.
+	fi
+	sqlite3 db "delete from game_replay where game_id=$1 and replay_id>=$COUNT"
+	./bin/rtt-patch $1 '{"validate_actions":false}'
+else
+	echo "usage: rtt-undo GAME"
+fi
diff --git a/bin/rtt-update-covers b/bin/rtt-update-covers
new file mode 100755
index 0000000..0eae8e7
--- /dev/null
+++ b/bin/rtt-update-covers
@@ -0,0 +1,53 @@
+#!/bin/bash
+
+if command -v convert && command -v pngtopnm && command -v cjpeg
+then
+	echo "Generating cover images and thumbnails!"
+else
+	echo error: cannot find imagemagick, netpbm, and/or cjpeg
+	exit 1
+fi
+
+for F in public/*/cover.jpg public/*/cover.png
+do
+	echo processing $F
+	B=$(echo $F | sed s/.jpg// | sed s/.png//)
+	D=$(dirname $F)
+
+	if [ $F -nt $D/thumbnail.png ]
+	then
+
+	PORTRAIT=$(convert $F -format '%[fx:w<h]' info:)
+	SQUARE=$(convert $F -format '%[fx:w=h]' info:)
+
+	SIZE_1X=170x200
+	SIZE_2X=340x400
+	SIZE_TH=120x144
+
+	if test $PORTRAIT = 1
+	then
+		echo - portrait
+		SIZE_1X=150x200!
+		SIZE_2X=300x400!
+		SIZE_TH=108x144!
+	fi
+
+	if test $SQUARE = 1
+	then
+		echo - square
+		SIZE_1X=170x170!
+		SIZE_2X=170x170!
+		SIZE_TH=120x120!
+	fi
+
+	convert -colorspace RGB -resize $SIZE_TH $F -colorspace sRGB $D/thumbnail.png
+	convert -colorspace RGB -resize $SIZE_1X -colorspace sRGB $F $B.1x.png
+	convert -colorspace RGB -resize $SIZE_2X $F -colorspace sRGB $B.2x.png
+
+	fi
+
+	pngtopnm $D/thumbnail.png | cjpeg -progressive -optimize -sample 1x1 -quality 95 > $D/thumbnail.jpg
+	pngtopnm $B.1x.png | cjpeg -progressive -optimize -sample 1x1 -quality 95 > $B.1x.jpg
+	pngtopnm $B.2x.png | cjpeg -progressive -optimize -sample 1x1 -quality 95 > $B.2x.jpg
+
+done
diff --git a/bin/rtt-update-elo b/bin/rtt-update-elo
new file mode 100755
index 0000000..538964a
--- /dev/null
+++ b/bin/rtt-update-elo
@@ -0,0 +1,66 @@
+#!/usr/bin/env -S node
+
+// Recompute Elo ratings from scratch!
+
+const sqlite3 = require("better-sqlite3")
+
+const db = new sqlite3("db")
+
+const SQL_SELECT_GAMES = db.prepare("select * from rated_games_view order by mtime")
+const SQL_SELECT_RATING = db.prepare("select * from player_rating_view where game_id=?")
+const SQL_INSERT_RATING = db.prepare("insert or replace into ratings (title_id,user_id,rating,count,last) values (?,?,?,?,?)")
+
+function is_winner(role, result) {
+	// NOTE: uses substring matching for multiple winners instead of splitting result on comma.
+	return (result === "Draw" || result === role || result.includes(role))
+}
+
+function elo_k(n) {
+	return 30
+}
+
+function elo_ev(a, players) {
+	// Generalized formula for multiple players.
+	// https://arxiv.org/pdf/2104.05422.pdf
+	let sum = 0
+	for (let p of players)
+		sum += Math.pow(10, p.rating / 400)
+	return Math.pow(10, a.rating / 400) / sum
+}
+
+function elo_change(a, players, s) {
+	return Math.round(elo_k(a.count) * (s - elo_ev(a, players)))
+}
+
+function update_elo_ratings(game) {
+	let players = SQL_SELECT_RATING.all(game.game_id)
+
+	if (game.player_count !== players.length)
+		return
+
+	if (!game.result || game.result === "None")
+		return
+
+	let winners = 0
+	for (let p of players)
+		if (is_winner(p.role, game.result))
+			winners ++
+
+	if (winners === 0)
+		return
+
+	for (let p of players)
+		if (is_winner(p.role, game.result))
+			p.change = elo_change(p, players, 1 / winners)
+		else
+			p.change = elo_change(p, players, 0)
+
+	for (let p of players)
+		SQL_INSERT_RATING.run(game.title_id, p.user_id, p.rating + p.change, p.count + 1, game.mtime)
+}
+
+db.exec("begin transaction")
+db.exec("delete from ratings")
+for (let game of SQL_SELECT_GAMES.all())
+	update_elo_ratings(game)
+db.exec("commit")