Patreon notifies my VFP backend

The problem

My patrons on Patreon get a discount on the licensing portal that depends on how much they contribute. To work it out, the portal reads a DBF table of patrons that I kept up to date by hand: I logged into Patreon, downloaded the members CSV and uploaded it to an importer.

Between one download and the next, the table went stale. A new patron paid full price until I downloaded the CSV again, and one who moved up a tier kept the discount of the previous tier. It all depended on me remembering.

Patreon offers webhooks, so the solution was for their backend to notify mine whenever someone joins, leaves or changes tier, and for the DBF to update itself.

What Patreon sends

Six events are enough for me: members:create, members:update, members:delete and the three variants of members:pledge:*. Each call is a POST with two headers: X-Patreon-Event says what happened, and X-Patreon-Signature carries the hexadecimal HMAC-MD5 of the body, computed with a secret Patreon gives you when you create the webhook.

The body follows the JSON:API format: the member is in data, and the user and the tiers are in an included array, linked by id. Two details from the documentation shaped the design:

• The email can arrive null. It needs a specific permission on the campaign, and the patron can hide it. The user id, on the other hand, always arrives.
• The signature carries no timestamp, so there is no replay window as with other payment gateways. Receiving the same event twice has to make no difference.

Signing over the original bytes

The signature is computed over the bytes of the body, and in VFP the body arrives after crossing COM, which converts it to CP1252. I explained this in the previous post: with a Turkish or Polish name, or an emoji, a signature computed over the text does not match. That is why this webhook verifies the signature with req.GetRawBodyBase64(), which keeps the bytes as they arrived, and not with req.GetRawBody().

The webhook complements the CSV

The patrons table has 32 fields and a Patreon event carries about 14: status, amount, tier and billing dates. The address, Discord handle, currency or billing frequency only come in the CSV.

So the webhook never touches a field the event does not carry. A stale value is a smaller problem than a deleted one. In a webhook sign-up those fields stay empty until the next CSV runs. The importer is still there for the full refresh, but a new patron no longer has to wait for it to get the discount.

I made three more decisions in the same spirit:

• Match by user id first and by email second. The id does not change and always arrives; the email can be missing or change.
• A cancellation does not delete the row. The patron becomes “Former patron” with a zero amount, the same way a revoked license is deactivated without being deleted.
• The patron-since date is never rewritten. If the row already knows when the patronage started, a later event does not change it.

* Match: USERID first, lower-case email second.
llFound = .F.
IF !EMPTY(loEv.cUserId)
    LOCATE FOR ALLTRIM(patrons.userid) == loEv.cUserId
    llFound = FOUND()
ENDIF
IF !llFound AND !EMPTY(loEv.cEmail)
    LOCATE FOR LOWER(ALLTRIM(patrons.email)) == loEv.cEmail
    llFound = FOUND()
ENDIF

IF !llFound
    APPEND BLANK
ELSE
    SET REPROCESS TO 3 SECONDS
    IF !RLOCK()
        THIS.cLastReason = "bloqueada"
        EXIT
    ENDIF
ENDIF

* What the event carries is written; what it does not carry is left alone.
IF loEv.lPledgeKnown
    REPLACE patrons.pledgeamount WITH loEv.nPledge
ENDIF
IF loEv.lTierKnown
    REPLACE patrons.tier WITH loEv.cTier
ENDIF
IF !EMPTY(loEv.tSince) AND EMPTY(patrons.patronagesince)
    REPLACE patrons.patronagesince WITH loEv.tSince
ENDIF
REPLACE patrons.lastupdated WITH DATETIME()

Because the upsert only assigns values and adds nothing up, applying the same event twice leaves the row unchanged. That covers the missing timestamp in the signature.

The table belongs to another project

The patrons DBF belongs to another project that runs in the same FoxServer process, and its CSV importer opens it exclusively. This already caused me a problem: a portal query left it open and the importer failed with error 1705 until the server was restarted.

The webhook opens it shared, locks only the record it modifies and closes it in the FINALLY, and only if that same call opened it. If at that moment the importer has it open exclusively, nothing can be written, and in that case Patreon has to retry instead of losing the event.

Response codes

Payment webhooks usually answer 200 no matter what, because a retry that creates a duplicate license is worse than an event that stays in the log. Here I did the opposite, because the upsert duplicates nothing when repeated:

llOk = loSvc.Process(lcTrigger, lcJson)
IF llOk
    res.status(200).json(THIS.ToJson(loRes))
    EXIT
ENDIF

IF INLIST(loSvc.cLastReason, "json-invalido", "sin-usuario")
    * It can never be applied: 200 so Patreon does not repeat it.
    THIS.LogWarning("Patreon webhook: no aplicable ({0})", loSvc.cLastReason)
    res.status(200).json(THIS.ToJson(loRes))
ELSE
    * Locked, no configuration or an error: let Patreon retry.
    THIS.LogWarning("Patreon webhook: se pide reintento ({0})", loSvc.cLastReason)
    res.status(500).json(THIS.ToJson(loRes))
ENDIF

The upper-case email

While reviewing the service that computes the discount, I saw that it searched with a lower-case SEEK over an alltrim(email) index, which does not apply LOWER. If the email was stored with capitals, the SEEK did not find it, and the fallback LOCATE only ran when the index was missing, not when the SEEK failed. There were three rows like that, all of them free members, so no money had been lost. But the webhook was going to write emails as Patreon sends them.

Now the webhook always stores the email in lower case, and the discount service runs a lower-case search before giving up on the patron. I did not reindex the table, because it belongs to another project and an index change made from outside would also affect its importer.

Tests

There are 69 FoxProof tests spread over four files. They cover parsing the JSON:API, the upsert cases over a cursor, the signature, the real table being closed after writing, the case where another process has it open exclusively, the controller’s response codes and the upper-case search against the same index the production table has.

The final test was done by Patreon. Its webhooks dashboard has a Send test button for each event, which sends a fictitious member signed with your secret. I sent a members:create: it arrived from Patreon’s robot, the signature matched and the row was created. Then I sent a members:delete, and that same row became a former patron with a zero amount without being deleted. After that I removed the test row by hand.

I was careful with the order of the steps. On the 1st of each month Patreon charges every patron and sends one members:update per patron. If the webhook is registered against a URL that does not answer, that means hundreds of failures in a row and Patreon pauses it. So I deployed the endpoint first, which answered with a configuration error while it had no secret, and only then registered the webhook on Patreon.

There was one fault during the deployment, with no consequences. The script checked that the new route returned the expected error while there was no secret. The route answered correctly, but PowerShell did not read the body of the 500 response and the check counted it as a failure, so the script rolled the deployment back, as intended in that case. I repeated it checking with curl and it worked the first time. The service went down three times, for about a second each time.

What I learned

• A webhook does not have to replace the manual process. Getting ahead of it is enough, and if it does not write what it does not know, it introduces no new errors.
• If repeating an event does no harm, it is better to answer 500 when something fails, so it gets retried, than to answer 200 and lose it.
• A signature failure has to show up somewhere. With a 401, Patreon’s dashboard warns me; with a 200 I would have gone weeks without noticing.

From now on, when someone becomes a patron, their next payment on the portal already carries the discount. The 1st of the month will be the first time the table updates itself.