I'm not sure what you mean by "not transactional". SQLite implements transaction support at the "page" level, and builds all other database operations on top of it, which means anything that touches the bytes of the database file is transaction-safe. You can verify this for yourself:

    sqlite> CREATE TABLE foo(a,b,c);
    sqlite> INSERT INTO foo VALUES (1,2,3);
    sqlite> BEGIN;
    sqlite> ALTER TABLE foo DROP COLUMN b;
    sqlite> SELECT * FROM foo;
    1|3
    sqlite> ROLLBACK;
    sqlite> SELECT * FROM foo;
    1|2|3

It's of course still subject to SQLite's normal restrictions on locking, which means a long-running ALTER statement will block concurrent writers (and probably also concurrent readers if you're not running in WAL mode).

> I'm surprised a database schema lasted 9/12 years without ever renaming or dropping a column.

I did have a couple of columns that were no longer needed and would have dropped them, but instead I just set them to null and ignored them. Nulls only take 1 byte of space in a row. I dropped them when DROP COLUMN was added.