How to build a CSV importer for a SaaS application

Decide whether you need a reusable feature

An internal uploader can assume a trained operator and a controlled file format. Customer onboarding usually cannot. A customer may not know which sheet is authoritative, why an enum value was rejected or which records already exist. The UI needs to make those decisions visible without asking the customer to understand your schema.

Existing parsers and open-source import interfaces can save useful work. Evaluate their behavior with your actual file cases and read where responsibility passes back to your application. A callback containing validated rows is a useful component; it does not define your database transaction, tenant authority or recovery procedure.

If the immediate task is a Supabase admin import, use the built-in import walkthrough first.

Start with a written import contract

Define what one row represents and whether the operation inserts, updates or upserts. Those are different contracts. For updates, the identity match and permitted changes need their own approval. Avoid a generic upsert merely because it makes duplicate errors disappear.

A compact contacts contract could say:

Operation: insert new contacts
Target: one approved application table
Source fields: external_contact_ref, email, display_name
Tenant: supplied by an authorized import job
Primary key: generated by the database
External identity: unique within tenant + source system
Missing display name: NULL
Existing external identity: report conflict; do not overwrite
Accepted set: frozen after review; edits require revalidation

Also record the supported file envelope, transformation rules, schema version, error policy and evidence needed for completion. This is an engineering artifact. It does not need a configuration language or a compiler.

A version ties a preview to the behavior that will execute. If a mapping or normalization rule changes after confirmation, invalidate the old preview. If the destination schema changes, re-evaluate compatibility rather than assuming the previously reviewed columns still have the same meaning.

Set file and parsing limits

Choose where parsing happens. Browser parsing can keep an exploratory file local and make previews responsive. Server parsing can support jobs that survive closing the tab. Both approaches need resource limits; browser validation must be repeated authoritatively before a write.

Set limits for bytes, records, columns, cell length, processing time and retained error details. For XLSX, compressed file size alone does not bound decompressed size, sheet count or cell count. Stream or offload work when appropriate, and stop with a useful message when the file or processing limits are exceeded.

For uploads, authenticate access, generate your own storage key and isolate files by authorized job. Do not trust a filename as a path. Make retention explicit for abandoned uploads, completed jobs, failed jobs and backups. A file stored “temporarily” without a deletion path is stored indefinitely in practice.

CSV-only is a legitimate first version. If supporting workbooks, let the user choose the sheet and header row, document formula/cached-value behavior, and do not execute spreadsheet macros or external links. Tell the user what was ignored.

Preserve values while parsing

Use a maintained parser rather than splitting on commas or newlines. RFC 4180 describes quoted delimiters and line breaks, but real exports still differ in encoding, delimiters and blank handling. Accept a documented set and show the detected interpretation before execution.

Keep identifiers as strings. Automatic numeric conversion can remove leading zeros or round a long account number. Parser options such as Papa Parse’s dynamic typing, header handling and error reporting are documented in its API reference; choose them deliberately.

Track a source record identifier through corrections and writes. Physical line numbers help locate parser errors, but a quoted multiline record can span several lines. A result should point back to the source record without pretending those are always interchangeable.

Allow mapping only to writable fields

Present destination fields that the source is actually allowed to supply. Suggestions can save typing; they should not create new write permissions. Require a resolution for missing required fields, conflicting mappings and excluded source columns.

A source header like “Owner” might mean a salesperson, a workspace or the creator of the old record. Show the destination description and sample values, not just a similarity score. Keep the confirmed mapping with the job so support can explain a result later.

Reject unknown keys again at the server. Construct database writes from the approved destination fields, never from arbitrary request keys. Parameterize values and use a reviewed identifier allowlist where dynamic identifiers are unavoidable. A filename or header must not become executable SQL.

Classify defaults, identities and generated expressions separately. An ordinary default does not make a field protected. See database-supplied values during CSV import for an executable example.

Separate normalization from business decisions

Normalization applies an approved interpretation. A business decision chooses that interpretation. Trimming an agreed identifier format can be mechanical; deciding that “former client” maps to “inactive” needs domain authority. Treat an inferred timezone, enum replacement or duplicate winner as a proposed change until approved.

Validate in layers, with a useful error at the earliest reliable point:

1. File structure: supported encoding, records, headers and limits.
2. Field values: requiredness, types, ranges and accepted formats.
3. Relations within the file: duplicates and inconsistent repeated references.
4. Business rules: allowed statuses, identity semantics and permissions.
5. Destination checks: applicable constraints, reference existence and actual write behavior.

A destination lookup is a fact about a moment in time. Another transaction can insert the same unique value between your preview and execution. Keep the constraint as the final arbiter and handle its failure; a green preview is not a reservation.

Errors should identify the record, field, rule and possible correction without logging sensitive values. Group repeated issues so a user can fix a systemic problem once. A decision to exclude records needs a visible count and reason; silent row dropping creates an unexplained mismatch later.

Show the proposed outcome before the write

A useful preview shows accepted and excluded counts, required corrections, approved transformations, target tenant and operation. State which checks ran against a live destination and which were static or local.

Call a no-write validation pass exactly that. A transaction that performs real writes and then rolls back is not necessarily a side-effect-free dry run: sequence allocations and externally initiated effects may not reverse. Rehearse write behavior in an isolated environment designed for that purpose.

Keep confirmation tied to the file, map, rules and accepted record set. Editing a cell after confirmation must create a newly validated proposal. For large inputs, virtualize the correction grid if necessary, while preserving keyboard access, field labels and a summary outside the virtualized rows.

Resolve tenant authority and foreign keys

Bind the job to an authorized tenant and operator. Supply tenant-owned fields through the trusted writer. A browser’s hidden input is still browser-controlled input. If RLS is part of the design, test with the real execution role and policy set; service-role access changes that boundary.

A reference such as “Company 42” usually identifies a row in the old system. Resolve an approved external key within the correct tenant/source namespace. Zero matches and several matches are different failures; choosing the first row hides ambiguity. Keep final foreign-key and tenant-consistency constraints in place.

The detailed guides on tenant authority and foreign-key resolution show these boundaries. Multi-table ordering and cycles require an explicit relational plan; they are not column-mapping features.

Commit writes and their receipt together

For a small bounded import, a single transaction is often the simplest result model. For larger work, choose batches based on measured transaction duration, memory, locks and database load. A batch size copied from a tutorial is not a capacity plan.

Give each job and batch a stable identity. An identical retry should return the same recorded outcome; a changed payload using the same identity should be rejected. The receipt and destination writes need to commit in the same transaction if the receipt is to prove those writes.

BEGIN
  authorize access to this job
  lock the existing job row FOR UPDATE
  look up receipt for (job_id, batch_number)
  if receipt exists:
    require matching payload hash and contract version
    return recorded result without inserting again
  else:
    check job state and the next expected batch
    validate and insert the approved rows
    insert receipt with identity, hash and result
    advance the job's committed progress
COMMIT

This is a design sketch, not executable SQL. The job-row lock serializes competing attempts for that job, and a unique receipt key provides an additional database constraint. Authorization also applies when reading an existing receipt. Define a stable hash representation that includes the accepted values and relevant contract identity.

Do not put a receipt in one database and target writes in another, then call the pair atomic. If you must cross systems, specify a reconciliation protocol for the gap. Likewise, a queue acknowledgement and a database commit are not one transaction merely because they occur in the same function.

If notifications or webhooks must follow a commit, a transactionally written outbox can record pending delivery. Delivery still needs its own idempotency and retry rules. Keep imported row contents out of routine event payloads.

Represent partial and unknown outcomes honestly

A lost response is not proof that the transaction failed. Query a durable receipt or retry the identical batch through the idempotent path. If that evidence cannot be reached, show an unresolved outcome and stop a blind retry-all.

If batches 1–4 committed and batch 5 failed, report the committed subset and the failed or unattempted remainder. A cancel button may stop future work; it cannot promise to undo committed batches. An undo operation would need a separately reviewed compensating change, including effects on references and subsequent edits.

Be explicit about row-tolerant behavior. “Skip invalid rows” means the customer accepts a partial import and can identify the skipped records. All-or-nothing batching is a reasonable alternative, especially when partial domain objects would be misleading. Neither choice should be hidden behind a generic success message.

Background jobs become useful when request timeouts, browser disconnects or processing time make synchronous execution unsuitable. Persist the state needed to resume, bound retries and provide a way to inspect or stop stuck work. A queue does not supply idempotency automatically.

Define completion and a support path

Reconcile the accepted set with committed records using stable keys, then compare important fields and domain invariants. Counts are the first check. They cannot detect equal-sized sets containing different records. The verification guide shows that failure with SQL.

Log job identifiers, versions, counts, error categories, durations and durable state transitions. Redact database errors before presenting them if they can reveal another tenant’s data. Restrict access to diagnostic artifacts and record when they should be deleted.

Offer a useful result page after the tab closes: what was accepted, what committed, what remains unresolved and what the operator can do next. Exported correction files need spreadsheet-formula handling too; quoting CSV syntax alone does not stop a spreadsheet from interpreting a dangerous cell as a formula.

Before release, exercise a lost commit response, two simultaneous submissions of the same batch, a changed payload under an old retry key, a revoked tenant permission, a schema change after preview and a cancelled job with committed batches. These cases tell you more about the importer than a large clean demo file.