Realtime agent trace bridge for SupaAgent (table, broadcast trigger, channel authorization)

# supa_agent_trace

Realtime agent trace bridge for [SupaAgent](https://github.com/JesseVent/supa-agent). Install this on the Supabase project that hosts the live trace channel (the project your DevTool is connected to).

## What it installs

| Object | Purpose |
| --- | --- |
| `public.agent_trace_events` | Persisted trace events (RLS: owner-only), used for backfill/replay |
| `public.agent_trace_topic(uuid)` | Derives the private Realtime topic `agent-trace:{sha256hex(user_id)}` |
| `agent_trace_broadcast_trigger` | Broadcasts every inserted event to the owner's private topic via `realtime.send()` |
| Policies on `realtime.messages` | Private-channel authorization: owners may join/read/write only their own topic |
| `public.agent_trace_prune(interval)` | Retention helper — schedule with pg_cron |

Single write path: publishers `INSERT` into `agent_trace_events`; the trigger broadcasts. This guarantees everything broadcast is also persisted, gives DB-side ordering, and `realtime.send()` writes through `realtime.messages` so [Broadcast Replay](https://supabase.com/docs/guides/realtime/broadcast) (`since`) works for reconnect gap-fill.

## Install

### Via database.dev (once published)

```sql
select dbdev.install('jessevent@supa_agent_trace');
create extension "jessevent@supa_agent_trace";
```

### Direct SQL

Run `supa_agent_trace--0.1.0.sql` against the project (psql, MCP `execute_sql`, or SQL editor). The script is idempotent.

## Companion edge function (required for auth)

Both the extension and the DevTool authenticate with the Supabase **Management API** (account-level OAuth) — neither holds a project GoTrue session. The `agent-trace-token` edge function (in `supabase/functions/agent-trace-token/`) validates that the caller's Management token can access *this* project (`GET /v1/projects/{ref}`) and exchanges it for a short-lived project JWT whose `sub` is a deterministic project-scoped UUID (UUIDv5 of the project ref), so `auth.uid()` matches on both sides.

> Pairing is per **project**, not per platform user: anyone with Management API access to the project (e.g. org members who authorized the app) shares the same trace channel. The per-user design (`gotrue_id` from `/v1/profile`) is currently impossible because that endpoint rejects OAuth tokens (`"GET /v1/profile does not support oauth access yet"`).

```bash
supabase functions deploy agent-trace-token --no-verify-jwt
supabase secrets set AGENT_TRACE_JWT_SECRET="<project legacy JWT secret>"
```

The secret is the project's **legacy JWT secret** (Dashboard → Project Settings → API → JWT Settings).

> ⚠️ Projects that migrated to asymmetric JWT signing keys *and revoked the legacy secret* cannot verify HS256 tokens — the bridge then falls back to public-channel mode (unguessable hashed topic, no persistence).

## Retention

Events are kept until pruned. To prune daily at 03:00:

```sql
select cron.schedule('prune-agent-traces', '0 3 * * *', $$select public.agent_trace_prune()$$);
```

## Publishing to database.dev

```bash
cd supabase/extensions/supa_agent_trace
dbdev login
dbdev publish
```

`supa_agent_trace.control`, the versioned SQL file, and this README make up the package.


AI inference SQL functions for Supabase. Self-deploys its own edge function via ai.deploy().

# ai_sql

AI inference SQL functions for Supabase. Call LLMs directly from SQL — completions, classification, sentiment, extraction, translation, redaction, and embeddings. Self-deploys its own edge function from a single SQL call; no CLI required.

```sql
select ai_sentiment(body) from posts;
-- → 'positive'

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

---

## 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](https://supabase.com/dashboard/account/tokens) (`sbp_…`)
- An API key for an OpenAI-compatible provider, **or** use the free Supabase built-in inference

---

## Bootstrap

### 1. Install the extension

From the SQL editor or `psql`:

```sql
select dbdev.install('JesseVent/supa_aisql');
create extension "JesseVent/supa_aisql";
```

> **Note:** [database.dev](https://database.dev) must be enabled on your project. If it isn't, follow the [dbdev setup guide](https://database.dev/installer).

### 2. Store your personal access token in Vault

```sql
select 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](https://supabase.com/dashboard/account/tokens)**.

### 3. Set your API key

Use the Supabase CLI — **never pass the key as a SQL parameter**, as it would appear in `pg_stat_activity` logs:

```bash
supabase secrets set OPENAI_API_KEY=sk-...
```

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

> **Using a different provider?** Any OpenAI-compatible API works. Also set `OPENAI_BASE_URL` and use the same `OPENAI_API_KEY` variable for the credential:
>
> ```bash
> # 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`.

### 4. Deploy

```sql
select 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

```sql
select ai.status();
```

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

### 6. Run your first query

```sql
select ai_complete2('Write a one-sentence tagline for a Postgres extension.');
```

---

## Functions

### Scalar — one LLM call per row

| Function | Returns | Description |
|---|---|---|
| `ai_complete2(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 functions default to `provider => 'openai'`. Pass `provider => 'supabase'` for free built-in inference (see [Providers](#providers)).

---

## Usage

```sql
-- Free-form completion
select ai_complete2('Explain JSONB indexes in one sentence.');

-- Custom system prompt
select ai_complete2(
  '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`.

```sql
-- Default provider
select ai_complete2('Write a haiku about Postgres.');

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

### `supabase` (free, no key required)

Uses [Supabase built-in inference](https://supabase.com/docs/guides/ai/quickstarts/generate-text-using-ai-models) (Mistral) at no extra cost. No API key needed — runs entirely within the Edge Function runtime.

```sql
select ai_complete2('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:

```sql
set 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()` |
| OpenAI 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 `REVOKE`d from `public` and `GRANT`ed 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, which is standard LLM input separation.

---

## Re-deploying

To update the edge function after an extension upgrade:

```sql
select ai.deploy('your-project-ref');
```

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

---

## Troubleshooting

**`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 the CLI or dashboard, then retry your query (no re-deploy needed).

**`Failed to fetch API keys (HTTP 401)`**
Your personal access token in Vault is expired or invalid. Create a new one and update Vault:
```sql
select vault.update_secret('sbp_new...', 'ai_access_token');
```

**`ai_sql extension not configured`**
`ai.deploy()` hasn't been run yet, or the config was cleared. Re-run it.

**`embed action requires provider=openai`**
Switch to `provider => 'openai'` for `ai_embed` — the Supabase built-in provider doesn't expose an embeddings endpoint.


Formatting-preserving anonymisation and data masking

# supa_privacy - It's not just private, it's supa private.

## PostgreSQL Data Anonymisation TLE Extension

`supa_privacy` is a relocatable, pure SQL PostgreSQL **Trusted Language Extension (TLE)** designed for database-side data de-identification, compliance, and sandbox security. It provides standard masking, hashing, and perturbation functions and is fully optimized for **[database.dev](https://database.dev)** and **Supabase**.

It is particularly useful for:
- Preparing production database schemas for staging or development environments.
- Sharing data with external analytics teams while respecting privacy regulations (GDPR, HIPAA, CCPA).
- Implementing Dynamic Data Masking (DDM) for low-privilege database roles.

---

## 🚀 Installation

### 1. Enable TLE Support
`supa_privacy` runs as a Trusted Language Extension and requires the `pg_tle` extension to be enabled in your PostgreSQL database:

```sql
CREATE EXTENSION IF NOT EXISTS "pg_tle";
```

### 2. Installing from database.dev

Using the [dbdev CLI](https://supabase.github.io/dbdev):

```bash
dbdev add -o ./migrations -s extensions -v 1.0.1 package -n "jvent@supa_privacy"
```

This will generate a migration file in your `./migrations` folder containing the SQL required to load the extension. After applying the migration, enable the extension:

```sql
CREATE EXTENSION "jvent@supa_privacy" VERSION '1.0.1' SCHEMA supa_privacy;
```

---

## 🛠️ API Reference

All functions are located under the schema where the extension is installed (e.g. `supa_privacy`).

### 1. Email Masking
Masks the username part of an email address while keeping the domain.
* **Function:** `supa_privacy.mask_email(email text) RETURNS text`
* **Behavior:** 
  - `jesse@vent.com` ➡️ `j***e@vent.com`
  - `ab@vent.com` ➡️ `a*@vent.com`
  - `a@vent.com` ➡️ `*@vent.com`
* **Example:**
  ```sql
  SELECT supa_privacy.mask_email('jesse@vent.com'); -- Returns: j***e@vent.com
  ```

### 2. Phone Masking
Masks digit characters while preserving formatting characters (such as spaces, hyphens, plus signs, and brackets).
* **Format-Preserving Masking:** `supa_privacy.mask_phone_flexible(phone text, keep_digits int DEFAULT 4, mask_char char DEFAULT '*') RETURNS text`
* **Default Wrapper:** `supa_privacy.mask_phone(phone text) RETURNS text` (wrapper calling `mask_phone_flexible` keeping 4 digits).
* **Behavior:**
  - `+1 (202) 555-0143` ➡️ `+* (***) ***-0143`
  - `+61412345678` ➡️ `+*******5678`
* **Example:**
  ```sql
  SELECT supa_privacy.mask_phone_flexible('+1 (202) 555-0143', 4); -- Returns: +* (***) ***-0143
  ```

### 3. Text Masking & Redaction
Fully or partially obscures text strings.
* **Full Masking:** `supa_privacy.mask_text(val text, mask_char char DEFAULT '*') RETURNS text`
  - `secret` ➡️ `******`
* **Partial Masking:** `supa_privacy.partial_mask(val text, prefix_keep int, suffix_keep int, mask_char char DEFAULT '*') RETURNS text`
  - `supa_privacy.partial_mask('1234-5678-9012', 4, 4, '*')` ➡️ `1234******9012`
* **Examples:**
  ```sql
  SELECT supa_privacy.mask_text('secret'); -- Returns: ******
  SELECT supa_privacy.partial_mask('1234-5678-9012', 4, 4, '*'); -- Returns: 1234******9012
  ```

### 4. Cryptographic Salted Hashing (Deterministic)
Generates a deterministic SHA-256 hash using a secret salt.
* **Function:** `supa_privacy.salted_hash(val text, salt text) RETURNS text`
* **Example:**
  ```sql
  SELECT supa_privacy.salted_hash('user_id_123', 'secret_salt_key');
  -- Returns hex-encoded SHA-256 hash: 39de1a88b56f...
  ```

### 5. Numeric Perturbation (Noise Injection)
Adds bounded random variance (noise) to numeric fields to protect details while preserving statistical averages.
* **Volatile Perturbation:** `supa_privacy.perturb_numeric(val numeric, max_deviation numeric DEFAULT 0.07) RETURNS numeric`
  - Noise changes on every query execution.
* **Deterministic Perturbation:** `supa_privacy.perturb_numeric_deterministic(val numeric, seed_key text, max_deviation numeric DEFAULT 0.07) RETURNS numeric`
  - Noise is seeded by `seed_key` (typically the row's primary key) so it remains identical across multiple query executions (critical for analytical query stability).
* **Examples:**
  ```sql
  -- Volatile noise
  SELECT supa_privacy.perturb_numeric(100.0, 0.10); -- Returns a value between 90.0 and 110.0
  -- Deterministic noise (always returns same value for 'user_1')
  SELECT supa_privacy.perturb_numeric_deterministic(100.0, 'user_1', 0.10);
  ```

### 6. Date Generalization & Shifting
Obfuscates absolute dates via truncation or chronological offsets.
* **Date Generalization (Bucketing):** `supa_privacy.generalize_date(val date, bucket text DEFAULT 'month') RETURNS date`
  - Truncates dates to `'year'`, `'quarter'`, `'month'`, or `'week'`.
* **Deterministic Date Shifting:** `supa_privacy.shift_date_deterministic(val date, seed_key text, max_days int DEFAULT 30) RETURNS date`
  - Shifts date back/forward by a deterministic number of days based on a seed key. Ideal for HIPAA compliance (preserves chronological order of events within a user profile while masking the absolute dates).
* **Examples:**
  ```sql
  SELECT supa_privacy.generalize_date('2026-06-08'::date, 'year'); -- Returns: 2026-01-01
  SELECT supa_privacy.shift_date_deterministic('2026-06-08'::date, 'user_123_salt', 15); -- Shifts by +/- 15 days deterministically
  ```

### 7. Numeric Generalization (Bucketing)
Groups numbers into custom bucket intervals (useful for ages, salaries).
* **Function:** `supa_privacy.generalize_numeric(val numeric, bucket_size numeric) RETURNS numeric`
* **Example:**
  ```sql
  SELECT supa_privacy.generalize_numeric(27, 5); -- Returns: 25 (rounds to nearest multiple of 5)
  ```

### 8. Dynamic Masked View Generator (Extensible)
Scans a physical table and automatically generates a secure VIEW that replaces sensitive fields with masking expressions while leaving other columns untouched.
* **Function:** `supa_privacy.create_masked_view(source_table regclass, view_name text, rules jsonb) RETURNS void`
* **Rules JSONB Format:**
  A JSON object mapping column names to rule configurations. Supported rule configurations:
  - `{"type": "email"}`
  - `{"type": "phone", "keep_digits": 4}` (uses formatting-preserving masking)
  - `{"type": "hash", "salt": "my_salt"}`
  - `{"type": "perturb", "variance": 0.05, "seed_column": "id"}` (deterministic noise)
  - `{"type": "shift_date", "days": 15, "seed_column": "id"}` (deterministic date shifting)
  - `{"type": "generalize_numeric", "bucket": 10}`
  - `{"type": "generalize_date", "bucket": "year"}`
  - `{"type": "redact", "value": "NULL"}` (replaces value with constant or NULL)
  - `{"type": "custom", "expression": "upper(reverse({col}))"}` (interpolates `{col}` with column name)

---

## 📖 End-to-End Example

Here is a full walkthrough showing how to de-identify a customer table.

### 1. Create a Source Table and Insert Mock Data
```sql
CREATE TABLE public.customers (
    id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    phone TEXT,
    age INT,
    yearly_salary NUMERIC(10, 2),
    signup_date DATE NOT NULL
);

INSERT INTO public.customers (full_name, email, phone, age, yearly_salary, signup_date) VALUES
('John Doe', 'john.doe@gmail.com', '+1 (202) 555-0143', 27, 85000.00, '2025-03-12'),
('Jane Smith', 'jane_smith@corp.com', '+61 412 000 222', 43, 142000.00, '2024-11-20'),
('Bob Johnson', 'bjohnson@yahoo.com', '0491 570 156', 58, 62500.50, '2026-01-05');
```

### 2. Generate a Masked View
Define anonymisation rules in JSON and invoke `create_masked_view`:

```sql
SELECT supa_privacy.create_masked_view(
    'public.customers'::regclass,
    'public.v_customers_deidentified',
    '{
        "full_name": {"type": "hash", "salt": "AppSaltKey123"},
        "email": {"type": "email"},
        "phone": {"type": "phone", "keep_digits": 4},
        "age": {"type": "generalize_numeric", "bucket": 10},
        "yearly_salary": {"type": "perturb", "variance": 0.07, "seed_column": "id"},
        "signup_date": {"type": "shift_date", "days": 15, "seed_column": "id"}
    }'::jsonb
);
```

### 3. Query the Masked View
Query the generated view:

```sql
SELECT * FROM public.v_customers_deidentified;
```

**Result:**
| id | full_name | email | phone | age | yearly_salary | signup_date |
|---|---|---|---|---|---|---|
| 1 | `e97a3a9489f...` | `j***e@gmail.com` | `+* (***) ***-0143` | 30 | `87140.23` (Stable) | `2025-03-14` (Shifted) |
| 2 | `a902b4d812d...` | `j***h@corp.com` | `+** *** *** 0222` | 40 | `138402.12` (Stable) | `2024-11-12` (Shifted) |
| 3 | `d8293bcde11...` | `b***n@yahoo.com` | `**** *** 0156` | 60 | `64102.50` (Stable) | `2026-01-08` (Shifted) |

---

## 🔐 RLS & Access Control Integration Patterns

For production environments (like **Supabase**), you should combine `supa_privacy`'s Column-level Dynamic Data Masking (DDM) with PostgreSQL **Row-Level Security (RLS)** for comprehensive access control.

### 1. Dual-Layer Masking + RLS Pattern
**Best Practice:** Always apply RLS to both the base table and the generated view. This ensures that users see only the rows they are allowed to see, with sensitive columns masked.

```sql
-- 1. Create source table with RLS
CREATE TABLE public.customers (
    id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    phone TEXT,
    department_id INT,  -- RLS filter key
    yearly_salary NUMERIC(10, 2)
);

ALTER TABLE public.customers ENABLE ROW LEVEL SECURITY;

-- 2. Create RLS policy for the base table
CREATE POLICY cust_department_policy ON public.customers
    FOR SELECT
    USING (department_id = current_setting('app.current_department_id')::int);

-- 3. Generate the masked view (DDM layer)
SELECT supa_privacy.create_masked_view(
    'public.customers'::regclass,
    'public.v_customers_masked',
    '{
        "full_name": {"type": "hash", "salt": "SecureAppSaltKey"},
        "email": {"type": "email"},
        "phone": {"type": "phone"},
        "yearly_salary": {"type": "perturb", "variance": 0.07}
    }'::jsonb
);

-- 4. Enable RLS on the masked view (CRITICAL!)
ALTER TABLE public.v_customers_masked ENABLE ROW LEVEL SECURITY;

CREATE POLICY mask_department_policy ON public.v_customers_masked
    FOR SELECT
    USING (department_id = current_setting('app.current_department_id')::int);

-- 5. Grant permissions to database role
GRANT SELECT ON public.v_customers_masked TO app_user;
```

### 2. Alternative: Role-Based Transparent Masking
If you want users to query the base table directly but have masking applied conditionally based on the user's role or session variables:

```sql
-- Wrapper function to conditionally mask based on session variables
CREATE OR REPLACE FUNCTION public.get_secured_customers()
RETURNS TABLE(
    id INT,
    full_name TEXT,
    email TEXT,
    phone TEXT,
    department_id INT,
    yearly_salary NUMERIC(10, 2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        c.id,
        CASE WHEN current_setting('app.masking_enabled', true) = 'true'
            THEN supa_privacy.mask_text(c.full_name, '*')
            ELSE c.full_name
        END,
        CASE WHEN current_setting('app.masking_enabled', true) = 'true'
            THEN supa_privacy.mask_email(c.email)
            ELSE c.email
        END,
        CASE WHEN current_setting('app.masking_enabled', true) = 'true'
            THEN supa_privacy.mask_phone(c.phone)
            ELSE c.phone
        END,
        c.department_id,
        CASE WHEN current_setting('app.masking_enabled', true) = 'true'
            THEN supa_privacy.perturb_numeric(c.yearly_salary, 0.07)
            ELSE c.yearly_salary
        END
    FROM public.customers c
    WHERE c.department_id = current_setting('app.current_department_id', true)::int;
END;
$$ LANGUAGE plpgsql;
```

---

## 🛡️ License
MIT License. Feel free to use and distribute.


# Supabase AI SQL functions

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

[![dbdev](https://img.shields.io/badge/dbdev-JesseVent%2Fsupa__aisql-3ecf8e)](https://database.dev/JesseVent/supa_aisql)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)
[![Supabase](https://img.shields.io/badge/Supabase-Edge%20Functions-3ecf8e?logo=supabase)](https://supabase.com)

`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.

```sql
select ai_sentiment(body) from posts;
-- → 'positive'

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

---

## Contents

- [Features](#features)
- [How it works](#how-it-works)
- [Prerequisites](#prerequisites)
- [Quick start](#quick-start)
- [Function reference](#function-reference)
- [Usage examples](#usage-examples)
- [Providers](#providers)
- [Aggregate safety caps](#aggregate-safety-caps)
- [Security model](#security-model)
- [Re-deploying](#re-deploying)
- [Troubleshooting](#troubleshooting)
- [License](#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](https://supabase.com/dashboard/account/tokens) (`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`:

```sql
select dbdev.install('JesseVent/supa_aisql');
create extension "JesseVent/supa_aisql";
```

> **Note:** [database.dev](https://database.dev) must be enabled on your project. If it isn't, follow the [dbdev setup guide](https://database.dev/installer).

### 2. Store your personal access token in Vault

```sql
select 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](https://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:

```bash
supabase 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:

```bash
# 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

```sql
select 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

```sql
select ai.status();
```

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

### 6. Run your first query

```sql
select 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](#providers)). Aggregates always use the `openai` provider.

---

## Usage examples

```sql
-- 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`.

```sql
-- 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](https://supabase.com/docs/guides/ai/quickstarts/generate-text-using-ai-models) (Mistral) at no extra cost. No API key needed — runs entirely within the Edge Function runtime.

```sql
select 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:

```sql
set 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 `REVOKE`d from `public` and `GRANT`ed 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:

```sql
select 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](https://opensource.org/licenses/MIT) © Jesse Vent


Database table and column statistical profiler

# supa_profile - PostgreSQL Database Table & Column Profiler

`supa_profile` is a relocatable, pure SQL PostgreSQL **Trusted Language Extension (TLE)** designed for native table and column-level statistical profiling. It introspects schema metadata, dynamically constructs profiling queries, and compiles a comprehensive statistical report (including counts, nullability, distinctness, min/max, string length distributions, averages, medians, percentiles, and frequent value distributions) returned directly as a single structured JSONB document.

The JSONB output format is fully compatible with the **`ProfilingResult`** TypeScript interface used by frontend profiling dashboards.

---

## 🚀 Installation

### 1. Enable TLE Support
`supa_profile` runs as a Trusted Language Extension and requires the `pg_tle` extension to be enabled:

```sql
CREATE EXTENSION IF NOT EXISTS "pg_tle";
```

### 2. Installing from database.dev

Using the [dbdev CLI](https://supabase.github.io/dbdev):

```bash
dbdev add -o ./migrations -s extensions -v 1.0.0 package -n "jvent@supa_profile"
```

This generates a migration script to load the TLE. Apply the migration, then enable the extension:

```sql
CREATE EXTENSION "jvent@supa_profile" VERSION '1.0.0' SCHEMA supa_profile;
```

---

## 🛠️ API Reference

### 1. Profile Table
Introspects and profiles a physical table, view, or materialized view.
* **Function:** `supa_profile.profile_table(target_table regclass, options jsonb DEFAULT '{}') RETURNS jsonb`
* **Options Parameter (`options`):**
  A JSON object supporting the following parameters:
  - `scan_field_values` (boolean, default: `true`): Scan column values to compute top value frequency distributions.
  - `min_cell_count` (int, default: `5`): Minimum occurrence count of a value to include it in the value distribution list.
  - `max_distinct_values` (int, default: `100`): Maximum distinct values returned per column in the value distribution list.
  - `rows_per_table` (int, default: `0` = unlimited): Maximum rows to scan. If greater than zero, it wraps the source in a sampled subquery (`LIMIT N`).
  - `calculate_numeric_stats` (boolean, default: `true`): Compute average, median, 90th percentile, and 99th percentile for numeric fields.

* **Example:**
  ```sql
  SELECT supa_profile.profile_table(
      'public.users'::regclass,
      '{"rows_per_table": 10000, "min_cell_count": 10}'::jsonb
  );
  ```

---

## 📊 Output Schema (JSONB)

The function returns a JSONB object containing:
- `tableStats`: Table name, row count, column count.
- `fields`: Array of column-level statistics.
- `values`: Value frequency distribution array.
- `profiledAt`: Timestamp in UTC.
- `durationMs`: Total duration of the profiling process.
- `queryMode`: Current query mode (`NORMAL` or `FAST` for sampled scans).

### Field Properties:
| Property | Type | Description |
|---|---|---|
| `columnName` | `text` | Column identifier |
| `dataType` | `text` | Column type format |
| `nullable` | `boolean` | True if nulls are allowed |
| `nonNullCount` | `bigint` | Total count of non-null cells |
| `nullCount` | `bigint` | Total count of null cells |
| `distinctCount` | `bigint` | Total unique value count |
| `minValue` | `text` | Minimum value (omitted for JSON/bytes/arrays) |
| `maxValue` | `text` | Maximum value (omitted for JSON/bytes/arrays) |
| `avgValue` | `numeric` | Average value (numeric columns only) |
| `median` | `numeric` | Median value / P50 (numeric columns only) |
| `p90` | `numeric` | 90th percentile value (numeric columns only) |
| `p99` | `numeric` | 99th percentile value (numeric columns only) |
| `avgLength` | `numeric` | Average string length of text representation |
| `maxLength` | `bigint` | Maximum string length of text representation |
| `mode` | `text` | Most frequent value (omitted if `scan_field_values` is false) |
| `pattern` | `text` | Inferred regex pattern for type verification |

---

## 📖 Example Walkthrough

### 1. Create Sample Table
```sql
CREATE TABLE public.customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT,
    country VARCHAR(3) DEFAULT 'USA',
    signup_date DATE
);

INSERT INTO public.customers (name, age, country, signup_date) VALUES
('Alice', 25, 'USA', '2025-01-10'),
('Bob', 30, 'USA', '2025-02-15'),
('Charlie', 45, 'CAN', '2024-11-20'),
('Diana', 25, 'USA', '2025-03-01'),
('Ethan', NULL, 'GBR', NULL);
```

### 2. Profile the Table
```sql
SELECT jsonb_pretty(supa_profile.profile_table('public.customers'::regclass, '{"min_cell_count": 1}'::jsonb));
```

### 3. Example JSONB Output
```json
{
  "fields": [
    {
      "mode": "",
      "pattern": "-?\\d+",
      "dataType": "integer",
      "nullable": false,
      "nullCount": 0,
      "avgLength": 1.0,
      "columnName": "id",
      "distinctCount": 5,
      "maxLength": 1,
      "nonNullCount": 5,
      "tableName": "public.customers",
      "avgValue": 3.0,
      "median": 3.0,
      "p90": 5.0,
      "p99": 5.0,
      "minValue": "1",
      "maxValue": "5"
    },
    {
      "mode": "",
      "pattern": ".*",
      "dataType": "text",
      "nullable": false,
      "nullCount": 0,
      "avgLength": 4.6,
      "columnName": "name",
      "distinctCount": 5,
      "maxLength": 7,
      "nonNullCount": 5,
      "tableName": "public.customers",
      "minValue": "Alice",
      "maxValue": "Ethan"
    },
    {
      "mode": "25",
      "pattern": "-?\\d+",
      "dataType": "integer",
      "nullable": true,
      "nullCount": 1,
      "avgLength": 1.6,
      "columnName": "age",
      "distinctCount": 3,
      "maxLength": 2,
      "nonNullCount": 4,
      "tableName": "public.customers",
      "avgValue": 31.25,
      "median": 30.0,
      "p90": 45.0,
      "p99": 45.0,
      "minValue": "25",
      "maxValue": "45"
    },
    {
      "mode": "USA",
      "pattern": ".*",
      "dataType": "character varying(3)",
      "nullable": true,
      "nullCount": 0,
      "avgLength": 3.0,
      "columnName": "country",
      "distinctCount": 3,
      "maxLength": 3,
      "nonNullCount": 5,
      "tableName": "public.customers",
      "minValue": "CAN",
      "maxValue": "USA"
    }
  ],
  "values": [
    {
      "value": "25",
      "percent": 0.4000,
      "frequency": 2,
      "columnName": "age"
    },
    {
      "value": "USA",
      "percent": 0.6000,
      "frequency": 3,
      "columnName": "country"
    }
  ],
  "queryMode": "NORMAL",
  "durationMs": 4.32,
  "profiledAt": "2026-06-08T18:00:00Z",
  "tableStats": {
    "rowCount": 5,
    "selected": false,
    "tableName": "public.customers",
    "columnCount": 5
  }
}
```

---

## 🛡️ License
MIT License. Feel free to use and distribute.


jessevent

Packages