Supabase is an open source Firebase alternative. Start your project with a Postgres database, Authentication, instant APIs, Edge Functions, Realtime subscriptions, and Storage.

Supabase

hi folks — a general question about schema design or structure — let's say I wanted files and folders — like a tree with nested folders (however many deep) — where would I start? a file would be an arbitrary table with its columns. also, a folder would need to be a column too since it may have its own metadata.

I think what I need is just a category table with a `parent_id` where `null` would be the highest up

Yes thats one option or depending on how complex it is then nested sets might be better

Example of what you are describing https://dzone.com/articles/trying-to-represent-a-tree-structure-using-postgre

Example of nested sets https://en.wikipedia.org/wiki/Nested_set_model

thanks! I'll look into both. FWIW it's a fairly lightweight list represented in the left-nav and specific to the user.

That's exactly what I have done recently: I made a function to get all the parents from a directory

```pgsql
drop function if exists public.category_hierarchy;

create function public.category_hierarchy(id_param int)
returns table (
  id bigint,
  title varchar,
  parent_id bigint
)
as $$
begin
  return query with recursive parents
  as (
    select
      c1.id,
      c1.title,
      c1.parent_id
    from categories c1
    where c1.id = id_param
    union
      select
        c2.id,
        c2.title,
        c2.parent_id
      from categories c2
      inner join parents p on p.parent_id = c2.id
  ) select * from parents;
end;
$$ language plpgsql security definer;
```

in what scenario would you need this, for example? and can you give me an idea of what the data it returns looks like?

I needed to build a breadcrumb so it returns a list of the hierachy of a specific category

Returns
```
[
{Id: 5, title: "Category 5", parent_id: 4},
{Id: 4, title: "Category 4", parent_id: 3},
...
{Id: 1, title: "Category 1", parent_id: null}
]
```