Issue trying to perform soft deletes to supabase

[gcosta815]

I'm trying to perform soft deletes of clients in my App. I have a "deleted_at" column in the "clients" table, to which I'm trying to set its value to a timestamp when that client is soft deleted.

Here is my handleDelete method for implementing soft deletes:

const handleDelete = async (clientId: string) => {
    try {      
      const { error: updateError } = await supabase
        .from('clients')
        .update({ 
          status: 'inactive',
          deleted_at: new Date().toISOString()
        })
        .eq('id', clientId)
      if (updateError) {
        console.error('Error updating client:', updateError);
        throw updateError;
      }
      ...
    } catch (error) {
     ...
    }
  };

And here is my supabase cliente initialization:

import { createClient } from '@supabase/supabase-js';
import type { Database } from './types';

const SUPABASE_URL = "yyyyyyy";
const SUPABASE_PUBLISHABLE_KEY = "xxxxxxxx";

export const supabase = createClient<Database>(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY);

This is not working, I get the following error when performing the request: "new row violates row-level security policy for table "clients""

This is the RLS policy I have created for the "clients" table:

alter policy "Users can update or soft delete their own clients"
on "public"."clients"
to public
using (
  (( SELECT auth.uid() AS uid) = user_id)
with check (
  (( SELECT auth.uid() AS uid) = user_id)
);

If I disable RLS, it works. But it also works if I remove the "deleted_at" argument passed to .update() within the handleDelete() method. So I guess there is something wrong with that specific column, but after hours of burning my brain I could not come up with a solution.

Thanks!

[GaryAustin1]

Is that the only policy on the table? That looks like an All policy. But if you have other policies for select or update they will still run.
A common thing I see is trying to use a deleted_at column with a select policy to not show if the column is deleted. You can't update if you don't meet the select policy before and after so that will fail. I've seen users use a time factor in the select RLS so it passes for 5 seconds after the deleted_at time to allow the update to finish.

[gcosta815]

I have three policies in the "clients" table:

Update policy: Users can create their own clients

Name:
Users can create their own clients

Action:
PERMISSIVE

Command:
INSERT

Target roles:
public

USING expression:

CHECK expression:
(auth.uid() = user_id)

Update policy: Users can update or soft delete their own clients

Name:
Users can update or soft delete their own clients

Action:
PERMISSIVE

Command:
UPDATE

Target roles:
public

USING expression:
(( SELECT auth.uid() AS uid) = user_id)

CHECK expression:
(( SELECT auth.uid() AS uid) = user_id)

Update policy: Users can view their non-deleted clients

Name:
Users can view their non-deleted clients

Action:
PERMISSIVE

Command:
SELECT

Target roles:
public

USING expression:
((auth.uid() = user_id) AND (deleted_at IS NULL))

CHECK expression:
None

Do you think any of the other poclicies could affect the UPDATE policy when trying to update the value of deleted_at?

[gcosta815]

I think you are write, I changed the USING expression of the SELECT policy to TRUE and it worked. I now need to see how to change the SELECT policy so that it does not affect the UPDATE policy...

[gcosta815]

I changed the USING expression of the SELECT policy to this:

((auth.uid() = user_id) AND ((deleted_at IS NULL) OR (auth.uid() = user_id)))

And it worked!

Thanks you so much @GaryAustin1 !