Coverage for app/backend/src/couchers/models/rest.py: 99%

1import enum 

2from datetime import date, datetime 

3from typing import TYPE_CHECKING, Any 

4 

5from geoalchemy2 import Geometry 

6from sqlalchemy import ( 

7 ARRAY, 

8 JSON, 

9 BigInteger, 

10 Boolean, 

11 CheckConstraint, 

12 Date, 

13 DateTime, 

14 Enum, 

15 Float, 

16 ForeignKey, 

17 Index, 

18 Integer, 

19 String, 

20 UniqueConstraint, 

21 func, 

22 text, 

23) 

24from sqlalchemy import LargeBinary as Binary 

25from sqlalchemy.dialects.postgresql import INET 

26from sqlalchemy.ext.hybrid import hybrid_property 

27from sqlalchemy.orm import Mapped, mapped_column, relationship 

28from sqlalchemy.sql import expression 

29 

30from couchers.constants import GUIDELINES_VERSION 

31from couchers.models.base import Base, Geom 

32from couchers.models.moderation import ModerationObjectType 

33from couchers.models.users import HostingStatus 

34from couchers.utils import now 

35 

36if TYPE_CHECKING: 

37 from couchers.models import HostRequest, User 

38 from couchers.models.moderation import ModerationState 

39 

40 

41class UserBadge(Base, kw_only=True): 

42 """ 

43 A badge on a user's profile 

44 """ 

45 

46 __tablename__ = "user_badges" 

47 __table_args__ = (UniqueConstraint("user_id", "badge_id"),) 

48 

49 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

50 

51 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True) 

52 # corresponds to "id" in badges.json 

53 badge_id: Mapped[str] = mapped_column(String, index=True) 

54 

55 # take this with a grain of salt, someone may get then lose a badge for whatever reason 

56 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

57 

58 user: Mapped[User] = relationship(init=False, back_populates="badges") 

59 

60 

61class FriendStatus(enum.Enum): 

62 pending = enum.auto() 

63 accepted = enum.auto() 

64 rejected = enum.auto() 

65 cancelled = enum.auto() 

66 

67 

68class FriendRelationship(Base, kw_only=True): 

69 """ 

70 Friendship relations between users 

71 

72 TODO: make this better with sqlalchemy self-referential stuff 

73 """ 

74 

75 __tablename__ = "friend_relationships" 

76 __moderation_author_column__ = "from_user_id" 

77 __moderation_object_type__ = ModerationObjectType.friend_request 

78 

79 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

80 

81 from_user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True) 

82 to_user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True) 

83 

84 # Unified Moderation System 

85 moderation_state_id: Mapped[int] = mapped_column(ForeignKey("moderation_states.id"), index=True) 

86 

87 status: Mapped[FriendStatus] = mapped_column(Enum(FriendStatus), default=FriendStatus.pending) 

88 

89 # timezones should always be UTC 

90 time_sent: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

91 time_responded: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None) 

92 

93 from_user: Mapped[User] = relationship( 

94 init=False, backref="friends_from", foreign_keys="FriendRelationship.from_user_id" 

95 ) 

96 to_user: Mapped[User] = relationship(init=False, backref="friends_to", foreign_keys="FriendRelationship.to_user_id") 

97 moderation_state: Mapped[ModerationState] = relationship(init=False) 

98 

99 __table_args__ = ( 

100 # Ping looks up pending friend reqs, this speeds that up 

101 Index( 

102 "ix_friend_relationships_status_to_from", 

103 status, 

104 to_user_id, 

105 from_user_id, 

106 ), 

107 # At most one active (pending or accepted) relationship per unordered user pair 

108 Index( 

109 "uq_friend_relationships_active_pair", 

110 func.least(from_user_id, to_user_id), 

111 func.greatest(from_user_id, to_user_id), 

112 unique=True, 

113 postgresql_where=status.in_([FriendStatus.pending, FriendStatus.accepted]), 

114 ), 

115 ) 

116 

117 

118class ContributeOption(enum.Enum): 

119 yes = enum.auto() 

120 maybe = enum.auto() 

121 no = enum.auto() 

122 

123 

124class ContributorForm(Base, kw_only=True): 

125 """ 

126 Someone filled in the contributor form 

127 """ 

128 

129 __tablename__ = "contributor_forms" 

130 

131 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

132 

133 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True) 

134 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

135 

136 ideas: Mapped[str | None] = mapped_column(String, default=None) 

137 features: Mapped[str | None] = mapped_column(String, default=None) 

138 experience: Mapped[str | None] = mapped_column(String, default=None) 

139 contribute: Mapped[ContributeOption | None] = mapped_column(Enum(ContributeOption), default=None) 

140 contribute_ways: Mapped[list[str]] = mapped_column(ARRAY(String)) 

141 expertise: Mapped[str | None] = mapped_column(String, default=None) 

142 

143 user: Mapped[User] = relationship(init=False, backref="contributor_forms") 

144 

145 @hybrid_property 

146 def is_filled(self) -> Any: 

147 """ 

148 Whether the form counts as having been filled 

149 """ 

150 return ( 

151 (self.ideas != None) 

152 | (self.features != None) 

153 | (self.experience != None) 

154 | (self.contribute != None) 

155 | (self.contribute_ways != []) 

156 | (self.expertise != None) 

157 ) 

158 

159 @property 

160 def should_notify(self) -> bool: 

161 """ 

162 If this evaluates to true, we send an email to the recruitment team. 

163 

164 We currently send if expertise is listed, or if they list a way to help outside of a set list 

165 """ 

166 return False 

167 

168 

169class SignupFlow(Base, kw_only=True): 

170 """ 

171 Signup flows/incomplete users 

172 

173 Coinciding fields have the same meaning as in User 

174 """ 

175 

176 __tablename__ = "signup_flows" 

177 

178 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

179 

180 # housekeeping 

181 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

182 flow_token: Mapped[str] = mapped_column(String, unique=True) 

183 email_verified: Mapped[bool] = mapped_column(Boolean, default=False) 

184 email_sent: Mapped[bool] = mapped_column(Boolean, default=False) 

185 email_token: Mapped[str | None] = mapped_column(String, default=None) 

186 email_token_expiry: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None) 

187 

188 ## Basic 

189 name: Mapped[str] = mapped_column(String) 

190 # TODO: unique across both tables 

191 email: Mapped[str] = mapped_column(String, unique=True) 

192 # TODO: invitation, attribution 

193 

194 ## Account 

195 # TODO: unique across both tables 

196 username: Mapped[str | None] = mapped_column(String, unique=True, default=None) 

197 hashed_password: Mapped[bytes | None] = mapped_column(Binary, default=None) 

198 birthdate: Mapped[date | None] = mapped_column(Date, default=None) # in the timezone of birthplace 

199 gender: Mapped[str | None] = mapped_column(String, default=None) 

200 hosting_status: Mapped[HostingStatus | None] = mapped_column(Enum(HostingStatus), default=None) 

201 city: Mapped[str | None] = mapped_column(String, default=None) 

202 geom: Mapped[Geom | None] = mapped_column(Geometry(geometry_type="POINT", srid=4326), default=None) 

203 geom_radius: Mapped[float | None] = mapped_column(Float, default=None) 

204 

205 accepted_tos: Mapped[int | None] = mapped_column(Integer, default=None) 

206 accepted_community_guidelines: Mapped[int] = mapped_column(Integer, server_default="0", init=False) 

207 

208 opt_out_of_newsletter: Mapped[bool | None] = mapped_column(Boolean, default=None) 

209 

210 ## Feedback (now unused) 

211 filled_feedback: Mapped[bool] = mapped_column(Boolean, default=False) 

212 ideas: Mapped[str | None] = mapped_column(String, default=None) 

213 features: Mapped[str | None] = mapped_column(String, default=None) 

214 experience: Mapped[str | None] = mapped_column(String, default=None) 

215 contribute: Mapped[ContributeOption | None] = mapped_column(Enum(ContributeOption), default=None) 

216 contribute_ways: Mapped[list[str] | None] = mapped_column(ARRAY(String), default=None) 

217 expertise: Mapped[str | None] = mapped_column(String, default=None) 

218 

219 ## Motivations (how they heard about us and what they want to do) 

220 filled_motivations: Mapped[bool] = mapped_column(Boolean, server_default=expression.false(), default=False) 

221 heard_about_couchers: Mapped[str | None] = mapped_column(String, default=None) 

222 signup_motivations: Mapped[list[str]] = mapped_column(ARRAY(String), server_default="{}", default_factory=list) 

223 

224 invite_code_id: Mapped[str | None] = mapped_column(ForeignKey("invite_codes.id"), default=None) 

225 

226 @hybrid_property 

227 def token_is_valid(self) -> Any: 

228 return (self.email_token != None) & (self.email_token_expiry >= now()) # type: ignore[operator] 

229 

230 @hybrid_property 

231 def account_is_filled(self) -> Any: 

232 return ( 

233 (self.username != None) 

234 & (self.birthdate != None) 

235 & (self.gender != None) 

236 & (self.hosting_status != None) 

237 & (self.city != None) 

238 & (self.geom != None) 

239 & (self.geom_radius != None) 

240 & (self.accepted_tos != None) 

241 & (self.opt_out_of_newsletter != None) 

242 ) 

243 

244 @hybrid_property 

245 def is_completed(self) -> Any: 

246 return ( 

247 self.email_verified 

248 & self.account_is_filled 

249 & (self.accepted_community_guidelines == GUIDELINES_VERSION) 

250 & self.filled_motivations 

251 ) 

252 

253 

254class AccountDeletionToken(Base, kw_only=True): 

255 __tablename__ = "account_deletion_tokens" 

256 

257 token: Mapped[str] = mapped_column(String, primary_key=True) 

258 

259 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True) 

260 

261 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

262 expiry: Mapped[datetime] = mapped_column(DateTime(timezone=True)) 

263 

264 user: Mapped[User] = relationship(init=False, backref="account_deletion_tokens") 

265 

266 @hybrid_property 

267 def is_valid(self) -> Any: 

268 return (self.created <= now()) & (self.expiry >= now()) 

269 

270 def __repr__(self) -> str: 

271 return f"AccountDeletionToken(token={self.token}, user_id={self.user_id}, created={self.created}, expiry={self.expiry})" 

272 

273 

274class ClientPlatform(enum.Enum): 

275 web_desktop = enum.auto() 

276 web_mobile = enum.auto() 

277 app_ios = enum.auto() 

278 app_android = enum.auto() 

279 

280 

281class UserActivity(Base, kw_only=True): 

282 """ 

283 User activity: for each unique (user_id, period, ip_address, user_agent, sofa) tuple, keep track of number of api 

284 calls 

285 

286 Used for user "last active" as well as admin stuff 

287 """ 

288 

289 __tablename__ = "user_activity" 

290 

291 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

292 

293 user_id: Mapped[int] = mapped_column(ForeignKey("users.id")) 

294 # the start of a period of time, e.g. 1 hour during which we bin activeness 

295 period: Mapped[datetime] = mapped_column(DateTime(timezone=True)) 

296 

297 # details of the browser, if available 

298 ip_address: Mapped[str | None] = mapped_column(INET, default=None) 

299 user_agent: Mapped[str | None] = mapped_column(String, default=None) 

300 # the sofa cookie, a persistent per-device identifier 

301 sofa: Mapped[str | None] = mapped_column(String, default=None) 

302 

303 # the client platform this activity came from (declared by the client) 

304 client_platform: Mapped[ClientPlatform | None] = mapped_column(Enum(ClientPlatform), default=None) 

305 

306 # count of api calls made with this ip, user_agent, sofa, and period 

307 api_calls: Mapped[int] = mapped_column(Integer, default=0) 

308 

309 __table_args__ = ( 

310 # helps look up this tuple quickly 

311 Index( 

312 "ix_user_activity_user_id_period_ip_address_user_agent_sofa", 

313 user_id, 

314 period, 

315 ip_address, 

316 user_agent, 

317 sofa, 

318 unique=True, 

319 # treat NULL ip_address/user_agent/sofa as equal so the upsert dedupes rows with absent columns 

320 postgresql_nulls_not_distinct=True, 

321 ), 

322 ) 

323 

324 

325class InviteCode(Base, kw_only=True): 

326 __tablename__ = "invite_codes" 

327 

328 id: Mapped[str] = mapped_column(String, primary_key=True) 

329 creator_user_id: Mapped[int] = mapped_column(Integer, ForeignKey("users.id")) 

330 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=func.now(), init=False) 

331 disabled: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None) 

332 

333 creator: Mapped[User] = relationship(init=False, foreign_keys=[creator_user_id]) 

334 

335 

336class ContentReport(Base, kw_only=True): 

337 """ 

338 A piece of content reported to admins 

339 """ 

340 

341 __tablename__ = "content_reports" 

342 

343 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

344 

345 time: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

346 

347 # the user who reported or flagged the content 

348 reporting_user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True) 

349 

350 # reason, e.g. spam, inappropriate, etc 

351 reason: Mapped[str] = mapped_column(String) 

352 # a short description 

353 description: Mapped[str] = mapped_column(String) 

354 

355 # a reference to the content, see //docs/content_ref.md 

356 content_ref: Mapped[str] = mapped_column(String) 

357 # the author of the content (e.g. the user who wrote the comment itself) 

358 author_user_id: Mapped[int] = mapped_column(ForeignKey("users.id")) 

359 

360 # details of the browser, if available 

361 user_agent: Mapped[str] = mapped_column(String) 

362 # the URL the user was on when reporting the content 

363 page: Mapped[str] = mapped_column(String) 

364 

365 # see comments above for reporting vs author 

366 reporting_user: Mapped[User] = relationship(init=False, foreign_keys="ContentReport.reporting_user_id") 

367 author_user: Mapped[User] = relationship(init=False, foreign_keys="ContentReport.author_user_id") 

368 

369 

370class Email(Base, kw_only=True): 

371 """ 

372 Table of all dispatched emails for debugging purposes, etc. 

373 """ 

374 

375 __tablename__ = "emails" 

376 

377 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

378 

379 # the X-Couchers-ID header of the sent email 

380 message_id: Mapped[str] = mapped_column(String, unique=True) 

381 

382 # timezone should always be UTC 

383 time: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False, index=True) 

384 

385 sender_name: Mapped[str] = mapped_column(String) 

386 sender_email: Mapped[str] = mapped_column(String) 

387 

388 recipient: Mapped[str] = mapped_column(String) 

389 subject: Mapped[str] = mapped_column(String) 

390 

391 plain: Mapped[str] = mapped_column(String) 

392 html: Mapped[str] = mapped_column(String) 

393 

394 list_unsubscribe_header: Mapped[str | None] = mapped_column(String, default=None) 

395 source_data: Mapped[str | None] = mapped_column(String, default=None) 

396 

397 __table_args__ = (Index("ix_emails_recipient_time", recipient, time.desc()),) 

398 

399 

400class SMS(Base, kw_only=True): 

401 """ 

402 Table of all sent SMSs for debugging purposes, etc. 

403 """ 

404 

405 __tablename__ = "smss" 

406 

407 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

408 

409 # timezone should always be UTC 

410 time: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

411 # AWS message id 

412 message_id: Mapped[str] = mapped_column(String) 

413 

414 # the SMS sender ID sent to AWS, name that the SMS appears to come from 

415 sms_sender_id: Mapped[str] = mapped_column(String) 

416 number: Mapped[str] = mapped_column(String) 

417 message: Mapped[str] = mapped_column(String) 

418 

419 

420class ReferenceType(enum.Enum): 

421 friend = enum.auto() 

422 surfed = enum.auto() # The "from" user surfed with the "to" user 

423 hosted = enum.auto() # The "from" user hosted the "to" user 

424 

425 

426class Reference(Base, kw_only=True): 

427 """ 

428 Reference from one user to another 

429 """ 

430 

431 __tablename__ = "references" 

432 __moderation_author_column__ = "from_user_id" 

433 __moderation_object_type__ = ModerationObjectType.reference 

434 

435 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

436 # timezone should always be UTC 

437 time: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

438 

439 from_user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True) 

440 to_user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True) 

441 

442 reference_type: Mapped[ReferenceType] = mapped_column(Enum(ReferenceType)) 

443 

444 moderation_state_id: Mapped[int] = mapped_column(ForeignKey("moderation_states.id"), index=True) 

445 

446 host_request_id: Mapped[int | None] = mapped_column(ForeignKey("host_requests.id"), default=None) 

447 

448 text: Mapped[str] = mapped_column(String) # plain text 

449 # text that's only visible to mods 

450 private_text: Mapped[str | None] = mapped_column(String, default=None) # plain text 

451 

452 rating: Mapped[float] = mapped_column(Float) 

453 was_appropriate: Mapped[bool] = mapped_column(Boolean) 

454 

455 from_user: Mapped[User] = relationship(init=False, backref="references_from", foreign_keys="Reference.from_user_id") 

456 to_user: Mapped[User] = relationship(init=False, backref="references_to", foreign_keys="Reference.to_user_id") 

457 

458 host_request: Mapped[HostRequest | None] = relationship(init=False, backref="references") 

459 moderation_state: Mapped[ModerationState] = relationship(init=False) 

460 

461 __table_args__ = ( 

462 # Rating must be between 0 and 1, inclusive 

463 CheckConstraint( 

464 "rating BETWEEN 0 AND 1", 

465 name="rating_between_0_and_1", 

466 ), 

467 # Has host_request_id or it's a friend reference 

468 CheckConstraint( 

469 "(host_request_id IS NOT NULL) <> (reference_type = 'friend')", 

470 name="host_request_id_xor_friend_reference", 

471 ), 

472 # Each user can leave at most one friend reference to another user 

473 Index( 

474 "ix_references_unique_friend_reference", 

475 from_user_id, 

476 to_user_id, 

477 reference_type, 

478 unique=True, 

479 postgresql_where=(reference_type == ReferenceType.friend), 

480 ), 

481 # Each user can leave at most one reference to another user for each stay 

482 Index( 

483 "ix_references_unique_per_host_request", 

484 from_user_id, 

485 to_user_id, 

486 host_request_id, 

487 unique=True, 

488 postgresql_where=(host_request_id != None), 

489 ), 

490 ) 

491 

492 @property 

493 def should_report(self) -> bool: 

494 """ 

495 If this evaluates to true, we send a report to the moderation team. 

496 """ 

497 return bool(self.rating <= 0.4 or not self.was_appropriate or self.private_text) 

498 

499 

500class UserBlock(Base, kw_only=True): 

501 """ 

502 Table of blocked users 

503 """ 

504 

505 __tablename__ = "user_blocks" 

506 

507 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

508 

509 blocking_user_id: Mapped[int] = mapped_column(ForeignKey("users.id")) 

510 blocked_user_id: Mapped[int] = mapped_column(ForeignKey("users.id")) 

511 time_blocked: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

512 

513 blocking_user: Mapped[User] = relationship(init=False, foreign_keys="UserBlock.blocking_user_id") 

514 blocked_user: Mapped[User] = relationship(init=False, foreign_keys="UserBlock.blocked_user_id") 

515 

516 __table_args__ = ( 

517 UniqueConstraint("blocking_user_id", "blocked_user_id"), 

518 Index("ix_user_blocks_blocking_user_id", blocking_user_id, blocked_user_id), 

519 Index("ix_user_blocks_blocked_user_id", blocked_user_id, blocking_user_id), 

520 ) 

521 

522 

523class AccountDeletionReason(Base, kw_only=True): 

524 __tablename__ = "account_deletion_reason" 

525 

526 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

527 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

528 user_id: Mapped[int] = mapped_column(ForeignKey("users.id")) 

529 reason: Mapped[str | None] = mapped_column(String, default=None) 

530 

531 user: Mapped[User] = relationship(init=False) 

532 

533 

534class ModerationUserList(Base, kw_only=True): 

535 """ 

536 Represents a list of users listed together by a moderator 

537 """ 

538 

539 __tablename__ = "moderation_user_lists" 

540 

541 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

542 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

543 

544 users: Mapped[list[User]] = relationship( 

545 init=False, secondary="moderation_user_list_members", back_populates="moderation_user_lists" 

546 ) 

547 

548 

549class ModerationUserListMember(Base, kw_only=True): 

550 """ 

551 Association table for many-to-many relationship between users and moderation_user_lists 

552 """ 

553 

554 __tablename__ = "moderation_user_list_members" 

555 

556 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), primary_key=True) 

557 moderation_list_id: Mapped[int] = mapped_column(ForeignKey("moderation_user_lists.id"), primary_key=True) 

558 

559 

560class AntiBotLog(Base, kw_only=True): 

561 __tablename__ = "antibot_logs" 

562 

563 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

564 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

565 user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), default=None) 

566 

567 ip_address: Mapped[str | None] = mapped_column(String, default=None) 

568 user_agent: Mapped[str | None] = mapped_column(String, default=None) 

569 

570 action: Mapped[str] = mapped_column(String) 

571 token: Mapped[str] = mapped_column(String) 

572 

573 score: Mapped[float] = mapped_column(Float) 

574 provider_data: Mapped[dict[str, Any]] = mapped_column(JSON) 

575 

576 

577class RateLimitAction(enum.Enum): 

578 """Possible user actions which can be rate limited.""" 

579 

580 host_request = "host request" 

581 friend_request = "friend request" 

582 chat_initiation = "chat initiation" 

583 

584 

585class RateLimitViolation(Base, kw_only=True): 

586 __tablename__ = "rate_limit_violations" 

587 

588 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

589 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

590 user_id: Mapped[int] = mapped_column(ForeignKey("users.id")) 

591 action: Mapped[RateLimitAction] = mapped_column(Enum(RateLimitAction)) 

592 is_hard_limit: Mapped[bool] = mapped_column(Boolean) 

593 

594 user: Mapped[User] = relationship(init=False) 

595 

596 __table_args__ = ( 

597 # Fast lookup for rate limits in interval 

598 Index("ix_rate_limits_by_user", user_id, action, is_hard_limit, created), 

599 ) 

600 

601 

602class Volunteer(Base, kw_only=True): 

603 __tablename__ = "volunteers" 

604 

605 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

606 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), unique=True) 

607 

608 display_name: Mapped[str | None] = mapped_column(String, default=None) 

609 display_location: Mapped[str | None] = mapped_column(String, default=None) 

610 

611 role: Mapped[str] = mapped_column(String) 

612 

613 # custom sort order on team page, sorted ascending 

614 sort_key: Mapped[float | None] = mapped_column(Float, default=None) 

615 

616 started_volunteering: Mapped[date] = mapped_column(Date, server_default=text("CURRENT_DATE"), init=False) 

617 stopped_volunteering: Mapped[date | None] = mapped_column(Date, default=None) 

618 

619 link_type: Mapped[str | None] = mapped_column(String, default=None) 

620 link_text: Mapped[str | None] = mapped_column(String, default=None) 

621 link_url: Mapped[str | None] = mapped_column(String, default=None) 

622 

623 show_on_team_page: Mapped[bool] = mapped_column(Boolean, server_default=expression.true()) 

624 

625 __table_args__ = ( 

626 # Link type, text, url should all be null or all not be null 

627 CheckConstraint( 

628 "(link_type IS NULL) = (link_text IS NULL) AND (link_type IS NULL) = (link_url IS NULL)", 

629 name="link_type_text", 

630 ), 

631 )