Travel traveller resolution: replace string sentinels with -1 / null — PRD¶
Goal: Give every Professional Travel row exactly one of three display
outcomes — a named Headcount person, "Internal other", or "External other" —
via a single centrally-resolved user_institutional_id contract, without
ever overwriting a source/third-party SCIPER just because it doesn't
currently resolve against Headcount.
This supersedes the string-sentinel scheme (__other_internal__ /
__other_external__) shipped in issue #1153
(commit 27619683) and extended in PR #2117.
1. Objective¶
Replace the __other_internal__ / __other_external__ user_institutional_id
hack with a deterministic representation that:
- Displays a named person from Headcount when resolvable.
- Displays "Internal other" for a SCIPER-bearing traveler who isn't in this unit's Headcount roster (wrong unit, not yet synced, or a third-party id Headcount has never heard of).
- Displays "External other" for a traveler with no SCIPER at all.
Works for: API-imported travel (Tableau), existing travel rows, new travel rows from the frontend form, and cases where Headcount hasn't been retrieved/synced yet.
Critical design principle — unchanged from the original PRD:
Never overwrite a source/third-party user_institutional_id merely because
it cannot currently be resolved against Headcount. A travel row imported
with SCIPER 45005 stays 45005 forever, even if unit 42's Headcount has
no record of 45005 — it displays as "Internal other", but the stored value
is untouched.
2. Data model — adapted to this codebase¶
The original PRD assumed a typed, nullable, FK'd integer user_id column.
We have neither a dedicated column nor a FK: user_institutional_id
lives inside DataEntry.data, a JSONB blob, as a free-text SCIPER string.
Real EPFL SCIPERs are 6-digit numeric strings, so "-1" can never collide
with one.
Adapted sentinel scheme (decided in brainstorming, 2026-08-14):
Stored data.user_institutional_id |
Meaning |
|---|---|
JSON key present, value null |
Explicit External other |
"-1" (string) |
Explicit Internal other |
| Any other string | Source/institutional SCIPER — resolve against Headcount, scoped to (carbon_report_module_id, sciper) |
- No new column. No
traveller_typefield. No syntheticUserrows. nullis JSON-null with the key present, not an absent key — matches how every other nullable field on these DTOs already serializes, and survives round-tripping through Pydantic (str | None = None) cleanly.- No FK exists on
data_entries.data(it's JSONB), so §12 of the original PRD (FK rejection risk) doesn't apply — nothing to inspect or weaken.
Central constant, defined once per language and reused everywhere (no scattered literal comparisons):
# backend/app/services/data_ingestion/api_providers/professional_travel_api_provider.py
TRAVELER_OTHER_INTERNAL = "-1"
TRAVELER_OTHER_EXTERNAL = None
// frontend/src/constant/module-config/traveler-options.ts
export const TRAVELER_OTHER_INTERNAL = "-1";
export const TRAVELER_OTHER_EXTERNAL = null;
3. Traveller resolution — unchanged business logic¶
if user_institutional_id is None:
return EXTERNAL_OTHER
elif user_institutional_id == TRAVELER_OTHER_INTERNAL: # "-1"
return INTERNAL_OTHER
elif headcount contains (carbon_report_module_id, user_institutional_id):
return HEADCOUNT_PERSON # display that member's name
else:
return INTERNAL_OTHER
Unit scoping note: the original PRD keys Headcount matching on
unit_id. This codebase already scopes strictly tighter — on
carbon_report_module_id, which pins both unit and report year. That's
already correct (a travel row must match the same report's Headcount
snapshot, not just "some year for this unit") and requires no change.
4. Resolution matrix (canonical — tests must cover every row)¶
user_institutional_id |
Matching Headcount (carbon_report_module_id, sciper) |
Result |
|---|---|---|
null |
N/A | External other |
"-1" |
N/A | Internal other |
"45005" |
Yes | Headcount name |
"45005" |
No | Internal other |
"12345" |
Yes | Headcount name |
"12345" |
No | Internal other |
"12345" |
Exists only for a different carbon_report_module_id |
Internal other |
If an implementation detail conflicts with this table, the table wins.
5. Existing code this plugs into¶
Read closely during brainstorming (2026-08-14) — do not re-derive, build on these:
backend/app/repositories/data_entry_repo.py:870-897— the correlated scalar subquery joiningMemberEntry.data["user_institutional_id"]againstDataEntry.data["user_institutional_id"], scoped tocarbon_report_module_id. No logic change needed. SQL's three-valued logic (NULL = NULL→ not true) means an External-other travel row can never spuriously match a Headcount member who also has no SCIPER yet (issue #951 made Headcount SCIPER optional too) — this safety is implicit in using real SQLNULL, not accidental; comment it in place.data_entry_repo.py:1174-1175— only setsenriched_data["traveler_name"]on a match; no match → key absent, response falls through to the rawuser_institutional_id. This is exactly the "no backend special case" mechanism both sentinels rely on.data_entry_repo.py:1298,1358-1378(get_professional_travel_trips_map) — no Headcount join;traveler_idon map legs defaults to the raw SCIPER, andtid = traveler_id or ""coercesNoneto""on this endpoint only. This matters for §7 below (frontend map legend).carbon_report_module.py:537-619(list_headcount_members) — dropdown source only, not row-display resolution; unaffected.professional_travel_api_provider.py(PR #2117) — blank/None/whitespace SCIPER on ingest currently maps to a string__other_external__; must be repointed to realNoneunder this scheme. A missing SCIPER from the feed is always External (no id at all), never Internal.app/modules/professional_travel/data_entries.py—ProfessionalTravelPlaneHandlerCreate.user_institutional_id/TrainHandlerCreate.user_institutional_idare currentlystr(non-optional); must becomestr | Noneto accept explicit External. Traveler stays Create-only (app/core/data_entry_permissions.py:91) — this PRD does not reopen editing it post-creation.-
Frontend, already shipped (#1153, commit
27619683) —frontend/src/constant/module-config/traveler-options.ts(resolveTravelerName+ the two constants),HeadcountMemberSelect.vue(the dropdown),ModuleTable.vue(table cell resolution),ModuleCharts.vue(trips-map legend),professional-travel.ts/planner-module-config/index.ts(both only re-export the constants, no hardcoded sentinel strings — confirmed by repo-wide grep). Swapping the two constants' values alone propagates everywhere except two real bugs the swap would introduce: -
resolveTravelerName(traveler-options.ts:42) doesif (userInstitutionalId == null) return '-';before the External check. A loose== nullmatches bothundefinedandnull— once External isnull, this swallows it and always renders'-'instead of the label. Fix:undefined(no data loaded yet) →'-';null(explicit External) → resolved label. Use===, not==. ModuleTable.vue:1352has the identical== nullpre-check ahead of callingresolveTravelerName. Same fix.ModuleCharts.vue:375-379(travelerSentinelLabels()) seeds aMap<string,string>keyed by the raw sentinel value for the trips-map legend — but that map is looked up bytraveler_id, which (per thetid = traveler_id or ""coercion above) arrives as""for External-other legs, nevernull. Key that legend entry under""explicitly, with a comment explaining the asymmetry with the JSON-row path. Internal ("-1") needs no change — it survives theor ""coercion untouched (it's a truthy string).HeadcountMemberSelect.vue'sSelectOptioninterface (found during plan-writing self-review, not caught in the original brainstorming pass): it typesvalue: string, but the dropdown already pushes{ value: TRAVELER_OTHER_EXTERNAL }— once that constant isnull, this is a type error, not a runtime bug (theq-selectbinding is alreadystring | null). Widen the interface tovalue: string | null.
6. Migration¶
Data-only Alembic migration, mirroring
2026_07_22_1444-2c7f5cf1c9de_migrate_mice_research_facility_type_to_.py's
jsonb_set pattern:
-- upgrade
UPDATE data_entries SET data = jsonb_set(data::jsonb, '{user_institutional_id}', '"-1"')::json
WHERE data->>'user_institutional_id' = '__other_internal__';
UPDATE data_entries SET data = jsonb_set(data::jsonb, '{user_institutional_id}', 'null')::json
WHERE data->>'user_institutional_id' = '__other_external__';
jsonb_set(..., 'null') writes an actual JSON null with the key present
(matches §2's decision), not an absent key. Downgrade is the exact inverse
(swap the two WHERE values). Real/unresolved SCIPERs (e.g. 45005) are
never touched — the WHERE only ever matches the two known legacy literals.
No FK/schema change — nothing to weaken (§12 of the original PRD doesn't apply to a JSONB field).
7. Validation & constraints¶
TRAVELER_OTHER_INTERNAL = "-1"is the one reserved sentinel; document it at the constant definition in both languages.- No other negative-looking values (
"-2","-3", …) carry any special meaning — they'd just be treated as an unresolved source SCIPER (→ Internal other via theelsebranch), which is correct and requires no extra guard. - Existing valid source SCIPERs (
"45005","12345", …) continue to work unchanged.
8. Headcount-unavailable behavior¶
Two of the three outcomes never need Headcount at all:
user_institutional_id = null → External other (no lookup)
user_institutional_id = "-1" → Internal other (no lookup)
user_institutional_id = "45005" → needs Headcount; "Internal other" until resolved
The frontend must not block or blank out a row's traveler cell merely
because the Headcount roster hasn't loaded yet — this is already how
resolveTravelerName is structured (memberName is an optional
already-looked-up value, not a required dependency), and remains true after
the §5 fixes.
9. Tests (resolution matrix + regressions)¶
Backend
- Extend
test_professional_travel_api_provider.py: blank/None/whitespace SCIPER →None(not a string sentinel) — update the assertions already changed in PR #2117 to checkis Noneinstead of an__other_external__string. - New
data_entry_repotest driving the full §4 matrix against the real correlated subquery (sqlite, matching this repo's existing test DB):null→ no match;"-1"→ no match; matching SCIPER+module → name; matching SCIPER, differentcarbon_report_module_id→ no match; unknown SCIPER → no match. - DTO test:
ProfessionalTravelPlaneHandlerCreate/TrainHandlerCreateacceptuser_institutional_id: null.
Frontend
resolveTravelerNameunit tests:undefined→'-';null→ External label;"-1"→ Internal label; unresolved SCIPER (nomemberName) → Internal label; resolved SCIPER (memberNameset) → that name.HeadcountMemberSelect.vue: submits"-1"for Internal,nullfor External, the realinstitutional_idfor a picked member; existing"-1"/nullvalues on load pre-select the right option.ModuleCharts.vuetrips-map legend: External-other leg (traveler_id "") resolves to the External label via the""-keyed entry.
Migration
__other_internal__→"-1";__other_external__→ JSONnull(key present); untouched values ("45005", real SCIPERs) unchanged; downgrade is the exact inverse.
10. Acceptance criteria¶
- A user can select a Headcount person, Internal other, or External other from the Travel form.
- Internal other persists as
user_institutional_id = "-1". - External other persists as
user_institutional_id = null(key present). - Third-party SCIPERs (e.g.
45005) are preserved unchanged whether or not they resolve. - An unresolved SCIPER displays as Internal other; a SCIPER matching
Headcount for the same
carbon_report_module_iddisplays that person's name; a SCIPER matching Headcount only for a different module/unit/year does not resolve to that person. - Legacy
__other_internal__/__other_external__values are migrated; no other values are touched. - No
traveller_typefield, no syntheticUserrows. - Resolution works with Headcount unavailable (
null/"-1"need no lookup at all). - Full resolution matrix (§4) is covered by automated tests, backend and frontend.
11. Out of scope¶
- Making the traveler field editable after creation (stays Create-only,
per existing
data_entry_permissions.py:91— not reopened here). - Any change to
get_professional_travel_trips_map's per-leg name resolution beyond the""-keyed legend fix (§5.3) — it already doesn't join Headcount and this PRD doesn't add that.