Skip to main content
These recipes assume you’ve read Querying the ai schema via PostgREST and have the Service Role (Secret) Key from the Connect modal. Every example uses Accept-Profile: ai (read) or Content-Profile: ai (write) to target the ai schema instead of the default public.
Use the Service Role key from a trusted backend for these recipes. They all touch project-wide data and are not RLS-safe for direct browser access. If you want to expose any of this to end users, gate it behind your own API.

​
Recipe 1 — Custom hybrid search over ai.chunks

The typed POST /api/knowledge-bases/{id}/search runs the platform’s hybrid retriever for you. If you want different filters, different ranking, or to merge results across multiple KBs in one query, drop down to direct PostgREST. This recipe fetches chunks across two KBs, scoped to a single source’s metadata tag, ordered by an extra score column you’ve populated yourself. 
import requests

KB_IDS = ["kb-uuid-1", "kb-uuid-2"]
TAG = "policy"

response = requests.get(
    f"{BASE_URL}/rest/v1/chunks",
    headers={
        **headers,
        "Accept-Profile": "ai",
    },
    params={
        "select": "id,text,score,source_id,meta,knowledge_base_id",
        "knowledge_base_id": f"in.({','.join(KB_IDS)})",
        "meta->>tag": f"eq.{TAG}",
        "order": "score.desc",
        "limit": 20,
    },
)
results = response.json()
for row in results:
    print(f"[KB {row['knowledge_base_id'][:8]}] score={row['score']:.3f}")
    print(row["text"][:200])
Two PostgREST patterns to notice. The in.(...) filter selects across multiple KBs in one query — no application-side fan-out. The meta->>tag=eq.policy selector reaches into the meta JSONB column with the standard Postgres ->> operator; PostgREST honors that syntax in filter expressions.

​
Recipe 2 — Usage analytics on agent_runs

Build a dashboard query that shows, for each agent, the run count, average input/output token counts, and last-run timestamp over the last 7 days. The typed /api/agents doesn’t aggregate; PostgREST + a server-side RPC does it in one round-trip. The simplest path is to define a SQL view or a Postgres function on ai, then call it via POST /rest/v1/rpc/{function}. Here’s the function: 
CREATE OR REPLACE FUNCTION ai.agent_usage_last_7d()
RETURNS TABLE (
  agent_id uuid,
  agent_name text,
  run_count bigint,
  avg_input_tokens numeric,
  avg_output_tokens numeric,
  last_run_at timestamptz
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ai
AS $$
  SELECT
    a.id,
    a.name,
    count(r.id)            AS run_count,
    avg(r.input_tokens)    AS avg_input_tokens,
    avg(r.output_tokens)   AS avg_output_tokens,
    max(r.created_at)      AS last_run_at
  FROM ai.agents a
  LEFT JOIN ai.agent_runs r
    ON r.agent_id = a.id
   AND r.created_at >= now() - interval '7 days'
  GROUP BY a.id, a.name
  ORDER BY run_count DESC;
$$;

GRANT EXECUTE ON FUNCTION ai.agent_usage_last_7d() TO service_role, authenticated;
Call it from your app: 
response = requests.post(
    f"{BASE_URL}/rest/v1/rpc/agent_usage_last_7d",
    headers={**headers, "Accept-Profile": "ai"},
    json={},
)
for row in response.json():
    print(
        f"{row['agent_name']:30s} "
        f"runs={row['run_count']:4d} "
        f"avg_in={row['avg_input_tokens']:8.0f} "
        f"avg_out={row['avg_output_tokens']:8.0f}"
    )
SECURITY DEFINER lets the function run with the owner’s permissions rather than the caller’s — useful here because the aggregate doesn’t need per-user filtering and you want consistent results regardless of which key calls it. Set search_path = ai explicitly to avoid the search-path SECURITY DEFINER pitfall.

​
Recipe 3 — Bulk-tag sources

You imported 200 sources last month and want to attach a {"tag": "q3-2025"} JSONB key to all of them in one round-trip. The typed PATCH /api/sources/{id} is one-source-per-call; PostgREST supports a single filter+update. 
SOURCE_IDS = ["uuid-1", "uuid-2", "..."]  # 200 of these

response = requests.patch(
    f"{BASE_URL}/rest/v1/sources",
    headers={
        **headers,
        "Content-Profile": "ai",
        "Prefer": "return=representation",
    },
    params={"id": f"in.({','.join(SOURCE_IDS)})"},
    json={"meta": {"tag": "q3-2025"}},
)
print(f"Updated {len(response.json())} sources")
Two notes. Prefer: return=representation makes PostgREST return the updated rows so you can confirm the count. Beware: the meta write here replaces the whole JSONB column rather than merging. For partial-update semantics, use jsonb_set via an RPC, or read-modify-write per row.

​
Recipe 4 — Cross-KB analytics with embeds

For a “which sources contribute most to this KB” report, you want each indexed_sources row joined to its sources row (for the name and file type) and a chunk count. PostgREST’s embed syntax does this in one request: 
KB_ID = "kb-uuid"

response = requests.get(
    f"{BASE_URL}/rest/v1/indexed_sources",
    headers={**headers, "Accept-Profile": "ai"},
    params={
        "select": "id,index_status,source:sources(name,file_type),chunks(count)",
        "knowledge_base_id": f"eq.{KB_ID}",
        "order": "chunks.count.desc",
    },
)
for row in response.json():
    s = row["source"]
    n = row["chunks"][0]["count"] if row["chunks"] else 0
    print(f"{s['name']:50s} ({s['file_type']}) — {n} chunks, {row['index_status']}")
The embed syntax source:sources(name,file_type) aliases the joined sources row as source in the response. chunks(count) does a count-only embed — PostgREST recognizes count as a special function and emits [{"count": N}] rather than the rows themselves. You can stack embeds, embed embeds, and filter on them — see the PostgREST reference for the full filter and embed grammar.

​
Next steps

Querying the ai schema

The framing and tables behind these recipes.

RLS Cookbook

Five tightening patterns when “all authenticated users see everything” isn’t what you want.

PostgREST Reference

Filter operators, embeds, headers, and the response/error contract.

Connection Pooling

What changes when you connect through PgBouncer’s transaction-mode pooler instead.