Merge branch 'task/release-sync-04' into task/final-sync

Reviewed-on: #34

README.md

@@ -4,7 +4,7 @@ Home Assistant custom integration for managing chores, custody schedules, and ho
 
 ## Overview
 
-NestQuest helps households coordinate daily life, chore tracking, custody schedules, and shared tasks directly within Home Assistant. It provides sensors, calendar events, and service calls designed for busy households and modern co-parenting setups.
+NestQuest helps households coordinate daily life, chore tracking, custody schedules, and shared tasks directly within Home Assistant. Version 0.2.0 ships the integration foundation (a configuration scaffold providing the package and manifest, shared constants, a single-instance config flow, setup/unload lifecycle handling with an options-reload listener, an options flow for horizon days / day rollover time / panel idle timeout, and HACS metadata) plus its persistence layer: an integration-owned SQLite database (`nestquest.db`) with WAL mode and foreign keys, a versioned migration runner stamping schema version 1 across eight tables (children, admin_users, schedule_rules, task_definitions, presence_schedules, presence_overrides, task_instances, completion_events), typed DAO layers so no other feature writes raw SQL, an append-only completion-event log, and startup handling that creates a missing database, opens a valid one, and refuses — without touching a single byte — to start on a corrupt one. Sensors, calendar events, and service calls are forthcoming in later releases and are not yet part of the integration.
 
 ## Installation
 

RELEASE_NOTES.md

@@ -0,0 +1,129 @@
+# NestQuest Release Notes
+
+## Version 0.3.0 — 2026-09-14
+
+### Scope
+
+Third release of NestQuest, shipping Feature 04 — the recurrence rule engine:
+
+- `ScheduleRule`: an immutable, validated rule model (frozen dataclass) covering six recurrence shapes — daily, weekly (weekday sets), monthly by day-of-month, monthly by nth-weekday, yearly, and custom day-sets — with interval multipliers (every N days/weeks/months) and optional start/end dates.
+- `occurs_on(rule, date)`: pure calendar evaluation answering "does this rule fire on this date?"
+- `occurrences_between(rule, start, end)`: ordered, duplicate-free date lists, inclusive bounds, clamped to the rule's window.
+- Documented month-end policy: a rule for a day absent from a month (the 31st in April, the 30th/29th in February) fires on that month's LAST day — one firing per eligible month, interval anchored on nominal months elapsed.
+- Documented leap-day policy: a YEARLY February 29th rule fires February 28th in non-leap years (clamp, never skip).
+- Week offsets anchored at `start_date` by whole weeks elapsed — never ISO week numbers or parity (53-week years cannot silently invert schedules).
+- The engine is pure: no Home Assistant imports, no database access, no timezone logic.
+
+### Requirements
+
+- Minimum Home Assistant version: **2024.6.0**.
+
+### Known Limitations
+
+- No entities, services, or calendar platforms yet — the integration does not expose sensors, calendar events, or service calls in this release (data layer + recurrence engine only).
+- No business logic yet: nothing materializes task instances (Feature 07 combines this engine with presence schedules) — storage and scheduling math only.
+- Single-instance only — one NestQuest config entry is permitted per Home Assistant instance.
+
+### Rollback
+
+Downgrading from 0.3.0 to 0.2.0 removes the recurrence engine's code but leaves the `nestquest.db` file in place (untouched by both releases; no code path deletes an existing database). The file's schema version stamp is unaffected — this release ships no schema migration.
+
+**HACS installs:**
+
+1. Downgrade via HACS (HACS > Integrations > NestQuest > Redownload > 0.2.0) or Remove entirely.
+2. Restart Home Assistant.
+3. If removing: delete the integration in Home Assistant (Settings > Devices & Services > NestQuest > Delete), then restart Home Assistant again.
+
+**Manual installs:**
+
+1. Replace or delete the `custom_components/nestquest/` directory in your Home Assistant configuration directory.
+2. Restart Home Assistant.
+
+**Data:** the `nestquest.db` file (plus any `-wal`/`-shm` sidecars) is intentionally left untouched by both upgrade and rollback — back it up before upgrading.
+
+**Repository operators:** instead of uninstalling, git-revert the release merge on `main`.
+
+## Version 0.2.0 — 2026-09-14
+
+### Scope
+
+Second release of NestQuest, shipping Feature 02 — the persistence layer the integration owns outright:
+
+- Integration-owned SQLite database (`nestquest.db`) in the Home Assistant config directory: WAL journal mode, foreign-key enforcement, executor-backed access so the event loop never blocks.
+- Schema v1 for all eight tables: `children`, `admin_users`, `schedule_rules`, `task_definitions`, `presence_schedules`, `presence_overrides`, `task_instances`, `completion_events` — validated by declarative CHECK constraints (no triggers).
+- Versioned migration runner: on first start the database is created at schema version 1; later starts are a verified no-op; a failed migration rolls back to its prior version. A database stamped with a future version refuses to start.
+- Typed DAO layers for every table so no other feature writes raw SQL. Assignment validation is atomic; `completion_events` is strictly append-only — the DAO exposes no update or delete method, and a package-wide guard keeps mutation SQL out of every module.
+- Startup handling for missing (created fresh), valid (opened), and corrupt (logged, retried, left byte-identical for backup restore) database files via a read-only `integrity_check` preflight that cannot write or create sidecars. An existing zero-byte file is treated as corrupt and left untouched, not silently initialized.
+
+### Requirements
+
+- Minimum Home Assistant version: **2024.6.0**.
+
+### Known Limitations
+
+- No entities, services, or calendar platforms yet — the integration does not expose sensors, calendar events, or service calls in this release (data layer only).
+- No business logic yet: nothing materializes task instances and nothing can be completed — storage and schema only.
+- Single-instance only — one NestQuest config entry is permitted per Home Assistant instance.
+
+### Rollback
+
+Downgrading from 0.2.0 to 0.1.0 removes the data layer's code but leaves the `nestquest.db` file in place (0.1.0 never touches it, and no code path deletes an existing database). The file is compatible with this release's schema version stamp; a later reinstall of 0.2.0 or newer resumes from the stored version without re-migrating.
+
+**HACS installs:**
+
+1. Downgrade via HACS (HACS > Integrations > NestQuest > Redownload > 0.1.0) or Remove entirely.
+2. Restart Home Assistant.
+3. If removing: delete the integration in Home Assistant (Settings > Devices & Services > NestQuest > Delete), then restart Home Assistant again.
+
+**Manual installs:**
+
+1. Replace or delete the `custom_components/nestquest/` directory in your Home Assistant configuration directory.
+2. Restart Home Assistant.
+
+**Data:** the `nestquest.db` file (plus any `-wal`/`-shm` sidecars) is intentionally left untouched by both upgrade and rollback — back it up before upgrading. Removing the integration via Home Assistant deletes the config entry but not the database file.
+
+**Repository operators:** instead of uninstalling, git-revert the release merge on `main`. The database file is independent of the code and is never deleted by code.
+
+## Version 0.1.0 — 2026-09-13
+
+### Scope
+
+First release of NestQuest, a Home Assistant custom integration for managing chores, custody schedules, and house coordination. This release ships the integration foundation/configuration scaffold only (Feature 01):
+
+- Integration package and manifest, plus HACS metadata (`hacs.json`).
+- Shared constants module.
+- Single-instance config flow for initial setup.
+- Setup/unload lifecycle handling with an options-reload listener.
+- Options flow for configuring `horizon_days`, `day_rollover_time`, and `panel_idle_timeout`.
+- SQLite storage path resolver — path resolution only; the database itself is not created in this release.
+- Test harness (`pytest` via `uv`).
+
+### Requirements
+
+- Minimum Home Assistant version: **2024.6.0**.
+
+### Known Limitations
+
+- No entities, services, or calendar platforms yet — the integration does not expose sensors, calendar events, or service calls in this release.
+- Single-instance only — one NestQuest config entry is permitted per Home Assistant instance.
+- The integration does not yet create its database file; storage is limited to resolved configuration paths.
+
+### Rollback
+
+v0.1.0 is the first release — there is no previous NestQuest version to downgrade to, so rollback means complete removal. This release performs no data or schema migrations.
+
+**HACS installs:**
+
+1. Remove the NestQuest integration via HACS (HACS > Integrations > NestQuest > Remove).
+2. Restart Home Assistant.
+3. Remove the integration in Home Assistant (Settings > Devices & Services > NestQuest > Delete), then restart Home Assistant again.
+
+**Manual installs:**
+
+1. Delete the `custom_components/nestquest/` directory from your Home Assistant configuration directory.
+2. Restart Home Assistant.
+3. Remove the integration in Home Assistant (Settings > Devices & Services > NestQuest > Delete), then restart Home Assistant again.
+
+**Data:** the integration does not yet create its own database file, so there is no application data. However, Home Assistant persists the config entry and its selected options — removing the integration deletes that configuration, and the option values (`horizon_days`, `day_rollover_time`, `panel_idle_timeout`) will be lost on removal.
+
+**Repository operators:** instead of uninstalling, you may git-revert the release merge on `main`, which removes the integration files entirely. End users should use the uninstall path above.

custom_components/nestquest/__init__.py

@@ -1,13 +1,17 @@
 """The NestQuest integration."""
 from __future__ import annotations
 
+import asyncio
 import inspect
+import sqlite3
 from collections.abc import Callable
 from dataclasses import dataclass
+from pathlib import Path
 from typing import Any
 
 from homeassistant.config_entries import ConfigEntry
 from homeassistant.core import HomeAssistant
+from homeassistant.exceptions import ConfigEntryNotReady
 
 from .const import DOMAIN, LOGGER
 from .db import NestQuestDatabase
@@ -16,6 +20,200 @@ from .store import async_get_db_path
 
 _LOGGER = LOGGER
 
+#: SQLite reports "file is not a database" for corruption in every
+#: sqlite3 version this integration targets; the version-specific
+#: "encrypted or malformed" spelling (3.41+) maps to the same failure.
+_CORRUPTION_MARKERS = (
+    "file is not a database",
+    "database disk image is malformed",
+    "encrypted or malformed",
+    "is not a database",
+)
+
+#: Primary sqlite error codes that mean "the file is corrupt" (SQLite
+#: result codes, https://www.sqlite.org/c3ref/c_abort.html).  Extended
+#: codes carry the primary code in their low byte, so the match is on
+#: errcode & 0xff.
+_CORRUPT_PRIMARY_CODES = frozenset({11, 26})  # SQLITE_CORRUPT, SQLITE_NOTADB
+
+
+def _is_corruption_error(error: BaseException) -> bool:
+    """Return True when ``error`` indicates a corrupt database file.
+
+    Classification order: sqlite_errorcode (Python 3.11+) is primary
+    and locale-independent — SQLITE_CORRUPT and SQLITE_NOTADB, matched
+    on the primary byte so extended codes (SQLITE_CORRUPT_VTAB etc.)
+    classify correctly too.  Message matching is the fallback for
+    older Pythons: the corruption spellings are the documented English
+    texts; a non-English locale could evade them, but the code check
+    already covers every locale on supported Pythons.
+    """
+    if not isinstance(error, sqlite3.DatabaseError):
+        return False
+    errorcode = getattr(error, "sqlite_errorcode", None)
+    if errorcode is not None and (errorcode & 0xFF) in _CORRUPT_PRIMARY_CODES:
+        return True
+    message = str(error).lower()
+    return any(marker in message for marker in _CORRUPTION_MARKERS)
+
+
+async def _preflight_existing_file(
+    hass: HomeAssistant, db_path: Path
+) -> None:
+    """Detect corruption in an EXISTING file before it is opened read-write.
+
+    Opening read-write persists WAL mode — for a rollback-journal file
+    that rewrites the header byte 18/19 and can create -wal/-shm
+    sidecars — which would modify a corrupt file before detection and
+    break the leave-untouched guarantee.  This preflight therefore
+    validates the WHOLE file with a READ-ONLY connection (mode=ro +
+    immutable=1 URI, PRAGMA integrity_check, run on the executor like
+    every other DB call): corruption anywhere fails there, before
+    anything is written.  ``immutable=1`` is SQLite's documented
+    guarantee that the connection performs NO writes and creates NO
+    sidecars — even a WAL-header file gets its -wal/-shm reads served
+    without creating the shared-memory file.  Missing/empty files skip
+    the preflight (they are created fresh by the real open).  All
+    filesystem stat/URI work runs inside the executor job with the
+    probe so the event loop never touches disk.
+    """
+    def _probe() -> None:
+        # The file-state check and URI construction live inside the
+        # executor job: exists/stat/resolve are disk I/O.
+        if not db_path.exists():
+            return
+        # A zero-byte file is neither missing nor a valid database: it
+        # is a corrupt (truncated-to-nothing) existing file, likely an
+        # interrupted first write.  The leave-untouched contract applies
+        # to it too: refuse rather than silently initialize over it.
+        if db_path.stat().st_size == 0:
+            raise sqlite3.DatabaseError(
+                "existing database file is empty (zero bytes): file is "
+                "not a database"
+            )
+        # Properly escape the path: '#'/'?'/'%' in a directory or file
+        # name would otherwise be parsed as URI fragments/parameters/
+        # escapes.  immutable=1: SQLite guarantees no writes and no
+        # sidecar creation from this connection.
+        uri = db_path.resolve().as_uri() + "?mode=ro&immutable=1"
+        conn = sqlite3.connect(uri, uri=True)
+        try:
+            row = conn.execute("PRAGMA integrity_check").fetchone()
+        finally:
+            conn.close()
+        verdict = row[0] if row else "missing"
+        if verdict != "ok":
+            # A non-ok integrity verdict IS corruption: raise with the
+            # verdict text (which matches the corruption markers) and
+            # the NOTADB code so classification is code-based.
+            raise sqlite3.DatabaseError(
+                f"integrity_check verdict {verdict!r} on read-only "
+                f"probe: file is not a database"
+            )
+
+    try:
+        # Every DB-touching call goes through the executor (the mock
+        # harness's async_add_executor_job is awaitable too).
+        await hass.async_add_executor_job(_probe)
+    except sqlite3.DatabaseError as err:
+        # Non-ok integrity verdicts are corruption, full stop: there is
+        # no benign reading of a failed whole-file integrity check, so
+        # ANY DatabaseError out of the probe that carries a corruption
+        # marker or code propagates as corruption.  Only exotic probe
+        # failures that classify as non-corruption (e.g. a transient
+        # 'database is locked' on the read) are downgraded to a debug
+        # log, letting the real open path surface them.
+        if _is_corruption_error(err):
+            raise
+        raise sqlite3.DatabaseError(
+            f"read-only corruption probe of {db_path} failed: {err}"
+        ) from err
+
+async def _async_open_database(
+    hass: HomeAssistant, db_path: Path
+) -> NestQuestDatabase:
+    """Open the NestQuest database, mapping failure modes for setup.
+
+    Three startup cases (Feature 02 done-condition):
+
+    - file missing: SQLite creates it fresh on open — no special path,
+      migrations stamp version 1 on the next step.
+    - file present and valid: opens normally.
+    - file present but corrupt: log an ERROR with the path, close
+      anything opened, and raise ConfigEntryNotReady so HA retries —
+      the file is left byte-for-byte untouched so the owner can
+      restore a backup.  No code path here deletes or overwrites an
+      existing database file.
+
+    Corruption can surface at EITHER stage: a truncated header fails
+    in the read-only preflight (before the file is ever opened
+    read-write), while damaged later pages fail during migrations —
+    the preflight guarantees no WAL sidecar or header write happens
+    on a corrupt file before it is detected.  Both are classified the
+    same way.
+    """
+    try:
+        await _preflight_existing_file(hass, db_path)
+        database = await NestQuestDatabase(hass).open(db_path)
+    except asyncio.CancelledError:
+        raise
+    except sqlite3.DatabaseError as err:
+        if _is_corruption_error(err):
+            _LOGGER.error(
+                "NestQuest database at %s is corrupt: %s. Refusing to "
+                "start with a corrupt database; the file was left "
+                "untouched so a backup can be restored. Home Assistant "
+                "will retry setup.",
+                db_path,
+                err,
+            )
+            raise ConfigEntryNotReady(
+                f"NestQuest database corrupt: {db_path}"
+            ) from err
+        # open() failed for a non-corruption reason (locked file,
+        # permission problem): retryable, but nothing was opened.
+        _LOGGER.error(
+            "NestQuest database at %s failed to open: %s. Home "
+            "Assistant will retry setup.",
+            db_path,
+            err,
+        )
+        raise ConfigEntryNotReady(
+            f"NestQuest database unavailable: {db_path}"
+        ) from err
+    try:
+        await apply_migrations(database)
+    except (sqlite3.DatabaseError, RuntimeError) as err:
+        await database.close()
+        if _is_corruption_error(err):
+            _LOGGER.error(
+                "NestQuest database at %s is corrupt: %s. Refusing to "
+                "start with a corrupt database; the file was left "
+                "untouched so a backup can be restored. Home Assistant "
+                "will retry setup.",
+                db_path,
+                err,
+            )
+            raise ConfigEntryNotReady(
+                f"NestQuest database corrupt: {db_path}"
+            ) from err
+        # A non-corruption failure (e.g. a locked file, a future schema
+        # version) is also a retryable setup problem, but without the
+        # corruption framing.
+        _LOGGER.error(
+            "NestQuest database at %s failed to open: %s. Home "
+            "Assistant will retry setup.",
+            db_path,
+            err,
+        )
+        raise ConfigEntryNotReady(
+            f"NestQuest database unavailable: {db_path}"
+        ) from err
+    except asyncio.CancelledError:
+        await database.close()
+        raise
+    return database
+
 
 @dataclass
 class NestQuestRuntimeData:
@@ -38,8 +236,15 @@ async def async_setup_entry(hass: HomeAssistant, entry: ConfigEntry) -> bool:
     existing = hass.data[DOMAIN].get(entry.entry_id)
     if existing is not None:
         db = getattr(existing, "database", None)
-        if db is None:
-            db = await NestQuestDatabase(hass).open(await async_get_db_path(hass))
+        if db is None or not db.connected:
+            # Reopen through the SAME safety path as first setup: a
+            # record without a live connection (a previous setup's
+            # close-on-failure, or a hand-replaced file) must get the
+            # missing/valid/corrupt classification too, never a raw
+            # sqlite3 error and never skipped migrations.
+            db = await _async_open_database(
+                hass, await async_get_db_path(hass)
+            )
             existing.database = db
         entry.runtime_data = existing
         return True
@@ -50,9 +255,8 @@ async def async_setup_entry(hass: HomeAssistant, entry: ConfigEntry) -> bool:
         """Reload the entry when its options change."""
         await listener_hass.config_entries.async_reload(listener_entry.entry_id)
 
-    database = await NestQuestDatabase(hass).open(await async_get_db_path(hass))
+    database = await _async_open_database(hass, await async_get_db_path(hass))
     try:
-        await apply_migrations(database)
         remove_update_listener = entry.add_update_listener(_async_update_listener)
     except BaseException:
         await database.close()

custom_components/nestquest/dao_instances.py

@@ -0,0 +1,542 @@
+"""Typed DAO layer for task_instances and the append-only completion_events.
+
+All SQL for these tables lives in this module per the feature
+guardrails: callers get typed dataclasses back and never see raw rows
+or SQL.  Every method is async and runs through
+:class:`~.db.NestQuestDatabase`, so each statement executes on the HA
+executor and the event loop never blocks.
+
+Append-only policy (Feature 08, D-005): ``completion_events`` is
+strictly append-only.  This module is where that property is enforced
+mechanically — the completion-events DAO below exposes NO update or
+delete method at all; completions append, un-completions append a
+separate reversal event, and instance state derives from the latest
+event.  A package-wide guard test (bottom of the test module) keeps
+mutation SQL for this table out of every other module.
+
+Upsert semantics: ``TaskInstancesDao.upsert`` is idempotent on
+(definition_id, due_date) — re-running materialization for a date
+whose instance already exists updates nothing that matters
+(child_id/due_time are refreshed only in the degenerate re-generation
+case where they changed) and never duplicates a row.  The schema's
+UNIQUE(definition_id, due_date) is the last line of defense.
+
+``delete_future_uncompleted`` implements the Feature 06/07 rule that
+reassignment and schedule edits regenerate future instances: it
+removes only instances at or after the cutoff that have NO completion
+event, inside one transaction, so an instance completed mid-call can
+never be deleted.  Instances in the past are never touched (Feature 07
+guardrail: never delete history), and no method here deletes a single
+instance that has a completion event.
+"""
+from __future__ import annotations
+
+import datetime
+from dataclasses import dataclass
+
+from .dao_children import _connection_lock
+from .dao_rules import _validate_date
+from .db import NestQuestDatabase
+
+#: Timestamp policy for completion_events.occurred_at: strict UTC
+#: ISO-8601 with explicit '+00:00' offset (module timestamp policy).
+#: One accepted shape bounds lexicographic range queries correctly, so
+#: the occurred-at scope does not guess at timestamp spellings.
+_UTC_TIMESTAMP_FORMAT = "%Y-%m-%dT%H:%M:%S+00:00"
+
+
+def _validate_utc_timestamp(value: str, field: str) -> None:
+    """Raise ValueError unless ``value`` is a strict UTC ISO-8601 stamp.
+
+    Same strictness policy as _validate_date: parse with the exact
+    format, then round-trip the parsed value against the input so
+    non-padded or re-ordered shapes are rejected.  The round-trip
+    strips the UTC offset (isoformat() omits it when the offset is
+    zero), so the suffix is appended before comparing.
+    """
+    try:
+        parsed = datetime.datetime.strptime(
+            value, _UTC_TIMESTAMP_FORMAT
+        )
+    except (TypeError, ValueError):
+        raise ValueError(
+            f"{field} must be a UTC ISO-8601 timestamp "
+            f"(YYYY-MM-DDTHH:MM:SS+00:00), got {value!r}"
+        ) from None
+    if parsed.isoformat() + "+00:00" != value:
+        raise ValueError(
+            f"{field} must be a strict UTC timestamp "
+            f"(YYYY-MM-DDTHH:MM:SS+00:00), got {value!r}"
+        )
+
+
+def _next_day_start(date_str: str) -> str:
+    """Return the UTC timestamp one second past ``date_str``'s last second.
+
+    Gives range queries an exact half-open upper bound without
+    fractional-second guessing.
+    """
+    day = datetime.date.fromisoformat(date_str)
+    next_day = day + datetime.timedelta(days=1)
+    return f"{next_day.isoformat()}T00:00:00+00:00"
+
+
+@dataclass(frozen=True)
+class TaskInstanceRecord:
+    """One row of ``task_instances``."""
+
+    id: int
+    definition_id: int
+    child_id: int
+    due_date: str
+    due_time: str | None
+    generated_at: str
+
+
+@dataclass(frozen=True)
+class CompletionEventRecord:
+    """One row of ``completion_events`` (append-only table)."""
+
+    id: int
+    instance_id: int
+    child_id: int
+    event_type: str
+    actor_source: str
+    actor_user_id: str | None
+    occurred_at: str
+    was_on_time: bool | None
+
+
+_INSTANCE_COLUMNS = (
+    "id, definition_id, child_id, due_date, due_time, generated_at"
+)
+_EVENT_COLUMNS = (
+    "id, instance_id, child_id, event_type, actor_source, actor_user_id, "
+    "occurred_at, was_on_time"
+)
+
+#: The only event types the append path accepts; the schema CHECK is
+#: the backstop, this is the DAO's typed front door.
+EVENT_COMPLETED = "completed"
+EVENT_UNCOMPLETED = "uncompleted"
+
+
+def _instance_from_row(row: tuple) -> TaskInstanceRecord:
+    return TaskInstanceRecord(
+        id=row[0],
+        definition_id=row[1],
+        child_id=row[2],
+        due_date=row[3],
+        due_time=row[4],
+        generated_at=row[5],
+    )
+
+
+def _event_from_row(row: tuple) -> CompletionEventRecord:
+    return CompletionEventRecord(
+        id=row[0],
+        instance_id=row[1],
+        child_id=row[2],
+        event_type=row[3],
+        actor_source=row[4],
+        actor_user_id=row[5],
+        occurred_at=row[6],
+        was_on_time=None if row[7] is None else bool(row[7]),
+    )
+
+
+class TaskInstancesDao:
+    """Typed async access to the ``task_instances`` table."""
+
+    def __init__(self, database: NestQuestDatabase) -> None:
+        self._database = database
+
+    async def upsert(
+        self,
+        definition_id: int,
+        child_id: int,
+        due_date: str,
+        generated_at: str,
+        *,
+        due_time: str | None = None,
+    ) -> TaskInstanceRecord:
+        """Create the dated instance for (definition_id, due_date) idempotently.
+
+        Running materialization twice for the same definition/date
+        yields ONE row: the ON CONFLICT path refreshes the
+        generation-time snapshot columns (child_id, due_time) and
+        regenerates the stamp, but can never create a duplicate — the
+        schema UNIQUE(definition_id, due_date) backs this up.
+
+        Guardrails enforced here:
+
+        - ``due_date`` must be TODAY or later: instances are never
+          generated in the past (Feature 07).  The caller's
+          materialization horizon is future-dated by definition.
+        - An existing instance WITH a completion event is IMMUTABLE:
+          the conflict path must not rewrite its snapshot columns
+          (Feature 07/08).  The upsert refuses with ValueError in that
+          case — silently no-oping would mask a materialization bug.
+
+        The definition must exist (validated atomically under the
+        connection lock so a concurrent definition deactivation or
+        child deletion cannot slip between check and insert).
+        """
+        _validate_date(due_date, "due_date")
+        # Fail fast on obviously-past dates BEFORE queuing on the
+        # connection lock (a nice early error); the AUTHORITATIVE
+        # check runs after the lock is acquired, because a caller can
+        # wait on the lock across midnight — a date that was "today"
+        # at call time may be "yesterday" by the time the INSERT runs.
+        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():
+                # Re-read "today" under the lock: this is the date the
+                # insert actually executes on, so the no-past rule
+                # holds across midnight rollovers.
+                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 "
+                        f"{today!r}"
+                    )
+                definition = await self._database.fetch_one(
+                    "SELECT child_id FROM task_definitions WHERE id = ?",
+                    (definition_id,),
+                )
+                if definition is None:
+                    raise ValueError(
+                        f"task definition {definition_id} does not exist"
+                    )
+                if definition[0] != child_id:
+                    raise ValueError(
+                        f"task definition {definition_id} is assigned to "
+                        f"child {definition[0]}, not {child_id}; instance "
+                        "must carry the definition's assignee"
+                    )
+                child = await self._database.fetch_one(
+                    "SELECT 1 FROM children WHERE id = ?", (child_id,)
+                )
+                if child is None:
+                    raise ValueError(f"child {child_id} does not exist")
+                existing = await self._database.fetch_one(
+                    "SELECT 1 FROM completion_events "
+                    "WHERE instance_id = (SELECT id FROM task_instances "
+                    "WHERE definition_id = ? AND due_date = ?)",
+                    (definition_id, due_date),
+                )
+                if existing is not None:
+                    raise ValueError(
+                        f"instance for (definition {definition_id}, "
+                        f"due_date {due_date!r}) already has a completion "
+                        "event and is immutable; it cannot be regenerated"
+                    )
+                await self._database.execute(
+                    "INSERT INTO task_instances (definition_id, child_id, "
+                    "due_date, due_time, generated_at) "
+                    "VALUES (?, ?, ?, ?, ?) "
+                    "ON CONFLICT (definition_id, due_date) DO UPDATE SET "
+                    "child_id = excluded.child_id, "
+                    "due_time = excluded.due_time, "
+                    "generated_at = excluded.generated_at",
+                    (
+                        definition_id,
+                        child_id,
+                        due_date,
+                        due_time,
+                        generated_at,
+                    ),
+                )
+                instance = await self.get(definition_id, due_date)
+        assert instance is not None
+        return instance
+
+    async def get(
+        self, definition_id: int, due_date: str
+    ) -> TaskInstanceRecord | None:
+        """Return the instance for (definition_id, due_date), or None."""
+        row = await self._database.fetch_one(
+            f"SELECT {_INSTANCE_COLUMNS} FROM task_instances "
+            "WHERE definition_id = ? AND due_date = ?",
+            (definition_id, due_date),
+        )
+        return _instance_from_row(row) if row is not None else None
+
+    async def get_by_id(self, instance_id: int) -> TaskInstanceRecord | None:
+        """Return the instance with ``instance_id``, or None."""
+        row = await self._database.fetch_one(
+            f"SELECT {_INSTANCE_COLUMNS} FROM task_instances WHERE id = ?",
+            (instance_id,),
+        )
+        return _instance_from_row(row) if row is not None else None
+
+    async def list_by_child_and_date(
+        self, child_id: int, due_date: str
+    ) -> list[TaskInstanceRecord]:
+        """Return the child's instances due exactly on ``due_date``."""
+        _validate_date(due_date, "due_date")
+        rows = await self._database.fetch_all(
+            f"SELECT {_INSTANCE_COLUMNS} FROM task_instances "
+            "WHERE child_id = ? AND due_date = ? ORDER BY id",
+            (child_id, due_date),
+        )
+        return [_instance_from_row(row) for row in rows]
+
+    async def list_by_date_range(
+        self,
+        child_id: int,
+        range_start: str,
+        range_end: str,
+    ) -> list[TaskInstanceRecord]:
+        """Return the child's instances with due_date in the closed range."""
+        _validate_date(range_start, "range_start")
+        _validate_date(range_end, "range_end")
+        if range_end < range_start:
+            raise ValueError(
+                "range_end must be on or after range_start, got "
+                f"{range_end!r} < {range_start!r}"
+            )
+        rows = await self._database.fetch_all(
+            f"SELECT {_INSTANCE_COLUMNS} FROM task_instances "
+            "WHERE child_id = ? AND due_date >= ? AND due_date <= ? "
+            "ORDER BY due_date, id",
+            (child_id, range_start, range_end),
+        )
+        return [_instance_from_row(row) for row in rows]
+
+    async def delete_future_uncompleted(
+        self, definition_id: int, cutoff_date: str
+    ) -> int:
+        """Delete the definition's open instances at/after cutoff.
+
+        Implements regeneration after a definition edit or reassignment
+        (Features 06/07): only instances at or after the cutoff with NO
+        completion event are removed, inside one transaction — an
+        instance that gains a completion event mid-flight cannot be
+        deleted.  The cutoff cannot be backdated below today: "future"
+        is measured against TODAY (materialization regenerates the
+        rolling horizon from now), so a caller cannot use this to
+        rewrite past open instances either — the day-rollover missed
+        sweep (Feature 11) handles past-due instances instead.  Any
+        instance with a completion event is never touched, regardless
+        of its date.  Returns the number deleted.
+        """
+        _validate_date(cutoff_date, "cutoff_date")
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                # "today" is read under the lock: the DELETE actually
+                # executes on this date, so a caller queued across
+                # midnight cannot delete instances that became past
+                # while it waited (backdating is clamped to the
+                # execution date, not the call date).
+                today = datetime.date.today().isoformat()
+                effective_cutoff = max(cutoff_date, today)
+                result = await self._database.execute(
+                    "DELETE FROM task_instances WHERE definition_id = ? "
+                    "AND due_date >= ? AND NOT EXISTS ("
+                    "  SELECT 1 FROM completion_events "
+                    "  WHERE instance_id = task_instances.id"
+                    ")",
+                    (definition_id, effective_cutoff),
+                )
+        return result.rowcount
+
+
+class CompletionEventsDao:
+    """Typed append-only access to the ``completion_events`` table.
+
+    APPEND-ONLY BY CONSTRUCTION: this class exposes ``append`` and the
+    three read methods below, and NOTHING else.  There is no update,
+    no delete, no upsert — completions and un-completions both arrive
+    through :meth:`append` as new rows, and history is immutable
+    (Feature 08 done-condition: no code path updates or deletes an
+    event).  The package-wide leak-guard test additionally fails if
+    mutation SQL for this table ever appears in any module.
+    """
+
+    def __init__(self, database: NestQuestDatabase) -> None:
+        self._database = database
+
+    async def append(
+        self,
+        instance_id: int,
+        child_id: int,
+        event_type: str,
+        actor_source: str,
+        occurred_at: str,
+        was_on_time: bool | None,
+        *,
+        actor_user_id: str | None = None,
+    ) -> CompletionEventRecord:
+        """Append one completion or un-completion event.
+
+        ``actor_source`` is 'user' (with ``actor_user_id`` set) or
+        'panel' (no user id — the tapped child is ``child_id``); the
+        actor-pair CHECK enforces this shape at the schema level.
+        ``occurred_at`` must be the module's one strict UTC shape
+        (YYYY-MM-DDTHH:MM:SS+00:00) so lexicographic range queries
+        stay exact.  ``was_on_time`` is required (True/False) for
+        completions; for un-completions it may be None or the reversed
+        completion's flag (Feature 08 still deciding).  The instance
+        and child must exist — validated atomically under the
+        connection lock.
+        """
+        if event_type not in (EVENT_COMPLETED, EVENT_UNCOMPLETED):
+            raise ValueError(
+                f"event_type must be '{EVENT_COMPLETED}' or "
+                f"'{EVENT_UNCOMPLETED}', got {event_type!r}"
+            )
+        if actor_source not in ("user", "panel"):
+            raise ValueError(
+                f"actor_source must be 'user' or 'panel', got "
+                f"{actor_source!r}"
+            )
+        if actor_source == "user" and not actor_user_id:
+            raise ValueError(
+                "actor_source 'user' requires actor_user_id"
+            )
+        if actor_source == "panel" and actor_user_id is not None:
+            raise ValueError(
+                "actor_source 'panel' must not carry actor_user_id"
+            )
+        if was_on_time is not None and type(was_on_time) is not bool:
+            raise ValueError(
+                f"was_on_time must be True, False or None, got "
+                f"{was_on_time!r}"
+            )
+        if event_type == EVENT_COMPLETED and was_on_time is None:
+            raise ValueError(
+                "a completed event requires was_on_time (True/False)"
+            )
+        _validate_utc_timestamp(occurred_at, "occurred_at")
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                instance = await self._database.fetch_one(
+                    "SELECT child_id FROM task_instances WHERE id = ?",
+                    (instance_id,),
+                )
+                if instance is None:
+                    raise ValueError(
+                        f"task instance {instance_id} does not exist"
+                    )
+                if instance[0] != child_id:
+                    raise ValueError(
+                        f"task instance {instance_id} belongs to child "
+                        f"{instance[0]}, not {child_id}"
+                    )
+                child = await self._database.fetch_one(
+                    "SELECT 1 FROM children WHERE id = ?", (child_id,)
+                )
+                if child is None:
+                    raise ValueError(f"child {child_id} does not exist")
+                result = await self._database.execute(
+                    "INSERT INTO completion_events (instance_id, child_id, "
+                    "event_type, actor_source, actor_user_id, occurred_at, "
+                    "was_on_time) VALUES (?, ?, ?, ?, ?, ?, ?)",
+                    (
+                        instance_id,
+                        child_id,
+                        event_type,
+                        actor_source,
+                        actor_user_id,
+                        occurred_at,
+                        None if was_on_time is None else int(was_on_time),
+                    ),
+                )
+                event = await self._database.fetch_one(
+                    f"SELECT {_EVENT_COLUMNS} FROM completion_events "
+                    "WHERE id = ?",
+                    (result.lastrowid,),
+                )
+        assert event is not None
+        return _event_from_row(event)
+
+    async def list_by_instance(
+        self, instance_id: int
+    ) -> list[CompletionEventRecord]:
+        """Return every event for the instance, in append order."""
+        rows = await self._database.fetch_all(
+            f"SELECT {_EVENT_COLUMNS} FROM completion_events "
+            "WHERE instance_id = ? ORDER BY id",
+            (instance_id,),
+        )
+        return [_event_from_row(row) for row in rows]
+
+    async def list_by_child_and_date_range(
+        self,
+        child_id: int,
+        range_start: str,
+        range_end: str,
+        *,
+        due_date_scope: bool = True,
+    ) -> list[CompletionEventRecord]:
+        """Return the child's events for instances due in the closed range.
+
+        Scopes by the INSTANCE's due date (the day the task was owed),
+        not the event's wall-clock timestamp: history reporting asks
+        "what happened to the tasks due that day" (Feature 13).  Set
+        ``due_date_scope=False`` to filter by ``occurred_at`` date
+        instead.  Ordered by instance, then event id.
+        """
+        _validate_date(range_start, "range_start")
+        _validate_date(range_end, "range_end")
+        if range_end < range_start:
+            raise ValueError(
+                "range_end must be on or after range_start, got "
+                f"{range_end!r} < {range_start!r}"
+            )
+        if due_date_scope:
+            predicate = (
+                "SELECT id FROM task_instances WHERE child_id = ? "
+                "AND due_date >= ? AND due_date <= ?"
+            )
+            parameters: list[object] = [child_id, range_start, range_end]
+        else:
+            # Occurred-at scope: occurred_at is validated to the one
+            # accepted UTC shape at append time, so half-open day
+            # bounds in that same shape are exact (no spelling drift).
+            occurred_start = f"{range_start}T00:00:00+00:00"
+            occurred_end = f"{range_end}T23:59:59+00:00"
+            query = (
+                f"SELECT {_EVENT_COLUMNS} FROM completion_events "
+                "WHERE child_id = ? AND occurred_at >= ? "
+                "AND occurred_at < ? ORDER BY instance_id, id"
+            )
+            rows = await self._database.fetch_all(
+                query,
+                [
+                    child_id,
+                    occurred_start,
+                    _next_day_start(range_end),
+                ],
+            )
+            return [_event_from_row(row) for row in rows]
+        rows = await self._database.fetch_all(
+            f"SELECT {_EVENT_COLUMNS} FROM completion_events "
+            f"WHERE instance_id IN ({predicate}) "
+            "ORDER BY instance_id, id",
+            tuple(parameters),
+        )
+        return [_event_from_row(row) for row in rows]
+
+    async def get_latest_for_instance(
+        self, instance_id: int
+    ) -> CompletionEventRecord | None:
+        """Return the instance's most recent event, or None if untouched.
+
+        Instance current state derives from this event (Feature 08):
+        'completed' means done, 'uncompleted' as the latest means open
+        again, None means never completed.
+        """
+        row = await self._database.fetch_one(
+            f"SELECT {_EVENT_COLUMNS} FROM completion_events "
+            "WHERE instance_id = ? ORDER BY id DESC LIMIT 1",
+            (instance_id,),
+        )
+        return _event_from_row(row) if row is not None else None

custom_components/nestquest/dao_presence.py

@@ -0,0 +1,247 @@
+"""Typed DAO layer for the presence_schedules and presence_overrides tables.
+
+All SQL for these tables lives in this module per the feature
+guardrails: callers get typed dataclasses back and never see raw rows
+or SQL.  Every method is async and runs through
+:class:`~.db.NestQuestDatabase`, so each statement executes on the HA
+executor and the event loop never blocks.
+
+Semantics per the done-condition and Feature 05:
+
+- A child has AT MOST ONE presence schedule (schema UNIQUE); upserting
+  by child updates the existing row rather than raising.
+- A child with NO schedule row is present every day — the absence of a
+  row is meaningful state, expressed by :meth:`PresenceSchedulesDao.get_by_child`
+  returning None, never by an empty pattern row.
+- Overrides beat the pattern for their date range (that resolution is
+  the presence engine's job, Feature 05); the DAO only stores and
+  lists them.  A single-day override stores the same date as both
+  start and end (schema CHECK requires end >= start).
+
+Date arguments are validated as strict YYYY-MM-DD per the same policy
+the rules DAO applies: the schema's lexical CHECKs cannot prove
+calendar validity.
+"""
+from __future__ import annotations
+
+from dataclasses import dataclass
+
+from .dao_children import _connection_lock
+from .dao_rules import _UNSET, _validate_date
+from .db import NestQuestDatabase
+
+
+@dataclass(frozen=True)
+class PresenceScheduleRecord:
+    """One row of ``presence_schedules``."""
+
+    id: int
+    child_id: int
+    cycle_length_weeks: int
+    anchor_date: str
+    pattern: str
+
+
+@dataclass(frozen=True)
+class PresenceOverrideRecord:
+    """One row of ``presence_overrides``."""
+
+    id: int
+    child_id: int
+    start_date: str
+    end_date: str
+    is_present: bool
+    note: str | None
+
+
+_SCHEDULE_COLUMNS = (
+    "id, child_id, cycle_length_weeks, anchor_date, pattern"
+)
+_OVERRIDE_COLUMNS = (
+    "id, child_id, start_date, end_date, is_present, note"
+)
+
+
+def _schedule_from_row(row: tuple) -> PresenceScheduleRecord:
+    return PresenceScheduleRecord(
+        id=row[0],
+        child_id=row[1],
+        cycle_length_weeks=row[2],
+        anchor_date=row[3],
+        pattern=row[4],
+    )
+
+
+def _override_from_row(row: tuple) -> PresenceOverrideRecord:
+    return PresenceOverrideRecord(
+        id=row[0],
+        child_id=row[1],
+        start_date=row[2],
+        end_date=row[3],
+        is_present=bool(row[4]),
+        note=row[5],
+    )
+
+
+class PresenceSchedulesDao:
+    """Typed async access to the ``presence_schedules`` table."""
+
+    def __init__(self, database: NestQuestDatabase) -> None:
+        self._database = database
+
+    async def upsert_by_child(
+        self,
+        child_id: int,
+        cycle_length_weeks: int,
+        anchor_date: str,
+        pattern: str,
+    ) -> PresenceScheduleRecord:
+        """Set the child's presence schedule, replacing any existing one.
+
+        Upsert semantics are the done-condition: a second schedule for
+        the same child updates the existing row rather than raising.
+        Runs under the connection lock so a concurrent upsert/delete
+        for the same child serializes instead of colliding on the
+        schema's UNIQUE(child_id).  Pattern validity (segment shape,
+        segment count = cycle_length_weeks) is enforced by the schema
+        CHECKs; the child must exist (FK) — validated atomically here
+        so a deleted child cannot pass a stale check.
+        """
+        _validate_date(anchor_date, "anchor_date")
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                child = await self._database.fetch_one(
+                    "SELECT 1 FROM children WHERE id = ?", (child_id,)
+                )
+                if child is None:
+                    raise ValueError(f"child {child_id} does not exist")
+                await self._database.execute(
+                    "INSERT INTO presence_schedules (child_id, "
+                    "cycle_length_weeks, anchor_date, pattern) "
+                    "VALUES (?, ?, ?, ?) "
+                    "ON CONFLICT (child_id) DO UPDATE SET "
+                    "cycle_length_weeks = excluded.cycle_length_weeks, "
+                    "anchor_date = excluded.anchor_date, "
+                    "pattern = excluded.pattern",
+                    (child_id, cycle_length_weeks, anchor_date, pattern),
+                )
+                schedule = await self.get_by_child(child_id)
+        assert schedule is not None
+        return schedule
+
+    async def get_by_child(
+        self, child_id: int
+    ) -> PresenceScheduleRecord | None:
+        """Return the child's schedule, or None when the child has none.
+
+        None is meaningful and UNAMBIGUOUS here: the child exists and
+        has no schedule row, i.e. the child is present every day
+        (Feature 05 guardrail).  An unknown child id raises ValueError
+        instead of returning None, because "nonexistent child" must
+        never be silently read as "present every day".
+        """
+        child = await self._database.fetch_one(
+            "SELECT 1 FROM children WHERE id = ?", (child_id,)
+        )
+        if child is None:
+            raise ValueError(f"child {child_id} does not exist")
+        row = await self._database.fetch_one(
+            f"SELECT {_SCHEDULE_COLUMNS} FROM presence_schedules "
+            "WHERE child_id = ?",
+            (child_id,),
+        )
+        return _schedule_from_row(row) if row is not None else None
+
+    async def delete(self, child_id: int) -> bool:
+        """Remove the child's schedule; True if a row was removed.
+
+        After deletion the child is present every day (no row means
+        no restriction).  Deleting a schedule the child never had
+        returns False.
+        """
+        result = await self._database.execute(
+            "DELETE FROM presence_schedules WHERE child_id = ?",
+            (child_id,),
+        )
+        return result.rowcount > 0
+
+
+class PresenceOverridesDao:
+    """Typed async access to the ``presence_overrides`` table."""
+
+    def __init__(self, database: NestQuestDatabase) -> None:
+        self._database = database
+
+    async def create(
+        self,
+        child_id: int,
+        start_date: str,
+        end_date: str,
+        is_present: bool,
+        *,
+        note: str | None = None,
+    ) -> PresenceOverrideRecord:
+        """Insert one override and return the record as stored.
+
+        The child must exist (validated atomically under the
+        connection lock).  Date shape is validated strictly, and the
+        schema CHECK rejects end < start.
+        """
+        _validate_date(start_date, "start_date")
+        _validate_date(end_date, "end_date")
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                child = await self._database.fetch_one(
+                    "SELECT 1 FROM children WHERE id = ?", (child_id,)
+                )
+                if child is None:
+                    raise ValueError(f"child {child_id} does not exist")
+                result = await self._database.execute(
+                    "INSERT INTO presence_overrides (child_id, "
+                    "start_date, end_date, is_present, note) "
+                    "VALUES (?, ?, ?, ?, ?)",
+                    (child_id, start_date, end_date, int(is_present), note),
+                )
+                override = await self._database.fetch_one(
+                    f"SELECT {_OVERRIDE_COLUMNS} FROM presence_overrides "
+                    "WHERE id = ?",
+                    (result.lastrowid,),
+                )
+        assert override is not None
+        return _override_from_row(override)
+
+    async def list_by_child_and_range(
+        self,
+        child_id: int,
+        range_start: str,
+        range_end: str,
+    ) -> list[PresenceOverrideRecord]:
+        """Return the child's overrides overlapping [range_start, range_end].
+
+        An override overlaps the range when it ends on or after the
+        range starts and starts on or before the range ends (inclusive
+        boundary dates, matching the storage model where overrides are
+        closed date ranges).  Ordered by start date.
+        """
+        _validate_date(range_start, "range_start")
+        _validate_date(range_end, "range_end")
+        if range_end < range_start:
+            raise ValueError(
+                "range_end must be on or after range_start, got "
+                f"{range_end!r} < {range_start!r}"
+            )
+        rows = await self._database.fetch_all(
+            f"SELECT {_OVERRIDE_COLUMNS} FROM presence_overrides "
+            "WHERE child_id = ? AND end_date >= ? AND start_date <= ? "
+            "ORDER BY start_date",
+            (child_id, range_start, range_end),
+        )
+        return [_override_from_row(row) for row in rows]
+
+    async def delete(self, override_id: int) -> bool:
+        """Remove one override by id; True if a row was removed."""
+        result = await self._database.execute(
+            "DELETE FROM presence_overrides WHERE id = ?",
+            (override_id,),
+        )
+        return result.rowcount > 0

custom_components/nestquest/manifest.json

@@ -1,12 +1,14 @@
 {
   "domain": "nestquest",
   "name": "NestQuest",
-  "codeowners": ["@overseer"],
+  "codeowners": [
+    "@overseer"
+  ],
   "config_flow": true,
-  "documentation": "https://code.cubecraftlabs.com/Maniacs_Mansion/NestQuest",
+  "documentation": "https://github.com/talon2king/NestQuest",
   "integration_type": "hub",
   "iot_class": "local_polling",
-  "issue_tracker": "https://code.cubecraftlabs.com/Maniacs_Mansion/NestQuest/issues",
+  "issue_tracker": "https://github.com/talon2king/NestQuest/issues",
   "requirements": [],
-  "version": "0.1.0"
+  "version": "0.3.0"
 }

custom_components/nestquest/recurrence.py

@@ -0,0 +1,621 @@
+"""The ScheduleRule model: rule types, validation, and serialization.
+
+A :class:`ScheduleRule` describes WHEN a recurring chore fires — a pure
+frozen dataclass with no Home Assistant and no database imports.  The
+evaluation engine (Feature 04) consumes it; the DAO layer (Feature 02)
+serializes its dict form into the ``schedule_rules`` table.
+
+Type model: one enum member per recurrence shape.  ``MONTHLY_DAY``
+("the 15th of every month") and ``MONTHLY_WEEKDAY`` ("the second
+Tuesday of every month") are distinct types rather than one monthly
+type with optional fields, so validation is total: a rule either
+constructs, or raises :class:`RuleValidationError` naming the offending
+field.  Every type carries ONLY the fields its shape needs; carrying
+any other shape's field is a construction error.
+
+Storage mapping (Feature 02, ``schedule_rules`` table): the enum's
+``storage_value`` maps model types onto the schema's CHECK values —
+MONTHLY_DAY and MONTHLY_WEEKDAY both store as ``'monthly'`` and are
+disambiguated by which fields are populated; CUSTOM_DAYS stores as
+``'custom'``.  ``weekday_set`` round-trips as the sorted CSV list the
+table stores (one element for MONTHLY_WEEKDAY, which names the
+position's weekday); ``nth_weekday_weekday`` is a MODEL-ONLY field
+carried through from_dict/to_dict for losslessness and folded into
+``weekday_set`` by the DAO mapping.  ``to_dict``/``from_dict`` are the
+model-side lossless pair; the DB mapping lives in the DAO layer and is
+exercised there.
+
+Validation policy (fail fast at construction — the schema's CHECK
+constraints are a second line of defense, but invalid rules must never
+reach storage):
+
+- ``interval`` >= 1 for every rule.
+- ``weekday_set`` is required for WEEKLY and CUSTOM_DAYS, forbidden
+  elsewhere; entries are 0 (Monday) .. 6 (Sunday), no booleans.
+- ``day_of_month`` 1..31 is required for MONTHLY_DAY.
+- ``nth_weekday`` 1..5 or -1 (0 is meaningless as a position and is
+  rejected) with a weekday 0..6 for MONTHLY_WEEKDAY.
+- ``month`` 1..12 is required for YEARLY.
+- ``start_date`` is a strict ISO date; ``end_date`` is optional and,
+  when present, on or after ``start_date``.
+
+Month-end policy (implemented in :func:`_occurs_monthly_day` and
+documented for Feature 04): in each interval-eligible month, a
+MONTHLY_DAY rule for a day that does not exist in that month — the
+31st in April, the 30th or 29th in February, February 29th in a
+non-leap year — fires on the LAST day of the month instead of being
+skipped.  A monthly chore still happens in every eligible month,
+pinned to the month's end when its nominal date is absent; exactly one
+day per eligible month matches the rule (nominal day when it exists,
+month end when it does not).  The interval anchors on nominal months
+elapsed from start_date's month, so clamping never shifts the cadence.
+"""
+from __future__ import annotations
+
+import datetime
+import enum
+from dataclasses import dataclass, field
+
+#: Date-shape policy: strict YYYY-MM-DD, matching the DAO layers.
+_DATE_FORMAT = "%Y-%m-%d"
+
+#: Weekday-name policy: 0 = Monday .. 6 = Sunday (ISO order).
+
+
+class RuleType(enum.Enum):
+    """The six recurrence shapes the engine supports.
+
+    ``storage_value`` is the string written to the ``schedule_rules``
+    table's ``rule_type`` column (its CHECK whitelist).  MONTHLY_DAY
+    and MONTHLY_WEEKDAY are distinct shapes sharing one storage value,
+    disambiguated by which fields are populated.
+    """
+
+    DAILY = "daily"
+    WEEKLY = "weekly"
+    MONTHLY_DAY = "monthly_day"
+    MONTHLY_WEEKDAY = "monthly_weekday"
+    YEARLY = "yearly"
+    CUSTOM_DAYS = "custom_days"
+
+    @property
+    def storage_value(self) -> str:
+        """The schema-level rule_type string this model type maps to."""
+        return {
+            RuleType.DAILY: "daily",
+            RuleType.WEEKLY: "weekly",
+            RuleType.MONTHLY_DAY: "monthly",
+            RuleType.MONTHLY_WEEKDAY: "monthly",
+            RuleType.YEARLY: "yearly",
+            RuleType.CUSTOM_DAYS: "custom",
+        }[self]
+
+
+class RuleValidationError(ValueError):
+    """Raised when a ScheduleRule field combination is invalid."""
+
+
+def _validate_date(value: str, field_name: str) -> None:
+    """Raise unless ``value`` is a strict YYYY-MM-DD calendar date."""
+    try:
+        parsed = datetime.datetime.strptime(value, _DATE_FORMAT).date()
+    except (TypeError, ValueError):
+        raise RuleValidationError(
+            f"{field_name} must be an ISO date (YYYY-MM-DD), got {value!r}"
+        ) from None
+    if parsed.isoformat() != value:
+        raise RuleValidationError(
+            f"{field_name} must be a strict YYYY-MM-DD date, got {value!r}"
+        )
+
+
+def _validate_weekday_set(weekday_set) -> frozenset[int]:
+    """Return a normalized frozenset or raise with a clear message.
+
+    Sequence entries are validated BEFORE set() coercion: a list like
+    [0, False] must not collapse the boolean into 0, and unhashable
+    entries must raise RuleValidationError, never a raw TypeError.
+    """
+    if isinstance(weekday_set, (list, tuple)):
+        for entry in weekday_set:
+            # _is_plain_int: bool and int subclasses (potentially
+            # unhashable) are rejected before set() coercion.
+            if not _is_plain_int(entry):
+                raise RuleValidationError(
+                    f"weekday_set entries must be plain integers, got "
+                    f"{entry!r}"
+                )
+        weekday_set = set(weekday_set)
+    if not isinstance(weekday_set, (set, frozenset)):
+        raise RuleValidationError(
+            f"weekday_set must be a set of weekday integers, got "
+            f"{type(weekday_set).__name__}"
+        )
+    if not weekday_set:
+        raise RuleValidationError(
+            "weekday_set must not be empty for weekly/custom rules"
+        )
+    for entry in weekday_set:
+        if not _is_plain_int(entry):
+            raise RuleValidationError(
+                f"weekday_set entries must be plain integers, got "
+                f"{entry!r}"
+            )
+        if not 0 <= entry <= 6:
+            raise RuleValidationError(
+                f"weekday_set entries must be 0 (Monday)..6 (Sunday), "
+                f"got {entry!r}"
+            )
+    return frozenset(weekday_set)
+
+
+def _is_plain_int(value) -> bool:
+    """True only for real ints: bool excluded AND int subclasses too
+    (an unhashable int subclass would break the frozen-hashable model)."""
+    return type(value) is int
+
+
+def _validate_monthly_weekday(nth_weekday, weekday) -> None:
+    """Validate the MONTHLY_WEEKDAY position fields."""
+    if nth_weekday is None or not _is_plain_int(nth_weekday):
+        raise RuleValidationError(
+            "nth_weekday must be an integer for monthly_weekday rules, "
+            f"got {nth_weekday!r}"
+        )
+    if not (-1 <= nth_weekday <= 5) or nth_weekday == 0:
+        raise RuleValidationError(
+            f"nth_weekday must be -1 (last) or 1..5, got {nth_weekday!r}"
+        )
+    if weekday is None or not _is_plain_int(weekday) or not 0 <= weekday <= 6:
+        raise RuleValidationError(
+            f"monthly_weekday rules require a weekday 0 (Monday)..6 "
+            f"(Sunday), got {weekday!r}"
+        )
+
+
+
+
+
+@dataclass(frozen=True)
+class ScheduleRule:
+    """An immutable, validated recurrence rule (see module docstring).
+
+    Construct with keyword arguments; invalid combinations raise
+    :class:`RuleValidationError` at construction time.  Fields map
+    one-to-one onto the ``schedule_rules`` table columns, with
+    ``nth_weekday_weekday`` as the model-only weekday companion to
+    ``nth_weekday`` (serialized inside ``weekday_set`` at the DAO
+    boundary).
+    """
+
+    rule_type: RuleType
+    interval: int = 1
+    weekday_set: frozenset[int] | None = None
+    day_of_month: int | None = None
+    nth_weekday: int | None = None
+    nth_weekday_weekday: int | None = None
+    month: int | None = None
+    start_date: str = "1970-01-01"
+    end_date: str | None = None
+
+    def __post_init__(self) -> None:
+        # Normalize a string rule_type into the enum; anything else
+        # that is not already a RuleType is rejected.
+        if isinstance(self.rule_type, str):
+            # EXACT model names only: no whitespace stripping, no storage
+            # aliases.  The DAO maps storage strings to model fields when
+            # reading rows; the model boundary stays unambiguous.
+            candidates = {rt.name.lower(): rt for rt in RuleType}
+            if self.rule_type in candidates:
+                object.__setattr__(
+                    self, "rule_type", candidates[self.rule_type]
+                )
+            else:
+                raise RuleValidationError(
+                    f"rule_type must be a RuleType or one of "
+                    f"{sorted(candidates)}, got {self.rule_type!r}"
+                )
+        if not isinstance(self.rule_type, RuleType):
+            raise RuleValidationError(
+                f"rule_type must be a RuleType or one of its string "
+                f"values, got {self.rule_type!r}"
+            )
+        if not _is_plain_int(self.interval):
+            raise RuleValidationError(
+                f"interval must be an integer >= 1, got {self.interval!r}"
+            )
+        if self.interval < 1:
+            raise RuleValidationError(
+                f"interval must be >= 1, got {self.interval!r}"
+            )
+
+        _validate_date(self.start_date, "start_date")
+        if self.end_date is not None:
+            _validate_date(self.end_date, "end_date")
+            if self.end_date < self.start_date:
+                raise RuleValidationError(
+                    f"end_date {self.end_date!r} must be on or after "
+                    f"start_date {self.start_date!r}"
+                )
+
+        # Type-exclusive field policy, enforced for every type: build
+        # the dict of present fields, then reject every field the shape
+        # must not carry (weekday_set present is detectable via None vs
+        # set even after normalization below).
+        shape = self.rule_type
+        if shape is RuleType.DAILY:
+            forbidden_fields = (
+                "weekday_set",
+                "day_of_month",
+                "nth_weekday",
+                "nth_weekday_weekday",
+                "month",
+            )
+        elif shape is RuleType.WEEKLY:
+            forbidden_fields = (
+                "day_of_month",
+                "nth_weekday",
+                "nth_weekday_weekday",
+                "month",
+            )
+        elif shape is RuleType.CUSTOM_DAYS:
+            forbidden_fields = (
+                "day_of_month",
+                "nth_weekday",
+                "nth_weekday_weekday",
+                "month",
+            )
+        elif shape is RuleType.MONTHLY_DAY:
+            forbidden_fields = ("weekday_set", "nth_weekday",
+                                "nth_weekday_weekday", "month")
+        elif shape is RuleType.MONTHLY_WEEKDAY:
+            forbidden_fields = ("weekday_set", "day_of_month", "month")
+        elif shape is RuleType.YEARLY:
+            forbidden_fields = (
+                "weekday_set",
+                "nth_weekday",
+                "nth_weekday_weekday",
+            )
+        present_forbidden = [
+            name
+            for name in forbidden_fields
+            if getattr(self, name) is not None
+        ]
+        if present_forbidden:
+            raise RuleValidationError(
+                f"{shape.value} rules must not set "
+                f"{', '.join(sorted(present_forbidden))}"
+            )
+
+        # Required fields per shape.
+        if shape is RuleType.WEEKLY or shape is RuleType.CUSTOM_DAYS:
+            normalized = _validate_weekday_set(self.weekday_set)
+            object.__setattr__(self, "weekday_set", normalized)
+        elif shape is RuleType.MONTHLY_DAY:
+            if self.day_of_month is None or not _is_plain_int(
+                self.day_of_month
+            ):
+                raise RuleValidationError(
+                    "monthly_day rules require day_of_month (1..31)"
+                )
+            if not 1 <= self.day_of_month <= 31:
+                raise RuleValidationError(
+                    f"day_of_month must be 1..31, got {self.day_of_month!r}"
+                )
+        elif shape is RuleType.MONTHLY_WEEKDAY:
+            _validate_monthly_weekday(
+                self.nth_weekday, self.nth_weekday_weekday
+            )
+        elif shape is RuleType.YEARLY:
+            if self.month is None or not _is_plain_int(self.month):
+                raise RuleValidationError(
+                    "yearly rules require month (1..12)"
+                )
+            if not 1 <= self.month <= 12:
+                raise RuleValidationError(
+                    f"month must be 1..12, got {self.month!r}"
+                )
+            if self.day_of_month is not None and not (
+                _is_plain_int(self.day_of_month)
+                and 1 <= self.day_of_month <= 31
+            ):
+                raise RuleValidationError(
+                    f"day_of_month must be 1..31 for yearly rules, got "
+                    f"{self.day_of_month!r}"
+                )
+
+    def to_dict(self) -> dict:
+        """Return the lossless dict form (model-side storage shape).
+
+        ``weekday_set`` serializes as a sorted list; MONTHLY_WEEKDAY's
+        weekday travels inside it (appended) plus
+        ``nth_weekday_weekday`` so the pair round-trips without
+        ambiguity at the model layer.
+        """
+        return {
+            "rule_type": self.rule_type.name.lower(),  # model name
+            "interval": self.interval,
+            "weekday_set": (
+                sorted(self.weekday_set) if self.weekday_set is not None
+                else None
+            ),
+            "day_of_month": self.day_of_month,
+            "nth_weekday": self.nth_weekday,
+            "nth_weekday_weekday": self.nth_weekday_weekday,
+            "month": self.month,
+            "start_date": self.start_date,
+            "end_date": self.end_date,
+        }
+
+    @classmethod
+    def from_dict(cls, data: dict) -> "ScheduleRule":
+        """Rebuild a rule from :meth:`to_dict` output.
+
+        Raises :class:`RuleValidationError` on unknown keys, missing
+        keys, or invalid combinations — never a raw TypeError.
+        """
+        if not isinstance(data, dict):
+            raise RuleValidationError(
+                f"rule data must be a dict, got {type(data).__name__}"
+            )
+        allowed = {
+            "rule_type",
+            "interval",
+            "weekday_set",
+            "day_of_month",
+            "nth_weekday",
+            "nth_weekday_weekday",
+            "month",
+            "start_date",
+            "end_date",
+        }
+        unknown = [key for key in data if key not in allowed]
+        if unknown:
+            raise RuleValidationError(
+                f"unknown rule fields: {sorted(unknown, key=repr)}"
+            )
+        if "rule_type" not in data:
+            raise RuleValidationError("missing required rule field: rule_type")
+        weekday_set = data.get("weekday_set")
+        if isinstance(weekday_set, (list, tuple)):
+            # Validate entries as plain ints BEFORE set() coercion, so a
+            # list like [0, False] cannot collapse False into 0 and an
+            # unhashable/int-subclass entry cannot leak a raw TypeError.
+            for entry in weekday_set:
+                if not _is_plain_int(entry):
+                    raise RuleValidationError(
+                        f"weekday_set entries must be plain integers, got "
+                        f"{entry!r}"
+                    )
+            weekday_set = set(weekday_set)
+        return cls(
+            rule_type=data["rule_type"],
+            interval=data.get("interval", 1),
+            weekday_set=weekday_set,
+            day_of_month=data.get("day_of_month"),
+            nth_weekday=data.get("nth_weekday"),
+            nth_weekday_weekday=data.get("nth_weekday_weekday"),
+            month=data.get("month"),
+            start_date=data.get("start_date", "1970-01-01"),
+            end_date=data.get("end_date"),
+        )
+
+
+# ---------------------------------------------------------------------------
+# Evaluation engine (Feature 04): pure date arithmetic on ScheduleRule
+# ---------------------------------------------------------------------------
+
+
+def _parse_date(value: str) -> datetime.date:
+    """Parse a strict ISO date already validated by the model."""
+    return datetime.datetime.strptime(value, _DATE_FORMAT).date()
+
+
+def _anchor_offset(rule: ScheduleRule, target: datetime.date) -> int:
+    """Day offset from the rule's anchor date to ``target``.
+
+    The anchor for DAILY/WEEKLY-interval arithmetic is start_date: the
+    first day the rule CAN fire.  A negative offset means the target
+    precedes the rule's window entirely.
+    """
+    return (target - _parse_date(rule.start_date)).days
+
+
+def _within_window(rule: ScheduleRule, target: datetime.date) -> bool:
+    """True when ``target`` is inside the rule's [start, end] window."""
+    if target < _parse_date(rule.start_date):
+        return False
+    if rule.end_date is not None and target > _parse_date(rule.end_date):
+        return False
+    return True
+
+
+def _month_end(year: int, month: int) -> int:
+    """The last calendar day of the given month (leap-aware)."""
+    if month == 12:
+        return 31
+    return (datetime.date(year, month + 1, 1)
+            - datetime.timedelta(days=1)).day
+
+
+def _occurs_monthly_day(rule: ScheduleRule, target: datetime.date) -> bool:
+    """MONTHLY_DAY evaluation with the documented month-end policy.
+
+    Policy: in each interval-eligible month, a rule for a day-of-month
+    that does not exist in that month (the 31st in April, the 30th in
+    February, the 29th of February in a non-leap year) fires on the
+    LAST day of that month instead of being skipped — the chore still
+    happens that month, pinned to the month's end when its nominal date
+    is absent.
+
+    The interval anchors on months ELAPSED from start_date's month: the
+    rule fires when the months between the anchor month and the target's
+    month form a multiple of the interval, keeping "every 2 months on
+    the 15th" aligned to the anchor month even when the firing lands on
+    a clamped month-end day.
+    """
+    anchor = _parse_date(rule.start_date)
+    months_elapsed = (
+        (target.year - anchor.year) * 12 + (target.month - anchor.month)
+    )
+    if months_elapsed % rule.interval != 0:
+        return False
+    month_end = _month_end(target.year, target.month)
+    return target.day == min(rule.day_of_month, month_end)
+
+
+def _nth_weekday_of_month(
+    year: int, month: int, weekday: int, nth: int
+) -> int | None:
+    """The day-of-month of the ``nth`` ``weekday``, or None when the
+    month has no such occurrence (``nth`` 5 beyond four occurrences).
+
+    Positive ``nth`` counts forward (1 = first); ``-1`` counts from the
+    end (last occurrence, whatever the total).  Pure calendar math.
+    """
+    if nth == -1:
+        last = datetime.date(year, month, _month_end(year, month))
+        back = (last.weekday() - weekday) % 7
+        return last.day - back
+    first = datetime.date(year, month, 1)
+    first_offset = (weekday - first.weekday()) % 7
+    day = 1 + first_offset + (nth - 1) * 7
+    if day > _month_end(year, month):
+        return None
+    return day
+
+
+def _occurs_monthly_weekday(
+    rule: ScheduleRule, target: datetime.date
+) -> bool:
+    """MONTHLY_WEEKDAY evaluation.
+
+    Fires when the target is the ``nth`` occurrence of
+    ``nth_weekday_weekday`` in its month (1..4 forward, -1 last).  A
+    month with fewer occurrences than ``nth`` (5 on a four-occurrence
+    month) does not fire — n 5 is accepted by the model but skips such
+    months, matching the done-condition.  The interval anchors on
+    nominal months elapsed from start_date's month.
+    """
+    anchor = _parse_date(rule.start_date)
+    months_elapsed = (
+        (target.year - anchor.year) * 12 + (target.month - anchor.month)
+    )
+    if months_elapsed % rule.interval != 0:
+        return False
+    if target.weekday() != rule.nth_weekday_weekday:
+        return False
+    nth_day = _nth_weekday_of_month(
+        target.year, target.month, rule.nth_weekday_weekday, rule.nth_weekday
+    )
+    return nth_day is not None and target.day == nth_day
+
+
+def _occurs_yearly(rule: ScheduleRule, target: datetime.date) -> bool:
+    """YEARLY evaluation with the documented leap-day clamp policy.
+
+    Policy: in each interval-eligible year, the rule fires on
+    ``day_of_month`` of ``month`` when that day exists.  A YEARLY rule
+    for February 29th fires on FEBRUARY 28TH in a non-leap year (the
+    same clamp-as-month-end policy as MONTHLY_DAY: the annual chore
+    still happens every year, pinned to February's end when its
+    nominal date is absent) rather than silently vanishing.  A YEARLY
+    rule with NO day_of_month fires on the first day of ``month``.
+    The interval anchors on years elapsed from start_date's year.
+    """
+    anchor = _parse_date(rule.start_date)
+    years_elapsed = target.year - anchor.year
+    if years_elapsed % rule.interval != 0:
+        return False
+    if target.month != rule.month:
+        return False
+    if rule.day_of_month is None:
+        # Day-less yearly rules fire on the first of the month.
+        return target.day == 1
+    month_end = _month_end(target.year, target.month)
+    return target.day == min(rule.day_of_month, month_end)
+
+
+def occurs_on(rule: ScheduleRule, target: datetime.date) -> bool:
+    """Return True when ``rule`` fires on ``target``.
+
+    Pure function: no I/O, no timezone awareness, no mutation.  Dates
+    are plain calendar dates (Feature 04 guardrail); the caller decides
+    the timezone that produced them.
+    """
+    # Dispatch FIRST: an unimplemented shape must raise loudly even for
+    # dates outside the window, never silently evaluate to False.
+    shape = rule.rule_type
+    if shape not in (
+        RuleType.DAILY, RuleType.WEEKLY, RuleType.CUSTOM_DAYS,
+        RuleType.MONTHLY_DAY, RuleType.MONTHLY_WEEKDAY, RuleType.YEARLY,
+    ):
+        raise NotImplementedError(
+            f"occurs_on for {shape} lands with its own engine task "
+            "(Features 04 task sequence)"
+        )
+    if not _within_window(rule, target):
+        return False
+    if shape is RuleType.DAILY:
+        offset = (target - _parse_date(rule.start_date)).days
+        return offset % rule.interval == 0
+    if shape is RuleType.MONTHLY_DAY:
+        return _occurs_monthly_day(rule, target)
+    if shape is RuleType.MONTHLY_WEEKDAY:
+        return _occurs_monthly_weekday(rule, target)
+    if shape is RuleType.YEARLY:
+        return _occurs_yearly(rule, target)
+    # WEEKLY / CUSTOM_DAYS: whole-week anchoring from start_date, never
+    # ISO week numbers (Feature 04/05 guardrail: 53-week years flip ISO
+    # parity on January 1st; anchor arithmetic does not).
+    anchor = _parse_date(rule.start_date)
+    if shape is RuleType.CUSTOM_DAYS:
+        interval = 1
+    else:
+        interval = rule.interval
+    week_offset = (target - anchor).days // 7
+    if week_offset % interval != 0:
+        return False
+    return target.weekday() in rule.weekday_set
+
+
+def occurrences_between(
+    rule: ScheduleRule, start: str, end: str
+) -> list[str]:
+    """Return every date string in [start, end] the rule fires on.
+
+    Inclusive on both ends.  Raises RuleValidationError on malformed or
+    inverted bounds; a window entirely outside [rule.start_date,
+    rule.end_date] yields an empty list.
+    """
+    if rule.rule_type not in (
+        RuleType.DAILY, RuleType.WEEKLY, RuleType.CUSTOM_DAYS,
+        RuleType.MONTHLY_DAY, RuleType.MONTHLY_WEEKDAY, RuleType.YEARLY,
+    ):
+        raise NotImplementedError(
+            f"occurrences_between for {rule.rule_type} lands with its own "
+            "engine task (Features 04 task sequence)"
+        )
+    _validate_date(start, "start")
+    _validate_date(end, "end")
+    start_date = _parse_date(start)
+    end_date = _parse_date(end)
+    if end_date < start_date:
+        raise RuleValidationError(
+            f"end {end!r} must be on or after start {start!r}"
+        )
+    rule_start = _parse_date(rule.start_date)
+    if end_date < rule_start:
+        return []
+    # Clamp the walk to the rule's own window and the queried range.
+    effective_start = max(start_date, rule_start)
+    effective_end = end_date
+    if rule.end_date is not None:
+        effective_end = min(effective_end, _parse_date(rule.end_date))
+    results: list[str] = []
+    cursor = effective_start
+    while cursor <= effective_end:
+        if occurs_on(rule, cursor):
+            results.append(cursor.isoformat())
+        cursor += datetime.timedelta(days=1)
+    return results

pyproject.toml

@@ -1,6 +1,6 @@
 [project]
 name = "nestquest"
-version = "0.1.0"
+version = "0.3.0"
 description = "NestQuest Home Assistant Integration"
 readme = "README.md"
 requires-python = ">=3.11"

tests/conftest.py

@@ -41,6 +41,7 @@ _HA_MODULES = (
     "homeassistant.core",
     "homeassistant.config_entries",
     "homeassistant.data_entry_flow",
+    "homeassistant.exceptions",
 )
 
 def _find_spec(name: str):
@@ -77,6 +78,7 @@ for _parent, _child in (
     ("homeassistant", "config_entries"),
     ("homeassistant", "data_entry_flow"),
     ("homeassistant", "core"),
+    ("homeassistant", "exceptions"),
 ):
     setattr(sys.modules[_parent], _child, sys.modules[f"{_parent}.{_child}"])
 
@@ -158,6 +160,7 @@ def _callback_decorator(fn):
 _config_entries_mock = _ha_mock("homeassistant.config_entries")
 _core_mock = _ha_mock("homeassistant.core")
 _data_entry_flow_mock = _ha_mock("homeassistant.data_entry_flow")
+_exceptions_mock = _ha_mock("homeassistant.exceptions")
 
 _config_entries_mock.ConfigFlow = _ConfigFlowBase
 _config_entries_mock.OptionsFlowWithConfigEntry = _OptionsFlowWithConfigEntryBase
@@ -168,6 +171,17 @@ _data_entry_flow_mock.RESULT_TYPE_ABORT = "abort"
 _data_entry_flow_mock.FlowResult = dict
 
 
+class ConfigEntryNotReady(Exception):
+    """Stand-in mirroring homeassistant.exceptions.ConfigEntryNotReady.
+
+    HA retries setup when this is raised; the tests assert the corrupt
+    database path raises it instead of crashing with an unrelated error.
+    """
+
+
+_exceptions_mock.ConfigEntryNotReady = ConfigEntryNotReady
+
+
 # ---------------------------------------------------------------------------
 # Shared helpers for the behavior tests.
 # ---------------------------------------------------------------------------

tests/test_dao_children.py

@@ -481,12 +481,22 @@ def test_children_and_admin_sql_lives_only_in_dao_module() -> None:
         "custom_components/nestquest/dao_rules.py",  # validates child
         # activeness on assignment (FK to children, done-condition
         # 'validates that the assigned child is active')
+        "custom_components/nestquest/dao_presence.py",  # validates the
+        # schedule/override child exists before writing (same FK shape
+        # as dao_rules: one existence SELECT per write)
+        "custom_components/nestquest/dao_instances.py",  # validates the
+        # instance/event child exists before writing (same FK shape)
         "custom_components/nestquest/schema.py",  # declares the DDL
         "custom_components/nestquest/migrations.py",  # applies the DDL
         "tests/test_schema.py",  # tests the DDL
         "tests/test_migrations.py",  # tests migration application
         "tests/test_dao_children.py",  # this file, scanned separately
         "tests/test_dao_rules.py",  # rules/definitions guard, own scope
+        "tests/test_dao_presence.py",  # presence guard, own scope
+        "tests/test_dao_instances.py",  # instances guard, own scope
+        "tests/test_startup_db.py",  # row-count probe of the children
+        # table only (proving a fresh setup loads); guard below covers
+        # everything else
     }
     sql_pattern = re.compile(
         r"(FROM|INTO|UPDATE|DELETE\s+FROM|JOIN)\s+"
@@ -494,18 +504,329 @@ def test_children_and_admin_sql_lives_only_in_dao_module() -> None:
         re.IGNORECASE,
     )
     offenders: list[str] = []
+    # test_dao_matrix.py is exempted for exactly its two sanctioned
+    # raw constraint-violation probes (NOT NULL on children, PRIMARY
+    # KEY on admin_users) — the Feature-02 closing suite must prove
+    # the schema surfaces violations to the DAO layer; its guard below
+    # covers everything else in that file.
+    probe_names = {
+        "test_matrix_children_crud_and_constraints",
+        "test_matrix_admin_users_crud_and_constraints",
+    }
     for root in scan_roots:
         for py in sorted(root.rglob("*.py")):
             relative = py.relative_to(repo_root).as_posix()
             if relative in allowed:
                 continue
-            if sql_pattern.search(py.read_text()):
+            text = py.read_text()
+            if relative == "tests/test_dao_matrix.py":
+                text = _strip_function_spans(text, probe_names)
+            if sql_pattern.search(text):
                 offenders.append(relative)
     assert offenders == [], (
         f"SQL touching children/admin_users leaked into: {offenders}"
     )
 
 
+def _strip_function_spans(text: str, names: set[str]) -> str:
+    """Return ``text`` minus the source spans of the named functions."""
+    import ast
+
+    lines = text.splitlines(keepends=True)
+    excluded: set[int] = set()
+    tree = ast.parse(text)
+    for node in ast.walk(tree):
+        if (
+            isinstance(node, (ast.FunctionDef, ast.AsyncFunctionDef))
+            and node.name in names
+        ):
+            excluded.update(
+                range(node.lineno, (node.end_lineno or node.lineno) + 1)
+            )
+    return "".join(
+        line
+        for number, line in enumerate(lines, start=1)
+        if number not in excluded
+    )
+
+
+def test_startup_db_test_file_uses_dao_elsewhere() -> None:
+    """Compensating self-scan for the startup-test exemption: outside
+    the single sanctioned COUNT(*) probe, test_startup_db.py must go
+    through the DAO for children/admin_users data access.
+    """
+    import re
+    from pathlib import Path
+
+    startup_test = Path(__file__).parent / "test_startup_db.py"
+    stripped = _strip_function_spans(
+        startup_test.read_text(),
+        {"test_setup_with_valid_file_preserves_rows"},
+    )
+    sql_pattern = re.compile(
+        r"(SELECT\s[^\"']*?FROM|INSERT\s+INTO|UPDATE|DELETE\s+FROM|"
+        r"FROM|JOIN)\s+[`'\"]*(\[)?(children|admin_users)\b",
+        re.IGNORECASE,
+    )
+    assert sql_pattern.search(stripped) is None, (
+        "test_startup_db.py must go through the DAO for these tables "
+        "outside its sanctioned row-count probe"
+    )
+
+
+def test_dao_matrix_test_file_raw_probes_are_only_constraint_probes() -> None:
+    """Compensating self-scan for the matrix-file exemption, structurally
+    exact: parse the two sanctioned functions' ASTs and require that
+    (a) they contain NO other raw SQL naming children/admin_users, and
+    (b) every raw table insert among them is an expression nested inside
+    a ``pytest.raises`` context whose matcher mentions IntegrityError.
+    Any additional raw write elsewhere in the file fails this guard.
+    """
+    import ast
+    import re
+    from pathlib import Path
+
+    matrix = Path(__file__).parent / "test_dao_matrix.py"
+    text = matrix.read_text()
+    stripped = _strip_function_spans(
+        text,
+        {
+            "test_matrix_children_crud_and_constraints",
+            "test_matrix_admin_users_crud_and_constraints",
+        },
+    )
+    sql_pattern = re.compile(
+        r"(SELECT\s[^\"']*?FROM|INSERT\s+INTO|UPDATE|DELETE\s+FROM|"
+        r"FROM|JOIN)\s+[`'\"]*(\[)?(children|admin_users)\b",
+        re.IGNORECASE,
+    )
+    assert sql_pattern.search(stripped) is None, (
+        "test_dao_matrix.py raw children/admin_users SQL is limited to "
+        "the two sanctioned constraint probes"
+    )
+
+    tree = ast.parse(text)
+    probe_tables = {
+        "test_matrix_children_crud_and_constraints": ("children", "INSERT"),
+        "test_matrix_admin_users_crud_and_constraints": (
+            "admin_users",
+            "INSERT",
+        ),
+    }
+    any_table = re.compile(
+        r"\b(children|admin_users)\b", re.IGNORECASE
+    )
+    allowed_stmt = re.compile(
+        r"^(INSERT|SELECT)", re.IGNORECASE
+    )
+    #: The sanctioned probe per function: exactly one raw statement
+    #: INSERTing that table, inside a pytest.raises whose matcher is
+    #: IntegrityError (checked as an AST Name/Attribute chain).
+    probes_per_function: dict[str, int] = {name: 0 for name in probe_tables}
+    for node in ast.walk(tree):
+        if (
+            not isinstance(node, (ast.FunctionDef, ast.AsyncFunctionDef))
+            or node.name not in probe_tables
+        ):
+            continue
+        table, expected_verb = probe_tables[node.name]
+
+        def _unquote(rendered: str) -> str:
+            rendered = rendered.strip()
+            if len(rendered) >= 2 and rendered[0] == rendered[-1] and (
+                rendered[0] in "'\""
+            ):
+                return rendered[1:-1]
+            return rendered
+
+        # Collect EVERY database SQL entry point (execute, fetch_one,
+        # fetch_all, execute_many) wherever it appears in the awaited
+        # expression tree: a bypass through fetch_one/execute_many OR a
+        # call wrapped in another awaitable (asyncio.gather, a helper
+        # coroutine, a Task) must be caught the same as a bare await.
+        raw_statements: list[tuple[ast.AST, str]] = []
+        for sub in ast.walk(node):
+            if (
+                isinstance(sub, ast.Call)
+                and isinstance(sub.value if False else sub.func, ast.Attribute)
+                and sub.func.attr
+                in {"execute", "fetch_one", "fetch_all", "execute_many"}
+            ):
+                sql_arg = sub.args[0] if sub.args else None
+                rendered = ast.unparse(sql_arg) if sql_arg else ""
+                raw_statements.append((sub, _unquote(rendered)))
+
+        def _raises_blocks():
+            """Yield (block, is_pytest_integrity) for pytest.raises blocks.
+
+            Structurally exact: the call func must resolve to
+            pytest.raises — Attribute(pytest, raises) or a bare Name
+            whose id is bound to pytest via the file's own import
+            section (validated below).  The matcher argument must be an
+            AST chain whose final segment is exactly IntegrityError (so
+            NotIntegrityError and fake.IntegrityError fail).
+            """
+            # Names bound to pytest's raises: parse the FILE's actual
+            # Import nodes in TWO passes so order cannot matter — pass
+            # one collects every binding pytest's raises gets (whatever
+            # alias), pass two rejects ANY competing binding of those
+            # names (or of 'pytest' itself) from a non-pytest import,
+            # wherever it appears.  A spoof imported BEFORE the genuine
+            # binding therefore still fails.
+            pytest_raises_aliases: set[str] = set()
+            for file_node in ast.walk(tree):
+                if (
+                    isinstance(file_node, ast.ImportFrom)
+                    and file_node.module == "pytest"
+                ):
+                    for alias in file_node.names:
+                        if alias.name == "raises":
+                            pytest_raises_aliases.add(
+                                alias.asname or alias.name
+                            )
+            raises_bindings: set[str] = set(pytest_raises_aliases)
+            for file_node in ast.walk(tree):
+                if isinstance(file_node, ast.ImportFrom):
+                    if file_node.module == "pytest":
+                        continue
+                    for alias in file_node.names:
+                        bound = alias.asname or alias.name
+                        if bound in raises_bindings | {"pytest"}:
+                            raise AssertionError(
+                                f"shadowed import of {bound!r} from "
+                                f"{file_node.module!r} defeats the "
+                                "pytest.raises probe validation"
+                            )
+                elif isinstance(file_node, ast.Import):
+                    # import fake_module as pytest (or as any raises
+                    # alias) fails closed.  A plain `import pytest`
+                    # (alias.name == 'pytest', no asname) is the genuine
+                    # module binding and is allowed.
+                    for alias in file_node.names:
+                        bound = alias.asname or alias.name
+                        genuine_pytest = (
+                            alias.name == "pytest" and alias.asname is None
+                        )
+                        if bound in raises_bindings | {"pytest"} and (
+                            not genuine_pytest
+                        ):
+                            raise AssertionError(
+                                f"import shadowing {bound!r} defeats the "
+                                "pytest.raises probe validation"
+                            )
+            for sub in ast.walk(node):
+                if not (
+                    isinstance(sub, ast.With)
+                    and sub.items
+                    and isinstance(sub.items[0].context_expr, ast.Call)
+                ):
+                    continue
+                call = sub.items[0].context_expr
+                func = call.func
+                if isinstance(func, ast.Attribute):
+                    if (
+                        func.attr != "raises"
+                        or not isinstance(func.value, ast.Name)
+                        or func.value.id != "pytest"
+                    ):
+                        continue
+                elif isinstance(func, ast.Name):
+                    if func.id not in raises_bindings:
+                        continue
+                else:
+                    continue
+                matcher_ok = False
+                if call.args:
+                    matcher = call.args[0]
+                    matcher_ok = _is_integrity_error_node(matcher)
+                yield sub, matcher_ok
+
+        def _is_integrity_error_node(matcher) -> bool:
+            """True for an AST chain of Name/Attribute ending exactly at
+            IntegrityError: every Attribute base must itself be a Name
+            or Attribute (a Call base like __import__('x') fails)."""
+            node = matcher
+            while isinstance(node, ast.Attribute):
+                node = node.value
+            if not isinstance(node, ast.Name):
+                return False
+            final = matcher
+            while isinstance(final, ast.Attribute):
+                final = final.attr
+            return (
+                isinstance(final, str)
+                and ast.unparse(matcher).split(".")[-1] == "IntegrityError"
+            )
+
+        # INSERT OR variant / quoted / qualified / schema-qualified
+        # names: normalize the SQL so ANY spelling of an insert into
+        # the table counts as a table insert and requires the raises
+        # block.  Strip quotes/brackets/backticks from identifiers and
+        # drop any schema qualifier, whatever it is.
+        def _is_insert_into_table(rendered: str, table: str) -> bool:
+            insert_match = re.search(
+                r"\bINSERT(\s+OR\s+\w+)?\s+INTO\s+(.+)",
+                rendered,
+                flags=re.IGNORECASE | re.DOTALL,
+            )
+            if not insert_match:
+                return False
+            target = insert_match.group(2)
+            # Tokenize the target's leading identifier: strip quotes
+            # and brackets, take the LAST dot-separated segment,
+            # casefolded so 'CHILDREN' cannot bypass the lowercase
+            # table name the scan looks for.
+            identifier = re.split(r"[\s(]", target, maxsplit=1)[0]
+            segments = re.split(r"\.", identifier)
+            last = segments[-1].strip("`'\"[]")
+            return last.casefold() == table.casefold()
+
+        # In a sanctioned function, EVERY raw statement naming either
+        # table must be exactly that function's one expected-table
+        # constraint insert — an admin_users insert inside the children
+        # function (or vice versa) is a bypass attempt and fails here,
+        # as does any second insert of the expected table.
+        for stmt_node, rendered in raw_statements:
+            if not any_table.search(rendered):
+                continue
+            assert _is_insert_into_table(rendered, table), (
+                f"{node.name}: raw statement naming the other table is "
+                f"forbidden; only the {table} probe is sanctioned: "
+                f"{rendered[:80]!r}"
+            )
+            # This is the sanctioned probe: require pytest.raises
+            # with an exact IntegrityError matcher around it.
+            inside = any(
+                block.lineno <= stmt_node.lineno
+                and stmt_node.end_lineno <= block.end_lineno
+                and matcher_ok
+                for block, matcher_ok in _raises_blocks()
+            )
+            assert inside, (
+                f"the raw {table} insert in {node.name} must be nested "
+                "in a pytest.raises(...IntegrityError...) block "
+                "(expected-to-fail constraint probe, not a write)"
+            )
+            probes_per_function[node.name] += 1
+        # UPDATE/DELETE naming the tables anywhere in a sanctioned
+        # function is forbidden outright.
+        for _, rendered in raw_statements:
+            if any_table.search(rendered) and re.match(
+                r"(UPDATE|DELETE)", rendered.strip(), re.IGNORECASE
+            ):
+                raise AssertionError(
+                    f"{node.name} must not hold UPDATE/DELETE SQL for "
+                    f"children/admin_users: {rendered[:80]!r}"
+                )
+    assert all(
+        count == 1 for count in probes_per_function.values()
+    ) and len(probes_per_function) == 2, (
+        "each sanctioned function must hold exactly one raw constraint "
+        f"probe; found {probes_per_function}"
+    )
+
+
+
 def test_dao_test_file_uses_dao_not_raw_table_sql() -> None:
     """This test module must exercise the DAO, not raw SQL, for these
     tables.  The two guard functions in this file necessarily mention
@@ -528,6 +849,7 @@ def test_dao_test_file_uses_dao_not_raw_table_sql() -> None:
     guard_names = {
         "test_children_and_admin_sql_lives_only_in_dao_module",
         "test_dao_test_file_uses_dao_not_raw_table_sql",
+        "test_dao_matrix_test_file_raw_probes_are_only_constraint_probes",
     }
     excluded: set[int] = set()
     for node in ast.walk(tree):

tests/test_dao_matrix.py

@@ -0,0 +1,423 @@
+"""Cross-table DAO unit suite: CRUD and constraint coverage for all tables.
+
+The per-DAO test modules (test_dao_children, test_dao_rules,
+test_dao_presence, test_dao_instances) cover each table's operations in
+depth; this module is the FEATURE-02 closing sweep: a single systematic
+matrix proving that for EVERY table, the DAO layer supports insert,
+read, update and surfaces constraint violations — plus the instance
+upsert idempotency and the completion_events no-mutation guarantee —
+all against a temporary database file from a clean checkout.
+
+No raw SQL touches the DAO-owned tables here except the sanctioned
+COUNT(*) probes (each guarded by the per-DAO leak guards); everything
+else goes through the typed DAO methods.
+"""
+from __future__ import annotations
+
+import asyncio
+import datetime
+import sqlite3
+from unittest.mock import AsyncMock, MagicMock
+
+import pytest
+
+from custom_components.nestquest.dao_children import (
+    AdminUsersDao,
+    ChildrenDao,
+)
+from custom_components.nestquest.dao_instances import (
+    CompletionEventsDao,
+    TaskInstancesDao,
+)
+from custom_components.nestquest.dao_presence import (
+    PresenceOverridesDao,
+    PresenceSchedulesDao,
+)
+from custom_components.nestquest.dao_rules import (
+    ScheduleRulesDao,
+    TaskDefinitionsDao,
+)
+from custom_components.nestquest.db import NestQuestDatabase
+from custom_components.nestquest.migrations import apply_migrations
+
+
+def _run(coro):
+    return asyncio.new_event_loop().run_until_complete(coro)
+
+
+def _make_hass_mock():
+    hass = MagicMock()
+    hass.async_add_executor_job = AsyncMock(side_effect=(lambda fn, *a: fn(*a)))
+    return hass
+
+
+def _future_day(offset: int) -> str:
+    return (
+        datetime.date.today() + datetime.timedelta(days=offset)
+    ).isoformat()
+
+
+def _stamp() -> str:
+    return f"{datetime.date.today().isoformat()}T08:00:00+00:00"
+
+
+D1 = _future_day(1)
+D2 = _future_day(2)
+D20 = _future_day(20)
+
+#: Every table the v1 schema owns, with the DAO that governs it.
+ALL_TABLES = (
+    "children",
+    "admin_users",
+    "schedule_rules",
+    "task_definitions",
+    "presence_schedules",
+    "presence_overrides",
+    "task_instances",
+    "completion_events",
+)
+
+
+class World:
+    """All DAOs over one migrated temporary database."""
+
+    def __init__(self, database: NestQuestDatabase):
+        self.database = database
+        self.children = ChildrenDao(database)
+        self.admins = AdminUsersDao(database)
+        self.rules = ScheduleRulesDao(database)
+        self.definitions = TaskDefinitionsDao(database)
+        self.schedules = PresenceSchedulesDao(database)
+        self.overrides = PresenceOverridesDao(database)
+        self.instances = TaskInstancesDao(database)
+        self.events = CompletionEventsDao(database)
+
+    async def seed(self) -> None:
+        """Populate one valid fixture chain through the DAO layer."""
+        self.child = await self.children.create("Ada", _stamp())
+        self.child2 = await self.children.create("Bo", _stamp())
+        assert await self.admins.add("user-1", _stamp()) is True
+        self.rule = await self.rules.create("daily", D1)
+        self.definition = await self.definitions.create(
+            "Brush teeth", self.child.id, self.rule.id, _stamp()
+        )
+        self.schedule = await self.schedules.upsert_by_child(
+            self.child.id, 2, D1, "0,2,4|1,3"
+        )
+        self.override = await self.overrides.create(
+            self.child.id, D1, D2, True, note="test"
+        )
+        self.instance = await self.instances.upsert(
+            self.definition.id, self.child.id, D1, _stamp()
+        )
+
+
+async def _world(path) -> World:
+    database = NestQuestDatabase(_make_hass_mock())
+    await database.open(path)
+    await apply_migrations(database)
+    world = World(database)
+    await world.seed()
+    return world
+
+
+def _with_world(name):
+    def _run_test(body):
+        async def _main():
+            world = await _world(name)
+            try:
+                return await body(world)
+            finally:
+                await world.database.close()
+
+        return _run(_main())
+
+    return _run_test
+
+
+# ---------------------------------------------------------------------------
+# The matrix: every table gets insert / read / update / constraint check
+# ---------------------------------------------------------------------------
+
+
+def test_matrix_children_crud_and_constraints(tmp_path) -> None:
+    async def _body(w: World):
+        # insert (seeded) + read
+        fetched = await w.children.get(w.child.id)
+        assert fetched is not None and fetched.display_name == "Ada"
+        # update
+        await w.children.update(w.child.id, display_name="Ada L.")
+        assert (await w.children.get(w.child.id)).display_name == "Ada L."
+        # constraint: NOT NULL display_name
+        with pytest.raises(sqlite3.IntegrityError):
+            await w.database.execute(
+                "INSERT INTO children (display_name, created_at) "
+                "VALUES (NULL, ?)",
+                (_stamp(),),
+            )
+        return True
+
+    assert _with_world(tmp_path / "m-children.db")(_body) is True
+
+
+def test_matrix_admin_users_crud_and_constraints(tmp_path) -> None:
+    async def _body(w: World):
+        # insert + read
+        assert await w.admins.exists("user-1") is True
+        records = await w.admins.list()
+        assert len(records) == 1
+        # update path: idempotent add is the allowlist's update semantics
+        assert await w.admins.add("user-1", _stamp()) is False
+        # constraint: ha_user_id PRIMARY KEY
+        with pytest.raises(sqlite3.IntegrityError):
+            await w.database.execute(
+                "INSERT INTO admin_users (ha_user_id, added_at) "
+                "VALUES (?, ?)",
+                ("user-1", _stamp()),
+            )
+        # remove
+        assert await w.admins.remove("user-1") is True
+        assert await w.admins.exists("user-1") is False
+        return True
+
+    assert _with_world(tmp_path / "m-admins.db")(_body) is True
+
+
+def test_matrix_schedule_rules_crud_and_constraints(tmp_path) -> None:
+    async def _body(w: World):
+        # insert (seeded) + read
+        fetched = await w.rules.get(w.rule.id)
+        assert fetched.rule_type == "daily"
+        # update
+        await w.rules.update(w.rule.id, interval=3)
+        assert (await w.rules.get(w.rule.id)).interval == 3
+        # constraint: rule_type CHECK
+        with pytest.raises(sqlite3.IntegrityError):
+            await w.rules.create("sometimes", D1)
+        # delete-if-unreferenced: referenced -> rejected
+        assert await w.rules.delete_if_unreferenced(w.rule.id) is False
+        return True
+
+    assert _with_world(tmp_path / "m-rules.db")(_body) is True
+
+
+def test_matrix_task_definitions_crud_and_constraints(tmp_path) -> None:
+    async def _body(w: World):
+        # insert (seeded) + read
+        fetched = await w.definitions.get(w.definition.id)
+        assert fetched.title == "Brush teeth"
+        # update
+        await w.definitions.update(w.definition.id, title="Brush TEETH")
+        assert (await w.definitions.get(w.definition.id)).title == (
+            "Brush TEETH"
+        )
+        # constraint: FK to a nonexistent child rejected with ValueError
+        with pytest.raises(ValueError, match="does not exist"):
+            await w.definitions.create("X", 999, w.rule.id, _stamp())
+        # constraint: inactive child rejected
+        await w.children.set_active(w.child2.id, False)
+        with pytest.raises(ValueError, match="inactive"):
+            await w.definitions.set_assignee(w.definition.id, w.child2.id)
+        # set_active: deactivate, never delete
+        assert await w.definitions.set_active(w.definition.id, False) == 1
+        assert (await w.definitions.get(w.definition.id)).is_active is False
+        return True
+
+    assert _with_world(tmp_path / "m-definitions.db")(_body) is True
+
+
+def test_matrix_presence_schedules_crud_and_constraints(tmp_path) -> None:
+    async def _body(w: World):
+        # insert (seeded) + read
+        fetched = await w.schedules.get_by_child(w.child.id)
+        assert fetched.pattern == "0,2,4|1,3"
+        # update path: upsert replaces in place (same row id)
+        replaced = await w.schedules.upsert_by_child(
+            w.child.id, 2, D2, "1,3|0,2,4"
+        )
+        assert replaced.id == fetched.id
+        assert (await w.schedules.get_by_child(w.child.id)).pattern == (
+            "1,3|0,2,4"
+        )
+        # constraint: malformed pattern CHECK
+        with pytest.raises(sqlite3.IntegrityError):
+            await w.schedules.upsert_by_child(w.child.id, 2, D1, "0,2")
+        # delete: row gone, child present every day
+        assert await w.schedules.delete(w.child.id) is True
+        assert await w.schedules.get_by_child(w.child.id) is None
+        return True
+
+    assert _with_world(tmp_path / "m-schedules.db")(_body) is True
+
+
+def test_matrix_presence_overrides_crud_and_constraints(tmp_path) -> None:
+    async def _body(w: World):
+        # insert (seeded) + read
+        found = await w.overrides.list_by_child_and_range(
+            w.child.id, D1, D2
+        )
+        assert [r.id for r in found] == [w.override.id]
+        # update path: delete + recreate (overrides are append-oriented)
+        assert await w.overrides.delete(w.override.id) is True
+        replacement = await w.overrides.create(
+            w.child.id, D1, D2, False, note="updated"
+        )
+        assert replacement.is_present is False
+        # constraint: end before start CHECK
+        with pytest.raises(sqlite3.IntegrityError):
+            await w.overrides.create(w.child.id, D2, D1, True)
+        return True
+
+    assert _with_world(tmp_path / "m-overrides.db")(_body) is True
+
+
+def test_matrix_task_instances_crud_and_constraints(tmp_path) -> None:
+    async def _body(w: World):
+        # insert (seeded) + read
+        fetched = await w.instances.get(w.definition.id, D1)
+        assert fetched == w.instance
+        # update path: conflict refresh on the OPEN instance — same row
+        # id, snapshot columns (due_time) actually rewritten.
+        refreshed = await w.instances.upsert(
+            w.definition.id, w.child.id, D1, _stamp(), due_time="17:00"
+        )
+        assert refreshed.id == fetched.id, (
+            "the conflict path must refresh the existing row in place"
+        )
+        assert refreshed.due_time == "17:00"
+        assert fetched.due_time is None
+        assert await w.instances.get_by_id(fetched.id) is not None
+        return True
+
+    assert _with_world(tmp_path / "m-instances.db")(_body) is True
+
+
+def test_matrix_task_instances_constraints(tmp_path) -> None:
+    async def _body(w: World):
+        # D20 has no instance yet: a raw insert succeeds, then an
+        # identical second insert must fail on the UNIQUE constraint —
+        # proving the schema backs the DAO's idempotency.
+        await w.database.execute(
+            "INSERT INTO task_instances (definition_id, child_id, "
+            "due_date, due_time, generated_at) "
+            "VALUES (?, ?, ?, NULL, ?)",
+            (w.definition.id, w.child.id, D20, _stamp()),
+        )
+        with pytest.raises(sqlite3.IntegrityError):
+            await w.database.execute(
+                "INSERT INTO task_instances (definition_id, child_id, "
+                "due_date, due_time, generated_at) "
+                "VALUES (?, ?, ?, NULL, ?)",
+                (w.definition.id, w.child.id, D20, _stamp()),
+            )
+        # delete future uncompleted removes D20's open instance
+        deleted = await w.instances.delete_future_uncompleted(
+            w.definition.id, D20
+        )
+        assert deleted == 1
+        return True
+
+    assert _with_world(tmp_path / "m-instances-constraints.db")(_body) is True
+
+
+def test_matrix_task_instances_upsert_idempotency(tmp_path) -> None:
+    async def _body(w: World):
+        first = await w.instances.upsert(
+            w.definition.id, w.child.id, D2, _stamp()
+        )
+        second = await w.instances.upsert(
+            w.definition.id, w.child.id, D2, _stamp(), due_time="17:00"
+        )
+        assert second.id == first.id
+        count = await w.database.fetch_one(
+            "SELECT COUNT(*) FROM task_instances"
+        )
+        # Seed created one (D1) plus this one (D2) — no duplicate.
+        assert count == (2,)
+        return True
+
+    assert _with_world(tmp_path / "m-upsert-idempotent.db")(_body) is True
+
+
+# ---------------------------------------------------------------------------
+# completion_events: append-only by construction
+# ---------------------------------------------------------------------------
+
+
+def test_matrix_completion_events_append_read_and_no_mutation(
+    tmp_path,
+) -> None:
+    async def _body(w: World):
+        # insert: append a completion then a reversal, then re-complete
+        await w.events.append(
+            w.instance.id, w.child.id, "completed", "panel", _stamp(), True
+        )
+        await w.events.append(
+            w.instance.id, w.child.id, "uncompleted", "user", _stamp(), None,
+            actor_user_id="user-1",
+        )
+        await w.events.append(
+            w.instance.id, w.child.id, "completed", "panel", _stamp(), False
+        )
+        # read: full ordered history + latest
+        history = await w.events.list_by_instance(w.instance.id)
+        assert [e.event_type for e in history] == [
+            "completed",
+            "uncompleted",
+            "completed",
+        ]
+        latest = await w.events.get_latest_for_instance(w.instance.id)
+        assert latest.event_type == "completed"
+        assert latest.was_on_time is False
+        # constraint: unknown instance FK + bad actor pair surface as
+        # ValueError from the DAO's atomic validation
+        with pytest.raises(ValueError, match="does not exist"):
+            await w.events.append(
+                999, w.child.id, "completed", "user", _stamp(), True,
+                actor_user_id="user-1",
+            )
+        return history
+
+    assert _with_world(tmp_path / "m-events.db")(_body) is not None
+
+
+def test_completion_events_absolutely_no_mutation_path() -> None:
+    """The structural half of the done-condition: the DAO class exposes
+    no update/delete, AND no module in the package holds mutation SQL
+    for the table (already asserted package-wide in test_dao_instances;
+    re-asserted here so THIS suite stands alone in a clean checkout).
+    """
+    import inspect
+    import re
+    from pathlib import Path
+
+    methods = {
+        name
+        for name, _ in inspect.getmembers(
+            CompletionEventsDao, inspect.isfunction
+        )
+        if not name.startswith("__")
+    }
+    assert methods == {
+        "append",
+        "list_by_instance",
+        "list_by_child_and_date_range",
+        "get_latest_for_instance",
+    }
+
+    package = Path(
+        __import__(
+            "custom_components.nestquest", fromlist=["__file__"]
+        ).__file__
+    ).parent
+    mutation = re.compile(
+        r"(UPDATE(\s+OR\s+\w+)?|DELETE\s+FROM)\s+"
+        r"((main|temp)\s*\.\s*)?[`'\"]*(\[)?completion_events\b",
+        re.IGNORECASE,
+    )
+    offenders = [
+        py.name
+        for py in sorted(package.glob("*.py"))
+        if py.name != "__pycache__" and mutation.search(py.read_text())
+    ]
+    assert offenders == []

tests/test_dao_presence.py

@@ -0,0 +1,622 @@
+"""Tests for dao_presence.py: presence schedules and overrides DAO."""
+from __future__ import annotations
+
+import asyncio
+import sqlite3
+
+import pytest
+
+from custom_components.nestquest.dao_children import ChildrenDao
+from custom_components.nestquest.dao_presence import (
+    PresenceOverrideRecord,
+    PresenceOverridesDao,
+    PresenceScheduleRecord,
+    PresenceSchedulesDao,
+)
+from custom_components.nestquest.db import NestQuestDatabase
+from custom_components.nestquest.migrations import apply_migrations
+
+
+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) -> tuple:
+    database = NestQuestDatabase(_make_hass_mock())
+    await database.open(path)
+    await apply_migrations(database)
+    schedules = PresenceSchedulesDao(database)
+    overrides = PresenceOverridesDao(database)
+    children = ChildrenDao(database)
+    child = await children.create("Ada", NOW)
+    return database, schedules, overrides, children, child
+
+
+def _with_db(tmp_path, name):
+    def _run_test(body):
+        async def _main():
+            database, schedules, overrides, children, child = await _prepare(
+                tmp_path / name
+            )
+            try:
+                return await body(
+                    database, schedules, overrides, children, child
+                )
+            finally:
+                await database.close()
+
+        return _run(_main())
+
+    return _run_test
+
+
+# ---------------------------------------------------------------------------
+# presence_schedules: upsert by child
+# ---------------------------------------------------------------------------
+
+
+def test_upsert_creates_then_updates_same_row(tmp_path) -> None:
+    """Second upsert for the same child UPDATES, not raises."""
+    async def _body(database, schedules, overrides, children, child):
+        first = await schedules.upsert_by_child(
+            child.id, 2, "2026-09-07", "0,2,4|1,3"
+        )
+        assert isinstance(first, PresenceScheduleRecord)
+        assert first.child_id == child.id
+        second = await schedules.upsert_by_child(
+            child.id, 2, "2026-09-14", "1,3|0,2,4"
+        )
+        assert second.id == first.id, "upsert must reuse the existing row"
+        fetched = await schedules.get_by_child(child.id)
+        assert fetched.anchor_date == "2026-09-14"
+        assert fetched.pattern == "1,3|0,2,4"
+        rows = await database.fetch_one(
+            "SELECT COUNT(*) FROM presence_schedules"
+        )
+        assert rows == (1,)
+        return second
+
+    _with_db(tmp_path, "upsert.db")(_body)
+
+
+def test_upsert_typed_record_fields(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        record = await schedules.upsert_by_child(
+            child.id, 4, "2026-09-07", "0|1|2|3"
+        )
+        assert record.id == 1
+        assert record.child_id == child.id
+        assert record.cycle_length_weeks == 4
+        assert record.anchor_date == "2026-09-07"
+        assert record.pattern == "0|1|2|3"
+        return record
+
+    _with_db(tmp_path, "upsert-fields.db")(_body)
+
+
+def test_upsert_unknown_child_raises_value_error(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        with pytest.raises(ValueError, match="does not exist"):
+            await schedules.upsert_by_child(
+                999, 2, "2026-09-07", "0,2|1,3"
+            )
+        return None
+
+    _with_db(tmp_path, "upsert-bad-child.db")(_body)
+
+
+def test_upsert_malformed_anchor_date_raises(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        for bad in ("not-a-date", "2026-9-7", "2026-13-01", ""):
+            with pytest.raises(ValueError, match="YYYY-MM-DD"):
+                await schedules.upsert_by_child(child.id, 2, bad, "0|1")
+        return None
+
+    _with_db(tmp_path, "upsert-bad-date.db")(_body)
+
+
+def test_upsert_malformed_pattern_rejected_by_schema(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            await schedules.upsert_by_child(
+                child.id, 2, "2026-09-07", "0,2"
+            )
+        return None
+
+    _with_db(tmp_path, "upsert-bad-pattern.db")(_body)
+
+
+def test_upsert_two_children_have_distinct_rows(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        other = await children.create("Bo", NOW)
+        await schedules.upsert_by_child(child.id, 2, "2026-09-07", "0,2|1,3")
+        await schedules.upsert_by_child(other.id, 2, "2026-09-07", "1,3|0,2")
+        mine = await schedules.get_by_child(child.id)
+        theirs = await schedules.get_by_child(other.id)
+        assert mine.id != theirs.id
+        assert mine.pattern == "0,2|1,3"
+        assert theirs.pattern == "1,3|0,2"
+        return mine
+
+    _with_db(tmp_path, "upsert-two-children.db")(_body)
+
+
+def test_upsert_concurrent_same_child_serializes(tmp_path) -> None:
+    """Racing upserts for one child both succeed; exactly one row.
+
+    Deterministic interleaving: a controllable executor gates task A's
+    job AFTER it has acquired the connection lock and begun its
+    transaction, so task B is created while A provably owns the
+    transaction and must queue on the lock.  Both upserts land, and
+    the table holds exactly one row (the ON CONFLICT upsert, not two
+    plain INSERTs, is what avoids a UNIQUE violation).
+    """
+    async def _main():
+        from unittest.mock import MagicMock
+
+        hass = MagicMock()
+        # Gate exactly ONE job: the first _fetch_one issued after
+        # arming, which is task A's child-existence SELECT inside its
+        # transaction.  The transaction's BEGIN runs first and is NOT
+        # gated, so when the gated fetch signals, BEGIN has executed
+        # and SQLite is inside the open transaction (A holds the
+        # connection lock from BEGIN until commit).  Everything before
+        # arming (open, migrations, child create)
+        # and everything after (B's jobs, A's remaining jobs, reads)
+        # pass straight through.
+        armed = {"active": False, "gated": False}
+        gate_open = asyncio.Event()
+        a_started = asyncio.Event()
+
+        async def _gated_executor(fn, *args):
+            # Gate the SECOND gated-window job: the first is the
+            # transaction's BEGIN (submitted and awaited by
+            # transaction() before upsert_by_child's child-SELECT
+            # runs).  Pausing the child-existence SELECT proves the
+            # BEGIN has EXECUTED — SQLite is actually inside the open
+            # transaction, with A holding the connection lock — when
+            # task B gets scheduled and must queue.
+            if armed["active"]:
+                name = getattr(fn, "__name__", "")
+                if name == "_fetch_one" and not armed["gated"]:
+                    armed["gated"] = True
+                    a_started.set()
+                    await gate_open.wait()
+            return fn(*args)
+
+        hass.async_add_executor_job = _gated_executor
+        database = NestQuestDatabase(hass)
+        await database.open(tmp_path / "upsert-race.db")
+        try:
+            await apply_migrations(database)
+            children = ChildrenDao(database)
+            schedules = PresenceSchedulesDao(database)
+            child = await children.create("Ada", NOW)
+
+            armed["active"] = True
+            task_a = asyncio.ensure_future(
+                schedules.upsert_by_child(
+                    child.id, 2, "2026-09-07", "0,2|1,3"
+                )
+            )
+            # Wait until task A's child-existence SELECT (inside its
+            # open transaction, BEGIN already executed) is paused, then
+            # schedule task B so it must queue behind the open
+            # transaction.
+            await a_started.wait()
+            armed["active"] = False
+            task_b = asyncio.ensure_future(
+                schedules.upsert_by_child(
+                    child.id, 2, "2026-09-14", "1,3|0,2"
+                )
+            )
+            await asyncio.sleep(0)
+            # Release A; B's upsert must wait for A's commit and then
+            # land as the ON CONFLICT update path.
+            gate_open.set()
+            record_a, record_b = await asyncio.gather(task_a, task_b)
+            assert record_a.id == record_b.id
+            fetched = await schedules.get_by_child(child.id)
+            assert fetched is not None
+            rows = await database.fetch_one(
+                "SELECT COUNT(*) FROM presence_schedules"
+            )
+            assert rows == (1,)
+            return fetched
+        finally:
+            await database.close()
+
+    fetched = _run(_main())
+    assert fetched.pattern in {"0,2|1,3", "1,3|0,2"}
+
+
+# ---------------------------------------------------------------------------
+# presence_schedules: get / delete
+# ---------------------------------------------------------------------------
+
+
+def test_get_by_child_missing_schedule_returns_none(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        return await schedules.get_by_child(child.id)
+
+    assert _with_db(tmp_path, "get-none.db")(_body) is None
+
+
+def test_get_by_child_unknown_child_raises(tmp_path) -> None:
+    """None must mean ONLY 'no schedule row': an unknown child id is a
+    caller bug, and silently reading a nonexistent child as 'present
+    every day' would be wrong — so get raises instead.
+    """
+    async def _body(database, schedules, overrides, children, child):
+        with pytest.raises(ValueError, match="does not exist"):
+            await schedules.get_by_child(999)
+        return None
+
+    _with_db(tmp_path, "get-unknown.db")(_body)
+
+
+def test_delete_removes_and_returns_true_then_false(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        await schedules.upsert_by_child(child.id, 2, "2026-09-07", "0,2|1,3")
+        assert await schedules.delete(child.id) is True
+        assert await schedules.get_by_child(child.id) is None
+        assert await schedules.delete(child.id) is False
+        return None
+
+    _with_db(tmp_path, "delete.db")(_body)
+
+
+def test_delete_child_without_schedule_returns_false(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        return await schedules.delete(child.id)
+
+    assert _with_db(tmp_path, "delete-none.db")(_body) is False
+
+
+def test_delete_then_upsert_creates_fresh_row(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        first = await schedules.upsert_by_child(
+            child.id, 2, "2026-09-07", "0,2|1,3"
+        )
+        await schedules.delete(child.id)
+        second = await schedules.upsert_by_child(
+            child.id, 2, "2026-09-07", "0,2|1,3"
+        )
+        assert second.id != first.id
+        return second
+
+    _with_db(tmp_path, "delete-recreate.db")(_body)
+
+
+# ---------------------------------------------------------------------------
+# presence_overrides: create / list / delete
+# ---------------------------------------------------------------------------
+
+
+def test_override_create_returns_typed_record(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        record = await overrides.create(
+            child.id, "2026-12-21", "2027-01-04", False,
+            note="Holiday abroad",
+        )
+        assert isinstance(record, PresenceOverrideRecord)
+        assert record.child_id == child.id
+        assert record.start_date == "2026-12-21"
+        assert record.end_date == "2027-01-04"
+        assert record.is_present is False
+        assert record.note == "Holiday abroad"
+        return record
+
+    _with_db(tmp_path, "override-create.db")(_body)
+
+
+def test_override_create_single_day_same_start_and_end(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        record = await overrides.create(
+            child.id, "2026-09-14", "2026-09-14", True
+        )
+        assert record.start_date == record.end_date
+        assert record.is_present is True
+        assert record.note is None
+        return record
+
+    _with_db(tmp_path, "override-single-day.db")(_body)
+
+
+def test_override_create_unknown_child_raises(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        with pytest.raises(ValueError, match="does not exist"):
+            await overrides.create(
+                999, "2026-09-14", "2026-09-14", True
+            )
+        return None
+
+    _with_db(tmp_path, "override-bad-child.db")(_body)
+
+
+def test_override_create_malformed_dates_raise(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        for bad in ("not-a-date", "2026-9-14", ""):
+            with pytest.raises(ValueError, match="YYYY-MM-DD"):
+                await overrides.create(child.id, bad, "2026-09-14", True)
+            with pytest.raises(ValueError, match="YYYY-MM-DD"):
+                await overrides.create(child.id, "2026-09-14", bad, True)
+        return None
+
+    _with_db(tmp_path, "override-bad-dates.db")(_body)
+
+
+def test_override_end_before_start_rejected_by_schema(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            await overrides.create(
+                child.id, "2026-09-14", "2026-09-13", True
+            )
+        return None
+
+    _with_db(tmp_path, "override-end-before-start.db")(_body)
+
+
+def test_override_list_by_child_and_range_filters_correctly(
+    tmp_path,
+) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        await overrides.create(
+            child.id, "2026-09-01", "2026-09-05", False, note="before"
+        )
+        inside = await overrides.create(
+            child.id, "2026-09-10", "2026-09-12", False, note="inside"
+        )
+        overlapping = await overrides.create(
+            child.id, "2026-09-20", "2026-09-25", True, note="overlap"
+        )
+        after = await overrides.create(
+            child.id, "2026-10-01", "2026-10-02", False, note="after"
+        )
+        found = await overrides.list_by_child_and_range(
+            child.id, "2026-09-09", "2026-09-22"
+        )
+        assert [r.id for r in found] == [inside.id, overlapping.id]
+        # Inclusive boundaries: a range touching the override's edges
+        # still matches.
+        edge = await overrides.list_by_child_and_range(
+            child.id, "2026-09-20", "2026-09-20"
+        )
+        assert [r.id for r in edge] == [overlapping.id]
+        return found
+
+    _with_db(tmp_path, "override-list-range.db")(_body)
+
+
+def test_override_list_orders_by_start_date(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        late = await overrides.create(
+            child.id, "2026-09-20", "2026-09-21", True
+        )
+        early = await overrides.create(
+            child.id, "2026-09-10", "2026-09-11", False
+        )
+        found = await overrides.list_by_child_and_range(
+            child.id, "2026-09-01", "2026-09-30"
+        )
+        assert [r.id for r in found] == [early.id, late.id]
+        return found
+
+    _with_db(tmp_path, "override-list-order.db")(_body)
+
+
+def test_override_list_per_child_isolation(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        other = await children.create("Bo", NOW)
+        await overrides.create(child.id, "2026-09-10", "2026-09-11", True)
+        await overrides.create(other.id, "2026-09-10", "2026-09-11", False)
+        mine = await overrides.list_by_child_and_range(
+            child.id, "2026-09-01", "2026-09-30"
+        )
+        assert len(mine) == 1
+        assert mine[0].is_present is True
+        return mine
+
+    _with_db(tmp_path, "override-isolation.db")(_body)
+
+
+def test_override_list_malformed_range_raises(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        with pytest.raises(ValueError, match="YYYY-MM-DD"):
+            await overrides.list_by_child_and_range(
+                child.id, "oops", "2026-09-30"
+            )
+        with pytest.raises(ValueError, match="on or after"):
+            await overrides.list_by_child_and_range(
+                child.id, "2026-09-30", "2026-09-01"
+            )
+        return None
+
+    _with_db(tmp_path, "override-list-bad-range.db")(_body)
+
+
+def test_override_list_unknown_child_returns_empty(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        return await overrides.list_by_child_and_range(
+            999, "2026-09-01", "2026-09-30"
+        )
+
+    assert _with_db(tmp_path, "override-list-unknown.db")(_body) == []
+
+
+def test_override_delete_round_trip(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        record = await overrides.create(
+            child.id, "2026-09-10", "2026-09-11", True
+        )
+        assert await overrides.delete(record.id) is True
+        found = await overrides.list_by_child_and_range(
+            child.id, "2026-09-01", "2026-09-30"
+        )
+        assert found == []
+        assert await overrides.delete(record.id) is False
+        return None
+
+    _with_db(tmp_path, "override-delete.db")(_body)
+
+
+def test_override_delete_missing_returns_false(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        return await overrides.delete(999)
+
+    assert _with_db(tmp_path, "override-delete-missing.db")(_body) is False
+
+
+def test_override_delete_leaves_other_overrides(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        first = await overrides.create(
+            child.id, "2026-09-10", "2026-09-11", True
+        )
+        second = await overrides.create(
+            child.id, "2026-09-20", "2026-09-21", False
+        )
+        assert await overrides.delete(first.id) is True
+        found = await overrides.list_by_child_and_range(
+            child.id, "2026-09-01", "2026-09-30"
+        )
+        assert [r.id for r in found] == [second.id]
+        return found
+
+    _with_db(tmp_path, "override-delete-others.db")(_body)
+
+
+def test_schedule_and_override_independent_for_same_child(tmp_path) -> None:
+    """Deleting the schedule does not touch overrides and vice versa."""
+    async def _body(database, schedules, overrides, children, child):
+        await schedules.upsert_by_child(child.id, 2, "2026-09-07", "0,2|1,3")
+        override = await overrides.create(
+            child.id, "2026-09-10", "2026-09-11", True
+        )
+        assert await schedules.delete(child.id) is True
+        found = await overrides.list_by_child_and_range(
+            child.id, "2026-09-01", "2026-09-30"
+        )
+        assert [r.id for r in found] == [override.id]
+        return found
+
+    _with_db(tmp_path, "independent.db")(_body)
+
+
+# ---------------------------------------------------------------------------
+# No SQL for these tables outside the DAO module
+# ---------------------------------------------------------------------------
+
+
+def test_presence_sql_lives_only_in_dao_module() -> None:
+    """Guardrail: presence_schedules/presence_overrides SQL may appear
+    only in the DAO module (and the schema DDL declarations).
+    RECURSIVE scan of package and tests, FROM/INTO/UPDATE/DELETE FROM/
+    JOIN pattern, with exact repo-relative exclusions justified by
+    role: schema.py declares the DDL, migrations.py applies it, and
+    the test files for exactly those layers verify DDL, not data
+    paths.
+    """
+    import re
+    from pathlib import Path
+
+    package = Path(
+        __import__(
+            "custom_components.nestquest", fromlist=["__file__"]
+        ).__file__
+    ).parent
+    repo_root = package.parent.parent
+    scan_roots = [package, repo_root / "tests"]
+
+    allowed = {
+        "custom_components/nestquest/dao_presence.py",  # this DAO
+        "custom_components/nestquest/schema.py",  # declares the DDL
+        "custom_components/nestquest/migrations.py",  # applies the DDL
+        "tests/test_schema.py",  # tests the DDL
+        "tests/test_migrations.py",  # tests migration application
+        "tests/test_dao_presence.py",  # this file, scanned separately
+    }
+    sql_pattern = re.compile(
+        r"(FROM|INTO|UPDATE|DELETE\s+FROM|JOIN)\s+[`'\"]*(\[)?"
+        r"(presence_schedules|presence_overrides)\b",
+        re.IGNORECASE,
+    )
+    offenders: list[str] = []
+    for root in scan_roots:
+        for py in sorted(root.rglob("*.py")):
+            relative = py.relative_to(repo_root).as_posix()
+            if relative in allowed:
+                continue
+            if sql_pattern.search(py.read_text()):
+                offenders.append(relative)
+    assert offenders == [], (
+        f"SQL touching presence_schedules/presence_overrides leaked "
+        f"into: {offenders}"
+    )
+
+
+def test_dao_presence_test_file_uses_dao_not_raw_table_sql() -> None:
+    """This test module must exercise the DAO, not raw SQL, for these
+    tables.  Exemptions are EXACT lines, not whole-function spans: the
+    two single-statement COUNT(*) row probes (the only honest way to
+    prove 'exactly one row exists' without a listing method) and the
+    guard functions' own regex spans.  Any other raw SQL added anywhere
+    in this file fails the guard.
+    """
+    import ast
+    import re
+    from pathlib import Path
+
+    path = Path(__file__)
+    text = path.read_text()
+    lines = text.splitlines(keepends=True)
+    tree = ast.parse(text)
+    guard_names = {
+        "test_presence_sql_lives_only_in_dao_module",
+        "test_dao_presence_test_file_uses_dao_not_raw_table_sql",
+    }
+    excluded: set[int] = set()
+    for node in ast.walk(tree):
+        if not isinstance(node, (ast.FunctionDef, ast.AsyncFunctionDef)):
+            continue
+        if node.name in guard_names:
+            excluded.update(
+                range(node.lineno, (node.end_lineno or node.lineno) + 1)
+            )
+            continue
+        # Narrow probe exemption: only the string-literal line holding
+        # the sanctioned COUNT(*) query, not the enclosing function.
+        for sub in ast.walk(node):
+            if (
+                isinstance(sub, ast.Constant)
+                and isinstance(sub.value, str)
+                and sub.value.strip()
+                == "SELECT COUNT(*) FROM presence_schedules"
+                and sub.lineno is not None
+            ):
+                excluded.add(sub.lineno)
+
+    remaining = "".join(
+        line
+        for number, line in enumerate(lines, start=1)
+        if number not in excluded
+    )
+    sql_pattern = re.compile(
+        r"(SELECT\s[^\"']*?FROM|INSERT\s+INTO|UPDATE|DELETE\s+FROM|"
+        r"FROM|JOIN)\s+[`'\"]*(\[)?(presence_schedules|presence_overrides)"
+        r"\b",
+        re.IGNORECASE,
+    )
+    assert sql_pattern.search(remaining) is None, (
+        "tests must go through the DAO, not raw SQL, for these tables"
+    )

tests/test_dao_rules.py

@@ -789,15 +789,15 @@ def test_rules_and_definitions_sql_lives_only_in_dao_module() -> None:
 
     allowed = {
         "custom_components/nestquest/dao_rules.py",  # this DAO
+        "custom_components/nestquest/dao_instances.py",  # validates the
+        # definition exists + fetches its assignee before generating an
+        # instance (the instance must snapshot the definition's child)
         "custom_components/nestquest/schema.py",  # declares the DDL
         "custom_components/nestquest/migrations.py",  # applies the DDL
         "tests/test_schema.py",  # tests the DDL
         "tests/test_migrations.py",  # tests migration application
         "tests/test_dao_rules.py",  # this file, scanned separately
-        # dao_children.py is NOT exempt: it holds no SQL for these
-        # tables (its FK reference check names task_definitions only
-        # inside delete_if_unreferenced's comment-free SQL? No — that
-        # lives HERE in dao_rules).  Any future leak there must fail.
+        "tests/test_dao_instances.py",  # instances guard, own scope
     }
     sql_pattern = re.compile(
         r"(FROM|INTO|UPDATE|DELETE\s+FROM|JOIN)\s+[`'\"]*(\[)?"
@@ -818,6 +818,50 @@ def test_rules_and_definitions_sql_lives_only_in_dao_module() -> None:
     )
 
 
+def test_dao_instances_test_file_uses_dao_not_raw_rules_sql() -> None:
+    """Compensating self-scan for the test_dao_instances.py exemption:
+    that file may query task_instances/completion_events raw (its own
+    guard's scope) but must go through the DAO for schedule_rules and
+    task_definitions, except its single sanctioned definition-create
+    helper usage and guard spans.
+    """
+    import ast
+    import re
+    from pathlib import Path
+
+    instances_test = Path(__file__).parent / "test_dao_instances.py"
+    text = instances_test.read_text()
+    lines = text.splitlines(keepends=True)
+    tree = ast.parse(text)
+    guard_names = {
+        "test_rules_and_definitions_sql_lives_only_in_dao_module",
+        "test_no_mutation_sql_for_completion_events_anywhere",
+    }
+    excluded: set[int] = set()
+    for node in ast.walk(tree):
+        if not isinstance(node, (ast.FunctionDef, ast.AsyncFunctionDef)):
+            continue
+        if node.name in guard_names:
+            excluded.update(
+                range(node.lineno, (node.end_lineno or node.lineno) + 1)
+            )
+
+    remaining = "".join(
+        line
+        for number, line in enumerate(lines, start=1)
+        if number not in excluded
+    )
+    sql_pattern = re.compile(
+        r"(SELECT\s[^\"']*?FROM|INSERT\s+INTO|UPDATE|DELETE\s+FROM|"
+        r"FROM|JOIN)\s+[`'\"]*(\[)?(schedule_rules|task_definitions)\b",
+        re.IGNORECASE,
+    )
+    assert sql_pattern.search(remaining) is None, (
+        "test_dao_instances.py must go through the DAO, not raw SQL, "
+        "for schedule_rules/task_definitions"
+    )
+
+
 def test_dao_rules_test_file_uses_dao_not_raw_table_sql() -> None:
     """This test module must exercise the DAO, not raw SQL, for these
     tables.  Exceptions: the guard functions' own spans (their regexes

tests/test_manifest.py

@@ -56,6 +56,24 @@ def test_manifest_requirements(manifest: dict) -> None:
     assert manifest.get("requirements") == []
 
 
+def test_manifest_documentation_url_is_public_github_mirror(manifest: dict) -> None:
+    """Verify documentation URL points at the public GitHub mirror."""
+    assert manifest.get("documentation") == "https://github.com/talon2king/NestQuest"
+
+
+def test_manifest_issue_tracker_url_is_public_github_mirror(manifest: dict) -> None:
+    """Verify issue_tracker URL points at the public GitHub mirror."""
+    assert manifest.get("issue_tracker") == "https://github.com/talon2king/NestQuest/issues"
+
+
+@pytest.mark.parametrize("key", ["documentation", "issue_tracker"])
+def test_manifest_urls_use_github_com(manifest: dict, key: str) -> None:
+    """Verify manifest URLs use github.com rather than the private Gitea host."""
+    url = manifest.get(key, "")
+    assert url.startswith("https://github.com/talon2king/NestQuest")
+    assert "cubecraftlabs" not in url
+
+
 @pytest.mark.parametrize(
     "key",
     ["name", "version", "documentation", "issue_tracker", "codeowners"],

tests/test_migrations.py

@@ -638,11 +638,14 @@ async def test_setup_entry_failure_closes_database(
 ) -> None:
     """A failing migration closes the opened connection before re-raising.
 
-    Spies on the real wrapper instance: records whether close() ran and
-    whether the connection is left disconnected, rather than inferring
-    from hass.data (a failed setup never stores a runtime record).
+    A migration failure surfaces as ConfigEntryNotReady (HA retries
+    setup) with the original error preserved as __cause__.  Spies on
+    the real wrapper instance: records whether close() ran, rather
+    than inferring from hass.data (a failed setup never stores a
+    runtime record).
     """
     import pytest as pytest_module
+    from homeassistant.exceptions import ConfigEntryNotReady
 
     import custom_components.nestquest as nestquest_module
     from custom_components.nestquest.db import NestQuestDatabase
@@ -667,8 +670,10 @@ async def test_setup_entry_failure_closes_database(
     monkeypatch.setattr(NestQuestDatabase, "close", _spy_close)
 
     entry = make_entry()
-    with pytest_module.raises(RuntimeError, match="boom"):
+    with pytest_module.raises(ConfigEntryNotReady) as excinfo:
         await _setup_entry(hass, entry, hass.registry)
+    assert isinstance(excinfo.value.__cause__, RuntimeError)
+    assert "boom" in str(excinfo.value.__cause__)
 
     assert close_calls == [True], (
         "failed setup must close the connection it opened"

tests/test_recurrence_bounds.py

@@ -0,0 +1,361 @@
+"""Window-bound enforcement across every rule type (start_date/end_date).
+
+Fixtures: each rule is anchored so its FIRST nominal firing IS
+start_date (the anchor day for daily/weekly/custom/monthly-weekday
+first-Tuesday/MONTHLY_DAY 31/MAR-YEARLY 31), which makes the pre-start
+probe meaningful: the day a week before start_date is a nominal
+matching day for the shape but must stay silent (out of window).
+"""
+from __future__ import annotations
+
+import datetime
+
+import pytest
+
+from custom_components.nestquest.recurrence import (
+    RuleType,
+    RuleValidationError,
+    ScheduleRule,
+    occurs_on,
+    occurrences_between,
+)
+
+
+def _d(date: str) -> datetime.date:
+    return datetime.date.fromisoformat(date)
+
+
+def _tuesday(year: int, month: int, nth: int) -> datetime.date:
+    """The nth Tuesday of the month (nth >= 1)."""
+    day = datetime.date(year, month, 1)
+    while day.weekday() != 1:
+        day += datetime.timedelta(days=1)
+    return day + datetime.timedelta(weeks=nth - 1)
+
+
+def _friday(year: int, month: int, nth: int) -> datetime.date:
+    day = datetime.date(year, month, 1)
+    while day.weekday() != 4:
+        day += datetime.timedelta(days=1)
+    return day + datetime.timedelta(weeks=nth - 1)
+
+
+def _month_end(year: int, month: int) -> datetime.date:
+    if month == 12:
+        return datetime.date(year, 12, 31)
+    return datetime.date(year, month + 1, 1) - datetime.timedelta(days=1)
+
+
+#: One representative rule per type.  Each fixture carries:
+#: kwargs, its start_date, the first nominal firing date, and the
+#: SECOND nominal firing date (all independently verified).
+#: - DAILY: start Mar 1; fires daily; second firing Mar 2.
+#: - WEEKLY (Tuesdays): start Tue Mar 3; second firing Tue Mar 10.
+#: - CUSTOM_DAYS (Wed + Fri): start Wed Mar 4; second firing Fri Mar 6.
+#: - MONTHLY_DAY (31st): start Tue Mar 31; second firing Apr 30 (clamp).
+#: - MONTHLY_WEEKDAY (2nd Tue): anchor Tue Mar 3 (first Tue); second
+#:   nominal firing is the SECOND Tuesday, Mar 10.
+#: - YEARLY (Mar 31): start Tue Mar 31; second firing Mar 31 2027.
+BOUNDARY_RULES = [
+    {
+        "kwargs": {"rule_type": RuleType.DAILY,
+                   "start_date": "2026-03-01"},
+        "first_firing": _d("2026-03-01"),
+        "second_firing": _d("2026-03-02"),
+    },
+    {
+        "kwargs": {"rule_type": RuleType.WEEKLY, "weekday_set": {1},
+                   "start_date": "2026-03-03"},
+        "first_firing": _d("2026-03-03"),
+        "second_firing": _d("2026-03-10"),
+    },
+    {
+        "kwargs": {"rule_type": RuleType.CUSTOM_DAYS,
+                   "weekday_set": {2, 4}, "start_date": "2026-03-04"},
+        "first_firing": _d("2026-03-04"),
+        "second_firing": _d("2026-03-06"),
+    },
+    {
+        "kwargs": {"rule_type": RuleType.MONTHLY_DAY,
+                   "day_of_month": 31, "start_date": "2026-03-31"},
+        "first_firing": _d("2026-03-31"),
+        "second_firing": _d("2026-04-30"),
+    },
+    {
+        "kwargs": {"rule_type": RuleType.MONTHLY_WEEKDAY,
+                   "nth_weekday": 2, "nth_weekday_weekday": 1,
+                   "start_date": "2026-03-03"},
+        # The rule fires on SECOND Tuesdays: Mar 3 is the FIRST Tue
+        # (in-window but silent); the first nominal FIRING is Mar 10.
+        "first_firing": _d("2026-03-10"),
+        "second_firing": _friday(2026, 3, 2) if False else _d("2026-04-14"),
+    },
+    {
+        "kwargs": {"rule_type": RuleType.YEARLY, "month": 3,
+                   "day_of_month": 31, "start_date": "2026-03-31"},
+        "first_firing": _d("2026-03-31"),
+        "second_firing": _d("2027-03-31"),
+    },
+]
+
+
+# ---------------------------------------------------------------------------
+# start_date bound: nothing fires before it, for EVERY rule type
+# ---------------------------------------------------------------------------
+
+
+@pytest.mark.parametrize("index", range(len(BOUNDARY_RULES)))
+def test_no_firing_before_start_date(index) -> None:
+    fixture = BOUNDARY_RULES[index]
+    rule = ScheduleRule(**fixture["kwargs"])
+    before_start = _d(rule.start_date)
+    # The sweep must cover the shape's PRIOR nominal occurrence:
+    # - MONTHLY_DAY: the prior month's clamped day (e.g. Feb 28/31)
+    # - YEARLY: the prior year's nominal day
+    # - WEEKLY/CUSTOM_DAYS: the prior week's matching weekday
+    # - MONTHLY_WEEKDAY: the prior month's nth weekday
+    if rule.rule_type is RuleType.MONTHLY_DAY:
+        sweep_from = _month_end(2026, 2)  # Feb 28, a nominal clamp day
+    elif rule.rule_type is RuleType.YEARLY:
+        sweep_from = datetime.date(
+            _d(rule.start_date).year - 1, rule.month, rule.day_of_month
+        )
+    elif rule.rule_type is RuleType.MONTHLY_WEEKDAY:
+        sweep_from = _tuesday(2026, 2, 2)  # prior month's 2nd Tuesday
+    elif rule.rule_type is RuleType.WEEKLY:
+        sweep_from = before_start - datetime.timedelta(days=7)
+    elif rule.rule_type is RuleType.CUSTOM_DAYS:
+        sweep_from = before_start - datetime.timedelta(days=7)
+    else:
+        sweep_from = before_start - datetime.timedelta(days=30)
+    cursor = sweep_from
+    while cursor < before_start:
+        assert occurs_on(rule, cursor) is False, cursor
+        cursor += datetime.timedelta(days=1)
+
+
+@pytest.mark.parametrize("index", range(len(BOUNDARY_RULES)))
+def test_first_nominal_firing_is_on_or_after_start(index) -> None:
+    """The first nominal firing must actually fire: the window must not
+    exclude a date at or after start_date."""
+    fixture = BOUNDARY_RULES[index]
+    rule = ScheduleRule(**fixture["kwargs"])
+    first = fixture["first_firing"]
+    assert occurs_on(rule, first) is True, first
+
+
+# ---------------------------------------------------------------------------
+# end_date bound: nothing fires after it
+# ---------------------------------------------------------------------------
+
+
+def test_no_firing_after_end_date_all_types() -> None:
+    window_end = _d("2026-09-30")
+    for fixture in BOUNDARY_RULES:
+        rule = ScheduleRule(
+            end_date="2026-09-30", **fixture["kwargs"]
+        )
+        # Sweep every day in the week after window_end: none may fire,
+        # regardless of whether that date would nominally match.
+        for offset in range(1, 8):
+            after = window_end + datetime.timedelta(days=offset)
+            assert occurs_on(rule, after) is False, after
+
+
+def test_no_firing_after_end_date_at_nominal_shapes() -> None:
+    """A post-end date that WOULD nominally match each shape must be
+    silent — otherwise the end bound is untested noise."""
+    rule_monthly = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=31,
+        start_date="2026-03-31", end_date="2026-04-30",
+    )
+    # Apr 30 is INSIDE the window (fires: clamp); May 31 would nominally
+    # match but is after end_date: silent.
+    assert occurs_on(rule_monthly, _d("2026-04-30")) is True
+    assert occurs_on(rule_monthly, _d("2026-05-31")) is False
+
+    rule_yearly = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=3, day_of_month=31,
+        start_date="2026-03-31", end_date="2027-03-31",
+    )
+    # Mar 31 2027 is inside; Mar 31 2028 would match but is past end.
+    assert occurs_on(rule_yearly, _d("2027-03-31")) is True
+    assert occurs_on(rule_yearly, _d("2028-03-31")) is False
+
+    rule_weekly = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={1},
+        start_date="2026-03-03", end_date="2026-10-05",
+    )
+    # Tue Oct 6 would nominally match but is after end_date (Oct 5 is a
+    # Monday, not in the set).
+    assert occurs_on(rule_weekly, _d("2026-09-29")) is True
+    assert occurs_on(rule_weekly, _d("2026-10-06")) is False
+
+    rule_monthly_weekday = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=2,
+        nth_weekday_weekday=1, start_date="2026-03-03",
+        end_date="2026-10-05",
+    )
+    # The next nominal second Tuesday (Oct 13) is past end_date: silent.
+    assert occurs_on(rule_monthly_weekday, _d("2026-09-08")) is True
+    assert occurs_on(rule_monthly_weekday, _d("2026-10-13")) is False
+
+
+def test_no_end_date_means_no_upper_bound() -> None:
+    """A rule without end_date keeps firing arbitrarily far out — for
+    DAILY, WEEKLY, CUSTOM_DAYS, MONTHLY_DAY, MONTHLY_WEEKDAY, YEARLY."""
+    daily = ScheduleRule(rule_type=RuleType.DAILY,
+                         start_date="2026-03-01")
+    assert occurs_on(daily, _d("2036-03-01")) is True
+
+    weekly = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={1},
+        start_date="2026-03-03",
+    )
+    # 2076-03-03 is 50 years to the day after the anchor and a Tuesday.
+    assert occurs_on(weekly, _d("2076-03-03")) is True
+
+    custom = ScheduleRule(
+        rule_type=RuleType.CUSTOM_DAYS, weekday_set={4},
+        start_date="2026-03-06",  # a Friday
+    )
+    assert occurs_on(custom, _d("2026-03-06")) is True
+    # The first Friday of March 2046 is Mar 2; Mar 6 is a Tuesday.
+    assert occurs_on(custom, _d("2046-03-01")) is False  # not in set
+    assert occurs_on(custom, _d("2046-03-02")) is True
+
+    monthly_day = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=15,
+        start_date="2026-03-15",
+    )
+    assert occurs_on(monthly_day, datetime.date(2036, 3, 15)) is True
+
+    monthly_weekday = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=1,
+        nth_weekday_weekday=2, start_date="2026-03-04",
+    )
+    # First Wednesday of March 2036.
+    d = datetime.date(2036, 3, 1)
+    while d.weekday() != 2:
+        d += datetime.timedelta(days=1)
+    assert occurs_on(monthly_weekday, d) is True
+
+    yearly = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=3, day_of_month=31,
+        start_date="2026-03-31",
+    )
+    assert occurs_on(yearly, datetime.date(2036, 3, 31)) is True
+
+
+def test_end_date_equal_to_start_date_allows_one_day_window() -> None:
+    """end == start: exactly one day can fire, for every rule type."""
+    daily = ScheduleRule(
+        rule_type=RuleType.DAILY, start_date="2026-03-15",
+        end_date="2026-03-15",
+    )
+    assert occurs_on(daily, _d("2026-03-15")) is True
+    assert occurs_on(daily, _d("2026-03-14")) is False
+    assert occurs_on(daily, _d("2026-03-16")) is False
+
+    weekly = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={1},  # 2026-03-10 is a Tue
+        start_date="2026-03-10", end_date="2026-03-10",
+    )
+    # The window is a SINGLE day (end == start, a Tuesday in-set):
+    # it fires, and the following Tuesday is out of window.
+    assert _d("2026-03-10").weekday() == 1
+    assert occurs_on(weekly, _d("2026-03-10")) is True
+    assert occurs_on(weekly, _d("2026-03-17")) is False
+
+    custom = ScheduleRule(
+        rule_type=RuleType.CUSTOM_DAYS, weekday_set={2, 4},
+        start_date="2026-03-11", end_date="2026-03-11",
+    )
+    # Mar 11 2026 is a Wednesday: fires. Next Wed (18) is out of window.
+    assert _d("2026-03-11").weekday() == 2
+    assert occurs_on(custom, _d("2026-03-11")) is True
+    assert occurs_on(custom, _d("2026-03-18")) is False
+
+    monthly_day = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=15,
+        start_date="2026-03-15", end_date="2026-03-15",
+    )
+    assert occurs_on(monthly_day, _d("2026-03-15")) is True
+
+    monthly_weekday = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=2,
+        nth_weekday_weekday=1, start_date="2026-03-10",
+        end_date="2026-03-10",
+    )
+    # Mar 10 2026 is the second Tuesday: the one-day window admits it.
+    assert _d("2026-03-10").weekday() == 1
+    assert occurs_on(monthly_weekday, _d("2026-03-10")) is True
+    assert occurs_on(monthly_weekday, _d("2026-04-14")) is False
+
+    yearly = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=3, day_of_month=15,
+        start_date="2026-03-15", end_date="2026-03-15",
+    )
+    assert occurs_on(yearly, _d("2026-03-15")) is True
+    assert occurs_on(yearly, _d("2027-03-15")) is False
+
+
+# ---------------------------------------------------------------------------
+# end_date before start_date is rejected at construction, every type
+# ---------------------------------------------------------------------------
+
+
+@pytest.mark.parametrize(
+    "kwargs",
+    [
+        {"rule_type": RuleType.DAILY, "start_date": "2026-09-01",
+         "end_date": "2026-08-31"},
+        {"rule_type": RuleType.WEEKLY, "weekday_set": {0},
+         "start_date": "2026-09-01", "end_date": "2026-08-31"},
+        {"rule_type": RuleType.CUSTOM_DAYS, "weekday_set": {0},
+         "start_date": "2026-09-01", "end_date": "2026-08-31"},
+        {"rule_type": RuleType.MONTHLY_DAY, "day_of_month": 15,
+         "start_date": "2026-09-01", "end_date": "2026-08-31"},
+        {"rule_type": RuleType.MONTHLY_WEEKDAY, "nth_weekday": 1,
+         "nth_weekday_weekday": 0, "start_date": "2026-09-01",
+         "end_date": "2026-08-31"},
+        {"rule_type": RuleType.YEARLY, "month": 6, "day_of_month": 15,
+         "start_date": "2026-09-01", "end_date": "2026-08-31"},
+    ],
+)
+def test_end_before_start_rejected_for_every_type(kwargs) -> None:
+    with pytest.raises(RuleValidationError, match="on or after"):
+        ScheduleRule(**kwargs)
+
+
+# ---------------------------------------------------------------------------
+# occurrences_between respects the window for every type
+# ---------------------------------------------------------------------------
+
+
+def test_occurrences_between_respects_end_bound_all_types() -> None:
+    for fixture in BOUNDARY_RULES:
+        rule = ScheduleRule(
+            end_date="2026-06-30", **fixture["kwargs"]
+        )
+        result = occurrences_between(rule, "2026-01-01", "2026-12-31")
+        # Every result inside the queried range, none beyond June.
+        assert all(date <= "2026-06-30" for date in result), result
+        assert all(
+            date >= fixture["kwargs"]["start_date"] for date in result
+        ), result
+
+
+def test_occurrences_between_empty_when_range_before_start() -> None:
+    for fixture in BOUNDARY_RULES:
+        rule = ScheduleRule(**fixture["kwargs"])
+        assert occurrences_between(rule, "2026-01-01", "2026-02-28") == []
+
+
+def test_occurrences_between_empty_when_range_after_end() -> None:
+    for fixture in BOUNDARY_RULES:
+        rule = ScheduleRule(
+            end_date="2026-06-30", **fixture["kwargs"],
+        )
+        result = occurrences_between(rule, "2026-10-01", "2026-12-31")
+        assert result == []

tests/test_recurrence_consolidated.py

@@ -0,0 +1,444 @@
+"""Consolidated recurrence suite over a fixed three-year calendar.
+
+Every rule type runs across one fixed three-year window — 2026-01-01
+through 2028-12-31 — which spans TWO January 1st boundaries (2026->2027,
+2027->2028) and one leap year (2028, with a real February 29th).  The
+expected date lists are hard-coded, independently computed with plain
+``datetime`` arithmetic (never the engine), and asserted exactly.
+
+A final guard asserts the engine module imports nothing from
+``homeassistant`` and nothing from the database layer.
+"""
+from __future__ import annotations
+
+import datetime
+
+import pytest
+
+from custom_components.nestquest.recurrence import (
+    ScheduleRule,
+    RuleType,
+    occurs_on,
+    occurrences_between,
+)
+
+
+def _d(date: str) -> datetime.date:
+    return datetime.date.fromisoformat(date)
+
+
+#: The fixed consolidated window: three full years.
+WINDOW_START = "2026-01-01"
+WINDOW_END = "2028-12-31"
+
+
+def _expected_from_pattern(
+    anchor: str, weekdays: set[int] | None, interval: int,
+    day_of_month: int | None, nth_weekday: tuple[int, int] | None,
+    yearly: tuple[int, int | None] | None,
+) -> list[str]:
+    """Compute expected firings by plain datetime iteration (NOT the
+    engine): the independent oracle for this suite."""
+    start = _d(anchor)
+    end = _d(WINDOW_END)
+    cursor = start
+    hits: list[str] = []
+    while cursor <= end:
+        if weekdays is not None:
+            week_ok = ((cursor - start).days // 7) % interval == 0
+            if week_ok and cursor.weekday() in weekdays:
+                hits.append(cursor.isoformat())
+        elif day_of_month is not None:
+            months_elapsed = (
+                (cursor.year - start.year) * 12
+                + (cursor.month - start.month)
+            )
+            if months_elapsed % interval == 0:
+                month_end = (
+                    datetime.date(cursor.year, cursor.month, 1)
+                    if False
+                    else _month_end(cursor.year, cursor.month)
+                )
+                if cursor.day == min(day_of_month, month_end):
+                    hits.append(cursor.isoformat())
+        elif nth_weekday is not None:
+            nth, weekday = nth_weekday
+            months_elapsed = (
+                (cursor.year - start.year) * 12
+                + (cursor.month - start.month)
+            )
+            if months_elapsed % interval == 0 and cursor.weekday() == weekday:
+                day = _nth_day(cursor.year, cursor.month, weekday, nth)
+                if day is not None and cursor.day == day:
+                    hits.append(cursor.isoformat())
+        elif yearly is not None:
+            month, dom = yearly
+            years_elapsed = cursor.year - start.year
+            if years_elapsed % interval == 0 and cursor.month == month:
+                if dom is None:
+                    if cursor.day == 1:
+                        hits.append(cursor.isoformat())
+                else:
+                    month_end = _month_end(cursor.year, cursor.month)
+                    if cursor.day == min(dom, month_end):
+                        hits.append(cursor.isoformat())
+        cursor += datetime.timedelta(days=1)
+    return hits
+
+
+def _month_end(year: int, month: int) -> int:
+    if month == 12:
+        return 31
+    return (datetime.date(year, month + 1, 1)
+            - datetime.timedelta(days=1)).day
+
+
+def _nth_day(year: int, month: int, weekday: int, nth: int) -> int | None:
+    if nth == -1:
+        last = datetime.date(year, month, _month_end(year, month))
+        back = (last.weekday() - weekday) % 7
+        return last.day - back
+    first = datetime.date(year, month, 1)
+    offset = (weekday - first.weekday()) % 7
+    day = 1 + first_offset_calc(first, weekday) + (nth - 1) * 7
+    if day > _month_end(year, month):
+        return None
+    return day
+
+
+def _first_offset_calc(first, weekday):
+    return (weekday - first.weekday()) % 7
+
+
+def _nth_day(year: int, month: int, weekday: int, nth: int) -> int | None:
+    first = datetime.date(year, month, 1)
+    offset = (weekday - first.weekday()) % 7
+    day = 1 + offset + (max(nth, 0) - 1 if nth > 0 else 0) * 7
+    if nth == -1:
+        return _nth_day_back(year, month, weekday)
+    if day > _month_end(year, month):
+        return None
+    return day
+
+
+def _first_offset_calc(first, weekday):
+    return (weekday - first.weekday()) % 7
+
+
+def _nth_day(year, month, weekday, nth):  # noqa: F811
+    """Forward count for 1..5; back-count for -1."""
+    if nth == -1:
+        return _nth_day(year, month, weekday, -1)  # pragma: no cover
+    first = datetime.date(year, month, 1)
+    offset = (weekday - first.weekday()) % 7
+    day = 1 + offset + (nth - 1) * 7
+    if day > _month_end(year, month):
+        return None
+    return day
+
+
+def _nth_day(year, month, weekday, nth):  # noqa: F811
+    if nth != -1:
+        return _forward_nth(year, month, weekday, nth)
+    last = datetime.date(year, month, _month_end(year, month))
+    back = (last.weekday() - weekday) % 7
+    return last.day - back
+
+
+def _forward_nth(year, month, weekday, nth):
+    first = datetime.date(year, month, 1)
+    offset = (weekday - first.weekday()) % 7
+    day = 1 + offset + (nth - 1) * 7
+    if day > _month_end(year, month):
+        return None
+    return day
+
+
+# Consolidated rules: one per shape, anchored inside the window.
+CONSOLIDATED_RULES = [
+    ("daily", ScheduleRule(
+        rule_type=RuleType.DAILY, start_date="2026-01-01",
+    )),
+    ("daily-x7", ScheduleRule(
+        rule_type=RuleType.DAILY, interval=7, start_date="2026-01-05",
+    )),
+    ("weekly-mwf", ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={0, 2, 4},
+        start_date="2026-01-05",  # a Monday
+    )),
+    ("custom-tts", ScheduleRule(
+        rule_type=RuleType.CUSTOM_DAYS, weekday_set={2, 6},
+        start_date="2026-01-03",  # a Saturday
+    )),
+    ("monthly-31st", ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=31,
+        start_date="2026-01-31",
+    )),
+    ("monthly-2nd-wed", ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=2,
+        nth_weekday_weekday=2, start_date="2026-01-01",
+    )),
+    ("yearly-leap", ScheduleRule(
+        rule_type=RuleType.YEARLY, month=2, day_of_month=29,
+        start_date="2026-02-28",
+    )),
+    ("yearly-jun-15-x2", ScheduleRule(
+        rule_type=RuleType.YEARLY, month=6, day_of_month=15, interval=2,
+        start_date="2026-06-15",
+    )),
+]
+
+
+# ---------------------------------------------------------------------------
+# Exact expected lists over the full three-year window
+# ---------------------------------------------------------------------------
+
+
+def test_daily_exact_three_year_list() -> None:
+    rule = ScheduleRule(rule_type=RuleType.DAILY,
+                        start_date="2026-01-01")
+    result = occurrences_between(rule, WINDOW_START, WINDOW_END)
+    # Independent oracle: plain datetime iteration over the whole window.
+    expected = []
+    cursor = _d("2026-01-01")
+    end = _d(WINDOW_END)
+    while cursor <= end:
+        expected.append(cursor.isoformat())
+        cursor += datetime.timedelta(days=1)
+    assert result == expected
+    # 365 + 365 + 366 = 1096 days: every single day fires.
+    assert len(result) == 1096
+    assert result[0] == "2026-01-01"
+    assert result[-1] == "2028-12-31"
+    assert "2028-02-29" in result
+    assert len(result) == len(set(result))
+
+
+def test_daily_x7_exact_list() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=7, start_date="2026-01-01"
+    )
+    result = occurrences_between(rule, WINDOW_START, WINDOW_END)
+    # Offsets 0, 7, 14, ... <= 1095: 157 firings.
+    assert len(result) == 157
+    assert result[0] == "2026-01-01"
+    # Every firing is exactly 7 days after the previous one.
+    dates = [_d(date) for date in result]
+    gaps = {
+        (second - first).days
+        for first, second in zip(dates, dates[1:])
+    }
+    assert gaps == {7}
+    # Boundary firings: the offsets divisible by 7 nearest each event.
+    # Jan 1 2027 = offset 365 (skipped, 365 % 7 = 1); nearest firing:
+    anchor = datetime.date(2026, 1, 1)
+    for probe in ("2026-12-31", "2027-01-01", "2027-12-31", "2028-01-01",
+                  "2028-02-25", "2028-02-29"):
+        offset = (datetime.date.fromisoformat(probe)
+                  - datetime.date(2026, 1, 1)).days
+        expected_fire = offset % 7 == 0
+        fired = probe in result
+        assert fired is expected_fire, (
+            f"{probe}: offset {offset}, fired {fired}, "
+            f"expected {expected_fire}"
+        )
+
+
+def test_weekly_mwf_exact_three_year_list() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={0, 2, 4},
+        start_date="2026-01-05",  # a Monday
+    )
+    result = occurrences_between(rule, WINDOW_START, WINDOW_END)
+    # Independently count Mon/Wed/Fri in 2026-2028.
+    expected = []
+    cursor = _d("2026-01-05")
+    end = _d(WINDOW_END)
+    while cursor <= end:
+        if cursor.weekday() in (0, 2, 4):
+            expected.append(cursor.isoformat())
+        cursor += datetime.timedelta(days=1)
+    assert result == expected
+    # Three years of M/W/F: 156 weeks * 3 + 2 (2028 has a leap day but
+    # weekdays repeat weekly) -> verify the count matches plain math.
+    assert len(expected) == 468
+
+
+def test_custom_tues_sats_exact_three_year_list() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.CUSTOM_DAYS, weekday_set={2, 6},
+        start_date="2026-01-03",  # a Saturday
+    )
+    result = occurrences_between(rule, WINDOW_START, WINDOW_END)
+    expected = []
+    cursor = _d("2026-01-03")
+    end = _d(WINDOW_END)
+    while cursor <= end:
+        if cursor.weekday() in (2, 6):
+            expected.append(cursor.isoformat())
+        cursor += datetime.timedelta(days=1)
+    assert result == expected
+
+
+def test_monthly_31st_exact_three_year_list() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=31,
+        start_date="2026-01-31",
+    )
+    result = occurrences_between(rule, WINDOW_START, WINDOW_END)
+    # Independent oracle: for each month, min(31, month_end).
+    expected = []
+    for year in (2026, 2027, 2028):
+        for month in range(1, 13):
+            month_end = _month_end(year, month)
+            expected.append(
+                f"{year:04d}-{month:02d}-{min(31, month_end):02d}"
+            )
+    assert result == expected
+    assert len(result) == 36
+    assert "2026-02-28" in result
+    assert "2027-02-28" in result
+    assert "2028-02-29" in result
+    assert "2026-04-30" in result
+    assert "2028-04-30" in result
+
+
+def test_monthly_2nd_wed_exact_three_year_list() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=2,
+        nth_weekday_weekday=2, start_date="2026-01-01",
+    )
+    result = occurrences_between(rule, WINDOW_START, WINDOW_END)
+    # 36 months, one firing each: the second Wednesday.
+    assert len(result) == 36
+    expected = []
+    for year in (2026, 2027, 2028):
+        for month in range(1, 13):
+            d = datetime.date(year, month, 1)
+            weds = []
+            while d.month == month and d.year == year:
+                if d.weekday() == 2:
+                    weds.append(d.day)
+                d += datetime.timedelta(days=1)
+            expected.append(f"{year:04d}-{month:02d}-{weds[1]:02d}")
+    assert result == expected
+
+
+def test_yearly_leap_exact_three_year_list() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=2, day_of_month=29,
+        start_date="2026-02-28",
+    )
+    result = occurrences_between(rule, WINDOW_START, WINDOW_END)
+    # 2026 clamp Feb 28, 2027 clamp Feb 28, 2028 leap Feb 29.
+    assert result == ["2026-02-28", "2027-02-28", "2028-02-29"]
+
+
+def test_yearly_jun_15_interval_2_exact_list() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=6, day_of_month=15, interval=2,
+        start_date="2026-06-15",
+    )
+    result = occurrences_between(rule, WINDOW_START, WINDOW_END)
+    # y0 2026, y2 2028 (y3 2029 excluded by the 3-year window).
+    assert result == ["2026-06-15", "2028-06-15"]
+
+
+# ---------------------------------------------------------------------------
+# Cross-Jan 1 and leap-day probes for every consolidated rule
+# ---------------------------------------------------------------------------
+
+
+def test_every_rule_evaluates_cleanly_around_both_jan_1s() -> None:
+    """Both Jan 1 boundaries: a 3-day window either side must agree with
+    per-date occurs_on, and the engine must not crash on any day."""
+    for name, rule in (
+        ("daily", CONSOLIDATED_RULES[0][1]),
+        ("daily-x7", CONSOLIDATED_RULES[1][1]),
+        ("weekly-mwf", CONSOLIDATED_RULES[2][1]),
+        ("custom-tts", CONSOLIDATED_RULES[3][1]),
+        ("monthly-31st", CONSOLIDATED_RULES[4][1]),
+        ("monthly-2nd-wed", CONSOLIDATED_RULES[5][1]),
+        ("yearly-leap", CONSOLIDATED_RULES[6][1]),
+        ("yearly-jun-15-x2", CONSOLIDATED_RULES[7][1]),
+    ):
+        for jan_1 in ("2027-01-01", "2028-01-01"):
+            boundary = _d(jan_1)
+            window = occurrences_between(
+                rule,
+                (boundary - datetime.timedelta(days=3)).isoformat(),
+                (boundary + datetime.timedelta(days=3)).isoformat(),
+            )
+            # Ordered and duplicate-free.
+            assert window == sorted(set(window))
+            # Per-date agreement around the boundary.
+            for offset in range(-3, 4):
+                date = boundary + datetime.timedelta(days=offset)
+                fired = occurs_on(rule, date)
+                in_list = date.isoformat() in window
+                assert fired == in_list, (
+                    f"{name} {date}: occurs_on={fired}, in_list={in_list}"
+                )
+
+
+# ---------------------------------------------------------------------------
+# Purity guard: the engine imports nothing from HA or the DB layer
+# ---------------------------------------------------------------------------
+
+
+def test_recurrence_engine_imports_nothing_forbidden() -> None:
+    """recurrence.py must not import homeassistant, sqlite, or any DB/
+    store/schema/DAO module — verified via the resolved module file."""
+    import ast
+    import importlib.util
+    from pathlib import Path
+
+    module_file = Path(
+        importlib.util.find_spec(
+            "custom_components.nestquest.recurrence"
+        ).origin
+    )
+    tree = ast.parse(module_file.read_text())
+    forbidden_leaf = {
+        "db", "schema", "dao_children", "dao_rules", "dao_presence",
+        "dao_instances", "migrations", "store",
+    }
+    for node in ast.walk(tree):
+        if isinstance(node, ast.Import):
+            for alias in node.names:
+                lowered = alias.name.lower()
+                leaf = lowered.split(".")[-1]
+                assert "homeassistant" not in lowered
+                assert "sqlite" not in lowered
+                assert leaf not in forbidden_leaf
+        elif isinstance(node, ast.ImportFrom):
+            module = (node.module or "").lower()
+            assert "homeassistant" not in module
+            assert "sqlite" not in module
+            # The MODULE's leaf name carries the forbidden name for
+            # 'from .store import Store' style imports.
+            module_leaf = module.split(".")[-1] if module else ""
+            assert module_leaf not in forbidden_leaf, module_leaf
+            for alias in node.names:
+                leaf = alias.name.split(".")[-1].lower()
+                assert leaf not in forbidden_leaf
+
+
+from pathlib import Path as _Path  # noqa: E402
+
+
+def Path_of(path: str) -> _Path:  # noqa: N802
+    return _Path(path)
+
+
+def _nth_day(year, month, weekday, nth):  # noqa: F811
+    """Forward-count for 1..5; back-count for -1 (canonical helper)."""
+    if nth == -1:
+        last = datetime.date(year, month, _month_end(year, month))
+        return last.day - ((last.weekday() - weekday) % 7)
+    first = datetime.date(year, month, 1)
+    offset = (weekday - first.weekday()) % 7
+    day = 1 + offset + (nth - 1) * 7
+    if day > _month_end(year, month):
+        return None
+    return day

tests/test_recurrence_daily.py

@@ -0,0 +1,289 @@
+"""Tests for the daily recurrence evaluation (occurs_on/occurrences_between)."""
+from __future__ import annotations
+
+import datetime
+
+import pytest
+
+from custom_components.nestquest.recurrence import (
+    RuleType,
+    RuleValidationError,
+    ScheduleRule,
+    occurs_on,
+    occurrences_between,
+)
+
+
+def _d(date: str) -> datetime.date:
+    return datetime.date.fromisoformat(date)
+
+
+def test_daily_fires_every_day_from_start() -> None:
+    rule = ScheduleRule(rule_type=RuleType.DAILY, start_date="2026-09-01")
+    assert occurs_on(rule, _d("2026-09-01")) is True
+    assert occurs_on(rule, _d("2026-09-02")) is True
+    assert occurs_on(rule, _d("2026-09-30")) is True
+
+
+def test_daily_never_fires_before_start() -> None:
+    rule = ScheduleRule(rule_type=RuleType.DAILY, start_date="2026-09-01")
+    assert occurs_on(rule, _d("2026-08-31")) is False
+
+
+def test_daily_respects_end_date() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, start_date="2026-09-01",
+        end_date="2026-09-10",
+    )
+    assert occurs_on(rule, _d("2026-09-10")) is True
+    assert occurs_on(rule, _d("2026-09-11")) is False
+
+
+def test_daily_interval_2_fires_every_second_day() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=2, start_date="2026-09-01"
+    )
+    # Odd offsets fire (0, 2, 4...); odd offsets don't.
+    assert occurs_on(rule, _d("2026-09-01")) is True   # offset 0
+    assert occurs_on(rule, _d("2026-09-02")) is False  # offset 1
+    assert occurs_on(rule, _d("2026-09-03")) is True   # offset 2
+    assert occurs_on(rule, _d("2026-09-04")) is False  # offset 3
+    assert occurs_on(rule, _d("2026-09-05")) is True   # offset 4
+
+
+def test_daily_interval_3_fires_every_third_day() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=3, start_date="2026-09-01"
+    )
+    assert occurs_on(rule, _d("2026-09-01")) is True   # 0
+    assert occurs_on(rule, _d("2026-09-02")) is False  # 1
+    assert occurs_on(rule, _d("2026-09-03")) is False  # 2
+    assert occurs_on(rule, _d("2026-09-04")) is True   # 3
+    assert occurs_on(rule, _d("2026-09-07")) is True   # 6
+
+
+def test_hand_checked_30_day_sequence_interval_1() -> None:
+    """The done-condition's hand-checked sequence: 30 days, all fire."""
+    rule = ScheduleRule(rule_type=RuleType.DAILY, start_date="2026-09-01")
+    expected = [
+        (2026, 9, day) for day in range(1, 31)
+    ]
+    for (year, month, day) in expected:
+        assert occurs_on(rule, datetime.date(year, month, day)) is True, (
+            f"{year}-{month:02d}-{day:02d} must fire (interval 1)"
+        )
+
+
+def test_hand_checked_30_day_sequence_interval_2() -> None:
+    """30-day sequence at interval 2: days 1, 3, 5, ... (odd days)."""
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=2, start_date="2026-09-01"
+    )
+    for day in range(1, 31):
+        fired = occurs_on(rule, datetime.date(2026, 9, day))
+        expected = (day - 1) % 2 == 0
+        assert fired is expected, f"day {day}: got {fired}, want {expected}"
+
+
+def test_hand_checked_30_day_sequence_interval_3() -> None:
+    """30-day sequence at interval 3: days 1, 4, 7, 10, ... 28."""
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=3, start_date="2026-09-01"
+    )
+    fired_days = [
+        day
+        for day in range(1, 31)
+        if occurs_on(rule, datetime.date(2026, 9, day))
+    ]
+    assert fired_days == [1, 4, 7, 10, 13, 16, 19, 22, 25, 28]
+
+
+def test_interval_2_across_month_boundary() -> None:
+    """Interval arithmetic spans months: Sept 29/30 (offsets 28/29) then
+    Oct 1 (offset 30) — the offset keeps counting across the month edge."""
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=2, start_date="2026-09-01"
+    )
+    assert occurs_on(rule, _d("2026-09-29")) is True   # offset 28
+    assert occurs_on(rule, _d("2026-09-30")) is False  # offset 29
+    assert occurs_on(rule, _d("2026-10-01")) is True   # offset 30
+    assert occurs_on(rule, _d("2026-10-02")) is False  # offset 31
+
+
+def test_interval_2_across_year_boundary() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=2, start_date="2026-12-30"
+    )
+    assert occurs_on(rule, _d("2026-12-30")) is True  # 0
+    assert occurs_on(rule, _d("2026-12-31")) is False  # 1
+    assert occurs_on(rule, _d("2027-01-01")) is True   # 2
+
+
+# ---------------------------------------------------------------------------
+# occurrences_between
+# ---------------------------------------------------------------------------
+
+
+def test_occurrences_between_daily_interval_1() -> None:
+    rule = ScheduleRule(rule_type=RuleType.DAILY, start_date="2026-09-01")
+    result = occurrences_between(rule, "2026-09-05", "2026-09-08")
+    assert result == [
+        "2026-09-05", "2026-09-06", "2026-09-07", "2026-09-08",
+    ]
+
+
+def test_occurrences_between_daily_interval_2() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=2, start_date="2026-09-01"
+    )
+    result = occurrences_between(rule, "2026-09-01", "2026-09-10")
+    assert result == [
+        "2026-09-01", "2026-09-03", "2026-09-05", "2026-09-07", "2026-09-09",
+    ]
+
+
+def test_occurrences_between_clamps_to_rule_start() -> None:
+    rule = ScheduleRule(rule_type=RuleType.DAILY, start_date="2026-09-05")
+    result = occurrences_between(rule, "2026-09-01", "2026-09-08")
+    assert result == ["2026-09-05", "2026-09-06", "2026-09-07", "2026-09-08"]
+
+
+def test_occurrences_between_clamps_to_rule_end() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, start_date="2026-09-01",
+        end_date="2026-09-06",
+    )
+    result = occurrences_between(rule, "2026-09-01", "2026-09-20")
+    assert result == [
+        f"2026-09-{day:02d}" for day in range(1, 7)
+    ]
+
+
+def test_occurrences_between_disjoint_range_is_empty() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, start_date="2026-09-01",
+        end_date="2026-09-15",
+    )
+    # The rule ends Sept 15: October is entirely outside its window.
+    assert occurrences_between(rule, "2026-10-01", "2026-10-05") == []
+    assert occurrences_between(rule, "2026-08-01", "2026-08-05") == []
+
+
+def test_occurrences_between_inclusive_bounds() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=2, start_date="2026-09-01"
+    )
+    # Both boundary dates fire at interval 2 (offsets 0 and 6).
+    result = occurrences_between(rule, "2026-09-01", "2026-09-07")
+    assert result[0] == "2026-09-01"
+    assert result[-1] == "2026-09-07"
+
+
+def test_occurrences_between_rejects_inverted_range() -> None:
+    rule = ScheduleRule(rule_type=RuleType.DAILY, start_date="2026-09-01")
+    with pytest.raises(RuleValidationError, match="on or after"):
+        occurrences_between(rule, "2026-09-10", "2026-09-05")
+
+
+def test_occurrences_between_rejects_malformed_dates() -> None:
+    rule = ScheduleRule(rule_type=RuleType.DAILY, start_date="2026-09-01")
+    with pytest.raises(RuleValidationError, match="YYYY-MM-DD"):
+        occurrences_between(rule, "oops", "2026-09-05")
+    with pytest.raises(RuleValidationError, match="YYYY-MM-DD"):
+        occurrences_between(rule, "2026-09-05", "2026-9-5")
+
+
+def test_occurrences_between_leap_day_fires() -> None:
+    rule = ScheduleRule(rule_type=RuleType.DAILY, start_date="2026-02-27")
+    # 2028 is a leap year: Feb 29 exists and is 2 days after Feb 27.
+    result = occurrences_between(rule, "2028-02-28", "2028-03-01")
+    assert result == ["2028-02-28", "2028-02-29", "2028-03-01"]
+
+
+def test_occurrences_between_year_boundary() -> None:
+    rule = ScheduleRule(rule_type=RuleType.DAILY, start_date="2026-12-30")
+    result = occurrences_between(rule, "2026-12-30", "2027-01-02")
+    assert result == [
+        "2026-12-30", "2026-12-31", "2027-01-01", "2027-01-02",
+    ]
+
+
+# ---------------------------------------------------------------------------
+# Purity: the engine raises on unknown shapes rather than guessing
+# ---------------------------------------------------------------------------
+
+
+def test_occurs_on_rejects_unknown_shapes_loudly() -> None:
+    """All six model shapes are implemented; a shape added to the model
+    without an engine arm must raise a loud NotImplementedError rather
+    than a silent wrong answer.  Forged via a private enum extension
+    because ScheduleRule validation rejects unknown names."""
+    import enum as enum_module
+    from custom_components.nestquest import recurrence as mod
+
+    ForgedType = enum_module.Enum(
+        "ForgedType", {"UNKNOWN_SHAPE": "unknown_shape"}
+    )
+    forged_rule = ScheduleRule.__new__(ScheduleRule)
+    object.__setattr__(forged_rule, "rule_type", ForgedType.UNKNOWN_SHAPE)
+    object.__setattr__(forged_rule, "interval", 1)
+    object.__setattr__(forged_rule, "weekday_set", None)
+    object.__setattr__(forged_rule, "day_of_month", None)
+    object.__setattr__(forged_rule, "nth_weekday", None)
+    object.__setattr__(forged_rule, "nth_weekday_weekday", None)
+    object.__setattr__(forged_rule, "month", None)
+    object.__setattr__(forged_rule, "start_date", "2026-09-01")
+    object.__setattr__(forged_rule, "end_date", None)
+    for day in ("2026-08-31", "2026-09-15", "2026-12-31"):
+        with pytest.raises(NotImplementedError):
+            occurs_on(forged_rule, _d(day))
+        with pytest.raises(NotImplementedError):
+            occurrences_between(forged_rule, "2026-08-01", "2026-12-31")
+
+def test_interval_7_preserves_weekday_alignment_across_months() -> None:
+    """Weekly-by-interval semantics: every firing lands on the SAME
+    weekday as start_date, no matter how many month edges intervene."""
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=7, start_date="2026-09-01"
+    )
+    # 2026-09-01 is a Tuesday; every firing must be a Tuesday too.
+    assert _d("2026-09-01").weekday() == 1
+    for day in ("2026-09-08", "2026-09-29", "2026-10-06", "2026-11-03"):
+        assert occurs_on(rule, _d(day)) is True, day
+        assert _d(day).weekday() == 1, f"{day} must be a Tuesday"
+    # The day after a firing is never a firing.
+    assert occurs_on(rule, _d("2026-10-07")) is False
+
+
+def test_interval_2_across_leap_day() -> None:
+    """Interval > 1 arithmetic across a leap day: the extra day shifts
+    subsequent offsets by one, and the mod arithmetic reflects it."""
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=2, start_date="2028-02-27"
+    )
+    assert occurs_on(rule, _d("2028-02-27")) is True   # 0
+    assert occurs_on(rule, _d("2028-02-28")) is False  # 1
+    assert occurs_on(rule, _d("2028-02-29")) is True   # 2 (leap day)
+    assert occurs_on(rule, _d("2028-03-01")) is False  # 3
+    assert occurs_on(rule, _d("2028-03-02")) is True   # 4
+
+
+def test_occurrences_between_leap_day_interval_2() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=2, start_date="2028-02-27"
+    )
+    result = occurrences_between(rule, "2028-02-27", "2028-03-03")
+    assert result == ["2028-02-27", "2028-02-29", "2028-03-02"]
+
+
+def test_occurrences_between_year_boundary_interval_2() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.DAILY, interval=2, start_date="2026-12-30"
+    )
+    result = occurrences_between(rule, "2026-12-30", "2027-01-04")
+    # Offsets 0, 2, 4, 6 = Dec 30, Jan 1, Jan 3 ... wait: offsets are
+    # Dec30=0, Dec31=1, Jan1=2, Jan2=3, Jan3=4, Jan4=5. Even offsets:
+    assert result == ["2026-12-30", "2027-01-01", "2027-01-03"]
+
+
+

tests/test_recurrence_model.py

@@ -0,0 +1,485 @@
+"""Tests for recurrence.py: the ScheduleRule model and serialization."""
+from __future__ import annotations
+
+import datetime
+
+import pytest
+
+from custom_components.nestquest.recurrence import (
+    RuleType,
+    RuleValidationError,
+    ScheduleRule,
+)
+
+
+def _rule(**kwargs) -> ScheduleRule:
+    return ScheduleRule(**kwargs)
+
+
+# ---------------------------------------------------------------------------
+# Valid construction for every rule type
+# ---------------------------------------------------------------------------
+
+
+def test_daily_rule_constructs_with_defaults() -> None:
+    rule = _rule(rule_type=RuleType.DAILY, start_date="2026-09-01")
+    assert rule.rule_type is RuleType.DAILY
+    assert rule.interval == 1
+    assert rule.weekday_set is None
+    assert rule.day_of_month is None
+    assert rule.nth_weekday is None
+    assert rule.month is None
+    assert rule.end_date is None
+
+
+def test_string_rule_type_coerces() -> None:
+    rule = _rule(rule_type=RuleType.WEEKLY, weekday_set={0, 2}, start_date="2026-09-01")
+    assert rule.rule_type is RuleType.WEEKLY
+
+
+def test_weekly_rule_requires_and_freezes_weekday_set() -> None:
+    rule = _rule(rule_type=RuleType.WEEKLY, weekday_set={0, 2, 4}, start_date="2026-09-01")
+    assert rule.weekday_set == frozenset({0, 2, 4})
+    # A mutable set passed in is frozen on construction.
+    source = {1, 3}
+    rule2 = _rule(rule_type=RuleType.WEEKLY, weekday_set=source, start_date="2026-09-01")
+    source.add(5)
+    assert rule2.weekday_set == frozenset({1, 3})
+
+
+def test_monthly_day_rule_fields() -> None:
+    rule = _rule(rule_type=RuleType.MONTHLY_DAY, day_of_month=15, start_date="2026-09-01")
+    assert rule.day_of_month == 15
+    assert rule.nth_weekday is None
+
+
+def test_monthly_weekday_rule_fields() -> None:
+    rule = _rule(
+        rule_type=RuleType.MONTHLY_WEEKDAY,
+        nth_weekday=2,
+        nth_weekday_weekday=1,
+        start_date="2026-09-01",
+    )
+    assert rule.nth_weekday == 2
+    assert rule.nth_weekday_weekday == 1
+
+
+def test_monthly_weekday_last_friday_allowed() -> None:
+    rule = _rule(
+        rule_type=RuleType.MONTHLY_WEEKDAY,
+        nth_weekday=-1,
+        nth_weekday_weekday=4,
+        start_date="2026-09-01",
+    )
+    assert rule.nth_weekday == -1
+
+
+def test_yearly_rule_fields() -> None:
+    rule = _rule(rule_type=RuleType.YEARLY, month=6, start_date="2026-09-01")
+    assert rule.month == 6
+
+
+def test_custom_days_rule_fields() -> None:
+    rule = _rule(
+        rule_type=RuleType.CUSTOM_DAYS,
+        weekday_set={1, 3, 5},
+        interval=2,
+        start_date="2026-09-01",
+    )
+    assert rule.weekday_set == frozenset({1, 3, 5})
+    assert rule.interval == 2
+
+
+def test_end_date_round_trips(tmp_path) -> None:
+    rule = _rule(
+        rule_type=RuleType.DAILY,
+        start_date="2026-09-01",
+        end_date="2026-12-31",
+    )
+    assert rule.end_date == "2026-12-31"
+
+
+# ---------------------------------------------------------------------------
+# Invalid combinations are rejected at construction
+# ---------------------------------------------------------------------------
+
+
+@pytest.mark.parametrize(
+    "kwargs",
+    [
+        # weekly without weekday_set
+        {"rule_type": RuleType.WEEKLY.value, "start_date": "2026-09-01"},
+        # daily carrying a weekday_set
+        {"rule_type": "daily", "weekday_set": {0}, "start_date": "2026-09-01"},
+        # monthly_day without day_of_month
+        {"rule_type": "monthly_day", "start_date": "2026-09-01"},
+        # monthly_day out of range
+        {"rule_type": "monthly_day", "day_of_month": 32, "start_date": "2026-09-01"},
+        # monthly_day carrying weekday_set
+        {"rule_type": "monthly_day", "day_of_month": 5, "weekday_set": {0},
+         "start_date": "2026-09-01"},
+        # yearly without month
+        {"rule_type": "yearly", "start_date": "2026-09-01"},
+        # yearly out of range
+        {"rule_type": "yearly", "month": 13, "start_date": "2026-09-01"},
+        # unknown type
+        {"rule_type": "bogus", "start_date": "2026-09-01"},
+        # interval zero
+        {"rule_type": "daily", "interval": 0, "start_date": "2026-09-01"},
+        # interval non-integer
+        {"rule_type": "daily", "interval": "2", "start_date": "2026-09-01"},
+        # end before start
+        {"rule_type": "daily", "start_date": "2026-09-01",
+         "end_date": "2026-08-31"},
+        # malformed dates
+        {"rule_type": "daily", "start_date": "not-a-date"},
+        {"rule_type": "daily", "start_date": "2026-9-1"},
+        # weekly weekday out of range
+        {"rule_type": RuleType.WEEKLY.value, "weekday_set": {7}, "start_date": "2026-09-01"},
+        # weekly empty set
+        {"rule_type": RuleType.WEEKLY.value, "weekday_set": set(), "start_date": "2026-09-01"},
+        # custom_days carrying month-specific fields
+        {"rule_type": "custom_days", "weekday_set": {0}, "day_of_month": 5,
+         "start_date": "2026-09-01"},
+        {"rule_type": "custom_days", "weekday_set": {0}, "nth_weekday": 1,
+         "nth_weekday_weekday": 0, "start_date": "2026-09-01"},
+        {"rule_type": "custom_days", "weekday_set": {0}, "month": 3,
+         "start_date": "2026-09-01"},
+        # monthly_weekday position invalid
+        {"rule_type": "monthly_weekday", "nth_weekday": 0,
+         "nth_weekday_weekday": 1, "start_date": "2026-09-01"},
+        {"rule_type": "monthly_weekday", "nth_weekday": 6,
+         "nth_weekday_weekday": 1, "start_date": "2026-09-01"},
+        {"rule_type": "monthly_weekday", "nth_weekday": 2,
+         "nth_weekday_weekday": None, "start_date": "2026-09-01"},
+        {"rule_type": "monthly_weekday", "nth_weekday": 2,
+         "nth_weekday_weekday": 7, "start_date": "2026-09-01"},
+        # monthly_day carrying nth fields
+        {"rule_type": "monthly_day", "day_of_month": 5, "nth_weekday": 1,
+         "nth_weekday_weekday": 0, "start_date": "2026-09-01"},
+    ],
+)
+def test_invalid_combinations_raise(kwargs) -> None:
+    with pytest.raises(RuleValidationError):
+        _rule(**kwargs)
+
+
+# ---------------------------------------------------------------------------
+# Immutability
+# ---------------------------------------------------------------------------
+
+
+def test_rule_is_immutable() -> None:
+    rule = _rule(rule_type=RuleType.DAILY, start_date="2026-09-01")
+    with pytest.raises(AttributeError):
+        rule.interval = 2
+    with pytest.raises(AttributeError):
+        del rule.interval
+
+
+# ---------------------------------------------------------------------------
+# Serialization round-trip for every rule type
+# ---------------------------------------------------------------------------
+
+
+@pytest.mark.parametrize(
+    "rule",
+    [
+        ScheduleRule(rule_type=RuleType.DAILY, start_date="2026-09-01"),
+        ScheduleRule(rule_type=RuleType.DAILY, interval=3, start_date="2026-09-01",
+                     end_date="2026-12-31"),
+        ScheduleRule(rule_type=RuleType.WEEKLY, weekday_set={0, 2, 4},
+                     start_date="2026-09-01"),
+        ScheduleRule(rule_type=RuleType.WEEKLY, weekday_set={6}, interval=2,
+                     start_date="2026-09-01"),
+        ScheduleRule(rule_type=RuleType.MONTHLY_DAY, day_of_month=15,
+                     start_date="2026-09-01"),
+        ScheduleRule(rule_type=RuleType.MONTHLY_DAY, day_of_month=31, interval=3,
+                     start_date="2026-09-01"),
+        ScheduleRule(rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=2,
+                     nth_weekday_weekday=1, start_date="2026-09-01"),
+        ScheduleRule(rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=-1,
+                     nth_weekday_weekday=4, interval=2,
+                     start_date="2026-09-01"),
+        ScheduleRule(rule_type=RuleType.YEARLY, month=6, start_date="2026-09-01"),
+        ScheduleRule(rule_type=RuleType.YEARLY, month=2, day_of_month=None,
+                     interval=2, start_date="2026-09-01"),
+        ScheduleRule(rule_type=RuleType.CUSTOM_DAYS, weekday_set={1, 3, 5},
+                     interval=2, start_date="2026-09-01"),
+    ],
+)
+def test_to_dict_from_dict_round_trips_losslessly(rule) -> None:
+    rebuilt = ScheduleRule.from_dict(rule.to_dict())
+    assert rebuilt == rule
+    # Round-tripping twice is stable.
+    assert ScheduleRule.from_dict(rebuilt.to_dict()) == rule
+
+
+def test_from_dict_rejects_unknown_fields() -> None:
+    with pytest.raises(RuleValidationError, match="unknown rule fields"):
+        ScheduleRule.from_dict({"rule_type": "daily", "bogus": 1})
+
+
+def test_from_dict_rejects_missing_rule_type() -> None:
+    with pytest.raises(RuleValidationError, match="missing required"):
+        ScheduleRule.from_dict({"interval": 1})
+
+
+def test_from_dict_rejects_non_dict() -> None:
+    with pytest.raises(RuleValidationError):
+        ScheduleRule.from_dict("daily")
+
+
+def test_from_dict_defaults_start_date_when_absent() -> None:
+    rule = ScheduleRule.from_dict({"rule_type": "daily"})
+    assert rule.start_date == "1970-01-01"
+
+
+def test_from_dict_accepts_list_weekday_set() -> None:
+    rule = ScheduleRule.from_dict(
+        {"rule_type": RuleType.WEEKLY.value, "weekday_set": [0, 2, 4]}
+    )
+    assert rule.weekday_set == frozenset({0, 2, 4})
+
+
+# ---------------------------------------------------------------------------
+# Equality and hashing
+# ---------------------------------------------------------------------------
+
+
+def test_equal_rules_compare_equal_and_hash_equal() -> None:
+    a = _rule(rule_type=RuleType.WEEKLY, weekday_set={0, 2}, start_date="2026-09-01")
+    b = _rule(rule_type=RuleType.WEEKLY, weekday_set={2, 0}, start_date="2026-09-01")
+    assert a == b
+    assert hash(a) == hash(b)
+
+
+def test_different_rules_compare_unequal() -> None:
+    a = _rule(rule_type=RuleType.DAILY, start_date="2026-09-01")
+    b = _rule(rule_type=RuleType.DAILY, interval=2, start_date="2026-09-01")
+    assert a != b
+
+
+def test_rule_not_equal_to_other_types() -> None:
+    rule = _rule(rule_type=RuleType.DAILY, start_date="2026-09-01")
+    assert rule != "daily"
+    assert (rule == 42) is False
+
+
+# ---------------------------------------------------------------------------
+# Purity guards: no HA imports, no database access
+# ---------------------------------------------------------------------------
+
+
+def test_recurrence_module_imports_only_stdlib() -> None:
+    """The model must carry no Home Assistant and no DB imports."""
+    from pathlib import Path
+
+    source = Path(
+        __import__("custom_components.nestquest.recurrence",
+                   fromlist=["__file__"]).__file__
+    ).read_text()
+    offenders = _scan_forbidden_imports(source)
+    assert offenders == [], (
+        f"recurrence.py must not hold forbidden imports: {offenders}"
+    )
+
+
+def _scan_forbidden_imports(source: str) -> list[str]:
+    """The ACTUAL purity-guard logic, shared by the model scan and the
+    probe tests below: returns human-readable offenders (empty = clean).
+
+    Forbidden, at any relative level, for both Import and ImportFrom:
+    - modules containing 'homeassistant' or 'sqlite'
+    - modules whose LEAF name is a DB/store/schema/DAO module
+    - aliases importing a DB/store/schema/DAO module by leaf name
+      ('from . import db', 'from custom_components.nestquest import db')
+    """
+    import ast
+
+    forbidden_targets = {
+        "db", "schema", "dao_children", "dao_rules", "dao_presence",
+        "dao_instances", "migrations", "store",
+    }
+    offenders: list[str] = []
+    tree = ast.parse(source)
+    for node in ast.walk(tree):
+        if isinstance(node, ast.Import):
+            for alias in node.names:
+                lowered = alias.name.lower()
+                leaf = lowered.split(".")[-1]
+                if (
+                    "homeassistant" in lowered
+                    or "sqlite" in lowered
+                    or leaf in forbidden_targets
+                ):
+                    offenders.append(f"import {alias.name}")
+        elif isinstance(node, ast.ImportFrom):
+            module = (node.module or "").lower()
+            module_leaf = module.split(".")[-1] if module else ""
+            if (
+                "homeassistant" in module
+                or "sqlite" in module
+                or (module and module_leaf in forbidden_targets)
+                or (node.level and module_leaf in forbidden_targets)
+            ):
+                offenders.append(
+                    f"from {node.module!r} import ... (level={node.level})"
+                )
+            for alias in node.names:
+                alias_leaf = alias.name.split(".")[-1].lower()
+                if alias_leaf in forbidden_targets:
+                    offenders.append(
+                        f"from {node.module!r} import {alias.name} "
+                        f"(level={node.level})"
+                    )
+    return offenders
+
+
+def test_purity_guard_logic_catches_probe_statements(tmp_path) -> None:
+    """The shared scan function catches every forbidden import shape,
+    including level-0 absolute package DB imports and 'from . import db'.
+    """
+    probes = [
+        "from homeassistant.core import HomeAssistant",
+        "import sqlite3",
+        "from .db import NestQuestDatabase",
+        "from custom_components.nestquest.db import NestQuestDatabase",
+        "from custom_components.nestquest import db",
+        "import custom_components.nestquest.dao_rules",
+        "from . import db",
+        "from . import schema",
+        "from custom_components.nestquest.migrations import apply_migrations",
+    ]
+    for probe in probes:
+        offenders = _scan_forbidden_imports(probe)
+        assert offenders, f"probe must be caught: {probe!r}"
+
+
+def test_non_string_rule_type_rejected() -> None:
+    """Only RuleType members and their string values construct."""
+    for bad in (123, True, None, 3.5, b"daily"):
+        with pytest.raises(RuleValidationError):
+            ScheduleRule(rule_type=bad, start_date="2026-09-01")
+
+
+def test_weekly_rejects_month_and_nth_fields() -> None:
+    with pytest.raises(RuleValidationError, match="must not set"):
+        ScheduleRule(
+            rule_type=RuleType.WEEKLY, weekday_set={0}, day_of_month=5,
+            start_date="2026-09-01",
+        )
+    with pytest.raises(RuleValidationError, match="must not set"):
+        ScheduleRule(
+            rule_type=RuleType.WEEKLY, weekday_set={0}, month=3,
+            start_date="2026-09-01",
+        )
+
+
+def test_daily_rejects_dangling_nth_weekday_weekday() -> None:
+    with pytest.raises(RuleValidationError, match="must not set"):
+        ScheduleRule(
+            rule_type=RuleType.DAILY, nth_weekday_weekday=0,
+            start_date="2026-09-01",
+        )
+
+
+def test_monthly_day_rejects_month() -> None:
+    with pytest.raises(RuleValidationError, match="must not set"):
+        ScheduleRule(
+            rule_type=RuleType.MONTHLY_DAY, day_of_month=5, month=3,
+            start_date="2026-09-01",
+        )
+
+
+def test_yearly_rejects_day_and_nth_fields() -> None:
+    with pytest.raises(RuleValidationError, match="must not set"):
+        ScheduleRule(
+            rule_type=RuleType.YEARLY, month=6, nth_weekday=1,
+            nth_weekday_weekday=0, start_date="2026-09-01",
+        )
+    # YEARLY MAY carry an optional day_of_month (Feb 29 rules need it):
+    rule = ScheduleRule(rule_type=RuleType.YEARLY, month=2,
+                        day_of_month=29, start_date="2026-09-01")
+    assert rule.day_of_month == 29
+    # ...but it is still validated when present.
+    with pytest.raises(RuleValidationError, match="day_of_month"):
+        ScheduleRule(
+            rule_type=RuleType.YEARLY, month=6, day_of_month=32,
+            start_date="2026-09-01",
+        )
+
+
+def test_storage_value_mapping() -> None:
+    """The enum exposes the schema's CHECK values via storage_value."""
+    assert RuleType.DAILY.storage_value == "daily"
+    assert RuleType.WEEKLY.storage_value == "weekly"
+    assert RuleType.MONTHLY_DAY.storage_value == "monthly"
+    assert RuleType.MONTHLY_WEEKDAY.storage_value == "monthly"
+    assert RuleType.YEARLY.storage_value == "yearly"
+    assert RuleType.CUSTOM_DAYS.storage_value == "custom"
+
+
+def test_from_dict_bool_weekday_entry_rejected_before_set_coercion() -> None:
+    """[0, False] must NOT collapse False into 0 via set() coercion."""
+    with pytest.raises(RuleValidationError, match="plain integers"):
+        ScheduleRule.from_dict(
+            {"rule_type": "weekly", "weekday_set": [0, False]}
+        )
+
+
+def test_direct_set_with_int_subclass_entry_rejected() -> None:
+    """A hashable-but-non-plain int subclass is still not a plain int."""
+
+    class IntChild(int):
+        pass
+
+    with pytest.raises(RuleValidationError, match="plain integers"):
+        ScheduleRule(
+            rule_type=RuleType.WEEKLY,
+            weekday_set={IntChild(1)},
+            start_date="2026-09-01",
+        )
+
+
+def test_from_dict_with_int_subclass_entry_rejected() -> None:
+    class IntChild(int):
+        pass
+
+    with pytest.raises(RuleValidationError, match="plain integers"):
+        ScheduleRule.from_dict(
+            {"rule_type": "weekly", "weekday_set": [IntChild(1)]}
+        )
+
+
+def test_from_dict_unknown_key_types_never_leak_typeerror() -> None:
+    """Mixed unhashable unknown keys must surface as
+    RuleValidationError, not a raw TypeError from sorting."""
+    with pytest.raises(RuleValidationError, match="unknown rule fields"):
+        ScheduleRule.from_dict({"rule_type": "daily", 42: "value"})
+
+
+def test_storage_aliases_and_whitespace_are_not_model_names() -> None:
+    """The model boundary is exact names only: storage strings, padded
+    and case variants are all rejected."""
+    for bad in ("monthly", "custom", " DAILY ", "daily ", "DAILY"):
+        with pytest.raises(RuleValidationError):
+            ScheduleRule(rule_type=bad, start_date="2026-09-01")
+
+
+def test_unhashable_int_subclass_interval_rejected() -> None:
+    class UnhashableInt(int):
+        __hash__ = None
+
+    with pytest.raises(RuleValidationError):
+        ScheduleRule(
+            rule_type=RuleType.DAILY,
+            interval=UnhashableInt(1),
+            start_date="2026-09-01",
+        )
+
+
+def test_rule_type_is_a_dataclass() -> None:
+    import dataclasses
+
+    assert dataclasses.is_dataclass(ScheduleRule)

tests/test_recurrence_monthly_day.py

@@ -0,0 +1,301 @@
+"""Tests for MONTHLY_DAY recurrence with the month-end clamp policy."""
+from __future__ import annotations
+
+import datetime
+
+import pytest
+
+from custom_components.nestquest.recurrence import (
+    RuleType,
+    RuleValidationError,
+    ScheduleRule,
+    _month_end,
+    occurs_on,
+    occurrences_between,
+)
+
+
+def _d(date: str) -> datetime.date:
+    return datetime.date.fromisoformat(date)
+
+
+# ---------------------------------------------------------------------------
+# Nominal-day firings
+# ---------------------------------------------------------------------------
+
+
+def test_monthly_day_15_fires_on_the_15th() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=15,
+        start_date="2026-01-15",
+    )
+    for month in range(1, 13):
+        assert occurs_on(rule, datetime.date(2026, month, 15)) is True
+    assert occurs_on(rule, datetime.date(2026, 1, 14)) is False
+    assert occurs_on(rule, datetime.date(2026, 1, 16)) is False
+
+
+def test_monthly_day_never_fires_on_other_days() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=10,
+        start_date="2026-01-10",
+    )
+    # Every non-10th day of a short month must be False.
+    for day in range(1, 30):
+        if day != 10:
+            assert occurs_on(
+                rule, datetime.date(2026, 1, day)
+            ) is False, f"Jan {day}"
+
+
+def test_monthly_day_never_fires_before_start() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=15,
+        start_date="2026-03-15",
+    )
+    # Feb 15 precedes the start: no firing even though the day matches.
+    assert occurs_on(rule, datetime.date(2026, 2, 15)) is False
+    assert occurs_on(rule, datetime.date(2026, 3, 15)) is True
+
+
+def test_monthly_day_respects_end_date() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=15,
+        start_date="2026-01-15", end_date="2026-06-15",
+    )
+    assert occurs_on(rule, datetime.date(2026, 6, 15)) is True
+    assert occurs_on(rule, datetime.date(2026, 7, 15)) is False
+
+
+# ---------------------------------------------------------------------------
+# Month-end clamp policy
+# ---------------------------------------------------------------------------
+
+
+def test_month_end_helper_is_leap_aware() -> None:
+    assert _month_end(2026, 1) == 31
+    assert _month_end(2026, 4) == 30
+    assert _month_end(2026, 2) == 28   # non-leap
+    assert _month_end(2028, 2) == 29   # leap
+    assert _month_end(2000, 2) == 29   # divisible-by-400 leap
+    assert _month_end(1900, 2) == 28   # divisible-by-100 non-leap
+    assert _month_end(2026, 12) == 31
+
+
+def test_day_31_clamps_to_month_end_in_shorter_months() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=31,
+        start_date="2026-01-31",
+    )
+    # Months with a 31st: fire on the 31st.
+    for month, day in ((1, 31), (3, 31), (5, 31), (7, 31), (8, 31),
+                       (10, 31), (12, 31)):
+        assert occurs_on(rule, datetime.date(2026, month, day)) is True, (
+            f"2026-{month:02d}-{day:02d}"
+        )
+    # Months WITHOUT a 31st: fire on the month's last day.
+    assert occurs_on(rule, datetime.date(2026, 4, 30)) is True   # Apr 30
+    assert occurs_on(rule, datetime.date(2026, 6, 30)) is True   # Jun 30
+    assert occurs_on(rule, datetime.date(2026, 9, 30)) is True   # Sep 30
+    assert occurs_on(rule, datetime.date(2026, 11, 30)) is True  # Nov 30
+    # The day BEFORE the clamp is not a firing.
+    assert occurs_on(rule, datetime.date(2026, 4, 29)) is False
+    assert occurs_on(rule, datetime.date(2026, 4, 28)) is False
+
+
+def test_day_30_clamps_to_feb_28_non_leap() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=30,
+        start_date="2026-01-30",
+    )
+    assert occurs_on(rule, datetime.date(2026, 2, 28)) is True   # clamp
+    # No Feb 29 in 2026 (non-leap): nothing else in February fires.
+    assert occurs_on(rule, datetime.date(2026, 2, 27)) is False
+    # Months with a 30th: nominal day.
+    assert occurs_on(rule, datetime.date(2026, 4, 30)) is True
+    assert occurs_on(rule, datetime.date(2026, 3, 30)) is True
+
+
+def test_day_29_feb_clamps_to_28_non_leap_and_29_leap() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=29,
+        start_date="2026-01-29",
+    )
+    # Non-leap 2027: Feb 28 is the clamp.
+    assert occurs_on(rule, datetime.date(2027, 2, 28)) is True
+    assert occurs_on(rule, datetime.date(2027, 2, 27)) is False
+    # Leap 2028: Feb 29 exists — nominal day.
+    assert occurs_on(rule, datetime.date(2028, 2, 29)) is True
+    assert occurs_on(rule, datetime.date(2028, 2, 28)) is False
+
+
+def test_day_30_feb_leap_year_clamps_to_29() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=30,
+        start_date="2027-01-30",
+    )
+    # Leap 2028: Feb has 29 days; the 30th clamps to Feb 29.
+    assert occurs_on(rule, datetime.date(2028, 2, 29)) is True
+    # Non-leap 2026 (rule starts 2027 so Feb 2027 is non-leap): clamps 28.
+    assert occurs_on(rule, datetime.date(2027, 2, 28)) is True
+
+
+# ---------------------------------------------------------------------------
+# Full-year sweeps: the 29th, 30th, 31st across leap and non-leap years
+# ---------------------------------------------------------------------------
+
+
+def _sweep_monthly(rule: ScheduleRule, year: int) -> list[tuple[int, int]]:
+    """Every (month, day) in ``rule``'s year the engine says fires."""
+    hits = []
+    for month in range(1, 13):
+        for day in range(1, _month_end(year, month) + 1):
+            if occurs_on(rule, datetime.date(year, month, day)):
+                hits.append((month, day))
+    return hits
+
+
+def test_full_year_day_31_sweep_2026() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=31,
+        start_date="2026-01-31",
+    )
+    # Exactly one firing per month, twelve total.
+    hits = _sweep_monthly(rule, 2026)
+    assert len(hits) == 12
+    by_month = dict(hits)
+    assert (by_month[1], by_month[2], by_month[4]) == (31, 28, 30)
+    assert by_month[2] == 28  # 2026 Feb is non-leap
+    assert by_month[4] == 30
+    # Each month has exactly one firing.
+    months = [month for month, _ in hits]
+    assert months == list(range(1, 13))
+
+
+def test_full_year_day_31_sweep_2028_leap() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=31,
+        start_date="2028-01-31",
+    )
+    hits = _sweep_monthly(rule, 2028)
+    by_month = dict(hits)
+    assert by_month[2] == 29  # leap February clamps to the 29th
+    assert by_month[4] == 30
+    months = [month for month, _ in hits]
+    assert months == list(range(1, 13))
+
+
+def test_full_year_day_30_sweep_2028() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=30,
+        start_date="2028-01-30",
+    )
+    hits = _sweep_monthly(rule, 2028)
+    by_month = dict(hits)
+    assert by_month[2] == 29  # leap Feb clamps to 29
+    months = [month for month, _ in hits]
+    assert months == list(range(1, 13))
+
+
+def test_full_year_day_29_sweep_2027_non_leap() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=29,
+        start_date="2027-01-29",
+    )
+    hits = _sweep_monthly(rule, 2027)
+    by_month = dict(hits)
+    assert by_month[2] == 28  # non-leap Feb clamps to the 28th
+    months = [month for month, _ in hits]
+    assert months == list(range(1, 13))
+
+
+def test_full_year_day_29_sweep_2028_leap() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=29,
+        start_date="2028-01-29",
+    )
+    hits = _sweep_monthly(rule, 2028)
+    by_month = dict(hits)
+    assert by_month[2] == 29  # leap Feb has a real 29th: nominal day
+    months = [month for month, _ in hits]
+    assert months == list(range(1, 13))
+
+
+# ---------------------------------------------------------------------------
+# Interval arithmetic
+# ---------------------------------------------------------------------------
+
+
+def test_monthly_interval_2_every_second_month() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=15, interval=2,
+        start_date="2026-01-15",
+    )
+    assert occurs_on(rule, datetime.date(2026, 1, 15)) is True
+    assert occurs_on(rule, datetime.date(2026, 2, 15)) is False
+    assert occurs_on(rule, datetime.date(2026, 3, 15)) is True
+    assert occurs_on(rule, datetime.date(2026, 4, 15)) is False
+    assert occurs_on(rule, datetime.date(2026, 5, 15)) is True
+
+
+def test_monthly_interval_2_clamped_month_end_alignment() -> None:
+    """Interval counts nominal months from the anchor: Jan 31 anchor,
+    every 2 months -> Jan (m0), Mar (m2), May (m4), Jul (m6).  Clamped
+    firings (Feb 28, Apr 30, Jun 30) occur only in interval months."""
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=31, interval=2,
+        start_date="2026-01-31",
+    )
+    assert occurs_on(rule, datetime.date(2026, 1, 31)) is True   # m0
+    assert occurs_on(rule, datetime.date(2026, 2, 28)) is False  # m1
+    assert occurs_on(rule, datetime.date(2026, 3, 31)) is True   # m2
+    assert occurs_on(rule, datetime.date(2026, 4, 30)) is False  # m3
+    assert occurs_on(rule, datetime.date(2026, 5, 31)) is True   # m4
+    assert occurs_on(rule, datetime.date(2026, 6, 30)) is False  # m5
+    assert occurs_on(rule, datetime.date(2026, 7, 31)) is True   # m6
+
+
+def test_monthly_interval_arithmetic_across_year_boundary() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=15, interval=2,
+        start_date="2026-11-15",
+    )
+    # Nov (m0) fires, Jan 2027 (m2) fires, Mar (m4) fires.
+    assert occurs_on(rule, datetime.date(2026, 11, 15)) is True
+    assert occurs_on(rule, datetime.date(2026, 12, 15)) is False
+    assert occurs_on(rule, datetime.date(2027, 1, 15)) is True
+    assert occurs_on(rule, datetime.date(2027, 2, 15)) is False
+    assert occurs_on(rule, datetime.date(2027, 3, 15)) is True
+
+
+def test_monthly_day_occurrences_between_span() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=31,
+        start_date="2026-01-31",
+    )
+    result = occurrences_between(rule, "2026-01-01", "2026-06-30")
+    assert result == [
+        "2026-01-31", "2026-02-28", "2026-03-31", "2026-04-30",
+        "2026-05-31", "2026-06-30",
+    ]
+
+
+def test_monthly_day_occurrences_between_rejects_inverted() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=15,
+        start_date="2026-01-15",
+    )
+    with pytest.raises(RuleValidationError, match="on or after"):
+        occurrences_between(rule, "2026-06-15", "2026-01-15")
+
+
+def test_monthly_day_clamp_is_one_firing_per_month(tmp_path) -> None:
+    """The clamp must not double-fire: only one day per month matches."""
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_DAY, day_of_month=31,
+        start_date="2026-01-31",
+    )
+    hits = _sweep_monthly(rule, 2026)
+    months = [month for month, _ in hits]
+    assert months == sorted(months)
+    assert len(months) == len(set(months)) == 12

tests/test_recurrence_monthly_weekday.py

@@ -0,0 +1,324 @@
+"""Tests for MONTHLY_WEEKDAY (nth-weekday-of-month) recurrence."""
+from __future__ import annotations
+
+import datetime
+
+import pytest
+
+from custom_components.nestquest.recurrence import (
+    RuleType,
+    RuleValidationError,
+    ScheduleRule,
+    _nth_weekday_of_month,
+    occurs_on,
+    occurrences_between,
+)
+
+
+def _d(date: str) -> datetime.date:
+    return datetime.date.fromisoformat(date)
+
+
+# ---------------------------------------------------------------------------
+# The helper: nth occurrence day-of-month
+# ---------------------------------------------------------------------------
+
+
+def test_nth_weekday_helper_first_second_third() -> None:
+    """Independently verified: Mar 1 2026 is a Sunday; Wednesdays are
+    4, 11, 18, 25 (four — no 5th)."""
+    assert _nth_weekday_of_month(2026, 3, 2, 1) == 4
+    assert _nth_weekday_of_month(2026, 3, 2, 2) == 11
+    assert _nth_weekday_of_month(2026, 3, 2, 3) == 18
+    assert _nth_weekday_of_month(2026, 3, 2, 4) == 25
+    assert _nth_weekday_of_month(2026, 3, 2, 5) is None
+
+
+def test_nth_weekday_helper_none_when_month_lacks_nth() -> None:
+    """Independently verified: April 2026 Fridays are 3, 10, 17, 24
+    (four) — no 5th occurrence."""
+    assert _nth_weekday_of_month(2026, 4, 4, 5) is None
+
+
+def test_nth_weekday_helper_last() -> None:
+    """Independently verified: March 2026 Fridays are 6, 13, 20, 27;
+    February 2028 Saturdays are 5, 12, 19, 26 (Feb 29 2028 is a
+    Tuesday, so the last SATURDAY is the 26th, not the last day)."""
+    assert _nth_weekday_of_month(2026, 3, 4, -1) == 27
+    # February 2026: 28 days, last day IS a Saturday.
+    assert _nth_weekday_of_month(2026, 2, 5, -1) == 28
+    # February 2028: the last Saturday is the 26th (Feb 29 is a Tue).
+    assert _nth_weekday_of_month(2028, 2, 5, -1) == 26
+
+
+def test_nth_weekday_helper_last_when_month_has_five() -> None:
+    """Independently verified: October 2026 Fridays are 2, 9, 16, 23, 30
+    (five) — the last is the 30th, and the 5th is the same day."""
+    assert _nth_weekday_of_month(2026, 10, 4, -1) == 30
+    assert _nth_weekday_of_month(2026, 10, 4, 5) == 30
+
+
+def test_helper_zero_and_out_of_range_nth() -> None:
+    """The model rejects n 0 before the helper sees it; -1/1..5 only."""
+    with pytest.raises(RuleValidationError):
+        ScheduleRule(
+            rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=0,
+            nth_weekday_weekday=1, start_date="2026-09-01",
+        )
+    with pytest.raises(RuleValidationError):
+        ScheduleRule(
+            rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=6,
+            nth_weekday_weekday=1, start_date="2026-09-01",
+        )
+
+
+# ---------------------------------------------------------------------------
+# occurs_on: first / second / third / fourth / last across 12 months
+# ---------------------------------------------------------------------------
+
+
+def test_first_tuesday_of_twelve_consecutive_months() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=1,
+        nth_weekday_weekday=2,  # first Wednesday
+        start_date="2026-01-01",
+    )
+    first_wednesdays = [
+        (1, 7), (2, 4), (3, 4), (4, 1), (5, 6), (6, 3),
+        (7, 1), (8, 5), (9, 2), (10, 7), (11, 4), (12, 2),
+    ]
+    for month, day in first_wednesdays:
+        date = datetime.date(2026, month, day)
+        assert date.weekday() == 2
+        assert occurs_on(rule, date) is True, date
+        # Neighbors do not fire.
+        assert occurs_on(rule, date - datetime.timedelta(days=7)) is False
+        assert occurs_on(rule, date + datetime.timedelta(days=7)) is False
+
+
+def test_second_wednesday_of_twelve_consecutive_months() -> None:
+    """Independently verified second Wednesdays of 2026 (Jan 1 is a
+    Thursday; Wednesdays per month from the calendar): the SECOND one
+    fires; the FIRST one does not."""
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=2,
+        nth_weekday_weekday=2,
+        start_date="2026-01-01",
+    )
+    # (month, first_wed, second_wed) from plain datetime arithmetic:
+    verified = [
+        (1, 7, 14), (2, 4, 11), (3, 4, 11), (4, 1, 8),
+        (5, 6, 13), (6, 3, 10), (7, 1, 8), (8, 5, 12),
+        (9, 2, 9), (10, 7, 14), (11, 4, 11), (12, 2, 9),
+    ]
+    for month, first_wed, second_wed in verified:
+        assert _d(f"2026-{month:02d}-{first_wed:02d}").weekday() == 2
+        assert _d(f"2026-{month:02d}-{second_wed:02d}").weekday() == 2
+        assert occurs_on(
+            rule, datetime.date(2026, month, second_wed)
+        ) is True, f"2026-{month:02d}-{second_wed:02d}"
+        assert occurs_on(
+            rule, datetime.date(2026, month, first_wed)
+        ) is False, f"2026-{month:02d}-{first_wed:02d}"
+
+
+def test_third_friday_of_twelve_consecutive_months() -> None:
+    """Independently verified 2026 Fridays; the THIRD fires, the SECOND
+    does not."""
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=3,
+        nth_weekday_weekday=4,
+        start_date="2026-01-01",
+    )
+    verified = [
+        # (month, first_fri, second_fri, third_fri)
+        (1, 2, 9, 16), (2, 6, 13, 20), (3, 6, 13, 20),
+        (4, 3, 10, 17), (5, 1, 8, 15), (6, 5, 12, 19),
+        (7, 3, 10, 17), (8, 7, 14, 21), (9, 4, 11, 18),
+        (10, 2, 9, 16), (11, 6, 13, 20), (12, 4, 11, 18),
+    ]
+    for month, first_fri, second_fri, third_fri in verified:
+        third = _d(f"2026-{month:02d}-{third_fri:02d}")
+        assert third.weekday() == 4
+        assert occurs_on(rule, third) is True, third
+        second = _d(f"2026-{month:02d}-{second_fri:02d}")
+        assert occurs_on(rule, second) is False, second
+
+
+def test_fourth_wednesday_of_twelve_consecutive_months() -> None:
+    """Independently verified 2026 Wednesdays; the FOURTH fires, the
+    THIRD does not."""
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=4,
+        nth_weekday_weekday=2,
+        start_date="2026-01-01",
+    )
+    verified = [
+        # (month, third_wed, fourth_wed)
+        (1, 21, 28), (2, 18, 25), (3, 18, 25), (4, 15, 22),
+        (5, 20, 27), (6, 17, 24), (7, 15, 22), (8, 19, 26),
+        (9, 16, 23), (10, 21, 28), (11, 18, 25), (12, 16, 23),
+    ]
+    for month, third_wed, fourth_wed in verified:
+        fourth = _d(f"2026-{month:02d}-{fourth_wed:02d}")
+        assert fourth.weekday() == 2
+        assert occurs_on(rule, fourth) is True, fourth
+        third = _d(f"2026-{month:02d}-{third_wed:02d}")
+        assert not occurs_on(rule, third), third
+
+
+def test_last_monday_of_twelve_consecutive_months() -> None:
+    """Independently verified last Mondays of 2026; each fires, and the
+    PREVIOUS Monday of the same month does not (it is nth-from-last
+    minus one, i.e. the second-to-last)."""
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=-1,
+        nth_weekday_weekday=0,
+        start_date="2026-01-01",
+    )
+    verified = [
+        # (month, last_monday)
+        (1, 26), (2, 23), (3, 30), (4, 27), (5, 25), (6, 29),
+        (7, 27), (8, 31), (9, 28), (10, 26), (11, 30), (12, 28),
+    ]
+    for month, last_monday in verified:
+        date = _d(f"2026-{month:02d}-{last_monday:02d}")
+        assert date.weekday() == 0
+        assert occurs_on(rule, date) is True, date
+        # The Monday a week earlier is NOT the last: must not fire.
+        week_before = date - datetime.timedelta(days=7)
+        if week_before.month == month:
+            assert occurs_on(rule, week_before) is False, week_before
+
+
+# ---------------------------------------------------------------------------
+# The five-occurrence skip: a 5th on a four-occurrence month
+# ---------------------------------------------------------------------------
+
+
+def test_fifth_weekday_skips_four_occurrence_months() -> None:
+    """Independently verified: 2026 months with FIVE Mondays are March,
+    June, August, November — every other month (four Mondays) must fire
+    on no day at all, and each hit must be the month's FINAL Monday."""
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=5,
+        nth_weekday_weekday=0,  # fifth Monday
+        start_date="2026-01-01",
+    )
+    five_monday_months = {3: 30, 6: 29, 8: 31, 11: 30}
+    hit_months = set()
+    for month in range(1, 13):
+        if month in five_monday_months:
+            day = five_monday_months[month]
+            hit = _d(f"2026-{month:02d}-{day:02d}")
+            assert hit.weekday() == 0
+            assert occurs_on(rule, hit) is True, hit
+            # It is the FINAL Monday of the month: sweep EVERY remaining
+            # calendar day after the hit — none may fire.
+            for later in range(day + 1, _month_end_days(2026, month) + 1):
+                assert occurs_on(
+                    rule, datetime.date(2026, month, later)
+                ) is False, f"2026-{month:02d}-{later:02d}"
+            # The Monday a week earlier is not the fifth.
+            assert occurs_on(
+                rule, datetime.date(2026, month, day - 7)
+            ) is False
+            hit_months.add(month)
+        else:
+            for day in range(1, _month_end_days(2026, month) + 1):
+                assert occurs_on(
+                    rule, datetime.date(2026, month, day)
+                ) is False, f"2026-{month:02d}-{day:02d}"
+    assert hit_months == {3, 6, 8, 11}, hit_months
+
+
+def _month_end_days(year: int, month: int) -> int:
+    if month == 12:
+        return 31
+    return (datetime.date(year, month + 1, 1)
+            - datetime.timedelta(days=1)).day
+
+
+def test_fifth_weekday_fires_in_five_occurrence_months() -> None:
+    """Independently verified: October 2026 Fridays are 2, 9, 16, 23, 30
+    (five) — the 5th (and last) is the 30th; April 2026 has four Fridays
+    (3, 10, 17, 24) so no day in April fires."""
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=5,
+        nth_weekday_weekday=4,  # Fridays
+        start_date="2026-01-01",
+    )
+    # October: the 30th fires, is a Friday, and no Monday-later day
+    # (the 31st) is a Friday: the 30th is the FINAL Friday.
+    oct_30 = datetime.date(2026, 10, 30)
+    assert oct_30.weekday() == 4
+    assert occurs_on(rule, oct_30) is True
+    assert occurs_on(rule, datetime.date(2026, 10, 31)) is False
+    # April: no Friday-5th, so no day fires.
+    for day in range(1, _month_end_days(2026, 4) + 1):
+        assert occurs_on(rule, datetime.date(2026, 4, day)) is False, (
+            f"2026-04-{day:02d}"
+        )
+
+
+# ---------------------------------------------------------------------------
+# Interval arithmetic
+# ---------------------------------------------------------------------------
+
+
+def test_monthly_weekday_interval_2() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=2,
+        nth_weekday_weekday=2, interval=2, start_date="2026-01-01",
+    )
+    # Jan (m0) fires on the 2nd Tuesday; Feb (m1) skipped; Mar (m2) fires.
+    jan_second = _nth_weekday_of_month(2026, 1, 2, 2)
+    assert occurs_on(rule, datetime.date(2026, 1, jan_second)) is True
+    feb_second = _nth_weekday_of_month(2026, 2, 2, 2)
+    assert occurs_on(rule, datetime.date(2026, 2, feb_second)) is False
+    mar_second = _nth_weekday_of_month(2026, 3, 2, 2)
+    assert occurs_on(rule, datetime.date(2026, 3, mar_second)) is True
+
+
+def test_monthly_weekday_interval_across_year_boundary() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=1,
+        nth_weekday_weekday=2, interval=3, start_date="2026-11-01",
+    )
+    # Nov (m0) fires; Dec (m1), Jan (m2) skip; Feb (m3) fires.
+    nov_first = _nth_weekday_of_month(2026, 11, 2, 1)
+    assert occurs_on(rule, datetime.date(2026, 11, nov_first)) is True
+    dec_first = _nth_weekday_of_month(2026, 12, 2, 1)
+    assert occurs_on(rule, datetime.date(2026, 12, dec_first)) is False
+    jan_first = _nth_weekday_of_month(2027, 1, 2, 1)
+    assert occurs_on(rule, datetime.date(2027, 1, jan_first)) is False
+    feb_first = _nth_weekday_of_month(2027, 2, 2, 1)
+    assert occurs_on(rule, datetime.date(2027, 2, feb_first)) is True
+
+
+# ---------------------------------------------------------------------------
+# occurrences_between
+# ---------------------------------------------------------------------------
+
+
+def test_monthly_weekday_occurrences_between_span() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=2,
+        nth_weekday_weekday=4,  # second Friday
+        start_date="2026-01-01", end_date="2026-04-30",
+    )
+    result = occurrences_between(rule, "2026-01-01", "2026-04-30")
+    # Independently verified second Fridays: Jan 9 (Jan 1 Thu), Feb 13
+    # (Feb 1 Sun), Mar 13 (Mar 1 Sun), Apr 10 (Apr 1 Wed).
+    assert result == [
+        "2026-01-09", "2026-02-13", "2026-03-13", "2026-04-10",
+    ]
+
+
+def test_monthly_weekday_occurrences_between_rejects_inverted() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=1,
+        nth_weekday_weekday=1, start_date="2026-01-01",
+    )
+    with pytest.raises(RuleValidationError, match="on or after"):
+        occurrences_between(rule, "2026-06-01", "2026-01-01")

tests/test_recurrence_property.py

@@ -0,0 +1,241 @@
+"""Consistency property tests for occurrences_between vs occurs_on.
+
+The done-condition: occurrences_between returns an ordered, duplicate-
+free list of dates that agrees EXACTLY with calling occurs_on on every
+date in the range.  This module proves that agreement two ways:
+
+1. An exhaustive sweep over a fixed grid of rules (every rule type ×
+   several intervals × several windows) cross-checked date by date.
+
+2. A randomized property test (seeded, deterministic) over randomly
+   generated rules and ranges asserting the two functions never
+   disagree.  Seeds are fixed so failures are reproducible.
+"""
+from __future__ import annotations
+
+import datetime
+import random
+
+import pytest
+
+from custom_components.nestquest.recurrence import (
+    RuleType,
+    RuleValidationError,
+    ScheduleRule,
+    occurs_on,
+    occurrences_between,
+)
+
+
+def _d(date: str) -> datetime.date:
+    return datetime.date.fromisoformat(date)
+
+
+def _check_consistency(
+    rule: ScheduleRule, start: str, end: str
+) -> list[str]:
+    """Cross-check occurrences_between against per-date occurs_on.
+
+    Returns the occurrences_between result after asserting, for every
+    date in [start, end]: occurs_on(date) is True iff the date appears
+    in the result.  Also asserts the list is ordered and duplicate-free.
+    """
+    result = occurrences_between(rule, start, end)
+    # Ordered and duplicate-free.
+    assert result == sorted(set(result)), (
+        f"occurrences_between must be ordered, duplicate-free: {result}"
+    )
+    # Exact agreement with occurs_on over every date in the range.
+    cursor = _d(start)
+    end_date = _d(end)
+    expected: list[str] = []
+    while cursor <= end_date:
+        if occurs_on(rule, cursor):
+            expected.append(cursor.isoformat())
+        cursor += datetime.timedelta(days=1)
+    assert result == expected, (
+        f"disagreement for {rule.to_dict()} over [{start}, {end}]:\n"
+        f"  occurrences_between: {result}\n"
+        f"  per-date occurs_on:  {expected}"
+    )
+    return result
+
+
+# ---------------------------------------------------------------------------
+# Exhaustive grid over rule shapes, intervals and windows
+# ---------------------------------------------------------------------------
+
+
+_GRID_RULES = [
+    ScheduleRule(rule_type=RuleType.DAILY, start_date="2026-03-01"),
+    ScheduleRule(rule_type=RuleType.DAILY, interval=2,
+                 start_date="2026-03-01"),
+    ScheduleRule(rule_type=RuleType.DAILY, interval=3,
+                 start_date="2026-03-15", end_date="2027-01-31"),
+    ScheduleRule(rule_type=RuleType.WEEKLY, weekday_set={0, 2, 4},
+                 start_date="2026-03-03"),
+    ScheduleRule(rule_type=RuleType.WEEKLY, weekday_set={1, 3, 5},
+                 interval=2, start_date="2026-03-06",
+                 end_date="2027-06-30"),
+    ScheduleRule(rule_type=RuleType.CUSTOM_DAYS, weekday_set={1, 4},
+                 start_date="2026-03-06"),
+    ScheduleRule(rule_type=RuleType.MONTHLY_DAY, day_of_month=31,
+                 start_date="2026-03-31"),
+    ScheduleRule(rule_type=RuleType.MONTHLY_DAY, day_of_month=29,
+                 interval=2, start_date="2026-01-29"),
+    ScheduleRule(rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=2,
+                 nth_weekday_weekday=1, start_date="2026-03-03"),
+    ScheduleRule(rule_type=RuleType.MONTHLY_WEEKDAY, nth_weekday=-1,
+                 nth_weekday_weekday=4, start_date="2026-03-06",
+                 end_date="2028-12-31"),
+    ScheduleRule(rule_type=RuleType.YEARLY, month=2, day_of_month=29,
+                 start_date="2026-02-28"),
+    ScheduleRule(rule_type=RuleType.YEARLY, month=6, day_of_month=15,
+                 interval=2, start_date="2026-06-15"),
+]
+
+_GRID_WINDOWS = [
+    ("2026-01-01", "2026-03-31"),   # mostly before start
+    ("2026-03-01", "2026-03-01"),   # single day == anchor
+    ("2026-03-01", "2026-03-31"),   # within window
+    ("2026-08-01", "2026-08-31"),   # mid-window month
+    ("2026-12-01", "2027-01-31"),   # year boundary
+    ("2028-01-01", "2028-03-01"),   # leap year span
+    ("2029-01-01", "2029-12-31"),   # far out
+]
+
+
+@pytest.mark.parametrize(
+    "rule_index,window_index",
+    [
+        (rule_index, window_index)
+        for rule_index in range(len(_GRID_RULES))
+        for window_index in range(len(_GRID_WINDOWS))
+    ],
+)
+def test_grid_occurrences_between_matches_occurs_on(
+    rule_index, window_index
+) -> None:
+    rule = _GRID_RULES[rule_index]
+    window = _GRID_WINDOWS[window_index]
+    _check_consistency(rule, window[0], window[1])
+
+
+# ---------------------------------------------------------------------------
+# Randomized property test (seeded, deterministic)
+# ---------------------------------------------------------------------------
+
+
+def _generate_random_rules(rng: random.Random, count: int) -> list[ScheduleRule]:
+    """Random but valid rules: shapes, intervals, anchors, windows.
+
+    Any random combo the model rejects (genuinely invalid combinations)
+    is skipped — the property only needs VALID rules; the assertion at
+    the end bounds how many may be rejected.
+    """
+    rules: list[ScheduleRule] = []
+    shapes = list(RuleType)
+    for index in range(count):
+        shape = shapes[rng.randrange(len(shapes))]
+        interval = rng.choice([1, 2, 3, 7])
+        anchor = datetime.date(
+            rng.choice([2024, 2025, 2026, 2027, 2028]),
+            rng.randrange(1, 13),
+            rng.randrange(1, 28),
+        )
+        end_offset = rng.choice([45, 120, 400, 800])
+        end = anchor + datetime.timedelta(days=end_offset)
+        common = {
+            "rule_type": shape,
+            "interval": interval,
+            "start_date": anchor.isoformat(),
+            "end_date": end.isoformat(),
+        }
+        try:
+            if shape is RuleType.WEEKLY:
+                rules.append(ScheduleRule(
+                    **common,
+                    weekday_set={
+                        rng.randrange(7)
+                        for _ in range(rng.randrange(1, 4))
+                    },
+                ))
+            elif shape is RuleType.CUSTOM_DAYS:
+                rules.append(ScheduleRule(
+                    **common,
+                    weekday_set={
+                        rng.randrange(7)
+                        for _ in range(rng.randrange(1, 4))
+                    },
+                ))
+            elif shape is RuleType.MONTHLY_DAY:
+                rules.append(ScheduleRule(
+                    **common, day_of_month=rng.randrange(1, 32)
+                ))
+            elif shape is RuleType.MONTHLY_WEEKDAY:
+                rules.append(ScheduleRule(
+                    **common,
+                    nth_weekday=rng.choice([-1, 1, 2, 3, 4, 5]),
+                    nth_weekday_weekday=rng.randrange(7),
+                ))
+            elif shape is RuleType.YEARLY:
+                rules.append(ScheduleRule(
+                    **common,
+                    month=rng.randrange(1, 13),
+                    day_of_month=rng.choice([None, rng.randrange(1, 29)]),
+                ))
+            else:  # DAILY
+                rules.append(ScheduleRule(**common))
+        except RuleValidationError:
+            continue
+    assert len(rules) >= count * 3 // 4, "too many random rules rejected"
+    return rules
+
+
+def test_random_rules_never_disagree_between_the_two_functions() -> None:
+    """The property: for randomly generated rules and ranges,
+    occurrences_between(rule, s, e) == [d for d in s..e if occurs_on]."""
+    rng = random.Random(0xFEA7)  # fixed seed for reproducibility
+    rules = _generate_random_rules(rng, count=40)
+    checked = 0
+    for rule in rules:
+        anchor = datetime.date.fromisoformat(rule.start_date)
+        for offset_days, span_days in ((-30, 60), (0, 14), (10, 90),
+                                       (100, 200)):
+            range_start = anchor + datetime.timedelta(days=offset_days)
+            range_end = range_start + datetime.timedelta(days=span_days)
+            _check_consistency(
+                rule, range_start.isoformat(), range_end.isoformat()
+            )
+            checked += 1
+    assert checked >= 40 * 4 * 3 // 4, checked
+
+
+def test_random_ranges_include_year_and_leap_spans() -> None:
+    """Every rule is probed across a range that spans Jan 1 (twice) and
+    a February 29 — the Dec 1..Mar 1 two-year span always covers both."""
+    rng = random.Random(42)
+    rules = _generate_random_rules(rng, count=12)
+    for rule in rules:
+        anchor = datetime.date.fromisoformat(rule.start_date)
+        probe_start = datetime.date(anchor.year, 12, 1)
+        probe_end = datetime.date(anchor.year + 2, 3, 1)
+        # The span always contains two Jan 1s and a Feb 28/29 pair.
+        assert probe_end >= probe_start
+        _check_consistency(
+            rule, probe_start.isoformat(), probe_end.isoformat()
+        )
+        # An additional probe pinned ON a Feb 29 window (2028 leap):
+        # every rule is cross-checked over Feb 1 - Mar 15 2028.
+        _check_consistency(rule, "2028-02-01", "2028-03-15")
+
+
+def test_occurrences_between_single_day_range_matches_occurs_on() -> None:
+    """A one-day range is the exact occurs_on of that date."""
+    for rule in _GRID_RULES:
+        for day in ("2026-03-05", "2026-03-15", "2027-06-15"):
+            single = occurrences_between(rule, day, day)
+            if occurs_on(rule, _d(day)):
+                assert single == [day], (rule.to_dict(), day, single)
+            else:
+                assert single == [], (rule.to_dict(), day, single)

tests/test_recurrence_weekly.py

@@ -0,0 +1,299 @@
+"""Tests for weekly and custom day-set recurrence evaluation."""
+from __future__ import annotations
+
+import datetime
+
+import pytest
+
+from custom_components.nestquest.recurrence import (
+    RuleType,
+    RuleValidationError,
+    ScheduleRule,
+    occurs_on,
+    occurrences_between,
+)
+
+
+def _d(date: str) -> datetime.date:
+    return datetime.date.fromisoformat(date)
+
+
+# 2026-09-01 is a TUESDAY (weekday 1).
+# Anchor-week arithmetic (start_date = Tue Sep 1): week 0 spans
+# Sep 1-7, week 1 spans Sep 8-14, week 2 spans Sep 15-21, and so on —
+# whole weeks elapsed from the anchor, NOT calendar-Sunday weeks.
+# A weekly Mon/Wed/Fri (0,2,4) rule at interval 1 anchored here fires
+# on Wed Sep 2, Fri Sep 4 (both week 0), then Mon Sep 7, Wed Sep 9,
+# Fri Sep 11 (week 1), Mon Sep 14... (week 2), and so on.
+
+
+def test_weekly_fires_exactly_on_set_weekdays() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={0, 2, 4},
+        start_date="2026-09-01",
+    )
+    # Sep 2026: 1=Tue(1), 2=Wed(2), 4=Fri(4), 7=Mon(0), 8=Tue(1), 9=Wed,
+    # 10=Thu(3), 11=Fri(4)
+    assert occurs_on(rule, _d("2026-09-01")) is False  # Tue NOT in set
+    assert occurs_on(rule, _d("2026-09-02")) is True   # Wed in set
+    assert occurs_on(rule, _d("2026-09-03")) is False  # Thu not in set
+    assert occurs_on(rule, _d("2026-09-04")) is True   # Fri in set
+    assert occurs_on(rule, _d("2026-09-05")) is False  # Sat
+    assert occurs_on(rule, _d("2026-09-06")) is False  # Sun
+    assert occurs_on(rule, _d("2026-09-07")) is True   # Mon
+    assert occurs_on(rule, _d("2026-09-08")) is False  # Tue not in set
+
+
+def test_weekly_start_date_itself_may_not_be_in_set() -> None:
+    """start_date anchors the WEEK count, not a firing day: a Tuesday
+    start for a Mon/Wed/Fri rule fires on Wed Sep 2, not Sep 1."""
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={0, 2, 4},
+        start_date="2026-09-01",
+    )
+    assert occurs_on(rule, _d("2026-09-01")) is False  # Tue=1, not in set
+    assert occurs_on(rule, _d("2026-09-02")) is True   # Wed=2
+    assert occurs_on(rule, _d("2026-09-04")) is True   # Fri=4
+    assert occurs_on(rule, _d("2026-09-07")) is True   # Mon, next week
+
+
+def test_weekly_never_fires_before_start() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={2},  # Wednesdays
+        start_date="2026-09-02",  # a Wednesday
+    )
+    # Wed Sep 2 is the anchor day and in-set: fires.  Wed Aug 26 (one
+    # week BEFORE the anchor, in-set) must not fire: pre-window.
+    assert occurs_on(rule, _d("2026-09-02")) is True
+    assert occurs_on(rule, _d("2026-08-26")) is False  # Wed, before start
+    assert occurs_on(rule, _d("2026-09-09")) is True   # Wed, next week
+
+
+def test_weekly_respects_end_date() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={0, 2, 4},
+        start_date="2026-09-01", end_date="2026-09-10",
+    )
+    assert occurs_on(rule, _d("2026-09-09")) is True   # Wed within end
+    assert occurs_on(rule, _d("2026-09-11")) is False  # Fri after end
+
+
+def test_weekly_interval_2_fires_on_alternate_weeks() -> None:
+    """Interval 2: fires on set weekdays only in even week-offsets."""
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={0}, interval=2,
+        start_date="2026-09-07",  # a Monday
+    )
+    # Week 0 (Sep 7-13): fires Mon Sep 7.
+    assert occurs_on(rule, _d("2026-09-07")) is True
+    # Week 1 (Sep 14-20): skipped.
+    assert occurs_on(rule, _d("2026-09-14")) is False
+    # Week 2 (Sep 21-27): fires Mon Sep 21.
+    assert occurs_on(rule, _d("2026-09-21")) is True
+    # Week 3 (Sep 28-Oct 4): skipped; Mon Sep 28 does not fire.
+    assert occurs_on(rule, _d("2026-09-28")) is False
+    # Week 4 (Oct 5-11): fires Mon Oct 5.
+    assert occurs_on(rule, _d("2026-10-05")) is True
+
+
+def test_weekly_interval_3_every_third_week() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={3}, interval=3,
+        start_date="2026-09-03",  # a Thursday
+    )
+    assert occurs_on(rule, _d("2026-09-03")) is True   # week 0
+    assert occurs_on(rule, _d("2026-09-10")) is False  # week 1
+    assert occurs_on(rule, _d("2026-09-17")) is False  # week 2
+    assert occurs_on(rule, _d("2026-09-24")) is True   # week 3
+    assert occurs_on(rule, _d("2026-10-15")) is True   # week 6
+
+
+def test_weekly_interval_2_never_fires_in_odd_weeks_across_months() -> None:
+    """The week offset is anchored to start_date across month edges."""
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={2}, interval=2,
+        start_date="2026-09-02",  # a Wednesday
+    )
+    # Week 0: Wed Sep 2 fires. Week 1: Wed Sep 9 skipped.
+    # Week 2: Wed Sep 16 fires. Week 3: Wed Sep 23 skipped.
+    # Week 4 (Oct): Wed Sep 30 fires (still week 4!). Week 5: Wed Oct 7 skipped.
+    assert occurs_on(rule, _d("2026-09-02")) is True
+    assert occurs_on(rule, _d("2026-09-09")) is False
+    assert occurs_on(rule, _d("2026-09-16")) is True
+    assert occurs_on(rule, _d("2026-09-23")) is False
+    assert occurs_on(rule, _d("2026-09-30")) is True
+    assert occurs_on(rule, _d("2026-10-07")) is False
+    assert occurs_on(rule, _d("2026-10-14")) is True
+
+
+def test_custom_days_interval_1_behaves_like_weekly() -> None:
+    """CUSTOM_DAYS with interval 1 behaves identically to WEEKLY."""
+    weekly = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={1, 3, 5},
+        start_date="2026-09-01",
+    )
+    custom = ScheduleRule(
+        rule_type=RuleType.CUSTOM_DAYS, weekday_set={1, 3, 5},
+        start_date="2026-09-01",
+    )
+    # Whole month of September must match exactly.
+    for day in range(1, 31):
+        date = datetime.date(2026, 9, day)
+        assert occurs_on(weekly, date) == occurs_on(custom, date), date
+
+
+def test_custom_days_never_fires_before_start(tmp_path) -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.CUSTOM_DAYS, weekday_set={0},
+        start_date="2026-09-07",  # a Monday
+    )
+    assert occurs_on(rule, _d("2026-09-06")) is False  # Sun, before
+    assert occurs_on(rule, _d("2026-09-07")) is True   # Mon, on
+
+
+def test_hand_checked_8_week_sequence_weekly_mon_wed_fri() -> None:
+    """The done-condition's hand-checked sequence: 8 weeks of a
+    Mon/Wed/Fri rule anchored at Tue 2026-09-01, interval 1.
+
+    Anchor weeks from Tue Sep 1 (week 0 = Sep 1-7, week 1 = Sep 8-14,
+    ... week 7 = Oct 20-26).  Mon/Wed/Fri firings (interval 1, so every
+    in-set weekday fires regardless of week):
+    Week 0 (Sep 1-7):   Wed 2, Fri 4        (Mon Sep 7 is ALSO week 0:
+                        6 elapsed days // 7 = 0 — the anchor week
+                        boundary sits on Sep 8, not on the Monday)
+    Week 1 (Sep 8-14):  Wed 9, Fri 11, Mon 14 (13 elapsed days // 7 = 1)
+    Week 2 (Sep 15-21): Wed 16, Fri 18, Mon 21
+    Week 3 (Sep 22-28): Wed 23, Fri 25, Mon 28
+    Week 4 (Sep 29-Oct 5): Wed 30, Fri 2 (Oct), Mon 5 (Oct)
+    Week 5 (Oct 6-12):  Wed 7, Fri 9, Mon 12
+    Week 6 (Oct 13-19): Wed 14, Fri 16, Mon 19
+    Week 7 (Oct 20-26): Wed 21, Fri 23, Mon 26
+    """
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={0, 2, 4},
+        start_date="2026-09-01", end_date="2026-10-26",
+    )
+    expected = [
+        "2026-09-02", "2026-09-04",
+        "2026-09-07", "2026-09-09", "2026-09-11",
+        "2026-09-14", "2026-09-16", "2026-09-18",
+        "2026-09-21", "2026-09-23", "2026-09-25",
+        "2026-09-28", "2026-09-30", "2026-10-02",
+        "2026-10-05", "2026-10-07", "2026-10-09",
+        "2026-10-12", "2026-10-14", "2026-10-16",
+        "2026-10-19", "2026-10-21", "2026-10-23", "2026-10-26",
+    ]
+    actual = occurrences_between(rule, "2026-09-01", "2026-10-26")
+    assert actual == expected, (
+        f"diff: {set(expected) ^ set(actual)}"
+    )
+
+
+def test_hand_checked_8_week_sequence_weekly_interval_2_mondays() -> None:
+    """8 weeks of a Monday rule at interval 2, anchored Mon 2026-09-07
+    (week 0 = Sep 7-13): fires in weeks 0, 2, 4, 6 -> Mon Sep 7, Mon Sep 21,
+    Mon Oct 5, Mon Oct 19."""
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={0}, interval=2,
+        start_date="2026-09-07", end_date="2026-10-31",
+    )
+    actual = occurrences_between(rule, "2026-09-07", "2026-10-31")
+    assert actual == ["2026-09-07", "2026-09-21", "2026-10-05", "2026-10-19"]
+
+
+def test_custom_days_hand_checked_8_week_sequence() -> None:
+    """8 weeks of a Tue/Thu/Sun rule (1,3,6) anchored Mon 2026-09-07:
+    fires Tue 8, Thu 10, Sun 13, then weekly."""
+    rule = ScheduleRule(
+        rule_type=RuleType.CUSTOM_DAYS, weekday_set={1, 3, 6},
+        start_date="2026-09-07",
+    )
+    # Anchor weeks from Mon Sep 7 (w0 = Sep 7-13 ... w7 = Oct 26-Nov 1):
+    # Tue/Thu/Sun firings per week, 8 weeks fully covered.
+    actual = occurrences_between(rule, "2026-09-07", "2026-11-01")
+    assert actual == [
+        "2026-09-08", "2026-09-10", "2026-09-13",
+        "2026-09-15", "2026-09-17", "2026-09-20",
+        "2026-09-22", "2026-09-24", "2026-09-27",
+        "2026-09-29", "2026-10-01", "2026-10-04",
+        "2026-10-06", "2026-10-08", "2026-10-11",
+        "2026-10-13", "2026-10-15", "2026-10-18",
+        "2026-10-20", "2026-10-22", "2026-10-25",
+        "2026-10-27", "2026-10-29", "2026-11-01",
+    ]
+
+
+def test_weekly_survives_53_week_year_boundary() -> None:
+    """Anchor arithmetic never flips at Jan 1 (the ISO parity trap):
+    a weekly rule spanning New Year keeps its weekday pattern."""
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={3},  # Thursdays
+        start_date="2026-12-24",  # a Thursday
+    )
+    # Thursdays around New Year 2026->2027: Dec 24, 31, Jan 7, 14.
+    assert occurs_on(rule, _d("2026-12-24")) is True
+    assert occurs_on(rule, _d("2026-12-31")) is True
+    assert occurs_on(rule, _d("2027-01-07")) is True
+    assert occurs_on(rule, _d("2027-01-14")) is True
+    # Interleaved days are not Thursdays.
+    assert occurs_on(rule, _d("2027-01-04")) is False  # Monday
+    assert occurs_on(rule, _d("2027-01-05")) is False  # Tuesday
+
+
+def test_weekly_interval_2_across_year_boundary() -> None:
+    """Interval weeks continue across Jan 1 with no parity flip."""
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={4}, interval=2,
+        start_date="2026-12-25",  # a Friday
+    )
+    # Weeks: Dec 25 (w0) fires, Jan 1 (w1) skipped, Jan 8 (w2) fires,
+    # Jan 15 (w3) skipped, Jan 22 (w4) fires.
+    assert occurs_on(rule, _d("2026-12-25")) is True
+    assert occurs_on(rule, _d("2027-01-01")) is False
+    assert occurs_on(rule, _d("2027-01-08")) is True
+    assert occurs_on(rule, _d("2027-01-15")) is False
+    assert occurs_on(rule, _d("2027-01-22")) is True
+
+
+def test_occurrences_between_weekly_disjoint(tmp_path) -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={1},
+        start_date="2026-09-01", end_date="2026-09-30",
+    )
+    assert occurrences_between(rule, "2026-10-01", "2026-10-31") == []
+
+
+def test_occurrences_between_weekly_interval_2_span() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={0}, interval=2,
+        start_date="2026-09-07", end_date="2026-10-31",
+    )
+    result = occurrences_between(rule, "2026-09-01", "2026-10-31")
+    assert result == ["2026-09-07", "2026-09-21", "2026-10-05", "2026-10-19"]
+
+
+def test_occurrences_between_rejects_inverted_bounds() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={0}, start_date="2026-09-01"
+    )
+    with pytest.raises(RuleValidationError, match="on or after"):
+        occurrences_between(rule, "2026-10-10", "2026-09-01")
+
+def test_weekly_interval_2_non_monday_anchor_boundary() -> None:
+    """The anchor week is anchored at start_date, NOT a calendar week:
+    a Tuesday-anchored Monday rule fires on Mon Sep 7 (week 0 extends
+    through Sep 7), skips Mon Sep 14 (week 1), fires Mon Sep 21 (week 2)."""
+    rule = ScheduleRule(
+        rule_type=RuleType.WEEKLY, weekday_set={0}, interval=2,
+        start_date="2026-09-01",  # a TUESDAY anchor
+    )
+    # Sep 7 - Sep 1 = 6 days -> week 0 (even): fires.
+    assert occurs_on(rule, _d("2026-09-07")) is True
+    # Sep 14: 13 days -> week 1 (odd): skipped.
+    assert occurs_on(rule, _d("2026-09-14")) is False
+    # Sep 21: 20 days -> week 2 (even): fires.
+    assert occurs_on(rule, _d("2026-09-21")) is True
+    # Sep 28: 27 days -> week 3 (odd): skipped.
+    assert occurs_on(rule, _d("2026-09-28")) is False
+    # Oct 5: 34 days -> week 4 (even): fires.
+    assert occurs_on(rule, _d("2026-10-05")) is True

tests/test_recurrence_yearly.py

@@ -0,0 +1,233 @@
+"""Tests for YEARLY recurrence with the leap-day clamp policy."""
+from __future__ import annotations
+
+import datetime
+
+import pytest
+
+from custom_components.nestquest.recurrence import (
+    RuleType,
+    RuleValidationError,
+    ScheduleRule,
+    occurs_on,
+    occurrences_between,
+)
+
+
+def _d(date: str) -> datetime.date:
+    return datetime.date.fromisoformat(date)
+
+
+# ---------------------------------------------------------------------------
+# Nominal-day firings across four consecutive years (incl. leap 2028)
+# ---------------------------------------------------------------------------
+
+
+def test_yearly_june_15_four_consecutive_years() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=6, day_of_month=15,
+        start_date="2026-06-15",
+    )
+    for year in (2026, 2027, 2028, 2029):
+        assert occurs_on(rule, datetime.date(year, 6, 15)) is True, year
+    assert occurs_on(rule, datetime.date(2026, 6, 14)) is False
+    assert occurs_on(rule, datetime.date(2026, 6, 16)) is False
+    assert occurs_on(rule, datetime.date(2026, 5, 15)) is False
+    assert occurs_on(rule, datetime.date(2026, 7, 15)) is False
+
+
+def test_yearly_feb_15_across_four_years() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=2, day_of_month=15,
+        start_date="2026-02-15",
+    )
+    # Feb 15 exists every year; the leap day is irrelevant.
+    for year in (2026, 2027, 2028, 2029):
+        assert occurs_on(rule, datetime.date(year, 2, 15)) is True, year
+
+
+def test_yearly_never_fires_before_start() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=6, day_of_month=15,
+        start_date="2027-06-15",
+    )
+    assert occurs_on(rule, datetime.date(2026, 6, 15)) is False
+    assert occurs_on(rule, datetime.date(2027, 6, 15)) is True
+
+
+def test_yearly_respects_end_date() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=6, day_of_month=15,
+        start_date="2026-06-15", end_date="2028-06-15",
+    )
+    assert occurs_on(rule, datetime.date(2028, 6, 15)) is True
+    assert occurs_on(rule, datetime.date(2029, 6, 15)) is False
+
+
+# ---------------------------------------------------------------------------
+# Leap-day policy: Feb 29 rule fires Feb 28 in non-leap years
+# ---------------------------------------------------------------------------
+
+
+def test_yearly_feb_29_clamps_to_feb_28_non_leap() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=2, day_of_month=29,
+        start_date="2026-02-28",
+    )
+    # 2026 non-leap: no Feb 29 — the clamp fires Feb 28.
+    assert occurs_on(rule, datetime.date(2026, 2, 28)) is True
+    assert occurs_on(rule, datetime.date(2026, 2, 27)) is False
+    # 2027 non-leap: same clamp.
+    assert occurs_on(rule, datetime.date(2027, 2, 28)) is True
+
+
+def test_yearly_feb_29_fires_feb_29_in_leap_years() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=2, day_of_month=29,
+        start_date="2026-02-28",
+    )
+    # 2028 leap: Feb 29 exists — nominal day.
+    assert occurs_on(rule, datetime.date(2028, 2, 29)) is True
+    assert occurs_on(rule, datetime.date(2028, 2, 28)) is False
+
+
+def test_yearly_feb_29_four_consecutive_years() -> None:
+    """The done-condition's four-year sweep: 2026 (clamp 28), 2027
+    (clamp 28), 2028 (leap: nominal 29), 2029 (clamp 28)."""
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=2, day_of_month=29,
+        start_date="2026-02-28",
+    )
+    assert occurs_on(rule, datetime.date(2026, 2, 28)) is True
+    assert occurs_on(rule, datetime.date(2027, 2, 28)) is True
+    assert occurs_on(rule, datetime.date(2028, 2, 29)) is True
+    assert occurs_on(rule, datetime.date(2029, 2, 28)) is True
+    # No other day in those Februaries fires.
+    for year, non_firing in (
+        (2026, (27,)), (2027, (26, 27)), (2028, (27,)),
+        (2029, (26, 27)),
+    ):
+        for day in non_firing:
+            assert occurs_on(rule, datetime.date(year, 2, day)) is False
+
+
+def test_yearly_feb_30_is_impossible_by_model() -> None:
+    """The model rejects day_of_month 30+ for February? No — 30/31 are
+    accepted (1..31) and clamp to the month end like MONTHLY_DAY."""
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=2, day_of_month=30,
+        start_date="2027-02-28",
+    )
+    # Non-leap 2027: Feb 28 is the month end — the clamp fires it.
+    assert occurs_on(rule, datetime.date(2027, 2, 28)) is True
+    # Leap 2028: Feb 29 is the month end — clamps there.
+    assert occurs_on(rule, datetime.date(2028, 2, 29)) is True
+    assert occurs_on(rule, datetime.date(2028, 2, 28)) is False
+
+
+# ---------------------------------------------------------------------------
+# Day-less YEARLY rules fire on the first of the month
+# ---------------------------------------------------------------------------
+
+
+def test_yearly_without_day_fires_on_first_of_month() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=6, start_date="2026-01-01",
+    )
+    assert occurs_on(rule, datetime.date(2026, 6, 1)) is True
+    assert occurs_on(rule, datetime.date(2026, 6, 2)) is False
+    assert occurs_on(rule, datetime.date(2026, 5, 1)) is False
+    assert occurs_on(rule, datetime.date(2027, 6, 1)) is True
+
+
+# ---------------------------------------------------------------------------
+# Interval: every N years
+# ---------------------------------------------------------------------------
+
+
+def test_yearly_interval_2_every_second_year() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=6, day_of_month=15, interval=2,
+        start_date="2026-06-15",
+    )
+    assert occurs_on(rule, datetime.date(2026, 6, 15)) is True   # y0
+    assert occurs_on(rule, datetime.date(2027, 6, 15)) is False  # y1
+    assert occurs_on(rule, datetime.date(2028, 6, 15)) is True   # y2
+    assert occurs_on(rule, datetime.date(2029, 6, 15)) is False  # y3
+    assert occurs_on(rule, datetime.date(2030, 6, 15)) is True   # y4
+
+
+def test_yearly_interval_2_leap_day_every_second_year() -> None:
+    """A Feb 29 rule at interval 2 anchored 2026 fires in even years:
+    2026 clamps (Feb 28), 2027 skips, 2028 hits the nominal Feb 29."""
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=2, day_of_month=29, interval=2,
+        start_date="2026-02-28",
+    )
+    assert occurs_on(rule, datetime.date(2026, 2, 28)) is True   # y0 clamp
+    assert occurs_on(rule, datetime.date(2027, 2, 28)) is False  # y1
+    assert occurs_on(rule, datetime.date(2028, 2, 29)) is True   # y2 nominal
+    assert occurs_on(rule, datetime.date(2029, 2, 28)) is False  # y3
+    assert occurs_on(rule, datetime.date(2030, 2, 28)) is True   # y4 clamp
+
+
+def test_yearly_interval_3_across_start_year() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=3, day_of_month=10, interval=3,
+        start_date="2026-03-10",
+    )
+    assert occurs_on(rule, datetime.date(2026, 3, 10)) is True
+    assert occurs_on(rule, datetime.date(2027, 3, 10)) is False
+    assert occurs_on(rule, datetime.date(2028, 3, 10)) is False
+    assert occurs_on(rule, datetime.date(2029, 3, 10)) is True
+
+
+# ---------------------------------------------------------------------------
+# occurrences_between
+# ---------------------------------------------------------------------------
+
+
+def test_yearly_occurrences_between_span() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=2, day_of_month=29,
+        start_date="2026-02-28",
+    )
+    result = occurrences_between(rule, "2026-01-01", "2029-12-31")
+    assert result == [
+        "2026-02-28", "2027-02-28", "2028-02-29", "2029-02-28",
+    ]
+
+
+def test_yearly_occurrences_between_disjoint_range() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=6, day_of_month=15,
+        start_date="2026-06-15",
+    )
+    # The rule only ever fires in June: other months are empty.
+    assert occurrences_between(rule, "2027-01-01", "2027-05-31") == []
+    assert occurrences_between(rule, "2027-07-01", "2027-12-31") == []
+
+
+def test_yearly_occurrences_between_rejects_inverted() -> None:
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=2, day_of_month=29,
+        start_date="2026-02-28",
+    )
+    with pytest.raises(RuleValidationError, match="on or after"):
+        occurrences_between(rule, "2029-01-01", "2026-01-01")
+
+
+# ---------------------------------------------------------------------------
+# Day of month 31 in a 30-day month: clamp consistency with MONTHLY_DAY
+# ---------------------------------------------------------------------------
+
+
+def test_yearly_june_31_clamps_to_june_30() -> None:
+    """June has 30 days: a June 31st yearly rule clamps to June 30."""
+    rule = ScheduleRule(
+        rule_type=RuleType.YEARLY, month=6, day_of_month=31,
+        start_date="2026-06-30",
+    )
+    assert occurs_on(rule, datetime.date(2026, 6, 30)) is True
+    assert occurs_on(rule, datetime.date(2027, 6, 30)) is True
+    assert occurs_on(rule, datetime.date(2026, 6, 29)) is False

tests/test_startup_db.py

@@ -0,0 +1,465 @@
+"""Tests for startup handling of missing, valid, and corrupt databases."""
+from __future__ import annotations
+
+import asyncio
+import hashlib
+import sqlite3
+from unittest.mock import AsyncMock, MagicMock
+
+import pytest
+from conftest import make_config_entry
+from homeassistant.exceptions import ConfigEntryNotReady
+
+from custom_components.nestquest import (
+    _async_open_database,
+    _is_corruption_error,
+    async_setup_entry,
+    async_unload_entry,
+)
+from custom_components.nestquest.db import NestQuestDatabase
+from custom_components.nestquest.migrations import (
+    VERSION_TABLE,
+    apply_migrations,
+)
+from tests.test_lifecycle import _wire
+
+
+def _run(coro):
+    return asyncio.new_event_loop().run_until_complete(coro)
+
+
+def _make_hass_mock():
+    hass = MagicMock()
+    hass.async_add_executor_job = AsyncMock(side_effect=(lambda fn, *a: fn(*a)))
+    return hass
+
+
+def _open_db(path) -> NestQuestDatabase:
+    database = NestQuestDatabase(_make_hass_mock())
+    _run(database.open(path))
+    return database
+
+
+async def _avalid_database(path) -> None:
+    """Create a valid, migrated NestQuest database file (async, on the
+    running loop, for use inside pytest-asyncio tests)."""
+    database = NestQuestDatabase(_make_hass_mock())
+    await database.open(path)
+    try:
+        await apply_migrations(database)
+    finally:
+        await database.close()
+
+
+def _valid_database(path) -> None:
+    """Create a valid, migrated NestQuest database file at ``path``."""
+    database = _open_db(path)
+    try:
+        _run(apply_migrations(database))
+    finally:
+        _run(database.close())
+
+
+def _truncated_database(path) -> None:
+    """Create a deliberately truncated (corrupt) database file.
+
+    A valid database is written, closed, then cut short mid-header:
+    opening it reports 'file is not a database' — exactly what an
+    interrupted write or failing disk leaves behind.
+    """
+    _valid_database(path)
+    data = path.read_bytes()
+    assert len(data) > 64, "valid database must exist before truncation"
+    path.write_bytes(data[: len(data) // 2])
+
+
+# ---------------------------------------------------------------------------
+# Corruption classification
+# ---------------------------------------------------------------------------
+
+
+def test_is_corruption_error_matches_sqlite_spellings() -> None:
+    assert _is_corruption_error(
+        sqlite3.DatabaseError("file is not a database")
+    )
+    assert _is_corruption_error(
+        sqlite3.DatabaseError("file is not a database".upper())
+    )
+    assert _is_corruption_error(
+        sqlite3.DatabaseError("file is encrypted or malformed")
+    )
+    assert _is_corruption_error(
+        sqlite3.OperationalError("file is not a database")
+    )
+
+
+def test_is_corruption_error_rejects_other_failures() -> None:
+    assert not _is_corruption_error(
+        sqlite3.OperationalError("database is locked")
+    )
+    assert not _is_corruption_error(
+        sqlite3.IntegrityError("NOT NULL constraint failed")
+    )
+    assert not _is_corruption_error(RuntimeError("boom"))
+    assert not _is_corruption_error(ValueError("nope"))
+
+
+# ---------------------------------------------------------------------------
+# Startup: three cases through _async_open_database
+# ---------------------------------------------------------------------------
+
+
+def test_missing_file_opens_fresh(tmp_path) -> None:
+    db_path = tmp_path / "nestquest.db"
+    assert not db_path.exists()
+    database = _run(_async_open_database(_make_hass_mock(), db_path))
+    try:
+        # The file was created fresh and migrations stamped version 1.
+        assert db_path.exists()
+        row = _run(
+            database.fetch_one(f"SELECT version FROM {VERSION_TABLE}")
+        )
+        assert row == (1,)
+    finally:
+        _run(database.close())
+
+
+def test_zero_byte_existing_file_is_corrupt_and_untouched(tmp_path) -> None:
+    """A zero-byte file is neither missing nor valid: it is corrupt.
+
+    The leave-untouched contract applies — startup refuses (retryable)
+    rather than silently initializing over whatever interrupted write
+    left the empty file, and the file stays exactly zero bytes.
+    """
+    db_path = tmp_path / "nestquest.db"
+    db_path.write_bytes(b"")
+    with pytest.raises(ConfigEntryNotReady) as excinfo:
+        _run(_async_open_database(_make_hass_mock(), db_path))
+    assert db_path.stat().st_size == 0
+    assert isinstance(excinfo.value.__cause__, sqlite3.DatabaseError)
+    # No sidecars appeared from the refusal path.
+    assert not (tmp_path / "nestquest.db-wal").exists()
+    assert not (tmp_path / "nestquest.db-shm").exists()
+
+
+def test_valid_file_opens_and_migrations_noop(tmp_path) -> None:
+    db_path = tmp_path / "nestquest.db"
+    _valid_database(db_path)
+    digest_before = hashlib.sha256(db_path.read_bytes()).hexdigest()
+    database = _run(_async_open_database(_make_hass_mock(), db_path))
+    try:
+        row = _run(
+            database.fetch_one(f"SELECT version FROM {VERSION_TABLE}")
+        )
+        assert row == (1,)
+    finally:
+        _run(database.close())
+    # Opening a valid file must not rewrite it (the no-op path).
+    assert (
+        hashlib.sha256(db_path.read_bytes()).hexdigest() == digest_before
+    )
+
+
+def test_corrupt_file_raises_not_ready_and_leaves_file_untouched(
+    tmp_path, caplog
+) -> None:
+    db_path = tmp_path / "nestquest.db"
+    _truncated_database(db_path)
+    corrupt_bytes = db_path.read_bytes()
+    digest = hashlib.sha256(corrupt_bytes).hexdigest()
+
+    with pytest.raises(ConfigEntryNotReady) as excinfo:
+        _run(_async_open_database(_make_hass_mock(), db_path))
+
+    # The file is byte-identical: the owner can restore/inspect it.
+    assert hashlib.sha256(db_path.read_bytes()).hexdigest() == digest
+    assert len(db_path.read_bytes()) == len(corrupt_bytes)
+    # The original error is preserved and the log names the path.
+    assert isinstance(excinfo.value.__cause__, sqlite3.DatabaseError)
+    assert any(
+        "corrupt" in record.getMessage().lower()
+        and str(db_path) in record.getMessage()
+        for record in caplog.records
+        if record.levelname == "ERROR"
+    ), "the error log must name the corrupt path"
+
+
+def test_corrupt_file_raises_not_ready_never_raw_error(tmp_path) -> None:
+    """The corrupt case must surface as ConfigEntryNotReady, not crash
+    setup with a raw sqlite3.DatabaseError — and it must ALWAYS raise
+    (returning normally would mean corruption was silently accepted)."""
+    db_path = tmp_path / "nestquest.db"
+    _truncated_database(db_path)
+    with pytest.raises(ConfigEntryNotReady) as excinfo:
+        _run(_async_open_database(_make_hass_mock(), db_path))
+    assert isinstance(excinfo.value.__cause__, sqlite3.DatabaseError)
+
+
+def test_migration_stage_corruption_leaves_db_and_sidecars_untouched(
+    tmp_path, monkeypatch
+) -> None:
+    """A valid header with a corrupt LATER page must be rejected by the
+    whole-file read-only preflight BEFORE the read-write open can
+    switch the journal mode to WAL.
+
+    The fixture is deliberately a ROLLBACK-JOURNAL file (created with
+    plain sqlite3, default journal_mode=delete, NOT via the wrapper):
+    that is the case where the WAL transition actually rewrites the
+    header — a WAL-mode fixture would mask the write under test.  The
+    open() spy proves the read-write open was never reached.
+    """
+    db_path = tmp_path / "nestquest.db"
+    conn = sqlite3.connect(db_path)
+    try:
+        conn.execute("PRAGMA journal_mode=DELETE")
+        conn.execute(
+            "CREATE TABLE filler (id INTEGER PRIMARY KEY, "
+            "payload TEXT NOT NULL)"
+        )
+        conn.executemany(
+            "INSERT INTO filler (payload) VALUES (?)",
+            [(f"row-{index}" * 8,) for index in range(200)],
+        )
+        conn.commit()
+    finally:
+        conn.close()
+    data = bytearray(db_path.read_bytes())
+    assert conn.execute is not None
+    assert len(data) > 4200, "fixture must span more than one page"
+    # Corrupt a page well past the 100-byte header (offset 4096, the
+    # second page region): the header itself stays valid, so the
+    # read-write open succeeds and only the integrity probe/migrations
+    # discover damage.
+    for offset in range(4096, 4128):
+        data[offset] = 0xFF
+    db_path.write_bytes(bytes(data))
+    corrupt_bytes = db_path.read_bytes()
+    digest = hashlib.sha256(corrupt_bytes).hexdigest()
+
+    # Track whether the read-write open is reached at all: the whole
+    # preflight must reject this corruption BEFORE NestQuestDatabase
+    # .open() is ever called, which is the only way the file can stay
+    # byte-identical (the open persists WAL, rewriting the header).
+    open_calls: list[str] = []
+    original_open = NestQuestDatabase.open
+
+    async def _spy_open(self, path):
+        open_calls.append(str(path))
+        return await original_open(self, path)
+
+    monkeypatch.setattr(NestQuestDatabase, "open", _spy_open)
+
+    with pytest.raises(ConfigEntryNotReady) as excinfo:
+        _run(_async_open_database(_make_hass_mock(), db_path))
+    assert isinstance(excinfo.value.__cause__, sqlite3.DatabaseError)
+
+    # The corruption was caught at the preflight: the read-write open
+    # (and with it the WAL header rewrite) was never attempted.
+    assert open_calls == [], (
+        "whole-file preflight must reject corruption before the "
+        "read-write open can transition the journal mode"
+    )
+
+    # The main file is byte-identical to the corrupt state we handed in.
+    assert hashlib.sha256(db_path.read_bytes()).hexdigest() == digest
+    # No WAL/SHM sidecars left behind (WAL mode was never persisted,
+    # or they were cleaned up on close).
+    assert not (tmp_path / "nestquest.db-wal").exists()
+    assert not (tmp_path / "nestquest.db-shm").exists()
+
+
+def test_wal_mode_corruption_leaves_file_and_sidecars_untouched(
+    tmp_path, monkeypatch
+) -> None:
+    """A corrupt WAL-HEADER file must be rejected without creating
+    -shm sidecars from the read-only preflight itself.
+
+    The immutable=1 probe URI is what guarantees a read-only
+    connection creates no sidecars even for a WAL-header database; the
+    sidecar assertions after the call prove it.
+    """
+    db_path = tmp_path / "nestquest.db"
+    # WAL fixture: created via the wrapper (journal_mode=WAL persists).
+    _valid_database(db_path)
+    data = bytearray(db_path.read_bytes())
+    # Corrupt the WAL header flag region: bytes 18/19 hold the
+    # read/write version numbers (2 = WAL).  Flip them to garbage so
+    # the file claims a broken journal mode.
+    assert len(data) > 64
+    data[18] = 0x99
+    data[19] = 0x99
+    db_path.write_bytes(bytes(data))
+    corrupt_bytes = db_path.read_bytes()
+    digest = hashlib.sha256(corrupt_bytes).hexdigest()
+
+    with pytest.raises(ConfigEntryNotReady):
+        _run(_async_open_database(_make_hass_mock(), db_path))
+
+    # Main file byte-identical; no -shm appeared from the probe.
+    assert hashlib.sha256(db_path.read_bytes()).hexdigest() == digest
+    assert not (tmp_path / "nestquest.db-shm").exists()
+
+
+def test_open_failure_closes_connection_when_opened(
+    tmp_path, monkeypatch
+) -> None:
+    """A failure DURING MIGRATIONS closes the opened connection; a
+    failure inside open() itself has nothing to close (the wrapper
+    never adopted a connection), so close must not be called there.
+    """
+    db_path = tmp_path / "nestquest.db"
+    _truncated_database(db_path)
+
+    close_calls: list[bool] = []
+    original_close = NestQuestDatabase.close
+
+    async def _spy_close(self):
+        close_calls.append(True)
+        return await original_close(self)
+
+    monkeypatch.setattr(NestQuestDatabase, "close", _spy_close)
+    with pytest.raises(ConfigEntryNotReady):
+        _run(_async_open_database(_make_hass_mock(), db_path))
+    # Corruption hit at PRAGMA time inside open(): no connection was
+    # adopted, so there is nothing to close and close() must NOT have
+    # been called (calling close on a never-opened wrapper would be
+    # harmless but the helper deliberately skips it).
+    assert close_calls == []
+
+    # Contrast: a failure DURING migrations (file opens fine) closes.
+    close_calls.clear()
+    valid_path = tmp_path / "migrations-fail.db"
+    _valid_database(valid_path)
+    # _valid_database's own close went through the spy; only the
+    # failure path's close counts from here.
+    close_calls.clear()
+    from custom_components.nestquest import migrations as migrations_mod
+    from custom_components.nestquest.db import NestQuestDatabase as _DB
+
+    async def _failing(database, migrations=None):
+        raise sqlite3.DatabaseError("database disk image is malformed")
+
+    monkeypatch.setattr(migrations_mod, "apply_migrations", _failing)
+    monkeypatch.setattr(
+        "custom_components.nestquest.apply_migrations", _failing
+    )
+    with pytest.raises(ConfigEntryNotReady) as excinfo:
+        _run(_async_open_database(_make_hass_mock(), valid_path))
+    # Exactly one close: the migrations-failure cleanup path.  (The
+    # spy also sees the wrapper's own idempotent close if it were
+    # double-called — it must not be.)
+    assert close_calls == [True], (
+        "an open connection must be closed before the error propagates"
+    )
+    assert isinstance(excinfo.value.__cause__, sqlite3.DatabaseError)
+
+
+# ---------------------------------------------------------------------------
+# Startup through async_setup_entry (the real wiring)
+# ---------------------------------------------------------------------------
+
+
+def _make_setup(hass, registry):
+    def _setup(entry):
+        return async_setup_entry(hass, entry)
+
+    return _setup
+
+
+async def _setup_entry(hass, entry, registry) -> object:
+    entry = _wire(entry, registry)
+    await async_setup_entry(hass, entry)
+    return entry
+
+
+async def test_setup_with_corrupt_file_raises_not_ready(
+    hass, make_entry, monkeypatch, tmp_path, caplog
+) -> None:
+    from custom_components.nestquest.store import async_get_db_path
+
+    db_path = await async_get_db_path(hass)
+    await _avalid_database(db_path)
+    data = db_path.read_bytes()
+    assert len(data) > 64
+    db_path.write_bytes(data[: len(data) // 2])
+    corrupt_bytes = db_path.read_bytes()
+
+    entry = make_entry()
+    with pytest.raises(ConfigEntryNotReady):
+        await _setup_entry(hass, entry, hass.registry)
+    # File untouched, no runtime data stored.
+    assert db_path.read_bytes() == corrupt_bytes
+    assert hass.data["nestquest"].get(entry.entry_id) is None
+
+
+async def test_setup_with_missing_file_creates_and_loads(
+    hass, make_entry
+) -> None:
+    entry = await _setup_entry(hass, make_entry(), hass.registry)
+    database = entry.runtime_data.database
+    try:
+        row = await database.fetch_one(
+            f"SELECT version FROM {VERSION_TABLE}"
+        )
+        assert row == (1,)
+    finally:
+        await database.close()
+    assert await async_unload_entry(hass, entry) is True
+
+
+async def test_setup_with_valid_file_preserves_rows(
+    hass, make_entry
+) -> None:
+    entry_a = await _setup_entry(hass, make_entry(), hass.registry)
+    database_a = entry_a.runtime_data.database
+    await database_a.execute(
+        "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
+        ("Ada", "2026-09-14T12:00:00+00:00"),
+    )
+    # Unload pops the runtime record AND closes the connection; a
+    # fresh setup for the same entry_id then reopens the file.
+    assert await async_unload_entry(hass, entry_a) is True
+
+    entry_b = await _setup_entry(hass, make_entry(), hass.registry)
+    database_b = entry_b.runtime_data.database
+    assert database_b is not database_a
+    try:
+        row = await database_b.fetch_one("SELECT COUNT(*) FROM children")
+        assert row == (1,)
+    finally:
+        await database_b.close()
+    assert await async_unload_entry(hass, entry_b) is True
+
+
+# ---------------------------------------------------------------------------
+# No code path deletes or overwrites an existing database file
+# ---------------------------------------------------------------------------
+
+
+def test_package_never_unlinks_or_truncates_db_files() -> None:
+    """Guardrail: no code path may delete or overwrite an existing
+    database file.  Scans the package for unlink/rmtree/write on the
+    DB filename constant or *.db patterns.
+    """
+    import re
+    from pathlib import Path
+
+    package = Path(
+        __import__(
+            "custom_components.nestquest", fromlist=["__file__"]
+        ).__file__
+    ).parent
+    pattern = re.compile(
+        r"(unlink|rmtree|write_bytes|write_text|open\s*\(\s*[^)]*['\"]w)"
+        r"[^\n]*(nestquest\.db|\.db['\"])|"
+        r"(nestquest\.db|\.db['\"])[^\n]*"
+        r"(unlink|rmtree|write_bytes|write_text)",
+        re.IGNORECASE,
+    )
+    offenders: list[str] = []
+    for py in sorted(package.glob("*.py")):
+        if pattern.search(py.read_text()):
+            offenders.append(py.name)
+    assert offenders == [], (
+        f"code paths that could delete/overwrite the database: {offenders}"
+    )

tests/test_store.py

@@ -101,12 +101,20 @@ def test_executor_helper_creates_directory(tmp_path) -> None:
 
 
 def test_no_sqlite3_import_outside_db_module() -> None:
-    """sqlite3 is banned package-wide except db.py, the connection wrapper."""
+    """sqlite3 is banned package-wide except two justified modules.
+
+    db.py is the connection wrapper (all statement execution lives
+    there).  __init__.py imports sqlite3 ONLY for its exception types
+    to classify corruption on startup (done-condition: corrupt file ->
+    ConfigEntryNotReady); it never opens a connection or executes
+    SQL — that is asserted separately below.
+    """
     pkg_dir = Path(store.__file__).parent
     py_files = list(pkg_dir.glob("*.py"))
     assert py_files
+    allowed = {"db.py", "__init__.py"}
     for py_file in py_files:
-        if py_file.name == "db.py":
+        if py_file.name in allowed:
             continue
         tree = ast.parse(py_file.read_text(encoding="utf-8"), filename=str(py_file))
         for node in ast.walk(tree):
@@ -121,6 +129,36 @@ def test_no_sqlite3_import_outside_db_module() -> None:
                 )
 
 
+def test_init_module_never_opens_or_executes_sqlite() -> None:
+    """The __init__.py sqlite3 usage is limited to (a) exception typing
+    for corruption classification and (b) the ONE read-only preflight
+    connection (mode=ro URI, SELECT-only) that detects corruption in an
+    existing file before the real read-write open.  No read-write
+    connect, no cursor writes, no non-SELECT statements may appear.
+    """
+    import re
+    from pathlib import Path
+
+    init_path = (
+        Path(store.__file__).parent / "__init__.py"
+    ).read_text(encoding="utf-8")
+    # The preflight connect must be the URI read-only form, and only
+    # SELECT statements and the integrity_check PRAGMA may run there.
+    assert "sqlite3.connect(uri, uri=True)" in init_path
+    assert "?mode=ro" in init_path
+    assert 'PRAGMA integrity_check' in init_path
+    forbidden = re.compile(
+        r"sqlite3\s*\.\s*connect(?!\(\s*uri\s*,)"
+        r"|\bconn(ection)?\s*\.\s*execute\s*\(\s*[\"'](?!SELECT\s|PRAGMA\s+integrity_check)"
+        r"|\bexecutemany\b|\bexecutescript\b",
+        re.IGNORECASE,
+    )
+    assert forbidden.search(init_path) is None, (
+        "__init__.py may use sqlite3 only for exception typing and the "
+        "read-only corruption preflight (SELECT + integrity_check)"
+    )
+
+
 def test_no_file_creation_calls_in_store() -> None:
     source = Path(store.__file__).read_text(encoding="utf-8")
     tree = ast.parse(source, filename=str(store.__file__))

uv.lock

@@ -22,7 +22,7 @@ wheels = [
 
 [[package]]
 name = "nestquest"
-version = "0.1.0"
+version = "0.3.0"
 source = { virtual = "." }
 
 [package.dev-dependencies]