AI Chat

The template ships with a fully wired AI chat — persistent conversations, database queries in natural language, typed write tools, streaming responses, and activity-log attribution. It is enabled when OPENROUTER_API_KEY is set in your .env; without a key, the chat UI is hidden and no API routes run.

You do not need to install it via a prompt. What you do need to know is how to make your own tables readable and writable from the chat.

How it works

Stack — Vercel AI SDK (ai, @ai-sdk/vue) with the OpenRouter provider (@openrouter/ai-sdk-provider). Switch providers by swapping one package and one line.

Storage — chats (one row per conversation) and chat_messages (one row per message, including tool calls and outputs) are team-scoped and user-owned. RLS restricts every row to auth.uid() + is_team_member().

Endpoints — server/api/chats/:

• GET /api/chats — list the user's chats for the current team
• POST /api/chats — create a new chat
• POST /api/chats/[id] — stream a response for a message; the endpoint routes writes through createAuditedClient({ source: 'chat', sourceRef: id }) so every mutation lands in activity_log tagged with the chat ID

Tools — server/utils/chat-tools/ exports:

• queryTools(ctx) — query_database (read-only SQL via the sandboxed chat_reader Postgres role)
• mutationTools(ctx) — typed create_*, update_*, delete_* tools generated from the tables in tablePermissions
• webSearchTools(ctx) — web search tool when enabled

Schema introspection — server/utils/getDbSchema.ts feeds the LLM a compact description of each allowed table: columns and types, CHECK constraints rendered as one of: (...) lists, foreign keys as fk: other_table.id, and COMMENT ON COLUMN text as the free-text hint after the —. Enum violations and constraint errors are enriched with the same constraint map before being returned to the LLM, so the model can self-correct.

Security model

The LLM never touches the database directly. Enforcement is in three places:

1. tablePermissions gate — a table not listed in the map is invisible to the chat tools. Adding a table to the LLM's surface is an explicit, per-table, per-CRUD decision (see below).
2. chat_reader Postgres role — every LLM-generated SELECT runs as chat_reader, a nologin role that only has SELECT on the tables granted to it. Parallel RLS policies scoped TO chat_reader mirror the team boundaries. The LLM cannot see another team's data even if it crafts a query claiming it can.
3. Typed write tools — the LLM cannot execute INSERT / UPDATE / DELETE as raw SQL. Writes go through create_* / update_* / delete_* tools with Zod-validated inputs, scoped to the current team, enforcing the same per-user permission check as the API routes.

Adding a new table to chat

Do this once per table you want the assistant to read or write. Everything is in two files plus one migration.

1. Migration — make the table chat-friendly

Harden the schema so the schema-to-prompt introspection gives the LLM the allowed values and intent of each column. getDbSchema reads CHECK constraints and column comments directly — whatever you put here is what the model sees.

-- CHECK constraints for enum-like columns and format rules.
-- Single-column IN (...) checks are parsed into allowed-value lists in
-- the prompt. Compound CHECKs fall back to the raw definition.
alter table tasks
  add constraint tasks_status_check   check (status in ('open','in_progress','done')),
  add constraint tasks_priority_check check (priority in ('low','normal','high'));

-- COMMENT ON COLUMN — one short technical sentence per non-obvious column.
-- Mention format rules the DB does not enforce (the LLM obeys these).
comment on column tasks.status   is 'Current state. Moves forward only: open → in_progress → done.';
comment on column tasks.priority is 'Task urgency. Defaults to normal.';
comment on column tasks.due_date is 'Target completion date. Nullable.';

-- Activity log opt-in (see Activity Log reference).
select enable_activity_log('tasks');

If the table needs to be readable from chat, grant chat_reader SELECT access and add a parallel RLS policy:

grant select on tasks to chat_reader;

create policy "tasks_select_chat" on tasks
  for select to chat_reader
  using (team_id = current_chat_team());

Writes run through typed tools using the service role client, so no TO chat_reader write policies are needed.

2. shared/permissions.ts — register the table

export const tablePermissions = {
  // ...
  tasks: {
    view:   "tasks.view",
    create: "tasks.create",
    update: "tasks.update",
    delete: "tasks.delete",
  },
} as const satisfies Record<string, Record<CrudAction, Permission>>;

Read-only tables point their write actions at readonly.write (empty role set):

activity_log: {
  view:   "activity.view",
  create: "readonly.write",
  update: "readonly.write",
  delete: "readonly.write",
},

3. server/utils/chat-tools/mutations.ts — (optional) custom write tool

The generic create/update/delete tools cover most tables. Add a custom tool only when the resource needs unusual validation, multi-step orchestration, or side-effects that differ from a raw upsert.

Configuration

• OPENROUTER_API_KEY — required. Gets you access to every model on OpenRouter through one key. Get one at openrouter.ai. Leave blank to disable the chat and hide its UI.
• Model — set in server/api/chats/[id].post.ts via openrouter("anthropic/claude-sonnet-4-6"). Swap to any OpenRouter model by changing the string.
• Switching providers — install @ai-sdk/anthropic / @ai-sdk/openai / @ai-sdk/google, use anthropic("claude-sonnet-4-6") / openai("gpt-4o") / google("gemini-2.5-pro"), set the matching *_API_KEY. Everything else stays the same.

Testing

Once you've added a table, try questions that exercise both surfaces:

• Read — "How many open tasks do we have?" / "Show me the latest 5 tasks assigned to Sarah"
• Write — "Create a task called 'Review Q2 budget', priority high, due next Friday"
• Constraint feedback — ask it to create a task with a bad status value. It should see the one of: (...) list in the error and retry.

Every write shows up at /app/activity tagged with source='chat' and the chat ID in source_ref.