Skip to content

Database Schema#

Production deployments normally use PostgreSQL; SQLite provides the embedded development, test, and mobile backend. The schema is designed for the scheduling hot path, tenant isolation, and retained execution evidence. PostgreSQL migrations run on startup only when explicitly enabled.

Core tables

sequences

Versioned sequence definitions. Scoped by tenant and namespace.

id (UUID), tenant_id, namespace, name, definition (JSONB), version, created_at

task_instances

One row per task. The scheduling hot path queries this table with FOR UPDATE SKIP LOCKED.

id (UUID), sequence_id, tenant_id, namespace, state, next_fire_at, priority, timezone, metadata (JSONB), context (JSONB), created_at, updated_at

execution_tree

Tracks position within nested blocks. Self-referencing parent_id for tree structure.

id (UUID), instance_id, block_id, parent_id, block_type, branch_index, state, started_at, completed_at

block_outputs

Committed step results. A unique instance/block identity prevents duplicate result rows, but does not prove an external provider observed an effect only once.

id (UUID), instance_id, block_id, output (JSONB), output_ref, output_size, attempt, created_at

rate_limits

Sliding window counters per resource per tenant. Atomic check-and-increment.

id (UUID), tenant_id, resource_key, max_count, window_seconds, current_count, window_start

signal_inbox

Queued signals. Processed before each step execution, marked delivered atomically.

id (UUID), instance_id, signal_type, payload (JSONB), delivered, created_at, delivered_at

Key indexes

JSON
-- Scheduling hot path: find due tasks fast
CREATE INDEX idx_instances_fire
  ON task_instances (next_fire_at)
  WHERE state = 'scheduled';

-- Filter by tenant and state
CREATE INDEX idx_instances_tenant
  ON task_instances (tenant_id, state);

-- Filter by namespace and state
CREATE INDEX idx_instances_namespace
  ON task_instances (namespace, state);

-- Metadata queries (JSONB containment)
CREATE INDEX idx_instances_metadata
  ON task_instances USING GIN (metadata jsonb_path_ops);

-- Execution tree lookups
CREATE INDEX idx_exec_tree_instance
  ON execution_tree (instance_id, state);

CREATE INDEX idx_exec_tree_parent
  ON execution_tree (parent_id)
  WHERE parent_id IS NOT NULL;

-- Block output lookups
CREATE INDEX idx_block_outputs_instance
  ON block_outputs (instance_id, block_id);

-- Pending signals
CREATE INDEX idx_signal_inbox_pending
  ON signal_inbox (instance_id)
  WHERE delivered = FALSE;

-- Rate limit lookups
CREATE INDEX idx_rate_limits_key
  ON rate_limits (tenant_id, resource_key);

Additional tables

sessions

Groups related instances into a session. Lifecycle: Active → Completed / Expired. Cross-instance references via session_id FK on task_instances.

id (UUID), tenant_id, key (unique per tenant), state, metadata (JSONB), created_at, expires_at

audit_log

Append-only event journal. Every state transition recorded via audit_transition hook. Queryable per instance via GET /instances/{id}/audit.

id (UUID), instance_id, tenant_id, event_type, payload (JSONB), created_at

checkpoints

Periodic state snapshots for long-running instances. Pruned automatically, keeping the N most recent per instance.

id (UUID), instance_id, snapshot (JSONB), created_at

externalized_state

Large block outputs stored separately when they exceed ORCH8_EXTERNALIZE_THRESHOLD. Reference markers in block_outputs point here.

id (UUID), instance_id, block_id, data (JSONB), created_at

Migrations

Set ORCH8_RUN_MIGRATIONS=true orrun_migrations = true only in the process that owns schema changes. Version 0.7.0 includes PostgreSQL migrations through 074; the recent continuity and operations additions include:

  1. 061 — unique tenant/current-instance ownership for continuity
  2. 062 — immutable execution location history by ownership epoch
  3. 063 — encrypted, tenant-scoped what-if summaries
  4. 064 — ownership-safe live migration ledger
  5. 065–066 — compensation runs and one-active-run invariant
  6. 067 — provenance chain compare-and-swap
  7. 068 — automatic DLQ incident reproductions
  8. 069 — federation replay receipts
  9. 072 — resumable external-worker checkpoints
  10. 073 — bounded shared agent knowledge
  11. 074 — hash partitions for block_outputs and audit_log

Migration 074 converts existing tables and can require a table rewrite. Reserve a migration window, validate free space and lock impact on a production-sized copy, and keep application nodes from racing the schema owner. It creates 16 hash partitions forblock_outputs byinstance_id and 16 foraudit_log by tenant_id.

Previous← ArchitectureNextGlossary →