​
ADR-0024: Per-org local-time scheduling via Go-side time.LoadLocation

​
Status

Accepted

​
Tags

cron, scheduling, timezones, fieldforce, briefing

​
Decision

When a cron pipeline needs to fire at a per-org local time (e.g., “send daily briefing at 08:00 in the org’s timezone”), the eligibility check MUST be split:
  • SQL layer — apply only cheap, tz-independent filters: module enabled, feature flag on, today’s row exists (with a ±1 day UTC window to cover all offsets). Returns a candidate set.
  • Go layer — for each candidate, compute the org’s local time using time.LoadLocation(orgTimezone) and compare against the configured schedule. Decide eligibility in pure Go.
AT TIME ZONE arithmetic in SQL is forbidden for scheduling decisions. JSONB-extraction-with-timezone-cast inside a WHERE clause is forbidden. This applies to any future per-org local-time cron. The first consumer is Phase 4 fieldforce briefings.

​
Why

Per-org timezones live inside org_module_configs.config_json (per ADR-0019) — they are JSONB fields, not first-class columns. Expressing “is now() ≥ schedule_time in org’s tz?” purely in SQL would require: 
WHERE (now() AT TIME ZONE (config_json->>'timezone'))::time
      >= (config_json->>'schedule_local_time')::time
  AND (now() AT TIME ZONE (config_json->>'timezone'))::date
      NOT IN (SELECT briefing_date FROM ff_briefings WHERE org_id = om.org_id)
This is hard to read, hard to test, and DST-bug-prone. Postgres handles DST correctly given a valid IANA name, but any future schema drift (key rename, missing config row, malformed tz string) silently breaks scheduling across all orgs at once. The Go layer uses time.LoadLocation — the same IANA database, but with three benefits the SQL form lacks:
  1. Testable as a pure function. No database fixture needed.
  2. Failure-isolated per org. A malformed tz string for one org returns an error for that org; the rest of the tick proceeds.
  3. One implementation site. Every future “daily at X local time” feature uses the same helper, so the DST and IANA-correctness story is told once.
Phase 4 is the first feature to schedule on per-org local time. It sets the template. Rejected alternatives:
  • AT TIME ZONE in SQL. Distributes timezone math across every scheduling query and every feature’s repo layer. Rejected.
  • Per-org tick goroutine. One goroutine per org, each with its own ticker keyed to local schedule. Avoids the candidate scan but explodes with org count. Rejected.
  • First-class timezone column on organizations. Would simplify the SQL slightly but breaks the ADR-0019 decision to keep per-module config inside org_module_configs. Rejected for consistency.

​
How it works

The repo returns a candidate set with cheap filters applied: 
SELECT om.org_id, om.config_json,
       b.briefing_date AS today_briefing_date
FROM org_module_configs om
JOIN admin_feature_flags af ON af.key = 'fieldforce.briefing' AND af.is_enabled = true
LEFT JOIN org_feature_flags of ON of.org_id = om.org_id AND of.key = 'fieldforce.briefing'
LEFT JOIN ff_briefings b ON b.org_id = om.org_id
     AND b.briefing_date BETWEEN (current_date - 1) AND (current_date + 1)
WHERE om.module = 'fieldforce'
  AND (of.is_enabled IS NULL OR of.is_enabled = true)
The BETWEEN current_date - 1 AND current_date + 1 window covers every realistic UTC-offset boundary so the LEFT JOIN finds the relevant row regardless of which side of UTC midnight the org sits on. Go layer (in the job, not the repo): 
loc, err := time.LoadLocation(cfg.Briefing.Timezone)
if err != nil {
    log.Warn("invalid org timezone", "org", row.OrgID, "tz", cfg.Briefing.Timezone)
    continue
}
localNow := now.In(loc)
if localNow.Format("15:04") < cfg.Briefing.ScheduleLocalTime {
    continue // not yet
}
localDate := localNow.Format("2006-01-02")
if row.todayBriefingDate == localDate {
    continue // already generated for this local day
}
enqueue(row.OrgID, localDate)
briefing_date semantics: the value stored is whatever the org’s tz says at the moment the row is created. No retroactive remapping if the org changes timezone later — existing rows keep their original date.

​
Known limitations

  • The candidate scan over org_module_configs is a sequential scan with JSONB extraction (cheap fields). At realistic org counts (≤ low thousands) this is invisible; at much larger counts a derived index or denormalized column may be warranted.
  • time.LoadLocation requires the Go binary’s tzdata or the OS tzdata to be present. The Dockerfile already copies tzdata; new build pipelines must preserve this.
  • An org with an invalid tz string is silently skipped (with a warning log). There is no UI gate today preventing admins from saving a bad value — Phase 4 admin UI should validate against time.LoadLocation client-side, but the worker is the safety net.

​
Rules for agents

  • New per-org local-time cron features MUST place tz arithmetic in Go using time.LoadLocation, not in SQL.
  • The repo MUST return a candidate set; eligibility decisions belong to the use case / job layer.
  • The candidate scan MUST use a ±1 day UTC window when joining “today’s” row to cover offset boundaries.
  • A snapshot date column (e.g., briefing_date) stores the org-local date at creation time and is never remapped.

​
Bad pattern (do not generate)

-- Per-row tz cast inside WHERE — distributes DST logic across the codebase
SELECT *
FROM org_module_configs om
WHERE (now() AT TIME ZONE (om.config_json->>'timezone'))::time
      >= (om.config_json->>'schedule_local_time')::time

​
Good pattern

See “How it works” above. The shape is: SQL candidate set → Go-side tz math.