SmartfolioBackend / src /modules /users /users-admin.service.ts
ibrahimgulbutt's picture
deploy: recover signup dates from the admin page
d5ba26c
Raw History Blame Contribute Delete
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';
}
@Injectable()
export class UsersAdminService {
constructor(
@InjectRepository(User) private readonly users: Repository<User>,
@InjectRepository(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,
};
}
}