go-database-case-insensitive-duplicate-registration

Answer

Root cause. CreateProject only ran a case-insensitive duplicate guard on workdir (LOWER(workdir)=LOWER(?) against enabled rows). name is TEXT PRIMARY KEY with SQLite's default BINARY collation, so 'HEADING' and 'heading' are distinct rows to the database. Two ghosts slipped through: a different-casing name with a different workdir (the workdir guard passed), and any name whose existing row was disabled/archived (the guard only considered enabled rows).

Fix. Mirror the workdir guard for names — LOWER(name)=LOWER(?), rejecting only when the existing row is enabled, allowing re-registration when the existing row is disabled/archived — and return the same ErrProjectExists sentinel so the HTTP layer's existing 409 Conflict mapping ("project already exists" phrase) is reused unchanged.

var ErrProjectExists = errors.New("project already exists") // -> 409 mapping phrase

func (s *Store) CreateProject(name, workdir string) (Project, error) {
    name = strings.TrimSpace(name)
    workdir = strings.TrimSpace(workdir)
    if name == "" || workdir == "" {
        return Project{}, errors.New("name and workdir are required")
    }

    // 1) NEW: case-insensitive NAME duplicate check, mirroring the workdir
    //    check below. Disabled/archived rows don't block (they're ghosts).
    if err := s.ensureFree("LOWER(name) = LOWER(?)", name); err != nil {
        return Project{}, err
    }
    // 2) Existing case-insensitive WORKDIR duplicate check (unchanged).
    if err := s.ensureFree("LOWER(workdir) = LOWER(?)", workdir); err != nil {
        return Project{}, err
    }

    // 3) Insert. A PRIMARY KEY conflict on the exact-case name can only be a
    //    disabled/archived row (an enabled one was rejected above), so revive it.
    _, err := s.db.Exec(`
        INSERT INTO projects (name, workdir, enabled, archived)
        VALUES (?, ?, 1, 0)`, name, workdir)
    if err == nil {
        return s.get(name)
    }
    if !isConstraintErr(err) { // SQLITE_CONSTRAINT_{PRIMARYKEY,UNIQUE}
        return Project{}, err
    }
    var enabled, archived int
    if qerr := s.db.QueryRow(
        `SELECT enabled, archived FROM projects WHERE name = ?`, name,
    ).Scan(&enabled, &archived); qerr != nil {
        return Project{}, err
    }
    if enabled == 1 && archived == 0 {
        return Project{}, fmt.Errorf("%w: %q", ErrProjectExists, name)
    }
    if _, uerr := s.db.Exec(
        `UPDATE projects SET workdir = ?, enabled = 1, archived = 0 WHERE name = ?`,
        workdir, name); uerr != nil {
        return Project{}, uerr
    }
    return s.get(name)
}

// ensureFree rejects when an enabled, non-archived row matches
// LOWER(col)=LOWER(?) — the shared predicate for both guards.
func (s *Store) ensureFree(predicate, arg string) error {
    var blocked string
    err := s.db.QueryRow(
        `SELECT name FROM projects WHERE `+predicate+
            ` AND enabled = 1 AND archived = 0 LIMIT 1`, arg,
    ).Scan(&blocked)
    switch {
    case errors.Is(err, sql.ErrNoRows):
        return nil
    case err != nil:
        return err
    default:
        return fmt.Errorf("%w: %q", ErrProjectExists, blocked)
    }
}

HTTP handler — no new mapping needed; the existing 409 branch now also catches name duplicates via errors.Is:

p, err := s.CreateProject(req.Name, req.Workdir)
if errors.Is(err, ErrProjectExists) {
    http.Error(w, ErrProjectExists.Error(), http.StatusConflict) // existing phrase
    return
}

Production note: with multiple DB connections the pre-check + INSERT isn't atomic; the PK-conflict revive path handles exact-case races, and a BEGIN IMMEDIATE transaction around steps 1–3 fully closes the window (serialized here via SetMaxOpenConns(1)).