The job hunt as a data warehouse

The problem#

A job search is a data problem, but most people run it out of forty browser tabs and a spreadsheet. Postings come from several sources and arrive in different shapes. The same role turns up on three boards. Postings disappear. What you sent to whom, and which version of your resume, lives in your email if it lives anywhere.

I built Job-Hunter-Flow to treat that as what it is: a small warehouse. Openings are ingested from permitted public sources every four hours, landed raw, transformed into a tested and deduplicated model, and served through a personal API and a browsable UI. The interesting parts are the same ones that matter in any warehouse: source agnosticism, schema contracts, who owns which data, and checks that fail loudly instead of quietly.

Architecture#

flowchart LR
    subgraph Sources["Sources (one directory each)"]
        S1["ATS job board APIs"]
        S2["config.yaml + source.py + README"]
    end
    subgraph Ingest["dlt"]
        RAW["raw.* tables<br/>one JSONB payload per posting"]
    end
    subgraph Transform["dbt"]
        STG["staging<br/>one model per source<br/>enforced column contract"]
        MART["marts<br/>fct_job_posting, dim_job"]
    end
    subgraph App["FastAPI + HTMX"]
        API["/api/v1 (API key)"]
        UI["Discover, Board, Profile, Ops"]
    end
    APPDB[("app.* schema<br/>Alembic-owned<br/>applications, documents, tags")]
    DAG["Dagster<br/>every 4 hours<br/>asset checks"]

    S1 --> RAW --> STG --> MART --> API
    MART --> UI
    APPDB --> API
    APPDB --> UI
    DAG -.orchestrates.-> RAW
    DAG -.orchestrates.-> STG

A source is a directory. Each one holds a declarative config.yaml (filters, rate limit, page cap, kill switch), a source.py that knows how to fetch, and a README recording its terms-of-service posture. A registry discovers the directories. The Dagster asset factory, the API's /sources endpoint and the UI filters all read that same registry, so none of them names a source and they cannot drift apart.

Python stays dumb; dbt does the shaping. Every source yields the same record: an identity, a timestamp, and the provider's untouched JSON in a single JSONB column. No source-specific field ever reaches Python, so a provider reshaping its response cannot break ingestion. Each source then owns one dbt staging model that unpacks its JSONB into a shared column contract with enforced: true. Drift fails dbt build in the right place instead of silently emptying a mart.

Deduplication by a hashed natural key. job_key is a SHA-256 of normalised company, title and location. fct_job_posting keeps every posting at the grain of source plus source id; dim_job keeps one row per real job. When sources disagree, the one closest to the origin wins: an applicant tracking system beats an aggregator.

Schema ownership is explicit. raw belongs to dlt and staging/marts to dbt; all three can be dropped and rebuilt. The app schema belongs to Alembic and holds the things nothing can regenerate: applications and their status history, notes, tags, uploaded documents (original bytes included), and every tailoring run. No pipeline command is allowed to touch it, and dbt does not reference it at all.

Stack choices and why#

• dlt for ingestion. It handles schema evolution and merge loads into Postgres, and I only needed it to land JSON reliably. One shared HTTP client wraps it with a token-bucket rate limiter, a hard page cap, and backoff that honours Retry-After. A source's config can only make itself more conservative than the global budget, never less, and that rule is unit-tested.
• dbt contracts. The column contract is written in three places (Python, a dbt macro, the enforced model schemas), so a test asserts all three agree. Adding a source is one manual edit to a list in dbt_project.yml, and forgetting it fails the build with the missing model's name.
• Dagster. Assets map naturally onto "one raw table per source, then the dbt project." Asset checks gate the dbt build: an empty raw table, a table that shrank after a merge, or a payload that is not a JSON object stops it. After the build, a check fails the run if fewer than 98% of a source's raw rows reach the fact table. Freshness policies on the raw and served tables are the only thing that notices the schedule itself has stopped.
• FastAPI and HTMX, bound to localhost. Four pages, no SPA build step. The API sits behind an API key enforced at the router level, so new endpoints are protected by default. The UI write routes also reject any request whose Sec-Fetch-Site is not same-origin, which stops a random page in another tab from posting to the board.
• Keyset pagination, not OFFSET. The list is sorted by recency and the pipeline inserts rows every four hours. Offset paging would skip or repeat rows mid-scroll.
• Two search mechanisms on purpose. ILIKE on title and company for partial words as you type, plus a GIN-indexed tsvector over the description so a term that only appears in the body is still found.

The tailoring feature runs the posting and a profile through Claude and returns structured JSON: a fit score, matched and missing skills, gaps you would be asked about, a draft resume and cover letter, and a list of what changed. Two rules there are non-negotiable. The model gets no tools at all, because the prompt contains text scraped from a public website, and it is forbidden from inventing employers, dates, metrics or technologies. A separate check compares every figure, technology and proper noun in a tailored draft against the master document and flags anything new. Figures are compared with their units, so a number that quietly grew a unit is caught.

What broke / what I learned#

A blocked source is not a failed pipeline. One aggregator serves a bot challenge to every automated client. I left it switched on so the refusal is observed rather than assumed: every run records it as blocked, the ops page says so, and the pipeline stays green. Treating a provider's access decision as an error would have turned the whole run red every four hours, which teaches you to ignore red. The block is detected by walking the exception's cause chain, because dlt wraps extraction errors twice.

Ranking and exclusion are different things. Ranking never hides anything; a posting matching none of the criteria still appears, at the bottom. Exclusion rules do drop rows, so whatever they remove is always counted with a one-click reveal. An exclusion you cannot see is indistinguishable from a bug. The exclusion text matcher strips negations before testing for a requirement, because "no X required" contains "X required" and would otherwise delete a perfectly good posting.

Weights were settled against real data. With company preference weighted above title match, a recruiting role at a well-ranked company outscored an actual data engineering role. Title answers "is this even my job" and that question comes first. Unknown salary scores zero, never negative, because most postings publish nothing.

A pre-joined mart went stale the moment the UI became interactive. Phase one had dbt join application status into a mart. Once the board supported drag and drop, a status changed a minute ago kept showing its old value until the next dbt run. Moving that join into the API's read layer made it current by construction and cut dbt's last dependency on the app schema.

PDF rendering is where fidelity goes to die. The pure-Python PDF path can only encode latin-1, so dashes and ligatures flattened. A LaTeX path fixes that, but the obvious preamble pulls in a gigabyte of TeX packages. I defined the few macros I needed in base LaTeX instead, which kept the image growth under 100 MB, and the preview page labels which renderer produced the file so a degraded PDF is never silent.

Status#

Feature-complete for its purpose and running locally under Docker Compose. CI runs lint, the offline unit suite with a coverage floor, a full pipeline end-to-end run against a throwaway Postgres with recorded fixtures (twice, to prove idempotence), image builds, and secret scanning. Deliberately left out: semantic search, automatic tailoring of every new posting, alerting, and any multi-user or non-local deployment.