Jobs vs runs, the three durable tables, record kinds, and how to query them from dashboards

Storage Model

OqronKit uses a two-plane persistence model, and the durable plane is designed to be queried directly — by dashboards, boards, and your own SQL.

┌───────────────────────────────┐   ┌──────────────────────────────────┐
│  HOT PLANE (Redis / memory)   │   │  DURABLE PLANE (Postgres / …)    │
│  broker queues · locks        │   │  definitions · control state     │
│  atomic counters · cache      │   │  jobs · dead letters · runs      │
│  live run overlay             │   │  module logs                     │
└───────────────────────────────┘   └──────────────────────────────────┘
     speed & coordination                truth & history

memory mode: one in-process backend serves both planes.
redis mode: Redis serves both.
redis-postgres mode: Redis is hot, Postgres is durable.

Jobs vs Runs — never the same thing

The two records people most often confuse:

	Job	Run
What it is	The work order — "this must be done"	The receipt — "here's what happened when we tried"
Exists	Before any execution (`queue.add()`, durable cron slot)	Only when an execution starts
Cardinality	1	N — one per attempt cycle
Drives	Correctness: claiming, retries, crash recovery, DLQ	Observability: logs, timeline, duration, output/error
Safe to delete?	Deleting a waiting job loses work	Deleting runs loses only history

Proof they're distinct — the cardinality is not 1:1:

Job "send-email-42"  ──► Run #1 (failed,   500ms)   ← attempt 1
                     ──► Run #2 (failed,   480ms)   ← attempt 2
                     ──► Run #3 (completed, 510ms)  ← attempt 3

Cron "heartbeat"     ──► Run (completed)            ← run with NO job
Job just added       ──► (no runs yet)              ← job with ZERO runs

A run snapshots the job's payload as input so history stays readable after the job is pruned by retention.

The three durable tables

The durable plane is split by storage class — because Postgres vacuums, bloats, and partitions per table, and the three workloads behave completely differently:

Table	Holds	Workload
`oqron_records`	definitions, control state, webhook endpoints, circuit breakers, module logs	tiny, hot-read config plane
`oqron_jobs`	jobs + dead letters	high churn (insert → update × N → delete)
`oqron_runs`	run history + the pub/sub retained message log	append-heavy, bulk-pruned by retention

All three share one shape:

project      text    -- 'my-app'
environment  text    -- 'production'
namespace    text    -- LOGICAL namespace: 'queue_job:emails', 'runs', 'cron'
record_kind  text    -- 'job' | 'run' | 'definition' | ... (auto-stamped)
id           text
value        jsonb   -- the record
inserted_at  bigint
expires_at   bigint  -- optional TTL
PRIMARY KEY (project, environment, namespace, id)

project and environment are real columns — multiple apps and environments share one database with true row-level isolation, symmetric with the Redis key-prefix isolation on the hot plane.

Record kinds

Every row is stamped with a record_kind derived from its namespace — no string parsing needed to know what a row is:

record_kind	Namespaces	Meaning
`definition`	`cron`, `schedule`, `cache`, `ratelimit`, `topic`, `webhook_endpoints:*`	What's defined + its scheduling/config state
`control`	`queue`, `worker`, `webhook`, `topic_state`, `topic_groups:*`, `cron_durable`, `schedule_durable`	Pause/version state
`job`	`queue_job:*`	Durable work orders (includes pub/sub group deliveries)
`dlq`	`queue_dlq:*`	Dead-lettered jobs (retries exhausted)
`run`	`runs`	Execution history
`message`	`topic_msg:*`	Retained pub/sub message log (audit, backfill, replay)
`log`	`module_logs:*`	Opt-in module logs
`state`	`webhook_cb`	Circuit-breaker state
`record`	anything else	Custom namespaces

The classifier is exported so your tools use the same taxonomy:

import { classifyNamespace, tableForKind } from "oqronkit";

classifyNamespace("queue_job:emails"); // 'job'
tableForKind("job"); // 'jobs' → oqron_jobs

Querying from a dashboard

Plain SQL — no library required:

-- Failed runs in production, most recent first
SELECT value FROM oqron_runs
WHERE project = 'my-app' AND environment = 'production'
  AND value->>'status' = 'failed'
ORDER BY (value->>'startedAt')::numeric DESC
LIMIT 50;

-- Dead-letter depth per queue
SELECT namespace, count(*) FROM oqron_jobs
WHERE project = 'my-app' AND environment = 'production'
  AND record_kind = 'dlq'
GROUP BY namespace;

-- Every definition the app runs (crons, schedules, caches, limiters, webhooks)
SELECT namespace, id, value FROM oqron_records
WHERE project = 'my-app' AND environment = 'production'
  AND record_kind = 'definition';

-- A specific run with full logs + timeline
SELECT value FROM oqron_runs
WHERE project = 'my-app' AND environment = 'production'
  AND id = '<run-id>';

-- Retained pub/sub log for one topic, newest first
SELECT value FROM oqron_runs
WHERE project = 'my-app' AND environment = 'production'
  AND record_kind = 'message' AND namespace = 'topic_msg:order-events'
ORDER BY (value->>'publishedAt')::numeric DESC
LIMIT 50;

Hot run-query fields are indexed: value->>'status', value->>'name', value->>'module', value->>'startedAt'.

The run record

{
  "id": "…",
  "name": "emails",             // definition / queue name
  "module": "queue",            // cron | schedule | queue | worker | webhook
  "status": "completed",        // queued | running | completed | failed | cancelled | held
  "trigger": "queue",
  "input": { "to": "a@x.com" },
  "output": { "delivered": true },
  "attempts": 1,
  "progressPercent": 100,
  "logs": [{ "level": "info", "msg": "…", "ts": 1751587201000 }],
  "timeline": [{ "ts": …, "from": "waiting", "to": "running", "reason": "…" }],
  "error": null,
  "app": "my-app",
  "environment": "production",
  "workerId": "node-uuid",
  "startedAt": 1751587201000,
  "endedAt": 1751587203000,
  "durationMs": 2000
}

Runs are written non-blocking: created instantly in memory + written through to the hot plane, then flushed to Postgres in the background (immediately on completion). Recording history never slows job execution.

What lives where — quick reference

Data	Plane	Why
Queue/worker/webhook/pub-sub jobs	Durable-first, hot broker for claiming	Work must survive crashes
Run history	Hot write-through → durable write-behind	Rich history, zero hot-path cost
Pub/sub message log	Durable (shares the `runs` table)	Retained for late-group backfill, replay, audit
Cron/schedule pointers (`nextRunAt`)	Durable	Schedules survive restarts
Cache entries, rate-limit counters, bans	Hot only	Ephemeral by nature; TTL-managed
Locks, leader election, broker claims	Hot only	Coordination state
Definitions + pause state	Durable	The board's source of truth

Migration note

Upgrading from the earlier single-table (oqron_kv) layout is not automatic, and it fails quietly.

v2 creates its three tables with CREATE TABLE IF NOT EXISTS and never reads oqron_kv. There is no detection, no error, and no migration — so pointing v2 at a v1 database starts from an empty state while the old rows sit orphaned. Pending jobs are not picked up and schedule pointers reset to "never run".

Drain your v1 deployment before switching over, then drop oqron_kv yourself once you've confirmed nothing else needs it.

Storage Model

On this page