Coverage for src/couchers/models/users.py: 100%
229 statements
« prev ^ index » next coverage.py v7.11.0, created at 2025-12-25 10:58 +0000
1import enum
2from datetime import date, datetime, timedelta
3from typing import TYPE_CHECKING, Any
5from geoalchemy2 import Geometry
6from sqlalchemy import (
7 BigInteger,
8 Boolean,
9 CheckConstraint,
10 Date,
11 DateTime,
12 Enum,
13 Float,
14 ForeignKey,
15 Index,
16 Integer,
17 Interval,
18 String,
19 UniqueConstraint,
20 and_,
21 func,
22 not_,
23 or_,
24 text,
25)
26from sqlalchemy import LargeBinary as Binary
27from sqlalchemy import select as sa_select
28from sqlalchemy.ext.hybrid import hybrid_property
29from sqlalchemy.orm import Mapped, column_property, mapped_column, relationship
30from sqlalchemy.sql import expression
32from couchers.constants import (
33 EMAIL_REGEX,
34 GUIDELINES_VERSION,
35 PHONE_VERIFICATION_LIFETIME,
36 SMS_CODE_LIFETIME,
37 TOS_VERSION,
38)
39from couchers.models.activeness_probe import ActivenessProbe
40from couchers.models.base import Base, Geom
41from couchers.models.mod_note import ModNote
42from couchers.models.static import Language, Region, TimezoneArea
43from couchers.models.uploads import Upload
44from couchers.utils import get_coordinates, last_active_coarsen, now
46if TYPE_CHECKING:
47 from couchers.models.rest import InviteCode, ModerationUserList
48 from couchers.models.uploads import PhotoGallery
51class HostingStatus(enum.Enum):
52 can_host = enum.auto()
53 maybe = enum.auto()
54 cant_host = enum.auto()
57class MeetupStatus(enum.Enum):
58 wants_to_meetup = enum.auto()
59 open_to_meetup = enum.auto()
60 does_not_want_to_meetup = enum.auto()
63class SmokingLocation(enum.Enum):
64 yes = enum.auto()
65 window = enum.auto()
66 outside = enum.auto()
67 no = enum.auto()
70class SleepingArrangement(enum.Enum):
71 private = enum.auto()
72 common = enum.auto()
73 shared_room = enum.auto()
76class ParkingDetails(enum.Enum):
77 free_onsite = enum.auto()
78 free_offsite = enum.auto()
79 paid_onsite = enum.auto()
80 paid_offsite = enum.auto()
83class ProfilePublicVisibility(enum.Enum):
84 # no public info
85 nothing = enum.auto()
86 # only show on map, randomized, unclickable
87 map_only = enum.auto()
88 # name, gender, location, hosting/meetup status, badges, number of references, and signup time
89 limited = enum.auto()
90 # full about me except additional info (hide my home)
91 most = enum.auto()
92 # all but references
93 full = enum.auto()
96class User(Base):
97 """
98 Basic user and profile details
99 """
101 __tablename__ = "users"
103 id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
105 username: Mapped[str] = mapped_column(String, unique=True)
106 email: Mapped[str] = mapped_column(String, unique=True)
107 # stored in libsodium hash format, can be null for email login
108 hashed_password: Mapped[bytes] = mapped_column(Binary)
109 # phone number in E.164 format with leading +, for example "+46701740605"
110 phone: Mapped[str | None] = mapped_column(String, nullable=True, server_default=expression.null())
111 # language preference -- defaults to empty string
112 ui_language_preference: Mapped[str | None] = mapped_column(String, nullable=True, server_default="")
114 # timezones should always be UTC
115 ## location
116 # point describing their location. EPSG4326 is the SRS (spatial ref system, = way to describe a point on earth) used
117 # by GPS, it has the WGS84 geoid with lat/lon
118 geom: Mapped[Geom] = mapped_column(Geometry(geometry_type="POINT", srid=4326))
119 # randomized coordinates within a radius of 0.02-0.1 degrees, equates to about 2-10 km
120 randomized_geom: Mapped[Geom | None] = mapped_column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
121 # their display location (displayed to other users), in meters
122 geom_radius: Mapped[float] = mapped_column(Float)
123 # the display address (text) shown on their profile
124 city: Mapped[str] = mapped_column(String)
125 # "Grew up in" on profile
126 hometown: Mapped[str | None] = mapped_column(String, nullable=True)
128 regions_visited: Mapped[list["Region"]] = relationship(
129 "Region", secondary="regions_visited", order_by="Region.name"
130 )
131 regions_lived: Mapped[list["Region"]] = relationship("Region", secondary="regions_lived", order_by="Region.name")
133 timezone = column_property(
134 sa_select(TimezoneArea.tzid).where(func.ST_Contains(TimezoneArea.geom, geom)).limit(1).scalar_subquery(),
135 deferred=True,
136 )
138 joined: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
139 last_active: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
140 profile_last_updated: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
142 public_visibility: Mapped[ProfilePublicVisibility] = mapped_column(
143 Enum(ProfilePublicVisibility), server_default="map_only"
144 )
145 has_modified_public_visibility: Mapped[bool] = mapped_column(Boolean, server_default=expression.false())
147 # id of the last message that they received a notification about
148 last_notified_message_id: Mapped[int] = mapped_column(BigInteger, default=0)
149 # same as above for host requests
150 last_notified_request_message_id: Mapped[int] = mapped_column(BigInteger, server_default=text("0"))
152 # display name
153 name: Mapped[str] = mapped_column(String)
154 gender: Mapped[str] = mapped_column(String)
155 pronouns: Mapped[str | None] = mapped_column(String, nullable=True)
156 birthdate: Mapped[date] = mapped_column(Date) # in the timezone of birthplace
158 avatar_key: Mapped[str | None] = mapped_column(ForeignKey("uploads.key"), nullable=True)
160 # Profile photo gallery for this user (photos about themselves)
161 profile_gallery_id: Mapped[int | None] = mapped_column(ForeignKey("photo_galleries.id"), nullable=True)
163 hosting_status: Mapped[HostingStatus] = mapped_column(Enum(HostingStatus))
164 meetup_status: Mapped[MeetupStatus] = mapped_column(Enum(MeetupStatus), server_default="open_to_meetup")
166 # community standing score
167 community_standing: Mapped[float | None] = mapped_column(Float, nullable=True)
169 occupation: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
170 education: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
172 # "Who I am" under "About Me" tab
173 about_me: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
174 # "What I do in my free time" under "About Me" tab
175 things_i_like: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
176 # "About my home" under "My Home" tab
177 about_place: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
178 # "Additional information" under "About Me" tab
179 additional_information: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
181 is_banned: Mapped[bool] = mapped_column(Boolean, server_default=expression.false())
182 is_deleted: Mapped[bool] = mapped_column(Boolean, server_default=expression.false())
183 is_superuser: Mapped[bool] = mapped_column(Boolean, server_default=expression.false())
184 is_editor: Mapped[bool] = mapped_column(Boolean, server_default=expression.false())
186 # the undelete token allows a user to recover their account for a couple of days after deletion in case it was
187 # accidental or they changed their mind
188 # constraints make sure these are non-null only if is_deleted and that these are null in unison
189 undelete_token: Mapped[str | None] = mapped_column(String, nullable=True)
190 # validity of the undelete token
191 undelete_until: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
193 # hosting preferences
194 max_guests: Mapped[int | None] = mapped_column(Integer, nullable=True)
195 last_minute: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
196 has_pets: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
197 accepts_pets: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
198 pet_details: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
199 has_kids: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
200 accepts_kids: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
201 kid_details: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
202 has_housemates: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
203 housemate_details: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
204 wheelchair_accessible: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
205 smoking_allowed: Mapped[SmokingLocation | None] = mapped_column(Enum(SmokingLocation), nullable=True)
206 smokes_at_home: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
207 drinking_allowed: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
208 drinks_at_home: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
209 # "Additional information" under "My Home" tab
210 other_host_info: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
212 # "Sleeping privacy" (not long-form text)
213 sleeping_arrangement: Mapped[SleepingArrangement | None] = mapped_column(Enum(SleepingArrangement), nullable=True)
214 # "Sleeping arrangement" under "My Home" tab
215 sleeping_details: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
216 # "Local area information" under "My Home" tab
217 area: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
218 # "House rules" under "My Home" tab
219 house_rules: Mapped[str | None] = mapped_column(String, nullable=True) # CommonMark without images
220 parking: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
221 parking_details: Mapped[ParkingDetails | None] = mapped_column(
222 Enum(ParkingDetails), nullable=True
223 ) # CommonMark without images
224 camping_ok: Mapped[bool | None] = mapped_column(Boolean, nullable=True)
226 accepted_tos: Mapped[int] = mapped_column(Integer, default=0)
227 accepted_community_guidelines: Mapped[int] = mapped_column(Integer, server_default="0")
228 # whether the user has filled in the contributor form
229 filled_contributor_form: Mapped[bool] = mapped_column(Boolean, server_default=expression.false())
231 # number of onboarding emails sent
232 onboarding_emails_sent: Mapped[int] = mapped_column(Integer, server_default="0")
233 last_onboarding_email_sent: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
235 # whether we need to sync the user's newsletter preferences with the newsletter server
236 in_sync_with_newsletter: Mapped[bool] = mapped_column(Boolean, server_default=expression.false())
237 # opted out of the newsletter
238 opt_out_of_newsletter: Mapped[bool] = mapped_column(Boolean, server_default=expression.false())
240 # set to null to receive no digests
241 digest_frequency: Mapped[timedelta | None] = mapped_column(Interval, nullable=True)
242 last_digest_sent: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=text("to_timestamp(0)"))
244 # for changing their email
245 new_email: Mapped[str | None] = mapped_column(String, nullable=True)
247 new_email_token: Mapped[str | None] = mapped_column(String, nullable=True)
248 new_email_token_created: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
249 new_email_token_expiry: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
251 recommendation_score: Mapped[float] = mapped_column(Float, server_default="0")
253 # Columns for verifying their phone number. State chart:
254 # ,-------------------,
255 # | Start |
256 # | phone = None | someone else
257 # ,-----------------, | token = None | verifies ,-----------------------,
258 # | Code Expired | | sent = 1970 or zz | phone xx | Verification Expired |
259 # | phone = xx | time passes | verified = None | <------, | phone = xx |
260 # | token = yy | <------------, | attempts = 0 | | | token = None |
261 # | sent = zz (exp.)| | '-------------------' | | sent = zz |
262 # | verified = None | | V ^ +-----------< | verified = ww (exp.) |
263 # | attempts = 0..2 | >--, | | | ChangePhone("") | | attempts = 0 |
264 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------'
265 # | | | | ChangePhone(xx) | ^ time passes
266 # | | ^ V | |
267 # ,-----------------, | | ,-------------------, | ,-----------------------,
268 # | Too Many | >--' '--< | Code sent | >------+ | Verified |
269 # | phone = xx | | phone = xx | | | phone = xx |
270 # | token = yy | VerifyPhone(wrong)| token = yy | '-----------< | token = None |
271 # | sent = zz | <------+--------< | sent = zz | | sent = zz |
272 # | verified = None | | | verified = None | VerifyPhone(correct) | verified = ww |
273 # | attempts = 3 | '--------> | attempts = 0..2 | >------------------> | attempts = 0 |
274 # '-----------------' '-------------------' '-----------------------'
276 # randomly generated Luhn 6-digit string
277 phone_verification_token: Mapped[str | None] = mapped_column(
278 String(6), nullable=True, server_default=expression.null()
279 )
281 phone_verification_sent: Mapped[datetime] = mapped_column(
282 DateTime(timezone=True), server_default=text("to_timestamp(0)")
283 )
284 phone_verification_verified: Mapped[datetime | None] = mapped_column(
285 DateTime(timezone=True), nullable=True, server_default=expression.null()
286 )
287 phone_verification_attempts: Mapped[int] = mapped_column(Integer, server_default=text("0"))
289 # the stripe customer identifier if the user has donated to Couchers
290 # e.g. cus_JjoXHttuZopv0t
291 # for new US entity
292 stripe_customer_id: Mapped[str | None] = mapped_column(String, nullable=True)
293 # for old AU entity
294 stripe_customer_id_old: Mapped[str | None] = mapped_column(String, nullable=True)
296 has_passport_sex_gender_exception: Mapped[bool] = mapped_column(Boolean, server_default=expression.false())
298 # checking for phone verification
299 last_donated: Mapped[datetime | None] = mapped_column(
300 DateTime(timezone=True), nullable=True, server_default=expression.null()
301 )
303 # whether this user has all emails turned off
304 do_not_email: Mapped[bool] = mapped_column(Boolean, server_default=expression.false())
306 avatar: Mapped[Upload | None] = relationship("Upload", foreign_keys="User.avatar_key")
307 profile_gallery: Mapped["PhotoGallery | None"] = relationship(
308 "PhotoGallery", foreign_keys="User.profile_gallery_id"
309 )
311 admin_note: Mapped[str] = mapped_column(String, server_default=text("''"))
313 # whether mods have marked this user has having to update their location
314 needs_to_update_location: Mapped[bool] = mapped_column(Boolean, server_default=expression.false())
316 last_antibot: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=text("to_timestamp(0)"))
318 age = column_property(func.date_part("year", func.age(birthdate)))
320 # ID of the invite code used to sign up (if any)
321 invite_code_id: Mapped[int | None] = mapped_column(ForeignKey("invite_codes.id"), nullable=True)
322 invite_code: Mapped["InviteCode | None"] = relationship("InviteCode", foreign_keys=[invite_code_id])
324 moderation_user_lists: Mapped[list["ModerationUserList"]] = relationship(
325 "ModerationUserList", secondary="moderation_user_list_members", back_populates="users"
326 )
327 language_abilities: Mapped[list["LanguageAbility"]] = relationship("LanguageAbility", back_populates="user")
328 galleries: Mapped[list["PhotoGallery"]] = relationship(
329 "PhotoGallery", foreign_keys="PhotoGallery.owner_user_id", back_populates="owner_user"
330 )
332 __table_args__ = (
333 # Verified phone numbers should be unique
334 Index(
335 "ix_users_unique_phone",
336 phone,
337 unique=True,
338 postgresql_where=phone_verification_verified != None,
339 ),
340 Index(
341 "ix_users_active",
342 id,
343 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
344 ),
345 # create index on users(geom, id, username) where not is_banned and not is_deleted and geom is not null;
346 Index(
347 "ix_users_geom_active",
348 geom,
349 id,
350 username,
351 postgresql_using="gist",
352 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
353 ),
354 Index(
355 "ix_users_by_id",
356 id,
357 postgresql_using="hash",
358 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
359 ),
360 Index(
361 "ix_users_by_username",
362 username,
363 postgresql_using="hash",
364 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
365 ),
366 # There are two possible states for new_email_token, new_email_token_created, and new_email_token_expiry
367 CheckConstraint(
368 "(new_email_token IS NOT NULL AND new_email_token_created IS NOT NULL AND new_email_token_expiry IS NOT NULL) OR \
369 (new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL)",
370 name="check_new_email_token_state",
371 ),
372 # Whenever a phone number is set, it must either be pending verification or already verified.
373 # Exactly one of the following must always be true: not phone, token, verified.
374 CheckConstraint(
375 "(phone IS NULL)::int + (phone_verification_verified IS NOT NULL)::int + (phone_verification_token IS NOT NULL)::int = 1",
376 name="phone_verified_conditions",
377 ),
378 # Email must match our regex
379 CheckConstraint(
380 f"email ~ '{EMAIL_REGEX}'",
381 name="valid_email",
382 ),
383 # Undelete token + time are coupled: either both null or neither; and if they're not null then the account is deleted
384 CheckConstraint(
385 "((undelete_token IS NULL) = (undelete_until IS NULL)) AND ((undelete_token IS NULL) OR is_deleted)",
386 name="undelete_nullity",
387 ),
388 # If the user disabled all emails, then they can't host or meet up
389 CheckConstraint(
390 "(do_not_email IS FALSE) OR ((hosting_status = 'cant_host') AND (meetup_status = 'does_not_want_to_meetup'))",
391 name="do_not_email_inactive",
392 ),
393 # Superusers must be editors
394 CheckConstraint(
395 "(is_superuser IS FALSE) OR (is_editor IS TRUE)",
396 name="superuser_is_editor",
397 ),
398 )
400 @hybrid_property
401 def has_completed_profile(self) -> bool:
402 return self.avatar_key is not None and self.about_me is not None and len(self.about_me) >= 150
404 @has_completed_profile.expression
405 def has_completed_profile(cls):
406 return (cls.avatar_key != None) & (func.character_length(cls.about_me) >= 150)
408 @hybrid_property
409 def has_completed_my_home(self) -> bool:
410 # completed my profile means that:
411 # 1. has filled out max_guests
412 # 2. has filled out sleeping_arrangement (sleeping privacy)
413 # 3. has some text in at least one of the my home free text fields
414 return (
415 self.max_guests is not None
416 and self.sleeping_arrangement is not None
417 and (
418 self.about_place is not None
419 or self.other_host_info is not None
420 or self.sleeping_details is not None
421 or self.area is not None
422 or self.house_rules is not None
423 )
424 )
426 @has_completed_my_home.expression
427 def has_completed_my_home(cls):
428 return and_(
429 cls.max_guests != None,
430 cls.sleeping_arrangement != None,
431 or_(
432 cls.about_place != None,
433 cls.other_host_info != None,
434 cls.sleeping_details != None,
435 cls.area != None,
436 cls.house_rules != None,
437 ),
438 )
440 @hybrid_property
441 def jailed_missing_tos(self) -> bool:
442 return self.accepted_tos < TOS_VERSION
444 @hybrid_property
445 def jailed_missing_community_guidelines(self) -> bool:
446 return self.accepted_community_guidelines < GUIDELINES_VERSION
448 @hybrid_property
449 def jailed_pending_mod_notes(self) -> Any:
450 # mod_notes come from a backref in ModNote
451 return self.mod_notes.where(ModNote.is_pending).count() > 0 # type: ignore[attr-defined]
453 @hybrid_property
454 def jailed_pending_activeness_probe(self) -> Any:
455 # search for User.pending_activeness_probe
456 return self.pending_activeness_probe != None # type: ignore[attr-defined]
458 @hybrid_property
459 def is_jailed(self) -> Any:
460 return (
461 self.jailed_missing_tos
462 | self.jailed_missing_community_guidelines
463 | self.is_missing_location
464 | self.jailed_pending_mod_notes
465 | self.jailed_pending_activeness_probe
466 )
468 @hybrid_property
469 def is_missing_location(self) -> bool:
470 return self.needs_to_update_location
472 @hybrid_property
473 def is_visible(self) -> bool:
474 return not self.is_banned and not self.is_deleted
476 @is_visible.expression
477 def is_visible(cls):
478 return ~(cls.is_banned | cls.is_deleted)
480 @property
481 def coordinates(self) -> tuple[float, float]:
482 return get_coordinates(self.geom)
484 @property
485 def display_joined(self) -> datetime:
486 """
487 Returns the last active time rounded down to the nearest hour.
488 """
489 return self.joined.replace(minute=0, second=0, microsecond=0)
491 @property
492 def display_last_active(self) -> datetime:
493 """
494 Returns the last active time rounded down whatever is the "last active" coarsening.
495 """
496 return last_active_coarsen(self.last_active)
498 @hybrid_property
499 def phone_is_verified(self) -> bool:
500 return (
501 self.phone_verification_verified is not None
502 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
503 )
505 @phone_is_verified.expression
506 def phone_is_verified(cls):
507 return (cls.phone_verification_verified != None) & (
508 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
509 )
511 @hybrid_property
512 def phone_code_expired(self) -> bool:
513 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME
515 def __repr__(self) -> str:
516 return f"User(id={self.id}, email={self.email}, username={self.username})"
519User.pending_activeness_probe = relationship(
520 ActivenessProbe,
521 primaryjoin="and_(ActivenessProbe.user_id == User.id, ActivenessProbe.is_pending)",
522 uselist=False,
523 back_populates="user",
524)
527class LanguageFluency(enum.Enum):
528 # note that the numbering is important here, these are ordinal
529 beginner = 1
530 conversational = 2
531 fluent = 3
534class LanguageAbility(Base):
535 __tablename__ = "language_abilities"
536 __table_args__ = (
537 # Users can only have one language ability per language
538 UniqueConstraint("user_id", "language_code"),
539 )
541 id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
542 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)
543 language_code: Mapped[str] = mapped_column(ForeignKey("languages.code", deferrable=True))
544 fluency: Mapped[LanguageFluency] = mapped_column(Enum(LanguageFluency))
546 user: Mapped["User"] = relationship("User", back_populates="language_abilities")
547 language: Mapped[Language] = relationship("Language")
550class RegionVisited(Base):
551 __tablename__ = "regions_visited"
552 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
554 id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
555 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)
556 region_code: Mapped[str] = mapped_column(ForeignKey("regions.code", deferrable=True))
559class RegionLived(Base):
560 __tablename__ = "regions_lived"
561 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
563 id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
564 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)
565 region_code: Mapped[str] = mapped_column(ForeignKey("regions.code", deferrable=True))