I am able to get it working. Posting my solution in case anyone face similar issue.

Here is how my model code looks like

    public function rules()
    {
        return
        [
            ['code','validateCaseInsensitive']
        ];
    }

    public function validateCaseInsensitive($attribute, $params)
    {
        $query = self::find()
            ->where('LOWER(code) = LOWER(:code)', [':code' => strtolower($this->$attribute)]);

        // Exclude current record on update
        if (!$this->isNewRecord) {
            $query->andWhere(['<>', 'id', $this->id]);
        }
        
        if ($query->exists()) {
            $this->addError($attribute, 'Code already exists.');
        }
    }