# How Postgres Migrations Are Versioned in supabase/migrations/ and Replicated in supabase/baseline.sql

> Discover how DeskcommCRM versions Postgres migrations using supabase/migrations/ and replicates them in supabase/baseline.sql. Ensure reproducible database installations with this three-artifact doctrine.

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

---

**DeskcommCRM enforces a strict three-artifact doctrine where every schema change requires a timestamped migration file in `supabase/migrations/`, an idempotent appendix block in [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql), and a registry entry in [`MANIFEST.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/MANIFEST.md) to guarantee immutable ordering and fully reproducible database installations.**

DeskcommCRM implements a rigorous Postgres migration strategy that ensures both incremental upgrades and fresh installations converge on identical database states. By treating the migration log, baseline schema, and manifest registry as a unified trio, the repository allows developers to evolve schemas incrementally while enabling self-hosted VPS installs to apply the complete final state in a single, repeatable step.

## The Three-Artifact Doctrine for Schema Changes

Every schema modification in DeskcommCRM must produce three synchronized artifacts. This pattern is enforced by the test suite in [`tests/unit/manifest-x-migrations.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/unit/manifest-x-migrations.test.ts) and validated by the `invariants` CI job.

### 1. Versioned Migration Files in supabase/migrations/

The primary source of truth lives in `supabase/migrations/` as pure-SQL scripts. Each filename follows a strict format: a **14-digit timestamp** (`YYYYMMDDHHMMSS`) followed by an incremental **four-digit identifier (NNNN)** and a descriptive slug.

```sql
-- 20260827180000_0196_followup_pointer_surface.sql
-- 20260911170000_0238_convites_de_time_persistidos.sql

```

This timestamp serves as the primary key in Supabase’s `supabase_migrations.schema_migrations` table, guaranteeing an immutable order of application. When a developer runs `supabase db push`, the CLI applies these files sequentially by timestamp.

### 2. Idempotent Appendices in supabase/baseline.sql

The same DDL must be appended to [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) as a labeled, idempotent block. These appendices use `IF NOT EXISTS` clauses and `ON CONFLICT DO NOTHING` patterns to make the schema **re-applicable** without side effects.

Each block is wrapped with a comment marker tying it to the migration number:

```sql
-- ---- convites de time persistidos (migration 0238) ----
CREATE TABLE IF NOT EXISTS team_invites (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid NOT NULL,
  inviter_user_id uuid NOT NULL,
  invitee_email text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL
);
ALTER TABLE team_invites ENABLE ROW LEVEL SECURITY;

```

Fresh VPS installations execute only [`install.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/install.sh) → [`baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/baseline.sql), bypassing the entire migration chain while arriving at the identical final schema.

### 3. The MANIFEST.md Registry

Every migration requires a corresponding line in [`supabase/migrations/MANIFEST.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/migrations/MANIFEST.md). This file acts as the authoritative list used by CI and the [`update.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/update.sh) script to track which migrations have been applied.

The manifest uses a standard table format:

```markdown
| Version            | Name                                 | Description |
|--------------------|--------------------------------------|-------------|
| `20260717190001`   | `0038_webhooks_automation`           | Webhooks universal + automation engine |
| `20260911170000`   | `0238_convites_de_time_persistidos`  | Persist team invite records |

```

The manifest also documents **timestamp collision resolution** (adding one second to later files) to ensure the Supabase CLI never encounters primary-key conflicts when registering migrations.

## Migration Naming Convention and Ordering

The 14-digit timestamp format (`YYYYMMDDHHMMSS`) establishes a lexicographical sort order that matches chronological execution. The four-digit incremental identifier (`NNNN`) provides human-readable sequence markers while preventing collisions between migrations created in the same minute.

According to the repository documentation in [`docs/superpowers/plans/2026-07-28-canais-fase-3a-templates.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/docs/superpowers/plans/2026-07-28-canais-fase-3a-templates.md), this naming convention is mandatory for all schema changes. The `supabase_migrations.schema_migrations` table records the exact timestamp, making the migration history immutable and strictly ordered.

## Replication Workflow: From Migration to Baseline

When introducing a new schema change, developers must create all three artifacts. The following example illustrates the complete workflow for adding a team invites table.

### Example: Creating the Team Invites Migration

First, create the versioned migration file in [`supabase/migrations/20260911170000_0238_convites_de_time_persistidos.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/migrations/20260911170000_0238_convites_de_time_persistidos.sql):

```sql
-- 20260911170000_0238_convites_de_time_persistidos.sql
CREATE TABLE IF NOT EXISTS team_invites (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid NOT NULL,
  inviter_user_id uuid NOT NULL,
  invitee_email text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL
);
ALTER TABLE team_invites ENABLE ROW LEVEL SECURITY;

```

Next, append the identical idempotent block to [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql):

```sql
-- ---- convites de time persistidos (migration 0238) ----
CREATE TABLE IF NOT EXISTS team_invites (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid NOT NULL,
  inviter_user_id uuid NOT NULL,
  invitee_email text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL
);
ALTER TABLE team_invites ENABLE ROW LEVEL SECURITY;

```

Finally, register the migration in [`supabase/migrations/MANIFEST.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/migrations/MANIFEST.md):

```markdown
| `20260911170000` | `0238_convites_de_time_persistidos` | Persist team invite records |

```

## Enforcement Through CI and Testing

The repository validates the three-artifact doctrine through automated invariants. The `gov:verify` CI job runs type-checking and linting, while the `invariants` job executes `pnpm test:db` to run [`tests/unit/baseline-reaplicavel.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/unit/baseline-reaplicavel.test.ts).

This test suite spins up a disposable Postgres container, applies [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql), and verifies that the resulting schema matches the state produced by running the full migration chain sequentially. Additionally, [`tests/unit/manifest-x-migrations.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/unit/manifest-x-migrations.test.ts) asserts that every file in `supabase/migrations/` has a corresponding appendix block in [`baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/baseline.sql) and an entry in [`MANIFEST.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/MANIFEST.md).

## Why Three Artifacts Matter for Self-Hosting

The separation of concerns between the three artifacts serves distinct operational requirements:

- **The migration file** provides the granular history required for incremental upgrades on existing installations.
- **The baseline appendix** enables the [`install.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/install.sh) and [`update.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/update.sh) scripts to provision fresh databases instantly without replaying years of schema evolution.
- **The manifest** guarantees that the ordering in the database matches the filesystem order and resolves timestamp collisions consistently across all branches.

By maintaining this synchronization, DeskcommCRM ensures that a developer running `supabase db push` on a staging environment and a sysadmin running [`install.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/install.sh) on a new VPS both arrive at bit-for-bit identical database schemas.

## Summary

- **Three artifacts required**: Every schema change needs a timestamped migration file (`supabase/migrations/`), an idempotent appendix ([`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql)), and a manifest entry ([`MANIFEST.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/MANIFEST.md)).
- **Strict naming**: Migration filenames use a 14-digit timestamp (`YYYYMMDDHHMMSS`) followed by a four-digit sequential ID to ensure immutable ordering in `supabase_migrations.schema_migrations`.
- **Idempotent baselines**: The [`baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/baseline.sql) file uses `IF NOT EXISTS` and `ON CONFLICT DO NOTHING` patterns to allow repeatable execution on fresh installs.
- **Automated enforcement**: CI jobs `invariants` and `gov:verify` run [`tests/unit/manifest-x-migrations.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/unit/manifest-x-migrations.test.ts) and [`tests/unit/baseline-reaplicavel.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/unit/baseline-reaplicavel.test.ts) to verify synchronization.
- **Collision handling**: The [`MANIFEST.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/MANIFEST.md) registry documents timestamp collisions and their resolution (adding one second) to prevent primary-key conflicts.

## Frequently Asked Questions

### What is the exact filename format for migrations in supabase/migrations/?

Migration filenames must start with a 14-digit timestamp in the format `YYYYMMDDHHMMSS`, followed by an underscore, a four-digit incremental identifier (e.g., `0196` or `0238`), another underscore, and a descriptive slug ending in `.sql`. An example is [`20260827180000_0196_followup_pointer_surface.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/20260827180000_0196_followup_pointer_surface.sql). This timestamp serves as the primary key in the `supabase_migrations.schema_migrations` table.

### How does supabase/baseline.sql differ from individual migration files?

While migration files in `supabase/migrations/` are executed sequentially to upgrade existing databases, [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) contains idempotent appendices of every migration combined into a single file. Self-hosted installations run only [`baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/baseline.sql) via [`install.sh`](https://github.com/melgarafael/DeskcommCRM/blob/main/install.sh), allowing a fresh database to reach the final schema state without replaying the entire migration history. Each appendix block uses `CREATE TABLE IF NOT EXISTS` and similar patterns to ensure safe re-execution.

### What happens if two developers create migrations with the same timestamp?

Timestamp collisions are resolved by adding one second to the later file, as documented in lines 7-14 of [`supabase/migrations/MANIFEST.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/migrations/MANIFEST.md). The manifest records these adjustments to ensure the Supabase CLI never encounters a primary-key conflict when inserting the migration record into `supabase_migrations.schema_migrations`. The CI `invariants` job validates that the manifest ordering matches the filesystem timestamps.

### How does CI validate that migrations are correctly replicated to the baseline?

The `invariants` CI job runs `pnpm test:db`, which executes [`tests/unit/baseline-reaplicavel.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/unit/baseline-reaplicavel.test.ts) against a disposable Postgres container. This test applies [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql) and compares the resulting schema against the state produced by running the full migration chain from `supabase/migrations/`. Additionally, [`tests/unit/manifest-x-migrations.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/unit/manifest-x-migrations.test.ts) verifies that every migration file has a corresponding labeled block in [`baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/baseline.sql) and an entry in [`MANIFEST.md`](https://github.com/melgarafael/DeskcommCRM/blob/main/MANIFEST.md).