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

Shortcuts on this page

r m x   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

953 statements  

1import enum 

2from calendar import monthrange 

3from datetime import date 

4 

5from geoalchemy2.types import Geometry 

6from sqlalchemy import ( 

7 ARRAY, 

8 BigInteger, 

9 Boolean, 

10 CheckConstraint, 

11 Column, 

12 Date, 

13 DateTime, 

14 Enum, 

15 Float, 

16 ForeignKey, 

17 Index, 

18 Integer, 

19) 

20from sqlalchemy import LargeBinary as Binary 

21from sqlalchemy import MetaData, Sequence, String, UniqueConstraint 

22from sqlalchemy.dialects.postgresql import TSTZRANGE, ExcludeConstraint 

23from sqlalchemy.ext.associationproxy import association_proxy 

24from sqlalchemy.ext.hybrid import hybrid_property 

25from sqlalchemy.orm import backref, column_property, declarative_base, relationship 

26from sqlalchemy.sql import func 

27from sqlalchemy.sql import select as sa_select 

28from sqlalchemy.sql import text 

29 

30from couchers.config import config 

31from couchers.constants import ( 

32 DATETIME_INFINITY, 

33 DATETIME_MINUS_INFINITY, 

34 EMAIL_REGEX, 

35 GUIDELINES_VERSION, 

36 PHONE_VERIFICATION_LIFETIME, 

37 SMS_CODE_LIFETIME, 

38 TOS_VERSION, 

39) 

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

41 

42meta = MetaData( 

43 naming_convention={ 

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

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

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

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

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

49 } 

50) 

51 

52Base = declarative_base(metadata=meta) 

53 

54 

55class HostingStatus(enum.Enum): 

56 can_host = enum.auto() 

57 maybe = enum.auto() 

58 cant_host = enum.auto() 

59 

60 

61class MeetupStatus(enum.Enum): 

62 wants_to_meetup = enum.auto() 

63 open_to_meetup = enum.auto() 

64 does_not_want_to_meetup = enum.auto() 

65 

66 

67class SmokingLocation(enum.Enum): 

68 yes = enum.auto() 

69 window = enum.auto() 

70 outside = enum.auto() 

71 no = enum.auto() 

72 

73 

74class SleepingArrangement(enum.Enum): 

75 private = enum.auto() 

76 common = enum.auto() 

77 shared_room = enum.auto() 

78 shared_space = enum.auto() 

79 

80 

81class ParkingDetails(enum.Enum): 

82 free_onsite = enum.auto() 

83 free_offsite = enum.auto() 

84 paid_onsite = enum.auto() 

85 paid_offsite = enum.auto() 

86 

87 

88class TimezoneArea(Base): 

89 __tablename__ = "timezone_areas" 

90 id = Column(BigInteger, primary_key=True) 

91 

92 tzid = Column(String) 

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

94 

95 __table_args__ = ( 

96 Index( 

97 "ix_timezone_areas_geom_tzid", 

98 geom, 

99 tzid, 

100 postgresql_using="gist", 

101 ), 

102 ) 

103 

104 

105class User(Base): 

106 """ 

107 Basic user and profile details 

108 """ 

109 

110 __tablename__ = "users" 

111 

112 id = Column(BigInteger, primary_key=True) 

113 

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

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

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

117 hashed_password = Column(Binary, nullable=True) 

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

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

120 

121 # timezones should always be UTC 

122 ## location 

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

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

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

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

127 geom_radius = Column(Float, nullable=True) 

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

129 city = Column(String, nullable=False) 

130 hometown = Column(String, nullable=True) 

131 

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

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

134 

135 timezone = column_property( 

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

137 deferred=True, 

138 ) 

139 

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

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

142 

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

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

145 # same as above for host requests 

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

147 

148 # display name 

149 name = Column(String, nullable=False) 

150 gender = Column(String, nullable=False) 

151 pronouns = Column(String, nullable=True) 

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

153 

154 # name as on official docs for verification, etc. not needed until verification 

155 full_name = Column(String, nullable=True) 

156 

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

158 

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

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

161 

162 # community standing score 

163 community_standing = Column(Float, nullable=True) 

164 

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

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

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

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

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

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

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

172 

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

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

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

176 

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

178 # accidental or they changed their mind 

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

180 undelete_token = Column(String, nullable=True) 

181 # validity of the undelete token 

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

183 

184 # hosting preferences 

185 max_guests = Column(Integer, nullable=True) 

186 last_minute = Column(Boolean, nullable=True) 

187 has_pets = Column(Boolean, nullable=True) 

188 accepts_pets = Column(Boolean, nullable=True) 

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

190 has_kids = Column(Boolean, nullable=True) 

191 accepts_kids = Column(Boolean, nullable=True) 

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

193 has_housemates = Column(Boolean, nullable=True) 

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

195 wheelchair_accessible = Column(Boolean, nullable=True) 

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

197 smokes_at_home = Column(Boolean, nullable=True) 

198 drinking_allowed = Column(Boolean, nullable=True) 

199 drinks_at_home = Column(Boolean, nullable=True) 

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

201 

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

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

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

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

206 parking = Column(Boolean, nullable=True) 

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

208 camping_ok = Column(Boolean, nullable=True) 

209 

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

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

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

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

214 

215 # number of onboarding emails sent 

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

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

218 

219 added_to_mailing_list = Column(Boolean, nullable=False, server_default="false") 

220 

221 last_digest_sent = Column(DateTime(timezone=True), nullable=True) 

222 

223 # for changing their email 

224 new_email = Column(String, nullable=True) 

225 old_email_token = Column(String, nullable=True) 

226 old_email_token_created = Column(DateTime(timezone=True), nullable=True) 

227 old_email_token_expiry = Column(DateTime(timezone=True), nullable=True) 

228 need_to_confirm_via_old_email = Column(Boolean, nullable=True, default=None) 

229 new_email_token = Column(String, nullable=True) 

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

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

232 need_to_confirm_via_new_email = Column(Boolean, nullable=True, default=None) 

233 

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

235 

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

237 # ,-------------------, 

238 # | Start | 

239 # | phone = None | someone else 

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

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

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

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

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

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

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

247 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------' 

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

249 # | | ^ V | | 

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

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

252 # | phone = xx | | phone = xx | | | phone = xx | 

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

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

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

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

257 # '-----------------' '-------------------' '-----------------------' 

258 

259 # randomly generated Luhn 6-digit string 

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

261 

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

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

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

265 

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

267 # e.g. cus_JjoXHttuZopv0t 

268 # for new US entity 

269 stripe_customer_id = Column(String, nullable=True) 

270 # for old AU entity 

271 stripe_customer_id_old = Column(String, nullable=True) 

272 

273 # True if the user has opted in to get notifications using the new notification system 

274 # This column will be removed in the future when notifications are enabled for everyone and come out of preview 

275 new_notifications_enabled = Column(Boolean, nullable=False, server_default=text("false")) 

276 

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

278 

279 __table_args__ = ( 

280 # Verified phone numbers should be unique 

281 Index( 

282 "ix_users_unique_phone", 

283 phone, 

284 unique=True, 

285 postgresql_where=phone_verification_verified != None, 

286 ), 

287 Index( 

288 "ix_users_active", 

289 id, 

290 postgresql_where=~is_banned & ~is_deleted, 

291 ), 

292 # There are three possible states for need_to_confirm_via_old_email, old_email_token, old_email_token_created, and old_email_token_expiry 

293 # 1) All None (default) 

294 # 2) need_to_confirm_via_old_email is True and the others have assigned value (confirmation initiated) 

295 # 3) need_to_confirm_via_old_email is False and the others are None (confirmation via old email complete) 

296 CheckConstraint( 

297 "(need_to_confirm_via_old_email IS NULL AND old_email_token IS NULL AND old_email_token_created IS NULL AND old_email_token_expiry IS NULL) OR \ 

298 (need_to_confirm_via_old_email IS TRUE AND old_email_token IS NOT NULL AND old_email_token_created IS NOT NULL AND old_email_token_expiry IS NOT NULL) OR \ 

299 (need_to_confirm_via_old_email IS FALSE AND old_email_token IS NULL AND old_email_token_created IS NULL AND old_email_token_expiry IS NULL)", 

300 name="check_old_email_token_state", 

301 ), 

302 # There are three possible states for need_to_confirm_via_new_email, new_email_token, new_email_token_created, and new_email_token_expiry 

303 # They mirror the states above 

304 CheckConstraint( 

305 "(need_to_confirm_via_new_email IS NULL AND new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL) OR \ 

306 (need_to_confirm_via_new_email IS TRUE AND new_email_token IS NOT NULL AND new_email_token_created IS NOT NULL AND new_email_token_expiry IS NOT NULL) OR \ 

307 (need_to_confirm_via_new_email IS FALSE AND new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL)", 

308 name="check_new_email_token_state", 

309 ), 

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

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

312 CheckConstraint( 

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

314 name="phone_verified_conditions", 

315 ), 

316 # Email must match our regex 

317 CheckConstraint( 

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

319 name="valid_email", 

320 ), 

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

322 CheckConstraint( 

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

324 name="undelete_nullity", 

325 ), 

326 ) 

327 

328 @hybrid_property 

329 def has_completed_profile(self): 

330 return self.avatar_key is not None and self.about_me is not None and len(self.about_me) >= 20 

331 

332 @has_completed_profile.expression 

333 def has_completed_profile(cls): 

334 return (cls.avatar_key != None) & (func.character_length(cls.about_me) >= 20) 

335 

336 @property 

337 def has_password(self): 

338 return self.hashed_password is not None 

339 

340 @hybrid_property 

341 def is_jailed(self): 

342 return ( 

343 (self.accepted_tos < TOS_VERSION) 

344 | (self.accepted_community_guidelines < GUIDELINES_VERSION) 

345 | self.is_missing_location 

346 | (self.hashed_password == None) 

347 ) 

348 

349 @hybrid_property 

350 def is_missing_location(self): 

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

352 

353 @hybrid_property 

354 def is_visible(self): 

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

356 

357 @property 

358 def coordinates(self): 

359 if self.geom: 

360 return get_coordinates(self.geom) 

361 else: 

362 return None 

363 

364 @property 

365 def age(self): 

366 max_day = monthrange(date.today().year, self.birthdate.month)[1] 

367 age = date.today().year - self.birthdate.year 

368 # in case of leap-day babies, make sure the date is valid for this year 

369 safe_birthdate = self.birthdate 

370 if self.birthdate.day > max_day: 

371 safe_birthdate = safe_birthdate.replace(day=max_day) 

372 if date.today() < safe_birthdate.replace(year=date.today().year): 

373 age -= 1 

374 return age 

375 

376 @property 

377 def display_joined(self): 

378 """ 

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

380 """ 

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

382 

383 @property 

384 def display_last_active(self): 

385 """ 

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

387 """ 

388 return last_active_coarsen(self.last_active) 

389 

390 @hybrid_property 

391 def phone_is_verified(self): 

392 return ( 

393 self.phone_verification_verified is not None 

394 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME 

395 ) 

396 

397 @phone_is_verified.expression 

398 def phone_is_verified(cls): 

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

400 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME 

401 ) 

402 

403 @hybrid_property 

404 def phone_code_expired(self): 

405 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME 

406 

407 def __repr__(self): 

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

409 

410 

411class OneTimeDonation(Base): 

412 __tablename__ = "one_time_donations" 

413 id = Column(BigInteger, primary_key=True) 

414 

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

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

417 amount = Column(Float, nullable=False) 

418 stripe_checkout_session_id = Column(String, nullable=False) 

419 stripe_payment_intent_id = Column(String, nullable=False) 

420 paid = Column(DateTime(timezone=True), nullable=True) 

421 

422 user = relationship("User", backref="one_time_donations") 

423 

424 

425class RecurringDonation(Base): 

426 __tablename__ = "recurring_donations" 

427 

428 id = Column(BigInteger, primary_key=True) 

429 

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

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

432 amount = Column(Float, nullable=False) 

433 stripe_checkout_session_id = Column(String, nullable=False) 

434 # for some silly reason the events come unordered from stripe 

435 # e.g. sub_JjonjdfUIeZyn0 

436 stripe_subscription_id = Column(String, nullable=True) 

437 

438 user = relationship("User", backref="recurring_donations") 

439 

440 

441class Invoice(Base): 

442 """ 

443 Successful donations, both one off and recurring 

444 

445 Triggered by `payment_intent.succeeded` webhook 

446 """ 

447 

448 __tablename__ = "invoices" 

449 

450 id = Column(BigInteger, primary_key=True) 

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

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

453 

454 amount = Column(Float, nullable=False) 

455 

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

457 stripe_receipt_url = Column(String, nullable=False) 

458 

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

460 

461 

462class LanguageFluency(enum.Enum): 

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

464 beginner = 1 

465 conversational = 2 

466 fluent = 3 

467 

468 

469class LanguageAbility(Base): 

470 __tablename__ = "language_abilities" 

471 __table_args__ = ( 

472 # Users can only have one language ability per language 

473 UniqueConstraint("user_id", "language_code"), 

474 ) 

475 

476 id = Column(BigInteger, primary_key=True) 

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

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

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

480 

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

482 language = relationship("Language") 

483 

484 

485class RegionVisited(Base): 

486 __tablename__ = "regions_visited" 

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

488 

489 id = Column(BigInteger, primary_key=True) 

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

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

492 

493 user = relationship("User") 

494 region = relationship("Region") 

495 

496 

497class RegionLived(Base): 

498 __tablename__ = "regions_lived" 

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

500 

501 id = Column(BigInteger, primary_key=True) 

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

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

504 

505 user = relationship("User") 

506 region = relationship("Region") 

507 

508 

509class FriendStatus(enum.Enum): 

510 pending = enum.auto() 

511 accepted = enum.auto() 

512 rejected = enum.auto() 

513 cancelled = enum.auto() 

514 

515 

516class FriendRelationship(Base): 

517 """ 

518 Friendship relations between users 

519 

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

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

522 """ 

523 

524 __tablename__ = "friend_relationships" 

525 

526 id = Column(BigInteger, primary_key=True) 

527 

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

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

530 

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

532 

533 # timezones should always be UTC 

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

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

536 

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

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

539 

540 

541class ContributeOption(enum.Enum): 

542 yes = enum.auto() 

543 maybe = enum.auto() 

544 no = enum.auto() 

545 

546 

547class ContributorForm(Base): 

548 """ 

549 Someone filled in the contributor form 

550 """ 

551 

552 __tablename__ = "contributor_forms" 

553 

554 id = Column(BigInteger, primary_key=True) 

555 

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

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

558 

559 ideas = Column(String, nullable=True) 

560 features = Column(String, nullable=True) 

561 experience = Column(String, nullable=True) 

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

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

564 expertise = Column(String, nullable=True) 

565 

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

567 

568 @hybrid_property 

569 def is_filled(self): 

570 """ 

571 Whether the form counts as having been filled 

572 """ 

573 return ( 

574 (self.ideas != None) 

575 | (self.features != None) 

576 | (self.experience != None) 

577 | (self.contribute != None) 

578 | (self.contribute_ways != []) 

579 | (self.expertise != None) 

580 ) 

581 

582 @property 

583 def should_notify(self): 

584 """ 

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

586 

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

588 """ 

589 return (self.expertise != None) | (not set(self.contribute_ways).issubset(set(["community", "blog", "other"]))) 

590 

591 

592class SignupFlow(Base): 

593 """ 

594 Signup flows/incomplete users 

595 

596 Coinciding fields have the same meaning as in User 

597 """ 

598 

599 __tablename__ = "signup_flows" 

600 

601 id = Column(BigInteger, primary_key=True) 

602 

603 # housekeeping 

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

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

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

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

608 email_token = Column(String, nullable=True) 

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

610 

611 ## Basic 

612 name = Column(String, nullable=False) 

613 # TODO: unique across both tables 

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

615 # TODO: invitation, attribution 

616 

617 ## Account 

618 # TODO: unique across both tables 

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

620 hashed_password = Column(Binary, nullable=True) 

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

622 gender = Column(String, nullable=True) 

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

624 city = Column(String, nullable=True) 

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

626 geom_radius = Column(Float, nullable=True) 

627 

628 accepted_tos = Column(Integer, nullable=True) 

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

630 

631 ## Feedback 

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

633 ideas = Column(String, nullable=True) 

634 features = Column(String, nullable=True) 

635 experience = Column(String, nullable=True) 

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

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

638 expertise = Column(String, nullable=True) 

639 

640 @hybrid_property 

641 def token_is_valid(self): 

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

643 

644 @hybrid_property 

645 def account_is_filled(self): 

646 return ( 

647 (self.username != None) 

648 & (self.birthdate != None) 

649 & (self.gender != None) 

650 & (self.hosting_status != None) 

651 & (self.city != None) 

652 & (self.geom != None) 

653 & (self.geom_radius != None) 

654 & (self.accepted_tos != None) 

655 ) 

656 

657 @hybrid_property 

658 def is_completed(self): 

659 return ( 

660 self.email_verified 

661 & self.account_is_filled 

662 & self.filled_feedback 

663 & (self.accepted_community_guidelines == GUIDELINES_VERSION) 

664 ) 

665 

666 

667class LoginToken(Base): 

668 """ 

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

670 """ 

671 

672 __tablename__ = "login_tokens" 

673 token = Column(String, primary_key=True) 

674 

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

676 

677 # timezones should always be UTC 

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

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

680 

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

682 

683 @hybrid_property 

684 def is_valid(self): 

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

686 

687 def __repr__(self): 

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

689 

690 

691class PasswordResetToken(Base): 

692 __tablename__ = "password_reset_tokens" 

693 token = Column(String, primary_key=True) 

694 

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

696 

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

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

699 

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

701 

702 @hybrid_property 

703 def is_valid(self): 

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

705 

706 def __repr__(self): 

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

708 

709 

710class AccountDeletionToken(Base): 

711 __tablename__ = "account_deletion_tokens" 

712 

713 token = Column(String, primary_key=True) 

714 

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

716 

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

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

719 

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

721 

722 @hybrid_property 

723 def is_valid(self): 

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

725 

726 def __repr__(self): 

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

728 

729 

730class UserSession(Base): 

731 """ 

732 API keys/session cookies for the app 

733 

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

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

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

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

738 site. 

739 

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

741 

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

743 """ 

744 

745 __tablename__ = "sessions" 

746 token = Column(String, primary_key=True) 

747 

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

749 

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

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

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

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

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

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

756 

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

758 long_lived = Column(Boolean, nullable=False) 

759 

760 # the time at which the session was created 

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

762 

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

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

765 

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

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

768 

769 # the last time this session was used 

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

771 

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

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

774 

775 # details of the browser, if available 

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

777 ip_address = Column(String, nullable=True) 

778 user_agent = Column(String, nullable=True) 

779 

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

781 

782 @hybrid_property 

783 def is_valid(self): 

784 """ 

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

786 

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

788 

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

790 """ 

791 return ( 

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

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

794 & (self.deleted == None) 

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

796 ) 

797 

798 

799class Conversation(Base): 

800 """ 

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

802 """ 

803 

804 __tablename__ = "conversations" 

805 

806 id = Column(BigInteger, primary_key=True) 

807 # timezone should always be UTC 

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

809 

810 def __repr__(self): 

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

812 

813 

814class GroupChat(Base): 

815 """ 

816 Group chat 

817 """ 

818 

819 __tablename__ = "group_chats" 

820 

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

822 

823 title = Column(String, nullable=True) 

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

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

826 is_dm = Column(Boolean, nullable=False) 

827 

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

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

830 

831 def __repr__(self): 

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

833 

834 

835class GroupChatRole(enum.Enum): 

836 admin = enum.auto() 

837 participant = enum.auto() 

838 

839 

840class GroupChatSubscription(Base): 

841 """ 

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

843 """ 

844 

845 __tablename__ = "group_chat_subscriptions" 

846 id = Column(BigInteger, primary_key=True) 

847 

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

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

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

851 

852 # timezones should always be UTC 

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

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

855 

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

857 

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

859 

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

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

862 

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

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

865 

866 def muted_display(self): 

867 """ 

868 Returns (muted, muted_until) display values: 

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

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

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

872 """ 

873 if self.muted_until < now(): 

874 return (False, None) 

875 elif self.muted_until == DATETIME_INFINITY: 

876 return (True, None) 

877 else: 

878 return (True, self.muted_until) 

879 

880 @hybrid_property 

881 def is_muted(self): 

882 return self.muted_until > func.now() 

883 

884 def __repr__(self): 

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

886 

887 

888class MessageType(enum.Enum): 

889 text = enum.auto() 

890 # e.g. 

891 # image = 

892 # emoji = 

893 # ... 

894 chat_created = enum.auto() 

895 chat_edited = enum.auto() 

896 user_invited = enum.auto() 

897 user_left = enum.auto() 

898 user_made_admin = enum.auto() 

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

900 host_request_status_changed = enum.auto() 

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

902 

903 

904class HostRequestStatus(enum.Enum): 

905 pending = enum.auto() 

906 accepted = enum.auto() 

907 rejected = enum.auto() 

908 confirmed = enum.auto() 

909 cancelled = enum.auto() 

910 

911 

912class Message(Base): 

913 """ 

914 A message. 

915 

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

917 """ 

918 

919 __tablename__ = "messages" 

920 

921 id = Column(BigInteger, primary_key=True) 

922 

923 # which conversation the message belongs in 

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

925 

926 # the user that sent the message/command 

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

928 

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

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

931 

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

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

934 

935 # time sent, timezone should always be UTC 

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

937 

938 # the plain-text message text if not control 

939 text = Column(String, nullable=True) 

940 

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

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

943 

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

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

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

947 

948 @property 

949 def is_normal_message(self): 

950 """ 

951 There's only one normal type atm, text 

952 """ 

953 return self.message_type == MessageType.text 

954 

955 def __repr__(self): 

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

957 

958 

959class ContentReport(Base): 

960 """ 

961 A piece of content reported to admins 

962 """ 

963 

964 __tablename__ = "content_reports" 

965 

966 id = Column(BigInteger, primary_key=True) 

967 

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

969 

970 # the user who reported or flagged the content 

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

972 

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

974 reason = Column(String, nullable=False) 

975 # a short description 

976 description = Column(String, nullable=False) 

977 

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

979 content_ref = Column(String, nullable=False) 

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

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

982 

983 # details of the browser, if available 

984 user_agent = Column(String, nullable=False) 

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

986 page = Column(String, nullable=False) 

987 

988 # see comments above for reporting vs author 

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

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

991 

992 

993class Email(Base): 

994 """ 

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

996 """ 

997 

998 __tablename__ = "emails" 

999 

1000 id = Column(String, primary_key=True) 

1001 

1002 # timezone should always be UTC 

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

1004 

1005 sender_name = Column(String, nullable=False) 

1006 sender_email = Column(String, nullable=False) 

1007 

1008 recipient = Column(String, nullable=False) 

1009 subject = Column(String, nullable=False) 

1010 

1011 plain = Column(String, nullable=False) 

1012 html = Column(String, nullable=False) 

1013 

1014 

1015class SMS(Base): 

1016 """ 

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

1018 """ 

1019 

1020 __tablename__ = "smss" 

1021 

1022 id = Column(BigInteger, primary_key=True) 

1023 

1024 # timezone should always be UTC 

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

1026 # AWS message id 

1027 message_id = Column(String, nullable=False) 

1028 

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

1030 sms_sender_id = Column(String, nullable=False) 

1031 number = Column(String, nullable=False) 

1032 message = Column(String, nullable=False) 

1033 

1034 

1035class HostRequest(Base): 

1036 """ 

1037 A request to stay with a host 

1038 """ 

1039 

1040 __tablename__ = "host_requests" 

1041 

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

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

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

1045 

1046 # TODO: proper timezone handling 

1047 timezone = "Etc/UTC" 

1048 

1049 # dates in the timezone above 

1050 from_date = Column(Date, nullable=False) 

1051 to_date = Column(Date, nullable=False) 

1052 

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

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

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

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

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

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

1059 

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

1061 

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

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

1064 

1065 # number of reference reminders sent out 

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

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

1068 

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

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

1071 conversation = relationship("Conversation") 

1072 

1073 @hybrid_property 

1074 def can_write_reference(self): 

1075 return ( 

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

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

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

1079 ) 

1080 

1081 @can_write_reference.expression 

1082 def can_write_reference(cls): 

1083 return ( 

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

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

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

1087 ) 

1088 

1089 def __repr__(self): 

1090 return ( 

1091 f"HostRequest(id={self.conversation_id}, from_user_id={self.from_user_id}, to_user_id={self.to_user_id}...)" 

1092 ) 

1093 

1094 

1095class ReferenceType(enum.Enum): 

1096 friend = enum.auto() 

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

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

1099 

1100 

1101class Reference(Base): 

1102 """ 

1103 Reference from one user to another 

1104 """ 

1105 

1106 __tablename__ = "references" 

1107 

1108 id = Column(BigInteger, primary_key=True) 

1109 # timezone should always be UTC 

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

1111 

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

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

1114 

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

1116 

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

1118 

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

1120 # text that's only visible to mods 

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

1122 

1123 rating = Column(Float, nullable=False) 

1124 was_appropriate = Column(Boolean, nullable=False) 

1125 

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

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

1128 

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

1130 

1131 __table_args__ = ( 

1132 # Rating must be between 0 and 1, inclusive 

1133 CheckConstraint( 

1134 "rating BETWEEN 0 AND 1", 

1135 name="rating_between_0_and_1", 

1136 ), 

1137 # Has host_request_id or it's a friend reference 

1138 CheckConstraint( 

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

1140 name="host_request_id_xor_friend_reference", 

1141 ), 

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

1143 Index( 

1144 "ix_references_unique_friend_reference", 

1145 from_user_id, 

1146 to_user_id, 

1147 reference_type, 

1148 unique=True, 

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

1150 ), 

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

1152 Index( 

1153 "ix_references_unique_per_host_request", 

1154 from_user_id, 

1155 to_user_id, 

1156 host_request_id, 

1157 unique=True, 

1158 postgresql_where=(host_request_id != None), 

1159 ), 

1160 ) 

1161 

1162 @property 

1163 def should_report(self): 

1164 """ 

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

1166 """ 

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

1168 

1169 

1170class InitiatedUpload(Base): 

1171 """ 

1172 Started downloads, not necessarily complete yet. 

1173 """ 

1174 

1175 __tablename__ = "initiated_uploads" 

1176 

1177 key = Column(String, primary_key=True) 

1178 

1179 # timezones should always be UTC 

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

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

1182 

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

1184 

1185 initiator_user = relationship("User") 

1186 

1187 @hybrid_property 

1188 def is_valid(self): 

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

1190 

1191 

1192class Upload(Base): 

1193 """ 

1194 Completed uploads. 

1195 """ 

1196 

1197 __tablename__ = "uploads" 

1198 key = Column(String, primary_key=True) 

1199 

1200 filename = Column(String, nullable=False) 

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

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

1203 

1204 # photo credit, etc 

1205 credit = Column(String, nullable=True) 

1206 

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

1208 

1209 def _url(self, size): 

1210 return f"{config['MEDIA_SERVER_BASE_URL']}/img/{size}/{self.filename}" 

1211 

1212 @property 

1213 def thumbnail_url(self): 

1214 return self._url("thumbnail") 

1215 

1216 @property 

1217 def full_url(self): 

1218 return self._url("full") 

1219 

1220 

1221communities_seq = Sequence("communities_seq") 

1222 

1223 

1224class Node(Base): 

1225 """ 

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

1227 

1228 Administered by the official cluster 

1229 """ 

1230 

1231 __tablename__ = "nodes" 

1232 

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

1234 

1235 # name and description come from official cluster 

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

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

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

1239 

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

1241 

1242 contained_users = relationship( 

1243 "User", 

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

1245 viewonly=True, 

1246 uselist=True, 

1247 ) 

1248 

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

1250 

1251 

1252class Cluster(Base): 

1253 """ 

1254 Cluster, administered grouping of content 

1255 """ 

1256 

1257 __tablename__ = "clusters" 

1258 

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

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

1261 name = Column(String, nullable=False) 

1262 # short description 

1263 description = Column(String, nullable=False) 

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

1265 

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

1267 

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

1269 

1270 official_cluster_for_node = relationship( 

1271 "Node", 

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

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

1274 uselist=False, 

1275 viewonly=True, 

1276 ) 

1277 

1278 parent_node = relationship( 

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

1280 ) 

1281 

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

1283 # all pages 

1284 pages = relationship( 

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

1286 ) 

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

1288 discussions = relationship( 

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

1290 ) 

1291 

1292 # includes also admins 

1293 members = relationship( 

1294 "User", 

1295 lazy="dynamic", 

1296 backref="cluster_memberships", 

1297 secondary="cluster_subscriptions", 

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

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

1300 viewonly=True, 

1301 ) 

1302 

1303 admins = relationship( 

1304 "User", 

1305 lazy="dynamic", 

1306 backref="cluster_adminships", 

1307 secondary="cluster_subscriptions", 

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

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

1310 viewonly=True, 

1311 ) 

1312 

1313 main_page = relationship( 

1314 "Page", 

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

1316 viewonly=True, 

1317 uselist=False, 

1318 ) 

1319 

1320 __table_args__ = ( 

1321 # Each node can have at most one official cluster 

1322 Index( 

1323 "ix_clusters_owner_parent_node_id_is_official_cluster", 

1324 parent_node_id, 

1325 is_official_cluster, 

1326 unique=True, 

1327 postgresql_where=is_official_cluster, 

1328 ), 

1329 ) 

1330 

1331 

1332class NodeClusterAssociation(Base): 

1333 """ 

1334 NodeClusterAssociation, grouping of nodes 

1335 """ 

1336 

1337 __tablename__ = "node_cluster_associations" 

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

1339 

1340 id = Column(BigInteger, primary_key=True) 

1341 

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

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

1344 

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

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

1347 

1348 

1349class ClusterRole(enum.Enum): 

1350 member = enum.auto() 

1351 admin = enum.auto() 

1352 

1353 

1354class ClusterSubscription(Base): 

1355 """ 

1356 ClusterSubscription of a user 

1357 """ 

1358 

1359 __tablename__ = "cluster_subscriptions" 

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

1361 

1362 id = Column(BigInteger, primary_key=True) 

1363 

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

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

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

1367 

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

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

1370 

1371 

1372class ClusterPageAssociation(Base): 

1373 """ 

1374 pages related to clusters 

1375 """ 

1376 

1377 __tablename__ = "cluster_page_associations" 

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

1379 

1380 id = Column(BigInteger, primary_key=True) 

1381 

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

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

1384 

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

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

1387 

1388 

1389class PageType(enum.Enum): 

1390 main_page = enum.auto() 

1391 place = enum.auto() 

1392 guide = enum.auto() 

1393 

1394 

1395class Page(Base): 

1396 """ 

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

1398 """ 

1399 

1400 __tablename__ = "pages" 

1401 

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

1403 

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

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

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

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

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

1409 

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

1411 

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

1413 

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

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

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

1417 owner_cluster = relationship( 

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

1419 ) 

1420 

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

1422 

1423 __table_args__ = ( 

1424 # Only one of owner_user and owner_cluster should be set 

1425 CheckConstraint( 

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

1427 name="one_owner", 

1428 ), 

1429 # Only clusters can own main pages 

1430 CheckConstraint( 

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

1432 name="main_page_owned_by_cluster", 

1433 ), 

1434 # Each cluster can have at most one main page 

1435 Index( 

1436 "ix_pages_owner_cluster_id_type", 

1437 owner_cluster_id, 

1438 type, 

1439 unique=True, 

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

1441 ), 

1442 ) 

1443 

1444 def __repr__(self): 

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

1446 

1447 

1448class PageVersion(Base): 

1449 """ 

1450 version of page content 

1451 """ 

1452 

1453 __tablename__ = "page_versions" 

1454 

1455 id = Column(BigInteger, primary_key=True) 

1456 

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

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

1459 title = Column(String, nullable=False) 

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

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

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

1463 # the human-readable address 

1464 address = Column(String, nullable=True) 

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

1466 

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

1468 

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

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

1471 photo = relationship("Upload") 

1472 

1473 __table_args__ = ( 

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

1475 CheckConstraint( 

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

1477 name="geom_iff_address", 

1478 ), 

1479 ) 

1480 

1481 @property 

1482 def coordinates(self): 

1483 # returns (lat, lng) or None 

1484 if self.geom: 

1485 return get_coordinates(self.geom) 

1486 else: 

1487 return None 

1488 

1489 def __repr__(self): 

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

1491 

1492 

1493class ClusterEventAssociation(Base): 

1494 """ 

1495 events related to clusters 

1496 """ 

1497 

1498 __tablename__ = "cluster_event_associations" 

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

1500 

1501 id = Column(BigInteger, primary_key=True) 

1502 

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

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

1505 

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

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

1508 

1509 

1510class Event(Base): 

1511 """ 

1512 An event is compose of two parts: 

1513 

1514 * An event template (Event) 

1515 * An occurrence (EventOccurrence) 

1516 

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

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

1519 """ 

1520 

1521 __tablename__ = "events" 

1522 

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

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

1525 

1526 title = Column(String, nullable=False) 

1527 

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

1529 

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

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

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

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

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

1535 

1536 parent_node = relationship( 

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

1538 ) 

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

1540 subscribers = relationship( 

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

1542 ) 

1543 organizers = relationship( 

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

1545 ) 

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

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

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

1549 owner_cluster = relationship( 

1550 "Cluster", 

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

1552 uselist=False, 

1553 foreign_keys="Event.owner_cluster_id", 

1554 ) 

1555 

1556 __table_args__ = ( 

1557 # Only one of owner_user and owner_cluster should be set 

1558 CheckConstraint( 

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

1560 name="one_owner", 

1561 ), 

1562 ) 

1563 

1564 

1565class EventOccurrence(Base): 

1566 __tablename__ = "event_occurrences" 

1567 

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

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

1570 

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

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

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

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

1575 

1576 # a null geom is an online-only event 

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

1578 # physical address, iff geom is not null 

1579 address = Column(String, nullable=True) 

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

1581 link = Column(String, nullable=True) 

1582 

1583 timezone = "Etc/UTC" 

1584 

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

1586 # simplifies database constraints, etc 

1587 during = Column(TSTZRANGE, nullable=False) 

1588 

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

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

1591 

1592 creator_user = relationship( 

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

1594 ) 

1595 event = relationship( 

1596 "Event", 

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

1598 remote_side="Event.id", 

1599 foreign_keys="EventOccurrence.event_id", 

1600 ) 

1601 

1602 photo = relationship("Upload") 

1603 

1604 __table_args__ = ( 

1605 # Geom and address go together 

1606 CheckConstraint( 

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

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

1609 name="geom_iff_address", 

1610 ), 

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

1612 CheckConstraint( 

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

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

1615 name="link_or_geom", 

1616 ), 

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

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

1619 ) 

1620 

1621 @property 

1622 def coordinates(self): 

1623 # returns (lat, lng) or None 

1624 if self.geom: 

1625 return get_coordinates(self.geom) 

1626 else: 

1627 return None 

1628 

1629 @hybrid_property 

1630 def start_time(self): 

1631 return self.during.lower 

1632 

1633 @start_time.expression 

1634 def start_time(cls): 

1635 return func.lower(cls.during) 

1636 

1637 @hybrid_property 

1638 def end_time(self): 

1639 return self.during.upper 

1640 

1641 @end_time.expression 

1642 def end_time(cls): 

1643 return func.upper(cls.during) 

1644 

1645 

1646class EventSubscription(Base): 

1647 """ 

1648 users subscriptions to events 

1649 """ 

1650 

1651 __tablename__ = "event_subscriptions" 

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

1653 

1654 id = Column(BigInteger, primary_key=True) 

1655 

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

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

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

1659 

1660 user = relationship("User") 

1661 event = relationship("Event") 

1662 

1663 

1664class EventOrganizer(Base): 

1665 """ 

1666 Organizers for events 

1667 """ 

1668 

1669 __tablename__ = "event_organizers" 

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

1671 

1672 id = Column(BigInteger, primary_key=True) 

1673 

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

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

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

1677 

1678 user = relationship("User") 

1679 event = relationship("Event") 

1680 

1681 

1682class AttendeeStatus(enum.Enum): 

1683 going = enum.auto() 

1684 maybe = enum.auto() 

1685 

1686 

1687class EventOccurrenceAttendee(Base): 

1688 """ 

1689 Attendees for events 

1690 """ 

1691 

1692 __tablename__ = "event_occurrence_attendees" 

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

1694 

1695 id = Column(BigInteger, primary_key=True) 

1696 

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

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

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

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

1701 

1702 user = relationship("User") 

1703 occurrence = relationship("EventOccurrence", backref=backref("attendees", lazy="dynamic")) 

1704 

1705 

1706class ClusterDiscussionAssociation(Base): 

1707 """ 

1708 discussions related to clusters 

1709 """ 

1710 

1711 __tablename__ = "cluster_discussion_associations" 

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

1713 

1714 id = Column(BigInteger, primary_key=True) 

1715 

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

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

1718 

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

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

1721 

1722 

1723class Discussion(Base): 

1724 """ 

1725 forum board 

1726 """ 

1727 

1728 __tablename__ = "discussions" 

1729 

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

1731 

1732 title = Column(String, nullable=False) 

1733 content = Column(String, nullable=False) 

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

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

1736 

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

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

1739 

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

1741 

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

1743 

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

1745 

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

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

1748 

1749 

1750class DiscussionSubscription(Base): 

1751 """ 

1752 users subscriptions to discussions 

1753 """ 

1754 

1755 __tablename__ = "discussion_subscriptions" 

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

1757 

1758 id = Column(BigInteger, primary_key=True) 

1759 

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

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

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

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

1764 

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

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

1767 

1768 

1769class Thread(Base): 

1770 """ 

1771 Thread 

1772 """ 

1773 

1774 __tablename__ = "threads" 

1775 

1776 id = Column(BigInteger, primary_key=True) 

1777 

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

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

1780 

1781 

1782class Comment(Base): 

1783 """ 

1784 Comment 

1785 """ 

1786 

1787 __tablename__ = "comments" 

1788 

1789 id = Column(BigInteger, primary_key=True) 

1790 

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

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

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

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

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

1796 

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

1798 

1799 

1800class Reply(Base): 

1801 """ 

1802 Reply 

1803 """ 

1804 

1805 __tablename__ = "replies" 

1806 

1807 id = Column(BigInteger, primary_key=True) 

1808 

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

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

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

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

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

1814 

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

1816 

1817 

1818class BackgroundJobType(enum.Enum): 

1819 # payload: jobs.SendEmailPayload 

1820 send_email = enum.auto() 

1821 # payload: google.protobuf.Empty 

1822 purge_login_tokens = enum.auto() 

1823 # payload: google.protobuf.Empty 

1824 purge_signup_tokens = enum.auto() 

1825 # payload: google.protobuf.Empty 

1826 purge_account_deletion_tokens = enum.auto() 

1827 # payload: google.protobuf.Empty 

1828 purge_password_reset_tokens = enum.auto() 

1829 # payload: google.protobuf.Empty 

1830 send_message_notifications = enum.auto() 

1831 # payload: google.protobuf.Empty 

1832 send_onboarding_emails = enum.auto() 

1833 # payload: google.protobuf.Empty 

1834 add_users_to_email_list = enum.auto() 

1835 # payload: google.protobuf.Empty 

1836 send_request_notifications = enum.auto() 

1837 # payload: google.protobuf.Empty 

1838 enforce_community_membership = enum.auto() 

1839 # payload: google.protobuf.Empty 

1840 send_reference_reminders = enum.auto() 

1841 # payload: jobs.HandleNotificationPayload 

1842 handle_notification = enum.auto() 

1843 # payload: google.protobuf.Empty 

1844 handle_email_notifications = enum.auto() 

1845 # payload: google.protobuf.Empty 

1846 handle_email_digests = enum.auto() 

1847 # payload: jobs.GenerateMessageNotificationsPayload 

1848 generate_message_notifications = enum.auto() 

1849 # payload: google.protobuf.Empty 

1850 update_recommendation_scores = enum.auto() 

1851 

1852 

1853class BackgroundJobState(enum.Enum): 

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

1855 pending = enum.auto() 

1856 # job complete 

1857 completed = enum.auto() 

1858 # error occured, will be retried 

1859 error = enum.auto() 

1860 # failed too many times, not retrying anymore 

1861 failed = enum.auto() 

1862 

1863 

1864class BackgroundJob(Base): 

1865 """ 

1866 This table implements a queue of background jobs. 

1867 """ 

1868 

1869 __tablename__ = "background_jobs" 

1870 

1871 id = Column(BigInteger, primary_key=True) 

1872 

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

1874 job_type = Column(Enum(BackgroundJobType), nullable=False) 

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

1876 

1877 # time queued 

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

1879 

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

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

1882 

1883 # used to count number of retries for failed jobs 

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

1885 

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

1887 

1888 # protobuf encoded job payload 

1889 payload = Column(Binary, nullable=False) 

1890 

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

1892 failure_info = Column(String, nullable=True) 

1893 

1894 __table_args__ = ( 

1895 # Allows fast lookup of jobs to attempt 

1896 Index( 

1897 "ix_background_jobs_state_next_attempt_after", 

1898 state, 

1899 next_attempt_after, 

1900 ), 

1901 ) 

1902 

1903 @hybrid_property 

1904 def ready_for_retry(self): 

1905 return ( 

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

1907 & (self.try_count < self.max_tries) 

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

1909 ) 

1910 

1911 def __repr__(self): 

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

1913 

1914 

1915class NotificationDeliveryType(enum.Enum): 

1916 # send push notification to mobile/web 

1917 push = enum.auto() 

1918 # send individual email immediately 

1919 email = enum.auto() 

1920 # send in digest 

1921 digest = enum.auto() 

1922 

1923 

1924dt = NotificationDeliveryType 

1925 

1926 

1927class NotificationTopicAction(enum.Enum): 

1928 def __init__(self, topic, action, defaults): 

1929 self.topic = topic 

1930 self.action = action 

1931 self.defaults = defaults 

1932 

1933 def unpack(self): 

1934 return self.topic, self.action 

1935 

1936 # topic, action, default delivery types 

1937 friend_request__send = ("friend_request", "send", [dt.email, dt.push, dt.digest]) 

1938 friend_request__accept = ("friend_request", "accept", [dt.push, dt.digest]) 

1939 

1940 # host requests 

1941 host_request__create = ("host_request", "create", [dt.email, dt.push, dt.digest]) 

1942 host_request__accept = ("host_request", "accept", [dt.email, dt.push, dt.digest]) 

1943 host_request__reject = ("host_request", "reject", [dt.push, dt.digest]) 

1944 host_request__confirm = ("host_request", "confirm", [dt.email, dt.push, dt.digest]) 

1945 host_request__cancel = ("host_request", "cancel", [dt.push, dt.digest]) 

1946 host_request__message = ("host_request", "message", [dt.push, dt.digest]) 

1947 

1948 # account settings 

1949 password__change = ("password", "change", [dt.email, dt.push, dt.digest]) 

1950 email_address__change = ("email_address", "change", [dt.email, dt.push, dt.digest]) 

1951 phone_number__change = ("phone_number", "change", [dt.email, dt.push, dt.digest]) 

1952 phone_number__verify = ("phone_number", "verify", [dt.email, dt.push, dt.digest]) 

1953 # reset password 

1954 account_recovery__start = ("account_recovery", "start", [dt.email, dt.push, dt.digest]) 

1955 account_recovery__complete = ("account_recovery", "complete", [dt.email, dt.push, dt.digest]) 

1956 

1957 # admin actions 

1958 gender__change = ("gender", "change", [dt.email, dt.push, dt.digest]) 

1959 birthdate__change = ("birthdate", "change", [dt.email, dt.push, dt.digest]) 

1960 api_key__create = ("api_key", "create", [dt.email, dt.push, dt.digest]) 

1961 

1962 # group chats 

1963 chat__message = ("chat", "message", [dt.email, dt.push, dt.digest]) 

1964 

1965 

1966class NotificationPreference(Base): 

1967 __tablename__ = "notification_preferences" 

1968 

1969 id = Column(BigInteger, primary_key=True) 

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

1971 

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

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

1974 deliver = Column(Boolean, nullable=False) 

1975 

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

1977 

1978 

1979class Notification(Base): 

1980 """ 

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

1982 """ 

1983 

1984 __tablename__ = "notifications" 

1985 

1986 id = Column(BigInteger, primary_key=True) 

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

1988 

1989 # recipient user id 

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

1991 

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

1993 key = Column(String, nullable=False) 

1994 

1995 avatar_key = Column(String, nullable=True) 

1996 icon = Column(String, nullable=True) # the name (excluding .svg) in the resources/icons folder 

1997 title = Column(String, nullable=True) # bold markup surrounded by double asterisks allowed, otherwise plain text 

1998 content = Column(String, nullable=True) # bold markup surrounded by double asterisks allowed, otherwise plain text 

1999 link = Column(String, nullable=True) 

2000 

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

2002 

2003 @property 

2004 def topic(self): 

2005 return self.topic_action.topic 

2006 

2007 @property 

2008 def action(self): 

2009 return self.topic_action.action 

2010 

2011 @property 

2012 def plain_title(self): 

2013 # only bold is allowed 

2014 return self.title.replace("**", "") 

2015 

2016 

2017class NotificationDelivery(Base): 

2018 __tablename__ = "notification_deliveries" 

2019 __table_args__ = (UniqueConstraint("notification_id", "delivery_type"),) 

2020 

2021 id = Column(BigInteger, primary_key=True) 

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

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

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

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

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

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

2028 # todo: device id 

2029 # todo: receipt id, etc 

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

2031 

2032 

2033class Language(Base): 

2034 """ 

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

2036 """ 

2037 

2038 __tablename__ = "languages" 

2039 

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

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

2042 

2043 # the english name 

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

2045 

2046 

2047class Region(Base): 

2048 """ 

2049 Table of regions 

2050 """ 

2051 

2052 __tablename__ = "regions" 

2053 

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

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

2056 

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

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

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

2060 

2061 

2062class UserBlock(Base): 

2063 """ 

2064 Table of blocked users 

2065 """ 

2066 

2067 __tablename__ = "user_blocks" 

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

2069 

2070 id = Column(BigInteger, primary_key=True) 

2071 

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

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

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

2075 

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

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

2078 

2079 

2080class APICall(Base): 

2081 """ 

2082 API call logs 

2083 """ 

2084 

2085 __tablename__ = "api_calls" 

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

2087 

2088 id = Column(BigInteger, primary_key=True) 

2089 

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

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

2092 

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

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

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

2096 

2097 # approximate time of the call 

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

2099 

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

2101 method = Column(String, nullable=False) 

2102 

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

2104 status_code = Column(String, nullable=True) 

2105 

2106 # handler duration (excluding serialization, etc) 

2107 duration = Column(Float, nullable=False) 

2108 

2109 # user_id of caller, None means not logged in 

2110 user_id = Column(BigInteger, nullable=True) 

2111 

2112 # sanitized request bytes 

2113 request = Column(Binary, nullable=True) 

2114 

2115 # sanitized response bytes 

2116 response = Column(Binary, nullable=True) 

2117 

2118 # the exception traceback, if any 

2119 traceback = Column(String, nullable=True) 

2120 

2121 

2122class AccountDeletionReason(Base): 

2123 __tablename__ = "account_deletion_reason" 

2124 

2125 id = Column(BigInteger, primary_key=True) 

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

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

2128 reason = Column(String, nullable=True) 

2129 

2130 user = relationship("User")