Data model
All persistent state lives in one Postgres database whose schema is packages/db/src/schema.ts (Drizzle). Temporal
holds workflow histories, but its payloads carry only ids, digests and small enums; Postgres decides. There are
17 tables in four groups: ledger and control, sessions, software factory, and CodeCrucible.
Conventions that apply to every table (from schema.ts and CODING_STANDARDS.md section 11):
- Money is
numeric(18,8)(theusd()column helper). JS numbers are rounded to 8 decimal places at the store boundary, sums happen in SQL, values are read back withNumber(text). - Timestamps are
timestamp with time zone. The UTC day uses theday(n)helper instores.ts, based ontransaction_timestamp()unless a test injects a time. - Enumerated text columns are guarded by
CHECK ... IN (...)constraints generated from the Zod enums in@agentforge/agent-types(sqlList), so the database rejects an unknown status even if a write path is wrong. - Digests are checked by regex:
^[0-9a-f]{64}$(sha256,hex64),^[0-9a-f]{40}$(git sha,sha40). - Store SQL is raw parameterized
pg; Drizzle is used for schema and migrations only.
Ledger, control and gateway tables
There are no foreign keys between these tables; usage.request_id and gateway_audit.request_id are correlated by
value only.
usage
The single source of truth for spend. A reservation is a row with status = 'reserved'; settling replaces the status
with a RuntimeStatus. Spend per UTC day is SUM(COALESCE(cost_usd, amount_usd)), so an unsettled row counts in full.
| Constraint or index | Rule |
|---|---|
request_id unique | One ledger row per request id. Session turns use session-<sessionId>/<messageId>#<n>, factory steps factory-<jobId>/<step_key>#<attempts>, synchronous API runs api/<uuid> |
usage_day_client_idx | (day, client) |
usage_job_id_idx | (job_id) WHERE job_id IS NOT NULL |
usage_request_id_prefix | request_id text_pattern_ops, serves LIKE 'session-<id>/%' prefix scans (getSession, abandonPrefix) |
usage_amount_usd_nonneg, usage_cost_usd_nonneg, usage_debited_usd_nonneg | money is never negative |
usage_tokens_nonneg | all four token counters >= 0 |
usage_status_valid | status in the 13 UsageStatus values: success, aborted, agent_limit, timeout, agent_failed, disabled, budget_exhausted, internal_error, invalid_request, unsupported_capability, reserved, rejected_budget, rejected_disabled |
debited_usd and metered (added in 0003) belong to the gateway: once the gateway debited a row it owns cost_usd
("one cost authority").
settings
Key-value store. The only key read by code is agents_disabled: '1' or 'true' (case-insensitive) disables agents,
anything else or no row enables (PgSettingsStore.isDisabled). No application code writes this table; it is an
operator kill switch set with SQL.
alerts_sent
Primary key (day, threshold). PgAlertStore.markSent inserts a row the first time a spend threshold is crossed on a
day, so each alert fires once per day.
gateway_audit
Evidence of gateway decisions; it stores no prompts, bodies, tokens, headers or tool arguments (only args_sha256).
| Constraint | Rule |
|---|---|
gateway_audit_kind_valid | kind in model, tool, context, control |
gateway_audit_decision_valid | decision in allow, deny |
gateway_audit_identity_valid | identity in obo, service, none |
gateway_audit_reason_valid | an allow of kind model or tool has reason IS NULL; every other row has a non-null reason in AuditReason |
gateway_audit_tokens_nonneg, gateway_audit_cost_usd_nonneg, gateway_audit_latency_ms_nonneg | null or >= 0 |
gateway_audit_target_len | target IS NULL OR length(target) <= 256 (added by 0004) |
Session tables
| Constraint or index | Rule |
|---|---|
sessions_status_valid | status IN ('open','closed','cancelled','failed') |
sessions_client_created_at_idx | (client, created_at) |
messages_session_id_seq_unique | (session_id, seq) unique |
messages_one_open_turn | unique (session_id) WHERE status IN ('pending','running'): at most one open turn per session |
messages_status_valid | status IN ('pending','running','completed','failed','aborted') |
messages_session_id_sessions_id_fk | messages.session_id references sessions.id |
messages.error_code stores the raw code; the API maps it with toResponse(code, 'none', now) before returning it (see
Error model). Lifecycle: Sessions and workflows.
Software factory tables
factory_jobs constraints
| Constraint | Rule |
|---|---|
factory_jobs_client_idempotency_key_unique | (client, idempotency_key); a replay with another body (request_sha256 differs) is a conflict |
factory_jobs_status_valid | status in FactoryStatus |
factory_jobs_error_code_valid | failed requires a non-null error_code in FactoryErrorCode; every other status requires error_code IS NULL |
factory_jobs_base_sha_hex, factory_jobs_request_sha256_hex | hex regex |
factory_jobs_budget_usd_range | budget_usd > 0 AND budget_usd <= 2 (FACTORY_LIMITS.maxJobUsdCap) |
factory_jobs_max_iterations_range | BETWEEN 1 AND 5 (FACTORY_LIMITS.maxIterations) |
factory_jobs_idempotency_key_len | length BETWEEN 1 AND 128 |
factory_jobs_task_len | length BETWEEN 1 AND 16384 (maxTaskChars = 16 * 1024) |
factory_jobs_pending_gate_valid | the four pending columns are all null, or all set with pending_gate in FactoryGate, a hex digest and status IN ('awaiting_plan_approval','awaiting_pr_approval') |
factory_jobs_closed_iff_terminal | closed_at IS NULL exactly when status NOT IN ('pr_opened','failed','cancelled') |
factory_jobs_client_created_at_idx | (client, created_at) |
Other factory constraints
| Table | Constraint | Rule |
|---|---|---|
factory_steps | factory_steps_job_id_step_key_unique | (job_id, step_key) |
factory_steps | factory_steps_step_key_valid | step_key ~ '^(plan|(code|test|review)-[1-5])$' |
factory_steps | factory_steps_attempts_range | BETWEEN 0 AND 3 (maxStepAttempts) |
factory_steps | factory_steps_outcome_size | octet_length(outcome::text) <= 4096 |
factory_artifacts | factory_artifacts_size_valid | size = octet_length(bytes) AND size <= 1048576 (maxArtifactBytes) |
factory_artifacts | factory_artifacts_iteration_range | BETWEEN 0 AND 5 |
factory_artifacts | factory_artifacts_kind_valid, factory_artifacts_sha256_hex | enum, hex |
factory_approvals | factory_approvals_one_consumed | unique (job_id, gate) WHERE consumed_at IS NOT NULL: an approval is consumed at most once per gate |
factory_approvals | factory_approvals_gate_valid, factory_approvals_decision_valid, factory_approvals_digest_hex | gate in FactoryGate; decision IN ('approve','reject'); hex |
factory_events | factory_events_kind_valid, factory_events_detail_size | kind ~ '^[a-z][a-z0-9_.]{0,63}$'; detail at most 4096 bytes |
Artifacts store plan, patch, test-log and review bytes only; the base tree is streamed with git archive and never
stored. Events record token mints, pushes and PRs and never contain tokens, task or patch text. Detail on the job
lifecycle: Job lifecycle and Gates and approvals.
CodeCrucible tables
quality_chunk_cache and quality_submissions have no foreign keys. A submission's id becomes the quality_runs.id
when the run is admitted, so the two are linked by value.
quality_runs constraints
| Constraint | Rule |
|---|---|
quality_runs_status_valid | status in running, completed, partial, inconclusive, error, cancelled |
quality_runs_intent_valid | intent in remediation, feature |
quality_runs_verdict_valid | completed requires a verdict that is failed or 'passed:' || intent; every other status requires verdict IS NULL |
quality_runs_error_code_valid | error requires error_code ~ '^[a-z][a-z0-9_]{0,63}$' (for example exec_lost); every other status requires null |
quality_runs_base_sha_hex, quality_runs_head_sha_hex, quality_runs_config_sha256_hex | hex regex. An admitted run keeps base_sha = '0' * 40 (PENDING_BASE_SHA) until setRunRefs records the merge base |
quality_runs_image_digest_valid | null or ^sha256:[0-9a-f]{64}$ |
quality_runs_repo_len, quality_runs_package_len, quality_runs_protected_branch_len | 1 to 140, 1 to 214, null or 1 to 255 characters |
quality_runs_findings_size | octet_length(findings::text) <= 65536 (maxFindingsBytes = 64 * 1024) |
quality_runs_counters_nonneg | sandbox_ms, chunks_total, chunks_timeout, cache_hits >= 0 |
quality_runs_closed_iff_terminal | closed_at IS NULL exactly when status = 'running' |
| indexes | quality_runs_repo_package_created_at_idx (repo, package, created_at), quality_runs_client_created_at_idx (client, created_at) |
Other CodeCrucible constraints
| Table | Constraint | Rule |
|---|---|---|
quality_functions | quality_functions_run_id_file_qualified_name_unique | (run_id, file, qualified_name) |
quality_functions | quality_functions_coverage_range | coverage BETWEEN 0 AND 1 |
quality_functions | quality_functions_counts_valid | counters >= 0 and killed + survived + no_coverage + timeout <= mutants_total |
quality_functions | quality_functions_file_len, quality_functions_qualified_name_len | 1 to 512 characters |
quality_mutants | quality_mutants_run_id_stable_id_unique | (run_id, stable_id); stable_id is the sha256 of (file, qualified name, mutator, original, replacement, ordinal) |
quality_mutants | quality_mutants_status_valid | killed, survived, no_coverage, timeout, compile_error, runtime_error, ignored, pending |
quality_mutants | quality_mutants_rekill_valid | null, confirmed or not_reproduced |
quality_mutants | quality_mutants_snippet_len | original and replacement at most 80 characters (maxSnippetChars) |
quality_mutants | quality_mutants_killed_by_valid | killed_by is a JSON array of at most 4096 bytes |
quality_baselines | quality_baselines_function_unique | (repo, package, branch, file, qualified_name) |
quality_baselines | quality_baselines_mutation_score_range, quality_baselines_crap_nonneg | 0 to 100; >= 0 |
quality_chunk_cache | quality_chunk_cache_report_size | octet_length(report) <= 131072 (maxChunkReportBytes = 128 * 1024) |
quality_chunk_cache | quality_chunk_cache_last_hit_at_idx | (last_hit_at); rows beyond 2000 (maxChunkCacheRows) are evicted least-recently-hit first; rows not hit for 30 days (chunkCacheTtlDays) are deleted |
quality_submissions | quality_submissions_intent_valid, quality_submissions_head_sha_hex, _repo_len, _package_len | as for quality_runs |
quality_submissions | quality_submissions_client_created_at_idx | (client, created_at) |
Baselines are written only from runs on protected-branch commits; writeBaseline writes nothing unless the run is open
and flagged with protected_branch. See Quality data model for how the workflows use these rows.
Who writes what
| Table | API | Session worker | Factory worker | Gateway | Notes |
|---|---|---|---|---|---|
usage | admit and settle synchronous runs | prepareTurn admits, finishTurn settles, abandon on retry | step rows, abandonPrefix('factory-<jobId>/') on close | debit (debited_usd, metered, cost_usd) | via createPgStores / PgLedgerStore |
settings | read | read | read | read per request | written only by an operator |
alerts_sent | insert | insert | insert | none | PgAlertStore.markSent |
gateway_audit | none | none | none | insert | PgAuditStore.write |
sessions, messages | createSession, addMessage, requestCancel, markSessionFailed, failMessage | claimTurn, finishTurn, failTurn, closeSession | none | none | PgSessionStore |
factory_jobs | createJob, requestCancel, consumeApproval (clears pending columns) | none | setStatus, openGate, expireGate, setPr, closeJob | none | PgFactoryStore |
factory_steps, factory_artifacts, factory_events | read | none | claimStep, finishStep, setWorkspaceIn, putArtifact, recordEvent | none | |
factory_approvals | insert (consumed in the same transaction) | none | readDecision (read only) | none | |
quality_submissions | submit, releaseSubmission | none | none | none | swept by the next submit |
quality_runs, quality_functions, quality_mutants, quality_baselines, quality_chunk_cache | read (getRun, listSurvivors) | quality core activities: createRun, setRunRefs, putFunctions, putMutants, writeBaseline, putChunk, sweepChunkCache, closeRun | none | none | quality exec worker has no database access |
The exec worker and quality exec worker hold no DATABASE_URL (their configs refuse it).
Migrations
Migrations are SQL files generated by drizzle-kit into packages/db/drizzle/, tracked in drizzle/meta/_journal.json.
migrate(pool) applies them idempotently (applied ones are tracked in drizzle.__drizzle_migrations). Apps never
migrate at boot; apps/api ships dist/migrate.js, which needs only DATABASE_URL.
| # | File | Change |
|---|---|---|
| 0 | 0000_init.sql | usage, settings, alerts_sent; index usage_day_client_idx |
| 1 | 0001_usage_checks.sql | CHECKs usage_amount_usd_nonneg, usage_cost_usd_nonneg, usage_tokens_nonneg, usage_status_valid |
| 2 | 0002_sessions.sql | sessions, messages, FK messages_session_id_sessions_id_fk, messages_one_open_turn, sessions_client_created_at_idx, usage_request_id_prefix |
| 3 | 0003_gateway.sql | gateway_audit; usage.debited_usd, usage.metered, usage_job_id_idx, usage_debited_usd_nonneg |
| 4 | 0004_audit_target_len.sql | CHECK gateway_audit_target_len |
| 5 | 0005_factory.sql | factory_jobs, factory_steps, factory_artifacts, factory_approvals, factory_events, their FKs to factory_jobs, factory_approvals_one_consumed, three indexes |
| 6 | 0006_quality.sql | quality_runs (self FK on parent_id), quality_functions, quality_mutants, quality_baselines, quality_chunk_cache, their FKs and indexes |
| 7 | 0007_quality_submissions.sql | quality_submissions and quality_submissions_client_created_at_idx |
Adding a migration (CODING_STANDARDS.md section 11): edit schema.ts, run
pnpm --filter @agentforge/db db:generate, commit the SQL file with drizzle/meta/*, and update
packages/db/test/migrate.test.ts, which pins the applied-migration count and the constraint names. Migrations stay
additive. Backups are covered in Persistence and backups.
Sources
packages/db/src/schema.ts(every table,usd,sqlList,hex64,sha40,len,nonneg)packages/db/drizzle/0000_init.sqltopackages/db/drizzle/0007_quality_submissions.sql,packages/db/drizzle/meta/_journal.jsonpackages/db/src/migrate.ts(migrate),packages/db/src/index.tspackages/db/src/stores.ts(PgLedgerStore,PgSettingsStore.isDisabled,PgAlertStore.markSent,PgAuditStore.write,createPgStores)packages/db/src/sessions.ts,packages/db/src/factory.ts(createJob,claimStep,consumeApproval,closeJob),packages/db/src/quality.ts(PENDING_BASE_SHA,createRun,submit,writeBaseline,putChunk)packages/agent-types/src/index.ts(UsageStatus),packages/agent-types/src/audit.ts,packages/agent-types/src/factory.ts(FACTORY_LIMITS,FactoryErrorCode),packages/agent-types/src/quality.ts(QUALITY_LIMITS, enums)apps/api/src/app.ts,apps/gateway/src/server.ts,apps/worker/src/main.ts,apps/worker/src/worker.ts,apps/worker/src/factory-main.ts(store construction)CODING_STANDARDS.mdsection 11