Design Rationale — Ai
Non-obvious design choices for the ai module — the WHY behind each decision.
AI Module (Locked 2026-06-11)
DR1 — inferred_mapping JSONB is per-file ephemeral, NOT integrations.field_mapping
Decision: import_file.inferred_mapping stores the AI-inferred column→field mapping as per-file JSONB. It is deliberately NOT integrations.field_mapping and is never reused across files or jobs. One inference per file; discarded after the import job completes.
Why: Zero-mapping and deterministic import are fundamentally different operations. Zero-mapping: the AI looks at an arbitrary file and guesses what's in it — the mapping is specific to that file's headers and layout and has no reuse value. Deterministic: a human has pre-configured a reusable mapping in integrations.field_mapping for a known-format connector (Picas CSV, QuickBooks export). Sharing the mapping table would force AI inference results into a config table designed for stable reusable mappings, mix two unrelated ownership domains (AI inference is AIService's concern; field_mapping is IntegrationsService's concern), and create an implicit coupling that would surface as a broken abstraction at implementation. JSONB on import_file is intentionally "throw away" — cheap, colocated with the file, and reset on every import.
Rejected: Storing inferred_mapping in integrations.field_mapping (wrong ownership, wrong lifecycle, wrong reuse model). A separate ai_field_mapping table (same problems as above — one-inference-per-file doesn't warrant a standalone table). Sharing via inheritance/extension (no shared base in this codebase; the models are unrelated).
Guard: Never model AI import mappings as integrations.field_mapping rows. import_file.inferred_mapping is per-file, ephemeral — it is the output of AI inference, not a configuration artifact. If a human later wants to save a mapping as a reusable template, that is a new feature (explicit user action, stored in Admin settings or Integrations config) — not an automatic promotion of inferred_mapping.
DR2 — Human-in-the-loop CHECKs on import_record are the safety rail
Decision: import_record enforces two DB-level CHECK constraints: (1) load_status='loaded' requires 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; (2) polymorphic target all-or-nothing (module/table/id all NULL or all NOT NULL). These are non-negotiable safety rails.
Why: AI-inferred data must never auto-load into live tenant tables without a human (or explicitly configured auto-accept) decision on record. Without the CHECK, a service bug could set load_status='loaded' on a review_status='pending_review' row, silently inserting unreviewed AI guesses into inventory.item, crm.customer, or purchasing.vendor. Once live data is polluted with incorrect AI-inferred rows, the damage is hard to detect and hard to reverse — the tenant sees bad inventory counts, bad customer records, or bad vendor entries with no indication they came from an unreviewed import. The DB CHECK makes this class of bug impossible at the storage layer, not just the service layer.
Rejected: Enforcing the human-in-the-loop invariant at the service layer only (a service bug or a direct DB write from a migration would bypass it). Soft-deleting reviewed records and writing new loaded records (adds complexity without safety benefit — the CHECK is cleaner).
Guard: Never weaken or remove the load-decision CHECK on import_record. A load_status='loaded' row that doesn't satisfy review_status IN ('auto_accepted','accepted') is a data integrity violation. 'auto_accepted' is an intentional carve-out (high-confidence rows skip the review UI but the decision is still recorded on the row — this is NOT a bypass of human-in-the-loop; it IS a human-configured acceptance policy).
DR3 — Cluster 2 is service-layer + ai_request call log; AI features get no tables
Decision: Vrida Sage, send-time optimization, plant enrichment, anomaly detection, and consumer plant-care AI are AIService logic over existing module data. None of them gets a new table in v1.0. The only cluster 2 schema artifact is ai_request (the Bedrock-call log). All AI features route through AIService; no module calls Bedrock directly.
Why: Each of these features reads or writes to existing tables (shared.plant, notifications.delivery_attempt, inventory.*, crm.*, pos.*) and returns a result to the calling service. They are compute-over-existing-data, not stateful services that need their own persistence. Adding a table per AI feature would pre-build infrastructure for requirements that haven't arrived — a Sage query result doesn't need to outlive the HTTP request; an anomaly detection result is an instantaneous computation; send-time optimization is a recommendation, not a stored record. ai_request is the only exception because operational observability (cost, latency, token spend, error rate) applies uniformly to all AI calls and genuinely needs durable logging.
Rejected: A sage_session or sage_query table (Sage query results are ephemeral — log the Bedrock call in ai_request, no session state needed at v1.0). A send_time_recommendation table (recommendations are computed and consumed immediately by NotificationsService; no persistence needed). An anomaly_event table (deferred to v1.1 when persistence + acknowledgement workflow is a product requirement — see Deferred list). A separate ai_enrichment_job table (deferred to v1.5 when scheduled batch re-enrichment is a product requirement).
Guard: When an AI feature is proposed that seems to need a new table, ask: does it need to persist state BEYOND the current request/job? If the state is already captured in existing module tables (or in ai_request for the call metadata), no new table is needed. Add a table only when a feature needs durable state of its own that no existing table captures.
DR4 — ai_request reference-don't-copy (parallel to integrations.provider_call)
Decision: ai_request logs mechanical Bedrock call metadata only: model ID, caller module, token counts (not content), cost estimate, latency, status, error detail, idempotency key. It NEVER stores prompt text, response content, or business data from the calling module.
Why: AI prompts and responses carry sensitive tenant data — plant names, customer information, purchase history, inventory details — depending on the calling feature. Storing that content in ai_request would: (a) duplicate sensitive data from the module that already owns it (two-source-of-truth violation); (b) make ai_request a de-facto second copy of business data, requiring separate RLS, GDPR handling, and retention policy; (c) blow out row sizes for a table designed as a high-volume mechanical call log. ai_request is the AI-layer equivalent of integrations.provider_call, which logs that an API call happened but not what was in the API payload. The calling module's business context (the import_record, the Sage query result, the enrichment being applied to shared.plant) is where the domain content belongs, if it needs to be persisted at all.
Rejected: Adding prompt_text and response_text columns (violates reference-don't-copy; PII exposure; two sources of truth). A separate ai_request_detail table for content (same violations; just moves them one join away).
Guard: Never add prompt_text, response_text, prompt, response, content, body, or any column containing AI input/output content to ai_request or any mechanical log table in this schema. prompt_token_count and completion_token_count are counts (metadata); they are not content.
DR5 — import_job status vs. tenant_setup_task retry — no duplication
Decision: import_job owns the import lifecycle (uploading→processing→review_pending→loading→completed/failed/cancelled). platform.tenant_setup_task owns task-level retry (retry_count, next_retry_at) and milestone tracking for onboarding flows. setup_task_code on import_job is a text seam (not an FK) connecting the two. Neither table duplicates the other's concern.
Why: tenant_setup_task is a generic onboarding milestone tracker — it tracks whether the tenant has completed setup steps (inventory imported, customers imported, etc.) and drives retry if an onboarding step fails. import_job is a specific data ingestion artifact — it tracks whether a particular batch of files was parsed, reviewed, and loaded. These are different lifecycles: an onboarding task might trigger multiple import jobs (first attempt fails, retry creates a new job); tenant_setup_task.retry_count tracks the attempt number at the task level, while each import_job has its own terminal status. If import_job duplicated retry_count/next_retry_at, there would be two sources of truth for "how many times has this onboarding step been attempted."
Rejected: Adding retry_count and next_retry_at to import_job (would duplicate task-level retry state from platform.tenant_setup_task). Moving milestone tracking into import_job (would require AI module to know about the onboarding lifecycle structure — wrong ownership).
Guard: import_job must not carry retry_count or next_retry_at columns. Task-level retry is platform.tenant_setup_task's concern. If per-file or per-record retry is ever needed (e.g. retry a failed parse), that state belongs on import_file or import_record respectively — not on import_job as a task-level retry counter.
DR6 — ai_response_cache belongs in ai schema, not consumer_app
Decision: When built at consumer phase, the query-result cache for consumer-facing AI features (plant-care chat, Vrida Sage responses) belongs in the ai schema, not the consumer_app schema. This is a forward-decision recorded at AI lock; the table does not exist yet.
Why: Cache tables are AI infrastructure, not consumer-app feature data. ai_response_cache would cache computed AI responses (keyed by query hash, model, tenant/plant context) to avoid redundant Bedrock calls. This is an AIService concern — the cache is populated and read by AIService; consumer_app calls AIService and receives results, unaware of caching. Putting the cache table in consumer_app would couple AI infrastructure to the consumer layer's schema lifecycle and would require consumer_app to own a table that has no business meaning to the consumer product. ai owns all AI infrastructure; this table is AI infrastructure.
Rejected: consumer_app.ai_response_cache (wrong ownership — consumer_app is a product feature module; it should not own infrastructure tables). integrations.ai_response_cache (AI is its own module). A separate ai_cache schema (unnecessary granularity; ai schema is the right home).
Guard: When building ai_response_cache, place it in the ai schema. The table is AI infrastructure (cache management, TTL, key hashing, model versioning) that happens to serve consumer-facing features — it is not a consumer feature table. Consumer Layer modules call AIService; they do not write to or read from ai.* directly.