Data model

Everything is stored in Postgres, scoped to a workspace. The rule that shapes the schema: nothing that was recorded is ever changed. A change is a new row or a new version, and the database refuses updates and deletes on the tables that hold evidence.

In plain words

• A record is anything with an identity: a task, an idea, a plan, a decision board. It never changes. Each edit adds a version with a hash of its content.
• A signature is a person approving one exact version. It names who, what they meant, and the hash they saw.
• The audit log lists every event in order. Each entry includes the hash of the one before it, so a gap or edit shows.
• A task is a record with a history of transitions (ready, claimed, running and so on) and links to the issue, branch, commit, PR and card it works on.
• Evidence (gate results, publish checkpoints, preflight results, run manifests) is written once and kept.

Groups of tables

Group	Tables
Records and signatures	workspaces, records, record_versions, record_signatures, record_lineage
Audit	audit_events (partitioned by month), audit_chain_heads
Tasks	tasks, task_transitions, task_links, task_human_touches, task_change_fingerprints, task_branch_cleanups
Runner queue	runner_jobs (lease, token hash, expiry), runner_registrations
Evidence and publishing	agent_gate_evidence, agent_publish_intents, agent_publish_checkpoints, agent_publish_trees, preflight_evidence, run_manifests, review_certificates, runner_gate_keys
Sessions	task_session_chunks (the saved agent session)
Review and board	agent_shadow_reviews, pr_review_observations, reconciler_findings, reconciler_settled_checks, main_health_result, main_health_alert
Briefs and intake	brief_areas, brief_intake_channels, brief_source_stagings, brief_source_consumed, research_notes, research_fetches, memory_notes
Sign-in	local_users, local_passkeys, local_sessions, local_ceremonies, local_authentication_events, oidc_identities, oidc_pending, oidc_requests
Models and usage	model_usage, model_catalog_heads, model_catalog_changes, model_candidate_alerts, workspace_caps
Alerts	notification_deliveries, phone_alert_deliveries

Technical notes

• Record IDs look like <kind>_<uuidv7>. Versions must be appended in order (a trigger enforces it) and each has a SHA-256 content_hash over its canonical JSON (forelead-json-v1).
• Evidence tables carry an immutability trigger (reject_immutable_change) and grant the app role SELECT, INSERT only.
• Each service has its own database role (forelead_app, forelead_gateway, forelead_migrator, forelead_preflight and others; see deploy/service-roles.sql). The runner has no database access at all.
• Migrations are forward-only Goose files in migrations/. 00001 to 00089 keep their numbers; new ones are named YYYYMMDDHHMMSS_name.sql and may apply out of order. An image refuses to start against a schema newer than it knows, unless FORELEAD_ALLOW_NEWER_SCHEMA=1.
• The table list above is taken from the CREATE TABLE statements in migrations/; a table dropped by a later migration may still be listed.
• Record lineage and rebuild: Record lineage rebuild. Audit export: Audit export.

Sources

• migrations/, migrations/README.md, migrations/00001_foundations.sql
• internal/records/, internal/auditexport
• deploy/service-roles.sql