Operations CSV Imports: Schema Contracts, Row-Level Deduplication, and Database Write Reconciliation

The same CSV may look “normal” in Excel, yet once it enters a service it may turn 00123 into 123, treat 1,20 as two fields, interpret an empty string as a deletion instruction, or parse 03/04/26 as two different dates. More dangerously, a job may crash at 70%; the operator then reuploads a file with one edited row, and the system cannot explain which customers have already been created, which entries are merely duplicates, or which were overwritten by the later upload.

The Secure File Upload Pipeline handles size, type, malicious content, and quarantined storage. This article begins after a file has passed security scanning and discusses only the verifiable mapping from spreadsheet semantics to business records. Training Data Schema Evolution addresses training-corpus compatibility; here, we process a finite set of business rows submitted by operations staff and do not share its strategy of “make the best guess when encountering an unknown column.”

Import Contracts Must Fix the Dialect and Business Types

RFC 4180 defines a general format for commas, quotes, and CRLF, but real-world files also require explicit definitions for encoding, BOM, delimiter, header, line endings, quoting, null-value tokens, maximum logical record size, and multiline fields. Publish csvImportContract.v1, declaring for each column a stable field ID, display aliases, business type, whether it is required, default value, time zone, decimal scale, enum version, and normalization rules. Dates accept only the ISO formats listed in the contract; monetary values use decimal minor units; identifiers are always read as strings.

schema: csvImportContract.v1
contract: customer-catalog@6
encoding: UTF-8
delimiter: ","
unknownColumns: REJECT
duplicatePolicy: QUARANTINE_CONFLICT
fields:
  customer_external_id: {type: string, required: true, trim: ascii-edge}
  plan_code: {type: enum, vocabulary: plans@12}
  effective_at: {type: datetime, format: RFC3339}

Unicode NFKC may fold visually similar characters together. That is appropriate for some search fields, but it may change external IDs. Each field must therefore independently declare NFC, NFKC, or no normalization while also retaining the original value. A contract upgrade must not change the interpretation of a sealed import. When old files require new semantics, create a migration generation that outputs the old value, new value, and reason; do not rerun the import and overwrite the old staging data.

Every Logical Row Gets a Stable Disposition

The ingestion endpoint first computes the original file's SHA256 and stores the object generation, tenant, contract digest, uploader, byte count, and logical row count. The parser correctly handles newlines inside quoted fields and reports both the logical row number and raw byte range for each error location. Parsing results are written to isolated staging, uniquely keyed by (importId, logicalRow). When a worker restarts and writes the same row again, it returns the original receipt if the canonical payload hash is unchanged; otherwise, it routes the row to ROW_MUTATION_CONFLICT.

After normalization, generate rowKey from the business key defined in the contract. Within the same file, rows with the same key and identical payloads are marked DUPLICATE_IDENTICAL, and only one candidate is retained. Rows with the same key but different payloads are marked DUPLICATE_CONFLICT, and the entire group is quarantined rather than resolved with “last row wins.” Cross-file deduplication uses the source namespace, business key, and import mode; it does not substitute a whole-row hash for identity. INSERT_ONLY, UPSERT_IF_VERSION, and REPLACE_SNAPSHOT are distinct contracts and must be selected explicitly by the operator.

The preview report provides counts and samples for the VALID, INVALID_SCHEMA, UNKNOWN_REFERENCE, DUPLICATE_IDENTICAL, DUPLICATE_CONFLICT, and STALE_TARGET buckets, and computes a candidateRoot over the valid canonical rows. Approval is bound only to the import ID, contract, mode, candidate root, and count; any change to the original file or referenced vocabulary requires a new preview. This approval only permits verified rows to be promoted into business tables and does not trigger external actions such as emails or refunds. It remains separate from the effects control plane for Bulk Customer Operations.

Promotion and Reconciliation Must Balance Exactly

INSERT_ONLY and UPSERT_IF_VERSION can be promoted by reading staging in small batches and writing with natural-key and expected-version conditions; the business record, row receipt, and outbox are committed in the same transaction. REPLACE_SNAPSHOT cannot modify the active set row by row: first build a complete immutable dataset generation, validate the candidate root and full membership, and then atomically switch the active pointer with CAS. Old rows absent from the new file become invisible to new readers only after the switch. Read requests pin a generation, and the old generation is reclaimed only after it has drained. After a connection interruption, confirm the outcome through the receipt or active generation. PostgreSQL Transaction Isolation provides only the implementation foundation.

The final importReconciliation.v1 must satisfy the following: the total number of logical input rows equals the mutually exclusive sum of blank, invalid, duplicate, skipped, inserted, updated, and quarantined rows; every candidate row has exactly one terminal-state receipt; and a target digest recomputed from canonical business keys matches the committed root or active dataset root. When exporting an error CSV, escape as plain text any cell beginning with = + - @ to prevent spreadsheet formula injection. Delete raw PII, error samples, and staging according to the purposes and retention periods defined in AI Data Privacy Compliance, and generate a deletion receipt.

Fault tests cover UTF-8 BOMs, invalid bytes, embedded newlines, duplicate headers, Unicode composition/decomposition, conflicting rows with the same key, vocabulary upgrades, crashes during small-batch promotion, two concurrent promoters, interruptions during snapshot construction, and crashes before and after the pointer CAS. The targets are zero cross-tenant writes, zero additional effects when the same row is promoted repeatedly, zero readers observing a partial snapshot, all counts balancing exactly, and 100% of error locations mapping back to a byte range.

Release gates require parsing results for 10,000 generated dialect fixtures to match an independent oracle exactly; a reconciliation discrepancy of 0 for a 100,000-row import; an unchanged target digest across five repeated executions; zero false acceptances of unknown columns, invalid dates, and conflicting keys; and p95 preview latency below 90 seconds for 100,000 rows. During rollout, first generate previews only, then enable INSERT_ONLY for internal catalogs, and finally allow version-guarded upserts. If the conservation invariant fails or the error rate spikes, halt promotion and point the entry alias back to the old contract. For rows already committed, generate an explicit reverse migration from their receipts; never delete staging and pretend the entire import never happened.