test: three-run idempotency with stable instance ids

custom_components/nestquest/dao_instances.py

@@ -277,6 +277,117 @@ class QuestInstancesDao:
         assert instance is not None
         return instance
 
+    async def upsert_if_valid(
+        self,
+        definition_id: int,
+        child_id: int,
+        due_date: str,
+        generated_at: str,
+        *,
+        window: str,
+        due_time: str | None = None,
+    ) -> QuestInstanceRecord | None:
+        """Insert one dated instance iff its tuple is still materializable.
+
+        This is the materialization walk's single write path.  Unlike the
+        generic :meth:`upsert`, which validates only existence and
+        assignment, this method re-validates EVERY materialization
+        precondition in the SAME connection-lock-held transaction as the
+        INSERT — closing the check-then-insert gap a concurrent config
+        write could otherwise slip through:
+
+        - the definition exists AND is active (a definition deactivated
+          after the input snapshot is skipped, never written);
+        - the child exists AND is active (a deactivated child is skipped);
+        - the (definition, child) assignment link still exists (a removed
+          assignment is skipped);
+        - the window is still declared on the definition (a removed window
+          is skipped).
+
+        The live checks above ONLY decide whether the tuple is still
+        valid; they never read the window's ``due_time``.  The instance is
+        written with the caller-supplied ``due_time`` — the value snapshotted
+        from the coherent input read — so a window-time edit landing
+        mid-walk cannot leak a newer time into a batch that was already
+        snapshotted against older rule/presence/assignment decisions.
+
+        Returns the stored instance, or ``None`` when a precondition
+        failed — the tuple no longer materializable, which the walk SKIPS
+        rather than raising, so the rest of the batch is unaffected.  The
+        no-past rule and the immutable-completed-instance refusal are
+        preserved from :meth:`upsert` (those are not configuration
+        invalidations and still raise ValueError).
+        """
+        _validate_date(due_date, "due_date")
+        _validate_window(window)
+        # Fail fast on obviously-past dates before queuing on the lock;
+        # the authoritative check re-runs under the lock (see upsert).
+        today = datetime.date.today().isoformat()
+        if due_date < today:
+            raise ValueError(
+                f"instances are never generated in the past: due_date "
+                f"{due_date!r} is before today {today!r}"
+            )
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                today = datetime.date.today().isoformat()
+                if due_date < today:
+                    raise ValueError(
+                        f"instances are never generated in the past: "
+                        f"due_date {due_date!r} is before today {today!r}"
+                    )
+                still_valid = await self._database.fetch_one(
+                    "SELECT 1 FROM quest_definitions d "
+                    "JOIN quest_definition_assignees a "
+                    "ON a.definition_id = d.id "
+                    "JOIN children c ON c.id = a.child_id "
+                    "JOIN quest_definition_windows w "
+                    "ON w.definition_id = d.id "
+                    "WHERE d.id = ? AND a.child_id = ? AND w.window = ? "
+                    "AND d.is_active = 1 AND c.is_active = 1 LIMIT 1",
+                    (definition_id, child_id, window),
+                )
+                if still_valid is None:
+                    # A config change invalidated the tuple since the
+                    # snapshot: skip it rather than abort the batch.
+                    return None
+                existing = await self._database.fetch_one(
+                    "SELECT 1 FROM completion_events "
+                    "WHERE instance_id = (SELECT id FROM quest_instances "
+                    "WHERE definition_id = ? AND child_id = ? "
+                    "AND due_date = ? AND window = ?)",
+                    (definition_id, child_id, due_date, window),
+                )
+                if existing is not None:
+                    raise ValueError(
+                        f"instance for (definition {definition_id}, "
+                        f"child {child_id}, due_date {due_date!r}, "
+                        f"window {window!r}) already has a completion "
+                        "event and is immutable; it cannot be regenerated"
+                    )
+                await self._database.execute(
+                    "INSERT INTO quest_instances (definition_id, child_id, "
+                    "window, due_date, due_time, generated_at) "
+                    "VALUES (?, ?, ?, ?, ?, ?) "
+                    "ON CONFLICT (definition_id, child_id, due_date, "
+                    "window) DO UPDATE SET "
+                    "due_time = excluded.due_time, "
+                    "generated_at = excluded.generated_at",
+                    (
+                        definition_id,
+                        child_id,
+                        window,
+                        due_date,
+                        due_time,
+                        generated_at,
+                    ),
+                )
+                instance = await self.get(
+                    definition_id, child_id, due_date, window
+                )
+        assert instance is not None
+        return instance
+
     async def get(
         self,
         definition_id: int,

custom_components/nestquest/dao_presence.py

@@ -277,3 +277,58 @@ class PresenceOverridesDao:
             (override_id,),
         )
         return result.rowcount > 0
+
+
+async def read_snapshot_unlocked(
+    database: NestQuestDatabase,
+    child_ids: list[int],
+    range_start: str,
+    range_end: str,
+) -> tuple[
+    dict[int, PresenceScheduleRecord],
+    dict[int, list[PresenceOverrideRecord]],
+]:
+    """Read schedules and overrides for ``child_ids`` WITHOUT locking.
+
+    MUST be called inside the connection lock and an open transaction, so
+    the schedules and overrides returned here stay coherent with every
+    other table the caller reads inside that same transaction (the
+    materialization walk's single input snapshot does exactly that).  The
+    date range comes already validated by the caller.
+
+    Returns ``(schedules, overrides)``:
+
+    - ``schedules`` maps ``child_id`` to its :class:`PresenceScheduleRecord`
+      for every child that HAS a schedule row.  A child absent from the
+      mapping has no schedule, i.e. is present every day (Feature 05).
+    - ``overrides`` maps ``child_id`` to that child's overrides overlapping
+      ``[range_start, range_end]`` (inclusive boundaries), ordered by start
+      date; a child with no matching override is absent from the mapping.
+
+    ``child_ids`` must name existing children (the caller derives them
+    from a trusted snapshot, within the same transaction).
+    """
+    children = sorted(set(child_ids))
+    if not children:
+        return {}, {}
+    placeholders = ",".join("?" for _ in children)
+    schedule_rows = await database.fetch_all(
+        f"SELECT {_SCHEDULE_COLUMNS} FROM presence_schedules "
+        f"WHERE child_id IN ({placeholders})",
+        tuple(children),
+    )
+    override_rows = await database.fetch_all(
+        f"SELECT {_OVERRIDE_COLUMNS} FROM presence_overrides "
+        f"WHERE child_id IN ({placeholders}) AND end_date >= ? "
+        "AND start_date <= ? ORDER BY start_date",
+        tuple(children) + (range_start, range_end),
+    )
+    schedules = {
+        record.child_id: record
+        for record in map(_schedule_from_row, schedule_rows)
+    }
+    overrides: dict[int, list[PresenceOverrideRecord]] = {}
+    for row in override_rows:
+        record = _override_from_row(row)
+        overrides.setdefault(record.child_id, []).append(record)
+    return schedules, overrides

custom_components/nestquest/dao_rules.py

@@ -163,6 +163,24 @@ class QuestDefinitionWindowRecord:
     due_time: str | None
 
 
+@dataclass(frozen=True)
+class QuestDefinitionSnapshot:
+    """A definition plus its rule, assignees and windows, read atomically.
+
+    Returned by :meth:`QuestDefinitionsDao.create_with_rule_and_windows`
+    and :meth:`QuestDefinitionsDao.edit_definition` with the rule,
+    assignees and windows read inside the SAME transaction that wrote
+    them, so the snapshot is a consistent creation/edit-time view — a
+    concurrent rule, assignee or window mutation cannot change it
+    between the write and the read.
+    """
+
+    definition: QuestDefinitionRecord
+    rule: ScheduleRuleRecord
+    assignees: list[ChildRecord]
+    windows: list[QuestDefinitionWindowRecord]
+
+
 _RULE_COLUMNS = (
     "id, rule_type, interval, weekday_set, day_of_month, nth_weekday, "
     "month, start_date, end_date"
@@ -313,6 +331,28 @@ def schedule_rule_from_storage(storage: ScheduleRuleStorage) -> ScheduleRule:
     )
 
 
+def schedule_rule_storage_from_record(
+    record: ScheduleRuleRecord,
+) -> ScheduleRuleStorage:
+    """Copy a rule row's value columns into a storage mapping.
+
+    ``ScheduleRuleRecord`` is the read shape (it carries ``id``);
+    :func:`schedule_rule_from_storage` consumes the value-only
+    :class:`ScheduleRuleStorage` shape, so callers re-reading a stored
+    rule convert through this helper.
+    """
+    return ScheduleRuleStorage(
+        rule_type=record.rule_type,
+        interval=record.interval,
+        weekday_set=record.weekday_set,
+        day_of_month=record.day_of_month,
+        nth_weekday=record.nth_weekday,
+        month=record.month,
+        start_date=record.start_date,
+        end_date=record.end_date,
+    )
+
+
 def _reject_forbidden_storage_fields(
     storage: ScheduleRuleStorage, shape: RuleType
 ) -> None:
@@ -400,6 +440,37 @@ def _single_weekday_from_csv(value: str | None) -> int:
         ) from None
 
 
+async def _insert_rule_row(
+    database: NestQuestDatabase, storage: ScheduleRuleStorage
+) -> int:
+    """Insert one ``schedule_rules`` row and return its new id.
+
+    Runs inside the caller's transaction (no lock or transaction of its
+    own) so a multi-table write can persist the rule together with the
+    rows that reference it.  The caller is responsible for validating
+    the storage fields — :meth:`ScheduleRulesDao.create` validates the
+    date shape, and :meth:`QuestDefinitionsDao.create_with_rule_and_windows`
+    receives storage already mapped from a validated
+    :class:`~.recurrence.ScheduleRule`.
+    """
+    result = await database.execute(
+        "INSERT INTO schedule_rules (rule_type, interval, weekday_set, "
+        "day_of_month, nth_weekday, month, start_date, end_date) "
+        "VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
+        (
+            storage.rule_type,
+            storage.interval,
+            storage.weekday_set,
+            storage.day_of_month,
+            storage.nth_weekday,
+            storage.month,
+            storage.start_date,
+            storage.end_date,
+        ),
+    )
+    return result.lastrowid
+
+
 class ScheduleRulesDao:
     """Typed async access to the ``schedule_rules`` table."""
 
@@ -430,22 +501,20 @@ class ScheduleRulesDao:
         _validate_date(start_date, "start_date")
         if end_date is not None:
             _validate_date(end_date, "end_date")
-        result = await self._database.execute(
+        rule_id = await _insert_rule_row(
-            "INSERT INTO schedule_rules (rule_type, interval, weekday_set, "
+            self._database,
-            "day_of_month, nth_weekday, month, start_date, end_date) "
+            ScheduleRuleStorage(
-            "VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
+                rule_type=rule_type,
-            (
+                interval=interval,
-                rule_type,
+                weekday_set=weekday_set,
-                interval,
+                day_of_month=day_of_month,
-                weekday_set,
+                nth_weekday=nth_weekday,
-                day_of_month,
+                month=month,
-                nth_weekday,
+                start_date=start_date,
-                month,
+                end_date=end_date,
-                start_date,
-                end_date,
             ),
         )
-        rule = await self.get(result.lastrowid)
+        rule = await self.get(rule_id)
         assert rule is not None
         return rule
 
@@ -605,6 +674,191 @@ class QuestDefinitionsDao:
         assert definition is not None
         return definition
 
+    async def create_with_rule_and_windows(
+        self,
+        title: str,
+        schedule_rule: ScheduleRuleStorage,
+        created_at: str,
+        assignee_child_ids: list[int],
+        windows: list[tuple[str, str | None]],
+        *,
+        description: str | None = None,
+        icon: str | None = None,
+    ) -> QuestDefinitionSnapshot:
+        """Insert a rule, definition, assignees and windows atomically.
+
+        The whole write — the schedule rule row, the definition row,
+        every assignee link and every window declaration — runs inside
+        ONE transaction under the connection lock, so a validation or
+        write failure rolls the lot back together (all-or-nothing): the
+        caller can never observe a definition with a missing rule,
+        assignee or window, and a rejected create leaves no partial
+        rows.
+
+        Returns a :class:`QuestDefinitionSnapshot` whose assignees and
+        windows are read back inside the same transaction, so the
+        returned view cannot be raced by a concurrent assignee/window
+        mutation after the write.
+
+        The rule arrives as pre-validated storage fields (mapped from a
+        :class:`~.recurrence.ScheduleRule` via
+        :func:`schedule_rule_to_storage`); its dates are re-validated
+        here so a hand-built storage can never persist a malformed date.
+        The definition-level ``due_time`` column is deliberately left
+        NULL — per-window due times supersede it (D-008).  Assignees are
+        validated as existing-and-active at insert time, and windows are
+        validated for name and strict HH:MM due time, matching
+        :meth:`upsert_window`.
+        """
+        _validate_date(schedule_rule.start_date, "start_date")
+        if schedule_rule.end_date is not None:
+            _validate_date(schedule_rule.end_date, "end_date")
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                for child_id in assignee_child_ids:
+                    await self._validate_assignable_child(child_id)
+                rule_id = await _insert_rule_row(self._database, schedule_rule)
+                result = await self._database.execute(
+                    "INSERT INTO quest_definitions (title, description, "
+                    "icon, schedule_rule_id, due_time, is_active, "
+                    "created_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
+                    (title, description, icon, rule_id, None, 1, created_at),
+                )
+                definition_id = result.lastrowid
+                for child_id in assignee_child_ids:
+                    await self._database.execute(
+                        "INSERT INTO quest_definition_assignees "
+                        "(definition_id, child_id) VALUES (?, ?)",
+                        (definition_id, child_id),
+                    )
+                for window, due_time in windows:
+                    _validate_window(window)
+                    if due_time is not None:
+                        _validate_time(due_time, "due_time")
+                    await self._database.execute(
+                        "INSERT INTO quest_definition_windows "
+                        "(definition_id, window, due_time) VALUES (?, ?, ?)",
+                        (definition_id, window, due_time),
+                    )
+                definition = await self.get(definition_id)
+                assignees = await self.list_assignees(definition_id)
+                window_records = await self.list_windows(definition_id)
+                rule_record = await ScheduleRulesDao(self._database).get(
+                    rule_id
+                )
+        assert definition is not None
+        assert rule_record is not None
+        return QuestDefinitionSnapshot(
+            definition=definition,
+            rule=rule_record,
+            assignees=assignees,
+            windows=window_records,
+        )
+
+    async def edit_definition(
+        self,
+        definition_id: int,
+        *,
+        title: str | None | object = _UNSET,
+        description: str | None | object = _UNSET,
+        icon: str | None | object = _UNSET,
+        rule: ScheduleRuleStorage | object = _UNSET,
+        windows: list[tuple[str, str | None]] | object = _UNSET,
+    ) -> QuestDefinitionSnapshot:
+        """Edit a definition's metadata, rule and windows atomically.
+
+        Mirrors :meth:`create_with_rule_and_windows`: everything runs
+        inside ONE transaction under the connection lock, and the
+        returned snapshot's rule, assignees and windows are read back
+        inside that transaction.  Arguments default to the module sentinel
+        ``_UNSET`` meaning "leave this field alone"; ``None`` writes SQL
+        NULL so optional metadata (description, icon) can be cleared.
+
+        - ``title``/``description``/``icon`` update the definition row
+          in place; the definition-level ``due_time`` column is never
+          written (per-window due times supersede it, D-008).
+        - ``rule`` (a pre-validated :class:`ScheduleRuleStorage`)
+          overwrites the referenced ``schedule_rules`` row in place: the
+          definition keeps its ``schedule_rule_id``, so no new or
+          orphaned rule row appears.
+        - ``windows`` REPLACES the whole window set: the current rows
+          are deleted and the given set re-inserted, so missing windows
+          are added, changed due times updated and removed windows
+          dropped.
+
+        Assignment is deliberately not settable here (multi-assignee is
+        managed by :meth:`add_assignee`/:meth:`remove_assignee`), and
+        activation is untouched.  Raises ValueError when the definition
+        does not exist.  Edits change future instances only: no
+        ``quest_instances`` or ``completion_events`` row is touched.
+        """
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                definition = await self.get(definition_id)
+                if definition is None:
+                    raise ValueError(
+                        f"quest definition {definition_id} does not exist"
+                    )
+                assignments: list[str] = []
+                parameters: list[object] = []
+                for column, value in (
+                    ("title", title),
+                    ("description", description),
+                    ("icon", icon),
+                ):
+                    if value is not _UNSET:
+                        assignments.append(f"{column} = ?")
+                        parameters.append(value)
+                if assignments:
+                    parameters.append(definition_id)
+                    await self._database.execute(
+                        f"UPDATE quest_definitions SET "
+                        f"{', '.join(assignments)} WHERE id = ?",
+                        tuple(parameters),
+                    )
+                if rule is not _UNSET:
+                    await ScheduleRulesDao(self._database).update(
+                        definition.schedule_rule_id,
+                        rule_type=rule.rule_type,
+                        interval=rule.interval,
+                        weekday_set=rule.weekday_set,
+                        day_of_month=rule.day_of_month,
+                        nth_weekday=rule.nth_weekday,
+                        month=rule.month,
+                        start_date=rule.start_date,
+                        end_date=rule.end_date,
+                    )
+                if windows is not _UNSET:
+                    await self._database.execute(
+                        "DELETE FROM quest_definition_windows "
+                        "WHERE definition_id = ?",
+                        (definition_id,),
+                    )
+                    for window, due_time in windows:
+                        _validate_window(window)
+                        if due_time is not None:
+                            _validate_time(due_time, "due_time")
+                        await self._database.execute(
+                            "INSERT INTO quest_definition_windows "
+                            "(definition_id, window, due_time) "
+                            "VALUES (?, ?, ?)",
+                            (definition_id, window, due_time),
+                        )
+                updated = await self.get(definition_id)
+                assignees = await self.list_assignees(definition_id)
+                window_records = await self.list_windows(definition_id)
+                rule_record = await ScheduleRulesDao(self._database).get(
+                    updated.schedule_rule_id
+                )
+        assert updated is not None
+        assert rule_record is not None
+        return QuestDefinitionSnapshot(
+            definition=updated,
+            rule=rule_record,
+            assignees=assignees,
+            windows=window_records,
+        )
+
     async def _validate_rule_exists(self, schedule_rule_id: int) -> None:
         """Raise ValueError unless the rule exists.
 
@@ -653,6 +907,81 @@ class QuestDefinitionsDao:
         )
         return [_definition_from_row(row) for row in rows]
 
+    async def _snapshots_for(
+        self, definitions: list[QuestDefinitionRecord]
+    ) -> list[QuestDefinitionSnapshot]:
+        """Read each definition's rule, assignees and windows.
+
+        MUST be called inside the connection lock (and ideally a
+        transaction) so the definitions handed in and the rows this
+        reads back cannot be raced by a concurrent edit between list
+        and bundle.  Each rule is read through
+        :class:`ScheduleRulesDao` on the same connection, so the
+        snapshot's rule record is the one the definition references AT
+        the moment of the locked read.
+        """
+        rules_dao = ScheduleRulesDao(self._database)
+        snapshots: list[QuestDefinitionSnapshot] = []
+        for definition in definitions:
+            rule_record = await rules_dao.get(definition.schedule_rule_id)
+            assert rule_record is not None
+            snapshots.append(
+                QuestDefinitionSnapshot(
+                    definition=definition,
+                    rule=rule_record,
+                    assignees=await self.list_assignees(definition.id),
+                    windows=await self.list_windows(definition.id),
+                )
+            )
+        return snapshots
+
+    async def list_snapshots_active(self) -> list[QuestDefinitionSnapshot]:
+        """Return every active definition with its rule, assignees and
+        windows, all read inside one locked transaction.
+
+        The definitive list and each definition's linked rows are read
+        in a single BEGIN..COMMIT span under the connection lock, so a
+        concurrent atomic edit cannot interleave between the definition
+        list and the bundle read: the caller receives a coherent
+        snapshot (each definition's rule, assignees and windows as they
+        were at one instant), never a mixed old-rule/new-windows state.
+        """
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                definitions = await self.list_active()
+                return await self._snapshots_for(definitions)
+
+    async def read_snapshots_active(self) -> list[QuestDefinitionSnapshot]:
+        """Return every active definition's snapshot WITHOUT locking.
+
+        Mirrors :meth:`list_snapshots_active` minus the lock and
+        transaction: the definitions and their rules, assignees and
+        windows are read on the caller's connection under whatever
+        transaction the caller is already holding, so they stay coherent
+        with every OTHER table the caller reads inside that same
+        transaction.  MUST be called inside the connection lock and an
+        open transaction (the single-transaction input snapshot the
+        materialization walk uses is that caller).
+        """
+        definitions = await self.list_active()
+        return await self._snapshots_for(definitions)
+
+    async def list_snapshots_by_child(
+        self, child_id: int
+    ) -> list[QuestDefinitionSnapshot]:
+        """Return each definition assigned to ``child_id`` with its rule,
+        assignees and windows, all read inside one locked transaction.
+
+        Mirrors :meth:`list_snapshots_active` but selects by assignment
+        (``list_by_child`` order: newest first).  Locks read-and-bundle
+        into one transaction so the bundle never mixes one edit's
+        definition with another's windows.
+        """
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                definitions = await self.list_by_child(child_id)
+                return await self._snapshots_for(definitions)
+
     async def update(
         self,
         definition_id: int,
@@ -708,14 +1037,18 @@ class QuestDefinitionsDao:
 
     async def add_assignee(
         self, definition_id: int, child_id: int
-    ) -> None:
+    ) -> list[ChildRecord]:
         """Assign ``child_id`` to the definition (idempotent).
 
         Validates the assignee inside the same serialized transaction
         as the INSERT (existing and active AT ASSIGNMENT TIME), so a
         child deactivated concurrently cannot become an assignee.
         Assigning an already-assigned child is a no-op, mirroring the
-        composite primary key's storage-level guarantee.  Changes
+        composite primary key's storage-level guarantee.  Returns the
+        definition's assignees read back INSIDE the same transaction
+        that performed the write, so the returned roster is a
+        consistent post-assignment snapshot — a concurrent assign or
+        unassign cannot race it after the write commits.  Changes
         future instances only: already-generated instances and
         completion history are never rewritten here.
         """
@@ -736,6 +1069,7 @@ class QuestDefinitionsDao:
                     "ON CONFLICT (definition_id, child_id) DO NOTHING",
                     (definition_id, child_id),
                 )
+                return await self.list_assignees(definition_id)
 
     async def remove_assignee(
         self, definition_id: int, child_id: int
@@ -871,3 +1205,46 @@ class QuestDefinitionsDao:
             raise ValueError(
                 f"child {child_id} is inactive and cannot be assigned"
             )
+
+
+async def load_materialization_input(
+    database: NestQuestDatabase,
+    range_start: str,
+    range_end: str,
+):
+    """Read every materialization input in ONE locked transaction.
+
+    Returns ``(snapshots, schedules, overrides)``:
+
+    - ``snapshots``: every active definition with its rule, assignees and
+      windows (the :class:`QuestDefinitionSnapshot` shape).
+    - ``schedules`` / ``overrides``: the presence schedules and
+      range-scoped overrides of every assignee child, in
+      :mod:`.dao_presence`'s record shapes.
+
+    The whole read — definitions, rules, assignees, windows, and the
+    assignees' presence schedules and overrides — runs inside a single
+    BEGIN..COMMIT span under the connection lock, so a concurrent edit
+    (definition, assignment, window, or presence) cannot interleave
+    between the parts and the walk never sees a mixed-time view.  Each
+    table's SQL stays in its own DAO module: the presence portion is
+    delegated to :mod:`.dao_presence` (a lazy import here, since that
+    module imports this one).
+    """
+    definitions_dao = QuestDefinitionsDao(database)
+    async with _connection_lock(database):
+        async with database.transaction():
+            snapshots = await definitions_dao.read_snapshots_active()
+            child_ids = sorted(
+                {
+                    child.id
+                    for snapshot in snapshots
+                    for child in snapshot.assignees
+                }
+            )
+            from . import dao_presence  # lazy: dao_presence imports us
+
+            schedules, overrides = await dao_presence.read_snapshot_unlocked(
+                database, child_ids, range_start, range_end
+            )
+    return snapshots, schedules, overrides

custom_components/nestquest/materialize.py

@@ -0,0 +1,167 @@
+"""The materialization walk (Feature 07 core).
+
+:func:`materialize` turns the schedule rules + custody presence of the
+active quest definitions into concrete ``quest_instances`` rows over a
+date range.  It is the pure walk over definitions x dates x assignees x
+windows; HA scheduling (day-rollover triggers) and skip-completed
+generation are separate later tasks and live elsewhere.
+
+The walk is deterministic and idempotent:
+
+- All inputs — the active definitions with their rules, assignees and
+  windows, AND the assignee children's presence schedules and scoped
+  overrides — are read inside ONE locked transaction (see
+  :func:`~.dao_rules.load_materialization_input`), so the walk sees a
+  single coherent snapshot rather than a definition state from one
+  instant and a presence state from another.
+- Presence is snapshotted into an immutable
+  :class:`~.presence.PresenceEngine` from that same coherent read.
+- For each date in the range, each definition whose decoded rule
+  :func:`~.recurrence.occurs_on` on that date, each assignee who is
+  ``is_present`` on that date and each declared window, one instance is
+  written through :class:`~.dao_instances.QuestInstancesDao.upsert_if_valid`
+  with a ``generated_at`` stamp shared by the whole batch (one coherent
+  time per run).
+
+A config change that lands AFTER the snapshot is read but BEFORE a
+tuple's write (an assignment removed, a definition or child deactivated,
+a window removed) makes that tuple SKIP rather than abort the batch:
+:meth:`~.dao_instances.QuestInstancesDao.upsert_if_valid` re-validates
+every materialization precondition (active definition, active child,
+live assignment, declared window, and its CURRENT ``due_time``) inside
+the SAME transaction as the INSERT, so the check and the write cannot be
+split by a racing config edit.
+
+The upsert is idempotent on (definition_id, child_id, due_date, window),
+so re-running the materialization over the same range never duplicates a
+row; skipped-completed handling is deliberately NOT done here.
+"""
+from __future__ import annotations
+
+import datetime
+
+from .dao_instances import QuestInstancesDao
+from .dao_rules import (
+    _validate_date,
+    load_materialization_input,
+    schedule_rule_from_storage,
+    schedule_rule_storage_from_record,
+)
+from .db import NestQuestDatabase
+from .presence import PresenceEngine, PresenceOverride, PresenceSchedule
+from .recurrence import ScheduleRule, occurs_on
+
+
+def _now_stamp() -> str:
+    """Return the batch's shared UTC second-precision generation stamp."""
+    return datetime.datetime.now(datetime.timezone.utc).isoformat(
+        timespec="seconds"
+    )
+
+
+def _decode_rule(snapshot) -> ScheduleRule:
+    """Decode a snapshot's rule record back into a ScheduleRule.
+
+    The record carried by the snapshot is the one the definition
+    referenced at read time, so decoding it here (rather than re-reading
+    through ``ScheduleRulesDao``) preserves the coherence the snapshot
+    guarantees.
+    """
+    return schedule_rule_from_storage(
+        schedule_rule_storage_from_record(snapshot.rule)
+    )
+
+
+def _build_engine(
+    schedules_records,
+    overrides_records,
+) -> PresenceEngine:
+    """Convert raw presence records into an immutable PresenceEngine."""
+    schedules: dict[int, PresenceSchedule] = {}
+    for child_id, record in schedules_records.items():
+        schedules[child_id] = PresenceSchedule.decode(
+            child_id, record.anchor_date, record.pattern
+        )
+    overrides: dict[int, list[PresenceOverride]] = {}
+    for child_id, records in overrides_records.items():
+        overrides[child_id] = [
+            PresenceOverride(
+                record.child_id,
+                record.start_date,
+                record.end_date,
+                record.is_present,
+                record.note,
+            )
+            for record in records
+        ]
+    return PresenceEngine(schedules, overrides)
+
+
+async def materialize(
+    database: NestQuestDatabase,
+    start_date: str,
+    end_date: str,
+) -> int:
+    """Generate quest instances for the closed range [start_date, end_date].
+
+    ``start_date`` and ``end_date`` must be strict ISO calendar dates
+    (YYYY-MM-DD) with ``end_date`` on or after ``start_date``; anything
+    else raises ValueError naming the offending field.  Returns the number
+    of instances upserted for the range (idempotent — a re-run refreshes
+    the same rows, never duplicates them).
+
+    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
+    transaction as the INSERT, so a config change that arrives mid-batch
+    skips the affected tuples rather than aborting the batch or leaving a
+    partial result.
+    """
+    _validate_date(start_date, "start_date")
+    _validate_date(end_date, "end_date")
+    if end_date < start_date:
+        raise ValueError(
+            f"end_date must be on or after start_date, got "
+            f"{end_date!r} < {start_date!r}"
+        )
+
+    snapshots, schedules_records, overrides_records = await (
+        load_materialization_input(database, start_date, end_date)
+    )
+    engine = _build_engine(schedules_records, overrides_records)
+
+    definitions = [
+        (snapshot.definition.id, _decode_rule(snapshot),
+         snapshot.assignees, snapshot.windows)
+        for snapshot in snapshots
+    ]
+
+    generated_at = _now_stamp()
+    instances = QuestInstancesDao(database)
+
+    start = datetime.datetime.strptime(start_date, "%Y-%m-%d").date()
+    end = datetime.datetime.strptime(end_date, "%Y-%m-%d").date()
+
+    count = 0
+    cursor = start
+    while cursor <= end:
+        iso = cursor.isoformat()
+        for definition_id, rule, assignees, windows in definitions:
+            if not occurs_on(rule, cursor):
+                continue
+            for child in assignees:
+                if not engine.is_present(child.id, cursor):
+                    continue
+                for window in windows:
+                    written = await instances.upsert_if_valid(
+                        definition_id,
+                        child.id,
+                        iso,
+                        generated_at,
+                        window=window.window,
+                        due_time=window.due_time,
+                    )
+                    if written is not None:
+                        count += 1
+        cursor += datetime.timedelta(days=1)
+    return count

custom_components/nestquest/quest_definitions.py

@@ -0,0 +1,551 @@
+"""Business layer for creating and editing quest definitions (Feature 06).
+
+Sits between the Feature 09 service gate and the typed
+:class:`~.dao_rules.QuestDefinitionsDao`: callers get validation and the
+all-or-nothing create/edit here, and never touch the DAO SQL directly.
+:func:`create_quest_definition` is the create path,
+:func:`edit_quest_definition` the edit path, :func:`assign_child` and
+:func:`unassign_child` the assignment paths, and
+:func:`set_quest_definition_active` the deactivate/reactivate path.
+There is no permission check here (that is Feature 09).
+
+Validation policy mirrors :mod:`.children`:
+
+- ``title`` is required and trimmed; empty or whitespace-only raises
+  ValueError.
+- ``rule`` must be an already-validated :class:`~.recurrence.ScheduleRule`
+  (its constructor is total — rule validation is NOT re-implemented
+  here).
+- ``assignee_child_ids`` must be a non-empty list (strictly a ``list``);
+  every id is a plain int (bools and floats rejected, since SQLite would
+  bind them onto a real child) and must reference an existing ACTIVE
+  child (the DAO enforces the active/exists check inside the same
+  transaction).  Duplicate ids are rejected here, before the composite
+  primary key could surface them as an :class:`sqlite3.IntegrityError`.
+- ``windows`` must be a non-empty list (strictly a ``list``); each entry
+  is a window name from :data:`~.const.QUEST_WINDOWS` — either a bare
+  name (no due time) or a ``(name, due_time)`` pair whose due time is a
+  strict 24-hour HH:MM string.  Duplicate window names are rejected
+  here, before the composite primary key could surface them as an
+  :class:`sqlite3.IntegrityError`.
+
+The rule, definition, assignees and windows are persisted in ONE
+transaction (the DAO's :meth:`~.dao_rules.QuestDefinitionsDao.create_with_rule_and_windows`),
+so a rejected or failed create persists nothing.  The definition-level
+``due_time`` column is superseded by per-window due times (D-008) and is
+never set here.  ``created_at`` is stamped UTC ISO-8601 at second
+precision, matching :mod:`.children`.
+"""
+from __future__ import annotations
+
+import datetime
+from dataclasses import dataclass
+
+from .dao_children import ChildRecord, _connection_lock
+from .dao_rules import (
+    QuestDefinitionRecord,
+    QuestDefinitionSnapshot,
+    QuestDefinitionWindowRecord,
+    QuestDefinitionsDao,
+    ScheduleRuleStorage,
+    ScheduleRulesDao,
+    _UNSET,
+    _validate_date,
+    _validate_time,
+    _validate_window,
+    schedule_rule_from_storage,
+    schedule_rule_storage_from_record,
+    schedule_rule_to_storage,
+)
+from .db import NestQuestDatabase
+from .recurrence import ScheduleRule, occurs_on
+
+
+@dataclass(frozen=True)
+class CreatedQuestDefinition:
+    """The persisted result of a create, edit or activation change.
+
+    ``definition`` is the stored row; ``rule`` is the schedule rule
+    decoded back through the storage mapping; ``assignees`` and
+    ``windows`` are the definition's linked child records and window
+    declarations, read through the DAO.
+    """
+
+    definition: QuestDefinitionRecord
+    rule: ScheduleRule
+    assignees: list[ChildRecord]
+    windows: list[QuestDefinitionWindowRecord]
+
+
+def _now_stamp() -> str:
+    """Return the module's strict UTC second-precision stamp."""
+    return datetime.datetime.now(datetime.timezone.utc).isoformat(
+        timespec="seconds"
+    )
+
+
+def _validate_text(
+    value: object, field: str, *, required: bool = False
+) -> str | None:
+    """Validate a free-text field and return it trimmed.
+
+    ``None`` raises when ``required`` and otherwise returns None.  A
+    non-string value is rejected; an empty result is rejected for
+    required fields.
+    """
+    if value is None:
+        if required:
+            raise ValueError(f"{field} is required")
+        return None
+    if not isinstance(value, str):
+        raise ValueError(f"{field} must be a string, got {value!r}")
+    trimmed = value.strip()
+    if required and not trimmed:
+        raise ValueError(f"{field} is required")
+    return trimmed
+
+
+def _validate_assignee_ids(value: object) -> list[int]:
+    """Return the assignee ids as plain ints, or raise naming the field.
+
+    The argument must be a non-empty list — strictly a ``list``: tuples
+    and other sequences are rejected.  Each entry must be a real int
+    (bools and floats rejected) so SQLite cannot bind ``True`` onto
+    child 1 or a float onto a neighbouring profile.
+    """
+    if type(value) is not list:
+        raise ValueError(
+            f"assignee_child_ids must be a list, got {type(value).__name__}"
+        )
+    if not value:
+        raise ValueError("assignee_child_ids must not be empty")
+    ids: list[int] = []
+    for entry in value:
+        if isinstance(entry, bool) or not isinstance(entry, int):
+            raise ValueError(
+                "assignee_child_ids must contain integer child ids, "
+                f"got {entry!r}"
+            )
+        ids.append(entry)
+    if len(set(ids)) != len(ids):
+        raise ValueError(
+            "assignee_child_ids must not contain duplicate child ids"
+        )
+    return ids
+
+
+def _normalize_windows(
+    value: object,
+) -> list[tuple[str, str | None]]:
+    """Normalize the windows argument to ``(name, due_time)`` pairs.
+
+    The outer argument must be a non-empty list — strictly a ``list``:
+    tuples and other sequences are rejected, while each individual entry
+    may still be a ``(window, due_time)`` tuple.  A bare window name
+    (due time None) is also accepted.  The name must be a
+    :data:`~.const.QUEST_WINDOWS` spelling and a present due time must be
+    a strict 24-hour HH:MM, both validated up front so a malformed
+    window is rejected before any write.
+    """
+    if type(value) is not list:
+        raise ValueError(
+            f"windows must be a list, got {type(value).__name__}"
+        )
+    if not value:
+        raise ValueError("windows must not be empty")
+    normalized: list[tuple[str, str | None]] = []
+    seen: set[str] = set()
+    for index, entry in enumerate(value):
+        if isinstance(entry, str):
+            name: object = entry
+            due_time: object = None
+        elif isinstance(entry, (tuple, list)) and len(entry) == 2:
+            name, due_time = entry[0], entry[1]
+        else:
+            raise ValueError(
+                f"windows[{index}] must be a window name or a "
+                f"(name, due_time) pair, got {entry!r}"
+            )
+        _validate_window(name)
+        if due_time is not None:
+            _validate_time(due_time, "due_time")
+        if name in seen:
+            raise ValueError(
+                f"windows must not contain duplicate window {name!r}"
+            )
+        seen.add(name)
+        normalized.append((name, due_time))
+    return normalized
+
+
+def _validate_definition_id(value: object) -> int:
+    """Reject non-int definition ids (bools included) before any lookup.
+
+    SQLite binds Python bools as integers, so ``True`` would silently
+    address definition 1 and a float would round — malformed service
+    input must never mutate the wrong definition.
+    """
+    if isinstance(value, bool) or not isinstance(value, int):
+        raise ValueError(f"definition_id must be an integer, got {value!r}")
+    return value
+
+
+def _validate_child_id(value: object) -> int:
+    """Reject non-int child ids (bools included) before any lookup.
+
+    SQLite binds Python bools as integers, so ``True`` would silently
+    address child 1 and a float would round — malformed service input
+    must never mutate the wrong profile.
+    """
+    if isinstance(value, bool) or not isinstance(value, int):
+        raise ValueError(f"child_id must be an integer, got {value!r}")
+    return value
+
+
+async def create_quest_definition(
+    database: NestQuestDatabase,
+    title: str,
+    rule: ScheduleRule,
+    assignee_child_ids: list[int],
+    windows: list[str | tuple[str, str | None]],
+    *,
+    description: str | None = None,
+    icon: str | None = None,
+) -> CreatedQuestDefinition:
+    """Create a quest definition (rule + assignees + windows) atomically.
+
+    Validates the arguments up front, then persists the schedule rule,
+    the definition, its assignees and its windows in one transaction via
+    the DAO.  Returns the stored definition together with its decoded
+    rule, assignees and windows — all read back inside that same
+    transaction, so the returned bundle is a consistent creation
+    snapshot.  Raises ValueError on any rejected argument; a rejected or
+    failed create persists nothing.
+    """
+    name = _validate_text(title, "title", required=True)
+    assert name is not None
+    if not isinstance(rule, ScheduleRule):
+        raise ValueError(f"rule must be a ScheduleRule, got {rule!r}")
+    assignee_ids = _validate_assignee_ids(assignee_child_ids)
+    window_specs = _normalize_windows(windows)
+    description_value = _validate_text(description, "description")
+    icon_value = _validate_text(icon, "icon")
+
+    storage = schedule_rule_to_storage(rule)
+    dao = QuestDefinitionsDao(database)
+    try:
+        snapshot = await dao.create_with_rule_and_windows(
+            name,
+            storage,
+            _now_stamp(),
+            assignee_ids,
+            window_specs,
+            description=description_value,
+            icon=icon_value,
+        )
+    except ValueError as error:
+        # The DAO's assignee check names only the child; re-raise so the
+        # public contract names the field the caller actually passed.
+        message = str(error)
+        if message.startswith("child "):
+            raise ValueError(
+                f"assignee_child_ids: {message}"
+            ) from error
+        raise
+    decoded_rule = schedule_rule_from_storage(storage)
+    return CreatedQuestDefinition(
+        definition=snapshot.definition,
+        rule=decoded_rule,
+        assignees=snapshot.assignees,
+        windows=snapshot.windows,
+    )
+
+
+async def edit_quest_definition(
+    database: NestQuestDatabase,
+    definition_id: int,
+    *,
+    title: str | object = _UNSET,
+    description: str | None | object = _UNSET,
+    icon: str | None | object = _UNSET,
+    rule: ScheduleRule | object = _UNSET,
+    windows: list[str | tuple[str, str | None]] | object = _UNSET,
+) -> CreatedQuestDefinition:
+    """Edit a quest definition's metadata, rule and windows atomically.
+
+    Arguments default to the sentinel ``_UNSET`` meaning "leave this
+    field alone".  A provided ``title`` is validated like create's
+    (required and trimmed); ``description`` and ``icon`` are optional
+    and may be cleared by passing ``None`` explicitly.  A provided
+    ``rule`` must be an already-validated :class:`~.recurrence.ScheduleRule`
+    and REPLACES the definition's schedule rule IN PLACE — the existing
+    ``schedule_rules`` row is updated, never orphaned or duplicated.  A
+    provided ``windows`` list REPLACES the whole window set (each entry
+    a ``const.QUEST_WINDOWS`` name, optionally with a strict HH:MM due
+    time), matching create's non-empty contract.  Assignment is NOT
+    settable here.  Raises ValueError when the definition does not
+    exist or any argument is rejected.
+
+    Returns the updated definition together with its decoded rule,
+    assignees and windows — all read back inside the DAO's transaction,
+    so the returned bundle is a consistent edit snapshot.  Edits change
+    future instances only; no ``quest_instances`` or
+    ``completion_events`` row is touched.
+    """
+    _validate_definition_id(definition_id)
+
+    title_value: str | None | object = _UNSET
+    if title is not _UNSET:
+        name = _validate_text(title, "title", required=True)
+        assert name is not None
+        title_value = name
+
+    description_value: str | None | object = _UNSET
+    if description is not _UNSET:
+        description_value = _validate_text(description, "description")
+
+    icon_value: str | None | object = _UNSET
+    if icon is not _UNSET:
+        icon_value = _validate_text(icon, "icon")
+
+    rule_storage: ScheduleRuleStorage | object = _UNSET
+    if rule is not _UNSET:
+        if not isinstance(rule, ScheduleRule):
+            raise ValueError(f"rule must be a ScheduleRule, got {rule!r}")
+        rule_storage = schedule_rule_to_storage(rule)
+
+    window_specs: list[tuple[str, str | None]] | object = _UNSET
+    if windows is not _UNSET:
+        window_specs = _normalize_windows(windows)
+
+    dao = QuestDefinitionsDao(database)
+    try:
+        snapshot = await dao.edit_definition(
+            definition_id,
+            title=title_value,
+            description=description_value,
+            icon=icon_value,
+            rule=rule_storage,
+            windows=window_specs,
+        )
+    except ValueError as error:
+        # The DAO's missing-definition error names only the id; re-raise
+        # so the public contract names the field the caller passed (matching
+        # assign/unassign/deactivate).
+        message = str(error)
+        if message.startswith("quest definition "):
+            raise ValueError(f"definition_id: {message}") from error
+        raise
+
+    decoded_rule = schedule_rule_from_storage(
+        schedule_rule_storage_from_record(snapshot.rule)
+    )
+
+    return CreatedQuestDefinition(
+        definition=snapshot.definition,
+        rule=decoded_rule,
+        assignees=snapshot.assignees,
+        windows=snapshot.windows,
+    )
+
+
+async def assign_child(
+    database: NestQuestDatabase,
+    definition_id: int,
+    child_id: int,
+) -> list[ChildRecord]:
+    """Assign ``child_id`` to the definition (idempotent).
+
+    Validates that both ids are plain ints (bools and floats rejected)
+    and that the child exists and is active — the DAO enforces the
+    active/exists check inside its transaction, and also confirms the
+    definition exists.  Assigning an already-assigned child is a no-op
+    (no duplicate row).  Returns the definition's assignees read back
+    inside the DAO's same transaction, a consistent post-assignment
+    roster (a concurrent assign/unassign cannot race it after the
+    write).  Raises ValueError on any rejected argument.  Only the
+    ``quest_definition_assignees`` link is written; existing instances
+    and completion history are never touched.
+    """
+    _validate_definition_id(definition_id)
+    _validate_child_id(child_id)
+    dao = QuestDefinitionsDao(database)
+    try:
+        return await dao.add_assignee(definition_id, child_id)
+    except ValueError as error:
+        # The DAO names only the child/definition; re-raise so the
+        # public contract names the field the caller actually passed.
+        message = str(error)
+        if message.startswith("child "):
+            raise ValueError(f"child_id: {message}") from error
+        if message.startswith("quest definition "):
+            raise ValueError(f"definition_id: {message}") from error
+        raise
+
+
+async def unassign_child(
+    database: NestQuestDatabase,
+    definition_id: int,
+    child_id: int,
+) -> bool:
+    """Unassign ``child_id`` from the definition; True when removed.
+
+    Validates both ids are plain ints (bools and floats rejected) and
+    that the definition exists, then removes the (definition, child)
+    link.  Returns True when a row was removed and False when the 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.
+    """
+    _validate_definition_id(definition_id)
+    _validate_child_id(child_id)
+    dao = QuestDefinitionsDao(database)
+    definition = await dao.get(definition_id)
+    if definition is None:
+        raise ValueError(
+            f"definition_id: quest definition {definition_id} does not exist"
+        )
+    return await dao.remove_assignee(definition_id, child_id)
+
+
+async def set_quest_definition_active(
+    database: NestQuestDatabase,
+    definition_id: int,
+    is_active: bool,
+) -> CreatedQuestDefinition:
+    """Deactivate or reactivate a definition; returns the updated view.
+
+    Flipping the flag is the only removal path (Feature 06 guardrail):
+    a deactivated definition stops future instance generation but its
+    row, schedule rule, assignees and windows all survive, and existing
+    instances plus completion history are never touched.  Reactivation
+    re-enables future generation; generating or removing instances is
+    Feature 07 and does not happen here.
+
+    ``definition_id`` must be a plain int (bools and floats rejected,
+    since SQLite would bind ``True`` onto definition 1) and
+    ``is_active`` must be a real bool — the DAO's ``int()`` would
+    otherwise silently coerce strings and numerics ("1", 0) into a
+    state the caller never asked for.  Raises ValueError naming the
+    offending field when the id is malformed, ``is_active`` is not a
+    bool, or the definition does not exist.
+
+    Atomicity: the existence check, the UPDATE and the readback of the
+    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.
+    """
+    _validate_definition_id(definition_id)
+    if not isinstance(is_active, bool):
+        raise ValueError(f"is_active must be a real bool, got {is_active!r}")
+    async with _connection_lock(database):
+        dao = QuestDefinitionsDao(database)
+        if await dao.get(definition_id) is None:
+            raise ValueError(
+                f"definition_id: quest definition {definition_id} "
+                "does not exist"
+            )
+        await dao.set_active(definition_id, is_active)
+        updated = await dao.get(definition_id)
+        assert updated is not None
+        rule_record = await ScheduleRulesDao(database).get(
+            updated.schedule_rule_id
+        )
+        assert rule_record is not None
+        assignees = await dao.list_assignees(definition_id)
+        windows = await dao.list_windows(definition_id)
+    rule = schedule_rule_from_storage(
+        schedule_rule_storage_from_record(rule_record)
+    )
+    return CreatedQuestDefinition(
+        definition=updated,
+        rule=rule,
+        assignees=assignees,
+        windows=windows,
+    )
+
+
+def _bundle_snapshot(
+    snapshot: QuestDefinitionSnapshot,
+) -> CreatedQuestDefinition:
+    """Decode a DAO snapshot into the layer's rich view.
+
+    The rule record carried by the snapshot is the one the definition
+    referenced at read time, so decoding it here (rather than re-reading
+    through :class:`~.dao_rules.ScheduleRulesDao`) preserves the
+    coherence the DAO's locked snapshot guarantees: the rule, assignees
+    and windows all come from one atomic read, never three separate ones.
+    """
+    rule = schedule_rule_from_storage(
+        schedule_rule_storage_from_record(snapshot.rule)
+    )
+    return CreatedQuestDefinition(
+        definition=snapshot.definition,
+        rule=rule,
+        assignees=snapshot.assignees,
+        windows=snapshot.windows,
+    )
+
+
+async def list_active_definitions(
+    database: NestQuestDatabase,
+) -> list[CreatedQuestDefinition]:
+    """Return every ACTIVE definition with its decoded rule and links.
+
+    Ordered by rising definition id, matching the DAO's ``list_active``
+    order, so callers get a stable, repeatable sequence.  Each entry is
+    the same rich :class:`CreatedQuestDefinition` snapshot as the rest of
+    the layer: the stored row plus its decoded
+    :class:`~.recurrence.ScheduleRule`, assignees and windows.  The
+    definitions and their links are read by the DAO inside one locked
+    transaction, so each bundle is a coherent snapshot that a concurrent
+    edit cannot interleave.
+    """
+    dao = QuestDefinitionsDao(database)
+    snapshots = await dao.list_snapshots_active()
+    return [_bundle_snapshot(snapshot) for snapshot in snapshots]
+
+
+async def list_definitions_for_child(
+    database: NestQuestDatabase,
+    child_id: int,
+) -> list[CreatedQuestDefinition]:
+    """Return the definitions assigned to ``child_id`` (rich view).
+
+    ``child_id`` must be a plain int (bools and floats rejected, since
+    SQLite would bind ``True`` onto child 1).  Results are ordered by
+    rising definition id, matching ``list_active_definitions`` and
+    ``list_definitions_firing_on``.  Each entry is a
+    :class:`CreatedQuestDefinition` snapshot bundling the definition with
+    its decoded rule, assignees and windows, read atomically by the DAO.
+    """
+    _validate_child_id(child_id)
+    dao = QuestDefinitionsDao(database)
+    snapshots = await dao.list_snapshots_by_child(child_id)
+    bundles = [_bundle_snapshot(snapshot) for snapshot in snapshots]
+    return sorted(bundles, key=lambda bundle: bundle.definition.id)
+
+
+async def list_definitions_firing_on(
+    database: NestQuestDatabase,
+    date: str,
+) -> list[CreatedQuestDefinition]:
+    """Return the ACTIVE definitions whose rule fires on ``date``.
+
+    ``date`` must be a strict ISO calendar date (YYYY-MM-DD); a malformed
+    value raises ValueError naming the field.  Each active definition's
+    stored rule is decoded through the task-1 storage mapping and handed
+    to the recurrence engine's :func:`~.recurrence.occurs_on`; only the
+    definitions whose decoded rule fires on the date are returned, in
+    rising definition-id order, each as the same rich
+    :class:`CreatedQuestDefinition` snapshot as the rest of the layer.
+    The definitions and their rules are read inside one locked
+    transaction, so a bundle can never filter on one rule while
+    presenting another state's windows.
+    """
+    _validate_date(date, "date")
+    target = datetime.datetime.strptime(date, "%Y-%m-%d").date()
+    dao = QuestDefinitionsDao(database)
+    snapshots = await dao.list_snapshots_active()
+    bundles = [_bundle_snapshot(snapshot) for snapshot in snapshots]
+    return [bundle for bundle in bundles if occurs_on(bundle.rule, target)]

tests/test_materialize.py

@@ -0,0 +1,582 @@
+"""Tests for materialize.py: the Feature 07 materialization walk."""
+from __future__ import annotations
+
+import asyncio
+import datetime
+
+import pytest
+
+from custom_components.nestquest.dao_children import ChildrenDao
+from custom_components.nestquest.dao_instances import QuestInstancesDao
+from custom_components.nestquest.dao_presence import PresenceSchedulesDao
+from custom_components.nestquest.dao_rules import QuestDefinitionsDao
+from custom_components.nestquest.db import NestQuestDatabase
+from custom_components.nestquest.materialize import materialize
+from custom_components.nestquest.migrations import apply_migrations
+from custom_components.nestquest.quest_definitions import (
+    create_quest_definition,
+)
+from custom_components.nestquest.recurrence import RuleType, ScheduleRule
+
+
+def _run(coro):
+    return asyncio.new_event_loop().run_until_complete(coro)
+
+
+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
+
+
+NOW = "2026-09-14T12:00:00+00:00"
+
+
+async def _prepare(path):
+    database = NestQuestDatabase(_make_hass_mock())
+    await database.open(path)
+    await apply_migrations(database)
+    return database
+
+
+def _with_db(tmp_path, name):
+    def _run_test(body):
+        async def _main():
+            database = await _prepare(tmp_path / name)
+            try:
+                return await body(database)
+            finally:
+                await database.close()
+
+        return _run(_main())
+
+    return _run_test
+
+
+def _future_monday() -> datetime.date:
+    """The Monday of next week (strictly future, all 28 days eligible)."""
+    today = datetime.date.today()
+    this_monday = today - datetime.timedelta(days=today.weekday())
+    return this_monday + datetime.timedelta(days=7)
+
+
+def _build_expected(start, def1_id, def2_id, a_id, b_id, c_id) -> set:
+    """Hand-derived expected (definition_id, child_id, window, due_date,
+    due_time) tuples over the four-week window.
+
+    Custody: A is present week 0 and 2 of the two-week cycle, absent
+    1 and 3; B is the mirror; C has no schedule (present every day).
+    """
+    expected: set = set()
+    start_dt = datetime.date.fromisoformat(start)
+    for offset in range(28):
+        day = start_dt + datetime.timedelta(days=offset)
+        iso = day.isoformat()
+        week = offset // 7
+        monday = day.weekday() == 0
+
+        # Def1: DAILY, assignees A + C, window morning due 09:00.
+        if week % 2 == 0:
+            expected.add((def1_id, a_id, "morning", iso, "09:00"))
+        expected.add((def1_id, c_id, "morning", iso, "09:00"))
+
+        # Def2: WEEKLY Monday, assignees B + C, morning 07:00 + evening 19:00.
+        if monday:
+            if week % 2 == 1:
+                expected.add((def2_id, b_id, "morning", iso, "07:00"))
+                expected.add((def2_id, b_id, "evening", iso, "19:00"))
+            expected.add((def2_id, c_id, "morning", iso, "07:00"))
+            expected.add((def2_id, c_id, "evening", iso, "19:00"))
+    return expected
+
+
+async def _collect_instances(database, child_ids, start, end) -> set:
+    dao = QuestInstancesDao(database)
+    collected = set()
+    for child_id in child_ids:
+        for record in await dao.list_by_date_range(child_id, start, end):
+            collected.add(
+                (
+                    record.definition_id,
+                    record.child_id,
+                    record.window,
+                    record.due_date,
+                    record.due_time,
+                )
+            )
+    return collected
+
+
+def test_materialize_two_custody_one_always_present(tmp_path) -> None:
+    async def _body(database):
+        children = ChildrenDao(database)
+        schedules = PresenceSchedulesDao(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
+
+        start = _future_monday()
+        start_iso = start.isoformat()
+        end_iso = (start + datetime.timedelta(days=27)).isoformat()
+
+        await schedules.upsert_by_child(
+            a.id, 2, start_iso, "0,1,2,3,4,5,6|"
+        )
+        await schedules.upsert_by_child(
+            b.id, 2, start_iso, "|0,1,2,3,4,5,6"
+        )
+
+        def1 = await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+            [a.id, c.id],
+            [("morning", "09:00")],
+        )
+        def2 = await create_quest_definition(
+            database,
+            "Weekly chore",
+            ScheduleRule(
+                rule_type=RuleType.WEEKLY,
+                weekday_set={0},
+                start_date=start_iso,
+            ),
+            [b.id, c.id],
+            [("morning", "07:00"), ("evening", "19:00")],
+        )
+
+        expected = _build_expected(
+            start_iso, def1.definition.id, def2.definition.id,
+            a.id, b.id, c.id,
+        )
+
+        count = await materialize(database, start_iso, end_iso)
+        assert count == len(expected)
+
+        actual = await _collect_instances(
+            database, (a.id, b.id, c.id), start_iso, end_iso
+        )
+        assert actual == expected
+        return count
+
+    _with_db(tmp_path, "materialize.db")(_body)
+
+
+def test_materialize_shares_one_generated_at_per_run(tmp_path) -> None:
+    async def _body(database):
+        children = ChildrenDao(database)
+        child = await children.create("Ada", NOW)
+        start = _future_monday()
+        start_iso = start.isoformat()
+        end_iso = (start + datetime.timedelta(days=6)).isoformat()
+        await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+            [child.id],
+            ["morning"],
+        )
+        await materialize(database, start_iso, end_iso)
+        dao = QuestInstancesDao(database)
+        stamps = {
+            record.generated_at
+            for record in await dao.list_by_date_range(
+                child.id, start_iso, end_iso
+            )
+        }
+        assert len(stamps) == 1
+        return stamps
+
+    _with_db(tmp_path, "materialize-stamp.db")(_body)
+
+
+def test_materialize_is_idempotent(tmp_path) -> None:
+    async def _body(database):
+        children = ChildrenDao(database)
+        child = await children.create("Ada", NOW)
+        start = _future_monday()
+        start_iso = start.isoformat()
+        end_iso = (start + datetime.timedelta(days=6)).isoformat()
+        await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+            [child.id],
+            ["morning"],
+        )
+        dao = QuestInstancesDao(database)
+        first = await materialize(database, start_iso, end_iso)
+        assert first == 7
+        first_ids = sorted(
+            r.id
+            for r in await dao.list_by_date_range(child.id, start_iso, end_iso)
+        )
+        assert len(first_ids) == 7
+        assert len(set(first_ids)) == 7
+
+        # Re-running over the same range refreshes the SAME physical rows:
+        # the ON CONFLICT path updates the snapshot columns in place and
+        # never delete + reinserts, so every primary-key id is stable.
+        for _ in range(2):
+            count = await materialize(database, start_iso, end_iso)
+            assert count == 7
+            run_ids = sorted(
+                r.id
+                for r in await dao.list_by_date_range(
+                    child.id, start_iso, end_iso
+                )
+            )
+            assert run_ids == first_ids
+
+        # And the widened key never duplicated: exactly one physical row
+        # per (definition_id, child_id, due_date, window).
+        records = await dao.list_by_date_range(child.id, start_iso, end_iso)
+        keys = [
+            (r.definition_id, r.child_id, r.due_date, r.window)
+            for r in records
+        ]
+        assert len(keys) == 7
+        assert len(set(keys)) == 7
+        return first
+
+    _with_db(tmp_path, "materialize-idempotent.db")(_body)
+
+
+async def _materialize_with_change(
+    database, start_iso, end_iso, change
+) -> int:
+    """Run materialize, pause before its first atomic write, apply
+    ``change``, then release.  Returns the materialize count.
+
+    The gate parks the walk AFTER the single input snapshot is read but
+    BEFORE the first :meth:`QuestInstancesDao.upsert_if_valid` call
+    acquires the connection lock, so ``change`` commits against the live
+    state and the write's own in-transaction precondition check then
+    skips the tuples it invalidated (or, for a presence-only change,
+    proceeds with the snapped presence).  This deterministically
+    exercises the snapshot-to-insert gap that a config edit can
+    interleave into.
+    """
+    original_upsert = QuestInstancesDao.upsert_if_valid
+    entered = asyncio.Event()
+    release = asyncio.Event()
+    seen = {"n": 0}
+
+    async def _gated(self, definition_id, child_id, due_date, generated_at,
+                     *, window, due_time=None):
+        seen["n"] += 1
+        if seen["n"] == 1:
+            seen["first"] = (definition_id, child_id, window)
+            entered.set()
+            await release.wait()
+        return await original_upsert(
+            self, definition_id, child_id, due_date, generated_at,
+            window=window, due_time=due_time,
+        )
+
+    QuestInstancesDao.upsert_if_valid = _gated
+    try:
+        task = asyncio.ensure_future(
+            materialize(database, start_iso, end_iso)
+        )
+        await entered.wait()
+        await change()
+        release.set()
+        return await task
+    finally:
+        QuestInstancesDao.upsert_if_valid = original_upsert
+
+
+def test_materialize_skips_removed_assignee_mid_walk(tmp_path) -> None:
+    async def _body(database):
+        children = ChildrenDao(database)
+        a = await children.create("Ada", NOW)
+        c = await children.create("Cleo", NOW)
+        start = _future_monday()
+        start_iso = start.isoformat()
+        end_iso = (start + datetime.timedelta(days=2)).isoformat()
+        created = await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+            [a.id, c.id],
+            ["morning"],
+        )
+
+        async def _remove_assignee():
+            await QuestDefinitionsDao(database).remove_assignee(
+                created.definition.id, a.id
+            )
+
+        count = await _materialize_with_change(
+            database, start_iso, end_iso, _remove_assignee
+        )
+        assert count == 3  # Cleo's three days survive
+        assert await QuestInstancesDao(database).list_by_date_range(
+            a.id, start_iso, end_iso
+        ) == []
+        assert len(await QuestInstancesDao(database).list_by_date_range(
+            c.id, start_iso, end_iso
+        )) == 3
+        return count
+
+    _with_db(tmp_path, "materialize-removed-assignee.db")(_body)
+
+
+def test_materialize_skips_deactivated_definition_mid_walk(tmp_path) -> None:
+    async def _body(database):
+        children = ChildrenDao(database)
+        a = await children.create("Ada", NOW)
+        c = await children.create("Cleo", NOW)
+        start = _future_monday()
+        start_iso = start.isoformat()
+        end_iso = (start + datetime.timedelta(days=2)).isoformat()
+        created = await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+            [a.id, c.id],
+            ["morning"],
+        )
+
+        async def _deactivate():
+            await QuestDefinitionsDao(database).set_active(
+                created.definition.id, False
+            )
+
+        count = await _materialize_with_change(
+            database, start_iso, end_iso, _deactivate
+        )
+        assert count == 0
+        assert await QuestInstancesDao(database).list_by_date_range(
+            a.id, start_iso, end_iso
+        ) == []
+        assert await QuestInstancesDao(database).list_by_date_range(
+            c.id, start_iso, end_iso
+        ) == []
+        return count
+
+    _with_db(tmp_path, "materialize-deactivated-definition.db")(_body)
+
+
+def test_materialize_skips_removed_window_mid_walk(tmp_path) -> None:
+    async def _body(database):
+        children = ChildrenDao(database)
+        a = await children.create("Ada", NOW)
+        start = _future_monday()
+        start_iso = start.isoformat()
+        end_iso = (start + datetime.timedelta(days=2)).isoformat()
+        created = await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+            [a.id],
+            [("morning", "09:00")],
+        )
+
+        async def _remove_window():
+            await QuestDefinitionsDao(database).remove_window(
+                created.definition.id, "morning"
+            )
+
+        count = await _materialize_with_change(
+            database, start_iso, end_iso, _remove_window
+        )
+        assert count == 0
+        assert await QuestInstancesDao(database).list_by_date_range(
+            a.id, start_iso, end_iso
+        ) == []
+        return count
+
+    _with_db(tmp_path, "materialize-removed-window.db")(_body)
+
+
+def test_materialize_skips_deactivated_child_mid_walk(tmp_path) -> None:
+    async def _body(database):
+        children = ChildrenDao(database)
+        a = await children.create("Ada", NOW)
+        c = await children.create("Cleo", NOW)
+        start = _future_monday()
+        start_iso = start.isoformat()
+        end_iso = (start + datetime.timedelta(days=2)).isoformat()
+        created = await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+            [a.id, c.id],
+            ["morning"],
+        )
+
+        async def _deactivate_child():
+            await ChildrenDao(database).set_active(a.id, False)
+
+        count = await _materialize_with_change(
+            database, start_iso, end_iso, _deactivate_child
+        )
+        assert count == 3  # Cleo's three days survive
+        assert await QuestInstancesDao(database).list_by_date_range(
+            a.id, start_iso, end_iso
+        ) == []
+        assert len(await QuestInstancesDao(database).list_by_date_range(
+            c.id, start_iso, end_iso
+        )) == 3
+        return count
+
+    _with_db(tmp_path, "materialize-deactivated-child.db")(_body)
+
+
+def test_materialize_uses_single_presence_snapshot_mid_walk(tmp_path) -> None:
+    async def _body(database):
+        from custom_components.nestquest.dao_presence import (
+            PresenceOverridesDao,
+        )
+
+        children = ChildrenDao(database)
+        overrides = PresenceOverridesDao(database)
+        child = await children.create("Ada", NOW)
+        start = _future_monday()
+        start_iso = start.isoformat()
+        end_iso = (start + datetime.timedelta(days=2)).isoformat()
+        await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+            [child.id],
+            ["morning"],
+        )
+
+        async def _add_blocking_override():
+            await overrides.create(
+                child.id, start_iso, end_iso, False, note="away"
+            )
+
+        # Presence is read once, inside the input snapshot; a blocking
+        # override landing AFTER that read must not make the walk panic
+        # or half-apply it.  The walk stays fully governed by the
+        # snapshot it read: Ada was present every day there, so all
+        # three days still materialize, deterministically.
+        count = await _materialize_with_change(
+            database, start_iso, end_iso, _add_blocking_override
+        )
+        assert count == 3
+        assert len(await QuestInstancesDao(database).list_by_date_range(
+            child.id, start_iso, end_iso
+        )) == 3
+        return count
+
+    _with_db(tmp_path, "materialize-presence-snapshot.db")(_body)
+
+
+def test_materialize_stores_snapshot_due_time_not_live(tmp_path) -> None:
+    async def _body(database):
+        children = ChildrenDao(database)
+        child = await children.create("Ada", NOW)
+        start = _future_monday()
+        start_iso = start.isoformat()
+        end_iso = (start + datetime.timedelta(days=2)).isoformat()
+        created = await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+            [child.id],
+            [("morning", "09:00")],
+        )
+
+        async def _edit_due_time():
+            await QuestDefinitionsDao(database).upsert_window(
+                created.definition.id, "morning", due_time="10:15"
+            )
+
+        # The window's due_time is edited to 10:15 between the input
+        # snapshot and the insert.  The walk must store the SNAPSHOT's
+        # 09:00, not the live 10:15 — the batch is governed entirely by
+        # the coherent snapshot it read.
+        count = await _materialize_with_change(
+            database, start_iso, end_iso, _edit_due_time
+        )
+        assert count == 3
+        records = await QuestInstancesDao(database).list_by_date_range(
+            child.id, start_iso, end_iso
+        )
+        assert len(records) == 3
+        assert {r.due_time for r in records} == {"09:00"}
+        return records
+
+    _with_db(tmp_path, "materialize-snapshot-due-time.db")(_body)
+
+
+def test_materialize_honours_overrides(tmp_path) -> None:
+    async def _body(database):
+        from custom_components.nestquest.dao_presence import (
+            PresenceOverridesDao,
+        )
+
+        children = ChildrenDao(database)
+        overrides = PresenceOverridesDao(database)
+        child = await children.create("Ada", NOW)
+        start = _future_monday()
+        start_iso = start.isoformat()
+        end_iso = (start + datetime.timedelta(days=6)).isoformat()
+        # No schedule means the child is present every day; a blocking
+        # override covering the whole week must still suppress every day.
+        await overrides.create(
+            child.id, start_iso, end_iso, False, note="away all week"
+        )
+        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)
+        assert count == 0
+        return count
+
+    _with_db(tmp_path, "materialize-override.db")(_body)
+
+
+def test_materialize_rejects_malformed_or_inverted_bounds(tmp_path) -> None:
+    async def _body(database):
+        start = (_future_monday()).isoformat()
+        end = (datetime.date.fromisoformat(start)
+               + datetime.timedelta(days=1)).isoformat()
+        for bad in ("not-a-date", "2026-9-7", ""):
+            with pytest.raises(ValueError, match="start_date"):
+                await materialize(database, bad, end)
+            with pytest.raises(ValueError, match="end_date"):
+                await materialize(database, start, bad)
+        with pytest.raises(ValueError, match="end_date must be on or after"):
+            await materialize(database, end, start)
+        return None
+
+    _with_db(tmp_path, "materialize-bounds.db")(_body)
+
+
+def test_materialize_skips_inactive_definitions(tmp_path) -> None:
+    async def _body(database):
+        from custom_components.nestquest.quest_definitions import (
+            set_quest_definition_active,
+        )
+
+        children = ChildrenDao(database)
+        child = await children.create("Ada", NOW)
+        start = _future_monday()
+        start_iso = start.isoformat()
+        end_iso = (start + datetime.timedelta(days=6)).isoformat()
+        created = await create_quest_definition(
+            database,
+            "Daily chore",
+            ScheduleRule(rule_type=RuleType.DAILY, start_date=start_iso),
+            [child.id],
+            ["morning"],
+        )
+        await set_quest_definition_active(database, created.definition.id, False)
+        count = await materialize(database, start_iso, end_iso)
+        assert count == 0
+        return count
+
+    _with_db(tmp_path, "materialize-inactive.db")(_body)