How DeskcommCRM Manages Database Schema Changes for Self‑Hosted Deployments

DeskcommCRM handles database schema changes through a strict three‑step doctrine: versioned migration files in supabase/migrations/, idempotent DDL appended to supabase/baseline.sql, and registry entries in 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 repository implements a disciplined migration strategy that treats the consolidated 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). These files contain the authoritative CREATE, ALTER, or DROP statements required for the change.

During development, the Supabase CLI applies these incrementally via:

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

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

-- ---- 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 to guarantee global uniqueness and traceability. The manifest tracks the 14‑digit timestamp, migration name, and description in a structured table:

- 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 file. The repository documentation in docs/vendaval-vps-deploy-comandos.md specifies the exact command:

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 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 allows self‑hosters to initialize production databases with a single psql command without managing migration sequences
• Manifest registration in 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 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 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 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. 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}