Database Naming Conventions

All SQL identifiers follow consistent, predictable naming standards.

Schemas & Tables

Schemas (Singular / Domain Noun):
Contracts: api, enterprise_api, internal_api
Helpers: auth, access
Domains: identity, accounts, places, devices, projects, site_maintenance, media, invoicing, agent_control, agent_access
Tables (Plural Nouns):
identity.users, accounts.companies, places.sites, devices.devices
Join Tables (Name Both Sides):
accounts.company_members, places.site_members, devices.user_devices

Primary Keys: Always named id (UUIDv4 by default, or identity bigint for high-volume append-only telemetry).
Foreign Keys: Named <entity>_id (e.g. user_id, site_id, company_id, device_id).

Auth Helpers (auth.*):
auth.uid() — Returns caller UUID subject (sub).
auth.jwt() — Returns raw JWT payload as jsonb.
auth.role() — Returns the active database role claim.
auth.claim('name') — Returns a named JWT claim.
Access Helpers (access.*):
RLS-safe boolean functions used in policies instead of repetitive joins.
Examples: access.can_read_site(site_id, user_id), access.has_company_role(company_id, user_id, 'owner').

Tier	PostgREST Authenticator	Anonymous Role	Authenticated Role	Application DB Role
Public	`authenticator`	`anon`	`authenticated`	`platform_api_role`
Enterprise	`enterprise_portal_authenticator`	`enterprise_portal_anon`	`enterprise_portal_role`	`enterprise_api_role`
Internal	`internal_postgrest_authenticator`	`internal_anon` (no grants)	`internal_api_role`	`internal_api_role`

Views (Plural Nouns): Client-facing read projections (api.sites, enterprise_api.work_items, internal_api.vendors).
RPC Functions (Verb Phrases): State-changing actions (api.claim_device(...), enterprise_api.decide_work_item(...), internal_api.create_project(...)).