[{"_1":2,"_178":-5,"_179":-5},"loaderData",{"_3":4,"_92":93},"root",{"_5":6,"_35":36,"_65":66},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20,"_34":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","isTeamOrgMember","ENV",{"_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":64},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","89c4055f4fdfd53203ab5dac178878366c306b55","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",{"_67":68},"__internal_clerk_state",{"_69":70,"_71":46,"_72":73,"_74":73,"_75":20,"_76":73,"_77":73,"_78":73,"_79":73,"_80":81,"_90":20,"_91":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",{"_82":20,"_19":20,"_83":73,"_84":85,"_86":73,"_87":46,"_88":20,"_89":73},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_94":95,"_96":97,"_98":99,"_100":101,"_153":154,"_175":176,"_177":-5},"handle","tijs","name","anchor-appview","path","backend/services/user-stats-service.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":111,"_112":113,"_12":114,"_115":116,"_117":118,"_119":118,"_120":-5,"_121":122,"_14":123,"_124":125,"_126":125,"_127":128,"_129":128,"_130":128,"_131":125,"_132":125,"_23":133,"_136":137,"_139":140,"_152":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","db7d92c8-58ab-11f0-bdec-f69ea79377d9","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",1,"c04d51b2-a7dc-46ca-ad6f-52f47c2906a8","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_134":135},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yd2Nod2N0bE1MWFdEUlRYd083ZE5pbXJ1bDAifQ","org",{"_94":95,"_138":135,"_12":114},"avatar","branch",{"_141":142,"_96":44,"_102":143,"_144":103,"_145":-5,"_146":-5,"_147":-5,"_148":-5,"_149":-5,"_150":97,"_151":95},"version",1662,"db7c07aa-58ab-11f0-bdec-f69ea79377d9","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":155,"_96":156,"_157":158,"_159":160,"_161":143,"_162":163,"_164":118,"_104":165,"_141":122,"_166":167,"_168":169,"_170":20,"_171":-5,"_98":172},"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",["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",[173,174,156],"backend","services","permalink","https://www.val.town/x/tijs/anchor-appview/code/backend/services/user-stats-service.ts","extension","actionData","errors"]