SkyMoney/TIMEZONE_ROLLOVER_EXPLAINED.md

Timezone-Aware Job Scheduling

How It Works

Cron Schedule (Every 15 Minutes)

*/15 * * * * → Runs at: 00:00, 00:15, 00:30, 00:45, 01:00, 01:15, ...

Each Run:

1. Query all candidate plans (across ALL users, all timezones)
2. For each plan, check: "Is it past target hour in THIS user's timezone?"
3. If yes → process the plan
4. If no → skip until next run (15 min later)

Real World Example: Rollover at 6 AM Local Time

Timeline Across Timezones (Dec 17, 2025)

UTC Time	Los Angeles (UTC-8)	New York (UTC-5)	London (UTC+0)	Tokyo (UTC+9)	Action
21:00 Dec 16	1:00 PM Dec 16	4:00 PM Dec 16	9:00 PM Dec 16	6:00 AM Dec 17	✅ Process Tokyo users
00:00 Dec 17	4:00 PM Dec 16	7:00 PM Dec 16	12:00 AM Dec 17	9:00 AM Dec 17	(Tokyo already done)
06:00 Dec 17	10:00 PM Dec 16	1:00 AM Dec 17	6:00 AM Dec 17	3:00 PM Dec 17	✅ Process London users
11:00 Dec 17	3:00 AM Dec 17	6:00 AM Dec 17	11:00 AM Dec 17	8:00 PM Dec 17	✅ Process NYC users
14:00 Dec 17	6:00 AM Dec 17	9:00 AM Dec 17	2:00 PM Dec 17	11:00 PM Dec 17	✅ Process LA users

Processing Window

• With 15-min cron: Users processed within 0-15 minutes after their local 6 AM
• With hourly cron: Users processed within 0-60 minutes after their local 6 AM
• With 5-min cron: Users processed within 0-5 minutes after their local 6 AM

Why This Approach?

✅ Advantages

1. No per-user scheduling needed - single cron handles all users
2. Automatic timezone handling - works for any timezone without config
3. Scalable - adding users doesn't increase job complexity
4. Self-correcting - if a job misses a run, next run catches it

⚠️ Considerations

1. Small delay - Users processed within 15 min (not exactly at 6:00 AM)
2. Query overhead - Queries all candidate plans every 15 min
3. Database filtering - Good indexes on dueOn and nextPaymentDate are important

🔄 Alternative Approach (Not Implemented)

Store each user's next run time as UTC timestamp:

nextRolloverAt = '2025-12-17T21:00:00Z' -- for Tokyo user's 6 AM

Then query: WHERE nextRolloverAt <= NOW()

Trade-offs:

• ✅ Exact timing - no delay
• ✅ More efficient query - index on single timestamp column
• ❌ More complex - need to update nextRolloverAt after each run
• ❌ DST complications - need to recalculate when timezone rules change

Configuration

Environment Variables

# Rollover: default = every 15 minutes
ROLLOVER_SCHEDULE_CRON="*/15 * * * *"

# Auto-payment: default = every 15 minutes  
AUTO_PAYMENT_SCHEDULE_CRON="*/15 * * * *"

# For high-precision (every 5 minutes):
ROLLOVER_SCHEDULE_CRON="*/5 * * * *"

# For lower load (hourly):
ROLLOVER_SCHEDULE_CRON="0 * * * *"

Cron Format

* * * * *
│ │ │ │ │
│ │ │ │ └─ Day of week (0-7, both 0 and 7 = Sunday)
│ │ │ └─── Month (1-12)
│ │ └───── Day of month (1-31)
│ └─────── Hour (0-23)
└───────── Minute (0-59)

Examples:
*/15 * * * *  → Every 15 minutes
0 * * * *     → Every hour at :00
0 6 * * *     → Once daily at 6 AM UTC
*/5 * * * *   → Every 5 minutes

Testing

Test Specific Timezone

# 1. Change user timezone
docker compose exec -T postgres psql -U app -d skymoney -c \
  "UPDATE \"User\" SET timezone = 'Asia/Tokyo' WHERE id = 'user-id';"

# 2. Run test script
npx tsx src/scripts/test-timezone-jobs.ts user-id

Simulate Specific UTC Time

import { rolloverFixedPlans } from "./src/jobs/rollover.js";

// Simulate running at 21:00 UTC (= 6 AM Tokyo)
await rolloverFixedPlans(prisma, "2025-12-17T21:00:00Z", { dryRun: true });

Test Different Timezones

# Tokyo (UTC+9) - 6 AM = 21:00 UTC previous day
npx tsx -e "import { rolloverFixedPlans } from './src/jobs/rollover.js'; ..."

# Los Angeles (UTC-8) - 6 AM = 14:00 UTC same day
# London (UTC+0) - 6 AM = 06:00 UTC same day
# New York (UTC-5) - 6 AM = 11:00 UTC same day