feat: add schema v1 ddl for presence schedules and overrides

custom_components/nestquest/__init__.py

@@ -11,7 +11,7 @@ from homeassistant.core import HomeAssistant
 
 from .const import DOMAIN, LOGGER
 from .db import NestQuestDatabase
-from .migrations import apply_migrations
+from .schema import SCHEMA_V1_STATEMENTS, async_apply_ddl
 from .store import async_get_db_path
 
 _LOGGER = LOGGER
@@ -52,7 +52,7 @@ async def async_setup_entry(hass: HomeAssistant, entry: ConfigEntry) -> bool:
 
     database = await NestQuestDatabase(hass).open(await async_get_db_path(hass))
     try:
-        await apply_migrations(database)
+        await async_apply_ddl(database, SCHEMA_V1_STATEMENTS)
         remove_update_listener = entry.add_update_listener(_async_update_listener)
     except BaseException:
         await database.close()

custom_components/nestquest/dao_children.py

@@ -1,289 +0,0 @@
-"""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/migrations.py

@@ -1,236 +0,0 @@
-"""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

@@ -85,68 +85,6 @@ 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
 
@@ -271,46 +209,6 @@ SCHEMA_V1_PRESENCE_OVERRIDES_DDL: list[str] = [
     """,
 ]
 
-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,
@@ -318,8 +216,6 @@ SCHEMA_V1_STATEMENTS: list[str] = [
     *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,
 ]
 
 

tests/test_dao_children.py

@@ -1,553 +0,0 @@
-"""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/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
-    }
-    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_migrations.py

@@ -1,677 +0,0 @@
-"""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_schema.py

@@ -10,13 +10,11 @@ from custom_components.nestquest.db import NestQuestDatabase
 from custom_components.nestquest.schema import (
     SCHEMA_V1_ADMIN_USERS_DDL,
     SCHEMA_V1_CHILDREN_DDL,
-    SCHEMA_V1_COMPLETION_EVENTS_DDL,
     SCHEMA_V1_PRESENCE_OVERRIDES_DDL,
     SCHEMA_V1_PRESENCE_SCHEDULES_DDL,
     SCHEMA_V1_SCHEDULE_RULES_DDL,
     SCHEMA_V1_STATEMENTS,
     SCHEMA_V1_TASK_DEFINITIONS_DDL,
-    SCHEMA_V1_TASK_INSTANCES_DDL,
     async_apply_ddl,
 )
 
@@ -164,8 +162,6 @@ def test_schema_v1_statements_compose_the_six_tables() -> None:
         *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,
     ]
 
 
@@ -1401,637 +1397,5 @@ def test_applying_presence_ddl_twice_keeps_rows(tmp_path) -> None:
             row = _run(database.fetch_one(f"SELECT COUNT(*) FROM {table}"))
             counts.append(row[0])
         assert counts == [1, 1]
-    finally:
-        _run(database.close())
-
-
-# ---------------------------------------------------------------------------
-# task_instances and completion_events: helpers
-# ---------------------------------------------------------------------------
-
-
-def _insert_instance(database, definition_id=1, **overrides):
-    """Insert one valid task_instance, returning the instance id.
-
-    Pass ``_OMIT`` as a value to leave that column out of the INSERT so
-    its DDL DEFAULT applies.
-    """
-    values: dict[str, object] = {
-        "definition_id": definition_id,
-        "child_id": 1,
-        "due_date": "2026-09-14",
-        "generated_at": "2026-09-14T00:00:00+00:00",
-    }
-    values.update(overrides)
-    values = {k: v for k, v in values.items() if v is not _OMIT}
-    columns = ", ".join(values)
-    placeholders = ", ".join("?" for _ in values)
-    result = _run(
-        database.execute(
-            f"INSERT INTO task_instances ({columns}) VALUES ({placeholders})",
-            tuple(values.values()),
-        )
-    )
-    assert result.lastrowid is not None
-    return result.lastrowid
-
-
-def _insert_event(database, instance_id=1, **overrides):
-    """Insert one valid completion_event, returning the event id.
-
-    Pass ``_OMIT`` as a value to leave that column out of the INSERT so
-    its DDL DEFAULT applies.
-    """
-    values: dict[str, object] = {
-        "instance_id": instance_id,
-        "child_id": 1,
-        "event_type": "completed",
-        "actor_source": "user",
-        "actor_user_id": "user-1",
-        "occurred_at": "2026-09-14T08:30:00+00:00",
-        "was_on_time": 1,
-    }
-    values.update(overrides)
-    values = {k: v for k, v in values.items() if v is not _OMIT}
-    columns = ", ".join(values)
-    placeholders = ", ".join("?" for _ in values)
-    result = _run(
-        database.execute(
-            f"INSERT INTO completion_events ({columns}) VALUES ({placeholders})",
-            tuple(values.values()),
-        )
-    )
-    assert result.lastrowid is not None
-    return result.lastrowid
-
-
-def _setup_definition(database) -> None:
-    """Create one child, one schedule rule and one task definition."""
-    _child(database)
-    _insert_rule(database)
-    _insert_definition(database, child_id=1, rule_id=1)
-
-
-# ---------------------------------------------------------------------------
-# task_instances and completion_events: DDL shape
-# ---------------------------------------------------------------------------
-
-
-def test_task_instances_table_exists_after_applying_ddl(tmp_path) -> None:
-    database = _open_db(tmp_path / "task-instances.db")
-    try:
-        _apply(database)
-        row = _run(
-            database.fetch_one(
-                "SELECT name FROM sqlite_master WHERE type = 'table' "
-                "AND name = 'task_instances'"
-            )
-        )
-        assert row is not None and row[0] == "task_instances"
-    finally:
-        _run(database.close())
-
-
-def test_completion_events_table_exists_after_applying_ddl(tmp_path) -> None:
-    database = _open_db(tmp_path / "completion-events.db")
-    try:
-        _apply(database)
-        row = _run(
-            database.fetch_one(
-                "SELECT name FROM sqlite_master WHERE type = 'table' "
-                "AND name = 'completion_events'"
-            )
-        )
-        assert row is not None and row[0] == "completion_events"
-    finally:
-        _run(database.close())
-
-
-def test_task_instances_columns_types_and_constraints(tmp_path) -> None:
-    database = _open_db(tmp_path / "task-instances-columns.db")
-    try:
-        _apply(database)
-        columns = _run(database.fetch_all("PRAGMA table_info(task_instances)"))
-        # cid, name, type, notnull, dflt_value, pk
-        assert [(row[1], row[2], row[3], row[4], row[5]) for row in columns] == [
-            ("id", "INTEGER", 0, None, 1),
-            ("definition_id", "INTEGER", 1, None, 0),
-            ("child_id", "INTEGER", 1, None, 0),
-            ("due_date", "TEXT", 1, None, 0),
-            ("due_time", "TEXT", 0, None, 0),
-            ("generated_at", "TEXT", 1, None, 0),
-        ]
-    finally:
-        _run(database.close())
-
-
-def test_completion_events_columns_types_and_constraints(tmp_path) -> None:
-    database = _open_db(tmp_path / "completion-events-columns.db")
-    try:
-        _apply(database)
-        columns = _run(database.fetch_all("PRAGMA table_info(completion_events)"))
-        assert [(row[1], row[2], row[3], row[4], row[5]) for row in columns] == [
-            ("id", "INTEGER", 0, None, 1),
-            ("instance_id", "INTEGER", 1, None, 0),
-            ("child_id", "INTEGER", 1, None, 0),
-            ("event_type", "TEXT", 1, None, 0),
-            ("actor_source", "TEXT", 1, None, 0),
-            ("actor_user_id", "TEXT", 0, None, 0),
-            ("occurred_at", "TEXT", 1, None, 0),
-            ("was_on_time", "INTEGER", 0, None, 0),
-        ]
-    finally:
-        _run(database.close())
-
-
-def test_task_instances_foreign_keys_declared(tmp_path) -> None:
-    database = _open_db(tmp_path / "task-instances-fks.db")
-    try:
-        _apply(database)
-        fks = _run(database.fetch_all("PRAGMA foreign_key_list(task_instances)"))
-        declared = {(row[2], row[3], row[4]) for row in fks}
-        assert declared == {
-            ("task_definitions", "definition_id", "id"),
-            ("children", "child_id", "id"),
-        }
-    finally:
-        _run(database.close())
-
-
-def test_completion_events_foreign_keys_declared(tmp_path) -> None:
-    database = _open_db(tmp_path / "completion-events-fks.db")
-    try:
-        _apply(database)
-        fks = _run(database.fetch_all("PRAGMA foreign_key_list(completion_events)"))
-        declared = {(row[2], row[3], row[4]) for row in fks}
-        assert declared == {
-            ("task_instances", "instance_id", "id"),
-            ("children", "child_id", "id"),
-        }
-    finally:
-        _run(database.close())
-
-
-def test_task_instances_unique_index_on_definition_and_date(tmp_path) -> None:
-    database = _open_db(tmp_path / "task-instances-unique.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        rows = _run(
-            database.fetch_all(
-                "SELECT name, sql FROM sqlite_master WHERE type = 'index' "
-                "AND tbl_name = 'task_instances'"
-            )
-        )
-        implicit = [(name, sql) for name, sql in rows if sql is None]
-        assert len(implicit) == 1, (
-            "the (definition_id, due_date) UNIQUE constraint must be backed "
-            "by exactly one implicit unique index"
-        )
-        columns = _run(
-            database.fetch_all(
-                f"PRAGMA index_info('{implicit[0][0]}')"
-            )
-        )
-        assert [row[2] for row in columns] == ["definition_id", "due_date"]
-        # The implicit index is genuinely unique: UNIQUE(...) is the only
-        # constraint SQLite backs with a sql-less autoindex on this table.
-        row = _run(
-            database.fetch_one(
-                "SELECT COUNT(DISTINCT name) FROM pragma_index_list"
-                "('task_instances') WHERE \"unique\" = 1"
-            )
-        )
-        assert row == (1,)
-    finally:
-        _run(database.close())
-
-
-# ---------------------------------------------------------------------------
-# task_instances: constraint enforcement
-# ---------------------------------------------------------------------------
-
-
-def test_task_instances_valid_insert_round_trips(tmp_path) -> None:
-    database = _open_db(tmp_path / "task-instances-valid.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1, child_id=1)
-        row = _run(
-            database.fetch_one(
-                "SELECT definition_id, child_id, due_date, due_time, generated_at "
-                "FROM task_instances WHERE id = ?",
-                (instance_id,),
-            )
-        )
-        assert row == (1, 1, "2026-09-14", None, "2026-09-14T00:00:00+00:00")
-    finally:
-        _run(database.close())
-
-
-def test_task_instances_due_time_round_trips(tmp_path) -> None:
-    database = _open_db(tmp_path / "task-instances-due-time.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(
-            database, definition_id=1, due_time="17:30"
-        )
-        row = _run(
-            database.fetch_one(
-                "SELECT due_time FROM task_instances WHERE id = ?",
-                (instance_id,),
-            )
-        )
-        assert row == ("17:30",)
-    finally:
-        _run(database.close())
-
-
-def test_task_instances_duplicate_definition_date_fails(tmp_path) -> None:
-    database = _open_db(tmp_path / "task-instances-duplicate.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        _insert_instance(database, definition_id=1, due_date="2026-09-14")
-        with pytest.raises(sqlite3.IntegrityError, match="UNIQUE"):
-            _insert_instance(database, definition_id=1, due_date="2026-09-14")
-    finally:
-        _run(database.close())
-
-
-def test_task_instances_same_date_different_definitions_allowed(
-    tmp_path,
-) -> None:
-    database = _open_db(tmp_path / "task-instances-multi-def.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        _run(
-            database.execute(
-                "INSERT INTO children (display_name, created_at) VALUES (?, ?)",
-                ("Bo", "2026-09-13T00:00:00+00:00"),
-            )
-        )
-        rule_id = _insert_rule(database)
-        _insert_definition(database, child_id=2, rule_id=rule_id)
-        _insert_instance(database, definition_id=1, due_date="2026-09-14")
-        _insert_instance(database, definition_id=2, due_date="2026-09-14")
-        row = _run(database.fetch_one("SELECT COUNT(*) FROM task_instances"))
-        assert row == (2,)
-    finally:
-        _run(database.close())
-
-
-def test_task_instances_same_definition_different_dates_allowed(tmp_path) -> None:
-    database = _open_db(tmp_path / "task-instances-multi-date.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        _insert_instance(database, definition_id=1, due_date="2026-09-14")
-        _insert_instance(database, definition_id=1, due_date="2026-09-15")
-        row = _run(database.fetch_one("SELECT COUNT(*) FROM task_instances"))
-        assert row == (2,)
-    finally:
-        _run(database.close())
-
-
-def test_task_instances_unknown_definition_fails(tmp_path) -> None:
-    database = _open_db(tmp_path / "task-instances-bad-def.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        with pytest.raises(sqlite3.IntegrityError, match="FOREIGN KEY"):
-            _insert_instance(database, definition_id=999)
-    finally:
-        _run(database.close())
-
-
-def test_task_instances_unknown_child_fails(tmp_path) -> None:
-    database = _open_db(tmp_path / "task-instances-bad-child.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        with pytest.raises(sqlite3.IntegrityError, match="FOREIGN KEY"):
-            _insert_instance(database, definition_id=1, child_id=999)
-    finally:
-        _run(database.close())
-
-
-def test_task_instances_due_date_not_null_enforced(tmp_path) -> None:
-    database = _open_db(tmp_path / "task-instances-due-null.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        with pytest.raises(sqlite3.IntegrityError, match="NOT NULL"):
-            _insert_instance(database, definition_id=1, due_date=None)
-    finally:
-        _run(database.close())
-
-
-def test_task_instances_generated_at_not_null_enforced(tmp_path) -> None:
-    database = _open_db(tmp_path / "task-instances-generated-null.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        with pytest.raises(sqlite3.IntegrityError, match="NOT NULL"):
-            _insert_instance(database, definition_id=1, generated_at=None)
-    finally:
-        _run(database.close())
-
-
-# ---------------------------------------------------------------------------
-# completion_events: constraint enforcement
-# ---------------------------------------------------------------------------
-
-
-def test_completion_events_valid_user_completion_round_trips(tmp_path) -> None:
-    database = _open_db(tmp_path / "completion-events-valid.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        event_id = _insert_event(
-            database, instance_id=instance_id, child_id=1
-        )
-        row = _run(
-            database.fetch_one(
-                "SELECT instance_id, child_id, event_type, actor_source, "
-                "actor_user_id, occurred_at, was_on_time "
-                "FROM completion_events WHERE id = ?",
-                (event_id,),
-            )
-        )
-        assert row == (
-            instance_id,
-            1,
-            "completed",
-            "user",
-            "user-1",
-            "2026-09-14T08:30:00+00:00",
-            1,
-        )
-    finally:
-        _run(database.close())
-
-
-def test_completion_events_valid_panel_completion_round_trips(tmp_path) -> None:
-    database = _open_db(tmp_path / "completion-events-panel.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        event_id = _insert_event(
-            database,
-            instance_id=instance_id,
-            actor_source="panel",
-            actor_user_id=None,
-            was_on_time=0,
-        )
-        row = _run(
-            database.fetch_one(
-                "SELECT actor_source, actor_user_id, was_on_time "
-                "FROM completion_events WHERE id = ?",
-                (event_id,),
-            )
-        )
-        assert row == ("panel", None, 0)
-    finally:
-        _run(database.close())
-
-
-def test_completion_events_valid_uncompleted_round_trips(tmp_path) -> None:
-    database = _open_db(tmp_path / "completion-events-uncompleted.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        event_id = _insert_event(
-            database,
-            instance_id=instance_id,
-            event_type="uncompleted",
-            actor_source="user",
-            was_on_time=_OMIT,
-        )
-        row = _run(
-            database.fetch_one(
-                "SELECT event_type, actor_source, was_on_time "
-                "FROM completion_events WHERE id = ?",
-                (event_id,),
-            )
-        )
-        assert row == ("uncompleted", "user", None)
-    finally:
-        _run(database.close())
-
-
-def test_completion_events_uncompleted_with_on_time_flag_allowed(
-    tmp_path,
-) -> None:
-    database = _open_db(tmp_path / "completion-events-uncompleted-ontime.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        event_id = _insert_event(
-            database,
-            instance_id=instance_id,
-            event_type="uncompleted",
-            was_on_time=1,
-        )
-        row = _run(
-            database.fetch_one(
-                "SELECT was_on_time FROM completion_events WHERE id = ?",
-                (event_id,),
-            )
-        )
-        assert row == (1,)
-    finally:
-        _run(database.close())
-
-
-def test_completion_events_unknown_instance_fails(tmp_path) -> None:
-    database = _open_db(tmp_path / "completion-events-bad-instance.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        with pytest.raises(sqlite3.IntegrityError, match="FOREIGN KEY"):
-            _insert_event(database, instance_id=999)
-    finally:
-        _run(database.close())
-
-
-def test_completion_events_unknown_child_fails(tmp_path) -> None:
-    database = _open_db(tmp_path / "completion-events-bad-child.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        with pytest.raises(sqlite3.IntegrityError, match="FOREIGN KEY"):
-            _insert_event(database, instance_id=instance_id, child_id=999)
-    finally:
-        _run(database.close())
-
-
-@pytest.mark.parametrize("bad_event_type", ["missed", "COMPLETE", ""])
-def test_completion_events_event_type_check_enforced(
-    tmp_path, bad_event_type
-) -> None:
-    database = _open_db(tmp_path / f"completion-events-type-{hash(bad_event_type)}.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
-            _insert_event(
-                database, instance_id=instance_id, event_type=bad_event_type
-            )
-    finally:
-        _run(database.close())
-
-
-@pytest.mark.parametrize("bad_source", ["kiosk", "system", "USER", ""])
-def test_completion_events_actor_source_check_enforced(
-    tmp_path, bad_source
-) -> None:
-    database = _open_db(tmp_path / f"completion-events-source-{hash(bad_source)}.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
-            _insert_event(
-                database, instance_id=instance_id, actor_source=bad_source
-            )
-    finally:
-        _run(database.close())
-
-
-@pytest.mark.parametrize(
-    ("actor_source", "actor_user_id"),
-    [
-        ("user", None),  # user without id
-        ("panel", "user-1"),  # panel with id
-    ],
-)
-def test_completion_events_actor_pair_coherence_enforced(
-    tmp_path, actor_source, actor_user_id
-) -> None:
-    database = _open_db(tmp_path / f"completion-events-actor-{actor_source}.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
-            _insert_event(
-                database,
-                instance_id=instance_id,
-                actor_source=actor_source,
-                actor_user_id=actor_user_id,
-            )
-    finally:
-        _run(database.close())
-
-
-@pytest.mark.parametrize("bad_was_on_time", ["abc", 1.5, 2])
-def test_completion_events_was_on_time_non_integer_or_out_of_range_fails(
-    tmp_path, bad_was_on_time
-) -> None:
-    database = _open_db(tmp_path / f"completion-events-ontime-{type(bad_was_on_time).__name__}.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
-            _insert_event(
-                database, instance_id=instance_id, was_on_time=bad_was_on_time
-            )
-    finally:
-        _run(database.close())
-
-
-def test_completion_events_completed_requires_was_on_time(tmp_path) -> None:
-    database = _open_db(tmp_path / "completion-events-completed-ontime-null.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        with pytest.raises(sqlite3.IntegrityError, match="CHECK"):
-            _insert_event(
-                database,
-                instance_id=instance_id,
-                event_type="completed",
-                was_on_time=_OMIT,
-            )
-    finally:
-        _run(database.close())
-
-
-def test_completion_events_occurred_at_not_null_enforced(tmp_path) -> None:
-    database = _open_db(tmp_path / "completion-events-occurred-null.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        with pytest.raises(sqlite3.IntegrityError, match="NOT NULL"):
-            _insert_event(
-                database, instance_id=instance_id, occurred_at=None
-            )
-    finally:
-        _run(database.close())
-
-
-def test_completion_events_multiple_events_per_instance_allowed(
-    tmp_path,
-) -> None:
-    database = _open_db(tmp_path / "completion-events-multi.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        _insert_event(
-            database, instance_id=instance_id, event_type="completed",
-            occurred_at="2026-09-14T08:30:00+00:00",
-        )
-        _insert_event(
-            database, instance_id=instance_id, event_type="uncompleted",
-            actor_source="user", was_on_time=1,
-            occurred_at="2026-09-14T09:00:00+00:00",
-        )
-        _insert_event(
-            database, instance_id=instance_id, event_type="completed",
-            occurred_at="2026-09-14T09:15:00+00:00",
-        )
-        row = _run(database.fetch_one("SELECT COUNT(*) FROM completion_events"))
-        assert row == (3,)
-    finally:
-        _run(database.close())
-
-
-# ---------------------------------------------------------------------------
-# Idempotence across the full eight-table v1 list
-# ---------------------------------------------------------------------------
-
-
-def test_applying_full_ddl_twice_keeps_rows(tmp_path) -> None:
-    database = _open_db(tmp_path / "idempotent-full.db")
-    try:
-        _apply(database)
-        _setup_definition(database)
-        instance_id = _insert_instance(database, definition_id=1)
-        _insert_event(database, instance_id=instance_id)
-        _apply(database)
-        counts = []
-        for table in (
-            "children",
-            "schedule_rules",
-            "task_definitions",
-            "task_instances",
-            "completion_events",
-        ):
-            row = _run(database.fetch_one(f"SELECT COUNT(*) FROM {table}"))
-            counts.append(row[0])
-        assert counts == [1, 1, 1, 1, 1]
     finally:
         _run(database.close())