Something went wrong. Try again.
message board on atproto atchan.net
Something went wrong. Try again.
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283import { sql } from 'kysely'import type { DidString } from '@atproto/syntax'import { get_db } from './connection'
export interface AdminUser { did: DidString handle: string active: boolean firstSeenAt: string | undefined posts: number recent_posts: number lastPostAt: string | undefined}
export interface UserListing { users: AdminUser[] total: number}
interface Row { did: string handle: string | null active: number | null firstSeenAt: string | null posts: number recent_posts: number lastPostAt: string | null total: number}
export async function list_users( since: string, limit: number): Promise<UserListing> { const { rows } = await sql<Row>` with activity as ( select did, count(*) as posts, sum(case when at >= ${since} then 1 else 0 end) as recent_posts, max(at) as "lastPostAt" from ( select "authorDid" as did, "createdAt" as at from thread union all select "authorDid" as did, "createdAt" as at from reply ) group by did ) select u.did as did, a.handle as handle, a.active as active, a."firstSeenAt" as "firstSeenAt", coalesce(act.posts, 0) as posts, coalesce(act.recent_posts, 0) as recent_posts, act."lastPostAt" as "lastPostAt", count(*) over () as total from ( select did from account union select did from activity ) as u left join account a on a.did = u.did left join activity act on act.did = u.did order by recent_posts desc, posts desc, a."firstSeenAt" desc, u.did asc limit ${limit} `.execute(get_db())
return { users: rows.map((row) => ({ did: row.did as DidString, handle: row.handle ?? row.did, // a repo we only know from its posts has never told us otherwise active: row.active === null ? true : row.active === 1, firstSeenAt: row.firstSeenAt ?? undefined, posts: Number(row.posts), recent_posts: Number(row.recent_posts), lastPostAt: row.lastPostAt ?? undefined })), total: Number(rows[0]?.total ?? 0) }}