getHouseholdTimezone(db) returns the stored household_timezone value when the app_config row is present (D-05)
getHouseholdTimezone(db) falls back to process.env.TZ when no row is stored, then to Intl.DateTimeFormat().resolvedOptions().timeZone when TZ is also unset (D-06)
isValidIanaTimezone accepts real IANA zones including 'UTC', 'Etc/UTC', and 'America/Chicago', and rejects garbage like 'Not/AZone'
path
provides
exports
apps/api/src/lib/householdTimezone.ts
Shared stored-timezone accessor (D-05 single accessor) + IANA validator
getHouseholdTimezone
isValidIanaTimezone
path
provides
apps/api/tests/lib/householdTimezone.test.ts
RED→GREEN unit coverage for accessor fallback chain + validator
from
to
via
pattern
apps/api/src/lib/householdTimezone.ts
app_config row key='household_timezone'
drizzle select on appConfig
appConfig.*household_timezone
Create the single shared accessor and IANA validator that every other Phase 18 task
depends on. `getHouseholdTimezone(db)` reads the `household_timezone` key from `app_config`
and applies the D-06 fallback chain; `isValidIanaTimezone(tz)` validates an IANA string via
`Intl.DateTimeFormat` try/catch.
Purpose: D-05 mandates ONE accessor so reminderScheduler.ts and outboxWorker.ts cannot
drift; D-06 mandates the exact process.env.TZ ?? Intl… fallback so existing all-day tests
stay green with no modification. This plan is the foundation both later waves consume.
Output: apps/api/src/lib/householdTimezone.ts exporting getHouseholdTimezone + isValidIanaTimezone, fully unit-tested.
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/phases/18-auto-timezone-detection-and-ability-to-change-timezone/18-CONTEXT.md
@.planning/phases/18-auto-timezone-detection-and-ability-to-change-timezone/18-RESEARCH.md
@.planning/phases/18-auto-timezone-detection-and-ability-to-change-timezone/18-PATTERNS.md
Task 1: RED — failing unit tests for getHouseholdTimezone fallback chain + isValidIanaTimezone
apps/api/tests/lib/householdTimezone.test.ts
- apps/api/tests/lib/requireAdmin.test.ts (vitest structure + how a mock Drizzle db is built for a lib helper)
- apps/api/tests/broker/reminderScheduler.test.ts (how existing tests pin process.env.TZ in beforeEach/afterEach — mirror the env save/restore)
- apps/api/src/db/schema.ts §282 (appConfig key/value/updatedAt shape — the row this accessor reads)
- .planning/phases/18-auto-timezone-detection-and-ability-to-change-timezone/18-RESEARCH.md (Pitfall 2 = why 'UTC' must pass; Validation Architecture → Test Map)
- getHouseholdTimezone: when the mocked db returns a row { value: 'America/Chicago' } for key 'household_timezone' → resolves to 'America/Chicago'.
- getHouseholdTimezone: when the mocked db returns no row (empty array) AND process.env.TZ='America/New_York' → resolves to 'America/New_York'.
- getHouseholdTimezone: when no row AND process.env.TZ is deleted → resolves to Intl.DateTimeFormat().resolvedOptions().timeZone (assert it equals that exact runtime value, not a hardcoded string).
- getHouseholdTimezone: when the row exists but value is null → falls through to the process.env.TZ branch (null is treated as unset).
- isValidIanaTimezone: returns true for 'UTC', 'Etc/UTC', 'America/Chicago', 'Europe/London'.
- isValidIanaTimezone: returns false for 'Not/AZone', '', 'Mars/Phobos'.
Create the test file mocking the Drizzle query chain the same way requireAdmin.test.ts mocks
`db` (select→from→where→limit returning a Promise of an array). Save and restore
`process.env.TZ` around each test (capture original in beforeEach, restore in afterEach) so the
fallback-branch tests do not leak env state. Import the not-yet-existing symbols
`getHouseholdTimezone` and `isValidIanaTimezone` from `../../src/lib/householdTimezone.js`.
Run the suite and confirm it FAILS because the module does not exist yet (import/resolution
error is the expected RED). Commit as `test(18-01): add failing tests for household timezone accessor + IANA validator`.
pnpm --filter @familysync/api exec vitest run tests/lib/householdTimezone.test.ts 2>&1 | grep -qi "fail\|error\|cannot find" && echo RED-OK
- File `apps/api/tests/lib/householdTimezone.test.ts` exists with the six behavior cases above.
- The test run fails (RED) prior to any implementation — failure is module-not-found / missing-export, not a syntax error in the test.
- A `test(18-01): ...` commit precedes the implementation commit.
RED gate met: failing test committed, fails for the right reason (no implementation).
Task 2: GREEN — implement getHouseholdTimezone + isValidIanaTimezone
apps/api/src/lib/householdTimezone.ts
- apps/api/src/lib/requireAdmin.ts (analog: single-purpose lib helper, drizzle select→from→where→limit(1), typed return — copy import + query style)
- apps/api/src/db/schema.ts §282 (appConfig — import { appConfig } from '../db/schema.js')
- apps/api/src/broker/reminderScheduler.ts §247 (the EXACT fallback expression `process.env.TZ ?? Intl.DateTimeFormat().resolvedOptions().timeZone` to reproduce verbatim per D-06)
- .planning/phases/18-auto-timezone-detection-and-ability-to-change-timezone/18-PATTERNS.md (householdTimezone.ts section — full target shape)
Create `apps/api/src/lib/householdTimezone.ts`. Export `async getHouseholdTimezone(db: MySql2Database): Promise` that selects `{ value: appConfig.value }` from `appConfig`
where `eq(appConfig.key, 'household_timezone')` `.limit(1)`, destructures `const [row]`, and returns
`row?.value ?? process.env.TZ ?? Intl.DateTimeFormat().resolvedOptions().timeZone` — the D-06 fallback
reproduced verbatim from the bare lookups. Use the literal key string `'household_timezone'` (D-01).
Export `isValidIanaTimezone(tz: string): boolean` that returns true if `Intl.DateTimeFormat(undefined, { timeZone: tz })` does not throw, false on catch — do NOT use `Intl.supportedValuesOf` (it omits 'UTC', RESEARCH Pitfall 2).
Import types via `import type { MySql2Database } from 'drizzle-orm/mysql2'` and `import type * as schema from '../db/schema.js'`.
Run the test; iterate until GREEN. Commit as `feat(18-01): implement household timezone accessor + IANA validator`.
pnpm --filter @familysync/api exec vitest run tests/lib/householdTimezone.test.ts
- `apps/api/src/lib/householdTimezone.ts` exports `getHouseholdTimezone` and `isValidIanaTimezone`.
- Source contains the literal key `'household_timezone'` and the verbatim fallback `process.env.TZ ?? Intl.DateTimeFormat().resolvedOptions().timeZone`.
- Source does NOT reference `Intl.supportedValuesOf` (assert: `grep -L "supportedValuesOf" apps/api/src/lib/householdTimezone.ts`).
- All six behavior cases pass (GREEN).
- `pnpm --filter @familysync/api exec tsc --noEmit` passes for the new file.
- A `feat(18-01): ...` commit follows the RED commit.
GREEN gate met: implementation passes all unit tests; typecheck clean.
<threat_model>
Trust Boundaries
Boundary
Description
scheduler/outbox → DB
trusted server process reads a stored config value; the value was written through the validated admin/seed path (Plan 02)
STRIDE Threat Register
Threat ID
Category
Component
Disposition
Mitigation Plan
T-18-01
Tampering
stored household_timezone consumed by computeAlertInstantUtc
mitigate
This plan only READS the value; all writes go through Plan 02's Zod isValidIanaTimezone refine, so a garbage zone can never be stored to break the scheduler. The accessor additionally never passes an unvalidated value (write path is the gate).
T-18-02
Denial of Service
getHouseholdTimezone DB read per scheduler tick
accept
Single PK lookup on a 60s interval; negligible. Read-per-run chosen for correctness (changes propagate within one tick) — RESEARCH A1.
T-18-SC
Tampering
npm/pip/cargo installs
mitigate
No new packages installed this plan (RESEARCH Package Legitimacy Audit: zero installs). No legitimacy checkpoint needed.
</threat_model>
- `pnpm --filter @familysync/api exec vitest run tests/lib/householdTimezone.test.ts` green.
- `pnpm --filter @familysync/api test -- --run` still green (no regression in existing suites).
