▤ CODEX // oaoisme wiki

§ unswayed-backend

Master data (Phase 2)

updated 2026-06-05

Master data is the layer of reference / lookup tables the rest of the platform points at — countries, states, cities, genders, ethnicities, disabilities, universities, and the five job-reference tables. Phase 2 makes these tables real and serves the 14 read-only list endpoints the mobile app calls to populate dropdowns (registration, job posting, filters). It lives in one bounded module, src/master-data/, plus an idempotent CSV seeder in src/cli/.

Why it sits where it does

Phase 0 stored master-data references on profiles as plain integers — applicants.country/state/city/gender/ethnicity, employers.country/state/city — with no foreign-key target. Phase 2 turns those loose ints into real tables so a later phase (Profiles / Employer jobs) can convert the integers into proper foreign keys and validate them. That's why master data lands before profiles: it's a shared contract many downstream slices compile against.

The shape of the data

Most tables share one shape: { id, name, status, created_at, updated_at }. Two are special:

  • Geo hierarchy — Country → State → City. Countries add code + icon; states/cities add a parent FK (country_id / state_id, cascade delete) + icon. The whole model is serialized on the wire (no projection), so code/icon/status/timestamps all appear.
  • University — a distinct schema with no status: name, alpha_two_code (a 2-char ISO code, indexed), country (indexed), state_province?, domain?, web_page?. The endpoint projects only those seven columns (no timestamps).

Every searchable name column is indexed to back the LIKE %q% search.

How the endpoints work

The legacy Laravel MasterController had 13 near-identical handlers (when($search) → where name like %q% → orderBy('name') → get()). The reimplementation keeps every route's exact path, auth, and response key but factors the duplication into one table-parameterized MasterDataService: a private nameFilter(q) builds the shared case-insensitive contains clause, and thin listGenders/listJobTypes/… methods each call the right Prisma delegate. Universities (its own projection + a second country filter + country,name order) and the two name-resolvers are the only non-generic shapes.

Controllers stay thin — read the optional ?q= (and path params), call one service method, map the camelCase Prisma row to the snake_case wire resource, and return { message, data: { <key>: [...] } }. A global interceptor adds the { status: "success", … } envelope; nothing builds it by hand.

Two controllers split by auth:

  • Public (§1, guest): GET /api/countries, /states/{countryId?}, /cities/{cityId?}, /states-by-country-name/{countryName}, /cities-by-state-name/{stateName}, /genders, /ethnicities, /universities.
  • Authenticated (§28, JwtAuthGuard + AccountStatusGuard): GET /api/job_categories, /job_locations, /job_types, /job_shifts, /job_shift_timings, /disabilities. (Disabilities is reference data but is auth-gated like the job tables — it's consumed at apply-time, not registration. That gating is preserved.)

The frozen quirks (reproduced exactly)

The wire contract is read-only, so several legacy oddities are kept on purpose, each pinned by a test:

  • GET /cities/{cityId?} is misnamed — the path param is actually a STATE id. It also accepts a JSON array (/cities/[1,2,3] → WHERE state_id IN (…)). Malformed JSON, 0, or no param → no filter (mirrors Laravel's json_decode + when()). The public path stays …/{cityId?}; only the internal variable is named stateParam.
  • GET /ethnicities returns a capitalized key — data.Ethnicities, not ethnicities. A legacy typo, now frozen into §1.
  • Name resolvers do an exact (case-insensitive) match, not a LIKE, and return Country not found. / State not found. as 404s (legacy frequently 500'd on not-found; we return typed 4xx but keep the exact message strings).
  • status is dead-for-reads — every reference table carries a status column, but the list endpoints never filter on it; all rows are returned regardless. (Active-only filtering, if ever wanted, must be a new query param, not a default change — it would alter the wire.)

Seeding — from the real production dumps

The data is loaded by an idempotent seed:master-data CLI command (nest-commander) from production CSV dumps in seeders/. These dumps are the live data and supersede the stale legacy seeder arrays — they differ in real ways (separate "Administration & Office Work" + "Aviation" categories rather than a merged typo; well-formed (05:00 AM - 1:30 PM) Monday - Friday shift timings; a "Prefer not to say" gender; an id-47 "Other" category). The seeder:

  • preserves the exact ids from the CSV (a later phase's foreign keys depend on them);
  • seeds FK-safe (countries → states → cities, then the independent lookups);
  • is non-destructive and idempotent — it uses createMany({ skipDuplicates: true }) (never truncates), so a second run inserts zero rows;
  • parses CSV itself (a small RFC-4180 parser, no new dependency) so quoted fields with embedded commas (e.g. "Southern Nations, Nationalities, and Peoples'") load correctly.

Run it with npx ts-node -r tsconfig-paths/register src/cli.ts seed:master-data (add --dir <path> to point at a different dump directory).

Gotchas for the next agent

  • Don't add a status = active filter to any list — it would change the wire response. All rows, always.
  • Don't "clean up" the data — the capitalized Ethnicities key and the verbatim CSV strings are intentional parity.
  • Universities has no status and the endpoint returns no timestamps — keep its projection distinct.
  • The reference created_at/updated_at are kept non-null (codebase convention); rows whose CSV timestamps were empty get a seed-time created_at. The wire shape is unchanged (the contract doesn't pin timestamp values) — recorded as an internal-only deviation in API-CONTRACT.md / ADR-0025.

See ADR-0025 (DECISIONS.md), the §1/§28 rows in API-CONTRACT.md, and test/master-data.e2e-spec.ts for the contract proofs.