Import and Export System Design

Every application that replaces a spreadsheet has to start by reading one. The first thing a new customer asks is whether they can bring their data, and the answer decides whether they adopt the thing. The last thing they ask, often years later, is whether they can get it out.

A naive importer reads a file into memory, loops over the rows and saves each one. That works for the test file with twelve rows and fails for the real one with ninety thousand, in several ways at once: memory, because the file and the parsed rows are both resident; the connection pool, because the loop holds a connection for its whole run and several concurrent imports drain the pool that every request shares; and partial failure, because row 40,000 is invalid and the first 39,999 are already committed with no record of what happened.

Then there is the part that is specific to importing rather than to bulk writing. A spreadsheet refers to things by name, not by identifier. The contacts file says the group is "Suppliers", and the database wants a group identifier. Resolving that per row is one query per row, which is ninety thousand queries, and resolving it wrongly is worse: treating a lookup failure as a missing record silently creates duplicates of things that already exist under a slightly different name.

Export has a smaller version of the same shape. Building the whole file in memory before sending it works until the result set is large, and then it does not.

The system has to be able to:

• Read a large file without holding it all in memory.
• Write in batches, not row by row.
• Limit how many imports run at once, because each one holds a connection for its whole run.
• Resolve a relationship by its natural key, with a cache so a repeated name costs one lookup.
• Distinguish "not found" from "the lookup failed", and fail the row on the second.
• Report progress while running, and a per-row error list afterwards.
• Validate each row the way the API would, rather than inventing a second set of rules.
• Stream an export, so the response starts before the file is finished.

Functional requirements

• CSV import per resource, generated with the resource.
• Streaming read, batched write.
• A concurrency gate, from IMPORT_CONCURRENCY, that makes imports take turns.
• A progress callback, so the interface can show where it is.
• Natural key resolution for belongs-to columns, with a per-run cache.
• A missing related record created; any other lookup error failing the row.
• Per-row validation against the model’s binding rules.
• An error report naming the row number and what was wrong with it.
• Export to CSV and XLSX, streamed.
• An import modal and an export menu in the admin.

Non-functional requirements

• Bounded memory. the file is streamed and the writes are batched, so a ninety thousand row import uses the memory of a batch rather than of a file.
• Imports take turns. each one holds a database connection and writes in batches for its whole run. A burst of them drained the pool that every request shares, so they are gated.
• A lookup failure is not a miss. treating a database error as "no such record" silently creates duplicates. The distinction is the difference between an import that works and one that quietly corrupts.
• One set of validation rules. rows are validated against the same binding tags the API uses. A second set of rules for imports is a second set to keep in step.
• Row-level reporting. a failed import that says "failed" is useless. It must say which rows and why, because the person fixing it has the spreadsheet open.
• Streamed export. the response starts before the file is complete, so a large export does not need the whole thing in memory or a long silence.

Numbers for a mid-sized deployment. They are here to size the thing, not to predict your traffic: change an assumption and the sums below move with it.

Assumptions

Stream the file, gate the concurrency, resolve names through a cache, write in batches, report per row.

Core components

• Concurrency gate. imports wait for each other. The waiting is reported to the interface, so a queued import looks queued rather than stuck.
• Streaming reader. one row at a time from the file, so memory is a function of the batch rather than of the upload.
• Name resolver. a closure per belongs-to column, with a map from natural key to identifier. A missing related record is created; any other error fails the row.
• Row validator. the model’s binding rules, so an imported row has to satisfy what an API-created row satisfies.
• Batch writer. accumulates and inserts in batches, inside a transaction per batch, so a failure costs one batch rather than the run.
• Progress and errors. a callback for position and a per-row error list with row numbers, which is the only form of report anybody can act on.
• Export streamer. reads in batches and writes to the response as it goes, for CSV and XLSX.

Request flow

1. 1A file arrives. It is stored as an upload like any other file, so the import can be retried against the same bytes rather than needing a second upload.
2. 2The import waits for the gate. One at a time by default, because each import holds a database connection and writes in batches for its whole run, and a burst of them drained the pool every request shares. The wait is reported, so a queued import does not look like a hung one.
3. 3Rows are read one at a time. The file is never resident, so a file ten times larger costs the same memory and ten times the time.
4. 4A belongs-to column referring to a group by name is resolved through a per-run map. A distinct name costs one lookup; the other four hundred rows mentioning it cost none.
5. 5A missing related record is created. Any other error from the lookup fails the row instead of being treated as a miss, because a database error read as "not found" silently creates duplicates of records that already exist.
6. 6The row is validated against the model’s binding rules, the same rules the API applies, and accumulated into the current batch. Each batch is one transaction, so a failure costs five hundred rows rather than ninety thousand.
7. 7A row that fails is recorded with its number and its reason, and the run continues. Stopping on the first bad row means ninety thousand rows get imported in ninety attempts.
8. 8The report lists what was created, what was skipped, and every failed row with its number and its error, which is what somebody with the spreadsheet open can act on.

Data flow

• The resolution cache is per run. A global one would be stale as soon as another import created a related record, and the staleness would present as rows attached to the wrong parent.
• A batch is a transaction. Not the whole run, because a ninety thousand row transaction holds locks for its duration, and not a row, because that is the per-row cost the batching exists to remove.
• Validation reuses the model’s binding tags. A separate rule set for imports drifts, and the drift means an import can create a row the API would have refused.
• Export streams: read a batch, write a batch, flush. The client starts receiving before the query has finished.

Core types

Design principles applied

• Generate the importer with the resource. a hand-written importer per resource diverges from the model it imports into. Generating both from the same definition keeps them in step.
• Share the code between generate and upgrade. the resolver and the gate are written by functions used by both the generator and the upgrade repair, so a new project and an upgraded one cannot differ.
• Never read an error as absence. this is the subtle one. A failed lookup treated as "not found" creates duplicates, and nothing in the run reports a problem.
• Continue and report. one bad row must not end the run. The useful output is a list of row numbers, because the person fixing it is looking at the spreadsheet.
• Gate what holds a connection. an import is a long-running database client. Letting several run at once is a pool outage that shows up as general slowness.

Patterns

• Import throughput is batch size times batch rate, and batch size has diminishing returns past a few hundred rows while lock duration keeps growing.
• The gate means import throughput does not scale with concurrent imports, deliberately. Serialised and predictable beats parallel and pool-exhausting.
• Name resolution is the cost that scales with distinct values rather than rows, which is why the cache turns 180,000 queries into 400.
• A very large import belongs in a background job rather than a request, so the browser is not what has to stay open for four minutes.
• Export streaming means the response size is unbounded without the memory being unbounded, which is the only way a hundred thousand row export works.
• XLSX cannot stream as freely as CSV because of its structure, so very large exports should prefer CSV and say so.

What breaks first

• Connection pool exhaustion. the original failure. Several concurrent imports each holding a connection for minutes present as the whole application being slow.
• Duplicate creation from misread errors. a lookup error treated as a miss creates a second "Suppliers" group, and nothing reports anything wrong.
• A run that fails halfway. batches are committed, so a failure at row 60,000 leaves 59,500 rows written and the user unsure what to do with the file.
• Spreadsheet data quality. dates in three formats, numbers with thousands separators, trailing spaces, and a header row that does not match the columns.
• Browser-held imports. a four minute import in a request is a four minute request, and a closed tab or a proxy timeout ends it.

What to do about it

• Keep the gate, report the wait. serialising is correct. Telling the user they are queued is what makes it acceptable.
• Distinguish every lookup outcome. found, not found, and failed are three results. Collapsing the last two is how an import corrupts data quietly.
• Make imports idempotent on a natural key. upsert on the key rather than insert, so rerunning the same file after a half-failure converges instead of duplicating.
• Validate the whole file first. a dry run reporting every bad row before anything is written. The user fixes the spreadsheet once rather than discovering problems in stages.
• Run large imports as a job. upload, queue, and let the user close the tab. Progress comes from polling the run, not from a held request.