Update does not reflect changes in supabase table

[mrbende]

Bug report

I am unable to return data, or see the updated content reflected in my Supabase console, when working with a public database... This is in the context of a chatbot application where I am trying to save conversation history alongside user profiles as a json field.

Describe the bug

Egress from the table works with no problem -- I am able to authenticate with the python client, connect to my table, and read the appropriate data from the table.

When trying to update a user profile to update the field, however, no ingress is ever executed. I can validate that the proper json is being passed to the supabase client, but the resulting data is empty and no update is reflected in supabase.

To Reproduce

These are my python functions to interface with supabase within my application:

class Memory:
    def __init__(self,
                 MyAgent):
        self.MyAgent = MyAgent

    def init_supabase(self):
        ### DEFINE SUPABASE CLIENT
        self.supaclient = create_client(
            self.MyAgent.SUPABASE_AUTH_URL, 
            self.MyAgent.SUPABASE_AUTH_KEY
        )
    def ingress_memory(self, 
                                         user_id,
                                         message_thread,
                                         question, 
                                         response):
          ### ADD NEW MESSAGES
          # message_thread.append({"role": "user", "content": question})
          message_thread.append({"role": "assistant", "content": response})

          ### UPDATE DATABASE
          # json_chat = json.dumps({'chat_history': message_thread})
          json_chat = {'chat_history': message_thread}
          print(f"Ingress Thread: {json_chat}")

          ### !!! SCREENSHOT ATTACHED FOR THE INGRESS ABOVE AND DATA BELOW !!!
          data, _ = self.supaclient.table("profiles")\
              .update({"chat_history": json_chat})\
              .eq('id', user_id)\
              .execute()
          print(data)

    def egress_memory(self, 
                                        user_id):

          ### COMPILE CHAT THREAD
          result, _ = self.supaclient.table('profiles')\
              .select('chat_history')\
              .eq('id', user_id)\
              .execute()
          user_chat_history_json = result[1][0]['chat_history']
          if not user_chat_history_json:
              user_chat_history =  {'chat_history': [{"role": "assistant", "content": "This is the beginning of your conversation."}]}
          else:
              user_chat_history = user_chat_history_json['chat_history']
          return user_chat_history

Expected behavior

In the attached screenshot, the data that is returned on ingress should reflect that data has been added to supabase but nothing is updated.

Supabase table chat_history json information can be seen here:

---

Any help would be appreciated. Thank you!

~ Reed

[silentworks]

My first thought is that you aren't meeting your row level security (RLS) policy requirements. I would suggest turning off RLS and then testing this again to see if it works.

[mrbende]

What are the repercussions of that to the privacy of my user database...?

The python client attempting to make these changes is always the same with the table owner's API key.

[mrbende]

It does appear at first glance that the Python client would adopt the authenticated role which respects RLS... Although this doesn't explain why I am able to read from the table but I am not able to update the table...?

Is there a way from the Python client init to specify the postgres role? Or to otherwise somehow keep RLS enabled for the sake of my data's privacy, while still allowing the Python client to make updates?

[mrbende]

@silentworks Any chance you could update here regarding RLS and best practices? Would it make more sense to create a separate table for my user chat information that is linked to the auth profiles?

[silentworks]

@mrbende I think the Supabase docs does a better job at explaining RLS than I can. You should check out the docs and create policies that fits your needs for your project.

The python library only uses anon and authenticated roles and not the postgres user. The role changes depend on whether your user is signed in or not.