ai — Phase 13 (module #6, cross-cutting)

21 logical tables, 227 columns (49 if counting agent_execution/agent_memory's 26 partition-child relations individually — this doc reports the logical count, matching every other module's own convention, per a correction made during Phase 2's own independent lock-gate verification) — schema-locked 2026-07-06; reopened for the first time 2026-07-13 (agents-v2/v3 build, Phase 2 of 6) to add the C1 model-registry/deployment/prompt/routing layer and C4 memory-provenance governance, and to PARTITION agent_execution/agent_memory BY RANGE(created_at), monthly. ai is the agent-runtime schema layer: the mechanical substrate every other module's autonomy columns (automation_source, review_status, decision_provenance) ultimately reports back to. It is cross-cutting rather than a nursery-vertical product module — no tenant-facing "AI" screen exists; every table here is either infrastructure the AI onboarding/import pipeline uses (import_job, import_file, import_record — reconciled from v1, lightly retargeted), original agent-runtime infrastructure (an inference-call log wired to a real agent identity — ai_request; a business-language execution ledger — agent_execution; a cumulative usage/value meter — agent_usage_period; a tenant operating-memory store — agent_memory), or Phase 2's own new agent-infrastructure layer (not yet a working agents module, which lands at Phase 5): a 3-level LLM provider/model registry (provider_registry → model_family → model_version), a 3-state-class model-deployment surface (model_deployment durable config + _limit/_region/_policy satellites, model_deployment_override administrative override, model_deployment_status_observation transient runtime observation), an A2b-shaped prompt registry (prompt_definition/prompt_version/prompt_model_compatibility), a routing-policy table (routing_policy), and a memory-provenance join (agent_memory_source). Depends on platform (tenant ownership + platform.tenant_regional_policy for the C1 residency guard), identity (agent_identity/permission/actor FKs throughout), and has two deferred forward-refs: import_file.file_id (to files, from the original build) and routing_policy.workload_class_id (to agents.workload_class, added Phase 2 — see Open items below).

PROJECT_DECISIONS entries: #25 (original 2026-07-06 lock), #63 (2026-07-13 Phase 2 reopen — registry/deployment/prompt/routing + partitioned agent_execution/agent_memory + memory governance).

Global rules for this schema:

• Mixed tenant-scoping — 6 of 7 tables carry tenant_id NOT NULL; ai_request.tenant_id is the sole nullable exception (platform-level AI calls, e.g. shared.plant enrichment, have no tenant context and are written via service_role). All 7 tables have RLS enabled with a permissive tenant-isolation policy (USING/WITH CHECK on current_setting('app.current_tenant_id')::uuid). Verified live: rowsecurity = true on all 7.
• updated_at is trigger-maintained via platform.set_updated_at() on 6 of the 7 tables (verified live: import_job, import_file, import_record, ai_request, agent_usage_period, agent_memory). The one exception, agent_execution, correctly has no updated_at column at all and no trigger — it is an append-only ledger, matching the immutability precedent ai_request itself established for the mechanical-log half of this schema (though ai_request still carries updated_at/deleted_at, since v1 gave it those columns; only the new agent_execution table is genuinely append-only-with-no-trigger).
• Soft delete on the reconciled-from-v1 pipeline tables (import_job, import_file, import_record, ai_request — all carry deleted_at timestamptz, nullable); no soft delete on the three new agent-runtime tables (agent_execution — append-only ledger; agent_usage_period — maintained counter, relevance fully captured by its quantities; agent_memory — lifecycle fully captured by status).
• Agent-as-actor attribution, continuing the canonical pattern — every *_actor_id column (created_by_actor_id, reviewed_by_actor_id, updated_by_actor_id) targets identity.actor (the polymorphic root), never identity.identity_user directly, matching crm/inventory's native pattern. agent_execution/agent_usage_period instead attribute to identity.agent_identity directly (agent_identity_id NOT NULL) — the acting party for these two tables is definitionally an agent, not a general actor.
• v1 baseline vs. this build — a delta, not a from-scratch design. v1's locked docs/old/schema/schema_modules/schema_ai.md (2026-06-11) specified 4 tables / 71 cols for this module — independently confirmed accurate to v1's real (pre-agent-infrastructure) design, not stale. This build's delta: +3 tables (4→7: agent_execution, agent_usage_period, agent_memory), +47 columns (71→118).
• Reference-don't-copy discipline preserved from v1 (DR4) — ai_request never stores prompt text or response content, only mechanical metadata (token counts, cost, latency, status). This is unchanged from v1 and still enforced by convention (no DB mechanism forces it — a service-layer discipline, same caveat v1 always carried).
• One deferred forward-ref, unchanged from v1 — import_file.file_id, a plain nullable uuid with no FK, because the files schema does not exist in v2. Verified live via psql \dn: as of this build the only application schemas present are identity, multi_loc, platform, shared, crm, inventory, ai (plus Postgres/Supabase system schemas). Same treatment as inventory.stock_movement.photo_ref/item_image.file_id and crm.customer_tax_certificate.document_ref.
• Four verification-caught fixes applied before this schema was built — see the dedicated section below. All four were caught by independent verification against this project's own already-locked precedents, not invented from scratch.
• Phase 2 (2026-07-13) adds a genuinely global, non-tenant-scoped layer — 12 of the 14 new tables carry no tenant_id at all and have no RLS policy (provider_registry, model_family, model_version, model_deployment, model_deployment_limit, model_deployment_region, model_deployment_policy, model_deployment_override, model_deployment_status_observation, prompt_definition, prompt_version, prompt_model_compatibility — admin/service_role-managed reference + operational config, mirroring platform.module_catalog's own Phase 1 precedent). authenticated gets SELECT only on all 12 — an explicit REVOKE INSERT, UPDATE, DELETE is present on each, since Phase 1's own lock-gate finding proved this schema's ALTER DEFAULT PRIVILEGES grants write access to every new table by default. The remaining 2 new tables ARE tenant-scoped: routing_policy.tenant_id is nullable (NULL = platform-wide default policy, mirroring platform.ai_capacity_policy's own scope-column shape) with no RLS policy either (both scopes must stay visible to the not-yet-built resolution function regardless of which tenant asks); agent_memory_source.tenant_id is NOT NULL, RLS-enabled, matching the original schema's tenant-scoped majority.
• Phase 2 partitions agent_execution and agent_memory BY RANGE(created_at), monthly — the first partitioned tables in this codebase's history. Both gain UNIQUE(id, tenant_id, created_at), the composite-identity shape agent_memory_source's own composite FK resolves against. Two invariants that used to live on a native partial-unique index (agent_execution's at-most-one-resolver + idempotency dedup, agent_memory's at-most-one-active-per-key) cannot survive partitioning as native indexes — a native unique index on a partitioned table must include the partition key, which would silently narrow a global invariant to per-partition-month scope. All three are now enforced by BEFORE INSERT (or BEFORE INSERT OR UPDATE OF status) triggers instead — see each table's own section below.
• Phase 2 triggers added: ai.check_agent_execution_single_resolver(), ai.check_agent_execution_idempotency() (both on agent_execution), ai.check_agent_memory_single_active() (on agent_memory), ai.check_model_deployment_override_residency() (on model_deployment_override) — 4 new trigger functions, all ai-schema-owned (unlike the shared platform.set_updated_at()).
• Phase 2 BLOCKER found and fixed by independent lock-gate verification, disclosed here — Postgres GRANT and RLS on a partitioned PARENT table do NOT propagate to its child partitions; every one of agent_execution/agent_memory's 28 partition-child relations (14 monthly-or-default × 2 tables) was left with a full authenticated INSERT/SELECT/UPDATE/DELETE grant and RLS disabled by the schema's own ALTER DEFAULT PRIVILEGES, despite the parent tables' correct GRANT/RLS setup — live-exploited as a genuine cross-tenant read leak AND write-forgery vector via direct partition access (e.g. SELECT * FROM ai.agent_execution_2026_07, bypassing the parent entirely). Fixed: REVOKE ALL ... FROM authenticated applied to all 28 partitions, forcing every access path through the parent; the migration itself now applies this automatically via a pg_inherits-driven DO block. See PROJECT_DECISIONS #63's own verification addendum for the full live-reproduction transcript.

Cross-Phase Foreign Keys (ai)

Column	Target	Notes
import_job.tenant_id, import_file.tenant_id, import_record.tenant_id, agent_execution.tenant_id, agent_usage_period.tenant_id, agent_memory.tenant_id	platform.tenant	NOT NULL, intra-tenancy, enforced on 6 of 7 tables.
ai_request.tenant_id	platform.tenant	Nullable — the sole exception. Platform-level AI calls (no tenant context) write NULL via service_role.
ai_request.agent_identity_id, agent_execution.agent_identity_id, agent_usage_period.agent_identity_id	identity.agent_identity.id	ai_request.agent_identity_id nullable (NEW this pass — v1 predates agent_identity entirely). agent_execution.agent_identity_id/agent_usage_period.agent_identity_id NOT NULL — every agent belongs to exactly one tenant, so no platform-level-nullable case applies to these two.
agent_execution.permission_id	identity.permission.id	Nullable. Cross-schema, enforced.
agent_execution.resolves_execution_id	ai.agent_execution.id (self)	Nullable self-FK. Verification-caught fix — see dedicated section.
agent_execution.ai_request_id	ai.ai_request.id	Nullable. Intra-schema, enforced. Links an execution to the specific inference call (if any) that produced it.
import_file.import_job_id, import_record.import_file_id, import_record.import_job_id	ai.import_job, ai.import_file	Intra-schema, enforced.
import_job.created_by_actor_id, import_record.reviewed_by_actor_id, agent_memory.created_by_actor_id/updated_by_actor_id	identity.actor	Nullable throughout. Cross-schema, enforced.
import_job.setup_task_code	(none — text seam, not an FK)	References platform.tenant_setup_task.task_code by convention only. Unchanged from v1 (DR5) — import_job owns import lifecycle status independently of tenant_setup_task's task-level retry (retry_count/next_retry_at), not duplicated here.
import_file.file_id	(none — deferred)	Plain NOT NULL UUID, no FK. The files schema does not exist in v2 (verified live).
import_record.target_module/target_table/target_row_id	(none — polymorphic, not an FK)	All-or-nothing CHECK enforces coherence instead of a real FK — same pattern audit_log uses elsewhere in the platform.
agent_execution.target_module/target_table/target_row_id	(none — polymorphic, not an FK)	Reuses import_record's exact established pattern for tagging what an action touched — distinct from the propose→execute linkage, which resolves_execution_id handles (see below).
agent_execution.resolves_execution_id + resolves_execution_created_at	ai.agent_execution.id/.tenant_id/.created_at (self, composite)	Phase 2. Composite self-FK, replacing the pre-Phase-2 plain self-FK + partial-unique index — a native partial unique cannot survive partitioning. resolves_execution_created_at is the write-once shadow column the FK itself validates; the at-most-one-resolver guarantee is now trigger-enforced (check_agent_execution_single_resolver), not index-enforced.
routing_policy.tenant_id	platform.tenant	Phase 2. Nullable — NULL = platform-wide default policy; set = a tenant-specific override. No RLS (see Global rules).
routing_policy.workload_class_id	(none — deferred forward-ref)	Phase 2. Plain nullable uuid, no FK — forward-ref to agents.workload_class (Phase 5, not yet built). Logged to OPEN_ITEMS.md and CROSS_MODULE_CONTRACTS.md.
agent_memory_source.tenant_id	platform.tenant	Phase 2. NOT NULL. RLS-enabled — the one Phase 2 table besides agent_execution/agent_memory that is genuinely tenant-scoped.
agent_memory_source.agent_memory_id + agent_memory_created_at	ai.agent_memory.id/.tenant_id/.created_at (composite)	Phase 2. Real, enforced composite FK — despite agent_memory now being partitioned. agent_memory_created_at is a write-once shadow column the FK constraint itself validates (no sync trigger needed — agent_memory rows are never updated after created_at is set). source_ref (the other half of the join, polymorphic across 4 possible targets) deliberately carries no FK, matching tax.tax_calculation.source_ref's established convention.
model_family.provider_registry_id, model_deployment.model_version_id/.provider_registry_id, model_deployment_limit/_region/_policy/_override/_status_observation.model_deployment_id, prompt_version.prompt_definition_id, prompt_model_compatibility.prompt_version_id/.model_version_id, model_version.model_family_id	(intra-schema, all enforced)	Phase 2. The registry/deployment/prompt chain's own internal FKs — see each table's section below for the full shape. None cross a schema boundary.
model_deployment.rollback_target_deployment_id, .previous_deployment_id	ai.model_deployment.id (self)	Phase 2. Both nullable self-FKs — previous_deployment_id chains a traffic-weight change back to the row it superseded (never-overwrite discipline); rollback_target_deployment_id names the deployment an auto-rollback would restore.
model_deployment_override.temporary_tenant_restriction	platform.tenant	Phase 2. Nullable — NULL = platform-wide override; set = scoped to one tenant, the input the residency-guard trigger reads.

---

ai.import_job

Import batch header — one row per tenant import run, either onboarding-gated (zero-mapping ingestion during signup) or a manual post-onboarding re-import. Reconciled from v1's locked schema_ai.md (2026-06-11): core shape unchanged, the only touch this pass is created_by_user_id → created_by_actor_id (FK retarget to identity.actor, matching the canonical autonomy pattern every other module already applies). Owns the import lifecycle status independently of platform.tenant_setup_task, which owns task-level retry (retry_count/next_retry_at) — DR5, not duplicated here.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy import_job_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
source	text	NOT NULL	—	CHECK IN (onboarding,manual)
setup_task_code	text	nullable	—	Text seam, not an FK — references platform.tenant_setup_task.task_code by convention. Coherence enforced with source — see table CHECK
target_entity	text	NOT NULL	—	CHECK IN (inventory_item,customer,vendor,mixed)
status	text	NOT NULL	'uploading'	CHECK IN (uploading,processing,review_pending,loading,completed,failed,cancelled)
file_count	integer	NOT NULL	0
record_total	integer	nullable	—
record_loaded	integer	NOT NULL	0
record_rejected	integer	NOT NULL	0
started_at	timestamptz	nullable	—
completed_at	timestamptz	nullable	—
created_by_actor_id	UUID	nullable	—	FK → identity.actor. Retargeted this pass from v1's created_by_user_id
failure_reason	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (verified live):

Name	Condition
chk_import_job_source	source IN ('onboarding','manual')
chk_import_job_target_entity	target_entity IN ('inventory_item','customer','vendor','mixed')
chk_import_job_status	status IN ('uploading','processing','review_pending','loading','completed','failed','cancelled')
chk_import_job_source_task_code_coherence	(source = 'manual' AND setup_task_code IS NULL) OR (source = 'onboarding' AND setup_task_code IS NOT NULL)

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on source
• Composite index on (tenant_id, created_at)
• Partial index on setup_task_code WHERE NOT NULL
• Partial index on status WHERE IN (processing,review_pending,loading) — active-job query

---

ai.import_file

One uploaded file within an import job — detected type/entity, inferred column mapping and its confidence, parse status. Reconciled from v1, core shape unchanged.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy import_file_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
import_job_id	UUID	NOT NULL	—	FK → ai.import_job
file_id	UUID	NOT NULL	—	DEFERRED forward-ref, plain UUID, NO FK — the files schema doesn't exist in v2. See Global rules
original_filename	text	NOT NULL	—
detected_type	text	nullable	—	CHECK IN (csv,excel,pdf,image,other) OR NULL
detected_entity	text	nullable	—	CHECK IN (inventory_item,customer,vendor,unknown) OR NULL
inferred_mapping	jsonb	nullable	—	Column→field mapping guess
mapping_confidence	numeric	nullable	—
status	text	NOT NULL	'uploaded'	CHECK IN (uploaded,parsing,parsed,failed)
row_count	integer	nullable	—
parse_error	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (verified live):

Name	Condition
chk_import_file_detected_type	detected_type IS NULL OR detected_type IN ('csv','excel','pdf','image','other')
chk_import_file_detected_entity	detected_entity IS NULL OR detected_entity IN ('inventory_item','customer','vendor','unknown')
chk_import_file_status	status IN ('uploaded','parsing','parsed','failed')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on import_job_id
• Plain index on file_id
• Partial index on status WHERE IN (uploaded,parsing,failed)

---

ai.import_record

One parsed row per file — the load-bearing table of the import pipeline. Reconciled from v1, core shape unchanged, including both existing DB-level CHECK safety rails, which are non-negotiable per rationale_ai.md DR2. Only touch this pass: reviewed_by_user_id → reviewed_by_actor_id.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy import_record_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
import_file_id	UUID	NOT NULL	—	FK → ai.import_file
import_job_id	UUID	NOT NULL	—	FK → ai.import_job
source_row_number	integer	nullable	—
raw_data	jsonb	NOT NULL	—	The parsed row exactly as read
mapped_data	jsonb	nullable	—	Field-mapped version of raw_data
confidence_score	numeric	nullable	—
review_status	text	NOT NULL	'pending_review'	CHECK IN (pending_review,auto_accepted,accepted,rejected)
corrected_data	jsonb	nullable	—	Human correction, if any, over mapped_data
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor. Retargeted this pass from v1's reviewed_by_user_id
reviewed_at	timestamptz	nullable	—
load_status	text	NOT NULL	'pending'	CHECK IN (pending,loaded,failed,skipped)
target_module	text	nullable	—	Polymorphic backref, not an FK — see table CHECK
target_table	text	nullable	—	Polymorphic backref, not an FK
target_row_id	UUID	nullable	—	Polymorphic backref, not an FK
load_error	text	nullable	—
loaded_at	timestamptz	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (verified live):

Name	Condition
chk_import_record_review_status	review_status IN ('pending_review','auto_accepted','accepted','rejected')
chk_import_record_load_status	load_status IN ('pending','loaded','failed','skipped')
chk_import_record_load_decision_consistency	load_status <> 'loaded' OR (review_status IN ('auto_accepted','accepted') AND target_module IS NOT NULL AND target_table IS NOT NULL AND target_row_id IS NOT NULL) — a record cannot be marked loaded without also being accepted AND fully resolved to a target row
chk_import_record_target_all_or_nothing	(target_module IS NULL AND target_table IS NULL AND target_row_id IS NULL) OR (target_module IS NOT NULL AND target_table IS NOT NULL AND target_row_id IS NOT NULL) — the polymorphic backref is either fully set or fully unset, never partial

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on import_file_id
• Plain index on import_job_id
• Partial index on review_status WHERE = 'pending_review'
• Partial index on load_status WHERE IN (pending,failed)
• Composite partial index on (target_module, target_row_id) WHERE target_row_id IS NOT NULL

---

ai.ai_request

Bedrock-call log — the mechanical record of every inference request routed through AIService. Reconciled from v1, core shape unchanged, including the reference-don't-copy discipline (DR4: never prompt text or response content, ever). tenant_id stays nullable — platform-level AI calls (e.g. shared.plant enrichment) have no tenant context and are written via service_role, unchanged from v1's own design.

agent_identity_id is NEW this pass (nullable FK → identity.agent_identity) — v1 predates agent_identity entirely, so ai_request had no concept of "which agent triggered this call." This closes that gap and is now the authoritative attribution path agent_usage_period.total_tokens' reconciliation formula joins on directly (see that table's section and the dedicated verification-fixes section below).

Mixed-scope — the one exception in this schema. tenant_id nullable, FK → platform.tenant. RLS enabled — permissive policy ai_request_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	nullable	—	FK → platform.tenant. NULL = platform-level call, no tenant context
agent_identity_id	UUID	nullable	—	FK → identity.agent_identity. NEW this pass — verification-caught fix, see dedicated section
caller_module	text	NOT NULL	—	CHECK IN (ai_import,sage,notifications,enrichment,anomaly,platform)
feature	text	nullable	—
model_id	text	NOT NULL	—	Bedrock model identifier
request_type	text	nullable	—	CHECK IN (completion,embedding,vision) OR NULL
prompt_token_count	integer	nullable	—
completion_token_count	integer	nullable	—
total_token_count	integer	nullable	—	Authoritative token count this row contributes to agent_usage_period.total_tokens's reconciliation
cost_millicents	bigint	nullable	—	Milli-cent precision cost
latency_ms	integer	nullable	—
status	text	NOT NULL	'success'	CHECK IN (success,failed,rate_limited,timeout)
error_detail	text	nullable	—
idempotency_key	text	nullable	—	UNIQUE per tenant when set — partial-unique, see indexes
requested_at	timestamptz	NOT NULL	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (verified live):

Name	Condition
chk_ai_request_caller_module	caller_module IN ('ai_import','sage','notifications','enrichment','anomaly','platform')
chk_ai_request_request_type	request_type IS NULL OR request_type IN ('completion','embedding','vision')
chk_ai_request_status	status IN ('success','failed','rate_limited','timeout')

Indexes:

• PK on id
• Partial index on tenant_id WHERE NOT NULL
• Partial index on agent_identity_id WHERE NOT NULL
• Plain index on caller_module
• Plain index on model_id
• Partial index on status WHERE <> 'success'
• Plain index on requested_at
• ai_request_tenant_id_idempotency_key_unique — UNIQUE, btree (tenant_id, idempotency_key) WHERE tenant_id IS NOT NULL AND idempotency_key IS NOT NULL AND deleted_at IS NULL
• ai_request_null_tenant_idempotency_key_unique — UNIQUE, btree (idempotency_key) WHERE tenant_id IS NULL AND idempotency_key IS NOT NULL AND deleted_at IS NULL — post-build Section 4 audit fix, see below

Two-partial-unique NULL-tenant fix (post-build Section 4 audit). tenant_id is nullable on this table (platform-level calls have no tenant context). The original single UNIQUE (tenant_id, idempotency_key) let two NULL-tenant rows share the same idempotency_key — Postgres treats NULL as distinct in unique indexes, so both inserted successfully (live-reproduced: two rows, same key, tenant_id both NULL, no conflict). This is the same bug class already caught and fixed multiple times this project (identity.role, the original agent_duty_grant design, inventory.stock/stock_lot) — missed here because this table was carried forward from v1 without re-auditing it against this exact trap. Fixed by splitting into a tenant-scoped index and a dedicated NULL-tenant index, mirroring inventory.stock/stock_lot's own two-partial-unique precedent. Live-tested: two NULL-tenant rows with the same idempotency_key are now rejected with duplicate key value violates unique constraint "ai_request_null_tenant_idempotency_key_unique".

---

ai.agent_execution

NEW table this build — the A6 execution ledger: a complete, business-language record of every agent action — what it did, why, what it read, what it cost, and (via resolves_execution_id) what earlier proposal it resolves. Append-only, no updated_at, matching ai_request's own immutability. Every agent belongs to exactly one tenant (identity.agent_identity.tenant_id is NOT NULL), so — unlike ai_request — there is no platform-level nullable-tenant case here.

PARTITIONED BY RANGE(created_at), monthly, as of Phase 2 (2026-07-13) — see the dedicated subsection below for the composite-identity resolution this required, the 2 new triggers, and why the old partial-unique indexes on idempotency_key/resolves_execution_id could not simply carry forward.

resolves_execution_id — verification-caught fix. The original design claimed a later human-approval/execution action writes a separate row sharing the same target_table/target_row_id as its link back to the proposal, and claimed this "reuses the exact pattern" inventory.stock_adjustment_request→stock_movement and crm.customer_merge_candidate→customer_merge established. Independent verification proved this false: both real, already-built precedents link via a dedicated FK column (stock_movement.adjustment_request_id, customer_merge.candidate_id), not a shared polymorphic key — and a 'proposed' action whose target row does not exist yet (e.g. drafting a not-yet-created purchase order) has target_row_id NULL, sharing literally no key with its later 'executed' row. The nullable self-FK resolves_execution_id closes this gap, mirroring the real precedent exactly: set on the later (executed/rejected/failed) row, pointing back at the 'proposed' row it resolves. Live-tested: the exact failure case the bug was about — a 'proposed' action for a not-yet-existing row (target_row_id NULL) — was inserted, then a follow-up 'executed' row (which now has a real target_row_id) was linked to it via resolves_execution_id; a direct JOIN on that column correctly retrieves the pair, something the old shared-polymorphic-key design had no mechanism for at all.

idempotency_key — verification-caught fix. Agent runtimes are exactly the retry-prone caller class this project has already been burned by once (crm.customer_merge_candidate shipped with no dedup protection, logged to OPEN_ITEMS, never fixed there) and fixed once (inventory.stock_movement's own idempotency_key). Omitting dedup protection from the first agent-specific ledger table was a real, independently-confirmed gap, closed by idempotency_key + UNIQUE (tenant_id, agent_identity_id, idempotency_key) WHERE idempotency_key IS NOT NULL, matching inventory.stock_movement.idempotency_key's exact precedent. Live-tested: inserting a duplicate idempotency_key for the same (tenant_id, agent_identity_id) is rejected with duplicate key value violates unique constraint "agent_execution_tenant_agent_idempotency_unique".

Tenant-scoped, append-only, PARTITIONED BY RANGE(created_at) as of Phase 2. tenant_id NOT NULL FK → platform.tenant. No updated_at, no deleted_at. RLS enabled — permissive policy agent_execution_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid. Append-only is now also GRANT-enforced (GRANT SELECT, INSERT; REVOKE UPDATE, DELETE) plus a trg_agent_execution_append_only trigger reusing platform.reject_append_only_mutation() — reinstalled verbatim on the post-swap table, not a new behavior.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	platform.uuid_generate_v7()	PK. Changed pre-Phase-2 — see Remediation Phase 2 below. Part of a composite PK (id, created_at) as of Phase 2's partitioning (Postgres requires the partition key in the PK of a partitioned table)
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
agent_identity_id	UUID	NOT NULL	—	FK → identity.agent_identity. Acting party — always a specific agent, never a general actor
permission_id	UUID	nullable	—	FK → identity.permission
authority_level_applied	text	nullable	—	CHECK IN (may_act_alone,draft_only,needs_approval) OR NULL. Point-in-time snapshot of what identity.agent_duty_grant.authority_level applied at action time — no FK, a snapshot not a live reference (documented relationship only)
action_code	text	NOT NULL	—	Business-language action identifier, e.g. inventory:stock_adjustment:propose
target_module	text	nullable	—	Polymorphic tag, not an FK — see table CHECK. Reuses import_record's tagging pattern
target_table	text	nullable	—	Polymorphic tag
target_row_id	UUID	nullable	—	Polymorphic tag
reasoning_summary	text	NOT NULL	—	Business-language "why" — always present, even for may_act_alone actions
evidence	jsonb	nullable	—	Supporting data the agent read/considered
confidence_score	numeric(3,2)	nullable	—
predicted_outcome	jsonb	nullable	—
status	text	NOT NULL	—	CHECK IN (proposed,executed,rejected,failed). No default — every row must state its status explicitly
resolves_execution_id	UUID	nullable	—	Self-FK → ai.agent_execution.id. Verification-caught fix — see above. Set on the later row, points back at the 'proposed' row it resolves. At-most-one-resolver now trigger-enforced, not index-enforced — see Phase 2 below
resolves_execution_created_at	timestamptz	nullable	—	NEW Phase 2. The write-once shadow half of the composite self-FK (resolves_execution_id, tenant_id, resolves_execution_created_at) → (id, tenant_id, created_at) — required because agent_execution is now partitioned and a plain single-column self-FK is impossible. chk_agent_execution_resolves_execution_created_at_pairing requires both-null-or-both-set with resolves_execution_id
ai_request_id	UUID	nullable	—	FK → ai.ai_request. Links to the specific inference call (if any) that produced this action
cost_millicents	bigint	nullable	—
idempotency_key	text	nullable	—	Dedup per (tenant, agent) when set — verification-caught fix, see above. Now trigger-enforced, not index-enforced — see Phase 2 below
created_at	timestamptz	NOT NULL	now()	Partition key as of Phase 2

No updated_at, no deleted_at — append-only execution ledger; an execution is a historical fact, never edited or removed.

CHECK constraints (verified live):

Name	Condition
chk_agent_execution_authority_level	authority_level_applied IS NULL OR authority_level_applied IN ('may_act_alone','draft_only','needs_approval')
chk_agent_execution_status	status IN ('proposed','executed','rejected','failed')
chk_agent_execution_target_module_table_together	(target_module IS NULL AND target_table IS NULL AND target_row_id IS NULL) OR (target_module IS NOT NULL AND target_table IS NOT NULL)
chk_agent_execution_resolves_execution_created_at_pairing	NEW Phase 2. (resolves_execution_id IS NULL) = (resolves_execution_created_at IS NULL) — the composite self-FK's shadow column is present iff the FK itself is used

chk_agent_execution_target_module_table_together — post-build Section 4 audit fix (renamed from chk_agent_execution_target_all_or_nothing). The original all-or-nothing form required target_row_id to be set whenever target_module/target_table were set, which made it structurally IMPOSSIBLE to tag a 'proposed' create-new-record action with what it's proposing — live-reproduced: inserting (target_module='crm', target_table='customer', target_row_id=NULL, status='proposed') was REJECTED by the original CHECK. This directly contradicted the documented purpose of resolves_execution_id (linking an untargeted proposal to its later fully-targeted execution). Relaxed so target_row_id is independently optional: either no targeting info at all, or target_module+target_table set together (still catching genuinely malformed partial state — one set without the other). Live-tested: the tagged create-new-record proposal now succeeds, and executing it (setting a real target_row_id + resolves_execution_id pointing back) also succeeds.

Remediation Phase 1 (2026-07-08)

A cross-cutting Remediation Plan Phase 1 pass (senior-architect review) added 2 more CHECK constraints on agent_execution, closing a C8 financial-autonomy boundary gap: an action could previously be recorded as status='executed' with no recorded authority level, or — for a needs_approval action — with no link back to the human decision that resolved it.

Name	Condition
chk_agent_execution_authority_required_when_executed	status <> 'executed' OR authority_level_applied IS NOT NULL — an executed action must carry the authority level that applied at execution time
chk_agent_execution_needs_approval_requires_resolution	status <> 'executed' OR authority_level_applied <> 'needs_approval' OR resolves_execution_id IS NOT NULL — an executed needs_approval action must link back to the proposal/decision it resolves

No column or table count change (118 cols unchanged). Full cross-module record: PROJECT_DECISIONS #37.

Remediation Phase 2 (2026-07-08)

A cross-cutting Remediation Plan Phase 2 pass changed agent_execution.id's PK-generation strategy: DEFAULT gen_random_uuid() → DEFAULT platform.uuid_generate_v7() (Item 6). agent_execution is one of the plan's 4 named "hot ledgers." Why: UUIDv7 is time-ordered, keeping future time-range partitioning possible on this append-only ledger without a PK rewrite — something impossible once data lands on a random UUIDv4 PK. DEFAULT-only change, no column/table count change (118 cols unchanged). Full cross-module record: PROJECT_DECISIONS #38.

Phase 2 (agents-v2/v3 build, 2026-07-13) — Partitioning + Composite Identity

PARTITIONED BY RANGE(created_at), monthly (E1/BLOCKER 2's composite-identity resolution) — one of the 2 tables this reopen partitioned (with agent_memory), and the first partitioned tables in this codebase's history. Migration mechanics: a real backfill (not an empty-table swap) — a new agent_execution_new table created with the partitioned shape, 13 monthly partitions (2026_06 through 2027_06) plus a DEFAULT catch-all, INSERT ... SELECT from the old table (a self-join resolves resolves_execution_created_at), a row-count + zero-unresolved-shadow verification, then DROP+RENAME. A mandatory pre-migration audit found 948 live rows, all created_at within a single month — 270 with resolves_execution_id set, 135 with idempotency_key set — plus one genuine pre-existing data-integrity violation (a dev-seed row's permission_id referenced a deleted identity.permission row despite a live FK; nulled out, disclosed, not silently dropped). See PROJECT_DECISIONS #63 for the full writeup, including a disclosed post-migration cleanup mistake (an overly-broad DELETE during live-reproduction removed 270 pre-existing dev-seed rows sharing a generic action_code prefix — harmless, but disclosed).

2 genuine gaps neither the v2 nor v3 design addressed, found during this phase: a native cross-partition unique index is structurally impossible (Postgres requires a partitioned table's unique index to include the partition key), and neither invariant this table depends on could be silently narrowed to per-partition-month scope without a real regression:

1. At-most-one-resolver. The old agent_execution_resolves_execution_id_unique partial-unique index is DROPPED. Existence is now guaranteed by the composite self-FK (resolves_execution_id, tenant_id, resolves_execution_created_at) → (id, tenant_id, created_at); the at-most-one-resolver guarantee itself is enforced by a new BEFORE INSERT trigger, ai.check_agent_execution_single_resolver(), which row-locks the TARGET execution row first (matching signals.lock_experiment_causal_basis's proven shape from the v3 design), then rejects a second resolver. Live-tested: a second execution resolving an already-resolved proposal is rejected ("execution ... is already resolved by execution ... at most one resolver is allowed").
2. Idempotency dedup. The old agent_execution_tenant_agent_idempotency_unique partial-unique index is DROPPED (replaced by a plain, non-unique supporting index of the same shape, for the trigger's own lookup). A new BEFORE INSERT trigger, ai.check_agent_execution_idempotency(), uses pg_advisory_xact_lock (transaction-scoped, no manual unlock) to serialize concurrent inserts sharing a (tenant_id, agent_identity_id, idempotency_key) key before checking for an existing match — the same class of mechanism this codebase's own outcome_authority fix used before being superseded there by a native upsert (native isn't possible here, so the advisory-lock pattern is the correct tool, not a downgrade). Live-tested: a second execution with the same (tenant_id, agent_identity_id, idempotency_key) is rejected ("idempotency_key ... already used by execution ...").

Also gains UNIQUE(id, tenant_id, created_at) (agent_execution_id_tenant_created_unique) — the composite-identity shape ai.agent_memory_source's own composite FK resolves against, and the shape Phase 5's agents.agent_decision (the v3 cascade table) will FK against.

No table-count change to this row (still 1 logical table); column count 19→20 (+1, resolves_execution_created_at). Full cross-module record: PROJECT_DECISIONS #63.

Indexes:

• PK on (id, created_at) — composite as of Phase 2 partitioning
• Plain index on tenant_id
• Composite index on (agent_identity_id, created_at)
• Composite partial index on (target_module, target_row_id) WHERE target_row_id IS NOT NULL
• Partial index on resolves_execution_id WHERE NOT NULL
• Partial index on ai_request_id WHERE NOT NULL
• agent_execution_id_tenant_created_unique — UNIQUE, btree (id, tenant_id, created_at) — NEW Phase 2, the composite-identity shape agent_memory_source FKs against
• Plain (non-unique) index on (tenant_id, agent_identity_id, idempotency_key) WHERE idempotency_key IS NOT NULL — Phase 2: supports check_agent_execution_idempotency()'s own lookup; the old partial-unique index of the same shape (agent_execution_tenant_agent_idempotency_unique) is DROPPED, since it cannot survive partitioning — see Phase 2 subsection above
• agent_execution_resolves_execution_id_unique — DROPPED Phase 2 (cannot survive partitioning); superseded by the composite self-FK + check_agent_execution_single_resolver() trigger, see Phase 2 subsection above

Triggers (3, all BEFORE): trg_agent_execution_append_only (BEFORE DELETE OR UPDATE, reuses platform.reject_append_only_mutation() — pre-existing, reinstalled verbatim on the post-swap table), trg_agent_execution_single_resolver (BEFORE INSERT, NEW Phase 2), trg_agent_execution_idempotency (BEFORE INSERT, NEW Phase 2). No set_updated_at trigger — this table has no updated_at column (append-only).

---

ai.agent_usage_period

NEW table this build — the A7 usage/value meter: a maintained, incrementally-updated counter of cumulative cost/tokens/quantity per agent per period. Closes the volume-abuse gap identity.agent_duty_grant.spend_limit_cents already documents as a known security limitation (per-action only, no cumulative/period tracking).

Deliberately not append-only — the enforcement use case ("has this agent exceeded its cumulative limit," checked at action time) needs O(1) lookup against a maintained row, not an aggregate scan over a growing ledger. This mirrors inventory.stock's own maintained-cache-with-documented-formula precedent, not platform.tenant_usage_summary's append-only-snapshot shape — that table only validates the period_start/period_end column shape as precedent; it is itself an insert-once snapshot with no updated_at, not a mutation-pattern precedent. Independent verification caught the original design overclaiming this distinction.

Reconciliation formulas (documented per SCHEMA_DESIGN_RUNBOOK.md Item I):

• total_cost_cents = SUM(agent_execution.cost_millicents) / 1000 for this (agent_identity_id, period).
• total_tokens = SUM(ai_request.total_token_count) WHERE ai_request.agent_identity_id = this row's agent_identity_id AND ai_request.requested_at falls within [period_start, period_end). CORRECTED post-verification: this sums directly off ai_request.agent_identity_id (the authoritative attribution path added this pass), not indirectly through agent_execution.ai_request_id. The indirect-only path silently undercounts whenever an LLM call is attributed to an agent but its agent_execution row was never logged — a real risk this design's own negative-space walk (D5) named. The direct path is the one actually implemented and documented here.

Build requirement — every increment MUST be a single atomic UPSERT, documented as a requirement, not just prose:

INSERT INTO ai.agent_usage_period (
  tenant_id, agent_identity_id, period_start, period_end,
  action_count, total_cost_cents, total_quantity, total_tokens, last_action_at
)
VALUES (
  $1, $2, $3, $4,
  $5, $6, $7, $8, $9
)
ON CONFLICT (tenant_id, agent_identity_id, period_start)
DO UPDATE SET
  action_count      = agent_usage_period.action_count      + EXCLUDED.action_count,
  total_cost_cents   = agent_usage_period.total_cost_cents   + EXCLUDED.total_cost_cents,
  total_quantity     = agent_usage_period.total_quantity     + EXCLUDED.total_quantity,
  total_tokens       = agent_usage_period.total_tokens       + EXCLUDED.total_tokens,
  last_action_at     = EXCLUDED.last_action_at;

This must never be an application-level read-then-write. Independent verification confirmed two real races an app-level read-modify-write would hit: a lost-update race (two concurrent same-period increments, where the second writer's read is stale by the time it writes) and a duplicate-insert race (two concurrent first-actions-of-a-new-period, both racing to INSERT a fresh row before either commits). The ON CONFLICT ... DO UPDATE clause makes the insert-or-increment a single statement Postgres serializes correctly, closing both races at the database level rather than relying on application-level locking. This requirement is documented both as a SQL comment in the migration file (packages/db/migrations/20260706080000_ai_module.sql) and here, per the runbook's own discipline of not leaving a build requirement as service-layer-only prose.

Tenant-scoped, maintained counter. tenant_id NOT NULL FK → platform.tenant. No deleted_at. RLS enabled — permissive policy agent_usage_period_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / 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
period_start	date	NOT NULL	—	Part of the UPSERT conflict target — see indexes
period_end	date	NOT NULL	—
action_count	integer	NOT NULL	0	Incremented atomically — see build requirement above
total_cost_cents	bigint	NOT NULL	0	Reconciliation formula: SUM(agent_execution.cost_millicents)/1000 — see above
total_quantity	numeric	NOT NULL	0
total_tokens	integer	NOT NULL	0	Corrected reconciliation formula — direct via ai_request.agent_identity_id, not indirect — see above
last_action_at	timestamptz	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

No CHECK constraints on this table (verified live — none defined; all validity is structural, via the UNIQUE conflict target and NOT NULL defaults).

Indexes:

• PK on id
• Plain index on tenant_id
• agent_usage_period_tenant_agent_period_unique — UNIQUE, btree (tenant_id, agent_identity_id, period_start) — the atomic-UPSERT conflict target; not a partial index, since every row participates in the dedup regardless of any status

---

ai.agent_memory

NEW table this build — the B12/G5 tenant operating-memory store: "a governed memory of how this specific business operates," each entry sourced, confidence-rated, viewable, editable, disableable, and never auto-applied to financial actions (a runtime consumption rule, not a schema CHECK — nothing here can express "never used to gate a payment" at the database level). Tenant-scoped, not agent-scoped — multiple agents/capabilities read the same learned pattern (B12: feeds B1/B2/B6/B8).

This is the real target decision_provenance.memory_refs finally points at. That JSONB key is documented on crm.customer/crm.customer_segment_membership-equivalent tables and inventory.item/item_variant/stock/stock_adjustment_request/stock_count/item_merge_candidate/item_merge, but in both of those modules' own passes it pointed at nothing — no memory table existed yet. This build gives it a real target: ai.agent_memory.id.

Unique index — verification-caught fix. The original design used UNIQUE (tenant_id, category, key) with no partial WHERE clause, reasoning "one row per concept, ever; disabling doesn't free the key; a superseding memory re-enables/updates the same row." Independent verification proved this contradicts an already-locked precedent in this same codebase for the identical situation: identity.agent_duty_grant's own unique index is UNIQUE (agent_identity_id, permission_id, scope_type) WHERE status = 'active' — a partial unique on a table with the identical active/suspended/revoked-style status lifecycle, which deliberately does let a fresh row exist once the old one is retired. agent_memory's index was corrected to match that precedent exactly: UNIQUE (tenant_id, category, key) WHERE status = 'active'. A superseding memory is a fresh INSERT (which also resolves the "what gets reset" ambiguity verification raised — there is nothing to reset on a brand-new row). Live-tested 3 scenarios: (a) two simultaneous status='active' rows for the same (tenant_id, category, key) → REJECTED; (b) disable the first row (status='disabled'), then insert a fresh status='active' row for the same key → SUCCEEDS; (c) after (b), both rows persist (2 total rows) — the disabled row is preserved as history, not destructively overwritten. As of Phase 2 (2026-07-13) this partial-unique index is DROPPED (cannot survive partitioning) and the same invariant is enforced by a trigger instead — see the Phase 2 subsection below.

PARTITIONED BY RANGE(created_at), monthly, as of Phase 2 (2026-07-13) — see the dedicated subsection below for the composite-identity resolution, the new trigger replacing the partial-unique index above, and the 3 new C4 governance columns.

Tenant-scoped, PARTITIONED BY RANGE(created_at) as of Phase 2. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy agent_memory_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

No soft delete — verified live, no deleted_at column; lifecycle fully captured by status.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK — the real target decision_provenance.memory_refs now resolves to. Part of a composite PK (id, created_at) as of Phase 2's partitioning
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
category	text	NOT NULL	—	Grouping concept, e.g. pricing_preference, seasonal_pattern
key	text	NOT NULL	—	Unique per (tenant, category) while active — see unique index fix above (now trigger-enforced, Phase 2)
content	jsonb	NOT NULL	—	The learned fact/preference/pattern itself
source	text	NOT NULL	—	Provenance of how this memory was formed (free text this pass — no CHECK constrains its vocabulary)
confidence_score	numeric(3,2)	nullable	—
status	text	NOT NULL	'active'	CHECK IN (active,disabled,tombstoned,redacted,retained_for_compliance). Widened Phase 2 (was active/disabled only) — the C4 governance lifecycle. Partial-unique-turned-trigger fix applies here — see above
created_by_actor_id	UUID	nullable	—	FK → identity.actor
updated_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
decision_provenance	jsonb	nullable	—
last_used_at	timestamptz	nullable	—
subject_type	text	nullable	—	Added Remediation Phase 4. Polymorphic subject-type tag, e.g. 'customer' — no fixed CHECK vocabulary this pass. Must be both-NULL or both-set with subject_ref — see chk_agent_memory_subject_consistency below
subject_ref	UUID	nullable	—	Added Remediation Phase 4. Polymorphic reference, target row named by subject_type — no FK (cannot FK a polymorphic column). GDPR/erasure-scoping seam, not yet consumed by any erasure job or service
expires_at	timestamptz	nullable	—	Added Remediation Phase 4. Independent retention deadline for this memory entry, separate from status/last_used_at
retention_until	timestamptz	nullable	—	NEW Phase 2 (C4 governance). A HARD floor — the (not-yet-built) source-deletion-propagation job must not tombstone a row while this is still in the future, or while status='retained_for_compliance', regardless of source-table state
memory_class	text	nullable	—	NEW Phase 2 (C4 governance). CHECK IN (episodic,semantic,procedural) OR NULL — see chk_agent_memory_class below
tenant_inspectable	boolean	NOT NULL	true	NEW Phase 2 (C4 governance). Whether the tenant's own owner console may surface this memory entry for inspection
created_at	timestamptz	NOT NULL	now()	Partition key as of Phase 2
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints (verified live):

Name	Condition
chk_agent_memory_status	Widened Phase 2. status IN ('active','disabled','tombstoned','redacted','retained_for_compliance') (was ('active','disabled'))
chk_agent_memory_automation_source	automation_source IN ('human','agent','system','seed')
chk_agent_memory_subject_consistency	Added Remediation Phase 4. (subject_type IS NULL) = (subject_ref IS NULL) — a true bidirectional requirement: both NULL, or both set, never just one. Live-reproduced all 4 combinations: subject_type set/subject_ref NULL → rejected; subject_type NULL/subject_ref set → rejected (the reverse case, independently confirmed, not just the obvious direction); both NULL → accepted; both set → accepted
chk_agent_memory_class	NEW Phase 2. memory_class IS NULL OR memory_class IN ('episodic','semantic','procedural')

Indexes:

• PK on (id, created_at) — composite as of Phase 2 partitioning
• Plain index on tenant_id
• Composite index on (tenant_id, category)
• Plain (non-unique) index on (tenant_id, category, key) — Phase 2: supports check_agent_memory_single_active()'s own lookup; the old partial-unique index of the same shape (agent_memory_tenant_category_key_active_unique) is DROPPED, since it cannot survive partitioning — see Phase 2 subsection below
• agent_memory_id_tenant_created_unique — UNIQUE, btree (id, tenant_id, created_at) — NEW Phase 2, the composite-identity shape agent_memory_source FKs against
• agent_memory_subject_idx — Added Remediation Phase 4. Composite index on (subject_type, subject_ref) WHERE subject_ref IS NOT NULL
• agent_memory_tenant_category_key_active_unique — DROPPED Phase 2 (cannot survive partitioning); superseded by check_agent_memory_single_active(), see Phase 2 subsection below

Triggers (2): set_updated_at (unchanged, via platform.set_updated_at()), trg_agent_memory_single_active (BEFORE INSERT OR UPDATE OF status, NEW Phase 2).

Remediation Phase 4 (2026-07-08)

Item 18 — GDPR/Erasure + Agent Memory. ai.agent_memory gains 3 new columns: subject_type (text, nullable), subject_ref (UUID, nullable), expires_at (timestamptz, nullable) — plus chk_agent_memory_subject_consistency CHECK ((subject_type IS NULL) = (subject_ref IS NULL)), a true bidirectional requirement: both null or both set, never just one.

This is a GDPR/erasure-scoping seam, not a fully-built erasure feature. subject_ref is a polymorphic reference — the target row's type is named by subject_type (e.g. 'customer') — so it deliberately carries no FK (a polymorphic column cannot be FK'd to a single target table, the same structural constraint already documented on agent_execution.target_row_id and import_record.target_row_id elsewhere in this schema). Most memory entries have no individual subject at all and leave both columns NULL (a tenant-wide learned pattern, e.g. a seasonal pricing preference, is not "about" any one customer); subject_type/subject_ref exist for the narrower case where a memory entry pertains to a specific identifiable subject and therefore needs to be discoverable/erasable if that subject exercises a GDPR erasure right.

Not yet consumed by any erasure job or service — this migration adds the seam only. No scheduled sweep, no AIService method, and no cross-table tenant/subject-consistency enforcement exist yet (the same class of gap already disclosed for decision_provenance.memory_refs in DR-74/OPEN_ITEMS row 9 — JSONB/polymorphic references cannot carry FK constraints in Postgres, so this remains a service-layer validation responsibility whenever an erasure workflow is built).

Migration: packages/db/migrations/20260709040000_phase4_item18_gdpr_erasure.sql (the ai.agent_memory ALTER + CHECK; the same migration also adds crm.customer.pii_vault_ref, out of scope for this schema doc). Module stays 7 tables, goes from 118 → 121 columns (+3, all on agent_memory). Live-reproduced all 4 combinations of the new CHECK; independently verified by 2 adversarial lenses (Lens A live-tested all 4 combinations directly; Lens B confirmed the scope/disclosure claims). Full record: PROJECT_DECISIONS #40 (Item 18).

Phase 2 (agents-v2/v3 build, 2026-07-13) — Partitioning + C4 Memory Governance

PARTITIONED BY RANGE(created_at), monthly — the same E1/BLOCKER 2 composite-identity resolution agent_execution received (see that table's own Phase 2 subsection above for the shared migration mechanics: a new agent_memory_new table, 13 monthly partitions + a DEFAULT catch-all, verification, DROP+RENAME). The pre-migration audit found 0 live rows in agent_memory — no backfill was needed, but the migration verifies the zero-row count defensively anyway rather than assuming it, matching this codebase's own "never assume, always verify" discipline.

Gap 3 (same class as agent_execution's own 2 gaps above): agent_memory_tenant_category_key_active_unique (the "at most one active row per (tenant, category, key)" invariant, established at this table's own original build by direct analogy to identity.agent_duty_grant's precedent) cannot survive partitioning as a native partial-unique index for the identical structural reason. Resolved via ai.check_agent_memory_single_active(), a BEFORE INSERT OR UPDATE OF status trigger using the same pg_advisory_xact_lock pattern as agent_execution's own idempotency fix; supersession (disable the old row, insert a fresh active row) re-verified to still work. Live-tested: a second status='active' row for the same (tenant_id, category, key) is rejected ("an active row already exists for tenant ..."); disabling the first row then inserting a fresh active row for the same key still succeeds.

Also gains UNIQUE(id, tenant_id, created_at) (agent_memory_id_tenant_created_unique) — the composite-identity shape ai.agent_memory_source's own composite FK resolves against (see that table's own section below).

C4 governance columns — 3 new columns implementing a durable-memory-lifecycle guard the original 2026-07-06 build's GDPR/erasure seam (Remediation Phase 4, Item 18) didn't yet cover: retention_until (a hard floor a future source-deletion-propagation job must respect), memory_class (an episodic/semantic/procedural classification, chk_agent_memory_class), tenant_inspectable (whether the tenant's own owner console may surface this entry, NOT NULL DEFAULT true). status's CHECK is widened from ('active','disabled') to ('active','disabled','tombstoned','redacted','retained_for_compliance') — the fuller governance lifecycle these columns imply.

No table-count change to this row (still 1 logical table); column count 18→21 (+3: retention_until, memory_class, tenant_inspectable). Full cross-module record: PROJECT_DECISIONS #63.

---

New Tables — agents-v2/v3 build, Phase 2 (2026-07-13)

14 new table definitions, all built this pass (PROJECT_DECISIONS #63; Migration packages/db/migrations/20260713000000_ai_registry_partition_memory_governance.sql). Disclosed count note: both PROJECT_DECISIONS #63's own summary sentence and the regression-test docstring (apps/api/src/platform/__tests__/ai-registry-partition-memory.spec.ts) describe this as "13 new tables"; independently cross-checking the Drizzle schema files (packages/db/src/schema/ai/registry.ts, prompt.ts, routing.ts, memory_source.ts) against the migration's own CREATE TABLE statements finds 14 distinct new table definitions (enumerated below) — an apparent off-by-one already present in both of those upstream artifacts. Not corrected there in this docs-only pass (out of scope — PROJECT_DECISIONS entries and test files are not touched here); disclosed rather than silently perpetuated, per this codebase's own convention. The table-count math itself is unaffected either way: ai is genuinely 49 tables live (7 original + 14 new table types + 28 partition-child tables from partitioning agent_execution/agent_memory — 13 monthly partitions + 1 DEFAULT catch-all each — confirmed via SELECT count(*) FROM information_schema.tables WHERE table_schema='ai').

12 of these 14 tables are global, non-tenant-scoped reference/config data — no tenant_id, no RLS, authenticated granted SELECT only (an explicit REVOKE INSERT, UPDATE, DELETE is present on each, since Phase 1's own lock-gate finding proved this schema's ALTER DEFAULT PRIVILEGES grants write access to every new table by default) — mirroring platform.module_catalog's own Phase 1 precedent for admin/service_role-managed reference data. The other 2 (routing_policy, agent_memory_source) carry tenant_id; agent_memory_source is RLS-enabled and tenant-scoped in the conventional sense, while routing_policy.tenant_id is nullable with no RLS (both platform-wide and tenant-specific rows must stay visible to the not-yet-built resolution function regardless of which tenant is asking).

C1 Registry — provider_registry → model_family → model_version

A 3-level LLM provider/model registry, the definition/version split (A2b) this codebase's skill_definition/skill_version pattern (Phase 5's agents module) also uses — root of the chain ai.model_deployment feeds off.

ai.provider_registry

The LLM provider catalog (Bedrock, direct Anthropic, etc.) — global reference data, admin-managed.

Global, non-tenant-scoped — no RLS. authenticated: SELECT only. No soft delete.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
provider_code	text	NOT NULL	—	UNIQUE — see indexes
display_name	text	NOT NULL	—
status	text	NOT NULL	'active'	CHECK IN (active,deprecated,retired)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints: chk_provider_registry_status — status IN ('active','deprecated','retired').

Indexes: PK on id; provider_registry_provider_code_unique — UNIQUE, btree (provider_code).

---

ai.model_family

A model lineage (e.g. "claude-sonnet") under a provider. Definition half of the A2b definition/version split.

Global, non-tenant-scoped — no RLS. authenticated: SELECT only. No soft delete.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
provider_registry_id	UUID	NOT NULL	—	FK → ai.provider_registry
family_code	text	NOT NULL	—	Unique per provider — see indexes
display_name	text	NOT NULL	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

No CHECK constraints on this table.

Indexes: PK on id; model_family_provider_family_code_unique — UNIQUE, btree (provider_registry_id, family_code); plain index on provider_registry_id.

---

ai.model_version

A specific, immutable release under a model_family — never mutated after creation except lifecycle status, matching every other version-row table in this codebase.

capabilities JSONB example shape (Section 4 audit item J): {"vision": true, "extended_thinking": true, "max_output_tokens": 64000, "supports_tool_use": true, "supports_prompt_caching": true}

Global, non-tenant-scoped — no RLS. authenticated: SELECT only. No soft delete.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
model_family_id	UUID	NOT NULL	—	FK → ai.model_family
version_label	text	NOT NULL	—	Unique per family — see indexes
capabilities	jsonb	nullable	—	Example shape above
context_window	integer	nullable	—
status	text	NOT NULL	'active'	CHECK IN (active,deprecated,retired)
released_at	timestamptz	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints: chk_model_version_status — status IN ('active','deprecated','retired').

Indexes: PK on id; model_version_family_version_label_unique — UNIQUE, btree (model_family_id, version_label); plain index on model_family_id.

---

C1 Deployment — model_deployment + 4 satellites + model_deployment_status_observation

Built per the v2 design's own C1 spec: 3 genuinely separate state classes, each its own table (independently confirmed live: mutating model_deployment_override leaves model_deployment.traffic_weight unchanged; model_deployment_status_observation rows are genuinely mutable — a plain UPDATE succeeds, unlike agent_execution's append-only trigger — and purging them leaves both the durable and override rows intact).

1. Durable config — model_deployment, never overwritten: a traffic-weight change is a NEW row referencing previous_deployment_id, matching A2b's own version-row discipline.
2. Administrative override — model_deployment_override, a separate small table so an emergency override never touches the durable row.
3. Transient runtime observation — model_deployment_status_observation, deliberately mutable/purgeable/NOT partitioned/NOT append-only, implementing Part D's own disclosed fallback ("if external observability is never built" — no such infra exists in this codebase yet). A scheduled retention job (service-layer, not yet built) is the intended cleanup mechanism, matching platform.outbox's own precedent.

ai.model_deployment

A specific model version deployed to a specific region, with traffic-shaping config.

Global, non-tenant-scoped — no RLS. authenticated: SELECT only. No soft delete.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
model_version_id	UUID	NOT NULL	—	FK → ai.model_version
provider_registry_id	UUID	NOT NULL	—	FK → ai.provider_registry
region	text	NOT NULL	—
status	text	NOT NULL	'active'	CHECK IN (active,canary,shadow,retired)
retirement_date	timestamptz	nullable	—
traffic_weight	numeric(5,4)	NOT NULL	1.0000	CHECK 0–1 inclusive
canary_percent	numeric(5,4)	nullable	—
shadow_only	boolean	NOT NULL	false
rollback_target_deployment_id	UUID	nullable	—	Self-FK → ai.model_deployment.id — the deployment an auto-rollback would restore
previous_deployment_id	UUID	nullable	—	Self-FK → ai.model_deployment.id — never-overwrite discipline: a traffic-weight change is a new row pointing back here
auto_rollback_threshold	numeric(5,4)	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints: chk_model_deployment_status — status IN ('active','canary','shadow','retired'); chk_model_deployment_traffic_weight_range — traffic_weight >= 0 AND traffic_weight <= 1.

Indexes: PK on id; plain index on model_version_id; plain index on status.

---

ai.model_deployment_limit

Per-deployment rate/concurrency ceiling — effectively 1:1 with model_deployment.

Global, non-tenant-scoped — no RLS. authenticated: SELECT only. No soft delete.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
model_deployment_id	UUID	NOT NULL	—	FK → ai.model_deployment. UNIQUE — see indexes
rate_limit_per_minute	integer	nullable	—
max_concurrency	integer	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

No CHECK constraints on this table.

Indexes: PK on id; model_deployment_limit_deployment_unique — UNIQUE, btree (model_deployment_id).

---

ai.model_deployment_region

Approved regions + residency compatibility per deployment — the C1 residency-guard trigger's own candidate-filter table (E7's "WHERE predicate, not a post-hoc check" rule).

Global, non-tenant-scoped — no RLS. authenticated: SELECT only. No soft delete. No updated_at column at all — static per-region compatibility data, not expected to mutate in place.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
model_deployment_id	UUID	NOT NULL	—	FK → ai.model_deployment
region_code	text	NOT NULL	—	Unique per deployment — see indexes
residency_compatible	boolean	NOT NULL	true	Read by ai.check_model_deployment_override_residency() and the (future) E7 resolver
created_at	timestamptz	NOT NULL	now()

No CHECK constraints on this table.

Indexes: PK on id; model_deployment_region_deployment_region_unique — UNIQUE, btree (model_deployment_id, region_code); plain index on model_deployment_id.

No trigger — no updated_at column.

---

ai.model_deployment_policy

Fallback/retry/circuit-breaker policy per deployment — effectively 1:1 with model_deployment.

traffic_policy JSONB example shape (Section 4 audit item J): {"sticky_session": false, "max_retries": 2, "retry_backoff_ms": 500, "circuit_breaker_threshold_errors": 5}

Global, non-tenant-scoped — no RLS. authenticated: SELECT only. No soft delete.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
model_deployment_id	UUID	NOT NULL	—	FK → ai.model_deployment. UNIQUE — see indexes
fallback_eligible	boolean	NOT NULL	true
traffic_policy	jsonb	nullable	—	Example shape above
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

No CHECK constraints on this table.

Indexes: PK on id; model_deployment_policy_deployment_unique — UNIQUE, btree (model_deployment_id).

---

ai.model_deployment_override

Administrative override state class — a separate, small, frequently-mutated table so an emergency override never touches the durable model_deployment row.

Residency guard — ai.check_model_deployment_override_residency(). emergency_traffic_weight may only ever RAISE traffic (vs. the deployment's own durable traffic_weight) when temporary_tenant_restriction is set AND at least one model_deployment_region row for this deployment satisfies that tenant's E7-resolved (platform.tenant_regional_policy) regional policy. An operator can always force traffic AWAY (lower/zero) or act platform-wide (temporary_tenant_restriction NULL) without this check; only the "raise toward a specific tenant" path is residency-gated — matching C1's own stated rule that an operator can reduce risk but never use this path to force traffic toward a non-compliant deployment, even under incident pressure. Live-tested: raising emergency_traffic_weight toward a tenant whose regional policy doesn't match the deployment's own compatible-region set is rejected ("is not residency-compatible with tenant ...").

Global, non-tenant-scoped — no RLS. authenticated: SELECT only. No soft delete.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
model_deployment_id	UUID	NOT NULL	—	FK → ai.model_deployment. UNIQUE — see indexes
manually_disabled	boolean	NOT NULL	false
forced_shadow	boolean	NOT NULL	false
emergency_traffic_weight	numeric(5,4)	nullable	—	Residency-guarded when raising traffic toward a tenant-restricted deployment — see above
operator_imposed_circuit_open	boolean	NOT NULL	false
temporary_tenant_restriction	UUID	nullable	—	FK → platform.tenant. NULL = platform-wide override; set = scoped to one tenant, the input the residency guard reads
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

No CHECK constraints on this table — the residency rule is trigger-enforced, not CHECK-enforced (it requires a cross-table lookup).

Indexes: PK on id; model_deployment_override_deployment_unique — UNIQUE, btree (model_deployment_id).

Triggers (2): set_updated_at; trg_model_deployment_override_residency (BEFORE INSERT OR UPDATE, executes ai.check_model_deployment_override_residency()).

---

ai.model_deployment_status_observation

Transient runtime observation state class — interim, short-retention DB table implementing Part D's own disclosed fallback ("if external observability is never built"). Deliberately mutable/purgeable, NOT partitioned, NOT append-only-enforced (matching platform.outbox's own precedent for small, constantly-drained tables) — a scheduled retention job, not a partition-detach, is the intended cleanup mechanism (disclosed, not yet built).

Global, non-tenant-scoped — no RLS. authenticated: SELECT only. No soft delete. No updated_at column at all — rows are inserted per observation, never edited in place, and purged by a future retention job rather than superseded.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
model_deployment_id	UUID	NOT NULL	—	FK → ai.model_deployment
observed_at	timestamptz	NOT NULL	now()
latency_ms	integer	nullable	—
error_rate	numeric(5,4)	nullable	—
queue_depth	integer	nullable	—
circuit_state	text	nullable	—	CHECK IN (closed,open,half_open) OR NULL
created_at	timestamptz	NOT NULL	now()

CHECK constraints: chk_model_deployment_status_observation_circuit_state — circuit_state IS NULL OR circuit_state IN ('closed','open','half_open').

Indexes: PK on id; composite index on (model_deployment_id, observed_at).

No trigger — no updated_at column.

---

C1 Prompts — prompt_definition → prompt_version → prompt_model_compatibility

The A2b definition/version split applied to prompts, plus a many-to-many compatibility mapping (not a 1:1 bind) — one prompt_version can be compatible with several model_versions and vice versa.

ai.prompt_definition

Definition half of the A2b split for prompts.

Global, non-tenant-scoped — no RLS. authenticated: SELECT only. No soft delete.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
prompt_code	text	NOT NULL	—	UNIQUE — see indexes
display_name	text	NOT NULL	—
description	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

No CHECK constraints on this table.

Indexes: PK on id; prompt_definition_prompt_code_unique — UNIQUE, btree (prompt_code).

---

ai.prompt_version

Version half — immutable template text once created, matching every other version-row table in this codebase (never mutated, only status transitions).

Global, non-tenant-scoped — no RLS. authenticated: SELECT only. No soft delete.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
prompt_definition_id	UUID	NOT NULL	—	FK → ai.prompt_definition
version_label	text	NOT NULL	—	Unique per definition — see indexes
template	text	NOT NULL	—	The prompt template text itself
status	text	NOT NULL	'draft'	CHECK IN (draft,active,deprecated,retired)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints: chk_prompt_version_status — status IN ('draft','active','deprecated','retired').

Indexes: PK on id; prompt_version_definition_version_label_unique — UNIQUE, btree (prompt_definition_id, version_label); plain index on prompt_definition_id.

---

ai.prompt_model_compatibility

A many-to-many compatibility MAPPING, not a 1:1 bind.

Global, non-tenant-scoped — no RLS. authenticated: SELECT only. No soft delete.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
prompt_version_id	UUID	NOT NULL	—	FK → ai.prompt_version
model_version_id	UUID	NOT NULL	—	FK → ai.model_version
compatibility_state	text	NOT NULL	'untested'	CHECK IN (compatible,incompatible,untested)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints: chk_prompt_model_compatibility_state — compatibility_state IN ('compatible','incompatible','untested').

Indexes: PK on id; prompt_model_compatibility_prompt_model_unique — UNIQUE, btree (prompt_version_id, model_version_id); plain index on model_version_id.

---

Routing

ai.routing_policy

The eligible-candidates + priority-order policy a (not-yet-built) resolution function will read at request time. Evaluation inputs — required capability, approved model status, tenant policy, data classification, residency (via platform.tenant_regional_policy, E7), context size, latency requirement, provider health, available capacity, cost ceiling, certification compatibility — are all read at resolution time, not stored redundantly here; this table holds only the policy itself. No resolution function exists yet — schema-only this phase, matching every other table in this module (a Section 4 audit fix corrected a Drizzle comment that had falsely claimed one was "built alongside this table").

criteria JSONB example shape (Section 4 audit item J): {"required_capability": "extended_thinking", "min_context_window": 200000, "max_cost_ceiling_millicents": 50000, "classification_max": "confidential"}

Tenant-optional, no RLS. tenant_id nullable, FK → platform.tenant — NULL = platform-wide default policy, set = a tenant-specific override. No RLS policy (both scopes must stay visible to the resolution function regardless of which tenant is asking, the same disclosed shape as platform.ai_capacity_policy's own scope columns). authenticated: SELECT only. No soft delete.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	nullable	—	FK → platform.tenant. NULL = platform-wide default policy
workload_class_id	UUID	nullable	—	Deferred forward-ref, no FK → agents.workload_class (E8, Phase 5, not yet built). Logged to OPEN_ITEMS.md and CROSS_MODULE_CONTRACTS.md — see Open items below
criteria	jsonb	NOT NULL	'{}'	Example shape above
priority	integer	NOT NULL	0
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

No CHECK constraints on this table.

Indexes: PK on id; plain index on tenant_id; plain index on workload_class_id.

---

C4 Memory Provenance

ai.agent_memory_source

A join table, not a single polymorphic column — justified because one memory may derive from MULTIPLE sources (a learned preference synthesized from several past decisions), which a single FK/polymorphic-pair column structurally cannot express. source_ref is polymorphic across 4 possible targets — deliberately no FK on source_ref itself, matching this codebase's established polymorphic-column convention (e.g. tax.tax_calculation.source_ref).

agent_memory_id — real, enforced composite FK, despite ai.agent_memory now being partitioned. agent_memory_created_at is the write-once shadow column the FK constraint itself validates: a caller supplying a value that doesn't match the real parent row's created_at is rejected by the FK constraint, exactly as any other composite-FK mismatch would be — no sync trigger needed or added (ai.agent_memory rows are never updated after their created_at is set). Live-tested: an insert with the correct agent_memory_created_at shadow value succeeds; the identical insert with a fabricated 2020-01-01 timestamp is rejected by the FK constraint itself (agent_memory_source_agent_memory_id_tenant_created_fkey).

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy agent_memory_source_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid. No soft delete, no updated_at column — write-once join row.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
agent_memory_id	UUID	NOT NULL	—	Composite FK half (with tenant_id, agent_memory_created_at) → ai.agent_memory
agent_memory_created_at	timestamptz	NOT NULL	—	Write-once shadow column — the composite FK's own partition-key half. See above
source_type	text	NOT NULL	—	CHECK IN (agent_execution,decision_context_snapshot,document_chunk,outcome_observation)
source_ref	UUID	NOT NULL	—	Polymorphic, target named by source_type — no FK, matching tax.tax_calculation.source_ref's convention
created_at	timestamptz	NOT NULL	now()

CHECK constraints: chk_agent_memory_source_source_type — source_type IN ('agent_execution','decision_context_snapshot','document_chunk','outcome_observation').

Composite FK: agent_memory_source_agent_memory_id_tenant_created_fkey — (agent_memory_id, tenant_id, agent_memory_created_at) → ai.agent_memory (id, tenant_id, created_at).

Indexes: PK on id; plain index on agent_memory_id; composite index on (source_type, source_ref).

No trigger — no updated_at column.

---

ai — Design Patterns Summary

Column-count reconciliation (verified live via information_schema.columns, schema ai)

As of the 2026-07-06 lock (7 tables):

Table	Cols
import_job	17
import_file	15
import_record	21
ai_request	19
agent_execution	19
agent_usage_period	12
agent_memory	18
Total	121

Verified live (as of the 2026-07-06 lock): SELECT count(*) FROM information_schema.tables WHERE table_schema='ai' → 7; SELECT count(*) FROM information_schema.columns WHERE table_schema='ai' → 118. Both matched the per-table sum above exactly at lock time. Remediation Phase 4 (2026-07-08) then added 3 columns to agent_memory (subject_type, subject_ref, expires_at), bringing the schema total to 121 columns across the same 7 tables — see the Remediation Phase 4 subsection above.

As of the Phase 2 reopen (2026-07-13, 49 tables) — per-distinct-table-definition columns (partition-child tables inherit their parent's columns and are not separately counted here; they are, however, separately counted in the physical table total of 49 — see below):

Table	Cols	Table	Cols
import_job	17	model_deployment_limit	6
import_file	15	model_deployment_region	5
import_record	21	model_deployment_policy	6
ai_request	19	model_deployment_override	9
agent_execution	20 (+1)	model_deployment_status_observation	8
agent_usage_period	12	prompt_definition	6
agent_memory	21 (+3)	prompt_version	7
provider_registry	6	prompt_model_compatibility	6
model_family	6	routing_policy	7
model_version	9	agent_memory_source	7
model_deployment	14
Total			227

agent_execution (19→20, +1: resolves_execution_created_at) and agent_memory (18→21, +3: retention_until, memory_class, tenant_inspectable) are the only 2 pre-existing tables that gained columns this phase. The 14 new tables contribute 102 columns. Physical table count: SELECT count(*) FROM information_schema.tables WHERE table_schema='ai' → 49 = the 7 original tables (2 of which — agent_execution/agent_memory — are now partition parents, not physically dropped) + 14 new table definitions + 28 partition-child tables (13 monthly partitions + 1 DEFAULT catch-all, × 2 partitioned tables). See the "New Tables" section above for the disclosed 13-vs-14 count note.

v1 baseline vs. this build — a delta, not a from-scratch design

v1's locked docs/old/schema/schema_modules/schema_ai.md (2026-06-11) already specified 4 tables / 71 columns for this module — independently confirmed accurate to v1's real design (not stale, unlike crm's v1 baseline), just pre-agent-infrastructure. The 2026-07-06 build's delta: +3 tables (4→7: agent_execution, agent_usage_period, agent_memory), +47 columns (71→118) — three wholly new tables plus the agent_identity_id addition to ai_request and the actor-FK retargets (created_by_user_id→created_by_actor_id, reviewed_by_user_id→reviewed_by_actor_id) on the carried-forward pipeline tables.

Phase 2 (2026-07-13) has no v1 antecedent at all — the C1 registry/deployment/prompt/routing tables and C4's agent_memory_source are net-new v2/v3-only capability with nothing in v1 to diff against (v1 never modeled an LLM provider/model registry, a deployment-traffic surface, a prompt registry, or memory provenance). The partitioning of agent_execution/agent_memory is a schema-shape change (how the 2 tables are physically stored), not a v1→v2 capability migration, so the 4-block Design-Phase Integrity delta-accounting requirement doesn't apply to this reopen the same way it applies to a whole-module build against a v1 predecessor — consistent with how Phase 1's own platform reopen (PROJECT_DECISIONS #62) treated the identical question.

The verification-caught fixes — why each mattered

Design-phase (caught before the migration was written, by the design-verification Workflow):

1. agent_execution.resolves_execution_id (self-FK, replacing a flawed polymorphic-key design). The original design assumed the propose→execute linkage this project already solved twice (inventory.stock_adjustment_request→stock_movement, crm.customer_merge_candidate→customer_merge) could be replicated by sharing a polymorphic target_table/target_row_id key between the proposed and executed rows. Both real precedents actually use a dedicated FK column, not a shared polymorphic key, and the polymorphic-key approach breaks entirely for the case that matters most for an agent: proposing an action against a row that does not exist yet (target_row_id NULL at propose time). The self-FK closes this with a direct, always-resolvable JOIN.
2. agent_execution.idempotency_key + partial-unique dedup. The first agent-specific ledger table would otherwise have shipped with the same missing-dedup gap crm.customer_merge_candidate shipped with and never fixed. Retry-prone agent runtimes make this gap more likely to bite, not less.
3. agent_memory's partial unique index on status='active'. A permanent one-row-per-key-forever unique index blocks a legitimate lifecycle: disabling a memory for cause, then later inserting an unrelated fresh fact under the same key. identity.agent_duty_grant already solved the identical shape of problem with a partial unique on status='active'; agent_memory now matches it exactly.
4. ai_request.agent_identity_id (new nullable FK). v1 predates agent_identity entirely, so v1's ai_request had no way to answer "which agent triggered this inference call." Without this column, agent_usage_period.total_tokens would have no authoritative attribution path at all.

Post-build (caught by the independent Section 4 audit and adversarial verification agents run against the LIVE built DDL, after the migration was first applied — these only manifest against real constraint behavior, not design prose, which is why the design-phase pass missed them):

5. ai_request's idempotency uniqueness split into two partials. Carried forward from v1 without re-auditing the existing single-unique shape against the NULL-tenant trap — see the two-partial-unique fix documented under ai.ai_request above.
6. agent_execution's target CHECK relaxed to chk_agent_execution_target_module_table_together. Introduced while fixing fix #1 above, by copying import_record's all-or-nothing CHECK shape without checking compatibility with the new propose/execute linkage — see the fix documented under ai.agent_execution above.
7. agent_execution_resolves_execution_id_unique added. Fix #1 (the self-FK) had no uniqueness guard, so a retried "execute" call could resolve the same "proposed" row twice — see the fix documented under ai.agent_execution above.

All seven were caught by independent verification, not self-grading — four during the design/audit phase before the migration was written, three during the post-build Section 4 audit and adversarial verification passes against the live schema.

Why the total_tokens reconciliation formula changed mid-design

The original formula summed tokens indirectly: join agent_execution.ai_request_id → ai_request.total_token_count, then filter by period. This undercounts whenever an LLM call is correctly attributed to an agent (ai_request.agent_identity_id set) but its corresponding agent_execution row was never logged (e.g. a read-only inference that informs a decision but isn't itself a loggable "action"). The corrected formula sums directly off ai_request.agent_identity_id = this row's agent_identity_id AND ai_request.requested_at in [period_start, period_end) — the authoritative attribution path, independent of whether an agent_execution row happens to exist. total_cost_cents does not have this problem and stays on its original formula (SUM(agent_execution.cost_millicents)/1000), since cost tracking is scoped to logged actions by design, not inference calls generally.

The atomic-upsert requirement is not optional prose

agent_usage_period is the one table in this schema whose correctness depends on how it is written, not just its shape. The INSERT ... ON CONFLICT (tenant_id, agent_identity_id, period_start) DO UPDATE SET ... = table.col + EXCLUDED.col ... shape (reproduced in full in that table's section above) is a documented build requirement — both as a SQL comment in the migration file and in this doc — because an application-level read-then-increment-then-write would hit two independently-confirmed races: a lost-update race under concurrent same-period increments, and a duplicate-insert race under concurrent first-actions-of-a-new-period. Whatever service eventually wraps this table (no AIService extension exists yet for these tables) must issue this exact statement shape, never a read-modify-write.

AI_CAPABILITY_GAPS.md — G5 ruling this pass: built, not just documented

Unlike crm/inventory's own passes — where decision_provenance.memory_refs was documented as a JSONB key pointing at nothing, because no memory table existed yet — this pass gives G5 a real, live target: ai.agent_memory.id. crm.customer/inventory.item (and siblings)'s memory_refs arrays now resolve to actual rows in this table, the first time that key has pointed at anything real. G3 (multi-agent handoff) stays correctly deferred, unchanged from crm/inventory's own ruling — no delegation/handoff table exists; decision_provenance.delegated_by_actor_id remains a documented JSONB key only.

Agent-authority mapping — pure consumer of identity.agent_duty_grant, agent_execution as its own action log

ai introduces no new authority-granting mechanism — identity.agent_duty_grant remains the single source of truth for what an agent may do and at what authority level. agent_execution.authority_level_applied is a point-in-time snapshot of the grant that applied at action time, not a live reference (no FK — see Cross-Phase FK table). The D7 autonomy-boundary table for this module's own actions:

Action	Authority
Write an agent_execution row (log an action happened)	may_act_alone — observational, zero mutation risk
Increment agent_usage_period	may_act_alone, automation_source='system' — deterministic bookkeeping
Create an agent_memory row	may_act_alone — low-stakes (preferences/patterns, not financial)
Edit/disable a memory entry	Human-only via owner console (B12: "editable/disableable by owner")
Import-record load decision	Unchanged from v1 — DB-enforced CHECK gate (chk_import_record_load_decision_consistency)

now() is not IMMUTABLE — same discipline as prior modules

No index predicate in this schema references now(). ai_request_status_idx (WHERE status <> 'success'), import_job_status_idx, import_record_review_status_idx/load_status_idx are all predicated on stored columns only, consistent with the reasoning crm.customer_segment_membership (DR-47) and inventory.stock_reservation/lot established.

Polymorphic backref pattern — two distinct uses, not conflated

import_record.target_module/target_table/target_row_id uses the strict all-or-nothing-CHECK polymorphic tagging pattern audit_log established elsewhere in the platform. agent_execution.target_module/target_table/target_row_id uses the same tagging intent but a relaxed CHECK (chk_agent_execution_target_module_table_together — module+table travel together, target_row_id independently optional, see fix #6 above), since an agent must be able to tag a not-yet-existing target. Both are used purely to tag what a row touched, deliberately distinct from agent_execution.resolves_execution_id, which handles the propose→execute linkage via a dedicated self-FK, not the polymorphic tag. Conflating linkage with tagging was the original design's mistake (fix #1 above) — the tagging pattern is fine for what it does; it was never fit for purpose as a linkage mechanism.

Triggers

As of the 2026-07-06 lock, set_updated_at (via platform.set_updated_at()) fired BEFORE UPDATE on 6 of the 7 tables (import_job, import_file, import_record, ai_request, agent_usage_period, agent_memory); the 1 table without it was agent_execution (no updated_at column at all — append-only ledger). No ai-schema-specific trigger function existed at that point — only the shared platform.set_updated_at(), used across platform, identity, shared, multi_loc, crm, inventory.

As of Phase 2 (2026-07-13), ai has its first-ever schema-owned trigger functions — 4 new ones, all ai-namespaced (not shared): ai.check_agent_execution_single_resolver(), ai.check_agent_execution_idempotency() (both agent_execution), ai.check_agent_memory_single_active() (agent_memory), ai.check_model_deployment_override_residency() (model_deployment_override) — each replacing an invariant a native partial-unique index could no longer express (partitioning) or a cross-table lookup a CHECK constraint cannot perform. set_updated_at now also fires on 11 of the 14 new tables (provider_registry, model_family, model_version, model_deployment, model_deployment_limit, model_deployment_policy, model_deployment_override, prompt_definition, prompt_version, prompt_model_compatibility, routing_policy); the 3 without it — model_deployment_region, model_deployment_status_observation, agent_memory_source — have no updated_at column at all (static reference data / transient observation / write-once join row, respectively). agent_execution also regained its pre-existing trg_agent_execution_append_only trigger (reinstalled verbatim on the post-partitioning table, not a new behavior).

Service layer

No AIService extension exists yet for any of the agent-runtime or Phase 2 tables (agent_execution, agent_usage_period, agent_memory, nor any of the 14 new Phase 2 tables) — this remains Drizzle schema + hand-written migration + tests against raw SQL only, matching the precedent set by shared, multi_loc, crm, and inventory (schema-first, service-layer-later). v1's import pipeline (import_job/import_file/import_record) already has service-layer intent documented in rationale_ai.md, but no actual AIService implementation exists in this codebase for any of the 49 tables. Every phase of the agents-v2/v3 build stays schema-only until Phase 5 lands the agents module itself.

JSONB columns

2026-07-06 build: import_file.inferred_mapping, import_record.raw_data/mapped_data/corrected_data, agent_execution.evidence/predicted_outcome, agent_memory.content/decision_provenance.

Phase 2 (2026-07-13) adds 3 more, each with a documented example shape (Section 4 audit item J — a gap the original design-phase draft left undocumented on all 3, fixed same-pass): model_version.capabilities, model_deployment_policy.traffic_policy, routing_policy.criteria. See each table's own section above for its example shape.

Note: agent_memory.subject_type/subject_ref (Remediation Phase 4) are plain text/UUID, not JSONB — the polymorphic pair is two ordinary nullable columns, not a JSONB shape. Same treatment applies to Phase 2's agent_memory_source.source_type/source_ref.

---

Open items carried forward (see OPEN_ITEMS.md for full text)

1. Cumulative spend-ceiling column on agent_duty_grant — DEFERRED. Trigger: "before the first cumulative-spend-limited agent runs — add spend_ceiling_cents + period to agent_duty_grant; agent_usage_period already supplies the data." No spending agent exists yet (reorder is quantity-only; money lives in the unbuilt Purchasing module) — identity is not being reopened for this now.
2. enrichment_job (batch re-enrichment tracking) — DEFERRED v1.5, re-logged from docs/old/design_rationale/rationale_ai.md DR6 and v1's schema_ai.md "Deferred items" section. Trigger: "when scheduled batch re-enrichment (re-verify all unverified plants) becomes a product requirement."
3. ai_response_cache (popular query result cache) — DEFERRED to consumer phase, re-logged from v1 DR6. Belongs in the ai schema (not consumer_app) per v1's own forward-decision. Trigger: "build at consumer phase."
4. ai_feedback (thumbs-up/down quality signals) — DEFERRED v1.1, re-logged from v1. Trigger: "when AI feature quality needs structured user-feedback collection."
5. anomaly_alert (persisted anomaly detection results) — DEFERRED v1.1, re-logged from v1. Trigger: "when anomaly persistence/acknowledgement workflow is a product requirement."
6. import_file.file_id forward-ref — already existed as a concept in v1, re-confirmed now that ai actually exists in v2. Trigger: "when the files module is built."
7. No AIService exists yet for any of the 49 tables (7 original + 14 Phase 2 registry/deployment/prompt/routing/provenance table types) — schema-only every pass so far, same pattern as every other module's deferred service layer. Every phase of the agents-v2/v3 build stays schema-only until Phase 5 lands the agents module itself.
8. ai_request.tenant_id/agent_identity_id consistency — no CHECK. Every agent belongs to exactly one tenant (identity.agent_identity.tenant_id NOT NULL), so an ai_request row with tenant_id=NULL (a platform-level call) but agent_identity_id set to a real tenant-scoped agent is nonsensical, and nothing currently prevents it. Found by adversarial verification; called low severity — a data-hygiene gap for whoever builds that platform-level write path, not an RLS leak today. Not fixed as a schema CHECK: Postgres has no cross-table CHECK without a trigger, and "build thin" argues against a trigger for a not-yet-real caller. Trigger: "when a real platform-level AI call path with agent_identity_id set is built — add a trigger or move the validation into that write path."
9. decision_provenance.memory_refs has no tenant-consistency enforcement against ai.agent_memory. A tenant-A row's memory_refs JSONB array could reference a tenant-B memory id with nothing preventing it — inherently unfixable at the schema level, since JSONB array elements cannot carry FK constraints in Postgres. Found by adversarial verification. Trigger: "when AIService (or a shared cross-module runtime-logging helper) is built — validate memory_refs tenant-scoping at the service layer."
10. agent_memory.subject_ref (Remediation Phase 4, PROJECT_DECISIONS #40 Item 18) — no erasure job or service consumes it yet. The GDPR/erasure-scoping seam (subject_type/subject_ref + chk_agent_memory_subject_consistency) is schema-only: no scheduled sweep, no AIService method, and (structurally, same as row 9) no cross-table tenant/subject-consistency CHECK exist for it. Trigger: "when a GDPR/CCPA erasure workflow is built — implement the job/service that resolves subject_type+subject_ref to real rows and validate tenant-scoping at the service layer."
11. ai.routing_policy.workload_class_id is a bare uuid, no FK — forward-ref to agents.workload_class (E8), not yet built (Phase 5 of the agents-v2/v3 build). Added in the agents-v2/v3 build Phase 2 (ai reopen #1, 2026-07-13, PROJECT_DECISIONS #63). Validated only by application logic until then. Also logged in docs/modules/CROSS_MODULE_CONTRACTS.md's own AI section. Trigger: "wire the composite FK when Phase 5 lands agents.workload_class."