CasTyou Platform Performance Audit — PERF-AUDIT-001

Date: 2026-06-15 Scope: castyou-backend (Apollo 4 / Prisma 7 + Postgres / Mongoose 8 + MongoDB / Redis), castyou-frontend (apps/app React 18 + Vite + React Query) Method: Static code analysis against the real code. No production profiling was run; estimates are derived from query shapes, result-set sizes, and bundle import graphs and are labelled as estimates. Status: Audit only. Every finding maps to a remediation scope (BE-PERF-001 backend / FE-PERF-001 frontend). Do NOT fix here.

A permanent instrumentation artifact was created to validate these findings in a running environment: castyou-backend/src/plugins/queryTiming.ts (see Instrumentation at the end).

---

P0 — Blocker

PERF-F01 — Job table has zero indexes; every jobs/discover query is a seq scan + sort

Severity: P0 · Scope: BE-PERF-001

Evidence: castyou-backend/prisma/schema.prisma:406-471 — the Job model ends at @@map("jobs") (line 470) with no @@index directives at all. Meanwhile the hottest read paths filter and order on unindexed columns:

• jobs query: castyou-backend/src/graphql/resolvers/job.ts:129,146-155 — where { status: 'OPEN', ... } + orderBy: { createdAt: 'desc' }.
• myJobs: job.ts:181,183-192 — where { producerId, status } + orderBy createdAt desc.
• discoverJobs / jobsForTalent: job.ts:454-462,556-561 — where { status: 'OPEN', id: { notIn: [...] } }, pulls a 500-row pool ordered by createdAt desc.

Measurement / estimate: On Postgres, status='OPEN' ORDER BY createdAt DESC LIMIT 20 with no index is a full sequential scan of jobs plus an in-memory sort. At 10k jobs that is ~10k row reads per request where an index would touch ~20. Estimated p95 latency for the jobs list grows roughly linearly with table size — sub-ms with an index vs. tens-to-hundreds of ms as the table grows past a few thousand rows. The notIn pool query in discover is the worst case (scan + sort + filter of up to the whole table).

Remediation hint: add @@index([status, createdAt]), @@index([producerId, createdAt]) to Job. Requires a prisma migrate (per project memory — prisma generate alone is not enough).

---

PERF-F02 — Feed Post field resolvers are 1 + 4N queries per page (counts + likedByMe + latestComments)

Severity: P0 · Scope: BE-PERF-001

Evidence: The feed query requests likeCount, commentCount, likedByMe, and latestComments for every post — confirmed in the client query shape castyou-frontend/apps/app/src/hooks/useFeed.ts:28-36,81-84. Each of those is an independent, un-batched per-post resolver:

• castyou-backend/src/graphql/resolvers/feed.ts:33-34 likeCount → prisma.postLike.count per post
• feed.ts:36-37 commentCount → prisma.postComment.count per post
• feed.ts:39-40 shareCount → prisma.postShare.count per post
• feed.ts:47-59 latestComments → prisma.postComment.findMany per post
• feed.ts:61-67 likedByMe → prisma.postLike.findUnique per post
• feed.ts:71-89 mine → up to 2 profile lookups per post (used on profile/detail surfaces)

Measurement: page size is 10 (useFeed.ts:49). The feed resolver itself issues 2 queries (findMany + count, feed.ts:171-180). Then per the 4 fields the client selects: 10 × 4 = 40 additional round-trips. Total ≈ 1 + 42 queries for a 10-post feed page. Infinite scroll multiplies this per page. The forYouFeed path already proves the fix is cheap: it fetches counts via _count in a single query (feed.ts:244-248) — the field resolvers do not reuse that.

Remediation hint: batch counts with a groupBy-backed DataLoader (mirror applicationCountByJobId in dataloaders.ts:49-57), batch likedByMe with a single postLike.findMany({ where: { userId, postId: { in } } }) loader, and resolve latestComments from one windowed query. Add @@index([postId, read?])-style coverage is already present for comments (schema.prisma:1129 @@index([postId, createdAt])) and shares (:1140), so only PostLike count-by-post would benefit from confirming @@index([postId]) exists (currently only @@unique([postId, userId]) + @@index([userId]) at :1113-1114 — the unique covers postId-prefixed lookups, so counts are OK; the cost here is round-trip count, not missing indexes).

---

P1 — Real user impact, fix this epic

PERF-F03 — Talent jobs grid: myApplication is an N+1 (2 queries per job card)

Severity: P1 · Scope: BE-PERF-001

Evidence: castyou-backend/src/graphql/resolvers/job.ts:72-79 — Job.myApplication resolves talentProfile.findUnique({ where: { userId } }) then application.findUnique(...) for every job in the list. The talent grid (jobsForTalent) returns up to first cards and the client type carries myApplication (castyou-frontend/apps/app/src/hooks/useJobs.ts:97).

Measurement: for a 10-job grid that is 10 × 2 = 20 queries, and the talent-profile lookup is identical on every call (same ctx.user.sub) so half of them are pure waste. Estimate ~20 redundant round-trips per jobs-grid render.

Remediation hint: resolve the talent profile once per request (cache on ctx or a loader), and batch the applications with a application.findMany({ where: { talentProfileId, jobId: { in } } }) DataLoader keyed by jobId.

---

PERF-F04 — PetJob.applicationCount is an un-batched count() per row

Severity: P1 · Scope: BE-PERF-001

Evidence: castyou-backend/src/graphql/resolvers/petJob.ts:33-36 — applicationCount runs petJobApplication.count({ where: { petJobId } }) per pet job. The code comment itself flags it: "kept simple (count() per call). Add a DataLoader if list views start showing N+1 latency." The producer jobs table renders this column per row (castyou-frontend/apps/app/src/pages/jobs/ProducerJobsPage.tsx:242,279). Contrast with Job.applicationCount which does use a loader (job.ts:68-69 → applicationCountByJobId).

Measurement: N pet jobs in a table → N extra count queries (e.g. 20-row table = 20 queries). The aggregate PetJobApplication table also has no @@index([petJobId]) (only @@unique([petJobId, petId]) at schema.prisma:630, which prefixes on petJobId so the count is index-served — the cost is round-trip count, not scan).

Remediation hint: add a petJobApplicationCountByPetJobId DataLoader in dataloaders.ts mirroring the existing job one.

---

PERF-F05 — Missing index on Application.talentProfileId and Application.jobId (dashboard + applicant list)

Severity: P1 · Scope: BE-PERF-001

Evidence: castyou-backend/prisma/schema.prisma:473-496 — Application has only @@unique([jobId, talentProfileId]). That composite is jobId-prefixed, so:

• jobApplications (job.ts:286-291): where { jobId, moderationStatus: 'ACTIVE' } — partially served (jobId prefix), but moderationStatus is unindexed.
• myApplications (job.ts:218-222): where { talentProfileId } + orderBy createdAt desc — not served by the composite (talentProfileId is the second column). This is a seq scan.
• discoverJobs / jobsForTalent (job.ts:443-446,528-531): where { talentProfileId } select jobId — same unindexed path.

Measurement: the applications dashboard (myApplications) scans the entire applications table filtered by a non-prefix column on every load. At 50k applications that is a 50k-row scan + sort where an index would touch only the talent's rows. Estimate: tens of ms growing linearly.

Remediation hint: add @@index([talentProfileId, createdAt]) to Application. Optionally @@index([jobId, moderationStatus]) for the producer applicant list. Requires prisma migrate.

PERF-F06 — TalentInteraction / JobInteraction lookups not covered by their unique indexes

Severity: P1 · Scope: BE-PERF-001

Evidence:

• TalentInteraction has @@unique([producerId, talentId, jobId]) (schema.prisma:768). The discover-feed exclusion query (job.ts:380-395) filters by producerId + type + (for some branches) jobId with no talentId — only the producerId prefix is usable; type/expiresAt are unindexed. myApplications shortlist query (job.ts:226-229) filters { talentId, type, jobId: { not: null } } — talentId is the second column, so the unique index does not serve it (seq scan).
• JobInteraction has @@unique([talentId, jobId]) (schema.prisma:788). discoverJobs hidden lookup (job.ts:433-440) filters { talentId, type, OR: expiresAt } — talentId-prefixed, so OK on talentId but type/expiresAt unindexed.

Measurement: the myApplications shortlist scan grows with total interaction volume across all producers (not just the talent's). Estimate: full-table scan of talent_interactions per applications-dashboard load.

Remediation hint: add @@index([talentId, type]) to TalentInteraction and @@index([producerId, type, jobId]); add @@index([talentId, type]) to JobInteraction. Requires prisma migrate.

PERF-F07 — Notification unread-count + listing lack a compound Mongo index

Severity: P1 · Scope: BE-PERF-001

Evidence: castyou-backend/src/models/mongo/Notification.ts:54 declares only a single-field index userId: { index: true }. The two hot reads are:

• getUnreadCount (castyou-backend/src/services/notifications/index.ts:96): countDocuments({ userId, read: false }) — the nav badge, polled.
• getNotifications (notifications/index.ts:88-91): find({ userId }).sort({ createdAt: -1 }).limit(50).

The single userId index serves neither the read filter nor the createdAt sort efficiently — Mongo filters on userId then does an in-memory filter/sort on the matched set.

Measurement: a user with thousands of notifications re-scans + re-sorts all of them on every badge poll and every notifications-page open. Compare to the Message/Conversation models which correctly declare compound indexes (Message.ts:73 { conversationId, createdAt }, Conversation.ts:29 { participantIds, lastMessageAt }).

Remediation hint: add NotificationSchema.index({ userId: 1, read: 1 }) and NotificationSchema.index({ userId: 1, createdAt: -1 }). Mongoose syncIndexes (or a deploy step) applies these.

PERF-F08 — searchTalents JSON post-filter fetches up to 1000 rows then filters in JS

Severity: P1 · Scope: BE-PERF-001

Evidence: castyou-backend/src/graphql/resolvers/talent.ts:138-143 — when any JSON filter (hasNotableWork / hasAward / dreamJobKeyword) is set, internalLimit = min(first * 10, 1000). It then findMany({ where, take: 1000, orderBy: profileCompleteness desc }) (talent.ts:213-217) and filters in JS over notableWorks / awards / dreamJobs JSON columns (talent.ts:222-261). TalentProfile has no index on profileCompleteness (the order-by) and the JSON columns can't be indexed by this access pattern.

Measurement: up to 1000 full TalentProfile rows (a very wide model — schema.prisma:141-224, ~70 columns including several large JSON blobs) deserialized and shipped into Node memory per search, then most discarded. Estimate: hundreds of KB to low-MB of row data per query + the seq scan to gather 1000 rows ordered by an unindexed column.

Remediation hint: add @@index([profileCompleteness]); longer term, denormalize the JSON-search predicates into queryable columns or a GIN index / move to the existing Qdrant semantic path. The non-JSON searchTalents path (talent.ts:149-156) uses ILIKE contains on displayName/bio/etc. with no trigram index — acceptable for now but note it for scale (see PERF-F12).

PERF-F09 — followSuggestions recomputes a multi-pass graph query on every request (no cache)

Severity: P1 · Scope: BE-PERF-001 (+ Redis)

Evidence: castyou-backend/src/services/social/suggestions.ts:49-219 runs, per request: a follow.findMany (followed set), a follow.groupBy over second-degree graph (:65-74), a talent-vector Qdrant call + talentProfile.findMany (:88-99), postLike.findMany + postComment.findMany for behavioral affinity (:118-141), a trending follow.groupBy (:155-164), a recent-posters post.findMany (:171-179), and a final user.findMany (:198-201). That is 7-9 queries plus a vector search for a discovery rail embedded on the feed (FeedPage.tsx:90,139 renders <SuggestionRail />).

Measurement: ~8 DB round-trips + 1 vector search per feed view where suggestions render. The result changes slowly (follow graph + trending over a 7-day window) — it is a textbook cache candidate. Redis is already wired (castyou-backend/src/config/redis.ts) but used only for BullMQ/pubsub, not read caching.

Remediation hint: cache followSuggestions(viewerId) in Redis with a short TTL (e.g. 5-15 min), invalidate on follow/unfollow. Mirrors the existing feature-flag cache pattern (services/featureFlags/cache.ts, 6h TTL).

PERF-F10 — forYouFeed ranks in-memory but paginates by re-scanning the whole ranked list

Severity: P1 · Scope: BE-PERF-001

Evidence: castyou-backend/src/services/feed/index.ts:209-326 — forYouFeed fetches up to 200 candidates (FOR_YOU_CANDIDATE_CAP, :197,242), plus postLike.findMany + postComment.findMany for the viewer's entire like/comment history (:251-260, unbounded — no take), scores all 200, interleaves system posts, then for pagination does ranked.findIndex(p => p.id === after) (:313) and re-runs the entire 200-candidate fetch + scoring on every page request because nothing is memoized between pages.

Measurement: every "load more" on the For You tab re-fetches 200 candidates + the viewer's full engagement history + re-scores, just to slice a different 10-item window. For a viewer with a large like/comment history the two unbounded findManys alone can be thousands of rows per page load.

Remediation hint: bound the engagement-history queries with a take, and cache the ranked candidate id-list per viewer for a short TTL so subsequent pages slice the cached ranking instead of recomputing.

PERF-F11 — ReactQueryDevtools ships in the production entry bundle

Severity: P1 · Scope: FE-PERF-001

Evidence: castyou-frontend/apps/app/src/main.tsx:4 statically imports ReactQueryDevtools from @tanstack/react-query-devtools and renders <ReactQueryDevtools initialIsOpen={false} /> (main.tsx:27) with no import.meta.env.DEV guard. Because it's a static import in the entry module, the devtools bundle is included in every production build.

Measurement / estimate: @tanstack/react-query-devtools is ~40-50 KB gzipped of JS that no end user needs. It loads in the critical entry chunk (main.tsx is the app entry), delaying interactivity on first paint.

Remediation hint: lazy-load behind if (import.meta.env.DEV) with React.lazy, or drop it from the production tree entirely. One-line gate.

PERF-F12 — Vite build has no manualChunks; React/Apollo/i18n/design-system collapse into one vendor chunk

Severity: P1 · Scope: FE-PERF-001

Evidence: castyou-frontend/apps/app/vite.config.ts defines only plugins, resolve.alias, and server — no build.rollupOptions.output.manualChunks and no build block at all. Route components are correctly React.lazy'd (App.tsx:12-97) and the heaviest libs are well-isolated to their lazy route chunks (Konva → only FlierEditorPage via the lazy FlierEditorRouter, confirmed apps/app/src/pages/jobs/flier/FlierEditorRouter.tsx:6 + components/flier/FlierCanvas.tsx; @dnd-kit → only the lazy ReelEditorPage, pages/reel/ReelEditorPage.tsx). Good: the heavy editors do NOT leak into the entry chunk. Problem: without manualChunks, all shared vendor code (react, react-dom, react-router, @tanstack/react-query, graphql-request, react-i18next, @castyou/design-system, @phosphor-icons/react) lands in a single large vendor chunk that must download fully before any route renders, and a single dependency bump busts the whole vendor cache.

Measurement / estimate: the combined react + router + react-query + i18n + design-system + phosphor-icons vendor surface is on the order of 250-400 KB gzipped in one undivided chunk. Splitting into stable vendor groups improves cache hit rate across deploys and lets the browser parallelize downloads.

Remediation hint: add build.rollupOptions.output.manualChunks grouping react/react-dom/react-router as one chunk and the data/i18n libs as another. Verify Konva/@dnd-kit stay in their route chunks after the change.

PERF-F13 — Producer "Discover" candidate pool fetches 500 full TalentProfile rows per request

Severity: P1 · Scope: BE-PERF-001

Evidence: castyou-backend/src/graphql/resolvers/job.ts:401-407 (discoverFeed) and :453-462 (discoverJobs) / :555-561 (jobsForTalent) all use POOL_LIMIT = 500 and findMany the full entity (no select) ordered by profileCompleteness (talent) — an unindexed order-by (TalentProfile has no @@index, schema.prisma:141-224) — then score in JS.

Measurement: 500 full ~70-column TalentProfile rows (with JSON blobs) materialized per discover/match request, ordered by a column with no index (seq scan + sort of the whole table to pick the top 500). Estimate: low-MB result transfer + a full-table sort per request.

Remediation hint: add @@index([profileCompleteness]); add a Prisma select limited to the fields the scorer (scoreDiscoverFeed/scoreDiscoverJobs) actually reads instead of *.

---

P2 — Backlog

PERF-F14 — Over-fetch: list resolvers return full entities where a projection suffices

Severity: P2 · Scope: BE-PERF-001

Evidence: jobs/myJobs include the whole Job + producer + flierConfig (job.ts:27 JOB_INCLUDE, used at :152,189). Job carries large JSON columns rarely needed in a list view: flierSpec, requirements, physicalRequirements, recordingDates (schema.prisma:428,433,435,442), and flierConfig pulls canvas-layout JSON. Same for searchTalents/discover returning full TalentProfile.

Estimate: for a 20-row jobs list, shipping flierSpec/requirements/flierConfig JSON that the table never renders is wasted serialization + network. Low individual cost, but it compounds with PERF-F01/F13.

Remediation hint: GraphQL field-level select driven by the requested selection set, or a lighter JobListItem projection for list endpoints.

PERF-F15 — reorderPortfolioItems / reorderPortfolioFolders issue one UPDATE per item in a transaction

Severity: P2 · Scope: BE-PERF-001

Evidence: castyou-backend/src/graphql/resolvers/portfolio.ts:235-239 and :304-308 map each id to a separate mediaItem.update inside $transaction. Reordering N items = N UPDATE statements.

Estimate: bounded by a talent's portfolio size (typically small), so impact is low — but a CASE-based single UPDATE ... SET order = CASE id ... or updateMany per distinct value would collapse N round-trips to 1.

PERF-F16 — R2 media: feed/portfolio rely on thumbnailUrl but there's no verified CDN cache-header or responsive-size strategy

Severity: P2 · Scope: BE-PERF-001 / FE-PERF-001

Evidence: Posts and media items store a single thumbnailUrl alongside the full mediaUrl (schema.prisma:1072 Post.thumbnailUrl, :325 MediaItem.thumbnailUrl), generated async by the media worker (feed/index.ts:113 enqueueMediaProcessing). The feed card consumes thumbnailUrl (useFeed.ts:79), which is correct. However: (a) flier renders are cache-busted with ?v=${Date.now()} on every save (job.ts:826,852) which defeats CDN caching for the flier image (acceptable since it's a deliberate cache-bust on re-save, but worth noting); (b) there is no evidence in the resolvers of multiple responsive thumbnail sizes or <video poster> wiring — a single thumbnail is used for both grid and full views. This is a static-analysis observation, not a measured regression.

Remediation hint: confirm R2/CDN Cache-Control headers on media objects (out of code scope — infra); consider 2-3 responsive thumbnail widths for the feed vs. profile grid.

PERF-F17 — ILIKE contains search has no trigram/GIN index (scale risk, not yet hot)

Severity: P2 · Scope: BE-PERF-001

Evidence: searchTalents (talent.ts:149-156), jobs/jobsForTalent (job.ts:131-136,541-546), searchPeople/searchPosts (services/search/unified.ts:74-95,199-207) all use Prisma contains with mode: 'insensitive', which compiles to ILIKE '%term%'. A leading-wildcard ILIKE cannot use a btree index — it is always a seq scan.

Estimate: fine at current data volumes; becomes a seq-scan-per-keystroke problem as talent_profiles/posts/jobs grow. Note that the semantic Qdrant path already offloads much of this when configured (unified.ts:108-142).

Remediation hint: add pg_trgm GIN indexes on the searched text columns, or lean harder on the existing vector search for these surfaces.

---

Summary table

ID	Severity	Scope	One-line
PERF-F01	P0	BE	Job table has zero indexes — every jobs/discover query is a seq scan + sort
PERF-F02	P0	BE	Feed Post field resolvers = ~1 + 4N queries per 10-post page
PERF-F03	P1	BE	Talent jobs grid myApplication is an N+1 (2 queries/card, profile lookup repeated)
PERF-F04	P1	BE	PetJob.applicationCount un-batched count() per row (no loader)
PERF-F05	P1	BE	No index on Application.talentProfileId — applications dashboard seq-scans
PERF-F06	P1	BE	TalentInteraction/JobInteraction lookups not covered by their unique indexes
PERF-F07	P1	BE	Notification unread-count/listing lack compound Mongo index (userId+read/createdAt)
PERF-F08	P1	BE	searchTalents JSON filters fetch up to 1000 wide rows, filter in JS, unindexed sort
PERF-F09	P1	BE/Redis	followSuggestions runs ~8 queries + vector search per feed view, no cache
PERF-F10	P1	BE	forYouFeed re-fetches 200 candidates + unbounded engagement history per page
PERF-F11	P1	FE	ReactQueryDevtools ships in the production entry bundle (no DEV guard)
PERF-F12	P1	FE	Vite build has no manualChunks — one undivided vendor chunk
PERF-F13	P1	BE	Discover pools fetch 500 full TalentProfile rows ordered by unindexed column
PERF-F14	P2	BE	List resolvers over-fetch full entities (heavy JSON) where a projection fits
PERF-F15	P2	BE	Portfolio reorder = N UPDATEs in a transaction
PERF-F16	P2	BE/FE	Single thumbnail for all sizes; CDN cache headers unverified; flier cache-bust
PERF-F17	P2	BE	ILIKE contains search has no trigram/GIN index (scale risk)

Counts: 2 × P0, 11 × P1, 4 × P2 — 17 findings total.

Top P0/P1 to fix first

1. PERF-F01 (P0) — add @@index([status, createdAt]) + @@index([producerId, createdAt]) to Job. Single migration, fixes the most-used read path on the platform.
2. PERF-F02 (P0) — batch the feed Post count/likedByMe/latestComments resolvers with DataLoaders (or reuse the _count pattern forYouFeed already has). Collapses ~42 queries/feed-page to ~5.
3. PERF-F05 + PERF-F06 (P1) — add Application.@@index([talentProfileId, createdAt]) and the interaction-table indexes. Same migration as F01.
4. PERF-F03 + PERF-F04 (P1) — add the talent-profile-per-request cache + myApplication/PetJob.applicationCount DataLoaders.
5. PERF-F11 + PERF-F12 (P1, FE) — gate ReactQueryDevtools behind DEV and add manualChunks. Two small, low-risk frontend diffs with immediate bundle-size payoff.
6. PERF-F09 (P1) — cache followSuggestions in Redis (infra already present).

---

Instrumentation

A permanent, opt-in Apollo Server plugin was added: castyou-backend/src/plugins/queryTiming.ts.

• Active only when process.env.PERF_TRACE === 'true'; a complete no-op otherwise (every hook early-returns), safe to leave registered in any environment.
• Per GraphQL operation it logs (via the repo's winston logger): total wall-clock duration, the slowest resolver fields (path + self-ms), the resolver count, and a Prisma + Mongo round-trip count for the operation.
• Matches the existing plugin style (ApolloServerPlugin<GraphQLContext>, requestDidStart, .js ESM import suffixes) used by src/middleware/impersonationAudit.ts and src/middleware/accountStatus.ts. Type-checks clean against the repo's Apollo 4 / Prisma 7 types (tsc --noEmit exits 0).

To enable (left for BE-PERF-001 remediation — not wired in, to avoid touching the running server):

1. In castyou-backend/src/index.ts, add queryTimingPlugin to the ApolloServer plugins array, and run with PERF_TRACE=true. Resolver timing + total duration work immediately with just this step.
2. For DB round-trip counts, wrap the per-request client in castyou-backend/src/config/context.ts with prisma: instrumentPrisma(prisma) (and the same in createSubscriptionContext). Mongo round-trips can be counted by calling the exported recordMongoQuery() from a Mongoose hook. Without step 2 the count simply reports 0 — the plugin never throws.

Use this to validate PERF-F02 (expect ~42 dbQueries on a feed operation) and PERF-F03 (expect ~20 on a talent jobsForTalent operation) before and after remediation.

---

Remediation — P0 closed (2026-06-15)

Scope: BE-PERF-001, P0 findings only. The 11 P1s and 4 P2s remain open.

PERF-F01 — Job indexes (CLOSED)

Added two composite indexes to the Job model (castyou-backend/prisma/schema.prisma) matching the verified hot-query shapes:

• @@index([status, createdAt]) — serves jobs / discoverJobs / jobsForTalent (where { status: 'OPEN' } orderBy createdAt desc).
• @@index([producerId, createdAt]) — serves myJobs (where { producerId, status } orderBy createdAt desc).

Migration applied (not hand-authored): a local dev Postgres was reachable, so prisma migrate dev --name perf_job_indexes created and applied castyou-backend/prisma/migrations/20260615165845_perf_job_indexes/migration.sql (CREATE INDEX "jobs_status_createdAt_idx" + CREATE INDEX "jobs_producerId_createdAt_idx"), and prisma generate was re-run. Deploy to other environments with prisma migrate deploy.

Before/after: status='OPEN' ORDER BY createdAt DESC LIMIT 20 went from a full seq scan + in-memory sort of jobs to an index range scan (~touches the LIMIT rows). myJobs likewise moves off a seq scan onto the producerId-prefixed index.

PERF-F02 — Feed Post field N+1 (CLOSED)

Wired the feed Post field resolvers (castyou-backend/src/graphql/resolvers/feed.ts) to per-request DataLoaders constructed in the context factory (createLoaders in castyou-backend/src/config/dataloaders.ts, instantiated per request in src/config/context.ts — never module-level, so no cross-user cache bleed):

• likeCountByPostId — one postLike.groupBy per page.
• commentCountByPostId — one postComment.groupBy per page.
• latestCommentsByPostId(limit) — one postComment.findMany per page, windowed top-N per post in JS; memoised per-request by limit.
• likedByMeByPostId(viewerId) — one postLike.findMany per page scoped to the viewer; memoised per-request by viewerId.

shareCount and mine were left as-is (out of P0 scope; mine is profile/detail-surface only).

Before/after query counts (10-post feed page):

• Before: 1 + 4N = 2 (feed findMany + count) + 10×4 field round-trips = ~42 queries.
• After: 2 (feed) + 4 batched field queries (one per loader) = ~6 queries — constant in N. Infinite scroll stays constant per page instead of multiplying.

Tests: added castyou-backend/src/__tests__/config/dataloaders.test.ts (proves each loader batches a multi-post page into a single underlying query, maps values back per post, and is memoised per viewer/limit with no cross-user instance sharing) and extended castyou-backend/src/__tests__/resolvers/feed.test.ts (field resolvers resolve through ctx.loaders). Loader stubs added to the makeLoaders() helper. tsc --noEmit clean; vitest run resolvers + feed + dataloaders = 552 passing.

---

Remediation — P1/P2 (2026-06-15)

Scope: BE-PERF-001, the 11 P1s and 4 P2s. The 2 FE P1s (PERF-F11, PERF-F12) belong to FE-PERF-001 and were not touched here. All changes type-check clean (tsc --noEmit exit 0) and the full backend suite is green (vitest run = 74 files / 1328 tests + 4 todo passing).

One migration covers all P1 Postgres indexes

prisma migrate dev --name perf_p1_indexes created + applied castyou-backend/prisma/migrations/20260615171702_perf_p1_indexes/migration.sql against the local dev Postgres, and prisma generate was re-run. Deploy elsewhere with prisma migrate deploy. Six indexes (covers F05, F06, F08, F13):

• applications(talentProfileId, createdAt) and applications(jobId, moderationStatus) — F05
• talent_interactions(talentId, type), talent_interactions(producerId, type, jobId), job_interactions(talentId, type) — F06
• talent_profiles(profileCompleteness) — F08 + F13

PERF-F03 — Job.myApplication N+1 (CLOSED)

castyou-backend/src/graphql/resolvers/job.ts — Job.myApplication now resolves the talent profile once per request via the existing talentByUserId loader (was a fresh talentProfile.findUnique on every card) and batches the per-job application lookups through a new per-request myApplicationByJobId(talentProfileId) loader in src/config/dataloaders.ts (one application.findMany per grid). Per-request lifetime ⇒ no cross-user cache bleed. Before/after (10-job grid): 10×2 = 20 queries (10 identical talent lookups + 10 application lookups) → 2 queries (1 talent + 1 batched applications), constant in N. Tests: src/__tests__/config/dataloaders.test.ts (batch + per-talent memoisation) and updated src/__tests__/resolvers/myApplications.test.ts.

PERF-F04 — PetJob.applicationCount un-batched count (CLOSED)

castyou-backend/src/graphql/resolvers/petJob.ts — PetJob.applicationCount now uses a new petJobApplicationCountByPetJobId groupBy-backed loader (mirrors applicationCountByJobId). Before/after (20-row pet-jobs table): 20 count() queries → 1 groupBy. Tests: src/__tests__/config/dataloaders.test.ts + updated src/__tests__/resolvers/petJob.test.ts.

PERF-F05 — Application indexes (CLOSED)

@@index([talentProfileId, createdAt]) (serves myApplications / discoverJobs / jobsForTalent where { talentProfileId } orderBy createdAt desc) and @@index([jobId, moderationStatus]) (producer applicant list). myApplications moves off a full applications seq scan onto an index range scan touching only the talent's rows.

PERF-F06 — Interaction-table indexes (CLOSED)

TalentInteraction.@@index([talentId, type]) (the myApplications shortlist read was a full-table scan because the unique is producerId-prefixed) + @@index([producerId, type, jobId]) (discover-feed exclusion). JobInteraction.@@index([talentId, type]) (discover hidden-jobs lookup). The shortlist scan no longer grows with cross-producer interaction volume.

PERF-F07 — Notification compound Mongo indexes (CLOSED)

castyou-backend/src/models/mongo/Notification.ts — added index({ userId: 1, read: 1 }) (serves the polled getUnreadCount badge) and index({ userId: 1, createdAt: -1 }) (serves getNotifications find({userId}).sort({createdAt:-1})). Dropped the now-redundant standalone userId single-field index (both compounds are userId-prefixed). Applied via Mongoose syncIndexes/autoIndex on deploy.

PERF-F08 — searchTalents over-fetch (PARTIAL)

Added talent_profiles(profileCompleteness) so the orderBy profileCompleteness desc is index-served instead of a full-table sort to gather the candidate pool. Deferred (large): the JSON post-filter still fetches up to 1000 wide rows and filters in JS — the JSON predicates (notableWorks/awards/dreamJobs) cannot be btree-indexed; a real fix needs denormalised queryable columns or a GIN index (overlaps F17) or leaning on the Qdrant semantic path. Not attempted to keep this surgical.

PERF-F09 — followSuggestions Redis cache (CLOSED)

New castyou-backend/src/services/social/suggestionsCache.ts (mirrors services/featureFlags/cache.ts): per-viewer cache, 5-min TTL, all failures non-fatal (falls through to recompute). suggestions.ts followSuggestions is now a thin cached wrapper around computeFollowSuggestions (the original body). Invalidated on follow/unfollow for both the actor and the target (services/social/index.ts), since either side's friends-of-friends ranking can shift. Caches only the slow-changing discovery rail — never viewer-specific authorization. Before/after (per feed view where the rail renders): ~8 DB round-trips + 1 vector search → 0 on a cache hit (one redis.get), recompute only on miss/expiry/invalidation. Tests: added cache hit/miss + store + computeFollowSuggestions-is-pure cases to src/__tests__/services/suggestions.test.ts.

PERF-F10 — forYouFeed unbounded engagement history (PARTIAL)

castyou-backend/src/services/feed/index.ts — the viewer's like/comment history pulled for affinity scoring was two unbounded findManys on every page load. Both now orderBy createdAt desc + take FOR_YOU_AFFINITY_HISTORY_CAP (500). Affinity saturates at 5 engagements per author, so the most-recent 500 is strictly sufficient signal. Before/after: the two affinity queries go from "viewer's entire lifetime like+comment history (thousands of rows for an active viewer)" to ≤500 rows each, per page. Deferred (larger): the per-page recompute (re-fetch 200 candidates + re-score to slice a different 10-item window) is not yet memoised. Caching the ranked id-list per viewer is more involved (it interleaves a live system-post query and would need its own short-TTL cache + shape/invalidation); left as the remaining piece. The candidate cap (200) already bounds that fetch.

PERF-F13 — Discover pool unindexed order-by (PARTIAL)

The talent_profiles(profileCompleteness) index above removes the full-table sort that discoverFeed did to gather its 500-row pool ordered by profileCompleteness desc. Deferred: narrowing the findMany to a Prisma select of only the scorer's fields is not safe here — the discover/jobs resolvers return these rows as GraphQL TalentProfile/Job entities whose field resolvers (socialHandles, followedByMe, applicationCount, etc.) read columns beyond the scorer's set, so a projection would break the resolver chain. Same constraint blocks the F14 list-projection idea. (Touching the SDL/field-redaction layer is also out of this agent's scope.)

P2 findings

• PERF-F14 (list-resolver projection): deferred — same blocker as F13: list endpoints return full GraphQL entities with downstream field resolvers, so a select projection would drop fields those resolvers depend on. A safe fix needs a dedicated lighter list type / selection-set-driven select, which is not a cheap change.
• PERF-F15 (portfolio reorder N UPDATEs): out of this agent's scope — lives in src/graphql/resolvers/portfolio.ts (reserved for the security agent's surface). Noted, not changed. Impact is low (bounded by a talent's portfolio size); a CASE-based single UPDATE or updateMany-per-value would collapse N→1 if picked up later.
• PERF-F16 (CDN cache headers / responsive thumbnails): out of code scope — R2/CDN Cache-Control is infra, and responsive thumbnail sizing spans the media worker + frontend (FE-PERF-001). No backend-only cheap fix. Noted.
• PERF-F17 (pg_trgm GIN for ILIKE search): deferred — not cheap/clear. Requires enabling the postgresqlExtensions preview feature on the Prisma generator, CREATE EXTENSION pg_trgm, and per-column GIN indexes across talent_profiles/posts/jobs, with write-amplification/index-bloat tradeoffs. The audit itself rates it "fine at current data volumes" / scale-risk-only. Left as a note per the P2 "leave a one-line note if not cheap" instruction.

---

Remediation — P1/P2 (2026-06-15): FE-PERF-001 (frontend)

Scope: the two FE P1s (PERF-F11, PERF-F12) + cheap FE P2s. pnpm typecheck (turbo, all 3 packages) and pnpm --filter @castyou/app lint both pass; full app suite 555/555 green.

PERF-F11 — ReactQueryDevtools in prod bundle (CLOSED)

apps/app/src/main.tsx — the static import { ReactQueryDevtools } was removed. It is now React.lazy-imported behind an import.meta.env.DEV guard and rendered only when import.meta.env.DEV (inside <Suspense fallback={null}>). In a production build the dynamic import is statically unreachable, so Rollup tree-shakes the ~40-50 KB devtools bundle out of the entry chunk entirely.

PERF-F12 — Vite manualChunks (CLOSED)

apps/app/vite.config.ts — added build.rollupOptions.output.manualChunks splitting the shared vendor surface into stable cache lines: vendor-react (react/react-dom/react-router/scheduler), vendor-data (@tanstack/react-query, graphql-request, graphql, i18next + react-i18next + detector, zustand), and vendor-icons (@phosphor-icons). Konva and @dnd-kit are intentionally NOT named, so Rollup keeps them in their existing lazy route chunks (FlierEditorPage / ReelEditorPage) — verified the matcher only catches the listed packages and returns undefined otherwise, so route-level code-splitting is not regressed. A single dependency bump now busts only its own vendor group, not the whole vendor cache.

React Query / staleTime / hover-prefetch

The global queryClient already sets staleTime: 5min + gcTime: 10min (apps/app/src/lib/queryClient.ts), and the stable-data hooks (useFeatureFlag, useJob, useTalentProfile) implement their own per-key 5-min freshness window via queryClient.getQueryState/setQueryData rather than useQuery. So feature-flag/profile reads are already effectively cached for 5 min. Hover-prefetch deferred (not cheap): those hooks don't use useQuery, so wiring prefetchQuery would need a new prefetch entry point per hook + onMouseEnter on every job/profile/talent card across many surfaces — too broad for a surgical pass. Noted for a follow-up.

PERF-F16 (FE half — responsive thumbnails)

Deferred (cross-cutting): the feed/portfolio already consume the backend thumbnailUrl (correct). Multiple responsive widths + <video poster> wiring span the media worker (BE) + DS image components and are out of a cheap FE pass. Noted.