Extensions

AnvilBase runs a Postgres image with a curated extension set already installed at the cluster level. You enable the ones you want per project database with CREATE EXTENSION, then use them like any Postgres feature.

Listing what’s available / installed

anvilbase schema extensions --project <id>

curl http://localhost:39001/api/v1/projects/<id>/schema/extensions \
  -H "Authorization: Bearer $ANVILBASE_TOKEN" | jq .

The bundled set

Extension	What it gives you	Guide
uuid-ossp	uuid_generate_v4() and friends	below
pgcrypto	column encryption, hashing, gen_random_uuid()	below
pgvector	vector columns + similarity search for embeddings	Vector Search
pgvectorscale	StreamingDiskANN index for fast, large-scale vector search	Vector Search
pg_graphql	GraphQL over your schema, exposed at POST /graphql/v1	GraphQL API
pgmq	durable, transactional message queues (driven via the Queues API / public.pgmq_* wrappers — not create extension / bare pgmq.*)	Queues
pg_net	make outbound HTTP requests from SQL/triggers	below
dblink	cross-database SQL calls (powers the database-webhook bridge)	Webhooks
pg_cron	run SQL on a cron schedule (platform-managed)	Cron & Scheduling

No pgsodium / Supabase vault extension. AnvilBase’s Postgres image does not bundle pgsodium, so create extension pgsodium and Supabase’s pgsodium-backed vault.create_secret() / vault.decrypted_secrets are not available. Use the app-managed Secrets Vault (pgcrypto pgp_sym_encrypt, keyed on CONTROL_PLANE_SECRET) instead.

Enabling an extension

Run via the SQL Editor or the exec_sql RPC (service_role):

create extension if not exists vector;
create extension if not exists pgcrypto;

Most projects need nothing here — gen_random_uuid() (from pgcrypto) and the basics are typically already enabled by provisioning.

pg_cron is platform-managed — don’t create extension it yourself. pg_cron’s scheduler runs in a single database (the platform DB), so it can’t be installed per project. Schedule recurring jobs through the Cron data-plane API / SDK instead; the platform runs them inside your project DB for you. If you try anyway, exec_sql returns a clear “pg_cron is platform-managed — use the Cron data-plane API” error rather than a raw Postgres message.

pgmq is driver-managed — don’t create extension pgmq or call pgmq.* directly. The extension ships preinstalled in every project DB but is owned by a non-superuser role, so create extension pgmq and bare pgmq.create()/pgmq.send() fail (must be owner of extension pgmq / permission denied for schema pgmq). Use the Queues data-plane API / SDK or the service_role-granted public.pgmq_* wrappers. See Queues.

Common patterns

UUID primary keys

create table widgets (
  id uuid primary key default gen_random_uuid(),
  name text not null
);

gen_random_uuid() ships with pgcrypto (and core Postgres 13+). The Schema API uses it as the default PK pattern.

Outbound HTTP from the database (pg_net)

pg_net lets a trigger or function call an external URL asynchronously — useful for fan-out that you don’t want to route through the webhooks service:

select net.http_post(
  url     := 'https://example.com/ingest',
  headers := '{"Content-Type":"application/json"}'::jsonb,
  body    := jsonb_build_object('event','row_inserted','id', new.id)
);

For most event-driven needs, prefer first-class Webhooks, which add signing, retries, and a delivery log.

Column encryption (pgcrypto)

-- encrypt at write
update profiles
set ssn_enc = pgp_sym_encrypt(ssn, current_setting('app.enc_key'))
where id = $1;

-- decrypt at read (service_role / trusted path)
select pgp_sym_decrypt(ssn_enc, current_setting('app.enc_key')) from profiles;

For application secrets (API keys, tokens), use the managed Secrets Vault instead of rolling your own.

Scheduled jobs (pg_cron)

Cron is platform-managed: schedule jobs through the data-plane API / SDK with a service_role key — the platform runs each job inside your project DB.

// SERVER-SIDE (service_role)
await client.cron.scheduleJob(
  "nightly_cleanup",
  "0 3 * * *",
  "delete from sessions where expires_at < now()",
);

See Cron & Scheduling for schedule syntax, run history, isolation, and the platform’s own scheduled jobs.

A note on availability

Extension availability depends on the Postgres image the deployment runs. The set above is what ships by default. AnvilBase’s Postgres image is built FROM postgres:15-bookworm (vanilla OSS + PGDG apt, no third-party Postgres base; see Upgrades → Postgres image), which bundles and enables pg_cron in the platform DB, so cron + the webhook dead-letter pruning job work out of the box. It also ships pgvectorscale (compiled into the image) and auto-enables it per project, so the diskann vector index is available with no extra setup. Operators running their own stock Postgres without these extensions still apply all migrations — pg_cron-dependent features (e.g. DLQ auto-purge) degrade to the manual trigger, and vector search falls back to pgvector’s hnsw/ivfflat indexes.

pg_graphql is exposed over HTTP at POST /graphql/v1 — see the GraphQL API. It resolves under the same per-request RLS context as the REST API, so the same policies apply.

Next: Vector Search.