# How DeskcommCRM Manages Database Schema Changes for Self‑Hosted Deployments

> Learn how DeskcommCRM manages database schema changes for self-hosted deployments using versioned migrations, idempotent DDL, and manifest files. Ensure smooth initialization for your instance.

- Repository: [Rafael Melgaço/DeskcommCRM](https://github.com/melgarafael/DeskcommCRM)
- Tags: how-to-guide
- Published: 2026-09-12

---

**DeskcommCRM handles database schema changes through a strict three‑step doctrine: versioned migration files in `supabase/migrations/`, idempotent DDL appended to [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql), and registry entries in [`supabase/migrations/MANIFEST.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/migrations/MANIFEST.md), ensuring self‑hosters can initialize fresh instances with a single SQL file without risking existing data.**

Managing database schema changes in open‑source CRMs requires balancing developer velocity with operational safety for self‑hosters. The [melgarafael/DeskcommCRM](https://github.com/melgarafael/DeskcommCRM) repository implements a disciplined migration strategy that treats the consolidated [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) as the single source of truth for new deployments. This approach guarantees that any structural evolution—whether adding tables, indexes, or Row Level Security (RLS) policies—remains traceable, reversible, and safe to replay on both empty and existing PostgreSQL instances.

## The Three‑Step Migration Doctrine

Each schema alteration in DeskcommCRM must satisfy three invariant requirements before merging into the main branch. This doctrine ensures that self‑hosters never execute raw migration sequences manually, while developers retain full version control over incremental changes.

### Step 1: Versioned Migration Files

All DDL changes originate in timestamped SQL files under `supabase/migrations/`. The filename follows a strict 14‑digit timestamp prefix plus sequential numbering (e.g., [`20260717190001_0038_webhooks_automation.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/20260717190001_0038_webhooks_automation.sql)). These files contain the authoritative `CREATE`, `ALTER`, or `DROP` statements required for the change.

During development, the Supabase CLI applies these incrementally via:

```bash
supabase db push

```

This command pushes pending migrations to the local or remote database, allowing developers to test structural changes against live data without affecting the production baseline.

### Step 2: Idempotent Appendix in baseline.sql

Self‑hosters do not run individual migration files. Instead, they execute the consolidated [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql), which contains every schema object required for a fresh installation. After creating a migration, developers extract its DDL and wrap it in idempotent guards—using `IF NOT EXISTS` for creating objects and `DROP … IF EXISTS` for destructive changes—then append it to the end of [`baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/baseline.sql).

For example, adding a new `example` table requires wrapping the creation logic in a PL/pgSQL anonymous block:

```sql
-- ---- example table (migration 0100) ----
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'public' AND tablename = 'example') THEN
    CREATE TABLE public.example (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      organization_id uuid NOT NULL,
      name text NOT NULL
    );
    ALTER TABLE public.example ENABLE ROW LEVEL SECURITY;
    CREATE POLICY example_select ON public.example
    USING (organization_id = ANY (fn_user_org_ids()));
  END IF;
END $$;

```

Because the baseline is idempotent, re‑applying it to an existing database performs zero destructive operations, while new installations receive the complete schema in a single transaction.

### Step 3: Manifest Registry

Every migration must be recorded in [`supabase/migrations/MANIFEST.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/migrations/MANIFEST.md) to guarantee global uniqueness and traceability. The manifest tracks the 14‑digit timestamp, migration name, and description in a structured table:

```yaml
- Version: 20260801000000
  Name: 0100_example
  Description: Add example table for demo purposes

```

This registry prevents version collisions across feature branches and provides a human‑readable audit trail of when specific schema objects were introduced.

## Deploying Schema Changes on Self‑Hosted VPS

For production deployments, self‑hosters initialize their PostgreSQL instance using only the [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) file. The repository documentation in [`docs/vendaval-vps-deploy-comandos.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/docs/vendaval-vps-deploy-comandos.md) specifies the exact command:

```bash
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f supabase/baseline.sql

```

Setting `ON_ERROR_STOP=1` ensures that any failure in the baseline script halts the deployment immediately, preventing partial schema states. This single‑file approach eliminates the complexity of migration runners for operators who simply need a working CRM instance without managing migration history tables.

## Automated Validation and CI Safety

The integrity of this three‑step process is enforced through continuous integration. Before any migration merges, the `pnpm test:db` command executes the invariant test suite against a disposable PostgreSQL container. This suite validates:

- **Clean install mode**: [`baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/baseline.sql) successfully creates a functional schema on an empty database
- **Update mode**: Re‑applying the baseline to an already‑initialized database causes no errors or data loss
- **RLS enforcement**: Row Level Security policies correctly restrict tenant data access

Additionally, the `gov:verify` job in the CI pipeline runs TypeScript type‑checking, linting, and unit tests to confirm that code changes align with the proposed schema modifications. These gates ensure that only idempotent, manifest‑registered migrations reach the main branch.

## Summary

DeskcommCRM’s approach to database schema changes prioritizes operational simplicity for self‑hosters while maintaining rigorous version control for developers. Key takeaways include:

- **Versioned migrations** live in `supabase/migrations/` with 14‑digit timestamps and are applied via `supabase db push` during development
- **Idempotent consolidation** in [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) allows self‑hosters to initialize production databases with a single `psql` command without managing migration sequences
- **Manifest registration** in [`supabase/migrations/MANIFEST.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/migrations/MANIFEST.md) guarantees unique versions and provides audit trails
- **CI validation** via `pnpm test:db` and `gov:verify` enforces idempotence and prevents breaking changes

## Frequently Asked Questions

### What file do self‑hosters run to initialize the DeskcommCRM database?

Self‑hosters execute only the [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) file using the command `psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f supabase/baseline.sql`. This consolidated script contains every schema object wrapped in idempotent checks, eliminating the need to run individual migration files in sequence.

### How does DeskcommCRM prevent baseline.sql from corrupting existing data?

All DDL statements in [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) are wrapped in idempotent guards such as `CREATE TABLE IF NOT EXISTS` and `DROP … IF EXISTS` patterns inside PL/pgSQL anonymous blocks. When the script runs against an existing database, these conditional checks skip already‑present objects, ensuring zero destructive impact on production data.

### Where are migration timestamps recorded and why?

Migration timestamps are recorded in [`supabase/migrations/MANIFEST.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/migrations/MANIFEST.md) alongside the migration name and description. This registry ensures that every schema change has a globally unique 14‑digit identifier, preventing version collisions across multiple development branches and providing a canonical history of schema evolution.

### How are Row Level Security (RLS) policies handled during schema changes?

RLS policies are defined directly within migration files and replicated in [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql). Each new table automatically includes `ALTER TABLE … ENABLE ROW LEVEL SECURITY` followed by tenant‑aware policies (e.g., `USING (organization_id = ANY (fn_user_org_ids()))`). The CI invariant tests verify that these policies correctly enforce multi‑tenancy on both fresh and existing databases.