In addition to tracking whether a specific study has space remaining toward a cap, there's a future where each session costs points, so we need to check and deduct/add points correctly as we go. In particular, we need to know where each point spent came from.
Currently, we don't maintain a "balance" of study cap space remaining per study, but calculate it from the table of responses (i.e. responses currently tallied) whenever it's time to check the study cap to see if study should be paused.
Events for that check include (@bgilbert please verify):
- Internal study session finishes and met criteria for tallying.
- External study session is created. (Or tbd)
- Researcher tallies or un-tallies a response manually
- Researcher makes a consent decision (because this can change a response from tallied to un-tallied)
BG: correct! I'll update this list if it changes.
When labs have banks of points that they are spending on these study counts, we need systems for tracking the overall lab balance, so that we can also check the lab balance (not just the study cap) to determine if studies need to be paused.
And it turns out that for audit reasons, we need to document where each point was actually coming from (e.g. was this from my annual membership limit, or a free one from recruiting?)
Next, I'll sketch a couple of output requirements we know about, followed by a proposed underlying DB structure.
Accrual reporting
Every month, we need to be able to produce a table that at a minimum looks like this:
| Lab |
Point group |
Points used during reporting period |
| Lab A |
Recruiting points |
14 |
| Lab A |
Membership package points |
30 |
| Lab B |
Membership package points |
29 |
Also needs to state the reporting period!
(*Note! We should lag reporting by however long the tally window is so that this table always reports numbers that won't be changing!)
Lab page dashboard
As a lab member, I should be able to see a set of info that looks something like this:
Total points remaining: 245
Total points used in (this calendar year): 67
Total points used on CHS: 67 (<-- open Q: when to start counting this from?)
Subscription license: Yes/No (determines whether or not you get infinite study objects)
Subscription license active since: DATE
Subscription license will expire on: DATE OR Subscription license will auto renew for PERIOD on: DATE
Finally for transparency, a look at the current balances so people can see e.g. that their soonest-expiring points are getting used first.
Remaining points:
| Point group |
Starting size |
Used |
Available |
Expiry date |
| Study pack A |
100 |
67 |
33 |
In 1 month |
| Membership |
100 |
0 |
100 |
6 months from now |
| Study pack B |
100 |
0 |
100 |
A year from now from now |
| Recruiting points |
18 |
12 |
6 |
Never |
| Top-up participants |
50 |
0 |
50 |
A year from now |
(When do points actually expire? Open question I'm not going to try to solve right now!)
A less developed section of this dashboard that will probably need to exist: What studies are running in what statuses, have they reached their participant cap, for how long am I allowed to keep running them, and can I launch new studies? (Note that this looks a lot like the existing studies table?)
(Desiderata: Members can always launch new studies. When a membership isn't active, a "study" should include 12mo of time running - then we likely want to count an additional tally.)
Proposed backend structure
Claude had good ideas about this! Basically, best practice is to store both a record of all transactions (a ledger) and a record of the current state (a balance sheet) that other operations can reference. At any point, you should be able to reconcile these: re-calculating all the transactions should result in the current state. <-- note this suggests it would be a good idea to also store the current tally sum on the study table as well as the tallied/untallied counts on the session entries, for the same reasons.
Point Balance Table
| Lab |
point group |
points at start |
points used (total, not within year) |
point remaining |
Expiry |
| Lab A |
Recruiting points |
18 |
12 |
6 |
Never |
| Lab A |
Membership package points |
100 |
0 |
100 |
6 months from now |
| Lab B |
Membership package points |
100 |
29 |
71 |
8 months from now |
(Note, this suggests that for actually calculating whether a session can start, we are going to this table and summing/checking all the active entries for a lab to see if slots are available)
Ledger
Claude suggests that there may be some performance advantages to separating credit & debit tables, i.e.
Point credit log
lab_id
entry_type (enum: 'membership credit', 'recruited bonus', 'adjustment')
credit_amount (int, always positive)
expiration_date (datetime)
date_created (datetime)
created_by (user_id)
reason (text)
Point debit log
lab_id
response_id
type ('tally', 'reversal')
credits_spent (int, 1 or 2) <- could also be negative, see below
tallied_at_datetime (datetime, when the Response was actually tallied)
lab_balance_after_debit (checksum, what the balance should be after this transaction)
reason (text)
Note that you would also put tally reversals on this table, with negative values. Reasons could be e.g. "consent rejected" "Researcher manually updated value"
In addition to tracking whether a specific study has space remaining toward a cap, there's a future where each session costs points, so we need to check and deduct/add points correctly as we go. In particular, we need to know where each point spent came from.
Currently, we don't maintain a "balance" of study cap space remaining per study, but calculate it from the table of responses (i.e. responses currently tallied) whenever it's time to check the study cap to see if study should be paused.
Events for that check include (@bgilbert please verify):
BG: correct! I'll update this list if it changes.
When labs have banks of points that they are spending on these study counts, we need systems for tracking the overall lab balance, so that we can also check the lab balance (not just the study cap) to determine if studies need to be paused.
And it turns out that for audit reasons, we need to document where each point was actually coming from (e.g. was this from my annual membership limit, or a free one from recruiting?)
Next, I'll sketch a couple of output requirements we know about, followed by a proposed underlying DB structure.
Accrual reporting
Every month, we need to be able to produce a table that at a minimum looks like this:
Also needs to state the reporting period!
(*Note! We should lag reporting by however long the tally window is so that this table always reports numbers that won't be changing!)
Lab page dashboard
As a lab member, I should be able to see a set of info that looks something like this:
Total points remaining: 245
Total points used in (this calendar year): 67
Total points used on CHS: 67 (<-- open Q: when to start counting this from?)
Subscription license: Yes/No (determines whether or not you get infinite study objects)
Subscription license active since: DATE
Subscription license will expire on: DATE OR Subscription license will auto renew for PERIOD on: DATE
Finally for transparency, a look at the current balances so people can see e.g. that their soonest-expiring points are getting used first.
Remaining points:
(When do points actually expire? Open question I'm not going to try to solve right now!)
A less developed section of this dashboard that will probably need to exist: What studies are running in what statuses, have they reached their participant cap, for how long am I allowed to keep running them, and can I launch new studies? (Note that this looks a lot like the existing studies table?)
(Desiderata: Members can always launch new studies. When a membership isn't active, a "study" should include 12mo of time running - then we likely want to count an additional tally.)
Proposed backend structure
Claude had good ideas about this! Basically, best practice is to store both a record of all transactions (a ledger) and a record of the current state (a balance sheet) that other operations can reference. At any point, you should be able to reconcile these: re-calculating all the transactions should result in the current state. <-- note this suggests it would be a good idea to also store the current tally sum on the study table as well as the tallied/untallied counts on the session entries, for the same reasons.
Point Balance Table
(Note, this suggests that for actually calculating whether a session can start, we are going to this table and summing/checking all the active entries for a lab to see if slots are available)
Ledger
Claude suggests that there may be some performance advantages to separating credit & debit tables, i.e.
Point credit log
lab_id
entry_type (enum: 'membership credit', 'recruited bonus', 'adjustment')
credit_amount (int, always positive)
expiration_date (datetime)
date_created (datetime)
created_by (user_id)
reason (text)
Point debit log
lab_id
response_id
type ('tally', 'reversal')
credits_spent (int, 1 or 2) <- could also be negative, see below
tallied_at_datetime (datetime, when the Response was actually tallied)
lab_balance_after_debit (checksum, what the balance should be after this transaction)
reason (text)
Note that you would also put tally reversals on this table, with negative values. Reasons could be e.g. "consent rejected" "Researcher manually updated value"