P-07 · Generate Infotech export

SOP: Payroll_Processing.md §6 / Step 5.0 (S8 → S10)Actors: Payroll Executive (PE). POL approval needed for any flagged compliance gates (CPF ceiling, IR21, duplicate receipts, salary delta). Pre-state: Cycle in SUBMITTED, READY_FOR_INTERNAL_REVIEW, or READY_FOR_EXPORT. Post-state: ExportBatch created (versioned). Cycle transitions to EXPORTED. Per the 2026-04-22 client requirement, an approval round is auto-fired in the same call when the client has an active approver, advancing the cycle to CLIENT_REVIEW.

0. Prerequisites

• Validation cleared (P-04) and any required issues handled (P-06).
• Active UPLOAD ExportTemplateVersion exists for the client (the seed creates one for ACME and GLOBEX). The Infotech 51-column layout is hardcoded in packages/excel/src/infotech-renderer.ts — mappingJson per client is informational only.

1. Steps

1.1 Click Generate Export

On /dashboard/cycles/<id>, click Generate Export. The handler bundles four operations in one click:

1. Validate — POST /ops/cycles/<id>/validate. If blocking issues exist:

   • Toast: Can't export yet — N blocking issues need attention.
   • The Issues tab is opened.
   • The export call is not made and the cycle stays where it was.

2. Generate — POST /ops/cycles/<id>/exports. Server:

   • Calls aggregateInfotechRows() over the latest submission items. Per-employee aggregation rules:
     • Multiple change items for the same employeeRef collapse into one row.
     • JOINER / SALARY_CHANGE → #BAS (last-write-wins) and #INC. JOINER reads monthlySalary from the intake form; SALARY_CHANGE reads newMonthlySalary.
     • VARIABLE_PAY → AWS / BONUS / CLAIMS (additive).
     • DEDUCTION → DEDUCT (additive).
     • LEAVER → OTHER (replaces).
     • JOINERs without employeeRef get a synthesised placeholder code NEW-{nricOrFin} or NEW-001, NEW-002, …
     • Non-JOINER items without employeeRef are skipped.
   • Renders the xlsx via renderExportWorkbook(context).
   • Uploads to S3 at <clientCode>/<cycleMonth>/exports/v<n>/upload.xlsx.
   • Persists an ExportBatch row (versioned; subsequent generates increment version_no) and one ExportRow per employee (carries canonicalRowJson for diagnostics).
   • Returns a row-by-row preview.

3. Auto-request approval (per 2026-04-22 client requirement):

   • Picks the client's first active contact with canApprove = true, alphabetical by name.
   • Creates an ApprovalRound with roundNo = 1.
   • Generates the Spade-format approval report from submission items (packages/excel/src/spade-report-renderer.ts). Uploaded to <clientCode>/<cycleMonth>/approval-reports/v1/spade-report.xlsx. Persisted as File with fileKind = APPROVAL_REPORT.
   • Transitions the cycle EXPORTED → CLIENT_REVIEW.
   • Enqueues send-approval-request.
   • The export response includes:

     { "approval": { "contactName": "John Smith", "contactEmail": "john@acme.sg", "roundNo": 1 } }

   • The UI surfaces a toast: "Export generated · approval request sent to John Smith."

4. Open the Export preview dialog — shows the populated Infotech columns for every employee row plus a Download .xlsx button (resolves a presigned S3 URL).

1.2 Skip-cases

• If the client has no active approver, auto-request is skipped:
  • Response carries approvalSkipped.reason = "no_approver_configured".
  • Toast flips to amber: "Auto-approval didn't fire — no approver configured."
  • The manual Request approval button stays visible on the cycle header (see P-09).
• If an ApprovalRound already exists for this cycle (e.g. PE re-generated the export), auto-request is skipped with approvalSkipped.reason = "approval_round_already_exists". The existing magic link remains valid.

2. Verification

Database

SELECT id, version_no, generated_at, file_id
  FROM export_batches WHERE cycle_id = '<cycleId>'
  ORDER BY version_no DESC LIMIT 1;

SELECT employee_ref, canonical_row_json -> '#BAS' AS bas, canonical_row_json -> 'AWS' AS aws
  FROM export_rows WHERE export_batch_id = '<exportId>';

SELECT status, approval_round_no FROM payroll_cycles WHERE id = '<cycleId>';
-- status = 'CLIENT_REVIEW', approval_round_no = 1   (when auto-approval fired)
-- status = 'EXPORTED',      approval_round_no = 0   (no approver)

S3 / MinIO

• <clientCode>/<cycleMonth>/exports/v1/upload.xlsx — Infotech 51-column workbook.
• <clientCode>/<cycleMonth>/approval-reports/v1/spade-report.xlsx — Spade-format report.

Mailpit

Within ~10 s the configured approver receives:

• Subject: Payroll approval required for {month} — Round 1
• Body has a single Approve payroll button → http://localhost:3000/portal/<jwt>.

Reference fixture

Compare the downloaded workbook against docs/output-sample-docs/Payroll Input_InfoExcelImport.xlsx.

Exports tab

Past export batches are now first-class:

• The Exports tab lists every ExportBatch for the cycle.
• Per row, Preview opens the same dialog (data rebuilt from ExportRow.canonicalRowJson via GET /ops/exports/<id>/preview) and Download opens the presigned S3 URL.

3. Negative & edge cases

• Cycle in SUBMITTED with blocking failures → 400 + Issues tab opened. No export batch created.
• Cycle in EXPORTED already (no approval round) → re-clicking Generate Export creates a new ExportBatch with version_no = 2. Auto-approval is skipped (approvalSkipped.reason = "approval_round_already_exists" — even though there is no round yet, the cycle status is still past EXPORTED; check the actual response). Both versions remain previewable + downloadable.
• Cycle in CLOSED → 400 "Generate Export is not allowed once the cycle is Closed."
• Render failure (e.g. malformed mapping) → response is 500 with the underlying error code; the export batch row is not created (transactional).
• Export uploaded to S3 but DB write fails (theoretical) → orphan key clean-up runs via delete-s3-object on the next retention-purge pass.

Next

If auto-approval fired the cycle is in CLIENT_REVIEW — proceed to P-10 · Client approval decision. Otherwise either upload the payroll output (P-08) or manually request approval (P-09).