1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
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();
|
