ai — Phase 5

Schema locked 2026-06-11. 4 tables, 71 cols: import_job (17), import_file (15), import_record (21), ai_request (18).

AI owns two clusters: the onboarding import pipeline (zero-mapping AI ingestion) and the AI feature runtime (the Bedrock-call log — all other AI features are service-layer over existing module data, not schema). Cluster 1 closes the platform.tenant_setup_task onboarding-import dependency. All tables tenant-scoped with RLS; ai_request.tenant_id nullable for platform-level AI calls (documented below).

Design principles (document at every session touching this schema):

• Zero-mapping vs. deterministic (the Integrations boundary). AI import = arbitrary file, AI infers structure per-file (ephemeral mapping, JSONB on import_file). Integrations = known-format recurring connector with reusable field_mapping config. import_file.inferred_mapping is one-file, one-inference, never reused — deliberately NOT Integrations' field_mapping table. Deterministic imports (Picas CSV v1.1, structured exports via integrations.connector with source='file') stay in Integrations. The boundary is: if a human must tell the system what the file contains, it belongs in Integrations; if the AI figures that out, it belongs here.
• Human-in-the-loop. AI proposes → human reviews / corrects → THEN loads. The review workflow lives on import_record (confidence_score, review_status, corrected_data). AI-inferred data is NEVER auto-loaded into live tenant tables without a decision recorded. Low-confidence rows surface for mandatory review; high-confidence 'auto_accepted' rows skip the review UI but still have a decision recorded on the row.
• Cluster 2 is service-layer + the call log. Vrida Sage, send-time optimization, plant enrichment, and anomaly detection are AIService logic over existing module data. They need no new tables. The only cluster 2 schema is ai_request. All AI features route through AIService; no module calls Bedrock directly.
• Reference-don't-copy on ai_request. Logs that an AI call happened (model, tokens, cost, latency, status, caller). NEVER stores prompt text or response content — those belong to the calling module's business context. Same principle as integrations.provider_call. Aggregated per tenant per period to increment platform.tenant_usage_summary.ai_calls_count.
• Bytes via Files. Uploaded import files live in R2 via the Files module. import_file.file_id FKs to files.file; bytes accessed via FilesService.getSignedUrl(). This module never stores file content in Postgres.
• Generic-first. import_job / import_file / import_record work for any vertical and entity type. Nursery-specific logic (plant name normalization via shared.plant_common_name.name_normalized, USDA zone matching) lives in AIService — not in the schema.

Cross-Phase FK seams:

Column	References	Status
*.tenant_id	platform.tenant	Locked — enforced
import_job.created_by_user_id	identity.identity_user	Locked — enforced (nullable)
import_file.import_job_id	ai.import_job	Intra-schema — enforced
import_file.file_id	files.file	Locked — enforced
import_record.import_file_id	ai.import_file	Intra-schema — enforced
import_record.import_job_id	ai.import_job	Intra-schema — enforced (denormalized for job-level queries)
import_record.reviewed_by_user_id	identity.identity_user	Locked — enforced (nullable)
import_record.target_module / target_table / target_row_id	inventory.item, crm.customer, purchasing.vendor (polymorphic)	NOT enforced FK — loose polymorphic backref, same pattern as audit_log.source_table / source_ref. All-or-nothing CHECK enforced.
import_job.setup_task_code	platform.tenant_setup_task.task_code	Text seam — not an FK. References 'inventory_imported' / 'customers_imported' / 'vendors_imported'. Job-level retry lives on tenant_setup_task (retry_count / next_retry_at already there — not duplicated here). AIService writes completed_at + result to tenant_setup_task on import completion.
ai_request.tenant_id	platform.tenant	Nullable — platform-level AI calls (e.g. shared.plant enrichment, which writes non-tenant-scoped data) have no tenant context. Written via service_role.
AI writes shared.plant (data_source='ai_generated', is_verified=false) + shared.plant_common_name aliases	shared schema	Service-layer write seam — AIService writes via service_role. No AI-side table needed; shared.plant.updated_at + data_source track enrichment provenance.
ai_request count aggregated → platform.tenant_usage_summary.ai_calls_count	platform.tenant_usage_summary	Write-back seam — aggregation job increments ai_calls_count. tenant_entitlement (entitlement_code='ai_pack') owns the tier cap.
Send-time optimization reads notifications.delivery_attempt.opened_at / clicked_at	notifications.delivery_attempt	Service-layer read seam — AIService queries engagement timestamps and returns timing recommendation to NotificationsService. No AI table.

Deferred items (with forward-decisions):

• enrichment_job (batch re-enrichment tracking): DEFERRED v1.5. v1.0 plant enrichment is service-layer — AIService writes shared.plant, logs Bedrock calls to ai_request, shared.plant.updated_at shows enrichment time. Add 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. FORWARD-DECISION (recorded here): this table belongs in the ai schema (AI infrastructure, not consumer-app feature data) — resolves the open "customer_app OR ai schema" question in PROJECT_DECISIONS. Build it here at consumer phase.
• ai_feedback (thumbs-up/down quality signals): DEFERRED v1.1. Not needed until AI feature quality needs structured user-feedback collection.
• anomaly_alert (persisted anomaly detection results): DEFERRED v1.1. v1.0 anomaly detection is service-layer returning live results logged to ai_request. Add when anomaly persistence / acknowledgement workflow is a product requirement.
• Service-layer-only AI features (NO tables, documented to prevent future "add a table" impulse): Vrida Sage (queries existing schemas, logs ai_request); send-time optimization (reads delivery_attempt, logs ai_request); plant enrichment v1.0 (writes shared.plant, logs ai_request); consumer plant-care AI chat (deferred to consumer phase; cache in ai schema when built).

---

ai.import_job — 17 cols

Import batch header — one row per tenant import run (onboarding-gated or manual post-onboarding re-import). Owns the import lifecycle status independently of platform.tenant_setup_task, which owns task-level retry (retry_count / next_retry_at) and milestone tracking for onboarding flows. A manual re-import has no setup_task_code.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
source	text	NOT NULL	—	CHECK IN ('onboarding','manual'). 'onboarding' = gated by setup, fulfills a tenant_setup_task milestone; 'manual' = post-onboarding re-import, no task dependency.
setup_task_code	text	nullable	—	The platform.tenant_setup_task.task_code this import fulfills: 'inventory_imported', 'customers_imported', or 'vendors_imported'. NULL for source='manual'. Text seam — not an FK.
target_entity	text	NOT NULL	—	CHECK IN ('inventory_item','customer','vendor','mixed'). The entity kind(s) this job loads. 'mixed' when a single job spans multiple entity types.
status	text	NOT NULL	'uploading'	CHECK IN ('uploading','processing','review_pending','loading','completed','failed','cancelled'). Own lifecycle, independent of tenant_setup_task.status.
file_count	integer	NOT NULL	0	Number of files attached; incremented as files are uploaded.
record_total	integer	nullable	—	Total parsed rows across all files; populated after parsing completes.
record_loaded	integer	NOT NULL	0	Rows successfully loaded to target entity tables.
record_rejected	integer	NOT NULL	0	Rows rejected (human-rejected or load-failed).
started_at	timestamptz	nullable	—	When AIService began processing.
completed_at	timestamptz	nullable	—	When job reached 'completed' or 'failed'.
created_by_user_id	UUID	nullable	—	FK → identity.identity_user. NULL for system-triggered onboarding flows.
failure_reason	text	nullable	—	Human-readable failure description for terminal 'failed' status.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• on (status) WHERE status IN ('processing','review_pending','loading')
• on (source)
• on (setup_task_code) WHERE setup_task_code IS NOT NULL
• on (tenant_id, created_at)

Constraints:

• CHECK (source-task-code coherence): (source = 'manual' AND setup_task_code IS NULL) OR (source = 'onboarding' AND setup_task_code IS NOT NULL) — onboarding imports must carry their milestone code so platform.tenant_setup_task can be closed; manual re-imports have no task dependency and must not carry one.

---

ai.import_file — 15 cols

One uploaded file within an import job. AI-inferred column mapping stored as per-file JSONB — ephemeral, never reused across files or jobs (zero-mapping = each file's structure is inferred independently). file_id FKs to files.file; bytes accessed via FilesService, never re-stored here.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
import_job_id	UUID	NOT NULL	—	FK → ai.import_job
file_id	UUID	NOT NULL	—	FK → files.file. R2 bytes accessed via FilesService.getSignedUrl(). File content is never re-stored in Postgres.
original_filename	text	NOT NULL	—	Filename as submitted by the tenant.
detected_type	text	nullable	—	CHECK (col IS NULL OR col IN ('csv','excel','pdf','image','other')). AI-detected file format.
detected_entity	text	nullable	—	CHECK (col IS NULL OR col IN ('inventory_item','customer','vendor','unknown')). AI's determination of what entity type the file contains.
inferred_mapping	JSONB	nullable	—	The AI-inferred per-file column→field mapping. Per-file, ephemeral — one inference per file, never reused. Deliberately NOT integrations.field_mapping. Example shape: {"Plant Name":"item.name","Retail Price":"item_variant.base_price_cents","SKU":"item_variant.sku","USDA Zone":"item.attributes.usda_zone"}. NULL until AI inference completes.
mapping_confidence	numeric	nullable	—	0–1. AI's overall confidence in the inferred mapping for this file.
status	text	NOT NULL	'uploaded'	CHECK IN ('uploaded','parsing','parsed','failed')
row_count	integer	nullable	—	Rows detected in the file; populated after parsing.
parse_error	text	nullable	—	Parse failure detail for 'failed' status.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

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

---

ai.import_record — 21 cols

One parsed row per file — the load-bearing table of the import pipeline. Carries both the AI proposal (mapped_data, confidence_score) and the human decision (review_status, corrected_data, reviewed_by_user_id). Only 'accepted' or 'auto_accepted' rows are loaded to target entity tables; no AI-inferred data reaches live tables without a decision recorded. Polymorphic target ref (target_module / target_table / target_row_id) records the created entity after successful load — same pattern as audit_log.source_table / source_ref.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	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. Denormalized — avoids join through import_file for job-level status queries.
source_row_number	integer	nullable	—	Row position in the source file. NULL for records extracted from unstructured sources (PDF, image).
raw_data	JSONB	NOT NULL	—	The row as parsed, before AI mapping is applied. Preserved for audit and re-processing. Example shape (raw strings, pre-mapping): {"Plant Name":"Japanese Maple","Retail Price":"29.99","SKU":"JM-001"}.
mapped_data	JSONB	nullable	—	AI's mapping applied to raw_data — the proposed entity field values. NULL until AI processing completes. Example: {"name":"Japanese Maple","sku":"JM-001","base_price_cents":2999}.
confidence_score	numeric	nullable	—	0–1. AI confidence for this row's field mapping. Low-confidence rows surface for mandatory human review.
review_status	text	NOT NULL	'pending_review'	CHECK IN ('pending_review','auto_accepted','accepted','rejected'). 'auto_accepted' = above-threshold confidence, review UI skipped, decision still recorded.
corrected_data	JSONB	nullable	—	Human's correction of the AI mapping. When present, this (not mapped_data) is what loads to the target entity. NULL if tenant accepted the AI proposal without changes.
reviewed_by_user_id	UUID	nullable	—	FK → identity.identity_user. NULL for 'auto_accepted' rows.
reviewed_at	timestamptz	nullable	—	When the human review decision was recorded.
load_status	text	NOT NULL	'pending'	CHECK IN ('pending','loaded','failed','skipped')
target_module	text	nullable	—	Module owning the loaded entity: 'inventory', 'crm', 'purchasing'. Part of polymorphic target ref.
target_table	text	nullable	—	Table of the loaded entity: 'item', 'customer', 'vendor'. Part of polymorphic target ref.
target_row_id	UUID	nullable	—	PK of the created entity row. NOT an enforced FK — polymorphic loose backref, same pattern as audit_log.source_ref.
load_error	text	nullable	—	Load failure detail for load_status = 'failed'.
loaded_at	timestamptz	nullable	—	When the record was successfully loaded to the target entity table.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Constraints:

• CHECK (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 loaded row must have been accepted AND have a full target ref.
• CHECK (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) — partial target refs are a data bug.

Indexes:

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

---

ai.ai_request — 18 cols

Bedrock-call log — mechanical record of every inference request routed through AIService. Logs call metadata (model, caller, token counts, cost, latency, status) but NEVER the prompt text or response content (reference-don't-copy — same principle as integrations.provider_call). Aggregated per tenant per billing period to increment platform.tenant_usage_summary.ai_calls_count. tenant_id is nullable: platform-level AI calls (e.g. shared.plant enrichment, writing non-tenant-scoped data) have no tenant context and are written via service_role.

RLS: tenant-isolated on tenant_id WHERE tenant_id IS NOT NULL. Rows with tenant_id IS NULL (platform-level calls) readable via service_role only — not exposed to tenant sessions.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	nullable	—	FK → platform.tenant. NULL for platform-level / non-tenant AI calls (e.g. shared.plant enrichment). See RLS note above.
caller_module	text	NOT NULL	—	CHECK IN ('ai_import','sage','notifications','enrichment','anomaly','platform'). The module that invoked AIService.
feature	text	nullable	—	Finer-grained context within a module. Examples: 'import_inference', 'sage_query', 'send_time', 'plant_enrichment', 'plant_care_chat'.
model_id	text	NOT NULL	—	Bedrock model identifier, e.g. 'anthropic.claude-haiku-4-5'. Model-as-config: no schema change required to switch models.
request_type	text	nullable	—	CHECK (col IS NULL OR col IN ('completion','embedding','vision')). The type of Bedrock inference request.
prompt_token_count	integer	nullable	—	Input tokens consumed; populated from Bedrock response metadata.
completion_token_count	integer	nullable	—	Output tokens generated.
total_token_count	integer	nullable	—	Sum of prompt + completion tokens; computed by AIService from response metadata.
cost_millicents	bigint	nullable	—	Estimated cost in millicents (1/1000 cent) for per-call cost precision. Computed by AIService from Bedrock pricing config.
latency_ms	integer	nullable	—	Wall-clock milliseconds from request dispatch to response received.
status	text	NOT NULL	'success'	CHECK IN ('success','failed','rate_limited','timeout')
error_detail	text	nullable	—	Error message / Bedrock error code for non-'success' statuses.
idempotency_key	text	nullable	—	Caller-supplied idempotency key for retryable calls (e.g. import inference retries).
requested_at	timestamptz	NOT NULL	—	When AIService dispatched the Bedrock request. Distinct from created_at (DB row insert time).
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id) WHERE tenant_id IS NOT NULL
• on (caller_module)
• on (model_id)
• on (status) WHERE status != 'success'
• on (requested_at)
• PARTIAL UNIQUE on (tenant_id, idempotency_key) WHERE idempotency_key IS NOT NULL AND deleted_at IS NULL

---

Column counts: import_job(17) + import_file(15) + import_record(21) + ai_request(18) = 71

---