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