Skip to content

PostgREST API Conventions

PostgREST provides a high-performance HTTP data layer directly on top of PostgreSQL views and stored procedures.

The Three PostgREST Surfaces

The platform runs three isolated PostgREST instances behind the gateway:

Surface Schema (PGRST_DB_SCHEMAS) Authenticator Role Allowed Roles
Public api authenticator anon, authenticated, service_role
Enterprise enterprise_api enterprise_portal_authenticator enterprise_portal_anon, enterprise_portal_role
Internal internal_api internal_postgrest_authenticator internal_anon (no grants), internal_api_role

Each instance uses a dedicated login role that is authorized to SET ROLE only to its respective tier's application roles. Public tokens cannot switch roles on internal or enterprise instances.

Contract Pattern: Views over Storage

  1. Keep Storage Private: Domain tables (invoicing.*, site_maintenance.*, devices.*) are never included in PGRST_DB_SCHEMAS.
  2. Expose Stable Views: Client read models are exposed as security-invoker views: sql CREATE VIEW api.device_status WITH (security_invoker = true) AS SELECT d.id, d.serial_number, ud.user_id, ud.name FROM devices.devices d JOIN devices.user_devices ud ON ud.device_id = d.id WHERE ud.deleted_at IS NULL;
  3. Security Invoker: Views must specify WITH (security_invoker = true) so PostgreSQL evaluates the underlying tables' Row-Level Security (RLS) policies against the caller's JWT claims (auth.uid()).

Grants & RPC Functions

  • Revoke Before Grant: Every function must revoke execution from PUBLIC before granting to authorized roles (REVOKE EXECUTE ON FUNCTION ... FROM PUBLIC;).
  • Strong Typing: Avoid ad-hoc RETURNS jsonb. Return scalar primitives (uuid, boolean), composite types, or RETURNS TABLE(...) (with #variable_conflict use_column).
  • OpenAPI Documentation: PostgREST automatically extracts comments into its OpenAPI specification. Every exposed view and function requires a COMMENT ON statement: sql COMMENT ON FUNCTION internal_api.archive_vendor(uuid) IS 'Soft-deletes a vendor and revokes active compliance approvals.';