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

Supabase

I have a tasks table, a projects table and a projects_tasks table. Pretty simple relationship stuff. However I have a need that I believe is outside my sql knowledge.

I need a unique incrementing value (just a simple int) per project that goes up every time a task is added, but only relative to the previous task with the same project_id. So it could be like this...

```
id   task_id   project_id   project_task_number
------------------------------------------------
1    15        42           1
2    16        42           2
3    17        44           1
4    18        44           2
5    19        45           1
6    20        42           3
7    21        42           4
```

What is the best way to go about this?

Don't know about best way, but I'd just use an insert trigger function to set new.project_task_number to the result of something like
SELECT COUNT(*)
FROM tasks
WHERE project_id = new.project_id
+1

You could probably use a where to find highest task number with that project id, but not sure that is any faster.  The only other thing I can thing of is to store a count on the project but then you have to update that table, which would not be good if you don't need that number for something else there.