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

1import enum 

2from datetime import date, datetime 

3from typing import 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 

35 

36class UserBadge(Base): 

37 """ 

38 A badge on a user's profile 

39 """ 

40 

41 __tablename__ = "user_badges" 

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

43 

44 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

45 

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

47 # corresponds to "id" in badges.json 

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

49 

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

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

52 

53 user = relationship("User", backref="badges") 

54 

55 

56class FriendStatus(enum.Enum): 

57 pending = enum.auto() 

58 accepted = enum.auto() 

59 rejected = enum.auto() 

60 cancelled = enum.auto() 

61 

62 

63class FriendRelationship(Base): 

64 """ 

65 Friendship relations between users 

66 

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

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

69 """ 

70 

71 __tablename__ = "friend_relationships" 

72 

73 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

74 

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

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

77 

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

79 

80 # timezones should always be UTC 

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

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

83 

84 from_user = relationship("User", backref="friends_from", foreign_keys="FriendRelationship.from_user_id") 

85 to_user = relationship("User", backref="friends_to", foreign_keys="FriendRelationship.to_user_id") 

86 

87 __table_args__ = ( 

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

89 Index( 

90 "ix_friend_relationships_status_to_from", 

91 status, 

92 to_user_id, 

93 from_user_id, 

94 ), 

95 ) 

96 

97 

98class ContributeOption(enum.Enum): 

99 yes = enum.auto() 

100 maybe = enum.auto() 

101 no = enum.auto() 

102 

103 

104class ContributorForm(Base): 

105 """ 

106 Someone filled in the contributor form 

107 """ 

108 

109 __tablename__ = "contributor_forms" 

110 

111 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

112 

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

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

115 

116 ideas: Mapped[str | None] = mapped_column(String, nullable=True) 

117 features: Mapped[str | None] = mapped_column(String, nullable=True) 

118 experience: Mapped[str | None] = mapped_column(String, nullable=True) 

119 contribute: Mapped[ContributeOption | None] = mapped_column(Enum(ContributeOption), nullable=True) 

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

121 expertise: Mapped[str | None] = mapped_column(String, nullable=True) 

122 

123 user = relationship("User", backref="contributor_forms") 

124 

125 @hybrid_property 

126 def is_filled(self) -> Any: 

127 """ 

128 Whether the form counts as having been filled 

129 """ 

130 return ( 

131 (self.ideas != None) 

132 | (self.features != None) 

133 | (self.experience != None) 

134 | (self.contribute != None) 

135 | (self.contribute_ways != []) 

136 | (self.expertise != None) 

137 ) 

138 

139 @property 

140 def should_notify(self) -> bool: 

141 """ 

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

143 

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

145 """ 

146 return False 

147 

148 

149class SignupFlow(Base): 

150 """ 

151 Signup flows/incomplete users 

152 

153 Coinciding fields have the same meaning as in User 

154 """ 

155 

156 __tablename__ = "signup_flows" 

157 

158 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

159 

160 # housekeeping 

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

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

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

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

165 email_token: Mapped[str | None] = mapped_column(String, nullable=True) 

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

167 

168 ## Basic 

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

170 # TODO: unique across both tables 

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

172 # TODO: invitation, attribution 

173 

174 ## Account 

175 # TODO: unique across both tables 

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

177 hashed_password: Mapped[bytes | None] = mapped_column(Binary, nullable=True) 

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

179 gender: Mapped[str | None] = mapped_column(String, nullable=True) 

180 hosting_status: Mapped[HostingStatus | None] = mapped_column(Enum(HostingStatus), nullable=True) 

181 city: Mapped[str | None] = mapped_column(String, nullable=True) 

182 geom: Mapped[Geom | None] = mapped_column(Geometry(geometry_type="POINT", srid=4326), nullable=True) 

183 geom_radius: Mapped[float | None] = mapped_column(Float, nullable=True) 

184 

185 accepted_tos: Mapped[int | None] = mapped_column(Integer, nullable=True) 

186 accepted_community_guidelines: Mapped[int] = mapped_column(Integer, server_default="0") 

187 

188 opt_out_of_newsletter: Mapped[bool | None] = mapped_column(Boolean, nullable=True) 

189 

190 ## Feedback (now unused) 

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

192 ideas: Mapped[str | None] = mapped_column(String, nullable=True) 

193 features: Mapped[str | None] = mapped_column(String, nullable=True) 

194 experience: Mapped[str | None] = mapped_column(String, nullable=True) 

195 contribute: Mapped[ContributeOption | None] = mapped_column(Enum(ContributeOption), nullable=True) 

196 contribute_ways: Mapped[list[str] | None] = mapped_column(ARRAY(String), nullable=True) 

197 expertise: Mapped[str | None] = mapped_column(String, nullable=True) 

198 

199 invite_code_id: Mapped[str | None] = mapped_column(ForeignKey("invite_codes.id"), nullable=True) 

200 

201 @hybrid_property 

202 def token_is_valid(self) -> Any: 

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

204 

205 @hybrid_property 

206 def account_is_filled(self) -> Any: 

207 return ( 

208 (self.username != None) 

209 & (self.birthdate != None) 

210 & (self.gender != None) 

211 & (self.hosting_status != None) 

212 & (self.city != None) 

213 & (self.geom != None) 

214 & (self.geom_radius != None) 

215 & (self.accepted_tos != None) 

216 & (self.opt_out_of_newsletter != None) 

217 ) 

218 

219 @hybrid_property 

220 def is_completed(self) -> Any: 

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

222 

223 

224class AccountDeletionToken(Base): 

225 __tablename__ = "account_deletion_tokens" 

226 

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

228 

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

230 

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

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

233 

234 user = relationship("User", backref="account_deletion_tokens") 

235 

236 @hybrid_property 

237 def is_valid(self) -> Any: 

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

239 

240 def __repr__(self) -> str: 

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

242 

243 

244class UserActivity(Base): 

245 """ 

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

247 

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

249 """ 

250 

251 __tablename__ = "user_activity" 

252 

253 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

254 

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

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

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

258 

259 # details of the browser, if available 

260 ip_address: Mapped[str | None] = mapped_column(INET, nullable=True) 

261 user_agent: Mapped[str | None] = mapped_column(String, nullable=True) 

262 

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

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

265 

266 __table_args__ = ( 

267 # helps look up this tuple quickly 

268 Index( 

269 "ix_user_activity_user_id_period_ip_address_user_agent", 

270 user_id, 

271 period, 

272 ip_address, 

273 user_agent, 

274 unique=True, 

275 ), 

276 ) 

277 

278 

279class InviteCode(Base): 

280 __tablename__ = "invite_codes" 

281 

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

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

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

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

286 

287 creator = relationship("User", foreign_keys=[creator_user_id]) 

288 

289 

290class ContentReport(Base): 

291 """ 

292 A piece of content reported to admins 

293 """ 

294 

295 __tablename__ = "content_reports" 

296 

297 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

298 

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

300 

301 # the user who reported or flagged the content 

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

303 

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

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

306 # a short description 

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

308 

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

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

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

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

313 

314 # details of the browser, if available 

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

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

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

318 

319 # see comments above for reporting vs author 

320 reporting_user = relationship("User", foreign_keys="ContentReport.reporting_user_id") 

321 author_user = relationship("User", foreign_keys="ContentReport.author_user_id") 

322 

323 

324class Email(Base): 

325 """ 

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

327 """ 

328 

329 __tablename__ = "emails" 

330 

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

332 

333 # timezone should always be UTC 

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

335 

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

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

338 

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

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

341 

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

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

344 

345 list_unsubscribe_header: Mapped[str | None] = mapped_column(String, nullable=True) 

346 source_data: Mapped[str | None] = mapped_column(String, nullable=True) 

347 

348 

349class SMS(Base): 

350 """ 

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

352 """ 

353 

354 __tablename__ = "smss" 

355 

356 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

357 

358 # timezone should always be UTC 

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

360 # AWS message id 

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

362 

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

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

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

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

367 

368 

369class ReferenceType(enum.Enum): 

370 friend = enum.auto() 

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

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

373 

374 

375class Reference(Base): 

376 """ 

377 Reference from one user to another 

378 """ 

379 

380 __tablename__ = "references" 

381 

382 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

383 # timezone should always be UTC 

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

385 

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

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

388 

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

390 

391 host_request_id: Mapped[int | None] = mapped_column(ForeignKey("host_requests.id"), nullable=True) 

392 

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

394 # text that's only visible to mods 

395 private_text: Mapped[str | None] = mapped_column(String, nullable=True) # plain text 

396 

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

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

399 

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

401 

402 from_user = relationship("User", backref="references_from", foreign_keys="Reference.from_user_id") 

403 to_user = relationship("User", backref="references_to", foreign_keys="Reference.to_user_id") 

404 

405 host_request = relationship("HostRequest", backref="references") 

406 

407 __table_args__ = ( 

408 # Rating must be between 0 and 1, inclusive 

409 CheckConstraint( 

410 "rating BETWEEN 0 AND 1", 

411 name="rating_between_0_and_1", 

412 ), 

413 # Has host_request_id or it's a friend reference 

414 CheckConstraint( 

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

416 name="host_request_id_xor_friend_reference", 

417 ), 

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

419 Index( 

420 "ix_references_unique_friend_reference", 

421 from_user_id, 

422 to_user_id, 

423 reference_type, 

424 unique=True, 

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

426 ), 

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

428 Index( 

429 "ix_references_unique_per_host_request", 

430 from_user_id, 

431 to_user_id, 

432 host_request_id, 

433 unique=True, 

434 postgresql_where=(host_request_id != None), 

435 ), 

436 ) 

437 

438 @property 

439 def should_report(self) -> bool: 

440 """ 

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

442 """ 

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

444 

445 

446class UserBlock(Base): 

447 """ 

448 Table of blocked users 

449 """ 

450 

451 __tablename__ = "user_blocks" 

452 

453 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

454 

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

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

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

458 

459 blocking_user = relationship("User", foreign_keys="UserBlock.blocking_user_id") 

460 blocked_user = relationship("User", foreign_keys="UserBlock.blocked_user_id") 

461 

462 __table_args__ = ( 

463 UniqueConstraint("blocking_user_id", "blocked_user_id"), 

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

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

466 ) 

467 

468 

469class AccountDeletionReason(Base): 

470 __tablename__ = "account_deletion_reason" 

471 

472 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

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

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

475 reason: Mapped[str | None] = mapped_column(String, nullable=True) 

476 

477 user = relationship("User") 

478 

479 

480class ModerationUserList(Base): 

481 """ 

482 Represents a list of users listed together by a moderator 

483 """ 

484 

485 __tablename__ = "moderation_user_lists" 

486 

487 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

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

489 

490 # Relationships 

491 users = relationship("User", secondary="moderation_user_list_members", back_populates="moderation_user_lists") 

492 

493 

494class ModerationUserListMember(Base): 

495 """ 

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

497 """ 

498 

499 __tablename__ = "moderation_user_list_members" 

500 

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

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

503 

504 

505class AntiBotLog(Base): 

506 __tablename__ = "antibot_logs" 

507 

508 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

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

510 user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True) 

511 

512 ip_address: Mapped[str | None] = mapped_column(String, nullable=True) 

513 user_agent: Mapped[str | None] = mapped_column(String, nullable=True) 

514 

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

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

517 

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

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

520 

521 

522class RateLimitAction(enum.Enum): 

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

524 

525 host_request = "host request" 

526 friend_request = "friend request" 

527 chat_initiation = "chat initiation" 

528 

529 

530class RateLimitViolation(Base): 

531 __tablename__ = "rate_limit_violations" 

532 

533 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

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

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

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

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

538 

539 user = relationship("User") 

540 

541 __table_args__ = ( 

542 # Fast lookup for rate limits in interval 

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

544 ) 

545 

546 

547class Volunteer(Base): 

548 __tablename__ = "volunteers" 

549 

550 id: Mapped[int] = mapped_column(BigInteger, primary_key=True) 

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

552 

553 display_name: Mapped[str | None] = mapped_column(String, nullable=True) 

554 display_location: Mapped[str | None] = mapped_column(String, nullable=True) 

555 

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

557 

558 # custom sort order on team page, sorted ascending 

559 sort_key: Mapped[float | None] = mapped_column(Float, nullable=True) 

560 

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

562 stopped_volunteering: Mapped[date | None] = mapped_column(Date, nullable=True, default=None) 

563 

564 link_type: Mapped[str | None] = mapped_column(String, nullable=True) 

565 link_text: Mapped[str | None] = mapped_column(String, nullable=True) 

566 link_url: Mapped[str | None] = mapped_column(String, nullable=True) 

567 

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

569 

570 __table_args__ = ( 

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

572 CheckConstraint( 

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

574 name="link_type_text", 

575 ), 

576 )