Coverage for src/couchers/models.py: 98%

1import enum

2from calendar import monthrange

3from datetime import date

5from geoalchemy2.types import Geometry

6from sqlalchemy import (

7 ARRAY,

8 BigInteger,

9 Boolean,

10 CheckConstraint,

11 Column,

12 Date,

13 DateTime,

14 Enum,

15 Float,

16 ForeignKey,

17 Index,

18 Integer,

19)

20from sqlalchemy import LargeBinary as Binary

21from sqlalchemy import MetaData, Sequence, String, UniqueConstraint

22from sqlalchemy.dialects.postgresql import TSTZRANGE, ExcludeConstraint

23from sqlalchemy.ext.associationproxy import association_proxy

24from sqlalchemy.ext.hybrid import hybrid_property

25from sqlalchemy.orm import backref, column_property, declarative_base, deferred, relationship

26from sqlalchemy.sql import func

27from sqlalchemy.sql import select as sa_select

28from sqlalchemy.sql import text

30from couchers.config import config

31from couchers.constants import (

32 DATETIME_INFINITY,

33 DATETIME_MINUS_INFINITY,

34 EMAIL_REGEX,

35 GUIDELINES_VERSION,

36 PHONE_VERIFICATION_LIFETIME,

37 SMS_CODE_LIFETIME,

38 TOS_VERSION,

39)

40from couchers.utils import date_in_timezone, get_coordinates, last_active_coarsen, now

42meta = MetaData(

43 naming_convention={

44 "ix": "ix_%(column_0_label)s",

45 "uq": "uq_%(table_name)s_%(column_0_name)s",

46 "ck": "ck_%(table_name)s_%(constraint_name)s",

47 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",

48 "pk": "pk_%(table_name)s",

49 }

50)

52Base = declarative_base(metadata=meta)

55class HostingStatus(enum.Enum):

56 can_host = enum.auto()

57 maybe = enum.auto()

58 cant_host = enum.auto()

61class MeetupStatus(enum.Enum):

62 wants_to_meetup = enum.auto()

63 open_to_meetup = enum.auto()

64 does_not_want_to_meetup = enum.auto()

67class SmokingLocation(enum.Enum):

68 yes = enum.auto()

69 window = enum.auto()

70 outside = enum.auto()

71 no = enum.auto()

74class SleepingArrangement(enum.Enum):

75 private = enum.auto()

76 common = enum.auto()

77 shared_room = enum.auto()

78 shared_space = enum.auto()

81class ParkingDetails(enum.Enum):

82 free_onsite = enum.auto()

83 free_offsite = enum.auto()

84 paid_onsite = enum.auto()

85 paid_offsite = enum.auto()

88class TimezoneArea(Base):

89 __tablename__ = "timezone_areas"

90 id = Column(BigInteger, primary_key=True)

92 tzid = Column(String)

93 geom = Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False)

95 __table_args__ = (

96 Index(

97 "ix_timezone_areas_geom_tzid",

98 geom,

99 tzid,

100 postgresql_using="gist",

101 ),

102 )

103

104

105class User(Base):

106 """

107 Basic user and profile details

108 """

109

110 __tablename__ = "users"

111

112 id = Column(BigInteger, primary_key=True)

113

114 username = Column(String, nullable=False, unique=True)

115 email = Column(String, nullable=False, unique=True)

116 # stored in libsodium hash format, can be null for email login

117 hashed_password = Column(Binary, nullable=True)

118 # phone number in E.164 format with leading +, for example "+46701740605"

119 phone = Column(String, nullable=True, server_default=text("NULL"))

120

121 # timezones should always be UTC

122 ## location

123 # point describing their location. EPSG4326 is the SRS (spatial ref system, = way to describe a point on earth) used

124 # by GPS, it has the WGS84 geoid with lat/lon

125 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)

126 # their display location (displayed to other users), in meters

127 geom_radius = Column(Float, nullable=True)

128 # the display address (text) shown on their profile

129 city = Column(String, nullable=False)

130 hometown = Column(String, nullable=True)

131

132 regions_visited = relationship("Region", secondary="regions_visited", order_by="Region.name")

133 regions_lived = relationship("Region", secondary="regions_lived", order_by="Region.name")

134

135 timezone = column_property(

136 sa_select(TimezoneArea.tzid).where(func.ST_Contains(TimezoneArea.geom, geom)).limit(1).scalar_subquery(),

137 deferred=True,

138 )

139

140 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

141 last_active = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

142

143 # id of the last message that they received a notification about

144 last_notified_message_id = Column(BigInteger, nullable=False, default=0)

145 # same as above for host requests

146 last_notified_request_message_id = Column(BigInteger, nullable=False, server_default=text("0"))

147

148 # display name

149 name = Column(String, nullable=False)

150 gender = Column(String, nullable=False)

151 pronouns = Column(String, nullable=True)

152 birthdate = Column(Date, nullable=False) # in the timezone of birthplace

153

154 # name as on official docs for verification, etc. not needed until verification

155 full_name = Column(String, nullable=True)

156

157 avatar_key = Column(ForeignKey("uploads.key"), nullable=True)

158

159 hosting_status = Column(Enum(HostingStatus), nullable=False)

160 meetup_status = Column(Enum(MeetupStatus), nullable=False, server_default="open_to_meetup")

161

162 # community standing score

163 community_standing = Column(Float, nullable=True)

164

165 occupation = Column(String, nullable=True) # CommonMark without images

166 education = Column(String, nullable=True) # CommonMark without images

167 about_me = Column(String, nullable=True) # CommonMark without images

168 my_travels = Column(String, nullable=True) # CommonMark without images

169 things_i_like = Column(String, nullable=True) # CommonMark without images

170 about_place = Column(String, nullable=True) # CommonMark without images

171 additional_information = Column(String, nullable=True) # CommonMark without images

172

173 is_banned = Column(Boolean, nullable=False, server_default=text("false"))

174 is_deleted = Column(Boolean, nullable=False, server_default=text("false"))

175 is_superuser = Column(Boolean, nullable=False, server_default=text("false"))

176

177 # the undelete token allows a user to recover their account for a couple of days after deletion in case it was

178 # accidental or they changed their mind

179 # constraints make sure these are non-null only if is_deleted and that these are null in unison

180 undelete_token = Column(String, nullable=True)

181 # validity of the undelete token

182 undelete_until = Column(DateTime(timezone=True), nullable=True)

183

184 # hosting preferences

185 max_guests = Column(Integer, nullable=True)

186 last_minute = Column(Boolean, nullable=True)

187 has_pets = Column(Boolean, nullable=True)

188 accepts_pets = Column(Boolean, nullable=True)

189 pet_details = Column(String, nullable=True) # CommonMark without images

190 has_kids = Column(Boolean, nullable=True)

191 accepts_kids = Column(Boolean, nullable=True)

192 kid_details = Column(String, nullable=True) # CommonMark without images

193 has_housemates = Column(Boolean, nullable=True)

194 housemate_details = Column(String, nullable=True) # CommonMark without images

195 wheelchair_accessible = Column(Boolean, nullable=True)

196 smoking_allowed = Column(Enum(SmokingLocation), nullable=True)

197 smokes_at_home = Column(Boolean, nullable=True)

198 drinking_allowed = Column(Boolean, nullable=True)

199 drinks_at_home = Column(Boolean, nullable=True)

200 other_host_info = Column(String, nullable=True) # CommonMark without images

201

202 sleeping_arrangement = Column(Enum(SleepingArrangement), nullable=True)

203 sleeping_details = Column(String, nullable=True) # CommonMark without images

204 area = Column(String, nullable=True) # CommonMark without images

205 house_rules = Column(String, nullable=True) # CommonMark without images

206 parking = Column(Boolean, nullable=True)

207 parking_details = Column(Enum(ParkingDetails), nullable=True) # CommonMark without images

208 camping_ok = Column(Boolean, nullable=True)

209

210 accepted_tos = Column(Integer, nullable=False, default=0)

211 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")

212 # whether the user has yet filled in the contributor form

213 filled_contributor_form = Column(Boolean, nullable=False, server_default="false")

214

215 # number of onboarding emails sent

216 onboarding_emails_sent = Column(Integer, nullable=False, server_default="0")

217 last_onboarding_email_sent = Column(DateTime(timezone=True), nullable=True)

218

219 added_to_mailing_list = Column(Boolean, nullable=False, server_default="false")

220

221 last_digest_sent = Column(DateTime(timezone=True), nullable=True)

222

223 # for changing their email

224 new_email = Column(String, nullable=True)

225 old_email_token = Column(String, nullable=True)

226 old_email_token_created = Column(DateTime(timezone=True), nullable=True)

227 old_email_token_expiry = Column(DateTime(timezone=True), nullable=True)

228 need_to_confirm_via_old_email = Column(Boolean, nullable=True, default=None)

229 new_email_token = Column(String, nullable=True)

230 new_email_token_created = Column(DateTime(timezone=True), nullable=True)

231 new_email_token_expiry = Column(DateTime(timezone=True), nullable=True)

232 need_to_confirm_via_new_email = Column(Boolean, nullable=True, default=None)

233

234 recommendation_score = Column(Float, nullable=False, server_default="0")

235

236 # Columns for verifying their phone number. State chart:

237 # ,-------------------,

238 # | Start |

239 # | phone = None | someone else

240 # ,-----------------, | token = None | verifies ,-----------------------,

244 # | sent = zz (exp.)| | '-------------------' | | sent = zz |

245 # | verified = None | | V ^ +-----------< | verified = ww (exp.) |

247 # '-----------------' +-------- | ------+----+--------------------+ '-----------------------'

249 # | | ^ V | |

250 # ,-----------------, | | ,-------------------, | ,-----------------------,

251 # | Too Many | >--' '--< | Code sent | >------+ | Verified |

256 # | attempts = 3 | '--------> | attempts = 0..2 | >------------------> | attempts = 0 |

257 # '-----------------' '-------------------' '-----------------------'

258

259 # randomly generated Luhn 6-digit string

260 phone_verification_token = Column(String(6), nullable=True, server_default=text("NULL"))

261

262 phone_verification_sent = Column(DateTime(timezone=True), nullable=False, server_default=text("to_timestamp(0)"))

263 phone_verification_verified = Column(DateTime(timezone=True), nullable=True, server_default=text("NULL"))

264 phone_verification_attempts = Column(Integer, nullable=False, server_default=text("0"))

265

266 # the stripe customer identifier if the user has donated to Couchers

267 # e.g. cus_JjoXHttuZopv0t

268 # for new US entity

269 stripe_customer_id = Column(String, nullable=True)

270 # for old AU entity

271 stripe_customer_id_old = Column(String, nullable=True)

272

273 # True if the user has opted in to get notifications using the new notification system

274 # This column will be removed in the future when notifications are enabled for everyone and come out of preview

275 new_notifications_enabled = Column(Boolean, nullable=False, server_default=text("false"))

276

277 avatar = relationship("Upload", foreign_keys="User.avatar_key")

278

279 __table_args__ = (

280 # Verified phone numbers should be unique

281 Index(

282 "ix_users_unique_phone",

283 phone,

284 unique=True,

285 postgresql_where=phone_verification_verified != None,

286 ),

287 Index(

288 "ix_users_active",

289 id,

290 postgresql_where=~is_banned & ~is_deleted,

291 ),

292 # create index on users(geom, id, username) where not is_banned and not is_deleted and geom is not null;

293 Index(

294 "ix_users_geom_active",

295 geom,

296 id,

297 username,

298 postgresql_where=~is_banned & ~is_deleted & (geom != None),

299 ),

300 # There are three possible states for need_to_confirm_via_old_email, old_email_token, old_email_token_created, and old_email_token_expiry

301 # 1) All None (default)

302 # 2) need_to_confirm_via_old_email is True and the others have assigned value (confirmation initiated)

303 # 3) need_to_confirm_via_old_email is False and the others are None (confirmation via old email complete)

304 CheckConstraint(

305 "(need_to_confirm_via_old_email IS NULL AND old_email_token IS NULL AND old_email_token_created IS NULL AND old_email_token_expiry IS NULL) OR \

306 (need_to_confirm_via_old_email IS TRUE AND old_email_token IS NOT NULL AND old_email_token_created IS NOT NULL AND old_email_token_expiry IS NOT NULL) OR \

307 (need_to_confirm_via_old_email IS FALSE AND old_email_token IS NULL AND old_email_token_created IS NULL AND old_email_token_expiry IS NULL)",

308 name="check_old_email_token_state",

309 ),

310 # There are three possible states for need_to_confirm_via_new_email, new_email_token, new_email_token_created, and new_email_token_expiry

311 # They mirror the states above

312 CheckConstraint(

313 "(need_to_confirm_via_new_email IS NULL AND new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL) OR \

314 (need_to_confirm_via_new_email IS TRUE AND new_email_token IS NOT NULL AND new_email_token_created IS NOT NULL AND new_email_token_expiry IS NOT NULL) OR \

315 (need_to_confirm_via_new_email IS FALSE AND new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL)",

316 name="check_new_email_token_state",

317 ),

318 # Whenever a phone number is set, it must either be pending verification or already verified.

319 # Exactly one of the following must always be true: not phone, token, verified.

320 CheckConstraint(

321 "(phone IS NULL)::int + (phone_verification_verified IS NOT NULL)::int + (phone_verification_token IS NOT NULL)::int = 1",

322 name="phone_verified_conditions",

323 ),

324 # Email must match our regex

325 CheckConstraint(

326 f"email ~ '{EMAIL_REGEX}'",

327 name="valid_email",

328 ),

329 # Undelete token + time are coupled: either both null or neither; and if they're not null then the account is deleted

330 CheckConstraint(

331 "((undelete_token IS NULL) = (undelete_until IS NULL)) AND ((undelete_token IS NULL) OR is_deleted)",

332 name="undelete_nullity",

333 ),

334 )

335

336 @hybrid_property

337 def has_completed_profile(self):

338 return self.avatar_key is not None and self.about_me is not None and len(self.about_me) >= 20

339

340 @has_completed_profile.expression

341 def has_completed_profile(cls):

342 return (cls.avatar_key != None) & (func.character_length(cls.about_me) >= 20)

343

344 @property

345 def has_password(self):

346 return self.hashed_password is not None

347

348 @hybrid_property

349 def is_jailed(self):

350 return (

351 (self.accepted_tos < TOS_VERSION)

352 | (self.accepted_community_guidelines < GUIDELINES_VERSION)

353 | self.is_missing_location

354 | (self.hashed_password == None)

355 )

356

357 @hybrid_property

358 def is_missing_location(self):

359 return (self.geom == None) | (self.geom_radius == None)

360

361 @hybrid_property

362 def is_visible(self):

363 return ~(self.is_banned | self.is_deleted)

364

365 @property

366 def coordinates(self):

367 if self.geom:

368 return get_coordinates(self.geom)

369 else:

370 return None

371

372 @property

373 def age(self):

374 max_day = monthrange(date.today().year, self.birthdate.month)[1]

375 age = date.today().year - self.birthdate.year

376 # in case of leap-day babies, make sure the date is valid for this year

377 safe_birthdate = self.birthdate

378 if self.birthdate.day > max_day:

379 safe_birthdate = safe_birthdate.replace(day=max_day)

380 if date.today() < safe_birthdate.replace(year=date.today().year):

381 age -= 1

382 return age

383

384 @property

385 def display_joined(self):

386 """

387 Returns the last active time rounded down to the nearest hour.

388 """

389 return self.joined.replace(minute=0, second=0, microsecond=0)

390

391 @property

392 def display_last_active(self):

393 """

394 Returns the last active time rounded down whatever is the "last active" coarsening.

395 """

396 return last_active_coarsen(self.last_active)

397

398 @hybrid_property

399 def phone_is_verified(self):

400 return (

401 self.phone_verification_verified is not None

402 and now() - self.phone_verification_verified < PHONE_VERIFICATION_LIFETIME

403 )

404

405 @phone_is_verified.expression

406 def phone_is_verified(cls):

407 return (cls.phone_verification_verified != None) & (

408 now() - cls.phone_verification_verified < PHONE_VERIFICATION_LIFETIME

409 )

410

411 @hybrid_property

412 def phone_code_expired(self):

413 return now() - self.phone_verification_sent > SMS_CODE_LIFETIME

414

415 def __repr__(self):

416 return f"User(id={self.id}, email={self.email}, username={self.username})"

417

418

419class OneTimeDonation(Base):

420 __tablename__ = "one_time_donations"

421 id = Column(BigInteger, primary_key=True)

422

423 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

424 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

425 amount = Column(Float, nullable=False)

426 stripe_checkout_session_id = Column(String, nullable=False)

427 stripe_payment_intent_id = Column(String, nullable=False)

428 paid = Column(DateTime(timezone=True), nullable=True)

429

430 user = relationship("User", backref="one_time_donations")

431

432

433class RecurringDonation(Base):

434 __tablename__ = "recurring_donations"

435

436 id = Column(BigInteger, primary_key=True)

437

438 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

439 user_id = Column(ForeignKey("users.id"), nullable=False)

440 amount = Column(Float, nullable=False)

441 stripe_checkout_session_id = Column(String, nullable=False)

442 # for some silly reason the events come unordered from stripe

443 # e.g. sub_JjonjdfUIeZyn0

444 stripe_subscription_id = Column(String, nullable=True)

445

446 user = relationship("User", backref="recurring_donations")

447

448

449class Invoice(Base):

450 """

451 Successful donations, both one off and recurring

452

453 Triggered by `payment_intent.succeeded` webhook

454 """

455

456 __tablename__ = "invoices"

457

458 id = Column(BigInteger, primary_key=True)

459 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

460 user_id = Column(ForeignKey("users.id"), nullable=False)

461

462 amount = Column(Float, nullable=False)

463

464 stripe_payment_intent_id = Column(String, nullable=False, unique=True)

465 stripe_receipt_url = Column(String, nullable=False)

466

467 user = relationship("User", backref="invoices")

468

469

470class LanguageFluency(enum.Enum):

471 # note that the numbering is important here, these are ordinal

472 beginner = 1

473 conversational = 2

474 fluent = 3

475

476

477class LanguageAbility(Base):

478 __tablename__ = "language_abilities"

479 __table_args__ = (

480 # Users can only have one language ability per language

481 UniqueConstraint("user_id", "language_code"),

482 )

483

484 id = Column(BigInteger, primary_key=True)

485 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

486 language_code = Column(ForeignKey("languages.code", deferrable=True), nullable=False)

487 fluency = Column(Enum(LanguageFluency), nullable=False)

488

489 user = relationship("User", backref="language_abilities")

490 language = relationship("Language")

491

492

493class RegionVisited(Base):

494 __tablename__ = "regions_visited"

495 __table_args__ = (UniqueConstraint("user_id", "region_code"),)

496

497 id = Column(BigInteger, primary_key=True)

498 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

499 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)

500

501

502class RegionLived(Base):

503 __tablename__ = "regions_lived"

504 __table_args__ = (UniqueConstraint("user_id", "region_code"),)

505

506 id = Column(BigInteger, primary_key=True)

507 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

508 region_code = Column(ForeignKey("regions.code", deferrable=True), nullable=False)

509

510

511class FriendStatus(enum.Enum):

512 pending = enum.auto()

513 accepted = enum.auto()

514 rejected = enum.auto()

515 cancelled = enum.auto()

516

517

518class FriendRelationship(Base):

519 """

520 Friendship relations between users

521

522 TODO: make this better with sqlalchemy self-referential stuff

523 TODO: constraint on only one row per user pair where accepted or pending

524 """

525

526 __tablename__ = "friend_relationships"

527

528 id = Column(BigInteger, primary_key=True)

529

530 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

531 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

532

533 status = Column(Enum(FriendStatus), nullable=False, default=FriendStatus.pending)

534

535 # timezones should always be UTC

536 time_sent = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

537 time_responded = Column(DateTime(timezone=True), nullable=True)

538

539 from_user = relationship("User", backref="friends_from", foreign_keys="FriendRelationship.from_user_id")

540 to_user = relationship("User", backref="friends_to", foreign_keys="FriendRelationship.to_user_id")

541

542

543class ContributeOption(enum.Enum):

544 yes = enum.auto()

545 maybe = enum.auto()

546 no = enum.auto()

547

548

549class ContributorForm(Base):

550 """

551 Someone filled in the contributor form

552 """

553

554 __tablename__ = "contributor_forms"

555

556 id = Column(BigInteger, primary_key=True)

557

558 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

559 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

560

561 ideas = Column(String, nullable=True)

562 features = Column(String, nullable=True)

563 experience = Column(String, nullable=True)

564 contribute = Column(Enum(ContributeOption), nullable=True)

565 contribute_ways = Column(ARRAY(String), nullable=False)

566 expertise = Column(String, nullable=True)

567

568 user = relationship("User", backref="contributor_forms")

569

570 @hybrid_property

571 def is_filled(self):

572 """

573 Whether the form counts as having been filled

574 """

575 return (

576 (self.ideas != None)

577 | (self.features != None)

578 | (self.experience != None)

579 | (self.contribute != None)

580 | (self.contribute_ways != [])

581 | (self.expertise != None)

582 )

583

584 @property

585 def should_notify(self):

586 """

587 If this evaluates to true, we send an email to the recruitment team.

588

589 We currently send if expertise is listed, or if they list a way to help outside of a set list

590 """

591 return (self.expertise != None) | (not set(self.contribute_ways).issubset(set(["community", "blog", "other"])))

592

593

594class SignupFlow(Base):

595 """

596 Signup flows/incomplete users

597

598 Coinciding fields have the same meaning as in User

599 """

600

601 __tablename__ = "signup_flows"

602

603 id = Column(BigInteger, primary_key=True)

604

605 # housekeeping

606 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

607 flow_token = Column(String, nullable=False, unique=True)

608 email_verified = Column(Boolean, nullable=False, default=False)

609 email_sent = Column(Boolean, nullable=False, default=False)

610 email_token = Column(String, nullable=True)

611 email_token_expiry = Column(DateTime(timezone=True), nullable=True)

612

613 ## Basic

614 name = Column(String, nullable=False)

615 # TODO: unique across both tables

616 email = Column(String, nullable=False, unique=True)

617 # TODO: invitation, attribution

618

619 ## Account

620 # TODO: unique across both tables

621 username = Column(String, nullable=True, unique=True)

622 hashed_password = Column(Binary, nullable=True)

623 birthdate = Column(Date, nullable=True) # in the timezone of birthplace

624 gender = Column(String, nullable=True)

625 hosting_status = Column(Enum(HostingStatus), nullable=True)

626 city = Column(String, nullable=True)

627 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)

628 geom_radius = Column(Float, nullable=True)

629

630 accepted_tos = Column(Integer, nullable=True)

631 accepted_community_guidelines = Column(Integer, nullable=False, server_default="0")

632

633 ## Feedback

634 filled_feedback = Column(Boolean, nullable=False, default=False)

635 ideas = Column(String, nullable=True)

636 features = Column(String, nullable=True)

637 experience = Column(String, nullable=True)

638 contribute = Column(Enum(ContributeOption), nullable=True)

639 contribute_ways = Column(ARRAY(String), nullable=True)

640 expertise = Column(String, nullable=True)

641

642 @hybrid_property

643 def token_is_valid(self):

644 return (self.email_token != None) & (self.email_token_expiry >= now())

645

646 @hybrid_property

647 def account_is_filled(self):

648 return (

649 (self.username != None)

650 & (self.birthdate != None)

651 & (self.gender != None)

652 & (self.hosting_status != None)

653 & (self.city != None)

654 & (self.geom != None)

655 & (self.geom_radius != None)

656 & (self.accepted_tos != None)

657 )

658

659 @hybrid_property

660 def is_completed(self):

661 return (

662 self.email_verified

663 & self.account_is_filled

664 & self.filled_feedback

665 & (self.accepted_community_guidelines == GUIDELINES_VERSION)

666 )

667

668

669class LoginToken(Base):

670 """

671 A login token sent in an email to a user, allows them to sign in between the times defined by created and expiry

672 """

673

674 __tablename__ = "login_tokens"

675 token = Column(String, primary_key=True)

676

677 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

678

679 # timezones should always be UTC

680 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

681 expiry = Column(DateTime(timezone=True), nullable=False)

682

683 user = relationship("User", backref="login_tokens")

684

685 @hybrid_property

686 def is_valid(self):

687 return (self.created <= now()) & (self.expiry >= now())

688

689 def __repr__(self):

690 return f"LoginToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"

691

692

693class PasswordResetToken(Base):

694 __tablename__ = "password_reset_tokens"

695 token = Column(String, primary_key=True)

696

697 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

698

699 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

700 expiry = Column(DateTime(timezone=True), nullable=False)

701

702 user = relationship("User", backref="password_reset_tokens")

703

704 @hybrid_property

705 def is_valid(self):

706 return (self.created <= now()) & (self.expiry >= now())

707

708 def __repr__(self):

709 return f"PasswordResetToken(token={self.token}, user={self.user}, created={self.created}, expiry={self.expiry})"

710

711

712class AccountDeletionToken(Base):

713 __tablename__ = "account_deletion_tokens"

714

715 token = Column(String, primary_key=True)

716

717 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

718

719 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

720 expiry = Column(DateTime(timezone=True), nullable=False)

721

722 user = relationship("User", backref="account_deletion_tokens")

723

724 @hybrid_property

725 def is_valid(self):

726 return (self.created <= now()) & (self.expiry >= now())

727

728 def __repr__(self):

729 return f"AccountDeletionToken(token={self.token}, user_id={self.user_id}, created={self.created}, expiry={self.expiry})"

730

731

732class UserSession(Base):

733 """

734 API keys/session cookies for the app

735

736 There are two types of sessions: long-lived, and short-lived. Long-lived are

737 like when you choose "remember this browser": they will be valid for a long

738 time without the user interacting with the site. Short-lived sessions on the

739 other hand get invalidated quickly if the user does not interact with the

740 site.

741

742 Long-lived tokens are valid from `created` until `expiry`.

743

744 Short-lived tokens expire after 168 hours (7 days) if they are not used.

745 """

746

747 __tablename__ = "sessions"

748 token = Column(String, primary_key=True)

749

750 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

751

752 # sessions are either "api keys" or "session cookies", otherwise identical

753 # an api key is put in the authorization header (e.g. "authorization: Bearer <token>")

754 # a session cookie is set in the "couchers-sesh" cookie (e.g. "cookie: couchers-sesh=<token>")

755 # when a session is created, it's fixed as one or the other for security reasons

756 # for api keys to be useful, they should be long lived and have a long expiry

757 is_api_key = Column(Boolean, nullable=False, server_default=text("false"))

758

759 # whether it's a long-lived or short-lived session

760 long_lived = Column(Boolean, nullable=False)

761

762 # the time at which the session was created

763 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

764

765 # the expiry of the session: the session *cannot* be refreshed past this

766 expiry = Column(DateTime(timezone=True), nullable=False, server_default=func.now() + text("interval '90 days'"))

767

768 # the time at which the token was invalidated, allows users to delete sessions

769 deleted = Column(DateTime(timezone=True), nullable=True, default=None)

770

771 # the last time this session was used

772 last_seen = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

773

774 # count of api calls made with this token/session (if we're updating last_seen, might as well update this too)

775 api_calls = Column(Integer, nullable=False, default=0)

776

777 # details of the browser, if available

778 # these are from the request creating the session, not used for anything else

779 ip_address = Column(String, nullable=True)

780 user_agent = Column(String, nullable=True)

781

782 user = relationship("User", backref="sessions")

783

784 @hybrid_property

785 def is_valid(self):

786 """

787 It must have been created and not be expired or deleted.

788

789 Also, if it's a short lived token, it must have been used in the last 168 hours.

790

791 TODO: this probably won't run in python (instance level), only in sql (class level)

792 """

793 return (

794 (self.created <= func.now())

795 & (self.expiry >= func.now())

796 & (self.deleted == None)

797 & (self.long_lived | (func.now() - self.last_seen < text("interval '168 hours'")))

798 )

799

800

801class Conversation(Base):

802 """

803 Conversation brings together the different types of message/conversation types

804 """

805

806 __tablename__ = "conversations"

807

808 id = Column(BigInteger, primary_key=True)

809 # timezone should always be UTC

810 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

811

812 def __repr__(self):

813 return f"Conversation(id={self.id}, created={self.created})"

814

815

816class GroupChat(Base):

817 """

818 Group chat

819 """

820

821 __tablename__ = "group_chats"

822

823 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)

824

825 title = Column(String, nullable=True)

826 only_admins_invite = Column(Boolean, nullable=False, default=True)

827 creator_id = Column(ForeignKey("users.id"), nullable=False, index=True)

828 is_dm = Column(Boolean, nullable=False)

829

830 conversation = relationship("Conversation", backref="group_chat")

831 creator = relationship("User", backref="created_group_chats")

832

833 def __repr__(self):

834 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})"

835

836

837class GroupChatRole(enum.Enum):

838 admin = enum.auto()

839 participant = enum.auto()

840

841

842class GroupChatSubscription(Base):

843 """

844 The recipient of a thread and information about when they joined/left/etc.

845 """

846

847 __tablename__ = "group_chat_subscriptions"

848 id = Column(BigInteger, primary_key=True)

849

850 # TODO: DB constraint on only one user+group_chat combo at a given time

851 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

852 group_chat_id = Column(ForeignKey("group_chats.id"), nullable=False, index=True)

853

854 # timezones should always be UTC

855 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

856 left = Column(DateTime(timezone=True), nullable=True)

857

858 role = Column(Enum(GroupChatRole), nullable=False)

859

860 last_seen_message_id = Column(BigInteger, nullable=False, default=0)

861

862 # when this chat is muted until, DATETIME_INFINITY for "forever"

863 muted_until = Column(DateTime(timezone=True), nullable=False, server_default=DATETIME_MINUS_INFINITY.isoformat())

864

865 user = relationship("User", backref="group_chat_subscriptions")

866 group_chat = relationship("GroupChat", backref=backref("subscriptions", lazy="dynamic"))

867

868 def muted_display(self):

869 """

870 Returns (muted, muted_until) display values:

871 1. If not muted, returns (False, None)

872 2. If muted forever, returns (True, None)

873 3. If muted until a given datetime returns (True, dt)

874 """

875 if self.muted_until < now():

876 return (False, None)

877 elif self.muted_until == DATETIME_INFINITY:

878 return (True, None)

879 else:

880 return (True, self.muted_until)

881

882 @hybrid_property

883 def is_muted(self):

884 return self.muted_until > func.now()

885

886 def __repr__(self):

887 return f"GroupChatSubscription(id={self.id}, user={self.user}, joined={self.joined}, left={self.left}, role={self.role}, group_chat={self.group_chat})"

888

889

890class MessageType(enum.Enum):

891 text = enum.auto()

892 # e.g.

893 # image =

894 # emoji =

895 # ...

896 chat_created = enum.auto()

897 chat_edited = enum.auto()

898 user_invited = enum.auto()

899 user_left = enum.auto()

900 user_made_admin = enum.auto()

901 user_removed_admin = enum.auto() # RemoveGroupChatAdmin: remove admin permission from a user in group chat

902 host_request_status_changed = enum.auto()

903 user_removed = enum.auto() # user is removed from group chat by amdin RemoveGroupChatUser

904

905

906class HostRequestStatus(enum.Enum):

907 pending = enum.auto()

908 accepted = enum.auto()

909 rejected = enum.auto()

910 confirmed = enum.auto()

911 cancelled = enum.auto()

912

913

914class Message(Base):

915 """

916 A message.

917

918 If message_type = text, then the message is a normal text message, otherwise, it's a special control message.

919 """

920

921 __tablename__ = "messages"

922

923 id = Column(BigInteger, primary_key=True)

924

925 # which conversation the message belongs in

926 conversation_id = Column(ForeignKey("conversations.id"), nullable=False, index=True)

927

928 # the user that sent the message/command

929 author_id = Column(ForeignKey("users.id"), nullable=False, index=True)

930

931 # the message type, "text" is a text message, otherwise a "control message"

932 message_type = Column(Enum(MessageType), nullable=False)

933

934 # the target if a control message and requires target, e.g. if inviting a user, the user invited is the target

935 target_id = Column(ForeignKey("users.id"), nullable=True, index=True)

936

937 # time sent, timezone should always be UTC

938 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

939

940 # the plain-text message text if not control

941 text = Column(String, nullable=True)

942

943 # the new host request status if the message type is host_request_status_changed

944 host_request_status_target = Column(Enum(HostRequestStatus), nullable=True)

945

946 conversation = relationship("Conversation", backref="messages", order_by="Message.time.desc()")

947 author = relationship("User", foreign_keys="Message.author_id")

948 target = relationship("User", foreign_keys="Message.target_id")

949

950 @property

951 def is_normal_message(self):

952 """

953 There's only one normal type atm, text

954 """

955 return self.message_type == MessageType.text

956

957 def __repr__(self):

958 return f"Message(id={self.id}, time={self.time}, text={self.text}, author={self.author}, conversation={self.conversation})"

959

960

961class ContentReport(Base):

962 """

963 A piece of content reported to admins

964 """

965

966 __tablename__ = "content_reports"

967

968 id = Column(BigInteger, primary_key=True)

969

970 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

971

972 # the user who reported or flagged the content

973 reporting_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

974

975 # reason, e.g. spam, inappropriate, etc

976 reason = Column(String, nullable=False)

977 # a short description

978 description = Column(String, nullable=False)

979

980 # a reference to the content, see //docs/content_ref.md

981 content_ref = Column(String, nullable=False)

982 # the author of the content (e.g. the user who wrote the comment itself)

983 author_user_id = Column(ForeignKey("users.id"), nullable=False)

984

985 # details of the browser, if available

986 user_agent = Column(String, nullable=False)

987 # the URL the user was on when reporting the content

988 page = Column(String, nullable=False)

989

990 # see comments above for reporting vs author

991 reporting_user = relationship("User", foreign_keys="ContentReport.reporting_user_id")

992 author_user = relationship("User", foreign_keys="ContentReport.author_user_id")

993

994

995class Email(Base):

996 """

997 Table of all dispatched emails for debugging purposes, etc.

998 """

999

1000 __tablename__ = "emails"

1001

1002 id = Column(String, primary_key=True)

1003

1004 # timezone should always be UTC

1005 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1006

1007 sender_name = Column(String, nullable=False)

1008 sender_email = Column(String, nullable=False)

1009

1010 recipient = Column(String, nullable=False)

1011 subject = Column(String, nullable=False)

1012

1013 plain = Column(String, nullable=False)

1014 html = Column(String, nullable=False)

1015

1016

1017class SMS(Base):

1018 """

1019 Table of all sent SMSs for debugging purposes, etc.

1020 """

1021

1022 __tablename__ = "smss"

1023

1024 id = Column(BigInteger, primary_key=True)

1025

1026 # timezone should always be UTC

1027 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1028 # AWS message id

1029 message_id = Column(String, nullable=False)

1030

1031 # the SMS sender ID sent to AWS, name that the SMS appears to come from

1032 sms_sender_id = Column(String, nullable=False)

1033 number = Column(String, nullable=False)

1034 message = Column(String, nullable=False)

1035

1036

1037class HostRequest(Base):

1038 """

1039 A request to stay with a host

1040 """

1041

1042 __tablename__ = "host_requests"

1043

1044 conversation_id = Column("id", ForeignKey("conversations.id"), nullable=False, primary_key=True)

1045 surfer_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1046 host_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1047

1048 # TODO: proper timezone handling

1049 timezone = "Etc/UTC"

1050

1051 # dates in the timezone above

1052 from_date = Column(Date, nullable=False)

1053 to_date = Column(Date, nullable=False)

1054

1055 # timezone aware start and end times of the request, can be compared to now()

1056 start_time = column_property(date_in_timezone(from_date, timezone))

1057 end_time = column_property(date_in_timezone(to_date, timezone) + text("interval '1 days'"))

1058 start_time_to_write_reference = column_property(date_in_timezone(to_date, timezone))

1059 # notice 1 day for midnight at the *end of the day*, then 14 days to write a ref

1060 end_time_to_write_reference = column_property(date_in_timezone(to_date, timezone) + text("interval '15 days'"))

1061

1062 status = Column(Enum(HostRequestStatus), nullable=False)

1063

1064 host_last_seen_message_id = Column(BigInteger, nullable=False, default=0)

1065 surfer_last_seen_message_id = Column(BigInteger, nullable=False, default=0)

1066

1067 # number of reference reminders sent out

1068 host_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))

1069 surfer_sent_reference_reminders = Column(BigInteger, nullable=False, server_default=text("0"))

1070

1071 surfer = relationship("User", backref="host_requests_sent", foreign_keys="HostRequest.surfer_user_id")

1072 host = relationship("User", backref="host_requests_received", foreign_keys="HostRequest.host_user_id")

1073 conversation = relationship("Conversation")

1074

1075 @hybrid_property

1076 def can_write_reference(self):

1077 return (

1078 ((self.status == HostRequestStatus.confirmed) | (self.status == HostRequestStatus.accepted))

1079 & (now() >= self.start_time_to_write_reference)

1080 & (now() <= self.end_time_to_write_reference)

1081 )

1082

1083 @can_write_reference.expression

1084 def can_write_reference(cls):

1085 return (

1086 ((cls.status == HostRequestStatus.confirmed) | (cls.status == HostRequestStatus.accepted))

1087 & (func.now() >= cls.start_time_to_write_reference)

1088 & (func.now() <= cls.end_time_to_write_reference)

1089 )

1090

1091 def __repr__(self):

1092 return (

1093 f"HostRequest(id={self.conversation_id}, from_user_id={self.from_user_id}, to_user_id={self.to_user_id}...)"

1094 )

1095

1096

1097class ReferenceType(enum.Enum):

1098 friend = enum.auto()

1099 surfed = enum.auto() # The "from" user surfed with the "to" user

1100 hosted = enum.auto() # The "from" user hosted the "to" user

1101

1102

1103class Reference(Base):

1104 """

1105 Reference from one user to another

1106 """

1107

1108 __tablename__ = "references"

1109

1110 id = Column(BigInteger, primary_key=True)

1111 # timezone should always be UTC

1112 time = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1113

1114 from_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1115 to_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1116

1117 reference_type = Column(Enum(ReferenceType), nullable=False)

1118

1119 host_request_id = Column(ForeignKey("host_requests.id"), nullable=True)

1120

1121 text = Column(String, nullable=False) # plain text

1122 # text that's only visible to mods

1123 private_text = Column(String, nullable=True) # plain text

1124

1125 rating = Column(Float, nullable=False)

1126 was_appropriate = Column(Boolean, nullable=False)

1127

1128 from_user = relationship("User", backref="references_from", foreign_keys="Reference.from_user_id")

1129 to_user = relationship("User", backref="references_to", foreign_keys="Reference.to_user_id")

1130

1131 host_request = relationship("HostRequest", backref="references")

1132

1133 __table_args__ = (

1134 # Rating must be between 0 and 1, inclusive

1135 CheckConstraint(

1136 "rating BETWEEN 0 AND 1",

1137 name="rating_between_0_and_1",

1138 ),

1139 # Has host_request_id or it's a friend reference

1140 CheckConstraint(

1141 "(host_request_id IS NOT NULL) <> (reference_type = 'friend')",

1142 name="host_request_id_xor_friend_reference",

1143 ),

1144 # Each user can leave at most one friend reference to another user

1145 Index(

1146 "ix_references_unique_friend_reference",

1147 from_user_id,

1148 to_user_id,

1149 reference_type,

1150 unique=True,

1151 postgresql_where=(reference_type == ReferenceType.friend),

1152 ),

1153 # Each user can leave at most one reference to another user for each stay

1154 Index(

1155 "ix_references_unique_per_host_request",

1156 from_user_id,

1157 to_user_id,

1158 host_request_id,

1159 unique=True,

1160 postgresql_where=(host_request_id != None),

1161 ),

1162 )

1163

1164 @property

1165 def should_report(self):

1166 """

1167 If this evaluates to true, we send a report to the moderation team.

1168 """

1169 return self.rating <= 0.4 or not self.was_appropriate or self.private_text

1170

1171

1172class InitiatedUpload(Base):

1173 """

1174 Started downloads, not necessarily complete yet.

1175 """

1176

1177 __tablename__ = "initiated_uploads"

1178

1179 key = Column(String, primary_key=True)

1180

1181 # timezones should always be UTC

1182 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1183 expiry = Column(DateTime(timezone=True), nullable=False)

1184

1185 initiator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1186

1187 initiator_user = relationship("User")

1188

1189 @hybrid_property

1190 def is_valid(self):

1191 return (self.created <= func.now()) & (self.expiry >= func.now())

1192

1193

1194class Upload(Base):

1195 """

1196 Completed uploads.

1197 """

1198

1199 __tablename__ = "uploads"

1200 key = Column(String, primary_key=True)

1201

1202 filename = Column(String, nullable=False)

1203 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1204 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1205

1206 # photo credit, etc

1207 credit = Column(String, nullable=True)

1208

1209 creator_user = relationship("User", backref="uploads", foreign_keys="Upload.creator_user_id")

1210

1211 def _url(self, size):

1212 return f"{config['MEDIA_SERVER_BASE_URL']}/img/{size}/{self.filename}"

1213

1214 @property

1215 def thumbnail_url(self):

1216 return self._url("thumbnail")

1217

1218 @property

1219 def full_url(self):

1220 return self._url("full")

1221

1222

1223communities_seq = Sequence("communities_seq")

1224

1225

1226class Node(Base):

1227 """

1228 Node, i.e. geographical subdivision of the world

1229

1230 Administered by the official cluster

1231 """

1232

1233 __tablename__ = "nodes"

1234

1235 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())

1236

1237 # name and description come from official cluster

1238 parent_node_id = Column(ForeignKey("nodes.id"), nullable=True, index=True)

1239 geom = deferred(Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False))

1240 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1241

1242 parent_node = relationship("Node", backref="child_nodes", remote_side="Node.id")

1243

1244 contained_users = relationship(

1245 "User",

1246 primaryjoin="func.ST_Contains(foreign(Node.geom), User.geom).as_comparison(1, 2)",

1247 viewonly=True,

1248 uselist=True,

1249 )

1250

1251 contained_user_ids = association_proxy("contained_users", "id")

1252

1253

1254class Cluster(Base):

1255 """

1256 Cluster, administered grouping of content

1257 """

1258

1259 __tablename__ = "clusters"

1260

1261 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())

1262 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)

1263 name = Column(String, nullable=False)

1264 # short description

1265 description = Column(String, nullable=False)

1266 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1267

1268 is_official_cluster = Column(Boolean, nullable=False, default=False)

1269

1270 slug = column_property(func.slugify(name))

1271

1272 official_cluster_for_node = relationship(

1273 "Node",

1274 primaryjoin="and_(Cluster.parent_node_id == Node.id, Cluster.is_official_cluster)",

1275 backref=backref("official_cluster", uselist=False),

1276 uselist=False,

1277 viewonly=True,

1278 )

1279

1280 parent_node = relationship(

1281 "Node", backref="child_clusters", remote_side="Node.id", foreign_keys="Cluster.parent_node_id"

1282 )

1283

1284 nodes = relationship("Cluster", backref="clusters", secondary="node_cluster_associations", viewonly=True)

1285 # all pages

1286 pages = relationship(

1287 "Page", backref="clusters", secondary="cluster_page_associations", lazy="dynamic", viewonly=True

1288 )

1289 events = relationship("Event", backref="clusters", secondary="cluster_event_associations", viewonly=True)

1290 discussions = relationship(

1291 "Discussion", backref="clusters", secondary="cluster_discussion_associations", viewonly=True

1292 )

1293

1294 # includes also admins

1295 members = relationship(

1296 "User",

1297 lazy="dynamic",

1298 backref="cluster_memberships",

1299 secondary="cluster_subscriptions",

1300 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",

1301 secondaryjoin="User.id == ClusterSubscription.user_id",

1302 viewonly=True,

1303 )

1304

1305 admins = relationship(

1306 "User",

1307 lazy="dynamic",

1308 backref="cluster_adminships",

1309 secondary="cluster_subscriptions",

1310 primaryjoin="Cluster.id == ClusterSubscription.cluster_id",

1311 secondaryjoin="and_(User.id == ClusterSubscription.user_id, ClusterSubscription.role == 'admin')",

1312 viewonly=True,

1313 )

1314

1315 main_page = relationship(

1316 "Page",

1317 primaryjoin="and_(Cluster.id == Page.owner_cluster_id, Page.type == 'main_page')",

1318 viewonly=True,

1319 uselist=False,

1320 )

1321

1322 __table_args__ = (

1323 # Each node can have at most one official cluster

1324 Index(

1325 "ix_clusters_owner_parent_node_id_is_official_cluster",

1326 parent_node_id,

1327 is_official_cluster,

1328 unique=True,

1329 postgresql_where=is_official_cluster,

1330 ),

1331 )

1332

1333

1334class NodeClusterAssociation(Base):

1335 """

1336 NodeClusterAssociation, grouping of nodes

1337 """

1338

1339 __tablename__ = "node_cluster_associations"

1340 __table_args__ = (UniqueConstraint("node_id", "cluster_id"),)

1341

1342 id = Column(BigInteger, primary_key=True)

1343

1344 node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)

1345 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)

1346

1347 node = relationship("Node", backref="node_cluster_associations")

1348 cluster = relationship("Cluster", backref="node_cluster_associations")

1349

1350

1351class ClusterRole(enum.Enum):

1352 member = enum.auto()

1353 admin = enum.auto()

1354

1355

1356class ClusterSubscription(Base):

1357 """

1358 ClusterSubscription of a user

1359 """

1360

1361 __tablename__ = "cluster_subscriptions"

1362 __table_args__ = (UniqueConstraint("user_id", "cluster_id"),)

1363

1364 id = Column(BigInteger, primary_key=True)

1365

1366 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1367 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)

1368 role = Column(Enum(ClusterRole), nullable=False)

1369

1370 user = relationship("User", backref="cluster_subscriptions")

1371 cluster = relationship("Cluster", backref="cluster_subscriptions")

1372

1373

1374class ClusterPageAssociation(Base):

1375 """

1376 pages related to clusters

1377 """

1378

1379 __tablename__ = "cluster_page_associations"

1380 __table_args__ = (UniqueConstraint("page_id", "cluster_id"),)

1381

1382 id = Column(BigInteger, primary_key=True)

1383

1384 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)

1385 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)

1386

1387 page = relationship("Page", backref="cluster_page_associations")

1388 cluster = relationship("Cluster", backref="cluster_page_associations")

1389

1390

1391class PageType(enum.Enum):

1392 main_page = enum.auto()

1393 place = enum.auto()

1394 guide = enum.auto()

1395

1396

1397class Page(Base):

1398 """

1399 similar to a wiki page about a community, POI or guide

1400 """

1401

1402 __tablename__ = "pages"

1403

1404 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())

1405

1406 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)

1407 type = Column(Enum(PageType), nullable=False)

1408 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1409 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)

1410 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)

1411

1412 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)

1413

1414 parent_node = relationship("Node", backref="child_pages", remote_side="Node.id", foreign_keys="Page.parent_node_id")

1415

1416 thread = relationship("Thread", backref="page", uselist=False)

1417 creator_user = relationship("User", backref="created_pages", foreign_keys="Page.creator_user_id")

1418 owner_user = relationship("User", backref="owned_pages", foreign_keys="Page.owner_user_id")

1419 owner_cluster = relationship(

1420 "Cluster", backref=backref("owned_pages", lazy="dynamic"), uselist=False, foreign_keys="Page.owner_cluster_id"

1421 )

1422

1423 editors = relationship("User", secondary="page_versions", viewonly=True)

1424

1425 __table_args__ = (

1426 # Only one of owner_user and owner_cluster should be set

1427 CheckConstraint(

1428 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",

1429 name="one_owner",

1430 ),

1431 # Only clusters can own main pages

1432 CheckConstraint(

1433 "NOT (owner_cluster_id IS NULL AND type = 'main_page')",

1434 name="main_page_owned_by_cluster",

1435 ),

1436 # Each cluster can have at most one main page

1437 Index(

1438 "ix_pages_owner_cluster_id_type",

1439 owner_cluster_id,

1440 type,

1441 unique=True,

1442 postgresql_where=(type == PageType.main_page),

1443 ),

1444 )

1445

1446 def __repr__(self):

1447 return f"Page({self.id=})"

1448

1449

1450class PageVersion(Base):

1451 """

1452 version of page content

1453 """

1454

1455 __tablename__ = "page_versions"

1456

1457 id = Column(BigInteger, primary_key=True)

1458

1459 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True)

1460 editor_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1461 title = Column(String, nullable=False)

1462 content = Column(String, nullable=False) # CommonMark without images

1463 photo_key = Column(ForeignKey("uploads.key"), nullable=True)

1464 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)

1465 # the human-readable address

1466 address = Column(String, nullable=True)

1467 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1468

1469 slug = column_property(func.slugify(title))

1470

1471 page = relationship("Page", backref="versions", order_by="PageVersion.id")

1472 editor_user = relationship("User", backref="edited_pages")

1473 photo = relationship("Upload")

1474

1475 __table_args__ = (

1476 # Geom and address must either both be null or both be set

1477 CheckConstraint(

1478 "(geom IS NULL) = (address IS NULL)",

1479 name="geom_iff_address",

1480 ),

1481 )

1482

1483 @property

1484 def coordinates(self):

1485 # returns (lat, lng) or None

1486 if self.geom:

1487 return get_coordinates(self.geom)

1488 else:

1489 return None

1490

1491 def __repr__(self):

1492 return f"PageVersion({self.id=}, {self.page_id=})"

1493

1494

1495class ClusterEventAssociation(Base):

1496 """

1497 events related to clusters

1498 """

1499

1500 __tablename__ = "cluster_event_associations"

1501 __table_args__ = (UniqueConstraint("event_id", "cluster_id"),)

1502

1503 id = Column(BigInteger, primary_key=True)

1504

1505 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)

1506 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)

1507

1508 event = relationship("Event", backref="cluster_event_associations")

1509 cluster = relationship("Cluster", backref="cluster_event_associations")

1510

1511

1512class Event(Base):

1513 """

1514 An event is compose of two parts:

1515

1516 * An event template (Event)

1517 * An occurrence (EventOccurrence)

1518

1519 One-off events will have one of each; repeating events will have one Event,

1520 multiple EventOccurrences, one for each time the event happens.

1521 """

1522

1523 __tablename__ = "events"

1524

1525 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())

1526 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True)

1527

1528 title = Column(String, nullable=False)

1529

1530 slug = column_property(func.slugify(title))

1531

1532 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1533 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1534 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True)

1535 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True)

1536 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)

1537

1538 parent_node = relationship(

1539 "Node", backref="child_events", remote_side="Node.id", foreign_keys="Event.parent_node_id"

1540 )

1541 thread = relationship("Thread", backref="event", uselist=False)

1542 subscribers = relationship(

1543 "User", backref="subscribed_events", secondary="event_subscriptions", lazy="dynamic", viewonly=True

1544 )

1545 organizers = relationship(

1546 "User", backref="organized_events", secondary="event_organizers", lazy="dynamic", viewonly=True

1547 )

1548 thread = relationship("Thread", backref="event", uselist=False)

1549 creator_user = relationship("User", backref="created_events", foreign_keys="Event.creator_user_id")

1550 owner_user = relationship("User", backref="owned_events", foreign_keys="Event.owner_user_id")

1551 owner_cluster = relationship(

1552 "Cluster",

1553 backref=backref("owned_events", lazy="dynamic"),

1554 uselist=False,

1555 foreign_keys="Event.owner_cluster_id",

1556 )

1557

1558 __table_args__ = (

1559 # Only one of owner_user and owner_cluster should be set

1560 CheckConstraint(

1561 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",

1562 name="one_owner",

1563 ),

1564 )

1565

1566

1567class EventOccurrence(Base):

1568 __tablename__ = "event_occurrences"

1569

1570 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())

1571 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)

1572

1573 # the user that created this particular occurrence of a repeating event (same as event.creator_user_id if single event)

1574 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1575 content = Column(String, nullable=False) # CommonMark without images

1576 photo_key = Column(ForeignKey("uploads.key"), nullable=True)

1577

1578 # a null geom is an online-only event

1579 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True)

1580 # physical address, iff geom is not null

1581 address = Column(String, nullable=True)

1582 # videoconferencing link, etc, must be specified if no geom, otherwise optional

1583 link = Column(String, nullable=True)

1584

1585 timezone = "Etc/UTC"

1586

1587 # time during which the event takes place; this is a range type (instead of separate start+end times) which

1588 # simplifies database constraints, etc

1589 during = Column(TSTZRANGE, nullable=False)

1590

1591 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1592 last_edited = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1593

1594 creator_user = relationship(

1595 "User", backref="created_event_occurrences", foreign_keys="EventOccurrence.creator_user_id"

1596 )

1597 event = relationship(

1598 "Event",

1599 backref=backref("occurrences", lazy="dynamic"),

1600 remote_side="Event.id",

1601 foreign_keys="EventOccurrence.event_id",

1602 )

1603

1604 photo = relationship("Upload")

1605

1606 __table_args__ = (

1607 # Geom and address go together

1608 CheckConstraint(

1609 # geom and address are either both null or neither of them are null

1610 "(geom IS NULL) = (address IS NULL)",

1611 name="geom_iff_address",

1612 ),

1613 # Online-only events need a link, note that online events may also have a link

1614 CheckConstraint(

1615 # exactly oen of geom or link is non-null

1616 "(geom IS NULL) <> (link IS NULL)",

1617 name="link_or_geom",

1618 ),

1619 # Can't have overlapping occurrences in the same Event

1620 ExcludeConstraint(("event_id", "="), ("during", "&&"), name="event_occurrences_event_id_during_excl"),

1621 )

1622

1623 @property

1624 def coordinates(self):

1625 # returns (lat, lng) or None

1626 if self.geom:

1627 return get_coordinates(self.geom)

1628 else:

1629 return None

1630

1631 @hybrid_property

1632 def start_time(self):

1633 return self.during.lower

1634

1635 @start_time.expression

1636 def start_time(cls):

1637 return func.lower(cls.during)

1638

1639 @hybrid_property

1640 def end_time(self):

1641 return self.during.upper

1642

1643 @end_time.expression

1644 def end_time(cls):

1645 return func.upper(cls.during)

1646

1647

1648class EventSubscription(Base):

1649 """

1650 users subscriptions to events

1651 """

1652

1653 __tablename__ = "event_subscriptions"

1654 __table_args__ = (UniqueConstraint("event_id", "user_id"),)

1655

1656 id = Column(BigInteger, primary_key=True)

1657

1658 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1659 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)

1660 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1661

1662 user = relationship("User")

1663 event = relationship("Event")

1664

1665

1666class EventOrganizer(Base):

1667 """

1668 Organizers for events

1669 """

1670

1671 __tablename__ = "event_organizers"

1672 __table_args__ = (UniqueConstraint("event_id", "user_id"),)

1673

1674 id = Column(BigInteger, primary_key=True)

1675

1676 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1677 event_id = Column(ForeignKey("events.id"), nullable=False, index=True)

1678 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1679

1680 user = relationship("User")

1681 event = relationship("Event")

1682

1683

1684class AttendeeStatus(enum.Enum):

1685 going = enum.auto()

1686 maybe = enum.auto()

1687

1688

1689class EventOccurrenceAttendee(Base):

1690 """

1691 Attendees for events

1692 """

1693

1694 __tablename__ = "event_occurrence_attendees"

1695 __table_args__ = (UniqueConstraint("occurrence_id", "user_id"),)

1696

1697 id = Column(BigInteger, primary_key=True)

1698

1699 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1700 occurrence_id = Column(ForeignKey("event_occurrences.id"), nullable=False, index=True)

1701 responded = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1702 attendee_status = Column(Enum(AttendeeStatus), nullable=False)

1703

1704 user = relationship("User")

1705 occurrence = relationship("EventOccurrence", backref=backref("attendees", lazy="dynamic"))

1706

1707

1708class ClusterDiscussionAssociation(Base):

1709 """

1710 discussions related to clusters

1711 """

1712

1713 __tablename__ = "cluster_discussion_associations"

1714 __table_args__ = (UniqueConstraint("discussion_id", "cluster_id"),)

1715

1716 id = Column(BigInteger, primary_key=True)

1717

1718 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)

1719 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)

1720

1721 discussion = relationship("Discussion", backref="cluster_discussion_associations")

1722 cluster = relationship("Cluster", backref="cluster_discussion_associations")

1723

1724

1725class Discussion(Base):

1726 """

1727 forum board

1728 """

1729

1730 __tablename__ = "discussions"

1731

1732 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value())

1733

1734 title = Column(String, nullable=False)

1735 content = Column(String, nullable=False)

1736 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True)

1737 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1738

1739 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1740 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True)

1741

1742 slug = column_property(func.slugify(title))

1743

1744 thread = relationship("Thread", backref="discussion", uselist=False)

1745

1746 subscribers = relationship("User", backref="discussions", secondary="discussion_subscriptions", viewonly=True)

1747

1748 creator_user = relationship("User", backref="created_discussions", foreign_keys="Discussion.creator_user_id")

1749 owner_cluster = relationship("Cluster", backref=backref("owned_discussions", lazy="dynamic"), uselist=False)

1750

1751

1752class DiscussionSubscription(Base):

1753 """

1754 users subscriptions to discussions

1755 """

1756

1757 __tablename__ = "discussion_subscriptions"

1758 __table_args__ = (UniqueConstraint("discussion_id", "user_id"),)

1759

1760 id = Column(BigInteger, primary_key=True)

1761

1762 user_id = Column(ForeignKey("users.id"), nullable=False, index=True)

1763 discussion_id = Column(ForeignKey("discussions.id"), nullable=False, index=True)

1764 joined = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1765 left = Column(DateTime(timezone=True), nullable=True)

1766

1767 user = relationship("User", backref="discussion_subscriptions")

1768 discussion = relationship("Discussion", backref="discussion_subscriptions")

1769

1770

1771class Thread(Base):

1772 """

1773 Thread

1774 """

1775

1776 __tablename__ = "threads"

1777

1778 id = Column(BigInteger, primary_key=True)

1779

1780 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1781 deleted = Column(DateTime(timezone=True), nullable=True)

1782

1783

1784class Comment(Base):

1785 """

1786 Comment

1787 """

1788

1789 __tablename__ = "comments"

1790

1791 id = Column(BigInteger, primary_key=True)

1792

1793 thread_id = Column(ForeignKey("threads.id"), nullable=False, index=True)

1794 author_user_id = Column(ForeignKey("users.id"), nullable=False)

1795 content = Column(String, nullable=False) # CommonMark without images

1796 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1797 deleted = Column(DateTime(timezone=True), nullable=True)

1798

1799 thread = relationship("Thread", backref="comments")

1800

1801

1802class Reply(Base):

1803 """

1804 Reply

1805 """

1806

1807 __tablename__ = "replies"

1808

1809 id = Column(BigInteger, primary_key=True)

1810

1811 comment_id = Column(ForeignKey("comments.id"), nullable=False, index=True)

1812 author_user_id = Column(ForeignKey("users.id"), nullable=False)

1813 content = Column(String, nullable=False) # CommonMark without images

1814 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1815 deleted = Column(DateTime(timezone=True), nullable=True)

1816

1817 comment = relationship("Comment", backref="replies")

1818

1819

1820class BackgroundJobType(enum.Enum):

1821 # payload: jobs.SendEmailPayload

1822 send_email = enum.auto()

1823 # payload: google.protobuf.Empty

1824 purge_login_tokens = enum.auto()

1825 # payload: google.protobuf.Empty

1826 purge_signup_tokens = enum.auto()

1827 # payload: google.protobuf.Empty

1828 purge_account_deletion_tokens = enum.auto()

1829 # payload: google.protobuf.Empty

1830 purge_password_reset_tokens = enum.auto()

1831 # payload: google.protobuf.Empty

1832 send_message_notifications = enum.auto()

1833 # payload: google.protobuf.Empty

1834 send_onboarding_emails = enum.auto()

1835 # payload: google.protobuf.Empty

1836 add_users_to_email_list = enum.auto()

1837 # payload: google.protobuf.Empty

1838 send_request_notifications = enum.auto()

1839 # payload: google.protobuf.Empty

1840 enforce_community_membership = enum.auto()

1841 # payload: google.protobuf.Empty

1842 send_reference_reminders = enum.auto()

1843 # payload: jobs.HandleNotificationPayload

1844 handle_notification = enum.auto()

1845 # payload: google.protobuf.Empty

1846 handle_email_notifications = enum.auto()

1847 # payload: google.protobuf.Empty

1848 handle_email_digests = enum.auto()

1849 # payload: jobs.GenerateMessageNotificationsPayload

1850 generate_message_notifications = enum.auto()

1851 # payload: google.protobuf.Empty

1852 update_recommendation_scores = enum.auto()

1853 # payload: google.protobuf.Empty

1854 refresh_materialized_views = enum.auto()

1855

1856

1857class BackgroundJobState(enum.Enum):

1858 # job is fresh, waiting to be picked off the queue

1859 pending = enum.auto()

1860 # job complete

1861 completed = enum.auto()

1862 # error occured, will be retried

1863 error = enum.auto()

1864 # failed too many times, not retrying anymore

1865 failed = enum.auto()

1866

1867

1868class BackgroundJob(Base):

1869 """

1870 This table implements a queue of background jobs.

1871 """

1872

1873 __tablename__ = "background_jobs"

1874

1875 id = Column(BigInteger, primary_key=True)

1876

1877 # used to discern which function should be triggered to service it

1878 job_type = Column(Enum(BackgroundJobType), nullable=False)

1879 state = Column(Enum(BackgroundJobState), nullable=False, default=BackgroundJobState.pending)

1880

1881 # time queued

1882 queued = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1883

1884 # time at which we may next attempt it, for implementing exponential backoff

1885 next_attempt_after = Column(DateTime(timezone=True), nullable=False, server_default=func.now())

1886

1887 # used to count number of retries for failed jobs

1888 try_count = Column(Integer, nullable=False, default=0)

1889

1890 max_tries = Column(Integer, nullable=False, default=5)

1891

1892 # protobuf encoded job payload

1893 payload = Column(Binary, nullable=False)

1894

1895 # if the job failed, we write that info here

1896 failure_info = Column(String, nullable=True)

1897

1898 __table_args__ = (

1899 # used in looking up background jobs to attempt

1900 # create index on background_jobs(next_attempt_after, (max_tries - try_count)) where state = 'pending' OR state = 'error';

1901 Index(

1902 "ix_background_jobs_lookup",

1903 next_attempt_after,

1904 (max_tries - try_count),

1905 postgresql_where=((state == BackgroundJobState.pending) | (state == BackgroundJobState.error)),

1906 ),

1907 )

1908

1909 @hybrid_property

1910 def ready_for_retry(self):

1911 return (

1912 (self.next_attempt_after <= func.now())

1913 & (self.try_count < self.max_tries)

1914 & ((self.state == BackgroundJobState.pending) | (self.state == BackgroundJobState.error))

1915 )

1916

1917 def __repr__(self):

1918 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})"

1919

1920

1921class NotificationDeliveryType(enum.Enum):

1922 # send push notification to mobile/web

1923 push = enum.auto()

1924 # send individual email immediately

1925 email = enum.auto()

1926 # send in digest

1927 digest = enum.auto()

1928

1929

1930dt = NotificationDeliveryType