From 493746b14c1251a45b061d2e3edd9160c929d2b9 Mon Sep 17 00:00:00 2001 From: benj Date: Fri, 10 Apr 2026 11:13:34 +0800 Subject: a basic ui and landing web interface for tidyindex.com --- web/ui/src/lib/server/db.ts | 233 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 233 insertions(+) create mode 100644 web/ui/src/lib/server/db.ts (limited to 'web/ui/src/lib/server/db.ts') diff --git a/web/ui/src/lib/server/db.ts b/web/ui/src/lib/server/db.ts new file mode 100644 index 0000000..ee5736d --- /dev/null +++ b/web/ui/src/lib/server/db.ts @@ -0,0 +1,233 @@ +import Database from 'better-sqlite3'; +import { mkdirSync } from 'node:fs'; +import { dirname, resolve } from 'node:path'; +import { randomUUID } from 'node:crypto'; +import { env } from '$env/dynamic/private'; + +import type { PlanId } from '$lib/plans'; + +const DB_PATH = resolve(env.DATABASE_PATH || './data/dashboard.db'); + +// Make sure the data/ directory exists before better-sqlite3 tries to open. +mkdirSync(dirname(DB_PATH), { recursive: true }); + +export const db = new Database(DB_PATH); +db.pragma('journal_mode = WAL'); +db.pragma('foreign_keys = ON'); + +// -------- schema -------- + +db.exec(` + CREATE TABLE IF NOT EXISTS accounts ( + id TEXT PRIMARY KEY, + email TEXT UNIQUE, + pending_email TEXT, + plan TEXT NOT NULL DEFAULT 'free', + created_at INTEGER NOT NULL + ); + + CREATE TABLE IF NOT EXISTS api_keys ( + id TEXT PRIMARY KEY, + account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, + key_hash TEXT NOT NULL UNIQUE, + key_prefix TEXT NOT NULL, + name TEXT NOT NULL, + scopes TEXT NOT NULL DEFAULT '["*"]', + active INTEGER NOT NULL DEFAULT 1, + created_at INTEGER NOT NULL, + last_used_at INTEGER + ); + CREATE INDEX IF NOT EXISTS idx_api_keys_account ON api_keys(account_id); + + CREATE TABLE IF NOT EXISTS magic_links ( + id TEXT PRIMARY KEY, + account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, + token TEXT NOT NULL UNIQUE, + expires_at INTEGER NOT NULL, + used INTEGER NOT NULL DEFAULT 0, + created_at INTEGER NOT NULL + ); + CREATE INDEX IF NOT EXISTS idx_magic_links_token ON magic_links(token); + + CREATE TABLE IF NOT EXISTS usage_events ( + id TEXT PRIMARY KEY, + api_key_id TEXT NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE, + dataset TEXT NOT NULL, + timestamp INTEGER NOT NULL + ); + CREATE INDEX IF NOT EXISTS idx_usage_key ON usage_events(api_key_id); + CREATE INDEX IF NOT EXISTS idx_usage_timestamp ON usage_events(timestamp); + CREATE INDEX IF NOT EXISTS idx_usage_dataset ON usage_events(dataset); +`); + +// Idempotent migration: pre-existing dashboard.db files won't have +// pending_email yet. SQLite has no IF NOT EXISTS for ALTER, so check +// PRAGMA table_info first. +{ + const cols = db + .prepare(`PRAGMA table_info(accounts)`) + .all() as Array<{ name: string }>; + if (!cols.some((c) => c.name === 'pending_email')) { + db.exec(`ALTER TABLE accounts ADD COLUMN pending_email TEXT`); + } +} + +// -------- types -------- + +export interface Account { + id: string; + email: string | null; + /** Email the user is mid-verifying. Cleared on completion or cancel. */ + pending_email: string | null; + plan: PlanId; + created_at: number; +} + +export interface ApiKeyRow { + id: string; + account_id: string; + key_hash: string; + key_prefix: string; + name: string; + scopes: string; // JSON string on disk + active: number; // 0 / 1 + created_at: number; + last_used_at: number | null; +} + +export interface ApiKey { + id: string; + account_id: string; + key_prefix: string; + name: string; + scopes: string[]; // decoded + active: boolean; + created_at: number; + last_used_at: number | null; +} + +export function rowToKey(row: ApiKeyRow): ApiKey { + return { + id: row.id, + account_id: row.account_id, + key_prefix: row.key_prefix, + name: row.name, + scopes: JSON.parse(row.scopes), + active: row.active === 1, + created_at: row.created_at, + last_used_at: row.last_used_at + }; +} + +// -------- helpers -------- + +export function now(): number { + return Date.now(); +} + +export function newId(): string { + return randomUUID(); +} + +// -------- prepared statements -------- + +const stmts = { + accountById: db.prepare( + `SELECT id, email, pending_email, plan, created_at FROM accounts WHERE id = ?` + ), + accountByEmail: db.prepare( + `SELECT id, email, pending_email, plan, created_at FROM accounts WHERE email = ?` + ), + insertAccount: db.prepare( + `INSERT INTO accounts (id, email, plan, created_at) VALUES (?, ?, ?, ?)` + ), + updateAccountEmail: db.prepare( + `UPDATE accounts SET email = ? WHERE id = ?` + ), + setPendingEmail: db.prepare( + `UPDATE accounts SET pending_email = ? WHERE id = ?` + ), + clearPendingEmail: db.prepare( + `UPDATE accounts SET pending_email = NULL WHERE id = ?` + ), + promotePendingEmail: db.prepare( + `UPDATE accounts SET email = pending_email, pending_email = NULL WHERE id = ?` + ), + updateAccountPlan: db.prepare( + `UPDATE accounts SET plan = ? WHERE id = ?` + ), + deleteAccount: db.prepare( + `DELETE FROM accounts WHERE id = ?` + ), + reassignKeys: db.prepare( + `UPDATE api_keys SET account_id = ? WHERE account_id = ?` + ), + reassignUsage: db.prepare( + `UPDATE usage_events SET api_key_id = api_key_id WHERE api_key_id IN + (SELECT id FROM api_keys WHERE account_id = ?)` + ), + keysForAccount: db.prepare( + `SELECT * FROM api_keys WHERE account_id = ? ORDER BY created_at DESC` + ), + keyById: db.prepare<[string, string], ApiKeyRow>( + `SELECT * FROM api_keys WHERE id = ? AND account_id = ?` + ), + insertKey: db.prepare( + `INSERT INTO api_keys + (id, account_id, key_hash, key_prefix, name, scopes, active, created_at) + VALUES (?, ?, ?, ?, ?, ?, 1, ?)` + ), + revokeKey: db.prepare( + `UPDATE api_keys SET active = 0 WHERE id = ? AND account_id = ?` + ), + countActiveKeys: db.prepare( + `SELECT COUNT(*) AS c FROM api_keys WHERE account_id = ? AND active = 1` + ), + insertMagicLink: db.prepare( + `INSERT INTO magic_links (id, account_id, token, expires_at, used, created_at) + VALUES (?, ?, ?, ?, 0, ?)` + ), + magicLinkByToken: db.prepare< + string, + { id: string; account_id: string; expires_at: number; used: number } + >( + `SELECT id, account_id, expires_at, used FROM magic_links WHERE token = ?` + ), + markMagicLinkUsed: db.prepare( + `UPDATE magic_links SET used = 1 WHERE id = ?` + ), + insertUsageEvent: db.prepare( + `INSERT INTO usage_events (id, api_key_id, dataset, timestamp) + VALUES (?, ?, ?, ?)` + ), + usageCountSince: db.prepare<[string, number], { c: number }>( + `SELECT COUNT(*) AS c FROM usage_events e + JOIN api_keys k ON k.id = e.api_key_id + WHERE k.account_id = ? AND e.timestamp >= ?` + ), + usageByDataset: db.prepare< + [string, number], + { dataset: string; c: number } + >( + `SELECT e.dataset AS dataset, COUNT(*) AS c + FROM usage_events e + JOIN api_keys k ON k.id = e.api_key_id + WHERE k.account_id = ? AND e.timestamp >= ? + GROUP BY e.dataset + ORDER BY c DESC` + ), + usageByKey: db.prepare< + [number, string], + { key_id: string; name: string; c: number } + >( + `SELECT k.id AS key_id, k.name AS name, COUNT(e.id) AS c + FROM api_keys k + LEFT JOIN usage_events e + ON e.api_key_id = k.id AND e.timestamp >= ? + WHERE k.account_id = ? + GROUP BY k.id + ORDER BY c DESC` + ) +}; + +export const queries = stmts; -- cgit v1.2.3