> ## Documentation Index
> Fetch the complete documentation index at: https://docs.powabase.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# ai schema recipes

> Four worked examples that use PostgREST directly against ai.* tables: custom hybrid search, usage analytics, bulk tagging, cross-KB joins.

These recipes assume you've read [Querying the ai schema via PostgREST](/concepts/ai-schema-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`.

<Note>
  **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.
</Note>

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

<CodeGroup>
  ```python Python theme={null}
  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])
  ```

  ```typescript TypeScript theme={null}
  const kbIds = ["kb-uuid-1", "kb-uuid-2"];
  const tag = "policy";

  const params = new URLSearchParams({
    select: "id,text,score,source_id,meta,knowledge_base_id",
    knowledge_base_id: `in.(${kbIds.join(",")})`,
    "meta->>tag": `eq.${tag}`,
    order: "score.desc",
    limit: "20",
  });

  const res = await fetch(`${BASE_URL}/rest/v1/chunks?${params}`, {
    headers: { ...headers, "Accept-Profile": "ai" },
  });
  const results = await res.json();
  for (const row of results) {
    console.log(`[KB ${row.knowledge_base_id.slice(0, 8)}] score=${row.score.toFixed(3)}`);
    console.log(row.text.slice(0, 200));
  }
  ```

  ```bash cURL theme={null}
  curl -G '{BASE_URL}/rest/v1/chunks' \
    -H "Accept-Profile: ai" \
    -H "apikey: {API_KEY}" \
    -H "Authorization: Bearer {API_KEY}" \
    --data-urlencode 'select=id,text,score,source_id,meta,knowledge_base_id' \
    --data-urlencode 'knowledge_base_id=in.(kb-uuid-1,kb-uuid-2)' \
    --data-urlencode 'meta->>tag=eq.policy' \
    --data-urlencode 'order=score.desc' \
    --data-urlencode 'limit=20'
  ```
</CodeGroup>

Two PostgREST patterns to notice. The `in.(...)` filter selects across multiple KBs in one query, with 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:

```sql theme={null}
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:

<CodeGroup>
  ```python Python theme={null}
  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}"
      )
  ```

  ```typescript TypeScript theme={null}
  const res = await fetch(`${BASE_URL}/rest/v1/rpc/agent_usage_last_7d`, {
    method: "POST",
    headers: { ...headers, "Accept-Profile": "ai" },
    body: "{}",
  });
  const rows = await res.json();
  for (const row of rows) {
    console.log(
      `${row.agent_name.padEnd(30)} runs=${String(row.run_count).padStart(4)} ` +
      `avg_in=${row.avg_input_tokens?.toFixed(0).padStart(8)} ` +
      `avg_out=${row.avg_output_tokens?.toFixed(0).padStart(8)}`
    );
  }
  ```

  ```bash cURL theme={null}
  curl -X POST '{BASE_URL}/rest/v1/rpc/agent_usage_last_7d' \
    -H "Accept-Profile: ai" \
    -H "apikey: {API_KEY}" \
    -H "Authorization: Bearer {API_KEY}" \
    -H "Content-Type: application/json" \
    -d '{}'
  ```
</CodeGroup>

`SECURITY DEFINER` runs the function with the owner's permissions rather than the caller's. That fits here: the aggregate doesn't need per-user filtering, and you want the same results regardless of which key calls it. Set `search_path = ai` explicitly to avoid the [search-path SECURITY DEFINER pitfall](https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY).

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

<CodeGroup>
  ```python Python theme={null}
  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")
  ```

  ```typescript TypeScript theme={null}
  const sourceIds = ["uuid-1", "uuid-2" /* ... */];

  const res = await fetch(
    `${BASE_URL}/rest/v1/sources?id=in.(${sourceIds.join(",")})`,
    {
      method: "PATCH",
      headers: {
        ...headers,
        "Content-Profile": "ai",
        Prefer: "return=representation",
      },
      body: JSON.stringify({ meta: { tag: "q3-2025" } }),
    },
  );
  const updated = await res.json();
  console.log(`Updated ${updated.length} sources`);
  ```

  ```bash cURL theme={null}
  curl -X PATCH '{BASE_URL}/rest/v1/sources?id=in.(uuid-1,uuid-2,...)' \
    -H "Content-Profile: ai" \
    -H "apikey: {API_KEY}" \
    -H "Authorization: Bearer {API_KEY}" \
    -H "Content-Type: application/json" \
    -H "Prefer: return=representation" \
    -d '{"meta": {"tag": "q3-2025"}}'
  ```
</CodeGroup>

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) plus a chunk count. PostgREST's embed syntax does this in one request:

<CodeGroup>
  ```python Python theme={null}
  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']}")
  ```

  ```typescript TypeScript theme={null}
  const kbId = "kb-uuid";

  const params = new URLSearchParams({
    select: "id,index_status,source:sources(name,file_type),chunks(count)",
    knowledge_base_id: `eq.${kbId}`,
    order: "chunks.count.desc",
  });

  const res = await fetch(`${BASE_URL}/rest/v1/indexed_sources?${params}`, {
    headers: { ...headers, "Accept-Profile": "ai" },
  });
  for (const row of await res.json()) {
    const s = row.source;
    const n = row.chunks?.[0]?.count ?? 0;
    console.log(`${s.name.padEnd(50)} (${s.file_type}) — ${n} chunks, ${row.index_status}`);
  }
  ```

  ```bash cURL theme={null}
  curl -G '{BASE_URL}/rest/v1/indexed_sources' \
    -H "Accept-Profile: ai" \
    -H "apikey: {API_KEY}" \
    -H "Authorization: Bearer {API_KEY}" \
    --data-urlencode 'select=id,index_status,source:sources(name,file_type),chunks(count)' \
    --data-urlencode 'knowledge_base_id=eq.kb-uuid' \
    --data-urlencode 'order=chunks.count.desc'
  ```
</CodeGroup>

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](/api-reference/postgrest) for the full filter and embed grammar.

## Next steps

<CardGroup cols={2}>
  <Card title="Querying the ai schema" href="/concepts/ai-schema-postgrest">
    The framing and tables behind these recipes.
  </Card>

  <Card title="RLS Cookbook" href="/guides/rls-policies">
    Five tightening patterns when "all authenticated users see everything" isn't what you want.
  </Card>

  <Card title="PostgREST Reference" href="/api-reference/postgrest">
    Filter operators, embeds, headers, and the response/error contract.
  </Card>

  <Card title="Connection Pooling" href="/guides/connection-pooling">
    What changes when you connect through PgBouncer's transaction-mode pooler instead.
  </Card>
</CardGroup>