# Design — Email Drafting Assistant (Phase 1 dev slice)

**Date:** 2026-06-29
**Status:** Approved (design), pending implementation
**Branch:** `feature/email-drafting-assistant`

## 1. Goal & scope

Deliver the Phase 1 deliverable in its simplest honest form: an Open WebUI assistant that **reads an existing client database (read-only)** and **helps staff draft client emails** grounded in that data.

**In scope (this iteration):**
- A **fake Postgres database** for dev, **shaped like Zammad** so the future swap to the real Zammad DB is a connection/query change, not a redesign.
- **Read-only** access, enforced at the database layer.
- **One** assistant ("Email Drafting Assistant") that drafts contextual replies from client history.
- Engine: `claude-sonnet-4-6` for now (swap to local Ollama later = change the model's base model).
- **Language: English everywhere** (seed data, system prompt, assistant name, schema comments). Single language for now.

**Explicitly deferred (NOT this iteration):**
- The 7-department RBAC matrix (groups, per-department Models/Folders/Tools, `full/read/create/—` granularity).
- Any write/CRUD operations.
- The real Zammad integration.
- Ollama as the engine.

These are recorded as future work in §8 so the slice stays minimal (YAGNI).

## 2. Architecture overview

```
User ──▶ "Email Drafting Assistant" (Model, claude-sonnet-4-6)
            │  system prompt: English, agency tone, grounding/no-fabrication
            │  attached Tool ▼
         crm_reader.py  (Open WebUI Tool, READ-ONLY)
            │  connects via DSN in Tool Valves, as role owui_reader
            ▼
      Postgres  client_db  schema `crm`   (container postgres_vector)
         organizations · users · tickets · ticket_articles
         role owui_reader = SELECT only
```

- The assistant calls the Tool to fetch a customer's context + email history, then drafts a reply.
- Authorization/visibility (RBAC) is out of scope here — the assistant is simply available to the signed-in user.

## 3. Data model (fake DB, Zammad-shaped)

Schema `crm` inside the existing `client_db` (container `postgres_vector`). Tables mirror Zammad core entities used for email drafting:

```sql
crm.organizations  (id PK, name, domain, note, created_at)
crm.users          (id PK, organization_id FK?, firstname, lastname,
                    email UNIQUE, role)            -- role ∈ {agent, customer}
crm.tickets        (id PK, number UNIQUE, title, customer_id FK→users,
                    organization_id FK?, state, priority, group_name,
                    owner_id FK→users?, created_at, updated_at)
                    -- state ∈ {new, open, pending, closed}
                    -- priority ∈ {low, normal, high}
crm.ticket_articles(id PK, ticket_id FK→tickets, sender, recipient,
                    subject, body, type, internal BOOL, created_at)
                    -- type ∈ {email, note, phone}
```

- Naming/shapes deliberately echo Zammad (`tickets`, `ticket_articles` = messages, `state`, `priority`, `group`, customer as a `user` with `role=customer`). The swap later maps these to Zammad's real tables.
- **Migrations are idempotent** (`CREATE SCHEMA/TABLE IF NOT EXISTS`) and versioned in the repo.
- **Seed** (`02-seed.sql`): a handful of organizations, customer users, and tickets each with a realistic multi-message English email thread, so "read history → draft a reply" is demonstrable.

## 4. Read-only enforcement (database layer)

Not a convention — physically enforced:

```sql
CREATE ROLE owui_reader LOGIN PASSWORD '<from .env>';
GRANT USAGE ON SCHEMA crm TO owui_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA crm TO owui_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA crm GRANT SELECT ON TABLES TO owui_reader;
-- no INSERT/UPDATE/DELETE granted
```

The Tool connects as `owui_reader`. Even a buggy/compromised tool cannot write. This is the literal meaning of "Open WebUI only reads".

## 5. The Tool — `crm_reader.py`

One Open WebUI Tool, read-only, **no free-form SQL from the LLM**. Parameterized queries only. Functions exposed to the model:

- `find_customer(query: str)` → search `crm.users` (role=customer) + organizations by name/email; returns candidates (id, name, email, org).
- `get_customer_context(identifier: str)` → resolve a customer by email or id; return organization + recent tickets (id, number, title, state, last activity).
- `get_ticket_thread(ticket_id: int)` → ordered `ticket_articles` for a ticket (sender, recipient, subject, body, created_at), excluding `internal=true` notes unless explicitly requested.

**Connection:** DSN (host `postgres_vector`/`postgres`, db `client_db`, user `owui_reader`, password) stored in the Tool's **Valves** (admin-configured), never hardcoded. Network: both containers share `mac-mini_default`.

**Defense-in-depth / future-proofing:** a thin internal helper isolates DB access so swapping the fake DB for real Zammad later touches one module. (No group/RBAC checks yet — deferred.)

## 6. The assistant — "Email Drafting Assistant"

- **Base model:** `claude-sonnet-4-6` (swap to Ollama later by changing the base model only).
- **Tool attached:** `crm_reader`.
- **System prompt** (`prompts/email-drafting-assistant.md`, English):
  - Role: drafts and corrects client emails for the agency.
  - Tone: professional by default, adjustable (warm/formal) on request; can correct/rephrase/translate.
  - **Grounding rule:** must base any client-specific fact (names, history, status) on Tool results. If the Tool returns nothing, say so — never invent client facts.
  - Output: a ready-to-send draft; ask for missing specifics rather than guessing.
- **Access:** available to the signed-in user (no group gating this iteration).

## 7. Deliverables & wiring

Repo layout under `test/dev/mac-mini/`:

```
db/01-schema.sql      -- schema crm + role owui_reader (SELECT only), idempotent
db/02-seed.sql        -- English fake data: orgs, customers, tickets, email threads
tools/crm_reader.py   -- Open WebUI read-only Tool
prompts/email-drafting-assistant.md  -- system prompt (English)
README.md             -- wiring & verification steps
```

- SQL applied now via `psql` against `postgres_vector`; the same files are also mounted into `/docker-entrypoint-initdb.d` for fresh deployments.
- Open WebUI wiring (admin, partly browser-driven): import the Tool, set its Valves DSN, create the Model with system prompt + tool, select base model `claude-sonnet-4-6`.

## 8. Error handling

- DB connection failure → Tool returns a clear, non-leaking message (no DSN/credentials in output); assistant tells the user it can't reach the data right now.
- No match → "no customer/ticket found" (assistant must not fabricate).
- Parameterized queries only; identifiers validated; LLM cannot inject SQL.
- Read-only role makes writes impossible regardless of tool logic.

## 9. Testing strategy (TDD on pure functions)

- **Schema/seed sanity:** row counts, FK integrity, at least one ticket with a multi-message thread.
- **Unit:** each Tool function against the `owui_reader` role — assert SELECT works and that an attempted write is rejected by the DB.
- **Manual/integration:**
  - "Draft a reply to <known customer> about <their ticket>" → assistant pulls the real thread and drafts a grounded reply.
  - "<unknown customer>" → assistant reports it found nothing, drafts nothing client-specific.
  - Tone switch (professional ↔ warm) and correction/translation requests behave.

## 10. Future work (out of scope, recorded)

1. **Swap fake DB → real Zammad** (DSN + map `crm.*` to Zammad tables; keep the Tool's DB module as the single change point).
2. **RBAC**: 7 departments (Quote, Sales, CAD, Logistics, Accounting, After-sales, Triage) as groups + per-department Models/Folders/Tools; Triage = Zammad. Native ACL for `full/—`; Tool-level checks for `read/create/full` granularity.
3. **Write operations** where the matrix allows (`create`/`full`).
4. **Ollama engine** for sovereignty (pull a tool-capable model; switch the Model's base model).
5. **pgvector RAG** folders per department (meeting notes, unstructured docs).