Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt

Use this file to discover all available pages before exploring further.

This document is the architecture-level entry point for the EncoreOS Supabase/Postgres schema. For generated inventory, relationship, and security snapshots, use the docs in docs/database/: Environment-specific hosted snapshots are generated as SCHEMA_CATALOG.DEV.md, SCHEMA_RELATIONSHIPS.DEV.md, SCHEMA_SECURITY_RLS.DEV.md, and matching PROD files after npm run db:docs:refresh:all.

​
Purpose

  • Define schema ownership and guardrails at architecture level.
  • Point to generated schema artifacts that are refreshed from tooling.
  • Avoid stale hand-maintained table inventories in architecture docs.

​
Source of Truth

  • Runtime schema source: Supabase Postgres (local/dev/prod environments).
  • Versioned DDL source: supabase/migrations/*.sql.
  • Declarative schema workspace: supabase/schemas/**/*.sql (audit/authoring surface used to generate migrations; not a direct production apply path).
  • Generated schema docs: docs/database/SCHEMA_*.md, produced by npm run db:docs:generate or npm run db:docs:refresh:all.

​
Promotion strategy

EncoreOS uses a hybrid migration strategy:
  • Promotion vehicle: migrations are the only artifact promoted to hosted environments.
  • Declarative visibility: supabase/schemas/ provides a desired-state view and helps catch drift, but does not replace migration review and CI gates.
  • Hosted accuracy: prefer environment-specific docs (SCHEMA_*.DEV.md / SCHEMA_*.PROD.md) when validating parity or investigating runtime drift.

​
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/doc refresh:
  • npm run db:check:rls-initplan
  • npm run db:check:fk-indexes
  • npm run db:check:topo
  • npm run db:schemas:lint

​
Documentation Maintenance

Do not manually edit generated schema inventory files. Instead:
  1. Refresh source artifacts (db:catalog:export and/or migration inventory fallback).
  2. Run npm run db:docs:generate.
  3. Review generated diffs for unexpected churn.
  4. Run validation checks listed in the workflow doc.
For hosted dev/prod accuracy, use: 
npm run db:docs:refresh:all
npm run db:docs:check:all
For operational details, see SCHEMA_DOCUMENTATION_WORKFLOW.md and docs/development/supabase/DECLARATIVE_SCHEMA_GUIDE.md.