Coverage for src/couchers/models.py: 99%
1078 statements
« prev ^ index » next coverage.py v7.6.10, created at 2025-03-11 15:27 +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()
84class ParkingDetails(enum.Enum):
85 free_onsite = enum.auto()
86 free_offsite = enum.auto()
87 paid_onsite = enum.auto()
88 paid_offsite = enum.auto()
91class TimezoneArea(Base):
92 __tablename__ = "timezone_areas"
93 id = Column(BigInteger, primary_key=True)
95 tzid = Column(String)
96 geom = Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False)
98 __table_args__ = (
99 Index(
100 "ix_timezone_areas_geom_tzid",
101 geom,
102 tzid,
103 postgresql_using="gist",
104 ),
105 )
108class User(Base):
109 """
110 Basic user and profile details
111 """
113 __tablename__ = "users"
115 id = Column(BigInteger, primary_key=True)
117 username = Column(String, nullable=False, unique=True)
118 email = Column(String, nullable=False, unique=True)
119 # stored in libsodium hash format, can be null for email login
120 hashed_password = Column(Binary, nullable=False)
121 # phone number in E.164 format with leading +, for example "+46701740605"
122 phone = Column(String, nullable=True, server_default=text("NULL"))
123 # language preference -- defaults to empty string
124 ui_language_preference = Column(String, nullable=True, server_default="")
126 # timezones should always be UTC
127 ## location
128 # point describing their location. EPSG4326 is the SRS (spatial ref system, = way to describe a point on earth) used
129 # by GPS, it has the WGS84 geoid with lat/lon
130 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
131 # their display location (displayed to other users), in meters
132 geom_radius = Column(Float, nullable=True)
133 # the display address (text) shown on their profile
134 city = Column(String, nullable=False)
135 # "Grew up in" on profile
136 hometown = Column(String, nullable=True)
138 regions_visited = relationship("Region", secondary="regions_visited", order_by="Region.name")
139 regions_lived = relationship("Region", secondary="regions_lived", order_by="Region.name")
141 timezone = column_property(
142 sa_select(TimezoneArea.tzid).where(func.ST_Contains(TimezoneArea.geom, geom)).limit(1).scalar_subquery(),
143 deferred=True,
144 )
146 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
147 last_active = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
149 # id of the last message that they received a notification about
150 last_notified_message_id = Column(BigInteger, nullable=False, default=0)
151 # same as above for host requests
152 last_notified_request_message_id = Column(BigInteger, nullable=False, server_default=text("0"))
154 # display name
155 name = Column(String, nullable=False)
156 gender = Column(String, nullable=False)
157 pronouns = Column(String, nullable=True)
158 birthdate = Column(Date, nullable=False) # in the timezone of birthplace
160 avatar_key = Column(ForeignKey("uploads.key"), nullable=True)
162 hosting_status = Column(Enum(HostingStatus), nullable=False)
163 meetup_status = Column(Enum(MeetupStatus), nullable=False, server_default="open_to_meetup")
165 # community standing score
166 community_standing = Column(Float, nullable=True)
168 occupation = Column(String, nullable=True) # CommonMark without images
169 education = Column(String, nullable=True) # CommonMark without images
171 # "Who I am" under "About Me" tab
172 about_me = Column(String, nullable=True) # CommonMark without images
173 # "What I do in my free time" under "About Me" tab
174 things_i_like = Column(String, nullable=True) # CommonMark without images
175 # "About my home" under "My Home" tab
176 about_place = Column(String, nullable=True) # CommonMark without images
177 # "Additional information" under "About Me" tab
178 additional_information = Column(String, nullable=True) # CommonMark without images
180 is_banned = Column(Boolean, nullable=False, server_default=text("false"))
181 is_deleted = Column(Boolean, nullable=False, server_default=text("false"))
182 is_superuser = Column(Boolean, nullable=False, server_default=text("false"))
184 # the undelete token allows a user to recover their account for a couple of days after deletion in case it was
185 # accidental or they changed their mind
186 # constraints make sure these are non-null only if is_deleted and that these are null in unison
187 undelete_token = Column(String, nullable=True)
188 # validity of the undelete token
189 undelete_until = Column(DateTime(timezone=True), nullable=True)
191 # hosting preferences
192 max_guests = Column(Integer, nullable=True)
193 last_minute = Column(Boolean, nullable=True)
194 has_pets = Column(Boolean, nullable=True)
195 accepts_pets = Column(Boolean, nullable=True)
196 pet_details = Column(String, nullable=True) # CommonMark without images
197 has_kids = Column(Boolean, nullable=True)
198 accepts_kids = Column(Boolean, nullable=True)
199 kid_details = Column(String, nullable=True) # CommonMark without images
200 has_housemates = Column(Boolean, nullable=True)
201 housemate_details = Column(String, nullable=True) # CommonMark without images
202 wheelchair_accessible = Column(Boolean, nullable=True)
203 smoking_allowed = Column(Enum(SmokingLocation), nullable=True)
204 smokes_at_home = Column(Boolean, nullable=True)
205 drinking_allowed = Column(Boolean, nullable=True)
206 drinks_at_home = Column(Boolean, nullable=True)
207 # "Additional information" under "My Home" tab
208 other_host_info = Column(String, nullable=True) # CommonMark without images
210 # "Sleeping privacy" (not long-form text)
211 sleeping_arrangement = Column(Enum(SleepingArrangement), nullable=True)
212 # "Sleeping arrangement" under "My Home" tab
213 sleeping_details = Column(String, nullable=True) # CommonMark without images
214 # "Local area information" under "My Home" tab
215 area = Column(String, nullable=True) # CommonMark without images
216 # "House rules" under "My Home" tab
217 house_rules = Column(String, nullable=True) # CommonMark without images
218 parking = Column(Boolean, nullable=True)
219 parking_details = Column(Enum(ParkingDetails), nullable=True) # CommonMark without images
220 camping_ok = Column(Boolean, nullable=True)
222 accepted_tos = Column(Integer, nullable=False, default=0)
223 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")
224 # whether the user has yet filled in the contributor form
225 filled_contributor_form = Column(Boolean, nullable=False, server_default="false")
227 # number of onboarding emails sent
228 onboarding_emails_sent = Column(Integer, nullable=False, server_default="0")
229 last_onboarding_email_sent = Column(DateTime(timezone=True), nullable=True)
231 # whether we need to sync the user's newsletter preferences with the newsletter server
232 in_sync_with_newsletter = Column(Boolean, nullable=False, server_default="false")
233 # opted out of the newsletter
234 opt_out_of_newsletter = Column(Boolean, nullable=False, server_default="false")
236 # set to null to receive no digests
237 digest_frequency = Column(Interval, nullable=True)
238 last_digest_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)"))
240 # for changing their email
241 new_email = Column(String, nullable=True)
243 new_email_token = Column(String, nullable=True)
244 new_email_token_created = Column(DateTime(timezone=True), nullable=True)
245 new_email_token_expiry = Column(DateTime(timezone=True), nullable=True)
247 recommendation_score = Column(Float, nullable=False, server_default="0")
249 # Columns for verifying their phone number. State chart:
250 # ,-------------------,
251 # | Start |
252 # | phone = None | someone else
253 # ,-----------------, | token = None | verifies ,-----------------------,
254 # | Code Expired | | sent = 1970 or zz | phone xx | Verification Expired |
255 # | phone = xx | time passes | verified = None | <------, | phone = xx |
256 # | token = yy | <------------, | attempts = 0 | | | token = None |
257 # | sent = zz (exp.)| | '-------------------' | | sent = zz |
258 # | verified = None | | V ^ +-----------< | verified = ww (exp.) |
259 # | attempts = 0..2 | >--, | | | ChangePhone("") | | attempts = 0 |
260 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------'
261 # | | | | ChangePhone(xx) | ^ time passes
262 # | | ^ V | |
263 # ,-----------------, | | ,-------------------, | ,-----------------------,
264 # | Too Many | >--' '--< | Code sent | >------+ | Verified |
265 # | phone = xx | | phone = xx | | | phone = xx |
266 # | token = yy | VerifyPhone(wrong)| token = yy | '-----------< | token = None |
267 # | sent = zz | <------+--------< | sent = zz | | sent = zz |
268 # | verified = None | | | verified = None | VerifyPhone(correct) | verified = ww |
269 # | attempts = 3 | '--------> | attempts = 0..2 | >------------------> | attempts = 0 |
270 # '-----------------' '-------------------' '-----------------------'
272 # randomly generated Luhn 6-digit string
273 phone_verification_token = Column(String(6), nullable=True, server_default=text("NULL"))
275 phone_verification_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)"))
276 phone_verification_verified = Column(DateTime(timezone=True), nullable=True, server_default=text("NULL"))
277 phone_verification_attempts = Column(Integer, nullable=False, server_default=text("0"))
279 # the stripe customer identifier if the user has donated to Couchers
280 # e.g. cus_JjoXHttuZopv0t
281 # for new US entity
282 stripe_customer_id = Column(String, nullable=True)
283 # for old AU entity
284 stripe_customer_id_old = Column(String, nullable=True)
286 has_passport_sex_gender_exception = Column(Boolean, nullable=False, server_default=text("false"))
288 # checking for phone verification
289 has_donated = Column(Boolean, nullable=False, server_default=text("false"))
291 # whether this user has all emails turned off
292 do_not_email = Column(Boolean, nullable=False, server_default=text("false"))
294 avatar = relationship("Upload", foreign_keys="User.avatar_key")
296 admin_note = Column(String, nullable=False, server_default=text("''"))
298 age = column_property(func.date_part("year", func.age(birthdate)))
300 __table_args__ = (
301 # Verified phone numbers should be unique
302 Index(
303 "ix_users_unique_phone",
304 phone,
305 unique=True,
306 postgresql_where=phone_verification_verified != None,
307 ),
308 Index(
309 "ix_users_active",
310 id,
311 postgresql_where=~is_banned & ~is_deleted,
312 ),
313 # create index on users(geom, id, username) where not is_banned and not is_deleted and geom is not null;
314 Index(
315 "ix_users_geom_active",
316 geom,
317 id,
318 username,
319 postgresql_where=~is_banned & ~is_deleted & (geom != None),
320 ),
321 # There are two possible states for new_email_token, new_email_token_created, and new_email_token_expiry
322 CheckConstraint(
323 "(new_email_token IS NOT NULL AND new_email_token_created IS NOT NULL AND new_email_token_expiry IS NOT NULL) OR \
324 (new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL)",
325 name="check_new_email_token_state",
326 ),
327 # Whenever a phone number is set, it must either be pending verification or already verified.
328 # Exactly one of the following must always be true: not phone, token, verified.
329 CheckConstraint(
330 "(phone IS NULL)::int + (phone_verification_verified IS NOT NULL)::int + (phone_verification_token IS NOT NULL)::int = 1",
331 name="phone_verified_conditions",
332 ),
333 # Email must match our regex
334 CheckConstraint(
335 f"email ~ '{EMAIL_REGEX}'",
336 name="valid_email",
337 ),
338 # Undelete token + time are coupled: either both null or neither; and if they're not null then the account is deleted
339 CheckConstraint(
340 "((undelete_token IS NULL) = (undelete_until IS NULL)) AND ((undelete_token IS NULL) OR is_deleted)",
341 name="undelete_nullity",
342 ),
343 # If the user disabled all emails, then they can't host or meet up
344 CheckConstraint(
345 "(do_not_email IS FALSE) OR ((hosting_status = 'cant_host') AND (meetup_status = 'does_not_want_to_meetup'))",
346 name="do_not_email_inactive",
347 ),
348 )
350 @hybrid_property
351 def has_completed_profile(self):
352 return self.avatar_key is not None and self.about_me is not None and len(self.about_me) >= 150
354 @has_completed_profile.expression
355 def has_completed_profile(cls):
356 return (cls.avatar_key != None) & (func.character_length(cls.about_me) >= 150)
358 @hybrid_property
359 def is_jailed(self):
360 return (
361 (self.accepted_tos < TOS_VERSION)
362 | (self.accepted_community_guidelines < GUIDELINES_VERSION)
363 | self.is_missing_location
364 | (self.mod_notes.where(ModNote.is_pending).count() > 0)
365 )
367 @hybrid_property
368 def is_missing_location(self):
369 return (self.geom == None) | (self.geom_radius == None)
371 @hybrid_property
372 def is_visible(self):
373 return ~(self.is_banned | self.is_deleted)
375 @property
376 def coordinates(self):
377 return get_coordinates(self.geom)
379 @property
380 def display_joined(self):
381 """
382 Returns the last active time rounded down to the nearest hour.
383 """
384 return self.joined.replace(minute=0, second=0, microsecond=0)
386 @property
387 def display_last_active(self):
388 """
389 Returns the last active time rounded down whatever is the "last active" coarsening.
390 """
391 return last_active_coarsen(self.last_active)
393 @hybrid_property
394 def phone_is_verified(self):
395 return (
396 self.phone_verification_verified is not None
397 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
398 )
400 @phone_is_verified.expression
401 def phone_is_verified(cls):
402 return (cls.phone_verification_verified != None) & (
403 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
404 )
406 @hybrid_property
407 def phone_code_expired(self):
408 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME
410 def __repr__(self):
411 return f"User(id={self.id}, email={self.email}, username={self.username})"
414class UserBadge(Base):
415 """
416 A badge on a user's profile
417 """
419 __tablename__ = "user_badges"
420 __table_args__ = (UniqueConstraint("user_id", "badge_id"),)
422 id = Column(BigInteger, primary_key=True)
424 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
425 # corresponds to "id" in badges.json
426 badge_id = Column(String, nullable=False, index=True)
428 # take this with a grain of salt, someone may get then lose a badge for whatever reason
429 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
431 user = relationship("User", backref="badges")
434class StrongVerificationAttemptStatus(enum.Enum):
435 ## full data states
436 # completed, this now provides verification for a user
437 succeeded = enum.auto()
439 ## no data states
440 # in progress: waiting for the user to scan the Iris code or open the app
441 in_progress_waiting_on_user_to_open_app = enum.auto()
442 # in progress: waiting for the user to scan MRZ or NFC/chip
443 in_progress_waiting_on_user_in_app = enum.auto()
444 # in progress, waiting for backend to pull verification data
445 in_progress_waiting_on_backend = enum.auto()
446 # failed, no data
447 failed = enum.auto()
449 # duplicate, at our end, has data
450 duplicate = enum.auto()
452 ## minimal data states
453 # the data, except minimal deduplication data, was deleted
454 deleted = enum.auto()
457class PassportSex(enum.Enum):
458 """
459 We don't care about sex, we use gender on the platform. But passports apparently do.
460 """
462 male = enum.auto()
463 female = enum.auto()
464 unspecified = enum.auto()
467class StrongVerificationAttempt(Base):
468 """
469 An attempt to perform strong verification
470 """
472 __tablename__ = "strong_verification_attempts"
474 # our verification id
475 id = Column(BigInteger, primary_key=True)
477 # this is returned in the callback, and we look up the attempt via this
478 verification_attempt_token = Column(String, nullable=False, unique=True)
480 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
481 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
483 status = Column(
484 Enum(StrongVerificationAttemptStatus),
485 nullable=False,
486 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app,
487 )
489 ## full data
490 has_full_data = Column(Boolean, nullable=False, default=False)
491 # the data returned from iris, encrypted with a public key whose private key is kept offline
492 passport_encrypted_data = Column(Binary, nullable=True)
493 passport_date_of_birth = Column(Date, nullable=True)
494 passport_sex = Column(Enum(PassportSex), nullable=True)
496 ## minimal data: this will not be deleted
497 has_minimal_data = Column(Boolean, nullable=False, default=False)
498 passport_expiry_date = Column(Date, nullable=True)
499 passport_nationality = Column(String, nullable=True)
500 # last three characters of the passport number
501 passport_last_three_document_chars = Column(String, nullable=True)
503 iris_token = Column(String, nullable=False, unique=True)
504 iris_session_id = Column(BigInteger, nullable=False, unique=True)
506 passport_expiry_datetime = column_property(date_in_timezone(passport_expiry_date, "Etc/UTC"))
508 user = relationship("User")
510 @hybrid_property
511 def is_valid(self):
512 """
513 This only checks whether the attempt is a success and the passport is not expired, use `has_strong_verification` for full check
514 """
515 return (self.status == StrongVerificationAttemptStatus.succeeded) and (self.passport_expiry_datetime >= now())
517 @is_valid.expression
518 def is_valid(cls):
519 return (cls.status == StrongVerificationAttemptStatus.succeeded) & (
520 func.coalesce(cls.passport_expiry_datetime >= func.now(), False)
521 )
523 @hybrid_property
524 def is_visible(self):
525 return self.status != StrongVerificationAttemptStatus.deleted
527 @hybrid_method
528 def _raw_birthdate_match(self, user):
529 """Does not check whether the SV attempt itself is not expired"""
530 return self.passport_date_of_birth == user.birthdate
532 @hybrid_method
533 def matches_birthdate(self, user):
534 return self.is_valid & self._raw_birthdate_match(user)
536 @hybrid_method
537 def _raw_gender_match(self, user):
538 """Does not check whether the SV attempt itself is not expired"""
539 return (
540 ((user.gender == "Woman") & (self.passport_sex == PassportSex.female))
541 | ((user.gender == "Man") & (self.passport_sex == PassportSex.male))
542 | (self.passport_sex == PassportSex.unspecified)
543 | (user.has_passport_sex_gender_exception == True)
544 )
546 @hybrid_method
547 def matches_gender(self, user):
548 return self.is_valid & self._raw_gender_match(user)
550 @hybrid_method
551 def has_strong_verification(self, user):
552 return self.is_valid & self._raw_birthdate_match(user) & self._raw_gender_match(user)
554 __table_args__ = (
555 # used to look up verification status for a user
556 Index(
557 "ix_strong_verification_attempts_current",
558 user_id,
559 passport_expiry_date,
560 postgresql_where=status == StrongVerificationAttemptStatus.succeeded,
561 ),
562 # each passport can be verified only once
563 Index(
564 "ix_strong_verification_attempts_unique_succeeded",
565 passport_expiry_date,
566 passport_nationality,
567 passport_last_three_document_chars,
568 unique=True,
569 postgresql_where=(
570 (status == StrongVerificationAttemptStatus.succeeded)
571 | (status == StrongVerificationAttemptStatus.deleted)
572 ),
573 ),
574 # full data check
575 CheckConstraint(
576 "(has_full_data IS TRUE AND passport_encrypted_data IS NOT NULL AND passport_date_of_birth IS NOT NULL) OR \
577 (has_full_data IS FALSE AND passport_encrypted_data IS NULL AND passport_date_of_birth IS NULL)",
578 name="full_data_status",
579 ),
580 # minimal data check
581 CheckConstraint(
582 "(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 \
583 (has_minimal_data IS FALSE AND passport_expiry_date IS NULL AND passport_nationality IS NULL AND passport_last_three_document_chars IS NULL)",
584 name="minimal_data_status",
585 ),
586 # note on implications: p => q iff ~p OR q
587 # full data implies minimal data, has_minimal_data => has_full_data
588 CheckConstraint(
589 "(has_full_data IS FALSE) OR (has_minimal_data IS TRUE)",
590 name="full_data_implies_minimal_data",
591 ),
592 # succeeded implies full data
593 CheckConstraint(
594 "(NOT (status = 'succeeded')) OR (has_full_data IS TRUE)",
595 name="succeeded_implies_full_data",
596 ),
597 # in_progress/failed implies no_data
598 CheckConstraint(
599 "(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)",
600 name="in_progress_failed_iris_implies_no_data",
601 ),
602 # deleted or duplicate implies minimal data
603 CheckConstraint(
604 "(NOT ((status = 'deleted') OR (status = 'duplicate'))) OR (has_minimal_data IS TRUE)",
605 name="deleted_duplicate_implies_minimal_data",
606 ),
607 )
610class ModNote(Base):
611 """
612 A moderator note to a user. This could be a warning, just a note "hey, we did X", or any other similar message.
614 The user has to read and "acknowledge" the note before continuing onto the platform, and being un-jailed.
615 """
617 __tablename__ = "mod_notes"
618 id = Column(BigInteger, primary_key=True)
620 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
622 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
623 acknowledged = Column(DateTime(timezone=True), nullable=True)
625 # this is an internal ID to allow the mods to track different types of notes
626 internal_id = Column(String, nullable=False)
627 # the admin that left this note
628 creator_user_id = Column(ForeignKey("users.id"), nullable=False)
630 note_content = Column(String, nullable=False) # CommonMark without images
632 user = relationship("User", backref=backref("mod_notes", lazy="dynamic"), foreign_keys="ModNote.user_id")
634 def __repr__(self):
635 return f"ModeNote(id={self.id}, user={self.user}, created={self.created}, ack'd={self.acknowledged})"
637 @hybrid_property
638 def is_pending(self):
639 return self.acknowledged == None
641 __table_args__ = (
642 # used to look up pending notes
643 Index(
644 "ix_mod_notes_unacknowledged",
645 user_id,
646 postgresql_where=acknowledged == None,
647 ),
648 )
651class StrongVerificationCallbackEvent(Base):
652 __tablename__ = "strong_verification_callback_events"
654 id = Column(BigInteger, primary_key=True)
655 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
657 verification_attempt_id = Column(ForeignKey("strong_verification_attempts.id"), nullable=False, index=True)
659 iris_status = Column(String, nullable=False)
662class DonationType(enum.Enum):
663 one_time = enum.auto()
664 recurring = enum.auto()
667class DonationInitiation(Base):
668 """
669 Whenever someone initiaties a donation through the platform
670 """
672 __tablename__ = "donation_initiations"
673 id = Column(BigInteger, primary_key=True)
675 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
676 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
678 amount = Column(Integer, nullable=False)
679 stripe_checkout_session_id = Column(String, nullable=False)
681 donation_type = Column(Enum(DonationType), nullable=False)
683 user = relationship("User", backref="donation_initiations")
686class Invoice(Base):
687 """
688 Successful donations, both one off and recurring
690 Triggered by `payment_intent.succeeded` webhook
691 """
693 __tablename__ = "invoices"
695 id = Column(BigInteger, primary_key=True)
696 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
697 user_id = Column(ForeignKey("users.id"), nullable=False)
699 amount = Column(Float, nullable=False)
701 stripe_payment_intent_id = Column(String, nullable=False, unique=True)
702 stripe_receipt_url = Column(String, nullable=False)
704 user = relationship("User", backref="invoices")
707class LanguageFluency(enum.Enum):
708 # note that the numbering is important here, these are ordinal
709 beginner = 1
710 conversational = 2
711 fluent = 3
714class LanguageAbility(Base):
715 __tablename__ = "language_abilities"
716 __table_args__ = (
717 # Users can only have one language ability per language
718 UniqueConstraint("user_id", "language_code"),
719 )
721 id = Column(BigInteger, primary_key=True)
722 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
723 language_code = Column(ForeignKey("languages.code", deferrable=True), nullable=False)
724 fluency = Column(Enum(LanguageFluency), nullable=False)
726 user = relationship("User", backref="language_abilities")
727 language = relationship("Language")
730class RegionVisited(Base):
731 __tablename__ = "regions_visited"
732 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
734 id = Column(BigInteger, primary_key=True)
735 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
736 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
739class RegionLived(Base):
740 __tablename__ = "regions_lived"
741 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
743 id = Column(BigInteger, primary_key=True)
744 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
745 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
748class FriendStatus(enum.Enum):
749 pending = enum.auto()
750 accepted = enum.auto()
751 rejected = enum.auto()
752 cancelled = enum.auto()
755class FriendRelationship(Base):
756 """
757 Friendship relations between users
759 TODO: make this better with sqlalchemy self-referential stuff
760 TODO: constraint on only one row per user pair where accepted or pending
761 """
763 __tablename__ = "friend_relationships"
765 id = Column(BigInteger, primary_key=True)
767 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
768 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
770 status = Column(Enum(FriendStatus), nullable=False, default=FriendStatus.pending)
772 # timezones should always be UTC
773 time_sent = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
774 time_responded = Column(DateTime(timezone=True), nullable=True)
776 from_user = relationship("User", backref="friends_from", foreign_keys="FriendRelationship.from_user_id")
777 to_user = relationship("User", backref="friends_to", foreign_keys="FriendRelationship.to_user_id")
780class ContributeOption(enum.Enum):
781 yes = enum.auto()
782 maybe = enum.auto()
783 no = enum.auto()
786class ContributorForm(Base):
787 """
788 Someone filled in the contributor form
789 """
791 __tablename__ = "contributor_forms"
793 id = Column(BigInteger, primary_key=True)
795 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
796 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
798 ideas = Column(String, nullable=True)
799 features = Column(String, nullable=True)
800 experience = Column(String, nullable=True)
801 contribute = Column(Enum(ContributeOption), nullable=True)
802 contribute_ways = Column(ARRAY(String), nullable=False)
803 expertise = Column(String, nullable=True)
805 user = relationship("User", backref="contributor_forms")
807 @hybrid_property
808 def is_filled(self):
809 """
810 Whether the form counts as having been filled
811 """
812 return (
813 (self.ideas != None)
814 | (self.features != None)
815 | (self.experience != None)
816 | (self.contribute != None)
817 | (self.contribute_ways != [])
818 | (self.expertise != None)
819 )
821 @property
822 def should_notify(self):
823 """
824 If this evaluates to true, we send an email to the recruitment team.
826 We currently send if expertise is listed, or if they list a way to help outside of a set list
827 """
828 return (self.expertise != None) | (not set(self.contribute_ways).issubset({"community", "blog", "other"}))
831class SignupFlow(Base):
832 """
833 Signup flows/incomplete users
835 Coinciding fields have the same meaning as in User
836 """
838 __tablename__ = "signup_flows"
840 id = Column(BigInteger, primary_key=True)
842 # housekeeping
843 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
844 flow_token = Column(String, nullable=False, unique=True)
845 email_verified = Column(Boolean, nullable=False, default=False)
846 email_sent = Column(Boolean, nullable=False, default=False)
847 email_token = Column(String, nullable=True)
848 email_token_expiry = Column(DateTime(timezone=True), nullable=True)
850 ## Basic
851 name = Column(String, nullable=False)
852 # TODO: unique across both tables
853 email = Column(String, nullable=False, unique=True)
854 # TODO: invitation, attribution
856 ## Account
857 # TODO: unique across both tables
858 username = Column(String, nullable=True, unique=True)
859 hashed_password = Column(Binary, nullable=True)
860 birthdate = Column(Date, nullable=True) # in the timezone of birthplace
861 gender = Column(String, nullable=True)
862 hosting_status = Column(Enum(HostingStatus), nullable=True)
863 city = Column(String, nullable=True)
864 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
865 geom_radius = Column(Float, nullable=True)
867 accepted_tos = Column(Integer, nullable=True)
868 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")
870 opt_out_of_newsletter = Column(Boolean, nullable=True)
872 ## Feedback
873 filled_feedback = Column(Boolean, nullable=False, default=False)
874 ideas = Column(String, nullable=True)
875 features = Column(String, nullable=True)
876 experience = Column(String, nullable=True)
877 contribute = Column(Enum(ContributeOption), nullable=True)
878 contribute_ways = Column(ARRAY(String), nullable=True)
879 expertise = Column(String, nullable=True)
881 @hybrid_property
882 def token_is_valid(self):
883 return (self.email_token != None) & (self.email_token_expiry >= now())
885 @hybrid_property
886 def account_is_filled(self):
887 return (
888 (self.username != None)
889 & (self.birthdate != None)
890 & (self.gender != None)
891 & (self.hosting_status != None)
892 & (self.city != None)
893 & (self.geom != None)
894 & (self.geom_radius != None)
895 & (self.accepted_tos != None)
896 & (self.opt_out_of_newsletter != None)
897 )
899 @hybrid_property
900 def is_completed(self):
901 return (
902 self.email_verified
903 & self.account_is_filled
904 & self.filled_feedback
905 & (self.accepted_community_guidelines == GUIDELINES_VERSION)
906 )
909class LoginToken(Base):
910 """
911 A login token sent in an email to a user, allows them to sign in between the times defined by created and expiry
912 """
914 __tablename__ = "login_tokens"
915 token = Column(String, primary_key=True)
917 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
919 # timezones should always be UTC
920 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
921 expiry = Column(DateTime(timezone=True), nullable=False)
923 user = relationship("User", backref="login_tokens")
925 @hybrid_property
926 def is_valid(self):
927 return (self.created <= now()) & (self.expiry >= now())
929 def __repr__(self):
930 return f"LoginToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
933class PasswordResetToken(Base):
934 __tablename__ = "password_reset_tokens"
935 token = Column(String, primary_key=True)
937 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
939 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
940 expiry = Column(DateTime(timezone=True), nullable=False)
942 user = relationship("User", backref="password_reset_tokens")
944 @hybrid_property
945 def is_valid(self):
946 return (self.created <= now()) & (self.expiry >= now())
948 def __repr__(self):
949 return f"PasswordResetToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
952class AccountDeletionToken(Base):
953 __tablename__ = "account_deletion_tokens"
955 token = Column(String, primary_key=True)
957 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
959 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
960 expiry = Column(DateTime(timezone=True), nullable=False)
962 user = relationship("User", backref="account_deletion_tokens")
964 @hybrid_property
965 def is_valid(self):
966 return (self.created <= now()) & (self.expiry >= now())
968 def __repr__(self):
969 return f"AccountDeletionToken(token={self.token}, user_id={self.user_id}, created={self.created}, expiry={self.expiry})"
972class UserActivity(Base):
973 """
974 User activity: for each unique (user_id, period, ip_address, user_agent) tuple, keep track of number of api calls
976 Used for user "last active" as well as admin stuff
977 """
979 __tablename__ = "user_activity"
981 id = Column(BigInteger, primary_key=True)
983 user_id = Column(ForeignKey("users.id"), nullable=False)
984 # the start of a period of time, e.g. 1 hour during which we bin activeness
985 period = Column(DateTime(timezone=True), nullable=False)
987 # details of the browser, if available
988 ip_address = Column(INET, nullable=True)
989 user_agent = Column(String, nullable=True)
991 # count of api calls made with this ip, user_agent, and period
992 api_calls = Column(Integer, nullable=False, default=0)
994 __table_args__ = (
995 # helps look up this tuple quickly
996 Index(
997 "ix_user_activity_user_id_period_ip_address_user_agent",
998 user_id,
999 period,
1000 ip_address,
1001 user_agent,
1002 unique=True,
1003 ),
1004 )
1007class UserSession(Base):
1008 """
1009 API keys/session cookies for the app
1011 There are two types of sessions: long-lived, and short-lived. Long-lived are
1012 like when you choose "remember this browser": they will be valid for a long
1013 time without the user interacting with the site. Short-lived sessions on the
1014 other hand get invalidated quickly if the user does not interact with the
1015 site.
1017 Long-lived tokens are valid from `created` until `expiry`.
1019 Short-lived tokens expire after 168 hours (7 days) if they are not used.
1020 """
1022 __tablename__ = "sessions"
1023 token = Column(String, primary_key=True)
1025 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1027 # sessions are either "api keys" or "session cookies", otherwise identical
1028 # an api key is put in the authorization header (e.g. "authorization: Bearer <token>")
1029 # a session cookie is set in the "couchers-sesh" cookie (e.g. "cookie: couchers-sesh=<token>")
1030 # when a session is created, it's fixed as one or the other for security reasons
1031 # for api keys to be useful, they should be long lived and have a long expiry
1032 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))
1034 # whether it's a long-lived or short-lived session
1035 long_lived = Column(Boolean, nullable=False)
1037 # the time at which the session was created
1038 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1040 # the expiry of the session: the session *cannot* be refreshed past this
1041 expiry = Column(DateTime(timezone=True), nullable=False, server_default=func.now() + text("interval '730 days'"))
1043 # the time at which the token was invalidated, allows users to delete sessions
1044 deleted = Column(DateTime(timezone=True), nullable=True, default=None)
1046 # the last time this session was used
1047 last_seen = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1049 # count of api calls made with this token/session (if we're updating last_seen, might as well update this too)
1050 api_calls = Column(Integer, nullable=False, default=0)
1052 # details of the browser, if available
1053 # these are from the request creating the session, not used for anything else
1054 ip_address = Column(String, nullable=True)
1055 user_agent = Column(String, nullable=True)
1057 user = relationship("User", backref="sessions")
1059 @hybrid_property
1060 def is_valid(self):
1061 """
1062 It must have been created and not be expired or deleted.
1064 Also, if it's a short lived token, it must have been used in the last 168 hours.
1066 TODO: this probably won't run in python (instance level), only in sql (class level)
1067 """
1068 return (
1069 (self.created <= func.now())
1070 & (self.expiry >= func.now())
1071 & (self.deleted == None)
1072 & (self.long_lived | (func.now() - self.last_seen < text("interval '168 hours'")))
1073 )
1076class Conversation(Base):
1077 """
1078 Conversation brings together the different types of message/conversation types
1079 """
1081 __tablename__ = "conversations"
1083 id = Column(BigInteger, primary_key=True)
1084 # timezone should always be UTC
1085 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1087 def __repr__(self):
1088 return f"Conversation(id={self.id}, created={self.created})"
1091class GroupChat(Base):
1092 """
1093 Group chat
1094 """
1096 __tablename__ = "group_chats"
1098 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1100 title = Column(String, nullable=True)
1101 only_admins_invite = Column(Boolean, nullable=False, default=True)
1102 creator_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1103 is_dm = Column(Boolean, nullable=False)
1105 conversation = relationship("Conversation", backref="group_chat")
1106 creator = relationship("User", backref="created_group_chats")
1108 def __repr__(self):
1109 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})"
1112class GroupChatRole(enum.Enum):
1113 admin = enum.auto()
1114 participant = enum.auto()
1117class GroupChatSubscription(Base):
1118 """
1119 The recipient of a thread and information about when they joined/left/etc.
1120 """
1122 __tablename__ = "group_chat_subscriptions"
1123 id = Column(BigInteger, primary_key=True)
1125 # TODO: DB constraint on only one user+group_chat combo at a given time
1126 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1127 group_chat_id = Column(ForeignKey("group_chats.id"), nullable=False, index=True)
1129 # timezones should always be UTC
1130 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1131 left = Column(DateTime(timezone=True), nullable=True)
1133 role = Column(Enum(GroupChatRole), nullable=False)
1135 last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1137 # when this chat is muted until, DATETIME_INFINITY for "forever"
1138 muted_until = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_MINUS_INFINITY.isoformat())
1140 user = relationship("User", backref="group_chat_subscriptions")
1141 group_chat = relationship("GroupChat", backref=backref("subscriptions", lazy="dynamic"))
1143 def muted_display(self):
1144 """
1145 Returns (muted, muted_until) display values:
1146 1. If not muted, returns (False, None)
1147 2. If muted forever, returns (True, None)
1148 3. If muted until a given datetime returns (True, dt)
1149 """
1150 if self.muted_until < now():
1151 return (False, None)
1152 elif self.muted_until == DATETIME_INFINITY:
1153 return (True, None)
1154 else:
1155 return (True, self.muted_until)
1157 @hybrid_property
1158 def is_muted(self):
1159 return self.muted_until > func.now()
1161 def __repr__(self):
1162 return f"GroupChatSubscription(id={self.id}, user={self.user}, joined={self.joined}, left={self.left}, role={self.role}, group_chat={self.group_chat})"
1165class MessageType(enum.Enum):
1166 text = enum.auto()
1167 # e.g.
1168 # image =
1169 # emoji =
1170 # ...
1171 chat_created = enum.auto()
1172 chat_edited = enum.auto()
1173 user_invited = enum.auto()
1174 user_left = enum.auto()
1175 user_made_admin = enum.auto()
1176 user_removed_admin = enum.auto() # RemoveGroupChatAdmin: remove admin permission from a user in group chat
1177 host_request_status_changed = enum.auto()
1178 user_removed = enum.auto() # user is removed from group chat by amdin RemoveGroupChatUser
1181class HostRequestStatus(enum.Enum):
1182 pending = enum.auto()
1183 accepted = enum.auto()
1184 rejected = enum.auto()
1185 confirmed = enum.auto()
1186 cancelled = enum.auto()
1189class Message(Base):
1190 """
1191 A message.
1193 If message_type = text, then the message is a normal text message, otherwise, it's a special control message.
1194 """
1196 __tablename__ = "messages"
1198 id = Column(BigInteger, primary_key=True)
1200 # which conversation the message belongs in
1201 conversation_id = Column(ForeignKey("conversations.id"), nullable=False, index=True)
1203 # the user that sent the message/command
1204 author_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1206 # the message type, "text" is a text message, otherwise a "control message"
1207 message_type = Column(Enum(MessageType), nullable=False)
1209 # the target if a control message and requires target, e.g. if inviting a user, the user invited is the target
1210 target_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1212 # time sent, timezone should always be UTC
1213 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1215 # the plain-text message text if not control
1216 text = Column(String, nullable=True)
1218 # the new host request status if the message type is host_request_status_changed
1219 host_request_status_target = Column(Enum(HostRequestStatus), nullable=True)
1221 conversation = relationship("Conversation", backref="messages", order_by="Message.time.desc()")
1222 author = relationship("User", foreign_keys="Message.author_id")
1223 target = relationship("User", foreign_keys="Message.target_id")
1225 @property
1226 def is_normal_message(self):
1227 """
1228 There's only one normal type atm, text
1229 """
1230 return self.message_type == MessageType.text
1232 def __repr__(self):
1233 return f"Message(id={self.id}, time={self.time}, text={self.text}, author={self.author}, conversation={self.conversation})"
1236class ContentReport(Base):
1237 """
1238 A piece of content reported to admins
1239 """
1241 __tablename__ = "content_reports"
1243 id = Column(BigInteger, primary_key=True)
1245 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1247 # the user who reported or flagged the content
1248 reporting_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1250 # reason, e.g. spam, inappropriate, etc
1251 reason = Column(String, nullable=False)
1252 # a short description
1253 description = Column(String, nullable=False)
1255 # a reference to the content, see //docs/content_ref.md
1256 content_ref = Column(String, nullable=False)
1257 # the author of the content (e.g. the user who wrote the comment itself)
1258 author_user_id = Column(ForeignKey("users.id"), nullable=False)
1260 # details of the browser, if available
1261 user_agent = Column(String, nullable=False)
1262 # the URL the user was on when reporting the content
1263 page = Column(String, nullable=False)
1265 # see comments above for reporting vs author
1266 reporting_user = relationship("User", foreign_keys="ContentReport.reporting_user_id")
1267 author_user = relationship("User", foreign_keys="ContentReport.author_user_id")
1270class Email(Base):
1271 """
1272 Table of all dispatched emails for debugging purposes, etc.
1273 """
1275 __tablename__ = "emails"
1277 id = Column(String, primary_key=True)
1279 # timezone should always be UTC
1280 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1282 sender_name = Column(String, nullable=False)
1283 sender_email = Column(String, nullable=False)
1285 recipient = Column(String, nullable=False)
1286 subject = Column(String, nullable=False)
1288 plain = Column(String, nullable=False)
1289 html = Column(String, nullable=False)
1291 list_unsubscribe_header = Column(String, nullable=True)
1292 source_data = Column(String, nullable=True)
1295class SMS(Base):
1296 """
1297 Table of all sent SMSs for debugging purposes, etc.
1298 """
1300 __tablename__ = "smss"
1302 id = Column(BigInteger, primary_key=True)
1304 # timezone should always be UTC
1305 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1306 # AWS message id
1307 message_id = Column(String, nullable=False)
1309 # the SMS sender ID sent to AWS, name that the SMS appears to come from
1310 sms_sender_id = Column(String, nullable=False)
1311 number = Column(String, nullable=False)
1312 message = Column(String, nullable=False)
1315class HostRequest(Base):
1316 """
1317 A request to stay with a host
1318 """
1320 __tablename__ = "host_requests"
1322 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1323 surfer_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1324 host_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1326 # TODO: proper timezone handling
1327 timezone = "Etc/UTC"
1329 # dates in the timezone above
1330 from_date = Column(Date, nullable=False)
1331 to_date = Column(Date, nullable=False)
1333 # timezone aware start and end times of the request, can be compared to now()
1334 start_time = column_property(date_in_timezone(from_date, timezone))
1335 end_time = column_property(date_in_timezone(to_date, timezone) + text("interval '1 days'"))
1336 start_time_to_write_reference = column_property(date_in_timezone(to_date, timezone))
1337 # notice 1 day for midnight at the *end of the day*, then 14 days to write a ref
1338 end_time_to_write_reference = column_property(date_in_timezone(to_date, timezone) + text("interval '15 days'"))
1340 status = Column(Enum(HostRequestStatus), nullable=False)
1342 host_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1343 surfer_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1345 # number of reference reminders sent out
1346 host_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1347 surfer_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1349 # reason why the host/surfer marked that they didn't meet up
1350 # if null then they haven't marked it such
1351 host_reason_didnt_meetup = Column(String, nullable=True)
1352 surfer_reason_didnt_meetup = Column(String, nullable=True)
1354 surfer = relationship("User", backref="host_requests_sent", foreign_keys="HostRequest.surfer_user_id")
1355 host = relationship("User", backref="host_requests_received", foreign_keys="HostRequest.host_user_id")
1356 conversation = relationship("Conversation")
1358 __table_args__ = (
1359 # allows fast lookup as to whether they didn't meet up
1360 Index(
1361 "ix_host_requests_host_didnt_meetup",
1362 host_reason_didnt_meetup != None,
1363 ),
1364 Index(
1365 "ix_host_requests_surfer_didnt_meetup",
1366 surfer_reason_didnt_meetup != None,
1367 ),
1368 )
1370 @hybrid_property
1371 def can_write_reference(self):
1372 return (
1373 ((self.status == HostRequestStatus.confirmed) | (self.status == HostRequestStatus.accepted))
1374 & (now() >= self.start_time_to_write_reference)
1375 & (now() <= self.end_time_to_write_reference)
1376 )
1378 @can_write_reference.expression
1379 def can_write_reference(cls):
1380 return (
1381 ((cls.status == HostRequestStatus.confirmed) | (cls.status == HostRequestStatus.accepted))
1382 & (func.now() >= cls.start_time_to_write_reference)
1383 & (func.now() <= cls.end_time_to_write_reference)
1384 )
1386 def __repr__(self):
1387 return f"HostRequest(id={self.conversation_id}, surfer_user_id={self.surfer_user_id}, host_user_id={self.host_user_id}...)"
1390class ReferenceType(enum.Enum):
1391 friend = enum.auto()
1392 surfed = enum.auto() # The "from" user surfed with the "to" user
1393 hosted = enum.auto() # The "from" user hosted the "to" user
1396class Reference(Base):
1397 """
1398 Reference from one user to another
1399 """
1401 __tablename__ = "references"
1403 id = Column(BigInteger, primary_key=True)
1404 # timezone should always be UTC
1405 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1407 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1408 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1410 reference_type = Column(Enum(ReferenceType), nullable=False)
1412 host_request_id = Column(ForeignKey("host_requests.id"), nullable=True)
1414 text = Column(String, nullable=False) # plain text
1415 # text that's only visible to mods
1416 private_text = Column(String, nullable=True) # plain text
1418 rating = Column(Float, nullable=False)
1419 was_appropriate = Column(Boolean, nullable=False)
1421 from_user = relationship("User", backref="references_from", foreign_keys="Reference.from_user_id")
1422 to_user = relationship("User", backref="references_to", foreign_keys="Reference.to_user_id")
1424 host_request = relationship("HostRequest", backref="references")
1426 __table_args__ = (
1427 # Rating must be between 0 and 1, inclusive
1428 CheckConstraint(
1429 "rating BETWEEN 0 AND 1",
1430 name="rating_between_0_and_1",
1431 ),
1432 # Has host_request_id or it's a friend reference
1433 CheckConstraint(
1434 "(host_request_id IS NOT NULL) <> (reference_type = 'friend')",
1435 name="host_request_id_xor_friend_reference",
1436 ),
1437 # Each user can leave at most one friend reference to another user
1438 Index(
1439 "ix_references_unique_friend_reference",
1440 from_user_id,
1441 to_user_id,
1442 reference_type,
1443 unique=True,
1444 postgresql_where=(reference_type == ReferenceType.friend),
1445 ),
1446 # Each user can leave at most one reference to another user for each stay
1447 Index(
1448 "ix_references_unique_per_host_request",
1449 from_user_id,
1450 to_user_id,
1451 host_request_id,
1452 unique=True,
1453 postgresql_where=(host_request_id != None),
1454 ),
1455 )
1457 @property
1458 def should_report(self):
1459 """
1460 If this evaluates to true, we send a report to the moderation team.
1461 """
1462 return self.rating <= 0.4 or not self.was_appropriate or self.private_text
1465class InitiatedUpload(Base):
1466 """
1467 Started downloads, not necessarily complete yet.
1468 """
1470 __tablename__ = "initiated_uploads"
1472 key = Column(String, primary_key=True)
1474 # timezones should always be UTC
1475 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1476 expiry = Column(DateTime(timezone=True), nullable=False)
1478 initiator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1480 initiator_user = relationship("User")
1482 @hybrid_property
1483 def is_valid(self):
1484 return (self.created <= func.now()) & (self.expiry >= func.now())
1487class Upload(Base):
1488 """
1489 Completed uploads.
1490 """
1492 __tablename__ = "uploads"
1493 key = Column(String, primary_key=True)
1495 filename = Column(String, nullable=False)
1496 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1497 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1499 # photo credit, etc
1500 credit = Column(String, nullable=True)
1502 creator_user = relationship("User", backref="uploads", foreign_keys="Upload.creator_user_id")
1504 def _url(self, size):
1505 return urls.media_url(filename=self.filename, size=size)
1507 @property
1508 def thumbnail_url(self):
1509 return self._url("thumbnail")
1511 @property
1512 def full_url(self):
1513 return self._url("full")
1516communities_seq = Sequence("communities_seq")
1519class Node(Base):
1520 """
1521 Node, i.e. geographical subdivision of the world
1523 Administered by the official cluster
1524 """
1526 __tablename__ = "nodes"
1528 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1530 # name and description come from official cluster
1531 parent_node_id = Column(ForeignKey("nodes.id"), nullable=True, index=True)
1532 geom = deferred(Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False))
1533 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1535 parent_node = relationship("Node", backref="child_nodes", remote_side="Node.id")
1537 contained_users = relationship(
1538 "User",
1539 primaryjoin="func.ST_Contains(foreign(Node.geom), User.geom).as_comparison(1, 2)",
1540 viewonly=True,
1541 uselist=True,
1542 )
1544 contained_user_ids = association_proxy("contained_users", "id")
1547class Cluster(Base):
1548 """
1549 Cluster, administered grouping of content
1550 """
1552 __tablename__ = "clusters"
1554 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1555 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1556 name = Column(String, nullable=False)
1557 # short description
1558 description = Column(String, nullable=False)
1559 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1561 is_official_cluster = Column(Boolean, nullable=False, default=False)
1563 slug = column_property(func.slugify(name))
1565 official_cluster_for_node = relationship(
1566 "Node",
1567 primaryjoin="and_(Cluster.parent_node_id == Node.id, Cluster.is_official_cluster)",
1568 backref=backref("official_cluster", uselist=False),
1569 uselist=False,
1570 viewonly=True,
1571 )
1573 parent_node = relationship(
1574 "Node", backref="child_clusters", remote_side="Node.id", foreign_keys="Cluster.parent_node_id"
1575 )
1577 nodes = relationship("Cluster", backref="clusters", secondary="node_cluster_associations", viewonly=True)
1578 # all pages
1579 pages = relationship(
1580 "Page", backref="clusters", secondary="cluster_page_associations", lazy="dynamic", viewonly=True
1581 )
1582 events = relationship("Event", backref="clusters", secondary="cluster_event_associations", viewonly=True)
1583 discussions = relationship(
1584 "Discussion", backref="clusters", secondary="cluster_discussion_associations", viewonly=True
1585 )
1587 # includes also admins
1588 members = relationship(
1589 "User",
1590 lazy="dynamic",
1591 backref="cluster_memberships",
1592 secondary="cluster_subscriptions",
1593 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1594 secondaryjoin="User.id == ClusterSubscription.user_id",
1595 viewonly=True,
1596 )
1598 admins = relationship(
1599 "User",
1600 lazy="dynamic",
1601 backref="cluster_adminships",
1602 secondary="cluster_subscriptions",
1603 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1604 secondaryjoin="and_(User.id == ClusterSubscription.user_id, ClusterSubscription.role == 'admin')",
1605 viewonly=True,
1606 )
1608 main_page = relationship(
1609 "Page",
1610 primaryjoin="and_(Cluster.id == Page.owner_cluster_id, Page.type == 'main_page')",
1611 viewonly=True,
1612 uselist=False,
1613 )
1615 @property
1616 def is_leaf(self) -> bool:
1617 """Whether the cluster is a leaf node in the cluster hierarchy."""
1618 return len(self.parent_node.child_nodes) == 0
1620 __table_args__ = (
1621 # Each node can have at most one official cluster
1622 Index(
1623 "ix_clusters_owner_parent_node_id_is_official_cluster",
1624 parent_node_id,
1625 is_official_cluster,
1626 unique=True,
1627 postgresql_where=is_official_cluster,
1628 ),
1629 )
1632class NodeClusterAssociation(Base):
1633 """
1634 NodeClusterAssociation, grouping of nodes
1635 """
1637 __tablename__ = "node_cluster_associations"
1638 __table_args__ = (UniqueConstraint("node_id", "cluster_id"),)
1640 id = Column(BigInteger, primary_key=True)
1642 node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1643 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1645 node = relationship("Node", backref="node_cluster_associations")
1646 cluster = relationship("Cluster", backref="node_cluster_associations")
1649class ClusterRole(enum.Enum):
1650 member = enum.auto()
1651 admin = enum.auto()
1654class ClusterSubscription(Base):
1655 """
1656 ClusterSubscription of a user
1657 """
1659 __tablename__ = "cluster_subscriptions"
1660 __table_args__ = (UniqueConstraint("user_id", "cluster_id"),)
1662 id = Column(BigInteger, primary_key=True)
1664 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1665 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1666 role = Column(Enum(ClusterRole), nullable=False)
1668 user = relationship("User", backref="cluster_subscriptions")
1669 cluster = relationship("Cluster", backref="cluster_subscriptions")
1672class ClusterPageAssociation(Base):
1673 """
1674 pages related to clusters
1675 """
1677 __tablename__ = "cluster_page_associations"
1678 __table_args__ = (UniqueConstraint("page_id", "cluster_id"),)
1680 id = Column(BigInteger, primary_key=True)
1682 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1683 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1685 page = relationship("Page", backref="cluster_page_associations")
1686 cluster = relationship("Cluster", backref="cluster_page_associations")
1689class PageType(enum.Enum):
1690 main_page = enum.auto()
1691 place = enum.auto()
1692 guide = enum.auto()
1695class Page(Base):
1696 """
1697 similar to a wiki page about a community, POI or guide
1698 """
1700 __tablename__ = "pages"
1702 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1704 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1705 type = Column(Enum(PageType), nullable=False)
1706 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1707 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1708 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1710 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
1712 parent_node = relationship("Node", backref="child_pages", remote_side="Node.id", foreign_keys="Page.parent_node_id")
1714 thread = relationship("Thread", backref="page", uselist=False)
1715 creator_user = relationship("User", backref="created_pages", foreign_keys="Page.creator_user_id")
1716 owner_user = relationship("User", backref="owned_pages", foreign_keys="Page.owner_user_id")
1717 owner_cluster = relationship(
1718 "Cluster", backref=backref("owned_pages", lazy="dynamic"), uselist=False, foreign_keys="Page.owner_cluster_id"
1719 )
1721 editors = relationship("User", secondary="page_versions", viewonly=True)
1723 __table_args__ = (
1724 # Only one of owner_user and owner_cluster should be set
1725 CheckConstraint(
1726 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
1727 name="one_owner",
1728 ),
1729 # Only clusters can own main pages
1730 CheckConstraint(
1731 "NOT (owner_cluster_id IS NULL AND type = 'main_page')",
1732 name="main_page_owned_by_cluster",
1733 ),
1734 # Each cluster can have at most one main page
1735 Index(
1736 "ix_pages_owner_cluster_id_type",
1737 owner_cluster_id,
1738 type,
1739 unique=True,
1740 postgresql_where=(type == PageType.main_page),
1741 ),
1742 )
1744 def __repr__(self):
1745 return f"Page({self.id=})"
1748class PageVersion(Base):
1749 """
1750 version of page content
1751 """
1753 __tablename__ = "page_versions"
1755 id = Column(BigInteger, primary_key=True)
1757 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1758 editor_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1759 title = Column(String, nullable=False)
1760 content = Column(String, nullable=False) # CommonMark without images
1761 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
1762 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
1763 # the human-readable address
1764 address = Column(String, nullable=True)
1765 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1767 slug = column_property(func.slugify(title))
1769 page = relationship("Page", backref="versions", order_by="PageVersion.id")
1770 editor_user = relationship("User", backref="edited_pages")
1771 photo = relationship("Upload")
1773 __table_args__ = (
1774 # Geom and address must either both be null or both be set
1775 CheckConstraint(
1776 "(geom IS NULL) = (address IS NULL)",
1777 name="geom_iff_address",
1778 ),
1779 )
1781 @property
1782 def coordinates(self):
1783 # returns (lat, lng) or None
1784 return get_coordinates(self.geom)
1786 def __repr__(self):
1787 return f"PageVersion({self.id=}, {self.page_id=})"
1790class ClusterEventAssociation(Base):
1791 """
1792 events related to clusters
1793 """
1795 __tablename__ = "cluster_event_associations"
1796 __table_args__ = (UniqueConstraint("event_id", "cluster_id"),)
1798 id = Column(BigInteger, primary_key=True)
1800 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1801 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1803 event = relationship("Event", backref="cluster_event_associations")
1804 cluster = relationship("Cluster", backref="cluster_event_associations")
1807class Event(Base):
1808 """
1809 An event is compose of two parts:
1811 * An event template (Event)
1812 * An occurrence (EventOccurrence)
1814 One-off events will have one of each; repeating events will have one Event,
1815 multiple EventOccurrences, one for each time the event happens.
1816 """
1818 __tablename__ = "events"
1820 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1821 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1823 title = Column(String, nullable=False)
1825 slug = column_property(func.slugify(title))
1827 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1828 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1829 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1830 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1831 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
1833 parent_node = relationship(
1834 "Node", backref="child_events", remote_side="Node.id", foreign_keys="Event.parent_node_id"
1835 )
1836 thread = relationship("Thread", backref="event", uselist=False)
1837 subscribers = relationship(
1838 "User", backref="subscribed_events", secondary="event_subscriptions", lazy="dynamic", viewonly=True
1839 )
1840 organizers = relationship(
1841 "User", backref="organized_events", secondary="event_organizers", lazy="dynamic", viewonly=True
1842 )
1843 creator_user = relationship("User", backref="created_events", foreign_keys="Event.creator_user_id")
1844 owner_user = relationship("User", backref="owned_events", foreign_keys="Event.owner_user_id")
1845 owner_cluster = relationship(
1846 "Cluster",
1847 backref=backref("owned_events", lazy="dynamic"),
1848 uselist=False,
1849 foreign_keys="Event.owner_cluster_id",
1850 )
1852 __table_args__ = (
1853 # Only one of owner_user and owner_cluster should be set
1854 CheckConstraint(
1855 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
1856 name="one_owner",
1857 ),
1858 )
1861class EventOccurrence(Base):
1862 __tablename__ = "event_occurrences"
1864 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1865 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1867 # the user that created this particular occurrence of a repeating event (same as event.creator_user_id if single event)
1868 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1869 content = Column(String, nullable=False) # CommonMark without images
1870 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
1872 is_cancelled = Column(Boolean, nullable=False, default=False, server_default=text("false"))
1873 is_deleted = Column(Boolean, nullable=False, default=False, server_default=text("false"))
1875 # a null geom is an online-only event
1876 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
1877 # physical address, iff geom is not null
1878 address = Column(String, nullable=True)
1879 # videoconferencing link, etc, must be specified if no geom, otherwise optional
1880 link = Column(String, nullable=True)
1882 timezone = "Etc/UTC"
1884 # time during which the event takes place; this is a range type (instead of separate start+end times) which
1885 # simplifies database constraints, etc
1886 during = Column(TSTZRANGE, nullable=False)
1888 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1889 last_edited = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1891 creator_user = relationship(
1892 "User", backref="created_event_occurrences", foreign_keys="EventOccurrence.creator_user_id"
1893 )
1894 event = relationship(
1895 "Event",
1896 backref=backref("occurrences", lazy="dynamic"),
1897 remote_side="Event.id",
1898 foreign_keys="EventOccurrence.event_id",
1899 )
1901 photo = relationship("Upload")
1903 __table_args__ = (
1904 # Geom and address go together
1905 CheckConstraint(
1906 # geom and address are either both null or neither of them are null
1907 "(geom IS NULL) = (address IS NULL)",
1908 name="geom_iff_address",
1909 ),
1910 # Online-only events need a link, note that online events may also have a link
1911 CheckConstraint(
1912 # exactly oen of geom or link is non-null
1913 "(geom IS NULL) <> (link IS NULL)",
1914 name="link_or_geom",
1915 ),
1916 # Can't have overlapping occurrences in the same Event
1917 ExcludeConstraint(("event_id", "="), ("during", "&&"), name="event_occurrences_event_id_during_excl"),
1918 )
1920 @property
1921 def coordinates(self):
1922 # returns (lat, lng) or None
1923 return get_coordinates(self.geom)
1925 @hybrid_property
1926 def start_time(self):
1927 return self.during.lower
1929 @start_time.expression
1930 def start_time(cls):
1931 return func.lower(cls.during)
1933 @hybrid_property
1934 def end_time(self):
1935 return self.during.upper
1937 @end_time.expression
1938 def end_time(cls):
1939 return func.upper(cls.during)
1942class EventSubscription(Base):
1943 """
1944 Users' subscriptions to events
1945 """
1947 __tablename__ = "event_subscriptions"
1948 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
1950 id = Column(BigInteger, primary_key=True)
1952 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1953 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1954 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1956 user = relationship("User")
1957 event = relationship("Event")
1960class EventOrganizer(Base):
1961 """
1962 Organizers for events
1963 """
1965 __tablename__ = "event_organizers"
1966 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
1968 id = Column(BigInteger, primary_key=True)
1970 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1971 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1972 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1974 user = relationship("User")
1975 event = relationship("Event")
1978class AttendeeStatus(enum.Enum):
1979 going = enum.auto()
1980 maybe = enum.auto()
1983class EventOccurrenceAttendee(Base):
1984 """
1985 Attendees for events
1986 """
1988 __tablename__ = "event_occurrence_attendees"
1989 __table_args__ = (UniqueConstraint("occurrence_id", "user_id"),)
1991 id = Column(BigInteger, primary_key=True)
1993 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1994 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
1995 responded = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1996 attendee_status = Column(Enum(AttendeeStatus), nullable=False)
1998 user = relationship("User")
1999 occurrence = relationship("EventOccurrence", backref=backref("attendances", lazy="dynamic"))
2002class EventCommunityInviteRequest(Base):
2003 """
2004 Requests to send out invitation notifications/emails to the community for a given event occurrence
2005 """
2007 __tablename__ = "event_community_invite_requests"
2009 id = Column(BigInteger, primary_key=True)
2011 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
2012 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2014 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2016 decided = Column(DateTime(timezone=True), nullable=True)
2017 decided_by_user_id = Column(ForeignKey("users.id"), nullable=True)
2018 approved = Column(Boolean, nullable=True)
2020 occurrence = relationship("EventOccurrence", backref=backref("community_invite_requests", lazy="dynamic"))
2021 user = relationship("User", foreign_keys="EventCommunityInviteRequest.user_id")
2023 __table_args__ = (
2024 # each user can only request once
2025 UniqueConstraint("occurrence_id", "user_id"),
2026 # each event can only have one notification sent out
2027 Index(
2028 "ix_event_community_invite_requests_unique",
2029 occurrence_id,
2030 unique=True,
2031 postgresql_where=and_(approved.is_not(None), approved == True),
2032 ),
2033 # decided and approved ought to be null simultaneously
2034 CheckConstraint(
2035 "((decided IS NULL) AND (decided_by_user_id IS NULL) AND (approved IS NULL)) OR \
2036 ((decided IS NOT NULL) AND (decided_by_user_id IS NOT NULL) AND (approved IS NOT NULL))",
2037 name="decided_approved",
2038 ),
2039 )
2042class ClusterDiscussionAssociation(Base):
2043 """
2044 discussions related to clusters
2045 """
2047 __tablename__ = "cluster_discussion_associations"
2048 __table_args__ = (UniqueConstraint("discussion_id", "cluster_id"),)
2050 id = Column(BigInteger, primary_key=True)
2052 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2053 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2055 discussion = relationship("Discussion", backref="cluster_discussion_associations")
2056 cluster = relationship("Cluster", backref="cluster_discussion_associations")
2059class Discussion(Base):
2060 """
2061 forum board
2062 """
2064 __tablename__ = "discussions"
2066 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
2068 title = Column(String, nullable=False)
2069 content = Column(String, nullable=False)
2070 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
2071 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2073 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2074 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2076 slug = column_property(func.slugify(title))
2078 thread = relationship("Thread", backref="discussion", uselist=False)
2080 subscribers = relationship("User", backref="discussions", secondary="discussion_subscriptions", viewonly=True)
2082 creator_user = relationship("User", backref="created_discussions", foreign_keys="Discussion.creator_user_id")
2083 owner_cluster = relationship("Cluster", backref=backref("owned_discussions", lazy="dynamic"), uselist=False)
2086class DiscussionSubscription(Base):
2087 """
2088 users subscriptions to discussions
2089 """
2091 __tablename__ = "discussion_subscriptions"
2092 __table_args__ = (UniqueConstraint("discussion_id", "user_id"),)
2094 id = Column(BigInteger, primary_key=True)
2096 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2097 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2098 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2099 left = Column(DateTime(timezone=True), nullable=True)
2101 user = relationship("User", backref="discussion_subscriptions")
2102 discussion = relationship("Discussion", backref="discussion_subscriptions")
2105class Thread(Base):
2106 """
2107 Thread
2108 """
2110 __tablename__ = "threads"
2112 id = Column(BigInteger, primary_key=True)
2114 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2115 deleted = Column(DateTime(timezone=True), nullable=True)
2118class Comment(Base):
2119 """
2120 Comment
2121 """
2123 __tablename__ = "comments"
2125 id = Column(BigInteger, primary_key=True)
2127 thread_id = Column(ForeignKey("threads.id"), nullable=False, index=True)
2128 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2129 content = Column(String, nullable=False) # CommonMark without images
2130 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2131 deleted = Column(DateTime(timezone=True), nullable=True)
2133 thread = relationship("Thread", backref="comments")
2136class Reply(Base):
2137 """
2138 Reply
2139 """
2141 __tablename__ = "replies"
2143 id = Column(BigInteger, primary_key=True)
2145 comment_id = Column(ForeignKey("comments.id"), nullable=False, index=True)
2146 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2147 content = Column(String, nullable=False) # CommonMark without images
2148 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2149 deleted = Column(DateTime(timezone=True), nullable=True)
2151 comment = relationship("Comment", backref="replies")
2154class BackgroundJobState(enum.Enum):
2155 # job is fresh, waiting to be picked off the queue
2156 pending = enum.auto()
2157 # job complete
2158 completed = enum.auto()
2159 # error occured, will be retried
2160 error = enum.auto()
2161 # failed too many times, not retrying anymore
2162 failed = enum.auto()
2165class BackgroundJob(Base):
2166 """
2167 This table implements a queue of background jobs.
2168 """
2170 __tablename__ = "background_jobs"
2172 id = Column(BigInteger, primary_key=True)
2174 # used to discern which function should be triggered to service it
2175 job_type = Column(String, nullable=False)
2176 state = Column(Enum(BackgroundJobState), nullable=False, default=BackgroundJobState.pending)
2178 # time queued
2179 queued = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2181 # time at which we may next attempt it, for implementing exponential backoff
2182 next_attempt_after = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2184 # used to count number of retries for failed jobs
2185 try_count = Column(Integer, nullable=False, default=0)
2187 max_tries = Column(Integer, nullable=False, default=5)
2189 # protobuf encoded job payload
2190 payload = Column(Binary, nullable=False)
2192 # if the job failed, we write that info here
2193 failure_info = Column(String, nullable=True)
2195 __table_args__ = (
2196 # used in looking up background jobs to attempt
2197 # create index on background_jobs(next_attempt_after, (max_tries - try_count)) where state = 'pending' OR state = 'error';
2198 Index(
2199 "ix_background_jobs_lookup",
2200 next_attempt_after,
2201 (max_tries - try_count),
2202 postgresql_where=((state == BackgroundJobState.pending) | (state == BackgroundJobState.error)),
2203 ),
2204 )
2206 @hybrid_property
2207 def ready_for_retry(self):
2208 return (
2209 (self.next_attempt_after <= func.now())
2210 & (self.try_count < self.max_tries)
2211 & ((self.state == BackgroundJobState.pending) | (self.state == BackgroundJobState.error))
2212 )
2214 def __repr__(self):
2215 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})"
2218class NotificationDeliveryType(enum.Enum):
2219 # send push notification to mobile/web
2220 push = enum.auto()
2221 # send individual email immediately
2222 email = enum.auto()
2223 # send in digest
2224 digest = enum.auto()
2227dt = NotificationDeliveryType
2228nd = notification_data_pb2
2230dt_sec = [dt.email, dt.push]
2231dt_all = [dt.email, dt.push, dt.digest]
2234class NotificationTopicAction(enum.Enum):
2235 def __init__(self, topic_action, defaults, user_editable, data_type):
2236 self.topic, self.action = topic_action.split(":")
2237 self.defaults = defaults
2238 # for now user editable == not a security notification
2239 self.user_editable = user_editable
2241 self.data_type = data_type
2243 def unpack(self):
2244 return self.topic, self.action
2246 @property
2247 def display(self):
2248 return f"{self.topic}:{self.action}"
2250 def __str__(self):
2251 return self.display
2253 # topic, action, default delivery types
2254 friend_request__create = ("friend_request:create", dt_all, True, nd.FriendRequestCreate)
2255 friend_request__accept = ("friend_request:accept", dt_all, True, nd.FriendRequestAccept)
2257 # host requests
2258 host_request__create = ("host_request:create", dt_all, True, nd.HostRequestCreate)
2259 host_request__accept = ("host_request:accept", dt_all, True, nd.HostRequestAccept)
2260 host_request__reject = ("host_request:reject", dt_all, True, nd.HostRequestReject)
2261 host_request__confirm = ("host_request:confirm", dt_all, True, nd.HostRequestConfirm)
2262 host_request__cancel = ("host_request:cancel", dt_all, True, nd.HostRequestCancel)
2263 host_request__message = ("host_request:message", [dt.push, dt.digest], True, nd.HostRequestMessage)
2264 host_request__missed_messages = ("host_request:missed_messages", [dt.email], True, nd.HostRequestMissedMessages)
2266 # you receive a friend ref
2267 reference__receive_friend = ("reference:receive_friend", dt_all, True, nd.ReferenceReceiveFriend)
2268 # you receive a reference from ... the host
2269 reference__receive_hosted = ("reference:receive_hosted", dt_all, True, nd.ReferenceReceiveHostRequest)
2270 # ... the surfer
2271 reference__receive_surfed = ("reference:receive_surfed", dt_all, True, nd.ReferenceReceiveHostRequest)
2273 # you hosted
2274 reference__reminder_hosted = ("reference:reminder_hosted", dt_all, True, nd.ReferenceReminder)
2275 # you surfed
2276 reference__reminder_surfed = ("reference:reminder_surfed", dt_all, True, nd.ReferenceReminder)
2278 badge__add = ("badge:add", [dt.push, dt.digest], True, nd.BadgeAdd)
2279 badge__remove = ("badge:remove", [dt.push, dt.digest], True, nd.BadgeRemove)
2281 # group chats
2282 chat__message = ("chat:message", [dt.push, dt.digest], True, nd.ChatMessage)
2283 chat__missed_messages = ("chat:missed_messages", [dt.email], True, nd.ChatMissedMessages)
2285 # events
2286 # approved by mods
2287 event__create_approved = ("event:create_approved", dt_all, True, nd.EventCreate)
2288 # any user creates any event, default to no notifications
2289 event__create_any = ("event:create_any", [], True, nd.EventCreate)
2290 event__update = ("event:update", dt_all, True, nd.EventUpdate)
2291 event__cancel = ("event:cancel", dt_all, True, nd.EventCancel)
2292 event__delete = ("event:delete", dt_all, True, nd.EventDelete)
2293 event__invite_organizer = ("event:invite_organizer", dt_all, True, nd.EventInviteOrganizer)
2294 # toplevel comment on an event
2295 event__comment = ("event:comment", dt_all, True, nd.EventComment)
2297 # discussion created
2298 discussion__create = ("discussion:create", [dt.digest], True, nd.DiscussionCreate)
2299 # someone comments on your discussion
2300 discussion__comment = ("discussion:comment", dt_all, True, nd.DiscussionComment)
2302 # someone responds to any of your top-level comment across the platform
2303 thread__reply = ("thread:reply", dt_all, True, nd.ThreadReply)
2305 # account settings
2306 password__change = ("password:change", dt_sec, False, empty_pb2.Empty)
2307 email_address__change = ("email_address:change", dt_sec, False, nd.EmailAddressChange)
2308 email_address__verify = ("email_address:verify", dt_sec, False, empty_pb2.Empty)
2309 phone_number__change = ("phone_number:change", dt_sec, False, nd.PhoneNumberChange)
2310 phone_number__verify = ("phone_number:verify", dt_sec, False, nd.PhoneNumberVerify)
2311 # reset password
2312 password_reset__start = ("password_reset:start", dt_sec, False, nd.PasswordResetStart)
2313 password_reset__complete = ("password_reset:complete", dt_sec, False, empty_pb2.Empty)
2315 # account deletion
2316 account_deletion__start = ("account_deletion:start", dt_sec, False, nd.AccountDeletionStart)
2317 # no more pushing to do
2318 account_deletion__complete = ("account_deletion:complete", dt_sec, False, nd.AccountDeletionComplete)
2319 # undeleted
2320 account_deletion__recovered = ("account_deletion:recovered", dt_sec, False, empty_pb2.Empty)
2322 # admin actions
2323 gender__change = ("gender:change", dt_sec, False, nd.GenderChange)
2324 birthdate__change = ("birthdate:change", dt_sec, False, nd.BirthdateChange)
2325 api_key__create = ("api_key:create", dt_sec, False, nd.ApiKeyCreate)
2327 donation__received = ("donation:received", dt_sec, True, nd.DonationReceived)
2329 onboarding__reminder = ("onboarding:reminder", dt_sec, True, empty_pb2.Empty)
2331 modnote__create = ("modnote:create", dt_sec, False, empty_pb2.Empty)
2333 verification__sv_fail = ("verification:sv_fail", dt_sec, False, nd.VerificationSVFail)
2334 verification__sv_success = ("verification:sv_success", dt_sec, False, empty_pb2.Empty)
2337class NotificationPreference(Base):
2338 __tablename__ = "notification_preferences"
2340 id = Column(BigInteger, primary_key=True)
2341 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2343 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2344 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2345 deliver = Column(Boolean, nullable=False)
2347 user = relationship("User", foreign_keys="NotificationPreference.user_id")
2349 __table_args__ = (UniqueConstraint("user_id", "topic_action", "delivery_type"),)
2352class Notification(Base):
2353 """
2354 Table for accumulating notifications until it is time to send email digest
2355 """
2357 __tablename__ = "notifications"
2359 id = Column(BigInteger, primary_key=True)
2360 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2362 # recipient user id
2363 user_id = Column(ForeignKey("users.id"), nullable=False)
2365 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2366 key = Column(String, nullable=False)
2368 data = Column(Binary, nullable=False)
2370 user = relationship("User", foreign_keys="Notification.user_id")
2372 __table_args__ = (
2373 # used in looking up which notifications need delivery
2374 Index(
2375 "ix_notifications_created",
2376 created,
2377 ),
2378 )
2380 @property
2381 def topic(self):
2382 return self.topic_action.topic
2384 @property
2385 def action(self):
2386 return self.topic_action.action
2389class NotificationDelivery(Base):
2390 __tablename__ = "notification_deliveries"
2392 id = Column(BigInteger, primary_key=True)
2393 notification_id = Column(ForeignKey("notifications.id"), nullable=False, index=True)
2394 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2395 delivered = Column(DateTime(timezone=True), nullable=True)
2396 read = Column(DateTime(timezone=True), nullable=True)
2397 # todo: enum of "phone, web, digest"
2398 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2399 # todo: device id
2400 # todo: receipt id, etc
2401 notification = relationship("Notification", foreign_keys="NotificationDelivery.notification_id")
2403 __table_args__ = (
2404 UniqueConstraint("notification_id", "delivery_type"),
2405 # used in looking up which notifications need delivery
2406 Index(
2407 "ix_notification_deliveries_delivery_type",
2408 delivery_type,
2409 postgresql_where=(delivered != None),
2410 ),
2411 Index(
2412 "ix_notification_deliveries_dt_ni_dnull",
2413 delivery_type,
2414 notification_id,
2415 delivered == None,
2416 ),
2417 )
2420class PushNotificationSubscription(Base):
2421 __tablename__ = "push_notification_subscriptions"
2423 id = Column(BigInteger, primary_key=True)
2424 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2426 # which user this is connected to
2427 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2429 # these come from https://developer.mozilla.org/en-US/docs/Web/API/PushSubscription
2430 # the endpoint
2431 endpoint = Column(String, nullable=False)
2432 # the "auth" key
2433 auth_key = Column(Binary, nullable=False)
2434 # the "p256dh" key
2435 p256dh_key = Column(Binary, nullable=False)
2437 full_subscription_info = Column(String, nullable=False)
2439 # the browse user-agent, so we can tell the user what browser notifications are going to
2440 user_agent = Column(String, nullable=True)
2442 # when it was disabled
2443 disabled_at = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_INFINITY.isoformat())
2445 user = relationship("User")
2448class PushNotificationDeliveryAttempt(Base):
2449 __tablename__ = "push_notification_delivery_attempt"
2451 id = Column(BigInteger, primary_key=True)
2452 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2454 push_notification_subscription_id = Column(
2455 ForeignKey("push_notification_subscriptions.id"), nullable=False, index=True
2456 )
2458 success = Column(Boolean, nullable=False)
2459 # the HTTP status code, 201 is success
2460 status_code = Column(Integer, nullable=False)
2462 # can be null if it was a success
2463 response = Column(String, nullable=True)
2465 push_notification_subscription = relationship("PushNotificationSubscription")
2468class Language(Base):
2469 """
2470 Table of allowed languages (a subset of ISO639-3)
2471 """
2473 __tablename__ = "languages"
2475 # ISO639-3 language code, in lowercase, e.g. fin, eng
2476 code = Column(String(3), primary_key=True)
2478 # the english name
2479 name = Column(String, nullable=False, unique=True)
2482class Region(Base):
2483 """
2484 Table of regions
2485 """
2487 __tablename__ = "regions"
2489 # iso 3166-1 alpha3 code in uppercase, e.g. FIN, USA
2490 code = Column(String(3), primary_key=True)
2492 # the name, e.g. Finland, United States
2493 # this is the display name in English, should be the "common name", not "Republic of Finland"
2494 name = Column(String, nullable=False, unique=True)
2497class UserBlock(Base):
2498 """
2499 Table of blocked users
2500 """
2502 __tablename__ = "user_blocks"
2503 __table_args__ = (UniqueConstraint("blocking_user_id", "blocked_user_id"),)
2505 id = Column(BigInteger, primary_key=True)
2507 blocking_user_id = Column(ForeignKey("users.id"), nullable=False)
2508 blocked_user_id = Column(ForeignKey("users.id"), nullable=False)
2509 time_blocked = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2511 blocking_user = relationship("User", foreign_keys="UserBlock.blocking_user_id")
2512 blocked_user = relationship("User", foreign_keys="UserBlock.blocked_user_id")
2515class APICall(Base):
2516 """
2517 API call logs
2518 """
2520 __tablename__ = "api_calls"
2521 __table_args__ = {"schema": "logging"}
2523 id = Column(BigInteger, primary_key=True)
2525 # whether the call was made using an api key or session cookies
2526 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))
2528 # backend version (normally e.g. develop-31469e3), allows us to figure out which proto definitions were used
2529 # note that `default` is a python side default, not hardcoded into DB schema
2530 version = Column(String, nullable=False, default=config["VERSION"])
2532 # approximate time of the call
2533 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2535 # the method call name, e.g. "/org.couchers.api.core.API/ListFriends"
2536 method = Column(String, nullable=False)
2538 # gRPC status code name, e.g. FAILED_PRECONDITION, None if success
2539 status_code = Column(String, nullable=True)
2541 # handler duration (excluding serialization, etc)
2542 duration = Column(Float, nullable=False)
2544 # user_id of caller, None means not logged in
2545 user_id = Column(BigInteger, nullable=True)
2547 # sanitized request bytes
2548 request = Column(Binary, nullable=True)
2550 # sanitized response bytes
2551 response = Column(Binary, nullable=True)
2553 # whether response bytes have been truncated
2554 response_truncated = Column(Boolean, nullable=False, server_default=text("false"))
2556 # the exception traceback, if any
2557 traceback = Column(String, nullable=True)
2559 # human readable perf report
2560 perf_report = Column(String, nullable=True)
2562 # details of the browser, if available
2563 ip_address = Column(String, nullable=True)
2564 user_agent = Column(String, nullable=True)
2567class AccountDeletionReason(Base):
2568 __tablename__ = "account_deletion_reason"
2570 id = Column(BigInteger, primary_key=True)
2571 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2572 user_id = Column(ForeignKey("users.id"), nullable=False)
2573 reason = Column(String, nullable=True)
2575 user = relationship("User")