Skip to content

Modularity & Schemas

The primary module boundary across the platform is the PostgreSQL schema.

Instead of splitting services into dozens of microservices with separate databases, we keep a single database partitioned into clean, domain-specific compartments (schemas).

The Mental Model: Vaults vs. Display Counters

  [ Applications & Clients ]
              │
              ▼  (Only talks to API Schemas)
┌────────────────────────────────────────────────────────┐
│ API Schemas (Display Counters)                         │
│   api  │  enterprise_api  │  internal_api              │
└───────┬──────────────┬───────────────┬─────────────────┘
        │              │               │ (Views & RPCs)
        ▼              ▼               ▼
┌────────────────────────────────────────────────────────┐
│ Domain Schemas (Private Storage Vaults)                │
│   identity, accounts, places, devices, invoicing,      │
│   site_maintenance, media, agent_control, etc.         │
└────────────────────────────────────────────────────────┘
  • Domain Schemas are Storage Vaults: Raw tables live here. Direct client access is forbidden by default. Internal columns (e.g. raw payload dumps, internal status flags) remain private.
  • API Schemas are Display Counters: PostgREST only exposes these schemas. They provide clean, stable views and stored procedures (FUNCTION), enforcing security and hiding database refactors from client apps.

Schema Catalog

1. Exposed Contract Schemas

  • api — Public mobile and customer-facing views and RPCs.
  • enterprise_api — Enterprise portal views (maintenance schedules, assets, walkthroughs, work items).
  • internal_api — Private internal views for operations, manufacturing, and billing.

2. Private Domain Schemas

  • identity — User records and external identity provider links.
  • access — Reusable, RLS-safe permission helper functions.
  • accounts — Companies, memberships, invites, and contractor relationships.
  • places — Sites, buildings, zones, and physical hierarchy.
  • devices — Hardware inventory, firmware versions, assignments, and sensor telemetry.
  • projects — Projects, project types, and member assignments.
  • site_maintenance — Preventive maintenance plans, inspection walkthroughs, and repair work items.
  • media — Object-storage metadata, upload intents, and asset image records.
  • invoicing — Vendors, bills, receipts, budgets, and accounting provider integrations.
  • agent_control — AI agent workspaces, conversation turns, and lifecycle tracking.
  • agent_access — Agent permissions, capabilities, and personal access tokens.
  • slack_bot — Automated notification schedules and state.
  • auth — JWT claim extraction helpers for PostgREST.

Rules of Exposure & Coupling

  1. Clients Read Contracts: Web and mobile apps must query views in api, enterprise_api, or internal_api. They never query domain tables directly unless a table is a static lookup or catalog.
  2. Writes Use RPCs for Workflows: Simple single-row CRUD can write to views where supported. Multi-table mutations, state machine transitions, and approvals (e.g. approving a bill, claiming a device) must go through dedicated RPC functions.
  3. Anchor References: Foreign keys across schemas should link only to stable anchor entities (identity.users.id, places.sites.id, accounts.companies.id, projects.projects.id, or core device IDs). For looser connections between domains, use bridge tables (e.g. places.site_members).

Technical Pointers

  • Migrations Directory: infrastructure/db/migrations/
  • Complete Database Schema: infrastructure/db/schema.sql
  • SQL Coding & Contract Standards: docs/database/sql-contracts.md
  • Database Architecture & Schemas Index: docs/database/index.md