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