Coverage for src/couchers/models.py: 99%
1074 statements
« prev ^ index » next coverage.py v7.6.10, created at 2025-01-22 06:42 +0000
1import enum
3from geoalchemy2.types import Geometry
4from google.protobuf import empty_pb2
5from sqlalchemy import (
6 ARRAY,
7 BigInteger,
8 Boolean,
9 CheckConstraint,
10 Column,
11 Date,
12 DateTime,
13 Enum,
14 Float,
15 ForeignKey,
16 Index,
17 Integer,
18 Interval,
19 MetaData,
20 Sequence,
21 String,
22 UniqueConstraint,
23)
24from sqlalchemy import LargeBinary as Binary
25from sqlalchemy.dialects.postgresql import INET, TSTZRANGE, ExcludeConstraint
26from sqlalchemy.ext.associationproxy import association_proxy
27from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property
28from sqlalchemy.orm import backref, column_property, declarative_base, deferred, relationship
29from sqlalchemy.sql import and_, func, text
30from sqlalchemy.sql import select as sa_select
32from couchers import urls
33from couchers.config import config
34from couchers.constants import (
35 DATETIME_INFINITY,
36 DATETIME_MINUS_INFINITY,
37 EMAIL_REGEX,
38 GUIDELINES_VERSION,
39 PHONE_VERIFICATION_LIFETIME,
40 SMS_CODE_LIFETIME,
41 TOS_VERSION,
42)
43from couchers.utils import date_in_timezone, get_coordinates, last_active_coarsen, now
44from proto import notification_data_pb2
46meta = MetaData(
47 naming_convention={
48 "ix": "ix_%(column_0_label)s",
49 "uq": "uq_%(table_name)s_%(column_0_name)s",
50 "ck": "ck_%(table_name)s_%(constraint_name)s",
51 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
52 "pk": "pk_%(table_name)s",
53 }
54)
56Base = declarative_base(metadata=meta)
59class HostingStatus(enum.Enum):
60 can_host = enum.auto()
61 maybe = enum.auto()
62 cant_host = enum.auto()
65class MeetupStatus(enum.Enum):
66 wants_to_meetup = enum.auto()
67 open_to_meetup = enum.auto()
68 does_not_want_to_meetup = enum.auto()
71class SmokingLocation(enum.Enum):
72 yes = enum.auto()
73 window = enum.auto()
74 outside = enum.auto()
75 no = enum.auto()
78class SleepingArrangement(enum.Enum):
79 private = enum.auto()
80 common = enum.auto()
81 shared_room = enum.auto()
82 shared_space = enum.auto()
85class ParkingDetails(enum.Enum):
86 free_onsite = enum.auto()
87 free_offsite = enum.auto()
88 paid_onsite = enum.auto()
89 paid_offsite = enum.auto()
92class TimezoneArea(Base):
93 __tablename__ = "timezone_areas"
94 id = Column(BigInteger, primary_key=True)
96 tzid = Column(String)
97 geom = Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False)
99 __table_args__ = (
100 Index(
101 "ix_timezone_areas_geom_tzid",
102 geom,
103 tzid,
104 postgresql_using="gist",
105 ),
106 )
109class User(Base):
110 """
111 Basic user and profile details
112 """
114 __tablename__ = "users"
116 id = Column(BigInteger, primary_key=True)
118 username = Column(String, nullable=False, unique=True)
119 email = Column(String, nullable=False, unique=True)
120 # stored in libsodium hash format, can be null for email login
121 hashed_password = Column(Binary, nullable=False)
122 # phone number in E.164 format with leading +, for example "+46701740605"
123 phone = Column(String, nullable=True, server_default=text("NULL"))
125 # timezones should always be UTC
126 ## location
127 # point describing their location. EPSG4326 is the SRS (spatial ref system, = way to describe a point on earth) used
128 # by GPS, it has the WGS84 geoid with lat/lon
129 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
130 # their display location (displayed to other users), in meters
131 geom_radius = Column(Float, nullable=True)
132 # the display address (text) shown on their profile
133 city = Column(String, nullable=False)
134 # "Grew up in" on profile
135 hometown = Column(String, nullable=True)
137 regions_visited = relationship("Region", secondary="regions_visited", order_by="Region.name")
138 regions_lived = relationship("Region", secondary="regions_lived", order_by="Region.name")
140 timezone = column_property(
141 sa_select(TimezoneArea.tzid).where(func.ST_Contains(TimezoneArea.geom, geom)).limit(1).scalar_subquery(),
142 deferred=True,
143 )
145 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
146 last_active = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
148 # id of the last message that they received a notification about
149 last_notified_message_id = Column(BigInteger, nullable=False, default=0)
150 # same as above for host requests
151 last_notified_request_message_id = Column(BigInteger, nullable=False, server_default=text("0"))
153 # display name
154 name = Column(String, nullable=False)
155 gender = Column(String, nullable=False)
156 pronouns = Column(String, nullable=True)
157 birthdate = Column(Date, nullable=False) # in the timezone of birthplace
159 avatar_key = Column(ForeignKey("uploads.key"), nullable=True)
161 hosting_status = Column(Enum(HostingStatus), nullable=False)
162 meetup_status = Column(Enum(MeetupStatus), nullable=False, server_default="open_to_meetup")
164 # community standing score
165 community_standing = Column(Float, nullable=True)
167 occupation = Column(String, nullable=True) # CommonMark without images
168 education = Column(String, nullable=True) # CommonMark without images
170 # "Who I am" under "About Me" tab
171 about_me = Column(String, nullable=True) # CommonMark without images
172 # "What I do in my free time" under "About Me" tab
173 things_i_like = Column(String, nullable=True) # CommonMark without images
174 # "About my home" under "My Home" tab
175 about_place = Column(String, nullable=True) # CommonMark without images
176 # "Additional information" under "About Me" tab
177 additional_information = Column(String, nullable=True) # CommonMark without images
179 is_banned = Column(Boolean, nullable=False, server_default=text("false"))
180 is_deleted = Column(Boolean, nullable=False, server_default=text("false"))
181 is_superuser = Column(Boolean, nullable=False, server_default=text("false"))
183 # the undelete token allows a user to recover their account for a couple of days after deletion in case it was
184 # accidental or they changed their mind
185 # constraints make sure these are non-null only if is_deleted and that these are null in unison
186 undelete_token = Column(String, nullable=True)
187 # validity of the undelete token
188 undelete_until = Column(DateTime(timezone=True), nullable=True)
190 # hosting preferences
191 max_guests = Column(Integer, nullable=True)
192 last_minute = Column(Boolean, nullable=True)
193 has_pets = Column(Boolean, nullable=True)
194 accepts_pets = Column(Boolean, nullable=True)
195 pet_details = Column(String, nullable=True) # CommonMark without images
196 has_kids = Column(Boolean, nullable=True)
197 accepts_kids = Column(Boolean, nullable=True)
198 kid_details = Column(String, nullable=True) # CommonMark without images
199 has_housemates = Column(Boolean, nullable=True)
200 housemate_details = Column(String, nullable=True) # CommonMark without images
201 wheelchair_accessible = Column(Boolean, nullable=True)
202 smoking_allowed = Column(Enum(SmokingLocation), nullable=True)
203 smokes_at_home = Column(Boolean, nullable=True)
204 drinking_allowed = Column(Boolean, nullable=True)
205 drinks_at_home = Column(Boolean, nullable=True)
206 # "Additional information" under "My Home" tab
207 other_host_info = Column(String, nullable=True) # CommonMark without images
209 # "Sleeping privacy" (not long-form text)
210 sleeping_arrangement = Column(Enum(SleepingArrangement), nullable=True)
211 # "Sleeping arrangement" under "My Home" tab
212 sleeping_details = Column(String, nullable=True) # CommonMark without images
213 # "Local area information" under "My Home" tab
214 area = Column(String, nullable=True) # CommonMark without images
215 # "House rules" under "My Home" tab
216 house_rules = Column(String, nullable=True) # CommonMark without images
217 parking = Column(Boolean, nullable=True)
218 parking_details = Column(Enum(ParkingDetails), nullable=True) # CommonMark without images
219 camping_ok = Column(Boolean, nullable=True)
221 accepted_tos = Column(Integer, nullable=False, default=0)
222 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")
223 # whether the user has yet filled in the contributor form
224 filled_contributor_form = Column(Boolean, nullable=False, server_default="false")
226 # number of onboarding emails sent
227 onboarding_emails_sent = Column(Integer, nullable=False, server_default="0")
228 last_onboarding_email_sent = Column(DateTime(timezone=True), nullable=True)
230 # whether we need to sync the user's newsletter preferences with the newsletter server
231 in_sync_with_newsletter = Column(Boolean, nullable=False, server_default="false")
232 # opted out of the newsletter
233 opt_out_of_newsletter = Column(Boolean, nullable=False, server_default="false")
235 # set to null to receive no digests
236 digest_frequency = Column(Interval, nullable=True)
237 last_digest_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)"))
239 # for changing their email
240 new_email = Column(String, nullable=True)
242 new_email_token = Column(String, nullable=True)
243 new_email_token_created = Column(DateTime(timezone=True), nullable=True)
244 new_email_token_expiry = Column(DateTime(timezone=True), nullable=True)
246 recommendation_score = Column(Float, nullable=False, server_default="0")
248 # Columns for verifying their phone number. State chart:
249 # ,-------------------,
250 # | Start |
251 # | phone = None | someone else
252 # ,-----------------, | token = None | verifies ,-----------------------,
253 # | Code Expired | | sent = 1970 or zz | phone xx | Verification Expired |
254 # | phone = xx | time passes | verified = None | <------, | phone = xx |
255 # | token = yy | <------------, | attempts = 0 | | | token = None |
256 # | sent = zz (exp.)| | '-------------------' | | sent = zz |
257 # | verified = None | | V ^ +-----------< | verified = ww (exp.) |
258 # | attempts = 0..2 | >--, | | | ChangePhone("") | | attempts = 0 |
259 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------'
260 # | | | | ChangePhone(xx) | ^ time passes
261 # | | ^ V | |
262 # ,-----------------, | | ,-------------------, | ,-----------------------,
263 # | Too Many | >--' '--< | Code sent | >------+ | Verified |
264 # | phone = xx | | phone = xx | | | phone = xx |
265 # | token = yy | VerifyPhone(wrong)| token = yy | '-----------< | token = None |
266 # | sent = zz | <------+--------< | sent = zz | | sent = zz |
267 # | verified = None | | | verified = None | VerifyPhone(correct) | verified = ww |
268 # | attempts = 3 | '--------> | attempts = 0..2 | >------------------> | attempts = 0 |
269 # '-----------------' '-------------------' '-----------------------'
271 # randomly generated Luhn 6-digit string
272 phone_verification_token = Column(String(6), nullable=True, server_default=text("NULL"))
274 phone_verification_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)"))
275 phone_verification_verified = Column(DateTime(timezone=True), nullable=True, server_default=text("NULL"))
276 phone_verification_attempts = Column(Integer, nullable=False, server_default=text("0"))
278 # the stripe customer identifier if the user has donated to Couchers
279 # e.g. cus_JjoXHttuZopv0t
280 # for new US entity
281 stripe_customer_id = Column(String, nullable=True)
282 # for old AU entity
283 stripe_customer_id_old = Column(String, nullable=True)
285 has_passport_sex_gender_exception = Column(Boolean, nullable=False, server_default=text("false"))
287 # checking for phone verification
288 has_donated = Column(Boolean, nullable=False, server_default=text("false"))
290 # whether this user has all emails turned off
291 do_not_email = Column(Boolean, nullable=False, server_default=text("false"))
293 avatar = relationship("Upload", foreign_keys="User.avatar_key")
295 admin_note = Column(String, nullable=False, server_default=text("''"))
297 age = column_property(func.date_part("year", func.age(birthdate)))
299 __table_args__ = (
300 # Verified phone numbers should be unique
301 Index(
302 "ix_users_unique_phone",
303 phone,
304 unique=True,
305 postgresql_where=phone_verification_verified != None,
306 ),
307 Index(
308 "ix_users_active",
309 id,
310 postgresql_where=~is_banned & ~is_deleted,
311 ),
312 # create index on users(geom, id, username) where not is_banned and not is_deleted and geom is not null;
313 Index(
314 "ix_users_geom_active",
315 geom,
316 id,
317 username,
318 postgresql_where=~is_banned & ~is_deleted & (geom != None),
319 ),
320 # There are two possible states for new_email_token, new_email_token_created, and new_email_token_expiry
321 CheckConstraint(
322 "(new_email_token IS NOT NULL AND new_email_token_created IS NOT NULL AND new_email_token_expiry IS NOT NULL) OR \
323 (new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL)",
324 name="check_new_email_token_state",
325 ),
326 # Whenever a phone number is set, it must either be pending verification or already verified.
327 # Exactly one of the following must always be true: not phone, token, verified.
328 CheckConstraint(
329 "(phone IS NULL)::int + (phone_verification_verified IS NOT NULL)::int + (phone_verification_token IS NOT NULL)::int = 1",
330 name="phone_verified_conditions",
331 ),
332 # Email must match our regex
333 CheckConstraint(
334 f"email ~ '{EMAIL_REGEX}'",
335 name="valid_email",
336 ),
337 # Undelete token + time are coupled: either both null or neither; and if they're not null then the account is deleted
338 CheckConstraint(
339 "((undelete_token IS NULL) = (undelete_until IS NULL)) AND ((undelete_token IS NULL) OR is_deleted)",
340 name="undelete_nullity",
341 ),
342 # If the user disabled all emails, then they can't host or meet up
343 CheckConstraint(
344 "(do_not_email IS FALSE) OR ((hosting_status = 'cant_host') AND (meetup_status = 'does_not_want_to_meetup'))",
345 name="do_not_email_inactive",
346 ),
347 )
349 @hybrid_property
350 def has_completed_profile(self):
351 return self.avatar_key is not None and self.about_me is not None and len(self.about_me) >= 150
353 @has_completed_profile.expression
354 def has_completed_profile(cls):
355 return (cls.avatar_key != None) & (func.character_length(cls.about_me) >= 150)
357 @hybrid_property
358 def is_jailed(self):
359 return (
360 (self.accepted_tos < TOS_VERSION)
361 | (self.accepted_community_guidelines < GUIDELINES_VERSION)
362 | self.is_missing_location
363 | (self.mod_notes.where(ModNote.is_pending).count() > 0)
364 )
366 @hybrid_property
367 def is_missing_location(self):
368 return (self.geom == None) | (self.geom_radius == None)
370 @hybrid_property
371 def is_visible(self):
372 return ~(self.is_banned | self.is_deleted)
374 @property
375 def coordinates(self):
376 return get_coordinates(self.geom)
378 @property
379 def display_joined(self):
380 """
381 Returns the last active time rounded down to the nearest hour.
382 """
383 return self.joined.replace(minute=0, second=0, microsecond=0)
385 @property
386 def display_last_active(self):
387 """
388 Returns the last active time rounded down whatever is the "last active" coarsening.
389 """
390 return last_active_coarsen(self.last_active)
392 @hybrid_property
393 def phone_is_verified(self):
394 return (
395 self.phone_verification_verified is not None
396 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
397 )
399 @phone_is_verified.expression
400 def phone_is_verified(cls):
401 return (cls.phone_verification_verified != None) & (
402 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
403 )
405 @hybrid_property
406 def phone_code_expired(self):
407 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME
409 def __repr__(self):
410 return f"User(id={self.id}, email={self.email}, username={self.username})"
413class UserBadge(Base):
414 """
415 A badge on a user's profile
416 """
418 __tablename__ = "user_badges"
419 __table_args__ = (UniqueConstraint("user_id", "badge_id"),)
421 id = Column(BigInteger, primary_key=True)
423 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
424 # corresponds to "id" in badges.json
425 badge_id = Column(String, nullable=False, index=True)
427 # take this with a grain of salt, someone may get then lose a badge for whatever reason
428 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
430 user = relationship("User", backref="badges")
433class StrongVerificationAttemptStatus(enum.Enum):
434 ## full data states
435 # completed, this now provides verification for a user
436 succeeded = enum.auto()
438 ## no data states
439 # in progress: waiting for the user to scan the Iris code or open the app
440 in_progress_waiting_on_user_to_open_app = enum.auto()
441 # in progress: waiting for the user to scan MRZ or NFC/chip
442 in_progress_waiting_on_user_in_app = enum.auto()
443 # in progress, waiting for backend to pull verification data
444 in_progress_waiting_on_backend = enum.auto()
445 # failed, no data
446 failed = enum.auto()
448 # duplicate, at our end, has data
449 duplicate = enum.auto()
451 ## minimal data states
452 # the data, except minimal deduplication data, was deleted
453 deleted = enum.auto()
456class PassportSex(enum.Enum):
457 """
458 We don't care about sex, we use gender on the platform. But passports apparently do.
459 """
461 male = enum.auto()
462 female = enum.auto()
463 unspecified = enum.auto()
466class StrongVerificationAttempt(Base):
467 """
468 An attempt to perform strong verification
469 """
471 __tablename__ = "strong_verification_attempts"
473 # our verification id
474 id = Column(BigInteger, primary_key=True)
476 # this is returned in the callback, and we look up the attempt via this
477 verification_attempt_token = Column(String, nullable=False, unique=True)
479 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
480 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
482 status = Column(
483 Enum(StrongVerificationAttemptStatus),
484 nullable=False,
485 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app,
486 )
488 ## full data
489 has_full_data = Column(Boolean, nullable=False, default=False)
490 # the data returned from iris, encrypted with a public key whose private key is kept offline
491 passport_encrypted_data = Column(Binary, nullable=True)
492 passport_date_of_birth = Column(Date, nullable=True)
493 passport_sex = Column(Enum(PassportSex), nullable=True)
495 ## minimal data: this will not be deleted
496 has_minimal_data = Column(Boolean, nullable=False, default=False)
497 passport_expiry_date = Column(Date, nullable=True)
498 passport_nationality = Column(String, nullable=True)
499 # last three characters of the passport number
500 passport_last_three_document_chars = Column(String, nullable=True)
502 iris_token = Column(String, nullable=False, unique=True)
503 iris_session_id = Column(BigInteger, nullable=False, unique=True)
505 passport_expiry_datetime = column_property(date_in_timezone(passport_expiry_date, "Etc/UTC"))
507 user = relationship("User")
509 @hybrid_property
510 def is_valid(self):
511 """
512 This only checks whether the attempt is a success and the passport is not expired, use `has_strong_verification` for full check
513 """
514 return (self.status == StrongVerificationAttemptStatus.succeeded) and (self.passport_expiry_datetime >= now())
516 @is_valid.expression
517 def is_valid(cls):
518 return (cls.status == StrongVerificationAttemptStatus.succeeded) & (
519 func.coalesce(cls.passport_expiry_datetime >= func.now(), False)
520 )
522 @hybrid_property
523 def is_visible(self):
524 return self.status != StrongVerificationAttemptStatus.deleted
526 @hybrid_method
527 def _raw_birthdate_match(self, user):
528 """Does not check whether the SV attempt itself is not expired"""
529 return self.passport_date_of_birth == user.birthdate
531 @hybrid_method
532 def matches_birthdate(self, user):
533 return self.is_valid & self._raw_birthdate_match(user)
535 @hybrid_method
536 def _raw_gender_match(self, user):
537 """Does not check whether the SV attempt itself is not expired"""
538 return (
539 ((user.gender == "Woman") & (self.passport_sex == PassportSex.female))
540 | ((user.gender == "Man") & (self.passport_sex == PassportSex.male))
541 | (self.passport_sex == PassportSex.unspecified)
542 | (user.has_passport_sex_gender_exception == True)
543 )
545 @hybrid_method
546 def matches_gender(self, user):
547 return self.is_valid & self._raw_gender_match(user)
549 @hybrid_method
550 def has_strong_verification(self, user):
551 return self.is_valid & self._raw_birthdate_match(user) & self._raw_gender_match(user)
553 __table_args__ = (
554 # used to look up verification status for a user
555 Index(
556 "ix_strong_verification_attempts_current",
557 user_id,
558 passport_expiry_date,
559 postgresql_where=status == StrongVerificationAttemptStatus.succeeded,
560 ),
561 # each passport can be verified only once
562 Index(
563 "ix_strong_verification_attempts_unique_succeeded",
564 passport_expiry_date,
565 passport_nationality,
566 passport_last_three_document_chars,
567 unique=True,
568 postgresql_where=(
569 (status == StrongVerificationAttemptStatus.succeeded)
570 | (status == StrongVerificationAttemptStatus.deleted)
571 ),
572 ),
573 # full data check
574 CheckConstraint(
575 "(has_full_data IS TRUE AND passport_encrypted_data IS NOT NULL AND passport_date_of_birth IS NOT NULL) OR \
576 (has_full_data IS FALSE AND passport_encrypted_data IS NULL AND passport_date_of_birth IS NULL)",
577 name="full_data_status",
578 ),
579 # minimal data check
580 CheckConstraint(
581 "(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 \
582 (has_minimal_data IS FALSE AND passport_expiry_date IS NULL AND passport_nationality IS NULL AND passport_last_three_document_chars IS NULL)",
583 name="minimal_data_status",
584 ),
585 # note on implications: p => q iff ~p OR q
586 # full data implies minimal data, has_minimal_data => has_full_data
587 CheckConstraint(
588 "(has_full_data IS FALSE) OR (has_minimal_data IS TRUE)",
589 name="full_data_implies_minimal_data",
590 ),
591 # succeeded implies full data
592 CheckConstraint(
593 "(NOT (status = 'succeeded')) OR (has_full_data IS TRUE)",
594 name="succeeded_implies_full_data",
595 ),
596 # in_progress/failed implies no_data
597 CheckConstraint(
598 "(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)",
599 name="in_progress_failed_iris_implies_no_data",
600 ),
601 # deleted or duplicate implies minimal data
602 CheckConstraint(
603 "(NOT ((status = 'deleted') OR (status = 'duplicate'))) OR (has_minimal_data IS TRUE)",
604 name="deleted_duplicate_implies_minimal_data",
605 ),
606 )
609class ModNote(Base):
610 """
611 A moderator note to a user. This could be a warning, just a note "hey, we did X", or any other similar message.
613 The user has to read and "acknowledge" the note before continuing onto the platform, and being un-jailed.
614 """
616 __tablename__ = "mod_notes"
617 id = Column(BigInteger, primary_key=True)
619 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
621 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
622 acknowledged = Column(DateTime(timezone=True), nullable=True)
624 # this is an internal ID to allow the mods to track different types of notes
625 internal_id = Column(String, nullable=False)
626 # the admin that left this note
627 creator_user_id = Column(ForeignKey("users.id"), nullable=False)
629 note_content = Column(String, nullable=False) # CommonMark without images
631 user = relationship("User", backref=backref("mod_notes", lazy="dynamic"), foreign_keys="ModNote.user_id")
633 def __repr__(self):
634 return f"ModeNote(id={self.id}, user={self.user}, created={self.created}, ack'd={self.acknowledged})"
636 @hybrid_property
637 def is_pending(self):
638 return self.acknowledged == None
640 __table_args__ = (
641 # used to look up pending notes
642 Index(
643 "ix_mod_notes_unacknowledged",
644 user_id,
645 postgresql_where=acknowledged == None,
646 ),
647 )
650class StrongVerificationCallbackEvent(Base):
651 __tablename__ = "strong_verification_callback_events"
653 id = Column(BigInteger, primary_key=True)
654 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
656 verification_attempt_id = Column(ForeignKey("strong_verification_attempts.id"), nullable=False, index=True)
658 iris_status = Column(String, nullable=False)
661class DonationType(enum.Enum):
662 one_time = enum.auto()
663 recurring = enum.auto()
666class DonationInitiation(Base):
667 """
668 Whenever someone initiaties a donation through the platform
669 """
671 __tablename__ = "donation_initiations"
672 id = Column(BigInteger, primary_key=True)
674 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
675 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
677 amount = Column(Integer, nullable=False)
678 stripe_checkout_session_id = Column(String, nullable=False)
680 donation_type = Column(Enum(DonationType), nullable=False)
682 user = relationship("User", backref="donation_initiations")
685class Invoice(Base):
686 """
687 Successful donations, both one off and recurring
689 Triggered by `payment_intent.succeeded` webhook
690 """
692 __tablename__ = "invoices"
694 id = Column(BigInteger, primary_key=True)
695 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
696 user_id = Column(ForeignKey("users.id"), nullable=False)
698 amount = Column(Float, nullable=False)
700 stripe_payment_intent_id = Column(String, nullable=False, unique=True)
701 stripe_receipt_url = Column(String, nullable=False)
703 user = relationship("User", backref="invoices")
706class LanguageFluency(enum.Enum):
707 # note that the numbering is important here, these are ordinal
708 beginner = 1
709 conversational = 2
710 fluent = 3
713class LanguageAbility(Base):
714 __tablename__ = "language_abilities"
715 __table_args__ = (
716 # Users can only have one language ability per language
717 UniqueConstraint("user_id", "language_code"),
718 )
720 id = Column(BigInteger, primary_key=True)
721 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
722 language_code = Column(ForeignKey("languages.code", deferrable=True), nullable=False)
723 fluency = Column(Enum(LanguageFluency), nullable=False)
725 user = relationship("User", backref="language_abilities")
726 language = relationship("Language")
729class RegionVisited(Base):
730 __tablename__ = "regions_visited"
731 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
733 id = Column(BigInteger, primary_key=True)
734 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
735 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
738class RegionLived(Base):
739 __tablename__ = "regions_lived"
740 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
742 id = Column(BigInteger, primary_key=True)
743 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
744 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
747class FriendStatus(enum.Enum):
748 pending = enum.auto()
749 accepted = enum.auto()
750 rejected = enum.auto()
751 cancelled = enum.auto()
754class FriendRelationship(Base):
755 """
756 Friendship relations between users
758 TODO: make this better with sqlalchemy self-referential stuff
759 TODO: constraint on only one row per user pair where accepted or pending
760 """
762 __tablename__ = "friend_relationships"
764 id = Column(BigInteger, primary_key=True)
766 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
767 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
769 status = Column(Enum(FriendStatus), nullable=False, default=FriendStatus.pending)
771 # timezones should always be UTC
772 time_sent = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
773 time_responded = Column(DateTime(timezone=True), nullable=True)
775 from_user = relationship("User", backref="friends_from", foreign_keys="FriendRelationship.from_user_id")
776 to_user = relationship("User", backref="friends_to", foreign_keys="FriendRelationship.to_user_id")
779class ContributeOption(enum.Enum):
780 yes = enum.auto()
781 maybe = enum.auto()
782 no = enum.auto()
785class ContributorForm(Base):
786 """
787 Someone filled in the contributor form
788 """
790 __tablename__ = "contributor_forms"
792 id = Column(BigInteger, primary_key=True)
794 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
795 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
797 ideas = Column(String, nullable=True)
798 features = Column(String, nullable=True)
799 experience = Column(String, nullable=True)
800 contribute = Column(Enum(ContributeOption), nullable=True)
801 contribute_ways = Column(ARRAY(String), nullable=False)
802 expertise = Column(String, nullable=True)
804 user = relationship("User", backref="contributor_forms")
806 @hybrid_property
807 def is_filled(self):
808 """
809 Whether the form counts as having been filled
810 """
811 return (
812 (self.ideas != None)
813 | (self.features != None)
814 | (self.experience != None)
815 | (self.contribute != None)
816 | (self.contribute_ways != [])
817 | (self.expertise != None)
818 )
820 @property
821 def should_notify(self):
822 """
823 If this evaluates to true, we send an email to the recruitment team.
825 We currently send if expertise is listed, or if they list a way to help outside of a set list
826 """
827 return (self.expertise != None) | (not set(self.contribute_ways).issubset({"community", "blog", "other"}))
830class SignupFlow(Base):
831 """
832 Signup flows/incomplete users
834 Coinciding fields have the same meaning as in User
835 """
837 __tablename__ = "signup_flows"
839 id = Column(BigInteger, primary_key=True)
841 # housekeeping
842 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
843 flow_token = Column(String, nullable=False, unique=True)
844 email_verified = Column(Boolean, nullable=False, default=False)
845 email_sent = Column(Boolean, nullable=False, default=False)
846 email_token = Column(String, nullable=True)
847 email_token_expiry = Column(DateTime(timezone=True), nullable=True)
849 ## Basic
850 name = Column(String, nullable=False)
851 # TODO: unique across both tables
852 email = Column(String, nullable=False, unique=True)
853 # TODO: invitation, attribution
855 ## Account
856 # TODO: unique across both tables
857 username = Column(String, nullable=True, unique=True)
858 hashed_password = Column(Binary, nullable=True)
859 birthdate = Column(Date, nullable=True) # in the timezone of birthplace
860 gender = Column(String, nullable=True)
861 hosting_status = Column(Enum(HostingStatus), nullable=True)
862 city = Column(String, nullable=True)
863 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
864 geom_radius = Column(Float, nullable=True)
866 accepted_tos = Column(Integer, nullable=True)
867 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")
869 opt_out_of_newsletter = Column(Boolean, nullable=True)
871 ## Feedback
872 filled_feedback = Column(Boolean, nullable=False, default=False)
873 ideas = Column(String, nullable=True)
874 features = Column(String, nullable=True)
875 experience = Column(String, nullable=True)
876 contribute = Column(Enum(ContributeOption), nullable=True)
877 contribute_ways = Column(ARRAY(String), nullable=True)
878 expertise = Column(String, nullable=True)
880 @hybrid_property
881 def token_is_valid(self):
882 return (self.email_token != None) & (self.email_token_expiry >= now())
884 @hybrid_property
885 def account_is_filled(self):
886 return (
887 (self.username != None)
888 & (self.birthdate != None)
889 & (self.gender != None)
890 & (self.hosting_status != None)
891 & (self.city != None)
892 & (self.geom != None)
893 & (self.geom_radius != None)
894 & (self.accepted_tos != None)
895 & (self.opt_out_of_newsletter != None)
896 )
898 @hybrid_property
899 def is_completed(self):
900 return (
901 self.email_verified
902 & self.account_is_filled
903 & self.filled_feedback
904 & (self.accepted_community_guidelines == GUIDELINES_VERSION)
905 )
908class LoginToken(Base):
909 """
910 A login token sent in an email to a user, allows them to sign in between the times defined by created and expiry
911 """
913 __tablename__ = "login_tokens"
914 token = Column(String, primary_key=True)
916 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
918 # timezones should always be UTC
919 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
920 expiry = Column(DateTime(timezone=True), nullable=False)
922 user = relationship("User", backref="login_tokens")
924 @hybrid_property
925 def is_valid(self):
926 return (self.created <= now()) & (self.expiry >= now())
928 def __repr__(self):
929 return f"LoginToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
932class PasswordResetToken(Base):
933 __tablename__ = "password_reset_tokens"
934 token = Column(String, primary_key=True)
936 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
938 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
939 expiry = Column(DateTime(timezone=True), nullable=False)
941 user = relationship("User", backref="password_reset_tokens")
943 @hybrid_property
944 def is_valid(self):
945 return (self.created <= now()) & (self.expiry >= now())
947 def __repr__(self):
948 return f"PasswordResetToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
951class AccountDeletionToken(Base):
952 __tablename__ = "account_deletion_tokens"
954 token = Column(String, primary_key=True)
956 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
958 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
959 expiry = Column(DateTime(timezone=True), nullable=False)
961 user = relationship("User", backref="account_deletion_tokens")
963 @hybrid_property
964 def is_valid(self):
965 return (self.created <= now()) & (self.expiry >= now())
967 def __repr__(self):
968 return f"AccountDeletionToken(token={self.token}, user_id={self.user_id}, created={self.created}, expiry={self.expiry})"
971class UserActivity(Base):
972 """
973 User activity: for each unique (user_id, period, ip_address, user_agent) tuple, keep track of number of api calls
975 Used for user "last active" as well as admin stuff
976 """
978 __tablename__ = "user_activity"
980 id = Column(BigInteger, primary_key=True)
982 user_id = Column(ForeignKey("users.id"), nullable=False)
983 # the start of a period of time, e.g. 1 hour during which we bin activeness
984 period = Column(DateTime(timezone=True), nullable=False)
986 # details of the browser, if available
987 ip_address = Column(INET, nullable=True)
988 user_agent = Column(String, nullable=True)
990 # count of api calls made with this ip, user_agent, and period
991 api_calls = Column(Integer, nullable=False, default=0)
993 __table_args__ = (
994 # helps look up this tuple quickly
995 Index(
996 "ix_user_activity_user_id_period_ip_address_user_agent",
997 user_id,
998 period,
999 ip_address,
1000 user_agent,
1001 unique=True,
1002 ),
1003 )
1006class UserSession(Base):
1007 """
1008 API keys/session cookies for the app
1010 There are two types of sessions: long-lived, and short-lived. Long-lived are
1011 like when you choose "remember this browser": they will be valid for a long
1012 time without the user interacting with the site. Short-lived sessions on the
1013 other hand get invalidated quickly if the user does not interact with the
1014 site.
1016 Long-lived tokens are valid from `created` until `expiry`.
1018 Short-lived tokens expire after 168 hours (7 days) if they are not used.
1019 """
1021 __tablename__ = "sessions"
1022 token = Column(String, primary_key=True)
1024 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1026 # sessions are either "api keys" or "session cookies", otherwise identical
1027 # an api key is put in the authorization header (e.g. "authorization: Bearer <token>")
1028 # a session cookie is set in the "couchers-sesh" cookie (e.g. "cookie: couchers-sesh=<token>")
1029 # when a session is created, it's fixed as one or the other for security reasons
1030 # for api keys to be useful, they should be long lived and have a long expiry
1031 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))
1033 # whether it's a long-lived or short-lived session
1034 long_lived = Column(Boolean, nullable=False)
1036 # the time at which the session was created
1037 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1039 # the expiry of the session: the session *cannot* be refreshed past this
1040 expiry = Column(DateTime(timezone=True), nullable=False, server_default=func.now() + text("interval '730 days'"))
1042 # the time at which the token was invalidated, allows users to delete sessions
1043 deleted = Column(DateTime(timezone=True), nullable=True, default=None)
1045 # the last time this session was used
1046 last_seen = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1048 # count of api calls made with this token/session (if we're updating last_seen, might as well update this too)
1049 api_calls = Column(Integer, nullable=False, default=0)
1051 # details of the browser, if available
1052 # these are from the request creating the session, not used for anything else
1053 ip_address = Column(String, nullable=True)
1054 user_agent = Column(String, nullable=True)
1056 user = relationship("User", backref="sessions")
1058 @hybrid_property
1059 def is_valid(self):
1060 """
1061 It must have been created and not be expired or deleted.
1063 Also, if it's a short lived token, it must have been used in the last 168 hours.
1065 TODO: this probably won't run in python (instance level), only in sql (class level)
1066 """
1067 return (
1068 (self.created <= func.now())
1069 & (self.expiry >= func.now())
1070 & (self.deleted == None)
1071 & (self.long_lived | (func.now() - self.last_seen < text("interval '168 hours'")))
1072 )
1075class Conversation(Base):
1076 """
1077 Conversation brings together the different types of message/conversation types
1078 """
1080 __tablename__ = "conversations"
1082 id = Column(BigInteger, primary_key=True)
1083 # timezone should always be UTC
1084 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1086 def __repr__(self):
1087 return f"Conversation(id={self.id}, created={self.created})"
1090class GroupChat(Base):
1091 """
1092 Group chat
1093 """
1095 __tablename__ = "group_chats"
1097 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1099 title = Column(String, nullable=True)
1100 only_admins_invite = Column(Boolean, nullable=False, default=True)
1101 creator_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1102 is_dm = Column(Boolean, nullable=False)
1104 conversation = relationship("Conversation", backref="group_chat")
1105 creator = relationship("User", backref="created_group_chats")
1107 def __repr__(self):
1108 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})"
1111class GroupChatRole(enum.Enum):
1112 admin = enum.auto()
1113 participant = enum.auto()
1116class GroupChatSubscription(Base):
1117 """
1118 The recipient of a thread and information about when they joined/left/etc.
1119 """
1121 __tablename__ = "group_chat_subscriptions"
1122 id = Column(BigInteger, primary_key=True)
1124 # TODO: DB constraint on only one user+group_chat combo at a given time
1125 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1126 group_chat_id = Column(ForeignKey("group_chats.id"), nullable=False, index=True)
1128 # timezones should always be UTC
1129 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1130 left = Column(DateTime(timezone=True), nullable=True)
1132 role = Column(Enum(GroupChatRole), nullable=False)
1134 last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1136 # when this chat is muted until, DATETIME_INFINITY for "forever"
1137 muted_until = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_MINUS_INFINITY.isoformat())
1139 user = relationship("User", backref="group_chat_subscriptions")
1140 group_chat = relationship("GroupChat", backref=backref("subscriptions", lazy="dynamic"))
1142 def muted_display(self):
1143 """
1144 Returns (muted, muted_until) display values:
1145 1. If not muted, returns (False, None)
1146 2. If muted forever, returns (True, None)
1147 3. If muted until a given datetime returns (True, dt)
1148 """
1149 if self.muted_until < now():
1150 return (False, None)
1151 elif self.muted_until == DATETIME_INFINITY:
1152 return (True, None)
1153 else:
1154 return (True, self.muted_until)
1156 @hybrid_property
1157 def is_muted(self):
1158 return self.muted_until > func.now()
1160 def __repr__(self):
1161 return f"GroupChatSubscription(id={self.id}, user={self.user}, joined={self.joined}, left={self.left}, role={self.role}, group_chat={self.group_chat})"
1164class MessageType(enum.Enum):
1165 text = enum.auto()
1166 # e.g.
1167 # image =
1168 # emoji =
1169 # ...
1170 chat_created = enum.auto()
1171 chat_edited = enum.auto()
1172 user_invited = enum.auto()
1173 user_left = enum.auto()
1174 user_made_admin = enum.auto()
1175 user_removed_admin = enum.auto() # RemoveGroupChatAdmin: remove admin permission from a user in group chat
1176 host_request_status_changed = enum.auto()
1177 user_removed = enum.auto() # user is removed from group chat by amdin RemoveGroupChatUser
1180class HostRequestStatus(enum.Enum):
1181 pending = enum.auto()
1182 accepted = enum.auto()
1183 rejected = enum.auto()
1184 confirmed = enum.auto()
1185 cancelled = enum.auto()
1188class Message(Base):
1189 """
1190 A message.
1192 If message_type = text, then the message is a normal text message, otherwise, it's a special control message.
1193 """
1195 __tablename__ = "messages"
1197 id = Column(BigInteger, primary_key=True)
1199 # which conversation the message belongs in
1200 conversation_id = Column(ForeignKey("conversations.id"), nullable=False, index=True)
1202 # the user that sent the message/command
1203 author_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1205 # the message type, "text" is a text message, otherwise a "control message"
1206 message_type = Column(Enum(MessageType), nullable=False)
1208 # the target if a control message and requires target, e.g. if inviting a user, the user invited is the target
1209 target_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1211 # time sent, timezone should always be UTC
1212 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1214 # the plain-text message text if not control
1215 text = Column(String, nullable=True)
1217 # the new host request status if the message type is host_request_status_changed
1218 host_request_status_target = Column(Enum(HostRequestStatus), nullable=True)
1220 conversation = relationship("Conversation", backref="messages", order_by="Message.time.desc()")
1221 author = relationship("User", foreign_keys="Message.author_id")
1222 target = relationship("User", foreign_keys="Message.target_id")
1224 @property
1225 def is_normal_message(self):
1226 """
1227 There's only one normal type atm, text
1228 """
1229 return self.message_type == MessageType.text
1231 def __repr__(self):
1232 return f"Message(id={self.id}, time={self.time}, text={self.text}, author={self.author}, conversation={self.conversation})"
1235class ContentReport(Base):
1236 """
1237 A piece of content reported to admins
1238 """
1240 __tablename__ = "content_reports"
1242 id = Column(BigInteger, primary_key=True)
1244 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1246 # the user who reported or flagged the content
1247 reporting_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1249 # reason, e.g. spam, inappropriate, etc
1250 reason = Column(String, nullable=False)
1251 # a short description
1252 description = Column(String, nullable=False)
1254 # a reference to the content, see //docs/content_ref.md
1255 content_ref = Column(String, nullable=False)
1256 # the author of the content (e.g. the user who wrote the comment itself)
1257 author_user_id = Column(ForeignKey("users.id"), nullable=False)
1259 # details of the browser, if available
1260 user_agent = Column(String, nullable=False)
1261 # the URL the user was on when reporting the content
1262 page = Column(String, nullable=False)
1264 # see comments above for reporting vs author
1265 reporting_user = relationship("User", foreign_keys="ContentReport.reporting_user_id")
1266 author_user = relationship("User", foreign_keys="ContentReport.author_user_id")
1269class Email(Base):
1270 """
1271 Table of all dispatched emails for debugging purposes, etc.
1272 """
1274 __tablename__ = "emails"
1276 id = Column(String, primary_key=True)
1278 # timezone should always be UTC
1279 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1281 sender_name = Column(String, nullable=False)
1282 sender_email = Column(String, nullable=False)
1284 recipient = Column(String, nullable=False)
1285 subject = Column(String, nullable=False)
1287 plain = Column(String, nullable=False)
1288 html = Column(String, nullable=False)
1290 list_unsubscribe_header = Column(String, nullable=True)
1291 source_data = Column(String, nullable=True)
1294class SMS(Base):
1295 """
1296 Table of all sent SMSs for debugging purposes, etc.
1297 """
1299 __tablename__ = "smss"
1301 id = Column(BigInteger, primary_key=True)
1303 # timezone should always be UTC
1304 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1305 # AWS message id
1306 message_id = Column(String, nullable=False)
1308 # the SMS sender ID sent to AWS, name that the SMS appears to come from
1309 sms_sender_id = Column(String, nullable=False)
1310 number = Column(String, nullable=False)
1311 message = Column(String, nullable=False)
1314class HostRequest(Base):
1315 """
1316 A request to stay with a host
1317 """
1319 __tablename__ = "host_requests"
1321 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1322 surfer_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1323 host_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1325 # TODO: proper timezone handling
1326 timezone = "Etc/UTC"
1328 # dates in the timezone above
1329 from_date = Column(Date, nullable=False)
1330 to_date = Column(Date, nullable=False)
1332 # timezone aware start and end times of the request, can be compared to now()
1333 start_time = column_property(date_in_timezone(from_date, timezone))
1334 end_time = column_property(date_in_timezone(to_date, timezone) + text("interval '1 days'"))
1335 start_time_to_write_reference = column_property(date_in_timezone(to_date, timezone))
1336 # notice 1 day for midnight at the *end of the day*, then 14 days to write a ref
1337 end_time_to_write_reference = column_property(date_in_timezone(to_date, timezone) + text("interval '15 days'"))
1339 status = Column(Enum(HostRequestStatus), nullable=False)
1341 host_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1342 surfer_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1344 # number of reference reminders sent out
1345 host_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1346 surfer_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1348 # reason why the host/surfer marked that they didn't meet up
1349 # if null then they haven't marked it such
1350 host_reason_didnt_meetup = Column(String, nullable=True)
1351 surfer_reason_didnt_meetup = Column(String, nullable=True)
1353 surfer = relationship("User", backref="host_requests_sent", foreign_keys="HostRequest.surfer_user_id")
1354 host = relationship("User", backref="host_requests_received", foreign_keys="HostRequest.host_user_id")
1355 conversation = relationship("Conversation")
1357 __table_args__ = (
1358 # allows fast lookup as to whether they didn't meet up
1359 Index(
1360 "ix_host_requests_host_didnt_meetup",
1361 host_reason_didnt_meetup != None,
1362 ),
1363 Index(
1364 "ix_host_requests_surfer_didnt_meetup",
1365 surfer_reason_didnt_meetup != None,
1366 ),
1367 )
1369 @hybrid_property
1370 def can_write_reference(self):
1371 return (
1372 ((self.status == HostRequestStatus.confirmed) | (self.status == HostRequestStatus.accepted))
1373 & (now() >= self.start_time_to_write_reference)
1374 & (now() <= self.end_time_to_write_reference)
1375 )
1377 @can_write_reference.expression
1378 def can_write_reference(cls):
1379 return (
1380 ((cls.status == HostRequestStatus.confirmed) | (cls.status == HostRequestStatus.accepted))
1381 & (func.now() >= cls.start_time_to_write_reference)
1382 & (func.now() <= cls.end_time_to_write_reference)
1383 )
1385 def __repr__(self):
1386 return f"HostRequest(id={self.conversation_id}, surfer_user_id={self.surfer_user_id}, host_user_id={self.host_user_id}...)"
1389class ReferenceType(enum.Enum):
1390 friend = enum.auto()
1391 surfed = enum.auto() # The "from" user surfed with the "to" user
1392 hosted = enum.auto() # The "from" user hosted the "to" user
1395class Reference(Base):
1396 """
1397 Reference from one user to another
1398 """
1400 __tablename__ = "references"
1402 id = Column(BigInteger, primary_key=True)
1403 # timezone should always be UTC
1404 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1406 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1407 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1409 reference_type = Column(Enum(ReferenceType), nullable=False)
1411 host_request_id = Column(ForeignKey("host_requests.id"), nullable=True)
1413 text = Column(String, nullable=False) # plain text
1414 # text that's only visible to mods
1415 private_text = Column(String, nullable=True) # plain text
1417 rating = Column(Float, nullable=False)
1418 was_appropriate = Column(Boolean, nullable=False)
1420 from_user = relationship("User", backref="references_from", foreign_keys="Reference.from_user_id")
1421 to_user = relationship("User", backref="references_to", foreign_keys="Reference.to_user_id")
1423 host_request = relationship("HostRequest", backref="references")
1425 __table_args__ = (
1426 # Rating must be between 0 and 1, inclusive
1427 CheckConstraint(
1428 "rating BETWEEN 0 AND 1",
1429 name="rating_between_0_and_1",
1430 ),
1431 # Has host_request_id or it's a friend reference
1432 CheckConstraint(
1433 "(host_request_id IS NOT NULL) <> (reference_type = 'friend')",
1434 name="host_request_id_xor_friend_reference",
1435 ),
1436 # Each user can leave at most one friend reference to another user
1437 Index(
1438 "ix_references_unique_friend_reference",
1439 from_user_id,
1440 to_user_id,
1441 reference_type,
1442 unique=True,
1443 postgresql_where=(reference_type == ReferenceType.friend),
1444 ),
1445 # Each user can leave at most one reference to another user for each stay
1446 Index(
1447 "ix_references_unique_per_host_request",
1448 from_user_id,
1449 to_user_id,
1450 host_request_id,
1451 unique=True,
1452 postgresql_where=(host_request_id != None),
1453 ),
1454 )
1456 @property
1457 def should_report(self):
1458 """
1459 If this evaluates to true, we send a report to the moderation team.
1460 """
1461 return self.rating <= 0.4 or not self.was_appropriate or self.private_text
1464class InitiatedUpload(Base):
1465 """
1466 Started downloads, not necessarily complete yet.
1467 """
1469 __tablename__ = "initiated_uploads"
1471 key = Column(String, primary_key=True)
1473 # timezones should always be UTC
1474 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1475 expiry = Column(DateTime(timezone=True), nullable=False)
1477 initiator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1479 initiator_user = relationship("User")
1481 @hybrid_property
1482 def is_valid(self):
1483 return (self.created <= func.now()) & (self.expiry >= func.now())
1486class Upload(Base):
1487 """
1488 Completed uploads.
1489 """
1491 __tablename__ = "uploads"
1492 key = Column(String, primary_key=True)
1494 filename = Column(String, nullable=False)
1495 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1496 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1498 # photo credit, etc
1499 credit = Column(String, nullable=True)
1501 creator_user = relationship("User", backref="uploads", foreign_keys="Upload.creator_user_id")
1503 def _url(self, size):
1504 return urls.media_url(filename=self.filename, size=size)
1506 @property
1507 def thumbnail_url(self):
1508 return self._url("thumbnail")
1510 @property
1511 def full_url(self):
1512 return self._url("full")
1515communities_seq = Sequence("communities_seq")
1518class Node(Base):
1519 """
1520 Node, i.e. geographical subdivision of the world
1522 Administered by the official cluster
1523 """
1525 __tablename__ = "nodes"
1527 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1529 # name and description come from official cluster
1530 parent_node_id = Column(ForeignKey("nodes.id"), nullable=True, index=True)
1531 geom = deferred(Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False))
1532 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1534 parent_node = relationship("Node", backref="child_nodes", remote_side="Node.id")
1536 contained_users = relationship(
1537 "User",
1538 primaryjoin="func.ST_Contains(foreign(Node.geom), User.geom).as_comparison(1, 2)",
1539 viewonly=True,
1540 uselist=True,
1541 )
1543 contained_user_ids = association_proxy("contained_users", "id")
1546class Cluster(Base):
1547 """
1548 Cluster, administered grouping of content
1549 """
1551 __tablename__ = "clusters"
1553 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1554 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1555 name = Column(String, nullable=False)
1556 # short description
1557 description = Column(String, nullable=False)
1558 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1560 is_official_cluster = Column(Boolean, nullable=False, default=False)
1562 slug = column_property(func.slugify(name))
1564 official_cluster_for_node = relationship(
1565 "Node",
1566 primaryjoin="and_(Cluster.parent_node_id == Node.id, Cluster.is_official_cluster)",
1567 backref=backref("official_cluster", uselist=False),
1568 uselist=False,
1569 viewonly=True,
1570 )
1572 parent_node = relationship(
1573 "Node", backref="child_clusters", remote_side="Node.id", foreign_keys="Cluster.parent_node_id"
1574 )
1576 nodes = relationship("Cluster", backref="clusters", secondary="node_cluster_associations", viewonly=True)
1577 # all pages
1578 pages = relationship(
1579 "Page", backref="clusters", secondary="cluster_page_associations", lazy="dynamic", viewonly=True
1580 )
1581 events = relationship("Event", backref="clusters", secondary="cluster_event_associations", viewonly=True)
1582 discussions = relationship(
1583 "Discussion", backref="clusters", secondary="cluster_discussion_associations", viewonly=True
1584 )
1586 # includes also admins
1587 members = relationship(
1588 "User",
1589 lazy="dynamic",
1590 backref="cluster_memberships",
1591 secondary="cluster_subscriptions",
1592 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1593 secondaryjoin="User.id == ClusterSubscription.user_id",
1594 viewonly=True,
1595 )
1597 admins = relationship(
1598 "User",
1599 lazy="dynamic",
1600 backref="cluster_adminships",
1601 secondary="cluster_subscriptions",
1602 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1603 secondaryjoin="and_(User.id == ClusterSubscription.user_id, ClusterSubscription.role == 'admin')",
1604 viewonly=True,
1605 )
1607 main_page = relationship(
1608 "Page",
1609 primaryjoin="and_(Cluster.id == Page.owner_cluster_id, Page.type == 'main_page')",
1610 viewonly=True,
1611 uselist=False,
1612 )
1614 @property
1615 def is_leaf(self) -> bool:
1616 """Whether the cluster is a leaf node in the cluster hierarchy."""
1617 return len(self.parent_node.child_nodes) == 0
1619 __table_args__ = (
1620 # Each node can have at most one official cluster
1621 Index(
1622 "ix_clusters_owner_parent_node_id_is_official_cluster",
1623 parent_node_id,
1624 is_official_cluster,
1625 unique=True,
1626 postgresql_where=is_official_cluster,
1627 ),
1628 )
1631class NodeClusterAssociation(Base):
1632 """
1633 NodeClusterAssociation, grouping of nodes
1634 """
1636 __tablename__ = "node_cluster_associations"
1637 __table_args__ = (UniqueConstraint("node_id", "cluster_id"),)
1639 id = Column(BigInteger, primary_key=True)
1641 node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1642 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1644 node = relationship("Node", backref="node_cluster_associations")
1645 cluster = relationship("Cluster", backref="node_cluster_associations")
1648class ClusterRole(enum.Enum):
1649 member = enum.auto()
1650 admin = enum.auto()
1653class ClusterSubscription(Base):
1654 """
1655 ClusterSubscription of a user
1656 """
1658 __tablename__ = "cluster_subscriptions"
1659 __table_args__ = (UniqueConstraint("user_id", "cluster_id"),)
1661 id = Column(BigInteger, primary_key=True)
1663 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1664 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1665 role = Column(Enum(ClusterRole), nullable=False)
1667 user = relationship("User", backref="cluster_subscriptions")
1668 cluster = relationship("Cluster", backref="cluster_subscriptions")
1671class ClusterPageAssociation(Base):
1672 """
1673 pages related to clusters
1674 """
1676 __tablename__ = "cluster_page_associations"
1677 __table_args__ = (UniqueConstraint("page_id", "cluster_id"),)
1679 id = Column(BigInteger, primary_key=True)
1681 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1682 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1684 page = relationship("Page", backref="cluster_page_associations")
1685 cluster = relationship("Cluster", backref="cluster_page_associations")
1688class PageType(enum.Enum):
1689 main_page = enum.auto()
1690 place = enum.auto()
1691 guide = enum.auto()
1694class Page(Base):
1695 """
1696 similar to a wiki page about a community, POI or guide
1697 """
1699 __tablename__ = "pages"
1701 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1703 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1704 type = Column(Enum(PageType), nullable=False)
1705 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1706 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1707 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1709 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
1711 parent_node = relationship("Node", backref="child_pages", remote_side="Node.id", foreign_keys="Page.parent_node_id")
1713 thread = relationship("Thread", backref="page", uselist=False)
1714 creator_user = relationship("User", backref="created_pages", foreign_keys="Page.creator_user_id")
1715 owner_user = relationship("User", backref="owned_pages", foreign_keys="Page.owner_user_id")
1716 owner_cluster = relationship(
1717 "Cluster", backref=backref("owned_pages", lazy="dynamic"), uselist=False, foreign_keys="Page.owner_cluster_id"
1718 )
1720 editors = relationship("User", secondary="page_versions", viewonly=True)
1722 __table_args__ = (
1723 # Only one of owner_user and owner_cluster should be set
1724 CheckConstraint(
1725 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
1726 name="one_owner",
1727 ),
1728 # Only clusters can own main pages
1729 CheckConstraint(
1730 "NOT (owner_cluster_id IS NULL AND type = 'main_page')",
1731 name="main_page_owned_by_cluster",
1732 ),
1733 # Each cluster can have at most one main page
1734 Index(
1735 "ix_pages_owner_cluster_id_type",
1736 owner_cluster_id,
1737 type,
1738 unique=True,
1739 postgresql_where=(type == PageType.main_page),
1740 ),
1741 )
1743 def __repr__(self):
1744 return f"Page({self.id=})"
1747class PageVersion(Base):
1748 """
1749 version of page content
1750 """
1752 __tablename__ = "page_versions"
1754 id = Column(BigInteger, primary_key=True)
1756 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1757 editor_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1758 title = Column(String, nullable=False)
1759 content = Column(String, nullable=False) # CommonMark without images
1760 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
1761 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
1762 # the human-readable address
1763 address = Column(String, nullable=True)
1764 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1766 slug = column_property(func.slugify(title))
1768 page = relationship("Page", backref="versions", order_by="PageVersion.id")
1769 editor_user = relationship("User", backref="edited_pages")
1770 photo = relationship("Upload")
1772 __table_args__ = (
1773 # Geom and address must either both be null or both be set
1774 CheckConstraint(
1775 "(geom IS NULL) = (address IS NULL)",
1776 name="geom_iff_address",
1777 ),
1778 )
1780 @property
1781 def coordinates(self):
1782 # returns (lat, lng) or None
1783 return get_coordinates(self.geom)
1785 def __repr__(self):
1786 return f"PageVersion({self.id=}, {self.page_id=})"
1789class ClusterEventAssociation(Base):
1790 """
1791 events related to clusters
1792 """
1794 __tablename__ = "cluster_event_associations"
1795 __table_args__ = (UniqueConstraint("event_id", "cluster_id"),)
1797 id = Column(BigInteger, primary_key=True)
1799 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1800 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1802 event = relationship("Event", backref="cluster_event_associations")
1803 cluster = relationship("Cluster", backref="cluster_event_associations")
1806class Event(Base):
1807 """
1808 An event is compose of two parts:
1810 * An event template (Event)
1811 * An occurrence (EventOccurrence)
1813 One-off events will have one of each; repeating events will have one Event,
1814 multiple EventOccurrences, one for each time the event happens.
1815 """
1817 __tablename__ = "events"
1819 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1820 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1822 title = Column(String, nullable=False)
1824 slug = column_property(func.slugify(title))
1826 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1827 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1828 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1829 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1830 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
1832 parent_node = relationship(
1833 "Node", backref="child_events", remote_side="Node.id", foreign_keys="Event.parent_node_id"
1834 )
1835 thread = relationship("Thread", backref="event", uselist=False)
1836 subscribers = relationship(
1837 "User", backref="subscribed_events", secondary="event_subscriptions", lazy="dynamic", viewonly=True
1838 )
1839 organizers = relationship(
1840 "User", backref="organized_events", secondary="event_organizers", lazy="dynamic", viewonly=True
1841 )
1842 creator_user = relationship("User", backref="created_events", foreign_keys="Event.creator_user_id")
1843 owner_user = relationship("User", backref="owned_events", foreign_keys="Event.owner_user_id")
1844 owner_cluster = relationship(
1845 "Cluster",
1846 backref=backref("owned_events", lazy="dynamic"),
1847 uselist=False,
1848 foreign_keys="Event.owner_cluster_id",
1849 )
1851 __table_args__ = (
1852 # Only one of owner_user and owner_cluster should be set
1853 CheckConstraint(
1854 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
1855 name="one_owner",
1856 ),
1857 )
1860class EventOccurrence(Base):
1861 __tablename__ = "event_occurrences"
1863 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1864 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1866 # the user that created this particular occurrence of a repeating event (same as event.creator_user_id if single event)
1867 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1868 content = Column(String, nullable=False) # CommonMark without images
1869 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
1871 is_cancelled = Column(Boolean, nullable=False, default=False, server_default=text("false"))
1872 is_deleted = Column(Boolean, nullable=False, default=False, server_default=text("false"))
1874 # a null geom is an online-only event
1875 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
1876 # physical address, iff geom is not null
1877 address = Column(String, nullable=True)
1878 # videoconferencing link, etc, must be specified if no geom, otherwise optional
1879 link = Column(String, nullable=True)
1881 timezone = "Etc/UTC"
1883 # time during which the event takes place; this is a range type (instead of separate start+end times) which
1884 # simplifies database constraints, etc
1885 during = Column(TSTZRANGE, nullable=False)
1887 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1888 last_edited = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1890 creator_user = relationship(
1891 "User", backref="created_event_occurrences", foreign_keys="EventOccurrence.creator_user_id"
1892 )
1893 event = relationship(
1894 "Event",
1895 backref=backref("occurrences", lazy="dynamic"),
1896 remote_side="Event.id",
1897 foreign_keys="EventOccurrence.event_id",
1898 )
1900 photo = relationship("Upload")
1902 __table_args__ = (
1903 # Geom and address go together
1904 CheckConstraint(
1905 # geom and address are either both null or neither of them are null
1906 "(geom IS NULL) = (address IS NULL)",
1907 name="geom_iff_address",
1908 ),
1909 # Online-only events need a link, note that online events may also have a link
1910 CheckConstraint(
1911 # exactly oen of geom or link is non-null
1912 "(geom IS NULL) <> (link IS NULL)",
1913 name="link_or_geom",
1914 ),
1915 # Can't have overlapping occurrences in the same Event
1916 ExcludeConstraint(("event_id", "="), ("during", "&&"), name="event_occurrences_event_id_during_excl"),
1917 )
1919 @property
1920 def coordinates(self):
1921 # returns (lat, lng) or None
1922 return get_coordinates(self.geom)
1924 @hybrid_property
1925 def start_time(self):
1926 return self.during.lower
1928 @start_time.expression
1929 def start_time(cls):
1930 return func.lower(cls.during)
1932 @hybrid_property
1933 def end_time(self):
1934 return self.during.upper
1936 @end_time.expression
1937 def end_time(cls):
1938 return func.upper(cls.during)
1941class EventSubscription(Base):
1942 """
1943 Users' subscriptions to events
1944 """
1946 __tablename__ = "event_subscriptions"
1947 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
1949 id = Column(BigInteger, primary_key=True)
1951 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1952 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1953 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1955 user = relationship("User")
1956 event = relationship("Event")
1959class EventOrganizer(Base):
1960 """
1961 Organizers for events
1962 """
1964 __tablename__ = "event_organizers"
1965 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
1967 id = Column(BigInteger, primary_key=True)
1969 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1970 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1971 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1973 user = relationship("User")
1974 event = relationship("Event")
1977class AttendeeStatus(enum.Enum):
1978 going = enum.auto()
1979 maybe = enum.auto()
1982class EventOccurrenceAttendee(Base):
1983 """
1984 Attendees for events
1985 """
1987 __tablename__ = "event_occurrence_attendees"
1988 __table_args__ = (UniqueConstraint("occurrence_id", "user_id"),)
1990 id = Column(BigInteger, primary_key=True)
1992 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1993 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
1994 responded = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1995 attendee_status = Column(Enum(AttendeeStatus), nullable=False)
1997 user = relationship("User")
1998 occurrence = relationship("EventOccurrence", backref=backref("attendances", lazy="dynamic"))
2001class EventCommunityInviteRequest(Base):
2002 """
2003 Requests to send out invitation notifications/emails to the community for a given event occurrence
2004 """
2006 __tablename__ = "event_community_invite_requests"
2008 id = Column(BigInteger, primary_key=True)
2010 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
2011 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2013 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2015 decided = Column(DateTime(timezone=True), nullable=True)
2016 decided_by_user_id = Column(ForeignKey("users.id"), nullable=True)
2017 approved = Column(Boolean, nullable=True)
2019 occurrence = relationship("EventOccurrence", backref=backref("community_invite_requests", lazy="dynamic"))
2020 user = relationship("User", foreign_keys="EventCommunityInviteRequest.user_id")
2022 __table_args__ = (
2023 # each user can only request once
2024 UniqueConstraint("occurrence_id", "user_id"),
2025 # each event can only have one notification sent out
2026 Index(
2027 "ix_event_community_invite_requests_unique",
2028 occurrence_id,
2029 unique=True,
2030 postgresql_where=and_(approved.is_not(None), approved == True),
2031 ),
2032 # decided and approved ought to be null simultaneously
2033 CheckConstraint(
2034 "((decided IS NULL) AND (decided_by_user_id IS NULL) AND (approved IS NULL)) OR \
2035 ((decided IS NOT NULL) AND (decided_by_user_id IS NOT NULL) AND (approved IS NOT NULL))",
2036 name="decided_approved",
2037 ),
2038 )
2041class ClusterDiscussionAssociation(Base):
2042 """
2043 discussions related to clusters
2044 """
2046 __tablename__ = "cluster_discussion_associations"
2047 __table_args__ = (UniqueConstraint("discussion_id", "cluster_id"),)
2049 id = Column(BigInteger, primary_key=True)
2051 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2052 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2054 discussion = relationship("Discussion", backref="cluster_discussion_associations")
2055 cluster = relationship("Cluster", backref="cluster_discussion_associations")
2058class Discussion(Base):
2059 """
2060 forum board
2061 """
2063 __tablename__ = "discussions"
2065 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
2067 title = Column(String, nullable=False)
2068 content = Column(String, nullable=False)
2069 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
2070 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2072 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2073 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2075 slug = column_property(func.slugify(title))
2077 thread = relationship("Thread", backref="discussion", uselist=False)
2079 subscribers = relationship("User", backref="discussions", secondary="discussion_subscriptions", viewonly=True)
2081 creator_user = relationship("User", backref="created_discussions", foreign_keys="Discussion.creator_user_id")
2082 owner_cluster = relationship("Cluster", backref=backref("owned_discussions", lazy="dynamic"), uselist=False)
2085class DiscussionSubscription(Base):
2086 """
2087 users subscriptions to discussions
2088 """
2090 __tablename__ = "discussion_subscriptions"
2091 __table_args__ = (UniqueConstraint("discussion_id", "user_id"),)
2093 id = Column(BigInteger, primary_key=True)
2095 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2096 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2097 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2098 left = Column(DateTime(timezone=True), nullable=True)
2100 user = relationship("User", backref="discussion_subscriptions")
2101 discussion = relationship("Discussion", backref="discussion_subscriptions")
2104class Thread(Base):
2105 """
2106 Thread
2107 """
2109 __tablename__ = "threads"
2111 id = Column(BigInteger, primary_key=True)
2113 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2114 deleted = Column(DateTime(timezone=True), nullable=True)
2117class Comment(Base):
2118 """
2119 Comment
2120 """
2122 __tablename__ = "comments"
2124 id = Column(BigInteger, primary_key=True)
2126 thread_id = Column(ForeignKey("threads.id"), nullable=False, index=True)
2127 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2128 content = Column(String, nullable=False) # CommonMark without images
2129 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2130 deleted = Column(DateTime(timezone=True), nullable=True)
2132 thread = relationship("Thread", backref="comments")
2135class Reply(Base):
2136 """
2137 Reply
2138 """
2140 __tablename__ = "replies"
2142 id = Column(BigInteger, primary_key=True)
2144 comment_id = Column(ForeignKey("comments.id"), nullable=False, index=True)
2145 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2146 content = Column(String, nullable=False) # CommonMark without images
2147 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2148 deleted = Column(DateTime(timezone=True), nullable=True)
2150 comment = relationship("Comment", backref="replies")
2153class BackgroundJobState(enum.Enum):
2154 # job is fresh, waiting to be picked off the queue
2155 pending = enum.auto()
2156 # job complete
2157 completed = enum.auto()
2158 # error occured, will be retried
2159 error = enum.auto()
2160 # failed too many times, not retrying anymore
2161 failed = enum.auto()
2164class BackgroundJob(Base):
2165 """
2166 This table implements a queue of background jobs.
2167 """
2169 __tablename__ = "background_jobs"
2171 id = Column(BigInteger, primary_key=True)
2173 # used to discern which function should be triggered to service it
2174 job_type = Column(String, nullable=False)
2175 state = Column(Enum(BackgroundJobState), nullable=False, default=BackgroundJobState.pending)
2177 # time queued
2178 queued = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2180 # time at which we may next attempt it, for implementing exponential backoff
2181 next_attempt_after = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2183 # used to count number of retries for failed jobs
2184 try_count = Column(Integer, nullable=False, default=0)
2186 max_tries = Column(Integer, nullable=False, default=5)
2188 # protobuf encoded job payload
2189 payload = Column(Binary, nullable=False)
2191 # if the job failed, we write that info here
2192 failure_info = Column(String, nullable=True)
2194 __table_args__ = (
2195 # used in looking up background jobs to attempt
2196 # create index on background_jobs(next_attempt_after, (max_tries - try_count)) where state = 'pending' OR state = 'error';
2197 Index(
2198 "ix_background_jobs_lookup",
2199 next_attempt_after,
2200 (max_tries - try_count),
2201 postgresql_where=((state == BackgroundJobState.pending) | (state == BackgroundJobState.error)),
2202 ),
2203 )
2205 @hybrid_property
2206 def ready_for_retry(self):
2207 return (
2208 (self.next_attempt_after <= func.now())
2209 & (self.try_count < self.max_tries)
2210 & ((self.state == BackgroundJobState.pending) | (self.state == BackgroundJobState.error))
2211 )
2213 def __repr__(self):
2214 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})"
2217class NotificationDeliveryType(enum.Enum):
2218 # send push notification to mobile/web
2219 push = enum.auto()
2220 # send individual email immediately
2221 email = enum.auto()
2222 # send in digest
2223 digest = enum.auto()
2226dt = NotificationDeliveryType
2227nd = notification_data_pb2
2229dt_sec = [dt.email, dt.push]
2230dt_all = [dt.email, dt.push, dt.digest]
2233class NotificationTopicAction(enum.Enum):
2234 def __init__(self, topic_action, defaults, user_editable, data_type):
2235 self.topic, self.action = topic_action.split(":")
2236 self.defaults = defaults
2237 # for now user editable == not a security notification
2238 self.user_editable = user_editable
2240 self.data_type = data_type
2242 def unpack(self):
2243 return self.topic, self.action
2245 @property
2246 def display(self):
2247 return f"{self.topic}:{self.action}"
2249 def __str__(self):
2250 return self.display
2252 # topic, action, default delivery types
2253 friend_request__create = ("friend_request:create", dt_all, True, nd.FriendRequestCreate)
2254 friend_request__accept = ("friend_request:accept", dt_all, True, nd.FriendRequestAccept)
2256 # host requests
2257 host_request__create = ("host_request:create", dt_all, True, nd.HostRequestCreate)
2258 host_request__accept = ("host_request:accept", dt_all, True, nd.HostRequestAccept)
2259 host_request__reject = ("host_request:reject", dt_all, True, nd.HostRequestReject)
2260 host_request__confirm = ("host_request:confirm", dt_all, True, nd.HostRequestConfirm)
2261 host_request__cancel = ("host_request:cancel", dt_all, True, nd.HostRequestCancel)
2262 host_request__message = ("host_request:message", [dt.push, dt.digest], True, nd.HostRequestMessage)
2263 host_request__missed_messages = ("host_request:missed_messages", [dt.email], True, nd.HostRequestMissedMessages)
2265 # you receive a friend ref
2266 reference__receive_friend = ("reference:receive_friend", dt_all, True, nd.ReferenceReceiveFriend)
2267 # you receive a reference from ... the host
2268 reference__receive_hosted = ("reference:receive_hosted", dt_all, True, nd.ReferenceReceiveHostRequest)
2269 # ... the surfer
2270 reference__receive_surfed = ("reference:receive_surfed", dt_all, True, nd.ReferenceReceiveHostRequest)
2272 # you hosted
2273 reference__reminder_hosted = ("reference:reminder_hosted", dt_all, True, nd.ReferenceReminder)
2274 # you surfed
2275 reference__reminder_surfed = ("reference:reminder_surfed", dt_all, True, nd.ReferenceReminder)
2277 badge__add = ("badge:add", [dt.push, dt.digest], True, nd.BadgeAdd)
2278 badge__remove = ("badge:remove", [dt.push, dt.digest], True, nd.BadgeRemove)
2280 # group chats
2281 chat__message = ("chat:message", [dt.push, dt.digest], True, nd.ChatMessage)
2282 chat__missed_messages = ("chat:missed_messages", [dt.email], True, nd.ChatMissedMessages)
2284 # events
2285 # approved by mods
2286 event__create_approved = ("event:create_approved", dt_all, True, nd.EventCreate)
2287 # any user creates any event, default to no notifications
2288 event__create_any = ("event:create_any", [], True, nd.EventCreate)
2289 event__update = ("event:update", dt_all, True, nd.EventUpdate)
2290 event__cancel = ("event:cancel", dt_all, True, nd.EventCancel)
2291 event__delete = ("event:delete", dt_all, True, nd.EventDelete)
2292 event__invite_organizer = ("event:invite_organizer", dt_all, True, nd.EventInviteOrganizer)
2294 # account settings
2295 password__change = ("password:change", dt_sec, False, empty_pb2.Empty)
2296 email_address__change = ("email_address:change", dt_sec, False, nd.EmailAddressChange)
2297 email_address__verify = ("email_address:verify", dt_sec, False, empty_pb2.Empty)
2298 phone_number__change = ("phone_number:change", dt_sec, False, nd.PhoneNumberChange)
2299 phone_number__verify = ("phone_number:verify", dt_sec, False, nd.PhoneNumberVerify)
2300 # reset password
2301 password_reset__start = ("password_reset:start", dt_sec, False, nd.PasswordResetStart)
2302 password_reset__complete = ("password_reset:complete", dt_sec, False, empty_pb2.Empty)
2304 # account deletion
2305 account_deletion__start = ("account_deletion:start", dt_sec, False, nd.AccountDeletionStart)
2306 # no more pushing to do
2307 account_deletion__complete = ("account_deletion:complete", dt_sec, False, nd.AccountDeletionComplete)
2308 # undeleted
2309 account_deletion__recovered = ("account_deletion:recovered", dt_sec, False, empty_pb2.Empty)
2311 # admin actions
2312 gender__change = ("gender:change", dt_sec, False, nd.GenderChange)
2313 birthdate__change = ("birthdate:change", dt_sec, False, nd.BirthdateChange)
2314 api_key__create = ("api_key:create", dt_sec, False, nd.ApiKeyCreate)
2316 donation__received = ("donation:received", dt_sec, True, nd.DonationReceived)
2318 onboarding__reminder = ("onboarding:reminder", dt_sec, True, empty_pb2.Empty)
2320 modnote__create = ("modnote:create", dt_sec, False, empty_pb2.Empty)
2322 verification__sv_fail = ("verification:sv_fail", dt_sec, False, nd.VerificationSVFail)
2323 verification__sv_success = ("verification:sv_success", dt_sec, False, empty_pb2.Empty)
2326class NotificationPreference(Base):
2327 __tablename__ = "notification_preferences"
2329 id = Column(BigInteger, primary_key=True)
2330 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2332 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2333 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2334 deliver = Column(Boolean, nullable=False)
2336 user = relationship("User", foreign_keys="NotificationPreference.user_id")
2338 __table_args__ = (UniqueConstraint("user_id", "topic_action", "delivery_type"),)
2341class Notification(Base):
2342 """
2343 Table for accumulating notifications until it is time to send email digest
2344 """
2346 __tablename__ = "notifications"
2348 id = Column(BigInteger, primary_key=True)
2349 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2351 # recipient user id
2352 user_id = Column(ForeignKey("users.id"), nullable=False)
2354 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2355 key = Column(String, nullable=False)
2357 data = Column(Binary, nullable=False)
2359 user = relationship("User", foreign_keys="Notification.user_id")
2361 __table_args__ = (
2362 # used in looking up which notifications need delivery
2363 Index(
2364 "ix_notifications_created",
2365 created,
2366 ),
2367 )
2369 @property
2370 def topic(self):
2371 return self.topic_action.topic
2373 @property
2374 def action(self):
2375 return self.topic_action.action
2378class NotificationDelivery(Base):
2379 __tablename__ = "notification_deliveries"
2381 id = Column(BigInteger, primary_key=True)
2382 notification_id = Column(ForeignKey("notifications.id"), nullable=False, index=True)
2383 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2384 delivered = Column(DateTime(timezone=True), nullable=True)
2385 read = Column(DateTime(timezone=True), nullable=True)
2386 # todo: enum of "phone, web, digest"
2387 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2388 # todo: device id
2389 # todo: receipt id, etc
2390 notification = relationship("Notification", foreign_keys="NotificationDelivery.notification_id")
2392 __table_args__ = (
2393 UniqueConstraint("notification_id", "delivery_type"),
2394 # used in looking up which notifications need delivery
2395 Index(
2396 "ix_notification_deliveries_delivery_type",
2397 delivery_type,
2398 postgresql_where=(delivered != None),
2399 ),
2400 Index(
2401 "ix_notification_deliveries_dt_ni_dnull",
2402 delivery_type,
2403 notification_id,
2404 delivered == None,
2405 ),
2406 )
2409class PushNotificationSubscription(Base):
2410 __tablename__ = "push_notification_subscriptions"
2412 id = Column(BigInteger, primary_key=True)
2413 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2415 # which user this is connected to
2416 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2418 # these come from https://developer.mozilla.org/en-US/docs/Web/API/PushSubscription
2419 # the endpoint
2420 endpoint = Column(String, nullable=False)
2421 # the "auth" key
2422 auth_key = Column(Binary, nullable=False)
2423 # the "p256dh" key
2424 p256dh_key = Column(Binary, nullable=False)
2426 full_subscription_info = Column(String, nullable=False)
2428 # the browse user-agent, so we can tell the user what browser notifications are going to
2429 user_agent = Column(String, nullable=True)
2431 # when it was disabled
2432 disabled_at = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_INFINITY.isoformat())
2434 user = relationship("User")
2437class PushNotificationDeliveryAttempt(Base):
2438 __tablename__ = "push_notification_delivery_attempt"
2440 id = Column(BigInteger, primary_key=True)
2441 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2443 push_notification_subscription_id = Column(
2444 ForeignKey("push_notification_subscriptions.id"), nullable=False, index=True
2445 )
2447 success = Column(Boolean, nullable=False)
2448 # the HTTP status code, 201 is success
2449 status_code = Column(Integer, nullable=False)
2451 # can be null if it was a success
2452 response = Column(String, nullable=True)
2454 push_notification_subscription = relationship("PushNotificationSubscription")
2457class Language(Base):
2458 """
2459 Table of allowed languages (a subset of ISO639-3)
2460 """
2462 __tablename__ = "languages"
2464 # ISO639-3 language code, in lowercase, e.g. fin, eng
2465 code = Column(String(3), primary_key=True)
2467 # the english name
2468 name = Column(String, nullable=False, unique=True)
2471class Region(Base):
2472 """
2473 Table of regions
2474 """
2476 __tablename__ = "regions"
2478 # iso 3166-1 alpha3 code in uppercase, e.g. FIN, USA
2479 code = Column(String(3), primary_key=True)
2481 # the name, e.g. Finland, United States
2482 # this is the display name in English, should be the "common name", not "Republic of Finland"
2483 name = Column(String, nullable=False, unique=True)
2486class UserBlock(Base):
2487 """
2488 Table of blocked users
2489 """
2491 __tablename__ = "user_blocks"
2492 __table_args__ = (UniqueConstraint("blocking_user_id", "blocked_user_id"),)
2494 id = Column(BigInteger, primary_key=True)
2496 blocking_user_id = Column(ForeignKey("users.id"), nullable=False)
2497 blocked_user_id = Column(ForeignKey("users.id"), nullable=False)
2498 time_blocked = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2500 blocking_user = relationship("User", foreign_keys="UserBlock.blocking_user_id")
2501 blocked_user = relationship("User", foreign_keys="UserBlock.blocked_user_id")
2504class APICall(Base):
2505 """
2506 API call logs
2507 """
2509 __tablename__ = "api_calls"
2510 __table_args__ = {"schema": "logging"}
2512 id = Column(BigInteger, primary_key=True)
2514 # whether the call was made using an api key or session cookies
2515 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))
2517 # backend version (normally e.g. develop-31469e3), allows us to figure out which proto definitions were used
2518 # note that `default` is a python side default, not hardcoded into DB schema
2519 version = Column(String, nullable=False, default=config["VERSION"])
2521 # approximate time of the call
2522 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2524 # the method call name, e.g. "/org.couchers.api.core.API/ListFriends"
2525 method = Column(String, nullable=False)
2527 # gRPC status code name, e.g. FAILED_PRECONDITION, None if success
2528 status_code = Column(String, nullable=True)
2530 # handler duration (excluding serialization, etc)
2531 duration = Column(Float, nullable=False)
2533 # user_id of caller, None means not logged in
2534 user_id = Column(BigInteger, nullable=True)
2536 # sanitized request bytes
2537 request = Column(Binary, nullable=True)
2539 # sanitized response bytes
2540 response = Column(Binary, nullable=True)
2542 # whether response bytes have been truncated
2543 response_truncated = Column(Boolean, nullable=False, server_default=text("false"))
2545 # the exception traceback, if any
2546 traceback = Column(String, nullable=True)
2548 # human readable perf report
2549 perf_report = Column(String, nullable=True)
2551 # details of the browser, if available
2552 ip_address = Column(String, nullable=True)
2553 user_agent = Column(String, nullable=True)
2556class AccountDeletionReason(Base):
2557 __tablename__ = "account_deletion_reason"
2559 id = Column(BigInteger, primary_key=True)
2560 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2561 user_id = Column(ForeignKey("users.id"), nullable=False)
2562 reason = Column(String, nullable=True)
2564 user = relationship("User")