Database Schema

​

Core Tables

​

Workflow Tables

​

Core Engine Tables

​

Key Columns

​

Relationships

• Jobs & Runs: A job has many job_runs. Each run is linked to a specific job_version.
• Workflows: A workflow has many workflow_versions, and each version has many workflow_version_steps used to materialize workflow_step_runs at trigger time.
• Workflow Governance: workflow_policies is one row per project and is enforced at workflow create/update/trigger boundaries.
• Workflow Explainability: workflow_step_decisions stores progression decisions per run and powers /v1/workflow-runs/{id}/explain.
• Environments: jobs and job_secrets are linked to environments for configuration isolation.

​

Event Trigger Tables

​

event_triggers

id                    TEXT PRIMARY KEY              -- UUIDv7
event_key             TEXT NOT NULL UNIQUE           -- Globally unique caller-controlled key
project_id            TEXT NOT NULL
source_type           TEXT NOT NULL                  -- 'workflow_step' or 'job_run'
trigger_type          TEXT NOT NULL DEFAULT 'event'  -- 'event' or 'sleep'
workflow_run_id       TEXT                           -- FK to workflow_runs
workflow_step_run_id  TEXT                           -- FK to workflow_step_runs
job_run_id            TEXT                           -- FK to job_runs
status                TEXT NOT NULL DEFAULT 'waiting' -- waiting, received, timed_out, canceled
timeout_secs          INT NOT NULL DEFAULT 3600
request_payload       JSONB
response_payload      JSONB
requested_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
received_at           TIMESTAMPTZ
expires_at            TIMESTAMPTZ
error                 TEXT
notify_url            TEXT
notify_status         TEXT
event_emit_key        TEXT
sent_by               TEXT
created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()

​

Key Indexes

• idx_event_triggers_status_expires: (status, expires_at) WHERE status = 'waiting' — Reaper timeout lookup
• idx_event_triggers_project_status: (project_id, status) — List queries
• idx_event_triggers_event_key_prefix: (event_key text_pattern_ops) — Prefix matching for batch resolution
• idx_event_triggers_step_run: (workflow_step_run_id) WHERE workflow_step_run_id IS NOT NULL — Step-to-trigger lookup

​

Performance Indexes

• idx_workflow_runs_status_expires: (status, expires_at) WHERE expires_at IS NOT NULL — Reaper timeout lookup
• idx_workflow_runs_status_finished: (status, finished_at) WHERE finished_at IS NOT NULL — Retention deletion
• idx_step_runs_workflow_run_status: (workflow_run_id, status) — Step run queries by run and status
• idx_step_decisions_step_run_id: (step_run_id) — CASCADE deletes on decisions
• idx_job_runs_active_by_job: (job_id) WHERE status IN ('queued', 'dequeued', 'executing') — Active run counting

​

webhook_deliveries Extensions

id                TEXT PRIMARY KEY
run_id            TEXT                -- FK to job_runs (nullable)
job_id            TEXT                -- FK to jobs (nullable)
event_trigger_id  TEXT                -- FK to event_triggers (nullable)
workflow_run_id   TEXT                -- FK to workflow_runs (nullable)
subscription_id   TEXT                -- FK to webhook_subscriptions (nullable)
webhook_url       TEXT NOT NULL
status            TEXT NOT NULL DEFAULT 'pending'  -- pending, delivering, delivered, failed, dead
attempts          INT NOT NULL DEFAULT 0
max_attempts      INT NOT NULL DEFAULT 5
last_status_code  INT
last_error        TEXT
next_retry_at     TIMESTAMPTZ
delivered_at      TIMESTAMPTZ
payload           JSONB
event_type        TEXT
sequence          INT NOT NULL DEFAULT 0
created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()

• idx_webhook_deliveries_pending: (next_retry_at ASC) WHERE status = 'pending' — Delivery worker queue
• idx_webhook_deliveries_run_seq: (run_id, sequence) WHERE run_id IS NOT NULL — Ordered delivery per run
• idx_webhook_deliveries_event_trigger: (event_trigger_id) WHERE event_trigger_id IS NOT NULL
• idx_webhook_deliveries_status_created: (status, created_at)

​

endpoint_circuit_state

endpoint_url    TEXT PRIMARY KEY
state           TEXT NOT NULL DEFAULT 'closed'  -- closed, open, half_open
failures        INT NOT NULL DEFAULT 0
opened_at       TIMESTAMPTZ
last_failure_at TIMESTAMPTZ
updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()

​

endpoint_health_scores

endpoint_url        TEXT PRIMARY KEY
health_score        DOUBLE PRECISION NOT NULL DEFAULT 100.0
success_rate        DOUBLE PRECISION NOT NULL DEFAULT 1.0
timeout_rate        DOUBLE PRECISION NOT NULL DEFAULT 0.0
latency_score       DOUBLE PRECISION NOT NULL DEFAULT 1.0
total_requests      BIGINT NOT NULL DEFAULT 0
last_latency_ms     DOUBLE PRECISION NOT NULL DEFAULT 0.0
updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()

​

job_slos and job_slo_evaluations

-- job_slos
id              TEXT PRIMARY KEY
job_id          TEXT NOT NULL REFERENCES jobs(id) ON DELETE CASCADE
project_id      TEXT NOT NULL
metric          TEXT NOT NULL  -- success_rate, p95_latency_secs, p99_latency_secs
target          DOUBLE PRECISION NOT NULL
window_hours    INT NOT NULL   -- 24, 168, 720
UNIQUE(job_id, metric, window_hours)

-- job_slo_evaluations
id              TEXT PRIMARY KEY
slo_id          TEXT NOT NULL REFERENCES job_slos(id) ON DELETE CASCADE
current_value   DOUBLE PRECISION NOT NULL
budget_remaining DOUBLE PRECISION NOT NULL
evaluated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()

​

job_runs Key Indexes

• idx_runs_queue_covering: (created_at ASC) INCLUDE (job_id, priority, scheduled_at, next_retry_at) WHERE status = 'queued' — Covering index for dequeue
• idx_runs_status_project_covering: (project_id, status, created_at DESC) INCLUDE (job_id, priority, scheduled_at, next_retry_at) — Covering index for list queries
• idx_runs_heartbeat: (heartbeat_at) WHERE status = 'executing' — Stale run detection
• idx_runs_expires: (expires_at) WHERE expires_at IS NOT NULL AND status IN ('delayed', 'queued') — Expiration reaper
• idx_job_runs_parent_run_id: (parent_run_id) WHERE parent_run_id IS NOT NULL — Parent-child lookup
• idx_runs_metadata: GIN index on metadata (migration 000016) — JSONB filtering
• idx_jobs_tags: GIN index on tags (migration 000054) — Tag-based filtering

​

FK Constraints on job_runs

• job_runs.parent_run_id — Self-referential FK dropped in migration 000066 to support range partitioning. Integrity is enforced at the application layer.
• run_events.run_id — No ON DELETE clause
• run_checkpoints.run_id — ON DELETE CASCADE
• run_usage.run_id — ON DELETE CASCADE
• run_tool_calls.run_id — ON DELETE CASCADE
• run_outputs.run_id — ON DELETE CASCADE

​

Miscellaneous Indexes

• idx_idempotency_expires: (expires_at) WHERE expires_at IS NOT NULL on job_run_idempotency
• idx_audit_events_resource_lookup: (resource_type, resource_id) on audit_events
• idx_webhook_subscriptions_project_active: (project_id, created_at DESC) WHERE active = TRUE on webhook_subscriptions

​

Migrations

​

Numbering Convention

​

Recent Migrations

​

Adding New Migrations

1. Create a new pair of SQL files in the apps/strait/migrations/ directory using the next sequence number:
   • 000090_your_feature.up.sql
   • 000090_your_feature.down.sql
2. Ensure the migration is idempotent.
3. The application will automatically detect and apply the new migration on the next startup.