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.users import HostingStatus 

33from couchers.utils import now 

34 

35if TYPE_CHECKING: 

36 from couchers.models import HostRequest, User 

37 

38 

39class UserBadge(Base, kw_only=True): 

40 """ 

41 A badge on a user's profile 

42 """ 

43 

44 __tablename__ = "user_badges" 

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

46 

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

48 

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

50 # corresponds to "id" in badges.json 

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

52 

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

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

55 

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

57 

58 

59class FriendStatus(enum.Enum): 

60 pending = enum.auto() 

61 accepted = enum.auto() 

62 rejected = enum.auto() 

63 cancelled = enum.auto() 

64 

65 

66class FriendRelationship(Base, kw_only=True): 

67 """ 

68 Friendship relations between users 

69 

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

71 TODO: constraint on only one row per user pair where accepted or pending 

72 """ 

73 

74 __tablename__ = "friend_relationships" 

75 

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

77 

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

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

80 

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

82 

83 # timezones should always be UTC 

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

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

86 

87 from_user: Mapped[User] = relationship( 

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

89 ) 

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

91 

92 __table_args__ = ( 

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

94 Index( 

95 "ix_friend_relationships_status_to_from", 

96 status, 

97 to_user_id, 

98 from_user_id, 

99 ), 

100 ) 

101 

102 

103class ContributeOption(enum.Enum): 

104 yes = enum.auto() 

105 maybe = enum.auto() 

106 no = enum.auto() 

107 

108 

109class ContributorForm(Base, kw_only=True): 

110 """ 

111 Someone filled in the contributor form 

112 """ 

113 

114 __tablename__ = "contributor_forms" 

115 

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

117 

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

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

120 

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

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

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

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

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

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

127 

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

129 

130 @hybrid_property 

131 def is_filled(self) -> Any: 

132 """ 

133 Whether the form counts as having been filled 

134 """ 

135 return ( 

136 (self.ideas != None) 

137 | (self.features != None) 

138 | (self.experience != None) 

139 | (self.contribute != None) 

140 | (self.contribute_ways != []) 

141 | (self.expertise != None) 

142 ) 

143 

144 @property 

145 def should_notify(self) -> bool: 

146 """ 

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

148 

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

150 """ 

151 return False 

152 

153 

154class SignupFlow(Base, kw_only=True): 

155 """ 

156 Signup flows/incomplete users 

157 

158 Coinciding fields have the same meaning as in User 

159 """ 

160 

161 __tablename__ = "signup_flows" 

162 

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

164 

165 # housekeeping 

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

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

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

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

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

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

172 

173 ## Basic 

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

175 # TODO: unique across both tables 

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

177 # TODO: invitation, attribution 

178 

179 ## Account 

180 # TODO: unique across both tables 

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

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

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

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

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

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

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

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

189 

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

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

192 

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

194 

195 ## Feedback (now unused) 

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

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

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

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

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

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

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

203 

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

205 

206 @hybrid_property 

207 def token_is_valid(self) -> Any: 

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

209 

210 @hybrid_property 

211 def account_is_filled(self) -> Any: 

212 return ( 

213 (self.username != None) 

214 & (self.birthdate != None) 

215 & (self.gender != None) 

216 & (self.hosting_status != None) 

217 & (self.city != None) 

218 & (self.geom != None) 

219 & (self.geom_radius != None) 

220 & (self.accepted_tos != None) 

221 & (self.opt_out_of_newsletter != None) 

222 ) 

223 

224 @hybrid_property 

225 def is_completed(self) -> Any: 

226 return self.email_verified & self.account_is_filled & (self.accepted_community_guidelines == GUIDELINES_VERSION) 

227 

228 

229class AccountDeletionToken(Base, kw_only=True): 

230 __tablename__ = "account_deletion_tokens" 

231 

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

233 

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

235 

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

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

238 

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

240 

241 @hybrid_property 

242 def is_valid(self) -> Any: 

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

244 

245 def __repr__(self) -> str: 

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

247 

248 

249class UserActivity(Base, kw_only=True): 

250 """ 

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

252 

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

254 """ 

255 

256 __tablename__ = "user_activity" 

257 

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

259 

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

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

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

263 

264 # details of the browser, if available 

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

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

267 

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

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

270 

271 __table_args__ = ( 

272 # helps look up this tuple quickly 

273 Index( 

274 "ix_user_activity_user_id_period_ip_address_user_agent", 

275 user_id, 

276 period, 

277 ip_address, 

278 user_agent, 

279 unique=True, 

280 ), 

281 ) 

282 

283 

284class InviteCode(Base, kw_only=True): 

285 __tablename__ = "invite_codes" 

286 

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

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

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

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

291 

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

293 

294 

295class ContentReport(Base, kw_only=True): 

296 """ 

297 A piece of content reported to admins 

298 """ 

299 

300 __tablename__ = "content_reports" 

301 

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

303 

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

305 

306 # the user who reported or flagged the content 

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

308 

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

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

311 # a short description 

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

313 

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

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

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

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

318 

319 # details of the browser, if available 

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

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

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

323 

324 # see comments above for reporting vs author 

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

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

327 

328 

329class Email(Base, kw_only=True): 

330 """ 

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

332 """ 

333 

334 __tablename__ = "emails" 

335 

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

337 

338 # timezone should always be UTC 

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

340 

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

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

343 

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

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

346 

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

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

349 

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

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

352 

353 

354class SMS(Base, kw_only=True): 

355 """ 

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

357 """ 

358 

359 __tablename__ = "smss" 

360 

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

362 

363 # timezone should always be UTC 

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

365 # AWS message id 

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

367 

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

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

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

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

372 

373 

374class ReferenceType(enum.Enum): 

375 friend = enum.auto() 

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

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

378 

379 

380class Reference(Base, kw_only=True): 

381 """ 

382 Reference from one user to another 

383 """ 

384 

385 __tablename__ = "references" 

386 

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

388 # timezone should always be UTC 

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

390 

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

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

393 

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

395 

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

397 

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

399 # text that's only visible to mods 

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

401 

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

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

404 

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

406 

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

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

409 

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

411 

412 __table_args__ = ( 

413 # Rating must be between 0 and 1, inclusive 

414 CheckConstraint( 

415 "rating BETWEEN 0 AND 1", 

416 name="rating_between_0_and_1", 

417 ), 

418 # Has host_request_id or it's a friend reference 

419 CheckConstraint( 

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

421 name="host_request_id_xor_friend_reference", 

422 ), 

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

424 Index( 

425 "ix_references_unique_friend_reference", 

426 from_user_id, 

427 to_user_id, 

428 reference_type, 

429 unique=True, 

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

431 ), 

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

433 Index( 

434 "ix_references_unique_per_host_request", 

435 from_user_id, 

436 to_user_id, 

437 host_request_id, 

438 unique=True, 

439 postgresql_where=(host_request_id != None), 

440 ), 

441 ) 

442 

443 @property 

444 def should_report(self) -> bool: 

445 """ 

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

447 """ 

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

449 

450 

451class UserBlock(Base, kw_only=True): 

452 """ 

453 Table of blocked users 

454 """ 

455 

456 __tablename__ = "user_blocks" 

457 

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

459 

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

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

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

463 

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

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

466 

467 __table_args__ = ( 

468 UniqueConstraint("blocking_user_id", "blocked_user_id"), 

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

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

471 ) 

472 

473 

474class AccountDeletionReason(Base, kw_only=True): 

475 __tablename__ = "account_deletion_reason" 

476 

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

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

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

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

481 

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

483 

484 

485class ModerationUserList(Base, kw_only=True): 

486 """ 

487 Represents a list of users listed together by a moderator 

488 """ 

489 

490 __tablename__ = "moderation_user_lists" 

491 

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

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

494 

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

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

497 ) 

498 

499 

500class ModerationUserListMember(Base, kw_only=True): 

501 """ 

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

503 """ 

504 

505 __tablename__ = "moderation_user_list_members" 

506 

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

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

509 

510 

511class AntiBotLog(Base, kw_only=True): 

512 __tablename__ = "antibot_logs" 

513 

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

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

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

517 

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

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

520 

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

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

523 

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

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

526 

527 

528class RateLimitAction(enum.Enum): 

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

530 

531 host_request = "host request" 

532 friend_request = "friend request" 

533 chat_initiation = "chat initiation" 

534 

535 

536class RateLimitViolation(Base, kw_only=True): 

537 __tablename__ = "rate_limit_violations" 

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 action: Mapped[RateLimitAction] = mapped_column(Enum(RateLimitAction)) 

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

544 

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

546 

547 __table_args__ = ( 

548 # Fast lookup for rate limits in interval 

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

550 ) 

551 

552 

553class Volunteer(Base, kw_only=True): 

554 __tablename__ = "volunteers" 

555 

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

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

558 

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

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

561 

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

563 

564 # custom sort order on team page, sorted ascending 

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

566 

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

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

569 

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

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

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

573 

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

575 

576 __table_args__ = ( 

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

578 CheckConstraint( 

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

580 name="link_type_text", 

581 ), 

582 )