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

1import enum

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

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

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)

56Base = declarative_base(metadata=meta)

59class HostingStatus(enum.Enum):

60 can_host = enum.auto()

61 maybe = enum.auto()

62 cant_host = enum.auto()

65class MeetupStatus(enum.Enum):

66 wants_to_meetup = enum.auto()

67 open_to_meetup = enum.auto()

68 does_not_want_to_meetup = enum.auto()

71class SmokingLocation(enum.Enum):

72 yes = enum.auto()

73 window = enum.auto()

74 outside = enum.auto()

75 no = enum.auto()

78class SleepingArrangement(enum.Enum):

79 private = enum.auto()

80 common = enum.auto()

81 shared_room = enum.auto()

84class ParkingDetails(enum.Enum):

85 free_onsite = enum.auto()

86 free_offsite = enum.auto()

87 paid_onsite = enum.auto()

88 paid_offsite = enum.auto()

91class TimezoneArea(Base):

92 __tablename__ = "timezone_areas"

93 id = Column(BigInteger, primary_key=True)

95 tzid = Column(String)

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

98 __table_args__ = (

99 Index(

100 "ix_timezone_areas_geom_tzid",

101 geom,

102 tzid,

103 postgresql_using="gist",

104 ),

105 )

106

107

108class User(Base):

109 """

110 Basic user and profile details

111 """

112

113 __tablename__ = "users"

114

115 id = Column(BigInteger, primary_key=True)

116

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

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

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

120 hashed_password = Column(Binary, nullable=False)

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

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

123 # language preference -- defaults to empty string

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

125

126 # timezones should always be UTC

127 ## location

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

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

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

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

132 geom_radius = Column(Float, nullable=True)

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

134 city = Column(String, nullable=False)

135 # "Grew up in" on profile

136 hometown = Column(String, nullable=True)

137

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

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

140

141 timezone = column_property(

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

143 deferred=True,

144 )

145

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

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

148

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

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

151 # same as above for host requests

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

153

154 # display name

155 name = Column(String, nullable=False)

156 gender = Column(String, nullable=False)

157 pronouns = Column(String, nullable=True)

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

159

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

161

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

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

164

165 # community standing score

166 community_standing = Column(Float, nullable=True)

167

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

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

170

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

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

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

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

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

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

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

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

179

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

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

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

183

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

185 # accidental or they changed their mind

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

187 undelete_token = Column(String, nullable=True)

188 # validity of the undelete token

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

190

191 # hosting preferences

192 max_guests = Column(Integer, nullable=True)

193 last_minute = Column(Boolean, nullable=True)

194 has_pets = Column(Boolean, nullable=True)

195 accepts_pets = Column(Boolean, nullable=True)

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

197 has_kids = Column(Boolean, nullable=True)

198 accepts_kids = Column(Boolean, nullable=True)

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

200 has_housemates = Column(Boolean, nullable=True)

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

202 wheelchair_accessible = Column(Boolean, nullable=True)

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

204 smokes_at_home = Column(Boolean, nullable=True)

205 drinking_allowed = Column(Boolean, nullable=True)

206 drinks_at_home = Column(Boolean, nullable=True)

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

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

209

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

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

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

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

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

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

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

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

218 parking = Column(Boolean, nullable=True)

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

220 camping_ok = Column(Boolean, nullable=True)

221

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

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

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

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

226

227 # number of onboarding emails sent

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

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

230

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

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

233 # opted out of the newsletter

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

235

236 # set to null to receive no digests

237 digest_frequency = Column(Interval, nullable=True)

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

239

240 # for changing their email

241 new_email = Column(String, nullable=True)

242

243 new_email_token = Column(String, nullable=True)

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

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

246

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

248

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

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

251 # | Start |

252 # | phone = None | someone else

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

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

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

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

262 # | | ^ V | |

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

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

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

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

271

272 # randomly generated Luhn 6-digit string

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

274

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

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

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

278

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

280 # e.g. cus_JjoXHttuZopv0t

281 # for new US entity

282 stripe_customer_id = Column(String, nullable=True)

283 # for old AU entity

284 stripe_customer_id_old = Column(String, nullable=True)

285

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

287

288 # checking for phone verification

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

290

291 # whether this user has all emails turned off

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

293

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

295

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

297

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

299

300 __table_args__ = (

301 # Verified phone numbers should be unique

302 Index(

303 "ix_users_unique_phone",

304 phone,

305 unique=True,

306 postgresql_where=phone_verification_verified != None,

307 ),

308 Index(

309 "ix_users_active",

310 id,

311 postgresql_where=~is_banned & ~is_deleted,

312 ),

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

314 Index(

315 "ix_users_geom_active",

316 geom,

317 id,

318 username,

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

320 ),

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

322 CheckConstraint(

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

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

325 name="check_new_email_token_state",

326 ),

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

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

329 CheckConstraint(

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

331 name="phone_verified_conditions",

332 ),

333 # Email must match our regex

334 CheckConstraint(

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

336 name="valid_email",

337 ),

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

339 CheckConstraint(

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

341 name="undelete_nullity",

342 ),

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

344 CheckConstraint(

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

346 name="do_not_email_inactive",

347 ),

348 )

349

350 @hybrid_property

351 def has_completed_profile(self):

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

353

354 @has_completed_profile.expression

355 def has_completed_profile(cls):

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

357

358 @hybrid_property

359 def jailed_missing_tos(self):

360 return self.accepted_tos < TOS_VERSION

361

362 @hybrid_property

363 def jailed_missing_community_guidelines(self):

364 return self.accepted_community_guidelines < GUIDELINES_VERSION

365

366 @hybrid_property

367 def jailed_pending_mod_notes(self):

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

369

370 @hybrid_property

371 def jailed_pending_activeness_probe(self):

372 return self.pending_activeness_probe != None

373

374 @hybrid_property

375 def is_jailed(self):

376 return (

377 self.jailed_missing_tos

378 | self.jailed_missing_community_guidelines

379 | self.is_missing_location

380 | self.jailed_pending_mod_notes

381 | self.jailed_pending_activeness_probe

382 )

383

384 @hybrid_property

385 def is_missing_location(self):

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

387

388 @hybrid_property

389 def is_visible(self):

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

391

392 @property

393 def coordinates(self):

394 return get_coordinates(self.geom)

395

396 @property

397 def display_joined(self):

398 """

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

400 """

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

402

403 @property

404 def display_last_active(self):

405 """

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

407 """

408 return last_active_coarsen(self.last_active)

409

410 @hybrid_property

411 def phone_is_verified(self):

412 return (

413 self.phone_verification_verified is not None

414 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME

415 )

416

417 @phone_is_verified.expression

418 def phone_is_verified(cls):

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

420 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME

421 )

422

423 @hybrid_property

424 def phone_code_expired(self):

425 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME

426

427 def __repr__(self):

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

429

430

431class UserBadge(Base):

432 """

433 A badge on a user's profile

434 """

435

436 __tablename__ = "user_badges"

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

438

439 id = Column(BigInteger, primary_key=True)

440

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

442 # corresponds to "id" in badges.json

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

444

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

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

447

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

449

450

451class ActivenessProbeStatus(enum.Enum):

452 # no response yet

453 pending = enum.auto()

454

455 # didn't respond on time

456 expired = enum.auto()

457

458 # responded that they're still active

459 still_active = enum.auto()

460

461 # responded that they're no longer active

462 no_longer_active = enum.auto()

463

464

465class ActivenessProbe(Base):

466 """

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

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

469 show up as such.

470 """

471

472 __tablename__ = "activeness_probes"

473

474 id = Column(BigInteger, primary_key=True)

475

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

477 # the time this probe was initiated

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

479 # the number of reminders sent for this probe

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

481

482 # the time of response

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

484 # the response value

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

486

487 @hybrid_property

488 def is_pending(self):

489 return self.responded == None

490

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

492

493 __table_args__ = (

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

495 Index(

496 "ix_activeness_probe_unique_pending_response",

497 user_id,

498 unique=True,

499 postgresql_where=responded == None,

500 ),

501 # response time is none iff response is pending

502 CheckConstraint(

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

504 name="pending_has_no_responded",

505 ),

506 )

507

508

509User.pending_activeness_probe = relationship(

510 ActivenessProbe,

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

512 uselist=False,

513 back_populates="user",

514)

515

516

517class StrongVerificationAttemptStatus(enum.Enum):

518 ## full data states

519 # completed, this now provides verification for a user

520 succeeded = enum.auto()

521

522 ## no data states

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

524 in_progress_waiting_on_user_to_open_app = enum.auto()

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

526 in_progress_waiting_on_user_in_app = enum.auto()

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

528 in_progress_waiting_on_backend = enum.auto()

529 # failed, no data

530 failed = enum.auto()

531

532 # duplicate, at our end, has data

533 duplicate = enum.auto()

534

535 ## minimal data states

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

537 deleted = enum.auto()

538

539

540class PassportSex(enum.Enum):

541 """

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

543 """

544

545 male = enum.auto()

546 female = enum.auto()

547 unspecified = enum.auto()

548

549

550class StrongVerificationAttempt(Base):

551 """

552 An attempt to perform strong verification

553 """

554

555 __tablename__ = "strong_verification_attempts"

556

557 # our verification id

558 id = Column(BigInteger, primary_key=True)

559

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

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

562

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

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

565

566 status = Column(

567 Enum(StrongVerificationAttemptStatus),

568 nullable=False,

569 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app,

570 )

571

572 ## full data

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

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

575 passport_encrypted_data = Column(Binary, nullable=True)

576 passport_date_of_birth = Column(Date, nullable=True)

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

578

579 ## minimal data: this will not be deleted

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

581 passport_expiry_date = Column(Date, nullable=True)

582 passport_nationality = Column(String, nullable=True)

583 # last three characters of the passport number

584 passport_last_three_document_chars = Column(String, nullable=True)

585

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

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

588

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

590

591 user = relationship("User")

592

593 @hybrid_property

594 def is_valid(self):

595 """

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

597 """

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

599

600 @is_valid.expression

601 def is_valid(cls):

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

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

604 )

605

606 @hybrid_property

607 def is_visible(self):

608 return self.status != StrongVerificationAttemptStatus.deleted

609

610 @hybrid_method

611 def _raw_birthdate_match(self, user):

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

613 return self.passport_date_of_birth == user.birthdate

614

615 @hybrid_method

616 def matches_birthdate(self, user):

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

618

619 @hybrid_method

620 def _raw_gender_match(self, user):

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

622 return (

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

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

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

626 | (user.has_passport_sex_gender_exception == True)

627 )

628

629 @hybrid_method

630 def matches_gender(self, user):

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

632

633 @hybrid_method

634 def has_strong_verification(self, user):

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

636

637 __table_args__ = (

638 # used to look up verification status for a user

639 Index(

640 "ix_strong_verification_attempts_current",

641 user_id,

642 passport_expiry_date,

643 postgresql_where=status == StrongVerificationAttemptStatus.succeeded,

644 ),

645 # each passport can be verified only once

646 Index(

647 "ix_strong_verification_attempts_unique_succeeded",

648 passport_expiry_date,

649 passport_nationality,

650 passport_last_three_document_chars,

651 unique=True,

652 postgresql_where=(

653 (status == StrongVerificationAttemptStatus.succeeded)

654 | (status == StrongVerificationAttemptStatus.deleted)

655 ),

656 ),

657 # full data check

658 CheckConstraint(

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

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

661 name="full_data_status",

662 ),

663 # minimal data check

664 CheckConstraint(

665 "(has_minimal_data IS TRUE AND passport_expiry_date IS NOT NULL AND passport_nationality IS NOT NULL AND passport_last_three_document_chars IS NOT NULL) OR \

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

667 name="minimal_data_status",

668 ),

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

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

671 CheckConstraint(

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

673 name="full_data_implies_minimal_data",

674 ),

675 # succeeded implies full data

676 CheckConstraint(

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

678 name="succeeded_implies_full_data",

679 ),

680 # in_progress/failed implies no_data

681 CheckConstraint(

682 "(NOT ((status = 'in_progress_waiting_on_user_to_open_app') OR (status = 'in_progress_waiting_on_user_in_app') OR (status = 'in_progress_waiting_on_backend') OR (status = 'failed'))) OR (has_minimal_data IS FALSE)",

683 name="in_progress_failed_iris_implies_no_data",

684 ),

685 # deleted or duplicate implies minimal data

686 CheckConstraint(

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

688 name="deleted_duplicate_implies_minimal_data",

689 ),

690 )

691

692

693class ModNote(Base):

694 """

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

696

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

698 """

699

700 __tablename__ = "mod_notes"

701 id = Column(BigInteger, primary_key=True)

702

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

704

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

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

707

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

709 internal_id = Column(String, nullable=False)

710 # the admin that left this note

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

712

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

714

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

716

717 def __repr__(self):

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

719

720 @hybrid_property

721 def is_pending(self):

722 return self.acknowledged == None

723

724 __table_args__ = (

725 # used to look up pending notes

726 Index(

727 "ix_mod_notes_unacknowledged",

728 user_id,

729 postgresql_where=acknowledged == None,

730 ),

731 )

732

733

734class StrongVerificationCallbackEvent(Base):

735 __tablename__ = "strong_verification_callback_events"

736

737 id = Column(BigInteger, primary_key=True)

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

739

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

741

742 iris_status = Column(String, nullable=False)

743

744

745class DonationType(enum.Enum):

746 one_time = enum.auto()

747 recurring = enum.auto()

748

749

750class DonationInitiation(Base):

751 """

752 Whenever someone initiaties a donation through the platform

753 """

754

755 __tablename__ = "donation_initiations"

756 id = Column(BigInteger, primary_key=True)

757

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

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

760

761 amount = Column(Integer, nullable=False)

762 stripe_checkout_session_id = Column(String, nullable=False)

763

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

765

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

767

768

769class Invoice(Base):

770 """

771 Successful donations, both one off and recurring

772

773 Triggered by `payment_intent.succeeded` webhook

774 """

775

776 __tablename__ = "invoices"

777

778 id = Column(BigInteger, primary_key=True)

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

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

781

782 amount = Column(Float, nullable=False)

783

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

785 stripe_receipt_url = Column(String, nullable=False)

786

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

788

789

790class LanguageFluency(enum.Enum):

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

792 beginner = 1

793 conversational = 2

794 fluent = 3

795

796

797class LanguageAbility(Base):

798 __tablename__ = "language_abilities"

799 __table_args__ = (

800 # Users can only have one language ability per language

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

802 )

803

804 id = Column(BigInteger, primary_key=True)

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

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

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

808

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

810 language = relationship("Language")

811

812

813class RegionVisited(Base):

814 __tablename__ = "regions_visited"

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

816

817 id = Column(BigInteger, primary_key=True)

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

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

820

821

822class RegionLived(Base):

823 __tablename__ = "regions_lived"

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

825

826 id = Column(BigInteger, primary_key=True)

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

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

829

830

831class FriendStatus(enum.Enum):

832 pending = enum.auto()

833 accepted = enum.auto()

834 rejected = enum.auto()

835 cancelled = enum.auto()

836

837

838class FriendRelationship(Base):

839 """

840 Friendship relations between users

841

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

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

844 """

845

846 __tablename__ = "friend_relationships"

847

848 id = Column(BigInteger, primary_key=True)

849

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

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

852

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

854

855 # timezones should always be UTC

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

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

858

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

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

861

862

863class ContributeOption(enum.Enum):

864 yes = enum.auto()

865 maybe = enum.auto()

866 no = enum.auto()

867

868

869class ContributorForm(Base):

870 """

871 Someone filled in the contributor form

872 """

873

874 __tablename__ = "contributor_forms"

875

876 id = Column(BigInteger, primary_key=True)

877

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

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

880

881 ideas = Column(String, nullable=True)

882 features = Column(String, nullable=True)

883 experience = Column(String, nullable=True)

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

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

886 expertise = Column(String, nullable=True)

887

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

889

890 @hybrid_property

891 def is_filled(self):

892 """

893 Whether the form counts as having been filled

894 """

895 return (

896 (self.ideas != None)

897 | (self.features != None)

898 | (self.experience != None)

899 | (self.contribute != None)

900 | (self.contribute_ways != [])

901 | (self.expertise != None)

902 )

903

904 @property

905 def should_notify(self):

906 """

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

908

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

910 """

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

912

913

914class SignupFlow(Base):

915 """

916 Signup flows/incomplete users

917

918 Coinciding fields have the same meaning as in User

919 """

920

921 __tablename__ = "signup_flows"

922

923 id = Column(BigInteger, primary_key=True)

924

925 # housekeeping

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

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

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

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

930 email_token = Column(String, nullable=True)

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

932

933 ## Basic

934 name = Column(String, nullable=False)

935 # TODO: unique across both tables

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

937 # TODO: invitation, attribution

938

939 ## Account

940 # TODO: unique across both tables

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

942 hashed_password = Column(Binary, nullable=True)

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

944 gender = Column(String, nullable=True)

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

946 city = Column(String, nullable=True)

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

948 geom_radius = Column(Float, nullable=True)

949

950 accepted_tos = Column(Integer, nullable=True)

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

952

953 opt_out_of_newsletter = Column(Boolean, nullable=True)

954

955 ## Feedback

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

957 ideas = Column(String, nullable=True)

958 features = Column(String, nullable=True)

959 experience = Column(String, nullable=True)

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

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

962 expertise = Column(String, nullable=True)

963

964 @hybrid_property

965 def token_is_valid(self):

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

967

968 @hybrid_property

969 def account_is_filled(self):

970 return (

971 (self.username != None)

972 & (self.birthdate != None)

973 & (self.gender != None)

974 & (self.hosting_status != None)

975 & (self.city != None)

976 & (self.geom != None)

977 & (self.geom_radius != None)

978 & (self.accepted_tos != None)

979 & (self.opt_out_of_newsletter != None)

980 )

981

982 @hybrid_property

983 def is_completed(self):

984 return (

985 self.email_verified

986 & self.account_is_filled

987 & self.filled_feedback

988 & (self.accepted_community_guidelines == GUIDELINES_VERSION)

989 )

990

991

992class LoginToken(Base):

993 """

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

995 """

996

997 __tablename__ = "login_tokens"

998 token = Column(String, primary_key=True)

999

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

1001

1002 # timezones should always be UTC

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

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

1005

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

1007

1008 @hybrid_property

1009 def is_valid(self):

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

1011

1012 def __repr__(self):

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

1014

1015

1016class PasswordResetToken(Base):

1017 __tablename__ = "password_reset_tokens"

1018 token = Column(String, primary_key=True)

1019

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

1021

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

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

1024

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

1026

1027 @hybrid_property

1028 def is_valid(self):

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

1030

1031 def __repr__(self):

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

1033

1034

1035class AccountDeletionToken(Base):

1036 __tablename__ = "account_deletion_tokens"

1037

1038 token = Column(String, primary_key=True)

1039

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

1041

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

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

1044

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

1046

1047 @hybrid_property

1048 def is_valid(self):

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

1050

1051 def __repr__(self):

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

1053

1054

1055class UserActivity(Base):

1056 """

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

1058

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

1060 """

1061

1062 __tablename__ = "user_activity"

1063

1064 id = Column(BigInteger, primary_key=True)

1065

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

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

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

1069

1070 # details of the browser, if available

1071 ip_address = Column(INET, nullable=True)

1072 user_agent = Column(String, nullable=True)

1073

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

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

1076

1077 __table_args__ = (

1078 # helps look up this tuple quickly

1079 Index(

1080 "ix_user_activity_user_id_period_ip_address_user_agent",

1081 user_id,

1082 period,

1083 ip_address,

1084 user_agent,

1085 unique=True,

1086 ),

1087 )

1088

1089

1090class UserSession(Base):

1091 """

1092 API keys/session cookies for the app

1093

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

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

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

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

1098 site.

1099

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

1101

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

1103 """

1104

1105 __tablename__ = "sessions"

1106 token = Column(String, primary_key=True)

1107

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

1109

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

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

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

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

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

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

1116

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

1118 long_lived = Column(Boolean, nullable=False)

1119

1120 # the time at which the session was created

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

1122

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

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

1125

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

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

1128

1129 # the last time this session was used

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

1131

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

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

1134

1135 # details of the browser, if available

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

1137 ip_address = Column(String, nullable=True)

1138 user_agent = Column(String, nullable=True)

1139

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

1141

1142 @hybrid_property

1143 def is_valid(self):

1144 """

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

1146

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

1148

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

1150 """

1151 return (

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

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

1154 & (self.deleted == None)

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

1156 )

1157

1158

1159class Conversation(Base):

1160 """

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

1162 """

1163

1164 __tablename__ = "conversations"

1165

1166 id = Column(BigInteger, primary_key=True)

1167 # timezone should always be UTC

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

1169

1170 def __repr__(self):

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

1172

1173

1174class GroupChat(Base):

1175 """

1176 Group chat

1177 """

1178

1179 __tablename__ = "group_chats"

1180

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

1182

1183 title = Column(String, nullable=True)

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

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

1186 is_dm = Column(Boolean, nullable=False)

1187

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

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

1190

1191 def __repr__(self):

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

1193

1194

1195class GroupChatRole(enum.Enum):

1196 admin = enum.auto()

1197 participant = enum.auto()

1198

1199

1200class GroupChatSubscription(Base):

1201 """

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

1203 """

1204

1205 __tablename__ = "group_chat_subscriptions"

1206 id = Column(BigInteger, primary_key=True)

1207

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

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

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

1211

1212 # timezones should always be UTC

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

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

1215

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

1217

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

1219

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

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

1222

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

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

1225

1226 def muted_display(self):

1227 """

1228 Returns (muted, muted_until) display values:

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

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

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

1232 """

1233 if self.muted_until < now():

1234 return (False, None)

1235 elif self.muted_until == DATETIME_INFINITY:

1236 return (True, None)

1237 else:

1238 return (True, self.muted_until)

1239

1240 @hybrid_property

1241 def is_muted(self):

1242 return self.muted_until > func.now()

1243

1244 def __repr__(self):

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

1246

1247

1248class MessageType(enum.Enum):

1249 text = enum.auto()

1250 # e.g.

1251 # image =

1252 # emoji =

1253 # ...

1254 chat_created = enum.auto()

1255 chat_edited = enum.auto()

1256 user_invited = enum.auto()

1257 user_left = enum.auto()

1258 user_made_admin = enum.auto()

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

1260 host_request_status_changed = enum.auto()

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

1262

1263

1264class HostRequestStatus(enum.Enum):

1265 pending = enum.auto()

1266 accepted = enum.auto()

1267 rejected = enum.auto()

1268 confirmed = enum.auto()

1269 cancelled = enum.auto()

1270

1271

1272class Message(Base):

1273 """

1274 A message.

1275

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

1277 """

1278

1279 __tablename__ = "messages"

1280

1281 id = Column(BigInteger, primary_key=True)

1282

1283 # which conversation the message belongs in

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

1285

1286 # the user that sent the message/command

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

1288

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

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

1291

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

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

1294

1295 # time sent, timezone should always be UTC

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

1297

1298 # the plain-text message text if not control

1299 text = Column(String, nullable=True)

1300

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

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

1303

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

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

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

1307

1308 @property

1309 def is_normal_message(self):

1310 """

1311 There's only one normal type atm, text

1312 """

1313 return self.message_type == MessageType.text

1314

1315 def __repr__(self):

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

1317

1318

1319class ContentReport(Base):

1320 """

1321 A piece of content reported to admins

1322 """

1323

1324 __tablename__ = "content_reports"

1325

1326 id = Column(BigInteger, primary_key=True)

1327

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

1329

1330 # the user who reported or flagged the content

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

1332

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

1334 reason = Column(String, nullable=False)

1335 # a short description

1336 description = Column(String, nullable=False)

1337

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

1339 content_ref = Column(String, nullable=False)

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

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

1342

1343 # details of the browser, if available

1344 user_agent = Column(String, nullable=False)

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

1346 page = Column(String, nullable=False)

1347

1348 # see comments above for reporting vs author

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

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

1351

1352

1353class Email(Base):

1354 """

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

1356 """

1357

1358 __tablename__ = "emails"

1359

1360 id = Column(String, primary_key=True)

1361

1362 # timezone should always be UTC

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

1364

1365 sender_name = Column(String, nullable=False)

1366 sender_email = Column(String, nullable=False)

1367

1368 recipient = Column(String, nullable=False)

1369 subject = Column(String, nullable=False)

1370

1371 plain = Column(String, nullable=False)

1372 html = Column(String, nullable=False)

1373

1374 list_unsubscribe_header = Column(String, nullable=True)

1375 source_data = Column(String, nullable=True)

1376

1377

1378class SMS(Base):

1379 """

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

1381 """

1382

1383 __tablename__ = "smss"

1384

1385 id = Column(BigInteger, primary_key=True)

1386

1387 # timezone should always be UTC

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

1389 # AWS message id

1390 message_id = Column(String, nullable=False)

1391

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

1393 sms_sender_id = Column(String, nullable=False)

1394 number = Column(String, nullable=False)

1395 message = Column(String, nullable=False)

1396

1397

1398class HostRequest(Base):

1399 """

1400 A request to stay with a host

1401 """

1402

1403 __tablename__ = "host_requests"

1404

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

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

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

1408

1409 # TODO: proper timezone handling

1410 timezone = "Etc/UTC"

1411

1412 # dates in the timezone above

1413 from_date = Column(Date, nullable=False)

1414 to_date = Column(Date, nullable=False)

1415

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

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

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

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

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

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

1422

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

1424

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

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

1427

1428 # number of reference reminders sent out

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

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

1431

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

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

1434 host_reason_didnt_meetup = Column(String, nullable=True)

1435 surfer_reason_didnt_meetup = Column(String, nullable=True)

1436

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

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

1439 conversation = relationship("Conversation")

1440

1441 __table_args__ = (

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

1443 Index(

1444 "ix_host_requests_host_didnt_meetup",

1445 host_reason_didnt_meetup != None,

1446 ),

1447 Index(

1448 "ix_host_requests_surfer_didnt_meetup",

1449 surfer_reason_didnt_meetup != None,

1450 ),

1451 )

1452

1453 @hybrid_property

1454 def can_write_reference(self):

1455 return (

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

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

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

1459 )

1460

1461 @can_write_reference.expression

1462 def can_write_reference(cls):

1463 return (

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

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

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

1467 )

1468

1469 def __repr__(self):

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

1471

1472

1473class ReferenceType(enum.Enum):

1474 friend = enum.auto()

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

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

1477

1478

1479class Reference(Base):

1480 """

1481 Reference from one user to another

1482 """

1483

1484 __tablename__ = "references"

1485

1486 id = Column(BigInteger, primary_key=True)

1487 # timezone should always be UTC

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

1489

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

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

1492

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

1494

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

1496

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

1498 # text that's only visible to mods

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

1500

1501 rating = Column(Float, nullable=False)

1502 was_appropriate = Column(Boolean, nullable=False)

1503

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

1505

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

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

1508

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

1510

1511 __table_args__ = (

1512 # Rating must be between 0 and 1, inclusive

1513 CheckConstraint(

1514 "rating BETWEEN 0 AND 1",

1515 name="rating_between_0_and_1",

1516 ),

1517 # Has host_request_id or it's a friend reference

1518 CheckConstraint(

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

1520 name="host_request_id_xor_friend_reference",

1521 ),

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

1523 Index(

1524 "ix_references_unique_friend_reference",

1525 from_user_id,

1526 to_user_id,

1527 reference_type,

1528 unique=True,

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

1530 ),

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

1532 Index(

1533 "ix_references_unique_per_host_request",

1534 from_user_id,

1535 to_user_id,

1536 host_request_id,

1537 unique=True,

1538 postgresql_where=(host_request_id != None),

1539 ),

1540 )

1541

1542 @property

1543 def should_report(self):

1544 """

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

1546 """

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

1548

1549

1550class InitiatedUpload(Base):

1551 """

1552 Started downloads, not necessarily complete yet.

1553 """

1554

1555 __tablename__ = "initiated_uploads"

1556

1557 key = Column(String, primary_key=True)

1558

1559 # timezones should always be UTC

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

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

1562

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

1564

1565 initiator_user = relationship("User")

1566

1567 @hybrid_property

1568 def is_valid(self):

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

1570

1571

1572class Upload(Base):

1573 """

1574 Completed uploads.

1575 """

1576

1577 __tablename__ = "uploads"

1578 key = Column(String, primary_key=True)

1579

1580 filename = Column(String, nullable=False)

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

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

1583

1584 # photo credit, etc

1585 credit = Column(String, nullable=True)

1586

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

1588

1589 def _url(self, size):

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

1591

1592 @property

1593 def thumbnail_url(self):

1594 return self._url("thumbnail")

1595

1596 @property

1597 def full_url(self):

1598 return self._url("full")

1599

1600

1601communities_seq = Sequence("communities_seq")

1602

1603

1604class Node(Base):

1605 """

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

1607

1608 Administered by the official cluster

1609 """

1610

1611 __tablename__ = "nodes"

1612

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

1614

1615 # name and description come from official cluster

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

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

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

1619

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

1621

1622 contained_users = relationship(

1623 "User",

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

1625 viewonly=True,

1626 uselist=True,

1627 )

1628

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

1630

1631

1632class Cluster(Base):

1633 """

1634 Cluster, administered grouping of content

1635 """

1636

1637 __tablename__ = "clusters"

1638

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

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

1641 name = Column(String, nullable=False)

1642 # short description

1643 description = Column(String, nullable=False)

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

1645

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

1647

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

1649

1650 official_cluster_for_node = relationship(

1651 "Node",

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

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

1654 uselist=False,

1655 viewonly=True,

1656 )

1657

1658 parent_node = relationship(

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

1660 )

1661

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

1663 # all pages

1664 pages = relationship(

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

1666 )

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

1668 discussions = relationship(

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

1670 )

1671

1672 # includes also admins

1673 members = relationship(

1674 "User",

1675 lazy="dynamic",

1676 backref="cluster_memberships",

1677 secondary="cluster_subscriptions",

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

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

1680 viewonly=True,

1681 )

1682

1683 admins = relationship(

1684 "User",

1685 lazy="dynamic",

1686 backref="cluster_adminships",

1687 secondary="cluster_subscriptions",

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

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

1690 viewonly=True,

1691 )

1692

1693 main_page = relationship(

1694 "Page",

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

1696 viewonly=True,

1697 uselist=False,

1698 )

1699

1700 @property

1701 def is_leaf(self) -> bool:

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

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

1704

1705 __table_args__ = (

1706 # Each node can have at most one official cluster

1707 Index(

1708 "ix_clusters_owner_parent_node_id_is_official_cluster",

1709 parent_node_id,

1710 is_official_cluster,

1711 unique=True,

1712 postgresql_where=is_official_cluster,

1713 ),

1714 )

1715

1716

1717class NodeClusterAssociation(Base):

1718 """

1719 NodeClusterAssociation, grouping of nodes

1720 """

1721

1722 __tablename__ = "node_cluster_associations"

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

1724

1725 id = Column(BigInteger, primary_key=True)

1726

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

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

1729

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

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

1732

1733

1734class ClusterRole(enum.Enum):

1735 member = enum.auto()

1736 admin = enum.auto()

1737

1738

1739class ClusterSubscription(Base):

1740 """

1741 ClusterSubscription of a user

1742 """

1743

1744 __tablename__ = "cluster_subscriptions"

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

1746

1747 id = Column(BigInteger, primary_key=True)

1748

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

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

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

1752

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

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

1755

1756

1757class ClusterPageAssociation(Base):

1758 """

1759 pages related to clusters

1760 """

1761

1762 __tablename__ = "cluster_page_associations"

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

1764

1765 id = Column(BigInteger, primary_key=True)

1766

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

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

1769

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

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

1772

1773

1774class PageType(enum.Enum):

1775 main_page = enum.auto()

1776 place = enum.auto()

1777 guide = enum.auto()

1778

1779

1780class Page(Base):

1781 """

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

1783 """

1784

1785 __tablename__ = "pages"

1786

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

1788

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

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

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

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

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

1794

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

1796

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

1798

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

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

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

1802 owner_cluster = relationship(

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

1804 )

1805

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

1807

1808 __table_args__ = (

1809 # Only one of owner_user and owner_cluster should be set

1810 CheckConstraint(

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

1812 name="one_owner",

1813 ),

1814 # Only clusters can own main pages

1815 CheckConstraint(

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

1817 name="main_page_owned_by_cluster",

1818 ),

1819 # Each cluster can have at most one main page

1820 Index(

1821 "ix_pages_owner_cluster_id_type",

1822 owner_cluster_id,

1823 type,

1824 unique=True,

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

1826 ),

1827 )

1828

1829 def __repr__(self):

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

1831

1832

1833class PageVersion(Base):

1834 """

1835 version of page content

1836 """

1837

1838 __tablename__ = "page_versions"

1839

1840 id = Column(BigInteger, primary_key=True)

1841

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

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

1844 title = Column(String, nullable=False)

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

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

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

1848 # the human-readable address

1849 address = Column(String, nullable=True)

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

1851

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

1853

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

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

1856 photo = relationship("Upload")

1857

1858 __table_args__ = (

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

1860 CheckConstraint(

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

1862 name="geom_iff_address",

1863 ),

1864 )

1865

1866 @property

1867 def coordinates(self):

1868 # returns (lat, lng) or None

1869 return get_coordinates(self.geom)

1870

1871 def __repr__(self):

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

1873

1874

1875class ClusterEventAssociation(Base):

1876 """

1877 events related to clusters

1878 """

1879

1880 __tablename__ = "cluster_event_associations"

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

1882

1883 id = Column(BigInteger, primary_key=True)

1884

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

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

1887

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

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

1890

1891

1892class Event(Base):

1893 """

1894 An event is compose of two parts:

1895

1896 * An event template (Event)

1897 * An occurrence (EventOccurrence)

1898

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

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

1901 """

1902

1903 __tablename__ = "events"

1904

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

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

1907

1908 title = Column(String, nullable=False)

1909

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

1911

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

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

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

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

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

1917

1918 parent_node = relationship(

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

1920 )

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

1922 subscribers = relationship(

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

1924 )

1925 organizers = relationship(

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

1927 )

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

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

1930 owner_cluster = relationship(

1931 "Cluster",

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

1933 uselist=False,

1934 foreign_keys="Event.owner_cluster_id",

1935 )

1936

1937 __table_args__ = (

1938 # Only one of owner_user and owner_cluster should be set

1939 CheckConstraint(

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

1941 name="one_owner",

1942 ),

1943 )

1944

1945

1946class EventOccurrence(Base):

1947 __tablename__ = "event_occurrences"

1948

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

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

1951

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

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

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

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

1956

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

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

1959

1960 # a null geom is an online-only event

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

1962 # physical address, iff geom is not null

1963 address = Column(String, nullable=True)

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

1965 link = Column(String, nullable=True)

1966

1967 timezone = "Etc/UTC"

1968

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

1970 # simplifies database constraints, etc

1971 during = Column(TSTZRANGE, nullable=False)

1972

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

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

1975

1976 creator_user = relationship(

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

1978 )

1979 event = relationship(

1980 "Event",

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

1982 remote_side="Event.id",

1983 foreign_keys="EventOccurrence.event_id",

1984 )

1985

1986 photo = relationship("Upload")

1987

1988 __table_args__ = (

1989 # Geom and address go together

1990 CheckConstraint(

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

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

1993 name="geom_iff_address",

1994 ),

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

1996 CheckConstraint(

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

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

1999 name="link_or_geom",

2000 ),

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

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

2003 )

2004

2005 @property

2006 def coordinates(self):

2007 # returns (lat, lng) or None

2008 return get_coordinates(self.geom)

2009

2010 @hybrid_property

2011 def start_time(self):

2012 return self.during.lower

2013

2014 @start_time.expression

2015 def start_time(cls):

2016 return func.lower(cls.during)

2017

2018 @hybrid_property

2019 def end_time(self):

2020 return self.during.upper

2021

2022 @end_time.expression

2023 def end_time(cls):

2024 return func.upper(cls.during)

2025

2026

2027class EventSubscription(Base):

2028 """

2029 Users' subscriptions to events

2030 """

2031

2032 __tablename__ = "event_subscriptions"

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

2034

2035 id = Column(BigInteger, primary_key=True)

2036

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

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

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

2040

2041 user = relationship("User")

2042 event = relationship("Event")

2043

2044

2045class EventOrganizer(Base):

2046 """

2047 Organizers for events

2048 """

2049

2050 __tablename__ = "event_organizers"

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

2052

2053 id = Column(BigInteger, primary_key=True)

2054

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

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

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

2058

2059 user = relationship("User")

2060 event = relationship("Event")

2061

2062

2063class AttendeeStatus(enum.Enum):

2064 going = enum.auto()

2065 maybe = enum.auto()

2066

2067

2068class EventOccurrenceAttendee(Base):

2069 """

2070 Attendees for events

2071 """

2072

2073 __tablename__ = "event_occurrence_attendees"

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

2075

2076 id = Column(BigInteger, primary_key=True)

2077

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

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

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

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

2082

2083 user = relationship("User")

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

2085

2086

2087class EventCommunityInviteRequest(Base):

2088 """

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

2090 """

2091

2092 __tablename__ = "event_community_invite_requests"

2093

2094 id = Column(BigInteger, primary_key=True)

2095

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

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

2098

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

2100

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

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

2103 approved = Column(Boolean, nullable=True)

2104

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

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

2107

2108 __table_args__ = (

2109 # each user can only request once

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

2111 # each event can only have one notification sent out

2112 Index(

2113 "ix_event_community_invite_requests_unique",

2114 occurrence_id,

2115 unique=True,

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

2117 ),

2118 # decided and approved ought to be null simultaneously

2119 CheckConstraint(

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

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

2122 name="decided_approved",

2123 ),

2124 )

2125

2126

2127class ClusterDiscussionAssociation(Base):

2128 """

2129 discussions related to clusters

2130 """

2131

2132 __tablename__ = "cluster_discussion_associations"

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

2134

2135 id = Column(BigInteger, primary_key=True)

2136

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

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

2139

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

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

2142

2143

2144class Discussion(Base):

2145 """

2146 forum board

2147 """

2148

2149 __tablename__ = "discussions"

2150

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

2152

2153 title = Column(String, nullable=False)

2154 content = Column(String, nullable=False)

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

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

2157

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

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

2160

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

2162

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

2164

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

2166

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

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

2169

2170

2171class DiscussionSubscription(Base):

2172 """

2173 users subscriptions to discussions

2174 """

2175

2176 __tablename__ = "discussion_subscriptions"

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

2178

2179 id = Column(BigInteger, primary_key=True)

2180

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

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

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

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

2185

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

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

2188

2189

2190class Thread(Base):

2191 """

2192 Thread

2193 """

2194

2195 __tablename__ = "threads"

2196

2197 id = Column(BigInteger, primary_key=True)

2198

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

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

2201

2202

2203class Comment(Base):

2204 """

2205 Comment

2206 """

2207

2208 __tablename__ = "comments"

2209

2210 id = Column(BigInteger, primary_key=True)

2211

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

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

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

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

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

2217

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

2219

2220

2221class Reply(Base):

2222 """

2223 Reply

2224 """

2225

2226 __tablename__ = "replies"

2227

2228 id = Column(BigInteger, primary_key=True)

2229

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

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

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

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

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

2235

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

2237

2238

2239class BackgroundJobState(enum.Enum):

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

2241 pending = enum.auto()

2242 # job complete

2243 completed = enum.auto()

2244 # error occured, will be retried

2245 error = enum.auto()

2246 # failed too many times, not retrying anymore

2247 failed = enum.auto()

2248

2249

2250class BackgroundJob(Base):

2251 """

2252 This table implements a queue of background jobs.

2253 """

2254

2255 __tablename__ = "background_jobs"

2256

2257 id = Column(BigInteger, primary_key=True)

2258

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

2260 job_type = Column(String, nullable=False)

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

2262

2263 # time queued

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

2265

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

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

2268

2269 # used to count number of retries for failed jobs

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

2271

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

2273

2274 # higher is more important

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

2276

2277 # protobuf encoded job payload

2278 payload = Column(Binary, nullable=False)

2279

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

2281 failure_info = Column(String, nullable=True)

2282

2283 __table_args__ = (

2284 # used in looking up background jobs to attempt

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

2286 Index(

2287 "ix_background_jobs_lookup",

2288 priority.desc(),

2289 next_attempt_after,

2290 (max_tries - try_count),

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

2292 ),

2293 )

2294

2295 @hybrid_property

2296 def ready_for_retry(self):

2297 return (

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

2299 & (self.try_count < self.max_tries)

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

2301 )

2302

2303 def __repr__(self):

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

2305

2306

2307class NotificationDeliveryType(enum.Enum):

2308 # send push notification to mobile/web

2309 push = enum.auto()

2310 # send individual email immediately

2311 email = enum.auto()

2312 # send in digest

2313 digest = enum.auto()

2314

2315

2316dt = NotificationDeliveryType

2317nd = notification_data_pb2

2318

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

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

2321

2322

2323class NotificationTopicAction(enum.Enum):

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

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

2326 self.defaults = defaults

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

2328 self.user_editable = user_editable

2329

2330 self.data_type = data_type

2331

2332 def unpack(self):

2333 return self.topic, self.action

2334

2335 @property

2336 def display(self):

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

2338

2339 def __str__(self):

2340 return self.display

2341

2342 # topic, action, default delivery types

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

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

2345

2346 # host requests

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

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

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

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

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

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

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

2354

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

2356

2357 # you receive a friend ref

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

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

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

2361 # ... the surfer

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

2363

2364 # you hosted

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

2366 # you surfed

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

2368

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

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

2371

2372 # group chats

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

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

2375

2376 # events

2377 # approved by mods

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

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

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

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

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

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

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

2385 # toplevel comment on an event

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

2387

2388 # discussion created

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

2390 # someone comments on your discussion

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

2392

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

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

2395

2396 # account settings

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

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

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

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

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

2402 # reset password

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

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

2405

2406 # account deletion

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

2408 # no more pushing to do

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

2410 # undeleted

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

2412

2413 # admin actions

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

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

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

2417

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

2419

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

2421

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

2423

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

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

2426

2427

2428class NotificationPreference(Base):

2429 __tablename__ = "notification_preferences"

2430

2431 id = Column(BigInteger, primary_key=True)

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

2433

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

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

2436 deliver = Column(Boolean, nullable=False)

2437

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

2439

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

2441

2442

2443class Notification(Base):

2444 """

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

2446 """

2447

2448 __tablename__ = "notifications"

2449

2450 id = Column(BigInteger, primary_key=True)

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

2452

2453 # recipient user id

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

2455

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

2457 key = Column(String, nullable=False)

2458

2459 data = Column(Binary, nullable=False)

2460

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

2462

2463 __table_args__ = (

2464 # used in looking up which notifications need delivery

2465 Index(

2466 "ix_notifications_created",

2467 created,

2468 ),

2469 )

2470

2471 @property

2472 def topic(self):

2473 return self.topic_action.topic

2474

2475 @property

2476 def action(self):

2477 return self.topic_action.action

2478

2479

2480class NotificationDelivery(Base):

2481 __tablename__ = "notification_deliveries"

2482

2483 id = Column(BigInteger, primary_key=True)

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

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

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

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

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

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

2490 # todo: device id

2491 # todo: receipt id, etc

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

2493

2494 __table_args__ = (

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

2496 # used in looking up which notifications need delivery

2497 Index(

2498 "ix_notification_deliveries_delivery_type",

2499 delivery_type,

2500 postgresql_where=(delivered != None),

2501 ),

2502 Index(

2503 "ix_notification_deliveries_dt_ni_dnull",

2504 delivery_type,

2505 notification_id,

2506 delivered == None,

2507 ),

2508 )

2509

2510

2511class PushNotificationSubscription(Base):

2512 __tablename__ = "push_notification_subscriptions"

2513

2514 id = Column(BigInteger, primary_key=True)

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

2516

2517 # which user this is connected to

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

2519

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

2521 # the endpoint

2522 endpoint = Column(String, nullable=False)

2523 # the "auth" key

2524 auth_key = Column(Binary, nullable=False)

2525 # the "p256dh" key

2526 p256dh_key = Column(Binary, nullable=False)

2527

2528 full_subscription_info = Column(String, nullable=False)

2529

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

2531 user_agent = Column(String, nullable=True)

2532

2533 # when it was disabled

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

2535

2536 user = relationship("User")

2537

2538

2539class PushNotificationDeliveryAttempt(Base):

2540 __tablename__ = "push_notification_delivery_attempt"

2541

2542 id = Column(BigInteger, primary_key=True)

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

2544

2545 push_notification_subscription_id = Column(

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

2547 )

2548

2549 success = Column(Boolean, nullable=False)

2550 # the HTTP status code, 201 is success

2551 status_code = Column(Integer, nullable=False)

2552

2553 # can be null if it was a success

2554 response = Column(String, nullable=True)

2555

2556 push_notification_subscription = relationship("PushNotificationSubscription")

2557

2558

2559class Language(Base):

2560 """

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

2562 """

2563

2564 __tablename__ = "languages"

2565

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

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

2568

2569 # the english name

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

2571

2572

2573class Region(Base):

2574 """

2575 Table of regions

2576 """

2577

2578 __tablename__ = "regions"

2579

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

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

2582

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

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

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

2586

2587

2588class UserBlock(Base):

2589 """

2590 Table of blocked users

2591 """

2592

2593 __tablename__ = "user_blocks"

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

2595

2596 id = Column(BigInteger, primary_key=True)

2597

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

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

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

2601

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

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

2604

2605

2606class APICall(Base):

2607 """

2608 API call logs

2609 """

2610

2611 __tablename__ = "api_calls"

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

2613

2614 id = Column(BigInteger, primary_key=True)

2615

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

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

2618

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

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

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

2622

2623 # approximate time of the call

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

2625

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

2627 method = Column(String, nullable=False)

2628

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

2630 status_code = Column(String, nullable=True)

2631

2632 # handler duration (excluding serialization, etc)

2633 duration = Column(Float, nullable=False)

2634

2635 # user_id of caller, None means not logged in

2636 user_id = Column(BigInteger, nullable=True)

2637

2638 # sanitized request bytes

2639 request = Column(Binary, nullable=True)

2640

2641 # sanitized response bytes

2642 response = Column(Binary, nullable=True)

2643

2644 # whether response bytes have been truncated

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

2646

2647 # the exception traceback, if any

2648 traceback = Column(String, nullable=True)

2649

2650 # human readable perf report

2651 perf_report = Column(String, nullable=True)

2652

2653 # details of the browser, if available

2654 ip_address = Column(String, nullable=True)

2655 user_agent = Column(String, nullable=True)

2656

2657

2658class AccountDeletionReason(Base):

2659 __tablename__ = "account_deletion_reason"

2660

2661 id = Column(BigInteger, primary_key=True)

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

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

2664 reason = Column(String, nullable=True)

2665

2666 user = relationship("User")