a cache for slack profile pictures and emojis
dunkirk.sh
1import { Database } from "bun:sqlite";
2import { schedule } from "node-cron";
3import { MigrationManager } from "./migrations/migrationManager";
4import { endpointGroupingMigration } from "./migrations/endpointGroupingMigration";
5import { logGroupingMigration } from "./migrations/logGroupingMigration";
6
7/**
8 * @fileoverview This file contains the Cache class for storing user and emoji data with automatic expiration. To use the module in your project, import the default export and create a new instance of the Cache class. The class provides methods for inserting and retrieving user and emoji data from the cache. The cache automatically purges expired items every hour.
9 * @module cache
10 * @requires bun:sqlite
11 * @requires node-cron
12 */
13
14/**
15 * Base interface for cached items
16 */
17interface CacheItem {
18 id: string;
19 imageUrl: string;
20 expiration: Date;
21}
22
23/**
24 * Interface for cached user data
25 */
26interface User extends CacheItem {
27 type: "user";
28 displayName: string;
29 pronouns: string;
30 userId: string;
31}
32
33/**
34 * Interface for cached emoji data
35 */
36interface Emoji extends CacheItem {
37 type: "emoji";
38 name: string;
39 alias: string | null;
40}
41
42/**
43 * Cache class for storing user and emoji data with automatic expiration
44 */
45class Cache {
46 private db: Database;
47 private defaultExpiration: number; // in hours
48 private onEmojiExpired?: () => void;
49
50 /**
51 * Creates a new Cache instance
52 * @param dbPath Path to SQLite database file
53 * @param defaultExpirationHours Default cache expiration in hours
54 * @param onEmojiExpired Optional callback function called when emojis expire
55 */
56 constructor(
57 dbPath: string,
58 defaultExpirationHours = 24,
59 onEmojiExpired?: () => void,
60 ) {
61 this.db = new Database(dbPath);
62 this.defaultExpiration = defaultExpirationHours;
63 this.onEmojiExpired = onEmojiExpired;
64
65 this.initDatabase();
66 this.setupPurgeSchedule();
67
68 // Run migrations
69 this.runMigrations();
70 }
71
72 /**
73 * Initializes the database tables
74 * @private
75 */
76 private initDatabase() {
77 // Create users table
78 this.db.run(`
79 CREATE TABLE IF NOT EXISTS users (
80 id TEXT PRIMARY KEY,
81 userId TEXT UNIQUE,
82 displayName TEXT,
83 pronouns TEXT,
84 imageUrl TEXT,
85 expiration INTEGER
86 )
87 `);
88
89 // Create emojis table
90 this.db.run(`
91 CREATE TABLE IF NOT EXISTS emojis (
92 id TEXT PRIMARY KEY,
93 name TEXT UNIQUE,
94 alias TEXT,
95 imageUrl TEXT,
96 expiration INTEGER
97 )
98 `);
99
100 // Create request analytics table
101 this.db.run(`
102 CREATE TABLE IF NOT EXISTS request_analytics (
103 id TEXT PRIMARY KEY,
104 endpoint TEXT NOT NULL,
105 method TEXT NOT NULL,
106 status_code INTEGER NOT NULL,
107 user_agent TEXT,
108 ip_address TEXT,
109 timestamp INTEGER NOT NULL,
110 response_time INTEGER
111 )
112 `);
113
114 // Create index for faster queries
115 this.db.run(`
116 CREATE INDEX IF NOT EXISTS idx_request_analytics_timestamp
117 ON request_analytics(timestamp)
118 `);
119
120 this.db.run(`
121 CREATE INDEX IF NOT EXISTS idx_request_analytics_endpoint
122 ON request_analytics(endpoint)
123 `);
124
125 // check if there are any emojis in the db
126 if (this.onEmojiExpired) {
127 const result = this.db
128 .query("SELECT COUNT(*) as count FROM emojis WHERE expiration > ?")
129 .get(Date.now()) as { count: number };
130 if (result.count === 0) {
131 this.onEmojiExpired();
132 }
133 }
134 }
135
136 /**
137 * Sets up hourly purge of expired items
138 * @private
139 */
140 private setupPurgeSchedule() {
141 // Run purge every hour
142 schedule("45 * * * *", async () => {
143 await this.purgeExpiredItems();
144 });
145 }
146
147 /**
148 * Run database migrations
149 * @private
150 */
151 private async runMigrations() {
152 try {
153 // Define migrations directly here to avoid circular dependencies
154 // Note: We define migrations both here and in migrations/index.ts
155 // This is intentional to prevent circular imports
156 const migrations = [
157 endpointGroupingMigration,
158 logGroupingMigration
159 ];
160 const migrationManager = new MigrationManager(this.db, migrations);
161 const result = await migrationManager.runMigrations();
162
163 if (result.migrationsApplied > 0) {
164 console.log(`Applied ${result.migrationsApplied} migrations. Latest version: ${result.lastAppliedVersion}`);
165 } else {
166 console.log("No new migrations to apply");
167 }
168 } catch (error) {
169 console.error("Error running migrations:", error);
170 }
171 }
172
173 /**
174 * Purges expired items from the cache
175 * @returns int indicating number of items purged
176 */
177 async purgeExpiredItems(): Promise<number> {
178 const result = this.db.run("DELETE FROM users WHERE expiration < ?", [
179 Date.now(),
180 ]);
181 const result2 = this.db.run("DELETE FROM emojis WHERE expiration < ?", [
182 Date.now(),
183 ]);
184
185 // Clean up old analytics data (older than 30 days)
186 const thirtyDaysAgo = Date.now() - 30 * 24 * 60 * 60 * 1000;
187 this.db.run("DELETE FROM request_analytics WHERE timestamp < ?", [
188 thirtyDaysAgo,
189 ]);
190
191 if (this.onEmojiExpired) {
192 if (result2.changes > 0) {
193 this.onEmojiExpired();
194 }
195 }
196
197 return result.changes + result2.changes;
198 }
199
200 /**
201 * Purges cache for a specific user
202 * @param userId The Slack user ID to purge from cache
203 * @returns boolean indicating if any user was purged
204 */
205 async purgeUserCache(userId: string): Promise<boolean> {
206 try {
207 const result = this.db.run("DELETE FROM users WHERE userId = ?", [
208 userId.toUpperCase(),
209 ]);
210 return result.changes > 0;
211 } catch (error) {
212 console.error("Error purging user cache:", error);
213 return false;
214 }
215 }
216
217 /**
218 * Purges all items from the cache
219 * @returns Object containing purge results
220 */
221 async purgeAll(): Promise<{
222 message: string;
223 users: number;
224 emojis: number;
225 }> {
226 const result = this.db.run("DELETE FROM users");
227 const result2 = this.db.run("DELETE FROM emojis");
228
229 if (this.onEmojiExpired) {
230 if (result2.changes > 0) {
231 this.onEmojiExpired();
232 }
233 }
234
235 return {
236 message: "Cache purged",
237 users: result.changes,
238 emojis: result2.changes,
239 };
240 }
241
242 /**
243 * Checks if the cache is healthy by testing database connectivity
244 * @returns boolean indicating if cache is healthy
245 */
246 async healthCheck(): Promise<boolean> {
247 try {
248 this.db.query("SELECT 1").get();
249 return true;
250 } catch (error) {
251 console.error("Cache health check failed:", error);
252 return false;
253 }
254 }
255
256 /**
257 * Inserts a user into the cache
258 * @param userId Unique identifier for the user
259 * @param imageUrl URL of the user's image
260 * @param expirationHours Optional custom expiration time in hours
261 * @returns boolean indicating success
262 */
263 async insertUser(
264 userId: string,
265 displayName: string,
266 pronouns: string,
267 imageUrl: string,
268 expirationHours?: number,
269 ) {
270 const id = crypto.randomUUID();
271 const expiration =
272 Date.now() + (expirationHours || this.defaultExpiration) * 3600000;
273
274 try {
275 this.db.run(
276 `INSERT INTO users (id, userId, displayName, pronouns, imageUrl, expiration)
277 VALUES (?, ?, ?, ?, ?, ?)
278 ON CONFLICT(userId)
279 DO UPDATE SET imageUrl = ?, expiration = ?`,
280 [
281 id,
282 userId.toUpperCase(),
283 displayName,
284 pronouns,
285 imageUrl,
286 expiration,
287 imageUrl,
288 expiration,
289 ],
290 );
291 return true;
292 } catch (error) {
293 console.error("Error inserting/updating user:", error);
294 return false;
295 }
296 }
297
298 /**
299 * Inserts an emoji into the cache
300 * @param name Name of the emoji
301 * @param imageUrl URL of the emoji image
302 * @param expirationHours Optional custom expiration time in hours
303 * @returns boolean indicating success
304 */
305 async insertEmoji(
306 name: string,
307 alias: string | null,
308 imageUrl: string,
309 expirationHours?: number,
310 ) {
311 const id = crypto.randomUUID();
312 const expiration =
313 Date.now() + (expirationHours || this.defaultExpiration) * 3600000;
314
315 try {
316 this.db.run(
317 `INSERT INTO emojis (id, name, alias, imageUrl, expiration)
318 VALUES (?, ?, ?, ?, ?)
319 ON CONFLICT(name)
320 DO UPDATE SET imageUrl = ?, expiration = ?`,
321 [
322 id,
323 name.toLowerCase(),
324 alias?.toLowerCase() || null,
325 imageUrl,
326 expiration,
327 imageUrl,
328 expiration,
329 ],
330 );
331 return true;
332 } catch (error) {
333 console.error("Error inserting/updating emoji:", error);
334 return false;
335 }
336 }
337
338 /**
339 * Batch inserts multiple emojis into the cache
340 * @param emojis Array of {name, imageUrl} objects to insert
341 * @param expirationHours Optional custom expiration time in hours for all emojis
342 * @returns boolean indicating if all insertions were successful
343 */
344 async batchInsertEmojis(
345 emojis: Array<{ name: string; imageUrl: string; alias: string | null }>,
346 expirationHours?: number,
347 ): Promise<boolean> {
348 try {
349 const expiration =
350 Date.now() + (expirationHours || this.defaultExpiration) * 3600000;
351
352 this.db.transaction(() => {
353 for (const emoji of emojis) {
354 const id = crypto.randomUUID();
355 this.db.run(
356 `INSERT INTO emojis (id, name, alias, imageUrl, expiration)
357 VALUES (?, ?, ?, ?, ?)
358 ON CONFLICT(name)
359 DO UPDATE SET imageUrl = ?, expiration = ?`,
360 [
361 id,
362 emoji.name.toLowerCase(),
363 emoji.alias?.toLowerCase() || null,
364 emoji.imageUrl,
365 expiration,
366 emoji.imageUrl,
367 expiration,
368 ],
369 );
370 }
371 })();
372
373 return true;
374 } catch (error) {
375 console.error("Error batch inserting emojis:", error);
376 return false;
377 }
378 }
379
380 /**
381 * Lists all emoji in the cache
382 * @returns Array of Emoji objects that haven't expired
383 */
384 async listEmojis(): Promise<Emoji[]> {
385 const results = this.db
386 .query("SELECT * FROM emojis WHERE expiration > ?")
387 .all(Date.now()) as Emoji[];
388
389 return results.map((result) => ({
390 type: "emoji",
391 id: result.id,
392 name: result.name,
393 alias: result.alias || null,
394 imageUrl: result.imageUrl,
395 expiration: new Date(result.expiration),
396 }));
397 }
398
399 /**
400 * Retrieves a user from the cache
401 * @param userId Unique identifier of the user
402 * @returns User object if found and not expired, null otherwise
403 */
404 async getUser(userId: string): Promise<User | null> {
405 const result = this.db
406 .query("SELECT * FROM users WHERE userId = ?")
407 .get(userId.toUpperCase()) as User;
408
409 if (!result) {
410 return null;
411 }
412
413 if (new Date(result.expiration).getTime() < Date.now()) {
414 this.db.run("DELETE FROM users WHERE userId = ?", [userId]);
415 return null;
416 }
417
418 return {
419 type: "user",
420 id: result.id,
421 userId: result.userId,
422 displayName: result.displayName,
423 pronouns: result.pronouns,
424 imageUrl: result.imageUrl,
425 expiration: new Date(result.expiration),
426 };
427 }
428
429 /**
430 * Retrieves an emoji from the cache
431 * @param name Name of the emoji
432 * @returns Emoji object if found and not expired, null otherwise
433 */
434 async getEmoji(name: string): Promise<Emoji | null> {
435 const result = this.db
436 .query("SELECT * FROM emojis WHERE name = ? AND expiration > ?")
437 .get(name.toLowerCase(), Date.now()) as Emoji;
438
439 return result
440 ? {
441 type: "emoji",
442 id: result.id,
443 name: result.name,
444 alias: result.alias || null,
445 imageUrl: result.imageUrl,
446 expiration: new Date(result.expiration),
447 }
448 : null;
449 }
450
451 /**
452 * Records a request for analytics
453 * @param endpoint The endpoint that was accessed
454 * @param method HTTP method
455 * @param statusCode HTTP status code
456 * @param userAgent User agent string
457 * @param ipAddress IP address of the client
458 * @param responseTime Response time in milliseconds
459 */
460 async recordRequest(
461 endpoint: string,
462 method: string,
463 statusCode: number,
464 userAgent?: string,
465 ipAddress?: string,
466 responseTime?: number,
467 ): Promise<void> {
468 try {
469 const id = crypto.randomUUID();
470 this.db.run(
471 `INSERT INTO request_analytics
472 (id, endpoint, method, status_code, user_agent, ip_address, timestamp, response_time)
473 VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
474 [
475 id,
476 endpoint,
477 method,
478 statusCode,
479 userAgent || null,
480 ipAddress || null,
481 Date.now(),
482 responseTime || null,
483 ],
484 );
485 } catch (error) {
486 console.error("Error recording request analytics:", error);
487 }
488 }
489
490 /**
491 * Gets request analytics statistics
492 * @param days Number of days to look back (default: 7)
493 * @returns Analytics data
494 */
495 async getAnalytics(days: number = 7): Promise<{
496 totalRequests: number;
497 requestsByEndpoint: Array<{
498 endpoint: string;
499 count: number;
500 averageResponseTime: number;
501 }>;
502 requestsByStatus: Array<{
503 status: number;
504 count: number;
505 averageResponseTime: number;
506 }>;
507 requestsByDay: Array<{
508 date: string;
509 count: number;
510 averageResponseTime: number;
511 }>;
512 averageResponseTime: number | null;
513 topUserAgents: Array<{ userAgent: string; count: number }>;
514 latencyAnalytics: {
515 percentiles: {
516 p50: number | null;
517 p75: number | null;
518 p90: number | null;
519 p95: number | null;
520 p99: number | null;
521 };
522 distribution: Array<{
523 range: string;
524 count: number;
525 percentage: number;
526 }>;
527 slowestEndpoints: Array<{
528 endpoint: string;
529 averageResponseTime: number;
530 count: number;
531 }>;
532 latencyOverTime: Array<{
533 time: string;
534 averageResponseTime: number;
535 p95: number | null;
536 count: number;
537 }>;
538 };
539 performanceMetrics: {
540 uptime: number;
541 errorRate: number;
542 throughput: number;
543 apdex: number;
544 cachehitRate: number;
545 };
546 peakTraffic: {
547 peakHour: string;
548 peakRequests: number;
549 peakDay: string;
550 peakDayRequests: number;
551 };
552 dashboardMetrics: {
553 statsRequests: number;
554 totalWithStats: number;
555 };
556 trafficOverview: Array<{
557 time: string;
558 routes: Record<string, number>;
559 total: number;
560 }>;
561 }> {
562 const cutoffTime = Date.now() - days * 24 * 60 * 60 * 1000;
563
564 // Total requests (excluding stats endpoint)
565 const totalResult = this.db
566 .query(
567 "SELECT COUNT(*) as count FROM request_analytics WHERE timestamp > ? AND endpoint != '/stats'",
568 )
569 .get(cutoffTime) as { count: number };
570
571 // Stats endpoint requests (tracked separately)
572 const statsResult = this.db
573 .query(
574 "SELECT COUNT(*) as count FROM request_analytics WHERE timestamp > ? AND endpoint = '/stats'",
575 )
576 .get(cutoffTime) as { count: number };
577
578 // Get raw endpoint data and group them intelligently (excluding stats)
579 const rawEndpointResults = this.db
580 .query(
581 `
582 SELECT endpoint, COUNT(*) as count, AVG(response_time) as averageResponseTime
583 FROM request_analytics
584 WHERE timestamp > ? AND endpoint != '/stats'
585 GROUP BY endpoint
586 ORDER BY count DESC
587 `,
588 )
589 .all(cutoffTime) as Array<{
590 endpoint: string;
591 count: number;
592 averageResponseTime: number | null;
593 }>;
594
595 // Group endpoints intelligently
596 const endpointGroups: Record<
597 string,
598 { count: number; totalResponseTime: number; requestCount: number }
599 > = {};
600
601 for (const result of rawEndpointResults) {
602 const endpoint = result.endpoint;
603 let groupKey: string;
604
605 if (endpoint === "/" || endpoint === "/dashboard") {
606 groupKey = "Dashboard";
607 } else if (endpoint === "/health") {
608 groupKey = "Health Check";
609 } else if (endpoint === "/swagger" || endpoint.startsWith("/swagger")) {
610 groupKey = "API Documentation";
611 } else if (endpoint === "/emojis") {
612 groupKey = "Emoji List";
613 } else if (endpoint.match(/^\/emojis\/[^\/]+$/)) {
614 groupKey = "Emoji Data";
615 } else if (endpoint.match(/^\/emojis\/[^\/]+\/r$/)) {
616 groupKey = "Emoji Redirects";
617 } else if (endpoint.match(/^\/users\/[^\/]+$/)) {
618 groupKey = "User Data";
619 } else if (endpoint.match(/^\/users\/[^\/]+\/r$/)) {
620 groupKey = "User Redirects";
621 } else if (endpoint.match(/^\/users\/[^\/]+\/purge$/)) {
622 groupKey = "Cache Management";
623 } else if (endpoint === "/reset") {
624 groupKey = "Cache Management";
625 } else {
626 // For any other endpoints, try to categorize them
627 if (endpoint.includes("/users/") && endpoint.includes("/r")) {
628 groupKey = "User Redirects";
629 } else if (endpoint.includes("/users/")) {
630 groupKey = "User Data";
631 } else if (endpoint.includes("/emojis/") && endpoint.includes("/r")) {
632 groupKey = "Emoji Redirects";
633 } else if (endpoint.includes("/emojis/")) {
634 groupKey = "Emoji Data";
635 } else {
636 groupKey = "Other";
637 }
638 }
639
640 if (!endpointGroups[groupKey]) {
641 endpointGroups[groupKey] = {
642 count: 0,
643 totalResponseTime: 0,
644 requestCount: 0,
645 };
646 }
647
648 // Defensive: Only update if groupKey exists (should always exist due to initialization above)
649 const group = endpointGroups[groupKey];
650 if (group) {
651 group.count += result.count;
652 if (
653 result.averageResponseTime !== null &&
654 result.averageResponseTime !== undefined
655 ) {
656 group.totalResponseTime += result.averageResponseTime * result.count;
657 group.requestCount += result.count;
658 }
659 }
660 }
661
662 // Convert back to array format with calculated averages
663 const requestsByEndpoint = Object.entries(endpointGroups)
664 .map(([endpoint, data]) => ({
665 endpoint,
666 count: data.count,
667 averageResponseTime:
668 data.requestCount > 0
669 ? data.totalResponseTime / data.requestCount
670 : 0,
671 }))
672 .sort((a, b) => b.count - a.count);
673
674 // Requests by status code with average response time (excluding stats)
675 const statusResultsRaw = this.db
676 .query(
677 `
678 SELECT status_code as status, COUNT(*) as count, AVG(response_time) as averageResponseTime
679 FROM request_analytics
680 WHERE timestamp > ? AND endpoint != '/stats'
681 GROUP BY status_code
682 ORDER BY count DESC
683 `,
684 )
685 .all(cutoffTime) as Array<{
686 status: number;
687 count: number;
688 averageResponseTime: number | null;
689 }>;
690
691 const statusResults = statusResultsRaw.map((s) => ({
692 status: s.status,
693 count: s.count,
694 averageResponseTime: s.averageResponseTime ?? 0,
695 }));
696
697 // Requests over time - hourly for 1 day, daily for longer periods
698 let timeResults: Array<{
699 date: string;
700 count: number;
701 averageResponseTime: number;
702 }>;
703
704 if (days === 1) {
705 // Hourly data for last 24 hours (excluding stats)
706 const hourResultsRaw = this.db
707 .query(
708 `
709 SELECT
710 strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')) as date,
711 COUNT(*) as count,
712 AVG(response_time) as averageResponseTime
713 FROM request_analytics
714 WHERE timestamp > ? AND endpoint != '/stats'
715 GROUP BY strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch'))
716 ORDER BY date ASC
717 `,
718 )
719 .all(cutoffTime) as Array<{
720 date: string;
721 count: number;
722 averageResponseTime: number | null;
723 }>;
724
725 timeResults = hourResultsRaw.map((h) => ({
726 date: h.date,
727 count: h.count,
728 averageResponseTime: h.averageResponseTime ?? 0,
729 }));
730 } else {
731 // Daily data for longer periods (excluding stats)
732 const dayResultsRaw = this.db
733 .query(
734 `
735 SELECT
736 DATE(timestamp / 1000, 'unixepoch') as date,
737 COUNT(*) as count,
738 AVG(response_time) as averageResponseTime
739 FROM request_analytics
740 WHERE timestamp > ? AND endpoint != '/stats'
741 GROUP BY DATE(timestamp / 1000, 'unixepoch')
742 ORDER BY date ASC
743 `,
744 )
745 .all(cutoffTime) as Array<{
746 date: string;
747 count: number;
748 averageResponseTime: number | null;
749 }>;
750
751 timeResults = dayResultsRaw.map((d) => ({
752 date: d.date,
753 count: d.count,
754 averageResponseTime: d.averageResponseTime ?? 0,
755 }));
756 }
757
758 // Average response time (excluding stats)
759 const avgResponseResult = this.db
760 .query(
761 `
762 SELECT AVG(response_time) as avg
763 FROM request_analytics
764 WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'
765 `,
766 )
767 .get(cutoffTime) as { avg: number | null };
768
769 // Top user agents (simplified and grouped, excluding stats)
770 const rawUserAgentResults = this.db
771 .query(
772 `
773 SELECT user_agent as userAgent, COUNT(*) as count
774 FROM request_analytics
775 WHERE timestamp > ? AND user_agent IS NOT NULL AND endpoint != '/stats'
776 GROUP BY user_agent
777 ORDER BY count DESC
778 LIMIT 20
779 `,
780 )
781 .all(cutoffTime) as Array<{ userAgent: string; count: number }>;
782
783 // Group user agents intelligently
784 const userAgentGroups: Record<string, number> = {};
785
786 for (const result of rawUserAgentResults) {
787 const ua = result.userAgent.toLowerCase();
788 let groupKey: string;
789
790 if (ua.includes("chrome") && !ua.includes("edg")) {
791 groupKey = "Chrome";
792 } else if (ua.includes("firefox")) {
793 groupKey = "Firefox";
794 } else if (ua.includes("safari") && !ua.includes("chrome")) {
795 groupKey = "Safari";
796 } else if (ua.includes("edg")) {
797 groupKey = "Edge";
798 } else if (ua.includes("curl")) {
799 groupKey = "curl";
800 } else if (ua.includes("wget")) {
801 groupKey = "wget";
802 } else if (ua.includes("postman")) {
803 groupKey = "Postman";
804 } else if (
805 ua.includes("bot") ||
806 ua.includes("crawler") ||
807 ua.includes("spider")
808 ) {
809 groupKey = "Bots/Crawlers";
810 } else if (ua.includes("python")) {
811 groupKey = "Python Scripts";
812 } else if (
813 ua.includes("node") ||
814 ua.includes("axios") ||
815 ua.includes("fetch")
816 ) {
817 groupKey = "API Clients";
818 } else {
819 groupKey = "Other";
820 }
821
822 userAgentGroups[groupKey] =
823 (userAgentGroups[groupKey] || 0) + result.count;
824 }
825
826 // Convert back to array format, sorted by count
827 const topUserAgents = Object.entries(userAgentGroups)
828 .map(([userAgent, count]) => ({ userAgent, count }))
829 .sort((a, b) => b.count - a.count)
830 .slice(0, 10);
831
832 // Enhanced Latency Analytics
833
834 // Get all response times for percentile calculations (excluding stats)
835 const responseTimes = this.db
836 .query(
837 `
838 SELECT response_time
839 FROM request_analytics
840 WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'
841 ORDER BY response_time
842 `,
843 )
844 .all(cutoffTime) as Array<{ response_time: number }>;
845
846 // Calculate percentiles
847 const calculatePercentile = (
848 arr: number[],
849 percentile: number,
850 ): number | null => {
851 if (arr.length === 0) return null;
852 const index = Math.ceil((percentile / 100) * arr.length) - 1;
853 return arr[Math.max(0, index)] ?? 0;
854 };
855
856 const sortedTimes = responseTimes
857 .map((r) => r.response_time)
858 .sort((a, b) => a - b);
859 const percentiles = {
860 p50: calculatePercentile(sortedTimes, 50),
861 p75: calculatePercentile(sortedTimes, 75),
862 p90: calculatePercentile(sortedTimes, 90),
863 p95: calculatePercentile(sortedTimes, 95),
864 p99: calculatePercentile(sortedTimes, 99),
865 };
866
867 // Response time distribution
868 const totalWithResponseTime = responseTimes.length;
869 const distributionRanges = [
870 { min: 0, max: 50, label: "0-50ms" },
871 { min: 50, max: 100, label: "50-100ms" },
872 { min: 100, max: 200, label: "100-200ms" },
873 { min: 200, max: 500, label: "200-500ms" },
874 { min: 500, max: 1000, label: "500ms-1s" },
875 { min: 1000, max: 2000, label: "1-2s" },
876 { min: 2000, max: 5000, label: "2-5s" },
877 { min: 5000, max: Infinity, label: "5s+" },
878 ];
879
880 const distribution = distributionRanges.map((range) => {
881 const count = sortedTimes.filter(
882 (time) => time >= range.min && time < range.max,
883 ).length;
884 return {
885 range: range.label,
886 count,
887 percentage:
888 totalWithResponseTime > 0 ? (count / totalWithResponseTime) * 100 : 0,
889 };
890 });
891
892 // Slowest endpoints (grouped)
893 const slowestEndpoints = requestsByEndpoint
894 .filter((e) => e.averageResponseTime > 0)
895 .sort((a, b) => b.averageResponseTime - a.averageResponseTime)
896 .slice(0, 10);
897
898 // Latency over time - hourly for 1 day, daily for longer periods
899 let latencyOverTime: Array<{
900 time: string;
901 averageResponseTime: number;
902 p95: number | null;
903 count: number;
904 }>;
905
906 if (days === 1) {
907 // Hourly latency data for last 24 hours (excluding stats)
908 const latencyOverTimeRaw = this.db
909 .query(
910 `
911 SELECT
912 strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')) as time,
913 AVG(response_time) as averageResponseTime,
914 COUNT(*) as count
915 FROM request_analytics
916 WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'
917 GROUP BY strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch'))
918 ORDER BY time ASC
919 `,
920 )
921 .all(cutoffTime) as Array<{
922 time: string;
923 averageResponseTime: number;
924 count: number;
925 }>;
926
927 // Calculate P95 for each hour
928 latencyOverTime = latencyOverTimeRaw.map((hourData) => {
929 const hourStart = new Date(hourData.time).getTime();
930 const hourEnd = hourStart + 60 * 60 * 1000; // 1 hour later
931
932 const hourResponseTimes = this.db
933 .query(
934 `
935 SELECT response_time
936 FROM request_analytics
937 WHERE timestamp >= ? AND timestamp < ? AND response_time IS NOT NULL AND endpoint != '/stats'
938 ORDER BY response_time
939 `,
940 )
941 .all(hourStart, hourEnd) as Array<{ response_time: number }>;
942
943 const hourTimes = hourResponseTimes
944 .map((r) => r.response_time)
945 .sort((a, b) => a - b);
946 const p95 = calculatePercentile(hourTimes, 95);
947
948 return {
949 time: hourData.time,
950 averageResponseTime: hourData.averageResponseTime,
951 p95,
952 count: hourData.count,
953 };
954 });
955 } else {
956 // Daily latency data for longer periods (excluding stats)
957 const latencyOverTimeRaw = this.db
958 .query(
959 `
960 SELECT
961 DATE(timestamp / 1000, 'unixepoch') as time,
962 AVG(response_time) as averageResponseTime,
963 COUNT(*) as count
964 FROM request_analytics
965 WHERE timestamp > ? AND response_time IS NOT NULL AND endpoint != '/stats'
966 GROUP BY DATE(timestamp / 1000, 'unixepoch')
967 ORDER BY time ASC
968 `,
969 )
970 .all(cutoffTime) as Array<{
971 time: string;
972 averageResponseTime: number;
973 count: number;
974 }>;
975
976 // Calculate P95 for each day
977 latencyOverTime = latencyOverTimeRaw.map((dayData) => {
978 const dayStart = new Date(dayData.time + " 00:00:00").getTime();
979 const dayEnd = dayStart + 24 * 60 * 60 * 1000; // 1 day later
980
981 const dayResponseTimes = this.db
982 .query(
983 `
984 SELECT response_time
985 FROM request_analytics
986 WHERE timestamp >= ? AND timestamp < ? AND response_time IS NOT NULL AND endpoint != '/stats'
987 ORDER BY response_time
988 `,
989 )
990 .all(dayStart, dayEnd) as Array<{ response_time: number }>;
991
992 const dayTimes = dayResponseTimes
993 .map((r) => r.response_time)
994 .sort((a, b) => a - b);
995 const p95 = calculatePercentile(dayTimes, 95);
996
997 return {
998 time: dayData.time,
999 averageResponseTime: dayData.averageResponseTime,
1000 p95,
1001 count: dayData.count,
1002 };
1003 });
1004 }
1005
1006 // Performance Metrics
1007 const errorRequests = statusResults
1008 .filter((s) => s.status >= 400)
1009 .reduce((sum, s) => sum + s.count, 0);
1010 const errorRate =
1011 totalResult.count > 0 ? (errorRequests / totalResult.count) * 100 : 0;
1012
1013 // Calculate throughput (requests per hour)
1014 const timeSpanHours = days * 24;
1015 const throughput = totalResult.count / timeSpanHours;
1016
1017 // Calculate APDEX score (Application Performance Index)
1018 // Satisfied: <= 100ms, Tolerating: <= 400ms, Frustrated: > 400ms
1019 const satisfiedCount = sortedTimes.filter((t) => t <= 100).length;
1020 const toleratingCount = sortedTimes.filter(
1021 (t) => t > 100 && t <= 400,
1022 ).length;
1023 const apdex =
1024 totalWithResponseTime > 0
1025 ? (satisfiedCount + toleratingCount * 0.5) / totalWithResponseTime
1026 : 0;
1027
1028 // Calculate cache hit rate (redirects vs data endpoints)
1029 const redirectRequests = requestsByEndpoint
1030 .filter((e) =>
1031 e.endpoint === "User Redirects" ||
1032 e.endpoint === "Emoji Redirects")
1033 .reduce((sum, e) => sum + e.count, 0);
1034 const dataRequests = requestsByEndpoint
1035 .filter((e) =>
1036 e.endpoint === "User Data" ||
1037 e.endpoint === "Emoji Data")
1038 .reduce((sum, e) => sum + e.count, 0);
1039 const cachehitRate =
1040 redirectRequests + dataRequests > 0
1041 ? (redirectRequests / (redirectRequests + dataRequests)) * 100
1042 : 0;
1043
1044 // Simulate uptime (would need actual monitoring data)
1045 const uptime = Math.max(0, 100 - errorRate * 2); // Simple approximation
1046
1047 // Peak traffic analysis (excluding stats)
1048 const peakHourData = this.db
1049 .query(
1050 `
1051 SELECT
1052 strftime('%H:00', datetime(timestamp / 1000, 'unixepoch')) as hour,
1053 COUNT(*) as count
1054 FROM request_analytics
1055 WHERE timestamp > ? AND endpoint != '/stats'
1056 GROUP BY strftime('%H:00', datetime(timestamp / 1000, 'unixepoch'))
1057 ORDER BY count DESC
1058 LIMIT 1
1059 `,
1060 )
1061 .get(cutoffTime) as { hour: string; count: number } | null;
1062
1063 const peakDayData = this.db
1064 .query(
1065 `
1066 SELECT
1067 DATE(timestamp / 1000, 'unixepoch') as day,
1068 COUNT(*) as count
1069 FROM request_analytics
1070 WHERE timestamp > ? AND endpoint != '/stats'
1071 GROUP BY DATE(timestamp / 1000, 'unixepoch')
1072 ORDER BY count DESC
1073 LIMIT 1
1074 `,
1075 )
1076 .get(cutoffTime) as { day: string; count: number } | null;
1077
1078 // Traffic Overview - detailed route breakdown over time
1079 let trafficOverview: Array<{
1080 time: string;
1081 routes: Record<string, number>;
1082 total: number;
1083 }>;
1084
1085 if (days === 1) {
1086 // Hourly route breakdown for last 24 hours
1087 const trafficRaw = this.db
1088 .query(
1089 `
1090 SELECT
1091 strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')) as time,
1092 endpoint,
1093 COUNT(*) as count
1094 FROM request_analytics
1095 WHERE timestamp > ? AND endpoint != '/stats'
1096 GROUP BY strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')), endpoint
1097 ORDER BY time ASC
1098 `,
1099 )
1100 .all(cutoffTime) as Array<{
1101 time: string;
1102 endpoint: string;
1103 count: number;
1104 }>;
1105
1106 // Group by time and create route breakdown
1107 const timeGroups: Record<string, Record<string, number>> = {};
1108 for (const row of trafficRaw) {
1109 if (!timeGroups[row.time]) {
1110 timeGroups[row.time] = {};
1111 }
1112
1113 // Apply same grouping logic as endpoints
1114 let groupKey: string;
1115 const endpoint = row.endpoint;
1116
1117 if (endpoint === "/" || endpoint === "/dashboard") {
1118 groupKey = "Dashboard";
1119 } else if (endpoint === "/health") {
1120 groupKey = "Health Check";
1121 } else if (endpoint === "/swagger" || endpoint.startsWith("/swagger")) {
1122 groupKey = "API Documentation";
1123 } else if (endpoint === "/emojis") {
1124 groupKey = "Emoji List";
1125 } else if (endpoint.match(/^\/emojis\/[^\/]+$/)) {
1126 groupKey = "Emoji Data";
1127 } else if (endpoint.match(/^\/emojis\/[^\/]+\/r$/)) {
1128 groupKey = "Emoji Redirects";
1129 } else if (endpoint.match(/^\/users\/[^\/]+$/)) {
1130 groupKey = "User Data";
1131 } else if (endpoint.match(/^\/users\/[^\/]+\/r$/)) {
1132 groupKey = "User Redirects";
1133 } else if (endpoint.match(/^\/users\/[^\/]+\/purge$/)) {
1134 groupKey = "Cache Management";
1135 } else if (endpoint === "/reset") {
1136 groupKey = "Cache Management";
1137 } else {
1138 // For any other endpoints, try to categorize them
1139 if (endpoint.includes("/users/") && endpoint.includes("/r")) {
1140 groupKey = "User Redirects";
1141 } else if (endpoint.includes("/users/")) {
1142 groupKey = "User Data";
1143 } else if (endpoint.includes("/emojis/") && endpoint.includes("/r")) {
1144 groupKey = "Emoji Redirects";
1145 } else if (endpoint.includes("/emojis/")) {
1146 groupKey = "Emoji Data";
1147 } else {
1148 groupKey = "Other";
1149 }
1150 }
1151
1152 const group = timeGroups[row.time];
1153
1154 if (group) {
1155 group[groupKey] = (group[groupKey] || 0) + row.count;
1156 }
1157 }
1158
1159 trafficOverview = Object.entries(timeGroups)
1160 .map(([time, routes]) => ({
1161 time,
1162 routes,
1163 total: Object.values(routes).reduce((sum, count) => sum + count, 0),
1164 }))
1165 .sort((a, b) => a.time.localeCompare(b.time));
1166 } else if (days <= 7) {
1167 // 4-hour intervals for 7 days
1168 const trafficRaw = this.db
1169 .query(
1170 `
1171 SELECT
1172 strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')) as hour,
1173 endpoint,
1174 COUNT(*) as count
1175 FROM request_analytics
1176 WHERE timestamp > ? AND endpoint != '/stats'
1177 GROUP BY strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')), endpoint
1178 ORDER BY hour ASC
1179 `,
1180 )
1181 .all(cutoffTime) as Array<{
1182 hour: string;
1183 endpoint: string;
1184 count: number;
1185 }>;
1186
1187 // Group into 4-hour intervals
1188 const intervalGroups: Record<string, Record<string, number>> = {};
1189 for (const row of trafficRaw) {
1190 const hourStr = row.hour?.split(" ")[1]?.split(":")[0];
1191 const hour = hourStr ? parseInt(hourStr) : 0;
1192 const intervalHour = Math.floor(hour / 4) * 4;
1193 const intervalTime =
1194 row.hour.split(" ")[0] +
1195 ` ${intervalHour.toString().padStart(2, "0")}:00`;
1196
1197 if (!intervalGroups[intervalTime]) {
1198 intervalGroups[intervalTime] = {};
1199 }
1200
1201 // Apply same grouping logic
1202 let groupKey: string;
1203 const endpoint = row.endpoint;
1204
1205 if (endpoint === "/" || endpoint === "/dashboard") {
1206 groupKey = "Dashboard";
1207 } else if (endpoint === "/health") {
1208 groupKey = "Health Check";
1209 } else if (endpoint === "/swagger" || endpoint.startsWith("/swagger")) {
1210 groupKey = "API Documentation";
1211 } else if (endpoint === "/emojis") {
1212 groupKey = "Emoji List";
1213 } else if (endpoint.match(/^\/emojis\/[^\/]+$/)) {
1214 groupKey = "Emoji Data";
1215 } else if (endpoint.match(/^\/emojis\/[^\/]+\/r$/)) {
1216 groupKey = "Emoji Redirects";
1217 } else if (endpoint.match(/^\/users\/[^\/]+$/)) {
1218 groupKey = "User Data";
1219 } else if (endpoint.match(/^\/users\/[^\/]+\/r$/)) {
1220 groupKey = "User Redirects";
1221 } else if (endpoint.match(/^\/users\/[^\/]+\/purge$/)) {
1222 groupKey = "Cache Management";
1223 } else if (endpoint === "/reset") {
1224 groupKey = "Cache Management";
1225 } else {
1226 // For any other endpoints, try to categorize them
1227 if (endpoint.includes("/users/") && endpoint.includes("/r")) {
1228 groupKey = "User Redirects";
1229 } else if (endpoint.includes("/users/")) {
1230 groupKey = "User Data";
1231 } else if (endpoint.includes("/emojis/") && endpoint.includes("/r")) {
1232 groupKey = "Emoji Redirects";
1233 } else if (endpoint.includes("/emojis/")) {
1234 groupKey = "Emoji Data";
1235 } else {
1236 groupKey = "Other";
1237 }
1238 }
1239
1240 intervalGroups[intervalTime][groupKey] =
1241 (intervalGroups[intervalTime][groupKey] || 0) + row.count;
1242 }
1243
1244 trafficOverview = Object.entries(intervalGroups)
1245 .map(([time, routes]) => ({
1246 time,
1247 routes,
1248 total: Object.values(routes).reduce((sum, count) => sum + count, 0),
1249 }))
1250 .sort((a, b) => a.time.localeCompare(b.time));
1251 } else {
1252 // Daily breakdown for longer periods
1253 const trafficRaw = this.db
1254 .query(
1255 `
1256 SELECT
1257 DATE(timestamp / 1000, 'unixepoch') as time,
1258 endpoint,
1259 COUNT(*) as count
1260 FROM request_analytics
1261 WHERE timestamp > ? AND endpoint != '/stats'
1262 GROUP BY DATE(timestamp / 1000, 'unixepoch'), endpoint
1263 ORDER BY time ASC
1264 `,
1265 )
1266 .all(cutoffTime) as Array<{
1267 time: string;
1268 endpoint: string;
1269 count: number;
1270 }>;
1271
1272 // Group by day
1273 const dayGroups: Record<string, Record<string, number>> = {};
1274 for (const row of trafficRaw) {
1275 if (!dayGroups[row.time]) {
1276 dayGroups[row.time] = {};
1277 }
1278
1279 // Apply same grouping logic
1280 let groupKey: string;
1281 const endpoint = row.endpoint;
1282
1283 if (endpoint === "/" || endpoint === "/dashboard") {
1284 groupKey = "Dashboard";
1285 } else if (endpoint === "/health") {
1286 groupKey = "Health Check";
1287 } else if (endpoint === "/swagger" || endpoint.startsWith("/swagger")) {
1288 groupKey = "API Documentation";
1289 } else if (endpoint === "/emojis") {
1290 groupKey = "Emoji List";
1291 } else if (endpoint.match(/^\/emojis\/[^\/]+$/)) {
1292 groupKey = "Emoji Data";
1293 } else if (endpoint.match(/^\/emojis\/[^\/]+\/r$/)) {
1294 groupKey = "Emoji Redirects";
1295 } else if (endpoint.match(/^\/users\/[^\/]+$/)) {
1296 groupKey = "User Data";
1297 } else if (endpoint.match(/^\/users\/[^\/]+\/r$/)) {
1298 groupKey = "User Redirects";
1299 } else if (endpoint.match(/^\/users\/[^\/]+\/purge$/)) {
1300 groupKey = "Cache Management";
1301 } else if (endpoint === "/reset") {
1302 groupKey = "Cache Management";
1303 } else {
1304 // For any other endpoints, try to categorize them
1305 if (endpoint.includes("/users/") && endpoint.includes("/r")) {
1306 groupKey = "User Redirects";
1307 } else if (endpoint.includes("/users/")) {
1308 groupKey = "User Data";
1309 } else if (endpoint.includes("/emojis/") && endpoint.includes("/r")) {
1310 groupKey = "Emoji Redirects";
1311 } else if (endpoint.includes("/emojis/")) {
1312 groupKey = "Emoji Data";
1313 } else {
1314 groupKey = "Other";
1315 }
1316 }
1317 const group = dayGroups[row.time];
1318 if (group) {
1319 group[groupKey] = (group[groupKey] || 0) + row.count;
1320 }
1321 }
1322
1323 trafficOverview = Object.entries(dayGroups)
1324 .map(([time, routes]) => ({
1325 time,
1326 routes,
1327 total: Object.values(routes).reduce((sum, count) => sum + count, 0),
1328 }))
1329 .sort((a, b) => a.time.localeCompare(b.time));
1330 }
1331
1332 return {
1333 totalRequests: totalResult.count,
1334 requestsByEndpoint: requestsByEndpoint,
1335 requestsByStatus: statusResults,
1336 requestsByDay: timeResults,
1337 averageResponseTime: avgResponseResult.avg,
1338 topUserAgents: topUserAgents,
1339 latencyAnalytics: {
1340 percentiles,
1341 distribution,
1342 slowestEndpoints,
1343 latencyOverTime,
1344 },
1345 performanceMetrics: {
1346 uptime,
1347 errorRate,
1348 throughput,
1349 apdex,
1350 cachehitRate,
1351 },
1352 peakTraffic: {
1353 peakHour: peakHourData?.hour || "N/A",
1354 peakRequests: peakHourData?.count || 0,
1355 peakDay: peakDayData?.day || "N/A",
1356 peakDayRequests: peakDayData?.count || 0,
1357 },
1358 dashboardMetrics: {
1359 statsRequests: statsResult.count,
1360 totalWithStats: totalResult.count + statsResult.count,
1361 },
1362 trafficOverview,
1363 };
1364 }
1365}
1366
1367export { Cache as SlackCache };