class PolymorphicParent(BaseMixin, db.Model): __tablename__ = 'polymorphic_parent' ptype = immutable(db.Column('type', db.Unicode(30), index=True)) is_immutable = immutable(db.Column(db.Unicode(250), default='my_default')) also_immutable = immutable(db.Column(db.Unicode(250))) __mapper_args__ = {'polymorphic_on': ptype, 'polymorphic_identity': 'parent'}
class SMSMessage(BaseMixin, db.Model): __tablename__ = 'sms_message' # Phone number that the message was sent to phone_number = immutable(db.Column(db.String(15), nullable=False)) transactionid = immutable(db.Column(db.UnicodeText, unique=True, nullable=True)) # The message itself message = immutable(db.Column(db.UnicodeText, nullable=False)) # Flags status = db.Column(db.Integer, default=SMS_STATUS.QUEUED, nullable=False) status_at = db.Column(db.TIMESTAMP(timezone=True), nullable=True) fail_reason = db.Column(db.UnicodeText, nullable=True)
class PolymorphicParent(BaseMixin, db.Model): __tablename__ = 'polymorphic_parent' type = immutable(db.Column(db.Unicode(30), index=True)) is_immutable = immutable(db.Column(db.Unicode(250), default='my_default')) also_immutable = immutable(db.Column(db.Unicode(250))) __mapper_args__ = { 'polymorphic_on': type, # The ``type`` column in this model, not the ``type`` builtin 'polymorphic_identity': 'parent' }
class UuidOnly(UuidMixin, BaseMixin, db.Model): __tablename__ = 'uuid_only' __uuid_primary_key__ = True is_regular = db.Column(db.Unicode(250)) is_immutable = immutable(db.deferred(db.Column(db.Unicode(250)))) is_cached = cached(db.Column(db.Unicode(250))) # Make both raw column and relationship immutable referral_target_id = immutable( db.Column(None, db.ForeignKey('referral_target.id'), nullable=True)) referral_target = immutable(db.relationship(ReferralTarget))
class IdOnly(BaseMixin, db.Model): __tablename__ = 'id_only' __uuid_primary_key__ = False is_regular = db.Column(db.Integer) is_immutable = immutable(db.Column(db.Integer)) is_cached = cached(db.Column(db.Integer)) # Make the raw column immutable, but allow changes via the relationship referral_target_id = immutable( db.Column(None, db.ForeignKey('referral_target.id'), nullable=True)) referral_target = db.relationship(ReferralTarget)
class SynonymAnnotation(BaseMixin, db.Model): __tablename__ = 'synonym_annotation' col_regular = db.Column(db.UnicodeText()) col_immutable = immutable(db.Column(db.UnicodeText())) # The immutable annotation is ineffective on synonyms as SQLAlchemy does not # honour the `set` event on synonyms syn_to_regular = immutable(db.synonym('col_regular')) # However, the immutable annotation on the target of a synonym will also apply # on the synonym syn_to_immutable = db.synonym('col_immutable')
class IdUuid(UuidMixin, BaseMixin, db.Model): __tablename__ = 'id_uuid' __uuid_primary_key__ = False is_regular = db.Column(db.Unicode(250)) is_immutable = immutable(db.Column(db.Unicode(250))) is_cached = cached(db.Column(db.Unicode(250))) # Only block changes via the relationship; raw column remains mutable referral_target_id = db.Column(None, db.ForeignKey('referral_target.id'), nullable=True) referral_target = immutable(db.relationship(ReferralTarget))
class ImmutableMembershipMixin(UuidMixin, BaseMixin): """ Support class for immutable memberships """ __uuid_primary_key__ = True #: List of columns that will be copied into a new row when a membership is amended __data_columns__ = () #: Parent column (override as synonym of 'profile_id' or 'project_id' in the subclasses) parent_id = None #: Start time of membership, ordinarily a mirror of created_at except #: for records created when the member table was added to the database granted_at = immutable( with_roles( db.Column(db.TIMESTAMP(timezone=True), nullable=False, default=db.func.utcnow()), read={'subject', 'editor'}, )) #: End time of membership, ordinarily a mirror of updated_at revoked_at = with_roles( db.Column(db.TIMESTAMP(timezone=True), nullable=True), read={'subject', 'editor'}, ) #: Record type record_type = immutable( with_roles( db.Column( db.Integer, StateManager.check_constraint('record_type', MEMBERSHIP_RECORD_TYPE), default=MEMBERSHIP_RECORD_TYPE.DIRECT_ADD, nullable=False, ), read={'subject', 'editor'}, )) @declared_attr def user_id(cls): return db.Column( None, db.ForeignKey('user.id', ondelete='CASCADE'), nullable=False, index=True, ) @with_roles(read={'subject', 'editor'}, grants={'subject'}) @declared_attr def user(cls): return db.relationship(User, foreign_keys=[cls.user_id]) @declared_attr def revoked_by_id(cls): """Id of user who revoked the membership""" return db.Column(None, db.ForeignKey('user.id', ondelete='SET NULL'), nullable=True) @with_roles(read={'subject'}, grants={'editor'}) @declared_attr def revoked_by(cls): """User who revoked the membership""" return db.relationship(User, foreign_keys=[cls.revoked_by_id]) @declared_attr def granted_by_id(cls): """ Id of user who assigned the membership. This is nullable only for historical data. New records always require a value for granted_by """ return db.Column(None, db.ForeignKey('user.id', ondelete='SET NULL'), nullable=True) @with_roles(read={'subject', 'editor'}, grants={'editor'}) @declared_attr def granted_by(cls): """User who assigned the membership""" return db.relationship(User, foreign_keys=[cls.granted_by_id]) @hybrid_property def is_active(self): return (self.revoked_at is None and self.record_type != MEMBERSHIP_RECORD_TYPE.INVITE) @is_active.expression def is_active(cls): # NOQA: N805 return db.and_(cls.revoked_at.is_(None), cls.record_type != MEMBERSHIP_RECORD_TYPE.INVITE) with_roles(is_active, read={'subject'}) @with_roles(read={'subject', 'editor'}) @hybrid_property def is_invite(self): return self.record_type == MEMBERSHIP_RECORD_TYPE.INVITE @declared_attr def __table_args__(cls): if cls.parent_id is not None: return (db.Index( 'ix_' + cls.__tablename__ + '_active', cls.parent_id.name, 'user_id', unique=True, postgresql_where=db.text('revoked_at IS NULL'), ), ) else: return (db.Index( 'ix_' + cls.__tablename__ + '_active', 'user_id', unique=True, postgresql_where=db.text('revoked_at IS NULL'), ), ) @cached_property def offered_roles(self): """Roles offered by this membership record""" return set() # Subclasses must gate these methods in __roles__ @with_roles(call={'subject', 'editor'}) def revoke(self, actor): if self.revoked_at is not None: raise MembershipRevokedError( "This membership record has already been revoked") self.revoked_at = db.func.utcnow() self.revoked_by = actor @with_roles(call={'editor'}) def replace(self, actor, **roles): if self.revoked_at is not None: raise MembershipRevokedError( "This membership record has already been revoked") if not set(roles.keys()).issubset(self.__data_columns__): raise AttributeError("Unknown role") # Perform sanity check. If nothing changed, just return self has_changes = False if self.record_type == MEMBERSHIP_RECORD_TYPE.INVITE: # If we existing record is an INVITE, this must be an ACCEPT. This is an # acceptable change has_changes = True else: # If it's not an ACCEPT, are the supplied roles different from existing? for column in roles: if roles[column] != getattr(self, column): has_changes = True if not has_changes: # Nothing is changing. This is probably a form submit with no changes. # Do nothing and return self return self # An actual change? Revoke this record and make a new record self.revoked_at = db.func.utcnow() self.revoked_by = actor new = type(self)(user=self.user, parent_id=self.parent_id, granted_by=self.granted_by) # if existing record type is INVITE, replace it with ACCEPT, # else replace it with AMEND if self.record_type == MEMBERSHIP_RECORD_TYPE.INVITE: new.record_type = MEMBERSHIP_RECORD_TYPE.ACCEPT else: new.record_type = MEMBERSHIP_RECORD_TYPE.AMEND for column in self.__data_columns__: if column in roles: setattr(new, column, roles[column]) else: setattr(new, column, getattr(self, column)) db.session.add(new) return new @with_roles(call={'subject'}) def accept(self, actor): if self.record_type != MEMBERSHIP_RECORD_TYPE.INVITE: raise MembershipRecordTypeError( "This membership record is not an invite") return self.replace(actor)
class ProposalMembership(ImmutableMembershipMixin, db.Model): """ Users can be presenters or reviewers on proposals. """ __tablename__ = 'proposal_membership' # List of is_role columns in this model __data_columns__ = ('is_reviewer', 'is_presenter') __roles__ = { 'all': { 'read': {'urls', 'user', 'is_reviewer', 'is_presenter', 'proposal'} } } __datasets__ = { 'primary': { 'urls', 'uuid_b58', 'offered_roles', 'is_reviewer', 'is_presenter', 'user', 'proposal', }, 'without_parent': { 'urls', 'uuid_b58', 'offered_roles', 'is_reviewer', 'is_presenter', 'user', }, 'related': {'urls', 'uuid_b58', 'offered_roles', 'is_reviewer', 'is_presenter'}, } proposal_id = immutable( db.Column(None, db.ForeignKey('proposal.id', ondelete='CASCADE'), nullable=False)) proposal = immutable( db.relationship( Proposal, backref=db.backref('memberships', lazy='dynamic', cascade='all', passive_deletes=True), )) parent = immutable(db.synonym('proposal')) parent_id = immutable(db.synonym('proposal_id')) # Proposal roles (at least one must be True): #: Reviewers can change state of proposal is_reviewer = db.Column(db.Boolean, nullable=False, default=False) #: Presenters can edit and withdraw proposals is_presenter = db.Column(db.Boolean, nullable=False, default=False) @declared_attr def __table_args__(cls): args = list(super().__table_args__) args.append( db.CheckConstraint( db.or_(cls.is_reviewer.is_(True), cls.is_presenter.is_(True)), name='proposal_membership_has_role', )) return tuple(args) @cached_property def offered_roles(self): """Roles offered by this membership record""" roles = set() if self.is_reviewer: roles.add('reviewer') elif self.is_presenter: roles.add('presenter') return roles
class OrganizationMembership(ImmutableMembershipMixin, db.Model): """ A user can be an administrator of an organization and optionally an owner. Owners can manage other administrators. This model may introduce non-admin memberships in a future iteration by replacing :attr:`is_owner` with :attr:`member_level` or distinct role flags as in :class:`ProjectMembership`. """ __tablename__ = 'organization_membership' # List of role columns in this model __data_columns__ = ('is_owner', ) __roles__ = { 'all': { 'read': {'urls', 'user', 'is_owner', 'organization'} }, 'profile_admin': { 'read': { 'record_type', 'granted_at', 'granted_by', 'revoked_at', 'revoked_by', 'user', 'is_active', 'is_invite', } }, } __datasets__ = { 'primary': { 'urls', 'uuid_b58', 'offered_roles', 'is_owner', 'user', 'organization', }, 'without_parent': {'urls', 'uuid_b58', 'offered_roles', 'is_owner', 'user'}, 'related': {'urls', 'uuid_b58', 'offered_roles', 'is_owner'}, } #: Organization that this membership is being granted on organization_id = immutable( db.Column(None, db.ForeignKey('organization.id', ondelete='CASCADE'), nullable=False)) organization = immutable( with_roles( db.relationship( Organization, backref=db.backref('memberships', lazy='dynamic', cascade='all', passive_deletes=True), ), grants_via={ None: { 'admin': 'profile_admin', 'owner': 'profile_owner' } }, )) parent = immutable(db.synonym('organization')) parent_id = immutable(db.synonym('organization_id')) # Organization roles: is_owner = immutable(db.Column(db.Boolean, nullable=False, default=False)) @cached_property def offered_roles(self): """Roles offered by this membership record""" roles = {'admin'} if self.is_owner: roles.add('owner') return roles
class EmailAddress(BaseMixin, db.Model): """ Represents an email address as a standalone entity, with associated metadata. Prior to this model, email addresses were regarded as properties of other models. Specifically: Proposal.email, Participant.email, User.emails and User.emailclaims, the latter two lists populated using the UserEmail and UserEmailClaim join models. This subordination made it difficult to track ownership of an email address or its reachability (active, bouncing, etc). Having EmailAddress as a standalone model (with incoming foreign keys) provides some sanity: 1. Email addresses are stored with a hash, and always looked up using the hash. This allows the address to be forgotten while preserving the record for metadata. 2. A forgotten address's record can be restored given the correct email address. 3. Addresses can be automatically forgotten when they are no longer referenced. This ability is implemented using the :attr:`emailaddress_refcount_dropping` signal and supporting code in ``views/helpers.py`` and ``jobs/jobs.py``. 4. If there is abuse, an email address can be comprehensively blocked using its canonical representation, which prevents the address from being used even via its ``+sub-address`` variations. 5. Via :class:`EmailAddressMixin`, the UserEmail model can establish ownership of an email address on behalf of a user, placing an automatic block on its use by other users. This mechanism is not limited to users. A future OrgEmail link can establish ownership on behalf of an organization. 6. Upcoming: column-level encryption of the email column, securing SQL dumps. New email addresses must be added using the :meth:`add` or :meth:`add_for` classmethods, depending on whether the email address is linked to an owner or not. """ __tablename__ = 'email_address' #: Backrefs to this model from other models, populated by :class:`EmailAddressMixin` __backrefs__ = set() #: These backrefs claim exclusive use of the email address for their linked owner. #: See :class:`EmailAddressMixin` for implementation detail __exclusive_backrefs__ = set() #: The email address, centrepiece of this model. Case preserving. #: Validated by the :func:`_validate_email` event handler email = db.Column(db.Unicode, nullable=True) #: The domain of the email, stored for quick lookup of related addresses #: Read-only, accessible via the :property:`domain` property _domain = db.Column('domain', db.Unicode, nullable=True, index=True) # email_normalized is defined below #: BLAKE2b 160-bit hash of :property:`email_normalized`. Kept permanently even if email #: is removed. SQLAlchemy type LargeBinary maps to PostgreSQL BYTEA. Despite the #: name, we're only storing 20 bytes blake2b160 = immutable(db.Column(db.LargeBinary, nullable=False, unique=True)) #: BLAKE2b 160-bit hash of :property:`email_canonical`. Kept permanently for blocked #: email detection. Indexed but does not use a unique constraint because a+b@tld and #: a+c@tld are both a@tld canonically. blake2b160_canonical = immutable( db.Column(db.LargeBinary, nullable=False, index=True) ) #: Does this email address work? Records last known delivery state _delivery_state = db.Column( 'delivery_state', db.Integer, StateManager.check_constraint( 'delivery_state', EMAIL_DELIVERY_STATE, name='email_address_delivery_state_check', ), nullable=False, default=EMAIL_DELIVERY_STATE.UNKNOWN, ) delivery_state = StateManager( '_delivery_state', EMAIL_DELIVERY_STATE, doc="Last known delivery state of this email address", ) #: Timestamp of last known delivery state delivery_state_at = db.Column( db.TIMESTAMP(timezone=True), nullable=False, default=db.func.utcnow() ) #: Timestamp of last known recipient activity resulting from sent mail active_at = db.Column(db.TIMESTAMP(timezone=True), nullable=True) #: Is this email address blocked from being used? If so, :attr:`email` should be #: null. Blocks apply to the canonical address (without the +sub-address variation), #: so a test for whether an address is blocked should use blake2b160_canonical to #: load the record. Other records with the same canonical hash _may_ exist without #: setting the flag due to a lack of database-side enforcement _is_blocked = db.Column('is_blocked', db.Boolean, nullable=False, default=False) __table_args__ = ( # `domain` must be lowercase always. Note that Python `.lower()` is not # guaranteed to produce identical output to SQL `lower()` with non-ASCII # characters. It is only safe to use here because domain names are always ASCII db.CheckConstraint( _domain == db.func.lower(_domain), 'email_address_domain_check' ), # If `is_blocked` is True, `email` and `domain` must be None db.CheckConstraint( db.or_( _is_blocked.isnot(True), db.and_(_is_blocked.is_(True), email.is_(None), _domain.is_(None)), ), 'email_address_email_is_blocked_check', ), # `email` and `domain` must be None, or `email.endswith(domain)` must be True. # However, the endswith constraint is relaxed with IDN domains, as there is no # easy way to do an IDN match in Postgres without an extension. # `_` and `%` must be escaped as they are wildcards to the LIKE/ILIKE operator db.CheckConstraint( db.or_( # email and domain must both be non-null, or db.and_(email.is_(None), _domain.is_(None)), # domain must be an IDN, or email.op('SIMILAR TO')('(xn--|%.xn--)%'), # domain is ASCII (typical case) and must be the suffix of email email.ilike( '%' + db.func.replace(db.func.replace(_domain, '_', r'\_'), '%', r'\%') ), ), 'email_address_email_domain_check', ), ) @hybrid_property def is_blocked(self) -> bool: """ Read-only flag indicating this email address is blocked from use. To set this flag, call :classmethod:`mark_blocked` using the email address. The flag will be simultaneously set on all matching instances. """ return self._is_blocked @hybrid_property def domain(self) -> Optional[str]: """The domain of the email, stored for quick lookup of related addresses.""" return self._domain # This should not use `cached_property` as email is partially mutable @property def email_normalized(self) -> Optional[str]: """ Normalized representation of the email address, for hashing. """ return email_normalized(self.email) if self.email else None # This should not use `cached_property` as email is partially mutable @property def email_canonical(self) -> Optional[str]: """ Email address with the ``+sub-address`` portion of the mailbox removed. This is only used to identify and prevent re-use of blocked email addresses using the ``+sub-address`` method. Regular use does allow the ``+`` symbol. Special handling for the gmail.com domain also strips periods from the canonical representation. This makes the representation invalid for emailing. The canonical representation is not stored, but its blake2b160 representation is """ return canonical_email_representation(self.email)[0] if self.email else None @with_roles(read={'all'}) @cached_property def email_hash(self) -> str: """Public identifier string for email address, usable in URLs.""" return base58.b58encode(self.blake2b160).decode() # Compatibility name for notifications framework transport_hash = email_hash @with_roles(call={'all'}) def md5(self) -> Optional[str]: """MD5 hash of :property:`email_normalized`, for legacy use only.""" return ( hashlib.md5( # NOQA: S303 # skipcq: PTC-W1003 # nosec self.email_normalized.encode('utf-8') ).hexdigest() if self.email_normalized else None ) def __str__(self) -> str: """Cast email address into a string.""" return self.email or '' def __repr__(self) -> str: """Debugging representation of the email address.""" return f'EmailAddress({self.email!r})' def __init__(self, email: str) -> None: if not isinstance(email, str): raise ValueError("A string email address is required") # Set the hash first so the email column validator passes. Both hash columns # are immutable once set, so there are no content validators for them. self.blake2b160 = email_blake2b160_hash(email) self.email = email self.blake2b160_canonical = email_blake2b160_hash(self.email_canonical) def is_available_for(self, owner: Any): """Return True if this EmailAddress is available for the given owner.""" for backref_name in self.__exclusive_backrefs__: for related_obj in getattr(self, backref_name): curr_owner = getattr(related_obj, related_obj.__email_for__) if curr_owner is not None and curr_owner != owner: return False return True @delivery_state.transition(None, delivery_state.SENT) def mark_sent(self) -> None: """Record fact of an email message being sent to this address.""" self.delivery_state_at = db.func.utcnow() def mark_active(self) -> None: """Record timestamp of recipient activity.""" self.active_at = db.func.utcnow() @delivery_state.transition(None, delivery_state.SOFT_FAIL) def mark_soft_fail(self) -> None: """Record fact of a soft fail to this email address.""" self.delivery_state_at = db.func.utcnow() @delivery_state.transition(None, delivery_state.HARD_FAIL) def mark_hard_fail(self) -> None: """Record fact of a hard fail to this email address.""" self.delivery_state_at = db.func.utcnow() def refcount(self) -> int: """Returns count of references to this EmailAddress instance""" # obj.email_address_reference_is_active is a bool, but int(bool) is 0 or 1 return sum( sum( obj.email_address_reference_is_active for obj in getattr(self, backref_name) ) for backref_name in self.__backrefs__ ) @classmethod def mark_blocked(cls, email: str) -> None: """ Mark email address as blocked. Looks up all existing instances of EmailAddress with the same canonical representation and amends them to forget the email address and set the :attr:`is_blocked` flag. """ for obj in cls.get_canonical(email, is_blocked=False).all(): obj.email = None obj._is_blocked = True @classmethod def get_filter( cls, email: Optional[str] = None, blake2b160: Optional[bytes] = None, email_hash: Optional[str] = None, ): """ Get an filter condition for retriving an EmailAddress. Accepts an email address or a blake2b160 hash in either bytes or base58 form. Internally converts all lookups to a bytes-based hash lookup. Returns an expression suitable for use as a query filter. """ require_one_of(email=email, blake2b160=blake2b160, email_hash=email_hash) if email: if not cls.is_valid_email_address(email): return blake2b160 = email_blake2b160_hash(email) elif email_hash: blake2b160 = base58.b58decode(email_hash) return cls.blake2b160 == blake2b160 @classmethod def get( cls, email: Optional[str] = None, blake2b160: Optional[bytes] = None, email_hash: Optional[str] = None, ) -> Optional[EmailAddress]: """ Get an :class:`EmailAddress` instance by email address or its hash. Internally converts an email-based lookup into a hash-based lookup. """ return cls.query.filter( cls.get_filter(email, blake2b160, email_hash) ).one_or_none() @classmethod def get_canonical( cls, email: str, is_blocked: Optional[bool] = None ) -> Iterable[EmailAddress]: """ Get :class:`EmailAddress` instances matching the canonical representation. Optionally filtered by the :attr:`is_blocked` flag. """ hashes = [ email_blake2b160_hash(result) for result in canonical_email_representation(email) ] query = cls.query.filter(cls.blake2b160_canonical.in_(hashes)) if is_blocked is not None: query = query.filter_by(_is_blocked=is_blocked) return query @classmethod def _get_existing(cls, email: str) -> Optional[EmailAddress]: """ Internal method used by :meth:`add`, :meth:`add_for` and :meth:`validate_for`. """ if not cls.is_valid_email_address(email): return if cls.get_canonical(email, is_blocked=True).notempty(): raise EmailAddressBlockedError("Email address is blocked") return EmailAddress.get(email) @classmethod def add(cls, email: str) -> EmailAddress: """ Create a new :class:`EmailAddress` after validation. Raises an exception if the address is blocked from use, or the email address is syntactically invalid. """ existing = cls._get_existing(email) if existing: # Restore the email column if it's not present. Do not modify it otherwise if not existing.email: existing.email = email return existing new_email = EmailAddress(email) db.session.add(new_email) return new_email @classmethod def add_for(cls, owner: Optional[Any], email: str) -> EmailAddress: """ Create a new :class:`EmailAddress` after validation. Unlike :meth:`add`, this one requires the email address to not be in an exclusive relationship with another owner. """ existing = cls._get_existing(email) if existing: if not existing.is_available_for(owner): raise EmailAddressInUseError("This email address is in use") # No exclusive lock found? Let it be used then existing.email = email return existing new_email = EmailAddress(email) db.session.add(new_email) return new_email @classmethod def validate_for( cls, owner: Optional[Any], email: str, check_dns: bool = False, new: bool = False, ) -> Union[bool, str]: """ Validate whether the email address is available to the given owner. Returns False if the address is blocked or in use by another owner, True if available without issues, or a string value indicating the concern: 1. 'nomx': Email address is available, but has no MX records 2. 'not_new': Email address is already attached to owner (if `new` is True) 3. 'soft_fail': Known to be soft bouncing, requiring a warning message 4. 'hard_fail': Known to be hard bouncing, usually a validation failure 5. 'invalid': Available, but failed syntax validation :param owner: Proposed owner of this email address (may be None) :param str email: Email address to validate :param bool check_dns: Check for MX records for a new email address :param bool new: Fail validation if email address is already in use """ try: existing = cls._get_existing(email) except EmailAddressBlockedError: return False if not existing: diagnosis = cls.is_valid_email_address( email, check_dns=check_dns, diagnose=True ) if diagnosis is True: # No problems return True if diagnosis and diagnosis.diagnosis_type == 'NO_MX_RECORD': return 'nomx' return 'invalid' # There's an existing? Is it available for this owner? if not existing.is_available_for(owner): return False # Any other concerns? if new: return 'not_new' elif existing.delivery_state.SOFT_FAIL: return 'soft_fail' elif existing.delivery_state.HARD_FAIL: return 'hard_fail' return True @staticmethod def is_valid_email_address(email: str, check_dns=False, diagnose=False) -> bool: """ Return True if given email address is syntactically valid. This implementation will refuse to accept unusual elements such as quoted strings, as they are unlikely to appear in real-world use. :param bool check_dns: Optionally, check for existence of MX records :param bool diagnose: In case of errors only, return the diagnosis """ if email: result = is_email(email, check_dns=check_dns, diagnose=True) if result.diagnosis_type in ('VALID', 'NO_NAMESERVERS', 'DNS_TIMEDOUT'): return True return result if diagnose else False return False
class ProjectCrewMembership(ImmutableMembershipMixin, db.Model): """ Users can be crew members of projects, with specified access rights. """ __tablename__ = 'project_crew_membership' # List of is_role columns in this model __data_columns__ = ('is_editor', 'is_concierge', 'is_usher') __roles__ = { 'all': { 'read': { 'urls', 'user', 'is_editor', 'is_concierge', 'is_usher', 'project' } } } __datasets__ = { 'primary': { 'urls', 'uuid_b58', 'offered_roles', 'is_editor', 'is_concierge', 'is_usher', 'user', 'project', }, 'without_parent': { 'urls', 'uuid_b58', 'offered_roles', 'is_editor', 'is_concierge', 'is_usher', 'user', }, 'related': { 'urls', 'uuid_b58', 'offered_roles', 'is_editor', 'is_concierge', 'is_usher', }, } project_id = immutable( db.Column(None, db.ForeignKey('project.id', ondelete='CASCADE'), nullable=False)) project = immutable( db.relationship( Project, backref=db.backref('crew_memberships', lazy='dynamic', cascade='all', passive_deletes=True), )) parent = immutable(db.synonym('project')) parent_id = immutable(db.synonym('project_id')) # Project crew roles (at least one must be True): #: Editors can edit all common and editorial details of an event is_editor = db.Column(db.Boolean, nullable=False, default=False) #: Concierges are responsible for logistics and have write access #: to common details plus read access to everything else. Unlike #: editors, they cannot edit the schedule is_concierge = db.Column(db.Boolean, nullable=False, default=False) #: Ushers help participants find their way around an event and have #: the ability to scan badges at the door is_usher = db.Column(db.Boolean, nullable=False, default=False) @declared_attr def __table_args__(cls): args = list(super().__table_args__) args.append( db.CheckConstraint( db.or_( cls.is_editor.is_(True), cls.is_concierge.is_(True), cls.is_usher.is_(True), ), name='project_crew_membership_has_role', )) return tuple(args) @cached_property def offered_roles(self): """Roles offered by this membership record""" roles = set() if self.is_editor: roles.add('editor') if self.is_concierge: roles.add('concierge') if self.is_usher: roles.add('usher') roles.add('crew') roles.add('participant') return roles def roles_for(self, actor=None, anchors=()): roles = super(ProjectCrewMembership, self).roles_for(actor, anchors) if 'editor' in self.project.roles_for(actor, anchors): roles.add('project_editor') if 'admin' in self.project.profile.roles_for(actor, anchors): roles.add('profile_admin') return roles
class NotificationPreferences(BaseMixin, db.Model): """Holds a user's preferences for a particular Notification type""" __tablename__ = 'notification_preferences' #: Id of user whose preferences are represented here user_id = immutable( db.Column( None, db.ForeignKey('user.id', ondelete='CASCADE'), nullable=False, index=True, ) ) #: User whose preferences are represented here user = with_roles( immutable(db.relationship(User)), read={'owner'}, grants={'owner'} ) # Notification type, corresponding to Notification.type (a class attribute there) # notification_type = '' holds the veto switch to disable a transport entirely notification_type = immutable(db.Column(db.Unicode, nullable=False)) by_email = with_roles(db.Column(db.Boolean, nullable=False), rw={'owner'}) by_sms = with_roles(db.Column(db.Boolean, nullable=False), rw={'owner'}) by_webpush = with_roles(db.Column(db.Boolean, nullable=False), rw={'owner'}) by_telegram = with_roles(db.Column(db.Boolean, nullable=False), rw={'owner'}) by_whatsapp = with_roles(db.Column(db.Boolean, nullable=False), rw={'owner'}) __table_args__ = (db.UniqueConstraint('user_id', 'notification_type'),) __datasets__ = { 'preferences': { 'by_email', 'by_sms', 'by_webpush', 'by_telegram', 'by_whatsapp', } } def __init__(self, **kwargs): super().__init__(**kwargs) if self.user: self.set_defaults() def __repr__(self): return ( f'NotificationPreferences(' f'notification_type={self.notification_type!r}, user={self.user!r}' f')' ) def set_defaults(self): """ Set defaults based on notification type's defaults, and previous user prefs. """ transport_attrs = ( ('by_email', 'default_email'), ('by_sms', 'default_sms'), ('by_webpush', 'default_webpush'), ('by_telegram', 'default_telegram'), ('by_whatsapp', 'default_whatsapp'), ) if not self.user.notification_preferences: # No existing preferences. Get defaults from notification type's class if ( self.notification_type and self.notification_type in notification_type_registry ): type_cls = notification_type_registry[self.notification_type] for t_attr, d_attr in transport_attrs: if getattr(self, t_attr) is None: setattr(self, t_attr, getattr(type_cls, d_attr)) else: # No notification type class either. Turn on everything. for t_attr, d_attr in transport_attrs: if getattr(self, t_attr) is None: setattr(self, t_attr, True) else: for t_attr, d_attr in transport_attrs: if getattr(self, t_attr) is None: # If this transport is enabled for any existing notification type, # also enable here. setattr( self, t_attr, any( getattr(np, t_attr) for np in self.user.notification_preferences.values() ), ) @with_roles(call={'owner'}) def by_transport(self, transport): """Helper method to return ``self.by_<transport>``.""" return getattr(self, 'by_' + transport) @with_roles(call={'owner'}) def set_transport(self, transport, value): """Helper method to set a preference for a transport.""" setattr(self, 'by_' + transport, value) @cached_property def type_cls(self): """Return the Notification subclass corresponding to self.notification_type""" # Use `registry.get(type)` instead of `registry[type]` because the user may have # saved preferences for a discontinued notification type. These should ideally # be dropped in migrations, but it's possible for the data to be outdated. return notification_type_registry.get(self.notification_type) @classmethod def migrate_user(cls, old_user, new_user): for ntype, prefs in list(old_user.notification_preferences.items()): if ntype not in new_user.notification_preferences: prefs.user = new_user else: db.session.delete(prefs) @db.validates('notification_type') def _valid_notification_type(self, key, value): if value == '': # Special-cased name for main preferences return value if value is None or value not in notification_type_registry: raise ValueError("Invalid notification_type: %s" % value) return value
class UserNotification(UserNotificationMixin, NoIdMixin, db.Model): """ The recipient of a notification. Contains delivery metadata and helper methods to render the notification. """ __tablename__ = 'user_notification' # Primary key is a compound of (user_id, eventid). #: Id of user being notified (not immutable, to support account merge) user_id = db.Column( None, db.ForeignKey('user.id', ondelete='CASCADE'), primary_key=True, nullable=False, ) #: User being notified (backref defined below, outside the model) #: (not immutable, to support account merge) user = with_roles(db.relationship(User), read={'owner'}, grants={'owner'}) #: Random eventid, shared with the Notification instance eventid = with_roles( immutable(db.Column(UUIDType(binary=False), primary_key=True, nullable=False)), read={'owner'}, ) #: Id of notification that this user received notification_id = immutable( db.Column(None, nullable=False) ) # fkey in __table_args__ below #: Notification that this user received notification = with_roles( immutable( db.relationship( Notification, backref=db.backref('recipients', lazy='dynamic') ) ), read={'owner'}, ) #: The role they held at the time of receiving the notification, used for #: customizing the template. #: #: Note: This column represents the first instance of a role shifting from being an #: entirely in-app symbol (i.e., code refactorable) to being data in the database #: (i.e., requiring a data migration alongside a code refactor) role = with_roles(immutable(db.Column(db.Unicode, nullable=False)), read={'owner'}) #: Timestamp for when this notification was marked as read read_at = with_roles( db.Column(db.TIMESTAMP(timezone=True), default=None, nullable=True), read={'owner'}, ) #: Timestamp when/if the notification is revoked. This can happen if: #: 1. The action that caused the notification has been undone (future use), or #: 2. A new notification has been raised for the same document and this user was #: a recipient of the new notification. revoked_at = with_roles( db.Column(db.TIMESTAMP(timezone=True), nullable=True, index=True), read={'owner'}, ) #: When a roll-up is performed, record an identifier for the items rolled up rollupid = with_roles( db.Column(UUIDType(binary=False), nullable=True, index=True), read={'owner'} ) #: Message id for email delivery messageid_email = db.Column(db.Unicode, nullable=True) #: Message id for SMS delivery messageid_sms = db.Column(db.Unicode, nullable=True) #: Message id for web push delivery messageid_webpush = db.Column(db.Unicode, nullable=True) #: Message id for Telegram delivery messageid_telegram = db.Column(db.Unicode, nullable=True) #: Message id for WhatsApp delivery messageid_whatsapp = db.Column(db.Unicode, nullable=True) __table_args__ = ( db.ForeignKeyConstraint( [eventid, notification_id], [Notification.eventid, Notification.id], ondelete='CASCADE', name='user_notification_eventid_notification_id_fkey', ), ) __roles__ = {'owner': {'read': {'created_at'}}} __datasets__ = { 'primary': { 'created_at', 'eventid', 'eventid_b58', 'role', 'read_at', 'is_read', 'is_revoked', 'rollupid', 'notification_type', }, 'related': { 'created_at', 'eventid', 'eventid_b58', 'role', 'read_at', 'is_read', 'is_revoked', 'rollupid', }, } # --- User notification properties ------------------------------------------------- @property def identity(self): """Primary key of this object.""" return (self.user_id, self.eventid) @hybrid_property def eventid_b58(self): """URL-friendly UUID representation, using Base58 with the Bitcoin alphabet""" return uuid_to_base58(self.eventid) @eventid_b58.setter def eventid_b58(self, value): self.eventid = uuid_from_base58(value) @eventid_b58.comparator def eventid_b58(cls): # NOQA: N805 return SqlUuidB58Comparator(cls.eventid) with_roles(eventid_b58, read={'owner'}) @hybrid_property def is_read(self): """Whether this notification has been marked as read.""" return self.read_at is not None @is_read.setter def is_read(self, value): if value: if not self.read_at: self.read_at = db.func.utcnow() else: self.read_at = None @is_read.expression def is_read(cls): # NOQA: N805 return cls.read_at.isnot(None) with_roles(is_read, rw={'owner'}) @hybrid_property def is_revoked(self): """Whether this notification has been marked as revoked.""" return self.revoked_at is not None @is_revoked.setter def is_revoked(self, value): if value: if not self.revoked_at: self.revoked_at = db.func.utcnow() else: self.revoked_at = None @is_revoked.expression def is_revoked(cls): # NOQA: N805 return cls.revoked_at.isnot(None) with_roles(is_revoked, rw={'owner'}) # --- Dispatch helper methods ------------------------------------------------------ def user_preferences(self): """Return the user's notification preferences for this notification type.""" prefs = self.user.notification_preferences.get(self.notification_type) if not prefs: prefs = NotificationPreferences( user=self.user, notification_type=self.notification_type ) db.session.add(prefs) self.user.notification_preferences[self.notification_type] = prefs return prefs def has_transport(self, transport): """ Return whether the requested transport is an option. Uses four criteria: 1. The notification type allows delivery over this transport 2. The user's main transport preferences allow this one 3. The user's per-type preference allows it 4. The user actually has this transport (verified email or phone, etc) """ # This property inserts the row if not already present. An immediate database # commit is required to ensure a parallel worker processing another notification # doesn't make a conflicting row. main_prefs = self.user.main_notification_preferences user_prefs = self.user_preferences() return ( self.notification.allow_transport(transport) and main_prefs.by_transport(transport) and user_prefs.by_transport(transport) and self.user.has_transport(transport) ) def transport_for(self, transport): """ Return transport address for the requested transport. Uses four criteria: 1. The notification type allows delivery over this transport 2. The user's main transport preferences allow this one 3. The user's per-type preference allows it 4. The user has this transport (verified email or phone, etc) """ main_prefs = self.user.main_notification_preferences user_prefs = self.user_preferences() if ( self.notification.allow_transport(transport) and main_prefs.by_transport(transport) and user_prefs.by_transport(transport) ): return self.user.transport_for( transport, self.notification.preference_context ) return None def rollup_previous(self): """ Rollup prior instances of :class:`UserNotification` against the same document. Revokes and sets a shared rollup id on all prior user notifications. """ if not self.notification.fragment_model: # We can only rollup fragments within a document. Rollup doesn't apply # for notifications without fragments. return if self.is_revoked or self.rollupid is not None: # We've already been revoked or rolled up. Nothing to do. return # For rollup: find most recent unread that has a rollupid. Reuse that id so that # the current notification becomes the latest in that batch of rolled up # notifications. If none, this is the start of a new batch, so make a new id. rollupid = ( db.session.query(UserNotification.rollupid) .join(Notification) .filter( # Same user UserNotification.user_id == self.user_id, # Same type of notification Notification.type == self.notification.type, # Same document Notification.document_uuid == self.notification.document_uuid, # Same reason for receiving notification as earlier instance (same role) UserNotification.role == self.role, # Earlier instance is unread UserNotification.read_at.is_(None), # Earlier instance is not revoked UserNotification.revoked_at.is_(None), # Earlier instance has a rollupid UserNotification.rollupid.isnot(None), ) .order_by(UserNotification.created_at.asc()) .limit(1) .scalar() ) if not rollupid: # No previous rollupid? Then we're the first. The next notification # will use our rollupid as long as we're unread self.rollupid = uuid4() else: # Use the existing id, find all using it and revoke them self.rollupid = rollupid # Now rollup all previous unread. This will skip (a) previously revoked user # notifications, and (b) unrolled but read user notifications. for previous in ( UserNotification.query.join(Notification) .filter( # Same user UserNotification.user_id == self.user_id, # Not ourselves UserNotification.eventid != self.eventid, # Same type of notification Notification.type == self.notification.type, # Same document Notification.document_uuid == self.notification.document_uuid, # Same role as earlier notification, UserNotification.role == self.role, # Earlier instance is not revoked UserNotification.revoked_at.is_(None), # Earlier instance shares our rollupid UserNotification.rollupid == self.rollupid, ) .options( db.load_only( UserNotification.user_id, UserNotification.eventid, UserNotification.revoked_at, UserNotification.rollupid, ) ) ): previous.is_revoked = True previous.rollupid = self.rollupid def rolledup_fragments(self): """Return all fragments in the rolled up batch as a base query.""" if not self.notification.fragment_model: return None # Return a query on the fragment model with the rolled up identifiers if not self.rollupid: return self.notification.fragment_model.query.filter_by( uuid=self.notification.fragment_uuid ) return self.notification.fragment_model.query.filter( self.notification.fragment_model.uuid.in_( db.session.query(Notification.fragment_uuid) .select_from(UserNotification) .join(UserNotification.notification) .filter(UserNotification.rollupid == self.rollupid) ) ) @classmethod def get_for(cls, user, eventid_b58): """ Helper method to retrieve a UserNotification using SQLAlchemy session cache. """ return cls.query.get((user.id, uuid_from_base58(eventid_b58))) @classmethod def web_notifications_for(cls, user): return ( UserNotification.query.join(Notification) .filter( Notification.type.in_(notification_web_types), UserNotification.user == user, UserNotification.revoked_at.is_(None), ) .order_by(Notification.created_at.desc()) ) @classmethod def unread_count_for(cls, user): return ( UserNotification.query.join(Notification) .filter( Notification.type.in_(notification_web_types), UserNotification.user == user, UserNotification.read_at.is_(None), UserNotification.revoked_at.is_(None), ) .count() ) @classmethod def migrate_user(cls, old_user, new_user): for user_notification in cls.query.filter_by(user_id=old_user.id).all(): existing = cls.query.get((new_user.id, user_notification.eventid)) # TODO: Instead of dropping old_user's dupe notifications, check which of # the two has a higher priority role and keep that. This may not be possible # if the two copies are for different notifications under the same eventid. if existing: db.session.delete(user_notification) else: user_notification.user_id = new_user.id
class Notification(NoIdMixin, db.Model): """ Holds a single notification for an activity on a document object. Notifications are fanned out to recipients using :class:`UserNotification` and may be accessed through the website and delivered over email, push notification, SMS or other transport. Notifications cannot hold any data and must source everything from the linked document and fragment. """ __tablename__ = 'notification' #: Flag indicating this is an active notification type. Can be False for draft #: and retired notification types to hide them from preferences UI active = True #: Random identifier for the event that triggered this notification. Event ids can #: be shared across notifications, and will be used to enforce a limit of one #: instance of a UserNotification per-event rather than per-notification eventid = immutable( db.Column( UUIDType(binary=False), primary_key=True, nullable=False, default=uuid4 ) ) #: Notification id id = immutable( # NOQA: A003 db.Column( UUIDType(binary=False), primary_key=True, nullable=False, default=uuid4 ) ) #: Default category of notification. Subclasses MUST override category = notification_categories.none #: Default description for notification. Subclasses MUST override title = __("Unspecified notification type") #: Default description for notification. Subclasses MUST override description = '' #: Subclasses may set this to aid loading of :attr:`document` document_model = None #: Subclasses may set this to aid loading of :attr:`fragment` fragment_model = None #: Roles to send notifications to. Roles must be in order of priority for situations #: where a user has more than one role on the document. roles = [] #: Exclude triggering actor from receiving notifications? Subclasses may override exclude_actor = False #: If this notification is typically for a single recipient, views will need to be #: careful about leaking out recipient identifiers such as a utm_source tracking tag for_private_recipient = False #: The preference context this notification is being served under. Users may have #: customized preferences per profile or project preference_context = None #: Notification type (identifier for subclass of :class:`NotificationType`) type = immutable(db.Column(db.Unicode, nullable=False)) # NOQA: A003 #: Id of user that triggered this notification user_id = immutable( db.Column(None, db.ForeignKey('user.id', ondelete='SET NULL'), nullable=True) ) #: User that triggered this notification. Optional, as not all notifications are #: caused by user activity. Used to optionally exclude user from receiving #: notifications of their own activity user = immutable(db.relationship(User)) #: UUID of document that the notification refers to document_uuid = immutable( db.Column(UUIDType(binary=False), nullable=False, index=True) ) #: Optional fragment within document that the notification refers to. This may be #: the document itself, or something within it, such as a comment. Notifications for #: multiple fragments are collapsed into a single notification fragment_uuid = immutable(db.Column(UUIDType(binary=False), nullable=True)) __table_args__ = ( # This could have been achieved with a UniqueConstraint on all three columns. # When the third column (fragment_uuid) is null it has the same effect as the # PostgreSQL-specific where clause. We use the clause here to make clear our # intent of only enforcing a one-notification limit when the fragment is # present. Hence the naming convention of `_key` suffix rather than `ix_` prefix db.Index( 'notification_type_document_uuid_fragment_uuid_key', type, document_uuid, fragment_uuid, unique=True, postgresql_where=fragment_uuid.isnot(None), ), ) __mapper_args__ = { # 'polymorphic_identity' from subclasses is stored in the type column 'polymorphic_on': type, # When querying the Notification model, cast automatically to all subclasses 'with_polymorphic': '*', } __datasets__ = { 'primary': { 'eventid', 'eventid_b58', 'document_type', 'fragment_type', 'document', 'fragment', 'type', 'user', }, 'related': { 'eventid', 'eventid_b58', 'document_type', 'fragment_type', 'document', 'fragment', 'type', }, } # Flags to control whether this notification can be delivered over a particular # transport. Subclasses can disable these if they consider notifications unsuitable # for particular transports. #: This notification class may be seen on the website allow_web = True #: This notification class may be delivered by email allow_email = True #: This notification class may be delivered by SMS allow_sms = True #: This notification class may be delivered by push notification allow_webpush = True #: This notification class may be delivered by Telegram message allow_telegram = True #: This notification class may be delivered by WhatsApp message allow_whatsapp = True # Flags to set defaults for transports, in case the user has not made a choice #: By default, turn on/off delivery by email default_email = True #: By default, turn on/off delivery by SMS default_sms = True #: By default, turn on/off delivery by push notification default_webpush = True #: By default, turn on/off delivery by Telegram message default_telegram = True #: By default, turn on/off delivery by WhatsApp message default_whatsapp = True #: Ignore transport errors? If True, an error will be ignored silently. If False, #: an error report will be logged for the user or site administrator. TODO ignore_transport_errors = False #: Registry of per-class renderers renderers = {} # Registry of {cls_type: CustomNotificationView} def __init__(self, document=None, fragment=None, **kwargs): if document: if not isinstance(document, self.document_model): raise TypeError(f"{document!r} is not of type {self.document_model!r}") kwargs['document_uuid'] = document.uuid if fragment: if not isinstance(fragment, self.fragment_model): raise TypeError(f"{fragment!r} is not of type {self.fragment_model!r}") kwargs['fragment_uuid'] = fragment.uuid super().__init__(**kwargs) @classmethodproperty def cls_type(cls): # NOQA: N805 return cls.__mapper_args__['polymorphic_identity'] @property def identity(self): """Primary key of this object.""" return (self.eventid, self.id) @hybrid_property def eventid_b58(self): """URL-friendly UUID representation, using Base58 with the Bitcoin alphabet""" return uuid_to_base58(self.eventid) @eventid_b58.setter def eventid_b58(self, value): self.eventid = uuid_from_base58(value) @eventid_b58.comparator def eventid_b58(cls): # NOQA: N805 return SqlUuidB58Comparator(cls.eventid) @with_roles(read={'all'}) @classmethodproperty def document_type(cls): # NOQA: N805 return cls.document_model.__tablename__ if cls.document_model else None @with_roles(read={'all'}) @classmethodproperty def fragment_type(cls): # NOQA: N805 return cls.fragment_model.__tablename__ if cls.fragment_model else None @cached_property def document(self): """ Retrieve the document referenced by this Notification, if any. This assumes the underlying object won't disappear, as there is no SQL foreign key constraint enforcing a link. The proper way to do this is by having a secondary table for each type of document. """ if self.document_model and self.document_uuid: return self.document_model.query.filter_by(uuid=self.document_uuid).one() return None @cached_property def fragment(self): """ Retrieve the fragment within a document referenced by this Notification, if any. This assumes the underlying object won't disappear, as there is no SQL foreign key constraint enforcing a link. """ if self.fragment_model and self.fragment_uuid: return self.fragment_model.query.filter_by(uuid=self.fragment_uuid).one() return None @classmethod def renderer(cls, view): """ Decorator for view class containing render methods. Usage in views:: from ..models import MyNotificationType from .views import NotificationView @MyNotificationType.renderer class MyNotificationView(NotificationView): ... """ if cls.cls_type in cls.renderers: raise TypeError( f"A renderer has already been registered for {cls.cls_type}" ) cls.renderers[cls.cls_type] = view return view @classmethod def allow_transport(cls, transport): """Helper method to return ``cls.allow_<transport>``.""" return getattr(cls, 'allow_' + transport) def dispatch(self): """ Create :class:`UserNotification` instances and yield in an iterator. This is a heavy method and must be called from a background job. When making new notifications, it will revoke previous notifications issued against the same document. Subclasses wanting more control over how their notifications are dispatched should override this method. """ for user, role in (self.fragment or self.document).actors_with( self.roles, with_role=True ): # If this notification requires that it not be sent to the actor that # triggered the notification, don't notify them. For example, a user # who leaves a comment should not be notified of their own comment. # This `if` condition uses `user_id` instead of the recommended `user` # for faster processing in a loop. if ( self.exclude_actor and self.user_id is not None and self.user_id == user.id ): continue # Don't notify inactive (suspended, merged) users if not user.is_active: continue # Was a notification already sent to this user? If so: # 1. The user has multiple roles # 2. We're being dispatched a second time, possibly because a background # job failed and is re-queued. # In either case, don't notify the user a second time. # Since this query uses SQLAlchemy's session cache, we don't have to # bother with a local cache for the first case. existing_notification = UserNotification.query.get((user.id, self.eventid)) if not existing_notification: user_notification = UserNotification( eventid=self.eventid, user_id=user.id, notification_id=self.id, role=role, ) db.session.add(user_notification) yield user_notification