Some notes on how I handled manually cleaning up the database file. This needs to be automated later...

apk add sqlite
cd /data
sqlite3
.open coffee.db
ATTACH DATABASE 'archive.db' AS archive;
.schema post
CREATE TABLE IF NOT EXISTS archive."post" ("uri" varchar primary key, "cid" varchar not null, "topic" varchar not null, "indexedAt" varchar not null);
SELECT COUNT(*) FROM post WHERE indexedAt < '2025-06-01T00:00:00Z';
INSERT INTO archive.post SELECT * FROM post WHERE indexedAt < '2025-06-01T00:00:00Z';
DETACH DATABASE archive;
DELETE FROM post WHERE indexedAt < '2025-06-01T00:00:00Z';

apk add sqlite
cd /data
rm archive.db archive.db-journal 
cp archive.db.bak archive.db
sqlite3
.open coffee.db
ATTACH DATABASE 'archive.db' AS archive;
INSERT INTO archive.post SELECT * FROM post WHERE indexedAt < '2025-06-15T00:00:00Z';
DETACH DATABASE archive;
DELETE FROM post WHERE indexedAt < '2025-06-15T00:00:00Z';
.exit
cp archive.db archive.db.bak

32876d22b7d238:/data# apk add sqlite
fetch https://dl-cdn.alpinelinux.org/alpine/v3.21/main/x86_64/APKINDEX.tar.gz
fetch https://dl-cdn.alpinelinux.org/alpine/v3.21/community/x86_64/APKINDEX.tar.gz
(1/4) Installing ncurses-terminfo-base (6.5_p20241006-r3)
(2/4) Installing libncursesw (6.5_p20241006-r3)
(3/4) Installing readline (8.2.13-r0)
(4/4) Installing sqlite (3.48.0-r4)
Executing busybox-1.37.0-r12.trigger
OK: 10 MiB in 21 packages
32876d22b7d238:/data# sqlite3
SQLite version 3.48.0 2025-01-14 11:05:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open coffee.db
sqlite> ATTACH DATABASE 'archive.db' AS archive;
sqlite> .schema post
CREATE TABLE IF NOT EXISTS "post" ("uri" varchar primary key, "cid" varchar not null, "topic" varchar not null, "indexedAt" varchar not null);
sqlite> CREATE TABLE IF NOT EXISTS archive."post" ("uri" varchar primary key, "cid" varchar not null, "topic" varchar not null, "indexedAt" varchar not null);
sqlite> SELECT COUNT(*) FROM post WHERE indexedAt < '2025-06-01T00:00:00Z';
35438
sqlite> INSERT INTO archive.post SELECT * FROM post WHERE indexedAt < '2025-06-01T00:00:00Z';
sqlite> DETACH DATABASE archive;
sqlite> .exit
# because this crashed quite a few times...
32876d22b7d238:/data# cp archive.db archive.db.bak

# ... later
32876d22b7d238:/data# sqlite3
SQLite version 3.48.0 2025-01-14 11:05:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open coffee.db
sqlite> ATTACH DATABASE 'archive.db' AS archive;
sqlite> SELECT COUNT(*) FROM post WHERE indexedAt < '2025-06-29T02:43:00Z';
188598
sqlite> INSERT INTO archive.post SELECT * FROM post WHERE indexedAt < '2025-06-15T00:00:00Z';
sqlite> DETACH DATABASE archive;
sqlite> DELETE FROM post WHERE indexedAt < '2025-06-15T00:00:00Z';