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

1156 statements  

« prev     ^ index     » next       coverage.py v7.6.10, created at 2025-07-09 00:05 +0000

1import enum 

2 

3from geoalchemy2.types import Geometry 

4from google.protobuf import empty_pb2 

5from sqlalchemy import ( 

6 ARRAY, 

7 JSON, 

8 BigInteger, 

9 Boolean, 

10 CheckConstraint, 

11 Column, 

12 Date, 

13 DateTime, 

14 Enum, 

15 Float, 

16 ForeignKey, 

17 Index, 

18 Integer, 

19 Interval, 

20 MetaData, 

21 Sequence, 

22 String, 

23 UniqueConstraint, 

24) 

25from sqlalchemy import LargeBinary as Binary 

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

27from sqlalchemy.ext.associationproxy import association_proxy 

28from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property 

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

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

31from sqlalchemy.sql import select as sa_select 

32 

33from couchers import urls 

34from couchers.config import config 

35from couchers.constants import ( 

36 DATETIME_INFINITY, 

37 DATETIME_MINUS_INFINITY, 

38 EMAIL_REGEX, 

39 GUIDELINES_VERSION, 

40 PHONE_VERIFICATION_LIFETIME, 

41 SMS_CODE_LIFETIME, 

42 TOS_VERSION, 

43) 

44from couchers.utils import ( 

45 date_in_timezone, 

46 get_coordinates, 

47 last_active_coarsen, 

48 now, 

49) 

50from proto import notification_data_pb2 

51 

52meta = MetaData( 

53 naming_convention={ 

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

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

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

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

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

59 } 

60) 

61 

62Base = declarative_base(metadata=meta) 

63 

64 

65class HostingStatus(enum.Enum): 

66 can_host = enum.auto() 

67 maybe = enum.auto() 

68 cant_host = enum.auto() 

69 

70 

71class MeetupStatus(enum.Enum): 

72 wants_to_meetup = enum.auto() 

73 open_to_meetup = enum.auto() 

74 does_not_want_to_meetup = enum.auto() 

75 

76 

77class SmokingLocation(enum.Enum): 

78 yes = enum.auto() 

79 window = enum.auto() 

80 outside = enum.auto() 

81 no = enum.auto() 

82 

83 

84class SleepingArrangement(enum.Enum): 

85 private = enum.auto() 

86 common = enum.auto() 

87 shared_room = enum.auto() 

88 

89 

90class ParkingDetails(enum.Enum): 

91 free_onsite = enum.auto() 

92 free_offsite = enum.auto() 

93 paid_onsite = enum.auto() 

94 paid_offsite = enum.auto() 

95 

96 

97class ProfilePublicVisibility(enum.Enum): 

98 # no public info 

99 nothing = enum.auto() 

100 # only show on map, randomized, unclickable 

101 map_only = enum.auto() 

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

103 limited = enum.auto() 

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

105 most = enum.auto() 

106 # all but references 

107 full = enum.auto() 

108 

109 

110class TimezoneArea(Base): 

111 __tablename__ = "timezone_areas" 

112 id = Column(BigInteger, primary_key=True) 

113 

114 tzid = Column(String) 

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

116 

117 __table_args__ = ( 

118 Index( 

119 "ix_timezone_areas_geom_tzid", 

120 geom, 

121 tzid, 

122 postgresql_using="gist", 

123 ), 

124 ) 

125 

126 

127class User(Base): 

128 """ 

129 Basic user and profile details 

130 """ 

131 

132 __tablename__ = "users" 

133 

134 id = Column(BigInteger, primary_key=True) 

135 

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

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

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

139 hashed_password = Column(Binary, nullable=False) 

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

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

142 # language preference -- defaults to empty string 

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

144 

145 # timezones should always be UTC 

146 ## location 

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

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

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

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

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

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

153 geom_radius = Column(Float, nullable=False) 

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

155 city = Column(String, nullable=False) 

156 # "Grew up in" on profile 

157 hometown = Column(String, nullable=True) 

158 

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

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

161 

162 timezone = column_property( 

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

164 deferred=True, 

165 ) 

166 

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

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

169 

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

171 has_modified_public_visibility = Column(Boolean, nullable=False, server_default=text("false")) 

172 

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

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

175 # same as above for host requests 

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

177 

178 # display name 

179 name = Column(String, nullable=False) 

180 gender = Column(String, nullable=False) 

181 pronouns = Column(String, nullable=True) 

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

183 

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

185 

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

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

188 

189 # community standing score 

190 community_standing = Column(Float, nullable=True) 

191 

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

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

194 

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

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

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

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

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

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

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

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

203 

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

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

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

207 

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

209 # accidental or they changed their mind 

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

211 undelete_token = Column(String, nullable=True) 

212 # validity of the undelete token 

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

214 

215 # hosting preferences 

216 max_guests = Column(Integer, nullable=True) 

217 last_minute = Column(Boolean, nullable=True) 

218 has_pets = Column(Boolean, nullable=True) 

219 accepts_pets = Column(Boolean, nullable=True) 

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

221 has_kids = Column(Boolean, nullable=True) 

222 accepts_kids = Column(Boolean, nullable=True) 

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

224 has_housemates = Column(Boolean, nullable=True) 

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

226 wheelchair_accessible = Column(Boolean, nullable=True) 

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

228 smokes_at_home = Column(Boolean, nullable=True) 

229 drinking_allowed = Column(Boolean, nullable=True) 

230 drinks_at_home = Column(Boolean, nullable=True) 

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

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

233 

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

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

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

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

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

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

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

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

242 parking = Column(Boolean, nullable=True) 

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

244 camping_ok = Column(Boolean, nullable=True) 

245 

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

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

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

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

250 

251 # number of onboarding emails sent 

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

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

254 

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

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

257 # opted out of the newsletter 

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

259 

260 # set to null to receive no digests 

261 digest_frequency = Column(Interval, nullable=True) 

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

263 

264 # for changing their email 

265 new_email = Column(String, nullable=True) 

266 

267 new_email_token = Column(String, nullable=True) 

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

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

270 

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

272 

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

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

275 # | Start | 

276 # | phone = None | someone else 

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

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

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

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

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

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

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

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

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

286 # | | ^ V | | 

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

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

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

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

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

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

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

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

295 

296 # randomly generated Luhn 6-digit string 

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

298 

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

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

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

302 

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

304 # e.g. cus_JjoXHttuZopv0t 

305 # for new US entity 

306 stripe_customer_id = Column(String, nullable=True) 

307 # for old AU entity 

308 stripe_customer_id_old = Column(String, nullable=True) 

309 

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

311 

312 # checking for phone verification 

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

314 

315 # whether this user has all emails turned off 

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

317 

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

319 

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

321 

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

323 needs_to_update_location = Column(Boolean, nullable=False, server_default=text("false")) 

324 

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

326 

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

328 

329 __table_args__ = ( 

330 # Verified phone numbers should be unique 

331 Index( 

332 "ix_users_unique_phone", 

333 phone, 

334 unique=True, 

335 postgresql_where=phone_verification_verified != None, 

336 ), 

337 Index( 

338 "ix_users_active", 

339 id, 

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

341 ), 

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

343 Index( 

344 "ix_users_geom_active", 

345 geom, 

346 id, 

347 username, 

348 postgresql_using="gist", 

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

350 ), 

351 Index( 

352 "ix_users_by_id", 

353 id, 

354 postgresql_using="hash", 

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

356 ), 

357 Index( 

358 "ix_users_by_username", 

359 username, 

360 postgresql_using="hash", 

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

362 ), 

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

364 CheckConstraint( 

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

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

367 name="check_new_email_token_state", 

368 ), 

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

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

371 CheckConstraint( 

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

373 name="phone_verified_conditions", 

374 ), 

375 # Email must match our regex 

376 CheckConstraint( 

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

378 name="valid_email", 

379 ), 

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

381 CheckConstraint( 

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

383 name="undelete_nullity", 

384 ), 

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

386 CheckConstraint( 

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

388 name="do_not_email_inactive", 

389 ), 

390 ) 

391 

392 @hybrid_property 

393 def has_completed_profile(self): 

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

395 

396 @has_completed_profile.expression 

397 def has_completed_profile(cls): 

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

399 

400 @hybrid_property 

401 def jailed_missing_tos(self): 

402 return self.accepted_tos < TOS_VERSION 

403 

404 @hybrid_property 

405 def jailed_missing_community_guidelines(self): 

406 return self.accepted_community_guidelines < GUIDELINES_VERSION 

407 

408 @hybrid_property 

409 def jailed_pending_mod_notes(self): 

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

411 

412 @hybrid_property 

413 def jailed_pending_activeness_probe(self): 

414 return self.pending_activeness_probe != None 

415 

416 @hybrid_property 

417 def is_jailed(self): 

418 return ( 

419 self.jailed_missing_tos 

420 | self.jailed_missing_community_guidelines 

421 | self.is_missing_location 

422 | self.jailed_pending_mod_notes 

423 | self.jailed_pending_activeness_probe 

424 ) 

425 

426 @hybrid_property 

427 def is_missing_location(self): 

428 return self.needs_to_update_location 

429 

430 @hybrid_property 

431 def is_visible(self): 

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

433 

434 @property 

435 def coordinates(self): 

436 return get_coordinates(self.geom) 

437 

438 @property 

439 def display_joined(self): 

440 """ 

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

442 """ 

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

444 

445 @property 

446 def display_last_active(self): 

447 """ 

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

449 """ 

450 return last_active_coarsen(self.last_active) 

451 

452 @hybrid_property 

453 def phone_is_verified(self): 

454 return ( 

455 self.phone_verification_verified is not None 

456 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME 

457 ) 

458 

459 @phone_is_verified.expression 

460 def phone_is_verified(cls): 

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

462 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME 

463 ) 

464 

465 @hybrid_property 

466 def phone_code_expired(self): 

467 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME 

468 

469 def __repr__(self): 

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

471 

472 

473class UserBadge(Base): 

474 """ 

475 A badge on a user's profile 

476 """ 

477 

478 __tablename__ = "user_badges" 

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

480 

481 id = Column(BigInteger, primary_key=True) 

482 

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

484 # corresponds to "id" in badges.json 

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

486 

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

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

489 

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

491 

492 

493class ActivenessProbeStatus(enum.Enum): 

494 # no response yet 

495 pending = enum.auto() 

496 

497 # didn't respond on time 

498 expired = enum.auto() 

499 

500 # responded that they're still active 

501 still_active = enum.auto() 

502 

503 # responded that they're no longer active 

504 no_longer_active = enum.auto() 

505 

506 

507class ActivenessProbe(Base): 

508 """ 

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

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

511 show up as such. 

512 """ 

513 

514 __tablename__ = "activeness_probes" 

515 

516 id = Column(BigInteger, primary_key=True) 

517 

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

519 # the time this probe was initiated 

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

521 # the number of reminders sent for this probe 

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

523 

524 # the time of response 

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

526 # the response value 

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

528 

529 @hybrid_property 

530 def is_pending(self): 

531 return self.responded == None 

532 

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

534 

535 __table_args__ = ( 

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

537 Index( 

538 "ix_activeness_probe_unique_pending_response", 

539 user_id, 

540 unique=True, 

541 postgresql_where=responded == None, 

542 ), 

543 # response time is none iff response is pending 

544 CheckConstraint( 

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

546 name="pending_has_no_responded", 

547 ), 

548 ) 

549 

550 

551User.pending_activeness_probe = relationship( 

552 ActivenessProbe, 

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

554 uselist=False, 

555 back_populates="user", 

556) 

557 

558 

559class StrongVerificationAttemptStatus(enum.Enum): 

560 ## full data states 

561 # completed, this now provides verification for a user 

562 succeeded = enum.auto() 

563 

564 ## no data states 

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

566 in_progress_waiting_on_user_to_open_app = enum.auto() 

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

568 in_progress_waiting_on_user_in_app = enum.auto() 

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

570 in_progress_waiting_on_backend = enum.auto() 

571 # failed, no data 

572 failed = enum.auto() 

573 

574 # duplicate, at our end, has data 

575 duplicate = enum.auto() 

576 

577 ## minimal data states 

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

579 deleted = enum.auto() 

580 

581 

582class PassportSex(enum.Enum): 

583 """ 

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

585 """ 

586 

587 male = enum.auto() 

588 female = enum.auto() 

589 unspecified = enum.auto() 

590 

591 

592class StrongVerificationAttempt(Base): 

593 """ 

594 An attempt to perform strong verification 

595 """ 

596 

597 __tablename__ = "strong_verification_attempts" 

598 

599 # our verification id 

600 id = Column(BigInteger, primary_key=True) 

601 

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

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

604 

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

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

607 

608 status = Column( 

609 Enum(StrongVerificationAttemptStatus), 

610 nullable=False, 

611 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app, 

612 ) 

613 

614 ## full data 

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

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

617 passport_encrypted_data = Column(Binary, nullable=True) 

618 passport_date_of_birth = Column(Date, nullable=True) 

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

620 

621 ## minimal data: this will not be deleted 

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

623 passport_expiry_date = Column(Date, nullable=True) 

624 passport_nationality = Column(String, nullable=True) 

625 # last three characters of the passport number 

626 passport_last_three_document_chars = Column(String, nullable=True) 

627 

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

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

630 

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

632 

633 user = relationship("User") 

634 

635 @hybrid_property 

636 def is_valid(self): 

637 """ 

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

639 """ 

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

641 

642 @is_valid.expression 

643 def is_valid(cls): 

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

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

646 ) 

647 

648 @hybrid_property 

649 def is_visible(self): 

650 return self.status != StrongVerificationAttemptStatus.deleted 

651 

652 @hybrid_method 

653 def _raw_birthdate_match(self, user): 

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

655 return self.passport_date_of_birth == user.birthdate 

656 

657 @hybrid_method 

658 def matches_birthdate(self, user): 

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

660 

661 @hybrid_method 

662 def _raw_gender_match(self, user): 

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

664 return ( 

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

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

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

668 | (user.has_passport_sex_gender_exception == True) 

669 ) 

670 

671 @hybrid_method 

672 def matches_gender(self, user): 

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

674 

675 @hybrid_method 

676 def has_strong_verification(self, user): 

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

678 

679 __table_args__ = ( 

680 # used to look up verification status for a user 

681 Index( 

682 "ix_strong_verification_attempts_current", 

683 user_id, 

684 passport_expiry_date, 

685 postgresql_where=status == StrongVerificationAttemptStatus.succeeded, 

686 ), 

687 # each passport can be verified only once 

688 Index( 

689 "ix_strong_verification_attempts_unique_succeeded", 

690 passport_expiry_date, 

691 passport_nationality, 

692 passport_last_three_document_chars, 

693 unique=True, 

694 postgresql_where=( 

695 (status == StrongVerificationAttemptStatus.succeeded) 

696 | (status == StrongVerificationAttemptStatus.deleted) 

697 ), 

698 ), 

699 # full data check 

700 CheckConstraint( 

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

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

703 name="full_data_status", 

704 ), 

705 # minimal data check 

706 CheckConstraint( 

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

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

709 name="minimal_data_status", 

710 ), 

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

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

713 CheckConstraint( 

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

715 name="full_data_implies_minimal_data", 

716 ), 

717 # succeeded implies full data 

718 CheckConstraint( 

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

720 name="succeeded_implies_full_data", 

721 ), 

722 # in_progress/failed implies no_data 

723 CheckConstraint( 

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

725 name="in_progress_failed_iris_implies_no_data", 

726 ), 

727 # deleted or duplicate implies minimal data 

728 CheckConstraint( 

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

730 name="deleted_duplicate_implies_minimal_data", 

731 ), 

732 ) 

733 

734 

735class ModNote(Base): 

736 """ 

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

738 

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

740 """ 

741 

742 __tablename__ = "mod_notes" 

743 id = Column(BigInteger, primary_key=True) 

744 

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

746 

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

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

749 

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

751 internal_id = Column(String, nullable=False) 

752 # the admin that left this note 

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

754 

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

756 

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

758 

759 def __repr__(self): 

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

761 

762 @hybrid_property 

763 def is_pending(self): 

764 return self.acknowledged == None 

765 

766 __table_args__ = ( 

767 # used to look up pending notes 

768 Index( 

769 "ix_mod_notes_unacknowledged", 

770 user_id, 

771 postgresql_where=acknowledged == None, 

772 ), 

773 ) 

774 

775 

776class StrongVerificationCallbackEvent(Base): 

777 __tablename__ = "strong_verification_callback_events" 

778 

779 id = Column(BigInteger, primary_key=True) 

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

781 

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

783 

784 iris_status = Column(String, nullable=False) 

785 

786 

787class DonationType(enum.Enum): 

788 one_time = enum.auto() 

789 recurring = enum.auto() 

790 

791 

792class DonationInitiation(Base): 

793 """ 

794 Whenever someone initiaties a donation through the platform 

795 """ 

796 

797 __tablename__ = "donation_initiations" 

798 id = Column(BigInteger, primary_key=True) 

799 

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

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

802 

803 amount = Column(Integer, nullable=False) 

804 stripe_checkout_session_id = Column(String, nullable=False) 

805 

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

807 

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

809 

810 

811class Invoice(Base): 

812 """ 

813 Successful donations, both one off and recurring 

814 

815 Triggered by `payment_intent.succeeded` webhook 

816 """ 

817 

818 __tablename__ = "invoices" 

819 

820 id = Column(BigInteger, primary_key=True) 

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

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

823 

824 amount = Column(Float, nullable=False) 

825 

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

827 stripe_receipt_url = Column(String, nullable=False) 

828 

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

830 

831 

832class LanguageFluency(enum.Enum): 

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

834 beginner = 1 

835 conversational = 2 

836 fluent = 3 

837 

838 

839class LanguageAbility(Base): 

840 __tablename__ = "language_abilities" 

841 __table_args__ = ( 

842 # Users can only have one language ability per language 

843 UniqueConstraint("user_id", "language_code"), 

844 ) 

845 

846 id = Column(BigInteger, primary_key=True) 

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

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

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

850 

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

852 language = relationship("Language") 

853 

854 

855class RegionVisited(Base): 

856 __tablename__ = "regions_visited" 

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

858 

859 id = Column(BigInteger, primary_key=True) 

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

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

862 

863 

864class RegionLived(Base): 

865 __tablename__ = "regions_lived" 

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

867 

868 id = Column(BigInteger, primary_key=True) 

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

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

871 

872 

873class FriendStatus(enum.Enum): 

874 pending = enum.auto() 

875 accepted = enum.auto() 

876 rejected = enum.auto() 

877 cancelled = enum.auto() 

878 

879 

880class FriendRelationship(Base): 

881 """ 

882 Friendship relations between users 

883 

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

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

886 """ 

887 

888 __tablename__ = "friend_relationships" 

889 

890 id = Column(BigInteger, primary_key=True) 

891 

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

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

894 

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

896 

897 # timezones should always be UTC 

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

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

900 

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

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

903 

904 __table_args__ = ( 

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

906 Index( 

907 "ix_friend_relationships_status_to_from", 

908 status, 

909 to_user_id, 

910 from_user_id, 

911 ), 

912 ) 

913 

914 

915class ContributeOption(enum.Enum): 

916 yes = enum.auto() 

917 maybe = enum.auto() 

918 no = enum.auto() 

919 

920 

921class ContributorForm(Base): 

922 """ 

923 Someone filled in the contributor form 

924 """ 

925 

926 __tablename__ = "contributor_forms" 

927 

928 id = Column(BigInteger, primary_key=True) 

929 

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

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

932 

933 ideas = Column(String, nullable=True) 

934 features = Column(String, nullable=True) 

935 experience = Column(String, nullable=True) 

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

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

938 expertise = Column(String, nullable=True) 

939 

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

941 

942 @hybrid_property 

943 def is_filled(self): 

944 """ 

945 Whether the form counts as having been filled 

946 """ 

947 return ( 

948 (self.ideas != None) 

949 | (self.features != None) 

950 | (self.experience != None) 

951 | (self.contribute != None) 

952 | (self.contribute_ways != []) 

953 | (self.expertise != None) 

954 ) 

955 

956 @property 

957 def should_notify(self): 

958 """ 

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

960 

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

962 """ 

963 return False 

964 

965 

966class SignupFlow(Base): 

967 """ 

968 Signup flows/incomplete users 

969 

970 Coinciding fields have the same meaning as in User 

971 """ 

972 

973 __tablename__ = "signup_flows" 

974 

975 id = Column(BigInteger, primary_key=True) 

976 

977 # housekeeping 

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

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

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

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

982 email_token = Column(String, nullable=True) 

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

984 

985 ## Basic 

986 name = Column(String, nullable=False) 

987 # TODO: unique across both tables 

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

989 # TODO: invitation, attribution 

990 

991 ## Account 

992 # TODO: unique across both tables 

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

994 hashed_password = Column(Binary, nullable=True) 

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

996 gender = Column(String, nullable=True) 

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

998 city = Column(String, nullable=True) 

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

1000 geom_radius = Column(Float, nullable=True) 

1001 

1002 accepted_tos = Column(Integer, nullable=True) 

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

1004 

1005 opt_out_of_newsletter = Column(Boolean, nullable=True) 

1006 

1007 ## Feedback (now unused) 

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

1009 ideas = Column(String, nullable=True) 

1010 features = Column(String, nullable=True) 

1011 experience = Column(String, nullable=True) 

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

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

1014 expertise = Column(String, nullable=True) 

1015 

1016 @hybrid_property 

1017 def token_is_valid(self): 

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

1019 

1020 @hybrid_property 

1021 def account_is_filled(self): 

1022 return ( 

1023 (self.username != None) 

1024 & (self.birthdate != None) 

1025 & (self.gender != None) 

1026 & (self.hosting_status != None) 

1027 & (self.city != None) 

1028 & (self.geom != None) 

1029 & (self.geom_radius != None) 

1030 & (self.accepted_tos != None) 

1031 & (self.opt_out_of_newsletter != None) 

1032 ) 

1033 

1034 @hybrid_property 

1035 def is_completed(self): 

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

1037 

1038 

1039class LoginToken(Base): 

1040 """ 

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

1042 """ 

1043 

1044 __tablename__ = "login_tokens" 

1045 token = Column(String, primary_key=True) 

1046 

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

1048 

1049 # timezones should always be UTC 

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

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

1052 

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

1054 

1055 @hybrid_property 

1056 def is_valid(self): 

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

1058 

1059 def __repr__(self): 

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

1061 

1062 

1063class PasswordResetToken(Base): 

1064 __tablename__ = "password_reset_tokens" 

1065 token = Column(String, primary_key=True) 

1066 

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

1068 

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

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

1071 

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

1073 

1074 @hybrid_property 

1075 def is_valid(self): 

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

1077 

1078 def __repr__(self): 

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

1080 

1081 

1082class AccountDeletionToken(Base): 

1083 __tablename__ = "account_deletion_tokens" 

1084 

1085 token = Column(String, primary_key=True) 

1086 

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

1088 

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

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

1091 

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

1093 

1094 @hybrid_property 

1095 def is_valid(self): 

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

1097 

1098 def __repr__(self): 

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

1100 

1101 

1102class UserActivity(Base): 

1103 """ 

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

1105 

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

1107 """ 

1108 

1109 __tablename__ = "user_activity" 

1110 

1111 id = Column(BigInteger, primary_key=True) 

1112 

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

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

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

1116 

1117 # details of the browser, if available 

1118 ip_address = Column(INET, nullable=True) 

1119 user_agent = Column(String, nullable=True) 

1120 

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

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

1123 

1124 __table_args__ = ( 

1125 # helps look up this tuple quickly 

1126 Index( 

1127 "ix_user_activity_user_id_period_ip_address_user_agent", 

1128 user_id, 

1129 period, 

1130 ip_address, 

1131 user_agent, 

1132 unique=True, 

1133 ), 

1134 ) 

1135 

1136 

1137class UserSession(Base): 

1138 """ 

1139 API keys/session cookies for the app 

1140 

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

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

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

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

1145 site. 

1146 

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

1148 

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

1150 """ 

1151 

1152 __tablename__ = "sessions" 

1153 token = Column(String, primary_key=True) 

1154 

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

1156 

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

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

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

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

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

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

1163 

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

1165 long_lived = Column(Boolean, nullable=False) 

1166 

1167 # the time at which the session was created 

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

1169 

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

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

1172 

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

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

1175 

1176 # the last time this session was used 

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

1178 

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

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

1181 

1182 # details of the browser, if available 

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

1184 ip_address = Column(String, nullable=True) 

1185 user_agent = Column(String, nullable=True) 

1186 

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

1188 

1189 @hybrid_property 

1190 def is_valid(self): 

1191 """ 

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

1193 

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

1195 

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

1197 """ 

1198 return ( 

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

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

1201 & (self.deleted == None) 

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

1203 ) 

1204 

1205 __table_args__ = ( 

1206 Index( 

1207 "ix_sessions_by_token", 

1208 "token", 

1209 postgresql_using="hash", 

1210 ), 

1211 ) 

1212 

1213 

1214class Conversation(Base): 

1215 """ 

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

1217 """ 

1218 

1219 __tablename__ = "conversations" 

1220 

1221 id = Column(BigInteger, primary_key=True) 

1222 # timezone should always be UTC 

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

1224 

1225 def __repr__(self): 

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

1227 

1228 

1229class GroupChat(Base): 

1230 """ 

1231 Group chat 

1232 """ 

1233 

1234 __tablename__ = "group_chats" 

1235 

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

1237 

1238 title = Column(String, nullable=True) 

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

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

1241 is_dm = Column(Boolean, nullable=False) 

1242 

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

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

1245 

1246 def __repr__(self): 

1247 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})" 

1248 

1249 

1250class GroupChatRole(enum.Enum): 

1251 admin = enum.auto() 

1252 participant = enum.auto() 

1253 

1254 

1255class GroupChatSubscription(Base): 

1256 """ 

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

1258 """ 

1259 

1260 __tablename__ = "group_chat_subscriptions" 

1261 id = Column(BigInteger, primary_key=True) 

1262 

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

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

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

1266 

1267 # timezones should always be UTC 

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

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

1270 

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

1272 

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

1274 

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

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

1277 

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

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

1280 

1281 def muted_display(self): 

1282 """ 

1283 Returns (muted, muted_until) display values: 

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

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

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

1287 """ 

1288 if self.muted_until < now(): 

1289 return (False, None) 

1290 elif self.muted_until == DATETIME_INFINITY: 

1291 return (True, None) 

1292 else: 

1293 return (True, self.muted_until) 

1294 

1295 @hybrid_property 

1296 def is_muted(self): 

1297 return self.muted_until > func.now() 

1298 

1299 def __repr__(self): 

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

1301 

1302 

1303class MessageType(enum.Enum): 

1304 text = enum.auto() 

1305 # e.g. 

1306 # image = 

1307 # emoji = 

1308 # ... 

1309 chat_created = enum.auto() 

1310 chat_edited = enum.auto() 

1311 user_invited = enum.auto() 

1312 user_left = enum.auto() 

1313 user_made_admin = enum.auto() 

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

1315 host_request_status_changed = enum.auto() 

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

1317 

1318 

1319class HostRequestStatus(enum.Enum): 

1320 pending = enum.auto() 

1321 accepted = enum.auto() 

1322 rejected = enum.auto() 

1323 confirmed = enum.auto() 

1324 cancelled = enum.auto() 

1325 

1326 

1327class Message(Base): 

1328 """ 

1329 A message. 

1330 

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

1332 """ 

1333 

1334 __tablename__ = "messages" 

1335 

1336 id = Column(BigInteger, primary_key=True) 

1337 

1338 # which conversation the message belongs in 

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

1340 

1341 # the user that sent the message/command 

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

1343 

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

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

1346 

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

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

1349 

1350 # time sent, timezone should always be UTC 

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

1352 

1353 # the plain-text message text if not control 

1354 text = Column(String, nullable=True) 

1355 

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

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

1358 

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

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

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

1362 

1363 @property 

1364 def is_normal_message(self): 

1365 """ 

1366 There's only one normal type atm, text 

1367 """ 

1368 return self.message_type == MessageType.text 

1369 

1370 def __repr__(self): 

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

1372 

1373 

1374class ContentReport(Base): 

1375 """ 

1376 A piece of content reported to admins 

1377 """ 

1378 

1379 __tablename__ = "content_reports" 

1380 

1381 id = Column(BigInteger, primary_key=True) 

1382 

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

1384 

1385 # the user who reported or flagged the content 

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

1387 

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

1389 reason = Column(String, nullable=False) 

1390 # a short description 

1391 description = Column(String, nullable=False) 

1392 

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

1394 content_ref = Column(String, nullable=False) 

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

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

1397 

1398 # details of the browser, if available 

1399 user_agent = Column(String, nullable=False) 

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

1401 page = Column(String, nullable=False) 

1402 

1403 # see comments above for reporting vs author 

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

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

1406 

1407 

1408class Email(Base): 

1409 """ 

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

1411 """ 

1412 

1413 __tablename__ = "emails" 

1414 

1415 id = Column(String, primary_key=True) 

1416 

1417 # timezone should always be UTC 

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

1419 

1420 sender_name = Column(String, nullable=False) 

1421 sender_email = Column(String, nullable=False) 

1422 

1423 recipient = Column(String, nullable=False) 

1424 subject = Column(String, nullable=False) 

1425 

1426 plain = Column(String, nullable=False) 

1427 html = Column(String, nullable=False) 

1428 

1429 list_unsubscribe_header = Column(String, nullable=True) 

1430 source_data = Column(String, nullable=True) 

1431 

1432 

1433class SMS(Base): 

1434 """ 

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

1436 """ 

1437 

1438 __tablename__ = "smss" 

1439 

1440 id = Column(BigInteger, primary_key=True) 

1441 

1442 # timezone should always be UTC 

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

1444 # AWS message id 

1445 message_id = Column(String, nullable=False) 

1446 

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

1448 sms_sender_id = Column(String, nullable=False) 

1449 number = Column(String, nullable=False) 

1450 message = Column(String, nullable=False) 

1451 

1452 

1453class HostRequest(Base): 

1454 """ 

1455 A request to stay with a host 

1456 """ 

1457 

1458 __tablename__ = "host_requests" 

1459 

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

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

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

1463 

1464 # TODO: proper timezone handling 

1465 timezone = "Etc/UTC" 

1466 

1467 # dates in the timezone above 

1468 from_date = Column(Date, nullable=False) 

1469 to_date = Column(Date, nullable=False) 

1470 

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

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

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

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

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

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

1477 

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

1479 is_host_archived = Column(Boolean, nullable=False, default=False, server_default=text("false")) 

1480 is_surfer_archived = Column(Boolean, nullable=False, default=False, server_default=text("false")) 

1481 

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

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

1484 

1485 # number of reference reminders sent out 

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

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

1488 host_sent_request_reminders = Column(BigInteger, nullable=False, server_default=text("0")) 

1489 last_sent_request_reminder_time = Column(DateTime, nullable=False, server_default=func.now()) 

1490 

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

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

1493 host_reason_didnt_meetup = Column(String, nullable=True) 

1494 surfer_reason_didnt_meetup = Column(String, nullable=True) 

1495 

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

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

1498 conversation = relationship("Conversation") 

1499 

1500 __table_args__ = ( 

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

1502 Index( 

1503 "ix_host_requests_host_didnt_meetup", 

1504 host_reason_didnt_meetup != None, 

1505 ), 

1506 Index( 

1507 "ix_host_requests_surfer_didnt_meetup", 

1508 surfer_reason_didnt_meetup != None, 

1509 ), 

1510 # Used for figuring out who needs a reminder to respond 

1511 Index( 

1512 "ix_host_requests_status_reminder_counts", 

1513 status, 

1514 host_sent_request_reminders, 

1515 last_sent_request_reminder_time, 

1516 from_date, 

1517 ), 

1518 ) 

1519 

1520 @hybrid_property 

1521 def can_write_reference(self): 

1522 return ( 

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

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

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

1526 ) 

1527 

1528 @can_write_reference.expression 

1529 def can_write_reference(cls): 

1530 return ( 

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

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

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

1534 ) 

1535 

1536 def __repr__(self): 

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

1538 

1539 

1540class ReferenceType(enum.Enum): 

1541 friend = enum.auto() 

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

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

1544 

1545 

1546class Reference(Base): 

1547 """ 

1548 Reference from one user to another 

1549 """ 

1550 

1551 __tablename__ = "references" 

1552 

1553 id = Column(BigInteger, primary_key=True) 

1554 # timezone should always be UTC 

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

1556 

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

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

1559 

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

1561 

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

1563 

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

1565 # text that's only visible to mods 

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

1567 

1568 rating = Column(Float, nullable=False) 

1569 was_appropriate = Column(Boolean, nullable=False) 

1570 

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

1572 

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

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

1575 

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

1577 

1578 __table_args__ = ( 

1579 # Rating must be between 0 and 1, inclusive 

1580 CheckConstraint( 

1581 "rating BETWEEN 0 AND 1", 

1582 name="rating_between_0_and_1", 

1583 ), 

1584 # Has host_request_id or it's a friend reference 

1585 CheckConstraint( 

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

1587 name="host_request_id_xor_friend_reference", 

1588 ), 

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

1590 Index( 

1591 "ix_references_unique_friend_reference", 

1592 from_user_id, 

1593 to_user_id, 

1594 reference_type, 

1595 unique=True, 

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

1597 ), 

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

1599 Index( 

1600 "ix_references_unique_per_host_request", 

1601 from_user_id, 

1602 to_user_id, 

1603 host_request_id, 

1604 unique=True, 

1605 postgresql_where=(host_request_id != None), 

1606 ), 

1607 ) 

1608 

1609 @property 

1610 def should_report(self): 

1611 """ 

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

1613 """ 

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

1615 

1616 

1617class InitiatedUpload(Base): 

1618 """ 

1619 Started downloads, not necessarily complete yet. 

1620 """ 

1621 

1622 __tablename__ = "initiated_uploads" 

1623 

1624 key = Column(String, primary_key=True) 

1625 

1626 # timezones should always be UTC 

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

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

1629 

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

1631 

1632 initiator_user = relationship("User") 

1633 

1634 @hybrid_property 

1635 def is_valid(self): 

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

1637 

1638 

1639class Upload(Base): 

1640 """ 

1641 Completed uploads. 

1642 """ 

1643 

1644 __tablename__ = "uploads" 

1645 key = Column(String, primary_key=True) 

1646 

1647 filename = Column(String, nullable=False) 

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

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

1650 

1651 # photo credit, etc 

1652 credit = Column(String, nullable=True) 

1653 

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

1655 

1656 def _url(self, size): 

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

1658 

1659 @property 

1660 def thumbnail_url(self): 

1661 return self._url("thumbnail") 

1662 

1663 @property 

1664 def full_url(self): 

1665 return self._url("full") 

1666 

1667 

1668communities_seq = Sequence("communities_seq") 

1669 

1670 

1671class Node(Base): 

1672 """ 

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

1674 

1675 Administered by the official cluster 

1676 """ 

1677 

1678 __tablename__ = "nodes" 

1679 

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

1681 

1682 # name and description come from official cluster 

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

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

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

1686 

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

1688 

1689 contained_users = relationship( 

1690 "User", 

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

1692 viewonly=True, 

1693 uselist=True, 

1694 ) 

1695 

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

1697 

1698 

1699class Cluster(Base): 

1700 """ 

1701 Cluster, administered grouping of content 

1702 """ 

1703 

1704 __tablename__ = "clusters" 

1705 

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

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

1708 name = Column(String, nullable=False) 

1709 # short description 

1710 description = Column(String, nullable=False) 

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

1712 

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

1714 

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

1716 

1717 official_cluster_for_node = relationship( 

1718 "Node", 

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

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

1721 uselist=False, 

1722 viewonly=True, 

1723 ) 

1724 

1725 parent_node = relationship( 

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

1727 ) 

1728 

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

1730 # all pages 

1731 pages = relationship( 

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

1733 ) 

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

1735 discussions = relationship( 

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

1737 ) 

1738 

1739 # includes also admins 

1740 members = relationship( 

1741 "User", 

1742 lazy="dynamic", 

1743 backref="cluster_memberships", 

1744 secondary="cluster_subscriptions", 

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

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

1747 viewonly=True, 

1748 ) 

1749 

1750 admins = relationship( 

1751 "User", 

1752 lazy="dynamic", 

1753 backref="cluster_adminships", 

1754 secondary="cluster_subscriptions", 

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

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

1757 viewonly=True, 

1758 ) 

1759 

1760 main_page = relationship( 

1761 "Page", 

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

1763 viewonly=True, 

1764 uselist=False, 

1765 ) 

1766 

1767 @property 

1768 def is_leaf(self) -> bool: 

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

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

1771 

1772 __table_args__ = ( 

1773 # Each node can have at most one official cluster 

1774 Index( 

1775 "ix_clusters_owner_parent_node_id_is_official_cluster", 

1776 parent_node_id, 

1777 is_official_cluster, 

1778 unique=True, 

1779 postgresql_where=is_official_cluster, 

1780 ), 

1781 ) 

1782 

1783 

1784class NodeClusterAssociation(Base): 

1785 """ 

1786 NodeClusterAssociation, grouping of nodes 

1787 """ 

1788 

1789 __tablename__ = "node_cluster_associations" 

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

1791 

1792 id = Column(BigInteger, primary_key=True) 

1793 

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

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

1796 

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

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

1799 

1800 

1801class ClusterRole(enum.Enum): 

1802 member = enum.auto() 

1803 admin = enum.auto() 

1804 

1805 

1806class ClusterSubscription(Base): 

1807 """ 

1808 ClusterSubscription of a user 

1809 """ 

1810 

1811 __tablename__ = "cluster_subscriptions" 

1812 

1813 id = Column(BigInteger, primary_key=True) 

1814 

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

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

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

1818 

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

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

1821 

1822 __table_args__ = ( 

1823 UniqueConstraint("user_id", "cluster_id"), 

1824 Index( 

1825 "ix_cluster_subscriptions_members", 

1826 cluster_id, 

1827 user_id, 

1828 ), 

1829 # For fast lookup of nodes this user is an admin of 

1830 Index( 

1831 "ix_cluster_subscriptions_admins", 

1832 user_id, 

1833 cluster_id, 

1834 postgresql_where=(role == ClusterRole.admin), 

1835 ), 

1836 ) 

1837 

1838 

1839class ClusterPageAssociation(Base): 

1840 """ 

1841 pages related to clusters 

1842 """ 

1843 

1844 __tablename__ = "cluster_page_associations" 

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

1846 

1847 id = Column(BigInteger, primary_key=True) 

1848 

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

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

1851 

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

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

1854 

1855 

1856class PageType(enum.Enum): 

1857 main_page = enum.auto() 

1858 place = enum.auto() 

1859 guide = enum.auto() 

1860 

1861 

1862class Page(Base): 

1863 """ 

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

1865 """ 

1866 

1867 __tablename__ = "pages" 

1868 

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

1870 

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

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

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

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

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

1876 

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

1878 

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

1880 

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

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

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

1884 owner_cluster = relationship( 

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

1886 ) 

1887 

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

1889 

1890 __table_args__ = ( 

1891 # Only one of owner_user and owner_cluster should be set 

1892 CheckConstraint( 

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

1894 name="one_owner", 

1895 ), 

1896 # Only clusters can own main pages 

1897 CheckConstraint( 

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

1899 name="main_page_owned_by_cluster", 

1900 ), 

1901 # Each cluster can have at most one main page 

1902 Index( 

1903 "ix_pages_owner_cluster_id_type", 

1904 owner_cluster_id, 

1905 type, 

1906 unique=True, 

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

1908 ), 

1909 ) 

1910 

1911 def __repr__(self): 

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

1913 

1914 

1915class PageVersion(Base): 

1916 """ 

1917 version of page content 

1918 """ 

1919 

1920 __tablename__ = "page_versions" 

1921 

1922 id = Column(BigInteger, primary_key=True) 

1923 

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

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

1926 title = Column(String, nullable=False) 

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

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

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

1930 # the human-readable address 

1931 address = Column(String, nullable=True) 

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

1933 

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

1935 

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

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

1938 photo = relationship("Upload") 

1939 

1940 __table_args__ = ( 

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

1942 CheckConstraint( 

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

1944 name="geom_iff_address", 

1945 ), 

1946 ) 

1947 

1948 @property 

1949 def coordinates(self): 

1950 # returns (lat, lng) or None 

1951 return get_coordinates(self.geom) 

1952 

1953 def __repr__(self): 

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

1955 

1956 

1957class ClusterEventAssociation(Base): 

1958 """ 

1959 events related to clusters 

1960 """ 

1961 

1962 __tablename__ = "cluster_event_associations" 

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

1964 

1965 id = Column(BigInteger, primary_key=True) 

1966 

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

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

1969 

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

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

1972 

1973 

1974class Event(Base): 

1975 """ 

1976 An event is compose of two parts: 

1977 

1978 * An event template (Event) 

1979 * An occurrence (EventOccurrence) 

1980 

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

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

1983 """ 

1984 

1985 __tablename__ = "events" 

1986 

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

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

1989 

1990 title = Column(String, nullable=False) 

1991 

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

1993 

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

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

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

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

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

1999 

2000 parent_node = relationship( 

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

2002 ) 

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

2004 subscribers = relationship( 

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

2006 ) 

2007 organizers = relationship( 

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

2009 ) 

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

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

2012 owner_cluster = relationship( 

2013 "Cluster", 

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

2015 uselist=False, 

2016 foreign_keys="Event.owner_cluster_id", 

2017 ) 

2018 

2019 __table_args__ = ( 

2020 # Only one of owner_user and owner_cluster should be set 

2021 CheckConstraint( 

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

2023 name="one_owner", 

2024 ), 

2025 ) 

2026 

2027 

2028class EventOccurrence(Base): 

2029 __tablename__ = "event_occurrences" 

2030 

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

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

2033 

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

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

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

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

2038 

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

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

2041 

2042 # a null geom is an online-only event 

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

2044 # physical address, iff geom is not null 

2045 address = Column(String, nullable=True) 

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

2047 link = Column(String, nullable=True) 

2048 

2049 timezone = "Etc/UTC" 

2050 

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

2052 # simplifies database constraints, etc 

2053 during = Column(TSTZRANGE, nullable=False) 

2054 

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

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

2057 

2058 creator_user = relationship( 

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

2060 ) 

2061 event = relationship( 

2062 "Event", 

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

2064 remote_side="Event.id", 

2065 foreign_keys="EventOccurrence.event_id", 

2066 ) 

2067 

2068 photo = relationship("Upload") 

2069 

2070 __table_args__ = ( 

2071 # Geom and address go together 

2072 CheckConstraint( 

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

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

2075 name="geom_iff_address", 

2076 ), 

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

2078 CheckConstraint( 

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

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

2081 name="link_or_geom", 

2082 ), 

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

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

2085 ) 

2086 

2087 @property 

2088 def coordinates(self): 

2089 # returns (lat, lng) or None 

2090 return get_coordinates(self.geom) 

2091 

2092 @hybrid_property 

2093 def start_time(self): 

2094 return self.during.lower 

2095 

2096 @start_time.expression 

2097 def start_time(cls): 

2098 return func.lower(cls.during) 

2099 

2100 @hybrid_property 

2101 def end_time(self): 

2102 return self.during.upper 

2103 

2104 @end_time.expression 

2105 def end_time(cls): 

2106 return func.upper(cls.during) 

2107 

2108 

2109class EventSubscription(Base): 

2110 """ 

2111 Users' subscriptions to events 

2112 """ 

2113 

2114 __tablename__ = "event_subscriptions" 

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

2116 

2117 id = Column(BigInteger, primary_key=True) 

2118 

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

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

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

2122 

2123 user = relationship("User") 

2124 event = relationship("Event") 

2125 

2126 

2127class EventOrganizer(Base): 

2128 """ 

2129 Organizers for events 

2130 """ 

2131 

2132 __tablename__ = "event_organizers" 

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

2134 

2135 id = Column(BigInteger, primary_key=True) 

2136 

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

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

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

2140 

2141 user = relationship("User") 

2142 event = relationship("Event") 

2143 

2144 

2145class AttendeeStatus(enum.Enum): 

2146 going = enum.auto() 

2147 maybe = enum.auto() 

2148 

2149 

2150class EventOccurrenceAttendee(Base): 

2151 """ 

2152 Attendees for events 

2153 """ 

2154 

2155 __tablename__ = "event_occurrence_attendees" 

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

2157 

2158 id = Column(BigInteger, primary_key=True) 

2159 

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

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

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

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

2164 

2165 user = relationship("User") 

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

2167 

2168 

2169class EventCommunityInviteRequest(Base): 

2170 """ 

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

2172 """ 

2173 

2174 __tablename__ = "event_community_invite_requests" 

2175 

2176 id = Column(BigInteger, primary_key=True) 

2177 

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

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

2180 

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

2182 

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

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

2185 approved = Column(Boolean, nullable=True) 

2186 

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

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

2189 

2190 __table_args__ = ( 

2191 # each user can only request once 

2192 UniqueConstraint("occurrence_id", "user_id"), 

2193 # each event can only have one notification sent out 

2194 Index( 

2195 "ix_event_community_invite_requests_unique", 

2196 occurrence_id, 

2197 unique=True, 

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

2199 ), 

2200 # decided and approved ought to be null simultaneously 

2201 CheckConstraint( 

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

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

2204 name="decided_approved", 

2205 ), 

2206 ) 

2207 

2208 

2209class ClusterDiscussionAssociation(Base): 

2210 """ 

2211 discussions related to clusters 

2212 """ 

2213 

2214 __tablename__ = "cluster_discussion_associations" 

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

2216 

2217 id = Column(BigInteger, primary_key=True) 

2218 

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

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

2221 

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

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

2224 

2225 

2226class Discussion(Base): 

2227 """ 

2228 forum board 

2229 """ 

2230 

2231 __tablename__ = "discussions" 

2232 

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

2234 

2235 title = Column(String, nullable=False) 

2236 content = Column(String, nullable=False) 

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

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

2239 

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

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

2242 

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

2244 

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

2246 

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

2248 

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

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

2251 

2252 

2253class DiscussionSubscription(Base): 

2254 """ 

2255 users subscriptions to discussions 

2256 """ 

2257 

2258 __tablename__ = "discussion_subscriptions" 

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

2260 

2261 id = Column(BigInteger, primary_key=True) 

2262 

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

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

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

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

2267 

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

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

2270 

2271 

2272class Thread(Base): 

2273 """ 

2274 Thread 

2275 """ 

2276 

2277 __tablename__ = "threads" 

2278 

2279 id = Column(BigInteger, primary_key=True) 

2280 

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

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

2283 

2284 

2285class Comment(Base): 

2286 """ 

2287 Comment 

2288 """ 

2289 

2290 __tablename__ = "comments" 

2291 

2292 id = Column(BigInteger, primary_key=True) 

2293 

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

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

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

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

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

2299 

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

2301 

2302 

2303class Reply(Base): 

2304 """ 

2305 Reply 

2306 """ 

2307 

2308 __tablename__ = "replies" 

2309 

2310 id = Column(BigInteger, primary_key=True) 

2311 

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

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

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

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

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

2317 

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

2319 

2320 

2321class BackgroundJobState(enum.Enum): 

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

2323 pending = enum.auto() 

2324 # job complete 

2325 completed = enum.auto() 

2326 # error occured, will be retried 

2327 error = enum.auto() 

2328 # failed too many times, not retrying anymore 

2329 failed = enum.auto() 

2330 

2331 

2332class BackgroundJob(Base): 

2333 """ 

2334 This table implements a queue of background jobs. 

2335 """ 

2336 

2337 __tablename__ = "background_jobs" 

2338 

2339 id = Column(BigInteger, primary_key=True) 

2340 

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

2342 job_type = Column(String, nullable=False) 

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

2344 

2345 # time queued 

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

2347 

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

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

2350 

2351 # used to count number of retries for failed jobs 

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

2353 

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

2355 

2356 # higher is more important 

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

2358 

2359 # protobuf encoded job payload 

2360 payload = Column(Binary, nullable=False) 

2361 

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

2363 failure_info = Column(String, nullable=True) 

2364 

2365 __table_args__ = ( 

2366 # used in looking up background jobs to attempt 

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

2368 Index( 

2369 "ix_background_jobs_lookup", 

2370 priority.desc(), 

2371 next_attempt_after, 

2372 (max_tries - try_count), 

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

2374 ), 

2375 ) 

2376 

2377 @hybrid_property 

2378 def ready_for_retry(self): 

2379 return ( 

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

2381 & (self.try_count < self.max_tries) 

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

2383 ) 

2384 

2385 def __repr__(self): 

2386 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})" 

2387 

2388 

2389class NotificationDeliveryType(enum.Enum): 

2390 # send push notification to mobile/web 

2391 push = enum.auto() 

2392 # send individual email immediately 

2393 email = enum.auto() 

2394 # send in digest 

2395 digest = enum.auto() 

2396 

2397 

2398dt = NotificationDeliveryType 

2399nd = notification_data_pb2 

2400 

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

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

2403 

2404 

2405class NotificationTopicAction(enum.Enum): 

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

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

2408 self.defaults = defaults 

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

2410 self.user_editable = user_editable 

2411 

2412 self.data_type = data_type 

2413 

2414 def unpack(self): 

2415 return self.topic, self.action 

2416 

2417 @property 

2418 def display(self): 

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

2420 

2421 def __str__(self): 

2422 return self.display 

2423 

2424 # topic, action, default delivery types 

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

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

2427 

2428 # host requests 

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

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

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

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

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

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

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

2436 host_request__reminder = ("host_request:reminder", dt_all, True, nd.HostRequestReminder) 

2437 

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

2439 

2440 # you receive a friend ref 

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

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

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

2444 # ... the surfer 

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

2446 

2447 # you hosted 

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

2449 # you surfed 

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

2451 

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

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

2454 

2455 # group chats 

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

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

2458 

2459 # events 

2460 # approved by mods 

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

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

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

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

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

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

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

2468 # toplevel comment on an event 

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

2470 

2471 # discussion created 

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

2473 # someone comments on your discussion 

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

2475 

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

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

2478 

2479 # account settings 

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

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

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

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

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

2485 # reset password 

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

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

2488 

2489 # account deletion 

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

2491 # no more pushing to do 

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

2493 # undeleted 

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

2495 

2496 # admin actions 

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

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

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

2500 

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

2502 

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

2504 

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

2506 

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

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

2509 

2510 # general announcements 

2511 general__new_blog_post = ("general:new_blog_post", [dt.push, dt.digest], True, nd.GeneralNewBlogPost) 

2512 

2513 

2514class NotificationPreference(Base): 

2515 __tablename__ = "notification_preferences" 

2516 

2517 id = Column(BigInteger, primary_key=True) 

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

2519 

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

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

2522 deliver = Column(Boolean, nullable=False) 

2523 

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

2525 

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

2527 

2528 

2529class Notification(Base): 

2530 """ 

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

2532 """ 

2533 

2534 __tablename__ = "notifications" 

2535 

2536 id = Column(BigInteger, primary_key=True) 

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

2538 

2539 # recipient user id 

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

2541 

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

2543 key = Column(String, nullable=False) 

2544 

2545 data = Column(Binary, nullable=False) 

2546 

2547 # whether the user has marked this notification as seen or not 

2548 is_seen = Column(Boolean, nullable=False, server_default=text("false")) 

2549 

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

2551 

2552 __table_args__ = ( 

2553 # used in looking up which notifications need delivery 

2554 Index( 

2555 "ix_notifications_created", 

2556 created, 

2557 ), 

2558 # Fast lookup for unseen notification count 

2559 Index( 

2560 "ix_notifications_unseen", 

2561 user_id, 

2562 topic_action, 

2563 postgresql_where=(is_seen == False), 

2564 ), 

2565 # Fast lookup for latest notifications 

2566 Index( 

2567 "ix_notifications_latest", 

2568 user_id, 

2569 id.desc(), 

2570 topic_action, 

2571 ), 

2572 ) 

2573 

2574 @property 

2575 def topic(self): 

2576 return self.topic_action.topic 

2577 

2578 @property 

2579 def action(self): 

2580 return self.topic_action.action 

2581 

2582 

2583class NotificationDelivery(Base): 

2584 __tablename__ = "notification_deliveries" 

2585 

2586 id = Column(BigInteger, primary_key=True) 

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

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

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

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

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

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

2593 # todo: device id 

2594 # todo: receipt id, etc 

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

2596 

2597 __table_args__ = ( 

2598 UniqueConstraint("notification_id", "delivery_type"), 

2599 # used in looking up which notifications need delivery 

2600 Index( 

2601 "ix_notification_deliveries_delivery_type", 

2602 delivery_type, 

2603 postgresql_where=(delivered != None), 

2604 ), 

2605 Index( 

2606 "ix_notification_deliveries_dt_ni_dnull", 

2607 delivery_type, 

2608 notification_id, 

2609 delivered == None, 

2610 ), 

2611 ) 

2612 

2613 

2614class PushNotificationSubscription(Base): 

2615 __tablename__ = "push_notification_subscriptions" 

2616 

2617 id = Column(BigInteger, primary_key=True) 

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

2619 

2620 # which user this is connected to 

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

2622 

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

2624 # the endpoint 

2625 endpoint = Column(String, nullable=False) 

2626 # the "auth" key 

2627 auth_key = Column(Binary, nullable=False) 

2628 # the "p256dh" key 

2629 p256dh_key = Column(Binary, nullable=False) 

2630 

2631 full_subscription_info = Column(String, nullable=False) 

2632 

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

2634 user_agent = Column(String, nullable=True) 

2635 

2636 # when it was disabled 

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

2638 

2639 user = relationship("User") 

2640 

2641 

2642class PushNotificationDeliveryAttempt(Base): 

2643 __tablename__ = "push_notification_delivery_attempt" 

2644 

2645 id = Column(BigInteger, primary_key=True) 

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

2647 

2648 push_notification_subscription_id = Column( 

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

2650 ) 

2651 

2652 success = Column(Boolean, nullable=False) 

2653 # the HTTP status code, 201 is success 

2654 status_code = Column(Integer, nullable=False) 

2655 

2656 # can be null if it was a success 

2657 response = Column(String, nullable=True) 

2658 

2659 push_notification_subscription = relationship("PushNotificationSubscription") 

2660 

2661 

2662class Language(Base): 

2663 """ 

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

2665 """ 

2666 

2667 __tablename__ = "languages" 

2668 

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

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

2671 

2672 # the english name 

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

2674 

2675 

2676class Region(Base): 

2677 """ 

2678 Table of regions 

2679 """ 

2680 

2681 __tablename__ = "regions" 

2682 

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

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

2685 

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

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

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

2689 

2690 

2691class UserBlock(Base): 

2692 """ 

2693 Table of blocked users 

2694 """ 

2695 

2696 __tablename__ = "user_blocks" 

2697 

2698 id = Column(BigInteger, primary_key=True) 

2699 

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

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

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

2703 

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

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

2706 

2707 __table_args__ = ( 

2708 UniqueConstraint("blocking_user_id", "blocked_user_id"), 

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

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

2711 ) 

2712 

2713 

2714class APICall(Base): 

2715 """ 

2716 API call logs 

2717 """ 

2718 

2719 __tablename__ = "api_calls" 

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

2721 

2722 id = Column(BigInteger, primary_key=True) 

2723 

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

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

2726 

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

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

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

2730 

2731 # approximate time of the call 

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

2733 

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

2735 method = Column(String, nullable=False) 

2736 

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

2738 status_code = Column(String, nullable=True) 

2739 

2740 # handler duration (excluding serialization, etc) 

2741 duration = Column(Float, nullable=False) 

2742 

2743 # user_id of caller, None means not logged in 

2744 user_id = Column(BigInteger, nullable=True) 

2745 

2746 # sanitized request bytes 

2747 request = Column(Binary, nullable=True) 

2748 

2749 # sanitized response bytes 

2750 response = Column(Binary, nullable=True) 

2751 

2752 # whether response bytes have been truncated 

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

2754 

2755 # the exception traceback, if any 

2756 traceback = Column(String, nullable=True) 

2757 

2758 # human readable perf report 

2759 perf_report = Column(String, nullable=True) 

2760 

2761 # details of the browser, if available 

2762 ip_address = Column(String, nullable=True) 

2763 user_agent = Column(String, nullable=True) 

2764 

2765 

2766class AccountDeletionReason(Base): 

2767 __tablename__ = "account_deletion_reason" 

2768 

2769 id = Column(BigInteger, primary_key=True) 

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

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

2772 reason = Column(String, nullable=True) 

2773 

2774 user = relationship("User") 

2775 

2776 

2777class AntiBotLog(Base): 

2778 __tablename__ = "antibot_logs" 

2779 

2780 id = Column(BigInteger, primary_key=True) 

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

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

2783 

2784 ip_address = Column(String, nullable=True) 

2785 user_agent = Column(String, nullable=True) 

2786 

2787 action = Column(String, nullable=False) 

2788 token = Column(String, nullable=False) 

2789 

2790 score = Column(Float, nullable=False) 

2791 provider_data = Column(JSON, nullable=False) 

2792 

2793 

2794class RateLimitAction(enum.Enum): 

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

2796 

2797 host_request = "host request" 

2798 friend_request = "friend request" 

2799 chat_initiation = "chat initiation" 

2800 

2801 

2802class RateLimitViolation(Base): 

2803 __tablename__ = "rate_limit_violations" 

2804 

2805 id = Column(BigInteger, primary_key=True) 

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

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

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

2809 is_hard_limit = Column(Boolean, nullable=False) 

2810 

2811 user = relationship("User") 

2812 

2813 __table_args__ = ( 

2814 # Fast lookup for rate limits in interval 

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

2816 )