Supabase AI SQL functions

Call LLMs directly from SQL on Supabase — completions, classification, sentiment, extraction, translation, redaction, and embeddings.

supa_aisql adds AI inference functions to your Postgres database. There's no CLI step to wire up infrastructure: a single ai.deploy() call bootstraps its own Supabase Edge Function straight from SQL.

Copyselect ai_sentiment(body) from posts;
-- → 'positive'

select category, ai_summarize_agg(description)
from products
group by category;
-- → one LLM call per group

---

Contents

• Features
• How it works
• Prerequisites
• Quick start
• Function reference
• Usage examples
• Providers
• Aggregate safety caps
• Security model
• Re-deploying
• Troubleshooting
• License

---

Features

• 🧠 LLM inference in plain SQL — no app server, no glue code.
• 🚀 Self-deploying — ai.deploy() builds and ships its own Edge Function via the Management API.
• 🔀 Provider-flexible — OpenAI by default; works with any OpenAI-compatible API (Groq, Together, Azure, Ollama), or Supabase's free built-in inference.
• 📊 Aggregate-aware — GROUP BY-friendly aggregates make one LLM call per group, not per row.
• 🔒 Secure by default — secrets stay in Vault / Edge secrets; functions are restricted to the authenticated role.

---

How it works

Your SQL query
    └─► public.ai_sentiment(text)          ← SECURITY DEFINER, authenticated only
            └─► public._ai_call(jsonb)     ← reads config from ai._config
                    └─► POST /functions/v1/ai   ← edge function on your project
                              └─► OpenAI-compatible API  (or Supabase built-in)

ai.deploy() bootstraps the whole pipeline from Postgres in one call:

1. Reads your personal access token from Vault (encrypted at rest).
2. Fetches your project's anon key from the Management API.
3. Deploys the self-contained edge function to /functions/v1/ai.
4. Saves project_ref + anon_key to ai._config.

The edge function source is embedded directly in the SQL file (zero imports, raw fetch) so it can be deployed via the Management API without the CLI.

---

Prerequisites

• A Supabase project (any plan).
• A personal access token (sbp_…).
• An API key for an OpenAI-compatible provider — or use the free Supabase built-in inference.

---

Quick start

1. Install the extension

From the SQL editor or psql:

Copyselect dbdev.install('JesseVent/supa_aisql');
create extension "JesseVent/supa_aisql";

Note: database.dev must be enabled on your project. If it isn't, follow the dbdev setup guide.

2. Store your personal access token in Vault

Copyselect vault.create_secret('sbp_xxxxxxxxxxxx', 'ai_access_token');

This is the only secret that touches the database. It is AES-256 encrypted at rest and only ever decrypted inside ai.deploy() — it never appears in query logs or pg_stat_activity. Get your token from supabase.com/dashboard/account/tokens.

3. Set your provider API key

Set it via the Supabase CLI — never pass the key as a SQL parameter, since it would land in pg_stat_activity logs:

Copysupabase secrets set OPENAI_API_KEY=sk-...

Or via the dashboard: Settings → Edge Functions → Secrets.

<details> <summary><strong>Using a different provider?</strong> (Groq, Together, Azure, Ollama)</summary>

Any OpenAI-compatible API works. Set OPENAI_BASE_URL and reuse OPENAI_API_KEY for the credential:

Copy# Groq
supabase secrets set OPENAI_BASE_URL=https://api.groq.com/openai/v1
supabase secrets set OPENAI_API_KEY=gsk_...

# Together AI
supabase secrets set OPENAI_BASE_URL=https://api.together.xyz/v1
supabase secrets set OPENAI_API_KEY=...

# Azure OpenAI
supabase secrets set OPENAI_BASE_URL=https://<resource>.openai.azure.com/openai/deployments/<deployment>
supabase secrets set OPENAI_API_KEY=...

# Local Ollama (self-hosted)
supabase secrets set OPENAI_BASE_URL=http://localhost:11434/v1
supabase secrets set OPENAI_API_KEY=ollama

Omit OPENAI_BASE_URL to default to https://api.openai.com/v1.

</details>

4. Deploy

Copyselect ai.deploy('your-project-ref');
-- → { "status": "ok", "deployed": "functions/v1/ai", "project_ref": "..." }

This is the only time you need your project ref. Everything else is automatic.

5. Verify

Copyselect ai.status();

Copy{
  "version": "1.0.0",
  "configured": true,
  "project_ref": "abcdefghijklmnopqrst",
  "vault_token": true
}

6. Run your first query

Copyselect ai_complete('Write a one-sentence tagline for a Postgres extension.');

---

Function reference

Scalar — one LLM call per row

Function	Returns	Description
ai_complete(input, system?, model?, provider?)	text	Free-form completion
ai_classify(input, categories?, model?, provider?)	text	One of the supplied labels
ai_sentiment(input, model?, provider?)	text	positive, negative, or neutral
ai_extract(input, schema_hint?, model?, provider?)	jsonb	Structured fields as JSON
ai_translate(input, target_language?, model?, provider?)	text	Translated text
ai_redact(input, entity_types?, model?, provider?)	text	PII replaced with [REDACTED]
ai_embed(input, model?, provider?)	real[]	Embedding vector (1536 dims, OpenAI only)

Aggregates — one LLM call per GROUP BY group

Aggregate	Returns	Description
ai_summarize_agg(text)	text	Summarize all rows in a group
ai_extract_agg(text)	jsonb	Extract entities across all rows in a group

All scalar functions default to provider => 'openai'. Pass provider => 'supabase' for free built-in inference (see Providers). Aggregates always use the openai provider.

---

Usage examples

Copy-- Free-form completion
select ai_complete('Explain JSONB indexes in one sentence.');

-- Custom system prompt
select ai_complete(
  'What is the capital of France?',
  system_text => 'You are a geography tutor. Be brief.'
);

-- Sentiment on every row
select id, body, ai_sentiment(body) as mood
from posts;

-- Classify with custom labels
select ai_classify(
  'The login button is broken',
  array['bug', 'feature-request', 'question']
);
-- → 'bug'

-- Extract structured data
select ai_extract(
  'Jesse Vent, jesse@example.com, Supabase, joined 2024-01-15',
  schema_hint => 'name, email, company, joined_date'
);
-- → { "name": "Jesse Vent", "email": "jesse@example.com", "company": "Supabase", "joined_date": "2024-01-15" }

-- Translate
select ai_translate('Good morning', target_language => 'Japanese');
-- → おはようございます

-- Redact PII
select ai_redact('Call me at 555-123-4567 or email bob@example.com');
-- → Call me at [REDACTED] or email [REDACTED]

-- Embeddings (cast to vector if pgvector is installed)
select ai_embed('hello world')::vector;

-- GROUP BY aggregate — one LLM call per category
select category, ai_summarize_agg(description)
from products
group by category;

-- Extract entities grouped by user
select user_id, ai_extract_agg(message)
from support_tickets
group by user_id;

---

Providers

openai (default)

Routes to OPENAI_BASE_URL (default https://api.openai.com/v1) with OPENAI_API_KEY. Works with any OpenAI-compatible API — swap the URL and key to use Groq, Together AI, Azure OpenAI, Ollama, and others without changing your SQL.

Default model: gpt-5.4-mini. Default embedding model: text-embedding-3-small.

Copy-- Default provider
select ai_complete('Write a haiku about Postgres.');

-- Override model per call
select ai_complete(
  'Write a haiku about Postgres.',
  model_name => 'gpt-5.4-pro'
);

supabase (free, no key required)

Uses Supabase built-in inference (Mistral) at no extra cost. No API key needed — runs entirely within the Edge Function runtime.

Copyselect ai_complete('Hello', provider => 'supabase');
select ai_sentiment('Great product!', provider => 'supabase');
select ai_classify('Bug in login', array['bug', 'feature'], provider => 'supabase');

ai_embed requires provider => 'openai' — built-in inference does not expose an embeddings endpoint.

---

Aggregate safety caps

Aggregates accumulate row values in memory before a single LLM call. Two GUCs cap input size to prevent runaway token costs:

Copyset ai_agg.max_items = 50;    -- max rows accumulated per group (default: 100)
set ai_agg.max_chars = 8000;  -- max joined characters sent per group (default: 12000)

Set these at the session level before expensive aggregate queries.

---

Security model

Credential	Where it lives	Who can read it
Personal access token (sbp_…)	Vault (AES-256 encrypted)	postgres / service role only, inside ai.deploy()
Provider API key	Edge Function secrets (set via CLI)	Edge runtime only — never stored in DB, never a SQL parameter
Anon key	ai._config table	Readable by service role — same key shipped to browsers

Function access — all ai_* functions are REVOKEd from public and GRANTed to authenticated only. Unauthenticated callers cannot trigger LLM calls.

Prompt injection — caller-supplied values interpolated into system prompts (categories, entity_types, target_language, schema_hint) are sanitized (control characters stripped, length-capped) before being embedded in the prompt. The input field is passed as the user message — standard LLM input separation.

---

Re-deploying

To update the edge function after an extension upgrade:

Copyselect ai.deploy('your-project-ref');

The edge function source embedded in the SQL file is always in sync with the installed extension version.

---

Troubleshooting

Error	Fix
No access token found in Vault	Run step 2 — vault.create_secret(...) with the name ai_access_token.
OPENAI_API_KEY secret is not set	The edge function is deployed but the secret isn't. Set it via CLI/dashboard, then retry (no re-deploy needed).
Failed to fetch API keys (HTTP 401)	Your Vault token is expired/invalid. Recreate it: delete from vault.secrets where name = 'ai_access_token'; select vault.create_secret('sbp_new...', 'ai_access_token');
supa_aisql extension not configured	ai.deploy() hasn't run yet, or config was cleared. Re-run it.
Supabase built-in inference does not support embeddings. Use provider=openai.	Switch to provider => 'openai' for ai_embed.

---

License

MIT © Jesse Vent