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

# Schema Documentation Workflow

> This document defines the repeatable workflow for generating and validating EncoreOS schema documentation.

This document defines the repeatable workflow for generating and validating EncoreOS schema documentation.

## Generated Outputs

The following files are generated by tooling and should not be hand-edited:

* `docs/database/SCHEMA_CATALOG.md`
* `docs/database/SCHEMA_RELATIONSHIPS.md`
* `docs/database/SCHEMA_SECURITY_RLS.md`
* `docs/database/SCHEMA_CATALOG.DEV.md`
* `docs/database/SCHEMA_RELATIONSHIPS.DEV.md`
* `docs/database/SCHEMA_SECURITY_RLS.DEV.md`
* `docs/database/SCHEMA_CATALOG.PROD.md`
* `docs/database/SCHEMA_RELATIONSHIPS.PROD.md`
* `docs/database/SCHEMA_SECURITY_RLS.PROD.md`

Generation script:

* `scripts/database/generate-schema-docs.ts`
* `scripts/database/generate-catalog-from-remote.ts`

## Data Sources

Primary source (preferred):

* `reports/db/catalog-snapshot.json` from `npm run db:catalog:export`
* `reports/db/catalog-snapshot.dev.json` from `npm run db:catalog:remote:dev`
* `reports/db/catalog-snapshot.prod.json` from `npm run db:catalog:remote:prod`

Fallback source (when local Supabase catalog export is unavailable):

* `reports/audits/migration-inventory.json` from `npx tsx scripts/database/generate-migration-inventory.ts`

When fallback is used, FK/RLS/policy metadata may be unavailable and generated docs will show `unknown`/`n/a`.

Use the remote catalog commands when accuracy against hosted environments matters. The remote catalog exporter is read-only and queries Postgres system catalogs (`pg_class`, `pg_attribute`, `pg_index`, `pg_constraint`, `pg_policies`, `pg_proc`, `pg_trigger`). It captures tables, columns, indexes, foreign keys, RLS status, policies, views, functions, and triggers.

Required environment variables:

* Dev: `SUPABASE_DEV_DB_URL` or `SUPABASE_DEV_DB_PASSWORD`
* Prod: `SUPABASE_PROD_DB_URL` or `SUPABASE_PROD_DB_PASSWORD`
* Shared fallback: `SUPABASE_DB_PASSWORD`

Default project refs:

* Dev: `<SUPABASE_PROJECT_ID_DEV>`
* Prod: `<SUPABASE_PROJECT_ID_PROD>`

## Commands

### Generate docs

```bash theme={null}
npm run db:docs:generate
```

### Check docs are up-to-date

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

### Refresh from local catalog and regenerate

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

### Refresh hosted dev and prod catalogs

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

### Check hosted dev and prod docs are up-to-date

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

## Recommended End-to-End Refresh

1. Start/reset local Supabase if available:
   * `npx supabase start`
   * `npx supabase db reset`
2. Export catalog:
   * `npm run db:catalog:export`
3. Generate docs:
   * `npm run db:docs:generate`
4. Run schema checks:
   * `npm run db:schemas:lint`
   * `npm run db:check:topo`
   * `npm run db:check:fk-indexes`
   * `npm run db:check:rls-initplan`
5. Validate docs and links:
   * `npm run validate-docs`

If local Supabase is unavailable, generate migration inventory and run docs generation with fallback data, then note limitations in the PR/change summary.

## Hosted Environment Accuracy Refresh

Use this workflow when the goal is complete schema documentation accuracy for current hosted Supabase environments:

1. Ensure read-only database credentials are available:
   * `SUPABASE_DEV_DB_URL` or `SUPABASE_DEV_DB_PASSWORD`
   * `SUPABASE_PROD_DB_URL` or `SUPABASE_PROD_DB_PASSWORD`
2. Export both remote catalogs:
   * `npm run db:catalog:remote:all`
3. Generate environment-specific docs:
   * `npm run db:docs:generate:all`
4. Check generated docs are deterministic:
   * `npm run db:docs:check:all`
5. Spot-check against Supabase MCP or SQL:
   * table count
   * RLS enabled/disabled count
   * FK count
   * policy count
   * high-risk table samples

## Guardrails

* Do not connect documentation generation to production databases by default.
* Do not manually patch generated schema tables in markdown.
* Keep architecture-level guidance in `docs/architecture/DATABASE_SCHEMA.md` and operational generation details here.
* Preserve constitution requirements on tenant isolation and RLS when interpreting generated output.
* Remote catalog scripts must remain read-only; use `SELECT` against system catalogs only.
* Treat prod documentation refresh as observation only. Do not run migrations or DDL as part of schema docs refresh.