agents — Vrida Docs

`agents` — module #28, Phase 5 of 6 of the agents-v2/v3 build

47 tables, 475 columns — schema-locked 2026-07-16 (PROJECT_DECISIONS #66, to be assigned at lock). agents is the full agent-orchestration module: v1's own 21-table baseline (agent_task, agent_thread, agent_event_log, agent_schedule, agent_trigger, agent_eval_suite, agent_eval_run, agent_eval_case, agent_performance_profile, agent_shadow_run, agent_shadow_decision, agent_autonomy_profile, agent_action, rollback_recipe, rollback_execution, tool_catalog [superseded], agent_tool_grant, agent_catalog_entry, agent_catalog_entry_required_tool, tenant_agent_deployment, agent_incident — never previously built, confirmed live: zero tables existed anywhere in this schema before this migration) merged with v2's amendments (A1–A8) and v3's corrections (BLOCKER 1–8, I1–I8) into one coherent build, not merely the v2/v3 delta. Design of record: vrida-agents-module-design-proposal-2026-07-11.md (v1) + vrida-agents-v2-design-amendment-2026-07-11.md (A1–A8) + vrida-agents-v3-correction-pass-2026-07-12.md (BLOCKER 1–8, I1–I8, v3 wins on conflict). Migration: packages/db/migrations/20260716000000_agents_module_new_schema.sql (~1650 lines, including a same-day SECTION 10 appended after the build's own Section 4 self-audit found 2 fixes). Drizzle schema files: packages/db/src/schema/agents/{_schema,tool,skill,marketplace,task,eval,shadow,certification,decision_context,action,kill_switch,policy,index}.ts.

This is Phase 5 of the 6-phase agents-v2/v3 build authorization (Phase 1 reopened platform for the module registry + AI-capacity layer, PROJECT_DECISIONS #62; Phase 2 reopened ai for the model-registry/deployment/prompt/routing layer, #63; Phase 3 built semantics, #64; Phase 4 built signals, #65). Several of the design's own named blockers were already resolved by those earlier phases before this migration was ever written — I2/I3/I4 (approved_function_registry hardening, tenant_goal_binding/tenant_constraint_binding, dimension_definition/metric_dependency + cycle trigger) and BLOCKER 1/2/3/8 (signals SECURITY DEFINER fix, partition/authority split, assigned_at forgery, outcome_observation ROI fields) are Phase 3/Phase 4 work, confirmed live before this migration started. I7 (module_dependency cycle + deactivation guard) is Phase 1 work. This migration covers exactly the remainder: the agents schema itself, plus the specific cross-schema wiring gaps those earlier phases left as disclosed forward-refs (wiring signals.lock_experiment_causal_basis() onto agents.decision_context_snapshot; I6's platform.polymorphic_target_registry + validator function).

tool_catalog (v1) is SUPERSEDED by tool_definition/tool_version (the A2b version-row pattern — v1's tool_catalog had zero live rows anywhere, so no migration/backfill was needed; it is simply not built). identity.agent_skill_assignment (v1/A2) is SUPERSEDED by agents.agent_skill_assignment (I5) — built here as a new, empty table (zero live rows in the old table, confirmed live). Dropping the old identity.agent_skill_assignment table is Phase 6's job — a deliberate build-order exception, matching v2's own Section 10 disclosed sequencing (see "Transitional Drizzle barrel collision" below).

PROJECT_DECISIONS entry: #66 (to be finalized at lock; cross-referenced from #62–#65 for Phases 1–4 of this same 6-phase build).

Groups: Tool/Skill Catalog (12 tables: tool_definition, tool_version, tool_version_module, toolset_definition, toolset_version, toolset_version_member, skill_definition, skill_version, skill_version_toolset, skill_version_required_duty, skill_version_module, agent_skill_assignment) / Marketplace + Deployment (4 tables: agent_catalog_entry, agent_catalog_entry_required_tool, tenant_agent_deployment, agent_tool_grant) / Task Orchestration (5 tables: agent_schedule, agent_trigger, agent_task, agent_thread, agent_event_log) / Eval (5 tables: agent_eval_suite, agent_eval_suite_version, agent_eval_run, agent_eval_case, agent_performance_profile) / Shadow-Mode (3 tables: agent_shadow_run, agent_shadow_decision, agent_autonomy_profile) / Decision-Context Evidence Envelope (7 tables: evidence_retention_policy, decision_context_snapshot, decision_context_manifest, decision_context_feature, decision_context_forecast, decision_context_metric, decision_context_policy, decision_context_knowledge_source — 8 tables, see note below) / Action Ledger + Rollback + Certification (6 tables: agent_decision, agent_action, rollback_recipe, rollback_execution, agent_incident, skill_certification) / Kill-Switch (2 tables: kill_switch_event, kill_switch_scope_state) / Policy (2 tables: skill_execution_policy, workload_class).

(Decision-Context group is actually 8 tables, not 7 — evidence_retention_policy plus the 2 partitioned envelope tables plus the 5 typed direct-link join tables; the group count above corrects the summary-sentence miscount to match the table-by-table section that follows.)

Which tables are partitioned

Table	Partitioned?	Strategy
`decision_context_snapshot`	YES	`PARTITION BY RANGE (created_at)`, monthly — 13 monthly partitions (2026-06 through 2027-06) + 1 `DEFAULT` catch-all = 14 partitions
`decision_context_manifest`	YES	Same shape as `decision_context_snapshot` — 14 partitions
`agent_decision`	YES	Same shape — 14 partitions
All other 44 tables	NO	Standard catalog/transactional/tenant-scoped tables, native `PRIMARY KEY`

Same E1 volume-profile convention ai.agent_execution/signals.outcome_observation already established: the partition key (created_at) is required in every UNIQUE/PRIMARY KEY constraint on these 3 tables, so all 3 carry no standalone PRIMARY KEY on id alone — PRIMARY KEY (id, created_at) plus a separate UNIQUE (id, tenant_id, created_at) (the composite-identity shape any FK into them must resolve against). Triggers created directly on a partitioned parent table automatically clone to every partition (confirmed live, Postgres 11+); GRANT/RLS do not propagate this way and need the same per-partition DO-block loop this build's own predecessor phases (2 and 4) already established.

Cross-Phase / Cross-Module Foreign Keys (agents)

Column	Target	Notes
`*.tenant_id` (all 39 tenant-scoped tables)	`platform.tenant`	NOT NULL. 8 tables are deliberately global/no-`tenant_id` (`tool_definition`, `tool_version`, `tool_version_module`, `toolset_definition`, `toolset_version`, `toolset_version_member`, `skill_definition`, `skill_version`, `skill_version_toolset`, `skill_version_required_duty`, `skill_version_module`, `agent_catalog_entry`, `agent_catalog_entry_required_tool`, `skill_certification`, `workload_class` — 15 total global tables; `agent_eval_suite` and `rollback_recipe` are the 2 mixed-scope tables, `tenant_id` nullable).
`tool_version.tool_definition_id`, `toolset_version.toolset_definition_id`, `skill_version.skill_definition_id`, `agent_eval_suite_version.agent_eval_suite_id`	intra-schema, own definition table	NOT NULL — the standard A2b version-row pattern
`tool_version_module.module_id`, `skill_version_module.module_id`	`platform.module_catalog`	NOT NULL
`skill_version_required_duty.permission_id`	`identity.permission`	NOT NULL
`agent_skill_assignment.agent_identity_id`, `tenant_agent_deployment.deployed_agent_identity_id`, `agent_task.agent_identity_id`, `agent_schedule.agent_identity_id`, `agent_trigger.agent_identity_id`, `agent_tool_grant.agent_identity_id`, `agent_eval_run.agent_identity_id`, `agent_performance_profile.agent_identity_id`, `agent_shadow_run.agent_identity_id`, `agent_autonomy_profile.agent_identity_id`, `skill_certification.agent_identity_id`	`identity.agent_identity`	NOT NULL except `skill_certification` (nullable — cert may pin only model+skill+toolset with no specific agent identity)
`skill_certification.model_version_id`, `decision_context_snapshot.model_version_id`	`ai.model_version`	nullable
`decision_context_snapshot.prompt_version_id`	`ai.prompt_version`	nullable
`agent_action.source_module_id`	`platform.module_catalog`	NOT NULL — BLOCKER4's replacement for v1's free-text `source_module`
`agent_task.module_id`, `agent_task.allowed_mutating_module_id`	`platform.module_catalog`	`module_id` NOT NULL; `allowed_mutating_module_id` nullable, claimed atomically by the saga-gate trigger
`agent_decision.agent_execution_id` + `.tenant_id` + `.agent_execution_created_at`	`ai.agent_execution (id, tenant_id, created_at)`	NOT NULL, composite (BLOCKER2 shadow-column shape — `agent_execution` is partitioned)
`agent_decision.decision_context_snapshot_id` + `.tenant_id` + `.decision_context_snapshot_created_at`	`agents.decision_context_snapshot (id, tenant_id, created_at)`	NOT NULL, composite
`agent_action.agent_execution_id` + `.tenant_id` + `.agent_execution_created_at`	`ai.agent_execution (id, tenant_id, created_at)`	NOT NULL, composite
`agent_action.agent_decision_id` + `.tenant_id` + `.agent_decision_created_at`	`agents.agent_decision (id, tenant_id, created_at)`	NOT NULL, composite — I1's own decision-granularity fix
`agent_action.approval_request_id` + `.tenant_id`	`approvals.approval_request (id, tenant_id)`	nullable, composite — the saga-gate exemption seam (BLOCKER4)
`agent_autonomy_profile.last_promotion_approval_request_id` + `.tenant_id`	`approvals.approval_request (id, tenant_id)`	nullable, composite — the promotion-blessing seam
`agent_incident.agent_execution_id` + `.tenant_id` + `.agent_execution_created_at`	`ai.agent_execution (id, tenant_id, created_at)`	NOT NULL, composite
`agent_incident.agent_action_id` + `.tenant_id`, `.rollback_execution_id` + `.tenant_id`	`agents.agent_action (id, tenant_id)`, `agents.rollback_execution (id, tenant_id)`	both nullable, composite
`rollback_execution.agent_action_id` + `.tenant_id`	`agents.agent_action (id, tenant_id)`	NOT NULL, composite
`rollback_execution.rollback_recipe_id`	`agents.rollback_recipe`	nullable, bare (recipe may be global — no tenant to compose against)
`agent_task.schedule_id`/`.trigger_id`/`.tenant_agent_deployment_id` + `.tenant_id`	`agents.agent_schedule`/`agent_trigger`/`tenant_agent_deployment (id, tenant_id)`	all 3 nullable, composite
`agent_thread.agent_task_id` + `.tenant_id`	`agents.agent_task (id, tenant_id)`	NOT NULL, composite
`agent_event_log.agent_task_id` + `.tenant_id`, `.agent_thread_id` + `.tenant_id`	`agents.agent_task (id, tenant_id)`, `agents.agent_thread (id, tenant_id)`	task NOT NULL, thread nullable, both composite
`agent_shadow_decision.shadow_run_id` + `.tenant_id`	`agents.agent_shadow_run (id, tenant_id)`	NOT NULL, composite
`decision_context_snapshot.skill_version_id`, `.toolset_version_id`	`agents.skill_version`, `agents.toolset_version`	both nullable
`decision_context_snapshot.experiment_assignment_id` + `.tenant_id`	`signals.experiment_assignment (id, tenant_id)`	nullable, composite — I1's own cross-reference fix, closing a real v2 prose/schema gap
`decision_context_manifest.decision_context_snapshot_id` + `.tenant_id` + `.decision_context_snapshot_created_at`	`agents.decision_context_snapshot (id, tenant_id, created_at)`	NOT NULL, composite
`decision_context_manifest.retention_policy_id` + `.tenant_id`	`agents.evidence_retention_policy (id, tenant_id)`	NOT NULL, composite
`decision_context_feature.feature_value_id` + `.tenant_id` + `.feature_value_recorded_at`	`signals.feature_value (id, tenant_id, recorded_at)`	NOT NULL, composite — TYPED direct link (BLOCKER6)
`decision_context_forecast.forecast_id` + `.tenant_id` + `.forecast_recorded_at`	`signals.forecast (id, tenant_id, recorded_at)`	NOT NULL, composite
`decision_context_metric.metric_version_id`	`semantics.metric_version`	NOT NULL
`decision_context_policy.ai_capacity_policy_id`	`platform.ai_capacity_policy`	NOT NULL
`decision_context_knowledge_source.document_chunk_id` + `.tenant_id`	`files.document_chunk (id, tenant_id)`	NOT NULL, composite — required a new `UNIQUE(id, tenant_id)` on `files.document_chunk`, added by this migration (Section 1)
`decision_context_{feature,forecast,metric,policy,knowledge_source}.decision_context_snapshot_id` + `.tenant_id` + `.decision_context_snapshot_created_at`	`agents.decision_context_snapshot (id, tenant_id, created_at)`	NOT NULL, composite, all 5 join tables
`ai.routing_policy.workload_class_id`	`agents.workload_class`	plain FK (workload_class is global, no tenant_id) — closes a Phase 2-disclosed forward-ref, this migration
`signals.outcome_observation.agent_action_id` + `.tenant_id`, `signals.outcome_authority.agent_action_id` + `.tenant_id`	`agents.agent_action (id, tenant_id)`	composite — closes 2 Phase 4-disclosed forward-refs, this migration (Section 10)
`ai.agent_execution.agent_task_id` + `.tenant_id`	`agents.agent_task (id, tenant_id)`	nullable, composite — new column, Section 7 of this migration
`ai.agent_execution.tool_version_id`	`agents.tool_version`	nullable — new column
`ai.agent_execution.approval_request_id` + `.tenant_id`	`approvals.approval_request (id, tenant_id)`	nullable, composite — new column

Tool / Skill Catalog (12 tables)

The A2b definition/version pattern applied uniformly across tools, toolsets, and skills — a stable identity row plus one or more immutable, content-hashed version rows, so certification/dispatch/replay can pin an exact behavior rather than "whatever the identity row currently says." All 12 tables in this group are global (no tenant_id) except agent_skill_assignment, the one tenant-scoped instantiation point ("this skill is active for this agent, for this tenant").

`agents.tool_definition` (7 cols)

The stable identity of a tool — what tool_catalog (v1, superseded) would have been, minus the certifiable payload, which now lives on tool_version.

Global, non-tenant-scoped catalog table. No RLS. No soft delete. updated_at has no maintaining trigger (a disclosed, low-severity gap shared by every A2b identity-row table in this module — see "Known gaps" below).

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tool_code`	text	NOT NULL	—	UNIQUE
`name`	text	NOT NULL	—
`description`	text	NOT NULL	—
`lifecycle_status`	text	NOT NULL	`'planned'`	CHECK IN (`planned`,`designed`,`in_build`,`active`,`deprecated`,`retired`)
`created_at`	timestamptz	NOT NULL	`now()`
`updated_at`	timestamptz	NOT NULL	`now()`	No maintaining trigger

CHECK constraints: chk_tool_definition_lifecycle_status.

Indexes: PK on id; UNIQUE on tool_code.

Grants: GRANT SELECT to authenticated (schema-default privilege); no INSERT/UPDATE/DELETE grant (platform/service-role managed).

`agents.tool_version` (14 cols)

The versioned, certifiable payload — input_schema/is_write/risk_tier (v1's own tool_catalog columns, folded in here since tool_catalog was never built).

Global, non-tenant-scoped. No RLS. No soft delete, no created_at/updated_at (matches every other version-row table's own convention — authorship is platform-only).

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tool_definition_id`	UUID	NOT NULL	—	FK → `tool_definition`
`version`	text	NOT NULL	—	UNIQUE per `tool_definition_id`
`content_hash`	text	NOT NULL	—
`risk_tier`	text	NOT NULL	`'low'`	CHECK IN (`low`,`medium`,`high`)
`is_write`	boolean	NOT NULL	`false`
`requires_approval`	boolean	NOT NULL	`false`
`input_schema`	jsonb	NOT NULL	—	JSON Schema, e.g. `{"type":"object","properties":{"po_id":{"type":"string","format":"uuid"}},"required":["po_id"]}` — validates params an `agent_execution` passes to invoke this tool version
`output_schema`	jsonb	nullable	—	Same convention, describes the return shape; NULL for tools with no structured return
`service_reference`	text	NOT NULL	—
`status`	text	NOT NULL	—	CHECK IN (`draft`,`published`,`deprecated`,`retired`)
`compatibility_state`	text	nullable	—	CHECK NULL OR IN (`backward`,`forward`,`bidirectional`,`breaking`,`deprecated`,`read_only_historical`,`unsupported_for_new_execution`,`unavailable_for_replay`)
`superseded_by_version_id`	UUID	nullable	—	Self-reference, no declared FK
`minimum_supported_reader_version`	text	nullable	—
`published_at`/`deprecated_at`/`retired_at`	timestamptz	nullable	—	Consistency-CHECK'd against `status`

CHECK constraints (4): chk_tool_version_status; chk_tool_version_risk_tier; chk_tool_version_compatibility_state; chk_tool_version_status_timestamp_consistency (NULL-safety co-requirement: status NOT NULL is what keeps every branch from evaluating to NULL); chk_tool_version_high_risk_write_requires_approval — NOT (is_write AND risk_tier='high') OR requires_approval (restates v1's chk_tool_catalog_high_risk_write_requires_approval on the actual certifiable payload row).

Indexes: PK on id; UNIQUE (tool_definition_id,version); UNIQUE on superseded_by_version_id WHERE NOT NULL; plain index on tool_definition_id.

Grants: GRANT SELECT to authenticated.

`agents.tool_version_module` (2 cols, join table)

Version-scoped module applicability — REPLACES v1/A1's identity-scoped tool_module (BLOCKER5: a tool's module applicability can legitimately change between versions; referencing the identity row would retroactively alter what every historical version appeared to be).

Global, no RLS, no soft delete. Composite PK, no surrogate id.

Column	Type	Nullable	Default	Notes
`tool_version_id`	UUID	NOT NULL	—	FK → `tool_version`, part of PK
`module_id`	UUID	NOT NULL	—	FK → `platform.module_catalog`, part of PK

Indexes: composite PK on (tool_version_id,module_id).

`agents.toolset_definition` (6 cols)

Same A2b identity/version pattern, for reusable toolset objects (Option A — a real, browsable/auditable toolset row, chosen over a deterministic hash since toolsets are reused across many skill certifications).

Global, no RLS, no soft delete. No maintaining trigger on updated_at.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`toolset_code`	text	NOT NULL	—	UNIQUE
`name`	text	NOT NULL	—
`description`	text	NOT NULL	—
`created_at`	timestamptz	NOT NULL	`now()`
`updated_at`	timestamptz	NOT NULL	`now()`	No maintaining trigger

Indexes: PK on id; UNIQUE on toolset_code.

Grants: GRANT SELECT to authenticated.

`agents.toolset_version` (9 cols)

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`toolset_definition_id`	UUID	NOT NULL	—	FK → `toolset_definition`
`version`	text	NOT NULL	—	UNIQUE per `toolset_definition_id`
`content_hash`	text	NOT NULL	—
`status`	text	NOT NULL	—	CHECK IN (`draft`,`published`,`deprecated`,`retired`)
`compatibility_state`	text	nullable	—	Same 8-value CHECK as `tool_version`
`superseded_by_version_id`	UUID	nullable	—	Self-reference, no declared FK
`published_at`/`deprecated_at`/`retired_at`	timestamptz	nullable	—	Consistency-CHECK'd against `status`

CHECK constraints: chk_toolset_version_status; chk_toolset_version_status_timestamp_consistency.

Indexes: PK on id; UNIQUE (toolset_definition_id,version); UNIQUE on superseded_by_version_id WHERE NOT NULL.

Grants: GRANT SELECT to authenticated.

`agents.toolset_version_member` (2 cols, join table)

Which tool versions belong to a toolset version.

Column	Type	Nullable	Default	Notes
`toolset_version_id`	UUID	NOT NULL	—	FK → `toolset_version`, part of PK
`tool_version_id`	UUID	NOT NULL	—	FK → `tool_version`, part of PK

Indexes: composite PK on (toolset_version_id,tool_version_id).

`agents.skill_definition` (8 cols)

SUPERSEDES identity.agent_skill (a flat, unversioned, 9-column global catalog with zero live rows). Certification, dispatch, and replay all need to pin an exact skill behavior, per A2b.

Global, no RLS, no soft delete. No maintaining trigger on updated_at.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`skill_code`	text	NOT NULL	—	UNIQUE
`name`	text	NOT NULL	—
`description`	text	NOT NULL	—
`publisher`	text	NOT NULL	`'vrida'`	CHECK IN (`vrida`,`partner`)
`lifecycle_status`	text	NOT NULL	`'planned'`	CHECK IN (`planned`,`designed`,`in_build`,`active`,`deprecated`,`retired`)
`created_at`	timestamptz	NOT NULL	`now()`
`updated_at`	timestamptz	NOT NULL	`now()`	No maintaining trigger

CHECK constraints: chk_skill_definition_publisher; chk_skill_definition_lifecycle_status.

Indexes: PK on id; UNIQUE on skill_code.

Grants: GRANT SELECT to authenticated.

`agents.skill_version` (11 cols)

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`skill_definition_id`	UUID	NOT NULL	—	FK → `skill_definition`
`version`	text	NOT NULL	—	UNIQUE per `skill_definition_id`
`content_hash`	text	NOT NULL	—
`status`	text	NOT NULL	—	CHECK IN (`draft`,`published`,`deprecated`,`retired`)
`compatibility_state`	text	nullable	—	Same 8-value CHECK as `tool_version`
`superseded_by_version_id`	UUID	nullable	—	Self-reference, no declared FK
`minimum_supported_reader_version`	text	nullable	—
`published_at`/`deprecated_at`/`retired_at`	timestamptz	nullable	—	Consistency-CHECK'd against `status`

CHECK constraints: chk_skill_version_status; chk_skill_version_compatibility_state; chk_skill_version_status_timestamp_consistency.

Indexes: PK on id; UNIQUE (skill_definition_id,version); UNIQUE on superseded_by_version_id WHERE NOT NULL; plain index on skill_definition_id.

Triggers: trg_decision_context_snapshot_reject_retired (on decision_context_snapshot, not this table itself) reads this table's status — see the Decision-Context group and T1/GUARD-8 below.

Grants: GRANT SELECT to authenticated.

`agents.skill_version_toolset` (2 cols, join table)

Which toolset version a skill version requires.

Column	Type	Nullable	Default	Notes
`skill_version_id`	UUID	NOT NULL	—	FK → `skill_version`, part of PK
`toolset_version_id`	UUID	NOT NULL	—	FK → `toolset_version`, part of PK

Indexes: composite PK on (skill_version_id,toolset_version_id).

`agents.skill_version_required_duty` (3 cols, join table)

Which identity.agent_duty_grant-shaped permission + minimum authority level a skill version requires to activate. minimum_authority_level's vocabulary deliberately matches the real, live identity.agent_duty_grant.authority_level CHECK exactly (draft_only/needs_approval/may_act_alone) — v3's own independent review found the first design draft invented a different vocabulary sharing only one value with reality.

Column	Type	Nullable	Default	Notes
`skill_version_id`	UUID	NOT NULL	—	FK → `skill_version`, part of PK
`permission_id`	UUID	NOT NULL	—	FK → `identity.permission`, part of PK
`minimum_authority_level`	text	NOT NULL	—	CHECK IN (`draft_only`,`needs_approval`,`may_act_alone`)

CHECK constraints: chk_skill_version_required_duty_authority_level.

Indexes: composite PK on (skill_version_id,permission_id).

Read by: validate_skill_activation() (GUARD 6, Point 2) — see Design Patterns Summary below.

`agents.skill_version_module` (2 cols, join table)

Version-scoped module applicability for skills — same BLOCKER5 rationale as tool_version_module, REPLACES v1/A1's identity-scoped skill_module.

Column	Type	Nullable	Default	Notes
`skill_version_id`	UUID	NOT NULL	—	FK → `skill_version`, part of PK
`module_id`	UUID	NOT NULL	—	FK → `platform.module_catalog`, part of PK

Indexes: composite PK on (skill_version_id,module_id).

`agents.agent_skill_assignment` (10 cols) — MOVED here from `identity` (I5)

The one tenant-scoped instantiation point: "this skill is active for this agent, for this tenant." A genuine table relocation — the 3rd instance of this pattern codebase-wide (after approvals out of admin, and receiving out of purchasing). Resolves a real foundation-layer dependency-direction cycle: identity.agent_skill_assignment → agents.skill_version would be an outbound identity→agents edge, while agents already has the deep, unavoidable agents→identity edge (agent_identity, actor attribution) — both directions simultaneously would violate SCHEMA_CONVENTIONS.md's one-way foundation-layer dependency rule. skill_version_id is now intra-schema, no cycle. Zero live rows existed in identity.agent_skill_assignment at build time (confirmed live) — Phase 6's identity reopen drops the old table outright, no backfill needed (this table is built here, empty, as part of agents' own native table set from day one — see "Transitional Drizzle barrel collision" below).

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_skill_assignment_tenant_isolation. Soft delete (deleted_at). No automation_source/review_status pack (this table's own trigger, validate_skill_activation(), is the enforcement mechanism, not the standard autonomy columns).

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_identity_id`	UUID	NOT NULL	—	FK → `identity.agent_identity`
`skill_version_id`	UUID	NOT NULL	—	FK → `agents.skill_version` (intra-schema — the whole point of the move)
`assigned_by`	text	NOT NULL	—	CHECK IN (`vrida_seed`,`provisioning`,`admin_override`)
`is_enabled`	boolean	NOT NULL	`true`	Toggling `true` (INSERT or UPDATE) fires GUARD 6
`config_override`	jsonb	nullable	—	`{"max_retries": 5}` — tenant-specific overrides layered on the skill_version's own defaults for this one assignment; NULL = use defaults as-is
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`
`deleted_at`	timestamptz	nullable	—	Soft delete

CHECK constraints: chk_agent_skill_assignment_assigned_by.

Indexes: PK on id; plain index on tenant_id; UNIQUE (agent_identity_id,skill_version_id) WHERE deleted_at IS NULL.

Triggers: trg_agent_skill_assignment_validate_activation (BEFORE INSERT OR UPDATE OF is_enabled) — agents.validate_skill_activation() — GUARD 6, see Design Patterns Summary below.

Grants: standard tenant-scoped RLS-gated table (SELECT/INSERT/UPDATE/DELETE all via RLS, no column-level restriction).

Marketplace + Deployment (4 tables)

`agents.agent_catalog_entry` (14 cols) — v1 Gap 11

Global (Vrida/partner-authored) marketplace listing a tenant can deploy from.

Global, no RLS. Soft delete (deleted_at). No maintaining trigger on updated_at.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`name`	text	NOT NULL	—
`description`	text	NOT NULL	—
`domain`	text	nullable	—	Free text
`use_case`	text	nullable	—
`publisher`	text	NOT NULL	—	CHECK IN (`vrida`,`partner`)
`version`	text	NOT NULL	—
`status`	text	NOT NULL	`'active'`	CHECK IN (`active`,`deprecated`,`beta`)
`pricing_model`	text	NOT NULL	—	CHECK IN (`credits_per_task`,`subscription`,`hybrid`)
`estimated_credits_per_task`	integer	nullable	—
`min_autonomy_mode`	text	NOT NULL	—	CHECK IN (`draft_only`,`needs_approval`,`may_act_alone`)
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`
`deleted_at`	timestamptz	nullable	—	Soft delete

CHECK constraints (4): chk_agent_catalog_entry_publisher; _status; _pricing_model; _min_autonomy_mode.

Indexes: PK on id.

Grants: GRANT SELECT to authenticated.

`agents.agent_catalog_entry_required_tool` (4 cols, join table)

A real join table, not a JSONB array (this codebase's own strong preference). tool_version_id retargets to tool_version.id (tool_catalog superseded).

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`catalog_entry_id`	UUID	NOT NULL	—	FK → `agent_catalog_entry`
`tool_version_id`	UUID	NOT NULL	—	FK → `tool_version`
`created_at`	timestamptz	NOT NULL	`now()`

Indexes: PK on id; UNIQUE (catalog_entry_id,tool_version_id).

`agents.tenant_agent_deployment` (11 cols) — v1 Gap 11

A tenant "employing" an agent from the catalog. Cost tracking rides the existing platform.ai_credit_transaction (ai_call_ref free-text correlation) — no parallel ledger. UNIQUE(id, tenant_id) is the prerequisite for agent_task's own composite FK.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive tenant_agent_deployment_tenant_isolation. No soft delete.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`catalog_entry_id`	UUID	NOT NULL	—	FK → `agent_catalog_entry`
`deployed_agent_identity_id`	UUID	NOT NULL	—	FK → `identity.agent_identity`
`status`	text	NOT NULL	`'trial'`	CHECK IN (`trial`,`active`,`paused`,`terminated`)
`spend_cap_cents`	bigint	nullable	—
`deployed_at`	timestamptz	NOT NULL	`now()`
`deployed_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`terminated_at`	timestamptz	nullable	—
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`

CHECK constraints: chk_tenant_agent_deployment_status.

Indexes: PK on id; plain index on tenant_id; UNIQUE (tenant_id,deployed_agent_identity_id); UNIQUE (id,tenant_id) — the agent_task composite-FK prerequisite.

`agents.agent_tool_grant` (14 cols) — v1 Gap 7

Per-agent, per-tool-version grant with optional spend cap and rate limit. tool_id retargets to tool_version.id. Unique index deliberately excludes every other nullable column (identity.agent_duty_grant's own DR-31 lesson) — (agent_identity_id, tool_version_id) WHERE status='active' only.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_tool_grant_tenant_isolation. No soft delete.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_identity_id`	UUID	NOT NULL	—	FK → `identity.agent_identity`
`tool_version_id`	UUID	NOT NULL	—	FK → `agents.tool_version`
`max_spend_cents`	bigint	nullable	—
`rate_limit_per_period`	integer	nullable	—	Paired with the next column
`rate_limit_period_minutes`	integer	nullable	—
`status`	text	NOT NULL	`'active'`	CHECK IN (`active`,`suspended`,`revoked`) — read by `check_agent_execution_not_blocked()`, GUARD 2
`granted_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`granted_at`	timestamptz	NOT NULL	`now()`
`revoked_at`	timestamptz	nullable	—
`revoked_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`

CHECK constraints: chk_agent_tool_grant_status; chk_agent_tool_grant_rate_limit_together — (rate_limit_per_period IS NULL) = (rate_limit_period_minutes IS NULL), both-or-neither.

Indexes: PK on id; plain index on tenant_id; partial UNIQUE (agent_identity_id,tool_version_id) WHERE status='active'.

Task Orchestration (5 tables)

`agents.agent_schedule` (20 cols) — v1 Gap 1

Cron-like task originator, defined ahead of agent_task so the latter's composite FK can reference it directly.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_schedule_tenant_isolation. Soft delete (deleted_at). Full autonomy pack (created_by_actor_id/automation_source/review seam/decision_provenance).

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_identity_id`	UUID	NOT NULL	—	FK → `identity.agent_identity`
`name`	text	NOT NULL	—
`cron_expression`	text	NOT NULL	—
`timezone`	text	NOT NULL	`'UTC'`
`is_active`	boolean	NOT NULL	`true`
`task_template`	jsonb	NOT NULL	—	`{"goal_description":"...","module_id":"<uuid>","risk_tier":"low","autonomy_mode":"needs_approval","max_steps":20,"max_cost_cents":500}` — stamped onto every task this schedule creates
`last_run_at`/`next_run_at`	timestamptz	nullable	—
`created_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`automation_source`	text	NOT NULL	`'human'`	CHECK IN (`human`,`agent`,`system`,`seed`)
`review_status`	text	NOT NULL	`'not_required'`	CHECK IN (`not_required`,`pending`,`approved`,`rejected`)
`review_reason`	text	nullable	—
`reviewed_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`reviewed_at`	timestamptz	nullable	—
`decision_provenance`	jsonb	nullable	—	`{"triggered_by":"agent_decision","agent_decision_id":"<uuid>","reasoning_summary":"text"}` — traces WHY an agent-authored row was auto-created; NULL for human-created rows
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`
`deleted_at`	timestamptz	nullable	—	Soft delete

CHECK constraints: chk_agent_schedule_automation_source; chk_agent_schedule_review_status.

Indexes: PK on id; plain index on tenant_id; UNIQUE (tenant_id,name) WHERE deleted_at IS NULL; UNIQUE (id,tenant_id) — the agent_task composite-FK prerequisite.

`agents.agent_trigger` (19 cols) — v1 Gap 1

Event-based task originator. aggregate_type/event_type deliberately match platform.outbox's own column names (a reuse-first finding) — a listener service (Part D, unbuilt) matches these against delivered outbox events; no schema-level FK (outbox is a different concern, not a joinable target at the DB layer).

Same tenant-scoping/RLS/soft-delete/autonomy-pack shape as agent_schedule.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_identity_id`	UUID	NOT NULL	—	FK → `identity.agent_identity`
`name`	text	NOT NULL	—
`aggregate_type`	text	NOT NULL	—	Matches `platform.outbox.aggregate_type` naming
`event_type`	text	NOT NULL	—	Matches `platform.outbox.event_type` naming
`condition`	jsonb	nullable	—	`{"field":"status","op":"eq","value":"overdue"}` or `{"all":[{...},...]}`; NULL fires on every matching aggregate/event with no extra filter
`task_template`	jsonb	NOT NULL	—	Same shape as `agent_schedule.task_template`
`is_active`	boolean	NOT NULL	`true`
`created_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`automation_source`	text	NOT NULL	`'human'`	CHECK IN (`human`,`agent`,`system`,`seed`)
`review_status`	text	NOT NULL	`'not_required'`	CHECK IN (`not_required`,`pending`,`approved`,`rejected`)
`review_reason`	text	nullable	—
`reviewed_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`reviewed_at`	timestamptz	nullable	—
`decision_provenance`	jsonb	nullable	—	Same shape as `agent_schedule`'s own
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`
`deleted_at`	timestamptz	nullable	—	Soft delete

CHECK constraints: chk_agent_trigger_automation_source; chk_agent_trigger_review_status.

Indexes: PK on id; plain index on tenant_id; composite index (tenant_id,aggregate_type,event_type) WHERE is_active — the listener's own match path; UNIQUE (tenant_id,name) WHERE deleted_at IS NULL; UNIQUE (id,tenant_id).

`agents.agent_task` (34 cols) — v1 Gap 1, amended A1/A5/E3/BLOCKER4

The unit of work, and the single largest table in this module. domain (v1's closed CHECK enum) is REPLACED by module_id (A1 — a real FK into platform.module_catalog, not a second enum).

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_task_tenant_isolation. No soft delete (a task reaches a terminal status instead). Full autonomy pack.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_identity_id`	UUID	NOT NULL	—	FK → `identity.agent_identity`
`principal_actor_id`	UUID	nullable	—	FK → `identity.actor` — the human/system on whose behalf this task runs
`schedule_id`	UUID	nullable	—	Composite FK → `agent_schedule (id, tenant_id)`
`trigger_id`	UUID	nullable	—	Composite FK → `agent_trigger (id, tenant_id)`
`tenant_agent_deployment_id`	UUID	nullable	—	Composite FK → `tenant_agent_deployment (id, tenant_id)`
`goal_description`	text	NOT NULL	—
`module_id`	UUID	NOT NULL	—	FK → `platform.module_catalog` — replaces v1's `domain` CHECK enum (A1)
`allowed_mutating_module_id`	UUID	nullable	—	FK → `platform.module_catalog` — claimed atomically by the FIRST autonomous `agent_action` write for this task (BLOCKER4 saga gate, GUARD 4)
`risk_tier`	text	NOT NULL	`'low'`	CHECK IN (`low`,`medium`,`high`)
`autonomy_mode`	text	NOT NULL	—	CHECK IN (`draft_only`,`needs_approval`,`may_act_alone`)
`status`	text	NOT NULL	`'pending'`	CHECK IN (`pending`,`in_progress`,`awaiting_human`,`completed`,`failed`,`canceled`)
`max_steps`	integer	nullable	—	Runaway-ceiling bound, BLOCK not flag
`max_cost_cents`	bigint	nullable	—	Runaway-ceiling bound
`step_count`	integer	NOT NULL	`0`	Atomically incremented by GUARD 3
`cost_cents_accrued`	bigint	NOT NULL	`0`	Atomically incremented by GUARD 3
`lease_owner`	text	nullable	—	E3 — the worker holding the claim
`lease_expires_at`	timestamptz	nullable	—	E3
`fencing_token`	bigint	NOT NULL	`0`	E3 — incremented on claim, NOT on lease renewal (the self-fencing bug v2's own independent review caught, CC-2); also bumped by kill-switch propagation (GUARD 1)
`claim_attempts`	integer	NOT NULL	`0`	E3
`max_age`	integer	nullable	—	E3, disclosed judgment call: integer seconds, service-enforced staleness, not DB-enforced
`cancellation_requested_at`	timestamptz	nullable	—	Set by the structural kill path (GUARD 1's propagation trigger) and by explicit cancellation; a running worker is expected (runtime protocol, not DB-enforced) to poll it
`created_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`automation_source`	text	NOT NULL	`'human'`	CHECK IN (`human`,`agent`,`system`,`seed`)
`review_status`	text	NOT NULL	`'not_required'`	CHECK IN (`not_required`,`pending`,`approved`,`rejected`)
`review_reason`	text	nullable	—
`reviewed_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`reviewed_at`	timestamptz	nullable	—
`decision_provenance`	jsonb	nullable	—	Same shape as `agent_schedule`'s own
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`
`started_at`/`completed_at`	timestamptz	nullable	—

CHECK constraints (8): chk_agent_task_risk_tier; chk_agent_task_autonomy_mode; chk_agent_task_status; chk_agent_task_max_steps_bound — max_steps IS NULL OR step_count <= max_steps; chk_agent_task_max_cost_bound — same shape for cost; chk_agent_task_completed_requires_timestamp — status NOT IN ('completed','failed','canceled') OR completed_at IS NOT NULL; chk_agent_task_reviewer_not_creator — NULL-safe distinctness; chk_agent_task_automation_source; chk_agent_task_review_status.

Indexes: PK on id; plain indexes on tenant_id, agent_identity_id, status; UNIQUE (id,tenant_id) — the composite-identity shape agent_thread/agent_event_log/ai.agent_execution all resolve against.

Triggers: trg_agent_task_check_not_blocked (BEFORE INSERT OR UPDATE OF status) — agents.check_agent_task_not_blocked() — GUARD 2. Fires on the claim UPDATE (a status transition), not just INSERT — the A5 correction.

Claim/lease mechanics (E3, service-layer convention, not a stored procedure): atomic claim is UPDATE agents.agent_task SET status='in_progress', lease_owner=:worker, lease_expires_at=now()+:ttl, fencing_token=fencing_token+1, claim_attempts=claim_attempts+1 WHERE id=:id AND status='pending' AND (lease_expires_at IS NULL OR lease_expires_at < now()) RETURNING fencing_token — a deliberately DIFFERENT statement from lease RENEWAL, which extends lease_expires_at only and does NOT increment fencing_token.

`agents.agent_thread` (9 cols) — v1

Durable checkpoint state. No uniqueness on agent_task_id (a task may be retried across multiple thread instances). UNIQUE(id, tenant_id) is the prerequisite for agent_event_log's own composite FK — a BLOCKER an independent verification pass caught in v1's own design, restated here as built-in from the start.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_thread_tenant_isolation. No soft delete.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_task_id`	UUID	NOT NULL	—	Composite FK → `agent_task (id, tenant_id)`
`engine`	text	NOT NULL	—	CHECK IN (`temporal`,`langgraph`,`internal`)
`state_blob`	jsonb	NOT NULL	`'{}'`	Opaque, engine-specific — e.g. Temporal: `{"workflow_id":"...","run_id":"...","history_length":42}`; LangGraph: `{"graph_state":{...},"next_node":"..."}`. Not interpreted by this schema
`checkpoint_index`	integer	NOT NULL	`0`	The ordering key
`last_checkpoint_at`	timestamptz	nullable	—
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`

CHECK constraints: chk_agent_thread_engine.

Indexes: PK on id; composite index (agent_task_id,checkpoint_index); UNIQUE (id,tenant_id).

`agents.agent_event_log` (7 cols) — v1, append-only

Event-sourced state transitions. platform.uuid_generate_v7() PK per the hot-ledger UUIDv7 convention. Deliberately untouched by v2/v3 (confirmed in BLOCKER 2's own cascade table: "no change needed").

Tenant-scoped, append-only. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_event_log_tenant_isolation. REVOKE UPDATE, DELETE FROM authenticated; GRANT SELECT, INSERT.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`platform.uuid_generate_v7()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_task_id`	UUID	NOT NULL	—	Composite FK → `agent_task (id, tenant_id)`
`agent_thread_id`	UUID	nullable	—	Composite FK → `agent_thread (id, tenant_id)`
`event_type`	text	NOT NULL	—	CHECK IN (14 values: `task_created`,`step_started`,`step_completed`,`step_failed`,`escalated`,`approval_requested`,`approval_resolved`,`checkpoint_saved`,`resumed`,`paused`,`completed`,`failed`,`canceled`,`rolled_back`)
`payload`	jsonb	nullable	—	Shape keyed by `event_type`, e.g. `status_changed` → `{"from":"pending","to":"in_progress"}`; `error` → `{"message":"...","stack":"..."}`
`created_at`	timestamptz	NOT NULL	`now()`

CHECK constraints: chk_agent_event_log_event_type.

Indexes: composite index (agent_task_id,created_at) — no separate PK index needed since id is already PK.

Triggers: trg_agent_event_log_append_only (BEFORE UPDATE OR DELETE) — platform.reject_append_only_mutation() (shared, reused).

Grants: REVOKE UPDATE, DELETE; GRANT SELECT, INSERT.

Eval (5 tables)

`agents.agent_eval_suite` (9 cols) — v1 Gap 2, versioned per A2b

v1's own agent_eval_suite was unversioned; amended here since a suite's scenarios change and certification must pin which version certified. scenario_definition (the payload) lives on the VERSION row, not here — this identity row is now pure catalog metadata. tenant_id nullable — a suite can be Vrida-authored/global or tenant-custom (mirrors ai.ai_request's own nullable-tenant precedent).

Mixed-scope. RLS uses 2 separate policies — agent_eval_suite_select (read: own tenant OR global) and agent_eval_suite_write (write: own tenant only) — NOT a single blanket policy, since a single tenant_id = current_setting(...) policy would make every global row invisible. Soft delete (deleted_at).

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	nullable	—	FK → `platform.tenant`; NULL = global
`agent_type_id`	UUID	nullable	—	FK → `identity.agent_type_catalog`
`name`	text	NOT NULL	—
`description`	text	nullable	—
`is_active`	boolean	NOT NULL	`true`
`created_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`
`deleted_at`	timestamptz	nullable	—	Soft delete

Indexes: PK on id; UNIQUE (tenant_id,agent_type_id,name) WHERE tenant_id IS NOT NULL AND deleted_at IS NULL; UNIQUE (agent_type_id,name) WHERE tenant_id IS NULL AND deleted_at IS NULL.

RLS policies: agent_eval_suite_select (SELECT, tenant_id = current_setting(...) OR tenant_id IS NULL); agent_eval_suite_write (ALL, own-tenant only).

`agents.agent_eval_suite_version` (9 cols)

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`agent_eval_suite_id`	UUID	NOT NULL	—	FK → `agent_eval_suite`
`version`	text	NOT NULL	—	UNIQUE per `agent_eval_suite_id`
`content_hash`	text	NOT NULL	—
`scenario_definition`	jsonb	NOT NULL	—	`{"scenarios":[{"input":{...},"expected_output":{...},"tolerance":0.05},...]}` — the suite's actual test cases
`status`	text	NOT NULL	—	CHECK IN (`draft`,`published`,`deprecated`,`retired`)
`published_at`/`deprecated_at`/`retired_at`	timestamptz	nullable	—	Consistency-CHECK'd against `status`

CHECK constraints: chk_agent_eval_suite_version_status; chk_agent_eval_suite_version_status_timestamp_consistency.

Indexes: PK on id; UNIQUE (agent_eval_suite_id,version); plain index on agent_eval_suite_id.

Read by: skill_certification.evaluation_suite_version_id — pins exactly which eval-suite configuration a certification ran against.

`agents.agent_eval_run` (14 cols) — v1

eval_suite_id RETARGETED to agent_eval_suite_version.id (disclosed judgment call: neither v2 nor v3 explicitly addresses this column now that the suite payload moved to a version row — the same version-pinning A3 certification already does via evaluation_suite_version_id, applied here for consistency). Stays a bare FK — a composite FK cannot match a NULL-tenant global suite version's own parent.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_eval_run_tenant_isolation. No soft delete.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_identity_id`	UUID	NOT NULL	—	FK → `identity.agent_identity`
`eval_suite_version_id`	UUID	NOT NULL	—	Bare FK → `agent_eval_suite_version`
`eval_type`	text	NOT NULL	—	CHECK IN (`pre_deploy`,`periodic`,`regression`)
`environment`	text	NOT NULL	—	CHECK IN (`staging`,`prod_shadow`)
`status`	text	NOT NULL	`'pending'`	CHECK IN (`pending`,`running`,`completed`,`failed`)
`tool_selection_accuracy`	numeric(5,4)	nullable	—	CHECK NULL OR 0–1
`goal_completion_rate`	numeric(5,4)	nullable	—	CHECK NULL OR 0–1
`trajectory_accuracy`	numeric(5,4)	nullable	—	CHECK NULL OR 0–1
`human_input_rate`	numeric(5,4)	nullable	—	CHECK NULL OR 0–1
`started_at`/`completed_at`	timestamptz	nullable	—
`created_at`	timestamptz	NOT NULL	`now()`

CHECK constraints (7): chk_agent_eval_run_eval_type; _environment; _status; _tool_selection_accuracy; _goal_completion_rate; _trajectory_accuracy; _human_input_rate.

Indexes: PK on id; plain index on tenant_id; UNIQUE (id,tenant_id).

`agents.agent_eval_case` (9 cols)

Individual pass/fail cases within an eval run.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_eval_case_tenant_isolation. No soft delete. id default platform.uuid_generate_v7() (hot-ledger convention — high write volume, one row per scenario per run).

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`platform.uuid_generate_v7()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`eval_run_id`	UUID	NOT NULL	—	Bare FK (no declared FK constraint in the Drizzle source — see note)
`case_reference`	text	NOT NULL	—
`passed`	boolean	NOT NULL	—
`actual_trajectory`	jsonb	nullable	—	`[{"step":1,"action":"create_po","params":{...}},...]` — the ordered sequence of actions the agent actually took
`expected_trajectory`	jsonb	nullable	—	Same shape, the expected sequence
`notes`	text	nullable	—
`created_at`	timestamptz	NOT NULL	`now()`

Indexes: PK on id; plain index on eval_run_id.

`agents.agent_performance_profile` (14 cols)

Rolling aggregate reliability metrics per agent per domain, feeding autonomy-promotion decisions.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_performance_profile_tenant_isolation. No soft delete.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_identity_id`	UUID	NOT NULL	—	FK → `identity.agent_identity`
`domain`	text	nullable	—	Free text, NOT retargeted to `module_id` — A1's own consumer-FK migration is explicitly scoped to exactly 3 columns (`agent_task.domain`, `tool_definition.domain`, `skill_definition.domain`) and no others; this table faithfully ports v1's literal spec
`reliability_score`	numeric(5,4)	nullable	—
`avg_tool_selection_accuracy`	numeric(5,4)	nullable	—
`avg_goal_completion_rate`	numeric(5,4)	nullable	—
`avg_trajectory_accuracy`	numeric(5,4)	nullable	—
`avg_human_input_rate`	numeric(5,4)	nullable	—
`recommended_autonomy_mode`	text	nullable	—	CHECK NULL OR IN (`draft_only`,`needs_approval`,`may_act_alone`)
`sample_size`	integer	NOT NULL	`0`
`last_computed_at`	timestamptz	nullable	—
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`

CHECK constraints: chk_agent_performance_profile_recommended_autonomy_mode.

Indexes: PK on id; UNIQUE (tenant_id,agent_identity_id,domain).

Shadow-Mode (3 tables)

`agents.agent_shadow_run` (10 cols) — v1 Gap 3

Untouched by v2/v3, ported faithfully. Prod-shadow evaluation window during which an agent's decisions are logged but not acted on.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_shadow_run_tenant_isolation. No soft delete.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_identity_id`	UUID	NOT NULL	—	FK → `identity.agent_identity`
`workflow_type`	text	NOT NULL	—
`risk_tier`	text	NOT NULL	`'low'`	CHECK IN (`low`,`medium`,`high`)
`status`	text	NOT NULL	`'active'`	CHECK IN (`active`,`completed`,`canceled`)
`started_at`	timestamptz	NOT NULL	`now()`
`ended_at`	timestamptz	nullable	—
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`

CHECK constraints: chk_agent_shadow_run_risk_tier; chk_agent_shadow_run_status.

Indexes: PK on id; plain index on tenant_id; UNIQUE (id,tenant_id).

`agents.agent_shadow_decision` (9 cols)

Side-by-side comparison of what the agent WOULD have done vs. what the human actually did.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_shadow_decision_tenant_isolation. No soft delete. id default platform.uuid_generate_v7().

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`platform.uuid_generate_v7()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`shadow_run_id`	UUID	NOT NULL	—	Composite FK → `agent_shadow_run (id, tenant_id)`
`context_reference`	text	NOT NULL	—
`agent_recommendation`	jsonb	NOT NULL	—	`{"action_type":"create_po","confidence":0.87,"reasoning":"..."}` — what the agent would have done
`human_action`	jsonb	nullable	—	Same shape, what the human actually did; NULL until the human acts
`agreement_flag`	boolean	nullable	—
`disagreement_reason`	text	nullable	—
`created_at`	timestamptz	NOT NULL	`now()`

CHECK constraints: chk_agent_shadow_decision_agreement_requires_human_action — agreement_flag IS NULL OR human_action IS NOT NULL.

Indexes: plain index on shadow_run_id.

`agents.agent_autonomy_profile` (12 cols) — v1 Gap 4

current_mode is ADVISORY/TRACKING ONLY, disclosed honestly per v1's own independent review finding: the intended ENFORCED authority source is identity.agent_duty_grant.authority_level, which has no REVOKE/trigger today (confirmed live) — closing that gap requires its own future identity reopen, logged to OPEN_ITEMS, not bundled into this already-large build. last_promotion_approval_request_id is the promotion-blessing seam.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_autonomy_profile_tenant_isolation. No soft delete.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_identity_id`	UUID	NOT NULL	—	FK → `identity.agent_identity`
`domain`	text	NOT NULL	—
`current_mode`	text	NOT NULL	—	CHECK IN (`draft_only`,`needs_approval`,`may_act_alone`) — ADVISORY ONLY, disclosed
`min_eval_score`	numeric(5,4)	nullable	—	CHECK NULL OR 0–1
`rolling_window_days`	integer	NOT NULL	`30`
`rolling_window_min_samples`	integer	NOT NULL	`10`
`last_promoted_at`	timestamptz	nullable	—
`last_promotion_approval_request_id`	UUID	nullable	—	Composite FK → `approvals.approval_request (id, tenant_id)` — the promotion-blessing seam, requires `chk_approval_request_source_module` widened to add `'agents'` (Section 2 of this migration)
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`

CHECK constraints: chk_agent_autonomy_profile_current_mode; chk_agent_autonomy_profile_min_eval_score.

Indexes: PK on id; UNIQUE (tenant_id,agent_identity_id,domain).

Decision-Context Evidence Envelope (8 tables)

The "why did the agent do that" reconstruction layer. decision_context_snapshot is the immutable evidence root; decision_context_manifest is its durable off-DB archive pointer; the 5 decision_context_* join tables are TYPED direct-links into the specific signals/semantics/files rows that fed the decision (BLOCKER6 — replaces v2's own generic referenced_id/referenced_table_tag untyped pointer).

`agents.evidence_retention_policy` (7 cols) — A4b

FKs into the REAL retention policy, not a bucket-lifecycle default. The bucket's own lifecycle rule must be provisioned to READ this policy (deployment infra, Part D) — the schema seam (this FK) is what this design can actually guarantee.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive evidence_retention_policy_tenant_isolation. No soft delete.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`min_retention_days`	integer	NOT NULL	—
`legal_hold`	boolean	NOT NULL	`false`
`applies_to`	text	NOT NULL	—	CHECK IN (`decision_context`,`shadow_decision`,`outcome_observation`)
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`

CHECK constraints: chk_evidence_retention_policy_applies_to.

Indexes: PK on id; UNIQUE (id,tenant_id) — the decision_context_manifest composite-FK prerequisite.

`agents.decision_context_snapshot` (16 cols) — A4, PARTITIONED

Immutable, append-only. The evidence root every downstream join table points back to. experiment_assignment_id is I1's own cross-reference fix — v2's own prose promised "decision_context_snapshot references the assignment" but never actually added the column; added here. causal_basis_locked_at is NOT stored here (it lives on signals.experiment_assignment itself) — this table's own AFTER INSERT trigger CALLS signals.lock_experiment_causal_basis() when experiment_assignment_id is set (BLOCKER 3's own design). resolved_goal_profile (I3) mirrors resolved_effective_policy's own discipline — the actual resolved goal/constraint values in force at decision time, snapshotted, not a live pointer.

Tenant-scoped, PARTITIONED BY RANGE(created_at), monthly, 14 partitions. tenant_id NOT NULL FK → platform.tenant. No standalone PRIMARY KEY — PRIMARY KEY (id, created_at) + UNIQUE(id, tenant_id, created_at). RLS enabled — permissive decision_context_snapshot_tenant_isolation. Append-only: REVOKE UPDATE, DELETE; GRANT SELECT, INSERT.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`platform.uuid_generate_v7()`	Part of composite PK/UNIQUE, no standalone PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`context_schema_version`	text	NOT NULL	—
`content_hash`	text	NOT NULL	—
`business_effective_time`	timestamptz	NOT NULL	—
`knowledge_cutoff_time`	timestamptz	NOT NULL	—
`model_version_id`	UUID	nullable	—	FK → `ai.model_version`; retirement-checked (T1/GUARD 8)
`prompt_version_id`	UUID	nullable	—	FK → `ai.prompt_version`; retirement-checked
`skill_version_id`	UUID	nullable	—	FK → `agents.skill_version`; retirement-checked
`toolset_version_id`	UUID	nullable	—	FK → `agents.toolset_version`; retirement-checked
`kill_switch_generation`	bigint	nullable	—
`resolved_effective_policy`	jsonb	nullable	—	`{"skill_execution_policy":{"max_cost_cents":500},"duty_grant":{"authority_level":"needs_approval"}}` — the policy inputs actually resolved at decision time (I3's frozen snapshot), not a live pointer
`resolved_goal_profile`	jsonb	nullable	—	`{"tenant_goal_binding_id":"<uuid>","objective":"reduce_stockouts","weight":0.7}` — same frozen-snapshot discipline, for `semantics.tenant_goal_binding`
`experiment_assignment_id`	UUID	nullable	—	Composite FK → `signals.experiment_assignment (id, tenant_id)` — I1's fix
`summary`	text	nullable	—
`created_at`	timestamptz	NOT NULL	`now()`	Partition key

Indexes: plain index on tenant_id; UNIQUE (id,tenant_id,created_at).

Partitions: 13 monthly (2026-06 through 2027-06) + 1 DEFAULT = 14 total.

Triggers:

trg_decision_context_snapshot_append_only (BEFORE UPDATE OR DELETE) — platform.reject_append_only_mutation() (shared).
trg_decision_context_snapshot_reject_retired (BEFORE INSERT) — agents.reject_retired_decision_context_versions() — T1 / GUARD 8, see Design Patterns Summary.
trg_decision_context_snapshot_lock_causal_basis (AFTER INSERT) — signals.lock_experiment_causal_basis() — closes BLOCKER 3's own disclosed forward-ref from Phase 4 (that function was built then, its CREATE TRIGGER deferred until this table existed).

`agents.decision_context_manifest` (14 cols) — A4b, BLOCKER6, PARTITIONED

Self-contained evidence envelope, not an index of pointers — item_schema_contract defaults 'evidence_envelope_v1', the payload-shape contract embedding observed_value/value_hash so reconstruction never depends on the source operational row surviving archival. Partitioned same shape as decision_context_snapshot (E1). retention_policy_id is the binding this design can actually guarantee at the schema layer; the bucket's own lifecycle rule reading it is deployment infra (Part D).

Tenant-scoped, PARTITIONED BY RANGE(created_at), monthly, 14 partitions. Same append-only/RLS shape as decision_context_snapshot.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`platform.uuid_generate_v7()`	Part of composite PK, no standalone PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`decision_context_snapshot_id`	UUID	NOT NULL	—	Composite FK (with `.tenant_id`, `.decision_context_snapshot_created_at`) → `decision_context_snapshot (id, tenant_id, created_at)`
`decision_context_snapshot_created_at`	timestamptz	NOT NULL	—	Shadow column, the composite-FK partner
`item_count`	integer	NOT NULL	—
`manifest_hash`	text	NOT NULL	—
`storage_reference`	text	NOT NULL	—
`storage_bucket`	text	NOT NULL	—
`compression`	text	nullable	—
`schema_version`	text	NOT NULL	—
`item_schema_contract`	text	NOT NULL	`'evidence_envelope_v1'`
`verification_status`	text	NOT NULL	`'pending'`	CHECK IN (`pending`,`verified`,`hash_mismatch`,`unreachable`)
`retention_policy_id`	UUID	NOT NULL	—	Composite FK → `evidence_retention_policy (id, tenant_id)`
`created_at`	timestamptz	NOT NULL	`now()`	Partition key

CHECK constraints: chk_decision_context_manifest_verification_status.

Indexes: plain index on tenant_id.

Partitions: 13 monthly + 1 DEFAULT = 14 total.

Triggers: trg_decision_context_manifest_append_only — platform.reject_append_only_mutation().

`agents.decision_context_feature` (6 cols) — A4b direct-link, TYPED (BLOCKER6)

Thin join row, composite FK into decision_context_snapshot plus a TYPED composite FK into signals.feature_value (itself partitioned — the FK is a genuine 3-part composite matching the partition-key shadow column).

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive decision_context_feature_tenant_isolation. No soft delete, no updated_at/created_at (a pure join row).

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`decision_context_snapshot_id`	UUID	NOT NULL	—	Composite FK → `decision_context_snapshot (id, tenant_id, created_at)`
`decision_context_snapshot_created_at`	timestamptz	NOT NULL	—	Shadow column
`feature_value_id`	UUID	NOT NULL	—	Composite FK → `signals.feature_value (id, tenant_id, recorded_at)`
`feature_value_recorded_at`	timestamptz	NOT NULL	—	Shadow column

Indexes: PK on id.

`agents.decision_context_forecast` (6 cols)

Same shape as decision_context_feature, targeting signals.forecast.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`decision_context_snapshot_id`	UUID	NOT NULL	—	Composite FK → `decision_context_snapshot (id, tenant_id, created_at)`
`decision_context_snapshot_created_at`	timestamptz	NOT NULL	—	Shadow column
`forecast_id`	UUID	NOT NULL	—	Composite FK → `signals.forecast (id, tenant_id, recorded_at)`
`forecast_recorded_at`	timestamptz	NOT NULL	—	Shadow column

Indexes: PK on id. RLS: permissive decision_context_forecast_tenant_isolation.

`agents.decision_context_metric` (5 cols)

Links a decision snapshot to the semantics.metric_version rows it consulted. metric_version is not time-partitioned, so this FK is a plain 1-part reference (no shadow column needed).

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`decision_context_snapshot_id`	UUID	NOT NULL	—	Composite FK → `decision_context_snapshot (id, tenant_id, created_at)`
`decision_context_snapshot_created_at`	timestamptz	NOT NULL	—	Shadow column
`metric_version_id`	UUID	NOT NULL	—	FK → `semantics.metric_version`

Indexes: PK on id. RLS: permissive decision_context_metric_tenant_isolation.

`agents.decision_context_policy` (5 cols)

Links a decision snapshot to the platform.ai_capacity_policy row(s) that governed it.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`decision_context_snapshot_id`	UUID	NOT NULL	—	Composite FK → `decision_context_snapshot (id, tenant_id, created_at)`
`decision_context_snapshot_created_at`	timestamptz	NOT NULL	—	Shadow column
`ai_capacity_policy_id`	UUID	NOT NULL	—	FK → `platform.ai_capacity_policy`

Indexes: PK on id. RLS: permissive decision_context_policy_tenant_isolation.

`agents.decision_context_knowledge_source` (5 cols)

Links a decision snapshot to the files.document_chunk row(s) (RAG context) it drew on. document_chunk is not time-partitioned, so its own FK is a plain composite (id, tenant_id) — required files.document_chunk to gain UNIQUE(id, tenant_id), a small files reopen bundled into this migration (Section 1), a disclosed prerequisite BLOCKER6 itself found missing.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`decision_context_snapshot_id`	UUID	NOT NULL	—	Composite FK → `decision_context_snapshot (id, tenant_id, created_at)`
`decision_context_snapshot_created_at`	timestamptz	NOT NULL	—	Shadow column
`document_chunk_id`	UUID	NOT NULL	—	Composite FK → `files.document_chunk (id, tenant_id)`

Indexes: PK on id. RLS: permissive decision_context_knowledge_source_tenant_isolation.

Action Ledger + Rollback + Certification (6 tables)

`agents.agent_decision` (9 cols) — I1, PARTITIONED

A single ai.agent_execution may make several model calls / reach several distinct decisions, each authorizing a different agent_action — v1's own 1-snapshot-per-execution granularity could not represent this. Partitioned BY RANGE(created_at) monthly (E1, same volume profile as agent_execution/decision_context_snapshot).

Tenant-scoped, PARTITIONED BY RANGE(created_at), monthly, 14 partitions. PRIMARY KEY (id, created_at) + UNIQUE(id, tenant_id, created_at). RLS enabled — permissive agent_decision_tenant_isolation. Append-only: REVOKE UPDATE, DELETE; GRANT SELECT, INSERT.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`platform.uuid_generate_v7()`	Part of composite PK, no standalone PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_execution_id`	UUID	NOT NULL	—	Composite FK → `ai.agent_execution (id, tenant_id, created_at)`
`agent_execution_created_at`	timestamptz	NOT NULL	—	Shadow column
`decision_context_snapshot_id`	UUID	NOT NULL	—	Composite FK → `decision_context_snapshot (id, tenant_id, created_at)`
`decision_context_snapshot_created_at`	timestamptz	NOT NULL	—	Shadow column
`decision_type`	text	nullable	—	CHECK NULL OR IN (`action_selection`,`escalation`,`no_action`)
`rationale_summary`	text	nullable	—
`created_at`	timestamptz	NOT NULL	`now()`	Partition key

CHECK constraints: chk_agent_decision_decision_type.

Indexes: plain index on tenant_id; UNIQUE (id,tenant_id,created_at).

Partitions: 13 monthly + 1 DEFAULT = 14 total.

Triggers: trg_agent_decision_append_only — platform.reject_append_only_mutation().

`agents.agent_action` (15 cols) — v1 Gap 5, amended BLOCKER2/I1/BLOCKER4

Append-only fact-record — every operational write an agent claims to have made. agent_execution_id is a composite 3-part FK (BLOCKER2 — agent_execution is partitioned). agent_task_id is deliberately NOT a column here — a disclosed fix to a genuine gap in v3's own raw SQL, which referenced NEW.agent_task_id on this table without ever declaring the column; the migration's enforce_single_module_autonomy() trigger derives the task instead by joining through agent_execution_id, avoiding a redundant, driftable denormalized column. source_module_id (BLOCKER4) replaces v1's free-text source_module. approval_request_id/orchestration_id (BLOCKER4) are the saga-gate exemptions. agent_decision_id (I1) links this action to the specific decision that authorized it, with a real consistency trigger validating the two rows' agent_execution_id actually match.

Tenant-scoped, append-only (not time-partitioned itself). tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_action_tenant_isolation. REVOKE UPDATE, DELETE; GRANT SELECT, INSERT.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`platform.uuid_generate_v7()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_execution_id`	UUID	NOT NULL	—	Composite FK → `ai.agent_execution (id, tenant_id, created_at)`
`agent_execution_created_at`	timestamptz	NOT NULL	—	Shadow column
`agent_decision_id`	UUID	NOT NULL	—	Composite FK → `agent_decision (id, tenant_id, created_at)` — I1
`agent_decision_created_at`	timestamptz	NOT NULL	—	Shadow column
`source_module_id`	UUID	NOT NULL	—	FK → `platform.module_catalog` — replaces v1's free-text `source_module` (BLOCKER4)
`source_type`	text	NOT NULL	—	Free text, e.g. `'purchase_order'`
`source_ref`	UUID	NOT NULL	—	The record this action wrote to — polymorphic, no FK
`action_type`	text	NOT NULL	—
`approval_request_id`	UUID	nullable	—	Composite FK → `approvals.approval_request (id, tenant_id)` — saga-gate exemption
`orchestration_id`	UUID	nullable	—	Saga-gate exemption (deterministic multi-module orchestration, no FK — orchestration is a runtime concept)
`claimed_effect`	jsonb	nullable	—	`{"before":{"status":"draft"},"after":{"status":"sent"}}` — what the agent asserts this write changed, for later reconciliation against the source row's actual state
`claimed_value_cents`	bigint	nullable	—
`created_at`	timestamptz	NOT NULL	`now()`

Indexes: composite index (tenant_id,source_module_id,source_type,source_ref); plain index on agent_execution_id; UNIQUE (id,tenant_id) — the signals.outcome_observation/outcome_authority/rollback_execution/agent_incident composite-FK target.

Triggers:

trg_agent_action_append_only — platform.reject_append_only_mutation().
trg_agent_action_enforce_single_module (BEFORE INSERT) — agents.enforce_single_module_autonomy() — GUARD 4, see Design Patterns Summary.
trg_agent_action_validate_execution_consistency (BEFORE INSERT) — agents.validate_action_decision_execution_consistency() — GUARD 5.

Cross-module consumers: signals.outcome_observation.agent_action_id, signals.outcome_authority.agent_action_id — both closed to real composite FKs by this migration (Section 10), 2 forward-refs Phase 4 disclosed.

`agents.rollback_recipe` (17 cols) — v1

The CATALOG of which compensation applies to which action type — not a new reversal mechanism (this codebase already does compensating transactions elsewhere: receiving reversals, loyalty clawback, returns resolutions). tenant_id nullable (mostly Vrida-authored/global, tenant override possible) — same corrected 2-policy RLS shape as agent_eval_suite.

Mixed-scope. RLS: rollback_recipe_select (read: own OR global) + rollback_recipe_write (write: own only). Soft delete (deleted_at). Full autonomy pack.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	nullable	—	FK → `platform.tenant`; NULL = global
`action_type`	text	NOT NULL	—
`compensating_action_type`	text	NOT NULL	—
`compensating_service_method`	text	NOT NULL	—
`description`	text	nullable	—
`is_active`	boolean	NOT NULL	`true`
`created_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`automation_source`	text	NOT NULL	`'human'`	CHECK IN (`human`,`agent`,`system`,`seed`)
`review_status`	text	NOT NULL	`'not_required'`	CHECK IN (`not_required`,`pending`,`approved`,`rejected`)
`review_reason`	text	nullable	—
`reviewed_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`reviewed_at`	timestamptz	nullable	—
`decision_provenance`	jsonb	nullable	—	Same shape as `agent_task.decision_provenance`
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`
`deleted_at`	timestamptz	nullable	—	Soft delete

CHECK constraints: chk_rollback_recipe_automation_source; chk_rollback_recipe_review_status.

Indexes: PK on id; UNIQUE (tenant_id,action_type) WHERE tenant_id IS NOT NULL AND deleted_at IS NULL; UNIQUE (action_type) WHERE tenant_id IS NULL AND deleted_at IS NULL.

`agents.rollback_execution` (12 cols) — v1, mutable (status lifecycle)

compensating_source_* CHECK uses the genuine NULL-safe 3-way "all or nothing" form already proven correct elsewhere in the ai schema (ai.import_record's own chk_import_record_target_all_or_nothing), not agent_execution's own flawed 2-of-3 precedent (a live-verified citation error in v1's own design pass) — a permanent dangling pointer from copying that flawed shape would be hard to correct cleanly, though this row itself is mutable (see below).

Correction (Phase 1 remediation, 2026-07-18, Item 3): this table was mislabeled "append-only" at build time. status (pending→completed/failed) and completed_at are a genuine in-progress-to-terminal state transition, updated in place as a rollback executes — the opposite of an immutable ledger row. authenticated correctly retains UPDATE (no REVOKE, no reject_append_only_mutation trigger installed, and none should be). Label-only fix; no schema/migration change.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive rollback_execution_tenant_isolation. No soft delete.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`platform.uuid_generate_v7()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_action_id`	UUID	NOT NULL	—	Composite FK → `agent_action (id, tenant_id)`
`rollback_recipe_id`	UUID	nullable	—	FK → `rollback_recipe` (bare — recipe may be global)
`status`	text	NOT NULL	`'pending'`	CHECK IN (`pending`,`completed`,`failed`)
`compensating_source_module`	text	nullable	—	Part of the 3-way all-or-nothing group
`compensating_source_type`	text	nullable	—
`compensating_source_ref`	UUID	nullable	—
`failure_reason`	text	nullable	—
`initiated_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`created_at`	timestamptz	NOT NULL	`now()`
`completed_at`	timestamptz	nullable	—

CHECK constraints: chk_rollback_execution_status; chk_rollback_execution_compensating_ref_together — all 3 of compensating_source_{module,type,ref} NULL, or all 3 NOT NULL, never a partial set.

Indexes: plain index on agent_action_id; UNIQUE (id,tenant_id) — the agent_incident composite-FK target.

`agents.agent_incident` (14 cols) — v1 Gap 12

The human-facing narrative ("an agent caused a loss, here's what happened and its impact") — distinct from rollback_execution's mechanical compensation record. Does not preempt a future audit module (a different concern: data-privacy/compliance breach notification, not agent-caused operational loss).

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive agent_incident_tenant_isolation. No soft delete.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`agent_execution_id`	UUID	NOT NULL	—	Composite FK → `ai.agent_execution (id, tenant_id, created_at)`
`agent_execution_created_at`	timestamptz	NOT NULL	—	Shadow column
`agent_action_id`	UUID	nullable	—	Composite FK → `agent_action (id, tenant_id)`
`rollback_execution_id`	UUID	nullable	—	Composite FK → `rollback_execution (id, tenant_id)`
`severity`	text	NOT NULL	—	CHECK IN (`low`,`medium`,`high`,`critical`)
`description`	text	NOT NULL	—
`financial_impact_cents`	bigint	nullable	—
`status`	text	NOT NULL	`'open'`	CHECK IN (`open`,`investigating`,`resolved`,`closed`)
`reported_by_actor_id`	UUID	nullable	—	FK → `identity.actor`
`resolved_at`	timestamptz	nullable	—
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`

CHECK constraints: chk_agent_incident_severity; chk_agent_incident_status.

Indexes: plain index on tenant_id.

`agents.skill_certification` (13 cols) — A3

Global (no tenant_id), references immutable version rows only. Pinned via agent_identity_id + model_version_id (A2b's own assessment: agent_identity has no version pair, so this combination identifies the evaluated configuration precisely enough — corrects the design's own literal ask of a non-existent agent_version_id). Deployment-activation enforcement (2 points, BLOCKER5-extended) and execution-time revalidation both live in the migration's own trigger functions, not on this table.

Global, non-tenant-scoped. No RLS — matches skill_version/tool_version's own SELECT-only-via-GRANT convention, no tenant dimension to isolate.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`platform.uuid_generate_v7()`	PK
`skill_version_id`	UUID	NOT NULL	—	FK → `agents.skill_version`
`evaluation_suite_version_id`	UUID	nullable	—	FK → `agent_eval_suite_version`
`agent_identity_id`	UUID	nullable	—	FK → `identity.agent_identity`
`model_version_id`	UUID	nullable	—	FK → `ai.model_version`
`toolset_version_id`	UUID	NOT NULL	—	FK → `agents.toolset_version`
`environment`	text	NOT NULL	—	CHECK IN (`sandbox`,`staging`,`prod_shadow`)
`result`	text	NOT NULL	—	CHECK IN (`pass`,`fail`)
`score`	numeric(5,4)	nullable	—	CHECK NULL OR 0–1
`certified_at`	timestamptz	NOT NULL	`now()`
`expires_at`	timestamptz	nullable	—
`revoked_at`	timestamptz	nullable	—
`revocation_reason`	text	nullable	—

CHECK constraints: chk_skill_certification_environment; chk_skill_certification_result; chk_skill_certification_score.

Indexes: PK on id.

Read by: validate_skill_activation() (GUARD 6, Point 1) — "a valid, unexpired, unrevoked skill_certification exists AND none of the versions it references is itself retired."

Kill-Switch (2 tables)

`agents.kill_switch_event` (8 cols) — BLOCKER 7, append-only history

REPLACES v2's own single kill_switch_directive table, split into history (append-only, this table) and current resolved state (native-upsert-only, kill_switch_scope_state below). Fixes a real defect in v2's own propagate_kill_switch_fencing() trigger: it fired unconditionally on AFTER INSERT with no directive='resume' exclusion, so a resume re-cancelled/re-fenced every task it was supposed to release — this build's corrected trigger excludes resume explicitly. scope_ref is polymorphic (agent_identity/skill/tool/model target) — no FK possible (4+ targets), CHECK-validated by scope_type instead, the same disclosed shape as platform.ai_capacity_policy.scope_ref. issued_at uses the same forced-timestamp discipline as BLOCKER 3's assigned_at (a DEFAULT clock_timestamp(), not now()).

Mixed-scope (tenant_id nullable — NULL for scope_type='global'). RLS: fixed same-day as a Section 4 self-audit finding (item B) — see below. No soft delete, no updated_at.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`platform.uuid_generate_v7()`	PK
`tenant_id`	UUID	nullable	—	FK → `platform.tenant`; NULL only for `scope_type='global'`
`scope_type`	text	NOT NULL	—	CHECK IN (`global`,`tenant`,`agent_identity`,`skill`,`tool`,`model`)
`scope_ref`	UUID	nullable	—	Polymorphic, no FK; CHECK-paired with `scope_type`
`directive`	text	NOT NULL	—	CHECK IN (`kill`,`suspend`,`resume`)
`issued_by_actor_id`	UUID	NOT NULL	—	Bare (no FK declared in Drizzle source)
`issued_at`	timestamptz	NOT NULL	`clock_timestamp()`	Forced-timestamp discipline — matches BLOCKER 3's `assigned_at`
`fencing_generation`	bigint	NOT NULL	—

CHECK constraints: chk_kill_switch_event_scope_type; chk_kill_switch_event_directive; chk_kill_switch_event_scope_ref — (scope_type='global' AND scope_ref IS NULL) OR (scope_type != 'global' AND scope_ref IS NOT NULL).

Indexes: PK on id.

RLS — the Section 4 self-audit fix (Item B, this build's own finding, disclosed and fixed same-day, Section 10 of the migration). The original build shipped tenant_id + GRANT SELECT, INSERT TO authenticated with ZERO RLS policies — a live security gap letting any tenant session read every other tenant's kill-switch history and forge a kill/suspend/resume event against another tenant's agents (a 'global' kill was already correctly blocked for ordinary tenant sessions by the intended WITH CHECK, but that WITH CHECK didn't exist yet either). Fixed with the same 2-policy mixed-scope pattern already established by agent_eval_suite/rollback_recipe:

kill_switch_event_select (SELECT, tenant_id = current_setting('app.current_tenant_id')::uuid OR tenant_id IS NULL)
kill_switch_event_write (ALL, tenant_id = current_setting('app.current_tenant_id')::uuid on both USING and WITH CHECK)

Live-reproduced: cross-tenant SELECT now returns 0 rows; a cross-tenant or forged-global INSERT is rejected; a legitimate own-tenant INSERT succeeds.

Triggers:

trg_kill_switch_event_append_only (BEFORE UPDATE OR DELETE) — platform.reject_append_only_mutation().
trg_kill_switch_event_resolve_state (AFTER INSERT) — agents.resolve_kill_switch_state() — GUARD 1, part A.
trg_kill_switch_event_propagate (AFTER INSERT) — agents.propagate_kill_switch_fencing() — GUARD 1, part B.

`agents.kill_switch_scope_state` (6 cols) — current resolved state, native-upsert-only

Exactly ONE row per scope, maintained exclusively via a native INSERT ... ON CONFLICT upsert (the resolve_kill_switch_state() trigger on kill_switch_event). 2 partial unique indexes since Postgres cannot target either dynamically in one ON CONFLICT clause (live-verified) — the trigger function branches explicitly by scope_ref IS NULL. current_fencing_generation uses GREATEST() across concurrent writers; a resume resets it to 0 (a resumed scope has no meaningful "generation to fence against" until a future kill re-establishes one).

No tenant_id column at all (scope is expressed via scope_type/scope_ref, not a direct tenant FK — a tenant-scoped kill still reads through scope_type='tenant', scope_ref=<tenant_id>). No RLS (read via the is_agent_blocked() SECURITY DEFINER helper, GRANT SELECT to authenticated for direct reads). No soft delete, no created_at.

Column	Type	Nullable	Default	Notes
`scope_type`	text	NOT NULL	—	CHECK IN (`global`,`tenant`,`agent_identity`,`skill`,`tool`,`model`); part of both partial-unique shapes
`scope_ref`	UUID	nullable	—	Polymorphic, no FK; CHECK-paired with `scope_type`
`current_directive`	text	NOT NULL	—	CHECK IN (`kill`,`suspend`,`resume`)
`current_fencing_generation`	bigint	NOT NULL	`0`	`GREATEST()`-maintained across concurrent writers; reset to 0 on resume
`last_event_id`	UUID	NOT NULL	—	Points at the `kill_switch_event` row that last updated this state (no declared FK — event may be in a different partition/table lifecycle)
`updated_at`	timestamptz	NOT NULL	`now()`	Trigger-maintained by the upsert itself

CHECK constraints: chk_kill_switch_scope_state_current_directive; chk_kill_switch_scope_state_scope_ref.

Indexes: UNIQUE (scope_type,scope_ref) WHERE scope_ref IS NOT NULL; UNIQUE (scope_type) WHERE scope_ref IS NULL — 2 partial uniques covering the scoped and global cases respectively, since one ON CONFLICT target can't dynamically cover both.

Read by: agents.is_agent_blocked(p_tenant_id, p_agent_identity_id, p_skill_version_id, p_toolset_version_id, p_model_version_id) — a SECURITY DEFINER, STABLE SQL function, GRANT EXECUTE to authenticated, the single fail-closed precedence resolver both check_agent_task_not_blocked() and check_agent_execution_not_blocked() (GUARD 2) call.

Policy (2 tables)

`agents.skill_execution_policy` (7 cols) — A8 Rule 4

Bounds a skill's own concurrency/cost policy against the LOWEST of the agent_duty_grant.spend_limit_cents reachable for that skill's required permissions — enforced by a trigger on agent_skill_assignment (GUARD 6, Point 3), not a CHECK (a plain CHECK cannot reference another table's rows).

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive skill_execution_policy_tenant_isolation. No soft delete.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`tenant_id`	UUID	NOT NULL	—	FK → `platform.tenant`
`skill_version_id`	UUID	NOT NULL	—	FK → `agents.skill_version`
`max_concurrency`	integer	nullable	—
`max_cost_cents`	bigint	nullable	—	Bound-checked against the lowest reachable duty-grant `spend_limit_cents` at skill-activation time (GUARD 6, Point 3)
`created_at`/`updated_at`	timestamptz	NOT NULL	`now()`

Indexes: PK on id; UNIQUE (tenant_id,skill_version_id).

`agents.workload_class` (11 cols) — E8

A taxonomy of latency/timeout profiles, one valid ai_capacity_policy.scope_type value, global (no tenant_id). Seeded with the design's own precedence convention: interactive_copilot at the numerically-highest queue_priority, batch_enrichment/embedding_indexing/evaluation at the lowest. The scheduler that actually honors this queue-priority ordering is Part D, deferred.

Global, no RLS, no soft delete, no created_at/updated_at.

Column	Type	Nullable	Default	Notes
`id`	UUID	NOT NULL	`gen_random_uuid()`	PK
`code`	text	NOT NULL	—	UNIQUE; CHECK IN (10 values: `interactive_copilot`,`sync_transactional_decision`,`background_agent_task`,`batch_enrichment`,`forecast_generation`,`embedding_indexing`,`evaluation`,`shadow_execution`,`experiment_measurement`,`long_running_integration`)
`latency_objective_ms`	integer	nullable	—
`timeout_ms`	integer	nullable	—
`retry_policy`	jsonb	nullable	—	`{"max_attempts":3,"backoff":"exponential","base_delay_ms":500}`
`queue_priority`	integer	NOT NULL	`0`	Higher = served first
`max_concurrency`	integer	nullable	—
`acceptable_model_tier`	text	nullable	—
`fallback_allowed`	boolean	NOT NULL	`true`
`degraded_mode_allowed`	boolean	NOT NULL	`false`
`human_approval_after_timeout`	boolean	NOT NULL	`false`

CHECK constraints: chk_workload_class_code.

Indexes: PK on id; UNIQUE on code.

Seed data (Section 9 of the migration, 10 rows): interactive_copilot (priority 100, 2s/10s latency/timeout), sync_transactional_decision (90), background_agent_task (50), shadow_execution (40), experiment_measurement (30, fallback_allowed=false), long_running_integration (30), forecast_generation (20, fallback_allowed=false), batch_enrichment/embedding_indexing/evaluation (10 each).

Cross-module consumer: ai.routing_policy.workload_class_id — a plain FK, closed by this migration (Section 10), a Phase 2-disclosed forward-ref.

Companion changes outside the `agents` schema (same migration)

files.document_chunk gains UNIQUE(id, tenant_id) (Section 1) — the prerequisite decision_context_knowledge_source.document_chunk_id's own composite FK needed; BLOCKER6's own disclosed prerequisite, live-verified missing before this build.

approvals.approval_request.source_module CHECK widened to add 'agents' (Section 2) — the saga-gate exemption seam (agent_action.approval_request_id) and A3's autonomy-promotion seam (agent_autonomy_profile.last_promotion_approval_request_id) both need it. This same fix also closed a pre-existing, unrelated Drizzle-file/live-DB drift found while touching this constraint: 'offers'/'returns' were already live values but missing from the Drizzle source file.

platform.polymorphic_target_registry (I6, new table, 4 cols: target_type PK, target_schema, target_table, tenant_column default 'tenant_id') + platform.validate_polymorphic_reference(p_target_type, p_target_id, p_tenant_id) — the real validator for genuinely-unavoidable polymorphic references (ai.agent_memory_source.source_ref, spanning 4 target tables that cannot be typed). Split target_schema/target_table columns (not a single free-text 'schema.table' string) — a live-verified fix to a real SQL-injection vector in the single-string form v3's own independent review found (and its own %I-on-the-combined-string alternative was also tested and confirmed wrong — a quoted compound string isn't a valid two-part identifier; %I.%I on 2 separate columns is the actual fix). Seeded with 4 rows: agent_execution → ai.agent_execution, decision_context_snapshot → agents.decision_context_snapshot, document_chunk → files.document_chunk, outcome_observation → signals.outcome_observation.

ai.agent_execution gains 8 new columns (Section 7, all nullable/defaulted, purely additive): agent_task_id (composite FK → agents.agent_task), sequence_index (CHECK: requires agent_task_id NOT NULL when set), tool_version_id (FK → agents.tool_version), approval_request_id (composite FK → approvals.approval_request), retry_count (NOT NULL DEFAULT 0, CHECK ≥ 0), blocked_by_policy, confidence_threshold (numeric(3,2)), escalation_reason.

ai.agent_memory_source — a new trigger, trg_agent_memory_source_validate_polymorphic_ref (BEFORE INSERT) — ai.validate_agent_memory_source_polymorphic_ref() — GUARD 7, calling platform.validate_polymorphic_reference().

agents — Design Patterns Summary

The 8 distinct trigger-backed guards

Section 8 of the migration is headed "the 7 named critical guards + supporting mechanisms." A same-day Section 4 self-audit found and fixed a real trigger gap (T1, below) that functions as a genuine 6th guard on top of the audit's own item T — so 8 distinct trigger-backed guards are proven in this build, not 7; disclosed honestly here rather than forced to match the original count.

GUARD 1 (BLOCKER 7) — kill-switch history/state split, resume-safe propagation, fail-closed precedence resolver. agents.resolve_kill_switch_state() (AFTER INSERT ON kill_switch_event) upserts kill_switch_scope_state via INSERT ... ON CONFLICT, GREATEST()-merging current_fencing_generation across concurrent writers, resetting to 0 on resume. agents.propagate_kill_switch_fencing() (AFTER INSERT ON kill_switch_event) bumps agent_task.fencing_token and sets cancellation_requested_at for every matching in-progress task — explicitly excluding directive='resume' (the real v2 defect this build fixes: the original unconditionally fired on every insert, so a resume re-cancelled/re-fenced every task it was supposed to release). agents.is_agent_blocked(...) (SECURITY DEFINER, STABLE, GRANT EXECUTE TO authenticated) is the single fail-closed precedence resolver both downstream claim-time guards (GUARD 2) call.

GUARD 2 (A5, corrected per BLOCKER 7) — kill-switch check at claim time, not just creation time. agents.check_agent_task_not_blocked() (BEFORE INSERT OR UPDATE OF status ON agent_task) — fires on the claim UPDATE (a status transition), not INSERT-only, since E3's claim is itself an UPDATE and must be re-checked at the moment of claim. ai.check_agent_execution_not_blocked() (BEFORE INSERT ON ai.agent_execution) — same kill-switch check, plus (A5's own extended text) verifies identity.agent_duty_grant.status='active' for the referenced permission and agents.agent_tool_grant.status='active' for the referenced tool version. Both check identity.agent_identity.status NOT IN ('killed','suspended') first — fail-closed if the agent row doesn't even exist.

GUARD 3 (E3 + v1 Gap 1) — runaway-loop/cost ceiling, BLOCK not flag. agents.enforce_task_runaway_ceiling() (BEFORE INSERT ON ai.agent_execution) atomically increments agent_task.step_count/cost_cents_accrued in the same UPDATE ... WHERE (max_steps IS NULL OR step_count+1 <= max_steps) AND (max_cost_cents IS NULL OR ...); NOT FOUND → RAISE EXCEPTION. v1's own explicit decision: an agent cost/step runaway is a safety property, not a business-judgment tolerance call like receiving's over-shipment — never a silent LEAST()-capped write-back. 2 supporting (not separately "named") mechanisms fire alongside it on the same table: ai.require_idempotency_key_for_write_tools() (E3's idempotency-key requirement, now DB-enforced — was convention-only in v1) and ai.reject_retired_tool_version().

GUARD 4 (BLOCKER 4) — the saga gate, enforced against real writes, not a static toolset-shape proxy. agents.enforce_single_module_autonomy() (BEFORE INSERT ON agent_action) — no-ops if approval_request_id/orchestration_id is set (the exemption). Otherwise derives agent_task_id by joining ai.agent_execution, then atomically claims agent_task.allowed_mutating_module_id via UPDATE ... WHERE allowed_mutating_module_id IS NULL (the same proven claim-on-first-write idiom E3's own task claim and BLOCKER2's own promotion upsert already use) — a second agent_action targeting a DIFFERENT module without the exemption is rejected.

GUARD 5 (I1) — agent_action/agent_decision consistency. agents.validate_action_decision_execution_consistency() (BEFORE INSERT ON agent_action) — 2 independently-settable FK columns (agent_action.agent_execution_id and agent_action.agent_decision_id → agent_decision.agent_execution_id) do not self-reconcile merely by both existing; this trigger cross-checks them explicitly (v3's own independent-review correction).

GUARD 6 (A3 deployment activation + BLOCKER5 duty-check + A8 Rule 4 spend-ceiling) — validate_skill_activation(). Fires on agent_skill_assignment (BEFORE INSERT OR UPDATE OF is_enabled) — the table's own resolution of a real ambiguity: A3's own prose named "a trigger on tenant_agent_deployment," but that table has no skill_version_id column at all, making the literal description structurally impossible; agent_skill_assignment is the schema's actual "this skill is active for this agent" instantiation point, used here for all 3 points. Point 1 (A3): a valid, unexpired, unrevoked skill_certification exists, AND none of the versions it references (skill_version/toolset_version/model_version) is itself retired — the independent-review MAJOR fix: a cert issued against a since-retired version must not remain activatable. Point 2 (BLOCKER5): the agent holds a sufficient agent_duty_grant for every permission skill_version_required_duty names, via an explicit ordinal CASE mapping (may_act_alone=3, needs_approval=2, draft_only=1) — not a raw text comparison, since 'may_act_alone' alphabetically sorts before 'needs_approval', which would silently invert the intended authority ordering. Point 3 (A8 Rule 4): a skill cannot grant itself more concurrency/cost than the LOWEST duty grant reachable for its own required permissions — row-locks the referenced agent_duty_grant rows FOR UPDATE for the duration of the check.

GUARD 7 (I6) — the real validator for ai.agent_memory_source.source_ref. ai.validate_agent_memory_source_polymorphic_ref() (BEFORE INSERT ON ai.agent_memory_source) calls platform.validate_polymorphic_reference(), the genuine, injection-safe (%I.%I on 2 separate columns) EXECUTE-based existence check against the registry.

T1 (Section 4 self-audit finding, functions as an 8th guard) — the A2b retirement-version-check trigger. agents.reject_retired_decision_context_versions() (BEFORE INSERT ON decision_context_snapshot) rejects an insert referencing a retired model_version/prompt_version/skill_version/toolset_version — A2b's own retirement rule 1 ("retirement blocks new executions") mapped onto the actual schema shape, since ai.agent_execution carries tool_version_id directly while the other 4 references live on decision_context_snapshot (a mapping A2b's own prose never pinned down, only settled later by I1/BLOCKER6). Tested via a dedicated regression section, not originally counted among the "7 named" guards in the migration's own Section 8 header — disclosed here as a genuine 8th.

2 genuine PL/pgSQL bugs found and fixed during this build's own live guard-reproduction

(a) A record-typed PL/pgSQL variable's IS NOT NULL test is unreliable when used directly as an IF condition. Inside validate_skill_activation()'s original Point 3 draft, a record-typed variable holding the resolved skill_execution_policy row silently evaluated as if NULL in a boolean IF context — empirically confirmed via a bare DO block: a genuinely non-null, fully-populated record still evaluated as if NULL, even though v_policy.max_cost_cents IS NOT NULL checked alone worked correctly moments earlier. This silently swallowed the entire A8 Rule 4 spend-ceiling check (Point 3 never actually fired). Fixed by rewriting to scalar variables (v_policy_max_cost_cents, v_min_duty_spend_cents) and checking each scalar's own nullity directly — never testing a record variable's nullity again.

(b) SELECT MIN(...) ... FOR UPDATE is syntactically REJECTED by Postgres ("FOR UPDATE is not allowed with aggregate functions"). Also found inside validate_skill_activation()'s Point 3. Fixed by splitting into a plain PERFORM 1 ... FOR UPDATE OF dg (lock only, no aggregate) followed by a separate SELECT MIN(dg.spend_limit_cents) INTO ... over the now-locked, stable rows — both visible in the function body above.

Both bugs are cited exactly in the migration's own inline comment above validate_skill_activation() — see the function definition for the full narrative.

1 genuine security gap found and fixed during the Section 4 self-audit (not live guard testing)

agents.kill_switch_event had tenant_id + GRANT SELECT, INSERT TO authenticated but zero RLS policies — any tenant session could read every other tenant's kill-switch history and forge a kill/suspend/resume event against another tenant's agents (a 'global' kill was correctly rejected for ordinary tenant sessions once the WITH CHECK was added, but no WITH CHECK existed at all before the fix). Fixed with the same 2-policy mixed-scope RLS pattern agent_eval_suite/rollback_recipe already established (kill_switch_event_select / kill_switch_event_write) — see that table's own entry above for the exact policy predicates. Live-reproduced: cross-tenant SELECT returns 0 rows, cross-tenant/global forged INSERT rejected, legitimate own-tenant INSERT succeeds. A regression test (Section I5 of the test suite) was added.

3 pre-existing forward-ref OPEN_ITEMS rows closed same-day

Phase 5 landing agents.workload_class and agents.agent_action unblocked 3 pre-existing forward-ref FKs deferred from Phase 2 (ai) and Phase 4 (signals), each explicitly logged as "wire when Phase 5 lands <target>": ai.routing_policy.workload_class_id → agents.workload_class(id) (plain FK — workload_class is global, no tenant_id); signals.outcome_observation.agent_action_id → agents.agent_action(id, tenant_id) and signals.outcome_authority.agent_action_id → agents.agent_action(id, tenant_id) (both composite FKs). Pre-migration orphan audits found 0 violating rows for all 3 — all target tables were either empty or fully consistent at the time of the ALTER. Wiring these wasn't just a schema edit — Phase 4's own pre-existing signals-schema.spec.ts used 5 hardcoded placeholder agent_action_id UUIDs that had to be retrofitted with a real minimal fixture chain (identity.actor → agent_identity → decision_context_snapshot → ai.agent_execution → agents.agent_decision → agents.agent_action) to keep passing.

Disclosed design judgment calls

tool_catalog (v1) SUPERSEDED by tool_definition/tool_version (v2 A2b version-row pattern) — v1 had zero live rows anywhere.
A1's domain → module_id retype scoped to EXACTLY 3 columns (agent_task.domain, tool_definition.domain, skill_definition.domain) per A1's own explicit scope-limiting text — NOT extended to agent_performance_profile.domain/agent_autonomy_profile.domain (both stay free-text).
agent_eval_run.eval_suite_version_id retargets to agent_eval_suite_version.id (A2b version-pinning), not the identity row.
agent_action has NO agent_task_id column — BLOCKER4's trigger derives it via a join through agent_execution_id instead (a genuine gap in v3's own literal SQL, fixed structurally, not patched with a redundant column).
A3's "trigger on tenant_agent_deployment" resolved instead as a trigger on agents.agent_skill_assignment (the only table with both agent_identity_id + skill_version_id), folding A3 cert-check + BLOCKER5 duty-check + A8 Rule 4 policy-bound check into ONE function, validate_skill_activation().
A2b retirement-rule-1 checks split by actual column location — ai.agent_execution checks only its own tool_version_id; agents.decision_context_snapshot checks its own model_version_id/prompt_version_id/skill_version_id/toolset_version_id.

Transitional Drizzle barrel collision

identity.agentSkillAssignment (v1, still used by production IdentityService) and agents.agentSkillAssignment (I5's new destination table) coexist until Phase 6 drops the old one — resolved via explicit named re-exports in packages/db/src/schema/index.ts, not a rename of either export.

Known gaps (disclosed, not fixed this pass)

Item J (Section 4 audit): most JSONB columns lacked documented example shapes in the original build — closed in this doc and in the Drizzle source comments (all ~15 logical JSONB columns across task.ts, eval.ts, shadow.ts, action.ts, policy.ts, decision_context.ts, skill.ts, tool.ts now carry a concise shape comment).
Item K (Section 4 audit, logged as a GAP, not fixed): 12 tables lack a tenant_id-LEADING index (agent_eval_case, agent_event_log, agent_shadow_decision, agent_thread, decision_context_feature/_forecast/_knowledge_source/_metric/_policy, evidence_retention_policy, kill_switch_event, rollback_execution). RLS correctness is unaffected (RLS policies work regardless of index presence); each table's natural query path is via its own FK-indexed parent (snapshot_id, eval_run_id, etc.), not a direct tenant-wide scan. Low severity, logged to OPEN_ITEMS.
updated_at has no maintaining trigger on the A2b identity-row tables (tool_definition, toolset_definition, skill_definition) — a disclosed, low-severity gap shared across the whole catalog family; every other module's own set_updated_at()-carrying table gets it via platform.set_updated_at(), but these 3 (and their sibling version rows, which have no updated_at at all by design) were not wired to it in this build.
agent_eval_case.eval_run_id, kill_switch_scope_state.last_event_id are bare (no declared FK constraint) in the Drizzle source — both disclosed as intentional (the former: no explicit rationale recorded, logged for a future reopen to evaluate; the latter: the event may already be time-partition-lifecycle-managed differently than a plain FK could track).
agent_autonomy_profile.current_mode is ADVISORY/TRACKING ONLY — the intended ENFORCED authority source, identity.agent_duty_grant.authority_level, has no REVOKE/trigger backing it today; closing that gap is a future identity reopen, not bundled into this build.

Column-count reconciliation

Table	Cols	Table	Cols
`tool_definition`	7	`agent_eval_run`	14
`tool_version`	14	`agent_eval_case`	9
`tool_version_module`	2	`agent_performance_profile`	14
`toolset_definition`	6	`agent_shadow_run`	10
`toolset_version`	9	`agent_shadow_decision`	9
`toolset_version_member`	2	`agent_autonomy_profile`	12
`skill_definition`	8	`evidence_retention_policy`	7
`skill_version`	11	`decision_context_snapshot`	16
`skill_version_toolset`	2	`decision_context_manifest`	14
`skill_version_required_duty`	3	`decision_context_feature`	6
`skill_version_module`	2	`decision_context_forecast`	6
`agent_skill_assignment`	10	`decision_context_metric`	5
`agent_catalog_entry`	14	`decision_context_policy`	5
`agent_catalog_entry_required_tool`	4	`decision_context_knowledge_source`	5
`tenant_agent_deployment`	11	`agent_decision`	9
`agent_tool_grant`	14	`agent_action`	15
`agent_schedule`	20	`rollback_recipe`	17
`agent_trigger`	19	`rollback_execution`	12
`agent_task`	34	`agent_incident`	14
`agent_thread`	9	`skill_certification`	13
`agent_event_log`	7	`kill_switch_event`	8
`agent_eval_suite`	9	`kill_switch_scope_state`	6
`agent_eval_suite_version`	9	`skill_execution_policy`	7
		`workload_class`	11
		Total	475

Verified live via pg_attribute/pg_class (logical basis — excludes partition-child relations, which inherit their parent's columns and add none of their own), schema agents: 47 logical tables, 475 columns — matches the regression suite's own count assertion exactly. The physical relation count including partition children is higher: 47 logical tables + 3 partitioned parents × 14 partitions each (13 monthly + 1 DEFAULT) = 47 + 42 = 89 physical relations in information_schema.tables, the same logical-vs-physical distinction ai.agent_execution/signals.outcome_observation already established in earlier phases of this build.

v1 baseline reconciliation: v1's own 21-table orchestration baseline never had any of its tables actually built (confirmed live before this migration — zero tables existed in the agents schema). This build is therefore not a delta against a prior live shape; it is the full A1–A8/BLOCKER1–8/I1–I8-amended design landing at once. All 21 v1 table names are represented in some form in the 47 (either directly, or superseded — tool_catalog by tool_definition/tool_version), plus 26 net-new tables the v2/v3 amendment passes added (the tool/skill version-row family, the decision-context evidence envelope, the kill-switch split, agent_decision, skill_certification, skill_execution_policy, workload_class, and the various join tables).

Regression tests

apps/api/src/platform/__tests__/agents-schema.spec.ts — 24/24 passing. Also touched: apps/api/src/platform/__tests__/signals-schema.spec.ts (Phase 4's own file, retrofitted with a real agent_action fixture chain after this build wired a composite FK into it). Run with:

cd apps/api && npx jest src/platform/__tests__/agents-schema.spec.ts --forceExit

Full apps/api suite: 1198/1198 green (serially — npx jest --forceExit --runInBand; a known pre-existing connection-pool-exhaustion flake in database/__tests__/rls-cross-tenant.spec.ts intermittently fails only under parallel workers, unrelated to this build, documented in PROJECT_DECISIONS #37).

Service layer

No AgentsService/orchestration engine yet — schema-only this phase, matching every phase of the agents-v2/v3 build so far. Downstream code queries agents.* directly via Drizzle until a service/runtime layer is built (the claim/lease/fencing protocol above, the workload-class-aware scheduler, and the evidence-manifest archival job are all Part D — deferred, unbuilt).

Open items carried forward (see OPEN_ITEMS.md)

Item K (12 tables missing a tenant_id-leading index, low severity). agent_autonomy_profile.current_mode enforcement gap (advisory only until identity.agent_duty_grant.authority_level gets its own REVOKE/trigger backing — a future identity reopen). updated_at maintaining triggers missing on the 3 A2b identity-row catalog tables. agent_eval_case.eval_run_id and kill_switch_scope_state.last_event_id bare-FK disclosures. Phase 6 (next, per CLAUDE.md's own current-focus line) closes the identity.agent_skill_assignment DROP (superseded by this module's own copy), builds the agent_reader role + agentReaderDB() connection helper (the sanctioned read path signals' own as-of functions were stubbed for), and adds an ESLint rule banning adminDb/tenantDB/set_config from agent code paths.

Last modified: Jul 12, 2026, 9:33 AM PT

agents — module #28, Phase 5 of 6 of the agents-v2/v3 build

Which tables are partitioned

Cross-Phase / Cross-Module Foreign Keys (agents)

Tool / Skill Catalog (12 tables)

agents.tool_definition (7 cols)

agents.tool_version (14 cols)

agents.tool_version_module (2 cols, join table)

agents.toolset_definition (6 cols)

agents.toolset_version (9 cols)

agents.toolset_version_member (2 cols, join table)

agents.skill_definition (8 cols)

agents.skill_version (11 cols)

agents.skill_version_toolset (2 cols, join table)

agents.skill_version_required_duty (3 cols, join table)

agents.skill_version_module (2 cols, join table)

agents.agent_skill_assignment (10 cols) — MOVED here from identity (I5)

Marketplace + Deployment (4 tables)

agents.agent_catalog_entry (14 cols) — v1 Gap 11

agents.agent_catalog_entry_required_tool (4 cols, join table)

agents.tenant_agent_deployment (11 cols) — v1 Gap 11

agents.agent_tool_grant (14 cols) — v1 Gap 7

Task Orchestration (5 tables)

agents.agent_schedule (20 cols) — v1 Gap 1

agents.agent_trigger (19 cols) — v1 Gap 1

agents.agent_task (34 cols) — v1 Gap 1, amended A1/A5/E3/BLOCKER4

agents.agent_thread (9 cols) — v1

agents.agent_event_log (7 cols) — v1, append-only

Eval (5 tables)

agents.agent_eval_suite (9 cols) — v1 Gap 2, versioned per A2b

agents.agent_eval_suite_version (9 cols)

agents.agent_eval_run (14 cols) — v1

agents.agent_eval_case (9 cols)

agents.agent_performance_profile (14 cols)

Shadow-Mode (3 tables)

agents.agent_shadow_run (10 cols) — v1 Gap 3

agents.agent_shadow_decision (9 cols)

agents.agent_autonomy_profile (12 cols) — v1 Gap 4

Decision-Context Evidence Envelope (8 tables)

agents.evidence_retention_policy (7 cols) — A4b

agents.decision_context_snapshot (16 cols) — A4, PARTITIONED

agents.decision_context_manifest (14 cols) — A4b, BLOCKER6, PARTITIONED

agents.decision_context_feature (6 cols) — A4b direct-link, TYPED (BLOCKER6)

agents.decision_context_forecast (6 cols)

agents.decision_context_metric (5 cols)

agents.decision_context_policy (5 cols)

agents.decision_context_knowledge_source (5 cols)

Action Ledger + Rollback + Certification (6 tables)

agents.agent_decision (9 cols) — I1, PARTITIONED

agents.agent_action (15 cols) — v1 Gap 5, amended BLOCKER2/I1/BLOCKER4

agents.rollback_recipe (17 cols) — v1

agents.rollback_execution (12 cols) — v1, mutable (status lifecycle)

agents.agent_incident (14 cols) — v1 Gap 12

agents.skill_certification (13 cols) — A3

Kill-Switch (2 tables)

agents.kill_switch_event (8 cols) — BLOCKER 7, append-only history

agents.kill_switch_scope_state (6 cols) — current resolved state, native-upsert-only

Policy (2 tables)

agents.skill_execution_policy (7 cols) — A8 Rule 4

agents.workload_class (11 cols) — E8

Companion changes outside the agents schema (same migration)

agents — Design Patterns Summary

The 8 distinct trigger-backed guards

2 genuine PL/pgSQL bugs found and fixed during this build's own live guard-reproduction

1 genuine security gap found and fixed during the Section 4 self-audit (not live guard testing)

3 pre-existing forward-ref OPEN_ITEMS rows closed same-day

Disclosed design judgment calls

Transitional Drizzle barrel collision

Known gaps (disclosed, not fixed this pass)

Column-count reconciliation

Regression tests

Service layer

Open items carried forward (see OPEN_ITEMS.md)

`agents` — module #28, Phase 5 of 6 of the agents-v2/v3 build

`agents.tool_definition` (7 cols)

`agents.tool_version` (14 cols)

`agents.tool_version_module` (2 cols, join table)

`agents.toolset_definition` (6 cols)

`agents.toolset_version` (9 cols)

`agents.toolset_version_member` (2 cols, join table)

`agents.skill_definition` (8 cols)

`agents.skill_version` (11 cols)

`agents.skill_version_toolset` (2 cols, join table)

`agents.skill_version_required_duty` (3 cols, join table)

`agents.skill_version_module` (2 cols, join table)

`agents.agent_skill_assignment` (10 cols) — MOVED here from `identity` (I5)

`agents.agent_catalog_entry` (14 cols) — v1 Gap 11

`agents.agent_catalog_entry_required_tool` (4 cols, join table)

`agents.tenant_agent_deployment` (11 cols) — v1 Gap 11

`agents.agent_tool_grant` (14 cols) — v1 Gap 7

`agents.agent_schedule` (20 cols) — v1 Gap 1

`agents.agent_trigger` (19 cols) — v1 Gap 1

`agents.agent_task` (34 cols) — v1 Gap 1, amended A1/A5/E3/BLOCKER4

`agents.agent_thread` (9 cols) — v1

`agents.agent_event_log` (7 cols) — v1, append-only

`agents.agent_eval_suite` (9 cols) — v1 Gap 2, versioned per A2b

`agents.agent_eval_suite_version` (9 cols)

`agents.agent_eval_run` (14 cols) — v1

`agents.agent_eval_case` (9 cols)

`agents.agent_performance_profile` (14 cols)

`agents.agent_shadow_run` (10 cols) — v1 Gap 3

`agents.agent_shadow_decision` (9 cols)

`agents.agent_autonomy_profile` (12 cols) — v1 Gap 4

`agents.evidence_retention_policy` (7 cols) — A4b

`agents.decision_context_snapshot` (16 cols) — A4, PARTITIONED

`agents.decision_context_manifest` (14 cols) — A4b, BLOCKER6, PARTITIONED

`agents.decision_context_feature` (6 cols) — A4b direct-link, TYPED (BLOCKER6)

`agents.decision_context_forecast` (6 cols)

`agents.decision_context_metric` (5 cols)

`agents.decision_context_policy` (5 cols)

`agents.decision_context_knowledge_source` (5 cols)

`agents.agent_decision` (9 cols) — I1, PARTITIONED

`agents.agent_action` (15 cols) — v1 Gap 5, amended BLOCKER2/I1/BLOCKER4

`agents.rollback_recipe` (17 cols) — v1

`agents.rollback_execution` (12 cols) — v1, mutable (status lifecycle)

`agents.agent_incident` (14 cols) — v1 Gap 12

`agents.skill_certification` (13 cols) — A3

`agents.kill_switch_event` (8 cols) — BLOCKER 7, append-only history

`agents.kill_switch_scope_state` (6 cols) — current resolved state, native-upsert-only

`agents.skill_execution_policy` (7 cols) — A8 Rule 4

`agents.workload_class` (11 cols) — E8

Companion changes outside the `agents` schema (same migration)