how could i model something like medium's claps, where a user can make many many votes on a post/thing but with a max on their vote count having one per user is as simple as

primary key(item_id, user_id)

how do i write a check constraint that limits the amount of rows a user can make on an item? i dont know any way to use aggregates like max/count in indexes or check constraints...

Not completely thought out, but you can use upsert to either insert or update the item_id/user_id combo with both as a unique constraint (the pair must not exist to do insert). Have vote column default to 1. Then in a before trigger function on update do new.vote = old.vote +1. Finally have a constraint on the vote column that it must be < 5. If it goes over that you get an error that you would have to handle. You might want to still do gracious handling in your client code if you have read the vote count they have done. Then above is just to make sure a client can't fake the code and do more votes than allowed. Edit. Actually in your before trigger you could just check if the old.vote +1 is going to exceed the count and return null to error out right there.