Import Pipeline

The shared pipeline imports CSV or XLSX into Students, Teachers, Staff, and Rooms. Domain services own lookup, validation, classification, and persistence; the pipeline coordinates them. Imports target the tenant's active academic year.

1. Source map

Concern	Source
Orchestration and row limit	import-pipeline.ts
Callback and descriptor types	import-pipeline.interfaces.ts
CSV/XLSX parsing	import-file-parser.ts
Dates and validation helpers	import-helpers.ts
Classification and sweep helpers	import-duplicate-checker.ts
Column metadata	import-columns.ts
Shared result envelope	import-summary.dto.ts
Person importer	students.service.ts
Non-person importer	rooms.service.ts
Cross-year identity	resolve-person-uuid.ts

2. Pipeline and ownership

runImportPipeline<TRow, TLookup, TResult> takes the file, tenant, column schema, four callbacks, and mandatory yearOptions containing the Prisma year delegate and target year ID. It checks year writability before invoking callbacks.

Stage	Responsibility
Parse	Produce rows with normalized headers; reject empty files and files above 10,000 rows.
Validate headers	Reject missing required columns before processing rows.
Load lookups	Load tenant/year reference data and existing records once.
Validate rows	Run declarative rules, then domain checks; aggregate failures.
Classify	Assign each row an insert, update, or skip verdict.
Persist	Apply verdicts and any setup sweep atomically; return the shared summary.

The pipeline does not implement entity transactions or cascades. The persistence callback owns those operations and joins the request's tenant transaction. See Multitenancy for transaction ownership and Setup Wizard for setup state.

The service resolves isSetup before classification and uses the same value for validation and persistence. Person importers also receive the acting user's identity for sweep protection. Student imports receive the full RecordAccessContext so enrollment writes retain their author.

3. Parsing and field descriptors

CSV/plain MIME types use PapaParse; other MIME types use the XLSX parser. XLSX reads the first worksheet. Headers are trimmed and lowercased; fully blank rows are skipped.

SheetJS extracts errors as empty strings, numbers/booleans as text, and text/rich-text/hyperlink/formula cells through their flattened values. Date-formatted serial cells become ISO calendar dates using SSF.parse_date_code and the workbook's 1900/1904 epoch. Avoid constructing a JavaScript Date merely to extract a spreadsheet calendar day: timezone conversion can change that day. Corrupt files and files without usable headers produce a controlled validation error.

The xlsx dependency comes from the CDN tarball declared in package.json and pinned by the lockfile. Dependency mirrors must support that source.

Text dates

parseImportDate accepts consistent /, ., or - separators:

• A four-digit leading component selects year-month-day.
• Otherwise a four-digit trailing year is required. A component greater than 12 identifies the day.
• Ambiguous day/month pairs default to day-first.

It rejects two-digit years, datetime components, textual months, mixed separators, and impossible calendar dates. It constructs a UTC date and checks components to detect overflow. Prefer ISO dates in templates to avoid ambiguity.

Declarative rules

Each importer declares fields in src/<domain>/constants/import-schema.ts. deriveImportColumns derives all and required columns from this schema.

Rule	Contract
required	Value must be present and nonblank.
maxLength	Text must fit the supplied limit.
date	Value must pass parseImportDate.
pattern	Value must match the regex; an optional error key selects the reason.
set	Normalized value must belong to the allowed set.
custom	Callback returns an error key, or null on success.

Descriptors can supply static or lazy allowedValues. Run validateImportRows before domain hooks, then combine STANDARD_CODE_REGISTRY with the entity's extra registry in buildValidationResult. Dynamic allowed values, such as configured grades or departments, come from the loaded tenant data.

Use column:rule error keys. A grade/department pair should report the field that fails: an unknown department belongs on the department column; a known grade unavailable in a valid department belongs on the grade column.

4. Route and response contracts

Import routes use the entity's create action, @RequireRoles('admin'), @TenantTxBudget('IMPORT'), and @AggregateResponse(). Uploads use FileInterceptor('file', ...) with the shared size limit and AssertFilePresentPipe. Copy the domain's controller signature rather than adding an academic-year query argument: these routes resolve the active year.

The aggregate marker is required because summary keys are not scope names. Summary DTOs extend AggregateResponseDto through ImportSummaryDtoOf. Changes to summaries require reviewing their visibility under RBAC.

The envelope is ImportSummary<TItem, TSkipped, TDeleted>; skipped/deleted types default to person shapes and can be specialized for rooms.

Field	Meaning
importColumns?	Required and optional template headers.
count, items	Population after import and a preview of up to five records.
created, updated, deleted	Counts for this upload.
skippedItems	Full list of skipped input rows, including their row numbers.
deletedItems	Full list of swept records; these have no input row number.
rolloverAppliedAt	Active year's rollover timestamp; setup student imports reset it.

Parser/empty-file/row-limit failures use 400 VALIDATION_FAILED. Header and row failures use 422 IMPORT_VALIDATION_FAILED. Account conflicts can produce 409 during persistence; a failed import has no partial success.

Import errors internally carry code, column, row ranges, reason, and allowed values. The error renderer turns each wire leaf into bilingual messages; internal discriminators are not retained. A new reason needing distinct wording needs a message-catalog entry. See Error Handling.

5. Classification and setup replacement

People match on normalized first name, last name, and date of birth within the target year. Rooms supply name-based rowKeyOf/recordKeyOf functions. Email and room code are uniqueness constraints, not identity keys.

Verdict	Condition
Insert	No existing identity match.
Update	Setup is active, an identity matches, and mapped values differ.
Skip	Unchanged match, repeated input identity, or any existing match after setup.

In-file repeats use the first occurrence. importDataDiffers reuses the row-to-create mapping: a blank mapped to undefined means leave unchanged; a blank mapped to null means clear. Domain comparisons also cover links such as referents or teacher departments.

During setup, the file describes the entity's whole active-year population. computeSweepTargets identifies existing records absent from it. Persistence deletes these records before applying inserts/updates in the same transaction. After setup, imports only insert or skip; there is no update or sweep.

Sweep rules:

• Define the sweep configuration once and share it between validation and persistence.
• Protect the acting admin's linked person record via protectedUserId.
• Exclude CANTEEN rooms from import-managed sweeps.
• Use the domain's deleteRecordWithinTx teardown and post-commit queue. This shares record cleanup with CRUD deletion, including the membership release: a swept row's account is ended, kept or switched off, and sweeping the last admin's row fails the upload with LAST_ADMIN_REQUIRED. It does not imply that every outer remove hook runs.
• Database restrictions on historical attendance, grades, and other relations still apply. Setup state does not override referential integrity.
• Return the deleted records in deletedItems; post-setup this is empty.

Validation uses survivingRecords(rows, sweepConfig), so an email/code held only by a record scheduled for deletion does not produce a false conflict. It must use exactly the same protected population as the actual sweep.

An identity-preserving reimport can reuse its own email. A different person claiming that email fails validation. If an apparently unique email conflicts, inspect the identity columns, especially ambiguously formatted birth dates.

6. Account binding and atomicity

Person inserts check the final mapped email with InvitationsService. The prior-year resolver runs first and passes a valid carried User binding, the carried personUuid and the import year into the account check, so the person's own earlier rows never hold the address. Email agreement alone cannot establish a binding.

Setup email updates lock and reread the actual person, retain the acting admin's account protection, and check a nonempty changed email before updating or resetting access. Clearing an optional email follows its reset behavior without an occupancy check.

Student referent creation reuses existing Referents and checks the deduplicated set of missing emails before creating them. Skipped rows do not create referents.

An address held by another local account, or by another person under the person-row rule, produces LOGIN_EMAIL_IN_USE, naming the holder, with no field; an invalid carried binding produces INVITATION_ACCOUNT_CONFLICT. A same-year row of the imported type is normally reported earlier by the validation-stage email check. A switched-off account still bound to a closed row occupies its email; one whose last row was swept earlier in the same transaction has ended and does not (membership lifecycle). These conflicts roll back the entire upload, including sweeps, links, access changes, and setup timestamps. External effects run only after commit.

Import success is not an account reservation and does not guarantee later invitation eligibility.

7. Cross-year identity

resolvePriorYearPerson searches the same tenant outside the target year, first by tax code and then by name plus birth date. A match supplies the existing personUuid; absent a match, creation uses a fresh UUID.

The resolver also supplies the prior bound User. carriedUserId retains that binding only when the imported email agrees with the bound User's email, ignoring case and surrounding whitespace. An identity match alone cannot attach a login.

Run this on the insert branch using the transaction client. Updates preserve the existing record's identity and binding; within-year classification is separate from cross-year lookup.

8. Rooms and column discovery

Rooms use the same pipeline with name identity and code uniqueness. They have no person UUID carry or import completion timestamp. CANTEEN rows are rejected because their lunch-shift configuration uses the manual room flow.

maximum_capacity must be an integer of at least one. Keep isValidRoomCapacity, import descriptors, setup validation, and DTO constraints aligned.

Import steps in the wizard expose lean importColumns: { required, optional } through their summary. ROOMS is a form step and supplies the same metadata through getSetupSummary.

Standalone GET /students/import/columns, /teachers/import/columns, /staff/import/columns, and /rooms/import/columns return the richer ImportColumnsSchemaDto. Their permission gates match the corresponding POST import.

describeImportColumns derives name, required, maximum length, date/email format, and static allowed values from descriptors. Domain hooks overlay active-year grade, department, or room-type names. A metadata request without an active year returns empty year-dependent choices; global room types can still be resolved.

For allowed values, absent means unconstrained text, an empty array means a constrained column with no configured choices, and a nonempty array supplies choices. Boolean/number columns implemented through custom rules have no separate format metadata; clients must not infer an unsupported schema field.