Export transactions as CSV for the ledger importer
Go · Go · advanced · modification
Adds a CSV export of an org's transactions for a date range, used by the ledger importer and by finance from the Transactions page. Rows go from the query through `encoding/csv` straight into the response, so a full year never sits in memory, and the dates reach Postgres as `date` values, so the range is exactly the days asked for. Errors before the CSV starts are logged with the org id and answer the usual JSON error.
Go 1.22 service, standard library `net/http` (method-pattern `ServeMux`) and `log/slog`, served over HTTP/1.1 behind a TCP load balancer, with no panic-recovery middleware (net/http's own handler recovery applies). Postgres through `database/sql` with the pgx v5 stdlib driver: rows stream from the server as `rows.Next` reads them, a `date` column scans into a `time.Time` at midnight UTC, and a query can fail after it has already returned rows (a statement timeout, a failover, a lost connection), which `database/sql` reports from `rows.Err()`. All `transactions` columns are `NOT NULL`: `booked_on` is a `date`, `amount_cents` a `bigint` (negative for money out), `currency` a lowercase ISO code, and an index on `(org_id, booked_on, id)` exists. `auth.FromContext` returns the signed-in `auth.Member` (`OrgID int64`). `Handler` (with its `DB *sql.DB`), `writeJSON` and `writeError` (`{"error": code}` with the given status) exist in `handler.go`, unchanged.
Requirements
- `GET /api/transactions/export?from=YYYY-MM-DD&to=YYYY-MM-DD` returns the caller's org's transactions booked from `from` to `to`, both days included, as a CSV download ordered by booking date, then id: a header row `id,booked_on,description,amount_cents,currency,category`, then one row per transaction (just the header when there are none). Values are written as stored, quoted by `encoding/csv` where needed; the importer reads them verbatim, so there is no spreadsheet formula escaping. Any signed-in member of the org may export.
- Missing or malformed dates, a date outside the years 2000 to 2099, `to` before `from`, or a range covering more than 366 days (both ends counted) answers 400 `{"error": "invalid_range"}`. A query that cannot be started answers 500 `{"error": "internal"}`, with the error logged.
- An org's year can hold up to 50,000 transactions, so rows are written to the client as they are read, never collected in memory or on disk first.
- The ledger importer downloads exports unattended and accepts any download that ends normally with a 2xx status as the complete range; it reads no trailers. If reading the transactions fails, the client must see the download fail, never a download that ends normally. Every such failure is logged with the org id.
Files touched
- internal/transactions/routes.go
- internal/transactions/export.go
--- internal/transactions/routes.go
func Routes(mux *http.ServeMux, h *Handler) {
mux.HandleFunc("GET /api/transactions", h.List)
+ mux.HandleFunc("GET /api/transactions/export", h.Export)
}
--- internal/transactions/export.go
+package transactions
+
+import (
+ "database/sql"
+ "encoding/csv"
+ "errors"
+ "log/slog"
+ "net/http"
+ "net/url"
+ "strconv"
+ "time"
+
+ "example.com/ledger/internal/auth"
+)
+
+const (
+ dateLayout = "2006-01-02"
+ maxRangeDays = 366
+)
+
+const exportQuery = `
+SELECT id, booked_on, description, amount_cents, currency, category
+FROM transactions