Data Model Reference

Quick reference for HealthArchive database models.

Full details: See Architecture Guide

Entity Relationship

erDiagram
    Source ||--o{ ArchiveJob : has
    Source ||--o{ Snapshot : has
    Source ||--o{ AnnualEdition : has
    AnnualEdition ||--o{ ArchiveJob : contains
    ArchiveJob ||--o{ Snapshot : produces

    Source {
        int id PK
        string code UK
        string name
        string base_url
        bool enabled
    }

    ArchiveJob {
        int id PK
        int source_id FK
        int edition_id FK
        string name
        string status
        string output_dir
        string shard_key
        string shard_kind
        string acceptance_state
        json config
        int warc_file_count
        int indexed_page_count
    }

    AnnualEdition {
        int id PK
        int source_id FK
        int year
        string status
        bool search_ready
        bool research_ready
        int intended_url_count
        int captured_url_count
    }

    Snapshot {
        int id PK
        int job_id FK
        int source_id FK
        string url
        string normalized_url_group
        datetime capture_timestamp
        string title
        string snippet
        string language
        string capture_backend
        string capture_fidelity
    }

Source

Represents a content origin (e.g., Health Canada, PHAC).

Table: sources

Indexes: - Unique on code

Relationships: - jobs: One-to-many → ArchiveJob - snapshots: One-to-many → Snapshot - annual_editions: One-to-many → AnnualEdition

AnnualEdition

Represents one researcher-facing annual archive for {source, year}. An edition can be built from many shard jobs and may include legacy full-site salvage jobs.

Table: annual_editions

Indexes: - Unique on source_id, year - Index on status - Index on year

Relationships: - source: Many-to-one → Source - jobs: One-to-many → ArchiveJob

ArchiveJob

Represents a single crawl job execution.

Table: archive_jobs

Indexes: - Index on source_id - Index on edition_id - Index on shard_key - Index on acceptance_state - Index on status - Index on queued_at

Relationships: - source: Many-to-one → Source - edition: Many-to-one → AnnualEdition - snapshots: One-to-many → Snapshot

Snapshot

Represents a single captured web page.

Table: snapshots

Indexes: - Index on job_id - Index on source_id - Index on url - Index on normalized_url_group - Index on capture_timestamp - Index on status_code - Index on capture_backend - Index on capture_fidelity

Relationships: - job: Many-to-one → ArchiveJob - source: Many-to-one → Source

Job Status Lifecycle

stateDiagram-v2
    [*] --> queued: create-job
    queued --> running: worker starts
    running --> completed: crawl succeeds
    running --> failed: crawl fails
    failed --> retryable: retry if count < MAX
    retryable --> running: worker retries
    completed --> indexing: index-job starts
    indexing --> indexed: indexing succeeds
    indexing --> index_failed: indexing fails
    indexed --> [*]
    failed --> [*]
    index_failed --> completed: retry-job (reindex)

Common status values: - queued - Job created, waiting for worker - running - Crawl in progress - completed - Crawl succeeded - failed - Crawl failed (terminal if retries exhausted) - retryable - Failed but can retry - indexing - WARC indexing in progress - indexed - Fully indexed and ready to serve - index_failed - Indexing failed

Config JSON Schema

ArchiveJob.config structure:

{
  "seeds": [
    "https://www.canada.ca/en/health-canada.html"
  ],
  "zimit_passthrough_args": [
    "--profile", "social-media"
  ],
  "tool_options": {
    "cleanup": false,
    "overwrite": false,
    "skip_final_build": false,
    "enable_monitoring": false,
    "enable_adaptive_workers": false,
    "enable_adaptive_restart": false,
    "enable_vpn_rotation": false,
    "initial_workers": 2,
    "log_level": "INFO",
    "relax_perms": true,
    "docker_shm_size": "1g",
    "monitor_interval_seconds": 30,
    "stall_timeout_minutes": 30,
    "error_threshold_timeout": 10,
    "error_threshold_http": 10,
    "min_workers": 1,
    "max_worker_reductions": 2,
    "vpn_connect_command": "vpn connect ca",
    "max_vpn_rotations": 3,
    "vpn_rotation_frequency_minutes": 60,
    "backoff_delay_minutes": 15
  }
}

See: Job Registry for defaults per source

Database Configuration

Location: src/ha_backend/models.py

ORM: SQLAlchemy 2.0

Migrations: Alembic (in alembic/ directory)

Running Migrations

# Upgrade to latest
alembic upgrade head

# Downgrade one revision
alembic downgrade -1

# Show current revision
alembic current

# Show migration history
alembic history

Supported Databases

• SQLite (default for dev): sqlite:///healtharchive.db
• PostgreSQL (recommended for production): postgresql://user:pass@host/dbname

Environment variable: HEALTHARCHIVE_DATABASE_URL

Common Queries

Find jobs by status

from ha_backend.models import ArchiveJob
from ha_backend.db import get_session

session = get_session()
jobs = session.query(ArchiveJob).filter_by(status="queued").all()

Get source with all jobs

from ha_backend.models import Source

session = get_session()
source = session.query(Source).filter_by(code="hc").one()
print(f"{source.name}: {len(source.jobs)} jobs")

Find snapshots by URL

from ha_backend.models import Snapshot

session = get_session()
snapshots = session.query(Snapshot).filter(
    Snapshot.url.like("%health-canada%")
).limit(10).all()

Related Documentation

• Full Architecture Guide: architecture.md
• Job Creation: architecture.md#4-job-registry--creation
• Indexing Pipeline: architecture.md#6-indexing-pipeline
• CLI Commands: cli-commands.md