github-featured/triggerdotdev--trigger.dev

Files

T

Eric Allam 05d3ab1059 docs(clickhouse): require max+1 numbering and idempotent DDL (#3633 )

## Summary

Codify two rules for ClickHouse migration authors that came out of the
029/030 ordering incident on the TRI-9367 test cloud deploy:

1. **Number files to `max(existing) + 1`, never slot in below the
latest.** Goose runs in strict mode in the cloud deploy pipeline and
refuses to apply a missing version below the current version — slotting
a file in below an already-applied number blocks the next deploy.
2. **DDL must be idempotent** (`ADD COLUMN IF NOT EXISTS`, `DROP COLUMN
IF EXISTS`, `CREATE TABLE IF NOT EXISTS`, etc.) so a retry or
out-of-order apply (`goose up --allow-missing` for local recovery,
manual fixups) is a no-op rather than an error.

## Where the rules live

- `internal-packages/clickhouse/CLAUDE.md` — full rules + example for
migration authors (and AI agents writing migrations).
- `.claude/REVIEW.md` — added a 🔴 finding under "What makes a 🔴
Important finding" so PR reviewers flag either fault as blocking.

The existing migration files are left untouched; the idempotency
requirement applies going forward.

## Test plan

- [ ] Next ClickHouse migration PR uses `IF NOT EXISTS` / `IF EXISTS`
forms
- [ ] No new migration files numbered below an already-applied version
on test/prod

2026-05-15 17:25:29 +00:00

2.2 KiB

Raw Permalink Blame History

ClickHouse Package

@internal/clickhouse - ClickHouse client for analytics and observability data.

Migrations

Goose-format SQL migrations live in schema/. Two rules below are load-bearing — both can block a deploy.

Rule 1: number to `max + 1`, never slot in

Goose runs in strict mode in the deploy pipeline. If a migration file numbered below the version currently recorded in goose_db_version ever shows up, goose refuses to apply it and the deploy fails:

goose run: error: found 1 missing migrations before current version 30:
  version 29: 029_add_task_kind_to_task_runs_v2.sql

When adding a migration:

Look at schema/ and take the largest existing number, call it N.
Name your file 0(N+1)_descriptive_name.sql.
If you've been on a branch while main added migrations, rebase and renumber before opening the PR — a file numbered below the new max will block the next deploy after your PR merges.

Rule 2: DDL must be idempotent

Migrations can be applied out of order in some environments (goose up --allow-missing for local recovery, manual fixups, etc.) and may be retried. Always use idempotent forms so a re-apply is a no-op:

-- +goose Up
ALTER TABLE trigger_dev.your_table
  ADD COLUMN IF NOT EXISTS new_column String DEFAULT '';

-- +goose Down
ALTER TABLE trigger_dev.your_table
  DROP COLUMN IF EXISTS new_column;

Equivalent forms for other DDL:

CREATE TABLE IF NOT EXISTS …
DROP TABLE IF EXISTS …
ADD INDEX IF NOT EXISTS … / DROP INDEX IF EXISTS …
CREATE MATERIALIZED VIEW IF NOT EXISTS … / DROP VIEW IF EXISTS …

ClickHouse supports IF [NOT] EXISTS on all of the above. Older migrations in this directory predate the rule and are not idempotent — leave them as-is unless you're explicitly hardening one.

Naming Conventions

raw_ prefix for input tables (where data lands first)
_v1, _v2 suffixes for table versioning
_mv_v1 suffix for materialized views
_per_day, _per_month for aggregation tables

See README.md in this directory for full naming convention documentation.

Purpose

Stores time-series data for task run analytics, event streams, and performance metrics. Separate from PostgreSQL to handle high-volume writes from task execution.

2.2 KiB Raw Permalink Blame History