Enhancement: AI Chat Assistant (Vibe Coder) for Next Novas Staff

[weilies]

Overview

An AI-powered chat assistant embedded in PulseBox that allows Next Novas staff to interact with the platform in natural language — querying data, managing schemas, and building integrations via chat.

Phase 1 scope: Next Novas staff only. Architecture must be tenant-scoped from day 1 to support v2 expansion to tenant-facing usage.

---

Scope

A — Data Queries (NL2SQL)

• Natural language → SQL → results streamed back in chat
• Example: "Show me total active employees across all tenants"
• Read + write operations, scoped to tenant context

B — Schema Operations

• Create/update/delete collections and fields via chat
• Example: "Create an Employee collection with Name, Email, Department, Status fields"

C — Integration Builder

• AI researches external product docs (crawls developer portal)
• Breaks integration into spec → proposes approach A/B/C → user picks
• Generates migrations, API routes, and config
• Stores credentials encrypted in a new integrations system collection
• Example: "Build a Larksuite SSO integration"

---

Architecture

New System Collections

integrations

id, name, provider, status (active|testing|inactive),
credentials (encrypted JSONB via pgcrypto/Supabase Vault),
config (JSONB), created_by, tenant_id, created_at, updated_at

ai_chat_history

id, session_id, tenant_id, user_id, role (user|assistant),
content (text), metadata (JSONB), created_at

Both collections must have RLS policies: tenant_id = current_tenant_id. Chat history must NEVER leak across tenants.

New API Endpoint

POST /api/integrations/chat

• Streams Claude API response (Sonnet 4.6)
• Accepts: { message, session_id, mode: explore|plan|build|test|deploy }
• Resolves tenant from pb-tenant cookie — all queries scoped to that tenant
• Executes NL2SQL via Supabase admin client within tenant boundary
• Logs every turn to ai_chat_history with tenant_id

New Frontend

• Page: /dashboard/integrations/chat (super_admin only, v1)
• Component: src/components/integration-chat.tsx
• Stack: Vercel ai package (streaming) + react-markdown + shadcn/ui
• Custom UI: code preview pane, approval buttons (Yes/Build/Skip), secret input forms

AI Stack

• Model: Claude Sonnet 4.6 via Anthropic API
• Schema introspection: Fetch once per session, cache in-memory + invalidate on schema change

---

Token Cost-Cutting (MVP)

Strategy	Savings	Notes
Schema caching (in-memory per session)	~40-50%	Invalidate on schema change
Lossy schema (pgvector to find relevant tables)	~30%	Send only relevant tables per query
Anthropic prompt caching (cache_control)	~20-30%	System message cached after first use
Conversation pruning (last 5 turns + summary)	~15-20%	Avoid ballooning context
Combined	~65% reduction	Real-time, no quality loss

Model routing (Haiku for simple NL2SQL, Sonnet for code gen) deferred to v2.

---

Tenant Isolation Requirements (Hard Rules)

• ai_chat_history rows have tenant_id — RLS enforces tenant boundary
• integrations credentials scoped to tenant_id
• /api/integrations/chat resolves tenant from cookie, never from request body
• Schema introspection only fetches collections belonging to current tenant
• NL2SQL execution uses admin client but injects tenant_id filter explicitly
• No cross-tenant data in any Claude API prompt (schema, history, items)

---

Integration Builder Flow (Example: Larksuite SSO)

1. Explore — AI crawls Larksuite developer portal, summarizes OAuth/SSO requirements
2. Plan — proposes A/B/C approaches (email auto-provisioning, OAuth-only, org sync)
3. User picks — approval via chat
4. Build — AI generates migration + API routes (staged, not committed)
5. Test — user provides test credentials, AI validates OAuth flow
6. Deploy — user approves, migrations pushed, code committed, secrets encrypted in DB

---

Implementation Checklist

• integrations system collection + migration + RLS
• ai_chat_history system collection + migration + RLS
• POST /api/integrations/chat streaming endpoint
• Vercel ai package + Claude API integration
• Schema introspection helper (fetch + cache + lossy filter)
• NL2SQL execution with tenant isolation
• Prompt caching (cache_control in system message)
• Conversation pruning (last-N + summarize)
• integration-chat.tsx component (streaming, markdown, code preview, approval buttons)
• /dashboard/integrations/chat page (super_admin only)
• Sidebar nav item under new "Integrations" folder
• Secrets encryption (pgcrypto or Supabase Vault)
• Audit log (who asked, what was built, tenant, timestamp)

---

Out of Scope (v1)

• Tenant-facing chat (v2)
• n8n integration
• Batch API / async processing
• Model routing (Haiku for simple queries)
• Multi-tenant chat billing/metering

---

Notes

• Do NOT use n8n for chat — custom backend gives full control over tenant scoping and audit trail
• Claude Code (IDE) is for building this feature; Claude API (paid) is what runs in production
• All token cost strategies must be implemented at MVP, not deferred