fix: enforce integer storage and weekday_set validation in schema

custom_components/nestquest/__init__.py

@@ -10,6 +10,9 @@ from homeassistant.config_entries import ConfigEntry
 from homeassistant.core import HomeAssistant
 
 from .const import DOMAIN, LOGGER
+from .db import NestQuestDatabase
+from .schema import SCHEMA_V1_STATEMENTS, async_apply_ddl
+from .store import async_get_db_path
 
 _LOGGER = LOGGER
 
@@ -20,6 +23,7 @@ class NestQuestRuntimeData:
 
     entry_id: str
     options: dict[str, Any]
+    database: NestQuestDatabase
     remove_update_listener: Callable[[], Any]
 
 
@@ -31,6 +35,14 @@ async def async_setup(hass: HomeAssistant, config: dict[str, Any]) -> bool:
 async def async_setup_entry(hass: HomeAssistant, entry: ConfigEntry) -> bool:
     """Set up NestQuest from a config entry."""
     hass.data.setdefault(DOMAIN, {})
+    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))
+            existing.database = db
+        entry.runtime_data = existing
+        return True
 
     async def _async_update_listener(
         listener_hass: HomeAssistant, listener_entry: ConfigEntry
@@ -38,10 +50,18 @@ 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))
+    try:
+        await async_apply_ddl(database, SCHEMA_V1_STATEMENTS)
+        remove_update_listener = entry.add_update_listener(_async_update_listener)
+    except BaseException:
+        await database.close()
+        raise
     runtime_data = NestQuestRuntimeData(
         entry_id=entry.entry_id,
         options=dict(entry.options),
-        remove_update_listener=entry.add_update_listener(_async_update_listener),
+        database=database,
+        remove_update_listener=remove_update_listener,
     )
     entry.runtime_data = runtime_data
     hass.data[DOMAIN][entry.entry_id] = runtime_data
@@ -50,15 +70,30 @@ async def async_setup_entry(hass: HomeAssistant, entry: ConfigEntry) -> bool:
 
 async def async_unload_entry(hass: HomeAssistant, entry: ConfigEntry) -> bool:
     """Unload a config entry."""
-    runtime_data = hass.data.setdefault(DOMAIN, {}).pop(entry.entry_id, None)
+    domain_data = hass.data.setdefault(DOMAIN, {})
+    runtime_data = domain_data.pop(entry.entry_id, None)
+    if not domain_data:
+        hass.data.pop(DOMAIN, None)
     if runtime_data is None:
         runtime_data = getattr(entry, "runtime_data", None)
     if runtime_data is not None:
         remove_update_listener = getattr(runtime_data, "remove_update_listener", None)
-        if remove_update_listener is not None:
-            result = remove_update_listener()
-            if inspect.isawaitable(result):
-                await result
+        unload_error: BaseException | None = None
+        try:
+            if remove_update_listener is not None:
+                result = remove_update_listener()
+                if inspect.isawaitable(result):
+                    await result
+        except BaseException as err:
+            unload_error = err
+        finally:
+            database = getattr(runtime_data, "database", None)
+            if database is not None:
+                await database.close()
+        if unload_error is not None:
+            if getattr(entry, "runtime_data", None) is not None:
+                entry.runtime_data = None
+            raise unload_error
     if getattr(entry, "runtime_data", None) is not None:
         entry.runtime_data = None
     return True

custom_components/nestquest/config_flow.py

@@ -4,6 +4,7 @@ from __future__ import annotations
 from typing import Any
 
 from homeassistant import config_entries
+from homeassistant.core import callback
 from homeassistant.data_entry_flow import FlowResult
 
 from .const import DOMAIN
@@ -14,6 +15,16 @@ class NestQuestConfigFlow(config_entries.ConfigFlow, domain=DOMAIN):
 
     VERSION = 1
 
+    @staticmethod
+    @callback
+    def async_get_options_flow(
+        config_entry: config_entries.ConfigEntry,
+    ) -> Any:
+        """Create the options flow handler for NestQuest."""
+        from .options_flow import NestQuestOptionsFlow
+
+        return NestQuestOptionsFlow(config_entry=config_entry)
+
     async def async_step_user(
         self, user_input: dict[str, Any] | None = None
     ) -> FlowResult:

custom_components/nestquest/db.py

@@ -0,0 +1,422 @@
+"""Async SQLite connection wrapper for the NestQuest integration."""
+from __future__ import annotations
+
+import asyncio
+import sqlite3
+from collections.abc import AsyncIterator, Awaitable, Sequence
+from contextlib import asynccontextmanager
+from dataclasses import dataclass
+from pathlib import Path
+from typing import Any
+
+from homeassistant.core import HomeAssistant
+
+
+@dataclass(frozen=True)
+class ExecutionResult:
+    """Plain-data outcome of one statement.
+
+    ``rowcount`` and ``lastrowid`` mirror the corresponding
+    :class:`sqlite3.Cursor` attributes; both are captured on the executor
+    thread so no sqlite3 object ever crosses back to the event loop.
+    """
+
+    rowcount: int
+    lastrowid: int | None
+
+
+class NestQuestDatabase:
+    """Async wrapper around a single SQLite connection.
+
+    Every sqlite3 call is delegated to the Home Assistant executor through
+    ``hass.async_add_executor_job`` so the event loop is never blocked, and
+    only plain Python data (row tuples and :class:`ExecutionResult`) is
+    returned to the event loop.
+
+    Every operation is serialized through one asyncio.Lock per wrapper.  A
+    ``transaction()`` holds that lock for its whole BEGIN..COMMIT span, so
+    interleaved coroutines cannot corrupt the transaction and concurrent
+    BEGINs are impossible; statements issued while a transaction is open
+    queue until it completes.  Concurrent transactions on one wrapper are
+    not supported: a second ``transaction()`` entered while one is active
+    raises :class:`RuntimeError`.  ``close()`` serializes on the same lock
+    and is idempotent.
+
+    Every connection-affecting executor job (connect, pragmas, statements,
+    transaction lifecycle and close) is awaited through the settlement
+    helper :meth:`_await_settled`: if the awaiting task is cancelled while
+    a job is in flight, the job still settles on the worker thread before
+    the cancellation propagates, so the wrapper lock is never released
+    while the executor thread is mid-job.
+    """
+
+    def __init__(self, hass: HomeAssistant) -> None:
+        """Initialize the wrapper around a Home Assistant instance."""
+        self._hass = hass
+        self._conn: sqlite3.Connection | None = None
+        self._rowcount: int = -1
+        self._lastrowid: int | None = None
+        self._lock: asyncio.Lock | None = None
+        self._lock_loop: asyncio.AbstractEventLoop | None = None
+        self._in_transaction = False
+        self._tx_task: asyncio.Task | None = None
+
+    def _get_lock(self) -> asyncio.Lock:
+        """Return the per-wrapper lock, (re)bound to the running loop.
+
+        HA drives every wrapper call from one event loop, so the lock is
+        created once and reused; re-binding only when the running loop
+        differs keeps the wrapper usable from plain synchronous drivers
+        that run each call on a fresh loop.
+        """
+        loop = asyncio.get_running_loop()
+        if self._lock is None or self._lock_loop is not loop:
+            self._lock = asyncio.Lock()
+            self._lock_loop = loop
+        return self._lock
+
+    @property
+    def connected(self) -> bool:
+        """Return True while the underlying connection is open."""
+        return self._conn is not None
+
+    @property
+    def rowcount(self) -> int:
+        """Row count of the most recent statement, or -1 when unset."""
+        return self._rowcount
+
+    @property
+    def lastrowid(self) -> int | None:
+        """Row id of the most recent insert, or None when unset."""
+        return self._lastrowid
+
+    @asynccontextmanager
+    async def _serialized(self) -> AsyncIterator[None]:
+        """Hold the wrapper lock unless the caller owns the active transaction.
+
+        A transaction holds the lock for its whole BEGIN..COMMIT span;
+        statements issued by the transaction's own task are already
+        serialized by it and must not re-acquire the non-reentrant lock.
+        Any other task queues on the lock until the transaction ends.
+        """
+        if (
+            self._in_transaction
+            and self._tx_task is not None
+            and asyncio.current_task() is self._tx_task
+        ):
+            yield
+            return
+        async with self._get_lock():
+            yield
+
+    async def open(self, path: Path | str) -> NestQuestDatabase:
+        """Open the SQLite file with WAL journal mode and foreign keys on.
+
+        Cancellation-safe: if this call is cancelled while the connect job
+        is in flight, the job still settles (see :meth:`_await_settled`).
+        A connection the settled job created is never adopted by the
+        cancelled call; it is closed through a settled executor job instead
+        of being abandoned to garbage collection, and the cancellation
+        propagates afterwards, so a follow-up ``open()`` starts from a
+        clean state.
+        """
+        if self._conn is not None:
+            return self
+        async with self._get_lock():
+            if self._conn is not None:
+                return self
+
+            def _open() -> sqlite3.Connection:
+                conn = sqlite3.connect(
+                    path, check_same_thread=False, isolation_level=None
+                )
+                try:
+                    conn.execute("PRAGMA journal_mode=WAL")
+                    conn.execute("PRAGMA foreign_keys=ON")
+                except BaseException:
+                    conn.close()
+                    raise
+                return conn
+
+            settled, cancelled = await self._await_settled_state(
+                self._hass.async_add_executor_job(_open)
+            )
+            if cancelled:
+                if isinstance(settled, sqlite3.Connection):
+
+                    def _close_abandoned() -> None:
+                        settled.close()
+
+                    await self._await_settled(
+                        self._hass.async_add_executor_job(_close_abandoned)
+                    )
+                raise asyncio.CancelledError()
+            self._conn = settled
+        return self
+
+    async def execute(
+        self, sql: str, parameters: Sequence[Any] = ()
+    ) -> ExecutionResult:
+        """Run one statement in the executor and return its plain result."""
+        async with self._serialized():
+            conn = self._require_conn()
+
+            def _execute() -> ExecutionResult:
+                cursor = conn.execute(sql, parameters)
+                return ExecutionResult(cursor.rowcount, cursor.lastrowid)
+
+            result = await self._await_settled(
+                self._hass.async_add_executor_job(_execute)
+            )
+            self._rowcount = result.rowcount
+            self._lastrowid = result.lastrowid
+            return result
+
+    async def execute_many(
+        self, sql: str, parameters: Sequence[Sequence[Any]]
+    ) -> ExecutionResult:
+        """Run executemany in the executor and return its plain result."""
+        async with self._serialized():
+            conn = self._require_conn()
+
+            def _execute_many() -> ExecutionResult:
+                cursor = conn.executemany(sql, parameters)
+                return ExecutionResult(cursor.rowcount, cursor.lastrowid)
+
+            result = await self._await_settled(
+                self._hass.async_add_executor_job(_execute_many)
+            )
+            self._rowcount = result.rowcount
+            self._lastrowid = result.lastrowid
+            return result
+
+    async def fetch_one(
+        self, sql: str, parameters: Sequence[Any] = ()
+    ) -> tuple | None:
+        """Run a query in the executor and return its first row, or None."""
+        async with self._serialized():
+            conn = self._require_conn()
+
+            def _fetch_one() -> tuple[tuple | None, int, int | None]:
+                cursor = conn.execute(sql, parameters)
+                return cursor.fetchone(), cursor.rowcount, cursor.lastrowid
+
+            row, rowcount, lastrowid = await self._await_settled(
+                self._hass.async_add_executor_job(_fetch_one)
+            )
+            self._rowcount = rowcount
+            self._lastrowid = lastrowid
+            return row
+
+    async def fetch_all(
+        self, sql: str, parameters: Sequence[Any] = ()
+    ) -> list[tuple]:
+        """Run a query in the executor and return all of its rows."""
+        async with self._serialized():
+            conn = self._require_conn()
+
+            def _fetch_all() -> tuple[list[tuple], int, int | None]:
+                cursor = conn.execute(sql, parameters)
+                return cursor.fetchall(), cursor.rowcount, cursor.lastrowid
+
+            rows, rowcount, lastrowid = await self._await_settled(
+                self._hass.async_add_executor_job(_fetch_all)
+            )
+            self._rowcount = rowcount
+            self._lastrowid = lastrowid
+            return rows
+
+    @asynccontextmanager
+    async def transaction(self) -> AsyncIterator[None]:
+        """Run a block inside BEGIN/COMMIT, rolling back on any exception.
+
+        The wrapper lock is held for the entire BEGIN..COMMIT span, so no
+        other statement can interleave inside the transaction.  Entering a
+        second transaction while one is active raises RuntimeError.  A
+        failing COMMIT (e.g. a deferred foreign-key violation) also triggers
+        a ROLLBACK before the commit error propagates, so the real
+        connection is never left inside a stray transaction.
+
+        The lifecycle is cancellation-safe.  Transaction ownership and the
+        wrapper lock are only released after the executor work has fully
+        settled, even when the owning task is cancelled mid-flight: if a
+        cancelled BEGIN or COMMIT still completes on the worker thread,
+        the corresponding ROLLBACK is shielded and awaited before any state
+        is cleared.  A lifecycle statement that fails plus a failing
+        follow-up cleanup surface both errors in an ExceptionGroup.
+        """
+        if self._in_transaction:
+            raise RuntimeError(
+                "A NestQuest transaction is already active on this "
+                "connection; concurrent transactions are not supported"
+            )
+        async with self._get_lock():
+            conn = self._require_conn()
+            self._in_transaction = True
+            self._tx_task = asyncio.current_task()
+            try:
+
+                def _begin() -> None:
+                    conn.execute("BEGIN")
+
+                await self._settle_lifecycle(
+                    conn,
+                    self._hass.async_add_executor_job(_begin),
+                    "BEGIN did not settle cleanly and the follow-up "
+                    "cleanup also raised; the transaction may need manual "
+                    "cleanup",
+                )
+                try:
+                    yield
+                except BaseException:
+
+                    def _rollback() -> None:
+                        conn.execute("ROLLBACK")
+
+                    await self._settle_lifecycle(
+                        conn,
+                        self._hass.async_add_executor_job(_rollback),
+                        "ROLLBACK failed and the follow-up cleanup also "
+                        "raised; the transaction may need manual cleanup",
+                    )
+                    raise
+
+                def _commit() -> None:
+                    conn.execute("COMMIT")
+
+                await self._settle_lifecycle(
+                    conn,
+                    self._hass.async_add_executor_job(_commit),
+                    "COMMIT failed and the follow-up cleanup also raised; "
+                    "the transaction may need manual cleanup",
+                )
+            finally:
+                self._in_transaction = False
+                self._tx_task = None
+
+    async def _settle_lifecycle(
+        self, conn: sqlite3.Connection, job: Awaitable[Any], message: str
+    ) -> None:
+        """Run one BEGIN/ROLLBACK/COMMIT job, then force the connection clean.
+
+        The job always settles first (see :meth:`_await_settled`), so a
+        cancelled statement that the worker thread completed anyway is
+        never abandoned mid-flight.  If the job raised, a follow-up
+        ROLLBACK — itself settled — guarantees the connection ends outside
+        any SQLite transaction before the error propagates.  When both the
+        job and the cleanup raise, both errors are preserved in an
+        ExceptionGroup instead of the original being lost.
+        """
+        try:
+            await self._await_settled(job)
+        except BaseException as settle_error:
+            errors: list[BaseException] = [settle_error]
+            try:
+                await self._ensure_no_stray_transaction(conn)
+            except BaseException as cleanup_error:
+                errors.append(cleanup_error)
+            if len(errors) == 2:
+                raise BaseExceptionGroup(message, errors) from settle_error
+            raise
+
+    async def _ensure_no_stray_transaction(
+        self, conn: sqlite3.Connection
+    ) -> None:
+        """Force the connection out of any transaction via ROLLBACK.
+
+        Runs after a failed or cancelled lifecycle statement.  The worker
+        thread may still be finishing (or may already have completed) the
+        very statement whose await was cancelled, so both the check and
+        the ROLLBACK settle in the executor before the caller releases
+        transaction ownership.
+        """
+
+        def _cleanup() -> bool:
+            if conn.in_transaction:
+                conn.execute("ROLLBACK")
+            return conn.in_transaction
+
+        stray = await self._await_settled(
+            self._hass.async_add_executor_job(_cleanup)
+        )
+        if stray:
+            raise RuntimeError(
+                "The SQLite connection is still inside a transaction after "
+                "cleanup; the transaction may need manual cleanup"
+            )
+
+    async def _await_settled(self, job: Awaitable[Any]) -> Any:
+        """Await ``job``, letting it settle even if this task is cancelled.
+
+        Cancellation is observed, not swallowed: it is re-raised only once
+        the job has fully settled, so the caller can finalize against a
+        known connection state instead of abandoning in-flight work.  The
+        job is wrapped in a task because a bare coroutine dies un-awaited
+        once its await is cancelled.  A second cancellation during
+        settlement is absorbed the same way; if the job itself fails after
+        a cancellation, both errors surface in an ExceptionGroup.
+        """
+        result, cancelled = await self._await_settled_state(job)
+        if cancelled:
+            raise asyncio.CancelledError()
+        return result
+
+    async def _await_settled_state(self, job: Awaitable[Any]) -> tuple[Any, bool]:
+        """Await ``job`` to settlement and report ``(result, cancelled)``.
+
+        Same settlement guarantees as :meth:`_await_settled`, but returns
+        the settled outcome together with whether the awaiting task was
+        cancelled, so callers that must act on a settled result before
+        re-raising cancellation (e.g. closing an abandoned connection)
+        can do so without losing the result.
+        """
+        cancelled = False
+        inner = asyncio.ensure_future(job)
+        while not inner.done():
+            try:
+                await asyncio.shield(inner)
+            except asyncio.CancelledError:
+                cancelled = True
+        try:
+            result = inner.result()
+        except BaseException as job_error:
+            if cancelled:
+                raise BaseExceptionGroup(
+                    "executor job failed while settling after cancellation",
+                    [asyncio.CancelledError(), job_error],
+                ) from job_error
+            raise
+        return result, cancelled
+
+    async def close(self) -> None:
+        """Close the connection in the executor; safe to call repeatedly.
+
+        Serialized on the wrapper lock, so an in-flight operation or open
+        transaction always completes before the connection is closed.
+        """
+        if (
+            self._in_transaction
+            and self._tx_task is not None
+            and asyncio.current_task() is self._tx_task
+        ):
+            raise RuntimeError(
+                "Cannot close the NestQuest database from inside its own "
+                "transaction; exit the transaction first"
+            )
+        async with self._get_lock():
+            conn, self._conn = self._conn, None
+            self._rowcount, self._lastrowid = -1, None
+            if conn is None:
+                return
+
+            def _close() -> None:
+                conn.close()
+
+            await self._await_settled(self._hass.async_add_executor_job(_close))
+
+    def _require_conn(self) -> sqlite3.Connection:
+        if self._conn is None:
+            raise RuntimeError(
+                "The NestQuest database connection is closed; call open() first"
+            )
+        return self._conn

custom_components/nestquest/options_flow.py

@@ -0,0 +1,120 @@
+"""Options flow for NestQuest post-setup settings."""
+from __future__ import annotations
+
+import re
+from typing import Any
+
+import voluptuous as vol
+from homeassistant import config_entries
+from homeassistant.data_entry_flow import FlowResult
+
+from .const import (
+    CONF_DAY_ROLLOVER_TIME,
+    CONF_HORIZON_DAYS,
+    CONF_PANEL_IDLE_TIMEOUT,
+    DEFAULT_DAY_ROLLOVER_TIME,
+    DEFAULT_HORIZON_DAYS,
+    DEFAULT_PANEL_IDLE_TIMEOUT,
+)
+
+MIN_HORIZON_DAYS = 1
+MIN_PANEL_IDLE_TIMEOUT = 30
+
+_TIME_PATTERN = re.compile(r"(?:[01]\d|2[0-3]):[0-5]\d")
+
+
+def _parse_time(value: str) -> bool:
+    """Return True when value is a valid 24-hour HH:MM string."""
+    return _TIME_PATTERN.fullmatch(value) is not None
+
+
+def _async_validate(user_input: dict[str, Any]) -> dict[str, str]:
+    """Validate user input and return field-level errors instead of raising."""
+    errors: dict[str, str] = {}
+
+    horizon_days = user_input.get(CONF_HORIZON_DAYS)
+    if (
+        isinstance(horizon_days, bool)
+        or not isinstance(horizon_days, int)
+        or horizon_days < MIN_HORIZON_DAYS
+    ):
+        errors[CONF_HORIZON_DAYS] = "invalid"
+
+    day_rollover_time = user_input.get(CONF_DAY_ROLLOVER_TIME)
+    if not isinstance(day_rollover_time, str) or not _parse_time(day_rollover_time):
+        errors[CONF_DAY_ROLLOVER_TIME] = "invalid_time"
+
+    panel_idle_timeout = user_input.get(CONF_PANEL_IDLE_TIMEOUT)
+    if (
+        isinstance(panel_idle_timeout, bool)
+        or not isinstance(panel_idle_timeout, int)
+        or panel_idle_timeout < MIN_PANEL_IDLE_TIMEOUT
+    ):
+        errors[CONF_PANEL_IDLE_TIMEOUT] = "invalid"
+
+    return errors
+
+
+def _build_schema(current: dict[str, Any]) -> vol.Schema:
+    """Build the form schema, pre-filled with the current values."""
+    return vol.Schema(
+        {
+            vol.Required(
+                CONF_HORIZON_DAYS, default=current[CONF_HORIZON_DAYS]
+            ): int,
+            vol.Required(
+                CONF_DAY_ROLLOVER_TIME, default=current[CONF_DAY_ROLLOVER_TIME]
+            ): str,
+            vol.Required(
+                CONF_PANEL_IDLE_TIMEOUT, default=current[CONF_PANEL_IDLE_TIMEOUT]
+            ): int,
+        }
+    )
+
+
+class NestQuestOptionsFlow(config_entries.OptionsFlowWithConfigEntry):
+    """Handle the options flow for NestQuest."""
+
+    async def async_step_init(
+        self, user_input: dict[str, Any] | None = None
+    ) -> FlowResult:
+        """Manage the NestQuest options."""
+        if user_input is None:
+            return self.async_show_form(
+                step_id="init", data_schema=_build_schema(self._current_values())
+            )
+
+        errors = _async_validate(user_input)
+        if errors:
+            return self.async_show_form(
+                step_id="init",
+                data_schema=_build_schema(self._current_values()),
+                errors=errors,
+            )
+
+        return self.async_create_entry(
+            title="NestQuest",
+            data={
+                CONF_HORIZON_DAYS: user_input[CONF_HORIZON_DAYS],
+                CONF_DAY_ROLLOVER_TIME: user_input[CONF_DAY_ROLLOVER_TIME],
+                CONF_PANEL_IDLE_TIMEOUT: user_input[CONF_PANEL_IDLE_TIMEOUT],
+            },
+        )
+
+    def _current_values(self) -> dict[str, Any]:
+        """Resolve current values from entry options, then entry data, then defaults."""
+        options = self.options
+        entry_data = getattr(self.config_entry, "data", None) or {}
+        values: dict[str, Any] = {}
+        for key, default in (
+            (CONF_HORIZON_DAYS, DEFAULT_HORIZON_DAYS),
+            (CONF_DAY_ROLLOVER_TIME, DEFAULT_DAY_ROLLOVER_TIME),
+            (CONF_PANEL_IDLE_TIMEOUT, DEFAULT_PANEL_IDLE_TIMEOUT),
+        ):
+            if key in options:
+                values[key] = options[key]
+            elif key in entry_data:
+                values[key] = entry_data[key]
+            else:
+                values[key] = default
+        return values

custom_components/nestquest/schema.py

@@ -0,0 +1,151 @@
+"""Declarative schema DDL for the NestQuest SQLite database.
+
+Every v1 table is declared here as plain SQL statements so later schema
+tasks can append their own DDL to :data:`SCHEMA_V1_STATEMENTS` without
+touching this module's tables.  All statements use
+``CREATE TABLE IF NOT EXISTS`` so application is idempotent; the versioned
+migration runner is a separate later concern.
+
+Timestamp policy: all timestamps are UTC ISO-8601 strings, set by the
+caller/DAO rather than a DB default, per the feature guardrails.  No
+``length(created_at) = 25`` CHECK is added: the exact string shape
+produced by callers is not fixed yet (``datetime.isoformat()`` may
+include microseconds, changing the length), so enforcing a fixed length
+here would reject valid values.  Only non-null is enforced for now.
+
+Date policy: ``start_date``/``end_date`` are ISO date strings (YYYY-MM-DD,
+no time component) compared lexically by the ``end_date >= start_date``
+CHECK; due_date/due_time string shapes are caller-validated by the rule
+engine and DAO layers, not by DDL.
+
+``weekday_set`` format: comma-separated weekday integers (CSV), 0 =
+Monday .. 6 = Sunday, e.g. ``'0,2,4'`` for Mon/Wed/Fri.  Chosen over JSON
+because it sorts, compares and parses trivially in SQL.  The format is
+enforced at the DB level: a CHECK matches ``weekday_set`` against GLOB
+patterns for element counts 1..7 of single digits 0-6 (GLOB has no
+repetition quantifier, so the element count is enumerated; more than 7
+elements is impossible for a weekday set).  ``''``, ``'x'``, ``'7'`` and
+``'0,x'`` are all rejected; NULL stays allowed for non-weekly rules.
+This keeps the schema layer storage-only — the DAO layer (tasks
+8c72300c+) does not need its own validator, it can rely on the CHECK.
+
+Type affinity: SQLite applies column affinity on insert, but CHECKs
+still observe text and fractional values ('abc' stays text, '1.5'
+becomes REAL), so every INTEGER column carrying rule semantics carries
+a ``typeof(...) = 'integer'`` guard in its CHECK.
+
+Rule-type coherence: two CHECKs enforce that ``weekly`` carries a
+non-null ``weekday_set``, ``monthly`` carries a ``day_of_month`` or an
+``nth_weekday``, ``yearly`` carries a ``month``, and ``daily``/``custom``
+carry none of the month-specific fields.  Relaxed deliberately: the DDL
+does NOT forbid ``month`` on weekly/monthly rules (an "every 2 weeks"
+rule or a monthly rule could later be pinned to a month) nor extra
+fields on weekly/monthly/yearly rules generally, so the CHECKs only
+reject what the rule engine definitively cannot express now.
+"""
+from __future__ import annotations
+
+from .db import NestQuestDatabase
+
+#: GLOB patterns matching a valid ``weekday_set`` CSV: one GLOB per
+#: possible element count 1..7 (a weekday set cannot exceed 7 elements).
+#: Each element is a single digit 0-6, separated by a literal comma.
+#: GLOB has no repetition quantifier, so the element count is enumerated.
+#: ``''`` and NULL are deliberately NOT matched: NULL is allowed for
+#: non-weekly rules via a separate ``IS NULL`` arm, and '' is invalid
+#: because weekly rules need at least one weekday.
+WEEKDAY_SET_GLOB_PATTERNS: list[str] = [
+    "[0-6]",
+    "[0-6],[0-6]",
+    "[0-6],[0-6],[0-6]",
+    "[0-6],[0-6],[0-6],[0-6]",
+    "[0-6],[0-6],[0-6],[0-6],[0-6]",
+    "[0-6],[0-6],[0-6],[0-6],[0-6],[0-6]",
+    "[0-6],[0-6],[0-6],[0-6],[0-6],[0-6],[0-6]",
+]
+
+SCHEMA_V1_CHILDREN_DDL: list[str] = [
+    """
+    CREATE TABLE IF NOT EXISTS children (
+        id INTEGER PRIMARY KEY AUTOINCREMENT,
+        display_name TEXT NOT NULL,
+        colour TEXT,
+        avatar_ref TEXT,
+        sort_order INTEGER NOT NULL DEFAULT 0 CHECK (typeof(sort_order) = 'integer'),
+        is_active INTEGER NOT NULL DEFAULT 1 CHECK (typeof(is_active) = 'integer' AND is_active IN (0, 1)),
+        created_at TEXT NOT NULL
+    )
+    """,
+]
+
+SCHEMA_V1_ADMIN_USERS_DDL: list[str] = [
+    """
+    CREATE TABLE IF NOT EXISTS admin_users (
+        ha_user_id TEXT PRIMARY KEY NOT NULL,
+        added_at TEXT NOT NULL
+    )
+    """,
+]
+
+_WEEKDAY_SET_CHECK = " OR ".join(
+    f"weekday_set GLOB '{pattern}'" for pattern in WEEKDAY_SET_GLOB_PATTERNS
+)
+
+SCHEMA_V1_SCHEDULE_RULES_DDL: list[str] = [
+    f"""
+    CREATE TABLE IF NOT EXISTS schedule_rules (
+        id INTEGER PRIMARY KEY AUTOINCREMENT,
+        rule_type TEXT NOT NULL CHECK (rule_type IN ('daily', 'weekly', 'monthly', 'yearly', 'custom')),
+        interval INTEGER NOT NULL DEFAULT 1 CHECK (typeof(interval) = 'integer' AND interval >= 1),
+        weekday_set TEXT CHECK (weekday_set IS NULL OR ({_WEEKDAY_SET_CHECK})),
+        day_of_month INTEGER CHECK (day_of_month IS NULL OR (typeof(day_of_month) = 'integer' AND day_of_month >= 1 AND day_of_month <= 31)),
+        nth_weekday INTEGER CHECK (nth_weekday IS NULL OR (typeof(nth_weekday) = 'integer' AND nth_weekday IN (-1, 1, 2, 3, 4, 5))),
+        month INTEGER CHECK (month IS NULL OR (typeof(month) = 'integer' AND month >= 1 AND month <= 12)),
+        start_date TEXT NOT NULL,
+        end_date TEXT,
+        CHECK (end_date IS NULL OR end_date >= start_date),
+        CHECK (
+            (rule_type = 'weekly' AND weekday_set IS NOT NULL)
+            OR (rule_type = 'monthly' AND (day_of_month IS NOT NULL OR nth_weekday IS NOT NULL))
+            OR (rule_type = 'yearly' AND month IS NOT NULL)
+            OR (rule_type IN ('daily', 'custom') AND day_of_month IS NULL AND nth_weekday IS NULL AND month IS NULL)
+        )
+    )
+    """,
+]
+
+SCHEMA_V1_TASK_DEFINITIONS_DDL: list[str] = [
+    """
+    CREATE TABLE IF NOT EXISTS task_definitions (
+        id INTEGER PRIMARY KEY AUTOINCREMENT,
+        title TEXT NOT NULL,
+        description TEXT,
+        icon TEXT,
+        child_id INTEGER NOT NULL REFERENCES children(id),
+        schedule_rule_id INTEGER NOT NULL REFERENCES schedule_rules(id),
+        due_time TEXT,
+        is_active INTEGER NOT NULL DEFAULT 1 CHECK (typeof(is_active) = 'integer' AND is_active IN (0, 1)),
+        created_at TEXT NOT NULL
+    )
+    """,
+]
+
+SCHEMA_V1_STATEMENTS: list[str] = [
+    *SCHEMA_V1_CHILDREN_DDL,
+    *SCHEMA_V1_ADMIN_USERS_DDL,
+    *SCHEMA_V1_SCHEDULE_RULES_DDL,
+    *SCHEMA_V1_TASK_DEFINITIONS_DDL,
+]
+
+
+async def async_apply_ddl(
+    database: NestQuestDatabase, statements: list[str]
+) -> None:
+    """Apply each DDL statement inside one transaction on ``database``.
+
+    Idempotent: every statement is ``CREATE TABLE IF NOT EXISTS``, so
+    applying the same list twice leaves the schema and its rows intact.
+    """
+    async with database.transaction():
+        for sql in statements:
+            await database.execute(sql)

custom_components/nestquest/strings.json

@@ -13,5 +13,22 @@
     "create_entry": {
       "default": "NestQuest was successfully configured."
     }
+  },
+  "options": {
+    "step": {
+      "init": {
+        "title": "NestQuest Settings",
+        "description": "Adjust how NestQuest plans, rolls over days, and idles its panel.",
+        "data": {
+          "horizon_days": "Generation horizon (days)",
+          "day_rollover_time": "Day rollover time (HH:MM)",
+          "panel_idle_timeout": "Panel idle timeout (seconds)"
+        }
+      }
+    },
+    "errors": {
+      "invalid": "Invalid value.",
+      "invalid_time": "Time must use 24-hour HH:MM format."
+    }
   }
 }

custom_components/nestquest/translations/en.json

@@ -13,5 +13,22 @@
     "create_entry": {
       "default": "NestQuest was successfully configured."
     }
+  },
+  "options": {
+    "step": {
+      "init": {
+        "title": "NestQuest Settings",
+        "description": "Adjust how NestQuest plans, rolls over days, and idles its panel.",
+        "data": {
+          "horizon_days": "Generation horizon (days)",
+          "day_rollover_time": "Day rollover time (HH:MM)",
+          "panel_idle_timeout": "Panel idle timeout (seconds)"
+        }
+      }
+    },
+    "errors": {
+      "invalid": "Invalid value.",
+      "invalid_time": "Time must use 24-hour HH:MM format."
+    }
   }
 }

pyproject.toml

@@ -9,8 +9,11 @@ dependencies = []
 [dependency-groups]
 dev = [
     "pytest>=8.0.0",
+    "pytest-asyncio>=0.24",
+    "voluptuous>=0.14",
 ]
 
 [tool.pytest.ini_options]
 testpaths = ["tests"]
 pythonpath = ["."]
+asyncio_mode = "auto"

tests/conftest.py

@@ -0,0 +1,333 @@
+"""Shared pytest fixtures for the NestQuest test suite.
+
+pytest-homeassistant-custom-component could not be adopted: it pins
+``pytest==9.0.3`` and ``voluptuous==0.13.1``, which conflicts with the dev
+group's ``voluptuous>=0.14`` (used directly by the options flow's schema and
+its tests), and its back-releases raise the floor to Python >=3.12 while this
+project supports >=3.11.  The documented fallback is therefore used:
+``pytest-asyncio`` plus a standard-shaped ``hass`` fixture below.
+
+conftest.py is the single place where ``homeassistant`` modules are provided:
+this harness is explicitly MOCK-ONLY.  It unconditionally installs a coherent
+set of MagicMock stand-ins via ``sys.modules`` and wires the parent-package
+attributes.  All of that happens here, before any test module imports the
+integration, so no test module needs its own ``sys.modules`` setup.  It also
+provides the ``hass`` fixture plus shared helpers used by the behavior tests
+(config-entry factory, listener registry).
+
+Run this suite in its own venv *without* homeassistant installed (``uv run
+pytest -q``); it is never meant to run against a real HA install.
+"""
+
+from __future__ import annotations
+
+import asyncio
+import importlib.util
+import inspect
+import sys
+import tempfile
+import weakref
+from pathlib import Path
+from types import SimpleNamespace
+from unittest.mock import MagicMock
+
+import pytest
+
+# ---------------------------------------------------------------------------
+# Homeassistant module provisioning (explicitly mock-only).
+# ---------------------------------------------------------------------------
+_HA_MODULES = (
+    "homeassistant",
+    "homeassistant.core",
+    "homeassistant.config_entries",
+    "homeassistant.data_entry_flow",
+)
+
+def _find_spec(name: str):
+    """find_spec that returns None when the module (or its parent) is absent."""
+    try:
+        return importlib.util.find_spec(name)
+    except (ModuleNotFoundError, ValueError):
+        return None
+
+
+def _ensure_mock_only():
+    """Fail fast if real homeassistant is importable: this harness is mock-only."""
+    for _mod in _HA_MODULES:
+        # Anything already in sys.modules at load time is real HA; our mocks
+        # are only installed after this check.
+        if _mod in sys.modules or _find_spec(_mod) is not None:
+            raise pytest.UsageError(
+                "The NestQuest test harness is mock-only: real homeassistant is "
+                f"importable ({_mod!r} found), and mixing real HA modules with the "
+                "shaped mocks is not supported. Run the suite in its own venv "
+                "without homeassistant installed, e.g. `uv run pytest -q`."
+            )
+
+
+_ensure_mock_only()
+
+for _mod in _HA_MODULES:
+    sys.modules.setdefault(_mod, MagicMock())
+
+# Wire parent package attributes so "from homeassistant import
+# config_entries" resolves to the mocked submodule, mirroring real HA's
+# package layout.
+for _parent, _child in (
+    ("homeassistant", "config_entries"),
+    ("homeassistant", "data_entry_flow"),
+    ("homeassistant", "core"),
+):
+    setattr(sys.modules[_parent], _child, sys.modules[f"{_parent}.{_child}"])
+
+
+def _ha_mock(name: str):
+    """Return the shaped mock module object for a homeassistant module."""
+    return sys.modules[name]
+
+
+def options_flow_base():
+    """Return the mocked OptionsFlowWithConfigEntry class."""
+    return getattr(sys.modules["homeassistant.config_entries"], "OptionsFlowWithConfigEntry")
+
+
+class _ConfigFlowBase:
+    """Stand-in for homeassistant.config_entries.ConfigFlow.
+
+    A superset covering both the config-flow and options-flow tests: it must
+    satisfy every consumer of the mocked surface.
+    """
+
+    VERSION = 1
+
+    def __init_subclass__(cls, domain=None, **kwargs):
+        super().__init_subclass__(**kwargs)
+        cls._domain = domain
+
+    def __init__(self):
+        self.context = {}
+
+    def async_show_form(self, *, step_id, **kwargs):
+        return {"type": "form", "step_id": step_id, **kwargs}
+
+    def async_abort(self, *, reason, **kwargs):
+        return {"type": "abort", "reason": reason}
+
+    def async_create_entry(self, *, title, data, **kwargs):
+        """Return the flow result only; the flow manager persists the entry."""
+        return {"type": "create_entry", "title": title, "data": data}
+
+    async def async_set_unique_id(self, unique_id, raise_on_progress=True):
+        self.context["unique_id"] = unique_id
+        return None
+
+    def _async_current_entries(self):
+        return self.hass.config_entries.async_entries(self._domain)
+
+    def _async_in_progress(self):
+        return []
+
+    @staticmethod
+    def async_get_options_flow(config_entry):
+        raise NotImplementedError
+
+
+class _OptionsFlowWithConfigEntryBase:
+    """Stand-in mirroring HA 2024.6 config_entries.OptionsFlowWithConfigEntry."""
+
+    def __init__(self, config_entry=None):
+        self.config_entry = config_entry
+        self.hass = None
+        self.options = dict(config_entry.options) if config_entry is not None else {}
+
+    def async_show_form(self, *, step_id, **kwargs):
+        return {"type": "form", "step_id": step_id, **kwargs}
+
+    def async_create_entry(self, *, title, data, **kwargs):
+        return {"type": "create_entry", "title": title, "data": data}
+
+
+def _callback_decorator(fn):
+    """Mirror homeassistant.core.callback: flags the fn and returns it unchanged."""
+    fn.__ha_callback__ = True
+    return fn
+
+
+# The concrete 2024.6-shaped surface the integration is written against
+# (mirrors the shapes the tests previously installed per module).
+_config_entries_mock = _ha_mock("homeassistant.config_entries")
+_core_mock = _ha_mock("homeassistant.core")
+_data_entry_flow_mock = _ha_mock("homeassistant.data_entry_flow")
+
+_config_entries_mock.ConfigFlow = _ConfigFlowBase
+_config_entries_mock.OptionsFlowWithConfigEntry = _OptionsFlowWithConfigEntryBase
+_core_mock.callback = _callback_decorator
+_data_entry_flow_mock.RESULT_TYPE_FORM = "form"
+_data_entry_flow_mock.RESULT_TYPE_CREATE_ENTRY = "create_entry"
+_data_entry_flow_mock.RESULT_TYPE_ABORT = "abort"
+_data_entry_flow_mock.FlowResult = dict
+
+
+# ---------------------------------------------------------------------------
+# Shared helpers for the behavior tests.
+# ---------------------------------------------------------------------------
+class ListenerRegistry:
+    """Models HA's update-listener bookkeeping: appends to a list per entry, with per-listener removers."""
+
+    def __init__(self, hass=None):
+        self._hass = hass
+        self._listeners: dict[str, list[object]] = {}
+        self.reloaded: list[str] = []
+
+    def add(self, entry_id: str, listener) -> object:
+        def _remove():
+            self._listeners.get(entry_id, []).remove(listener)
+
+        self._listeners.setdefault(entry_id, []).append(listener)
+        return _remove
+
+    async def dispatch_options_update(self, entry) -> None:
+        """Dispatch on the running loop; sync listeners run inline, async ones awaited."""
+        for listener in list(self._listeners.get(entry.entry_id, [])):
+            result = listener(self._hass, entry)
+            if inspect.isawaitable(result):
+                await result
+
+    async_dispatch_options_update = dispatch_options_update
+
+    @property
+    def size(self) -> int:
+        return sum(len(listeners) for listeners in self._listeners.values())
+
+
+def make_config_entry(
+    entry_id: str = "test_entry",
+    options: dict | None = None,
+    data: dict | None = None,
+):
+    """Create a stub config entry shaped like HA's ConfigEntry for tests."""
+    entry = SimpleNamespace(
+        entry_id=entry_id,
+        options=dict(options or {}),
+        data=dict(data or {}),
+    )
+    entry.add_update_listener = None
+    return entry
+
+
+def wire_entry_to_registry(entry, registry: ListenerRegistry):
+    """Attach an update-listener registration function to the entry."""
+    entry.add_update_listener = lambda listener: registry.add(entry.entry_id, listener)
+    return entry
+
+
+class _HassNamespace(SimpleNamespace):
+    """SimpleNamespace subclass so the hass stub can hold weak references."""
+
+
+def make_hass() -> tuple:
+    """Create a hass stub plus its listener registry, mirroring HA's shape."""
+    config_dir = Path(tempfile.mkdtemp(prefix="nestquest-hass-"))
+    hass = _HassNamespace(
+        data={},
+        config=MagicMock(),
+        config_entries=SimpleNamespace(async_reload=None),
+    )
+
+    def _cleanup_config_dir():
+        import shutil
+
+        shutil.rmtree(config_dir, ignore_errors=True)
+
+    weakref.finalize(hass, _cleanup_config_dir)
+    hass.config.path = MagicMock(
+        side_effect=lambda name: str(config_dir / (name or "config"))
+    )
+
+    def _run_executor_job(fn, *args, **kwargs):
+        return asyncio.get_running_loop().run_in_executor(
+            None, lambda: fn(*args, **kwargs)
+        )
+
+    hass.async_add_executor_job = _run_executor_job
+    registry = ListenerRegistry(hass)
+
+    async def _async_reload(entry_id: str) -> None:
+        registry.reloaded.append(entry_id)
+
+    hass.config_entries.async_reload = MagicMock(side_effect=_async_reload)
+    hass.config_entries._entries = []
+    hass.config_entries.async_add = (
+        lambda entry: hass.config_entries._entries.append(entry)
+    )
+
+    def _async_entries(domain: str):
+        return [e for e in hass.config_entries._entries if e.domain == domain]
+
+    hass.config_entries.async_entries = _async_entries
+    return hass, registry
+
+
+@pytest.fixture
+async def hass():
+    """Provide a standard-shaped hass fixture bound to the running test loop.
+
+    Mirrors the surface of Home Assistant's ``hass`` fixture: a ``config``
+    namespace, a ``config_entries`` manager wired to a listener registry, a
+    ``data`` dict for integration state, and a ``loop`` bound to the
+    pytest-asyncio loop the test itself runs on (HA's running-loop contract),
+    so code scheduling on ``hass.loop`` runs on the actual test loop.
+    """
+    _hass, registry = make_hass()
+    _hass.registry = registry
+    _hass.loop = asyncio.get_running_loop()
+    yield _hass
+
+
+@pytest.fixture
+def make_entry():
+    """Provide the config-entry factory as a fixture."""
+
+    def _factory(entry_id: str = "test_entry", options: dict | None = None, data: dict | None = None):
+        return make_config_entry(entry_id=entry_id, options=options, data=data)
+
+    return _factory
+
+
+@pytest.fixture
+def listener_registry():
+    """Provide a fresh listener registry for manual wiring."""
+
+    def _factory(hass=None):
+        return ListenerRegistry(hass)
+
+    return _factory
+
+
+@pytest.fixture
+def make_flow():
+    """Provide a factory for a stubbed NestQuestConfigFlow bound to hass."""
+
+    def _factory(hass=None, existing_entries=None, in_progress=None):
+        from custom_components.nestquest import config_flow
+
+        flow = config_flow.NestQuestConfigFlow()
+        flow.hass = hass if hass is not None else make_hass()[0]
+        for entry in existing_entries or []:
+            if getattr(entry, "domain", None) == flow._domain:
+                flow.hass.config_entries.async_add(entry)
+        if in_progress:
+            flow._async_in_progress = lambda: in_progress
+        return flow
+
+    return _factory
+
+
+@pytest.fixture
+def make_config_flow_entry():
+    """Provide an entry stub carrying a domain, for current-entries checks."""
+
+    def _factory(domain: str, entry_id: str = "existing"):
+        return SimpleNamespace(domain=domain, entry_id=entry_id)
+
+    return _factory

tests/test_config_flow.py

@@ -4,58 +4,6 @@ from __future__ import annotations
 import ast
 import json
 from pathlib import Path
-import sys
-from types import SimpleNamespace
-from unittest.mock import MagicMock
-
-# Mock homeassistant modules if not installed in environment
-for _mod in (
-    "homeassistant",
-    "homeassistant.core",
-    "homeassistant.config_entries",
-    "homeassistant.data_entry_flow",
-):
-    sys.modules.setdefault(_mod, MagicMock())
-
-# Wire parent package attributes to the actual module mocks so that
-# "from homeassistant import config_entries" resolves to the mocked module.
-sys.modules["homeassistant"].config_entries = sys.modules["homeassistant.config_entries"]
-sys.modules["homeassistant"].data_entry_flow = sys.modules["homeassistant.data_entry_flow"]
-
-
-class _ConfigFlowBase:
-    """Minimal stand-in for homeassistant ConfigFlow."""
-
-    VERSION = 1
-
-    def __init_subclass__(cls, domain=None, **kwargs):
-        super().__init_subclass__(**kwargs)
-        cls._domain = domain
-
-    def __init__(self):
-        self.context = {}
-
-    def async_show_form(self, *, step_id, **kwargs):
-        return {"type": "form", "step_id": step_id}
-
-    def async_abort(self, *, reason, **kwargs):
-        return {"type": "abort", "reason": reason}
-
-    def async_create_entry(self, *, title, data, **kwargs):
-        return {"type": "create_entry", "title": title, "data": data}
-
-    async def async_set_unique_id(self, unique_id, raise_on_progress=True):
-        self.context["unique_id"] = unique_id
-        return None
-
-    def _async_current_entries(self):
-        return self.hass.config_entries.async_entries(self._domain)
-
-    def _async_in_progress(self):
-        return []
-
-
-sys.modules["homeassistant.config_entries"].ConfigFlow = _ConfigFlowBase
 
 import pytest
 
@@ -67,79 +15,63 @@ STRINGS_PATH = PKG_DIR / "strings.json"
 TRANSLATIONS_EN_PATH = PKG_DIR / "translations" / "en.json"
 
 
-def _run(coro):
-    import asyncio
-
-    return asyncio.new_event_loop().run_until_complete(coro)
-
-
-def _make_flow(existing_entries: list | None = None, in_progress: list | None = None):
-    """Create a config flow instance with a stubbed hass."""
-    flow = config_flow.NestQuestConfigFlow()
-    flow.hass = SimpleNamespace(
-        config_entries=SimpleNamespace(
-            async_entries=lambda domain: [
-                entry
-                for entry in (existing_entries or [])
-                if entry.domain == domain
-            ]
-        )
-    )
-    if in_progress:
-        flow._async_in_progress = lambda: in_progress
-    return flow
-
-
-def _make_entry(domain: str):
-    """Create a stub config entry with the given domain."""
-    return SimpleNamespace(domain=domain)
-
-
-def test_flow_shows_form_initially() -> None:
+async def test_flow_shows_form_initially(hass, make_flow) -> None:
     """The initial user step shows a form."""
-    flow = _make_flow()
-    result = _run(flow.async_step_user(None))
+    flow = make_flow(hass)
+    result = await flow.async_step_user(None)
     assert result["type"] == "form"
     assert result["step_id"] == "user"
 
 
-def test_flow_creates_entry_on_submit() -> None:
-    """Submitting the user step creates a config entry."""
-    flow = _make_flow()
-    result = _run(flow.async_step_user({}))
+async def test_flow_creates_entry_on_submit(hass, make_flow) -> None:
+    """Submitting the user step returns a create-entry result with the payload.
+
+    Real HA's ConfigFlow.async_create_entry only returns the flow result; the
+    flow manager persists the entry afterward.  The manager surface therefore
+    stays empty here — persistence is the manager's job, not the flow's.
+    """
+    flow = make_flow(hass)
+    assert hass.config_entries.async_entries(DOMAIN) == []
+    result = await flow.async_step_user({})
     assert result["type"] == "create_entry"
     assert result["title"] == "NestQuest"
     assert result["data"] == {}
+    assert hass.config_entries.async_entries(DOMAIN) == []
 
 
-def test_second_flow_aborts_single_instance() -> None:
+async def test_second_flow_aborts_single_instance(
+    hass, make_flow, make_config_flow_entry
+) -> None:
     """A second flow aborts with single_instance_allowed."""
-    flow = _make_flow([_make_entry(DOMAIN)])
-    result = _run(flow.async_step_user(None))
+    existing = make_config_flow_entry(DOMAIN)
+    flow = make_flow(hass, existing_entries=[existing])
+    assert hass.config_entries.async_entries(DOMAIN) == [existing]
+    result = await flow.async_step_user(None)
     assert result["type"] == "abort"
     assert result["reason"] == "single_instance_allowed"
+    assert hass.config_entries.async_entries(DOMAIN) == [existing]
 
 
-def test_flow_aborts_when_in_progress() -> None:
+async def test_flow_aborts_when_in_progress(hass, make_flow) -> None:
     """A concurrent flow in progress aborts with single_instance_allowed."""
-    flow = _make_flow(in_progress=[{"flow_id": "other"}])
-    result = _run(flow.async_step_user(None))
+    flow = make_flow(hass, in_progress=[{"flow_id": "other"}])
+    result = await flow.async_step_user(None)
     assert result["type"] == "abort"
     assert result["reason"] == "single_instance_allowed"
 
 
-def test_flow_sets_stable_unique_id() -> None:
+async def test_flow_sets_stable_unique_id(hass, make_flow) -> None:
     """The flow assigns a stable unique id equal to the domain."""
-    flow = _make_flow()
+    flow = make_flow(hass)
     assert flow.context.get("unique_id") is None
-    _run(flow.async_step_user(None))
+    await flow.async_step_user(None)
     assert flow.context["unique_id"] == DOMAIN
 
 
-def test_flow_ignores_other_domain_entries() -> None:
+async def test_flow_ignores_other_domain_entries(hass, make_flow, make_config_flow_entry) -> None:
     """Entries of other domains do not trigger the abort."""
-    flow = _make_flow([_make_entry("other_domain")])
-    result = _run(flow.async_step_user(None))
+    flow = make_flow(hass, existing_entries=[make_config_flow_entry("other_domain")])
+    result = await flow.async_step_user(None)
     assert result["type"] == "form"
     assert result["step_id"] == "user"
 

tests/test_const.py

@@ -5,12 +5,6 @@ import ast
 from datetime import datetime
 import logging
 from pathlib import Path
-import sys
-from unittest.mock import MagicMock
-
-# Mock homeassistant modules if not installed in environment
-for _mod in ("homeassistant", "homeassistant.core", "homeassistant.config_entries"):
-    sys.modules.setdefault(_mod, MagicMock())
 
 from custom_components.nestquest import const
 

tests/test_db.py

@@ -0,0 +1,998 @@
+"""Tests for db.py, the async SQLite connection wrapper of NestQuest."""
+from __future__ import annotations
+
+import ast
+import asyncio
+import sqlite3
+import threading
+from pathlib import Path
+from typing import Any
+from unittest.mock import AsyncMock, MagicMock
+
+import pytest
+
+from conftest import make_hass
+
+from custom_components.nestquest import db as db_module
+from custom_components.nestquest.db import NestQuestDatabase
+
+
+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) -> tuple[NestQuestDatabase, MagicMock]:
+    hass = _make_hass_mock()
+    database = NestQuestDatabase(hass)
+    _run(database.open(path))
+    return database, hass
+
+
+# ---------------------------------------------------------------------------
+# open(): pragmas, executor delegation, idempotence
+# ---------------------------------------------------------------------------
+
+
+def test_open_enables_wal_journal_mode(tmp_path) -> None:
+    database, _hass = _open_db(tmp_path / "wal.db")
+    row = _run(database.fetch_one("PRAGMA journal_mode"))
+    assert row is not None and str(row[0]).lower() == "wal"
+    _run(database.close())
+
+
+def test_open_enables_foreign_key_enforcement(tmp_path) -> None:
+    database, _hass = _open_db(tmp_path / "fk.db")
+    row = _run(database.fetch_one("PRAGMA foreign_keys"))
+    assert row is not None and int(row[0]) == 1
+    _run(database.close())
+
+
+def test_open_delegates_connection_to_executor(tmp_path) -> None:
+    hass = _make_hass_mock()
+    database = NestQuestDatabase(hass)
+    _run(database.open(tmp_path / "exec.db"))
+    first_call = hass.async_add_executor_job.call_args_list[0]
+    assert callable(first_call.args[0])
+    _run(database.close())
+
+
+def test_open_twice_returns_same_wrapper_without_second_connect(tmp_path) -> None:
+    path = tmp_path / "once.db"
+    hass = _make_hass_mock()
+    database = NestQuestDatabase(hass)
+    first = _run(database.open(path))
+    second = _run(database.open(path))
+    assert first is database
+    assert second is database
+    assert database.connected is True
+    opens = [
+        c
+        for c in hass.async_add_executor_job.call_args_list
+        if getattr(c.args[0], "__name__", "") == "_open"
+    ]
+    assert len(opens) == 1
+    _run(database.close())
+
+
+def test_open_accepts_str_path(tmp_path) -> None:
+    database, _hass = _open_db(str(tmp_path / "str.db"))
+    assert database.connected is True
+    _run(database.close())
+
+
+# ---------------------------------------------------------------------------
+# Executor delegation: every sqlite3 call happens off the event loop
+# ---------------------------------------------------------------------------
+
+
+def test_every_method_routes_sqlite3_through_executor(tmp_path) -> None:
+    """Each wrapper call must go via hass.async_add_executor_job."""
+    database, hass = _open_db(tmp_path / "route.db")
+    calls_before = hass.async_add_executor_job.await_count
+
+    _run(database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)"))
+    _run(database.execute("INSERT INTO t (name) VALUES (?)", ("a",)))
+    _run(database.execute_many("INSERT INTO t (name) VALUES (?)", [("b",), ("c",)]))
+    row = _run(database.fetch_one("SELECT name FROM t WHERE id = 1"))
+    rows = _run(database.fetch_all("SELECT name FROM t ORDER BY id"))
+
+    assert row == ("a",)
+    assert rows == [("a",), ("b",), ("c",)]
+    assert hass.async_add_executor_job.await_count == calls_before + 5
+    _run(database.close())
+
+
+async def test_every_wrapper_call_awaits_executor_job(tmp_path) -> None:
+    """The executor surface is awaited per wrapper call on a live loop."""
+    hass = _make_hass_mock()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "loop.db")
+
+    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
+    await database.fetch_one("SELECT 1")
+    await database.fetch_all("SELECT 1")
+    await database.execute_many("INSERT INTO t (id) VALUES (?)", [(1,)])
+    async with database.transaction():
+        pass
+
+    assert hass.async_add_executor_job.await_count == 7
+    assert all(callable(c.args[0]) for c in hass.async_add_executor_job.call_args_list)
+    await database.close()
+
+
+async def test_public_methods_return_only_plain_data(tmp_path) -> None:
+    """execute/execute_many return plain dataclasses, never sqlite3 objects."""
+    hass = _make_hass_mock()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "plain.db")
+    result = await database.execute(
+        "CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)"
+    )
+    assert not isinstance(result, (sqlite3.Connection, sqlite3.Cursor))
+    insert = await database.execute("INSERT INTO t (name) VALUES (?)", ("a",))
+    assert not isinstance(insert, (sqlite3.Connection, sqlite3.Cursor))
+    assert insert.rowcount == 1
+    assert insert.lastrowid == 1
+    many = await database.execute_many(
+        "INSERT INTO t (name) VALUES (?)", [("b",), ("c",)]
+    )
+    assert not isinstance(many, (sqlite3.Connection, sqlite3.Cursor))
+    assert many.rowcount == 2
+    row = await database.fetch_one("SELECT name FROM t WHERE id = 1")
+    assert row == ("a",)
+    rows = await database.fetch_all("SELECT name FROM t ORDER BY id")
+    assert rows == [("a",), ("b",), ("c",)]
+    for value in (*rows, row):
+        assert not isinstance(value, sqlite3.Cursor)
+    await database.close()
+
+
+async def test_no_sqlite3_call_on_event_loop_with_real_executor(tmp_path) -> None:
+    """With the conftest hass (real run_in_executor), queries still succeed."""
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "real.db")
+    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
+    assert await database.fetch_one("SELECT 1") == (1,)
+    await database.close()
+
+
+async def test_real_executor_never_touches_loop_thread(tmp_path, monkeypatch) -> None:
+    """Every real sqlite3 call must run on a non-event-loop thread.
+
+    sqlite3.connect is instrumented so a proxy wraps the connection and
+    every cursor it hands out; each proxied call records the thread it ran
+    on.  A recorded thread id equal to the event-loop thread would mean a
+    sqlite3 call crossed the executor boundary.
+    """
+    loop_thread = threading.get_ident()
+    recorded: list[int] = []
+    guard = threading.Lock()
+
+    def _note() -> None:
+        with guard:
+            recorded.append(threading.get_ident())
+
+    class _Proxy:
+        """Generic proxy recording the thread of every attribute call."""
+
+        def __init__(self, target: Any) -> None:
+            object.__setattr__(self, "_target", target)
+
+        def __getattr__(self, name: str):
+            attr = getattr(object.__getattribute__(self, "_target"), name)
+            if not callable(attr):
+                return attr
+
+            def _wrapped(*args, **kwargs):
+                _note()
+                result = attr(*args, **kwargs)
+                if isinstance(result, sqlite3.Cursor):
+                    return _Proxy(result)
+                return result
+
+            return _wrapped
+
+    real_connect = sqlite3.connect
+    connect_threads: list[int] = []
+
+    def _traced_connect(*args, **kwargs):
+        _note()
+        connect_threads.append(threading.get_ident())
+        return _Proxy(real_connect(*args, **kwargs))
+
+    monkeypatch.setattr(db_module.sqlite3, "connect", _traced_connect)
+
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "threads.db")
+    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
+    await database.execute("INSERT INTO t (id) VALUES (?)", (1,))
+    await database.execute_many("INSERT INTO t (id) VALUES (?)", [(2,), (3,)])
+    assert await database.fetch_one("SELECT id FROM t WHERE id = 1") == (1,)
+    assert await database.fetch_all("SELECT id FROM t") == [(1,), (2,), (3,)]
+    async with database.transaction():
+        await database.execute("INSERT INTO t (id) VALUES (?)", (4,))
+    await database.close()
+
+    assert recorded, "expected at least one recorded sqlite3 call"
+    assert len(connect_threads) == 1, (
+        f"expected exactly one traced sqlite3.connect, got {len(connect_threads)}"
+    )
+    assert loop_thread not in recorded, (
+        f"sqlite3 call ran on the event-loop thread {loop_thread}; "
+        f"recorded threads: {sorted(set(recorded))}"
+    )
+
+
+# ---------------------------------------------------------------------------
+# Source scan: sqlite3.* only inside executor-delegated functions
+# ---------------------------------------------------------------------------
+
+
+def _iter_with_scope(node, chain):
+    """Yield (node, enclosing-function chain), depth-first."""
+    for child in ast.iter_child_nodes(node):
+        yield child, chain
+        next_chain = chain + (child,) if isinstance(
+            child, (ast.FunctionDef, ast.AsyncFunctionDef)
+        ) else chain
+        yield from _iter_with_scope(child, next_chain)
+
+
+def test_db_sqlite3_calls_only_inside_executor_delegated_functions() -> None:
+    """Every sqlite3.* call in db.py sits inside an executor-delegating function.
+
+    A function is executor-delegating when it (or, for nested closures, any of
+    its enclosing functions) passes work to ``hass.async_add_executor_job``.
+    """
+    source = Path(db_module.__file__).read_text(encoding="utf-8")
+    tree = ast.parse(source, filename=str(db_module.__file__))
+
+    delegated = {
+        node.name
+        for node in ast.walk(tree)
+        if isinstance(node, (ast.FunctionDef, ast.AsyncFunctionDef))
+        and any(
+            isinstance(sub, ast.Call)
+            and isinstance(sub.func, ast.Attribute)
+            and sub.func.attr == "async_add_executor_job"
+            for sub in ast.walk(node)
+        )
+    }
+    assert delegated, "expected executor-delegated functions in db.py"
+
+    violations = []
+    for node, chain in _iter_with_scope(tree, ()):
+        if (
+            isinstance(node, ast.Call)
+            and isinstance(node.func, ast.Attribute)
+            and isinstance(node.func.value, ast.Name)
+            and node.func.value.id == "sqlite3"
+            and not any(f.name in delegated for f in chain)
+        ):
+            violations.append((node.lineno, node.func.attr))
+    assert violations == [], (
+        f"Direct sqlite3 calls outside executor-delegated functions: {violations}"
+    )
+
+
+def test_package_wide_sqlite3_scan_excludes_only_db_module() -> None:
+    """The package-wide store scan exempts exactly the db module."""
+    pkg_dir = Path(db_module.__file__).parent
+    scanned = [p.name for p in pkg_dir.glob("*.py") if p.name != "db.py"]
+    assert scanned, "expected other package modules to be scanned"
+    assert "db.py" not in scanned
+
+
+# ---------------------------------------------------------------------------
+# close(): idempotence and post-close errors
+# ---------------------------------------------------------------------------
+
+
+def test_close_is_idempotent(tmp_path) -> None:
+    database, hass = _open_db(tmp_path / "idem.db")
+    _run(database.close())
+    assert database.connected is False
+    _run(database.close())
+    assert database.connected is False
+    assert hass.async_add_executor_job.await_count == 2
+    assert database.rowcount == -1
+    assert database.lastrowid is None
+
+
+def test_operations_after_close_raise_clear_error(tmp_path) -> None:
+    database, _hass = _open_db(tmp_path / "closed.db")
+    _run(database.close())
+
+    async def _txn():
+        async with database.transaction():
+            pass
+
+    with pytest.raises(RuntimeError, match="closed"):
+        _run(database.execute("SELECT 1"))
+    with pytest.raises(RuntimeError, match="closed"):
+        _run(database.fetch_one("SELECT 1"))
+    with pytest.raises(RuntimeError, match="closed"):
+        _run(database.fetch_all("SELECT 1"))
+    with pytest.raises(RuntimeError, match="closed"):
+        _run(database.execute_many("SELECT 1", []))
+    with pytest.raises(RuntimeError, match="closed"):
+        _run(_txn())
+
+
+# ---------------------------------------------------------------------------
+# Transactions: commit on success, rollback on exception
+# ---------------------------------------------------------------------------
+
+
+def test_transaction_commits_on_success(tmp_path) -> None:
+    database, _hass = _open_db(tmp_path / "commit.db")
+    _run(database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)"))
+
+    async def _block():
+        async with database.transaction():
+            await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
+
+    _run(_block())
+    assert _run(database.fetch_all("SELECT name FROM t")) == [("kept",)]
+    _run(database.close())
+
+
+def test_transaction_rolls_back_on_exception(tmp_path) -> None:
+    database, _hass = _open_db(tmp_path / "rollback.db")
+    _run(database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)"))
+    _run(database.execute("INSERT INTO t (name) VALUES (?)", ("before",)))
+
+    async def _block():
+        with pytest.raises(ValueError, match="boom"):
+            async with database.transaction():
+                await database.execute(
+                    "INSERT INTO t (name) VALUES (?)", ("dropped",)
+                )
+                raise ValueError("boom")
+
+    _run(_block())
+    assert _run(database.fetch_all("SELECT name FROM t")) == [("before",)]
+    _run(database.close())
+
+
+async def test_commit_failure_rolls_back_and_next_transaction_works(
+    tmp_path,
+) -> None:
+    """A COMMIT failure rolls back and leaves the wrapper reusable.
+
+    With PRAGMA defer_foreign_keys=ON a violating insert succeeds inside
+    the transaction and the foreign-key violation only surfaces at COMMIT.
+    The commit error must propagate, the real connection must end up
+    outside any transaction, and the next transaction() plus statement
+    must run cleanly.
+    """
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "deferred.db")
+    await database.execute("CREATE TABLE parent (id INTEGER PRIMARY KEY)")
+    await database.execute(
+        "CREATE TABLE child ("
+        "id INTEGER PRIMARY KEY, "
+        "parent_id INTEGER NOT NULL REFERENCES parent(id))"
+    )
+
+    with pytest.raises(sqlite3.IntegrityError, match="FOREIGN KEY"):
+        async with database.transaction():
+            await database.execute("PRAGMA defer_foreign_keys=ON")
+            await database.execute(
+                "INSERT INTO child (parent_id) VALUES (?)", (999,)
+            )
+
+    assert database._in_transaction is False
+    assert database._tx_task is None
+    assert database._conn is not None
+    assert database._conn.in_transaction is False
+    assert await database.fetch_all("SELECT COUNT(*) FROM child") == [(0,)]
+
+    async with database.transaction():
+        await database.execute("INSERT INTO parent (id) VALUES (?)", (1,))
+        await database.execute(
+            "INSERT INTO child (parent_id) VALUES (?)", (1,)
+        )
+    assert await database.fetch_all("SELECT COUNT(*) FROM child") == [(1,)]
+    await database.close()
+
+
+async def test_cancel_during_open_closes_abandoned_connection(tmp_path) -> None:
+    """NQ-PR12-010: cancel during open() must close the settled connection.
+
+    The _open executor job is gated: the open task is cancelled while the
+    connect is still in flight, then the worker is allowed to complete it
+    anyway.  The settled connection must never be adopted, must be closed
+    through a settled executor-backed close before the cancellation
+    propagates, and a follow-up open() must succeed cleanly.
+    """
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+
+    open_started = asyncio.Event()
+    release_open = asyncio.Event()
+    jobs: list[str] = []
+    captured: dict[str, Any] = {}
+    real_add = hass.async_add_executor_job
+
+    async def _gated_add_executor_job(fn, *args, **kwargs):
+        name = getattr(fn, "__name__", "")
+        jobs.append(name)
+        if name == "_open":
+            open_started.set()
+            await release_open.wait()
+        result = await real_add(fn, *args, **kwargs)
+        if name == "_open":
+            captured["conn"] = result
+        return result
+
+    hass.async_add_executor_job = _gated_add_executor_job
+
+    open_task = asyncio.create_task(database.open(tmp_path / "cancel-open.db"))
+    await open_started.wait()
+    open_task.cancel()
+    await asyncio.sleep(0.05)
+    assert database._conn is None, (
+        "the cancelled open adopted a connection that was still settling"
+    )
+    release_open.set()
+    with pytest.raises(asyncio.CancelledError):
+        await open_task
+
+    assert database.connected is False
+    assert "_close_abandoned" in jobs, (
+        "the abandoned connection was not closed via an executor job"
+    )
+    conn = captured.get("conn")
+    assert conn is not None
+
+    def _probe() -> str:
+        try:
+            conn.execute("SELECT 1")
+        except sqlite3.ProgrammingError:
+            return "closed"
+        return "open"
+
+    assert await real_add(_probe) == "closed"
+
+    hass.async_add_executor_job = real_add
+    await database.open(tmp_path / "cancel-open.db")
+    assert database.connected is True
+    assert await database.fetch_one("SELECT 1") == (1,)
+    await database.close()
+
+
+async def test_cancel_during_begin_settles_then_rolls_back(tmp_path) -> None:
+    """NQ-PR12-007: cancel during BEGIN must not leak a stray transaction.
+
+    The BEGIN executor job is gated: the transaction task is cancelled
+    while BEGIN is still in flight, then the worker is allowed to
+    complete it anyway.  The wrapper must keep transaction ownership
+    until the submitted work settles, roll the completed BEGIN back,
+    clear its state, and stay usable for the next transaction.
+    """
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "cancel-begin.db")
+    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
+    await database.execute("INSERT INTO t (name) VALUES (?)", ("before",))
+
+    begin_started = asyncio.Event()
+    release_begin = asyncio.Event()
+    real_add = hass.async_add_executor_job
+
+    async def _gated_add_executor_job(fn, *args, **kwargs):
+        if getattr(fn, "__name__", "") == "_begin":
+            begin_started.set()
+            await release_begin.wait()
+        return await real_add(fn, *args, **kwargs)
+
+    hass.async_add_executor_job = _gated_add_executor_job
+
+    async def _txn() -> None:
+        async with database.transaction():
+            await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
+
+    txn_task = asyncio.create_task(_txn())
+    await begin_started.wait()
+    txn_task.cancel()
+    await asyncio.sleep(0.05)
+    assert database._in_transaction is True, (
+        "ownership was released while the cancelled BEGIN was still in flight"
+    )
+    release_begin.set()
+    with pytest.raises(asyncio.CancelledError):
+        await txn_task
+
+    assert database._in_transaction is False
+    assert database._tx_task is None
+    assert database._conn is not None
+    assert database._conn.in_transaction is False
+
+    hass.async_add_executor_job = real_add
+    assert await database.fetch_all("SELECT name FROM t ORDER BY id") == [
+        ("before",)
+    ]
+    async with database.transaction():
+        await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
+    assert await database.fetch_all("SELECT name FROM t ORDER BY id") == [
+        ("before",),
+        ("kept",),
+    ]
+    await database.close()
+
+
+async def test_cancel_during_commit_settles_then_propagates_cancellation(
+    tmp_path,
+) -> None:
+    """NQ-PR12-007: cancel during COMMIT must not mask the cancellation.
+
+    COMMIT is gated; the task is cancelled while it is in flight and the
+    worker then completes it anyway.  The cancellation must propagate as
+    CancelledError (not be converted by a doomed ROLLBACK of an already
+    committed transaction), the connection must end outside any
+    transaction, and the wrapper must stay usable.
+    """
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "cancel-commit.db")
+    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
+
+    commit_started = asyncio.Event()
+    release_commit = asyncio.Event()
+    real_add = hass.async_add_executor_job
+
+    async def _gated_add_executor_job(fn, *args, **kwargs):
+        if getattr(fn, "__name__", "") == "_commit":
+            commit_started.set()
+            await release_commit.wait()
+        return await real_add(fn, *args, **kwargs)
+
+    hass.async_add_executor_job = _gated_add_executor_job
+
+    async def _txn() -> None:
+        async with database.transaction():
+            await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
+
+    txn_task = asyncio.create_task(_txn())
+    await commit_started.wait()
+    txn_task.cancel()
+    await asyncio.sleep(0.05)
+    assert database._in_transaction is True, (
+        "ownership was released while the cancelled COMMIT was still in flight"
+    )
+    release_commit.set()
+    with pytest.raises(asyncio.CancelledError):
+        await txn_task
+
+    assert database._in_transaction is False
+    assert database._tx_task is None
+    assert database._conn is not None
+    assert database._conn.in_transaction is False
+
+    hass.async_add_executor_job = real_add
+    assert await database.fetch_all("SELECT name FROM t") == [("kept",)]
+    async with database.transaction():
+        await database.execute("INSERT INTO t (name) VALUES (?)", ("again",))
+    assert await database.fetch_all("SELECT name FROM t ORDER BY id") == [
+        ("kept",),
+        ("again",),
+    ]
+    await database.close()
+
+
+async def test_cancel_during_in_transaction_execute_settles_then_rolls_back(
+    tmp_path,
+) -> None:
+    """NQ-PR12-009: cancel during an in-transaction statement must not race ROLLBACK.
+
+    The in-transaction INSERT executor job is gated: the transaction task
+    is cancelled while the statement is still in flight, then the worker
+    is allowed to complete it anyway.  The ROLLBACK must only run after
+    the statement has settled — while the statement is in flight the
+    transaction is still open — and the cancelled statement's write must
+    not persist.
+    """
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "cancel-execute.db")
+    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
+    await database.execute("INSERT INTO t (name) VALUES (?)", ("before",))
+
+    insert_started = asyncio.Event()
+    release_insert = asyncio.Event()
+    real_add = hass.async_add_executor_job
+
+    async def _gated_add_executor_job(fn, *args, **kwargs):
+        if getattr(fn, "__name__", "") == "_execute":
+            insert_started.set()
+            await release_insert.wait()
+        return await real_add(fn, *args, **kwargs)
+
+    hass.async_add_executor_job = _gated_add_executor_job
+
+    async def _txn() -> None:
+        async with database.transaction():
+            await database.execute("INSERT INTO t (name) VALUES (?)", ("lost",))
+
+    txn_task = asyncio.create_task(_txn())
+    await insert_started.wait()
+    txn_task.cancel()
+    await asyncio.sleep(0.05)
+    assert database._in_transaction is True, (
+        "ownership was released while the cancelled statement was in flight"
+    )
+    assert database._conn is not None
+    assert database._conn.in_transaction is True, (
+        "ROLLBACK ran before the cancelled statement settled"
+    )
+    release_insert.set()
+    with pytest.raises(asyncio.CancelledError):
+        await txn_task
+
+    assert database._in_transaction is False
+    assert database._tx_task is None
+    assert database._conn is not None
+    assert database._conn.in_transaction is False
+
+    hass.async_add_executor_job = real_add
+    assert await database.fetch_all("SELECT name FROM t ORDER BY id") == [
+        ("before",)
+    ]
+    async with database.transaction():
+        await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
+    assert await database.fetch_all("SELECT name FROM t ORDER BY id") == [
+        ("before",),
+        ("kept",),
+    ]
+    await database.close()
+
+
+async def test_commit_and_rollback_failures_are_both_preserved(tmp_path) -> None:
+    """NQ-PR12-008: both errors survive when COMMIT and ROLLBACK both fail.
+
+    The COMMIT executor call is forced to fail, and the follow-up
+    cleanup ROLLBACK is forced to fail as well.  The raised error must
+    keep both original exceptions inspectable instead of discarding the
+    commit failure behind the rollback failure.
+    """
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "both-fail.db")
+    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
+
+    commit_failure = sqlite3.OperationalError("commit exploded")
+    rollback_failure = sqlite3.OperationalError("rollback exploded")
+    real_add = hass.async_add_executor_job
+
+    async def _failing_add_executor_job(fn, *args, **kwargs):
+        name = getattr(fn, "__name__", "")
+        if name == "_commit":
+            raise commit_failure
+        if name == "_cleanup":
+            raise rollback_failure
+        return await real_add(fn, *args, **kwargs)
+
+    hass.async_add_executor_job = _failing_add_executor_job
+
+    with pytest.raises(BaseExceptionGroup) as exc_info:
+        async with database.transaction():
+            pass
+
+    assert commit_failure in exc_info.value.exceptions
+    assert rollback_failure in exc_info.value.exceptions
+    assert database._in_transaction is False
+    assert database._tx_task is None
+    await database.close()
+
+
+async def test_transaction_persists_across_connections(tmp_path) -> None:
+    """A committed transaction is visible to a brand-new connection."""
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "persist.db")
+    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
+    async with database.transaction():
+        await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
+    await database.close()
+
+    other = NestQuestDatabase(hass)
+    await other.open(tmp_path / "persist.db")
+    assert await other.fetch_all("SELECT name FROM t") == [("kept",)]
+    await other.close()
+
+
+# ---------------------------------------------------------------------------
+# Serialization: one lock per wrapper, no cross-commit interleaving
+# ---------------------------------------------------------------------------
+
+
+async def test_concurrent_writers_do_not_interleave_commits(tmp_path) -> None:
+    """Real-executor proof: nothing commits inside another transaction's span.
+
+    A transaction runner repeatedly counts rows, inserts, and counts again
+    inside BEGIN..COMMIT; the count must grow by exactly one, proving no
+    other writer committed in between even though several writer tasks fire
+    statements concurrently on real executor threads.  Statements issued
+    while a transaction is open queue behind it on the wrapper lock.
+    """
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "serial.db")
+    await database.execute("CREATE TABLE t (kind TEXT, seq INTEGER)")
+    tx_rounds, writers, writer_rounds = 6, 5, 8
+    total = tx_rounds + writers * writer_rounds
+
+    async def _tx_runner() -> None:
+        for rnd in range(tx_rounds):
+            async with database.transaction():
+                rows = await database.fetch_all("SELECT COUNT(*) FROM t")
+                seen = rows[0][0] if rows else -1
+                await database.execute(
+                    "INSERT INTO t (kind, seq) VALUES (?, ?)", ("tx", rnd)
+                )
+                rows = await database.fetch_all("SELECT COUNT(*) FROM t")
+                grown = rows[0][0] if rows else -1
+                if grown != seen + 1:
+                    raise AssertionError(
+                        f"interleaved commit: count {seen} -> {grown} "
+                        f"in tx round {rnd}"
+                    )
+
+    async def _writer(worker: int) -> None:
+        for rnd in range(writer_rounds):
+            await database.execute(
+                "INSERT INTO t (kind, seq) VALUES (?, ?)", ("w", worker)
+            )
+
+    await asyncio.gather(
+        _tx_runner(), *(_writer(w) for w in range(writers))
+    )
+
+    rows = await database.fetch_all("SELECT COUNT(*) FROM t")
+    assert rows[0][0] == total, f"expected {total} rows, got {rows[0][0]}"
+    await database.close()
+
+
+async def test_transaction_span_excludes_concurrent_transaction(tmp_path) -> None:
+    """A second transaction caller is rejected with a clear error."""
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "reject.db")
+    await database.execute("CREATE TABLE t (id INTEGER)")
+
+    started = asyncio.Event()
+
+    async def _first() -> None:
+        async with database.transaction():
+            started.set()
+            await asyncio.sleep(0.05)
+
+    first = asyncio.create_task(_first())
+    await started.wait()
+    with pytest.raises(RuntimeError, match="transaction"):
+        async with database.transaction():
+            pass
+    await first
+    await database.execute("INSERT INTO t (id) VALUES (?)", (1,))
+    assert await database.fetch_one("SELECT id FROM t") == (1,)
+    await database.close()
+
+
+async def test_transaction_blocks_other_statements_until_commit(tmp_path) -> None:
+    """A statement issued during an open transaction queues behind it."""
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "block.db")
+    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
+
+    order: list[str] = []
+    started = asyncio.Event()
+    release = asyncio.Event()
+
+    async def _txn() -> None:
+        async with database.transaction():
+            await database.execute("INSERT INTO t (name) VALUES (?)", ("in_tx",))
+            order.append("tx-start")
+            started.set()
+            await release.wait()
+            order.append("tx-end")
+
+    async def _outsider() -> None:
+        await started.wait()
+        await database.execute("INSERT INTO t (name) VALUES (?)", ("after",))
+        order.append("outside")
+
+    txn_task = asyncio.create_task(_txn())
+    await started.wait()
+    outsider_task = asyncio.create_task(_outsider())
+    await asyncio.sleep(0.05)
+    assert order == ["tx-start"], f"outsider ran inside transaction: {order}"
+    release.set()
+    await asyncio.gather(txn_task, outsider_task)
+    assert order == ["tx-start", "tx-end", "outside"]
+    rows = await database.fetch_all("SELECT name FROM t ORDER BY id")
+    assert rows == [("in_tx",), ("after",)]
+    await database.close()
+
+
+async def test_concurrent_transaction_is_rejected_with_clear_error(
+    tmp_path,
+) -> None:
+    """A second concurrent transaction() gets a clear RuntimeError."""
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "concurrent-tx.db")
+    await database.execute("CREATE TABLE t (id INTEGER)")
+
+    started = asyncio.Event()
+
+    async def _first() -> None:
+        async with database.transaction():
+            started.set()
+            await asyncio.sleep(0.05)
+
+    first = asyncio.create_task(_first())
+    await started.wait()
+    with pytest.raises(RuntimeError, match="transaction"):
+        async with database.transaction():
+            pass
+    await first
+    await database.close()
+
+
+async def test_close_waits_for_in_flight_transaction(tmp_path) -> None:
+    """close() serializes: an open transaction finishes before shutdown."""
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "closerace.db")
+    await database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
+
+    started = asyncio.Event()
+    release = asyncio.Event()
+
+    async def _txn() -> None:
+        async with database.transaction():
+            await database.execute("INSERT INTO t (name) VALUES (?)", ("kept",))
+            started.set()
+            await release.wait()
+
+    txn_task = asyncio.create_task(_txn())
+    await started.wait()
+    close_task = asyncio.create_task(database.close())
+    await asyncio.sleep(0.05)
+    assert database.connected is True, "close() raced an open transaction"
+    release.set()
+    await asyncio.gather(txn_task, close_task)
+    assert database.connected is False
+
+    witness = NestQuestDatabase(hass)
+    await witness.open(tmp_path / "closerace.db")
+    assert await witness.fetch_all("SELECT name FROM t") == [("kept",)]
+    await witness.close()
+
+
+async def test_close_during_transaction_from_same_task_is_rejected(
+    tmp_path,
+) -> None:
+    """close() inside its own transaction body raises instead of deadlocking."""
+    hass, _registry = make_hass()
+    database = NestQuestDatabase(hass)
+    await database.open(tmp_path / "selfclose.db")
+    await database.execute("CREATE TABLE t (id INTEGER)")
+    with pytest.raises(RuntimeError, match="transaction"):
+        async with database.transaction():
+            await database.close()
+    assert database.connected is True
+    await database.close()
+
+
+# ---------------------------------------------------------------------------
+# rowcount / lastrowid surface
+# ---------------------------------------------------------------------------
+
+
+def test_rowcount_and_lastrowid_before_any_operation(tmp_path) -> None:
+    database, _hass = _open_db(tmp_path / "unset.db")
+    assert database.rowcount == -1
+    assert database.lastrowid is None
+    _run(database.close())
+
+
+def test_execute_exposes_rowcount_and_lastrowid(tmp_path) -> None:
+    database, _hass = _open_db(tmp_path / "counts.db")
+    _run(database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)"))
+    _run(database.execute("INSERT INTO t (name) VALUES (?)", ("x",)))
+    assert database.rowcount == 1
+    assert database.lastrowid == 1
+    _run(database.execute("INSERT INTO t (name) VALUES (?)", ("y",)))
+    assert database.rowcount == 1
+    assert database.lastrowid == 2
+    _run(database.close())
+
+
+def test_execute_many_exposes_rowcount(tmp_path) -> None:
+    database, _hass = _open_db(tmp_path / "many.db")
+    _run(database.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)"))
+    _run(database.execute_many("INSERT INTO t (name) VALUES (?)", [("a",), ("b",)]))
+    assert database.rowcount == 2
+    _run(database.execute("INSERT INTO t (name) VALUES (?)", ("c",)))
+    assert database.lastrowid == 3
+    _run(database.close())
+
+
+# ---------------------------------------------------------------------------
+# Lifecycle wiring: opened once per entry, closed on unload
+# ---------------------------------------------------------------------------
+
+
+async def test_setup_entry_opens_database_in_runtime_data(hass, make_entry) -> None:
+    from conftest import wire_entry_to_registry
+
+    from custom_components.nestquest import async_setup_entry, async_unload_entry
+
+    entry = wire_entry_to_registry(make_entry(), hass.registry)
+    assert await async_setup_entry(hass, entry) is True
+    runtime = entry.runtime_data
+    assert runtime.database is not None
+    assert runtime.database.connected is True
+    await async_unload_entry(hass, entry)
+
+
+async def test_unload_entry_closes_database(hass, make_entry) -> None:
+    from conftest import wire_entry_to_registry
+
+    from custom_components.nestquest import async_setup_entry, async_unload_entry
+
+    entry = wire_entry_to_registry(make_entry(), hass.registry)
+    await async_setup_entry(hass, entry)
+    database = entry.runtime_data.database
+    assert database.connected is True
+    assert await async_unload_entry(hass, entry) is True
+    assert database.connected is False
+    with pytest.raises(RuntimeError, match="closed"):
+        await database.fetch_one("SELECT 1")
+
+
+async def test_second_setup_does_not_open_second_connection(hass, make_entry) -> None:
+    from conftest import wire_entry_to_registry
+
+    from custom_components.nestquest import async_setup_entry, async_unload_entry
+
+    entry = wire_entry_to_registry(make_entry(), hass.registry)
+    await async_setup_entry(hass, entry)
+    first = entry.runtime_data.database
+
+    await async_setup_entry(hass, entry)
+    second = entry.runtime_data.database
+
+    assert first is second
+    assert first.connected is True
+    await async_unload_entry(hass, entry)
+
+
+async def test_setup_unload_cycles_reopen_fresh_connection(hass, make_entry) -> None:
+    from conftest import wire_entry_to_registry
+
+    from custom_components.nestquest import async_setup_entry, async_unload_entry
+
+    entry = wire_entry_to_registry(make_entry(), hass.registry)
+    seen: list[NestQuestDatabase] = []
+    for _ in range(3):
+        await async_setup_entry(hass, entry)
+        runtime = entry.runtime_data
+        assert runtime.database.connected is True
+        seen.append(runtime.database)
+        await async_unload_entry(hass, entry)
+        assert runtime.database.connected is False
+    assert len(set(seen)) == 3
+    assert hass.data == {}

tests/test_harness.py

@@ -0,0 +1,86 @@
+"""Contract test for the mock-only homeassistant harness in conftest.py."""
+
+from __future__ import annotations
+
+import asyncio
+import importlib.metadata
+import inspect
+import sys
+from unittest.mock import MagicMock
+
+import pytest
+
+import conftest
+
+from conftest import _HA_MODULES, _ensure_mock_only
+
+
+def test_mock_only_harness_contract(monkeypatch):
+    """The harness is mock-only: shaped mocks everywhere, fail-fast on real HA."""
+    # Every required homeassistant module in sys.modules is a mock stand-in
+    # with the shaped surface, and parent-package attributes point at them.
+    for name in _HA_MODULES:
+        assert isinstance(sys.modules[name], MagicMock), f"{name} is not a mock"
+
+    config_entries = sys.modules["homeassistant.config_entries"]
+    assert config_entries.ConfigFlow is conftest._ConfigFlowBase
+    assert config_entries.OptionsFlowWithConfigEntry is (
+        conftest._OptionsFlowWithConfigEntryBase
+    )
+    assert sys.modules["homeassistant"].config_entries is config_entries
+    assert sys.modules["homeassistant.core"].callback is conftest._callback_decorator
+    assert sys.modules["homeassistant.data_entry_flow"].RESULT_TYPE_FORM == "form"
+
+    # No homeassistant distribution is installed in this suite's own venv.
+    with pytest.raises(importlib.metadata.PackageNotFoundError):
+        importlib.metadata.version("homeassistant")
+
+    # Fail-fast paths: a findable real HA module, or one preloaded in
+    # sys.modules, both abort collection with a UsageError.
+    real_spec = MagicMock(origin="/fake/site-packages/homeassistant/__init__.py")
+    monkeypatch.setattr(conftest, "_find_spec", lambda name: real_spec)
+    with pytest.raises(pytest.UsageError, match="mock-only"):
+        _ensure_mock_only()
+
+    monkeypatch.setattr(conftest, "_find_spec", lambda name: None)
+    monkeypatch.setitem(sys.modules, "homeassistant", MagicMock(name="real-module"))
+    with pytest.raises(pytest.UsageError, match="mock-only"):
+        _ensure_mock_only()
+
+
+async def test_hass_and_registry_run_on_the_running_test_loop(hass, make_entry):
+    """hass.loop is the test's own loop and the registry spins no private loop."""
+    # hass.loop is the very loop the async test runs on (HA running-loop
+    # contract): code scheduling on hass.loop lands on the test loop itself.
+    assert hass.loop is asyncio.get_running_loop()
+    ran = []
+
+    async def _scheduled():
+        ran.append(True)
+
+    await hass.loop.create_task(_scheduled())
+    assert ran == [True]
+
+    # ListenerRegistry never spins its own event loop (no resource leak) and
+    # dispatch is a plain coroutine: sync listeners run inline, async awaited.
+    registry = hass.registry
+    assert not hasattr(registry, "_loop")
+    assert inspect.iscoroutinefunction(registry.dispatch_options_update)
+
+    calls = []
+
+    def _sync_listener(h, entry):
+        calls.append(("sync", entry.entry_id, h is hass))
+
+    entry = make_entry(entry_id="disp")
+    registry.add("disp", _sync_listener)
+    await registry.dispatch_options_update(entry)
+    assert calls == [("sync", "disp", True)]
+
+    async def _async_listener(h, entry):
+        calls.append(("async", entry.entry_id))
+
+    registry.add("disp", _async_listener)
+    calls.clear()
+    await registry.async_dispatch_options_update(entry)
+    assert calls == [("sync", "disp", True), ("async", "disp")]

tests/test_lifecycle.py

@@ -2,249 +2,257 @@
 from __future__ import annotations
 
 import asyncio
-import inspect
-import sys
-from types import SimpleNamespace
-from unittest.mock import MagicMock
-
-# Mock homeassistant modules if not installed in environment
-for _mod in (
-    "homeassistant",
-    "homeassistant.core",
-    "homeassistant.config_entries",
-):
-    sys.modules.setdefault(_mod, MagicMock())
-
-# Wire parent package attributes to the actual module mocks so that
-# "from homeassistant import config_entries" resolves to the mocked module.
-sys.modules["homeassistant"].config_entries = sys.modules["homeassistant.config_entries"]
 
 import pytest
 
+from conftest import make_config_entry, make_hass, wire_entry_to_registry
+
 from custom_components.nestquest import DOMAIN, async_setup_entry, async_unload_entry
 from custom_components.nestquest.const import DOMAIN as DOMAIN_CONST
 
 
-def _run(coro):
-    return asyncio.new_event_loop().run_until_complete(coro)
+def _wire(entry, registry):
+    return wire_entry_to_registry(entry, registry)
 
 
-class _ListenerRegistry:
-    """Models HA's update-listener bookkeeping: appends to a list per entry, with per-listener removers."""
-
-    def __init__(self, hass=None):
-        self._hass = hass
-        self._listeners: dict[str, list[object]] = {}
-        self.reloaded: list[str] = []
-        self._loop = asyncio.new_event_loop()
-
-    def add(self, entry_id: str, listener) -> object:
-        def _remove():
-            self._listeners.get(entry_id, []).remove(listener)
-
-        self._listeners.setdefault(entry_id, []).append(listener)
-        return _remove
-
-    def dispatch_options_update(self, entry) -> None:
-        for listener in list(self._listeners.get(entry.entry_id, [])):
-            result = listener(self._hass, entry)
-            if inspect.isawaitable(result):
-                self._loop.run_until_complete(result)
-
-    @property
-    def size(self) -> int:
-        return sum(len(listeners) for listeners in self._listeners.values())
-
-
-def _make_hass():
-    hass = SimpleNamespace(
-        data={},
-        config_entries=SimpleNamespace(
-            async_reload=None,
-        ),
-    )
-    registry = _ListenerRegistry(hass)
-    async def _async_reload(entry_id: str) -> None:
-        registry.reloaded.append(entry_id)
-
-    hass.config_entries.async_reload = MagicMock(side_effect=_async_reload)
-    return hass, registry
-
-
-def _make_entry(entry_id: str = "test_entry", options: dict | None = None):
-    entry = SimpleNamespace(entry_id=entry_id, options=dict(options or {}))
-    entry.add_update_listener = None
-    return entry
-
-
-def _wire_entry(entry, registry: _ListenerRegistry):
-    entry.add_update_listener = lambda listener: registry.add(entry.entry_id, listener)
-    return entry
-
-
-def test_setup_stores_runtime_data_in_hass_data() -> None:
-    hass, _ = _make_hass()
-    entry = _wire_entry(_make_entry(), _ListenerRegistry())
-    assert _run(async_setup_entry(hass, entry)) is True
+async def test_setup_stores_runtime_data_in_hass_data(hass, make_entry) -> None:
+    entry = _wire(make_entry(), hass.registry)
+    assert await async_setup_entry(hass, entry) is True
     assert entry.entry_id in hass.data[DOMAIN]
     assert hass.data[DOMAIN][entry.entry_id] is entry.runtime_data
 
 
-def test_setup_runtime_data_has_entry_id_and_options() -> None:
-    hass, _ = _make_hass()
-    entry = _wire_entry(_make_entry(options={"horizon_days": 7}), _ListenerRegistry())
-    _run(async_setup_entry(hass, entry))
+async def test_setup_runtime_data_has_entry_id_and_options(hass, make_entry) -> None:
+    entry = _wire(make_entry(options={"horizon_days": 7}), hass.registry)
+    await async_setup_entry(hass, entry)
     runtime = entry.runtime_data
     assert runtime.entry_id == entry.entry_id
     assert runtime.options == {"horizon_days": 7}
     assert callable(runtime.remove_update_listener)
 
 
-def test_unload_returns_true_and_removes_hass_data() -> None:
-    hass, _ = _make_hass()
-    registry = _ListenerRegistry()
-    entry = _wire_entry(_make_entry(), registry)
-    _run(async_setup_entry(hass, entry))
-    assert _run(async_unload_entry(hass, entry)) is True
-    assert entry.entry_id not in hass.data[DOMAIN]
-
-
-def test_unload_is_idempotent() -> None:
-    hass, _ = _make_hass()
-    registry = _ListenerRegistry()
-    entry = _wire_entry(_make_entry(), registry)
-    _run(async_setup_entry(hass, entry))
-    assert _run(async_unload_entry(hass, entry)) is True
-    assert _run(async_unload_entry(hass, entry)) is True
-    assert entry.entry_id not in hass.data[DOMAIN]
-
-
-def test_unload_without_setup_returns_true() -> None:
-    hass, _ = _make_hass()
-    entry = _make_entry()
-    assert _run(async_unload_entry(hass, entry)) is True
-    assert DOMAIN not in hass.data or entry.entry_id not in hass.data[DOMAIN]
-
-
-def test_unload_clears_entry_runtime_data() -> None:
-    hass, _ = _make_hass()
-    registry = _ListenerRegistry()
-    entry = _wire_entry(_make_entry(), registry)
-    _run(async_setup_entry(hass, entry))
-    _run(async_unload_entry(hass, entry))
-    assert entry.runtime_data is None
-
-
-def test_unload_removes_update_listener() -> None:
-    hass, _ = _make_hass()
-    registry = _ListenerRegistry()
-    entry = _wire_entry(_make_entry(), registry)
-    _run(async_setup_entry(hass, entry))
+async def test_unload_returns_true_and_removes_hass_data(hass, make_entry) -> None:
+    registry = hass.registry
+    entry = _wire(make_entry(), registry)
+    assert await async_setup_entry(hass, entry) is True
     assert registry.size == 1
-    _run(async_unload_entry(hass, entry))
+    assert await async_unload_entry(hass, entry) is True
+    assert hass.data == {}
+    assert DOMAIN not in hass.data
+    assert entry.runtime_data is None
     assert registry.size == 0
 
 
-def test_options_update_triggers_reload_once() -> None:
-    hass, registry = _make_hass()
-    entry = _wire_entry(_make_entry(), registry)
-    _run(async_setup_entry(hass, entry))
+async def test_unload_of_one_entry_keeps_domain_while_other_remains(
+    hass, make_entry
+) -> None:
+    """Unloading one of two entries keeps DOMAIN alive with the other entry."""
+    registry = hass.registry
+    entry_a = _wire(make_entry(entry_id="entry_a"), registry)
+    entry_b = _wire(make_entry(entry_id="entry_b"), registry)
+    assert await async_setup_entry(hass, entry_a) is True
+    assert await async_setup_entry(hass, entry_b) is True
+    assert set(hass.data[DOMAIN]) == {"entry_a", "entry_b"}
+
+    assert await async_unload_entry(hass, entry_a) is True
+    assert DOMAIN in hass.data
+    assert set(hass.data[DOMAIN]) == {"entry_b"}
+    assert hass.data[DOMAIN]["entry_b"] is entry_b.runtime_data
+    assert entry_a.runtime_data is None
+    assert entry_b.runtime_data is not None
+    assert registry.size == 1
+
+    assert await async_unload_entry(hass, entry_b) is True
+    assert DOMAIN not in hass.data
+    assert hass.data == {}
+    assert entry_b.runtime_data is None
+    assert registry.size == 0
+
+
+async def test_unload_is_idempotent(hass, make_entry) -> None:
+    entry = _wire(make_entry(), hass.registry)
+    assert await async_setup_entry(hass, entry) is True
+    assert await async_unload_entry(hass, entry) is True
+    assert await async_unload_entry(hass, entry) is True
+    assert DOMAIN not in hass.data
+    assert hass.data == {}
+
+
+async def test_unload_without_setup_returns_true(hass, make_entry) -> None:
+    entry = _wire(make_entry(), hass.registry)
+    assert await async_unload_entry(hass, entry) is True
+    assert DOMAIN not in hass.data
+    assert hass.data == {}
+
+
+async def test_unload_clears_entry_runtime_data(hass, make_entry) -> None:
+    entry = _wire(make_entry(), hass.registry)
+    await async_setup_entry(hass, entry)
+    await async_unload_entry(hass, entry)
+    assert entry.runtime_data is None
+
+
+async def test_unload_removes_update_listener(hass, make_entry) -> None:
+    registry = hass.registry
+    entry = _wire(make_entry(), registry)
+    await async_setup_entry(hass, entry)
+    assert registry.size == 1
+    await async_unload_entry(hass, entry)
+    assert registry.size == 0
+
+
+async def test_options_update_triggers_reload_once(hass, make_entry) -> None:
+    registry = hass.registry
+    entry = _wire(make_entry(), registry)
+    await async_setup_entry(hass, entry)
     entry.options = {"horizon_days": 30}
-    registry.dispatch_options_update(entry)
+    await registry.async_dispatch_options_update(entry)
     assert hass.config_entries.async_reload.call_count == 1
     assert registry.reloaded == [entry.entry_id]
 
 
-def test_no_reload_after_unload() -> None:
-    hass, registry = _make_hass()
-    entry = _wire_entry(_make_entry(), registry)
-    _run(async_setup_entry(hass, entry))
-    _run(async_unload_entry(hass, entry))
-    registry.dispatch_options_update(entry)
+async def test_no_reload_after_unload(hass, make_entry) -> None:
+    registry = hass.registry
+    entry = _wire(make_entry(), registry)
+    await async_setup_entry(hass, entry)
+    await async_unload_entry(hass, entry)
+    await registry.async_dispatch_options_update(entry)
     assert hass.config_entries.async_reload.call_count == 0
 
 
-def test_three_reload_cycles_keep_single_listener() -> None:
-    hass, registry = _make_hass()
-    entry = _wire_entry(_make_entry(), registry)
+async def test_three_reload_cycles_keep_single_listener(hass, make_entry) -> None:
+    registry = hass.registry
+    entry = _wire(make_entry(), registry)
     for _ in range(3):
-        assert _run(async_setup_entry(hass, entry)) is True
+        assert await async_setup_entry(hass, entry) is True
         assert registry.size == 1
-        assert _run(async_unload_entry(hass, entry)) is True
+        assert await async_unload_entry(hass, entry) is True
         assert registry.size == 0
     assert hass.config_entries.async_reload.call_count == 0
 
 
-def test_three_setup_unload_cycles_leave_no_listeners() -> None:
+async def test_three_setup_unload_cycles_leave_no_listeners(hass, make_entry) -> None:
     """Listeners accumulate per entry (HA semantics); unload must remove them all."""
-    hass, registry = _make_hass()
-    entry = _wire_entry(_make_entry(), registry)
+    registry = hass.registry
+    entry = _wire(make_entry(), registry)
     for _ in range(3):
-        _run(async_setup_entry(hass, entry))
-        _run(async_unload_entry(hass, entry))
+        await async_setup_entry(hass, entry)
+        await async_unload_entry(hass, entry)
     assert registry._listeners.get(entry.entry_id, []) == []
     assert registry.size == 0
 
 
-def test_options_update_after_each_setup_invokes_exactly_one_listener() -> None:
+async def test_options_update_after_each_setup_invokes_exactly_one_listener(
+    hass, make_entry
+) -> None:
     """After each setup, an options update dispatches to exactly one listener."""
-    hass, registry = _make_hass()
-    entry = _wire_entry(_make_entry(), registry)
+    registry = hass.registry
+    entry = _wire(make_entry(), registry)
     for _ in range(3):
-        _run(async_setup_entry(hass, entry))
-        registry.dispatch_options_update(entry)
+        await async_setup_entry(hass, entry)
+        await registry.async_dispatch_options_update(entry)
         assert hass.config_entries.async_reload.call_count == 1
         assert registry.reloaded == [entry.entry_id]
-        _run(async_unload_entry(hass, entry))
+        await async_unload_entry(hass, entry)
         hass.config_entries.async_reload.reset_mock()
         registry.reloaded.clear()
 
 
-def test_reload_setup_cycles_dispatch_reload_each_time() -> None:
-    hass, registry = _make_hass()
-    entry = _wire_entry(_make_entry(), registry)
+async def test_reload_setup_cycles_dispatch_reload_each_time(hass, make_entry) -> None:
+    registry = hass.registry
+    entry = _wire(make_entry(), registry)
     for _ in range(3):
-        _run(async_setup_entry(hass, entry))
-        registry.dispatch_options_update(entry)
-        _run(async_unload_entry(hass, entry))
+        await async_setup_entry(hass, entry)
+        await registry.async_dispatch_options_update(entry)
+        await async_unload_entry(hass, entry)
     assert hass.config_entries.async_reload.call_count == 3
     assert registry.size == 0
 
 
-def test_no_orphaned_tasks_after_unload() -> None:
-    async def _scenario():
-        hass, registry = _make_hass()
-        entry = _wire_entry(_make_entry(), registry)
-        await async_setup_entry(hass, entry)
-        await async_unload_entry(hass, entry)
-        pending = [task for task in asyncio.all_tasks() if task is not asyncio.current_task()]
-        assert pending == []
-
-    _run(_scenario())
+async def test_no_orphaned_tasks_after_unload(hass, make_entry) -> None:
+    entry = _wire(make_entry(), hass.registry)
+    await async_setup_entry(hass, entry)
+    await async_unload_entry(hass, entry)
+    pending = [task for task in asyncio.all_tasks() if task is not asyncio.current_task()]
+    assert pending == []
 
 
-def test_setup_entry_uses_domain_constant() -> None:
-    hass, _ = _make_hass()
-    entry = _wire_entry(_make_entry(), _ListenerRegistry())
-    _run(async_setup_entry(hass, entry))
+async def test_setup_entry_uses_domain_constant(hass, make_entry) -> None:
+    entry = _wire(make_entry(), hass.registry)
+    await async_setup_entry(hass, entry)
     assert set(hass.data) == {DOMAIN_CONST}
 
 
-def test_setup_awaitable_listener_removal_supported() -> None:
+async def test_setup_awaitable_listener_removal_supported() -> None:
     """A coroutine-returning remover is awaited during unload."""
+    removed = []
 
-    async def _scenario():
-        removed = []
+    async def _remover():
+        removed.append(True)
 
-        async def _remover():
-            removed.append(True)
+    entry = make_config_entry(entry_id="awaitable")
+    entry.add_update_listener = lambda listener: _remover
+    hass, _registry = make_hass()
+    await async_setup_entry(hass, entry)
+    result = await async_unload_entry(hass, entry)
+    assert result is True
+    assert removed == [True]
+    assert hass.data == {}
+    assert entry.runtime_data is None
 
-        entry = SimpleNamespace(entry_id="awaitable", options={})
-        entry.add_update_listener = lambda listener: _remover
-        hass = SimpleNamespace(data={}, config_entries=SimpleNamespace(async_reload=None))
+
+async def test_setup_closes_database_when_listener_registration_fails() -> None:
+    """A listener-registration failure must not leak the opened database."""
+    from custom_components.nestquest.db import NestQuestDatabase
+
+    class _Boom(Exception):
+        pass
+
+    entry = make_config_entry(entry_id="listener-boom")
+    entry.add_update_listener = lambda listener: (_ for _ in ()).throw(_Boom())
+    hass, _registry = make_hass()
+    with pytest.raises(_Boom):
         await async_setup_entry(hass, entry)
-        result = await async_unload_entry(hass, entry)
-        assert result is True
-        assert removed == [True]
+    assert hass.data.get(DOMAIN, {}) == {}
+    assert not hasattr(entry, "runtime_data")
 
-    _run(_scenario())
+
+async def test_unload_closes_database_even_when_listener_removal_raises() -> None:
+    """Unload removes the listener, closes the DB in a finally, then raises."""
+    from custom_components.nestquest.db import NestQuestDatabase
+
+    class _RemoveBoom(Exception):
+        pass
+
+    def _remover():
+        raise _RemoveBoom()
+
+    entry = make_config_entry(entry_id="remove-boom")
+    entry.add_update_listener = lambda listener: _remover
+    hass, _registry = make_hass()
+    await async_setup_entry(hass, entry)
+    database = entry.runtime_data.database
+    with pytest.raises(_RemoveBoom):
+        await async_unload_entry(hass, entry)
+    assert database.connected is False, "database leaked despite remover error"
+    assert entry.runtime_data is None
+    assert DOMAIN not in hass.data
+
+
+async def test_setup_applies_all_v1_tables(hass, make_entry) -> None:
+    """Setup applies the full v1 DDL: all four v1 tables exist afterwards."""
+    entry = _wire(make_entry(), hass.registry)
+    assert await async_setup_entry(hass, entry) is True
+    database = entry.runtime_data.database
+    rows = await database.fetch_all(
+        "SELECT name FROM sqlite_master WHERE type = 'table' "
+        "AND name IN ('children', 'admin_users', 'schedule_rules', "
+        "'task_definitions') ORDER BY name"
+    )
+    assert [row[0] for row in rows] == [
+        "admin_users",
+        "children",
+        "schedule_rules",
+        "task_definitions",
+    ]
+    await async_unload_entry(hass, entry)

tests/test_manifest.py

@@ -3,12 +3,6 @@ from __future__ import annotations
 
 import json
 from pathlib import Path
-import sys
-from unittest.mock import MagicMock
-
-# Mock homeassistant modules if not installed in environment
-for _mod in ("homeassistant", "homeassistant.core", "homeassistant.config_entries"):
-    sys.modules.setdefault(_mod, MagicMock())
 
 import pytest
 

tests/test_options_flow.py

@@ -0,0 +1,376 @@
+"""Tests for the options flow of NestQuest."""
+from __future__ import annotations
+
+import ast
+import asyncio
+import json
+from pathlib import Path
+from types import SimpleNamespace
+
+import pytest
+
+from conftest import make_config_entry, make_hass, options_flow_base as _options_flow_base
+
+from custom_components.nestquest import config_flow
+from custom_components.nestquest import options_flow
+from custom_components.nestquest.const import (
+    CONF_DAY_ROLLOVER_TIME,
+    CONF_HORIZON_DAYS,
+    CONF_PANEL_IDLE_TIMEOUT,
+    DEFAULT_DAY_ROLLOVER_TIME,
+    DEFAULT_HORIZON_DAYS,
+    DEFAULT_PANEL_IDLE_TIMEOUT,
+    DOMAIN,
+)
+
+PKG_DIR = Path(options_flow.__file__).parent
+STRINGS_PATH = PKG_DIR / "strings.json"
+TRANSLATIONS_EN_PATH = PKG_DIR / "translations" / "en.json"
+
+VALID_INPUT = {
+    CONF_HORIZON_DAYS: 7,
+    CONF_DAY_ROLLOVER_TIME: "03:30",
+    CONF_PANEL_IDLE_TIMEOUT: 60,
+}
+
+
+def _run(coro):
+    return asyncio.new_event_loop().run_until_complete(coro)
+
+
+def _make_hass():
+    return make_hass()
+
+
+def _make_entry(entry_id: str = "test_entry", options: dict | None = None, data: dict | None = None):
+    return make_config_entry(entry_id=entry_id, options=options, data=data)
+
+
+def _make_flow(entry) -> options_flow.NestQuestOptionsFlow:
+    """Create an options flow instance bound to a stubbed config entry."""
+    flow = options_flow.NestQuestOptionsFlow(config_entry=entry)
+    flow.hass = SimpleNamespace()
+    return flow
+
+
+def _schema_values(schema) -> dict:
+    """Extract the per-field default values from a real voluptuous schema."""
+    return {
+        marker.schema: marker.default()
+        for marker in schema.schema
+        if hasattr(marker, "schema")
+    }
+
+
+def test_options_flow_reachable_via_configure_button() -> None:
+    """The config flow exposes async_get_options_flow returning the options flow."""
+    entry = _make_entry()
+    handler = config_flow.NestQuestConfigFlow.async_get_options_flow(entry)
+    assert isinstance(handler, options_flow.NestQuestOptionsFlow)
+    assert handler.config_entry is entry
+
+
+def test_options_flow_get_options_flow_is_ha_callback() -> None:
+    """async_get_options_flow is wrapped by the homeassistant.core.callback decorator."""
+    assert getattr(
+        config_flow.NestQuestConfigFlow.async_get_options_flow, "__ha_callback__", False
+    ) is True
+
+
+def test_options_flow_constructs_through_2024_6_surface() -> None:
+    """The handler constructs against the mocked 2024.6 base and inherits its contract."""
+    entry = _make_entry(options={CONF_HORIZON_DAYS: 21})
+    flow = options_flow.NestQuestOptionsFlow(config_entry=entry)
+    assert isinstance(flow, _options_flow_base())
+    assert flow.config_entry is entry
+    assert flow.options == {CONF_HORIZON_DAYS: 21}
+
+
+def test_options_flow_submits_through_2024_6_surface() -> None:
+    """The handler can be submitted through the mocked 2024.6-shaped surface."""
+    entry = _make_entry()
+    handler = config_flow.NestQuestConfigFlow.async_get_options_flow(entry)
+    result = _run(handler.async_step_init(dict(VALID_INPUT)))
+    assert result["type"] == "create_entry"
+    assert result["title"] == "NestQuest"
+    assert result["data"] == VALID_INPUT
+
+
+def test_options_flow_shows_form_initially() -> None:
+    """The init step shows a form."""
+    flow = _make_flow(_make_entry())
+    result = _run(flow.async_step_init(None))
+    assert result["type"] == "form"
+    assert result["step_id"] == "init"
+
+
+def test_options_flow_schema_is_voluptuous() -> None:
+    """The form's data_schema is a real voluptuous Schema (no dict fallback)."""
+    flow = _make_flow(_make_entry())
+    result = _run(flow.async_step_init(None))
+    import voluptuous as vol
+
+    assert isinstance(result["data_schema"], vol.Schema)
+
+
+def test_options_flow_prefills_from_defaults() -> None:
+    """Without stored options, the form pre-fills with the constant defaults."""
+    flow = _make_flow(_make_entry())
+    result = _run(flow.async_step_init(None))
+    values = _schema_values(result["data_schema"])
+    assert values[CONF_HORIZON_DAYS] == DEFAULT_HORIZON_DAYS
+    assert values[CONF_DAY_ROLLOVER_TIME] == DEFAULT_DAY_ROLLOVER_TIME
+    assert values[CONF_PANEL_IDLE_TIMEOUT] == DEFAULT_PANEL_IDLE_TIMEOUT
+
+
+def test_options_flow_prefills_from_entry_options() -> None:
+    """Stored entry options win over entry data and defaults."""
+    entry = _make_entry(
+        options={
+            CONF_HORIZON_DAYS: 21,
+            CONF_DAY_ROLLOVER_TIME: "05:45",
+            CONF_PANEL_IDLE_TIMEOUT: 120,
+        },
+        data={
+            CONF_HORIZON_DAYS: 99,
+            CONF_DAY_ROLLOVER_TIME: "23:59",
+            CONF_PANEL_IDLE_TIMEOUT: 999,
+        },
+    )
+    result = _run(_make_flow(entry).async_step_init(None))
+    values = _schema_values(result["data_schema"])
+    assert values[CONF_HORIZON_DAYS] == 21
+    assert values[CONF_DAY_ROLLOVER_TIME] == "05:45"
+    assert values[CONF_PANEL_IDLE_TIMEOUT] == 120
+
+
+def test_options_flow_falls_back_to_entry_data() -> None:
+    """Pre-upgrade entries without options fall back to entry.data."""
+    entry = _make_entry(
+        data={
+            CONF_HORIZON_DAYS: 10,
+            CONF_DAY_ROLLOVER_TIME: "01:15",
+            CONF_PANEL_IDLE_TIMEOUT: 90,
+        }
+    )
+    result = _run(_make_flow(entry).async_step_init(None))
+    values = _schema_values(result["data_schema"])
+    assert values[CONF_HORIZON_DAYS] == 10
+    assert values[CONF_DAY_ROLLOVER_TIME] == "01:15"
+    assert values[CONF_PANEL_IDLE_TIMEOUT] == 90
+
+
+def test_options_flow_schema_applies_stored_defaults() -> None:
+    """A real voluptuous validate({}) fills every field from the stored defaults."""
+    entry = _make_entry(
+        options={
+            CONF_HORIZON_DAYS: 21,
+            CONF_DAY_ROLLOVER_TIME: "05:45",
+            CONF_PANEL_IDLE_TIMEOUT: 120,
+        }
+    )
+    result = _run(_make_flow(entry).async_step_init(None))
+    assert result["data_schema"]({}) == {
+        CONF_HORIZON_DAYS: 21,
+        CONF_DAY_ROLLOVER_TIME: "05:45",
+        CONF_PANEL_IDLE_TIMEOUT: 120,
+    }
+
+
+def test_options_flow_schema_coerces_field_types() -> None:
+    """The schema validates the expected field types via voluptuous."""
+    flow = _make_flow(_make_entry())
+    result = _run(flow.async_step_init(None))
+    schema = result["data_schema"]
+    validators = {marker.schema: validator for marker, validator in schema.schema.items()}
+    assert validators[CONF_HORIZON_DAYS] is int
+    assert validators[CONF_DAY_ROLLOVER_TIME] is str
+    assert validators[CONF_PANEL_IDLE_TIMEOUT] is int
+
+
+@pytest.mark.parametrize("bad_horizon", [0, -5])
+def test_options_flow_rejects_non_positive_horizon(bad_horizon: int) -> None:
+    """Zero or negative horizon values yield a field-level error, no exception."""
+    flow = _make_flow(_make_entry())
+    result = _run(
+        flow.async_step_init({**VALID_INPUT, CONF_HORIZON_DAYS: bad_horizon})
+    )
+    assert result["type"] == "form"
+    assert result["errors"] == {CONF_HORIZON_DAYS: "invalid"}
+
+
+def test_options_flow_rejects_malformed_time_not_a_time() -> None:
+    """Non-HH:MM strings are rejected with invalid_time."""
+    flow = _make_flow(_make_entry())
+    result = _run(
+        flow.async_step_init({**VALID_INPUT, CONF_DAY_ROLLOVER_TIME: "noon"})
+    )
+    assert result["type"] == "form"
+    assert result["errors"] == {CONF_DAY_ROLLOVER_TIME: "invalid_time"}
+
+
+def test_options_flow_rejects_malformed_time_out_of_range() -> None:
+    """Out-of-range HH:MM values are rejected with invalid_time."""
+    flow = _make_flow(_make_entry())
+    result = _run(
+        flow.async_step_init({**VALID_INPUT, CONF_DAY_ROLLOVER_TIME: "25:99"})
+    )
+    assert result["type"] == "form"
+    assert result["errors"] == {CONF_DAY_ROLLOVER_TIME: "invalid_time"}
+
+
+def test_options_flow_rejects_pasted_unicode_superscript_time() -> None:
+    """Pasted Unicode digits like '²²:²²' yield a field-level error, no exception."""
+    flow = _make_flow(_make_entry())
+    result = _run(
+        flow.async_step_init({**VALID_INPUT, CONF_DAY_ROLLOVER_TIME: "²²:²²"})
+    )
+    assert result["type"] == "form"
+    assert result["errors"] == {CONF_DAY_ROLLOVER_TIME: "invalid_time"}
+
+
+@pytest.mark.parametrize("bad_timeout", ["abc", 0, -10, 10])
+def test_options_flow_rejects_invalid_idle_timeout(bad_timeout) -> None:
+    """Non-numeric or too-low idle timeouts yield a field-level error."""
+    flow = _make_flow(_make_entry())
+    result = _run(
+        flow.async_step_init({**VALID_INPUT, CONF_PANEL_IDLE_TIMEOUT: bad_timeout})
+    )
+    assert result["type"] == "form"
+    assert result["errors"] == {CONF_PANEL_IDLE_TIMEOUT: "invalid"}
+
+
+def test_options_flow_creates_entry_with_three_keys() -> None:
+    """A valid submit creates an entry with exactly the three validated keys."""
+    flow = _make_flow(_make_entry())
+    result = _run(flow.async_step_init(dict(VALID_INPUT)))
+    assert result["type"] == "create_entry"
+    assert result["title"] == "NestQuest"
+    assert set(result["data"]) == {
+        CONF_HORIZON_DAYS,
+        CONF_DAY_ROLLOVER_TIME,
+        CONF_PANEL_IDLE_TIMEOUT,
+    }
+    assert result["data"] == VALID_INPUT
+
+
+def test_options_flow_create_entry_data_lands_in_entry_options_and_reloads_once() -> None:
+    """The flow's created data is written to entry.options and reloads exactly once."""
+    hass, registry = _make_hass()
+    entry = _make_entry()
+    entry.add_update_listener = lambda listener: registry.add(entry.entry_id, listener)
+
+    # Simulate setup registering the merged lifecycle update listener.
+    from custom_components.nestquest import async_setup_entry
+
+    _run(async_setup_entry(hass, entry))
+
+    # Run the options flow and apply its created data to entry.options like HA does.
+    flow = _make_flow(entry)
+    result = _run(flow.async_step_init(dict(VALID_INPUT)))
+    assert result["type"] == "create_entry"
+    entry.options = dict(result["data"])
+
+    _run(registry.dispatch_options_update(entry))
+    assert hass.config_entries.async_reload.call_count == 1
+    assert registry.reloaded == [entry.entry_id]
+    assert entry.options == VALID_INPUT
+
+
+def test_options_flow_validation_never_raises() -> None:
+    """Validation returns errors instead of raising for arbitrary junk input."""
+    junk = {CONF_HORIZON_DAYS: None, CONF_DAY_ROLLOVER_TIME: None, CONF_PANEL_IDLE_TIMEOUT: None}
+    flow = _make_flow(_make_entry())
+    result = _run(flow.async_step_init(junk))
+    assert result["type"] == "form"
+    assert result["errors"] == {
+        CONF_HORIZON_DAYS: "invalid",
+        CONF_DAY_ROLLOVER_TIME: "invalid_time",
+        CONF_PANEL_IDLE_TIMEOUT: "invalid",
+    }
+
+
+@pytest.fixture
+def strings() -> dict:
+    """Load and parse strings.json."""
+    assert STRINGS_PATH.exists(), f"strings.json does not exist at {STRINGS_PATH}"
+    with open(STRINGS_PATH, encoding="utf-8") as f:
+        return json.load(f)
+
+
+@pytest.fixture
+def translations_en() -> dict:
+    """Load and parse translations/en.json."""
+    assert TRANSLATIONS_EN_PATH.exists(), (
+        f"translations/en.json does not exist at {TRANSLATIONS_EN_PATH}"
+    )
+    with open(TRANSLATIONS_EN_PATH, encoding="utf-8") as f:
+        return json.load(f)
+
+
+def test_options_strings_present_and_matching(strings: dict, translations_en: dict) -> None:
+    """Both string files carry identical options step/error strings."""
+    assert strings.get("options") == translations_en.get("options")
+    options = strings["options"]
+    init = options["step"]["init"]
+    assert init["title"]
+    assert init["description"]
+    data = init["data"]
+    for key in (CONF_HORIZON_DAYS, CONF_DAY_ROLLOVER_TIME, CONF_PANEL_IDLE_TIMEOUT):
+        assert data.get(key), f"Missing field name for '{key}'"
+    errors = options["errors"]
+    for key in ("invalid", "invalid_time"):
+        assert errors.get(key), f"Missing error string for '{key}'"
+
+
+def test_options_strings_no_raw_keys(strings: dict, translations_en: dict) -> None:
+    """No placeholder/raw keys appear in the options strings."""
+
+    def check(value) -> None:
+        if isinstance(value, dict):
+            for key, val in value.items():
+                assert val, f"Empty string value for key '{key}'"
+                check(val)
+        elif isinstance(value, str):
+            assert value.strip() != ""
+
+    check(strings["options"])
+    check(translations_en["options"])
+
+
+def test_options_flow_no_plain_dict_schema_fallback() -> None:
+    """No try/except ImportError fallback for voluptuous in the options flow module."""
+    py_file = Path(options_flow.__file__)
+    tree = ast.parse(py_file.read_text(encoding="utf-8"), filename=str(py_file))
+    for node in ast.walk(tree):
+        if isinstance(node, ast.Try):
+            handlers = node.handlers or []
+            has_import_error_handler = any(
+                getattr(handler.type, "id", getattr(handler.type, "attr", None))
+                == "ImportError"
+                for handler in handlers
+            )
+            assert not has_import_error_handler, (
+                f"Plain-dict schema fallback (try/except ImportError) found in {py_file.name}:{node.lineno}"
+            )
+
+
+def test_options_flow_subclasses_options_flow_with_config_entry() -> None:
+    """NestQuestOptionsFlow subclasses config_entries.OptionsFlowWithConfigEntry."""
+    base = _options_flow_base()
+    assert issubclass(options_flow.NestQuestOptionsFlow, base)
+    assert options_flow.NestQuestOptionsFlow.__bases__ == (base,)
+
+
+def test_options_flow_no_hardcoded_domain_or_db_filename() -> None:
+    """The options flow module does not hardcode the domain or db filename."""
+    py_file = Path(options_flow.__file__)
+    tree = ast.parse(py_file.read_text(encoding="utf-8"), filename=str(py_file))
+    for node in ast.walk(tree):
+        if isinstance(node, ast.Constant) and isinstance(node.value, str):
+            assert node.value != DOMAIN, (
+                f"Hard-coded domain string in {py_file.name}:{node.lineno}"
+            )
+            assert node.value != "nestquest.db", (
+                f"Hard-coded database filename in {py_file.name}:{node.lineno}"
+            )

tests/test_schema.py

@@ -0,0 +1,917 @@
+"""Tests for schema.py: v1 DDL for children and admin_users tables."""
+from __future__ import annotations
+
+import asyncio
+import sqlite3
+
+import pytest
+
+from custom_components.nestquest.db import NestQuestDatabase
+from custom_components.nestquest.schema import (
+    SCHEMA_V1_ADMIN_USERS_DDL,
+    SCHEMA_V1_CHILDREN_DDL,
+    SCHEMA_V1_SCHEDULE_RULES_DDL,
+    SCHEMA_V1_STATEMENTS,
+    SCHEMA_V1_TASK_DEFINITIONS_DDL,
+    async_apply_ddl,
+)
+
+
+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
+
+
+def _open_db(path) -> NestQuestDatabase:
+    database = NestQuestDatabase(_make_hass_mock())
+    _run(database.open(path))
+    return database
+
+
+def _apply(database) -> None:
+    _run(async_apply_ddl(database, SCHEMA_V1_STATEMENTS))
+
+
+# Sentinel used by the insert helpers: omit the column from the INSERT so
+# its DDL DEFAULT applies (explicit NULL would not trigger a DEFAULT).
+_OMIT = object()
+
+
+def _insert_rule(database, **overrides):
+    """Insert one valid schedule_rule, returning the rule id.
+
+    Pass ``_OMIT`` as a value to leave that column out of the INSERT so
+    its DDL DEFAULT applies.
+    """
+    values: dict[str, object] = {
+        "rule_type": "weekly",
+        "weekday_set": "0,2,4",
+        "start_date": "2026-09-13",
+    }
+    values.update(overrides)
+    values = {k: v for k, v in values.items() if v is not _OMIT}
+    columns = ", ".join(values)
+    placeholders = ", ".join("?" for _ in values)
+    result = _run(
+        database.execute(
+            f"INSERT INTO schedule_rules ({columns}) VALUES ({placeholders})",
+            tuple(values.values()),
+        )
+    )
+    assert result.lastrowid is not None
+    return result.lastrowid
+
+
+def _insert_definition(database, child_id=1, rule_id=1, **overrides):
+    """Insert one valid task_definition, returning the definition id.
+
+    Pass ``_OMIT`` as a value to leave that column out of the INSERT so
+    its DDL DEFAULT applies.
+    """
+    values: dict[str, object] = {
+        "title": "Brush teeth",
+        "child_id": child_id,
+        "schedule_rule_id": rule_id,
+        "created_at": "2026-09-13T00:00:00+00:00",
+    }
+    values.update(overrides)
+    values = {k: v for k, v in values.items() if v is not _OMIT}
+    columns = ", ".join(values)
+    placeholders = ", ".join("?" for _ in values)
+    result = _run(
+        database.execute(
+            f"INSERT INTO task_definitions ({columns}) VALUES ({placeholders})",
+            tuple(values.values()),
+        )
+    )
+    assert result.lastrowid is not None
+    return result.lastrowid
+
+
+# ---------------------------------------------------------------------------
+# DDL constants
+# ---------------------------------------------------------------------------
+
+
+def test_schema_v1_statements_compose_the_four_tables() -> None:
+    assert SCHEMA_V1_STATEMENTS == [
+        *SCHEMA_V1_CHILDREN_DDL,
+        *SCHEMA_V1_ADMIN_USERS_DDL,
+        *SCHEMA_V1_SCHEDULE_RULES_DDL,
+        *SCHEMA_V1_TASK_DEFINITIONS_DDL,
+    ]
+
+
+def test_every_statement_is_create_if_not_exists() -> None:
+    for sql in SCHEMA_V1_STATEMENTS:
+        assert sql.lstrip().upper().startswith("CREATE TABLE IF NOT EXISTS")
+
+
+# ---------------------------------------------------------------------------
+# Tables after applying DDL
+# ---------------------------------------------------------------------------
+
+
+def test_children_table_exists_after_applying_ddl(tmp_path) -> None:
+    database = _open_db(tmp_path / "children.db")
+    try:
+        _apply(database)
+        row = _run(
+            database.fetch_one(
+                "SELECT name FROM sqlite_master WHERE type = 'table' "
+                "AND name = 'children'"
+            )
+        )
+        assert row is not None and row[0] == "children"
+    finally:
+        _run(database.close())
+
+
+def test_admin_users_table_exists_after_applying_ddl(tmp_path) -> None:
+    database = _open_db(tmp_path / "admins.db")
+    try:
+        _apply(database)
+        row = _run(
+            database.fetch_one(
+                "SELECT name FROM sqlite_master WHERE type = 'table' "
+                "AND name = 'admin_users'"
+            )
+        )
+        assert row is not None and row[0] == "admin_users"
+    finally:
+        _run(database.close())
+
+
+def test_children_columns_types_and_constraints(tmp_path) -> None:
+    database = _open_db(tmp_path / "children-columns.db")
+    try:
+        _apply(database)
+        columns = _run(database.fetch_all("PRAGMA table_info(children)"))
+        # cid, name, type, notnull, dflt_value, pk
+        assert [(row[1], row[2], row[3], row[4], row[5]) for row in columns] == [
+            ("id", "INTEGER", 0, None, 1),
+            ("display_name", "TEXT", 1, None, 0),
+            ("colour", "TEXT", 0, None, 0),
+            ("avatar_ref", "TEXT", 0, None, 0),
+            ("sort_order", "INTEGER", 1, "0", 0),
+            ("is_active", "INTEGER", 1, "1", 0),
+            ("created_at", "TEXT", 1, None, 0),
+        ]
+    finally:
+        _run(database.close())
+
+
+def test_admin_users_columns_types_and_constraints(tmp_path) -> None:
+    database = _open_db(tmp_path / "admins-columns.db")
+    try:
+        _apply(database)
+        columns = _run(database.fetch_all("PRAGMA table_info(admin_users)"))
+        assert [(row[1], row[2], row[3], row[4], row[5]) for row in columns] == [
+            ("ha_user_id", "TEXT", 1, None, 1),
+            ("added_at", "TEXT", 1, None, 0),
+        ]
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_table_exists_after_applying_ddl(tmp_path) -> None:
+    database = _open_db(tmp_path / "rules.db")
+    try:
+        _apply(database)
+        row = _run(
+            database.fetch_one(
+                "SELECT name FROM sqlite_master WHERE type = 'table' "
+                "AND name = 'schedule_rules'"
+            )
+        )
+        assert row is not None and row[0] == "schedule_rules"
+    finally:
+        _run(database.close())
+
+
+def test_task_definitions_table_exists_after_applying_ddl(tmp_path) -> None:
+    database = _open_db(tmp_path / "definitions.db")
+    try:
+        _apply(database)
+        row = _run(
+            database.fetch_one(
+                "SELECT name FROM sqlite_master WHERE type = 'table' "
+                "AND name = 'task_definitions'"
+            )
+        )
+        assert row is not None and row[0] == "task_definitions"
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_columns_types_and_constraints(tmp_path) -> None:
+    database = _open_db(tmp_path / "rules-columns.db")
+    try:
+        _apply(database)
+        columns = _run(database.fetch_all("PRAGMA table_info(schedule_rules)"))
+        # cid, name, type, notnull, dflt_value, pk
+        assert [(row[1], row[2], row[3], row[4], row[5]) for row in columns] == [
+            ("id", "INTEGER", 0, None, 1),
+            ("rule_type", "TEXT", 1, None, 0),
+            ("interval", "INTEGER", 1, "1", 0),
+            ("weekday_set", "TEXT", 0, None, 0),
+            ("day_of_month", "INTEGER", 0, None, 0),
+            ("nth_weekday", "INTEGER", 0, None, 0),
+            ("month", "INTEGER", 0, None, 0),
+            ("start_date", "TEXT", 1, None, 0),
+            ("end_date", "TEXT", 0, None, 0),
+        ]
+    finally:
+        _run(database.close())
+
+
+def test_task_definitions_columns_types_and_constraints(tmp_path) -> None:
+    database = _open_db(tmp_path / "definitions-columns.db")
+    try:
+        _apply(database)
+        columns = _run(database.fetch_all("PRAGMA table_info(task_definitions)"))
+        assert [(row[1], row[2], row[3], row[4], row[5]) for row in columns] == [
+            ("id", "INTEGER", 0, None, 1),
+            ("title", "TEXT", 1, None, 0),
+            ("description", "TEXT", 0, None, 0),
+            ("icon", "TEXT", 0, None, 0),
+            ("child_id", "INTEGER", 1, None, 0),
+            ("schedule_rule_id", "INTEGER", 1, None, 0),
+            ("due_time", "TEXT", 0, None, 0),
+            ("is_active", "INTEGER", 1, "1", 0),
+            ("created_at", "TEXT", 1, None, 0),
+        ]
+    finally:
+        _run(database.close())
+
+
+def test_task_definitions_foreign_keys_declared(tmp_path) -> None:
+    database = _open_db(tmp_path / "definitions-fks.db")
+    try:
+        _apply(database)
+        fks = _run(database.fetch_all("PRAGMA foreign_key_list(task_definitions)"))
+        declared = {(row[2], row[3], row[4]) for row in fks}
+        assert declared == {
+            ("children", "child_id", "id"),
+            ("schedule_rules", "schedule_rule_id", "id"),
+        }
+    finally:
+        _run(database.close())
+
+
+# ---------------------------------------------------------------------------
+# Constraint enforcement
+# ---------------------------------------------------------------------------
+
+
+def test_children_display_name_not_null_enforced(tmp_path) -> None:
+    database = _open_db(tmp_path / "display-name.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="NOT NULL"):
+            _run(
+                database.execute(
+                    "INSERT INTO children (display_name, created_at) "
+                    "VALUES (NULL, ?)",
+                    ("2026-09-13T00:00:00+00:00",),
+                )
+            )
+    finally:
+        _run(database.close())
+
+
+def test_children_is_active_check_enforced(tmp_path) -> None:
+    database = _open_db(tmp_path / "is-active.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _run(
+                database.execute(
+                    "INSERT INTO children (display_name, is_active, created_at) "
+                    "VALUES (?, ?, ?)",
+                    ("Ada", 2, "2026-09-13T00:00:00+00:00"),
+                )
+            )
+    finally:
+        _run(database.close())
+
+
+@pytest.mark.parametrize(
+    ("column", "bad_value"),
+    [
+        ("sort_order", "abc"),
+        ("sort_order", 1.5),
+        ("is_active", "abc"),
+        ("is_active", 1.5),
+    ],
+)
+def test_children_integer_columns_reject_text_and_fraction(
+    tmp_path, column, bad_value
+) -> None:
+    database = _open_db(tmp_path / f"children-{column}-typeof.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _run(
+                database.execute(
+                    f"INSERT INTO children (display_name, {column}, created_at) "
+                    "VALUES (?, ?, ?)",
+                    ("Ada", bad_value, "2026-09-13T00:00:00+00:00"),
+                )
+            )
+    finally:
+        _run(database.close())
+
+
+def test_children_created_at_not_null_enforced(tmp_path) -> None:
+    database = _open_db(tmp_path / "created-at.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="NOT NULL"):
+            _run(
+                database.execute(
+                    "INSERT INTO children (display_name, created_at) "
+                    "VALUES (?, NULL)",
+                    ("Ada",),
+                )
+            )
+    finally:
+        _run(database.close())
+
+
+def test_admin_users_primary_key_enforced(tmp_path) -> None:
+    database = _open_db(tmp_path / "pk.db")
+    try:
+        _apply(database)
+        _run(
+            database.execute(
+                "INSERT INTO admin_users (ha_user_id, added_at) VALUES (?, ?)",
+                ("user-1", "2026-09-13T00:00:00+00:00"),
+            )
+        )
+        with pytest.raises(sqlite3.IntegrityError, match="UNIQUE"):
+            _run(
+                database.execute(
+                    "INSERT INTO admin_users (ha_user_id, added_at) VALUES (?, ?)",
+                    ("user-1", "2026-09-13T00:00:01+00:00"),
+                )
+            )
+    finally:
+        _run(database.close())
+
+
+def test_admin_users_added_at_not_null_enforced(tmp_path) -> None:
+    database = _open_db(tmp_path / "added-at.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="NOT NULL"):
+            _run(
+                database.execute(
+                    "INSERT INTO admin_users (ha_user_id, added_at) "
+                    "VALUES (?, NULL)",
+                    ("user-1",),
+                )
+            )
+    finally:
+        _run(database.close())
+
+
+def test_children_defaults_apply_on_insert(tmp_path) -> None:
+    database = _open_db(tmp_path / "defaults.db")
+    try:
+        _apply(database)
+        _run(
+            database.execute(
+                "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
+                ("Ada", "2026-09-13T00:00:00+00:00"),
+            )
+        )
+        row = _run(
+            database.fetch_one(
+                "SELECT sort_order, is_active FROM children "
+                "WHERE display_name = 'Ada'"
+            )
+        )
+        assert row == (0, 1)
+    finally:
+        _run(database.close())
+
+
+# ---------------------------------------------------------------------------
+# Idempotence and transactional application
+# ---------------------------------------------------------------------------
+
+
+def test_applying_ddl_twice_succeeds_and_keeps_rows(tmp_path) -> None:
+    database = _open_db(tmp_path / "idempotent.db")
+    try:
+        _apply(database)
+        _run(
+            database.execute(
+                "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
+                ("Ada", "2026-09-13T00:00:00+00:00"),
+            )
+        )
+        _apply(database)
+        row = _run(
+            database.fetch_one("SELECT COUNT(*) FROM children")
+        )
+        assert row == (1,)
+    finally:
+        _run(database.close())
+
+
+def test_failed_statement_rolls_back_whole_application(tmp_path) -> None:
+    from unittest.mock import AsyncMock
+
+    database = _open_db(tmp_path / "rollback.db")
+    try:
+        bad_statements = [
+            "CREATE TABLE IF NOT EXISTS children (id INTEGER PRIMARY KEY)",
+            "CREATE TABLE nope (",
+        ]
+        original = database.execute
+
+        async def _failing_execute(sql, parameters=()):
+            if "CREATE TABLE nope" in sql:
+                raise sqlite3.OperationalError("near \"(\": syntax error")
+            return await original(sql, parameters)
+
+        database.execute = AsyncMock(side_effect=_failing_execute)
+        with pytest.raises(sqlite3.OperationalError):
+            _run(async_apply_ddl(database, bad_statements))
+        row = _run(
+            database.fetch_one(
+                "SELECT name FROM sqlite_master WHERE type = 'table' "
+                "AND name = 'children'"
+            )
+        )
+        assert row is None, "partial DDL must not survive a failed application"
+    finally:
+        _run(database.close())
+
+
+# ---------------------------------------------------------------------------
+# schedule_rules and task_definitions constraint enforcement
+# ---------------------------------------------------------------------------
+
+
+def test_schedule_rules_valid_insert_round_trips(tmp_path) -> None:
+    database = _open_db(tmp_path / "valid-rule.db")
+    try:
+        _apply(database)
+        rule_id = _insert_rule(database)
+        row = _run(
+            database.fetch_one(
+                "SELECT rule_type, interval, weekday_set, start_date "
+                "FROM schedule_rules WHERE id = ?",
+                (rule_id,),
+            )
+        )
+        assert row == ("weekly", 1, "0,2,4", "2026-09-13")
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_interval_default_applies(tmp_path) -> None:
+    database = _open_db(tmp_path / "interval-default.db")
+    try:
+        _apply(database)
+        rule_id = _insert_rule(database, interval=_OMIT)
+        row = _run(
+            database.fetch_one(
+                "SELECT interval FROM schedule_rules WHERE id = ?", (rule_id,)
+            )
+        )
+        assert row == (1,)
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_rule_type_check_enforced(tmp_path) -> None:
+    database = _open_db(tmp_path / "rule-type.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(database, rule_type="biweekly", weekday_set=None)
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_interval_zero_fails(tmp_path) -> None:
+    database = _open_db(tmp_path / "interval-zero.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(database, interval=0)
+    finally:
+        _run(database.close())
+
+
+@pytest.mark.parametrize("bad_interval", ["abc", 1.5])
+def test_schedule_rules_interval_non_integer_fails(
+    tmp_path, bad_interval
+) -> None:
+    database = _open_db(tmp_path / f"interval-{type(bad_interval).__name__}.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(database, interval=bad_interval)
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_interval_integer_one_succeeds(tmp_path) -> None:
+    database = _open_db(tmp_path / "interval-one.db")
+    try:
+        _apply(database)
+        rule_id = _insert_rule(database, interval=1)
+        row = _run(
+            database.fetch_one(
+                "SELECT interval FROM schedule_rules WHERE id = ?", (rule_id,)
+            )
+        )
+        assert row == (1,)
+    finally:
+        _run(database.close())
+
+
+@pytest.mark.parametrize(
+    ("column", "bad_value"),
+    [
+        ("day_of_month", "abc"),
+        ("day_of_month", 1.5),
+        ("nth_weekday", "abc"),
+        ("nth_weekday", 1.5),
+        ("month", "abc"),
+        ("month", 1.5),
+    ],
+)
+def test_schedule_rules_integer_columns_reject_text_and_fraction(
+    tmp_path, column, bad_value
+) -> None:
+    database = _open_db(tmp_path / f"{column}-typeof.db")
+    try:
+        _apply(database)
+        overrides: dict[str, object] = {"rule_type": "monthly", "weekday_set": None}
+        overrides[column] = bad_value
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(database, **overrides)
+    finally:
+        _run(database.close())
+
+
+@pytest.mark.parametrize(
+    "bad_weekday_set",
+    ["", "x", "7", "0,x", "0,8", "0,", "0,1,2,3,4,5,6,0"],
+)
+def test_schedule_rules_weekday_set_invalid_csv_fails(
+    tmp_path, bad_weekday_set
+) -> None:
+    database = _open_db(tmp_path / "weekday-set-invalid.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(database, weekday_set=bad_weekday_set)
+    finally:
+        _run(database.close())
+
+
+@pytest.mark.parametrize("weekday_set", ["0", "6", "0,1,2,3,4,5,6"])
+def test_schedule_rules_weekday_set_valid_csv_succeeds(
+    tmp_path, weekday_set
+) -> None:
+    database = _open_db(tmp_path / "weekday-set-valid.db")
+    try:
+        _apply(database)
+        rule_id = _insert_rule(database, weekday_set=weekday_set)
+        row = _run(
+            database.fetch_one(
+                "SELECT weekday_set FROM schedule_rules WHERE id = ?", (rule_id,)
+            )
+        )
+        assert row == (weekday_set,)
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_day_of_month_out_of_range_fails(tmp_path) -> None:
+    database = _open_db(tmp_path / "day-of-month.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(
+                database,
+                rule_type="monthly",
+                weekday_set=None,
+                day_of_month=32,
+            )
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_nth_weekday_zero_fails(tmp_path) -> None:
+    database = _open_db(tmp_path / "nth-weekday.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(
+                database,
+                rule_type="monthly",
+                weekday_set=None,
+                day_of_month=None,
+                nth_weekday=0,
+            )
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_nth_weekday_minus_one_last_week_allowed(tmp_path) -> None:
+    database = _open_db(tmp_path / "nth-weekday-last.db")
+    try:
+        _apply(database)
+        rule_id = _insert_rule(
+            database,
+            rule_type="monthly",
+            weekday_set=None,
+            day_of_month=None,
+            nth_weekday=-1,
+        )
+        row = _run(
+            database.fetch_one(
+                "SELECT nth_weekday FROM schedule_rules WHERE id = ?", (rule_id,)
+            )
+        )
+        assert row == (-1,)
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_month_out_of_range_fails(tmp_path) -> None:
+    database = _open_db(tmp_path / "month.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(
+                database, rule_type="yearly", weekday_set=None, month=13
+            )
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_weekly_without_weekday_set_fails(tmp_path) -> None:
+    database = _open_db(tmp_path / "weekly-no-weekdays.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(database, rule_type="weekly", weekday_set=None)
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_monthly_without_day_or_nth_fails(tmp_path) -> None:
+    database = _open_db(tmp_path / "monthly-no-fields.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(
+                database,
+                rule_type="monthly",
+                weekday_set=None,
+                day_of_month=None,
+                nth_weekday=None,
+            )
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_yearly_without_month_fails(tmp_path) -> None:
+    database = _open_db(tmp_path / "yearly-no-month.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(
+                database,
+                rule_type="yearly",
+                weekday_set=None,
+                day_of_month=None,
+                month=None,
+            )
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_daily_with_month_fields_fails(tmp_path) -> None:
+    database = _open_db(tmp_path / "daily-month-fields.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(database, rule_type="daily", month=3)
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_end_date_before_start_date_fails(tmp_path) -> None:
+    database = _open_db(tmp_path / "end-before-start.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_rule(database, end_date="2026-09-12")
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_end_date_equal_to_start_date_allowed(tmp_path) -> None:
+    database = _open_db(tmp_path / "end-equals-start.db")
+    try:
+        _apply(database)
+        rule_id = _insert_rule(database, end_date="2026-09-13")
+        row = _run(
+            database.fetch_one(
+                "SELECT end_date FROM schedule_rules WHERE id = ?", (rule_id,)
+            )
+        )
+        assert row == ("2026-09-13",)
+    finally:
+        _run(database.close())
+
+
+def test_schedule_rules_start_date_not_null_enforced(tmp_path) -> None:
+    database = _open_db(tmp_path / "start-date-null.db")
+    try:
+        _apply(database)
+        with pytest.raises(sqlite3.IntegrityError, match="NOT NULL"):
+            _insert_rule(database, start_date=None)
+    finally:
+        _run(database.close())
+
+
+def test_task_definitions_insert_requires_existing_child_and_rule(
+    tmp_path,
+) -> None:
+    database = _open_db(tmp_path / "definitions-fk-unknown.db")
+    try:
+        _apply(database)
+        _run(
+            database.execute(
+                "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
+                ("Ada", "2026-09-13T00:00:00+00:00"),
+            )
+        )
+        rule_id = _insert_rule(database)
+        definition_id = _insert_definition(
+            database, child_id=1, rule_id=rule_id
+        )
+        row = _run(
+            database.fetch_one(
+                "SELECT title, child_id, schedule_rule_id FROM task_definitions "
+                "WHERE id = ?",
+                (definition_id,),
+            )
+        )
+        assert row == ("Brush teeth", 1, rule_id)
+    finally:
+        _run(database.close())
+
+
+def test_task_definitions_unknown_child_id_fails(tmp_path) -> None:
+    database = _open_db(tmp_path / "definitions-bad-child.db")
+    try:
+        _apply(database)
+        _insert_rule(database)
+        with pytest.raises(sqlite3.IntegrityError, match="FOREIGN KEY"):
+            _insert_definition(database, child_id=999)
+    finally:
+        _run(database.close())
+
+
+def test_task_definitions_unknown_schedule_rule_id_fails(tmp_path) -> None:
+    database = _open_db(tmp_path / "definitions-bad-rule.db")
+    try:
+        _apply(database)
+        _run(
+            database.execute(
+                "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
+                ("Ada", "2026-09-13T00:00:00+00:00"),
+            )
+        )
+        with pytest.raises(sqlite3.IntegrityError, match="FOREIGN KEY"):
+            _insert_definition(database, child_id=1, rule_id=999)
+    finally:
+        _run(database.close())
+
+
+def test_task_definitions_is_active_check_enforced(tmp_path) -> None:
+    database = _open_db(tmp_path / "definitions-is-active.db")
+    try:
+        _apply(database)
+        _insert_rule(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_definition(database, child_id=1, rule_id=1, is_active=2)
+    finally:
+        _run(database.close())
+
+
+@pytest.mark.parametrize("bad_is_active", ["abc", 1.5])
+def test_task_definitions_is_active_non_integer_fails(
+    tmp_path, bad_is_active
+) -> None:
+    database = _open_db(tmp_path / "definitions-is-active-typeof.db")
+    try:
+        _apply(database)
+        _run(
+            database.execute(
+                "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
+                ("Ada", "2026-09-13T00:00:00+00:00"),
+            )
+        )
+        _insert_rule(database)
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _insert_definition(
+                database, child_id=1, rule_id=1, is_active=bad_is_active
+            )
+    finally:
+        _run(database.close())
+
+
+def test_task_definitions_title_not_null_enforced(tmp_path) -> None:
+    database = _open_db(tmp_path / "definitions-title.db")
+    try:
+        _apply(database)
+        _insert_rule(database)
+        with pytest.raises(sqlite3.IntegrityError, match="NOT NULL"):
+            _insert_definition(database, child_id=1, rule_id=1, title=None)
+    finally:
+        _run(database.close())
+
+
+def test_task_definitions_created_at_not_null_enforced(tmp_path) -> None:
+    database = _open_db(tmp_path / "definitions-created-at.db")
+    try:
+        _apply(database)
+        _insert_rule(database)
+        with pytest.raises(sqlite3.IntegrityError, match="NOT NULL"):
+            _insert_definition(
+                database, child_id=1, rule_id=1, created_at=None
+            )
+    finally:
+        _run(database.close())
+
+
+def test_task_definitions_defaults_apply_on_insert(tmp_path) -> None:
+    database = _open_db(tmp_path / "definitions-defaults.db")
+    try:
+        _apply(database)
+        _run(
+            database.execute(
+                "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
+                ("Ada", "2026-09-13T00:00:00+00:00"),
+            )
+        )
+        _insert_rule(database)
+        definition_id = _insert_definition(
+            database, child_id=1, rule_id=1, is_active=_OMIT
+        )
+        row = _run(
+            database.fetch_one(
+                "SELECT is_active, due_time FROM task_definitions WHERE id = ?",
+                (definition_id,),
+            )
+        )
+        assert row == (1, None)
+    finally:
+        _run(database.close())
+
+
+# ---------------------------------------------------------------------------
+# Idempotence across the extended v1 list
+# ---------------------------------------------------------------------------
+
+
+def test_applying_extended_ddl_twice_keeps_new_table_rows(tmp_path) -> None:
+    database = _open_db(tmp_path / "idempotent-extended.db")
+    try:
+        _apply(database)
+        _run(
+            database.execute(
+                "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
+                ("Ada", "2026-09-13T00:00:00+00:00"),
+            )
+        )
+        _insert_rule(database)
+        _insert_definition(database, child_id=1, rule_id=1)
+        _apply(database)
+        counts = []
+        for table in ("children", "schedule_rules", "task_definitions"):
+            row = _run(database.fetch_one(f"SELECT COUNT(*) FROM {table}"))
+            counts.append(row[0])
+        assert counts == [1, 1, 1]
+    finally:
+        _run(database.close())

tests/test_store.py

@@ -4,17 +4,8 @@ from __future__ import annotations
 import ast
 import asyncio
 from pathlib import Path
-import sys
 from unittest.mock import AsyncMock, MagicMock
 
-# Mock homeassistant modules if not installed in environment
-for _mod in (
-    "homeassistant",
-    "homeassistant.core",
-    "homeassistant.config_entries",
-):
-    sys.modules.setdefault(_mod, MagicMock())
-
 from custom_components.nestquest import store
 from custom_components.nestquest.const import SQLITE_DB_FILENAME
 
@@ -109,11 +100,14 @@ def test_executor_helper_creates_directory(tmp_path) -> None:
     assert nested.is_dir()
 
 
-def test_no_sqlite3_import_in_package() -> None:
+def test_no_sqlite3_import_outside_db_module() -> None:
+    """sqlite3 is banned package-wide except db.py, the connection wrapper."""
     pkg_dir = Path(store.__file__).parent
     py_files = list(pkg_dir.glob("*.py"))
     assert py_files
     for py_file in py_files:
+        if py_file.name == "db.py":
+            continue
         tree = ast.parse(py_file.read_text(encoding="utf-8"), filename=str(py_file))
         for node in ast.walk(tree):
             if isinstance(node, ast.Import):

uv.lock

@@ -0,0 +1,116 @@
+version = 1
+revision = 3
+requires-python = ">=3.11"
+
+[[package]]
+name = "colorama"
+version = "0.4.6"
+source = { registry = "https://pypi.org/simple" }
+sdist = { url = "https://files.pythonhosted.org/packages/d8/53/6f443c9a4a8358a93a6792e2acffb9d9d5cb0a5cfd8802644b7b1c9a02e4/colorama-0.4.6.tar.gz", hash = "sha256:08695f5cb7ed6e0531a20572697297273c47b8cae5a63ffc6d6ed5c201be6e44", size = 27697, upload-time = "2022-10-25T02:36:22.414Z" }
+wheels = [
+    { url = "https://files.pythonhosted.org/packages/d1/d6/3965ed04c63042e047cb6a3e6ed1a63a35087b6a609aa3a15ed8ac56c221/colorama-0.4.6-py2.py3-none-any.whl", hash = "sha256:4f1d9991f5acc0ca119f9d443620b77f9d6b33703e51011c16baf57afb285fc6", size = 25335, upload-time = "2022-10-25T02:36:20.889Z" },
+]
+
+[[package]]
+name = "iniconfig"
+version = "2.3.0"
+source = { registry = "https://pypi.org/simple" }
+sdist = { url = "https://files.pythonhosted.org/packages/72/34/14ca021ce8e5dfedc35312d08ba8bf51fdd999c576889fc2c24cb97f4f10/iniconfig-2.3.0.tar.gz", hash = "sha256:c76315c77db068650d49c5b56314774a7804df16fee4402c1f19d6d15d8c4730", size = 20503, upload-time = "2025-10-18T21:55:43.219Z" }
+wheels = [
+    { url = "https://files.pythonhosted.org/packages/cb/b1/3846dd7f199d53cb17f49cba7e651e9ce294d8497c8c150530ed11865bb8/iniconfig-2.3.0-py3-none-any.whl", hash = "sha256:f631c04d2c48c52b84d0d0549c99ff3859c98df65b3101406327ecc7d53fbf12", size = 7484, upload-time = "2025-10-18T21:55:41.639Z" },
+]
+
+[[package]]
+name = "nestquest"
+version = "0.1.0"
+source = { virtual = "." }
+
+[package.dev-dependencies]
+dev = [
+    { name = "pytest" },
+    { name = "pytest-asyncio" },
+    { name = "voluptuous" },
+]
+
+[package.metadata]
+
+[package.metadata.requires-dev]
+dev = [
+    { name = "pytest", specifier = ">=8.0.0" },
+    { name = "pytest-asyncio", specifier = ">=0.24" },
+    { name = "voluptuous", specifier = ">=0.14" },
+]
+
+[[package]]
+name = "packaging"
+version = "26.3"
+source = { registry = "https://pypi.org/simple" }
+sdist = { url = "https://files.pythonhosted.org/packages/7d/fa/3944b40b07da9ce895c0e6303a5ab7d53da063554f534556b134a54d6093/packaging-26.3.tar.gz", hash = "sha256:94edc256424af38762eb31306eed28beb9f0efc50a8837492c9d6fd6004aed79", size = 313412, upload-time = "2026-08-04T18:15:28.737Z" }
+wheels = [
+    { url = "https://files.pythonhosted.org/packages/63/34/ba1c580383c9eada3711951fef0795c80b829a078d72188184bcab9dd527/packaging-26.3-py3-none-any.whl", hash = "sha256:d7193f7c8e4e93f444fde0262bf90af30e16fa0ad0ad44cb553c87339b23cd1c", size = 129956, upload-time = "2026-08-04T18:15:27.159Z" },
+]
+
+[[package]]
+name = "pluggy"
+version = "1.6.0"
+source = { registry = "https://pypi.org/simple" }
+sdist = { url = "https://files.pythonhosted.org/packages/f9/e2/3e91f31a7d2b083fe6ef3fa267035b518369d9511ffab804f839851d2779/pluggy-1.6.0.tar.gz", hash = "sha256:7dcc130b76258d33b90f61b658791dede3486c3e6bfb003ee5c9bfb396dd22f3", size = 69412, upload-time = "2025-05-15T12:30:07.975Z" }
+wheels = [
+    { url = "https://files.pythonhosted.org/packages/54/20/4d324d65cc6d9205fabedc306948156824eb9f0ee1633355a8f7ec5c66bf/pluggy-1.6.0-py3-none-any.whl", hash = "sha256:e920276dd6813095e9377c0bc5566d94c932c33b27a3e3945d8389c374dd4746", size = 20538, upload-time = "2025-05-15T12:30:06.134Z" },
+]
+
+[[package]]
+name = "pygments"
+version = "2.21.0"
+source = { registry = "https://pypi.org/simple" }
+sdist = { url = "https://files.pythonhosted.org/packages/49/2e/ced460408999b33da6b31b0021b0f37d329e202d4169aeb164493778f25b/pygments-2.21.0.tar.gz", hash = "sha256:610ca751c9bc2492b38eb9a38a7fbc93edbbb2d7182edaf34e66ae493dee5c8c", size = 5005329, upload-time = "2026-08-17T08:02:48.824Z" }
+wheels = [
+    { url = "https://files.pythonhosted.org/packages/71/46/17f022dd3e953bf20a04a028a21ec746d942f8d2af30fa0f124fa0e6a684/pygments-2.21.0-py3-none-any.whl", hash = "sha256:2363c69b61c4a97c838da3b130dcd6468f4848992b21a82f2a63ec34377137d9", size = 1250147, upload-time = "2026-08-17T08:02:44.912Z" },
+]
+
+[[package]]
+name = "pytest"
+version = "9.1.1"
+source = { registry = "https://pypi.org/simple" }
+dependencies = [
+    { name = "colorama", marker = "sys_platform == 'win32'" },
+    { name = "iniconfig" },
+    { name = "packaging" },
+    { name = "pluggy" },
+    { name = "pygments" },
+]
+sdist = { url = "https://files.pythonhosted.org/packages/e4/47/b9efed96c114afcfa3c9d3fe98a76a1d14c74a9e266d397cf6eb64be5e01/pytest-9.1.1.tar.gz", hash = "sha256:1088fbde8f2b49d95a549a195707afa7a76a3ce9bcadc26b6d71f0ffda5fe313", size = 1636369, upload-time = "2026-06-19T10:58:32.857Z" }
+wheels = [
+    { url = "https://files.pythonhosted.org/packages/24/25/1de2678b631f5a49215c6c96fff41ba892b0a34df68d6d80292b1b48aa7f/pytest-9.1.1-py3-none-any.whl", hash = "sha256:37a86b45efb9a47a61a36449063e8e18d0cab3161329fc099eb21783169c4f0c", size = 386536, upload-time = "2026-06-19T10:58:31.347Z" },
+]
+
+[[package]]
+name = "pytest-asyncio"
+version = "1.4.0"
+source = { registry = "https://pypi.org/simple" }
+dependencies = [
+    { name = "pytest" },
+    { name = "typing-extensions", marker = "python_full_version < '3.13'" },
+]
+sdist = { url = "https://files.pythonhosted.org/packages/43/7c/d36d04db312ecf4298932ef77e6e4a9e8ad017906e24e34f0b0c361a2473/pytest_asyncio-1.4.0.tar.gz", hash = "sha256:c6c0d2259945122819f171a32ecea2c349ead889ee28176caaf492143424be42", size = 58514, upload-time = "2026-05-26T09:56:04.083Z" }
+wheels = [
+    { url = "https://files.pythonhosted.org/packages/03/e2/08a497ef684b88559c9cc5f4ad53a37e7b99e727094a86d6ea32536d5d3c/pytest_asyncio-1.4.0-py3-none-any.whl", hash = "sha256:933ca923a23075a87fb7070c0ec272a6848489824d887c85c812670932835aa1", size = 16930, upload-time = "2026-05-26T09:56:02.576Z" },
+]
+
+[[package]]
+name = "typing-extensions"
+version = "4.16.0"
+source = { registry = "https://pypi.org/simple" }
+sdist = { url = "https://files.pythonhosted.org/packages/f6/cc/6253133b5bb138fc3306cebfbda2c520f545d36b5be2c7255cc528bb45d6/typing_extensions-4.16.0.tar.gz", hash = "sha256:dc983d19a509c94dba722ee6abd33940f7c05a89e243c47e907eb4db6f1a43e5", size = 113555, upload-time = "2026-07-02T08:40:05.92Z" }
+wheels = [
+    { url = "https://files.pythonhosted.org/packages/49/d3/b8441a820a491ddfc024b0b0cf0393375b75ea13866d9c66727e54c2fc80/typing_extensions-4.16.0-py3-none-any.whl", hash = "sha256:481caa481374e813c1b176ada14e97f1f67a4539ce9cfeb3f350d78d6370c2e8", size = 45571, upload-time = "2026-07-02T08:40:04.659Z" },
+]
+
+[[package]]
+name = "voluptuous"
+version = "0.16.0"
+source = { registry = "https://pypi.org/simple" }
+sdist = { url = "https://files.pythonhosted.org/packages/92/f4/0738e6849858deae22218be3bbb8207ba83a96e9d0ec7e8e8cd67b30e5ca/voluptuous-0.16.0.tar.gz", hash = "sha256:006535e22fed944aec17bef6e8725472476194743c87bd233e912eb463f8ff05", size = 54238, upload-time = "2025-12-18T23:18:46.08Z" }
+wheels = [
+    { url = "https://files.pythonhosted.org/packages/d1/00/0e0da784245c93cf346150ab67634177bf277f93b7a162bb56c928c39c04/voluptuous-0.16.0-py3-none-any.whl", hash = "sha256:ee342095263e1b5afbd4d418cb5adc92810eebfd07696bb033a261210df33db4", size = 31931, upload-time = "2025-12-18T23:18:44.694Z" },
+]