Agreements
Naming of schemas, columns, RLS policies, auth helpers, indexes. Stable, documented conventions so your team has one way to name things.
One prefix per domain#
Each project receives four subschemas prefixed project_{uuid}. Your business tables go into the main schema; never create objects in system schemas.
| Schema | Owner | Use |
|---|---|---|
project_{uuid} | you | Tables, views and business functions. This is the schema your unqualified SQL (create table posts …) lands in; it is named public only on the SQL you export with aura db backup download and schema/dump, which are portable. |
project_{uuid}_auth | aura-auth | Users, sessions and MFA. Do not modify. |
project_{uuid}_storage | aura-storage | Buckets, objects and storage policies |
project_{uuid}_platform | platform | Functions, jobs, cron and secrets |
snake_case, plural, id uuid#
- 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()— nouuid_generate_v4(). Preferuuidoverbigint generated always as identitywhen ids are exposed to clients (not guessable, no 2^53 precision issue in JavaScript); an identity column works too, and backups and restores handle it. - Timestamps
timestamptz, nevertimestampnaked - FKs suffixed
_id:author_id,tenant_id - Booleans prefixed
is_orhas_ - No column
project_id— the schematic is already the insulator
sql
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()
);
Attention
Never add a project_id column. Isolation already comes from the schema, and such a column would add indexes without adding security.
Three functions injected into each session#
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.
auth.uid()— uuid of the current user, NULL if anonauth.role()— text · anon / authenticated / service_roleauth.tenant()— uuid of the current tenant (multi-tenant RBAC)auth.has_role(name)— boolean · true if the user has this application roleauth.email()— text · user email, read from the JWTauth.claim(key)— jsonb · raw access to a custom claim
A predictable name, an operation, a role#
Convention: <table>_<scope>_<operation>. Example: posts_own_read, orders_admin_delete. Always specify to aura_authenticated or to aura_anon.
sql
-- ✓ 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
Naming and when to index#
- Name:
idx_<table>_<colonnes>oruq_<table>_<colonnes>(unique) - Always index: FKs, frequent
filter/order_bycolumns,whereof RLS policies - Do not index: Boolean columns with high cardinality low (use partial index), tables < 1000 rows
- pgvector:
using hnswfor > 100k vectors,ivfflatotherwise