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

# Migration Workflow — From Spec to Deployment

> End-to-end workflow for creating a new table or column in Encore OS — including the declarative DDL diff workflow, RLS template, testing, and rollback.

**Last Updated:** 2026-05-28
**Authoritative sources:** [`constitution.md`](/governance/canonical/constitution) §5 (Database), [`DATABASE_DEVELOPMENT_GUIDE`](/development/DATABASE_DEVELOPMENT_GUIDE), [`SUPABASE_CLI_LOCAL_WORKFLOW`](/development/supabase/SUPABASE_CLI_LOCAL_WORKFLOW)

This page consolidates everything an engineer needs to ship a database change — from a spec gate to a deployed migration with RLS coverage. If you've never added a table before, follow this top-to-bottom once; afterwards use it as a checklist.

***

## When to use which workflow

Encore OS supports two migration workflows. Use the right one:

| Workflow                                                   | When                                                                                                                                                                    |
| ---------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Declarative DDL** (`npm run db:schemas:diff`)            | Most schema work: new tables, columns, indexes, RLS policies, functions. You edit the declarative SQL under `supabase/schemas/`; the diff tool generates the migration. |
| **Imperative migration** (`supabase migration new <name>`) | Data backfills, complex multi-step changes, anything that can't be expressed as "the final shape of the schema."                                                        |

If you're not sure, use declarative — it stays in sync with the live schema and avoids drift.

***

## End-to-end workflow

<Steps>
  <Step title="Pre-flight">
    * Confirm the spec is at `pipeline_status: validated` or later. New tables without a spec are forbidden — see [`SPEC_WORKFLOW`](/development/SPEC_WORKFLOW).
    * Ensure `npm run supabase:start` is running locally (Docker daemon + Supabase stack).
    * Pull main: `git fetch origin && git rebase origin/main`.
  </Step>

  <Step title="Edit the declarative schema">
    Edit the relevant file under `supabase/schemas/{core}/` (e.g. `supabase/schemas/hr/employees.sql`). Add the table, columns, indexes, and RLS policy in the same file — the final shape of the entity lives in one place.

    Follow the [naming conventions](/development/DATABASE_DEVELOPMENT_GUIDE):

    * Tables: `{core}_{entity}` snake\_case (e.g. `hr_credentials`, `pm_encounters`).
    * Columns: snake\_case; required audit columns `id`, `created_at`, `updated_at`, `org_id` (tenant); soft-delete `deleted_at` only when explicit policy requires.
  </Step>

  <Step title="Add the RLS policy in the same edit">
    **Every business table MUST have RLS enabled and tested.** Use the template (also in `.cursor/rules/database-migrations.md`):

    ```sql theme={null}
    alter table hr_credentials enable row level security;

    create policy hr_credentials_tenant_isolation
      on hr_credentials
      for all
      using (org_id = (select auth_org_id()))
      with check (org_id = (select auth_org_id()));
    ```

    For role-bounded reads (e.g. only HR managers see credentials), add a second policy. See [`RLS_PATTERNS`](/database/RLS_PATTERNS) for the canonical templates (tenant + role-bounded + read/write split).

    For PHI tables, add the standard audit trigger:

    ```sql theme={null}
    create trigger hr_credentials_audit
      after insert or update or delete on hr_credentials
      for each row execute function pf_log_phi_access();
    ```
  </Step>

  <Step title="Generate the migration">
    ```bash theme={null}
    npm run db:schemas:diff -- -f add_hr_credentials
    ```

    This compares your edited schema against the current applied state and writes a timestamped migration to `supabase/migrations/<ts>_add_hr_credentials.sql`. Review the generated SQL — if it contains anything you didn't intend, edit the declarative file and re-diff.

    For an imperative change instead: `supabase migration new add_hr_credentials_backfill`.
  </Step>

  <Step title="Validate before applying">
    ```bash theme={null}
    npm run validate-migration -- --latest
    ```

    Catches naming violations, missing RLS, missing tenant isolation, and security anti-patterns. **Do not bypass.** Common failures:

    * Missing `enable row level security`
    * Policy without `org_id = auth_org_id()` predicate
    * SECURITY DEFINER without `search_path` lockdown
    * Column-level grant without RLS
  </Step>

  <Step title="Apply locally">
    ```bash theme={null}
    npm run supabase:db:reset   # destroys local DB; reapplies all migrations cleanly
    npm run generate-types       # regenerates src/integrations/supabase/types.ts
    ```

    `generate-types` is required — TypeScript will fail later if you don't.
  </Step>

  <Step title="Add RLS tests">
    Every new table needs an RLS test in `tests/rls/{core}/{table}.rls.test.ts`. Pattern:

    ```ts theme={null}
    import { describe, it, expect } from 'vitest';
    import { setupRlsTest } from '../helpers';

    describe('hr_credentials RLS', () => {
      it('blocks cross-tenant reads', async () => {
        const { tenantA, tenantB } = await setupRlsTest();
        await tenantA.from('hr_credentials').insert({ /* ... */ });
        const { data } = await tenantB.from('hr_credentials').select();
        expect(data).toHaveLength(0);
      });
    });
    ```

    Run: `npm run test:rls -- hr/credentials`. See [`rls-testing` skill](/development/MCP_USAGE) for failure-mode debugging.
  </Step>

  <Step title="Commit and push">
    Stage the **declarative schema file, the generated migration, and the regenerated types** together:

    ```bash theme={null}
    git add supabase/schemas/hr/employees.sql supabase/migrations/<ts>_add_hr_credentials.sql src/integrations/supabase/types.ts tests/rls/hr/credentials.rls.test.ts
    git commit -m "hr: add credentials table with tenant + role RLS"
    git push -u origin <branch>
    ```

    CI runs `validate-migration`, `check-rls-coverage`, `test:rls`, and architecture checks. Fix locally on failure — never `--no-verify`.
  </Step>

  <Step title="Deploy to dev / prod">
    Migrations apply automatically on merge through the Supabase branching pipeline. See [`SUPABASE_MULTI_ENV_SETUP`](/development/supabase/SUPABASE_MULTI_ENV_SETUP) for env wiring and [`SUPABASE_GO_LIVE_DASHBOARD_CHECKLIST`](/development/supabase/SUPABASE_GO_LIVE_DASHBOARD_CHECKLIST) for prod cuts.
  </Step>
</Steps>

***

## Rollback

If a migration ships and breaks something:

1. Do **not** delete the migration file. It's already applied to dev/prod.
2. Write a forward-fix migration that undoes the breaking change: `supabase migration new revert_<original_name>`.
3. Apply locally, test, ship through the same pipeline.
4. If the issue is PHI-data corruption, also create a compliance evidence entry under `docs/compliance/evidence/`.

See [`MIGRATION_ROLLBACK_STRATEGY`](/migration/migration-checklist) for the full playbook.

***

## Common mistakes (the ones reviewers will flag)

| Symptom                                       | Cause                                            | Fix                                                                                           |
| --------------------------------------------- | ------------------------------------------------ | --------------------------------------------------------------------------------------------- |
| `Cannot read property 'org_id'` at runtime    | Forgot `org_id` column or insert default         | Add NOT NULL constraint and default to `(select auth_org_id())` in the policy                 |
| RLS test passes but live data leaks           | Used USING without WITH CHECK                    | Add WITH CHECK identical to USING (or stricter)                                               |
| Migration ran on dev but fails in prod        | Hand-edited a previously-applied migration       | Never edit applied migrations. Write a forward-fix                                            |
| `generate-types` shows the table as `unknown` | Forgot to reset and regenerate after schema edit | `npm run supabase:db:reset && npm run generate-types`                                         |
| Architecture check fails on the type          | Imported types from another core                 | Cross-core types go through PF — see [Core Boundary Rules](/architecture/core-boundary-rules) |

***

## Related skills

* **`migration-authoring`** — pre-flight checklist + constitutional patterns; trigger when starting any migration.
* **`rls-testing`** — debugging RLS test failures.
* **`test-rls`** — running the RLS suite scoped to a table/core.
* **`check-rls-coverage`** — gap report for tables missing tests.

Invoke from any AI session: just say "migration for hr credentials" and the skill triggers.