Coverage for src/robot_dispatch/repositories/sql.py: 100%
95 statements
« prev ^ index » next coverage.py v7.17.2, created at 2026-11-07 17:18 +0000
« prev ^ index » next coverage.py v7.17.2, created at 2026-11-07 17:18 +0000
1from __future__ import annotations
3import uuid
4from typing import Any
6from sqlalchemy import func, select
7from sqlalchemy.ext.asyncio import AsyncSession
9from robot_dispatch.db.models import OutboxMessage, Robot, Task, TaskEvent
10from robot_dispatch.domain.enums import QUEUED_TASK_STATUSES, RobotStatus
13class SqlTaskRepository:
14 def __init__(self, session: AsyncSession) -> None:
15 self._session = session
17 async def add(self, task: Task) -> Task:
18 self._session.add(task)
19 await self._session.flush()
20 return task
22 async def get(self, task_id: uuid.UUID) -> Task | None:
23 return await self._session.get(Task, task_id)
25 async def get_by_external_id(self, external_id: str) -> Task | None:
26 stmt = select(Task).where(Task.external_id == external_id)
27 return (await self._session.execute(stmt)).scalar_one_or_none()
29 async def lock_ready(self, *, now, limit: int) -> list[Task]:
30 """Select queued tasks and lock them, skipping rows locked by other dispatchers.
32 `FOR UPDATE SKIP LOCKED` makes several dispatcher replicas safe: each task is
33 handed to exactly one of them, no advisory locks or coordination required.
34 """
35 stmt = (
36 select(Task)
37 .where(
38 Task.status.in_([status.value for status in QUEUED_TASK_STATUSES]),
39 Task.available_at <= now,
40 )
41 .order_by(Task.priority.desc(), Task.available_at.asc(), Task.created_at.asc())
42 .limit(limit)
43 .with_for_update(skip_locked=True)
44 )
45 result = await self._session.execute(stmt)
46 return list(result.scalars().all())
48 async def lock_expired_leases(self, *, now, limit: int) -> list[Task]:
49 stmt = (
50 select(Task)
51 .where(
52 Task.status.in_([status.value for status in QUEUED_TASK_STATUSES] + ["assigned", "running"]),
53 Task.lease_expires_at.is_not(None),
54 Task.lease_expires_at < now,
55 )
56 .order_by(Task.lease_expires_at.asc())
57 .limit(limit)
58 .with_for_update(skip_locked=True)
59 )
60 result = await self._session.execute(stmt)
61 return list(result.scalars().all())
63 async def list(
64 self,
65 *,
66 statuses: list[str] | None = None,
67 robot_id: uuid.UUID | None = None,
68 task_type: str | None = None,
69 limit: int = 50,
70 offset: int = 0,
71 ) -> list[Task]:
72 stmt = select(Task)
73 if statuses:
74 stmt = stmt.where(Task.status.in_(statuses))
75 if robot_id is not None:
76 stmt = stmt.where(Task.robot_id == robot_id)
77 if task_type is not None:
78 stmt = stmt.where(Task.type == task_type)
79 stmt = stmt.order_by(Task.created_at.desc()).limit(limit).offset(offset)
80 result = await self._session.execute(stmt)
81 return list(result.scalars().all())
83 async def count_by_status(self) -> dict[str, int]:
84 stmt = select(Task.status, func.count()).group_by(Task.status)
85 result = await self._session.execute(stmt)
86 return {row[0]: int(row[1]) for row in result.all()}
89class SqlRobotRepository:
90 def __init__(self, session: AsyncSession) -> None:
91 self._session = session
93 async def add(self, robot: Robot) -> Robot:
94 self._session.add(robot)
95 await self._session.flush()
96 return robot
98 async def get(self, robot_id: uuid.UUID) -> Robot | None:
99 return await self._session.get(Robot, robot_id)
101 async def get_by_name(self, name: str) -> Robot | None:
102 stmt = select(Robot).where(Robot.name == name)
103 return (await self._session.execute(stmt)).scalar_one_or_none()
105 async def list(self) -> list[Robot]:
106 stmt = select(Robot).order_by(Robot.name)
107 result = await self._session.execute(stmt)
108 return list(result.unique().scalars().all())
110 async def find_idle_for(self, *, task_type: str, now, offline_after) -> Robot | None:
111 """Pick a live robot that advertises the required capability."""
112 from datetime import timedelta
114 threshold = now - timedelta(seconds=offline_after)
115 stmt = (
116 select(Robot)
117 .join(Robot.capabilities)
118 .where(
119 Robot.status == RobotStatus.IDLE.value,
120 Robot.last_seen_at.is_not(None),
121 Robot.last_seen_at >= threshold,
122 )
123 .order_by(Robot.last_seen_at.desc())
124 .limit(1)
125 )
126 result = await self._session.execute(stmt)
127 return result.unique().scalars().first()
130class SqlEventRepository:
131 def __init__(self, session: AsyncSession) -> None:
132 self._session = session
134 async def add(self, event: TaskEvent) -> TaskEvent:
135 self._session.add(event)
136 await self._session.flush()
137 return event
139 async def list_for_task(self, task_id: uuid.UUID, *, limit: int = 200) -> list[TaskEvent]:
140 stmt = (
141 select(TaskEvent)
142 .where(TaskEvent.task_id == task_id)
143 .order_by(TaskEvent.created_at.asc())
144 .limit(limit)
145 )
146 result = await self._session.execute(stmt)
147 return list(result.scalars().all())
150class SqlOutboxRepository:
151 def __init__(self, session: AsyncSession) -> None:
152 self._session = session
154 async def add(self, message: OutboxMessage) -> OutboxMessage:
155 self._session.add(message)
156 await self._session.flush()
157 return message
159 async def lock_pending(self, *, now, limit: int) -> list[OutboxMessage]:
160 stmt = (
161 select(OutboxMessage)
162 .where(OutboxMessage.sent_at.is_(None), OutboxMessage.available_at <= now)
163 .order_by(OutboxMessage.created_at.asc())
164 .limit(limit)
165 .with_for_update(skip_locked=True)
166 )
167 result = await self._session.execute(stmt)
168 return list(result.scalars().all())
170 async def mark_sent(self, message: OutboxMessage, sent_at) -> None:
171 message.sent_at = sent_at
172 message.attempts += 1
173 await self._session.flush()
175 async def mark_failed(self, message: OutboxMessage, error: str, available_at, attempts: int) -> None:
176 message.attempts = attempts
177 message.last_error = error[:2000]
178 message.available_at = available_at
179 await self._session.flush()
182def _unused(*_: Any) -> None: # pragma: no cover - keeps linters honest about imports
183 return None