Importing 3.57 Million Parts Without Pretending the CSV Was True

The download was 82.4 MB. Small enough that I did not think very hard about it.

Then I extracted it.

The archive became a 3.70 GB CSV containing 4,631,216 electronic-part rows. A few days earlier I had finished rebuilding mpn.cc from its prototype architecture. This file was going to be the first serious test of whether the new system was actually better or merely cleaner.

PostgreSQL could ingest the file quickly. That was never the hard part.

The hard part was importing millions of claims without quietly turning them into millions of facts.

The Seductive Version of the Import

The source was an official manufacturer export. Every row looked structured. There were MPNs, descriptions, product families, package information, and datasheet URLs.

The seductive implementation was about twenty lines long:

stream CSV
normalize fields
INSERT ... ON CONFLICT UPDATE

It would have produced an impressive catalog count by the end of the day. It also would have bypassed most of the reasons I had rebuilt mpn.cc.

“Official manufacturer data” is good provenance, but it does not answer every catalog question. Is the MPN an exact orderable identity? Does the source's product family map to a reviewed category? Is the manufacturer already a verified organization in our registry? Does the datasheet actually cover this variant? Should an incomplete record be public, private, or rejected?

An import that cannot answer those questions is not a catalog operation. It is a bulk copy.

So I made the CSV go through the same boundaries as every other source.

What Survived the Boundary

The final artifact set looked like this:

About 77.2% of the raw export reached the governed artifact set. I did not force the rest into Other Electronic Components to make the result look larger.

The normalized artifacts occupied roughly 5.6 GB. The compressed download had hidden almost every operational number that mattered: decompressed bytes, accepted-row distribution, family-datasheet reuse, transaction time, and collision rate.

I now treat compressed size as trivia.

First I Made the Input Immutable

The API never reads the manufacturer's live export during an apply. It reads a normalized Catalog Import Artifact.

The distinction matters. An upstream file is transport; an artifact is the input contract mpn.cc has agreed to understand.

The adapter scans the source, validates each row, preserves the raw manufacturer spelling, maps supported product families, and writes source-neutral records in deterministic order. A manifest records the source revision, license evidence, adapter version, schema version, metrics, rejection counts, and SHA-256 of the normalized content.

Preparing an artifact does not touch the catalog database.

manufacturer export
        ↓ parse and validate
normalized source records
        ↓ deterministic bytes + manifest
immutable catalog artifact
        ↓ read-only comparison
durable database plan
        ↓ bounded audited apply
catalog identities and source observations

This separation was useful almost immediately. I could rerun parsing, inspect rejections, and measure the complete revision without holding a database transaction or changing a public page.

One 3.57 Million-Row File Was Still Too Large

A single artifact would have been technically possible and operationally unpleasant. One corrupt tail, interrupted upload, or failed apply could turn the whole revision into one giant recovery problem.

I split the accepted records into 40 shards using the normalized MPN. The sharding function is intentionally uninteresting:

function shardIndex(mpn: string, shardCount: number): number {
  const digest = sha256(mpn);
  return Math.floor((digest[0] * shardCount) / 256);
}

The same MPN always lands in the same shard. The average shard contains about 89,424 records and the largest remains below the explicit 100,000-row boundary.

Stable sharding gave me units I could operate:

• each shard had its own hash, record count, and source identity;
• independent shards could run concurrently without intentionally owning the same MPN;
• a failed shard could resume without rebuilding the revision;
• the complete release could reject missing or duplicate shard indexes before writing anything.

Before the first production mutation, the runner performed a dry run across all 40 shards. One missing artifact, changed hash, unmapped category, duplicate identity, or truncated record set stopped the release.

I did not want the first discovery of a bad artifact to happen after shard 31.

The Plan Was Not Permission

Planning compared the immutable records with current source observations and persisted the exact rows that could be added, updated, left unchanged, or rejected.

It was tempting to treat that plan as final authority. But a plan is only a picture of the database at one moment.

An administrator can merge an organization, add a manufacturer alias, reject a candidate, correct a category, or publish a better fact between planning and apply. The apply path therefore rechecks the live state in batches of at most 500 selected rows.

For each batch, the server:

1. Reads the exact records from the immutable artifact.
2. Verifies their bytes against the durable plan.
3. Rechecks the current source-observation hashes.
4. Resolves manufacturer identity from the live verified registry.
5. Applies catalog identity and source observations atomically.
6. Records the administrator, reason, source, and outcome.
7. Commits before claiming the next batch.

The artifact cannot create its own canonical manufacturer. Manufacturer names, aliases, official domains, logos, and roles remain database-owned. If a source spelling no longer resolves uniquely, the old plan fails closed.

This is slower than COPY. It is also the reason a three-day import did not get three days of unchecked authority.

Then I Deployed in the Middle of It

My first throughput estimate put the rollout somewhere between two and four days. I added bounded concurrency across independent shards while keeping the 500-row transaction boundary.

Then a routine application deployment replaced the API while the import runner was active.

The in-flight request disappeared and the runner exited.

I remember the brief, unpleasant moment before checking the database. Long-running jobs have a way of making every architecture claim feel theoretical until a process actually dies.

The catalog was fine.

Completed batches were committed. The interrupted batch had not become half-applied. The artifact hashes and durable plan were unchanged. I reopened the same run and continued from the last committed boundary.

That incident did more for my confidence in the design than another week of unit tests. Resumability was no longer a diagram. It was the reason I did not restart 3.57 million records from zero.

The execution layer still deserved better retry and deployment awareness, but the data boundary had done its job.

Importing Identity Is Not Importing Completeness

Most accepted rows were not ready to become rich public part pages. That was expected.

mpn.cc separates a resolved component identity from its data completeness. A manufacturer and exact separator-preserving MPN can be valid even when the image, package, category, lifecycle, or primary datasheet still needs work.

The result of the import therefore split naturally:

• complete identities could be published;
• unresolved or conflicting identities stayed private;
• valid but incomplete records entered admission and data-quality workflows;
• source observations remained attached regardless of the public decision.

This is why the table contains 2,468,327 valid private candidates. They are not failed rows. They are records for which the system knows what the source claimed but is not yet willing to claim the same thing publicly.

That difference is easy to erase in a schema and difficult to recover later.

Provider Acquisition Filled Gaps, Not Truth

The manufacturer export gave mpn.cc breadth. It did not give every useful field, and it could not stay fresh forever.

Provider acquisition handles that second problem on demand. When a user searches for a missing or incomplete MPN, mpn.cc queries configured catalog sources under one shared time budget. Every adapter returns the same internal record shape; the registry normalizes identities, removes duplicates, ranks exact matches ahead of folded and similar results, and checks the manufacturer against the canonical organization registry.

It stops early when it finds a strict-exact record that satisfies admission policy. One failed provider does not abort the entire search.

The important part is what happens after the response: provider data becomes a source observation. A normal refresh does not overwrite the catalog's selected technical values. Only an explicit Import Control apply may initialize a canonical key that is still empty. This single-writer rule is shared with datasheet fact promotion and retraction.

provider response → observation ledger → reviewable catalog proposal
                                      ↘ never “last write wins”

If a distributor later reports 2.8 A where the catalog already contains a reviewed 3 A datasheet fact, mpn.cc keeps both pieces of information in their proper places. It does not silently pick whichever arrived last.

A Datasheet URL Was Only a Candidate

The export contained datasheet links. I still did not consider those rows evidence-ready.

External URLs disappear, redirect, rate-limit, and sometimes return an HTML error page from a path ending in .pdf. More subtly, the file behind a URL can change while the URL remains the same.

For an eligible part, mpn.cc downloads the document through its outbound security boundary, verifies PDF bytes, limits the size, hashes the complete content, and stores it under an application-owned immutable key in Cloudflare R2.

source URL
   ↓ download and validate
PDF bytes
   ↓ SHA-256
immutable R2 object
   ↓ exact part relationship
primary datasheet evidence

Thousands of orderable variants can share one family PDF, so identical bytes are stored once. But object deduplication does not prove that every part is covered by the document. The part-to-datasheet relationship has its own evidence and provenance.

If mirroring times out during acquisition, the useful catalog observation still survives. The datasheet candidate is remembered as pending and can be retried. I did not want an unreliable vendor PDF server to decide whether an otherwise valid part existed.

The Model Was Not Allowed to Publish

Once a primary PDF is mirrored and hashed, analysis can begin.

Before spending model tokens, the workflow checks whether the exact target MPN appears in the immutable document or whether an explicitly attested family-document relationship exists. A similar product name is not enough.

The PDF text is split into deterministic, page-addressed chunks. The part's taxonomy key resolves a versioned analysis family and extraction profile. A linear regulator profile asks about input voltage, output current, dropout voltage, quiescent current, output configuration, and protection features. A resistor profile asks different questions and accepts different units.

The model returns structured claims, not prose:

type MeasurementClaim = {
  key: string;
  observations: Array<{
    minimum: number | null;
    typical: number | null;
    maximum: number | null;
    unit: string;
    condition: string | null;
    evidence: Array<{ page: number; chunkId: string }>;
  }>;
};

The application verifies that every cited chunk came from the supplied PDF and that the page matches. The profile rejects unknown keys, unsupported units, and invalid shapes. The MPN scope check rejects output for another part.

If the category has no canonical profile, analysis can still produce evidence and suggest a better category. Those claims remain evidence-only. The lack of a profile is not permission to invent one at runtime.

This became one of the most important sentences in the entire project:

An extracted claim is not a published fact.

Publishing a Fact Was Another Import

The fact review screen shows a human-readable value beside the exact PDF page that supports it. An administrator can inspect the evidence, correct the structured claim, choose eligible facts, and record a reason.

Approval rechecks the immutable document, analysis identity, category family, profile version, parameter definition, evidence references, and any existing fact inside the write transaction. The fact ledger and the part's selected technical projection change atomically.

There is also a narrow, versioned automatic-admission policy for high-confidence claims that meet stronger evidence rules. It calls the same promotion boundary. The model still never writes directly to the public catalog.

PDF publication remains separate. Sometimes the document relationship is correct while the category contract is missing or stale. In that case an administrator can approve the PDF and leave the extracted claims as evidence-only.

This is why a part can be public, have an approved datasheet, show older published facts, and still have a newer analysis blocked for review. Catalog identity, document publication, extraction, review, and fact publication are different state machines.

Trying to compress them into one approved badge caused real UX bugs. Keeping them separate prevented much worse data bugs.

I Tested Against a Copy of the Real Catalog

Local fixtures could prove transaction behavior, but they could not reproduce the production manufacturer registry, taxonomy, existing identities, or R2 reuse patterns.

Before applying the full revision, I created a logical production backup and restored it into an isolated database. The restore had to succeed and the important counts had to match before I trusted the rehearsal.

That environment exposed the things synthetic benchmarks miss: real aliases, category history, pre-existing PDF objects, canonical collisions, and observation hashes from earlier imports.

The production run kept the public catalog responsive while shards progressed concurrently. This was not a benchmark of PostgreSQL insert speed. Every applied row passed artifact verification, identity resolution, transaction boundaries, provenance, and audit. Eligible documents also entered the mirroring and evidence flow.

That is a much less flattering benchmark number than raw rows per second. It is also the one I needed.

What I Learned From 4.63 Million Rows

The first lesson is that a large import is a product workflow, not a database script. Someone has to review what changed, understand why rows were rejected, stop a bad revision, resume an interrupted run, and explain a published record months later.

The second lesson is that provenance becomes more important as the source becomes more reputable. “The manufacturer said it” is useful, but it can make teams less likely to notice where identity mapping, family documents, or category assumptions still require a decision.

The third lesson is that infrastructure should follow the failure boundary. Forty immutable shards and 500-row transactions earned their complexity because they made a multi-day production mutation recoverable. A general queue platform would not have added the same value at this stage.

I would make the runner deployment-aware earlier, and I would measure the decompressed source in stages before estimating anything. I would not weaken the admission rules to improve the headline number.

3.57 million governed records are more useful than 4.63 million unexplained rows.

The satisfying part is not that mpn.cc can store millions of electronic parts. It is that a published value can still lead backward—from the public page, to a fact decision, to page-addressed evidence, to immutable PDF bytes, to an exact part identity, to the source revision that introduced it.

The import started with a CSV. The catalog started when the CSV stopped being trusted.