Migrations

go-auth owns its auth tables and migration history. Run auth migrations through the matching goauth CLI, separately from the application's schema and ORM migrations. Both may use the same logical database and different connection pools. New never migrates. The Go API supports explicit deployment execution without constructing Auth.

The catalog begins with baseline 000001_v0.4.0_001.sql for PostgreSQL, MySQL and SQLite. Revision 2, 000002_v0.5.0_001.sql, adds organization-local permission definitions, roles, grants, membership assignments, invitation role snapshots and revision state. It is additive: existing credentials, memberships, invitations and application roles are preserved. Existing organizations are not automatically initialized for granular permissions. Their owners initialize the feature explicitly after deployment; new organizations initialize at creation when the feature is enabled.

Revision 2 is required when upgrading to v0.5.0 even if org permissions remain disabled. With organizations enabled, organization deletion cleans up role references and fixed-role changes reconcile assignments through the new tables. The feature flag does not make revision 1 compatible. With startup schema checking disabled, an unmigrated deployment can start and then fail these operations with database errors. Apply and verify the migration before auth traffic resumes; see the v0.5.0 rollout notes.

Baseline adoption still requires the complete revision-1 schema, followed by migrate to execute pending upgrades. A database from an older release must match that supported baseline before adoption; its installed Go version does not identify its database layout. Published revision-1 SQL and checksums are unchanged.

The published v0.3.0 SQL at commit c289be372e62f84cabe3f3ee74eea1a67ed01db2 has the same schema checksums as these baseline fixtures for all three dialects. Actual databases still require live adoption validation: the old bootstrap could have left partial or modified objects. Earlier schema layouts require a reviewed reconciliation procedure.

Exported artifacts

From an application module with go-auth pinned in go.mod:

go run github.com/nazimdjebloun/go-auth/cmd/goauth generate \
  --driver postgres --out goauth

goauth/
  auth.schema.sql
  reference.md
  migrations/
    catalog.json
    000001_v0.4.0_001.sql
    000002_v0.5.0_001.sql

--out is a directory and defaults to goauth. auth.schema.sql is the desired schema for that release, never an applied-state snapshot. The database journal is authoritative for applied state. Generation never connects to a database. It refreshes the desired schema/reference and adds published migration files; it refuses to replace changed published SQL, a different dialect, or a newer or mismatched catalog. Use separate directories for different dialects.

The old goauth generate --out file.sql CLI behavior is replaced by directory export. The Go GenerateSchema(driver, path) function remains a raw single-file bootstrap export for inspection. Do not use raw bootstrap SQL or a fresh diff to replace auth upgrade history. Exported files are review artifacts; the runner executes its embedded catalog rather than arbitrary files from disk.

Fresh installation and upgrades

Run commands from the application's module without @latest, so the selected CLI follows its go.mod dependency. From the go-auth source checkout, use go run ./cmd/goauth. An installed CLI must be pinned to the library's exact release. Version-suffixed go run/go install commands select that release independently of the application's dependencies.

go run github.com/nazimdjebloun/go-auth/cmd/goauth migrate \
  --driver postgres --dsn "$DATABASE_URL"

On a fresh database, all reserved auth tables and explicitly named auth indexes must be unused. Unrelated application tables may already exist. The runner acquires migration serialization before inspecting history or initializing the journal. It installs the published baseline and records completion.

On a tracked database, history must be a checksum-matching prefix of the embedded catalog. The runner verifies the previous completed schema and data postconditions, then executes pending IDs in increasing numeric order. A repeat verifies existing state and skips completed SQL. Published migrations own renames and backfills; matching the desired DDL alone cannot establish correct data transformation.

Stop auth traffic and all auth-writing workers before execution. This first implementation does not provide online backfill jobs or generic rolling schema compatibility. Use deployment credentials for DDL and runtime credentials for ordinary operations. The runner borrows Go database handles; the CLI owns and closes the handle it opens.

Adopting an existing installation

An existing schema made by the old bootstrap executor has no recognized history. migrate refuses to infer its baseline from a users table or dependency version. Adoption explicitly selects and validates a supported baseline:

go run github.com/nazimdjebloun/go-auth/cmd/goauth migrate adopt \
  --driver postgres --dsn "$DATABASE_URL" --baseline 1

Currently only baseline 1 is supported. Adoption verifies tables, columns, types, defaults, nullability, collations, constraints and indexes before recording an adopted baseline. It does not execute baseline creation SQL or rewrite auth data. Missing, partial or modified schemas stop with diagnostics. Reconcile them through a reviewed procedure or restore a compatible backup before adoption; there is no force flag or destructive automatic diff.

SQLite table adoption compares normalized complete definitions with the supported snapshot. A manually rebuilt but differently declared table may require reviewed reconciliation even if some queries behave alike. Added columns, constraints, indexes or triggers on auth tables are outside the supported ownership contract.

History, status and startup compatibility

The reserved goauth_migrations table journals each migration's ID, baseline ID, dialect, filename, SQL and verification-schema checksums, execution/adoption mode, started/complete state, and UTC Unix-second timestamps. Numeric IDs order execution; the release and per-release sequence in the filename are metadata. A release with no schema changes keeps the same target revision.

go run github.com/nazimdjebloun/go-auth/cmd/goauth migrate status \
  --driver postgres --dsn "$DATABASE_URL"
go run github.com/nazimdjebloun/go-auth/cmd/goauth migrate verify \
  --driver postgres --dsn "$DATABASE_URL"

status reads history and pending IDs as JSON without DDL or journal initialization. Missing history is an error; dirty history is reported. Unknown IDs, ordering or identity errors and checksum mismatches are rejected. verify requires clean history at the target revision, holds migration serialization, and checks live schema definitions and published data postconditions.

Structural checks also require reserved names absent from the inspected snapshot to be absent from the database. Ownership comes from every snapshot in the embedded catalog, so verification and repair reject obsolete tables left behind by an incomplete drop or rename. Starting-schema checks also reject partial additions from pending steps. Unrelated application objects remain outside these checks.

New always checks connectivity and history before constructing services or starting workers, using the shared ten-second startup database timeout. This also applies to borrowed databases and pools. The compatibility check accepts only the library's completed target revision with recognized history/checksums. It rejects older, newer/unknown, missing and dirty state. It does not certify absence of live drift; use deployment verification for that.

Database: goauth.DatabaseConfig{
    URL:         databaseURL,
    Driver:      goauth.DriverPostgres,
},

The standalone CheckSchema(ctx, db, driver) API accepts a caller context for an explicit compatibility check. Migration errors support errors.Is with ErrUntrackedSchema, ErrDirtyMigration, ErrIncompatibleSchema, ErrSchemaDrift, ErrUnknownMigrationHistory, ErrMigrationChecksum and ErrMigrationLockTimeout. MigrationError supplies dialect, migration ID, phase, dirty/uncertain flags and a wrapped infrastructure cause. CLI output omits raw SQL and driver messages; programmatic callers must redact potentially sensitive driver causes.

Transactions, locks and recovery

The maintained backend fixtures target PostgreSQL 18, MySQL 8.4 and the bundled modernc SQLite driver. This implementation does not alter existing auth table definitions; it freezes the current complete schema as the first baseline.

CLI defaults are --timeout 30m, --lock-timeout 30s, and --statement-timeout 5m. All CLI timeout values must be positive. Go migration options use zero to select the 30-second lock and five-minute statement/phase defaults; the caller context supplies the overall timeout. Each migration SQL statement gets its own statement-timeout budget. Each verification phase shares one budget of the same duration across structural checks, reserved-name absence, foreign-key scans and data postconditions. Journal structural validation and fresh-install name-collision preflight each get a separate shared budget. A phase's budget is not renewed for each query or row; an earlier caller deadline or cancellation still takes precedence. Journal-existence and history reads outside verification use the caller context. Cleanup has an independent five-second budget to release locks and restore settings even after cancellation.

Lock waits honor cancellation and include reserving a physical connection. PostgreSQL lock/statement settings, MySQL metadata-lock/FK settings and SQLite busy/FK settings are restored before returning a borrowed connection. A connection whose cleanup or lock ownership is uncertain is discarded from the pool.

A nontransactional failure blocks subsequent migration execution. Inspect the exact published step and actual database state while auth traffic is stopped. Follow a reviewed reconciliation procedure to establish all schema and data postconditions, or restore a compatible backup. Do not blindly replay SQL or edit the journal to hide an error. A lost commit acknowledgement can leave an uncertain outcome; inspect history and actual state before deciding how to recover.

After completing reconciliation, verified recovery records completion:

go run github.com/nazimdjebloun/go-auth/cmd/goauth migrate repair \
  --driver mysql --dsn "$DATABASE_URL" --id 1

repair accepts only the exact last incomplete ID. It executes no migration SQL and succeeds only after that step's structural and data checks pass. It cannot repair unknown history, changed checksums, or a partially populated backfill. Transactional failures roll back and can be retried after resolving their cause; an uncertain outcome always needs inspection first.

An incomplete nontransactional upgrade may also publish a retry policy in the exported catalog. Read its instructions, reconcile the database to the previous completed schema and the declared starting data conditions, then run:

go run github.com/nazimdjebloun/go-auth/cmd/goauth migrate retry \
  --driver mysql --dsn "$DATABASE_URL" --id 42

The ID is illustrative; use the actual last incomplete ID. retry holds the migration lock while checking history, the previous schema and its data postconditions, the step's preconditions, and its retry.checks. All checks must pass before SQL executes. It replays that step in full, preserves its original journal row and start time, and verifies the target before recording completion. Later steps remain pending; run migrate to continue. A second failed attempt remains dirty.

Partial DDL is refused until the published starting structure is restored. Reconciliation must also account for data and external effects; a matching table definition alone does not establish safe replay. There is no arbitrary statement offset, force flag, or automatic undo. The initial catalog publishes only the baseline, which has no retry policy. Future retry policies need actual-engine upgrade and recovery coverage before release.

Migrations are forward-only. Application rollback depends on actual schema/data compatibility; there are no down scripts or arbitrary future-schema allowances. Test backup restoration and account for restored revocations, consumed one-time tokens and durable audit obligations in the recovery procedure.

Application and ORM ownership

Auth uses fixed names such as users, sessions and organizations; deployments must resolve naming conflicts on their side. Names appearing in any catalog snapshot remain reserved, including retired names and pending additions. The desired schema and generated reference list the target objects; the journal goauth_migrations is also reserved. Explicit index names are schema-wide in PostgreSQL/SQLite and table-scoped in MySQL. SQLite compares reserved table, index and view names without regard to ASCII case. Triggers on application tables may share reserved names; triggers on auth tables are rejected. Use extension tables for application fields. The current declared stable FK targets are users.id and organizations.id; changing a declared stable target requires a major-version change. The reference publishes the selected dialect's key types. MySQL child FK columns must match the auth type and collation.

For PostgreSQL, put auth objects in the first existing schema selected by the connection's search path (current_schema()). Use the same search-path contract for migration and runtime connections. The library does not rewrite query names or offer configurable prefixes. Avoid shadowing auth names in another visible schema. Shared extensions must be available to that path.

Run auth migration first, then application migrations that create child foreign keys. A future auth change affecting host constraints needs a separately reviewed ordered procedure; separate histories do not make the two runners atomic. Do not let ORM auto-migration or an application diff alter auth objects. Host-runner adapters, copied-SQL execution and generated ORM models are outside this phase.

Go deployment API

status, err := goauth.Migrate(ctx, db, goauth.MigrationOptions{
    Driver:           "postgres",
    LockTimeout:      30 * time.Second,
    StatementTimeout: 5 * time.Minute,
})
if err != nil {
    return err
}
_ = status

AdoptSchema takes BaselineAdoptionOptions; RepairMigration takes MigrationRepairOptions; RetryMigration takes MigrationRetryOptions with the exact incomplete MigrationID. GetMigrationStatus reads the journal. VerifySchema checks the tracked target under serialization. GetMigrationCatalog returns an independent copy of validated metadata and SQL. ExportSchema(driver, directory) exports artifacts without database access. Database operations accept *sql.DB, never construct Auth, and never close a caller-owned database.

Authoring later migrations

The maintainer authoring tool drafts SQL with pinned Atlas/development versions, validates replay before sealing new catalog entries, and handles release metadata updates without empty migration files. It operates on a maintainer checkout; application deployments use the standalone runner.

Maintainers update all affected canonical schemas, append a globally increasing ID for each step, and retain published SQL/verification fixtures byte for byte. The filename form is 000042_v0.4.0_001.sql; semantic versions do not determine execution order. Do not insert a backport behind an already published ID. A parallel release line with divergent history needs an explicit future policy.

Catalog metadata declares transaction mode, target structural fixture, postconditions, optional starting-data preconditions and opt-in retry policies. Failed preconditions or retry.checks stop initial execution before the step's started record or SQL. Preconditions use the statement timeout as a shared validation budget. ErrMigrationPrecondition identifies that failure; ErrMigrationRetryUnsupported identifies a step without a retry contract. Each SQL statement begins with -- goauth:statement on its own line. This reserved marker must never occur inside a statement body; compound functions/triggers and dollar-quoted blocks remain one authored unit. Historical verification fixtures remain internal; public export has one complete desired schema. Snapshot parsing supports the checked-in formatting and expression subset and fails closed on unsupported definitions. It is not a general SQL diff or arbitrary migration-file parser.

Backfills must include explicit data postconditions: each catalog check is a SELECT returning exactly one true value. Preserve hash/pepper pairs, nullable OAuth passwords, token hashes, expiry/revocation/consumption state, provider uniqueness, role assignments and audit obligations. Migrations cannot reconstruct encryption keys or regenerate credential material safely. Large/resumable live backfills and expand/contract rolling upgrades require a future documented and tested execution protocol; do not infer them from the first baseline runner.

Redis integration upgrade

Revision 5 adds internal cache_version columns to sessions and workflow tokens on all dialects. Existing rows keep their credentials and start at legacy; they use SQL until changed normally. Deploy version-aware code to every writer before enabling Redis, and disable caches before rolling back to older writers. See Redis rollout.