Spaces:
Sleeping
Sleeping
Download src/modules/users/users-admin.service.ts from mahrooshishaq/SmartfolioBackend: direct link, hf CLI and curl.
- Browser
- Download file 10.9 kB
-
https://huggingface.co/spaces/mahrooshishaq/SmartfolioBackend/resolve/main/src/modules/users/users-admin.service.ts
- Command line
-
hf download hf://spaces/mahrooshishaq/SmartfolioBackend/src/modules/users/users-admin.service.ts
-
curl -L -o users-admin.service.ts https://huggingface.co/spaces/mahrooshishaq/SmartfolioBackend/resolve/main/src/modules/users/users-admin.service.ts
10.9 kB
| 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'; | |
| } | |
| () | |
| export class UsersAdminService { | |
| constructor( | |
| (User) private readonly users: Repository<User>, | |
| (Campaign) private readonly campaigns: Repository<Campaign>, | |
| ) {} | |
| 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<Record<string, string>>(); | |
| 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<string, string>(); | |
| 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, | |
| }; | |
| } | |
| } | |