Pathsy: the data model and analytics behind a career-guidance platform

Every year, millions of Indian students finish Class 10 and 12 with no structured way to compare what comes next. Guidance is fragmented across coaching-institute marketing (biased), word of mouth (narrow), and scattered blogs (unverified) — so demand hyper-concentrates into engineering and medicine while vocational and emerging paths stay invisible.

Pathsy structures the decision as data: every major path as comparable, queryable records — courses, entrance exams, specializations, career roles, salary progressions — plus a quiz that maps student preferences to streams. My question as the analyst: can a transparent, rules-based model (no black-box AI) give students a genuinely useful ranked recommendation?

Curated reference data (supply side): 78 active courses, 288 specializations, and 833 career roles across 4 education stages and 30 streams, hand-researched from state board syllabi (BIEAP, TSBIE, CBSE) and exam-authority sources, normalized into a 12-table PostgreSQL schema — courses → specializations → career roles, entrance exams linked through a many-to-many junction table, and a 4-band salary progression (Fresher → Junior → Mid → Senior) per course.

Behavioral data (demand side): product events — quiz starts and completions, searches (including zero-result ones), path views, saves. The append-only events table is designed and the funnel/search-gap SQL is written; instrumentation is the current sprint (see 07).

• Naming normalization: AP/Telangana stream names (MPC, BiPC) unified with CBSE equivalents (PCM, PCB) — both always displayed; a synonym map handles search (“doctor” → MBBS).
• Salary standardization: all sources converted to a common 4-band LPA progression; conflicting sources resolved to conservative midpoints and flagged with an on-page disclaimer.
• Soft deletes: courses carry an is_active flag instead of being deleted, so saved-path references never break.
• Stated assumptions: salary bands are indicative national medians, not offers; quiz weights are hand-assigned per path and validated by simulation (see 05).

A guidance product is only as trustworthy as its least-complete record, so the first analysis was an audit of the dataset I curated — run against production:

SELECT c.slug, c.name,
       COUNT(DISTINCT sr.id)      AS salary_bands,   -- expected: 4
       COUNT(DISTINCT ce.exam_id) AS linked_exams
FROM courses c
LEFT JOIN salary_ranges sr         ON sr.course_id = c.id
LEFT JOIN course_entrance_exams ce ON ce.course_id = c.id
WHERE c.is_active
GROUP BY c.id, c.slug, c.name
HAVING COUNT(DISTINCT sr.id) < 4
    OR COUNT(DISTINCT ce.exam_id) = 0;

The quiz scores 9 answers against per-path JSONB weights. To test calibration, I defined 9 student archetypes — the answers a stereotypical aspirant of each path would pick, chosen from option text alone — and ran them through the live scoring matrix:

Behavioral events flow into a single append-only app_events table (anonymous session id, event name, JSONB payload — insert-only RLS for clients). The funnel and zero-result-search SQL is already written against it. The Power BI report has three pages:

• Engagement overview: WAU, views by stage, top paths, save-rate trend
• Quiz funnel: start → per-question → completion, top-1 result distribution
• Content gaps: zero-result search terms, demand vs. supply per stream, saves-to-views ranking

Relational modeling from a blank page (12 tables, junction tables, RLS, full-text search), disciplined data curation with documented assumptions, a transparent scoring model whose flaws I went looking for and found, and SQL/Python used the way analysts actually use them — to audit, validate, and decide what to fix next. The honest status: reference data and validation are live; behavioral instrumentation ships next, and this page will grow real funnel numbers when it does.