FS-02 · Upload trial balance

SOP: SOP_Financial_Statements.md §6 / Step 2.0 (DRAFT → TB_UPLOADED)Actors: FS Preparer (fs-preparer@spade.local) — holds UPLOAD_TRIAL_BALANCE. Pre-state: Engagement at DRAFT (or TB_UPLOADED / TB_MAPPED if you are re-uploading; re-upload resets state). Post-state: Engagement at TB_UPLOADED. FsTrialBalanceItem rows populated from the upload. Raw file persisted to S3.

0. Prerequisites

• Environment up per _shared/00-environment-setup.md.
• An engagement at DRAFT (see FS-01).
• A trial-balance file in CSV / XLSX / XLS, max 10 MB, with recognisable columns. The smart-column detector recognises:
  • account_code, description, debit, credit
  • OR account_code, description, current_year, prior_year
  • Case-insensitive, underscore/space-agnostic.

TIP

If you do not have a real client file at hand, the AURORA seed loaded 13 mapped rows that approximate the shape of a small-entity TB. The CSV is hand-rolled by the seed; you can dump and re-upload it to exercise the parser:

docker exec breezycorp-monorepo-postgres-1 psql -U spade -d breezycorp -c "
  COPY (SELECT account_code, account_description, current_year_balance, prior_year_balance FROM fs_trial_balance_items WHERE engagement_id = '<auroraEngagementId>') TO STDOUT WITH CSV HEADER" > /tmp/aurora-tb.csv

1. Steps

1.1 Upload via Web UI

1. Open the engagement detail page.
2. Click Upload Trial Balance.
3. Drag-drop the CSV/XLSX into the drop zone (single file per upload).
4. The UI shows an upload progress bar, then a preview table with: row count, detected current-year and prior-year columns, balance-check badge (Σ currentYearBalance = 0 ± 1.0 SGD).
5. Confirm → engagement transitions DRAFT → TB_UPLOADED.

1.2 Upload via API

POST /ops/financial-statements/engagements/<engagementId>/trial-balance
Content-Type: multipart/form-data
Authorization: Bearer <fs-preparer-jwt>

(form-data: file=<tb.csv>)

The handler:

1. Asserts UPLOAD_TRIAL_BALANCE.
2. Persists the bytes to S3 at financial-statements/<clientId>/<engagementId>/tb-<timestamp>.<ext>.
3. Inserts a File row with fileKind = FS_TRIAL_BALANCE.
4. Enqueues parse-trial-balance with {engagementId, fileId}.
5. Returns 202 Accepted immediately with the file id and a parseStatus = PENDING indicator.

1.3 Background — parse-trial-balance

The worker handler:

1. Streams the file from S3.
2. Runs smart column detection (case-insensitive, underscore/space-agnostic).
3. Normalises signs to debit-positive in storage. Credit columns flipped to negative currentYearBalance.
4. Detects 4-digit year headers; sets hasPriorYear.
5. Drops every existing FsTrialBalanceItem for the engagement (append-replace semantics).
6. Inserts one row per GL line.
7. Transitions engagement DRAFT → TB_UPLOADED.
8. Emits AuditEvent fs.tb.uploaded with {rowCount, balanceCheckPassed, fileId}.

2. Verification

Database

SELECT count(*) AS row_count,
       sum(current_year_balance) AS balance_check
  FROM fs_trial_balance_items
  WHERE engagement_id = '<engagementId>';
-- expect row_count > 0, balance_check ≈ 0 (within ±1.0)

SELECT status FROM fs_engagements WHERE id = '<engagementId>';
-- expect 'TB_UPLOADED'

S3

The raw upload should exist at financial-statements/<clientId>/<engagementId>/tb-<timestamp>.<ext>:

docker exec breezycorp-monorepo-minio-1 mc ls local/breezycorp/financial-statements/<clientId>/<engagementId>/

Audit log

Event type	Notes
fs.tb.upload.requested	API hit, file persisted, job enqueued
fs.tb.uploaded	Background job completed; payload shows rowCount + balanceCheckPassed

3. Negative & edge cases

• Unbalanced TB — handler still imports the rows; balance-check badge flips amber on the preview but the engagement transitions normally. The Mapping screen prominently displays the unbalance and the Statements step will surface it as an anomaly.
• Unrecognised columns — handler emits AuditEvent fs.tb.parse_failed with the column headers it saw. Engagement stays at DRAFT. Reviewer reformats the file and re-uploads.
• File > 10 MB — 413 Payload Too Large.
• Empty file or all-zero balances — accepted (parsers don't reject); the engagement transitions but Mapping is meaningless.
• Re-upload from TB_MAPPED or later — engagement returns to TB_UPLOADED (which is a regression from TB_MAPPED via the explicit TB_MAPPED → TB_UPLOADED transition); mappings are dropped. The status badge regression is by design.

Next

Proceed to FS-03 · Auto-suggest and confirm mappings.