[{"_1":2,"_175":-5,"_176":-5},"loaderData",{"_3":4,"_91":92},"root",{"_5":6,"_34":35,"_64":65},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","ENV",{"_36":37,"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","7f439e1a7c8854d0a3e79c4918a7c5fa89d43a5d","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","API_URL","https://api.val.town","clerkState",{"_66":67},"__internal_clerk_state",{"_68":69,"_70":45,"_71":72,"_73":72,"_74":20,"_75":72,"_76":72,"_77":72,"_78":72,"_79":80,"_89":20,"_90":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_81":20,"_19":20,"_82":72,"_83":84,"_85":72,"_86":45,"_87":20,"_88":72},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_93":94,"_95":96,"_97":98,"_99":100,"_149":150,"_172":173,"_174":-5},"handle","tijs","name","location-feed-generator","path","backend/services/user-stats-service.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":110,"_111":112,"_12":113,"_114":-5,"_115":20,"_116":-5,"_117":118,"_14":119,"_120":121,"_122":121,"_123":124,"_125":124,"_126":124,"_127":121,"_128":121,"_23":129,"_132":133,"_135":136,"_148":20},"id","db7bc7d6-58ab-11f0-bdec-f69ea79377d9","createdAt",["D",1751615559344],"deletedAt","legacyEnvVars","privacy","public","description","This is the Anchor AppView - location based feed generator","imageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/318e963f-ae91-4683-a733-6c4a3da95500/square512","14049850-28c1-11f0-89c9-c7d0e2d9e1ea","pinnedFileId","pinPreview","screenshotVersion","likeCount",1,"c04d51b2-a7dc-46ca-ad6f-52f47c2906a8","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_130":131},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yd2Nod2N0bE1MWFdEUlRYd083ZE5pbXJ1bDAifQ","org",{"_93":94,"_134":131,"_12":113},"avatar","branch",{"_137":138,"_95":43,"_101":139,"_140":102,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":-5,"_146":96,"_147":94},"version",1447,"db7c07aa-58ab-11f0-bdec-f69ea79377d9","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":151,"_95":152,"_153":154,"_155":156,"_157":139,"_158":159,"_160":161,"_103":162,"_137":118,"_163":164,"_165":166,"_167":20,"_168":-5,"_97":169},"8b40247c-a35d-11f0-a5b6-0224a6c84d84","user-stats-service.ts","fileId","f68cac06-a35c-11f0-a358-0224a6c84d84","parentId","73359fc4-754a-11f0-8437-0224a6c84d84","branchId","branchVersion",1332,"latest",true,["D",1759828261189],"fileType","script","content","/**\n * User Statistics Service\n * Extracts user counts, handles, and PDS server information from OAuth sessions\n */\n\nimport { db } from \"../database/db.ts\";\nimport { ironSessionStorageTable } from \"../database/schema.ts\";\nimport { like } from \"https://esm.sh/drizzle-orm@0.44.5\";\n\nexport interface UserSession {\n did: string;\n handle: string;\n pdsUrl: string;\n createdAt: number;\n expiresAt?: number;\n isExpired: boolean;\n checkinCount?: number;\n}\n\nexport interface UserStats {\n totalUsers: number;\n activeUsers: number;\n expiredUsers: number;\n uniquePdsServers: number;\n usersByPds: Record;\n recentUsers: UserSession[];\n topPdsServers: Array<{ pdsUrl: string; userCount: number }>;\n totalCheckins: number;\n averageCheckinsPerUser: number;\n usersWithCheckins: number;\n topUsersByCheckins: Array<\n { did: string; handle: string; checkinCount: number }\n >;\n}\n\nexport interface StatsResponse {\n success: boolean;\n data?: UserStats;\n error?: string;\n timestamp: string;\n}\n\n/**\n * Extract session data from the database and parse it into a UserSession object\n */\nfunction parseSessionData(\n sessionKey: string,\n sessionValue: string,\n): UserSession | null {\n try {\n const sessionData = JSON.parse(sessionValue);\n const did = sessionKey.replace(\"session:\", \"\");\n\n return {\n did,\n handle: sessionData.handle || \"unknown\",\n pdsUrl: sessionData.pdsUrl || \"unknown\",\n createdAt: sessionData.createdAt || Date.now(),\n expiresAt: sessionData.expiresAt,\n isExpired: sessionData.expiresAt\n ? new Date() > new Date(sessionData.expiresAt)\n : false,\n };\n } catch (error) {\n console.error(`Failed to parse session data for ${sessionKey}:`, error);\n return null;\n }\n}\n\n/**\n * Get checkin count for a specific user from their PDS\n */\nasync function getUserCheckinCount(\n did: string,\n pdsUrl: string,\n): Promise {\n try {\n let cursor: string | undefined;\n let totalCount = 0;\n\n do {\n const listRecordsUrl = cursor\n ? `${pdsUrl}/xrpc/com.atproto.repo.listRecords?repo=${did}&collection=app.dropanchor.checkin&limit=100&cursor=${cursor}`\n : `${pdsUrl}/xrpc/com.atproto.repo.listRecords?repo=${did}&collection=app.dropanchor.checkin&limit=100`;\n\n const response = await fetch(listRecordsUrl);\n\n if (!response.ok) {\n if (response.status === 404) {\n // User has no checkins or collection doesn't exist\n return 0;\n }\n throw new Error(`Failed to fetch checkins: ${response.status}`);\n }\n\n const data = await response.json();\n totalCount += data.records?.length || 0;\n cursor = data.cursor;\n\n // Prevent infinite loops\n if (totalCount > 10000) {\n console.warn(\n `User ${did} has more than 10,000 checkins, capping count`,\n );\n break;\n }\n } while (cursor);\n\n return totalCount;\n } catch (error) {\n console.error(`Failed to get checkin count for user ${did}:`, error);\n return 0;\n }\n}\n\n/**\n * Get all OAuth sessions from the database\n */\nasync function getAllSessions(): Promise {\n try {\n const sessions = await db.select().from(ironSessionStorageTable)\n .where(like(ironSessionStorageTable.key, \"session:%\"));\n\n const userSessions: UserSession[] = [];\n\n for (const session of sessions) {\n const parsed = parseSessionData(session.key, session.value);\n if (parsed) {\n userSessions.push(parsed);\n }\n }\n\n return userSessions;\n } catch (error) {\n console.error(\"Failed to get sessions:\", error);\n throw error;\n }\n}\n\n/**\n * Generate comprehensive user statistics from session data\n */\nexport async function generateUserStats(): Promise {\n const sessions = await getAllSessions();\n\n // Filter active vs expired sessions\n const activeSessions = sessions.filter((s) => !s.isExpired);\n const expiredSessions = sessions.filter((s) => s.isExpired);\n\n // Get unique users (by DID)\n const uniqueSessions = sessions.filter((session, index, self) =>\n index === self.findIndex((s) => s.did === session.did)\n );\n\n // Group users by PDS server\n const usersByPds: Record = {};\n sessions.forEach((session) => {\n const pds = session.pdsUrl;\n usersByPds[pds] = (usersByPds[pds] || 0) + 1;\n });\n\n // Get top PDS servers by user count\n const topPdsServers = Object.entries(usersByPds)\n .map(([pdsUrl, userCount]) => ({ pdsUrl, userCount }))\n .sort((a, b) => b.userCount - a.userCount)\n .slice(0, 10); // Top 10 servers\n\n // Get most recent users (last 10 sessions by creation time)\n const recentUsers = sessions\n .sort((a, b) => b.createdAt - a.createdAt)\n .slice(0, 10);\n\n // Get checkin counts for each unique user\n console.log(\n `📊 Fetching checkin counts for ${uniqueSessions.length} users...`,\n );\n const usersWithCheckins = [];\n let totalCheckins = 0;\n\n for (const user of uniqueSessions) {\n try {\n const checkinCount = await getUserCheckinCount(user.did, user.pdsUrl);\n user.checkinCount = checkinCount;\n totalCheckins += checkinCount;\n\n if (checkinCount > 0) {\n usersWithCheckins.push({\n did: user.did,\n handle: user.handle,\n checkinCount,\n });\n }\n } catch (error) {\n console.error(`Failed to get checkin count for ${user.did}:`, error);\n user.checkinCount = 0;\n }\n\n // Add small delay to avoid overwhelming PDS servers\n await new Promise((resolve) => setTimeout(resolve, 100));\n }\n\n // Sort users by checkin count for top users list\n const topUsersByCheckins = usersWithCheckins\n .sort((a, b) => b.checkinCount - a.checkinCount)\n .slice(0, 10);\n\n const averageCheckinsPerUser = uniqueSessions.length > 0\n ? Math.round((totalCheckins / uniqueSessions.length) * 100) / 100\n : 0;\n\n return {\n totalUsers: uniqueSessions.length,\n activeUsers: activeSessions.length,\n expiredUsers: expiredSessions.length,\n uniquePdsServers: Object.keys(usersByPds).length,\n usersByPds,\n recentUsers,\n topPdsServers,\n totalCheckins,\n averageCheckinsPerUser,\n usersWithCheckins: usersWithCheckins.length,\n topUsersByCheckins,\n };\n}\n\n/**\n * Get user statistics as a JSON response\n */\nexport async function getStatsResponse(): Promise {\n try {\n const stats = await generateUserStats();\n\n return {\n success: true,\n data: stats,\n timestamp: new Date().toISOString(),\n };\n } catch (error) {\n console.error(\"Failed to generate user stats:\", error);\n\n return {\n success: false,\n error: error.message,\n timestamp: new Date().toISOString(),\n };\n }\n}\n\n/**\n * Export user data for analysis (returns all unique users with their info)\n */\nexport async function exportUserData(): Promise {\n const sessions = await getAllSessions();\n\n // Return unique users only (by DID), with most recent session data\n const uniqueUsers = new Map();\n\n sessions.forEach((session) => {\n const existing = uniqueUsers.get(session.did);\n if (!existing || session.createdAt > existing.createdAt) {\n uniqueUsers.set(session.did, session);\n }\n });\n\n return Array.from(uniqueUsers.values());\n}\n","deleted","label",[170,171,152],"backend","services","permalink","https://www.val.town/x/tijs/location-feed-generator/code/backend/services/user-stats-service.ts","extension","actionData","errors"]