Start with REST

Turn on Vector and Embed, store your text, then search using a sentence. starterpg generates the document and query embeddings for you. You do not need a custom SQL matching function, a separate vector database, or your own embedding-provider integration to use hosted Embed.

Prefer a complete script? Copy the cURL, JavaScript, Python, or Go example.

export PROJECT_URL="https://www.starterpg.com/api/v1/your-project"
export API_KEY="your-server-side-secret-key"

Run this example in a project without a help_articles table. Your key needs schema write, REST write/read and vector read access. Hosted embedding requests use your plan's monthly embedding allowance.

2. Create a table and send text

curl --fail-with-body -X POST "$PROJECT_URL/schema" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "kind": "create_table",
  "name": "help_articles",
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "primaryKey": true
    },
    {
      "name": "title",
      "type": "text",
      "nullable": false
    },
    {
      "name": "category",
      "type": "text",
      "nullable": false
    },
    {
      "name": "published",
      "type": "boolean",
      "default": "false"
    },
    {
      "name": "content",
      "type": "text",
      "nullable": false
    },
    {
      "name": "embedding",
      "type": "vector(768)",
      "nullable": false
    }
  ]
}'

Insert one object or a batch. With one vector column and no explicit embedding value, starterpg embeds content automatically. The original text remains available for display. This creates three rows with 768-dimensional embeddings; you never have to send 768 numbers.

curl --fail-with-body -X POST "$PROJECT_URL/rest/help_articles?embed=true" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '[
  {
    "id": 1,
    "title": "Reset your password",
    "category": "account",
    "published": true,
    "content": "Use Forgot password on the login page to receive a reset link by email."
  },
  {
    "id": 2,
    "title": "Change your plan",
    "category": "billing",
    "published": true,
    "content": "Open Billing in your dashboard to upgrade your subscription or change your plan."
  },
  {
    "id": 3,
    "title": "Invite a teammate",
    "category": "account",
    "published": false,
    "content": "Open your team settings and send an invitation to a colleague by email."
  }
]'

Inserts return HTTP 201 and row data. To avoid returning large embeddings, add -H "Prefer: return=minimal"; a successful write then returns an empty 204 response.

3. Search with a sentence

curl --fail-with-body -X POST "$PROJECT_URL/vector/help_articles?embed=true" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "text": "I cannot sign in because I forgot my password",
  "column": "embedding",
  "metric": "cosine",
  "select": "id,title,content",
  "limit": 3
}'

The query text is embedded automatically, then compared with stored vectors. The response is a JSON array of selected columns plus score, ordered nearest first. The password article is a relevant candidate even though the question does not repeat its title exactly. Scores and exact ordering depend on the model and your content.

// Illustrative response shape, not a guaranteed score or ranking:
[
  {
    "id": 1,
    "title": "Reset your password",
    "content": "Use Forgot password on the login page to receive a reset link by email.",
    "score": 0.82
  }
]

Search only published articles in a category

curl --fail-with-body -X POST "$PROJECT_URL/vector/help_articles?embed=true" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "text": "I cannot sign in because I forgot my password",
  "column": "embedding",
  "metric": "cosine",
  "select": "id,title,content",
  "limit": 3,
  "filters": {
    "category": "eq.account",
    "published": "eq.true"
  }
}'

Filters narrow the candidate rows before nearest-neighbor ranking. Vector filters support eq, neq, gt, gte, lt, lte, like and ilike, using actual column names. Unlike REST reads, they do not support in, logical groups, or embedded joins. A user-supplied filter is not an authorization rule: use RLS with a publishable key and the user's access token for per-user visibility.

Require a minimum cosine score

curl --fail-with-body -X POST "$PROJECT_URL/vector/help_articles?embed=true" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "text": "I cannot sign in because I forgot my password",
  "column": "embedding",
  "metric": "cosine",
  "select": "id,title,content",
  "limit": 3,
  "filters": {
    "category": "eq.account",
    "published": "eq.true"
  },
  "threshold": 0.6
}'

Start without a threshold, inspect the results, then tune it for your dataset. 0.6 is an example, not a universal relevance cutoff or a confidence percentage. The threshold is applied to the top limit candidates, so fewer rows (or []) may be returned. The default limit is 10 and the maximum is 200.

Choose exactly which text gets embedded

curl --fail-with-body -X POST "$PROJECT_URL/rest/help_articles?embed=true" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "id": 4,
  "title": "Update your email",
  "category": "account",
  "published": true,
  "content": "Open account settings and verify your new email address.",
  "embedding": "Change email address. Update account contact details. Verify a new email."
}'

A string in the vector column overrides automatic text selection. Use this for a title plus body, a product description plus category, or a searchable summary. If you omit it and the table has exactly one vector column, the first nonempty field from content, body, text, title, task, nameis used; those fields are not concatenated. With multiple vector columns, explicitly supply text for the columns you want embedded.

Update content and regenerate its embedding

curl --fail-with-body -X PATCH "$PROJECT_URL/rest/help_articles?id=eq.1&embed=true" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "content": "Choose Forgot password on the login page. Open the emailed reset link and choose a new password."
}'

The filter limits the update to article 1. Re-embed whenever its searchable text changes; updating text without the flag leaves the previous embedding unchanged. A metadata-only update needs no new vector and should omit the flag:

curl --fail-with-body -X PATCH "$PROJECT_URL/rest/help_articles?id=eq.3" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "published": true
}'

Repeat an import safely with upsert

curl --fail-with-body -X POST "$PROJECT_URL/rest/help_articles?embed=true&on_conflict=id" \
  -H "apikey: $API_KEY" \
  -H "Prefer: resolution=merge-duplicates,return=minimal" \
  -H "content-type: application/json" \
  -d '[
  {
    "id": 1,
    "title": "Reset your password",
    "category": "account",
    "published": true,
    "content": "Use Forgot password on the login page to receive a reset link by email."
  },
  {
    "id": 2,
    "title": "Change your plan",
    "category": "billing",
    "published": true,
    "content": "Open Billing in your dashboard to upgrade your subscription or change your plan."
  },
  {
    "id": 3,
    "title": "Invite a teammate",
    "category": "account",
    "published": false,
    "content": "Open your team settings and send an invitation to a colleague by email."
  }
]'

Existing IDs are updated rather than duplicated; missing IDs are inserted. This request re-embeds the supplied text, including existing rows, and uses embedding allowance again. Send only changed documents when possible.

Add an index as the collection grows

curl --fail-with-body -X POST "$PROJECT_URL/schema" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "kind": "create_index",
  "table": "help_articles",
  "columns": [
    "embedding"
  ],
  "method": "hnsw",
  "opclass": "vector_cosine_ops"
}'

Search works without an index. HNSW speeds up larger collections using approximate nearest-neighbor search, which can trade recall for speed. The operator class must match the metric you query. Filtered approximate searches may return fewer than the requested number of neighbors.

Use JavaScript fetch from your server

const projectUrl = process.env.PROJECT_URL.replace(/[/]$/, "");
const apiKey = process.env.API_KEY; // server only

async function postJson(resource, body, extraHeaders = {}) {
  const response = await fetch(projectUrl + resource, {
    method: "POST",
    headers: { apikey: apiKey, "content-type": "application/json", ...extraHeaders },
    body: JSON.stringify(body),
  });
  if (!response.ok) throw new Error(await response.text());
  return response.status === 204 ? null : response.json();
}

// The table above must exist and both Vector and Embed must be enabled.
await postJson("/rest/help_articles?embed=true&on_conflict=id", {
  id: 5, title: "Download invoices", category: "billing", published: true,
  content: "Open Billing, then Invoices, to download a PDF receipt.",
}, { Prefer: "resolution=merge-duplicates,return=minimal" });

const results = await postJson("/vector/help_articles?embed=true", {
  text: "Where can I get a receipt for my payment?",
  column: "embedding", metric: "cosine", limit: 5,
  select: "id,title,content", filters: { published: "eq.true" },
});
console.log(results);

Use the results as context for an answer

For retrieval-augmented generation (RAG), run the search above, then pass only the returned content to your chosen chat model. Keep article IDs for citations. starterpg handles retrieval, not answer generation. Treat retrieved text as untrusted source material rather than instructions.

const sources = results.map(({ id, title, content }) => ({ id, title, content }));
// Send these sources and the user's question to your own answer-generation step.
// If sources is empty, say that no matching material was found.
console.log(JSON.stringify(sources, null, 2));

Enable Vector; Embed can stay off. This tiny, deterministic three-dimensional example demonstrates the API without calling an embedding provider. These hand-written vectors are a teaching aid, not real semantic embeddings. For production, replace 3 with your model's output dimension and use that same model for every document and query.

curl --fail-with-body -X POST "$PROJECT_URL/schema" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "kind": "create_table",
  "name": "vector_demo",
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "primaryKey": true
    },
    {
      "name": "label",
      "type": "text"
    },
    {
      "name": "embedding",
      "type": "vector(3)",
      "nullable": false
    }
  ]
}'

curl --fail-with-body -X POST "$PROJECT_URL/rest/vector_demo" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '[
  {
    "id": 1,
    "label": "apple",
    "embedding": "[1,0,0]"
  },
  {
    "id": 2,
    "label": "orange",
    "embedding": "[0.8,0.2,0]"
  },
  {
    "id": 3,
    "label": "car",
    "embedding": "[0,0,1]"
  }
]'

For ordinary REST writes without embed=true, send the vector column in pgvector's text form, such as "[1,0,0]". For a vector search, send a JSON numeric array in vector:

curl --fail-with-body -X POST "$PROJECT_URL/vector/vector_demo" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "column": "embedding",
  "vector": [
    1,
    0,
    0
  ],
  "metric": "cosine",
  "select": "id,label",
  "limit": 3
}'

[
  { "id": 1, "label": "apple", "score": 1 },
  { "id": 2, "label": "orange", "score": 0.9701425 },
  { "id": 3, "label": "car", "score": 0 }
]

Scores above are rounded. Do not send a zero vector for cosine search. Do not use ?embed=true with the three-dimensional table: hosted Embed produces 768 dimensions. In particular, a vector literal string with that flag is treated as text to embed, not as stored numbers.

Choose a distance metric

curl --fail-with-body -X POST "$PROJECT_URL/vector/vector_demo" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "column": "embedding",
  "vector": [
    1,
    0,
    0
  ],
  "metric": "l2",
  "select": "id,label",
  "limit": 3
}'

Every metric orders nearest first, but L2 scores are distances. The current threshold parameter always keeps score >= threshold; it is not a maximum-distance filter. Omit it for L2 nearest-neighbor queries. None of these scores is a probability.

Troubleshooting text and vector search

“Turn on Embed” / no vector column (400)

Enable Vector then Embed in Extensions, create a vector column, and include ?embed=true only when converting text.

Dimension mismatch (400)

Hosted Embed needs vector(768). BYO vectors must match the column's dimension. A model change requires re-embedding stored documents; do not mix embedding spaces.

Permission denied (403) or no rows

Check key scopes and RLS. Publishable keys use the caller's policies; secret keys bypass RLS. Lower a too-strict cosine threshold or remove filters to diagnose relevance issues without weakening access rules.

Embedding quota reached (429)

Hosted inserts, re-embeddings and text searches consume allowance. Upgrade or wait for the allowance to reset, or send your own vectors without the flag. A rate-limit 429 is separate; follow its Retry-After header.

Hosted embed unavailable (503) or provider error (502)

Contact the platform operator. Hosted customers do not need to provide a Gemini key. Self-hosting operators configure GEMINI_API_KEY on the server, never in client requests.

Everyday data recipes

These examples use the quickstart's project URL and server-side secret key. Create this separate demo table first, then run any of the queries.

curl --fail-with-body -X POST "$PROJECT_URL/schema" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "kind": "create_table",
  "name": "demo_tasks",
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "primaryKey": true
    },
    {
      "name": "task",
      "type": "text",
      "nullable": false
    },
    {
      "name": "done",
      "type": "boolean",
      "default": "false"
    },
    {
      "name": "priority",
      "type": "integer",
      "default": "1"
    },
    {
      "name": "due_on",
      "type": "date"
    }
  ]
}'

curl --fail-with-body -X POST "$PROJECT_URL/rest/demo_tasks" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '[
  {
    "id": 1,
    "task": "Write the API guide",
    "done": false,
    "priority": 3,
    "due_on": "2026-10-01"
  },
  {
    "id": 2,
    "task": "Ship search",
    "done": false,
    "priority": 2,
    "due_on": null
  },
  {
    "id": 3,
    "task": "Review the guide",
    "done": true,
    "priority": 1,
    "due_on": null
  }
]'

Filter, sort and paginate

# First page of unfinished tasks; show the Content-Range response header.
curl --fail-with-body -i --get "$PROJECT_URL/rest/demo_tasks" \
  -H "apikey: $API_KEY" -H "Prefer: count=exact" \
  --data-urlencode 'select=id,task,priority' \
  --data-urlencode 'done=eq.false' \
  --data-urlencode 'order=priority.desc,id.asc' \
  --data-urlencode 'limit=1' --data-urlencode 'offset=0'

# Change offset to 1 for the next page. Initial count is Content-Range: 0-0/2.
# Include a unique tie-breaker (id) for stable pagination.

Find text, match a list, or combine conditions

# Case-insensitive substring search.
curl --fail-with-body --get "$PROJECT_URL/rest/demo_tasks" \
  -H "apikey: $API_KEY" --data-urlencode 'task=ilike.%guide%'

# Select several IDs.
curl --fail-with-body --get "$PROJECT_URL/rest/demo_tasks" \
  -H "apikey: $API_KEY" --data-urlencode 'id=in.(1,3)'

# Tasks without a due date.
curl --fail-with-body --get "$PROJECT_URL/rest/demo_tasks" \
  -H "apikey: $API_KEY" --data-urlencode 'due_on=is.null'

# High-priority OR unfinished; separate query parameters are combined with AND.
curl --fail-with-body --get "$PROJECT_URL/rest/demo_tasks" \
  -H "apikey: $API_KEY" --data-urlencode 'or=(priority.gte.3,done.eq.false)'

Update, upsert and delete

curl --fail-with-body -X PATCH "$PROJECT_URL/rest/demo_tasks?id=eq.1" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "done": true
}'

curl --fail-with-body -X POST "$PROJECT_URL/rest/demo_tasks?on_conflict=id" \
  -H "apikey: $API_KEY" \
  -H "Prefer: resolution=merge-duplicates" \
  -H "content-type: application/json" \
  -d '{
  "id": 2,
  "task": "Ship semantic search",
  "priority": 3
}'

# Destructive: delete only task 3 and suppress the response body (204).
curl --fail-with-body -X DELETE "$PROJECT_URL/rest/demo_tasks?id=eq.3" \
  -H "apikey: $API_KEY" -H "Prefer: return=minimal"

PATCH changes only supplied columns. Upsert inserts or merges on a primary-key/unique conflict; supply the complete intended row where defaults matter. Always filter updates and deletes. Bulk POST sends an array of row objects; rows should use a consistent set of fields.

App users and authenticated requests

These are users of your app, not dashboard accounts. Signup and login require a secret key and belong in your server backend. After login, clients can use a publishable key plus the user's access token. Enable the intended access rules on your tables first; signing in does not grant unrestricted database access.

# Server-side signup. Use your own test email and a strong password.
export END_USER_EMAIL="reader@example.com"
export END_USER_PASSWORD="replace-with-a-strong-test-password"

# These shell recipes require jq to safely build/parse JSON.
LOGIN_BODY=$(jq -n --arg email "$END_USER_EMAIL" --arg password "$END_USER_PASSWORD" \
  '{email: $email, password: $password}')

curl --fail-with-body -X POST "$PROJECT_URL/auth/signup" \
  -H "apikey: $API_KEY" -H "content-type: application/json" -d "$LOGIN_BODY"

# Skip signup if the user already exists; duplicate email returns 409.
SESSION=$(curl --fail-with-body -X POST "$PROJECT_URL/auth/login" \
  -H "apikey: $API_KEY" -H "content-type: application/json" -d "$LOGIN_BODY")
ACCESS_TOKEN=$(printf '%s' "$SESSION" | jq -er '.access_token')
REFRESH_TOKEN=$(printf '%s' "$SESSION" | jq -er '.refresh_token')
export PUBLISHABLE_KEY="your-publishable-key"

curl --fail-with-body "$PROJECT_URL/auth/me" \
  -H "apikey: $PUBLISHABLE_KEY" -H "Authorization: Bearer $ACCESS_TOKEN"

To read a table as that user, send these same two headers to /rest/your_table. RLS determines which rows are visible. Keep refresh tokens private and use secure session storage appropriate to your app; do not log tokens or publish the shell output.

Refresh once, then sign out

REFRESH_BODY=$(jq -n --arg token "$REFRESH_TOKEN" '{refresh_token: $token}')
SESSION=$(curl --fail-with-body -X POST "$PROJECT_URL/auth/refresh" \
  -H "apikey: $PUBLISHABLE_KEY" -H "content-type: application/json" -d "$REFRESH_BODY")
ACCESS_TOKEN=$(printf '%s' "$SESSION" | jq -er '.access_token')
REFRESH_TOKEN=$(printf '%s' "$SESSION" | jq -er '.refresh_token')

# Always replace the previous refresh token; rotation is single-use.
LOGOUT_BODY=$(jq -n --arg token "$REFRESH_TOKEN" '{refresh_token: $token}')
curl --fail-with-body -X POST "$PROJECT_URL/auth/logout" \
  -H "apikey: $PUBLISHABLE_KEY" -H "content-type: application/json" -d "$LOGOUT_BODY"
unset SESSION ACCESS_TOKEN REFRESH_TOKEN END_USER_PASSWORD LOGIN_BODY REFRESH_BODY LOGOUT_BODY

Logout returns an empty 204 and revokes the refresh token. An existing stateless access token remains valid until it expires (up to one hour). Reusing an already-rotated refresh token revokes the user's sessions; serialize refresh requests instead of refreshing concurrently.

Upload and download files

These examples run on your server using a secret key with storage access. Do not assume table RLS governs object access; authorize app users in your backend before handing out signed URLs. URLs are temporary access credentials, so share them only with the intended recipient.

curl --fail-with-body -X POST "$PROJECT_URL/storage/buckets" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "name": "demo-files",
  "public": false
}'

# Small upload through the API: raw bytes, not a JSON object.
curl --fail-with-body -X PUT "$PROJECT_URL/storage/objects?bucket=demo-files&path=hello.txt" \
  -H "apikey: $API_KEY" -H "content-type: text/plain" \
  --data-binary 'Hello from starterpg storage!'

curl --fail-with-body "$PROJECT_URL/storage/objects?bucket=demo-files" \
  -H "apikey: $API_KEY"

# Get a download URL (requires jq); fetch it WITHOUT forwarding your API key.
DOWNLOAD_URL=$(curl --fail-with-body \
  "$PROJECT_URL/storage/objects?bucket=demo-files&path=hello.txt" \
  -H "apikey: $API_KEY" | jq -er '.url')
curl --fail-with-body "$DOWNLOAD_URL"

Upload directly using a signed URL

Signed uploads bypass the app's request-body limit (about 4.5 MB on Vercel). Use the same content type when signing and uploading. Confirm the upload afterward so it appears in object listings.

UPLOAD_URL=$(curl --fail-with-body -X POST "$PROJECT_URL/storage/sign" \
  -H "apikey: $API_KEY" -H "content-type: application/json" \
  -d '{"action":"upload","bucket":"demo-files","path":"direct.txt","contentType":"text/plain"}' \
  | jq -er '.url')

# No apikey header: this URL already authorizes the GCS upload.
curl --fail-with-body -X PUT "$UPLOAD_URL" \
  -H "content-type: text/plain" --data-binary 'Uploaded directly to storage'

curl --fail-with-body -X POST "$PROJECT_URL/storage/objects" \
  -H "apikey: $API_KEY" -H "content-type: application/json" \
  -d '{"bucket":"demo-files","path":"direct.txt"}'

# Delete both example objects, then the now-empty logical bucket.
curl --fail-with-body -X DELETE "$PROJECT_URL/storage/objects?bucket=demo-files&path=hello.txt" -H "apikey: $API_KEY"
curl --fail-with-body -X DELETE "$PROJECT_URL/storage/objects?bucket=demo-files&path=direct.txt" -H "apikey: $API_KEY"
curl --fail-with-body -X DELETE "$PROJECT_URL/storage/buckets?name=demo-files" -H "apikey: $API_KEY"

Call a database function

Create this function in your project's SQL editor, then call it over HTTP. Arguments are named JSON properties, not a positional array. No custom function is needed for the built-in vector search examples above.

CREATE OR REPLACE FUNCTION public.add_numbers(a integer, b integer)
RETURNS integer
LANGUAGE sql IMMUTABLE
AS $$ SELECT a + b $$;

curl --fail-with-body -X POST "$PROJECT_URL/rpc/add_numbers" \
  -H "apikey: $API_KEY" \
  -H "content-type: application/json" \
  -d '{
  "a": 7,
  "b": 5
}'

The scalar response is 12. Set-returning functions return an array of rows. The function must be in the exposed public schema and the key must have RPC access. Publishable-key calls also obey database privileges and row-level policies.