Enhance upload performance for larger datasets

Description of the issue

Uploading large datasets to the OEP is prohibitively slow. A 2 GB dataset (~6 columns,
complex types) uploads at roughly 150–400 rows/s — about 40 hours — and users have
reported ~100 MB taking hours. Researchers producing datasets of this size (and larger)
cannot commit to the OEP as their primary data home while upload takes days, ties up
their machine, and fails without recovery options.

The cause is architectural: the only write path (Row Upload) records every row in the
Edit Journal before Applying it to the Main Table. Each row costs at least three writes
plus journal scans that grow with accumulated journal size, all executed synchronously
inside the HTTP request on a WSGI stack — and the Apply step currently runs twice per
request due to a bug.

Ideas of solution

Two shippable stages:

1. Row Upload quick wins — make the existing path faster for every client with no
   API change: index the unapplied-changes lookup, replace the N-term OR chain in the
   applied-marking step with a set-based comparison, skip scanning empty Journal Tables,
   and fix the duplicated Apply per request.

2. Bulk Upload — a new, append-only, high-throughput write path: the client streams
   a CSV file (optionally gzip-compressed) in a single authenticated HTTP request; the
   server pipes it straight into the Main Table via PostgreSQL COPY FROM STDIN,
   bypassing the Edit Journal and recording one Bulk Load Event as the audit record.
   All-or-nothing per request; target throughput ~2 GB in ≤5 minutes where the client's
   uplink allows.

User Stories

1. As a data publisher, I want to upload a multi-GB CSV dataset in minutes instead of days, so that I can rely on the OEP for my complete datasets.
2. As a data publisher, I want to upload with a single HTTP request using my existing API token, so that I need no new credentials, session protocol, or tooling.
3. As a data publisher, I want to send the CSV gzip-compressed, so that my limited uplink bandwidth is used efficiently.
4. As a data publisher, I want each upload to be all-or-nothing, so that a failure never leaves my table half-filled in an unknown state.
5. As a data publisher, I want failure responses to include the CSV line number and the database's data-level message, so that I can locate and fix bad data quickly.
6. As a data publisher, I want to declare the delimiter explicitly on each upload, so that semicolon-delimited exports (e.g. German-locale Excel) work without re-export.
7. As a data publisher, I want empty fields to always mean NULL regardless of quoting style, so that my tool's quoting settings cannot silently turn missing values into empty strings.
8. As a data publisher, I want to either include or omit the id column (columns mapped by CSV header), so that both pre-assigned-id datapackages and plain appends work.
9. As a data publisher, I want the id sequence advanced automatically after an id-bearing upload, so that my next Row Upload doesn't fail with duplicate-key errors.
10. As a data publisher, I want to split one dataset across several Bulk Uploads, so that I can stay under the size cap and recover from a dropped connection by re-sending only the failed part.
11. As a data publisher, I want a clear "too large" response naming the cap, so that I know to chunk rather than guessing why the upload died.
12. As a data publisher, I want a "busy" response with a retry hint when bulk capacity is taken, so that my client can back off and resume automatically.
13. As a data publisher, I want permission and embargo rules identical to Row Upload, so that access behavior is predictable across both paths.
14. As a client developer (e.g. oep-upload), I want a precise documented contract — header names the columns, values in PostgreSQL text-input syntax for the declared column types — so that I can convert values client-side reliably.
15. As a client developer, I want the header validated before the body streams, so that a wrong column mapping fails in milliseconds, not after gigabytes.
16. As a co-writer on a shared table, I want absurd id values rejected, so that another writer cannot exhaust the table's id sequence and break inserts for everyone.
17. As a platform maintainer, I want at most one running Bulk Upload per user plus a small global cap, so that bulk ingestion cannot starve interactive API traffic.
18. As a platform maintainer, I want stalled or trickling uploads aborted by minimum-throughput and database-side timeouts, so that no client can pin a worker and an open transaction indefinitely.
19. As a platform maintainer, I want a cap on decompressed bytes per request, so that gzip bombs and runaway streams cannot fill the database disk.
20. As a platform maintainer, I want one structured log line per upload attempt with phase timings (transfer, COPY, sequence update) and outcome, so that I can see where time goes and detect regressions.
21. As a platform maintainer, I want every attempt — success or failure — recorded as a Bulk Load Event with a status and error class, so that retry storms and abuse are visible in the admin interface rather than only in logs.
22. As an incident responder, I want the Bulk Load Event to record the id range of loaded rows, so that a poisoned or mistaken upload can be identified and deleted as a block without a backup restore.
23. As a data consumer, I want bulk-loaded data attributable to a user, table, and time, so that provenance remains auditable at the event level even without per-row history.
24. As a security reviewer, I want COPY restricted to FROM STDIN with column identifiers whitelisted against the table's real columns and safely quoted, so that no file access, program execution, or SQL injection is possible through this endpoint.
25. As an existing Row Upload user, I want the quick-win fixes, so that my current uploads get faster without changing my client at all.
26. As a future developer, I want the deliberate decisions (Edit Journal bypass, synchronous design, CSV contract deviations) recorded as decision records, so that I don't "fix" them and silently break clients or invariants.
27. As a platform operator, I want overload beyond the guards to be a monitored, non-data-losing condition, so that worst-case abuse costs availability, never data.

Implementation Decisions

Stage 1 — Row Upload quick wins (separate PR, first):

• Partial index on the unapplied-changes flag of all Journal Tables (existing and newly created ones), turning the growing sequential scans per batch into index lookups.
• The applied-marking step uses a set-based id comparison instead of building an N-term OR chain.
• The Apply step only scans Journal Tables that can contain pending changes for the operation (a pure insert no longer scans the edit and delete journals).
• The duplicated Apply per insert request is removed (it currently runs both inside the insert action and unconditionally in the view).
• All four changes are behavior-preserving; no API contract changes.

Stage 2 — Bulk Upload endpoint:

• New REST endpoint on the existing tables API, append-only. The request body is the CSV (text/csv), optionally Content-Encoding: gzip; the server streams it into COPY ... FROM STDIN without buffering the file in memory. No multipart, no chunked session protocol.
• The delimiter is a required request parameter (whitelisted values: comma, semicolon, tab). It is authoritative for parsing this request; the server never infers the dialect from oemetadata (ADR 0003).
• CSV contract: header row required and maps columns by name (order free); UTF-8 only with BOM stripped; standard double-quote quoting; empty field = NULL always (FORCE_NULL on all columns — deliberate deviation from COPY's native semantics, ADR 0003); values must be in PostgreSQL text-input syntax for the declared column types — the table always exists before upload, its schema is the sole source of truth for types; the server never creates tables, never guesses types, never transforms values.
• Header preflight before streaming: reject duplicate names, names not in the table, and missing NOT-NULL-without-default columns. Column identifiers are matched as a whitelist against the table's actual columns and quoted with the database's identifier quoting — never regex-validated.
• Versioning: rows go directly into the Main Table; no per-row Edit Journal records (ADR 0001). One transaction per request: all-or-nothing.
• New Django model BulkLoadEvent: table, user, timestamp, status (success / error class such as copy-error, size-cap, stall, permission), bytes received, and on success the row count and min/max id of the loaded rows.
• id handling: clients may include or omit the id column. After an id-bearing COPY, the sequence is advanced to max(id) in the same transaction. Uploads whose max(id) exceeds a generous sanity bound are rejected to prevent sequence exhaustion.
• Guards (ADR 0002): concurrency — max one running Bulk Upload per user plus a configurable global cap, excess rejected with 429 + Retry-After; stall — minimum-transfer-rate abort in the streaming loop plus statement_timeout/idle_in_transaction_session_timeout on the endpoint's database session; size — configurable cap on decompressed bytes per request, rejected with 413.
• Authentication and authorization are exactly the Row Upload chain: same DRF auth classes (token/basic/session with CSRF), table resolution via the platform's table registry (internal tables unreachable by construction), write permission, embargo check.
• COPY is FROM STDIN only — no code path for COPY FROM <file> or COPY FROM PROGRAM exists.
• Response: on success, the loaded row count, id range, and Bulk Load Event reference. On failure, a sanitized error carrying the CSV line number and data-level message, never internal paths or raw SQL.
• Observability shipped with the endpoint: one structured log line per attempt (user, table, bytes, rows, phase timings, outcome including guard rejections); the BulkLoadEvent table doubles as a queryable metrics source.
• No feature gate: available to every authenticated user with write permission on the target table; abuse beyond the guards is an accepted, monitored operational risk (cannot lose data).

Testing Decisions

• A good test exercises external behavior only: send an HTTP request, assert on the response, the table's contents (read back through the API), and the Bulk Load Event records. No assertions on internal call structure, SQL statements, or intermediate state.
• Primary seam — the HTTP API via the existing API test-case base classes (Django test client against the test database), following the existing row-endpoint tests as prior art. This covers: the happy path (rows land, Event carries count and id range, a subsequent Row Upload insert succeeds thanks to the sequence update), delimiter parameter behavior, empty-is-NULL semantics, gzip bodies, BOM stripping, header preflight rejections, all-or-nothing rollback with line-numbered errors, permission and embargo denials, the size cap (413), the id sanity bound, and failure Events.
• Secondary seam — the guard module: the concurrency and stall guards get direct unit tests, since parallel in-flight uploads and wall-clock transfer rates cannot be exercised through a synchronous test client. One HTTP-seam test patches the guard to "full" and asserts the 429, keeping the wiring covered at the top seam.
• The quick wins introduce no new seam: the existing row-endpoint test suite is their regression net (they are behavior-preserving).

Out of Scope

• Per-user storage quotas across the database's capacity — separate issue (a per-request cap is not a footprint control; quotas need accounting, shared-table ownership policy, and admin UI).
• Operational monitoring beyond the endpoint's own telemetry — the synthetic upload canary, pg_stat_statements/auto_explain enablement, and an alerting channel are a separate ops issue.
• Async/worker architecture (task queue, ASGI, job-status polling) — deliberately rejected for now due to funding constraints (ADR 0002); the endpoint's contract allows migrating to it later.
• Replace/truncate mode — Bulk Upload is append-only; "replace" remains a client-side composition of existing delete paths plus Bulk Upload.
• Table creation from CSV or server-side type inference/transformation — tables are created first via the existing table-creation endpoint with oemetadata.
• Resumable/chunked upload protocol — recovery is client-side splitting into independent all-or-nothing requests.
• Changes to the Row Upload API contract — quick wins are internal only.
• Client-side work in oep-upload (chunking defaults, type conversion, delimiter handling) — tracked in that repository.

Further Notes

• Throughput goal: ~2 GB in ≤5 minutes where the client's uplink allows; measure before/after with the 2 GB reference dataset (currently ~40 h).
• The decision records (ADR 0001–0003) and the domain glossary are kept in the design note accompanying this issue and can be quoted in the PR descriptions.
• Work proceeds on branch feature-2362-bulk-copy-upload in two PRs: quick wins first, endpoint second.

Workflow checklist

• I am aware of the workflow in
  CONTRIBUTING.md