import { Database } from "bun:sqlite";
import { createCipheriv, createDecipheriv, randomBytes } from "node:crypto";
const db = new Database(process.env.DATABASE_PATH || "./canvas-mcp.db");
// In-memory cache for verified API keys
interface ApiKeyCacheEntry {
userId: number;
verifiedAt: number;
}
const apiKeyCache = new Map<string, ApiKeyCacheEntry>();
const CACHE_TTL = parseInt(process.env.API_KEY_CACHE_TTL || "900000", 10); // 15 minutes default
// Cache cleanup interval (runs every 5 minutes)
setInterval(
() => {
const now = Date.now();
for (const [key, entry] of apiKeyCache.entries()) {
if (now - entry.verifiedAt > CACHE_TTL) {
apiKeyCache.delete(key);
}
}
},
5 * 60 * 1000,
);
// Initialize database schema
db.exec(`
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
canvas_user_id TEXT,
canvas_domain TEXT,
email TEXT,
canvas_access_token TEXT,
canvas_refresh_token TEXT,
mcp_api_key TEXT UNIQUE,
created_at INTEGER NOT NULL DEFAULT (unixepoch() * 1000),
last_used_at INTEGER,
token_expires_at INTEGER
);
CREATE TABLE IF NOT EXISTS usage_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
endpoint TEXT NOT NULL,
timestamp INTEGER NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS sessions (
id TEXT PRIMARY KEY,
user_id INTEGER,
canvas_domain TEXT NOT NULL,
state TEXT,
api_key TEXT,
created_at INTEGER NOT NULL,
expires_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS magic_links (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULL,
token TEXT UNIQUE NOT NULL,
expires_at INTEGER NOT NULL,
used INTEGER DEFAULT 0,
created_at INTEGER DEFAULT (unixepoch() * 1000)
);
CREATE TABLE IF NOT EXISTS auth_codes (
code TEXT PRIMARY KEY,
user_id INTEGER NOT NULL,
client_id TEXT NOT NULL,
redirect_uri TEXT NOT NULL,
code_challenge TEXT NOT NULL,
code_challenge_method TEXT NOT NULL,
scope TEXT NOT NULL,
expires_at INTEGER NOT NULL,
created_at INTEGER DEFAULT (unixepoch() * 1000),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS oauth_tokens (
token TEXT PRIMARY KEY,
user_id INTEGER NOT NULL,
scope TEXT NOT NULL,
expires_at INTEGER NOT NULL,
created_at INTEGER DEFAULT (unixepoch() * 1000),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_users_api_key ON users(mcp_api_key);
CREATE INDEX IF NOT EXISTS idx_users_canvas_id ON users(canvas_user_id);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_usage_logs_user_id ON usage_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_magic_links_token ON magic_links(token);
CREATE INDEX IF NOT EXISTS idx_magic_links_email ON magic_links(email);
CREATE INDEX IF NOT EXISTS idx_auth_codes_code ON auth_codes(code);
CREATE INDEX IF NOT EXISTS idx_oauth_tokens_token ON oauth_tokens(token);
`);
// Encryption utilities
const ENCRYPTION_KEY = Buffer.from(process.env.ENCRYPTION_KEY || "", "base64");
const ALGORITHM = "aes-256-gcm";
function encrypt(text: string): string {
const iv = randomBytes(16);
const cipher = createCipheriv(ALGORITHM, ENCRYPTION_KEY, iv);
let encrypted = cipher.update(text, "utf8", "hex");
encrypted += cipher.final("hex");
const authTag = cipher.getAuthTag();
// Return: iv:authTag:encrypted
return `${iv.toString("hex")}:${authTag.toString("hex")}:${encrypted}`;
}
function decrypt(encryptedData: string): string {
const [ivHex, authTagHex, encrypted] = encryptedData.split(":");
const iv = Buffer.from(ivHex, "hex");
const authTag = Buffer.from(authTagHex, "hex");
const decipher = createDecipheriv(ALGORITHM, ENCRYPTION_KEY, iv);
decipher.setAuthTag(authTag);
let decrypted = decipher.update(encrypted, "hex", "utf8");
decrypted += decipher.final("utf8");
return decrypted;
}
// Generate secure API key
function generateApiKey(): string {
return `cmcp_${randomBytes(32).toString("base64url")}`;
}
// Hash API key for storage
async function hashApiKey(apiKey: string): Promise<string> {
return await Bun.password.hash(apiKey, {
algorithm: "argon2id",
memoryCost: 19456,
timeCost: 2,
});
}
// Verify API key
async function verifyApiKey(apiKey: string, hash: string): Promise<boolean> {
return await Bun.password.verify(apiKey, hash);
}
export interface User {
id: number;
canvas_user_id: string;
canvas_domain: string;
email?: string;
canvas_access_token: string;
canvas_refresh_token?: string;
mcp_api_key: string;
created_at: number;
last_used_at?: number;
token_expires_at?: number;
}
export const DB = {
// Raw database access
raw: db,
// Create or update user after OAuth
async createOrUpdateUser(data: {
canvas_user_id: string;
canvas_domain: string;
email?: string;
canvas_access_token: string;
canvas_refresh_token?: string;
token_expires_at?: number;
}): Promise<{ user: User; apiKey: string | null; isNewUser: boolean }> {
const encryptedToken = encrypt(data.canvas_access_token);
const encryptedRefreshToken = data.canvas_refresh_token
? encrypt(data.canvas_refresh_token)
: null;
// Check if user exists by canvas_user_id or email
let existing = db
.query("SELECT * FROM users WHERE canvas_user_id = ?")
.get(data.canvas_user_id) as User | null;
// If not found by canvas_user_id, check by email (for magic link users)
if (!existing && data.email) {
existing = db
.query("SELECT * FROM users WHERE email = ? AND canvas_user_id IS NULL")
.get(data.email) as User | null;
}
if (existing) {
// Check if user needs an API key (magic link users)
let apiKey: string | null = null;
let hashedApiKey = existing.mcp_api_key;
if (!hashedApiKey) {
// Generate API key for magic link users connecting Canvas for first time
apiKey = generateApiKey();
hashedApiKey = await hashApiKey(apiKey);
}
// Update existing user (might not have canvas_user_id if from magic link)
db.run(
`UPDATE users SET
canvas_user_id = ?,
canvas_domain = ?,
canvas_access_token = ?,
canvas_refresh_token = ?,
token_expires_at = ?,
last_used_at = ?,
mcp_api_key = ?
WHERE id = ?`,
[
data.canvas_user_id,
data.canvas_domain,
encryptedToken,
encryptedRefreshToken,
data.token_expires_at,
Date.now(),
hashedApiKey,
existing.id,
],
);
const user = db
.query("SELECT * FROM users WHERE id = ?")
.get(existing.id) as User;
// Return API key only if we just generated it (for magic link users)
const isNewUser = apiKey !== null;
return { user, apiKey, isNewUser };
} else {
// Create new user with API key
const apiKey = generateApiKey();
const hashedApiKey = await hashApiKey(apiKey);
const result = db.run(
`INSERT INTO users (
canvas_user_id, canvas_domain, email,
canvas_access_token, canvas_refresh_token,
mcp_api_key, created_at, token_expires_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
[
data.canvas_user_id,
data.canvas_domain,
data.email,
encryptedToken,
encryptedRefreshToken,
hashedApiKey,
Date.now(),
data.token_expires_at,
],
);
const user = db
.query("SELECT * FROM users WHERE id = ?")
.get(result.lastInsertRowid) as User;
return { user, apiKey, isNewUser: true };
}
},
// Get user by API key (with caching for performance)
async getUserByApiKey(apiKey: string): Promise<User | null> {
// Check cache first for O(1) lookup
const cached = apiKeyCache.get(apiKey);
if (cached && Date.now() - cached.verifiedAt < CACHE_TTL) {
// Cache hit - fast path
const user = db
.query("SELECT * FROM users WHERE id = ?")
.get(cached.userId) as User | null;
if (user) {
return user;
}
// User was deleted - invalidate cache entry
apiKeyCache.delete(apiKey);
}
// Cache miss - perform full verification (slow path)
const users = db
.query("SELECT * FROM users WHERE mcp_api_key IS NOT NULL")
.all() as User[];
for (const user of users) {
if (await verifyApiKey(apiKey, user.mcp_api_key)) {
// Cache the verified key for future requests
apiKeyCache.set(apiKey, {
userId: user.id,
verifiedAt: Date.now(),
});
return user;
}
}
return null;
},
// Get user by Canvas user ID
getUserByCanvasId(canvas_user_id: string): User | null {
return db
.query("SELECT * FROM users WHERE canvas_user_id = ?")
.get(canvas_user_id) as User | null;
},
// Get decrypted Canvas token for user
getCanvasToken(user: User): string {
return decrypt(user.canvas_access_token);
},
// Get decrypted refresh token
getRefreshToken(user: User): string | null {
return user.canvas_refresh_token
? decrypt(user.canvas_refresh_token)
: null;
},
// Log API usage
logUsage(userId: number, endpoint: string) {
db.run(
"INSERT INTO usage_logs (user_id, endpoint, timestamp) VALUES (?, ?, ?)",
[userId, endpoint, Date.now()],
);
},
// Get usage stats for user
getUsageStats(userId: number, since?: number) {
const query = since
? "SELECT * FROM usage_logs WHERE user_id = ? AND timestamp >= ?"
: "SELECT * FROM usage_logs WHERE user_id = ?";
const params = since ? [userId, since] : [userId];
return db.query(query).all(...params);
},
// Update last used timestamp
updateLastUsed(userId: number) {
db.run("UPDATE users SET last_used_at = ? WHERE id = ?", [
Date.now(),
userId,
]);
},
// Regenerate API key
async regenerateApiKey(userId: number): Promise<string> {
// Invalidate all cached entries for this user
for (const [key, entry] of apiKeyCache.entries()) {
if (entry.userId === userId) {
apiKeyCache.delete(key);
}
}
const newApiKey = generateApiKey();
const hashedApiKey = await hashApiKey(newApiKey);
db.run("UPDATE users SET mcp_api_key = ? WHERE id = ?", [
hashedApiKey,
userId,
]);
return newApiKey;
},
// Session management
createSession(
sessionId: string,
data: {
user_id?: number;
canvas_domain: string;
state: string;
api_key?: string;
maxAge: number; // in seconds
},
) {
const now = Date.now();
db.run(
`INSERT INTO sessions (id, user_id, canvas_domain, state, api_key, created_at, expires_at)
VALUES (?, ?, ?, ?, ?, ?, ?)`,
[
sessionId,
data.user_id || null,
data.canvas_domain,
data.state,
data.api_key || null,
now,
now + data.maxAge * 1000,
],
);
},
getSession(sessionId: string) {
return db
.query("SELECT * FROM sessions WHERE id = ? AND expires_at > ?")
.get(sessionId, Date.now()) as any;
},
updateSession(
sessionId: string,
data: Partial<{ user_id: number; api_key: string }>,
) {
const updates: string[] = [];
const values: any[] = [];
if (data.user_id !== undefined) {
updates.push("user_id = ?");
values.push(data.user_id);
}
if (data.api_key !== undefined) {
updates.push("api_key = ?");
values.push(data.api_key);
}
if (updates.length > 0) {
values.push(sessionId);
db.run(`UPDATE sessions SET ${updates.join(", ")} WHERE id = ?`, values);
}
},
deleteSession(sessionId: string) {
db.run("DELETE FROM sessions WHERE id = ?", [sessionId]);
},
clearApiKeyFromSession(sessionId: string) {
db.run("UPDATE sessions SET api_key = NULL WHERE id = ?", [sessionId]);
},
// Get session by API key (for MCP authentication)
getSessionByToken(token: string) {
return db
.query("SELECT * FROM sessions WHERE api_key = ? AND expires_at > ?")
.get(token, Date.now()) as any;
},
// Magic link authentication
createMagicLink(email: string, token: string, expiresAt: number) {
return db.run(
"INSERT INTO magic_links (email, token, expires_at) VALUES (?, ?, ?)",
[email, token, expiresAt],
);
},
getMagicLink(token: string) {
return db
.query(
"SELECT * FROM magic_links WHERE token = ? AND expires_at > ? AND used = 0",
)
.get(token, Date.now()) as any;
},
markMagicLinkUsed(token: string) {
return db.run("UPDATE magic_links SET used = 1 WHERE token = ?", [token]);
},
getUserByEmail(email: string) {
return db.query("SELECT * FROM users WHERE email = ?").get(email) as any;
},
// Update user with Canvas credentials (for magic link users)
async updateUserCanvas(
userId: number,
canvasUserId: string,
canvasDomain: string,
canvasToken: string,
): Promise<{ apiKey: string | null }> {
const existing = db
.query("SELECT * FROM users WHERE id = ?")
.get(userId) as User | null;
if (!existing) {
throw new Error("User not found");
}
const encryptedToken = encrypt(canvasToken);
// Generate API key if user doesn't have one
let apiKey: string | null = null;
let hashedApiKey = existing.mcp_api_key;
if (!hashedApiKey) {
apiKey = generateApiKey();
hashedApiKey = await hashApiKey(apiKey);
}
// Update user with Canvas credentials
db.run(
`UPDATE users SET
canvas_user_id = ?,
canvas_domain = ?,
canvas_access_token = ?,
mcp_api_key = ?,
last_used_at = ?
WHERE id = ?`,
[
canvasUserId,
canvasDomain,
encryptedToken,
hashedApiKey,
Date.now(),
userId,
],
);
return { apiKey };
},
// Rate limiting for magic links
canSendMagicLink(email: string, cooldownMs: number = 60000): boolean {
// Check if a magic link was sent recently (within cooldown period)
const recent = db
.query(
"SELECT * FROM magic_links WHERE email = ? AND created_at > ? ORDER BY created_at DESC LIMIT 1",
)
.get(email, Date.now() - cooldownMs) as any;
return !recent;
},
getLastMagicLinkTime(email: string): number | null {
const recent = db
.query(
"SELECT created_at FROM magic_links WHERE email = ? ORDER BY created_at DESC LIMIT 1",
)
.get(email) as any;
return recent ? recent.created_at : null;
},
// OAuth tokens
createOAuthToken(
userId: number,
scope: string,
expiresIn: number = 86400000,
): string {
const token = generateApiKey(); // Reuse the API key generator
const expiresAt = Date.now() + expiresIn;
db.run(
"INSERT INTO oauth_tokens (token, user_id, scope, expires_at) VALUES (?, ?, ?, ?)",
[token, userId, scope, expiresAt],
);
return token;
},
getUserByOAuthToken(token: string): User | null {
const tokenData = db
.query("SELECT * FROM oauth_tokens WHERE token = ? AND expires_at > ?")
.get(token, Date.now()) as any;
if (!tokenData) {
return null;
}
return db
.query("SELECT * FROM users WHERE id = ?")
.get(tokenData.user_id) as User | null;
},
// Cache management utilities
clearApiKeyCache() {
apiKeyCache.clear();
},
invalidateUserCache(userId: number) {
for (const [key, entry] of apiKeyCache.entries()) {
if (entry.userId === userId) {
apiKeyCache.delete(key);
}
}
},
getApiKeyCacheStats() {
return {
size: apiKeyCache.size,
ttl: CACHE_TTL,
};
},
// Background cleanup operations (run these periodically, not on request path)
cleanupExpiredSessions(): number {
const result = db.run("DELETE FROM sessions WHERE expires_at < ?", [
Date.now(),
]);
return result.changes;
},
cleanupExpiredMagicLinks(): number {
const result = db.run("DELETE FROM magic_links WHERE expires_at < ?", [
Date.now(),
]);
return result.changes;
},
cleanupExpiredAuthCodes(): number {
const result = db.run("DELETE FROM auth_codes WHERE expires_at < ?", [
Date.now(),
]);
return result.changes;
},
cleanupExpiredOAuthTokens(): number {
const result = db.run("DELETE FROM oauth_tokens WHERE expires_at < ?", [
Date.now(),
]);
return result.changes;
},
// Clean up old usage logs (keep last 90 days)
cleanupOldUsageLogs(retentionDays: number = 90): number {
const cutoffTime = Date.now() - retentionDays * 24 * 60 * 60 * 1000;
const result = db.run("DELETE FROM usage_logs WHERE timestamp < ?", [
cutoffTime,
]);
return result.changes;
},
// Run all cleanup operations
runAllCleanups(): { [key: string]: number } {
const results = {
sessions: this.cleanupExpiredSessions(),
magicLinks: this.cleanupExpiredMagicLinks(),
authCodes: this.cleanupExpiredAuthCodes(),
oauthTokens: this.cleanupExpiredOAuthTokens(),
usageLogs: this.cleanupOldUsageLogs(),
};
return results;
},
};
export default DB;
