Supabase Guide

PostgreSQL + pgvector + edge functions. The production backend for semantic-loop.

Project setup

Create a Supabase project at supabase.com
Copy project URL and service role key from Settings > API
Run the migration below in the SQL Editor
Set env vars and go

What is Supabase?

Supabase is an open-source alternative to Firebase. It gives you a PostgreSQL database, a REST API, and edge functions — all hosted. The free tier is enough to run semantic-loop.

Step by step

Go to supabase.com and create a free account.
Click "New project". Pick any name and region. Note the database password (you won't need it for the API, but save it).
Once the project is ready, go to Settings > API.
Copy the Project URL (looks like https://abcdef.supabase.co).
Copy the service_role key (starts with eyJ...). This is a secret — never expose it in client-side code.
Go to the SQL Editor tab and run the migration from the next section.

Why service_role? The service_role key bypasses Row Level Security and can read/write all tables. semantic-loop needs this because it manages items and scores server-side. Never use the anon key for this.

Supabase provides PostgREST over your PostgreSQL database. The SupabaseRpcStore communicates exclusively via:

RPC calls (/rest/v1/rpc/sl_*) for all writes — ensures atomicity
REST queries (/rest/v1/semantic_items?id=eq.xyz) for single-item reads

The pgvector extension enables cosine similarity search directly in PostgreSQL. The sl_match_items RPC function handles vector comparison, filtering, and sorting in a single query.

For multi-tenant deployments, you can prefix table names and RPC functions via SupabaseRpcStoreOptions.

SQL migration

sql/001_init.sql — run in Supabase SQL Editor

-- Enable pgvector
create extension if not exists vector;

-- Items table
create table if not exists public.semantic_items (
  id text primary key,
  tribe text not null,
  kind text not null,
  content text not null,
  embedding vector(1536) not null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  archived_at timestamptz
);

-- Aggregate scores
create table if not exists public.semantic_item_scores (
  item_id text primary key references public.semantic_items(id) on delete cascade,
  attempts integer not null default 0,
  score_sum double precision not null default 0,
  score_avg double precision not null default 0,
  critic_avg double precision not null default 0,
  engagement_avg double precision not null default 0,
  last_score double precision,
  last_critic_score double precision,
  last_engagement_score double precision,
  last_outcome_at timestamptz,
  updated_at timestamptz not null default now()
);

-- Outcome events
create table if not exists public.semantic_outcomes (
  id text primary key,
  item_id text not null references public.semantic_items(id) on delete cascade,
  platform text not null,
  occurred_at timestamptz not null,
  metrics jsonb not null default '{}'::jsonb,
  payload jsonb not null default '{}'::jsonb,
  engagement_score double precision not null,
  critic_score double precision not null,
  final_score double precision not null,
  rationale text,
  tags text[] not null default '{}',
  meta jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

-- Auto-update timestamps
create or replace function public.sl_touch_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

drop trigger if exists semantic_items_touch_updated_at on public.semantic_items;
create trigger semantic_items_touch_updated_at
before update on public.semantic_items
for each row execute function public.sl_touch_updated_at();

drop trigger if exists semantic_item_scores_touch_updated_at on public.semantic_item_scores;
create trigger semantic_item_scores_touch_updated_at
before update on public.semantic_item_scores
for each row execute function public.sl_touch_updated_at();

-- Indexes
create index if not exists semantic_items_tribe_kind_idx on public.semantic_items (tribe, kind);
create index if not exists semantic_outcomes_item_id_idx on public.semantic_outcomes (item_id, occurred_at desc);
create index if not exists semantic_items_embedding_cos_idx
  on public.semantic_items using ivfflat (embedding vector_cosine_ops) with (lists = 100);

-- RPC: upsert item
create or replace function public.sl_upsert_item(
  p_id text, p_tribe text, p_kind text, p_content text,
  p_embedding double precision[],
  p_metadata jsonb default '{}'::jsonb,
  p_created_at timestamptz default now(),
  p_updated_at timestamptz default now(),
  p_archived_at timestamptz default null
) returns void language plpgsql as $$
begin
  insert into public.semantic_items (id, tribe, kind, content, embedding, metadata, created_at, updated_at, archived_at)
  values (p_id, p_tribe, p_kind, p_content, p_embedding::vector, coalesce(p_metadata, '{}'::jsonb), p_created_at, p_updated_at, p_archived_at)
  on conflict (id) do update
  set tribe = excluded.tribe, kind = excluded.kind, content = excluded.content,
      embedding = excluded.embedding, metadata = excluded.metadata,
      updated_at = excluded.updated_at, archived_at = excluded.archived_at;

  insert into public.semantic_item_scores (item_id) values (p_id)
  on conflict (item_id) do nothing;
end;
$$;

-- RPC: vector similarity search
create or replace function public.sl_match_items(
  p_tribe text default null,
  p_kind text default null,
  p_query_embedding double precision[] default null,
  p_limit integer default 8,
  p_min_similarity double precision default 0,
  p_include_archived boolean default false
) returns table (
  id text, tribe text, kind text, content text, metadata jsonb,
  created_at timestamptz, updated_at timestamptz, archived_at timestamptz,
  attempts integer, score_sum double precision, score_avg double precision,
  critic_avg double precision, engagement_avg double precision,
  last_score double precision, last_critic_score double precision,
  last_engagement_score double precision, last_outcome_at timestamptz,
  similarity double precision
) language sql stable as $$
  with ranked as (
    select i.id, i.tribe, i.kind, i.content, i.metadata,
           i.created_at, i.updated_at, i.archived_at,
           coalesce(s.attempts, 0) as attempts,
           coalesce(s.score_sum, 0) as score_sum,
           coalesce(s.score_avg, 0) as score_avg,
           coalesce(s.critic_avg, 0) as critic_avg,
           coalesce(s.engagement_avg, 0) as engagement_avg,
           s.last_score, s.last_critic_score, s.last_engagement_score, s.last_outcome_at,
           case when p_query_embedding is null then 0.5
                else 1 - (i.embedding <=> (p_query_embedding::vector))
           end as similarity
    from public.semantic_items i
    left join public.semantic_item_scores s on s.item_id = i.id
    where (p_tribe is null or i.tribe = p_tribe)
      and (p_kind is null or i.kind = p_kind)
      and (p_include_archived or i.archived_at is null)
  )
  select * from ranked
  where similarity >= coalesce(p_min_similarity, 0)
  order by similarity desc, score_avg desc, attempts asc
  limit greatest(1, coalesce(p_limit, 8));
$$;

-- RPC: record outcome event
create or replace function public.sl_record_outcome(
  p_item_id text, p_event_id text, p_platform text,
  p_occurred_at timestamptz, p_metrics jsonb, p_payload jsonb,
  p_engagement_score double precision, p_critic_score double precision,
  p_final_score double precision,
  p_rationale text default null,
  p_tags text[] default '{}',
  p_meta jsonb default '{}'::jsonb
) returns void language plpgsql as $$
begin
  insert into public.semantic_outcomes
    (id, item_id, platform, occurred_at, metrics, payload, engagement_score,
     critic_score, final_score, rationale, tags, meta)
  values (p_event_id, p_item_id, p_platform, p_occurred_at,
          coalesce(p_metrics, '{}'::jsonb), coalesce(p_payload, '{}'::jsonb),
          p_engagement_score, p_critic_score, p_final_score,
          p_rationale, coalesce(p_tags, '{}'), coalesce(p_meta, '{}'::jsonb))
  on conflict (id) do nothing;
end;
$$;

-- RPC: atomic aggregate update with row-level locking
create or replace function public.sl_apply_outcome(
  p_item_id text, p_occurred_at timestamptz,
  p_engagement_score double precision, p_critic_score double precision,
  p_final_score double precision, p_decay_factor double precision default 0.95
) returns table (
  item_id text, attempts integer, score_sum double precision,
  score_avg double precision, critic_avg double precision,
  engagement_avg double precision, last_score double precision,
  last_critic_score double precision, last_engagement_score double precision,
  last_outcome_at timestamptz, updated_at timestamptz
) language plpgsql as $$
declare
  v_prev public.semantic_item_scores%rowtype;
  v_attempts integer;
  v_score_sum double precision;
  v_score_avg double precision;
  v_critic_avg double precision;
  v_engagement_avg double precision;
begin
  insert into public.semantic_item_scores (item_id)
  values (p_item_id) on conflict (item_id) do nothing;

  select * into v_prev from public.semantic_item_scores
  where semantic_item_scores.item_id = p_item_id for update;

  v_attempts := coalesce(v_prev.attempts, 0) + 1;
  v_score_sum := coalesce(v_prev.score_sum, 0)
    * greatest(0, least(1, coalesce(p_decay_factor, 0.95)))
    + p_final_score;
  v_score_avg := v_score_sum / greatest(1, v_attempts);
  v_critic_avg := ((coalesce(v_prev.critic_avg, 0) * coalesce(v_prev.attempts, 0))
    + p_critic_score) / greatest(1, v_attempts);
  v_engagement_avg := ((coalesce(v_prev.engagement_avg, 0) * coalesce(v_prev.attempts, 0))
    + p_engagement_score) / greatest(1, v_attempts);

  update public.semantic_item_scores
  set attempts = v_attempts,
      score_sum = v_score_sum,
      score_avg = greatest(0, least(1, v_score_avg)),
      critic_avg = greatest(0, least(1, v_critic_avg)),
      engagement_avg = greatest(0, least(1, v_engagement_avg)),
      last_score = p_final_score,
      last_critic_score = p_critic_score,
      last_engagement_score = p_engagement_score,
      last_outcome_at = p_occurred_at,
      updated_at = now()
  where semantic_item_scores.item_id = p_item_id;

  return query select s.item_id, s.attempts, s.score_sum, s.score_avg,
    s.critic_avg, s.engagement_avg, s.last_score, s.last_critic_score,
    s.last_engagement_score, s.last_outcome_at, s.updated_at
  from public.semantic_item_scores s where s.item_id = p_item_id;
end;
$$;

This migration creates everything semantic-loop needs in your Supabase database. Copy the entire block and paste it into the SQL Editor in your Supabase dashboard.

What gets created

Object	Type	Purpose
`semantic_items`	Table	Your content with embedding vectors.
`semantic_item_scores`	Table	Running aggregate scores per item.
`semantic_outcomes`	Table	Raw outcome events (the audit trail).
`sl_upsert_item`	Function	Insert or update an item (idempotent).
`sl_match_items`	Function	Vector similarity search with filtering.
`sl_record_outcome`	Function	Store an outcome event.
`sl_apply_outcome`	Function	Update aggregate scores atomically.

The embedding column

The embedding column uses vector(1536) which matches OpenAI's text-embedding-3-small model. If you use a different model with different dimensions, change the number (e.g., vector(768) for some open-source models).

The full SQL

Copy and paste this into your Supabase SQL Editor:

sql/001_init.sql

create extension if not exists vector;

create table if not exists public.semantic_items (
  id text primary key,
  tribe text not null,
  kind text not null,
  content text not null,
  embedding vector(1536) not null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  archived_at timestamptz
);

create table if not exists public.semantic_item_scores (
  item_id text primary key references public.semantic_items(id) on delete cascade,
  attempts integer not null default 0,
  score_sum double precision not null default 0,
  score_avg double precision not null default 0,
  critic_avg double precision not null default 0,
  engagement_avg double precision not null default 0,
  last_score double precision,
  last_critic_score double precision,
  last_engagement_score double precision,
  last_outcome_at timestamptz,
  updated_at timestamptz not null default now()
);

create table if not exists public.semantic_outcomes (
  id text primary key,
  item_id text not null references public.semantic_items(id) on delete cascade,
  platform text not null,
  occurred_at timestamptz not null,
  metrics jsonb not null default '{}'::jsonb,
  payload jsonb not null default '{}'::jsonb,
  engagement_score double precision not null,
  critic_score double precision not null,
  final_score double precision not null,
  rationale text,
  tags text[] not null default '{}',
  meta jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

Then run the functions and triggers (copy the full migration from the Vibe Coder tab for the complete SQL).

Schema design decisions

Text primary keys — UUIDs as text for portability across runtimes. No serial IDs.
Separate scores table — Avoids bloating the items table with frequently-updated aggregate columns. The scores table is write-heavy, items table is read-heavy.
Outcome append-only — semantic_outcomes is an audit log. Outcomes are never updated or deleted. The on conflict do nothing ensures idempotent replays.
IVFFlat index — lists = 100 is a reasonable default for up to ~100k items. For larger datasets, increase lists or consider HNSW: using hnsw (embedding vector_cosine_ops).
Row-level locking — sl_apply_outcome uses SELECT ... FOR UPDATE to prevent race conditions when concurrent edge function invocations update the same item's scores.

sl_match_items internals

The function uses 1 - (embedding <=> query::vector) for cosine distance-to-similarity conversion. The <=> operator returns cosine distance (0 = identical, 2 = opposite), so 1 - distance maps to [-1, 1] similarity. When no query vector is provided, similarity defaults to 0.5.

Sorting order: similarity desc, score_avg desc, attempts asc. This ensures that among equally-similar items, proven performers rank higher, and among those, less-tested items get priority.

sl_apply_outcome atomicity

The decay-weighted update is performed in a single transaction with row-level locking:

Ensure the scores row exists (INSERT ... ON CONFLICT DO NOTHING)
Lock the row (SELECT ... FOR UPDATE)
Compute new values with decay
Update in place
Return the updated row

This prevents lost updates when multiple webhooks fire simultaneously for the same item.

Environment variables

.env

SUPABASE_URL=https://your-project.supabase.co
SUPABASE_SERVICE_ROLE_KEY=eyJ...
OPENAI_API_KEY=sk-...  # optional

Create a .env file in your project root with these values from your Supabase dashboard:

.env

SUPABASE_URL=https://your-project.supabase.co
SUPABASE_SERVICE_ROLE_KEY=eyJ...
OPENAI_API_KEY=sk-...  # only if using embedding: "openai"

Deno reads .env files automatically with --env flag or when deployed to Deno Deploy / Supabase Edge Functions (set via dashboard).

Security: The service_role key has full database access. Never commit it to version control. Add .env to your .gitignore.

The string shorthands ("supabase", "openai") call Deno.env.get() internally. On Supabase Edge Functions, SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY are automatically available without any configuration.

For explicit config (no env dependency):

no env vars needed

const loop = createLoop({
  store: {
    provider: "supabase",
    url: "https://your-project.supabase.co",
    serviceRoleKey: "eyJ...",
  },
  embedding: {
    provider: "openai",
    apiKey: "sk-...",
  },
});

Edge function pattern

supabase/functions/ingest/index.ts

import { createLoop } from "@semantic-loop/core";
import { verifyHmacSignature, readJson, json, methodNotAllowed } from "@semantic-loop/core/runtime/edge";

const loop = createLoop({ store: "supabase", embedding: "openai" });

Deno.serve(async (req) => {
  if (req.method !== "POST") return methodNotAllowed(["POST"]);

  const body = await req.text();
  const sig = req.headers.get("x-webhook-signature") ?? "";
  const valid = await verifyHmacSignature({
    body, signature: sig, secret: Deno.env.get("WEBHOOK_SECRET")!,
  });
  if (!valid) return json({ error: "invalid signature" }, 401);

  const { itemId, platform, metrics } = JSON.parse(body);
  const result = await loop.ingest(itemId, platform, metrics);

  return json({ finalScore: result.finalScore, tags: result.critic.tags });
});

An edge function is a serverless function that runs close to your users. Supabase Edge Functions run on Deno Deploy. Here's the pattern for receiving webhook events and feeding them into the loop:

supabase/functions/ingest/index.ts

import { createLoop } from "@semantic-loop/core";
import { verifyHmacSignature, readJson, json, methodNotAllowed } from "@semantic-loop/core/runtime/edge";

// Create the loop once — it's reused across requests
const loop = createLoop({ store: "supabase", embedding: "openai" });

// Handle incoming webhooks
Deno.serve(async (req) => {
  // Only accept POST
  if (req.method !== "POST") return methodNotAllowed(["POST"]);

  // Verify the webhook signature
  const body = await req.text();
  const sig = req.headers.get("x-webhook-signature") ?? "";
  const valid = await verifyHmacSignature({
    body,
    signature: sig,
    secret: Deno.env.get("WEBHOOK_SECRET")!,
  });
  if (!valid) return json({ error: "invalid signature" }, 401);

  // Parse the webhook payload and ingest
  const { itemId, platform, metrics } = JSON.parse(body);
  const result = await loop.ingest(itemId, platform, metrics);

  // Return fast
  return json({ finalScore: result.finalScore, tags: result.critic.tags });
});

The pattern is: verify the webhook, ingest the outcome, return fast. The loop and database handle everything else.

How to deploy

Install the Supabase CLI: npm install -g supabase
Create the function: supabase functions new ingest
Paste the code into supabase/functions/ingest/index.ts
Set secrets: supabase secrets set WEBHOOK_SECRET=your-secret OPENAI_API_KEY=sk-...
Deploy: supabase functions deploy ingest

Architecture pattern: stateless edge, stateful database.

deployment topology

// Request flow:
// platform webhook → edge function → verify → ingest → database → response
//
// The edge function holds no state. The loop instance is created
// at module scope for connection reuse, but it's stateless —
// all durable state is in PostgreSQL.
//
// For high-throughput scenarios:
// - sl_apply_outcome uses FOR UPDATE locking — safe for concurrent writes
// - sl_record_outcome uses ON CONFLICT DO NOTHING — safe for replays
// - The only bottleneck is pgvector indexing during batch upserts

For a select endpoint (e.g., returning the best content for an API call):

supabase/functions/select/index.ts

import { createLoop } from "@semantic-loop/core";
import { readJson, json, methodNotAllowed } from "@semantic-loop/core/runtime/edge";

const loop = createLoop({ store: "supabase", embedding: "openai" });

Deno.serve(async (req) => {
  if (req.method !== "POST") return methodNotAllowed(["POST"]);

  const { query, tribe, kind } = await readJson<{
    query?: string;
    tribe?: string;
    kind?: string;
  }>(req);

  const pick = await loop.select(query, { tribe, kind });

  return json({
    content: pick.candidate.item.content,
    id: pick.candidate.item.id,
    strategy: pick.strategy,
    score: pick.weightedScore,
  });
});

HMAC webhook verification

verify a webhook

import { verifyHmacSignature } from "@semantic-loop/core/runtime/edge";

const valid = await verifyHmacSignature({
  body: rawBodyString,
  signature: req.headers.get("x-webhook-signature") ?? "",
  secret: Deno.env.get("WEBHOOK_SECRET")!,
  algorithm: "SHA-256",  // default, also supports SHA-384, SHA-512
});

When a platform sends you a webhook (e.g., Instagram notifying you about post engagement), you need to verify it's actually from them and not a malicious request.

HMAC verification works like this:

You and the platform share a secret key.
The platform signs the webhook body with the secret using HMAC-SHA256.
They send the signature in a header (e.g., x-webhook-signature).
You compute the same signature and compare. If they match, the request is authentic.

usage

import { verifyHmacSignature } from "@semantic-loop/core/runtime/edge";

const body = await req.text();
const signature = req.headers.get("x-webhook-signature") ?? "";

const valid = await verifyHmacSignature({
  body,            // the raw request body as a string
  signature,       // the signature from the header
  secret: "your-shared-secret",
});

if (!valid) {
  return new Response("Unauthorized", { status: 401 });
}

The implementation uses crypto.subtle (Web Crypto API) for HMAC computation. Key details:

Supports SHA-256 (default), SHA-384, and SHA-512
Uses timing-safe comparison — constant-time string equality to prevent timing attacks
Automatically strips sha256= prefix from signatures (GitHub-style)
Signature is normalized to lowercase hex before comparison

edge runtime helpers

// All exported from "@semantic-loop/core/runtime/edge"

verifyHmacSignature(input: HmacVerificationInput): Promise<boolean>
readJson<T>(request: Request): Promise<T>
json(data: JsonValue, status?: number, headers?: HeadersInit): Response
methodNotAllowed(methods: readonly string[]): Response
toHex(bytes: Uint8Array): string
timingSafeEqual(left: string, right: string): boolean

The timingSafeEqual function uses XOR comparison and returns false early only on length mismatch (length itself is not secret). For same-length strings, it always iterates the full length.

Note: readJson() is a typed wrapper around request.json(). json() creates a Response with JSON content type. methodNotAllowed() returns a 405 with the allowed methods listed.