If anyone is still using sql lite I cannot (again) recommend enough migrating to postgres with Karl's excellent utility, or just starting again.
I have had issues in the past recovering from crashes with sql lite but postgres has been very forgiving of hard power off in the middle of use if the mac just siezes up.
However a days ago the drive failed and I had to do a full timemachine recovery of the drive. All was good except 3 device tables where I was getting a read error.
SQL Logger Error exception trying to insert row into table device_history_612976019 ("Kitchen UnifiAP") -- unable to modify table
SQL Logger Error ERROR: index "device_history_612976019_pkey" contains unexpected zero page at block 8596
SQL Logger Error INSERT INTO device_history_612976019 ("mac", "channel_2", "channel_5", "created", "displaystatus", "essid_2", "essid_5", "groupmember", "hostname", "ipnumber", "laststatuschange", "model", "nstations_2", "nstations_5", "radio_2", "radio_5", "status", "tx_power_2", "tx_power_5", "updownsetting", "upsince", "vendor") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Error exception trying to insert row into table device_history_1588585220 ("Landing UnifiAP") -- unable to modify table
SQL Logger Error ERROR: index "device_history_1588585220_pkey" contains unexpected zero page at block 7285
I use pgAdmin4 and was getting the same error there when trying to read the whole table, however I realised that I could export it all, which I did, and couldnt see anything wrong in Excel.
I then did a table export, truncated the table to delete all rows, leaving column setup, and then imported the file I had exported. I guess it refreshed some indicies or something? but those tables then started working properly.
Best guess is that I would have lost the tables in sqllite, if not the whole database.
ps I did also have to delete the postmaster.pid file as postgres refused to start initially on the new machine, but after that it was all good.