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

1112 statements  

« prev     ^ index     » next       coverage.py v7.6.10, created at 2025-03-24 14:08 +0000

1import enum 

2 

3from geoalchemy2.types import Geometry 

4from google.protobuf import empty_pb2 

5from sqlalchemy import ( 

6 ARRAY, 

7 BigInteger, 

8 Boolean, 

9 CheckConstraint, 

10 Column, 

11 Date, 

12 DateTime, 

13 Enum, 

14 Float, 

15 ForeignKey, 

16 Index, 

17 Integer, 

18 Interval, 

19 MetaData, 

20 Sequence, 

21 String, 

22 UniqueConstraint, 

23) 

24from sqlalchemy import LargeBinary as Binary 

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

26from sqlalchemy.ext.associationproxy import association_proxy 

27from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property 

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

29from sqlalchemy.sql import and_, func, text 

30from sqlalchemy.sql import select as sa_select 

31 

32from couchers import urls 

33from couchers.config import config 

34from couchers.constants import ( 

35 DATETIME_INFINITY, 

36 DATETIME_MINUS_INFINITY, 

37 EMAIL_REGEX, 

38 GUIDELINES_VERSION, 

39 PHONE_VERIFICATION_LIFETIME, 

40 SMS_CODE_LIFETIME, 

41 TOS_VERSION, 

42) 

43from couchers.utils import date_in_timezone, get_coordinates, last_active_coarsen, now 

44from proto import notification_data_pb2 

45 

46meta = MetaData( 

47 naming_convention={ 

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

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

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

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

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

53 } 

54) 

55 

56Base = declarative_base(metadata=meta) 

57 

58 

59class HostingStatus(enum.Enum): 

60 can_host = enum.auto() 

61 maybe = enum.auto() 

62 cant_host = enum.auto() 

63 

64 

65class MeetupStatus(enum.Enum): 

66 wants_to_meetup = enum.auto() 

67 open_to_meetup = enum.auto() 

68 does_not_want_to_meetup = enum.auto() 

69 

70 

71class SmokingLocation(enum.Enum): 

72 yes = enum.auto() 

73 window = enum.auto() 

74 outside = enum.auto() 

75 no = enum.auto() 

76 

77 

78class SleepingArrangement(enum.Enum): 

79 private = enum.auto() 

80 common = enum.auto() 

81 shared_room = enum.auto() 

82 

83 

84class ParkingDetails(enum.Enum): 

85 free_onsite = enum.auto() 

86 free_offsite = enum.auto() 

87 paid_onsite = enum.auto() 

88 paid_offsite = enum.auto() 

89 

90 

91class TimezoneArea(Base): 

92 __tablename__ = "timezone_areas" 

93 id = Column(BigInteger, primary_key=True) 

94 

95 tzid = Column(String) 

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

97 

98 __table_args__ = ( 

99 Index( 

100 "ix_timezone_areas_geom_tzid", 

101 geom, 

102 tzid, 

103 postgresql_using="gist", 

104 ), 

105 ) 

106 

107 

108class User(Base): 

109 """ 

110 Basic user and profile details 

111 """ 

112 

113 __tablename__ = "users" 

114 

115 id = Column(BigInteger, primary_key=True) 

116 

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

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

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

120 hashed_password = Column(Binary, nullable=False) 

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

122 phone = Column(String, nullable=True, server_default=text("NULL")) 

123 # language preference -- defaults to empty string 

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

125 

126 # timezones should always be UTC 

127 ## location 

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

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

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

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

132 geom_radius = Column(Float, nullable=True) 

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

134 city = Column(String, nullable=False) 

135 # "Grew up in" on profile 

136 hometown = Column(String, nullable=True) 

137 

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

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

140 

141 timezone = column_property( 

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

143 deferred=True, 

144 ) 

145 

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

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

148 

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

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

151 # same as above for host requests 

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

153 

154 # display name 

155 name = Column(String, nullable=False) 

156 gender = Column(String, nullable=False) 

157 pronouns = Column(String, nullable=True) 

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

159 

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

161 

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

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

164 

165 # community standing score 

166 community_standing = Column(Float, nullable=True) 

167 

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

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

170 

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

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

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

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

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

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

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

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

179 

180 is_banned = Column(Boolean, nullable=False, server_default=text("false")) 

181 is_deleted = Column(Boolean, nullable=False, server_default=text("false")) 

182 is_superuser = Column(Boolean, nullable=False, server_default=text("false")) 

183 

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

185 # accidental or they changed their mind 

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

187 undelete_token = Column(String, nullable=True) 

188 # validity of the undelete token 

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

190 

191 # hosting preferences 

192 max_guests = Column(Integer, nullable=True) 

193 last_minute = Column(Boolean, nullable=True) 

194 has_pets = Column(Boolean, nullable=True) 

195 accepts_pets = Column(Boolean, nullable=True) 

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

197 has_kids = Column(Boolean, nullable=True) 

198 accepts_kids = Column(Boolean, nullable=True) 

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

200 has_housemates = Column(Boolean, nullable=True) 

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

202 wheelchair_accessible = Column(Boolean, nullable=True) 

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

204 smokes_at_home = Column(Boolean, nullable=True) 

205 drinking_allowed = Column(Boolean, nullable=True) 

206 drinks_at_home = Column(Boolean, nullable=True) 

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

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

209 

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

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

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

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

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

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

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

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

218 parking = Column(Boolean, nullable=True) 

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

220 camping_ok = Column(Boolean, nullable=True) 

221 

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

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

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

225 filled_contributor_form = Column(Boolean, nullable=False, server_default="false") 

226 

227 # number of onboarding emails sent 

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

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

230 

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

232 in_sync_with_newsletter = Column(Boolean, nullable=False, server_default="false") 

233 # opted out of the newsletter 

234 opt_out_of_newsletter = Column(Boolean, nullable=False, server_default="false") 

235 

236 # set to null to receive no digests 

237 digest_frequency = Column(Interval, nullable=True) 

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

239 

240 # for changing their email 

241 new_email = Column(String, nullable=True) 

242 

243 new_email_token = Column(String, nullable=True) 

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

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

246 

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

248 

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

250 # ,-------------------, 

251 # | Start | 

252 # | phone = None | someone else 

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

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

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

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

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

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

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

260 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------' 

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

262 # | | ^ V | | 

263 # ,-----------------, | | ,-------------------, | ,-----------------------, 

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

265 # | phone = xx | | phone = xx | | | phone = xx | 

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

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

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

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

270 # '-----------------' '-------------------' '-----------------------' 

271 

272 # randomly generated Luhn 6-digit string 

273 phone_verification_token = Column(String(6), nullable=True, server_default=text("NULL")) 

274 

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

276 phone_verification_verified = Column(DateTime(timezone=True), nullable=True, server_default=text("NULL")) 

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

278 

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

280 # e.g. cus_JjoXHttuZopv0t 

281 # for new US entity 

282 stripe_customer_id = Column(String, nullable=True) 

283 # for old AU entity 

284 stripe_customer_id_old = Column(String, nullable=True) 

285 

286 has_passport_sex_gender_exception = Column(Boolean, nullable=False, server_default=text("false")) 

287 

288 # checking for phone verification 

289 has_donated = Column(Boolean, nullable=False, server_default=text("false")) 

290 

291 # whether this user has all emails turned off 

292 do_not_email = Column(Boolean, nullable=False, server_default=text("false")) 

293 

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

295 

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

297 

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

299 

300 __table_args__ = ( 

301 # Verified phone numbers should be unique 

302 Index( 

303 "ix_users_unique_phone", 

304 phone, 

305 unique=True, 

306 postgresql_where=phone_verification_verified != None, 

307 ), 

308 Index( 

309 "ix_users_active", 

310 id, 

311 postgresql_where=~is_banned & ~is_deleted, 

312 ), 

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

314 Index( 

315 "ix_users_geom_active", 

316 geom, 

317 id, 

318 username, 

319 postgresql_where=~is_banned & ~is_deleted & (geom != None), 

320 ), 

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

322 CheckConstraint( 

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

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

325 name="check_new_email_token_state", 

326 ), 

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

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

329 CheckConstraint( 

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

331 name="phone_verified_conditions", 

332 ), 

333 # Email must match our regex 

334 CheckConstraint( 

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

336 name="valid_email", 

337 ), 

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

339 CheckConstraint( 

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

341 name="undelete_nullity", 

342 ), 

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

344 CheckConstraint( 

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

346 name="do_not_email_inactive", 

347 ), 

348 ) 

349 

350 @hybrid_property 

351 def has_completed_profile(self): 

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

353 

354 @has_completed_profile.expression 

355 def has_completed_profile(cls): 

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

357 

358 @hybrid_property 

359 def jailed_missing_tos(self): 

360 return self.accepted_tos < TOS_VERSION 

361 

362 @hybrid_property 

363 def jailed_missing_community_guidelines(self): 

364 return self.accepted_community_guidelines < GUIDELINES_VERSION 

365 

366 @hybrid_property 

367 def jailed_pending_mod_notes(self): 

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

369 

370 @hybrid_property 

371 def jailed_pending_activeness_probe(self): 

372 return self.pending_activeness_probe != None 

373 

374 @hybrid_property 

375 def is_jailed(self): 

376 return ( 

377 self.jailed_missing_tos 

378 | self.jailed_missing_community_guidelines 

379 | self.is_missing_location 

380 | self.jailed_pending_mod_notes 

381 | self.jailed_pending_activeness_probe 

382 ) 

383 

384 @hybrid_property 

385 def is_missing_location(self): 

386 return (self.geom == None) | (self.geom_radius == None) 

387 

388 @hybrid_property 

389 def is_visible(self): 

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

391 

392 @property 

393 def coordinates(self): 

394 return get_coordinates(self.geom) 

395 

396 @property 

397 def display_joined(self): 

398 """ 

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

400 """ 

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

402 

403 @property 

404 def display_last_active(self): 

405 """ 

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

407 """ 

408 return last_active_coarsen(self.last_active) 

409 

410 @hybrid_property 

411 def phone_is_verified(self): 

412 return ( 

413 self.phone_verification_verified is not None 

414 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME 

415 ) 

416 

417 @phone_is_verified.expression 

418 def phone_is_verified(cls): 

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

420 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME 

421 ) 

422 

423 @hybrid_property 

424 def phone_code_expired(self): 

425 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME 

426 

427 def __repr__(self): 

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

429 

430 

431class UserBadge(Base): 

432 """ 

433 A badge on a user's profile 

434 """ 

435 

436 __tablename__ = "user_badges" 

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

438 

439 id = Column(BigInteger, primary_key=True) 

440 

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

442 # corresponds to "id" in badges.json 

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

444 

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

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

447 

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

449 

450 

451class ActivenessProbeStatus(enum.Enum): 

452 # no response yet 

453 pending = enum.auto() 

454 

455 # didn't respond on time 

456 expired = enum.auto() 

457 

458 # responded that they're still active 

459 still_active = enum.auto() 

460 

461 # responded that they're no longer active 

462 no_longer_active = enum.auto() 

463 

464 

465class ActivenessProbe(Base): 

466 """ 

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

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

469 show up as such. 

470 """ 

471 

472 __tablename__ = "activeness_probes" 

473 

474 id = Column(BigInteger, primary_key=True) 

475 

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

477 # the time this probe was initiated 

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

479 # the number of reminders sent for this probe 

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

481 

482 # the time of response 

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

484 # the response value 

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

486 

487 @hybrid_property 

488 def is_pending(self): 

489 return self.responded == None 

490 

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

492 

493 __table_args__ = ( 

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

495 Index( 

496 "ix_activeness_probe_unique_pending_response", 

497 user_id, 

498 unique=True, 

499 postgresql_where=responded == None, 

500 ), 

501 # response time is none iff response is pending 

502 CheckConstraint( 

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

504 name="pending_has_no_responded", 

505 ), 

506 ) 

507 

508 

509User.pending_activeness_probe = relationship( 

510 ActivenessProbe, 

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

512 uselist=False, 

513 back_populates="user", 

514) 

515 

516 

517class StrongVerificationAttemptStatus(enum.Enum): 

518 ## full data states 

519 # completed, this now provides verification for a user 

520 succeeded = enum.auto() 

521 

522 ## no data states 

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

524 in_progress_waiting_on_user_to_open_app = enum.auto() 

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

526 in_progress_waiting_on_user_in_app = enum.auto() 

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

528 in_progress_waiting_on_backend = enum.auto() 

529 # failed, no data 

530 failed = enum.auto() 

531 

532 # duplicate, at our end, has data 

533 duplicate = enum.auto() 

534 

535 ## minimal data states 

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

537 deleted = enum.auto() 

538 

539 

540class PassportSex(enum.Enum): 

541 """ 

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

543 """ 

544 

545 male = enum.auto() 

546 female = enum.auto() 

547 unspecified = enum.auto() 

548 

549 

550class StrongVerificationAttempt(Base): 

551 """ 

552 An attempt to perform strong verification 

553 """ 

554 

555 __tablename__ = "strong_verification_attempts" 

556 

557 # our verification id 

558 id = Column(BigInteger, primary_key=True) 

559 

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

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

562 

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

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

565 

566 status = Column( 

567 Enum(StrongVerificationAttemptStatus), 

568 nullable=False, 

569 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app, 

570 ) 

571 

572 ## full data 

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

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

575 passport_encrypted_data = Column(Binary, nullable=True) 

576 passport_date_of_birth = Column(Date, nullable=True) 

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

578 

579 ## minimal data: this will not be deleted 

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

581 passport_expiry_date = Column(Date, nullable=True) 

582 passport_nationality = Column(String, nullable=True) 

583 # last three characters of the passport number 

584 passport_last_three_document_chars = Column(String, nullable=True) 

585 

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

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

588 

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

590 

591 user = relationship("User") 

592 

593 @hybrid_property 

594 def is_valid(self): 

595 """ 

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

597 """ 

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

599 

600 @is_valid.expression 

601 def is_valid(cls): 

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

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

604 ) 

605 

606 @hybrid_property 

607 def is_visible(self): 

608 return self.status != StrongVerificationAttemptStatus.deleted 

609 

610 @hybrid_method 

611 def _raw_birthdate_match(self, user): 

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

613 return self.passport_date_of_birth == user.birthdate 

614 

615 @hybrid_method 

616 def matches_birthdate(self, user): 

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

618 

619 @hybrid_method 

620 def _raw_gender_match(self, user): 

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

622 return ( 

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

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

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

626 | (user.has_passport_sex_gender_exception == True) 

627 ) 

628 

629 @hybrid_method 

630 def matches_gender(self, user): 

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

632 

633 @hybrid_method 

634 def has_strong_verification(self, user): 

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

636 

637 __table_args__ = ( 

638 # used to look up verification status for a user 

639 Index( 

640 "ix_strong_verification_attempts_current", 

641 user_id, 

642 passport_expiry_date, 

643 postgresql_where=status == StrongVerificationAttemptStatus.succeeded, 

644 ), 

645 # each passport can be verified only once 

646 Index( 

647 "ix_strong_verification_attempts_unique_succeeded", 

648 passport_expiry_date, 

649 passport_nationality, 

650 passport_last_three_document_chars, 

651 unique=True, 

652 postgresql_where=( 

653 (status == StrongVerificationAttemptStatus.succeeded) 

654 | (status == StrongVerificationAttemptStatus.deleted) 

655 ), 

656 ), 

657 # full data check 

658 CheckConstraint( 

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

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

661 name="full_data_status", 

662 ), 

663 # minimal data check 

664 CheckConstraint( 

665 "(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 \ 

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

667 name="minimal_data_status", 

668 ), 

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

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

671 CheckConstraint( 

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

673 name="full_data_implies_minimal_data", 

674 ), 

675 # succeeded implies full data 

676 CheckConstraint( 

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

678 name="succeeded_implies_full_data", 

679 ), 

680 # in_progress/failed implies no_data 

681 CheckConstraint( 

682 "(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)", 

683 name="in_progress_failed_iris_implies_no_data", 

684 ), 

685 # deleted or duplicate implies minimal data 

686 CheckConstraint( 

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

688 name="deleted_duplicate_implies_minimal_data", 

689 ), 

690 ) 

691 

692 

693class ModNote(Base): 

694 """ 

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

696 

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

698 """ 

699 

700 __tablename__ = "mod_notes" 

701 id = Column(BigInteger, primary_key=True) 

702 

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

704 

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

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

707 

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

709 internal_id = Column(String, nullable=False) 

710 # the admin that left this note 

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

712 

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

714 

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

716 

717 def __repr__(self): 

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

719 

720 @hybrid_property 

721 def is_pending(self): 

722 return self.acknowledged == None 

723 

724 __table_args__ = ( 

725 # used to look up pending notes 

726 Index( 

727 "ix_mod_notes_unacknowledged", 

728 user_id, 

729 postgresql_where=acknowledged == None, 

730 ), 

731 ) 

732 

733 

734class StrongVerificationCallbackEvent(Base): 

735 __tablename__ = "strong_verification_callback_events" 

736 

737 id = Column(BigInteger, primary_key=True) 

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

739 

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

741 

742 iris_status = Column(String, nullable=False) 

743 

744 

745class DonationType(enum.Enum): 

746 one_time = enum.auto() 

747 recurring = enum.auto() 

748 

749 

750class DonationInitiation(Base): 

751 """ 

752 Whenever someone initiaties a donation through the platform 

753 """ 

754 

755 __tablename__ = "donation_initiations" 

756 id = Column(BigInteger, primary_key=True) 

757 

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

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

760 

761 amount = Column(Integer, nullable=False) 

762 stripe_checkout_session_id = Column(String, nullable=False) 

763 

764 donation_type = Column(Enum(DonationType), nullable=False) 

765 

766 user = relationship("User", backref="donation_initiations") 

767 

768 

769class Invoice(Base): 

770 """ 

771 Successful donations, both one off and recurring 

772 

773 Triggered by `payment_intent.succeeded` webhook 

774 """ 

775 

776 __tablename__ = "invoices" 

777 

778 id = Column(BigInteger, primary_key=True) 

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

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

781 

782 amount = Column(Float, nullable=False) 

783 

784 stripe_payment_intent_id = Column(String, nullable=False, unique=True) 

785 stripe_receipt_url = Column(String, nullable=False) 

786 

787 user = relationship("User", backref="invoices") 

788 

789 

790class LanguageFluency(enum.Enum): 

791 # note that the numbering is important here, these are ordinal 

792 beginner = 1 

793 conversational = 2 

794 fluent = 3 

795 

796 

797class LanguageAbility(Base): 

798 __tablename__ = "language_abilities" 

799 __table_args__ = ( 

800 # Users can only have one language ability per language 

801 UniqueConstraint("user_id", "language_code"), 

802 ) 

803 

804 id = Column(BigInteger, primary_key=True) 

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

806 language_code = Column(ForeignKey("languages.code", deferrable=True), nullable=False) 

807 fluency = Column(Enum(LanguageFluency), nullable=False) 

808 

809 user = relationship("User", backref="language_abilities") 

810 language = relationship("Language") 

811 

812 

813class RegionVisited(Base): 

814 __tablename__ = "regions_visited" 

815 __table_args__ = (UniqueConstraint("user_id", "region_code"),) 

816 

817 id = Column(BigInteger, primary_key=True) 

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

819 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False) 

820 

821 

822class RegionLived(Base): 

823 __tablename__ = "regions_lived" 

824 __table_args__ = (UniqueConstraint("user_id", "region_code"),) 

825 

826 id = Column(BigInteger, primary_key=True) 

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

828 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False) 

829 

830 

831class FriendStatus(enum.Enum): 

832 pending = enum.auto() 

833 accepted = enum.auto() 

834 rejected = enum.auto() 

835 cancelled = enum.auto() 

836 

837 

838class FriendRelationship(Base): 

839 """ 

840 Friendship relations between users 

841 

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

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

844 """ 

845 

846 __tablename__ = "friend_relationships" 

847 

848 id = Column(BigInteger, primary_key=True) 

849 

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

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

852 

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

854 

855 # timezones should always be UTC 

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

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

858 

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

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

861 

862 

863class ContributeOption(enum.Enum): 

864 yes = enum.auto() 

865 maybe = enum.auto() 

866 no = enum.auto() 

867 

868 

869class ContributorForm(Base): 

870 """ 

871 Someone filled in the contributor form 

872 """ 

873 

874 __tablename__ = "contributor_forms" 

875 

876 id = Column(BigInteger, primary_key=True) 

877 

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

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

880 

881 ideas = Column(String, nullable=True) 

882 features = Column(String, nullable=True) 

883 experience = Column(String, nullable=True) 

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

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

886 expertise = Column(String, nullable=True) 

887 

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

889 

890 @hybrid_property 

891 def is_filled(self): 

892 """ 

893 Whether the form counts as having been filled 

894 """ 

895 return ( 

896 (self.ideas != None) 

897 | (self.features != None) 

898 | (self.experience != None) 

899 | (self.contribute != None) 

900 | (self.contribute_ways != []) 

901 | (self.expertise != None) 

902 ) 

903 

904 @property 

905 def should_notify(self): 

906 """ 

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

908 

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

910 """ 

911 return (self.expertise != None) | (not set(self.contribute_ways).issubset({"community", "blog", "other"})) 

912 

913 

914class SignupFlow(Base): 

915 """ 

916 Signup flows/incomplete users 

917 

918 Coinciding fields have the same meaning as in User 

919 """ 

920 

921 __tablename__ = "signup_flows" 

922 

923 id = Column(BigInteger, primary_key=True) 

924 

925 # housekeeping 

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

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

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

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

930 email_token = Column(String, nullable=True) 

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

932 

933 ## Basic 

934 name = Column(String, nullable=False) 

935 # TODO: unique across both tables 

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

937 # TODO: invitation, attribution 

938 

939 ## Account 

940 # TODO: unique across both tables 

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

942 hashed_password = Column(Binary, nullable=True) 

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

944 gender = Column(String, nullable=True) 

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

946 city = Column(String, nullable=True) 

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

948 geom_radius = Column(Float, nullable=True) 

949 

950 accepted_tos = Column(Integer, nullable=True) 

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

952 

953 opt_out_of_newsletter = Column(Boolean, nullable=True) 

954 

955 ## Feedback 

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

957 ideas = Column(String, nullable=True) 

958 features = Column(String, nullable=True) 

959 experience = Column(String, nullable=True) 

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

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

962 expertise = Column(String, nullable=True) 

963 

964 @hybrid_property 

965 def token_is_valid(self): 

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

967 

968 @hybrid_property 

969 def account_is_filled(self): 

970 return ( 

971 (self.username != None) 

972 & (self.birthdate != None) 

973 & (self.gender != None) 

974 & (self.hosting_status != None) 

975 & (self.city != None) 

976 & (self.geom != None) 

977 & (self.geom_radius != None) 

978 & (self.accepted_tos != None) 

979 & (self.opt_out_of_newsletter != None) 

980 ) 

981 

982 @hybrid_property 

983 def is_completed(self): 

984 return ( 

985 self.email_verified 

986 & self.account_is_filled 

987 & self.filled_feedback 

988 & (self.accepted_community_guidelines == GUIDELINES_VERSION) 

989 ) 

990 

991 

992class LoginToken(Base): 

993 """ 

994 A login token sent in an email to a user, allows them to sign in between the times defined by created and expiry 

995 """ 

996 

997 __tablename__ = "login_tokens" 

998 token = Column(String, primary_key=True) 

999 

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

1001 

1002 # timezones should always be UTC 

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

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

1005 

1006 user = relationship("User", backref="login_tokens") 

1007 

1008 @hybrid_property 

1009 def is_valid(self): 

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

1011 

1012 def __repr__(self): 

1013 return f"LoginToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})" 

1014 

1015 

1016class PasswordResetToken(Base): 

1017 __tablename__ = "password_reset_tokens" 

1018 token = Column(String, primary_key=True) 

1019 

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

1021 

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

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

1024 

1025 user = relationship("User", backref="password_reset_tokens") 

1026 

1027 @hybrid_property 

1028 def is_valid(self): 

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

1030 

1031 def __repr__(self): 

1032 return f"PasswordResetToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})" 

1033 

1034 

1035class AccountDeletionToken(Base): 

1036 __tablename__ = "account_deletion_tokens" 

1037 

1038 token = Column(String, primary_key=True) 

1039 

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

1041 

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

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

1044 

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

1046 

1047 @hybrid_property 

1048 def is_valid(self): 

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

1050 

1051 def __repr__(self): 

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

1053 

1054 

1055class UserActivity(Base): 

1056 """ 

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

1058 

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

1060 """ 

1061 

1062 __tablename__ = "user_activity" 

1063 

1064 id = Column(BigInteger, primary_key=True) 

1065 

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

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

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

1069 

1070 # details of the browser, if available 

1071 ip_address = Column(INET, nullable=True) 

1072 user_agent = Column(String, nullable=True) 

1073 

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

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

1076 

1077 __table_args__ = ( 

1078 # helps look up this tuple quickly 

1079 Index( 

1080 "ix_user_activity_user_id_period_ip_address_user_agent", 

1081 user_id, 

1082 period, 

1083 ip_address, 

1084 user_agent, 

1085 unique=True, 

1086 ), 

1087 ) 

1088 

1089 

1090class UserSession(Base): 

1091 """ 

1092 API keys/session cookies for the app 

1093 

1094 There are two types of sessions: long-lived, and short-lived. Long-lived are 

1095 like when you choose "remember this browser": they will be valid for a long 

1096 time without the user interacting with the site. Short-lived sessions on the 

1097 other hand get invalidated quickly if the user does not interact with the 

1098 site. 

1099 

1100 Long-lived tokens are valid from `created` until `expiry`. 

1101 

1102 Short-lived tokens expire after 168 hours (7 days) if they are not used. 

1103 """ 

1104 

1105 __tablename__ = "sessions" 

1106 token = Column(String, primary_key=True) 

1107 

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

1109 

1110 # sessions are either "api keys" or "session cookies", otherwise identical 

1111 # an api key is put in the authorization header (e.g. "authorization: Bearer <token>") 

1112 # a session cookie is set in the "couchers-sesh" cookie (e.g. "cookie: couchers-sesh=<token>") 

1113 # when a session is created, it's fixed as one or the other for security reasons 

1114 # for api keys to be useful, they should be long lived and have a long expiry 

1115 is_api_key = Column(Boolean, nullable=False, server_default=text("false")) 

1116 

1117 # whether it's a long-lived or short-lived session 

1118 long_lived = Column(Boolean, nullable=False) 

1119 

1120 # the time at which the session was created 

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

1122 

1123 # the expiry of the session: the session *cannot* be refreshed past this 

1124 expiry = Column(DateTime(timezone=True), nullable=False, server_default=func.now() + text("interval '730 days'")) 

1125 

1126 # the time at which the token was invalidated, allows users to delete sessions 

1127 deleted = Column(DateTime(timezone=True), nullable=True, default=None) 

1128 

1129 # the last time this session was used 

1130 last_seen = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

1131 

1132 # count of api calls made with this token/session (if we're updating last_seen, might as well update this too) 

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

1134 

1135 # details of the browser, if available 

1136 # these are from the request creating the session, not used for anything else 

1137 ip_address = Column(String, nullable=True) 

1138 user_agent = Column(String, nullable=True) 

1139 

1140 user = relationship("User", backref="sessions") 

1141 

1142 @hybrid_property 

1143 def is_valid(self): 

1144 """ 

1145 It must have been created and not be expired or deleted. 

1146 

1147 Also, if it's a short lived token, it must have been used in the last 168 hours. 

1148 

1149 TODO: this probably won't run in python (instance level), only in sql (class level) 

1150 """ 

1151 return ( 

1152 (self.created <= func.now()) 

1153 & (self.expiry >= func.now()) 

1154 & (self.deleted == None) 

1155 & (self.long_lived | (func.now() - self.last_seen < text("interval '168 hours'"))) 

1156 ) 

1157 

1158 

1159class Conversation(Base): 

1160 """ 

1161 Conversation brings together the different types of message/conversation types 

1162 """ 

1163 

1164 __tablename__ = "conversations" 

1165 

1166 id = Column(BigInteger, primary_key=True) 

1167 # timezone should always be UTC 

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

1169 

1170 def __repr__(self): 

1171 return f"Conversation(id={self.id}, created={self.created})" 

1172 

1173 

1174class GroupChat(Base): 

1175 """ 

1176 Group chat 

1177 """ 

1178 

1179 __tablename__ = "group_chats" 

1180 

1181 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True) 

1182 

1183 title = Column(String, nullable=True) 

1184 only_admins_invite = Column(Boolean, nullable=False, default=True) 

1185 creator_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

1186 is_dm = Column(Boolean, nullable=False) 

1187 

1188 conversation = relationship("Conversation", backref="group_chat") 

1189 creator = relationship("User", backref="created_group_chats") 

1190 

1191 def __repr__(self): 

1192 return f"GroupChat(conversation={self.conversation}, title={self.title or 'None'}, only_admins_invite={self.only_admins_invite}, creator={self.creator}, is_dm={self.is_dm})" 

1193 

1194 

1195class GroupChatRole(enum.Enum): 

1196 admin = enum.auto() 

1197 participant = enum.auto() 

1198 

1199 

1200class GroupChatSubscription(Base): 

1201 """ 

1202 The recipient of a thread and information about when they joined/left/etc. 

1203 """ 

1204 

1205 __tablename__ = "group_chat_subscriptions" 

1206 id = Column(BigInteger, primary_key=True) 

1207 

1208 # TODO: DB constraint on only one user+group_chat combo at a given time 

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

1210 group_chat_id = Column(ForeignKey("group_chats.id"), nullable=False, index=True) 

1211 

1212 # timezones should always be UTC 

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

1214 left = Column(DateTime(timezone=True), nullable=True) 

1215 

1216 role = Column(Enum(GroupChatRole), nullable=False) 

1217 

1218 last_seen_message_id = Column(BigInteger, nullable=False, default=0) 

1219 

1220 # when this chat is muted until, DATETIME_INFINITY for "forever" 

1221 muted_until = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_MINUS_INFINITY.isoformat()) 

1222 

1223 user = relationship("User", backref="group_chat_subscriptions") 

1224 group_chat = relationship("GroupChat", backref=backref("subscriptions", lazy="dynamic")) 

1225 

1226 def muted_display(self): 

1227 """ 

1228 Returns (muted, muted_until) display values: 

1229 1. If not muted, returns (False, None) 

1230 2. If muted forever, returns (True, None) 

1231 3. If muted until a given datetime returns (True, dt) 

1232 """ 

1233 if self.muted_until < now(): 

1234 return (False, None) 

1235 elif self.muted_until == DATETIME_INFINITY: 

1236 return (True, None) 

1237 else: 

1238 return (True, self.muted_until) 

1239 

1240 @hybrid_property 

1241 def is_muted(self): 

1242 return self.muted_until > func.now() 

1243 

1244 def __repr__(self): 

1245 return f"GroupChatSubscription(id={self.id}, user={self.user}, joined={self.joined}, left={self.left}, role={self.role}, group_chat={self.group_chat})" 

1246 

1247 

1248class MessageType(enum.Enum): 

1249 text = enum.auto() 

1250 # e.g. 

1251 # image = 

1252 # emoji = 

1253 # ... 

1254 chat_created = enum.auto() 

1255 chat_edited = enum.auto() 

1256 user_invited = enum.auto() 

1257 user_left = enum.auto() 

1258 user_made_admin = enum.auto() 

1259 user_removed_admin = enum.auto() # RemoveGroupChatAdmin: remove admin permission from a user in group chat 

1260 host_request_status_changed = enum.auto() 

1261 user_removed = enum.auto() # user is removed from group chat by amdin RemoveGroupChatUser 

1262 

1263 

1264class HostRequestStatus(enum.Enum): 

1265 pending = enum.auto() 

1266 accepted = enum.auto() 

1267 rejected = enum.auto() 

1268 confirmed = enum.auto() 

1269 cancelled = enum.auto() 

1270 

1271 

1272class Message(Base): 

1273 """ 

1274 A message. 

1275 

1276 If message_type = text, then the message is a normal text message, otherwise, it's a special control message. 

1277 """ 

1278 

1279 __tablename__ = "messages" 

1280 

1281 id = Column(BigInteger, primary_key=True) 

1282 

1283 # which conversation the message belongs in 

1284 conversation_id = Column(ForeignKey("conversations.id"), nullable=False, index=True) 

1285 

1286 # the user that sent the message/command 

1287 author_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

1288 

1289 # the message type, "text" is a text message, otherwise a "control message" 

1290 message_type = Column(Enum(MessageType), nullable=False) 

1291 

1292 # the target if a control message and requires target, e.g. if inviting a user, the user invited is the target 

1293 target_id = Column(ForeignKey("users.id"), nullable=True, index=True) 

1294 

1295 # time sent, timezone should always be UTC 

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

1297 

1298 # the plain-text message text if not control 

1299 text = Column(String, nullable=True) 

1300 

1301 # the new host request status if the message type is host_request_status_changed 

1302 host_request_status_target = Column(Enum(HostRequestStatus), nullable=True) 

1303 

1304 conversation = relationship("Conversation", backref="messages", order_by="Message.time.desc()") 

1305 author = relationship("User", foreign_keys="Message.author_id") 

1306 target = relationship("User", foreign_keys="Message.target_id") 

1307 

1308 @property 

1309 def is_normal_message(self): 

1310 """ 

1311 There's only one normal type atm, text 

1312 """ 

1313 return self.message_type == MessageType.text 

1314 

1315 def __repr__(self): 

1316 return f"Message(id={self.id}, time={self.time}, text={self.text}, author={self.author}, conversation={self.conversation})" 

1317 

1318 

1319class ContentReport(Base): 

1320 """ 

1321 A piece of content reported to admins 

1322 """ 

1323 

1324 __tablename__ = "content_reports" 

1325 

1326 id = Column(BigInteger, primary_key=True) 

1327 

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

1329 

1330 # the user who reported or flagged the content 

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

1332 

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

1334 reason = Column(String, nullable=False) 

1335 # a short description 

1336 description = Column(String, nullable=False) 

1337 

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

1339 content_ref = Column(String, nullable=False) 

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

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

1342 

1343 # details of the browser, if available 

1344 user_agent = Column(String, nullable=False) 

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

1346 page = Column(String, nullable=False) 

1347 

1348 # see comments above for reporting vs author 

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

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

1351 

1352 

1353class Email(Base): 

1354 """ 

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

1356 """ 

1357 

1358 __tablename__ = "emails" 

1359 

1360 id = Column(String, primary_key=True) 

1361 

1362 # timezone should always be UTC 

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

1364 

1365 sender_name = Column(String, nullable=False) 

1366 sender_email = Column(String, nullable=False) 

1367 

1368 recipient = Column(String, nullable=False) 

1369 subject = Column(String, nullable=False) 

1370 

1371 plain = Column(String, nullable=False) 

1372 html = Column(String, nullable=False) 

1373 

1374 list_unsubscribe_header = Column(String, nullable=True) 

1375 source_data = Column(String, nullable=True) 

1376 

1377 

1378class SMS(Base): 

1379 """ 

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

1381 """ 

1382 

1383 __tablename__ = "smss" 

1384 

1385 id = Column(BigInteger, primary_key=True) 

1386 

1387 # timezone should always be UTC 

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

1389 # AWS message id 

1390 message_id = Column(String, nullable=False) 

1391 

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

1393 sms_sender_id = Column(String, nullable=False) 

1394 number = Column(String, nullable=False) 

1395 message = Column(String, nullable=False) 

1396 

1397 

1398class HostRequest(Base): 

1399 """ 

1400 A request to stay with a host 

1401 """ 

1402 

1403 __tablename__ = "host_requests" 

1404 

1405 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True) 

1406 surfer_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

1407 host_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

1408 

1409 # TODO: proper timezone handling 

1410 timezone = "Etc/UTC" 

1411 

1412 # dates in the timezone above 

1413 from_date = Column(Date, nullable=False) 

1414 to_date = Column(Date, nullable=False) 

1415 

1416 # timezone aware start and end times of the request, can be compared to now() 

1417 start_time = column_property(date_in_timezone(from_date, timezone)) 

1418 end_time = column_property(date_in_timezone(to_date, timezone) + text("interval '1 days'")) 

1419 start_time_to_write_reference = column_property(date_in_timezone(to_date, timezone)) 

1420 # notice 1 day for midnight at the *end of the day*, then 14 days to write a ref 

1421 end_time_to_write_reference = column_property(date_in_timezone(to_date, timezone) + text("interval '15 days'")) 

1422 

1423 status = Column(Enum(HostRequestStatus), nullable=False) 

1424 

1425 host_last_seen_message_id = Column(BigInteger, nullable=False, default=0) 

1426 surfer_last_seen_message_id = Column(BigInteger, nullable=False, default=0) 

1427 

1428 # number of reference reminders sent out 

1429 host_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0")) 

1430 surfer_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0")) 

1431 

1432 # reason why the host/surfer marked that they didn't meet up 

1433 # if null then they haven't marked it such 

1434 host_reason_didnt_meetup = Column(String, nullable=True) 

1435 surfer_reason_didnt_meetup = Column(String, nullable=True) 

1436 

1437 surfer = relationship("User", backref="host_requests_sent", foreign_keys="HostRequest.surfer_user_id") 

1438 host = relationship("User", backref="host_requests_received", foreign_keys="HostRequest.host_user_id") 

1439 conversation = relationship("Conversation") 

1440 

1441 __table_args__ = ( 

1442 # allows fast lookup as to whether they didn't meet up 

1443 Index( 

1444 "ix_host_requests_host_didnt_meetup", 

1445 host_reason_didnt_meetup != None, 

1446 ), 

1447 Index( 

1448 "ix_host_requests_surfer_didnt_meetup", 

1449 surfer_reason_didnt_meetup != None, 

1450 ), 

1451 ) 

1452 

1453 @hybrid_property 

1454 def can_write_reference(self): 

1455 return ( 

1456 ((self.status == HostRequestStatus.confirmed) | (self.status == HostRequestStatus.accepted)) 

1457 & (now() >= self.start_time_to_write_reference) 

1458 & (now() <= self.end_time_to_write_reference) 

1459 ) 

1460 

1461 @can_write_reference.expression 

1462 def can_write_reference(cls): 

1463 return ( 

1464 ((cls.status == HostRequestStatus.confirmed) | (cls.status == HostRequestStatus.accepted)) 

1465 & (func.now() >= cls.start_time_to_write_reference) 

1466 & (func.now() <= cls.end_time_to_write_reference) 

1467 ) 

1468 

1469 def __repr__(self): 

1470 return f"HostRequest(id={self.conversation_id}, surfer_user_id={self.surfer_user_id}, host_user_id={self.host_user_id}...)" 

1471 

1472 

1473class ReferenceType(enum.Enum): 

1474 friend = enum.auto() 

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

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

1477 

1478 

1479class Reference(Base): 

1480 """ 

1481 Reference from one user to another 

1482 """ 

1483 

1484 __tablename__ = "references" 

1485 

1486 id = Column(BigInteger, primary_key=True) 

1487 # timezone should always be UTC 

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

1489 

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

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

1492 

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

1494 

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

1496 

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

1498 # text that's only visible to mods 

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

1500 

1501 rating = Column(Float, nullable=False) 

1502 was_appropriate = Column(Boolean, nullable=False) 

1503 

1504 is_deleted = Column(Boolean, nullable=False, default=False, server_default="false") 

1505 

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

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

1508 

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

1510 

1511 __table_args__ = ( 

1512 # Rating must be between 0 and 1, inclusive 

1513 CheckConstraint( 

1514 "rating BETWEEN 0 AND 1", 

1515 name="rating_between_0_and_1", 

1516 ), 

1517 # Has host_request_id or it's a friend reference 

1518 CheckConstraint( 

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

1520 name="host_request_id_xor_friend_reference", 

1521 ), 

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

1523 Index( 

1524 "ix_references_unique_friend_reference", 

1525 from_user_id, 

1526 to_user_id, 

1527 reference_type, 

1528 unique=True, 

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

1530 ), 

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

1532 Index( 

1533 "ix_references_unique_per_host_request", 

1534 from_user_id, 

1535 to_user_id, 

1536 host_request_id, 

1537 unique=True, 

1538 postgresql_where=(host_request_id != None), 

1539 ), 

1540 ) 

1541 

1542 @property 

1543 def should_report(self): 

1544 """ 

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

1546 """ 

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

1548 

1549 

1550class InitiatedUpload(Base): 

1551 """ 

1552 Started downloads, not necessarily complete yet. 

1553 """ 

1554 

1555 __tablename__ = "initiated_uploads" 

1556 

1557 key = Column(String, primary_key=True) 

1558 

1559 # timezones should always be UTC 

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

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

1562 

1563 initiator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

1564 

1565 initiator_user = relationship("User") 

1566 

1567 @hybrid_property 

1568 def is_valid(self): 

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

1570 

1571 

1572class Upload(Base): 

1573 """ 

1574 Completed uploads. 

1575 """ 

1576 

1577 __tablename__ = "uploads" 

1578 key = Column(String, primary_key=True) 

1579 

1580 filename = Column(String, nullable=False) 

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

1582 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

1583 

1584 # photo credit, etc 

1585 credit = Column(String, nullable=True) 

1586 

1587 creator_user = relationship("User", backref="uploads", foreign_keys="Upload.creator_user_id") 

1588 

1589 def _url(self, size): 

1590 return urls.media_url(filename=self.filename, size=size) 

1591 

1592 @property 

1593 def thumbnail_url(self): 

1594 return self._url("thumbnail") 

1595 

1596 @property 

1597 def full_url(self): 

1598 return self._url("full") 

1599 

1600 

1601communities_seq = Sequence("communities_seq") 

1602 

1603 

1604class Node(Base): 

1605 """ 

1606 Node, i.e. geographical subdivision of the world 

1607 

1608 Administered by the official cluster 

1609 """ 

1610 

1611 __tablename__ = "nodes" 

1612 

1613 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value()) 

1614 

1615 # name and description come from official cluster 

1616 parent_node_id = Column(ForeignKey("nodes.id"), nullable=True, index=True) 

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

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

1619 

1620 parent_node = relationship("Node", backref="child_nodes", remote_side="Node.id") 

1621 

1622 contained_users = relationship( 

1623 "User", 

1624 primaryjoin="func.ST_Contains(foreign(Node.geom), User.geom).as_comparison(1, 2)", 

1625 viewonly=True, 

1626 uselist=True, 

1627 ) 

1628 

1629 contained_user_ids = association_proxy("contained_users", "id") 

1630 

1631 

1632class Cluster(Base): 

1633 """ 

1634 Cluster, administered grouping of content 

1635 """ 

1636 

1637 __tablename__ = "clusters" 

1638 

1639 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value()) 

1640 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True) 

1641 name = Column(String, nullable=False) 

1642 # short description 

1643 description = Column(String, nullable=False) 

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

1645 

1646 is_official_cluster = Column(Boolean, nullable=False, default=False) 

1647 

1648 slug = column_property(func.slugify(name)) 

1649 

1650 official_cluster_for_node = relationship( 

1651 "Node", 

1652 primaryjoin="and_(Cluster.parent_node_id == Node.id, Cluster.is_official_cluster)", 

1653 backref=backref("official_cluster", uselist=False), 

1654 uselist=False, 

1655 viewonly=True, 

1656 ) 

1657 

1658 parent_node = relationship( 

1659 "Node", backref="child_clusters", remote_side="Node.id", foreign_keys="Cluster.parent_node_id" 

1660 ) 

1661 

1662 nodes = relationship("Cluster", backref="clusters", secondary="node_cluster_associations", viewonly=True) 

1663 # all pages 

1664 pages = relationship( 

1665 "Page", backref="clusters", secondary="cluster_page_associations", lazy="dynamic", viewonly=True 

1666 ) 

1667 events = relationship("Event", backref="clusters", secondary="cluster_event_associations", viewonly=True) 

1668 discussions = relationship( 

1669 "Discussion", backref="clusters", secondary="cluster_discussion_associations", viewonly=True 

1670 ) 

1671 

1672 # includes also admins 

1673 members = relationship( 

1674 "User", 

1675 lazy="dynamic", 

1676 backref="cluster_memberships", 

1677 secondary="cluster_subscriptions", 

1678 primaryjoin="Cluster.id == ClusterSubscription.cluster_id", 

1679 secondaryjoin="User.id == ClusterSubscription.user_id", 

1680 viewonly=True, 

1681 ) 

1682 

1683 admins = relationship( 

1684 "User", 

1685 lazy="dynamic", 

1686 backref="cluster_adminships", 

1687 secondary="cluster_subscriptions", 

1688 primaryjoin="Cluster.id == ClusterSubscription.cluster_id", 

1689 secondaryjoin="and_(User.id == ClusterSubscription.user_id, ClusterSubscription.role == 'admin')", 

1690 viewonly=True, 

1691 ) 

1692 

1693 main_page = relationship( 

1694 "Page", 

1695 primaryjoin="and_(Cluster.id == Page.owner_cluster_id, Page.type == 'main_page')", 

1696 viewonly=True, 

1697 uselist=False, 

1698 ) 

1699 

1700 @property 

1701 def is_leaf(self) -> bool: 

1702 """Whether the cluster is a leaf node in the cluster hierarchy.""" 

1703 return len(self.parent_node.child_nodes) == 0 

1704 

1705 __table_args__ = ( 

1706 # Each node can have at most one official cluster 

1707 Index( 

1708 "ix_clusters_owner_parent_node_id_is_official_cluster", 

1709 parent_node_id, 

1710 is_official_cluster, 

1711 unique=True, 

1712 postgresql_where=is_official_cluster, 

1713 ), 

1714 ) 

1715 

1716 

1717class NodeClusterAssociation(Base): 

1718 """ 

1719 NodeClusterAssociation, grouping of nodes 

1720 """ 

1721 

1722 __tablename__ = "node_cluster_associations" 

1723 __table_args__ = (UniqueConstraint("node_id", "cluster_id"),) 

1724 

1725 id = Column(BigInteger, primary_key=True) 

1726 

1727 node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True) 

1728 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True) 

1729 

1730 node = relationship("Node", backref="node_cluster_associations") 

1731 cluster = relationship("Cluster", backref="node_cluster_associations") 

1732 

1733 

1734class ClusterRole(enum.Enum): 

1735 member = enum.auto() 

1736 admin = enum.auto() 

1737 

1738 

1739class ClusterSubscription(Base): 

1740 """ 

1741 ClusterSubscription of a user 

1742 """ 

1743 

1744 __tablename__ = "cluster_subscriptions" 

1745 __table_args__ = (UniqueConstraint("user_id", "cluster_id"),) 

1746 

1747 id = Column(BigInteger, primary_key=True) 

1748 

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

1750 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True) 

1751 role = Column(Enum(ClusterRole), nullable=False) 

1752 

1753 user = relationship("User", backref="cluster_subscriptions") 

1754 cluster = relationship("Cluster", backref="cluster_subscriptions") 

1755 

1756 

1757class ClusterPageAssociation(Base): 

1758 """ 

1759 pages related to clusters 

1760 """ 

1761 

1762 __tablename__ = "cluster_page_associations" 

1763 __table_args__ = (UniqueConstraint("page_id", "cluster_id"),) 

1764 

1765 id = Column(BigInteger, primary_key=True) 

1766 

1767 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True) 

1768 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True) 

1769 

1770 page = relationship("Page", backref="cluster_page_associations") 

1771 cluster = relationship("Cluster", backref="cluster_page_associations") 

1772 

1773 

1774class PageType(enum.Enum): 

1775 main_page = enum.auto() 

1776 place = enum.auto() 

1777 guide = enum.auto() 

1778 

1779 

1780class Page(Base): 

1781 """ 

1782 similar to a wiki page about a community, POI or guide 

1783 """ 

1784 

1785 __tablename__ = "pages" 

1786 

1787 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value()) 

1788 

1789 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True) 

1790 type = Column(Enum(PageType), nullable=False) 

1791 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

1792 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True) 

1793 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True) 

1794 

1795 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True) 

1796 

1797 parent_node = relationship("Node", backref="child_pages", remote_side="Node.id", foreign_keys="Page.parent_node_id") 

1798 

1799 thread = relationship("Thread", backref="page", uselist=False) 

1800 creator_user = relationship("User", backref="created_pages", foreign_keys="Page.creator_user_id") 

1801 owner_user = relationship("User", backref="owned_pages", foreign_keys="Page.owner_user_id") 

1802 owner_cluster = relationship( 

1803 "Cluster", backref=backref("owned_pages", lazy="dynamic"), uselist=False, foreign_keys="Page.owner_cluster_id" 

1804 ) 

1805 

1806 editors = relationship("User", secondary="page_versions", viewonly=True) 

1807 

1808 __table_args__ = ( 

1809 # Only one of owner_user and owner_cluster should be set 

1810 CheckConstraint( 

1811 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)", 

1812 name="one_owner", 

1813 ), 

1814 # Only clusters can own main pages 

1815 CheckConstraint( 

1816 "NOT (owner_cluster_id IS NULL AND type = 'main_page')", 

1817 name="main_page_owned_by_cluster", 

1818 ), 

1819 # Each cluster can have at most one main page 

1820 Index( 

1821 "ix_pages_owner_cluster_id_type", 

1822 owner_cluster_id, 

1823 type, 

1824 unique=True, 

1825 postgresql_where=(type == PageType.main_page), 

1826 ), 

1827 ) 

1828 

1829 def __repr__(self): 

1830 return f"Page({self.id=})" 

1831 

1832 

1833class PageVersion(Base): 

1834 """ 

1835 version of page content 

1836 """ 

1837 

1838 __tablename__ = "page_versions" 

1839 

1840 id = Column(BigInteger, primary_key=True) 

1841 

1842 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True) 

1843 editor_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

1844 title = Column(String, nullable=False) 

1845 content = Column(String, nullable=False) # CommonMark without images 

1846 photo_key = Column(ForeignKey("uploads.key"), nullable=True) 

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

1848 # the human-readable address 

1849 address = Column(String, nullable=True) 

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

1851 

1852 slug = column_property(func.slugify(title)) 

1853 

1854 page = relationship("Page", backref="versions", order_by="PageVersion.id") 

1855 editor_user = relationship("User", backref="edited_pages") 

1856 photo = relationship("Upload") 

1857 

1858 __table_args__ = ( 

1859 # Geom and address must either both be null or both be set 

1860 CheckConstraint( 

1861 "(geom IS NULL) = (address IS NULL)", 

1862 name="geom_iff_address", 

1863 ), 

1864 ) 

1865 

1866 @property 

1867 def coordinates(self): 

1868 # returns (lat, lng) or None 

1869 return get_coordinates(self.geom) 

1870 

1871 def __repr__(self): 

1872 return f"PageVersion({self.id=}, {self.page_id=})" 

1873 

1874 

1875class ClusterEventAssociation(Base): 

1876 """ 

1877 events related to clusters 

1878 """ 

1879 

1880 __tablename__ = "cluster_event_associations" 

1881 __table_args__ = (UniqueConstraint("event_id", "cluster_id"),) 

1882 

1883 id = Column(BigInteger, primary_key=True) 

1884 

1885 event_id = Column(ForeignKey("events.id"), nullable=False, index=True) 

1886 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True) 

1887 

1888 event = relationship("Event", backref="cluster_event_associations") 

1889 cluster = relationship("Cluster", backref="cluster_event_associations") 

1890 

1891 

1892class Event(Base): 

1893 """ 

1894 An event is compose of two parts: 

1895 

1896 * An event template (Event) 

1897 * An occurrence (EventOccurrence) 

1898 

1899 One-off events will have one of each; repeating events will have one Event, 

1900 multiple EventOccurrences, one for each time the event happens. 

1901 """ 

1902 

1903 __tablename__ = "events" 

1904 

1905 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value()) 

1906 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True) 

1907 

1908 title = Column(String, nullable=False) 

1909 

1910 slug = column_property(func.slugify(title)) 

1911 

1912 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

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

1914 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True) 

1915 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True) 

1916 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True) 

1917 

1918 parent_node = relationship( 

1919 "Node", backref="child_events", remote_side="Node.id", foreign_keys="Event.parent_node_id" 

1920 ) 

1921 thread = relationship("Thread", backref="event", uselist=False) 

1922 subscribers = relationship( 

1923 "User", backref="subscribed_events", secondary="event_subscriptions", lazy="dynamic", viewonly=True 

1924 ) 

1925 organizers = relationship( 

1926 "User", backref="organized_events", secondary="event_organizers", lazy="dynamic", viewonly=True 

1927 ) 

1928 creator_user = relationship("User", backref="created_events", foreign_keys="Event.creator_user_id") 

1929 owner_user = relationship("User", backref="owned_events", foreign_keys="Event.owner_user_id") 

1930 owner_cluster = relationship( 

1931 "Cluster", 

1932 backref=backref("owned_events", lazy="dynamic"), 

1933 uselist=False, 

1934 foreign_keys="Event.owner_cluster_id", 

1935 ) 

1936 

1937 __table_args__ = ( 

1938 # Only one of owner_user and owner_cluster should be set 

1939 CheckConstraint( 

1940 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)", 

1941 name="one_owner", 

1942 ), 

1943 ) 

1944 

1945 

1946class EventOccurrence(Base): 

1947 __tablename__ = "event_occurrences" 

1948 

1949 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value()) 

1950 event_id = Column(ForeignKey("events.id"), nullable=False, index=True) 

1951 

1952 # the user that created this particular occurrence of a repeating event (same as event.creator_user_id if single event) 

1953 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

1954 content = Column(String, nullable=False) # CommonMark without images 

1955 photo_key = Column(ForeignKey("uploads.key"), nullable=True) 

1956 

1957 is_cancelled = Column(Boolean, nullable=False, default=False, server_default=text("false")) 

1958 is_deleted = Column(Boolean, nullable=False, default=False, server_default=text("false")) 

1959 

1960 # a null geom is an online-only event 

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

1962 # physical address, iff geom is not null 

1963 address = Column(String, nullable=True) 

1964 # videoconferencing link, etc, must be specified if no geom, otherwise optional 

1965 link = Column(String, nullable=True) 

1966 

1967 timezone = "Etc/UTC" 

1968 

1969 # time during which the event takes place; this is a range type (instead of separate start+end times) which 

1970 # simplifies database constraints, etc 

1971 during = Column(TSTZRANGE, nullable=False) 

1972 

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

1974 last_edited = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

1975 

1976 creator_user = relationship( 

1977 "User", backref="created_event_occurrences", foreign_keys="EventOccurrence.creator_user_id" 

1978 ) 

1979 event = relationship( 

1980 "Event", 

1981 backref=backref("occurrences", lazy="dynamic"), 

1982 remote_side="Event.id", 

1983 foreign_keys="EventOccurrence.event_id", 

1984 ) 

1985 

1986 photo = relationship("Upload") 

1987 

1988 __table_args__ = ( 

1989 # Geom and address go together 

1990 CheckConstraint( 

1991 # geom and address are either both null or neither of them are null 

1992 "(geom IS NULL) = (address IS NULL)", 

1993 name="geom_iff_address", 

1994 ), 

1995 # Online-only events need a link, note that online events may also have a link 

1996 CheckConstraint( 

1997 # exactly oen of geom or link is non-null 

1998 "(geom IS NULL) <> (link IS NULL)", 

1999 name="link_or_geom", 

2000 ), 

2001 # Can't have overlapping occurrences in the same Event 

2002 ExcludeConstraint(("event_id", "="), ("during", "&&"), name="event_occurrences_event_id_during_excl"), 

2003 ) 

2004 

2005 @property 

2006 def coordinates(self): 

2007 # returns (lat, lng) or None 

2008 return get_coordinates(self.geom) 

2009 

2010 @hybrid_property 

2011 def start_time(self): 

2012 return self.during.lower 

2013 

2014 @start_time.expression 

2015 def start_time(cls): 

2016 return func.lower(cls.during) 

2017 

2018 @hybrid_property 

2019 def end_time(self): 

2020 return self.during.upper 

2021 

2022 @end_time.expression 

2023 def end_time(cls): 

2024 return func.upper(cls.during) 

2025 

2026 

2027class EventSubscription(Base): 

2028 """ 

2029 Users' subscriptions to events 

2030 """ 

2031 

2032 __tablename__ = "event_subscriptions" 

2033 __table_args__ = (UniqueConstraint("event_id", "user_id"),) 

2034 

2035 id = Column(BigInteger, primary_key=True) 

2036 

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

2038 event_id = Column(ForeignKey("events.id"), nullable=False, index=True) 

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

2040 

2041 user = relationship("User") 

2042 event = relationship("Event") 

2043 

2044 

2045class EventOrganizer(Base): 

2046 """ 

2047 Organizers for events 

2048 """ 

2049 

2050 __tablename__ = "event_organizers" 

2051 __table_args__ = (UniqueConstraint("event_id", "user_id"),) 

2052 

2053 id = Column(BigInteger, primary_key=True) 

2054 

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

2056 event_id = Column(ForeignKey("events.id"), nullable=False, index=True) 

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

2058 

2059 user = relationship("User") 

2060 event = relationship("Event") 

2061 

2062 

2063class AttendeeStatus(enum.Enum): 

2064 going = enum.auto() 

2065 maybe = enum.auto() 

2066 

2067 

2068class EventOccurrenceAttendee(Base): 

2069 """ 

2070 Attendees for events 

2071 """ 

2072 

2073 __tablename__ = "event_occurrence_attendees" 

2074 __table_args__ = (UniqueConstraint("occurrence_id", "user_id"),) 

2075 

2076 id = Column(BigInteger, primary_key=True) 

2077 

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

2079 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True) 

2080 responded = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

2081 attendee_status = Column(Enum(AttendeeStatus), nullable=False) 

2082 

2083 user = relationship("User") 

2084 occurrence = relationship("EventOccurrence", backref=backref("attendances", lazy="dynamic")) 

2085 

2086 

2087class EventCommunityInviteRequest(Base): 

2088 """ 

2089 Requests to send out invitation notifications/emails to the community for a given event occurrence 

2090 """ 

2091 

2092 __tablename__ = "event_community_invite_requests" 

2093 

2094 id = Column(BigInteger, primary_key=True) 

2095 

2096 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True) 

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

2098 

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

2100 

2101 decided = Column(DateTime(timezone=True), nullable=True) 

2102 decided_by_user_id = Column(ForeignKey("users.id"), nullable=True) 

2103 approved = Column(Boolean, nullable=True) 

2104 

2105 occurrence = relationship("EventOccurrence", backref=backref("community_invite_requests", lazy="dynamic")) 

2106 user = relationship("User", foreign_keys="EventCommunityInviteRequest.user_id") 

2107 

2108 __table_args__ = ( 

2109 # each user can only request once 

2110 UniqueConstraint("occurrence_id", "user_id"), 

2111 # each event can only have one notification sent out 

2112 Index( 

2113 "ix_event_community_invite_requests_unique", 

2114 occurrence_id, 

2115 unique=True, 

2116 postgresql_where=and_(approved.is_not(None), approved == True), 

2117 ), 

2118 # decided and approved ought to be null simultaneously 

2119 CheckConstraint( 

2120 "((decided IS NULL) AND (decided_by_user_id IS NULL) AND (approved IS NULL)) OR \ 

2121 ((decided IS NOT NULL) AND (decided_by_user_id IS NOT NULL) AND (approved IS NOT NULL))", 

2122 name="decided_approved", 

2123 ), 

2124 ) 

2125 

2126 

2127class ClusterDiscussionAssociation(Base): 

2128 """ 

2129 discussions related to clusters 

2130 """ 

2131 

2132 __tablename__ = "cluster_discussion_associations" 

2133 __table_args__ = (UniqueConstraint("discussion_id", "cluster_id"),) 

2134 

2135 id = Column(BigInteger, primary_key=True) 

2136 

2137 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True) 

2138 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True) 

2139 

2140 discussion = relationship("Discussion", backref="cluster_discussion_associations") 

2141 cluster = relationship("Cluster", backref="cluster_discussion_associations") 

2142 

2143 

2144class Discussion(Base): 

2145 """ 

2146 forum board 

2147 """ 

2148 

2149 __tablename__ = "discussions" 

2150 

2151 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value()) 

2152 

2153 title = Column(String, nullable=False) 

2154 content = Column(String, nullable=False) 

2155 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True) 

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

2157 

2158 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

2159 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True) 

2160 

2161 slug = column_property(func.slugify(title)) 

2162 

2163 thread = relationship("Thread", backref="discussion", uselist=False) 

2164 

2165 subscribers = relationship("User", backref="discussions", secondary="discussion_subscriptions", viewonly=True) 

2166 

2167 creator_user = relationship("User", backref="created_discussions", foreign_keys="Discussion.creator_user_id") 

2168 owner_cluster = relationship("Cluster", backref=backref("owned_discussions", lazy="dynamic"), uselist=False) 

2169 

2170 

2171class DiscussionSubscription(Base): 

2172 """ 

2173 users subscriptions to discussions 

2174 """ 

2175 

2176 __tablename__ = "discussion_subscriptions" 

2177 __table_args__ = (UniqueConstraint("discussion_id", "user_id"),) 

2178 

2179 id = Column(BigInteger, primary_key=True) 

2180 

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

2182 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True) 

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

2184 left = Column(DateTime(timezone=True), nullable=True) 

2185 

2186 user = relationship("User", backref="discussion_subscriptions") 

2187 discussion = relationship("Discussion", backref="discussion_subscriptions") 

2188 

2189 

2190class Thread(Base): 

2191 """ 

2192 Thread 

2193 """ 

2194 

2195 __tablename__ = "threads" 

2196 

2197 id = Column(BigInteger, primary_key=True) 

2198 

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

2200 deleted = Column(DateTime(timezone=True), nullable=True) 

2201 

2202 

2203class Comment(Base): 

2204 """ 

2205 Comment 

2206 """ 

2207 

2208 __tablename__ = "comments" 

2209 

2210 id = Column(BigInteger, primary_key=True) 

2211 

2212 thread_id = Column(ForeignKey("threads.id"), nullable=False, index=True) 

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

2214 content = Column(String, nullable=False) # CommonMark without images 

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

2216 deleted = Column(DateTime(timezone=True), nullable=True) 

2217 

2218 thread = relationship("Thread", backref="comments") 

2219 

2220 

2221class Reply(Base): 

2222 """ 

2223 Reply 

2224 """ 

2225 

2226 __tablename__ = "replies" 

2227 

2228 id = Column(BigInteger, primary_key=True) 

2229 

2230 comment_id = Column(ForeignKey("comments.id"), nullable=False, index=True) 

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

2232 content = Column(String, nullable=False) # CommonMark without images 

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

2234 deleted = Column(DateTime(timezone=True), nullable=True) 

2235 

2236 comment = relationship("Comment", backref="replies") 

2237 

2238 

2239class BackgroundJobState(enum.Enum): 

2240 # job is fresh, waiting to be picked off the queue 

2241 pending = enum.auto() 

2242 # job complete 

2243 completed = enum.auto() 

2244 # error occured, will be retried 

2245 error = enum.auto() 

2246 # failed too many times, not retrying anymore 

2247 failed = enum.auto() 

2248 

2249 

2250class BackgroundJob(Base): 

2251 """ 

2252 This table implements a queue of background jobs. 

2253 """ 

2254 

2255 __tablename__ = "background_jobs" 

2256 

2257 id = Column(BigInteger, primary_key=True) 

2258 

2259 # used to discern which function should be triggered to service it 

2260 job_type = Column(String, nullable=False) 

2261 state = Column(Enum(BackgroundJobState), nullable=False, default=BackgroundJobState.pending) 

2262 

2263 # time queued 

2264 queued = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

2265 

2266 # time at which we may next attempt it, for implementing exponential backoff 

2267 next_attempt_after = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

2268 

2269 # used to count number of retries for failed jobs 

2270 try_count = Column(Integer, nullable=False, default=0) 

2271 

2272 max_tries = Column(Integer, nullable=False, default=5) 

2273 

2274 # higher is more important 

2275 priority = Column(Integer, nullable=False, server_default=text("10")) 

2276 

2277 # protobuf encoded job payload 

2278 payload = Column(Binary, nullable=False) 

2279 

2280 # if the job failed, we write that info here 

2281 failure_info = Column(String, nullable=True) 

2282 

2283 __table_args__ = ( 

2284 # used in looking up background jobs to attempt 

2285 # create index on background_jobs(priority desc, next_attempt_after, (max_tries - try_count)) where state = 'pending' OR state = 'error'; 

2286 Index( 

2287 "ix_background_jobs_lookup", 

2288 priority.desc(), 

2289 next_attempt_after, 

2290 (max_tries - try_count), 

2291 postgresql_where=((state == BackgroundJobState.pending) | (state == BackgroundJobState.error)), 

2292 ), 

2293 ) 

2294 

2295 @hybrid_property 

2296 def ready_for_retry(self): 

2297 return ( 

2298 (self.next_attempt_after <= func.now()) 

2299 & (self.try_count < self.max_tries) 

2300 & ((self.state == BackgroundJobState.pending) | (self.state == BackgroundJobState.error)) 

2301 ) 

2302 

2303 def __repr__(self): 

2304 return f"BackgroundJob(id={self.id}, job_type={self.job_type}, state={self.state}, next_attempt_after={self.next_attempt_after}, try_count={self.try_count}, failure_info={self.failure_info})" 

2305 

2306 

2307class NotificationDeliveryType(enum.Enum): 

2308 # send push notification to mobile/web 

2309 push = enum.auto() 

2310 # send individual email immediately 

2311 email = enum.auto() 

2312 # send in digest 

2313 digest = enum.auto() 

2314 

2315 

2316dt = NotificationDeliveryType 

2317nd = notification_data_pb2 

2318 

2319dt_sec = [dt.email, dt.push] 

2320dt_all = [dt.email, dt.push, dt.digest] 

2321 

2322 

2323class NotificationTopicAction(enum.Enum): 

2324 def __init__(self, topic_action, defaults, user_editable, data_type): 

2325 self.topic, self.action = topic_action.split(":") 

2326 self.defaults = defaults 

2327 # for now user editable == not a security notification 

2328 self.user_editable = user_editable 

2329 

2330 self.data_type = data_type 

2331 

2332 def unpack(self): 

2333 return self.topic, self.action 

2334 

2335 @property 

2336 def display(self): 

2337 return f"{self.topic}:{self.action}" 

2338 

2339 def __str__(self): 

2340 return self.display 

2341 

2342 # topic, action, default delivery types 

2343 friend_request__create = ("friend_request:create", dt_all, True, nd.FriendRequestCreate) 

2344 friend_request__accept = ("friend_request:accept", dt_all, True, nd.FriendRequestAccept) 

2345 

2346 # host requests 

2347 host_request__create = ("host_request:create", dt_all, True, nd.HostRequestCreate) 

2348 host_request__accept = ("host_request:accept", dt_all, True, nd.HostRequestAccept) 

2349 host_request__reject = ("host_request:reject", dt_all, True, nd.HostRequestReject) 

2350 host_request__confirm = ("host_request:confirm", dt_all, True, nd.HostRequestConfirm) 

2351 host_request__cancel = ("host_request:cancel", dt_all, True, nd.HostRequestCancel) 

2352 host_request__message = ("host_request:message", [dt.push, dt.digest], True, nd.HostRequestMessage) 

2353 host_request__missed_messages = ("host_request:missed_messages", [dt.email], True, nd.HostRequestMissedMessages) 

2354 

2355 activeness__probe = ("activeness:probe", dt_sec, False, nd.ActivenessProbe) 

2356 

2357 # you receive a friend ref 

2358 reference__receive_friend = ("reference:receive_friend", dt_all, True, nd.ReferenceReceiveFriend) 

2359 # you receive a reference from ... the host 

2360 reference__receive_hosted = ("reference:receive_hosted", dt_all, True, nd.ReferenceReceiveHostRequest) 

2361 # ... the surfer 

2362 reference__receive_surfed = ("reference:receive_surfed", dt_all, True, nd.ReferenceReceiveHostRequest) 

2363 

2364 # you hosted 

2365 reference__reminder_hosted = ("reference:reminder_hosted", dt_all, True, nd.ReferenceReminder) 

2366 # you surfed 

2367 reference__reminder_surfed = ("reference:reminder_surfed", dt_all, True, nd.ReferenceReminder) 

2368 

2369 badge__add = ("badge:add", [dt.push, dt.digest], True, nd.BadgeAdd) 

2370 badge__remove = ("badge:remove", [dt.push, dt.digest], True, nd.BadgeRemove) 

2371 

2372 # group chats 

2373 chat__message = ("chat:message", [dt.push, dt.digest], True, nd.ChatMessage) 

2374 chat__missed_messages = ("chat:missed_messages", [dt.email], True, nd.ChatMissedMessages) 

2375 

2376 # events 

2377 # approved by mods 

2378 event__create_approved = ("event:create_approved", dt_all, True, nd.EventCreate) 

2379 # any user creates any event, default to no notifications 

2380 event__create_any = ("event:create_any", [], True, nd.EventCreate) 

2381 event__update = ("event:update", dt_all, True, nd.EventUpdate) 

2382 event__cancel = ("event:cancel", dt_all, True, nd.EventCancel) 

2383 event__delete = ("event:delete", dt_all, True, nd.EventDelete) 

2384 event__invite_organizer = ("event:invite_organizer", dt_all, True, nd.EventInviteOrganizer) 

2385 # toplevel comment on an event 

2386 event__comment = ("event:comment", dt_all, True, nd.EventComment) 

2387 

2388 # discussion created 

2389 discussion__create = ("discussion:create", [dt.digest], True, nd.DiscussionCreate) 

2390 # someone comments on your discussion 

2391 discussion__comment = ("discussion:comment", dt_all, True, nd.DiscussionComment) 

2392 

2393 # someone responds to any of your top-level comment across the platform 

2394 thread__reply = ("thread:reply", dt_all, True, nd.ThreadReply) 

2395 

2396 # account settings 

2397 password__change = ("password:change", dt_sec, False, empty_pb2.Empty) 

2398 email_address__change = ("email_address:change", dt_sec, False, nd.EmailAddressChange) 

2399 email_address__verify = ("email_address:verify", dt_sec, False, empty_pb2.Empty) 

2400 phone_number__change = ("phone_number:change", dt_sec, False, nd.PhoneNumberChange) 

2401 phone_number__verify = ("phone_number:verify", dt_sec, False, nd.PhoneNumberVerify) 

2402 # reset password 

2403 password_reset__start = ("password_reset:start", dt_sec, False, nd.PasswordResetStart) 

2404 password_reset__complete = ("password_reset:complete", dt_sec, False, empty_pb2.Empty) 

2405 

2406 # account deletion 

2407 account_deletion__start = ("account_deletion:start", dt_sec, False, nd.AccountDeletionStart) 

2408 # no more pushing to do 

2409 account_deletion__complete = ("account_deletion:complete", dt_sec, False, nd.AccountDeletionComplete) 

2410 # undeleted 

2411 account_deletion__recovered = ("account_deletion:recovered", dt_sec, False, empty_pb2.Empty) 

2412 

2413 # admin actions 

2414 gender__change = ("gender:change", dt_sec, False, nd.GenderChange) 

2415 birthdate__change = ("birthdate:change", dt_sec, False, nd.BirthdateChange) 

2416 api_key__create = ("api_key:create", dt_sec, False, nd.ApiKeyCreate) 

2417 

2418 donation__received = ("donation:received", dt_sec, True, nd.DonationReceived) 

2419 

2420 onboarding__reminder = ("onboarding:reminder", dt_sec, True, empty_pb2.Empty) 

2421 

2422 modnote__create = ("modnote:create", dt_sec, False, empty_pb2.Empty) 

2423 

2424 verification__sv_fail = ("verification:sv_fail", dt_sec, False, nd.VerificationSVFail) 

2425 verification__sv_success = ("verification:sv_success", dt_sec, False, empty_pb2.Empty) 

2426 

2427 

2428class NotificationPreference(Base): 

2429 __tablename__ = "notification_preferences" 

2430 

2431 id = Column(BigInteger, primary_key=True) 

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

2433 

2434 topic_action = Column(Enum(NotificationTopicAction), nullable=False) 

2435 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False) 

2436 deliver = Column(Boolean, nullable=False) 

2437 

2438 user = relationship("User", foreign_keys="NotificationPreference.user_id") 

2439 

2440 __table_args__ = (UniqueConstraint("user_id", "topic_action", "delivery_type"),) 

2441 

2442 

2443class Notification(Base): 

2444 """ 

2445 Table for accumulating notifications until it is time to send email digest 

2446 """ 

2447 

2448 __tablename__ = "notifications" 

2449 

2450 id = Column(BigInteger, primary_key=True) 

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

2452 

2453 # recipient user id 

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

2455 

2456 topic_action = Column(Enum(NotificationTopicAction), nullable=False) 

2457 key = Column(String, nullable=False) 

2458 

2459 data = Column(Binary, nullable=False) 

2460 

2461 user = relationship("User", foreign_keys="Notification.user_id") 

2462 

2463 __table_args__ = ( 

2464 # used in looking up which notifications need delivery 

2465 Index( 

2466 "ix_notifications_created", 

2467 created, 

2468 ), 

2469 ) 

2470 

2471 @property 

2472 def topic(self): 

2473 return self.topic_action.topic 

2474 

2475 @property 

2476 def action(self): 

2477 return self.topic_action.action 

2478 

2479 

2480class NotificationDelivery(Base): 

2481 __tablename__ = "notification_deliveries" 

2482 

2483 id = Column(BigInteger, primary_key=True) 

2484 notification_id = Column(ForeignKey("notifications.id"), nullable=False, index=True) 

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

2486 delivered = Column(DateTime(timezone=True), nullable=True) 

2487 read = Column(DateTime(timezone=True), nullable=True) 

2488 # todo: enum of "phone, web, digest" 

2489 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False) 

2490 # todo: device id 

2491 # todo: receipt id, etc 

2492 notification = relationship("Notification", foreign_keys="NotificationDelivery.notification_id") 

2493 

2494 __table_args__ = ( 

2495 UniqueConstraint("notification_id", "delivery_type"), 

2496 # used in looking up which notifications need delivery 

2497 Index( 

2498 "ix_notification_deliveries_delivery_type", 

2499 delivery_type, 

2500 postgresql_where=(delivered != None), 

2501 ), 

2502 Index( 

2503 "ix_notification_deliveries_dt_ni_dnull", 

2504 delivery_type, 

2505 notification_id, 

2506 delivered == None, 

2507 ), 

2508 ) 

2509 

2510 

2511class PushNotificationSubscription(Base): 

2512 __tablename__ = "push_notification_subscriptions" 

2513 

2514 id = Column(BigInteger, primary_key=True) 

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

2516 

2517 # which user this is connected to 

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

2519 

2520 # these come from https://developer.mozilla.org/en-US/docs/Web/API/PushSubscription 

2521 # the endpoint 

2522 endpoint = Column(String, nullable=False) 

2523 # the "auth" key 

2524 auth_key = Column(Binary, nullable=False) 

2525 # the "p256dh" key 

2526 p256dh_key = Column(Binary, nullable=False) 

2527 

2528 full_subscription_info = Column(String, nullable=False) 

2529 

2530 # the browse user-agent, so we can tell the user what browser notifications are going to 

2531 user_agent = Column(String, nullable=True) 

2532 

2533 # when it was disabled 

2534 disabled_at = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_INFINITY.isoformat()) 

2535 

2536 user = relationship("User") 

2537 

2538 

2539class PushNotificationDeliveryAttempt(Base): 

2540 __tablename__ = "push_notification_delivery_attempt" 

2541 

2542 id = Column(BigInteger, primary_key=True) 

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

2544 

2545 push_notification_subscription_id = Column( 

2546 ForeignKey("push_notification_subscriptions.id"), nullable=False, index=True 

2547 ) 

2548 

2549 success = Column(Boolean, nullable=False) 

2550 # the HTTP status code, 201 is success 

2551 status_code = Column(Integer, nullable=False) 

2552 

2553 # can be null if it was a success 

2554 response = Column(String, nullable=True) 

2555 

2556 push_notification_subscription = relationship("PushNotificationSubscription") 

2557 

2558 

2559class Language(Base): 

2560 """ 

2561 Table of allowed languages (a subset of ISO639-3) 

2562 """ 

2563 

2564 __tablename__ = "languages" 

2565 

2566 # ISO639-3 language code, in lowercase, e.g. fin, eng 

2567 code = Column(String(3), primary_key=True) 

2568 

2569 # the english name 

2570 name = Column(String, nullable=False, unique=True) 

2571 

2572 

2573class Region(Base): 

2574 """ 

2575 Table of regions 

2576 """ 

2577 

2578 __tablename__ = "regions" 

2579 

2580 # iso 3166-1 alpha3 code in uppercase, e.g. FIN, USA 

2581 code = Column(String(3), primary_key=True) 

2582 

2583 # the name, e.g. Finland, United States 

2584 # this is the display name in English, should be the "common name", not "Republic of Finland" 

2585 name = Column(String, nullable=False, unique=True) 

2586 

2587 

2588class UserBlock(Base): 

2589 """ 

2590 Table of blocked users 

2591 """ 

2592 

2593 __tablename__ = "user_blocks" 

2594 __table_args__ = (UniqueConstraint("blocking_user_id", "blocked_user_id"),) 

2595 

2596 id = Column(BigInteger, primary_key=True) 

2597 

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

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

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

2601 

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

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

2604 

2605 

2606class APICall(Base): 

2607 """ 

2608 API call logs 

2609 """ 

2610 

2611 __tablename__ = "api_calls" 

2612 __table_args__ = {"schema": "logging"} 

2613 

2614 id = Column(BigInteger, primary_key=True) 

2615 

2616 # whether the call was made using an api key or session cookies 

2617 is_api_key = Column(Boolean, nullable=False, server_default=text("false")) 

2618 

2619 # backend version (normally e.g. develop-31469e3), allows us to figure out which proto definitions were used 

2620 # note that `default` is a python side default, not hardcoded into DB schema 

2621 version = Column(String, nullable=False, default=config["VERSION"]) 

2622 

2623 # approximate time of the call 

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

2625 

2626 # the method call name, e.g. "/org.couchers.api.core.API/ListFriends" 

2627 method = Column(String, nullable=False) 

2628 

2629 # gRPC status code name, e.g. FAILED_PRECONDITION, None if success 

2630 status_code = Column(String, nullable=True) 

2631 

2632 # handler duration (excluding serialization, etc) 

2633 duration = Column(Float, nullable=False) 

2634 

2635 # user_id of caller, None means not logged in 

2636 user_id = Column(BigInteger, nullable=True) 

2637 

2638 # sanitized request bytes 

2639 request = Column(Binary, nullable=True) 

2640 

2641 # sanitized response bytes 

2642 response = Column(Binary, nullable=True) 

2643 

2644 # whether response bytes have been truncated 

2645 response_truncated = Column(Boolean, nullable=False, server_default=text("false")) 

2646 

2647 # the exception traceback, if any 

2648 traceback = Column(String, nullable=True) 

2649 

2650 # human readable perf report 

2651 perf_report = Column(String, nullable=True) 

2652 

2653 # details of the browser, if available 

2654 ip_address = Column(String, nullable=True) 

2655 user_agent = Column(String, nullable=True) 

2656 

2657 

2658class AccountDeletionReason(Base): 

2659 __tablename__ = "account_deletion_reason" 

2660 

2661 id = Column(BigInteger, primary_key=True) 

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

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

2664 reason = Column(String, nullable=True) 

2665 

2666 user = relationship("User")