golid / rules

---
description: Patterns for SQL migration files — schema changes, enums, indexes, triggers
globs: backend/migrations/*.sql
alwaysApply: false
---

# SQL Migration Patterns

> **Thesis:** Migrations are the source of truth for the data model. Every table gets UUIDs, TIMESTAMPTZ, FK indexes, and an updated_at trigger.

**Reference files:** `000001_init.up.sql`

## File Naming

`000NNN_description.up.sql` and `000NNN_description.down.sql` — always create both.

## Once Committed, Never Edited

Migrations are append-only history. Once a migration file is committed, even on
a feature branch, do not modify it. Any schema change goes in a new migration
with the next sequence number.

- Need to add a column? New `ALTER TABLE ... ADD COLUMN`.
- Need to drop a column you just added? New `ALTER TABLE ... DROP COLUMN`.
- Forgot an index? New `CREATE INDEX`.

Editing an already-committed `.up.sql` silently desyncs schemas: any environment
that ran the old version will never pick up the change, while fresh databases
will. Before editing any `00NN_*.sql` file, ask: "is this committed?" If yes,
create a new migration.

## Up Migration

```sql
-- Enums first
CREATE TYPE item_status AS ENUM ('new', 'in_progress', 'complete', 'cancelled');

-- Tables (with UUID PKs, timestamps, FKs with ON DELETE CASCADE)
CREATE TABLE items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  status item_status DEFAULT 'new',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes on FKs and frequently filtered columns
CREATE INDEX idx_items_user_id ON items(user_id);
CREATE INDEX idx_items_status ON items(status);

-- Reuse the existing updated_at trigger function
CREATE TRIGGER items_updated_at BEFORE
UPDATE ON items FOR EACH ROW EXECUTE FUNCTION update_updated_at();
```

## Down Migration

Drop in reverse dependency order: columns (ALTER), then tables, then enums.

```sql
DROP TABLE IF EXISTS item_history;
DROP TABLE IF EXISTS items;
DROP TYPE IF EXISTS item_status;
```

## Conventions

- **UUIDs** for all primary keys (not serial/bigint).
- **TIMESTAMPTZ** for all timestamps (not TIMESTAMP).
- **TEXT** for strings (not VARCHAR) — PostgreSQL treats them identically.
- **DECIMAL(x,2)** for money/hours (not FLOAT).
- **JSONB** for structured metadata (audit trails, preferences).
- **TEXT[]** for tag-like arrays (skills, industries).
- **Enums** for finite status sets — add a `cancelled` state if the entity can be deactivated.
- **ON DELETE CASCADE** on child table FKs.
- **Index every FK** and every column used in WHERE filters.
- **`updated_at` trigger** on every table that has the column.

## Idempotent Status Transitions

When writing status transitions in service code that references these enums:

```sql
-- GOOD — idempotent, no error on repeat
UPDATE items SET status = 'in_progress' WHERE id = $1 AND status = 'new';
```

## Adding a CHECK Constraint to an Existing Table

Any `ADD CONSTRAINT ... CHECK (...)` against an existing table risks failing at deploy time on a single legacy row that violates the new predicate. The `ALTER TABLE` errors out with a generic message and no row IDs, blocking the migration.

Always precede the `ADD CONSTRAINT` with a `DO $$ ... RAISE` pre-flight that names the offending IDs. Defensive only — no-op when data is clean.

```sql
DO $$
DECLARE
    bad_ids TEXT;
BEGIN
    SELECT string_agg(id::text, ', ') INTO bad_ids
    FROM feature_flags
    WHERE key IS NULL OR trim(key) = '';
    IF bad_ids IS NOT NULL THEN
        RAISE EXCEPTION
            'Migration NNN cannot apply feature_flags_key_not_empty: % row(s) have empty key (ids: %). Fix data before re-running.',
            (SELECT count(*) FROM feature_flags WHERE key IS NULL OR trim(key) = ''),
            bad_ids;
    END IF;
END $$;

ALTER TABLE feature_flags
    ADD CONSTRAINT feature_flags_key_not_empty CHECK (trim(key) <> '');
```

When this matters most: any CHECK that tightens a previously-loose column (`NOT NULL` → `> 0`, free-text → enum, etc.). One bad legacy row is enough to fail the deploy.

More agent context in golid-ai/golid

44 other files this repository gives its agents.