AI Chat with database

[tarunpahade18]

AI to interact with database where people user ai chat that can query database answer documents retrive data make api calls

[solvionholdings]

The safe way to build this is to not let the model talk to the database directly. Give it a few tools you write yourself. Each tool runs as the signed-in user, so your RLS policies decide what it can see.

In an Edge Function or a Next.js route, create the client with the caller's JWT, not the service_role key:

const supabase = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY, {
  global: { headers: { Authorization: req.headers.get('Authorization')! } },
})

// a "tool" the model can call
async function searchDocuments(q: string) {
  const { data, error } = await supabase
    .from('documents')
    .select('id, title, content')
    .ilike('title', `%${q}%`)
    .limit(5)
  if (error) throw error
  return data
}

A few rules that save trouble later:

• No service_role key anywhere the model's output can steer. It bypasses RLS.
• If you want free-form SQL, run it as a dedicated role that only has SELECT on the tables it needs, plus a statement_timeout. Rely on grants, not just a read-only session setting, because SQL can turn that setting off.
• Treat document text as untrusted input. A document can contain instructions. Don't let the model call write tools or outside APIs based on it without a confirmation step, and keep an allowlist of the APIs it may call.

Supabase also just shipped a template for exactly this, an MCP server for your app where each request runs as the signed-in user: https://github.com/orgs/supabase/discussions/52171