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, unique=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

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

216

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

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

219

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

221 # accidental or they changed their mind

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

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

224 # validity of the undelete token

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

226

227 # hosting preferences

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

245

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

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

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

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

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

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

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

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

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

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

256 Enum(ParkingDetails), default=None

257 ) # CommonMark without images

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

259

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

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

262 # whether the user has filled in the contributor form

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

264

265 # number of onboarding emails sent

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

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

268

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

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

271 # opted out of the newsletter

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

273

274 # set to null to receive no digests

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

276 last_digest_sent: Mapped[datetime] = mapped_column(

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

278 )

279

280 # for changing their email

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

282

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

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

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

286

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

288

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

290

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

292 # ,-------------------,

293 # | Start |

294 # | phone = None | someone else

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

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

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

302 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------'

304 # | | ^ V | |

305 # ,-----------------, | | ,-------------------, | ,-----------------------,

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

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

312 # '-----------------' '-------------------' '-----------------------'

313

314 # randomly generated Luhn 6-digit string

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

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

317 )

318

319 phone_verification_sent: Mapped[datetime] = mapped_column(

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

321 )

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

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

324 )

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

326

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

328 # e.g. cus_JjoXHttuZopv0t

329 # for new US entity

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

331 # for old AU entity

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

333

334 has_passport_sex_gender_exception: Mapped[bool] = mapped_column(

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

336 )

337

338 # checking for phone verification

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

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

341 )

342

343 # whether this user has all emails turned off

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

345

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

347

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

349

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

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

352

353 last_antibot: Mapped[datetime] = mapped_column(

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

355 )

356

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

358

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

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

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

362

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

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

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

366

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

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

369 )

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

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

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

373 )

374 mod_notes: DynamicMapped[ModNote] = relationship(

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

376 )

377

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

379

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

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

382 )

383

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

385 init=False,

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

387 uselist=False,

388 back_populates="user",

389 )

390

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

392

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

394

395 __table_args__ = (

396 # Verified phone numbers should be unique

397 Index(

398 "ix_users_unique_phone",

399 phone,

400 unique=True,

401 postgresql_where=phone_verification_verified != None,

402 ),

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

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

405 Index(

406 "ix_users_unique_undelete_token",

407 undelete_token,

408 unique=True,

409 postgresql_where=undelete_token != None,

410 ),

411 Index(

412 "ix_users_unique_new_email_token",

413 new_email_token,

414 unique=True,

415 postgresql_where=new_email_token != None,

416 ),

417 Index(

418 "ix_users_unique_stripe_customer_id",

419 stripe_customer_id,

420 unique=True,

421 postgresql_where=stripe_customer_id != None,

422 ),

423 Index(

424 "ix_users_active",

425 id,

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

427 ),

428 Index(

429 "ix_users_geom_active",

430 geom,

431 id,

432 username,

433 postgresql_using="gist",

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

435 ),

436 Index(

437 "ix_users_by_id",

438 id,

439 postgresql_using="hash",

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

441 ),

442 Index(

443 "ix_users_by_username",

444 username,

445 postgresql_using="hash",

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

447 ),

448 Index(

449 "ix_users_visible_with_about_me",

450 id,

451 postgresql_where=and_(

452 banned_at.is_(None),

453 deleted_at.is_(None),

454 profile_gallery_id.isnot(None),

455 about_me_length >= COMPLETED_PROFILE_MINIMUM_CHAR_LENGTH,

456 ),

457 ),

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

459 CheckConstraint(

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

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

462 name="check_new_email_token_state",

463 ),

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

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

466 CheckConstraint(

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

468 name="phone_verified_conditions",

469 ),

470 # Email must match our regex

471 CheckConstraint(

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

473 name="valid_email",

474 ),

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

476 CheckConstraint(

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

478 name="undelete_nullity",

479 ),

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

481 CheckConstraint(

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

483 name="do_not_email_inactive",

484 ),

485 # Superusers must be editors

486 CheckConstraint(

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

488 name="superuser_is_editor",

489 ),

490 )

491

492 @hybrid_property

493 def has_completed_my_home(self) -> bool:

494 # completed my profile means that:

495 # 1. has filled out max_guests

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

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

498 return (

499 self.max_guests is not None

500 and self.sleeping_arrangement is not None

501 and (

502 self.about_place is not None

503 or self.other_host_info is not None

504 or self.sleeping_details is not None

505 or self.area is not None

506 or self.house_rules is not None

507 )

508 )

509

510 @has_completed_my_home.inplace.expression

511 @classmethod

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

513 return and_(

514 cls.max_guests != None,

515 cls.sleeping_arrangement != None,

516 or_(

517 cls.about_place != None,

518 cls.other_host_info != None,

519 cls.sleeping_details != None,

520 cls.area != None,

521 cls.house_rules != None,

522 ),

523 )

524

525 @hybrid_property

526 def jailed_missing_tos(self) -> bool:

527 return self.accepted_tos < TOS_VERSION

528

529 @hybrid_property

530 def jailed_missing_community_guidelines(self) -> bool:

531 return self.accepted_community_guidelines < GUIDELINES_VERSION

532

533 @hybrid_property

534 def jailed_pending_mod_notes(self) -> Any:

535 # mod_notes come from a backref in ModNote

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

537

538 @jailed_pending_mod_notes.inplace.expression

539 @classmethod

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

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

542

543 @hybrid_property

544 def jailed_pending_activeness_probe(self) -> Any:

545 # search for User.pending_activeness_probe

546 return self.pending_activeness_probe != None

547

548 @jailed_pending_activeness_probe.inplace.expression

549 @classmethod

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

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

552

553 @hybrid_property

554 def is_jailed(self) -> Any:

555 return (

556 self.jailed_missing_tos

557 | self.jailed_missing_community_guidelines

558 | self.is_missing_location

559 | self.jailed_pending_mod_notes

560 | self.jailed_pending_activeness_probe

561 )

562

563 @is_jailed.inplace.expression

564 @classmethod

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

566 return (

567 cls.jailed_missing_tos

568 | cls.jailed_missing_community_guidelines

569 | cls.is_missing_location

570 | cls.jailed_pending_mod_notes

571 | cls.jailed_pending_activeness_probe

572 )

573

574 @hybrid_property

575 def is_missing_location(self) -> bool:

576 return self.needs_to_update_location

577

578 @hybrid_property

579 def is_visible(self) -> bool:

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

581

582 @is_visible.inplace.expression

583 @classmethod

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

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

586

587 @hybrid_property

588 def is_shadowed(self) -> bool:

589 return self.shadowed_at is not None

590

591 @is_shadowed.inplace.expression

592 @classmethod

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

594 return cls.shadowed_at.is_not(None)

595

596 @property

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

598 return get_coordinates(self.geom)

599

600 @property

601 def display_joined(self) -> datetime:

602 """

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

604 """

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

606

607 @property

608 def display_last_active(self) -> datetime:

609 """

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

611 """

612 return last_active_coarsen(self.last_active)

613

614 @hybrid_property

615 def phone_is_verified(self) -> bool:

616 return (

617 self.phone_verification_verified is not None

618 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME

619 )

620

621 @phone_is_verified.inplace.expression

622 @classmethod

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

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

625 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME

626 )

627

628 @hybrid_property

629 def phone_code_expired(self) -> bool:

630 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME

631

632 def __repr__(self) -> str:

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

634

635

636class LanguageFluency(enum.Enum):

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

638 beginner = 1

639 conversational = 2

640 fluent = 3

641

642

643class LanguageAbility(Base, kw_only=True):

644 __tablename__ = "language_abilities"

645 __table_args__ = (

646 # Users can only have one language ability per language

647 UniqueConstraint("user_id", "language_code"),

648 )

649

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

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

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

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

654

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

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

657

658

659class RegionVisited(Base, kw_only=True):

660 __tablename__ = "regions_visited"

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

662

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

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

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

666

667

668class RegionLived(Base, kw_only=True):

669 __tablename__ = "regions_lived"

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

671

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

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

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

675

676

677class HostingMeetupStatusHistory(Base, kw_only=True):

678 """

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

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

681 """

682

683 __tablename__ = "hosting_meetup_status_history"

684

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

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

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

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

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

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

691

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

693

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