Skip to content

Database Architecture & Schemas

The database layer runs on PostgreSQL 18.4 and uses schema-based modularity.

Tables are split between private domain storage schemas (where business data lives) and exposed API schemas (which PostgREST queries directly).

Core Principles

  1. Storage vs. Contract: Domain tables (devices, accounts, places, invoicing, etc.) are private vaults. Web and mobile applications read and write through exposed contract schemas (api, enterprise_api, internal_api).
  2. Row-Level Security (RLS): Every domain table enforces declarative security policies. Caller identity is resolved per-request via Keycloak JWT claims (auth.uid(), auth.role()).
  3. Dual Transports: PostgREST handles data reads through security-invoker views. Complex multi-step writes, file uploads, and background workers run via FastAPI.

Database Documentation Map

Architecture & Standards

  • SQL Contracts & Standards: Canonical design rules: return types, #variable_conflict use_column, privilege revocation, and schema health linting.
  • Migrations Guide: Managing migrations with dbmate, production vs. development migrations, and gateway-shared baselines.
  • PostgREST Conventions: How PostgREST maps HTTP requests to views, OpenAPI comments, and authenticator role switching.
  • Naming Conventions: Universal naming standards for schemas, tables, foreign keys, indexes, and RLS policies.

Domain Schema Guides