Coverage for src/couchers/models.py: 99%
1112 statements
« prev ^ index » next coverage.py v7.6.10, created at 2025-03-24 14:08 +0000
« prev ^ index » next coverage.py v7.6.10, created at 2025-03-24 14:08 +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 jailed_missing_tos(self):
360 return self.accepted_tos < TOS_VERSION
362 @hybrid_property
363 def jailed_missing_community_guidelines(self):
364 return self.accepted_community_guidelines < GUIDELINES_VERSION
366 @hybrid_property
367 def jailed_pending_mod_notes(self):
368 return self.mod_notes.where(ModNote.is_pending).count() > 0
370 @hybrid_property
371 def jailed_pending_activeness_probe(self):
372 return self.pending_activeness_probe != None
374 @hybrid_property
375 def is_jailed(self):
376 return (
377 self.jailed_missing_tos
378 | self.jailed_missing_community_guidelines
379 | self.is_missing_location
380 | self.jailed_pending_mod_notes
381 | self.jailed_pending_activeness_probe
382 )
384 @hybrid_property
385 def is_missing_location(self):
386 return (self.geom == None) | (self.geom_radius == None)
388 @hybrid_property
389 def is_visible(self):
390 return ~(self.is_banned | self.is_deleted)
392 @property
393 def coordinates(self):
394 return get_coordinates(self.geom)
396 @property
397 def display_joined(self):
398 """
399 Returns the last active time rounded down to the nearest hour.
400 """
401 return self.joined.replace(minute=0, second=0, microsecond=0)
403 @property
404 def display_last_active(self):
405 """
406 Returns the last active time rounded down whatever is the "last active" coarsening.
407 """
408 return last_active_coarsen(self.last_active)
410 @hybrid_property
411 def phone_is_verified(self):
412 return (
413 self.phone_verification_verified is not None
414 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
415 )
417 @phone_is_verified.expression
418 def phone_is_verified(cls):
419 return (cls.phone_verification_verified != None) & (
420 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
421 )
423 @hybrid_property
424 def phone_code_expired(self):
425 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME
427 def __repr__(self):
428 return f"User(id={self.id}, email={self.email}, username={self.username})"
431class UserBadge(Base):
432 """
433 A badge on a user's profile
434 """
436 __tablename__ = "user_badges"
437 __table_args__ = (UniqueConstraint("user_id", "badge_id"),)
439 id = Column(BigInteger, primary_key=True)
441 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
442 # corresponds to "id" in badges.json
443 badge_id = Column(String, nullable=False, index=True)
445 # take this with a grain of salt, someone may get then lose a badge for whatever reason
446 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
448 user = relationship("User", backref="badges")
451class ActivenessProbeStatus(enum.Enum):
452 # no response yet
453 pending = enum.auto()
455 # didn't respond on time
456 expired = enum.auto()
458 # responded that they're still active
459 still_active = enum.auto()
461 # responded that they're no longer active
462 no_longer_active = enum.auto()
465class ActivenessProbe(Base):
466 """
467 Activeness probes are used to gauge if users are still active: we send them a notification and ask them to respond,
468 we use this data both to help indicate response rate, as well as to make sure only those who are actively hosting
469 show up as such.
470 """
472 __tablename__ = "activeness_probes"
474 id = Column(BigInteger, primary_key=True)
476 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
477 # the time this probe was initiated
478 probe_initiated = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
479 # the number of reminders sent for this probe
480 notifications_sent = Column(Integer, nullable=False, server_default="0")
482 # the time of response
483 responded = Column(DateTime(timezone=True), nullable=True, default=None)
484 # the response value
485 response = Column(Enum(ActivenessProbeStatus), nullable=False, default=ActivenessProbeStatus.pending)
487 @hybrid_property
488 def is_pending(self):
489 return self.responded == None
491 user = relationship("User", back_populates="pending_activeness_probe")
493 __table_args__ = (
494 # a user can have at most one pending activeness probe at a time
495 Index(
496 "ix_activeness_probe_unique_pending_response",
497 user_id,
498 unique=True,
499 postgresql_where=responded == None,
500 ),
501 # response time is none iff response is pending
502 CheckConstraint(
503 "(responded IS NULL AND response = 'pending') OR (responded IS NOT NULL AND response != 'pending')",
504 name="pending_has_no_responded",
505 ),
506 )
509User.pending_activeness_probe = relationship(
510 ActivenessProbe,
511 primaryjoin="and_(ActivenessProbe.user_id == User.id, ActivenessProbe.is_pending)",
512 uselist=False,
513 back_populates="user",
514)
517class StrongVerificationAttemptStatus(enum.Enum):
518 ## full data states
519 # completed, this now provides verification for a user
520 succeeded = enum.auto()
522 ## no data states
523 # in progress: waiting for the user to scan the Iris code or open the app
524 in_progress_waiting_on_user_to_open_app = enum.auto()
525 # in progress: waiting for the user to scan MRZ or NFC/chip
526 in_progress_waiting_on_user_in_app = enum.auto()
527 # in progress, waiting for backend to pull verification data
528 in_progress_waiting_on_backend = enum.auto()
529 # failed, no data
530 failed = enum.auto()
532 # duplicate, at our end, has data
533 duplicate = enum.auto()
535 ## minimal data states
536 # the data, except minimal deduplication data, was deleted
537 deleted = enum.auto()
540class PassportSex(enum.Enum):
541 """
542 We don't care about sex, we use gender on the platform. But passports apparently do.
543 """
545 male = enum.auto()
546 female = enum.auto()
547 unspecified = enum.auto()
550class StrongVerificationAttempt(Base):
551 """
552 An attempt to perform strong verification
553 """
555 __tablename__ = "strong_verification_attempts"
557 # our verification id
558 id = Column(BigInteger, primary_key=True)
560 # this is returned in the callback, and we look up the attempt via this
561 verification_attempt_token = Column(String, nullable=False, unique=True)
563 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
564 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
566 status = Column(
567 Enum(StrongVerificationAttemptStatus),
568 nullable=False,
569 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app,
570 )
572 ## full data
573 has_full_data = Column(Boolean, nullable=False, default=False)
574 # the data returned from iris, encrypted with a public key whose private key is kept offline
575 passport_encrypted_data = Column(Binary, nullable=True)
576 passport_date_of_birth = Column(Date, nullable=True)
577 passport_sex = Column(Enum(PassportSex), nullable=True)
579 ## minimal data: this will not be deleted
580 has_minimal_data = Column(Boolean, nullable=False, default=False)
581 passport_expiry_date = Column(Date, nullable=True)
582 passport_nationality = Column(String, nullable=True)
583 # last three characters of the passport number
584 passport_last_three_document_chars = Column(String, nullable=True)
586 iris_token = Column(String, nullable=False, unique=True)
587 iris_session_id = Column(BigInteger, nullable=False, unique=True)
589 passport_expiry_datetime = column_property(date_in_timezone(passport_expiry_date, "Etc/UTC"))
591 user = relationship("User")
593 @hybrid_property
594 def is_valid(self):
595 """
596 This only checks whether the attempt is a success and the passport is not expired, use `has_strong_verification` for full check
597 """
598 return (self.status == StrongVerificationAttemptStatus.succeeded) and (self.passport_expiry_datetime >= now())
600 @is_valid.expression
601 def is_valid(cls):
602 return (cls.status == StrongVerificationAttemptStatus.succeeded) & (
603 func.coalesce(cls.passport_expiry_datetime >= func.now(), False)
604 )
606 @hybrid_property
607 def is_visible(self):
608 return self.status != StrongVerificationAttemptStatus.deleted
610 @hybrid_method
611 def _raw_birthdate_match(self, user):
612 """Does not check whether the SV attempt itself is not expired"""
613 return self.passport_date_of_birth == user.birthdate
615 @hybrid_method
616 def matches_birthdate(self, user):
617 return self.is_valid & self._raw_birthdate_match(user)
619 @hybrid_method
620 def _raw_gender_match(self, user):
621 """Does not check whether the SV attempt itself is not expired"""
622 return (
623 ((user.gender == "Woman") & (self.passport_sex == PassportSex.female))
624 | ((user.gender == "Man") & (self.passport_sex == PassportSex.male))
625 | (self.passport_sex == PassportSex.unspecified)
626 | (user.has_passport_sex_gender_exception == True)
627 )
629 @hybrid_method
630 def matches_gender(self, user):
631 return self.is_valid & self._raw_gender_match(user)
633 @hybrid_method
634 def has_strong_verification(self, user):
635 return self.is_valid & self._raw_birthdate_match(user) & self._raw_gender_match(user)
637 __table_args__ = (
638 # used to look up verification status for a user
639 Index(
640 "ix_strong_verification_attempts_current",
641 user_id,
642 passport_expiry_date,
643 postgresql_where=status == StrongVerificationAttemptStatus.succeeded,
644 ),
645 # each passport can be verified only once
646 Index(
647 "ix_strong_verification_attempts_unique_succeeded",
648 passport_expiry_date,
649 passport_nationality,
650 passport_last_three_document_chars,
651 unique=True,
652 postgresql_where=(
653 (status == StrongVerificationAttemptStatus.succeeded)
654 | (status == StrongVerificationAttemptStatus.deleted)
655 ),
656 ),
657 # full data check
658 CheckConstraint(
659 "(has_full_data IS TRUE AND passport_encrypted_data IS NOT NULL AND passport_date_of_birth IS NOT NULL) OR \
660 (has_full_data IS FALSE AND passport_encrypted_data IS NULL AND passport_date_of_birth IS NULL)",
661 name="full_data_status",
662 ),
663 # minimal data check
664 CheckConstraint(
665 "(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 \
666 (has_minimal_data IS FALSE AND passport_expiry_date IS NULL AND passport_nationality IS NULL AND passport_last_three_document_chars IS NULL)",
667 name="minimal_data_status",
668 ),
669 # note on implications: p => q iff ~p OR q
670 # full data implies minimal data, has_minimal_data => has_full_data
671 CheckConstraint(
672 "(has_full_data IS FALSE) OR (has_minimal_data IS TRUE)",
673 name="full_data_implies_minimal_data",
674 ),
675 # succeeded implies full data
676 CheckConstraint(
677 "(NOT (status = 'succeeded')) OR (has_full_data IS TRUE)",
678 name="succeeded_implies_full_data",
679 ),
680 # in_progress/failed implies no_data
681 CheckConstraint(
682 "(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)",
683 name="in_progress_failed_iris_implies_no_data",
684 ),
685 # deleted or duplicate implies minimal data
686 CheckConstraint(
687 "(NOT ((status = 'deleted') OR (status = 'duplicate'))) OR (has_minimal_data IS TRUE)",
688 name="deleted_duplicate_implies_minimal_data",
689 ),
690 )
693class ModNote(Base):
694 """
695 A moderator note to a user. This could be a warning, just a note "hey, we did X", or any other similar message.
697 The user has to read and "acknowledge" the note before continuing onto the platform, and being un-jailed.
698 """
700 __tablename__ = "mod_notes"
701 id = Column(BigInteger, primary_key=True)
703 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
705 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
706 acknowledged = Column(DateTime(timezone=True), nullable=True)
708 # this is an internal ID to allow the mods to track different types of notes
709 internal_id = Column(String, nullable=False)
710 # the admin that left this note
711 creator_user_id = Column(ForeignKey("users.id"), nullable=False)
713 note_content = Column(String, nullable=False) # CommonMark without images
715 user = relationship("User", backref=backref("mod_notes", lazy="dynamic"), foreign_keys="ModNote.user_id")
717 def __repr__(self):
718 return f"ModeNote(id={self.id}, user={self.user}, created={self.created}, ack'd={self.acknowledged})"
720 @hybrid_property
721 def is_pending(self):
722 return self.acknowledged == None
724 __table_args__ = (
725 # used to look up pending notes
726 Index(
727 "ix_mod_notes_unacknowledged",
728 user_id,
729 postgresql_where=acknowledged == None,
730 ),
731 )
734class StrongVerificationCallbackEvent(Base):
735 __tablename__ = "strong_verification_callback_events"
737 id = Column(BigInteger, primary_key=True)
738 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
740 verification_attempt_id = Column(ForeignKey("strong_verification_attempts.id"), nullable=False, index=True)
742 iris_status = Column(String, nullable=False)
745class DonationType(enum.Enum):
746 one_time = enum.auto()
747 recurring = enum.auto()
750class DonationInitiation(Base):
751 """
752 Whenever someone initiaties a donation through the platform
753 """
755 __tablename__ = "donation_initiations"
756 id = Column(BigInteger, primary_key=True)
758 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
759 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
761 amount = Column(Integer, nullable=False)
762 stripe_checkout_session_id = Column(String, nullable=False)
764 donation_type = Column(Enum(DonationType), nullable=False)
766 user = relationship("User", backref="donation_initiations")
769class Invoice(Base):
770 """
771 Successful donations, both one off and recurring
773 Triggered by `payment_intent.succeeded` webhook
774 """
776 __tablename__ = "invoices"
778 id = Column(BigInteger, primary_key=True)
779 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
780 user_id = Column(ForeignKey("users.id"), nullable=False)
782 amount = Column(Float, nullable=False)
784 stripe_payment_intent_id = Column(String, nullable=False, unique=True)
785 stripe_receipt_url = Column(String, nullable=False)
787 user = relationship("User", backref="invoices")
790class LanguageFluency(enum.Enum):
791 # note that the numbering is important here, these are ordinal
792 beginner = 1
793 conversational = 2
794 fluent = 3
797class LanguageAbility(Base):
798 __tablename__ = "language_abilities"
799 __table_args__ = (
800 # Users can only have one language ability per language
801 UniqueConstraint("user_id", "language_code"),
802 )
804 id = Column(BigInteger, primary_key=True)
805 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
806 language_code = Column(ForeignKey("languages.code", deferrable=True), nullable=False)
807 fluency = Column(Enum(LanguageFluency), nullable=False)
809 user = relationship("User", backref="language_abilities")
810 language = relationship("Language")
813class RegionVisited(Base):
814 __tablename__ = "regions_visited"
815 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
817 id = Column(BigInteger, primary_key=True)
818 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
819 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
822class RegionLived(Base):
823 __tablename__ = "regions_lived"
824 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
826 id = Column(BigInteger, primary_key=True)
827 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
828 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
831class FriendStatus(enum.Enum):
832 pending = enum.auto()
833 accepted = enum.auto()
834 rejected = enum.auto()
835 cancelled = enum.auto()
838class FriendRelationship(Base):
839 """
840 Friendship relations between users
842 TODO: make this better with sqlalchemy self-referential stuff
843 TODO: constraint on only one row per user pair where accepted or pending
844 """
846 __tablename__ = "friend_relationships"
848 id = Column(BigInteger, primary_key=True)
850 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
851 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
853 status = Column(Enum(FriendStatus), nullable=False, default=FriendStatus.pending)
855 # timezones should always be UTC
856 time_sent = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
857 time_responded = Column(DateTime(timezone=True), nullable=True)
859 from_user = relationship("User", backref="friends_from", foreign_keys="FriendRelationship.from_user_id")
860 to_user = relationship("User", backref="friends_to", foreign_keys="FriendRelationship.to_user_id")
863class ContributeOption(enum.Enum):
864 yes = enum.auto()
865 maybe = enum.auto()
866 no = enum.auto()
869class ContributorForm(Base):
870 """
871 Someone filled in the contributor form
872 """
874 __tablename__ = "contributor_forms"
876 id = Column(BigInteger, primary_key=True)
878 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
879 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
881 ideas = Column(String, nullable=True)
882 features = Column(String, nullable=True)
883 experience = Column(String, nullable=True)
884 contribute = Column(Enum(ContributeOption), nullable=True)
885 contribute_ways = Column(ARRAY(String), nullable=False)
886 expertise = Column(String, nullable=True)
888 user = relationship("User", backref="contributor_forms")
890 @hybrid_property
891 def is_filled(self):
892 """
893 Whether the form counts as having been filled
894 """
895 return (
896 (self.ideas != None)
897 | (self.features != None)
898 | (self.experience != None)
899 | (self.contribute != None)
900 | (self.contribute_ways != [])
901 | (self.expertise != None)
902 )
904 @property
905 def should_notify(self):
906 """
907 If this evaluates to true, we send an email to the recruitment team.
909 We currently send if expertise is listed, or if they list a way to help outside of a set list
910 """
911 return (self.expertise != None) | (not set(self.contribute_ways).issubset({"community", "blog", "other"}))
914class SignupFlow(Base):
915 """
916 Signup flows/incomplete users
918 Coinciding fields have the same meaning as in User
919 """
921 __tablename__ = "signup_flows"
923 id = Column(BigInteger, primary_key=True)
925 # housekeeping
926 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
927 flow_token = Column(String, nullable=False, unique=True)
928 email_verified = Column(Boolean, nullable=False, default=False)
929 email_sent = Column(Boolean, nullable=False, default=False)
930 email_token = Column(String, nullable=True)
931 email_token_expiry = Column(DateTime(timezone=True), nullable=True)
933 ## Basic
934 name = Column(String, nullable=False)
935 # TODO: unique across both tables
936 email = Column(String, nullable=False, unique=True)
937 # TODO: invitation, attribution
939 ## Account
940 # TODO: unique across both tables
941 username = Column(String, nullable=True, unique=True)
942 hashed_password = Column(Binary, nullable=True)
943 birthdate = Column(Date, nullable=True) # in the timezone of birthplace
944 gender = Column(String, nullable=True)
945 hosting_status = Column(Enum(HostingStatus), nullable=True)
946 city = Column(String, nullable=True)
947 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
948 geom_radius = Column(Float, nullable=True)
950 accepted_tos = Column(Integer, nullable=True)
951 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")
953 opt_out_of_newsletter = Column(Boolean, nullable=True)
955 ## Feedback
956 filled_feedback = Column(Boolean, nullable=False, default=False)
957 ideas = Column(String, nullable=True)
958 features = Column(String, nullable=True)
959 experience = Column(String, nullable=True)
960 contribute = Column(Enum(ContributeOption), nullable=True)
961 contribute_ways = Column(ARRAY(String), nullable=True)
962 expertise = Column(String, nullable=True)
964 @hybrid_property
965 def token_is_valid(self):
966 return (self.email_token != None) & (self.email_token_expiry >= now())
968 @hybrid_property
969 def account_is_filled(self):
970 return (
971 (self.username != None)
972 & (self.birthdate != None)
973 & (self.gender != None)
974 & (self.hosting_status != None)
975 & (self.city != None)
976 & (self.geom != None)
977 & (self.geom_radius != None)
978 & (self.accepted_tos != None)
979 & (self.opt_out_of_newsletter != None)
980 )
982 @hybrid_property
983 def is_completed(self):
984 return (
985 self.email_verified
986 & self.account_is_filled
987 & self.filled_feedback
988 & (self.accepted_community_guidelines == GUIDELINES_VERSION)
989 )
992class LoginToken(Base):
993 """
994 A login token sent in an email to a user, allows them to sign in between the times defined by created and expiry
995 """
997 __tablename__ = "login_tokens"
998 token = Column(String, primary_key=True)
1000 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1002 # timezones should always be UTC
1003 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1004 expiry = Column(DateTime(timezone=True), nullable=False)
1006 user = relationship("User", backref="login_tokens")
1008 @hybrid_property
1009 def is_valid(self):
1010 return (self.created <= now()) & (self.expiry >= now())
1012 def __repr__(self):
1013 return f"LoginToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
1016class PasswordResetToken(Base):
1017 __tablename__ = "password_reset_tokens"
1018 token = Column(String, primary_key=True)
1020 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1022 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1023 expiry = Column(DateTime(timezone=True), nullable=False)
1025 user = relationship("User", backref="password_reset_tokens")
1027 @hybrid_property
1028 def is_valid(self):
1029 return (self.created <= now()) & (self.expiry >= now())
1031 def __repr__(self):
1032 return f"PasswordResetToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
1035class AccountDeletionToken(Base):
1036 __tablename__ = "account_deletion_tokens"
1038 token = Column(String, primary_key=True)
1040 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1042 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1043 expiry = Column(DateTime(timezone=True), nullable=False)
1045 user = relationship("User", backref="account_deletion_tokens")
1047 @hybrid_property
1048 def is_valid(self):
1049 return (self.created <= now()) & (self.expiry >= now())
1051 def __repr__(self):
1052 return f"AccountDeletionToken(token={self.token}, user_id={self.user_id}, created={self.created}, expiry={self.expiry})"
1055class UserActivity(Base):
1056 """
1057 User activity: for each unique (user_id, period, ip_address, user_agent) tuple, keep track of number of api calls
1059 Used for user "last active" as well as admin stuff
1060 """
1062 __tablename__ = "user_activity"
1064 id = Column(BigInteger, primary_key=True)
1066 user_id = Column(ForeignKey("users.id"), nullable=False)
1067 # the start of a period of time, e.g. 1 hour during which we bin activeness
1068 period = Column(DateTime(timezone=True), nullable=False)
1070 # details of the browser, if available
1071 ip_address = Column(INET, nullable=True)
1072 user_agent = Column(String, nullable=True)
1074 # count of api calls made with this ip, user_agent, and period
1075 api_calls = Column(Integer, nullable=False, default=0)
1077 __table_args__ = (
1078 # helps look up this tuple quickly
1079 Index(
1080 "ix_user_activity_user_id_period_ip_address_user_agent",
1081 user_id,
1082 period,
1083 ip_address,
1084 user_agent,
1085 unique=True,
1086 ),
1087 )
1090class UserSession(Base):
1091 """
1092 API keys/session cookies for the app
1094 There are two types of sessions: long-lived, and short-lived. Long-lived are
1095 like when you choose "remember this browser": they will be valid for a long
1096 time without the user interacting with the site. Short-lived sessions on the
1097 other hand get invalidated quickly if the user does not interact with the
1098 site.
1100 Long-lived tokens are valid from `created` until `expiry`.
1102 Short-lived tokens expire after 168 hours (7 days) if they are not used.
1103 """
1105 __tablename__ = "sessions"
1106 token = Column(String, primary_key=True)
1108 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1110 # sessions are either "api keys" or "session cookies", otherwise identical
1111 # an api key is put in the authorization header (e.g. "authorization: Bearer <token>")
1112 # a session cookie is set in the "couchers-sesh" cookie (e.g. "cookie: couchers-sesh=<token>")
1113 # when a session is created, it's fixed as one or the other for security reasons
1114 # for api keys to be useful, they should be long lived and have a long expiry
1115 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))
1117 # whether it's a long-lived or short-lived session
1118 long_lived = Column(Boolean, nullable=False)
1120 # the time at which the session was created
1121 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1123 # the expiry of the session: the session *cannot* be refreshed past this
1124 expiry = Column(DateTime(timezone=True), nullable=False, server_default=func.now() + text("interval '730 days'"))
1126 # the time at which the token was invalidated, allows users to delete sessions
1127 deleted = Column(DateTime(timezone=True), nullable=True, default=None)
1129 # the last time this session was used
1130 last_seen = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1132 # count of api calls made with this token/session (if we're updating last_seen, might as well update this too)
1133 api_calls = Column(Integer, nullable=False, default=0)
1135 # details of the browser, if available
1136 # these are from the request creating the session, not used for anything else
1137 ip_address = Column(String, nullable=True)
1138 user_agent = Column(String, nullable=True)
1140 user = relationship("User", backref="sessions")
1142 @hybrid_property
1143 def is_valid(self):
1144 """
1145 It must have been created and not be expired or deleted.
1147 Also, if it's a short lived token, it must have been used in the last 168 hours.
1149 TODO: this probably won't run in python (instance level), only in sql (class level)
1150 """
1151 return (
1152 (self.created <= func.now())
1153 & (self.expiry >= func.now())
1154 & (self.deleted == None)
1155 & (self.long_lived | (func.now() - self.last_seen < text("interval '168 hours'")))
1156 )
1159class Conversation(Base):
1160 """
1161 Conversation brings together the different types of message/conversation types
1162 """
1164 __tablename__ = "conversations"
1166 id = Column(BigInteger, primary_key=True)
1167 # timezone should always be UTC
1168 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1170 def __repr__(self):
1171 return f"Conversation(id={self.id}, created={self.created})"
1174class GroupChat(Base):
1175 """
1176 Group chat
1177 """
1179 __tablename__ = "group_chats"
1181 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1183 title = Column(String, nullable=True)
1184 only_admins_invite = Column(Boolean, nullable=False, default=True)
1185 creator_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1186 is_dm = Column(Boolean, nullable=False)
1188 conversation = relationship("Conversation", backref="group_chat")
1189 creator = relationship("User", backref="created_group_chats")
1191 def __repr__(self):
1192 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})"
1195class GroupChatRole(enum.Enum):
1196 admin = enum.auto()
1197 participant = enum.auto()
1200class GroupChatSubscription(Base):
1201 """
1202 The recipient of a thread and information about when they joined/left/etc.
1203 """
1205 __tablename__ = "group_chat_subscriptions"
1206 id = Column(BigInteger, primary_key=True)
1208 # TODO: DB constraint on only one user+group_chat combo at a given time
1209 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1210 group_chat_id = Column(ForeignKey("group_chats.id"), nullable=False, index=True)
1212 # timezones should always be UTC
1213 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1214 left = Column(DateTime(timezone=True), nullable=True)
1216 role = Column(Enum(GroupChatRole), nullable=False)
1218 last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1220 # when this chat is muted until, DATETIME_INFINITY for "forever"
1221 muted_until = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_MINUS_INFINITY.isoformat())
1223 user = relationship("User", backref="group_chat_subscriptions")
1224 group_chat = relationship("GroupChat", backref=backref("subscriptions", lazy="dynamic"))
1226 def muted_display(self):
1227 """
1228 Returns (muted, muted_until) display values:
1229 1. If not muted, returns (False, None)
1230 2. If muted forever, returns (True, None)
1231 3. If muted until a given datetime returns (True, dt)
1232 """
1233 if self.muted_until < now():
1234 return (False, None)
1235 elif self.muted_until == DATETIME_INFINITY:
1236 return (True, None)
1237 else:
1238 return (True, self.muted_until)
1240 @hybrid_property
1241 def is_muted(self):
1242 return self.muted_until > func.now()
1244 def __repr__(self):
1245 return f"GroupChatSubscription(id={self.id}, user={self.user}, joined={self.joined}, left={self.left}, role={self.role}, group_chat={self.group_chat})"
1248class MessageType(enum.Enum):
1249 text = enum.auto()
1250 # e.g.
1251 # image =
1252 # emoji =
1253 # ...
1254 chat_created = enum.auto()
1255 chat_edited = enum.auto()
1256 user_invited = enum.auto()
1257 user_left = enum.auto()
1258 user_made_admin = enum.auto()
1259 user_removed_admin = enum.auto() # RemoveGroupChatAdmin: remove admin permission from a user in group chat
1260 host_request_status_changed = enum.auto()
1261 user_removed = enum.auto() # user is removed from group chat by amdin RemoveGroupChatUser
1264class HostRequestStatus(enum.Enum):
1265 pending = enum.auto()
1266 accepted = enum.auto()
1267 rejected = enum.auto()
1268 confirmed = enum.auto()
1269 cancelled = enum.auto()
1272class Message(Base):
1273 """
1274 A message.
1276 If message_type = text, then the message is a normal text message, otherwise, it's a special control message.
1277 """
1279 __tablename__ = "messages"
1281 id = Column(BigInteger, primary_key=True)
1283 # which conversation the message belongs in
1284 conversation_id = Column(ForeignKey("conversations.id"), nullable=False, index=True)
1286 # the user that sent the message/command
1287 author_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1289 # the message type, "text" is a text message, otherwise a "control message"
1290 message_type = Column(Enum(MessageType), nullable=False)
1292 # the target if a control message and requires target, e.g. if inviting a user, the user invited is the target
1293 target_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1295 # time sent, timezone should always be UTC
1296 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1298 # the plain-text message text if not control
1299 text = Column(String, nullable=True)
1301 # the new host request status if the message type is host_request_status_changed
1302 host_request_status_target = Column(Enum(HostRequestStatus), nullable=True)
1304 conversation = relationship("Conversation", backref="messages", order_by="Message.time.desc()")
1305 author = relationship("User", foreign_keys="Message.author_id")
1306 target = relationship("User", foreign_keys="Message.target_id")
1308 @property
1309 def is_normal_message(self):
1310 """
1311 There's only one normal type atm, text
1312 """
1313 return self.message_type == MessageType.text
1315 def __repr__(self):
1316 return f"Message(id={self.id}, time={self.time}, text={self.text}, author={self.author}, conversation={self.conversation})"
1319class ContentReport(Base):
1320 """
1321 A piece of content reported to admins
1322 """
1324 __tablename__ = "content_reports"
1326 id = Column(BigInteger, primary_key=True)
1328 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1330 # the user who reported or flagged the content
1331 reporting_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1333 # reason, e.g. spam, inappropriate, etc
1334 reason = Column(String, nullable=False)
1335 # a short description
1336 description = Column(String, nullable=False)
1338 # a reference to the content, see //docs/content_ref.md
1339 content_ref = Column(String, nullable=False)
1340 # the author of the content (e.g. the user who wrote the comment itself)
1341 author_user_id = Column(ForeignKey("users.id"), nullable=False)
1343 # details of the browser, if available
1344 user_agent = Column(String, nullable=False)
1345 # the URL the user was on when reporting the content
1346 page = Column(String, nullable=False)
1348 # see comments above for reporting vs author
1349 reporting_user = relationship("User", foreign_keys="ContentReport.reporting_user_id")
1350 author_user = relationship("User", foreign_keys="ContentReport.author_user_id")
1353class Email(Base):
1354 """
1355 Table of all dispatched emails for debugging purposes, etc.
1356 """
1358 __tablename__ = "emails"
1360 id = Column(String, primary_key=True)
1362 # timezone should always be UTC
1363 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1365 sender_name = Column(String, nullable=False)
1366 sender_email = Column(String, nullable=False)
1368 recipient = Column(String, nullable=False)
1369 subject = Column(String, nullable=False)
1371 plain = Column(String, nullable=False)
1372 html = Column(String, nullable=False)
1374 list_unsubscribe_header = Column(String, nullable=True)
1375 source_data = Column(String, nullable=True)
1378class SMS(Base):
1379 """
1380 Table of all sent SMSs for debugging purposes, etc.
1381 """
1383 __tablename__ = "smss"
1385 id = Column(BigInteger, primary_key=True)
1387 # timezone should always be UTC
1388 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1389 # AWS message id
1390 message_id = Column(String, nullable=False)
1392 # the SMS sender ID sent to AWS, name that the SMS appears to come from
1393 sms_sender_id = Column(String, nullable=False)
1394 number = Column(String, nullable=False)
1395 message = Column(String, nullable=False)
1398class HostRequest(Base):
1399 """
1400 A request to stay with a host
1401 """
1403 __tablename__ = "host_requests"
1405 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1406 surfer_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1407 host_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1409 # TODO: proper timezone handling
1410 timezone = "Etc/UTC"
1412 # dates in the timezone above
1413 from_date = Column(Date, nullable=False)
1414 to_date = Column(Date, nullable=False)
1416 # timezone aware start and end times of the request, can be compared to now()
1417 start_time = column_property(date_in_timezone(from_date, timezone))
1418 end_time = column_property(date_in_timezone(to_date, timezone) + text("interval '1 days'"))
1419 start_time_to_write_reference = column_property(date_in_timezone(to_date, timezone))
1420 # notice 1 day for midnight at the *end of the day*, then 14 days to write a ref
1421 end_time_to_write_reference = column_property(date_in_timezone(to_date, timezone) + text("interval '15 days'"))
1423 status = Column(Enum(HostRequestStatus), nullable=False)
1425 host_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1426 surfer_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1428 # number of reference reminders sent out
1429 host_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1430 surfer_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1432 # reason why the host/surfer marked that they didn't meet up
1433 # if null then they haven't marked it such
1434 host_reason_didnt_meetup = Column(String, nullable=True)
1435 surfer_reason_didnt_meetup = Column(String, nullable=True)
1437 surfer = relationship("User", backref="host_requests_sent", foreign_keys="HostRequest.surfer_user_id")
1438 host = relationship("User", backref="host_requests_received", foreign_keys="HostRequest.host_user_id")
1439 conversation = relationship("Conversation")
1441 __table_args__ = (
1442 # allows fast lookup as to whether they didn't meet up
1443 Index(
1444 "ix_host_requests_host_didnt_meetup",
1445 host_reason_didnt_meetup != None,
1446 ),
1447 Index(
1448 "ix_host_requests_surfer_didnt_meetup",
1449 surfer_reason_didnt_meetup != None,
1450 ),
1451 )
1453 @hybrid_property
1454 def can_write_reference(self):
1455 return (
1456 ((self.status == HostRequestStatus.confirmed) | (self.status == HostRequestStatus.accepted))
1457 & (now() >= self.start_time_to_write_reference)
1458 & (now() <= self.end_time_to_write_reference)
1459 )
1461 @can_write_reference.expression
1462 def can_write_reference(cls):
1463 return (
1464 ((cls.status == HostRequestStatus.confirmed) | (cls.status == HostRequestStatus.accepted))
1465 & (func.now() >= cls.start_time_to_write_reference)
1466 & (func.now() <= cls.end_time_to_write_reference)
1467 )
1469 def __repr__(self):
1470 return f"HostRequest(id={self.conversation_id}, surfer_user_id={self.surfer_user_id}, host_user_id={self.host_user_id}...)"
1473class ReferenceType(enum.Enum):
1474 friend = enum.auto()
1475 surfed = enum.auto() # The "from" user surfed with the "to" user
1476 hosted = enum.auto() # The "from" user hosted the "to" user
1479class Reference(Base):
1480 """
1481 Reference from one user to another
1482 """
1484 __tablename__ = "references"
1486 id = Column(BigInteger, primary_key=True)
1487 # timezone should always be UTC
1488 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1490 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1491 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1493 reference_type = Column(Enum(ReferenceType), nullable=False)
1495 host_request_id = Column(ForeignKey("host_requests.id"), nullable=True)
1497 text = Column(String, nullable=False) # plain text
1498 # text that's only visible to mods
1499 private_text = Column(String, nullable=True) # plain text
1501 rating = Column(Float, nullable=False)
1502 was_appropriate = Column(Boolean, nullable=False)
1504 is_deleted = Column(Boolean, nullable=False, default=False, server_default="false")
1506 from_user = relationship("User", backref="references_from", foreign_keys="Reference.from_user_id")
1507 to_user = relationship("User", backref="references_to", foreign_keys="Reference.to_user_id")
1509 host_request = relationship("HostRequest", backref="references")
1511 __table_args__ = (
1512 # Rating must be between 0 and 1, inclusive
1513 CheckConstraint(
1514 "rating BETWEEN 0 AND 1",
1515 name="rating_between_0_and_1",
1516 ),
1517 # Has host_request_id or it's a friend reference
1518 CheckConstraint(
1519 "(host_request_id IS NOT NULL) <> (reference_type = 'friend')",
1520 name="host_request_id_xor_friend_reference",
1521 ),
1522 # Each user can leave at most one friend reference to another user
1523 Index(
1524 "ix_references_unique_friend_reference",
1525 from_user_id,
1526 to_user_id,
1527 reference_type,
1528 unique=True,
1529 postgresql_where=(reference_type == ReferenceType.friend),
1530 ),
1531 # Each user can leave at most one reference to another user for each stay
1532 Index(
1533 "ix_references_unique_per_host_request",
1534 from_user_id,
1535 to_user_id,
1536 host_request_id,
1537 unique=True,
1538 postgresql_where=(host_request_id != None),
1539 ),
1540 )
1542 @property
1543 def should_report(self):
1544 """
1545 If this evaluates to true, we send a report to the moderation team.
1546 """
1547 return self.rating <= 0.4 or not self.was_appropriate or self.private_text
1550class InitiatedUpload(Base):
1551 """
1552 Started downloads, not necessarily complete yet.
1553 """
1555 __tablename__ = "initiated_uploads"
1557 key = Column(String, primary_key=True)
1559 # timezones should always be UTC
1560 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1561 expiry = Column(DateTime(timezone=True), nullable=False)
1563 initiator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1565 initiator_user = relationship("User")
1567 @hybrid_property
1568 def is_valid(self):
1569 return (self.created <= func.now()) & (self.expiry >= func.now())
1572class Upload(Base):
1573 """
1574 Completed uploads.
1575 """
1577 __tablename__ = "uploads"
1578 key = Column(String, primary_key=True)
1580 filename = Column(String, nullable=False)
1581 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1582 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1584 # photo credit, etc
1585 credit = Column(String, nullable=True)
1587 creator_user = relationship("User", backref="uploads", foreign_keys="Upload.creator_user_id")
1589 def _url(self, size):
1590 return urls.media_url(filename=self.filename, size=size)
1592 @property
1593 def thumbnail_url(self):
1594 return self._url("thumbnail")
1596 @property
1597 def full_url(self):
1598 return self._url("full")
1601communities_seq = Sequence("communities_seq")
1604class Node(Base):
1605 """
1606 Node, i.e. geographical subdivision of the world
1608 Administered by the official cluster
1609 """
1611 __tablename__ = "nodes"
1613 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1615 # name and description come from official cluster
1616 parent_node_id = Column(ForeignKey("nodes.id"), nullable=True, index=True)
1617 geom = deferred(Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False))
1618 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1620 parent_node = relationship("Node", backref="child_nodes", remote_side="Node.id")
1622 contained_users = relationship(
1623 "User",
1624 primaryjoin="func.ST_Contains(foreign(Node.geom), User.geom).as_comparison(1, 2)",
1625 viewonly=True,
1626 uselist=True,
1627 )
1629 contained_user_ids = association_proxy("contained_users", "id")
1632class Cluster(Base):
1633 """
1634 Cluster, administered grouping of content
1635 """
1637 __tablename__ = "clusters"
1639 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1640 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1641 name = Column(String, nullable=False)
1642 # short description
1643 description = Column(String, nullable=False)
1644 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1646 is_official_cluster = Column(Boolean, nullable=False, default=False)
1648 slug = column_property(func.slugify(name))
1650 official_cluster_for_node = relationship(
1651 "Node",
1652 primaryjoin="and_(Cluster.parent_node_id == Node.id, Cluster.is_official_cluster)",
1653 backref=backref("official_cluster", uselist=False),
1654 uselist=False,
1655 viewonly=True,
1656 )
1658 parent_node = relationship(
1659 "Node", backref="child_clusters", remote_side="Node.id", foreign_keys="Cluster.parent_node_id"
1660 )
1662 nodes = relationship("Cluster", backref="clusters", secondary="node_cluster_associations", viewonly=True)
1663 # all pages
1664 pages = relationship(
1665 "Page", backref="clusters", secondary="cluster_page_associations", lazy="dynamic", viewonly=True
1666 )
1667 events = relationship("Event", backref="clusters", secondary="cluster_event_associations", viewonly=True)
1668 discussions = relationship(
1669 "Discussion", backref="clusters", secondary="cluster_discussion_associations", viewonly=True
1670 )
1672 # includes also admins
1673 members = relationship(
1674 "User",
1675 lazy="dynamic",
1676 backref="cluster_memberships",
1677 secondary="cluster_subscriptions",
1678 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1679 secondaryjoin="User.id == ClusterSubscription.user_id",
1680 viewonly=True,
1681 )
1683 admins = relationship(
1684 "User",
1685 lazy="dynamic",
1686 backref="cluster_adminships",
1687 secondary="cluster_subscriptions",
1688 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1689 secondaryjoin="and_(User.id == ClusterSubscription.user_id, ClusterSubscription.role == 'admin')",
1690 viewonly=True,
1691 )
1693 main_page = relationship(
1694 "Page",
1695 primaryjoin="and_(Cluster.id == Page.owner_cluster_id, Page.type == 'main_page')",
1696 viewonly=True,
1697 uselist=False,
1698 )
1700 @property
1701 def is_leaf(self) -> bool:
1702 """Whether the cluster is a leaf node in the cluster hierarchy."""
1703 return len(self.parent_node.child_nodes) == 0
1705 __table_args__ = (
1706 # Each node can have at most one official cluster
1707 Index(
1708 "ix_clusters_owner_parent_node_id_is_official_cluster",
1709 parent_node_id,
1710 is_official_cluster,
1711 unique=True,
1712 postgresql_where=is_official_cluster,
1713 ),
1714 )
1717class NodeClusterAssociation(Base):
1718 """
1719 NodeClusterAssociation, grouping of nodes
1720 """
1722 __tablename__ = "node_cluster_associations"
1723 __table_args__ = (UniqueConstraint("node_id", "cluster_id"),)
1725 id = Column(BigInteger, primary_key=True)
1727 node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1728 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1730 node = relationship("Node", backref="node_cluster_associations")
1731 cluster = relationship("Cluster", backref="node_cluster_associations")
1734class ClusterRole(enum.Enum):
1735 member = enum.auto()
1736 admin = enum.auto()
1739class ClusterSubscription(Base):
1740 """
1741 ClusterSubscription of a user
1742 """
1744 __tablename__ = "cluster_subscriptions"
1745 __table_args__ = (UniqueConstraint("user_id", "cluster_id"),)
1747 id = Column(BigInteger, primary_key=True)
1749 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1750 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1751 role = Column(Enum(ClusterRole), nullable=False)
1753 user = relationship("User", backref="cluster_subscriptions")
1754 cluster = relationship("Cluster", backref="cluster_subscriptions")
1757class ClusterPageAssociation(Base):
1758 """
1759 pages related to clusters
1760 """
1762 __tablename__ = "cluster_page_associations"
1763 __table_args__ = (UniqueConstraint("page_id", "cluster_id"),)
1765 id = Column(BigInteger, primary_key=True)
1767 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1768 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1770 page = relationship("Page", backref="cluster_page_associations")
1771 cluster = relationship("Cluster", backref="cluster_page_associations")
1774class PageType(enum.Enum):
1775 main_page = enum.auto()
1776 place = enum.auto()
1777 guide = enum.auto()
1780class Page(Base):
1781 """
1782 similar to a wiki page about a community, POI or guide
1783 """
1785 __tablename__ = "pages"
1787 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1789 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1790 type = Column(Enum(PageType), nullable=False)
1791 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1792 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1793 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1795 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
1797 parent_node = relationship("Node", backref="child_pages", remote_side="Node.id", foreign_keys="Page.parent_node_id")
1799 thread = relationship("Thread", backref="page", uselist=False)
1800 creator_user = relationship("User", backref="created_pages", foreign_keys="Page.creator_user_id")
1801 owner_user = relationship("User", backref="owned_pages", foreign_keys="Page.owner_user_id")
1802 owner_cluster = relationship(
1803 "Cluster", backref=backref("owned_pages", lazy="dynamic"), uselist=False, foreign_keys="Page.owner_cluster_id"
1804 )
1806 editors = relationship("User", secondary="page_versions", viewonly=True)
1808 __table_args__ = (
1809 # Only one of owner_user and owner_cluster should be set
1810 CheckConstraint(
1811 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
1812 name="one_owner",
1813 ),
1814 # Only clusters can own main pages
1815 CheckConstraint(
1816 "NOT (owner_cluster_id IS NULL AND type = 'main_page')",
1817 name="main_page_owned_by_cluster",
1818 ),
1819 # Each cluster can have at most one main page
1820 Index(
1821 "ix_pages_owner_cluster_id_type",
1822 owner_cluster_id,
1823 type,
1824 unique=True,
1825 postgresql_where=(type == PageType.main_page),
1826 ),
1827 )
1829 def __repr__(self):
1830 return f"Page({self.id=})"
1833class PageVersion(Base):
1834 """
1835 version of page content
1836 """
1838 __tablename__ = "page_versions"
1840 id = Column(BigInteger, primary_key=True)
1842 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1843 editor_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1844 title = Column(String, nullable=False)
1845 content = Column(String, nullable=False) # CommonMark without images
1846 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
1847 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
1848 # the human-readable address
1849 address = Column(String, nullable=True)
1850 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1852 slug = column_property(func.slugify(title))
1854 page = relationship("Page", backref="versions", order_by="PageVersion.id")
1855 editor_user = relationship("User", backref="edited_pages")
1856 photo = relationship("Upload")
1858 __table_args__ = (
1859 # Geom and address must either both be null or both be set
1860 CheckConstraint(
1861 "(geom IS NULL) = (address IS NULL)",
1862 name="geom_iff_address",
1863 ),
1864 )
1866 @property
1867 def coordinates(self):
1868 # returns (lat, lng) or None
1869 return get_coordinates(self.geom)
1871 def __repr__(self):
1872 return f"PageVersion({self.id=}, {self.page_id=})"
1875class ClusterEventAssociation(Base):
1876 """
1877 events related to clusters
1878 """
1880 __tablename__ = "cluster_event_associations"
1881 __table_args__ = (UniqueConstraint("event_id", "cluster_id"),)
1883 id = Column(BigInteger, primary_key=True)
1885 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1886 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1888 event = relationship("Event", backref="cluster_event_associations")
1889 cluster = relationship("Cluster", backref="cluster_event_associations")
1892class Event(Base):
1893 """
1894 An event is compose of two parts:
1896 * An event template (Event)
1897 * An occurrence (EventOccurrence)
1899 One-off events will have one of each; repeating events will have one Event,
1900 multiple EventOccurrences, one for each time the event happens.
1901 """
1903 __tablename__ = "events"
1905 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1906 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1908 title = Column(String, nullable=False)
1910 slug = column_property(func.slugify(title))
1912 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1913 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1914 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1915 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1916 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
1918 parent_node = relationship(
1919 "Node", backref="child_events", remote_side="Node.id", foreign_keys="Event.parent_node_id"
1920 )
1921 thread = relationship("Thread", backref="event", uselist=False)
1922 subscribers = relationship(
1923 "User", backref="subscribed_events", secondary="event_subscriptions", lazy="dynamic", viewonly=True
1924 )
1925 organizers = relationship(
1926 "User", backref="organized_events", secondary="event_organizers", lazy="dynamic", viewonly=True
1927 )
1928 creator_user = relationship("User", backref="created_events", foreign_keys="Event.creator_user_id")
1929 owner_user = relationship("User", backref="owned_events", foreign_keys="Event.owner_user_id")
1930 owner_cluster = relationship(
1931 "Cluster",
1932 backref=backref("owned_events", lazy="dynamic"),
1933 uselist=False,
1934 foreign_keys="Event.owner_cluster_id",
1935 )
1937 __table_args__ = (
1938 # Only one of owner_user and owner_cluster should be set
1939 CheckConstraint(
1940 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
1941 name="one_owner",
1942 ),
1943 )
1946class EventOccurrence(Base):
1947 __tablename__ = "event_occurrences"
1949 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1950 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1952 # the user that created this particular occurrence of a repeating event (same as event.creator_user_id if single event)
1953 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1954 content = Column(String, nullable=False) # CommonMark without images
1955 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
1957 is_cancelled = Column(Boolean, nullable=False, default=False, server_default=text("false"))
1958 is_deleted = Column(Boolean, nullable=False, default=False, server_default=text("false"))
1960 # a null geom is an online-only event
1961 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
1962 # physical address, iff geom is not null
1963 address = Column(String, nullable=True)
1964 # videoconferencing link, etc, must be specified if no geom, otherwise optional
1965 link = Column(String, nullable=True)
1967 timezone = "Etc/UTC"
1969 # time during which the event takes place; this is a range type (instead of separate start+end times) which
1970 # simplifies database constraints, etc
1971 during = Column(TSTZRANGE, nullable=False)
1973 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1974 last_edited = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1976 creator_user = relationship(
1977 "User", backref="created_event_occurrences", foreign_keys="EventOccurrence.creator_user_id"
1978 )
1979 event = relationship(
1980 "Event",
1981 backref=backref("occurrences", lazy="dynamic"),
1982 remote_side="Event.id",
1983 foreign_keys="EventOccurrence.event_id",
1984 )
1986 photo = relationship("Upload")
1988 __table_args__ = (
1989 # Geom and address go together
1990 CheckConstraint(
1991 # geom and address are either both null or neither of them are null
1992 "(geom IS NULL) = (address IS NULL)",
1993 name="geom_iff_address",
1994 ),
1995 # Online-only events need a link, note that online events may also have a link
1996 CheckConstraint(
1997 # exactly oen of geom or link is non-null
1998 "(geom IS NULL) <> (link IS NULL)",
1999 name="link_or_geom",
2000 ),
2001 # Can't have overlapping occurrences in the same Event
2002 ExcludeConstraint(("event_id", "="), ("during", "&&"), name="event_occurrences_event_id_during_excl"),
2003 )
2005 @property
2006 def coordinates(self):
2007 # returns (lat, lng) or None
2008 return get_coordinates(self.geom)
2010 @hybrid_property
2011 def start_time(self):
2012 return self.during.lower
2014 @start_time.expression
2015 def start_time(cls):
2016 return func.lower(cls.during)
2018 @hybrid_property
2019 def end_time(self):
2020 return self.during.upper
2022 @end_time.expression
2023 def end_time(cls):
2024 return func.upper(cls.during)
2027class EventSubscription(Base):
2028 """
2029 Users' subscriptions to events
2030 """
2032 __tablename__ = "event_subscriptions"
2033 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
2035 id = Column(BigInteger, primary_key=True)
2037 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2038 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
2039 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2041 user = relationship("User")
2042 event = relationship("Event")
2045class EventOrganizer(Base):
2046 """
2047 Organizers for events
2048 """
2050 __tablename__ = "event_organizers"
2051 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
2053 id = Column(BigInteger, primary_key=True)
2055 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2056 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
2057 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2059 user = relationship("User")
2060 event = relationship("Event")
2063class AttendeeStatus(enum.Enum):
2064 going = enum.auto()
2065 maybe = enum.auto()
2068class EventOccurrenceAttendee(Base):
2069 """
2070 Attendees for events
2071 """
2073 __tablename__ = "event_occurrence_attendees"
2074 __table_args__ = (UniqueConstraint("occurrence_id", "user_id"),)
2076 id = Column(BigInteger, primary_key=True)
2078 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2079 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
2080 responded = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2081 attendee_status = Column(Enum(AttendeeStatus), nullable=False)
2083 user = relationship("User")
2084 occurrence = relationship("EventOccurrence", backref=backref("attendances", lazy="dynamic"))
2087class EventCommunityInviteRequest(Base):
2088 """
2089 Requests to send out invitation notifications/emails to the community for a given event occurrence
2090 """
2092 __tablename__ = "event_community_invite_requests"
2094 id = Column(BigInteger, primary_key=True)
2096 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
2097 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2099 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2101 decided = Column(DateTime(timezone=True), nullable=True)
2102 decided_by_user_id = Column(ForeignKey("users.id"), nullable=True)
2103 approved = Column(Boolean, nullable=True)
2105 occurrence = relationship("EventOccurrence", backref=backref("community_invite_requests", lazy="dynamic"))
2106 user = relationship("User", foreign_keys="EventCommunityInviteRequest.user_id")
2108 __table_args__ = (
2109 # each user can only request once
2110 UniqueConstraint("occurrence_id", "user_id"),
2111 # each event can only have one notification sent out
2112 Index(
2113 "ix_event_community_invite_requests_unique",
2114 occurrence_id,
2115 unique=True,
2116 postgresql_where=and_(approved.is_not(None), approved == True),
2117 ),
2118 # decided and approved ought to be null simultaneously
2119 CheckConstraint(
2120 "((decided IS NULL) AND (decided_by_user_id IS NULL) AND (approved IS NULL)) OR \
2121 ((decided IS NOT NULL) AND (decided_by_user_id IS NOT NULL) AND (approved IS NOT NULL))",
2122 name="decided_approved",
2123 ),
2124 )
2127class ClusterDiscussionAssociation(Base):
2128 """
2129 discussions related to clusters
2130 """
2132 __tablename__ = "cluster_discussion_associations"
2133 __table_args__ = (UniqueConstraint("discussion_id", "cluster_id"),)
2135 id = Column(BigInteger, primary_key=True)
2137 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2138 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2140 discussion = relationship("Discussion", backref="cluster_discussion_associations")
2141 cluster = relationship("Cluster", backref="cluster_discussion_associations")
2144class Discussion(Base):
2145 """
2146 forum board
2147 """
2149 __tablename__ = "discussions"
2151 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
2153 title = Column(String, nullable=False)
2154 content = Column(String, nullable=False)
2155 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
2156 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2158 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2159 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2161 slug = column_property(func.slugify(title))
2163 thread = relationship("Thread", backref="discussion", uselist=False)
2165 subscribers = relationship("User", backref="discussions", secondary="discussion_subscriptions", viewonly=True)
2167 creator_user = relationship("User", backref="created_discussions", foreign_keys="Discussion.creator_user_id")
2168 owner_cluster = relationship("Cluster", backref=backref("owned_discussions", lazy="dynamic"), uselist=False)
2171class DiscussionSubscription(Base):
2172 """
2173 users subscriptions to discussions
2174 """
2176 __tablename__ = "discussion_subscriptions"
2177 __table_args__ = (UniqueConstraint("discussion_id", "user_id"),)
2179 id = Column(BigInteger, primary_key=True)
2181 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2182 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2183 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2184 left = Column(DateTime(timezone=True), nullable=True)
2186 user = relationship("User", backref="discussion_subscriptions")
2187 discussion = relationship("Discussion", backref="discussion_subscriptions")
2190class Thread(Base):
2191 """
2192 Thread
2193 """
2195 __tablename__ = "threads"
2197 id = Column(BigInteger, primary_key=True)
2199 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2200 deleted = Column(DateTime(timezone=True), nullable=True)
2203class Comment(Base):
2204 """
2205 Comment
2206 """
2208 __tablename__ = "comments"
2210 id = Column(BigInteger, primary_key=True)
2212 thread_id = Column(ForeignKey("threads.id"), nullable=False, index=True)
2213 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2214 content = Column(String, nullable=False) # CommonMark without images
2215 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2216 deleted = Column(DateTime(timezone=True), nullable=True)
2218 thread = relationship("Thread", backref="comments")
2221class Reply(Base):
2222 """
2223 Reply
2224 """
2226 __tablename__ = "replies"
2228 id = Column(BigInteger, primary_key=True)
2230 comment_id = Column(ForeignKey("comments.id"), nullable=False, index=True)
2231 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2232 content = Column(String, nullable=False) # CommonMark without images
2233 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2234 deleted = Column(DateTime(timezone=True), nullable=True)
2236 comment = relationship("Comment", backref="replies")
2239class BackgroundJobState(enum.Enum):
2240 # job is fresh, waiting to be picked off the queue
2241 pending = enum.auto()
2242 # job complete
2243 completed = enum.auto()
2244 # error occured, will be retried
2245 error = enum.auto()
2246 # failed too many times, not retrying anymore
2247 failed = enum.auto()
2250class BackgroundJob(Base):
2251 """
2252 This table implements a queue of background jobs.
2253 """
2255 __tablename__ = "background_jobs"
2257 id = Column(BigInteger, primary_key=True)
2259 # used to discern which function should be triggered to service it
2260 job_type = Column(String, nullable=False)
2261 state = Column(Enum(BackgroundJobState), nullable=False, default=BackgroundJobState.pending)
2263 # time queued
2264 queued = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2266 # time at which we may next attempt it, for implementing exponential backoff
2267 next_attempt_after = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2269 # used to count number of retries for failed jobs
2270 try_count = Column(Integer, nullable=False, default=0)
2272 max_tries = Column(Integer, nullable=False, default=5)
2274 # higher is more important
2275 priority = Column(Integer, nullable=False, server_default=text("10"))
2277 # protobuf encoded job payload
2278 payload = Column(Binary, nullable=False)
2280 # if the job failed, we write that info here
2281 failure_info = Column(String, nullable=True)
2283 __table_args__ = (
2284 # used in looking up background jobs to attempt
2285 # create index on background_jobs(priority desc, next_attempt_after, (max_tries - try_count)) where state = 'pending' OR state = 'error';
2286 Index(
2287 "ix_background_jobs_lookup",
2288 priority.desc(),
2289 next_attempt_after,
2290 (max_tries - try_count),
2291 postgresql_where=((state == BackgroundJobState.pending) | (state == BackgroundJobState.error)),
2292 ),
2293 )
2295 @hybrid_property
2296 def ready_for_retry(self):
2297 return (
2298 (self.next_attempt_after <= func.now())
2299 & (self.try_count < self.max_tries)
2300 & ((self.state == BackgroundJobState.pending) | (self.state == BackgroundJobState.error))
2301 )
2303 def __repr__(self):
2304 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})"
2307class NotificationDeliveryType(enum.Enum):
2308 # send push notification to mobile/web
2309 push = enum.auto()
2310 # send individual email immediately
2311 email = enum.auto()
2312 # send in digest
2313 digest = enum.auto()
2316dt = NotificationDeliveryType
2317nd = notification_data_pb2
2319dt_sec = [dt.email, dt.push]
2320dt_all = [dt.email, dt.push, dt.digest]
2323class NotificationTopicAction(enum.Enum):
2324 def __init__(self, topic_action, defaults, user_editable, data_type):
2325 self.topic, self.action = topic_action.split(":")
2326 self.defaults = defaults
2327 # for now user editable == not a security notification
2328 self.user_editable = user_editable
2330 self.data_type = data_type
2332 def unpack(self):
2333 return self.topic, self.action
2335 @property
2336 def display(self):
2337 return f"{self.topic}:{self.action}"
2339 def __str__(self):
2340 return self.display
2342 # topic, action, default delivery types
2343 friend_request__create = ("friend_request:create", dt_all, True, nd.FriendRequestCreate)
2344 friend_request__accept = ("friend_request:accept", dt_all, True, nd.FriendRequestAccept)
2346 # host requests
2347 host_request__create = ("host_request:create", dt_all, True, nd.HostRequestCreate)
2348 host_request__accept = ("host_request:accept", dt_all, True, nd.HostRequestAccept)
2349 host_request__reject = ("host_request:reject", dt_all, True, nd.HostRequestReject)
2350 host_request__confirm = ("host_request:confirm", dt_all, True, nd.HostRequestConfirm)
2351 host_request__cancel = ("host_request:cancel", dt_all, True, nd.HostRequestCancel)
2352 host_request__message = ("host_request:message", [dt.push, dt.digest], True, nd.HostRequestMessage)
2353 host_request__missed_messages = ("host_request:missed_messages", [dt.email], True, nd.HostRequestMissedMessages)
2355 activeness__probe = ("activeness:probe", dt_sec, False, nd.ActivenessProbe)
2357 # you receive a friend ref
2358 reference__receive_friend = ("reference:receive_friend", dt_all, True, nd.ReferenceReceiveFriend)
2359 # you receive a reference from ... the host
2360 reference__receive_hosted = ("reference:receive_hosted", dt_all, True, nd.ReferenceReceiveHostRequest)
2361 # ... the surfer
2362 reference__receive_surfed = ("reference:receive_surfed", dt_all, True, nd.ReferenceReceiveHostRequest)
2364 # you hosted
2365 reference__reminder_hosted = ("reference:reminder_hosted", dt_all, True, nd.ReferenceReminder)
2366 # you surfed
2367 reference__reminder_surfed = ("reference:reminder_surfed", dt_all, True, nd.ReferenceReminder)
2369 badge__add = ("badge:add", [dt.push, dt.digest], True, nd.BadgeAdd)
2370 badge__remove = ("badge:remove", [dt.push, dt.digest], True, nd.BadgeRemove)
2372 # group chats
2373 chat__message = ("chat:message", [dt.push, dt.digest], True, nd.ChatMessage)
2374 chat__missed_messages = ("chat:missed_messages", [dt.email], True, nd.ChatMissedMessages)
2376 # events
2377 # approved by mods
2378 event__create_approved = ("event:create_approved", dt_all, True, nd.EventCreate)
2379 # any user creates any event, default to no notifications
2380 event__create_any = ("event:create_any", [], True, nd.EventCreate)
2381 event__update = ("event:update", dt_all, True, nd.EventUpdate)
2382 event__cancel = ("event:cancel", dt_all, True, nd.EventCancel)
2383 event__delete = ("event:delete", dt_all, True, nd.EventDelete)
2384 event__invite_organizer = ("event:invite_organizer", dt_all, True, nd.EventInviteOrganizer)
2385 # toplevel comment on an event
2386 event__comment = ("event:comment", dt_all, True, nd.EventComment)
2388 # discussion created
2389 discussion__create = ("discussion:create", [dt.digest], True, nd.DiscussionCreate)
2390 # someone comments on your discussion
2391 discussion__comment = ("discussion:comment", dt_all, True, nd.DiscussionComment)
2393 # someone responds to any of your top-level comment across the platform
2394 thread__reply = ("thread:reply", dt_all, True, nd.ThreadReply)
2396 # account settings
2397 password__change = ("password:change", dt_sec, False, empty_pb2.Empty)
2398 email_address__change = ("email_address:change", dt_sec, False, nd.EmailAddressChange)
2399 email_address__verify = ("email_address:verify", dt_sec, False, empty_pb2.Empty)
2400 phone_number__change = ("phone_number:change", dt_sec, False, nd.PhoneNumberChange)
2401 phone_number__verify = ("phone_number:verify", dt_sec, False, nd.PhoneNumberVerify)
2402 # reset password
2403 password_reset__start = ("password_reset:start", dt_sec, False, nd.PasswordResetStart)
2404 password_reset__complete = ("password_reset:complete", dt_sec, False, empty_pb2.Empty)
2406 # account deletion
2407 account_deletion__start = ("account_deletion:start", dt_sec, False, nd.AccountDeletionStart)
2408 # no more pushing to do
2409 account_deletion__complete = ("account_deletion:complete", dt_sec, False, nd.AccountDeletionComplete)
2410 # undeleted
2411 account_deletion__recovered = ("account_deletion:recovered", dt_sec, False, empty_pb2.Empty)
2413 # admin actions
2414 gender__change = ("gender:change", dt_sec, False, nd.GenderChange)
2415 birthdate__change = ("birthdate:change", dt_sec, False, nd.BirthdateChange)
2416 api_key__create = ("api_key:create", dt_sec, False, nd.ApiKeyCreate)
2418 donation__received = ("donation:received", dt_sec, True, nd.DonationReceived)
2420 onboarding__reminder = ("onboarding:reminder", dt_sec, True, empty_pb2.Empty)
2422 modnote__create = ("modnote:create", dt_sec, False, empty_pb2.Empty)
2424 verification__sv_fail = ("verification:sv_fail", dt_sec, False, nd.VerificationSVFail)
2425 verification__sv_success = ("verification:sv_success", dt_sec, False, empty_pb2.Empty)
2428class NotificationPreference(Base):
2429 __tablename__ = "notification_preferences"
2431 id = Column(BigInteger, primary_key=True)
2432 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2434 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2435 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2436 deliver = Column(Boolean, nullable=False)
2438 user = relationship("User", foreign_keys="NotificationPreference.user_id")
2440 __table_args__ = (UniqueConstraint("user_id", "topic_action", "delivery_type"),)
2443class Notification(Base):
2444 """
2445 Table for accumulating notifications until it is time to send email digest
2446 """
2448 __tablename__ = "notifications"
2450 id = Column(BigInteger, primary_key=True)
2451 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2453 # recipient user id
2454 user_id = Column(ForeignKey("users.id"), nullable=False)
2456 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2457 key = Column(String, nullable=False)
2459 data = Column(Binary, nullable=False)
2461 user = relationship("User", foreign_keys="Notification.user_id")
2463 __table_args__ = (
2464 # used in looking up which notifications need delivery
2465 Index(
2466 "ix_notifications_created",
2467 created,
2468 ),
2469 )
2471 @property
2472 def topic(self):
2473 return self.topic_action.topic
2475 @property
2476 def action(self):
2477 return self.topic_action.action
2480class NotificationDelivery(Base):
2481 __tablename__ = "notification_deliveries"
2483 id = Column(BigInteger, primary_key=True)
2484 notification_id = Column(ForeignKey("notifications.id"), nullable=False, index=True)
2485 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2486 delivered = Column(DateTime(timezone=True), nullable=True)
2487 read = Column(DateTime(timezone=True), nullable=True)
2488 # todo: enum of "phone, web, digest"
2489 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2490 # todo: device id
2491 # todo: receipt id, etc
2492 notification = relationship("Notification", foreign_keys="NotificationDelivery.notification_id")
2494 __table_args__ = (
2495 UniqueConstraint("notification_id", "delivery_type"),
2496 # used in looking up which notifications need delivery
2497 Index(
2498 "ix_notification_deliveries_delivery_type",
2499 delivery_type,
2500 postgresql_where=(delivered != None),
2501 ),
2502 Index(
2503 "ix_notification_deliveries_dt_ni_dnull",
2504 delivery_type,
2505 notification_id,
2506 delivered == None,
2507 ),
2508 )
2511class PushNotificationSubscription(Base):
2512 __tablename__ = "push_notification_subscriptions"
2514 id = Column(BigInteger, primary_key=True)
2515 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2517 # which user this is connected to
2518 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2520 # these come from https://developer.mozilla.org/en-US/docs/Web/API/PushSubscription
2521 # the endpoint
2522 endpoint = Column(String, nullable=False)
2523 # the "auth" key
2524 auth_key = Column(Binary, nullable=False)
2525 # the "p256dh" key
2526 p256dh_key = Column(Binary, nullable=False)
2528 full_subscription_info = Column(String, nullable=False)
2530 # the browse user-agent, so we can tell the user what browser notifications are going to
2531 user_agent = Column(String, nullable=True)
2533 # when it was disabled
2534 disabled_at = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_INFINITY.isoformat())
2536 user = relationship("User")
2539class PushNotificationDeliveryAttempt(Base):
2540 __tablename__ = "push_notification_delivery_attempt"
2542 id = Column(BigInteger, primary_key=True)
2543 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2545 push_notification_subscription_id = Column(
2546 ForeignKey("push_notification_subscriptions.id"), nullable=False, index=True
2547 )
2549 success = Column(Boolean, nullable=False)
2550 # the HTTP status code, 201 is success
2551 status_code = Column(Integer, nullable=False)
2553 # can be null if it was a success
2554 response = Column(String, nullable=True)
2556 push_notification_subscription = relationship("PushNotificationSubscription")
2559class Language(Base):
2560 """
2561 Table of allowed languages (a subset of ISO639-3)
2562 """
2564 __tablename__ = "languages"
2566 # ISO639-3 language code, in lowercase, e.g. fin, eng
2567 code = Column(String(3), primary_key=True)
2569 # the english name
2570 name = Column(String, nullable=False, unique=True)
2573class Region(Base):
2574 """
2575 Table of regions
2576 """
2578 __tablename__ = "regions"
2580 # iso 3166-1 alpha3 code in uppercase, e.g. FIN, USA
2581 code = Column(String(3), primary_key=True)
2583 # the name, e.g. Finland, United States
2584 # this is the display name in English, should be the "common name", not "Republic of Finland"
2585 name = Column(String, nullable=False, unique=True)
2588class UserBlock(Base):
2589 """
2590 Table of blocked users
2591 """
2593 __tablename__ = "user_blocks"
2594 __table_args__ = (UniqueConstraint("blocking_user_id", "blocked_user_id"),)
2596 id = Column(BigInteger, primary_key=True)
2598 blocking_user_id = Column(ForeignKey("users.id"), nullable=False)
2599 blocked_user_id = Column(ForeignKey("users.id"), nullable=False)
2600 time_blocked = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2602 blocking_user = relationship("User", foreign_keys="UserBlock.blocking_user_id")
2603 blocked_user = relationship("User", foreign_keys="UserBlock.blocked_user_id")
2606class APICall(Base):
2607 """
2608 API call logs
2609 """
2611 __tablename__ = "api_calls"
2612 __table_args__ = {"schema": "logging"}
2614 id = Column(BigInteger, primary_key=True)
2616 # whether the call was made using an api key or session cookies
2617 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))
2619 # backend version (normally e.g. develop-31469e3), allows us to figure out which proto definitions were used
2620 # note that `default` is a python side default, not hardcoded into DB schema
2621 version = Column(String, nullable=False, default=config["VERSION"])
2623 # approximate time of the call
2624 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2626 # the method call name, e.g. "/org.couchers.api.core.API/ListFriends"
2627 method = Column(String, nullable=False)
2629 # gRPC status code name, e.g. FAILED_PRECONDITION, None if success
2630 status_code = Column(String, nullable=True)
2632 # handler duration (excluding serialization, etc)
2633 duration = Column(Float, nullable=False)
2635 # user_id of caller, None means not logged in
2636 user_id = Column(BigInteger, nullable=True)
2638 # sanitized request bytes
2639 request = Column(Binary, nullable=True)
2641 # sanitized response bytes
2642 response = Column(Binary, nullable=True)
2644 # whether response bytes have been truncated
2645 response_truncated = Column(Boolean, nullable=False, server_default=text("false"))
2647 # the exception traceback, if any
2648 traceback = Column(String, nullable=True)
2650 # human readable perf report
2651 perf_report = Column(String, nullable=True)
2653 # details of the browser, if available
2654 ip_address = Column(String, nullable=True)
2655 user_agent = Column(String, nullable=True)
2658class AccountDeletionReason(Base):
2659 __tablename__ = "account_deletion_reason"
2661 id = Column(BigInteger, primary_key=True)
2662 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2663 user_id = Column(ForeignKey("users.id"), nullable=False)
2664 reason = Column(String, nullable=True)
2666 user = relationship("User")