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 

29from sqlalchemy.sql.elements import ColumnElement 

30 

31from couchers.constants import GUIDELINES_VERSION 

32from couchers.models.base import Base, Geom 

33from couchers.models.moderation import ModerationObjectType 

34from couchers.models.users import HostingStatus 

35from couchers.utils import now 

36 

37if TYPE_CHECKING: 

38 from couchers.models import HostRequest, User 

39 from couchers.models.moderation import ModerationState 

40 

41 

42class UserBadge(Base, kw_only=True): 

43 """ 

44 A badge on a user's profile 

45 """ 

46 

47 __tablename__ = "user_badges" 

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

49 

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

51 

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

53 # corresponds to "id" in badges.json 

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

55 

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

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

58 

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

60 

61 

62class FriendStatus(enum.Enum): 

63 pending = enum.auto() 

64 accepted = enum.auto() 

65 rejected = enum.auto() 

66 cancelled = enum.auto() 

67 

68 

69class FriendRelationship(Base, kw_only=True): 

70 """ 

71 Friendship relations between users 

72 

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

74 """ 

75 

76 __tablename__ = "friend_relationships" 

77 __moderation_author_column__ = "from_user_id" 

78 __moderation_object_type__ = ModerationObjectType.friend_request 

79 __moderation_has_own_visibility_mechanism__ = False 

80 

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

82 

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

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

85 

86 # Unified Moderation System 

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

88 

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

90 

91 # timezones should always be UTC 

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

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

94 

95 from_user: Mapped[User] = relationship( 

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

97 ) 

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

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

100 

101 __table_args__ = ( 

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

103 Index( 

104 "ix_friend_relationships_status_to_from", 

105 status, 

106 to_user_id, 

107 from_user_id, 

108 ), 

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

110 Index( 

111 "uq_friend_relationships_active_pair", 

112 func.least(from_user_id, to_user_id), 

113 func.greatest(from_user_id, to_user_id), 

114 unique=True, 

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

116 ), 

117 ) 

118 

119 

120class ContributeOption(enum.Enum): 

121 yes = enum.auto() 

122 maybe = enum.auto() 

123 no = enum.auto() 

124 

125 

126class ContributorForm(Base, kw_only=True): 

127 """ 

128 Someone filled in the contributor form 

129 """ 

130 

131 __tablename__ = "contributor_forms" 

132 

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

134 

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

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

137 

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

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

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

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

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

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

144 

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

146 

147 @hybrid_property 

148 def is_filled(self) -> Any: 

149 """ 

150 Whether the form counts as having been filled 

151 """ 

152 return ( 

153 (self.ideas != None) 

154 | (self.features != None) 

155 | (self.experience != None) 

156 | (self.contribute != None) 

157 | (self.contribute_ways != []) 

158 | (self.expertise != None) 

159 ) 

160 

161 @property 

162 def should_notify(self) -> bool: 

163 """ 

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

165 

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

167 """ 

168 return False 

169 

170 

171class SignupFlow(Base, kw_only=True): 

172 """ 

173 Signup flows/incomplete users 

174 

175 Coinciding fields have the same meaning as in User 

176 """ 

177 

178 __tablename__ = "signup_flows" 

179 

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

181 

182 # housekeeping 

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

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

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

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

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

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

189 

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

191 

192 ## Basic 

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

194 # TODO: unique across both tables 

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

196 # TODO: invitation, attribution 

197 

198 ## Account 

199 # TODO: unique across both tables 

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

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

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

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

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

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

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

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

208 

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

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

211 

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

213 

214 ## Feedback (now unused) 

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

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

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

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

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

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

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

222 

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

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

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

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

227 

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

229 

230 @hybrid_property 

231 def token_is_valid(self) -> bool: 

232 # `and` so a flow without a token answers False instead of raising on the None comparison 

233 return self.email_token is not None and self.email_token_expiry is not None and self.email_token_expiry >= now() 

234 

235 @token_is_valid.inplace.expression 

236 @classmethod 

237 def _token_is_valid_expression(cls) -> ColumnElement[bool]: 

238 return (cls.email_token != None) & (cls.email_token_expiry >= now()) 

239 

240 @hybrid_property 

241 def account_is_filled(self) -> Any: 

242 return ( 

243 (self.username != None) 

244 & (self.birthdate != None) 

245 & (self.gender != None) 

246 & (self.hosting_status != None) 

247 & (self.city != None) 

248 & (self.geom != None) 

249 & (self.geom_radius != None) 

250 & (self.accepted_tos != None) 

251 & (self.opt_out_of_newsletter != None) 

252 ) 

253 

254 @hybrid_property 

255 def is_completed(self) -> Any: 

256 return ( 

257 self.email_verified 

258 & self.account_is_filled 

259 & (self.accepted_community_guidelines == GUIDELINES_VERSION) 

260 & self.filled_motivations 

261 ) 

262 

263 

264class AccountDeletionToken(Base, kw_only=True): 

265 __tablename__ = "account_deletion_tokens" 

266 

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

268 

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

270 

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

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

273 

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

275 

276 @hybrid_property 

277 def is_valid(self) -> Any: 

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

279 

280 def __repr__(self) -> str: 

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

282 

283 

284class ClientPlatform(enum.Enum): 

285 web_desktop = enum.auto() 

286 web_mobile = enum.auto() 

287 app_ios = enum.auto() 

288 app_android = enum.auto() 

289 

290 

291class UserActivity(Base, kw_only=True): 

292 """ 

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

294 calls 

295 

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

297 """ 

298 

299 __tablename__ = "user_activity" 

300 

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

302 

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

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

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

306 

307 # details of the browser, if available 

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

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

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

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

312 

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

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

315 

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

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

318 

319 __table_args__ = ( 

320 # helps look up this tuple quickly 

321 Index( 

322 "ix_user_activity_user_id_period_ip_address_user_agent_sofa", 

323 user_id, 

324 period, 

325 ip_address, 

326 user_agent, 

327 sofa, 

328 unique=True, 

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

330 postgresql_nulls_not_distinct=True, 

331 ), 

332 Index("ix_user_activity_sofa", sofa), 

333 Index("ix_user_activity_ip_address", ip_address), 

334 ) 

335 

336 

337class InviteCode(Base, kw_only=True): 

338 __tablename__ = "invite_codes" 

339 

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

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

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

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

344 

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

346 

347 

348class ContentReport(Base, kw_only=True): 

349 """ 

350 A piece of content reported to admins 

351 """ 

352 

353 __tablename__ = "content_reports" 

354 

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

356 

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

358 

359 # the user who reported or flagged the content 

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

361 

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

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

364 # a short description 

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

366 

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

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

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

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

371 

372 # details of the browser, if available 

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

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

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

376 

377 # see comments above for reporting vs author 

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

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

380 

381 

382class Email(Base, kw_only=True): 

383 """ 

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

385 """ 

386 

387 __tablename__ = "emails" 

388 

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

390 

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

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

393 

394 # timezone should always be UTC 

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

396 

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

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

399 

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

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

402 

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

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

405 

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

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

408 

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

410 

411 

412class SMS(Base, kw_only=True): 

413 """ 

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

415 """ 

416 

417 __tablename__ = "smss" 

418 

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

420 

421 # timezone should always be UTC 

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

423 # AWS message id 

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

425 

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

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

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

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

430 

431 

432class ReferenceType(enum.Enum): 

433 friend = enum.auto() 

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

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

436 

437 

438class Reference(Base, kw_only=True): 

439 """ 

440 Reference from one user to another 

441 """ 

442 

443 __tablename__ = "references" 

444 __moderation_author_column__ = "from_user_id" 

445 __moderation_object_type__ = ModerationObjectType.reference 

446 __moderation_has_own_visibility_mechanism__ = False 

447 

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

449 # timezone should always be UTC 

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

451 

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

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

454 

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

456 

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

458 

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

460 

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

462 # text that's only visible to mods 

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

464 

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

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

467 

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

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

470 

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

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

473 

474 __table_args__ = ( 

475 # Rating must be between 0 and 1, inclusive 

476 CheckConstraint( 

477 "rating BETWEEN 0 AND 1", 

478 name="rating_between_0_and_1", 

479 ), 

480 # Has host_request_id or it's a friend reference 

481 CheckConstraint( 

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

483 name="host_request_id_xor_friend_reference", 

484 ), 

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

486 Index( 

487 "ix_references_unique_friend_reference", 

488 from_user_id, 

489 to_user_id, 

490 reference_type, 

491 unique=True, 

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

493 ), 

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

495 Index( 

496 "ix_references_unique_per_host_request", 

497 from_user_id, 

498 to_user_id, 

499 host_request_id, 

500 unique=True, 

501 postgresql_where=(host_request_id != None), 

502 ), 

503 ) 

504 

505 @property 

506 def should_report(self) -> bool: 

507 """ 

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

509 """ 

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

511 

512 

513class UserBlock(Base, kw_only=True): 

514 """ 

515 Table of blocked users 

516 """ 

517 

518 __tablename__ = "user_blocks" 

519 

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

521 

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

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

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

525 

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

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

528 

529 __table_args__ = ( 

530 UniqueConstraint("blocking_user_id", "blocked_user_id"), 

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

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

533 ) 

534 

535 

536class AccountDeletionReason(Base, kw_only=True): 

537 __tablename__ = "account_deletion_reason" 

538 

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

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

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

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

543 

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

545 

546 

547class ModerationUserList(Base, kw_only=True): 

548 """ 

549 Represents a list of users listed together by a moderator 

550 """ 

551 

552 __tablename__ = "moderation_user_lists" 

553 

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

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

556 

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

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

559 ) 

560 

561 

562class ModerationUserListMember(Base, kw_only=True): 

563 """ 

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

565 """ 

566 

567 __tablename__ = "moderation_user_list_members" 

568 

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

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

571 

572 

573class AntiBotLog(Base, kw_only=True): 

574 __tablename__ = "antibot_logs" 

575 

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

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

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

579 

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

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

582 

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

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

585 

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

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

588 

589 

590class RateLimitAction(enum.Enum): 

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

592 

593 host_request = "host request" 

594 friend_request = "friend request" 

595 chat_initiation = "chat initiation" 

596 

597 

598class RateLimitViolation(Base, kw_only=True): 

599 __tablename__ = "rate_limit_violations" 

600 

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

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

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

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

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

606 

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

608 

609 __table_args__ = ( 

610 # Fast lookup for rate limits in interval 

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

612 ) 

613 

614 

615class Volunteer(Base, kw_only=True): 

616 __tablename__ = "volunteers" 

617 

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

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

620 

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

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

623 

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

625 

626 # custom sort order on team page, sorted ascending 

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

628 

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

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

631 

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

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

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

635 

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

637 

638 __table_args__ = ( 

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

640 CheckConstraint( 

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

642 name="link_type_text", 

643 ), 

644 )