Coverage for src/couchers/models.py: 99%
1070 statements
« prev ^ index » next coverage.py v7.5.0, created at 2024-10-21 08:09 +0000
1import enum
3from geoalchemy2.types import Geometry
4from google.protobuf import empty_pb2
5from sqlalchemy import (
6 ARRAY,
7 BigInteger,
8 Boolean,
9 CheckConstraint,
10 Column,
11 Date,
12 DateTime,
13 Enum,
14 Float,
15 ForeignKey,
16 Index,
17 Integer,
18 Interval,
19 MetaData,
20 Sequence,
21 String,
22 UniqueConstraint,
23)
24from sqlalchemy import LargeBinary as Binary
25from sqlalchemy.dialects.postgresql import INET, TSTZRANGE, ExcludeConstraint
26from sqlalchemy.ext.associationproxy import association_proxy
27from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property
28from sqlalchemy.orm import backref, column_property, declarative_base, deferred, relationship
29from sqlalchemy.sql import and_, func, text
30from sqlalchemy.sql import select as sa_select
32from couchers import urls
33from couchers.config import config
34from couchers.constants import (
35 DATETIME_INFINITY,
36 DATETIME_MINUS_INFINITY,
37 EMAIL_REGEX,
38 GUIDELINES_VERSION,
39 PHONE_VERIFICATION_LIFETIME,
40 SMS_CODE_LIFETIME,
41 TOS_VERSION,
42)
43from couchers.utils import date_in_timezone, get_coordinates, last_active_coarsen, now
44from proto import notification_data_pb2
46meta = MetaData(
47 naming_convention={
48 "ix": "ix_%(column_0_label)s",
49 "uq": "uq_%(table_name)s_%(column_0_name)s",
50 "ck": "ck_%(table_name)s_%(constraint_name)s",
51 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
52 "pk": "pk_%(table_name)s",
53 }
54)
56Base = declarative_base(metadata=meta)
59class HostingStatus(enum.Enum):
60 can_host = enum.auto()
61 maybe = enum.auto()
62 cant_host = enum.auto()
65class MeetupStatus(enum.Enum):
66 wants_to_meetup = enum.auto()
67 open_to_meetup = enum.auto()
68 does_not_want_to_meetup = enum.auto()
71class SmokingLocation(enum.Enum):
72 yes = enum.auto()
73 window = enum.auto()
74 outside = enum.auto()
75 no = enum.auto()
78class SleepingArrangement(enum.Enum):
79 private = enum.auto()
80 common = enum.auto()
81 shared_room = enum.auto()
82 shared_space = enum.auto()
85class ParkingDetails(enum.Enum):
86 free_onsite = enum.auto()
87 free_offsite = enum.auto()
88 paid_onsite = enum.auto()
89 paid_offsite = enum.auto()
92class TimezoneArea(Base):
93 __tablename__ = "timezone_areas"
94 id = Column(BigInteger, primary_key=True)
96 tzid = Column(String)
97 geom = Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False)
99 __table_args__ = (
100 Index(
101 "ix_timezone_areas_geom_tzid",
102 geom,
103 tzid,
104 postgresql_using="gist",
105 ),
106 )
109class User(Base):
110 """
111 Basic user and profile details
112 """
114 __tablename__ = "users"
116 id = Column(BigInteger, primary_key=True)
118 username = Column(String, nullable=False, unique=True)
119 email = Column(String, nullable=False, unique=True)
120 # stored in libsodium hash format, can be null for email login
121 hashed_password = Column(Binary, nullable=False)
122 # phone number in E.164 format with leading +, for example "+46701740605"
123 phone = Column(String, nullable=True, server_default=text("NULL"))
125 # timezones should always be UTC
126 ## location
127 # point describing their location. EPSG4326 is the SRS (spatial ref system, = way to describe a point on earth) used
128 # by GPS, it has the WGS84 geoid with lat/lon
129 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
130 # their display location (displayed to other users), in meters
131 geom_radius = Column(Float, nullable=True)
132 # the display address (text) shown on their profile
133 city = Column(String, nullable=False)
134 hometown = Column(String, nullable=True)
136 regions_visited = relationship("Region", secondary="regions_visited", order_by="Region.name")
137 regions_lived = relationship("Region", secondary="regions_lived", order_by="Region.name")
139 timezone = column_property(
140 sa_select(TimezoneArea.tzid).where(func.ST_Contains(TimezoneArea.geom, geom)).limit(1).scalar_subquery(),
141 deferred=True,
142 )
144 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
145 last_active = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
147 # id of the last message that they received a notification about
148 last_notified_message_id = Column(BigInteger, nullable=False, default=0)
149 # same as above for host requests
150 last_notified_request_message_id = Column(BigInteger, nullable=False, server_default=text("0"))
152 # display name
153 name = Column(String, nullable=False)
154 gender = Column(String, nullable=False)
155 pronouns = Column(String, nullable=True)
156 birthdate = Column(Date, nullable=False) # in the timezone of birthplace
158 # name as on official docs for verification, etc. not needed until verification
159 full_name = Column(String, nullable=True)
161 avatar_key = Column(ForeignKey("uploads.key"), nullable=True)
163 hosting_status = Column(Enum(HostingStatus), nullable=False)
164 meetup_status = Column(Enum(MeetupStatus), nullable=False, server_default="open_to_meetup")
166 # community standing score
167 community_standing = Column(Float, nullable=True)
169 occupation = Column(String, nullable=True) # CommonMark without images
170 education = Column(String, nullable=True) # CommonMark without images
171 about_me = Column(String, nullable=True) # CommonMark without images
172 my_travels = Column(String, nullable=True) # CommonMark without images
173 things_i_like = Column(String, nullable=True) # CommonMark without images
174 about_place = Column(String, nullable=True) # CommonMark without images
175 additional_information = Column(String, nullable=True) # CommonMark without images
177 is_banned = Column(Boolean, nullable=False, server_default=text("false"))
178 is_deleted = Column(Boolean, nullable=False, server_default=text("false"))
179 is_superuser = Column(Boolean, nullable=False, server_default=text("false"))
181 # the undelete token allows a user to recover their account for a couple of days after deletion in case it was
182 # accidental or they changed their mind
183 # constraints make sure these are non-null only if is_deleted and that these are null in unison
184 undelete_token = Column(String, nullable=True)
185 # validity of the undelete token
186 undelete_until = Column(DateTime(timezone=True), nullable=True)
188 # hosting preferences
189 max_guests = Column(Integer, nullable=True)
190 last_minute = Column(Boolean, nullable=True)
191 has_pets = Column(Boolean, nullable=True)
192 accepts_pets = Column(Boolean, nullable=True)
193 pet_details = Column(String, nullable=True) # CommonMark without images
194 has_kids = Column(Boolean, nullable=True)
195 accepts_kids = Column(Boolean, nullable=True)
196 kid_details = Column(String, nullable=True) # CommonMark without images
197 has_housemates = Column(Boolean, nullable=True)
198 housemate_details = Column(String, nullable=True) # CommonMark without images
199 wheelchair_accessible = Column(Boolean, nullable=True)
200 smoking_allowed = Column(Enum(SmokingLocation), nullable=True)
201 smokes_at_home = Column(Boolean, nullable=True)
202 drinking_allowed = Column(Boolean, nullable=True)
203 drinks_at_home = Column(Boolean, nullable=True)
204 other_host_info = Column(String, nullable=True) # CommonMark without images
206 sleeping_arrangement = Column(Enum(SleepingArrangement), nullable=True)
207 sleeping_details = Column(String, nullable=True) # CommonMark without images
208 area = Column(String, nullable=True) # CommonMark without images
209 house_rules = Column(String, nullable=True) # CommonMark without images
210 parking = Column(Boolean, nullable=True)
211 parking_details = Column(Enum(ParkingDetails), nullable=True) # CommonMark without images
212 camping_ok = Column(Boolean, nullable=True)
214 accepted_tos = Column(Integer, nullable=False, default=0)
215 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")
216 # whether the user has yet filled in the contributor form
217 filled_contributor_form = Column(Boolean, nullable=False, server_default="false")
219 # number of onboarding emails sent
220 onboarding_emails_sent = Column(Integer, nullable=False, server_default="0")
221 last_onboarding_email_sent = Column(DateTime(timezone=True), nullable=True)
223 # whether we need to sync the user's newsletter preferences with the newsletter server
224 in_sync_with_newsletter = Column(Boolean, nullable=False, server_default="false")
225 # opted out of the newsletter
226 opt_out_of_newsletter = Column(Boolean, nullable=False, server_default="false")
228 # set to null to receive no digests
229 digest_frequency = Column(Interval, nullable=True)
230 last_digest_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)"))
232 # for changing their email
233 new_email = Column(String, nullable=True)
235 new_email_token = Column(String, nullable=True)
236 new_email_token_created = Column(DateTime(timezone=True), nullable=True)
237 new_email_token_expiry = Column(DateTime(timezone=True), nullable=True)
239 recommendation_score = Column(Float, nullable=False, server_default="0")
241 # Columns for verifying their phone number. State chart:
242 # ,-------------------,
243 # | Start |
244 # | phone = None | someone else
245 # ,-----------------, | token = None | verifies ,-----------------------,
246 # | Code Expired | | sent = 1970 or zz | phone xx | Verification Expired |
247 # | phone = xx | time passes | verified = None | <------, | phone = xx |
248 # | token = yy | <------------, | attempts = 0 | | | token = None |
249 # | sent = zz (exp.)| | '-------------------' | | sent = zz |
250 # | verified = None | | V ^ +-----------< | verified = ww (exp.) |
251 # | attempts = 0..2 | >--, | | | ChangePhone("") | | attempts = 0 |
252 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------'
253 # | | | | ChangePhone(xx) | ^ time passes
254 # | | ^ V | |
255 # ,-----------------, | | ,-------------------, | ,-----------------------,
256 # | Too Many | >--' '--< | Code sent | >------+ | Verified |
257 # | phone = xx | | phone = xx | | | phone = xx |
258 # | token = yy | VerifyPhone(wrong)| token = yy | '-----------< | token = None |
259 # | sent = zz | <------+--------< | sent = zz | | sent = zz |
260 # | verified = None | | | verified = None | VerifyPhone(correct) | verified = ww |
261 # | attempts = 3 | '--------> | attempts = 0..2 | >------------------> | attempts = 0 |
262 # '-----------------' '-------------------' '-----------------------'
264 # randomly generated Luhn 6-digit string
265 phone_verification_token = Column(String(6), nullable=True, server_default=text("NULL"))
267 phone_verification_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)"))
268 phone_verification_verified = Column(DateTime(timezone=True), nullable=True, server_default=text("NULL"))
269 phone_verification_attempts = Column(Integer, nullable=False, server_default=text("0"))
271 # the stripe customer identifier if the user has donated to Couchers
272 # e.g. cus_JjoXHttuZopv0t
273 # for new US entity
274 stripe_customer_id = Column(String, nullable=True)
275 # for old AU entity
276 stripe_customer_id_old = Column(String, nullable=True)
278 has_passport_sex_gender_exception = Column(Boolean, nullable=False, server_default=text("false"))
280 # checking for phone verification
281 has_donated = Column(Boolean, nullable=False, server_default=text("false"))
283 # whether this user has all emails turned off
284 do_not_email = Column(Boolean, nullable=False, server_default=text("false"))
286 avatar = relationship("Upload", foreign_keys="User.avatar_key")
288 admin_note = Column(String, nullable=False, server_default=text("''"))
290 age = column_property(func.date_part("year", func.age(birthdate)))
292 __table_args__ = (
293 # Verified phone numbers should be unique
294 Index(
295 "ix_users_unique_phone",
296 phone,
297 unique=True,
298 postgresql_where=phone_verification_verified != None,
299 ),
300 Index(
301 "ix_users_active",
302 id,
303 postgresql_where=~is_banned & ~is_deleted,
304 ),
305 # create index on users(geom, id, username) where not is_banned and not is_deleted and geom is not null;
306 Index(
307 "ix_users_geom_active",
308 geom,
309 id,
310 username,
311 postgresql_where=~is_banned & ~is_deleted & (geom != None),
312 ),
313 # There are two possible states for new_email_token, new_email_token_created, and new_email_token_expiry
314 CheckConstraint(
315 "(new_email_token IS NOT NULL AND new_email_token_created IS NOT NULL AND new_email_token_expiry IS NOT NULL) OR \
316 (new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL)",
317 name="check_new_email_token_state",
318 ),
319 # Whenever a phone number is set, it must either be pending verification or already verified.
320 # Exactly one of the following must always be true: not phone, token, verified.
321 CheckConstraint(
322 "(phone IS NULL)::int + (phone_verification_verified IS NOT NULL)::int + (phone_verification_token IS NOT NULL)::int = 1",
323 name="phone_verified_conditions",
324 ),
325 # Email must match our regex
326 CheckConstraint(
327 f"email ~ '{EMAIL_REGEX}'",
328 name="valid_email",
329 ),
330 # Undelete token + time are coupled: either both null or neither; and if they're not null then the account is deleted
331 CheckConstraint(
332 "((undelete_token IS NULL) = (undelete_until IS NULL)) AND ((undelete_token IS NULL) OR is_deleted)",
333 name="undelete_nullity",
334 ),
335 # If the user disabled all emails, then they can't host or meet up
336 CheckConstraint(
337 "(do_not_email IS FALSE) OR ((hosting_status = 'cant_host') AND (meetup_status = 'does_not_want_to_meetup'))",
338 name="do_not_email_inactive",
339 ),
340 )
342 @hybrid_property
343 def has_completed_profile(self):
344 return self.avatar_key is not None and self.about_me is not None and len(self.about_me) >= 150
346 @has_completed_profile.expression
347 def has_completed_profile(cls):
348 return (cls.avatar_key != None) & (func.character_length(cls.about_me) >= 150)
350 @hybrid_property
351 def is_jailed(self):
352 return (
353 (self.accepted_tos < TOS_VERSION)
354 | (self.accepted_community_guidelines < GUIDELINES_VERSION)
355 | self.is_missing_location
356 | (self.mod_notes.where(ModNote.is_pending).count() > 0)
357 )
359 @hybrid_property
360 def is_missing_location(self):
361 return (self.geom == None) | (self.geom_radius == None)
363 @hybrid_property
364 def is_visible(self):
365 return ~(self.is_banned | self.is_deleted)
367 @property
368 def coordinates(self):
369 if self.geom:
370 return get_coordinates(self.geom)
371 else:
372 return None
374 @property
375 def display_joined(self):
376 """
377 Returns the last active time rounded down to the nearest hour.
378 """
379 return self.joined.replace(minute=0, second=0, microsecond=0)
381 @property
382 def display_last_active(self):
383 """
384 Returns the last active time rounded down whatever is the "last active" coarsening.
385 """
386 return last_active_coarsen(self.last_active)
388 @hybrid_property
389 def phone_is_verified(self):
390 return (
391 self.phone_verification_verified is not None
392 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
393 )
395 @phone_is_verified.expression
396 def phone_is_verified(cls):
397 return (cls.phone_verification_verified != None) & (
398 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
399 )
401 @hybrid_property
402 def phone_code_expired(self):
403 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME
405 def __repr__(self):
406 return f"User(id={self.id}, email={self.email}, username={self.username})"
409class UserBadge(Base):
410 """
411 A badge on a user's profile
412 """
414 __tablename__ = "user_badges"
415 __table_args__ = (UniqueConstraint("user_id", "badge_id"),)
417 id = Column(BigInteger, primary_key=True)
419 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
420 # corresponds to "id" in badges.json
421 badge_id = Column(String, nullable=False, index=True)
423 # take this with a grain of salt, someone may get then lose a badge for whatever reason
424 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
426 user = relationship("User", backref="badges")
429class StrongVerificationAttemptStatus(enum.Enum):
430 ## full data states
431 # completed, this now provides verification for a user
432 succeeded = enum.auto()
434 ## no data states
435 # in progress: waiting for the user to scan the Iris code or open the app
436 in_progress_waiting_on_user_to_open_app = enum.auto()
437 # in progress: waiting for the user to scan MRZ or NFC/chip
438 in_progress_waiting_on_user_in_app = enum.auto()
439 # in progress, waiting for backend to pull verification data
440 in_progress_waiting_on_backend = enum.auto()
441 # failed at iris end, no data
442 failed = enum.auto()
444 ## minimal data states
445 # the data, except minimal deduplication data, was deleted
446 deleted = enum.auto()
449class PassportSex(enum.Enum):
450 """
451 We don't care about sex, we use gender on the platform. But passports apparently do.
452 """
454 male = enum.auto()
455 female = enum.auto()
456 unspecified = enum.auto()
459class StrongVerificationAttempt(Base):
460 """
461 An attempt to perform strong verification
462 """
464 __tablename__ = "strong_verification_attempts"
466 # our verification id
467 id = Column(BigInteger, primary_key=True)
469 # this is returned in the callback, and we look up the attempt via this
470 verification_attempt_token = Column(String, nullable=False, unique=True)
472 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
473 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
475 status = Column(
476 Enum(StrongVerificationAttemptStatus),
477 nullable=False,
478 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app,
479 )
481 ## full data
482 has_full_data = Column(Boolean, nullable=False, default=False)
483 # the data returned from iris, encrypted with a public key whose private key is kept offline
484 passport_encrypted_data = Column(Binary, nullable=True)
485 passport_date_of_birth = Column(Date, nullable=True)
486 passport_sex = Column(Enum(PassportSex), nullable=True)
488 ## minimal data: this will not be deleted
489 has_minimal_data = Column(Boolean, nullable=False, default=False)
490 passport_expiry_date = Column(Date, nullable=True)
491 passport_nationality = Column(String, nullable=True)
492 # last three characters of the passport number
493 passport_last_three_document_chars = Column(String, nullable=True)
495 iris_token = Column(String, nullable=False, unique=True)
496 iris_session_id = Column(BigInteger, nullable=False, unique=True)
498 passport_expiry_datetime = column_property(date_in_timezone(passport_expiry_date, "Etc/UTC"))
500 user = relationship("User")
502 @hybrid_property
503 def is_valid(self):
504 """
505 This only checks whether the attempt is a success and the passport is not expired, use `has_strong_verification` for full check
506 """
507 return (self.status == StrongVerificationAttemptStatus.succeeded) and (self.passport_expiry_datetime >= now())
509 @is_valid.expression
510 def is_valid(cls):
511 return (cls.status == StrongVerificationAttemptStatus.succeeded) & (
512 func.coalesce(cls.passport_expiry_datetime >= now(), False)
513 )
515 @hybrid_property
516 def is_visible(self):
517 return self.status != StrongVerificationAttemptStatus.deleted
519 @hybrid_method
520 def matches_birthdate(self, user):
521 return self.is_valid & (self.passport_date_of_birth == user.birthdate)
523 @hybrid_method
524 def matches_gender(self, user):
525 return self.is_valid & (
526 ((user.gender == "Woman") & (self.passport_sex == PassportSex.female))
527 | ((user.gender == "Man") & (self.passport_sex == PassportSex.male))
528 | (self.passport_sex == PassportSex.unspecified)
529 | (user.has_passport_sex_gender_exception == True)
530 )
532 @hybrid_method
533 def has_strong_verification(self, user):
534 return self.is_valid & self.matches_birthdate(user) & self.matches_gender(user)
536 __table_args__ = (
537 # used to look up verification status for a user
538 Index(
539 "ix_strong_verification_attempts_current",
540 user_id,
541 passport_expiry_date,
542 postgresql_where=status == StrongVerificationAttemptStatus.succeeded,
543 ),
544 # each passport can be verified only once
545 Index(
546 "ix_strong_verification_attempts_unique_succeeded",
547 passport_expiry_date,
548 passport_nationality,
549 passport_last_three_document_chars,
550 unique=True,
551 postgresql_where=(
552 (status == StrongVerificationAttemptStatus.succeeded)
553 | (status == StrongVerificationAttemptStatus.deleted)
554 ),
555 ),
556 # full data check
557 CheckConstraint(
558 "(has_full_data IS TRUE AND passport_encrypted_data IS NOT NULL AND passport_date_of_birth IS NOT NULL) OR \
559 (has_full_data IS FALSE AND passport_encrypted_data IS NULL AND passport_date_of_birth IS NULL)",
560 name="full_data_status",
561 ),
562 # minimal data check
563 CheckConstraint(
564 "(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 \
565 (has_minimal_data IS FALSE AND passport_expiry_date IS NULL AND passport_nationality IS NULL AND passport_last_three_document_chars IS NULL)",
566 name="minimal_data_status",
567 ),
568 # note on implications: p => q iff ~p OR q
569 # full data implies minimal data, has_minimal_data => has_full_data
570 CheckConstraint(
571 "(has_full_data IS FALSE) OR (has_minimal_data IS TRUE)",
572 name="full_data_implies_minimal_data",
573 ),
574 # succeeded implies full data
575 CheckConstraint(
576 "(NOT (status = 'succeeded')) OR (has_full_data IS TRUE)",
577 name="succeeded_implies_full_data",
578 ),
579 # in_progress/failed implies no_data
580 CheckConstraint(
581 "(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)",
582 name="in_progress_failed_iris_implies_no_data",
583 ),
584 # deleted implies minimal data
585 CheckConstraint(
586 "(NOT (status = 'deleted')) OR (has_minimal_data IS TRUE)",
587 name="deleted_implies_minimal_data",
588 ),
589 )
592class ModNote(Base):
593 """
594 A moderator note to a user. This could be a warning, just a note "hey, we did X", or any other similar message.
596 The user has to read and "acknowledge" the note before continuing onto the platform, and being un-jailed.
597 """
599 __tablename__ = "mod_notes"
600 id = Column(BigInteger, primary_key=True)
602 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
604 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
605 acknowledged = Column(DateTime(timezone=True), nullable=True)
607 # this is an internal ID to allow the mods to track different types of notes
608 internal_id = Column(String, nullable=False)
609 # the admin that left this note
610 creator_user_id = Column(ForeignKey("users.id"), nullable=False)
612 note_content = Column(String, nullable=False) # CommonMark without images
614 user = relationship("User", backref=backref("mod_notes", lazy="dynamic"), foreign_keys="ModNote.user_id")
616 def __repr__(self):
617 return f"ModeNote(id={self.id}, user={self.user}, created={self.created}, ack'd={self.acknowledged})"
619 @hybrid_property
620 def is_pending(self):
621 return self.acknowledged == None
623 __table_args__ = (
624 # used to look up pending notes
625 Index(
626 "ix_mod_notes_unacknowledged",
627 user_id,
628 postgresql_where=acknowledged == None,
629 ),
630 )
633class StrongVerificationCallbackEvent(Base):
634 __tablename__ = "strong_verification_callback_events"
636 id = Column(BigInteger, primary_key=True)
637 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
639 verification_attempt_id = Column(ForeignKey("strong_verification_attempts.id"), nullable=False, index=True)
641 iris_status = Column(String, nullable=False)
644class DonationType(enum.Enum):
645 one_time = enum.auto()
646 recurring = enum.auto()
649class DonationInitiation(Base):
650 """
651 Whenever someone initiaties a donation through the platform
652 """
654 __tablename__ = "donation_initiations"
655 id = Column(BigInteger, primary_key=True)
657 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
658 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
660 amount = Column(Integer, nullable=False)
661 stripe_checkout_session_id = Column(String, nullable=False)
663 donation_type = Column(Enum(DonationType), nullable=False)
665 user = relationship("User", backref="donation_initiations")
668class Invoice(Base):
669 """
670 Successful donations, both one off and recurring
672 Triggered by `payment_intent.succeeded` webhook
673 """
675 __tablename__ = "invoices"
677 id = Column(BigInteger, primary_key=True)
678 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
679 user_id = Column(ForeignKey("users.id"), nullable=False)
681 amount = Column(Float, nullable=False)
683 stripe_payment_intent_id = Column(String, nullable=False, unique=True)
684 stripe_receipt_url = Column(String, nullable=False)
686 user = relationship("User", backref="invoices")
689class LanguageFluency(enum.Enum):
690 # note that the numbering is important here, these are ordinal
691 beginner = 1
692 conversational = 2
693 fluent = 3
696class LanguageAbility(Base):
697 __tablename__ = "language_abilities"
698 __table_args__ = (
699 # Users can only have one language ability per language
700 UniqueConstraint("user_id", "language_code"),
701 )
703 id = Column(BigInteger, primary_key=True)
704 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
705 language_code = Column(ForeignKey("languages.code", deferrable=True), nullable=False)
706 fluency = Column(Enum(LanguageFluency), nullable=False)
708 user = relationship("User", backref="language_abilities")
709 language = relationship("Language")
712class RegionVisited(Base):
713 __tablename__ = "regions_visited"
714 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
716 id = Column(BigInteger, primary_key=True)
717 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
718 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
721class RegionLived(Base):
722 __tablename__ = "regions_lived"
723 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
725 id = Column(BigInteger, primary_key=True)
726 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
727 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
730class FriendStatus(enum.Enum):
731 pending = enum.auto()
732 accepted = enum.auto()
733 rejected = enum.auto()
734 cancelled = enum.auto()
737class FriendRelationship(Base):
738 """
739 Friendship relations between users
741 TODO: make this better with sqlalchemy self-referential stuff
742 TODO: constraint on only one row per user pair where accepted or pending
743 """
745 __tablename__ = "friend_relationships"
747 id = Column(BigInteger, primary_key=True)
749 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
750 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
752 status = Column(Enum(FriendStatus), nullable=False, default=FriendStatus.pending)
754 # timezones should always be UTC
755 time_sent = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
756 time_responded = Column(DateTime(timezone=True), nullable=True)
758 from_user = relationship("User", backref="friends_from", foreign_keys="FriendRelationship.from_user_id")
759 to_user = relationship("User", backref="friends_to", foreign_keys="FriendRelationship.to_user_id")
762class ContributeOption(enum.Enum):
763 yes = enum.auto()
764 maybe = enum.auto()
765 no = enum.auto()
768class ContributorForm(Base):
769 """
770 Someone filled in the contributor form
771 """
773 __tablename__ = "contributor_forms"
775 id = Column(BigInteger, primary_key=True)
777 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
778 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
780 ideas = Column(String, nullable=True)
781 features = Column(String, nullable=True)
782 experience = Column(String, nullable=True)
783 contribute = Column(Enum(ContributeOption), nullable=True)
784 contribute_ways = Column(ARRAY(String), nullable=False)
785 expertise = Column(String, nullable=True)
787 user = relationship("User", backref="contributor_forms")
789 @hybrid_property
790 def is_filled(self):
791 """
792 Whether the form counts as having been filled
793 """
794 return (
795 (self.ideas != None)
796 | (self.features != None)
797 | (self.experience != None)
798 | (self.contribute != None)
799 | (self.contribute_ways != [])
800 | (self.expertise != None)
801 )
803 @property
804 def should_notify(self):
805 """
806 If this evaluates to true, we send an email to the recruitment team.
808 We currently send if expertise is listed, or if they list a way to help outside of a set list
809 """
810 return (self.expertise != None) | (not set(self.contribute_ways).issubset({"community", "blog", "other"}))
813class SignupFlow(Base):
814 """
815 Signup flows/incomplete users
817 Coinciding fields have the same meaning as in User
818 """
820 __tablename__ = "signup_flows"
822 id = Column(BigInteger, primary_key=True)
824 # housekeeping
825 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
826 flow_token = Column(String, nullable=False, unique=True)
827 email_verified = Column(Boolean, nullable=False, default=False)
828 email_sent = Column(Boolean, nullable=False, default=False)
829 email_token = Column(String, nullable=True)
830 email_token_expiry = Column(DateTime(timezone=True), nullable=True)
832 ## Basic
833 name = Column(String, nullable=False)
834 # TODO: unique across both tables
835 email = Column(String, nullable=False, unique=True)
836 # TODO: invitation, attribution
838 ## Account
839 # TODO: unique across both tables
840 username = Column(String, nullable=True, unique=True)
841 hashed_password = Column(Binary, nullable=True)
842 birthdate = Column(Date, nullable=True) # in the timezone of birthplace
843 gender = Column(String, nullable=True)
844 hosting_status = Column(Enum(HostingStatus), nullable=True)
845 city = Column(String, nullable=True)
846 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
847 geom_radius = Column(Float, nullable=True)
849 accepted_tos = Column(Integer, nullable=True)
850 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")
852 opt_out_of_newsletter = Column(Boolean, nullable=True)
854 ## Feedback
855 filled_feedback = Column(Boolean, nullable=False, default=False)
856 ideas = Column(String, nullable=True)
857 features = Column(String, nullable=True)
858 experience = Column(String, nullable=True)
859 contribute = Column(Enum(ContributeOption), nullable=True)
860 contribute_ways = Column(ARRAY(String), nullable=True)
861 expertise = Column(String, nullable=True)
863 @hybrid_property
864 def token_is_valid(self):
865 return (self.email_token != None) & (self.email_token_expiry >= now())
867 @hybrid_property
868 def account_is_filled(self):
869 return (
870 (self.username != None)
871 & (self.birthdate != None)
872 & (self.gender != None)
873 & (self.hosting_status != None)
874 & (self.city != None)
875 & (self.geom != None)
876 & (self.geom_radius != None)
877 & (self.accepted_tos != None)
878 & (self.opt_out_of_newsletter != None)
879 )
881 @hybrid_property
882 def is_completed(self):
883 return (
884 self.email_verified
885 & self.account_is_filled
886 & self.filled_feedback
887 & (self.accepted_community_guidelines == GUIDELINES_VERSION)
888 )
891class LoginToken(Base):
892 """
893 A login token sent in an email to a user, allows them to sign in between the times defined by created and expiry
894 """
896 __tablename__ = "login_tokens"
897 token = Column(String, primary_key=True)
899 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
901 # timezones should always be UTC
902 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
903 expiry = Column(DateTime(timezone=True), nullable=False)
905 user = relationship("User", backref="login_tokens")
907 @hybrid_property
908 def is_valid(self):
909 return (self.created <= now()) & (self.expiry >= now())
911 def __repr__(self):
912 return f"LoginToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
915class PasswordResetToken(Base):
916 __tablename__ = "password_reset_tokens"
917 token = Column(String, primary_key=True)
919 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
921 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
922 expiry = Column(DateTime(timezone=True), nullable=False)
924 user = relationship("User", backref="password_reset_tokens")
926 @hybrid_property
927 def is_valid(self):
928 return (self.created <= now()) & (self.expiry >= now())
930 def __repr__(self):
931 return f"PasswordResetToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
934class AccountDeletionToken(Base):
935 __tablename__ = "account_deletion_tokens"
937 token = Column(String, primary_key=True)
939 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
941 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
942 expiry = Column(DateTime(timezone=True), nullable=False)
944 user = relationship("User", backref="account_deletion_tokens")
946 @hybrid_property
947 def is_valid(self):
948 return (self.created <= now()) & (self.expiry >= now())
950 def __repr__(self):
951 return f"AccountDeletionToken(token={self.token}, user_id={self.user_id}, created={self.created}, expiry={self.expiry})"
954class UserActivity(Base):
955 """
956 User activity: for each unique (user_id, period, ip_address, user_agent) tuple, keep track of number of api calls
958 Used for user "last active" as well as admin stuff
959 """
961 __tablename__ = "user_activity"
963 id = Column(BigInteger, primary_key=True)
965 user_id = Column(ForeignKey("users.id"), nullable=False)
966 # the start of a period of time, e.g. 1 hour during which we bin activeness
967 period = Column(DateTime(timezone=True), nullable=False)
969 # details of the browser, if available
970 ip_address = Column(INET, nullable=True)
971 user_agent = Column(String, nullable=True)
973 # count of api calls made with this ip, user_agent, and period
974 api_calls = Column(Integer, nullable=False, default=0)
976 __table_args__ = (
977 # helps look up this tuple quickly
978 Index(
979 "ix_user_activity_user_id_period_ip_address_user_agent",
980 user_id,
981 period,
982 ip_address,
983 user_agent,
984 unique=True,
985 ),
986 )
989class UserSession(Base):
990 """
991 API keys/session cookies for the app
993 There are two types of sessions: long-lived, and short-lived. Long-lived are
994 like when you choose "remember this browser": they will be valid for a long
995 time without the user interacting with the site. Short-lived sessions on the
996 other hand get invalidated quickly if the user does not interact with the
997 site.
999 Long-lived tokens are valid from `created` until `expiry`.
1001 Short-lived tokens expire after 168 hours (7 days) if they are not used.
1002 """
1004 __tablename__ = "sessions"
1005 token = Column(String, primary_key=True)
1007 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1009 # sessions are either "api keys" or "session cookies", otherwise identical
1010 # an api key is put in the authorization header (e.g. "authorization: Bearer <token>")
1011 # a session cookie is set in the "couchers-sesh" cookie (e.g. "cookie: couchers-sesh=<token>")
1012 # when a session is created, it's fixed as one or the other for security reasons
1013 # for api keys to be useful, they should be long lived and have a long expiry
1014 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))
1016 # whether it's a long-lived or short-lived session
1017 long_lived = Column(Boolean, nullable=False)
1019 # the time at which the session was created
1020 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1022 # the expiry of the session: the session *cannot* be refreshed past this
1023 expiry = Column(DateTime(timezone=True), nullable=False, server_default=func.now() + text("interval '730 days'"))
1025 # the time at which the token was invalidated, allows users to delete sessions
1026 deleted = Column(DateTime(timezone=True), nullable=True, default=None)
1028 # the last time this session was used
1029 last_seen = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1031 # count of api calls made with this token/session (if we're updating last_seen, might as well update this too)
1032 api_calls = Column(Integer, nullable=False, default=0)
1034 # details of the browser, if available
1035 # these are from the request creating the session, not used for anything else
1036 ip_address = Column(String, nullable=True)
1037 user_agent = Column(String, nullable=True)
1039 user = relationship("User", backref="sessions")
1041 @hybrid_property
1042 def is_valid(self):
1043 """
1044 It must have been created and not be expired or deleted.
1046 Also, if it's a short lived token, it must have been used in the last 168 hours.
1048 TODO: this probably won't run in python (instance level), only in sql (class level)
1049 """
1050 return (
1051 (self.created <= func.now())
1052 & (self.expiry >= func.now())
1053 & (self.deleted == None)
1054 & (self.long_lived | (func.now() - self.last_seen < text("interval '168 hours'")))
1055 )
1058class Conversation(Base):
1059 """
1060 Conversation brings together the different types of message/conversation types
1061 """
1063 __tablename__ = "conversations"
1065 id = Column(BigInteger, primary_key=True)
1066 # timezone should always be UTC
1067 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1069 def __repr__(self):
1070 return f"Conversation(id={self.id}, created={self.created})"
1073class GroupChat(Base):
1074 """
1075 Group chat
1076 """
1078 __tablename__ = "group_chats"
1080 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1082 title = Column(String, nullable=True)
1083 only_admins_invite = Column(Boolean, nullable=False, default=True)
1084 creator_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1085 is_dm = Column(Boolean, nullable=False)
1087 conversation = relationship("Conversation", backref="group_chat")
1088 creator = relationship("User", backref="created_group_chats")
1090 def __repr__(self):
1091 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})"
1094class GroupChatRole(enum.Enum):
1095 admin = enum.auto()
1096 participant = enum.auto()
1099class GroupChatSubscription(Base):
1100 """
1101 The recipient of a thread and information about when they joined/left/etc.
1102 """
1104 __tablename__ = "group_chat_subscriptions"
1105 id = Column(BigInteger, primary_key=True)
1107 # TODO: DB constraint on only one user+group_chat combo at a given time
1108 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1109 group_chat_id = Column(ForeignKey("group_chats.id"), nullable=False, index=True)
1111 # timezones should always be UTC
1112 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1113 left = Column(DateTime(timezone=True), nullable=True)
1115 role = Column(Enum(GroupChatRole), nullable=False)
1117 last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1119 # when this chat is muted until, DATETIME_INFINITY for "forever"
1120 muted_until = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_MINUS_INFINITY.isoformat())
1122 user = relationship("User", backref="group_chat_subscriptions")
1123 group_chat = relationship("GroupChat", backref=backref("subscriptions", lazy="dynamic"))
1125 def muted_display(self):
1126 """
1127 Returns (muted, muted_until) display values:
1128 1. If not muted, returns (False, None)
1129 2. If muted forever, returns (True, None)
1130 3. If muted until a given datetime returns (True, dt)
1131 """
1132 if self.muted_until < now():
1133 return (False, None)
1134 elif self.muted_until == DATETIME_INFINITY:
1135 return (True, None)
1136 else:
1137 return (True, self.muted_until)
1139 @hybrid_property
1140 def is_muted(self):
1141 return self.muted_until > func.now()
1143 def __repr__(self):
1144 return f"GroupChatSubscription(id={self.id}, user={self.user}, joined={self.joined}, left={self.left}, role={self.role}, group_chat={self.group_chat})"
1147class MessageType(enum.Enum):
1148 text = enum.auto()
1149 # e.g.
1150 # image =
1151 # emoji =
1152 # ...
1153 chat_created = enum.auto()
1154 chat_edited = enum.auto()
1155 user_invited = enum.auto()
1156 user_left = enum.auto()
1157 user_made_admin = enum.auto()
1158 user_removed_admin = enum.auto() # RemoveGroupChatAdmin: remove admin permission from a user in group chat
1159 host_request_status_changed = enum.auto()
1160 user_removed = enum.auto() # user is removed from group chat by amdin RemoveGroupChatUser
1163class HostRequestStatus(enum.Enum):
1164 pending = enum.auto()
1165 accepted = enum.auto()
1166 rejected = enum.auto()
1167 confirmed = enum.auto()
1168 cancelled = enum.auto()
1171class Message(Base):
1172 """
1173 A message.
1175 If message_type = text, then the message is a normal text message, otherwise, it's a special control message.
1176 """
1178 __tablename__ = "messages"
1180 id = Column(BigInteger, primary_key=True)
1182 # which conversation the message belongs in
1183 conversation_id = Column(ForeignKey("conversations.id"), nullable=False, index=True)
1185 # the user that sent the message/command
1186 author_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1188 # the message type, "text" is a text message, otherwise a "control message"
1189 message_type = Column(Enum(MessageType), nullable=False)
1191 # the target if a control message and requires target, e.g. if inviting a user, the user invited is the target
1192 target_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1194 # time sent, timezone should always be UTC
1195 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1197 # the plain-text message text if not control
1198 text = Column(String, nullable=True)
1200 # the new host request status if the message type is host_request_status_changed
1201 host_request_status_target = Column(Enum(HostRequestStatus), nullable=True)
1203 conversation = relationship("Conversation", backref="messages", order_by="Message.time.desc()")
1204 author = relationship("User", foreign_keys="Message.author_id")
1205 target = relationship("User", foreign_keys="Message.target_id")
1207 @property
1208 def is_normal_message(self):
1209 """
1210 There's only one normal type atm, text
1211 """
1212 return self.message_type == MessageType.text
1214 def __repr__(self):
1215 return f"Message(id={self.id}, time={self.time}, text={self.text}, author={self.author}, conversation={self.conversation})"
1218class ContentReport(Base):
1219 """
1220 A piece of content reported to admins
1221 """
1223 __tablename__ = "content_reports"
1225 id = Column(BigInteger, primary_key=True)
1227 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1229 # the user who reported or flagged the content
1230 reporting_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1232 # reason, e.g. spam, inappropriate, etc
1233 reason = Column(String, nullable=False)
1234 # a short description
1235 description = Column(String, nullable=False)
1237 # a reference to the content, see //docs/content_ref.md
1238 content_ref = Column(String, nullable=False)
1239 # the author of the content (e.g. the user who wrote the comment itself)
1240 author_user_id = Column(ForeignKey("users.id"), nullable=False)
1242 # details of the browser, if available
1243 user_agent = Column(String, nullable=False)
1244 # the URL the user was on when reporting the content
1245 page = Column(String, nullable=False)
1247 # see comments above for reporting vs author
1248 reporting_user = relationship("User", foreign_keys="ContentReport.reporting_user_id")
1249 author_user = relationship("User", foreign_keys="ContentReport.author_user_id")
1252class Email(Base):
1253 """
1254 Table of all dispatched emails for debugging purposes, etc.
1255 """
1257 __tablename__ = "emails"
1259 id = Column(String, primary_key=True)
1261 # timezone should always be UTC
1262 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1264 sender_name = Column(String, nullable=False)
1265 sender_email = Column(String, nullable=False)
1267 recipient = Column(String, nullable=False)
1268 subject = Column(String, nullable=False)
1270 plain = Column(String, nullable=False)
1271 html = Column(String, nullable=False)
1273 list_unsubscribe_header = Column(String, nullable=True)
1274 source_data = Column(String, nullable=True)
1277class SMS(Base):
1278 """
1279 Table of all sent SMSs for debugging purposes, etc.
1280 """
1282 __tablename__ = "smss"
1284 id = Column(BigInteger, primary_key=True)
1286 # timezone should always be UTC
1287 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1288 # AWS message id
1289 message_id = Column(String, nullable=False)
1291 # the SMS sender ID sent to AWS, name that the SMS appears to come from
1292 sms_sender_id = Column(String, nullable=False)
1293 number = Column(String, nullable=False)
1294 message = Column(String, nullable=False)
1297class HostRequest(Base):
1298 """
1299 A request to stay with a host
1300 """
1302 __tablename__ = "host_requests"
1304 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1305 surfer_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1306 host_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1308 # TODO: proper timezone handling
1309 timezone = "Etc/UTC"
1311 # dates in the timezone above
1312 from_date = Column(Date, nullable=False)
1313 to_date = Column(Date, nullable=False)
1315 # timezone aware start and end times of the request, can be compared to now()
1316 start_time = column_property(date_in_timezone(from_date, timezone))
1317 end_time = column_property(date_in_timezone(to_date, timezone) + text("interval '1 days'"))
1318 start_time_to_write_reference = column_property(date_in_timezone(to_date, timezone))
1319 # notice 1 day for midnight at the *end of the day*, then 14 days to write a ref
1320 end_time_to_write_reference = column_property(date_in_timezone(to_date, timezone) + text("interval '15 days'"))
1322 status = Column(Enum(HostRequestStatus), nullable=False)
1324 host_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1325 surfer_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1327 # number of reference reminders sent out
1328 host_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1329 surfer_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1331 surfer = relationship("User", backref="host_requests_sent", foreign_keys="HostRequest.surfer_user_id")
1332 host = relationship("User", backref="host_requests_received", foreign_keys="HostRequest.host_user_id")
1333 conversation = relationship("Conversation")
1335 @hybrid_property
1336 def can_write_reference(self):
1337 return (
1338 ((self.status == HostRequestStatus.confirmed) | (self.status == HostRequestStatus.accepted))
1339 & (now() >= self.start_time_to_write_reference)
1340 & (now() <= self.end_time_to_write_reference)
1341 )
1343 @can_write_reference.expression
1344 def can_write_reference(cls):
1345 return (
1346 ((cls.status == HostRequestStatus.confirmed) | (cls.status == HostRequestStatus.accepted))
1347 & (func.now() >= cls.start_time_to_write_reference)
1348 & (func.now() <= cls.end_time_to_write_reference)
1349 )
1351 def __repr__(self):
1352 return f"HostRequest(id={self.conversation_id}, surfer_user_id={self.surfer_user_id}, host_user_id={self.host_user_id}...)"
1355class ReferenceType(enum.Enum):
1356 friend = enum.auto()
1357 surfed = enum.auto() # The "from" user surfed with the "to" user
1358 hosted = enum.auto() # The "from" user hosted the "to" user
1361class Reference(Base):
1362 """
1363 Reference from one user to another
1364 """
1366 __tablename__ = "references"
1368 id = Column(BigInteger, primary_key=True)
1369 # timezone should always be UTC
1370 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1372 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1373 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1375 reference_type = Column(Enum(ReferenceType), nullable=False)
1377 host_request_id = Column(ForeignKey("host_requests.id"), nullable=True)
1379 text = Column(String, nullable=False) # plain text
1380 # text that's only visible to mods
1381 private_text = Column(String, nullable=True) # plain text
1383 rating = Column(Float, nullable=False)
1384 was_appropriate = Column(Boolean, nullable=False)
1386 from_user = relationship("User", backref="references_from", foreign_keys="Reference.from_user_id")
1387 to_user = relationship("User", backref="references_to", foreign_keys="Reference.to_user_id")
1389 host_request = relationship("HostRequest", backref="references")
1391 __table_args__ = (
1392 # Rating must be between 0 and 1, inclusive
1393 CheckConstraint(
1394 "rating BETWEEN 0 AND 1",
1395 name="rating_between_0_and_1",
1396 ),
1397 # Has host_request_id or it's a friend reference
1398 CheckConstraint(
1399 "(host_request_id IS NOT NULL) <> (reference_type = 'friend')",
1400 name="host_request_id_xor_friend_reference",
1401 ),
1402 # Each user can leave at most one friend reference to another user
1403 Index(
1404 "ix_references_unique_friend_reference",
1405 from_user_id,
1406 to_user_id,
1407 reference_type,
1408 unique=True,
1409 postgresql_where=(reference_type == ReferenceType.friend),
1410 ),
1411 # Each user can leave at most one reference to another user for each stay
1412 Index(
1413 "ix_references_unique_per_host_request",
1414 from_user_id,
1415 to_user_id,
1416 host_request_id,
1417 unique=True,
1418 postgresql_where=(host_request_id != None),
1419 ),
1420 )
1422 @property
1423 def should_report(self):
1424 """
1425 If this evaluates to true, we send a report to the moderation team.
1426 """
1427 return self.rating <= 0.4 or not self.was_appropriate or self.private_text
1430class InitiatedUpload(Base):
1431 """
1432 Started downloads, not necessarily complete yet.
1433 """
1435 __tablename__ = "initiated_uploads"
1437 key = Column(String, primary_key=True)
1439 # timezones should always be UTC
1440 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1441 expiry = Column(DateTime(timezone=True), nullable=False)
1443 initiator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1445 initiator_user = relationship("User")
1447 @hybrid_property
1448 def is_valid(self):
1449 return (self.created <= func.now()) & (self.expiry >= func.now())
1452class Upload(Base):
1453 """
1454 Completed uploads.
1455 """
1457 __tablename__ = "uploads"
1458 key = Column(String, primary_key=True)
1460 filename = Column(String, nullable=False)
1461 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1462 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1464 # photo credit, etc
1465 credit = Column(String, nullable=True)
1467 creator_user = relationship("User", backref="uploads", foreign_keys="Upload.creator_user_id")
1469 def _url(self, size):
1470 return urls.media_url(filename=self.filename, size=size)
1472 @property
1473 def thumbnail_url(self):
1474 return self._url("thumbnail")
1476 @property
1477 def full_url(self):
1478 return self._url("full")
1481communities_seq = Sequence("communities_seq")
1484class Node(Base):
1485 """
1486 Node, i.e. geographical subdivision of the world
1488 Administered by the official cluster
1489 """
1491 __tablename__ = "nodes"
1493 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1495 # name and description come from official cluster
1496 parent_node_id = Column(ForeignKey("nodes.id"), nullable=True, index=True)
1497 geom = deferred(Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False))
1498 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1500 parent_node = relationship("Node", backref="child_nodes", remote_side="Node.id")
1502 contained_users = relationship(
1503 "User",
1504 primaryjoin="func.ST_Contains(foreign(Node.geom), User.geom).as_comparison(1, 2)",
1505 viewonly=True,
1506 uselist=True,
1507 )
1509 contained_user_ids = association_proxy("contained_users", "id")
1512class Cluster(Base):
1513 """
1514 Cluster, administered grouping of content
1515 """
1517 __tablename__ = "clusters"
1519 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1520 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1521 name = Column(String, nullable=False)
1522 # short description
1523 description = Column(String, nullable=False)
1524 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1526 is_official_cluster = Column(Boolean, nullable=False, default=False)
1528 slug = column_property(func.slugify(name))
1530 official_cluster_for_node = relationship(
1531 "Node",
1532 primaryjoin="and_(Cluster.parent_node_id == Node.id, Cluster.is_official_cluster)",
1533 backref=backref("official_cluster", uselist=False),
1534 uselist=False,
1535 viewonly=True,
1536 )
1538 parent_node = relationship(
1539 "Node", backref="child_clusters", remote_side="Node.id", foreign_keys="Cluster.parent_node_id"
1540 )
1542 nodes = relationship("Cluster", backref="clusters", secondary="node_cluster_associations", viewonly=True)
1543 # all pages
1544 pages = relationship(
1545 "Page", backref="clusters", secondary="cluster_page_associations", lazy="dynamic", viewonly=True
1546 )
1547 events = relationship("Event", backref="clusters", secondary="cluster_event_associations", viewonly=True)
1548 discussions = relationship(
1549 "Discussion", backref="clusters", secondary="cluster_discussion_associations", viewonly=True
1550 )
1552 # includes also admins
1553 members = relationship(
1554 "User",
1555 lazy="dynamic",
1556 backref="cluster_memberships",
1557 secondary="cluster_subscriptions",
1558 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1559 secondaryjoin="User.id == ClusterSubscription.user_id",
1560 viewonly=True,
1561 )
1563 admins = relationship(
1564 "User",
1565 lazy="dynamic",
1566 backref="cluster_adminships",
1567 secondary="cluster_subscriptions",
1568 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1569 secondaryjoin="and_(User.id == ClusterSubscription.user_id, ClusterSubscription.role == 'admin')",
1570 viewonly=True,
1571 )
1573 main_page = relationship(
1574 "Page",
1575 primaryjoin="and_(Cluster.id == Page.owner_cluster_id, Page.type == 'main_page')",
1576 viewonly=True,
1577 uselist=False,
1578 )
1580 @property
1581 def is_leaf(self) -> bool:
1582 """Whether the cluster is a leaf node in the cluster hierarchy."""
1583 return len(self.parent_node.child_nodes) == 0
1585 __table_args__ = (
1586 # Each node can have at most one official cluster
1587 Index(
1588 "ix_clusters_owner_parent_node_id_is_official_cluster",
1589 parent_node_id,
1590 is_official_cluster,
1591 unique=True,
1592 postgresql_where=is_official_cluster,
1593 ),
1594 )
1597class NodeClusterAssociation(Base):
1598 """
1599 NodeClusterAssociation, grouping of nodes
1600 """
1602 __tablename__ = "node_cluster_associations"
1603 __table_args__ = (UniqueConstraint("node_id", "cluster_id"),)
1605 id = Column(BigInteger, primary_key=True)
1607 node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1608 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1610 node = relationship("Node", backref="node_cluster_associations")
1611 cluster = relationship("Cluster", backref="node_cluster_associations")
1614class ClusterRole(enum.Enum):
1615 member = enum.auto()
1616 admin = enum.auto()
1619class ClusterSubscription(Base):
1620 """
1621 ClusterSubscription of a user
1622 """
1624 __tablename__ = "cluster_subscriptions"
1625 __table_args__ = (UniqueConstraint("user_id", "cluster_id"),)
1627 id = Column(BigInteger, primary_key=True)
1629 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1630 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1631 role = Column(Enum(ClusterRole), nullable=False)
1633 user = relationship("User", backref="cluster_subscriptions")
1634 cluster = relationship("Cluster", backref="cluster_subscriptions")
1637class ClusterPageAssociation(Base):
1638 """
1639 pages related to clusters
1640 """
1642 __tablename__ = "cluster_page_associations"
1643 __table_args__ = (UniqueConstraint("page_id", "cluster_id"),)
1645 id = Column(BigInteger, primary_key=True)
1647 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1648 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1650 page = relationship("Page", backref="cluster_page_associations")
1651 cluster = relationship("Cluster", backref="cluster_page_associations")
1654class PageType(enum.Enum):
1655 main_page = enum.auto()
1656 place = enum.auto()
1657 guide = enum.auto()
1660class Page(Base):
1661 """
1662 similar to a wiki page about a community, POI or guide
1663 """
1665 __tablename__ = "pages"
1667 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1669 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1670 type = Column(Enum(PageType), nullable=False)
1671 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1672 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1673 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1675 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
1677 parent_node = relationship("Node", backref="child_pages", remote_side="Node.id", foreign_keys="Page.parent_node_id")
1679 thread = relationship("Thread", backref="page", uselist=False)
1680 creator_user = relationship("User", backref="created_pages", foreign_keys="Page.creator_user_id")
1681 owner_user = relationship("User", backref="owned_pages", foreign_keys="Page.owner_user_id")
1682 owner_cluster = relationship(
1683 "Cluster", backref=backref("owned_pages", lazy="dynamic"), uselist=False, foreign_keys="Page.owner_cluster_id"
1684 )
1686 editors = relationship("User", secondary="page_versions", viewonly=True)
1688 __table_args__ = (
1689 # Only one of owner_user and owner_cluster should be set
1690 CheckConstraint(
1691 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
1692 name="one_owner",
1693 ),
1694 # Only clusters can own main pages
1695 CheckConstraint(
1696 "NOT (owner_cluster_id IS NULL AND type = 'main_page')",
1697 name="main_page_owned_by_cluster",
1698 ),
1699 # Each cluster can have at most one main page
1700 Index(
1701 "ix_pages_owner_cluster_id_type",
1702 owner_cluster_id,
1703 type,
1704 unique=True,
1705 postgresql_where=(type == PageType.main_page),
1706 ),
1707 )
1709 def __repr__(self):
1710 return f"Page({self.id=})"
1713class PageVersion(Base):
1714 """
1715 version of page content
1716 """
1718 __tablename__ = "page_versions"
1720 id = Column(BigInteger, primary_key=True)
1722 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1723 editor_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1724 title = Column(String, nullable=False)
1725 content = Column(String, nullable=False) # CommonMark without images
1726 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
1727 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
1728 # the human-readable address
1729 address = Column(String, nullable=True)
1730 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1732 slug = column_property(func.slugify(title))
1734 page = relationship("Page", backref="versions", order_by="PageVersion.id")
1735 editor_user = relationship("User", backref="edited_pages")
1736 photo = relationship("Upload")
1738 __table_args__ = (
1739 # Geom and address must either both be null or both be set
1740 CheckConstraint(
1741 "(geom IS NULL) = (address IS NULL)",
1742 name="geom_iff_address",
1743 ),
1744 )
1746 @property
1747 def coordinates(self):
1748 # returns (lat, lng) or None
1749 if self.geom:
1750 return get_coordinates(self.geom)
1751 else:
1752 return None
1754 def __repr__(self):
1755 return f"PageVersion({self.id=}, {self.page_id=})"
1758class ClusterEventAssociation(Base):
1759 """
1760 events related to clusters
1761 """
1763 __tablename__ = "cluster_event_associations"
1764 __table_args__ = (UniqueConstraint("event_id", "cluster_id"),)
1766 id = Column(BigInteger, primary_key=True)
1768 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1769 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1771 event = relationship("Event", backref="cluster_event_associations")
1772 cluster = relationship("Cluster", backref="cluster_event_associations")
1775class Event(Base):
1776 """
1777 An event is compose of two parts:
1779 * An event template (Event)
1780 * An occurrence (EventOccurrence)
1782 One-off events will have one of each; repeating events will have one Event,
1783 multiple EventOccurrences, one for each time the event happens.
1784 """
1786 __tablename__ = "events"
1788 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)
1791 title = Column(String, nullable=False)
1793 slug = column_property(func.slugify(title))
1795 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1796 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1797 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1798 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1799 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
1801 parent_node = relationship(
1802 "Node", backref="child_events", remote_side="Node.id", foreign_keys="Event.parent_node_id"
1803 )
1804 thread = relationship("Thread", backref="event", uselist=False)
1805 subscribers = relationship(
1806 "User", backref="subscribed_events", secondary="event_subscriptions", lazy="dynamic", viewonly=True
1807 )
1808 organizers = relationship(
1809 "User", backref="organized_events", secondary="event_organizers", lazy="dynamic", viewonly=True
1810 )
1811 creator_user = relationship("User", backref="created_events", foreign_keys="Event.creator_user_id")
1812 owner_user = relationship("User", backref="owned_events", foreign_keys="Event.owner_user_id")
1813 owner_cluster = relationship(
1814 "Cluster",
1815 backref=backref("owned_events", lazy="dynamic"),
1816 uselist=False,
1817 foreign_keys="Event.owner_cluster_id",
1818 )
1820 __table_args__ = (
1821 # Only one of owner_user and owner_cluster should be set
1822 CheckConstraint(
1823 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
1824 name="one_owner",
1825 ),
1826 )
1829class EventOccurrence(Base):
1830 __tablename__ = "event_occurrences"
1832 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1833 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1835 # the user that created this particular occurrence of a repeating event (same as event.creator_user_id if single event)
1836 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1837 content = Column(String, nullable=False) # CommonMark without images
1838 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
1840 is_cancelled = Column(Boolean, nullable=False, default=False, server_default=text("false"))
1841 is_deleted = Column(Boolean, nullable=False, default=False, server_default=text("false"))
1843 # a null geom is an online-only event
1844 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
1845 # physical address, iff geom is not null
1846 address = Column(String, nullable=True)
1847 # videoconferencing link, etc, must be specified if no geom, otherwise optional
1848 link = Column(String, nullable=True)
1850 timezone = "Etc/UTC"
1852 # time during which the event takes place; this is a range type (instead of separate start+end times) which
1853 # simplifies database constraints, etc
1854 during = Column(TSTZRANGE, nullable=False)
1856 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1857 last_edited = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1859 creator_user = relationship(
1860 "User", backref="created_event_occurrences", foreign_keys="EventOccurrence.creator_user_id"
1861 )
1862 event = relationship(
1863 "Event",
1864 backref=backref("occurrences", lazy="dynamic"),
1865 remote_side="Event.id",
1866 foreign_keys="EventOccurrence.event_id",
1867 )
1869 photo = relationship("Upload")
1871 __table_args__ = (
1872 # Geom and address go together
1873 CheckConstraint(
1874 # geom and address are either both null or neither of them are null
1875 "(geom IS NULL) = (address IS NULL)",
1876 name="geom_iff_address",
1877 ),
1878 # Online-only events need a link, note that online events may also have a link
1879 CheckConstraint(
1880 # exactly oen of geom or link is non-null
1881 "(geom IS NULL) <> (link IS NULL)",
1882 name="link_or_geom",
1883 ),
1884 # Can't have overlapping occurrences in the same Event
1885 ExcludeConstraint(("event_id", "="), ("during", "&&"), name="event_occurrences_event_id_during_excl"),
1886 )
1888 @property
1889 def coordinates(self):
1890 # returns (lat, lng) or None
1891 if self.geom:
1892 return get_coordinates(self.geom)
1893 else:
1894 return None
1896 @hybrid_property
1897 def start_time(self):
1898 return self.during.lower
1900 @start_time.expression
1901 def start_time(cls):
1902 return func.lower(cls.during)
1904 @hybrid_property
1905 def end_time(self):
1906 return self.during.upper
1908 @end_time.expression
1909 def end_time(cls):
1910 return func.upper(cls.during)
1913class EventSubscription(Base):
1914 """
1915 Users' subscriptions to events
1916 """
1918 __tablename__ = "event_subscriptions"
1919 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
1921 id = Column(BigInteger, primary_key=True)
1923 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1924 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1925 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1927 user = relationship("User")
1928 event = relationship("Event")
1931class EventOrganizer(Base):
1932 """
1933 Organizers for events
1934 """
1936 __tablename__ = "event_organizers"
1937 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
1939 id = Column(BigInteger, primary_key=True)
1941 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1942 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1943 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1945 user = relationship("User")
1946 event = relationship("Event")
1949class AttendeeStatus(enum.Enum):
1950 going = enum.auto()
1951 maybe = enum.auto()
1954class EventOccurrenceAttendee(Base):
1955 """
1956 Attendees for events
1957 """
1959 __tablename__ = "event_occurrence_attendees"
1960 __table_args__ = (UniqueConstraint("occurrence_id", "user_id"),)
1962 id = Column(BigInteger, primary_key=True)
1964 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1965 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
1966 responded = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1967 attendee_status = Column(Enum(AttendeeStatus), nullable=False)
1969 user = relationship("User")
1970 occurrence = relationship("EventOccurrence", backref=backref("attendances", lazy="dynamic"))
1973class EventCommunityInviteRequest(Base):
1974 """
1975 Requests to send out invitation notifications/emails to the community for a given event occurrence
1976 """
1978 __tablename__ = "event_community_invite_requests"
1980 id = Column(BigInteger, primary_key=True)
1982 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
1983 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1985 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1987 decided = Column(DateTime(timezone=True), nullable=True)
1988 decided_by_user_id = Column(ForeignKey("users.id"), nullable=True)
1989 approved = Column(Boolean, nullable=True)
1991 occurrence = relationship("EventOccurrence", backref=backref("community_invite_requests", lazy="dynamic"))
1992 user = relationship("User", foreign_keys="EventCommunityInviteRequest.user_id")
1994 __table_args__ = (
1995 # each user can only request once
1996 UniqueConstraint("occurrence_id", "user_id"),
1997 # each event can only have one notification sent out
1998 Index(
1999 "ix_event_community_invite_requests_unique",
2000 occurrence_id,
2001 unique=True,
2002 postgresql_where=and_(approved.is_not(None), approved == True),
2003 ),
2004 # decided and approved ought to be null simultaneously
2005 CheckConstraint(
2006 "((decided IS NULL) AND (decided_by_user_id IS NULL) AND (approved IS NULL)) OR \
2007 ((decided IS NOT NULL) AND (decided_by_user_id IS NOT NULL) AND (approved IS NOT NULL))",
2008 name="decided_approved",
2009 ),
2010 )
2013class ClusterDiscussionAssociation(Base):
2014 """
2015 discussions related to clusters
2016 """
2018 __tablename__ = "cluster_discussion_associations"
2019 __table_args__ = (UniqueConstraint("discussion_id", "cluster_id"),)
2021 id = Column(BigInteger, primary_key=True)
2023 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2024 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2026 discussion = relationship("Discussion", backref="cluster_discussion_associations")
2027 cluster = relationship("Cluster", backref="cluster_discussion_associations")
2030class Discussion(Base):
2031 """
2032 forum board
2033 """
2035 __tablename__ = "discussions"
2037 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
2039 title = Column(String, nullable=False)
2040 content = Column(String, nullable=False)
2041 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
2042 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2044 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2045 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2047 slug = column_property(func.slugify(title))
2049 thread = relationship("Thread", backref="discussion", uselist=False)
2051 subscribers = relationship("User", backref="discussions", secondary="discussion_subscriptions", viewonly=True)
2053 creator_user = relationship("User", backref="created_discussions", foreign_keys="Discussion.creator_user_id")
2054 owner_cluster = relationship("Cluster", backref=backref("owned_discussions", lazy="dynamic"), uselist=False)
2057class DiscussionSubscription(Base):
2058 """
2059 users subscriptions to discussions
2060 """
2062 __tablename__ = "discussion_subscriptions"
2063 __table_args__ = (UniqueConstraint("discussion_id", "user_id"),)
2065 id = Column(BigInteger, primary_key=True)
2067 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2068 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2069 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2070 left = Column(DateTime(timezone=True), nullable=True)
2072 user = relationship("User", backref="discussion_subscriptions")
2073 discussion = relationship("Discussion", backref="discussion_subscriptions")
2076class Thread(Base):
2077 """
2078 Thread
2079 """
2081 __tablename__ = "threads"
2083 id = Column(BigInteger, primary_key=True)
2085 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2086 deleted = Column(DateTime(timezone=True), nullable=True)
2089class Comment(Base):
2090 """
2091 Comment
2092 """
2094 __tablename__ = "comments"
2096 id = Column(BigInteger, primary_key=True)
2098 thread_id = Column(ForeignKey("threads.id"), nullable=False, index=True)
2099 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2100 content = Column(String, nullable=False) # CommonMark without images
2101 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2102 deleted = Column(DateTime(timezone=True), nullable=True)
2104 thread = relationship("Thread", backref="comments")
2107class Reply(Base):
2108 """
2109 Reply
2110 """
2112 __tablename__ = "replies"
2114 id = Column(BigInteger, primary_key=True)
2116 comment_id = Column(ForeignKey("comments.id"), nullable=False, index=True)
2117 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2118 content = Column(String, nullable=False) # CommonMark without images
2119 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2120 deleted = Column(DateTime(timezone=True), nullable=True)
2122 comment = relationship("Comment", backref="replies")
2125class BackgroundJobState(enum.Enum):
2126 # job is fresh, waiting to be picked off the queue
2127 pending = enum.auto()
2128 # job complete
2129 completed = enum.auto()
2130 # error occured, will be retried
2131 error = enum.auto()
2132 # failed too many times, not retrying anymore
2133 failed = enum.auto()
2136class BackgroundJob(Base):
2137 """
2138 This table implements a queue of background jobs.
2139 """
2141 __tablename__ = "background_jobs"
2143 id = Column(BigInteger, primary_key=True)
2145 # used to discern which function should be triggered to service it
2146 job_type = Column(String, nullable=False)
2147 state = Column(Enum(BackgroundJobState), nullable=False, default=BackgroundJobState.pending)
2149 # time queued
2150 queued = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2152 # time at which we may next attempt it, for implementing exponential backoff
2153 next_attempt_after = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2155 # used to count number of retries for failed jobs
2156 try_count = Column(Integer, nullable=False, default=0)
2158 max_tries = Column(Integer, nullable=False, default=5)
2160 # protobuf encoded job payload
2161 payload = Column(Binary, nullable=False)
2163 # if the job failed, we write that info here
2164 failure_info = Column(String, nullable=True)
2166 __table_args__ = (
2167 # used in looking up background jobs to attempt
2168 # create index on background_jobs(next_attempt_after, (max_tries - try_count)) where state = 'pending' OR state = 'error';
2169 Index(
2170 "ix_background_jobs_lookup",
2171 next_attempt_after,
2172 (max_tries - try_count),
2173 postgresql_where=((state == BackgroundJobState.pending) | (state == BackgroundJobState.error)),
2174 ),
2175 )
2177 @hybrid_property
2178 def ready_for_retry(self):
2179 return (
2180 (self.next_attempt_after <= func.now())
2181 & (self.try_count < self.max_tries)
2182 & ((self.state == BackgroundJobState.pending) | (self.state == BackgroundJobState.error))
2183 )
2185 def __repr__(self):
2186 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})"
2189class NotificationDeliveryType(enum.Enum):
2190 # send push notification to mobile/web
2191 push = enum.auto()
2192 # send individual email immediately
2193 email = enum.auto()
2194 # send in digest
2195 digest = enum.auto()
2198dt = NotificationDeliveryType
2199nd = notification_data_pb2
2201dt_sec = [dt.email, dt.push]
2202dt_all = [dt.email, dt.push, dt.digest]
2205class NotificationTopicAction(enum.Enum):
2206 def __init__(self, topic_action, defaults, user_editable, data_type):
2207 self.topic, self.action = topic_action.split(":")
2208 self.defaults = defaults
2209 # for now user editable == not a security notification
2210 self.user_editable = user_editable
2212 self.data_type = data_type
2214 def unpack(self):
2215 return self.topic, self.action
2217 @property
2218 def display(self):
2219 return f"{self.topic}:{self.action}"
2221 def __str__(self):
2222 return self.display
2224 # topic, action, default delivery types
2225 friend_request__create = ("friend_request:create", dt_all, True, nd.FriendRequestCreate)
2226 friend_request__accept = ("friend_request:accept", dt_all, True, nd.FriendRequestAccept)
2228 # host requests
2229 host_request__create = ("host_request:create", dt_all, True, nd.HostRequestCreate)
2230 host_request__accept = ("host_request:accept", dt_all, True, nd.HostRequestAccept)
2231 host_request__reject = ("host_request:reject", dt_all, True, nd.HostRequestReject)
2232 host_request__confirm = ("host_request:confirm", dt_all, True, nd.HostRequestConfirm)
2233 host_request__cancel = ("host_request:cancel", dt_all, True, nd.HostRequestCancel)
2234 host_request__message = ("host_request:message", [dt.push, dt.digest], True, nd.HostRequestMessage)
2235 host_request__missed_messages = ("host_request:missed_messages", [dt.email], True, nd.HostRequestMissedMessages)
2237 # you receive a friend ref
2238 reference__receive_friend = ("reference:receive_friend", dt_all, True, nd.ReferenceReceiveFriend)
2239 # you receive a reference from ... the host
2240 reference__receive_hosted = ("reference:receive_hosted", dt_all, True, nd.ReferenceReceiveHostRequest)
2241 # ... the surfer
2242 reference__receive_surfed = ("reference:receive_surfed", dt_all, True, nd.ReferenceReceiveHostRequest)
2244 # you hosted
2245 reference__reminder_hosted = ("reference:reminder_hosted", dt_all, True, nd.ReferenceReminder)
2246 # you surfed
2247 reference__reminder_surfed = ("reference:reminder_surfed", dt_all, True, nd.ReferenceReminder)
2249 badge__add = ("badge:add", [dt.push, dt.digest], True, nd.BadgeAdd)
2250 badge__remove = ("badge:remove", [dt.push, dt.digest], True, nd.BadgeRemove)
2252 # group chats
2253 chat__message = ("chat:message", [dt.push, dt.digest], True, nd.ChatMessage)
2254 chat__missed_messages = ("chat:missed_messages", [dt.email], True, nd.ChatMissedMessages)
2256 # events
2257 # approved by mods
2258 event__create_approved = ("event:create_approved", dt_all, True, nd.EventCreate)
2259 # any user creates any event, default to no notifications
2260 event__create_any = ("event:create_any", [], True, nd.EventCreate)
2261 event__update = ("event:update", dt_all, True, nd.EventUpdate)
2262 event__cancel = ("event:cancel", dt_all, True, nd.EventCancel)
2263 event__delete = ("event:delete", dt_all, True, nd.EventDelete)
2264 event__invite_organizer = ("event:invite_organizer", dt_all, True, nd.EventInviteOrganizer)
2266 # account settings
2267 password__change = ("password:change", dt_sec, False, empty_pb2.Empty)
2268 email_address__change = ("email_address:change", dt_sec, False, nd.EmailAddressChange)
2269 email_address__verify = ("email_address:verify", dt_sec, False, empty_pb2.Empty)
2270 phone_number__change = ("phone_number:change", dt_sec, False, nd.PhoneNumberChange)
2271 phone_number__verify = ("phone_number:verify", dt_sec, False, nd.PhoneNumberVerify)
2272 # reset password
2273 password_reset__start = ("password_reset:start", dt_sec, False, nd.PasswordResetStart)
2274 password_reset__complete = ("password_reset:complete", dt_sec, False, empty_pb2.Empty)
2276 # account deletion
2277 account_deletion__start = ("account_deletion:start", dt_sec, False, nd.AccountDeletionStart)
2278 # no more pushing to do
2279 account_deletion__complete = ("account_deletion:complete", dt_sec, False, nd.AccountDeletionComplete)
2280 # undeleted
2281 account_deletion__recovered = ("account_deletion:recovered", dt_sec, False, empty_pb2.Empty)
2283 # admin actions
2284 gender__change = ("gender:change", dt_sec, False, nd.GenderChange)
2285 birthdate__change = ("birthdate:change", dt_sec, False, nd.BirthdateChange)
2286 api_key__create = ("api_key:create", dt_sec, False, nd.ApiKeyCreate)
2288 donation__received = ("donation:received", dt_sec, True, nd.DonationReceived)
2290 onboarding__reminder = ("onboarding:reminder", dt_sec, True, empty_pb2.Empty)
2292 modnote__create = ("modnote:create", dt_sec, False, empty_pb2.Empty)
2295class NotificationPreference(Base):
2296 __tablename__ = "notification_preferences"
2298 id = Column(BigInteger, primary_key=True)
2299 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2301 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2302 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2303 deliver = Column(Boolean, nullable=False)
2305 user = relationship("User", foreign_keys="NotificationPreference.user_id")
2307 __table_args__ = (UniqueConstraint("user_id", "topic_action", "delivery_type"),)
2310class Notification(Base):
2311 """
2312 Table for accumulating notifications until it is time to send email digest
2313 """
2315 __tablename__ = "notifications"
2317 id = Column(BigInteger, primary_key=True)
2318 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2320 # recipient user id
2321 user_id = Column(ForeignKey("users.id"), nullable=False)
2323 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2324 key = Column(String, nullable=False)
2326 data = Column(Binary, nullable=False)
2328 user = relationship("User", foreign_keys="Notification.user_id")
2330 __table_args__ = (
2331 # used in looking up which notifications need delivery
2332 Index(
2333 "ix_notifications_created",
2334 created,
2335 ),
2336 )
2338 @property
2339 def topic(self):
2340 return self.topic_action.topic
2342 @property
2343 def action(self):
2344 return self.topic_action.action
2347class NotificationDelivery(Base):
2348 __tablename__ = "notification_deliveries"
2350 id = Column(BigInteger, primary_key=True)
2351 notification_id = Column(ForeignKey("notifications.id"), nullable=False, index=True)
2352 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2353 delivered = Column(DateTime(timezone=True), nullable=True)
2354 read = Column(DateTime(timezone=True), nullable=True)
2355 # todo: enum of "phone, web, digest"
2356 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2357 # todo: device id
2358 # todo: receipt id, etc
2359 notification = relationship("Notification", foreign_keys="NotificationDelivery.notification_id")
2361 __table_args__ = (
2362 UniqueConstraint("notification_id", "delivery_type"),
2363 # used in looking up which notifications need delivery
2364 Index(
2365 "ix_notification_deliveries_delivery_type",
2366 delivery_type,
2367 postgresql_where=(delivered != None),
2368 ),
2369 Index(
2370 "ix_notification_deliveries_dt_ni_dnull",
2371 delivery_type,
2372 notification_id,
2373 delivered == None,
2374 ),
2375 )
2378class PushNotificationSubscription(Base):
2379 __tablename__ = "push_notification_subscriptions"
2381 id = Column(BigInteger, primary_key=True)
2382 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2384 # which user this is connected to
2385 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2387 # these come from https://developer.mozilla.org/en-US/docs/Web/API/PushSubscription
2388 # the endpoint
2389 endpoint = Column(String, nullable=False)
2390 # the "auth" key
2391 auth_key = Column(Binary, nullable=False)
2392 # the "p256dh" key
2393 p256dh_key = Column(Binary, nullable=False)
2395 full_subscription_info = Column(String, nullable=False)
2397 # the browse user-agent, so we can tell the user what browser notifications are going to
2398 user_agent = Column(String, nullable=True)
2400 # when it was disabled
2401 disabled_at = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_INFINITY.isoformat())
2403 user = relationship("User")
2406class PushNotificationDeliveryAttempt(Base):
2407 __tablename__ = "push_notification_delivery_attempt"
2409 id = Column(BigInteger, primary_key=True)
2410 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2412 push_notification_subscription_id = Column(
2413 ForeignKey("push_notification_subscriptions.id"), nullable=False, index=True
2414 )
2416 success = Column(Boolean, nullable=False)
2417 # the HTTP status code, 201 is success
2418 status_code = Column(Integer, nullable=False)
2420 # can be null if it was a success
2421 response = Column(String, nullable=True)
2423 push_notification_subscription = relationship("PushNotificationSubscription")
2426class Language(Base):
2427 """
2428 Table of allowed languages (a subset of ISO639-3)
2429 """
2431 __tablename__ = "languages"
2433 # ISO639-3 language code, in lowercase, e.g. fin, eng
2434 code = Column(String(3), primary_key=True)
2436 # the english name
2437 name = Column(String, nullable=False, unique=True)
2440class Region(Base):
2441 """
2442 Table of regions
2443 """
2445 __tablename__ = "regions"
2447 # iso 3166-1 alpha3 code in uppercase, e.g. FIN, USA
2448 code = Column(String(3), primary_key=True)
2450 # the name, e.g. Finland, United States
2451 # this is the display name in English, should be the "common name", not "Republic of Finland"
2452 name = Column(String, nullable=False, unique=True)
2455class UserBlock(Base):
2456 """
2457 Table of blocked users
2458 """
2460 __tablename__ = "user_blocks"
2461 __table_args__ = (UniqueConstraint("blocking_user_id", "blocked_user_id"),)
2463 id = Column(BigInteger, primary_key=True)
2465 blocking_user_id = Column(ForeignKey("users.id"), nullable=False)
2466 blocked_user_id = Column(ForeignKey("users.id"), nullable=False)
2467 time_blocked = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2469 blocking_user = relationship("User", foreign_keys="UserBlock.blocking_user_id")
2470 blocked_user = relationship("User", foreign_keys="UserBlock.blocked_user_id")
2473class APICall(Base):
2474 """
2475 API call logs
2476 """
2478 __tablename__ = "api_calls"
2479 __table_args__ = {"schema": "logging"}
2481 id = Column(BigInteger, primary_key=True)
2483 # whether the call was made using an api key or session cookies
2484 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))
2486 # backend version (normally e.g. develop-31469e3), allows us to figure out which proto definitions were used
2487 # note that `default` is a python side default, not hardcoded into DB schema
2488 version = Column(String, nullable=False, default=config["VERSION"])
2490 # approximate time of the call
2491 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2493 # the method call name, e.g. "/org.couchers.api.core.API/ListFriends"
2494 method = Column(String, nullable=False)
2496 # gRPC status code name, e.g. FAILED_PRECONDITION, None if success
2497 status_code = Column(String, nullable=True)
2499 # handler duration (excluding serialization, etc)
2500 duration = Column(Float, nullable=False)
2502 # user_id of caller, None means not logged in
2503 user_id = Column(BigInteger, nullable=True)
2505 # sanitized request bytes
2506 request = Column(Binary, nullable=True)
2508 # sanitized response bytes
2509 response = Column(Binary, nullable=True)
2511 # whether response bytes have been truncated
2512 response_truncated = Column(Boolean, nullable=False, server_default=text("false"))
2514 # the exception traceback, if any
2515 traceback = Column(String, nullable=True)
2517 # human readable perf report
2518 perf_report = Column(String, nullable=True)
2520 # details of the browser, if available
2521 ip_address = Column(String, nullable=True)
2522 user_agent = Column(String, nullable=True)
2525class AccountDeletionReason(Base):
2526 __tablename__ = "account_deletion_reason"
2528 id = Column(BigInteger, primary_key=True)
2529 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2530 user_id = Column(ForeignKey("users.id"), nullable=False)
2531 reason = Column(String, nullable=True)
2533 user = relationship("User")