122 lines
4.1 KiB
Markdown
122 lines
4.1 KiB
Markdown
# 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:
|
|
```sql
|
|
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
|
|
```bash
|
|
# 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
|
|
```bash
|
|
# 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
|
|
```typescript
|
|
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
|
|
```bash
|
|
# 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
|
|
```
|