Pipelines

Competency matching

The feature that separates this from a generic LMS. Trainer profiles and courses each get an embedding from the same local model. An admin opens a course and sees the five closest trainers, ranked by similarity.

Flow

Competency mapping flow

The highlighted step is the only code path allowed to read every trainer's vector.

Functions

Function	Trigger	Does
`embedTrainerProfile(userId)`	PUT /api/trainers/me/profile, after commit	Builds the profile text, embeds it, writes competency_embedding and embedding_model.
`embedCourse(courseId)`	course status becomes published	Builds the course text, embeds it, writes competency_embedding and embedding_model.
`suggestTrainers(courseId, limit = 5)`	GET /api/admin/competency/suggest	Runs the ranking query on matcherPool and returns user_id, name, similarity.
`matcherPool`	startup	A pg Pool using MATCHER_DATABASE_URL, the BYPASSRLS role. Imported by suggestTrainers only.

Text that gets embedded

embedding input

trainer:  {bio}
          Skills: {skills joined by ", "}
          Specialisations: {specialization labels joined by ", "}

course:   {title}
          {description}
          Subject: {subject label}

Same model, same prefix

Trainers and courses must come from the same model and the same prefix convention, or the distances mean nothing.

Labels, not ids

specialization_ids are UUIDs. Resolve them to their labels first. Embedding an id string carries no meaning.

Refresh on change

Regenerate when the source text changes, and on a model change. The embedding_model column tells you which rows are stale.

The ranking query

services/api/src/competency/suggest.sql

SELECT tp.user_id, u.name,
       1 - (tp.competency_embedding <=> c.competency_embedding) AS similarity
FROM   trainer_profiles tp
JOIN   "user" u ON u.id = tp.user_id
CROSS  JOIN courses c
WHERE  c.id = $1
  AND  tp.embedding_model = c.embedding_model      -- never compare across models
  AND  tp.competency_embedding IS NOT NULL
ORDER  BY tp.competency_embedding <=> c.competency_embedding
LIMIT  5;

services/api/src/competency/suggest.ts

import { matcherPool } from '../db/matcherPool';   // the ONLY importer of this pool

export async function suggestTrainers(courseId: string, limit = 5) {
  const { rows } = await matcherPool.query(SUGGEST_SQL, [courseId]);
  return rows.map(r => ({ userId: r.user_id, name: r.name, similarity: Number(r.similarity) }));
}
// Route: GET /api/admin/competency/suggest?course_id=  (admin only, generalApiLimiter)

Privileged on purpose

Under normal RLS one trainer cannot see another trainer's row. This job needs all of them, so it gets its own BYPASSRLS Postgres role and its own pool. Nothing else may import it.

Guard the route

Because the pool bypasses RLS, the route itself must enforce admin. authorizeRoute does that, and the handler should also assert req.role === "admin".

No index needed

A sequential scan over a few hundred trainers is instant. Skip a vector index on these two columns.

Reading the score

Similarity is 1 minus cosine distance. It ranks candidates. Do not present it as a percentage of fit.

Beyond trainers

The same vectors can compare a trainee's skills with a role's needs and recommend courses that close the gap. It reuses these embeddings, so it adds a query, not a pipeline.

Capacity Connect · Team Syntax Squad · SIH 2026 · PS 26075Code samples are implementation sketches.