There's a really good list of lessons in the second half of this page which I think are really interesting (in general, but also in specific for this project). I don't have a Lobste.rs account but I have some comments about some of them so I'll add them here

* If you want to fail on full table scans in tests you could take advantage of EXPLAIN - though, because it relies on the built in analysis/stats this may exactly mimic production tables. I believe Ruby is dynamic enough that you could probably attach an EXPLAIN to all queries in the unit tests [0] and parse the output.

* For the three UDFs - Sqlite has a regexp module that you can build and load rather than using a udf in rails (probably a different version of regexp though, so user beware) [1] there is also an extensions_functions.c in the external contributions that provides 'stddev' [2] (or you could fake it with the percentile module). I'm confused why there's a udf for 'if' but it'd be trivial to build as C module, but I agree it's best to patch over this sort of thing separately from the migration, so perhaps limited benefit.

* 'I'm constantly surprised by the default choices of SQLite.' I think this is fair, but they're quite dedicated in not breaking existing installations, they even have pages dedicated to explaining some peculiar architecture [3]

[0] https://www.sqlite.org/eqp.html EDIT: there's an alternative suggestion here - https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlit...

[1] https://sqlite.org/src/file/ext/misc/regexp.c

[2] https://sqlite.org/src/ext/contrib/ (ctrl-f for 'extensions-functions.c'. It has more than just stddev but you could strip the rest)

[3] https://www.sqlite.org/rowidtable.html I find this a really touching note 'The designer of SQLite offers his sincere apology for the current mess'