Import Pipeline¶

The import pipeline provides a generic, reusable mechanism for uploading CSV or XLSX files and turning them into database records. Each domain entity supplies column schemas, field descriptors, and four callbacks; the pipeline handles orchestration, header validation, error aggregation, and reporting.

1. Overview — 6-Stage Flow¶

parse file → validate headers → load lookup data → validate rows → classify rows → create records

Stage	Responsibility
parse file	Parse CSV or XLSX buffer into raw `Record<string, string>[]` rows. Rejects empty files and files exceeding 10,000 rows. XLSX cells are not always plain strings — `cellToString` (SheetJS-based) unwraps each cell (error cells → `''`; date-formatted number cells → ISO `YYYY-MM-DD` via `SSF.parse_date_code`; numbers/booleans → raw value as text; text/rich-text/hyperlink/formula → SheetJS's flattened display text `cell.w`). Skipping this silently drops a visible value → a spurious `FIELD_REQUIRED` (the symptom that surfaced once `tax_code` became mandatory). See §3 for the full SheetJS rationale.
validate headers	Confirm that all required columns are present. Missing required columns produce an `HEADERS_MISSING` error immediately — no row processing occurs.
load lookup data	Fetch tenant-specific reference data needed downstream — grades/departments and the existing records for the target year (loaded once, reused by validation + classification).
validate rows	Run the declarative field-descriptor pass then entity-specific hooks (including identity-aware email-uniqueness). Collects all errors before reporting.
classify rows	Resolve each row to an `ImportRowVerdict` — `insert`, `update`, or `skip` — by matching on identity (firstName + lastName + DOB) within the target year. See §6.
create records	Apply the verdicts inside a `prisma.$transaction()`: insert new rows, update matched rows in place (setup-only upsert), skip the rest.

2. Using `runImportPipeline`¶

runImportPipeline is the sole orchestrator. Entity services call it from their importFromFile() method after two pre-flight steps:

Resolve the target academic year (fall back to the tenant's ACTIVE year if none is supplied).
Resolve isSetup (await this.isSetupInProgress(tenantId)) — true until the wizard reaches COMPLETE. This gates re-import upsert: during setup a changed row updates the matched person in place; post-setup the same row is skipped (insert-or-skip). See §6.

The year-writability guard (assertYearWritable) is now owned by the pipeline itself. Pass prisma and the resolved academicYearId via the yearOptions argument — the pipeline calls the guard before any callback is invoked.

async importFromFile(
  buffer: Buffer | Uint8Array,
  mimetype: string,
  tenantId: string,
): Promise<EntityImportSuccessDto> {
  const yearId = await this.resolveActiveYear(tenantId);

  return runImportPipeline<ParsedRow, ImportLookupData, EntityImportSuccessDto>(
    buffer,
    mimetype,
    tenantId,
    { allColumns: ALL_COLUMNS, requiredColumns: REQUIRED_COLUMNS },
    {
      loadLookupData: (tid) => this.loadLookupData(tid, yearId),
      validateRows:   (rows, lookup, agg) => this.validateRows(rows, lookup, agg),
      classifyRows:   (rows, lookup) => this.classifyRows(rows, lookup, isSetup),
      createRecords:  (rows, verdicts, lookup, tid) =>
        this.createImportRecords(rows, verdicts, lookup, tid, yearId),
    },
    { prisma: this.prisma, academicYearId: yearId },
  );
}

The four generic type parameters are: - TRow — the typed row shape (a Record<ColumnName, string> derived from ImportColumnName) - TLookup — the lookup data interface populated by loadLookupData - TResult — the success DTO returned to the controller

Canonical implementation: src/students/students.service.ts → importFromFile() Pipeline source: src/common/services/import-pipeline.ts

3. Import Field Descriptors¶

Declarative schema¶

Field descriptors live in src/<domain>/constants/import-schema.ts. Each entry declares the CSV column name, an ordered list of validation rules, and optional allowed values for error messages.

// src/students/constants/import-schema.ts
export const STUDENT_IMPORT_FIELDS: ImportFieldDescriptor[] = [
  {
    column: 'first_name',
    rules: [{ type: 'required' }, { type: 'maxLength', max: 100 }],
  },
  {
    column: 'date_of_birth',
    rules: [
      { type: 'required' },
      { type: 'date' },
      {
        type: 'custom',
        validate: (v) => {
          const d = parseImportDate(v);
          return d && d >= new Date() ? 'future' : null;
        },
      },
    ],
  },
  {
    column: 'gender',
    rules: [
      { type: 'custom', validate: (v) => (parseGender(v) === null ? 'invalid' : null) },
    ],
    allowedValues: () => Object.values(Gender),  // lazy — evaluated only on error
  },
  {
    column: 'nationality',
    rules: [
      { type: 'set', values: VALID_ISO_COUNTRIES, normalize: (v) => v.toUpperCase() },
    ],
  },
  {
    column: 'school_email',
    rules: [{ type: 'pattern', regex: EMAIL_REGEX, errorKey: 'invalid_email' }],
  },
];

Six rule types¶

Rule type	Fields	Behaviour
`required`	—	Fails if the value is absent or blank
`maxLength`	`max: number`	Fails if the string exceeds `max` characters
`date`	—	Fails if the value cannot be parsed by `parseImportDate` (see Date parsing below)
`pattern`	`regex: RegExp`, `errorKey?: string`	Fails if the value does not match the regex; `errorKey` overrides the default error key
`set`	`values: Set<string>`, `normalize?: fn`	Fails if the (optionally normalised) value is not in the set
`custom`	`validate: (v: string) => string \\| null`	Runs arbitrary logic; return an error key string on failure, `null` on success

File parsing — `parseFile` / `parseXlsx` / `parseCsv`¶

parseFile (src/common/utils/import-file-parser.ts) dispatches by MIME type: CSV/plain → PapaParse, everything else → parseXlsx. Both return Record<string, string>[] keyed by lower-cased, trimmed headers (first worksheet only for XLSX), skipping fully-blank rows.

XLSX is parsed with SheetJS (xlsx), not ExcelJS. ExcelJS's hand-rolled SAX parser only recognises unprefixed/default-namespace OOXML; valid files that use namespace-prefixed elements (<x:workbook>, <x:sheet> — emitted by some exporters) crash it with Cannot read properties of undefined (reading 'sheets'), which previously escaped as an unhandled 500 on /import. SheetJS implements the spec and reads both forms. Cell extraction: error cells → ''; date cells → ISO (see Tier 1 above); numbers/booleans → raw value as text; text/rich-text/hyperlink/formula → flattened display text. Any parse failure (corrupt, truncated, non-OOXML) is caught and re-thrown as a clean VALIDATION_FAILED 400 — a bad upload never crashes the request. SheetJS is lenient (it fabricates an empty sheet for garbage input), so parseXlsx also guards "no usable header row" explicitly.

Dependency note: xlsx is pinned to the SheetJS CDN tarball (https://cdn.sheetjs.com/xlsx-0.20.3/...), not the npm registry. The registry copy is frozen at the vulnerable 0.18.5 (prototype-pollution CVE-2023-30533, ReDoS CVE-2024-22363); the patched line ships only via the CDN. CI/registry mirrors must allow cdn.sheetjs.com. ExcelJS remains a dependency only for building XLSX fixtures in the parser spec.

Date parsing — `parseImportDate` (format-agnostic)¶

parseImportDate (src/common/utils/import-helpers.ts) is the single chokepoint for every date column — both the declarative date rule and every row-map transform. The frontend is format-agnostic: it never reformats a spreadsheet. Parsing is two-tier:

Tier 1 — native XLSX date cells. A column formatted as a real Date in Excel is stored as a whole-day serial. The XLSX parser (parseXlsx, see below) detects date-formatted cells (SSF.is_date) and converts the serial to ISO YYYY-MM-DD via SSF.parse_date_code, honouring the workbook's 1900/1904 epoch. This is computed by pure arithmetic on the serial — no Date object is constructed — so it is timezone-independent by construction. (Do not "fix" this by building a Date and reading components: a JS Date shifts the day across the UTC boundary on some servers. Verified stable UTC→UTC+14, SheetJS 0.20.3, 2026-06-22.) parseImportDate only ever sees text — all CSV values + Excel text cells.
Tier 2 — text values. parseImportDate accepts ISO plus the common numeric forms, resolving order per-value:
4-digit leading component → ISO order (YYYY-MM-DD, YYYY/MM/DD, YYYY.MM.DD);
otherwise the year is the 4-digit trailing component, and a part that can only be a day (>12) forces the order (15/05/2012 → day-first EU; 05/15/2012 → month-first US);
a genuinely ambiguous pair (both ≤12, e.g. 01/02/2012) defaults to day-first — the correct bias for a European SIS.

Separators / . - are all accepted but must be consistent within a value. Rejected: 2-digit years (century-ambiguous), datetime/time components, textual month names (May 15, 2012), mixed separators, and calendar-impossible dates (2024-02-31, 31/02/2012). Construction is via Date.UTC(...) with a re-derivation check so overflow can't slip past. Truth table: src/common/utils/import-helpers.spec.ts.

Deriving column lists¶

ALL_COLUMNS and REQUIRED_COLUMNS are derived automatically — no hand-maintained duplicates:

const { allColumns: ALL_COLUMNS, requiredColumns: REQUIRED_COLUMNS } =
  deriveImportColumns(STUDENT_IMPORT_FIELDS);

export { ALL_COLUMNS, REQUIRED_COLUMNS };

deriveImportColumns() treats any descriptor that contains a required rule as a required column.

Error registries¶

Two registries map error keys to ImportErrorCode values:

STANDARD_CODE_REGISTRY — ships in src/common. Covers the generic keys produced by the declarative pass: required, maxlength, invalid, invalid_email, future.
<ENTITY>_EXTRA_REGISTRY — defined per entity in constants/import-schema.ts. Covers hook-only keys that the standard registry does not know about.

// src/students/constants/import-schema.ts
export const STUDENT_EXTRA_REGISTRY: CodeRegistry = {
  missing_pair: { code: ImportErrorCode.FIELD_INVALID },
  ambiguous:    { code: ImportErrorCode.FIELD_INVALID },
  duplicate:    { code: ImportErrorCode.FIELD_INVALID },
  'referent:required': { code: ImportErrorCode.FIELD_REQUIRED },
};

Merge them when resolving errors:

const errors = buildValidationResult(
  agg,
  { ...STANDARD_CODE_REGISTRY, ...STUDENT_EXTRA_REGISTRY },
  STUDENT_IMPORT_FIELDS,
);

Hooks — entity-specific validation¶

The declarative pass runs first via validateImportRows(rows, IMPORT_FIELDS, agg). Entity-specific logic (lookup validation, cross-column rules, within-file uniqueness) runs afterwards in the validateRows callback:

validateRows: (rows, lookup, agg) => {
  // 1. Declarative pass
  validateImportRows(rows, STUDENT_IMPORT_FIELDS, agg);

  // 2. Hook: resolve (grade, department) pair. Department is required so
  //    grade names that exist in multiple departments resolve unambiguously.
  //    When the pair doesn't resolve, blame the column that is actually wrong
  //    (grade names are unique *per department*, not globally):
  //      - grade name unknown everywhere        → grade:invalid
  //      - grade known, department unknown       → department:invalid
  //      - grade known, department valid but
  //        doesn't offer that grade              → grade:not_in_department
  //    (blaming department in the last case would list every department as an
  //     allowed value even though the one the row names is valid.)
  for (let i = 0; i < rows.length; i++) {
    const gradeVal = rows[i]['grade']?.trim();
    const deptVal = rows[i]['department']?.trim();
    if (!gradeVal || !deptVal) continue;
    const entries = lookup.gradeMap.get(gradeVal.toLowerCase());
    if (!entries) {
      agg.addError('grade:invalid', i);
    } else {
      const deptKey = deptVal.toLowerCase();
      if (!entries.some((e) => e.departmentName.toLowerCase() === deptKey)) {
        const deptExists = lookup.allDepartmentNames.some(
          (d) => d.toLowerCase() === deptKey,
        );
        agg.addError(
          deptExists ? 'grade:not_in_department' : 'department:invalid',
          i,
        );
      }
    }
  }

  // 3. Resolve + attach allowedValues
  const errors = buildValidationResult(
    agg,
    { ...STANDARD_CODE_REGISTRY, ...STUDENT_EXTRA_REGISTRY },
    STUDENT_IMPORT_FIELDS,
  );

  // 4. Dynamic allowedValues for hook-produced errors
  for (const e of errors) {
    if (e.code !== ImportErrorCode.FIELD_INVALID) continue;
    if (e.column === 'grade') e.allowedValues = lookup.allGradeNames;
    else if (e.column === 'department') e.allowedValues = lookup.allDepartmentNames;
  }

  return errors;
}

applyFieldAllowedValues (called inside buildValidationResult) attaches static or lazy allowedValues from descriptors automatically. Dynamic values that depend on tenant data (like the grade list) are attached manually after resolution.

Conventions¶

Error key format: {column}:{rule} — e.g. email:invalid_email, grade:invalid.
allowedValues accepts string[] (static) or () => string[] (lazy, evaluated only when an error occurs for that column).
CSV column naming: snake_case — first_name, date_of_birth, institutional_email.

4. Import Error Codes¶

ImportErrorCode is an enum in src/common/constants/:

Code	Meaning
`HEADERS_MISSING`	One or more required columns are absent from the file header row
`FIELD_REQUIRED`	A required field is blank in a data row
`FIELD_MAX_LENGTH`	A field value exceeds the maximum allowed length
`FIELD_INVALID`	A field value fails pattern, set, date, or custom validation

All import failures are surfaced as ErrorCode.IMPORT_VALIDATION_FAILED (HTTP 422) with a structured data.errors[] payload using ImportValidationError objects. Each error carries code, column, rows (compressed row list), optional reason, optional params, and optional allowedValues.

reason disambiguates the coarse code. The four ImportErrorCode values are deliberately coarse — in particular many distinct causes (pattern / set / date / custom / duplicate) all collapse into FIELD_INVALID. reason carries the granular rule key that actually fired (duplicate, invalid_email, future, invalid, required, maxlength, …), derived from the column:rule aggregator key in toErrors(). The FE branches on (code, reason) for messaging — e.g. { code: FIELD_INVALID, column: 'code', reason: 'duplicate' } means "this room code is already used by another room", whereas room_type's reason: 'invalid' (plus allowedValues) means "not a known room type". reason is absent only for errors built outside the aggregator (the HEADERS_MISSING / HEADERS_UNKNOWN header errors, which the code already fully distinguishes).

5. Wiring an Import Route¶

Controller route¶

/** Import <entities> from CSV/XLSX file */
@Post('import')
@HttpCode(HttpStatus.OK)
@RequireAction(EntityKey.<ENTITY>, 'create')
@RequireRoles('admin')
@AggregateResponse()
@UseInterceptors(
  FileInterceptor('file', { limits: { fileSize: 10_485_760 } }),
)
@ApiImport<Entity>()
async import<Entity>(
  @UploadedFile() file: Express.Multer.File,
  @TenantId() tenantId: string,
  @Query('academicYearId') academicYearId?: string,
): Promise<<Entity>ImportSummaryDto> {
  if (!file) throw new BadRequestException('No file uploaded');
  return this.service.importFromFile(
    file.buffer,
    file.mimetype,
    tenantId,
    academicYearId,
  );
}

FileInterceptor is imported from @nestjs/platform-express. The import route reuses the create action — no separate action is needed.

@AggregateResponse() is mandatory on import routes, and the summary DTO must extend AggregateResponseDto. The summary has top-level keys count and items — neither of which is a scope name. Without the marker, FieldFilterInterceptor strips every key and non-platform-admin callers receive {}. The marker is security-sensitive: re-read the rules in chapter 04 → Interceptor Contract: Scope-Grouped vs Aggregate Responses before applying it, and re-audit whenever you enrich the summary with new fields. The interceptor runs a runtime assertion that throws in dev/test if an aggregate response ever grows a scope-grouped top-level key (e.g. sensitive) — don't disable that check.

@RequireRoles('admin') is the current role gate on import routes. Imports are bulk/destructive and restricted to tenant admins today; the list will widen to additional roles (e.g. 'secretary') once the product team signs off. The decorator takes OR semantics — add roles to the call list rather than stacking multiple decorators.

Shared envelope: `ImportSummaryDtoOf`¶

All three import endpoints — POST /students/import, /teachers/import, /staff/import — return the same envelope shape, defined once in src/common/dto/import-summary.dto.ts:

interface ImportSummary<T, TSkipped = SkippedImportItemDto> {
  importColumns?: ImportColumnsDto;    // BE-authoritative CSV headers (wizard steps); see §8
  count: number;                       // total records for this entity after the import
  items: T[];                          // preview of up to 5 records, oldest first
  created: number;                     // rows inserted by this run
  updated: number;                     // rows updated in place by this run (setup upsert)
  skippedItems: TSkipped[];            // full list of skipped rows, default SkippedImportItemDto (see §6/§8)
  rolloverAppliedAt: Date | null;      // active year's value; null after a setup student-import reset
}

Per-entity DTOs extend the factory-generated class and only vary in the item type:

export class StudentImportItemDto {
  @ApiProperty({ format: 'uuid' }) id: string;
  @ApiProperty() firstName: string;
  @ApiProperty() lastName: string;
  @ApiProperty() dateOfBirth: string;                 // ISO YYYY-MM-DD
  @ApiProperty({ enum: StudentStatus }) status: StudentStatus;
  @ApiPropertyOptional({ nullable: true }) identificationCode: string | null;
  @ApiProperty() departmentName: string;
  @ApiPropertyOptional({ nullable: true }) gradeName: string | null;
  @ApiPropertyOptional({ nullable: true }) referentEmail1: string | null;  // referent_email_1 slot
}

export class StudentImportSummaryDto extends ImportSummaryDtoOf(
  StudentImportItemDto,
  'StudentImportSummaryDto',
) {}

The factory renames the generated class via Object.defineProperty(..., 'name', ...) so Swagger emits a distinct schema per entity; don't remove that or the three DTOs collapse into a single anonymous class in the OpenAPI output. The factory extends AggregateResponseDto, so subclasses automatically satisfy the @AggregateResponse() contract.

The preview item key set mirrors the template's required columns and is stable + complete. The FE derives the preview table's columns from the keys present in each items[] object, so the item must carry one key per required template column (plus the system id/identificationCode), always present and valued null when blank — never omitted. Were a required column dropped whenever it was empty across the previewed sample (e.g. institutionalEmail null on every staff record), the FE would silently lose that column. The shape is produced by each service's getImportPreviewConfig() (select + mapItem) — the single chokepoint feeding both POST /<entity>/import and the setup-step GET (StepHandler.load() → getImportSummary), so widening the preview is one edit per entity, not two. Required-column coverage today: Staff/Teacher items carry dateOfBirth/taxCode/institutionalEmail; Student items carry dateOfBirth/status/referentEmail1 (the last via an ordered referents: { …, orderBy: { createdAt: 'asc' }, take: 1 } join — the first-linked referent). dateOfBirth is formatted to a date-only YYYY-MM-DD string in mapItem. The relation-join select shape (PrismaSelectClause) permits orderBy/take on a to-many join for exactly this deterministic-slice case.

When adding a new import endpoint, reuse the factory — don't re-invent an envelope. The only thing you should define per-entity is the <Entity>ImportItemDto class — and it should carry every required template column (null when empty), not just an identity glance.

Steps¶

src/<domain>/dto/<domain>-import-summary.dto.ts — create <Entity>ImportItemDto and extend ImportSummaryDtoOf(...) (the envelope, incl. created/updated/skippedItems/rolloverAppliedAt, comes for free).
src/<domain>/<domain>.controller.ts — add the import route above. Declare it before any @Get(':id') or @Patch(':id') routes to avoid path collisions.
src/<domain>/<domain>.service.ts — add importFromFile() calling runImportPipeline<>() with the four callbacks and { prisma: this.prisma, academicYearId: yearId } as the yearOptions argument. The pipeline owns the assertYearWritable guard — do not call it again from the service. Scope the existing-record load to the target academicYearId. For person entities (students, teachers, staff), call resolvePersonUuid() inside the insert branch of createRecords to assign or reuse personUuid.
src/<domain>/<domain>.swagger.ts — add ApiImport<Entity>() using the ApiImportEndpoint factory.
src/<domain>/constants/import-schema.ts — define field descriptors (see §3).
Update barrel exports — src/<domain>/index.ts.

Canonical examples: src/students/students.controller.ts, src/students/students.service.ts

6. Classification & Re-import Upsert¶

Classification is scoped to the target academic year only. The same personUuid is valid in multiple years — only same-year matches are considered.

The match key is identity alone: firstName + lastName + dateOfBirth. Email is no longer a match key — it is enforced as a uniqueness constraint in the validation stage (below).

Single-record create¶

assertNoPeopleDuplicate() is called in the beforeCreate hook for single-record creation. It checks both identity match (name + date-of-birth) and email uniqueness within the target year:

await assertNoPeopleDuplicate(this.prisma, tenantId, yearId, {
  firstName, lastName, dateOfBirth, schoolEmail,
});

Import batch — `classifyImportRows`¶

classifyImportRows() resolves each row to an ImportRowVerdict against the existing-record set (loaded once in stage 3 and passed in via the lookup):

classifyRows: (rows, lookup) =>
  classifyImportRows<ExistingStudent>(rows, {
    existing: lookup.existingStudents,
    idOf:   (s) => s.id,
    codeOf: (s) => s.identificationCode,
    differs: (row, existing) => this.studentRowDiffers(row, existing, lookup),
    upsert: isSetup,
  }),

Verdict	When	`createRecords` action
`insert`	No existing identity match	Create a new record (+ `resolvePersonUuid`, referent/department links).
`update`	Identity match and a changed cell, and `upsert` (setup)	Patch in place via `mapRowToCreateData`; `id` / `personUuid` / `identificationCode` are preserved. Sub-resources (referent links, teacher–department links) are reconciled to the row.
`skip`	Identity match with no change, an in-file identity repeat (first-occurrence-wins), or any identity match when `upsert` is false (post-setup)	Nothing; the row is recorded in `skippedItems`.

Change detection (importDataDiffers) reuses mapRowToCreateData so the diff and the persisted write share one normalization. A whenEmpty:'undefined' blank reads as "leave untouched" (no diff); a whenEmpty:'null' blank reads as a clear (diff vs a populated field). Each entity layers its own extras on top — students compare departmentId/gradeId + the linked referent-email set; teachers compare the department-link set.

Email uniqueness is a validation constraint, not a dedup key¶

isImportEmailConflict() (called in validateRows) raises <emailColumn>:duplicate (a 422) when a row's email belongs to a different identity — an existing record or an earlier in-file row. A row matching its own person's email is allowed, so a re-import of an unchanged or edited person never 422s on its own email. This applies to all three entities (students school_email, teachers/staff institutional_email).

Response envelope¶

getImportSummary(tenantId, { created, updated, skippedItems }) returns the shared envelope (§5) plus: - created / updated — counts for this run. - skippedItems[] — full (uncapped) list of skipped rows: { rowNumber, firstName, lastName, identificationCode?, email? }. - rolloverAppliedAt — the active year's value (a setup student-import resets it to null), so the FE knows whether rollover still needs applying.

7. Cross-Year Identity¶

For person entities (Student, Teacher, Staff), the same physical person may be imported again in a new academic year. resolvePersonUuid() preserves identity continuity across years without requiring a manual rollover.

Location: src/common/utils/resolve-person-uuid.ts

Match cascade (most reliable first):

taxCode — case-insensitive match against any record for the same tenant in a different academic year.
firstName + lastName + dateOfBirth — case-insensitive name match plus exact date match.

If a match is found, the existing personUuid is reused. If no match is found, the caller generates a fresh UUID (Prisma default).

// Inside createRecords — called per non-duplicate row
const personUuid = await resolvePersonUuid(
  this.prisma,
  tenantId,
  academicYearId,
  EntityKey.STUDENTS,
  {
    taxCode:     row['tax_code']?.trim() || undefined,
    firstName:   row['first_name'].trim(),
    lastName:    row['last_name'].trim(),
    dateOfBirth: parseImportDate(row['date_of_birth'])!,
  },
);

await tx.student.create({
  data: {
    tenantId,
    academicYearId,
    ...(personUuid ? { personUuid } : {}),  // omit to let Prisma generate a fresh UUID
    // ...other fields
  },
});

resolvePersonUuid() searches records outside the target year (academicYearId: { not: yearId }) so within-year matches are never returned here — they are handled by classification (§6) instead. Note resolvePersonUuid runs only on the insert branch; an update preserves the matched record's existing personUuid.

8. Non-person imports (rooms)¶

Rooms are the first non-person entity on the pipeline. The flow is identical to the people imports; the deltas come from rooms not having a person identity, email, or wizard handshake. Canonical: src/rooms/rooms.service.ts (importFromFile + validateRoomRows/classifyRoomRows/createRoomImportRecords/getImportPreviewConfig), src/rooms/constants/, spec docs/superpowers/specs/2026-06-12-rooms-import-design.md.

Concern	People imports	Rooms import
Match key (classification)	`firstName + lastName + dateOfBirth`	`name` (per-year DB unique), lowercased
Secondary unique (validation constraint)	`email` (`isImportEmailConflict`)	`code` (a row whose code belongs to a different room → `code:duplicate`)
Cross-year identity	`resolvePersonUuid` on insert	none (fresh UUID per row)
Wizard handshake	`completionField` (Tenant timestamp)	none — `completionField` is now optional
Lookups	grade/department	room type (by name, `CANTEEN` rejected — `room_type:invalid`) + department (by name); both attach dynamic `allowedValues`
Columns	full person field set	`name`, `code`, `maximum_capacity`, `building`, `floor`, `room_type`, `department` (no lunch shifts)

Three shared helpers were generalized additively so the three person imports stay byte-identical: - ImportSummaryDtoOf<TItem, TSkipped = SkippedImportItemDto> — a third, defaulted type param so a non-person entity can supply its own skipped-item shape (rooms: { rowNumber, name, code }). getImportSummary<TSkipped> is correspondingly method-generic. - ClassifyConfig.rowKeyOf / recordKeyOf — optional identity-key extractors defaulting to the people tuple; rooms pass name-based keys. - ImportPreviewConfig.completionField? — optional; isImportComplete returns false when absent.

Canteen room types and their lunch shifts stay a manual-create flow — a CANTEEN row is rejected in validation (whole-file 422, like every validation error), so no partial import happens.

maximum_capacity is validated identically across every write path — a whole number ≥ 1. The rule is single-sourced in isValidRoomCapacity (src/rooms/rooms.validation.ts), consumed by both the import custom rule (constants/import-schema.ts) and the setup bulkSync validator (validateRoomsData); the create/setup DTOs (CreateRoomConfigDto, setup RoomItemDto) enforce the same rule declaratively via @IsInt() @Min(1). The bulkSync re-check is defense-in-depth: without it, a capacity that reached bulkSync without passing the DTO pipe would hit the required Int column and surface as an opaque 500 rather than a clean SETUP_VALIDATION_FAILED. Keep all four in lockstep when the rule changes.

Wizard-step `importColumns` (BE-authoritative headers)¶

The people import steps (STUDENTS/TEACHERS/STAFF) are Import handlers whose load() returns getImportSummary(), so the wizard GET state already carries importColumns ({ required[], optional[] }, built by buildImportColumns(allColumns, requiredColumns)) — the FE renders the CSV template from the backend rather than hardcoding headers.

ROOMS is a Form step (FormStepHandler → RoomsService.getSetupSummary()), so it does not flow through getImportSummary. To keep the rooms in-step import equally BE-authoritative, getSetupSummary() includes its own importColumns: buildImportColumns(ALL_COLUMNS, REQUIRED_COLUMNS) (same constants the importer validates against — headers can't drift), surfaced as RoomsFormDataDto.importColumns (data.importColumns on the SetupRoomsStateDto, mirroring the people steps' placement).