# Task PostgreSQL Storage

This document records the operational shape for task PostgreSQL schemas.
It applies to task lifecycle storage, task artifact metadata, queue tables,
event and usage records, and task idempotency records.

For worker deployment, environment configuration, and failure-mode runbooks,
see [Task queue operations](TASK_OPERATIONS.md).

## Migration Framework

Alembic is the authoritative schema migration framework for task PostgreSQL
schemas. Revisions are hand-authored PostgreSQL migrations rather than
autogenerated ORM metadata. Runtime task store modules must not apply schema
changes on hot request paths.

Task PostgreSQL revisions are stored under the task-owned Alembic script
location in `src/avalan/task/stores/pgsql_migrations/`. Runtime modules expose
programmatic helpers for applying, checking, inspecting, and stamping schema
state, but they do not own DDL constants or apply migrations automatically.

## Revision Layout

Task revisions live in a task-owned Alembic script location. Memory
PostgreSQL revisions live in `src/avalan/memory/permanent/pgsql_migrations/`
with a separate revision history. Task migrations must not import memory
modules or require pgvector.

The task migration version table is named `avalan_task_alembic_version` by
default. Deployments that use multiple PostgreSQL schemas may configure the
schema/search path explicitly; migration helpers must pass that setting to
Alembic rather than relying on process-global state.

## DDL Policy

Task schema migrations use PostgreSQL transactional DDL where PostgreSQL
supports it. Migrations are forward-only for production recovery. Downgrade
functions may be omitted or raise a clear diagnostic when a change cannot be
safely reversed.

Concurrent migration runners are fenced with PostgreSQL advisory locks. The
lock key is stable for task migrations and independent from memory migration
locks. Helpers should expose `upgrade`, `current`, `check`, and `stamp` so
operators can apply, inspect, validate, or intentionally mark schema state.

Before applying production migrations, take a database snapshot or provider
backup and run `avalan task pgsql check` against the target schema. If a
forward migration fails after partial infrastructure rollout, restore the
database backup or apply a new corrective forward revision, then run
`avalan task pgsql check` again. Use `stamp` only after manually verifying
that the live schema already matches the intended revision; stamping is not a
repair mechanism for mismatched objects.

## Schema Shape

Task rows keep privacy-sanitized snapshots in JSONB only where the structure is
not part of hot operational lookup. Definition snapshots, run requests, claims,
results, contexts, artifact references, idempotency digests, metadata, and
retention records are constrained to object-shaped JSONB so malformed scalar or
array payloads fail at the database boundary.

Hot lifecycle fields stay in typed columns. Run state, queue name, queue item
state, priority, availability, worker id, claim token, heartbeat time, lease
expiry, attempt counts, artifact byte retention deadlines, and usage counters
are indexed as columns rather than extracted from JSONB during queue, worker,
retention, or inspection paths.

Task usage records expose `input_tokens`, `cached_input_tokens`,
`cache_creation_input_tokens`, `output_tokens`, `reasoning_tokens`, and
`total_tokens`. Exact records come only from provider-returned usage metadata.
Estimated records come from Avalan or local counters. Missing or malformed
provider counters remain unavailable and are stored as `NULL`; reported zero is
preserved as zero. Avalan does not derive provider totals, cache counters, or
reasoning counters from other fields.

`usage_totals` represents billable run burn across all recorded attempts.
Stores may receive a stable usage id for a provider call; repeated observations
with the same id return the existing record instead of incrementing counters.
Distinct provider calls must use distinct ids. Usage metadata is allowlisted to
low-cardinality `provider_family` values and numeric cache TTL buckets:
`cache_creation_ephemeral_5m_input_tokens`,
`cache_creation_ephemeral_1h_input_tokens`,
`cache_read_ephemeral_5m_input_tokens`, and
`cache_read_ephemeral_1h_input_tokens`. It drops raw provider objects, model
ids, deployment names, endpoints, headers, request ids, response ids, cache
handles, filenames, paths, and unknown nested fields.

Anthropic `input_tokens` is the provider-reported uncached input count.
When Anthropic reports all three fields, total input for cache analysis is
`input_tokens + cache_creation_input_tokens + cached_input_tokens`. Anthropic
cache-creation TTL buckets remain metadata only; they do not replace or
derive `cache_creation_input_tokens`.

Bedrock cache write fields such as `cacheWriteInputTokens` and
`cache_write_input_tokens` map to `cache_creation_input_tokens`. Bedrock
reasoning tokens remain unavailable unless a model returns an explicit exact
reasoning counter; visible reasoning text, signatures, redacted content, and
reasoning configuration are not counters.

Google/Gemini usage maps `promptTokenCount` or `prompt_token_count` to
`input_tokens`, `candidatesTokenCount` or `candidates_token_count` to
`output_tokens`, `totalTokenCount` or `total_token_count` to `total_tokens`,
`thoughtsTokenCount` or `thoughts_token_count` to `reasoning_tokens`, and
`cachedContentTokenCount` or `cached_content_token_count` to
`cached_input_tokens`. Google prompt token counts may include cached content;
the cached-content counter is the cached subset and is not cache-write cost for
separately created cached content.

Provider compatibility follows this matrix:

| Provider family | Usage source |
| --- | --- |
| `openai`, `azure_openai` | Provider-reported OpenAI Responses `usage` fields. Cache, reasoning, and total counters are exact only when returned by the provider. |
| `openai_compatible` | Provider-returned OpenAI-compatible `usage` fields. Cache and reasoning counters are unavailable unless the adapter response includes compatible detail fields. |
| `anthropic` | Provider-reported message usage. Cache creation, cache read, and TTL bucket fields are preserved when Anthropic returns them. |
| `bedrock` | Provider-reported metadata usage. Cache write fields map to cache creation; reasoning remains unavailable unless an explicit counter is returned. |
| `google` | Provider `usage_metadata` or `usageMetadata` fields. Cached content is tracked as a cached subset, not cache creation cost. |
| `hugging_face` | Provider-returned OpenAI-compatible `usage` fields when present. Otherwise exact usage remains unavailable. |
| `ollama`, `local` | Local token counters remain estimated. Exact cache, cache creation, reasoning, and provider total counters remain unavailable unless explicit provider metadata is returned through a supported adapter. |
| `other` | Exact counters require explicit provider usage metadata that matches supported field names; otherwise usage is unavailable or estimated from Avalan counters. |

Task events use a single `task_events` table with a unique `(run_id,
sequence)` constraint and indexes for incremental run and attempt inspection.
This layout is the default profile for deployments that keep retained task
events below 5 million rows per schema and whose EXPLAIN plans for append and
incremental fetch use `ix_task_events_by_run_sequence`. Above that threshold,
or when plans show sequential scans on `task_events`, introduce a forward
partitioning revision before increasing retention or worker throughput. The
partitioning key should preserve ordered per-run fetches and keep sanitized
payloads separate from lifecycle transition state.

Artifact byte rows store encrypted bytes only with explicit key id, algorithm,
encryption metadata, retention days, and a concrete retention deadline. Cleanup
and orphan detection use partial indexes over active byte rows; sanitized
artifact metadata remains in task artifact records after raw bytes are deleted.

## Optional Dependencies

Runtime PostgreSQL task storage uses the `task-pgsql` extra. Migration tooling
is dependency-gated on Alembic and SQLAlchemy so base installs and direct
in-memory task usage do not install PostgreSQL, Alembic, SQLAlchemy, pgvector,
or asyncpg.

When migration dependencies are missing, CLI and SDK entry points must return
the stable feature-gate diagnostic
`dependency.task_pgsql_migrations_missing` without leaking import exceptions,
paths, DSNs, usernames, passwords, tokens, or stack traces.

## Operational Requirements

Task PostgreSQL storage targets PostgreSQL 14 or newer. Deployments need a role
that can create the task tables, indexes, triggers, and functions in the target
database or configured schema. No PostgreSQL extensions are required for task
storage. Memory storage has separate extension requirements and a separate
migration history.

Managed providers such as AWS RDS/Aurora, Azure Database for PostgreSQL, Cloud
SQL, Supabase, and Neon can use provider-specific DSN query parameters such as
`sslmode`, `target_session_attrs`, `channel_binding`, and pooler endpoints. The
runtime pool helper preserves DSN query parameters and adds configured
connection timeout and application name values without printing raw DSNs in CLI
diagnostics.

Pool settings should be explicit in production. Configure minimum and maximum
pool size, pool acquisition timeout, connection timeout, statement timeout, lock
timeout, idle transaction timeout, application name, and optional schema/search
path for the worker profile being deployed. Borrowed pools remain owned by the
caller; DSN-created pools are opened and closed by the Avalan PostgreSQL helper.

PostgreSQL failures are reported with low-cardinality categories only:
timeout, connection, serialization, deadlock, unique conflict, insufficient
privilege, migration, or unknown. Diagnostics must not include raw SQL
parameters, prompts, inputs, outputs, stack traces, file bytes, passwords, or
provider tokens.

## Verification

Set `AVALAN_TASK_TEST_POSTGRESQL_DSN` to run the env-gated PostgreSQL
verification tests against an existing test database. These tests create
isolated schemas, run upgrade/current/check/stamp helpers, exercise concurrent
migration runners through the advisory lock, verify artifact byte storage, and
run queued tasks through the PostgreSQL queue and worker.

Run `make test-pgsql` to start a throwaway PostgreSQL container with Docker,
create a temporary test database, export `AVALAN_TASK_TEST_POSTGRESQL_DSN` for
the pytest subprocess, run the full suite including migration and queue e2e
tests, drop the database, and remove the container. Use
`AVALAN_TASK_TEST_POSTGRESQL_DOCKER_IMAGE` to override the Docker image and
`AVALAN_TASK_TEST_POSTGRESQL_DATABASE_PREFIX` to override the generated database
name prefix.

Set `AVALAN_TASK_TEST_POSTGRESQL_ADMIN_DSN` before `make test` to run the same
throwaway database flow against an existing PostgreSQL server. The admin DSN
must point to a maintenance database with permission to create and drop
databases. Set `AVALAN_TASK_TEST_POSTGRESQL_DOCKER=1` before `make test` to use
the Docker-backed flow without calling `make test-pgsql`.

Set `AVALAN_TASK_BENCHMARK_POSTGRESQL_DSN` to run the opt-in EXPLAIN coverage.
The benchmark profile records worker count, run count, queue count, pool size,
PostgreSQL version, and thresholds. The covered paths are run creation,
idempotency reservation, enqueue, claim, heartbeat, event append, inspection
fetch, and retention sweeps. Plan checks are designed to flag missing indexes,
unbounded scans, duplicate-claim risk, and inspection fetch regressions without
executing user workloads.

For event-volume checks, record the expected retained run count, maximum events
per run, retention window, PostgreSQL version, and the maximum unpartitioned
event-row threshold for the schema. A profile that exceeds the threshold must
either enable a partitioned event table revision or keep retention and worker
throughput capped until the forward revision has been applied and checked.

Set `AVALAN_TASK_LOAD_POSTGRESQL_DSN` to run the opt-in queue load profile.
The load profile records worker count, run count, queue count, lease duration,
retry settings, bounded reaper limit, pool size, PostgreSQL version, elapsed
time, and optional minimum claims per second. Override the defaults with
`AVALAN_TASK_LOAD_WORKERS`, `AVALAN_TASK_LOAD_RUNS`,
`AVALAN_TASK_LOAD_QUEUES`, `AVALAN_TASK_LOAD_LEASE_SECONDS`,
`AVALAN_TASK_LOAD_MAX_ATTEMPTS`, `AVALAN_TASK_LOAD_RETRY_DELAY_SECONDS`,
`AVALAN_TASK_LOAD_ABANDON_LIMIT`, `AVALAN_TASK_LOAD_POOL_SIZE`, and
`AVALAN_TASK_LOAD_MIN_CLAIMS_PER_SECOND`.

Queue depth and health are inspected before claims, while all load-profile
runs are claimed, and after expired claims are reaped. Before claims, active
depth should equal the submitted run count and the oldest available timestamp
should be present for non-empty queues. During claims, claimed depth should
equal the submitted run count and expired claim count should remain zero until
the test clock crosses the lease deadline. Reaper calls must stay within the
configured per-call limit, stale claim tokens must not commit, each claimed run
must have a created attempt, and claimed run ids must be unique.

## Existing Databases

Existing embedded task migration metadata and development databases do not
receive an automatic compatibility bridge. Local development databases may be
dropped and recreated. Operators that intentionally preserve an existing schema
must use the migration helper to stamp the appropriate Alembic revision after
verifying the schema manually.
