Agreements

Each project receives four subschemas prefixed project_{uuid}. Your business tables go into the main schema; never create objects in system schemas.

SchemaOwnerUsage

project_{uuid}youTables, views, business functions

project_{uuid}_authaura-authUsers, sessions, MFA — do not touch

project_{uuid}_storageaura-storageBuckets, objects, policies storage

project_{uuid}_platformplatformFunctions, jobs, cron, secrets

• Plural tables: posts, comments, invoices
• Columns in snake_case: created_at, user_id, last_seen_at
• Primary keys id uuid primary key default gen_random_uuid() — no uuid_generate_v4()
• Timestamps timestamptz, never timestamp naked
• FKs suffixed _id: author_id, tenant_id
• Booleans prefixed is_ or has_
• No column project_id — the schematic is already the insulator

create table posts (
  id            uuid primary key default gen_random_uuid(),
  author_id     uuid not null references users(id) on delete cascade,
  title         text not null,
  is_published  boolean not null default false,
  created_at    timestamptz not null default now()
);

The gateway opens Postgres connections with request.jwt.claims specified. The three helpers below read these claims and can be used anywhere in your policies.

HelperBack

auth.uid()uuid of the current user, NULL if anon

auth.role()text · anon / authenticated / service_role

auth.tenant()uuid of the current tenant (multi-tenant RBAC)

auth.has_role(name)boolean · true if the user has this application role

auth.email()text · user email, read from the JWT

auth.claim(key)jsonb · raw access to a custom claim

Convention: <table>_<scope>_<operation>. Example: posts_own_read, orders_admin_delete. Always specify to aura_authenticated or to aura_anon.

-- ✓ Good: table_scope_op
create policy "posts_own_read"   on posts for select …
create policy "orders_admin_all" on orders for all …

-- ✗ To avoid
create policy "allow_stuff"    -- too vague
create policy "new_policy_42" -- not descriptive

• Name: idx_<table>_<colonnes> or uq_<table>_<colonnes> (unique)
• Always index: FKs, frequent filter/order_by columns, where of RLS policies
• Do not index: Boolean columns with high cardinality low (use partial index), tables < 1000 rows
• pgvector: using hnsw for > 100k vectors, ivfflat otherwise