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

1from __future__ import annotations 

2 

3import uuid 

4from typing import Any 

5 

6from sqlalchemy import func, select 

7from sqlalchemy.ext.asyncio import AsyncSession 

8 

9from robot_dispatch.db.models import OutboxMessage, Robot, Task, TaskEvent 

10from robot_dispatch.domain.enums import QUEUED_TASK_STATUSES, RobotStatus 

11 

12 

13class SqlTaskRepository: 

14 def __init__(self, session: AsyncSession) -> None: 

15 self._session = session 

16 

17 async def add(self, task: Task) -> Task: 

18 self._session.add(task) 

19 await self._session.flush() 

20 return task 

21 

22 async def get(self, task_id: uuid.UUID) -> Task | None: 

23 return await self._session.get(Task, task_id) 

24 

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

28 

29 async def lock_ready(self, *, now, limit: int) -> list[Task]: 

30 """Select queued tasks and lock them, skipping rows locked by other dispatchers. 

31 

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

47 

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

62 

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

82 

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()} 

87 

88 

89class SqlRobotRepository: 

90 def __init__(self, session: AsyncSession) -> None: 

91 self._session = session 

92 

93 async def add(self, robot: Robot) -> Robot: 

94 self._session.add(robot) 

95 await self._session.flush() 

96 return robot 

97 

98 async def get(self, robot_id: uuid.UUID) -> Robot | None: 

99 return await self._session.get(Robot, robot_id) 

100 

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

104 

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

109 

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 

113 

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

128 

129 

130class SqlEventRepository: 

131 def __init__(self, session: AsyncSession) -> None: 

132 self._session = session 

133 

134 async def add(self, event: TaskEvent) -> TaskEvent: 

135 self._session.add(event) 

136 await self._session.flush() 

137 return event 

138 

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

148 

149 

150class SqlOutboxRepository: 

151 def __init__(self, session: AsyncSession) -> None: 

152 self._session = session 

153 

154 async def add(self, message: OutboxMessage) -> OutboxMessage: 

155 self._session.add(message) 

156 await self._session.flush() 

157 return message 

158 

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

169 

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

174 

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

180 

181 

182def _unused(*_: Any) -> None: # pragma: no cover - keeps linters honest about imports 

183 return None