DB Reference — Events, Severity Config, Deployment Runs

Schemas for the unified events log, write-side severity config and registry, deployment runs, run/item membership, run-level QA, and ephemeral / preview environment tracking. Cross-link back from db-reference.md for entry points, the domain catalog, timestamp discipline, JSON-payload conventions, qa CLI, body write path, and the status lifecycle reference.

Table: events

Cross-stack structured event log. Unified envelope for agent tool calls, session lifecycle, backend telemetry, frontend analytics, and system events. All source types share the same schema, enabling cross-source queries on a single table.

Compatibility note: first-class tool-call correlation columns are optional across local installs and emitters. Readers and emitters must tolerate their absence until all live event tables expose the full correlation surface.

id INTEGER PRIMARY KEY
event_id TEXT UNIQUE NOT NULL -- UUID, deduplication key (ON CONFLICT DO NOTHING)
source_type TEXT NOT NULL -- 'agent' | 'backend' | 'frontend' | 'system' | 'script' | 'hook' | 'skill'
session_id TEXT NOT NULL -- session/request correlation ID
severity TEXT NOT NULL DEFAULT 'INFO' -- DEBUG | INFO | WARN | ERROR | FATAL
event_kind TEXT NOT NULL -- taxonomy tier 1 (e.g., 'system', 'domain', 'user')
event_type TEXT NOT NULL -- taxonomy tier 2 (e.g., 'tool_call', 'session')
event_name TEXT NOT NULL -- PascalCase event name (e.g., 'HarnessToolCallCompleted')
event_outcome TEXT -- nullable outcome (e.g., 'completed', 'failed')
org_id TEXT -- organization identifier
actor_id INTEGER -- nullable Yoke control-plane subject; references actors(id)
environment TEXT -- runtime environment (e.g., 'dev', 'prod')
service TEXT NOT NULL DEFAULT 'cli' -- emitting service
project_id INTEGER NOT NULL DEFAULT 1 -- project context; references projects(id)
item_id TEXT -- backlog item (e.g., 'PREFIX-N')
task_num INTEGER -- epic task number
agent TEXT -- agent role (e.g., 'engineer', 'tester')
tool_name TEXT -- tool that was called (e.g., 'Bash', 'Read')
duration_ms INTEGER -- execution duration in milliseconds
exit_code INTEGER -- process exit code
trace_id TEXT -- distributed trace ID
anomaly_flags TEXT -- → JSONB on Postgres (array shape); today a comma-separated anomaly-flag list per `docs/event-contract.md` (e.g., 'nonzero_exit,retry_loop')
tool_use_id TEXT -- target first-class dedup key for tool-call events; current live coverage is still incomplete
client_timing_id TEXT -- key a hook client minted for its own dispatch, so the wall time it reports afterwards finds this row by index; cleared once that report lands
envelope TEXT -- → JSONB on Postgres; full JSON envelope for lossless storage
created_at TEXT NOT NULL -- app-supplied ISO-8601 UTC; see "Timestamp discipline" below

Indexes: source_type, session_id, event_name, created_at, actor_id, trace_id, project_id, tool_name, (event_kind, event_type), plus the partial dedup index (tool_use_id, event_name) WHERE tool_use_id IS NOT NULL and the partial correlation index (client_timing_id) WHERE client_timing_id IS NOT NULL on DBs with the full correlation surface. Correlate through an indexed column; matching a substring of envelope with LIKE has no index to use and reads every row the other predicates admit.

Engine identity is actor-only. Historical JSON envelopes may retain a null human-user key, but fresh schemas, writers, readers, and filters do not expose that retired surface.

Deduplication: The event_id column has a UNIQUE constraint. Inserts use ON CONFLICT DO NOTHING so duplicate event IDs are silently dropped.

Write-side severity filtering: Before inserting, events are checked against the severity_config table. Events below the configured minimum severity for their (event_name, source_type) pair are silently dropped without error.

Retention (prune): DEBUG=1d, INFO=30d, WARN=90d, ERROR/FATAL=forever. Run python3 -m yoke_core.cli.db_router events prune periodically. DEBUG is the on-demand-capture tier (dropped at the default INFO write floor; enable by lowering severity_config to DEBUG), so it carries the shortest retention.

Table: severity_config

Write-side severity filtering configuration for the events table. Controls which severity levels are persisted per event name and source type combination.

id INTEGER PRIMARY KEY
event_name TEXT NOT NULL DEFAULT '*' -- event name pattern ('*' = wildcard)
source_type TEXT NOT NULL DEFAULT '*' -- source type pattern ('*' = wildcard)
min_severity TEXT NOT NULL DEFAULT 'INFO' -- DEBUG | INFO | WARN | ERROR | FATAL
created_at TEXT NOT NULL -- app-supplied ISO-8601 UTC; see "Timestamp discipline" below
UNIQUE(event_name, source_type)

Lookup order: exact (event_name, source_type) > (event_name, ) > (, source_type) > (, ). First match wins. Seeded with catch-all (, , INFO) on init.

Table: event_registry

Central registry of all known event types. Governs event naming, ownership, and lifecycle. Used by yoke_core.domain.observe (lint-event-registry guardrail) (PreToolUse hook) and doctor health checks to enforce event governance.

event_name TEXT PRIMARY KEY -- PascalCase event name (e.g., 'HarnessToolCallCompleted')
event_kind TEXT NOT NULL -- taxonomy tier 1 (e.g., 'system', 'domain')
event_type TEXT NOT NULL -- taxonomy tier 2 (e.g., 'tool_call', 'session')
owner_service TEXT NOT NULL -- emitting service (e.g., 'yoke_core.domain.observe')
description TEXT NOT NULL -- human-readable description
context_schema TEXT -- optional JSON schema for context payload
severity_default TEXT NOT NULL DEFAULT 'INFO' -- default severity level
added_in TEXT -- PREFIX-N or version when added
status TEXT NOT NULL DEFAULT 'active' -- 'active' | 'deprecated'

Idempotent writes: registry add uses ON CONFLICT DO NOTHING semantics — duplicate event_name inserts silently succeed (exit 0, no row change). This makes python3 -m yoke_core.domain.populate_registry safe to re-run.

Lifecycle: Events start as active. Use registry deprecate <name> to mark events no longer emitted. The registry audit subcommand reports stale active entries (registered but not emitted in 30 days) and rogue events (emitted but not registered).

Table: deployment_runs

One row per pipeline execution. Stage authority lives on the run, not on individual items. A run may be item-bound through deployment_run_items, or item-less for environment-level deploys such as Yoke prod/stage redeploys.

id TEXT PRIMARY KEY -- human-readable slug (e.g., 'run-20260315-001')
project_id INTEGER NOT NULL REFERENCES projects(id)
flow TEXT NOT NULL REFERENCES deployment_flows(id)
target_tier TEXT -- persistent | ephemeral | NULL (merge-only)
target_environment_id INTEGER -- internal REFERENCES environments(id); required exactly when target_tier='persistent'
release_lineage TEXT -- links preview->prod runs (shared lineage ID)
status TEXT NOT NULL DEFAULT 'created' -- created|executing|succeeded|failed|cancelled
current_stage TEXT -- stage authority lives here, not on items
created_at TEXT NOT NULL -- app-supplied ISO-8601 UTC; see "Timestamp discipline" below
started_at TEXT -- when execution actually began
completed_at TEXT
created_by TEXT -- 'operator' or 'system'
carried_work TEXT -- → JSONB on Postgres; resolved items and unresolved commit SHAs

A run copies the internal target_tier and target_environment_id from its flow. Operators select or override a persistent target only with --environment <registered-name>; numeric keys are never accepted or emitted by the operator surface. Setting status=succeeded stamps environments.last_deployed_at on the referenced row.

Successful completion also compares this run's immutable release_lineage with the previous succeeded run for the same project and target environment. The resulting carried_work object keeps item matches under items and unresolved first-parent commits as bare SHAs under commits. Its derivation.reason names explicit empty cases such as no prior run or an unreachable lineage. This record never enrolls those items in deployment_run_items and never changes their lifecycle state. Historical runs remain unset; recording is forward-only.

Table: deployment_run_items

Membership table linking items to deployment runs. Zero rows for a run are valid when the run is an environment-level deploy with no attached backlog item; do not infer failure from item-less membership after the run has started executing.

run_id TEXT NOT NULL REFERENCES deployment_runs(id)
item_id INTEGER NOT NULL -- backlog item numeric ID
added_at TEXT NOT NULL -- app-supplied ISO-8601 UTC; see "Timestamp discipline" below
PRIMARY KEY (run_id, item_id)

Item-bound delivery starts from /yoke usher PREFIX-N or yoke deployment-runs start-for-item, which creates the run and inserts membership rows.

The deploy lock gates create and execute

Creating a run (deployment_runs.create, deployment_runs.start_for_item) and executing one (deployment-runs execute, and the item form of the same pipeline) each refuse unless the calling session holds the project's deploy lock — the deploy_serialization coordination claim addressed by DEPLOY:<project-slug>. One driver owns a project's deployments, so a stage promotion cannot overtake the production promotion it precedes. Take it before the pair and release it after:

yoke claims coordination-claim acquire --project P --key DEPLOY:P --reason "driving the release pair"
yoke claims coordination-claim release --project P --key DEPLOY:P --reason "release pair complete"

It is per project rather than per environment because a release pair deploys stage and production from one pinned source; both halves run under one hold, so no second driver slips between them. The steering seat takes it when it starts driving a pair and releases it when the pair completes. Refusals name the current holder, the acquire recipe, and the release recipe.

The kind is sticky: nothing reclaims the lock automatically, because a pipeline whose local driver died is still running on CI. After confirming the pipeline has settled, list the live claim and review its holder, then use the signed-in human action outside any harness session to free the exact stranded row with yoke coordination-claim release --project P --key DEPLOY:P --claim-id N --holder-session-id S --reason "...". Manual and launched agent sessions are refused. This registered action works over HTTPS or local authority, requires project claims.release permission, refuses if the claim or holder changed after review, and records a WARN OperatorLeaseRelease plus the durable reason on the claim row.

Exclusivity is on project_id alone. The slug rides in the claim scope so the operator key renders without a database read, and renaming a project cannot hand out a second live lock.

A claim row is session-bound by foreign key, so there is no session-less deploy lock: a caller that resolves no harness session is refused and told the same recovery every claim-holding operation names — run it from a harness session, or, for an operator driving the deploy from a plain terminal, register one (yoke sessions begin --help) and point $YOKE_SESSION_ID at it so the acquire and the run share one holder.

Claim storage, stickiness, and the board rendering for every coordination kind: the "Shared-operation coordination claims" section of qa-and-sessions.md.

Table: deployment_run_qa

Run-level QA requirements materialized at run creation.

run_id TEXT NOT NULL REFERENCES deployment_runs(id)
check_name TEXT NOT NULL -- e.g., 'smoke-test', 'manual-acceptance'
source TEXT NOT NULL -- 'flow-default', 'item-rollup', 'operator'
blocking INTEGER NOT NULL DEFAULT 1 -- 1 = blocks done transition
status TEXT NOT NULL DEFAULT 'pending' -- pending|passed|failed|waived
updated_at TEXT
PRIMARY KEY (run_id, check_name)

Blocking QA holds the succeeded stamp

A run does not reach status='succeeded' while any blocking QA obligation is unresolved. deployment_runs.cmd_update is the boundary that enforces it, so every route into a succeeded stamp — including the pipeline's own finalization — is covered by one check.

Two tables carry those obligations, and both are read:

• deployment_run_qa, the flow-derived checks. Only passed and

waived resolve one. failed does not: a failed blocking check is the strongest reason not to call the run succeeded.

• qa_requirements rows keyed by deployment_run_id, the run's plan

cases. One resolves on a qa_runs row with verdict='pass', or on a waiver. A case whose latest run is undetermined and awaits human evidence review is named with that pending review and its authorities.

Non-blocking checks never hold a run, and force=True overrides the hold exactly as it overrides the stage checks beside it.

The pipeline reports this before it tries the write, so the operator reads the specific unresolved obligations rather than a refusal from the status update. It stamps current_stage='complete', prints each obligation, and exits 5 without emitting DeploymentRunSucceeded or a completed_at. The stages already executed keep their recorded results. Settle or waive each obligation and re-drive the run: the pipeline resumes at complete, skips every stage, and finalizes.

Owner: yoke_core.domain.deployment_run_completion_preconditions.

Table: deployment_preview_environments

Preview environment occupancy tracking for deployment runs.

id INTEGER PRIMARY KEY
project TEXT NOT NULL REFERENCES projects(id)
env_name TEXT NOT NULL -- e.g., 'stage', 'sandbox'
run_id TEXT REFERENCES deployment_runs(id)
status TEXT NOT NULL DEFAULT 'available' -- available | claimed | stale
url TEXT
created_at TEXT NOT NULL -- app-supplied ISO-8601 UTC; see "Timestamp discipline" below
env_type TEXT NOT NULL DEFAULT 'adhoc' -- shared | adhoc
UNIQUE(project, env_name)

Table: ephemeral_environments

Tracks per-branch ephemeral environments for pre-merge E2E validation. GitHub Actions creates environments; Yoke tracks lifecycle via python3 -m yoke_core.cli.db_router envs.

id INTEGER PRIMARY KEY
project TEXT NOT NULL REFERENCES projects(id)
branch TEXT NOT NULL -- MUST use 'PREFIX-{id}' format (see Branch Naming Contract below)
item TEXT -- backlog item (e.g., 'PREFIX-N')
workflow_run_id TEXT -- GitHub Actions run ID that created this environment
github_ref TEXT -- git ref used for the environment
port_api INTEGER
port_web INTEGER
url TEXT
status TEXT NOT NULL DEFAULT 'pending' -- pending | starting | running | healthy | stopped | failed
started_at TEXT
stopped_at TEXT
health_check_url TEXT
deployed_sha TEXT -- commit SHA of last pushed code (enables push/poll short-circuit)
created_at TEXT
UNIQUE(project, branch)

Branch Naming Contract

The branch column MUST use the value PREFIX-{id} (matching the item's worktree branch name, e.g., PREFIX-N). This convention is required for conduct compatibility -- the conduct skill queries ephemeral environments by branch='PREFIX-{id}' at steps d2, E1, and E3 in dispatch-context.md. CI systems that write ephemeral environment records (e.g., external-webapp-ephemeral.yml) must use the same PREFIX-{id} branch value. If a future project uses a different branch naming scheme, both the CI workflow and the conduct skill query must be updated in lockstep.