1 files changed, 233 insertions, 0 deletions
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<string, Account>(
+    `SELECT id, email, pending_email, plan, created_at FROM accounts WHERE id = ?`
+  ),
+  accountByEmail: db.prepare<string, Account>(
+    `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<string, ApiKeyRow>(
+    `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<string, { c: number }>(
+    `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;