Coverage for src/couchers/models.py: 99%
1126 statements
« prev ^ index » next coverage.py v7.6.10, created at 2025-06-01 15:07 +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, not_, 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 (
44 date_in_timezone,
45 get_coordinates,
46 last_active_coarsen,
47 now,
48)
49from proto import notification_data_pb2
51meta = MetaData(
52 naming_convention={
53 "ix": "ix_%(column_0_label)s",
54 "uq": "uq_%(table_name)s_%(column_0_name)s",
55 "ck": "ck_%(table_name)s_%(constraint_name)s",
56 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
57 "pk": "pk_%(table_name)s",
58 }
59)
61Base = declarative_base(metadata=meta)
64class HostingStatus(enum.Enum):
65 can_host = enum.auto()
66 maybe = enum.auto()
67 cant_host = enum.auto()
70class MeetupStatus(enum.Enum):
71 wants_to_meetup = enum.auto()
72 open_to_meetup = enum.auto()
73 does_not_want_to_meetup = enum.auto()
76class SmokingLocation(enum.Enum):
77 yes = enum.auto()
78 window = enum.auto()
79 outside = enum.auto()
80 no = enum.auto()
83class SleepingArrangement(enum.Enum):
84 private = enum.auto()
85 common = enum.auto()
86 shared_room = enum.auto()
89class ParkingDetails(enum.Enum):
90 free_onsite = enum.auto()
91 free_offsite = enum.auto()
92 paid_onsite = enum.auto()
93 paid_offsite = enum.auto()
96class ProfilePublicVisibility(enum.Enum):
97 # no public info
98 nothing = enum.auto()
99 # only show on map, randomized, unclickable
100 map_only = enum.auto()
101 # name, gender, location, hosting/meetup status, badges, number of references, and signup time
102 limited = enum.auto()
103 # full about me except additional info (hide my home)
104 most = enum.auto()
105 # all but references
106 full = enum.auto()
109class TimezoneArea(Base):
110 __tablename__ = "timezone_areas"
111 id = Column(BigInteger, primary_key=True)
113 tzid = Column(String)
114 geom = Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False)
116 __table_args__ = (
117 Index(
118 "ix_timezone_areas_geom_tzid",
119 geom,
120 tzid,
121 postgresql_using="gist",
122 ),
123 )
126class User(Base):
127 """
128 Basic user and profile details
129 """
131 __tablename__ = "users"
133 id = Column(BigInteger, primary_key=True)
135 username = Column(String, nullable=False, unique=True)
136 email = Column(String, nullable=False, unique=True)
137 # stored in libsodium hash format, can be null for email login
138 hashed_password = Column(Binary, nullable=False)
139 # phone number in E.164 format with leading +, for example "+46701740605"
140 phone = Column(String, nullable=True, server_default=text("NULL"))
141 # language preference -- defaults to empty string
142 ui_language_preference = Column(String, nullable=True, server_default="")
144 # timezones should always be UTC
145 ## location
146 # point describing their location. EPSG4326 is the SRS (spatial ref system, = way to describe a point on earth) used
147 # by GPS, it has the WGS84 geoid with lat/lon
148 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=False)
149 # randomized coordinates within a radius of 0.02-0.1 degrees, equates to about 2-10 km
150 randomized_geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
151 # their display location (displayed to other users), in meters
152 geom_radius = Column(Float, nullable=False)
153 # the display address (text) shown on their profile
154 city = Column(String, nullable=False)
155 # "Grew up in" on profile
156 hometown = Column(String, nullable=True)
158 regions_visited = relationship("Region", secondary="regions_visited", order_by="Region.name")
159 regions_lived = relationship("Region", secondary="regions_lived", order_by="Region.name")
161 timezone = column_property(
162 sa_select(TimezoneArea.tzid).where(func.ST_Contains(TimezoneArea.geom, geom)).limit(1).scalar_subquery(),
163 deferred=True,
164 )
166 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
167 last_active = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
169 public_visibility = Column(Enum(ProfilePublicVisibility), nullable=False, server_default="map_only")
170 has_modified_public_visibility = Column(Boolean, nullable=False, server_default=text("false"))
172 # id of the last message that they received a notification about
173 last_notified_message_id = Column(BigInteger, nullable=False, default=0)
174 # same as above for host requests
175 last_notified_request_message_id = Column(BigInteger, nullable=False, server_default=text("0"))
177 # display name
178 name = Column(String, nullable=False)
179 gender = Column(String, nullable=False)
180 pronouns = Column(String, nullable=True)
181 birthdate = Column(Date, nullable=False) # in the timezone of birthplace
183 avatar_key = Column(ForeignKey("uploads.key"), nullable=True)
185 hosting_status = Column(Enum(HostingStatus), nullable=False)
186 meetup_status = Column(Enum(MeetupStatus), nullable=False, server_default="open_to_meetup")
188 # community standing score
189 community_standing = Column(Float, nullable=True)
191 occupation = Column(String, nullable=True) # CommonMark without images
192 education = Column(String, nullable=True) # CommonMark without images
194 # "Who I am" under "About Me" tab
195 about_me = Column(String, nullable=True) # CommonMark without images
196 # "What I do in my free time" under "About Me" tab
197 things_i_like = Column(String, nullable=True) # CommonMark without images
198 # "About my home" under "My Home" tab
199 about_place = Column(String, nullable=True) # CommonMark without images
200 # "Additional information" under "About Me" tab
201 additional_information = Column(String, nullable=True) # CommonMark without images
203 is_banned = Column(Boolean, nullable=False, server_default=text("false"))
204 is_deleted = Column(Boolean, nullable=False, server_default=text("false"))
205 is_superuser = Column(Boolean, nullable=False, server_default=text("false"))
207 # the undelete token allows a user to recover their account for a couple of days after deletion in case it was
208 # accidental or they changed their mind
209 # constraints make sure these are non-null only if is_deleted and that these are null in unison
210 undelete_token = Column(String, nullable=True)
211 # validity of the undelete token
212 undelete_until = Column(DateTime(timezone=True), nullable=True)
214 # hosting preferences
215 max_guests = Column(Integer, nullable=True)
216 last_minute = Column(Boolean, nullable=True)
217 has_pets = Column(Boolean, nullable=True)
218 accepts_pets = Column(Boolean, nullable=True)
219 pet_details = Column(String, nullable=True) # CommonMark without images
220 has_kids = Column(Boolean, nullable=True)
221 accepts_kids = Column(Boolean, nullable=True)
222 kid_details = Column(String, nullable=True) # CommonMark without images
223 has_housemates = Column(Boolean, nullable=True)
224 housemate_details = Column(String, nullable=True) # CommonMark without images
225 wheelchair_accessible = Column(Boolean, nullable=True)
226 smoking_allowed = Column(Enum(SmokingLocation), nullable=True)
227 smokes_at_home = Column(Boolean, nullable=True)
228 drinking_allowed = Column(Boolean, nullable=True)
229 drinks_at_home = Column(Boolean, nullable=True)
230 # "Additional information" under "My Home" tab
231 other_host_info = Column(String, nullable=True) # CommonMark without images
233 # "Sleeping privacy" (not long-form text)
234 sleeping_arrangement = Column(Enum(SleepingArrangement), nullable=True)
235 # "Sleeping arrangement" under "My Home" tab
236 sleeping_details = Column(String, nullable=True) # CommonMark without images
237 # "Local area information" under "My Home" tab
238 area = Column(String, nullable=True) # CommonMark without images
239 # "House rules" under "My Home" tab
240 house_rules = Column(String, nullable=True) # CommonMark without images
241 parking = Column(Boolean, nullable=True)
242 parking_details = Column(Enum(ParkingDetails), nullable=True) # CommonMark without images
243 camping_ok = Column(Boolean, nullable=True)
245 accepted_tos = Column(Integer, nullable=False, default=0)
246 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")
247 # whether the user has yet filled in the contributor form
248 filled_contributor_form = Column(Boolean, nullable=False, server_default="false")
250 # number of onboarding emails sent
251 onboarding_emails_sent = Column(Integer, nullable=False, server_default="0")
252 last_onboarding_email_sent = Column(DateTime(timezone=True), nullable=True)
254 # whether we need to sync the user's newsletter preferences with the newsletter server
255 in_sync_with_newsletter = Column(Boolean, nullable=False, server_default="false")
256 # opted out of the newsletter
257 opt_out_of_newsletter = Column(Boolean, nullable=False, server_default="false")
259 # set to null to receive no digests
260 digest_frequency = Column(Interval, nullable=True)
261 last_digest_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)"))
263 # for changing their email
264 new_email = Column(String, nullable=True)
266 new_email_token = Column(String, nullable=True)
267 new_email_token_created = Column(DateTime(timezone=True), nullable=True)
268 new_email_token_expiry = Column(DateTime(timezone=True), nullable=True)
270 recommendation_score = Column(Float, nullable=False, server_default="0")
272 # Columns for verifying their phone number. State chart:
273 # ,-------------------,
274 # | Start |
275 # | phone = None | someone else
276 # ,-----------------, | token = None | verifies ,-----------------------,
277 # | Code Expired | | sent = 1970 or zz | phone xx | Verification Expired |
278 # | phone = xx | time passes | verified = None | <------, | phone = xx |
279 # | token = yy | <------------, | attempts = 0 | | | token = None |
280 # | sent = zz (exp.)| | '-------------------' | | sent = zz |
281 # | verified = None | | V ^ +-----------< | verified = ww (exp.) |
282 # | attempts = 0..2 | >--, | | | ChangePhone("") | | attempts = 0 |
283 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------'
284 # | | | | ChangePhone(xx) | ^ time passes
285 # | | ^ V | |
286 # ,-----------------, | | ,-------------------, | ,-----------------------,
287 # | Too Many | >--' '--< | Code sent | >------+ | Verified |
288 # | phone = xx | | phone = xx | | | phone = xx |
289 # | token = yy | VerifyPhone(wrong)| token = yy | '-----------< | token = None |
290 # | sent = zz | <------+--------< | sent = zz | | sent = zz |
291 # | verified = None | | | verified = None | VerifyPhone(correct) | verified = ww |
292 # | attempts = 3 | '--------> | attempts = 0..2 | >------------------> | attempts = 0 |
293 # '-----------------' '-------------------' '-----------------------'
295 # randomly generated Luhn 6-digit string
296 phone_verification_token = Column(String(6), nullable=True, server_default=text("NULL"))
298 phone_verification_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)"))
299 phone_verification_verified = Column(DateTime(timezone=True), nullable=True, server_default=text("NULL"))
300 phone_verification_attempts = Column(Integer, nullable=False, server_default=text("0"))
302 # the stripe customer identifier if the user has donated to Couchers
303 # e.g. cus_JjoXHttuZopv0t
304 # for new US entity
305 stripe_customer_id = Column(String, nullable=True)
306 # for old AU entity
307 stripe_customer_id_old = Column(String, nullable=True)
309 has_passport_sex_gender_exception = Column(Boolean, nullable=False, server_default=text("false"))
311 # checking for phone verification
312 has_donated = Column(Boolean, nullable=False, server_default=text("false"))
314 # whether this user has all emails turned off
315 do_not_email = Column(Boolean, nullable=False, server_default=text("false"))
317 avatar = relationship("Upload", foreign_keys="User.avatar_key")
319 admin_note = Column(String, nullable=False, server_default=text("''"))
321 # whether mods have marked this user has having to update their location
322 needs_to_update_location = Column(Boolean, nullable=False, server_default=text("false"))
324 age = column_property(func.date_part("year", func.age(birthdate)))
326 __table_args__ = (
327 # Verified phone numbers should be unique
328 Index(
329 "ix_users_unique_phone",
330 phone,
331 unique=True,
332 postgresql_where=phone_verification_verified != None,
333 ),
334 Index(
335 "ix_users_active",
336 id,
337 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
338 ),
339 # create index on users(geom, id, username) where not is_banned and not is_deleted and geom is not null;
340 Index(
341 "ix_users_geom_active",
342 geom,
343 id,
344 username,
345 postgresql_using="gist",
346 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
347 ),
348 Index(
349 "ix_users_by_id",
350 id,
351 postgresql_using="hash",
352 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
353 ),
354 Index(
355 "ix_users_by_username",
356 username,
357 postgresql_using="hash",
358 postgresql_where=and_(not_(is_banned), not_(is_deleted)),
359 ),
360 # There are two possible states for new_email_token, new_email_token_created, and new_email_token_expiry
361 CheckConstraint(
362 "(new_email_token IS NOT NULL AND new_email_token_created IS NOT NULL AND new_email_token_expiry IS NOT NULL) OR \
363 (new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL)",
364 name="check_new_email_token_state",
365 ),
366 # Whenever a phone number is set, it must either be pending verification or already verified.
367 # Exactly one of the following must always be true: not phone, token, verified.
368 CheckConstraint(
369 "(phone IS NULL)::int + (phone_verification_verified IS NOT NULL)::int + (phone_verification_token IS NOT NULL)::int = 1",
370 name="phone_verified_conditions",
371 ),
372 # Email must match our regex
373 CheckConstraint(
374 f"email ~ '{EMAIL_REGEX}'",
375 name="valid_email",
376 ),
377 # Undelete token + time are coupled: either both null or neither; and if they're not null then the account is deleted
378 CheckConstraint(
379 "((undelete_token IS NULL) = (undelete_until IS NULL)) AND ((undelete_token IS NULL) OR is_deleted)",
380 name="undelete_nullity",
381 ),
382 # If the user disabled all emails, then they can't host or meet up
383 CheckConstraint(
384 "(do_not_email IS FALSE) OR ((hosting_status = 'cant_host') AND (meetup_status = 'does_not_want_to_meetup'))",
385 name="do_not_email_inactive",
386 ),
387 )
389 @hybrid_property
390 def has_completed_profile(self):
391 return self.avatar_key is not None and self.about_me is not None and len(self.about_me) >= 150
393 @has_completed_profile.expression
394 def has_completed_profile(cls):
395 return (cls.avatar_key != None) & (func.character_length(cls.about_me) >= 150)
397 @hybrid_property
398 def jailed_missing_tos(self):
399 return self.accepted_tos < TOS_VERSION
401 @hybrid_property
402 def jailed_missing_community_guidelines(self):
403 return self.accepted_community_guidelines < GUIDELINES_VERSION
405 @hybrid_property
406 def jailed_pending_mod_notes(self):
407 return self.mod_notes.where(ModNote.is_pending).count() > 0
409 @hybrid_property
410 def jailed_pending_activeness_probe(self):
411 return self.pending_activeness_probe != None
413 @hybrid_property
414 def is_jailed(self):
415 return (
416 self.jailed_missing_tos
417 | self.jailed_missing_community_guidelines
418 | self.is_missing_location
419 | self.jailed_pending_mod_notes
420 | self.jailed_pending_activeness_probe
421 )
423 @hybrid_property
424 def is_missing_location(self):
425 return self.needs_to_update_location
427 @hybrid_property
428 def is_visible(self):
429 return ~(self.is_banned | self.is_deleted)
431 @property
432 def coordinates(self):
433 return get_coordinates(self.geom)
435 @property
436 def display_joined(self):
437 """
438 Returns the last active time rounded down to the nearest hour.
439 """
440 return self.joined.replace(minute=0, second=0, microsecond=0)
442 @property
443 def display_last_active(self):
444 """
445 Returns the last active time rounded down whatever is the "last active" coarsening.
446 """
447 return last_active_coarsen(self.last_active)
449 @hybrid_property
450 def phone_is_verified(self):
451 return (
452 self.phone_verification_verified is not None
453 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
454 )
456 @phone_is_verified.expression
457 def phone_is_verified(cls):
458 return (cls.phone_verification_verified != None) & (
459 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME
460 )
462 @hybrid_property
463 def phone_code_expired(self):
464 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME
466 def __repr__(self):
467 return f"User(id={self.id}, email={self.email}, username={self.username})"
470class UserBadge(Base):
471 """
472 A badge on a user's profile
473 """
475 __tablename__ = "user_badges"
476 __table_args__ = (UniqueConstraint("user_id", "badge_id"),)
478 id = Column(BigInteger, primary_key=True)
480 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
481 # corresponds to "id" in badges.json
482 badge_id = Column(String, nullable=False, index=True)
484 # take this with a grain of salt, someone may get then lose a badge for whatever reason
485 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
487 user = relationship("User", backref="badges")
490class ActivenessProbeStatus(enum.Enum):
491 # no response yet
492 pending = enum.auto()
494 # didn't respond on time
495 expired = enum.auto()
497 # responded that they're still active
498 still_active = enum.auto()
500 # responded that they're no longer active
501 no_longer_active = enum.auto()
504class ActivenessProbe(Base):
505 """
506 Activeness probes are used to gauge if users are still active: we send them a notification and ask them to respond,
507 we use this data both to help indicate response rate, as well as to make sure only those who are actively hosting
508 show up as such.
509 """
511 __tablename__ = "activeness_probes"
513 id = Column(BigInteger, primary_key=True)
515 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
516 # the time this probe was initiated
517 probe_initiated = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
518 # the number of reminders sent for this probe
519 notifications_sent = Column(Integer, nullable=False, server_default="0")
521 # the time of response
522 responded = Column(DateTime(timezone=True), nullable=True, default=None)
523 # the response value
524 response = Column(Enum(ActivenessProbeStatus), nullable=False, default=ActivenessProbeStatus.pending)
526 @hybrid_property
527 def is_pending(self):
528 return self.responded == None
530 user = relationship("User", back_populates="pending_activeness_probe")
532 __table_args__ = (
533 # a user can have at most one pending activeness probe at a time
534 Index(
535 "ix_activeness_probe_unique_pending_response",
536 user_id,
537 unique=True,
538 postgresql_where=responded == None,
539 ),
540 # response time is none iff response is pending
541 CheckConstraint(
542 "(responded IS NULL AND response = 'pending') OR (responded IS NOT NULL AND response != 'pending')",
543 name="pending_has_no_responded",
544 ),
545 )
548User.pending_activeness_probe = relationship(
549 ActivenessProbe,
550 primaryjoin="and_(ActivenessProbe.user_id == User.id, ActivenessProbe.is_pending)",
551 uselist=False,
552 back_populates="user",
553)
556class StrongVerificationAttemptStatus(enum.Enum):
557 ## full data states
558 # completed, this now provides verification for a user
559 succeeded = enum.auto()
561 ## no data states
562 # in progress: waiting for the user to scan the Iris code or open the app
563 in_progress_waiting_on_user_to_open_app = enum.auto()
564 # in progress: waiting for the user to scan MRZ or NFC/chip
565 in_progress_waiting_on_user_in_app = enum.auto()
566 # in progress, waiting for backend to pull verification data
567 in_progress_waiting_on_backend = enum.auto()
568 # failed, no data
569 failed = enum.auto()
571 # duplicate, at our end, has data
572 duplicate = enum.auto()
574 ## minimal data states
575 # the data, except minimal deduplication data, was deleted
576 deleted = enum.auto()
579class PassportSex(enum.Enum):
580 """
581 We don't care about sex, we use gender on the platform. But passports apparently do.
582 """
584 male = enum.auto()
585 female = enum.auto()
586 unspecified = enum.auto()
589class StrongVerificationAttempt(Base):
590 """
591 An attempt to perform strong verification
592 """
594 __tablename__ = "strong_verification_attempts"
596 # our verification id
597 id = Column(BigInteger, primary_key=True)
599 # this is returned in the callback, and we look up the attempt via this
600 verification_attempt_token = Column(String, nullable=False, unique=True)
602 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
603 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
605 status = Column(
606 Enum(StrongVerificationAttemptStatus),
607 nullable=False,
608 default=StrongVerificationAttemptStatus.in_progress_waiting_on_user_to_open_app,
609 )
611 ## full data
612 has_full_data = Column(Boolean, nullable=False, default=False)
613 # the data returned from iris, encrypted with a public key whose private key is kept offline
614 passport_encrypted_data = Column(Binary, nullable=True)
615 passport_date_of_birth = Column(Date, nullable=True)
616 passport_sex = Column(Enum(PassportSex), nullable=True)
618 ## minimal data: this will not be deleted
619 has_minimal_data = Column(Boolean, nullable=False, default=False)
620 passport_expiry_date = Column(Date, nullable=True)
621 passport_nationality = Column(String, nullable=True)
622 # last three characters of the passport number
623 passport_last_three_document_chars = Column(String, nullable=True)
625 iris_token = Column(String, nullable=False, unique=True)
626 iris_session_id = Column(BigInteger, nullable=False, unique=True)
628 passport_expiry_datetime = column_property(date_in_timezone(passport_expiry_date, "Etc/UTC"))
630 user = relationship("User")
632 @hybrid_property
633 def is_valid(self):
634 """
635 This only checks whether the attempt is a success and the passport is not expired, use `has_strong_verification` for full check
636 """
637 return (self.status == StrongVerificationAttemptStatus.succeeded) and (self.passport_expiry_datetime >= now())
639 @is_valid.expression
640 def is_valid(cls):
641 return (cls.status == StrongVerificationAttemptStatus.succeeded) & (
642 func.coalesce(cls.passport_expiry_datetime >= func.now(), False)
643 )
645 @hybrid_property
646 def is_visible(self):
647 return self.status != StrongVerificationAttemptStatus.deleted
649 @hybrid_method
650 def _raw_birthdate_match(self, user):
651 """Does not check whether the SV attempt itself is not expired"""
652 return self.passport_date_of_birth == user.birthdate
654 @hybrid_method
655 def matches_birthdate(self, user):
656 return self.is_valid & self._raw_birthdate_match(user)
658 @hybrid_method
659 def _raw_gender_match(self, user):
660 """Does not check whether the SV attempt itself is not expired"""
661 return (
662 ((user.gender == "Woman") & (self.passport_sex == PassportSex.female))
663 | ((user.gender == "Man") & (self.passport_sex == PassportSex.male))
664 | (self.passport_sex == PassportSex.unspecified)
665 | (user.has_passport_sex_gender_exception == True)
666 )
668 @hybrid_method
669 def matches_gender(self, user):
670 return self.is_valid & self._raw_gender_match(user)
672 @hybrid_method
673 def has_strong_verification(self, user):
674 return self.is_valid & self._raw_birthdate_match(user) & self._raw_gender_match(user)
676 __table_args__ = (
677 # used to look up verification status for a user
678 Index(
679 "ix_strong_verification_attempts_current",
680 user_id,
681 passport_expiry_date,
682 postgresql_where=status == StrongVerificationAttemptStatus.succeeded,
683 ),
684 # each passport can be verified only once
685 Index(
686 "ix_strong_verification_attempts_unique_succeeded",
687 passport_expiry_date,
688 passport_nationality,
689 passport_last_three_document_chars,
690 unique=True,
691 postgresql_where=(
692 (status == StrongVerificationAttemptStatus.succeeded)
693 | (status == StrongVerificationAttemptStatus.deleted)
694 ),
695 ),
696 # full data check
697 CheckConstraint(
698 "(has_full_data IS TRUE AND passport_encrypted_data IS NOT NULL AND passport_date_of_birth IS NOT NULL) OR \
699 (has_full_data IS FALSE AND passport_encrypted_data IS NULL AND passport_date_of_birth IS NULL)",
700 name="full_data_status",
701 ),
702 # minimal data check
703 CheckConstraint(
704 "(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 \
705 (has_minimal_data IS FALSE AND passport_expiry_date IS NULL AND passport_nationality IS NULL AND passport_last_three_document_chars IS NULL)",
706 name="minimal_data_status",
707 ),
708 # note on implications: p => q iff ~p OR q
709 # full data implies minimal data, has_minimal_data => has_full_data
710 CheckConstraint(
711 "(has_full_data IS FALSE) OR (has_minimal_data IS TRUE)",
712 name="full_data_implies_minimal_data",
713 ),
714 # succeeded implies full data
715 CheckConstraint(
716 "(NOT (status = 'succeeded')) OR (has_full_data IS TRUE)",
717 name="succeeded_implies_full_data",
718 ),
719 # in_progress/failed implies no_data
720 CheckConstraint(
721 "(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)",
722 name="in_progress_failed_iris_implies_no_data",
723 ),
724 # deleted or duplicate implies minimal data
725 CheckConstraint(
726 "(NOT ((status = 'deleted') OR (status = 'duplicate'))) OR (has_minimal_data IS TRUE)",
727 name="deleted_duplicate_implies_minimal_data",
728 ),
729 )
732class ModNote(Base):
733 """
734 A moderator note to a user. This could be a warning, just a note "hey, we did X", or any other similar message.
736 The user has to read and "acknowledge" the note before continuing onto the platform, and being un-jailed.
737 """
739 __tablename__ = "mod_notes"
740 id = Column(BigInteger, primary_key=True)
742 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
744 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
745 acknowledged = Column(DateTime(timezone=True), nullable=True)
747 # this is an internal ID to allow the mods to track different types of notes
748 internal_id = Column(String, nullable=False)
749 # the admin that left this note
750 creator_user_id = Column(ForeignKey("users.id"), nullable=False)
752 note_content = Column(String, nullable=False) # CommonMark without images
754 user = relationship("User", backref=backref("mod_notes", lazy="dynamic"), foreign_keys="ModNote.user_id")
756 def __repr__(self):
757 return f"ModeNote(id={self.id}, user={self.user}, created={self.created}, ack'd={self.acknowledged})"
759 @hybrid_property
760 def is_pending(self):
761 return self.acknowledged == None
763 __table_args__ = (
764 # used to look up pending notes
765 Index(
766 "ix_mod_notes_unacknowledged",
767 user_id,
768 postgresql_where=acknowledged == None,
769 ),
770 )
773class StrongVerificationCallbackEvent(Base):
774 __tablename__ = "strong_verification_callback_events"
776 id = Column(BigInteger, primary_key=True)
777 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
779 verification_attempt_id = Column(ForeignKey("strong_verification_attempts.id"), nullable=False, index=True)
781 iris_status = Column(String, nullable=False)
784class DonationType(enum.Enum):
785 one_time = enum.auto()
786 recurring = enum.auto()
789class DonationInitiation(Base):
790 """
791 Whenever someone initiaties a donation through the platform
792 """
794 __tablename__ = "donation_initiations"
795 id = Column(BigInteger, primary_key=True)
797 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
798 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
800 amount = Column(Integer, nullable=False)
801 stripe_checkout_session_id = Column(String, nullable=False)
803 donation_type = Column(Enum(DonationType), nullable=False)
805 user = relationship("User", backref="donation_initiations")
808class Invoice(Base):
809 """
810 Successful donations, both one off and recurring
812 Triggered by `payment_intent.succeeded` webhook
813 """
815 __tablename__ = "invoices"
817 id = Column(BigInteger, primary_key=True)
818 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
819 user_id = Column(ForeignKey("users.id"), nullable=False)
821 amount = Column(Float, nullable=False)
823 stripe_payment_intent_id = Column(String, nullable=False, unique=True)
824 stripe_receipt_url = Column(String, nullable=False)
826 user = relationship("User", backref="invoices")
829class LanguageFluency(enum.Enum):
830 # note that the numbering is important here, these are ordinal
831 beginner = 1
832 conversational = 2
833 fluent = 3
836class LanguageAbility(Base):
837 __tablename__ = "language_abilities"
838 __table_args__ = (
839 # Users can only have one language ability per language
840 UniqueConstraint("user_id", "language_code"),
841 )
843 id = Column(BigInteger, primary_key=True)
844 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
845 language_code = Column(ForeignKey("languages.code", deferrable=True), nullable=False)
846 fluency = Column(Enum(LanguageFluency), nullable=False)
848 user = relationship("User", backref="language_abilities")
849 language = relationship("Language")
852class RegionVisited(Base):
853 __tablename__ = "regions_visited"
854 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
856 id = Column(BigInteger, primary_key=True)
857 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
858 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
861class RegionLived(Base):
862 __tablename__ = "regions_lived"
863 __table_args__ = (UniqueConstraint("user_id", "region_code"),)
865 id = Column(BigInteger, primary_key=True)
866 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
867 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)
870class FriendStatus(enum.Enum):
871 pending = enum.auto()
872 accepted = enum.auto()
873 rejected = enum.auto()
874 cancelled = enum.auto()
877class FriendRelationship(Base):
878 """
879 Friendship relations between users
881 TODO: make this better with sqlalchemy self-referential stuff
882 TODO: constraint on only one row per user pair where accepted or pending
883 """
885 __tablename__ = "friend_relationships"
887 id = Column(BigInteger, primary_key=True)
889 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
890 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
892 status = Column(Enum(FriendStatus), nullable=False, default=FriendStatus.pending)
894 # timezones should always be UTC
895 time_sent = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
896 time_responded = Column(DateTime(timezone=True), nullable=True)
898 from_user = relationship("User", backref="friends_from", foreign_keys="FriendRelationship.from_user_id")
899 to_user = relationship("User", backref="friends_to", foreign_keys="FriendRelationship.to_user_id")
901 __table_args__ = (
902 # Ping looks up pending friend reqs, this speeds that up
903 Index(
904 "ix_friend_relationships_status_to_from",
905 status,
906 to_user_id,
907 from_user_id,
908 ),
909 )
912class ContributeOption(enum.Enum):
913 yes = enum.auto()
914 maybe = enum.auto()
915 no = enum.auto()
918class ContributorForm(Base):
919 """
920 Someone filled in the contributor form
921 """
923 __tablename__ = "contributor_forms"
925 id = Column(BigInteger, primary_key=True)
927 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
928 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
930 ideas = Column(String, nullable=True)
931 features = Column(String, nullable=True)
932 experience = Column(String, nullable=True)
933 contribute = Column(Enum(ContributeOption), nullable=True)
934 contribute_ways = Column(ARRAY(String), nullable=False)
935 expertise = Column(String, nullable=True)
937 user = relationship("User", backref="contributor_forms")
939 @hybrid_property
940 def is_filled(self):
941 """
942 Whether the form counts as having been filled
943 """
944 return (
945 (self.ideas != None)
946 | (self.features != None)
947 | (self.experience != None)
948 | (self.contribute != None)
949 | (self.contribute_ways != [])
950 | (self.expertise != None)
951 )
953 @property
954 def should_notify(self):
955 """
956 If this evaluates to true, we send an email to the recruitment team.
958 We currently send if expertise is listed, or if they list a way to help outside of a set list
959 """
960 return False
963class SignupFlow(Base):
964 """
965 Signup flows/incomplete users
967 Coinciding fields have the same meaning as in User
968 """
970 __tablename__ = "signup_flows"
972 id = Column(BigInteger, primary_key=True)
974 # housekeeping
975 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
976 flow_token = Column(String, nullable=False, unique=True)
977 email_verified = Column(Boolean, nullable=False, default=False)
978 email_sent = Column(Boolean, nullable=False, default=False)
979 email_token = Column(String, nullable=True)
980 email_token_expiry = Column(DateTime(timezone=True), nullable=True)
982 ## Basic
983 name = Column(String, nullable=False)
984 # TODO: unique across both tables
985 email = Column(String, nullable=False, unique=True)
986 # TODO: invitation, attribution
988 ## Account
989 # TODO: unique across both tables
990 username = Column(String, nullable=True, unique=True)
991 hashed_password = Column(Binary, nullable=True)
992 birthdate = Column(Date, nullable=True) # in the timezone of birthplace
993 gender = Column(String, nullable=True)
994 hosting_status = Column(Enum(HostingStatus), nullable=True)
995 city = Column(String, nullable=True)
996 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
997 geom_radius = Column(Float, nullable=True)
999 accepted_tos = Column(Integer, nullable=True)
1000 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")
1002 opt_out_of_newsletter = Column(Boolean, nullable=True)
1004 ## Feedback (now unused)
1005 filled_feedback = Column(Boolean, nullable=False, default=False)
1006 ideas = Column(String, nullable=True)
1007 features = Column(String, nullable=True)
1008 experience = Column(String, nullable=True)
1009 contribute = Column(Enum(ContributeOption), nullable=True)
1010 contribute_ways = Column(ARRAY(String), nullable=True)
1011 expertise = Column(String, nullable=True)
1013 @hybrid_property
1014 def token_is_valid(self):
1015 return (self.email_token != None) & (self.email_token_expiry >= now())
1017 @hybrid_property
1018 def account_is_filled(self):
1019 return (
1020 (self.username != None)
1021 & (self.birthdate != None)
1022 & (self.gender != None)
1023 & (self.hosting_status != None)
1024 & (self.city != None)
1025 & (self.geom != None)
1026 & (self.geom_radius != None)
1027 & (self.accepted_tos != None)
1028 & (self.opt_out_of_newsletter != None)
1029 )
1031 @hybrid_property
1032 def is_completed(self):
1033 return self.email_verified & self.account_is_filled & (self.accepted_community_guidelines == GUIDELINES_VERSION)
1036class LoginToken(Base):
1037 """
1038 A login token sent in an email to a user, allows them to sign in between the times defined by created and expiry
1039 """
1041 __tablename__ = "login_tokens"
1042 token = Column(String, primary_key=True)
1044 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1046 # timezones should always be UTC
1047 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1048 expiry = Column(DateTime(timezone=True), nullable=False)
1050 user = relationship("User", backref="login_tokens")
1052 @hybrid_property
1053 def is_valid(self):
1054 return (self.created <= now()) & (self.expiry >= now())
1056 def __repr__(self):
1057 return f"LoginToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
1060class PasswordResetToken(Base):
1061 __tablename__ = "password_reset_tokens"
1062 token = Column(String, primary_key=True)
1064 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1066 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1067 expiry = Column(DateTime(timezone=True), nullable=False)
1069 user = relationship("User", backref="password_reset_tokens")
1071 @hybrid_property
1072 def is_valid(self):
1073 return (self.created <= now()) & (self.expiry >= now())
1075 def __repr__(self):
1076 return f"PasswordResetToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"
1079class AccountDeletionToken(Base):
1080 __tablename__ = "account_deletion_tokens"
1082 token = Column(String, primary_key=True)
1084 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1086 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1087 expiry = Column(DateTime(timezone=True), nullable=False)
1089 user = relationship("User", backref="account_deletion_tokens")
1091 @hybrid_property
1092 def is_valid(self):
1093 return (self.created <= now()) & (self.expiry >= now())
1095 def __repr__(self):
1096 return f"AccountDeletionToken(token={self.token}, user_id={self.user_id}, created={self.created}, expiry={self.expiry})"
1099class UserActivity(Base):
1100 """
1101 User activity: for each unique (user_id, period, ip_address, user_agent) tuple, keep track of number of api calls
1103 Used for user "last active" as well as admin stuff
1104 """
1106 __tablename__ = "user_activity"
1108 id = Column(BigInteger, primary_key=True)
1110 user_id = Column(ForeignKey("users.id"), nullable=False)
1111 # the start of a period of time, e.g. 1 hour during which we bin activeness
1112 period = Column(DateTime(timezone=True), nullable=False)
1114 # details of the browser, if available
1115 ip_address = Column(INET, nullable=True)
1116 user_agent = Column(String, nullable=True)
1118 # count of api calls made with this ip, user_agent, and period
1119 api_calls = Column(Integer, nullable=False, default=0)
1121 __table_args__ = (
1122 # helps look up this tuple quickly
1123 Index(
1124 "ix_user_activity_user_id_period_ip_address_user_agent",
1125 user_id,
1126 period,
1127 ip_address,
1128 user_agent,
1129 unique=True,
1130 ),
1131 )
1134class UserSession(Base):
1135 """
1136 API keys/session cookies for the app
1138 There are two types of sessions: long-lived, and short-lived. Long-lived are
1139 like when you choose "remember this browser": they will be valid for a long
1140 time without the user interacting with the site. Short-lived sessions on the
1141 other hand get invalidated quickly if the user does not interact with the
1142 site.
1144 Long-lived tokens are valid from `created` until `expiry`.
1146 Short-lived tokens expire after 168 hours (7 days) if they are not used.
1147 """
1149 __tablename__ = "sessions"
1150 token = Column(String, primary_key=True)
1152 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1154 # sessions are either "api keys" or "session cookies", otherwise identical
1155 # an api key is put in the authorization header (e.g. "authorization: Bearer <token>")
1156 # a session cookie is set in the "couchers-sesh" cookie (e.g. "cookie: couchers-sesh=<token>")
1157 # when a session is created, it's fixed as one or the other for security reasons
1158 # for api keys to be useful, they should be long lived and have a long expiry
1159 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))
1161 # whether it's a long-lived or short-lived session
1162 long_lived = Column(Boolean, nullable=False)
1164 # the time at which the session was created
1165 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1167 # the expiry of the session: the session *cannot* be refreshed past this
1168 expiry = Column(DateTime(timezone=True), nullable=False, server_default=func.now() + text("interval '730 days'"))
1170 # the time at which the token was invalidated, allows users to delete sessions
1171 deleted = Column(DateTime(timezone=True), nullable=True, default=None)
1173 # the last time this session was used
1174 last_seen = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1176 # count of api calls made with this token/session (if we're updating last_seen, might as well update this too)
1177 api_calls = Column(Integer, nullable=False, default=0)
1179 # details of the browser, if available
1180 # these are from the request creating the session, not used for anything else
1181 ip_address = Column(String, nullable=True)
1182 user_agent = Column(String, nullable=True)
1184 user = relationship("User", backref="sessions")
1186 @hybrid_property
1187 def is_valid(self):
1188 """
1189 It must have been created and not be expired or deleted.
1191 Also, if it's a short lived token, it must have been used in the last 168 hours.
1193 TODO: this probably won't run in python (instance level), only in sql (class level)
1194 """
1195 return (
1196 (self.created <= func.now())
1197 & (self.expiry >= func.now())
1198 & (self.deleted == None)
1199 & (self.long_lived | (func.now() - self.last_seen < text("interval '168 hours'")))
1200 )
1202 __table_args__ = (
1203 Index(
1204 "ix_sessions_by_token",
1205 "token",
1206 postgresql_using="hash",
1207 ),
1208 )
1211class Conversation(Base):
1212 """
1213 Conversation brings together the different types of message/conversation types
1214 """
1216 __tablename__ = "conversations"
1218 id = Column(BigInteger, primary_key=True)
1219 # timezone should always be UTC
1220 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1222 def __repr__(self):
1223 return f"Conversation(id={self.id}, created={self.created})"
1226class GroupChat(Base):
1227 """
1228 Group chat
1229 """
1231 __tablename__ = "group_chats"
1233 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1235 title = Column(String, nullable=True)
1236 only_admins_invite = Column(Boolean, nullable=False, default=True)
1237 creator_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1238 is_dm = Column(Boolean, nullable=False)
1240 conversation = relationship("Conversation", backref="group_chat")
1241 creator = relationship("User", backref="created_group_chats")
1243 def __repr__(self):
1244 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})"
1247class GroupChatRole(enum.Enum):
1248 admin = enum.auto()
1249 participant = enum.auto()
1252class GroupChatSubscription(Base):
1253 """
1254 The recipient of a thread and information about when they joined/left/etc.
1255 """
1257 __tablename__ = "group_chat_subscriptions"
1258 id = Column(BigInteger, primary_key=True)
1260 # TODO: DB constraint on only one user+group_chat combo at a given time
1261 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1262 group_chat_id = Column(ForeignKey("group_chats.id"), nullable=False, index=True)
1264 # timezones should always be UTC
1265 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1266 left = Column(DateTime(timezone=True), nullable=True)
1268 role = Column(Enum(GroupChatRole), nullable=False)
1270 last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1272 # when this chat is muted until, DATETIME_INFINITY for "forever"
1273 muted_until = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_MINUS_INFINITY.isoformat())
1275 user = relationship("User", backref="group_chat_subscriptions")
1276 group_chat = relationship("GroupChat", backref=backref("subscriptions", lazy="dynamic"))
1278 def muted_display(self):
1279 """
1280 Returns (muted, muted_until) display values:
1281 1. If not muted, returns (False, None)
1282 2. If muted forever, returns (True, None)
1283 3. If muted until a given datetime returns (True, dt)
1284 """
1285 if self.muted_until < now():
1286 return (False, None)
1287 elif self.muted_until == DATETIME_INFINITY:
1288 return (True, None)
1289 else:
1290 return (True, self.muted_until)
1292 @hybrid_property
1293 def is_muted(self):
1294 return self.muted_until > func.now()
1296 def __repr__(self):
1297 return f"GroupChatSubscription(id={self.id}, user={self.user}, joined={self.joined}, left={self.left}, role={self.role}, group_chat={self.group_chat})"
1300class MessageType(enum.Enum):
1301 text = enum.auto()
1302 # e.g.
1303 # image =
1304 # emoji =
1305 # ...
1306 chat_created = enum.auto()
1307 chat_edited = enum.auto()
1308 user_invited = enum.auto()
1309 user_left = enum.auto()
1310 user_made_admin = enum.auto()
1311 user_removed_admin = enum.auto() # RemoveGroupChatAdmin: remove admin permission from a user in group chat
1312 host_request_status_changed = enum.auto()
1313 user_removed = enum.auto() # user is removed from group chat by amdin RemoveGroupChatUser
1316class HostRequestStatus(enum.Enum):
1317 pending = enum.auto()
1318 accepted = enum.auto()
1319 rejected = enum.auto()
1320 confirmed = enum.auto()
1321 cancelled = enum.auto()
1324class Message(Base):
1325 """
1326 A message.
1328 If message_type = text, then the message is a normal text message, otherwise, it's a special control message.
1329 """
1331 __tablename__ = "messages"
1333 id = Column(BigInteger, primary_key=True)
1335 # which conversation the message belongs in
1336 conversation_id = Column(ForeignKey("conversations.id"), nullable=False, index=True)
1338 # the user that sent the message/command
1339 author_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1341 # the message type, "text" is a text message, otherwise a "control message"
1342 message_type = Column(Enum(MessageType), nullable=False)
1344 # the target if a control message and requires target, e.g. if inviting a user, the user invited is the target
1345 target_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1347 # time sent, timezone should always be UTC
1348 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1350 # the plain-text message text if not control
1351 text = Column(String, nullable=True)
1353 # the new host request status if the message type is host_request_status_changed
1354 host_request_status_target = Column(Enum(HostRequestStatus), nullable=True)
1356 conversation = relationship("Conversation", backref="messages", order_by="Message.time.desc()")
1357 author = relationship("User", foreign_keys="Message.author_id")
1358 target = relationship("User", foreign_keys="Message.target_id")
1360 @property
1361 def is_normal_message(self):
1362 """
1363 There's only one normal type atm, text
1364 """
1365 return self.message_type == MessageType.text
1367 def __repr__(self):
1368 return f"Message(id={self.id}, time={self.time}, text={self.text}, author={self.author}, conversation={self.conversation})"
1371class ContentReport(Base):
1372 """
1373 A piece of content reported to admins
1374 """
1376 __tablename__ = "content_reports"
1378 id = Column(BigInteger, primary_key=True)
1380 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1382 # the user who reported or flagged the content
1383 reporting_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1385 # reason, e.g. spam, inappropriate, etc
1386 reason = Column(String, nullable=False)
1387 # a short description
1388 description = Column(String, nullable=False)
1390 # a reference to the content, see //docs/content_ref.md
1391 content_ref = Column(String, nullable=False)
1392 # the author of the content (e.g. the user who wrote the comment itself)
1393 author_user_id = Column(ForeignKey("users.id"), nullable=False)
1395 # details of the browser, if available
1396 user_agent = Column(String, nullable=False)
1397 # the URL the user was on when reporting the content
1398 page = Column(String, nullable=False)
1400 # see comments above for reporting vs author
1401 reporting_user = relationship("User", foreign_keys="ContentReport.reporting_user_id")
1402 author_user = relationship("User", foreign_keys="ContentReport.author_user_id")
1405class Email(Base):
1406 """
1407 Table of all dispatched emails for debugging purposes, etc.
1408 """
1410 __tablename__ = "emails"
1412 id = Column(String, primary_key=True)
1414 # timezone should always be UTC
1415 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1417 sender_name = Column(String, nullable=False)
1418 sender_email = Column(String, nullable=False)
1420 recipient = Column(String, nullable=False)
1421 subject = Column(String, nullable=False)
1423 plain = Column(String, nullable=False)
1424 html = Column(String, nullable=False)
1426 list_unsubscribe_header = Column(String, nullable=True)
1427 source_data = Column(String, nullable=True)
1430class SMS(Base):
1431 """
1432 Table of all sent SMSs for debugging purposes, etc.
1433 """
1435 __tablename__ = "smss"
1437 id = Column(BigInteger, primary_key=True)
1439 # timezone should always be UTC
1440 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1441 # AWS message id
1442 message_id = Column(String, nullable=False)
1444 # the SMS sender ID sent to AWS, name that the SMS appears to come from
1445 sms_sender_id = Column(String, nullable=False)
1446 number = Column(String, nullable=False)
1447 message = Column(String, nullable=False)
1450class HostRequest(Base):
1451 """
1452 A request to stay with a host
1453 """
1455 __tablename__ = "host_requests"
1457 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)
1458 surfer_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1459 host_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1461 # TODO: proper timezone handling
1462 timezone = "Etc/UTC"
1464 # dates in the timezone above
1465 from_date = Column(Date, nullable=False)
1466 to_date = Column(Date, nullable=False)
1468 # timezone aware start and end times of the request, can be compared to now()
1469 start_time = column_property(date_in_timezone(from_date, timezone))
1470 end_time = column_property(date_in_timezone(to_date, timezone) + text("interval '1 days'"))
1471 start_time_to_write_reference = column_property(date_in_timezone(to_date, timezone))
1472 # notice 1 day for midnight at the *end of the day*, then 14 days to write a ref
1473 end_time_to_write_reference = column_property(date_in_timezone(to_date, timezone) + text("interval '15 days'"))
1475 status = Column(Enum(HostRequestStatus), nullable=False)
1477 host_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1478 surfer_last_seen_message_id = Column(BigInteger, nullable=False, default=0)
1480 # number of reference reminders sent out
1481 host_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1482 surfer_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))
1484 # reason why the host/surfer marked that they didn't meet up
1485 # if null then they haven't marked it such
1486 host_reason_didnt_meetup = Column(String, nullable=True)
1487 surfer_reason_didnt_meetup = Column(String, nullable=True)
1489 surfer = relationship("User", backref="host_requests_sent", foreign_keys="HostRequest.surfer_user_id")
1490 host = relationship("User", backref="host_requests_received", foreign_keys="HostRequest.host_user_id")
1491 conversation = relationship("Conversation")
1493 __table_args__ = (
1494 # allows fast lookup as to whether they didn't meet up
1495 Index(
1496 "ix_host_requests_host_didnt_meetup",
1497 host_reason_didnt_meetup != None,
1498 ),
1499 Index(
1500 "ix_host_requests_surfer_didnt_meetup",
1501 surfer_reason_didnt_meetup != None,
1502 ),
1503 )
1505 @hybrid_property
1506 def can_write_reference(self):
1507 return (
1508 ((self.status == HostRequestStatus.confirmed) | (self.status == HostRequestStatus.accepted))
1509 & (now() >= self.start_time_to_write_reference)
1510 & (now() <= self.end_time_to_write_reference)
1511 )
1513 @can_write_reference.expression
1514 def can_write_reference(cls):
1515 return (
1516 ((cls.status == HostRequestStatus.confirmed) | (cls.status == HostRequestStatus.accepted))
1517 & (func.now() >= cls.start_time_to_write_reference)
1518 & (func.now() <= cls.end_time_to_write_reference)
1519 )
1521 def __repr__(self):
1522 return f"HostRequest(id={self.conversation_id}, surfer_user_id={self.surfer_user_id}, host_user_id={self.host_user_id}...)"
1525class ReferenceType(enum.Enum):
1526 friend = enum.auto()
1527 surfed = enum.auto() # The "from" user surfed with the "to" user
1528 hosted = enum.auto() # The "from" user hosted the "to" user
1531class Reference(Base):
1532 """
1533 Reference from one user to another
1534 """
1536 __tablename__ = "references"
1538 id = Column(BigInteger, primary_key=True)
1539 # timezone should always be UTC
1540 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1542 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1543 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1545 reference_type = Column(Enum(ReferenceType), nullable=False)
1547 host_request_id = Column(ForeignKey("host_requests.id"), nullable=True)
1549 text = Column(String, nullable=False) # plain text
1550 # text that's only visible to mods
1551 private_text = Column(String, nullable=True) # plain text
1553 rating = Column(Float, nullable=False)
1554 was_appropriate = Column(Boolean, nullable=False)
1556 is_deleted = Column(Boolean, nullable=False, default=False, server_default="false")
1558 from_user = relationship("User", backref="references_from", foreign_keys="Reference.from_user_id")
1559 to_user = relationship("User", backref="references_to", foreign_keys="Reference.to_user_id")
1561 host_request = relationship("HostRequest", backref="references")
1563 __table_args__ = (
1564 # Rating must be between 0 and 1, inclusive
1565 CheckConstraint(
1566 "rating BETWEEN 0 AND 1",
1567 name="rating_between_0_and_1",
1568 ),
1569 # Has host_request_id or it's a friend reference
1570 CheckConstraint(
1571 "(host_request_id IS NOT NULL) <> (reference_type = 'friend')",
1572 name="host_request_id_xor_friend_reference",
1573 ),
1574 # Each user can leave at most one friend reference to another user
1575 Index(
1576 "ix_references_unique_friend_reference",
1577 from_user_id,
1578 to_user_id,
1579 reference_type,
1580 unique=True,
1581 postgresql_where=(reference_type == ReferenceType.friend),
1582 ),
1583 # Each user can leave at most one reference to another user for each stay
1584 Index(
1585 "ix_references_unique_per_host_request",
1586 from_user_id,
1587 to_user_id,
1588 host_request_id,
1589 unique=True,
1590 postgresql_where=(host_request_id != None),
1591 ),
1592 )
1594 @property
1595 def should_report(self):
1596 """
1597 If this evaluates to true, we send a report to the moderation team.
1598 """
1599 return self.rating <= 0.4 or not self.was_appropriate or self.private_text
1602class InitiatedUpload(Base):
1603 """
1604 Started downloads, not necessarily complete yet.
1605 """
1607 __tablename__ = "initiated_uploads"
1609 key = Column(String, primary_key=True)
1611 # timezones should always be UTC
1612 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1613 expiry = Column(DateTime(timezone=True), nullable=False)
1615 initiator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1617 initiator_user = relationship("User")
1619 @hybrid_property
1620 def is_valid(self):
1621 return (self.created <= func.now()) & (self.expiry >= func.now())
1624class Upload(Base):
1625 """
1626 Completed uploads.
1627 """
1629 __tablename__ = "uploads"
1630 key = Column(String, primary_key=True)
1632 filename = Column(String, nullable=False)
1633 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1634 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1636 # photo credit, etc
1637 credit = Column(String, nullable=True)
1639 creator_user = relationship("User", backref="uploads", foreign_keys="Upload.creator_user_id")
1641 def _url(self, size):
1642 return urls.media_url(filename=self.filename, size=size)
1644 @property
1645 def thumbnail_url(self):
1646 return self._url("thumbnail")
1648 @property
1649 def full_url(self):
1650 return self._url("full")
1653communities_seq = Sequence("communities_seq")
1656class Node(Base):
1657 """
1658 Node, i.e. geographical subdivision of the world
1660 Administered by the official cluster
1661 """
1663 __tablename__ = "nodes"
1665 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1667 # name and description come from official cluster
1668 parent_node_id = Column(ForeignKey("nodes.id"), nullable=True, index=True)
1669 geom = deferred(Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False))
1670 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1672 parent_node = relationship("Node", backref="child_nodes", remote_side="Node.id")
1674 contained_users = relationship(
1675 "User",
1676 primaryjoin="func.ST_Contains(foreign(Node.geom), User.geom).as_comparison(1, 2)",
1677 viewonly=True,
1678 uselist=True,
1679 )
1681 contained_user_ids = association_proxy("contained_users", "id")
1684class Cluster(Base):
1685 """
1686 Cluster, administered grouping of content
1687 """
1689 __tablename__ = "clusters"
1691 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1692 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1693 name = Column(String, nullable=False)
1694 # short description
1695 description = Column(String, nullable=False)
1696 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1698 is_official_cluster = Column(Boolean, nullable=False, default=False)
1700 slug = column_property(func.slugify(name))
1702 official_cluster_for_node = relationship(
1703 "Node",
1704 primaryjoin="and_(Cluster.parent_node_id == Node.id, Cluster.is_official_cluster)",
1705 backref=backref("official_cluster", uselist=False),
1706 uselist=False,
1707 viewonly=True,
1708 )
1710 parent_node = relationship(
1711 "Node", backref="child_clusters", remote_side="Node.id", foreign_keys="Cluster.parent_node_id"
1712 )
1714 nodes = relationship("Cluster", backref="clusters", secondary="node_cluster_associations", viewonly=True)
1715 # all pages
1716 pages = relationship(
1717 "Page", backref="clusters", secondary="cluster_page_associations", lazy="dynamic", viewonly=True
1718 )
1719 events = relationship("Event", backref="clusters", secondary="cluster_event_associations", viewonly=True)
1720 discussions = relationship(
1721 "Discussion", backref="clusters", secondary="cluster_discussion_associations", viewonly=True
1722 )
1724 # includes also admins
1725 members = relationship(
1726 "User",
1727 lazy="dynamic",
1728 backref="cluster_memberships",
1729 secondary="cluster_subscriptions",
1730 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1731 secondaryjoin="User.id == ClusterSubscription.user_id",
1732 viewonly=True,
1733 )
1735 admins = relationship(
1736 "User",
1737 lazy="dynamic",
1738 backref="cluster_adminships",
1739 secondary="cluster_subscriptions",
1740 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",
1741 secondaryjoin="and_(User.id == ClusterSubscription.user_id, ClusterSubscription.role == 'admin')",
1742 viewonly=True,
1743 )
1745 main_page = relationship(
1746 "Page",
1747 primaryjoin="and_(Cluster.id == Page.owner_cluster_id, Page.type == 'main_page')",
1748 viewonly=True,
1749 uselist=False,
1750 )
1752 @property
1753 def is_leaf(self) -> bool:
1754 """Whether the cluster is a leaf node in the cluster hierarchy."""
1755 return len(self.parent_node.child_nodes) == 0
1757 __table_args__ = (
1758 # Each node can have at most one official cluster
1759 Index(
1760 "ix_clusters_owner_parent_node_id_is_official_cluster",
1761 parent_node_id,
1762 is_official_cluster,
1763 unique=True,
1764 postgresql_where=is_official_cluster,
1765 ),
1766 )
1769class NodeClusterAssociation(Base):
1770 """
1771 NodeClusterAssociation, grouping of nodes
1772 """
1774 __tablename__ = "node_cluster_associations"
1775 __table_args__ = (UniqueConstraint("node_id", "cluster_id"),)
1777 id = Column(BigInteger, primary_key=True)
1779 node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1780 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1782 node = relationship("Node", backref="node_cluster_associations")
1783 cluster = relationship("Cluster", backref="node_cluster_associations")
1786class ClusterRole(enum.Enum):
1787 member = enum.auto()
1788 admin = enum.auto()
1791class ClusterSubscription(Base):
1792 """
1793 ClusterSubscription of a user
1794 """
1796 __tablename__ = "cluster_subscriptions"
1798 id = Column(BigInteger, primary_key=True)
1800 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1801 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1802 role = Column(Enum(ClusterRole), nullable=False)
1804 user = relationship("User", backref="cluster_subscriptions")
1805 cluster = relationship("Cluster", backref="cluster_subscriptions")
1807 __table_args__ = (
1808 UniqueConstraint("user_id", "cluster_id"),
1809 Index(
1810 "ix_cluster_subscriptions_members",
1811 cluster_id,
1812 user_id,
1813 ),
1814 # For fast lookup of nodes this user is an admin of
1815 Index(
1816 "ix_cluster_subscriptions_admins",
1817 user_id,
1818 cluster_id,
1819 postgresql_where=(role == ClusterRole.admin),
1820 ),
1821 )
1824class ClusterPageAssociation(Base):
1825 """
1826 pages related to clusters
1827 """
1829 __tablename__ = "cluster_page_associations"
1830 __table_args__ = (UniqueConstraint("page_id", "cluster_id"),)
1832 id = Column(BigInteger, primary_key=True)
1834 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1835 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1837 page = relationship("Page", backref="cluster_page_associations")
1838 cluster = relationship("Cluster", backref="cluster_page_associations")
1841class PageType(enum.Enum):
1842 main_page = enum.auto()
1843 place = enum.auto()
1844 guide = enum.auto()
1847class Page(Base):
1848 """
1849 similar to a wiki page about a community, POI or guide
1850 """
1852 __tablename__ = "pages"
1854 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1856 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1857 type = Column(Enum(PageType), nullable=False)
1858 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1859 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1860 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1862 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
1864 parent_node = relationship("Node", backref="child_pages", remote_side="Node.id", foreign_keys="Page.parent_node_id")
1866 thread = relationship("Thread", backref="page", uselist=False)
1867 creator_user = relationship("User", backref="created_pages", foreign_keys="Page.creator_user_id")
1868 owner_user = relationship("User", backref="owned_pages", foreign_keys="Page.owner_user_id")
1869 owner_cluster = relationship(
1870 "Cluster", backref=backref("owned_pages", lazy="dynamic"), uselist=False, foreign_keys="Page.owner_cluster_id"
1871 )
1873 editors = relationship("User", secondary="page_versions", viewonly=True)
1875 __table_args__ = (
1876 # Only one of owner_user and owner_cluster should be set
1877 CheckConstraint(
1878 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
1879 name="one_owner",
1880 ),
1881 # Only clusters can own main pages
1882 CheckConstraint(
1883 "NOT (owner_cluster_id IS NULL AND type = 'main_page')",
1884 name="main_page_owned_by_cluster",
1885 ),
1886 # Each cluster can have at most one main page
1887 Index(
1888 "ix_pages_owner_cluster_id_type",
1889 owner_cluster_id,
1890 type,
1891 unique=True,
1892 postgresql_where=(type == PageType.main_page),
1893 ),
1894 )
1896 def __repr__(self):
1897 return f"Page({self.id=})"
1900class PageVersion(Base):
1901 """
1902 version of page content
1903 """
1905 __tablename__ = "page_versions"
1907 id = Column(BigInteger, primary_key=True)
1909 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)
1910 editor_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1911 title = Column(String, nullable=False)
1912 content = Column(String, nullable=False) # CommonMark without images
1913 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
1914 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
1915 # the human-readable address
1916 address = Column(String, nullable=True)
1917 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1919 slug = column_property(func.slugify(title))
1921 page = relationship("Page", backref="versions", order_by="PageVersion.id")
1922 editor_user = relationship("User", backref="edited_pages")
1923 photo = relationship("Upload")
1925 __table_args__ = (
1926 # Geom and address must either both be null or both be set
1927 CheckConstraint(
1928 "(geom IS NULL) = (address IS NULL)",
1929 name="geom_iff_address",
1930 ),
1931 )
1933 @property
1934 def coordinates(self):
1935 # returns (lat, lng) or None
1936 return get_coordinates(self.geom)
1938 def __repr__(self):
1939 return f"PageVersion({self.id=}, {self.page_id=})"
1942class ClusterEventAssociation(Base):
1943 """
1944 events related to clusters
1945 """
1947 __tablename__ = "cluster_event_associations"
1948 __table_args__ = (UniqueConstraint("event_id", "cluster_id"),)
1950 id = Column(BigInteger, primary_key=True)
1952 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
1953 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
1955 event = relationship("Event", backref="cluster_event_associations")
1956 cluster = relationship("Cluster", backref="cluster_event_associations")
1959class Event(Base):
1960 """
1961 An event is compose of two parts:
1963 * An event template (Event)
1964 * An occurrence (EventOccurrence)
1966 One-off events will have one of each; repeating events will have one Event,
1967 multiple EventOccurrences, one for each time the event happens.
1968 """
1970 __tablename__ = "events"
1972 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
1973 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)
1975 title = Column(String, nullable=False)
1977 slug = column_property(func.slugify(title))
1979 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
1980 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
1981 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)
1982 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)
1983 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
1985 parent_node = relationship(
1986 "Node", backref="child_events", remote_side="Node.id", foreign_keys="Event.parent_node_id"
1987 )
1988 thread = relationship("Thread", backref="event", uselist=False)
1989 subscribers = relationship(
1990 "User", backref="subscribed_events", secondary="event_subscriptions", lazy="dynamic", viewonly=True
1991 )
1992 organizers = relationship(
1993 "User", backref="organized_events", secondary="event_organizers", lazy="dynamic", viewonly=True
1994 )
1995 creator_user = relationship("User", backref="created_events", foreign_keys="Event.creator_user_id")
1996 owner_user = relationship("User", backref="owned_events", foreign_keys="Event.owner_user_id")
1997 owner_cluster = relationship(
1998 "Cluster",
1999 backref=backref("owned_events", lazy="dynamic"),
2000 uselist=False,
2001 foreign_keys="Event.owner_cluster_id",
2002 )
2004 __table_args__ = (
2005 # Only one of owner_user and owner_cluster should be set
2006 CheckConstraint(
2007 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
2008 name="one_owner",
2009 ),
2010 )
2013class EventOccurrence(Base):
2014 __tablename__ = "event_occurrences"
2016 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
2017 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
2019 # the user that created this particular occurrence of a repeating event (same as event.creator_user_id if single event)
2020 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2021 content = Column(String, nullable=False) # CommonMark without images
2022 photo_key = Column(ForeignKey("uploads.key"), nullable=True)
2024 is_cancelled = Column(Boolean, nullable=False, default=False, server_default=text("false"))
2025 is_deleted = Column(Boolean, nullable=False, default=False, server_default=text("false"))
2027 # a null geom is an online-only event
2028 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)
2029 # physical address, iff geom is not null
2030 address = Column(String, nullable=True)
2031 # videoconferencing link, etc, must be specified if no geom, otherwise optional
2032 link = Column(String, nullable=True)
2034 timezone = "Etc/UTC"
2036 # time during which the event takes place; this is a range type (instead of separate start+end times) which
2037 # simplifies database constraints, etc
2038 during = Column(TSTZRANGE, nullable=False)
2040 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2041 last_edited = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2043 creator_user = relationship(
2044 "User", backref="created_event_occurrences", foreign_keys="EventOccurrence.creator_user_id"
2045 )
2046 event = relationship(
2047 "Event",
2048 backref=backref("occurrences", lazy="dynamic"),
2049 remote_side="Event.id",
2050 foreign_keys="EventOccurrence.event_id",
2051 )
2053 photo = relationship("Upload")
2055 __table_args__ = (
2056 # Geom and address go together
2057 CheckConstraint(
2058 # geom and address are either both null or neither of them are null
2059 "(geom IS NULL) = (address IS NULL)",
2060 name="geom_iff_address",
2061 ),
2062 # Online-only events need a link, note that online events may also have a link
2063 CheckConstraint(
2064 # exactly oen of geom or link is non-null
2065 "(geom IS NULL) <> (link IS NULL)",
2066 name="link_or_geom",
2067 ),
2068 # Can't have overlapping occurrences in the same Event
2069 ExcludeConstraint(("event_id", "="), ("during", "&&"), name="event_occurrences_event_id_during_excl"),
2070 )
2072 @property
2073 def coordinates(self):
2074 # returns (lat, lng) or None
2075 return get_coordinates(self.geom)
2077 @hybrid_property
2078 def start_time(self):
2079 return self.during.lower
2081 @start_time.expression
2082 def start_time(cls):
2083 return func.lower(cls.during)
2085 @hybrid_property
2086 def end_time(self):
2087 return self.during.upper
2089 @end_time.expression
2090 def end_time(cls):
2091 return func.upper(cls.during)
2094class EventSubscription(Base):
2095 """
2096 Users' subscriptions to events
2097 """
2099 __tablename__ = "event_subscriptions"
2100 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
2102 id = Column(BigInteger, primary_key=True)
2104 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2105 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
2106 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2108 user = relationship("User")
2109 event = relationship("Event")
2112class EventOrganizer(Base):
2113 """
2114 Organizers for events
2115 """
2117 __tablename__ = "event_organizers"
2118 __table_args__ = (UniqueConstraint("event_id", "user_id"),)
2120 id = Column(BigInteger, primary_key=True)
2122 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2123 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)
2124 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2126 user = relationship("User")
2127 event = relationship("Event")
2130class AttendeeStatus(enum.Enum):
2131 going = enum.auto()
2132 maybe = enum.auto()
2135class EventOccurrenceAttendee(Base):
2136 """
2137 Attendees for events
2138 """
2140 __tablename__ = "event_occurrence_attendees"
2141 __table_args__ = (UniqueConstraint("occurrence_id", "user_id"),)
2143 id = Column(BigInteger, primary_key=True)
2145 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2146 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
2147 responded = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2148 attendee_status = Column(Enum(AttendeeStatus), nullable=False)
2150 user = relationship("User")
2151 occurrence = relationship("EventOccurrence", backref=backref("attendances", lazy="dynamic"))
2154class EventCommunityInviteRequest(Base):
2155 """
2156 Requests to send out invitation notifications/emails to the community for a given event occurrence
2157 """
2159 __tablename__ = "event_community_invite_requests"
2161 id = Column(BigInteger, primary_key=True)
2163 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)
2164 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2166 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2168 decided = Column(DateTime(timezone=True), nullable=True)
2169 decided_by_user_id = Column(ForeignKey("users.id"), nullable=True)
2170 approved = Column(Boolean, nullable=True)
2172 occurrence = relationship("EventOccurrence", backref=backref("community_invite_requests", lazy="dynamic"))
2173 user = relationship("User", foreign_keys="EventCommunityInviteRequest.user_id")
2175 __table_args__ = (
2176 # each user can only request once
2177 UniqueConstraint("occurrence_id", "user_id"),
2178 # each event can only have one notification sent out
2179 Index(
2180 "ix_event_community_invite_requests_unique",
2181 occurrence_id,
2182 unique=True,
2183 postgresql_where=and_(approved.is_not(None), approved == True),
2184 ),
2185 # decided and approved ought to be null simultaneously
2186 CheckConstraint(
2187 "((decided IS NULL) AND (decided_by_user_id IS NULL) AND (approved IS NULL)) OR \
2188 ((decided IS NOT NULL) AND (decided_by_user_id IS NOT NULL) AND (approved IS NOT NULL))",
2189 name="decided_approved",
2190 ),
2191 )
2194class ClusterDiscussionAssociation(Base):
2195 """
2196 discussions related to clusters
2197 """
2199 __tablename__ = "cluster_discussion_associations"
2200 __table_args__ = (UniqueConstraint("discussion_id", "cluster_id"),)
2202 id = Column(BigInteger, primary_key=True)
2204 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2205 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2207 discussion = relationship("Discussion", backref="cluster_discussion_associations")
2208 cluster = relationship("Cluster", backref="cluster_discussion_associations")
2211class Discussion(Base):
2212 """
2213 forum board
2214 """
2216 __tablename__ = "discussions"
2218 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())
2220 title = Column(String, nullable=False)
2221 content = Column(String, nullable=False)
2222 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)
2223 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2225 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2226 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)
2228 slug = column_property(func.slugify(title))
2230 thread = relationship("Thread", backref="discussion", uselist=False)
2232 subscribers = relationship("User", backref="discussions", secondary="discussion_subscriptions", viewonly=True)
2234 creator_user = relationship("User", backref="created_discussions", foreign_keys="Discussion.creator_user_id")
2235 owner_cluster = relationship("Cluster", backref=backref("owned_discussions", lazy="dynamic"), uselist=False)
2238class DiscussionSubscription(Base):
2239 """
2240 users subscriptions to discussions
2241 """
2243 __tablename__ = "discussion_subscriptions"
2244 __table_args__ = (UniqueConstraint("discussion_id", "user_id"),)
2246 id = Column(BigInteger, primary_key=True)
2248 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2249 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)
2250 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2251 left = Column(DateTime(timezone=True), nullable=True)
2253 user = relationship("User", backref="discussion_subscriptions")
2254 discussion = relationship("Discussion", backref="discussion_subscriptions")
2257class Thread(Base):
2258 """
2259 Thread
2260 """
2262 __tablename__ = "threads"
2264 id = Column(BigInteger, primary_key=True)
2266 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2267 deleted = Column(DateTime(timezone=True), nullable=True)
2270class Comment(Base):
2271 """
2272 Comment
2273 """
2275 __tablename__ = "comments"
2277 id = Column(BigInteger, primary_key=True)
2279 thread_id = Column(ForeignKey("threads.id"), nullable=False, index=True)
2280 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2281 content = Column(String, nullable=False) # CommonMark without images
2282 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2283 deleted = Column(DateTime(timezone=True), nullable=True)
2285 thread = relationship("Thread", backref="comments")
2288class Reply(Base):
2289 """
2290 Reply
2291 """
2293 __tablename__ = "replies"
2295 id = Column(BigInteger, primary_key=True)
2297 comment_id = Column(ForeignKey("comments.id"), nullable=False, index=True)
2298 author_user_id = Column(ForeignKey("users.id"), nullable=False)
2299 content = Column(String, nullable=False) # CommonMark without images
2300 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2301 deleted = Column(DateTime(timezone=True), nullable=True)
2303 comment = relationship("Comment", backref="replies")
2306class BackgroundJobState(enum.Enum):
2307 # job is fresh, waiting to be picked off the queue
2308 pending = enum.auto()
2309 # job complete
2310 completed = enum.auto()
2311 # error occured, will be retried
2312 error = enum.auto()
2313 # failed too many times, not retrying anymore
2314 failed = enum.auto()
2317class BackgroundJob(Base):
2318 """
2319 This table implements a queue of background jobs.
2320 """
2322 __tablename__ = "background_jobs"
2324 id = Column(BigInteger, primary_key=True)
2326 # used to discern which function should be triggered to service it
2327 job_type = Column(String, nullable=False)
2328 state = Column(Enum(BackgroundJobState), nullable=False, default=BackgroundJobState.pending)
2330 # time queued
2331 queued = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2333 # time at which we may next attempt it, for implementing exponential backoff
2334 next_attempt_after = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2336 # used to count number of retries for failed jobs
2337 try_count = Column(Integer, nullable=False, default=0)
2339 max_tries = Column(Integer, nullable=False, default=5)
2341 # higher is more important
2342 priority = Column(Integer, nullable=False, server_default=text("10"))
2344 # protobuf encoded job payload
2345 payload = Column(Binary, nullable=False)
2347 # if the job failed, we write that info here
2348 failure_info = Column(String, nullable=True)
2350 __table_args__ = (
2351 # used in looking up background jobs to attempt
2352 # create index on background_jobs(priority desc, next_attempt_after, (max_tries - try_count)) where state = 'pending' OR state = 'error';
2353 Index(
2354 "ix_background_jobs_lookup",
2355 priority.desc(),
2356 next_attempt_after,
2357 (max_tries - try_count),
2358 postgresql_where=((state == BackgroundJobState.pending) | (state == BackgroundJobState.error)),
2359 ),
2360 )
2362 @hybrid_property
2363 def ready_for_retry(self):
2364 return (
2365 (self.next_attempt_after <= func.now())
2366 & (self.try_count < self.max_tries)
2367 & ((self.state == BackgroundJobState.pending) | (self.state == BackgroundJobState.error))
2368 )
2370 def __repr__(self):
2371 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})"
2374class NotificationDeliveryType(enum.Enum):
2375 # send push notification to mobile/web
2376 push = enum.auto()
2377 # send individual email immediately
2378 email = enum.auto()
2379 # send in digest
2380 digest = enum.auto()
2383dt = NotificationDeliveryType
2384nd = notification_data_pb2
2386dt_sec = [dt.email, dt.push]
2387dt_all = [dt.email, dt.push, dt.digest]
2390class NotificationTopicAction(enum.Enum):
2391 def __init__(self, topic_action, defaults, user_editable, data_type):
2392 self.topic, self.action = topic_action.split(":")
2393 self.defaults = defaults
2394 # for now user editable == not a security notification
2395 self.user_editable = user_editable
2397 self.data_type = data_type
2399 def unpack(self):
2400 return self.topic, self.action
2402 @property
2403 def display(self):
2404 return f"{self.topic}:{self.action}"
2406 def __str__(self):
2407 return self.display
2409 # topic, action, default delivery types
2410 friend_request__create = ("friend_request:create", dt_all, True, nd.FriendRequestCreate)
2411 friend_request__accept = ("friend_request:accept", dt_all, True, nd.FriendRequestAccept)
2413 # host requests
2414 host_request__create = ("host_request:create", dt_all, True, nd.HostRequestCreate)
2415 host_request__accept = ("host_request:accept", dt_all, True, nd.HostRequestAccept)
2416 host_request__reject = ("host_request:reject", dt_all, True, nd.HostRequestReject)
2417 host_request__confirm = ("host_request:confirm", dt_all, True, nd.HostRequestConfirm)
2418 host_request__cancel = ("host_request:cancel", dt_all, True, nd.HostRequestCancel)
2419 host_request__message = ("host_request:message", [dt.push, dt.digest], True, nd.HostRequestMessage)
2420 host_request__missed_messages = ("host_request:missed_messages", [dt.email], True, nd.HostRequestMissedMessages)
2422 activeness__probe = ("activeness:probe", dt_sec, False, nd.ActivenessProbe)
2424 # you receive a friend ref
2425 reference__receive_friend = ("reference:receive_friend", dt_all, True, nd.ReferenceReceiveFriend)
2426 # you receive a reference from ... the host
2427 reference__receive_hosted = ("reference:receive_hosted", dt_all, True, nd.ReferenceReceiveHostRequest)
2428 # ... the surfer
2429 reference__receive_surfed = ("reference:receive_surfed", dt_all, True, nd.ReferenceReceiveHostRequest)
2431 # you hosted
2432 reference__reminder_hosted = ("reference:reminder_hosted", dt_all, True, nd.ReferenceReminder)
2433 # you surfed
2434 reference__reminder_surfed = ("reference:reminder_surfed", dt_all, True, nd.ReferenceReminder)
2436 badge__add = ("badge:add", [dt.push, dt.digest], True, nd.BadgeAdd)
2437 badge__remove = ("badge:remove", [dt.push, dt.digest], True, nd.BadgeRemove)
2439 # group chats
2440 chat__message = ("chat:message", [dt.push, dt.digest], True, nd.ChatMessage)
2441 chat__missed_messages = ("chat:missed_messages", [dt.email], True, nd.ChatMissedMessages)
2443 # events
2444 # approved by mods
2445 event__create_approved = ("event:create_approved", dt_all, True, nd.EventCreate)
2446 # any user creates any event, default to no notifications
2447 event__create_any = ("event:create_any", [], True, nd.EventCreate)
2448 event__update = ("event:update", dt_all, True, nd.EventUpdate)
2449 event__cancel = ("event:cancel", dt_all, True, nd.EventCancel)
2450 event__delete = ("event:delete", dt_all, True, nd.EventDelete)
2451 event__invite_organizer = ("event:invite_organizer", dt_all, True, nd.EventInviteOrganizer)
2452 # toplevel comment on an event
2453 event__comment = ("event:comment", dt_all, True, nd.EventComment)
2455 # discussion created
2456 discussion__create = ("discussion:create", [dt.digest], True, nd.DiscussionCreate)
2457 # someone comments on your discussion
2458 discussion__comment = ("discussion:comment", dt_all, True, nd.DiscussionComment)
2460 # someone responds to any of your top-level comment across the platform
2461 thread__reply = ("thread:reply", dt_all, True, nd.ThreadReply)
2463 # account settings
2464 password__change = ("password:change", dt_sec, False, empty_pb2.Empty)
2465 email_address__change = ("email_address:change", dt_sec, False, nd.EmailAddressChange)
2466 email_address__verify = ("email_address:verify", dt_sec, False, empty_pb2.Empty)
2467 phone_number__change = ("phone_number:change", dt_sec, False, nd.PhoneNumberChange)
2468 phone_number__verify = ("phone_number:verify", dt_sec, False, nd.PhoneNumberVerify)
2469 # reset password
2470 password_reset__start = ("password_reset:start", dt_sec, False, nd.PasswordResetStart)
2471 password_reset__complete = ("password_reset:complete", dt_sec, False, empty_pb2.Empty)
2473 # account deletion
2474 account_deletion__start = ("account_deletion:start", dt_sec, False, nd.AccountDeletionStart)
2475 # no more pushing to do
2476 account_deletion__complete = ("account_deletion:complete", dt_sec, False, nd.AccountDeletionComplete)
2477 # undeleted
2478 account_deletion__recovered = ("account_deletion:recovered", dt_sec, False, empty_pb2.Empty)
2480 # admin actions
2481 gender__change = ("gender:change", dt_sec, False, nd.GenderChange)
2482 birthdate__change = ("birthdate:change", dt_sec, False, nd.BirthdateChange)
2483 api_key__create = ("api_key:create", dt_sec, False, nd.ApiKeyCreate)
2485 donation__received = ("donation:received", dt_sec, True, nd.DonationReceived)
2487 onboarding__reminder = ("onboarding:reminder", dt_sec, True, empty_pb2.Empty)
2489 modnote__create = ("modnote:create", dt_sec, False, empty_pb2.Empty)
2491 verification__sv_fail = ("verification:sv_fail", dt_sec, False, nd.VerificationSVFail)
2492 verification__sv_success = ("verification:sv_success", dt_sec, False, empty_pb2.Empty)
2494 # general announcements
2495 general__new_blog_post = ("general:new_blog_post", [dt.push, dt.digest], True, nd.GeneralNewBlogPost)
2498class NotificationPreference(Base):
2499 __tablename__ = "notification_preferences"
2501 id = Column(BigInteger, primary_key=True)
2502 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2504 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2505 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2506 deliver = Column(Boolean, nullable=False)
2508 user = relationship("User", foreign_keys="NotificationPreference.user_id")
2510 __table_args__ = (UniqueConstraint("user_id", "topic_action", "delivery_type"),)
2513class Notification(Base):
2514 """
2515 Table for accumulating notifications until it is time to send email digest
2516 """
2518 __tablename__ = "notifications"
2520 id = Column(BigInteger, primary_key=True)
2521 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2523 # recipient user id
2524 user_id = Column(ForeignKey("users.id"), nullable=False)
2526 topic_action = Column(Enum(NotificationTopicAction), nullable=False)
2527 key = Column(String, nullable=False)
2529 data = Column(Binary, nullable=False)
2531 # whether the user has marked this notification as seen or not
2532 is_seen = Column(Boolean, nullable=False, server_default=text("false"))
2534 user = relationship("User", foreign_keys="Notification.user_id")
2536 __table_args__ = (
2537 # used in looking up which notifications need delivery
2538 Index(
2539 "ix_notifications_created",
2540 created,
2541 ),
2542 # Fast lookup for unseen notification count
2543 Index(
2544 "ix_notifications_unseen",
2545 user_id,
2546 topic_action,
2547 postgresql_where=(is_seen == False),
2548 ),
2549 # Fast lookup for latest notifications
2550 Index(
2551 "ix_notifications_latest",
2552 user_id,
2553 id.desc(),
2554 topic_action,
2555 ),
2556 )
2558 @property
2559 def topic(self):
2560 return self.topic_action.topic
2562 @property
2563 def action(self):
2564 return self.topic_action.action
2567class NotificationDelivery(Base):
2568 __tablename__ = "notification_deliveries"
2570 id = Column(BigInteger, primary_key=True)
2571 notification_id = Column(ForeignKey("notifications.id"), nullable=False, index=True)
2572 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2573 delivered = Column(DateTime(timezone=True), nullable=True)
2574 read = Column(DateTime(timezone=True), nullable=True)
2575 # todo: enum of "phone, web, digest"
2576 delivery_type = Column(Enum(NotificationDeliveryType), nullable=False)
2577 # todo: device id
2578 # todo: receipt id, etc
2579 notification = relationship("Notification", foreign_keys="NotificationDelivery.notification_id")
2581 __table_args__ = (
2582 UniqueConstraint("notification_id", "delivery_type"),
2583 # used in looking up which notifications need delivery
2584 Index(
2585 "ix_notification_deliveries_delivery_type",
2586 delivery_type,
2587 postgresql_where=(delivered != None),
2588 ),
2589 Index(
2590 "ix_notification_deliveries_dt_ni_dnull",
2591 delivery_type,
2592 notification_id,
2593 delivered == None,
2594 ),
2595 )
2598class PushNotificationSubscription(Base):
2599 __tablename__ = "push_notification_subscriptions"
2601 id = Column(BigInteger, primary_key=True)
2602 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2604 # which user this is connected to
2605 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)
2607 # these come from https://developer.mozilla.org/en-US/docs/Web/API/PushSubscription
2608 # the endpoint
2609 endpoint = Column(String, nullable=False)
2610 # the "auth" key
2611 auth_key = Column(Binary, nullable=False)
2612 # the "p256dh" key
2613 p256dh_key = Column(Binary, nullable=False)
2615 full_subscription_info = Column(String, nullable=False)
2617 # the browse user-agent, so we can tell the user what browser notifications are going to
2618 user_agent = Column(String, nullable=True)
2620 # when it was disabled
2621 disabled_at = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_INFINITY.isoformat())
2623 user = relationship("User")
2626class PushNotificationDeliveryAttempt(Base):
2627 __tablename__ = "push_notification_delivery_attempt"
2629 id = Column(BigInteger, primary_key=True)
2630 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2632 push_notification_subscription_id = Column(
2633 ForeignKey("push_notification_subscriptions.id"), nullable=False, index=True
2634 )
2636 success = Column(Boolean, nullable=False)
2637 # the HTTP status code, 201 is success
2638 status_code = Column(Integer, nullable=False)
2640 # can be null if it was a success
2641 response = Column(String, nullable=True)
2643 push_notification_subscription = relationship("PushNotificationSubscription")
2646class Language(Base):
2647 """
2648 Table of allowed languages (a subset of ISO639-3)
2649 """
2651 __tablename__ = "languages"
2653 # ISO639-3 language code, in lowercase, e.g. fin, eng
2654 code = Column(String(3), primary_key=True)
2656 # the english name
2657 name = Column(String, nullable=False, unique=True)
2660class Region(Base):
2661 """
2662 Table of regions
2663 """
2665 __tablename__ = "regions"
2667 # iso 3166-1 alpha3 code in uppercase, e.g. FIN, USA
2668 code = Column(String(3), primary_key=True)
2670 # the name, e.g. Finland, United States
2671 # this is the display name in English, should be the "common name", not "Republic of Finland"
2672 name = Column(String, nullable=False, unique=True)
2675class UserBlock(Base):
2676 """
2677 Table of blocked users
2678 """
2680 __tablename__ = "user_blocks"
2682 id = Column(BigInteger, primary_key=True)
2684 blocking_user_id = Column(ForeignKey("users.id"), nullable=False)
2685 blocked_user_id = Column(ForeignKey("users.id"), nullable=False)
2686 time_blocked = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2688 blocking_user = relationship("User", foreign_keys="UserBlock.blocking_user_id")
2689 blocked_user = relationship("User", foreign_keys="UserBlock.blocked_user_id")
2691 __table_args__ = (
2692 UniqueConstraint("blocking_user_id", "blocked_user_id"),
2693 Index("ix_user_blocks_blocking_user_id", blocking_user_id, blocked_user_id),
2694 Index("ix_user_blocks_blocked_user_id", blocked_user_id, blocking_user_id),
2695 )
2698class APICall(Base):
2699 """
2700 API call logs
2701 """
2703 __tablename__ = "api_calls"
2704 __table_args__ = {"schema": "logging"}
2706 id = Column(BigInteger, primary_key=True)
2708 # whether the call was made using an api key or session cookies
2709 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))
2711 # backend version (normally e.g. develop-31469e3), allows us to figure out which proto definitions were used
2712 # note that `default` is a python side default, not hardcoded into DB schema
2713 version = Column(String, nullable=False, default=config["VERSION"])
2715 # approximate time of the call
2716 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2718 # the method call name, e.g. "/org.couchers.api.core.API/ListFriends"
2719 method = Column(String, nullable=False)
2721 # gRPC status code name, e.g. FAILED_PRECONDITION, None if success
2722 status_code = Column(String, nullable=True)
2724 # handler duration (excluding serialization, etc)
2725 duration = Column(Float, nullable=False)
2727 # user_id of caller, None means not logged in
2728 user_id = Column(BigInteger, nullable=True)
2730 # sanitized request bytes
2731 request = Column(Binary, nullable=True)
2733 # sanitized response bytes
2734 response = Column(Binary, nullable=True)
2736 # whether response bytes have been truncated
2737 response_truncated = Column(Boolean, nullable=False, server_default=text("false"))
2739 # the exception traceback, if any
2740 traceback = Column(String, nullable=True)
2742 # human readable perf report
2743 perf_report = Column(String, nullable=True)
2745 # details of the browser, if available
2746 ip_address = Column(String, nullable=True)
2747 user_agent = Column(String, nullable=True)
2750class AccountDeletionReason(Base):
2751 __tablename__ = "account_deletion_reason"
2753 id = Column(BigInteger, primary_key=True)
2754 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
2755 user_id = Column(ForeignKey("users.id"), nullable=False)
2756 reason = Column(String, nullable=True)
2758 user = relationship("User")