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) =>
988 e.endpoint === "User Redirects" ||
989 e.endpoint === "Emoji Redirects")
990 .reduce((sum, e) => sum + e.count, 0);
991 const dataRequests = requestsByEndpoint
992 .filter((e) =>
993 e.endpoint === "User Data" ||
994 e.endpoint === "Emoji Data")
995 .reduce((sum, e) => sum + e.count, 0);
996 const cachehitRate =
997 redirectRequests + dataRequests > 0
998 ? (redirectRequests / (redirectRequests + dataRequests)) * 100
999 : 0;
1000
1001 // Simulate uptime (would need actual monitoring data)
1002 const uptime = Math.max(0, 100 - errorRate * 2); // Simple approximation
1003
1004 // Peak traffic analysis (excluding stats)
1005 const peakHourData = this.db
1006 .query(
1007 `
1008 SELECT
1009 strftime('%H:00', datetime(timestamp / 1000, 'unixepoch')) as hour,
1010 COUNT(*) as count
1011 FROM request_analytics
1012 WHERE timestamp > ? AND endpoint != '/stats'
1013 GROUP BY strftime('%H:00', datetime(timestamp / 1000, 'unixepoch'))
1014 ORDER BY count DESC
1015 LIMIT 1
1016 `,
1017 )
1018 .get(cutoffTime) as { hour: string; count: number } | null;
1019
1020 const peakDayData = this.db
1021 .query(
1022 `
1023 SELECT
1024 DATE(timestamp / 1000, 'unixepoch') as day,
1025 COUNT(*) as count
1026 FROM request_analytics
1027 WHERE timestamp > ? AND endpoint != '/stats'
1028 GROUP BY DATE(timestamp / 1000, 'unixepoch')
1029 ORDER BY count DESC
1030 LIMIT 1
1031 `,
1032 )
1033 .get(cutoffTime) as { day: string; count: number } | null;
1034
1035 // Traffic Overview - detailed route breakdown over time
1036 let trafficOverview: Array<{
1037 time: string;
1038 routes: Record<string, number>;
1039 total: number;
1040 }>;
1041
1042 if (days === 1) {
1043 // Hourly route breakdown for last 24 hours
1044 const trafficRaw = this.db
1045 .query(
1046 `
1047 SELECT
1048 strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')) as time,
1049 endpoint,
1050 COUNT(*) as count
1051 FROM request_analytics
1052 WHERE timestamp > ? AND endpoint != '/stats'
1053 GROUP BY strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')), endpoint
1054 ORDER BY time ASC
1055 `,
1056 )
1057 .all(cutoffTime) as Array<{
1058 time: string;
1059 endpoint: string;
1060 count: number;
1061 }>;
1062
1063 // Group by time and create route breakdown
1064 const timeGroups: Record<string, Record<string, number>> = {};
1065 for (const row of trafficRaw) {
1066 if (!timeGroups[row.time]) {
1067 timeGroups[row.time] = {};
1068 }
1069
1070 // Apply same grouping logic as endpoints
1071 let groupKey: string;
1072 const endpoint = row.endpoint;
1073
1074 if (endpoint === "/" || endpoint === "/dashboard") {
1075 groupKey = "Dashboard";
1076 } else if (endpoint === "/health") {
1077 groupKey = "Health Check";
1078 } else if (endpoint === "/swagger" || endpoint.startsWith("/swagger")) {
1079 groupKey = "API Documentation";
1080 } else if (endpoint === "/emojis") {
1081 groupKey = "Emoji List";
1082 } else if (endpoint.match(/^\/emojis\/[^\/]+$/)) {
1083 groupKey = "Emoji Data";
1084 } else if (endpoint.match(/^\/emojis\/[^\/]+\/r$/)) {
1085 groupKey = "Emoji Redirects";
1086 } else if (endpoint.match(/^\/users\/[^\/]+$/)) {
1087 groupKey = "User Data";
1088 } else if (endpoint.match(/^\/users\/[^\/]+\/r$/)) {
1089 groupKey = "User Redirects";
1090 } else if (endpoint.match(/^\/users\/[^\/]+\/purge$/)) {
1091 groupKey = "Cache Management";
1092 } else if (endpoint === "/reset") {
1093 groupKey = "Cache Management";
1094 } else {
1095 groupKey = endpoint;
1096 }
1097
1098 const group = timeGroups[row.time];
1099
1100 if (group) {
1101 group[groupKey] = (group[groupKey] || 0) + row.count;
1102 }
1103 }
1104
1105 trafficOverview = Object.entries(timeGroups)
1106 .map(([time, routes]) => ({
1107 time,
1108 routes,
1109 total: Object.values(routes).reduce((sum, count) => sum + count, 0),
1110 }))
1111 .sort((a, b) => a.time.localeCompare(b.time));
1112 } else if (days <= 7) {
1113 // 4-hour intervals for 7 days
1114 const trafficRaw = this.db
1115 .query(
1116 `
1117 SELECT
1118 strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')) as hour,
1119 endpoint,
1120 COUNT(*) as count
1121 FROM request_analytics
1122 WHERE timestamp > ? AND endpoint != '/stats'
1123 GROUP BY strftime('%Y-%m-%d %H:00', datetime(timestamp / 1000, 'unixepoch')), endpoint
1124 ORDER BY hour ASC
1125 `,
1126 )
1127 .all(cutoffTime) as Array<{
1128 hour: string;
1129 endpoint: string;
1130 count: number;
1131 }>;
1132
1133 // Group into 4-hour intervals
1134 const intervalGroups: Record<string, Record<string, number>> = {};
1135 for (const row of trafficRaw) {
1136 const hourStr = row.hour?.split(" ")[1]?.split(":")[0];
1137 const hour = hourStr ? parseInt(hourStr) : 0;
1138 const intervalHour = Math.floor(hour / 4) * 4;
1139 const intervalTime =
1140 row.hour.split(" ")[0] +
1141 ` ${intervalHour.toString().padStart(2, "0")}:00`;
1142
1143 if (!intervalGroups[intervalTime]) {
1144 intervalGroups[intervalTime] = {};
1145 }
1146
1147 // Apply same grouping logic
1148 let groupKey: string;
1149 const endpoint = row.endpoint;
1150
1151 if (endpoint === "/" || endpoint === "/dashboard") {
1152 groupKey = "Dashboard";
1153 } else if (endpoint === "/health") {
1154 groupKey = "Health Check";
1155 } else if (endpoint === "/swagger" || endpoint.startsWith("/swagger")) {
1156 groupKey = "API Documentation";
1157 } else if (endpoint === "/emojis") {
1158 groupKey = "Emoji List";
1159 } else if (endpoint.match(/^\/emojis\/[^\/]+$/)) {
1160 groupKey = "Emoji Data";
1161 } else if (endpoint.match(/^\/emojis\/[^\/]+\/r$/)) {
1162 groupKey = "Emoji Redirects";
1163 } else if (endpoint.match(/^\/users\/[^\/]+$/)) {
1164 groupKey = "User Data";
1165 } else if (endpoint.match(/^\/users\/[^\/]+\/r$/)) {
1166 groupKey = "User Redirects";
1167 } else if (endpoint.match(/^\/users\/[^\/]+\/purge$/)) {
1168 groupKey = "Cache Management";
1169 } else if (endpoint === "/reset") {
1170 groupKey = "Cache Management";
1171 } else {
1172 groupKey = endpoint;
1173 }
1174
1175 intervalGroups[intervalTime][groupKey] =
1176 (intervalGroups[intervalTime][groupKey] || 0) + row.count;
1177 }
1178
1179 trafficOverview = Object.entries(intervalGroups)
1180 .map(([time, routes]) => ({
1181 time,
1182 routes,
1183 total: Object.values(routes).reduce((sum, count) => sum + count, 0),
1184 }))
1185 .sort((a, b) => a.time.localeCompare(b.time));
1186 } else {
1187 // Daily breakdown for longer periods
1188 const trafficRaw = this.db
1189 .query(
1190 `
1191 SELECT
1192 DATE(timestamp / 1000, 'unixepoch') as time,
1193 endpoint,
1194 COUNT(*) as count
1195 FROM request_analytics
1196 WHERE timestamp > ? AND endpoint != '/stats'
1197 GROUP BY DATE(timestamp / 1000, 'unixepoch'), endpoint
1198 ORDER BY time ASC
1199 `,
1200 )
1201 .all(cutoffTime) as Array<{
1202 time: string;
1203 endpoint: string;
1204 count: number;
1205 }>;
1206
1207 // Group by day
1208 const dayGroups: Record<string, Record<string, number>> = {};
1209 for (const row of trafficRaw) {
1210 if (!dayGroups[row.time]) {
1211 dayGroups[row.time] = {};
1212 }
1213
1214 // Apply same grouping logic
1215 let groupKey: string;
1216 const endpoint = row.endpoint;
1217
1218 if (endpoint === "/" || endpoint === "/dashboard") {
1219 groupKey = "Dashboard";
1220 } else if (endpoint === "/health") {
1221 groupKey = "Health Check";
1222 } else if (endpoint === "/swagger" || endpoint.startsWith("/swagger")) {
1223 groupKey = "API Documentation";
1224 } else if (endpoint === "/emojis") {
1225 groupKey = "Emoji List";
1226 } else if (endpoint.match(/^\/emojis\/[^\/]+$/)) {
1227 groupKey = "Emoji Data";
1228 } else if (endpoint.match(/^\/emojis\/[^\/]+\/r$/)) {
1229 groupKey = "Emoji Redirects";
1230 } else if (endpoint.match(/^\/users\/[^\/]+$/)) {
1231 groupKey = "User Data";
1232 } else if (endpoint.match(/^\/users\/[^\/]+\/r$/)) {
1233 groupKey = "User Redirects";
1234 } else if (endpoint.match(/^\/users\/[^\/]+\/purge$/)) {
1235 groupKey = "Cache Management";
1236 } else if (endpoint === "/reset") {
1237 groupKey = "Cache Management";
1238 } else {
1239 groupKey = endpoint;
1240 }
1241 const group = dayGroups[row.time];
1242 if (group) {
1243 group[groupKey] = (group[groupKey] || 0) + row.count;
1244 }
1245 }
1246
1247 trafficOverview = Object.entries(dayGroups)
1248 .map(([time, routes]) => ({
1249 time,
1250 routes,
1251 total: Object.values(routes).reduce((sum, count) => sum + count, 0),
1252 }))
1253 .sort((a, b) => a.time.localeCompare(b.time));
1254 }
1255
1256 return {
1257 totalRequests: totalResult.count,
1258 requestsByEndpoint: requestsByEndpoint,
1259 requestsByStatus: statusResults,
1260 requestsByDay: timeResults,
1261 averageResponseTime: avgResponseResult.avg,
1262 topUserAgents: topUserAgents,
1263 latencyAnalytics: {
1264 percentiles,
1265 distribution,
1266 slowestEndpoints,
1267 latencyOverTime,
1268 },
1269 performanceMetrics: {
1270 uptime,
1271 errorRate,
1272 throughput,
1273 apdex,
1274 cachehitRate,
1275 },
1276 peakTraffic: {
1277 peakHour: peakHourData?.hour || "N/A",
1278 peakRequests: peakHourData?.count || 0,
1279 peakDay: peakDayData?.day || "N/A",
1280 peakDayRequests: peakDayData?.count || 0,
1281 },
1282 dashboardMetrics: {
1283 statsRequests: statsResult.count,
1284 totalWithStats: totalResult.count + statsResult.count,
1285 },
1286 trafficOverview,
1287 };
1288 }
1289}
1290
1291export { Cache as SlackCache };