go-sqlite-partial-unique-index-empty-string

Answer

Root cause. The partial unique index WHERE col IS NOT NULL only exempts SQL NULL. When the HTTP handler didn't set idempotency_key, the repository still bound Go's zero-value "" into the INSERT, and '' IS NOT NULL is true in SQLite — so the second row collided, surfacing as 500 UNIQUE constraint failed (2067).

Fix part 1 — repository layer (SQLite and PG): normalize empty strings to NULL at the bind point, before the value reaches the driver. This keeps the index DDL untouched and works identically for SQLite and Postgres because both encode a nil *string as NULL.

// internal/repo/normalize.go
// normalizeOptional returns a *string that is nil when s is empty,
// so the driver binds SQL NULL and the partial unique index is bypassed.
func normalizeOptional(s string) *string {
    if s == "" {
        return nil
    }
    return &s
}

// internal/repo/memories.go
func (r *MemoryRepo) Create(ctx context.Context, m Memory) (Memory, error) {
    // normalize BEFORE binding — applies to both SQLite and PG paths
    key := normalizeOptional(m.IdempotencyKey)

    if r.isSQLite { // modernc.org/sqlite / mattn/go-sqlite3
        res, err := r.db.ExecContext(ctx,
            `INSERT INTO memories (title, body, idempotency_key)
             VALUES (?, ?, ?)`,
            m.Title, m.Body, key,
        )
        // ...assign ID from res.LastInsertId()
    } else { // postgres (database/sql or pgx)
        err := r.db.QueryRowContext(ctx,
            `INSERT INTO memories (title, body, idempotency_key)
             VALUES ($1, $2, $3) RETURNING id`,
            m.Title, m.Body, key,
        ).Scan(&m.ID)
    }
    return m, err
}

Fix part 2 — handler wiring (DTO → model): the optional field was only copied in the MCP path, so the HTTP handler dropped it (leaving the Go zero value, which is the empty string that triggered the bug):

// internal/http/handler.go
func (h *Handler) CreateMemory(w http.ResponseWriter, r *http.Request) {
    var dto CreateMemoryRequest
    if err := json.NewDecoder(r.Body).Decode(&dto); err != nil {
        writeError(w, http.StatusBadRequest, err)
        return
    }
    mem := Memory{
        Title:          dto.Title,
        Body:           dto.Body,
        IdempotencyKey: dto.IdempotencyKey, // was missing — zero value "" was bound
    }
    saved, err := h.repo.Create(r.Context(), mem)
    if err != nil {
        // UNIQUE (2067) on a non-empty key → 409 Conflict instead of 500
        writeError(w, statusFor(err), err)
        return
    }
    writeJSON(w, http.StatusCreated, saved)
}

Optionally, surface genuine duplicates as 409 Conflict instead of 500 by checking for the SQLite extended code 2067 / PG code 23505 — but the constraint-vs-'' collision itself is resolved by the NULL normalization.