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

1070 statements  

« prev     ^ index     » next       coverage.py v7.5.0, created at 2024-10-15 13:03 +0000

1import enum 

2 

3from geoalchemy2.types import Geometry 

4from google.protobuf import empty_pb2 

5from sqlalchemy import ( 

6 ARRAY, 

7 BigInteger, 

8 Boolean, 

9 CheckConstraint, 

10 Column, 

11 Date, 

12 DateTime, 

13 Enum, 

14 Float, 

15 ForeignKey, 

16 Index, 

17 Integer, 

18 Interval, 

19 MetaData, 

20 Sequence, 

21 String, 

22 UniqueConstraint, 

23) 

24from sqlalchemy import LargeBinary as Binary 

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

26from sqlalchemy.ext.associationproxy import association_proxy 

27from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property 

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

29from sqlalchemy.sql import and_, func, text 

30from sqlalchemy.sql import select as sa_select 

31 

32from couchers import urls 

33from couchers.config import config 

34from couchers.constants import ( 

35 DATETIME_INFINITY, 

36 DATETIME_MINUS_INFINITY, 

37 EMAIL_REGEX, 

38 GUIDELINES_VERSION, 

39 PHONE_VERIFICATION_LIFETIME, 

40 SMS_CODE_LIFETIME, 

41 TOS_VERSION, 

42) 

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

44from proto import notification_data_pb2 

45 

46meta = MetaData( 

47 naming_convention={ 

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

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

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

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

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

53 } 

54) 

55 

56Base = declarative_base(metadata=meta) 

57 

58 

59class HostingStatus(enum.Enum): 

60 can_host = enum.auto() 

61 maybe = enum.auto() 

62 cant_host = enum.auto() 

63 

64 

65class MeetupStatus(enum.Enum): 

66 wants_to_meetup = enum.auto() 

67 open_to_meetup = enum.auto() 

68 does_not_want_to_meetup = enum.auto() 

69 

70 

71class SmokingLocation(enum.Enum): 

72 yes = enum.auto() 

73 window = enum.auto() 

74 outside = enum.auto() 

75 no = enum.auto() 

76 

77 

78class SleepingArrangement(enum.Enum): 

79 private = enum.auto() 

80 common = enum.auto() 

81 shared_room = enum.auto() 

82 shared_space = enum.auto() 

83 

84 

85class ParkingDetails(enum.Enum): 

86 free_onsite = enum.auto() 

87 free_offsite = enum.auto() 

88 paid_onsite = enum.auto() 

89 paid_offsite = enum.auto() 

90 

91 

92class TimezoneArea(Base): 

93 __tablename__ = "timezone_areas" 

94 id = Column(BigInteger, primary_key=True) 

95 

96 tzid = Column(String) 

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

98 

99 __table_args__ = ( 

100 Index( 

101 "ix_timezone_areas_geom_tzid", 

102 geom, 

103 tzid, 

104 postgresql_using="gist", 

105 ), 

106 ) 

107 

108 

109class User(Base): 

110 """ 

111 Basic user and profile details 

112 """ 

113 

114 __tablename__ = "users" 

115 

116 id = Column(BigInteger, primary_key=True) 

117 

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

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

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

121 hashed_password = Column(Binary, nullable=False) 

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

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

124 

125 # timezones should always be UTC 

126 ## location 

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

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

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

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

131 geom_radius = Column(Float, nullable=True) 

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

133 city = Column(String, nullable=False) 

134 hometown = Column(String, nullable=True) 

135 

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

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

138 

139 timezone = column_property( 

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

141 deferred=True, 

142 ) 

143 

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

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

146 

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

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

149 # same as above for host requests 

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

151 

152 # display name 

153 name = Column(String, nullable=False) 

154 gender = Column(String, nullable=False) 

155 pronouns = Column(String, nullable=True) 

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

157 

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

159 full_name = Column(String, nullable=True) 

160 

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

162 

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

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

165 

166 # community standing score 

167 community_standing = Column(Float, nullable=True) 

168 

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

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

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

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

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

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

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

176 

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

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

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

180 

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

182 # accidental or they changed their mind 

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

184 undelete_token = Column(String, nullable=True) 

185 # validity of the undelete token 

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

187 

188 # hosting preferences 

189 max_guests = Column(Integer, nullable=True) 

190 last_minute = Column(Boolean, nullable=True) 

191 has_pets = Column(Boolean, nullable=True) 

192 accepts_pets = Column(Boolean, nullable=True) 

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

194 has_kids = Column(Boolean, nullable=True) 

195 accepts_kids = Column(Boolean, nullable=True) 

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

197 has_housemates = Column(Boolean, nullable=True) 

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

199 wheelchair_accessible = Column(Boolean, nullable=True) 

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

201 smokes_at_home = Column(Boolean, nullable=True) 

202 drinking_allowed = Column(Boolean, nullable=True) 

203 drinks_at_home = Column(Boolean, nullable=True) 

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

205 

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

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

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

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

210 parking = Column(Boolean, nullable=True) 

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

212 camping_ok = Column(Boolean, nullable=True) 

213 

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

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

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

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

218 

219 # number of onboarding emails sent 

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

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

222 

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

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

225 # opted out of the newsletter 

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

227 

228 # set to null to receive no digests 

229 digest_frequency = Column(Interval, nullable=True) 

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

231 

232 # for changing their email 

233 new_email = Column(String, nullable=True) 

234 

235 new_email_token = Column(String, nullable=True) 

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

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

238 

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

240 

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

242 # ,-------------------, 

243 # | Start | 

244 # | phone = None | someone else 

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

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

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

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

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

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

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

252 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------' 

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

254 # | | ^ V | | 

255 # ,-----------------, | | ,-------------------, | ,-----------------------, 

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

257 # | phone = xx | | phone = xx | | | phone = xx | 

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

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

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

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

262 # '-----------------' '-------------------' '-----------------------' 

263 

264 # randomly generated Luhn 6-digit string 

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

266 

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

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

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

270 

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

272 # e.g. cus_JjoXHttuZopv0t 

273 # for new US entity 

274 stripe_customer_id = Column(String, nullable=True) 

275 # for old AU entity 

276 stripe_customer_id_old = Column(String, nullable=True) 

277 

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

279 

280 # checking for phone verification 

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

282 

283 # whether this user has all emails turned off 

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

285 

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

287 

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

289 

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

291 

292 __table_args__ = ( 

293 # Verified phone numbers should be unique 

294 Index( 

295 "ix_users_unique_phone", 

296 phone, 

297 unique=True, 

298 postgresql_where=phone_verification_verified != None, 

299 ), 

300 Index( 

301 "ix_users_active", 

302 id, 

303 postgresql_where=~is_banned & ~is_deleted, 

304 ), 

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

306 Index( 

307 "ix_users_geom_active", 

308 geom, 

309 id, 

310 username, 

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

312 ), 

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

314 CheckConstraint( 

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

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

317 name="check_new_email_token_state", 

318 ), 

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

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

321 CheckConstraint( 

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

323 name="phone_verified_conditions", 

324 ), 

325 # Email must match our regex 

326 CheckConstraint( 

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

328 name="valid_email", 

329 ), 

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

331 CheckConstraint( 

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

333 name="undelete_nullity", 

334 ), 

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

336 CheckConstraint( 

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

338 name="do_not_email_inactive", 

339 ), 

340 ) 

341 

342 @hybrid_property 

343 def has_completed_profile(self): 

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

345 

346 @has_completed_profile.expression 

347 def has_completed_profile(cls): 

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

349 

350 @hybrid_property 

351 def is_jailed(self): 

352 return ( 

353 (self.accepted_tos < TOS_VERSION) 

354 | (self.accepted_community_guidelines < GUIDELINES_VERSION) 

355 | self.is_missing_location 

356 | (self.mod_notes.where(ModNote.is_pending).count() > 0) 

357 ) 

358 

359 @hybrid_property 

360 def is_missing_location(self): 

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

362 

363 @hybrid_property 

364 def is_visible(self): 

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

366 

367 @property 

368 def coordinates(self): 

369 if self.geom: 

370 return get_coordinates(self.geom) 

371 else: 

372 return None 

373 

374 @property 

375 def display_joined(self): 

376 """ 

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

378 """ 

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

380 

381 @property 

382 def display_last_active(self): 

383 """ 

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

385 """ 

386 return last_active_coarsen(self.last_active) 

387 

388 @hybrid_property 

389 def phone_is_verified(self): 

390 return ( 

391 self.phone_verification_verified is not None 

392 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME 

393 ) 

394 

395 @phone_is_verified.expression 

396 def phone_is_verified(cls): 

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

398 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME 

399 ) 

400 

401 @hybrid_property 

402 def phone_code_expired(self): 

403 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME 

404 

405 def __repr__(self): 

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

407 

408 

409class UserBadge(Base): 

410 """ 

411 A badge on a user's profile 

412 """ 

413 

414 __tablename__ = "user_badges" 

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

416 

417 id = Column(BigInteger, primary_key=True) 

418 

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

420 # corresponds to "id" in badges.json 

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

422 

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

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

425 

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

427 

428 

429class StrongVerificationAttemptStatus(enum.Enum): 

430 ## full data states 

431 # completed, this now provides verification for a user 

432 succeeded = enum.auto() 

433 

434 ## no data states 

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

436 in_progress_waiting_on_user_to_open_app = enum.auto() 

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

438 in_progress_waiting_on_user_in_app = enum.auto() 

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

440 in_progress_waiting_on_backend = enum.auto() 

441 # failed at iris end, no data 

442 failed = enum.auto() 

443 

444 ## minimal data states 

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

446 deleted = enum.auto() 

447 

448 

449class PassportSex(enum.Enum): 

450 """ 

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

452 """ 

453 

454 male = enum.auto() 

455 female = enum.auto() 

456 unspecified = enum.auto() 

457 

458 

459class StrongVerificationAttempt(Base): 

460 """ 

461 An attempt to perform strong verification 

462 """ 

463 

464 __tablename__ = "strong_verification_attempts" 

465 

466 # our verification id 

467 id = Column(BigInteger, primary_key=True) 

468 

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

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

471 

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

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

474 

475 status = Column( 

476 Enum(StrongVerificationAttemptStatus), 

477 nullable=False, 

478 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app, 

479 ) 

480 

481 ## full data 

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

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

484 passport_encrypted_data = Column(Binary, nullable=True) 

485 passport_date_of_birth = Column(Date, nullable=True) 

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

487 

488 ## minimal data: this will not be deleted 

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

490 passport_expiry_date = Column(Date, nullable=True) 

491 passport_nationality = Column(String, nullable=True) 

492 # last three characters of the passport number 

493 passport_last_three_document_chars = Column(String, nullable=True) 

494 

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

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

497 

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

499 

500 user = relationship("User") 

501 

502 @hybrid_property 

503 def is_valid(self): 

504 """ 

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

506 """ 

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

508 

509 @is_valid.expression 

510 def is_valid(cls): 

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

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

513 ) 

514 

515 @hybrid_property 

516 def is_visible(self): 

517 return self.status != StrongVerificationAttemptStatus.deleted 

518 

519 @hybrid_method 

520 def matches_birthdate(self, user): 

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

522 

523 @hybrid_method 

524 def matches_gender(self, user): 

525 return self.is_valid & ( 

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

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

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

529 | (user.has_passport_sex_gender_exception == True) 

530 ) 

531 

532 @hybrid_method 

533 def has_strong_verification(self, user): 

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

535 

536 __table_args__ = ( 

537 # used to look up verification status for a user 

538 Index( 

539 "ix_strong_verification_attempts_current", 

540 user_id, 

541 passport_expiry_date, 

542 postgresql_where=status == StrongVerificationAttemptStatus.succeeded, 

543 ), 

544 # each passport can be verified only once 

545 Index( 

546 "ix_strong_verification_attempts_unique_succeeded", 

547 passport_expiry_date, 

548 passport_nationality, 

549 passport_last_three_document_chars, 

550 unique=True, 

551 postgresql_where=( 

552 (status == StrongVerificationAttemptStatus.succeeded) 

553 | (status == StrongVerificationAttemptStatus.deleted) 

554 ), 

555 ), 

556 # full data check 

557 CheckConstraint( 

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

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

560 name="full_data_status", 

561 ), 

562 # minimal data check 

563 CheckConstraint( 

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

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

566 name="minimal_data_status", 

567 ), 

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

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

570 CheckConstraint( 

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

572 name="full_data_implies_minimal_data", 

573 ), 

574 # succeeded implies full data 

575 CheckConstraint( 

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

577 name="succeeded_implies_full_data", 

578 ), 

579 # in_progress/failed implies no_data 

580 CheckConstraint( 

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

582 name="in_progress_failed_iris_implies_no_data", 

583 ), 

584 # deleted implies minimal data 

585 CheckConstraint( 

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

587 name="deleted_implies_minimal_data", 

588 ), 

589 ) 

590 

591 

592class ModNote(Base): 

593 """ 

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

595 

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

597 """ 

598 

599 __tablename__ = "mod_notes" 

600 id = Column(BigInteger, primary_key=True) 

601 

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

603 

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

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

606 

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

608 internal_id = Column(String, nullable=False) 

609 # the admin that left this note 

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

611 

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

613 

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

615 

616 def __repr__(self): 

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

618 

619 @hybrid_property 

620 def is_pending(self): 

621 return self.acknowledged == None 

622 

623 __table_args__ = ( 

624 # used to look up pending notes 

625 Index( 

626 "ix_mod_notes_unacknowledged", 

627 user_id, 

628 postgresql_where=acknowledged == None, 

629 ), 

630 ) 

631 

632 

633class StrongVerificationCallbackEvent(Base): 

634 __tablename__ = "strong_verification_callback_events" 

635 

636 id = Column(BigInteger, primary_key=True) 

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

638 

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

640 

641 iris_status = Column(String, nullable=False) 

642 

643 

644class DonationType(enum.Enum): 

645 one_time = enum.auto() 

646 recurring = enum.auto() 

647 

648 

649class DonationInitiation(Base): 

650 """ 

651 Whenever someone initiaties a donation through the platform 

652 """ 

653 

654 __tablename__ = "donation_initiations" 

655 id = Column(BigInteger, primary_key=True) 

656 

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

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

659 

660 amount = Column(Integer, nullable=False) 

661 stripe_checkout_session_id = Column(String, nullable=False) 

662 

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

664 

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

666 

667 

668class Invoice(Base): 

669 """ 

670 Successful donations, both one off and recurring 

671 

672 Triggered by `payment_intent.succeeded` webhook 

673 """ 

674 

675 __tablename__ = "invoices" 

676 

677 id = Column(BigInteger, primary_key=True) 

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

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

680 

681 amount = Column(Float, nullable=False) 

682 

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

684 stripe_receipt_url = Column(String, nullable=False) 

685 

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

687 

688 

689class LanguageFluency(enum.Enum): 

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

691 beginner = 1 

692 conversational = 2 

693 fluent = 3 

694 

695 

696class LanguageAbility(Base): 

697 __tablename__ = "language_abilities" 

698 __table_args__ = ( 

699 # Users can only have one language ability per language 

700 UniqueConstraint("user_id", "language_code"), 

701 ) 

702 

703 id = Column(BigInteger, primary_key=True) 

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

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

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

707 

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

709 language = relationship("Language") 

710 

711 

712class RegionVisited(Base): 

713 __tablename__ = "regions_visited" 

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

715 

716 id = Column(BigInteger, primary_key=True) 

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

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

719 

720 

721class RegionLived(Base): 

722 __tablename__ = "regions_lived" 

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

724 

725 id = Column(BigInteger, primary_key=True) 

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

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

728 

729 

730class FriendStatus(enum.Enum): 

731 pending = enum.auto() 

732 accepted = enum.auto() 

733 rejected = enum.auto() 

734 cancelled = enum.auto() 

735 

736 

737class FriendRelationship(Base): 

738 """ 

739 Friendship relations between users 

740 

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

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

743 """ 

744 

745 __tablename__ = "friend_relationships" 

746 

747 id = Column(BigInteger, primary_key=True) 

748 

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

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

751 

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

753 

754 # timezones should always be UTC 

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

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

757 

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

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

760 

761 

762class ContributeOption(enum.Enum): 

763 yes = enum.auto() 

764 maybe = enum.auto() 

765 no = enum.auto() 

766 

767 

768class ContributorForm(Base): 

769 """ 

770 Someone filled in the contributor form 

771 """ 

772 

773 __tablename__ = "contributor_forms" 

774 

775 id = Column(BigInteger, primary_key=True) 

776 

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

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

779 

780 ideas = Column(String, nullable=True) 

781 features = Column(String, nullable=True) 

782 experience = Column(String, nullable=True) 

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

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

785 expertise = Column(String, nullable=True) 

786 

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

788 

789 @hybrid_property 

790 def is_filled(self): 

791 """ 

792 Whether the form counts as having been filled 

793 """ 

794 return ( 

795 (self.ideas != None) 

796 | (self.features != None) 

797 | (self.experience != None) 

798 | (self.contribute != None) 

799 | (self.contribute_ways != []) 

800 | (self.expertise != None) 

801 ) 

802 

803 @property 

804 def should_notify(self): 

805 """ 

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

807 

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

809 """ 

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

811 

812 

813class SignupFlow(Base): 

814 """ 

815 Signup flows/incomplete users 

816 

817 Coinciding fields have the same meaning as in User 

818 """ 

819 

820 __tablename__ = "signup_flows" 

821 

822 id = Column(BigInteger, primary_key=True) 

823 

824 # housekeeping 

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

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

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

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

829 email_token = Column(String, nullable=True) 

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

831 

832 ## Basic 

833 name = Column(String, nullable=False) 

834 # TODO: unique across both tables 

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

836 # TODO: invitation, attribution 

837 

838 ## Account 

839 # TODO: unique across both tables 

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

841 hashed_password = Column(Binary, nullable=True) 

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

843 gender = Column(String, nullable=True) 

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

845 city = Column(String, nullable=True) 

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

847 geom_radius = Column(Float, nullable=True) 

848 

849 accepted_tos = Column(Integer, nullable=True) 

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

851 

852 opt_out_of_newsletter = Column(Boolean, nullable=True) 

853 

854 ## Feedback 

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

856 ideas = Column(String, nullable=True) 

857 features = Column(String, nullable=True) 

858 experience = Column(String, nullable=True) 

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

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

861 expertise = Column(String, nullable=True) 

862 

863 @hybrid_property 

864 def token_is_valid(self): 

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

866 

867 @hybrid_property 

868 def account_is_filled(self): 

869 return ( 

870 (self.username != None) 

871 & (self.birthdate != None) 

872 & (self.gender != None) 

873 & (self.hosting_status != None) 

874 & (self.city != None) 

875 & (self.geom != None) 

876 & (self.geom_radius != None) 

877 & (self.accepted_tos != None) 

878 & (self.opt_out_of_newsletter != None) 

879 ) 

880 

881 @hybrid_property 

882 def is_completed(self): 

883 return ( 

884 self.email_verified 

885 & self.account_is_filled 

886 & self.filled_feedback 

887 & (self.accepted_community_guidelines == GUIDELINES_VERSION) 

888 ) 

889 

890 

891class LoginToken(Base): 

892 """ 

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

894 """ 

895 

896 __tablename__ = "login_tokens" 

897 token = Column(String, primary_key=True) 

898 

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

900 

901 # timezones should always be UTC 

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

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

904 

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

906 

907 @hybrid_property 

908 def is_valid(self): 

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

910 

911 def __repr__(self): 

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

913 

914 

915class PasswordResetToken(Base): 

916 __tablename__ = "password_reset_tokens" 

917 token = Column(String, primary_key=True) 

918 

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

920 

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

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

923 

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

925 

926 @hybrid_property 

927 def is_valid(self): 

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

929 

930 def __repr__(self): 

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

932 

933 

934class AccountDeletionToken(Base): 

935 __tablename__ = "account_deletion_tokens" 

936 

937 token = Column(String, primary_key=True) 

938 

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

940 

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

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

943 

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

945 

946 @hybrid_property 

947 def is_valid(self): 

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

949 

950 def __repr__(self): 

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

952 

953 

954class UserActivity(Base): 

955 """ 

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

957 

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

959 """ 

960 

961 __tablename__ = "user_activity" 

962 

963 id = Column(BigInteger, primary_key=True) 

964 

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

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

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

968 

969 # details of the browser, if available 

970 ip_address = Column(INET, nullable=True) 

971 user_agent = Column(String, nullable=True) 

972 

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

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

975 

976 __table_args__ = ( 

977 # helps look up this tuple quickly 

978 Index( 

979 "ix_user_activity_user_id_period_ip_address_user_agent", 

980 user_id, 

981 period, 

982 ip_address, 

983 user_agent, 

984 unique=True, 

985 ), 

986 ) 

987 

988 

989class UserSession(Base): 

990 """ 

991 API keys/session cookies for the app 

992 

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

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

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

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

997 site. 

998 

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

1000 

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

1002 """ 

1003 

1004 __tablename__ = "sessions" 

1005 token = Column(String, primary_key=True) 

1006 

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

1008 

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

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

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

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

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

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

1015 

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

1017 long_lived = Column(Boolean, nullable=False) 

1018 

1019 # the time at which the session was created 

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

1021 

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

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

1024 

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

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

1027 

1028 # the last time this session was used 

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

1030 

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

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

1033 

1034 # details of the browser, if available 

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

1036 ip_address = Column(String, nullable=True) 

1037 user_agent = Column(String, nullable=True) 

1038 

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

1040 

1041 @hybrid_property 

1042 def is_valid(self): 

1043 """ 

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

1045 

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

1047 

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

1049 """ 

1050 return ( 

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

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

1053 & (self.deleted == None) 

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

1055 ) 

1056 

1057 

1058class Conversation(Base): 

1059 """ 

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

1061 """ 

1062 

1063 __tablename__ = "conversations" 

1064 

1065 id = Column(BigInteger, primary_key=True) 

1066 # timezone should always be UTC 

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

1068 

1069 def __repr__(self): 

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

1071 

1072 

1073class GroupChat(Base): 

1074 """ 

1075 Group chat 

1076 """ 

1077 

1078 __tablename__ = "group_chats" 

1079 

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

1081 

1082 title = Column(String, nullable=True) 

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

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

1085 is_dm = Column(Boolean, nullable=False) 

1086 

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

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

1089 

1090 def __repr__(self): 

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

1092 

1093 

1094class GroupChatRole(enum.Enum): 

1095 admin = enum.auto() 

1096 participant = enum.auto() 

1097 

1098 

1099class GroupChatSubscription(Base): 

1100 """ 

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

1102 """ 

1103 

1104 __tablename__ = "group_chat_subscriptions" 

1105 id = Column(BigInteger, primary_key=True) 

1106 

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

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

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

1110 

1111 # timezones should always be UTC 

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

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

1114 

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

1116 

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

1118 

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

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

1121 

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

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

1124 

1125 def muted_display(self): 

1126 """ 

1127 Returns (muted, muted_until) display values: 

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

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

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

1131 """ 

1132 if self.muted_until < now(): 

1133 return (False, None) 

1134 elif self.muted_until == DATETIME_INFINITY: 

1135 return (True, None) 

1136 else: 

1137 return (True, self.muted_until) 

1138 

1139 @hybrid_property 

1140 def is_muted(self): 

1141 return self.muted_until > func.now() 

1142 

1143 def __repr__(self): 

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

1145 

1146 

1147class MessageType(enum.Enum): 

1148 text = enum.auto() 

1149 # e.g. 

1150 # image = 

1151 # emoji = 

1152 # ... 

1153 chat_created = enum.auto() 

1154 chat_edited = enum.auto() 

1155 user_invited = enum.auto() 

1156 user_left = enum.auto() 

1157 user_made_admin = enum.auto() 

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

1159 host_request_status_changed = enum.auto() 

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

1161 

1162 

1163class HostRequestStatus(enum.Enum): 

1164 pending = enum.auto() 

1165 accepted = enum.auto() 

1166 rejected = enum.auto() 

1167 confirmed = enum.auto() 

1168 cancelled = enum.auto() 

1169 

1170 

1171class Message(Base): 

1172 """ 

1173 A message. 

1174 

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

1176 """ 

1177 

1178 __tablename__ = "messages" 

1179 

1180 id = Column(BigInteger, primary_key=True) 

1181 

1182 # which conversation the message belongs in 

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

1184 

1185 # the user that sent the message/command 

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

1187 

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

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

1190 

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

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

1193 

1194 # time sent, timezone should always be UTC 

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

1196 

1197 # the plain-text message text if not control 

1198 text = Column(String, nullable=True) 

1199 

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

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

1202 

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

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

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

1206 

1207 @property 

1208 def is_normal_message(self): 

1209 """ 

1210 There's only one normal type atm, text 

1211 """ 

1212 return self.message_type == MessageType.text 

1213 

1214 def __repr__(self): 

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

1216 

1217 

1218class ContentReport(Base): 

1219 """ 

1220 A piece of content reported to admins 

1221 """ 

1222 

1223 __tablename__ = "content_reports" 

1224 

1225 id = Column(BigInteger, primary_key=True) 

1226 

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

1228 

1229 # the user who reported or flagged the content 

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

1231 

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

1233 reason = Column(String, nullable=False) 

1234 # a short description 

1235 description = Column(String, nullable=False) 

1236 

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

1238 content_ref = Column(String, nullable=False) 

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

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

1241 

1242 # details of the browser, if available 

1243 user_agent = Column(String, nullable=False) 

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

1245 page = Column(String, nullable=False) 

1246 

1247 # see comments above for reporting vs author 

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

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

1250 

1251 

1252class Email(Base): 

1253 """ 

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

1255 """ 

1256 

1257 __tablename__ = "emails" 

1258 

1259 id = Column(String, primary_key=True) 

1260 

1261 # timezone should always be UTC 

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

1263 

1264 sender_name = Column(String, nullable=False) 

1265 sender_email = Column(String, nullable=False) 

1266 

1267 recipient = Column(String, nullable=False) 

1268 subject = Column(String, nullable=False) 

1269 

1270 plain = Column(String, nullable=False) 

1271 html = Column(String, nullable=False) 

1272 

1273 list_unsubscribe_header = Column(String, nullable=True) 

1274 source_data = Column(String, nullable=True) 

1275 

1276 

1277class SMS(Base): 

1278 """ 

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

1280 """ 

1281 

1282 __tablename__ = "smss" 

1283 

1284 id = Column(BigInteger, primary_key=True) 

1285 

1286 # timezone should always be UTC 

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

1288 # AWS message id 

1289 message_id = Column(String, nullable=False) 

1290 

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

1292 sms_sender_id = Column(String, nullable=False) 

1293 number = Column(String, nullable=False) 

1294 message = Column(String, nullable=False) 

1295 

1296 

1297class HostRequest(Base): 

1298 """ 

1299 A request to stay with a host 

1300 """ 

1301 

1302 __tablename__ = "host_requests" 

1303 

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

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

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

1307 

1308 # TODO: proper timezone handling 

1309 timezone = "Etc/UTC" 

1310 

1311 # dates in the timezone above 

1312 from_date = Column(Date, nullable=False) 

1313 to_date = Column(Date, nullable=False) 

1314 

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

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

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

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

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

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

1321 

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

1323 

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

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

1326 

1327 # number of reference reminders sent out 

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

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

1330 

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

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

1333 conversation = relationship("Conversation") 

1334 

1335 @hybrid_property 

1336 def can_write_reference(self): 

1337 return ( 

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

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

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

1341 ) 

1342 

1343 @can_write_reference.expression 

1344 def can_write_reference(cls): 

1345 return ( 

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

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

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

1349 ) 

1350 

1351 def __repr__(self): 

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

1353 

1354 

1355class ReferenceType(enum.Enum): 

1356 friend = enum.auto() 

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

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

1359 

1360 

1361class Reference(Base): 

1362 """ 

1363 Reference from one user to another 

1364 """ 

1365 

1366 __tablename__ = "references" 

1367 

1368 id = Column(BigInteger, primary_key=True) 

1369 # timezone should always be UTC 

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

1371 

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

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

1374 

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

1376 

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

1378 

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

1380 # text that's only visible to mods 

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

1382 

1383 rating = Column(Float, nullable=False) 

1384 was_appropriate = Column(Boolean, nullable=False) 

1385 

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

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

1388 

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

1390 

1391 __table_args__ = ( 

1392 # Rating must be between 0 and 1, inclusive 

1393 CheckConstraint( 

1394 "rating BETWEEN 0 AND 1", 

1395 name="rating_between_0_and_1", 

1396 ), 

1397 # Has host_request_id or it's a friend reference 

1398 CheckConstraint( 

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

1400 name="host_request_id_xor_friend_reference", 

1401 ), 

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

1403 Index( 

1404 "ix_references_unique_friend_reference", 

1405 from_user_id, 

1406 to_user_id, 

1407 reference_type, 

1408 unique=True, 

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

1410 ), 

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

1412 Index( 

1413 "ix_references_unique_per_host_request", 

1414 from_user_id, 

1415 to_user_id, 

1416 host_request_id, 

1417 unique=True, 

1418 postgresql_where=(host_request_id != None), 

1419 ), 

1420 ) 

1421 

1422 @property 

1423 def should_report(self): 

1424 """ 

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

1426 """ 

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

1428 

1429 

1430class InitiatedUpload(Base): 

1431 """ 

1432 Started downloads, not necessarily complete yet. 

1433 """ 

1434 

1435 __tablename__ = "initiated_uploads" 

1436 

1437 key = Column(String, primary_key=True) 

1438 

1439 # timezones should always be UTC 

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

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

1442 

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

1444 

1445 initiator_user = relationship("User") 

1446 

1447 @hybrid_property 

1448 def is_valid(self): 

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

1450 

1451 

1452class Upload(Base): 

1453 """ 

1454 Completed uploads. 

1455 """ 

1456 

1457 __tablename__ = "uploads" 

1458 key = Column(String, primary_key=True) 

1459 

1460 filename = Column(String, nullable=False) 

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

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

1463 

1464 # photo credit, etc 

1465 credit = Column(String, nullable=True) 

1466 

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

1468 

1469 def _url(self, size): 

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

1471 

1472 @property 

1473 def thumbnail_url(self): 

1474 return self._url("thumbnail") 

1475 

1476 @property 

1477 def full_url(self): 

1478 return self._url("full") 

1479 

1480 

1481communities_seq = Sequence("communities_seq") 

1482 

1483 

1484class Node(Base): 

1485 """ 

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

1487 

1488 Administered by the official cluster 

1489 """ 

1490 

1491 __tablename__ = "nodes" 

1492 

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

1494 

1495 # name and description come from official cluster 

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

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

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

1499 

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

1501 

1502 contained_users = relationship( 

1503 "User", 

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

1505 viewonly=True, 

1506 uselist=True, 

1507 ) 

1508 

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

1510 

1511 

1512class Cluster(Base): 

1513 """ 

1514 Cluster, administered grouping of content 

1515 """ 

1516 

1517 __tablename__ = "clusters" 

1518 

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

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

1521 name = Column(String, nullable=False) 

1522 # short description 

1523 description = Column(String, nullable=False) 

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

1525 

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

1527 

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

1529 

1530 official_cluster_for_node = relationship( 

1531 "Node", 

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

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

1534 uselist=False, 

1535 viewonly=True, 

1536 ) 

1537 

1538 parent_node = relationship( 

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

1540 ) 

1541 

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

1543 # all pages 

1544 pages = relationship( 

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

1546 ) 

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

1548 discussions = relationship( 

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

1550 ) 

1551 

1552 # includes also admins 

1553 members = relationship( 

1554 "User", 

1555 lazy="dynamic", 

1556 backref="cluster_memberships", 

1557 secondary="cluster_subscriptions", 

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

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

1560 viewonly=True, 

1561 ) 

1562 

1563 admins = relationship( 

1564 "User", 

1565 lazy="dynamic", 

1566 backref="cluster_adminships", 

1567 secondary="cluster_subscriptions", 

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

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

1570 viewonly=True, 

1571 ) 

1572 

1573 main_page = relationship( 

1574 "Page", 

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

1576 viewonly=True, 

1577 uselist=False, 

1578 ) 

1579 

1580 @property 

1581 def is_leaf(self) -> bool: 

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

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

1584 

1585 __table_args__ = ( 

1586 # Each node can have at most one official cluster 

1587 Index( 

1588 "ix_clusters_owner_parent_node_id_is_official_cluster", 

1589 parent_node_id, 

1590 is_official_cluster, 

1591 unique=True, 

1592 postgresql_where=is_official_cluster, 

1593 ), 

1594 ) 

1595 

1596 

1597class NodeClusterAssociation(Base): 

1598 """ 

1599 NodeClusterAssociation, grouping of nodes 

1600 """ 

1601 

1602 __tablename__ = "node_cluster_associations" 

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

1604 

1605 id = Column(BigInteger, primary_key=True) 

1606 

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

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

1609 

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

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

1612 

1613 

1614class ClusterRole(enum.Enum): 

1615 member = enum.auto() 

1616 admin = enum.auto() 

1617 

1618 

1619class ClusterSubscription(Base): 

1620 """ 

1621 ClusterSubscription of a user 

1622 """ 

1623 

1624 __tablename__ = "cluster_subscriptions" 

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

1626 

1627 id = Column(BigInteger, primary_key=True) 

1628 

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

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

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

1632 

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

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

1635 

1636 

1637class ClusterPageAssociation(Base): 

1638 """ 

1639 pages related to clusters 

1640 """ 

1641 

1642 __tablename__ = "cluster_page_associations" 

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

1644 

1645 id = Column(BigInteger, primary_key=True) 

1646 

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

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

1649 

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

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

1652 

1653 

1654class PageType(enum.Enum): 

1655 main_page = enum.auto() 

1656 place = enum.auto() 

1657 guide = enum.auto() 

1658 

1659 

1660class Page(Base): 

1661 """ 

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

1663 """ 

1664 

1665 __tablename__ = "pages" 

1666 

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

1668 

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

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

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

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

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

1674 

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

1676 

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

1678 

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

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

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

1682 owner_cluster = relationship( 

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

1684 ) 

1685 

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

1687 

1688 __table_args__ = ( 

1689 # Only one of owner_user and owner_cluster should be set 

1690 CheckConstraint( 

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

1692 name="one_owner", 

1693 ), 

1694 # Only clusters can own main pages 

1695 CheckConstraint( 

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

1697 name="main_page_owned_by_cluster", 

1698 ), 

1699 # Each cluster can have at most one main page 

1700 Index( 

1701 "ix_pages_owner_cluster_id_type", 

1702 owner_cluster_id, 

1703 type, 

1704 unique=True, 

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

1706 ), 

1707 ) 

1708 

1709 def __repr__(self): 

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

1711 

1712 

1713class PageVersion(Base): 

1714 """ 

1715 version of page content 

1716 """ 

1717 

1718 __tablename__ = "page_versions" 

1719 

1720 id = Column(BigInteger, primary_key=True) 

1721 

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

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

1724 title = Column(String, nullable=False) 

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

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

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

1728 # the human-readable address 

1729 address = Column(String, nullable=True) 

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

1731 

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

1733 

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

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

1736 photo = relationship("Upload") 

1737 

1738 __table_args__ = ( 

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

1740 CheckConstraint( 

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

1742 name="geom_iff_address", 

1743 ), 

1744 ) 

1745 

1746 @property 

1747 def coordinates(self): 

1748 # returns (lat, lng) or None 

1749 if self.geom: 

1750 return get_coordinates(self.geom) 

1751 else: 

1752 return None 

1753 

1754 def __repr__(self): 

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

1756 

1757 

1758class ClusterEventAssociation(Base): 

1759 """ 

1760 events related to clusters 

1761 """ 

1762 

1763 __tablename__ = "cluster_event_associations" 

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

1765 

1766 id = Column(BigInteger, primary_key=True) 

1767 

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

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

1770 

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

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

1773 

1774 

1775class Event(Base): 

1776 """ 

1777 An event is compose of two parts: 

1778 

1779 * An event template (Event) 

1780 * An occurrence (EventOccurrence) 

1781 

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

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

1784 """ 

1785 

1786 __tablename__ = "events" 

1787 

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

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

1790 

1791 title = Column(String, nullable=False) 

1792 

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

1794 

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

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

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

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

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

1800 

1801 parent_node = relationship( 

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

1803 ) 

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

1805 subscribers = relationship( 

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

1807 ) 

1808 organizers = relationship( 

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

1810 ) 

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

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

1813 owner_cluster = relationship( 

1814 "Cluster", 

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

1816 uselist=False, 

1817 foreign_keys="Event.owner_cluster_id", 

1818 ) 

1819 

1820 __table_args__ = ( 

1821 # Only one of owner_user and owner_cluster should be set 

1822 CheckConstraint( 

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

1824 name="one_owner", 

1825 ), 

1826 ) 

1827 

1828 

1829class EventOccurrence(Base): 

1830 __tablename__ = "event_occurrences" 

1831 

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

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

1834 

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

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

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

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

1839 

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

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

1842 

1843 # a null geom is an online-only event 

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

1845 # physical address, iff geom is not null 

1846 address = Column(String, nullable=True) 

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

1848 link = Column(String, nullable=True) 

1849 

1850 timezone = "Etc/UTC" 

1851 

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

1853 # simplifies database constraints, etc 

1854 during = Column(TSTZRANGE, nullable=False) 

1855 

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

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

1858 

1859 creator_user = relationship( 

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

1861 ) 

1862 event = relationship( 

1863 "Event", 

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

1865 remote_side="Event.id", 

1866 foreign_keys="EventOccurrence.event_id", 

1867 ) 

1868 

1869 photo = relationship("Upload") 

1870 

1871 __table_args__ = ( 

1872 # Geom and address go together 

1873 CheckConstraint( 

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

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

1876 name="geom_iff_address", 

1877 ), 

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

1879 CheckConstraint( 

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

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

1882 name="link_or_geom", 

1883 ), 

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

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

1886 ) 

1887 

1888 @property 

1889 def coordinates(self): 

1890 # returns (lat, lng) or None 

1891 if self.geom: 

1892 return get_coordinates(self.geom) 

1893 else: 

1894 return None 

1895 

1896 @hybrid_property 

1897 def start_time(self): 

1898 return self.during.lower 

1899 

1900 @start_time.expression 

1901 def start_time(cls): 

1902 return func.lower(cls.during) 

1903 

1904 @hybrid_property 

1905 def end_time(self): 

1906 return self.during.upper 

1907 

1908 @end_time.expression 

1909 def end_time(cls): 

1910 return func.upper(cls.during) 

1911 

1912 

1913class EventSubscription(Base): 

1914 """ 

1915 Users' subscriptions to events 

1916 """ 

1917 

1918 __tablename__ = "event_subscriptions" 

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

1920 

1921 id = Column(BigInteger, primary_key=True) 

1922 

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

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

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

1926 

1927 user = relationship("User") 

1928 event = relationship("Event") 

1929 

1930 

1931class EventOrganizer(Base): 

1932 """ 

1933 Organizers for events 

1934 """ 

1935 

1936 __tablename__ = "event_organizers" 

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

1938 

1939 id = Column(BigInteger, primary_key=True) 

1940 

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

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

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

1944 

1945 user = relationship("User") 

1946 event = relationship("Event") 

1947 

1948 

1949class AttendeeStatus(enum.Enum): 

1950 going = enum.auto() 

1951 maybe = enum.auto() 

1952 

1953 

1954class EventOccurrenceAttendee(Base): 

1955 """ 

1956 Attendees for events 

1957 """ 

1958 

1959 __tablename__ = "event_occurrence_attendees" 

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

1961 

1962 id = Column(BigInteger, primary_key=True) 

1963 

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

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

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

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

1968 

1969 user = relationship("User") 

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

1971 

1972 

1973class EventCommunityInviteRequest(Base): 

1974 """ 

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

1976 """ 

1977 

1978 __tablename__ = "event_community_invite_requests" 

1979 

1980 id = Column(BigInteger, primary_key=True) 

1981 

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

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

1984 

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

1986 

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

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

1989 approved = Column(Boolean, nullable=True) 

1990 

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

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

1993 

1994 __table_args__ = ( 

1995 # each user can only request once 

1996 UniqueConstraint("occurrence_id", "user_id"), 

1997 # each event can only have one notification sent out 

1998 Index( 

1999 "ix_event_community_invite_requests_unique", 

2000 occurrence_id, 

2001 unique=True, 

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

2003 ), 

2004 # decided and approved ought to be null simultaneously 

2005 CheckConstraint( 

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

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

2008 name="decided_approved", 

2009 ), 

2010 ) 

2011 

2012 

2013class ClusterDiscussionAssociation(Base): 

2014 """ 

2015 discussions related to clusters 

2016 """ 

2017 

2018 __tablename__ = "cluster_discussion_associations" 

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

2020 

2021 id = Column(BigInteger, primary_key=True) 

2022 

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

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

2025 

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

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

2028 

2029 

2030class Discussion(Base): 

2031 """ 

2032 forum board 

2033 """ 

2034 

2035 __tablename__ = "discussions" 

2036 

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

2038 

2039 title = Column(String, nullable=False) 

2040 content = Column(String, nullable=False) 

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

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

2043 

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

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

2046 

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

2048 

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

2050 

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

2052 

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

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

2055 

2056 

2057class DiscussionSubscription(Base): 

2058 """ 

2059 users subscriptions to discussions 

2060 """ 

2061 

2062 __tablename__ = "discussion_subscriptions" 

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

2064 

2065 id = Column(BigInteger, primary_key=True) 

2066 

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

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

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

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

2071 

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

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

2074 

2075 

2076class Thread(Base): 

2077 """ 

2078 Thread 

2079 """ 

2080 

2081 __tablename__ = "threads" 

2082 

2083 id = Column(BigInteger, primary_key=True) 

2084 

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

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

2087 

2088 

2089class Comment(Base): 

2090 """ 

2091 Comment 

2092 """ 

2093 

2094 __tablename__ = "comments" 

2095 

2096 id = Column(BigInteger, primary_key=True) 

2097 

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

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

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

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

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

2103 

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

2105 

2106 

2107class Reply(Base): 

2108 """ 

2109 Reply 

2110 """ 

2111 

2112 __tablename__ = "replies" 

2113 

2114 id = Column(BigInteger, primary_key=True) 

2115 

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

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

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

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

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

2121 

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

2123 

2124 

2125class BackgroundJobState(enum.Enum): 

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

2127 pending = enum.auto() 

2128 # job complete 

2129 completed = enum.auto() 

2130 # error occured, will be retried 

2131 error = enum.auto() 

2132 # failed too many times, not retrying anymore 

2133 failed = enum.auto() 

2134 

2135 

2136class BackgroundJob(Base): 

2137 """ 

2138 This table implements a queue of background jobs. 

2139 """ 

2140 

2141 __tablename__ = "background_jobs" 

2142 

2143 id = Column(BigInteger, primary_key=True) 

2144 

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

2146 job_type = Column(String, nullable=False) 

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

2148 

2149 # time queued 

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

2151 

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

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

2154 

2155 # used to count number of retries for failed jobs 

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

2157 

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

2159 

2160 # protobuf encoded job payload 

2161 payload = Column(Binary, nullable=False) 

2162 

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

2164 failure_info = Column(String, nullable=True) 

2165 

2166 __table_args__ = ( 

2167 # used in looking up background jobs to attempt 

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

2169 Index( 

2170 "ix_background_jobs_lookup", 

2171 next_attempt_after, 

2172 (max_tries - try_count), 

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

2174 ), 

2175 ) 

2176 

2177 @hybrid_property 

2178 def ready_for_retry(self): 

2179 return ( 

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

2181 & (self.try_count < self.max_tries) 

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

2183 ) 

2184 

2185 def __repr__(self): 

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

2187 

2188 

2189class NotificationDeliveryType(enum.Enum): 

2190 # send push notification to mobile/web 

2191 push = enum.auto() 

2192 # send individual email immediately 

2193 email = enum.auto() 

2194 # send in digest 

2195 digest = enum.auto() 

2196 

2197 

2198dt = NotificationDeliveryType 

2199nd = notification_data_pb2 

2200 

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

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

2203 

2204 

2205class NotificationTopicAction(enum.Enum): 

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

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

2208 self.defaults = defaults 

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

2210 self.user_editable = user_editable 

2211 

2212 self.data_type = data_type 

2213 

2214 def unpack(self): 

2215 return self.topic, self.action 

2216 

2217 @property 

2218 def display(self): 

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

2220 

2221 def __str__(self): 

2222 return self.display 

2223 

2224 # topic, action, default delivery types 

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

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

2227 

2228 # host requests 

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

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

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

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

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

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

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

2236 

2237 # you receive a friend ref 

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

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

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

2241 # ... the surfer 

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

2243 

2244 # you hosted 

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

2246 # you surfed 

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

2248 

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

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

2251 

2252 # group chats 

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

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

2255 

2256 # events 

2257 # approved by mods 

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

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

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

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

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

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

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

2265 

2266 # account settings 

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

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

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

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

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

2272 # reset password 

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

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

2275 

2276 # account deletion 

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

2278 # no more pushing to do 

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

2280 # undeleted 

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

2282 

2283 # admin actions 

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

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

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

2287 

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

2289 

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

2291 

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

2293 

2294 

2295class NotificationPreference(Base): 

2296 __tablename__ = "notification_preferences" 

2297 

2298 id = Column(BigInteger, primary_key=True) 

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

2300 

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

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

2303 deliver = Column(Boolean, nullable=False) 

2304 

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

2306 

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

2308 

2309 

2310class Notification(Base): 

2311 """ 

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

2313 """ 

2314 

2315 __tablename__ = "notifications" 

2316 

2317 id = Column(BigInteger, primary_key=True) 

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

2319 

2320 # recipient user id 

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

2322 

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

2324 key = Column(String, nullable=False) 

2325 

2326 data = Column(Binary, nullable=False) 

2327 

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

2329 

2330 __table_args__ = ( 

2331 # used in looking up which notifications need delivery 

2332 Index( 

2333 "ix_notifications_created", 

2334 created, 

2335 ), 

2336 ) 

2337 

2338 @property 

2339 def topic(self): 

2340 return self.topic_action.topic 

2341 

2342 @property 

2343 def action(self): 

2344 return self.topic_action.action 

2345 

2346 

2347class NotificationDelivery(Base): 

2348 __tablename__ = "notification_deliveries" 

2349 

2350 id = Column(BigInteger, primary_key=True) 

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

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

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

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

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

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

2357 # todo: device id 

2358 # todo: receipt id, etc 

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

2360 

2361 __table_args__ = ( 

2362 UniqueConstraint("notification_id", "delivery_type"), 

2363 # used in looking up which notifications need delivery 

2364 Index( 

2365 "ix_notification_deliveries_delivery_type", 

2366 delivery_type, 

2367 postgresql_where=(delivered != None), 

2368 ), 

2369 Index( 

2370 "ix_notification_deliveries_dt_ni_dnull", 

2371 delivery_type, 

2372 notification_id, 

2373 delivered == None, 

2374 ), 

2375 ) 

2376 

2377 

2378class PushNotificationSubscription(Base): 

2379 __tablename__ = "push_notification_subscriptions" 

2380 

2381 id = Column(BigInteger, primary_key=True) 

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

2383 

2384 # which user this is connected to 

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

2386 

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

2388 # the endpoint 

2389 endpoint = Column(String, nullable=False) 

2390 # the "auth" key 

2391 auth_key = Column(Binary, nullable=False) 

2392 # the "p256dh" key 

2393 p256dh_key = Column(Binary, nullable=False) 

2394 

2395 full_subscription_info = Column(String, nullable=False) 

2396 

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

2398 user_agent = Column(String, nullable=True) 

2399 

2400 # when it was disabled 

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

2402 

2403 user = relationship("User") 

2404 

2405 

2406class PushNotificationDeliveryAttempt(Base): 

2407 __tablename__ = "push_notification_delivery_attempt" 

2408 

2409 id = Column(BigInteger, primary_key=True) 

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

2411 

2412 push_notification_subscription_id = Column( 

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

2414 ) 

2415 

2416 success = Column(Boolean, nullable=False) 

2417 # the HTTP status code, 201 is success 

2418 status_code = Column(Integer, nullable=False) 

2419 

2420 # can be null if it was a success 

2421 response = Column(String, nullable=True) 

2422 

2423 push_notification_subscription = relationship("PushNotificationSubscription") 

2424 

2425 

2426class Language(Base): 

2427 """ 

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

2429 """ 

2430 

2431 __tablename__ = "languages" 

2432 

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

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

2435 

2436 # the english name 

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

2438 

2439 

2440class Region(Base): 

2441 """ 

2442 Table of regions 

2443 """ 

2444 

2445 __tablename__ = "regions" 

2446 

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

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

2449 

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

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

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

2453 

2454 

2455class UserBlock(Base): 

2456 """ 

2457 Table of blocked users 

2458 """ 

2459 

2460 __tablename__ = "user_blocks" 

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

2462 

2463 id = Column(BigInteger, primary_key=True) 

2464 

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

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

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

2468 

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

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

2471 

2472 

2473class APICall(Base): 

2474 """ 

2475 API call logs 

2476 """ 

2477 

2478 __tablename__ = "api_calls" 

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

2480 

2481 id = Column(BigInteger, primary_key=True) 

2482 

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

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

2485 

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

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

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

2489 

2490 # approximate time of the call 

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

2492 

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

2494 method = Column(String, nullable=False) 

2495 

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

2497 status_code = Column(String, nullable=True) 

2498 

2499 # handler duration (excluding serialization, etc) 

2500 duration = Column(Float, nullable=False) 

2501 

2502 # user_id of caller, None means not logged in 

2503 user_id = Column(BigInteger, nullable=True) 

2504 

2505 # sanitized request bytes 

2506 request = Column(Binary, nullable=True) 

2507 

2508 # sanitized response bytes 

2509 response = Column(Binary, nullable=True) 

2510 

2511 # whether response bytes have been truncated 

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

2513 

2514 # the exception traceback, if any 

2515 traceback = Column(String, nullable=True) 

2516 

2517 # human readable perf report 

2518 perf_report = Column(String, nullable=True) 

2519 

2520 # details of the browser, if available 

2521 ip_address = Column(String, nullable=True) 

2522 user_agent = Column(String, nullable=True) 

2523 

2524 

2525class AccountDeletionReason(Base): 

2526 __tablename__ = "account_deletion_reason" 

2527 

2528 id = Column(BigInteger, primary_key=True) 

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

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

2531 reason = Column(String, nullable=True) 

2532 

2533 user = relationship("User")