a basic ui and landing web interface for tidyindex.com

1 files changed, 191 insertions, 0 deletions

diff --git a/web/ui/scripts/seed.js b/web/ui/scripts/seed.js
new file mode 100644
index 0000000..f4d27c2
--- /dev/null
+++ b/web/ui/scripts/seed.js
@@ -0,0 +1,191 @@
+/**
+ * Populate the dashboard SQLite database with fake usage data against
+ * the first account in the DB so the Usage tab isn't empty.
+ *
+ * Run with: `npm run seed` (which uses node --env-file=.env).
+ *
+ * If there's no account yet, we create an anonymous one and also mint
+ * two keys so the dashboard has something to show when you first log in.
+ */
+
+import Database from 'better-sqlite3';
+import { mkdirSync } from 'node:fs';
+import { dirname, resolve } from 'node:path';
+import { createHash, randomBytes, randomUUID } from 'node:crypto';
+
+const DB_PATH = resolve(process.env.DATABASE_PATH || './data/dashboard.db');
+mkdirSync(dirname(DB_PATH), { recursive: true });
+
+const db = new Database(DB_PATH);
+db.pragma('journal_mode = WAL');
+db.pragma('foreign_keys = ON');
+
+// Make sure the schema exists (same as src/lib/server/db.ts).
+db.exec(`
+  CREATE TABLE IF NOT EXISTS accounts (
+    id TEXT PRIMARY KEY,
+    email TEXT UNIQUE,
+    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);
+`);
+
+const now = Date.now();
+
+const ALPHABET =
+  'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
+
+function mintKey() {
+  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);
+  return { key, hash, prefix };
+}
+
+// --- find or create an account ---
+
+/** @typedef {{ id: string, plan: string }} AccountRow */
+/** @typedef {{ id: string, name: string }} KeyRow */
+
+let account = /** @type {AccountRow | undefined} */ (
+  db
+    .prepare(`SELECT id, plan FROM accounts ORDER BY created_at ASC LIMIT 1`)
+    .get()
+);
+
+if (!account) {
+  const id = randomUUID();
+  db.prepare(
+    `INSERT INTO accounts (id, email, plan, created_at) VALUES (?, NULL, 'dev', ?)`
+  ).run(id, now - 1000 * 60 * 60 * 24 * 14); // created 2 weeks ago
+  account = { id, plan: 'dev' };
+  console.log(`created demo account ${id}`);
+} else {
+  console.log(`seeding against existing account ${account.id}`);
+}
+
+// --- make sure there are at least two active keys ---
+
+const existingKeys = /** @type {KeyRow[]} */ (
+  db
+    .prepare(`SELECT id, name FROM api_keys WHERE account_id = ? AND active = 1`)
+    .all(account.id)
+);
+
+/** @type {KeyRow[]} */
+const keyRows =
+  existingKeys.length >= 2
+    ? existingKeys
+    : (() => {
+        /** @type {KeyRow[]} */
+        const created = [];
+        for (const name of ['production ingest', 'research notebook']) {
+          const { hash, prefix } = mintKey();
+          const id = randomUUID();
+          db.prepare(
+            `INSERT INTO api_keys
+              (id, account_id, key_hash, key_prefix, name, scopes, active, created_at)
+             VALUES (?, ?, ?, ?, ?, '["*"]', 1, ?)`
+          ).run(id, account.id, hash, prefix, name, now - 1000 * 60 * 60 * 24 * 10);
+          created.push({ id, name });
+        }
+        return [...existingKeys, ...created];
+      })();
+
+// --- wipe usage events we seeded previously and re-generate ---
+
+db.prepare(
+  `DELETE FROM usage_events WHERE api_key_id IN
+    (SELECT id FROM api_keys WHERE account_id = ?)`
+).run(account.id);
+
+// Generate roughly 2,400 events distributed over the past 30 days, heavily
+// weighted toward irs-990 and sec-edgar (the flagship datasets).
+const weights = {
+  'irs-990': 28,
+  'sec-edgar': 22,
+  'sec-13f': 14,
+  'fec-contributions': 10,
+  'nih-reporter': 8,
+  'pacer': 6,
+  'fda-faers': 4,
+  'osha': 3,
+  'usaspending': 3,
+  'cfpb-complaints': 2
+};
+const weighted = /** @type {string[]} */ ([]);
+for (const [slug, w] of Object.entries(weights)) {
+  for (let i = 0; i < w; i++) weighted.push(slug);
+}
+
+const THIRTY_DAYS = 30 * 24 * 60 * 60 * 1000;
+const insertEvent = db.prepare(
+  `INSERT INTO usage_events (id, api_key_id, dataset, timestamp) VALUES (?, ?, ?, ?)`
+);
+
+const TOTAL = 2400;
+const tx = db.transaction(() => {
+  for (let i = 0; i < TOTAL; i++) {
+    const dataset = weighted[Math.floor(Math.random() * weighted.length)];
+    const keyRow = keyRows[Math.floor(Math.random() * keyRows.length)];
+    const age = Math.floor(Math.random() * THIRTY_DAYS);
+    insertEvent.run(randomUUID(), keyRow.id, dataset, now - age);
+  }
+});
+tx();
+
+// Update last_used_at on each key to the most recent event timestamp.
+for (const row of keyRows) {
+  const latest = /** @type {{ ts: number | null } | undefined} */ (
+    db
+      .prepare(
+        `SELECT MAX(timestamp) AS ts FROM usage_events WHERE api_key_id = ?`
+      )
+      .get(row.id)
+  );
+  if (latest?.ts) {
+    db.prepare(`UPDATE api_keys SET last_used_at = ? WHERE id = ?`).run(
+      latest.ts,
+      row.id
+    );
+  }
+}
+
+console.log(`seeded ${TOTAL} usage events against ${keyRows.length} keys`);
+console.log(`db: ${DB_PATH}`);
+db.close();