Discovery — Under the Hood

What a run costs, statement by statement

Ingestion (ingestConnectorRun, packages/db/src/discovery.ts:180) processes items sequentially. Per parsed item: company upsert, fingerprint lookup, posting insert-or-update, source-item upsert, signal insert — ~5 statements, each a full HTTP round trip on the Neon driver.

The sequential per-item loop is the obvious optimization target (batch the inserts, or pipeline over a pooled connection) — and the code chose caps instead. That's a legitimate engineering answer: when the bound is what matters, limiting n is cheaper and safer than making each n faster. Batching changes failure semantics too — a batch insert that dies mid-way is harder to reason about than "we stopped cleanly at item 350 of 500."

The decision: content fingerprints with badge-on-disagreement

The geometry to internalize: normalization defines equivalence classes. Every stripped token (seasons, "Intern", "Inc") widens the class — more true merges, more risk of false ones. The recipe strips exactly the tokens that vary across listings of the same job and keeps everything that distinguishes actual roles. When a class is wrong in the dangerous direction (same fingerprint, different apply URL or track), hasDuplicateDisagreement refuses to merge and raises a badge instead. Dedup keys are product decisions wearing a hash function's clothes.

Also chosen: the raw/normalized split. source_items keeps what the source said; job_postings keeps what Wera concluded. Ingestion is thus re-runnable and auditable — the same principle as event logs and staging tables in data engineering: never overwrite your inputs with your conclusions.

A shipped bug, dissected — plus the races that remain

• The running-run lock is advisory: before starting, ingestion fails any running run older than 15 minutes, then rejects if one is still active. But the check and the insert aren't atomic — two simultaneous manual runs can slip through the window. A SELECT … FOR UPDATE or a partial unique index on (user_id, connector_key) WHERE status = 'running' would close it; at one user the race needs two of your own tabs. Noticing that it's a check-then-act is the skill.
• Crash mid-ingest: the catch block marks the run failed with the error — but a hard process kill (OOM, platform eviction) leaves running forever. That's what the 15-minute stale sweep at the start of the next run is for: self-healing state machines beat cron janitors you have to remember to build.
• One bad timestamp ≠ one dead query: postedAt lives in jsonb; casting it in SQL (::timestamptz) would abort the whole inbox query on the first malformed value. The code reads it as text and parses per-row in JS (discovery.ts:582) — blast-radius engineering at the column level.
• Partial success is a status, not a lie: runStatus maps (error ∧ zero parsed) → failed, (error ∨ failures) → partial, else completed — and the UI renders partial as a warning, never a green check. Honest statuses are a failure-mode defense: they keep humans in the loop while numbers drift.

Make it yours