Something went wrong. Try again.
Ghost is lightweight headless server that enables Ghost self hosters to bridge to standard.site with ease..
Something went wrong. Try again.
8.2 kB · 219 lines
TypeScript
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220// 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<Site, 'id' | 'created_at' | 'updated_at' | 'publication_at_uri'>;export type SiteUpdates = Partial<Omit<Site, 'id' | 'slug' | 'created_at' | 'updated_at'>>;
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;}