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

1054 statements  

« prev     ^ index     » next       coverage.py v7.5.0, created at 2024-07-22 17:19 +0000

1import enum 

2from calendar import monthrange 

3from datetime import date 

4 

5from geoalchemy2.types import Geometry 

6from google.protobuf import empty_pb2 

7from sqlalchemy import ( 

8 ARRAY, 

9 BigInteger, 

10 Boolean, 

11 CheckConstraint, 

12 Column, 

13 Date, 

14 DateTime, 

15 Enum, 

16 Float, 

17 ForeignKey, 

18 Index, 

19 Integer, 

20 Interval, 

21 MetaData, 

22 Sequence, 

23 String, 

24 UniqueConstraint, 

25) 

26from sqlalchemy import LargeBinary as Binary 

27from sqlalchemy.dialects.postgresql import TSTZRANGE, ExcludeConstraint 

28from sqlalchemy.ext.associationproxy import association_proxy 

29from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property 

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

31from sqlalchemy.sql import and_, func, text 

32from sqlalchemy.sql import select as sa_select 

33 

34from couchers import urls 

35from couchers.config import config 

36from couchers.constants import ( 

37 DATETIME_INFINITY, 

38 DATETIME_MINUS_INFINITY, 

39 EMAIL_REGEX, 

40 GUIDELINES_VERSION, 

41 PHONE_VERIFICATION_LIFETIME, 

42 SMS_CODE_LIFETIME, 

43 TOS_VERSION, 

44) 

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

46from proto import notification_data_pb2 

47 

48meta = MetaData( 

49 naming_convention={ 

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

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

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

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

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

55 } 

56) 

57 

58Base = declarative_base(metadata=meta) 

59 

60 

61class HostingStatus(enum.Enum): 

62 can_host = enum.auto() 

63 maybe = enum.auto() 

64 cant_host = enum.auto() 

65 

66 

67class MeetupStatus(enum.Enum): 

68 wants_to_meetup = enum.auto() 

69 open_to_meetup = enum.auto() 

70 does_not_want_to_meetup = enum.auto() 

71 

72 

73class SmokingLocation(enum.Enum): 

74 yes = enum.auto() 

75 window = enum.auto() 

76 outside = enum.auto() 

77 no = enum.auto() 

78 

79 

80class SleepingArrangement(enum.Enum): 

81 private = enum.auto() 

82 common = enum.auto() 

83 shared_room = enum.auto() 

84 shared_space = enum.auto() 

85 

86 

87class ParkingDetails(enum.Enum): 

88 free_onsite = enum.auto() 

89 free_offsite = enum.auto() 

90 paid_onsite = enum.auto() 

91 paid_offsite = enum.auto() 

92 

93 

94class TimezoneArea(Base): 

95 __tablename__ = "timezone_areas" 

96 id = Column(BigInteger, primary_key=True) 

97 

98 tzid = Column(String) 

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

100 

101 __table_args__ = ( 

102 Index( 

103 "ix_timezone_areas_geom_tzid", 

104 geom, 

105 tzid, 

106 postgresql_using="gist", 

107 ), 

108 ) 

109 

110 

111class User(Base): 

112 """ 

113 Basic user and profile details 

114 """ 

115 

116 __tablename__ = "users" 

117 

118 id = Column(BigInteger, primary_key=True) 

119 

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

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

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

123 hashed_password = Column(Binary, nullable=False) 

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

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

126 

127 # timezones should always be UTC 

128 ## location 

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

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

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

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

133 geom_radius = Column(Float, nullable=True) 

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

135 city = Column(String, nullable=False) 

136 hometown = Column(String, nullable=True) 

137 

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

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

140 

141 timezone = column_property( 

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

143 deferred=True, 

144 ) 

145 

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

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

148 

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

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

151 # same as above for host requests 

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

153 

154 # display name 

155 name = Column(String, nullable=False) 

156 gender = Column(String, nullable=False) 

157 pronouns = Column(String, nullable=True) 

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

159 

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

161 full_name = Column(String, nullable=True) 

162 

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

164 

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

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

167 

168 # community standing score 

169 community_standing = Column(Float, nullable=True) 

170 

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

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

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

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

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

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

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

178 

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

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

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

182 

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

184 # accidental or they changed their mind 

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

186 undelete_token = Column(String, nullable=True) 

187 # validity of the undelete token 

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

189 

190 # hosting preferences 

191 max_guests = Column(Integer, nullable=True) 

192 last_minute = Column(Boolean, nullable=True) 

193 has_pets = Column(Boolean, nullable=True) 

194 accepts_pets = Column(Boolean, nullable=True) 

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

196 has_kids = Column(Boolean, nullable=True) 

197 accepts_kids = Column(Boolean, nullable=True) 

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

199 has_housemates = Column(Boolean, nullable=True) 

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

201 wheelchair_accessible = Column(Boolean, nullable=True) 

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

203 smokes_at_home = Column(Boolean, nullable=True) 

204 drinking_allowed = Column(Boolean, nullable=True) 

205 drinks_at_home = Column(Boolean, nullable=True) 

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

207 

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

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

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

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

212 parking = Column(Boolean, nullable=True) 

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

214 camping_ok = Column(Boolean, nullable=True) 

215 

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

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

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

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

220 

221 # number of onboarding emails sent 

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

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

224 

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

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

227 # opted out of the newsletter 

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

229 

230 # set to null to receive no digests 

231 digest_frequency = Column(Interval, nullable=True) 

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

233 

234 # for changing their email 

235 new_email = Column(String, nullable=True) 

236 

237 new_email_token = Column(String, nullable=True) 

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

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

240 

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

242 

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

244 # ,-------------------, 

245 # | Start | 

246 # | phone = None | someone else 

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

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

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

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

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

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

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

254 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------' 

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

256 # | | ^ V | | 

257 # ,-----------------, | | ,-------------------, | ,-----------------------, 

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

259 # | phone = xx | | phone = xx | | | phone = xx | 

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

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

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

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

264 # '-----------------' '-------------------' '-----------------------' 

265 

266 # randomly generated Luhn 6-digit string 

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

268 

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

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

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

272 

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

274 # e.g. cus_JjoXHttuZopv0t 

275 # for new US entity 

276 stripe_customer_id = Column(String, nullable=True) 

277 # for old AU entity 

278 stripe_customer_id_old = Column(String, nullable=True) 

279 

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

281 

282 # whether this user has all emails turned off 

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

284 

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

286 

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

288 

289 __table_args__ = ( 

290 # Verified phone numbers should be unique 

291 Index( 

292 "ix_users_unique_phone", 

293 phone, 

294 unique=True, 

295 postgresql_where=phone_verification_verified != None, 

296 ), 

297 Index( 

298 "ix_users_active", 

299 id, 

300 postgresql_where=~is_banned & ~is_deleted, 

301 ), 

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

303 Index( 

304 "ix_users_geom_active", 

305 geom, 

306 id, 

307 username, 

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

309 ), 

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

311 CheckConstraint( 

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

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

314 name="check_new_email_token_state", 

315 ), 

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

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

318 CheckConstraint( 

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

320 name="phone_verified_conditions", 

321 ), 

322 # Email must match our regex 

323 CheckConstraint( 

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

325 name="valid_email", 

326 ), 

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

328 CheckConstraint( 

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

330 name="undelete_nullity", 

331 ), 

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

333 CheckConstraint( 

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

335 name="do_not_email_inactive", 

336 ), 

337 ) 

338 

339 @hybrid_property 

340 def has_completed_profile(self): 

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

342 

343 @has_completed_profile.expression 

344 def has_completed_profile(cls): 

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

346 

347 @hybrid_property 

348 def is_jailed(self): 

349 return ( 

350 (self.accepted_tos < TOS_VERSION) 

351 | (self.accepted_community_guidelines < GUIDELINES_VERSION) 

352 | self.is_missing_location 

353 ) 

354 

355 @hybrid_property 

356 def is_missing_location(self): 

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

358 

359 @hybrid_property 

360 def is_visible(self): 

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

362 

363 @property 

364 def coordinates(self): 

365 if self.geom: 

366 return get_coordinates(self.geom) 

367 else: 

368 return None 

369 

370 @property 

371 def age(self): 

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

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

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

375 safe_birthdate = self.birthdate 

376 if self.birthdate.day > max_day: 

377 safe_birthdate = safe_birthdate.replace(day=max_day) 

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

379 age -= 1 

380 return age 

381 

382 @property 

383 def display_joined(self): 

384 """ 

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

386 """ 

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

388 

389 @property 

390 def display_last_active(self): 

391 """ 

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

393 """ 

394 return last_active_coarsen(self.last_active) 

395 

396 @hybrid_property 

397 def phone_is_verified(self): 

398 return ( 

399 self.phone_verification_verified is not None 

400 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME 

401 ) 

402 

403 @phone_is_verified.expression 

404 def phone_is_verified(cls): 

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

406 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME 

407 ) 

408 

409 @hybrid_property 

410 def phone_code_expired(self): 

411 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME 

412 

413 def __repr__(self): 

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

415 

416 

417class UserBadge(Base): 

418 """ 

419 A badge on a user's profile 

420 """ 

421 

422 __tablename__ = "user_badges" 

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

424 

425 id = Column(BigInteger, primary_key=True) 

426 

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

428 # corresponds to "id" in badges.json 

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

430 

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

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

433 

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

435 

436 

437class StrongVerificationAttemptStatus(enum.Enum): 

438 ## full data states 

439 # completed, this now provides verification for a user 

440 succeeded = enum.auto() 

441 

442 ## no data states 

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

444 in_progress_waiting_on_user_to_open_app = enum.auto() 

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

446 in_progress_waiting_on_user_in_app = enum.auto() 

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

448 in_progress_waiting_on_backend = enum.auto() 

449 # failed at iris end, no data 

450 failed = enum.auto() 

451 

452 ## minimal data states 

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

454 deleted = enum.auto() 

455 

456 

457class PassportSex(enum.Enum): 

458 """ 

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

460 """ 

461 

462 male = enum.auto() 

463 female = enum.auto() 

464 unspecified = enum.auto() 

465 

466 

467class StrongVerificationAttempt(Base): 

468 """ 

469 An attempt to perform strong verification 

470 """ 

471 

472 __tablename__ = "strong_verification_attempts" 

473 

474 # our verification id 

475 id = Column(BigInteger, primary_key=True) 

476 

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

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

479 

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

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

482 

483 status = Column( 

484 Enum(StrongVerificationAttemptStatus), 

485 nullable=False, 

486 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app, 

487 ) 

488 

489 ## full data 

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

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

492 passport_encrypted_data = Column(Binary, nullable=True) 

493 passport_date_of_birth = Column(Date, nullable=True) 

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

495 

496 ## minimal data: this will not be deleted 

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

498 passport_expiry_date = Column(Date, nullable=True) 

499 passport_nationality = Column(String, nullable=True) 

500 # last three characters of the passport number 

501 passport_last_three_document_chars = Column(String, nullable=True) 

502 

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

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

505 

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

507 

508 user = relationship("User") 

509 

510 @hybrid_property 

511 def is_valid(self): 

512 """ 

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

514 """ 

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

516 

517 @is_valid.expression 

518 def is_valid(cls): 

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

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

521 ) 

522 

523 @hybrid_property 

524 def is_visible(self): 

525 return self.status != StrongVerificationAttemptStatus.deleted 

526 

527 @hybrid_method 

528 def matches_birthdate(self, user): 

529 return self.is_valid & (self.passport_date_of_birth == user.birthdate) 

530 

531 @hybrid_method 

532 def matches_gender(self, user): 

533 return self.is_valid & ( 

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

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

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

537 | (user.has_passport_sex_gender_exception == True) 

538 ) 

539 

540 @hybrid_method 

541 def has_strong_verification(self, user): 

542 return self.is_valid & self.matches_birthdate(user) & self.matches_gender(user) 

543 

544 __table_args__ = ( 

545 # used to look up verification status for a user 

546 Index( 

547 "ix_strong_verification_attempts_current", 

548 user_id, 

549 passport_expiry_date, 

550 postgresql_where=status == StrongVerificationAttemptStatus.succeeded, 

551 ), 

552 # each passport can be verified only once 

553 Index( 

554 "ix_strong_verification_attempts_unique_succeeded", 

555 passport_expiry_date, 

556 passport_nationality, 

557 passport_last_three_document_chars, 

558 unique=True, 

559 postgresql_where=( 

560 (status == StrongVerificationAttemptStatus.succeeded) 

561 | (status == StrongVerificationAttemptStatus.deleted) 

562 ), 

563 ), 

564 # full data check 

565 CheckConstraint( 

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

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

568 name="full_data_status", 

569 ), 

570 # minimal data check 

571 CheckConstraint( 

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

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

574 name="minimal_data_status", 

575 ), 

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

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

578 CheckConstraint( 

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

580 name="full_data_implies_minimal_data", 

581 ), 

582 # succeeded implies full data 

583 CheckConstraint( 

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

585 name="succeeded_implies_full_data", 

586 ), 

587 # in_progress/failed implies no_data 

588 CheckConstraint( 

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

590 name="in_progress_failed_iris_implies_no_data", 

591 ), 

592 # deleted implies minimal data 

593 CheckConstraint( 

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

595 name="deleted_implies_minimal_data", 

596 ), 

597 ) 

598 

599 

600class StrongVerificationCallbackEvent(Base): 

601 __tablename__ = "strong_verification_callback_events" 

602 

603 id = Column(BigInteger, primary_key=True) 

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

605 

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

607 

608 iris_status = Column(String, nullable=False) 

609 

610 

611class DonationType(enum.Enum): 

612 one_time = enum.auto() 

613 recurring = enum.auto() 

614 

615 

616class DonationInitiation(Base): 

617 """ 

618 Whenever someone initiaties a donation through the platform 

619 """ 

620 

621 __tablename__ = "donation_initiations" 

622 id = Column(BigInteger, primary_key=True) 

623 

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

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

626 

627 amount = Column(Integer, nullable=False) 

628 stripe_checkout_session_id = Column(String, nullable=False) 

629 

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

631 

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

633 

634 

635class Invoice(Base): 

636 """ 

637 Successful donations, both one off and recurring 

638 

639 Triggered by `payment_intent.succeeded` webhook 

640 """ 

641 

642 __tablename__ = "invoices" 

643 

644 id = Column(BigInteger, primary_key=True) 

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

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

647 

648 amount = Column(Float, nullable=False) 

649 

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

651 stripe_receipt_url = Column(String, nullable=False) 

652 

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

654 

655 

656class LanguageFluency(enum.Enum): 

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

658 beginner = 1 

659 conversational = 2 

660 fluent = 3 

661 

662 

663class LanguageAbility(Base): 

664 __tablename__ = "language_abilities" 

665 __table_args__ = ( 

666 # Users can only have one language ability per language 

667 UniqueConstraint("user_id", "language_code"), 

668 ) 

669 

670 id = Column(BigInteger, primary_key=True) 

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

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

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

674 

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

676 language = relationship("Language") 

677 

678 

679class RegionVisited(Base): 

680 __tablename__ = "regions_visited" 

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

682 

683 id = Column(BigInteger, primary_key=True) 

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

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

686 

687 

688class RegionLived(Base): 

689 __tablename__ = "regions_lived" 

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

691 

692 id = Column(BigInteger, primary_key=True) 

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

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

695 

696 

697class FriendStatus(enum.Enum): 

698 pending = enum.auto() 

699 accepted = enum.auto() 

700 rejected = enum.auto() 

701 cancelled = enum.auto() 

702 

703 

704class FriendRelationship(Base): 

705 """ 

706 Friendship relations between users 

707 

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

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

710 """ 

711 

712 __tablename__ = "friend_relationships" 

713 

714 id = Column(BigInteger, primary_key=True) 

715 

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

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

718 

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

720 

721 # timezones should always be UTC 

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

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

724 

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

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

727 

728 

729class ContributeOption(enum.Enum): 

730 yes = enum.auto() 

731 maybe = enum.auto() 

732 no = enum.auto() 

733 

734 

735class ContributorForm(Base): 

736 """ 

737 Someone filled in the contributor form 

738 """ 

739 

740 __tablename__ = "contributor_forms" 

741 

742 id = Column(BigInteger, primary_key=True) 

743 

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

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

746 

747 ideas = Column(String, nullable=True) 

748 features = Column(String, nullable=True) 

749 experience = Column(String, nullable=True) 

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

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

752 expertise = Column(String, nullable=True) 

753 

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

755 

756 @hybrid_property 

757 def is_filled(self): 

758 """ 

759 Whether the form counts as having been filled 

760 """ 

761 return ( 

762 (self.ideas != None) 

763 | (self.features != None) 

764 | (self.experience != None) 

765 | (self.contribute != None) 

766 | (self.contribute_ways != []) 

767 | (self.expertise != None) 

768 ) 

769 

770 @property 

771 def should_notify(self): 

772 """ 

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

774 

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

776 """ 

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

778 

779 

780class SignupFlow(Base): 

781 """ 

782 Signup flows/incomplete users 

783 

784 Coinciding fields have the same meaning as in User 

785 """ 

786 

787 __tablename__ = "signup_flows" 

788 

789 id = Column(BigInteger, primary_key=True) 

790 

791 # housekeeping 

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

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

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

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

796 email_token = Column(String, nullable=True) 

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

798 

799 ## Basic 

800 name = Column(String, nullable=False) 

801 # TODO: unique across both tables 

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

803 # TODO: invitation, attribution 

804 

805 ## Account 

806 # TODO: unique across both tables 

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

808 hashed_password = Column(Binary, nullable=True) 

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

810 gender = Column(String, nullable=True) 

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

812 city = Column(String, nullable=True) 

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

814 geom_radius = Column(Float, nullable=True) 

815 

816 accepted_tos = Column(Integer, nullable=True) 

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

818 

819 opt_out_of_newsletter = Column(Boolean, nullable=True) 

820 

821 ## Feedback 

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

823 ideas = Column(String, nullable=True) 

824 features = Column(String, nullable=True) 

825 experience = Column(String, nullable=True) 

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

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

828 expertise = Column(String, nullable=True) 

829 

830 @hybrid_property 

831 def token_is_valid(self): 

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

833 

834 @hybrid_property 

835 def account_is_filled(self): 

836 return ( 

837 (self.username != None) 

838 & (self.birthdate != None) 

839 & (self.gender != None) 

840 & (self.hosting_status != None) 

841 & (self.city != None) 

842 & (self.geom != None) 

843 & (self.geom_radius != None) 

844 & (self.accepted_tos != None) 

845 & (self.opt_out_of_newsletter != None) 

846 ) 

847 

848 @hybrid_property 

849 def is_completed(self): 

850 return ( 

851 self.email_verified 

852 & self.account_is_filled 

853 & self.filled_feedback 

854 & (self.accepted_community_guidelines == GUIDELINES_VERSION) 

855 ) 

856 

857 

858class LoginToken(Base): 

859 """ 

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

861 """ 

862 

863 __tablename__ = "login_tokens" 

864 token = Column(String, primary_key=True) 

865 

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

867 

868 # timezones should always be UTC 

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

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

871 

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

873 

874 @hybrid_property 

875 def is_valid(self): 

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

877 

878 def __repr__(self): 

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

880 

881 

882class PasswordResetToken(Base): 

883 __tablename__ = "password_reset_tokens" 

884 token = Column(String, primary_key=True) 

885 

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

887 

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

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

890 

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

892 

893 @hybrid_property 

894 def is_valid(self): 

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

896 

897 def __repr__(self): 

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

899 

900 

901class AccountDeletionToken(Base): 

902 __tablename__ = "account_deletion_tokens" 

903 

904 token = Column(String, primary_key=True) 

905 

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

907 

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

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

910 

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

912 

913 @hybrid_property 

914 def is_valid(self): 

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

916 

917 def __repr__(self): 

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

919 

920 

921class UserSession(Base): 

922 """ 

923 API keys/session cookies for the app 

924 

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

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

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

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

929 site. 

930 

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

932 

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

934 """ 

935 

936 __tablename__ = "sessions" 

937 token = Column(String, primary_key=True) 

938 

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

940 

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

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

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

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

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

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

947 

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

949 long_lived = Column(Boolean, nullable=False) 

950 

951 # the time at which the session was created 

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

953 

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

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

956 

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

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

959 

960 # the last time this session was used 

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

962 

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

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

965 

966 # details of the browser, if available 

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

968 ip_address = Column(String, nullable=True) 

969 user_agent = Column(String, nullable=True) 

970 

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

972 

973 @hybrid_property 

974 def is_valid(self): 

975 """ 

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

977 

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

979 

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

981 """ 

982 return ( 

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

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

985 & (self.deleted == None) 

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

987 ) 

988 

989 

990class Conversation(Base): 

991 """ 

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

993 """ 

994 

995 __tablename__ = "conversations" 

996 

997 id = Column(BigInteger, primary_key=True) 

998 # timezone should always be UTC 

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

1000 

1001 def __repr__(self): 

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

1003 

1004 

1005class GroupChat(Base): 

1006 """ 

1007 Group chat 

1008 """ 

1009 

1010 __tablename__ = "group_chats" 

1011 

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

1013 

1014 title = Column(String, nullable=True) 

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

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

1017 is_dm = Column(Boolean, nullable=False) 

1018 

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

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

1021 

1022 def __repr__(self): 

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

1024 

1025 

1026class GroupChatRole(enum.Enum): 

1027 admin = enum.auto() 

1028 participant = enum.auto() 

1029 

1030 

1031class GroupChatSubscription(Base): 

1032 """ 

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

1034 """ 

1035 

1036 __tablename__ = "group_chat_subscriptions" 

1037 id = Column(BigInteger, primary_key=True) 

1038 

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

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

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

1042 

1043 # timezones should always be UTC 

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

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

1046 

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

1048 

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

1050 

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

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

1053 

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

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

1056 

1057 def muted_display(self): 

1058 """ 

1059 Returns (muted, muted_until) display values: 

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

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

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

1063 """ 

1064 if self.muted_until < now(): 

1065 return (False, None) 

1066 elif self.muted_until == DATETIME_INFINITY: 

1067 return (True, None) 

1068 else: 

1069 return (True, self.muted_until) 

1070 

1071 @hybrid_property 

1072 def is_muted(self): 

1073 return self.muted_until > func.now() 

1074 

1075 def __repr__(self): 

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

1077 

1078 

1079class MessageType(enum.Enum): 

1080 text = enum.auto() 

1081 # e.g. 

1082 # image = 

1083 # emoji = 

1084 # ... 

1085 chat_created = enum.auto() 

1086 chat_edited = enum.auto() 

1087 user_invited = enum.auto() 

1088 user_left = enum.auto() 

1089 user_made_admin = enum.auto() 

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

1091 host_request_status_changed = enum.auto() 

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

1093 

1094 

1095class HostRequestStatus(enum.Enum): 

1096 pending = enum.auto() 

1097 accepted = enum.auto() 

1098 rejected = enum.auto() 

1099 confirmed = enum.auto() 

1100 cancelled = enum.auto() 

1101 

1102 

1103class Message(Base): 

1104 """ 

1105 A message. 

1106 

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

1108 """ 

1109 

1110 __tablename__ = "messages" 

1111 

1112 id = Column(BigInteger, primary_key=True) 

1113 

1114 # which conversation the message belongs in 

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

1116 

1117 # the user that sent the message/command 

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

1119 

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

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

1122 

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

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

1125 

1126 # time sent, timezone should always be UTC 

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

1128 

1129 # the plain-text message text if not control 

1130 text = Column(String, nullable=True) 

1131 

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

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

1134 

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

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

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

1138 

1139 @property 

1140 def is_normal_message(self): 

1141 """ 

1142 There's only one normal type atm, text 

1143 """ 

1144 return self.message_type == MessageType.text 

1145 

1146 def __repr__(self): 

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

1148 

1149 

1150class ContentReport(Base): 

1151 """ 

1152 A piece of content reported to admins 

1153 """ 

1154 

1155 __tablename__ = "content_reports" 

1156 

1157 id = Column(BigInteger, primary_key=True) 

1158 

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

1160 

1161 # the user who reported or flagged the content 

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

1163 

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

1165 reason = Column(String, nullable=False) 

1166 # a short description 

1167 description = Column(String, nullable=False) 

1168 

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

1170 content_ref = Column(String, nullable=False) 

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

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

1173 

1174 # details of the browser, if available 

1175 user_agent = Column(String, nullable=False) 

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

1177 page = Column(String, nullable=False) 

1178 

1179 # see comments above for reporting vs author 

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

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

1182 

1183 

1184class Email(Base): 

1185 """ 

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

1187 """ 

1188 

1189 __tablename__ = "emails" 

1190 

1191 id = Column(String, primary_key=True) 

1192 

1193 # timezone should always be UTC 

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

1195 

1196 sender_name = Column(String, nullable=False) 

1197 sender_email = Column(String, nullable=False) 

1198 

1199 recipient = Column(String, nullable=False) 

1200 subject = Column(String, nullable=False) 

1201 

1202 plain = Column(String, nullable=False) 

1203 html = Column(String, nullable=False) 

1204 

1205 list_unsubscribe_header = Column(String, nullable=True) 

1206 source_data = Column(String, nullable=True) 

1207 

1208 

1209class SMS(Base): 

1210 """ 

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

1212 """ 

1213 

1214 __tablename__ = "smss" 

1215 

1216 id = Column(BigInteger, primary_key=True) 

1217 

1218 # timezone should always be UTC 

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

1220 # AWS message id 

1221 message_id = Column(String, nullable=False) 

1222 

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

1224 sms_sender_id = Column(String, nullable=False) 

1225 number = Column(String, nullable=False) 

1226 message = Column(String, nullable=False) 

1227 

1228 

1229class HostRequest(Base): 

1230 """ 

1231 A request to stay with a host 

1232 """ 

1233 

1234 __tablename__ = "host_requests" 

1235 

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

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

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

1239 

1240 # TODO: proper timezone handling 

1241 timezone = "Etc/UTC" 

1242 

1243 # dates in the timezone above 

1244 from_date = Column(Date, nullable=False) 

1245 to_date = Column(Date, nullable=False) 

1246 

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

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

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

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

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

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

1253 

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

1255 

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

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

1258 

1259 # number of reference reminders sent out 

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

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

1262 

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

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

1265 conversation = relationship("Conversation") 

1266 

1267 @hybrid_property 

1268 def can_write_reference(self): 

1269 return ( 

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

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

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

1273 ) 

1274 

1275 @can_write_reference.expression 

1276 def can_write_reference(cls): 

1277 return ( 

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

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

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

1281 ) 

1282 

1283 def __repr__(self): 

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

1285 

1286 

1287class ReferenceType(enum.Enum): 

1288 friend = enum.auto() 

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

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

1291 

1292 

1293class Reference(Base): 

1294 """ 

1295 Reference from one user to another 

1296 """ 

1297 

1298 __tablename__ = "references" 

1299 

1300 id = Column(BigInteger, primary_key=True) 

1301 # timezone should always be UTC 

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

1303 

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

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

1306 

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

1308 

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

1310 

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

1312 # text that's only visible to mods 

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

1314 

1315 rating = Column(Float, nullable=False) 

1316 was_appropriate = Column(Boolean, nullable=False) 

1317 

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

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

1320 

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

1322 

1323 __table_args__ = ( 

1324 # Rating must be between 0 and 1, inclusive 

1325 CheckConstraint( 

1326 "rating BETWEEN 0 AND 1", 

1327 name="rating_between_0_and_1", 

1328 ), 

1329 # Has host_request_id or it's a friend reference 

1330 CheckConstraint( 

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

1332 name="host_request_id_xor_friend_reference", 

1333 ), 

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

1335 Index( 

1336 "ix_references_unique_friend_reference", 

1337 from_user_id, 

1338 to_user_id, 

1339 reference_type, 

1340 unique=True, 

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

1342 ), 

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

1344 Index( 

1345 "ix_references_unique_per_host_request", 

1346 from_user_id, 

1347 to_user_id, 

1348 host_request_id, 

1349 unique=True, 

1350 postgresql_where=(host_request_id != None), 

1351 ), 

1352 ) 

1353 

1354 @property 

1355 def should_report(self): 

1356 """ 

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

1358 """ 

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

1360 

1361 

1362class InitiatedUpload(Base): 

1363 """ 

1364 Started downloads, not necessarily complete yet. 

1365 """ 

1366 

1367 __tablename__ = "initiated_uploads" 

1368 

1369 key = Column(String, primary_key=True) 

1370 

1371 # timezones should always be UTC 

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

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

1374 

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

1376 

1377 initiator_user = relationship("User") 

1378 

1379 @hybrid_property 

1380 def is_valid(self): 

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

1382 

1383 

1384class Upload(Base): 

1385 """ 

1386 Completed uploads. 

1387 """ 

1388 

1389 __tablename__ = "uploads" 

1390 key = Column(String, primary_key=True) 

1391 

1392 filename = Column(String, nullable=False) 

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

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

1395 

1396 # photo credit, etc 

1397 credit = Column(String, nullable=True) 

1398 

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

1400 

1401 def _url(self, size): 

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

1403 

1404 @property 

1405 def thumbnail_url(self): 

1406 return self._url("thumbnail") 

1407 

1408 @property 

1409 def full_url(self): 

1410 return self._url("full") 

1411 

1412 

1413communities_seq = Sequence("communities_seq") 

1414 

1415 

1416class Node(Base): 

1417 """ 

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

1419 

1420 Administered by the official cluster 

1421 """ 

1422 

1423 __tablename__ = "nodes" 

1424 

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

1426 

1427 # name and description come from official cluster 

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

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

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

1431 

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

1433 

1434 contained_users = relationship( 

1435 "User", 

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

1437 viewonly=True, 

1438 uselist=True, 

1439 ) 

1440 

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

1442 

1443 

1444class Cluster(Base): 

1445 """ 

1446 Cluster, administered grouping of content 

1447 """ 

1448 

1449 __tablename__ = "clusters" 

1450 

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

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

1453 name = Column(String, nullable=False) 

1454 # short description 

1455 description = Column(String, nullable=False) 

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

1457 

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

1459 

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

1461 

1462 official_cluster_for_node = relationship( 

1463 "Node", 

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

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

1466 uselist=False, 

1467 viewonly=True, 

1468 ) 

1469 

1470 parent_node = relationship( 

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

1472 ) 

1473 

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

1475 # all pages 

1476 pages = relationship( 

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

1478 ) 

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

1480 discussions = relationship( 

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

1482 ) 

1483 

1484 # includes also admins 

1485 members = relationship( 

1486 "User", 

1487 lazy="dynamic", 

1488 backref="cluster_memberships", 

1489 secondary="cluster_subscriptions", 

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

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

1492 viewonly=True, 

1493 ) 

1494 

1495 admins = relationship( 

1496 "User", 

1497 lazy="dynamic", 

1498 backref="cluster_adminships", 

1499 secondary="cluster_subscriptions", 

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

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

1502 viewonly=True, 

1503 ) 

1504 

1505 main_page = relationship( 

1506 "Page", 

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

1508 viewonly=True, 

1509 uselist=False, 

1510 ) 

1511 

1512 @property 

1513 def is_leaf(self) -> bool: 

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

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

1516 

1517 __table_args__ = ( 

1518 # Each node can have at most one official cluster 

1519 Index( 

1520 "ix_clusters_owner_parent_node_id_is_official_cluster", 

1521 parent_node_id, 

1522 is_official_cluster, 

1523 unique=True, 

1524 postgresql_where=is_official_cluster, 

1525 ), 

1526 ) 

1527 

1528 

1529class NodeClusterAssociation(Base): 

1530 """ 

1531 NodeClusterAssociation, grouping of nodes 

1532 """ 

1533 

1534 __tablename__ = "node_cluster_associations" 

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

1536 

1537 id = Column(BigInteger, primary_key=True) 

1538 

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

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

1541 

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

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

1544 

1545 

1546class ClusterRole(enum.Enum): 

1547 member = enum.auto() 

1548 admin = enum.auto() 

1549 

1550 

1551class ClusterSubscription(Base): 

1552 """ 

1553 ClusterSubscription of a user 

1554 """ 

1555 

1556 __tablename__ = "cluster_subscriptions" 

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

1558 

1559 id = Column(BigInteger, primary_key=True) 

1560 

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

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

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

1564 

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

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

1567 

1568 

1569class ClusterPageAssociation(Base): 

1570 """ 

1571 pages related to clusters 

1572 """ 

1573 

1574 __tablename__ = "cluster_page_associations" 

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

1576 

1577 id = Column(BigInteger, primary_key=True) 

1578 

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

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

1581 

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

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

1584 

1585 

1586class PageType(enum.Enum): 

1587 main_page = enum.auto() 

1588 place = enum.auto() 

1589 guide = enum.auto() 

1590 

1591 

1592class Page(Base): 

1593 """ 

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

1595 """ 

1596 

1597 __tablename__ = "pages" 

1598 

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

1600 

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

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

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

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

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

1606 

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

1608 

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

1610 

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

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

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

1614 owner_cluster = relationship( 

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

1616 ) 

1617 

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

1619 

1620 __table_args__ = ( 

1621 # Only one of owner_user and owner_cluster should be set 

1622 CheckConstraint( 

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

1624 name="one_owner", 

1625 ), 

1626 # Only clusters can own main pages 

1627 CheckConstraint( 

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

1629 name="main_page_owned_by_cluster", 

1630 ), 

1631 # Each cluster can have at most one main page 

1632 Index( 

1633 "ix_pages_owner_cluster_id_type", 

1634 owner_cluster_id, 

1635 type, 

1636 unique=True, 

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

1638 ), 

1639 ) 

1640 

1641 def __repr__(self): 

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

1643 

1644 

1645class PageVersion(Base): 

1646 """ 

1647 version of page content 

1648 """ 

1649 

1650 __tablename__ = "page_versions" 

1651 

1652 id = Column(BigInteger, primary_key=True) 

1653 

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

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

1656 title = Column(String, nullable=False) 

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

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

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

1660 # the human-readable address 

1661 address = Column(String, nullable=True) 

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

1663 

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

1665 

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

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

1668 photo = relationship("Upload") 

1669 

1670 __table_args__ = ( 

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

1672 CheckConstraint( 

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

1674 name="geom_iff_address", 

1675 ), 

1676 ) 

1677 

1678 @property 

1679 def coordinates(self): 

1680 # returns (lat, lng) or None 

1681 if self.geom: 

1682 return get_coordinates(self.geom) 

1683 else: 

1684 return None 

1685 

1686 def __repr__(self): 

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

1688 

1689 

1690class ClusterEventAssociation(Base): 

1691 """ 

1692 events related to clusters 

1693 """ 

1694 

1695 __tablename__ = "cluster_event_associations" 

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

1697 

1698 id = Column(BigInteger, primary_key=True) 

1699 

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

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

1702 

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

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

1705 

1706 

1707class Event(Base): 

1708 """ 

1709 An event is compose of two parts: 

1710 

1711 * An event template (Event) 

1712 * An occurrence (EventOccurrence) 

1713 

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

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

1716 """ 

1717 

1718 __tablename__ = "events" 

1719 

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

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

1722 

1723 title = Column(String, nullable=False) 

1724 

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

1726 

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

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

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

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

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

1732 

1733 parent_node = relationship( 

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

1735 ) 

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

1737 subscribers = relationship( 

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

1739 ) 

1740 organizers = relationship( 

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

1742 ) 

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

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

1745 owner_cluster = relationship( 

1746 "Cluster", 

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

1748 uselist=False, 

1749 foreign_keys="Event.owner_cluster_id", 

1750 ) 

1751 

1752 __table_args__ = ( 

1753 # Only one of owner_user and owner_cluster should be set 

1754 CheckConstraint( 

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

1756 name="one_owner", 

1757 ), 

1758 ) 

1759 

1760 

1761class EventOccurrence(Base): 

1762 __tablename__ = "event_occurrences" 

1763 

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

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

1766 

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

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

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

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

1771 

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

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

1774 

1775 # a null geom is an online-only event 

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

1777 # physical address, iff geom is not null 

1778 address = Column(String, nullable=True) 

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

1780 link = Column(String, nullable=True) 

1781 

1782 timezone = "Etc/UTC" 

1783 

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

1785 # simplifies database constraints, etc 

1786 during = Column(TSTZRANGE, nullable=False) 

1787 

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

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

1790 

1791 creator_user = relationship( 

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

1793 ) 

1794 event = relationship( 

1795 "Event", 

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

1797 remote_side="Event.id", 

1798 foreign_keys="EventOccurrence.event_id", 

1799 ) 

1800 

1801 photo = relationship("Upload") 

1802 

1803 __table_args__ = ( 

1804 # Geom and address go together 

1805 CheckConstraint( 

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

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

1808 name="geom_iff_address", 

1809 ), 

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

1811 CheckConstraint( 

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

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

1814 name="link_or_geom", 

1815 ), 

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

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

1818 ) 

1819 

1820 @property 

1821 def coordinates(self): 

1822 # returns (lat, lng) or None 

1823 if self.geom: 

1824 return get_coordinates(self.geom) 

1825 else: 

1826 return None 

1827 

1828 @hybrid_property 

1829 def start_time(self): 

1830 return self.during.lower 

1831 

1832 @start_time.expression 

1833 def start_time(cls): 

1834 return func.lower(cls.during) 

1835 

1836 @hybrid_property 

1837 def end_time(self): 

1838 return self.during.upper 

1839 

1840 @end_time.expression 

1841 def end_time(cls): 

1842 return func.upper(cls.during) 

1843 

1844 

1845class EventSubscription(Base): 

1846 """ 

1847 Users' subscriptions to events 

1848 """ 

1849 

1850 __tablename__ = "event_subscriptions" 

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

1852 

1853 id = Column(BigInteger, primary_key=True) 

1854 

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

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

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

1858 

1859 user = relationship("User") 

1860 event = relationship("Event") 

1861 

1862 

1863class EventOrganizer(Base): 

1864 """ 

1865 Organizers for events 

1866 """ 

1867 

1868 __tablename__ = "event_organizers" 

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

1870 

1871 id = Column(BigInteger, primary_key=True) 

1872 

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

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

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

1876 

1877 user = relationship("User") 

1878 event = relationship("Event") 

1879 

1880 

1881class AttendeeStatus(enum.Enum): 

1882 going = enum.auto() 

1883 maybe = enum.auto() 

1884 

1885 

1886class EventOccurrenceAttendee(Base): 

1887 """ 

1888 Attendees for events 

1889 """ 

1890 

1891 __tablename__ = "event_occurrence_attendees" 

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

1893 

1894 id = Column(BigInteger, primary_key=True) 

1895 

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

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

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

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

1900 

1901 user = relationship("User") 

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

1903 

1904 

1905class EventCommunityInviteRequest(Base): 

1906 """ 

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

1908 """ 

1909 

1910 __tablename__ = "event_community_invite_requests" 

1911 

1912 id = Column(BigInteger, primary_key=True) 

1913 

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

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

1916 

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

1918 

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

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

1921 approved = Column(Boolean, nullable=True) 

1922 

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

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

1925 

1926 __table_args__ = ( 

1927 # each user can only request once 

1928 UniqueConstraint("occurrence_id", "user_id"), 

1929 # each event can only have one notification sent out 

1930 Index( 

1931 "ix_event_community_invite_requests_unique", 

1932 occurrence_id, 

1933 unique=True, 

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

1935 ), 

1936 # decided and approved ought to be null simultaneously 

1937 CheckConstraint( 

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

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

1940 name="decided_approved", 

1941 ), 

1942 ) 

1943 

1944 

1945class ClusterDiscussionAssociation(Base): 

1946 """ 

1947 discussions related to clusters 

1948 """ 

1949 

1950 __tablename__ = "cluster_discussion_associations" 

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

1952 

1953 id = Column(BigInteger, primary_key=True) 

1954 

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

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

1957 

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

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

1960 

1961 

1962class Discussion(Base): 

1963 """ 

1964 forum board 

1965 """ 

1966 

1967 __tablename__ = "discussions" 

1968 

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

1970 

1971 title = Column(String, nullable=False) 

1972 content = Column(String, nullable=False) 

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

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

1975 

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

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

1978 

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

1980 

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

1982 

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

1984 

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

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

1987 

1988 

1989class DiscussionSubscription(Base): 

1990 """ 

1991 users subscriptions to discussions 

1992 """ 

1993 

1994 __tablename__ = "discussion_subscriptions" 

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

1996 

1997 id = Column(BigInteger, primary_key=True) 

1998 

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

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

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

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

2003 

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

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

2006 

2007 

2008class Thread(Base): 

2009 """ 

2010 Thread 

2011 """ 

2012 

2013 __tablename__ = "threads" 

2014 

2015 id = Column(BigInteger, primary_key=True) 

2016 

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

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

2019 

2020 

2021class Comment(Base): 

2022 """ 

2023 Comment 

2024 """ 

2025 

2026 __tablename__ = "comments" 

2027 

2028 id = Column(BigInteger, primary_key=True) 

2029 

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

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

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

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

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

2035 

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

2037 

2038 

2039class Reply(Base): 

2040 """ 

2041 Reply 

2042 """ 

2043 

2044 __tablename__ = "replies" 

2045 

2046 id = Column(BigInteger, primary_key=True) 

2047 

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

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

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

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

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

2053 

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

2055 

2056 

2057class BackgroundJobState(enum.Enum): 

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

2059 pending = enum.auto() 

2060 # job complete 

2061 completed = enum.auto() 

2062 # error occured, will be retried 

2063 error = enum.auto() 

2064 # failed too many times, not retrying anymore 

2065 failed = enum.auto() 

2066 

2067 

2068class BackgroundJob(Base): 

2069 """ 

2070 This table implements a queue of background jobs. 

2071 """ 

2072 

2073 __tablename__ = "background_jobs" 

2074 

2075 id = Column(BigInteger, primary_key=True) 

2076 

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

2078 job_type = Column(String, nullable=False) 

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

2080 

2081 # time queued 

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

2083 

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

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

2086 

2087 # used to count number of retries for failed jobs 

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

2089 

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

2091 

2092 # protobuf encoded job payload 

2093 payload = Column(Binary, nullable=False) 

2094 

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

2096 failure_info = Column(String, nullable=True) 

2097 

2098 __table_args__ = ( 

2099 # used in looking up background jobs to attempt 

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

2101 Index( 

2102 "ix_background_jobs_lookup", 

2103 next_attempt_after, 

2104 (max_tries - try_count), 

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

2106 ), 

2107 ) 

2108 

2109 @hybrid_property 

2110 def ready_for_retry(self): 

2111 return ( 

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

2113 & (self.try_count < self.max_tries) 

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

2115 ) 

2116 

2117 def __repr__(self): 

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

2119 

2120 

2121class NotificationDeliveryType(enum.Enum): 

2122 # send push notification to mobile/web 

2123 push = enum.auto() 

2124 # send individual email immediately 

2125 email = enum.auto() 

2126 # send in digest 

2127 digest = enum.auto() 

2128 

2129 

2130dt = NotificationDeliveryType 

2131nd = notification_data_pb2 

2132 

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

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

2135 

2136 

2137class NotificationTopicAction(enum.Enum): 

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

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

2140 self.defaults = defaults 

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

2142 self.user_editable = user_editable 

2143 

2144 self.data_type = data_type 

2145 

2146 def unpack(self): 

2147 return self.topic, self.action 

2148 

2149 @property 

2150 def display(self): 

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

2152 

2153 def __str__(self): 

2154 return self.display 

2155 

2156 # topic, action, default delivery types 

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

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

2159 

2160 # host requests 

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

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

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

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

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

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

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

2168 

2169 # you receive a friend ref 

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

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

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

2173 # ... the surfer 

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

2175 

2176 # you hosted 

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

2178 # you surfed 

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

2180 

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

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

2183 

2184 # group chats 

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

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

2187 

2188 # events 

2189 # approved by mods 

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

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

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

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

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

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

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

2197 

2198 # account settings 

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

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

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

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

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

2204 # reset password 

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

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

2207 

2208 # account deletion 

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

2210 # no more pushing to do 

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

2212 # undeleted 

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

2214 

2215 # admin actions 

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

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

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

2219 

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

2221 

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

2223 

2224 

2225class NotificationPreference(Base): 

2226 __tablename__ = "notification_preferences" 

2227 

2228 id = Column(BigInteger, primary_key=True) 

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

2230 

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

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

2233 deliver = Column(Boolean, nullable=False) 

2234 

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

2236 

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

2238 

2239 

2240class Notification(Base): 

2241 """ 

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

2243 """ 

2244 

2245 __tablename__ = "notifications" 

2246 

2247 id = Column(BigInteger, primary_key=True) 

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

2249 

2250 # recipient user id 

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

2252 

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

2254 key = Column(String, nullable=False) 

2255 

2256 data = Column(Binary, nullable=False) 

2257 

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

2259 

2260 __table_args__ = ( 

2261 # used in looking up which notifications need delivery 

2262 Index( 

2263 "ix_notifications_created", 

2264 created, 

2265 ), 

2266 ) 

2267 

2268 @property 

2269 def topic(self): 

2270 return self.topic_action.topic 

2271 

2272 @property 

2273 def action(self): 

2274 return self.topic_action.action 

2275 

2276 

2277class NotificationDelivery(Base): 

2278 __tablename__ = "notification_deliveries" 

2279 

2280 id = Column(BigInteger, primary_key=True) 

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

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

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

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

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

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

2287 # todo: device id 

2288 # todo: receipt id, etc 

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

2290 

2291 __table_args__ = ( 

2292 UniqueConstraint("notification_id", "delivery_type"), 

2293 # used in looking up which notifications need delivery 

2294 Index( 

2295 "ix_notification_deliveries_delivery_type", 

2296 delivery_type, 

2297 postgresql_where=(delivered != None), 

2298 ), 

2299 Index( 

2300 "ix_notification_deliveries_dt_ni_dnull", 

2301 delivery_type, 

2302 notification_id, 

2303 delivered == None, 

2304 ), 

2305 ) 

2306 

2307 

2308class PushNotificationSubscription(Base): 

2309 __tablename__ = "push_notification_subscriptions" 

2310 

2311 id = Column(BigInteger, primary_key=True) 

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

2313 

2314 # which user this is connected to 

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

2316 

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

2318 # the endpoint 

2319 endpoint = Column(String, nullable=False) 

2320 # the "auth" key 

2321 auth_key = Column(Binary, nullable=False) 

2322 # the "p256dh" key 

2323 p256dh_key = Column(Binary, nullable=False) 

2324 

2325 full_subscription_info = Column(String, nullable=False) 

2326 

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

2328 user_agent = Column(String, nullable=True) 

2329 

2330 # when it was disabled 

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

2332 

2333 user = relationship("User") 

2334 

2335 

2336class PushNotificationDeliveryAttempt(Base): 

2337 __tablename__ = "push_notification_delivery_attempt" 

2338 

2339 id = Column(BigInteger, primary_key=True) 

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

2341 

2342 push_notification_subscription_id = Column( 

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

2344 ) 

2345 

2346 success = Column(Boolean, nullable=False) 

2347 # the HTTP status code, 201 is success 

2348 status_code = Column(Integer, nullable=False) 

2349 

2350 # can be null if it was a success 

2351 response = Column(String, nullable=True) 

2352 

2353 push_notification_subscription = relationship("PushNotificationSubscription") 

2354 

2355 

2356class Language(Base): 

2357 """ 

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

2359 """ 

2360 

2361 __tablename__ = "languages" 

2362 

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

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

2365 

2366 # the english name 

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

2368 

2369 

2370class Region(Base): 

2371 """ 

2372 Table of regions 

2373 """ 

2374 

2375 __tablename__ = "regions" 

2376 

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

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

2379 

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

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

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

2383 

2384 

2385class UserBlock(Base): 

2386 """ 

2387 Table of blocked users 

2388 """ 

2389 

2390 __tablename__ = "user_blocks" 

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

2392 

2393 id = Column(BigInteger, primary_key=True) 

2394 

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

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

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

2398 

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

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

2401 

2402 

2403class APICall(Base): 

2404 """ 

2405 API call logs 

2406 """ 

2407 

2408 __tablename__ = "api_calls" 

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

2410 

2411 id = Column(BigInteger, primary_key=True) 

2412 

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

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

2415 

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

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

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

2419 

2420 # approximate time of the call 

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

2422 

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

2424 method = Column(String, nullable=False) 

2425 

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

2427 status_code = Column(String, nullable=True) 

2428 

2429 # handler duration (excluding serialization, etc) 

2430 duration = Column(Float, nullable=False) 

2431 

2432 # user_id of caller, None means not logged in 

2433 user_id = Column(BigInteger, nullable=True) 

2434 

2435 # sanitized request bytes 

2436 request = Column(Binary, nullable=True) 

2437 

2438 # sanitized response bytes 

2439 response = Column(Binary, nullable=True) 

2440 

2441 # whether response bytes have been truncated 

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

2443 

2444 # the exception traceback, if any 

2445 traceback = Column(String, nullable=True) 

2446 

2447 # human readable perf report 

2448 perf_report = Column(String, nullable=True) 

2449 

2450 # details of the browser, if available 

2451 ip_address = Column(String, nullable=True) 

2452 user_agent = Column(String, nullable=True) 

2453 

2454 

2455class AccountDeletionReason(Base): 

2456 __tablename__ = "account_deletion_reason" 

2457 

2458 id = Column(BigInteger, primary_key=True) 

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

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

2461 reason = Column(String, nullable=True) 

2462 

2463 user = relationship("User")