a cache for slack profile pictures and emojis
dunkirk.sh
1import { Database } from "bun:sqlite";
2import { schedule } from "node-cron";
3import { MigrationManager } from "./migrations/migrationManager";
4import { endpointGroupingMigration } from "./migrations/endpointGroupingMigration";
5import { logGroupingMigration } from "./migrations/logGroupingMigration";
6
7/**
8 * @fileoverview This file contains the Cache class for storing user and emoji data with automatic expiration. To use the module in your project, import the default export and create a new instance of the Cache class. The class provides methods for inserting and retrieving user and emoji data from the cache. The cache automatically purges expired items every hour.
9 * @module cache
10 * @requires bun:sqlite
11 * @requires node-cron
12 */
13
14/**
15 * Base interface for cached items
16 */
17interface CacheItem {
18 id: string;
19 imageUrl: string;
20 expiration: Date;
21}
22
23/**
24 * Interface for cached user data
25 */
26interface User extends CacheItem {
27 type: "user";
28 displayName: string;
29 pronouns: string;
30 userId: string;
31}
32
33/**
34 * Interface for cached emoji data
35 */
36interface Emoji extends CacheItem {
37 type: "emoji";
38 name: string;
39 alias: string | null;
40}
41
42/**
43 * Cache class for storing user and emoji data with automatic expiration
44 */
45class Cache {
46 private db: Database;
47 private defaultExpiration: number; // in hours
48 private onEmojiExpired?: () => void;
49 private analyticsCache: Map<string, { data: any; timestamp: number }> = new Map();
50 private analyticsCacheTTL = 30000; // 30 second cache for faster updates
51
52 // Background user update queue to avoid Slack API limits
53 private userUpdateQueue: Set<string> = new Set();
54 private isProcessingQueue = false;
55 private slackWrapper?: any; // Will be injected after construction
56
57 /**
58 * Creates a new Cache instance
59 * @param dbPath Path to SQLite database file
60 * @param defaultExpirationHours Default cache expiration in hours
61 * @param onEmojiExpired Optional callback function called when emojis expire
62 */
63 constructor(
64 dbPath: string,
65 defaultExpirationHours = 24,
66 onEmojiExpired?: () => void,
67 ) {
68 this.db = new Database(dbPath);
69 this.defaultExpiration = defaultExpirationHours;
70 this.onEmojiExpired = onEmojiExpired;
71
72 this.initDatabase();
73 this.setupPurgeSchedule();
74 this.startQueueProcessor();
75
76 // Run migrations
77 this.runMigrations();
78 }
79
80 /**
81 * Initializes the database tables
82 * @private
83 */
84 private initDatabase() {
85 // Create users table
86 this.db.run(`
87 CREATE TABLE IF NOT EXISTS users (
88 id TEXT PRIMARY KEY,
89 userId TEXT UNIQUE,
90 displayName TEXT,
91 pronouns TEXT,
92 imageUrl TEXT,
93 expiration INTEGER
94 )
95 `);
96
97 // Create emojis table
98 this.db.run(`
99 CREATE TABLE IF NOT EXISTS emojis (
100 id TEXT PRIMARY KEY,
101 name TEXT UNIQUE,
102 alias TEXT,
103 imageUrl TEXT,
104 expiration INTEGER
105 )
106 `);
107
108 // Create request analytics table
109 this.db.run(`
110 CREATE TABLE IF NOT EXISTS request_analytics (
111 id TEXT PRIMARY KEY,
112 endpoint TEXT NOT NULL,
113 method TEXT NOT NULL,
114 status_code INTEGER NOT NULL,
115 user_agent TEXT,
116 ip_address TEXT,
117 timestamp INTEGER NOT NULL,
118 response_time INTEGER
119 )
120 `);
121
122 // Create index for faster queries
123 this.db.run(`
124 CREATE INDEX IF NOT EXISTS idx_request_analytics_timestamp
125 ON request_analytics(timestamp)
126 `);
127
128 this.db.run(`
129 CREATE INDEX IF NOT EXISTS idx_request_analytics_endpoint
130 ON request_analytics(endpoint)
131 `);
132
133 this.db.run(`
134 CREATE INDEX IF NOT EXISTS idx_request_analytics_status_timestamp
135 ON request_analytics(status_code, timestamp)
136 `);
137
138 this.db.run(`
139 CREATE INDEX IF NOT EXISTS idx_request_analytics_response_time
140 ON request_analytics(response_time) WHERE response_time IS NOT NULL
141 `);
142
143 this.db.run(`
144 CREATE INDEX IF NOT EXISTS idx_request_analytics_composite
145 ON request_analytics(timestamp, endpoint, status_code)
146 `);
147
148 // Additional performance indexes
149 this.db.run(`
150 CREATE INDEX IF NOT EXISTS idx_request_analytics_user_agent
151 ON request_analytics(user_agent, timestamp) WHERE user_agent IS NOT NULL
152 `);
153
154 this.db.run(`
155 CREATE INDEX IF NOT EXISTS idx_request_analytics_time_response
156 ON request_analytics(timestamp, response_time) WHERE response_time IS NOT NULL
157 `);
158
159 this.db.run(`
160 CREATE INDEX IF NOT EXISTS idx_request_analytics_exclude_stats
161 ON request_analytics(timestamp, endpoint, status_code) WHERE endpoint != '/stats'
162 `);
163
164 // Enable WAL mode for better concurrent performance
165 this.db.run('PRAGMA journal_mode = WAL');
166 this.db.run('PRAGMA synchronous = NORMAL');
167 this.db.run('PRAGMA cache_size = 50000'); // Increased cache size
168 this.db.run('PRAGMA temp_store = memory');
169 this.db.run('PRAGMA mmap_size = 268435456'); // 256MB memory map
170 this.db.run('PRAGMA page_size = 4096'); // Optimal page size
171
172 // check if there are any emojis in the db
173 if (this.onEmojiExpired) {
174 const result = this.db
175 .query("SELECT COUNT(*) as count FROM emojis WHERE expiration > ?")
176 .get(Date.now()) as { count: number };
177 if (result.count === 0) {
178 this.onEmojiExpired();
179 }
180 }
181 }
182
183 /**
184 * Sets up scheduled tasks for cache maintenance
185 * @private
186 */
187 private setupPurgeSchedule() {
188 // Run purge every hour at 45 minutes (only expired items, analytics cleanup)
189 schedule("45 * * * *", async () => {
190 await this.purgeExpiredItems();
191 await this.lazyUserCleanup();
192 });
193
194 // Schedule emoji updates daily at 3 AM to avoid peak hours
195 schedule("0 3 * * *", async () => {
196 console.log("Scheduled emoji update starting...");
197 if (this.onEmojiExpired) {
198 await this.onEmojiExpired();
199 console.log("Scheduled emoji update completed");
200 }
201 });
202 }
203
204 /**
205 * Run database migrations
206 * @private
207 */
208 private async runMigrations() {
209 try {
210 // Define migrations directly here to avoid circular dependencies
211 // Note: We define migrations both here and in migrations/index.ts
212 // This is intentional to prevent circular imports
213 const migrations = [
214 endpointGroupingMigration,
215 logGroupingMigration
216 ];
217 const migrationManager = new MigrationManager(this.db, migrations);
218 const result = await migrationManager.runMigrations();
219
220 if (result.migrationsApplied > 0) {
221 console.log(`Applied ${result.migrationsApplied} migrations. Latest version: ${result.lastAppliedVersion}`);
222 } else {
223 console.log("No new migrations to apply");
224 }
225 } catch (error) {
226 console.error("Error running migrations:", error);
227 }
228 }
229
230 /**
231 * Purges expired items from the cache
232 * @returns int indicating number of items purged
233 */
234 async purgeExpiredItems(): Promise<number> {
235 // Only purge emojis - users will use lazy loading with longer TTL
236 const result2 = this.db.run("DELETE FROM emojis WHERE expiration < ?", [
237 Date.now(),
238 ]);
239
240 // Clean up old analytics data (older than 30 days) - moved to off-peak hours
241 const thirtyDaysAgo = Date.now() - 30 * 24 * 60 * 60 * 1000;
242 const currentHour = new Date().getHours();
243 // Only run analytics cleanup during off-peak hours (2-6 AM)
244 if (currentHour >= 2 && currentHour < 6) {
245 this.db.run("DELETE FROM request_analytics WHERE timestamp < ?", [
246 thirtyDaysAgo,
247 ]);
248 console.log(`Analytics cleanup completed - removed records older than 30 days`);
249 }
250
251 // Emojis are now updated on schedule, not on expiration
252 return result2.changes;
253 }
254
255 /**
256 * Lazy cleanup of truly expired users (older than 7 days) during off-peak hours only
257 * This runs much less frequently than the old aggressive purging
258 * @private
259 */
260 private async lazyUserCleanup(): Promise<void> {
261 const currentHour = new Date().getHours();
262 // Only run during off-peak hours (3-5 AM) and not every time
263 if (currentHour >= 3 && currentHour < 5 && Math.random() < 0.1) { // 10% chance
264 const sevenDaysAgo = Date.now() - 7 * 24 * 60 * 60 * 1000;
265 const result = this.db.run("DELETE FROM users WHERE expiration < ?", [
266 sevenDaysAgo,
267 ]);
268 if (result.changes > 0) {
269 console.log(`Lazy user cleanup: removed ${result.changes} expired users`);
270 }
271 }
272 }
273
274 /**
275 * Purges cache for a specific user
276 * @param userId The Slack user ID to purge from cache
277 * @returns boolean indicating if any user was purged
278 */
279 async purgeUserCache(userId: string): Promise<boolean> {
280 try {
281 const result = this.db.run("DELETE FROM users WHERE userId = ?", [
282 userId.toUpperCase(),
283 ]);
284 return result.changes > 0;
285 } catch (error) {
286 console.error("Error purging user cache:", error);
287 return false;
288 }
289 }
290
291 /**
292 * Purges all items from the cache
293 * @returns Object containing purge results
294 */
295 async purgeAll(): Promise<{
296 message: string;
297 users: number;
298 emojis: number;
299 }> {
300 const result = this.db.run("DELETE FROM users");
301 const result2 = this.db.run("DELETE FROM emojis");
302
303 if (this.onEmojiExpired) {
304 if (result2.changes > 0) {
305 this.onEmojiExpired();
306 }
307 }
308
309 return {
310 message: "Cache purged",
311 users: result.changes,
312 emojis: result2.changes,
313 };
314 }
315
316 /**
317 * Checks if the cache is healthy by testing database connectivity
318 * @returns boolean indicating if cache is healthy
319 */
320 async healthCheck(): Promise<boolean> {
321 try {
322 this.db.query("SELECT 1").get();
323 return true;
324 } catch (error) {
325 console.error("Cache health check failed:", error);
326 return false;
327 }
328 }
329
330 /**
331 * Sets the Slack wrapper for user updates
332 * @param slackWrapper SlackWrapper instance for API calls
333 */
334 setSlackWrapper(slackWrapper: any) {
335 this.slackWrapper = slackWrapper;
336 }
337
338 /**
339 * Adds a user to the background update queue
340 * @param userId User ID to queue for update
341 * @private
342 */
343 private queueUserUpdate(userId: string) {
344 this.userUpdateQueue.add(userId.toUpperCase());
345 }
346
347 /**
348 * Starts the background queue processor
349 * @private
350 */
351 private startQueueProcessor() {
352 // Process queue every 30 seconds to respect Slack API limits
353 setInterval(async () => {
354 await this.processUserUpdateQueue();
355 }, 30 * 1000);
356 }
357
358 /**
359 * Processes the user update queue with rate limiting
360 * @private
361 */
362 private async processUserUpdateQueue() {
363 if (this.isProcessingQueue || this.userUpdateQueue.size === 0 || !this.slackWrapper) {
364 return;
365 }
366
367 this.isProcessingQueue = true;
368
369 try {
370 // Process up to 3 users at a time to respect API limits
371 const usersToUpdate = Array.from(this.userUpdateQueue).slice(0, 3);
372
373 for (const userId of usersToUpdate) {
374 try {
375 console.log(`Background updating user: ${userId}`);
376 const slackUser = await this.slackWrapper.getUserInfo(userId);
377
378 // Update user in cache with fresh data
379 await this.insertUser(
380 slackUser.id,
381 slackUser.real_name || slackUser.name || "Unknown",
382 slackUser.profile?.pronouns || "",
383 slackUser.profile?.image_512 || slackUser.profile?.image_192 || ""
384 );
385
386 // Remove from queue after successful update
387 this.userUpdateQueue.delete(userId);
388 } catch (error) {
389 console.warn(`Failed to update user ${userId}:`, error);
390 // Remove from queue even if failed to prevent infinite retry
391 this.userUpdateQueue.delete(userId);
392 }
393 }
394 } catch (error) {
395 console.error("Error processing user update queue:", error);
396 } finally {
397 this.isProcessingQueue = false;
398 }
399 }
400
401 /**
402 * Inserts a user into the cache
403 * @param userId Unique identifier for the user
404 * @param imageUrl URL of the user's image
405 * @param expirationHours Optional custom expiration time in hours
406 * @returns boolean indicating success
407 */
408 async insertUser(
409 userId: string,
410 displayName: string,
411 pronouns: string,
412 imageUrl: string,
413 expirationHours?: number,
414 ) {
415 const id = crypto.randomUUID();
416 // Users get longer TTL (7 days) for lazy loading, unless custom expiration specified
417 const userDefaultTTL = 7 * 24; // 7 days in hours
418 const expiration =
419 Date.now() + (expirationHours || userDefaultTTL) * 3600000;
420
421 try {
422 this.db.run(
423 `INSERT INTO users (id, userId, displayName, pronouns, imageUrl, expiration)
424 VALUES (?, ?, ?, ?, ?, ?)
425 ON CONFLICT(userId)
426 DO UPDATE SET imageUrl = ?, expiration = ?`,
427 [
428 id,
429 userId.toUpperCase(),
430 displayName,
431 pronouns,
432 imageUrl,
433 expiration,
434 imageUrl,
435 expiration,
436 ],
437 );
438 return true;
439 } catch (error) {
440 console.error("Error inserting/updating user:", error);
441 return false;
442 }
443 }
444
445 /**
446 * Inserts an emoji into the cache
447 * @param name Name of the emoji
448 * @param imageUrl URL of the emoji image
449 * @param expirationHours Optional custom expiration time in hours
450 * @returns boolean indicating success
451 */
452 async insertEmoji(
453 name: string,
454 alias: string | null,
455 imageUrl: string,
456 expirationHours?: number,
457 ) {
458 const id = crypto.randomUUID();
459 const expiration =
460 Date.now() + (expirationHours || this.defaultExpiration) * 3600000;
461
462 try {
463 this.db.run(
464 `INSERT INTO emojis (id, name, alias, imageUrl, expiration)
465 VALUES (?, ?, ?, ?, ?)
466 ON CONFLICT(name)
467 DO UPDATE SET imageUrl = ?, expiration = ?`,
468 [
469 id,
470 name.toLowerCase(),
471 alias?.toLowerCase() || null,
472 imageUrl,
473 expiration,
474 imageUrl,
475 expiration,
476 ],
477 );
478 return true;
479 } catch (error) {
480 console.error("Error inserting/updating emoji:", error);
481 return false;
482 }
483 }
484
485 /**
486 * Batch inserts multiple emojis into the cache
487 * @param emojis Array of {name, imageUrl} objects to insert
488 * @param expirationHours Optional custom expiration time in hours for all emojis
489 * @returns boolean indicating if all insertions were successful
490 */
491 async batchInsertEmojis(
492 emojis: Array<{ name: string; imageUrl: string; alias: string | null }>,
493 expirationHours?: number,
494 ): Promise<boolean> {
495 try {
496 const expiration =
497 Date.now() + (expirationHours || this.defaultExpiration) * 3600000;
498
499 this.db.transaction(() => {
500 for (const emoji of emojis) {
501 const id = crypto.randomUUID();
502 this.db.run(
503 `INSERT INTO emojis (id, name, alias, imageUrl, expiration)
504 VALUES (?, ?, ?, ?, ?)
505 ON CONFLICT(name)
506 DO UPDATE SET imageUrl = ?, expiration = ?`,
507 [
508 id,
509 emoji.name.toLowerCase(),
510 emoji.alias?.toLowerCase() || null,
511 emoji.imageUrl,
512 expiration,
513 emoji.imageUrl,
514 expiration,
515 ],
516 );
517 }
518 })();
519
520 return true;
521 } catch (error) {
522 console.error("Error batch inserting emojis:", error);
523 return false;
524 }
525 }
526
527 /**
528 * Lists all emoji in the cache
529 * @returns Array of Emoji objects that haven't expired
530 */
531 async listEmojis(): Promise<Emoji[]> {
532 const results = this.db
533 .query("SELECT * FROM emojis WHERE expiration > ?")
534 .all(Date.now()) as Emoji[];
535
536 return results.map((result) => ({
537 type: "emoji",
538 id: result.id,
539 name: result.name,
540 alias: result.alias || null,
541 imageUrl: result.imageUrl,
542 expiration: new Date(result.expiration),
543 }));
544 }
545
546 /**
547 * Retrieves a user from the cache
548 * @param userId Unique identifier of the user
549 * @returns User object if found and not expired, null otherwise
550 */
551 async getUser(userId: string): Promise<User | null> {
552 const result = this.db
553 .query("SELECT * FROM users WHERE userId = ?")
554 .get(userId.toUpperCase()) as User;
555
556 if (!result) {
557 return null;
558 }
559
560 const now = Date.now();
561 const expiration = new Date(result.expiration).getTime();
562
563 // If user is expired, remove and return null
564 if (expiration < now) {
565 this.db.run("DELETE FROM users WHERE userId = ?", [userId]);
566 return null;
567 }
568
569 // Touch-to-refresh: if user is older than 24 hours, extend TTL and queue for background update
570 const twentyFourHoursAgo = now - (24 * 60 * 60 * 1000);
571 const userAge = expiration - (7 * 24 * 60 * 60 * 1000); // When user was originally cached
572
573 if (userAge < twentyFourHoursAgo) {
574 // Extend TTL by another 7 days from now
575 const newExpiration = now + (7 * 24 * 60 * 60 * 1000);
576 this.db.run("UPDATE users SET expiration = ? WHERE userId = ?", [
577 newExpiration,
578 userId.toUpperCase()
579 ]);
580
581 // Queue for background update to get fresh data
582 this.queueUserUpdate(userId);
583
584 console.log(`Touch-refresh: Extended TTL for user ${userId} and queued for update`);
585 }
586
587 return {
588 type: "user",
589 id: result.id,
590 userId: result.userId,
591 displayName: result.displayName,
592 pronouns: result.pronouns,
593 imageUrl: result.imageUrl,
594 expiration: new Date(result.expiration),
595 };
596 }
597
598 /**
599 * Retrieves an emoji from the cache
600 * @param name Name of the emoji
601 * @returns Emoji object if found and not expired, null otherwise
602 */
603 async getEmoji(name: string): Promise<Emoji | null> {
604 const result = this.db
605 .query("SELECT * FROM emojis WHERE name = ? AND expiration > ?")
606 .get(name.toLowerCase(), Date.now()) as Emoji;
607
608 return result
609 ? {
610 type: "emoji",
611 id: result.id,
612 name: result.name,
613 alias: result.alias || null,
614 imageUrl: result.imageUrl,
615 expiration: new Date(result.expiration),
616 }
617 : null;
618 }
619
620 /**
621 * Get all emojis from the cache
622 * @returns Array of all non-expired emojis
623 */
624 async getAllEmojis(): Promise<Emoji[]> {
625 const results = this.db
626 .query("SELECT * FROM emojis WHERE expiration > ?")
627 .all(Date.now()) as Emoji[];
628
629 return results.map(result => ({
630 type: "emoji",
631 id: result.id,
632 name: result.name,
633 alias: result.alias || null,
634 imageUrl: result.imageUrl,
635 expiration: new Date(result.expiration),
636 }));
637 }
638
639 /**
640 * Records a request for analytics
641 * @param endpoint The endpoint that was accessed
642 * @param method HTTP method
643 * @param statusCode HTTP status code
644 * @param userAgent User agent string
645 * @param ipAddress IP address of the client
646 * @param responseTime Response time in milliseconds
647 */
648 async recordRequest(
649 endpoint: string,
650 method: string,
651 statusCode: number,
652 userAgent?: string,
653 ipAddress?: string,
654 responseTime?: number,
655 ): Promise<void> {
656 try {
657 const id = crypto.randomUUID();
658 this.db.run(
659 `INSERT INTO request_analytics
660 (id, endpoint, method, status_code, user_agent, ip_address, timestamp, response_time)
661 VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
662 [
663 id,
664 endpoint,
665 method,
666 statusCode,
667 userAgent || null,
668 ipAddress || null,
669 Date.now(),
670 responseTime || null,
671 ],
672 );
673 } catch (error) {
674 console.error("Error recording request analytics:", error);
675 }
676 }
677
678 /**
679 * Gets request analytics statistics with performance optimizations
680 * @param days Number of days to look back (default: 7)
681 * @returns Analytics data
682 */
683 async getAnalytics(days: number = 7): Promise<{
684 totalRequests: number;
685 requestsByEndpoint: Array<{
686 endpoint: string;
687 count: number;
688 averageResponseTime: number;
689 }>;
690 requestsByStatus: Array<{
691 status: number;
692 count: number;
693 averageResponseTime: number;
694 }>;
695 requestsByDay: Array<{
696 date: string;
697 count: number;
698 averageResponseTime: number;
699 }>;
700 averageResponseTime: number | null;
701 topUserAgents: Array<{ userAgent: string; count: number }>;
702 latencyAnalytics: {
703 percentiles: {
704 p50: number | null;
705 p75: number | null;
706 p90: number | null;
707 p95: number | null;
708 p99: number | null;
709 };
710 distribution: Array<{
711 range: string;
712 count: number;
713 percentage: number;
714 }>;
715 slowestEndpoints: Array<{
716 endpoint: string;
717 averageResponseTime: number;
718 count: number;
719 }>;
720 latencyOverTime: Array<{
721 time: string;
722 averageResponseTime: number;
723 p95: number | null;
724 count: number;
725 }>;
726 };
727 performanceMetrics: {
728 uptime: number;
729 errorRate: number;
730 throughput: number;
731 apdex: number;
732 cachehitRate: number;
733 };
734 peakTraffic: {
735 peakHour: string;
736 peakRequests: number;
737 peakDay: string;
738 peakDayRequests: number;
739 };
740 dashboardMetrics: {
741 statsRequests: number;
742 totalWithStats: number;
743 };
744 trafficOverview: Array<{
745 time: string;
746 routes: Record<string, number>;
747 total: number;
748 }>;
749 }> {
750 // Check cache first
751 const cacheKey = `analytics_${days}`;
752 const cached = this.analyticsCache.get(cacheKey);
753 const now = Date.now();
754
755 if (cached && (now - cached.timestamp) < this.analyticsCacheTTL) {
756 return cached.data;
757 }
758 const cutoffTime = Date.now() - days * 24 * 60 * 60 * 1000;
759
760 // Total requests (excluding stats endpoint)
761 const totalResult = this.db
762 .query(
763 "SELECT COUNT(*) as count FROM request_analytics WHERE timestamp > ? AND endpoint != '/stats'",
764 )
765 .get(cutoffTime) as { count: number };
766
767 // Stats endpoint requests (tracked separately)
768 const statsResult = this.db
769 .query(
770 "SELECT COUNT(*) as count FROM request_analytics WHERE timestamp > ? AND endpoint = '/stats'",
771 )
772 .get(cutoffTime) as { count: number };
773
774 // Get raw endpoint data and group them intelligently (excluding stats)
775 const rawEndpointResults = this.db
776 .query(
777 `
778 SELECT endpoint, COUNT(*) as count, AVG(response_time) as averageResponseTime
779 FROM request_analytics
780 WHERE timestamp > ? AND endpoint != '/stats'
781 GROUP BY endpoint
782 ORDER BY count DESC
783 `,
784 )
785 .all(cutoffTime) as Array<{
786 endpoint: string;
787 count: number;
788 averageResponseTime: number | null;
789 }>;
790
791 // Group endpoints intelligently
792 const endpointGroups: Record<
793 string,
794 { count: number; totalResponseTime: number; requestCount: number }
795 > = {};
796
797 for (const result of rawEndpointResults) {
798 const endpoint = result.endpoint;
799 let groupKey: string;
800
801 if (endpoint === "/" || endpoint === "/dashboard") {
802 groupKey = "Dashboard";
803 } else if (endpoint === "/health") {
804 groupKey = "Health Check";
805 } else if (endpoint === "/swagger" || endpoint.startsWith("/swagger")) {
806 groupKey = "API Documentation";
807 } else if (endpoint === "/emojis") {
808 groupKey = "Emoji List";
809 } else if (endpoint.match(/^\/emojis\/[^\/]+$/)) {
810 groupKey = "Emoji Data";
811 } else if (endpoint.match(/^\/emojis\/[^\/]+\/r$/)) {
812 groupKey = "Emoji Redirects";
813 } else if (endpoint.match(/^\/users\/[^\/]+$/)) {
814 groupKey = "User Data";
815 } else if (endpoint.match(/^\/users\/[^\/]+\/r$/)) {
816 groupKey = "User Redirects";
817 } else if (endpoint.match(/^\/users\/[^\/]+\/purge$/)) {
818 groupKey = "Cache Management";
819 } else if (endpoint === "/reset") {
820 groupKey = "Cache Management";
821 } else {
822 // For any other endpoints, try to categorize them
823 if (endpoint.includes("/users/") && endpoint.includes("/r")) {
824 groupKey = "User Redirects";
825 } else if (endpoint.includes("/users/")) {
826 groupKey = "User Data";
827 } else if (endpoint.includes("/emojis/") && endpoint.includes("/r")) {
828 groupKey = "Emoji Redirects";
829 } else if (endpoint.includes("/emojis/")) {
830 groupKey = "Emoji Data";
831 } else {
832 groupKey = "Other";
833 }
834 }
835
836 if (!endpointGroups[groupKey]) {
837 endpointGroups[groupKey] = {
838 count: 0,
839 totalResponseTime: 0,
840 requestCount: 0,
841 };
842 }
843
844 // Defensive: Only update if groupKey exists (should always exist due to initialization above)
845 const group = endpointGroups[groupKey];
846 if (group) {
847 group.count += result.count;
848 if (
849 result.averageResponseTime !== null &&
850 result.averageResponseTime !== undefined
851 ) {
852 group.totalResponseTime += result.averageResponseTime * result.count;
853 group.requestCount += result.count;
854 }
855 }
856 }
857
858 // Convert back to array format with calculated averages
859 const requestsByEndpoint = Object.entries(endpointGroups)
860 .map(([endpoint, data]) => ({
861 endpoint,
862 count: data.count,
863 averageResponseTime:
864 data.requestCount > 0
865 ? data.totalResponseTime / data.requestCount
866 : 0,
867 }))
868 .sort((a, b) => b.count - a.count);
869
870 // Requests by status code with average response time (excluding stats)
871 const statusResultsRaw = this.db
872 .query(
873 `
874 SELECT status_code as status, COUNT(*) as count, AVG(response_time) as averageResponseTime
875 FROM request_analytics
876 WHERE timestamp > ? AND endpoint != '/stats'
877 GROUP BY status_code
878 ORDER BY count DESC
879 `,
880 )
881 .all(cutoffTime) as Array<{
882 status: number;
883 count: number;
884 averageResponseTime: number | null;
885 }>;
886
887 const statusResults = statusResultsRaw.map((s) => ({
888 status: s.status,
889 count: s.count,
890 averageResponseTime: s.averageResponseTime ?? 0,
891 }));
892
893 // Requests over time - hourly for 1 day, daily for longer periods
894 let timeResults: Array<{
895 date: string;
896 count: number;
897 averageResponseTime: number;
898 }>;
899
900 if (days === 1) {
901 // 15-minute intervals for last 24 hours (excluding stats)
902 const intervalResultsRaw = this.db
903 .query(
904 `
905 SELECT
906 strftime('%Y-%m-%d %H:', datetime(timestamp / 1000, 'unixepoch')) ||
907 CASE
908 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 15 THEN '00'
909 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 30 THEN '15'
910 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 45 THEN '30'
911 ELSE '45'
912 END as date,
913 COUNT(*) as count,
914 AVG(response_time) as averageResponseTime
915 FROM request_analytics
916 WHERE timestamp > ? AND endpoint != '/stats'
917 GROUP BY strftime('%Y-%m-%d %H:', datetime(timestamp / 1000, 'unixepoch')) ||
918 CASE
919 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 15 THEN '00'
920 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 30 THEN '15'
921 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 45 THEN '30'
922 ELSE '45'
923 END
924 ORDER BY date ASC
925 `,
926 )
927 .all(cutoffTime) as Array<{
928 date: string;
929 count: number;
930 averageResponseTime: number | null;
931 }>;
932
933 timeResults = intervalResultsRaw.map((h) => ({
934 date: h.date,
935 count: h.count,
936 averageResponseTime: h.averageResponseTime ?? 0,
937 }));
938 } else if (days <= 7) {
939 // Hourly data for 7 days (excluding stats)
940 const hourResultsRaw = this.db
941 .query(
942 `
943 SELECT
944 strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')) as date,
945 COUNT(*) as count,
946 AVG(response_time) as averageResponseTime
947 FROM request_analytics
948 WHERE timestamp > ? AND endpoint != '/stats'
949 GROUP BY strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch'))
950 ORDER BY date ASC
951 `,
952 )
953 .all(cutoffTime) as Array<{
954 date: string;
955 count: number;
956 averageResponseTime: number | null;
957 }>;
958
959 timeResults = hourResultsRaw.map((h) => ({
960 date: h.date,
961 count: h.count,
962 averageResponseTime: h.averageResponseTime ?? 0,
963 }));
964 } else {
965 // 4-hour intervals for longer periods (excluding stats)
966 const intervalResultsRaw = this.db
967 .query(
968 `
969 SELECT
970 strftime('%Y-%m-%d ', datetime(timestamp / 1000, 'unixepoch')) ||
971 CASE
972 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 4 THEN '00:00'
973 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 8 THEN '04:00'
974 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 12 THEN '08:00'
975 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 16 THEN '12:00'
976 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 20 THEN '16:00'
977 ELSE '20:00'
978 END as date,
979 COUNT(*) as count,
980 AVG(response_time) as averageResponseTime
981 FROM request_analytics
982 WHERE timestamp > ? AND endpoint != '/stats'
983 GROUP BY strftime('%Y-%m-%d ', datetime(timestamp / 1000, 'unixepoch')) ||
984 CASE
985 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 4 THEN '00:00'
986 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 8 THEN '04:00'
987 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 12 THEN '08:00'
988 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 16 THEN '12:00'
989 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 20 THEN '16:00'
990 ELSE '20:00'
991 END
992 ORDER BY date ASC
993 `,
994 )
995 .all(cutoffTime) as Array<{
996 date: string;
997 count: number;
998 averageResponseTime: number | null;
999 }>;
1000
1001 timeResults = intervalResultsRaw.map((d) => ({
1002 date: d.date,
1003 count: d.count,
1004 averageResponseTime: d.averageResponseTime ?? 0,
1005 }));
1006 }
1007
1008 // Average response time (excluding stats)
1009 const avgResponseResult = this.db
1010 .query(
1011 `
1012 SELECT AVG(response_time) as avg
1013 FROM request_analytics
1014 WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'
1015 `,
1016 )
1017 .get(cutoffTime) as { avg: number | null };
1018
1019 // Top user agents (raw strings, excluding stats) - optimized with index hint
1020 const topUserAgents = this.db
1021 .query(
1022 `
1023 SELECT user_agent as userAgent, COUNT(*) as count
1024 FROM request_analytics INDEXED BY idx_request_analytics_user_agent
1025 WHERE timestamp > ? AND user_agent IS NOT NULL AND endpoint != '/stats'
1026 GROUP BY user_agent
1027 ORDER BY count DESC
1028 LIMIT 50
1029 `,
1030 )
1031 .all(cutoffTime) as Array<{ userAgent: string; count: number }>;
1032
1033 // Enhanced Latency Analytics
1034
1035 // Get all response times for percentile calculations (excluding stats)
1036 const responseTimes = this.db
1037 .query(
1038 `
1039 SELECT response_time
1040 FROM request_analytics
1041 WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'
1042 ORDER BY response_time
1043 `,
1044 )
1045 .all(cutoffTime) as Array<{ response_time: number }>;
1046
1047 // Calculate percentiles
1048 const calculatePercentile = (
1049 arr: number[],
1050 percentile: number,
1051 ): number | null => {
1052 if (arr.length === 0) return null;
1053 const index = Math.ceil((percentile / 100) * arr.length) - 1;
1054 return arr[Math.max(0, index)] ?? 0;
1055 };
1056
1057 const sortedTimes = responseTimes
1058 .map((r) => r.response_time)
1059 .sort((a, b) => a - b);
1060 const percentiles = {
1061 p50: calculatePercentile(sortedTimes, 50),
1062 p75: calculatePercentile(sortedTimes, 75),
1063 p90: calculatePercentile(sortedTimes, 90),
1064 p95: calculatePercentile(sortedTimes, 95),
1065 p99: calculatePercentile(sortedTimes, 99),
1066 };
1067
1068 // Response time distribution
1069 const totalWithResponseTime = responseTimes.length;
1070 const distributionRanges = [
1071 { min: 0, max: 50, label: "0-50ms" },
1072 { min: 50, max: 100, label: "50-100ms" },
1073 { min: 100, max: 200, label: "100-200ms" },
1074 { min: 200, max: 500, label: "200-500ms" },
1075 { min: 500, max: 1000, label: "500ms-1s" },
1076 { min: 1000, max: 2000, label: "1-2s" },
1077 { min: 2000, max: 5000, label: "2-5s" },
1078 { min: 5000, max: Infinity, label: "5s+" },
1079 ];
1080
1081 const distribution = distributionRanges.map((range) => {
1082 const count = sortedTimes.filter(
1083 (time) => time >= range.min && time < range.max,
1084 ).length;
1085 return {
1086 range: range.label,
1087 count,
1088 percentage:
1089 totalWithResponseTime > 0 ? (count / totalWithResponseTime) * 100 : 0,
1090 };
1091 });
1092
1093 // Slowest endpoints (grouped)
1094 const slowestEndpoints = requestsByEndpoint
1095 .filter((e) => e.averageResponseTime > 0)
1096 .sort((a, b) => b.averageResponseTime - a.averageResponseTime)
1097 .slice(0, 10);
1098
1099 // Latency over time - hourly for 1 day, daily for longer periods
1100 let latencyOverTime: Array<{
1101 time: string;
1102 averageResponseTime: number;
1103 p95: number | null;
1104 count: number;
1105 }>;
1106
1107 if (days === 1) {
1108 // 15-minute intervals for last 24 hours (excluding stats)
1109 const latencyOverTimeRaw = this.db
1110 .query(
1111 `
1112 SELECT
1113 strftime('%Y-%m-%d %H:', datetime(timestamp / 1000, 'unixepoch')) ||
1114 CASE
1115 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 15 THEN '00'
1116 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 30 THEN '15'
1117 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 45 THEN '30'
1118 ELSE '45'
1119 END as time,
1120 AVG(response_time) as averageResponseTime,
1121 COUNT(*) as count
1122 FROM request_analytics
1123 WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'
1124 GROUP BY strftime('%Y-%m-%d %H:', datetime(timestamp / 1000, 'unixepoch')) ||
1125 CASE
1126 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 15 THEN '00'
1127 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 30 THEN '15'
1128 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 45 THEN '30'
1129 ELSE '45'
1130 END
1131 ORDER BY time ASC
1132 `,
1133 )
1134 .all(cutoffTime) as Array<{
1135 time: string;
1136 averageResponseTime: number;
1137 count: number;
1138 }>;
1139
1140 // For 15-minute intervals, we'll skip P95 calculation to improve performance
1141 latencyOverTime = latencyOverTimeRaw.map((intervalData) => ({
1142 time: intervalData.time,
1143 averageResponseTime: intervalData.averageResponseTime,
1144 p95: null, // Skip P95 for better performance with high granularity
1145 count: intervalData.count,
1146 }));
1147 } else if (days <= 7) {
1148 // Hourly latency data for 7 days (excluding stats)
1149 const latencyOverTimeRaw = this.db
1150 .query(
1151 `
1152 SELECT
1153 strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')) as time,
1154 AVG(response_time) as averageResponseTime,
1155 COUNT(*) as count
1156 FROM request_analytics
1157 WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'
1158 GROUP BY strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch'))
1159 ORDER BY time ASC
1160 `,
1161 )
1162 .all(cutoffTime) as Array<{
1163 time: string;
1164 averageResponseTime: number;
1165 count: number;
1166 }>;
1167
1168 latencyOverTime = latencyOverTimeRaw.map((hourData) => ({
1169 time: hourData.time,
1170 averageResponseTime: hourData.averageResponseTime,
1171 p95: null, // Skip P95 for better performance
1172 count: hourData.count,
1173 }));
1174 } else {
1175 // 4-hour intervals for longer periods (excluding stats)
1176 const latencyOverTimeRaw = this.db
1177 .query(
1178 `
1179 SELECT
1180 strftime('%Y-%m-%d ', datetime(timestamp / 1000, 'unixepoch')) ||
1181 CASE
1182 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 4 THEN '00:00'
1183 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 8 THEN '04:00'
1184 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 12 THEN '08:00'
1185 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 16 THEN '12:00'
1186 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 20 THEN '16:00'
1187 ELSE '20:00'
1188 END as time,
1189 AVG(response_time) as averageResponseTime,
1190 COUNT(*) as count
1191 FROM request_analytics
1192 WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'
1193 GROUP BY strftime('%Y-%m-%d ', datetime(timestamp / 1000, 'unixepoch')) ||
1194 CASE
1195 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 4 THEN '00:00'
1196 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 8 THEN '04:00'
1197 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 12 THEN '08:00'
1198 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 16 THEN '12:00'
1199 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 20 THEN '16:00'
1200 ELSE '20:00'
1201 END
1202 ORDER BY time ASC
1203 `,
1204 )
1205 .all(cutoffTime) as Array<{
1206 time: string;
1207 averageResponseTime: number;
1208 count: number;
1209 }>;
1210
1211 latencyOverTime = latencyOverTimeRaw.map((intervalData) => ({
1212 time: intervalData.time,
1213 averageResponseTime: intervalData.averageResponseTime,
1214 p95: null, // Skip P95 for better performance
1215 count: intervalData.count,
1216 }));
1217 }
1218
1219 // Performance Metrics
1220 const errorRequests = statusResults
1221 .filter((s) => s.status >= 400)
1222 .reduce((sum, s) => sum + s.count, 0);
1223 const errorRate =
1224 totalResult.count > 0 ? (errorRequests / totalResult.count) * 100 : 0;
1225
1226 // Calculate throughput (requests per hour)
1227 const timeSpanHours = days * 24;
1228 const throughput = totalResult.count / timeSpanHours;
1229
1230 // Calculate APDEX score (Application Performance Index)
1231 // Satisfied: <= 100ms, Tolerating: <= 400ms, Frustrated: > 400ms
1232 const satisfiedCount = sortedTimes.filter((t) => t <= 100).length;
1233 const toleratingCount = sortedTimes.filter(
1234 (t) => t > 100 && t <= 400,
1235 ).length;
1236 const apdex =
1237 totalWithResponseTime > 0
1238 ? (satisfiedCount + toleratingCount * 0.5) / totalWithResponseTime
1239 : 0;
1240
1241 // Calculate cache hit rate (redirects vs data endpoints)
1242 const redirectRequests = requestsByEndpoint
1243 .filter((e) =>
1244 e.endpoint === "User Redirects" ||
1245 e.endpoint === "Emoji Redirects")
1246 .reduce((sum, e) => sum + e.count, 0);
1247 const dataRequests = requestsByEndpoint
1248 .filter((e) =>
1249 e.endpoint === "User Data" ||
1250 e.endpoint === "Emoji Data")
1251 .reduce((sum, e) => sum + e.count, 0);
1252 const cachehitRate =
1253 redirectRequests + dataRequests > 0
1254 ? (redirectRequests / (redirectRequests + dataRequests)) * 100
1255 : 0;
1256
1257 // Simulate uptime (would need actual monitoring data)
1258 const uptime = Math.max(0, 100 - errorRate * 2); // Simple approximation
1259
1260 // Peak traffic analysis (excluding stats)
1261 const peakHourData = this.db
1262 .query(
1263 `
1264 SELECT
1265 strftime('%H:00', datetime(timestamp / 1000, 'unixepoch')) as hour,
1266 COUNT(*) as count
1267 FROM request_analytics
1268 WHERE timestamp > ? AND endpoint != '/stats'
1269 GROUP BY strftime('%H:00', datetime(timestamp / 1000, 'unixepoch'))
1270 ORDER BY count DESC
1271 LIMIT 1
1272 `,
1273 )
1274 .get(cutoffTime) as { hour: string; count: number } | null;
1275
1276 const peakDayData = this.db
1277 .query(
1278 `
1279 SELECT
1280 DATE(timestamp / 1000, 'unixepoch') as day,
1281 COUNT(*) as count
1282 FROM request_analytics
1283 WHERE timestamp > ? AND endpoint != '/stats'
1284 GROUP BY DATE(timestamp / 1000, 'unixepoch')
1285 ORDER BY count DESC
1286 LIMIT 1
1287 `,
1288 )
1289 .get(cutoffTime) as { day: string; count: number } | null;
1290
1291 // Traffic Overview - detailed route breakdown over time
1292 let trafficOverview: Array<{
1293 time: string;
1294 routes: Record<string, number>;
1295 total: number;
1296 }>;
1297
1298 if (days === 1) {
1299 // Hourly route breakdown for last 24 hours
1300 const trafficRaw = this.db
1301 .query(
1302 `
1303 SELECT
1304 strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')) as time,
1305 endpoint,
1306 COUNT(*) as count
1307 FROM request_analytics
1308 WHERE timestamp > ? AND endpoint != '/stats'
1309 GROUP BY strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')), endpoint
1310 ORDER BY time ASC
1311 `,
1312 )
1313 .all(cutoffTime) as Array<{
1314 time: string;
1315 endpoint: string;
1316 count: number;
1317 }>;
1318
1319 // Group by time and create route breakdown
1320 const timeGroups: Record<string, Record<string, number>> = {};
1321 for (const row of trafficRaw) {
1322 if (!timeGroups[row.time]) {
1323 timeGroups[row.time] = {};
1324 }
1325
1326 // Apply same grouping logic as endpoints
1327 let groupKey: string;
1328 const endpoint = row.endpoint;
1329
1330 if (endpoint === "/" || endpoint === "/dashboard") {
1331 groupKey = "Dashboard";
1332 } else if (endpoint === "/health") {
1333 groupKey = "Health Check";
1334 } else if (endpoint === "/swagger" || endpoint.startsWith("/swagger")) {
1335 groupKey = "API Documentation";
1336 } else if (endpoint === "/emojis") {
1337 groupKey = "Emoji List";
1338 } else if (endpoint.match(/^\/emojis\/[^\/]+$/)) {
1339 groupKey = "Emoji Data";
1340 } else if (endpoint.match(/^\/emojis\/[^\/]+\/r$/)) {
1341 groupKey = "Emoji Redirects";
1342 } else if (endpoint.match(/^\/users\/[^\/]+$/)) {
1343 groupKey = "User Data";
1344 } else if (endpoint.match(/^\/users\/[^\/]+\/r$/)) {
1345 groupKey = "User Redirects";
1346 } else if (endpoint.match(/^\/users\/[^\/]+\/purge$/)) {
1347 groupKey = "Cache Management";
1348 } else if (endpoint === "/reset") {
1349 groupKey = "Cache Management";
1350 } else {
1351 // For any other endpoints, try to categorize them
1352 if (endpoint.includes("/users/") && endpoint.includes("/r")) {
1353 groupKey = "User Redirects";
1354 } else if (endpoint.includes("/users/")) {
1355 groupKey = "User Data";
1356 } else if (endpoint.includes("/emojis/") && endpoint.includes("/r")) {
1357 groupKey = "Emoji Redirects";
1358 } else if (endpoint.includes("/emojis/")) {
1359 groupKey = "Emoji Data";
1360 } else {
1361 groupKey = "Other";
1362 }
1363 }
1364
1365 const group = timeGroups[row.time];
1366
1367 if (group) {
1368 group[groupKey] = (group[groupKey] || 0) + row.count;
1369 }
1370 }
1371
1372 trafficOverview = Object.entries(timeGroups)
1373 .map(([time, routes]) => ({
1374 time,
1375 routes,
1376 total: Object.values(routes).reduce((sum, count) => sum + count, 0),
1377 }))
1378 .sort((a, b) => a.time.localeCompare(b.time));
1379 } else if (days <= 7) {
1380 // 4-hour intervals for 7 days
1381 const trafficRaw = this.db
1382 .query(
1383 `
1384 SELECT
1385 strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')) as hour,
1386 endpoint,
1387 COUNT(*) as count
1388 FROM request_analytics
1389 WHERE timestamp > ? AND endpoint != '/stats'
1390 GROUP BY strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')), endpoint
1391 ORDER BY hour ASC
1392 `,
1393 )
1394 .all(cutoffTime) as Array<{
1395 hour: string;
1396 endpoint: string;
1397 count: number;
1398 }>;
1399
1400 // Group into 4-hour intervals
1401 const intervalGroups: Record<string, Record<string, number>> = {};
1402 for (const row of trafficRaw) {
1403 const hourStr = row.hour?.split(" ")[1]?.split(":")[0];
1404 const hour = hourStr ? parseInt(hourStr) : 0;
1405 const intervalHour = Math.floor(hour / 4) * 4;
1406 const intervalTime =
1407 row.hour.split(" ")[0] +
1408 ` ${intervalHour.toString().padStart(2, "0")}:00`;
1409
1410 if (!intervalGroups[intervalTime]) {
1411 intervalGroups[intervalTime] = {};
1412 }
1413
1414 // Apply same grouping logic
1415 let groupKey: string;
1416 const endpoint = row.endpoint;
1417
1418 if (endpoint === "/" || endpoint === "/dashboard") {
1419 groupKey = "Dashboard";
1420 } else if (endpoint === "/health") {
1421 groupKey = "Health Check";
1422 } else if (endpoint === "/swagger" || endpoint.startsWith("/swagger")) {
1423 groupKey = "API Documentation";
1424 } else if (endpoint === "/emojis") {
1425 groupKey = "Emoji List";
1426 } else if (endpoint.match(/^\/emojis\/[^\/]+$/)) {
1427 groupKey = "Emoji Data";
1428 } else if (endpoint.match(/^\/emojis\/[^\/]+\/r$/)) {
1429 groupKey = "Emoji Redirects";
1430 } else if (endpoint.match(/^\/users\/[^\/]+$/)) {
1431 groupKey = "User Data";
1432 } else if (endpoint.match(/^\/users\/[^\/]+\/r$/)) {
1433 groupKey = "User Redirects";
1434 } else if (endpoint.match(/^\/users\/[^\/]+\/purge$/)) {
1435 groupKey = "Cache Management";
1436 } else if (endpoint === "/reset") {
1437 groupKey = "Cache Management";
1438 } else {
1439 // For any other endpoints, try to categorize them
1440 if (endpoint.includes("/users/") && endpoint.includes("/r")) {
1441 groupKey = "User Redirects";
1442 } else if (endpoint.includes("/users/")) {
1443 groupKey = "User Data";
1444 } else if (endpoint.includes("/emojis/") && endpoint.includes("/r")) {
1445 groupKey = "Emoji Redirects";
1446 } else if (endpoint.includes("/emojis/")) {
1447 groupKey = "Emoji Data";
1448 } else {
1449 groupKey = "Other";
1450 }
1451 }
1452
1453 intervalGroups[intervalTime][groupKey] =
1454 (intervalGroups[intervalTime][groupKey] || 0) + row.count;
1455 }
1456
1457 trafficOverview = Object.entries(intervalGroups)
1458 .map(([time, routes]) => ({
1459 time,
1460 routes,
1461 total: Object.values(routes).reduce((sum, count) => sum + count, 0),
1462 }))
1463 .sort((a, b) => a.time.localeCompare(b.time));
1464 } else {
1465 // Daily breakdown for longer periods
1466 const trafficRaw = this.db
1467 .query(
1468 `
1469 SELECT
1470 DATE(timestamp / 1000, 'unixepoch') as time,
1471 endpoint,
1472 COUNT(*) as count
1473 FROM request_analytics
1474 WHERE timestamp > ? AND endpoint != '/stats'
1475 GROUP BY DATE(timestamp / 1000, 'unixepoch'), endpoint
1476 ORDER BY time ASC
1477 `,
1478 )
1479 .all(cutoffTime) as Array<{
1480 time: string;
1481 endpoint: string;
1482 count: number;
1483 }>;
1484
1485 // Group by day
1486 const dayGroups: Record<string, Record<string, number>> = {};
1487 for (const row of trafficRaw) {
1488 if (!dayGroups[row.time]) {
1489 dayGroups[row.time] = {};
1490 }
1491
1492 // Apply same grouping logic
1493 let groupKey: string;
1494 const endpoint = row.endpoint;
1495
1496 if (endpoint === "/" || endpoint === "/dashboard") {
1497 groupKey = "Dashboard";
1498 } else if (endpoint === "/health") {
1499 groupKey = "Health Check";
1500 } else if (endpoint === "/swagger" || endpoint.startsWith("/swagger")) {
1501 groupKey = "API Documentation";
1502 } else if (endpoint === "/emojis") {
1503 groupKey = "Emoji List";
1504 } else if (endpoint.match(/^\/emojis\/[^\/]+$/)) {
1505 groupKey = "Emoji Data";
1506 } else if (endpoint.match(/^\/emojis\/[^\/]+\/r$/)) {
1507 groupKey = "Emoji Redirects";
1508 } else if (endpoint.match(/^\/users\/[^\/]+$/)) {
1509 groupKey = "User Data";
1510 } else if (endpoint.match(/^\/users\/[^\/]+\/r$/)) {
1511 groupKey = "User Redirects";
1512 } else if (endpoint.match(/^\/users\/[^\/]+\/purge$/)) {
1513 groupKey = "Cache Management";
1514 } else if (endpoint === "/reset") {
1515 groupKey = "Cache Management";
1516 } else {
1517 // For any other endpoints, try to categorize them
1518 if (endpoint.includes("/users/") && endpoint.includes("/r")) {
1519 groupKey = "User Redirects";
1520 } else if (endpoint.includes("/users/")) {
1521 groupKey = "User Data";
1522 } else if (endpoint.includes("/emojis/") && endpoint.includes("/r")) {
1523 groupKey = "Emoji Redirects";
1524 } else if (endpoint.includes("/emojis/")) {
1525 groupKey = "Emoji Data";
1526 } else {
1527 groupKey = "Other";
1528 }
1529 }
1530 const group = dayGroups[row.time];
1531 if (group) {
1532 group[groupKey] = (group[groupKey] || 0) + row.count;
1533 }
1534 }
1535
1536 trafficOverview = Object.entries(dayGroups)
1537 .map(([time, routes]) => ({
1538 time,
1539 routes,
1540 total: Object.values(routes).reduce((sum, count) => sum + count, 0),
1541 }))
1542 .sort((a, b) => a.time.localeCompare(b.time));
1543 }
1544
1545 const result = {
1546 totalRequests: totalResult.count,
1547 requestsByEndpoint: requestsByEndpoint,
1548 requestsByStatus: statusResults,
1549 requestsByDay: timeResults,
1550 averageResponseTime: avgResponseResult.avg,
1551 topUserAgents: topUserAgents,
1552 latencyAnalytics: {
1553 percentiles,
1554 distribution,
1555 slowestEndpoints,
1556 latencyOverTime,
1557 },
1558 performanceMetrics: {
1559 uptime,
1560 errorRate,
1561 throughput,
1562 apdex,
1563 cachehitRate,
1564 },
1565 peakTraffic: {
1566 peakHour: peakHourData?.hour || "N/A",
1567 peakRequests: peakHourData?.count || 0,
1568 peakDay: peakDayData?.day || "N/A",
1569 peakDayRequests: peakDayData?.count || 0,
1570 },
1571 dashboardMetrics: {
1572 statsRequests: statsResult.count,
1573 totalWithStats: totalResult.count + statsResult.count,
1574 },
1575 trafficOverview,
1576 };
1577
1578 // Cache the result
1579 this.analyticsCache.set(cacheKey, {
1580 data: result,
1581 timestamp: now
1582 });
1583
1584 // Clean up old cache entries (keep only last 5)
1585 if (this.analyticsCache.size > 5) {
1586 const keys = Array.from(this.analyticsCache.keys());
1587 const oldestKey = keys[0];
1588 if (oldestKey) {
1589 this.analyticsCache.delete(oldestKey);
1590 }
1591 }
1592
1593 return result;
1594 }
1595
1596 /**
1597 * Gets essential stats only (fast loading)
1598 * @param days Number of days to look back (default: 7)
1599 * @returns Essential stats data
1600 */
1601 async getEssentialStats(days: number = 7): Promise<{
1602 totalRequests: number;
1603 averageResponseTime: number | null;
1604 uptime: number;
1605 }> {
1606 // Check cache first
1607 const cacheKey = `essential_${days}`;
1608 const cached = this.analyticsCache.get(cacheKey);
1609 const now = Date.now();
1610
1611 if (cached && (now - cached.timestamp) < this.analyticsCacheTTL) {
1612 return cached.data;
1613 }
1614
1615 const cutoffTime = Date.now() - days * 24 * 60 * 60 * 1000;
1616
1617 // Total requests (excluding stats endpoint) - fastest query
1618 const totalResult = this.db
1619 .query(
1620 "SELECT COUNT(*) as count FROM request_analytics WHERE timestamp > ? AND endpoint != '/stats'",
1621 )
1622 .get(cutoffTime) as { count: number };
1623
1624 // Average response time (excluding stats) - simple query
1625 const avgResponseResult = this.db
1626 .query(
1627 "SELECT AVG(response_time) as avg FROM request_analytics WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'",
1628 )
1629 .get(cutoffTime) as { avg: number | null };
1630
1631 // Simple error rate calculation for uptime
1632 const errorRequests = this.db
1633 .query(
1634 "SELECT COUNT(*) as count FROM request_analytics WHERE timestamp > ? AND status_code >= 400 AND endpoint != '/stats'",
1635 )
1636 .get(cutoffTime) as { count: number };
1637
1638 const errorRate = totalResult.count > 0 ? (errorRequests.count / totalResult.count) * 100 : 0;
1639 const uptime = Math.max(0, 100 - errorRate * 2); // Simple approximation
1640
1641 const result = {
1642 totalRequests: totalResult.count,
1643 averageResponseTime: avgResponseResult.avg,
1644 uptime: uptime,
1645 };
1646
1647 // Cache the result
1648 this.analyticsCache.set(cacheKey, {
1649 data: result,
1650 timestamp: now
1651 });
1652
1653 return result;
1654 }
1655
1656 /**
1657 * Gets chart data only (requests and latency over time)
1658 * @param days Number of days to look back (default: 7)
1659 * @returns Chart data
1660 */
1661 async getChartData(days: number = 7): Promise<{
1662 requestsByDay: Array<{
1663 date: string;
1664 count: number;
1665 averageResponseTime: number;
1666 }>;
1667 latencyOverTime: Array<{
1668 time: string;
1669 averageResponseTime: number;
1670 p95: number | null;
1671 count: number;
1672 }>;
1673 }> {
1674 // Check cache first
1675 const cacheKey = `charts_${days}`;
1676 const cached = this.analyticsCache.get(cacheKey);
1677 const now = Date.now();
1678
1679 if (cached && (now - cached.timestamp) < this.analyticsCacheTTL) {
1680 return cached.data;
1681 }
1682
1683 const cutoffTime = Date.now() - days * 24 * 60 * 60 * 1000;
1684
1685 // Reuse the existing time logic from getAnalytics
1686 let timeResults: Array<{
1687 date: string;
1688 count: number;
1689 averageResponseTime: number;
1690 }>;
1691
1692 if (days === 1) {
1693 // 15-minute intervals for last 24 hours (excluding stats)
1694 const intervalResultsRaw = this.db
1695 .query(
1696 `
1697 SELECT
1698 strftime('%Y-%m-%d %H:', datetime(timestamp / 1000, 'unixepoch')) ||
1699 CASE
1700 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 15 THEN '00'
1701 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 30 THEN '15'
1702 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 45 THEN '30'
1703 ELSE '45'
1704 END as date,
1705 COUNT(*) as count,
1706 AVG(response_time) as averageResponseTime
1707 FROM request_analytics
1708 WHERE timestamp > ? AND endpoint != '/stats'
1709 GROUP BY strftime('%Y-%m-%d %H:', datetime(timestamp / 1000, 'unixepoch')) ||
1710 CASE
1711 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 15 THEN '00'
1712 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 30 THEN '15'
1713 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 45 THEN '30'
1714 ELSE '45'
1715 END
1716 ORDER BY date ASC
1717 `,
1718 )
1719 .all(cutoffTime) as Array<{
1720 date: string;
1721 count: number;
1722 averageResponseTime: number | null;
1723 }>;
1724
1725 timeResults = intervalResultsRaw.map((h) => ({
1726 date: h.date,
1727 count: h.count,
1728 averageResponseTime: h.averageResponseTime ?? 0,
1729 }));
1730 } else if (days <= 7) {
1731 // Hourly data for 7 days (excluding stats)
1732 const hourResultsRaw = this.db
1733 .query(
1734 `
1735 SELECT
1736 strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')) as date,
1737 COUNT(*) as count,
1738 AVG(response_time) as averageResponseTime
1739 FROM request_analytics
1740 WHERE timestamp > ? AND endpoint != '/stats'
1741 GROUP BY strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch'))
1742 ORDER BY date ASC
1743 `,
1744 )
1745 .all(cutoffTime) as Array<{
1746 date: string;
1747 count: number;
1748 averageResponseTime: number | null;
1749 }>;
1750
1751 timeResults = hourResultsRaw.map((h) => ({
1752 date: h.date,
1753 count: h.count,
1754 averageResponseTime: h.averageResponseTime ?? 0,
1755 }));
1756 } else {
1757 // 4-hour intervals for longer periods (excluding stats)
1758 const intervalResultsRaw = this.db
1759 .query(
1760 `
1761 SELECT
1762 strftime('%Y-%m-%d ', datetime(timestamp / 1000, 'unixepoch')) ||
1763 CASE
1764 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 4 THEN '00:00'
1765 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 8 THEN '04:00'
1766 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 12 THEN '08:00'
1767 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 16 THEN '12:00'
1768 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 20 THEN '16:00'
1769 ELSE '20:00'
1770 END as date,
1771 COUNT(*) as count,
1772 AVG(response_time) as averageResponseTime
1773 FROM request_analytics
1774 WHERE timestamp > ? AND endpoint != '/stats'
1775 GROUP BY strftime('%Y-%m-%d ', datetime(timestamp / 1000, 'unixepoch')) ||
1776 CASE
1777 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 4 THEN '00:00'
1778 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 8 THEN '04:00'
1779 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 12 THEN '08:00'
1780 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 16 THEN '12:00'
1781 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 20 THEN '16:00'
1782 ELSE '20:00'
1783 END
1784 ORDER BY date ASC
1785 `,
1786 )
1787 .all(cutoffTime) as Array<{
1788 date: string;
1789 count: number;
1790 averageResponseTime: number | null;
1791 }>;
1792
1793 timeResults = intervalResultsRaw.map((d) => ({
1794 date: d.date,
1795 count: d.count,
1796 averageResponseTime: d.averageResponseTime ?? 0,
1797 }));
1798 }
1799
1800 // Latency over time data (reuse from getAnalytics)
1801 let latencyOverTime: Array<{
1802 time: string;
1803 averageResponseTime: number;
1804 p95: number | null;
1805 count: number;
1806 }>;
1807
1808 if (days === 1) {
1809 const latencyOverTimeRaw = this.db
1810 .query(
1811 `
1812 SELECT
1813 strftime('%Y-%m-%d %H:', datetime(timestamp / 1000, 'unixepoch')) ||
1814 CASE
1815 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 15 THEN '00'
1816 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 30 THEN '15'
1817 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 45 THEN '30'
1818 ELSE '45'
1819 END as time,
1820 AVG(response_time) as averageResponseTime,
1821 COUNT(*) as count
1822 FROM request_analytics
1823 WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'
1824 GROUP BY strftime('%Y-%m-%d %H:', datetime(timestamp / 1000, 'unixepoch')) ||
1825 CASE
1826 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 15 THEN '00'
1827 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 30 THEN '15'
1828 WHEN CAST(strftime('%M', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 45 THEN '30'
1829 ELSE '45'
1830 END
1831 ORDER BY time ASC
1832 `,
1833 )
1834 .all(cutoffTime) as Array<{
1835 time: string;
1836 averageResponseTime: number;
1837 count: number;
1838 }>;
1839
1840 latencyOverTime = latencyOverTimeRaw.map((intervalData) => ({
1841 time: intervalData.time,
1842 averageResponseTime: intervalData.averageResponseTime,
1843 p95: null, // Skip P95 for better performance
1844 count: intervalData.count,
1845 }));
1846 } else if (days <= 7) {
1847 const latencyOverTimeRaw = this.db
1848 .query(
1849 `
1850 SELECT
1851 strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')) as time,
1852 AVG(response_time) as averageResponseTime,
1853 COUNT(*) as count
1854 FROM request_analytics
1855 WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'
1856 GROUP BY strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch'))
1857 ORDER BY time ASC
1858 `,
1859 )
1860 .all(cutoffTime) as Array<{
1861 time: string;
1862 averageResponseTime: number;
1863 count: number;
1864 }>;
1865
1866 latencyOverTime = latencyOverTimeRaw.map((hourData) => ({
1867 time: hourData.time,
1868 averageResponseTime: hourData.averageResponseTime,
1869 p95: null, // Skip P95 for better performance
1870 count: hourData.count,
1871 }));
1872 } else {
1873 const latencyOverTimeRaw = this.db
1874 .query(
1875 `
1876 SELECT
1877 strftime('%Y-%m-%d ', datetime(timestamp / 1000, 'unixepoch')) ||
1878 CASE
1879 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 4 THEN '00:00'
1880 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 8 THEN '04:00'
1881 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 12 THEN '08:00'
1882 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 16 THEN '12:00'
1883 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 20 THEN '16:00'
1884 ELSE '20:00'
1885 END as time,
1886 AVG(response_time) as averageResponseTime,
1887 COUNT(*) as count
1888 FROM request_analytics
1889 WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'
1890 GROUP BY strftime('%Y-%m-%d ', datetime(timestamp / 1000, 'unixepoch')) ||
1891 CASE
1892 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 4 THEN '00:00'
1893 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 8 THEN '04:00'
1894 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 12 THEN '08:00'
1895 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 16 THEN '12:00'
1896 WHEN CAST(strftime('%H', datetime(timestamp / 1000, 'unixepoch')) AS INTEGER) < 20 THEN '16:00'
1897 ELSE '20:00'
1898 END
1899 ORDER BY time ASC
1900 `,
1901 )
1902 .all(cutoffTime) as Array<{
1903 time: string;
1904 averageResponseTime: number;
1905 count: number;
1906 }>;
1907
1908 latencyOverTime = latencyOverTimeRaw.map((intervalData) => ({
1909 time: intervalData.time,
1910 averageResponseTime: intervalData.averageResponseTime,
1911 p95: null, // Skip P95 for better performance
1912 count: intervalData.count,
1913 }));
1914 }
1915
1916 const result = {
1917 requestsByDay: timeResults,
1918 latencyOverTime: latencyOverTime,
1919 };
1920
1921 // Cache the result
1922 this.analyticsCache.set(cacheKey, {
1923 data: result,
1924 timestamp: now
1925 });
1926
1927 return result;
1928 }
1929
1930 /**
1931 * Gets user agents data only (slowest loading)
1932 * @param days Number of days to look back (default: 7)
1933 * @returns User agents data
1934 */
1935 async getUserAgents(days: number = 7): Promise<Array<{ userAgent: string; count: number }>> {
1936 // Check cache first
1937 const cacheKey = `useragents_${days}`;
1938 const cached = this.analyticsCache.get(cacheKey);
1939 const now = Date.now();
1940
1941 if (cached && (now - cached.timestamp) < this.analyticsCacheTTL) {
1942 return cached.data;
1943 }
1944
1945 const cutoffTime = Date.now() - days * 24 * 60 * 60 * 1000;
1946
1947 // Top user agents (raw strings, excluding stats) - optimized with index hint
1948 const topUserAgents = this.db
1949 .query(
1950 `
1951 SELECT user_agent as userAgent, COUNT(*) as count
1952 FROM request_analytics INDEXED BY idx_request_analytics_user_agent
1953 WHERE timestamp > ? AND user_agent IS NOT NULL AND endpoint != '/stats'
1954 GROUP BY user_agent
1955 ORDER BY count DESC
1956 LIMIT 50
1957 `,
1958 )
1959 .all(cutoffTime) as Array<{ userAgent: string; count: number }>;
1960
1961 // Cache the result
1962 this.analyticsCache.set(cacheKey, {
1963 data: topUserAgents,
1964 timestamp: now
1965 });
1966
1967 return topUserAgents;
1968 }
1969}
1970
1971export { Cache as SlackCache };