Skip to main content
This document is the architecture-level entry point for the EncoreOS Supabase/Postgres schema. For the schema itself, use:

​
Purpose

  • Define schema ownership and guardrails at architecture level.
  • Point to the declarative schema source of truth and ERDs.
  • Avoid stale hand-maintained table inventories in architecture docs.

​
Source of Truth

  • Canonical declarative DDL: supabase/schemas/**/*.sql (desired final state; not currently populated — declarative export is deferred per the 2026-06-09 pg-delta scaling spike; migrations are the operative source of truth until a scalable backfill lands).
  • Promotion vehicle: supabase/migrations/*.sql — the baseline (00000000000000_baseline_2026_05_28_ddl.sql + …_data.sql) plus forward, db diff-generated migrations. Migrations are the only artifact applied to hosted environments.
  • Runtime schema: Supabase Postgres — live projects are dev2 (anuwknikgsijbameytzr) and prod2 (aximlomrwfuhctxjhrsf).
  • Generated TypeScript types: src/integrations/supabase/types.ts, regenerated via npm run db:types (local) / npm run db:types:dev2; CI runs npm run db:types:check.

​
Promotion strategy

EncoreOS uses a declarative-first strategy: author DDL in supabase/schemas/**, generate migrations with supabase db diff, review, and apply via supabase db push. supabase/schemas/ is the canonical desired-state; migrations remain the reviewed, CI-gated promotion vehicle.

​
Core Schema Boundaries

Table naming follows {core}_{entity} where core prefixes are:
  • pf, ce, rh, fa, hr, gr, fw, fm, lo, it, cl, pm
Architecture constraints:
  • Cores depend on Platform Foundation patterns, not direct core-to-core coupling.
  • Tenant isolation must be enforced by schema design plus RLS policy design.
  • Cross-core links must follow constitution architecture boundaries.

​
Security and Tenant Isolation

All schema work must preserve constitution requirements:
  • Tenant scoping (organization_id, and site_id where applicable).
  • RLS on business tables in exposed schemas.
  • UPDATE policies include both USING and WITH CHECK.
  • SECURITY DEFINER helpers use safe search_path and prevent recursive RLS patterns.
Use these checks during schema work:
  • npm run db:check:rls-initplan
  • npm run db:check:fk-indexes
  • npm run db:schemas:lint

​
Documentation Maintenance

The schema is documented by the declarative supabase/schemas/** tree and the per-core ERDs — not by hand-maintained inventory files. To change the schema:
  1. Edit supabase/schemas/**/*.sql (desired state).
  2. Generate a migration: npm run db:schemas:diff -- -f <name>.
  3. Apply locally (supabase db reset) and regenerate types (npm run db:types).
  4. Review the migration + type diff before promotion.
See DECLARATIVE_SCHEMA_GUIDE.md for the full workflow.