import { Injectable } from '@nestjs/common'; import { InjectRepository } from '@nestjs/typeorm'; import { In, Repository } from 'typeorm'; import { User } from './user.entity'; import { Campaign } from '../campaigns/entities/campaign.entity'; /** * How long since `lastActiveAt` before somebody counts as gone quiet. * * 30 days rather than the 3 the nightly scraper uses. That number answers "is * it worth spending a scrape on them tonight"; this one answers "have we lost * this person", and a fortnight's holiday is not churn. */ const ACTIVE_WINDOW_DAYS = 30; export type UserSort = 'newest' | 'oldest' | 'active' | 'inactive' | 'name'; export type ActivityFilter = 'all' | 'active' | 'inactive' | 'never'; export type SourceFilter = 'all' | 'campaign' | 'direct'; export interface AdminUserQuery { page?: number; limit?: number; search?: string; sort?: UserSort; activity?: ActivityFilter; source?: SourceFilter; verified?: 'all' | 'yes' | 'no'; availability?: 'all' | 'looking' | 'suspended'; role?: 'all' | 'admin' | 'candidate'; } @Injectable() export class UsersAdminService { constructor( @InjectRepository(User) private readonly users: Repository, @InjectRepository(Campaign) private readonly campaigns: Repository, ) {} private activeSince(): Date { return new Date(Date.now() - ACTIVE_WINDOW_DAYS * 24 * 60 * 60 * 1000); } /** * The headline numbers. * * Deliberately one round trip of counting rather than fetching rows and * counting them here: the page shows totals for a user base that is meant to * grow, and "how many users do I have" must not become a query that reads * every user to answer. */ async stats() { const since = this.activeSince(); const weekAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000); const monthAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000); const row = await this.users .createQueryBuilder('u') .select('COUNT(*)', 'total') .addSelect(`COUNT(*) FILTER (WHERE u."lastActiveAt" >= :since)`, 'active') .addSelect( `COUNT(*) FILTER (WHERE u."lastActiveAt" IS NOT NULL AND u."lastActiveAt" < :since)`, 'inactive', ) .addSelect(`COUNT(*) FILTER (WHERE u."lastActiveAt" IS NULL)`, 'never') .addSelect(`COUNT(*) FILTER (WHERE u."sourceCampaignId" IS NOT NULL)`, 'fromCampaign') .addSelect(`COUNT(*) FILTER (WHERE u."sourceCampaignId" IS NULL)`, 'direct') .addSelect(`COUNT(*) FILTER (WHERE u."isVerified")`, 'verified') .addSelect(`COUNT(*) FILTER (WHERE u.role = 'admin')`, 'admins') .addSelect(`COUNT(*) FILTER (WHERE u.availability = 'suspended')`, 'suspended') .addSelect(`COUNT(*) FILTER (WHERE u."createdAt" >= :weekAgo)`, 'newThisWeek') .addSelect(`COUNT(*) FILTER (WHERE u."createdAt" >= :monthAgo)`, 'newThisMonth') // Signup dates only exist from the day the column was added, so the page // can say how much of its own history it is missing rather than quietly // under-reporting growth. .addSelect(`COUNT(*) FILTER (WHERE u."createdAt" IS NULL)`, 'undated') .setParameters({ since, weekAgo, monthAgo }) .getRawOne>(); const n = (k: string) => Number(row?.[k] ?? 0); return { total: n('total'), active: n('active'), inactive: n('inactive'), never: n('never'), fromCampaign: n('fromCampaign'), direct: n('direct'), verified: n('verified'), admins: n('admins'), suspended: n('suspended'), newThisWeek: n('newThisWeek'), newThisMonth: n('newThisMonth'), undated: n('undated'), activeWindowDays: ACTIVE_WINDOW_DAYS, }; } /** * Recover signup dates for accounts created before the column existed. * * The same job as scripts/backfill-user-created-at.mjs, exposed as an action * because the script cannot practically be run where it is needed: the * production database is Neon, reached through credentials held as Space * secrets, so running the script means moving those credentials onto * somebody's laptop. The server already holds the connection. It should do * the work. * * Every table below carries a timestamp and belongs to exactly one user, and * none of those things can have happened before the account did — so the * earliest of them is an upper bound on the signup date, and in practice * within minutes of it. LEAST ignores NULLs, so an account is dated by * whichever evidence exists. * * Only ever fills rows that are still NULL: it never overwrites a real * signup date, and running it twice changes nothing the second time. * Accounts with no trace at all keep NULL rather than being given a date we * would have invented. */ async backfillSignupDates(): Promise<{ filled: number; remaining: number; total: number }> { const undated = async () => Number( ( await this.users .createQueryBuilder('u') .select('COUNT(*)', 'n') .where('u."createdAt" IS NULL') .getRawOne<{ n: string }>() )?.n ?? 0, ); const before = await undated(); await this.users.query(` WITH evidence AS ( SELECT u.id, LEAST( (SELECT min(p."createdAt") FROM user_profiles p WHERE p."userId" = u.id), (SELECT min(c."createdAt") FROM campaign_candidates c WHERE c."userId" = u.id), (SELECT min(a."createdAt") FROM job_applications a WHERE a."userId" = u.id), (SELECT min(r."uploadedAt") FROM resumes r WHERE r."userId" = u.id), u."lastActiveAt" ) AS earliest FROM "user" u WHERE u."createdAt" IS NULL ) UPDATE "user" u SET "createdAt" = e.earliest FROM evidence e WHERE u.id = e.id AND e.earliest IS NOT NULL `); const after = await undated(); const total = await this.users.count(); return { filled: before - after, remaining: after, total }; } async list(query: AdminUserQuery) { const page = Math.max(1, Number(query.page) || 1); const limit = Math.min(100, Math.max(1, Number(query.limit) || 25)); const since = this.activeSince(); /* * No join to campaigns here, deliberately. * * It was `leftJoin('campaigns', 'c', ...)` plus `addSelect('c.title')` — * a RAW table name, so the alias carried no entity metadata. That is * survivable until pagination: `skip`/`take` makes TypeORM build a * DISTINCT id subquery, which needs metadata for every selected column, * and it threw "Cannot read properties of undefined (reading * 'databaseName')". The count ran first and succeeded, so the page showed * a correct total above a table that could never load a row. * * The titles are a second, tiny lookup instead — one query for the handful * of campaigns on the current page. */ const qb = this.users .createQueryBuilder('u') // Named columns only. The old /users routes returned the whole entity, // password and token hashes included, and this list is not going to // reintroduce that. .select([ 'u.id', 'u.name', 'u.email', 'u.role', 'u.isVerified', 'u.availability', 'u.suspensionReason', 'u.lastActiveAt', 'u.createdAt', 'u.sourceCampaignId', 'u.googleId', ]); if (query.search?.trim()) { const term = `%${query.search.trim().toLowerCase()}%`; qb.andWhere('(LOWER(u.name) LIKE :term OR LOWER(u.email) LIKE :term)', { term }); } switch (query.activity) { case 'active': qb.andWhere('u."lastActiveAt" >= :since', { since }); break; case 'inactive': qb.andWhere('u."lastActiveAt" IS NOT NULL AND u."lastActiveAt" < :since', { since }); break; case 'never': qb.andWhere('u."lastActiveAt" IS NULL'); break; } // Where they came from: an account created by claiming a campaign // application carries the campaign that brought them in; a direct signup // has nothing there. if (query.source === 'campaign') qb.andWhere('u."sourceCampaignId" IS NOT NULL'); if (query.source === 'direct') qb.andWhere('u."sourceCampaignId" IS NULL'); if (query.verified === 'yes') qb.andWhere('u."isVerified" = true'); if (query.verified === 'no') qb.andWhere('u."isVerified" = false'); if (query.availability && query.availability !== 'all') { qb.andWhere('u.availability = :availability', { availability: query.availability }); } if (query.role && query.role !== 'all') { qb.andWhere('u.role = :role', { role: query.role }); } /* * NULLS LAST on every date sort. * * Accounts predating the createdAt column have no signup date, and Postgres * sorts NULLs first on DESC by default - so "newest first" would have * opened with the oldest accounts on the platform, which is precisely * backwards. */ switch (query.sort) { case 'oldest': qb.orderBy('u."createdAt"', 'ASC', 'NULLS LAST'); break; case 'active': qb.orderBy('u."lastActiveAt"', 'DESC', 'NULLS LAST'); break; case 'inactive': qb.orderBy('u."lastActiveAt"', 'ASC', 'NULLS LAST'); break; case 'name': qb.orderBy('LOWER(u.name)', 'ASC'); break; default: qb.orderBy('u."createdAt"', 'DESC', 'NULLS LAST'); } // A stable tiebreak, so paging cannot show the same row twice. qb.addOrderBy('u.id', 'ASC'); const [rows, total] = await qb .skip((page - 1) * limit) .take(limit) .getManyAndCount(); // Only the campaigns actually on this page. const campaignIds = [...new Set(rows.map((u) => u.sourceCampaignId).filter(Boolean))] as string[]; const titles = new Map(); if (campaignIds.length) { const found = await this.campaigns.find({ where: { id: In(campaignIds) }, select: { id: true, title: true }, }); for (const c of found) titles.set(c.id, c.title); } return { users: rows.map((u) => ({ id: u.id, name: u.name, email: u.email, role: u.role, isVerified: u.isVerified, availability: u.availability, suspensionReason: u.suspensionReason, lastActiveAt: u.lastActiveAt, createdAt: u.createdAt, signedUpWith: u.googleId ? 'google' : 'password', source: u.sourceCampaignId ? 'campaign' : 'direct', sourceCampaignTitle: u.sourceCampaignId ? (titles.get(u.sourceCampaignId) ?? null) : null, })), total, page, limit, totalPages: Math.max(1, Math.ceil(total / limit)), activeWindowDays: ACTIVE_WINDOW_DAYS, }; } }