Data Model

OpenOpps keeps the operational ledger in local SQLite, publishes portable extracts for analysis, and serves the docs jobs/explorer surfaces from committed static artifacts. Use this page when you need to reason about data boundaries, counts, exports, or telemetry.

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.

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)

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

OpenOpps has two generated count families:

Count family	Source	Used by
Package catalog counts	`scripts/generate_docs_data.py` writes `docs/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 `docs/public/data/openopps-search/`.	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

boards export and jobs export provide filtered extracts of normalized records:

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 --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

The docs app does not call provider APIs at runtime. It reads committed artifacts under docs/public/data/openopps-search/:

Artifact	Purpose
`manifest.json`	Snapshot version, generated time, counts, chunks, facets, and provenance.
Job chunks	Searchable latest job rows for the jobs workbench.
Detail shards	Larger posting detail payloads loaded only after a job is selected. T1 shards carry metadata only; T2 shards add a bounded plain-text body (≤4000 characters, HTML stripped).
Board/provider chunks	Explorer coverage, route, and source/provider inspection.

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:

just docs-search-index
just docs-search-index-check

just docs-search-index-check requires the ignored local SQLite snapshot, regenerates docs/public/data/openopps-search/, and fails if the committed search artifacts still differ. If the SQLite file is absent, use package-derived docs checks and record the missing snapshot as the blocker for search-index parity.

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 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/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

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.