> ## 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.

# Encore Health OS Database Schema

> This document is the architecture-level entry point for the EncoreOS Supabase/Postgres schema.

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/`](../database/):

* [`SCHEMA_CATALOG.md`](../database/SCHEMA_CATALOG.md)
* [`SCHEMA_RELATIONSHIPS.md`](../database/SCHEMA_RELATIONSHIPS.md)
* [`SCHEMA_SECURITY_RLS.md`](../database/SCHEMA_SECURITY_RLS.md)
* [`SCHEMA_DOCUMENTATION_WORKFLOW.md`](../database/SCHEMA_DOCUMENTATION_WORKFLOW.md)

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:

```bash theme={null}
npm run db:docs:refresh:all
npm run db:docs:check:all
```

For operational details, see [`SCHEMA_DOCUMENTATION_WORKFLOW.md`](../database/SCHEMA_DOCUMENTATION_WORKFLOW.md) and [`docs/development/supabase/DECLARATIVE_SCHEMA_GUIDE.md`](../development/supabase/DECLARATIVE_SCHEMA_GUIDE.md).