Coverage for src/couchers/models.py: 99%
1224 statements
« prev ^ index » next coverage.py v7.11.0, created at 2025-11-02 20:25 +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_, expression, func, not_, or_, 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=expression.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=expression.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=expression.false())
205 is_deleted = Column(Boolean, nullable=False, server_default=expression.false())
206 is_superuser = Column(Boolean, nullable=False, server_default=expression.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=expression.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=expression.false())
257 # opted out of the newsletter
258 opt_out_of_newsletter = Column(Boolean, nullable=False, server_default=expression.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=expression.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=expression.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=expression.false())
312 # checking for phone verification
313 has_donated = Column(Boolean, nullable=False, server_default=expression.false())
315 # whether this user has all emails turned off
316 do_not_email = Column(Boolean, nullable=False, server_default=expression.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=expression.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 # ID of the invite code used to sign up (if any)
330 invite_code_id = Column(ForeignKey("invite_codes.id"), nullable=True)
331 invite_code = relationship("InviteCode", foreign_keys=[invite_code_id])
333 moderation_user_lists = relationship(
334 "ModerationUserList", secondary="moderation_user_list_members", back_populates="users"
335 )
337 __table_args__ = (
338 # Verified phone numbers should be unique
339 Index(
340 "ix_users_unique_phone",
341 phone,
342 unique=True,
343 postgresql_where=phone_verification_verified != None,
344 ),
345 Index(
346 "ix_users_active",
347 id,
348 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
349 ),
350 # create index on users(geom, id, username) where not is_banned and not is_deleted and geom is not null;
351 Index(
352 "ix_users_geom_active",
353 geom,
354 id,
355 username,
356 postgresql_using="gist",
357 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
358 ),
359 Index(
360 "ix_users_by_id",
361 id,
362 postgresql_using="hash",
363 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
364 ),
365 Index(
366 "ix_users_by_username",
367 username,
368 postgresql_using="hash",
369 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
370 ),
371 # There are two possible states for new_email_token, new_email_token_created, and new_email_token_expiry
372 CheckConstraint(
373 "(new_email_token IS NOT NULL AND new_email_token_created IS NOT NULL AND new_email_token_expiry IS NOT NULL) OR \
374 (new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL)",
375 name="check_new_email_token_state",
376 ),
377 # Whenever a phone number is set, it must either be pending verification or already verified.
378 # Exactly one of the following must always be true: not phone, token, verified.
379 CheckConstraint(
380 "(phone IS NULL)::int + (phone_verification_verified IS NOT NULL)::int + (phone_verification_token IS NOT NULL)::int = 1",
381 name="phone_verified_conditions",
382 ),
383 # Email must match our regex
384 CheckConstraint(
385 f"email ~ '{EMAIL_REGEX}'",
386 name="valid_email",
387 ),
388 # Undelete token + time are coupled: either both null or neither; and if they're not null then the account is deleted
389 CheckConstraint(
390 "((undelete_token IS NULL) = (undelete_until IS NULL)) AND ((undelete_token IS NULL) OR is_deleted)",
391 name="undelete_nullity",
392 ),
393 # If the user disabled all emails, then they can't host or meet up
394 CheckConstraint(
395 "(do_not_email IS FALSE) OR ((hosting_status = 'cant_host') AND (meetup_status = 'does_not_want_to_meetup'))",
396 name="do_not_email_inactive",
397 ),
398 )
400 @hybrid_property
401 def has_completed_profile(self):
402 return self.avatar_key is not None and self.about_me is not None and len(self.about_me) >= 150
404 @has_completed_profile.expression
405 def has_completed_profile(cls):
406 return (cls.avatar_key != None) & (func.character_length(cls.about_me) >= 150)
408 @hybrid_property
409 def has_completed_my_home(self):
410 # completed my profile means that:
411 # 1. has filled out max_guests
412 # 2. has filled out sleeping_arrangement (sleeping privacy)
413 # 3. has some text in at least one of the my home free text fields
414 return (
415 self.max_guests is not None
416 and self.sleeping_arrangement is not None
417 and (
418 self.about_place is not None
419 or self.other_host_info is not None
420 or self.sleeping_details is not None
421 or self.area is not None
422 or self.house_rules is not None
423 )
424 )
426 @has_completed_my_home.expression
427 def has_completed_my_home(cls):
428 return and_(
429 cls.max_guests != None,
430 cls.sleeping_arrangement != None,
431 or_(
432 cls.about_place != None,
433 cls.other_host_info != None,
434 cls.sleeping_details != None,
435 cls.area != None,
436 cls.house_rules != None,
437 ),
438 )
440 @hybrid_property
441 def jailed_missing_tos(self):
442 return self.accepted_tos < TOS_VERSION
444 @hybrid_property
445 def jailed_missing_community_guidelines(self):
446 return self.accepted_community_guidelines < GUIDELINES_VERSION
448 @hybrid_property
449 def jailed_pending_mod_notes(self):
450 return self.mod_notes.where(ModNote.is_pending).count() > 0
452 @hybrid_property
453 def jailed_pending_activeness_probe(self):
454 return self.pending_activeness_probe != None
456 @hybrid_property
457 def is_jailed(self):
458 return (
459 self.jailed_missing_tos
460 | self.jailed_missing_community_guidelines
461 | self.is_missing_location
462 | self.jailed_pending_mod_notes
463 | self.jailed_pending_activeness_probe
464 )
466 @hybrid_property
467 def is_missing_location(self):
468 return self.needs_to_update_location
470 @hybrid_property
471 def is_visible(self):
472 return not self.is_banned and not self.is_deleted
474 @is_visible.expression
475 def is_visible(cls):
476 return ~(cls.is_banned | cls.is_deleted)
478 @property
479 def coordinates(self):
480 return get_coordinates(self.geom)
482 @property
483 def display_joined(self):
484 """
485 Returns the last active time rounded down to the nearest hour.
486 """
487 return self.joined.replace(minute=0, second=0, microsecond=0)
489 @property
490 def display_last_active(self):
491 """
492 Returns the last active time rounded down whatever is the "last active" coarsening.
493 """
494 return last_active_coarsen(self.last_active)
496 @hybrid_property
497 def phone_is_verified(self):
498 return (
499 self.phone_verification_verified is not None
500 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
501 )
503 @phone_is_verified.expression
504 def phone_is_verified(cls):
505 return (cls.phone_verification_verified != None) & (
506 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
507 )
509 @hybrid_property
510 def phone_code_expired(self):
511 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME
513 def __repr__(self):
514 return f"User(id={self.id}, email={self.email}, username={self.username})"
517class UserBadge(Base):
518 """
519 A badge on a user's profile
520 """
522 __tablename__ = "user_badges"
523 __table_args__ = (UniqueConstraint("user_id", "badge_id"),)
525 id = Column(BigInteger, primary_key=True)
527 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
528 # corresponds to "id" in badges.json
529 badge_id = Column(String, nullable=False, index=True)
531 # take this with a grain of salt, someone may get then lose a badge for whatever reason
532 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
534 user = relationship("User", backref="badges")
537class ActivenessProbeStatus(enum.Enum):
538 # no response yet
539 pending = enum.auto()
541 # didn't respond on time
542 expired = enum.auto()
544 # responded that they're still active
545 still_active = enum.auto()
547 # responded that they're no longer active
548 no_longer_active = enum.auto()
551class ActivenessProbe(Base):
552 """
553 Activeness probes are used to gauge if users are still active: we send them a notification and ask them to respond,
554 we use this data both to help indicate response rate, as well as to make sure only those who are actively hosting
555 show up as such.
556 """
558 __tablename__ = "activeness_probes"
560 id = Column(BigInteger, primary_key=True)
562 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
563 # the time this probe was initiated
564 probe_initiated = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
565 # the number of reminders sent for this probe
566 notifications_sent = Column(Integer, nullable=False, server_default="0")
568 # the time of response
569 responded = Column(DateTime(timezone=True), nullable=True, default=None)
570 # the response value
571 response = Column(Enum(ActivenessProbeStatus), nullable=False, default=ActivenessProbeStatus.pending)
573 @hybrid_property
574 def is_pending(self):
575 return self.responded == None
577 user = relationship("User", back_populates="pending_activeness_probe")
579 __table_args__ = (
580 # a user can have at most one pending activeness probe at a time
581 Index(
582 "ix_activeness_probe_unique_pending_response",
583 user_id,
584 unique=True,
585 postgresql_where=responded == None,
586 ),
587 # response time is none iff response is pending
588 CheckConstraint(
589 "(responded IS NULL AND response = 'pending') OR (responded IS NOT NULL AND response != 'pending')",
590 name="pending_has_no_responded",
591 ),
592 )
595User.pending_activeness_probe = relationship(
596 ActivenessProbe,
597 primaryjoin="and_(ActivenessProbe.user_id == User.id, ActivenessProbe.is_pending)",
598 uselist=False,
599 back_populates="user",
600)
603class StrongVerificationAttemptStatus(enum.Enum):
604 ## full data states
605 # completed, this now provides verification for a user
606 succeeded = enum.auto()
608 ## no data states
609 # in progress: waiting for the user to scan the Iris code or open the app
610 in_progress_waiting_on_user_to_open_app = enum.auto()
611 # in progress: waiting for the user to scan MRZ or NFC/chip
612 in_progress_waiting_on_user_in_app = enum.auto()
613 # in progress, waiting for backend to pull verification data
614 in_progress_waiting_on_backend = enum.auto()
615 # failed, no data
616 failed = enum.auto()
618 # duplicate, at our end, has data
619 duplicate = enum.auto()
621 ## minimal data states
622 # the data, except minimal deduplication data, was deleted
623 deleted = enum.auto()
626class PassportSex(enum.Enum):
627 """
628 We don't care about sex, we use gender on the platform. But passports apparently do.
629 """
631 male = enum.auto()
632 female = enum.auto()
633 unspecified = enum.auto()
636class StrongVerificationAttempt(Base):
637 """
638 An attempt to perform strong verification
639 """
641 __tablename__ = "strong_verification_attempts"
643 # our verification id
644 id = Column(BigInteger, primary_key=True)
646 # this is returned in the callback, and we look up the attempt via this
647 verification_attempt_token = Column(String, nullable=False, unique=True)
649 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
650 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
652 status = Column(
653 Enum(StrongVerificationAttemptStatus),
654 nullable=False,
655 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app,
656 )
658 ## full data
659 has_full_data = Column(Boolean, nullable=False, default=False)
660 # the data returned from iris, encrypted with a public key whose private key is kept offline
661 passport_encrypted_data = Column(Binary, nullable=True)
662 passport_date_of_birth = Column(Date, nullable=True)
663 passport_sex = Column(Enum(PassportSex), nullable=True)
665 ## minimal data: this will not be deleted
666 has_minimal_data = Column(Boolean, nullable=False, default=False)
667 passport_expiry_date = Column(Date, nullable=True)
668 passport_nationality = Column(String, nullable=True)
669 # last three characters of the passport number
670 passport_last_three_document_chars = Column(String, nullable=True)
672 iris_token = Column(String, nullable=False, unique=True)
673 iris_session_id = Column(BigInteger, nullable=False, unique=True)
675 passport_expiry_datetime = column_property(date_in_timezone(passport_expiry_date, "Etc/UTC"))
677 user = relationship("User")
679 @hybrid_property
680 def is_valid(self):
681 """
682 This only checks whether the attempt is a success and the passport is not expired, use `has_strong_verification` for full check
683 """
684 return (self.status == StrongVerificationAttemptStatus.succeeded) and (self.passport_expiry_datetime >= now())
686 @is_valid.expression
687 def is_valid(cls):
688 return (cls.status == StrongVerificationAttemptStatus.succeeded) & (
689 func.coalesce(cls.passport_expiry_datetime >= func.now(), False)
690 )
692 @hybrid_property
693 def is_visible(self):
694 return self.status != StrongVerificationAttemptStatus.deleted
696 @hybrid_method
697 def _raw_birthdate_match(self, user):
698 """Does not check whether the SV attempt itself is not expired"""
699 return self.passport_date_of_birth == user.birthdate
701 @hybrid_method
702 def matches_birthdate(self, user):
703 return self.is_valid & self._raw_birthdate_match(user)
705 @hybrid_method
706 def _raw_gender_match(self, user):
707 """Does not check whether the SV attempt itself is not expired"""
708 return (
709 ((user.gender == "Woman") & (self.passport_sex == PassportSex.female))
710 | ((user.gender == "Man") & (self.passport_sex == PassportSex.male))
711 | (self.passport_sex == PassportSex.unspecified)
712 | (user.has_passport_sex_gender_exception == True)
713 )
715 @hybrid_method
716 def matches_gender(self, user):
717 return self.is_valid & self._raw_gender_match(user)
719 @hybrid_method
720 def has_strong_verification(self, user):
721 return self.is_valid & self._raw_birthdate_match(user) & self._raw_gender_match(user)
723 __table_args__ = (
724 # used to look up verification status for a user
725 Index(
726 "ix_strong_verification_attempts_current",
727 user_id,
728 passport_expiry_date,
729 postgresql_where=status == StrongVerificationAttemptStatus.succeeded,
730 ),
731 # each passport can be verified only once
732 Index(
733 "ix_strong_verification_attempts_unique_succeeded",
734 passport_expiry_date,
735 passport_nationality,
736 passport_last_three_document_chars,
737 unique=True,
738 postgresql_where=(
739 (status == StrongVerificationAttemptStatus.succeeded)
740 | (status == StrongVerificationAttemptStatus.deleted)
741 ),
742 ),
743 # full data check
744 CheckConstraint(
745 "(has_full_data IS TRUE AND passport_encrypted_data IS NOT NULL AND passport_date_of_birth IS NOT NULL) OR \
746 (has_full_data IS FALSE AND passport_encrypted_data IS NULL AND passport_date_of_birth IS NULL)",
747 name="full_data_status",
748 ),
749 # minimal data check
750 CheckConstraint(
751 "(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 \
752 (has_minimal_data IS FALSE AND passport_expiry_date IS NULL AND passport_nationality IS NULL AND passport_last_three_document_chars IS NULL)",
753 name="minimal_data_status",
754 ),
755 # note on implications: p => q iff ~p OR q
756 # full data implies minimal data, has_minimal_data => has_full_data
757 CheckConstraint(
758 "(has_full_data IS FALSE) OR (has_minimal_data IS TRUE)",
759 name="full_data_implies_minimal_data",
760 ),
761 # succeeded implies full data
762 CheckConstraint(
763 "(NOT (status = 'succeeded')) OR (has_full_data IS TRUE)",
764 name="succeeded_implies_full_data",
765 ),
766 # in_progress/failed implies no_data
767 CheckConstraint(
768 "(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)",
769 name="in_progress_failed_iris_implies_no_data",
770 ),
771 # deleted or duplicate implies minimal data
772 CheckConstraint(
773 "(NOT ((status = 'deleted') OR (status = 'duplicate'))) OR (has_minimal_data IS TRUE)",
774 name="deleted_duplicate_implies_minimal_data",
775 ),
776 )
779class ModNote(Base):
780 """
781 A moderator note to a user. This could be a warning, just a note "hey, we did X", or any other similar message.
783 The user has to read and "acknowledge" the note before continuing onto the platform, and being un-jailed.
784 """
786 __tablename__ = "mod_notes"
787 id = Column(BigInteger, primary_key=True)
789 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
791 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
792 acknowledged = Column(DateTime(timezone=True), nullable=True)
794 # this is an internal ID to allow the mods to track different types of notes
795 internal_id = Column(String, nullable=False)
796 # the admin that left this note
797 creator_user_id = Column(ForeignKey("users.id"), nullable=False)
799 note_content = Column(String, nullable=False) # CommonMark without images
801 user = relationship("User", backref=backref("mod_notes", lazy="dynamic"), foreign_keys="ModNote.user_id")
803 def __repr__(self):
804 return f"ModeNote(id={self.id}, user={self.user}, created={self.created}, ack'd={self.acknowledged})"
806 @hybrid_property
807 def is_pending(self):
808 return self.acknowledged == None
810 __table_args__ = (
811 # used to look up pending notes
812 Index(
813 "ix_mod_notes_unacknowledged",
814 user_id,
815 postgresql_where=acknowledged == None,
816 ),
817 )
820class StrongVerificationCallbackEvent(Base):
821 __tablename__ = "strong_verification_callback_events"
823 id = Column(BigInteger, primary_key=True)
824 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
826 verification_attempt_id = Column(ForeignKey("strong_verification_attempts.id"), nullable=False, index=True)
828 iris_status = Column(String, nullable=False)
831class DonationType(enum.Enum):
832 one_time = enum.auto()
833 recurring = enum.auto()
836class DonationInitiation(Base):
837 """
838 Whenever someone initiaties a donation through the platform
839 """
841 __tablename__ = "donation_initiations"
842 id = Column(BigInteger, primary_key=True)
844 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
845 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
847 amount = Column(Integer, nullable=False)
848 stripe_checkout_session_id = Column(String, nullable=False)
850 donation_type = Column(Enum(DonationType), nullable=False)
851 source = Column(String, nullable=True)
853 user = relationship("User", backref="donation_initiations")
856class Invoice(Base):
857 """
858 Successful donations, both one off and recurring
860 Triggered by `payment_intent.succeeded` webhook
861 """
863 __tablename__ = "invoices"
865 id = Column(BigInteger, primary_key=True)
866 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
867 user_id = Column(ForeignKey("users.id"), nullable=False)
869 amount = Column(Float, nullable=False)
871 stripe_payment_intent_id = Column(String, nullable=False, unique=True)
872 stripe_receipt_url = Column(String, nullable=False)
874 user = relationship("User", backref="invoices")
877class LanguageFluency(enum.Enum):
878 # note that the numbering is important here, these are ordinal
879 beginner = 1
880 conversational = 2
881 fluent = 3
884class LanguageAbility(Base):
885 __tablename__ = "language_abilities"
886 __table_args__ = (
887 # Users can only have one language ability per language
888 UniqueConstraint("user_id", "language_code"),
889 )
891 id = Column(BigInteger, primary_key=True)
892 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
893 language_code = Column(ForeignKey("languages.code", deferrable=True), nullable=False)
894 fluency = Column(Enum(LanguageFluency), nullable=False)
896 user = relationship("User", backref="language_abilities")
897 language = relationship("Language")
900class RegionVisited(Base):
901 __tablename__ = "regions_visited"
902 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
904 id = Column(BigInteger, primary_key=True)
905 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
906 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
909class RegionLived(Base):
910 __tablename__ = "regions_lived"
911 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
913 id = Column(BigInteger, primary_key=True)
914 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
915 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
918class FriendStatus(enum.Enum):
919 pending = enum.auto()
920 accepted = enum.auto()
921 rejected = enum.auto()
922 cancelled = enum.auto()
925class FriendRelationship(Base):
926 """
927 Friendship relations between users
929 TODO: make this better with sqlalchemy self-referential stuff
930 TODO: constraint on only one row per user pair where accepted or pending
931 """
933 __tablename__ = "friend_relationships"
935 id = Column(BigInteger, primary_key=True)
937 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
938 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
940 status = Column(Enum(FriendStatus), nullable=False, default=FriendStatus.pending)
942 # timezones should always be UTC
943 time_sent = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
944 time_responded = Column(DateTime(timezone=True), nullable=True)
946 from_user = relationship("User", backref="friends_from", foreign_keys="FriendRelationship.from_user_id")
947 to_user = relationship("User", backref="friends_to", foreign_keys="FriendRelationship.to_user_id")
949 __table_args__ = (
950 # Ping looks up pending friend reqs, this speeds that up
951 Index(
952 "ix_friend_relationships_status_to_from",
953 status,
954 to_user_id,
955 from_user_id,
956 ),
957 )
960class ContributeOption(enum.Enum):
961 yes = enum.auto()
962 maybe = enum.auto()
963 no = enum.auto()
966class ContributorForm(Base):
967 """
968 Someone filled in the contributor form
969 """
971 __tablename__ = "contributor_forms"
973 id = Column(BigInteger, primary_key=True)
975 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
976 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
978 ideas = Column(String, nullable=True)
979 features = Column(String, nullable=True)
980 experience = Column(String, nullable=True)
981 contribute = Column(Enum(ContributeOption), nullable=True)
982 contribute_ways = Column(ARRAY(String), nullable=False)
983 expertise = Column(String, nullable=True)
985 user = relationship("User", backref="contributor_forms")
987 @hybrid_property
988 def is_filled(self):
989 """
990 Whether the form counts as having been filled
991 """
992 return (
993 (self.ideas != None)
994 | (self.features != None)
995 | (self.experience != None)
996 | (self.contribute != None)
997 | (self.contribute_ways != [])
998 | (self.expertise != None)
999 )
1001 @property
1002 def should_notify(self):
1003 """
1004 If this evaluates to true, we send an email to the recruitment team.
1006 We currently send if expertise is listed, or if they list a way to help outside of a set list
1007 """
1008 return False
1011class SignupFlow(Base):
1012 """
1013 Signup flows/incomplete users
1015 Coinciding fields have the same meaning as in User
1016 """
1018 __tablename__ = "signup_flows"
1020 id = Column(BigInteger, primary_key=True)
1022 # housekeeping
1023 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1024 flow_token = Column(String, nullable=False, unique=True)
1025 email_verified = Column(Boolean, nullable=False, default=False)
1026 email_sent = Column(Boolean, nullable=False, default=False)
1027 email_token = Column(String, nullable=True)
1028 email_token_expiry = Column(DateTime(timezone=True), nullable=True)
1030 ## Basic
1031 name = Column(String, nullable=False)
1032 # TODO: unique across both tables
1033 email = Column(String, nullable=False, unique=True)
1034 # TODO: invitation, attribution
1036 ## Account
1037 # TODO: unique across both tables
1038 username = Column(String, nullable=True, unique=True)
1039 hashed_password = Column(Binary, nullable=True)
1040 birthdate = Column(Date, nullable=True) # in the timezone of birthplace
1041 gender = Column(String, nullable=True)
1042 hosting_status = Column(Enum(HostingStatus), nullable=True)
1043 city = Column(String, nullable=True)
1044 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
1045 geom_radius = Column(Float, nullable=True)
1047 accepted_tos = Column(Integer, nullable=True)
1048 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")
1050 opt_out_of_newsletter = Column(Boolean, nullable=True)
1052 ## Feedback (now unused)
1053 filled_feedback = Column(Boolean, nullable=False, default=False)
1054 ideas = Column(String, nullable=True)
1055 features = Column(String, nullable=True)
1056 experience = Column(String, nullable=True)
1057 contribute = Column(Enum(ContributeOption), nullable=True)
1058 contribute_ways = Column(ARRAY(String), nullable=True)
1059 expertise = Column(String, nullable=True)
1061 invite_code_id = Column(ForeignKey("invite_codes.id"), nullable=True)
1063 @hybrid_property
1064 def token_is_valid(self):
1065 return (self.email_token != None) & (self.email_token_expiry >= now())
1067 @hybrid_property
1068 def account_is_filled(self):
1069 return (
1070 (self.username != None)
1071 & (self.birthdate != None)
1072 & (self.gender != None)
1073 & (self.hosting_status != None)
1074 & (self.city != None)
1075 & (self.geom != None)
1076 & (self.geom_radius != None)
1077 & (self.accepted_tos != None)
1078 & (self.opt_out_of_newsletter != None)
1079 )
1081 @hybrid_property
1082 def is_completed(self):
1083 return self.email_verified & self.account_is_filled & (self.accepted_community_guidelines == GUIDELINES_VERSION)
1086class LoginToken(Base):
1087 """
1088 A login token sent in an email to a user, allows them to sign in between the times defined by created and expiry
1089 """
1091 __tablename__ = "login_tokens"
1092 token = Column(String, primary_key=True)
1094 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1096 # timezones should always be UTC
1097 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1098 expiry = Column(DateTime(timezone=True), nullable=False)
1100 user = relationship("User", backref="login_tokens")
1102 @hybrid_property
1103 def is_valid(self):
1104 return (self.created <= now()) & (self.expiry >= now())
1106 def __repr__(self):
1107 return f"LoginToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
1110class PasswordResetToken(Base):
1111 __tablename__ = "password_reset_tokens"
1112 token = Column(String, primary_key=True)
1114 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1116 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1117 expiry = Column(DateTime(timezone=True), nullable=False)
1119 user = relationship("User", backref="password_reset_tokens")
1121 @hybrid_property
1122 def is_valid(self):
1123 return (self.created <= now()) & (self.expiry >= now())
1125 def __repr__(self):
1126 return f"PasswordResetToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
1129class AccountDeletionToken(Base):
1130 __tablename__ = "account_deletion_tokens"
1132 token = Column(String, primary_key=True)
1134 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1136 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1137 expiry = Column(DateTime(timezone=True), nullable=False)
1139 user = relationship("User", backref="account_deletion_tokens")
1141 @hybrid_property
1142 def is_valid(self):
1143 return (self.created <= now()) & (self.expiry >= now())
1145 def __repr__(self):
1146 return f"AccountDeletionToken(token={self.token}, user_id={self.user_id}, created={self.created}, expiry={self.expiry})"
1149class UserActivity(Base):
1150 """
1151 User activity: for each unique (user_id, period, ip_address, user_agent) tuple, keep track of number of api calls
1153 Used for user "last active" as well as admin stuff
1154 """
1156 __tablename__ = "user_activity"
1158 id = Column(BigInteger, primary_key=True)
1160 user_id = Column(ForeignKey("users.id"), nullable=False)
1161 # the start of a period of time, e.g. 1 hour during which we bin activeness
1162 period = Column(DateTime(timezone=True), nullable=False)
1164 # details of the browser, if available
1165 ip_address = Column(INET, nullable=True)
1166 user_agent = Column(String, nullable=True)
1168 # count of api calls made with this ip, user_agent, and period
1169 api_calls = Column(Integer, nullable=False, default=0)
1171 __table_args__ = (
1172 # helps look up this tuple quickly
1173 Index(
1174 "ix_user_activity_user_id_period_ip_address_user_agent",
1175 user_id,
1176 period,
1177 ip_address,
1178 user_agent,
1179 unique=True,
1180 ),
1181 )
1184class UserSession(Base):
1185 """
1186 API keys/session cookies for the app
1188 There are two types of sessions: long-lived, and short-lived. Long-lived are
1189 like when you choose "remember this browser": they will be valid for a long
1190 time without the user interacting with the site. Short-lived sessions on the
1191 other hand get invalidated quickly if the user does not interact with the
1192 site.
1194 Long-lived tokens are valid from `created` until `expiry`.
1196 Short-lived tokens expire after 168 hours (7 days) if they are not used.
1197 """
1199 __tablename__ = "sessions"
1200 token = Column(String, primary_key=True)
1202 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1204 # sessions are either "api keys" or "session cookies", otherwise identical
1205 # an api key is put in the authorization header (e.g. "authorization: Bearer <token>")
1206 # a session cookie is set in the "couchers-sesh" cookie (e.g. "cookie: couchers-sesh=<token>")
1207 # when a session is created, it's fixed as one or the other for security reasons
1208 # for api keys to be useful, they should be long lived and have a long expiry
1209 is_api_key = Column(Boolean, nullable=False, server_default=expression.false())
1211 # whether it's a long-lived or short-lived session
1212 long_lived = Column(Boolean, nullable=False)
1214 # the time at which the session was created
1215 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1217 # the expiry of the session: the session *cannot* be refreshed past this
1218 expiry = Column(DateTime(timezone=True), nullable=False, server_default=func.now() + text("interval '730 days'"))
1220 # the time at which the token was invalidated, allows users to delete sessions
1221 deleted = Column(DateTime(timezone=True), nullable=True, default=None)
1223 # the last time this session was used
1224 last_seen = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1226 # count of api calls made with this token/session (if we're updating last_seen, might as well update this too)
1227 api_calls = Column(Integer, nullable=False, default=0)
1229 # details of the browser, if available
1230 # these are from the request creating the session, not used for anything else
1231 ip_address = Column(String, nullable=True)
1232 user_agent = Column(String, nullable=True)
1234 user = relationship("User", backref="sessions")
1236 @hybrid_property
1237 def is_valid(self):
1238 """
1239 It must have been created and not be expired or deleted.
1241 Also, if it's a short lived token, it must have been used in the last 168 hours.
1243 TODO: this probably won't run in python (instance level), only in sql (class level)
1244 """
1245 return (
1246 (self.created <= func.now())
1247 & (self.expiry >= func.now())
1248 & (self.deleted == None)
1249 & (self.long_lived | (func.now() - self.last_seen < text("interval '168 hours'")))
1250 )
1252 __table_args__ = (
1253 Index(
1254 "ix_sessions_by_token",
1255 "token",
1256 postgresql_using="hash",
1257 ),
1258 )
1261class Conversation(Base):
1262 """
1263 Conversation brings together the different types of message/conversation types
1264 """
1266 __tablename__ = "conversations"
1268 id = Column(BigInteger, primary_key=True)
1269 # timezone should always be UTC
1270 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1272 def __repr__(self):
1273 return f"Conversation(id={self.id}, created={self.created})"
1276class GroupChat(Base):
1277 """
1278 Group chat
1279 """
1281 __tablename__ = "group_chats"
1283 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1285 title = Column(String, nullable=True)
1286 only_admins_invite = Column(Boolean, nullable=False, default=True)
1287 creator_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1288 is_dm = Column(Boolean, nullable=False)
1290 conversation = relationship("Conversation", backref="group_chat")
1291 creator = relationship("User", backref="created_group_chats")
1293 def __repr__(self):
1294 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})"
1297class GroupChatRole(enum.Enum):
1298 admin = enum.auto()
1299 participant = enum.auto()
1302class GroupChatSubscription(Base):
1303 """
1304 The recipient of a thread and information about when they joined/left/etc.
1305 """
1307 __tablename__ = "group_chat_subscriptions"
1308 id = Column(BigInteger, primary_key=True)
1310 # TODO: DB constraint on only one user+group_chat combo at a given time
1311 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1312 group_chat_id = Column(ForeignKey("group_chats.id"), nullable=False, index=True)
1314 # timezones should always be UTC
1315 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1316 left = Column(DateTime(timezone=True), nullable=True)
1318 role = Column(Enum(GroupChatRole), nullable=False)
1320 last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1322 # when this chat is muted until, DATETIME_INFINITY for "forever"
1323 muted_until = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_MINUS_INFINITY.isoformat())
1325 user = relationship("User", backref="group_chat_subscriptions")
1326 group_chat = relationship("GroupChat", backref=backref("subscriptions", lazy="dynamic"))
1328 def muted_display(self):
1329 """
1330 Returns (muted, muted_until) display values:
1331 1. If not muted, returns (False, None)
1332 2. If muted forever, returns (True, None)
1333 3. If muted until a given datetime returns (True, dt)
1334 """
1335 if self.muted_until < now():
1336 return (False, None)
1337 elif self.muted_until == DATETIME_INFINITY:
1338 return (True, None)
1339 else:
1340 return (True, self.muted_until)
1342 @hybrid_property
1343 def is_muted(self):
1344 return self.muted_until > func.now()
1346 def __repr__(self):
1347 return f"GroupChatSubscription(id={self.id}, user={self.user}, joined={self.joined}, left={self.left}, role={self.role}, group_chat={self.group_chat})"
1350class InviteCode(Base):
1351 __tablename__ = "invite_codes"
1353 id = Column(String, primary_key=True)
1354 creator_user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
1355 created = Column(DateTime(timezone=True), nullable=False, default=func.now())
1356 disabled = Column(DateTime(timezone=True), nullable=True)
1358 creator = relationship("User", foreign_keys=[creator_user_id])
1361class MessageType(enum.Enum):
1362 text = enum.auto()
1363 # e.g.
1364 # image =
1365 # emoji =
1366 # ...
1367 chat_created = enum.auto()
1368 chat_edited = enum.auto()
1369 user_invited = enum.auto()
1370 user_left = enum.auto()
1371 user_made_admin = enum.auto()
1372 user_removed_admin = enum.auto() # RemoveGroupChatAdmin: remove admin permission from a user in group chat
1373 host_request_status_changed = enum.auto()
1374 user_removed = enum.auto() # user is removed from group chat by amdin RemoveGroupChatUser
1377class HostRequestStatus(enum.Enum):
1378 pending = enum.auto()
1379 accepted = enum.auto()
1380 rejected = enum.auto()
1381 confirmed = enum.auto()
1382 cancelled = enum.auto()
1385class Message(Base):
1386 """
1387 A message.
1389 If message_type = text, then the message is a normal text message, otherwise, it's a special control message.
1390 """
1392 __tablename__ = "messages"
1394 id = Column(BigInteger, primary_key=True)
1396 # which conversation the message belongs in
1397 conversation_id = Column(ForeignKey("conversations.id"), nullable=False, index=True)
1399 # the user that sent the message/command
1400 author_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1402 # the message type, "text" is a text message, otherwise a "control message"
1403 message_type = Column(Enum(MessageType), nullable=False)
1405 # the target if a control message and requires target, e.g. if inviting a user, the user invited is the target
1406 target_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1408 # time sent, timezone should always be UTC
1409 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1411 # the plain-text message text if not control
1412 text = Column(String, nullable=True)
1414 # the new host request status if the message type is host_request_status_changed
1415 host_request_status_target = Column(Enum(HostRequestStatus), nullable=True)
1417 conversation = relationship("Conversation", backref="messages", order_by="Message.time.desc()")
1418 author = relationship("User", foreign_keys="Message.author_id")
1419 target = relationship("User", foreign_keys="Message.target_id")
1421 @property
1422 def is_normal_message(self):
1423 """
1424 There's only one normal type atm, text
1425 """
1426 return self.message_type == MessageType.text
1428 def __repr__(self):
1429 return f"Message(id={self.id}, time={self.time}, text={self.text}, author={self.author}, conversation={self.conversation})"
1432class ContentReport(Base):
1433 """
1434 A piece of content reported to admins
1435 """
1437 __tablename__ = "content_reports"
1439 id = Column(BigInteger, primary_key=True)
1441 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1443 # the user who reported or flagged the content
1444 reporting_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1446 # reason, e.g. spam, inappropriate, etc
1447 reason = Column(String, nullable=False)
1448 # a short description
1449 description = Column(String, nullable=False)
1451 # a reference to the content, see //docs/content_ref.md
1452 content_ref = Column(String, nullable=False)
1453 # the author of the content (e.g. the user who wrote the comment itself)
1454 author_user_id = Column(ForeignKey("users.id"), nullable=False)
1456 # details of the browser, if available
1457 user_agent = Column(String, nullable=False)
1458 # the URL the user was on when reporting the content
1459 page = Column(String, nullable=False)
1461 # see comments above for reporting vs author
1462 reporting_user = relationship("User", foreign_keys="ContentReport.reporting_user_id")
1463 author_user = relationship("User", foreign_keys="ContentReport.author_user_id")
1466class Email(Base):
1467 """
1468 Table of all dispatched emails for debugging purposes, etc.
1469 """
1471 __tablename__ = "emails"
1473 id = Column(String, primary_key=True)
1475 # timezone should always be UTC
1476 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1478 sender_name = Column(String, nullable=False)
1479 sender_email = Column(String, nullable=False)
1481 recipient = Column(String, nullable=False)
1482 subject = Column(String, nullable=False)
1484 plain = Column(String, nullable=False)
1485 html = Column(String, nullable=False)
1487 list_unsubscribe_header = Column(String, nullable=True)
1488 source_data = Column(String, nullable=True)
1491class SMS(Base):
1492 """
1493 Table of all sent SMSs for debugging purposes, etc.
1494 """
1496 __tablename__ = "smss"
1498 id = Column(BigInteger, primary_key=True)
1500 # timezone should always be UTC
1501 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1502 # AWS message id
1503 message_id = Column(String, nullable=False)
1505 # the SMS sender ID sent to AWS, name that the SMS appears to come from
1506 sms_sender_id = Column(String, nullable=False)
1507 number = Column(String, nullable=False)
1508 message = Column(String, nullable=False)
1511class HostRequest(Base):
1512 """
1513 A request to stay with a host
1514 """
1516 __tablename__ = "host_requests"
1518 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1519 surfer_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1520 host_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1522 hosting_city = Column(String, nullable=False)
1523 hosting_location = Column(Geometry("POINT", srid=4326), nullable=False)
1524 hosting_radius = Column(Float, nullable=False)
1526 # TODO: proper timezone handling
1527 timezone = "Etc/UTC"
1529 # dates in the timezone above
1530 from_date = Column(Date, nullable=False)
1531 to_date = Column(Date, nullable=False)
1533 # timezone aware start and end times of the request, can be compared to now()
1534 start_time = column_property(date_in_timezone(from_date, timezone))
1535 end_time = column_property(date_in_timezone(to_date, timezone) + text("interval '1 days'"))
1536 start_time_to_write_reference = column_property(date_in_timezone(to_date, timezone))
1537 # notice 1 day for midnight at the *end of the day*, then 14 days to write a ref
1538 end_time_to_write_reference = column_property(date_in_timezone(to_date, timezone) + text("interval '15 days'"))
1540 status = Column(Enum(HostRequestStatus), nullable=False)
1541 is_host_archived = Column(Boolean, nullable=False, default=False, server_default=expression.false())
1542 is_surfer_archived = Column(Boolean, nullable=False, default=False, server_default=expression.false())
1544 host_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1545 surfer_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1547 # number of reference reminders sent out
1548 host_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1549 surfer_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1550 host_sent_request_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1551 last_sent_request_reminder_time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1553 # reason why the host/surfer marked that they didn't meet up
1554 # if null then they haven't marked it such
1555 host_reason_didnt_meetup = Column(String, nullable=True)
1556 surfer_reason_didnt_meetup = Column(String, nullable=True)
1558 surfer = relationship("User", backref="host_requests_sent", foreign_keys="HostRequest.surfer_user_id")
1559 host = relationship("User", backref="host_requests_received", foreign_keys="HostRequest.host_user_id")
1560 conversation = relationship("Conversation")
1562 __table_args__ = (
1563 # allows fast lookup as to whether they didn't meet up
1564 Index(
1565 "ix_host_requests_host_didnt_meetup",
1566 host_reason_didnt_meetup != None,
1567 ),
1568 Index(
1569 "ix_host_requests_surfer_didnt_meetup",
1570 surfer_reason_didnt_meetup != None,
1571 ),
1572 # Used for figuring out who needs a reminder to respond
1573 Index(
1574 "ix_host_requests_status_reminder_counts",
1575 status,
1576 host_sent_request_reminders,
1577 last_sent_request_reminder_time,
1578 from_date,
1579 ),
1580 )
1582 @hybrid_property
1583 def can_write_reference(self):
1584 return (
1585 ((self.status == HostRequestStatus.confirmed) | (self.status == HostRequestStatus.accepted))
1586 & (now() >= self.start_time_to_write_reference)
1587 & (now() <= self.end_time_to_write_reference)
1588 )
1590 @can_write_reference.expression
1591 def can_write_reference(cls):
1592 return (
1593 ((cls.status == HostRequestStatus.confirmed) | (cls.status == HostRequestStatus.accepted))
1594 & (func.now() >= cls.start_time_to_write_reference)
1595 & (func.now() <= cls.end_time_to_write_reference)
1596 )
1598 def __repr__(self):
1599 return f"HostRequest(id={self.conversation_id}, surfer_user_id={self.surfer_user_id}, host_user_id={self.host_user_id}...)"
1602class HostRequestQuality(enum.Enum):
1603 high_quality = enum.auto()
1604 okay_quality = enum.auto()
1605 low_quality = enum.auto()
1608class HostRequestFeedback(Base):
1609 """
1610 Private feedback from host about a host request
1611 """
1613 __tablename__ = "host_request_feedbacks"
1615 id = Column(BigInteger, primary_key=True)
1616 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1617 host_request_id = Column(ForeignKey("host_requests.id"), nullable=False)
1619 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1620 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1622 request_quality = Column(Enum(HostRequestQuality), nullable=True)
1623 decline_reason = Column(String, nullable=True) # plain text
1625 host_request = relationship("HostRequest")
1627 __table_args__ = (
1628 # Each user can leave at most one friend reference to another user
1629 Index(
1630 "ix_unique_host_req_feedback",
1631 from_user_id,
1632 to_user_id,
1633 host_request_id,
1634 unique=True,
1635 ),
1636 )
1639class ReferenceType(enum.Enum):
1640 friend = enum.auto()
1641 surfed = enum.auto() # The "from" user surfed with the "to" user
1642 hosted = enum.auto() # The "from" user hosted the "to" user
1645class Reference(Base):
1646 """
1647 Reference from one user to another
1648 """
1650 __tablename__ = "references"
1652 id = Column(BigInteger, primary_key=True)
1653 # timezone should always be UTC
1654 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1656 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1657 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1659 reference_type = Column(Enum(ReferenceType), nullable=False)
1661 host_request_id = Column(ForeignKey("host_requests.id"), nullable=True)
1663 text = Column(String, nullable=False) # plain text
1664 # text that's only visible to mods
1665 private_text = Column(String, nullable=True) # plain text
1667 rating = Column(Float, nullable=False)
1668 was_appropriate = Column(Boolean, nullable=False)
1670 is_deleted = Column(Boolean, nullable=False, default=False, server_default=expression.false())
1672 from_user = relationship("User", backref="references_from", foreign_keys="Reference.from_user_id")
1673 to_user = relationship("User", backref="references_to", foreign_keys="Reference.to_user_id")
1675 host_request = relationship("HostRequest", backref="references")
1677 __table_args__ = (
1678 # Rating must be between 0 and 1, inclusive
1679 CheckConstraint(
1680 "rating BETWEEN 0 AND 1",
1681 name="rating_between_0_and_1",
1682 ),
1683 # Has host_request_id or it's a friend reference
1684 CheckConstraint(
1685 "(host_request_id IS NOT NULL) <> (reference_type = 'friend')",
1686 name="host_request_id_xor_friend_reference",
1687 ),
1688 # Each user can leave at most one friend reference to another user
1689 Index(
1690 "ix_references_unique_friend_reference",
1691 from_user_id,
1692 to_user_id,
1693 reference_type,
1694 unique=True,
1695 postgresql_where=(reference_type == ReferenceType.friend),
1696 ),
1697 # Each user can leave at most one reference to another user for each stay
1698 Index(
1699 "ix_references_unique_per_host_request",
1700 from_user_id,
1701 to_user_id,
1702 host_request_id,
1703 unique=True,
1704 postgresql_where=(host_request_id != None),
1705 ),
1706 )
1708 @property
1709 def should_report(self):
1710 """
1711 If this evaluates to true, we send a report to the moderation team.
1712 """
1713 return self.rating <= 0.4 or not self.was_appropriate or self.private_text
1716class InitiatedUpload(Base):
1717 """
1718 Started downloads, not necessarily complete yet.
1719 """
1721 __tablename__ = "initiated_uploads"
1723 key = Column(String, primary_key=True)
1725 # timezones should always be UTC
1726 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1727 expiry = Column(DateTime(timezone=True), nullable=False)
1729 initiator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1731 initiator_user = relationship("User")
1733 @hybrid_property
1734 def is_valid(self):
1735 return (self.created <= func.now()) & (self.expiry >= func.now())
1738class Upload(Base):
1739 """
1740 Completed uploads.
1741 """
1743 __tablename__ = "uploads"
1744 key = Column(String, primary_key=True)
1746 filename = Column(String, nullable=False)
1747 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1748 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1750 # photo credit, etc
1751 credit = Column(String, nullable=True)
1753 creator_user = relationship("User", backref="uploads", foreign_keys="Upload.creator_user_id")
1755 def _url(self, size):
1756 return urls.media_url(filename=self.filename, size=size)
1758 @property
1759 def thumbnail_url(self):
1760 return self._url("thumbnail")
1762 @property
1763 def full_url(self):
1764 return self._url("full")
1767communities_seq = Sequence("communities_seq")
1770class Node(Base):
1771 """
1772 Node, i.e. geographical subdivision of the world
1774 Administered by the official cluster
1775 """
1777 __tablename__ = "nodes"
1779 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1781 # name and description come from official cluster
1782 parent_node_id = Column(ForeignKey("nodes.id"), nullable=True, index=True)
1783 geom = deferred(Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False))
1784 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1786 parent_node = relationship("Node", backref="child_nodes", remote_side="Node.id")
1788 contained_users = relationship(
1789 "User",
1790 primaryjoin="func.ST_Contains(foreign(Node.geom), User.geom).as_comparison(1, 2)",
1791 viewonly=True,
1792 uselist=True,
1793 )
1795 contained_user_ids = association_proxy("contained_users", "id")
1798class Cluster(Base):
1799 """
1800 Cluster, administered grouping of content
1801 """
1803 __tablename__ = "clusters"
1805 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1806 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1807 name = Column(String, nullable=False)
1808 # short description
1809 description = Column(String, nullable=False)
1810 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1812 is_official_cluster = Column(Boolean, nullable=False, default=False)
1814 discussions_enabled = Column(Boolean, nullable=False, default=True, server_default=expression.true())
1815 events_enabled = Column(Boolean, nullable=False, default=True, server_default=expression.true())
1817 slug = column_property(func.slugify(name))
1819 official_cluster_for_node = relationship(
1820 "Node",
1821 primaryjoin="and_(Cluster.parent_node_id == Node.id, Cluster.is_official_cluster)",
1822 backref=backref("official_cluster", uselist=False),
1823 uselist=False,
1824 viewonly=True,
1825 )
1827 parent_node = relationship(
1828 "Node", backref="child_clusters", remote_side="Node.id", foreign_keys="Cluster.parent_node_id"
1829 )
1831 nodes = relationship("Cluster", backref="clusters", secondary="node_cluster_associations", viewonly=True)
1832 # all pages
1833 pages = relationship(
1834 "Page", backref="clusters", secondary="cluster_page_associations", lazy="dynamic", viewonly=True
1835 )
1836 events = relationship("Event", backref="clusters", secondary="cluster_event_associations", viewonly=True)
1837 discussions = relationship(
1838 "Discussion", backref="clusters", secondary="cluster_discussion_associations", viewonly=True
1839 )
1841 # includes also admins
1842 members = relationship(
1843 "User",
1844 lazy="dynamic",
1845 backref="cluster_memberships",
1846 secondary="cluster_subscriptions",
1847 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1848 secondaryjoin="User.id == ClusterSubscription.user_id",
1849 viewonly=True,
1850 )
1852 admins = relationship(
1853 "User",
1854 lazy="dynamic",
1855 backref="cluster_adminships",
1856 secondary="cluster_subscriptions",
1857 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1858 secondaryjoin="and_(User.id == ClusterSubscription.user_id, ClusterSubscription.role == 'admin')",
1859 viewonly=True,
1860 )
1862 main_page = relationship(
1863 "Page",
1864 primaryjoin="and_(Cluster.id == Page.owner_cluster_id, Page.type == 'main_page')",
1865 viewonly=True,
1866 uselist=False,
1867 )
1869 @property
1870 def is_leaf(self) -> bool:
1871 """Whether the cluster is a leaf node in the cluster hierarchy."""
1872 return len(self.parent_node.child_nodes) == 0
1874 __table_args__ = (
1875 # Each node can have at most one official cluster
1876 Index(
1877 "ix_clusters_owner_parent_node_id_is_official_cluster",
1878 parent_node_id,
1879 is_official_cluster,
1880 unique=True,
1881 postgresql_where=is_official_cluster,
1882 ),
1883 # trigram index on unaccented name
1884 # note that the function `unaccent` is not immutable so cannot be used in an index, that's why we wrap it
1885 Index(
1886 "idx_clusters_name_unaccented_trgm",
1887 text("immutable_unaccent(name) gin_trgm_ops"),
1888 postgresql_using="gin",
1889 ),
1890 )
1893class NodeClusterAssociation(Base):
1894 """
1895 NodeClusterAssociation, grouping of nodes
1896 """
1898 __tablename__ = "node_cluster_associations"
1899 __table_args__ = (UniqueConstraint("node_id", "cluster_id"),)
1901 id = Column(BigInteger, primary_key=True)
1903 node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1904 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1906 node = relationship("Node", backref="node_cluster_associations")
1907 cluster = relationship("Cluster", backref="node_cluster_associations")
1910class ClusterRole(enum.Enum):
1911 member = enum.auto()
1912 admin = enum.auto()
1915class ClusterSubscription(Base):
1916 """
1917 ClusterSubscription of a user
1918 """
1920 __tablename__ = "cluster_subscriptions"
1922 id = Column(BigInteger, primary_key=True)
1924 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1925 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1926 role = Column(Enum(ClusterRole), nullable=False)
1928 user = relationship("User", backref="cluster_subscriptions")
1929 cluster = relationship("Cluster", backref="cluster_subscriptions")
1931 __table_args__ = (
1932 UniqueConstraint("user_id", "cluster_id"),
1933 Index(
1934 "ix_cluster_subscriptions_members",
1935 cluster_id,
1936 user_id,
1937 ),
1938 # For fast lookup of nodes this user is an admin of
1939 Index(
1940 "ix_cluster_subscriptions_admins",
1941 user_id,
1942 cluster_id,
1943 postgresql_where=(role == ClusterRole.admin),
1944 ),
1945 )
1948class ClusterPageAssociation(Base):
1949 """
1950 pages related to clusters
1951 """
1953 __tablename__ = "cluster_page_associations"
1954 __table_args__ = (UniqueConstraint("page_id", "cluster_id"),)
1956 id = Column(BigInteger, primary_key=True)
1958 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1959 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1961 page = relationship("Page", backref="cluster_page_associations")
1962 cluster = relationship("Cluster", backref="cluster_page_associations")
1965class PageType(enum.Enum):
1966 main_page = enum.auto()
1967 place = enum.auto()
1968 guide = enum.auto()
1971class Page(Base):
1972 """
1973 similar to a wiki page about a community, POI or guide
1974 """
1976 __tablename__ = "pages"
1978 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1980 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1981 type = Column(Enum(PageType), nullable=False)
1982 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1983 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1984 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1986 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
1988 parent_node = relationship("Node", backref="child_pages", remote_side="Node.id", foreign_keys="Page.parent_node_id")
1990 thread = relationship("Thread", backref="page", uselist=False)
1991 creator_user = relationship("User", backref="created_pages", foreign_keys="Page.creator_user_id")
1992 owner_user = relationship("User", backref="owned_pages", foreign_keys="Page.owner_user_id")
1993 owner_cluster = relationship(
1994 "Cluster", backref=backref("owned_pages", lazy="dynamic"), uselist=False, foreign_keys="Page.owner_cluster_id"
1995 )
1997 editors = relationship("User", secondary="page_versions", viewonly=True)
1999 __table_args__ = (
2000 # Only one of owner_user and owner_cluster should be set
2001 CheckConstraint(
2002 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
2003 name="one_owner",
2004 ),
2005 # Only clusters can own main pages
2006 CheckConstraint(
2007 "NOT (owner_cluster_id IS NULL AND type = 'main_page')",
2008 name="main_page_owned_by_cluster",
2009 ),
2010 # Each cluster can have at most one main page
2011 Index(
2012 "ix_pages_owner_cluster_id_type",
2013 owner_cluster_id,
2014 type,
2015 unique=True,
2016 postgresql_where=(type == PageType.main_page),
2017 ),
2018 )
2020 def __repr__(self):
2021 return f"Page({self.id=})"
2024class PageVersion(Base):
2025 """
2026 version of page content
2027 """
2029 __tablename__ = "page_versions"
2031 id = Column(BigInteger, primary_key=True)
2033 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
2034 editor_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2035 title = Column(String, nullable=False)
2036 content = Column(String, nullable=False) # CommonMark without images
2037 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
2038 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
2039 # the human-readable address
2040 address = Column(String, nullable=True)
2041 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2043 slug = column_property(func.slugify(title))
2045 page = relationship("Page", backref="versions", order_by="PageVersion.id")
2046 editor_user = relationship("User", backref="edited_pages")
2047 photo = relationship("Upload")
2049 __table_args__ = (
2050 # Geom and address must either both be null or both be set
2051 CheckConstraint(
2052 "(geom IS NULL) = (address IS NULL)",
2053 name="geom_iff_address",
2054 ),
2055 )
2057 @property
2058 def coordinates(self):
2059 # returns (lat, lng) or None
2060 return get_coordinates(self.geom)
2062 def __repr__(self):
2063 return f"PageVersion({self.id=}, {self.page_id=})"
2066class ClusterEventAssociation(Base):
2067 """
2068 events related to clusters
2069 """
2071 __tablename__ = "cluster_event_associations"
2072 __table_args__ = (UniqueConstraint("event_id", "cluster_id"),)
2074 id = Column(BigInteger, primary_key=True)
2076 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
2077 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2079 event = relationship("Event", backref="cluster_event_associations")
2080 cluster = relationship("Cluster", backref="cluster_event_associations")
2083class Event(Base):
2084 """
2085 An event is compose of two parts:
2087 * An event template (Event)
2088 * An occurrence (EventOccurrence)
2090 One-off events will have one of each; repeating events will have one Event,
2091 multiple EventOccurrences, one for each time the event happens.
2092 """
2094 __tablename__ = "events"
2096 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
2097 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
2099 title = Column(String, nullable=False)
2101 slug = column_property(func.slugify(title))
2103 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2104 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2105 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
2106 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
2107 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
2109 parent_node = relationship(
2110 "Node", backref="child_events", remote_side="Node.id", foreign_keys="Event.parent_node_id"
2111 )
2112 thread = relationship("Thread", backref="event", uselist=False)
2113 subscribers = relationship(
2114 "User", backref="subscribed_events", secondary="event_subscriptions", lazy="dynamic", viewonly=True
2115 )
2116 organizers = relationship(
2117 "User", backref="organized_events", secondary="event_organizers", lazy="dynamic", viewonly=True
2118 )
2119 creator_user = relationship("User", backref="created_events", foreign_keys="Event.creator_user_id")
2120 owner_user = relationship("User", backref="owned_events", foreign_keys="Event.owner_user_id")
2121 owner_cluster = relationship(
2122 "Cluster",
2123 backref=backref("owned_events", lazy="dynamic"),
2124 uselist=False,
2125 foreign_keys="Event.owner_cluster_id",
2126 )
2128 __table_args__ = (
2129 # Only one of owner_user and owner_cluster should be set
2130 CheckConstraint(
2131 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
2132 name="one_owner",
2133 ),
2134 )
2137class EventOccurrence(Base):
2138 __tablename__ = "event_occurrences"
2140 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
2141 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
2143 # the user that created this particular occurrence of a repeating event (same as event.creator_user_id if single event)
2144 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2145 content = Column(String, nullable=False) # CommonMark without images
2146 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
2148 is_cancelled = Column(Boolean, nullable=False, default=False, server_default=expression.false())
2149 is_deleted = Column(Boolean, nullable=False, default=False, server_default=expression.false())
2151 # a null geom is an online-only event
2152 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
2153 # physical address, iff geom is not null
2154 address = Column(String, nullable=True)
2155 # videoconferencing link, etc, must be specified if no geom, otherwise optional
2156 link = Column(String, nullable=True)
2158 timezone = "Etc/UTC"
2160 # time during which the event takes place; this is a range type (instead of separate start+end times) which
2161 # simplifies database constraints, etc
2162 during = Column(TSTZRANGE, nullable=False)
2164 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2165 last_edited = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2167 creator_user = relationship(
2168 "User", backref="created_event_occurrences", foreign_keys="EventOccurrence.creator_user_id"
2169 )
2170 event = relationship(
2171 "Event",
2172 backref=backref("occurrences", lazy="dynamic"),
2173 remote_side="Event.id",
2174 foreign_keys="EventOccurrence.event_id",
2175 )
2177 photo = relationship("Upload")
2179 __table_args__ = (
2180 # Geom and address go together
2181 CheckConstraint(
2182 # geom and address are either both null or neither of them are null
2183 "(geom IS NULL) = (address IS NULL)",
2184 name="geom_iff_address",
2185 ),
2186 # Online-only events need a link, note that online events may also have a link
2187 CheckConstraint(
2188 # exactly oen of geom or link is non-null
2189 "(geom IS NULL) <> (link IS NULL)",
2190 name="link_or_geom",
2191 ),
2192 # Can't have overlapping occurrences in the same Event
2193 ExcludeConstraint(("event_id", "="), ("during", "&&"), name="event_occurrences_event_id_during_excl"),
2194 )
2196 @property
2197 def coordinates(self):
2198 # returns (lat, lng) or None
2199 return get_coordinates(self.geom)
2201 @hybrid_property
2202 def start_time(self):
2203 return self.during.lower
2205 @start_time.expression
2206 def start_time(cls):
2207 return func.lower(cls.during)
2209 @hybrid_property
2210 def end_time(self):
2211 return self.during.upper
2213 @end_time.expression
2214 def end_time(cls):
2215 return func.upper(cls.during)
2218class EventSubscription(Base):
2219 """
2220 Users' subscriptions to events
2221 """
2223 __tablename__ = "event_subscriptions"
2224 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
2226 id = Column(BigInteger, primary_key=True)
2228 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2229 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
2230 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2232 user = relationship("User")
2233 event = relationship("Event")
2236class EventOrganizer(Base):
2237 """
2238 Organizers for events
2239 """
2241 __tablename__ = "event_organizers"
2242 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
2244 id = Column(BigInteger, primary_key=True)
2246 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2247 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
2248 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2250 user = relationship("User")
2251 event = relationship("Event")
2254class AttendeeStatus(enum.Enum):
2255 going = enum.auto()
2256 maybe = enum.auto()
2259class EventOccurrenceAttendee(Base):
2260 """
2261 Attendees for events
2262 """
2264 __tablename__ = "event_occurrence_attendees"
2265 __table_args__ = (UniqueConstraint("occurrence_id", "user_id"),)
2267 id = Column(BigInteger, primary_key=True)
2269 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2270 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
2271 responded = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2272 attendee_status = Column(Enum(AttendeeStatus), nullable=False)
2274 user = relationship("User")
2275 occurrence = relationship("EventOccurrence", backref=backref("attendances", lazy="dynamic"))
2277 reminder_sent = Column(Boolean, nullable=False, default=False, server_default=expression.false())
2280class EventCommunityInviteRequest(Base):
2281 """
2282 Requests to send out invitation notifications/emails to the community for a given event occurrence
2283 """
2285 __tablename__ = "event_community_invite_requests"
2287 id = Column(BigInteger, primary_key=True)
2289 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
2290 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2292 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2294 decided = Column(DateTime(timezone=True), nullable=True)
2295 decided_by_user_id = Column(ForeignKey("users.id"), nullable=True)
2296 approved = Column(Boolean, nullable=True)
2298 occurrence = relationship("EventOccurrence", backref=backref("community_invite_requests", lazy="dynamic"))
2299 user = relationship("User", foreign_keys="EventCommunityInviteRequest.user_id")
2301 __table_args__ = (
2302 # each user can only request once
2303 UniqueConstraint("occurrence_id", "user_id"),
2304 # each event can only have one notification sent out
2305 Index(
2306 "ix_event_community_invite_requests_unique",
2307 occurrence_id,
2308 unique=True,
2309 postgresql_where=and_(approved.is_not(None), approved == True),
2310 ),
2311 # decided and approved ought to be null simultaneously
2312 CheckConstraint(
2313 "((decided IS NULL) AND (decided_by_user_id IS NULL) AND (approved IS NULL)) OR \
2314 ((decided IS NOT NULL) AND (decided_by_user_id IS NOT NULL) AND (approved IS NOT NULL))",
2315 name="decided_approved",
2316 ),
2317 )
2320class ClusterDiscussionAssociation(Base):
2321 """
2322 discussions related to clusters
2323 """
2325 __tablename__ = "cluster_discussion_associations"
2326 __table_args__ = (UniqueConstraint("discussion_id", "cluster_id"),)
2328 id = Column(BigInteger, primary_key=True)
2330 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2331 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2333 discussion = relationship("Discussion", backref="cluster_discussion_associations")
2334 cluster = relationship("Cluster", backref="cluster_discussion_associations")
2337class Discussion(Base):
2338 """
2339 forum board
2340 """
2342 __tablename__ = "discussions"
2344 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
2346 title = Column(String, nullable=False)
2347 content = Column(String, nullable=False)
2348 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
2349 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2351 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2352 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2354 slug = column_property(func.slugify(title))
2356 thread = relationship("Thread", backref="discussion", uselist=False)
2358 subscribers = relationship("User", backref="discussions", secondary="discussion_subscriptions", viewonly=True)
2360 creator_user = relationship("User", backref="created_discussions", foreign_keys="Discussion.creator_user_id")
2361 owner_cluster = relationship("Cluster", backref=backref("owned_discussions", lazy="dynamic"), uselist=False)
2364class DiscussionSubscription(Base):
2365 """
2366 users subscriptions to discussions
2367 """
2369 __tablename__ = "discussion_subscriptions"
2370 __table_args__ = (UniqueConstraint("discussion_id", "user_id"),)
2372 id = Column(BigInteger, primary_key=True)
2374 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2375 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2376 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2377 left = Column(DateTime(timezone=True), nullable=True)
2379 user = relationship("User", backref="discussion_subscriptions")
2380 discussion = relationship("Discussion", backref="discussion_subscriptions")
2383class Thread(Base):
2384 """
2385 Thread
2386 """
2388 __tablename__ = "threads"
2390 id = Column(BigInteger, primary_key=True)
2392 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2393 deleted = Column(DateTime(timezone=True), nullable=True)
2396class Comment(Base):
2397 """
2398 Comment
2399 """
2401 __tablename__ = "comments"
2403 id = Column(BigInteger, primary_key=True)
2405 thread_id = Column(ForeignKey("threads.id"), nullable=False, index=True)
2406 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2407 content = Column(String, nullable=False) # CommonMark without images
2408 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2409 deleted = Column(DateTime(timezone=True), nullable=True)
2411 thread = relationship("Thread", backref="comments")
2414class Reply(Base):
2415 """
2416 Reply
2417 """
2419 __tablename__ = "replies"
2421 id = Column(BigInteger, primary_key=True)
2423 comment_id = Column(ForeignKey("comments.id"), nullable=False, index=True)
2424 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2425 content = Column(String, nullable=False) # CommonMark without images
2426 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2427 deleted = Column(DateTime(timezone=True), nullable=True)
2429 comment = relationship("Comment", backref="replies")
2432class BackgroundJobState(enum.Enum):
2433 # job is fresh, waiting to be picked off the queue
2434 pending = enum.auto()
2435 # job complete
2436 completed = enum.auto()
2437 # error occured, will be retried
2438 error = enum.auto()
2439 # failed too many times, not retrying anymore
2440 failed = enum.auto()
2443class BackgroundJob(Base):
2444 """
2445 This table implements a queue of background jobs.
2446 """
2448 __tablename__ = "background_jobs"
2450 id = Column(BigInteger, primary_key=True)
2452 # used to discern which function should be triggered to service it
2453 job_type = Column(String, nullable=False)
2454 state = Column(Enum(BackgroundJobState), nullable=False, default=BackgroundJobState.pending)
2456 # time queued
2457 queued = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2459 # time at which we may next attempt it, for implementing exponential backoff
2460 next_attempt_after = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2462 # used to count number of retries for failed jobs
2463 try_count = Column(Integer, nullable=False, default=0)
2465 max_tries = Column(Integer, nullable=False, default=5)
2467 # higher is more important
2468 priority = Column(Integer, nullable=False, server_default=text("10"))
2470 # protobuf encoded job payload
2471 payload = Column(Binary, nullable=False)
2473 # if the job failed, we write that info here
2474 failure_info = Column(String, nullable=True)
2476 __table_args__ = (
2477 # used in looking up background jobs to attempt
2478 # create index on background_jobs(priority desc, next_attempt_after, (max_tries - try_count)) where state = 'pending' OR state = 'error';
2479 Index(
2480 "ix_background_jobs_lookup",
2481 priority.desc(),
2482 next_attempt_after,
2483 (max_tries - try_count),
2484 postgresql_where=((state == BackgroundJobState.pending) | (state == BackgroundJobState.error)),
2485 ),
2486 )
2488 @hybrid_property
2489 def ready_for_retry(self):
2490 return (
2491 (self.next_attempt_after <= func.now())
2492 & (self.try_count < self.max_tries)
2493 & ((self.state == BackgroundJobState.pending) | (self.state == BackgroundJobState.error))
2494 )
2496 def __repr__(self):
2497 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})"
2500class NotificationDeliveryType(enum.Enum):
2501 # send push notification to mobile/web
2502 push = enum.auto()
2503 # send individual email immediately
2504 email = enum.auto()
2505 # send in digest
2506 digest = enum.auto()
2509dt = NotificationDeliveryType
2510nd = notification_data_pb2
2512dt_sec = [dt.email, dt.push]
2513dt_all = [dt.email, dt.push, dt.digest]
2516class NotificationTopicAction(enum.Enum):
2517 def __init__(self, topic_action, defaults, user_editable, data_type):
2518 self.topic, self.action = topic_action.split(":")
2519 self.defaults = defaults
2520 # for now user editable == not a security notification
2521 self.user_editable = user_editable
2523 self.data_type = data_type
2525 def unpack(self):
2526 return self.topic, self.action
2528 @property
2529 def display(self):
2530 return f"{self.topic}:{self.action}"
2532 def __str__(self):
2533 return self.display
2535 # topic, action, default delivery types
2536 friend_request__create = ("friend_request:create", dt_all, True, nd.FriendRequestCreate)
2537 friend_request__accept = ("friend_request:accept", dt_all, True, nd.FriendRequestAccept)
2539 # host requests
2540 host_request__create = ("host_request:create", dt_all, True, nd.HostRequestCreate)
2541 host_request__accept = ("host_request:accept", dt_all, True, nd.HostRequestAccept)
2542 host_request__reject = ("host_request:reject", dt_all, True, nd.HostRequestReject)
2543 host_request__confirm = ("host_request:confirm", dt_all, True, nd.HostRequestConfirm)
2544 host_request__cancel = ("host_request:cancel", dt_all, True, nd.HostRequestCancel)
2545 host_request__message = ("host_request:message", [dt.push, dt.digest], True, nd.HostRequestMessage)
2546 host_request__missed_messages = ("host_request:missed_messages", [dt.email], True, nd.HostRequestMissedMessages)
2547 host_request__reminder = ("host_request:reminder", dt_all, True, nd.HostRequestReminder)
2549 activeness__probe = ("activeness:probe", dt_sec, False, nd.ActivenessProbe)
2551 # you receive a friend ref
2552 reference__receive_friend = ("reference:receive_friend", dt_all, True, nd.ReferenceReceiveFriend)
2553 # you receive a reference from ... the host
2554 reference__receive_hosted = ("reference:receive_hosted", dt_all, True, nd.ReferenceReceiveHostRequest)
2555 # ... the surfer
2556 reference__receive_surfed = ("reference:receive_surfed", dt_all, True, nd.ReferenceReceiveHostRequest)
2558 # you hosted
2559 reference__reminder_hosted = ("reference:reminder_hosted", dt_all, True, nd.ReferenceReminder)
2560 # you surfed
2561 reference__reminder_surfed = ("reference:reminder_surfed", dt_all, True, nd.ReferenceReminder)
2563 badge__add = ("badge:add", [dt.push, dt.digest], True, nd.BadgeAdd)
2564 badge__remove = ("badge:remove", [dt.push, dt.digest], True, nd.BadgeRemove)
2566 # group chats
2567 chat__message = ("chat:message", [dt.push, dt.digest], True, nd.ChatMessage)
2568 chat__missed_messages = ("chat:missed_messages", [dt.email], True, nd.ChatMissedMessages)
2570 # events
2571 # approved by mods
2572 event__create_approved = ("event:create_approved", dt_all, True, nd.EventCreate)
2573 # any user creates any event, default to no notifications
2574 event__create_any = ("event:create_any", [], True, nd.EventCreate)
2575 event__update = ("event:update", dt_all, True, nd.EventUpdate)
2576 event__cancel = ("event:cancel", dt_all, True, nd.EventCancel)
2577 event__delete = ("event:delete", dt_all, True, nd.EventDelete)
2578 event__invite_organizer = ("event:invite_organizer", dt_all, True, nd.EventInviteOrganizer)
2579 event__reminder = ("event:reminder", dt_all, True, nd.EventReminder)
2580 # toplevel comment on an event
2581 event__comment = ("event:comment", dt_all, True, nd.EventComment)
2583 # discussion created
2584 discussion__create = ("discussion:create", [dt.digest], True, nd.DiscussionCreate)
2585 # someone comments on your discussion
2586 discussion__comment = ("discussion:comment", dt_all, True, nd.DiscussionComment)
2588 # someone responds to any of your top-level comment across the platform
2589 thread__reply = ("thread:reply", dt_all, True, nd.ThreadReply)
2591 # account settings
2592 password__change = ("password:change", dt_sec, False, empty_pb2.Empty)
2593 email_address__change = ("email_address:change", dt_sec, False, nd.EmailAddressChange)
2594 email_address__verify = ("email_address:verify", dt_sec, False, empty_pb2.Empty)
2595 phone_number__change = ("phone_number:change", dt_sec, False, nd.PhoneNumberChange)
2596 phone_number__verify = ("phone_number:verify", dt_sec, False, nd.PhoneNumberVerify)
2597 # reset password
2598 password_reset__start = ("password_reset:start", dt_sec, False, nd.PasswordResetStart)
2599 password_reset__complete = ("password_reset:complete", dt_sec, False, empty_pb2.Empty)
2601 # account deletion
2602 account_deletion__start = ("account_deletion:start", dt_sec, False, nd.AccountDeletionStart)
2603 # no more pushing to do
2604 account_deletion__complete = ("account_deletion:complete", dt_sec, False, nd.AccountDeletionComplete)
2605 # undeleted
2606 account_deletion__recovered = ("account_deletion:recovered", dt_sec, False, empty_pb2.Empty)
2608 # admin actions
2609 gender__change = ("gender:change", dt_sec, False, nd.GenderChange)
2610 birthdate__change = ("birthdate:change", dt_sec, False, nd.BirthdateChange)
2611 api_key__create = ("api_key:create", dt_sec, False, nd.ApiKeyCreate)
2613 donation__received = ("donation:received", dt_sec, True, nd.DonationReceived)
2615 onboarding__reminder = ("onboarding:reminder", dt_sec, True, empty_pb2.Empty)
2617 modnote__create = ("modnote:create", dt_sec, False, empty_pb2.Empty)
2619 verification__sv_fail = ("verification:sv_fail", dt_sec, False, nd.VerificationSVFail)
2620 verification__sv_success = ("verification:sv_success", dt_sec, False, empty_pb2.Empty)
2622 # general announcements
2623 general__new_blog_post = ("general:new_blog_post", [dt.push, dt.digest], True, nd.GeneralNewBlogPost)
2626class NotificationPreference(Base):
2627 __tablename__ = "notification_preferences"
2629 id = Column(BigInteger, primary_key=True)
2630 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2632 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2633 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2634 deliver = Column(Boolean, nullable=False)
2636 user = relationship("User", foreign_keys="NotificationPreference.user_id")
2638 __table_args__ = (UniqueConstraint("user_id", "topic_action", "delivery_type"),)
2641class Notification(Base):
2642 """
2643 Table for accumulating notifications until it is time to send email digest
2644 """
2646 __tablename__ = "notifications"
2648 id = Column(BigInteger, primary_key=True)
2649 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2651 # recipient user id
2652 user_id = Column(ForeignKey("users.id"), nullable=False)
2654 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2655 key = Column(String, nullable=False)
2657 data = Column(Binary, nullable=False)
2659 # whether the user has marked this notification as seen or not
2660 is_seen = Column(Boolean, nullable=False, server_default=expression.false())
2662 user = relationship("User", foreign_keys="Notification.user_id")
2664 __table_args__ = (
2665 # used in looking up which notifications need delivery
2666 Index(
2667 "ix_notifications_created",
2668 created,
2669 ),
2670 # Fast lookup for unseen notification count
2671 Index(
2672 "ix_notifications_unseen",
2673 user_id,
2674 topic_action,
2675 postgresql_where=(is_seen == False),
2676 ),
2677 # Fast lookup for latest notifications
2678 Index(
2679 "ix_notifications_latest",
2680 user_id,
2681 id.desc(),
2682 topic_action,
2683 ),
2684 )
2686 @property
2687 def topic(self):
2688 return self.topic_action.topic
2690 @property
2691 def action(self):
2692 return self.topic_action.action
2695class NotificationDelivery(Base):
2696 __tablename__ = "notification_deliveries"
2698 id = Column(BigInteger, primary_key=True)
2699 notification_id = Column(ForeignKey("notifications.id"), nullable=False, index=True)
2700 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2701 delivered = Column(DateTime(timezone=True), nullable=True)
2702 read = Column(DateTime(timezone=True), nullable=True)
2703 # todo: enum of "phone, web, digest"
2704 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2705 # todo: device id
2706 # todo: receipt id, etc
2707 notification = relationship("Notification", foreign_keys="NotificationDelivery.notification_id")
2709 __table_args__ = (
2710 UniqueConstraint("notification_id", "delivery_type"),
2711 # used in looking up which notifications need delivery
2712 Index(
2713 "ix_notification_deliveries_delivery_type",
2714 delivery_type,
2715 postgresql_where=(delivered != None),
2716 ),
2717 Index(
2718 "ix_notification_deliveries_dt_ni_dnull",
2719 delivery_type,
2720 notification_id,
2721 delivered == None,
2722 ),
2723 )
2726class PushNotificationSubscription(Base):
2727 __tablename__ = "push_notification_subscriptions"
2729 id = Column(BigInteger, primary_key=True)
2730 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2732 # which user this is connected to
2733 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2735 # these come from https://developer.mozilla.org/en-US/docs/Web/API/PushSubscription
2736 # the endpoint
2737 endpoint = Column(String, nullable=False)
2738 # the "auth" key
2739 auth_key = Column(Binary, nullable=False)
2740 # the "p256dh" key
2741 p256dh_key = Column(Binary, nullable=False)
2743 full_subscription_info = Column(String, nullable=False)
2745 # the browse user-agent, so we can tell the user what browser notifications are going to
2746 user_agent = Column(String, nullable=True)
2748 # when it was disabled
2749 disabled_at = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_INFINITY.isoformat())
2751 user = relationship("User")
2754class PushNotificationDeliveryAttempt(Base):
2755 __tablename__ = "push_notification_delivery_attempt"
2757 id = Column(BigInteger, primary_key=True)
2758 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2760 push_notification_subscription_id = Column(
2761 ForeignKey("push_notification_subscriptions.id"), nullable=False, index=True
2762 )
2764 success = Column(Boolean, nullable=False)
2765 # the HTTP status code, 201 is success
2766 status_code = Column(Integer, nullable=False)
2768 # can be null if it was a success
2769 response = Column(String, nullable=True)
2771 push_notification_subscription = relationship("PushNotificationSubscription")
2774class Language(Base):
2775 """
2776 Table of allowed languages (a subset of ISO639-3)
2777 """
2779 __tablename__ = "languages"
2781 # ISO639-3 language code, in lowercase, e.g. fin, eng
2782 code = Column(String(3), primary_key=True)
2784 # the english name
2785 name = Column(String, nullable=False, unique=True)
2788class Region(Base):
2789 """
2790 Table of regions
2791 """
2793 __tablename__ = "regions"
2795 # iso 3166-1 alpha3 code in uppercase, e.g. FIN, USA
2796 code = Column(String(3), primary_key=True)
2798 # the name, e.g. Finland, United States
2799 # this is the display name in English, should be the "common name", not "Republic of Finland"
2800 name = Column(String, nullable=False, unique=True)
2803class UserBlock(Base):
2804 """
2805 Table of blocked users
2806 """
2808 __tablename__ = "user_blocks"
2810 id = Column(BigInteger, primary_key=True)
2812 blocking_user_id = Column(ForeignKey("users.id"), nullable=False)
2813 blocked_user_id = Column(ForeignKey("users.id"), nullable=False)
2814 time_blocked = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2816 blocking_user = relationship("User", foreign_keys="UserBlock.blocking_user_id")
2817 blocked_user = relationship("User", foreign_keys="UserBlock.blocked_user_id")
2819 __table_args__ = (
2820 UniqueConstraint("blocking_user_id", "blocked_user_id"),
2821 Index("ix_user_blocks_blocking_user_id", blocking_user_id, blocked_user_id),
2822 Index("ix_user_blocks_blocked_user_id", blocked_user_id, blocking_user_id),
2823 )
2826class APICall(Base):
2827 """
2828 API call logs
2829 """
2831 __tablename__ = "api_calls"
2832 __table_args__ = {"schema": "logging"}
2834 id = Column(BigInteger, primary_key=True)
2836 # whether the call was made using an api key or session cookies
2837 is_api_key = Column(Boolean, nullable=False, server_default=expression.false())
2839 # backend version (normally e.g. develop-31469e3), allows us to figure out which proto definitions were used
2840 # note that `default` is a python side default, not hardcoded into DB schema
2841 version = Column(String, nullable=False, default=config["VERSION"])
2843 # approximate time of the call
2844 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2846 # the method call name, e.g. "/org.couchers.api.core.API/ListFriends"
2847 method = Column(String, nullable=False)
2849 # gRPC status code name, e.g. FAILED_PRECONDITION, None if success
2850 status_code = Column(String, nullable=True)
2852 # handler duration (excluding serialization, etc)
2853 duration = Column(Float, nullable=False)
2855 # user_id of caller, None means not logged in
2856 user_id = Column(BigInteger, nullable=True)
2858 # sanitized request bytes
2859 request = Column(Binary, nullable=True)
2861 # sanitized response bytes
2862 response = Column(Binary, nullable=True)
2864 # whether response bytes have been truncated
2865 response_truncated = Column(Boolean, nullable=False, server_default=expression.false())
2867 # the exception traceback, if any
2868 traceback = Column(String, nullable=True)
2870 # human readable perf report
2871 perf_report = Column(String, nullable=True)
2873 # details of the browser, if available
2874 ip_address = Column(String, nullable=True)
2875 user_agent = Column(String, nullable=True)
2878class AccountDeletionReason(Base):
2879 __tablename__ = "account_deletion_reason"
2881 id = Column(BigInteger, primary_key=True)
2882 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2883 user_id = Column(ForeignKey("users.id"), nullable=False)
2884 reason = Column(String, nullable=True)
2886 user = relationship("User")
2889class ModerationUserList(Base):
2890 """
2891 Represents a list of users listed together by a moderator
2892 """
2894 __tablename__ = "moderation_user_lists"
2896 id = Column(BigInteger, primary_key=True)
2897 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2899 # Relationships
2900 users = relationship("User", secondary="moderation_user_list_members", back_populates="moderation_user_lists")
2903class ModerationUserListMember(Base):
2904 """
2905 Association table for many-to-many relationship between users and moderation_user_lists
2906 """
2908 __tablename__ = "moderation_user_list_members"
2910 user_id = Column(ForeignKey("users.id"), primary_key=True)
2911 moderation_list_id = Column(ForeignKey("moderation_user_lists.id"), primary_key=True)
2913 __table_args__ = (UniqueConstraint("user_id", "moderation_list_id"),)
2916class AntiBotLog(Base):
2917 __tablename__ = "antibot_logs"
2919 id = Column(BigInteger, primary_key=True)
2920 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2921 user_id = Column(ForeignKey("users.id"), nullable=True)
2923 ip_address = Column(String, nullable=True)
2924 user_agent = Column(String, nullable=True)
2926 action = Column(String, nullable=False)
2927 token = Column(String, nullable=False)
2929 score = Column(Float, nullable=False)
2930 provider_data = Column(JSON, nullable=False)
2933class RateLimitAction(enum.Enum):
2934 """Possible user actions which can be rate limited."""
2936 host_request = "host request"
2937 friend_request = "friend request"
2938 chat_initiation = "chat initiation"
2941class RateLimitViolation(Base):
2942 __tablename__ = "rate_limit_violations"
2944 id = Column(BigInteger, primary_key=True)
2945 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2946 user_id = Column(ForeignKey("users.id"), nullable=False)
2947 action = Column(Enum(RateLimitAction), nullable=False)
2948 is_hard_limit = Column(Boolean, nullable=False)
2950 user = relationship("User")
2952 __table_args__ = (
2953 # Fast lookup for rate limits in interval
2954 Index("ix_rate_limits_by_user", user_id, action, is_hard_limit, created),
2955 )
2958class Volunteer(Base):
2959 __tablename__ = "volunteers"
2961 id = Column(BigInteger, primary_key=True)
2962 user_id = Column(ForeignKey("users.id"), nullable=False, unique=True)
2964 display_name = Column(String, nullable=True)
2965 display_location = Column(String, nullable=True)
2967 role = Column(String, nullable=False)
2969 # custom sort order on team page, sorted ascending
2970 sort_key = Column(Float, nullable=True)
2972 started_volunteering = Column(Date, nullable=False, server_default=text("CURRENT_DATE"))
2973 stopped_volunteering = Column(Date, nullable=True, default=None)
2975 link_type = Column(String, nullable=True)
2976 link_text = Column(String, nullable=True)
2977 link_url = Column(String, nullable=True)
2979 show_on_team_page = Column(Boolean, nullable=False, server_default=expression.true())
2981 __table_args__ = (
2982 # Link type, text, url should all be null or all not be null
2983 CheckConstraint(
2984 "(link_type IS NULL) = (link_text IS NULL) AND (link_type IS NULL) = (link_url IS NULL)",
2985 name="link_type_text",
2986 ),
2987 )