mirror of
https://github.com/agent-substrate/substrate.git
synced 2026-10-02 03:24:42 +08:00
Jeremy Alvis
f100c27286
> [!WARNING] > Recreate PostgreSQL databases from earlier development builds. ## Summary This change replaces startup schema setup with embedded, versioned SQL migrations. `ateapi` uses Goose to apply migrations before readiness. Goose stores one ledger record for each applied migration. Goose runs each migration and inserts its ledger record in one PostgreSQL transaction. Closes #901. Based on this design: https://docs.google.com/document/d/13ixDKRoAIFXeLxS-_1nikNcAy76ca8m0eVgobfib93E/edit?usp=sharing ## Migration behavior `ateapi` gets a session advisory lock for the configured schema before it applies pending migrations. One replica applies migrations while other replicas wait. Goose reads the ledger again after it gets the lock. If a migration fails, PostgreSQL rolls back its SQL and ledger record. Earlier successful migrations remain applied and recorded. Kubernetes restarts the failed replica. The next startup resumes from the first migration without a ledger record. ## Changes - Add Goose and a per-migration ledger. - Replace the initial up and down files with one transactional, up-only migration. - Keep migration 1 aligned with the current schema, including actor egress policy storage. - Remove existence guards and explicit transaction statements from the baseline migration. - Apply all pending migrations before `ateapi` becomes ready. - Serialize each migration run with a PostgreSQL session advisory lock. - Start without changes when the database schema is current or ahead. - Reject application tables that do not have a migration ledger. - Log the starting, current, and latest versions. - Log the applied migration count and duration. - Retry only initial database connection failures. - Return schema and migration errors without a retry. - Add `--postgres-schema` and `ATE_API_POSTGRES_SCHEMA`. - Use `public` as the default PostgreSQL schema. - Use the configured schema for the main and watch pools. - Restrict outbox partition maintenance to the configured schema. - Let the installer use an external PostgreSQL database. - Add the migration design and recovery policy to the repository. ## Migration file policy Migration files use sequential versions and contain exactly one Goose `Up` section. CI rejects down migrations, nontransactional migrations, environment substitution, explicit transaction control, and `IF NOT EXISTS` guards. Before the first stable v1 release, developers can change or squash migrations. Developers must recreate databases after migration history changes. After that release, CI rejects changes or deletions against the latest stable release tag that contains migrations. Goose does not store migration checksums. The binary embeds each migration file, and release-tag checks protect released migration history. ## Compatibility No release includes PostgreSQL support. The `v0.0.0` release predates the PostgreSQL backend. Users must recreate databases from earlier PostgreSQL development builds. Every committed migration prefix must work with the current and previous `ateapi` releases. This rule supports rolling upgrades and temporary binary rollback. A binary rollback does not roll back the database schema. ## Testing Tests cover: - Fresh database migration. - Concurrent startup. - Advisory lock waits. - Current and ahead database schemas. - Rejection of application tables without a migration ledger. - Atomic rollback of a failed migration. - Retention of earlier successful migrations. - Resume from the failed migration after restart. - Configured schema isolation. - Outbox partition isolation. - Migration file policy checks. - Stable release migration immutability.