`ai` — Module Spec

1. Purpose

ai owns the tenant's agent-runtime substrate: the onboarding zero-mapping import pipeline (unchanged from v1), the raw LLM-call log, and — new this pass — the execution ledger, usage meter, and memory store that let every OTHER module's autonomous actions be logged, bounded, and learned from. It is not where AI features "live" (Sage, send-time optimization, plant enrichment, anomaly detection are all service-layer logic over other modules' own tables, with no table here) — it is the shared infrastructure layer those features and every agent-authored action in crm/inventory/future modules write through. ai is module #6 (cross-cutting) in the v2 pipeline and the first module built specifically to satisfy the AI Capability Plane's own schema-relevant controls (A6, A7) plus the one gap AI_CAPABILITY_GAPS.md names as genuinely schema-shaped (G5, agent memory).

2. Ownership

Owns — 7 tables across two clusters:

IMPORT PIPELINE (3, reconciled from v1, light retargeting only): import_job, import_file, import_record.

CALL LOG (1, reconciled from v1): ai_request.

AGENT RUNTIME (3, new this pass): agent_execution, agent_usage_period, agent_memory.

Does NOT own:

The AI features themselves — Sage (conversational assistant), send-time optimization, plant enrichment, anomaly detection, reorder suggestions, dead-stock flags, and every other AI capability in the Plane are service-layer logic over other modules' own tables. ai supplies the substrate (execution ledger, usage meter, memory) those features log through and read from; it does not hold a table per feature. This was true of v1's design (DR3: "cluster 2 gets no tables") and remains true after this pass adds the agent-runtime cluster.
Agent identity and authority → identity (module #2). identity.agent_identity (who the agent is) and identity.agent_duty_grant (what it's allowed to do, at what authority level, within what limits) are already built and locked; ai is a pure consumer of both, never redefines or extends them.
File/photo storage → Files (not yet built in v2 — verified live via psql \dn). ai holds one deferred bridge column, import_file.file_id (plain nullable uuid, no FK) — unchanged from v1's own already-correct forward-ref design.
Per-tenant AI budget/entitlement configuration → Platform. platform.tenant_entitlement/platform.tenant_usage_summary already own the token cap and the aggregated ai_calls_count; ai's tables feed that aggregate, they don't duplicate its configuration.

3. Layer & Dependencies

Tenant-scoped agent-runtime infrastructure layer, cross-cutting (every future module is a prospective writer into ai.agent_execution/agent_usage_period/agent_memory, the same way every module already writes into its own tables and reads identity.actor).

Depends on:

platform — platform.tenant is the FK target for every tenant-scoped ai table; platform.set_updated_at() trigger (on the 2 tables that carry updated_at: agent_usage_period, agent_memory, plus the 3 v1 import tables that already had it).
identity (module #2) — already a dependency, not new this pass. Every actor-attribution column (created_by_actor_id on import_job, reviewed_by_actor_id on import_record, created_by_actor_id/updated_by_actor_id on agent_memory) FKs to identity.actor. The 3 new agent-runtime tables additionally FK to identity.agent_identity (ai_request.agent_identity_id, agent_execution.agent_identity_id, agent_usage_period.agent_identity_id) and identity.permission (agent_execution.permission_id) — this is ai's headline new seam, the first live consumer of the agent-identity infrastructure from the runtime-logging side (identity.agent_duty_grant is the authority side; ai is where that authority gets exercised and recorded).
Files (deferred, unchanged from v1): import_file.file_id — plain uuid, no FK, files schema absent from v2 (verified live).

Depended on by: every module with an autonomous write path. crm and inventory already carry decision_provenance.memory_refs keys documented as pointing at agent memory; this build is the first pass that gives that key a real target (ai.agent_memory.id) rather than a JSONB convention with nothing behind it. Future modules building their own autonomous actions are expected to write agent_execution rows (via a not-yet-built shared service, not directly) and read agent_usage_period for cumulative-limit enforcement.

4. Tables

7 tables, 121 columns (118 at 2026-07-06 lock, +3 from Remediation Phase 4, 2026-07-08 — see §10 DR-77/PROJECT_DECISIONS #40 Item 18). See packages/db/migrations/20260706080000_ai_module.sql for the original DDL and packages/db/migrations/20260709040000_phase4_item18_gdpr_erasure.sql for the Phase 4 agent_memory ALTER. Drizzle schema files: packages/db/src/schema/ai/{_schema,import,request,execution,usage,memory,index}.ts (import.ts holds all 3 import-pipeline tables).

Table	Group	PK style	Notes
`import_job`	Import pipeline	UUID	Tenant-scoped, RLS enabled, unchanged core shape from v1
`import_file`	Import pipeline	UUID	Subordinate to `import_job`, `file_id` deferred forward-ref (no FK)
`import_record`	Import pipeline	UUID	Subordinate to `import_file`, both DB-level CHECK safety rails kept (load-decision consistency; polymorphic all-or-nothing)
`ai_request`	Call log	UUID	Tenant-scoped (nullable `tenant_id` for platform-level calls, unchanged v1 design), reference-don't-copy discipline (no prompt/response content ever)
`agent_execution`	Agent runtime	UUID	NEW — append-only (no `updated_at`), the A6 execution ledger
`agent_usage_period`	Agent runtime	UUID	NEW — maintained running counter (deliberately not append-only), the A7 value/usage meter
`agent_memory`	Agent runtime	UUID	NEW — tenant-scoped operating-memory store, the B12/G5 capability. +3 cols Remediation Phase 4 (`subject_type`/`subject_ref`/`expires_at`, GDPR/erasure seam)
Total		121 cols

`import_job`, `import_file`, `import_record`

Unchanged core shape from v1's locked design (docs/old/schema/schema_modules/schema_ai.md, 2026-06-11) — this was confirmed accurate to v1's real intent by prior independent verification, not a stale placeholder. The only touch this pass makes is retargeting actor columns to the canonical pattern: import_job.created_by_user_id → created_by_actor_id (FK → identity.actor); import_record.reviewed_by_user_id → reviewed_by_actor_id (FK → identity.actor). import_file.file_id remains a deferred forward-ref — plain nullable uuid, no FK, files schema still absent from v2 (verified live). Both existing DB-level CHECK rails on import_record (load-decision consistency; polymorphic all-or-nothing) are kept exactly as v1 designed them.

`ai_request`

Unchanged core shape — the reference-don't-copy discipline is kept exactly (no prompt/response content is ever stored, only model/tokens/cost/latency metadata). One reconciliation: this table predates identity.agent_identity entirely, so it had no concept of an agent. This pass adds agent_identity_id (nullable uuid, FK → identity.agent_identity) so an LLM call can optionally be attributed to the agent that triggered it — nullable because non-agent callers genuinely exist (a human-triggered Sage query, a service_role enrichment job).

`agent_execution`

The A6 execution ledger — genuinely new. Append-only (no updated_at column at all), matching ai_request's own immutability precedent. Carries: agent_identity_id (FK → identity.agent_identity, NOT NULL — every agent belongs to exactly one tenant so no platform-level nullable case applies here, unlike ai_request); permission_id (nullable FK → identity.permission, ties the action back to the agent_duty_grant row that gated it); authority_level_applied (point-in-time snapshot, CHECK may_act_alone/draft_only/needs_approval); action_code (matches identity.permission.permission_code convention, e.g. inventory:stock_adjustment:propose); target_module/target_table/target_row_id (polymorphic backref reusing import_record's tagging pattern, CHECK chk_agent_execution_target_module_table_together — module+table travel together, target_row_id independently optional so a not-yet-existing target can still be tagged; see DR-71) plus resolves_execution_id (nullable self-FK → ai.agent_execution.id, UNIQUE when set — see DR-65/DR-72, this is NOT the polymorphic key, it is a dedicated column); reasoning_summary (NOT NULL, business-language only, never raw chain-of-thought); evidence (JSONB, documented example shape); confidence_score; predicted_outcome (JSONB, for future B14-simulation ties); status (CHECK proposed/executed/rejected/failed); ai_request_id (nullable FK → ai.ai_request, links the business action to the LLM call(s) that informed it); cost_millicents (snapshot, not a live join, so agent_usage_period increments stay cheap); idempotency_key (nullable, see DR-66); created_at.

`agent_usage_period`

The A7 value/usage meter — genuinely new, and the one table in this schema that deliberately departs from the append-only-ledger pattern. Closes the cumulative-abuse gap agent_duty_grant.spend_limit_cents already documents as a known limitation (per-action only). A maintained, incrementally-updated running counter — the enforcement use case ("has this agent exceeded its cumulative limit," checked at action time) needs O(1) lookup, not an aggregate scan over a growing ledger. Mirrors platform.tenant_usage_summary's period-scoping shape. Carries period_start/period_end (date), action_count, total_cost_cents, total_quantity, total_tokens, last_action_at, created_at/updated_at (trigger-maintained via platform.set_updated_at() — this table genuinely mutates, unlike the ledger). Uniqueness: UNIQUE (tenant_id, agent_identity_id, period_start).

`agent_memory`

The B12/G5 tenant operating-memory store — genuinely new. Tenant-scoped, not agent-scoped (multiple agents/capabilities read the same learned pattern, per B12's own framing). Carries category/key (stable identifier within category, e.g. vendor_preference / preferred_vendor:maple), content (JSONB, the learned value), source (where the memory came from: derived_from_pos_history/user_stated/agent_inferred), confidence_score (the memory-poisoning guard lives here, not a separate flag), status (CHECK active/disabled), created_by_actor_id/updated_by_actor_id (FK → identity.actor, may be an agent or a human), automation_source, decision_provenance (JSONB), last_used_at (updated whenever a capability actually applies this memory), created_at/updated_at (trigger-maintained). Uniqueness: partial unique UNIQUE (tenant_id, category, key) WHERE status='active' — see DR-69.

Remediation Phase 4 (2026-07-08, PROJECT_DECISIONS #40 Item 18) added 3 columns to agent_memory: subject_type (text, nullable), subject_ref (UUID, nullable, polymorphic — no FK, target row named by subject_type), expires_at (timestamptz, nullable, independent retention deadline) — 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 yet consumed by any erasure job or service — see DR-77.

Net column delta vs. v1: v1's locked docs/old/schema/schema_modules/schema_ai.md (2026-06-11) specified 4 tables / 71 cols for the import pipeline + call log — confirmed accurate to v1's real design (not stale), just pre-agent-infrastructure. This build's delta: +3 tables (4→7: agent_execution + agent_usage_period + agent_memory), +47 columns (71→118) — entirely the new agent-runtime cluster; the 4 carried-forward tables get actor-FK retargeting only, no structural change. Remediation Phase 4 then added +3 columns to agent_memory only (118→121), module stays at 7 tables.

5. Capabilities

Import pipeline — zero-mapping onboarding import: import_job tracks a batch, import_file tracks each uploaded file (with a deferred files-module bridge), import_record tracks each row's inferred mapping and human-in-the-loop load decision. Unchanged from v1.
LLM call logging — ai_request is the reference-don't-copy call log (model, tokens, cost, latency; never prompt/response content), now optionally attributable to the agent that triggered it.
Execution ledger (A6) — agent_execution records every autonomous action any module takes: what agent, under what authority, what permission, what it touched, why (business-language reasoning), what it read (evidence), how confident, what happened (proposed/executed/rejected/failed), and what it cost. This is the substrate every other module's autonomy story (crm, inventory, and future modules) writes through.
Usage/value meter (A7) — agent_usage_period gives O(1) cumulative-limit enforcement per (tenant, agent, period): total cost, total quantity, total tokens, action count, last-action timestamp. Closes the "many small actions each under the per-action limit, but the sum is abusive" gap.
Agent memory (B12/G5) — agent_memory is the tenant-scoped operating-memory store: learned vendor preferences, rounding rules, cadences, etc. — sourced, confidence-scored, owner-editable/disableable, with a last_used_at staleness signal. Gives decision_provenance.memory_refs (already documented on crm/inventory tables) a real target for the first time. Remediation Phase 4 added a GDPR/erasure-scoping seam — subject_type/subject_ref (polymorphic, no FK) + expires_at — for the narrower case where a memory entry pertains to one identifiable subject; not yet consumed by any erasure job or service.

No AI write path is implemented this pass — schema-only build (Drizzle + migration + tests), no AIService extensions for the 3 new tables. See §6 and Deferred/Future Items.

6. Service Contract — `AIService`

No AIService extensions yet for the 3 new agent-runtime tables — schema-only this pass, matching shared/multi_loc/crm/inventory's own precedent for a module's (or a module's newest cluster's) first pass. This is a nuanced case: v1's import pipeline (import_job/import_file/import_record) already had documented service-layer intent in v1's own design (the zero-mapping pipeline's inference/review workflow) — that documented intent is unchanged and still pending, not newly deferred by this pass. What's genuinely new-and-deferred is the agent-runtime cluster: agent_execution writes, agent_usage_period increments, and agent_memory reads/writes have no service methods yet. Downstream code queries ai.* directly via Drizzle for now. Any future AIService (or a shared cross-module runtime-logging helper, since agent_execution/agent_usage_period are written FROM other modules' service layers, not from within ai itself) is deferred to whenever it's built.

7. Data-Flow / Population Model

ai's 3 new tables are unusual among this project's modules: they are not populated by a user filling out a form in ai itself. They are written by other modules' service-layer code, as a byproduct of actions those modules take:

agent_execution — written once per autonomous action, by whichever module's service layer performs that action (e.g. InventoryService.proposeStockAdjustment() would write a row with target_module='inventory', target_table='stock_adjustment_request'). ai owns the table; it does not initiate the write.
agent_usage_period — incremented as a direct consequence of an agent_execution write landing (see DR-67/DR-68 — this MUST be a single atomic UPSERT, not an application read-then-write).
agent_memory — written when an agent or human records/updates a learned pattern (may_act_alone to create per D7); read by any capability that wants to apply that pattern before acting, with last_used_at bumped on read-and-apply.
import_job/import_file/import_record/ai_request — populated the same way as v1 designed: import_* during tenant onboarding/data import; ai_request on every LLM call, from any module.

This means ai has essentially no first-party UI of its own — it is invisible infrastructure other modules write to and read from, consistent with Part D's own D1/D15 rulings below (no capture targets, no capture modality — this module IS the AI infrastructure, not a capture surface).

8. Part D — AI Capability Discovery Walk

This module received the schema-translation half of the AI Capability Plane pass (agent-as-actor FKs, automation_source, review seams — applied per-table above) plus its own Part A schema-vs-runtime classification (below), since ai is the module that most directly embodies the Plane rather than merely consuming it.

D1 — Capture targets. Import file uploads (existing v1 pipeline, unchanged). The 3 new tables are NOT directly user-captured — they're written by agent-runtime code as a byproduct of actions taken in OTHER modules.

D2 — Routing. import_job/import_record route to entity types (existing, unchanged). agent_execution routes via its polymorphic target_module/target_table/target_row_id ref to whichever table the action touched.

D3 — Maintenance. Stale, low-confidence agent_memory rows never confirmed and unused for a long time (last_used_at old) — a hygiene candidate for a future sweep job, not built as automation this pass.

D4 — Error-prevention. The entire point of agent_usage_period: closing the "many small actions each under the per-action limit, but the sum is abusive" gap agent_duty_grant.spend_limit_cents already documents as a known security limitation.

D5 — Negative-space. An agent_duty_grant row exists but has zero agent_execution rows ever logged against it (agent registered, never acted, or the runtime forgot to log) — a fleet-console-adjacent gap-detection query (feeds A14 later), not built as automation this pass.

D6 — Decision-support. The value-meter narrative itself (e.g. "$14.20 in tokens drafted 42 POs...") is a read/reporting concern computed FROM agent_execution + agent_usage_period + ai_request — no new table for the narrative itself.

D7 — Autonomy boundary (the load-bearing question). See the full per-action table below.

D8 — Evidence sources. This is where D8 gets recorded system-wide — agent_execution.reasoning_summary/evidence IS the evidence-capture mechanism A6 requires, self-referential to this module.

D9 — Reconciliation pairs. agent_usage_period's running totals must reconcile against agent_execution/ai_request per the documented formulas (see §9 DR-67/DR-68) — avoiding the "cache without reconciliation formula" bug class this session already caught twice (crm's tax_exempt, inventory's avg_cost_cents before it was fixed).

D10 — Rollback. Central per this module's own framing — A12's rollback contract "draws on A6's stored final record as the reversal source"; agent_execution.resolves_execution_id (DR-65) is exactly the mechanism that makes this work.

D11 — Adversarial. Memory poisoning is explicitly named in AI_CAPABILITY_GAPS.md (G5) — agent_memory.source/confidence_score are the schema-level guard; the runtime rule ("never auto-apply memory to financial behavior") is the operational backstop.

D12/D13 — Offline / channel sync. N/A — server-side infrastructure, not a capture-bar or external-channel concern.

D14 — Lifecycle. Memory entries age — a vendor preference from 2 years ago may no longer hold. last_used_at supports a future staleness sweep; not built as automation this pass. Import-record load decisions unchanged from v1.

D15 — Capture modality. N/A — this module is invisible infrastructure other modules write to, not a user-facing capture surface.

Ruled out, honestly: none beyond D12/D13/D15 above — this module's Part D walk is narrower than a product module's because ai itself is infrastructure, not a capture surface.

Part A — Schema-vs-runtime classification (all 14 controls)

Run against all 14 AI Capability Plane Part A controls before the schema was finalized, to determine which controls are genuinely schema-relevant to this module versus runtime-only:

#	Control	Classification	Why
A1	Access boundary	Runtime	Service-layer tool wall ("AI never issues raw SQL"); no persisted state.
A2	Tier router	Runtime	Dispatcher logic (logic → cheap AI → generative); no persisted state.
A3	Budget gate	Runtime (reads existing config)	Per-tenant token cap already lives in `platform.tenant_entitlement`/`tenant_usage_summary`; the gate LOGIC is runtime, no new table.
A4	Mode Contract + authority ladder	Runtime	Policy resolution logic; does not itself enforce identity/limits at runtime (that is A5).
A5	Agent identity & passport	Schema — already built	`identity.agent_identity` + `identity.agent_duty_grant`. Not part of this module.
A6	Execution ledger / evidence trace	Schema — genuinely new	Structured, queryable execution trace per action. This is `agent_execution`.
A7	Value meter	Schema — genuinely new	Needs a maintained, fast-to-query cumulative counter for "has this agent exceeded its cumulative limit," checked at action time. This is `agent_usage_period`.
A8	Data governance & model boundary	Runtime	Classification/redaction gate; a Platform/Admin config concern, not `ai`'s own ledger.
A9	Eval, replay & safety harness	Runtime	Shares A6's substrate — reads FROM the ledger, no table of its own.
A10	Circuit breakers & incident response	Runtime	Kill switch already surfaced via `agent_duty_grant.status` (active/suspended/revoked, already built).
A11	Adversarial/prompt-injection defense	Runtime	Stores evidence/decisions in the A6 trace; uses the new ledger, no table of its own.
A12	Rollback & compensating-transaction	Runtime	The mechanism lives in each consuming module (`inventory.stock_adjustment_request`→`stock_movement`, `crm.customer_merge_candidate`→`customer_merge`); draws on A6's stored final record as the reversal source.
A13	Segregation-of-duties	Runtime	A constraint/rule over A4/A5 at approval-routing time; no new table.
A14	Agent registry & fleet console	Runtime (UI/reporting surface)	A read/report surface over A5 (built) + A6 (new) + A10 (runtime) — no table of its own.

Result: exactly 2 of the 14 Part A controls are genuinely schema-relevant (A6, A7) — matching the pattern already established for agent_duty_grant's own design pass. The 3rd new table (agent_memory) is not one of the 14 Part A controls at all — it is B12 (a Part B capability, "Tenant operating memory") and G5 in AI_CAPABILITY_GAPS.md, the gap the gaps doc itself names as "the one gap with real schema-design implications."

D7 — Full per-action autonomy-boundary table

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

9. Agent Authority Mapping

ai introduces no new authority mechanism — like crm and inventory before it, it is a pure consumer of identity.agent_duty_grant (PROJECT_DECISIONS #22). The distinction here is directional: crm/inventory consume agent_duty_grant to gate their OWN autonomous actions; ai is where the EXERCISE of that authority (by any module) gets logged (agent_execution.authority_level_applied, agent_execution.permission_id) and cumulatively metered (agent_usage_period). ai does not gate anything itself — agent_execution.authority_level_applied is a point-in-time snapshot of what agent_duty_grant.authority_level applied at action time, not a live reference (see §11 seams).

10. Design Rationale (DR-63 through DR-74)

DR-63 — v1's ai design (docs/old/schema/schema_modules/schema_ai.md, locked 2026-06-11) predates identity's agent infrastructure entirely: agent_type_catalog/agent_identity/agent_skill (Batch C, locked 2026-06-28, 17 days later) and identity.agent_duty_grant (A5's passport, built 2026-07-06, 25 days later). So v1's ai_request logs "an LLM call happened" (model, tokens, cost, latency) but has no concept of an agent — it cannot answer "what did Agent X do" or "has Agent X exceeded its cumulative limit." These are genuinely new questions this design pass answers, not v1 gaps to patch; the MODULE_INDEX one-line description of ai as "AI onboarding import pipeline + Bedrock-call log, AI features are service-layer over existing data" was independently verified NOT stale — it accurately summarized the real, locked v1 design, which simply predates agent infrastructure.
DR-64 — Exactly 2 of the AI Capability Plane's 14 Part A controls are genuinely schema-relevant to this module (A6 execution ledger, A7 value meter) — see the full classification table in §8. The 3rd new table, agent_memory, answers a different Plane item entirely (B12 capability + G5 gap), not a Part A control. This mirrors the discipline already established for agent_duty_grant's own design pass, where most of the Plane turned out to be runtime and only the authority-passport itself needed a table.
DR-65 — agent_execution.resolves_execution_id (nullable self-FK → ai.agent_execution.id), NOT a polymorphic key. The original design draft proposed routing a "proposed → later executed" linkage through the same polymorphic target_module/target_table/target_row_id triple already used for the action's own backref, 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 claim FALSE. Neither real precedent uses a shared polymorphic key for propose→execute linkage — both use a DEDICATED FK column: stock_movement.adjustment_request_id (a direct FK back to the stock_adjustment_request row it resolves) and customer_merge.candidate_id (a direct FK back to the customer_merge_candidate row it resolves). A shared polymorphic key cannot express this linkage at all for the specific case that matters most: a "proposed" agent_execution row for an action whose target row doesn't exist yet (target_row_id is NULL, because nothing has been created yet — the row IS the proposal). Under the old polymorphic-only design there was no way to later connect that proposal to the "executed" row once the target row DID exist, because the two rows would have different target_row_id values (NULL vs. real) and no shared key to join on. resolves_execution_id fixes this with the same dedicated-FK shape the real precedents use: the later "executed" agent_execution row (which now has a real target_row_id) sets resolves_execution_id to point directly at the earlier "proposed" row's id. Live-tested the exact failure case: inserted a "proposed" row with target_row_id IS NULL, then a later "executed" row with a real target_row_id and resolves_execution_id set to the proposed row's id — a direct JOIN on resolves_execution_id correctly links them, something the polymorphic-only design had no mechanism for at all.
DR-66 — agent_execution.idempotency_key (nullable) with UNIQUE (tenant_id, agent_identity_id, idempotency_key) WHERE idempotency_key IS NOT NULL — dedup protection for retry-prone agent runtimes (an agent's HTTP call times out and retries, but the first attempt actually succeeded server-side), matching inventory.stock_movement.idempotency_key's exact established precedent. Live-tested: inserting a second row with 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".
DR-67 / DR-68 — The atomic-upsert build requirement for agent_usage_period (MANDATORY, not optional). agent_usage_period's reconciliation formulas are: total_cost_cents = SUM(agent_execution.cost_millicents) / 1000 for the (agent_identity_id, period); and total_tokens = SUM(ai_request.total_token_count) WHERE ai_request.agent_identity_id = this row's agent AND ai_request.requested_at falls in [period_start, period_end) — corrected post-verification to sum DIRECTLY off ai_request.agent_identity_id (the authoritative attribution path added in DR-63/v1-reconciliation), not indirectly through agent_execution.ai_request_id. The original formula would have silently undercounted tokens whenever an LLM call was attributed to an agent but its corresponding agent_execution row was never logged (e.g. a runtime bug, or an LLM call that informed a decision but wasn't itself wrapped in an execution record) — summing through agent_execution as an intermediary loses those calls entirely, while summing directly off ai_request.agent_identity_id cannot miss them.

This is a mandatory requirement for whoever builds the service-layer increment logic, not a style preference: every increment to agent_usage_period MUST be a single atomic UPSERT statement. It must never be implemented as an application-level read-then-write (read the current row, add the delta in application code, write it back), because that pattern is vulnerable to two distinct races that were independently verified against this design:
1. Lost-update race — two concurrent actions by the same agent in the same period both read the same total_cost_cents value, both add their own delta in application memory, and both write back — the second write silently clobbers the first's contribution, permanently undercounting.
2. Duplicate-insert race — two concurrent "first action of a new period" attempts both check "does a row for this period exist yet," both see no, and both attempt to INSERT a new row — one succeeds, the other either errors or (worse, if not handled) creates a duplicate row for the same period, which breaks the UNIQUE (tenant_id, agent_identity_id, period_start) invariant's meaning as a single-source-of-truth counter.
The required shape is a single INSERT ... ON CONFLICT (tenant_id, agent_identity_id, period_start) DO UPDATE SET x = table.x + EXCLUDED.x statement, which Postgres serializes atomically — this closes BOTH races in one statement, because the insert-or-increment decision and the arithmetic happen inside the same atomic operation the database engine guarantees, not two separate steps an application can be interrupted between. The exact SQL shape (documented in the migration file itself as a comment, and restated here as the canonical reference for any future service-layer code):
```
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 (
  $tenant_id, $agent_identity_id, $period_start, $period_end,
  1, $cost_cents_delta, $quantity_delta, $tokens_delta, now()
)
ON CONFLICT (tenant_id, agent_identity_id, period_start)
DO UPDATE SET
  action_count      = ai.agent_usage_period.action_count      + EXCLUDED.action_count,
  total_cost_cents  = ai.agent_usage_period.total_cost_cents  + EXCLUDED.total_cost_cents,
  total_quantity    = ai.agent_usage_period.total_quantity    + EXCLUDED.total_quantity,
  total_tokens      = ai.agent_usage_period.total_tokens      + EXCLUDED.total_tokens,
  last_action_at    = EXCLUDED.last_action_at,
  updated_at        = now();
```
Any future AIService method (or shared cross-module runtime-logging helper) that increments agent_usage_period MUST use this exact INSERT ... ON CONFLICT ... DO UPDATE SET x = table.x + EXCLUDED.x shape — never a SELECT followed by an application-computed UPDATE. This requirement is binding on whoever builds the service layer, not a suggestion to revisit.
DR-69 — agent_memory's unique index is UNIQUE (tenant_id, category, key) WHERE status='active' (partial, not permanent), directly mirroring identity.agent_duty_grant's own already-locked precedent (UNIQUE (agent_identity_id, permission_id, scope_type) WHERE status='active'). The original design draft specified a permanent, non-partial UNIQUE (tenant_id, category, key) with no WHERE clause — a one-row-per-key-ever constraint. Independent verification proved this blocks a legitimate real case: a memory disabled for cause (found to be wrong, or poisoned per D11) that is later superseded by a new, unrelated-provenance fact recorded under the same (category, key). Under the permanent-unique design, once a key was ever used, it could never be reused even by a completely fresh, correctly-sourced memory — the disabled row would permanently block re-insertion. The partial-unique fix (scoped WHERE status='active') allows exactly one ACTIVE row per key at a time while permitting a disabled row and a subsequent fresh active row to coexist, preserving history rather than destructively overwriting it. Live-tested 3 scenarios: (a) attempting to insert two simultaneous ACTIVE rows for the same (tenant_id, category, key) — REJECTED by the partial unique index; (b) disabling the first row (status='disabled'), then inserting a fresh active row for the same key — SUCCEEDS; (c) after (b), both rows persist (2 total rows for that key) — the disabled row is preserved as history, not destructively overwritten or deleted.

Post-build fixes (DR-70 through DR-72) — caught by the independent Section 4 audit and adversarial verification agents run against the LIVE built DDL after the migration was first applied, not by the design-phase verification (DR-63 through DR-69) that ran against design prose.

DR-70 — ai_request's idempotency uniqueness split into two partial-unique indexes (ai_request_tenant_id_idempotency_key_unique WHERE tenant_id IS NOT NULL, ai_request_null_tenant_idempotency_key_unique WHERE tenant_id IS NULL), replacing a single UNIQUE (tenant_id, idempotency_key) WHERE idempotency_key IS NOT NULL. Why: tenant_id is nullable on this table for platform-level calls, and Postgres treats NULL as distinct in unique indexes — two NULL-tenant rows with the identical idempotency_key both inserted successfully under the single-unique form (live-reproduced). Same bug class already caught and fixed multiple times this project (identity.role, the original agent_duty_grant design); missed here because ai_request was carried forward from v1 without re-auditing its existing index shape against this exact trap. Guard: live-tested — two NULL-tenant rows sharing an idempotency_key are now rejected with duplicate key value violates unique constraint "ai_request_null_tenant_idempotency_key_unique".
DR-71 — agent_execution's target CHECK renamed and relaxed from chk_agent_execution_target_all_or_nothing to chk_agent_execution_target_module_table_together. Why: the all-or-nothing form required target_row_id to be set whenever target_module/target_table were set, making 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, directly contradicting the documented purpose of resolves_execution_id (DR-65), which exists specifically to link an untargeted proposal to its later fully-targeted execution. This bug was self-inflicted while fixing DR-65 — the all-or-nothing shape was copied from import_record's precedent without checking compatibility with the new propose/execute linkage being added. Fix: relaxed to (all three NULL) OR (target_module IS NOT NULL AND target_table IS NOT NULL) — target_row_id independently optional, module+table still required to travel together. Guard: live-tested — the tagged create-new-record proposal now succeeds, and executing it (setting a real target_row_id + resolves_execution_id) also succeeds.
DR-72 — agent_execution_resolves_execution_id_unique added: UNIQUE (resolves_execution_id) WHERE resolves_execution_id IS NOT NULL. Why: DR-65's self-FK had no uniqueness guard, so a retried "execute" call (not necessarily reusing the same idempotency_key — DR-66 only guards same-key retries) could create two rows both resolving the SAME 'proposed' row — live-reproduced as a distinct gap from DR-66's. Guard: live-tested — a second execution attempting to resolve an already-resolved proposal is rejected with duplicate key value violates unique constraint "agent_execution_resolves_execution_id_unique". Guarantees a proposal is resolved at most once.
DR-73 — ai_request.tenant_id/agent_identity_id consistency has no CHECK — logged to OPEN_ITEMS, not fixed as schema. Every agent belongs to exactly one tenant (identity.agent_identity.tenant_id NOT NULL), so a NULL-tenant ai_request row with agent_identity_id set to a real tenant-scoped agent is nonsensical, and nothing prevents it today. Found by adversarial verification; ruled low severity (data-hygiene gap for a not-yet-real platform-level write path, not an RLS leak). Not fixed: Postgres has no cross-table CHECK without a trigger, and "build thin" argues against a trigger for a caller that doesn't exist yet. See OPEN_ITEMS row 8 (§11).
DR-74 — decision_provenance.memory_refs has no tenant-consistency enforcement against ai.agent_memory — logged to OPEN_ITEMS, structurally unfixable at the schema level. A tenant-A row's memory_refs JSONB array could reference a tenant-B memory id with nothing preventing it. Found by adversarial verification. Not fixed: JSONB array elements cannot carry FK constraints in Postgres — this is a service-layer validation concern, not a schema gap that can be closed here. See OPEN_ITEMS row 9 (§11).

Remediation Plan Phase 1 (2026-07-08).

DR-75 — agent_execution gained 2 CHECK constraints closing a C8 financial-autonomy boundary gap, found by a cross-cutting senior-architect review: chk_agent_execution_authority_required_when_executed (status='executed' requires authority_level_applied NOT NULL) and chk_agent_execution_needs_approval_requires_resolution (status='executed' AND authority_level_applied='needs_approval' requires resolves_execution_id NOT NULL). Closes the gap where an agent action could be recorded as executed with no recorded authority level, or, for a needs-approval action, with no link to the resolving human decision. No column/table count change (118 cols unchanged). Full cross-module record: PROJECT_DECISIONS #37.

Remediation Plan Phase 2 (2026-07-08).

DR-76 — agent_execution.id's PK-generation strategy changed: DEFAULT gen_random_uuid() → DEFAULT platform.uuid_generate_v7() (Item 6 of a cross-cutting Remediation Plan Phase 2 pass). 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.

Remediation Plan Phase 4 (2026-07-08).

DR-77 — agent_memory gains subject_type/subject_ref/expires_at + chk_agent_memory_subject_consistency CHECK ((subject_type IS NULL) = (subject_ref IS NULL)) (Item 18 of a cross-cutting Remediation Plan Phase 4 pass, alongside crm.customer.pii_vault_ref in the same migration — the crm column is out of scope for this module spec). subject_ref is a polymorphic reference (target row's type named by subject_type, e.g. 'customer') and therefore carries no FK — a polymorphic column cannot be FK'd to one target table, the same structural constraint already governing agent_execution.target_row_id/import_record.target_row_id elsewhere in this schema. Most memory entries have no individual subject and leave both columns NULL (a tenant-wide learned pattern isn't "about" any one customer); the pair exists for the narrower case where a memory entry pertains to one identifiable subject and must be discoverable/erasable under a GDPR-style erasure request. The CHECK is a true bidirectional requirement — live-reproduced all 4 combinations: subject_type set/subject_ref NULL rejected; the reverse (subject_ref set/subject_type NULL) also rejected; both NULL accepted; both set accepted. This is a seam, not a built erasure feature — no scheduled sweep, AIService method, or cross-table tenant/subject-consistency enforcement exists yet (same disclosed class of gap as DR-74's memory_refs, logged separately since subject_ref is a new column, not the pre-existing JSONB key). Additive-only migration (ai.agent_memory confirmed 0 live rows at build time, zero backfill risk). Column count: 118→121 (+3, all on agent_memory), table count unchanged (7). Full cross-module record: PROJECT_DECISIONS #40 (Item 18).

AI_CAPABILITY_GAPS.md — gap-ruling table (G1–G11)

Gap	Ruling
G1 (agent-readable catalog flag)	None — out of scope for this module (API-layer/catalog-flag concern, already ruled out for `inventory`'s own build).
G2 (MCP server endpoints)	None — genuinely out of scope (API-layer concern).
G3 (multi-agent handoff)	Considered, correctly deferred — no delegation/handoff table built here either; `decision_provenance.delegated_by_actor_id` stays a documented JSONB key with no backing table, consistent with every other module's ruling on G3 so far.
G4 (semantic layer)	Cross-cutting, not `ai`-specific; out of scope.
G5 (agent memory)	BUILT. Unlike `crm`/`inventory`'s passes (where `memory_refs` pointed at nothing), this pass gives it a real target: `ai.agent_memory.id` — a genuine table, schema-built 2026-07-06, not just documentation.
G6 (simulation/sandbox)	None — genuinely out of scope (testing concern).
G7 (trajectory evals / OTel)	None — genuinely out of scope (testing/observability concern).
G8 (proactive/scheduled agents)	Cross-cutting shared infrastructure, not `ai`'s alone to build; out of scope this pass.
G9 (agent marketplace)	None — genuinely out of scope (future-platform concern).
G10 (voice/vision capture)	None — N/A for this module (`ai` is invisible infrastructure, not a capture surface; D15 is N/A here).
G11 (agent identity standards)	None — genuinely out of scope (`identity.agent_identity`/A5 already covers the concept proprietarily).

11. Deferred / Future Items

All items tracked in docs/open-items/OPEN_ITEMS.md, attributed to ai (PROJECT_DECISIONS #25). Summary for context:

Item	Status	Trigger
`agent_duty_grant.spend_limit_cents` cumulative/period ceiling	deferred	No spending agent exists yet (reorder is quantity-only; money lives in the unbuilt Purchasing module). 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.
`enrichment_job` (batch re-enrichment tracking)	deferred v1.5 (re-logged)	Trigger: when scheduled batch re-enrichment (re-verify all unverified plants) becomes a product requirement.
`ai_response_cache` (popular query result cache)	deferred to consumer phase (re-logged)	Belongs in the `ai` schema (not `consumer_app`) per v1's own DR6 forward-decision. Trigger: build at consumer phase.
`ai_feedback` (thumbs-up/down quality signals)	deferred v1.1 (re-logged)	Trigger: when AI feature quality needs structured user-feedback collection.
`anomaly_alert` (persisted anomaly detection results)	deferred v1.1 (re-logged)	Trigger: when anomaly persistence/acknowledgement workflow is a product requirement.
`import_file.file_id` forward-ref	deferred	The `files` module doesn't exist in v2 (verified live). Trigger: when the `files` module is built.
`AIService` (agent-runtime service methods)	open	No `AIService` extensions exist yet for the 3 new tables — schema-only this pass, same pattern as every other module's deferred service layer. Trigger: when `AIService` (or a shared cross-module runtime-logging helper) is built — MUST follow the atomic-upsert requirement in DR-67/DR-68.
`ai_request.tenant_id`/`agent_identity_id` consistency (DR-73)	open, low severity	Found by adversarial verification; no cross-table CHECK possible without a trigger. Trigger: when a real platform-level AI call path with `agent_identity_id` set is built.
`decision_provenance.memory_refs` cross-tenant reference risk (DR-74)	open, structurally unfixable at schema level	JSONB array elements cannot carry FK constraints in Postgres. Trigger: when `AIService` (or a shared cross-module runtime-logging helper) is built — validate tenant-scoping at the service layer.
`agent_memory.subject_type`/`subject_ref` GDPR erasure seam (DR-77, PROJECT_DECISIONS #40 Item 18)	open	Schema-only seam, added Remediation Phase 4 (2026-07-08); no erasure job or service consumes it yet, and (structurally, same as DR-74) no cross-table tenant/subject-consistency CHECK exists. 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.

12. Cross-Module Seams

Seams are cataloged in docs/modules/CROSS_MODULE_CONTRACTS.md (referenced, not restated here). Key relationships:

ai → identity: ai_request.agent_identity_id, agent_execution.agent_identity_id, agent_usage_period.agent_identity_id → identity.agent_identity.id (enforced); agent_execution.permission_id → identity.permission.id (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.id (enforced).
ai → agent_duty_grant: agent_execution.authority_level_applied is a point-in-time snapshot of what agent_duty_grant.authority_level applied at action time — no FK (a snapshot, not a live reference), documented relationship only. agent_duty_grant.authority_level can change later; the snapshot preserves what actually gated the action, for audit.
ai → Files (deferred, unchanged from v1): import_file.file_id — plain uuid, no FK, files schema absent from v2 (verified live).
NEW seam: crm/inventory → ai.agent_memory. decision_provenance.memory_refs (documented on crm.customer-equivalent tables and inventory.item/item_variant/stock/stock_adjustment_request/stock_count/item_merge_candidate/item_merge) now resolves to a REAL row: ai.agent_memory.id. This is a plain-UUID reference inside a JSONB array (not an enforced FK — the same "reference inside JSONB, not a column-level FK" pattern decision_provenance already uses everywhere), but is documented explicitly here since this is the first time memory_refs has had a real target. Unlike crm/inventory's own passes (where memory_refs pointed at nothing), this pass closes that gap.

Last modified: Jul 8, 2026, 2:58 PM PT

ai — Module Spec