AI Chat

• Install the Vercel AI SDK, build a query function, and create an AI chatbot that can answer questions about your data and manage records

Prerequisite

Complete the Get Started guide first.

An internal AI assistant that understands your database and can answer natural language questions by running SQL queries, and create/update/delete records through typed tool calls.

How it works: The user types a question like "How many records do we have?" The server sends that question to an AI model (via OpenRouter). The AI generates a SQL query and hands it back to the server. The server validates the query is read-only, executes it against Supabase, and sends the results back to the AI. The AI then summarizes the data in plain English and streams the response to the user. For write operations, the AI uses dedicated tools with typed inputs — no raw SQL. The AI never touches the database directly — the server is the gatekeeper.

Permissions

Add this permission to shared/permissions.ts if it doesn't already exist:

"ai.chat": ["owner", "admin"],

Prompt 1 — Backend

Install the Vercel AI SDK and build the chat backend.

1. Install these npm packages: `ai`, `@openrouter/ai-sdk-provider`, `@ai-sdk/vue`

   - `ai` is the Vercel AI SDK core — provides streamText, tool definitions,
     and message conversion
   - `@openrouter/ai-sdk-provider` is the OpenRouter provider — gives access
     to Claude, GPT, Gemini, and many other models through a single API key.
     Reads OPENROUTER_API_KEY from the environment automatically.
   - `@ai-sdk/vue` provides the Chat class that Nuxt UI's chat components
     are designed to work with

2. Add `OPENROUTER_API_KEY=` to `.env.example`. Get an API key from
   openrouter.ai and add it to your `.env`.

3. Create a Postgres function via Supabase MCP that safely executes
   read-only SQL queries:

   ```sql
   CREATE OR REPLACE FUNCTION execute_readonly_query(query text)
   RETURNS jsonb
   LANGUAGE plpgsql
   SECURITY DEFINER
   SET statement_timeout = '5s'
   AS $$
   DECLARE
     result jsonb;
   BEGIN
     SET TRANSACTION READ ONLY;
     EXECUTE format(
       'SELECT coalesce(jsonb_agg(row_to_json(t)), ''[]''::jsonb) FROM (%s) t',
       query
     ) INTO result;
     RETURN result;
   END;
   $$;

   REVOKE ALL ON FUNCTION execute_readonly_query(text) FROM PUBLIC;
   REVOKE ALL ON FUNCTION execute_readonly_query(text) FROM anon;
   REVOKE ALL ON FUNCTION execute_readonly_query(text) FROM authenticated;
   ```

   This function is the security backbone:
   - `SET TRANSACTION READ ONLY` — Postgres itself rejects any mutation
   - `statement_timeout = '5s'` — prevents runaway queries
   - `SECURITY DEFINER` — runs as the function owner
   - `REVOKE` — only the service role can call it

4. Create `server/utils/getDbSchema.ts` — a utility that fetches the
   database schema at runtime. It should:
   - Call `client.rpc('execute_readonly_query', { query })` where the
     query selects from `information_schema.columns`: table_name,
     column_name, data_type, is_nullable for all tables in the `public`
     schema
   - Exclude internal/system tables: `rate_limits`, `schema_migrations`,
     `api_keys` (we don't want the AI querying API key hashes)
   - Format the result into a readable string grouped by table, like:
     ```
     Table: clients
       - id (uuid, not null)
       - team_id (uuid, not null)
       - name (text, not null)
       ...
     ```
   - Return the formatted string

5. Create `server/api/chat.post.ts` — the streaming chat endpoint:
   - Uses `authUser(event, "ai.chat")` for authentication and permission
   - Reads `{ messages }` from the request body
   - Fetches the schema string via `getDbSchema(client)` using the
     service role client from authUser
   - Gets the team_id and userId from the auth context
   - Builds a system prompt (see below)
   - Uses `streamText()` from `ai` with:
     - `model: openrouter("anthropic/claude-sonnet-4-6")` using
       `createOpenRouter` from `@openrouter/ai-sdk-provider`
     - `system: systemPrompt`
     - `messages: await convertToModelMessages(messages)`
     - `stopWhen: stepCountIs(10)` from `ai` (allows multiple tool calls
       per response — use `stepCountIs`, not `maxSteps`)
     - A `query_database` tool with:
       - `description`: "Execute a read-only SQL query against the database.
         Only SELECT statements are allowed."
       - `inputSchema`: a Zod schema with a single `sql` string field
       - `execute`: an async function that:
         1. Trims the SQL and converts to uppercase for checking
         2. Rejects if it does not start with SELECT or WITH (for CTEs)
         3. Rejects if it contains DROP, ALTER, TRUNCATE, GRANT, or REVOKE
            anywhere in the query
         4. Calls `client.rpc('execute_readonly_query', { query: sql })`
         5. If the result has more than 50 rows, truncate and append
            a note: "(showing first 50 of {n} rows)"
         6. Returns the result as a JSON string
     - Write tools will be added in Prompt 3 — for now just include
       `query_database`
   - Returns `streamText(...).toUIMessageStreamResponse()`

   System prompt to use:
   ```
   You are a data analyst and operations assistant.
   You help team members understand their data and manage records.

   DATABASE SCHEMA:
   {schema}

   RULES:
   - For reading data, use the query_database tool. Only SELECT queries
     are allowed there.
   - ALWAYS filter by team_id = '{teamId}' when querying tables that have
     a team_id column. This is critical for data isolation.
   - The current team's ID is: {teamId}
   - Keep queries simple and efficient. Always use LIMIT (default to 25).
   - After receiving query results, summarize them in clear, natural language.
   - Format numbers nicely (currency with $ and commas, dates in readable format).
   - Do not expose raw UUIDs to the user unless they specifically ask.
     Show names and emails instead.
   - For "this month" or "this week" questions, use the current date in
     your SQL (CURRENT_DATE, date_trunc, etc).

   WRITE OPERATIONS:
   - You can create, update, and delete records using the dedicated tools.
   - When creating a record that references another table, first look up
     the referenced ID by querying. Never guess an ID.
   - When updating or deleting, first query the record to confirm it exists
     and show the user what you found before making changes.
   - Always confirm what you did after a write operation succeeds.
   ```

Regenerate TypeScript types via Supabase MCP to include the new function.

Prompt 2 — Frontend

Build the chat UI using Nuxt UI's built-in chat components.

1. Create `app/components/chat/AiChat.vue` — the main chat component:

   Template structure:
   - Wrap everything in a single root `<div>` with
     `class="flex flex-col h-full"` (a single root is required so Vue
     can inherit attributes — do NOT use a fragment with two siblings).
   - `UChatMessages` at the top (flex-1 to fill space) with `:messages`,
     `:status`, and `should-auto-scroll` props. Set the assistant prop
     to show an icon: `{ icon: 'i-lucide-bot' }`.
   - Use the `#content` slot to render message parts. Iterate over
     `message.parts` and render:
     - `UChatTool` for tool invocations (`isToolUIPart(part)`) — show a
       label based on the tool name using `getToolName(part)`.
       Bind `:streaming="part.state !== 'output-available'"`. In the
       default slot, show the result from `part.output` when available
       (formatted as JSON in a `<pre>` tag).
     - Plain text for text parts (`isTextUIPart(part)`) — wrap in a
       `<p>` with `whitespace-pre-wrap`. Do NOT use MDC — the
       `@nuxtjs/mdc` module is not installed.
   - `UChatPrompt` at the bottom with `v-model` bound to the input ref,
     `@submit` calling the submit function, and `placeholder="Ask about
     your data..."`. Nest a `UChatPromptSubmit` inside with `:status`
     bound to the chat status and `@stop` calling `chat.stop()`.

   Script:
   - Import `Chat` from `@ai-sdk/vue`
   - Import `DefaultChatTransport`, `isTextUIPart`, `isToolUIPart`,
     `getToolName` from `ai`
   - Create a `ref('')` for the input
   - Instantiate `const chat = new Chat()` with `transport` set to
     `new DefaultChatTransport({ api: '/api/chat' })` (do NOT pass
     `api` directly to Chat — it does not accept it). Add an `onError`
     callback that shows an error toast using `useToast()`.
   - Create an `onSubmit` function that calls
     `chat.sendMessage({ text: input.value })` and clears the input
   - Add a TOOL_META record mapping tool names to labels and icons.
     Map each tool that exists (e.g. `query_database` →
     "Querying database" / `i-lucide-database`). Create appropriate
     labels for any write tools based on the tool names.

2. Create `app/pages/chat/index.vue` — thin page wrapper as a **top-level
   route** (not nested under `/dashboard` — chat is its own feature):

   - `UDashboardPanel` with id "chat"
   - `UDashboardNavbar` in the header with title "AI Assistant" and
     `UDashboardSidebarCollapse` on the left
   - The body wraps `ChatAiChat` inside
     `CanAccess permission="ai.chat"` with a `#fallback` slot showing
     a message like "You don't have access to the AI assistant."
   - Do NOT define `definePageMeta({ layout: "dashboard" })` — the
     default layout already handles this.

3. Add "AI Assistant" link to the sidebar navigation in
   `app/components/layout/sidebar/Links.vue`. Use the icon
   `i-solar-chat-square-bold-duotone`. Gate visibility with
   `can("ai.chat")` so only owners and admins see it. Place it after
   the last domain link and before "Settings" in the navigation order.

Prompt 3 — Write Tools

Add write tools to the AI chat so it can create, update, and delete records
in your domain tables — not just query data.

The tools are getting large, so extract them out of `chat.post.ts` into
separate files grouped by resource:

Create `server/utils/chat-tools/` with these files:

- `types.ts` — a `ChatToolContext` interface with `client`
  (SupabaseClient), `teamId` (string), and `userId` (string).

- `query.ts` — exports `queryTools(ctx: ChatToolContext)` returning an
  object with the existing `query_database` tool (move it here from
  chat.post.ts). Also move the BLOCKED_KEYWORDS list here.

- For each domain-specific table in the database (exclude system tables
  like teams, profiles, members, invitations, api_keys, rate_limits, and
  schema_migrations), create a tool file that exports tools for that
  resource:
  - `create_{resource}` — Zod input matching the table's required and
    optional columns. Sets team_id and created_by from the context.
  - `update_{resource}` — Zod input with the record ID required, all
    other columns optional. Verifies the record exists in the team
    before updating.
  - `delete_{resource}` — Zod input with the record ID required. Verifies
    the record exists in the team before deleting.

Every write tool must:
- Scope to the current team_id
- Verify the record exists before update/delete
- Use the authenticated Supabase client (not raw SQL)
- Return `{ success: true }` or `{ error: message }` as JSON

Then simplify `chat.post.ts` to import and spread all tool groups:

```ts
const ctx = { client, teamId: teamMember.teamId, userId: teamMember.userId };
// ...
tools: {
  ...queryTools(ctx),
  ...resourceTools(ctx),  // spread each resource's tools
},
```

Update the TOOL_META in `AiChat.vue` to include labels and icons for all
the new write tools.

Using a different provider

We use OpenRouter because it gives you access to many models through one API key. But the Vercel AI SDK is provider-agnostic — you can swap to a direct provider by changing one package and one line of code:

• Anthropic directly: install @ai-sdk/anthropic, use anthropic("claude-sonnet-4-6"), set ANTHROPIC_API_KEY
• OpenAI directly: install @ai-sdk/openai, use openai("gpt-4o"), set OPENAI_API_KEY
• Google directly: install @ai-sdk/google, use google("gemini-2.5-pro"), set GOOGLE_GENERATIVE_AI_API_KEY

Everything else — streamText, tool calling, the Chat class, the UI components — stays exactly the same.

Testing the AI chat

Once built, try these questions to verify it works:

• "How many records do we have?"
• "Show me the most recent entries"
• "Summarize the data in [table name]"
• "Create a new [record] with [details]"

You should see the tool invocation appear in the chat (expandable to see the SQL or tool input), followed by a natural language summary of the results.