The invite quota counts held codes, enforced in one statement

Decision

A member may hold at most two unused codes at a time. Used codes are kept (marked with used_by_id and used_at) instead of deleted, and only unused, non-reusable codes count against the quota. The quota check and the insert are a single INSERT ... SELECT ... WHERE (count of held codes) < 2, and a sign-up consumes its code with a conditional UPDATE ... WHERE used_by_id IS NULL inside the sign-up transaction. Codes issued by the system inviter "Cradle" stay reusable, as in 2022.

Context

The 2022 controller (invitationController.generateNewCode) refused a third code with codes.length >= 2 over InvitationCode.find({ inviter }), and userController.createNewUser deleted a code once someone joined with it. Together these capped the codes a member was holding, not the codes they had ever issued. The revival needs used codes to draw the invite tree, so it can't delete them.

Options considered

1. Delete used codes, as in 2022. Faithful, but the tree loses the record of who joined with which code.
2. Count every code ever issued. Simple, but it silently turns the rule into a lifetime cap of two invitations, which is not what 2022 did.
3. Count unused codes only (chosen). Same observable behaviour as 2022, with history kept.
4. Check in application code, then insert. Two requests racing between the check and the insert can both succeed and mint a third code.
5. A database trigger that refuses a third held code. Strong, but harder to read and test than one statement in the service layer.

Why

Option 3 keeps the 2022 behaviour members could observe while keeping the data the revival needs. Putting the check inside the insert makes the rule hold under concurrency without locks or interactive transactions, which matters on Turso, where every round trip crosses the network.

What happened

• An integration test fires four "make a code" requests at once for a member with two free slots; exactly two succeed.
• Property-based tests (fast-check, seed 20220107, printed on /methods):
  • 1,000 random sequences of up to 120 operations against the pure 2022 rules, checking after every step that nobody holds more than two codes, no code lets two people in, and the tree has no cycle. No counterexample was found.
  • 100 runs of up to 8 rounds of up to 4 concurrent operations against the real service layer and SQLite, starting each run from a fresh copy of the seed, with the invariants checked in SQL after every round. No counterexample was found, and the runs reached every interesting branch (codes refused at the quota, sign-ups lost to used codes and to taken usernames).
  • A mutant with an off-by-one quota (> instead of >=) is caught and shrunk to a counterexample of six steps or fewer, so the property has teeth.
• The honest reading of "no counterexample": under the generators' distribution, the exact 95% upper bound on the chance that one random scenario breaks an invariant is about 0.3% for the 1,000 model runs but about 3% for the 100 database runs. That second bound is weak. The database suite is small because each run copies a database, and it is evidence, not proof.
• On the live database the demo member @mika holds two of two codes (one was minted during an earlier deployment check). That is the rule working: "Borrow a code" on /join lends one of her existing codes instead of minting a third.

What I'd change

• Add the cap as a database trigger as well, so a future code path can't bypass the service layer.
• Grow the database property suite (more runs on CI only) and make its generator closer to real usage, where most operations are reads.
• Record why a code went unused (expired interest, sent to a wrong address) if the community ever becomes real; the current data can't say.