Something went wrong. Try again.
An easy to set up and operate PDS admin panel. forked from brookie.blog/pds-operator
Something went wrong. Try again.
7.0 kB · 202 lines
TypeScript
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203import Database from "better-sqlite3";import path from "node:path";import { fileURLToPath } from "node:url";import { createHash, randomBytes } from "node:crypto";
export interface AccountRow { did: string; handle: string; email: string | null; indexed_at: string; status: "active" | "takendown" | "deactivated"; avatar: string | null; rev: string; repo_bytes: number | null; blob_bytes: number | null; storage_rev: string | null; storage_at: number | null;}
export interface LabelRow { did: string; src: string; val: string; cts: string;}
export function openDb(file?: string) { const dbPath = file ?? process.env.DB_PATH ?? path.join(path.dirname(fileURLToPath(import.meta.url)), "../data.sqlite"); const db = new Database(dbPath); db.pragma("journal_mode = WAL"); db.exec(` CREATE TABLE IF NOT EXISTS accounts ( did TEXT PRIMARY KEY, handle TEXT NOT NULL DEFAULT '(unknown)', email TEXT, indexed_at TEXT NOT NULL DEFAULT '', status TEXT NOT NULL DEFAULT 'active', avatar TEXT, updated_at INTEGER NOT NULL ); CREATE INDEX IF NOT EXISTS accounts_indexed_at ON accounts(indexed_at DESC);
CREATE TABLE IF NOT EXISTS labels ( did TEXT NOT NULL, src TEXT NOT NULL, val TEXT NOT NULL, cts TEXT NOT NULL, PRIMARY KEY (did, src, val) ); CREATE INDEX IF NOT EXISTS labels_did ON labels(did);
-- per-account commit counts from the firehose, bucketed by UTC hour ("YYYY-MM-DDTHH") -- so the stats API can regroup them into days in the viewer's timezone. Unlike the -- rest of this file, activity is NOT rebuildable from the PDS — counting starts when -- the server first runs and history is lost if the file is deleted. CREATE TABLE IF NOT EXISTS activity ( hour TEXT NOT NULL, did TEXT NOT NULL, events INTEGER NOT NULL, PRIMARY KEY (hour, did) );
CREATE TABLE IF NOT EXISTS sync_state ( key TEXT PRIMARY KEY, value TEXT NOT NULL );
-- per-blob sizes, fetched once per cid (blobs are immutable) so the storage -- sweep only pays for new blobs CREATE TABLE IF NOT EXISTS blob_sizes ( did TEXT NOT NULL, cid TEXT NOT NULL, bytes INTEGER NOT NULL, PRIMARY KEY (did, cid) );
CREATE TABLE IF NOT EXISTS passkeys ( id TEXT PRIMARY KEY, public_key BLOB NOT NULL, counter INTEGER NOT NULL, transports TEXT, name TEXT, created_at INTEGER NOT NULL );
-- named admins, keyed by their atproto DID. enrolled_at is set once they have -- proven the DID via OAuth and created their passkey; null means invited only CREATE TABLE IF NOT EXISTS operators ( did TEXT PRIMARY KEY, handle TEXT NOT NULL, added_at INTEGER NOT NULL, enrolled_at INTEGER );
CREATE TABLE IF NOT EXISTS enroll_tokens ( token_hash TEXT PRIMARY KEY, expires_at INTEGER NOT NULL );
-- the record-purge feature was removed; drop its table where it lingers DROP TABLE IF EXISTS purge_jobs; `);
const addColumn = (table: string, name: string, ddl: string) => { const has = db .prepare(`SELECT COUNT(*) AS n FROM pragma_table_info('${table}') WHERE name = ?`) .get(name) as { n: number }; if (has.n === 0) db.exec(`ALTER TABLE ${table} ADD COLUMN ${ddl}`); }; // storage columns: rev tracks the repo head and storage_at the last measurement, // so the sweep only re-measures changed repos, at most so often addColumn("accounts", "rev", "rev TEXT NOT NULL DEFAULT ''"); addColumn("accounts", "repo_bytes", "repo_bytes INTEGER"); addColumn("accounts", "blob_bytes", "blob_bytes INTEGER"); addColumn("accounts", "storage_rev", "storage_rev TEXT"); addColumn("accounts", "storage_at", "storage_at INTEGER"); // passkeys enrolled through the admin OAuth flow belong to a named operator; // legacy CLI-enrolled passkeys keep a null owner and sign in as "operator" addColumn("passkeys", "operator_did", "operator_did TEXT"); // enroll tokens minted by the OAuth callback carry the verified DID, so the // enrollment can hop from the 127.0.0.1 callback origin back to the dashboard // origin without relying on a session cookie addColumn("enroll_tokens", "operator_did", "operator_did TEXT");
// activity was briefly bucketed by whole day; re-home those rows at UTC midnight const legacyDayColumn = db .prepare("SELECT COUNT(*) AS n FROM pragma_table_info('activity') WHERE name = 'day'") .get() as { n: number }; if (legacyDayColumn.n > 0) { db.exec(` ALTER TABLE activity RENAME TO activity_legacy; CREATE TABLE activity ( hour TEXT NOT NULL, did TEXT NOT NULL, events INTEGER NOT NULL, PRIMARY KEY (hour, did) ); INSERT INTO activity (hour, did, events) SELECT day || 'T00', did, events FROM activity_legacy; DROP TABLE activity_legacy; `); } return db;}
export type Db = ReturnType<typeof openDb>;
export function getSyncState(db: Db, key: string): string | null { const row = db.prepare("SELECT value FROM sync_state WHERE key = ?").get(key) as | { value: string } | undefined; return row?.value ?? null;}
export function setSyncState(db: Db, key: string, value: string) { db.prepare( "INSERT INTO sync_state (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value", ).run(key, value);}
// ---- one-time passkey enrollment tokens (minted by the CLI, hashed at rest) ----
const ENROLL_TOKEN_TTL_MS = 15 * 60 * 1000;
function hashToken(token: string): string { return createHash("sha256").update(token).digest("hex");}
export function createEnrollToken( db: Db, operatorDid: string | null = null,): { token: string; expiresAt: number } { db.prepare("DELETE FROM enroll_tokens WHERE expires_at <= ?").run(Date.now()); const token = randomBytes(32).toString("base64url"); const expiresAt = Date.now() + ENROLL_TOKEN_TTL_MS; db.prepare( "INSERT INTO enroll_tokens (token_hash, expires_at, operator_did) VALUES (?, ?, ?)", ).run(hashToken(token), expiresAt, operatorDid); return { token, expiresAt };}
/** Valid-token lookup without consuming it. */export function peekEnrollToken(db: Db, token: string): { operatorDid: string | null } | null { const row = db .prepare("SELECT operator_did FROM enroll_tokens WHERE token_hash = ? AND expires_at > ?") .get(hashToken(token), Date.now()) as { operator_did: string | null } | undefined; return row ? { operatorDid: row.operator_did } : null;}
export function checkEnrollToken(db: Db, token: string): boolean { return peekEnrollToken(db, token) != null;}
export function consumeEnrollToken(db: Db, token: string): { operatorDid: string | null } | null { const found = peekEnrollToken(db, token); if (!found) return null; db.prepare("DELETE FROM enroll_tokens WHERE token_hash = ?").run(hashToken(token)); return found;}