a fancy canvas mcp server!
1import { Database } from "bun:sqlite";
2import { createCipheriv, createDecipheriv, randomBytes } from "node:crypto";
3
4const db = new Database(process.env.DATABASE_PATH || "./canvas-mcp.db");
5
6// In-memory cache for verified API keys
7interface ApiKeyCacheEntry {
8 userId: number;
9 verifiedAt: number;
10}
11
12const apiKeyCache = new Map<string, ApiKeyCacheEntry>();
13const CACHE_TTL = parseInt(process.env.API_KEY_CACHE_TTL || "900000", 10); // 15 minutes default
14
15// Cache cleanup interval (runs every 5 minutes)
16setInterval(
17 () => {
18 const now = Date.now();
19 for (const [key, entry] of apiKeyCache.entries()) {
20 if (now - entry.verifiedAt > CACHE_TTL) {
21 apiKeyCache.delete(key);
22 }
23 }
24 },
25 5 * 60 * 1000,
26);
27
28// Initialize database schema
29db.exec(`
30 CREATE TABLE IF NOT EXISTS users (
31 id INTEGER PRIMARY KEY AUTOINCREMENT,
32 canvas_user_id TEXT,
33 canvas_domain TEXT,
34 email TEXT,
35 canvas_access_token TEXT,
36 canvas_refresh_token TEXT,
37 mcp_api_key TEXT UNIQUE,
38 created_at INTEGER NOT NULL DEFAULT (unixepoch() * 1000),
39 last_used_at INTEGER,
40 token_expires_at INTEGER
41 );
42
43 CREATE TABLE IF NOT EXISTS usage_logs (
44 id INTEGER PRIMARY KEY AUTOINCREMENT,
45 user_id INTEGER NOT NULL,
46 endpoint TEXT NOT NULL,
47 timestamp INTEGER NOT NULL,
48 FOREIGN KEY (user_id) REFERENCES users(id)
49 );
50
51 CREATE TABLE IF NOT EXISTS sessions (
52 id TEXT PRIMARY KEY,
53 user_id INTEGER,
54 canvas_domain TEXT NOT NULL,
55 state TEXT,
56 api_key TEXT,
57 created_at INTEGER NOT NULL,
58 expires_at INTEGER NOT NULL
59 );
60
61 CREATE TABLE IF NOT EXISTS magic_links (
62 id INTEGER PRIMARY KEY AUTOINCREMENT,
63 email TEXT NOT NULL,
64 token TEXT UNIQUE NOT NULL,
65 expires_at INTEGER NOT NULL,
66 used INTEGER DEFAULT 0,
67 created_at INTEGER DEFAULT (unixepoch() * 1000)
68 );
69
70 CREATE TABLE IF NOT EXISTS auth_codes (
71 code TEXT PRIMARY KEY,
72 user_id INTEGER NOT NULL,
73 client_id TEXT NOT NULL,
74 redirect_uri TEXT NOT NULL,
75 code_challenge TEXT NOT NULL,
76 code_challenge_method TEXT NOT NULL,
77 scope TEXT NOT NULL,
78 expires_at INTEGER NOT NULL,
79 created_at INTEGER DEFAULT (unixepoch() * 1000),
80 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
81 );
82
83 CREATE TABLE IF NOT EXISTS oauth_tokens (
84 token TEXT PRIMARY KEY,
85 user_id INTEGER NOT NULL,
86 scope TEXT NOT NULL,
87 expires_at INTEGER NOT NULL,
88 created_at INTEGER DEFAULT (unixepoch() * 1000),
89 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
90 );
91
92 CREATE INDEX IF NOT EXISTS idx_users_api_key ON users(mcp_api_key);
93 CREATE INDEX IF NOT EXISTS idx_users_canvas_id ON users(canvas_user_id);
94 CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
95 CREATE INDEX IF NOT EXISTS idx_usage_logs_user_id ON usage_logs(user_id);
96 CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
97 CREATE INDEX IF NOT EXISTS idx_magic_links_token ON magic_links(token);
98 CREATE INDEX IF NOT EXISTS idx_magic_links_email ON magic_links(email);
99 CREATE INDEX IF NOT EXISTS idx_auth_codes_code ON auth_codes(code);
100 CREATE INDEX IF NOT EXISTS idx_oauth_tokens_token ON oauth_tokens(token);
101`);
102
103// Encryption utilities
104const ENCRYPTION_KEY = Buffer.from(process.env.ENCRYPTION_KEY || "", "base64");
105const ALGORITHM = "aes-256-gcm";
106
107function encrypt(text: string): string {
108 const iv = randomBytes(16);
109 const cipher = createCipheriv(ALGORITHM, ENCRYPTION_KEY, iv);
110
111 let encrypted = cipher.update(text, "utf8", "hex");
112 encrypted += cipher.final("hex");
113
114 const authTag = cipher.getAuthTag();
115
116 // Return: iv:authTag:encrypted
117 return `${iv.toString("hex")}:${authTag.toString("hex")}:${encrypted}`;
118}
119
120function decrypt(encryptedData: string): string {
121 const [ivHex, authTagHex, encrypted] = encryptedData.split(":");
122
123 const iv = Buffer.from(ivHex, "hex");
124 const authTag = Buffer.from(authTagHex, "hex");
125 const decipher = createDecipheriv(ALGORITHM, ENCRYPTION_KEY, iv);
126
127 decipher.setAuthTag(authTag);
128
129 let decrypted = decipher.update(encrypted, "hex", "utf8");
130 decrypted += decipher.final("utf8");
131
132 return decrypted;
133}
134
135// Generate secure API key
136function generateApiKey(): string {
137 return `cmcp_${randomBytes(32).toString("base64url")}`;
138}
139
140// Hash API key for storage
141async function hashApiKey(apiKey: string): Promise<string> {
142 return await Bun.password.hash(apiKey, {
143 algorithm: "argon2id",
144 memoryCost: 19456,
145 timeCost: 2,
146 });
147}
148
149// Verify API key
150async function verifyApiKey(apiKey: string, hash: string): Promise<boolean> {
151 return await Bun.password.verify(apiKey, hash);
152}
153
154export interface User {
155 id: number;
156 canvas_user_id: string;
157 canvas_domain: string;
158 email?: string;
159 canvas_access_token: string;
160 canvas_refresh_token?: string;
161 mcp_api_key: string;
162 created_at: number;
163 last_used_at?: number;
164 token_expires_at?: number;
165}
166
167export const DB = {
168 // Raw database access
169 raw: db,
170
171 // Create or update user after OAuth
172 async createOrUpdateUser(data: {
173 canvas_user_id: string;
174 canvas_domain: string;
175 email?: string;
176 canvas_access_token: string;
177 canvas_refresh_token?: string;
178 token_expires_at?: number;
179 }): Promise<{ user: User; apiKey: string | null; isNewUser: boolean }> {
180 const encryptedToken = encrypt(data.canvas_access_token);
181 const encryptedRefreshToken = data.canvas_refresh_token
182 ? encrypt(data.canvas_refresh_token)
183 : null;
184
185 // Check if user exists by canvas_user_id or email
186 let existing = db
187 .query("SELECT * FROM users WHERE canvas_user_id = ?")
188 .get(data.canvas_user_id) as User | null;
189
190 // If not found by canvas_user_id, check by email (for magic link users)
191 if (!existing && data.email) {
192 existing = db
193 .query("SELECT * FROM users WHERE email = ? AND canvas_user_id IS NULL")
194 .get(data.email) as User | null;
195 }
196
197 if (existing) {
198 // Check if user needs an API key (magic link users)
199 let apiKey: string | null = null;
200 let hashedApiKey = existing.mcp_api_key;
201
202 if (!hashedApiKey) {
203 // Generate API key for magic link users connecting Canvas for first time
204 apiKey = generateApiKey();
205 hashedApiKey = await hashApiKey(apiKey);
206 }
207
208 // Update existing user (might not have canvas_user_id if from magic link)
209 db.run(
210 `UPDATE users SET
211 canvas_user_id = ?,
212 canvas_domain = ?,
213 canvas_access_token = ?,
214 canvas_refresh_token = ?,
215 token_expires_at = ?,
216 last_used_at = ?,
217 mcp_api_key = ?
218 WHERE id = ?`,
219 [
220 data.canvas_user_id,
221 data.canvas_domain,
222 encryptedToken,
223 encryptedRefreshToken,
224 data.token_expires_at,
225 Date.now(),
226 hashedApiKey,
227 existing.id,
228 ],
229 );
230
231 const user = db
232 .query("SELECT * FROM users WHERE id = ?")
233 .get(existing.id) as User;
234
235 // Return API key only if we just generated it (for magic link users)
236 const isNewUser = apiKey !== null;
237 return { user, apiKey, isNewUser };
238 } else {
239 // Create new user with API key
240 const apiKey = generateApiKey();
241 const hashedApiKey = await hashApiKey(apiKey);
242
243 const result = db.run(
244 `INSERT INTO users (
245 canvas_user_id, canvas_domain, email,
246 canvas_access_token, canvas_refresh_token,
247 mcp_api_key, created_at, token_expires_at
248 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
249 [
250 data.canvas_user_id,
251 data.canvas_domain,
252 data.email,
253 encryptedToken,
254 encryptedRefreshToken,
255 hashedApiKey,
256 Date.now(),
257 data.token_expires_at,
258 ],
259 );
260
261 const user = db
262 .query("SELECT * FROM users WHERE id = ?")
263 .get(result.lastInsertRowid) as User;
264
265 return { user, apiKey, isNewUser: true };
266 }
267 },
268
269 // Get user by API key (with caching for performance)
270 async getUserByApiKey(apiKey: string): Promise<User | null> {
271 // Check cache first for O(1) lookup
272 const cached = apiKeyCache.get(apiKey);
273 if (cached && Date.now() - cached.verifiedAt < CACHE_TTL) {
274 // Cache hit - fast path
275 const user = db
276 .query("SELECT * FROM users WHERE id = ?")
277 .get(cached.userId) as User | null;
278
279 if (user) {
280 return user;
281 }
282 // User was deleted - invalidate cache entry
283 apiKeyCache.delete(apiKey);
284 }
285
286 // Cache miss - perform full verification (slow path)
287 const users = db
288 .query("SELECT * FROM users WHERE mcp_api_key IS NOT NULL")
289 .all() as User[];
290
291 for (const user of users) {
292 if (await verifyApiKey(apiKey, user.mcp_api_key)) {
293 // Cache the verified key for future requests
294 apiKeyCache.set(apiKey, {
295 userId: user.id,
296 verifiedAt: Date.now(),
297 });
298 return user;
299 }
300 }
301
302 return null;
303 },
304
305 // Get user by Canvas user ID
306 getUserByCanvasId(canvas_user_id: string): User | null {
307 return db
308 .query("SELECT * FROM users WHERE canvas_user_id = ?")
309 .get(canvas_user_id) as User | null;
310 },
311
312 // Get decrypted Canvas token for user
313 getCanvasToken(user: User): string {
314 return decrypt(user.canvas_access_token);
315 },
316
317 // Get decrypted refresh token
318 getRefreshToken(user: User): string | null {
319 return user.canvas_refresh_token
320 ? decrypt(user.canvas_refresh_token)
321 : null;
322 },
323
324 // Log API usage
325 logUsage(userId: number, endpoint: string) {
326 db.run(
327 "INSERT INTO usage_logs (user_id, endpoint, timestamp) VALUES (?, ?, ?)",
328 [userId, endpoint, Date.now()],
329 );
330 },
331
332 // Get usage stats for user
333 getUsageStats(userId: number, since?: number) {
334 const query = since
335 ? "SELECT * FROM usage_logs WHERE user_id = ? AND timestamp >= ?"
336 : "SELECT * FROM usage_logs WHERE user_id = ?";
337
338 const params = since ? [userId, since] : [userId];
339 return db.query(query).all(...params);
340 },
341
342 // Update last used timestamp
343 updateLastUsed(userId: number) {
344 db.run("UPDATE users SET last_used_at = ? WHERE id = ?", [
345 Date.now(),
346 userId,
347 ]);
348 },
349
350 // Regenerate API key
351 async regenerateApiKey(userId: number): Promise<string> {
352 // Invalidate all cached entries for this user
353 for (const [key, entry] of apiKeyCache.entries()) {
354 if (entry.userId === userId) {
355 apiKeyCache.delete(key);
356 }
357 }
358
359 const newApiKey = generateApiKey();
360 const hashedApiKey = await hashApiKey(newApiKey);
361
362 db.run("UPDATE users SET mcp_api_key = ? WHERE id = ?", [
363 hashedApiKey,
364 userId,
365 ]);
366
367 return newApiKey;
368 },
369
370 // Session management
371 createSession(
372 sessionId: string,
373 data: {
374 user_id?: number;
375 canvas_domain: string;
376 state: string;
377 api_key?: string;
378 maxAge: number; // in seconds
379 },
380 ) {
381 const now = Date.now();
382 db.run(
383 `INSERT INTO sessions (id, user_id, canvas_domain, state, api_key, created_at, expires_at)
384 VALUES (?, ?, ?, ?, ?, ?, ?)`,
385 [
386 sessionId,
387 data.user_id || null,
388 data.canvas_domain,
389 data.state,
390 data.api_key || null,
391 now,
392 now + data.maxAge * 1000,
393 ],
394 );
395 },
396
397 getSession(sessionId: string) {
398 return db
399 .query("SELECT * FROM sessions WHERE id = ? AND expires_at > ?")
400 .get(sessionId, Date.now()) as any;
401 },
402
403 updateSession(
404 sessionId: string,
405 data: Partial<{ user_id: number; api_key: string }>,
406 ) {
407 const updates: string[] = [];
408 const values: any[] = [];
409
410 if (data.user_id !== undefined) {
411 updates.push("user_id = ?");
412 values.push(data.user_id);
413 }
414 if (data.api_key !== undefined) {
415 updates.push("api_key = ?");
416 values.push(data.api_key);
417 }
418
419 if (updates.length > 0) {
420 values.push(sessionId);
421 db.run(`UPDATE sessions SET ${updates.join(", ")} WHERE id = ?`, values);
422 }
423 },
424
425 deleteSession(sessionId: string) {
426 db.run("DELETE FROM sessions WHERE id = ?", [sessionId]);
427 },
428
429 clearApiKeyFromSession(sessionId: string) {
430 db.run("UPDATE sessions SET api_key = NULL WHERE id = ?", [sessionId]);
431 },
432
433 // Get session by API key (for MCP authentication)
434 getSessionByToken(token: string) {
435 return db
436 .query("SELECT * FROM sessions WHERE api_key = ? AND expires_at > ?")
437 .get(token, Date.now()) as any;
438 },
439
440 // Magic link authentication
441 createMagicLink(email: string, token: string, expiresAt: number) {
442 return db.run(
443 "INSERT INTO magic_links (email, token, expires_at) VALUES (?, ?, ?)",
444 [email, token, expiresAt],
445 );
446 },
447
448 getMagicLink(token: string) {
449 return db
450 .query(
451 "SELECT * FROM magic_links WHERE token = ? AND expires_at > ? AND used = 0",
452 )
453 .get(token, Date.now()) as any;
454 },
455
456 markMagicLinkUsed(token: string) {
457 return db.run("UPDATE magic_links SET used = 1 WHERE token = ?", [token]);
458 },
459
460 getUserByEmail(email: string) {
461 return db.query("SELECT * FROM users WHERE email = ?").get(email) as any;
462 },
463
464 // Update user with Canvas credentials (for magic link users)
465 async updateUserCanvas(
466 userId: number,
467 canvasUserId: string,
468 canvasDomain: string,
469 canvasToken: string,
470 ): Promise<{ apiKey: string | null }> {
471 const existing = db
472 .query("SELECT * FROM users WHERE id = ?")
473 .get(userId) as User | null;
474
475 if (!existing) {
476 throw new Error("User not found");
477 }
478
479 const encryptedToken = encrypt(canvasToken);
480
481 // Generate API key if user doesn't have one
482 let apiKey: string | null = null;
483 let hashedApiKey = existing.mcp_api_key;
484
485 if (!hashedApiKey) {
486 apiKey = generateApiKey();
487 hashedApiKey = await hashApiKey(apiKey);
488 }
489
490 // Update user with Canvas credentials
491 db.run(
492 `UPDATE users SET
493 canvas_user_id = ?,
494 canvas_domain = ?,
495 canvas_access_token = ?,
496 mcp_api_key = ?,
497 last_used_at = ?
498 WHERE id = ?`,
499 [
500 canvasUserId,
501 canvasDomain,
502 encryptedToken,
503 hashedApiKey,
504 Date.now(),
505 userId,
506 ],
507 );
508
509 return { apiKey };
510 },
511
512 // Rate limiting for magic links
513 canSendMagicLink(email: string, cooldownMs: number = 60000): boolean {
514 // Check if a magic link was sent recently (within cooldown period)
515 const recent = db
516 .query(
517 "SELECT * FROM magic_links WHERE email = ? AND created_at > ? ORDER BY created_at DESC LIMIT 1",
518 )
519 .get(email, Date.now() - cooldownMs) as any;
520
521 return !recent;
522 },
523
524 getLastMagicLinkTime(email: string): number | null {
525 const recent = db
526 .query(
527 "SELECT created_at FROM magic_links WHERE email = ? ORDER BY created_at DESC LIMIT 1",
528 )
529 .get(email) as any;
530
531 return recent ? recent.created_at : null;
532 },
533
534 // OAuth tokens
535 createOAuthToken(
536 userId: number,
537 scope: string,
538 expiresIn: number = 86400000,
539 ): string {
540 const token = generateApiKey(); // Reuse the API key generator
541 const expiresAt = Date.now() + expiresIn;
542
543 db.run(
544 "INSERT INTO oauth_tokens (token, user_id, scope, expires_at) VALUES (?, ?, ?, ?)",
545 [token, userId, scope, expiresAt],
546 );
547
548 return token;
549 },
550
551 getUserByOAuthToken(token: string): User | null {
552 const tokenData = db
553 .query("SELECT * FROM oauth_tokens WHERE token = ? AND expires_at > ?")
554 .get(token, Date.now()) as any;
555
556 if (!tokenData) {
557 return null;
558 }
559
560 return db
561 .query("SELECT * FROM users WHERE id = ?")
562 .get(tokenData.user_id) as User | null;
563 },
564
565 // Cache management utilities
566 clearApiKeyCache() {
567 apiKeyCache.clear();
568 },
569
570 invalidateUserCache(userId: number) {
571 for (const [key, entry] of apiKeyCache.entries()) {
572 if (entry.userId === userId) {
573 apiKeyCache.delete(key);
574 }
575 }
576 },
577
578 getApiKeyCacheStats() {
579 return {
580 size: apiKeyCache.size,
581 ttl: CACHE_TTL,
582 };
583 },
584
585 // Background cleanup operations (run these periodically, not on request path)
586 cleanupExpiredSessions(): number {
587 const result = db.run("DELETE FROM sessions WHERE expires_at < ?", [
588 Date.now(),
589 ]);
590 return result.changes;
591 },
592
593 cleanupExpiredMagicLinks(): number {
594 const result = db.run("DELETE FROM magic_links WHERE expires_at < ?", [
595 Date.now(),
596 ]);
597 return result.changes;
598 },
599
600 cleanupExpiredAuthCodes(): number {
601 const result = db.run("DELETE FROM auth_codes WHERE expires_at < ?", [
602 Date.now(),
603 ]);
604 return result.changes;
605 },
606
607 cleanupExpiredOAuthTokens(): number {
608 const result = db.run("DELETE FROM oauth_tokens WHERE expires_at < ?", [
609 Date.now(),
610 ]);
611 return result.changes;
612 },
613
614 // Clean up old usage logs (keep last 90 days)
615 cleanupOldUsageLogs(retentionDays: number = 90): number {
616 const cutoffTime = Date.now() - retentionDays * 24 * 60 * 60 * 1000;
617 const result = db.run("DELETE FROM usage_logs WHERE timestamp < ?", [
618 cutoffTime,
619 ]);
620 return result.changes;
621 },
622
623 // Run all cleanup operations
624 runAllCleanups(): { [key: string]: number } {
625 const results = {
626 sessions: this.cleanupExpiredSessions(),
627 magicLinks: this.cleanupExpiredMagicLinks(),
628 authCodes: this.cleanupExpiredAuthCodes(),
629 oauthTokens: this.cleanupExpiredOAuthTokens(),
630 usageLogs: this.cleanupOldUsageLogs(),
631 };
632
633 return results;
634 },
635};
636
637export default DB;