Ref https://linear.app/tryghost/issue/SLO-193/optimise-count-query-skip-distinct-from-count-query-for-members-events
The member events endpoint have many queries like:-
select count(distinct members_subscribe_events.id) as aggregate
from `members_subscribe_events`
where `members_subscribe_events`.`created_at` < '2024-07-30 11:30:39'
In these queries, distinct is not required as id is a primary key. Skipping distinct would improve the performance.
This PR will changed the query to:-
select count(*) as aggregate
from `members_subscribe_events`
where `members_subscribe_events`.`created_at` < '2024-07-30 11:30:39'
ref https://linear.app/tryghost/issue/ENG-1254
- when a subscription is canceled automatically by Stripe (e.g. due to
multiple failed payments), we now send a staff notification
- logic before: if a member cancels a sub in Portal, then send a staff
notification
- logic now: if a subscription was active, but is now set to cancel
immediately or at the end of the billing period, then send a staff
notification.
- with that logic change, we now send a cancellation staff notification
when:
1. A member cancels their sub in Portal (existing)
2. A staff member cancels a member sub in Stripe (new)
3. A staff member cancels a member sub in Admin (new)
4. A sub is canceled automatically by Stripe because of multiple failed
payments (new)
- the copy of the staff notification email has also been updated to take
into account 1) manual vs automatic cancellations, and 2) immediate vs
end of billing period cancellations
ref
https://linear.app/tryghost/issue/ENG-1251/support-escalation-re-offers-not-tracking
- Offer Redemptions were not being persisted in the database for
existing free members who upgrade to a paid plan with an offer, which
resulted in inaccurate offer redemption counts. This made it difficult
to assess the performance of an offer.
- Previously, Ghost recorded an offer redemption in the DB in response
to the `SubscriptionCreatedEvent`, under the assumption that the offer
details would be included in this event. This assumption was valid for
brand new members starting a subscription with an offer, but not for
existing free members upgrading to a paid plan with an offer.
- For existing free members, the subscription is first stored in Ghost
in response to the `customer.subscription.created` Stripe webhook. At
this point, the offer/discount is not attached to the subscription, so
the `SubscriptionCreatedEvent` triggers without the offer information,
and the offer redemption is not recorded. After the
`checkout.session.completed` webhook is received (which _does_ include
the offer details), the subscription is updated in Ghost, but the Offer
Redemption is not stored.
- For brand new members, the `customer.subscription.created` webhook
no-ops, because the member and Stripe Customer don't exist yet.
Therefore, the subscription is first created in Ghost in response to the
`checkout.session.completed` webhook, which _does_ include the offer
information, so the offer information is included in the
`SubscriptionCreatedEvent` and the offer redemption is recorded as
expected.
- This change adds a new `OfferRedemptionEvent`, which triggers
either: (1) when a new subscription is created with an offer (as in the
case of a brand new member), or (2) when an existing subscription is
first updated to include an offer (as in the case of an existing free
member upgrading with an offer). The Offer Redemption is then persisted
in the DB in response to the `OfferRedemptionEvent` rather than the
`SubscriptionCreatedEvent`.
ref https://linear.app/tryghost/issue/ENG-1254
- we currently only store a cancellation reason when a member cancels
manually in Portal
- we now also store "Payment failed" when the cancellation is automatic
due to several payment failures
ref https://linear.app/tryghost/issue/SLO-173/removed-distinct-from-member-count-query
Performance of GET /members API can be improved by dropping the distinct from the total members count query.
select count(distinct members.id) as aggregate from `members`; // 275ms
select count(*) as aggregate from `members`; // 30ms
In this case we know that the result set will always be unique.
ref https://linear.app/tryghost/issue/CFR-27
- updated packages to include performance improvement for NQL filter
strings including multiple neq filters for the same resource
- bumped `bookshelf-plugins`
- bumped NQL versions
We identified a performance fix that allows us to combine not equal
(neq) filters for the same resource in a logically-equivalent way that
also has far more performant resulting SQL.
We're effectively automatically combining strings like
'tag:-tag1+tag:-tag2` into 'tag:-[tag1,tag2]'.
fix https://linear.app/tryghost/issue/SLO-82/query-error-unexpected-character-in-filter-at-char-1
- previously, we weren't handling a parsing error, and just bubbling it
back up the chain
- this would result in an InternalServerError somewhere, which caused
500s
- we can handle this, because it's just a bad filter
- this adds handling so we return a 422 upon receiving an invalid filter
ref https://linear.app/tryghost/issue/KTLO-1/members-spam-signups
- Some customers are seeing many spammy signups ("hundreds a day") — our
hypothesis is that bots and/or email link checkers are able to signup by
simply following the link in the email without even loading the page in
a browser.
- Currently new members signup by clicking a magic link in an email,
which is a simple GET request. When the user (or a bot) clicks that link, Ghost
creates the member and signs them in for the first time.
- This change, behind an alpha flag, requires a new member to click the
link in the email, which takes them to a new frontend route `/confirm_signup/`, then submit a form on the page which sends a POST request to the
server. If JavaScript is enabled, the form will be submitted
automatically so the only change to the user is an extra flash/redirect
before being signed in and redirected to the homepage.
- This change is behind the alpha flag `membersSpamPrevention` so we can
test it out on a few customer's sites and see if it helps reduce the
spam signups. With the flag off, the signup flow remains the same as
before.
refs KTLO-19
When we need to migrate subscriptions from a platform with platform
fees, we need to recreate the subscriptions. That can cause the same
subscription to be attached multiple times to the same member in Ghost.
This is a problem because all MRR, subscriptions and cancellations stats
are no longer correct. Ghost will add a MRR event for the duplicated
subscription from the start time, so there is a sudden peak in MRR and a
dip after the migration because all those duplicate subscriptions are
suddenly cancelled 'today'.
The migrator tool adds a ghost_migrated_to metadata field to the old
subscription. Ghost can use this to detect the old subscription and
delete the subscription and corresponding events.
ref https://linear.app/tryghost/issue/CFR-4/
- added request queueing middleware (express-queue) to handle high
request volume
- added new config option `optimization.requestQueue`
- added new config option `optimization.requestConcurrency`
- added logging of request queue depth - `req.queueDepth`
We've done a fair amount of investigation around improving Ghost's
resiliency to high request volume. While we believe this to be partly
due to database connection contention, it also seems Ghost gets
overwhelmed by the requests themselves. Implementing a simple queueing
system allows us a simple lever to change the volume of requests Ghost
is actually ingesting at any given time and gives us options besides
simply increasing database connection pool size.
---------
Co-authored-by: Michael Barrett <mike@ghost.org>
- this version is written in TS, but was published a few months ago and
needs to be bumped here
- also updates a previous deep include into the library, which was
unnecessary anyway
fixes PROD-102
When a newsletter has a sender_email stored in the database that Ghost
is not allowed to send from, we no longer return it as sender_email in
the API. Instead we return it as the sender_reply_to. That way the
expected behaviour is shown correctly in the frontend and the API result
also makes more sense.
In addition to that, when a change is made to a newsletters reply_to
address we'll clear any invalid sender_email values in that newsletter.
That makes sure we can clear the sender_reply_to value instead of
keeping the current fallback to sender_email if that one is stored.
On top of that, this change correclty updates the browse endpoint to use
the newsletter service instead of directly using the model.
fixes GRO-25
Updated @tryghost/nql to 0.12.0 and other packages that depend on it
1. SQLite: when a filter string contains /.
When we use a NQL contain/starts/endsWith filter that contains a slash,
underlyingly the whole filter will get converted to a MongoDB query, in
which we just use a regexp to represent the filter. In here we will
escape the slash: \/ as expected in a regexp. Later when we convert this
MongoDB query back to knex/SQL, we use a SQL LIKE query. Currently we
don't remove the escaping here for a normal slash. MySQL seems to ignore
this (kinda incorrect). SQLite doesn't like it, and this breaks queries
on SQLite that use slashes. The solution here is simple: remove the
backslash escaping when converting the regexp to LIKE, just like we do
with other special regexp characters.
2. We don't escape % and _, which have a special meaning in LIKE queries
Usage of % and _ is now as expected and doesn't have the special SQL
meaning anymore.
fixes https://github.com/TryGhost/Product/issues/3738https://www.notion.so/ghost/Member-Session-Invalidation-13254316f2244c34bcbc65c101eb5cc4
- Adds the transient_id column to the members table. This defaults to
email, to keep it backwards compatible (not logging out all existing
sessions)
- Instead of using the email in the cookies, we now use the transient_id
- Updating the transient_id means invalidating all sessions of a member
- Adds an endpoint to the admin api to log out a member from all devices
- Added the `all` body property to the DELETE session endpoint in the
members API. Setting it to true will sign a member out from all devices.
- Adds a UI button in Admin to sign a member out from all devices
- Portal 'sign out of all devices' will not be added for now
Related changes (added because these areas were affected by the code
changes):
- Adds a serializer to member events / activity feed endpoints - all
member fields were returned here, so the transient_id would also be
returned - which is not needed and bloats the API response size
(`transient_id` is not a secret because the cookies are signed)
- Removed `loadMemberSession` from public settings browse (not used
anymore + bad pattern)
Performance tests on site with 50.000 members (on Macbook M1 Pro):
- Migrate: 6s (adding column 4s, setting to email is 1s, dropping
nullable: 1s)
- Rollback: 2s
fixes https://github.com/TryGhost/Product/issues/4108
- Updates filters behind a new alpha feature flag so you can also filter
on members who have email disabled (because the email had a permanent
bounce, they reported spam or the email address is invalid)
- When returning members, we now also use the email_disabled flag to set
email_suppression.suppressed correctly (in case they are out of sync,
which should normally never happen).
closes https://github.com/TryGhost/Product/issues/4046
- when editing the member's email in Admin, the email_disabled field was
not recalculated, making it inconsistent with the suppression list
- now, if the new email is part of the suppression list, we set
email_disabled to true. Otherwise set it to false
