Store date of running crowdmatches #629

smichel17 commented 2021年07月02日 02:03:26 +02:00

Currently when I run crowdmatch 2020 08 01, it'll record a crowdmatch on that date in the database. But it won't record that the match was run today, 2021年07月01日. We should store both. Also, they should both include a timestamp, although it's currently always midnight UTC.

Implementation notes (outdated)

We could do this in a simpler way, but this kicks off updating the DB to support our long-term, multiple-projects-and-goals plan (see snowdrift/snowdrift!253 for a draft of what the end state might look like).

1. Update the DB
   1. Rename CrowdmatchHistory table to Donation
   2. Rename DonationHistory to PaymentHistory (for sanity)
   3. Add Crowdmatch and FinalCrowdmatch tables
   4. Populate Crowdmatch table based on data in Donation table (group by date and count pledges, then math progress)
   5. Populate FinalizedCrowdmatch table manually-ish.
      • Most crowdmatches were run on the 3rd of the month. Then there was one day where we started actually running crowdmatches, which processed a few years' worth. (TODO: find that date).
      • All final goal progress will be equal to initial goal progress since we are not dropping yet.1
   6. Update Donation table.
      1. Add Crowdmatch column, by joining on Crowdmatch table by date.
      2. Add PledgeAmount column, default 600 cents (technically it was 10ドル very early on when we had a "limit" framing, but we never exceeded that so it won't make a material difference).
      3. VERIFY locally that for all rows, Donation.amount == (FinalCrowdmatch.amount / Crowdmatch.goalAmount) * Donation.pledgeAmount. Once confirmed, drop Donation.pledgeAmount.
   7. Add DonationOutcome table. One row for each Donation row. All status=Confirmed.1
2. Add startCrowdmatch action (to run on the first of each month). Takes a start date and finalize date.
   1. Add a new row the the Crowdmatch table
   2. For each patron who is pledged, add a Donation
3. Add finishCrowdmatch action (to run on the 4th of each month). Takes just a start date.
   For the Crowdmatch with the given start date, only if it doesn't have a corresponding FinalizedCrowdmatch yet:
   1. For each linked Donation with no corresponding DonationOutcome, check the pledge history to see if the pledge was still active on the current date. Create a DonationStatus for that donation with either status=Confirmed or status=DroppedPledge.
   2. Create a FinalizedCrowdmatch with the curent date and the finalGoalProgress = sum(donations for this crowdmatch with status=Confirmed)

1Technically this is wrong since a few patrons were dropped. we could sleuth through the history and see which patrons were dropped, add a Donation for them and corresponding DonationOutcome with status=Unpledged, and then calculate the correct initialProgress on the Crowdmatch from that. But it's not worth the effort; the final pledge amount is correct and in this pre-alpha stage that's what matters.

Currently when I run `crowdmatch 2020 08 01`, it'll record a crowdmatch on that date in the database. But it won't record that the match was run today, 2021年07月01日. We should store both. Also, they should both include a timestamp, although it's currently always midnight UTC. <details> <summary>Implementation notes (outdated)</summary> We could do this in a simpler way, but this kicks off updating the DB to support our long-term, multiple-projects-and-goals plan (see snowdrift/snowdrift!253 for a draft of what the end state might look like). 1. Update the DB 1. Rename `CrowdmatchHistory` table to `Donation` 2. Rename `DonationHistory` to `PaymentHistory` (for sanity) 3. Add `Crowdmatch` and `FinalCrowdmatch` tables 4. Populate `Crowdmatch` table based on data in `Donation` table (group by date and count pledges, then math progress) 5. Populate `FinalizedCrowdmatch` table manually-ish. - Most crowdmatches were run on the 3rd of the month. Then there was one day where we started actually running crowdmatches, which processed a few years' worth. (TODO: find that date). - All final goal progress will be equal to initial goal progress since we are not dropping yet.1 6. Update `Donation` table. 1. Add `Crowdmatch` column, by joining on `Crowdmatch` table by date. 2. Add `PledgeAmount` column, default 600 cents (technically it was 10ドル very early on when we had a "limit" framing, but we never exceeded that so it won't make a material difference). 3. VERIFY locally that for all rows, `Donation.amount == (FinalCrowdmatch.amount / Crowdmatch.goalAmount) * Donation.pledgeAmount`. Once confirmed, drop `Donation.pledgeAmount`. 7. Add `DonationOutcome` table. One row for each `Donation` row. All `status=Confirmed`.1 2. Add `startCrowdmatch` action (to run on the first of each month). Takes a start date and finalize date. 1. Add a new row the the `Crowdmatch` table 2. For each patron who is pledged, add a `Donation` 3. Add `finishCrowdmatch` action (to run on the 4th of each month). Takes just a start date. For the `Crowdmatch` with the given start date, only if it doesn't have a corresponding `FinalizedCrowdmatch` yet: 1. For each linked `Donation` with no corresponding `DonationOutcome`, check the pledge history to see if the pledge was still active on the current date. Create a `DonationStatus` for that donation with either `status=Confirmed` or `status=DroppedPledge`. 2. Create a `FinalizedCrowdmatch` with the curent date and the `finalGoalProgress` = sum(donations for this crowdmatch with status=Confirmed) 1Technically this is wrong since a few patrons were dropped. we _could_ sleuth through the history and see which patrons were dropped, add a `Donation` for them and corresponding `DonationOutcome` with `status=Unpledged`, and then calculate the correct `initialProgress` on the `Crowdmatch` from that. But it's not worth the effort; the final pledge amount is correct and in this pre-alpha stage that's what matters. </details>

smichel17 commented 2021年07月08日 01:34:13 +02:00

marked this issue as related to #28

marked this issue as related to #28

chreekat commented 2025年07月24日 12:55:09 +02:00

#969 is closed, so is this unblocked?

#969 is closed, so is this unblocked?

chreekat commented 2025年07月27日 19:48:02 +02:00

The linked issue, #253, seems unrelated. Is that a typo?

And are you sure you want to tie the rather simple addition of an execution time with a rearchitecture of the whole crowdmatch mechanism?

The linked issue, #253, seems unrelated. Is that a typo? And are you sure you want to tie the rather simple addition of an execution time with a rearchitecture of the whole crowdmatch mechanism?

smichel17 commented 2025年07月29日 00:15:46 +02:00

Not a typo, a quirk of how external issue trackers work in forgejo. Normally PRs and issues share the same numbers, so you can refer to either with # (github style). But if you use an external issue tracker then # always redirects to the external tracker (in our case, this tasks "repo"), so you need to use gitlab-style ! to refer to PRs instead. Updated now.

edit: opened a bug report: forgejo/forgejo#8713

Not a typo, a quirk of how external issue trackers work in forgejo. Normally PRs and issues share the same numbers, so you can refer to either with `#` (github style). But if you use an external issue tracker then `#` always redirects to the external tracker (in our case, this tasks "repo"), so you need to use gitlab-style `!` to refer to PRs instead. Updated now. edit: opened a bug report: https://codeberg.org/forgejo/forgejo/issues/8713

smichel17 commented 2025年07月29日 02:11:22 +02:00

Pretty sure. When iterating a live system, I strongly prefer... I don't have a name for this yet... basically, avoiding local maximums. Like climbing a mountain with the strategy: never walk downhill and never walk away from the peak (circling is OK). Even if that means you don't take the shortest path. (Next priority is to prioritize small iteration steps, which I guess would mean the least steep path or the most nice lookouts or something. It's not a detailed analogy.)

Adding the date now means there's more migration work (albeit, probably not too hard) when we do the architecture change.

• If the executed_at field were staying on the (削除) crowdmatch_history (削除ここまで) donation table in the new architecture, or if it was a 1:1 migration, then I'd have no objection to adding it now.
• If there is a smaller architecture change that can be made first to unblock this or some other feature, I'm 100% on board with doing that first. This is the smallest change I've found so far that doesn't break existing functionality or create more DB migration work.

Pretty sure. When iterating a live system, I strongly prefer... I don't have a name for this yet... basically, avoiding local maximums. Like climbing a mountain with the strategy: never walk downhill and never walk away from the peak (circling is OK). Even if that means you don't take the shortest path. (Next priority is to prioritize small iteration steps, which I guess would mean the least steep path or the most nice lookouts or something. It's not a detailed analogy.) Adding the date now means there's more migration work (albeit, probably not too hard) when we do the architecture change. - If the `executed_at` field were staying on the ~~`crowdmatch_history`~~ `donation` table in the new architecture, or if it was a 1:1 migration, then I'd have no objection to adding it now. - If there is a smaller architecture change that can be made first to unblock this or some other feature, I'm 100% on board with doing that first. This is the smallest change I've found so far that doesn't break existing functionality or create more DB migration work.

chreekat commented 2025年08月04日 10:29:33 +02:00

Ironically, my whole philosophy is oriented around always making the smallest incremental change possible. While there is such a thing as "too small", it's defined in terms of whether or not any value is provided at all. If this change is too small, it's because the data isn't being displayed on the website even though it now exists in the database. It's just hiding.

I don't want to try to convince you about this right now, since I'm more interested in developing a working mechanism. But I do want to convince you to reduce scope on this particular issue. The new database schema is designed to support four whole new modifications to the mechanism: multiple projects, multiple goals, flexible pledge amount per patron, and a new Stripe flow. First of all, I don't see support for multiple projects in there. :) [edit: I'm wrong -- it's in the draft PR, although not on the whiteboard image.] Second of all, I don't think all of these goals have the same priority. Doing them all at the same time makes it take longer to benefit from any of them, and has an opportunity cost of not doing whatever more-important changes might come up between them if they were split up. Third, the cost of adding this field now is extremely small. It might even be net negative cost, since we need to get practice doing migrations the new way, anyway.

So, I lied. I do want to try to convince you that making the smallest possible incremental change is always the right move. I won't argue that it's crucial to have a clear long-term vision to work towards. But the actual steps to get there are impossible to identify up front. Planning on doing everything at once just forces you to throw away your plans and make new ones about once a week. It's much better to actually provide new value once a week instead.

Ironically, my whole philosophy is oriented around always making the smallest incremental change possible. While there is such a thing as "too small", it's defined in terms of whether or not any value is provided at all. If this change is too small, it's because the data isn't being displayed on the website even though it now exists in the database. It's just hiding. I don't want to try to convince you about this right now, since I'm more interested in developing a working mechanism. But I do want to convince you to reduce scope on this particular issue. The new database schema is designed to support four whole new modifications to the mechanism: multiple projects, multiple goals, flexible pledge amount per patron, and a new Stripe flow. First of all, I don't see support for multiple projects in there. :) [edit: I'm wrong -- it's in the draft PR, although not on the whiteboard image.] Second of all, I don't think all of these goals have the same priority. Doing them all at the same time makes it take longer to benefit from any of them, and has an opportunity cost of not doing whatever more-important changes might come up between them if they were split up. Third, the cost of adding this field now is extremely small. It might even be net negative cost, since we need to get practice doing migrations the new way, anyway. So, I lied. I do want to try to convince you that making the smallest possible incremental change is always the right move. I won't argue that it's crucial to have a clear long-term vision to work towards. But the actual steps to get there are impossible to identify up front. Planning on doing everything at once just forces you to throw away your plans and make new ones about once a week. It's much better to actually provide new value once a week instead.

smichel17 commented 2025年08月06日 20:30:42 +02:00

I think we agree more than disagree; I think most of the time it is possible to provide incremental value that also works in the direction of the long term goals.

The new database schema is designed to support four whole new modifications to the mechanism: multiple projects, multiple goals, flexible pledge amount per patron, and a new Stripe flow.

Yes, but the DB changes written out in this issue don't actually add support for any1 of those. They do make a significant architecture change—which will allow those other things to be supported in the future just by adding a field or two—but it's not actually a very large change, just a refactor2 to normalize crowdmatch event information.

1Except flexible pledge amounts, but your comment made me realize I can drop that, so I will.

---

I usually prefer to tie refactors to new features. In my experience, it's easy for a refactor to go off the rails (e.g. "simplified" code that is actually more brittle) if you don't have the litmus test of "is this making it easier for me to add the new feature?" That's why I kept this refactor tied to adding the crowdmatch date.

However, it's well-scoped enough, and we are adding little enough denormalized data, that I'm OK with merging snowdrift/snowdrift!261 and then refactoring afterwards.

I think we agree more than disagree; I think most of the time it is possible to provide incremental value that *also* works in the direction of the long term goals. > The new database schema is designed to support four whole new modifications to the mechanism: multiple projects, multiple goals, flexible pledge amount per patron, and a new Stripe flow. Yes, but the DB changes written out in this issue don't actually add support for any1 of those. They do make a **significant** architecture change—which will allow those other things to be supported in the future just by adding a field or two—but it's not actually a very **large** change, just a refactor2 to normalize crowdmatch event information. 1Except flexible pledge amounts, but your comment made me realize I can drop that, so I will. --- I usually prefer to tie refactors to new features. In my experience, it's easy for a refactor to go off the rails (e.g. "simplified" code that is actually more brittle) if you don't have the litmus test of "is this making it easier for me to add the new feature?" That's why I kept this refactor tied to adding the crowdmatch date. However, it's well-scoped enough, and we are adding little enough denormalized data, that I'm OK with merging snowdrift/snowdrift!261 and then refactoring afterwards.

chreekat commented 2025年08月07日 08:58:09 +02:00

I agree we agree on strategy. I think perhaps I don't have a clear idea of your vision. I want to be assured that we're making code changes that move us as fast as possible to a website that other projects can use.

I agree we agree on strategy. I think perhaps I don't have a clear idea of your vision. I want to be assured that we're making code changes that move us as fast as possible to a website that other projects can use.

smichel17 commented 2025年08月07日 17:36:25 +02:00

Maybe let's jump on a call to sync on that? Ideally I'd be able to communicate it in writing (for others & the future) but I have a feeling that would take a lot longer.

Maybe let's jump on a call to sync on that? Ideally I'd be able to communicate it in writing (for others & the future) but I have a feeling that would take a lot longer.

smichel17 commented 2025年08月07日 17:51:55 +02:00

Okay, I wrote something out for direction. Updated the description of https://codeberg.org/snowdrift/tasks/milestone/9474

"Automatic: crowdmatches, notification emails to patrons, charge patrons when their balance is high enough, suspend them if the card bounces, etc."

There is a chicken and egg problem:

• We don't want to charge our first co-op members until we're ready to offer them co-op membership.
• We don't want to reach out to them and get a bunch of attention before the platform is reasonably functional (everything in this milestone).

To resolve this, we're going to get the platform functional/running automatically, but add a toggle for each patron so only enabled patrons will be charged, receive emails, etc. This will allow us to onboard them and immediately activate them, instead of onboarding and then asking them to wait a long time (and maybe lose interest) while we get the platform running on autopilot.

Technically: We'll also take steps towards supporting multiple projects, pledge levels, and goals, so that we can add external projects soon afterward (we don't want that to take a long time, for the same reason).

<details><summary>Okay, I wrote something out for direction. Updated the description of https://codeberg.org/snowdrift/tasks/milestone/9474</summary> > "Automatic: crowdmatches, notification emails to patrons, charge patrons when their balance is high enough, suspend them if the card bounces, etc." > > There is a chicken and egg problem: > > - We don't want to [charge our first co-op members](https://codeberg.org/snowdrift/tasks/milestone/5492) until we're ready to offer them co-op membership. > - We don't want to reach out to them and get a bunch of attention before the platform is reasonably functional (everything in this milestone). > > To resolve this, we're going to get the platform functional/running automatically, but add a toggle for each patron so only enabled patrons will be charged, receive emails, etc. This will allow us to onboard them and immediately activate them, instead of onboarding and then asking them to wait a long time (and maybe lose interest) while we get the platform running on autopilot. > > Technically: We'll also take steps towards supporting multiple projects, pledge levels, and goals, so that we can add external projects soon afterward (we don't want that to take a long time, for the same reason). </details>