IPEDS Data Bug?

[lionelle]

Not sure if this is a bug, this prompt below is more about verifying the data.

---

Bug: Verify IPEDS CIP codes are correctly associated between source CSV and database (e.g. 11.0101 vs 11.0701)

Summary

When pulling completion/program data, the system has returned CIP code 11.0101
("Computer and Information Sciences, General") in cases where the correct value
for the institution appears to be 11.0701 ("Computer Science") — observed for
Northeastern University (UNITID 167358). These are two distinct, valid CIP
codes that both exist in our seed taxonomy, so this is not a formatting or
normalization artifact: normalize_cip() only inserts a decimal point into a
6-digit string and would never rewrite 0701 → 0101. That rules out the parse
layer and points instead at data association — i.e. whether the cip_code
stored on a given (unitid, award_level, major_num, year) row in the
completions table actually matches the CIPCODE value on the corresponding
row of the source IPEDS C{year}_A.csv, and whether the human-readable title we
surface for a code is the title bound to that same code in cip-seed.sql.
Possible causes to rule out, in order of likelihood: (1) the institution
genuinely reports completions under 11.0101 and the discrepancy is an
expectation mismatch rather than a bug; (2) a code↔title labeling mix-up where
the right code is stored but a neighboring title is displayed (or vice-versa);
(3) a stale/partial import where an older year's row was not overwritten because
the upsert conflict key differs; or (4) an upstream IPEDS source-file
discrepancy. We should confirm which by diffing the raw CSV against the stored
rows for a known institution.

Debug references

CSV → DB association path (all in src/core/database/ipeds/ingest.rs):

• is_relevant_cip() — CS family filter (11.*, 30.7001, 30.7099) — ingest.rs:64
• Raw CIP read from CSV column CIPCODE — ingest.rs:437
• build_completion() stores normalize_cip(raw_cip) into cip_code — ingest.rs:539
• normalize_cip() (6-digit → dotted, else passthrough) — ingest.rs:622
• Column resolution via find_col / require_col (header-name matching across survey years) — ingest.rs:129, ingest.rs:138,
  ingest.rs:407-409
• Completions upsert conflict key (unitid, cip_code, award_level, major_num, year) — ingest.rs:52, ingest.rs:456-474

Schema / reference data:

• completions.cip_code TEXT and unique constraint — docs/database/schema.sql
• cip_codes lookup (code → title) — docs/database/schema.sql, seeded by docs/database/cip-seed.sql (contains both ('11.0101', 'Computer and Information Sciences, General') and ('11.0701', 'Computer Science'))

Read/query path (where the value is surfaced to the user):

• get_institution_completions / get_completion_demographics completion query — src/mcp/tools/completions.rs:~654
• search_cip_codes (prefix/title lookup) — src/mcp/tools/cip_codes.rs
• get_degree exact (unitid, cip_code, catalog_year) match — src/mcp/tools/degrees.rs:~277
• Tool registrations — src/mcp/server.rs:408-525

Validation prompt (paste into the agent / MCP client)

University (UNITID 167358) for completions year 2024.

1. Call search_cip_codes for prefix 11. and confirm both 11.0101
   ("Computer and Information Sciences, General") and 11.0701
   ("Computer Science") resolve to their correct titles — i.e. the code↔title
   binding is not swapped.
2. Call get_institution_completions for unitid 167358, year 2024, and list
   every cip_code returned in family 11.* together with its total
   completions. Note whether 11.0101, 11.0701, or both appear.
3. For each 11.* code returned, state the exact cip_code, award_level,
   and major_num so the row's full unique key is visible.
4. Report: does the database value match what the user expects (11.0701), and
   is the title we display the one bound to that code in the seed table?

Ground-truth cross-check (CSV vs DB)

Confirm the DB value equals the source CSV value for the same key. Replace the
path with the imported C2024_A file (db exec-sql / ipeds-import live in
src/cli/commands/db.rs):

# 1. Raw value(s) straight from the source IPEDS CSV for Northeastern, family 11
grep -E '^167358,' C2024_A.csv | awk -F',' '$2 ~ /^11/ {print $1","$2","$3","$4}'
#                                            ^UNITID  ^CIPCODE,AWLEVEL,MAJORNUM (verify col order against header)

# 2. What the database actually stored for the same institution/year
cargo run -- db exec-sql --file /tmp/check.sql
# /tmp/check.sql:
#   select unitid, cip_code, award_level, major_num, year, total
#   from completions
#   where unitid = 167358 and cip_code like '11%' and year = 2024
#   order by cip_code, award_level, major_num;

# 3. Confirm the title binding is correct in the lookup table
#   select cip_code, title from cip_codes where cip_code in ('11.0101','11.0701');

If step 1 (CSV) and step 2 (DB) agree but differ from the user's expectation,
this is an expectation mismatch (close as not-a-bug). If they disagree, the
association bug is in the import path (ingest.rs:437 → :539). If the codes
match but the title is wrong, the bug is in the cip_codes seed/lookup.

Acceptance criteria

- [ ] Reproduced or refuted: for UNITID 167358 / year 2024, the stored cip_code
values match the raw CIPCODE column of the source CSV row-for-row.
- [ ] Confirmed cip_codes titles are bound to the correct codes (no 11.0101↔11.0701 swap).
- [ ] If a mismatch exists, root cause identified at one of: CSV column mapping
(find_col/require_col), normalize_cip, upsert conflict key, or seed data.
- [ ] Regression test added covering a known institution's CIP association.

---

Two notes on the references so you can adjust before posting:
- **UNITID 167358** for Northeastern is taken from an example in `src/mcp/tools/degrees.rs`; worth a quick confirm against `institutions`
before posting.
- The CSV `awk` column order (`$3`/`$4` for `AWLEVEL`/`MAJORNUM`) assumes the standard `C_A` layout — the importer resolves columns by
header name (`find_col`), not position, so verify against the actual header row when you run the cross-check.