konnosDashboard
signup-geo-admin.ts122 lines · main
1import { and, desc, eq, gte, sql } from 'drizzle-orm';
2
3import { getDb } from '../db/client.js';
4import { authSignupGeo } from '../db/schema.js';
5
6/**
7 * Admin-only aggregation over auth_signup_geo (platform-wide sign-up geo).
8 * Powers the "sign-ups · geo (SEO)" cockpit page. Returns:
9 * - total sign-ups in the window
10 * - counts grouped by country
11 * - counts grouped by country + city
12 * - a recent-events list (raw IPs included — admin-only, the whole point)
13 *
14 * Optional filters mirror other admin analytics endpoints: `?projectId=` and
15 * a `?days=` time window (default 30, clamped 1..365).
16 */
17
18export interface CountryCount {
19 country: string | null;
20 count: number;
21}
22
23export interface CityCount {
24 country: string | null;
25 city: string | null;
26 count: number;
27}
28
29export interface RecentSignup {
30 id: string;
31 projectId: string;
32 userId: string | null;
33 email: string | null;
34 ip: string | null;
35 country: string | null;
36 city: string | null;
37 region: string | null;
38 createdAt: string;
39}
40
41export interface SignupGeoSummary {
42 total: number;
43 byCountry: CountryCount[];
44 byCity: CityCount[];
45 recent: RecentSignup[];
46 sinceDays: number;
47 projectId: string | null;
48 /** True when every geo field on every row in the window is null — i.e. the
49 * GeoLite2 .mmdb isn't installed yet. Lets the UI show a "geo pending" note
50 * instead of reading as "everyone is from nowhere". */
51 geoPending: boolean;
52}
53
54export async function getSignupGeoSummary(opts: {
55 sinceDays?: number;
56 projectId?: string | null;
57} = {}): Promise<SignupGeoSummary> {
58 const db = getDb();
59 const sinceDays = Math.max(1, Math.min(365, opts.sinceDays ?? 30));
60 const since = new Date(Date.now() - sinceDays * 24 * 60 * 60 * 1000);
61 const projectId = opts.projectId?.trim() || null;
62
63 const where = projectId
64 ? and(gte(authSignupGeo.createdAt, since), eq(authSignupGeo.projectId, projectId))
65 : gte(authSignupGeo.createdAt, since);
66
67 const [byCountryRows, byCityRows, recentRows] = await Promise.all([
68 db
69 .select({
70 country: authSignupGeo.country,
71 count: sql<number>`count(*)`,
72 })
73 .from(authSignupGeo)
74 .where(where)
75 .groupBy(authSignupGeo.country)
76 .orderBy(desc(sql`count(*)`)),
77 db
78 .select({
79 country: authSignupGeo.country,
80 city: authSignupGeo.city,
81 count: sql<number>`count(*)`,
82 })
83 .from(authSignupGeo)
84 .where(where)
85 .groupBy(authSignupGeo.country, authSignupGeo.city)
86 .orderBy(desc(sql`count(*)`)),
87 db
88 .select()
89 .from(authSignupGeo)
90 .where(where)
91 .orderBy(desc(authSignupGeo.createdAt))
92 .limit(100),
93 ]);
94
95 const byCountry: CountryCount[] = byCountryRows.map((r) => ({
96 country: r.country,
97 count: Number(r.count),
98 }));
99 const byCity: CityCount[] = byCityRows.map((r) => ({
100 country: r.country,
101 city: r.city,
102 count: Number(r.count),
103 }));
104 const total = byCountry.reduce((sum, r) => sum + r.count, 0);
105
106 const recent: RecentSignup[] = recentRows.map((r) => ({
107 id: r.id,
108 projectId: r.projectId,
109 userId: r.userId,
110 email: r.email,
111 ip: r.ip,
112 country: r.country,
113 city: r.city,
114 region: r.region,
115 createdAt: r.createdAt.toISOString(),
116 }));
117
118 const geoPending =
119 recent.length > 0 && recent.every((r) => !r.country && !r.city && !r.region);
120
121 return { total, byCountry, byCity, recent, sinceDays, projectId, geoPending };
122}