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

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, and a registry entry in 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 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.

-- 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 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:

-- ---- 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 → 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. This file acts as the authoritative list used by CI and the update.sh script to track which migrations have been applied.

The manifest uses a standard table format:

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

-- 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:

-- ---- 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:

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

This test suite spins up a disposable Postgres container, applies 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 asserts that every file in supabase/migrations/ has a corresponding appendix block in baseline.sql and an entry in 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 and 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 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), and a manifest entry (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 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 and tests/unit/baseline-reaplicavel.test.ts to verify synchronization.
• Collision handling: The 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. 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 contains idempotent appendices of every migration combined into a single file. Self-hosted installations run only baseline.sql via 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. 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 against a disposable Postgres container. This test applies 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 verifies that every migration file has a corresponding labeled block in baseline.sql and an entry in MANIFEST.md.

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