ADR-00019: Bulk Import is a Distinct Onboarding Source with Orthogonal Validity and Admin-Owned Repair

Approval Status

Status flow: Proposed → Accepted → Reviewed → Approved (or Rejected), or Not Required. A superseded ADR keeps its file; only its status changes to Superseded by NNNNN.

Governance body	Status	Last update
CTN Project Team	Proposed	2026-06-25
CTN Technical Advisory Board	Pending	—
CTN Steering Committee	Not Required	—
BDI Conformance Team	Pending	—
BDI Framework Team	Pending	—

RdN: Although we are developing this currently for CTN, this feature will also come in handy (a re-usable) for other initiatives. If BDI Conformance Team approves we can submit it as RFC for the BDI Framework.

Context and Problem Statement

The first bulk-import implementation (TDR-0003) reused the self-service intake path verbatim: BulkImportService looped each spreadsheet row through OnboardingRequestService.save(req, adminSub), so every row got the same validate-or-reject, account provisioning and set-password mail as an anonymous self-service submission. The steer then was “map only what fits, add no new rules.”

Operational reality has moved past that. Bulk import is now understood as a genuinely different workflow from self-service intake, not a thin wrapper over it:

• Failed rows must survive. Today a row that fails mapping or validation is dropped into a transient ImportResultResp.failed[] and lost. The association admin needs those rows persisted so they can be fixed later — the whole point of an admin bulk channel is that the admin, not the applicant, repairs bad data.
• The applicant is not in the loop at creation. A bulk row should provision the applicant’s Keycloak account but not email them yet; first contact (the set-password mail) is a deliberate admin action, sent when the admin is ready.
• The admin owns the row, not the requestor. Bulk rows are edited and corrected by an admin acting namens de aanvrager; the self-service rule “only the requestor edits, only in {Submitted, ChangesRequested}” does not apply.
• Uploads must be separable. Multiple bulk uploads need to be told apart.
• Duplicates will be grouped later. Post-enrichment deduplication (COMP-19) will group requests; before enrichment, “duplicate” means same Identifier URI.

The central design tension is how to represent a failed/incomplete row. The existing model fights it on two fronts: identifierUri is deliberately nullable = false (TDR-0002, ADR-00011), yet the most common failed row is one whose identifier could not be minted; and the lifecycle (ADR-00012) is a pure flat edge table whose openStates() is reused verbatim as the duplicate-guard scope, so any new “failed” state ripples into duplicate detection and the partial unique index.

A future reader needs to know why identifierUri became nullable after being deliberately required, why there is no Failed status despite “failed rows,” and why bulk no longer shares the self-service create path.

Considered Options

Option 1: Failed as a new lifecycle Status (same entity, relaxed invariants)

• Description: Add Failed to OnboardingRequest.Status; relax identifierUri to nullable; wire Failed into the edge table (admin repairs it forward into the normal flow, discards it to Rejected).
• Pros: One table, fix-in-place, no promotion step.
• Cons: Encodes validity (a data-completeness property) as a lifecycle position — a category error. Failed is lifecycle-open, so it leaks into openStates() and thus the duplicate guard and the partial unique index, forcing those two notions to be split apart. Still loses the original un-mapped cell data.

Option 2: Reuse ChangesRequested for incomplete bulk rows

• Description: Land incomplete bulk rows directly in the existing ChangesRequested state.
• Pros: No new status at all.
• Cons: Same category error as Option 1, merely hidden — valid rows would sit in Submitted, invalid ones in ChangesRequested, so the lifecycle position again encodes validity. Overloads ChangesRequested’s meaning (an admin reviewed and bounced it back, with a decision note the requestor reads) onto “born incomplete, never reviewed.” Still needs nullable identifier and admin-scoped editing.

Option 3: Separate import-staging table, promote valid rows

• Description: Failed (and raw) rows persist as staging records holding raw cells + per-row errors + batch id; the admin fixes them in staging; on success a fully-valid OnboardingRequest is promoted out.
• Pros: OnboardingRequest stays well-formed; raw data preserved; batches and pre-enrichment dedup attach naturally to staging.
• Cons: A second edit surface and a promotion/copy step; duplicated data; more moving parts than the demo warrants.

Option 4: Orthogonal Validity axis (chosen)

• Description: Keep the lifecycle topology unchanged (no Failed). Add a second, orthogonal axis — Validity (COMPLETE / INCOMPLETE) — recomputed on every edit. All bulk rows enter at Submitted; incomplete ones carry INCOMPLETE. Relax identifierUri to nullable. Guard approve by COMPLETE — the “validate before accept” gate. This is the user’s “hierarchical state machine” intuition expressed as parallel regions, the only form that does not reverse the pure-flat-core decision of ADR-00012.
• Pros: No category error — validity and lifecycle position are independent, as they truly are. Lifecycle topology and openStates() are untouched, so the duplicate guard and partial unique index need no decoupling. An edit moves validity, never status, so the rule “Enrichment is not a Lifecycle verb” still holds.
• Cons: Relaxes the deliberately-required identifierUri invariant for the whole population (mitigated by the approve-guard, which restores it before acceptance). Validity is a derived property that must be stored to be queryable.

Decision Outcome

Chosen option: Option 4 — bulk import becomes a distinct onboarding Source with an orthogonal Validity axis and admin-owned repair, sharing the lifecycle but not the create path of self-service intake.

Rationale

Option 4 is the only one that does not commit the category error of encoding data completeness as a lifecycle position. Because validity is modelled as a parallel region rather than a status, the prized pure-flat lifecycle core (ADR-00012) and its duplicate-guard reuse survive completely unchanged — a large, error-prone area (openStates(), the soft warning, the partial unique index, the consistency test) is left untouched. Option 3 keeps invariants strongest but pays with a staging table and a promotion step disproportionate to the demo. Options 1 and 2 are cheaper to type but bake in the conflation this ADR exists to avoid.

The decided package

1. Source axis. A persisted enum Source { SELF_SERVICE, BULK_IMPORT } on every Onboarding Request. Explicit, not inferred from createdBy presence (a future admin-keyed single entry would also carry a createdBy). It is the spine the divergent behaviour branches on.
2. Orthogonal Validity. COMPLETE / INCOMPLETE, recomputed on every edit; identifierUri becomes nullable; approve is guarded by COMPLETE. No Failed status. Self-service rows are always COMPLETE (intake rejects invalid input before persistence); only BULK_IMPORT rows can be INCOMPLETE.
3. Distinct bulk create path. Bulk no longer reuses save() unchanged. It (a) provisions the Keycloak account via ensureAccount but withholds triggerSetPassword; (b) never rejects a row — a row that fails mapping or validation is persisted with whatever fields mapped (identifier possibly null) and flagged INCOMPLETE; only a whole-file failure (non-.xlsx, missing header) hard-fails the upload (400, nothing persisted); (c) stamps source = BULK_IMPORT, batchId, createdBy = adminSub, status = Submitted; (d) treats an existing active participant for the identifier as a per-row flag, not a thrown error. The COMP-15 soft duplicate warning still applies when an identifier is present.
4. Invitation. The set-password mail for a bulk row is sent by a separate, idempotent admin action, allowed only when COMPLETE, recorded by an invitedAt timestamp. It is not a lifecycle verb (changes neither status nor the edge table). For MVP it is activation-only — it does not hand editing to the applicant; the admin keeps control.
5. Admin-owned editing. BULK_IMPORT rows are editable by any admin (not only the importer) in any open state, the repair target being identifierUri (which re-derives intakeCountry and the processor, per TDR-0003’s scheme→country mapping). applicantEmail is editable only before invitation; status stays behind the verbs; the supplied address country is never auto-overwritten — a country/identifier mismatch is a GUI advisory, not a hard rule.
6. Separable uploads. A batchId UUID stamped on every row of one upload (self-service rows leave it null); sort/group by it, with createdAt for time. No first-class batch entity for MVP and no batch-wide operations; metadata can attach to the id later.

Consequences

• Positive: lifecycle core, duplicate guard and partial unique index untouched; validity and lifecycle stay independent; failed rows are no longer lost; bulk and self-service diverge cleanly along the Source axis; account-to-request link stays honest (email locked post-invite).
• Negative: identifierUri is now nullable for the whole population (the deliberate TDR-0002/ADR-00011 invariant is restored only at the approve gate, not by the column); a second create path to maintain; validity must be stored to be queryable.
• Neutral: the German court-code / scheme→country derivation remains the TDR-0003 shared lookup; raw un-mapped cells are not preserved for MVP (the admin cross-references the original spreadsheet by row number + Bron/Bron-klantcode).

Deferred (explicitly out of scope)

• Deduplication grouping → COMP-19. Pre-enrichment rule: duplicate == same Identifier URI; grouped in the admin panel. Post-enrichment (KvK etc.) grouping is COMP-19’s concern.
• Validity storage mechanism (PostgreSQL generated column vs maintained boolean) → follow-up TDR.
• Four-eyes split (one admin enriches, another validates) — possible consortium requirement; the “any admin, any open state” rule is the MVP placeholder.
• Post-invite email editing for bulk; invitation-as-handoff to the applicant; true cross-country re-dispatch when the identifier scheme changes; raw-row provenance snapshot.

Implementation Plan

1. Phase 1: schema — source, batch_id, invited_at, validity marker; make identifier_uri nullable (Liquibase, TDR-0001). Distinct bulk create path on OnboardingRequestService / BulkImportService (withhold mail, persist-not-reject, compute validity, stamp markers, per-row active-participant flag). approve guard on COMPLETE. Admin Invitation action (triggerSetPassword + invitedAt, idempotent, COMPLETE-gated). Admin-scoped editing of bulk rows incl. identifierUri. Admin-panel: incomplete filter, batch grouping, country/identifier mismatch advisory.
2. Phase 2: validity-storage TDR; COMP-19 deduplication grouping.
3. Phase 3: four-eyes, post-invite email edit, invitation handoff, cross-country re-dispatch, raw-row provenance — as separate tickets if pulled in.

Compliance and Validation

• Security review completed (admin-gated import + invitation; email-edit locking)
• Performance impact assessed
• Integration impact evaluated (Keycloak provisioning timing; mail)
• Documentation updated (docs/ai/CONTEXT.md glossary entries added)
• Stakeholder approval obtained

Related Documents

• ADR-00012 — onboarding lifecycle & the pure flat transition core this ADR deliberately leaves unchanged. (Note: much code/doc text cites “ADR-00014” for the lifecycle — a known numbering drift; the lifecycle decision is file 00012.)
• ADR-00013 — applicant account provisioning, whose mail this ADR defers for bulk.
• ADR-00011 — URI-based identifier scheme; the identifierUri invariant relaxed here.
• TDR-0002 — stores the identifier as an Identifier URI (the now-nullable field).
• TDR-0003 — the prior bulk import (row→request, scheme→country) this ADR supersedes the create-path semantics of.
• REQ-20260625-COMP — the driving requirement.
• COMP-24, COMP-119 (existing bulk import), COMP-19 (deduplication) — Jira.

Status History

Date	Status	Notes
2026-06-25	Proposed	Initial proposal, from the grill-with-docs design session.

Status flow: Proposed → Accepted → Reviewed → Approved (or Rejected). A superseded ADR keeps its file; only its status changes to Superseded by NNNNN.

---

ADR format based on Michael Nygard’s template with CTN-specific enhancements