canopy-e2e-db-drift-503

Answer

Root cause: canopyd was started against localhost:5432 (empty fresh PG) instead of the canonical compose canopy-pg:5437. The fresh DB had schema (trees, tree_members, FK tree_members_user_id_fkey) but no seeded dev JWT user, so the CreateTree transaction did: INSERT tree ✅ → INSERT tree_members ❌ FK 23503 → tx rollback → error mapped to ErrDatabaseUnavailable → 503.

Fix (primary): idempotent dev-user seed. Seed the dev JWT user as part of schema bootstrap so any fresh DB converges to the canonical state.

migrations/0003_seed_dev_user.sql:

-- Dev JWT user must exist before tree_members rows can reference it.
-- ON CONFLICT makes the seed idempotent: fresh DB inserts, existing DB no-ops.
INSERT INTO users (id, email, display_name, role, jwt_sub)
VALUES (
  '00000000-0000-0000-0000-00000000dead', -- dev JWT user id E2E tokens resolve to
  '<email>',
  'Dev User',
  'admin',
  'dev-jwt-subject'
)
ON CONFLICT (jwt_sub) DO NOTHING;

Go bootstrap (pgx/v5), run after migrations, before serving:

// seedDevUser is safe to call on every boot and on both fresh and canonical DBs.
func seedDevUser(ctx context.Context, pool *pgxpool.Pool, cfg Config) error {
    _, err := pool.Exec(ctx, `
        INSERT INTO users (id, email, display_name, role, jwt_sub)
        VALUES ($1, $2, $3, $4, $5)
        ON CONFLICT (jwt_sub) DO NOTHING`,
        cfg.DevUserID, cfg.DevEmail, "Dev User", "admin", cfg.DevJWTSub)
    return err
}

Fix (hardening): stop masking constraint errors as "db unavailable." In the error mapper, inspect the pgx error before classifying:

func classifyDBError(err error) error {
    if err == nil {
        return nil
    }
    var pgErr *pgconn.PgError
    if errors.As(err, &pgErr) {
        // 23xxx constraint class — the DB is UP; the data is wrong. Not a 503.
        if strings.HasPrefix(pgErr.Code, "23") {
            return &APIError{Status: http.StatusConflict, Code: "constraint_violation",
                Constraint: pgErr.ConstraintName, Err: err}
        }
        return &APIError{Status: http.StatusBadRequest, Code: pgErr.Code, Err: err}
    }
    // Only real connectivity failures are ErrDatabaseUnavailable -> 503.
    if errors.Is(err, io.EOF) || errors.Is(err, net.ErrClosed) ||
        strings.Contains(err.Error(), "connection refused") {
        return ErrDatabaseUnavailable
    }
    return err
}

Fix (prevention): (a) bind DATABASE_URL explicitly from the compose config — no default localhost:5432 that silently points at the wrong PG; (b) add a boot-time probe that replays the CreateTree-shaped tx (insert tree → insert tree_members) and fails startup loudly on FK drift instead of surfacing in E2E as a 503.