How to handle complex user status?

Question

My application deals with user payments. In the company, this user has the following status:

• compliant (user payed all debts so far)
• overdue/default (user registered for 3 months minimum and has hasn't payed at least 1 debt)
• inactive (user is registered for less than 3 months and hasn't payed any debt)

How is the best way to deal with those rules in multiple places (and rules) inside the application?

Do I need a field like status_id and a cron to update this every hour?

No status_id field and write the SQL rule in every query that needs to show the status?

Load a User model and call a ->status() method that has the rule? In this case, how can I show "totals", like: We have 3000 overdue users, 15000 inactive users etc...

This is giving me headaches for months and I really need help haha. We currently have a solution but it's too complex to deal with it. As it seems to be something common within apps that deal with payment, there's must be a simplier way to do this :P

Thanks!

Notes

• Application has currently 90.000 users
• We need this info in real-time.
• This info is used in reports to generate chars.
• This info is showed inside the user profile.
• This info is showed in listings.
• Users are notified when a user change between those status (like, "you have debts" when user enters in "overdue").
• This info is not managed by application users.
• The status need to be tracked.

Answer 1

If you are using this field in multiple places, then you should store the status in a single place and update it as appropriate (I would also keep a history of the statuses, but that is another matter).

If the status changes due to some user actions (such as a payment being processed), then you can use a trigger on the action. However, your status changes seem to be based on time after an event. In that case, you should run a regularly scheduled job (as a cron job or database event).

I am a bit confused about why you would do this every hour. It seems that once per day would be most appropriate. If the "debts" are paid at arbitrary times, then the process of payment should update the status. For the downgrading of the status, a single job once per day should be sufficient.

Answer 2

Interesting question, but also not one with a single answer.

I think the complexity here might come from the surrounding code, rather than the core business logic and requirements. I say this because three status types, all of which are derived from your internal application, isn't too bad.

One possible solution, and I am assuming some level of MVC or similar.

Given your model, user, and extending an ORM like Eloquent (I will Eloquent from Laravel because I am most familiar with it, but any ORM will work):

use Illuminate\Database\Eloquent\Model;
use App\DebtCollector;

public class User extends Model
{
    // Assuming model has the following fields
    // id, status, registration_date, and a one to many
    // relationship with debts 

    protected $fillable = [
        'raw_status',
        'registration_date',
    ];

    public function debts()
    {
        return $this->hasMany(Debt::class);
    }

    public function refreshStatus()
    {
        $dc = new DebtCollector();

        // Business logic inside the "DebtCollector" class
        $this->raw_status = $dc->resolveStatus($this->debts, $this->registration_date);

        // Save value to the underlying datebase
        $this->save();            
    }

    // If you fetch a status directly, it will refresh first, 
    // then return the value
    // 
    public function getStatusAttribute()
    {
        $this->refreshStatus();
        return $this->raw_status;
    }
}


// Schedule task somewhere - ran nightly, or whenever
// 
// This way you can refresh the status only on certain groups
// of data - for example, if the business flow means that once
// they become compliant, they can't go back, there is no need
// to refresh their status anymore 
//
User::where('raw_status', '<>', 'compliant')->refreshStatus();

// Alternatively, the schedule could chunk results and do an update
// only to those not updated in the last 24 hours
//
$date = new DateTime;
$date->modify('-24 hours');
$formatted_date = $date->format('Y-m-d H:i:s');
User::where('last_updated', '>', $formatted_data)->refreshStatus();