Aller au contenu

Story ING-003: CSV/XLSX parsing + row validation + phone normalization

Module: ingestion Slice: Tier 1 sub-slice ING-001-b (architecture §11) Side: [BACKEND] Version target: V0.0.0 Priority: 3 Depends on: ING-001 (fixtures + test infra) Can develop concurrently with: ING-002 (no overlap — this slice is pure in-memory code) Merge order: Before ING-004 Estimated complexity: M PRD User Stories: Derived from ING-US-01..03 (validation kernel); rules V1 ING-R-001, R-002, R-005, R-008, R-010, R-011 (v0.0.0 subset) Wireframe: N/A — backend only

Objective

Deliver the pure, DB-free parsing + validation kernel for v0.0.0: CSV (Jackson CSV streaming, ;/, delimiter auto-detect, UTF-8), XLSX (Apache POI SAX XSSFReader), magic-byte verification, the v0.0.0 mandatory-column validation set, intra-file duplicate detection, and CI phone normalization to E.164. The output is an in-memory ParseResult { rows: List<ValidRow>, anomalies: List<Anomaly> }. No I/O beyond the input stream. This kernel is consumed by ING-004's commit transaction.

Backend Scope

Entities

None (in-memory only). Output DTOs: - ValidRow { rowNumber, sourceCustomerId, sourceContractId, nom, prenoms, telephoneE164, montantDu (long, XOF whole units), dateEcheance (LocalDate), email?, dateNaissance?, branche?, numeroPolice?, immatriculation?, statutSource?, informationsComplementaires? }. - Anomaly { rowNumber, code (enum), field?, rawValue? (redacted for PII) }. - ParseResult { rows, anomalies, totalRowsRead }.

Anomaly codes (enum, v0.0.0 subset): FORMAT_NON_SUPPORTE, TROP_DE_LIGNES, EN_TETE_INVALIDE, IDENTIFIANT_CLIENT_ABSENT, IDENTIFIANT_CONTRAT_ABSENT, NOM_ABSENT, PRENOMS_ABSENT, TELEPHONE_ABSENT, TELEPHONE_INVALIDE, MONTANT_ABSENT, MONTANT_INVALIDE, DATE_ECHEANCE_ABSENTE, DATE_ECHEANCE_INVALIDE, DOUBLON_INTRA_FICHIER.

Migrations

None.

Service Layer

  • CsvIngestionParser: streaming via com.fasterxml.jackson.dataformat.csv.CsvMapper. Delimiter auto-detection: sniff first non-empty line for ; vs , (whichever appears first); tie-break to ; (CIMA convention). UTF-8 with BOM tolerance.
  • XlsxIngestionParser: XSSFReader + SharedStringsTable + SAXParserFactory (POI streaming mode). Hard-stop at row 5 001 — if exceeded, emit single TROP_DE_LIGNES anomaly and abort with ParseResult { rows: [], anomalies: [TROP_DE_LIGNES] }. Must not use DOM-mode XSSFWorkbook (architecture §7.6 A1 + §12 A5: heap cap 256 MB asserted by CI).
  • MagicByteVerifier: first 8 bytes → CSV (any printable) or XLSX (50 4B 03 04 ZIP signature with xl/workbook.xml inside). Mismatch → FORMAT_NON_SUPPORTE anomaly + abort.
  • RowValidator (v0.0.0 subset of V1 ING-R-001..016):
  • Mandatory non-empty: identifiant_client, identifiant_contrat, nom, prenoms, telephone, montant_du, date_echeance → corresponding *_ABSENT anomaly when missing.
  • montant_du: parsed as integer XOF (no decimals); reject negative or zero with MONTANT_INVALIDE. Accept 15000, 15 000, 15.000 (CIMA grouping); reject 15.50.
  • date_echeance: ISO yyyy-MM-dd or dd/MM/yyyy accepted; otherwise DATE_ECHEANCE_INVALIDE. v0.0.0 does NOT enforce the >30-day-past ECHEANCE_DEPASSEE rule (deferred per architecture Appendix A, V1 R-007).
  • Optional fields parsed if present, never blocking: date_naissance, branche, email, numero_police, immatriculation, statut_source, informations_complementaires. code_agence is ignored in v0.0.0 (single implicit default agency).
  • PhoneNormalizer (V1 ING-R-005 CI algorithm): strip whitespace + () + - + .; accept +225 prefix → keep; accept 225 → prepend +; accept 10-digit local (starts 01/05/07) → prepend +225; accept 8-digit legacy → prepend +225 + leading 0 per CIMA mapping; reject otherwise with TELEPHONE_INVALIDE. Output +225XXXXXXXXXX. Inline regex chain — no libphonenumber in v0.0.0 (architecture §10).
  • IntraFileDeduplicator: scan rows by (sourceCustomerId, sourceContractId); second occurrence → DOUBLON_INTRA_FICHIER anomaly on the later row (V1 ING-R-011).

API Endpoints

None.

Validation Rules

See "Service Layer → RowValidator" above. All errors collected (no fail-fast); the caller decides whether to abort. v0.0.0 contract: any single anomaly causes ING-004 to take the all-or-nothing reject path (V1 ING-R-014 + architecture §0).

Multi-Tenant Considerations

This kernel is stateless and tenant-agnostic — it receives only the bytes. Callers (ING-004) are responsible for tagging the resulting rows with tenant_id. Works identically under SHARED and BYO DB profiles because it touches no DB.

Audit & Logging

No direct audit emission in this slice. Anomaly summary (counts by code + first 5 row numbers) is returned to the caller (ING-004), which emits ING_PARSE_FAILED with that summary in details. PII rule: raw phone/name values never appear in Anomaly.rawValue — that field is populated only for MONTANT_INVALIDE and date fields, never for TELEPHONE_* or NOM_* or PRENOMS_*.

Frontend Scope

N/A — backend only.

UI States (ALL REQUIRED)

State Behavior French Copy
Loading N/A — backend only —
Empty N/A — backend only —
Error N/A — backend only —
Offline / low network N/A — backend only —
Success N/A — backend only —

Responsive Behavior

N/A.

Interactions

N/A.

Acceptance Criteria

AC-001: Clean CSV produces zero anomalies and exact row mapping
Given the fixture `clean-5-rows.csv` (UTF-8, `;` delimited, 5 valid rows)
When CsvIngestionParser + RowValidator process the stream
Then ParseResult.rows.size() == 5
  And ParseResult.anomalies is empty
  And every row's `telephoneE164` matches `^\+225\d{10}$`
  And every row's `montantDu` is a positive `long`.

AC-002: Tainted XLSX flags exact anomaly codes on exact rows
Given the fixture `blocking-anomalies.xlsx` containing: row 2 missing `telephone`, row 3 `telephone="abc"`, row 4 `montant_du="15.50"`, row 5 duplicate of row 2's contract
When XlsxIngestionParser + RowValidator process the stream
Then ParseResult.anomalies contains exactly: TELEPHONE_ABSENT@row=2, TELEPHONE_INVALIDE@row=3, MONTANT_INVALIDE@row=4, DOUBLON_INTRA_FICHIER@row=5
  And no anomaly rawValue contains a phone or name string.

AC-003: 5001-row XLSX aborts with TROP_DE_LIGNES and stays under 256 MB heap
Given a generated XLSX with 5 001 data rows
When XlsxIngestionParser processes it
Then ParseResult.anomalies == [TROP_DE_LIGNES] and ParseResult.rows is empty
  And the JFR snapshot in CI shows max heap < 256 MB during parse.

AC-004: Magic-byte mismatch is rejected before any row parsing
Given a file whose extension is `.csv` but whose first 8 bytes are `50 4B 03 04 ...` (ZIP/OOXML)
When the parser dispatcher inspects the magic bytes
Then ParseResult.anomalies == [FORMAT_NON_SUPPORTE] and ParseResult.rows is empty.

Compliance Rules

  • V1 ING-R-001 (formats), R-002 (limits 5 MB / 5 000 rows), R-005 (CI phone normalization), R-008 (montant integer XOF), R-010 (mandatory fields), R-011 (intra-file dedup), R-014 (all-or-nothing) — all status VÉRIFIÉ per architecture §0.
  • Critical rule #2 (money): montant_du stored as long XOF whole units. Floats forbidden.
  • No PII (phone, name) in error payloads — architecture §7.4 PiiHasher discipline carries over to anomaly construction.

Standards & Conventions

  • docs/standards/critical-rules.md (#2 money, #3 audit).
  • docs/standards/java-spring-guidelines.md § stream parsing.
  • docs/standards/database-guidelines.md § XOF integer minor-unit convention.

Testing Requirements

Unit Tests

  • CsvIngestionParserTest: delimiter detection (; vs ,), UTF-8 BOM, malformed quoting → EN_TETE_INVALIDE.
  • XlsxIngestionParserTest: SAX mode confirmed by mocking XSSFReader; row 5 001 cutoff.
  • MagicByteVerifierTest: CSV / XLSX / mismatch / empty file.
  • RowValidatorTest: matrix of every anomaly code with table-driven inputs.
  • PhoneNormalizerTest: every accepted format variant + every reject case.
  • IntraFileDeduplicatorTest: 0 / 1 / multiple duplicates.

Integration Tests

  • XlsxLargeFileMemoryIT — generates 5 001-row XLSX in-temp, asserts AC-003 heap bound via JFR.

What QA Will Validate

  • Run ./gradlew :backend:test --tests "*ingestion.parse*" "*ingestion.validate*" — green.
  • Inspect a sample ParseResult for clean-5-rows.csv in the IDE debugger.

Out of Scope

  • DB writes (ingestion_import, ingestion_import_row, customer, contract) — ING-004.
  • SHA-256 dedup against prior imports — ING-004.
  • Audit emission of ING_PARSE_FAILED / ING_COMMITTED — ING-004.
  • DOB required validation (DATE_NAISSANCE_ABSENTE) — V0.0.2 ING-003.
  • Past-due rule ECHEANCE_DEPASSEE (>30 days past) — V0.0.2.
  • code_agence resolution — V0.0.2.
  • Operator UI / atelier — V0.0.3+.
  • All-fields-required V1 validation set — V0.0.3.

Definition of Done

  • [ ] All backend endpoints implemented and returning correct responses (N/A — no endpoints)
  • [ ] All frontend pages render correctly at 360 / 768 / 1024 px (N/A — backend only)
  • [ ] All 5 UI states implemented (N/A — backend only)
  • [ ] French micro-copy matches design spec exactly (anomaly enum names ARE the French copy — TELEPHONE_INVALIDE etc., used verbatim in the sidecar JSON that ING-004 will write)
  • [ ] All acceptance criteria pass manually
  • [ ] Unit + integration tests written and passing
  • [ ] Tenant isolation verified (kernel is tenant-agnostic; consumer responsibility — documented in javadoc)
  • [ ] [CUSTOMER stories] payload + Slow-3G + resumability (N/A — backend only)
  • [ ] Audit log entries present for every required event (N/A — no audit emissions here; consumer ING-004 emits)
  • [ ] No silent failures on network errors (parser receives an InputStream; IO errors propagate to caller as a single EN_TETE_INVALIDE anomaly with reason io_error)