Subscription Expires One Day Early

A 30-day subscription purchased on 2024-11-01 should grant access through the end of 2024-11-30. The expiry datetime is stored as 2024-11-30T00:00:00Z — the start of the last valid day in UTC — rather than 2024-12-01T00:00:00Z (the exclusive upper bound). The validity check NOW() >= expiry_at fires at midnight UTC on 2024-11-30, which is 7:00 PM EST on 2024-11-29 for a user in America/New_York (UTC−5). The subscription appears expired on November 29, one calendar day earlier than the user was told. This is a datetime boundary error: the exclusive expiry bound is set to start_of_day(last_valid_day) instead of start_of_day(last_valid_day + 1).

HighIntermediateManual testingAPI testingBoundary value testing

// UNDERSTAND

// Symptoms

A user in America/New_York (UTC−5) loses subscription access at 7:00 PM EST on 2024-11-29, even though the subscription was sold as valid through 2024-11-30
The subscription status page shows 'Expired' on November 29 for US Eastern users
Customer support receives complaints that subscriptions end one day earlier than the stated expiry date
The subscription expiry datetime stored in the database is 2024-11-30T00:00:00Z but users expected access until 2024-12-01T00:00:00Z
Users in UTC+0 lose access at the correct UTC midnight boundary, while users west of UTC lose access hours early in their local time

// Root Cause

The expiry datetime is computed as start_of_day(last_valid_day) — storing 2024-11-30T00:00:00Z instead of the correct exclusive upper bound start_of_day(last_valid_day + 1) = 2024-12-01T00:00:00Z. The validity check NOW() >= 2024-11-30T00:00:00Z immediately marks the subscription expired at the start of the last valid day rather than at the start of the day after it.
The developer treated the expiry as an inclusive endpoint stored at midnight of the last valid day, but the comparison operator (>=) makes it an exclusive endpoint — the subscription is expired for the entirety of 2024-11-30 instead of remaining active for it.

// Where It Appears

SaaS subscription billing systems where plan duration is stored as a datetime rather than a date
Trial period management where the trial end date is stored as start-of-day rather than end-of-day
Access control checks that compare server UTC time against a stored expiry datetime
Mobile applications that check subscription validity client-side using the device's local clock

// REPRODUCE & TEST

// How to Reproduce

01Create a test user account and start a 30-day subscription on 2024-11-01; the stored expiry_at should be 2024-11-30T00:00:00Z (confirm via GET /api/subscriptions/me)
02In the test environment, advance the server clock to 2024-11-30T00:00:01Z (one second past midnight UTC on the 30th) OR set the browser timezone to America/New_York (UTC−5) and advance the clock to 2024-11-29T19:00:01 EST (= 2024-11-30T00:00:01Z)
03Navigate to a page that checks subscription status (e.g. /dashboard or /subscription/status)
04Observe whether the subscription shows as 'Active' or 'Expired'
05The expected result is 'Active' — the user paid for access through all of 2024-11-30; the bug manifests as 'Expired'

// Test Data Needed

A test user account with a 30-day subscription whose expiry_at = '2024-11-30T00:00:00Z'
Ability to manipulate the server clock in the test environment, or a staging environment where the subscription record can be manually set to the boundary date
Browser set to America/New_York (UTC−5) to reproduce the local-time discrepancy

// Manual Testing Ideas

Create a short-duration test plan (e.g. 1-hour or 1-day) and monitor the exact UTC second when access is revoked; confirm it matches start_of_day(last_valid_day) rather than start_of_day(last_valid_day + 1)
Check subscription access at three boundary points: one minute before midnight UTC on the last valid day (should be Active), at midnight UTC (bug manifests as Expired), and one minute after midnight UTC on the day after (should be Expired for both correct and buggy implementations)
Test from an America/New_York browser (UTC−5): a user should have full access on November 29 EST even though midnight UTC November 30 has passed
Inspect the raw expiry_at value via GET /api/subscriptions/me and verify whether it is 2024-11-30T00:00:00Z or 2024-12-01T00:00:00Z

// API Testing Ideas

Call GET /api/subscriptions/me and record the expiry_at field — confirm its value is 2024-11-30T00:00:00Z
Using a test clock or a subscription record with expiry_at set to 30 seconds in the future, poll GET /api/subscriptions/me every 5 seconds and record the exact UTC timestamp when status changes from 'active' to 'expired'
Assert the status changes to 'expired' only at or after 2024-12-01T00:00:00Z — not at 2024-11-30T00:00:00Z
If the status changes at 2024-11-30T00:00:00Z, the bug is confirmed: the expiry boundary is off by exactly one day

// Automation Idea

Using a test environment that supports clock injection, set a subscription's expiry_at to a known future datetime (e.g. T+60 seconds). Poll GET /api/subscriptions/me every 5 seconds. Assert the status remains 'active' until T+60 seconds has passed. Record the exact UTC second when status changes to 'expired' and assert it is T+60 seconds (or later), not T+60 seconds − 24 hours. This catches the one-day-early expiry without waiting for a real calendar boundary.

// Expected Result

A subscription purchased on 2024-11-01 with a 30-day term remains active through 2024-11-30T23:59:59 in any timezone, expiring at or after 2024-12-01T00:00:00Z. For a user in America/New_York (UTC−5), access remains until at least 2024-11-30T19:00:00 EST (= 2024-12-01T00:00:00Z).

// Actual Result (Example)

GET /api/subscriptions/me returns expiry_at: '2024-11-30T00:00:00Z'. At 2024-11-30T00:00:01Z — which is 2024-11-29T19:00:01 EST for a user in America/New_York (UTC−5) — the subscription status changes to 'expired'. The user loses access on November 29 in their local timezone, one full calendar day before the expected expiry of November 30.

// REPORT IT

Example Bug Report

Title: 30-day subscription expires on 2024-11-29 in America/New_York instead of 2024-11-30
Severity: High
Environment: Staging environment Chrome 124 with timezone set to America/New_York (UTC−5) Test user — 30-day subscription purchased 2024-11-01 expiry_at stored as 2024-11-30T00:00:00Z
Steps to Reproduce: 01Log in as the test user in a browser with timezone set to America/New_York (UTC−5)
02Confirm the subscription is active via GET /api/subscriptions/me; note expiry_at = '2024-11-30T00:00:00Z'
03Advance the test environment clock to 2024-11-30T00:00:01Z (= 2024-11-29T19:00:01 EST)
04Reload the subscription status page at /subscription/status
05Observe the subscription status shown on the page
Expected Result: Subscription status shows 'Active'. Access should remain until 2024-12-01T00:00:00Z.
Actual Result: Subscription status shows 'Expired' at 2024-11-30T00:00:01Z — which is 7:00 PM EST on 2024-11-29 in America/New_York. The stored expiry_at (2024-11-30T00:00:00Z) is the start of the last valid day, not the exclusive upper bound; the validity check fires exactly 24 hours too early.
Impact: Users in timezones west of UTC lose subscription access hours before the expected date. At UTC−5, this is 5 hours early on the final day. Customer support and chargeback volume increases, and trust in the billing system is damaged.