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