# Model-driven migrations (experimental installable)

This independent Go module implements Cicadel's standard automated
migration battery. It uses GORM model metadata, compares it with recorded model
history, and writes **Goose SQL migration files**. Generation never opens a
database. Applying migrations remains an explicit, separate command.

For the default project workflow, use [the migration guide](https://cicadel.fauzaanu.com/en/dev/topics/migrations/index.md). The lower-level APIs and standalone tools below operate on the same engine and support existing hosts and history maintenance.

## Try the complete workflow

From this repository:

```sh
cd contrib/migrations/example
go run . migrations:makemigrations notes --dry-run
go run . migrations:makemigrations notes --name initial
DATABASE_URL='postgres://user:password@localhost/app?sslmode=disable' go run . migrate
```

Edit `models/note.go`, adding `Published bool` with the tag
`` `gorm:"not null;default:false"` ``, then:

```sh
go run . migrations:makemigrations notes --name published
go run . migrations:makemigrations notes --check
go run . migrate
```

Keep `DATABASE_URL` in the environment when applying. `--check` exits unsuccessfully
if an unrecorded model change exists; it does not check which files the database
has applied. `--dry-run` prints the candidate file. Both modes leave files and
directories untouched. An unchanged model set writes nothing.

## Install and register

The source module lives at `github.com/fauzaanu/cicadel/contrib/migrations` and has
its own `go.mod`, tests and dependencies. Framework management imports this module as its canonical migration implementation.
There is no published package release yet. For local development, add a project
`replace` for this module to the checkout's `contrib/migrations` directory and
require it at `v0.0.0`; likewise replace the framework with this checkout. Before
publication, pin a released framework version and remove the development replace.
The manifest's `v0.1.0` is the experimental contract version, not an existing tag.

```go
import migrations "github.com/fauzaanu/cicadel/contrib/migrations"

// In your installed-app factory:
generator, err := migrations.New(migrations.Config{
    Apps: []migrations.Definition{{
        Name: "blog",
        Directory: "apps/blog/migrations",
        Models: []any{&blog.Author{}, &blog.Post{}},
    }},
})
// Check err, then register your blog app and generator with cicadel.New.
// Give the blog app Migrations: os.DirFS("apps/blog/migrations") for development,
// or embed the committed migrations in the production executable.
```

Run `migrations:makemigrations blog` through `management.Manager`. The app registers
this as a `BuildCommands` contribution. **Your project builder must inspect
`app.IsBuildCommand(args[0])` before opening the database or acquiring resources.**
The manager bypasses runtime hooks, provider checks and global runtime checks for
these commands. Constructors must also remain free of database work. See the
[runnable host](https://github.com/fauzaanu/cicadel/blob/main/contrib/migrations/example/manage.go) for the full wiring; ordinary existing project
builders are not automatically rewritten by installing this module.

`models.go` and `models/*.go` both work: these are ordinary imported Go structs.
Explicitly register every managed model in `Definition.Models`. Alternatively, generate this registry using the build-time discovery command below. Use the same GORM naming
strategy in `Definition.NamingStrategy` and your runtime GORM configuration.
Each directory belongs to exactly one app. Cross-app relationships require
explicit pinned historical references, described below.

## File format and review

`00001_initial.sql` contains a model-state JSON comment followed by `-- +goose Up`
and `-- +goose Down` SQL. Later files record a hash of the preceding file. The
snapshot and SQL are published together as one file, with an exclusive generation
lock and an atomic no-overwrite publish. Every generation revalidates the history
and regenerates its expected SQL to detect accidental edits. Commit these files.
SQL from old files runs independently of current model definitions.

Review both directions before applying. Down statements can drop tables, columns
and data; their existence is not a promise of lossless rollback. Use the
checksum-aware `Runner` for PostgreSQL transactions, execution history,
locking and schema verification. Rollback uses an explicit target with
`cicadel-migrate -target APP=VERSION -allow-data-loss apply`, as shown below.

Handwritten histories require reviewed per-version snapshots and the explicit
import/adoption protocol below. Legacy generated formats require contiguous
versions; format 3 supports explicit branches and merges. Do not mix unrecorded
SQL into a managed directory. Use recorded data/schema declarations instead.
Generation validates immutable history offline; the runner additionally detects
applied-history changes and managed-schema drift against the live database.
Generation does not prove constraints will fit existing data.

## Supported slice

- PostgreSQL only; single or composite primary keys, optional integer identity.
- Built-in boolean, integer, float, string, byte and time fields; nullable columns,
  string lengths, and scalar defaults. Unsigned 64-bit values are limited to the
  PostgreSQL signed BIGINT range; choose signed types for predictable portability.
- Creating tables; adding ordinary nullable columns or non-null columns with an
  explicit scalar default; adding ordinary/unique indexes.
- Single/composite foreign keys to registered primary or unique keys in the same
  app, including belongs-to/has-one/has-many declarations and reference actions.
  Explicit join models represent relationship tables. All tables and indexes
  are created before foreign keys. Named check constraints are supported.
- Deterministic output, no-op detection, SQL preview, CI checks and reverse SQL.

Undeclared removals, renames and alterations are rejected. Explicit declarations
are described below. Remaining unsupported features include implicit many-to-many join tables,
native user-defined SQL types, unregistered custom storage mappings,
expression/default SQL and advanced indexes. Versioned built-in storage mappings
for custom fields and serializers are supported as described below.
Explicit `gorm:"-"`/migration-ignore fields remain unmanaged. Unsupported GORM
features may still be used with the existing handwritten migration workflow.
Other databases are excluded. Dependency graphs, compatible merges, replacement
bundles, verified baselines and managed-schema drift checks are supported with
the explicit boundaries documented below.

## GORM and GEN

GORM is already the runtime ORM. Normal Go models and GORM queries work today;
`AutoMigrate` is not called. This package generates schema migration files, and
The recorded-history runner applies them. [GORM GEN](https://gorm.io/gen/index.html) can additionally
generate type-safe query APIs from existing structs (`ApplyBasic`), or generate
structs from live tables. It does not replace historical schema diffing. GEN is
not a dependency of this package; the separate [querygen installable](https://cicadel.fauzaanu.com/en/dev/ref/querygen/index.md)
generates queries from authored models and tests GORM version compatibility.

## Verification

```sh
go test -race ./...
go vet ./...
CICADEL_TEST_DATABASE_URL='postgres://.../disposable_test_database' go test -race ./...
```

The PostgreSQL integration test creates and drops its `ym_*` fixture tables and
`cicadel_modeltest_versions`. Use a disposable database. CI runs this nested module
explicitly: root `go test ./...` does not traverse nested modules.

## Build-time model discovery

Opt models in with `//cicadel:model` immediately above their exported struct
declarations. Helpers without that marker are excluded even in `models.go`.
Select the installed app packages explicitly; no runtime directory scanning occurs.
From the consumer module (after installing this module):

```sh
go run github.com/fauzaanu/cicadel/contrib/migrations/cmd/cicadel-models --out modelregistry/models.gen.go ./apps/blog ./apps/blog/models
go run github.com/fauzaanu/cicadel/contrib/migrations/cmd/cicadel-models --check --out modelregistry/models.gen.go ./apps/blog ./apps/blog/models
```

Use `modelregistry.Models()` in `Definition.Models`. The output package must be
separate from model packages to avoid import cycles. Go package/type analysis
respects imports, GOOS, GOARCH and `--tags`; excludes test files; rejects aliases,
generic and unexported model types. Run `--check` for every supported build
configuration before compilation. A configuration with a different model set
requires a separate registry/build configuration, not silently shared history.
`--check` detects a stale or missing registry without writing anything.

The runnable example uses generated discovery; its `models.Models()` function
also demonstrates the explicit-registration escape hatch. Regenerate it from
this module using:

```sh
go run ./cmd/cicadel-models --out example/modelregistry/models.gen.go ./example/models
```

## Declared PostgreSQL changes (format 2)

Explicit declarations were introduced in format 2; new files use format 3.
Format-1 and format-2 files remain readable and byte-for-byte immutable;
the next file links their existing hash. No conversion or rewrite is necessary.

Pass `Options.Changes` in Go or `--changes changes.json` to `makemigrations`.
Declarations run in order before inferred additions. For example, after renaming
`Body` to `Text` in the model (keeping its table name):

```json
[{"kind":"rename_column","table":"demo_notes","name":"body","to":"text"}]
```

```sh
go run . migrations:makemigrations notes --name rename_body --changes changes.json
```

Declarations are one migration's intent, not permanent model settings. Omit them
on subsequent no-op/check runs. Reusing a stale declaration fails validation.

Supported declarations:

- `rename_table` (`table`, `to`) and `rename_column` (`table`, `name`, `to`)
  preserve data and update recorded references. Implicit PK/identity names are
  renamed with their owning objects.
- `drop_table` and `drop_column` require a nonempty `reason` acknowledging data
  loss. Drop dependent indexes/FKs explicitly first; no implicit CASCADE.
- `drop_index`, `drop_check` and `drop_foreign_key` explicitly remove constraints. A changed
  model declaration then adds its replacement through the ordinary diff.
- `alter_column` supplies the complete target `column`. Type/size changes require
  an authored `using` expression and `reason`; `reverse_using` is optional and
  is an author's explicit claim that the reverse conversion is appropriate.
  Making a column non-null requires `backfill`, an SQL expression for NULL rows.
  Defaults may be changed or removed. Primary/identity alterations still fail.
- `data` supplies `up` and optional `down` SQL against historical table/column
  names, independently of current Go types. It must not change schema. SQL is
  trusted developer input; never interpolate request input into declarations.

A removal, backfill, conversion without `reverse_using`, or data operation without
`down` makes the migration irreversible: its Down raises an error. Restore a
backup or write a reviewed forward repair. Even an explicit inverse conversion
may lose information; review it against real data. A reversible schema change
is not a data backup. New constraints may fail against existing rows; PostgreSQL
rolls back the transactional migration, permitting correction and retry.

Preview/check validate all declarations and historical files before writing.
Embedded Goose directives in authored expressions/SQL are rejected. PostgreSQL
checks SQL syntax and data-dependent constraints when applying; offline generation
does not claim to prove arbitrary SQL expressions safe or valid.

## PostgreSQL catalog, execution and recovery

`Load(definitions...)` reads historical files without current models. Its catalog
orders migrations by version-level dependencies plus each app's previous version.
Set `Options.Dependencies` to refs such as `Ref{App: "accounts", Version: 2}` when
creating a dependent migration. Missing dependencies, cycles and inconsistent
applied prefixes fail planning. A target cannot orphan another app's dependency.
Format-3 histories support explicit branches, merges and replacement bundles as
described below; duplicate IDs and missing parent versions are rejected.

A standalone command avoids starting application runtime hooks:

```sh
# From contrib/migrations, after generating example migrations:
go run ./cmd/cicadel-migrate -app notes=example/migrations show
go run ./cmd/cicadel-migrate -app notes=example/migrations -offline sql
DATABASE_URL='postgres://.../app' go run ./cmd/cicadel-migrate -app notes=example/migrations plan
DATABASE_URL='postgres://.../app' go run ./cmd/cicadel-migrate -app notes=example/migrations apply
DATABASE_URL='postgres://.../app' go run ./cmd/cicadel-migrate -app notes=example/migrations verify
# Roll back to a target after reviewing its Down SQL and taking a backup:
DATABASE_URL='postgres://.../app' go run ./cmd/cicadel-migrate -app notes=example/migrations -target notes=1 -allow-data-loss apply
```

Repeat `-app APP=DIRECTORY` for **every managed app**, including dependencies.
`show` inspects artifacts offline; `status` reports applied/pending state from the
database. `migrate`, `showmigrations` and `sqlmigrate` alias `apply`, `status` and
`sql`, respectively. `plan` and `sql` compare against applied history.
`-offline` explicitly assumes a fresh database. `-timeout` bounds connection,
lock and execution time. Plans are rechecked under the shared Goose advisory lock.

The new `Runner` maintains `cicadel_migration_checksums` alongside existing
`cicadel_APP_versions` Goose history. Every migration's SQL, checksum and Goose
record commit atomically. A failure rolls back that migration; previously
committed migrations remain applied. Correct the data prerequisite and retry the
same immutable SQL. Do not edit an applied migration or rewrite its checksum.
Missing applied files, modified checksums, inconsistent dependencies and drift
fail explicitly. A commit error is reported as uncertain: inspect before retrying.

For an existing **generated** Goose history, use `adopt` once. Adoption verifies
the actual managed schema against the snapshot of the applied prefix before
recording checksums. It never marks pending migrations applied or overwrites
checksums. Unrecorded handwritten histories are not automatically adopted. Existing schemas
without history use the explicit initial-baseline workflow below.

Schema verification renders expected tables in a randomly named scratch schema
inside a transaction that is always rolled back. It requires database CREATE
privilege. Managed columns, defaults, identities, keys, constraints, indexes,
user-trigger presence and row-level-security enablement are compared; unrelated
tables are ignored. This is schema verification, not verification of row contents,
permissions, sequence counters, every PostgreSQL extension feature or external
processes that change schema without taking the migration lock.

Management commands and the standalone tooling use this same checksum-aware runner. The former core Goose executor has been removed. Existing Goose records require verified adoption before further migrations; applied SQL is never silently replayed.

### Authored schema SQL and initial baselines

`Record(definition, historicalState, options)` is the model-independent recording
API. `RecordData(definition, up, down, options)` uses the latest recorded state
without loading models. Both use the same atomic file publication and hash chain.

For handwritten schema SQL, a `Change{Kind: "schema", After: &state, Up: sql,
Down: reverseSQL, Reason: "..."}` records the complete resulting historical
state. Missing Down makes it irreversible. The transactional runner verifies
that SQL actually produced the declared state **before committing**; it also
checks tables declared removed are gone. Only schema features represented by
`State` can be verified. Transaction-control statements and concurrent DDL are
rejected. Explicit handwritten SQL is trusted developer code.

To adopt an existing database with no migration history, generate schema-only
initial files from its intended models, review them, then run:

```sh
DATABASE_URL='postgres://.../app' go run ./cmd/cicadel-migrate -app notes=example/migrations -reason 'Adopt existing release after schema review and backup' baseline
```

Baseline requires exact managed-schema verification and no existing Goose history
for those apps or checksum-managed history in the database. Include all initial
apps in the same operation. Only generated initial schema files are eligible;
authored data SQL and later versions cannot be skipped this way. Initial SQL is
**not executed**. `cicadel_migration_baselines` permanently records the checksums,
reason and time of adoption, separately from execution history. Existing rows
remain intact. Later migrations execute normally. Existing Goose histories use
`adopt` instead; neither workflow can rewrite an existing checksum.

### Branches and replacements (format 3)

New files now use format 3, recording explicit parent IDs and hashes. Formats 1
and 2 remain readable without rewriting. Version IDs are unique positive integers;
format-3 IDs may have gaps so independently developed branches can allocate
separate IDs. Legacy formats retain their original contiguous-history contract.

Use `--parent VERSION --version NEW_ID` to generate a deliberate branch. On
combining branches, ordinary generation/application rejects multiple heads.
Create an explicit merge using `makemigrations --merge` with the combined models,
or, independently of models:

```sh
go run ./cmd/cicadel-migrate -app notes=example/migrations -name merge_branches merge
```

Compatible disjoint schema changes merge deterministically. Concurrent changes
to the same schema object, including duplicate additions, fail rather than
inventing a destructive resolution. Resolve unapplied conflicting work explicitly;
never edit a migration already applied to any deployment. Authored data SQL still
requires human review for semantic conflicts; schema snapshots cannot prove data
operations commute. Target versions select their ancestor closure, not every
smaller numeric ID. Rollback follows reverse dependency order.

Record a cross-app dependency with `--depends accounts:2` (repeat as needed), or
`Options.Dependencies`. All dependency apps must be supplied when loading/applying.
Data SQL can be recorded offline with `-up-file` and optional `-down-file` followed
by the `data` command; it reads historical state without importing current models.

Consolidate an app's ancestor history into a replacement:

```sh
go run ./cmd/cicadel-migrate -app notes=example/migrations -target notes=3 -name initial_history squash
```

The `.squash.json` bundle is canonical, checksummed and verified against every
original source. Fresh installations execute an eligible consecutive bundle in
one transaction and audit it in `cicadel_migration_replacements`. Partially applied
histories execute remaining originals, preserving data SQL order. Cross-app
ordering that interleaves bundle members also falls back to originals. Rollback
uses original reverse operations. SQL is consolidated, not optimized away.
**Retain the originals and the bundle**: deletion of either executed history is
an error, and originals remain necessary for older deployments. This workflow
supports replacement without assuming all users have already upgraded.

### PostgreSQL capabilities and nontransactional recovery

`Renderer("postgres")` exposes the versioned `SchemaRenderer` contract and its
capabilities. Other databases are rejected. `Render` is offline and accepts
historical before/after states and explicit declarations. PostgreSQL 18.4 is the
locally tested version; CI runs PostgreSQL 18. Ordinary schema operations run
transactionally. No support for unrelated database providers is implied.

One explicitly supported nontransactional operation is a single concurrent index
addition, selected with `Options.ConcurrentIndex` or
`makemigrations --concurrent-index INDEX_NAME`. Its reverse drops that index
concurrently. Mixing it with other schema changes is rejected before publication;
arbitrary nontransactional SQL and transactional replacement of concurrent-index
migrations are unsupported.

Before concurrent DDL, the runner commits a dirty marker. Failure or interruption
blocks ordinary apply/plan until recovery is deliberate. For a failed unique
index, inspect PostgreSQL's invalid index and duplicate rows, repair the data and
remove the known failed index explicitly. Then:

```sh
# Certify that the pre-migration schema is restored; this does not rerun SQL:
DATABASE_URL='postgres://.../app' go run ./cmd/cicadel-migrate -app notes=example/migrations -target notes=3 -recovery retry recover
# Retry the immutable migration:
DATABASE_URL='postgres://.../app' go run ./cmd/cicadel-migrate -app notes=example/migrations apply
```

If DDL completed but the process stopped before recording completion, use
`-recovery complete` instead. It records completion only after verifying the
post-migration schema. Both actions are audited in `cicadel_migration_recoveries`.
Neither action blindly executes SQL, discards a dirty marker, drops an index, or
claims transactional rollback of nontransactional work.

### Cross-app model relationships

Pin the referenced app's recorded schema in the dependent definition:

```go
migrations.Definition{
    Name: "notes", Directory: "apps/notes/migrations",
    Models: []any{&notes.Note{}},
    References: []migrations.ReferenceSource{{
        App: "accounts", Version: 1, Directory: "apps/accounts/migrations",
    }},
}
```

The referenced model may appear in a GORM relationship field, but do not register
its table as owned by both apps. Generation resolves the pinned historical state,
records it in the dependent snapshot, and automatically adds the version-level
dependency. Catalog loading checks that the recorded referenced snapshot matches
the referenced migration. Every dependency app must be supplied to the runner.

Cross-app destructive changes require deliberate ordering: for example, first
record removal of a dependent FK, then rename the referenced table with a dependency
on that removal, then add the FK against the new pinned snapshot. The planner
will not guess how to coordinate a destructive change across apps. Explicit join
models with composite keys can refer to multiple app snapshots.

## Project initialization and runnable lifecycle

The [project workflow guide](https://cicadel.fauzaanu.com/en/dev/topics/migrations/index.md) installs the commands with
Go tool directives and initializes an offline entrypoint. Run
`go tool cicadel-migrations-init -app blog=./apps/blog` after marking authored
models. Existing files are never overwritten. The separate-consumer test exercises
actual local module installation, discovery across `models.go` and `models/`,
regeneration checks, offline migration generation and PostgreSQL application.

From this module, run the complete disposable lifecycle:

```sh
DATABASE_URL='postgres://.../disposable_database' go run ./example/workflow
```

It preserves generated artifacts for review and removes its own uniquely named
schema. The walkthrough covers initial generation/application, a cross-app FK,
rename/rollback/reapply, conversion/backfill, historical data SQL, transactional
failure/retry and concurrent-index repair/recovery. Its integration test runs in CI.

### Importing an existing handwritten history

`ImportGoose(definition, []LegacySnapshot{...})` requires a reviewed schema state
and reason for **every** handwritten SQL version. It preserves all SQL bytes and
writes `.cicadel-legacy.json`, binding file hashes, historical states and declared
dependencies. `Reversible` defaults to false; a legacy Down is not evidence that
lost rows can be recovered. Later generated files link the imported fingerprints.

The CLI accepts the corresponding JSON array:

```sh
go tool cicadel-migrate -app blog=apps/blog/migrations -states-json reviewed_states.json import
DATABASE_URL='postgres://.../existing_app' go tool cicadel-migrate -app blog=apps/blog/migrations adopt
```

Existing applied prefixes are verified against their actual recorded schema
before adoption. Fresh installations execute the original SQL and generated
upgrades. Tests cover fresh, initial-prefix and fully-applied legacy histories.
Editing either imported SQL or its metadata changes the migration fingerprint.

Supported import directives are transactional Up/Down and
StatementBegin/StatementEnd. NO TRANSACTION, environment substitution, unknown
directives, transaction control and CRLF source files fail before publication;
convert unsupported histories deliberately rather than silently changing their
execution semantics. Snapshots must use supported PostgreSQL state metadata.
Adoption verifies schema, not historical row contents: reviewing old data SQL and
its provenance remains the operator's responsibility.

### Package-owned embedded histories

Runtime catalogs can use `Definition{Name: "packageapp", Source: migrationFS}`
with an `fs.FS` rooted at that app's migrations. This supports independently
installed packages and executables without source-tree access. Use
`//go:embed all:migrations` and `fs.Sub` when importing histories: the `all:` prefix
includes `.cicadel-legacy.json`. Generation/import/squash require a writable source
checkout and reject an embedded source. They never write into a dependency cache.
The separate-consumer integration test applies an embedded package history.

### Custom fields and serializers

Register explicit storage metadata in `Definition.CustomTypes`, keyed by the
explicit GORM `type` tag when present, otherwise its field data type. For example:

```go
CustomTypes: map[string]migrations.CustomType{
    "uuid": {Version: 1, SQL: "uuid"},
    "jsonb": {Version: 1, SQL: "jsonb"},
    "numeric": {Name: "money", Version: 1, SQL: "numeric(12,2)"},
},
```

`SnapshotWithOptions` accepts the same registry through `SnapshotOptions`.
Mappings use supported built-in PostgreSQL storage types and record their version
in historical state. Storage changes require explicit alteration declarations;
old SQL replays without the current custom Go field implementation. Native enum
and domain lifecycle management and arbitrary custom SQL types are unsupported.

## Handwritten schema metadata

Reviewed import snapshots can represent serial primary keys, named UNIQUE
constraints and the `now()` timestamp default. These are explicit historical
metadata, not inferred from the live database during generation. Verification
compares their PostgreSQL catalog signatures, including constraints and sequence
properties. Other SQL default expressions remain unsupported. Original SQL bytes
and applied versions are preserved during verified adoption.