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

1import enum 

2 

3from geoalchemy2.types import Geometry 

4from google.protobuf import empty_pb2 

5from sqlalchemy import ( 

6 ARRAY, 

7 JSON, 

8 BigInteger, 

9 Boolean, 

10 CheckConstraint, 

11 Column, 

12 Date, 

13 DateTime, 

14 Enum, 

15 Float, 

16 ForeignKey, 

17 Index, 

18 Integer, 

19 Interval, 

20 MetaData, 

21 Sequence, 

22 String, 

23 UniqueConstraint, 

24) 

25from sqlalchemy import LargeBinary as Binary 

26from sqlalchemy.dialects.postgresql import INET, TSTZRANGE, ExcludeConstraint 

27from sqlalchemy.ext.associationproxy import association_proxy 

28from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property 

29from sqlalchemy.orm import backref, column_property, declarative_base, deferred, relationship 

30from sqlalchemy.sql import and_, expression, func, not_, or_, text 

31from sqlalchemy.sql import select as sa_select 

32 

33from couchers import urls 

34from couchers.config import config 

35from couchers.constants import ( 

36 DATETIME_INFINITY, 

37 DATETIME_MINUS_INFINITY, 

38 EMAIL_REGEX, 

39 GUIDELINES_VERSION, 

40 PHONE_VERIFICATION_LIFETIME, 

41 SMS_CODE_LIFETIME, 

42 TOS_VERSION, 

43) 

44from couchers.utils import ( 

45 date_in_timezone, 

46 get_coordinates, 

47 last_active_coarsen, 

48 now, 

49) 

50from proto import notification_data_pb2 

51 

52meta = MetaData( 

53 naming_convention={ 

54 "ix": "ix_%(column_0_label)s", 

55 "uq": "uq_%(table_name)s_%(column_0_name)s", 

56 "ck": "ck_%(table_name)s_%(constraint_name)s", 

57 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", 

58 "pk": "pk_%(table_name)s", 

59 } 

60) 

61 

62Base = declarative_base(metadata=meta) 

63 

64 

65class HostingStatus(enum.Enum): 

66 can_host = enum.auto() 

67 maybe = enum.auto() 

68 cant_host = enum.auto() 

69 

70 

71class MeetupStatus(enum.Enum): 

72 wants_to_meetup = enum.auto() 

73 open_to_meetup = enum.auto() 

74 does_not_want_to_meetup = enum.auto() 

75 

76 

77class SmokingLocation(enum.Enum): 

78 yes = enum.auto() 

79 window = enum.auto() 

80 outside = enum.auto() 

81 no = enum.auto() 

82 

83 

84class SleepingArrangement(enum.Enum): 

85 private = enum.auto() 

86 common = enum.auto() 

87 shared_room = enum.auto() 

88 

89 

90class ParkingDetails(enum.Enum): 

91 free_onsite = enum.auto() 

92 free_offsite = enum.auto() 

93 paid_onsite = enum.auto() 

94 paid_offsite = enum.auto() 

95 

96 

97class ProfilePublicVisibility(enum.Enum): 

98 # no public info 

99 nothing = enum.auto() 

100 # only show on map, randomized, unclickable 

101 map_only = enum.auto() 

102 # name, gender, location, hosting/meetup status, badges, number of references, and signup time 

103 limited = enum.auto() 

104 # full about me except additional info (hide my home) 

105 most = enum.auto() 

106 # all but references 

107 full = enum.auto() 

108 

109 

110class TimezoneArea(Base): 

111 __tablename__ = "timezone_areas" 

112 id = Column(BigInteger, primary_key=True) 

113 

114 tzid = Column(String) 

115 geom = Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False) 

116 

117 __table_args__ = ( 

118 Index( 

119 "ix_timezone_areas_geom_tzid", 

120 geom, 

121 tzid, 

122 postgresql_using="gist", 

123 ), 

124 ) 

125 

126 

127class User(Base): 

128 """ 

129 Basic user and profile details 

130 """ 

131 

132 __tablename__ = "users" 

133 

134 id = Column(BigInteger, primary_key=True) 

135 

136 username = Column(String, nullable=False, unique=True) 

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

138 # stored in libsodium hash format, can be null for email login 

139 hashed_password = Column(Binary, nullable=False) 

140 # phone number in E.164 format with leading +, for example "+46701740605" 

141 phone = Column(String, nullable=True, server_default=expression.null()) 

142 # language preference -- defaults to empty string 

143 ui_language_preference = Column(String, nullable=True, server_default="") 

144 

145 # timezones should always be UTC 

146 ## location 

147 # point describing their location. EPSG4326 is the SRS (spatial ref system, = way to describe a point on earth) used 

148 # by GPS, it has the WGS84 geoid with lat/lon 

149 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=False) 

150 # randomized coordinates within a radius of 0.02-0.1 degrees, equates to about 2-10 km 

151 randomized_geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True) 

152 # their display location (displayed to other users), in meters 

153 geom_radius = Column(Float, nullable=False) 

154 # the display address (text) shown on their profile 

155 city = Column(String, nullable=False) 

156 # "Grew up in" on profile 

157 hometown = Column(String, nullable=True) 

158 

159 regions_visited = relationship("Region", secondary="regions_visited", order_by="Region.name") 

160 regions_lived = relationship("Region", secondary="regions_lived", order_by="Region.name") 

161 

162 timezone = column_property( 

163 sa_select(TimezoneArea.tzid).where(func.ST_Contains(TimezoneArea.geom, geom)).limit(1).scalar_subquery(), 

164 deferred=True, 

165 ) 

166 

167 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

168 last_active = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

169 

170 public_visibility = Column(Enum(ProfilePublicVisibility), nullable=False, server_default="map_only") 

171 has_modified_public_visibility = Column(Boolean, nullable=False, server_default=expression.false()) 

172 

173 # id of the last message that they received a notification about 

174 last_notified_message_id = Column(BigInteger, nullable=False, default=0) 

175 # same as above for host requests 

176 last_notified_request_message_id = Column(BigInteger, nullable=False, server_default=text("0")) 

177 

178 # display name 

179 name = Column(String, nullable=False) 

180 gender = Column(String, nullable=False) 

181 pronouns = Column(String, nullable=True) 

182 birthdate = Column(Date, nullable=False) # in the timezone of birthplace 

183 

184 avatar_key = Column(ForeignKey("uploads.key"), nullable=True) 

185 

186 hosting_status = Column(Enum(HostingStatus), nullable=False) 

187 meetup_status = Column(Enum(MeetupStatus), nullable=False, server_default="open_to_meetup") 

188 

189 # community standing score 

190 community_standing = Column(Float, nullable=True) 

191 

192 occupation = Column(String, nullable=True) # CommonMark without images 

193 education = Column(String, nullable=True) # CommonMark without images 

194 

195 # "Who I am" under "About Me" tab 

196 about_me = Column(String, nullable=True) # CommonMark without images 

197 # "What I do in my free time" under "About Me" tab 

198 things_i_like = Column(String, nullable=True) # CommonMark without images 

199 # "About my home" under "My Home" tab 

200 about_place = Column(String, nullable=True) # CommonMark without images 

201 # "Additional information" under "About Me" tab 

202 additional_information = Column(String, nullable=True) # CommonMark without images 

203 

204 is_banned = Column(Boolean, nullable=False, server_default=expression.false()) 

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

206 is_superuser = Column(Boolean, nullable=False, server_default=expression.false()) 

207 

208 # the undelete token allows a user to recover their account for a couple of days after deletion in case it was 

209 # accidental or they changed their mind 

210 # constraints make sure these are non-null only if is_deleted and that these are null in unison 

211 undelete_token = Column(String, nullable=True) 

212 # validity of the undelete token 

213 undelete_until = Column(DateTime(timezone=True), nullable=True) 

214 

215 # hosting preferences 

216 max_guests = Column(Integer, nullable=True) 

217 last_minute = Column(Boolean, nullable=True) 

218 has_pets = Column(Boolean, nullable=True) 

219 accepts_pets = Column(Boolean, nullable=True) 

220 pet_details = Column(String, nullable=True) # CommonMark without images 

221 has_kids = Column(Boolean, nullable=True) 

222 accepts_kids = Column(Boolean, nullable=True) 

223 kid_details = Column(String, nullable=True) # CommonMark without images 

224 has_housemates = Column(Boolean, nullable=True) 

225 housemate_details = Column(String, nullable=True) # CommonMark without images 

226 wheelchair_accessible = Column(Boolean, nullable=True) 

227 smoking_allowed = Column(Enum(SmokingLocation), nullable=True) 

228 smokes_at_home = Column(Boolean, nullable=True) 

229 drinking_allowed = Column(Boolean, nullable=True) 

230 drinks_at_home = Column(Boolean, nullable=True) 

231 # "Additional information" under "My Home" tab 

232 other_host_info = Column(String, nullable=True) # CommonMark without images 

233 

234 # "Sleeping privacy" (not long-form text) 

235 sleeping_arrangement = Column(Enum(SleepingArrangement), nullable=True) 

236 # "Sleeping arrangement" under "My Home" tab 

237 sleeping_details = Column(String, nullable=True) # CommonMark without images 

238 # "Local area information" under "My Home" tab 

239 area = Column(String, nullable=True) # CommonMark without images 

240 # "House rules" under "My Home" tab 

241 house_rules = Column(String, nullable=True) # CommonMark without images 

242 parking = Column(Boolean, nullable=True) 

243 parking_details = Column(Enum(ParkingDetails), nullable=True) # CommonMark without images 

244 camping_ok = Column(Boolean, nullable=True) 

245 

246 accepted_tos = Column(Integer, nullable=False, default=0) 

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

248 # whether the user has yet filled in the contributor form 

249 filled_contributor_form = Column(Boolean, nullable=False, server_default=expression.false()) 

250 

251 # number of onboarding emails sent 

252 onboarding_emails_sent = Column(Integer, nullable=False, server_default="0") 

253 last_onboarding_email_sent = Column(DateTime(timezone=True), nullable=True) 

254 

255 # whether we need to sync the user's newsletter preferences with the newsletter server 

256 in_sync_with_newsletter = Column(Boolean, nullable=False, server_default=expression.false()) 

257 # opted out of the newsletter 

258 opt_out_of_newsletter = Column(Boolean, nullable=False, server_default=expression.false()) 

259 

260 # set to null to receive no digests 

261 digest_frequency = Column(Interval, nullable=True) 

262 last_digest_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)")) 

263 

264 # for changing their email 

265 new_email = Column(String, nullable=True) 

266 

267 new_email_token = Column(String, nullable=True) 

268 new_email_token_created = Column(DateTime(timezone=True), nullable=True) 

269 new_email_token_expiry = Column(DateTime(timezone=True), nullable=True) 

270 

271 recommendation_score = Column(Float, nullable=False, server_default="0") 

272 

273 # Columns for verifying their phone number. State chart: 

274 # ,-------------------, 

275 # | Start | 

276 # | phone = None | someone else 

277 # ,-----------------, | token = None | verifies ,-----------------------, 

278 # | Code Expired | | sent = 1970 or zz | phone xx | Verification Expired | 

279 # | phone = xx | time passes | verified = None | <------, | phone = xx | 

280 # | token = yy | <------------, | attempts = 0 | | | token = None | 

281 # | sent = zz (exp.)| | '-------------------' | | sent = zz | 

282 # | verified = None | | V ^ +-----------< | verified = ww (exp.) | 

283 # | attempts = 0..2 | >--, | | | ChangePhone("") | | attempts = 0 | 

284 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------' 

285 # | | | | ChangePhone(xx) | ^ time passes 

286 # | | ^ V | | 

287 # ,-----------------, | | ,-------------------, | ,-----------------------, 

288 # | Too Many | >--' '--< | Code sent | >------+ | Verified | 

289 # | phone = xx | | phone = xx | | | phone = xx | 

290 # | token = yy | VerifyPhone(wrong)| token = yy | '-----------< | token = None | 

291 # | sent = zz | <------+--------< | sent = zz | | sent = zz | 

292 # | verified = None | | | verified = None | VerifyPhone(correct) | verified = ww | 

293 # | attempts = 3 | '--------> | attempts = 0..2 | >------------------> | attempts = 0 | 

294 # '-----------------' '-------------------' '-----------------------' 

295 

296 # randomly generated Luhn 6-digit string 

297 phone_verification_token = Column(String(6), nullable=True, server_default=expression.null()) 

298 

299 phone_verification_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)")) 

300 phone_verification_verified = Column(DateTime(timezone=True), nullable=True, server_default=expression.null()) 

301 phone_verification_attempts = Column(Integer, nullable=False, server_default=text("0")) 

302 

303 # the stripe customer identifier if the user has donated to Couchers 

304 # e.g. cus_JjoXHttuZopv0t 

305 # for new US entity 

306 stripe_customer_id = Column(String, nullable=True) 

307 # for old AU entity 

308 stripe_customer_id_old = Column(String, nullable=True) 

309 

310 has_passport_sex_gender_exception = Column(Boolean, nullable=False, server_default=expression.false()) 

311 

312 # checking for phone verification 

313 has_donated = Column(Boolean, nullable=False, server_default=expression.false()) 

314 

315 # whether this user has all emails turned off 

316 do_not_email = Column(Boolean, nullable=False, server_default=expression.false()) 

317 

318 avatar = relationship("Upload", foreign_keys="User.avatar_key") 

319 

320 admin_note = Column(String, nullable=False, server_default=text("''")) 

321 

322 # whether mods have marked this user has having to update their location 

323 needs_to_update_location = Column(Boolean, nullable=False, server_default=expression.false()) 

324 

325 last_antibot = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)")) 

326 

327 age = column_property(func.date_part("year", func.age(birthdate))) 

328 

329 # ID of the invite code used to sign up (if any) 

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

331 invite_code = relationship("InviteCode", foreign_keys=[invite_code_id]) 

332 

333 moderation_user_lists = relationship( 

334 "ModerationUserList", secondary="moderation_user_list_members", back_populates="users" 

335 ) 

336 

337 __table_args__ = ( 

338 # Verified phone numbers should be unique 

339 Index( 

340 "ix_users_unique_phone", 

341 phone, 

342 unique=True, 

343 postgresql_where=phone_verification_verified != None, 

344 ), 

345 Index( 

346 "ix_users_active", 

347 id, 

348 postgresql_where=and_(not_(is_banned), not_(is_deleted)), 

349 ), 

350 # create index on users(geom, id, username) where not is_banned and not is_deleted and geom is not null; 

351 Index( 

352 "ix_users_geom_active", 

353 geom, 

354 id, 

355 username, 

356 postgresql_using="gist", 

357 postgresql_where=and_(not_(is_banned), not_(is_deleted)), 

358 ), 

359 Index( 

360 "ix_users_by_id", 

361 id, 

362 postgresql_using="hash", 

363 postgresql_where=and_(not_(is_banned), not_(is_deleted)), 

364 ), 

365 Index( 

366 "ix_users_by_username", 

367 username, 

368 postgresql_using="hash", 

369 postgresql_where=and_(not_(is_banned), not_(is_deleted)), 

370 ), 

371 # There are two possible states for new_email_token, new_email_token_created, and new_email_token_expiry 

372 CheckConstraint( 

373 "(new_email_token IS NOT NULL AND new_email_token_created IS NOT NULL AND new_email_token_expiry IS NOT NULL) OR \ 

374 (new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL)", 

375 name="check_new_email_token_state", 

376 ), 

377 # Whenever a phone number is set, it must either be pending verification or already verified. 

378 # Exactly one of the following must always be true: not phone, token, verified. 

379 CheckConstraint( 

380 "(phone IS NULL)::int + (phone_verification_verified IS NOT NULL)::int + (phone_verification_token IS NOT NULL)::int = 1", 

381 name="phone_verified_conditions", 

382 ), 

383 # Email must match our regex 

384 CheckConstraint( 

385 f"email ~ '{EMAIL_REGEX}'", 

386 name="valid_email", 

387 ), 

388 # Undelete token + time are coupled: either both null or neither; and if they're not null then the account is deleted 

389 CheckConstraint( 

390 "((undelete_token IS NULL) = (undelete_until IS NULL)) AND ((undelete_token IS NULL) OR is_deleted)", 

391 name="undelete_nullity", 

392 ), 

393 # If the user disabled all emails, then they can't host or meet up 

394 CheckConstraint( 

395 "(do_not_email IS FALSE) OR ((hosting_status = 'cant_host') AND (meetup_status = 'does_not_want_to_meetup'))", 

396 name="do_not_email_inactive", 

397 ), 

398 ) 

399 

400 @hybrid_property 

401 def has_completed_profile(self): 

402 return self.avatar_key is not None and self.about_me is not None and len(self.about_me) >= 150 

403 

404 @has_completed_profile.expression 

405 def has_completed_profile(cls): 

406 return (cls.avatar_key != None) & (func.character_length(cls.about_me) >= 150) 

407 

408 @hybrid_property 

409 def has_completed_my_home(self): 

410 # completed my profile means that: 

411 # 1. has filled out max_guests 

412 # 2. has filled out sleeping_arrangement (sleeping privacy) 

413 # 3. has some text in at least one of the my home free text fields 

414 return ( 

415 self.max_guests is not None 

416 and self.sleeping_arrangement is not None 

417 and ( 

418 self.about_place is not None 

419 or self.other_host_info is not None 

420 or self.sleeping_details is not None 

421 or self.area is not None 

422 or self.house_rules is not None 

423 ) 

424 ) 

425 

426 @has_completed_my_home.expression 

427 def has_completed_my_home(cls): 

428 return and_( 

429 cls.max_guests != None, 

430 cls.sleeping_arrangement != None, 

431 or_( 

432 cls.about_place != None, 

433 cls.other_host_info != None, 

434 cls.sleeping_details != None, 

435 cls.area != None, 

436 cls.house_rules != None, 

437 ), 

438 ) 

439 

440 @hybrid_property 

441 def jailed_missing_tos(self): 

442 return self.accepted_tos < TOS_VERSION 

443 

444 @hybrid_property 

445 def jailed_missing_community_guidelines(self): 

446 return self.accepted_community_guidelines < GUIDELINES_VERSION 

447 

448 @hybrid_property 

449 def jailed_pending_mod_notes(self): 

450 return self.mod_notes.where(ModNote.is_pending).count() > 0 

451 

452 @hybrid_property 

453 def jailed_pending_activeness_probe(self): 

454 return self.pending_activeness_probe != None 

455 

456 @hybrid_property 

457 def is_jailed(self): 

458 return ( 

459 self.jailed_missing_tos 

460 | self.jailed_missing_community_guidelines 

461 | self.is_missing_location 

462 | self.jailed_pending_mod_notes 

463 | self.jailed_pending_activeness_probe 

464 ) 

465 

466 @hybrid_property 

467 def is_missing_location(self): 

468 return self.needs_to_update_location 

469 

470 @hybrid_property 

471 def is_visible(self): 

472 return ~(self.is_banned | self.is_deleted) 

473 

474 @property 

475 def coordinates(self): 

476 return get_coordinates(self.geom) 

477 

478 @property 

479 def display_joined(self): 

480 """ 

481 Returns the last active time rounded down to the nearest hour. 

482 """ 

483 return self.joined.replace(minute=0, second=0, microsecond=0) 

484 

485 @property 

486 def display_last_active(self): 

487 """ 

488 Returns the last active time rounded down whatever is the "last active" coarsening. 

489 """ 

490 return last_active_coarsen(self.last_active) 

491 

492 @hybrid_property 

493 def phone_is_verified(self): 

494 return ( 

495 self.phone_verification_verified is not None 

496 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME 

497 ) 

498 

499 @phone_is_verified.expression 

500 def phone_is_verified(cls): 

501 return (cls.phone_verification_verified != None) & ( 

502 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME 

503 ) 

504 

505 @hybrid_property 

506 def phone_code_expired(self): 

507 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME 

508 

509 def __repr__(self): 

510 return f"User(id={self.id}, email={self.email}, username={self.username})" 

511 

512 

513class UserBadge(Base): 

514 """ 

515 A badge on a user's profile 

516 """ 

517 

518 __tablename__ = "user_badges" 

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

520 

521 id = Column(BigInteger, primary_key=True) 

522 

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

524 # corresponds to "id" in badges.json 

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

526 

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

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

529 

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

531 

532 

533class ActivenessProbeStatus(enum.Enum): 

534 # no response yet 

535 pending = enum.auto() 

536 

537 # didn't respond on time 

538 expired = enum.auto() 

539 

540 # responded that they're still active 

541 still_active = enum.auto() 

542 

543 # responded that they're no longer active 

544 no_longer_active = enum.auto() 

545 

546 

547class ActivenessProbe(Base): 

548 """ 

549 Activeness probes are used to gauge if users are still active: we send them a notification and ask them to respond, 

550 we use this data both to help indicate response rate, as well as to make sure only those who are actively hosting 

551 show up as such. 

552 """ 

553 

554 __tablename__ = "activeness_probes" 

555 

556 id = Column(BigInteger, primary_key=True) 

557 

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

559 # the time this probe was initiated 

560 probe_initiated = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

561 # the number of reminders sent for this probe 

562 notifications_sent = Column(Integer, nullable=False, server_default="0") 

563 

564 # the time of response 

565 responded = Column(DateTime(timezone=True), nullable=True, default=None) 

566 # the response value 

567 response = Column(Enum(ActivenessProbeStatus), nullable=False, default=ActivenessProbeStatus.pending) 

568 

569 @hybrid_property 

570 def is_pending(self): 

571 return self.responded == None 

572 

573 user = relationship("User", back_populates="pending_activeness_probe") 

574 

575 __table_args__ = ( 

576 # a user can have at most one pending activeness probe at a time 

577 Index( 

578 "ix_activeness_probe_unique_pending_response", 

579 user_id, 

580 unique=True, 

581 postgresql_where=responded == None, 

582 ), 

583 # response time is none iff response is pending 

584 CheckConstraint( 

585 "(responded IS NULL AND response = 'pending') OR (responded IS NOT NULL AND response != 'pending')", 

586 name="pending_has_no_responded", 

587 ), 

588 ) 

589 

590 

591User.pending_activeness_probe = relationship( 

592 ActivenessProbe, 

593 primaryjoin="and_(ActivenessProbe.user_id == User.id, ActivenessProbe.is_pending)", 

594 uselist=False, 

595 back_populates="user", 

596) 

597 

598 

599class StrongVerificationAttemptStatus(enum.Enum): 

600 ## full data states 

601 # completed, this now provides verification for a user 

602 succeeded = enum.auto() 

603 

604 ## no data states 

605 # in progress: waiting for the user to scan the Iris code or open the app 

606 in_progress_waiting_on_user_to_open_app = enum.auto() 

607 # in progress: waiting for the user to scan MRZ or NFC/chip 

608 in_progress_waiting_on_user_in_app = enum.auto() 

609 # in progress, waiting for backend to pull verification data 

610 in_progress_waiting_on_backend = enum.auto() 

611 # failed, no data 

612 failed = enum.auto() 

613 

614 # duplicate, at our end, has data 

615 duplicate = enum.auto() 

616 

617 ## minimal data states 

618 # the data, except minimal deduplication data, was deleted 

619 deleted = enum.auto() 

620 

621 

622class PassportSex(enum.Enum): 

623 """ 

624 We don't care about sex, we use gender on the platform. But passports apparently do. 

625 """ 

626 

627 male = enum.auto() 

628 female = enum.auto() 

629 unspecified = enum.auto() 

630 

631 

632class StrongVerificationAttempt(Base): 

633 """ 

634 An attempt to perform strong verification 

635 """ 

636 

637 __tablename__ = "strong_verification_attempts" 

638 

639 # our verification id 

640 id = Column(BigInteger, primary_key=True) 

641 

642 # this is returned in the callback, and we look up the attempt via this 

643 verification_attempt_token = Column(String, nullable=False, unique=True) 

644 

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

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

647 

648 status = Column( 

649 Enum(StrongVerificationAttemptStatus), 

650 nullable=False, 

651 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app, 

652 ) 

653 

654 ## full data 

655 has_full_data = Column(Boolean, nullable=False, default=False) 

656 # the data returned from iris, encrypted with a public key whose private key is kept offline 

657 passport_encrypted_data = Column(Binary, nullable=True) 

658 passport_date_of_birth = Column(Date, nullable=True) 

659 passport_sex = Column(Enum(PassportSex), nullable=True) 

660 

661 ## minimal data: this will not be deleted 

662 has_minimal_data = Column(Boolean, nullable=False, default=False) 

663 passport_expiry_date = Column(Date, nullable=True) 

664 passport_nationality = Column(String, nullable=True) 

665 # last three characters of the passport number 

666 passport_last_three_document_chars = Column(String, nullable=True) 

667 

668 iris_token = Column(String, nullable=False, unique=True) 

669 iris_session_id = Column(BigInteger, nullable=False, unique=True) 

670 

671 passport_expiry_datetime = column_property(date_in_timezone(passport_expiry_date, "Etc/UTC")) 

672 

673 user = relationship("User") 

674 

675 @hybrid_property 

676 def is_valid(self): 

677 """ 

678 This only checks whether the attempt is a success and the passport is not expired, use `has_strong_verification` for full check 

679 """ 

680 return (self.status == StrongVerificationAttemptStatus.succeeded) and (self.passport_expiry_datetime >= now()) 

681 

682 @is_valid.expression 

683 def is_valid(cls): 

684 return (cls.status == StrongVerificationAttemptStatus.succeeded) & ( 

685 func.coalesce(cls.passport_expiry_datetime >= func.now(), False) 

686 ) 

687 

688 @hybrid_property 

689 def is_visible(self): 

690 return self.status != StrongVerificationAttemptStatus.deleted 

691 

692 @hybrid_method 

693 def _raw_birthdate_match(self, user): 

694 """Does not check whether the SV attempt itself is not expired""" 

695 return self.passport_date_of_birth == user.birthdate 

696 

697 @hybrid_method 

698 def matches_birthdate(self, user): 

699 return self.is_valid & self._raw_birthdate_match(user) 

700 

701 @hybrid_method 

702 def _raw_gender_match(self, user): 

703 """Does not check whether the SV attempt itself is not expired""" 

704 return ( 

705 ((user.gender == "Woman") & (self.passport_sex == PassportSex.female)) 

706 | ((user.gender == "Man") & (self.passport_sex == PassportSex.male)) 

707 | (self.passport_sex == PassportSex.unspecified) 

708 | (user.has_passport_sex_gender_exception == True) 

709 ) 

710 

711 @hybrid_method 

712 def matches_gender(self, user): 

713 return self.is_valid & self._raw_gender_match(user) 

714 

715 @hybrid_method 

716 def has_strong_verification(self, user): 

717 return self.is_valid & self._raw_birthdate_match(user) & self._raw_gender_match(user) 

718 

719 __table_args__ = ( 

720 # used to look up verification status for a user 

721 Index( 

722 "ix_strong_verification_attempts_current", 

723 user_id, 

724 passport_expiry_date, 

725 postgresql_where=status == StrongVerificationAttemptStatus.succeeded, 

726 ), 

727 # each passport can be verified only once 

728 Index( 

729 "ix_strong_verification_attempts_unique_succeeded", 

730 passport_expiry_date, 

731 passport_nationality, 

732 passport_last_three_document_chars, 

733 unique=True, 

734 postgresql_where=( 

735 (status == StrongVerificationAttemptStatus.succeeded) 

736 | (status == StrongVerificationAttemptStatus.deleted) 

737 ), 

738 ), 

739 # full data check 

740 CheckConstraint( 

741 "(has_full_data IS TRUE AND passport_encrypted_data IS NOT NULL AND passport_date_of_birth IS NOT NULL) OR \ 

742 (has_full_data IS FALSE AND passport_encrypted_data IS NULL AND passport_date_of_birth IS NULL)", 

743 name="full_data_status", 

744 ), 

745 # minimal data check 

746 CheckConstraint( 

747 "(has_minimal_data IS TRUE AND passport_expiry_date IS NOT NULL AND passport_nationality IS NOT NULL AND passport_last_three_document_chars IS NOT NULL) OR \ 

748 (has_minimal_data IS FALSE AND passport_expiry_date IS NULL AND passport_nationality IS NULL AND passport_last_three_document_chars IS NULL)", 

749 name="minimal_data_status", 

750 ), 

751 # note on implications: p => q iff ~p OR q 

752 # full data implies minimal data, has_minimal_data => has_full_data 

753 CheckConstraint( 

754 "(has_full_data IS FALSE) OR (has_minimal_data IS TRUE)", 

755 name="full_data_implies_minimal_data", 

756 ), 

757 # succeeded implies full data 

758 CheckConstraint( 

759 "(NOT (status = 'succeeded')) OR (has_full_data IS TRUE)", 

760 name="succeeded_implies_full_data", 

761 ), 

762 # in_progress/failed implies no_data 

763 CheckConstraint( 

764 "(NOT ((status = 'in_progress_waiting_on_user_to_open_app') OR (status = 'in_progress_waiting_on_user_in_app') OR (status = 'in_progress_waiting_on_backend') OR (status = 'failed'))) OR (has_minimal_data IS FALSE)", 

765 name="in_progress_failed_iris_implies_no_data", 

766 ), 

767 # deleted or duplicate implies minimal data 

768 CheckConstraint( 

769 "(NOT ((status = 'deleted') OR (status = 'duplicate'))) OR (has_minimal_data IS TRUE)", 

770 name="deleted_duplicate_implies_minimal_data", 

771 ), 

772 ) 

773 

774 

775class ModNote(Base): 

776 """ 

777 A moderator note to a user. This could be a warning, just a note "hey, we did X", or any other similar message. 

778 

779 The user has to read and "acknowledge" the note before continuing onto the platform, and being un-jailed. 

780 """ 

781 

782 __tablename__ = "mod_notes" 

783 id = Column(BigInteger, primary_key=True) 

784 

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

786 

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

788 acknowledged = Column(DateTime(timezone=True), nullable=True) 

789 

790 # this is an internal ID to allow the mods to track different types of notes 

791 internal_id = Column(String, nullable=False) 

792 # the admin that left this note 

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

794 

795 note_content = Column(String, nullable=False) # CommonMark without images 

796 

797 user = relationship("User", backref=backref("mod_notes", lazy="dynamic"), foreign_keys="ModNote.user_id") 

798 

799 def __repr__(self): 

800 return f"ModeNote(id={self.id}, user={self.user}, created={self.created}, ack'd={self.acknowledged})" 

801 

802 @hybrid_property 

803 def is_pending(self): 

804 return self.acknowledged == None 

805 

806 __table_args__ = ( 

807 # used to look up pending notes 

808 Index( 

809 "ix_mod_notes_unacknowledged", 

810 user_id, 

811 postgresql_where=acknowledged == None, 

812 ), 

813 ) 

814 

815 

816class StrongVerificationCallbackEvent(Base): 

817 __tablename__ = "strong_verification_callback_events" 

818 

819 id = Column(BigInteger, primary_key=True) 

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

821 

822 verification_attempt_id = Column(ForeignKey("strong_verification_attempts.id"), nullable=False, index=True) 

823 

824 iris_status = Column(String, nullable=False) 

825 

826 

827class DonationType(enum.Enum):