Export customers as CSV
Java · Spring Boot · intermediate · modification
Adds the CSV export behind the Export button on the customers page: `GET /api/customers/export.csv`. Rows come out in id order and are read in keyset batches of 500 into a small `CustomerCsvRow` projection, so a large org streams out with bounded memory instead of being loaded in one go. `CustomerCsvExporter.cell` does the escaping: a value starting with one of the formula characters OWASP lists (`=`, `+`, `-`, `@`, tab, CR) gets an apostrophe prefix, then RFC 4180 quoting applies. The file starts with a BOM so Excel reads it as UTF-8, and it downloads as an attachment named with today's date. Unit tests cover the escaping rules and the paging.
CRM backend on Spring Boot 3.3 (Spring Framework 6.1, Spring Data JPA 3.3 with Hibernate 6.5, Java 21) on PostgreSQL 16, served by the embedded Tomcat, with `spring.jpa.open-in-view=false` set in `application.yml`, so each repository call borrows a pooled connection only for its own query. Existing and unchanged: `SecurityConfig` requires an authenticated session for every `/api/**` request (401 otherwise) and puts a `CurrentUser` record (`id`, `orgId`, `email`) in the security context as the principal, which every controller reads with `@AuthenticationPrincipal`. `Customer` (table `customers`) has an identity `id` (positive, never changes), `orgId` (never changes), `name` and `email` (not null), `company` and `phone` (nullable) and `createdAt` (an `Instant` the server sets when the customer is created, not null); customers are deleted outright, there is no soft delete. Text columns hold valid Unicode. An index on `customers (org_id, id)` exists. `CustomerView.from` is unchanged. The Export button in the web app is a plain link to the new endpoint.
Requirements
- `GET /api/customers/export.csv` downloads the customers of the signed-in user's org as a CSV file. Any signed-in member of the org may export (members already see every customer of their org in the app). The file has a header row `id,name,email,company,phone,created_at`, then one record per customer of the org, ordered by `id`. A null `company` or `phone` is an empty field; `created_at` is written in UTC as `yyyy-MM-ddTHH:mm:ssZ`, truncated to whole seconds.
- The format is RFC 4180 with a comma separator: every record ends with CRLF, the last one included; a field that contains a comma, a double quote, a CR or an LF is enclosed in double quotes with each double quote doubled, and other fields are written bare. The file is UTF-8 and starts with a byte order mark so Excel picks up the encoding. The response is `text/csv` with `Content-Disposition: attachment` and the file name `customers-YYYY-MM-DD.csv` (today's UTC date). Users open the file in Excel or Google Sheets with comma as the separator, as the help text next to the button says; programs set to another separator are out of scope.
- Names, emails, companies and phone numbers are typed by users and sent by integrations, so a text value can be any Unicode text, including text a spreadsheet would read as a formula. The rule agreed with security, from OWASP's CSV injection guidance, is the contract for this export: a text value whose first character is `=`, `+`, `-`, `@`, a tab or a carriage return gets a single quote `'` prepended, and the result is then quoted as requirement 2 says if needed. A phone number such as `+49 30 1234567` therefore exports as `'+49 30 1234567`, which product accepted. All other values are exported exactly as stored.
- An org can hold up to 200,000 customers, so rows are read and written in batches: memory is bounded by the batch size, not the org size, and the response is written while the batches are read. The export is not a snapshot: a customer created or deleted while it runs may or may not be in the file, but every customer that exists for the whole export appears exactly once. If the database fails partway, the user gets a failed or cut-off download and retries; no completeness marker is needed.
- Ship unit tests for the escaping and the batching.
Files touched
- src/main/java/com/acme/crm/customer/CustomerRepository.java
- src/main/java/com/acme/crm/customer/CustomerCsvRow.java
- src/main/java/com/acme/crm/customer/CustomerCsvExporter.java
- src/main/java/com/acme/crm/customer/CustomerController.java
- src/test/java/com/acme/crm/customer/CustomerCsvExporterTest.java
--- src/main/java/com/acme/crm/customer/CustomerRepository.java
package com.acme.crm.customer;
+import java.util.List;
import java.util.Optional;
+import org.springframework.data.domain.Limit;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
+import org.springframework.data.jpa.repository.Query;
+import org.springframework.data.repository.query.Param;
public interface CustomerRepository extends JpaRepository<Customer, Long> {
Optional<Customer> findByIdAndOrgId(Long id, Long orgId);
+
+ @Query("""
+ select new com.acme.crm.customer.CustomerCsvRow(
+ c.id, c.name, c.email, c.company, c.phone, c.createdAt)
+ from Customer c
+ where c.orgId = :orgId and c.id > :afterId
+ order by c.id""")
+ List<CustomerCsvRow> findCsvRows(@Param("orgId") Long orgId, @Param("afterId") long afterId, Limit limit);
}
--- src/main/java/com/acme/crm/customer/CustomerCsvRow.java
+package com.acme.crm.customer;
+
+import java.time.Instant;
+