# AI Studio: chat-driven reports/dashboards module for the CRM

## Context

The original ask ("implement MCP") was superseded: the user instead wants an **AI Studio** — a
self-contained module inside the existing Laravel CRM API where a user chats in natural language and
gets back summaries, dashboard-style charts, and tables generated from live CRM data, with the
conversation history persisted. Confirmed decisions:

- **Bring-your-own API key, any provider** — the user pastes their own LLM API key (Anthropic, OpenAI,
  etc.); the backend must be provider-agnostic, not hardwired to one vendor.
- **Flexible query builder tool**, not a fixed report catalog — the LLM composes filtered/aggregated
  queries against an allow-listed set of CRM tables/columns, rather than picking from a small fixed
  set of canned reports. This is more powerful but is the module's main security surface, so the
  allow-list and query construction must live entirely in backend code the LLM cannot bypass.
- **Synchronous request/response** — no queue/broadcast needed for v1; a chat message POST blocks
  until the assistant's reply (text + any chart/table payload) is ready.

Codebase research findings that shape this plan:
- **No encryption precedent exists.** `SmsCredential`, `WabaCredential`, etc. store third-party API
  keys as **plaintext** columns (`app/Models/SmsCredential.php` and siblings). This is a deliberate
  deviation: the AI Studio API-key column should use Laravel's `encrypted` cast, not follow the
  plaintext convention, because it's a user-supplied external-service credential typed in from the
  outside. Flag this explicitly as new practice, not copy the existing pattern.
- **No multi-tenancy trait/global scope exists** (`docs/architecture.md` even states "Multi-tenancy:
  Not implemented"). Every model just carries a plain `company_id` FK and controllers manually
  `where('company_id', ...)`. AI Studio follows the same manual-scoping convention — no new
  abstraction — and critically, **`company_id`/`user_id` scoping must be injected server-side into
  every query the tool builds, never taken from LLM output**, since that's the tenant-isolation
  boundary.
- **No generic Settings model exists** — closest analog is `UserAppSetting` (fixed columns, not
  key/value). A new table is needed for storing the user's provider + encrypted key.
- **Service layer convention** (`app/Services/CampaignService.php`): plain class, constructor-injected
  `readonly` dependencies, verb-named public methods, `DB::transaction()` for multi-step writes,
  instantiated via container injection in controllers — no facades, no interfaces unless there's a
  real second implementation (there will be one here: multiple LLM providers).
- **JSON column pattern**: `CampaignChannel.credentials_config` — migration `$table->json(...)->nullable()`,
  model cast `'x' => 'array'`. Reuse this for chat message content, tool-call payloads, and
  chart/table attachments.
- Per CLAUDE.md: reuse Service classes, Form Requests, avoid raw SQL, protect sensitive fields, audit
  logging for mutating actions (not applicable here since AI Studio is read-only against CRM data).

## Architecture

```
POST /api/ai-studio/conversations/{id}/messages
        │
        ▼
AiStudioController → AiStudioChatService
        │
        ├─ loads Conversation + prior Messages (chat history)
        ├─ resolves the user's stored provider + decrypted API key
        ├─ builds an AiProviderClient (Anthropic | OpenAI | ...) via a small factory
        ├─ runs the tool-use loop:
        │     LLM ⇄ query_crm_data tool ⇄ CrmQueryBuilderService (allow-listed, company-scoped)
        │     LLM ⇄ render_visualization tool ⇄ formats the *actual* query rows into a chart/table shape
        ├─ persists the new user + assistant Messages (with attachments) to DB
        └─ returns the assistant's reply + attachments to the client
```

### Why two tools instead of one

`query_crm_data` fetches real numbers; `render_visualization` only tells the backend *how to shape*
already-fetched numbers (chart type, axis fields, table columns) — it never re-emits data itself. This
prevents the model from hallucinating figures into a chart: every number in the final payload is
traceable to a query result, not to LLM-generated text.

## Data model (new tables)

- **`ai_studio_credentials`** — `id, user_id (FK), provider (enum: anthropic|openai), api_key (encrypted
  cast), model (string, nullable — e.g. "claude-opus-5"), created_at, updated_at`. One row per user
  per provider; `api_key` never returned by any API response (add to `$hidden` on the model).
- **`ai_studio_conversations`** — `id, user_id (FK), company_id (FK), title (string, nullable —
  derived from first message), provider, model, created_at, updated_at`.
- **`ai_studio_messages`** — `id, conversation_id (FK), role (enum: user|assistant|tool), content (text),
  tool_calls (json, nullable), attachments (json, nullable — chart/table payloads), created_at`.

Follow existing migration conventions in `database/migrations/` (check most recent campaign migrations
for naming/index style before writing these).

## Backend components to build

1. **`app/Services/AiStudio/AiProviderClient` (interface)** — `sendMessage(array $history, array $tools):
   AiProviderResponse`. Two concrete implementations:
   - `AnthropicProviderClient` — Anthropic Messages API with tool use (see the Claude API reference
     already available in this session for exact request/response shapes: `tools` array, `tool_use`/
     `tool_result` content blocks, `stop_reason` handling). Use the official `anthropic-php` SDK if
     added to `composer.json`, or raw HTTP via Laravel's `Http` facade if not — decide during
     implementation based on what's already installed.
   - `OpenAiProviderClient` — OpenAI Chat Completions/function-calling, same interface contract.
   - A small `AiProviderClientFactory` resolves the right implementation from the user's stored
     `ai_studio_credentials.provider`.

2. **`app/Services/AiStudio/CrmQueryBuilderService`** — the security-critical piece. Holds a hardcoded
   allow-list: which Eloquent models (`Lead`, `Task`, `LeadSale`, `Campaign`, …) are queryable, which
   columns may be selected/filtered/grouped on, which aggregate functions are permitted
   (`count`, `sum`, `avg`), and which relations may be eager-loaded. The tool's JSON schema (what the
   LLM fills in) maps to a small typed spec — entity, filters (field/operator/value triples), groupBy,
   aggregate, dateRange, limit — which this service validates against the allow-list and translates
   into Eloquent query builder calls (`where`, `groupBy`, `selectRaw` only for whitelisted aggregate
   expressions — never raw LLM-supplied SQL strings). **`company_id` (and any other tenant/ownership
   scoping already used by `LeadController` etc.) is always applied by this service from the
   authenticated user's context, never from the LLM's tool-call arguments.** Cap `limit` server-side
   (e.g. max 500 rows) regardless of what the LLM requests.

3. **`app/Services/AiStudio/AiStudioChatService`** — orchestrates one turn: loads conversation history,
   calls the provider client, runs the tool-use loop (dispatch `query_crm_data` /
   `render_visualization` calls to `CrmQueryBuilderService`, feed results back to the model), persists
   messages, returns the final structured reply. Mirrors `CampaignService`'s constructor-injection
   style.

4. **`AiStudioCredentialController` + `AiStudioConversationController`** — thin controllers, Form
   Request validation (`StoreAiStudioCredentialRequest`, `SendAiStudioMessageRequest`), delegate to the
   services above, return the project's standard `{success, message, data}` envelope. Add to
   `routes/app.php` behind the existing `account_validation` + `auth:sanctum` middleware group, same
   as every other resource route.

5. **Migrations + Models** — `AiStudioCredential`, `AiStudioConversation`, `AiStudioMessage`, each with
   `$fillable`, appropriate `$casts` (`'api_key' => 'encrypted'`, `'tool_calls' => 'array'`,
   `'attachments' => 'array'`), and `$hidden = ['api_key']` on the credential model.

6. **API Resources** — `AiStudioConversationResource`, `AiStudioMessageResource` shaping the JSON
   response (never serialize `api_key`).

## Endpoints (draft — finalize exact paths against `routes/app.php` conventions)

```
POST   /api/ai-studio/credentials            store/update the user's provider + API key
GET    /api/ai-studio/credentials            list configured providers (no key value returned)
DELETE /api/ai-studio/credentials/{id}       remove a stored key

GET    /api/ai-studio/conversations          list the user's conversations
POST   /api/ai-studio/conversations          start a new conversation
GET    /api/ai-studio/conversations/{id}     conversation + full message history
POST   /api/ai-studio/conversations/{id}/messages   send a message, get the assistant's reply
DELETE /api/ai-studio/conversations/{id}     delete a conversation
```

## Verification

1. `php artisan migrate` — confirm the three new tables + indexes.
2. Store a real API key via `POST /ai-studio/credentials`, confirm the DB column is encrypted
   (raw `SELECT api_key` should not be readable) and never appears in any JSON response.
3. Start a conversation, ask a question that requires aggregation across leads (e.g. "how many leads
   converted this month by source?") — confirm `CrmQueryBuilderService` produces the same result as
   the equivalent hand-written Eloquent query, and that a request from user A never returns user B's
   company data even if the LLM is prompted to try.
4. Confirm a chart/table `attachments` payload in the response only ever contains numbers traceable to
   an actual `query_crm_data` tool result — not text the model invented.
5. Run through the OpenAI provider path (if implemented in this pass) to confirm the interface
   abstraction actually holds — same conversation, same tool contract, different vendor.
6. `php artisan test --filter=AiStudio` (Pest) — add feature tests: unauthenticated rejected, a
   crafted tool-call attempting to read another company's data is blocked by the allow-list/scoping,
   encrypted key round-trips correctly.

## Documentation updates (same turn, per CLAUDE.md doc-maintenance table)

- **New `docs/ai-studio.md`**: module overview, provider abstraction, the query-builder allow-list
  (which entities/columns/aggregates are exposed — this is the security contract, document it
  precisely), endpoint list, chat message/attachment JSON shapes.
- **`docs/architecture.md`**: add AI Studio as a new module under "Module Structure"/"Services"; note
  under "Multi-tenancy" that this module is the first to explicitly document manual company-scoping
  requirements at the service layer (since it's now a named security concern, not just convention).
- **`docs/database.md`**: new "### AI Studio" subsection under "Tables & Relationships" for the three
  new tables, plus a note in migration conventions about the `encrypted` cast being new practice here.
- **`docs/api.md`**: add the new `/ai-studio/*` routes.

## Scope notes / what's deliberately deferred

- Only Anthropic + OpenAI provider implementations in this pass (interface supports adding more later
  without touching the chat service or controllers).
- No streaming/websocket delivery — synchronous only, per the confirmed decision.
- No admin-level cross-company reporting — scoping stays at the same company/user level as the rest of
  the CRM's existing endpoints.