Bulk Creation of Comped Users via Admin API – Expiration Issue After approx. 15 Hours

Developer help
1

I’m working on a project where I’m creating 2300 comped users with different expiration times via the Ghost Admin API. The goal is to send email notifications to these users to alert them about their expiration.

I’ve discussed this issue with Cathy Sarisky in this thread, but we haven’t been able to pinpoint the cause of the problem.

Here’s the payload that works correctly to create the comped users under a specific tier with the desired expiration time:

// POST http://127.0.0.1:2368/ghost/api/admin/members/  

const member_payload = {
  email: 'Jamie@example.com',
  name: 'Jamie Example',
  note: 'Comped user expiration test from API',
  subscribed: true,
  labels: [{ name: "comped script", slug: "comped-script" }],
  tiers: [
    {
      id: tier_id,
      expiry_at: '2025-03-11T00:00:00.000Z',
    },
  ],
  status: "comped"
};

This payload works as expected on my staging instance Ghost ver. 5.95.0, which uses an SQLite database and is running in production mode. The issue arises after creating the comped users with the correct expiry_at timestamp. The comped users expire in approximately 15 hours (exact time is unclear) rather than at the set expiration date.

After this period, the member_products table in SQLITE initially contains the correct expiration timestamps, but after 15 hours, the table gets emptied, and the user is automatically converted to FREE.

Another challenge I’ve encountered is that I cannot test this behavior locally by changing the system time to simulate the future. Changing the OS time seems to have no impact on the comped users created via the API or manually. This leaves me with the inefficient process of waiting approx. 15 hours between each test attempt on the server.

Has anyone encountered a similar issue with bulk creation of comped users? Any advice on how to test the expiration behavior or insights into why this might be happening?

2

I’ve checked the server error logs and can see that all the newly added users were deleted by the clean-expired-comped job, which likely crashed due to SQLite database limitations.

{"name":"Log","hostname":"cloudtest","pid":26118,"level":50,"version":"5.95.0","err":{"domain":"https://ghostest.noiseamplifier.com","code":"SQLITE_ERROR","message":"insert into `members_status_events` (`created_at`, `from_status`, `id`, `member_id`, `to_status`) select CURRENT_TIMESTAMP as `created_at`, 'comped' as `from_status`, '67ac33d853ee58660613140a' as `id`, '67ab9f232b9d686606d2adbf' as `member_id`, 'free' as `to_status` union all select CURRENT_TIMESTAMP as `created_at`, 'comped' as `from_status`, '67ac33d853ee58660613140b' as `id`, 
.
.
.
.
.
(~2000 of similar records)
.
.
.
'67ac33d853ee586606131d03' as `id`, '67aba0342b9d686606d30779' as `member_id`, 'free' as `to_status` union all select CURRENT_TIMESTAMP as `created_at`, 'comped' as `from_status`, '67ac33d853ee586606131d04' as `id`, '67aba0342b9d686606d30783' as `member_id`, 'free' as `to_status` union all select CURRENT_TIMESTAMP as `created_at`, 'comped' as `from_status`, '67ac33d853ee586606131d05' as `id`, '67aba0342b9d686606d3078d' as `member_id`, 'free' as `to_status` union all select CURRENT_TIMESTAMP as `created_at`, 'comped' as `from_status`, '67ac33d853ee586606131d06' as `id`, '67aba0342b9d686606d30797' as `member_id`, 'free' as `to_status` union all select CURRENT_TIMESTAMP as `created_at`, 'comped' as `from_status`, '67ac33d853ee586606131d07' as `id`, '67aba0342b9d686606d307a1' as `member_id`, 'free' as `to_status` - SQLITE_ERROR: too many terms in compound SELECT","time":"2025-02-12T05:38:32.754Z","v":0}
{"name":"Log","hostname":"cloudtest","pid":26118,"level":50,"version":"5.95.0","err":{"domain":"https://ghosttest.noiseamplifier.com","message":"Worker for job \"clean-expired-comped\" exited with code 1","stack":"Error: Worker for job \"clean-expired-comped\" exited with code 1\n    at Worker.<anonymous> (/var/www/test_ghost/versions/5.95.0/node_modules/bree/lib/index.js:419:40)\n    at Worker.emit (node:events:517:28)\n    at Worker.emit (node:domain:489:12)\n    at [kOnExit] (node:internal/worker:303:10)\n    at Worker.<computed>.onexit (node:internal/worker:222:20)"},"msg":"Worker for job \"clean-expired-comped\" exited with code 1","time":"2025-02-12T05:38:32.789Z","v":0}
1 Like
3

Does the problem remain with the latest version of ghost? You said you’re on 5.95?

1 Like
4

Not sure now, but I tried to spin up a Ghost Pro instance today. I added around 500 comped members to see what will happen. I guess we need to wait for the clean-expired-comped job to execute. Maybe there is way to fire it up somehow manually, but I don’t know how.

That also explains why my attempt to change the time had no effect—the job is scheduled for a specific time.

Also, an interesting thing is that I don’t see any reason why these API-added members should be subject to deletion by the clean-expired-comped job. They have the correct expiration set, just like those created manually.

Thank you very much Cathy.

Screenshot 2025-02-12 at 22.34.10
Screenshot 2025-02-12 at 22.34.101870×1788 233 KB

5

That function last changed three years ago. So if there’s a bug here, it isn’t a new one.

but… we /could/ be looking at a knex bug with date handling. Or a windows bug (are you on Windows?)

    const expiredCompedRows = await db.knex('members_products')
        .where('expiry_at', '<', moment.utc().startOf('day').toISOString())
        .select('*');

^ that would seem to be the problem. Some logging might be interesting, on your dev setup.

My WSL install that I use for Ghost dev throws lots of errors around ‘toISOString()’ – related? Not sure.

1 Like
6

That is all happening on Ubuntu server. I haven’t seen that on my MacOS. But I have one user created this way on my MacOS local install as well. So I should see something shortly. I will check more logs.

7

OK, so it’s definitely mis-firing. (I might have hacked the core to fire every minute, because I’m impatient like that.)

Looking at why…

I just had an API-added member expire. Here’s what was in the DB when it happened:
expiredCompedRows[{“id”:“67ad38eb4e49814b0f14aad8”,“member_id”:“67ad38eb4e49814b0f14aad7”,“product_id”:“671eaded4cc933402fe292ec”,“sort_order”:0,“expiry_at”:1741651200000}]

Here’s one I made complimentary with the admin dashboard, which also expired:
expiredCompedRows[{“id”:“67ad38f84e49814b0f14aae6”,“member_id”:“671ec32e3ebefe38501f3874”,“product_id”:“671eaded4cc933402fe292ec”,“sort_order”:0,“expiry_at”:1740009600000}]

Huh… that’s interesting. The comparison is getting the date in isoString format, but the database appears to have epoch milliseconds.

Houston, I think I found our problem. And I’m guessing it’s a knex issue.

I looked at a production mysql site, and the expiration dates looked like isoStrings. So yeah, sqlite sites are dumping comped members because of an incorrect date comparison!

1 Like
8
1 Like
9

:partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face::partying_face:
I am just in process of settings MySQL version for further testing. So I can leave it be. It is SQLITE! Thank you SO MUCH Cathy! I can savely continue with rest of task and can sleep in piece.

1 Like
10

No problem. Thanks for helping to nail down the reproduction case. It’s a super easy fix if you decide you’d rather (briefly) patch the core while waiting on the PR merge instead of wrangling with MySQL.

1 Like