0

I'm working with Supabase which has the ability to create database functions. In a table, I have a json column which holds an array of simple objects, that are tracking a user's onboarding completion.

The json array might look something like this:

[
  {
    "page": "dashboard",
    "completion_date": "2022-07-18T17:10:42.254Z"
  }
]

and so as you can see, it's only tracking two properties, page and completion_date. The issue I'm facing however, is that anytime a user goes to another page and completes the onboarding there, this value is being overwritten with the new values rather than inserted.

How do I go about inserting that new object if it doesn't exist, or updating that object based on the "page" key if it does already exist?

J. Jackson
  • 3,326
  • 8
  • 34
  • 74

1 Answers1

1

If the structure of the data is already defined, I would create a separate table with page column with text type and completion_date as timestampz. This table most likely will also contain user_id column that represents the id of the user who has completed the step.

You could write a sql something like this to create the table:

create table if not exists public.onboarding_completion (
    user_id uuid references public.users on delete cascade not null,
    page text not null,
    completion_date timestamp with time zone default timezone('utc' :: text, now()) not null,
    primary key (user_id, page)
);

Notice that at the end I have defined a composite primary key, where the table makes sure that no row with the same combination of user_id and page will every exist.

Getting data from this table along with the user's profile is very easy.

const { data, error } = await supabase
  .from('users')
  .select('*, onboarding_completion(*)')
  .eq('id', user.id)
  .single()

Assuming user.id will get the currently logged in user, the above code will get the array of completed onboarding tasks in an array of objects just like how you wanted originally!

Having a separate table is a better approach, because you can make sure that your data will not be corrupted with unexpected data or bug on the frontend.

dshukertjr
  • 15,244
  • 11
  • 57
  • 94
  • Yea this is something I'd been considering. This would get past having to deal with json columns on the users table, and this would certainly work, though I'm still kind of leaning towards the original. – J. Jackson Jul 21 '22 at 15:32
  • @J.Jackson I see. May I ask why? – dshukertjr Jul 21 '22 at 23:39
  • i think mostly just preference? It doesn’t feel like something that needs to have it’s own entirely separate table, however if i can’t figure out the Postgres function and/or your method is easier, i may very well just go that route. I’ll have to think on it some more! – J. Jackson Jul 22 '22 at 00:44
  • 1
    @J.Jackson Yeah, think about it! Having a table is not a very computationally expensive thing, and it is the more standard way of doing things in relational database! I myself have a background of using firebase for a long time, so I do understand the feeling that creating a table feels like a overkill though! – dshukertjr Jul 22 '22 at 04:49