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