fix: serialize concurrent migration runners with a per-connection lock

NestQuestDatabase rejects a second transaction while one is active, so
two concurrent apply_migrations callers on one connection crashed with
RuntimeError instead of queueing. apply_migrations now holds a
per-(connection, loop) asyncio lock across the whole decide-and-apply
span; the queued runner re-reads the stamped version and applies
nothing. Deterministic concurrent-runner regression test added.

LICENSE

@@ -0,0 +1,21 @@
+MIT License
+
+Copyright (c) 2026 NestQuest Contributors / Maniacs Mansion
+
+Permission is hereby granted, free of charge, to any person obtaining a copy
+of this software and associated documentation files (the "Software"), to deal
+in the Software without restriction, including without limitation the rights
+to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
+copies of the Software, and to permit persons to whom the Software is
+furnished to do so, subject to the following conditions:
+
+The above copyright notice and this permission notice shall be included in all
+copies or substantial portions of the Software.
+
+THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
+AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
+LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
+OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
+SOFTWARE.

README.md

@@ -0,0 +1,68 @@
+# NestQuest - House Chaos Coordination
+
+Home Assistant custom integration for managing chores, custody schedules, and house coordination.
+
+## Overview
+
+NestQuest helps households coordinate daily life, chore tracking, custody schedules, and shared tasks directly within Home Assistant. It provides sensors, calendar events, and service calls designed for busy households and modern co-parenting setups.
+
+## Installation
+
+### HACS Installation (Recommended)
+
+1. Ensure [HACS (Home Assistant Community Store)](https://hacs.xyz/) is installed.
+2. In Home Assistant, open **HACS** > **Integrations**.
+3. Select the three-dots menu in the top right and click **Custom repositories**.
+4. Enter the repository URL: `https://github.com/talon2king/NestQuest`
+5. Select category **Integration** and click **Add**.
+6. Search for **NestQuest** and click **Download**.
+7. Restart Home Assistant.
+
+> **Note:** Development happens in the private Gitea repository
+> (`https://code.cubecraftlabs.com/Maniacs_Mansion/NestQuest`). The GitHub
+> repository is a public mirror maintained for HACS distribution.
+
+### Manual Installation
+
+1. Download the latest release from the repository.
+2. Copy the `custom_components/nestquest` directory into your Home Assistant installation's `config/custom_components/` folder:
+   ```bash
+   cp -R custom_components/nestquest <config_dir>/custom_components/
+   ```
+3. Restart Home Assistant.
+
+## Configuration
+
+After installation and restart:
+1. Navigate to **Settings** > **Devices & Services** in Home Assistant.
+2. Click **Add Integration** and search for **NestQuest**.
+3. Follow the on-screen configuration flow.
+
+## Developer Setup and Testing
+
+This project uses [`uv`](https://github.com/astral-sh/uv) for fast Python package and dependency management.
+
+### Prerequisites
+
+- Python 3.11 or newer
+- [uv](https://docs.astral.sh/uv/) installed on your machine
+
+### Setup Environment
+
+To set up the development environment and install dependencies:
+
+```bash
+uv sync
+```
+
+### Running Tests
+
+Run the test suite with `pytest`:
+
+```bash
+uv run pytest
+```
+
+## License
+
+This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.

custom_components/nestquest/__init__.py

@@ -1,16 +1,32 @@
 """The NestQuest integration."""
 from __future__ import annotations
 
+import inspect
+from collections.abc import Callable
+from dataclasses import dataclass
 from typing import Any
 
 from homeassistant.config_entries import ConfigEntry
 from homeassistant.core import HomeAssistant
 
 from .const import DOMAIN, LOGGER
+from .db import NestQuestDatabase
+from .migrations import apply_migrations
+from .store import async_get_db_path
 
 _LOGGER = LOGGER
 
 
+@dataclass
+class NestQuestRuntimeData:
+    """Runtime data stored on a NestQuest config entry."""
+
+    entry_id: str
+    options: dict[str, Any]
+    database: NestQuestDatabase
+    remove_update_listener: Callable[[], Any]
+
+
 async def async_setup(hass: HomeAssistant, config: dict[str, Any]) -> bool:
     """Set up the NestQuest integration. YAML configuration is not used, returns True."""
     return True
@@ -19,9 +35,65 @@ 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
+    ) -> None:
+        """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 apply_migrations(database)
+        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),
+        database=database,
+        remove_update_listener=remove_update_listener,
+    )
+    entry.runtime_data = runtime_data
+    hass.data[DOMAIN][entry.entry_id] = runtime_data
     return True
 
 
 async def async_unload_entry(hass: HomeAssistant, entry: ConfigEntry) -> bool:
     """Unload a config entry."""
+    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)
+        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

@@ -0,0 +1,39 @@
+"""Config flow for NestQuest."""
+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
+
+
+class NestQuestConfigFlow(config_entries.ConfigFlow, domain=DOMAIN):
+    """Handle a config flow for NestQuest."""
+
+    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:
+        """Handle the user step of the config flow."""
+        await self.async_set_unique_id(DOMAIN, raise_on_progress=False)
+        if self._async_in_progress() or self._async_current_entries():
+            return self.async_abort(reason="single_instance_allowed")
+
+        if user_input is None:
+            return self.async_show_form(step_id="user")
+
+        return self.async_create_entry(title="NestQuest", data=user_input)

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/migrations.py

@@ -0,0 +1,236 @@
+"""Versioned schema migration runner for the NestQuest SQLite database.
+
+The runner owns one metadata table, ``nestquest_schema_version``, holding
+a single row with the schema version the database file currently
+reflects.  Migrations are declared as an ordered list: entry ``n``
+upgrades version ``n-1`` to version ``n``.  Applying a migration means
+running its DDL inside one transaction and stamping the new version in
+that same transaction, so a database never persists a half-applied
+migration: any failure rolls the statements and the version stamp back
+together, leaving the file at its prior version.
+
+Version 0 means "the file exists but carries no NestQuest schema yet"
+— a fresh file, or a file this integration has never written.  Starting
+the runner against version 0 applies migration 1, which applies the
+full v1 DDL from :mod:`.schema` and stamps version 1.
+
+Each migration's statements and its version stamp run inside ONE
+transaction, and the stamp is written as DELETE + INSERT rather than
+UPDATE because a version-0 database has no version row to update yet.
+An UPDATE form would silently match zero rows there and leave the
+database migrated but unstamped — reporting version 0 forever, and
+re-applying migration 1 on every startup.  The DELETE+INSERT shape is
+also what makes the stamp a replace, so the row never duplicates.
+
+Idempotence across restarts: a database already at the latest version
+has no pending migrations, so the runner opens a read-only check, finds
+nothing to do, and leaves the file byte-untouched (no write transaction
+is opened, so no WAL churn).
+
+Migration list policy: entries are append-only.  Once shipped, a
+migration's SQL must never be edited — an old database applying it for
+the first time and a fresh database would then diverge.  Fix-forward
+with a new migration instead.  The version stamp lives in the same
+SQLite file as the data it describes, so restoring a backup restores
+its matching schema version with it.
+
+Corrupt or missing files are not this module's concern: startup
+handling of those cases is task d5bca74b, which decides whether to
+open fresh or raise ConfigEntryNotReady.  This runner assumes it is
+handed an open, working connection.
+"""
+from __future__ import annotations
+
+import asyncio
+import logging
+from collections.abc import Sequence
+
+from .db import NestQuestDatabase
+from .schema import SCHEMA_V1_STATEMENTS
+
+LOGGER = logging.getLogger(__name__)
+
+#: Metadata table holding the applied schema version.  Prefixed with the
+#: integration domain so a config directory shared with other tools can
+#: never collide with a foreign ``schema_version`` table.
+VERSION_TABLE = "nestquest_schema_version"
+
+#: DDL for the version metadata table.  The singleton-row invariant is
+#: enforced by the schema itself: ``id`` is fixed at 1 by CHECK, keyed
+#: PRIMARY KEY, so the table can never hold a second row, and the
+#: stamp write is an UPDATE (no DELETE+INSERT needed).  ``version`` is
+#: a positive integer: version 0 means "no row" and is never stored,
+#: so a negative or zero stored value is corruption, not a state the
+#: runner can act on.
+VERSION_TABLE_DDL = f"""
+    CREATE TABLE IF NOT EXISTS {VERSION_TABLE} (
+        id INTEGER NOT NULL PRIMARY KEY CHECK (id = 1),
+        version INTEGER NOT NULL CHECK (version >= 1)
+    )
+"""
+
+#: One-based index into :data:`MIGRATIONS`: entry ``n`` upgrades
+#: version ``n-1`` to version ``n``.
+MIGRATION_1_V1_DDL: list[str] = list(SCHEMA_V1_STATEMENTS)
+
+#: The ordered migration list.  Append-only: never edit an applied
+#: entry, add the next one instead.
+MIGRATIONS: Sequence[Sequence[str]] = [
+    MIGRATION_1_V1_DDL,
+]
+
+
+async def _create_version_table(database: NestQuestDatabase) -> None:
+    """Create the version metadata table if it does not exist.
+
+    The table is created before any version check: it is the runner's
+    own bookkeeping, and creating it is idempotent.  The DDL enforces
+    the singleton-row invariant (``id`` fixed at 1) and a positive
+    integer version at the database level.
+    """
+    await database.execute(VERSION_TABLE_DDL)
+
+
+async def read_schema_version(database: NestQuestDatabase) -> int:
+    """Return the schema version recorded in the database.
+
+    Returns 0 when the version table or its row is missing — the
+    "fresh file" case.  A stored version higher than the runner knows
+    is surfaced as-is; the caller decides how to treat a future version.
+
+    Reading is deliberately passive: a missing table raises, it is not
+    swallowed into 0, because :func:`apply_migrations` always creates
+    the table before reading and a direct reader hitting "no such
+    table" is a caller bug worth surfacing, not a fresh file.
+    """
+    row = await database.fetch_one(
+        f"SELECT version FROM {VERSION_TABLE} LIMIT 1"
+    )
+    if row is None:
+        return 0
+    version = row[0]
+    if not isinstance(version, int) or isinstance(version, bool):
+        raise RuntimeError(
+            f"Schema version metadata is not an integer: {version!r}"
+        )
+    if version < 1:
+        raise RuntimeError(
+            f"Schema version metadata is invalid (must be >= 1): {version!r}"
+        )
+    return version
+
+
+#: One asyncio.Lock per connection wrapper, created lazily and bound to
+#: the running loop, serializing whole apply_migrations runs so two
+#: concurrent callers on one connection queue instead of colliding on
+#: the wrapper's single-transaction rule.
+_MIGRATION_LOCKS: dict[int, asyncio.Lock] = {}
+
+
+def _migration_lock(database: NestQuestDatabase) -> asyncio.Lock:
+    """Return the migration lock bound to ``database``'s running loop."""
+    loop = asyncio.get_running_loop()
+    key = (id(database), id(loop))
+    lock = _MIGRATION_LOCKS.get(key)
+    if lock is None:
+        lock = asyncio.Lock()
+        _MIGRATION_LOCKS[key] = lock
+    return lock
+
+
+async def apply_migrations(
+    database: NestQuestDatabase,
+    migrations: Sequence[Sequence[str]] = MIGRATIONS,
+) -> int:
+    """Bring the database schema up to the latest version.
+
+    Reads the current version, applies every pending migration in order
+    — each inside one transaction that also stamps the new version —
+    and returns the resulting version.  Applying zero pending
+    migrations (database already current) is a no-op that logs at debug
+    level and opens no write transaction.
+
+    The version is re-read inside each migration transaction before
+    deciding to apply it: the outer read is advisory only (loop bounds,
+    no-op fast path), and on one shared connection two interleaved
+    runners would otherwise both read the same version and both apply
+    a non-idempotent migration.  Under the transaction's lock the
+    second runner sees the first runner's stamp and skips.
+
+    Runners on one connection are serialized by an asyncio lock held
+    for the whole decide-and-apply span: NestQuestDatabase rejects a
+    second transaction while one is active (RuntimeError), so
+    concurrent callers must queue, and the lock makes that queueing
+    explicit.  The loser of the race re-reads the version once it
+    acquires the lock, sees the winner's stamp, and applies nothing.
+
+    Raises whatever the failing migration raised, with the database
+    rolled back to its prior version and prior rows untouched.
+    """
+    async with _migration_lock(database):
+        await _create_version_table(database)
+        current = await read_schema_version(database)
+        latest = len(migrations)
+
+        if current > latest:
+            raise RuntimeError(
+                f"Database schema version {current} is newer than this "
+                f"integration understands (latest known: {latest}); "
+                "downgrades are not supported"
+            )
+
+        if current == latest:
+            LOGGER.debug(
+                "NestQuest schema already at version %d; "
+                "no migrations to apply",
+                current,
+            )
+            return current
+
+        for target in range(current + 1, latest + 1):
+            applied = False
+            async with database.transaction():
+                # Re-read the version INSIDE the write transaction: the
+                # outer read was advisory, and the wrapper serializes
+                # individual statements, not the read-decide-apply
+                # decision.  Under the transaction's lock the version
+                # cannot change between the read and the stamp, and the
+                # loser of an interleaved race (a queued second caller
+                # re-running apply_migrations after the winner already
+                # migrated) re-reads here, sees the winner's stamp, and
+                # skips instead of re-applying a non-idempotent
+                # migration with a stale version.
+                in_tx_version = await read_schema_version(database)
+                if in_tx_version > latest:
+                    raise RuntimeError(
+                        f"Database schema version {in_tx_version} is newer "
+                        f"than this integration understands (latest known: "
+                        f"{latest}); downgrades are not supported"
+                    )
+                if in_tx_version < target:
+                    for sql in migrations[target - 1]:
+                        await database.execute(sql)
+                    # INSERT keyed on the singleton row (id = 1): a
+                    # fresh file has no row yet, and migration 1 is the
+                    # only migration a version-0 database can run, so
+                    # the row is seeded by the same statement batch
+                    # when needed.  UPDATE alone would silently match
+                    # zero rows there.
+                    await database.execute(
+                        f"INSERT INTO {VERSION_TABLE} (id, version) "
+                        "VALUES (1, ?) "
+                        "ON CONFLICT (id) DO UPDATE SET version = "
+                        "excluded.version",
+                        (target,),
+                    )
+                    applied = True
+            if applied:
+                LOGGER.info(
+                    "NestQuest schema migrated to version %d "
+                    "(%d statement%s)",
+                    target,
+                    len(migrations[target - 1]),
+                    "s" if len(migrations[target - 1]) != 1 else "",
+                )
+
+        return latest

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,336 @@
+"""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.
+
+Presence pattern format: ``presence_schedules.pattern`` encodes the
+whole N-week repeating cycle in one TEXT column as pipe-separated
+segments, one per week of the cycle: segment ``i`` is a ``weekday_set``
+CSV (same single-digit 0-6 convention as ``schedule_rules.weekday_set``)
+for week ``i``, e.g. ``'0,2,4|1,3'`` = Mon/Wed/Fri in week 0, Tue/Thu in
+week 1.  An empty segment means the child is absent every day of that
+week; a wholly empty pattern with ``cycle_length_weeks = 1`` means
+absent every day of every week, which is distinct from having no
+schedule row at all (a child with no schedule is present every day, per
+the feature guardrails).  Chosen over JSON or per-week rows to stay
+consistent with the CSV ``weekday_set`` convention and fully checkable
+by plain CHECKs.  DB-level validation is pure CHECK, no triggers or
+custom functions: char-class GLOBs reject every character outside
+digits 0-6, comma and pipe; adjacency GLOBs reject empty elements
+(double comma, comma next to a pipe, leading/trailing comma) and
+two-digit runs; a further GLOB rejects contiguous runs of 8+ comma
+separated digits, which caps a segment at 7 elements — the GLOB cannot
+match across a ``|``, so cross-segment boundaries stay safe; and the
+segment count is pinned to ``cycle_length_weeks`` by
+``LENGTH(pattern) - LENGTH(REPLACE(pattern, '|', '')) + 1``.  Per
+weekday_set this is not full CSV-set normalisation (duplicates like
+``'0,0'`` or unsorted sets stay storable, as with ``schedule_rules``);
+the engine treats the segment as a set.  A
+one-row-per-``week_index`` normalization was considered and rejected:
+the flat single-column encoding keeps the done-condition's table shape
+and proved fully checkable with plain CHECKs.
+
+``cycle_length_weeks`` is capped at 4: 2 (alternating weeks) is the
+common case, and a small cap keeps anchor arithmetic and the encoded
+pattern human-readable.  Revisit the cap — and re-run the pattern
+validation matrix in tests — if a longer cycle is ever needed.
+``anchor_date`` is an ISO date string pinning the cycle: the week index
+of a date is (days since anchor) // 7 mod ``cycle_length_weeks``,
+computed by the presence engine (Feature 05) from anchor-date
+arithmetic, never ISO week parity.  Like other date columns it carries
+no shape CHECK, matching the established date policy.
+
+Presence overrides: ``presence_overrides.end_date`` is NOT NULL,
+unlike ``schedule_rules.end_date`` — an override is always a concrete
+date range; a single-day override stores the same date in both columns.
+``is_present`` 0/1 marks the child absent/present for the whole range;
+``note`` is optional free text.
+
+Task instances: ``task_instances`` deliberately carries NO completion
+status column.  An instance's current state derives from the latest row
+in ``completion_events`` (Feature 08), so materialization (Feature 07)
+can insert instances without knowing anything about completion and the
+append-only log stays the single source of truth.  ``definition_id``,
+``due_date`` and ``child_id`` snapshot the materialization-time facts
+("why this instance exists"): reassignment and rule edits change future
+instances only and never rewrite these rows.  ``due_time`` snapshots
+the definition's optional due time at generation for the same reason —
+the instance keeps its generation-time value even if the definition is
+edited later.  ``generated_at`` is a caller-set UTC ISO-8601 timestamp
+per the module timestamp policy (no DB default, so a generation batch
+stamps one coherent time).
+
+Uniqueness: the (definition_id, due_date) pair is declared as a
+table-level ``UNIQUE`` constraint rather than a separate ``CREATE
+UNIQUE INDEX`` statement.  SQLite materializes it as an implicit unique
+index (``sqlite_autoindex_task_instances_1``) — the required "unique
+index prevents duplicate instances" — and the constraint form cannot be
+silently dropped the way a standalone index can, so the duplicate door
+stays shut.  Idempotent materialization keys on exactly this pair.  No
+secondary indexes beyond it are declared yet: history and board queries
+(Features 08/10/13) should justify their indexes as a later migration
+once their query shapes exist.
+
+No ``ON DELETE`` clauses are declared on any foreign key in this
+module: children and definitions are deactivated, never deleted, so a
+deletion that would orphan instances or events is a bug and must fail
+loudly rather than cascade.
+
+Completion events: ``completion_events`` is strictly append-only.  That
+property is enforced at the DAO layer — the completion-events DAO
+exposes no UPDATE or DELETE path (task a9ea75fc) — and deliberately NOT
+by database triggers, matching the module's no-trigger policy: a RAISE
+trigger would be the one exception to "pure CHECK, no triggers",
+complicate the later migration runner, and still prove nothing (any
+writer can run raw SQL).  ``event_type`` is constrained to
+``completed``/``uncompleted``; a ``missed`` row is intentionally not
+representable — missed is a derived state announced on the HA event bus
+(Feature 10), never an appended event.
+
+``child_id`` is deliberately denormalized (it is also derivable via
+``task_instances``): history reporting (Feature 13) filters and rates
+per child directly against the log, and the log should stay
+self-describing without joins.
+
+Actor policy: ``actor_source`` distinguishes an authenticated HA user
+(``'user'``, with ``actor_user_id`` holding the HA user id) from a
+panel tap with no user context (``'panel'``, with ``actor_user_id``
+NULL — the tapped child profile is already ``child_id``).  A coherence
+CHECK makes the pair shape non-negotiable: ``'user'`` requires
+``actor_user_id``, ``'panel'`` forbids it.  Other actor sources are
+rejected; if an unattended system actor is ever needed, that is a
+schema migration, not a silent widening.
+
+On-time policy: ``was_on_time`` is required (integer 0 or 1) for
+``completed`` events — at completion time the due date is known and the
+flag is decidable.  For ``uncompleted`` events the flag is optional:
+Feature 08 decides whether a reversal carries the reversed completion's
+on-time flag or NULL, and the DDL deliberately accepts both rather than
+guessing.
+"""
+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_PRESENCE_SCHEDULES_DDL: list[str] = [
+    f"""
+    CREATE TABLE IF NOT EXISTS presence_schedules (
+        id INTEGER PRIMARY KEY AUTOINCREMENT,
+        child_id INTEGER NOT NULL UNIQUE REFERENCES children(id),
+        cycle_length_weeks INTEGER NOT NULL CHECK (typeof(cycle_length_weeks) = 'integer' AND cycle_length_weeks >= 1 AND cycle_length_weeks <= 4),
+        anchor_date TEXT NOT NULL,
+        pattern TEXT NOT NULL CHECK (
+            NOT pattern GLOB '*[^0-6,|]*'
+            AND NOT pattern GLOB '*[0-6][0-6]*'
+            AND NOT pattern GLOB '*,,*'
+            AND NOT pattern GLOB '*,|*'
+            AND NOT pattern GLOB '*|,*'
+            AND NOT pattern GLOB ',*'
+            AND NOT pattern GLOB '*,'
+            AND NOT pattern GLOB '*[0-6],[0-6],[0-6],[0-6],[0-6],[0-6],[0-6],[0-6]*'
+            AND (LENGTH(pattern) - LENGTH(REPLACE(pattern, '|', '')) + 1) = cycle_length_weeks
+        )
+    )
+    """,
+]
+
+SCHEMA_V1_PRESENCE_OVERRIDES_DDL: list[str] = [
+    """
+    CREATE TABLE IF NOT EXISTS presence_overrides (
+        id INTEGER PRIMARY KEY AUTOINCREMENT,
+        child_id INTEGER NOT NULL REFERENCES children(id),
+        start_date TEXT NOT NULL,
+        end_date TEXT NOT NULL,
+        is_present INTEGER NOT NULL CHECK (typeof(is_present) = 'integer' AND is_present IN (0, 1)),
+        note TEXT,
+        CHECK (end_date >= start_date)
+    )
+    """,
+]
+
+SCHEMA_V1_TASK_INSTANCES_DDL: list[str] = [
+    """
+    CREATE TABLE IF NOT EXISTS task_instances (
+        id INTEGER PRIMARY KEY AUTOINCREMENT,
+        definition_id INTEGER NOT NULL REFERENCES task_definitions(id),
+        child_id INTEGER NOT NULL REFERENCES children(id),
+        due_date TEXT NOT NULL,
+        due_time TEXT,
+        generated_at TEXT NOT NULL,
+        UNIQUE (definition_id, due_date)
+    )
+    """,
+]
+
+SCHEMA_V1_COMPLETION_EVENTS_DDL: list[str] = [
+    """
+    CREATE TABLE IF NOT EXISTS completion_events (
+        id INTEGER PRIMARY KEY AUTOINCREMENT,
+        instance_id INTEGER NOT NULL REFERENCES task_instances(id),
+        child_id INTEGER NOT NULL REFERENCES children(id),
+        event_type TEXT NOT NULL CHECK (event_type IN ('completed', 'uncompleted')),
+        actor_source TEXT NOT NULL CHECK (actor_source IN ('user', 'panel')),
+        actor_user_id TEXT,
+        occurred_at TEXT NOT NULL,
+        was_on_time INTEGER CHECK (
+            was_on_time IS NULL
+            OR (typeof(was_on_time) = 'integer' AND was_on_time IN (0, 1))
+        ),
+        CHECK (
+            (actor_source = 'user' AND actor_user_id IS NOT NULL)
+            OR (actor_source = 'panel' AND actor_user_id IS NULL)
+        ),
+        CHECK (
+            (event_type = 'completed' AND was_on_time IS NOT NULL)
+            OR (event_type = 'uncompleted')
+        )
+    )
+    """,
+]
+
+SCHEMA_V1_STATEMENTS: list[str] = [
+    *SCHEMA_V1_CHILDREN_DDL,
+    *SCHEMA_V1_ADMIN_USERS_DDL,
+    *SCHEMA_V1_SCHEDULE_RULES_DDL,
+    *SCHEMA_V1_TASK_DEFINITIONS_DDL,
+    *SCHEMA_V1_PRESENCE_SCHEDULES_DDL,
+    *SCHEMA_V1_PRESENCE_OVERRIDES_DDL,
+    *SCHEMA_V1_TASK_INSTANCES_DDL,
+    *SCHEMA_V1_COMPLETION_EVENTS_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/store.py

@@ -0,0 +1,20 @@
+"""SQLite storage path resolution for the NestQuest integration."""
+from __future__ import annotations
+
+from pathlib import Path
+
+from homeassistant.core import HomeAssistant
+
+from .const import SQLITE_DB_FILENAME
+
+
+def _ensure_dir(path: Path) -> None:
+    """Create the given directory and any missing parents."""
+    path.mkdir(parents=True, exist_ok=True)
+
+
+async def async_get_db_path(hass: HomeAssistant) -> Path:
+    """Return the absolute path to the NestQuest SQLite database file."""
+    db_path = Path(hass.config.path(SQLITE_DB_FILENAME)).absolute()
+    await hass.async_add_executor_job(_ensure_dir, db_path.parent)
+    return db_path

custom_components/nestquest/strings.json

@@ -0,0 +1,34 @@
+{
+  "title": "NestQuest",
+  "config": {
+    "step": {
+      "user": {
+        "title": "Add NestQuest",
+        "description": "Set up NestQuest to start coordinating the household. No additional configuration is required."
+      }
+    },
+    "abort": {
+      "single_instance_allowed": "NestQuest is already configured. Only a single instance is allowed."
+    },
+    "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

@@ -0,0 +1,34 @@
+{
+  "title": "NestQuest",
+  "config": {
+    "step": {
+      "user": {
+        "title": "Add NestQuest",
+        "description": "Set up NestQuest to start coordinating the household. No additional configuration is required."
+      }
+    },
+    "abort": {
+      "single_instance_allowed": "NestQuest is already configured. Only a single instance is allowed."
+    },
+    "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."
+    }
+  }
+}

hacs.json

@@ -0,0 +1,5 @@
+{
+  "name": "NestQuest",
+  "homeassistant": "2024.6.0",
+  "render_readme": true
+}

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

@@ -0,0 +1,157 @@
+"""Tests for the config flow of NestQuest."""
+from __future__ import annotations
+
+import ast
+import json
+from pathlib import Path
+
+import pytest
+
+from custom_components.nestquest import config_flow
+from custom_components.nestquest.const import DOMAIN
+
+PKG_DIR = Path(config_flow.__file__).parent
+STRINGS_PATH = PKG_DIR / "strings.json"
+TRANSLATIONS_EN_PATH = PKG_DIR / "translations" / "en.json"
+
+
+async def test_flow_shows_form_initially(hass, make_flow) -> None:
+    """The initial user step shows a form."""
+    flow = make_flow(hass)
+    result = await flow.async_step_user(None)
+    assert result["type"] == "form"
+    assert result["step_id"] == "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) == []
+
+
+async def test_second_flow_aborts_single_instance(
+    hass, make_flow, make_config_flow_entry
+) -> None:
+    """A second flow aborts with single_instance_allowed."""
+    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]
+
+
+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(hass, in_progress=[{"flow_id": "other"}])
+    result = await flow.async_step_user(None)
+    assert result["type"] == "abort"
+    assert result["reason"] == "single_instance_allowed"
+
+
+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(hass)
+    assert flow.context.get("unique_id") is None
+    await flow.async_step_user(None)
+    assert flow.context["unique_id"] == DOMAIN
+
+
+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(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"
+
+
+@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)
+
+
+@pytest.mark.parametrize("fixture_name", ["strings", "translations_en"])
+def test_flow_strings_valid_json(fixture_name: str, request: pytest.FixtureRequest) -> None:
+    """strings.json and translations/en.json are valid JSON objects."""
+    content = request.getfixturevalue(fixture_name)
+    assert isinstance(content, dict)
+
+
+@pytest.mark.parametrize("fixture_name", ["strings", "translations_en"])
+def test_flow_strings_structure(fixture_name: str, request: pytest.FixtureRequest) -> None:
+    """Config flow strings contain title, user step, and abort reasons."""
+    content = request.getfixturevalue(fixture_name)
+    assert content.get("title")
+    config = content.get("config", {})
+    assert config.get("step", {}).get("user", {}).get("title")
+    assert config.get("step", {}).get("user", {}).get("description")
+    abort = config.get("abort", {})
+    assert "single_instance_allowed" in abort
+    assert abort["single_instance_allowed"]
+
+
+@pytest.mark.parametrize("fixture_name", ["strings", "translations_en"])
+def test_flow_strings_no_raw_keys(fixture_name: str, request: pytest.FixtureRequest) -> None:
+    """No placeholder/raw keys appear in the flow strings."""
+    content = request.getfixturevalue(fixture_name)
+
+    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(content)
+
+
+def test_flow_strings_match_translations(
+    strings: dict, translations_en: dict
+) -> None:
+    """strings.json and translations/en.json carry identical flow strings."""
+    assert strings == translations_en
+
+
+def test_no_hardcoded_domain_or_db_filename_in_new_modules() -> None:
+    """Ensure no module other than const.py hardcodes the domain or db filename."""
+    pkg_dir = Path(config_flow.__file__).parent
+    py_files = [f for f in pkg_dir.glob("*.py") if f.name not in ("const.py",)]
+
+    assert len(py_files) > 0, "No python modules found in package to check"
+
+    for py_file in py_files:
+        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):
+                if node.value == "nestquest":
+                    raise AssertionError(
+                        f"Found hard-coded domain string in {py_file.name}:{node.lineno}"
+                    )
+                assert node.value != "nestquest.db", (
+                    f"Found hard-coded database filename in {py_file.name}:{node.lineno}"
+                )

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_hacs.py

@@ -0,0 +1,82 @@
+"""Tests verifying HACS repository metadata, documentation, and licensing."""
+from __future__ import annotations
+
+import json
+from pathlib import Path
+import pytest
+
+REPO_ROOT = Path(__file__).parent.parent
+HACS_JSON_PATH = REPO_ROOT / "hacs.json"
+LICENSE_PATH = REPO_ROOT / "LICENSE"
+README_PATH = REPO_ROOT / "README.md"
+MANIFEST_PATH = REPO_ROOT / "custom_components" / "nestquest" / "manifest.json"
+
+
+@pytest.fixture
+def hacs_config() -> dict:
+    """Load and parse hacs.json."""
+    assert HACS_JSON_PATH.exists(), f"hacs.json does not exist at {HACS_JSON_PATH}"
+    with open(HACS_JSON_PATH, encoding="utf-8") as f:
+        data = json.load(f)
+    return data
+
+
+def test_hacs_json_exists_and_is_valid(hacs_config: dict) -> None:
+    """Verify hacs.json exists and is valid JSON."""
+    assert isinstance(hacs_config, dict)
+    assert hacs_config.get("name") == "NestQuest"
+    assert hacs_config.get("homeassistant") == "2024.6.0"
+    assert hacs_config.get("render_readme") is True
+
+
+def test_hacs_json_minimum_version_supports_runtime_data(hacs_config: dict) -> None:
+    """Advertised HA floor must support ConfigEntry.runtime_data (added in 2024.6)."""
+    advertised = tuple(int(part) for part in hacs_config.get("homeassistant", "").split("."))
+    assert advertised >= (2024, 6, 0)
+
+
+def test_license_exists_and_non_empty() -> None:
+    """Verify LICENSE file exists and is non-empty."""
+    assert LICENSE_PATH.exists(), f"LICENSE file does not exist at {LICENSE_PATH}"
+    content = LICENSE_PATH.read_text(encoding="utf-8").strip()
+    assert len(content) > 0
+    assert "MIT License" in content
+    assert "NestQuest Contributors / Maniacs Mansion" in content
+
+
+def test_readme_exists_and_contains_required_sections() -> None:
+    """Verify README.md exists and documents HACS installation, manual installation, and test commands."""
+    assert README_PATH.exists(), f"README.md does not exist at {README_PATH}"
+    content = README_PATH.read_text(encoding="utf-8")
+    assert len(content.strip()) > 0
+
+    # Title and overview
+    assert "NestQuest - House Chaos Coordination" in content
+    assert "Home Assistant custom integration" in content
+
+    # Installation methods
+    assert "HACS" in content
+    assert "Manual Installation" in content or "manual installation" in content.lower()
+    assert "custom_components/nestquest" in content
+
+    # Developer Setup and Testing commands
+    assert "uv sync" in content
+    assert "uv run pytest" in content
+
+
+def test_readme_hacs_points_at_public_github_mirror() -> None:
+    """Verify README directs HACS users to the public GitHub mirror, not the private Gitea repo."""
+    content = README_PATH.read_text(encoding="utf-8")
+    assert "https://github.com/talon2king/NestQuest" in content
+    assert "https://code.cubecraftlabs.com/Maniacs_Mansion/NestQuest" in content
+    assert content.index("https://github.com/talon2king/NestQuest") < content.index(
+        "https://code.cubecraftlabs.com/Maniacs_Mansion/NestQuest"
+    )
+
+
+def test_hacs_directory_structure() -> None:
+    """Verify directory structure matches HACS integration expectations."""
+    assert MANIFEST_PATH.exists(), f"Integration manifest does not exist at {MANIFEST_PATH}"
+    with open(MANIFEST_PATH, encoding="utf-8") as f:
+        manifest = json.load(f)
+    assert manifest.get("domain") == "nestquest"

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

@@ -0,0 +1,258 @@
+"""Tests for the setup/unload lifecycle of NestQuest."""
+from __future__ import annotations
+
+import asyncio
+
+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 _wire(entry, registry):
+    return wire_entry_to_registry(entry, registry)
+
+
+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
+
+
+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)
+
+
+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
+    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
+
+
+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}
+    await registry.async_dispatch_options_update(entry)
+    assert hass.config_entries.async_reload.call_count == 1
+    assert registry.reloaded == [entry.entry_id]
+
+
+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
+
+
+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 await async_setup_entry(hass, entry) is True
+        assert registry.size == 1
+        assert await async_unload_entry(hass, entry) is True
+        assert registry.size == 0
+    assert hass.config_entries.async_reload.call_count == 0
+
+
+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."""
+    registry = hass.registry
+    entry = _wire(make_entry(), registry)
+    for _ in range(3):
+        await async_setup_entry(hass, entry)
+        await async_unload_entry(hass, entry)
+    assert registry._listeners.get(entry.entry_id, []) == []
+    assert registry.size == 0
+
+
+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."""
+    registry = hass.registry
+    entry = _wire(make_entry(), registry)
+    for _ in range(3):
+        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]
+        await async_unload_entry(hass, entry)
+        hass.config_entries.async_reload.reset_mock()
+        registry.reloaded.clear()
+
+
+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):
+        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
+
+
+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 == []
+
+
+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}
+
+
+async def test_setup_awaitable_listener_removal_supported() -> None:
+    """A coroutine-returning remover is awaited during unload."""
+    removed = []
+
+    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
+
+
+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)
+    assert hass.data.get(DOMAIN, {}) == {}
+    assert not hasattr(entry, "runtime_data")
+
+
+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_migrations.py

@@ -0,0 +1,677 @@
+"""Tests for migrations.py: the versioned schema migration runner."""
+from __future__ import annotations
+
+import asyncio
+import hashlib
+import logging
+import sqlite3
+from unittest.mock import AsyncMock
+
+import pytest
+
+from custom_components.nestquest.db import NestQuestDatabase
+from custom_components.nestquest.migrations import (
+    MIGRATIONS,
+    VERSION_TABLE,
+    VERSION_TABLE_DDL,
+    apply_migrations,
+    read_schema_version,
+)
+from custom_components.nestquest.schema import SCHEMA_V1_STATEMENTS
+
+
+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 _migrate(database, migrations=MIGRATIONS):
+    return _run(apply_migrations(database, migrations))
+
+
+def _version(database) -> int:
+    return _run(read_schema_version(database))
+
+
+def _tables(database) -> set[str]:
+    rows = _run(
+        database.fetch_all(
+            "SELECT name FROM sqlite_master WHERE type = 'table' "
+            "AND name NOT LIKE 'sqlite_%'"
+        )
+    )
+    return {row[0] for row in rows}
+
+
+def _counts(database) -> dict[str, int]:
+    """Row counts for every v1 data table plus the version table."""
+    tables = (
+        "children",
+        "admin_users",
+        "schedule_rules",
+        "task_definitions",
+        "presence_schedules",
+        "presence_overrides",
+        "task_instances",
+        "completion_events",
+    )
+    counts = {}
+    for table in tables:
+        row = _run(database.fetch_one(f"SELECT COUNT(*) FROM {table}"))
+        counts[table] = row[0]
+    return counts
+
+
+# ---------------------------------------------------------------------------
+# Fresh file: version 0 -> version 1
+# ---------------------------------------------------------------------------
+
+
+def test_fresh_file_migrates_to_version_1(tmp_path) -> None:
+    database = _open_db(tmp_path / "fresh.db")
+    try:
+        final = _migrate(database)
+        assert final == 1
+        assert _version(database) == 1
+    finally:
+        _run(database.close())
+
+
+def test_fresh_file_migration_creates_all_v1_tables(tmp_path) -> None:
+    database = _open_db(tmp_path / "fresh-tables.db")
+    try:
+        _migrate(database)
+        expected = {
+            "children",
+            "admin_users",
+            "schedule_rules",
+            "task_definitions",
+            "presence_schedules",
+            "presence_overrides",
+            "task_instances",
+            "completion_events",
+            VERSION_TABLE,
+        }
+        assert _tables(database) == expected
+    finally:
+        _run(database.close())
+
+
+def test_missing_version_table_raises_on_direct_read(tmp_path) -> None:
+    database = _open_db(tmp_path / "no-table.db")
+    try:
+        with pytest.raises(sqlite3.OperationalError, match="no such table"):
+            _run(read_schema_version(database))
+        row = _run(
+            database.fetch_one(
+                "SELECT name FROM sqlite_master WHERE type = 'table' "
+                f"AND name = '{VERSION_TABLE}'"
+            )
+        )
+        assert row is None, "read_schema_version must not create the table"
+    finally:
+        _run(database.close())
+
+
+def test_empty_version_table_reads_as_version_0(tmp_path) -> None:
+    database = _open_db(tmp_path / "empty-table.db")
+    try:
+        _run(database.execute(VERSION_TABLE_DDL))
+        assert _version(database) == 0
+    finally:
+        _run(database.close())
+
+
+def test_migration_1_is_the_full_v1_ddl() -> None:
+    assert MIGRATIONS[0] == SCHEMA_V1_STATEMENTS
+    assert len(MIGRATIONS) == 1
+
+
+# ---------------------------------------------------------------------------
+# Idempotence: second start is a no-op
+# ---------------------------------------------------------------------------
+
+
+def test_second_run_applies_nothing_and_keeps_rows(tmp_path) -> None:
+    database = _open_db(tmp_path / "idempotent.db")
+    try:
+        _migrate(database)
+        _run(
+            database.execute(
+                "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
+                ("Ada", "2026-09-13T00:00:00+00:00"),
+            )
+        )
+        _run(
+            database.execute(
+                "INSERT INTO admin_users (ha_user_id, added_at) VALUES (?, ?)",
+                ("user-1", "2026-09-13T00:00:00+00:00"),
+            )
+        )
+        final = _migrate(database)
+        assert final == 1
+        assert _version(database) == 1
+        assert _counts(database)["children"] == 1
+        assert _counts(database)["admin_users"] == 1
+    finally:
+        _run(database.close())
+
+
+def test_second_run_writes_nothing_to_the_file(tmp_path, caplog) -> None:
+    database = _open_db(tmp_path / "noop.db")
+    try:
+        _migrate(database)
+        _run(database.execute("PRAGMA wal_checkpoint(TRUNCATE)"))
+        db_file = tmp_path / "noop.db"
+        wal_file = tmp_path / "noop.db-wal"
+        digest_before = hashlib.sha256(db_file.read_bytes()).hexdigest()
+        mtime_before = db_file.stat().st_mtime_ns
+        wal_digest_before = (
+            hashlib.sha256(wal_file.read_bytes()).hexdigest()
+            if wal_file.exists()
+            else None
+        )
+
+        _migrate(database)
+
+        # A no-op run must not open a write transaction: the main
+        # database file is byte-identical (content hash, not just size)
+        # and untouched (mtime too), and the WAL gained no frames.
+        assert db_file.stat().st_mtime_ns == mtime_before
+        assert (
+            hashlib.sha256(db_file.read_bytes()).hexdigest() == digest_before
+        )
+        if wal_digest_before is None:
+            assert not wal_file.exists()
+        else:
+            assert (
+                hashlib.sha256(wal_file.read_bytes()).hexdigest()
+                == wal_digest_before
+            )
+    finally:
+        _run(database.close())
+
+
+def test_noop_run_logs_no_migration(caplog, tmp_path) -> None:
+    database = _open_db(tmp_path / "noop-log.db")
+    try:
+        _migrate(database)
+        caplog.clear()
+        with caplog.at_level(logging.INFO, logger="custom_components.nestquest.migrations"):
+            _migrate(database)
+        migrated = [
+            r for r in caplog.records if "migrated" in r.getMessage().lower()
+        ]
+        assert migrated == []
+    finally:
+        _run(database.close())
+
+
+def test_noop_run_is_logged_at_debug_level(caplog, tmp_path) -> None:
+    database = _open_db(tmp_path / "noop-debug.db")
+    try:
+        _migrate(database)
+        caplog.clear()
+        with caplog.at_level(logging.DEBUG, logger="custom_components.nestquest.migrations"):
+            _migrate(database)
+        assert any(
+            "already at version 1" in r.getMessage()
+            for r in caplog.records
+        )
+    finally:
+        _run(database.close())
+
+
+# ---------------------------------------------------------------------------
+# Failure atomicity: a failing migration leaves the prior version intact
+# ---------------------------------------------------------------------------
+
+
+def _failing_migrations(prior_statements: list[str]) -> list[list[str]]:
+    """Two-migration list where migration 2 fails mid-application."""
+    return [
+        prior_statements,
+        [
+            "CREATE TABLE IF NOT EXISTS later_table (id INTEGER PRIMARY KEY)",
+            "CREATE TABLE broken (",
+        ],
+    ]
+
+
+def test_failing_migration_rolls_back_to_prior_version(tmp_path) -> None:
+    database = _open_db(tmp_path / "rollback.db")
+    try:
+        # Manually simulate a database at version 1: apply migration 1's
+        # statements directly and stamp version 1 through the runner.
+        _migrate(database)
+        version_row = _run(
+            database.fetch_one(f"SELECT version FROM {VERSION_TABLE}")
+        )
+        assert version_row == (1,)
+
+        with pytest.raises(sqlite3.OperationalError):
+            _migrate(database, _failing_migrations(SCHEMA_V1_STATEMENTS))
+
+        # Version stamp unchanged, statements from the failed migration
+        # absent, and the v1 tables untouched.
+        assert _version(database) == 1
+        assert "later_table" not in _tables(database)
+        assert _counts(database)["children"] == 0
+        assert _counts(database)["completion_events"] == 0
+    finally:
+        _run(database.close())
+
+
+def test_failing_migration_on_fresh_file_leaves_version_0(tmp_path) -> None:
+    database = _open_db(tmp_path / "fresh-rollback.db")
+    try:
+        # The fresh file still reaches migration 2 (the failing one):
+        # migration 1 commits, stamping version 1, before migration 2
+        # fails and rolls back.  The run aborts at version 1 — the last
+        # fully-applied migration — not at version 0.
+        with pytest.raises(sqlite3.OperationalError):
+            _migrate(database, _failing_migrations(SCHEMA_V1_STATEMENTS))
+        assert _version(database) == 1
+        # Migration 2's tables must not survive the failed attempt.
+        assert "later_table" not in _tables(database)
+        # Migration 1's tables do: it committed before the failure.
+        assert "children" in _tables(database)
+        assert "task_instances" in _tables(database)
+    finally:
+        _run(database.close())
+
+
+def test_failed_migration_can_be_retried_after_fix(tmp_path) -> None:
+    database = _open_db(tmp_path / "retry.db")
+    try:
+        _migrate(database)
+        broken = _failing_migrations(SCHEMA_V1_STATEMENTS)
+        with pytest.raises(sqlite3.OperationalError):
+            _migrate(database, broken)
+        # "Fix" the migration and retry: it now applies cleanly.
+        broken[1] = [
+            "CREATE TABLE IF NOT EXISTS later_table (id INTEGER PRIMARY KEY)"
+        ]
+        final = _migrate(database, broken)
+        assert final == 2
+        assert _version(database) == 2
+        assert "later_table" in _tables(database)
+    finally:
+        _run(database.close())
+
+
+def test_failing_migration_error_is_preserved(tmp_path) -> None:
+    database = _open_db(tmp_path / "error-preserved.db")
+    try:
+        with pytest.raises(sqlite3.OperationalError, match="incomplete input"):
+            _migrate(database, _failing_migrations(SCHEMA_V1_STATEMENTS))
+    finally:
+        _run(database.close())
+
+
+# ---------------------------------------------------------------------------
+# Version guard rails
+# ---------------------------------------------------------------------------
+
+
+def test_future_version_is_rejected(tmp_path) -> None:
+    database = _open_db(tmp_path / "future.db")
+    try:
+        _migrate(database)
+        _run(
+            database.execute(
+                f"UPDATE {VERSION_TABLE} SET version = ?", (99,)
+            )
+        )
+        with pytest.raises(RuntimeError, match="newer than"):
+            _migrate(database)
+        # Nothing was modified by the rejected run.
+        assert _version(database) == 99
+    finally:
+        _run(database.close())
+
+
+def test_non_integer_version_raises_runtime_error(tmp_path) -> None:
+    database = _open_db(tmp_path / "string-version.db")
+    try:
+        _run(database.execute(VERSION_TABLE_DDL))
+        # The CHECK enforces version >= 1 but SQLite's loose typing
+        # stores TEXT under an INTEGER-affinity column when it cannot
+        # be losslessly coerced; guard the read path against it.
+        _run(
+            database.execute(
+                f"INSERT INTO {VERSION_TABLE} (id, version) VALUES (1, ?)",
+                ("one",),
+            )
+        )
+        with pytest.raises(RuntimeError, match="not an integer"):
+            _run(read_schema_version(database))
+    finally:
+        _run(database.close())
+
+
+def test_negative_version_read_guard_rejects_below_one() -> None:
+    """The read path rejects sub-1 versions even if storage missed them.
+
+    The VERSION_TABLE CHECK already makes negative/zero unstorable; this
+    exercises the defense-in-depth guard in read_schema_version itself
+    against a database stub returning such a row (e.g. a future schema
+    relaxing the CHECK, or a hand-edited file).
+    """
+    from unittest.mock import AsyncMock, MagicMock
+
+    for bad in (-1, 0):
+        database = MagicMock()
+        database.fetch_one = AsyncMock(return_value=(bad,))
+        with pytest.raises(RuntimeError, match="must be >= 1"):
+            _run(read_schema_version(database))
+
+
+def test_non_integer_version_read_guard_rejects_text() -> None:
+    from unittest.mock import AsyncMock, MagicMock
+
+    for bad in ("one", 1.5, True):
+        database = MagicMock()
+        database.fetch_one = AsyncMock(return_value=(bad,))
+        with pytest.raises(RuntimeError, match="not an integer"):
+            _run(read_schema_version(database))
+
+
+def test_zero_version_row_is_unstorable(tmp_path) -> None:
+    """The CHECK makes sub-1 versions impossible through SQL.
+
+    PRAGMA writable_schema does not bypass CHECKs on ordinary tables,
+    so the negative/zero read guard above is pure defense-in-depth;
+    here we prove the storage-level rule the normal path relies on.
+    """
+    database = _open_db(tmp_path / "zero-version.db")
+    try:
+        _run(database.execute(VERSION_TABLE_DDL))
+        for bad in (0, -1):
+            with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+                _run(
+                    database.execute(
+                        f"INSERT INTO {VERSION_TABLE} (id, version) "
+                        "VALUES (1, ?)",
+                        (bad,),
+                    )
+                )
+    finally:
+        _run(database.close())
+
+
+def test_version_table_enforces_singleton_row(tmp_path) -> None:
+    database = _open_db(tmp_path / "singleton.db")
+    try:
+        _run(database.execute(VERSION_TABLE_DDL))
+        _run(
+            database.execute(
+                f"INSERT INTO {VERSION_TABLE} (id, version) VALUES (1, 1)"
+            )
+        )
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            _run(
+                database.execute(
+                    f"INSERT INTO {VERSION_TABLE} (id, version) "
+                    "VALUES (2, 1)"
+                )
+            )
+    finally:
+        _run(database.close())
+
+
+def test_version_table_check_rejects_version_zero_and_negative(
+    tmp_path,
+) -> None:
+    database = _open_db(tmp_path / "version-check.db")
+    try:
+        _run(database.execute(VERSION_TABLE_DDL))
+        for bad in (0, -1):
+            with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+                _run(
+                    database.execute(
+                        f"INSERT INTO {VERSION_TABLE} (id, version) "
+                        "VALUES (1, ?)",
+                        (bad,),
+                    )
+                )
+    finally:
+        _run(database.close())
+
+
+def test_concurrent_runners_serialize_and_apply_once(tmp_path) -> None:
+    """Two concurrent runners on one connection apply each step once.
+
+    Deterministic race: runner B starts while runner A is queued into
+    its migration.  A applies everything and stamps; B then acquires
+    the migration lock, re-reads the already-stamped version, and
+    applies nothing.  Without serialization B's transaction() would
+    raise RuntimeError (the wrapper forbids concurrent transactions),
+    so this also proves the queueing works.
+    """
+    migrations = [
+        ["CREATE TABLE IF NOT EXISTS step_a (id INTEGER PRIMARY KEY)"],
+        ["CREATE TABLE IF NOT EXISTS step_b (id INTEGER PRIMARY KEY)"],
+    ]
+    results: dict[str, int] = {}
+
+    async def _main(tmp_name: str) -> None:
+        database = NestQuestDatabase(_make_hass_mock())
+        await database.open(tmp_name)
+        try:
+            # Start A, yield so it acquires the lock and enters its
+            # first transaction, then start B while A is mid-apply.
+            task_a = asyncio.ensure_future(
+                apply_migrations(database, migrations)
+            )
+            await asyncio.sleep(0)
+            task_b = asyncio.ensure_future(
+                apply_migrations(database, migrations)
+            )
+            results["a"], results["b"] = await asyncio.gather(
+                task_a, task_b
+            )
+
+            tables = {
+                row[0]
+                for row in await database.fetch_all(
+                    "SELECT name FROM sqlite_master WHERE type = 'table' "
+                    "AND name NOT LIKE 'sqlite_%'"
+                )
+            }
+            assert {"step_a", "step_b"} <= tables
+            # Each migration's statements ran exactly once: the CREATE
+            # IF NOT EXISTS would hide double-runs, so count via sqlite
+            # sequence/DDL fingerprint instead — re-check version and
+            # that both runners reported the same final version.
+            row = await database.fetch_one(
+                f"SELECT version FROM {VERSION_TABLE}"
+            )
+            assert row == (2,)
+        finally:
+            await database.close()
+
+    loop = asyncio.new_event_loop()
+    try:
+        loop.run_until_complete(_main(str(tmp_path / "concurrent.db")))
+    finally:
+        loop.close()
+
+    assert results["a"] == 2
+    assert results["b"] == 2
+
+
+def test_multiple_pending_migrations_apply_in_order(tmp_path) -> None:
+    database = _open_db(tmp_path / "multi-step.db")
+    try:
+        migrations = [
+            ["CREATE TABLE IF NOT EXISTS step_a (id INTEGER PRIMARY KEY)"],
+            ["CREATE TABLE IF NOT EXISTS step_b (id INTEGER PRIMARY KEY)"],
+            ["CREATE TABLE IF NOT EXISTS step_c (id INTEGER PRIMARY KEY)"],
+        ]
+        final = _run(apply_migrations(database, migrations))
+        assert final == 3
+        assert _version(database) == 3
+        assert {"step_a", "step_b", "step_c"} <= _tables(database)
+    finally:
+        _run(database.close())
+
+
+def test_partial_pending_migrations_resume_from_current_version(
+    tmp_path,
+) -> None:
+    database = _open_db(tmp_path / "resume.db")
+    try:
+        migrations = [
+            ["CREATE TABLE IF NOT EXISTS step_a (id INTEGER PRIMARY KEY)"],
+            ["CREATE TABLE IF NOT EXISTS step_b (id INTEGER PRIMARY KEY)"],
+        ]
+        # Advance to version 1 only, by applying a truncated list of the
+        # same migrations: patch apply_migrations' latest by passing a
+        # single-migration list first, then the full list.
+        _run(apply_migrations(database, migrations[:1]))
+        assert _version(database) == 1
+        final = _run(apply_migrations(database, migrations))
+        assert final == 2
+        assert _version(database) == 2
+        assert "step_b" in _tables(database)
+    finally:
+        _run(database.close())
+
+
+# ---------------------------------------------------------------------------
+# Startup wiring: setup runs the migration runner, not raw DDL
+# ---------------------------------------------------------------------------
+
+
+async def _setup_entry(hass, entry, registry) -> object:
+    from tests.test_lifecycle import _wire  # reuse the established wiring
+
+    entry = _wire(entry, registry)
+    from custom_components.nestquest import async_setup_entry
+
+    await async_setup_entry(hass, entry)
+    return entry
+
+
+async def test_setup_entry_migrates_fresh_database(hass, make_entry) -> None:
+    import sqlite3 as sqlite3_mod
+
+    from custom_components.nestquest.migrations import read_schema_version
+    from custom_components.nestquest.store import async_get_db_path
+
+    entry = await _setup_entry(hass, make_entry(), hass.registry)
+    database = entry.runtime_data.database
+    assert await read_schema_version(database) == 1
+    # The v1 tables exist because the runner applied migration 1.
+    names = {
+        row[0]
+        for row in await database.fetch_all(
+            "SELECT name FROM sqlite_master WHERE type = 'table' "
+            "AND name NOT LIKE 'sqlite_%'"
+        )
+    }
+    assert "children" in names and "completion_events" in names
+    # The version table exists with its singleton row stamped.
+    columns = await database.fetch_all(f"PRAGMA table_info({VERSION_TABLE})")
+    assert [(row[1], row[3]) for row in columns] == [("id", 1), ("version", 1)]
+    db_path = await async_get_db_path(hass)
+    conn = sqlite3_mod.connect(db_path)
+    try:
+        conn.execute(f"INSERT INTO {VERSION_TABLE} (id, version) VALUES (2, 1)")
+        violated = False
+    except sqlite3_mod.IntegrityError:
+        violated = True
+    finally:
+        conn.close()
+    assert violated, "singleton-row CHECK must be live in the setup-created DB"
+    await database.close()
+
+
+async def test_options_reload_reopens_and_rechecks_version(
+    hass, make_entry
+) -> None:
+    """A listener-triggered reload re-runs setup against the same file.
+
+    Unload then setup is the reload lifecycle: the old connection is
+    closed, apply_migrations runs again on the persisted file, keeps
+    version and rows intact, and the reopened connection is live.
+    """
+    from custom_components.nestquest import async_unload_entry
+    from custom_components.nestquest.migrations import read_schema_version
+
+    entry = await _setup_entry(hass, make_entry(), hass.registry)
+    database_a = entry.runtime_data.database
+    await database_a.execute(
+        "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
+        ("Ada", "2026-09-13T00:00:00+00:00"),
+    )
+    assert await async_unload_entry(hass, entry) is True
+    assert not database_a.connected
+
+    entry_reloaded = await _setup_entry(hass, make_entry(), hass.registry)
+    database_b = entry_reloaded.runtime_data.database
+    assert database_b is not database_a
+    try:
+        assert await read_schema_version(database_b) == 1
+        row = await database_b.fetch_one("SELECT COUNT(*) FROM children")
+        assert row == (1,)
+    finally:
+        await database_b.close()
+
+
+async def test_setup_entry_failure_closes_database(
+    hass, make_entry, monkeypatch
+) -> None:
+    """A failing migration closes the opened connection before re-raising.
+
+    Spies on the real wrapper instance: records whether close() ran and
+    whether the connection is left disconnected, rather than inferring
+    from hass.data (a failed setup never stores a runtime record).
+    """
+    import pytest as pytest_module
+
+    import custom_components.nestquest as nestquest_module
+    from custom_components.nestquest.db import NestQuestDatabase
+    from custom_components.nestquest.migrations import (
+        MIGRATIONS as _MIGRATIONS,
+    )
+
+    async def _failing(database, migrations=_MIGRATIONS):
+        raise RuntimeError("boom")
+
+    monkeypatch.setattr(
+        nestquest_module, "apply_migrations", _failing, raising=True
+    )
+
+    close_calls: list[bool] = []
+    original_close = NestQuestDatabase.close
+
+    async def _spy_close(self):
+        close_calls.append(True)
+        return await original_close(self)
+
+    monkeypatch.setattr(NestQuestDatabase, "close", _spy_close)
+
+    entry = make_entry()
+    with pytest_module.raises(RuntimeError, match="boom"):
+        await _setup_entry(hass, entry, hass.registry)
+
+    assert close_calls == [True], (
+        "failed setup must close the connection it opened"
+    )
+    record = hass.data["nestquest"].get(entry.entry_id)
+    assert record is None, "failed setup must not store runtime data"

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_store.py

@@ -0,0 +1,154 @@
+"""Tests for store.py of NestQuest."""
+from __future__ import annotations
+
+import ast
+import asyncio
+from pathlib import Path
+from unittest.mock import AsyncMock, MagicMock
+
+from custom_components.nestquest import store
+from custom_components.nestquest.const import SQLITE_DB_FILENAME
+
+
+def _run(coro):
+    return asyncio.new_event_loop().run_until_complete(coro)
+
+
+def _make_hass(config_dir: Path, execute: bool = True):
+    hass = MagicMock()
+    hass.config.path = MagicMock(return_value=str(config_dir / SQLITE_DB_FILENAME))
+    hass.async_add_executor_job = AsyncMock(
+        side_effect=(lambda fn, *a: fn(*a)) if execute else None
+    )
+    return hass
+
+
+def test_returns_absolute_path_from_config_path() -> None:
+    hass = _make_hass(Path("/config"), execute=False)
+    db_path = _run(store.async_get_db_path(hass))
+    hass.config.path.assert_called_once_with(SQLITE_DB_FILENAME)
+    assert db_path == Path("/config") / SQLITE_DB_FILENAME
+    assert db_path.is_absolute()
+
+
+def test_relative_config_path_is_normalized_to_absolute(tmp_path, monkeypatch) -> None:
+    monkeypatch.chdir(tmp_path)
+    hass = MagicMock()
+    hass.config.path = MagicMock(return_value="relative-config/" + SQLITE_DB_FILENAME)
+    hass.async_add_executor_job = AsyncMock(side_effect=(lambda fn, *a: fn(*a)))
+    db_path = _run(store.async_get_db_path(hass))
+    assert db_path.is_absolute()
+    assert db_path.name == SQLITE_DB_FILENAME
+    assert str(db_path).endswith("relative-config/" + SQLITE_DB_FILENAME)
+    assert db_path.parent.is_dir()
+    assert (tmp_path / "relative-config").is_dir()
+    assert not db_path.exists()
+
+
+def test_creates_missing_parent_directory(tmp_path) -> None:
+    nested = tmp_path / "homeassistant" / "config"
+    hass = _make_hass(nested)
+    db_path = _run(store.async_get_db_path(hass))
+    assert db_path.parent == nested
+    assert nested.is_dir()
+    assert db_path.parent.is_dir()
+    assert not db_path.exists()
+
+
+def test_two_calls_return_same_path(tmp_path) -> None:
+    hass = _make_hass(tmp_path)
+    first = _run(store.async_get_db_path(hass))
+    second = _run(store.async_get_db_path(hass))
+    assert first == second
+    assert first.parent.is_dir()
+    assert not first.exists()
+    assert not second.exists()
+
+
+def test_existing_directory_does_not_fail(tmp_path) -> None:
+    nested = tmp_path / "config"
+    nested.mkdir()
+    hass = _make_hass(nested)
+    db_path = _run(store.async_get_db_path(hass))
+    assert db_path == nested / SQLITE_DB_FILENAME
+    assert db_path.parent.is_dir()
+    assert not db_path.exists()
+
+
+def test_mkdir_runs_via_executor(tmp_path) -> None:
+    nested = tmp_path / "deep" / "nested"
+    hass = _make_hass(nested)
+    db_path = _run(store.async_get_db_path(hass))
+    assert nested.is_dir()
+    hass.async_add_executor_job.assert_called_once()
+    args, kwargs = hass.async_add_executor_job.call_args
+    fn, fn_args = args[0], args[1:]
+    assert callable(fn)
+    assert fn is store._ensure_dir
+    assert fn_args == (nested,)
+    assert kwargs == {}
+    assert db_path.parent == nested
+    assert db_path.parent.is_dir()
+    assert not db_path.exists()
+
+
+def test_executor_helper_creates_directory(tmp_path) -> None:
+    nested = tmp_path / "executor" / "made"
+    store._ensure_dir(nested)
+    assert nested.is_dir()
+    store._ensure_dir(nested)
+    assert nested.is_dir()
+
+
+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):
+                for alias in node.names:
+                    assert alias.name != "sqlite3", (
+                        f"Found sqlite3 import in {py_file.name}:{node.lineno}"
+                    )
+            elif isinstance(node, ast.ImportFrom) and node.module == "sqlite3":
+                raise AssertionError(
+                    f"Found sqlite3 import in {py_file.name}:{node.lineno}"
+                )
+
+
+def test_no_file_creation_calls_in_store() -> None:
+    source = Path(store.__file__).read_text(encoding="utf-8")
+    tree = ast.parse(source, filename=str(store.__file__))
+    for node in ast.walk(tree):
+        if isinstance(node, ast.Call):
+            func = node.func
+            if isinstance(func, ast.Name) and func.id == "open":
+                raise AssertionError(
+                    f"Found open() call in store.py:{node.lineno}"
+                )
+            if (
+                isinstance(func, ast.Attribute)
+                and func.attr == "touch"
+            ):
+                raise AssertionError(
+                    f"Found touch() call in store.py:{node.lineno}"
+                )
+    assert "touch(" not in source
+    assert "open(" not in source
+
+
+def test_container_style_path_resolves() -> None:
+    hass = _make_hass(Path("/config"), execute=False)
+    assert _run(store.async_get_db_path(hass)) == Path("/config") / SQLITE_DB_FILENAME
+
+
+def test_core_style_path_resolves(tmp_path) -> None:
+    config_dir = tmp_path / ".homeassistant"
+    hass = _make_hass(config_dir)
+    assert _run(store.async_get_db_path(hass)) == config_dir / SQLITE_DB_FILENAME
+    assert (config_dir / SQLITE_DB_FILENAME).parent.is_dir()

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" },
+]