Credits support

[MartinKolarik]

Following #473, we need to allow the users to spend the credits they have. The logic is similar to the existing rate limits, with 1 test location = 1 credit. The credits are used only if the user's hourly quota is not sufficient to fulfill the request.

Example: The user requests a measurement with 5 locations and has 2 more requests in their hourly quota. His remaining hourly quota is set to 0, and 3 credits are spent from their account. If they don't have enough credits, nothing happens, and the request is rejected.

There is a question of how we represent the credit spending in the DB. Right now, we have a separate entry for each addition, which is good for record keeping but hard to actually use here. I'm thinking we:

• rename the current credits to credits_additions,
• create a new credits table where we keep only one row per user, which can easily be incremented/decremented in an atomic way,
• create a new credits_deductions table to track the usage per day.

To maintain consistency, we can then set triggers on the additions/deduction tables so that the apps work with the DB in the following way (I can do the SQL part if needed):

• additions are handled by inserting into the credits_additions table as now; the DB automatically increases the total in the credits table,
• deductions are handled by inserting/updating the credits_deductions table; the DB automatically decreases the total or throws if it goes below 0.

Both operations are then one simple query from the app's perspective (and one hidden query in the DB, which is still lightweight).

When credits are involved in handling the request, it would be good to also add two new headers: X-Credits-Remaining and X-Credits-Cost

[alexey-yarmosh]

Insert to credits_additions may happen even when associated users are not registered. Since they don't have userId it is not possible create a row in credits for them. That means users can't get credits for their donations/adoptions before they are registered on dashboard.

I can add a e.g. consumed: false column to credits_additions and set it to true only if value was added to credits. Then, on user sign up it is possible to go through all additions and give them all their prev credits.

[MartinKolarik]

That seems nice, sure. So when you start sponsoring and register only sometime later, you'll get all the credits.

[alexey-yarmosh]

Should X-RateLimit-Limit return values as previously? Or include available credits data?

[MartinKolarik]

Existing headers as before and this:

When credits are involved in handling the request, it would be good to also add two new headers: X-Credits-Remaining and X-Credits-Cost

[alexey-yarmosh]

Oh, missed that description part.

• X-Credits-Cost is how much credits where deducted during that request?
• seems like a second sql request is required to support X-Credits-Remaining, as update only returns number of affected rows:

  await this.sql(CREDITS_TABLE).where({ user_id: userId }).update({ amount: this.sql.raw('amount - ?', [ credits ]) });
  const remaining = await this.sql(CREDITS_TABLE).where({ user_id: userId }).select('amount');

  there is an option to add add .returning('amount');, but that requires Postgresql.

  const remaining = await this.sql(CREDITS_TABLE).where({ user_id: userId }).update({ amount: this.sql.raw('amount - ?', [ credits ]) }).returning('amount');

[MartinKolarik]

Should be possible with our DB too now: https://mariadb.com/kb/en/insertreturning/

Maybe will need a raw query if knex doesn't know it yet.

[alexey-yarmosh]

We need to update gp_credits instead of inserting. Since there is no UPDATE...RETURNING it still looks doable with INSERT INTO ... ON DUPLICATE KEY UPDATE ... RETURNING, where insert does nothing (amount: 0) and subtraction logic is inside ON DUPLICATE KEY UPDATE.