Data Transfer Backend Documentation

1. Documentation Evidence

2. Backend Scope and Boundaries

Owns

• Import templates for the three people scopes (students, guardians, staff) — apps/api/src/utils/data-transfer/templates.ts.
• Parsing an uploaded spreadsheet into validated rows and reporting problems against the operator's own line numbers — parse-people-file.ts.
• The dry-run / commit lifecycle of an import request, backed by async_requests — data-transfer.service.ts, shared/data-transfer-request.service.ts.
• Writing validated rows into students, guardians, staff and their linked users rows, one transaction per row — shared/people-import-writer.service.ts.
• Building an export file (CSV/XLSX) from a role-scoped column set — shared/people-export-reader.service.ts, apps/api/src/utils/data-transfer/build-export-file.ts.
• Storing and re-serving import and export files through StorageManager, never through a signed object-storage URL.
• The single BullMQ worker on QueueName.PEOPLE, and the queue-level dead-letter record for anything that fails after being enqueued — workers/people-queue.processor.ts, shared/dead-letter.service.ts.
• Sending account-invitation emails to a bounded, paced batch of people, whether that batch was requested directly (POST /data/invites) or chained automatically off a completed import — workers/people-invite.processor.ts.

Does Not Own

• Person, student, guardian and staff row shape and validation itself — owned by the people module (PersonWriterService, PeopleCodeService), which this module calls rather than duplicates (apps/api/src/modules/data-transfer/data-transfer.module.ts:22-24).
• Scheduling guarantees — owned by OutboxService (apps/api/src/modules/outbox/shared/outbox.service.ts). This module never calls queue.add() directly for the commit or export flows.
• File storage mechanics (disk vs. remote driver, signed-URL support) — owned by @skoolsewa/storage's StorageManager.
• Sending the actual invitation email and issuing its token — owned by AuthEmailService and VerificationTokenService (apps/api/src/modules/auth/services/*); the invite processor only decides who is eligible, states the purpose and lifetime, and paces the calls. The token it asks for is an account_invite valid for ACCOUNT_INVITE_TTL_MS (7 days), redeemed at POST /api/auth/password/reset.
• Permission grants themselves — owned by the authorization module; this module only declares which permission codes (DataImport_*, DataExport_*) its routes require.

Source of Truth

3. Module Composition

Deliberately two modules, not one (data-transfer.module.ts:14-25, data-transfer-worker.module.ts:9-21): importing a module that declares an @Processor starts a BullMQ Worker in whatever process imports it. If the controller module also declared the processors, every short-lived process that imports it to serve one HTTP route — including a one-off script — would start a worker and could claim a job it is about to exit in the middle of. DataTransferWorkerModule is composed only where the app actually intends to run workers.

4. File and Directory Map

apps/api/src/modules/data-transfer/
  data-transfer.module.ts          # HTTP-facing module: controller + shared services
  data-transfer-worker.module.ts   # Worker-facing module: the one @Processor and its handlers
  data-transfer.controller.ts      # Every route in this doc
  data-transfer.service.ts         # Templates, upload/dry-run, commit, export request/status/download
  dto/
    data-transfer.dto.ts           # Every request/response DTO
    index.ts
  shared/
    data-transfer-request.service.ts  # async_requests CRUD + status transitions
    dead-letter.service.ts            # job_failures list/replay/record
    people-import-writer.service.ts   # Row -> users/students/guardians/staff writes
    people-export-reader.service.ts   # Role-scoped SELECTs for export
  workers/
    people-queue.processor.ts     # The one @Processor(QueueName.PEOPLE)
    people-import.processor.ts    # Handles PeopleJob.IMPORT_COMMIT
    people-export.processor.ts    # Handles PeopleJob.EXPORT_BUILD
    people-invite.processor.ts    # Handles PeopleJob.SEND_INVITE_BATCH

apps/api/src/utils/data-transfer/
  data-transfer.types.ts   # Pure shared types: DataScope, TemplateColumn, ParseResult, RowIssue, ImportReport
  templates.ts             # The three column lists + buildTemplateWorkbook
  parse-people-file.ts     # CSV/XLSX -> ParseResult
  build-export-file.ts     # rows -> CSV/XLSX buffer, with formula-injection guarding
  index.ts

5. Data Model

5.1 Schema Source

packages/db/src/schema/async-request/
  async-requests.ts   # asyncRequests table (import + export requests, both scopes)
packages/db/src/schema/jobs/
  job-failures.ts      # jobFailures table (the processing dead-letter queue)
packages/db/src/schema/outbox/
  outbox-events.ts     # outboxEvents table (the enqueue ledger the commit and export-request flows write to)

5.2 Tables and Collections

async_requests

The durable record behind every import and export. One row per upload (import) or per requested export.

result shape for an import request (built incrementally):

{
  "report": { "scope": "students", "totalRows": 120, "validRows": 118, "issues": [...], "unknownHeaders": [...], "missingHeaders": [...], "preview": [...] },
  "sendInvites": false,
  "storageKey": "imports/students/....dat",
  "outcome": { "created": 115, "failed": 3, "issues": [...], "createdUserIds": ["018f...", "018f..."] }
}

report and sendInvites are written by the dry-run upload; storageKey is added immediately after (only for a genuinely new upload, not a re-served duplicate — data-transfer.service.ts:165-181); outcome is added only after a successful commit, by the import processor. sendInvites is read back at commit time, not from the job payload, to decide can_login on every row the commit creates; outcome.createdUserIds is what the processor hands to requestInvites when it chains an invitation batch immediately afterward.

result shape for an invitation-batch request (scope invite:batch, whether created by POST /data/invites or chained off an import):

{ "requested": 3, "outcome": { "sent": 2, "skippedNoEmail": 0, "notInvitable": 1, "failed": [] } }

requested is written at creation time; outcome is added once PeopleInviteProcessor finishes the batch.

result shape for an export request:

{ "format": "xlsx", "storageKey": "exports/staff/....dat", "rowCount": 342 }

format is set at request time; storageKey and rowCount are added by the export processor once the file is built.

Indexes and constraints — rationale

job_failures

The processing dead-letter queue — what failed while a job was running, as opposed to outbox_events.status = 'dead', which records a job that could not be enqueued at all. See §9 for the full distinction.

Constraints and index rationale

outbox_events (shared infrastructure, not owned by this module)

Not owned by data-transfer, but load-bearing for it: commitImport and requestExport both write here inside the same transaction as their async_requests status change. See §9 and the outbox module's own documentation for the full table. The columns this module's rows actually populate:

5.3 Relationship Diagram

There is no foreign key between async_requests and either outbox_events or job_failures — the link is by value (aggregate_id / payload_ref.requestId equal to async_requests.request_id), because outbox_events and job_failures are shared infrastructure tables written by every module in the system, not tables this module can add a constraint to.

6. Services and Responsibilities

6.1 DataTransferService

Explain:

• Input normalization. None of the upload path is normalized before parsing — parsePeopleFile does all cell-level trimming; the service only checks size and readability.
• Validation order in commitImport: existence → ownership → status (pending only) → missing headers → zero valid rows → the transactional status flip. Each check throws before the next runs, so a caller always gets the first real reason, not a generic conflict.
• Transaction boundaries. commitImport, requestExport, and requestInvites each wrap their async_requests transition/insert and their outbox.enqueue call in one this.db.transaction(...) block — that pairing is the whole point of the outbox pattern (see §9). requestInvites is called both from the controller and from PeopleImportProcessor, so this atomicity applies identically whether the batch was asked for directly or chained off a completed import.
• Idempotency. uploadForDryRun is idempotent on file content via DataTransferRequestService.createOrGet; commitImport's transition is idempotent via the from: "pending" guard (a second click finds the status already committing and is refused with a 409, not silently re-run). requestInvites is not idempotent — it de-duplicates ids within one call, but two calls with the same userIds queue two independent batches, since DataTransferRequestService.create gives every invite request its own self-unique idempotencyKey.
• Fail-open or fail-closed. Ownership (assertOwnRequest) is fail-closed: anyone but the uploader or a superadmin is refused, even to just read the report, because a report carries a preview of real personal data (data-transfer.service.ts:425-432).
• Logger usage. One Logger.error in uploadForDryRun when the uploaded file cannot be read from disk.

6.2 DataTransferRequestService

Explain:

• Why transition returns a boolean instead of throwing. The from guard is a conditional UPDATE ... WHERE status IN (...). Zero rows updated means somebody else already moved the record — a second outbox delivery, or a concurrent operator — and the caller decides what that means (a duplicate-delivery no-op in the processors; a 409 in commitImport). Making this a return value rather than an exception is what lets one primitive serve both call sites correctly.
• Transaction boundaries. transition and findByRequestId both accept an optional executor so they can run inside a caller's transaction — commitImport passes its tx in; the read-only paths use the default (this.db).

6.3 DeadLetterService

Explain:

• Rows are addressed by public_id, never the serial id. id stays the primary key for indexes and any future retention job, but it never leaves the database — a sequential id on a route gated only by a read permission would let anyone holding it enumerate how much of the platform has failed and when, and the count itself is exactly the information the queue filter below already exists to withhold.
• The failure queue can never be read unpaginated. findAll refuses pagination: false outright with PAGINATION_LIMIT_INVALID (dead-letter.service.ts:71-76) — nothing prunes job_failures, so an unbounded read asks the process to buffer every row and every stack trace it has ever recorded at once, unlike the reference tables elsewhere in the codebase that do permit an unpaginated read.
• Queue scoping is done here, in code, not from the query string. REPLAYABLE_QUEUES = [QueueName.PEOPLE] is a private static array; queueFilter() ignores an unrecognised queueName query parameter and falls back to the one permitted queue rather than trusting the caller (dead-letter.service.ts:296-305). job_failures is global across every BullMQ queue in the system (backup, restore, catalog, orders-maintenance, outbox, people, …), so an unfiltered list or replay would let a clerk holding only DataImport_READ/DataImport_UPDATE see and re-run failures from every other domain.
• Replay uses a fresh job id, never the original. buildJobId(["replay", String(row.id), String(Date.now())]). BullMQ's add() treats a repeated existing jobId as a no-op that returns the existing (already-failed) job — replaying under the original id would report success having enqueued nothing (dead-letter.service.ts:160-164).
• Claim-then-enqueue ordering, not enqueue-then-claim. The conditional UPDATE ... WHERE replayed_at IS NULL runs before queue.add. The alternative order can leave a job running against a row that still reads unreplayed if the UPDATE then fails — and the next operator replays it again. This order's cost is that a claim can outlive an enqueue that never reached Redis, and since replayed_at is exactly what the already-replayed refusal reads, a row left in that state could never be replayed again through this route (dead-letter.service.ts:166-192).
• The enqueue is wrapped, and the claim is released if it fails. replay's try/catch around queue.add calls releaseClaim on failure, resetting replayed_at/replayed_by/replay_job_id back to null and returning 503 JOB_FAILURE_REPLAY_ENQUEUE_FAILED instead of a false success. releaseClaim matches on the failed call's own replay_job_id as well as the row id — without that predicate, a slow failing replay could clear a claim a second operator had already made successfully in the meantime, making their still-running job replayable a second time (dead-letter.service.ts:194-219,239-259).
• record is best-effort from the caller's side. It is called from inside a try/catch in PeopleQueueProcessor.onFailed, because a failed handler that throws has its exception swallowed by BullMQ — a failure to record the failure must never replace the original error in the log.

6.4 PeopleImportWriter

Explain:

• One transaction per row, not one for the file (people-import-writer.service.ts:30-46). An all-or-nothing transaction over 2,000 rows that fails on row 1,999 gives the operator nothing to show for the run and no way to tell which row was at fault beyond a raw constraint name — and it holds a lock on code_counters for the whole run, blocking every concurrent manual admission. Per-row transactions mean a bad row costs exactly one row; the report names it, and the operator re-uploads just the corrected lines.
• Admission/employee numbers are allocated once, up front, for the whole batch via PeopleCodeService.allocate(scope, rows.length) (people-import-writer.service.ts:73-78), then consumed positionally by array index. A row that fails leaves its allocated number unused rather than shifting every later row's number — a gap in the sequence is harmless; renumbering later rows would break the correspondence between "row 47 in the report" and "STU-2026-0051 in the database."
• Blank cells become undefined, not "". PeopleImportWriter.cell() trims and converts an empty string to undefined before it reaches an insert (people-import-writer.service.ts:107-122). Passing '' straight through made every sparsely-filled row fail with an unmapped Postgres 22P02 on an enum or date column, because Postgres rejects '' for those types outright — the report then said only "Failed query" for every row.
• The address is ten spreadsheet columns, resolved by name, scoped to the parent already resolved on that row (shared/people-import-address.util.ts, resolveAddress) — five for Permanent Province/District/Municipality/Ward/Tole, five more for the Current equivalents. Province, district and municipality are integer foreign keys in the database, exactly like ethnicity and mother tongue, so an operator filling a spreadsheet from a paper form has no id to hand; the resolver matches on lower(btrim(name)), refusing the row with IMPORT_UNKNOWN_PLACE rather than guessing. Because two different provinces can each have a district of the same name, Permanent District is looked up within Permanent Province, not by name alone — the same reasoning Permanent Municipality applies against Permanent District. The fill order is checked here, before the database ever sees the row, so a district given without its province fails with a message naming the missing column rather than a bare 23514 from the corresponding CHECK — which still exists as the authority and is mapped again by addressConstraintIssue in case a geography row is renamed or retired between resolution and insert. There is no house-number column on the template at all: it is the finest-grained, least standardised part of the address, and import leaves it unset, settable afterward through the person forms. Current Province/District/Municipality/Ward/Tole are only filled when they genuinely differ from the permanent address — leaving the whole Current group blank means "not recorded," never "same as permanent," because there is no such flag on the sheet or in the database.
• A student import row with no guardian information is refused outright, thrown inside the row's own transaction so the pupil it would otherwise orphan is rolled back with it. If both Guardian Name and Guardian Phone are blank, the row fails with IMPORT_STUDENT_REQUIRES_GUARDIAN naming the Guardian Name column — a second write path enforcing the same "a pupil must have at least one guardian" rule the people module's API enforces with STUDENT_REQUIRES_ONE_GUARDIAN, kept in step deliberately after an earlier version of this feature let the two drift.
• Guardian matching is by phone, not by name (people-import-writer.service.ts:200-212). Two rows both reading "Ram Rai" in a roll of several hundred are commonly two different people; a shared phone number in this context is a household. Matching on name would create duplicate guardian rows for every sibling pair; matching on phone lets siblings correctly share one guardian record. With no phone number at all, a new guardian is always created — a duplicate is easier to undo than a wrong merge.
• Two-or-more guardians already sharing one phone number is treated as ambiguous, not resolved. findOrCreateGuardian only reuses a match when the phone lookup returns exactly one row; two matches falls through to creating a third guardian rather than guessing (people-import-writer.service.ts:234-239).
• Department/designation resolution refuses rather than guesses (people-import-writer.service.ts:359-421). An unknown department name is a thrown ImportRowError, not a silently created department — a typo would otherwise permanently pollute the school's own department list and split every later report across the misspelling and the correct name.
• Salary is all-or-nothing at zero, not partial-null. If either basicSalary or allowances is present, the other defaults to "0" rather than staying null — the staff_salary_pair_coherent check (owned by the people/staff schema) requires both or neither, and defaulting to an explicit zero is a real value, unlike a silent NULL that would also null total_salary (people-import-writer.service.ts:331-340).
• Error translation never leaks a raw database error to the report. toIssue() tries ImportRowError first, then PersonWriterService.translate() (the shared typed-error translator), and only falls back to a generic IMPORT_ROW_WRITE_FAILED message — drizzle's own error message is the full parameterised SQL plus every bound value, which would otherwise put a pupil's personal data and the schema itself into a report an operator downloads (people-import-writer.service.ts:548-586). The raw error still reaches the log, at error level.
• Ethnicity and mother tongue arrive as names and refuse rather than guess. resolveClassification matches the spreadsheet cell against ethnicities/motherTongues on lower(btrim(name)) — the same normalisation the tables' own unique index uses — and throws IMPORT_UNKNOWN_ETHNICITY/IMPORT_UNKNOWN_MOTHER_TONGUE for a name that does not match, rather than writing null (people-import-writer.service.ts:454-479). Both columns are ON DELETE SET NULL, so a silently blanked value would be indistinguishable from one the operator left blank on purpose, and the school's own ethnicity return would simply come up short by however many rows were misspelled.
• can_login on every row this run creates follows the upload's own sendInvites choice, never a per-row value. apply's grantLogin parameter (default false) is threaded into every buildInsert call — an import is frequently a migration of records for people (last year's leavers, a roll typed from paper) who must never be emailed at all, so the default is no account; ticking "send invites" on the upload is what flips it for every row this run creates (people-import-writer.service.ts:84-90,184-188).
• imeisId on a student row is stored as text, straight from the "IEMIS ID" column. Kept as text, like every other identifier-shaped column in this module, so a leading zero in the ministry's own identifier is not silently dropped (people-import-writer.service.ts:202).

6.5 PeopleExportReader

Explain:

• Gated columns are added or removed as whole columns, never nulled. A caller without StudentMedical_READ/StaffSalary_READ gets a file with those headers absent entirely (people-export-reader.service.ts:23-28). An empty cell under a present header reads as "this person has no recorded value," which is a different and wrong statement — and a spreadsheet with the header present is one an operator will fill in and re-import, creating data nobody actually reviewed.
• Bank and statutory identifiers ride the salary gate, not the staff one. includeSalary (from StaffSalary_READ) additionally adds bank name, account number, branch, PAN number, citizenship number, SSF number, and CIT number to the staff export (people-export-reader.service.ts:367-386) — a colleague holding only Staff_READ has no more legitimate business with where somebody's wages are paid than with how much they are paid, so those columns are absent for them exactly as the salary figures are.
• Ethnicity and mother tongue are exported as names, on all three scopes. Each is a correlated scalar subquery against ethnicities/motherTongues (people-export-reader.service.ts:159-164, mirrored for guardians and staff) rather than the stored integer id — an export that emitted the id would produce a file the same system cannot re-import, and an office correcting a typo by round-tripping a spreadsheet through export-then-import is the ordinary case this is built for.
• The student export also carries the IEMIS ID column, read straight off students.imeisId (people-export-reader.service.ts:170,231) — present regardless of any gate, since it is not sensitive in the way medical or salary data is.
• The row cap is 20,000 (MAX_ROWS), applied as a plain SQL LIMIT, not surfaced as a separate error — an export simply truncates at that many rows. An unbounded SELECT materialised into a workbook in memory is an out-of-memory kill of the worker process, which BullMQ then retries and kills again.
• The student export flattens exactly one guardian per pupil — the one marked is_primary on student_guardian — via three correlated scalar subqueries (name, phone, relationship). A child with three guardians does not become three rows; the export's grain is "one row per pupil," and changing that silently would change what the file means to whoever opens it.
• Soft-deleted rows are excluded everywhere (isNull(*.deletedAt) on both the entity table and users), consistently across all three scopes.
• Filters are a fixed, typed allowlist per scope, never an arbitrary passthrough of the caller's filter object — recordStatus/gender for students, kind for guardians, employmentStatus/departmentId for staff. An unrecognised or wrongly-typed filter key is silently ignored rather than applied.

7. Runtime Flows

7.1 Upload and dry-run validate an import

Branches and Edge Cases

7.2 Commit an import

Branches and Edge Cases

7.3 Request, poll, and download an export

Branches and Edge Cases

7.4 A job fails while running

7.5 Replay a dead-lettered job

Branches and Edge Cases

7.6 Send an invitation batch

Whether the batch was requested through this route or chained automatically by PeopleImportProcessor after a commit, it is the same requestInvites call, the same async_requests row shape, and the same worker — there is exactly one code path for sending an invitation batch, with two producers.

8. Caching

This module does not use Redis for read caching. async_requests is read directly from PostgreSQL on every status poll — an export status endpoint is expected to be polled at low frequency by a human waiting for a file, so a cache layer here would add invalidation complexity (bust on every worker write) for a query pattern that does not need it.

9. BullMQ, Schedulers, and Async Work

Explain:

• One @Processor on QueueName.PEOPLE, routing by job name — not three. @nestjs/bullmq builds one BullMQ Worker per class decorated with @Processor; WorkerOptions.name is a monitoring label, not a filter. Three decorated processors on this queue would be three Worker instances racing for every job on it, and a handler that does not check job.name will happily run against the wrong payload shape. PeopleQueueProcessor.handlers is typed Record<PeopleJob, (job: Job) => Promise<unknown>>, so adding a member to PeopleJob without adding its handler is a compile error, not a job that silently completes having done nothing (people-queue.processor.ts:11-38).
• Concurrency is 2. An import holds a worker slot for the length of a whole file; concurrency of 1 would make an export queue invisibly behind a 2,000-row import with the operator having no way to see why. Higher than 2 is not free either — every import row transaction takes a lock on code_counters, and running many imports at once turns that counter into the contention point (people-queue.processor.ts:31-37).
• The BullMQ jobId the dispatcher actually uses is outbox_<outbox row id> (outbox-dispatcher.processor.ts:54,121), not the dedupeKey string this module supplies (import-commit-${requestId} / export-build-${record.requestId}). The dedupeKey only makes the outbox_events insert idempotent under (aggregate_type, aggregate_id, job_name, dedupe_key); it plays no role in BullMQ's own deduplication.
• Delivery is at-least-once, never exactly-once. Every processor's first act is therefore a conditional status read (committing-only for import, pending-only for export/invite) rather than an unconditional write.
• SEND_INVITE_BATCH has two producers and one handler. requestInvites is the single call both DataTransferController.sendInvites and PeopleImportProcessor make — so an invitation batch behaves identically regardless of whether an operator asked for it directly or an import chained it, and there is exactly one place that decides how the batch is queued.
• A dead letter is recorded only from PeopleQueueProcessor.onFailed, on the final attempt. Nothing else in this module writes to job_failures.

10. Realtime and Events

None. This module emits no Socket.IO events and has no realtime consumer.

11. Security, Auth, and Abuse Controls

• Guards. Every route on DataTransferController sits behind @UseGuards(JwtAuthGuard, RoleGuard) at the controller level (data-transfer.controller.ts:56) — there is no public or guest-accessible route in this module.
• Permissions. DataImport_READ (templates, get import, list job failures), DataImport_CREATE (upload), DataImport_UPDATE (commit, replay), DataExport_CREATE (request export), DataExport_READ (get export status, download), and Users_UPDATE (send invitations). DataImport_DELETE/_RESTORE and DataExport_UPDATE/_DELETE/_RESTORE exist in the permission catalog (every module gets all five actions by construction — permission-catalog.ts:117-123) but are not used by any route here; there is no delete or restore endpoint for a transfer request.
• Invitations are deliberately gated on a Users permission, not a DataImport/DataExport one. POST /data/invites requires Users_UPDATE because granting sign-in access to a person is an identity change, and this route reaches that outcome without ever touching a spreadsheet — gating it on an import permission would let anyone who can import files also grant login access to arbitrary existing people, which is a different capability than importing.
• Dead-letter rows are addressed by public_id, never the serial id. A sequential id on a route gated only by a read permission is a working enumeration of every job the platform has ever failed; job_failures.public_id is a uuid, generated per row, and the serial id never appears in a response.
• The failure queue refuses to be read unpaginated. pagination=false on GET /data/job-failures is rejected with PAGINATION_LIMIT_INVALID — the table only ever grows, and nothing prunes it.
• Ownership, not just authentication. assertOwnRequest additionally requires record.actorId === actor.id, with actor.activeRole?.isSuperadmin as the only bypass — a superadmin can see a stuck request when the clerk who started it is unavailable; everyone else is refused even read access, because a report/export can carry a live preview of pupils' or staff's personal data.
• File-type and size enforcement is layered, not single-point. spreadsheetMulterOptions rejects a non-.csv/.xlsx extension and anything over 10 MB before the controller method runs; DataTransferService.uploadForDryRun independently re-checks the same 10 MB cap on the bytes it actually reads. parsePeopleFile's own zip-signature sniff (0x50 0x4B) decides CSV-vs-XLSX decoding from the bytes themselves, not from the client-supplied MIME type, which Excel, LibreOffice and Windows report inconsistently for CSV.
• Row count is bounded on both directions. Imports refuse anything over 5,000 data rows (DataTransferService.MAX_ROWS); exports are capped at 20,000 rows read (PeopleExportReader.MAX_ROWS). Both exist to keep a single request from exhausting the process's memory.
• Column-level authorization is enforced by omission, decided once, at the edge. Gated export columns (StaffSalary_READ, StudentMedical_READ) are resolved from the requester's active role inside requestExport, carried on the outbox payload, and never re-derived inside the worker — a worker has no session and no active role, so re-deriving would mean choosing a role on the operator's behalf, and the safe-looking choice (the union of every role they hold) is exactly the privilege escalation the active-role model exists to prevent.
• Dead-letter payload redaction. job_failures.payload_ref never carries a full job payload — see §5.2 and §16.5. This is the load-bearing control that keeps DataImport_READ from becoming an account-takeover primitive against queued password-reset invitations.
• Downloads never leave this API's own auth boundary. Both the template download and the export download stream bytes through this controller behind the same guard chain as every other route, rather than minting a signed object-storage URL — a signed URL is a bearer credential in a query string that would bypass RoleGuard for anyone it is forwarded to.
• CSV/XLSX formula-injection guarding on export. Any exported cell whose text begins with =, +, -, @, a tab, or a carriage return is prefixed with a leading ' before being written (build-export-file.ts:15,28-30) — a free-text field influenced by an outsider (a rejection reason, a note) could otherwise carry =HYPERLINK(...), which Excel executes the moment the file opens.
• Fail-closed on unknown queue job names. PeopleQueueProcessor.process throws for any job.name not in its handlers map, deliberately, so BullMQ records the failure rather than the job silently completing.

13. Error Handling

14. Observability

15. Testing and Validation

16. Mandatory Backend Deep-Dive Pack

16.1 Submodule Coverage Matrix

Rules:

• Every file under apps/api/src/modules/data-transfer/ and apps/api/src/utils/data-transfer/ is represented above.
• PersonWriterService and PeopleCodeService are imported from the people module rather than reimplemented, because person-row shape and admission/employee-number allocation are people-module invariants this module must not duplicate or drift from.
• AuthEmailService/VerificationTokenService are imported from the auth module for the same reason — invitation delivery and token issuance are auth invariants.

16.2 UML and Architecture Diagram Pack

16.3 Code Flow Narrative

DataTransferService.commitImport — the highest-stakes method in this module

16.4 Data Layer Deep Dive

Covered in full in §5.2 (field tables, nullability, constraints, business meaning) and the index-rationale tables embedded there. Summary of what is not repeated here:

• No JSON Schema is enforced on async_requests.result or job_failures.payload_ref at the database level — both are plain jsonb, and the shape discipline (never store the payload, only a reference; specific keys per import/export) is enforced entirely in application code (data-transfer.service.ts, dead-letter.service.ts, people-queue.processor.ts).
• No money units in this module's own tables — basicSalary/allowances are staff-module columns this module writes to via PeopleImportWriter, not columns it owns.
• No timezone-sensitive interpretation beyond what timestamptz already gives; dates parsed from spreadsheets (admissionDate, dateOfBirth, joiningDate) are validated as calendar dates (YYYY-MM-DD) with no time-of-day component.
• No migration history is referenced in the reviewed files beyond the schema definitions themselves.
• No seed-data dependency — this module's tables start empty and are populated entirely by runtime use.

16.5 Business Logic and Invariant Catalog

16.6 Tradeoffs, Alternatives, and ADR Notes

16.7 Operational Runbook

16.8 Backend Risk Register

17. Zero-Omission Backend Checklist

• Every file in the module directory is represented or explicitly marked non-runtime.
• Every controller, service, provider, processor, scheduler, helper, mapper, DTO, enum, and schema is documented.
• Every method with business behavior has a code-flow narrative (the highest-stakes one in full; the rest via the runtime-flow tables in §7).
• Every table/collection/cache object/job payload has field-level detail.
• Every index, constraint, relation, and delete behavior has rationale.
• Every lifecycle/status transition has a state diagram and transition table (see §7.1-7.3 sequence diagrams and their step tables; async_requests.status has no separate state-machine diagram beyond the transition tables since it is a linear, non-branching progression per flow).
• Every read/write/action/job flow has sequence and activity diagrams.
• Every business invariant is cataloged.
• Every cache key (none exist), queue job, realtime event (none exist), and external call is documented.
• Every architectural tradeoff is documented with alternatives and revisit triggers.
• Every operational failure mode has a runbook entry.

18. Backend Completion Checklist

• Module boundaries are documented.
• Every controller, service, DTO, schema file, job, cache key, and event is covered.
• Every database table/collection has a field table and relationship diagram.
• Every runtime flow has a diagram and branch notes.
• API, feature/flows, and TDD docs are linked.
• No claim is made without a source file or documented source reference.

See Also

• API doc: /docs/developer/data-transfer/api
• Features and flows doc: /docs/developer/data-transfer/feature
• TDD: not present for this module