Skip to content
Aurabase Logo
aurabase

Agreements

Naming of schemas, columns, RLS policies, auth helpers, indexes. Stable, documented conventions so your team has one way to name things.

Reference6 min readLevel beginnerRevised on Apr 15, 2026

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.

SchemaOwnerUse
project_{uuid}youTables, 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}_authaura-authUsers, sessions and MFA. Do not modify.
project_{uuid}_storageaura-storageBuckets, objects and storage policies
project_{uuid}_platformplatformFunctions, 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() — no uuid_generate_v4(). Prefer uuid over bigint generated always as identity when 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, 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
exemplesql
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 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

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.

namingsql
-- ✓ 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> 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

To go further#