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