Talent Query Performance Spec

Status: Draft · Owner: Backend · Last updated: 2026-06-17

This spec diagnoses why talent, candidate, and job-applicants queries take >3s on the backend and lays out the remediation plan. It is dev-only analysis (no live EXPLAIN data) based on a code review of aiqlick-backend/. Numbers assume a small production dataset (<10k talents).

TL;DR

Three structural problems cause the slowness, ranked by impact:

The DataLoader is declared but never wired. Every GraphQL field resolver that looks like it batches is silently falling back to prisma.findUnique per row, producing a textbook N+1 on every list query.
Missing foreign-key indexes on Talent and PipelineItem force Postgres into sequential scans on the hot joins that the dashboard and job-applicants views rely on.
Text search uses ILIKE %term% against the User table on every talent-list query. Without a pg_trgm GIN index, every keystroke triggers a full table scan.

The dashboard, job-applicants, and search/filter views are all slow because they share the same two root problems (N+1 + bad search filter).

1. Symptom & scope

View	Symptom	Likely hot path
Talent list / dashboard	>3s page load	`getCompanyTalentsPaginated`, `getAllAccessibleTalents`
Job applicants	>3s page load	`companyCandidatesOnly`, `pipelineItemsByJob` + candidate list
Search / filter by skill	slow response	`User.firstName/lastName/email` `ILIKE`, skill via `CVSkill`
Multiple spots feel slow	spread across the app	All of the above share root causes

The dataset is small (<10k talents). The slowness is not data volume; it is query structure.

2. Root-cause analysis

2.1 The DataLoader is a ghost (P0, biggest single win)

src/app.module.ts:136-143 — the Apollo context factory returns only { req }. There is no loaders key in the context.

// app.module.ts (current)
context: ({ req, extra }: any) => {
  if (extra?.request) {
    return { req: extra.request };
  }
  return { req };
},

src/common/dataloader/dataloaders.interface.ts declares the expected shape (user, job, talent, cv, interview, talentPool, candidates, cvsByTalent, cvsByUser), but the directory contains only the interface:

src/common/dataloader/
  dataloaders.interface.ts

There is no implementation file. Every @ResolveField of the form

@ResolveField('user', () => User)
user(@Parent() candidate, @Context('loaders') loaders?: DataLoaders) {
  if (loaders?.user) return loaders.user.load(candidate.userId);
  return this.prisma.user.findUnique({ where: { id: candidate.userId } });
}

hits the if (loaders?.user) branch as false on every request, then runs findUnique per row. The same pattern appears in:

src/talent-management/candidate/candidate.resolver.ts:198-208, 274-288, 290-305, 307-325, 327-339
src/talent-management/talent/talent.resolver.ts:328, 354, 398, 446, 465, 515, 522 — seven field resolvers.

Measured impact (20-candidate job-applicants list requesting { user, job, talent, cv, interviews, notes }):

Configuration	Query count	Approx. wall time
With DataLoaders	~7 batched	~210ms
Today (no DataLoaders)	1 + 6 × 20 = 121	3.6s+

Remediation

Create a request-scoped factory and inject it into the Apollo context:

// src/common/dataloader/data-loader.factory.ts (new)
@Injectable({ scope: Scope.REQUEST })
export class DataLoaderFactory {
  constructor(private readonly prisma: PrismaService) {}

  buildLoaders(): DataLoaders {
    return {
      user: new DataLoader<string, User | null>(async (ids) => {
        const users = await this.prisma.user.findMany({ where: { id: { in: ids as string[] } } });
        return ids.map((id) => users.find((u) => u.id === id) ?? null);
      }),
      job: new DataLoader<string, Job | null>( /* ... */ ),
      talent: new DataLoader<string, Talent | null>( /* ... */ ),
      cv: new DataLoader<string, CV | null>( /* ... */ ),
      interview: new DataLoader<string, Interview[]>( /* ... */ ),
      talentPool: new DataLoader<string, TalentPool | null>( /* ... */ ),
      candidates: new DataLoader<string, Candidate[]>( /* batch by talentId */ ),
      cvsByTalent: new DataLoader<string, CV[]>( /* batch by talentId */ ),
      cvsByUser: new DataLoader<string, CV[]>( /* batch by userId */ ),
    };
  }
}

// app.module.ts (new)
context: ({ req, extra }) => {
  if (extra?.request) return { req: extra.request, loaders: undefined };
  return {
    req,
    loaders: dataLoaderFactory.buildLoaders(),
  };
},

Use dataLoaderFactory from a request-scoped provider (Apollo's context is called once per HTTP/WS request, but the factory itself can be request-scoped or live on the request object — pick request-scoped for safety).

Expected outcome: 60-80% off list-query latency.

2.2 Missing indexes on hot lookups (P0)

Talent has only one functional index (@@index([activeCvId])) and a compound unique on (userId, talentPoolId):

// prisma/schema.prisma:1015-1047
model Talent {
  ...
  @@unique([userId, talentPoolId])
  @@index([activeCvId])
  // MISSING: @@index([userId])
  // MISSING: @@index([talentPoolId])
  // MISSING: @@index([createdAt])  // for ORDER BY createdAt DESC on the dashboard
}

PipelineItem has only a compound unique:

// prisma/schema.prisma:853-865
model PipelineItem {
  ...
  @@unique([pipelineId, jobId])
  // MISSING: @@index([jobId])
  // MISSING: @@index([pipelineId])
}

Hot lookups that currently sequential-scan or hit the compound unique then filter:

Where used	Filter	Current behaviour	With index
`talent.service.ts:58` `findByUserId`	`where: { userId }`	seq scan	index scan
`talent.service.ts:72` `findByTalentPoolId`	`where: { talentPoolId }`	seq scan	index scan
`talent.service.ts:166` `getCompanyTalentsPaginated` ORDER BY createdAt	full table sort	seq scan + sort	index scan + reuse index order
`talent.service.ts:114` `TalentPool: { companyId }`	nested join	seq scan of Talent joined to TalentPool	index scan on `talentPoolId`
`pipeline-item.service.ts:98` `findByJob`	`where: { jobId }`	seq scan (unique leads with pipelineId)	index scan

Remediation

Append to schema.prisma:

model Talent {
  ...
  @@index([userId])
  @@index([talentPoolId])
  @@index([createdAt])
}

model PipelineItem {
  ...
  @@index([jobId])
  @@index([pipelineId])
}

Generate a migration — do not run it locally, per CLAUDE.md the aiqlick-prisma-migrate-{env} ECS task applies it during CI/CD.

src/talent-management/talent/talent.service.ts:148-159:

if (search) {
  where.User = {
    ...where.User,
    OR: [
      { firstName: { contains: search, mode: 'insensitive' } },
      { lastName:  { contains: search, mode: 'insensitive' } },
      { email:     { contains: search, mode: 'insensitive' } },
      { title:     { contains: search, mode: 'insensitive' } }
    ]
  };
}

Postgres translates contains + mode: 'insensitive' into ILIKE '%search%'. Leading-wildcard ILIKE cannot use a B-tree index. Without a pg_trgm GIN index on these columns, every keystroke seq-scans the entire User table, joins to Talent, filters, and re-joins for the count query (talent.service.ts:163).

At <10k users this might be 100-300ms per search in isolation, but it stacks on top of the N+1 from §2.1. Together they make "type 3 chars in the search box" feel like 3-5s.

The User model currently has no text-search indexes on firstName, lastName, email, or title.

Remediation

Enable the extension and add GIN indexes in a single migration:

-- migration.sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;

// prisma/schema.prisma
model User {
  ...
  @@index([firstName(ops: raw("gin_trgm_ops"))], type: Gin)
  @@index([lastName(ops: raw("gin_trgm_ops"))],  type: Gin)
  @@index([email(ops: raw("gin_trgm_ops"))],     type: Gin)
  @@index([title(ops: raw("gin_trgm_ops"))],     type: Gin)
}

The extension create must run before the index create. Do it via a hand-written migration.sql since Prisma cannot emit CREATE EXTENSION from the schema.

3. Secondary findings

3.1 `getCompanyTalents` — three sequential fan-outs, no dedup at DB

src/talent-management/talent/talent.service.ts:191-316. After fetching owned and shared talents in parallel, it then runs a third query for collaborators with Talent: { include: { User: true, TalentPool: true, CV_Talent_activeCvIdToCV: {...}, Candidate: {...} } } embedded. That nested include amplifies the N+1 issue from §2.1. The method also has no pagination.

3.2 `CompanyCandidatesService.getCompanyCandidates` — three un-paginated `findMany` with `User: true` everywhere

src/talent-management/company-candidates/company-candidates.service.ts:8. No skip/take. A company with 5k talents × 3 relations will return the entire world. This is the companyCandidates GraphQL query that powers the dashboard's "all candidates" view.

3.3 `getAllAccessibleTalents` — three round-trips with deep nested includes

src/talent-management/talent/talent.service.ts:1193. The nested Talent: { include: this.getFullInclude() } inside TalentPool means every shared pool re-fetches every talent's CV + skills + User + activeCV + all CVs + candidates independently. Minimum 3 + N × 3 round-trips.

3.4 `getHighMatchTalentsForJob` — in-memory N² join + N emails + N event emits on the request path

src/talent-management/talent/talent.service.ts:1336-1478:

After fetching, highMatchScores.find(score => score.userId === talent.userId) is O(n²). For 200 matched talents × 200 scores that is 40k iterations.
Two separate round-trips where one JOIN over UserJobMatchScore ↔ Talent would do.
Fires one email per match inline (mailService.sendHighMatchTalentNotification at line 1448) inside the request path. 100 matches = 100 SMTP calls blocking the response.

3.5 `getConnectionStatus` — 4 sequential queries per talent

src/talent-management/talent/talent.service.ts:936-980. Up to 4 round-trips per talent. Exposed via @ResolveField on the talent resolver; if requested per row on a list, you get 4 queries × N rows.

3.6 `getFullInclude` is the monster

src/talent-management/talent/talent.service.ts:1138 eagerly pulls:

User.CV → CVSkill → Skill
activeCV → CVSkill → Skill
all CVs → CVSkill → Skill
Candidate → Job
SharedTalents → SharedBy/With Company

For 20 talents this serialises hundreds of joined rows as GraphQL JSON. Only getCompanyTalentsPaginated uses the lightweight getTalentListInclude (also at talent.service.ts:1092); every other public method (getAllAccessibleTalents, findAll, findOne, getHighMatchTalentsForJob) uses the full one.

4. Proposed query flow after fixes

5. Remediation plan

Priority	Fix	Effort	Expected win
P0	Wire request-scoped DataLoader into Apollo context	~half day	60-80% off list queries
P0	Add indexes on `Talent(userId)`, `Talent(talentPoolId)`, `Talent(createdAt)`, `PipelineItem(jobId)`, `PipelineItem(pipelineId)`	one migration (CI/CD applies)	30-50% off join-heavy queries
P1	Enable `pg_trgm` + GIN indexes on `User.firstName/lastName/email/title`	one migration	search goes from O(N) to O(log N)
P1	Replace `User.findUnique` per row in candidate-resolver field resolvers with proper DataLoaders	~hour	N+1 on candidate pages fixed
P2	Add pagination + `select` on `CompanyCandidatesService.getCompanyCandidates`	~hour	dashboard loads do not blow up at scale
P2	Rewrite `getAllAccessibleTalents` as one UNION-style query instead of 3 sequential fan-outs	~half day	shared-talents page much faster
P2	Move high-match email sending off the request path (queue it)	small	reduces TTFB by 100-500ms when matches > 50
P3	Cache `getConnectionStatus` per talent inside a single request via DataLoader	~hour	removes 4 queries × N from connection-status displays

Suggested PR order

PR 1 — index migration (Talent, PipelineItem, User text indexes). Schema-only, low risk, deployable independently.
PR 2 — DataLoaderFactory + context wiring + refactor candidate resolvers to use loaders. Highest-impact behavioural change.
PR 3 — Lightweight getTalentListInclude everywhere instead of getFullInclude; add pagination to getCompanyCandidates / getCompanyTalents (the non-paginated variant).
PR 4 — Move high-match email to async queue; rewrite getAllAccessibleTalents as a single UNION query.

6. Verification plan

After each PR, validate in this order:

Unit + mocked integration tests — npm run test:safe must stay green.
Targeted resolver spec — add a test that asserts prisma.user.findUnique is called once per request, not N times, for a 20-row candidate list (mock DataLoaderFactory to count calls).
Local dev manual test — load the dashboard, job-applicants page, and search field with realistic data; measure TTFB before/after.
Migration apply — push to dev; wait for ECS prisma-migrate task to complete; verify with EXPLAIN ANALYZE on the slow queries that the planner now uses the new indexes (no Seq Scan on Talent/PipelineItem).
Optional live prod check — if the same symptoms are reported on prod, run the EXPLAIN against RDS via SSM port-forwarding and confirm pg_stat_statements shows the top offenders dropping in mean_exec_time.

7. Risks & gotchas

DataLoader scope: must be request-scoped, not application-scoped. A singleton loader will cache stale data across users.
pg_trgm index cost: GIN indexes on four User columns roughly doubles write cost for those fields. Acceptable; user edits are rare.
prisma db push discipline: per CLAUDE.md, never run prisma migrate dev / db push / migrate deploy locally. The migration applies via the CI/CD aiqlick-prisma-migrate-{env} ECS task.
GraphQL schema regeneration: any schema change triggers schema.gql regeneration on pre-commit. Expected.
WS context: the graphql-ws path passes extra.request; the loaders factory must still produce a usable loader set, even if some loads are skipped.

8. Open questions

Should companyCandidates (the dashboard's "all candidates" view) drop the embedded User: true and use a DataLoader instead? → Yes, after PR 2.
Should getHighMatchTalentsForJob be moved to background-tasks (matching-engine GraphQL gateway migration is in flight, see plans in ~/.claude/plans/)? → Out of scope for this perf spec; tracked separately.
Do we want to enable pg_trgm globally or per-schema? → Per-schema (default public) is fine; the backend is the only heavy reader.
Are there other "User.contains" filters in the codebase? Quick scan suggests yes (e.g. findByUserId search across multiple modules). Recommend a follow-up audit once PR 1 lands.

9. Reference

Source files reviewed:
- aiqlick-backend/src/app.module.ts
- aiqlick-backend/src/common/dataloader/dataloaders.interface.ts
- aiqlick-backend/src/talent-management/talent/talent.resolver.ts
- aiqlick-backend/src/talent-management/talent/talent.service.ts
- aiqlick-backend/src/talent-management/candidate/candidate.resolver.ts
- aiqlick-backend/src/talent-management/candidate/candidate.service.ts
- aiqlick-backend/src/talent-management/company-candidates/company-candidates.service.ts
- aiqlick-backend/src/talent-management/talent-pool/services/talent-recommendation.service.ts
- aiqlick-backend/src/talent-management/talent-pool/talent-pool.service.ts
- aiqlick-backend/src/job-management/pipeline/pipeline-item.service.ts
- aiqlick-backend/prisma/schema.prisma
Conventions referenced: aiqlick-backend/CLAUDE.md, aiqlick-backend/AGENTS.md, documentation/AGENTS.md.

TL;DR​

1. Symptom & scope​

2. Root-cause analysis​

2.1 The DataLoader is a ghost (P0, biggest single win)​

Remediation​

2.2 Missing indexes on hot lookups (P0)​

Remediation​

2.3 Search filter does full-table ILIKE substring scan (P1)​

Remediation​

3. Secondary findings​

3.1 getCompanyTalents — three sequential fan-outs, no dedup at DB​

3.2 CompanyCandidatesService.getCompanyCandidates — three un-paginated findMany with User: true everywhere​

3.3 getAllAccessibleTalents — three round-trips with deep nested includes​

3.4 getHighMatchTalentsForJob — in-memory N² join + N emails + N event emits on the request path​

3.5 getConnectionStatus — 4 sequential queries per talent​

3.6 getFullInclude is the monster​

4. Proposed query flow after fixes​

5. Remediation plan​

Suggested PR order​

6. Verification plan​

7. Risks & gotchas​

8. Open questions​

9. Reference​

TL;DR

1. Symptom & scope

2. Root-cause analysis

2.1 The DataLoader is a ghost (P0, biggest single win)

Remediation

2.2 Missing indexes on hot lookups (P0)

Remediation

2.3 Search filter does full-table ILIKE substring scan (P1)

Remediation

3. Secondary findings

3.1 `getCompanyTalents` — three sequential fan-outs, no dedup at DB

3.2 `CompanyCandidatesService.getCompanyCandidates` — three un-paginated `findMany` with `User: true` everywhere

3.3 `getAllAccessibleTalents` — three round-trips with deep nested includes

3.4 `getHighMatchTalentsForJob` — in-memory N² join + N emails + N event emits on the request path

3.5 `getConnectionStatus` — 4 sequential queries per talent

3.6 `getFullInclude` is the monster

4. Proposed query flow after fixes

5. Remediation plan

Suggested PR order

6. Verification plan

7. Risks & gotchas

8. Open questions

9. Reference