How to deal with fixed max number of posts a user can make within a time period?

Sorry for titlegore.

Users can have at most 3 active posts, and each post is active for 48 hours.

I was thinking about just putting active_post_{1/2/3}_id and active_post_{1/2/3}_expires_at on the users table, but wondering if there is a better way to handle something like this.

Answer

I would store only the timestamp of the post and use middle-tier logic to restrict the number of active posts to three.

If you have a table like so:

create table posts (
  id int generated always as identity,
  user_id int not null references users(id),
  created_at timestamptz not null,
  post_text text not null
);

You can get the number of active posts with this query and disable the user’s ability to create a new post if the result is more than three.

select count(*) 
  from posts
 where user_id = ?
   and created_at > now() - interval '48 hours';

This could be defeated by a determined attacker through multiple active sessions in your application, but if that is a concern, then I would use the same logic to restrict visible posts to only three per user. When pulling the list of posts to display:

with rnums as (
  select user_id, created_at, post_text, 
         row_number() over (partition by user_id
                                order by created_at desc) as rn
    from posts
   where created_at > now() - interval '48 hours'
)
select user_id, created_at, post_text
  from rnums
 where rn <= 3
 order by user_id, created_at desc;

If you want to use PostgreSQL to enforce this constraint, then you would need to bring triggers into the mix.