test: race hook pauses after validation passes, before INSERT; assert create must win that ordering

The pause point is now the actual TOCTOU window (validation passed,
INSERT pending): the create-first ordering asserts deleted is False -
the serialized transaction must keep the queued delete from removing
the rule between the create's check and its insert. Tautological or
True assertion removed.

README.md

@@ -4,7 +4,7 @@ Home Assistant custom integration for managing chores, custody schedules, and ho
 
 ## Overview
 
-NestQuest helps households coordinate daily life, chore tracking, custody schedules, and shared tasks directly within Home Assistant. Version 0.1.0 is the integration foundation: a configuration scaffold providing the package and manifest, shared constants, a single-instance config flow, setup/unload lifecycle handling with an options-reload listener, an options flow (horizon days, day rollover time, panel idle timeout), SQLite storage-path resolution, and HACS metadata. Sensors, calendar events, and service calls are forthcoming in later releases and are not yet part of the integration.
+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
 

RELEASE_NOTES.md

@@ -1,45 +0,0 @@
-# NestQuest Release Notes
-
-## Version 0.1.0 — 2026-09-13
-
-### Scope
-
-First release of NestQuest, a Home Assistant custom integration for managing chores, custody schedules, and house coordination. This release ships the integration foundation/configuration scaffold only (Feature 01):
-
-- Integration package and manifest, plus HACS metadata (`hacs.json`).
-- Shared constants module.
-- Single-instance config flow for initial setup.
-- Setup/unload lifecycle handling with an options-reload listener.
-- Options flow for configuring `horizon_days`, `day_rollover_time`, and `panel_idle_timeout`.
-- SQLite storage path resolver — path resolution only; the database itself is not created in this release.
-- Test harness (`pytest` via `uv`).
-
-### Requirements
-
-- Minimum Home Assistant version: **2024.6.0**.
-
-### Known Limitations
-
-- No entities, services, or calendar platforms yet — the integration does not expose sensors, calendar events, or service calls in this release.
-- Single-instance only — one NestQuest config entry is permitted per Home Assistant instance.
-- The integration does not yet create its database file; storage is limited to resolved configuration paths.
-
-### Rollback
-
-v0.1.0 is the first release — there is no previous NestQuest version to downgrade to, so rollback means complete removal. This release performs no data or schema migrations.
-
-**HACS installs:**
-
-1. Remove the NestQuest integration via HACS (HACS > Integrations > NestQuest > Remove).
-2. Restart Home Assistant.
-3. Remove the integration in Home Assistant (Settings > Devices & Services > NestQuest > Delete), then restart Home Assistant again.
-
-**Manual installs:**
-
-1. Delete the `custom_components/nestquest/` directory from your Home Assistant configuration directory.
-2. Restart Home Assistant.
-3. Remove the integration in Home Assistant (Settings > Devices & Services > NestQuest > Delete), then restart Home Assistant again.
-
-**Data:** the integration does not yet create its own database file, so there is no application data. However, Home Assistant persists the config entry and its selected options — removing the integration deletes that configuration, and the option values (`horizon_days`, `day_rollover_time`, `panel_idle_timeout`) will be lost on removal.
-
-**Repository operators:** instead of uninstalling, you may git-revert the release merge on `main`, which removes the integration files entirely. End users should use the uninstall path above.

custom_components/nestquest/__init__.py

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

custom_components/nestquest/dao_children.py

@@ -0,0 +1,289 @@
+"""Typed DAO layer for the children and admin_users tables.
+
+All SQL for ``children`` and ``admin_users`` lives in this module per
+the feature guardrail: callers get typed dataclasses back and never see
+raw rows or SQL.  Every method is async and runs through
+:class:`~.db.NestQuestDatabase`, so each statement executes on the HA
+executor and the event loop never blocks.
+
+Error contract: violations surface as the underlying
+:class:`sqlite3.IntegrityError` (duplicate, foreign-key, or CHECK
+failure) from the execute call; the DAO adds no wrapping exception
+type.  A get that matches nothing returns ``None``; a mutation that
+matches nothing is reported by :class:`~.db.ExecutionResult.rowcount`,
+which each method returns where the caller may need it (reorder, and
+the admin remove/set paths).
+"""
+from __future__ import annotations
+
+import asyncio
+from dataclasses import dataclass
+
+from .db import NestQuestDatabase
+
+
+@dataclass(frozen=True)
+class ChildRecord:
+    """One row of ``children``."""
+
+    id: int
+    display_name: str
+    colour: str | None
+    avatar_ref: str | None
+    sort_order: int
+    is_active: bool
+    created_at: str
+
+
+@dataclass(frozen=True)
+class AdminUserRecord:
+    """One row of ``admin_users``."""
+
+    ha_user_id: str
+    added_at: str
+
+
+def _child_from_row(row: tuple) -> ChildRecord:
+    """Build a :class:`ChildRecord` from a SELECT row."""
+    return ChildRecord(
+        id=row[0],
+        display_name=row[1],
+        colour=row[2],
+        avatar_ref=row[3],
+        sort_order=row[4],
+        is_active=bool(row[5]),
+        created_at=row[6],
+    )
+
+
+_CHILD_COLUMNS = (
+    "id, display_name, colour, avatar_ref, sort_order, is_active, created_at"
+)
+
+
+#: One asyncio.Lock per (connection wrapper, running loop), shared by
+#: every ChildrenDao instance over the same database: the wrapper
+#: rejects a second concurrent transaction with RuntimeError, so ALL
+#: transactional DAO operations on one connection must queue here, not
+#: per-instance.  Keyed like the migration runner's lock; entries are
+#: never evicted (a config entry holds one wrapper per process).
+_CONNECTION_LOCKS: dict[tuple[int, int], asyncio.Lock] = {}
+
+
+def _connection_lock(database) -> asyncio.Lock:
+    """Return the shared transaction-serializing lock for ``database``."""
+    loop = asyncio.get_running_loop()
+    key = (id(database), id(loop))
+    lock = _CONNECTION_LOCKS.get(key)
+    if lock is None:
+        lock = asyncio.Lock()
+        _CONNECTION_LOCKS[key] = lock
+    return lock
+
+
+class ChildrenDao:
+    """Typed async data access for the ``children`` table.
+
+    Deletion is deliberately absent: children are deactivated, never
+    deleted, because completion history references them (feature
+    guardrail).
+    """
+
+    def __init__(self, database) -> None:
+        self._database = database
+
+    async def create(
+        self,
+        display_name: str,
+        created_at: str,
+        *,
+        colour: str | None = None,
+        avatar_ref: str | None = None,
+        sort_order: int = 0,
+        is_active: bool = True,
+    ) -> ChildRecord:
+        """Insert one child and return the record as stored."""
+        result = await self._database.execute(
+            "INSERT INTO children (display_name, colour, avatar_ref, "
+            "sort_order, is_active, created_at) VALUES (?, ?, ?, ?, ?, ?)",
+            (
+                display_name,
+                colour,
+                avatar_ref,
+                sort_order,
+                int(is_active),
+                created_at,
+            ),
+        )
+        child = await self.get(result.lastrowid)
+        assert child is not None
+        return child
+
+    async def get(self, child_id: int) -> ChildRecord | None:
+        """Return the child with ``child_id``, or None."""
+        row = await self._database.fetch_one(
+            f"SELECT {_CHILD_COLUMNS} FROM children WHERE id = ?",
+            (child_id,),
+        )
+        return _child_from_row(row) if row is not None else None
+
+    async def list_active(self) -> list[ChildRecord]:
+        """Return active children ordered by sort_order, then id."""
+        rows = await self._database.fetch_all(
+            f"SELECT {_CHILD_COLUMNS} FROM children WHERE is_active = 1 "
+            "ORDER BY sort_order, id"
+        )
+        return [_child_from_row(row) for row in rows]
+
+    async def list_all(self) -> list[ChildRecord]:
+        """Return all children ordered by sort_order, then id."""
+        rows = await self._database.fetch_all(
+            f"SELECT {_CHILD_COLUMNS} FROM children "
+            "ORDER BY sort_order, id"
+        )
+        return [record for record in map(_child_from_row, rows)]
+
+    async def update(
+        self,
+        child_id: int,
+        *,
+        display_name: str | None = None,
+        colour: str | None = None,
+        avatar_ref: str | None = None,
+        sort_order: int | None = None,
+    ) -> int:
+        """Update the given fields; returns rows updated (0 if absent).
+
+        Only non-None arguments are written, so a caller cannot
+        accidentally blank a column by omitting it.
+        """
+        assignments: list[str] = []
+        parameters: list[object] = []
+        if display_name is not None:
+            assignments.append("display_name = ?")
+            parameters.append(display_name)
+        if colour is not None:
+            assignments.append("colour = ?")
+            parameters.append(colour)
+        if avatar_ref is not None:
+            assignments.append("avatar_ref = ?")
+            parameters.append(avatar_ref)
+        if sort_order is not None:
+            assignments.append("sort_order = ?")
+            parameters.append(sort_order)
+        if not assignments:
+            return 0
+        parameters.append(child_id)
+        result = await self._database.execute(
+            f"UPDATE children SET {', '.join(assignments)} WHERE id = ?",
+            tuple(parameters),
+        )
+        return result.rowcount
+
+    async def set_active(self, child_id: int, is_active: bool) -> int:
+        """Set the child's active flag; returns rows updated (0 if absent)."""
+        result = await self._database.execute(
+            "UPDATE children SET is_active = ? WHERE id = ?",
+            (int(is_active), child_id),
+        )
+        return result.rowcount
+
+    async def reorder(self, ordered_ids: list[int]) -> None:
+        """Persist a new sort order: ``ordered_ids[i]`` gets sort_order i.
+
+        Runs inside one transaction; positions are staged at offset
+        -len (all negative, so any permutation of the same ids never
+        collides mid-reorder under the wrapper's single-connection
+        serialization), then finalized 0..n-1.  Duplicate ids are
+        rejected up front: they cannot all hold distinct positions, and
+        silently assigning the last occurrence's index would hide a
+        caller bug.
+
+        Concurrent reorder() calls on one connection are serialized by
+        a connection-scoped asyncio lock (shared across every ChildrenDao
+        over the same wrapper, however many instances): the wrapper's
+        transaction() rejects a second concurrent transaction with
+        RuntimeError, so racing callers would crash instead of
+        queueing.  The lock makes the second call wait, then run
+        against the first call's final state.
+        """
+        if not ordered_ids:
+            return
+        if len(set(ordered_ids)) != len(ordered_ids):
+            raise ValueError(
+                "reorder() received duplicate child ids; every id must "
+                "appear at most once"
+            )
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                offset = len(ordered_ids)
+                await self._database.execute_many(
+                    "UPDATE children SET sort_order = ? WHERE id = ?",
+                    [
+                        (-(offset + index), child_id)
+                        for index, child_id in enumerate(ordered_ids)
+                    ],
+                )
+                await self._database.execute_many(
+                    "UPDATE children SET sort_order = ? WHERE id = ?",
+                    [
+                        (index, child_id)
+                        for index, child_id in enumerate(ordered_ids)
+                    ],
+                )
+
+
+class AdminUsersDao:
+    """Typed async access to the ``admin_users`` allowlist.
+
+    The allowlist stores HA user IDs, never usernames (feature
+    guardrail); an empty allowlist means no admin, enforced by callers
+    resolving through :meth:`exists`.
+    """
+
+    def __init__(self, database) -> None:
+        self._database = database
+
+    async def add(self, ha_user_id: str, added_at: str) -> bool:
+        """Add a user to the allowlist; True if inserted, False if present.
+
+        Idempotent under concurrency: a single INSERT ... ON CONFLICT DO
+        NOTHING decides and inserts in one statement (the wrapper
+        serializes statements, not check-then-insert sequences, so a
+        SELECT/INSERT pair would let two concurrent adds both see no
+        row and the second one raise IntegrityError).  rowcount reports
+        whether the insert actually happened.
+        """
+        result = await self._database.execute(
+            "INSERT INTO admin_users (ha_user_id, added_at) VALUES (?, ?) "
+            "ON CONFLICT (ha_user_id) DO NOTHING",
+            (ha_user_id, added_at),
+        )
+        return result.rowcount > 0
+
+    async def remove(self, ha_user_id: str) -> bool:
+        """Remove a user from the allowlist; True if a row was removed."""
+        result = await self._database.execute(
+            "DELETE FROM admin_users WHERE ha_user_id = ?",
+            (ha_user_id,),
+        )
+        return result.rowcount > 0
+
+    async def list(self) -> list[AdminUserRecord]:
+        """Return every allowlisted user, oldest first."""
+        rows = await self._database.fetch_all(
+            "SELECT ha_user_id, added_at FROM admin_users "
+            "ORDER BY added_at, ha_user_id"
+        )
+        return [
+            AdminUserRecord(ha_user_id=row[0], added_at=row[1])
+            for row in rows
+        ]
+
+    async def exists(self, ha_user_id: str) -> bool:
+        """Return True if ``ha_user_id`` is allowlisted."""
+        row = await self._database.fetch_one(
+            "SELECT 1 FROM admin_users WHERE ha_user_id = ?",
+            (ha_user_id,),
+        )
+        return row is not None

custom_components/nestquest/dao_rules.py

@@ -0,0 +1,465 @@
+"""Typed DAO layer for the schedule_rules and task_definitions tables.
+
+All SQL for ``schedule_rules`` and ``task_definitions`` lives in this
+module per the feature guardrails: callers get typed dataclasses back
+and never see raw rows or SQL.  Every method is async and runs through
+:class:`~.db.NestQuestDatabase`, so each statement executes on the HA
+executor and the event loop never blocks.
+
+Guardrail mapping:
+
+- ``schedule_rules`` deletion is delete-if-unreferenced: a rule still
+  referenced by any task definition is rejected (the done-condition),
+  not cascaded.
+- ``task_definitions`` has no delete path at all: definitions are
+  deactivated, never hard-deleted, and history must survive.
+- ``set_assignee`` changes future instances only — the DAO writes the
+  definition row and nothing else; reassignment never rewrites
+  existing instances or completion history (Feature 06 guardrail).
+- Validation of rule well-formedness and child activeness lives partly
+  in the schema CHECKs (rule-type coherence) and partly in
+  :meth:`TaskDefinitionsDao.create` (active child, rule existence);
+  no permission checks here, that is the Feature 09 gate's job.
+
+Concurrency: ``delete_rule_if_unreferenced`` performs its reference
+check and DELETE inside one transaction under the connection-scoped
+lock shared with :mod:`.dao_children`, so a definition referencing the
+rule cannot appear between check and delete on this connection.
+"""
+from __future__ import annotations
+
+import datetime
+from dataclasses import dataclass
+
+from .dao_children import _connection_lock
+from .db import NestQuestDatabase
+
+#: Sentinel distinguishing "argument omitted" from "explicit SQL NULL"
+#: in update methods: passing ``None`` must mean clearing a nullable
+#: column, not skipping it.
+_UNSET = object()
+
+#: Date-shape policy: schedule-rule dates are ISO YYYY-MM-DD.  The
+#: schema's lexical end>=start CHECK cannot prove a value is a real
+#: calendar date, so the DAO validates the shape it accepts.
+_DATE_FORMAT = "%Y-%m-%d"
+
+
+def _validate_date(value: str, field: str) -> None:
+    """Raise ValueError unless ``value`` is a YYYY-MM-DD calendar date.
+
+    ``datetime.date.fromisoformat`` accepts ISO dates but is lenient on
+    some builds (e.g. '2026-9-4'); strptime with an exact format is
+    strict, and strptime round-trips the parsed value to reject zero
+    padding ('2026-9-4' parses but its isoformat differs).
+    """
+    try:
+        parsed = datetime.datetime.strptime(value, _DATE_FORMAT).date()
+    except (TypeError, ValueError):
+        raise ValueError(
+            f"{field} must be an ISO date (YYYY-MM-DD), got {value!r}"
+        ) from None
+    if parsed.isoformat() != value:
+        raise ValueError(
+            f"{field} must be a strict YYYY-MM-DD date, got {value!r}"
+        )
+
+
+@dataclass(frozen=True)
+class ScheduleRuleRecord:
+    """One row of ``schedule_rules``."""
+
+    id: int
+    rule_type: str
+    interval: int
+    weekday_set: str | None
+    day_of_month: int | None
+    nth_weekday: int | None
+    month: int | None
+    start_date: str
+    end_date: str | None
+
+
+@dataclass(frozen=True)
+class TaskDefinitionRecord:
+    """One row of ``task_definitions``."""
+
+    id: int
+    title: str
+    description: str | None
+    icon: str | None
+    child_id: int
+    schedule_rule_id: int
+    due_time: str | None
+    is_active: bool
+    created_at: str
+
+
+_RULE_COLUMNS = (
+    "id, rule_type, interval, weekday_set, day_of_month, nth_weekday, "
+    "month, start_date, end_date"
+)
+_DEFINITION_COLUMNS = (
+    "id, title, description, icon, child_id, schedule_rule_id, due_time, "
+    "is_active, created_at"
+)
+
+
+def _rule_from_row(row: tuple) -> ScheduleRuleRecord:
+    return ScheduleRuleRecord(
+        id=row[0],
+        rule_type=row[1],
+        interval=row[2],
+        weekday_set=row[3],
+        day_of_month=row[4],
+        nth_weekday=row[5],
+        month=row[6],
+        start_date=row[7],
+        end_date=row[8],
+    )
+
+
+def _definition_from_row(row: tuple) -> TaskDefinitionRecord:
+    return TaskDefinitionRecord(
+        id=row[0],
+        title=row[1],
+        description=row[2],
+        icon=row[3],
+        child_id=row[4],
+        schedule_rule_id=row[5],
+        due_time=row[6],
+        is_active=bool(row[7]),
+        created_at=row[8],
+    )
+
+
+class ScheduleRulesDao:
+    """Typed async access to the ``schedule_rules`` table."""
+
+    def __init__(self, database: NestQuestDatabase) -> None:
+        self._database = database
+
+    async def create(
+        self,
+        rule_type: str,
+        start_date: str,
+        *,
+        interval: int = 1,
+        weekday_set: str | None = None,
+        day_of_month: int | None = None,
+        nth_weekday: int | None = None,
+        month: int | None = None,
+        end_date: str | None = None,
+    ) -> ScheduleRuleRecord:
+        """Insert one rule and return the record as stored.
+
+        Rule-type coherence (a weekly rule needs ``weekday_set``, a
+        yearly rule needs ``month``, etc.) is enforced by the schema
+        CHECKs and surfaces as :class:`sqlite3.IntegrityError`.  Date
+        shape (strict YYYY-MM-DD) is validated here because the
+        schema's lexical end>=start CHECK cannot prove a real calendar
+        date.
+        """
+        _validate_date(start_date, "start_date")
+        if end_date is not None:
+            _validate_date(end_date, "end_date")
+        result = await self._database.execute(
+            "INSERT INTO schedule_rules (rule_type, interval, weekday_set, "
+            "day_of_month, nth_weekday, month, start_date, end_date) "
+            "VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
+            (
+                rule_type,
+                interval,
+                weekday_set,
+                day_of_month,
+                nth_weekday,
+                month,
+                start_date,
+                end_date,
+            ),
+        )
+        rule = await self.get(result.lastrowid)
+        assert rule is not None
+        return rule
+
+    async def get(self, rule_id: int) -> ScheduleRuleRecord | None:
+        """Return the rule with ``rule_id``, or None."""
+        row = await self._database.fetch_one(
+            f"SELECT {_RULE_COLUMNS} FROM schedule_rules WHERE id = ?",
+            (rule_id,),
+        )
+        return _rule_from_row(row) if row is not None else None
+
+    async def update(
+        self,
+        rule_id: int,
+        *,
+        rule_type: str | None | object = _UNSET,
+        interval: int | None | object = _UNSET,
+        weekday_set: str | None | object = _UNSET,
+        day_of_month: int | None | object = _UNSET,
+        nth_weekday: int | None | object = _UNSET,
+        month: int | None | object = _UNSET,
+        start_date: str | None | object = _UNSET,
+        end_date: str | None | object = _UNSET,
+    ) -> int:
+        """Update the given fields; returns rows updated (0 if absent).
+
+        Arguments default to the module sentinel ``_UNSET`` meaning
+        "leave this column alone"; passing ``None`` explicitly writes
+        SQL NULL, so callers can reopen an ended rule (``end_date``
+        back to NULL) or clear nullable shape fields.  Passing
+        ``None``/a new value for a date field validates its shape.
+        Editing a rule that a definition references changes future
+        materializations only — this method touches no instances
+        (Feature 06/07 separation).
+        """
+        date_args = (
+            ("start_date", start_date),
+            ("end_date", end_date),
+        )
+        for field, value in date_args:
+            if value is not _UNSET and value is not None:
+                _validate_date(value, field)
+        assignments: list[str] = []
+        parameters: list[object] = []
+        for column, value in (
+            ("rule_type", rule_type),
+            ("interval", interval),
+            ("weekday_set", weekday_set),
+            ("day_of_month", day_of_month),
+            ("nth_weekday", nth_weekday),
+            ("month", month),
+            ("start_date", start_date),
+            ("end_date", end_date),
+        ):
+            if value is not _UNSET:
+                assignments.append(f"{column} = ?")
+                parameters.append(value)
+        if not assignments:
+            return 0
+        parameters.append(rule_id)
+        result = await self._database.execute(
+            f"UPDATE schedule_rules SET {', '.join(assignments)} "
+            "WHERE id = ?",
+            tuple(parameters),
+        )
+        return result.rowcount
+
+    async def delete_if_unreferenced(self, rule_id: int) -> bool:
+        """Delete the rule only if no definition references it.
+
+        Returns True if the rule was deleted, False if it is still
+        referenced (or does not exist — distinguish via :meth:`get`).
+        The reference check and the DELETE run inside one transaction
+        under the connection lock, so a definition cannot start
+        referencing the rule between the check and the delete.
+        """
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                row = await self._database.fetch_one(
+                    "SELECT 1 FROM task_definitions "
+                    "WHERE schedule_rule_id = ? LIMIT 1",
+                    (rule_id,),
+                )
+                if row is not None:
+                    return False
+                result = await self._database.execute(
+                    "DELETE FROM schedule_rules WHERE id = ?",
+                    (rule_id,),
+                )
+                return result.rowcount > 0
+
+
+class TaskDefinitionsDao:
+    """Typed async access to the ``task_definitions`` table.
+
+    No delete method exists: definitions are deactivated via
+    :meth:`set_active`, never hard-deleted, so completion history keeps
+    its references (feature guardrail).
+    """
+
+    def __init__(self, database: NestQuestDatabase) -> None:
+        self._database = database
+
+    async def create(
+        self,
+        title: str,
+        child_id: int,
+        schedule_rule_id: int,
+        created_at: str,
+        *,
+        description: str | None = None,
+        icon: str | None = None,
+        due_time: str | None = None,
+        is_active: bool = True,
+    ) -> TaskDefinitionRecord:
+        """Insert one definition and return the record as stored.
+
+        Validates the assignment target as part of the same serialized
+        transaction as the insert: the child must exist and be active,
+        and the rule must exist, AT INSERT TIME — a child deactivated
+        or a rule deleted concurrently cannot slip between the checks
+        and the INSERT, which would otherwise create an invalid
+        assignment or surface as a raw FK IntegrityError instead of
+        the documented ValueError.  An INACTIVE child would pass the
+        schema foreign keys, which is why the activeness check lives
+        here.
+        """
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                await self._validate_assignable(
+                    child_id, schedule_rule_id
+                )
+                result = await self._database.execute(
+                    "INSERT INTO task_definitions (title, description, "
+                    "icon, child_id, schedule_rule_id, due_time, "
+                    "is_active, created_at) "
+                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
+                    (
+                        title,
+                        description,
+                        icon,
+                        child_id,
+                        schedule_rule_id,
+                        due_time,
+                        int(is_active),
+                        created_at,
+                    ),
+                )
+                definition = await self.get(result.lastrowid)
+        assert definition is not None
+        return definition
+
+    async def _validate_assignable(
+        self, child_id: int, schedule_rule_id: int
+    ) -> None:
+        """Raise ValueError unless child (active) and rule both exist.
+
+        MUST be called inside the connection lock so the verdict cannot
+        go stale before the caller's INSERT commits.
+        """
+        await self._validate_assignable_child(child_id)
+        rule = await self._database.fetch_one(
+            "SELECT 1 FROM schedule_rules WHERE id = ?",
+            (schedule_rule_id,),
+        )
+        if rule is None:
+            raise ValueError(
+                f"schedule rule {schedule_rule_id} does not exist"
+            )
+
+    async def get(self, definition_id: int) -> TaskDefinitionRecord | None:
+        """Return the definition with ``definition_id``, or None."""
+        row = await self._database.fetch_one(
+            f"SELECT {_DEFINITION_COLUMNS} FROM task_definitions "
+            "WHERE id = ?",
+            (definition_id,),
+        )
+        return _definition_from_row(row) if row is not None else None
+
+    async def list_by_child(self, child_id: int) -> list[TaskDefinitionRecord]:
+        """Return all definitions assigned to ``child_id``, newest first."""
+        rows = await self._database.fetch_all(
+            f"SELECT {_DEFINITION_COLUMNS} FROM task_definitions "
+            "WHERE child_id = ? ORDER BY id DESC",
+            (child_id,),
+        )
+        return [_definition_from_row(row) for row in rows]
+
+    async def list_active(self) -> list[TaskDefinitionRecord]:
+        """Return all active definitions, oldest first (stable order)."""
+        rows = await self._database.fetch_all(
+            f"SELECT {_DEFINITION_COLUMNS} FROM task_definitions "
+            "WHERE is_active = 1 ORDER BY id"
+        )
+        return [_definition_from_row(row) for row in rows]
+
+    async def update(
+        self,
+        definition_id: int,
+        *,
+        title: str | None | object = _UNSET,
+        description: str | None | object = _UNSET,
+        icon: str | None | object = _UNSET,
+        due_time: str | None | object = _UNSET,
+    ) -> int:
+        """Update the given fields; returns rows updated (0 if absent).
+
+        Arguments default to the module sentinel ``_UNSET`` meaning
+        "leave this column alone"; passing ``None`` explicitly writes
+        SQL NULL, so callers can remove optional metadata
+        (description, icon, due_time).  Assignment and activation are
+        deliberately NOT settable here: they have their own explicit
+        operations (:meth:`set_assignee`, :meth:`set_active`) so they
+        stay separately loggable and permission-gateable.
+        """
+        assignments: list[str] = []
+        parameters: list[object] = []
+        for column, value in (
+            ("title", title),
+            ("description", description),
+            ("icon", icon),
+            ("due_time", due_time),
+        ):
+            if value is not _UNSET:
+                assignments.append(f"{column} = ?")
+                parameters.append(value)
+        if not assignments:
+            return 0
+        parameters.append(definition_id)
+        result = await self._database.execute(
+            f"UPDATE task_definitions SET {', '.join(assignments)} "
+            "WHERE id = ?",
+            tuple(parameters),
+        )
+        return result.rowcount
+
+    async def set_active(self, definition_id: int, is_active: bool) -> int:
+        """Deactivate/reactivate; returns rows updated (0 if absent).
+
+        Deactivating stops future instance generation but leaves
+        existing instances and completion history untouched.
+        """
+        result = await self._database.execute(
+            "UPDATE task_definitions SET is_active = ? WHERE id = ?",
+            (int(is_active), definition_id),
+        )
+        return result.rowcount
+
+    async def set_assignee(
+        self, definition_id: int, child_id: int
+    ) -> int:
+        """Reassign the definition to ``child_id``; rows updated (0 if absent).
+
+        Validates the new assignee inside the same serialized
+        transaction as the UPDATE (existing and active AT ASSIGNMENT
+        TIME), so a child deactivated concurrently cannot become the
+        assignee.  Changes future instances only: already-generated
+        instances and completion history are never rewritten here.
+        """
+        async with _connection_lock(self._database):
+            async with self._database.transaction():
+                await self._validate_assignable_child(child_id)
+                result = await self._database.execute(
+                    "UPDATE task_definitions SET child_id = ? "
+                    "WHERE id = ?",
+                    (child_id, definition_id),
+                )
+        return result.rowcount
+
+    async def _validate_assignable_child(self, child_id: int) -> None:
+        """Raise ValueError unless the child exists and is active.
+
+        MUST be called inside the connection lock so the verdict cannot
+        go stale before the caller's UPDATE commits.
+        """
+        child = await self._database.fetch_one(
+            "SELECT is_active FROM children WHERE id = ?", (child_id,)
+        )
+        if child is None:
+            raise ValueError(f"child {child_id} does not exist")
+        if not child[0]:
+            raise ValueError(
+                f"child {child_id} is inactive and cannot be assigned"
+            )

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/manifest.json

@@ -3,10 +3,10 @@
   "name": "NestQuest",
   "codeowners": ["@overseer"],
   "config_flow": true,
-  "documentation": "https://github.com/talon2king/NestQuest",
+  "documentation": "https://code.cubecraftlabs.com/Maniacs_Mansion/NestQuest",
   "integration_type": "hub",
   "iot_class": "local_polling",
-  "issue_tracker": "https://github.com/talon2king/NestQuest/issues",
+  "issue_tracker": "https://code.cubecraftlabs.com/Maniacs_Mansion/NestQuest/issues",
   "requirements": [],
   "version": "0.1.0"
 }

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/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)

tests/conftest.py

@@ -25,6 +25,9 @@ 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
 
@@ -218,13 +221,35 @@ def wire_entry_to_registry(entry, registry: ListenerRegistry):
     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."""
-    hass = SimpleNamespace(
+    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:

tests/test_dao_children.py

@@ -0,0 +1,557 @@
+"""Tests for dao_children.py: typed DAO for children and admin_users."""
+from __future__ import annotations
+
+import asyncio
+import sqlite3
+
+import pytest
+
+from custom_components.nestquest.dao_children import (
+    AdminUserRecord,
+    AdminUsersDao,
+    ChildRecord,
+    ChildrenDao,
+)
+from custom_components.nestquest.db import NestQuestDatabase
+from custom_components.nestquest.schema import SCHEMA_V1_STATEMENTS
+from custom_components.nestquest.migrations import apply_migrations
+
+
+def _run(coro):
+    return asyncio.new_event_loop().run_until_complete(coro)
+
+
+def _make_hass_mock():
+    from unittest.mock import AsyncMock, MagicMock
+
+    hass = MagicMock()
+    hass.async_add_executor_job = AsyncMock(side_effect=(lambda fn, *a: fn(*a)))
+    return hass
+
+
+def _open_db(path) -> NestQuestDatabase:
+    database = NestQuestDatabase(_make_hass_mock())
+    _run(database.open(path))
+    _run(apply_migrations(database))
+    return database
+
+
+def _daos(database) -> tuple[ChildrenDao, AdminUsersDao]:
+    return ChildrenDao(database), AdminUsersDao(database)
+
+
+NOW = "2026-09-14T11:00:00+00:00"
+
+
+def _make_child(dao, name="Ada", **kwargs) -> ChildRecord:
+    return _run(dao.create(name, NOW, **kwargs))
+
+
+# ---------------------------------------------------------------------------
+# children: create and get
+# ---------------------------------------------------------------------------
+
+
+def test_create_returns_typed_record(tmp_path) -> None:
+    database = _open_db(tmp_path / "create.db")
+    try:
+        dao, _ = _daos(database)
+        child = _make_child(dao, colour="#ff0000", avatar_ref="avatars/1")
+        assert isinstance(child, ChildRecord)
+        assert child.id == 1
+        assert child.display_name == "Ada"
+        assert child.colour == "#ff0000"
+        assert child.avatar_ref == "avatars/1"
+        assert child.sort_order == 0
+        assert child.is_active is True
+        assert child.created_at == NOW
+    finally:
+        _run(database.close())
+
+
+def test_get_returns_none_for_missing_child(tmp_path) -> None:
+    database = _open_db(tmp_path / "get-missing.db")
+    try:
+        dao, _ = _daos(database)
+        assert _run(dao.get(999)) is None
+    finally:
+        _run(database.close())
+
+
+def test_create_duplicate_name_is_allowed(tmp_path) -> None:
+    database = _open_db(tmp_path / "dup-name.db")
+    try:
+        dao, _ = _daos(database)
+        first = _make_child(dao, "Ada")
+        second = _make_child(dao, "Ada")
+        assert first.id != second.id
+    finally:
+        _run(database.close())
+
+
+# ---------------------------------------------------------------------------
+# children: list active / list all
+# ---------------------------------------------------------------------------
+
+
+def test_list_active_excludes_inactive_and_sorts(tmp_path) -> None:
+    database = _open_db(tmp_path / "list-active.db")
+    try:
+        dao, _ = _daos(database)
+        _make_child(dao, "Bo", sort_order=2)
+        ada = _make_child(dao, "Ada", sort_order=1)
+        cy = _make_child(dao, "Cy", sort_order=1)
+        _make_child(dao, "Dee", sort_order=0)
+        _run(dao.set_active(ada.id, False))
+        active = _run(dao.list_active())
+        assert [c.display_name for c in active] == ["Dee", "Cy", "Bo"]
+        assert all(c.is_active for c in active)
+    finally:
+        _run(database.close())
+
+
+def test_list_all_includes_inactive(tmp_path) -> None:
+    database = _open_db(tmp_path / "list-all.db")
+    try:
+        dao, _ = _daos(database)
+        ada = _make_child(dao, "Ada", sort_order=1)
+        _make_child(dao, "Bo", sort_order=0)
+        _run(dao.set_active(ada.id, False))
+        all_children = _run(dao.list_all())
+        assert [c.display_name for c in all_children] == ["Bo", "Ada"]
+        assert all_children[1].is_active is False
+    finally:
+        _run(database.close())
+
+
+# ---------------------------------------------------------------------------
+# children: update, set_active, reorder
+# ---------------------------------------------------------------------------
+
+
+def test_update_fields_round_trip(tmp_path) -> None:
+    database = _open_db(tmp_path / "update.db")
+    try:
+        dao, _ = _daos(database)
+        child = _make_child(dao, "Ada", colour="#111111")
+        updated = _run(
+            dao.update(
+                child.id,
+                display_name="Ada L.",
+                colour="#222222",
+                avatar_ref="avatars/x.png",
+            )
+        )
+        assert updated == 1
+        fetched = _run(dao.get(child.id))
+        assert fetched is not None
+        assert fetched.display_name == "Ada L."
+        assert fetched.colour == "#222222"
+        assert fetched.avatar_ref == "avatars/x.png"
+        assert fetched.sort_order == 0
+    finally:
+        _run(database.close())
+
+
+def test_update_omitted_fields_untouched(tmp_path) -> None:
+    database = _open_db(tmp_path / "update-partial.db")
+    try:
+        dao, _ = _daos(database)
+        child = _make_child(dao, "Ada", colour="#111111")
+        _run(dao.update(child.id, display_name="Ada L."))
+        fetched = _run(dao.get(child.id))
+        assert fetched.colour == "#111111"
+    finally:
+        _run(database.close())
+
+
+def test_update_no_fields_returns_zero_and_changes_nothing(tmp_path) -> None:
+    database = _open_db(tmp_path / "update-empty.db")
+    try:
+        dao, _ = _daos(database)
+        child = _make_child(dao, "Ada")
+        assert _run(dao.update(child.id)) == 0
+        fetched = _run(dao.get(child.id))
+        assert fetched is not None and fetched.display_name == "Ada"
+    finally:
+        _run(database.close())
+
+
+def test_update_missing_child_returns_zero(tmp_path) -> None:
+    database = _open_db(tmp_path / "update-missing.db")
+    try:
+        dao, _ = _daos(database)
+        assert _run(dao.update(999, display_name="X")) == 0
+    finally:
+        _run(database.close())
+
+
+def test_set_active_missing_child_returns_zero(tmp_path) -> None:
+    database = _open_db(tmp_path / "set-active-missing.db")
+    try:
+        dao, _ = _daos(database)
+        assert _run(dao.set_active(999, False)) == 0
+    finally:
+        _run(database.close())
+
+
+def test_reorder_assigns_positions_in_order(tmp_path) -> None:
+    database = _open_db(tmp_path / "reorder.db")
+    try:
+        dao, _ = _daos(database)
+        a = _make_child(dao, "Ada")
+        b = _make_child(dao, "Bo")
+        c = _make_child(dao, "Cy")
+        _run(dao.reorder([c.id, a.id, b.id]))
+        children = _run(dao.list_all())
+        assert [x.display_name for x in children] == ["Cy", "Ada", "Bo"]
+        assert [x.sort_order for x in children] == [0, 1, 2]
+    finally:
+        _run(database.close())
+
+
+def test_reorder_is_transactional_and_idempotent(tmp_path) -> None:
+    database = _open_db(tmp_path / "reorder-idempotent.db")
+    try:
+        dao, _ = _daos(database)
+        a = _make_child(dao, "Ada")
+        b = _make_child(dao, "Bo")
+        _run(dao.reorder([b.id, a.id]))
+        _run(dao.reorder([b.id, a.id]))
+        children = _run(dao.list_all())
+        assert [(x.display_name, x.sort_order) for x in children] == [
+            ("Bo", 0),
+            ("Ada", 1),
+        ]
+    finally:
+        _run(database.close())
+
+
+def test_reorder_empty_list_is_noop(tmp_path) -> None:
+    database = _open_db(tmp_path / "reorder-empty.db")
+    try:
+        dao, _ = _daos(database)
+        _make_child(dao, "Ada")
+        _run(dao.reorder([]))
+        children = _run(dao.list_all())
+        assert children[0].sort_order == 0
+    finally:
+        _run(database.close())
+
+
+def test_reorder_unknown_id_leaves_it_untouched(tmp_path) -> None:
+    database = _open_db(tmp_path / "reorder-unknown.db")
+    try:
+        dao, _ = _daos(database)
+        a = _make_child(dao, "Ada")
+        _run(dao.reorder([a.id, 999]))
+        children = _run(dao.list_all())
+        assert children[0].sort_order == 0
+    finally:
+        _run(database.close())
+
+
+def test_reorder_rolls_back_to_original_order_on_failure(tmp_path) -> None:
+    """A failing finalize batch rolls the whole reorder back.
+
+    The two-phase staging is only meaningful if a crash between the
+    batches leaves the original order intact: monkeypatch execute_many
+    so the finalize (non-negative) batch raises, then assert every
+    sort_order is unchanged.
+    """
+    database = _open_db(tmp_path / "reorder-rollback.db")
+    try:
+        dao, _ = _daos(database)
+        a = _make_child(dao, "Ada", sort_order=0)
+        b = _make_child(dao, "Bo", sort_order=1)
+
+        original_execute_many = database.execute_many
+
+        async def _failing_execute_many(sql, parameters):
+            # The staging batch writes negative positions; the finalize
+            # batch writes 0..n-1.  Fail the finalize batch.
+            if all(position >= 0 for position, _ in parameters):
+                raise sqlite3.OperationalError("boom")
+            return await original_execute_many(sql, parameters)
+
+        database.execute_many = _failing_execute_many
+        with pytest.raises(sqlite3.OperationalError, match="boom"):
+            _run(dao.reorder([b.id, a.id]))
+        children = _run(dao.list_all())
+        assert [(c.display_name, c.sort_order) for c in children] == [
+            ("Ada", 0),
+            ("Bo", 1),
+        ], "failed reorder must leave original sort orders intact"
+    finally:
+        _run(database.close())
+
+
+def test_reorder_rejects_duplicate_ids(tmp_path) -> None:
+    database = _open_db(tmp_path / "reorder-duplicate.db")
+    try:
+        dao, _ = _daos(database)
+        a = _make_child(dao, "Ada")
+        with pytest.raises(ValueError, match="duplicate"):
+            _run(dao.reorder([a.id, a.id]))
+        fetched = _run(dao.get(a.id))
+        assert fetched is not None and fetched.sort_order == 0
+    finally:
+        _run(database.close())
+
+
+def test_reorder_concurrent_calls_serialize(tmp_path) -> None:
+    """Concurrent reorders from DIFFERENT DAO instances queue safely.
+
+    Two ChildrenDao objects over one connection race two reorders: the
+    connection-scoped lock makes the second wait, then run against the
+    first call's final state.  Without the lock the wrapper's
+    transaction() would reject the second concurrent transaction with
+    RuntimeError.
+    """
+    async def _main() -> None:
+        database = NestQuestDatabase(_make_hass_mock())
+        await database.open(tmp_path / "reorder-concurrent.db")
+        try:
+            await apply_migrations(database)
+            dao_a, _ = _daos(database)
+            a = await dao_a.create("Ada", NOW)
+            b = await dao_a.create("Bo", NOW)
+            dao_b, _ = _daos(database)  # second instance, same connection
+            task_1 = asyncio.ensure_future(dao_a.reorder([b.id, a.id]))
+            await asyncio.sleep(0)
+            task_2 = asyncio.ensure_future(dao_b.reorder([a.id, b.id]))
+            await asyncio.gather(task_1, task_2)
+            children = await dao_a.list_all()
+            orders = {c.display_name: c.sort_order for c in children}
+            assert sorted(orders.values()) == [0, 1]
+        finally:
+            await database.close()
+
+    _run(_main())
+
+
+# ---------------------------------------------------------------------------
+# admin_users: add, remove, list, exists
+# ---------------------------------------------------------------------------
+
+
+def test_admin_add_then_exists(tmp_path) -> None:
+    database = _open_db(tmp_path / "admin-add.db")
+    try:
+        _, dao = _daos(database)
+        assert _run(dao.add("user-1", NOW)) is True
+        assert _run(dao.exists("user-1")) is True
+        assert _run(dao.exists("user-2")) is False
+    finally:
+        _run(database.close())
+
+
+def test_admin_add_is_idempotent(tmp_path) -> None:
+    database = _open_db(tmp_path / "admin-idempotent.db")
+    try:
+        _, dao = _daos(database)
+        assert _run(dao.add("user-1", NOW)) is True
+        assert _run(dao.add("user-1", NOW)) is False
+        records = _run(dao.list())
+        assert len(records) == 1
+        assert records[0].added_at == NOW
+    finally:
+        _run(database.close())
+
+
+def test_admin_add_concurrent_calls_are_idempotent(tmp_path) -> None:
+    """Concurrent add() calls of the same user: one inserts, rest False.
+
+    The single-statement ON CONFLICT DO NOTHING makes the decision and
+    the write atomic per statement, so racing coroutines cannot both
+    'see no row' and one of them raise IntegrityError.
+    """
+    async def _main() -> None:
+        database = NestQuestDatabase(_make_hass_mock())
+        await database.open(tmp_path / "admin-race.db")
+        try:
+            await apply_migrations(database)
+            _, dao = _daos(database)
+            tasks = [
+                asyncio.ensure_future(dao.add("user-1", NOW))
+                for _ in range(5)
+            ]
+            await asyncio.sleep(0)
+            results = list(await asyncio.gather(*tasks))
+            assert sorted(results) == [False, False, False, False, True]
+            records = await dao.list()
+            assert len(records) == 1
+            assert records[0].added_at == NOW
+        finally:
+            await database.close()
+
+    _run(_main())
+
+
+def test_admin_add_preserves_original_added_at(tmp_path) -> None:
+    database = _open_db(tmp_path / "admin-added-at.db")
+    try:
+        _, dao = _daos(database)
+        _run(dao.add("user-1", NOW))
+        _run(dao.add("user-1", "2026-09-15T00:00:00+00:00"))
+        records = _run(dao.list())
+        assert records[0].added_at == NOW
+    finally:
+        _run(database.close())
+
+
+def test_admin_remove_returns_true_then_false(tmp_path) -> None:
+    database = _open_db(tmp_path / "admin-remove.db")
+    try:
+        _, dao = _daos(database)
+        _run(dao.add("user-1", NOW))
+        assert _run(dao.remove("user-1")) is True
+        assert _run(dao.remove("user-1")) is False
+        assert _run(dao.exists("user-1")) is False
+    finally:
+        _run(database.close())
+
+
+def test_admin_list_returns_typed_records_ordered(tmp_path) -> None:
+    database = _open_db(tmp_path / "admin-list.db")
+    try:
+        _, dao = _daos(database)
+        _run(dao.add("user-2", "2026-09-14T10:00:00+00:00"))
+        _run(dao.add("user-1", "2026-09-14T09:00:00+00:00"))
+        records = _run(dao.list())
+        assert isinstance(records[0], AdminUserRecord)
+        assert [r.ha_user_id for r in records] == ["user-1", "user-2"]
+        assert records[0].added_at == "2026-09-14T09:00:00+00:00"
+    finally:
+        _run(database.close())
+
+
+def test_admin_exists_empty_allowlist_fails_closed(tmp_path) -> None:
+    database = _open_db(tmp_path / "admin-empty.db")
+    try:
+        _, dao = _daos(database)
+        assert _run(dao.exists("user-1")) is False
+        assert _run(dao.list()) == []
+    finally:
+        _run(database.close())
+
+
+def test_admin_records_are_typed_not_raw_rows(tmp_path) -> None:
+    database = _open_db(tmp_path / "admin-typed.db")
+    try:
+        _, dao = _daos(database)
+        _run(dao.add("user-1", NOW))
+        records = _run(dao.list())
+        assert records == [AdminUserRecord("user-1", NOW)]
+    finally:
+        _run(database.close())
+
+
+# ---------------------------------------------------------------------------
+# No SQL for these tables outside the DAO module
+# ---------------------------------------------------------------------------
+
+
+def test_children_and_admin_sql_lives_only_in_dao_module() -> None:
+    """Guardrail: children/admin_users SQL may appear only in the DAO
+    (and the schema DDL declarations).  Scans the integration package
+    AND the repo's test files RECURSIVELY, matching FROM/INTO/UPDATE/
+    DELETE FROM/JOIN against either table — including DELETE FROM
+    children, the hard-delete path the guardrail forbids.
+
+    Exclusions are exact repo-relative paths, narrowly justified:
+    schema.py declares the tables' DDL; migrations.py applies that DDL;
+    test_schema.py and test_migrations.py test exactly those
+    DDL/migration layers.  None of them touch the tables' data paths —
+    that is the DAO's job.
+    """
+    import re
+    from pathlib import Path
+
+    package = Path(
+        __import__(
+            "custom_components.nestquest", fromlist=["__file__"]
+        ).__file__
+    ).parent
+    repo_root = package.parent.parent
+    scan_roots = [package, repo_root / "tests"]
+
+    allowed = {
+        "custom_components/nestquest/dao_children.py",  # the DAO itself
+        "custom_components/nestquest/dao_rules.py",  # validates child
+        # activeness on assignment (FK to children, done-condition
+        # 'validates that the assigned child is active')
+        "custom_components/nestquest/schema.py",  # declares the DDL
+        "custom_components/nestquest/migrations.py",  # applies the DDL
+        "tests/test_schema.py",  # tests the DDL
+        "tests/test_migrations.py",  # tests migration application
+        "tests/test_dao_children.py",  # this file, scanned separately
+        "tests/test_dao_rules.py",  # rules/definitions guard, own scope
+    }
+    sql_pattern = re.compile(
+        r"(FROM|INTO|UPDATE|DELETE\s+FROM|JOIN)\s+"
+        r'["\`]*(children|admin_users)["\`]*\b',
+        re.IGNORECASE,
+    )
+    offenders: list[str] = []
+    for root in scan_roots:
+        for py in sorted(root.rglob("*.py")):
+            relative = py.relative_to(repo_root).as_posix()
+            if relative in allowed:
+                continue
+            if sql_pattern.search(py.read_text()):
+                offenders.append(relative)
+    assert offenders == [], (
+        f"SQL touching children/admin_users leaked into: {offenders}"
+    )
+
+
+def test_dao_test_file_uses_dao_not_raw_table_sql() -> None:
+    """This test module must exercise the DAO, not raw SQL, for these
+    tables.  The two guard functions in this file necessarily mention
+    the table names inside their regex patterns; their source spans are
+    removed BEFORE the scan, and the remaining lines are then scanned
+    as ONE combined blob (not line-by-line), so SQL split across
+    physical lines — e.g. an implicitly concatenated string ending in
+    FROM children — is still caught.  The guard regexes themselves
+    cannot match the SQL keyword pattern, so removing the spans only
+    spares regex text, not executable SQL.
+    """
+    import ast
+    import re
+    from pathlib import Path
+
+    path = Path(__file__)
+    text = path.read_text()
+    lines = text.splitlines(keepends=True)
+    tree = ast.parse(text)
+    guard_names = {
+        "test_children_and_admin_sql_lives_only_in_dao_module",
+        "test_dao_test_file_uses_dao_not_raw_table_sql",
+    }
+    excluded: set[int] = set()
+    for node in ast.walk(tree):
+        if (
+            isinstance(node, (ast.FunctionDef, ast.AsyncFunctionDef))
+            and node.name in guard_names
+        ):
+            excluded.update(
+                range(node.lineno, (node.end_lineno or node.lineno) + 1)
+            )
+
+    # Scan the source minus excluded lines as ONE blob so SQL split
+    # across physical lines cannot evade the pattern.
+    remaining = "".join(
+        line
+        for number, line in enumerate(lines, start=1)
+        if number not in excluded
+    )
+    sql_pattern = re.compile(
+        r"(SELECT\s[^\"']*?FROM|INSERT\s+INTO|UPDATE|"
+        r"DELETE\s+FROM|FROM|JOIN)\s+[`'\"]*(\[)?"
+        r"(children|admin_users)\b",
+        re.IGNORECASE,
+    )
+    assert sql_pattern.search(remaining) is None, (
+        "tests must go through the DAO, not raw SQL, for these tables"
+    )

tests/test_dao_rules.py

@@ -0,0 +1,862 @@
+"""Tests for dao_rules.py: typed DAO for schedule_rules and task_definitions."""
+from __future__ import annotations
+
+import asyncio
+import sqlite3
+
+import pytest
+
+from custom_components.nestquest.dao_children import ChildrenDao
+from custom_components.nestquest.dao_rules import (
+    ScheduleRuleRecord,
+    ScheduleRulesDao,
+    TaskDefinitionRecord,
+    TaskDefinitionsDao,
+)
+from custom_components.nestquest.db import NestQuestDatabase
+from custom_components.nestquest.migrations import apply_migrations
+
+
+def _run(coro):
+    return asyncio.new_event_loop().run_until_complete(coro)
+
+
+def _make_hass_mock():
+    from unittest.mock import AsyncMock, MagicMock
+
+    hass = MagicMock()
+    hass.async_add_executor_job = AsyncMock(side_effect=(lambda fn, *a: fn(*a)))
+    return hass
+
+
+NOW = "2026-09-14T12:00:00+00:00"
+
+
+async def _prepare(path) -> tuple:
+    database = NestQuestDatabase(_make_hass_mock())
+    await database.open(path)
+    await apply_migrations(database)
+    rules = ScheduleRulesDao(database)
+    definitions = TaskDefinitionsDao(database)
+    children = ChildrenDao(database)
+    child = await children.create("Ada", NOW)
+    return database, rules, definitions, children, child
+
+
+def _with_db(tmp_path, name):
+    """Run an async body against a prepared database, closing it after."""
+
+    def _run_test(body):
+        async def _main():
+            database, rules, definitions, children, child = await _prepare(
+                tmp_path / name
+            )
+            try:
+                return await body(database, rules, definitions, children, child)
+            finally:
+                await database.close()
+
+        return _run(_main())
+
+    return _run_test
+
+
+# ---------------------------------------------------------------------------
+# schedule_rules: create / get
+# ---------------------------------------------------------------------------
+
+
+def test_rule_create_returns_typed_record(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create(
+            "weekly", "2026-09-14", weekday_set="0,2,4", interval=2
+        )
+        assert isinstance(rule, ScheduleRuleRecord)
+        assert rule.id == 1
+        assert rule.rule_type == "weekly"
+        assert rule.interval == 2
+        assert rule.weekday_set == "0,2,4"
+        assert rule.day_of_month is None
+        assert rule.nth_weekday is None
+        assert rule.month is None
+        assert rule.start_date == "2026-09-14"
+        assert rule.end_date is None
+        return rule
+
+    _with_db(tmp_path, "rule-create.db")(_body)
+
+
+def test_rule_create_monthly_with_day_of_month(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create(
+            "monthly", "2026-09-14", day_of_month=15, weekday_set=None
+        )
+        assert rule.day_of_month == 15
+        assert rule.weekday_set is None
+        return rule
+
+    _with_db(tmp_path, "rule-monthly.db")(_body)
+
+
+def test_rule_get_missing_returns_none(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        return await rules.get(999)
+
+    assert _with_db(tmp_path, "rule-get-missing.db")(_body) is None
+
+
+def test_rule_create_coherence_enforced_by_schema(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            await rules.create("weekly", "2026-09-14", weekday_set=None)
+        return None
+
+    _with_db(tmp_path, "rule-coherence.db")(_body)
+
+
+def test_rule_create_rejects_malformed_dates(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        for bad in (
+            "not-a-date",
+            "2026-9-4",
+            "2026-13-01",
+            "2026-02-30",
+            "09-14",
+            "",
+        ):
+            with pytest.raises(ValueError, match="YYYY-MM-DD"):
+                await rules.create("daily", bad)
+            with pytest.raises(ValueError, match="YYYY-MM-DD"):
+                await rules.create("daily", "2026-09-14", end_date=bad)
+        return None
+
+    _with_db(tmp_path, "rule-create-dates.db")(_body)
+
+
+def test_rule_create_end_before_start_rejected(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            await rules.create(
+                "daily", "2026-09-14", end_date="2026-09-13"
+            )
+        return None
+
+    _with_db(tmp_path, "rule-dates.db")(_body)
+
+
+# ---------------------------------------------------------------------------
+# schedule_rules: update
+# ---------------------------------------------------------------------------
+
+
+def test_rule_update_fields_round_trip(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("weekly", "2026-09-14", weekday_set="0,2")
+        updated = await rules.update(
+            rule.id, weekday_set="1,3", interval=3, end_date="2026-12-31"
+        )
+        assert updated == 1
+        fetched = await rules.get(rule.id)
+        assert fetched.weekday_set == "1,3"
+        assert fetched.interval == 3
+        assert fetched.end_date == "2026-12-31"
+        assert fetched.rule_type == "weekly"
+        return fetched
+
+    _with_db(tmp_path, "rule-update.db")(_body)
+
+
+def test_rule_update_omitted_fields_untouched(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("weekly", "2026-09-14", weekday_set="0,2")
+        await rules.update(rule.id, interval=2)
+        fetched = await rules.get(rule.id)
+        assert fetched.weekday_set == "0,2"
+        assert fetched.start_date == "2026-09-14"
+        return fetched
+
+    _with_db(tmp_path, "rule-update-partial.db")(_body)
+
+
+def test_rule_update_no_fields_returns_zero(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        return await rules.update(rule.id)
+
+    assert _with_db(tmp_path, "rule-update-empty.db")(_body) == 0
+
+
+def test_rule_update_missing_rule_returns_zero(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        return await rules.update(999, interval=2)
+
+    assert _with_db(tmp_path, "rule-update-missing.db")(_body) == 0
+
+
+def test_rule_update_bad_type_rejected_by_check(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
+            await rules.update(rule.id, rule_type="sometimes")
+        return None
+
+    _with_db(tmp_path, "rule-update-bad-type.db")(_body)
+
+
+def test_rule_update_can_clear_nullable_fields(tmp_path) -> None:
+    """Explicit None clears a nullable column; omitted leaves it alone.
+
+    A caller must be able to reopen an ended rule (end_date back to
+    NULL) and clear rule-shape fields; the _UNSET sentinel keeps
+    'omitted' distinct from 'write NULL'.
+    """
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create(
+            "weekly",
+            "2026-09-14",
+            weekday_set="0,2",
+            end_date="2026-12-31",
+            month=3,
+        )
+        # Omitted: nothing changes.
+        await rules.update(rule.id, interval=2)
+        kept = await rules.get(rule.id)
+        assert kept.end_date == "2026-12-31"
+        assert kept.month == 3
+        # Explicit None: clears both.
+        await rules.update(rule.id, end_date=None, month=None)
+        cleared = await rules.get(rule.id)
+        assert cleared.end_date is None
+        assert cleared.month is None
+        assert cleared.interval == 2
+        return cleared
+
+    _with_db(tmp_path, "rule-update-clear.db")(_body)
+
+
+def test_rule_update_validates_date_shape(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        for bad in ("not-a-date", "2026-9-4", "2026-13-01", "09-14"):
+            with pytest.raises(ValueError, match="YYYY-MM-DD"):
+                await rules.update(rule.id, start_date=bad)
+            with pytest.raises(ValueError, match="YYYY-MM-DD"):
+                await rules.update(rule.id, end_date=bad)
+        # None is still allowed (clears the column) without validation.
+        await rules.update(rule.id, end_date=None)
+        return None
+
+    _with_db(tmp_path, "rule-update-dates.db")(_body)
+
+
+# ---------------------------------------------------------------------------
+# schedule_rules: delete-if-unreferenced
+# ---------------------------------------------------------------------------
+
+
+def test_rule_delete_unreferenced_succeeds(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        assert await rules.delete_if_unreferenced(rule.id) is True
+        return await rules.get(rule.id)
+
+    assert _with_db(tmp_path, "rule-delete-ok.db")(_body) is None
+
+
+def test_rule_delete_referenced_is_rejected(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        await definitions.create("Brush teeth", child.id, rule.id, NOW)
+        assert await rules.delete_if_unreferenced(rule.id) is False
+        fetched = await rules.get(rule.id)
+        assert fetched is not None, (
+            "referenced rule must survive the delete attempt"
+        )
+        return fetched
+
+    _with_db(tmp_path, "rule-delete-referenced.db")(_body)
+
+
+def test_rule_delete_missing_returns_false(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        return await rules.delete_if_unreferenced(999)
+
+    assert _with_db(tmp_path, "rule-delete-missing.db")(_body) is False
+
+
+def test_rule_delete_after_last_reference_removed_succeeds(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        definition = await definitions.create(
+            "Brush teeth", child.id, rule.id, NOW
+        )
+        assert await rules.delete_if_unreferenced(rule.id) is False
+        await definitions.set_active(definition.id, False)
+        # Deactivation is NOT deletion: the definition still references
+        # the rule, so the delete must still be rejected.
+        assert await rules.delete_if_unreferenced(rule.id) is False
+        return None
+
+    _with_db(tmp_path, "rule-delete-inactive-ref.db")(_body)
+
+
+def test_rule_delete_concurrent_with_definition_create_is_safe(
+    tmp_path,
+) -> None:
+    """A racing definition creation cannot produce a dangling reference.
+
+    Both operations queue on the connection lock, so exactly one of two
+    safe outcomes happens: the create wins and the delete is rejected
+    (definition exists, rule exists), or the delete wins and the create
+    fails cleanly with ValueError (rule gone).  A definition row
+    pointing at a deleted rule is impossible in either case.
+
+    BOTH orderings are forced: delete-first by starting the delete and
+    yielding; create-first by hooking ScheduleRulesDao.get so the
+    create's validation pauses mid-flight before the delete starts.
+    """
+    async def _main(order: str):
+        database = NestQuestDatabase(_make_hass_mock())
+        await database.open(tmp_path / f"rule-delete-race-{order}.db")
+        try:
+            await apply_migrations(database)
+            children = ChildrenDao(database)
+            rules = ScheduleRulesDao(database)
+            definitions = TaskDefinitionsDao(database)
+            child = await children.create("Ada", NOW)
+            rule_a = await rules.create("daily", "2026-09-14")
+
+            if order == "create-first":
+                # Pause the create inside its rule validation: the hook
+                # blocks after the rule-lookup statement until the
+                # delete task has been scheduled, so the create holds
+                # the connection lock while the delete queues behind
+                # it — then the create proceeds and wins.
+                import custom_components.nestquest.dao_rules as dao_rules
+
+                original_validate = (
+                    dao_rules.TaskDefinitionsDao._validate_assignable
+                )
+                release = asyncio.Event()
+                started = asyncio.Event()
+
+                async def _pausing_validate(self, child_id, rule_id):
+                    # Run the real validation FIRST (this is the
+                    # time-of-check), THEN pause: the delete task gets
+                    # scheduled while the create sits between its
+                    # passed validation and its INSERT.  The serialized
+                    # transaction must keep the delete queued until the
+                    # insert commits, so the create must win the race
+                    # and the delete must be rejected as referenced.
+                    await original_validate(self, child_id, rule_id)
+                    if rule_id == rule_a.id and not started.is_set():
+                        started.set()
+                        await release.wait()
+
+                dao_rules.TaskDefinitionsDao._validate_assignable = (
+                    _pausing_validate
+                )
+                try:
+                    create_task = asyncio.ensure_future(
+                        definitions.create(
+                            "Brush teeth", child.id, rule_a.id, NOW
+                        )
+                    )
+                    await started.wait()
+                    delete_task = asyncio.ensure_future(
+                        rules.delete_if_unreferenced(rule_a.id)
+                    )
+                    await asyncio.sleep(0)
+                    started.clear()
+                    release.set()
+                    deleted, creation = await asyncio.gather(
+                        delete_task, create_task, return_exceptions=True
+                    )
+                finally:
+                    dao_rules.TaskDefinitionsDao._validate_assignable = (
+                        original_validate
+                    )
+            else:
+                async def _delete():
+                    return await rules.delete_if_unreferenced(rule_a.id)
+
+                async def _create():
+                    return await definitions.create(
+                        "Brush teeth", child.id, rule_a.id, NOW
+                    )
+
+                delete_task = asyncio.ensure_future(_delete())
+                await asyncio.sleep(0)
+                create_task = asyncio.ensure_future(_create())
+                deleted, creation = await asyncio.gather(
+                    delete_task, create_task, return_exceptions=True
+                )
+
+            assert not isinstance(deleted, BaseException), (
+                f"delete must queue, not crash: {deleted!r}"
+            )
+            rule_row = await rules.get(rule_a.id)
+            if deleted:
+                # Delete won: the create must have failed cleanly.
+                assert isinstance(creation, ValueError), (
+                    f"creating against a deleted rule must raise "
+                    f"ValueError, got {creation!r}"
+                )
+                assert rule_row is None
+            else:
+                # Create won: the delete was rejected, both rows exist.
+                assert isinstance(creation, TaskDefinitionRecord), (
+                    f"create must succeed, got {creation!r}"
+                )
+                assert rule_row is not None
+            return deleted
+        finally:
+            await database.close()
+
+    delete_won = _run(_main("delete-first"))
+    create_won = _run(_main("create-first"))
+    # delete-first: the delete acquires the lock before the create can
+    # validate, so the rule is gone and the create fails cleanly.
+    assert delete_won is True
+    # create-first: validation passed, the create holds the lock across
+    # its INSERT, so the queued delete must find the definition and be
+    # rejected — the create must NOT lose its rule after validating it.
+    assert create_won is False
+
+
+# ---------------------------------------------------------------------------
+# task_definitions: create / get
+# ---------------------------------------------------------------------------
+
+
+def test_definition_create_returns_typed_record(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("weekly", "2026-09-14", weekday_set="0,2")
+        definition = await definitions.create(
+            "Brush teeth",
+            child.id,
+            rule.id,
+            NOW,
+            description="Morning and night",
+            icon="mdi:tooth",
+            due_time="08:00",
+        )
+        assert isinstance(definition, TaskDefinitionRecord)
+        assert definition.title == "Brush teeth"
+        assert definition.child_id == child.id
+        assert definition.schedule_rule_id == rule.id
+        assert definition.due_time == "08:00"
+        assert definition.is_active is True
+        assert definition.created_at == NOW
+        return definition
+
+    _with_db(tmp_path, "definition-create.db")(_body)
+
+
+def test_definition_get_missing_returns_none(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        return await definitions.get(999)
+
+    assert _with_db(tmp_path, "definition-get-missing.db")(_body) is None
+
+
+def test_definition_create_unknown_child_raises_value_error(
+    tmp_path,
+) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        with pytest.raises(ValueError, match="does not exist"):
+            await definitions.create("X", 999, rule.id, NOW)
+        return None
+
+    _with_db(tmp_path, "definition-bad-child.db")(_body)
+
+
+def test_definition_create_inactive_child_raises_value_error(
+    tmp_path,
+) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        await children.set_active(child.id, False)
+        with pytest.raises(ValueError, match="inactive"):
+            await definitions.create("X", child.id, rule.id, NOW)
+        return None
+
+    _with_db(tmp_path, "definition-inactive-child.db")(_body)
+
+
+def test_definition_create_unknown_rule_raises_value_error(
+    tmp_path,
+) -> None:
+    async def _body(database, rules, definitions, children, child):
+        with pytest.raises(ValueError, match="does not exist"):
+            await definitions.create("X", child.id, 999, NOW)
+        return None
+
+    _with_db(tmp_path, "definition-bad-rule.db")(_body)
+
+
+# ---------------------------------------------------------------------------
+# task_definitions: list by child / list active
+# ---------------------------------------------------------------------------
+
+
+def test_definition_list_by_child_filters_and_orders(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        other_child = await children.create("Bo", NOW)
+        await definitions.create("A", child.id, rule.id, NOW)
+        await definitions.create("B", child.id, rule.id, NOW)
+        await definitions.create("C", other_child.id, rule.id, NOW)
+        mine = await definitions.list_by_child(child.id)
+        assert [d.title for d in mine] == ["B", "A"]
+        return mine
+
+    _with_db(tmp_path, "definition-list-by-child.db")(_body)
+
+
+def test_definition_list_by_child_missing_child_returns_empty(
+    tmp_path,
+) -> None:
+    async def _body(database, rules, definitions, children, child):
+        return await definitions.list_by_child(999)
+
+    assert _with_db(tmp_path, "definition-list-missing.db")(_body) == []
+
+
+def test_definition_list_active_excludes_inactive(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        first = await definitions.create("A", child.id, rule.id, NOW)
+        await definitions.create("B", child.id, rule.id, NOW)
+        await definitions.set_active(first.id, False)
+        active = await definitions.list_active()
+        assert [d.title for d in active] == ["B"]
+        assert all(d.is_active for d in active)
+        return active
+
+    _with_db(tmp_path, "definition-list-active.db")(_body)
+
+
+# ---------------------------------------------------------------------------
+# task_definitions: update / set_active / set_assignee
+# ---------------------------------------------------------------------------
+
+
+def test_definition_update_fields_round_trip(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        definition = await definitions.create(
+            "A", child.id, rule.id, NOW, due_time="08:00"
+        )
+        assert await definitions.update(
+            definition.id,
+            title="A2",
+            description="New",
+            icon="mdi:star",
+            due_time="09:00",
+        ) == 1
+        fetched = await definitions.get(definition.id)
+        assert fetched.title == "A2"
+        assert fetched.description == "New"
+        assert fetched.icon == "mdi:star"
+        assert fetched.due_time == "09:00"
+        return fetched
+
+    _with_db(tmp_path, "definition-update.db")(_body)
+
+
+def test_definition_update_cannot_change_assignee_or_active(
+    tmp_path,
+) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        other = await children.create("Bo", NOW)
+        definition = await definitions.create("A", child.id, rule.id, NOW)
+        # title= / child_id= / is_active= are NOT update() parameters;
+        # this asserts the API shape: reassignment and activation have
+        # their own explicit methods.
+        import inspect
+
+        parameters = inspect.signature(definitions.update).parameters
+        assert set(parameters) == {
+            "definition_id",
+            "title",
+            "description",
+            "icon",
+            "due_time",
+        }
+        assert await definitions.update(
+            definition.id, title="A2"
+        ) == 1
+        fetched = await definitions.get(definition.id)
+        assert fetched.child_id == child.id
+        assert fetched.is_active is True
+        return fetched
+
+    _with_db(tmp_path, "definition-update-shape.db")(_body)
+
+
+def test_definition_update_no_fields_returns_zero(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        definition = await definitions.create("A", child.id, rule.id, NOW)
+        return await definitions.update(definition.id)
+
+    assert _with_db(tmp_path, "definition-update-empty.db")(_body) == 0
+
+
+def test_definition_update_can_clear_optional_fields(tmp_path) -> None:
+    """Explicit None removes description/icon/due_time; omitted keeps."""
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        definition = await definitions.create(
+            "A",
+            child.id,
+            rule.id,
+            NOW,
+            description="d",
+            icon="mdi:x",
+            due_time="08:00",
+        )
+        await definitions.update(definition.id, title="A2")
+        kept = await definitions.get(definition.id)
+        assert kept.description == "d"
+        assert kept.icon == "mdi:x"
+        await definitions.update(
+            definition.id, description=None, icon=None, due_time=None
+        )
+        cleared = await definitions.get(definition.id)
+        assert cleared.description is None
+        assert cleared.icon is None
+        assert cleared.due_time is None
+        assert cleared.title == "A2"
+        return cleared
+
+    _with_db(tmp_path, "definition-update-clear.db")(_body)
+
+
+def test_definition_update_missing_returns_zero(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        return await definitions.update(999, title="X")
+
+    assert _with_db(tmp_path, "definition-update-missing.db")(_body) == 0
+
+
+def test_definition_set_active_round_trip(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        definition = await definitions.create("A", child.id, rule.id, NOW)
+        assert await definitions.set_active(definition.id, False) == 1
+        fetched = await definitions.get(definition.id)
+        assert fetched.is_active is False
+        assert await definitions.set_active(definition.id, True) == 1
+        fetched = await definitions.get(definition.id)
+        assert fetched.is_active is True
+        return fetched
+
+    _with_db(tmp_path, "definition-set-active.db")(_body)
+
+
+def test_definition_set_active_missing_returns_zero(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        return await definitions.set_active(999, False)
+
+    assert _with_db(tmp_path, "definition-set-active-missing.db")(_body) == 0
+
+
+def test_definition_no_delete_method_exists(tmp_path) -> None:
+    """Guardrail: definitions are deactivated, never hard-deleted."""
+    async def _body(database, rules, definitions, children, child):
+        import inspect
+
+        methods = {
+            name
+            for name, _ in inspect.getmembers(
+                TaskDefinitionsDao, inspect.isfunction
+            )
+        }
+        assert "delete" not in methods
+        assert not any(name.startswith("delete") for name in methods)
+        return methods
+
+    _with_db(tmp_path, "definition-no-delete.db")(_body)
+
+
+def test_definition_set_assignee_round_trip(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        other = await children.create("Bo", NOW)
+        definition = await definitions.create("A", child.id, rule.id, NOW)
+        assert await definitions.set_assignee(definition.id, other.id) == 1
+        fetched = await definitions.get(definition.id)
+        assert fetched.child_id == other.id
+        return fetched
+
+    _with_db(tmp_path, "definition-set-assignee.db")(_body)
+
+
+def test_definition_set_assignee_inactive_child_raises(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        other = await children.create("Bo", NOW)
+        definition = await definitions.create("A", child.id, rule.id, NOW)
+        await children.set_active(other.id, False)
+        with pytest.raises(ValueError, match="inactive"):
+            await definitions.set_assignee(definition.id, other.id)
+        fetched = await definitions.get(definition.id)
+        assert fetched.child_id == child.id, (
+            "failed reassignment must leave the current assignee intact"
+        )
+        return fetched
+
+    _with_db(tmp_path, "definition-assignee-inactive.db")(_body)
+
+
+def test_definition_set_assignee_unknown_child_raises(tmp_path) -> None:
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        definition = await definitions.create("A", child.id, rule.id, NOW)
+        with pytest.raises(ValueError, match="does not exist"):
+            await definitions.set_assignee(definition.id, 999)
+        return None
+
+    _with_db(tmp_path, "definition-assignee-unknown.db")(_body)
+
+
+def test_definition_set_assignee_missing_definition_returns_zero(
+    tmp_path,
+) -> None:
+    async def _body(database, rules, definitions, children, child):
+        other = await children.create("Bo", NOW)
+        return await definitions.set_assignee(999, other.id)
+
+    assert _with_db(tmp_path, "definition-assignee-missing.db")(_body) == 0
+
+
+def test_definition_set_assignee_leaves_history_rows_alone(
+    tmp_path,
+) -> None:
+    """Reassignment writes the definition row only: any instances that
+    already exist keep their generation-time child_id (Feature 06
+    guardrail 'changes future instances only').
+    """
+    async def _body(database, rules, definitions, children, child):
+        rule = await rules.create("daily", "2026-09-14")
+        other = await children.create("Bo", NOW)
+        definition = await definitions.create("A", child.id, rule.id, NOW)
+        # Simulate an already-generated instance (materialization owns
+        # this table, but its row shape proves reassignment isolation).
+        await database.execute(
+            "INSERT INTO task_instances (definition_id, child_id, "
+            "due_date, generated_at) VALUES (?, ?, ?, ?)",
+            (definition.id, child.id, "2026-09-15", NOW),
+        )
+        await definitions.set_assignee(definition.id, other.id)
+        row = await database.fetch_one(
+            "SELECT child_id FROM task_instances WHERE definition_id = ?",
+            (definition.id,),
+        )
+        assert row == (child.id,)
+        return None
+
+    _with_db(tmp_path, "definition-assignee-history.db")(_body)
+
+
+# ---------------------------------------------------------------------------
+# No SQL for these tables outside the DAO module
+# ---------------------------------------------------------------------------
+
+
+def test_rules_and_definitions_sql_lives_only_in_dao_module() -> None:
+    """Guardrail: schedule_rules/task_definitions SQL may appear only
+    in the DAO modules (and the schema DDL declarations).  RECURSIVE
+    scan of package and tests, FROM/INTO/UPDATE/DELETE FROM/JOIN
+    pattern, with exact repo-relative exclusions justified by role:
+    schema.py declares the DDL, migrations.py applies it, and the
+    test files for exactly those layers verify DDL, not data paths.
+    """
+    import re
+    from pathlib import Path
+
+    package = Path(
+        __import__(
+            "custom_components.nestquest", fromlist=["__file__"]
+        ).__file__
+    ).parent
+    repo_root = package.parent.parent
+    scan_roots = [package, repo_root / "tests"]
+
+    allowed = {
+        "custom_components/nestquest/dao_rules.py",  # this DAO
+        "custom_components/nestquest/schema.py",  # declares the DDL
+        "custom_components/nestquest/migrations.py",  # applies the DDL
+        "tests/test_schema.py",  # tests the DDL
+        "tests/test_migrations.py",  # tests migration application
+        "tests/test_dao_rules.py",  # this file, scanned separately
+        # dao_children.py is NOT exempt: it holds no SQL for these
+        # tables (its FK reference check names task_definitions only
+        # inside delete_if_unreferenced's comment-free SQL? No — that
+        # lives HERE in dao_rules).  Any future leak there must fail.
+    }
+    sql_pattern = re.compile(
+        r"(FROM|INTO|UPDATE|DELETE\s+FROM|JOIN)\s+[`'\"]*(\[)?"
+        r"(schedule_rules|task_definitions)\b",
+        re.IGNORECASE,
+    )
+    offenders: list[str] = []
+    for root in scan_roots:
+        for py in sorted(root.rglob("*.py")):
+            relative = py.relative_to(repo_root).as_posix()
+            if relative in allowed:
+                continue
+            if sql_pattern.search(py.read_text()):
+                offenders.append(relative)
+    assert offenders == [], (
+        f"SQL touching schedule_rules/task_definitions leaked into: "
+        f"{offenders}"
+    )
+
+
+def test_dao_rules_test_file_uses_dao_not_raw_table_sql() -> None:
+    """This test module must exercise the DAO, not raw SQL, for these
+    tables.  Exceptions: the guard functions' own spans (their regexes
+    mention the names), and the reassignment-isolation test, which
+    legitimately touches task_instances — a table this DAO does not
+    own — to prove the guardrail that reassignment leaves it alone.
+    """
+    import ast
+    import re
+    from pathlib import Path
+
+    path = Path(__file__)
+    text = path.read_text()
+    lines = text.splitlines(keepends=True)
+    tree = ast.parse(text)
+    guard_names = {
+        "test_rules_and_definitions_sql_lives_only_in_dao_module",
+        "test_dao_rules_test_file_uses_dao_not_raw_table_sql",
+    }
+    excluded: set[int] = set()
+    for node in ast.walk(tree):
+        if (
+            isinstance(node, (ast.FunctionDef, ast.AsyncFunctionDef))
+            and node.name in guard_names
+        ):
+            excluded.update(
+                range(node.lineno, (node.end_lineno or node.lineno) + 1)
+            )
+
+    remaining = "".join(
+        line
+        for number, line in enumerate(lines, start=1)
+        if number not in excluded
+    )
+    sql_pattern = re.compile(
+        r"(SELECT\s[^\"']*?FROM|INSERT\s+INTO|UPDATE|DELETE\s+FROM|"
+        r"FROM|JOIN)\s+[`'\"]*(\[)?(schedule_rules|task_definitions)\b",
+        re.IGNORECASE,
+    )
+    assert sql_pattern.search(remaining) is None, (
+        "tests must go through the DAO, not raw SQL, for these tables"
+    )

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

@@ -3,6 +3,8 @@ 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
@@ -196,4 +198,61 @@ async def test_setup_awaitable_listener_removal_supported() -> None:
     assert result is True
     assert removed == [True]
     assert hass.data == {}
-    assert entry.runtime_data is None
+    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

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

tests/test_migrations.py

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

@@ -100,11 +100,14 @@ def test_executor_helper_creates_directory(tmp_path) -> None:
     assert nested.is_dir()
 
 
-def test_no_sqlite3_import_in_package() -> None:
+def test_no_sqlite3_import_outside_db_module() -> None:
+    """sqlite3 is banned package-wide except db.py, the connection wrapper."""
     pkg_dir = Path(store.__file__).parent
     py_files = list(pkg_dir.glob("*.py"))
     assert py_files
     for py_file in py_files:
+        if py_file.name == "db.py":
+            continue
         tree = ast.parse(py_file.read_text(encoding="utf-8"), filename=str(py_file))
         for node in ast.walk(tree):
             if isinstance(node, ast.Import):