# How the CI Job in DeskcommCRM Ensures Idempotent Database Migrations

> Discover how the DeskcommCRM CI job guarantees idempotent database migrations through isolated invariant checks, ensuring data integrity and reliable updates.

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

---

**The CI job ensures idempotent database migrations by running an isolated "invariants" check that applies a baseline schema, re-runs migrations in update mode, and verifies governance rules remain intact.**

The `melgarafael/DeskcommCRM` repository uses a dedicated CI workflow to guarantee that Supabase migrations can be applied repeatedly without side effects. By leveraging a specialized **invariants** job within [`.github/workflows/ci.yml`](https://github.com/melgarafael/DeskcommCRM/blob/main/.github/workflows/ci.yml), the pipeline validates that schema changes remain consistent across multiple execution cycles.

## The Invariants Job Architecture

The CI pipeline defines an `invariants` job that operates in parallel with the fast-feedback `verify` job. This parallelization ensures developers receive quick validation results while maintaining mandatory checks for migration safety.

### Parallel Execution for Fast Feedback

Running the idempotency checks separately from the primary verification allows the CI to enforce strict database governance without blocking rapid iteration. The `invariants` job executes on `ubuntu-latest` and invokes the database testing suite through `pnpm test:db`.

## The Idempotency Verification Process

At the core of the validation logic resides [`scripts/test-db.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/scripts/test-db.sh), which orchestrates a three-phase verification process. This script is triggered via the `pnpm test:db` command within the CI environment.

### Baseline Installation with Strict Error Handling

The script first enters **installation mode**, applying [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) with the `ON_ERROR_STOP=1` flag. This PostgreSQL setting ensures the process aborts immediately upon encountering any error, establishing a clean, deterministic initial state for subsequent testing.

### Idempotent Update Mode Execution

Following the baseline installation, the script runs the migration files as an **update operation**. Because the migration system is designed to be idempotent, re-applying the same migrations on an already-up-to-date database produces no side effects or duplicate schema changes. This re-execution confirms that the migrations can safely run multiple times in production environments.

### Governance Invariant Validation

After both installation and update phases complete, the script executes the invariant test suite located in `tests/invariants/**`. These tests validate that multi-tenant Row Level Security (RLS) rules and governance constraints remain intact throughout the migration process, ensuring that repeated schema applications do not compromise security policies.

## Implementation Details

According to the workflow definition in [`.github/workflows/ci.yml`](https://github.com/melgarafael/DeskcommCRM/blob/main/.github/workflows/ci.yml) (lines 69–73), the `invariants` job utilizes a composite action `./.github/actions/preparar-node` to prepare the environment before running the database tests.

```yaml

# .github/workflows/ci.yml – invariants job

jobs:
  invariants:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v7
      - uses: ./.github/actions/preparar-node
      - name: RLS isolation + governance invariants
        run: pnpm test:db   # executes scripts/test-db.sh

```

The [`scripts/test-db.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/scripts/test-db.sh) script encapsulates the complex logic of resetting the database state and re-applying migrations:

```bash

# scripts/test-db.sh (excerpt – described in ci.yml)

# 1️⃣ Apply baseline.sql in install mode (fails on any error)

# 2️⃣ Apply migrations in update mode (idempotent)

# 3️⃣ Run invariant tests against the DB

pnpm test:db

```

## Summary

- **The `invariants` job** runs in parallel with the `verify` job to provide mandatory migration checks without slowing down development feedback loops.
- **[`scripts/test-db.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/scripts/test-db.sh)** orchestrates the idempotency verification by applying [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) in strict install mode, then re-running migrations to confirm they produce no changes on subsequent executions.
- **Invariant tests** in `tests/invariants/**` verify that RLS policies and governance constraints survive repeated migration applications.
- **Strict error handling** via `ON_ERROR_STOP=1` ensures that any failure during the baseline installation immediately halts the CI process, preventing false positives.

## Frequently Asked Questions

### What does idempotency mean in database migrations?

Idempotency in database migrations means that applying the same migration script multiple times produces the same final schema state without errors or unintended side effects. In the `melgarafael/DeskcommCRM` repository, this property ensures that if a deployment retries or if a migration runs twice due to a network issue, the database remains consistent and operations remain safe.

### How does the invariants job differ from the verify job?

The `verify` job provides fast feedback on basic checks, while the `invariants` job specifically validates database schema integrity and migration idempotency. According to the source code in [`.github/workflows/ci.yml`](https://github.com/melgarafael/DeskcommCRM/blob/main/.github/workflows/ci.yml), the `invariants` job runs in parallel but serves as a mandatory status check that executes [`scripts/test-db.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/scripts/test-db.sh) to enforce RLS and governance rules.

### What happens if a migration is not idempotent?

If a migration lacks idempotency, the second phase of [`scripts/test-db.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/scripts/test-db.sh) will detect side effects or failures when re-applying the migration files. The script runs the invariant test suite (`tests/invariants/**`) after the update mode execution, which would fail if duplicate schema changes violate multi-tenant isolation or governance constraints, thereby blocking the CI pipeline.

### Where are the Row Level Security (RLS) rules tested?

The RLS rules and multi-tenant governance constraints are validated in the `tests/invariants/**` directory. After the idempotency verification completes in [`scripts/test-db.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/scripts/test-db.sh), the CI job runs these specific tests to confirm that repeated migration applications do not compromise the security policies defined in the Supabase schema.