# ADR-085 — File Upload Patterns: CSV Import, PDF Export, and Bulk Data Handling ## Status Accepted ## Status History ```yaml - 2026-06-27: Accepted Decision: Unified file upload, import, and export patterns across all PWAs. Client-side CSV parsing + server-side PDF generation + multipart upload. Size limits: CSV/XLSX ≤ 5 MB, general files ≤ 10 MB, proof images ≤ 100 KB (ADR-072). Changed_via: adr-kit (360lm) ``` --- ## Context 360lm PWAs require file operations beyond proof images (ADR-072): - **CSV/XLSX bulk import:** vendor rate cards, counter store lists, salary batch uploads - **PDF export:** monthly expense summaries, salary slips, custodian reports, reconciliation statements - **File uploads (non-image):** general document storage, blob references - **File references:** HMAC-signed proxy for recce view files (ADR-049) Currently, file handling is inconsistent across PWAs: - Some PWAs lack bulk import entirely; users must enter data row-by-row - No standardised PDF generation approach (some attempt client-side jsPDF, which is unreliable on mobile; some bypass export entirely) - Upload endpoints vary — some attempt multipart POST to PostgREST (which doesn't support multipart), others use ad-hoc Express sidecars - No standard size limits, progress indication, or error messaging for malformed files - CSV parsing is redundant across PWAs (no shared helper) **Affected PWAs:** - **Vendors** — bulk rate card import (CSV/XLSX) - **Counters** — bulk store list import - **HR** — salary batch file upload - **Finance (Custodian, Expense)** — PDF export of reports and slips - **Dispatch, Tour-Planner** — document uploads - Any future PWA requiring bulk import or PDF export **Related Decisions:** - ADR-072 (proof images — base64 in DB, max 100 KB, canvas editor) - ADR-049 (file proxy with HMAC signing) - ADR-014 (PostgREST as API layer — does NOT support multipart) - ADR-075 (RPC error response format) --- ## Decision ### 1. CSV/XLSX Import (Client-Side Parsing) **Pattern:** - Use native `FileReader` API to read file as text (CSV) or ArrayBuffer (XLSX) - Parse CSV with `String.split(',')` for simple cases; use `xlsx.full.min.js` (already bundled in tour-pg) for XLSX with complex formatting - Validate headers and row structure client-side before sending to server - Send parsed rows as JSON array to a dedicated RPC (e.g., `bulk_import_vendors`, `bulk_import_stores`) - Do NOT send raw file as multipart to PostgREST (PostgREST does not support multipart) - Do NOT attempt base64 encoding of the file — parse it first **Implementation Steps:** 1. User selects CSV/XLSX file via `` 2. Client calls `FileReader.readAsText()` (CSV) or `FileReader.readAsArrayBuffer()` (XLSX) 3. Parse result: - CSV: `text.split('\n').map(row => row.split(','))` - XLSX: use `xlsx.read(arrayBuffer, {type:'array'})` → `workbook.SheetNames[0]` → `xlsx.utils.sheet_to_json()` 4. Validate header row matches expected columns; reject with toast if not 5. Validate each row (type checks, required fields, numeric ranges); collect error rows by line number 6. If errors: show toast with list of offending row numbers; do NOT proceed 7. If valid: POST to `/db/rpc/bulk_import_` with JSON payload: ```javascript { "rows": [ { "vendor_name": "Foo Inc", "rate": 100, "region": "North" }, { "vendor_name": "Bar Ltd", "rate": 105, "region": "East" }, ... ] } ``` 8. RPC returns: `{ "inserted": 42, "updated": 3, "errors": [] }` (or errors with row indices) 9. Show result toast: "42 vendors imported, 3 updated" **Size Limits:** - Client-side reject: ≤ 5 MB before parsing (show toast "File too large") - RPC batch limit: 1,000 rows per request; split larger files into multiple batches **Error Handling:** - **Malformed CSV:** highlight offending rows in a toast list (up to 10 row numbers shown; "and N more…") - **Header mismatch:** show expected vs. received headers in toast - **Invalid rows (type, range, FK):** RPC returns errors with row indices; client shows in modal or paginated list --- ### 2. PDF Export (Server-Side Generation) **Pattern:** - PDF generation MUST be server-side only (not client-side jsPDF or html2pdf — unreliable on mobile, memory-hungry, layout inconsistent) - Create a dedicated Express sidecar endpoint (not PostgREST) that generates PDF and returns as `application/pdf` - PWA calls the sidecar endpoint with parameters (e.g., statement ID, month, format) - Client triggers download via `` or `fetch()` + blob download **Implementation Steps:** 1. PWA builds request: `POST /export-pdf` with query params or body: ```javascript const resp = await fetch('/export-pdf', { method: 'POST', headers: { 'Authorization': `Bearer ${token}` }, body: JSON.stringify({ entity: 'statement', id: 123, format: 'monthly' }) }); if (!resp.ok) { const error = await resp.json(); showToast(error.message || 'PDF generation failed'); return; } const blob = await resp.blob(); const url = URL.createObjectURL(blob); const a = document.createElement('a'); a.href = url; a.download = 'statement_2026-06.pdf'; a.click(); URL.revokeObjectURL(url); ``` 2. Express sidecar receives request, validates auth (user can export own data only) 3. Fetch data from PostgREST (list rows, get ledger details, etc.) 4. Render template using a library (e.g., `pdfkit`, `puppeteer`, `wkhtmltopdf`) 5. Stream PDF binary to client with headers: ```javascript res.set('Content-Type', 'application/pdf'); res.set('Content-Disposition', 'attachment; filename="statement_2026-06.pdf"'); pdfStream.pipe(res); ``` **Template Design:** - Use HTML + CSS template (not code-generated PDF primitives) - Render via `puppeteer` (headless Chrome) for best layout fidelity - Include: header (org/date), title, table of rows, footer (totals, QR code optional), page breaks on overflow - Support IST timestamps (ADR-070) and Indian number formatting (ADR-071) in template **Error Handling:** - If data fetch fails: return HTTP 400 + `{message: 'Statement not found or you do not have access'}` - If PDF generation fails: return HTTP 500 + `{message: 'PDF generation failed. Please try again later.'}` --- ### 3. General File Uploads (Non-Image, Non-CSV) **Pattern:** - Use multipart POST to a dedicated Express sidecar endpoint (not PostgREST — it does not support multipart) - Sidecar receives file, validates it (MIME type, size, virus scan optional), stores to disk or S3, returns file ID and path - PWA stores the file ID/path reference in a DB column, not the file content **Implementation Steps:** 1. User selects file via `` 2. Client checks size (reject if > 10 MB); show spinner 3. POST as multipart: ```javascript const formData = new FormData(); formData.append('file', fileInput.files[0]); formData.append('entity', 'dispatch_document'); // context for sidecar const resp = await fetch('/upload', { method: 'POST', body: formData }); if (!resp.ok) { const error = await resp.json(); showToast(error.message || 'Upload failed'); return; } const { file_id, path } = await resp.json(); // Store file_id/path in DB row ``` 4. Express sidecar: - Validates MIME type (whitelist per entity type) - Checks file size (reject if > 10 MB) - Stores to `/var/www/360lm/uploads//.` - Returns `{ "file_id": "", "path": "/uploads/dispatch_document/.pdf" }` 5. Client stores returned `file_id` in a DB column (e.g., `dispatch_documents.file_id TEXT`) 6. For retrieval: use `/file/` endpoint (validates user access via RPC before serving) --- ### 4. Size Limits and Progress | Type | Size Limit | Validation | Progress | |---|---|---|---| | CSV/XLSX import | ≤ 5 MB | Client-side (reject before parse) | Spinner for parse; optional progress bar during RPC | | PDF export | N/A (server-generated) | Server (data access check) | Spinner during generation + download | | General files | ≤ 10 MB | Client-side (reject before POST); server whitelist MIME type | Progress bar using `XMLHttpRequest.upload.onprogress` for > 1 MB | | Proof images | ≤ 100 KB | Client-side (ADR-072) | Compression slider shown during edit | **Progress Bar Implementation (for files > 1 MB):** ```javascript const xhr = new XMLHttpRequest(); xhr.upload.addEventListener('progress', (e) => { if (e.lengthComputable) { const pct = Math.round((e.loaded / e.total) * 100); progressBar.style.width = pct + '%'; } }); xhr.open('POST', '/upload'); xhr.send(formData); ``` --- ### 5. Error Handling and User Messaging **Client-Side Validation:** - File size check: "File too large. Max 5 MB for CSV/XLSX, 10 MB for documents." - File type check: "Invalid file type. Expected .csv, .xlsx, or .pdf." - Empty file: "File is empty." - CSV parsing errors: Show toast with offending row numbers: "Row 5, 12, 18: missing required column 'name'" **Server-Side RPC Error (per ADR-075):** - Invalid rows in bulk import RPC: return `{message: "Validation failed", errors: [{row: 5, reason: "name too long"}, ...]}` - Conflict (duplicate vendor code): return `{message: "Vendor code already exists"}` - Authorization (user cannot export this statement): return `{message: "You do not have access to this statement"}` **Network Error:** - Timeout on file upload: "Upload timed out. Please try again." - Connection lost during download: browser native retry (user re-clicks download link) --- ## Implementation Notes ### CSV Parsing Helper (Optional Shared Code) Place at `/shared/csv-parser.js` for reuse across PWAs (not mandatory; PWAs can inline simple parse logic): ```javascript // Parse CSV text into array of objects function parseCSV(text, expectedHeaders) { const lines = text.trim().split('\n'); const headers = lines[0].split(',').map(h => h.trim()); // Validate headers const missing = expectedHeaders.filter(h => !headers.includes(h)); if (missing.length > 0) { throw new Error(`Missing columns: ${missing.join(', ')}`); } // Parse rows const rows = lines.slice(1).map((line, idx) => { const values = line.split(',').map(v => v.trim()); const row = {}; headers.forEach((h, i) => { row[h] = values[i] || null; }); return { row, lineNumber: idx + 2 }; // +2: skip header, 1-indexed }); return { headers, rows }; } ``` ### XLSX Parsing (tour-pg already has `xlsx` bundled) ```javascript // Assume xlsx global is available (from bundled xlsx.full.min.js) function parseXLSX(arrayBuffer, sheetName = 0) { const workbook = XLSX.read(arrayBuffer, { type: 'array' }); const sheet = workbook.Sheets[workbook.SheetNames[sheetName]]; return XLSX.utils.sheet_to_json(sheet); } ``` ### Express Sidecar Endpoints (Python Flask or Node) Sidecar runs alongside the main app stack; routed via Traefik (ADR-017): - `POST /export-pdf` — generates and returns PDF - `POST /upload` — receives multipart file, stores, returns file_id and path - `GET /file/` — retrieves file after access check RPC Example structure: ```javascript // Node.js Express sidecar app.post('/export-pdf', async (req, res) => { const { entity, id, format } = req.body; const token = req.headers.authorization?.split(' ')[1]; // Validate user access (call hub RPC via proxy) const accessCheck = await checkUserAccess(token, entity, id); if (!accessCheck.allowed) { return res.status(403).json({ message: 'Access denied' }); } // Fetch data const data = await fetchEntityData(entity, id); // Render PDF const pdfBuffer = await renderPDF(entity, data, format); res.set('Content-Type', 'application/pdf'); res.set('Content-Disposition', `attachment; filename="${entity}_${id}.pdf"`); res.send(pdfBuffer); }); ``` --- ## Alternatives Considered ### 1. Client-Side PDF Generation (jsPDF, html2pdf) **Rejected.** Unreliable on mobile (memory constraints, browser crashes on large tables). Layout inconsistency across browsers. Server-side generation via Puppeteer is the industry standard for reliable PDF export. Client-side tools are suitable only for simple one-page documents; 360lm reports often span multiple pages with complex formatting. ### 2. POST CSV as Multipart to PostgREST **Rejected.** PostgREST does not support multipart requests — it is designed for JSON-only REST. Parsing CSV server-side requires a custom endpoint or RPC, adding complexity. Client-side parsing is simpler: parse in browser, send as JSON array to RPC. Also allows client-side validation before network overhead. ### 3. Store Entire File Content in DB (Not Just Reference) **Rejected.** Bloats the database (similar issue to ADR-072 with images included in list queries). Files should be stored in blob storage (S3, disk) and referenced by file_id. This keeps the database lean and supports fast backups. ### 4. Use a Third-Party File Upload Service (S3, Google Cloud Storage) **Rejected.** 360lm operates on self-hosted infrastructure (ADR-015, ADR-017). Adding external dependencies (AWS creds, GCP service accounts) increases operational burden and cost. Disk-based storage at `/var/www/360lm/uploads/` is simpler and sufficient for current scale. ### 5. Single RPC for All Bulk Imports **Rejected.** A generic `bulk_import(entity, rows)` RPC adds complexity in the RPC (conditional logic per entity) and makes validation harder to read. Dedicated RPCs (`bulk_import_vendors`, `bulk_import_stores`) are clearer, allow entity-specific validation, and easier to debug. ### 6. CSV Import via Web Form (Field Per Row) **Rejected.** Scales poorly; users entering 100 vendors one-by-one is unusable. Bulk import is essential for data migration and regular updates. --- ## Consequences ### Benefits - **Consistency:** all PWAs follow one bulk-import pattern (client parse → RPC), one PDF export pattern (server-side), one file-upload pattern (multipart sidecar) - **Performance:** client-side CSV parsing happens in browser (no server roundtrip); size validation is immediate (no upload waste) - **Reliability:** server-side PDF generation ensures mobile users get usable documents; no memory bloat from large tables - **User clarity:** progress bars for large uploads, error toasts list offending rows, schema validation before import attempt - **Offline support:** CSV parsing works offline; file upload requires network (acceptable for documents) - **Scalability:** file references keep DB size manageable; disk/S3 storage scales independently ### Trade-offs - **CSV parsing library redundancy:** each PWA may inline CSV parse logic or reuse `/shared/csv-parser.js`. Bundling xlsx.js adds ~150 KB (already in tour-pg; other PWAs can add if needed). - **Express sidecar operational burden:** adds another service to monitor/restart. Mitigated by keeping it simple (stateless, single process). - **PDF template maintenance:** each export format (monthly statement, salary slip) needs its own template. Centralizing templates in sidecar (not PWA) simplifies updates. ### Risks and Mitigations - **Risk:** User uploads malformed CSV; client-side validation misses an error; RPC fails halfway through bulk insert, leaving partial data. - **Mitigation:** RPC must be transactional (SAVEPOINT or full rollback on error). RPC returns detailed error responses (row index, reason) so client can surface to user. - **Risk:** File upload interrupted (connection lost); user tries to re-upload same file and duplicates are created. - **Mitigation:** Use UUID for file_id (no collision). PWA code should handle retry gracefully (re-uploading same file is allowed; duplicate detection is application logic, not file storage). - **Risk:** Malicious user uploads virus-infected file to `/uploads/`. - **Mitigation:** Whitelist MIME types per entity (e.g., dispatch_document accepts only PDF/images, not .exe). Optional: add antivirus scan in sidecar (ClamAV). For now, restrict upload endpoints to authenticated users only (ADR-012). - **Risk:** `/uploads/` directory grows unbounded; disk fills up. - **Mitigation:** Implement archival RPC to move old files to cold storage or delete after 30 days (configurable per entity). Monitor disk usage via cron. --- ## Related Decisions ### [ADR-072](ADR-072-proof-image-capture-annotation.md) — Proof Image Capture and Annotation Standard ADR-072 covers proof images (receipt_image TEXT, base64 JPEG, max 100 KB, canvas editor). ADR-085 covers general file uploads (file_id, multipart, ≤ 10 MB, disk storage). These are separate patterns: proof images are transaction metadata (always stored inline), while general files are blobs (stored separately, referenced by ID). ### [ADR-049](ADR-049-recce-view-files-via-hmac-signed-proxy.md) — Recce View Files via HMAC-Signed Proxy ADR-049 defines how to serve protected files (HMAC-signed proxy, time-expiring URLs). ADR-085 defines how to upload and store files (multipart sidecar, disk storage). When a file is uploaded via ADR-085, it may be served via ADR-049 if it requires access control. ### [ADR-014](ADR-014-postgrest-as-api-layer.md) — PostgREST as API Layer PostgREST is JSON-only and does not support multipart uploads. ADR-085 requires a separate Express sidecar for file uploads and PDF export (not PostgREST). This respects ADR-014 (PostgREST for CRUD) while adding specialized services as needed. ### [ADR-075](ADR-075-rpc-error-response-format.md) — Unified RPC Error Response Format Bulk import RPCs follow ADR-075 error contract: `RAISE EXCEPTION USING MESSAGE = '', DETAIL = ''`. Client code uses standard error-handling pattern: `if (!resp.ok) { const error = await resp.json(); showToast(error.message); }`. ### [ADR-070](ADR-070-ist-timezone-enforcement.md) — All User-Facing Timestamps Displayed in IST PDF export templates must convert timestamps to IST before rendering (ADR-070). Bulk import of date fields should accept both ISO 8601 and user-friendly formats (e.g., "15/06/2026"). ### [ADR-071](ADR-071-indian-number-formatting.md) — Indian Number Formatting for All Monetary Amount Inputs Bulk import of rate cards, salaries, and amounts should accept Indian-formatted numbers (e.g., "1,00,000"). CSV parser should normalize to float before sending to RPC. PDF export should render amounts in Indian format (ADR-071). --- ## References - **ADR-072** — Proof image standard (separate from general file uploads) - **ADR-014** — PostgREST limitations (no multipart support) - **ADR-075** — RPC error contract (applies to bulk import RPCs) - **ADR-049** — File proxy with HMAC signing (for serving protected uploads) - **xlsx.js library** — XLSX parsing (already bundled in tour-pg) - **Puppeteer** — server-side PDF rendering (no current implementation; recommended for future PDF features) - **Industry standard:** client-side CSV parsing is common (Airbnb, Salesforce, Google Sheets do this); server-side PDF generation is standard (all major reporting tools use this pattern) --- ## Decision Maker **hkl** (Harish) — 2026-06-27 **Changed via:** adr-kit (360lm)