← Technical blog

Python · SQLite · CSV imports

CSV imports that roll back and retry safely

How a small local support application avoids partial imports and repeat batches after a network failure.

Treat an import as one operation

A CSV importer can look simple until a request fails halfway through. If it creates the first ticket and then rejects the second, the user sees an error but the database already contains a partial result. Retrying can then create another copy. CasePilot addresses both problems with separate preview and commit endpoints, one database transaction and a persistent batch receipt.

CasePilot is a local Python, SQLite and JavaScript portfolio application. This import extension uses synthetic support requests. It is not a production client deployment. The design is intentionally bounded: administrators can import at most fifty rows and 24 KiB of UTF-8 CSV inside the existing JSON request limit.

Validate before writing

The preview parses CSV with Python's csv.reader(), so quoted commas and multiline fields remain intact. The header must contain five known fields exactly once each. Rows use the same text-length, category and priority rules as manual ticket creation. Every invalid field gets line-specific feedback, and preview creates no tickets.

Preview returns validated rows and a content hash when there are no errors. Commit parses and validates the submitted text again. It then compares the new hash with the preview hash. Editing the input after preview produces a conflict instead of importing content that the user has not reviewed. Both endpoints require an administrator session and CSRF validation.

Hash validated meaning, not file formatting

A raw file hash would treat a byte-order mark, reordered columns or different line endings as a new batch. This importer first maps each valid row to the fixed field names and trims surrounding cell spaces. It serializes that normalized data with stable JSON settings, then calculates SHA-256.

canonical = [{key: row[key] for key in IMPORT_FIELDS} for row in rows]
digest = hashlib.sha256(
    json.dumps(canonical, ensure_ascii=False, sort_keys=True,
               separators=(",", ":")).encode("utf-8")
).hexdigest()

That hash is a retry key, not a signature or proof of authorship. Row order still matters. Changing a ticket's content creates a different batch. The feature prevents repeat submission of the same normalized batch, rather than deduplicating individual tickets across every import.

Lock before checking the receipt

Commit starts BEGIN IMMEDIATE before reading the batch receipt. SQLite reserves the write transaction early, so concurrent requests cannot both inspect an absent receipt and create the batch. The receipt's content hash is also its primary key.

Inside that transaction, an existing receipt returns its original ticket IDs. Otherwise, the importer writes all tickets, their audit events and the receipt together. Each new ticket starts open and unassigned. The Python connection context commits on successful exit and rolls back on an exception. A database failure therefore leaves no partial tickets, audit trail or receipt.

The receipt lives in SQLite rather than process memory. Restarting the application does not erase retry protection. A lost network response can be retried with the same validated text and hash.

Verify the failure cases

The source includes 29 HTTP integration tests and 15 frontend tests. Import regressions force an error on the second write, submit two concurrent commits, restart the server and reorder CSV headers. They verify rollback and identical ticket IDs on retry. Frontend tests also exercise stale previews and preservation of the retry hash after a network failure.

python -W error::ResourceWarning -m unittest discover -s tests -v
node --test tests/frontend.test.cjs

Run these commands from the pinned CasePilot checkout with Python 3.11 or newer and Node installed. The HTTP suite starts a disposable local server and temporary database. Test results concern these automated scenarios. The CSV extension has no new verified browser walkthrough or mobile screenshot, and the standard-library demo server still needs production deployment work.

Source and references

About these notes

More technical notes ↗Discuss a scoped task ↗