v1.2.0 migrate fails on v2-era schemas: CREATE INDEX on workspace_uuid runs before ALTER ADD COLUMN

[idris-saddi]

Summary

event.store.postgres.go and command.store.postgres.go both run a CREATE INDEX ... ON workspace_uuid in the same ExecContext as CREATE TABLE IF NOT EXISTS events(...). Against a pre-existing v2-era schema (where the table exists without the workspace_uuid column), the index creation fails before the subsequent ALTER TABLE ADD COLUMN IF NOT EXISTS workspace_uuid in a separate Exec.

Net effect: any environment upgrading from comby-store-postgres v1.1.x (or comby v2.x) to v1.2.0 / comby v3 against a persisted Postgres volume fails at facade init with:

init event store: pq: column "workspace_uuid" does not exist

Affects events + commands stores symmetrically.

Repro

1. Boot any comby v2.x app + comby-store-postgres v1.1.x against a fresh Postgres → v2 schema created (no workspace_uuid column on events / commands).
2. Stop the app, bump to comby v3 + comby-store-postgres v1.2.0 while keeping the same Postgres volume.
3. Boot → init fails as above.

Fresh Postgres setups (no pre-existing volume) are not affected, because the table is created on the first ExecContext with the column already in place — that's why the CI suite passes but persistent dev / staging environments hit this.

Code location

event.store.postgres.go#L113-L161:

func (es *eventStorePostgres) migrate(ctx context.Context) error {
    query := `
    CREATE TABLE IF NOT EXISTS events (
        ...
        workspace_uuid TEXT,
        ...
    );
    CREATE INDEX IF NOT EXISTS "workspace_index" ON "events" (
        "workspace_uuid" ASC                                  -- ← fails if table predates this column
    );
    ...
    `
    if _, err := es.db.ExecContext(ctx, query); err != nil {
        return err                                            -- ← returns here on v2-era schemas
    }

    // The fix-up ALTERs only run AFTER the CREATE INDEX above:
    if _, err := es.db.ExecContext(ctx, `ALTER TABLE events ADD COLUMN IF NOT EXISTS req_ctx TEXT`); err != nil {
        return err
    }
    if _, err := es.db.ExecContext(ctx, `ALTER TABLE events ADD COLUMN IF NOT EXISTS workspace_uuid TEXT`); err != nil {
        return err
    }
    return nil
}

Same pattern at command.store.postgres.go#L107-L142. snapshot.store.postgres.go has a similar migrate() (lines ~140-170) which I haven't yet confirmed bites — snapshots is opt-in so few environments will be affected, but the same ordering looks present.

Suggested fix

Reorder so the additive ALTER TABLE ... ADD COLUMN IF NOT EXISTS runs before the CREATE INDEX on that column. The CREATE TABLE IF NOT EXISTS is a no-op against existing tables, and ADD COLUMN IF NOT EXISTS is a no-op against tables that already have the column — together they're idempotent for both fresh and pre-existing schemas.

Concretely, either:

(a) split the CREATE INDEX ... ON workspace_uuid out of the main CREATE TABLE block and run it as a separate Exec after the ALTER:

// 1) CREATE TABLE IF NOT EXISTS + indexes that don't depend on the new column
if _, err := es.db.ExecContext(ctx, createTableAndStableIndexes); err != nil { return err }
// 2) ALTER COLUMN IF NOT EXISTS to backfill any columns added in this release
if _, err := es.db.ExecContext(ctx, `ALTER TABLE events ADD COLUMN IF NOT EXISTS workspace_uuid TEXT`); err != nil { return err }
// 3) NOW it's safe to create the dependent index
if _, err := es.db.ExecContext(ctx, `CREATE INDEX IF NOT EXISTS "workspace_index" ON "events" ("workspace_uuid" ASC)`); err != nil { return err }

or (b) keep the single big query string but with the ALTERs interleaved before the dependent CREATE INDEXes (the whole thing runs as one statement so Postgres handles ordering within it).

(a) is more readable IMO and matches the existing "structural CREATE, then per-release ALTERs" mental model.

Workaround for affected environments

Apply manually against the events + commands DBs before booting the v3 app:

ALTER TABLE events   ADD COLUMN IF NOT EXISTS workspace_uuid TEXT;
ALTER TABLE commands ADD COLUMN IF NOT EXISTS workspace_uuid TEXT;

After that, migrate() becomes idempotent (table exists, column exists, index creates fine, ALTER is a no-op).

Context

Discovered while migrating gradientzero/clmpilot-backend#60 to comby v3. All other v3 surfaces audited cleanly; this was the only blocker for upgrading environments with persisted v2 data. Documented the workaround in that PR's body. Happy to send a PR with the fix if you'd like — straightforward reorder.

🤖 Filed via Claude Code from a clmpilot-backend v3 migration session.