test: correct zero-change contract (distinct stamps, stable keys/ids)

custom_components/nestquest/materialize.py

@@ -151,6 +151,14 @@ async def materialize(
     Assistant's configured time zone MUST pass that same date here so a
     time zone behind the host around midnight is not rejected as "past".
 
+    ``start_date`` is clamped up to the resolved "today" (the same
+    ``today`` anchor threaded into the no-past guard), so a caller that
+    passes a ``start_date`` already in the past never yields a
+    past-dated instance: the walk simply begins on today and skips every
+    earlier date.  A range entirely in the past therefore produces zero
+    instances rather than an error, and the DAO's no-past guard remains
+    the authoritative backstop under the lock.
+
     All inputs are read in ONE locked transaction; each tuple's write then
     goes through the atomic :meth:`~.dao_instances.QuestInstancesDao.upsert_if_valid`,
     which re-validates the tuple and its current ``due_time`` in the same
@@ -184,6 +192,13 @@ async def materialize(
     start = datetime.datetime.strptime(start_date, "%Y-%m-%d").date()
     end = datetime.datetime.strptime(end_date, "%Y-%m-%d").date()
 
+    # Clamp a past start up to today (the HA-local anchor when the caller
+    # pinned one, else the host clock) so the walk can never emit a
+    # past-dated instance; the DAO's no-past guard remains the backstop.
+    # A range entirely in the past therefore yields zero instances.
+    today_date = _resolve_today(today)
+    start = max(start, today_date)
+
     count = 0
     cursor = start
     while cursor <= end:

custom_components/nestquest/quest_definitions.py

@@ -271,6 +271,7 @@ async def edit_quest_definition(
     icon: str | None | object = _UNSET,
     rule: ScheduleRule | object = _UNSET,
     windows: list[str | tuple[str, str | None]] | object = _UNSET,
+    today: datetime.date | None = None,
 ) -> CreatedQuestDefinition:
     """Edit a quest definition's metadata, rule and windows atomically.
 
@@ -292,6 +293,13 @@ async def edit_quest_definition(
     so the returned bundle is a consistent edit snapshot.  Edits change
     future instances only; no ``quest_instances`` or
     ``completion_events`` row is touched.
+
+    ``today`` optionally pins the caller-resolved HA-local date the
+    regeneration's no-past guard and horizon are anchored to (threaded
+    into :func:`~.materialize.regenerate_for_definition`); it is a plain
+    ``datetime.date`` with no HA import here — the Feature 09 service
+    supplies the hass-derived value.  When omitted, regeneration falls
+    back to the host clock, same as before.
     """
     _validate_definition_id(definition_id)
 
@@ -342,7 +350,7 @@ async def edit_quest_definition(
         schedule_rule_storage_from_record(snapshot.rule)
     )
 
-    await regenerate_for_definition(database, definition_id)
+    await regenerate_for_definition(database, definition_id, today=today)
 
     return CreatedQuestDefinition(
         definition=snapshot.definition,
@@ -356,6 +364,8 @@ async def assign_child(
     database: NestQuestDatabase,
     definition_id: int,
     child_id: int,
+    *,
+    today: datetime.date | None = None,
 ) -> list[ChildRecord]:
     """Assign ``child_id`` to the definition (idempotent).
 
@@ -369,6 +379,9 @@ async def assign_child(
     write).  Raises ValueError on any rejected argument.  Only the
     ``quest_definition_assignees`` link is written; existing instances
     and completion history are never touched.
+
+    ``today`` optionally pins the caller-resolved HA-local date threaded
+    into the assignment's regeneration (see :func:`edit_quest_definition`).
     """
     _validate_definition_id(definition_id)
     _validate_child_id(child_id)
@@ -384,7 +397,7 @@ async def assign_child(
         if message.startswith("quest definition "):
             raise ValueError(f"definition_id: {message}") from error
         raise
-    await regenerate_for_definition(database, definition_id)
+    await regenerate_for_definition(database, definition_id, today=today)
     return assignees
 
 
@@ -392,6 +405,8 @@ async def unassign_child(
     database: NestQuestDatabase,
     definition_id: int,
     child_id: int,
+    *,
+    today: datetime.date | None = None,
 ) -> bool:
     """Unassign ``child_id`` from the definition; True when removed.
 
@@ -401,6 +416,9 @@ async def unassign_child(
     was not currently assigned.  Raises ValueError on a rejected
     argument.  Only the ``quest_definition_assignees`` link is written;
     existing instances and completion history are never touched.
+
+    ``today`` optionally pins the caller-resolved HA-local date threaded
+    into the unassignment's regeneration (see :func:`edit_quest_definition`).
     """
     _validate_definition_id(definition_id)
     _validate_child_id(child_id)
@@ -411,7 +429,7 @@ async def unassign_child(
             f"definition_id: quest definition {definition_id} does not exist"
         )
     removed = await dao.remove_assignee(definition_id, child_id)
-    await regenerate_for_definition(database, definition_id)
+    await regenerate_for_definition(database, definition_id, today=today)
     return removed
 
 
@@ -419,6 +437,8 @@ async def set_quest_definition_active(
     database: NestQuestDatabase,
     definition_id: int,
     is_active: bool,
+    *,
+    today: datetime.date | None = None,
 ) -> CreatedQuestDefinition:
     """Deactivate or reactivate a definition; returns the updated view.
 
@@ -441,6 +461,10 @@ async def set_quest_definition_active(
     definition, its rule, assignees and windows all run inside the
     connection-scoped lock, so the returned bundle is a consistent
     snapshot that a concurrent mutation cannot race.
+
+    ``today`` optionally pins the caller-resolved HA-local date threaded
+    into the activation change's regeneration (see
+    :func:`edit_quest_definition`).
     """
     _validate_definition_id(definition_id)
     if not isinstance(is_active, bool):
@@ -464,7 +488,7 @@ async def set_quest_definition_active(
     rule = schedule_rule_from_storage(
         schedule_rule_storage_from_record(rule_record)
     )
-    await regenerate_for_definition(database, definition_id)
+    await regenerate_for_definition(database, definition_id, today=today)
     return CreatedQuestDefinition(
         definition=updated,
         rule=rule,

tests/test_materialize.py

@@ -631,13 +631,14 @@ def test_materialize_rejects_malformed_or_inverted_bounds(tmp_path) -> None:
 
 
 def test_materialize_accepts_ha_local_today_behind_host(tmp_path) -> None:
-    """The no-past guard compares against a caller-supplied HA-local today.
+    """The clamp and no-past guard compare against a caller-supplied HA-local today.
 
     A household time zone behind the host clock around midnight resolves a
     HA-local "today" one calendar day earlier than the host's
-    ``date.today()``.  The host-clock guard would reject that date as past,
-    so the resolved HA-local date must be threaded through ``materialize``
-    and accepted instead of raising.
+    ``date.today()``.  The walk clamps its start to that anchor: without
+    a pinned HA-local date the effective start is the host's today (one
+    day after ha_today), so the now-past day is skipped rather than
+    rejected, and only the host-today-onward days materialize.
     """
     async def _body(database):
         children = ChildrenDao(database)
@@ -653,12 +654,13 @@ def test_materialize_accepts_ha_local_today_behind_host(tmp_path) -> None:
             ["morning"],
         )
 
-        # Without the pinned HA-local today, the host-clock guard rejects
-        # the horizon start (ha_today is "yesterday" on the host).
-        with pytest.raises(ValueError, match="never generated in the past"):
-            await materialize(database, start_iso, end_iso)
+        # Without a pinned HA-local today, the walk clamps its start to the
+        # host's today (one day after ha_today); the past day is dropped and
+        # the 14 host-today-onward days materialize.
+        without_pin = await materialize(database, start_iso, end_iso)
+        assert without_pin == 14
 
-        # With today=ha_today, the same range is accepted and materialized.
+        # With today=ha_today, the full 15-day horizon materializes.
         count = await materialize(database, start_iso, end_iso, today=ha_today)
         assert count == 15
         return count
@@ -666,6 +668,50 @@ def test_materialize_accepts_ha_local_today_behind_host(tmp_path) -> None:
     _with_db(tmp_path, "materialize-ha-local-today.db")(_body)
 
 
+def test_materialize_clamps_past_start_to_today(tmp_path) -> None:
+    """A past start_date is clamped to today: no past-dated instance is created.
+
+    A caller passing a start one month in the past never yields a
+    past-dated instance: the walk clamps its effective start up to today
+    and materializes only dates on/after today, leaving the past untouched
+    rather than raising or corrupting state.
+    """
+    async def _body(database):
+        children = ChildrenDao(database)
+        child = await children.create("Ada", NOW)
+
+        today = datetime.date.today()
+        start_iso = (today - datetime.timedelta(days=30)).isoformat()
+        end_iso = (today + datetime.timedelta(days=14)).isoformat()
+
+        await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+            [child.id],
+            ["morning"],
+        )
+
+        count = await materialize(database, start_iso, end_iso, today=today)
+        assert count == 15
+
+        dao = QuestInstancesDao(database)
+        records = await dao.list_by_date_range(child.id, start_iso, end_iso)
+        assert len(records) == 15
+        assert all(r.due_date >= today.isoformat() for r in records)
+
+        # The past month is empty: no past-dated instance was ever created.
+        past_records = await dao.list_by_date_range(
+            child.id,
+            start_iso,
+            (today - datetime.timedelta(days=1)).isoformat(),
+        )
+        assert past_records == []
+        return count
+
+    _with_db(tmp_path, "materialize-past-start.db")(_body)
+
+
 def test_materialize_skips_inactive_definitions(tmp_path) -> None:
     async def _body(database):
         from custom_components.nestquest.quest_definitions import (
@@ -1067,6 +1113,342 @@ def test_regenerate_for_child_is_child_scoped(tmp_path) -> None:
     _with_db(tmp_path, "regenerate-child-scoped.db")(_body)
 
 
+#: Fixed end-to-end anchor: 2026-06-01 is a Monday AND the 1st of its month,
+#: so the two-week custody cycle is week-aligned and the yearly rule's
+#: "1st of the anchor month" is GUARANTEED to fire inside the 28-day window
+#: (pin ``today=anchor`` throughout so the walk never clamps this fixed
+#: calendar date up to the host clock).
+_E2E_ANCHOR = "2026-06-01"
+
+#: Hand-checked oracle over 2026-06-01 .. 2026-06-28 (69 entries).  Each row
+#: is one independent (definition, child, window, due_date, due_time); the
+#: definition/child slugs are substituted with runtime ids, but the dates are
+#: literal, reviewed by hand — no recurrence/presence math is re-run here.
+#: Custody (2-week cycle anchored on the Monday): A present weeks 0 and 2,
+#: B present weeks 1 and 3, C present every day.
+_E2E_EXPECTED = [
+    # daily  [A, C]  morning 09:00, every day.  A present weeks 0, 2.
+    ("daily", "A", "morning", "2026-06-01", "09:00"),
+    ("daily", "A", "morning", "2026-06-02", "09:00"),
+    ("daily", "A", "morning", "2026-06-03", "09:00"),
+    ("daily", "A", "morning", "2026-06-04", "09:00"),
+    ("daily", "A", "morning", "2026-06-05", "09:00"),
+    ("daily", "A", "morning", "2026-06-06", "09:00"),
+    ("daily", "A", "morning", "2026-06-07", "09:00"),
+    ("daily", "A", "morning", "2026-06-15", "09:00"),
+    ("daily", "A", "morning", "2026-06-16", "09:00"),
+    ("daily", "A", "morning", "2026-06-17", "09:00"),
+    ("daily", "A", "morning", "2026-06-18", "09:00"),
+    ("daily", "A", "morning", "2026-06-19", "09:00"),
+    ("daily", "A", "morning", "2026-06-20", "09:00"),
+    ("daily", "A", "morning", "2026-06-21", "09:00"),
+    # daily  C is present every day: all 28 days.
+    ("daily", "C", "morning", "2026-06-01", "09:00"),
+    ("daily", "C", "morning", "2026-06-02", "09:00"),
+    ("daily", "C", "morning", "2026-06-03", "09:00"),
+    ("daily", "C", "morning", "2026-06-04", "09:00"),
+    ("daily", "C", "morning", "2026-06-05", "09:00"),
+    ("daily", "C", "morning", "2026-06-06", "09:00"),
+    ("daily", "C", "morning", "2026-06-07", "09:00"),
+    ("daily", "C", "morning", "2026-06-08", "09:00"),
+    ("daily", "C", "morning", "2026-06-09", "09:00"),
+    ("daily", "C", "morning", "2026-06-10", "09:00"),
+    ("daily", "C", "morning", "2026-06-11", "09:00"),
+    ("daily", "C", "morning", "2026-06-12", "09:00"),
+    ("daily", "C", "morning", "2026-06-13", "09:00"),
+    ("daily", "C", "morning", "2026-06-14", "09:00"),
+    ("daily", "C", "morning", "2026-06-15", "09:00"),
+    ("daily", "C", "morning", "2026-06-16", "09:00"),
+    ("daily", "C", "morning", "2026-06-17", "09:00"),
+    ("daily", "C", "morning", "2026-06-18", "09:00"),
+    ("daily", "C", "morning", "2026-06-19", "09:00"),
+    ("daily", "C", "morning", "2026-06-20", "09:00"),
+    ("daily", "C", "morning", "2026-06-21", "09:00"),
+    ("daily", "C", "morning", "2026-06-22", "09:00"),
+    ("daily", "C", "morning", "2026-06-23", "09:00"),
+    ("daily", "C", "morning", "2026-06-24", "09:00"),
+    ("daily", "C", "morning", "2026-06-25", "09:00"),
+    ("daily", "C", "morning", "2026-06-26", "09:00"),
+    ("daily", "C", "morning", "2026-06-27", "09:00"),
+    ("daily", "C", "morning", "2026-06-28", "09:00"),
+    # weekly  [B, C]  Mondays, morning 07:00 + evening 19:00.
+    # B present weeks 1, 3 -> Mondays 06-08 and 06-22.
+    ("weekly", "B", "morning", "2026-06-08", "07:00"),
+    ("weekly", "B", "evening", "2026-06-08", "19:00"),
+    ("weekly", "B", "morning", "2026-06-22", "07:00"),
+    ("weekly", "B", "evening", "2026-06-22", "19:00"),
+    # C present every Monday: 06-01, 06-08, 06-15, 06-22.
+    ("weekly", "C", "morning", "2026-06-01", "07:00"),
+    ("weekly", "C", "evening", "2026-06-01", "19:00"),
+    ("weekly", "C", "morning", "2026-06-08", "07:00"),
+    ("weekly", "C", "evening", "2026-06-08", "19:00"),
+    ("weekly", "C", "morning", "2026-06-15", "07:00"),
+    ("weekly", "C", "evening", "2026-06-15", "19:00"),
+    ("weekly", "C", "morning", "2026-06-22", "07:00"),
+    ("weekly", "C", "evening", "2026-06-22", "19:00"),
+    # month_day  [C]  the 15th, morning 08:00.  2026-06-15 is the 15th.
+    ("month_day", "C", "morning", "2026-06-15", "08:00"),
+    # month_wd  [C]  2nd Wednesday, morning 07:30.  June 2026's 2nd
+    # Wednesday is 06-10 (the 1st is 06-03).
+    ("month_wd", "C", "morning", "2026-06-10", "07:30"),
+    # yearly  [C]  1st of June, afternoon 12:00.  Fires on 06-01.
+    ("yearly", "C", "afternoon", "2026-06-01", "12:00"),
+    # custom  [A, C]  Saturdays + Sundays, evening 20:00.
+    # A present weeks 0, 2 -> 06-06, 06-07, 06-20, 06-21.
+    ("custom", "A", "evening", "2026-06-06", "20:00"),
+    ("custom", "A", "evening", "2026-06-07", "20:00"),
+    ("custom", "A", "evening", "2026-06-20", "20:00"),
+    ("custom", "A", "evening", "2026-06-21", "20:00"),
+    # C present every weekend day.
+    ("custom", "C", "evening", "2026-06-06", "20:00"),
+    ("custom", "C", "evening", "2026-06-07", "20:00"),
+    ("custom", "C", "evening", "2026-06-13", "20:00"),
+    ("custom", "C", "evening", "2026-06-14", "20:00"),
+    ("custom", "C", "evening", "2026-06-20", "20:00"),
+    ("custom", "C", "evening", "2026-06-21", "20:00"),
+    ("custom", "C", "evening", "2026-06-27", "20:00"),
+    ("custom", "C", "evening", "2026-06-28", "20:00"),
+]
+
+# The tuples that must disappear when Cleo (C) is overridden to ABSENT for
+# 2026-06-04 .. 2026-06-06 (a mid-window stretch): her two daily chores and
+# the Saturday-evening custom chore on 06-06.
+_E2E_REMOVED = [
+    ("daily", "C", "morning", "2026-06-04", "09:00"),
+    ("daily", "C", "morning", "2026-06-05", "09:00"),
+    ("daily", "C", "morning", "2026-06-06", "09:00"),
+    ("custom", "C", "evening", "2026-06-06", "20:00"),
+]
+
+
+def _resolve_e2e(raw, def_ids, child_ids) -> set:
+    """Substitute the oracle's definition/child slugs with runtime ids."""
+    return {
+        (def_ids[slug], child_ids[child], window, due_date, due_time)
+        for slug, child, window, due_date, due_time in raw
+    }
+
+
+async def _collect_keyed(database, child_ids, start, end) -> dict:
+    """Return {(definition_id, child_id, window, due_date): (id, due_time)}.
+
+    The stable parts of an instance — its widened key, its physical id and
+    its due_time snapshot — with the refreshable ``generated_at`` stamp
+    deliberately excluded.  Comparing two of these captures the real
+    re-run contract: no rows added/removed, ids stable (upsert refreshes
+    in place, never delete + reinsert), and due_time untouched.
+    """
+    dao = QuestInstancesDao(database)
+    keyed = {}
+    for child_id in child_ids:
+        for record in await dao.list_by_date_range(child_id, start, end):
+            key = (
+                record.definition_id,
+                record.child_id,
+                record.window,
+                record.due_date,
+            )
+            keyed[key] = (record.id, record.due_time)
+    return keyed
+
+
+async def _collect_records(database, child_ids, start, end) -> set:
+    """Return the full 7-field instance records over the children/range.
+
+    Includes the ``generated_at`` stamp, so this is only used for children
+    that should NEVER be written (Ada/Bo during Cleo's override): their
+    records are truly byte-for-byte untouched, unlike the idempotency
+    re-run where the stamp legitimately refreshes.
+    """
+    dao = QuestInstancesDao(database)
+    records = set()
+    for child_id in child_ids:
+        for record in await dao.list_by_date_range(child_id, start, end):
+            records.add(
+                (
+                    record.id,
+                    record.definition_id,
+                    record.child_id,
+                    record.window,
+                    record.due_date,
+                    record.due_time,
+                    record.generated_at,
+                )
+            )
+    return records
+
+
+def test_materialize_end_to_end_six_rule_types(tmp_path, monkeypatch) -> None:
+    import custom_components.nestquest.materialize as materialize_module
+
+    # Give each materialize run a DISTINCT batch stamp: production upserts
+    # rewrite generated_at on every re-run, so a shared pinned stamp would
+    # merely hide that rewrite.  The idempotency assertion below therefore
+    # ignores generated_at and checks the stable columns only.
+    stamps = {"n": 0}
+
+    def _distinct_stamp() -> str:
+        stamps["n"] += 1
+        return f"2026-06-01T00:00:{stamps['n']:02d}+00:00"
+
+    monkeypatch.setattr(materialize_module, "_now_stamp", _distinct_stamp)
+
+    async def _body(database):
+        from custom_components.nestquest.dao_presence import (
+            PresenceOverridesDao,
+        )
+        from custom_components.nestquest.presence import PresenceSchedule
+
+        children = ChildrenDao(database)
+        schedules = PresenceSchedulesDao(database)
+        overrides = PresenceOverridesDao(database)
+
+        a = await children.create("Ada", NOW)      # present weeks 0, 2
+        b = await children.create("Bo", NOW)       # present weeks 1, 3
+        c = await children.create("Cleo", NOW)     # no schedule: always present
+
+        anchor = datetime.date.fromisoformat(_E2E_ANCHOR)
+        start_iso = anchor.isoformat()
+        end_iso = (anchor + datetime.timedelta(days=27)).isoformat()
+
+        # Two-week custody with OPPOSITE weeks, encoded through the
+        # PresenceSchedule model on the fixed Monday anchor.
+        all_week = frozenset(range(7))
+        a_schedule = PresenceSchedule(a.id, 2, anchor, {0: all_week, 1: frozenset()})
+        b_schedule = PresenceSchedule(b.id, 2, anchor, {0: frozenset(), 1: all_week})
+        await schedules.upsert_by_child(a.id, 2, start_iso, a_schedule.encode())
+        await schedules.upsert_by_child(b.id, 2, start_iso, b_schedule.encode())
+
+        def_ids = {}
+        def_ids["daily"] = (await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+            [a.id, c.id],
+            [("morning", "09:00")],
+        )).definition.id
+        def_ids["weekly"] = (await create_quest_definition(
+            database,
+            "Weekly Monday chore",
+            ScheduleRule(
+                rule_type=RuleType.WEEKLY,
+                weekday_set={0},
+                start_date=start_iso,
+            ),
+            [b.id, c.id],
+            [("morning", "07:00"), ("evening", "19:00")],
+        )).definition.id
+        def_ids["month_day"] = (await create_quest_definition(
+            database,
+            "Monthly 15th chore",
+            ScheduleRule(
+                rule_type=RuleType.MONTHLY_DAY,
+                day_of_month=15,
+                start_date=start_iso,
+            ),
+            [c.id],
+            [("morning", "08:00")],
+        )).definition.id
+        def_ids["month_wd"] = (await create_quest_definition(
+            database,
+            "Monthly 2nd-Wednesday chore",
+            ScheduleRule(
+                rule_type=RuleType.MONTHLY_WEEKDAY,
+                nth_weekday=2,
+                nth_weekday_weekday=2,
+                start_date=start_iso,
+            ),
+            [c.id],
+            [("morning", "07:30")],
+        )).definition.id
+        def_ids["yearly"] = (await create_quest_definition(
+            database,
+            "Yearly first-of-June chore",
+            ScheduleRule(
+                rule_type=RuleType.YEARLY,
+                month=anchor.month,
+                day_of_month=1,
+                start_date=start_iso,
+            ),
+            [c.id],
+            [("afternoon", "12:00")],
+        )).definition.id
+        def_ids["custom"] = (await create_quest_definition(
+            database,
+            "Weekend chore",
+            ScheduleRule(
+                rule_type=RuleType.CUSTOM_DAYS,
+                weekday_set={5, 6},
+                start_date=start_iso,
+            ),
+            [a.id, c.id],
+            [("evening", "20:00")],
+        )).definition.id
+
+        child_ids = {"A": a.id, "B": b.id, "C": c.id}
+        expected = _resolve_e2e(_E2E_EXPECTED, def_ids, child_ids)
+        removed = _resolve_e2e(_E2E_REMOVED, def_ids, child_ids)
+        all_children = (a.id, b.id, c.id)
+
+        # First run: every (definition, child, window, date) tuple, exactly.
+        first = await materialize(database, start_iso, end_iso, today=anchor)
+        assert first == len(expected)
+        assert await _collect_instances(
+            database, all_children, start_iso, end_iso
+        ) == expected
+
+        # Re-run contract: the upsert refreshes each existing row IN PLACE,
+        # so the widened key set, the physical ids and each due_time are all
+        # stable (only generated_at may refresh to the new run's stamp).
+        first_keyed = await _collect_keyed(
+            database, all_children, start_iso, end_iso
+        )
+        second = await materialize(database, start_iso, end_iso, today=anchor)
+        assert second == len(expected)
+        assert await _collect_keyed(
+            database, all_children, start_iso, end_iso
+        ) == first_keyed
+
+        # Capture Ada and Bo's complete records BEFORE the override, so we
+        # can prove they are untouched afterward.
+        a_before = await _collect_records(database, (a.id,), start_iso, end_iso)
+        b_before = await _collect_records(database, (b.id,), start_iso, end_iso)
+
+        # Cleo is ABSENT 2026-06-04 .. 2026-06-06 (mid-window).
+        await overrides.create(
+            c.id, "2026-06-04", "2026-06-06", False, note="away"
+        )
+
+        # regenerate_for_child deletes + rebuilds CLEO ONLY (Ada and Bo are
+        # never touched).  It rebuilds a 14-day rolling horizon though, so
+        # the child-scoped materialize below restores the remainder of the
+        # 28-day window without ever writing another child.
+        await regenerate_for_child(database, c.id, today=anchor)
+        await materialize(
+            database, start_iso, end_iso, child_ids=[c.id], today=anchor
+        )
+
+        # Ada and Bo are byte-for-byte untouched: id, snapshot columns and
+        # generated_at alike.
+        assert await _collect_records(
+            database, (a.id,), start_iso, end_iso
+        ) == a_before
+        assert await _collect_records(
+            database, (b.id,), start_iso, end_iso
+        ) == b_before
+
+        # Exactly Cleo's override-covered tuples vanish; every other tuple
+        # survives.
+        remaining = await _collect_instances(
+            database, all_children, start_iso, end_iso
+        )
+        assert remaining == expected - removed
+        assert remaining.isdisjoint(removed)
+        return first
+
+    _with_db(tmp_path, "materialize-e2e.db")(_body)
+
+
 def test_regenerate_for_child_rejects_non_int_child_id(tmp_path) -> None:
     async def _body(database):
         for bad in (True, False, 1.0, "1"):

tests/test_timezones.py

@@ -0,0 +1,370 @@
+"""Timezone tests for the single HA-local date source in the generation path.
+
+These pin the done-condition that every "today" read in the generation path
+derives from ``hass.config.time_zone`` — not the host clock and not UTC —
+and that the rolling horizon ``[today, today + DEFAULT_HORIZON_DAYS]`` and the
+no-past guard resolve the correct local dates around midnight and across the
+daylight-saving transitions.
+"""
+from __future__ import annotations
+
+import asyncio
+import datetime
+from types import SimpleNamespace
+from zoneinfo import ZoneInfo
+
+from custom_components.nestquest.const import DEFAULT_HORIZON_DAYS
+from custom_components.nestquest.dao_children import ChildrenDao
+from custom_components.nestquest.dao_instances import QuestInstancesDao
+from custom_components.nestquest.db import NestQuestDatabase
+from custom_components.nestquest.migrations import apply_migrations
+from custom_components.nestquest.quest_definitions import (
+    assign_child,
+    create_quest_definition,
+    edit_quest_definition,
+)
+from custom_components.nestquest.recurrence import RuleType, ScheduleRule
+
+NOW = "2026-09-14T12:00:00+00:00"
+NEW_YORK = "America/New_York"
+
+
+def _make_hass_mock():
+    from unittest.mock import AsyncMock, MagicMock
+
+    hass = MagicMock()
+    hass.async_add_executor_job = AsyncMock(side_effect=(lambda fn, *a: fn(*a)))
+    return hass
+
+
+async def _prepare(path):
+    database = NestQuestDatabase(_make_hass_mock())
+    await database.open(path)
+    await apply_migrations(database)
+    return database
+
+
+def _run(coro):
+    return asyncio.new_event_loop().run_until_complete(coro)
+
+
+def _freeze_clock(
+    monkeypatch, instant: datetime.datetime
+) -> tuple[datetime.datetime, list]:
+    """Freeze the integration's ``datetime`` clock to a fixed aware instant.
+
+    Only ``custom_components.nestquest``'s ``datetime`` binding is patched, so
+    the generation path's "today" reads this instant (localized to whatever
+    time zone it asks for) while every other module and the test harness keep
+    the real clock.  Returns ``(frozen, requested)`` where ``frozen`` is the
+    instant normalized to UTC and ``requested`` is the list of ``tzinfo``
+    objects the code under test passed to ``now`` — so a test can prove the
+    generation path actually requested the configured HA time zone rather
+    than silently reading UTC.
+    """
+    import custom_components.nestquest as nestquest
+
+    frozen = instant.astimezone(datetime.timezone.utc)
+    requested: list = []
+
+    class _FrozenDatetime(datetime.datetime):
+        @classmethod
+        def now(cls, tz=None):
+            requested.append(tz)
+            if tz is None:
+                return frozen.replace(tzinfo=None)
+            return frozen.astimezone(tz)
+
+    fake_module = SimpleNamespace(
+        datetime=_FrozenDatetime,
+        timedelta=datetime.timedelta,
+        date=datetime.date,
+    )
+    monkeypatch.setattr(nestquest, "datetime", fake_module)
+    return frozen, requested
+
+
+def _assert_requested_ha_timezone(requested, time_zone: str) -> None:
+    """Assert ``now`` was called with (only) the configured HA time zone.
+
+    This is what turns the DST tests into a real proof of the HA-local date
+    source: if the generation path regressed to ``now(timezone.utc)``, its
+    requested tz would be UTC and this fails.
+    """
+    assert requested, "the generation path never called now(tz)"
+    assert all(tz == ZoneInfo(time_zone) for tz in requested)
+
+
+def _tz_hass(time_zone: str):
+    """A minimal hass exposing only the ``config.time_zone`` the walk reads."""
+    return SimpleNamespace(config=SimpleNamespace(time_zone=time_zone))
+
+
+async def _seed_daily(database, start_iso: str) -> int:
+    """One always-present child + a daily definition starting ``start_iso``."""
+    child = await ChildrenDao(database).create("Ada", NOW)
+    await create_quest_definition(
+        database,
+        "Daily chore",
+        ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+        [child.id],
+        ["morning"],
+    )
+    return child.id
+
+
+def _local_date(instant_utc: datetime.datetime, time_zone: str) -> datetime.date:
+    return instant_utc.astimezone(ZoneInfo(time_zone)).date()
+
+
+def _assert_contiguous_horizon(records, local_today: datetime.date) -> None:
+    """The horizon is exactly today..today+horizon, no off-by-one, no gap."""
+    expected = {
+        (local_today + datetime.timedelta(days=offset)).isoformat()
+        for offset in range(DEFAULT_HORIZON_DAYS + 1)
+    }
+    assert {r.due_date for r in records} == expected
+    assert len(records) == DEFAULT_HORIZON_DAYS + 1
+
+
+def test_horizon_uses_newyork_local_date_after_local_midnight(
+    tmp_path, monkeypatch
+) -> None:
+    """A run just after New York local midnight uses the local date.
+
+    The frozen instant is 00:05 New York local time on 2026-03-10 (EDT, so
+    04:05 UTC).  The horizon must start on 2026-03-10 — the local calendar
+    date — not any host/UTC-derived value.
+    """
+    instant = datetime.datetime(
+        2026, 3, 10, 0, 5, tzinfo=ZoneInfo(NEW_YORK)
+    )
+    local_today = instant.date()
+
+    async def _body(database):
+        hass = _tz_hass(NEW_YORK)
+        _frozen, requested = _freeze_clock(monkeypatch, instant)
+        import custom_components.nestquest as nestquest
+
+        child_id = await _seed_daily(database, local_today.isoformat())
+        await nestquest._run_horizon_materialization(hass, database)
+        records = await QuestInstancesDao(database).list_by_date_range(
+            child_id,
+            local_today.isoformat(),
+            (local_today + datetime.timedelta(days=DEFAULT_HORIZON_DAYS)).isoformat(),
+        )
+        _assert_contiguous_horizon(records, local_today)
+        _assert_requested_ha_timezone(requested, NEW_YORK)
+        return len(records)
+
+    async def _main():
+        database = await _prepare(tmp_path / "tz-midnight.db")
+        try:
+            return await _body(database)
+        finally:
+            await database.close()
+
+    assert _run(_main()) == DEFAULT_HORIZON_DAYS + 1
+
+
+def test_horizon_uses_local_date_not_utc_when_host_ahead(
+    tmp_path, monkeypatch
+) -> None:
+    """The walk's "today" is HA-local, never the UTC/system calendar date.
+
+    FROZEN at 2026-01-15 03:00 UTC: the UTC calendar date is 2026-01-15, but
+    New York local is still 2026-01-14 22:00 EST.  The horizon must start on
+    2026-01-14 (local), proving the UTC date did not win.
+    """
+    instant = datetime.datetime(2026, 1, 15, 3, 0, tzinfo=datetime.timezone.utc)
+    local_today = _local_date(instant, NEW_YORK)
+    assert local_today == datetime.date(2026, 1, 14)
+    assert instant.date() == datetime.date(2026, 1, 15)
+
+    async def _body(database):
+        hass = _tz_hass(NEW_YORK)
+        _frozen, requested = _freeze_clock(monkeypatch, instant)
+        import custom_components.nestquest as nestquest
+
+        child_id = await _seed_daily(database, local_today.isoformat())
+        await nestquest._run_horizon_materialization(hass, database)
+        records = await QuestInstancesDao(database).list_by_date_range(
+            child_id,
+            local_today.isoformat(),
+            (local_today + datetime.timedelta(days=DEFAULT_HORIZON_DAYS)).isoformat(),
+        )
+        _assert_contiguous_horizon(records, local_today)
+        _assert_requested_ha_timezone(requested, NEW_YORK)
+        return len(records)
+
+    async def _main():
+        database = await _prepare(tmp_path / "tz-host-ahead.db")
+        try:
+            return await _body(database)
+        finally:
+            await database.close()
+
+    assert _run(_main()) == DEFAULT_HORIZON_DAYS + 1
+
+
+def test_horizon_resolves_spring_forward_day_without_off_by_one(
+    tmp_path, monkeypatch
+) -> None:
+    """Across the 2026-03-08 spring-forward, today and the horizon stay local.
+
+    Before the jump ("01:30 EST") and after it ("03:30 EDT") both resolve to
+    2026-03-08 local — the lost hour must not skip or repeat a calendar day —
+    and the full horizon remains 15 contiguous days.
+    """
+    zone = ZoneInfo(NEW_YORK)
+    before = datetime.datetime(2026, 3, 8, 1, 30, tzinfo=zone)  # EST
+    after = datetime.datetime(2026, 3, 8, 3, 30, tzinfo=zone)   # EDT
+    assert before.date() == after.date() == datetime.date(2026, 3, 8)
+
+    async def _body(database):
+        hass = _tz_hass(NEW_YORK)
+        import custom_components.nestquest as nestquest
+
+        requested_batches: list[list] = []
+        for instant in (before, after):
+            _frozen, requested = _freeze_clock(monkeypatch, instant)
+            requested_batches.append(requested)
+            local_today = instant.date()
+            child_id = await _seed_daily(database, local_today.isoformat())
+            await nestquest._run_horizon_materialization(hass, database)
+            records = await QuestInstancesDao(database).list_by_date_range(
+                child_id,
+                local_today.isoformat(),
+                (local_today + datetime.timedelta(days=DEFAULT_HORIZON_DAYS)).isoformat(),
+            )
+            _assert_contiguous_horizon(records, local_today)
+        requested_all = [tz for batch in requested_batches for tz in batch]
+        _assert_requested_ha_timezone(requested_all, NEW_YORK)
+        return local_today
+
+    async def _main():
+        database = await _prepare(tmp_path / "tz-spring-forward.db")
+        try:
+            return await _body(database)
+        finally:
+            await database.close()
+
+    assert _run(_main()) == datetime.date(2026, 3, 8)
+
+
+def test_horizon_resolves_fall_back_day_without_off_by_one(
+    tmp_path, monkeypatch
+) -> None:
+    """Across the 2026-11-01 fall-back, today and the horizon stay local.
+
+    The repeated 01:00-02:00 hour must not resolve to the wrong calendar
+    day: the first 01:30 (``fold=0``, EDT) and the repeated 01:30
+    (``fold=1``, EST) are distinct instants yet both resolve to 2026-11-01
+    — and both, along with 03:30 EST after the jump, materialize the same
+    15 contiguous-day horizon with no off-by-one.
+    """
+    zone = ZoneInfo(NEW_YORK)
+    first = datetime.datetime(2026, 11, 1, 1, 30, tzinfo=zone, fold=0)  # EDT
+    second = datetime.datetime(2026, 11, 1, 1, 30, tzinfo=zone, fold=1)  # EST
+    after = datetime.datetime(2026, 11, 1, 3, 30, tzinfo=zone)  # EST
+    assert first.date() == second.date() == after.date() == datetime.date(2026, 11, 1)
+    assert first.utcoffset() != second.utcoffset()  # the two repeated-hour folds
+
+    async def _body(database):
+        hass = _tz_hass(NEW_YORK)
+        import custom_components.nestquest as nestquest
+
+        requested_batches: list[list] = []
+        for instant in (first, second, after):
+            _frozen, requested = _freeze_clock(monkeypatch, instant)
+            requested_batches.append(requested)
+            local_today = instant.date()
+            child_id = await _seed_daily(database, local_today.isoformat())
+            await nestquest._run_horizon_materialization(hass, database)
+            records = await QuestInstancesDao(database).list_by_date_range(
+                child_id,
+                local_today.isoformat(),
+                (local_today + datetime.timedelta(days=DEFAULT_HORIZON_DAYS)).isoformat(),
+            )
+            _assert_contiguous_horizon(records, local_today)
+        requested_all = [tz for batch in requested_batches for tz in batch]
+        _assert_requested_ha_timezone(requested_all, NEW_YORK)
+        return local_today
+
+    async def _main():
+        database = await _prepare(tmp_path / "tz-fall-back.db")
+        try:
+            return await _body(database)
+        finally:
+            await database.close()
+
+    assert _run(_main()) == datetime.date(2026, 11, 1)
+
+
+def test_definition_business_regeneration_uses_pinned_today_not_host(
+    tmp_path, monkeypatch
+) -> None:
+    """``edit_quest_definition``/``assign_child`` anchor regeneration on
+    the caller-pinned HA-local ``today``, not the host clock.
+
+    The host clock (the ``_today`` fallback) is pinned one day AHEAD of the
+    HA-local ``today``; the guard would reject the local "today" as past
+    (and the horizon would skip it) if it consulted the host clock.  Both
+    business operations must regenerate against the pinned date and keep
+    that first local day, proving the threaded ``today`` wins.
+    """
+    import custom_components.nestquest.dao_instances as dao_instances_module
+
+    pinned = datetime.date(2026, 1, 14)
+    host_today = datetime.date(2026, 1, 15)
+    monkeypatch.setattr(dao_instances_module, "_today", lambda: host_today)
+
+    async def _body(database):
+        children = ChildrenDao(database)
+        dao = QuestInstancesDao(database)
+        horizon_end = pinned + datetime.timedelta(days=DEFAULT_HORIZON_DAYS)
+
+        a = await children.create("Ada", NOW)
+        created = await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=pinned.isoformat()),
+            [a.id],
+            [("morning", "09:00")],
+        )
+        definition_id = created.definition.id
+
+        # edit anchors on pinned: the horizon starts at the local day the
+        # host clock calls "past", so its presence proves pinned won.
+        await edit_quest_definition(
+            database,
+            definition_id,
+            windows=[("morning", "10:15")],
+            today=pinned,
+        )
+        a_records = await dao.list_by_date_range(
+            a.id, pinned.isoformat(), horizon_end.isoformat()
+        )
+        assert [r.due_date for r in a_records][0] == pinned.isoformat()
+        assert {r.due_time for r in a_records} == {"10:15"}
+        assert len(a_records) == DEFAULT_HORIZON_DAYS + 1
+
+        # assign anchors on pinned too: the new child's regeneration keeps
+        # the same pinned-local first day rather than skipping to the host day.
+        b = await children.create("Bo", NOW)
+        await assign_child(database, definition_id, b.id, today=pinned)
+        b_records = await dao.list_by_date_range(
+            b.id, pinned.isoformat(), horizon_end.isoformat()
+        )
+        assert [r.due_date for r in b_records][0] == pinned.isoformat()
+        assert len(b_records) == DEFAULT_HORIZON_DAYS + 1
+        return definition_id
+
+    async def _main():
+        database = await _prepare(tmp_path / "tz-definition-regen.db")
+        try:
+            return await _body(database)
+        finally:
+            await database.close()
+
+    assert _run(_main()) is not None