Skip to main content

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) (the usd() column helper). JS numbers are rounded to 8 decimal places at the store boundary, sums happen in SQL, values are read back with Number(text).
  • Timestamps are timestamp with time zone. The UTC day uses the day(n) helper in stores.ts, based on transaction_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 indexRule
request_id uniqueOne 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_prefixrequest_id text_pattern_ops, serves LIKE 'session-<id>/%' prefix scans (getSession, abandonPrefix)
usage_amount_usd_nonneg, usage_cost_usd_nonneg, usage_debited_usd_nonnegmoney is never negative
usage_tokens_nonnegall four token counters >= 0
usage_status_validstatus 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).

ConstraintRule
gateway_audit_kind_validkind in model, tool, context, control
gateway_audit_decision_validdecision in allow, deny
gateway_audit_identity_valididentity in obo, service, none
gateway_audit_reason_validan 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_nonnegnull or >= 0
gateway_audit_target_lentarget IS NULL OR length(target) <= 256 (added by 0004)

Session tables​

Constraint or indexRule
sessions_status_validstatus 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_turnunique (session_id) WHERE status IN ('pending','running'): at most one open turn per session
messages_status_validstatus IN ('pending','running','completed','failed','aborted')
messages_session_id_sessions_id_fkmessages.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​

ConstraintRule
factory_jobs_client_idempotency_key_unique(client, idempotency_key); a replay with another body (request_sha256 differs) is a conflict
factory_jobs_status_validstatus in FactoryStatus
factory_jobs_error_code_validfailed 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_hexhex regex
factory_jobs_budget_usd_rangebudget_usd > 0 AND budget_usd <= 2 (FACTORY_LIMITS.maxJobUsdCap)
factory_jobs_max_iterations_rangeBETWEEN 1 AND 5 (FACTORY_LIMITS.maxIterations)
factory_jobs_idempotency_key_lenlength BETWEEN 1 AND 128
factory_jobs_task_lenlength BETWEEN 1 AND 16384 (maxTaskChars = 16 * 1024)
factory_jobs_pending_gate_validthe 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_terminalclosed_at IS NULL exactly when status NOT IN ('pr_opened','failed','cancelled')
factory_jobs_client_created_at_idx(client, created_at)

Other factory constraints​

TableConstraintRule
factory_stepsfactory_steps_job_id_step_key_unique(job_id, step_key)
factory_stepsfactory_steps_step_key_validstep_key ~ '^(plan|(code|test|review)-[1-5])$'
factory_stepsfactory_steps_attempts_rangeBETWEEN 0 AND 3 (maxStepAttempts)
factory_stepsfactory_steps_outcome_sizeoctet_length(outcome::text) <= 4096
factory_artifactsfactory_artifacts_size_validsize = octet_length(bytes) AND size <= 1048576 (maxArtifactBytes)
factory_artifactsfactory_artifacts_iteration_rangeBETWEEN 0 AND 5
factory_artifactsfactory_artifacts_kind_valid, factory_artifacts_sha256_hexenum, hex
factory_approvalsfactory_approvals_one_consumedunique (job_id, gate) WHERE consumed_at IS NOT NULL: an approval is consumed at most once per gate
factory_approvalsfactory_approvals_gate_valid, factory_approvals_decision_valid, factory_approvals_digest_hexgate in FactoryGate; decision IN ('approve','reject'); hex
factory_eventsfactory_events_kind_valid, factory_events_detail_sizekind ~ '^[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​

ConstraintRule
quality_runs_status_validstatus in running, completed, partial, inconclusive, error, cancelled
quality_runs_intent_validintent in remediation, feature
quality_runs_verdict_validcompleted requires a verdict that is failed or 'passed:' || intent; every other status requires verdict IS NULL
quality_runs_error_code_validerror 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_hexhex regex. An admitted run keeps base_sha = '0' * 40 (PENDING_BASE_SHA) until setRunRefs records the merge base
quality_runs_image_digest_validnull or ^sha256:[0-9a-f]{64}$
quality_runs_repo_len, quality_runs_package_len, quality_runs_protected_branch_len1 to 140, 1 to 214, null or 1 to 255 characters
quality_runs_findings_sizeoctet_length(findings::text) <= 65536 (maxFindingsBytes = 64 * 1024)
quality_runs_counters_nonnegsandbox_ms, chunks_total, chunks_timeout, cache_hits >= 0
quality_runs_closed_iff_terminalclosed_at IS NULL exactly when status = 'running'
indexesquality_runs_repo_package_created_at_idx (repo, package, created_at), quality_runs_client_created_at_idx (client, created_at)

Other CodeCrucible constraints​

TableConstraintRule
quality_functionsquality_functions_run_id_file_qualified_name_unique(run_id, file, qualified_name)
quality_functionsquality_functions_coverage_rangecoverage BETWEEN 0 AND 1
quality_functionsquality_functions_counts_validcounters >= 0 and killed + survived + no_coverage + timeout <= mutants_total
quality_functionsquality_functions_file_len, quality_functions_qualified_name_len1 to 512 characters
quality_mutantsquality_mutants_run_id_stable_id_unique(run_id, stable_id); stable_id is the sha256 of (file, qualified name, mutator, original, replacement, ordinal)
quality_mutantsquality_mutants_status_validkilled, survived, no_coverage, timeout, compile_error, runtime_error, ignored, pending
quality_mutantsquality_mutants_rekill_validnull, confirmed or not_reproduced
quality_mutantsquality_mutants_snippet_lenoriginal and replacement at most 80 characters (maxSnippetChars)
quality_mutantsquality_mutants_killed_by_validkilled_by is a JSON array of at most 4096 bytes
quality_baselinesquality_baselines_function_unique(repo, package, branch, file, qualified_name)
quality_baselinesquality_baselines_mutation_score_range, quality_baselines_crap_nonneg0 to 100; >= 0
quality_chunk_cachequality_chunk_cache_report_sizeoctet_length(report) <= 131072 (maxChunkReportBytes = 128 * 1024)
quality_chunk_cachequality_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_submissionsquality_submissions_intent_valid, quality_submissions_head_sha_hex, _repo_len, _package_lenas for quality_runs
quality_submissionsquality_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​

TableAPISession workerFactory workerGatewayNotes
usageadmit and settle synchronous runsprepareTurn admits, finishTurn settles, abandon on retrystep rows, abandonPrefix('factory-<jobId>/') on closedebit (debited_usd, metered, cost_usd)via createPgStores / PgLedgerStore
settingsreadreadreadread per requestwritten only by an operator
alerts_sentinsertinsertinsertnonePgAlertStore.markSent
gateway_auditnonenonenoneinsertPgAuditStore.write
sessions, messagescreateSession, addMessage, requestCancel, markSessionFailed, failMessageclaimTurn, finishTurn, failTurn, closeSessionnonenonePgSessionStore
factory_jobscreateJob, requestCancel, consumeApproval (clears pending columns)nonesetStatus, openGate, expireGate, setPr, closeJobnonePgFactoryStore
factory_steps, factory_artifacts, factory_eventsreadnoneclaimStep, finishStep, setWorkspaceIn, putArtifact, recordEventnone
factory_approvalsinsert (consumed in the same transaction)nonereadDecision (read only)none
quality_submissionssubmit, releaseSubmissionnonenonenoneswept by the next submit
quality_runs, quality_functions, quality_mutants, quality_baselines, quality_chunk_cacheread (getRun, listSurvivors)quality core activities: createRun, setRunRefs, putFunctions, putMutants, writeBaseline, putChunk, sweepChunkCache, closeRunnonenonequality 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.

#FileChange
00000_init.sqlusage, settings, alerts_sent; index usage_day_client_idx
10001_usage_checks.sqlCHECKs usage_amount_usd_nonneg, usage_cost_usd_nonneg, usage_tokens_nonneg, usage_status_valid
20002_sessions.sqlsessions, messages, FK messages_session_id_sessions_id_fk, messages_one_open_turn, sessions_client_created_at_idx, usage_request_id_prefix
30003_gateway.sqlgateway_audit; usage.debited_usd, usage.metered, usage_job_id_idx, usage_debited_usd_nonneg
40004_audit_target_len.sqlCHECK gateway_audit_target_len
50005_factory.sqlfactory_jobs, factory_steps, factory_artifacts, factory_approvals, factory_events, their FKs to factory_jobs, factory_approvals_one_consumed, three indexes
60006_quality.sqlquality_runs (self FK on parent_id), quality_functions, quality_mutants, quality_baselines, quality_chunk_cache, their FKs and indexes
70007_quality_submissions.sqlquality_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.sql to packages/db/drizzle/0007_quality_submissions.sql, packages/db/drizzle/meta/_journal.json
  • packages/db/src/migrate.ts (migrate), packages/db/src/index.ts
  • packages/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.md section 11