4 files changed, 498 insertions, 0 deletions
diff --git a/web/ui/src/lib/server/auth.ts b/web/ui/src/lib/server/auth.ts
new file mode 100644
index 0000000..6f202d3
--- /dev/null
+++ b/web/ui/src/lib/server/auth.ts
@@ -0,0 +1,153 @@
+import jwt from 'jsonwebtoken';
+import { randomBytes } from 'node:crypto';
+import { dev } from '$app/environment';
+import { env } from '$env/dynamic/private';
+import type { Cookies } from '@sveltejs/kit';
+
+import { db, queries, newId, now, type Account } from './db';
+
+function getJwtSecret(): string {
+  const secret = env.JWT_SECRET;
+  if (!secret) {
+    throw new Error(
+      'JWT_SECRET env variable is required. Copy .env.example to .env ' +
+        'and generate one with `openssl rand -base64 48`.'
+    );
+  }
+  return secret;
+}
+
+const COOKIE_NAME = 'ti_sess';
+const COOKIE_MAX_AGE = 60 * 60 * 24 * 365; // 1 year
+
+interface SessionPayload {
+  sub: string;              // account id
+}
+
+export function createSession(cookies: Cookies, accountId: string): void {
+  const token = jwt.sign(
+    { sub: accountId } satisfies SessionPayload,
+    getJwtSecret(),
+    { algorithm: 'HS256' }
+  );
+  cookies.set(COOKIE_NAME, token, {
+    path: '/',
+    httpOnly: true,
+    sameSite: 'lax',
+    secure: !dev,
+    maxAge: COOKIE_MAX_AGE
+  });
+}
+
+export function clearSession(cookies: Cookies): void {
+  cookies.delete(COOKIE_NAME, { path: '/' });
+}
+
+export function getAccountFromCookies(cookies: Cookies): Account | null {
+  const raw = cookies.get(COOKIE_NAME);
+  if (!raw) return null;
+  try {
+    const decoded = jwt.verify(raw, getJwtSecret(), {
+      algorithms: ['HS256']
+    }) as SessionPayload;
+    if (!decoded.sub) return null;
+    const account = queries.accountById.get(decoded.sub);
+    return account ?? null;
+  } catch {
+    return null;
+  }
+}
+
+// -------- account helpers --------
+
+/** Create a brand-new anonymous account (no email). */
+export function createAnonymousAccount(): Account {
+  const id = newId();
+  const ts = now();
+  queries.insertAccount.run(id, null, 'free', ts);
+  return { id, email: null, pending_email: null, plan: 'free', created_at: ts };
+}
+
+/** Find an account by email or create a new one with that email. */
+export function getOrCreateAccountByEmail(email: string): Account {
+  const existing = queries.accountByEmail.get(email);
+  if (existing) return existing;
+  const id = newId();
+  const ts = now();
+  queries.insertAccount.run(id, email, 'free', ts);
+  return { id, email, pending_email: null, plan: 'free', created_at: ts };
+}
+
+// -------- magic links --------
+
+export interface MagicLink {
+  token: string;
+  expires_at: number;
+  url: string;
+}
+
+export function generateMagicLink(
+  accountId: string,
+  baseUrl: string
+): MagicLink {
+  const token = randomBytes(24).toString('base64url');
+  const id = newId();
+  const ts = now();
+  const expires = ts + 15 * 60 * 1000; // 15 minutes
+  queries.insertMagicLink.run(id, accountId, token, expires, ts);
+  const url = `${baseUrl.replace(/\/$/, '')}/auth/callback?token=${token}`;
+  return { token, expires_at: expires, url };
+}
+
+export function consumeMagicLink(token: string): Account | null {
+  const row = queries.magicLinkByToken.get(token);
+  if (!row) return null;
+  if (row.used === 1) return null;
+  if (row.expires_at < now()) return null;
+  queries.markMagicLinkUsed.run(row.id);
+  const account = queries.accountById.get(row.account_id);
+  return account ?? null;
+}
+
+// -------- email attach / merge --------
+
+/**
+ * Attach an email to an existing (anonymous) account. If another account
+ * already uses that email, merge this account into the other one — all
+ * API keys move over, the anonymous account is deleted, and the caller
+ * receives the surviving account id (which may be different from the
+ * one passed in).
+ */
+export function attachEmailToAccount(
+  accountId: string,
+  email: string
+): { accountId: string; merged: boolean } {
+  const normalized = email.trim().toLowerCase();
+  if (!normalized) throw new Error('Email is empty');
+
+  const current = queries.accountById.get(accountId);
+  if (!current) throw new Error('Account not found');
+
+  const existing = queries.accountByEmail.get(normalized);
+
+  // Same account already has this email — no-op.
+  if (existing && existing.id === current.id) {
+    return { accountId: current.id, merged: false };
+  }
+
+  // No other account has this email — just attach it.
+  if (!existing) {
+    queries.updateAccountEmail.run(normalized, current.id);
+    return { accountId: current.id, merged: false };
+  }
+
+  // Another account already owns the email. Merge current into existing:
+  // move all keys over, then delete the current account.
+  const merge = db.transaction(() => {
+    queries.reassignKeys.run(existing.id, current.id);
+    queries.deleteAccount.run(current.id);
+  });
+  merge();
+
+  return { accountId: existing.id, merged: true };
+}
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;
diff --git a/web/ui/src/lib/server/keys.ts b/web/ui/src/lib/server/keys.ts
new file mode 100644
index 0000000..350839b
--- /dev/null
+++ b/web/ui/src/lib/server/keys.ts
@@ -0,0 +1,79 @@
+import { createHash, randomBytes } from 'node:crypto';
+
+import { queries, newId, now, rowToKey, type ApiKey } from './db';
+import { normalizeScopes } from '$lib/keys';
+
+const ALPHABET =
+  'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
+
+/**
+ * Generate a new API key string (not yet persisted).
+ * Format: "ti_" + 32 random alphanumeric characters.
+ */
+export function mintKey(): { key: string; hash: string; prefix: string } {
+  const bytes = randomBytes(32);
+  let random = '';
+  for (let i = 0; i < 32; i++) {
+    random += ALPHABET[bytes[i] % ALPHABET.length];
+  }
+  const key = `ti_${random}`;
+  const hash = createHash('sha256').update(key).digest('hex');
+  const prefix = key.slice(0, 8); // "ti_" + 5 chars
+  return { key, hash, prefix };
+}
+
+// -------- persistence --------
+
+export interface CreateKeyInput {
+  accountId: string;
+  name: string;
+  scopes: string[];
+}
+
+export interface CreatedKey extends ApiKey {
+  /** The full plaintext key. Only returned on creation — never stored. */
+  plaintext: string;
+}
+
+export function createKey(input: CreateKeyInput): CreatedKey {
+  const { key, hash, prefix } = mintKey();
+  const id = newId();
+  const ts = now();
+  const name = input.name.trim() || 'Untitled key';
+  const scopes = normalizeScopes(input.scopes);
+  queries.insertKey.run(
+    id,
+    input.accountId,
+    hash,
+    prefix,
+    name,
+    JSON.stringify(scopes),
+    ts
+  );
+  return {
+    id,
+    account_id: input.accountId,
+    key_prefix: prefix,
+    name,
+    scopes,
+    active: true,
+    created_at: ts,
+    last_used_at: null,
+    plaintext: key
+  };
+}
+
+export function listKeys(accountId: string): ApiKey[] {
+  return queries.keysForAccount.all(accountId).map(rowToKey);
+}
+
+export function revokeKey(accountId: string, keyId: string): boolean {
+  const row = queries.keyById.get(keyId, accountId);
+  if (!row) return false;
+  queries.revokeKey.run(keyId, accountId);
+  return true;
+}
+
+export function countActiveKeys(accountId: string): number {
+  return queries.countActiveKeys.get(accountId)?.c ?? 0;
+}
diff --git a/web/ui/src/lib/server/usage.ts b/web/ui/src/lib/server/usage.ts
new file mode 100644
index 0000000..721cc3c
--- /dev/null
+++ b/web/ui/src/lib/server/usage.ts
@@ -0,0 +1,33 @@
+import { queries } from './db';
+
+export function startOfCurrentMonth(): number {
+  const d = new Date();
+  d.setUTCDate(1);
+  d.setUTCHours(0, 0, 0, 0);
+  return d.getTime();
+}
+
+export function usageCountThisMonth(accountId: string): number {
+  return queries.usageCountSince.get(accountId, startOfCurrentMonth())?.c ?? 0;
+}
+
+export function usageByDataset(
+  accountId: string
+): Array<{ dataset: string; count: number }> {
+  const rows = queries.usageByDataset.all(accountId, startOfCurrentMonth());
+  return rows.map((r: { dataset: string; c: number }) => ({
+    dataset: r.dataset,
+    count: r.c
+  }));
+}
+
+export function usageByKey(
+  accountId: string
+): Array<{ keyId: string; name: string; count: number }> {
+  const rows = queries.usageByKey.all(startOfCurrentMonth(), accountId);
+  return rows.map((r: { key_id: string; name: string; c: number }) => ({
+    keyId: r.key_id,
+    name: r.name,
+    count: r.c
+  }));
+}