Function clearLegacyColumnUniqueness

internal/storage/db/sql/provider.go:150–226

clearLegacyColumnUniqueness removes any single-column UNIQUE constraint or index on the email / phone_number columns of authorizer_users and authorizer_otps, regardless of its name. See the call site in NewProvider for why this must run before AutoMigrate. Best-effort and non-fatal: fresh installs,

(db *gorm.DB, log *zerolog.Logger)

// installs, and catalogs without information_schema (sqlite), simply find
// nothing to drop.
func clearLegacyColumnUniqueness(db *gorm.DB, log *zerolog.Logger) {
	// Run through a discarding GORM logger. The information_schema probe below
	// intentionally errors on databases without that catalog (sqlite); GORM's
	// default logger writes such errors to os.Stdout, which corrupts the MCP
	// server's stdio JSON-RPC stream. We already surface anything actionable via
	// the zerolog `log` (stderr), so GORM's own logging here is redundant.
	db = db.Session(&gorm.Session{Logger: logger.Discard})

	targets := map[string]bool{"email": true, "phone_number": true}
	tables := []struct {
		model any
		name  string
	}{
		{&schemas.User{}, "authorizer_users"},
		{&schemas.OTP{}, "authorizer_otps"},
	}

	for _, t := range tables {
		// 1) Drop single-column UNIQUE *constraints* by their real names, read
		//    from the same catalog GORM uses (information_schema). This matches
		//    ANY name — ..._key, uni_..., idx_..., or custom — which is what
		//    actually prevents the DROP CONSTRAINT "uni_..." 42704 abort. A
		//    constraint's backing index cannot be removed with DROP INDEX, so it
		//    must go via DropConstraint.
		var rows []struct {
			ConstraintName string `gorm:"column:constraint_name"`
			ColumnName     string `gorm:"column:column_name"`
		}
		const q = `SELECT tc.constraint_name AS constraint_name, kcu.column_name AS column_name
			FROM information_schema.table_constraints tc
			JOIN information_schema.key_column_usage kcu
			  ON tc.constraint_name = kcu.constraint_name
			 AND tc.table_schema = kcu.table_schema
			 AND tc.table_name = kcu.table_name
			WHERE tc.constraint_type = 'UNIQUE' AND tc.table_name = ?`
		if err := db.Raw(q, t.name).Scan(&rows).Error; err != nil {
			// sqlite and other catalogs without information_schema land here; they
			// are not affected by the GORM unique-reconciliation behaviour.
			log.Debug().Err(err).Str("table", t.name).Msg("skip legacy unique-constraint scan")
		} else {
			columnsByConstraint := map[string][]string{}
			for _, r := range rows {
				columnsByConstraint[r.ConstraintName] = append(columnsByConstraint[r.ConstraintName], r.ColumnName)
			}
			for name, cols := range columnsByConstraint {
				if len(cols) == 1 && targets[cols[0]] {
					if err := db.Migrator().DropConstraint(t.model, name); err != nil {
						log.Debug().Err(err).Str("constraint", name).Msg("failed to drop legacy unique constraint")
					}
				}
			}
		}

		// 2) Drop any standalone single-column UNIQUE *index* (CREATE UNIQUE
		//    INDEX, not a constraint) on the same columns. These do not cause the
		//    abort — GORM reads column-uniqueness from table_constraints only —
		//    but removing them makes the column genuinely non-unique, matching the
		//    v2 application-layer model. GetIndexes already excludes