How the CI Job in DeskcommCRM Ensures Idempotent Database Migrations

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, 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, 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 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 (lines 69–73), the invariants job utilizes a composite action ./.github/actions/preparar-node to prepare the environment before running the database tests.

# .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 script encapsulates the complex logic of resetting the database state and re-applying migrations:

# 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 orchestrates the idempotency verification by applying 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, the invariants job runs in parallel but serves as a mandatory status check that executes 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 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, the CI job runs these specific tests to confirm that repeated migration applications do not compromise the security policies defined in the Supabase schema.

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