Report Export (PDF + CSV)

Specification for exporting Sondely reports and data as files: a funder-ready PDF impact report and CSV exports of responses, impact data, and organization data.

Why this is the priority: in the Montreal nonprofit market, the deliverable is the report. Centraide and PSOC filings are documents, not dashboards. A dashboard the funder cannot receive does not close the loop. With survey-content translation already shipped, the market analysis rates this the single most important remaining product gap, alongside the platform localization release it ships with.

Definition of done: from the impact report screen and the responses screen, an org admin can download (a) a polished PDF impact report and (b) CSV files of the underlying data, in one click, in any enabled platform language, with correct accents, verified headless from the CLI.

1. Current State (updated July 29, 2026)

The core of this spec shipped in PR #19 (merged July 25), together with the platform localization work. This section replaces the original "entirely net-new" analysis.

1.1 Implemented

• backend/app/services/report_export.py: build_impact_csv(report, locale) and build_impact_pdf(report, org, locale). The PDF is generated with reportlab (see the amended decision in section 2), branded with the organization's brand_color (with a safe fallback on bad values), labels localized EN/FR/ES.
• Endpoints: GET /api/v1/impact/{survey_id}/export.csv and GET /api/v1/impact/{survey_id}/report.pdf (?lang=), OWNER/ADMIN only, streamed with Content-Disposition: attachment and sanitized filenames (_safe_filename).
• Org branding fields live: logo_url, brand_color, and locale were added to the live organization model.
• Frontend: ImpactReportPage is fully localized and has "Export PDF" / "Export CSV" buttons downloading via authenticated fetch-to-blob in the active locale.
• Tests: 11 backend export tests (CSV rows and labels, %PDF header, bad-brand-color fallback, empty report, FR/ES variants). Live-verified: PDF returns 200 %PDF in fr and en; CSV returns localized headers.
• reportlab==5.0.0 pinned in backend/requirements.txt.

1.2 Remaining work (the open scope of this spec)

• CSV hardening: the shipped CSV has no UTF-8 BOM and no injection sanitization (question text is written raw). Both are mandatory (section 2) and small.
• Responses CSV (/surveys/{id}/responses/export) and members CSV (/organizations/{org_id}/members/export) do not exist yet, nor their export buttons on SurveyResponsesPage and OrganizationDetailPage.
• Logo in the PDF header: the logo_url field exists but the PDF does not render it yet (slot is reserved in the template).
• scripts/e2e_export.py has not been written; export coverage is unit-test plus live manual verification today.
• Dashboard/org-analytics export and scheduled emailed reports remain out of scope (unchanged).

2. Architecture Decision

CSV: server-side streaming (no library needed)

Python stdlib csv writing to an in-memory buffer, returned with text/csv; charset=utf-8 and a Content-Disposition: attachment filename. Requirements that are easy to get wrong:

• UTF-8 BOM () prefix so Excel opens French accents correctly. This market lives in Excel; a CSV that mangles "bénéficiaire" is a failed feature.
• CSV injection guard: any cell starting with =, +, -, or @ is prefixed with ' before writing. Survey answers are user input; this is mandatory.
• Stable, documented column order; ISO 8601 dates; the respondent email only in exports where the caller's role may see PII (same rules as the source endpoints).

PDF: server-side generation from the ImpactReport data (implemented with reportlab)

Generate the PDF on the backend from the ImpactReport data, recreating the report layout (stats, per-question table, delta bars) as a print document. The spec originally proposed WeasyPrint + Jinja2; the implementation (PR #19) chose reportlab, which keeps every property that motivated the server-side decision while avoiding system-library changes to the deploy image.

The options considered:

Backend dep (shipped): reportlab==5.0.0. No Dockerfile changes were needed, which is the main reason reportlab won over WeasyPrint. Dev-only test dep for deeper assertions if wanted: pypdf for text extraction.

What the PDF contains (v1)

One template: the Impact Report. Sections, in order:

1. Header: organization name and brand color (shipped); logo rendering from the live logo_url field (remaining), report title, survey title, generation date, period covered (first baseline to latest attempt).
2. Key stats row: total respondents, included in comparison, excluded (single attempt), baseline average, latest average, overall delta with direction.
3. Narrative summary (the existing auto-generated summary string).
4. Per-question breakdown table: question, baseline avg, latest avg, delta, delta %, direction, respondent count.
5. Visual comparison: horizontal paired bars (baseline vs latest) per question, inline SVG, one color for baseline, one for latest, consistent with the app's token palette.
6. Footer: "Generated by Sondely" + sondely.com + page numbers.

Dashboard/org-analytics PDF is explicitly out of v1 scope (org analytics itself is unbuilt).

3. API

All endpoints require org OWNER/ADMIN via the existing get_survey_with_org_access dependency, mirroring the JSON endpoints they shadow.

GET /api/v1/impact/{survey_id}/export.csv?lang=xx                # shipped (PR #19)
GET /api/v1/impact/{survey_id}/report.pdf?lang=xx                # shipped (PR #19)
GET /api/v1/surveys/{survey_id}/responses/export?format=csv      # remaining: one row per response
GET /api/v1/organizations/{org_id}/members/export?format=csv     # remaining: members + roles + status

The shipped endpoints use extension-suffixed paths rather than a format query parameter; the two remaining CSV endpoints should follow the shipped convention (/responses/export.csv, /members/export.csv) for consistency.

• lang optional, validated against the enabled-language registry from the localization spec; defaults to the requesting user's user.locale, else en.
• Responses are synchronous (the impact computation is already on-demand and fast at current scale). If generation ever exceeds a few seconds at real org sizes, move to a background task + notification; out of scope now.
• Filenames: sondely-impact-report-{survey-slug}-{YYYY-MM-DD}.pdf, sondely-responses-{survey-slug}-{YYYY-MM-DD}.csv. ASCII-safe slug plus RFC 5987 filename* for accented survey titles.
• New Mongo queries must not sort on unindexed fields (COSMOS_SORT_GUARD enforces this in CI); reuse the existing impact/responses query paths, which already comply.

4. Frontend

• ImpactReportPage: "Download PDF" and "Download CSV" buttons (design-system Button, download icon) in the page header. Fetch with auth header → blob → object-URL anchor click. Disabled state + spinner while generating; Alert on failure.
• SurveyResponsesPage: "Export CSV" button, same pattern.
• OrganizationDetailPage members tab: "Export CSV".
• Buttons are localized like everything else (they land after localization phase 1 introduces the catalog; until then EN literals are acceptable only if the localization work has not yet merged, and they convert to catalog keys the moment it has).
• Note for implementation: ImpactReportPage uses raw fetch (not react-query); follow the file's existing style. No new frontend dependencies.

5. Localization of the PDF and CSV

The export is the artifact this market shows its funders, so it must be exemplary in French.

• All template chrome strings (section titles, table headers, footer) live in per-locale dicts in the backend, generated and reviewed by the same UI translation agent defined in Platform Localization §3.6, keyed by the same enabled-language registry.
• Dates formatted per locale (e.g. "29 juillet 2026"). Numbers use locale decimal separators in the PDF; CSV keeps machine-readable dots and ISO dates regardless of locale (documented in the file header row).
• Direction labels (improved/declined/unchanged) and quality bands reuse the existing translated strings.
• v1 ships EN; FR and ES activate with the localization release (same enabled-language gate). The template must be locale-parameterized from day one so this is a catalog drop, not a refactor.

6. Testing (headless, CLI, per repo convention)

Unit (pytest, backend/tests/test_export.py)

• CSV: correct header row and column order; BOM present; accents intact; injection-prefix sanitization (=SUM(...) becomes '=SUM(...)); quoting of commas/newlines in answers.
• PDF: output starts with %PDF; pypdf text extraction contains survey title, org name, summary text, and a known per-question row; page count ≥ 1; generation with FR locale dict produces FR section titles.
• Auth: 403 for MEMBER role and anonymous on all export endpoints; 404 for foreign org.
• format validation: 400 on unknown format; lang validation against the registry.

Integration (pytest, backend/tests/integration/)

• Full path against Mongo service container: seed survey + 2-attempt respondents, call the export endpoints, assert headers (Content-Type, Content-Disposition) and parse the CSV back to the seeded values. Runs under COSMOS_SORT_GUARD=true.

E2E (scripts/e2e_export.py, patterned on scripts/e2e_take_flow.py)

• Against the local stack (backend :8947 USE_INMEMORY_DB=true, frontend :6291): register → org → survey → two attempts by one respondent → download PDF and CSV via the API with the admin token → assert PDF magic bytes and CSV row count → click-path check that the buttons exist on ImpactReportPage. PASS/FAIL per step, exit 0 only on full pass.

CI

• weasyprint + jinja2 in requirements.txt install in all three CI jobs automatically; backend/Dockerfile gains the system libraries (verify the deploy image builds in the develop deploy workflow).
• No new pytest markers, or register them in pytest.ini (--strict-markers is on).

7. Phasing (vertical slices, each with its own passing tests)

1. CSV slice. (Partial: impact CSV shipped in PR #19; responses and members CSVs, BOM, and injection guard remaining.) Responses CSV + impact CSV + members CSV, with sanitization and BOM, buttons on the three pages, unit + integration + E2E export steps green. Ships value in days, unblocks "can you send me the data" asks immediately.
2. PDF slice. (Shipped in PR #19, with EN/FR/ES labels already localized; logo rendering remaining.) WeasyPrint + template + Dockerfile system libs, impact-report PDF endpoint + button, pypdf-based tests, E2E extended. This is the funder deliverable.
3. Localized exports. (Largely shipped with the PDF slice; locale-formatted dates to verify during hardening.) Locale dicts through the translation agent, FR/ES activation with the localization release, locale-formatted dates, FR PDF asserted in tests.
4. Later (separate specs/features): org logo in the header once branding ships; dashboard/org-analytics export once org analytics exists; scheduled emailed reports (template and ACS attachment path are already compatible).

8. Acceptance Checklist

• Impact report downloads as a valid, well-typeset PDF with stats, narrative, per-question table, and bar visuals.
• Responses, impact, and members CSVs download with BOM, correct accents, injection sanitization, documented column order.
• All export endpoints enforce OWNER/ADMIN and validate format/lang.
• Filenames are date-stamped and accent-safe.
• FR/ES exports activate with the localization release; dates and chrome localized; CSV stays machine-readable.
• python scripts/e2e_export.py exits 0 against the local stack; unit + integration suites green in CI; deploy image builds with WeasyPrint libs.

9. Relationship to Other Specs

• Platform Localization: shared translation agent, enabled-language registry, and user.locale; the two specs ship as one gating release for Montreal.
• Impact Comparison: the data model and calculations the PDF renders; this spec adds no new computation.
• Montreal Nonprofit Market Analysis: the market case (§5.1) for why export gates paid pilots.
• Use Cases UC-017: the members/org CSV is also the first building block of Law 25 data-access-request support.