Review fix: gate the override race test inside A's transaction

Review round 1 (Codex gpt-5.6-terra, LOW): the racing test completed
synchronously and proved nothing. The executor now gates task A's
INSERT — inside its open transaction, after its overlap probe — so
task B queues on the connection lock, re-runs the probe against A's
committed row, and is rejected; exactly one lands. Suite: 990 passed.

custom_components/nestquest/admin_allowlist.py

@@ -91,6 +91,23 @@ async def set_admin_ids(
                 await dao.add(user_id, _now_stamp())
 
 
+async def is_admin(
+    database: NestQuestDatabase, user_id: object
+) -> bool:
+    """Resolve an HA user ID against the allowlist — FAILS CLOSED.
+
+    True only for an allowlisted user id.  Everything else is False:
+    a non-allowlisted id, an unknown id, a None or non-string value
+    (a service call with no user context), and an empty allowlist
+    (nobody is admin rather than everybody).  The Feature 09 gate
+    wraps every admin-only service with this verdict; it never raises
+    for malformed input — malformed means not admin.
+    """
+    if not isinstance(user_id, str) or not user_id:
+        return False
+    return await AdminUsersDao(database).exists(user_id)
+
+
 async def seed_setup_admin(
     database: NestQuestDatabase,
     *,

custom_components/nestquest/dao_presence.py

@@ -186,6 +186,15 @@ class PresenceOverridesDao:
         The child must exist (validated atomically under the
         connection lock).  Date shape is validated strictly, and the
         schema CHECK rejects end < start.
+
+        Overlap policy (Feature 05): an override for a child may not
+        overlap another override of the SAME child — two different
+        present/absent answers for one date are unresolvable.  The
+        overlap check (inclusive boundaries) runs inside the same
+        transaction as the INSERT, so a racing create cannot slip a
+        conflicting override between the check and the write; a
+        conflict raises ValueError naming the conflicting override's
+        id and range.
         """
         _validate_date(start_date, "start_date")
         _validate_date(end_date, "end_date")
@@ -196,6 +205,29 @@ class PresenceOverridesDao:
                 )
                 if child is None:
                     raise ValueError(f"child {child_id} does not exist")
+                # Only a well-ordered range has overlap semantics:
+                # inverted ranges fall through to the INSERT and hit
+                # the schema's end>=start CHECK, preserving the
+                # IntegrityError contract the matrix tests rely on.
+                if end_date >= start_date:
+                    conflicting = await self._database.fetch_one(
+                        f"SELECT {_OVERRIDE_COLUMNS} "
+                        "FROM presence_overrides "
+                        "WHERE child_id = ? AND end_date >= ? "
+                        "AND start_date <= ? ORDER BY id LIMIT 1",
+                        (child_id, start_date, end_date),
+                    )
+                else:
+                    conflicting = None
+                if conflicting is not None:
+                    conflict = _override_from_row(conflicting)
+                    raise ValueError(
+                        f"override for child {child_id} "
+                        f"[{start_date}, {end_date}] overlaps existing "
+                        f"override {conflict.id} "
+                        f"[{conflict.start_date}, {conflict.end_date}] "
+                        f"(is_present={conflict.is_present})"
+                    )
                 result = await self._database.execute(
                     "INSERT INTO presence_overrides (child_id, "
                     "start_date, end_date, is_present, note) "

custom_components/nestquest/presence.py

@@ -0,0 +1,377 @@
+"""The presence and custody model (Feature 05).
+
+PURE module: no Home Assistant imports, no database access, no
+timezone logic — it operates on plain calendar dates only (feature
+guardrail).  The later engine (cycle position, ``is_present``,
+overrides, previews) builds directly on this model.
+
+A :class:`PresenceSchedule` answers "on which weekdays of which cycle
+week is this child at this house?" for an N-week repeating custody
+pattern.  Two of the household's three children follow a two-week
+rotation; a child with NO schedule row is present every day (that
+default lives in the engine, not here).
+
+Pattern encoding: the model's canonical serialization is the SAME
+pipe-separated CSV the database stores (see schema.py's
+``presence_schedules.pattern``): segment ``i`` (week index ``i``) is a
+comma-separated list of present weekdays, 0 = Monday .. 6 = Sunday,
+e.g. ``'0,2,4|1,3'`` = Mon/Wed/Fri in week 0, Tue/Thu in week 1.  An
+empty segment means absent every day of that week.  Encoding sorts
+each segment's weekdays and uses the exact segment order, so
+``decode(encode(schedule))`` round-trips losslessly and two schedules
+with equal patterns produce equal strings.
+
+Anchor-date policy: ``anchor_date`` pins the cycle.  Cycle position is
+computed from anchor-date arithmetic ONLY — never ISO week numbers or
+week parity (D-004): 53-week years silently invert parity on January
+1st.  The arithmetic lives in the engine task; this module validates
+that the anchor is a strict calendar date.
+
+Validation policy: construction is total — every reject happens in
+``__init__`` so no invalid schedule can ever exist:
+- ``cycle_length_weeks`` must be an integer 1..4.  The 4 cap matches
+  the database CHECK (schema.py) and keeps anchor arithmetic and the
+  encoded pattern human-readable; revisit both together if a longer
+  cycle is ever needed.
+- ``pattern`` must cover week indices 0..cycle_length_weeks-1 exactly
+  (no gaps, no extra weeks) with sets of integers 0..6 (bools
+  rejected: True would silently mean Monday).
+- ``anchor_date`` must be a strict ISO YYYY-MM-DD calendar date (or a
+  ``datetime.date``).
+"""
+from __future__ import annotations
+
+import datetime
+from dataclasses import dataclass
+from types import MappingProxyType
+
+#: The schema's documented cap for cycle_length_weeks (schema.py keeps
+#: the pattern human-readable and the anchor arithmetic small).
+MAX_CYCLE_LENGTH_WEEKS = 4
+
+_DATE_FORMAT = "%Y-%m-%d"
+
+
+def _parse_date(value: object, field_name: str) -> datetime.date:
+    """Accept a datetime.date or a strict YYYY-MM-DD string.
+
+    ``datetime.datetime`` is rejected explicitly: it SUBCLASSES
+    datetime.date, so a bare isinstance check would store a value with
+    a time component and every later calendar-date comparison (e.g.
+    ``PresenceOverride.covers``) would raise TypeError.
+    """
+    if isinstance(value, datetime.datetime):
+        raise ValueError(
+            f"{field_name} must be a plain calendar date, not a "
+            f"datetime with a time component: {value!r}"
+        )
+    if isinstance(value, datetime.date):
+        return value
+    if not isinstance(value, str):
+        raise ValueError(
+            f"{field_name} must be a datetime.date or an ISO date string, "
+            f"got {value!r}"
+        )
+    try:
+        parsed = datetime.datetime.strptime(value, _DATE_FORMAT).date()
+    except (TypeError, ValueError):
+        raise ValueError(
+            f"{field_name} must be an ISO date (YYYY-MM-DD), got {value!r}"
+        ) from None
+    if parsed.isoformat() != value:
+        raise ValueError(
+            f"{field_name} must be a strict YYYY-MM-DD date, got {value!r}"
+        )
+    return parsed
+
+
+def _validate_week_index(week: object, cycle_length_weeks: int) -> int:
+    """Reject non-int week indices (bools included) and out-of-cycle."""
+    if isinstance(week, bool) or not isinstance(week, int):
+        raise ValueError(
+            f"pattern week index must be an integer, got {week!r}"
+        )
+    if not 0 <= week < cycle_length_weeks:
+        raise ValueError(
+            f"pattern week index must be in 0..{cycle_length_weeks - 1}, "
+            f"got {week}"
+        )
+    return week
+
+
+def _validate_weekday_set(week: int, weekdays: object) -> frozenset[int]:
+    """Validate one week's present-weekday set."""
+    if isinstance(weekdays, (str, bytes)) or weekdays is None:
+        raise ValueError(
+            f"pattern[{week}] must be an iterable of weekday ints 0..6, "
+            f"got {weekdays!r}"
+        )
+    try:
+        members = list(weekdays)
+    except TypeError:
+        raise ValueError(
+            f"pattern[{week}] must be an iterable of weekday ints 0..6, "
+            f"got {weekdays!r}"
+        ) from None
+    for member in members:
+        if isinstance(member, bool) or not isinstance(member, int):
+            raise ValueError(
+                f"pattern[{week}] weekday must be an int 0..6, got {member!r}"
+            )
+        if not 0 <= member <= 6:
+            raise ValueError(
+                f"pattern[{week}] weekday must be in 0..6 (Monday=0), "
+                f"got {member}"
+            )
+    return frozenset(members)
+
+
+def _validate_cycle_length(value: object) -> int:
+    """Reject non-int and out-of-range cycle lengths (bools included)."""
+    if isinstance(value, bool) or not isinstance(value, int):
+        raise ValueError(
+            f"cycle_length_weeks must be an integer, got {value!r}"
+        )
+    if not 1 <= value <= MAX_CYCLE_LENGTH_WEEKS:
+        raise ValueError(
+            f"cycle_length_weeks must be between 1 and "
+            f"{MAX_CYCLE_LENGTH_WEEKS}, got {value}"
+        )
+    return value
+
+
+def _validate_child_id(value: object) -> int:
+    """Reject non-int child ids (bools would silently address child 1)."""
+    if isinstance(value, bool) or not isinstance(value, int):
+        raise ValueError(f"child_id must be an integer, got {value!r}")
+    return value
+
+
+@dataclass(frozen=True, init=False)
+class PresenceSchedule:
+    """One child's N-week repeating presence pattern.
+
+    ``pattern`` maps each week index of the cycle (``0`` .. ``n-1``)
+    to the set of present weekdays (ints, Monday=0).  A week with an
+    empty set means the child is absent every day of that week — which
+    is distinct from having no schedule at all (the engine treats a
+    missing schedule as present every day).
+    """
+
+    child_id: int
+    cycle_length_weeks: int
+    anchor_date: datetime.date
+    pattern: MappingProxyType  # week index -> frozenset of weekdays
+
+    def __init__(
+        self,
+        child_id: int,
+        cycle_length_weeks: int,
+        anchor_date: object,
+        pattern: dict[int, object],
+    ) -> None:
+        object.__setattr__(self, "child_id", _validate_child_id(child_id))
+        cycle = _validate_cycle_length(cycle_length_weeks)
+        object.__setattr__(self, "cycle_length_weeks", cycle)
+        object.__setattr__(
+            self, "anchor_date", _parse_date(anchor_date, "anchor_date")
+        )
+        if not isinstance(pattern, dict):
+            raise ValueError(
+                "pattern must map week index to a set of present "
+                f"weekdays, got {pattern!r}"
+            )
+        normalised: dict[int, frozenset[int]] = {}
+        for week, weekdays in pattern.items():
+            index = _validate_week_index(week, cycle)
+            normalised[index] = _validate_weekday_set(index, weekdays)
+        missing = [
+            week for week in range(cycle) if week not in normalised
+        ]
+        if missing:
+            raise ValueError(
+                f"pattern must cover week indices 0..{cycle - 1}; "
+                f"missing {missing}"
+            )
+        # Frozen must mean frozen: a bare dict would let callers inject
+        # unvalidated weekdays (or drop required weeks) AFTER
+        # construction, bypassing total validation.  The mapping
+        # proxy exposes the validated contents read-only; the
+        # per-week sets are already frozensets.
+        object.__setattr__(self, "pattern", MappingProxyType(normalised))
+
+    def encode(self) -> str:
+        """Serialize to the database's pipe-separated CSV shape.
+
+        Segments appear in week order; each segment is the week's
+        present weekdays as a sorted comma-separated list (an empty set
+        encodes as an empty segment).  Two schedules with equal
+        patterns encode identically.
+        """
+        segments = [
+            ",".join(str(day) for day in sorted(self.pattern[week]))
+            for week in range(self.cycle_length_weeks)
+        ]
+        return "|".join(segments)
+
+    @classmethod
+    def decode(
+        cls,
+        child_id: int,
+        anchor_date: object,
+        encoded: str,
+    ) -> "PresenceSchedule":
+        """Build a schedule from its encoded pattern.
+
+        The cycle length is derived from the segment count, mirroring
+        the database's segment-count CHECK.  Decoding is strict: wrong
+        shapes, non-numeric or out-of-range weekdays are rejected —
+        the database CHECKs make such rows unrepresentable, and this
+        is the same contract on the model side.  A segment count above
+        the documented cycle cap is rejected by the ordinary
+        construction validation.
+        """
+        if not isinstance(encoded, str):
+            raise ValueError(
+                f"encoded pattern must be a string, got {encoded!r}"
+            )
+        segments = encoded.split("|")
+        pattern: dict[int, set[int]] = {}
+        for week, segment in enumerate(segments):
+            weekdays: set[int] = set()
+            if segment:
+                for part in segment.split(","):
+                    if len(part) != 1 or part not in "0123456":
+                        raise ValueError(
+                            f"encoded segment {week} carries an invalid "
+                            f"weekday {part!r} (single digits 0-6 only)"
+                        )
+                    weekdays.add(int(part))
+            pattern[week] = weekdays
+        return cls(child_id, len(segments), anchor_date, pattern)
+
+
+@dataclass(frozen=True, init=False)
+class PresenceOverride:
+    """A date-specific present/absent flag beating any pattern.
+
+    Covers a single date (``end_date == start_date``) or an inclusive
+    range.  A concrete range is the storage shape
+    (``presence_overrides.end_date`` is NOT NULL), so the model
+    requires both bounds and validates start <= end.  Construction and
+    validation live here so the engine can trust every instance.
+    """
+
+    child_id: int
+    start_date: datetime.date
+    end_date: datetime.date
+    is_present: bool
+    note: str | None
+
+    def __init__(
+        self,
+        child_id: int,
+        start_date: object,
+        end_date: object,
+        is_present: bool,
+        note: str | None = None,
+    ) -> None:
+        object.__setattr__(self, "child_id", _validate_child_id(child_id))
+        start = _parse_date(start_date, "start_date")
+        end = _parse_date(end_date, "end_date")
+        if end < start:
+            raise ValueError(
+                f"end_date must be on or after start_date, got "
+                f"{end.isoformat()} < {start.isoformat()}"
+            )
+        object.__setattr__(self, "start_date", start)
+        object.__setattr__(self, "end_date", end)
+        if not isinstance(is_present, bool):
+            raise ValueError(
+                f"is_present must be a real bool, got {is_present!r}"
+            )
+        object.__setattr__(self, "is_present", is_present)
+        if note is not None and not isinstance(note, str):
+            raise ValueError(f"note must be a string or None, got {note!r}")
+        object.__setattr__(self, "note", note)
+
+    def covers(self, date: datetime.date) -> bool:
+        """Return True when the override applies to ``date``."""
+        return self.start_date <= date <= self.end_date
+
+
+def cycle_week_index(
+    schedule: PresenceSchedule, target_date: object
+) -> int:
+    """Return the cycle week index ``target_date`` falls in.
+
+    Anchor-date arithmetic ONLY (D-004): ``((target_date -
+    anchor_date).days // 7) % cycle_length_weeks``.  Python's floor
+    division makes dates BEFORE the anchor behave correctly — they
+    walk the cycle backwards (day -1 is the last week of the cycle,
+    day -8 wraps one further) instead of inverting, which is exactly
+    what ISO week parity does wrong in 53-week years.  The modulo of
+    a negative in Python is non-negative, so the index is always a
+    valid week index.
+    """
+    target = _parse_date(target_date, "target_date")
+    days = (target - schedule.anchor_date).days
+    return (days // 7) % schedule.cycle_length_weeks
+
+
+class PresenceEngine:
+    """Answers "is this child at this house on this date?".
+
+    Pure and side-effect-free: built from an immutable snapshot of the
+    children's schedules (``child_id -> PresenceSchedule``), it never
+    touches the database — callers build a snapshot from the DAO layer
+    and pass it in.  A child with NO schedule entry is present every
+    day (the third household child's case); a child WITH a schedule is
+    present exactly when the date's weekday is in that schedule's
+    pattern for the date's cycle week index (anchor-date arithmetic —
+    :func:`cycle_week_index`, never ISO week parity).
+    """
+
+    def __init__(self, schedules: dict[int, PresenceSchedule]) -> None:
+        if not isinstance(schedules, dict):
+            raise ValueError(
+                f"schedules must map child_id to PresenceSchedule, got "
+                f"{schedules!r}"
+            )
+        for child_id, schedule in schedules.items():
+            # Validate the KEY as a model child id too: True and 1.0
+            # compare equal to 1, so {True: schedule(1)} would be
+            # accepted and then behave differently on lookup.
+            _validate_child_id(child_id)
+            if not isinstance(schedule, PresenceSchedule):
+                raise ValueError(
+                    f"schedules[{child_id!r}] must be a PresenceSchedule, "
+                    f"got {schedule!r}"
+                )
+            if schedule.child_id != child_id:
+                raise ValueError(
+                    f"schedules is keyed {child_id!r} but holds the "
+                    f"schedule of child {schedule.child_id}"
+                )
+        # Read-only snapshot: reassignment is refused and no mutator
+        # exists, so a built engine answers consistently for its
+        # lifetime.
+        object.__setattr__(self, "_schedules", MappingProxyType(dict(schedules)))
+
+    def is_present(self, child_id: object, date: object) -> bool:
+        """Return whether ``child_id`` is present on ``date``."""
+        if isinstance(child_id, bool) or not isinstance(child_id, int):
+            raise ValueError(f"child_id must be an integer, got {child_id!r}")
+        target = _parse_date(date, "date")
+        schedule = self._schedules.get(child_id)
+        if schedule is None:
+            # No schedule at all: present every day (household default).
+            return True
+        week = cycle_week_index(schedule, target)
+        return target.weekday() in schedule.pattern[week]
+
+    def __setattr__(self, name: object, value: object) -> None:
+        raise AttributeError(
+            "PresenceEngine is an immutable snapshot; build a new engine "
+            "instead of mutating this one"
+        )

tests/test_admin_allowlist.py

@@ -415,3 +415,57 @@ async def test_reopened_setup_failure_closes_database(
     # The reopen path opened AND closed a connection; the failure is
     # the last thing observed.
     assert close_calls[-1] is True
+
+
+# ---------------------------------------------------------------------------
+# is_admin: the fail-closed resolver (Feature 09's verdict function)
+# ---------------------------------------------------------------------------
+
+
+def test_is_admin_verdict_matrix(tmp_path) -> None:
+    async def _body(database):
+        from custom_components.nestquest.admin_allowlist import is_admin
+
+        await set_admin_ids(database, ["user-owner"])
+        assert await is_admin(database, "user-owner") is True
+        assert await is_admin(database, "user-other") is False
+        # Unknown, malformed, and absent identities all fail closed.
+        assert await is_admin(database, "") is False
+        assert await is_admin(database, "  user-owner  ") is False, (
+            "ids are stored trimmed; an untrimmed lookup is a different "
+            "id and must not match"
+        )
+        assert await is_admin(database, None) is False
+        assert await is_admin(database, 42) is False
+        assert await is_admin(database, ["user-owner"]) is False
+        assert await is_admin(database, True) is False
+        return None
+
+    _with_db(tmp_path, "resolver-matrix.db")(_body)
+
+
+def test_is_admin_empty_allowlist_fails_closed(tmp_path) -> None:
+    """An empty allowlist returns False for EVERYONE, not True."""
+    async def _body(database):
+        from custom_components.nestquest.admin_allowlist import is_admin
+
+        for candidate in ("user-owner", "", None, 1):
+            assert await is_admin(database, candidate) is False
+        return None
+
+    _with_db(tmp_path, "resolver-empty.db")(_body)
+
+
+def test_is_admin_false_after_last_admin_removal_attempt(tmp_path) -> None:
+    """The refusal keeps the verdict sound: a refused removal leaves
+    the admin in place, so is_admin still resolves True."""
+    async def _body(database):
+        from custom_components.nestquest.admin_allowlist import is_admin
+
+        await set_admin_ids(database, ["user-owner"])
+        with pytest.raises(ValueError, match="at least one admin"):
+            await set_admin_ids(database, [])
+        assert await is_admin(database, "user-owner") is True
+        return None
+
+    _with_db(tmp_path, "resolver-refusal.db")(_body)

tests/test_dao_presence.py

@@ -619,4 +619,173 @@ def test_dao_presence_test_file_uses_dao_not_raw_table_sql() -> None:
     )
     assert sql_pattern.search(remaining) is None, (
         "tests must go through the DAO, not raw SQL, for these tables"
-    )
+    )
+
+# ---------------------------------------------------------------------------
+# Presence overrides: overlap rejection naming the conflict
+# ---------------------------------------------------------------------------
+
+
+def test_override_create_round_trip_single_and_range(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        single = await overrides.create(
+            child.id, "2026-07-20", "2026-07-20", False
+        )
+        assert (single.start_date, single.end_date) == ("2026-07-20",) * 2
+        span = await overrides.create(
+            child.id, "2026-08-01", "2026-08-05", True, note="traded week"
+        )
+        assert span.is_present is True
+        assert span.note == "traded week"
+        # Distinct, non-overlapping ranges coexist.
+        listed = await overrides.list_by_child_and_range(
+            child.id, "2026-01-01", "2026-12-31"
+        )
+        assert [row.id for row in listed] == [single.id, span.id]
+        return listed
+
+    _with_db(tmp_path, "override-round-trip.db")(_body)
+
+
+def test_override_overlap_rejected_and_names_the_conflict(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        existing = await overrides.create(
+            child.id, "2026-07-20", "2026-07-24", False, note="holiday"
+        )
+        for start, end in (
+            ("2026-07-24", "2026-07-26"),  # touches the existing end
+            ("2026-07-18", "2026-07-20"),  # touches the existing start
+            ("2026-07-21", "2026-07-22"),  # fully inside
+            ("2026-07-01", "2026-07-30"),  # fully covering
+        ):
+            with pytest.raises(ValueError, match="overlaps existing"):
+                await overrides.create(
+                    child.id, start, end, True, note="swap"
+                )
+        # The error NAMES the conflicting override (id + its range).
+        with pytest.raises(
+            ValueError,
+            match=(
+                rf"override {existing.id} "
+                rf"\[2026-07-20, 2026-07-24\].*is_present=False"
+            ),
+        ):
+            await overrides.create(child.id, "2026-07-22", "2026-07-22", True)
+        # Nothing was written by any rejected attempt.
+        listed = await overrides.list_by_child_and_range(
+            child.id, "2026-01-01", "2026-12-31"
+        )
+        assert [row.id for row in listed] == [existing.id]
+        return existing
+
+    _with_db(tmp_path, "override-overlap.db")(_body)
+
+
+def test_override_overlap_is_per_child(tmp_path) -> None:
+    """Different children's overrides may cover the same dates: the
+    conflict rule is per child."""
+    async def _body(database, schedules, overrides, children, child):
+        other = await children.create("Bo", NOW)
+        await overrides.create(child.id, "2026-07-20", "2026-07-24", False)
+        twin = await overrides.create(
+            other.id, "2026-07-20", "2026-07-24", True
+        )
+        assert twin.child_id == other.id
+        listed = await overrides.list_by_child_and_range(
+            other.id, "2026-01-01", "2026-12-31"
+        )
+        assert [row.id for row in listed] == [twin.id]
+        return twin
+
+    _with_db(tmp_path, "override-per-child.db")(_body)
+
+
+def test_override_remove_then_recreate_overlapping(tmp_path) -> None:
+    async def _body(database, schedules, overrides, children, child):
+        first = await overrides.create(
+            child.id, "2026-07-20", "2026-07-24", False
+        )
+        assert await overrides.delete(first.id) is True
+        assert await overrides.delete(first.id) is False
+        replacement = await overrides.create(
+            child.id, "2026-07-20", "2026-07-24", True, note="swap back"
+        )
+        assert replacement.is_present is True
+        return replacement
+
+    _with_db(tmp_path, "override-recreate.db")(_body)
+
+
+def test_override_create_racing_conflicts_serialize(tmp_path) -> None:
+    """Two racing creates for the same child and dates: exactly one
+    lands, the other is rejected with the overlap error.  DETERMINISTIC
+    gate: task A is paused at its INSERT — inside its open transaction,
+    after its overlap probe — so task B is created while A provably
+    holds the connection lock and must queue; B then re-runs the probe
+    against A's committed row and is rejected."""
+    import asyncio as asyncio_module
+    from unittest.mock import MagicMock
+
+    async def _main():
+        from custom_components.nestquest.dao_children import ChildrenDao
+        from custom_components.nestquest.dao_presence import (
+            PresenceOverridesDao,
+        )
+        from custom_components.nestquest.db import NestQuestDatabase
+        from custom_components.nestquest.migrations import apply_migrations
+
+        armed = {"active": False, "gated": False}
+        gate_open = asyncio_module.Event()
+        a_started = asyncio_module.Event()
+
+        hass = MagicMock()
+
+        async def _gated_executor(fn, *args):
+            if armed["active"]:
+                # Gate the FIRST _execute after arming: task A's INSERT,
+                # which runs inside its open transaction (BEGIN already
+                # executed) after the overlap probe.
+                if getattr(fn, "__name__", "") == "_execute" and not armed["gated"]:
+                    armed["gated"] = True
+                    a_started.set()
+                    await gate_open.wait()
+            return fn(*args)
+
+        hass.async_add_executor_job = _gated_executor
+        database = NestQuestDatabase(hass)
+        await database.open(tmp_path / "override-race.db")
+        try:
+            await apply_migrations(database)
+            children = ChildrenDao(database)
+            overrides = PresenceOverridesDao(database)
+            child = await children.create("Ada", NOW)
+
+            armed["active"] = True
+            task_a = asyncio_module.ensure_future(
+                overrides.create(child.id, "2026-07-20", "2026-07-24", True)
+            )
+            await a_started.wait()
+            armed["active"] = False
+            task_b = asyncio_module.ensure_future(
+                overrides.create(child.id, "2026-07-22", "2026-07-26", False)
+            )
+            await asyncio_module.sleep(0)
+            gate_open.set()
+            record_a, error_b = await asyncio_module.gather(
+                task_a, task_b, return_exceptions=True
+            )
+            assert isinstance(record_a, object) and not isinstance(
+                record_a, BaseException
+            ), f"A must land: {record_a!r}"
+            assert isinstance(error_b, ValueError), (
+                f"B must be rejected with the overlap error: {error_b!r}"
+            )
+            assert "overlaps existing" in str(error_b)
+            listed = await overrides.list_by_child_and_range(
+                child.id, "2026-01-01", "2026-12-31"
+            )
+            assert [row.id for row in listed] == [record_a.id]
+        finally:
+            await database.close()
+
+    asyncio.new_event_loop().run_until_complete(_main())

tests/test_presence.py

@@ -0,0 +1,478 @@
+"""Tests for presence.py: the Feature 05 custody model."""
+from __future__ import annotations
+
+import datetime
+
+import pytest
+
+from custom_components.nestquest.presence import (
+    MAX_CYCLE_LENGTH_WEEKS,
+    PresenceEngine,
+    PresenceOverride,
+    PresenceSchedule,
+    cycle_week_index,
+)
+
+
+ANCHOR = datetime.date(2026, 1, 5)  # a Monday
+
+TWO_WEEK_PATTERN = {0: {0, 2, 4}, 1: {1, 3}}
+
+
+# ---------------------------------------------------------------------------
+# PresenceSchedule construction
+# ---------------------------------------------------------------------------
+
+
+def test_schedule_holds_validated_fields() -> None:
+    schedule = PresenceSchedule(
+        3, 2, "2026-01-05", {0: {0, 2, 4}, 1: {1, 3}}
+    )
+    assert schedule.child_id == 3
+    assert schedule.cycle_length_weeks == 2
+    assert schedule.anchor_date == ANCHOR
+    assert schedule.pattern == {0: frozenset({0, 2, 4}), 1: frozenset({1, 3})}
+
+
+def test_schedule_accepts_date_objects() -> None:
+    schedule = PresenceSchedule(1, 1, ANCHOR, {0: {0}})
+    assert schedule.anchor_date is ANCHOR
+
+
+def test_schedule_rejects_cycle_length_below_one() -> None:
+    with pytest.raises(ValueError, match="between 1 and"):
+        PresenceSchedule(1, 0, ANCHOR, {})
+    with pytest.raises(ValueError, match="between 1 and"):
+        PresenceSchedule(1, -1, ANCHOR, {})
+
+
+def test_schedule_rejects_cycle_length_above_documented_cap() -> None:
+    """The 4 cap matches the schema's CHECK — both documents agree."""
+    with pytest.raises(ValueError, match="between 1 and"):
+        PresenceSchedule(1, MAX_CYCLE_LENGTH_WEEKS + 1, ANCHOR, {})
+    assert MAX_CYCLE_LENGTH_WEEKS == 4
+
+
+@pytest.mark.parametrize("bad", [True, "2", 1.0, None])
+def test_schedule_rejects_non_int_cycle_length(bad) -> None:
+    with pytest.raises(ValueError, match="cycle_length_weeks must be an integer"):
+        PresenceSchedule(1, bad, ANCHOR, {0: {0}})
+
+
+def test_schedule_rejects_pattern_missing_week_indices() -> None:
+    with pytest.raises(ValueError, match="missing \\[1\\]"):
+        PresenceSchedule(1, 2, ANCHOR, {0: {0, 2}})
+
+
+def test_schedule_rejects_pattern_with_extra_week_indices() -> None:
+    with pytest.raises(ValueError, match="week index must be in 0"):
+        PresenceSchedule(1, 2, ANCHOR, {0: {0}, 1: {1}, 2: {2}})
+
+
+def test_schedule_rejects_pattern_with_non_int_week_keys() -> None:
+    with pytest.raises(ValueError, match="week index must be an integer"):
+        PresenceSchedule(1, 1, ANCHOR, {"0": {0}})
+
+
+def test_schedule_rejects_pattern_with_bool_week_key() -> None:
+    """True would silently be week 1; bools are never valid indices."""
+    with pytest.raises(ValueError, match="week index must be an integer"):
+        PresenceSchedule(1, 2, ANCHOR, {0: {0}, True: {1}})
+
+
+@pytest.mark.parametrize("bad_weekday", [-1, 7, 100])
+def test_schedule_rejects_weekday_outside_zero_to_six(bad_weekday) -> None:
+    with pytest.raises(ValueError, match="weekday must be in 0..6"):
+        PresenceSchedule(1, 1, ANCHOR, {0: {bad_weekday}})
+
+
+@pytest.mark.parametrize("bad", [True, "1", 1.5, None])
+def test_schedule_rejects_non_int_weekdays(bad) -> None:
+    with pytest.raises(ValueError, match="weekday must be an int"):
+        PresenceSchedule(1, 1, ANCHOR, {0: [bad]})
+
+
+def test_schedule_rejects_string_and_none_week_sets() -> None:
+    """A string is iterable — "02" would silently mean {0, 2}."""
+    with pytest.raises(ValueError, match="iterable of weekday ints"):
+        PresenceSchedule(1, 1, ANCHOR, {0: "02"})
+    with pytest.raises(ValueError, match="iterable of weekday ints"):
+        PresenceSchedule(1, 1, ANCHOR, {0: None})
+
+
+def test_schedule_rejects_non_dict_pattern() -> None:
+    with pytest.raises(ValueError, match="must map week index"):
+        PresenceSchedule(1, 1, ANCHOR, "0,2")
+
+
+@pytest.mark.parametrize("bad", [True, 1.0, "2026-01-05", None])
+def test_schedule_rejects_non_int_child_id(bad) -> None:
+    with pytest.raises(ValueError, match="child_id must be an integer"):
+        PresenceSchedule(bad, 1, ANCHOR, {0: {0}})
+
+
+@pytest.mark.parametrize(
+    "bad", ["2026-1-5", "2026/01/05", "not-a-date", 5, None, True]
+)
+def test_schedule_rejects_non_strict_anchor_dates(bad) -> None:
+    with pytest.raises(ValueError, match="anchor_date"):
+        PresenceSchedule(1, 1, bad, {0: {0}})
+
+
+def test_schedule_empty_week_means_absent_week() -> None:
+    """An explicitly empty week is valid: absent every day of it —
+    distinct from having no schedule (the engine's default)."""
+    schedule = PresenceSchedule(1, 2, ANCHOR, {0: {0, 2}, 1: set()})
+    assert schedule.pattern[1] == frozenset()
+
+
+def test_schedule_empty_week_encodes_as_empty_segment() -> None:
+    schedule = PresenceSchedule(1, 2, ANCHOR, {0: {0, 2}, 1: set()})
+    assert schedule.encode() == "0,2|"
+
+
+def test_schedule_equal_patterns_are_equal_and_encode_identically() -> None:
+    one = PresenceSchedule(1, 2, ANCHOR, {0: {4, 2, 0}, 1: {3, 1}})
+    two = PresenceSchedule(2, 2, ANCHOR, {0: {0, 2, 4}, 1: {1, 3}})
+    assert one.encode() == two.encode() == "0,2,4|1,3"
+
+
+# ---------------------------------------------------------------------------
+# Serialization round-trip
+# ---------------------------------------------------------------------------
+
+
+@pytest.mark.parametrize(
+    "encoded",
+    [
+        "0,2,4|1,3",
+        "0|1,2,3,4,5,6",
+        "|",
+        "0,2|",
+        "|0,2",
+        "6",
+        "0,1,2,3,4,5,6|",
+    ],
+)
+def test_schedule_decode_encode_round_trips_losslessly(encoded) -> None:
+    schedule = PresenceSchedule.decode(1, ANCHOR, encoded)
+    assert schedule.encode() == encoded
+    again = PresenceSchedule.decode(1, ANCHOR, schedule.encode())
+    assert again == schedule
+
+
+def test_schedule_decode_sets_cycle_length_from_segments() -> None:
+    one_week = PresenceSchedule.decode(1, ANCHOR, "0,2,4")
+    assert one_week.cycle_length_weeks == 1
+    four_week = PresenceSchedule.decode(1, ANCHOR, "0|1|2|3,4")
+    assert four_week.cycle_length_weeks == 4
+
+
+def test_schedule_decode_rejects_malformed_segments() -> None:
+    for bad in ("0,12|1", "0,,2|1", "x|1", "0|-1|2", "8", "0,|1"):
+        with pytest.raises(ValueError, match="invalid weekday"):
+            PresenceSchedule.decode(1, ANCHOR, bad)
+
+
+def test_schedule_decode_rejects_non_string() -> None:
+    with pytest.raises(ValueError, match="must be a string"):
+        PresenceSchedule.decode(1, ANCHOR, None)
+
+
+def test_schedule_decode_rejects_too_many_segments(tmp_path) -> None:
+    with pytest.raises(ValueError, match="between 1 and"):
+        PresenceSchedule.decode(1, ANCHOR, "0|1|2|3|4")
+
+
+# ---------------------------------------------------------------------------
+# PresenceOverride
+# ---------------------------------------------------------------------------
+
+
+def test_override_single_date_and_range_cover() -> None:
+    single = PresenceOverride(1, "2026-07-20", "2026-07-20", False)
+    assert single.covers(datetime.date(2026, 7, 20))
+    assert not single.covers(datetime.date(2026, 7, 21))
+    span = PresenceOverride(1, "2026-07-20", "2026-07-24", True, note="trade")
+    assert span.covers(datetime.date(2026, 7, 22))
+    assert not span.covers(datetime.date(2026, 7, 19))
+    assert not span.covers(datetime.date(2026, 7, 25))
+    assert span.note == "trade"
+
+
+def test_override_end_before_start_rejected() -> None:
+    with pytest.raises(ValueError, match="end_date must be on or after"):
+        PresenceOverride(1, "2026-07-24", "2026-07-20", True)
+
+
+@pytest.mark.parametrize("bad", ["1", 0, 1.0, None])
+def test_override_rejects_non_bool_is_present(bad) -> None:
+    with pytest.raises(ValueError, match="is_present must be a real bool"):
+        PresenceOverride(1, "2026-07-20", "2026-07-20", bad)
+
+
+@pytest.mark.parametrize("bad", [5, ["note"], 1.5])
+def test_override_rejects_non_string_note(bad) -> None:
+    with pytest.raises(ValueError, match="note must be a string or None"):
+        PresenceOverride(1, "2026-07-20", "2026-07-20", True, note=bad)
+
+
+@pytest.mark.parametrize("bad", [True, 1.0, "1"])
+def test_override_rejects_non_int_child_id(bad) -> None:
+    with pytest.raises(ValueError, match="child_id must be an integer"):
+        PresenceOverride(bad, "2026-07-20", "2026-07-20", True)
+
+
+@pytest.mark.parametrize(
+    ("start", "end"), [("2026-7-20", "2026-07-20"), ("nope", "2026-07-20")]
+)
+def test_override_rejects_non_strict_dates(start, end) -> None:
+    with pytest.raises(ValueError, match="date"):
+        PresenceOverride(1, start, end, True)
+
+
+def test_schedule_rejects_datetime_anchor() -> None:
+    """datetime.datetime subclasses date: a bare isinstance check would
+    store a time component and every date comparison would TypeError."""
+    with pytest.raises(ValueError, match="plain calendar date"):
+        PresenceSchedule(1, 1, datetime.datetime(2026, 1, 5, 9, 30), {0: {0}})
+
+
+def test_override_rejects_datetime_bounds() -> None:
+    with pytest.raises(ValueError, match="plain calendar date"):
+        PresenceOverride(
+            1, datetime.datetime(2026, 7, 20), "2026-07-20", True
+        )
+
+
+def test_schedule_pattern_is_immutable_after_construction() -> None:
+    """The frozen schedule must not leak a mutable dict: injecting
+    unvalidated weekdays or dropping required weeks after construction
+    would bypass total validation."""
+    schedule = PresenceSchedule(1, 2, ANCHOR, {0: {0, 2}, 1: {1}})
+    with pytest.raises(TypeError):
+        schedule.pattern[0] = {9}
+    with pytest.raises(TypeError):
+        del schedule.pattern[1]
+    # mappingproxy exposes no mutators at all.
+    assert not any(
+        hasattr(schedule.pattern, name)
+        for name in ("clear", "pop", "popitem", "setdefault", "update")
+    )
+    # The validated contents are still readable and encode correctly.
+    assert schedule.pattern[0] == frozenset({0, 2})
+    assert schedule.encode() == "0,2|1"
+
+
+# ---------------------------------------------------------------------------
+# Cycle arithmetic: anchor-date only, never ISO week parity
+# ---------------------------------------------------------------------------
+
+
+def test_cycle_week_index_follows_the_anchor_formula() -> None:
+    schedule = PresenceSchedule(1, 2, ANCHOR, {0: {0}, 1: {1}})
+    # The anchor day starts week 0; each whole 7 days steps the cycle.
+    offsets = {0: 0, 1: 0, 6: 0, 7: 1, 8: 1, 13: 1, 14: 0, 15: 0, 22: 1}
+    for offset, expected in offsets.items():
+        assert (
+            cycle_week_index(
+                schedule, ANCHOR + datetime.timedelta(days=offset)
+            )
+            == expected
+        ), f"offset {offset}"
+
+
+def test_cycle_week_index_before_the_anchor_walks_backwards() -> None:
+    """Python floor division: day -1 is the LAST week of the cycle,
+    wrapping backwards — never inverting."""
+    schedule = PresenceSchedule(1, 2, ANCHOR, {0: {0}, 1: {1}})
+    assert cycle_week_index(schedule, ANCHOR - datetime.timedelta(days=1)) == 1
+    assert cycle_week_index(schedule, ANCHOR - datetime.timedelta(days=7)) == 1
+    assert cycle_week_index(schedule, ANCHOR - datetime.timedelta(days=8)) == 0
+
+
+def test_cycle_week_index_three_week_pattern() -> None:
+    schedule = PresenceSchedule(
+        1, 3, ANCHOR, {0: {0}, 1: {1}, 2: {2}}
+    )
+    offsets = [0, 7, 14, 21, 28]
+    assert [cycle_week_index(schedule, ANCHOR + datetime.timedelta(days=o)) for o in offsets] == [
+        0, 1, 2, 0, 1
+    ]
+
+
+def test_cycle_week_index_stable_across_three_year_boundaries() -> None:
+    """The regression this exists for: walking day by day through a
+    December 31st -> January 1st boundary, the cycle index steps by 0
+    or +1 (mod cycle length) and NEVER resets or inverts — including
+    2020, a 53-ISO-week year, where ISO week parity flips and would
+    invert a two-week custody schedule on January 1st."""
+    schedule = PresenceSchedule(
+        1, 2, datetime.date(2019, 1, 6), {0: {0, 2, 4}, 1: {1, 3}}
+    )
+    # Independently derived expectations (anchor 2019-01-06, index =
+    # floor(days-since-anchor / 7) % 2, hand-verified) — hardcoded so
+    # even an implementation that resets or inverts at January 1st
+    # cannot pass by coincidence:
+    expected = {
+        # 2019 -> 2020 boundary
+        datetime.date(2019, 12, 26): 0,
+        datetime.date(2019, 12, 27): 0,
+        datetime.date(2019, 12, 28): 0,
+        datetime.date(2019, 12, 29): 1,
+        datetime.date(2019, 12, 30): 1,
+        datetime.date(2019, 12, 31): 1,
+        datetime.date(2020, 1, 1): 1,
+        datetime.date(2020, 1, 2): 1,
+        datetime.date(2020, 1, 3): 1,
+        datetime.date(2020, 1, 4): 1,
+        datetime.date(2020, 1, 5): 0,
+        datetime.date(2020, 1, 6): 0,
+        datetime.date(2020, 1, 7): 0,
+        # 2020 (53 ISO weeks) -> 2021 boundary
+        datetime.date(2020, 12, 27): 1,
+        datetime.date(2020, 12, 28): 1,
+        datetime.date(2020, 12, 29): 1,
+        datetime.date(2020, 12, 30): 1,
+        datetime.date(2020, 12, 31): 1,
+        datetime.date(2021, 1, 1): 1,
+        datetime.date(2021, 1, 2): 1,
+        datetime.date(2021, 1, 3): 0,
+        datetime.date(2021, 1, 4): 0,
+        datetime.date(2021, 1, 5): 0,
+        datetime.date(2021, 1, 6): 0,
+        datetime.date(2021, 1, 7): 0,
+        # 2021 -> 2022 boundary
+        datetime.date(2021, 12, 27): 1,
+        datetime.date(2021, 12, 28): 1,
+        datetime.date(2021, 12, 29): 1,
+        datetime.date(2021, 12, 30): 1,
+        datetime.date(2021, 12, 31): 1,
+        datetime.date(2022, 1, 1): 1,
+        datetime.date(2022, 1, 2): 0,
+        datetime.date(2022, 1, 3): 0,
+        datetime.date(2022, 1, 4): 0,
+        datetime.date(2022, 1, 5): 0,
+        datetime.date(2022, 1, 6): 0,
+        datetime.date(2022, 1, 7): 0,
+    }
+    for day, wanted in expected.items():
+        got = cycle_week_index(schedule, day)
+        assert got == wanted, f"{day}: expected week {wanted}, got {got}"
+    # And 2020 really is the 53-ISO-week year this guards against.
+    assert datetime.date(2020, 12, 31).isocalendar()[1] == 53
+
+
+def test_module_uses_no_iso_week_functions() -> None:
+    """D-004 guardrail: cycle math never reads ISO week numbers."""
+    from pathlib import Path
+
+    source = Path(
+        __import__("custom_components.nestquest.presence", fromlist=["__file__"]).__file__
+    ).read_text()
+    assert "isocalendar" not in source
+    assert "isoweek" not in source
+
+
+# ---------------------------------------------------------------------------
+# PresenceEngine: is_present with the always-present default
+# ---------------------------------------------------------------------------
+
+
+def _three_child_engine() -> PresenceEngine:
+    """Two children on opposite weeks of one two-week rotation, one
+    with no schedule at all (the always-present sibling)."""
+    anchor = datetime.date(2026, 1, 5)  # Monday, week 0
+    declan = PresenceSchedule(
+        1, 2, anchor, {0: {0, 1, 2, 3, 4, 5, 6}, 1: set()}
+    )
+    jordyn = PresenceSchedule(
+        2, 2, anchor, {0: set(), 1: {0, 1, 2, 3, 4, 5, 6}}
+    )
+    return PresenceEngine({1: declan, 2: jordyn})
+
+
+def test_engine_child_without_schedule_is_always_present() -> None:
+    """Every day of the four-week span, all three children get the
+    expected verdict: Chloe (no schedule) always present; the two
+    rotation children complementary per the anchor pattern."""
+    engine = _three_child_engine()
+    anchor = datetime.date(2026, 1, 5)
+    for offset in range(28):
+        day = anchor + datetime.timedelta(days=offset)
+        week = ((day - anchor).days // 7) % 2
+        assert engine.is_present(3, day) is True, f"offset {offset}: Chloe"
+        assert engine.is_present(1, day) is (week == 0), (
+            f"offset {offset}: Declan should follow week {week}"
+        )
+        assert engine.is_present(2, day) is (week == 1), (
+            f"offset {offset}: Jordyn should follow week {week}"
+        )
+
+
+def test_engine_alternating_children_are_complementary_for_four_weeks() -> None:
+    """Every day of a four-week span: the two rotation children swap
+    presence exactly per the anchor pattern — when one is home the
+    other is away."""
+    engine = _three_child_engine()
+    anchor = datetime.date(2026, 1, 5)
+    for offset in range(28):
+        day = anchor + datetime.timedelta(days=offset)
+        d = engine.is_present(1, day)
+        j = engine.is_present(2, day)
+        assert d == (not j), f"offset {offset}: {d}/{j} not complementary"
+        expected_week = ((day - anchor).days // 7) % 2
+        expected_present = day.weekday() in {0, 1, 2, 3, 4, 5, 6}
+        assert engine.is_present(1, day) == (expected_week == 0), (
+            f"offset {offset}: Declan week {expected_week}"
+        )
+    # Spot-check the swap days.
+    assert engine.is_present(1, anchor) is True
+    assert engine.is_present(2, anchor) is False
+    assert engine.is_present(1, anchor + datetime.timedelta(days=7)) is False
+    assert engine.is_present(2, anchor + datetime.timedelta(days=7)) is True
+
+
+def test_engine_weekend_absence_respects_the_pattern() -> None:
+    """A weekday-set pattern excludes days outside the set even in the
+    child's present week."""
+    engine = PresenceEngine(
+        {
+            1: PresenceSchedule(
+                1, 2, datetime.date(2026, 1, 5), {0: {1, 3}, 1: {2, 4}}
+            )
+        }
+    )
+    # 2026-01-06 (Tue) is week 0, weekday 1 -> present.
+    assert engine.is_present(1, datetime.date(2026, 1, 6)) is True
+    # 2026-01-07 (Wed) is week 0, weekday 2 -> absent.
+    assert engine.is_present(1, datetime.date(2026, 1, 7)) is False
+    # 2026-01-14 (Wed) is week 1, weekday 2 -> present.
+    assert engine.is_present(1, datetime.date(2026, 1, 14)) is True
+
+
+def test_engine_rejects_bad_construction_and_inputs() -> None:
+    schedule = PresenceSchedule(1, 1, ANCHOR, {0: {0}})
+    with pytest.raises(ValueError, match="must map child_id"):
+        PresenceEngine([schedule])
+    with pytest.raises(ValueError, match="must be a PresenceSchedule"):
+        PresenceEngine({1: "not-a-schedule"})
+    with pytest.raises(ValueError, match="holds the schedule"):
+        PresenceEngine({2: schedule})
+    with pytest.raises(ValueError, match="child_id must be an integer"):
+        PresenceEngine({True: schedule})
+    with pytest.raises(ValueError, match="child_id must be an integer"):
+        PresenceEngine({1.0: PresenceSchedule(1, 1, ANCHOR, {0: {0}})})
+    engine = PresenceEngine({1: schedule})
+    with pytest.raises(ValueError, match="child_id must be an integer"):
+        engine.is_present(True, ANCHOR)
+    with pytest.raises(ValueError, match="plain calendar date"):
+        engine.is_present(1, datetime.datetime(2026, 1, 5))
+
+
+def test_engine_is_immutable() -> None:
+    engine = _three_child_engine()
+    with pytest.raises(AttributeError, match="immutable snapshot"):
+        engine._schedules = {}
+    with pytest.raises(AttributeError, match="immutable snapshot"):
+        engine.something_new = 1