Why an ID Check Let the Same PDF Into Our Pipeline Twice

Our pipeline checked every new document against the ones it already had. It still saved the same PDF twice. The check was working fine, it was just checking the wrong thing.

You've probably seen this at home. The same bill reaches you by email and by post, each with its own reference number. If you only compare reference numbers, it looks like two bills, and you pay twice. The only way to know is to look at what's inside.

Multibagg AI is an AI-powered stock research platform for Indian investors. People read company documents on it and ask its AI assistant about them, so a document stored twice is a document they may see twice.

This is how I moved our duplicate check from IDs to file contents, and the bugs that turned up on the way, one of which would have thrown away real documents.

The real problem: one PDF, two IDs

Our pipeline pulls company filings from a few external feeds and saves each one as a row in a documents table, with the PDF in file storage. Every feed gives a document its own ID.

The save jobs decided "new or not" from those IDs. One job compared the feed's own ID with the IDs it had already stored. Another job was weaker still: it made up a fresh random ID for every row it saved, so its only real check was the download link. If the link was already stored, it skipped the document. If not, it saved it.

So when the same PDF reached us through a second feed, or under a new link, nothing matched. The pipeline saved it again.

A third feed had a different problem. It delivered investor documents such as call transcripts and presentations, and many of them were the same PDFs we already had from the filings feeds. Its records carried no fingerprint of the file at all, so there was no way to tell whether a document was new. That feed's records lived in a table of its own, separate from the main documents table, so the documents that only existed there never made it into the main table, which meant they never reached the step that converts and indexes documents for search.

How did the old pipeline catch duplicates?

It did catch most of them, just late. The classification job, the step that downloads each new document and asks an AI model what kind of document it is, had a duplicate check. I'd added it a few weeks earlier.

That check used a content hash. A content hash is a fixed-length fingerprint computed from a file's bytes. SHA-256, the one we use, gives the same 64-character hex string for the same bytes every time, and a different one for practically any change to the file.

Before calling the model, the classification job hashed the PDF and looked for an earlier row with the same hash, for the same company, that wasn't deleted and wasn't already marked as a copy. If it found one, it saved a pointer to the original on the new row and skipped the model call.

flowchart TD
    A["Feed sends a document"] --> B{"Seen this ID or link?"}
    B -->|"Yes"| C["Skip"]
    B -->|"No"| D["Download and save a new row"]
    D --> E["Classification job hashes the PDF"]
    E --> F{"Same company and hash already stored?"}
    F -->|"Yes"| G["Mark the new row as a copy"]
    F -->|"No"| H["Classify with the AI model"]

Here's the save step as it was, rewritten with generic names:

import { randomUUID } from "node:crypto";

interface FeedItem {
  ownerId: string;
  fileUrl: string;
  bytes: Buffer;
}

interface DocRow {
  id: string;
  ownerId: string;
  fileUrl: string;
  fileHash: string | null;
}

interface DocStore {
  findKnownUrls(urls: string[]): Promise<Set<string>>;
  insertMany(rows: DocRow[], options?: { skipDuplicates: boolean }): Promise<number>;
}

  (): <> {
   known =  store.(items.( item.));
   fresh = items.( !known.(item.));
   store.(
    fresh.( ({
      : (),
      : item.,
      : item.,
      : ,
    })),
  );
}

Nothing in it is wrong on its own terms. It just never asks what's inside the file. Every duplicate became a real row first and got marked later, and a marked copy was still a row: a teammate later pointed out that our document lists didn't filter on that marker. The investor feed's documents never reached the classification check at all.

How do you check the contents without downloading twice?

The save jobs already download every PDF so they can upload it to our file storage. At that moment the bytes are in memory. Hashing them there costs one pass over a buffer we already have, and no extra download.

For the investor-documents feed, the job now also saves the hash in its own table, and on later runs it reuses that stored hash instead of computing it again. I was firm on that one. Hashing is cheap next to a download, but it isn't free, so if we already have the hash, we don't make it twice.

import { createHash } from "node:crypto";

const hashBytes = (bytes: Buffer): string =>
  createHash("sha256").update(bytes).digest("hex");

const dedupeKey = (ownerId: string, fileHash: string): string =>
  `${ownerId}:${fileHash}`;

The point of all this is simple: deduplicate documents by content hash at the moment you save them, while the bytes are still in hand, instead of saving first and cleaning up later.

Why must the key include the owner, not only the hash?

My first version keyed on the hash alone. About twenty minutes later, before anyone had reviewed it, I changed it to company plus hash.

Two different companies can file a byte-identical PDF, and each filing is real. A hash-only check would keep the first one and throw the second away, and nobody would notice, because a missing document doesn't raise an error. The classification check had always scoped its search by company, so keying the new check the same way also made the two agree.

The lookup runs as two queries per run, however many documents the run has. We were also in the middle of moving the main documents table to a new version, so a copy could live in the old main table or the new one, and each gets one query. From here on, "the main table" means the new one, "the old main table" the one being retired, and "the feed's own table" is where the investor documents lived. Each query takes the list of hashes and the list of companies in the batch:

SELECT owner_id, file_hash
FROM documents
WHERE file_hash = ANY($1)
  AND owner_id = ANY($2)
  AND deleted = false
  AND duplicate_of IS NULL;

Two separate lists match any combination of a company and a hash, including pairs that never came in together. That's fine, because the code builds the key from each returned row and only checks the keys of the documents it's actually holding. A pair that wasn't in the batch never gets looked up.

The last two conditions came later, from review. More on them below.

Fail open or fail closed?

Any check that sits in front of a write can break: the database times out, a query fails. You have to decide what happens then.

Fail open means that if the duplicate check can't run, everything goes through. Fail closed means that if it can't run, nothing goes through.

For the save jobs I chose fail open (one other path still fails closed, see the limitations below). If we keep a duplicate, the worst case is an extra row that the classification check marks later. If we drop a real document, it's gone, and nothing tells us. Dropping a real record is worse than keeping a duplicate.

The same rule covers documents the check can't judge. If a download or upload failed there are no bytes to hash, and if the company couldn't be resolved there's no key to build. Both pass through unchanged instead of being dropped.

interface HashLookup {
  findExistingKeys(
    pairs: { ownerId: string; fileHash: string }[],
  ): Promise<Set<string>>;
}

async function dropContentDuplicates(
  rows: DocRow[],
  lookup: HashLookup,
  log: (message: string) => void,
): Promise<DocRow[]> {
  const pairs = rows.flatMap((row) =>
    row.fileHash ? [{ ownerId: row.ownerId, fileHash: row.fileHash }] : [],
  );
  let existing = new <>();
   {
    existing =  lookup.(pairs);
  }  (error) {
    ();
  }
   seen =  <>();
   rows.( {
     (!row. || !row.)  ;
     key = (row., row.);
     (existing.(key) || seen.(key)) {
      ();
       ;
    }
    seen.(key);
     ;
  });
}

The seen set catches the case where the same file shows up twice inside one run, which the database lookup can't see. It works even when the lookup fails, so copies within one run are still removed.

flowchart TD
    A["Download the PDF"] --> B["Hash the bytes already in memory"]
    B --> C["One batched lookup per table, by company and hash"]
    C --> D{"Did the lookup fail?"}
    D -->|"Yes"| E["Skip the database check and log it"]
    E --> I{"Seen earlier in this run?"}
    I -->|"Yes"| G
    I -->|"No"| H
    D -->|"No"| F{"Key already stored or seen in this run?"}
    F -->|"Yes"| G["Skip"]
    F -->|"No"| H["Save the row"]

Deterministic IDs: making a re-run harmless

Investor documents that matched nothing get a new row in the main table. Its ID isn't random. It's the feed's own serial number with a fixed prefix, so the same document always gets the same ID.

The insert then skips any row whose ID already exists. In PostgreSQL that's ON CONFLICT DO NOTHING, which our database library exposes as a skip-duplicates option on its insert-many call. Run the job twice and the second run inserts nothing new and fails on nothing.

const COPY_PREFIX = "docfeed-";

const copyId = (serial: number): string => `${COPY_PREFIX}${serial}`;

async function insertUnmatched(
  docs: { serial: number; ownerId: string; fileUrl: string; fileHash: string }[],
  store: DocStore,
): Promise<number> {
  return store.insertMany(
    docs.map((doc) => ({
      id: copyId(doc.serial),
      ownerId: doc.ownerId,
      fileUrl: doc.fileUrl,
      fileHash: doc.fileHash,
    })),
    { skipDuplicates: true },
  );
}

Which bugs did review catch?

Three, and none of them would have shown up as an error.

Deleted rows blocked the same content forever

An AI code review I ran on the branch compared my new lookup with the older classification check. The old check ignored deleted rows and rows already marked as copies. Mine didn't.

So a document we had deleted on purpose would block the same content from ever coming back in. And a row that was itself a copy counted as an original. I added the same two conditions you saw in the query above, so both checks now agree on what "already stored" means.

The same document came back twice in lists

The same review found a bug on the read side. For older dates, our readers pulled investor documents from both the feed's own table and the main table, then just sorted and cut the result to the page size. I was copying investor documents into the main table while they still lived in the feed's own table, under different IDs: the plain serial on one side, the prefixed serial on the other. Nothing could merge them.

So every one of those documents came back twice, and a page made up of them held only N/2 different documents for N results.

The review suggested filtering those rows out by their source. That would have hidden real records too, because regular filings from the same source live in the main table with the same source label. I filtered on the ID prefix instead, which only the copied rows carry.

The new step would almost never have run

A teammate's review found the one I'm least proud of.

The investor-documents job already had a date filter from the table migration. Anything dated after the switch-over date was skipped, because readers were supposed to get newer documents from the main table, where the filings feeds' copies already landed. My new insert step sat after that filter. And the job only fetches the previous day's documents.

Put those together: in production, almost every document would be newer than the switch-over date, the filter would drop it, and my new step would only ever see the odd backdated document. Nothing in our tests modelled what a real day of input looks like, so they had no reason to fail.

flowchart LR
    A["Daily job fetches yesterday's documents"] --> B{"Dated after the switch-over?"}
    B -->|"Yes, almost all"| C["Dropped by the old date filter"]
    B -->|"No, rare backdated ones"| D["New insert step"]

I removed that filter from the path that feeds the new step, and narrowed the read-side exclusion to copied rows dated before the switch-over. Older documents are read from the feed's own table and newer ones from the main table, so each document is returned once.

Should a duplicate be dropped, or kept with a pointer?

The same teammate pushed back on the design itself. The old classification check kept the duplicate row and stored a pointer to the original, which leaves a record you can query. My save-time check drops the duplicate, and the only trace is a job log line that ages out.

We kept the drop. We didn't want duplicate rows in the table at all. I didn't need the perfect design here, I needed the duplicates gone without breaking the flow.

That choice has costs, and they're real:

• A skipped duplicate leaves no lasting record. If the check is ever wrong, there's nothing to look back at.
• One of the save jobs checks the link before it downloads. A dropped duplicate never stores its link, so if the same PDF keeps coming under a different link, that job downloads it again on every run while it stays in the feed's window. We judged that case rare and the cost small.

Trade-offs and limitations

There's no unique constraint. Duplicates are prevented when we insert, not enforced by the database, so two runs at the same moment could both insert the same file. A partial unique index on company plus hash, limited to rows that aren't deleted and aren't marked as copies, would close that gap. It has to use the same scope as the lookup, because the classification check keeps its marked copies in the table. PostgreSQL treats NULLs as distinct by default, so rows with no hash would still go in. We didn't add it in this change.

The old main table has no index on the hash. Its half of the lookup is a full table scan, and one of the save jobs runs every two minutes. Combined with fail-open, a slow or failing scan quietly turns the duplicate check off. We accepted it because that table was being retired. The catch is the retirement itself: when the table goes, the lookup has to change in the same commit, or it throws on every run and the check silently stops.

Older rows have no hash. We didn't backfill hashes for documents saved before this change, so the save-time check can't match against them. The classification check still runs and remains the backstop for anything without a hash.

Only exact copies match. A content hash changes if a single byte changes. A re-saved or re-compressed PDF with the same text gets a different hash, and this check treats it as new.

One path still fails closed. In the job that copies investor documents into the main table, a failed lookup skips the whole insert. Because that job only fetches the previous day, those documents wouldn't come back on their own. Both reviews flagged it, and it went in as a follow-up instead of this change.

Results and observations

Before merging, the teammate's review asked a fair question: is the hash even a reliable match across feeds? If the investor-documents feed served a slightly different file than the filings feed, every document would land twice anyway.

So I checked a sample of recent data. The review thread records the matches, not the sample's size or window. 113 different documents from the investor-documents feed matched 221 existing records by hash (one document can match more than one stored row, for example when the same file sits in both the old main table and the new one during the migration; we didn't break the 221 down further). In every matched pair, the feed's own document category agreed with the type our classifier had assigned. Zero mismatches.

A matching hash already means identical bytes. The category check adds that these matches were the kind of document we expected. The sample doesn't tell us how many documents matched nothing, or how many copies it missed.

What other engineers can learn

Key duplicates on what a record is, not where it came from. IDs and links describe the delivery. If the same thing can be delivered twice, compare the thing. For files, a hash of the bytes is the cheapest honest answer, and it's cheapest of all when you take it from bytes you already hold.

Put the owner in the key. "Same content" and "same record" aren't the same question. Two owners with one identical file are two records. Ask what makes a record unique in your domain, and build the key from that, not just from the content.

Choose the failure mode by which mistake costs more. Every check in front of a write will fail one day. Write down what happens then. For us an extra row is a nuisance and a missing document is silent data loss, so the check fails open. In a payments system you'd likely pick the other way.

Make every "have we seen this?" check agree. We had two duplicate checks, and for a short while they disagreed about deleted rows and about rows already marked as copies. When two paths answer the same question, give them one definition, ideally one function.

Trace one real day through every filter. The step that never would have run had nothing wrong in it. The problem was a filter in front of it and the shape of the input. Before you ship a new step, take yesterday's real input and walk it through every condition between the fetch and your code.

Conclusion

Back to that bill that came by email and by post. Our pipeline wasn't careless. It checked every document, every time. It just asked "have I seen this ID?" when the real question was "have I seen this file, for this company?"

Changing that question took one hash and a better key. Getting it right took deciding what to do when the check breaks, and making sure every check in the pipeline asked the question the same way.

Which error costs more in your pipeline: a duplicate, or a record that quietly never arrives?