Detailed reference of the PostgreSQL database schema and migration system.

Strait uses PostgreSQL 18 as its primary data store. All primary keys are UUIDv7 stored as TEXT. Schema is managed by versioned SQL migrations (currently through the 000089_* series) that run automatically on startup.

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

The webhook_deliveries table (migration 000009, extended in 000061 and 000062) is shared between job run webhooks and event trigger webhooks:

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

Key indexes:

• 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

The circuit breaker state table (migration 000022) tracks per-endpoint failure 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

The endpoint health scoring table (migration 000120) tracks continuous health metrics per endpoint using EWMA:

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

SLO definitions and evaluation history (migration 000122):

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

Important for partitioning considerations:

• 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

We use golang-migrate for schema evolution.

Numbering Convention

Migrations follow a sequential 6-digit numbering scheme: 000001_*.up.sql through the latest migration in the repository.

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.