# venturi default adapter schema
Date: 2026-06-07
This document defines the PostgreSQL schema of venturi's **default storage
adapter**. Storage sits behind a backend trait (ADR 8), so these tables are the
default adapter's concern, not something the worker loop, registry, or task layers
depend on. The column and index choices realize decisions recorded across the
ADRs; the access paths the indexes serve are tracked in `docs/design/indexes.md`.
The adapter owns two tables, named from a configurable prefix (ADR 6):
`{{prefix}}_jobs` (the live queue and the durable job record) and
`{{prefix}}_journal` (the append-only per-execution log). The literal token
`{{prefix}}` below is what the migration files contain; it is replaced with the
configured prefix when migrations are applied.
## Migrations
Migrations are authored as ordinary SQL files that use the `{{prefix}}` token
wherever a table or index name appears. At apply time the adapter reads each file,
replaces `{{prefix}}` with the configured prefix using a plain string substitution,
and runs the result through refinery's runner, with refinery's migration-history
table name also set per prefix (ADR 24). Two queues with different prefixes in the
same database therefore track and apply their migrations independently.
The V1 migration is exactly the table and index DDL in this document, written with
the `{{prefix}}` placeholder. Index and constraint names are prefixed too, so for
a long prefix they must stay within PostgreSQL's 63-character identifier limit.
## `{{prefix}}_jobs`
```sql
CREATE TABLE {{prefix}}_jobs (
id text PRIMARY KEY,
kind text NOT NULL,
payload jsonb NOT NULL,
priority smallint NOT NULL DEFAULT 1
CHECK (priority IN (0, 1, 2)),
status text NOT NULL
CHECK (status IN ('pending', 'claimed', 'completed', 'dead')),
created_at timestamptz NOT NULL,
visible_at timestamptz NOT NULL,
claim_expires_at timestamptz,
claimed_by text,
finished_at timestamptz,
run_count integer NOT NULL DEFAULT 0,
failure_count integer NOT NULL DEFAULT 0,
carry jsonb NOT NULL DEFAULT 'null'::jsonb,
dedup_key text
);
```
| `id` | `text` | no | — | The job's ULID in its canonical 26-character form; primary key (ADR 2). |
| `kind` | `text` | no | — | The task `KIND` discriminator that routes the job to its handler (task model). |
| `payload` | `jsonb` | no | — | The serialized task payload. |
| `priority` | `smallint` | no | `1` | Tier: 0 High, 1 Normal, 2 Low. Numeric so `ORDER BY priority ASC` puts High first; the `CHECK` pins the three tiers (ADR 22). |
| `status` | `text` | no | — | Lifecycle state, constrained to the four states (ADR 5). |
| `created_at` | `timestamptz` | no | — | Enqueue time; the age tiebreak in claim ordering (ADR 3). |
| `visible_at` | `timestamptz` | no | — | Eligibility gate: equals the enqueue time for immediate work, a future time for delayed, backoff, paused, or scheduled work (ADR 12, ADR 20). |
| `claim_expires_at` | `timestamptz` | yes | — | Lease expiry, set at claim; null when the row is not `claimed` (ADR 19). |
| `claimed_by` | `text` | yes | — | Claiming worker identity (`host:pid`); null when not `claimed` (ADR 19, ADR 20). |
| `finished_at` | `timestamptz` | yes | — | Set when the job reaches `completed` or `dead`; drives history queries by completion time (ADR 5, ADR 18). |
| `run_count` | `integer` | no | `0` | Incremented at each claim; read by `ctx.run_count()` (ADR 15). |
| `failure_count` | `integer` | no | `0` | Incremented on a retryable failure or a stale-recovery; the backstop reads it (ADR 13, ADR 19). |
| `carry` | `jsonb` | no | `'null'::jsonb` | The typed carried state; the default-empty `()` serializes to JSON `null` (ADR 15). |
| `dedup_key` | `text` | yes | — | Deduplication candidacy key; null means the task is never coalesced (ADR 10). |
## `{{prefix}}_journal`
```sql
CREATE TABLE {{prefix}}_journal (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
job_id text NOT NULL
REFERENCES {{prefix}}_jobs (id) ON DELETE CASCADE,
kind text NOT NULL,
run_no integer NOT NULL,
recorded_at timestamptz NOT NULL,
outcome text NOT NULL
CHECK (outcome IN ('completed', 'paused', 'retried',
'dead', 'stale-recovered',
'released', 'merged')),
note text,
attachment jsonb
);
```
| `id` | `bigint` identity | no | Surrogate primary key for the append-only log. |
| `job_id` | `text` | no | The job this entry belongs to; `ON DELETE CASCADE` gives unified cleanup, so removing a job removes its journal (ADR 16, ADR 18). |
| `kind` | `text` | no | Denormalized from the job, so the journal is queryable by kind without joining `{{prefix}}_jobs` (ADR 16, ADR 18). |
| `run_no` | `integer` | no | The run number this entry records. |
| `recorded_at` | `timestamptz` | no | When the entry was written. |
| `outcome` | `text` | no | The recorded outcome, constrained to the execution and lifecycle outcomes (ADR 11, ADR 16, ADR 19, ADR 21, ADR 10). |
| `note` | `text` | yes | The run's conclusion, or on failure the error message (ADR 11). |
| `attachment` | `jsonb` | yes | Structured evidence set via `ctx.set_attachment` during the run (ADR 15). |
## Indexes
Each index realizes an access path from `docs/design/indexes.md`.
```sql
-- #1 Claim path: highest-priority oldest eligible row per kind (ADR 3, 20, 22).
CREATE INDEX {{prefix}}_jobs_claim
ON {{prefix}}_jobs (kind, priority, created_at)
WHERE status = 'pending';
-- #2 Soonest future eligibility, for the worker's wait timeout (ADR 20).
CREATE INDEX {{prefix}}_jobs_visible
ON {{prefix}}_jobs (kind, visible_at)
WHERE status = 'pending';
-- #3 Deduplication candidacy lookup (ADR 10). Non-unique (see note).
CREATE INDEX {{prefix}}_jobs_dedup
ON {{prefix}}_jobs (kind, dedup_key)
WHERE dedup_key IS NOT NULL AND status = 'pending';
-- #4 Stale-claim recovery: expired leases (ADR 19).
CREATE INDEX {{prefix}}_jobs_lease
ON {{prefix}}_jobs (claim_expires_at)
WHERE status = 'claimed';
-- #5/#6 History listing and terminal-job cleanup (ADR 5, ADR 18).
CREATE INDEX {{prefix}}_jobs_history
ON {{prefix}}_jobs (status, kind, finished_at);
-- #7 Journal per-job timeline and the FK cascade (ADR 16, ADR 18).
CREATE INDEX {{prefix}}_journal_job
ON {{prefix}}_journal (job_id);
-- #8 Journal global queries by kind over time (ADR 16, ADR 18).
CREATE INDEX {{prefix}}_journal_kind
ON {{prefix}}_journal (kind, recorded_at);
```
Notes on specific indexes:
- **`_jobs_claim` and the `visible_at` predicate.** The index is partial on
`status = 'pending'` and ordered `(kind, priority, created_at)`, which serves the
claim's kind membership and its `ORDER BY priority, created_at` per kind; with
`LIMIT 1 FOR UPDATE SKIP LOCKED` the planner takes the highest-priority oldest
eligible row. The `visible_at <= now()` eligibility test is applied as a residual
filter rather than carried in the index, because a btree cannot both range-filter
`visible_at` and preserve the `(priority, created_at)` ordering. This is
acceptable because not-yet-visible pending rows are normally a small minority:
most pending work is immediately visible, and delayed, backoff, paused, or
scheduled rows are the exception and become visible soon. A deployment that
accumulates many far-future rows would scan past more of them here.
- **`_jobs_visible`** is a separate partial-pending index because the wait-timeout
lookup orders by `visible_at`, a different ordering than the claim index's
`(priority, created_at)`; one btree cannot serve both orderings (noted in the
tracker).
- **`_jobs_dedup` is non-unique.** The `merge` decision's `Independent` outcome
(ADR 10) permits more than one pending row with the same `(kind, dedup_key)`, so
the index is a lookup aid, not a uniqueness constraint.
- **`_jobs_history` serves both history listing and cleanup.** Listing filters
`status` + `kind` + a `finished_at` window directly. Age-only cleanup
(`status IN ('completed','dead') AND finished_at < cutoff`, no kind) uses the
`status` prefix and then filters `finished_at`, scanning that status's rows;
acceptable for a bulk maintenance operation. If age-only cleanup ever needs to be
cheaper, a dedicated `(status, finished_at)` index can be added.
- PostgreSQL does not auto-create an index on a foreign key's referencing column,
so `_journal_job` is defined explicitly to back both the per-job timeline read
and the `ON DELETE CASCADE`.
## Out of scope / not yet decided
- **Rate-control state.** Columns or tables for throttling a kind over time are
deferred and tracked as a todo; nothing here supports rate limiting.
- **Global cross-worker coordination tables.** Per-kind concurrency caps are
local, in-memory worker state (ADR 23), so no shared coordination table exists;
any future global cap or rate limit would add its own.
- **Observability and metrics tables.** Logging, queue-state introspection, and
metric emission are a separate part and add no schema here.