Hypermedia Database Schemas

By Olivier Mironescu Added: 2026-07-17

Hypermedia Database Schemas

Full SQL schema for core.hypermedia's tables. Follows the same soft-delete convention as core.storage.

Design Principles

Jobs are durable. Every processing request is recorded before work begins. A crashed worker never loses a job.

Attempts are tracked. Each retry is a separate row. Failure history is preserved for debugging.

Outputs are first-class. Derived files are tracked with their own object_ids (from core.storage) and metadata.

Profiles are versioned. Conversion settings (DPI, quality, format) are versioned so old outputs can be regenerated when settings change.

Workers are registered. The scheduler needs to know which workers exist, what they can do, and whether they're healthy.

Tables

processing_jobs

The central job queue. One row per processing request.

CREATE TABLE processing_jobs (

    job_id              BIGINT PRIMARY KEY AUTO_INCREMENT,

    object_id           INT NOT NULL,                  -- FK → core.storage objects

    pool_id             INT NOT NULL,                  -- FK → core.storage pools (denormalized for queries)

    owner_id            VARCHAR(64) NOT NULL,           -- user who requested processing

    project_id          VARCHAR(64) NOT NULL,           -- core.console project (for billing)

    job_type            VARCHAR(50) NOT NULL,           -- 'image.compress', 'pdf.preview', 'ocr.extract',

                                                        -- 'video.transcode', 'document.convert', etc.

    queue_name          VARCHAR(50) NOT NULL,           -- 'images', 'documents', 'ocr', 'media', 'bulk'

    priority            TINYINT NOT NULL DEFAULT 5,     -- 1=highest, 10=lowest

    status              ENUM('created', 'queued', 'claimed', 'processing',

                             'completed', 'failed', 'cancelled', 'retry_wait')

                        NOT NULL DEFAULT 'created',

    profile_id          INT NOT NULL,                   -- FK → processing_profiles

    params              JSON,                           -- operation-specific parameters

    input_key           VARCHAR(500) NOT NULL,          -- S3 key of source file

    input_size_bytes    BIGINT NOT NULL,

    estimated_credits   INT NOT NULL DEFAULT 0,         -- estimated hypermedia credit cost

    actual_credits      INT,                            -- charged on completion (NULL until done)

    max_attempts        TINYINT NOT NULL DEFAULT 3,

    attempt_count       TINYINT NOT NULL DEFAULT 0,

    timeout_seconds     INT NOT NULL DEFAULT 3600,      -- max execution time per attempt

    claimed_by          VARCHAR(100),                   -- worker_id that claimed this job

    claimed_at          DATETIME,                       -- when the current claim started

    visibility_timeout  DATETIME,                       -- when the claim expires (job becomes available again)

    completed_at        DATETIME,

    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    deleted_at          DATETIME NULL,



    INDEX idx_status_queue (status, queue_name, priority),

    INDEX idx_object (object_id),

    INDEX idx_owner (owner_id),

    INDEX idx_project (project_id),

    INDEX idx_claimed (claimed_by, claimed_at),

    INDEX idx_visibility (visibility_timeout),

    INDEX idx_created (created_at)

);

Notes:

queue_name determines which worker pool picks up the job. Separated by workload type.

priority allows user-facing jobs (1–3) to preempt background jobs (7–10).

claimed_by + claimed_at + visibility_timeout implement the claim pattern. If a worker crashes, the visibility timeout expires and another worker can claim the job.

estimated_credits is set at creation. actual_credits is set on completion and may differ (e.g., OCR discovers fewer pages than estimated).

params is operation-specific JSON. Examples:

- Image: {"width": 800, "format": "webp", "quality": 85}
- OCR: {"language": "fra", "detect_tables": true}
- Video: {"codec": "h264", "bitrate": "2M", "resolution": "1080p"}

job_attempts

Tracks each execution attempt. A job may have multiple attempts if it fails and retries.

CREATE TABLE job_attempts (

    attempt_id          BIGINT PRIMARY KEY AUTO_INCREMENT,

    job_id              BIGINT NOT NULL,                -- FK → processing_jobs

    worker_id           VARCHAR(100) NOT NULL,          -- which worker ran this attempt

    attempt_number      TINYINT NOT NULL,               -- 1, 2, 3...

    status              ENUM('started', 'completed', 'failed', 'timeout', 'crashed')

                        NOT NULL DEFAULT 'started',

    error_message       TEXT,                           -- failure details

    error_code          VARCHAR(50),                    -- machine-readable error type

    started_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    finished_at         DATETIME,

    duration_ms         INT,                            -- wall-clock execution time

    peak_memory_mb      INT,                            -- peak RSS during processing

    cpu_time_ms         INT,                            -- actual CPU time used



    INDEX idx_job (job_id),

    INDEX idx_worker (worker_id),

    INDEX idx_status (status),

    UNIQUE KEY uk_job_attempt (job_id, attempt_number)

);

Notes:

duration_ms + peak_memory_mb + cpu_time_ms feed into the scheduler's scaling decisions and cost estimation refinement.

error_code enables programmatic retry decisions (e.g., OOM → retry with more memory; CORRUPT_INPUT → fail permanently).

job_outputs

Tracks files produced by completed jobs. Each output is a new object in core.storage.

CREATE TABLE job_outputs (

    output_id           BIGINT PRIMARY KEY AUTO_INCREMENT,

    job_id              BIGINT NOT NULL,                -- FK → processing_jobs

    output_type         VARCHAR(50) NOT NULL,           -- 'thumbnail', 'preview', 'text', 'transcoded', 'ocr_result'

    object_id           INT NOT NULL,                   -- FK → core.storage objects (the derived file)

    output_key          VARCHAR(500) NOT NULL,          -- S3 key of the derived file

    output_size_bytes   BIGINT NOT NULL,

    mime_type           VARCHAR(255) NOT NULL,

    metadata            JSON,                           -- type-specific metadata

    profile_version     VARCHAR(50) NOT NULL,           -- which profile version produced this

    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    deleted_at          DATETIME NULL,



    INDEX idx_job (job_id),

    INDEX idx_object (object_id),

    INDEX idx_type (output_type),

    UNIQUE KEY uk_job_type_profile (job_id, output_type, profile_version)

);

Notes:

metadata is type-specific. Examples:

- Thumbnail: {"width": 200, "height": 150, "format": "webp"}
- OCR: {"language": "fra", "pages": 12, "confidence_avg": 0.94, "method": "paddleocr"}
- Video: {"duration_s": 120, "codec": "h264", "resolution": "1920x1080"}

uk_job_type_profile prevents duplicate outputs from the same job+profile version (idempotency).

processing_profiles

Versioned conversion settings. When settings change, a new profile version is created — old outputs remain reproducible.

CREATE TABLE processing_profiles (

    profile_id          INT PRIMARY KEY AUTO_INCREMENT,

    profile_key         VARCHAR(100) NOT NULL,          -- 'image-thumbnail', 'pdf-preview', 'ocr-standard', 'ocr-ai'

    profile_version     VARCHAR(20) NOT NULL,           -- 'v1', 'v2', 'v3'...

    job_type            VARCHAR(50) NOT NULL,           -- matches processing_jobs.job_type

    queue_name          VARCHAR(50) NOT NULL,           -- default queue for this profile

    settings            JSON NOT NULL,                   -- conversion parameters

    credit_cost_base    INT NOT NULL,                   -- base credit cost per operation

    credit_cost_per_mb  INT NOT NULL DEFAULT 0,         -- additional credits per MB of input

    credit_cost_per_page INT NOT NULL DEFAULT 0,        -- additional credits per page (documents)

    credit_cost_per_minute INT NOT NULL DEFAULT 0,      -- additional credits per minute (media)

    timeout_seconds     INT NOT NULL DEFAULT 3600,

    max_input_bytes     BIGINT NOT NULL DEFAULT 5368709120, -- 5 GB default max

    is_active           BOOLEAN NOT NULL DEFAULT TRUE,

    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,



    UNIQUE KEY uk_key_version (profile_key, profile_version),

    INDEX idx_job_type (job_type),

    INDEX idx_active (is_active)

);

Notes:

settings contains all conversion parameters. Examples:

- image-thumbnail:v3: {"width": 200, "height": 200, "format": "webp", "quality": 85, "fit": "cover"}
- ocr-standard:v2: {"engine": "paddleocr", "languages": ["fra", "deu", "eng"], "dpi": 300}
- video-h264:v1: {"codec": "libx264", "preset": "slow", "crf": 23, "audio_codec": "aac"}

Old profile versions are never deleted — they're set is_active = FALSE to prevent new jobs from using them.

workers

Registered worker instances. The scheduler uses this table to track capacity and health.

CREATE TABLE workers (

    worker_id           VARCHAR(100) PRIMARY KEY,       -- unique identifier (hostname or cloud instance ID)

    worker_type         ENUM('vps', 'cloud_aws', 'cloud_google', 'cloud_ionos')

                        NOT NULL DEFAULT 'vps',

    status              ENUM('active', 'idle', 'draining', 'terminated', 'unhealthy')

                        NOT NULL DEFAULT 'active',

    queues              JSON NOT NULL,                   -- which queues this worker processes: ["images", "documents"]

    max_concurrency     TINYINT NOT NULL DEFAULT 1,     -- max simultaneous jobs

    current_jobs        TINYINT NOT NULL DEFAULT 0,     -- currently processing

    cpu_cores           TINYINT NOT NULL,

    memory_mb           INT NOT NULL,

    disk_mb             INT NOT NULL,

    cost_per_hour_cents INT NOT NULL DEFAULT 0,         -- for cloud workers (0 for existing VPS)

    last_heartbeat      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    started_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    terminated_at       DATETIME,

    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,



    INDEX idx_status (status),

    INDEX idx_type (worker_type),

    INDEX idx_heartbeat (last_heartbeat)

);

Notes:

worker_id is a string, not auto-increment. VPS workers use hostname; cloud workers use instance ID.

queues determines which jobs this worker can claim. An image worker doesn't pull video jobs.

last_heartbeat is updated every 30 seconds by the worker. If it goes stale (> 2 minutes), the worker is marked unhealthy and its claimed jobs are released.

cost_per_hour_cents is used by the scheduler to estimate cloud spend. Zero for the existing VPS.

worker_metrics

Periodic resource usage snapshots. Feeds the scheduler's scaling decisions.

CREATE TABLE worker_metrics (

    metric_id           BIGINT PRIMARY KEY AUTO_INCREMENT,

    worker_id           VARCHAR(100) NOT NULL,          -- FK → workers

    cpu_percent         DECIMAL(5,2) NOT NULL,

    memory_percent      DECIMAL(5,2) NOT NULL,

    disk_percent        DECIMAL(5,2) NOT NULL,

    jobs_completed_1m   INT NOT NULL DEFAULT 0,         -- jobs completed in last minute

    jobs_failed_1m      INT NOT NULL DEFAULT 0,

    avg_job_duration_ms INT NOT NULL DEFAULT 0,         -- rolling average

    recorded_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,



    INDEX idx_worker_time (worker_id, recorded_at),

    INDEX idx_recorded (recorded_at)

);

Notes:

Metrics are recorded every 60 seconds by each worker.

Retained for 7 days, then aggregated to hourly summaries.

The scheduler reads recent metrics to decide when to provision or terminate cloud workers.

Entity Relationships

core.storage objects

        │

        │  (object_id)

        ▼

┌──────────────────┐       ┌───────────────┐

│ processing_jobs  │──────>│ job_attempts  │

│                  │       └───────────────┘

│  profile_id ──────────> processing_profiles

│  claimed_by ─────────> workers

└────────┬─────────┘       ┌───────────────┐

         │                 │ worker_metrics│

         │                 └───────────────┘

         │  (job_id)

         ▼

  ┌─────────────┐

  │ job_outputs │──────> core.storage objects (derived files)

  └─────────────┘

Job State Machine

created

  │

  ▼

queued ──────────────────────────────────────────┐

  │                                              │

  ▼                                              │

claimed ──> processing ──> completed             │

  │              │                               │

  │              ├──> failed ──> retry_wait ──> queued (if attempts remaining)

  │              │         │                     │

  │              │         └──> failed (permanent, no attempts left)

  │              │                               │

  │              └──> timeout ──> retry_wait ──> queued

  │                            │

  │                            └──> failed (permanent)

  │

  └── (visibility timeout expires, job returns to queued)



Any state ──> cancelled (user or admin action)

Worker Architecture — How jobs flow through the system

Processing Pipelines — What each job type does

Scheduler — How workers are provisioned and managed

Storage Database Schemas — objects, pools tables

Core Docs

Hypermedia Database Schemas

Hypermedia Database Schemas

Design Principles

Tables

processing_jobs

job_attempts

job_outputs

processing_profiles

workers

worker_metrics

Entity Relationships

Job State Machine

Related