feat: track api key usage dates

2 files changed, 17 insertions, 1 deletions

diff --git a/packages/trpc/auth.ts b/packages/trpc/auth.ts
index d252bebb..764a0904 100644
--- a/packages/trpc/auth.ts
+++ b/packages/trpc/auth.ts
@@ -125,6 +125,19 @@ export async function authenticateApiKey(key: string, database: Context["db"]) {
     throw new Error("Invalid API Key");
   }
 
+  // Update lastUsedAt with 10-minute throttle to avoid excessive DB writes
+  const tenMinutesAgo = new Date(Date.now() - 10 * 60 * 1000);
+  if (!apiKey.lastUsedAt || apiKey.lastUsedAt < tenMinutesAgo) {
+    // Fire and forget - don't await to avoid blocking the auth response
+    database
+      .update(apiKeys)
+      .set({ lastUsedAt: new Date() })
+      .where(eq(apiKeys.id, apiKey.id))
+      .catch((err) => {
+        console.error("Failed to update API key lastUsedAt:", err);
+      });
+  }
+
   return apiKey.user;
 }
 
diff --git a/packages/trpc/routers/apiKeys.ts b/packages/trpc/routers/apiKeys.ts
index 763bc23a..90de824a 100644
--- a/packages/trpc/routers/apiKeys.ts
+++ b/packages/trpc/routers/apiKeys.ts
@@ -1,5 +1,5 @@
 import { TRPCError } from "@trpc/server";
-import { and, eq } from "drizzle-orm";
+import { and, desc, eq } from "drizzle-orm";
 import { z } from "zod";
 
 import { apiKeys } from "@karakeep/db/schema";
@@ -83,6 +83,7 @@ export const apiKeysAppRouter = router({
             name: z.string(),
             createdAt: z.date(),
             keyId: z.string(),
+            lastUsedAt: z.date().nullish(),
           }),
         ),
       }),
@@ -94,8 +95,10 @@ export const apiKeysAppRouter = router({
           id: true,
           name: true,
           createdAt: true,
+          lastUsedAt: true,
           keyId: true,
         },
+        orderBy: desc(apiKeys.createdAt),
       });
       return { keys: resp };
     }),