# ARCHITECTURE.md — Agent Engine

How the system is actually built, as of **2026-09-08**, verified against the `dev` branch and the live Supabase project.

This describes what exists. For what is planned but absent, see `PROJECT.md` §5. `README.md` describes an earlier plan and is not accurate.

---

## 1. System overview

Three deployable units and one managed backend.

```mermaid
flowchart TB

  subgraph Customer["Customer website"]
    Visitor["Visitor browser"]
    Widget["rag-agent-widget.js<br/>vanilla JS, served from apps/web/public"]
    Visitor --> Widget
  end

  subgraph Web["apps/web — Next.js 16 on DigitalOcean App Platform"]
    Dashboard["Dashboard<br/>(auth) routes: org, project, 14 tabs"]
    PublicAPI["Public API<br/>/api/public/rag/*<br/>auth: project API key + Origin check"]
    InternalAPI["Internal API<br/>/api/organizations, /projects, /storage,<br/>/database, /api-keys, /analytics, …<br/>auth: Supabase session cookie"]
    Static["Static hosting<br/>/scripts/rag-agent-widget.js"]
    Proxy["src/proxy.ts<br/>session refresh + route guard"]
  end

  subgraph Agent["apps/agents/rag-agent — FastAPI"]
    API["POST /v1/chat<br/>POST /v1/chat/stream<br/>GET /health"]
    Orch["orchestrator.py<br/>router → RAG / DB → answer"]
    API --> Orch
  end

  subgraph Worker["apps/workers/async/embedding-pipeline-worker"]
    Poll["polls document_processing_jobs"]
  end

  subgraph Supabase["Supabase — Postgres 17.6, Sydney"]
    Auth["Auth"]
    DB[("Postgres<br/>64 tables, RLS")]
    Vector["pgvector<br/>document_chunks"]
    Store["Storage<br/>documents + DB dumps"]
  end

  subgraph LLM["LLM providers"]
    Gemini["Google Gemini (default)"]
    Others["OpenAI · Anthropic · OpenRouter"]
  end

  subgraph CustomerDB["Customer's own databases"]
    MySQL["MySQL / MariaDB<br/>(incl. WordPress)"]
    Mongo["MongoDB"]
  end

  Widget -- "POST /api/public/rag/chat<br/>X-API-Key, SSE" --> PublicAPI
  Widget -- "GET widget-config, history<br/>POST contact" --> PublicAPI
  Visitor -. "script src" .-> Static

  Dashboard --> Proxy --> InternalAPI
  InternalAPI --> Auth
  InternalAPI --> DB
  InternalAPI --> Store

  PublicAPI -- "verify key, Origin,<br/>domain, quota" --> DB
  PublicAPI -- "X-RAG-Agent-Secret" --> API

  Orch --> Vector
  Orch --> DB
  Orch --> Gemini
  Orch --> Others
  Orch --> MySQL
  Orch --> Mongo

  Poll --> DB
  Poll --> Store
  Poll --> Gemini
  Poll --> Vector
```

---

## 2. Components

### 2.1 `apps/web` — Next.js 16, React 19, Node 20 LTS

One deployment carrying four responsibilities: dashboard, internal API, public API, and static host for the widget. Everything else in the system is reached through it.

```
src/
├─ app/
│  ├─ (auth)/            dashboard, org/[organizationId], project/[projectId] (14 tabs)
│  ├─ auth/              sign-in, sign-up
│  └─ api/               49 route handlers
│     ├─ public/rag/     chat · history · contact · widget-config     ← customer-facing
│     └─ …               organizations · projects · storage · database ·
│                        api-keys · analytics · notifications · search
├─ components/<name>/    34 files — views, thin
├─ lib/<name>/           146 files — all business logic
├─ proxy.ts              Supabase SSR session refresh, guards /dashboard /org/ /project/
└─ public/scripts/rag-agent-widget.js
```

The `components` / `lib` / `api` triple share feature names by rule (`CLAUDE.md` §2). Views are deliberately thin: `project-database-view.tsx` renders, `project-database-page-logic.ts` decides.

### 2.2 `apps/agents/rag-agent` — FastAPI, Python 3.12

Four endpoints — `GET /health`, `POST /v1/chat`, `POST /v1/chat/stream`, `POST /v1/llm-config/validate`. Never reachable from a browser; the web app calls it with `X-RAG-Agent-Secret`.

```
src/
├─ api/app.py            endpoints, SSE framing, secret verification
├─ agent/
│  ├─ orchestrator.py    2,467 lines — the whole turn
│  ├─ sql_langchain.py   primary text-to-SQL
│  ├─ sql_llamaindex.py  secondary text-to-SQL
│  ├─ sql_generator.py   custom fallback generator
│  ├─ sql_verifier.py    SELECT-only + schema validation
│  ├─ mongo_generator.py · mongo_verifier.py · mongo_runner.py
│  ├─ mysql_runner.py · sqlite_runner.py
│  ├─ live_query_templates.py   template selection and ranking
│  └─ schema_catalog.py         table shortlisting
├─ llm/                  base · factory · project_config · validate_config · param_compat ·
│                        google · openai · anthropic · openrouter
├─ embeddings/           factory · gemini_embed · openai_embed
├─ retrieval/            chunk_retrieval.py → match_document_chunks RPC
└─ db/supabase_client.py
```

**There is no platform LLM key.** `llm/project_config.py` loads each project agent's own LLM provider / model / key and embedding provider / model / key (`project_agents` + `project_agent_llm_secrets`); every LLM call of the turn — router, answer, suggestions, template pick, parameter extraction, SQL / Mongo generation and verification, LangChain / LlamaIndex — and the query embedding use them. Without them the turn raises `LlmNotConfiguredError` and the API answers `error_code: "LLM_NOT_CONFIGURED"` without calling any provider. `/v1/llm-config/validate` test-calls a configuration with the same clients before the web app saves it.

**`orchestrator.py` is the architectural centre of gravity** and the main piece of debt. In one module it holds routing, retrieval, live-DB context loading, template selection, parameter extraction and coercion, SQL and Mongo generation, verification, execution, card building, contact extraction, and follow-up suggestions.

### 2.3 `apps/workers/async/embedding-pipeline-worker` — Python 3.12

A polling loop, not a queue consumer. Reads `document_processing_jobs`, downloads from Storage, dispatches by extension (`pdf` / `docx` / `rtf` / `text`), chunks, embeds with **the document's project agent's own embedding provider, model and key** (Google or OpenAI, 1536 dimensions; no platform key), writes `document_chunks`, records the model on `documents.embedding_model`, and logs `embedding_usage_events` without cost.

The Upstash Redis queue in `README.md` does not exist. Postgres is the queue.

### 2.4 The widget

A single 2,424-line vanilla-JS file, no framework, no build step, served raw from `public/`. It owns: launcher rendering (Lucide icon / image / GIF / MP4, sized per device), the chat panel, SSE consumption with JSON fallback, carousel cards, the contact form, suggestion chips, and `localStorage` identity (`visitor_id`, `conversation_id`).

Because it is unhashed and unversioned, a browser can cache an old copy against a new API. Fixing this is a tracked thread; the rule it must satisfy is `CLAUDE.md` §8.

---

## 3. Request flows

### 3.1 A visitor chat turn

```
Widget
  └─ POST /api/public/rag/chat   { projectAgentId, message, visitorId, sessionId,
                                    conversationId?, pageUrl?, stream: true }
                                  header: X-API-Key: aepk_…

apps/web  (lib/public-rag/)
  ├─ verify-public-rag-request.ts   hash the key → project_api_keys, check active + expiry
  ├─ parse-origin-host.ts           Origin host must equal projects.domain
  ├─ verify-public-rag-access.ts    is_domain_verified, agent connected and active
  │   ∥ chat-rate-limit.ts          consume_chat_rate_limit(): N per second/minute per visitor,
  │                                 5×N per IP → 429 RATE_LIMITED (preview exempt)
  ├─ own keys saved?                no → LLM_NOT_CONFIGURED, nothing written, no LLM call
  ├─ execution-quota.ts             plan limits
  ├─ load conversation + history    conversations, messages
  └─ POST {RAG_AGENT_BASE_URL}/v1/chat/stream   header: X-RAG-Agent-Secret

rag-agent  (orchestrator.run_rag_agent_turn)
  ├─ load the project's own LLM client + query embedder   (project_config, cached 60s)
  ├─ router LLM  →  mode ∈ { rag, db, both }
  ├─ rag branch   embed query → match_document_chunks → chunks
  ├─ db branch    load live/uploaded DB context (cached)
  │                 query_mode = template_only
  │                 → rank templates → extract + coerce params against parameter_schema
  │                 → bind safely → verify SELECT-only → execute (MySQL / Mongo / SQLite)
  ├─ merge context → primary generation call → reply, cards, citations
  └─ phase: finalizing → follow-up suggestions

apps/web
  ├─ persist messages, update conversation summary
  ├─ record-agent-execution.ts → agent_executions (tokens, model name; no cost — customer's key)
  └─ relay SSE to widget:  start → delta* → phase* → done
```

The SSE event contract is versioned and documented in `docs/architecture/rag-agent-sse-protocol.md`. The latency budget for the blocking path — roughly 2.0–3.3s, one primary generation call — is in `docs/architecture/rag-agent-latency-playbook.md`. Full-detail diagrams: `docs/architecture/rag-agent-full-flow.mmd`, `rag-agent-rag-only-flow.mmd`, `rag-query-template-flow.mmd`.

### 3.2 Document ingestion

```
Dashboard → POST /api/projects/[id]/storage/upload
  → refused unless the agent has its own embedding key
  → Supabase Storage + documents row + document_processing_jobs row
worker  → claim job → load the agent's embedding config → download → extract → chunk
        → embed (Google / OpenAI, customer's key) → document_chunks (1536-dim)
        + documents.embedding_model + embedding_usage_events → mark job done
Dashboard → GET /storage/document-processing/sync   (status polling)
```

### 3.3 Live database connection

```
Dashboard → POST /database/connections            credentials → database_connection_secrets
         → POST /database/connections/[id]/sync-schema
                                                  introspect → database_connection_schemas
         → POST /database/connections/[id]/query-templates
                                                  named queries + parameter_schema + card config
rag-agent → reads the schema snapshot and templates at query time; connects directly to
            the customer's MySQL/MariaDB or MongoDB to execute.
```

---

## 4. Trust boundaries

| Boundary | Credential | Notes |
|----------|-----------|-------|
| Visitor browser → public API | `X-API-Key: aepk_…` | Hashed at rest; also gated on `Origin` == `projects.domain` and `is_domain_verified` |
| Dashboard → internal API | Supabase session cookie | Refreshed in `src/proxy.ts`; RLS enforces tenancy in the database |
| Web app → RAG agent | `X-RAG-Agent-Secret` | Shared secret, server-to-server only, never sent to a browser |
| Any service → Supabase | `SUPABASE_SERVICE_ROLE_KEY` | Server-only. The browser only ever sees `NEXT_PUBLIC_*` |
| RAG agent → customer database | Per-connection credentials | From `database_connection_secrets`; **SELECT-only, verified before execution** |
| RAG agent / worker → LLM providers | The customer's own API keys | In `project_agent_llm_secrets` (RLS on, no policies — service role only). Written by the web server only after a live test call; the browser sees only the last four characters. No platform key exists |

Three independent controls keep generated queries safe: the mode restriction (`template_only`), the verifiers (`sql_verifier.py`, `mongo_verifier.py`), and parameter binding rather than string interpolation. Each needs a regression test — `CLAUDE.md` §9 rule 4.

---

## 5. Data model

64 tables in `public`. **Verified 2026-09-08: the live schema matches `supabase/full-schema.sql` exactly — no drift in either direction, and all 34 migrations are applied.**

| Group | Tables |
|-------|--------|
| Reference | `languages` `countries` `states` `urls` `databases` `database_types` `document_allowed_extensions` `document_db_file_purposes` `document_db_file_allowed_extensions` `database_export_layouts` `worker_models` `model_tiers` `*_statuses` `*_types` |
| Identity | `users` `settings` `cookie_consents` |
| Tenancy | `organizations` `organization_members` `projects` `project_api_keys` |
| Catalog | `agents` `project_agents` `project_agent_widget_configs` `project_agent_llm_secrets` `models` `plans` `plan_prices` |
| Billing *(schema only)* | `subscriptions` `invoices` `payments` `payment_methods` |
| Usage | `agent_executions` ⚑ `usage_daily_rollups` `embedding_usage_events` |
| RAG runtime | `visitor_contacts` `conversations` `messages` ⚑ |
| Knowledge — documents | `documents` `document_chunks` `document_processing_jobs` |
| Knowledge — uploaded DB | `document_database_schemas` `document_database_table_data` |
| Knowledge — live DB | `database_connections` `database_connection_secrets` `database_connection_schemas` `database_connection_query_templates` |
| Platform | `notifications` `audit_logs` ⚑ `rate_limit_counters` (unlogged) |

⚑ = monthly range-partitioned on `created_at`.

Functions in the live database: `get_default_language_id`, `get_default_plan_id`, `is_org_member`, `has_org_role`, `is_valid_contact_fields`, `match_document_chunks`, `rollup_daily_usage`, `create_next_month_partitions`, `archive_old_partitions`, `consume_chat_rate_limit`.

Design intent — soft-delete strategy, UUID v7, partial indexes, connection pooling, read replicas, a 100–200M MAU scale target — is in `docs/db-schema/db-schema-i3.md`. Canonical DDL, indexes, and RLS policies are in `docs/db-schema/sql-queries.md`.

### 5.1 Partitioning and retention

`messages`, `agent_executions`, and `audit_logs` are declared `PARTITION BY RANGE (created_at)` with one child table per month. Postgres routes each insert to the child covering its timestamp.

Until 2026-09-09 children existed only for 2026-03…06, so every chat insert failed (`no partition of relation "messages" found for row`). Migration `0037` added children through 2027-12 plus a `DEFAULT` partition, and wrote the two rotation functions the design specifies:

| pg_cron job | Schedule (UTC) | Does |
|-------------|----------------|------|
| `create-monthly-partitions` | 03:00 on the 25th | `create_next_month_partitions(6)` — keeps six months ahead |
| `archive-executions` | 02:00 on the 1st | `archive_old_partitions('agent_executions', 90)` — **drops** months older than 90 days |
| `archive-messages` | 03:00 on the 1st | `archive_old_partitions('messages', 365)` — **drops** months older than 365 days |
| `purge-rate-limit-counters` | every 10 min | clears expired `rate_limit_counters` windows (`0038`) |

The two archive jobs predate `0037` and failed monthly until the function existed. Their first successful run, 2026-10-01, dropped `agent_executions_2026_03…06` per the 90-day policy in `docs/db-schema/db-schema-i3.md`. "Archive" in the spec means drop; nothing is exported first.

---

## 6. Migration history

`docs/db-schema/migrations/` is the migration history. Supabase CLI migrations are not used; files are applied by hand and the result is dumped to `supabase/full-schema.sql`.

**New migrations are numbered** `NNNN-YYYY-MM-DD-description.sql`, starting at **`0035`** (`CLAUDE.md` §7). The 34 legacy files keep their names; the table below is their authoritative apply order, recovered from git history.

| # | Migration | Landed |
|---|-----------|--------|
| 1 | `2026-03-24-document-processing-queue` | c75f9bf |
| 2 | `2026-03-26-document-chunks-delete-policy` | c75f9bf |
| 3 | `2026-03-26-embedding-usage-events` | c75f9bf |
| 4 | `2026-03-26-embedding-usage-events-worker-model-fk` | c75f9bf |
| 5 | `2026-03-26-notifications-insert-policy` | c75f9bf |
| 6 | `2026-03-26-worker-models` | c75f9bf |
| 7 | `2026-03-27-database-files-schema-and-policies` | c75f9bf |
| 8 | `2026-03-29-database-export-layouts-and-document-extensions` | c75f9bf |
| 9 | `2026-03-29-match-document-chunks-rpc` | c75f9bf |
| 10 | `2026-03-30-drop-visitor-source-conversation-id` | c75f9bf |
| 11 | `2026-03-30-gemini-2-flash-model-seed` | c75f9bf |
| 12 | `2026-03-30-phpmyadmin-layout-seed-and-table-data-rls` | c75f9bf |
| 13 | `2026-03-30-project-agents-add-greeting` | c75f9bf |
| 14 | `2026-03-30-visitor-contacts-drop-company-add-metadata` | c75f9bf |
| 15 | `2026-03-31-database-connections-and-schemas` | c75f9bf |
| 16 | `2026-03-31-urls-and-db-connection-secrets-timestamps` | c75f9bf |
| 17 | `2026-04-01-agent-executions-add-cost-columns` | c75f9bf |
| 18 | `2026-04-01-gemini-3-pro-model-seed` | c75f9bf |
| 19 | `2026-04-01-plans-add-default-model-id` | c75f9bf |
| 20 | `2026-04-01-project-agents-add-custom-agent-name` | c75f9bf |
| 21 | `2026-04-07-wordpress-relational-database-lookups` | 94602e9 |
| 22 | `2026-04-08-mongodb-snapshot-kind-and-entity-count` | 090f491 |
| 23 | `2026-04-10-database-connection-query-templates-and-mode` | aef569b |
| 24 | `2026-04-11-fix-query-template-sql-check-postgres-regex` | aef569b |
| 25 | `2026-04-12-database-connection-query-templates-non-relational` | aef569b |
| 26 | `2026-04-14-query-template-card-config` | 360b859 |
| 27 | `2026-04-15-project-agent-widget-launcher-config` | 360b859 |
| 28 | `2026-04-15-project-agent-widget-launcher-icon-options-expand` | 360b859 |
| 29 | `2026-04-15-project-agent-widget-required-contact-fields` | dcc9885 |
| 30 | `2026-04-16-widget-contact-fields-location` | dcc9885 |
| 31 | `2026-04-20-project-agent-widget-theme-color` | cfbfd41 |
| 32 | `2026-06-12-widget-launcher-avatar-gif-mp4` | 4dc1f13 |
| 33 | `2026-06-29-project-agent-widget-avatar-size` | 0c57e9e |
| 34 | `2026-06-29-project-agent-widget-avatar-size-per-device` | a526940 |

Note #33 and #34: identical dates, and lexical sort reverses them. That is the reason for the numbering rule.

---

## 7. Deployment topology

| Unit | Target | Status |
|------|--------|--------|
| `apps/web` | DigitalOcean App Platform, `main`, Node 20 LTS | Runbook: `docs/web-production-deployment-handoff.md`. Also containerised: `apps/web/Dockerfile` |
| Widget | Served by `apps/web` from `public/scripts/` | No separate deployment, by design |
| `rag-agent` | Containerised; hosting is DevOps' call | `apps/agents/rag-agent/Dockerfile` |
| `embedding-pipeline-worker` | Containerised; hosting is DevOps' call | `apps/workers/async/embedding-pipeline-worker/Dockerfile` |
| Database, Auth, Storage, Vector | Supabase `xbkcqtqrgrnbepunsayc` (Sydney) | Live |

There is **no CI**. No `.github/`, no pipeline, no automated build or test on push.

---

## 8. Architectural debt

Ranked by risk. Each has a thread in `THREADS.md`.

1. **Retention drops data with no export** — `archive_old_partitions` deletes old `agent_executions` / `messages` months outright (§5.1).
2. **The widget is unversioned** — a cached old copy can meet a new API on a customer's site.
3. **No tests, no CI** — every safety control in §4 is unguarded against regression.
4. **`orchestrator.py` at 2,467 lines** — routing, retrieval, generation, verification, execution, and presentation in one module.
5. **The `web` image is ~3.0 GB**, dominated by the Playwright chromium layer that only the live-website preview needs.
6. **Billing schema with no billing code** — six tables and a 670-line spec that nothing writes to.
7. **`README.md` and `COSTING.md` describe the abandoned Vercel/Render/Twilio plan** — actively misleading.
