Database and lifecycle¶

← Maintainer handbook · Architecture · Automation · Troubleshooting · Security policy

Preserve canonical SQLite state through repository transactions, versioned migrations, and verified backups. SQLite is the source of truth for operational lifecycle state. Search YAML defines discovery configuration, while the website, README, and public downloads remain read-only projections.

Contents¶

Database location
Schema overview
Canonical job state
Search state and runs
Provenance
Successful search transaction
Manual job insertion
Closure lifecycle
Bounded availability audit
Timestamp invariants
One-writer model
Migrations
Backup
Restore
Projection consistency
Data handling

Database location¶

Default database path:

data/opportunities.db

The database and SQLite sidecars are ignored by Git.

Legacy database rename¶

Deployments created before the Opportunities rename must stop all writers and rename internships.db to opportunities.db before starting the updated pipeline or website. Move any active -wal and -shm sidecars together, or checkpoint WAL before renaming.

Schema overview¶

searches 1 ──────── * search_runs
   │
   └──── 1 ─────── * job_searches * ─────── 1 jobs

data_quality_snapshots stores bounded aggregate-only monitoring baselines
alembic_version records the current schema revision

Table	Primary key	Responsibility
`jobs`	`linkedin_job_id`	Accepted listing fields, category, timestamps, and open or closed state
`searches`	`slug`	Synchronized search identity, configuration, enabled state, and private completion timestamp
`search_runs`	UUID `id`	Per-search outcome, counts, timing, warnings, and sanitized diagnostics
`job_searches`	`(search_slug, linkedin_job_id)`	Search provenance and explicit unavailability evidence
`data_quality_snapshots`	integer `id`	Aggregate-only drift baselines, pruned to the newest 90 observations
`alembic_version`	revision	Current Alembic schema revision

The data-quality table contains observation times, counts, category/country distributions, and search-configuration fingerprints, never listing fields or source responses. Repository writes validate a strict versioned aggregate schema, reject payloads above 128 KiB, and prune to the newest 90 observations in the same transaction. Collection reads at most the five newest snapshots; invalid or future-dated payloads produce a warning and are ignored. Baselines older than 30 days expire. The quality-report reference owns comparison and warm-up rules.

Canonical job state¶

Important jobs fields:

Field	Meaning
`linkedin_job_id`	Canonical numeric identity
`company`, `title`, `location`, `link`	Normalized public listing data
`category`	Deterministic internal technology category
`industries`	Structured source industries criterion, when available
`employment_type`	Required deterministic type: `internship` or `new-grad`
`start_date`	Explicit month or season plus year, when available
`first_seen_at`	Inferred source posting time or reviewed manual timestamp when supplied on first insert; otherwise first accepted observation; immutable
`last_seen_at`	Latest successful listing observation or availability validation; monotonic
`updated_at`	Latest material field change, reopen, or close
`status`	`open` or `closed`
`last_availability_checked_at`	UTC start time of the last non-denial availability attempt; private scheduling metadata, independent of discovery

Distinct LinkedIn IDs remain distinct jobs even when their display fields match.

Every accepted row has one employment type. The migration introducing New Grad support backfills all pre-existing rows as internship, because the former classifier accepted internships only. Missing optional metadata does not erase a previously observed value.

Search state and runs¶

Before collection, YAML definitions synchronize into searches:

new slugs are inserted;
changed definitions update their configuration hash;
removed slugs are disabled rather than deleted;
YAML remains configuration, not lifecycle state.

Each selected search creates one search_runs row; searches skipped after a denial record source_skipped diagnostics, not evidence of an attempted or empty search.

searches.last_completed_at advances monotonically in the successful search transaction only. Null timestamps run first, then oldest completion timestamps, with stable registry-order ties. This prevents repeatedly starting with the same groups after HTTP 429. New and substantively modified searches start with null scheduling metadata; editorial changes preserve progress. Removed searches retain disabled history, and disabled searches are not selected. Re-enabling an unchanged search retains its old completion timestamp. The additive migration starts all existing searches at null rather than inventing reviewed scheduling progress.

The timestamp is private operational metadata, never a job observation, quality baseline, or closure signal. It travels with the canonical snapshot, without a separate queue. Publication gates withhold failed working copies, including their scheduling progress. A published but unmerged partial snapshot remains blocked by the reviewed-README barrier; rejection does not authorize collecting over that state. Follow the review recovery procedure.

Successful runs record:

found, accepted, and excluded counts;
warning count;
start and finish timestamps;
duration.

Failed runs store bounded sanitized diagnostics.

Because every search has its own run record and transaction, partial pipeline execution can preserve successful results without applying lifecycle changes from failed searches.

Provenance¶

A job may be discovered through several role, employer, or country searches.

job_searches tracks every association independently:

first observation;
latest accepted observation;
latest successful run;
consecutive explicit-unavailability confirmations;
whether the association is active.

This distinction is fundamental:

absent from one search page
        ≠
explicitly unavailable

Search ranking, pagination, card disappearance, or query changes cannot close a job by themselves.

Successful search transaction¶

Each successful search commits atomically:

insert successful search run + advance search completion timestamp
↓
upsert accepted jobs
↓
insert or refresh provenance
↓
apply explicit 404/410 confirmations
↓
deactivate threshold-reaching associations
↓
close jobs with no active association

A failure rolls back the complete search transaction.

Rediscovery:

refreshes available public metadata;
reactivates provenance;
resets explicit-unavailability confirmations;
can reopen a previously closed job.

Manual job insertion¶

Maintainers may add one known, validated LinkedIn listing through opportunities add-job, or a bounded batch through opportunities add-jobs. The batch command validates every entry before writing and applies the rows in one repository transaction: a rejected entry leaves the whole batch unapplied. Neither command creates a synthetic search, search run, or job_searches association.

For a new row, the command records it as open and initializes first_seen_at from the supplied posting timestamp when present, bounded by the actual observation time. Otherwise, the observation time is used. For an existing open row, normal public fields are refreshed, omitted optional metadata remains preserved, first_seen_at remains immutable, and lifecycle timestamps remain monotonic. A closed row cannot be reopened manually: it requires a successful availability audit or later valid search discovery.

Manual rows participate in all normal read paths and the rotating availability audit. If collection later discovers the same numeric LinkedIn ID, the successful search transaction updates the existing row and attaches genuine search provenance. Manual insertion never changes collection statistics or the latest successful collection timestamp.

Closure lifecycle¶

Search-card absence is never closure evidence.

For each successful search:

Select active jobs associated with that search.
Choose a deterministic bounded subset missing from current eligible cards.
Fetch their detail pages.
Increment confirmation only for HTTP 404 or 410.
Reset confirmations when a valid accepted detail page is observed.
Ignore malformed or newly excluded details as closure evidence.
Apply no lifecycle mutation after unrelated network failures.
Deactivate the association when closure_confirmation_runs is reached.
Close the job only when no active search association remains.
Reopen the job after a later valid rediscovery.

With a threshold of two:

first 404  → confirmation 1; association remains active
second 404 → association becomes inactive

another active association → job remains open
no active association      → job closes

Deleting or disabling search YAML does not close jobs.

The confirmation threshold is configured through OPPORTUNITIES_CLOSURE_CONFIRMATION_RUNS; see Configuration.

Bounded availability audit¶

On each eligible three-day update, the scheduled workflow runs opportunities check-availability before collection; the daily trigger only checks the cadence. Manual combined updates audit too unless explicitly skipped. See the schedule and request limits. Each run selects at most availability_max_jobs rows (built-in default 50; recommended production Actions override OPPORTUNITIES_AVAILABILITY_MAX_JOBS=100) whose last audit attempt is at least availability_interval_days old (default five). Closed rows and manual listings are eligible. Never-checked rows go first, ordered by first_seen_at and ID; previously checked rows follow in oldest-check order. A successful public page without a closure alert is then followed by guest detail validation.

The migration leaves existing rows with a null last_availability_checked_at; it does not fabricate prior checks. The cap applies immediately, so the initial backlog cannot cause a full-database sweep. Excess due rows remain untouched and wait for later runs. Five days is a minimum interval, not a completion guarantee: larger backlogs, failures, or delayed schedules can extend it.

Completed non-denial attempts advance last_availability_checked_at in the audit transaction, including inconclusive responses. This prevents a repeatedly inconclusive low-ID listing from starving other jobs, without updating its lifecycle timestamps or status. Blocked and queued-after-denial jobs do not advance. Search discovery and manual updates do not reset the audit schedule. CLI output reports deferred due jobs; deferral alone is not a partial failure.

Results are deliberately narrow:

public page without a closure alert + valid guest detail → keep or reopen the job
HTTP 404 or 410 from either request                    → delete the job and job_searches rows
scoped “No longer accepting applications” alert       → delete the job and job_searches rows
anything else                                          → preserve the job as inconclusive

The auditor collects batch outcomes before applying confirmed changes and scheduling metadata in one transaction. Rate limits, authentication failures, redirects, server errors, invalid content, and transport failures never become deletion evidence. Search and run history remain available after a job deletion.

An HTTP 301 from an approved numeric public listing preserves the listing's public fields and lifecycle. It counts as an inconclusive audit attempt for scheduling, but does not stop unrelated checks. Its destination is never followed or used as evidence.

A source denial stops new requests, including details not yet started for an in-flight public page. Confirmed results remain in the working database; denied and deferred rows keep their audit priority. An HTTP 429-only partial audit may retain completed results through integrity, projection, and round-trip snapshot checks, followed by a labeled manual-review proposal. Other denials or mixed failures block publication. See the CLI audit results for eligibility, exit codes, and rendering behavior, and Automation for durable publication and review.

Timestamp invariants¶

Concurrent searches may finish out of order.

Persistence orders concurrently fetched search outcomes by finish time and ignores observations older than a job's latest observation or material state change. Individual detail pages may be fetched long before a search finishes, so both accepted jobs and explicit 404 evidence use that search's start time as a conservative observation lower bound. A late finish cannot make old detail evidence reopen a job or advance a 404 confirmation over a newer valid observation. Timestamp updates are monotonic:

next timestamp = max(existing timestamp, observed timestamp)

This applies to job and association observations and prevents state from moving backwards. On a job’s first insert, first_seen_at is initialized from LinkedIn’s relative posting age or an explicitly supplied manual posting timestamp; later observations never rewrite it. Search provenance timestamps represent the run's conservative lower bound, not the exact time each detail page was fetched.

Validation requires:

last_seen_at >= first_seen_at

first_seen_at remains immutable after insertion. For a new row, it is initialized from the inferred LinkedIn posting timestamp when available, bounded so it can never be later than the search start or manual observation time; otherwise it uses that conservative first accepted observation time. Missing posting metadata excludes a listing without explicit target-cycle evidence, but a listing with that evidence in its title or description may still be admitted. Known jobs may also be rechecked safely without treating missing current posting-age metadata as closure evidence.

One-writer model¶

Only one collection or maintenance process may write the database at a time. add-job and add-jobs are narrowly scoped maintainer inputs to the controlled repository writer; they do not create synthetic search provenance. The CLI reuses the deterministic classifier for title, cycle, category, employment type, posting timestamp, and European location; it cannot independently verify operator-supplied source facts offline. Do not insert rows directly into SQLite or edit the website/README/exports to add a listing. Manual entries still require reviewed projection, verified snapshot, and deployment sequencing.

Supported concurrent access:

one controlled application writer;
multiple read-only website requests.

GitHub collection workflows share a concurrency group to preserve this model.

Do not run an independent local or VPS collector against the same file.

The website must open SQLite in read-only mode and must never run migrations or lifecycle mutations.

Migrations¶

Upgrade the configured database:

uv run opportunities db-upgrade

Verify Alembic and SQLAlchemy agreement:

uv run python scripts/database/check_migrations.py

For a schema change:

Update src/opportunities/database/models.py.
Create a new revision under migrations/versions/.
Point down_revision to the current head.
Preserve existing data in upgrade().
Provide a practical downgrade().
Add migration and repository tests.
Test a fresh database.
Test a representative backup when existing state changes.
Run migration consistency checks.

Never rewrite an applied migration to hide schema drift. Coordinate schema changes with compatible application and workflow revisions; protected processing upgrades its restored working copy, not existing immutable snapshots.

Downgrading search-rotation revision d8f3a6b2c901 removes searches.last_completed_at, not job lifecycle history. Preserve a verified backup and reconcile canonical state with the reviewed README before any rollback.

A rebuild is not a normal migration strategy because it loses first-seen history, provenance, closure evidence, and run diagnostics.

Contributor expectations and affected validation paths are summarized in CONTRIBUTING.md.

Backup¶

When SQLite may still be open, use the backup API. Open the source read-only so a missing path fails instead of creating an empty database, and choose an unused backup destination:

uv run python -c "import sqlite3; s=sqlite3.connect('file:data/opportunities.db?mode=ro', uri=True); d=sqlite3.connect('data/opportunities.backup.db'); s.backup(d); d.close(); s.close()"

Store backups outside normal repository cleanup paths and treat verified durable snapshots as the preferred recovery source for production state.

For a cold filesystem copy:

Stop every writer.
Close active SQLite connections where practical.
Checkpoint write-ahead logging.
Copy the database and any required sidecars together.

GitHub Actions checkpoints WAL, then uses the SQLite backup API to create a timestamped snapshot through a restricted VPS SFTP account. Each snapshot has a strict manifest containing its SHA-256 checksum, schema revision, collection and creation timestamps, previous-snapshot reference, and configured retention metadata. The workflow round-trips and opens uploaded files before atomically advancing the latest pointer. A pre-existing local database that does not match the latest snapshot stops restoration instead of being silently replaced; preserve and investigate it before retrying. Canonical SQLite is never placed in GitHub Actions cache or artifacts; 30-day artifacts contain only sanitized public projections and the separate aggregate quality report.

VPS deployment retains immutable previous releases under data/releases/<run-id>-<attempt> and atomically updates data/current; it never removes a release while readers may hold it. Existing opportunities.db.previous files from the legacy deployment path must not be mistaken for the current canonical state after rollout.

Restricted VPS snapshot storage, sanitized artifacts, retention, and deployment sequencing are documented in Automation.

Restore¶

Stop every process that may write the database.
Stop or restart readers that could retain stale file handles.
Preserve the current or damaged database and any SQLite sidecars together, with no connections open. Never overwrite or discard uncheckpointed WAL transactions.
Select a timestamped durable snapshot; inspect its collection time, schema revision, checksum, and previous-snapshot link.
Download both the immutable database object and its manifest.

Verify the manifest and database before replacement:

uv run python scripts/database/canonical_snapshot.py verify \
  --database /safe/recovery/opportunities.db \
  --manifest /safe/recovery/manifest.json

After preserving the old file and its sidecars away from the target path, stage the verified database beside the target and atomically rename it into place. Do not place old sidecars beside the restored file or replace state with unverified bytes.

Upgrade and inspect the restored working database:

uv run opportunities db-upgrade
uv run opportunities stats

When the restored database contains the state expected by the reviewed README, regenerate only the disposable public exports, then validate without rewriting the reviewed documentation:

uv run opportunities export-public
uv run opportunities validate

A mismatch is a stop condition. Do not run render to make a recovered database appear reviewed; reconcile the restored state and reviewed README through the proposal recovery procedure.

This restores the working SQLite file, not the read-only versioned release served through data/current. For a served-projection rollback, follow Coordinated first rollout and rollback; do not write through that pointer. A fresh rebuild loses lifecycle history. Do not delete the database as the first response to migration, locking, or integrity problems.

For symptom-based diagnosis before destructive recovery, use Troubleshooting.

Projection consistency¶

SQLite remains the source of truth for the website, README, public downloads, and versioned API.

Website¶

The website reads every currently open row through short-lived read-only SQLite connections.

It does not:

classify listings;
update jobs;
apply migrations;
close or reopen state.

The website contract belongs to the website engineering guide. The versioned public API reuses the same read-only query with bounded input, pagination, and an explicit public-field allowlist.

README preview¶

render produces:

total open-job count;
latest successful collection time;
the public website link;
a hidden SHA-256 review seal covering all website-visible open rows and the exact latest successful collection time;
at most five open internships and five open New Grad opportunities ordered by immutable first-seen time, then descending ID text. This is not necessarily exact discovery order; first-seen time may reflect approximate source posting age.

The renderer owns the marked opportunity-count and opportunity-preview regions and replaces the resulting README atomically.

validate rebuilds both expected regions in memory and requires exact equality with the committed projection.

Never reconstruct lifecycle state from the README. It omits:

closed jobs;
most open jobs;
provenance;
search runs;
closure confirmations;
operational diagnostics.

Manual edits inside the generated regions are overwritten.

Public exports¶

The pipeline generates open-opportunities.csv and open-opportunities.json from the same currently open rows. Only the approved public export fields are serialized: LinkedIn job ID, company, title, location, listing URL, category, industries, employment type, and start date.

These two downloads exclude job status, all timestamps, provenance, search runs, closure confirmations, diagnostics, and other lifecycle or operational state. The separate dataset-metadata.json manifest contains only the schema version, UTC generation time, row/type counts, and download hashes. All three files are validated against the public schema and canonical rows, replaced atomically one file at a time, and deployed together beside the database for read-only delivery.

The download guide owns the public field contract. These are disposable projections, not backups: none can restore lifecycle history.

Data handling¶

The database should contain public listing metadata and operational history, never:

credentials;
LinkedIn cookies or sessions;
authenticated HTML;
access tokens;
private environment values.

Do not commit databases or sidecars, and do not attach production state to public issues.

Restrict access to:

restricted VPS snapshots and manifests;
backups and independent replicas;
VPS volume state;
temporary deployment copies.

Do not place canonical SQLite or its manifest in GitHub Actions cache or artifacts. Canonical-state workflow artifacts may contain only explicitly sanitized public projections and approved aggregate-only quality reports.

Review the disclosure and handling requirements in SECURITY.md.