I have a table with documents with separate categories and I want to have a number column which auto increments after adding a new document to a specified category

id |category | number
-----------------------------
1| cat_a      | 1
2 | cat_a     | 2
3 | cat_a     | 3
4 | cat_b     | 1

and now when I insert an document with cat_b i want to auto assign it to number: 2
I partially solved it by running max(number) where(category: "cat_b") before inserting but this is not an atomic operation - this way I still can have duplicates.
I can have 2 seperate tables but when if I depend on 2 columns (category and user)??
I was thinking about creating a trigger in the database but first I want to consult your opinion.