Coverage for app/backend/src/couchers/models/users.py: 100%

1import enum

2from datetime import date, datetime, timedelta

3from typing import TYPE_CHECKING, Any

5from geoalchemy2 import Geometry

6from sqlalchemy import (

7 ARRAY,

8 BigInteger,

9 Boolean,

10 CheckConstraint,

11 Computed,

12 Date,

13 DateTime,

14 Enum,

15 Float,

16 ForeignKey,

17 Index,

18 Integer,

19 Interval,

20 String,

21 UniqueConstraint,

22 and_,

23 func,

24 or_,

25 select,

26 text,

27)

28from sqlalchemy import LargeBinary as Binary

29from sqlalchemy.ext.hybrid import hybrid_property

30from sqlalchemy.orm import DynamicMapped, Mapped, column_property, mapped_column, relationship

31from sqlalchemy.sql import expression

32from sqlalchemy.sql.elements import ColumnElement

34from couchers.constants import (

35 COMPLETED_PROFILE_MINIMUM_CHAR_LENGTH,

36 EMAIL_REGEX,

37 GUIDELINES_VERSION,

38 PHONE_VERIFICATION_LIFETIME,

39 SMS_CODE_LIFETIME,

40 TOS_VERSION,

41)

42from couchers.models.activeness_probe import ActivenessProbe

43from couchers.models.base import Base, Geom

44from couchers.models.mod_note import ModNote

45from couchers.models.moderation import ModerationObjectType, ModerationState

46from couchers.models.static import Language, Region, TimezoneArea

47from couchers.utils import get_coordinates, last_active_coarsen, now

49if TYPE_CHECKING:

50 from couchers.models import UserBadge

51 from couchers.models.admin import UserAdminTag

52 from couchers.models.public_trips import PublicTrip

53 from couchers.models.rest import InviteCode, ModerationUserList

54 from couchers.models.uploads import PhotoGallery

57class HostingStatus(enum.Enum):

58 can_host = enum.auto()

59 maybe = enum.auto()

60 cant_host = enum.auto()

63class MeetupStatus(enum.Enum):

64 wants_to_meetup = enum.auto()

65 open_to_meetup = enum.auto()

66 does_not_want_to_meetup = enum.auto()

69class HostingMeetupStatusSource(enum.Enum):

70 # the statuses the account was created with

71 signup = enum.auto()

72 # the user edited their profile

73 profile_edit = enum.auto()

74 # the user enabled "do not email" in their notification settings

75 do_not_email = enum.auto()

76 # the user used the "do not email" quick link in an email

77 unsubscribe_link = enum.auto()

78 # the user responded to an activeness probe

79 activeness_probe_response = enum.auto()

80 # the user let an activeness probe expire, so we downgraded them

81 activeness_probe_expired = enum.auto()

84class SmokingLocation(enum.Enum):

85 yes = enum.auto()

86 window = enum.auto()

87 outside = enum.auto()

88 no = enum.auto()

91class SleepingArrangement(enum.Enum):

92 private = enum.auto()

93 common = enum.auto()

94 shared_room = enum.auto()

97class ParkingDetails(enum.Enum):

98 free_onsite = enum.auto()

99 free_offsite = enum.auto()

100 paid_onsite = enum.auto()

101 paid_offsite = enum.auto()

102

103

104class ProfilePublicVisibility(enum.Enum):

105 # no public info

106 nothing = enum.auto()

107 # only show on map, randomized, unclickable

108 map_only = enum.auto()

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

110 limited = enum.auto()

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

112 most = enum.auto()

113 # all but references

114 full = enum.auto()

115

116

117class User(Base, kw_only=True):

118 """

119 Basic user and profile details

120 """

121

122 __tablename__ = "users"

123 __moderation_author_column__ = "id"

124 __moderation_object_type__ = ModerationObjectType.user

125 __moderation_has_own_visibility_mechanism__ = True

126

127 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False)

128

129 username: Mapped[str] = mapped_column(String, unique=True)

130 email: Mapped[str] = mapped_column(String, index=True)

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

132 hashed_password: Mapped[bytes] = mapped_column(Binary)

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

134 phone: Mapped[str | None] = mapped_column(String, default=None, server_default=expression.null())

135 # language preference -- defaults to empty string

136 ui_language_preference: Mapped[str | None] = mapped_column(String, default=None, server_default="")

137

138 # timezones should always be UTC

139 ## location

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

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

142 geom: Mapped[Geom] = mapped_column(Geometry(geometry_type="POINT", srid=4326))

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

144 randomized_geom: Mapped[Geom | None] = mapped_column(Geometry(geometry_type="POINT", srid=4326), default=None)

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

146 geom_radius: Mapped[float] = mapped_column(Float)

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

148 city: Mapped[str] = mapped_column(String)

149 # "Grew up in" on profile

150 hometown: Mapped[str | None] = mapped_column(String, default=None)

151

152 regions_visited: Mapped[list[Region]] = relationship(

153 init=False, secondary="regions_visited", order_by="Region.name"

154 )

155 regions_lived: Mapped[list[Region]] = relationship(init=False, secondary="regions_lived", order_by="Region.name")

156

157 timezone = column_property(

158 select(TimezoneArea.tzid).where(func.ST_Contains(TimezoneArea.geom, geom)).limit(1).scalar_subquery(),

159 deferred=True,

160 )

161

162 joined: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False)

163 last_active: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False)

164 profile_last_updated: Mapped[datetime] = mapped_column(

165 DateTime(timezone=True), server_default=func.now(), init=False

166 )

167

168 public_visibility: Mapped[ProfilePublicVisibility] = mapped_column(

169 Enum(ProfilePublicVisibility), server_default="map_only", init=False

170 )

171 has_modified_public_visibility: Mapped[bool] = mapped_column(Boolean, server_default=expression.false(), init=False)

172

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

174 last_notified_message_id: Mapped[int] = mapped_column(BigInteger, default=0)

175 # same as above for host requests

176 last_notified_request_message_id: Mapped[int] = mapped_column(BigInteger, server_default=text("0"), init=False)

177

178 # display name

179 name: Mapped[str] = mapped_column(String)

180 gender: Mapped[str] = mapped_column(String)

181 pronouns: Mapped[str | None] = mapped_column(String, default=None)

182 birthdate: Mapped[date] = mapped_column(Date) # in the timezone of birthplace

183

184 # Profile photo gallery for this user (photos about themselves)

185 # The first photo in the gallery (by position) is used as the avatar

186 profile_gallery_id: Mapped[int | None] = mapped_column(ForeignKey("photo_galleries.id"), default=None)

187

188 hosting_status: Mapped[HostingStatus] = mapped_column(Enum(HostingStatus))

189 meetup_status: Mapped[MeetupStatus] = mapped_column(Enum(MeetupStatus), server_default="open_to_meetup", init=False)

190

191 # community standing score

192 community_standing: Mapped[float | None] = mapped_column(Float, default=None)

193

194 occupation: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

195 education: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

196

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

198 about_me: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

199 # kept in sync by the database so profile-completeness checks never detoast about_me; doing so was the dominant

200 # cost of both the lite_users refresh and the profile metrics, which scan every user several times a minute

201 about_me_length: Mapped[int] = mapped_column(

202 Integer, Computed("coalesce(character_length(about_me), 0)", persisted=True), init=False

203 )

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

205 things_i_like: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

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

207 about_place: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

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

209 additional_information: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

210

211 banned_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)

212 deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)

213 shadowed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)

214 moderation_auto_approve_deadline_seconds: Mapped[int | None] = mapped_column(Integer, default=None)

215

216 moderation_state_id: Mapped[int] = mapped_column(ForeignKey("moderation_states.id"), index=True)

217

218 is_superuser: Mapped[bool] = mapped_column(Boolean, server_default=expression.false(), init=False)

219 is_editor: Mapped[bool] = mapped_column(Boolean, server_default=expression.false(), init=False)

220

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

222 # accidental or they changed their mind

223 # constraints make sure these are non-null only if deleted_at is set and that these are null in unison

224 undelete_token: Mapped[str | None] = mapped_column(String, default=None)

225 # validity of the undelete token

226 undelete_until: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)

227

228 # hosting preferences

229 max_guests: Mapped[int | None] = mapped_column(Integer, default=None)

230 last_minute: Mapped[bool | None] = mapped_column(Boolean, default=None)

231 has_pets: Mapped[bool | None] = mapped_column(Boolean, default=None)

232 accepts_pets: Mapped[bool | None] = mapped_column(Boolean, default=None)

233 pet_details: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

234 has_kids: Mapped[bool | None] = mapped_column(Boolean, default=None)

235 accepts_kids: Mapped[bool | None] = mapped_column(Boolean, default=None)

236 kid_details: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

237 has_housemates: Mapped[bool | None] = mapped_column(Boolean, default=None)

238 housemate_details: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

239 wheelchair_accessible: Mapped[bool | None] = mapped_column(Boolean, default=None)

240 smoking_allowed: Mapped[SmokingLocation | None] = mapped_column(Enum(SmokingLocation), default=None)

241 smokes_at_home: Mapped[bool | None] = mapped_column(Boolean, default=None)

242 drinking_allowed: Mapped[bool | None] = mapped_column(Boolean, default=None)

243 drinks_at_home: Mapped[bool | None] = mapped_column(Boolean, default=None)

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

245 other_host_info: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

246

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

248 sleeping_arrangement: Mapped[SleepingArrangement | None] = mapped_column(Enum(SleepingArrangement), default=None)

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

250 sleeping_details: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

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

252 area: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

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

254 house_rules: Mapped[str | None] = mapped_column(String, default=None) # CommonMark without images

255 parking: Mapped[bool | None] = mapped_column(Boolean, default=None)

256 parking_details: Mapped[ParkingDetails | None] = mapped_column(

257 Enum(ParkingDetails), default=None

258 ) # CommonMark without images

259 camping_ok: Mapped[bool | None] = mapped_column(Boolean, default=None)

260

261 accepted_tos: Mapped[int] = mapped_column(Integer, default=0)

262 accepted_community_guidelines: Mapped[int] = mapped_column(Integer, server_default="0", init=False)

263 # whether the user has filled in the contributor form

264 filled_contributor_form: Mapped[bool] = mapped_column(Boolean, server_default=expression.false(), init=False)

265

266 # number of onboarding emails sent

267 onboarding_emails_sent: Mapped[int] = mapped_column(Integer, server_default="0", init=False)

268 last_onboarding_email_sent: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)

269

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

271 in_sync_with_newsletter: Mapped[bool] = mapped_column(Boolean, server_default=expression.false(), init=False)

272 # opted out of the newsletter

273 opt_out_of_newsletter: Mapped[bool] = mapped_column(Boolean, server_default=expression.false(), init=False)

274

275 # set to null to receive no digests

276 digest_frequency: Mapped[timedelta | None] = mapped_column(Interval, default=None)

277 last_digest_sent: Mapped[datetime] = mapped_column(

278 DateTime(timezone=True), server_default=text("to_timestamp(0)"), init=False

279 )

280

281 # for changing their email

282 new_email: Mapped[str | None] = mapped_column(String, default=None)

283

284 new_email_token: Mapped[str | None] = mapped_column(String, default=None)

285 new_email_token_created: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)

286 new_email_token_expiry: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None)

287

288 recommendation_score: Mapped[float] = mapped_column(Float, server_default="0", init=False)

289

290 mod_score: Mapped[float] = mapped_column(Float, server_default="1", init=False)

291

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

293 # ,-------------------,

294 # | Start |

295 # | phone = None | someone else

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

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

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

303 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------'

305 # | | ^ V | |

306 # ,-----------------, | | ,-------------------, | ,-----------------------,

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

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

313 # '-----------------' '-------------------' '-----------------------'

314

315 # randomly generated Luhn 6-digit string

316 phone_verification_token: Mapped[str | None] = mapped_column(

317 String(6), default=None, server_default=expression.null(), init=False

318 )

319

320 phone_verification_sent: Mapped[datetime] = mapped_column(

321 DateTime(timezone=True), server_default=text("to_timestamp(0)"), init=False

322 )

323 phone_verification_verified: Mapped[datetime | None] = mapped_column(

324 DateTime(timezone=True), default=None, server_default=expression.null(), init=False

325 )

326 phone_verification_attempts: Mapped[int] = mapped_column(Integer, server_default=text("0"), init=False)

327

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

329 # e.g. cus_JjoXHttuZopv0t

330 # for new US entity

331 stripe_customer_id: Mapped[str | None] = mapped_column(String, default=None)

332 # for old AU entity

333 stripe_customer_id_old: Mapped[str | None] = mapped_column(String, default=None)

334

335 has_passport_sex_gender_exception: Mapped[bool] = mapped_column(

336 Boolean, server_default=expression.false(), init=False

337 )

338

339 # checking for phone verification

340 last_donated: Mapped[datetime | None] = mapped_column(

341 DateTime(timezone=True), default=None, server_default=expression.null()

342 )

343

344 # whether this user has all emails turned off

345 do_not_email: Mapped[bool] = mapped_column(Boolean, server_default=expression.false(), init=False)

346

347 profile_gallery: Mapped[PhotoGallery | None] = relationship(init=False, foreign_keys="User.profile_gallery_id")

348

349 admin_note: Mapped[str] = mapped_column(String, server_default=text("''"), init=False)

350

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

352 needs_to_update_location: Mapped[bool] = mapped_column(Boolean, server_default=expression.false(), init=False)

353

354 last_antibot: Mapped[datetime] = mapped_column(

355 DateTime(timezone=True), server_default=text("to_timestamp(0)"), init=False

356 )

357

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

359

360 # ID of the invite code used to sign up (if any)

361 invite_code_id: Mapped[str | None] = mapped_column(ForeignKey("invite_codes.id"), default=None)

362 invite_code: Mapped[InviteCode | None] = relationship(init=False, foreign_keys=[invite_code_id])

363

364 # Signup motivations - how they heard about us and what they want to do

365 heard_about_couchers: Mapped[str | None] = mapped_column(String, default=None)

366 signup_motivations: Mapped[list[str] | None] = mapped_column(ARRAY(String), default=None)

367

368 moderation_user_lists: Mapped[list[ModerationUserList]] = relationship(

369 init=False, secondary="moderation_user_list_members", back_populates="users"

370 )

371 language_abilities: Mapped[list[LanguageAbility]] = relationship(init=False, back_populates="user")

372 galleries: Mapped[list[PhotoGallery]] = relationship(

373 init=False, foreign_keys="PhotoGallery.owner_user_id", back_populates="owner_user"

374 )

375 mod_notes: DynamicMapped[ModNote] = relationship(

376 init=False, foreign_keys="ModNote.user_id", back_populates="user", lazy="dynamic"

377 )

378

379 badges: Mapped[list[UserBadge]] = relationship(init=False, back_populates="user")

380

381 admin_tags: Mapped[list[UserAdminTag]] = relationship(

382 init=False, foreign_keys="UserAdminTag.user_id", overlaps="user"

383 )

384

385 pending_activeness_probe: Mapped[ActivenessProbe | None] = relationship(

386 init=False,

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

388 uselist=False,

389 back_populates="user",

390 )

391

392 public_trips: Mapped[list[PublicTrip]] = relationship(init=False, back_populates="user")

393

394 moderation_state: Mapped[ModerationState] = relationship(init=False)

395

396 __table_args__ = (

397 # Verified phone numbers should be unique

398 Index(

399 "ix_users_unique_phone",

400 phone,

401 unique=True,

402 postgresql_where=phone_verification_verified != None,

403 ),

404 Index(

405 "ix_users_unique_email",

406 email,

407 unique=True,

408 postgresql_where=deleted_at == None,

409 ),

410 # These three are each looked up by equality as though the value named exactly one user, so the database

411 # needs to enforce that; partial as the columns are null for almost every user

412 Index(

413 "ix_users_unique_undelete_token",

414 undelete_token,

415 unique=True,

416 postgresql_where=undelete_token != None,

417 ),

418 Index(

419 "ix_users_unique_new_email_token",

420 new_email_token,

421 unique=True,

422 postgresql_where=new_email_token != None,

423 ),

424 Index(

425 "ix_users_unique_stripe_customer_id",

426 stripe_customer_id,

427 unique=True,

428 postgresql_where=stripe_customer_id != None,

429 ),

430 Index(

431 "ix_users_active",

432 id,

433 postgresql_where=and_(banned_at.is_(None), deleted_at.is_(None)),

434 ),

435 Index(

436 "ix_users_geom_active",

437 geom,

438 id,

439 username,

440 postgresql_using="gist",

441 postgresql_where=and_(banned_at.is_(None), deleted_at.is_(None)),

442 ),

443 Index(

444 "ix_users_by_id",

445 id,

446 postgresql_using="hash",

447 postgresql_where=and_(banned_at.is_(None), deleted_at.is_(None)),

448 ),

449 Index(

450 "ix_users_by_username",

451 username,

452 postgresql_using="hash",

453 postgresql_where=and_(banned_at.is_(None), deleted_at.is_(None)),

454 ),

455 Index(

456 "ix_users_visible_with_about_me",

457 id,

458 postgresql_where=and_(

459 banned_at.is_(None),

460 deleted_at.is_(None),

461 profile_gallery_id.isnot(None),

462 about_me_length >= COMPLETED_PROFILE_MINIMUM_CHAR_LENGTH,

463 ),

464 ),

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

466 CheckConstraint(

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

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

469 name="check_new_email_token_state",

470 ),

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

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

473 CheckConstraint(

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

475 name="phone_verified_conditions",

476 ),

477 # Email must match our regex

478 CheckConstraint(

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

480 name="valid_email",

481 ),

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

483 CheckConstraint(

484 "((undelete_token IS NULL) = (undelete_until IS NULL)) AND ((undelete_token IS NULL) OR deleted_at IS NOT NULL)",

485 name="undelete_nullity",

486 ),

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

488 CheckConstraint(

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

490 name="do_not_email_inactive",

491 ),

492 # Superusers must be editors

493 CheckConstraint(

494 "(is_superuser IS FALSE) OR (is_editor IS TRUE)",

495 name="superuser_is_editor",

496 ),

497 )

498

499 @hybrid_property

500 def has_completed_my_home(self) -> bool:

501 # completed my profile means that:

502 # 1. has filled out max_guests

503 # 2. has filled out sleeping_arrangement (sleeping privacy)

504 # 3. has some text in at least one of the my home free text fields

505 return (

506 self.max_guests is not None

507 and self.sleeping_arrangement is not None

508 and (

509 self.about_place is not None

510 or self.other_host_info is not None

511 or self.sleeping_details is not None

512 or self.area is not None

513 or self.house_rules is not None

514 )

515 )

516

517 @has_completed_my_home.inplace.expression

518 @classmethod

519 def _has_completed_my_home_expression(cls) -> ColumnElement[bool]:

520 return and_(

521 cls.max_guests != None,

522 cls.sleeping_arrangement != None,

523 or_(

524 cls.about_place != None,

525 cls.other_host_info != None,

526 cls.sleeping_details != None,

527 cls.area != None,

528 cls.house_rules != None,

529 ),

530 )

531

532 @hybrid_property

533 def jailed_missing_tos(self) -> bool:

534 return self.accepted_tos < TOS_VERSION

535

536 @hybrid_property

537 def jailed_missing_community_guidelines(self) -> bool:

538 return self.accepted_community_guidelines < GUIDELINES_VERSION

539

540 @hybrid_property

541 def jailed_pending_mod_notes(self) -> Any:

542 # mod_notes come from a backref in ModNote

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

544

545 @jailed_pending_mod_notes.inplace.expression

546 @classmethod

547 def _jailed_pending_mod_notes_expression(cls) -> ColumnElement[bool]:

548 return select(ModNote.id).where(ModNote.user_id == cls.id, ModNote.is_pending).exists()

549

550 @hybrid_property

551 def jailed_pending_activeness_probe(self) -> Any:

552 # search for User.pending_activeness_probe

553 return self.pending_activeness_probe != None

554

555 @jailed_pending_activeness_probe.inplace.expression

556 @classmethod

557 def _jailed_pending_activeness_probe_expression(cls) -> ColumnElement[bool]:

558 return select(ActivenessProbe.id).where(ActivenessProbe.user_id == cls.id, ActivenessProbe.is_pending).exists()

559

560 @hybrid_property

561 def is_jailed(self) -> Any:

562 return (

563 self.jailed_missing_tos

564 | self.jailed_missing_community_guidelines

565 | self.is_missing_location

566 | self.jailed_pending_mod_notes

567 | self.jailed_pending_activeness_probe

568 )

569

570 @is_jailed.inplace.expression

571 @classmethod

572 def _is_jailed_expression(cls) -> ColumnElement[bool]:

573 return (

574 cls.jailed_missing_tos

575 | cls.jailed_missing_community_guidelines

576 | cls.is_missing_location

577 | cls.jailed_pending_mod_notes

578 | cls.jailed_pending_activeness_probe

579 )

580

581 @hybrid_property

582 def is_missing_location(self) -> bool:

583 return self.needs_to_update_location

584

585 @hybrid_property

586 def is_visible(self) -> bool:

587 return self.banned_at is None and self.deleted_at is None

588

589 @is_visible.inplace.expression

590 @classmethod

591 def _is_visible_expression(cls) -> ColumnElement[bool]:

592 return and_(cls.banned_at.is_(None), cls.deleted_at.is_(None))

593

594 @hybrid_property

595 def is_shadowed(self) -> bool:

596 return self.shadowed_at is not None

597

598 @is_shadowed.inplace.expression

599 @classmethod

600 def _is_shadowed_expression(cls) -> ColumnElement[bool]:

601 return cls.shadowed_at.is_not(None)

602

603 @hybrid_property

604 def is_banned(self) -> bool:

605 return self.banned_at is not None

606

607 @is_banned.inplace.expression

608 @classmethod

609 def _is_banned_expression(cls) -> ColumnElement[bool]:

610 return cls.banned_at.is_not(None)

611

612 @hybrid_property

613 def reserves_email(self) -> bool:

614 return self.deleted_at is None or self.banned_at is not None

615

616 @reserves_email.inplace.expression

617 @classmethod

618 def _reserves_email_expression(cls) -> ColumnElement[bool]:

619 return or_(cls.deleted_at.is_(None), cls.banned_at.is_not(None))

620

621 @property

622 def coordinates(self) -> tuple[float, float]:

623 return get_coordinates(self.geom)

624

625 @property

626 def display_joined(self) -> datetime:

627 """

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

629 """

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

631

632 @property

633 def display_last_active(self) -> datetime:

634 """

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

636 """

637 return last_active_coarsen(self.last_active)

638

639 @hybrid_property

640 def phone_is_verified(self) -> bool:

641 return (

642 self.phone_verification_verified is not None

643 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME

644 )

645

646 @phone_is_verified.inplace.expression

647 @classmethod

648 def _phone_is_verified_expression(cls) -> ColumnElement[bool]:

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

650 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME

651 )

652

653 @hybrid_property

654 def phone_code_expired(self) -> bool:

655 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME

656

657 def __repr__(self) -> str:

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

659

660

661class LanguageFluency(enum.Enum):

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

663 beginner = 1

664 conversational = 2

665 fluent = 3

666

667

668class LanguageAbility(Base, kw_only=True):

669 __tablename__ = "language_abilities"

670 __table_args__ = (

671 # Users can only have one language ability per language

672 UniqueConstraint("user_id", "language_code"),

673 )

674

675 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False)

676 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)

677 language_code: Mapped[str] = mapped_column(ForeignKey("languages.code", deferrable=True))

678 fluency: Mapped[LanguageFluency] = mapped_column(Enum(LanguageFluency))

679

680 user: Mapped[User] = relationship(init=False, back_populates="language_abilities")

681 language: Mapped[Language] = relationship(init=False)

682

683

684class RegionVisited(Base, kw_only=True):

685 __tablename__ = "regions_visited"

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

687

688 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False)

689 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)

690 region_code: Mapped[str] = mapped_column(ForeignKey("regions.code", deferrable=True))

691

692

693class RegionLived(Base, kw_only=True):

694 __tablename__ = "regions_lived"

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

696

697 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False)

698 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)

699 region_code: Mapped[str] = mapped_column(ForeignKey("regions.code", deferrable=True))

700

701

702class HostingMeetupStatusHistory(Base, kw_only=True):

703 """

704 Append-only snapshot log of users' hosting and meetup statuses. A row is written whenever either status changes, so

705 a user's statuses at any past time are those of their newest row at or before that time.

706 """

707

708 __tablename__ = "hosting_meetup_status_history"

709

710 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False)

711 time: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False)

712 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))

713 source: Mapped[HostingMeetupStatusSource] = mapped_column(Enum(HostingMeetupStatusSource))

714 hosting_status: Mapped[HostingStatus] = mapped_column(Enum(HostingStatus))

715 meetup_status: Mapped[MeetupStatus] = mapped_column(Enum(MeetupStatus))

716

717 user: Mapped[User] = relationship(init=False)

718

719 __table_args__ = (Index("ix_hosting_meetup_status_history_user_id_time", user_id, time),)

720

721

722class UserEmailHistory(Base, kw_only=True):

723 """

724 Append-only log of users' email addresses. A row is written whenever a user's email is set, so a user's email at any

725 past time is that of their newest row at or before that time.

726 """

727

728 __tablename__ = "user_email_history"

729

730 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False)

731 time: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False)

732 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))

733 email: Mapped[str] = mapped_column(String)

734

735 user: Mapped[User] = relationship(init=False)

736

737 __table_args__ = (

738 Index("ix_user_email_history_user_id_time", user_id, time),

739 Index("ix_user_email_history_email", email),

740 )

741

742

743class UserRankingOffset(Base, kw_only=True):

744 """

745 Offsets added to users' recommendation_score in user search for viewers on a given variant (picked by the

746 search_ranking_offsets_variant flag).

747 """

748

749 __tablename__ = "user_ranking_offsets"

750

751 variant: Mapped[str] = mapped_column(String, primary_key=True)

752 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), primary_key=True)

753 offset: Mapped[float] = mapped_column(Float)