// SQLite-backed persistence for ghoststandard. // // Multi-site: one ghoststandard instance can bridge many Ghost blogs to many // AT Protocol publications. Each blog is a row in `sites`; per-post records // are scoped via the site_id column on `post_mapping`. // // Tables: // sites — one row per Ghost↔PDS pairing the operator has set up. // Holds all per-tenant credentials and publication metadata. // post_mapping — ghost_post_id ↔ AT-URI, scoped by site_id. // config — single-row key/value for genuinely global settings // (the bridge's public URL; "any site set up yet?" flag). import Database from 'better-sqlite3'; import { mkdirSync } from 'node:fs'; import { dirname } from 'node:path'; const DB_PATH = process.env.GS_DB_PATH ?? './data/ghoststandard.sqlite'; mkdirSync(dirname(DB_PATH), { recursive: true }); export const db = new Database(DB_PATH); db.pragma('journal_mode = WAL'); db.pragma('foreign_keys = ON'); db.exec(` CREATE TABLE IF NOT EXISTS config ( key TEXT PRIMARY KEY, value TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS sites ( id INTEGER PRIMARY KEY AUTOINCREMENT, slug TEXT UNIQUE NOT NULL, name TEXT NOT NULL, atproto_service TEXT NOT NULL, atproto_handle TEXT NOT NULL, atproto_app_password TEXT NOT NULL, ghost_url TEXT NOT NULL, ghost_admin_api_key TEXT NOT NULL, webhook_secret TEXT NOT NULL, publication_name TEXT NOT NULL, publication_description TEXT, publication_theme TEXT, publication_icon_path TEXT, publication_at_uri TEXT, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS post_mapping ( site_id INTEGER NOT NULL REFERENCES sites(id) ON DELETE CASCADE, ghost_post_id TEXT NOT NULL, at_uri TEXT NOT NULL, synced_at INTEGER NOT NULL, PRIMARY KEY (site_id, ghost_post_id) ); CREATE INDEX IF NOT EXISTS post_mapping_site_idx ON post_mapping(site_id); `); // --------------------------------------------------------------------------- // Global config (bridge_url, setup_complete-ish flags) // --------------------------------------------------------------------------- const _configGet = db.prepare<[string], { value: string }>('SELECT value FROM config WHERE key = ?'); const _configSet = db.prepare(` INSERT INTO config (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value `); const _configDelete = db.prepare('DELETE FROM config WHERE key = ?'); export function getConfig(key: string): string | null { return _configGet.get(key)?.value ?? null; } export function setConfig(key: string, value: string): void { _configSet.run(key, value); } export function deleteConfig(key: string): void { _configDelete.run(key); } // --------------------------------------------------------------------------- // Site CRUD // --------------------------------------------------------------------------- export interface Site { id: number; slug: string; name: string; atproto_service: string; atproto_handle: string; atproto_app_password: string; ghost_url: string; ghost_admin_api_key: string; webhook_secret: string; publication_name: string; publication_description: string | null; publication_theme: string | null; publication_icon_path: string | null; publication_at_uri: string | null; created_at: number; updated_at: number; } export type NewSite = Omit; export type SiteUpdates = Partial>; const _siteCols = ` id, slug, name, atproto_service, atproto_handle, atproto_app_password, ghost_url, ghost_admin_api_key, webhook_secret, publication_name, publication_description, publication_theme, publication_icon_path, publication_at_uri, created_at, updated_at `; const _insertSite = db.prepare(` INSERT INTO sites ( slug, name, atproto_service, atproto_handle, atproto_app_password, ghost_url, ghost_admin_api_key, webhook_secret, publication_name, publication_description, publication_theme, publication_icon_path, created_at, updated_at ) VALUES ( @slug, @name, @atproto_service, @atproto_handle, @atproto_app_password, @ghost_url, @ghost_admin_api_key, @webhook_secret, @publication_name, @publication_description, @publication_theme, @publication_icon_path, @now, @now ) `); const _getSiteBySlug = db.prepare<[string], Site>(`SELECT ${_siteCols} FROM sites WHERE slug = ?`); const _getSiteById = db.prepare<[number], Site>(`SELECT ${_siteCols} FROM sites WHERE id = ?`); const _listSites = db.prepare<[], Site>(`SELECT ${_siteCols} FROM sites ORDER BY created_at ASC`); const _deleteSite = db.prepare('DELETE FROM sites WHERE id = ?'); const _countSites = db.prepare<[], { c: number }>('SELECT COUNT(*) AS c FROM sites'); export function insertSite(s: NewSite): Site { const now = Date.now(); const info = _insertSite.run({ ...s, publication_description: s.publication_description ?? null, publication_theme: s.publication_theme ?? null, publication_icon_path: s.publication_icon_path ?? null, now, }); return getSiteById(Number(info.lastInsertRowid))!; } export function getSiteBySlug(slug: string): Site | null { return _getSiteBySlug.get(slug) ?? null; } export function getSiteById(id: number): Site | null { return _getSiteById.get(id) ?? null; } export function listSites(): Site[] { return _listSites.all(); } export function countSites(): number { return _countSites.get()?.c ?? 0; } export function deleteSite(id: number): void { _deleteSite.run(id); } // Partial update — we build the SQL dynamically since each setter touches a // different subset. updated_at is always bumped. export function updateSite(id: number, updates: SiteUpdates): Site { const keys = Object.keys(updates) as (keyof SiteUpdates)[]; if (keys.length === 0) return getSiteById(id)!; const set = keys.map((k) => `${k} = @${k}`).join(', '); const stmt = db.prepare(`UPDATE sites SET ${set}, updated_at = @updated_at WHERE id = @id`); stmt.run({ ...updates, id, updated_at: Date.now() }); return getSiteById(id)!; } // --------------------------------------------------------------------------- // post_mapping helpers — site-scoped // --------------------------------------------------------------------------- const _upsertMapping = db.prepare(` INSERT INTO post_mapping (site_id, ghost_post_id, at_uri, synced_at) VALUES (?, ?, ?, ?) ON CONFLICT(site_id, ghost_post_id) DO UPDATE SET at_uri = excluded.at_uri, synced_at = excluded.synced_at `); const _getMapping = db.prepare<[number, string], { at_uri: string; synced_at: number }>( 'SELECT at_uri, synced_at FROM post_mapping WHERE site_id = ? AND ghost_post_id = ?' ); const _deleteMapping = db.prepare('DELETE FROM post_mapping WHERE site_id = ? AND ghost_post_id = ?'); const _countMappings = db.prepare<[number], { c: number }>('SELECT COUNT(*) AS c FROM post_mapping WHERE site_id = ?'); const _lastSynced = db.prepare<[number], { last: number | null }>('SELECT MAX(synced_at) AS last FROM post_mapping WHERE site_id = ?'); export function upsertPostMapping(siteId: number, ghostPostId: string, atUri: string): void { _upsertMapping.run(siteId, ghostPostId, atUri, Date.now()); } export function getPostMapping(siteId: number, ghostPostId: string): { at_uri: string; synced_at: number } | null { return _getMapping.get(siteId, ghostPostId) ?? null; } export function deletePostMapping(siteId: number, ghostPostId: string): void { _deleteMapping.run(siteId, ghostPostId); } export function countPostMappings(siteId: number): number { return _countMappings.get(siteId)?.c ?? 0; } export function lastPostMappingSyncedAt(siteId: number): number | null { return _lastSynced.get(siteId)?.last ?? null; }