Database Schema

All state lives in PostgreSQL. The schema is designed for the scheduling hot path — fast lookups for due tasks and efficient bulk operations. Migrations run automatically on startup when 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

Step results. UNIQUE on (instance_id, block_id) enforces exactly-once. Upsert on conflict.

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

-- 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

Migrations run automatically on startup (run_migrations = true by default). 18 migration files applied in order:

sequences — workflow definitions
task_instances — instance state and scheduling
block_outputs — memoized step results
rate_limits — per-resource sliding window counters
signal_inbox — queued signals for running instances
cron_schedules — recurring workflow triggers
execution_tree — composite block execution nodes
worker_tasks — external worker task queue
indexes — performance indexes for scheduling hot path
resource_pools — multi-resource distribution tables
externalized_state — large output storage
concurrency_keys — per-key concurrency tracking
checkpoints — periodic state snapshots
audit_log — append-only event journal
versioning — sequence version and deprecation fields
sessions — cross-instance session management
queue_name — queue column on worker_tasks
encryption — field-level encryption metadata