How to properly implement a user lock system?

Question

PostgreSQL

Hello. In my application I implement an authorization system. After several failed login attempts, the user should be locked out and unlocked after a few hours.
How I did it:
there is a user table that contains an identifier, login, password hash, salt and other service information.
There is a table in which all unsuccessful login attempts are added. It has the following structure: user ID (Foreign key per ID in the table with users), IP, number of failed attempts.
Also in this table there is a trigger that fires on insert and update. If login attempts > 5, then the user is added to the table where all blocked users are located. This table has the following structure: user ID (Foreign key to table from user), lock time (When the user was locked), unlock time (When the user needs to be unlocked).
There is a procedure that runs through the scheduler and unlocks users whose unlock time has come.

What can you say about my decision?
P.S. The database used is Postgresql.
Yes, the solution works, but I would like to do it the way it should be done, and not something that just works.

Answer 1

You can get by with one table with failed attempts. Structure:

• User ID
  
• IP address
  
• Date Time
  

When authorizing, make a request for x last x hours, if there are more than y unsuccessful attempts, calculate until what time the user should be blocked. If this time has not yet passed, do not let the user in. All this can be calculated in the query.
You don't need to update anything else. Periodically, you can clean the table.