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

1import enum 

2 

3from geoalchemy2 import Geometry 

4from sqlalchemy import ( 

5 ARRAY, 

6 JSON, 

7 BigInteger, 

8 Boolean, 

9 CheckConstraint, 

10 Column, 

11 Date, 

12 DateTime, 

13 Enum, 

14 Float, 

15 ForeignKey, 

16 Index, 

17 Integer, 

18 String, 

19 UniqueConstraint, 

20 func, 

21 text, 

22) 

23from sqlalchemy import LargeBinary as Binary 

24from sqlalchemy.dialects.postgresql import INET 

25from sqlalchemy.ext.hybrid import hybrid_property 

26from sqlalchemy.orm import relationship 

27from sqlalchemy.sql import expression 

28 

29from couchers.constants import GUIDELINES_VERSION 

30from couchers.models.base import Base 

31from couchers.models.users import HostingStatus 

32from couchers.utils import now 

33 

34 

35class UserBadge(Base): 

36 """ 

37 A badge on a user's profile 

38 """ 

39 

40 __tablename__ = "user_badges" 

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

42 

43 id = Column(BigInteger, primary_key=True) 

44 

45 user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

46 # corresponds to "id" in badges.json 

47 badge_id = Column(String, nullable=False, index=True) 

48 

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

50 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

51 

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

53 

54 

55class FriendStatus(enum.Enum): 

56 pending = enum.auto() 

57 accepted = enum.auto() 

58 rejected = enum.auto() 

59 cancelled = enum.auto() 

60 

61 

62class FriendRelationship(Base): 

63 """ 

64 Friendship relations between users 

65 

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

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

68 """ 

69 

70 __tablename__ = "friend_relationships" 

71 

72 id = Column(BigInteger, primary_key=True) 

73 

74 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

75 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

76 

77 status = Column(Enum(FriendStatus), nullable=False, default=FriendStatus.pending) 

78 

79 # timezones should always be UTC 

80 time_sent = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

81 time_responded = Column(DateTime(timezone=True), nullable=True) 

82 

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

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

85 

86 __table_args__ = ( 

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

88 Index( 

89 "ix_friend_relationships_status_to_from", 

90 status, 

91 to_user_id, 

92 from_user_id, 

93 ), 

94 ) 

95 

96 

97class ContributeOption(enum.Enum): 

98 yes = enum.auto() 

99 maybe = enum.auto() 

100 no = enum.auto() 

101 

102 

103class ContributorForm(Base): 

104 """ 

105 Someone filled in the contributor form 

106 """ 

107 

108 __tablename__ = "contributor_forms" 

109 

110 id = Column(BigInteger, primary_key=True) 

111 

112 user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

113 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

114 

115 ideas = Column(String, nullable=True) 

116 features = Column(String, nullable=True) 

117 experience = Column(String, nullable=True) 

118 contribute = Column(Enum(ContributeOption), nullable=True) 

119 contribute_ways = Column(ARRAY(String), nullable=False) 

120 expertise = Column(String, nullable=True) 

121 

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

123 

124 @hybrid_property 

125 def is_filled(self): 

126 """ 

127 Whether the form counts as having been filled 

128 """ 

129 return ( 

130 (self.ideas != None) 

131 | (self.features != None) 

132 | (self.experience != None) 

133 | (self.contribute != None) 

134 | (self.contribute_ways != []) 

135 | (self.expertise != None) 

136 ) 

137 

138 @property 

139 def should_notify(self): 

140 """ 

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

142 

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

144 """ 

145 return False 

146 

147 

148class SignupFlow(Base): 

149 """ 

150 Signup flows/incomplete users 

151 

152 Coinciding fields have the same meaning as in User 

153 """ 

154 

155 __tablename__ = "signup_flows" 

156 

157 id = Column(BigInteger, primary_key=True) 

158 

159 # housekeeping 

160 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

161 flow_token = Column(String, nullable=False, unique=True) 

162 email_verified = Column(Boolean, nullable=False, default=False) 

163 email_sent = Column(Boolean, nullable=False, default=False) 

164 email_token = Column(String, nullable=True) 

165 email_token_expiry = Column(DateTime(timezone=True), nullable=True) 

166 

167 ## Basic 

168 name = Column(String, nullable=False) 

169 # TODO: unique across both tables 

170 email = Column(String, nullable=False, unique=True) 

171 # TODO: invitation, attribution 

172 

173 ## Account 

174 # TODO: unique across both tables 

175 username = Column(String, nullable=True, unique=True) 

176 hashed_password = Column(Binary, nullable=True) 

177 birthdate = Column(Date, nullable=True) # in the timezone of birthplace 

178 gender = Column(String, nullable=True) 

179 hosting_status = Column(Enum(HostingStatus), nullable=True) 

180 city = Column(String, nullable=True) 

181 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True) 

182 geom_radius = Column(Float, nullable=True) 

183 

184 accepted_tos = Column(Integer, nullable=True) 

185 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0") 

186 

187 opt_out_of_newsletter = Column(Boolean, nullable=True) 

188 

189 ## Feedback (now unused) 

190 filled_feedback = Column(Boolean, nullable=False, default=False) 

191 ideas = Column(String, nullable=True) 

192 features = Column(String, nullable=True) 

193 experience = Column(String, nullable=True) 

194 contribute = Column(Enum(ContributeOption), nullable=True) 

195 contribute_ways = Column(ARRAY(String), nullable=True) 

196 expertise = Column(String, nullable=True) 

197 

198 invite_code_id = Column(ForeignKey("invite_codes.id"), nullable=True) 

199 

200 @hybrid_property 

201 def token_is_valid(self): 

202 return (self.email_token != None) & (self.email_token_expiry >= now()) 

203 

204 @hybrid_property 

205 def account_is_filled(self): 

206 return ( 

207 (self.username != None) 

208 & (self.birthdate != None) 

209 & (self.gender != None) 

210 & (self.hosting_status != None) 

211 & (self.city != None) 

212 & (self.geom != None) 

213 & (self.geom_radius != None) 

214 & (self.accepted_tos != None) 

215 & (self.opt_out_of_newsletter != None) 

216 ) 

217 

218 @hybrid_property 

219 def is_completed(self): 

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

221 

222 

223class AccountDeletionToken(Base): 

224 __tablename__ = "account_deletion_tokens" 

225 

226 token = Column(String, primary_key=True) 

227 

228 user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

229 

230 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

231 expiry = Column(DateTime(timezone=True), nullable=False) 

232 

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

234 

235 @hybrid_property 

236 def is_valid(self): 

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

238 

239 def __repr__(self): 

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

241 

242 

243class UserActivity(Base): 

244 """ 

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

246 

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

248 """ 

249 

250 __tablename__ = "user_activity" 

251 

252 id = Column(BigInteger, primary_key=True) 

253 

254 user_id = Column(ForeignKey("users.id"), nullable=False) 

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

256 period = Column(DateTime(timezone=True), nullable=False) 

257 

258 # details of the browser, if available 

259 ip_address = Column(INET, nullable=True) 

260 user_agent = Column(String, nullable=True) 

261 

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

263 api_calls = Column(Integer, nullable=False, default=0) 

264 

265 __table_args__ = ( 

266 # helps look up this tuple quickly 

267 Index( 

268 "ix_user_activity_user_id_period_ip_address_user_agent", 

269 user_id, 

270 period, 

271 ip_address, 

272 user_agent, 

273 unique=True, 

274 ), 

275 ) 

276 

277 

278class InviteCode(Base): 

279 __tablename__ = "invite_codes" 

280 

281 id = Column(String, primary_key=True) 

282 creator_user_id = Column(Integer, ForeignKey("users.id"), nullable=False) 

283 created = Column(DateTime(timezone=True), nullable=False, default=func.now()) 

284 disabled = Column(DateTime(timezone=True), nullable=True) 

285 

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

287 

288 

289class ContentReport(Base): 

290 """ 

291 A piece of content reported to admins 

292 """ 

293 

294 __tablename__ = "content_reports" 

295 

296 id = Column(BigInteger, primary_key=True) 

297 

298 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

299 

300 # the user who reported or flagged the content 

301 reporting_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

302 

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

304 reason = Column(String, nullable=False) 

305 # a short description 

306 description = Column(String, nullable=False) 

307 

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

309 content_ref = Column(String, nullable=False) 

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

311 author_user_id = Column(ForeignKey("users.id"), nullable=False) 

312 

313 # details of the browser, if available 

314 user_agent = Column(String, nullable=False) 

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

316 page = Column(String, nullable=False) 

317 

318 # see comments above for reporting vs author 

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

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

321 

322 

323class Email(Base): 

324 """ 

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

326 """ 

327 

328 __tablename__ = "emails" 

329 

330 id = Column(String, primary_key=True) 

331 

332 # timezone should always be UTC 

333 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

334 

335 sender_name = Column(String, nullable=False) 

336 sender_email = Column(String, nullable=False) 

337 

338 recipient = Column(String, nullable=False) 

339 subject = Column(String, nullable=False) 

340 

341 plain = Column(String, nullable=False) 

342 html = Column(String, nullable=False) 

343 

344 list_unsubscribe_header = Column(String, nullable=True) 

345 source_data = Column(String, nullable=True) 

346 

347 

348class SMS(Base): 

349 """ 

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

351 """ 

352 

353 __tablename__ = "smss" 

354 

355 id = Column(BigInteger, primary_key=True) 

356 

357 # timezone should always be UTC 

358 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

359 # AWS message id 

360 message_id = Column(String, nullable=False) 

361 

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

363 sms_sender_id = Column(String, nullable=False) 

364 number = Column(String, nullable=False) 

365 message = Column(String, nullable=False) 

366 

367 

368class ReferenceType(enum.Enum): 

369 friend = enum.auto() 

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

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

372 

373 

374class Reference(Base): 

375 """ 

376 Reference from one user to another 

377 """ 

378 

379 __tablename__ = "references" 

380 

381 id = Column(BigInteger, primary_key=True) 

382 # timezone should always be UTC 

383 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

384 

385 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

386 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

387 

388 reference_type = Column(Enum(ReferenceType), nullable=False) 

389 

390 host_request_id = Column(ForeignKey("host_requests.id"), nullable=True) 

391 

392 text = Column(String, nullable=False) # plain text 

393 # text that's only visible to mods 

394 private_text = Column(String, nullable=True) # plain text 

395 

396 rating = Column(Float, nullable=False) 

397 was_appropriate = Column(Boolean, nullable=False) 

398 

399 is_deleted = Column(Boolean, nullable=False, default=False, server_default=expression.false()) 

400 

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

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

403 

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

405 

406 __table_args__ = ( 

407 # Rating must be between 0 and 1, inclusive 

408 CheckConstraint( 

409 "rating BETWEEN 0 AND 1", 

410 name="rating_between_0_and_1", 

411 ), 

412 # Has host_request_id or it's a friend reference 

413 CheckConstraint( 

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

415 name="host_request_id_xor_friend_reference", 

416 ), 

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

418 Index( 

419 "ix_references_unique_friend_reference", 

420 from_user_id, 

421 to_user_id, 

422 reference_type, 

423 unique=True, 

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

425 ), 

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

427 Index( 

428 "ix_references_unique_per_host_request", 

429 from_user_id, 

430 to_user_id, 

431 host_request_id, 

432 unique=True, 

433 postgresql_where=(host_request_id != None), 

434 ), 

435 ) 

436 

437 @property 

438 def should_report(self): 

439 """ 

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

441 """ 

442 return self.rating <= 0.4 or not self.was_appropriate or self.private_text 

443 

444 

445class UserBlock(Base): 

446 """ 

447 Table of blocked users 

448 """ 

449 

450 __tablename__ = "user_blocks" 

451 

452 id = Column(BigInteger, primary_key=True) 

453 

454 blocking_user_id = Column(ForeignKey("users.id"), nullable=False) 

455 blocked_user_id = Column(ForeignKey("users.id"), nullable=False) 

456 time_blocked = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

457 

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

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

460 

461 __table_args__ = ( 

462 UniqueConstraint("blocking_user_id", "blocked_user_id"), 

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

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

465 ) 

466 

467 

468class AccountDeletionReason(Base): 

469 __tablename__ = "account_deletion_reason" 

470 

471 id = Column(BigInteger, primary_key=True) 

472 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

473 user_id = Column(ForeignKey("users.id"), nullable=False) 

474 reason = Column(String, nullable=True) 

475 

476 user = relationship("User") 

477 

478 

479class ModerationUserList(Base): 

480 """ 

481 Represents a list of users listed together by a moderator 

482 """ 

483 

484 __tablename__ = "moderation_user_lists" 

485 

486 id = Column(BigInteger, primary_key=True) 

487 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

488 

489 # Relationships 

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

491 

492 

493class ModerationUserListMember(Base): 

494 """ 

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

496 """ 

497 

498 __tablename__ = "moderation_user_list_members" 

499 

500 user_id = Column(ForeignKey("users.id"), primary_key=True) 

501 moderation_list_id = Column(ForeignKey("moderation_user_lists.id"), primary_key=True) 

502 

503 __table_args__ = (UniqueConstraint("user_id", "moderation_list_id"),) 

504 

505 

506class AntiBotLog(Base): 

507 __tablename__ = "antibot_logs" 

508 

509 id = Column(BigInteger, primary_key=True) 

510 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

511 user_id = Column(ForeignKey("users.id"), nullable=True) 

512 

513 ip_address = Column(String, nullable=True) 

514 user_agent = Column(String, nullable=True) 

515 

516 action = Column(String, nullable=False) 

517 token = Column(String, nullable=False) 

518 

519 score = Column(Float, nullable=False) 

520 provider_data = Column(JSON, nullable=False) 

521 

522 

523class RateLimitAction(enum.Enum): 

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

525 

526 host_request = "host request" 

527 friend_request = "friend request" 

528 chat_initiation = "chat initiation" 

529 

530 

531class RateLimitViolation(Base): 

532 __tablename__ = "rate_limit_violations" 

533 

534 id = Column(BigInteger, primary_key=True) 

535 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

536 user_id = Column(ForeignKey("users.id"), nullable=False) 

537 action = Column(Enum(RateLimitAction), nullable=False) 

538 is_hard_limit = Column(Boolean, nullable=False) 

539 

540 user = relationship("User") 

541 

542 __table_args__ = ( 

543 # Fast lookup for rate limits in interval 

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

545 ) 

546 

547 

548class Volunteer(Base): 

549 __tablename__ = "volunteers" 

550 

551 id = Column(BigInteger, primary_key=True) 

552 user_id = Column(ForeignKey("users.id"), nullable=False, unique=True) 

553 

554 display_name = Column(String, nullable=True) 

555 display_location = Column(String, nullable=True) 

556 

557 role = Column(String, nullable=False) 

558 

559 # custom sort order on team page, sorted ascending 

560 sort_key = Column(Float, nullable=True) 

561 

562 started_volunteering = Column(Date, nullable=False, server_default=text("CURRENT_DATE")) 

563 stopped_volunteering = Column(Date, nullable=True, default=None) 

564 

565 link_type = Column(String, nullable=True) 

566 link_text = Column(String, nullable=True) 

567 link_url = Column(String, nullable=True) 

568 

569 show_on_team_page = Column(Boolean, nullable=False, server_default=expression.true()) 

570 

571 __table_args__ = ( 

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

573 CheckConstraint( 

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

575 name="link_type_text", 

576 ), 

577 )