# EE data architecture (Claude-compact)

IRCC HTML table is JS-filled. Source of truth = JSON, not the empty HTML `<table>`.

Official page: https://www.canada.ca/en/immigration-refugees-citizenship/corporate/mandate/policies-operational-instructions-agreements/ministerial-instructions/express-entry-rounds.html
Feed: https://www.canada.ca/content/dam/ircc/documents/json/ee_rounds_123_en.json
Local copy: `backend/database/data/express_entry/ee_rounds_123_en.json`

## 1. Portal “All rounds” (`type=general`) = IRCC full table

| IRCC page | Portal `?type=general` |
|---|---|
| One table, **all** draws | Same: **435** (`#` 1–434 + `91a`/`91b`) |
| Col “Round type” = `drawName` | Col “Round” = `draw_name_source` |
| Empty HTML without JS | N/A (data from bootstrap/MySQL) |

Narrower views (optional):

- `?type=program&scope=CEC|PNP|FSTP|FSWP` → program-only
- `?type=category&scope=…` → one category/version

DB still stores `round_type` `general` (178: `No Program Specified` 167 + `General` 11) vs `program` (184) vs `category` (73). The unfiltered UI just does not hide the latter two.

Special labels: `91a` + `91b` (same `round_number=91`). Both listed.

## 2. Census (live feed = local published)

`435` published. Split: general 178, program 184, category 73.

Program: PNP 110, CEC 66, FSTP 7, FSWP 1.

Category drawName → our keys (mapper): see §5.

## 3. Pipeline

```
IRCC JSON ──seed──► MySQL ──GET /api/v1/public/express-entry/bootstrap──► portal
     ▲                ▲
     └── CMS POST import/ircc (corrects+inserts; log in express_entry_import_logs)
NOC/categories: static snapshot JSON + StatCan CSVs ──ExpressEntryCategorySnapshotSeeder──► MySQL
```

| Layer | Path | Role |
|---|---|---|
| Feed parse | `ExpressEntryIrccImportService` | HTTP GET JSON `rounds[]`; upsert by `round_label` |
| Seed | `ExpressEntrySeeder` | Same mapping from checked-in JSON |
| drawName | `ExpressEntryDrawNameMapper` | Exact string → `round_type`, `category_key`, `category_version` |
| NOC lists | `ee_category_snapshot_2026-06-22.json` | Category versions + members `[code, teer, en, fr]` |
| Index titles | `noc_2021_v1_index_titles_ee_{en,fr}.csv` | StatCan NOC 2021 V1 Elements extract |
| Portal API | `ExpressEntryPublicController::bootstrap` | All published rounds + categories/defs/NOCs + page copy |
| Filters | `apps/user-portal/.../invitationFilters.ts` | URL `type,scope,year,definition` |
| Table cols | `invitationRoundDisplay.ts` | Invite #, official name, date, invites, CRS, tie-break, programs |
| Occupations UI | `NocOccupationPanel` | Def members; expand → `/public/express-entry/noc/{ver}/{code}/titles` |

CMS: `POST /api/v1/content/admin/express-entry/import/ircc` (auth+feature). Portal bootstrap is under authenticated `v1` group.

## 4. IRCC JSON fields → DB

Object key: `rounds[]`. One object = one draw.

| JSON | DB `express_entry_rounds` | UI |
|---|---|---|
| `drawNumber` | `round_label`; numeric prefix → `round_number` | Invite # (`91a` kept) |
| `drawName` | `draw_name_source` + mapper | Col “Round”; **not** our type enum |
| `drawDate` | `draw_date` | Date |
| `drawSize` | `invitations_issued` + `invitations_display` | Invitations issued |
| `drawCRS` | `crs_score` | CRS cutoff |
| `drawCutOff` | `tie_breaking_display_*`, `tie_breaking_at` | Tie-break |
| `drawDateTime` | `draw_time_utc` (`HH:MM:SS UTC`; parse even if `UTC` missing / stray `AM`) | Latest-rounds time |
| `drawText2` | program sync via mapper | Programs (general UI label = “General”) |
| `drawNumberURL` | `source_url` | Official MI link |
| `dd1`–`dd18` | `crs_distribution` JSON | Not on invitations table |
| `drawDistributionAsOn` | `distribution_as_on` | Internal |

Detail pages: `invitations-{n}.html` (early, static Results) vs `invitations.html?q={label}` (later, empty until JSON). Do not treat empty `q=` HTML as missing data.

## 5. drawName map (complete)

`general`: `No Program Specified`, `General`

`program` (1 program): `Canadian Experience Class`→CEC, `Provincial Nominee Program`→PNP, `Federal Skilled Trades`→FSTP, `Federal Skilled Worker`→FSWP

`category` (key, version_label):

- french: `French language proficiency (Version 1)` | `French-Language proficiency 2026-Version 2`
- healthcare: `Healthcare occupations (Version 1)` *(discontinued; 6 rounds; now in category dropdown)*
- healthcare_social: `…(Version 2)` | `Healthcare and Social Services Occupations, 2026-Version 3`
- stem: `STEM occupations (Version 1)` *(2026 list exists; 0 draws under a 2026 drawName)*
- trade: `(Version 1)` `(Version 2)` `Trades Occupations, 2026-Version 3`
- transport: `(Version 1)` `Transport Occupations, 2026-Version 2`
- agriculture: `(Version 1)` *(discontinued; 3 rounds; now in dropdown)*
- education: `(Version 1)` *(2026 list = same 5 NOCs)*
- physicians / senior_managers / skilled_military / researchers: `…2026-Version 1` (researchers: 0 rounds)

Unknown `drawName` → import **skip** (logged). Seeder **throws**.

General/category programs from `drawText2` (CEC/FSWP/FSTP/PNP keywords). Default if none: CEC,FSWP,FSTP.

## 6. NOC sources (not the rounds JSON)

| What | Source | File / URL |
|---|---|---|
| Current 2026 occupation tables | IRCC category-based selection (page date 2026-06-22) | https://www.canada.ca/en/immigration-refugees-citizenship/services/immigrate-canada/express-entry/rounds-invitations/category-based-selection.html |
| Historical v1 lists | Reports to Parliament 2023-24 / 2024-25 | snapshot `sources.historical_*` |
| Unit-group EN/FR titles + TEER | Snapshot members | `ee_category_snapshot_2026-06-22.json` |
| Index of titles | StatCan Elements CSV extracts | `noc_2021_v1_index_titles_ee_*.csv` |
| Profile link | ESDC NOC 2021 **Version 1.0** | `https://noc.esdc.gc.ca/Structure/NOCProfile?code={5digit}&version=2021.0` |

French category: NCLC 7; `members=[]`. Internal `noc_version` label `2021.1` = dataset key; ESDC URL always `2021.0`.

Rounds attach `category_definition_id` by mapper `category_version` = definition `source_version_label`. Selecting “current” STEM/education with no matching drawName ⇒ **0 rows** (list still shown). Not data loss.

## 7. Tables (MySQL)

`express_entry_rounds` (+ `express_entry_round_program`)
`express_entry_categories` / `_category_definitions` / `_category_definition_noc` / `_category_occupations` (legacy admin sync of current def)
`express_entry_noc_unit_groups` / `_noc_index_titles`
`express_entry_programs` `express_entry_page_content` `express_entry_import_logs` `express_entry_dataset_revisions`

Portal shows `status=published` only.

## 8. Verify (do this before claiming miss)

1. Compare IRCC row `#` + date + invites + CRS to JSON (`drawNumber,drawDate,drawSize,drawCRS`).
2. Portal `?type=general` should list **all** published rounds (435), same as the IRCC table. Program/category filters are optional subsets.
3. Local: `/express-entry/rounds-history` or CMS rounds (all types).
4. `SELECT round_type, COUNT(*) FROM express_entry_rounds GROUP BY 1;` expect 178/184/73 (storage only).
5. Re-import if feed grew: CMS “Import from IRCC”.

## 9. Known non-issues

- IRCC page details date vs JSON: table body comes from JSON; ignore empty HTML scrape.
- All-program rows (`round_type=general`) show Program = “General”; CEC/PNP/category rows show their own program/draw labels.
- Category `total_invitations` strings on some category rows = CMS copy, not live SUM.
- `healthcare_legacy` category row: unused; v1 healthcare uses key `healthcare`.