Database Schema

This document is generated from the migrations. scripts/gen-db-schema.mjs applies every packages/backend/migrations/*.sql to a throwaway Postgres and introspects the result, so §3 below is the schema as it actually exists — not as someone remembered it. A hand-edit will be reverted by the next regeneration, and pnpm db:schema:check fails CI whenever a migration lands without the doc being regenerated.

Why it is generated. The previous hand-maintained doc documented ~20 tables while the migrations created 28. idempotency_keys was added with no doc entry and therefore never got the schema review this doc exists to enable; it shipped persisting credential-bearing response bodies in plaintext (remediated by 026_idempotency_no_store_bodies.sql). The documentation gap and the vulnerability were the same event.

Design intent (the why behind the DDL) lives in spec.md §28 (Persistence & State), §27 (Multi-Tenancy), §6 (Resource Model), §12.4 (Vault constraints), §13.5 (Memory versions), §17.8 (Scheduler exactly-once), and §23.6 (Webhook retry queue). The prose sections here (§1, §2, §4–§7) are hand-written commentary maintained in scripts/gen-db-schema.mjs; only §3 is machine-derived.

1. Principles

1. Postgres holds metadata only. The JSONL session tree is the canonical conversation record and is NOT duplicated into Postgres (§28). Postgres holds a row per session for queryability (status, usage, config) — never the conversation itself.
2. Every tenant-scoped table has tenant_id. Row-level filtering on tenantId is mandatory on every query (§27.1). The tenantScoped(query) helper in code makes the filter impossible to omit (compile-time: first arg is TenantCtx; runtime: asserts the SQL references tenant_id).
3. Cross-tenant access is impossible by construction (§27.1).
4. Forward-only migrations via node-pg-migrate (SQL files, §3.2). No heavyweight ORM; queries stay explicit so tenant filtering is auditable.
5. Secrets never land in a column that was not designed for them. Ciphertext columns are bytea alongside key_id/nonce; response/​payload columns must never be used to persist a credential (see §4).

2. Conventions

Convention	Choice	Rationale
tenant_id	text (prefixed, e.g. tnt_…)	Consistent with the wire ID format (§6.6); IDs are opaque strings throughout.
Resource IDs	text (prefixed per §6.6)	agent_, env_, sess_, vault_, mem_, memver_, skill_, file_, job_, wh_; ULID payload, server-generated.
Timestamps	timestamptz	RFC 3339 UTC. Every resource has created_at; mutable ones have updated_at.
metadata	jsonb	Max 4 KiB serialized, keys [a-zA-Z0-9_.-]+, scalar values only (app-enforced, §8).
Secrets	bytea (AES-256-GCM ciphertext + key_id + nonce)	Never plaintext; never logged (§12.4, §28).
API keys	argon2id hash only	Raw key never stored (§8).
Soft delete	status text column (active/archived/deleted)	Agents/vaults use archive (terminal, audit trail); environments/files have hard delete too (§6.2, §21).
Enums	text + app-level validation, not Postgres ENUM	Adding a value must not require a type migration.

---

3. Tables

Generated by introspecting a database with all packages/backend/migrations/*.sql applied. Column order is physical order; constraints and indexes are listed as Postgres reports them (pg_get_constraintdef / pg_get_indexdef), so what you read here is exactly what the database enforces.

31 tables (alphabetical):

• agent_versions
• agents
• api_keys
• console_sessions
• environments
• files
• idempotency_keys
• job_runs
• jobs
• ledger_entries
• memory_stores
• memory_versions
• onboarding_signups
• rate_limit_buckets
• sandbox_host_placements
• sandbox_hosts
• self_hosted_work_queue
• session_events
• session_outcomes
• session_threads
• sessions
• skill_versions
• skills
• tenant_billing
• tenant_quota_counters
• tenants
• usage_records
• vault_credentials
• vaults
• webhook_deliveries
• webhooks

agent_versions

Defined in: 003_agents.sql, 004_agent_versions.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
agent_id	text	NOT NULL
version	integer	NOT NULL
config	jsonb	NOT NULL
created_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
agent_versions_agent_id_fkey	FOREIGN KEY (agent_id) REFERENCES agents(id)
agent_versions_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
agent_versions_pkey	PRIMARY KEY (agent_id, version)

Index	Definition
idx_agent_versions_tenant_agent	CREATE INDEX idx_agent_versions_tenant_agent ON public.agent_versions USING btree (tenant_id, agent_id)

agents

Defined in: 003_agents.sql, 004_agent_versions.sql, 006_sessions.sql, 014_jobs.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
id	text	NOT NULL
name	text	NOT NULL
current_version	integer	NOT NULL	1
status	text	NOT NULL	'active'::text
metadata	jsonb	NOT NULL	'{}'::jsonb
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
agents_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
agents_pkey	PRIMARY KEY (id)

Index	Definition
idx_agents_tenant_id	CREATE INDEX idx_agents_tenant_id ON public.agents USING btree (tenant_id)
idx_agents_tenant_id_name	CREATE UNIQUE INDEX idx_agents_tenant_id_name ON public.agents USING btree (tenant_id, name)

api_keys

Defined in: 002_api_keys.sql, 025_onboarding.sql, 028_backfill_api_key_scopes.sql, 040_row_level_security.sql, 041_console_sessions.sql

Column	Type	Nullable	Default
id	text	NOT NULL
tenant_id	text	NOT NULL
key_hash	text	NOT NULL
name	text	NOT NULL
scopes	jsonb	NOT NULL	'[]'::jsonb
last_used_at	timestamp with time zone	nullable
created_at	timestamp with time zone	NOT NULL	now()
revoked_at	timestamp with time zone	nullable

Constraint	Definition
api_keys_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
api_keys_pkey	PRIMARY KEY (id)

Index	Definition
idx_api_keys_tenant_id	CREATE INDEX idx_api_keys_tenant_id ON public.api_keys USING btree (tenant_id)

console_sessions

Defined in: 041_console_sessions.sql

Column	Type	Nullable	Default
token_hash	text	NOT NULL
api_key_id	text	NOT NULL
tenant_id	text	NOT NULL
created_at	timestamp with time zone	NOT NULL	now()
expires_at	timestamp with time zone	NOT NULL
last_seen_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
console_sessions_api_key_id_fkey	FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON DELETE CASCADE
console_sessions_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
console_sessions_pkey	PRIMARY KEY (token_hash)

Index	Definition
idx_console_sessions_api_key_id	CREATE INDEX idx_console_sessions_api_key_id ON public.console_sessions USING btree (api_key_id)
idx_console_sessions_expires_at	CREATE INDEX idx_console_sessions_expires_at ON public.console_sessions USING btree (expires_at)
idx_console_sessions_tenant_id	CREATE INDEX idx_console_sessions_tenant_id ON public.console_sessions USING btree (tenant_id)

environments

Defined in: 005_environments.sql, 006_sessions.sql, 014_jobs.sql, 023_self_hosted_work_queue.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
id	text	NOT NULL
name	text	NOT NULL
type	text	NOT NULL
image	text	NOT NULL
resources	jsonb	NOT NULL
networking	jsonb	NOT NULL
packages	jsonb	NOT NULL	'[]'::jsonb
mounts	jsonb	NOT NULL	'[]'::jsonb
max_duration	integer	nullable
idle_timeout	integer	nullable
status	text	NOT NULL	'active'::text
metadata	jsonb	NOT NULL	'{}'::jsonb
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
environments_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
environments_pkey	PRIMARY KEY (id)

Index	Definition
idx_environments_tenant_id	CREATE INDEX idx_environments_tenant_id ON public.environments USING btree (tenant_id)
idx_environments_tenant_id_name	CREATE UNIQUE INDEX idx_environments_tenant_id_name ON public.environments USING btree (tenant_id, name)

files

Defined in: 006_sessions.sql, 011_files.sql, 038_quota_counter_file_storage.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
id	text	NOT NULL
name	text	NOT NULL
content_type	text	nullable
size_bytes	bigint	NOT NULL
object_key	text	NOT NULL
session_id	text	nullable
metadata	jsonb	NOT NULL	'{}'::jsonb
created_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
files_session_id_fkey	FOREIGN KEY (session_id) REFERENCES sessions(id)
files_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
files_pkey	PRIMARY KEY (id)

Index	Definition
idx_files_tenant_id	CREATE INDEX idx_files_tenant_id ON public.files USING btree (tenant_id)
idx_files_tenant_session	CREATE INDEX idx_files_tenant_session ON public.files USING btree (tenant_id, session_id)

idempotency_keys

Defined in: 021_idempotency_keys.sql, 026_idempotency_no_store_bodies.sql, 039_idempotency_claim_lease.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
key_hash	text	NOT NULL
request_body_hash	text	NOT NULL
response_status	integer	NOT NULL
response_body	text	nullable
created_at	timestamp with time zone	NOT NULL	now()
expires_at	timestamp with time zone	NOT NULL
claimed_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
idempotency_keys_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
idempotency_keys_tenant_id_key_hash_key	UNIQUE (tenant_id, key_hash)

Index	Definition
idx_idempotency_keys_expires_at	CREATE INDEX idx_idempotency_keys_expires_at ON public.idempotency_keys USING btree (expires_at)

job_runs

Defined in: 015_job_runs.sql, 034_job_runs_claim.sql, 039_idempotency_claim_lease.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
id	text	NOT NULL
job_id	text	NOT NULL
scheduled_at	timestamp with time zone	NOT NULL
triggered_at	timestamp with time zone	nullable
session_id	text	nullable
manual	boolean	NOT NULL	false
error	jsonb	nullable
created_at	timestamp with time zone	NOT NULL	now()
claimed_at	timestamp with time zone	nullable

Constraint	Definition
job_runs_job_id_fkey	FOREIGN KEY (job_id) REFERENCES jobs(id)
job_runs_session_id_fkey	FOREIGN KEY (session_id) REFERENCES sessions(id)
job_runs_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
job_runs_pkey	PRIMARY KEY (id)

Index	Definition
idx_job_runs_job_scheduled	CREATE UNIQUE INDEX idx_job_runs_job_scheduled ON public.job_runs USING btree (job_id, scheduled_at)
idx_job_runs_recovery	CREATE INDEX idx_job_runs_recovery ON public.job_runs USING btree (triggered_at, claimed_at, scheduled_at)
idx_job_runs_tenant_created	CREATE INDEX idx_job_runs_tenant_created ON public.job_runs USING btree (tenant_id, created_at)

jobs

Defined in: 014_jobs.sql, 015_job_runs.sql, 031_tenant_quota_counters.sql, 032_jobs_status_index.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
id	text	NOT NULL
name	text	NOT NULL
agent_id	text	NOT NULL
agent_version	integer	NOT NULL
environment_id	text	NOT NULL
initial_events	jsonb	NOT NULL
session_config	jsonb	NOT NULL	'{}'::jsonb
schedule_cron	text	NOT NULL
schedule_tz	text	NOT NULL
one_shot	boolean	NOT NULL	false
status	text	NOT NULL	'active'::text
paused_reason	jsonb	nullable
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
jobs_agent_id_fkey	FOREIGN KEY (agent_id) REFERENCES agents(id)
jobs_environment_id_fkey	FOREIGN KEY (environment_id) REFERENCES environments(id)
jobs_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
jobs_pkey	PRIMARY KEY (id)

Index	Definition
idx_jobs_status	CREATE INDEX idx_jobs_status ON public.jobs USING btree (status) WHERE (status = 'active'::text)
idx_jobs_tenant_id	CREATE INDEX idx_jobs_tenant_id ON public.jobs USING btree (tenant_id)
idx_jobs_tenant_id_name	CREATE UNIQUE INDEX idx_jobs_tenant_id_name ON public.jobs USING btree (tenant_id, name)

ledger_entries

Defined in: 042_billing_ledger.sql

Column	Type	Nullable	Default
id	text	NOT NULL
tenant_id	text	NOT NULL
kind	text	NOT NULL
amount_micros	bigint	NOT NULL
balance_after_micros	bigint	NOT NULL
idempotency_key	text	NOT NULL
source	text	nullable
metadata	jsonb	nullable
created_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
ledger_amount_sign	CHECK ((((kind = ANY (ARRAY['grant'::text, 'topup'::text])) AND (amount_micros > 0)) OR ((kind = 'debit'::text) AND (amount_micros < 0)) OR (kind = 'adjustment'::text)))
ledger_entries_amount_micros_check	CHECK ((amount_micros <> 0))
ledger_entries_kind_check	CHECK ((kind = ANY (ARRAY['grant'::text, 'topup'::text, 'debit'::text, 'adjustment'::text])))
ledger_entries_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
ledger_entries_pkey	PRIMARY KEY (id)
ledger_entries_tenant_id_idempotency_key_key	UNIQUE (tenant_id, idempotency_key)

Index	Definition
idx_ledger_entries_tenant_created	CREATE INDEX idx_ledger_entries_tenant_created ON public.ledger_entries USING btree (tenant_id, created_at DESC, id DESC)

memory_stores

Defined in: 009_memory_stores.sql, 010_memory_versions.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
id	text	NOT NULL
display_title	text	NOT NULL
instructions	text	nullable
access	text	NOT NULL	'read_write'::text
status	text	NOT NULL	'active'::text
object_key_prefix	text	NOT NULL
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
memory_stores_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
memory_stores_pkey	PRIMARY KEY (id)

Index	Definition
idx_memory_stores_tenant_id	CREATE INDEX idx_memory_stores_tenant_id ON public.memory_stores USING btree (tenant_id)
idx_memory_stores_tenant_title	CREATE UNIQUE INDEX idx_memory_stores_tenant_title ON public.memory_stores USING btree (tenant_id, display_title)

memory_versions

Defined in: 010_memory_versions.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
store_id	text	NOT NULL
id	text	NOT NULL
memory_path	text	nullable
content_sha256	text	NOT NULL
content_object_key	text	nullable
redacted	boolean	NOT NULL	false
created_at	timestamp with time zone	NOT NULL	now()
expires_at	timestamp with time zone	nullable

Constraint	Definition
memory_versions_store_id_fkey	FOREIGN KEY (store_id) REFERENCES memory_stores(id)
memory_versions_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
memory_versions_pkey	PRIMARY KEY (id)

Index	Definition
idx_memory_versions_store_created	CREATE INDEX idx_memory_versions_store_created ON public.memory_versions USING btree (store_id, created_at)
idx_memory_versions_tenant_id	CREATE INDEX idx_memory_versions_tenant_id ON public.memory_versions USING btree (tenant_id)

onboarding_signups

Defined in: 025_onboarding.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
admin_email	text	NOT NULL
tenant_id	text	NOT NULL
created_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
onboarding_signups_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
onboarding_signups_pkey	PRIMARY KEY (admin_email)

Index	Definition
idx_onboarding_signups_tenant_id	CREATE INDEX idx_onboarding_signups_tenant_id ON public.onboarding_signups USING btree (tenant_id)

rate_limit_buckets

Defined in: 027_rate_limit_buckets.sql

Column	Type	Nullable	Default
key	text	NOT NULL
tokens	double precision	NOT NULL
capacity	double precision	NOT NULL
refill_per_ms	double precision	NOT NULL
allowed	boolean	NOT NULL	true
last_refill_ms	bigint	NOT NULL
updated_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
rate_limit_buckets_pkey	PRIMARY KEY (key)

Index	Definition
idx_rate_limit_buckets_last_refill	CREATE INDEX idx_rate_limit_buckets_last_refill ON public.rate_limit_buckets USING btree (last_refill_ms)

sandbox_host_placements

Defined in: 024_sandbox_hosts.sql, 036_audit_remediation.sql

Column	Type	Nullable	Default
sandbox_name	text	NOT NULL
host_id	text	NOT NULL
created_at	timestamp with time zone	NOT NULL	now()
cpus	integer	NOT NULL	1
memory_mib	integer	NOT NULL	512

Constraint	Definition
sandbox_host_placements_host_id_fkey	FOREIGN KEY (host_id) REFERENCES sandbox_hosts(id)
sandbox_host_placements_pkey	PRIMARY KEY (sandbox_name)

Index	Definition
idx_sandbox_host_placements_host	CREATE INDEX idx_sandbox_host_placements_host ON public.sandbox_host_placements USING btree (host_id)

sandbox_hosts

Defined in: 024_sandbox_hosts.sql

Column	Type	Nullable	Default
id	text	NOT NULL
endpoint	text	NOT NULL
cpus	integer	NOT NULL
memory_mib	integer	NOT NULL
status	text	NOT NULL	'healthy'::text
labels	jsonb	NOT NULL	'{}'::jsonb
last_heartbeat	timestamp with time zone	nullable
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
sandbox_hosts_pkey	PRIMARY KEY (id)

Index	Definition
idx_sandbox_hosts_status	CREATE INDEX idx_sandbox_hosts_status ON public.sandbox_hosts USING btree (status)

self_hosted_work_queue

Defined in: 023_self_hosted_work_queue.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
id	text	NOT NULL
session_id	text	NOT NULL
environment_id	text	NOT NULL
status	text	NOT NULL	'queued'::text
work_spec	jsonb	NOT NULL
results	jsonb	NOT NULL	'[]'::jsonb
claimed_by	text	nullable
claimed_at	timestamp with time zone	nullable
queued_at	timestamp with time zone	NOT NULL	now()
completed_at	timestamp with time zone	nullable
stop_requested	text	nullable
stop_requested_at	timestamp with time zone	nullable
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
self_hosted_work_queue_environment_id_fkey	FOREIGN KEY (environment_id) REFERENCES environments(id)
self_hosted_work_queue_session_id_fkey	FOREIGN KEY (session_id) REFERENCES sessions(id)
self_hosted_work_queue_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
self_hosted_work_queue_pkey	PRIMARY KEY (id)

Index	Definition
idx_self_hosted_work_queue_env_status	CREATE INDEX idx_self_hosted_work_queue_env_status ON public.self_hosted_work_queue USING btree (environment_id, status, queued_at)
idx_self_hosted_work_queue_session	CREATE UNIQUE INDEX idx_self_hosted_work_queue_session ON public.self_hosted_work_queue USING btree (session_id)
idx_self_hosted_work_queue_tenant	CREATE INDEX idx_self_hosted_work_queue_tenant ON public.self_hosted_work_queue USING btree (tenant_id)

session_events

Defined in: 030_session_events.sql, 035_session_events_unique_event_id.sql, 037_session_events_retention.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
session_id	text	NOT NULL
position	bigint	NOT NULL
type	text	NOT NULL
event_id	text	NOT NULL
created_at	timestamp with time zone	NOT NULL	now()
payload	jsonb	NOT NULL

Constraint	Definition
session_events_session_id_fkey	FOREIGN KEY (session_id) REFERENCES sessions(id)
session_events_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
session_events_pkey	PRIMARY KEY (session_id, "position")

Index	Definition
idx_session_events_created_at	CREATE INDEX idx_session_events_created_at ON public.session_events USING btree (created_at)
idx_session_events_session_event_id	CREATE UNIQUE INDEX idx_session_events_session_event_id ON public.session_events USING btree (session_id, event_id)
idx_session_events_tenant_id	CREATE INDEX idx_session_events_tenant_id ON public.session_events USING btree (tenant_id)

session_outcomes

Defined in: 018_session_outcomes.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
session_id	text	NOT NULL
id	text	NOT NULL
description	text	NOT NULL
rubric	jsonb	NOT NULL
max_iterations	integer	NOT NULL	3
status	text	NOT NULL
result	text	nullable
iteration	integer	NOT NULL	0
created_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
session_outcomes_session_id_fkey	FOREIGN KEY (session_id) REFERENCES sessions(id)
session_outcomes_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
session_outcomes_pkey	PRIMARY KEY (id)

Index	Definition
idx_session_outcomes_tenant_session	CREATE INDEX idx_session_outcomes_tenant_session ON public.session_outcomes USING btree (tenant_id, session_id)

session_threads

Defined in: 019_session_threads.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
session_id	text	NOT NULL
thread_id	text	NOT NULL
agent_name	text	NOT NULL
status	text	NOT NULL
created_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
session_threads_session_id_fkey	FOREIGN KEY (session_id) REFERENCES sessions(id)
session_threads_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
session_threads_pkey	PRIMARY KEY (session_id, thread_id)

Index	Definition
idx_session_threads_tenant_session	CREATE INDEX idx_session_threads_tenant_session ON public.session_threads USING btree (tenant_id, session_id)

sessions

Defined in: 003_agents.sql, 006_sessions.sql, 011_files.sql, 015_job_runs.sql, 018_session_outcomes.sql, 019_session_threads.sql, 020_usage_records.sql, 023_self_hosted_work_queue.sql, 029_sessions_synced_etag.sql, 030_session_events.sql, 031_tenant_quota_counters.sql, 034_job_runs_claim.sql, 036_audit_remediation.sql, 040_row_level_security.sql, 041_console_sessions.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
id	text	NOT NULL
agent_id	text	NOT NULL
agent_version	integer	NOT NULL
environment_id	text	NOT NULL
title	text	nullable
status	text	NOT NULL	'idle'::text
stop_reason	text	nullable
budget	jsonb	nullable
usage	jsonb	NOT NULL	'{}'::jsonb
vault_ids	jsonb	NOT NULL	'[]'::jsonb
resources	jsonb	NOT NULL	'[]'::jsonb
agent_overrides	jsonb	nullable
metadata	jsonb	NOT NULL	'{}'::jsonb
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()
last_activity_at	timestamp with time zone	NOT NULL	now()
jsonl_object_key	text	NOT NULL
forked_from_session_id	text	nullable
sandbox_handle	text	nullable
synced_etag	text	nullable
owner_instance_id	text	nullable

Constraint	Definition
sessions_agent_id_fkey	FOREIGN KEY (agent_id) REFERENCES agents(id)
sessions_environment_id_fkey	FOREIGN KEY (environment_id) REFERENCES environments(id)
sessions_forked_from_session_id_fkey	FOREIGN KEY (forked_from_session_id) REFERENCES sessions(id)
sessions_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
sessions_pkey	PRIMARY KEY (id)

Index	Definition
idx_sessions_tenant_agent	CREATE INDEX idx_sessions_tenant_agent ON public.sessions USING btree (tenant_id, agent_id)
idx_sessions_tenant_created_id	CREATE INDEX idx_sessions_tenant_created_id ON public.sessions USING btree (tenant_id, created_at DESC, id DESC) WHERE (status <> 'archived'::text)
idx_sessions_tenant_environment	CREATE INDEX idx_sessions_tenant_environment ON public.sessions USING btree (tenant_id, environment_id)
idx_sessions_tenant_status	CREATE INDEX idx_sessions_tenant_status ON public.sessions USING btree (tenant_id, status)

skill_versions

Defined in: 013_skill_versions.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
skill_id	text	NOT NULL
version	integer	NOT NULL
object_key	text	NOT NULL
created_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
skill_versions_skill_id_fkey	FOREIGN KEY (skill_id) REFERENCES skills(id)
skill_versions_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
skill_versions_pkey	PRIMARY KEY (skill_id, version)

Index	Definition
idx_skill_versions_tenant_skill	CREATE INDEX idx_skill_versions_tenant_skill ON public.skill_versions USING btree (tenant_id, skill_id)

skills

Defined in: 004_agent_versions.sql, 012_skills.sql, 013_skill_versions.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
id	text	NOT NULL
display_title	text	NOT NULL
type	text	NOT NULL
created_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
skills_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
skills_pkey	PRIMARY KEY (id)

Index	Definition
idx_skills_tenant_id	CREATE INDEX idx_skills_tenant_id ON public.skills USING btree (tenant_id)
idx_skills_tenant_title_custom	CREATE UNIQUE INDEX idx_skills_tenant_title_custom ON public.skills USING btree (tenant_id, display_title) WHERE (type = 'custom'::text)

tenant_billing

Defined in: 042_billing_ledger.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
lifecycle	text	NOT NULL	'trial'::text
balance_micros	bigint	NOT NULL	0
verification_token_hash	text	nullable
verification_expires_at	timestamp with time zone	nullable
verified_at	timestamp with time zone	nullable
pending_grant_micros	bigint	NOT NULL	0
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
tenant_billing_lifecycle_check	CHECK ((lifecycle = ANY (ARRAY['trial'::text, 'active'::text, 'suspended'::text])))
tenant_billing_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
tenant_billing_pkey	PRIMARY KEY (tenant_id)

tenant_quota_counters

Defined in: 031_tenant_quota_counters.sql, 038_quota_counter_file_storage.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
resource	text	NOT NULL
count	bigint	NOT NULL	0

Constraint	Definition
tenant_quota_counters_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
tenant_quota_counters_pkey	PRIMARY KEY (tenant_id, resource)

tenants

Defined in: 001_tenants.sql, 002_api_keys.sql, 003_agents.sql, 004_agent_versions.sql, 005_environments.sql, 006_sessions.sql, 007_vaults.sql, 008_vault_credentials.sql, 009_memory_stores.sql, 010_memory_versions.sql, 011_files.sql, 012_skills.sql, 013_skill_versions.sql, 014_jobs.sql, 015_job_runs.sql, 016_webhooks.sql, 017_webhook_deliveries.sql, 018_session_outcomes.sql, 019_session_threads.sql, 020_usage_records.sql, 021_idempotency_keys.sql, 023_self_hosted_work_queue.sql, 024_sandbox_hosts.sql, 025_onboarding.sql, 030_session_events.sql, 031_tenant_quota_counters.sql, 032_jobs_status_index.sql, 037_session_events_retention.sql, 041_console_sessions.sql, 042_billing_ledger.sql

Column	Type	Nullable	Default
id	text	NOT NULL
name	text	NOT NULL
quota_plan	text	nullable
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
tenants_pkey	PRIMARY KEY (id)

usage_records

Defined in: 020_usage_records.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
session_id	text	NOT NULL
model	text	NOT NULL
input_tokens	bigint	NOT NULL	0
output_tokens	bigint	NOT NULL	0
cache_creation_input_tokens	bigint	NOT NULL	0
cache_read_input_tokens	bigint	NOT NULL	0
usd_cost	numeric(12,6)	NOT NULL	0
recorded_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
usage_records_session_id_fkey	FOREIGN KEY (session_id) REFERENCES sessions(id)
usage_records_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)

Index	Definition
idx_usage_records_tenant_recorded	CREATE INDEX idx_usage_records_tenant_recorded ON public.usage_records USING btree (tenant_id, recorded_at)
idx_usage_records_tenant_session_recorded	CREATE INDEX idx_usage_records_tenant_session_recorded ON public.usage_records USING btree (tenant_id, session_id, recorded_at)

vault_credentials

Defined in: 008_vault_credentials.sql, 022_vault_credentials_id.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
vault_id	text	NOT NULL
key	text	NOT NULL
category	text	NOT NULL
secret_enc	bytea	nullable
key_id	text	nullable
nonce	bytea	nullable
metadata	jsonb	NOT NULL	'{}'::jsonb
status	text	NOT NULL	'active'::text
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()
id	text	NOT NULL

Constraint	Definition
vault_credentials_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
vault_credentials_vault_id_fkey	FOREIGN KEY (vault_id) REFERENCES vaults(id)
vault_credentials_pkey	PRIMARY KEY (id)

Index	Definition
idx_vault_credentials_tenant_id	CREATE INDEX idx_vault_credentials_tenant_id ON public.vault_credentials USING btree (tenant_id)
idx_vault_credentials_vault_key	CREATE UNIQUE INDEX idx_vault_credentials_vault_key ON public.vault_credentials USING btree (vault_id, key)

vaults

Defined in: 007_vaults.sql, 008_vault_credentials.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
id	text	NOT NULL
name	text	NOT NULL
status	text	NOT NULL	'active'::text
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
vaults_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
vaults_pkey	PRIMARY KEY (id)

Index	Definition
idx_vaults_tenant_id	CREATE INDEX idx_vaults_tenant_id ON public.vaults USING btree (tenant_id)
idx_vaults_tenant_id_name	CREATE UNIQUE INDEX idx_vaults_tenant_id_name ON public.vaults USING btree (tenant_id, name)

webhook_deliveries

Defined in: 017_webhook_deliveries.sql, 033_webhook_delivery_safety.sql, 034_job_runs_claim.sql, 039_idempotency_claim_lease.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
id	text	NOT NULL
webhook_id	text	NOT NULL
event_id	text	NOT NULL
event_type	text	NOT NULL
payload	jsonb	NOT NULL
attempt	integer	NOT NULL	1
status	text	NOT NULL	'pending'::text
next_attempt_at	timestamp with time zone	nullable
last_response_code	integer	nullable
created_at	timestamp with time zone	NOT NULL	now()
claimed_at	timestamp with time zone	nullable

Constraint	Definition
webhook_deliveries_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
webhook_deliveries_webhook_id_fkey	FOREIGN KEY (webhook_id) REFERENCES webhooks(id)
webhook_deliveries_pkey	PRIMARY KEY (id)

Index	Definition
idx_webhook_deliveries_pending_next_attempt	CREATE INDEX idx_webhook_deliveries_pending_next_attempt ON public.webhook_deliveries USING btree (next_attempt_at) WHERE (status = 'pending'::text)
idx_webhook_deliveries_tenant_id	CREATE INDEX idx_webhook_deliveries_tenant_id ON public.webhook_deliveries USING btree (tenant_id)
idx_webhook_deliveries_webhook_event	CREATE UNIQUE INDEX idx_webhook_deliveries_webhook_event ON public.webhook_deliveries USING btree (webhook_id, event_id)

webhooks

Defined in: 016_webhooks.sql, 017_webhook_deliveries.sql, 033_webhook_delivery_safety.sql, 040_row_level_security.sql

Column	Type	Nullable	Default
tenant_id	text	NOT NULL
id	text	NOT NULL
url	text	NOT NULL
signing_secret_hash	text	NOT NULL
event_types	jsonb	NOT NULL	'[]'::jsonb
status	text	NOT NULL	'active'::text
disabled_reason	text	nullable
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

Constraint	Definition
webhooks_tenant_id_fkey	FOREIGN KEY (tenant_id) REFERENCES tenants(id)
webhooks_pkey	PRIMARY KEY (id)

Index	Definition
idx_webhooks_event_types	CREATE INDEX idx_webhooks_event_types ON public.webhooks USING gin (event_types jsonb_path_ops)
idx_webhooks_tenant_id	CREATE INDEX idx_webhooks_tenant_id ON public.webhooks USING btree (tenant_id)

---

4. Encrypted-Column Strategy

vault_credentials.secret_enc is stored as AES-256-GCM ciphertext, alongside key_id (which KMS/keyfile key was used) and nonce (the GCM nonce).

• Key source: KMS-managed key for SaaS; a key file for self-hosted (§28).
• The key is required for restore and must be backed up separately from the database (§28). Losing the key = losing all vault credentials.
• Encryption/decryption happens in the application layer (the SecretStore port, §25.3); Postgres never sees plaintext.
• On credential archive, secret_enc/key_id/nonce are set to null (secret purged, key freed, audit row retained — §12.7).
• API-key hashing uses argon2id (§8) — distinct from secret encryption; the raw key is never stored, only the hash.

Columns that must never hold a credential

Table.column	Rule
idempotency_keys.response_body	Nullable by design. Credential-issuing routes (API-key create, webhook create/rotate) record the idempotency key with a NULL body; a replay yields 409 idempotency_conflict rather than re-serving a pmb_live_/whsec_ secret (026_idempotency_no_store_bodies.sql, §25.1, §8).
webhook_deliveries.payload	Thin payload only — type + id + createdAt (§23.2). Never the resource body.
api_keys.key_hash	argon2id hash of the key, never the key.
webhooks.signing_secret_hash	Hash of the whsec_ secret, never the secret.

5. What is NOT in Postgres

Explicit list (§28):

State	Store
Session JSONL tree (conversation)	Object store (<key from sessions.jsonl_object_key>)
Memory store contents	Object store (memory_stores.object_key_prefix + path)
Uploaded files	Object store (files.object_key)
Skill bundles	Object store (skill_versions.object_key)
Sandbox filesystems	microsandbox home (~/.microsandbox/) — ephemeral, checkpointed on idle
Raw vault secrets	Only ciphertext in Postgres; raw values in the microsandbox host-side store at runtime, purged on session end

The backend does not duplicate the conversation into Postgres (§28).

Object-store key layout

Artifact	Key
Session JSONL log	tenants/<tenant_id>/sessions/<session_id>/log.jsonl
Memory store contents	tenants/<tenant_id>/memory/<store_id>/<memory_path>
Uploaded files	tenants/<tenant_id>/files/<file_id>
Skill bundles	tenants/<tenant_id>/skills/<skill_id>/v<version>/
Sandbox snapshots	tenants/<tenant_id>/snapshots/<session_id>/

JSONL durability policy (§28): active sessions append to local disk (Pi-native); the backend syncs to the object store on every session.status_idle transition and at a periodic interval while running (default 30s). A host loss can lose at most the tail of the current turn — never an idle session.

6. Retention & Purge

Table	Column	Policy	Source
sessions	last_activity_at	30-day checkpoint window (idle sandbox retained); 90-day JSONL retention (purge-on-request supported)	§6.3
memory_versions	expires_at	30-day retention; recent versions always kept regardless of age	§13.5
idempotency_keys	expires_at	24h replay window; rows swept after expiry	api-reference.md, §"Idempotency-Key"
job_runs	created_at	90-day default (configurable)	this doc
webhook_deliveries	created_at	90-day default (configurable)	this doc
usage_records	recorded_at	365-day default (configurable per tier)	this doc
session_events	created_at	Follows the owning session's retention	this doc
rate_limit_buckets	window_start	Transient; rows expire with their window	this doc

Per-tier overrides land in WP-5.5 (§29.6).

7. Migration Notes

• Forward-only SQL migrations via node-pg-migrate (§3.2). No down migrations in prod; the -- Down Migration half exists for the up/down clean test and local development.
• Each migration is a single .sql file in packages/backend/migrations/, numbered and applied in filename order.
• Adding a migration means regenerating this doc (pnpm db:schema:gen) and committing the result. pnpm db:schema:check fails CI otherwise — that check is the control that makes a new table impossible to land unreviewed.
• New tables must also be added to the TABLES list in packages/backend/src/infra/db/__tests__/migrations.test.ts, which is what makes the down-migration round-trip test able to catch a leaked table.
• FKs reference tenant-scoped PKs. The tenantScoped(query) helper makes omitting the tenant_id filter a compile-time error and a runtime assertion failure (§27.1).