for bluesky pds, when the underlying blobstore gets out of sync with actor's db, to fix media load issues

pds-fix-blobstore-db.mjs

import * as fs from "node:fs";
import * as crypto from "node:crypto";
import * as path from "node:path";
import { DatabaseSync } from 'node:sqlite'

const BLOBSTORE = '/srv/bluesky-pds/blobstore';
const DATA_DIR = '/srv/bluesky-pds/data';
const DID = 'did:web:tei.su'

const didHash = crypto.createHash('sha256').update(DID).digest('hex').slice(0, 2);
const didBlobstore = path.join(BLOBSTORE, DID);
const actorDbPath = path.join(DATA_DIR, 'actors', didHash, DID, 'store.sqlite')

const actorDb = new DatabaseSync(actorDbPath);

const allBlobs = actorDb.prepare('SELECT * FROM `blob`').all();

const deleteBlobStmt = actorDb.prepare('DELETE FROM `blob` WHERE `cid` = ?');
const updateSizeStmt = actorDb.prepare('UPDATE `blob` SET `size` = ? WHERE `cid` = ?');

for (const blob of allBlobs) {
    const fsPath = path.join(didBlobstore, blob.cid);
    try {
        const stat = fs.statSync(fsPath);
        if (stat.size !== blob.size) {
            console.log('%s: size mismatch, updating', blob.cid);
            updateSizeStmt.run(stat.size, blob.cid);
            continue;
        }

        console.log('%s: ok', blob.cid);
    } catch (e) {
        if (e.code !== 'ENOENT') {
            throw e;
        }

        console.log('%s: doesnt exist, deleting', blob.cid);
        deleteBlobStmt.run(blob.cid);
    }
}

ideally we should also fix mime and image width/height in the meta, but this should be enough to at least make shit load properly without issues