Coverage for src/couchers/models.py: 99%
1156 statements
« prev ^ index » next coverage.py v7.6.10, created at 2025-07-09 00:05 +0000
1import enum
3from geoalchemy2.types import Geometry
4from google.protobuf import empty_pb2
5from sqlalchemy import (
6 ARRAY,
7 JSON,
8 BigInteger,
9 Boolean,
10 CheckConstraint,
11 Column,
12 Date,
13 DateTime,
14 Enum,
15 Float,
16 ForeignKey,
17 Index,
18 Integer,
19 Interval,
20 MetaData,
21 Sequence,
22 String,
23 UniqueConstraint,
24)
25from sqlalchemy import LargeBinary as Binary
26from sqlalchemy.dialects.postgresql import INET, TSTZRANGE, ExcludeConstraint
27from sqlalchemy.ext.associationproxy import association_proxy
28from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property
29from sqlalchemy.orm import backref, column_property, declarative_base, deferred, relationship
30from sqlalchemy.sql import and_, func, not_, text
31from sqlalchemy.sql import select as sa_select
33from couchers import urls
34from couchers.config import config
35from couchers.constants import (
36 DATETIME_INFINITY,
37 DATETIME_MINUS_INFINITY,
38 EMAIL_REGEX,
39 GUIDELINES_VERSION,
40 PHONE_VERIFICATION_LIFETIME,
41 SMS_CODE_LIFETIME,
42 TOS_VERSION,
43)
44from couchers.utils import (
45 date_in_timezone,
46 get_coordinates,
47 last_active_coarsen,
48 now,
49)
50from proto import notification_data_pb2
52meta = MetaData(
53 naming_convention={
54 "ix": "ix_%(column_0_label)s",
55 "uq": "uq_%(table_name)s_%(column_0_name)s",
56 "ck": "ck_%(table_name)s_%(constraint_name)s",
57 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
58 "pk": "pk_%(table_name)s",
59 }
60)
62Base = declarative_base(metadata=meta)
65class HostingStatus(enum.Enum):
66 can_host = enum.auto()
67 maybe = enum.auto()
68 cant_host = enum.auto()
71class MeetupStatus(enum.Enum):
72 wants_to_meetup = enum.auto()
73 open_to_meetup = enum.auto()
74 does_not_want_to_meetup = enum.auto()
77class SmokingLocation(enum.Enum):
78 yes = enum.auto()
79 window = enum.auto()
80 outside = enum.auto()
81 no = enum.auto()
84class SleepingArrangement(enum.Enum):
85 private = enum.auto()
86 common = enum.auto()
87 shared_room = enum.auto()
90class ParkingDetails(enum.Enum):
91 free_onsite = enum.auto()
92 free_offsite = enum.auto()
93 paid_onsite = enum.auto()
94 paid_offsite = enum.auto()
97class ProfilePublicVisibility(enum.Enum):
98 # no public info
99 nothing = enum.auto()
100 # only show on map, randomized, unclickable
101 map_only = enum.auto()
102 # name, gender, location, hosting/meetup status, badges, number of references, and signup time
103 limited = enum.auto()
104 # full about me except additional info (hide my home)
105 most = enum.auto()
106 # all but references
107 full = enum.auto()
110class TimezoneArea(Base):
111 __tablename__ = "timezone_areas"
112 id = Column(BigInteger, primary_key=True)
114 tzid = Column(String)
115 geom = Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False)
117 __table_args__ = (
118 Index(
119 "ix_timezone_areas_geom_tzid",
120 geom,
121 tzid,
122 postgresql_using="gist",
123 ),
124 )
127class User(Base):
128 """
129 Basic user and profile details
130 """
132 __tablename__ = "users"
134 id = Column(BigInteger, primary_key=True)
136 username = Column(String, nullable=False, unique=True)
137 email = Column(String, nullable=False, unique=True)
138 # stored in libsodium hash format, can be null for email login
139 hashed_password = Column(Binary, nullable=False)
140 # phone number in E.164 format with leading +, for example "+46701740605"
141 phone = Column(String, nullable=True, server_default=text("NULL"))
142 # language preference -- defaults to empty string
143 ui_language_preference = Column(String, nullable=True, server_default="")
145 # timezones should always be UTC
146 ## location
147 # point describing their location. EPSG4326 is the SRS (spatial ref system, = way to describe a point on earth) used
148 # by GPS, it has the WGS84 geoid with lat/lon
149 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=False)
150 # randomized coordinates within a radius of 0.02-0.1 degrees, equates to about 2-10 km
151 randomized_geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
152 # their display location (displayed to other users), in meters
153 geom_radius = Column(Float, nullable=False)
154 # the display address (text) shown on their profile
155 city = Column(String, nullable=False)
156 # "Grew up in" on profile
157 hometown = Column(String, nullable=True)
159 regions_visited = relationship("Region", secondary="regions_visited", order_by="Region.name")
160 regions_lived = relationship("Region", secondary="regions_lived", order_by="Region.name")
162 timezone = column_property(
163 sa_select(TimezoneArea.tzid).where(func.ST_Contains(TimezoneArea.geom, geom)).limit(1).scalar_subquery(),
164 deferred=True,
165 )
167 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
168 last_active = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
170 public_visibility = Column(Enum(ProfilePublicVisibility), nullable=False, server_default="map_only")
171 has_modified_public_visibility = Column(Boolean, nullable=False, server_default=text("false"))
173 # id of the last message that they received a notification about
174 last_notified_message_id = Column(BigInteger, nullable=False, default=0)
175 # same as above for host requests
176 last_notified_request_message_id = Column(BigInteger, nullable=False, server_default=text("0"))
178 # display name
179 name = Column(String, nullable=False)
180 gender = Column(String, nullable=False)
181 pronouns = Column(String, nullable=True)
182 birthdate = Column(Date, nullable=False) # in the timezone of birthplace
184 avatar_key = Column(ForeignKey("uploads.key"), nullable=True)
186 hosting_status = Column(Enum(HostingStatus), nullable=False)
187 meetup_status = Column(Enum(MeetupStatus), nullable=False, server_default="open_to_meetup")
189 # community standing score
190 community_standing = Column(Float, nullable=True)
192 occupation = Column(String, nullable=True) # CommonMark without images
193 education = Column(String, nullable=True) # CommonMark without images
195 # "Who I am" under "About Me" tab
196 about_me = Column(String, nullable=True) # CommonMark without images
197 # "What I do in my free time" under "About Me" tab
198 things_i_like = Column(String, nullable=True) # CommonMark without images
199 # "About my home" under "My Home" tab
200 about_place = Column(String, nullable=True) # CommonMark without images
201 # "Additional information" under "About Me" tab
202 additional_information = Column(String, nullable=True) # CommonMark without images
204 is_banned = Column(Boolean, nullable=False, server_default=text("false"))
205 is_deleted = Column(Boolean, nullable=False, server_default=text("false"))
206 is_superuser = Column(Boolean, nullable=False, server_default=text("false"))
208 # the undelete token allows a user to recover their account for a couple of days after deletion in case it was
209 # accidental or they changed their mind
210 # constraints make sure these are non-null only if is_deleted and that these are null in unison
211 undelete_token = Column(String, nullable=True)
212 # validity of the undelete token
213 undelete_until = Column(DateTime(timezone=True), nullable=True)
215 # hosting preferences
216 max_guests = Column(Integer, nullable=True)
217 last_minute = Column(Boolean, nullable=True)
218 has_pets = Column(Boolean, nullable=True)
219 accepts_pets = Column(Boolean, nullable=True)
220 pet_details = Column(String, nullable=True) # CommonMark without images
221 has_kids = Column(Boolean, nullable=True)
222 accepts_kids = Column(Boolean, nullable=True)
223 kid_details = Column(String, nullable=True) # CommonMark without images
224 has_housemates = Column(Boolean, nullable=True)
225 housemate_details = Column(String, nullable=True) # CommonMark without images
226 wheelchair_accessible = Column(Boolean, nullable=True)
227 smoking_allowed = Column(Enum(SmokingLocation), nullable=True)
228 smokes_at_home = Column(Boolean, nullable=True)
229 drinking_allowed = Column(Boolean, nullable=True)
230 drinks_at_home = Column(Boolean, nullable=True)
231 # "Additional information" under "My Home" tab
232 other_host_info = Column(String, nullable=True) # CommonMark without images
234 # "Sleeping privacy" (not long-form text)
235 sleeping_arrangement = Column(Enum(SleepingArrangement), nullable=True)
236 # "Sleeping arrangement" under "My Home" tab
237 sleeping_details = Column(String, nullable=True) # CommonMark without images
238 # "Local area information" under "My Home" tab
239 area = Column(String, nullable=True) # CommonMark without images
240 # "House rules" under "My Home" tab
241 house_rules = Column(String, nullable=True) # CommonMark without images
242 parking = Column(Boolean, nullable=True)
243 parking_details = Column(Enum(ParkingDetails), nullable=True) # CommonMark without images
244 camping_ok = Column(Boolean, nullable=True)
246 accepted_tos = Column(Integer, nullable=False, default=0)
247 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")
248 # whether the user has yet filled in the contributor form
249 filled_contributor_form = Column(Boolean, nullable=False, server_default="false")
251 # number of onboarding emails sent
252 onboarding_emails_sent = Column(Integer, nullable=False, server_default="0")
253 last_onboarding_email_sent = Column(DateTime(timezone=True), nullable=True)
255 # whether we need to sync the user's newsletter preferences with the newsletter server
256 in_sync_with_newsletter = Column(Boolean, nullable=False, server_default="false")
257 # opted out of the newsletter
258 opt_out_of_newsletter = Column(Boolean, nullable=False, server_default="false")
260 # set to null to receive no digests
261 digest_frequency = Column(Interval, nullable=True)
262 last_digest_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)"))
264 # for changing their email
265 new_email = Column(String, nullable=True)
267 new_email_token = Column(String, nullable=True)
268 new_email_token_created = Column(DateTime(timezone=True), nullable=True)
269 new_email_token_expiry = Column(DateTime(timezone=True), nullable=True)
271 recommendation_score = Column(Float, nullable=False, server_default="0")
273 # Columns for verifying their phone number. State chart:
274 # ,-------------------,
275 # | Start |
276 # | phone = None | someone else
277 # ,-----------------, | token = None | verifies ,-----------------------,
278 # | Code Expired | | sent = 1970 or zz | phone xx | Verification Expired |
279 # | phone = xx | time passes | verified = None | <------, | phone = xx |
280 # | token = yy | <------------, | attempts = 0 | | | token = None |
281 # | sent = zz (exp.)| | '-------------------' | | sent = zz |
282 # | verified = None | | V ^ +-----------< | verified = ww (exp.) |
283 # | attempts = 0..2 | >--, | | | ChangePhone("") | | attempts = 0 |
284 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------'
285 # | | | | ChangePhone(xx) | ^ time passes
286 # | | ^ V | |
287 # ,-----------------, | | ,-------------------, | ,-----------------------,
288 # | Too Many | >--' '--< | Code sent | >------+ | Verified |
289 # | phone = xx | | phone = xx | | | phone = xx |
290 # | token = yy | VerifyPhone(wrong)| token = yy | '-----------< | token = None |
291 # | sent = zz | <------+--------< | sent = zz | | sent = zz |
292 # | verified = None | | | verified = None | VerifyPhone(correct) | verified = ww |
293 # | attempts = 3 | '--------> | attempts = 0..2 | >------------------> | attempts = 0 |
294 # '-----------------' '-------------------' '-----------------------'
296 # randomly generated Luhn 6-digit string
297 phone_verification_token = Column(String(6), nullable=True, server_default=text("NULL"))
299 phone_verification_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)"))
300 phone_verification_verified = Column(DateTime(timezone=True), nullable=True, server_default=text("NULL"))
301 phone_verification_attempts = Column(Integer, nullable=False, server_default=text("0"))
303 # the stripe customer identifier if the user has donated to Couchers
304 # e.g. cus_JjoXHttuZopv0t
305 # for new US entity
306 stripe_customer_id = Column(String, nullable=True)
307 # for old AU entity
308 stripe_customer_id_old = Column(String, nullable=True)
310 has_passport_sex_gender_exception = Column(Boolean, nullable=False, server_default=text("false"))
312 # checking for phone verification
313 has_donated = Column(Boolean, nullable=False, server_default=text("false"))
315 # whether this user has all emails turned off
316 do_not_email = Column(Boolean, nullable=False, server_default=text("false"))
318 avatar = relationship("Upload", foreign_keys="User.avatar_key")
320 admin_note = Column(String, nullable=False, server_default=text("''"))
322 # whether mods have marked this user has having to update their location
323 needs_to_update_location = Column(Boolean, nullable=False, server_default=text("false"))
325 last_antibot = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)"))
327 age = column_property(func.date_part("year", func.age(birthdate)))
329 __table_args__ = (
330 # Verified phone numbers should be unique
331 Index(
332 "ix_users_unique_phone",
333 phone,
334 unique=True,
335 postgresql_where=phone_verification_verified != None,
336 ),
337 Index(
338 "ix_users_active",
339 id,
340 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
341 ),
342 # create index on users(geom, id, username) where not is_banned and not is_deleted and geom is not null;
343 Index(
344 "ix_users_geom_active",
345 geom,
346 id,
347 username,
348 postgresql_using="gist",
349 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
350 ),
351 Index(
352 "ix_users_by_id",
353 id,
354 postgresql_using="hash",
355 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
356 ),
357 Index(
358 "ix_users_by_username",
359 username,
360 postgresql_using="hash",
361 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
362 ),
363 # There are two possible states for new_email_token, new_email_token_created, and new_email_token_expiry
364 CheckConstraint(
365 "(new_email_token IS NOT NULL AND new_email_token_created IS NOT NULL AND new_email_token_expiry IS NOT NULL) OR \
366 (new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL)",
367 name="check_new_email_token_state",
368 ),
369 # Whenever a phone number is set, it must either be pending verification or already verified.
370 # Exactly one of the following must always be true: not phone, token, verified.
371 CheckConstraint(
372 "(phone IS NULL)::int + (phone_verification_verified IS NOT NULL)::int + (phone_verification_token IS NOT NULL)::int = 1",
373 name="phone_verified_conditions",
374 ),
375 # Email must match our regex
376 CheckConstraint(
377 f"email ~ '{EMAIL_REGEX}'",
378 name="valid_email",
379 ),
380 # Undelete token + time are coupled: either both null or neither; and if they're not null then the account is deleted
381 CheckConstraint(
382 "((undelete_token IS NULL) = (undelete_until IS NULL)) AND ((undelete_token IS NULL) OR is_deleted)",
383 name="undelete_nullity",
384 ),
385 # If the user disabled all emails, then they can't host or meet up
386 CheckConstraint(
387 "(do_not_email IS FALSE) OR ((hosting_status = 'cant_host') AND (meetup_status = 'does_not_want_to_meetup'))",
388 name="do_not_email_inactive",
389 ),
390 )
392 @hybrid_property
393 def has_completed_profile(self):
394 return self.avatar_key is not None and self.about_me is not None and len(self.about_me) >= 150
396 @has_completed_profile.expression
397 def has_completed_profile(cls):
398 return (cls.avatar_key != None) & (func.character_length(cls.about_me) >= 150)
400 @hybrid_property
401 def jailed_missing_tos(self):
402 return self.accepted_tos < TOS_VERSION
404 @hybrid_property
405 def jailed_missing_community_guidelines(self):
406 return self.accepted_community_guidelines < GUIDELINES_VERSION
408 @hybrid_property
409 def jailed_pending_mod_notes(self):
410 return self.mod_notes.where(ModNote.is_pending).count() > 0
412 @hybrid_property
413 def jailed_pending_activeness_probe(self):
414 return self.pending_activeness_probe != None
416 @hybrid_property
417 def is_jailed(self):
418 return (
419 self.jailed_missing_tos
420 | self.jailed_missing_community_guidelines
421 | self.is_missing_location
422 | self.jailed_pending_mod_notes
423 | self.jailed_pending_activeness_probe
424 )
426 @hybrid_property
427 def is_missing_location(self):
428 return self.needs_to_update_location
430 @hybrid_property
431 def is_visible(self):
432 return ~(self.is_banned | self.is_deleted)
434 @property
435 def coordinates(self):
436 return get_coordinates(self.geom)
438 @property
439 def display_joined(self):
440 """
441 Returns the last active time rounded down to the nearest hour.
442 """
443 return self.joined.replace(minute=0, second=0, microsecond=0)
445 @property
446 def display_last_active(self):
447 """
448 Returns the last active time rounded down whatever is the "last active" coarsening.
449 """
450 return last_active_coarsen(self.last_active)
452 @hybrid_property
453 def phone_is_verified(self):
454 return (
455 self.phone_verification_verified is not None
456 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
457 )
459 @phone_is_verified.expression
460 def phone_is_verified(cls):
461 return (cls.phone_verification_verified != None) & (
462 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
463 )
465 @hybrid_property
466 def phone_code_expired(self):
467 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME
469 def __repr__(self):
470 return f"User(id={self.id}, email={self.email}, username={self.username})"
473class UserBadge(Base):
474 """
475 A badge on a user's profile
476 """
478 __tablename__ = "user_badges"
479 __table_args__ = (UniqueConstraint("user_id", "badge_id"),)
481 id = Column(BigInteger, primary_key=True)
483 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
484 # corresponds to "id" in badges.json
485 badge_id = Column(String, nullable=False, index=True)
487 # take this with a grain of salt, someone may get then lose a badge for whatever reason
488 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
490 user = relationship("User", backref="badges")
493class ActivenessProbeStatus(enum.Enum):
494 # no response yet
495 pending = enum.auto()
497 # didn't respond on time
498 expired = enum.auto()
500 # responded that they're still active
501 still_active = enum.auto()
503 # responded that they're no longer active
504 no_longer_active = enum.auto()
507class ActivenessProbe(Base):
508 """
509 Activeness probes are used to gauge if users are still active: we send them a notification and ask them to respond,
510 we use this data both to help indicate response rate, as well as to make sure only those who are actively hosting
511 show up as such.
512 """
514 __tablename__ = "activeness_probes"
516 id = Column(BigInteger, primary_key=True)
518 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
519 # the time this probe was initiated
520 probe_initiated = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
521 # the number of reminders sent for this probe
522 notifications_sent = Column(Integer, nullable=False, server_default="0")
524 # the time of response
525 responded = Column(DateTime(timezone=True), nullable=True, default=None)
526 # the response value
527 response = Column(Enum(ActivenessProbeStatus), nullable=False, default=ActivenessProbeStatus.pending)
529 @hybrid_property
530 def is_pending(self):
531 return self.responded == None
533 user = relationship("User", back_populates="pending_activeness_probe")
535 __table_args__ = (
536 # a user can have at most one pending activeness probe at a time
537 Index(
538 "ix_activeness_probe_unique_pending_response",
539 user_id,
540 unique=True,
541 postgresql_where=responded == None,
542 ),
543 # response time is none iff response is pending
544 CheckConstraint(
545 "(responded IS NULL AND response = 'pending') OR (responded IS NOT NULL AND response != 'pending')",
546 name="pending_has_no_responded",
547 ),
548 )
551User.pending_activeness_probe = relationship(
552 ActivenessProbe,
553 primaryjoin="and_(ActivenessProbe.user_id == User.id, ActivenessProbe.is_pending)",
554 uselist=False,
555 back_populates="user",
556)
559class StrongVerificationAttemptStatus(enum.Enum):
560 ## full data states
561 # completed, this now provides verification for a user
562 succeeded = enum.auto()
564 ## no data states
565 # in progress: waiting for the user to scan the Iris code or open the app
566 in_progress_waiting_on_user_to_open_app = enum.auto()
567 # in progress: waiting for the user to scan MRZ or NFC/chip
568 in_progress_waiting_on_user_in_app = enum.auto()
569 # in progress, waiting for backend to pull verification data
570 in_progress_waiting_on_backend = enum.auto()
571 # failed, no data
572 failed = enum.auto()
574 # duplicate, at our end, has data
575 duplicate = enum.auto()
577 ## minimal data states
578 # the data, except minimal deduplication data, was deleted
579 deleted = enum.auto()
582class PassportSex(enum.Enum):
583 """
584 We don't care about sex, we use gender on the platform. But passports apparently do.
585 """
587 male = enum.auto()
588 female = enum.auto()
589 unspecified = enum.auto()
592class StrongVerificationAttempt(Base):
593 """
594 An attempt to perform strong verification
595 """
597 __tablename__ = "strong_verification_attempts"
599 # our verification id
600 id = Column(BigInteger, primary_key=True)
602 # this is returned in the callback, and we look up the attempt via this
603 verification_attempt_token = Column(String, nullable=False, unique=True)
605 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
606 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
608 status = Column(
609 Enum(StrongVerificationAttemptStatus),
610 nullable=False,
611 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app,
612 )
614 ## full data
615 has_full_data = Column(Boolean, nullable=False, default=False)
616 # the data returned from iris, encrypted with a public key whose private key is kept offline
617 passport_encrypted_data = Column(Binary, nullable=True)
618 passport_date_of_birth = Column(Date, nullable=True)
619 passport_sex = Column(Enum(PassportSex), nullable=True)
621 ## minimal data: this will not be deleted
622 has_minimal_data = Column(Boolean, nullable=False, default=False)
623 passport_expiry_date = Column(Date, nullable=True)
624 passport_nationality = Column(String, nullable=True)
625 # last three characters of the passport number
626 passport_last_three_document_chars = Column(String, nullable=True)
628 iris_token = Column(String, nullable=False, unique=True)
629 iris_session_id = Column(BigInteger, nullable=False, unique=True)
631 passport_expiry_datetime = column_property(date_in_timezone(passport_expiry_date, "Etc/UTC"))
633 user = relationship("User")
635 @hybrid_property
636 def is_valid(self):
637 """
638 This only checks whether the attempt is a success and the passport is not expired, use `has_strong_verification` for full check
639 """
640 return (self.status == StrongVerificationAttemptStatus.succeeded) and (self.passport_expiry_datetime >= now())
642 @is_valid.expression
643 def is_valid(cls):
644 return (cls.status == StrongVerificationAttemptStatus.succeeded) & (
645 func.coalesce(cls.passport_expiry_datetime >= func.now(), False)
646 )
648 @hybrid_property
649 def is_visible(self):
650 return self.status != StrongVerificationAttemptStatus.deleted
652 @hybrid_method
653 def _raw_birthdate_match(self, user):
654 """Does not check whether the SV attempt itself is not expired"""
655 return self.passport_date_of_birth == user.birthdate
657 @hybrid_method
658 def matches_birthdate(self, user):
659 return self.is_valid & self._raw_birthdate_match(user)
661 @hybrid_method
662 def _raw_gender_match(self, user):
663 """Does not check whether the SV attempt itself is not expired"""
664 return (
665 ((user.gender == "Woman") & (self.passport_sex == PassportSex.female))
666 | ((user.gender == "Man") & (self.passport_sex == PassportSex.male))
667 | (self.passport_sex == PassportSex.unspecified)
668 | (user.has_passport_sex_gender_exception == True)
669 )
671 @hybrid_method
672 def matches_gender(self, user):
673 return self.is_valid & self._raw_gender_match(user)
675 @hybrid_method
676 def has_strong_verification(self, user):
677 return self.is_valid & self._raw_birthdate_match(user) & self._raw_gender_match(user)
679 __table_args__ = (
680 # used to look up verification status for a user
681 Index(
682 "ix_strong_verification_attempts_current",
683 user_id,
684 passport_expiry_date,
685 postgresql_where=status == StrongVerificationAttemptStatus.succeeded,
686 ),
687 # each passport can be verified only once
688 Index(
689 "ix_strong_verification_attempts_unique_succeeded",
690 passport_expiry_date,
691 passport_nationality,
692 passport_last_three_document_chars,
693 unique=True,
694 postgresql_where=(
695 (status == StrongVerificationAttemptStatus.succeeded)
696 | (status == StrongVerificationAttemptStatus.deleted)
697 ),
698 ),
699 # full data check
700 CheckConstraint(
701 "(has_full_data IS TRUE AND passport_encrypted_data IS NOT NULL AND passport_date_of_birth IS NOT NULL) OR \
702 (has_full_data IS FALSE AND passport_encrypted_data IS NULL AND passport_date_of_birth IS NULL)",
703 name="full_data_status",
704 ),
705 # minimal data check
706 CheckConstraint(
707 "(has_minimal_data IS TRUE AND passport_expiry_date IS NOT NULL AND passport_nationality IS NOT NULL AND passport_last_three_document_chars IS NOT NULL) OR \
708 (has_minimal_data IS FALSE AND passport_expiry_date IS NULL AND passport_nationality IS NULL AND passport_last_three_document_chars IS NULL)",
709 name="minimal_data_status",
710 ),
711 # note on implications: p => q iff ~p OR q
712 # full data implies minimal data, has_minimal_data => has_full_data
713 CheckConstraint(
714 "(has_full_data IS FALSE) OR (has_minimal_data IS TRUE)",
715 name="full_data_implies_minimal_data",
716 ),
717 # succeeded implies full data
718 CheckConstraint(
719 "(NOT (status = 'succeeded')) OR (has_full_data IS TRUE)",
720 name="succeeded_implies_full_data",
721 ),
722 # in_progress/failed implies no_data
723 CheckConstraint(
724 "(NOT ((status = 'in_progress_waiting_on_user_to_open_app') OR (status = 'in_progress_waiting_on_user_in_app') OR (status = 'in_progress_waiting_on_backend') OR (status = 'failed'))) OR (has_minimal_data IS FALSE)",
725 name="in_progress_failed_iris_implies_no_data",
726 ),
727 # deleted or duplicate implies minimal data
728 CheckConstraint(
729 "(NOT ((status = 'deleted') OR (status = 'duplicate'))) OR (has_minimal_data IS TRUE)",
730 name="deleted_duplicate_implies_minimal_data",
731 ),
732 )
735class ModNote(Base):
736 """
737 A moderator note to a user. This could be a warning, just a note "hey, we did X", or any other similar message.
739 The user has to read and "acknowledge" the note before continuing onto the platform, and being un-jailed.
740 """
742 __tablename__ = "mod_notes"
743 id = Column(BigInteger, primary_key=True)
745 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
747 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
748 acknowledged = Column(DateTime(timezone=True), nullable=True)
750 # this is an internal ID to allow the mods to track different types of notes
751 internal_id = Column(String, nullable=False)
752 # the admin that left this note
753 creator_user_id = Column(ForeignKey("users.id"), nullable=False)
755 note_content = Column(String, nullable=False) # CommonMark without images
757 user = relationship("User", backref=backref("mod_notes", lazy="dynamic"), foreign_keys="ModNote.user_id")
759 def __repr__(self):
760 return f"ModeNote(id={self.id}, user={self.user}, created={self.created}, ack'd={self.acknowledged})"
762 @hybrid_property
763 def is_pending(self):
764 return self.acknowledged == None
766 __table_args__ = (
767 # used to look up pending notes
768 Index(
769 "ix_mod_notes_unacknowledged",
770 user_id,
771 postgresql_where=acknowledged == None,
772 ),
773 )
776class StrongVerificationCallbackEvent(Base):
777 __tablename__ = "strong_verification_callback_events"
779 id = Column(BigInteger, primary_key=True)
780 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
782 verification_attempt_id = Column(ForeignKey("strong_verification_attempts.id"), nullable=False, index=True)
784 iris_status = Column(String, nullable=False)
787class DonationType(enum.Enum):
788 one_time = enum.auto()
789 recurring = enum.auto()
792class DonationInitiation(Base):
793 """
794 Whenever someone initiaties a donation through the platform
795 """
797 __tablename__ = "donation_initiations"
798 id = Column(BigInteger, primary_key=True)
800 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
801 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
803 amount = Column(Integer, nullable=False)
804 stripe_checkout_session_id = Column(String, nullable=False)
806 donation_type = Column(Enum(DonationType), nullable=False)
808 user = relationship("User", backref="donation_initiations")
811class Invoice(Base):
812 """
813 Successful donations, both one off and recurring
815 Triggered by `payment_intent.succeeded` webhook
816 """
818 __tablename__ = "invoices"
820 id = Column(BigInteger, primary_key=True)
821 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
822 user_id = Column(ForeignKey("users.id"), nullable=False)
824 amount = Column(Float, nullable=False)
826 stripe_payment_intent_id = Column(String, nullable=False, unique=True)
827 stripe_receipt_url = Column(String, nullable=False)
829 user = relationship("User", backref="invoices")
832class LanguageFluency(enum.Enum):
833 # note that the numbering is important here, these are ordinal
834 beginner = 1
835 conversational = 2
836 fluent = 3
839class LanguageAbility(Base):
840 __tablename__ = "language_abilities"
841 __table_args__ = (
842 # Users can only have one language ability per language
843 UniqueConstraint("user_id", "language_code"),
844 )
846 id = Column(BigInteger, primary_key=True)
847 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
848 language_code = Column(ForeignKey("languages.code", deferrable=True), nullable=False)
849 fluency = Column(Enum(LanguageFluency), nullable=False)
851 user = relationship("User", backref="language_abilities")
852 language = relationship("Language")
855class RegionVisited(Base):
856 __tablename__ = "regions_visited"
857 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
859 id = Column(BigInteger, primary_key=True)
860 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
861 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
864class RegionLived(Base):
865 __tablename__ = "regions_lived"
866 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
868 id = Column(BigInteger, primary_key=True)
869 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
870 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
873class FriendStatus(enum.Enum):
874 pending = enum.auto()
875 accepted = enum.auto()
876 rejected = enum.auto()
877 cancelled = enum.auto()
880class FriendRelationship(Base):
881 """
882 Friendship relations between users
884 TODO: make this better with sqlalchemy self-referential stuff
885 TODO: constraint on only one row per user pair where accepted or pending
886 """
888 __tablename__ = "friend_relationships"
890 id = Column(BigInteger, primary_key=True)
892 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
893 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
895 status = Column(Enum(FriendStatus), nullable=False, default=FriendStatus.pending)
897 # timezones should always be UTC
898 time_sent = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
899 time_responded = Column(DateTime(timezone=True), nullable=True)
901 from_user = relationship("User", backref="friends_from", foreign_keys="FriendRelationship.from_user_id")
902 to_user = relationship("User", backref="friends_to", foreign_keys="FriendRelationship.to_user_id")
904 __table_args__ = (
905 # Ping looks up pending friend reqs, this speeds that up
906 Index(
907 "ix_friend_relationships_status_to_from",
908 status,
909 to_user_id,
910 from_user_id,
911 ),
912 )
915class ContributeOption(enum.Enum):
916 yes = enum.auto()
917 maybe = enum.auto()
918 no = enum.auto()
921class ContributorForm(Base):
922 """
923 Someone filled in the contributor form
924 """
926 __tablename__ = "contributor_forms"
928 id = Column(BigInteger, primary_key=True)
930 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
931 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
933 ideas = Column(String, nullable=True)
934 features = Column(String, nullable=True)
935 experience = Column(String, nullable=True)
936 contribute = Column(Enum(ContributeOption), nullable=True)
937 contribute_ways = Column(ARRAY(String), nullable=False)
938 expertise = Column(String, nullable=True)
940 user = relationship("User", backref="contributor_forms")
942 @hybrid_property
943 def is_filled(self):
944 """
945 Whether the form counts as having been filled
946 """
947 return (
948 (self.ideas != None)
949 | (self.features != None)
950 | (self.experience != None)
951 | (self.contribute != None)
952 | (self.contribute_ways != [])
953 | (self.expertise != None)
954 )
956 @property
957 def should_notify(self):
958 """
959 If this evaluates to true, we send an email to the recruitment team.
961 We currently send if expertise is listed, or if they list a way to help outside of a set list
962 """
963 return False
966class SignupFlow(Base):
967 """
968 Signup flows/incomplete users
970 Coinciding fields have the same meaning as in User
971 """
973 __tablename__ = "signup_flows"
975 id = Column(BigInteger, primary_key=True)
977 # housekeeping
978 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
979 flow_token = Column(String, nullable=False, unique=True)
980 email_verified = Column(Boolean, nullable=False, default=False)
981 email_sent = Column(Boolean, nullable=False, default=False)
982 email_token = Column(String, nullable=True)
983 email_token_expiry = Column(DateTime(timezone=True), nullable=True)
985 ## Basic
986 name = Column(String, nullable=False)
987 # TODO: unique across both tables
988 email = Column(String, nullable=False, unique=True)
989 # TODO: invitation, attribution
991 ## Account
992 # TODO: unique across both tables
993 username = Column(String, nullable=True, unique=True)
994 hashed_password = Column(Binary, nullable=True)
995 birthdate = Column(Date, nullable=True) # in the timezone of birthplace
996 gender = Column(String, nullable=True)
997 hosting_status = Column(Enum(HostingStatus), nullable=True)
998 city = Column(String, nullable=True)
999 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
1000 geom_radius = Column(Float, nullable=True)
1002 accepted_tos = Column(Integer, nullable=True)
1003 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")
1005 opt_out_of_newsletter = Column(Boolean, nullable=True)
1007 ## Feedback (now unused)
1008 filled_feedback = Column(Boolean, nullable=False, default=False)
1009 ideas = Column(String, nullable=True)
1010 features = Column(String, nullable=True)
1011 experience = Column(String, nullable=True)
1012 contribute = Column(Enum(ContributeOption), nullable=True)
1013 contribute_ways = Column(ARRAY(String), nullable=True)
1014 expertise = Column(String, nullable=True)
1016 @hybrid_property
1017 def token_is_valid(self):
1018 return (self.email_token != None) & (self.email_token_expiry >= now())
1020 @hybrid_property
1021 def account_is_filled(self):
1022 return (
1023 (self.username != None)
1024 & (self.birthdate != None)
1025 & (self.gender != None)
1026 & (self.hosting_status != None)
1027 & (self.city != None)
1028 & (self.geom != None)
1029 & (self.geom_radius != None)
1030 & (self.accepted_tos != None)
1031 & (self.opt_out_of_newsletter != None)
1032 )
1034 @hybrid_property
1035 def is_completed(self):
1036 return self.email_verified & self.account_is_filled & (self.accepted_community_guidelines == GUIDELINES_VERSION)
1039class LoginToken(Base):
1040 """
1041 A login token sent in an email to a user, allows them to sign in between the times defined by created and expiry
1042 """
1044 __tablename__ = "login_tokens"
1045 token = Column(String, primary_key=True)
1047 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1049 # timezones should always be UTC
1050 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1051 expiry = Column(DateTime(timezone=True), nullable=False)
1053 user = relationship("User", backref="login_tokens")
1055 @hybrid_property
1056 def is_valid(self):
1057 return (self.created <= now()) & (self.expiry >= now())
1059 def __repr__(self):
1060 return f"LoginToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
1063class PasswordResetToken(Base):
1064 __tablename__ = "password_reset_tokens"
1065 token = Column(String, primary_key=True)
1067 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1069 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1070 expiry = Column(DateTime(timezone=True), nullable=False)
1072 user = relationship("User", backref="password_reset_tokens")
1074 @hybrid_property
1075 def is_valid(self):
1076 return (self.created <= now()) & (self.expiry >= now())
1078 def __repr__(self):
1079 return f"PasswordResetToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
1082class AccountDeletionToken(Base):
1083 __tablename__ = "account_deletion_tokens"
1085 token = Column(String, primary_key=True)
1087 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1089 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1090 expiry = Column(DateTime(timezone=True), nullable=False)
1092 user = relationship("User", backref="account_deletion_tokens")
1094 @hybrid_property
1095 def is_valid(self):
1096 return (self.created <= now()) & (self.expiry >= now())
1098 def __repr__(self):
1099 return f"AccountDeletionToken(token={self.token}, user_id={self.user_id}, created={self.created}, expiry={self.expiry})"
1102class UserActivity(Base):
1103 """
1104 User activity: for each unique (user_id, period, ip_address, user_agent) tuple, keep track of number of api calls
1106 Used for user "last active" as well as admin stuff
1107 """
1109 __tablename__ = "user_activity"
1111 id = Column(BigInteger, primary_key=True)
1113 user_id = Column(ForeignKey("users.id"), nullable=False)
1114 # the start of a period of time, e.g. 1 hour during which we bin activeness
1115 period = Column(DateTime(timezone=True), nullable=False)
1117 # details of the browser, if available
1118 ip_address = Column(INET, nullable=True)
1119 user_agent = Column(String, nullable=True)
1121 # count of api calls made with this ip, user_agent, and period
1122 api_calls = Column(Integer, nullable=False, default=0)
1124 __table_args__ = (
1125 # helps look up this tuple quickly
1126 Index(
1127 "ix_user_activity_user_id_period_ip_address_user_agent",
1128 user_id,
1129 period,
1130 ip_address,
1131 user_agent,
1132 unique=True,
1133 ),
1134 )
1137class UserSession(Base):
1138 """
1139 API keys/session cookies for the app
1141 There are two types of sessions: long-lived, and short-lived. Long-lived are
1142 like when you choose "remember this browser": they will be valid for a long
1143 time without the user interacting with the site. Short-lived sessions on the
1144 other hand get invalidated quickly if the user does not interact with the
1145 site.
1147 Long-lived tokens are valid from `created` until `expiry`.
1149 Short-lived tokens expire after 168 hours (7 days) if they are not used.
1150 """
1152 __tablename__ = "sessions"
1153 token = Column(String, primary_key=True)
1155 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1157 # sessions are either "api keys" or "session cookies", otherwise identical
1158 # an api key is put in the authorization header (e.g. "authorization: Bearer <token>")
1159 # a session cookie is set in the "couchers-sesh" cookie (e.g. "cookie: couchers-sesh=<token>")
1160 # when a session is created, it's fixed as one or the other for security reasons
1161 # for api keys to be useful, they should be long lived and have a long expiry
1162 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))
1164 # whether it's a long-lived or short-lived session
1165 long_lived = Column(Boolean, nullable=False)
1167 # the time at which the session was created
1168 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1170 # the expiry of the session: the session *cannot* be refreshed past this
1171 expiry = Column(DateTime(timezone=True), nullable=False, server_default=func.now() + text("interval '730 days'"))
1173 # the time at which the token was invalidated, allows users to delete sessions
1174 deleted = Column(DateTime(timezone=True), nullable=True, default=None)
1176 # the last time this session was used
1177 last_seen = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1179 # count of api calls made with this token/session (if we're updating last_seen, might as well update this too)
1180 api_calls = Column(Integer, nullable=False, default=0)
1182 # details of the browser, if available
1183 # these are from the request creating the session, not used for anything else
1184 ip_address = Column(String, nullable=True)
1185 user_agent = Column(String, nullable=True)
1187 user = relationship("User", backref="sessions")
1189 @hybrid_property
1190 def is_valid(self):
1191 """
1192 It must have been created and not be expired or deleted.
1194 Also, if it's a short lived token, it must have been used in the last 168 hours.
1196 TODO: this probably won't run in python (instance level), only in sql (class level)
1197 """
1198 return (
1199 (self.created <= func.now())
1200 & (self.expiry >= func.now())
1201 & (self.deleted == None)
1202 & (self.long_lived | (func.now() - self.last_seen < text("interval '168 hours'")))
1203 )
1205 __table_args__ = (
1206 Index(
1207 "ix_sessions_by_token",
1208 "token",
1209 postgresql_using="hash",
1210 ),
1211 )
1214class Conversation(Base):
1215 """
1216 Conversation brings together the different types of message/conversation types
1217 """
1219 __tablename__ = "conversations"
1221 id = Column(BigInteger, primary_key=True)
1222 # timezone should always be UTC
1223 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1225 def __repr__(self):
1226 return f"Conversation(id={self.id}, created={self.created})"
1229class GroupChat(Base):
1230 """
1231 Group chat
1232 """
1234 __tablename__ = "group_chats"
1236 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1238 title = Column(String, nullable=True)
1239 only_admins_invite = Column(Boolean, nullable=False, default=True)
1240 creator_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1241 is_dm = Column(Boolean, nullable=False)
1243 conversation = relationship("Conversation", backref="group_chat")
1244 creator = relationship("User", backref="created_group_chats")
1246 def __repr__(self):
1247 return f"GroupChat(conversation={self.conversation}, title={self.title or 'None'}, only_admins_invite={self.only_admins_invite}, creator={self.creator}, is_dm={self.is_dm})"
1250class GroupChatRole(enum.Enum):
1251 admin = enum.auto()
1252 participant = enum.auto()
1255class GroupChatSubscription(Base):
1256 """
1257 The recipient of a thread and information about when they joined/left/etc.
1258 """
1260 __tablename__ = "group_chat_subscriptions"
1261 id = Column(BigInteger, primary_key=True)
1263 # TODO: DB constraint on only one user+group_chat combo at a given time
1264 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1265 group_chat_id = Column(ForeignKey("group_chats.id"), nullable=False, index=True)
1267 # timezones should always be UTC
1268 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1269 left = Column(DateTime(timezone=True), nullable=True)
1271 role = Column(Enum(GroupChatRole), nullable=False)
1273 last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1275 # when this chat is muted until, DATETIME_INFINITY for "forever"
1276 muted_until = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_MINUS_INFINITY.isoformat())
1278 user = relationship("User", backref="group_chat_subscriptions")
1279 group_chat = relationship("GroupChat", backref=backref("subscriptions", lazy="dynamic"))
1281 def muted_display(self):
1282 """
1283 Returns (muted, muted_until) display values:
1284 1. If not muted, returns (False, None)
1285 2. If muted forever, returns (True, None)
1286 3. If muted until a given datetime returns (True, dt)
1287 """
1288 if self.muted_until < now():
1289 return (False, None)
1290 elif self.muted_until == DATETIME_INFINITY:
1291 return (True, None)
1292 else:
1293 return (True, self.muted_until)
1295 @hybrid_property
1296 def is_muted(self):
1297 return self.muted_until > func.now()
1299 def __repr__(self):
1300 return f"GroupChatSubscription(id={self.id}, user={self.user}, joined={self.joined}, left={self.left}, role={self.role}, group_chat={self.group_chat})"
1303class MessageType(enum.Enum):
1304 text = enum.auto()
1305 # e.g.
1306 # image =
1307 # emoji =
1308 # ...
1309 chat_created = enum.auto()
1310 chat_edited = enum.auto()
1311 user_invited = enum.auto()
1312 user_left = enum.auto()
1313 user_made_admin = enum.auto()
1314 user_removed_admin = enum.auto() # RemoveGroupChatAdmin: remove admin permission from a user in group chat
1315 host_request_status_changed = enum.auto()
1316 user_removed = enum.auto() # user is removed from group chat by amdin RemoveGroupChatUser
1319class HostRequestStatus(enum.Enum):
1320 pending = enum.auto()
1321 accepted = enum.auto()
1322 rejected = enum.auto()
1323 confirmed = enum.auto()
1324 cancelled = enum.auto()
1327class Message(Base):
1328 """
1329 A message.
1331 If message_type = text, then the message is a normal text message, otherwise, it's a special control message.
1332 """
1334 __tablename__ = "messages"
1336 id = Column(BigInteger, primary_key=True)
1338 # which conversation the message belongs in
1339 conversation_id = Column(ForeignKey("conversations.id"), nullable=False, index=True)
1341 # the user that sent the message/command
1342 author_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1344 # the message type, "text" is a text message, otherwise a "control message"
1345 message_type = Column(Enum(MessageType), nullable=False)
1347 # the target if a control message and requires target, e.g. if inviting a user, the user invited is the target
1348 target_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1350 # time sent, timezone should always be UTC
1351 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1353 # the plain-text message text if not control
1354 text = Column(String, nullable=True)
1356 # the new host request status if the message type is host_request_status_changed
1357 host_request_status_target = Column(Enum(HostRequestStatus), nullable=True)
1359 conversation = relationship("Conversation", backref="messages", order_by="Message.time.desc()")
1360 author = relationship("User", foreign_keys="Message.author_id")
1361 target = relationship("User", foreign_keys="Message.target_id")
1363 @property
1364 def is_normal_message(self):
1365 """
1366 There's only one normal type atm, text
1367 """
1368 return self.message_type == MessageType.text
1370 def __repr__(self):
1371 return f"Message(id={self.id}, time={self.time}, text={self.text}, author={self.author}, conversation={self.conversation})"
1374class ContentReport(Base):
1375 """
1376 A piece of content reported to admins
1377 """
1379 __tablename__ = "content_reports"
1381 id = Column(BigInteger, primary_key=True)
1383 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1385 # the user who reported or flagged the content
1386 reporting_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1388 # reason, e.g. spam, inappropriate, etc
1389 reason = Column(String, nullable=False)
1390 # a short description
1391 description = Column(String, nullable=False)
1393 # a reference to the content, see //docs/content_ref.md
1394 content_ref = Column(String, nullable=False)
1395 # the author of the content (e.g. the user who wrote the comment itself)
1396 author_user_id = Column(ForeignKey("users.id"), nullable=False)
1398 # details of the browser, if available
1399 user_agent = Column(String, nullable=False)
1400 # the URL the user was on when reporting the content
1401 page = Column(String, nullable=False)
1403 # see comments above for reporting vs author
1404 reporting_user = relationship("User", foreign_keys="ContentReport.reporting_user_id")
1405 author_user = relationship("User", foreign_keys="ContentReport.author_user_id")
1408class Email(Base):
1409 """
1410 Table of all dispatched emails for debugging purposes, etc.
1411 """
1413 __tablename__ = "emails"
1415 id = Column(String, primary_key=True)
1417 # timezone should always be UTC
1418 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1420 sender_name = Column(String, nullable=False)
1421 sender_email = Column(String, nullable=False)
1423 recipient = Column(String, nullable=False)
1424 subject = Column(String, nullable=False)
1426 plain = Column(String, nullable=False)
1427 html = Column(String, nullable=False)
1429 list_unsubscribe_header = Column(String, nullable=True)
1430 source_data = Column(String, nullable=True)
1433class SMS(Base):
1434 """
1435 Table of all sent SMSs for debugging purposes, etc.
1436 """
1438 __tablename__ = "smss"
1440 id = Column(BigInteger, primary_key=True)
1442 # timezone should always be UTC
1443 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1444 # AWS message id
1445 message_id = Column(String, nullable=False)
1447 # the SMS sender ID sent to AWS, name that the SMS appears to come from
1448 sms_sender_id = Column(String, nullable=False)
1449 number = Column(String, nullable=False)
1450 message = Column(String, nullable=False)
1453class HostRequest(Base):
1454 """
1455 A request to stay with a host
1456 """
1458 __tablename__ = "host_requests"
1460 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1461 surfer_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1462 host_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1464 # TODO: proper timezone handling
1465 timezone = "Etc/UTC"
1467 # dates in the timezone above
1468 from_date = Column(Date, nullable=False)
1469 to_date = Column(Date, nullable=False)
1471 # timezone aware start and end times of the request, can be compared to now()
1472 start_time = column_property(date_in_timezone(from_date, timezone))
1473 end_time = column_property(date_in_timezone(to_date, timezone) + text("interval '1 days'"))
1474 start_time_to_write_reference = column_property(date_in_timezone(to_date, timezone))
1475 # notice 1 day for midnight at the *end of the day*, then 14 days to write a ref
1476 end_time_to_write_reference = column_property(date_in_timezone(to_date, timezone) + text("interval '15 days'"))
1478 status = Column(Enum(HostRequestStatus), nullable=False)
1479 is_host_archived = Column(Boolean, nullable=False, default=False, server_default=text("false"))
1480 is_surfer_archived = Column(Boolean, nullable=False, default=False, server_default=text("false"))
1482 host_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1483 surfer_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1485 # number of reference reminders sent out
1486 host_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1487 surfer_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1488 host_sent_request_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1489 last_sent_request_reminder_time = Column(DateTime, nullable=False, server_default=func.now())
1491 # reason why the host/surfer marked that they didn't meet up
1492 # if null then they haven't marked it such
1493 host_reason_didnt_meetup = Column(String, nullable=True)
1494 surfer_reason_didnt_meetup = Column(String, nullable=True)
1496 surfer = relationship("User", backref="host_requests_sent", foreign_keys="HostRequest.surfer_user_id")
1497 host = relationship("User", backref="host_requests_received", foreign_keys="HostRequest.host_user_id")
1498 conversation = relationship("Conversation")
1500 __table_args__ = (
1501 # allows fast lookup as to whether they didn't meet up
1502 Index(
1503 "ix_host_requests_host_didnt_meetup",
1504 host_reason_didnt_meetup != None,
1505 ),
1506 Index(
1507 "ix_host_requests_surfer_didnt_meetup",
1508 surfer_reason_didnt_meetup != None,
1509 ),
1510 # Used for figuring out who needs a reminder to respond
1511 Index(
1512 "ix_host_requests_status_reminder_counts",
1513 status,
1514 host_sent_request_reminders,
1515 last_sent_request_reminder_time,
1516 from_date,
1517 ),
1518 )
1520 @hybrid_property
1521 def can_write_reference(self):
1522 return (
1523 ((self.status == HostRequestStatus.confirmed) | (self.status == HostRequestStatus.accepted))
1524 & (now() >= self.start_time_to_write_reference)
1525 & (now() <= self.end_time_to_write_reference)
1526 )
1528 @can_write_reference.expression
1529 def can_write_reference(cls):
1530 return (
1531 ((cls.status == HostRequestStatus.confirmed) | (cls.status == HostRequestStatus.accepted))
1532 & (func.now() >= cls.start_time_to_write_reference)
1533 & (func.now() <= cls.end_time_to_write_reference)
1534 )
1536 def __repr__(self):
1537 return f"HostRequest(id={self.conversation_id}, surfer_user_id={self.surfer_user_id}, host_user_id={self.host_user_id}...)"
1540class ReferenceType(enum.Enum):
1541 friend = enum.auto()
1542 surfed = enum.auto() # The "from" user surfed with the "to" user
1543 hosted = enum.auto() # The "from" user hosted the "to" user
1546class Reference(Base):
1547 """
1548 Reference from one user to another
1549 """
1551 __tablename__ = "references"
1553 id = Column(BigInteger, primary_key=True)
1554 # timezone should always be UTC
1555 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1557 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1558 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1560 reference_type = Column(Enum(ReferenceType), nullable=False)
1562 host_request_id = Column(ForeignKey("host_requests.id"), nullable=True)
1564 text = Column(String, nullable=False) # plain text
1565 # text that's only visible to mods
1566 private_text = Column(String, nullable=True) # plain text
1568 rating = Column(Float, nullable=False)
1569 was_appropriate = Column(Boolean, nullable=False)
1571 is_deleted = Column(Boolean, nullable=False, default=False, server_default="false")
1573 from_user = relationship("User", backref="references_from", foreign_keys="Reference.from_user_id")
1574 to_user = relationship("User", backref="references_to", foreign_keys="Reference.to_user_id")
1576 host_request = relationship("HostRequest", backref="references")
1578 __table_args__ = (
1579 # Rating must be between 0 and 1, inclusive
1580 CheckConstraint(
1581 "rating BETWEEN 0 AND 1",
1582 name="rating_between_0_and_1",
1583 ),
1584 # Has host_request_id or it's a friend reference
1585 CheckConstraint(
1586 "(host_request_id IS NOT NULL) <> (reference_type = 'friend')",
1587 name="host_request_id_xor_friend_reference",
1588 ),
1589 # Each user can leave at most one friend reference to another user
1590 Index(
1591 "ix_references_unique_friend_reference",
1592 from_user_id,
1593 to_user_id,
1594 reference_type,
1595 unique=True,
1596 postgresql_where=(reference_type == ReferenceType.friend),
1597 ),
1598 # Each user can leave at most one reference to another user for each stay
1599 Index(
1600 "ix_references_unique_per_host_request",
1601 from_user_id,
1602 to_user_id,
1603 host_request_id,
1604 unique=True,
1605 postgresql_where=(host_request_id != None),
1606 ),
1607 )
1609 @property
1610 def should_report(self):
1611 """
1612 If this evaluates to true, we send a report to the moderation team.
1613 """
1614 return self.rating <= 0.4 or not self.was_appropriate or self.private_text
1617class InitiatedUpload(Base):
1618 """
1619 Started downloads, not necessarily complete yet.
1620 """
1622 __tablename__ = "initiated_uploads"
1624 key = Column(String, primary_key=True)
1626 # timezones should always be UTC
1627 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1628 expiry = Column(DateTime(timezone=True), nullable=False)
1630 initiator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1632 initiator_user = relationship("User")
1634 @hybrid_property
1635 def is_valid(self):
1636 return (self.created <= func.now()) & (self.expiry >= func.now())
1639class Upload(Base):
1640 """
1641 Completed uploads.
1642 """
1644 __tablename__ = "uploads"
1645 key = Column(String, primary_key=True)
1647 filename = Column(String, nullable=False)
1648 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1649 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1651 # photo credit, etc
1652 credit = Column(String, nullable=True)
1654 creator_user = relationship("User", backref="uploads", foreign_keys="Upload.creator_user_id")
1656 def _url(self, size):
1657 return urls.media_url(filename=self.filename, size=size)
1659 @property
1660 def thumbnail_url(self):
1661 return self._url("thumbnail")
1663 @property
1664 def full_url(self):
1665 return self._url("full")
1668communities_seq = Sequence("communities_seq")
1671class Node(Base):
1672 """
1673 Node, i.e. geographical subdivision of the world
1675 Administered by the official cluster
1676 """
1678 __tablename__ = "nodes"
1680 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1682 # name and description come from official cluster
1683 parent_node_id = Column(ForeignKey("nodes.id"), nullable=True, index=True)
1684 geom = deferred(Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False))
1685 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1687 parent_node = relationship("Node", backref="child_nodes", remote_side="Node.id")
1689 contained_users = relationship(
1690 "User",
1691 primaryjoin="func.ST_Contains(foreign(Node.geom), User.geom).as_comparison(1, 2)",
1692 viewonly=True,
1693 uselist=True,
1694 )
1696 contained_user_ids = association_proxy("contained_users", "id")
1699class Cluster(Base):
1700 """
1701 Cluster, administered grouping of content
1702 """
1704 __tablename__ = "clusters"
1706 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1707 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1708 name = Column(String, nullable=False)
1709 # short description
1710 description = Column(String, nullable=False)
1711 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1713 is_official_cluster = Column(Boolean, nullable=False, default=False)
1715 slug = column_property(func.slugify(name))
1717 official_cluster_for_node = relationship(
1718 "Node",
1719 primaryjoin="and_(Cluster.parent_node_id == Node.id, Cluster.is_official_cluster)",
1720 backref=backref("official_cluster", uselist=False),
1721 uselist=False,
1722 viewonly=True,
1723 )
1725 parent_node = relationship(
1726 "Node", backref="child_clusters", remote_side="Node.id", foreign_keys="Cluster.parent_node_id"
1727 )
1729 nodes = relationship("Cluster", backref="clusters", secondary="node_cluster_associations", viewonly=True)
1730 # all pages
1731 pages = relationship(
1732 "Page", backref="clusters", secondary="cluster_page_associations", lazy="dynamic", viewonly=True
1733 )
1734 events = relationship("Event", backref="clusters", secondary="cluster_event_associations", viewonly=True)
1735 discussions = relationship(
1736 "Discussion", backref="clusters", secondary="cluster_discussion_associations", viewonly=True
1737 )
1739 # includes also admins
1740 members = relationship(
1741 "User",
1742 lazy="dynamic",
1743 backref="cluster_memberships",
1744 secondary="cluster_subscriptions",
1745 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1746 secondaryjoin="User.id == ClusterSubscription.user_id",
1747 viewonly=True,
1748 )
1750 admins = relationship(
1751 "User",
1752 lazy="dynamic",
1753 backref="cluster_adminships",
1754 secondary="cluster_subscriptions",
1755 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1756 secondaryjoin="and_(User.id == ClusterSubscription.user_id, ClusterSubscription.role == 'admin')",
1757 viewonly=True,
1758 )
1760 main_page = relationship(
1761 "Page",
1762 primaryjoin="and_(Cluster.id == Page.owner_cluster_id, Page.type == 'main_page')",
1763 viewonly=True,
1764 uselist=False,
1765 )
1767 @property
1768 def is_leaf(self) -> bool:
1769 """Whether the cluster is a leaf node in the cluster hierarchy."""
1770 return len(self.parent_node.child_nodes) == 0
1772 __table_args__ = (
1773 # Each node can have at most one official cluster
1774 Index(
1775 "ix_clusters_owner_parent_node_id_is_official_cluster",
1776 parent_node_id,
1777 is_official_cluster,
1778 unique=True,
1779 postgresql_where=is_official_cluster,
1780 ),
1781 )
1784class NodeClusterAssociation(Base):
1785 """
1786 NodeClusterAssociation, grouping of nodes
1787 """
1789 __tablename__ = "node_cluster_associations"
1790 __table_args__ = (UniqueConstraint("node_id", "cluster_id"),)
1792 id = Column(BigInteger, primary_key=True)
1794 node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1795 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1797 node = relationship("Node", backref="node_cluster_associations")
1798 cluster = relationship("Cluster", backref="node_cluster_associations")
1801class ClusterRole(enum.Enum):
1802 member = enum.auto()
1803 admin = enum.auto()
1806class ClusterSubscription(Base):
1807 """
1808 ClusterSubscription of a user
1809 """
1811 __tablename__ = "cluster_subscriptions"
1813 id = Column(BigInteger, primary_key=True)
1815 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1816 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1817 role = Column(Enum(ClusterRole), nullable=False)
1819 user = relationship("User", backref="cluster_subscriptions")
1820 cluster = relationship("Cluster", backref="cluster_subscriptions")
1822 __table_args__ = (
1823 UniqueConstraint("user_id", "cluster_id"),
1824 Index(
1825 "ix_cluster_subscriptions_members",
1826 cluster_id,
1827 user_id,
1828 ),
1829 # For fast lookup of nodes this user is an admin of
1830 Index(
1831 "ix_cluster_subscriptions_admins",
1832 user_id,
1833 cluster_id,
1834 postgresql_where=(role == ClusterRole.admin),
1835 ),
1836 )
1839class ClusterPageAssociation(Base):
1840 """
1841 pages related to clusters
1842 """
1844 __tablename__ = "cluster_page_associations"
1845 __table_args__ = (UniqueConstraint("page_id", "cluster_id"),)
1847 id = Column(BigInteger, primary_key=True)
1849 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1850 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1852 page = relationship("Page", backref="cluster_page_associations")
1853 cluster = relationship("Cluster", backref="cluster_page_associations")
1856class PageType(enum.Enum):
1857 main_page = enum.auto()
1858 place = enum.auto()
1859 guide = enum.auto()
1862class Page(Base):
1863 """
1864 similar to a wiki page about a community, POI or guide
1865 """
1867 __tablename__ = "pages"
1869 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1871 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1872 type = Column(Enum(PageType), nullable=False)
1873 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1874 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1875 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1877 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
1879 parent_node = relationship("Node", backref="child_pages", remote_side="Node.id", foreign_keys="Page.parent_node_id")
1881 thread = relationship("Thread", backref="page", uselist=False)
1882 creator_user = relationship("User", backref="created_pages", foreign_keys="Page.creator_user_id")
1883 owner_user = relationship("User", backref="owned_pages", foreign_keys="Page.owner_user_id")
1884 owner_cluster = relationship(
1885 "Cluster", backref=backref("owned_pages", lazy="dynamic"), uselist=False, foreign_keys="Page.owner_cluster_id"
1886 )
1888 editors = relationship("User", secondary="page_versions", viewonly=True)
1890 __table_args__ = (
1891 # Only one of owner_user and owner_cluster should be set
1892 CheckConstraint(
1893 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
1894 name="one_owner",
1895 ),
1896 # Only clusters can own main pages
1897 CheckConstraint(
1898 "NOT (owner_cluster_id IS NULL AND type = 'main_page')",
1899 name="main_page_owned_by_cluster",
1900 ),
1901 # Each cluster can have at most one main page
1902 Index(
1903 "ix_pages_owner_cluster_id_type",
1904 owner_cluster_id,
1905 type,
1906 unique=True,
1907 postgresql_where=(type == PageType.main_page),
1908 ),
1909 )
1911 def __repr__(self):
1912 return f"Page({self.id=})"
1915class PageVersion(Base):
1916 """
1917 version of page content
1918 """
1920 __tablename__ = "page_versions"
1922 id = Column(BigInteger, primary_key=True)
1924 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1925 editor_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1926 title = Column(String, nullable=False)
1927 content = Column(String, nullable=False) # CommonMark without images
1928 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
1929 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
1930 # the human-readable address
1931 address = Column(String, nullable=True)
1932 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1934 slug = column_property(func.slugify(title))
1936 page = relationship("Page", backref="versions", order_by="PageVersion.id")
1937 editor_user = relationship("User", backref="edited_pages")
1938 photo = relationship("Upload")
1940 __table_args__ = (
1941 # Geom and address must either both be null or both be set
1942 CheckConstraint(
1943 "(geom IS NULL) = (address IS NULL)",
1944 name="geom_iff_address",
1945 ),
1946 )
1948 @property
1949 def coordinates(self):
1950 # returns (lat, lng) or None
1951 return get_coordinates(self.geom)
1953 def __repr__(self):
1954 return f"PageVersion({self.id=}, {self.page_id=})"
1957class ClusterEventAssociation(Base):
1958 """
1959 events related to clusters
1960 """
1962 __tablename__ = "cluster_event_associations"
1963 __table_args__ = (UniqueConstraint("event_id", "cluster_id"),)
1965 id = Column(BigInteger, primary_key=True)
1967 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1968 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1970 event = relationship("Event", backref="cluster_event_associations")
1971 cluster = relationship("Cluster", backref="cluster_event_associations")
1974class Event(Base):
1975 """
1976 An event is compose of two parts:
1978 * An event template (Event)
1979 * An occurrence (EventOccurrence)
1981 One-off events will have one of each; repeating events will have one Event,
1982 multiple EventOccurrences, one for each time the event happens.
1983 """
1985 __tablename__ = "events"
1987 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1988 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1990 title = Column(String, nullable=False)
1992 slug = column_property(func.slugify(title))
1994 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1995 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1996 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1997 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1998 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
2000 parent_node = relationship(
2001 "Node", backref="child_events", remote_side="Node.id", foreign_keys="Event.parent_node_id"
2002 )
2003 thread = relationship("Thread", backref="event", uselist=False)
2004 subscribers = relationship(
2005 "User", backref="subscribed_events", secondary="event_subscriptions", lazy="dynamic", viewonly=True
2006 )
2007 organizers = relationship(
2008 "User", backref="organized_events", secondary="event_organizers", lazy="dynamic", viewonly=True
2009 )
2010 creator_user = relationship("User", backref="created_events", foreign_keys="Event.creator_user_id")
2011 owner_user = relationship("User", backref="owned_events", foreign_keys="Event.owner_user_id")
2012 owner_cluster = relationship(
2013 "Cluster",
2014 backref=backref("owned_events", lazy="dynamic"),
2015 uselist=False,
2016 foreign_keys="Event.owner_cluster_id",
2017 )
2019 __table_args__ = (
2020 # Only one of owner_user and owner_cluster should be set
2021 CheckConstraint(
2022 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
2023 name="one_owner",
2024 ),
2025 )
2028class EventOccurrence(Base):
2029 __tablename__ = "event_occurrences"
2031 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
2032 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
2034 # the user that created this particular occurrence of a repeating event (same as event.creator_user_id if single event)
2035 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2036 content = Column(String, nullable=False) # CommonMark without images
2037 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
2039 is_cancelled = Column(Boolean, nullable=False, default=False, server_default=text("false"))
2040 is_deleted = Column(Boolean, nullable=False, default=False, server_default=text("false"))
2042 # a null geom is an online-only event
2043 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
2044 # physical address, iff geom is not null
2045 address = Column(String, nullable=True)
2046 # videoconferencing link, etc, must be specified if no geom, otherwise optional
2047 link = Column(String, nullable=True)
2049 timezone = "Etc/UTC"
2051 # time during which the event takes place; this is a range type (instead of separate start+end times) which
2052 # simplifies database constraints, etc
2053 during = Column(TSTZRANGE, nullable=False)
2055 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2056 last_edited = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2058 creator_user = relationship(
2059 "User", backref="created_event_occurrences", foreign_keys="EventOccurrence.creator_user_id"
2060 )
2061 event = relationship(
2062 "Event",
2063 backref=backref("occurrences", lazy="dynamic"),
2064 remote_side="Event.id",
2065 foreign_keys="EventOccurrence.event_id",
2066 )
2068 photo = relationship("Upload")
2070 __table_args__ = (
2071 # Geom and address go together
2072 CheckConstraint(
2073 # geom and address are either both null or neither of them are null
2074 "(geom IS NULL) = (address IS NULL)",
2075 name="geom_iff_address",
2076 ),
2077 # Online-only events need a link, note that online events may also have a link
2078 CheckConstraint(
2079 # exactly oen of geom or link is non-null
2080 "(geom IS NULL) <> (link IS NULL)",
2081 name="link_or_geom",
2082 ),
2083 # Can't have overlapping occurrences in the same Event
2084 ExcludeConstraint(("event_id", "="), ("during", "&&"), name="event_occurrences_event_id_during_excl"),
2085 )
2087 @property
2088 def coordinates(self):
2089 # returns (lat, lng) or None
2090 return get_coordinates(self.geom)
2092 @hybrid_property
2093 def start_time(self):
2094 return self.during.lower
2096 @start_time.expression
2097 def start_time(cls):
2098 return func.lower(cls.during)
2100 @hybrid_property
2101 def end_time(self):
2102 return self.during.upper
2104 @end_time.expression
2105 def end_time(cls):
2106 return func.upper(cls.during)
2109class EventSubscription(Base):
2110 """
2111 Users' subscriptions to events
2112 """
2114 __tablename__ = "event_subscriptions"
2115 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
2117 id = Column(BigInteger, primary_key=True)
2119 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2120 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
2121 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2123 user = relationship("User")
2124 event = relationship("Event")
2127class EventOrganizer(Base):
2128 """
2129 Organizers for events
2130 """
2132 __tablename__ = "event_organizers"
2133 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
2135 id = Column(BigInteger, primary_key=True)
2137 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2138 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
2139 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2141 user = relationship("User")
2142 event = relationship("Event")
2145class AttendeeStatus(enum.Enum):
2146 going = enum.auto()
2147 maybe = enum.auto()
2150class EventOccurrenceAttendee(Base):
2151 """
2152 Attendees for events
2153 """
2155 __tablename__ = "event_occurrence_attendees"
2156 __table_args__ = (UniqueConstraint("occurrence_id", "user_id"),)
2158 id = Column(BigInteger, primary_key=True)
2160 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2161 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
2162 responded = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2163 attendee_status = Column(Enum(AttendeeStatus), nullable=False)
2165 user = relationship("User")
2166 occurrence = relationship("EventOccurrence", backref=backref("attendances", lazy="dynamic"))
2169class EventCommunityInviteRequest(Base):
2170 """
2171 Requests to send out invitation notifications/emails to the community for a given event occurrence
2172 """
2174 __tablename__ = "event_community_invite_requests"
2176 id = Column(BigInteger, primary_key=True)
2178 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
2179 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2181 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2183 decided = Column(DateTime(timezone=True), nullable=True)
2184 decided_by_user_id = Column(ForeignKey("users.id"), nullable=True)
2185 approved = Column(Boolean, nullable=True)
2187 occurrence = relationship("EventOccurrence", backref=backref("community_invite_requests", lazy="dynamic"))
2188 user = relationship("User", foreign_keys="EventCommunityInviteRequest.user_id")
2190 __table_args__ = (
2191 # each user can only request once
2192 UniqueConstraint("occurrence_id", "user_id"),
2193 # each event can only have one notification sent out
2194 Index(
2195 "ix_event_community_invite_requests_unique",
2196 occurrence_id,
2197 unique=True,
2198 postgresql_where=and_(approved.is_not(None), approved == True),
2199 ),
2200 # decided and approved ought to be null simultaneously
2201 CheckConstraint(
2202 "((decided IS NULL) AND (decided_by_user_id IS NULL) AND (approved IS NULL)) OR \
2203 ((decided IS NOT NULL) AND (decided_by_user_id IS NOT NULL) AND (approved IS NOT NULL))",
2204 name="decided_approved",
2205 ),
2206 )
2209class ClusterDiscussionAssociation(Base):
2210 """
2211 discussions related to clusters
2212 """
2214 __tablename__ = "cluster_discussion_associations"
2215 __table_args__ = (UniqueConstraint("discussion_id", "cluster_id"),)
2217 id = Column(BigInteger, primary_key=True)
2219 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2220 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2222 discussion = relationship("Discussion", backref="cluster_discussion_associations")
2223 cluster = relationship("Cluster", backref="cluster_discussion_associations")
2226class Discussion(Base):
2227 """
2228 forum board
2229 """
2231 __tablename__ = "discussions"
2233 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
2235 title = Column(String, nullable=False)
2236 content = Column(String, nullable=False)
2237 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
2238 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2240 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2241 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2243 slug = column_property(func.slugify(title))
2245 thread = relationship("Thread", backref="discussion", uselist=False)
2247 subscribers = relationship("User", backref="discussions", secondary="discussion_subscriptions", viewonly=True)
2249 creator_user = relationship("User", backref="created_discussions", foreign_keys="Discussion.creator_user_id")
2250 owner_cluster = relationship("Cluster", backref=backref("owned_discussions", lazy="dynamic"), uselist=False)
2253class DiscussionSubscription(Base):
2254 """
2255 users subscriptions to discussions
2256 """
2258 __tablename__ = "discussion_subscriptions"
2259 __table_args__ = (UniqueConstraint("discussion_id", "user_id"),)
2261 id = Column(BigInteger, primary_key=True)
2263 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2264 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2265 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2266 left = Column(DateTime(timezone=True), nullable=True)
2268 user = relationship("User", backref="discussion_subscriptions")
2269 discussion = relationship("Discussion", backref="discussion_subscriptions")
2272class Thread(Base):
2273 """
2274 Thread
2275 """
2277 __tablename__ = "threads"
2279 id = Column(BigInteger, primary_key=True)
2281 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2282 deleted = Column(DateTime(timezone=True), nullable=True)
2285class Comment(Base):
2286 """
2287 Comment
2288 """
2290 __tablename__ = "comments"
2292 id = Column(BigInteger, primary_key=True)
2294 thread_id = Column(ForeignKey("threads.id"), nullable=False, index=True)
2295 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2296 content = Column(String, nullable=False) # CommonMark without images
2297 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2298 deleted = Column(DateTime(timezone=True), nullable=True)
2300 thread = relationship("Thread", backref="comments")
2303class Reply(Base):
2304 """
2305 Reply
2306 """
2308 __tablename__ = "replies"
2310 id = Column(BigInteger, primary_key=True)
2312 comment_id = Column(ForeignKey("comments.id"), nullable=False, index=True)
2313 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2314 content = Column(String, nullable=False) # CommonMark without images
2315 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2316 deleted = Column(DateTime(timezone=True), nullable=True)
2318 comment = relationship("Comment", backref="replies")
2321class BackgroundJobState(enum.Enum):
2322 # job is fresh, waiting to be picked off the queue
2323 pending = enum.auto()
2324 # job complete
2325 completed = enum.auto()
2326 # error occured, will be retried
2327 error = enum.auto()
2328 # failed too many times, not retrying anymore
2329 failed = enum.auto()
2332class BackgroundJob(Base):
2333 """
2334 This table implements a queue of background jobs.
2335 """
2337 __tablename__ = "background_jobs"
2339 id = Column(BigInteger, primary_key=True)
2341 # used to discern which function should be triggered to service it
2342 job_type = Column(String, nullable=False)
2343 state = Column(Enum(BackgroundJobState), nullable=False, default=BackgroundJobState.pending)
2345 # time queued
2346 queued = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2348 # time at which we may next attempt it, for implementing exponential backoff
2349 next_attempt_after = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2351 # used to count number of retries for failed jobs
2352 try_count = Column(Integer, nullable=False, default=0)
2354 max_tries = Column(Integer, nullable=False, default=5)
2356 # higher is more important
2357 priority = Column(Integer, nullable=False, server_default=text("10"))
2359 # protobuf encoded job payload
2360 payload = Column(Binary, nullable=False)
2362 # if the job failed, we write that info here
2363 failure_info = Column(String, nullable=True)
2365 __table_args__ = (
2366 # used in looking up background jobs to attempt
2367 # create index on background_jobs(priority desc, next_attempt_after, (max_tries - try_count)) where state = 'pending' OR state = 'error';
2368 Index(
2369 "ix_background_jobs_lookup",
2370 priority.desc(),
2371 next_attempt_after,
2372 (max_tries - try_count),
2373 postgresql_where=((state == BackgroundJobState.pending) | (state == BackgroundJobState.error)),
2374 ),
2375 )
2377 @hybrid_property
2378 def ready_for_retry(self):
2379 return (
2380 (self.next_attempt_after <= func.now())
2381 & (self.try_count < self.max_tries)
2382 & ((self.state == BackgroundJobState.pending) | (self.state == BackgroundJobState.error))
2383 )
2385 def __repr__(self):
2386 return f"BackgroundJob(id={self.id}, job_type={self.job_type}, state={self.state}, next_attempt_after={self.next_attempt_after}, try_count={self.try_count}, failure_info={self.failure_info})"
2389class NotificationDeliveryType(enum.Enum):
2390 # send push notification to mobile/web
2391 push = enum.auto()
2392 # send individual email immediately
2393 email = enum.auto()
2394 # send in digest
2395 digest = enum.auto()
2398dt = NotificationDeliveryType
2399nd = notification_data_pb2
2401dt_sec = [dt.email, dt.push]
2402dt_all = [dt.email, dt.push, dt.digest]
2405class NotificationTopicAction(enum.Enum):
2406 def __init__(self, topic_action, defaults, user_editable, data_type):
2407 self.topic, self.action = topic_action.split(":")
2408 self.defaults = defaults
2409 # for now user editable == not a security notification
2410 self.user_editable = user_editable
2412 self.data_type = data_type
2414 def unpack(self):
2415 return self.topic, self.action
2417 @property
2418 def display(self):
2419 return f"{self.topic}:{self.action}"
2421 def __str__(self):
2422 return self.display
2424 # topic, action, default delivery types
2425 friend_request__create = ("friend_request:create", dt_all, True, nd.FriendRequestCreate)
2426 friend_request__accept = ("friend_request:accept", dt_all, True, nd.FriendRequestAccept)
2428 # host requests
2429 host_request__create = ("host_request:create", dt_all, True, nd.HostRequestCreate)
2430 host_request__accept = ("host_request:accept", dt_all, True, nd.HostRequestAccept)
2431 host_request__reject = ("host_request:reject", dt_all, True, nd.HostRequestReject)
2432 host_request__confirm = ("host_request:confirm", dt_all, True, nd.HostRequestConfirm)
2433 host_request__cancel = ("host_request:cancel", dt_all, True, nd.HostRequestCancel)
2434 host_request__message = ("host_request:message", [dt.push, dt.digest], True, nd.HostRequestMessage)
2435 host_request__missed_messages = ("host_request:missed_messages", [dt.email], True, nd.HostRequestMissedMessages)
2436 host_request__reminder = ("host_request:reminder", dt_all, True, nd.HostRequestReminder)
2438 activeness__probe = ("activeness:probe", dt_sec, False, nd.ActivenessProbe)
2440 # you receive a friend ref
2441 reference__receive_friend = ("reference:receive_friend", dt_all, True, nd.ReferenceReceiveFriend)
2442 # you receive a reference from ... the host
2443 reference__receive_hosted = ("reference:receive_hosted", dt_all, True, nd.ReferenceReceiveHostRequest)
2444 # ... the surfer
2445 reference__receive_surfed = ("reference:receive_surfed", dt_all, True, nd.ReferenceReceiveHostRequest)
2447 # you hosted
2448 reference__reminder_hosted = ("reference:reminder_hosted", dt_all, True, nd.ReferenceReminder)
2449 # you surfed
2450 reference__reminder_surfed = ("reference:reminder_surfed", dt_all, True, nd.ReferenceReminder)
2452 badge__add = ("badge:add", [dt.push, dt.digest], True, nd.BadgeAdd)
2453 badge__remove = ("badge:remove", [dt.push, dt.digest], True, nd.BadgeRemove)
2455 # group chats
2456 chat__message = ("chat:message", [dt.push, dt.digest], True, nd.ChatMessage)
2457 chat__missed_messages = ("chat:missed_messages", [dt.email], True, nd.ChatMissedMessages)
2459 # events
2460 # approved by mods
2461 event__create_approved = ("event:create_approved", dt_all, True, nd.EventCreate)
2462 # any user creates any event, default to no notifications
2463 event__create_any = ("event:create_any", [], True, nd.EventCreate)
2464 event__update = ("event:update", dt_all, True, nd.EventUpdate)
2465 event__cancel = ("event:cancel", dt_all, True, nd.EventCancel)
2466 event__delete = ("event:delete", dt_all, True, nd.EventDelete)
2467 event__invite_organizer = ("event:invite_organizer", dt_all, True, nd.EventInviteOrganizer)
2468 # toplevel comment on an event
2469 event__comment = ("event:comment", dt_all, True, nd.EventComment)
2471 # discussion created
2472 discussion__create = ("discussion:create", [dt.digest], True, nd.DiscussionCreate)
2473 # someone comments on your discussion
2474 discussion__comment = ("discussion:comment", dt_all, True, nd.DiscussionComment)
2476 # someone responds to any of your top-level comment across the platform
2477 thread__reply = ("thread:reply", dt_all, True, nd.ThreadReply)
2479 # account settings
2480 password__change = ("password:change", dt_sec, False, empty_pb2.Empty)
2481 email_address__change = ("email_address:change", dt_sec, False, nd.EmailAddressChange)
2482 email_address__verify = ("email_address:verify", dt_sec, False, empty_pb2.Empty)
2483 phone_number__change = ("phone_number:change", dt_sec, False, nd.PhoneNumberChange)
2484 phone_number__verify = ("phone_number:verify", dt_sec, False, nd.PhoneNumberVerify)
2485 # reset password
2486 password_reset__start = ("password_reset:start", dt_sec, False, nd.PasswordResetStart)
2487 password_reset__complete = ("password_reset:complete", dt_sec, False, empty_pb2.Empty)
2489 # account deletion
2490 account_deletion__start = ("account_deletion:start", dt_sec, False, nd.AccountDeletionStart)
2491 # no more pushing to do
2492 account_deletion__complete = ("account_deletion:complete", dt_sec, False, nd.AccountDeletionComplete)
2493 # undeleted
2494 account_deletion__recovered = ("account_deletion:recovered", dt_sec, False, empty_pb2.Empty)
2496 # admin actions
2497 gender__change = ("gender:change", dt_sec, False, nd.GenderChange)
2498 birthdate__change = ("birthdate:change", dt_sec, False, nd.BirthdateChange)
2499 api_key__create = ("api_key:create", dt_sec, False, nd.ApiKeyCreate)
2501 donation__received = ("donation:received", dt_sec, True, nd.DonationReceived)
2503 onboarding__reminder = ("onboarding:reminder", dt_sec, True, empty_pb2.Empty)
2505 modnote__create = ("modnote:create", dt_sec, False, empty_pb2.Empty)
2507 verification__sv_fail = ("verification:sv_fail", dt_sec, False, nd.VerificationSVFail)
2508 verification__sv_success = ("verification:sv_success", dt_sec, False, empty_pb2.Empty)
2510 # general announcements
2511 general__new_blog_post = ("general:new_blog_post", [dt.push, dt.digest], True, nd.GeneralNewBlogPost)
2514class NotificationPreference(Base):
2515 __tablename__ = "notification_preferences"
2517 id = Column(BigInteger, primary_key=True)
2518 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2520 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2521 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2522 deliver = Column(Boolean, nullable=False)
2524 user = relationship("User", foreign_keys="NotificationPreference.user_id")
2526 __table_args__ = (UniqueConstraint("user_id", "topic_action", "delivery_type"),)
2529class Notification(Base):
2530 """
2531 Table for accumulating notifications until it is time to send email digest
2532 """
2534 __tablename__ = "notifications"
2536 id = Column(BigInteger, primary_key=True)
2537 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2539 # recipient user id
2540 user_id = Column(ForeignKey("users.id"), nullable=False)
2542 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2543 key = Column(String, nullable=False)
2545 data = Column(Binary, nullable=False)
2547 # whether the user has marked this notification as seen or not
2548 is_seen = Column(Boolean, nullable=False, server_default=text("false"))
2550 user = relationship("User", foreign_keys="Notification.user_id")
2552 __table_args__ = (
2553 # used in looking up which notifications need delivery
2554 Index(
2555 "ix_notifications_created",
2556 created,
2557 ),
2558 # Fast lookup for unseen notification count
2559 Index(
2560 "ix_notifications_unseen",
2561 user_id,
2562 topic_action,
2563 postgresql_where=(is_seen == False),
2564 ),
2565 # Fast lookup for latest notifications
2566 Index(
2567 "ix_notifications_latest",
2568 user_id,
2569 id.desc(),
2570 topic_action,
2571 ),
2572 )
2574 @property
2575 def topic(self):
2576 return self.topic_action.topic
2578 @property
2579 def action(self):
2580 return self.topic_action.action
2583class NotificationDelivery(Base):
2584 __tablename__ = "notification_deliveries"
2586 id = Column(BigInteger, primary_key=True)
2587 notification_id = Column(ForeignKey("notifications.id"), nullable=False, index=True)
2588 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2589 delivered = Column(DateTime(timezone=True), nullable=True)
2590 read = Column(DateTime(timezone=True), nullable=True)
2591 # todo: enum of "phone, web, digest"
2592 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2593 # todo: device id
2594 # todo: receipt id, etc
2595 notification = relationship("Notification", foreign_keys="NotificationDelivery.notification_id")
2597 __table_args__ = (
2598 UniqueConstraint("notification_id", "delivery_type"),
2599 # used in looking up which notifications need delivery
2600 Index(
2601 "ix_notification_deliveries_delivery_type",
2602 delivery_type,
2603 postgresql_where=(delivered != None),
2604 ),
2605 Index(
2606 "ix_notification_deliveries_dt_ni_dnull",
2607 delivery_type,
2608 notification_id,
2609 delivered == None,
2610 ),
2611 )
2614class PushNotificationSubscription(Base):
2615 __tablename__ = "push_notification_subscriptions"
2617 id = Column(BigInteger, primary_key=True)
2618 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2620 # which user this is connected to
2621 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2623 # these come from https://developer.mozilla.org/en-US/docs/Web/API/PushSubscription
2624 # the endpoint
2625 endpoint = Column(String, nullable=False)
2626 # the "auth" key
2627 auth_key = Column(Binary, nullable=False)
2628 # the "p256dh" key
2629 p256dh_key = Column(Binary, nullable=False)
2631 full_subscription_info = Column(String, nullable=False)
2633 # the browse user-agent, so we can tell the user what browser notifications are going to
2634 user_agent = Column(String, nullable=True)
2636 # when it was disabled
2637 disabled_at = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_INFINITY.isoformat())
2639 user = relationship("User")
2642class PushNotificationDeliveryAttempt(Base):
2643 __tablename__ = "push_notification_delivery_attempt"
2645 id = Column(BigInteger, primary_key=True)
2646 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2648 push_notification_subscription_id = Column(
2649 ForeignKey("push_notification_subscriptions.id"), nullable=False, index=True
2650 )
2652 success = Column(Boolean, nullable=False)
2653 # the HTTP status code, 201 is success
2654 status_code = Column(Integer, nullable=False)
2656 # can be null if it was a success
2657 response = Column(String, nullable=True)
2659 push_notification_subscription = relationship("PushNotificationSubscription")
2662class Language(Base):
2663 """
2664 Table of allowed languages (a subset of ISO639-3)
2665 """
2667 __tablename__ = "languages"
2669 # ISO639-3 language code, in lowercase, e.g. fin, eng
2670 code = Column(String(3), primary_key=True)
2672 # the english name
2673 name = Column(String, nullable=False, unique=True)
2676class Region(Base):
2677 """
2678 Table of regions
2679 """
2681 __tablename__ = "regions"
2683 # iso 3166-1 alpha3 code in uppercase, e.g. FIN, USA
2684 code = Column(String(3), primary_key=True)
2686 # the name, e.g. Finland, United States
2687 # this is the display name in English, should be the "common name", not "Republic of Finland"
2688 name = Column(String, nullable=False, unique=True)
2691class UserBlock(Base):
2692 """
2693 Table of blocked users
2694 """
2696 __tablename__ = "user_blocks"
2698 id = Column(BigInteger, primary_key=True)
2700 blocking_user_id = Column(ForeignKey("users.id"), nullable=False)
2701 blocked_user_id = Column(ForeignKey("users.id"), nullable=False)
2702 time_blocked = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2704 blocking_user = relationship("User", foreign_keys="UserBlock.blocking_user_id")
2705 blocked_user = relationship("User", foreign_keys="UserBlock.blocked_user_id")
2707 __table_args__ = (
2708 UniqueConstraint("blocking_user_id", "blocked_user_id"),
2709 Index("ix_user_blocks_blocking_user_id", blocking_user_id, blocked_user_id),
2710 Index("ix_user_blocks_blocked_user_id", blocked_user_id, blocking_user_id),
2711 )
2714class APICall(Base):
2715 """
2716 API call logs
2717 """
2719 __tablename__ = "api_calls"
2720 __table_args__ = {"schema": "logging"}
2722 id = Column(BigInteger, primary_key=True)
2724 # whether the call was made using an api key or session cookies
2725 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))
2727 # backend version (normally e.g. develop-31469e3), allows us to figure out which proto definitions were used
2728 # note that `default` is a python side default, not hardcoded into DB schema
2729 version = Column(String, nullable=False, default=config["VERSION"])
2731 # approximate time of the call
2732 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2734 # the method call name, e.g. "/org.couchers.api.core.API/ListFriends"
2735 method = Column(String, nullable=False)
2737 # gRPC status code name, e.g. FAILED_PRECONDITION, None if success
2738 status_code = Column(String, nullable=True)
2740 # handler duration (excluding serialization, etc)
2741 duration = Column(Float, nullable=False)
2743 # user_id of caller, None means not logged in
2744 user_id = Column(BigInteger, nullable=True)
2746 # sanitized request bytes
2747 request = Column(Binary, nullable=True)
2749 # sanitized response bytes
2750 response = Column(Binary, nullable=True)
2752 # whether response bytes have been truncated
2753 response_truncated = Column(Boolean, nullable=False, server_default=text("false"))
2755 # the exception traceback, if any
2756 traceback = Column(String, nullable=True)
2758 # human readable perf report
2759 perf_report = Column(String, nullable=True)
2761 # details of the browser, if available
2762 ip_address = Column(String, nullable=True)
2763 user_agent = Column(String, nullable=True)
2766class AccountDeletionReason(Base):
2767 __tablename__ = "account_deletion_reason"
2769 id = Column(BigInteger, primary_key=True)
2770 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2771 user_id = Column(ForeignKey("users.id"), nullable=False)
2772 reason = Column(String, nullable=True)
2774 user = relationship("User")
2777class AntiBotLog(Base):
2778 __tablename__ = "antibot_logs"
2780 id = Column(BigInteger, primary_key=True)
2781 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2782 user_id = Column(ForeignKey("users.id"), nullable=True)
2784 ip_address = Column(String, nullable=True)
2785 user_agent = Column(String, nullable=True)
2787 action = Column(String, nullable=False)
2788 token = Column(String, nullable=False)
2790 score = Column(Float, nullable=False)
2791 provider_data = Column(JSON, nullable=False)
2794class RateLimitAction(enum.Enum):
2795 """Possible user actions which can be rate limited."""
2797 host_request = "host request"
2798 friend_request = "friend request"
2799 chat_initiation = "chat initiation"
2802class RateLimitViolation(Base):
2803 __tablename__ = "rate_limit_violations"
2805 id = Column(BigInteger, primary_key=True)
2806 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2807 user_id = Column(ForeignKey("users.id"), nullable=False)
2808 action = Column(Enum(RateLimitAction), nullable=False)
2809 is_hard_limit = Column(Boolean, nullable=False)
2811 user = relationship("User")
2813 __table_args__ = (
2814 # Fast lookup for rate limits in interval
2815 Index("ix_rate_limits_by_user", user_id, action, is_hard_limit, created),
2816 )