db-migration

name	db-migration
description	Create or update database schema and generate migrations. Use when modifying ent schema, adding database fields/tables, or generating migration files.
allowed-tools	Read, Edit, Write, Bash, Grep, Glob, Agent

Database Schema Change & Migration

You are helping the user modify the OpenMeter database schema and generate a corresponding migration.

Context

• Schema files: openmeter/ent/schema/*.go — ent schema definitions (source of truth)
• Generated ent code: openmeter/ent/db/ — DO NOT edit manually
• Migrations dir: tools/migrate/migrations/ — DO NOT edit manually
• Always use --env local — we do not use Atlas Cloud services

Workflow

Follow these steps in order:

Step 1: Modify the ent schema

Edit or create files in openmeter/ent/schema/. Look at existing schema files for conventions.

If the user described what change they want ($ARGUMENTS), implement it. Otherwise, ask what schema changes are needed. When creating a new schema always define schema to support soft delete.

Schemas supporting soft delete always have a deleted_at field.

Step 2: Regenerate ent code

Run:

make generate

This runs go generate ./... which regenerates the ent client code in openmeter/ent/db/ from the schema definitions. Check that it completes without errors.

Step 3: Generate the migration diff

Run:

atlas migrate --env local diff <migration-name>

Where <migration-name> is a short descriptive snake_case name for the change (e.g., add_customer_email, create_invoice_table). Derive the name from the schema change being made.

This creates timestamped .up.sql and .down.sql files in tools/migrate/migrations/ and updates atlas.sum.

Step 4: Copy view definitions into the migration (if views exist)

If the schema includes ent views (schemas with ent.View), the generated view SQL must be manually copied into the .up.sql migration file. Atlas does not auto-generate view DDL.

1. Run make generate to regenerate tools/migrate/views.sql from ent view schemas
2. Copy the relevant CREATE VIEW statements from tools/migrate/views.sql into the end of the generated .up.sql migration file
3. If the migration replaces a previous view definition, add a DROP VIEW IF EXISTS "<view_name>" statement before the CREATE VIEW

The view parity test (TestViewDefinitionsMatchGeneratedSchemaSQL in tools/migrate/view_parity_test.go) validates that view definitions in migrations match the generated view SQL. It strips individual VIEW statements from migration files while preserving all other DDL statements in the same file, so mixing VIEW and non-VIEW statements in a single migration is safe.

Step 5: Review the generated migration

Read the generated .up.sql file and verify:

• The SQL matches the intended schema change
• No unintended changes are included
• Indexes are created where appropriate

Present a summary of the migration to the user.

Available Mixins

From pkg/framework/entutils/mixins.go:

Mixin	Fields	Notes
entutils.IDMixin{}	id char(26) ULID	Auto-generated, unique, immutable
entutils.NamespaceMixin{}	namespace string	Immutable, indexed
entutils.TimeMixin{}	created_at, updated_at, deleted_at (nillable)	Provides soft delete support
entutils.MetadataMixin{}	metadata JSONB map[string]string	Optional
entutils.ResourceMixin{}	ID + Namespace + Metadata + Time + name + description	Composite of above mixins
entutils.UniqueResourceMixin{}	Resource + key	Adds unique index on (namespace, key, deleted_at)
entutils.KeyMixin{}	key string	Immutable, not empty
entutils.CadencedMixin{}	active_from, active_to (nillable)	For time-bounded entities

Usage in schema:

func (<Entity>) Mixin() []ent.Mixin {
    return []ent.Mixin{
        entutils.IDMixin{},
        entutils.NamespaceMixin{},
        entutils.TimeMixin{},
    }
}

Field, Edge, and Index Patterns

For fields, edges (relationships), and indexes, read existing schemas in openmeter/ent/schema/ for conventions. Key things to know:

• JSONB fields use entutils.JSONStringValueScanner — see openmeter/ent/schema/llmcostprice.go
• Foreign keys use char(26) schema type to match ULID IDs
• Soft-delete unique indexes include deleted_at in the unique constraint (e.g., index.Fields("namespace", "key", "deleted_at").Unique()) — always filter with Where(<entity>db.DeletedAtIsNil()) in queries
• Cascade deletes use entsql.OnDelete(entsql.Cascade) on the parent edge

Troubleshooting

Rehashing migrations

If the atlas.sum file gets out of sync (e.g., after manually editing a migration file or resolving conflicts), rehash it:

atlas migrate --env local hash

Dev database

Atlas uses a Docker-based dev database (docker://postgres/15/dev) for diffing. Make sure Docker is running before generating migrations.

Migration format

Migrations are generated, never edit them manually. Migrations use golang-migrate format. Each migration has:

• <timestamp>_<name>.up.sql — applied when migrating up
• <timestamp>_<name>.down.sql — applied when migrating down

Important Reminders

• Always use --env local with atlas commands
• Never edit files in openmeter/ent/db/ manually
• Never edit migration files in tools/migrate/migrations manually
• Run make generate before atlas migrate diff so the ent code is up to date
• Drop incidental go.sum changes produced by generation unless the task intentionally changes dependencies
• If compilation errors occur after schema changes, fix the schema first, then re-run make generate