# Data Model (/docs/data-model) OpenOpps keeps the operational ledger in local SQLite, publishes portable extracts for analysis, and serves the web Jobs/Explorer surfaces from static artifacts. Version 6 is the committed transition tree; version 7 is a separately delivered content-addressed publication. Use this page when you need to reason about data boundaries, counts, exports, or telemetry. ## Core Entities [#core-entities] | Entity | Meaning | Primary producer | | ------------------------------------------- | ------------------------------------------------------------------------------------------------------------ | -------------------------------------------------------- | | `sources` | Aggregate discovery catalogs such as `a16z`, `accel`, `yc`, public-company indices, or ecosystem landscapes. | Packaged source adapters and plugins. | | `boards` | Firm or company hiring boards discovered from sources. | `sources sync`, `boards sync`, and admin board commands. | | `board_providers` | Provider route metadata between a board and a public job adapter. | Provider detection, route probing, and board sync. | | `jobs` | Stable posting identity and lifecycle fields. | Successful provider-route job syncs. | | `job_versions` | Normalized current or historical public posting content. | Job sync content hashing. | | `job_payload_snapshots` | Distinct raw listing/detail payload snapshots for replay and audit. | Job sync payload hashing. | | `job_sync_runs` and `job_sync_observations` | Per-route sync evidence for new, unchanged, changed, reopened, and closed outcomes. | Provider-route job syncs. | | `url_pull_runs` | Dedicated URL-pull audit domain, distinct from `job_sync_runs`. | Opt-in `jobs pull --save` only. | Saved URL-pull boards use reserved source key `url-pull` and a punctuation-preserving digest. They are not packaged catalog sources. Default `jobs pull` is ephemeral (`--save` False). Persist failure exits 9. Unscoped `jobs sync` excludes `url-pull` routes. Alembic revisions are `0005_update_snapshot_ledger` then `0006_url_pull_runs`. Job storage separates durable posting identity from changing content. The `jobs` table tracks status, first/last seen timestamps, close time, and current hashes. `job_versions` stores normalized user-visible content with child tables for locations, skills, skill keywords, and responsibility or qualification bullets. Raw payload drift updates payload snapshots and observations without creating a new user-visible version when normalized content does not change. ## Ingest surplus taxonomy (S1–S4) [#ingest-surplus-taxonomy-s1s4] Board ingestions capture more than first-class normalized columns. OpenOpps classifies the extra signal into four surplus classes: | Class | Where it lives today | Examples | | --------------------------- | ---------------------------------------------------- | ----------------------------------------------------- | | **S1 Raw-buried** | `raw_listing`, `raw_detail`, `job_payload_snapshots` | Greenhouse `metadata`, `requisition_id`, office trees | | **S2 Fetchable-not-called** | Board detail APIs not requested during sync | Greenhouse `pay_input_ranges`, org `/offices` trees | | **S3 Derivable** | Computable from normalized text | `seniority`, expanded skill catalog, `daysOpen` | | **S4 Sync-evidence** | `job_sync_runs`, `job_sync_observations` | Velocity, churn, reopen, median days open | Promotion policy: move high-cardinality S1 keys into typed `JobRecord` fields or `version.extra_payload` (`posting_kind`, `seniority`, `provider_extras`); keep full raw payloads in SQLite/Kaggle only. The docs search index uses **tiered detail shards**—T2 full body for indexable SEO jobs, T1 metadata-only for other open jobs—and never commits `payloadSnapshots`. ## Count Provenance [#count-provenance] OpenOpps has two generated count families: | Count family | Source | Used by | | ---------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------ | | Package catalog counts | `scripts/generate_docs_data.py` writes `web/lib/generated/openopps-data.json`. | Source catalog, provider registry, export format, docs summary cards. | | Snapshot/search counts | `scripts/generate_docs_search_index.py` reads `kaggle/openoppsdb.sqlite` and writes either the v6 `web/public/data/openopps-search/` tree or a separate v7 publication root. | Jobs workbench, `/explorer`, snapshot freshness, filters, row totals, and preview/detail shards. | Do not copy counts into prose unless the sentence explicitly names the snapshot date or package source. Prefer generated components and generated manifest values so the site does not drift when sources, providers, locations, departments, or exports change. ## Exports [#exports] `boards export` and `jobs export` provide filtered extracts of normalized records. Job JSON/JSONL identifies `profile` and `schemaVersion` (`core`, `search`, `full`, `raw`). CLI default is `full`; generated web search artifacts use `search`. This page does not authorize Workers upload, Kaggle mutation, or a live hosted job database. ```bash uv run openopps boards export --provider ashbyhq --has-jobs --format jsonl --output /tmp/openopps-boards.jsonl uv run openopps jobs export --remote Full --skill Python --format csv --output /tmp/openopps-jobs.csv uv run openopps jobs export --profile search --format jsonl --output /tmp/openopps-jobs.jsonl uv run openopps jobs export --source a16z --salary-min 150000 --format parquet --output /tmp/openopps-jobs.parquet uv run openopps jobs export --status all --format sqlite --output /tmp/openopps-jobs.sqlite ``` | Format | Use case | Notes | | --------- | ---------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------- | | `jsonl` | Streaming audit logs and line-oriented processing. | Preserves normalized values as JSON records. | | `csv` | Spreadsheet review and lightweight exchange. | Formula-leading strings are neutralized before writing. | | `parquet` | Polars, DuckDB, and analytics workflows. | Empty exports should remain readable empty tables. | | `sqlite` | Relational handoff, filtered local extracts, and portable query files. | Entity tables should use the flattened CSV/Parquet contract; nested values should be stable JSON strings. | The full OpenOppsDB snapshot remains the durable SQLite ledger. The docs and Kaggle bundle flows use `kaggle/openoppsdb.sqlite` as the local ignored input for static search artifacts and public dataset generation. ## Static Jobs and Explorer Index [#static-jobs-and-explorer-index] The web app does not call provider APIs at runtime. It reads a generated static projection: | Artifact | Purpose | | -------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------- | | `search-manifest.json` (v7) / `manifest.json` (v6) | Snapshot time, counts, chunks, facets, and search projection metadata. | | Job chunks | Searchable latest job rows for the Jobs workbench and browser search worker. | | Detail shards | Posting details loaded after selection. T1 carries metadata only; T2 adds bounded plain text (≤4000 characters, HTML stripped). | | Board/provider chunks | Explorer coverage, route, and source/provider inspection. | | `publication-policy.json` (v7) | Sanitized source rights, required attribution, and snapshot-quality counts. | | `manifest.json` (v7 release root) | Exact file closure, byte lengths, SHA-256 values, semantic roles/counts, source provenance, generator provenance, and release identity. | The committed `web/public/data/openopps-search/` version 6 tree is a bounded transition fallback. A version 7 publication lives outside that tree under immutable `releases//` paths selected by `channels/production.json` schema version 2. Every v7 payload read must be declared by and match the pinned release manifest. **Description projection matrix:** Kaggle/SQLite bundle previews truncate normalized text at **512** characters; search **T2** detail shards cap plain `description` at **4000** characters after HTML stripping; **Parquet/JSONL** exports keep full normalized job fields from SQLite. Public artifacts never include `payloadSnapshots` or `descriptionHtml`. ### Indexable job criteria [#indexable-job-criteria] A job receives a **T2** detail shard (and appears in `jobs-indexable-ids.json`) only when all of the following hold at index generation time: * `status` is open (or unset) * `title`, `company`, and a non-empty description (plain or HTML) are present * at least one of `postedAt`, `firstSeenAt`, or `versionCreatedAt` is set * a safe absolute `postingUrl` or `applyUrl` is present (no credentials, fragments-only, or non-http schemes) All other open jobs still get T1 metadata-only detail shards for preview and filtering. Refresh the index after updating `kaggle/openoppsdb.sqlite`: ```bash just web-search-index just web-search-index-check ``` `just web-search-index-check` requires the ignored local SQLite snapshot, regenerates `web/public/data/openopps-search/`, and fails if the committed v6 transition artifacts still differ. If the SQLite file is absent, use package-derived web checks and record the missing snapshot as the blocker for v6 search-index parity. `just web-search-artifacts-check` validates the committed artifact graph (manifest paths, detail shard counts, Git tracking) without regenerating from SQLite. CI runs `uv run pytest tests/unit/openopps/test_docs_search_index.py -k committed` for committed manifest schema parity. Version 7 uses `--release-root` during generation and `--channel production` during verification; see [Public Data Releases](/docs/public-data-releases). ## Facets and Suggestions [#facets-and-suggestions] Jobs and explorer filters should be generated from the static search manifest, not hardcoded UI lists. The manifest is the right place for canonical values and counts for: * sources and source aliases * providers and support levels * locations * departments and teams * companies * skills and skill keywords * workplace and employment types * job status and salary currency **Seniority** (`seniorities` facet): populated from each job row's `seniority` column. Values come from `version.extra_payload.seniority` when promoted during ingest; otherwise the index generator derives a label from normalized title and `job_versions.experience` text using the same `derive_seniority()` rules as `openopps.models`. **Days open** (`daysOpen` column): computed at index generation from `snapshotAt` minus `firstSeenAt` (non-negative integer days). This is an S3 derivable field and is not stored in SQLite job rows. Fuzzy matching should rank normalized generated suggestions first and only fall back to typed substring matching when the user enters a value that is not in the generated index. ## Provider surplus promotion [#provider-surplus-promotion] Provider adapters preserve full upstream payloads on `raw_listing` and `raw_detail`. v0.1 promotion focuses on **S1 list-endpoint fields** already fetched during sync: | Provider | Promoted surplus (initial) | | ---------- | ------------------------------------------------------------------------------------------------------------------------- | | Greenhouse | `metadata`, `requisition_id`, `language`, department/office trees; `posting_kind=prospect` when `internal_job_id` is null | | Workable | Split `raw_listing` (list POST) vs `raw_detail` (per-job GET) | | Others | See `openspec/changes/archive/2026-07-10-ingest-data-surplus/provider-promotion-manifest.json` | Derived **S3** fields such as `seniority` are computed from title/experience text without extra HTTP. **S4** sync aggregates surface in the search manifest `dashboard.sync` block when `job_sync_runs` is present in the snapshot. ## Telemetry Event Lake [#telemetry-event-lake] The docs app telemetry source of truth should be a first-party event lake. The collector can run as a no-op locally, append raw events when persistent storage is configured, mirror sanitized events to PostHog when configured, and compact local events into Parquet for DuckDB analysis. Recommended event shape: | Field | Meaning | | -------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------- | | `event_id` | Client-generated unique id for de-duping. | | `schema_version` | Telemetry schema version, starting at `1`. | | `sent_at` and `received_at` | Client and collector timestamps. | | `event_name` | Allowlisted event name such as `jobs.filters_changed` or `explorer.lineage_loaded`. | | `anonymous_id`, `session_id`, `page_id` | First-party anonymous identifiers. | | `context.path`, `context.viewport`, `context.screen`, `context.timezone`, `context.language`, `context.languages`, `context.userAgent` | Browser context retained by the current route allowlist. Raw `search`, `url`, and `referrer` fields are dropped. | | `request.path`, `request.headers`, `request.ip` | Server-side request context after header allowlisting, query removal, and IP drop/hash/raw policy. | | `entity_context` | Job, source, provider, filter, or explorer entity ids when relevant. | | `properties` | Event-specific allowlisted metadata. | | `redaction_count`, `payload_truncated` | Evidence that sanitization and size limits were applied. | `page_engagement` events include both cumulative counters (`durationMs`, `visibleDurationMs`, `interactionCount`) and additive counters (`durationDeltaMs`, `visibleDurationDeltaMs`, `interactionDeltaCount`) with a `reason` and `sequence`. Use the additive counters for aggregate dwell-time stories so route changes, hidden-tab flushes, and unmounts do not double-count the same page interval. Runtime sink modes: | Sink | Use case | Cost posture | | ------------------ | --------------------------------------------------- | --------------------------------------------- | | `noop` | Local development or deployments without storage. | Free; records nothing. | | `local-event-lake` | Canonical first-party telemetry on persistent disk. | Free when running on existing infrastructure. | The current `/api/telemetry` route treats any unsupported sink value as `noop`. PostHog forwarding is controlled separately with `OPENOPPS_POSTHOG_PROJECT_API_KEY`, receives only allowlisted sanitized properties, and is bounded by `OPENOPPS_POSTHOG_TIMEOUT_MS`. Browser PostHog session replay is an opt-in mirror controlled by `NEXT_PUBLIC_OPENOPPS_POSTHOG_PROJECT_API_KEY`; replay masks text and inputs, disables network body/header capture, respects PostHog project sampling and trigger controls, and does not change the first-party event schema. Cloudflare, Umami, or BI dashboards can mirror sanitized summaries downstream, but the canonical raw sink remains `local-event-lake`. Sanitize obvious secret-like values, cap event size, and make raw IP retention an explicit opt-in. Hashing IPs with a deployment salt is the default compromise when approximate abuse/debugging signal is useful.