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