class ArgumentVote(Base): __tablename__ = 'argumentvotes' member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True) member = relationship("User", backref=backref("member_argumentvotes", cascade="all, delete-orphan")) relation_id: int = C(Integer, ForeignKey('argumentrelations.id'), primary_key=True) relation = relationship("ArgumentRelation", backref=backref("relation_votes", cascade="all, delete-orphan")) weight: int = C(Integer, nullable=False, comment='if extendedDiscussion: --(-2),-,0,+,++(+2) , otherwise -1 and +1')
class GroupMember(Base): __tablename__ = 'groupmembers' group_id: int = C(Integer, ForeignKey('groups.id'), primary_key=True) member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True) group = relationship("Group", backref=backref("group_members", cascade="all, delete-orphan")) member = relationship("User", backref=backref("member_groups", cascade="all, delete-orphan"))
class Page(Base): __tablename__ = 'page' name: str = C(Text, primary_key=True) lang: str = C(Text, primary_key=True) title: str = C(Text) text: str = C(Text) permissions = C(JSON)
class OAuthToken(Base): __tablename__ = 'oauth_token' id = C(Integer, FK('users.id'), primary_key=True) user = rel("User", backref=bref("oauth_token", uselist=False)) token = C(JSON) provider = C(Text) created_at = C(DateTime, nullable=False, server_default=func.now())
class Changeset(Base): __tablename__ = 'changeset' id: int = integer_pk() document_id: int = C(Integer, ForeignKey('document.id'), nullable=False) proposition_id: LID = C(LIDType, ForeignKey('propositions.id'), nullable=False) document = relationship(Document, back_populates='changesets') proposition = relationship(Proposition, back_populates='changesets') section: str = C(Text, comment='Identifier for the section of the document that is changed.')
class VotingPhaseType(Base): __tablename__ = 'voting_phase_types' id: int = integer_pk() name: str = C(Text, server_default='', comment='readable name') abbreviation: str = C(Text, server_default='', comment='abbreviated name') secret_voting_possible: bool = C(Boolean, nullable=False) voting_type = C(Enum(VotingType), nullable=False) # online, urn, assembly, board description: str = C(Text, server_default='')
class UserProfile(Base): __tablename__ = 'userprofiles' id: int = C(Integer, ForeignKey('users.id'), primary_key=True) user = relationship("User", back_populates="profile") sub: str = C(Text, unique=True) eligible: bool = C(Boolean) verified: bool = C(Boolean) profile: str = C(Text)
class PropositionType(Base): # Antragsart __tablename__ = 'propositiontypes' id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True) name: str = C(Text, unique=True, nullable=False) abbreviation: str = C(Text, unique=True, nullable=False) description: str = C(Text, server_default='') policy_id: int = C(Integer, ForeignKey('policies.id'), nullable=False) policy: Policy = relationship("Policy", back_populates="proposition_types") ballots = relationship("Ballot", back_populates="proposition_type")
class BallotOption(Base): __tablename__ = 'ballot_option' uuid = C(UUIDType, server_default=func.gen_random_uuid(), primary_key=True) voting_uuid = C(UUIDType, FK('ballot_voting.uuid'), nullable=False) title = C(String) text = C(Text, nullable=False) voting = rel('BallotVoting', back_populates='options') votes = rel('Vote', back_populates='option')
class UrnSupporter(Base): # §5b.2 __tablename__ = 'urnsupporters' member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True) member = relationship("User", backref=backref("member_urns", cascade="all, delete-orphan")) urn_id: int = C(Integer, ForeignKey('urns.id'), primary_key=True) urn = relationship("Urn", backref=backref("urn_members", cascade="all, delete-orphan")) type: str = C(Text, nullable=False) # responsible, request, voter # §5b.2+4 voted: bool = C(Boolean, nullable=False, server_default='false') # §5b.6
class VoteToken(Base): __tablename__ = 'vote_token' token = C(UUIDType, server_default=func.gen_random_uuid(), primary_key=True) auid = C(UUIDType, nullable=False) vote_uuid = C(UUIDType, FK('vote.uuid'), nullable=False) vote = rel('Vote', back_populates='token')
class SecretVoter(Base): # §3.7, §4.4 __tablename__ = 'secretvoters' member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True) member = relationship("User", backref=backref("member_ballots", cascade="all, delete-orphan")) ballot_id: int = C(Integer, ForeignKey('ballots.id'), primary_key=True) ballot = relationship("Ballot", backref=backref("ballot_members", cascade="all, delete-orphan")) status: str = C(Enum(SecretVoterStatus), nullable=False) # active,expired,retracted last_change: datetime = C(DateTime, nullable=False) # time of requested/retracted
class Supporter(Base): # §3.5 __tablename__ = 'supporters' member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True) member = relationship("User", backref=backref("member_propositions", cascade="all, delete-orphan")) proposition_id: LID = C(LIDType, ForeignKey('propositions.id'), primary_key=True) proposition = relationship("Proposition", backref=backref("propositions_member", cascade="all, delete-orphan")) submitter: bool = C(Boolean, nullable=False, server_default='false', comment='submitter or regular') status: SupporterStatus = C(Enum(SupporterStatus), nullable=False, server_default='ACTIVE') last_change: datetime = C(DateTime, nullable=False, server_default=func.now(), comment='last status change')
class AreaMember(Base): __tablename__ = 'areamembers' area_id: int = C(Integer, ForeignKey('subjectareas.id'), primary_key=True) area = relationship("SubjectArea", backref=backref("area_members", cascade="all, delete-orphan")) member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True) member = relationship("User", backref=backref("member_areas", cascade="all, delete-orphan")) def __init__(self, area=None, member=None): self.area = area self.member = member
class Tag(Base): __tablename__ = 'tags' id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True) name: str = C(Text, unique=True, nullable=False) parent_id: int = C(Integer, ForeignKey('tags.id')) children = relationship("Tag", backref=backref('parent', remote_side=[id])) mut_exclusive: bool = C( Boolean, nullable=False, server_default='false', comment='whether all children are mutually exclusive' ) propositions = association_proxy('tag_propositions', 'proposition') # <-PropositionTag-> Proposition
class PropositionTag(Base): __tablename__ = 'propositiontags' proposition_id: LID = C(LIDType, ForeignKey('propositions.id'), primary_key=True) proposition = relationship("Proposition", backref=backref("proposition_tags", cascade="all, delete-orphan")) tag_id: int = C(Integer, ForeignKey('tags.id'), primary_key=True) tag = relationship("Tag", backref=backref("tag_propositions", cascade="all, delete-orphan")) def __init__(self, tag=None, proposition=None): self.tag = tag self.proposition = proposition
class Department(Base): __tablename__ = 'departments' id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True) name: str = C(Text, unique=True, nullable=False) description: str = C(Text) voting_phases = relationship('VotingPhase', back_populates='department', cascade='all, delete-orphan') members = association_proxy('department_members', 'member') # <-DepartmentMember-> User areas = relationship("SubjectArea", back_populates="department") exporter_settings: dict = C(MutableDict.as_mutable(JSONB), server_default='{}') voting_module_settings: dict = C(MutableDict.as_mutable(JSONB), server_default='{}') """
class DepartmentMember(Base): __tablename__ = 'departmentmembers' department_id: int = C(Integer, ForeignKey('departments.id'), primary_key=True) department = relationship("Department", backref=backref("department_members", cascade="all, delete-orphan")) member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True) member = relationship("User", backref=backref("member_departments", cascade="all, delete-orphan")) is_admin: bool = C(Boolean, nullable=False, server_default='false') def __init__(self, department=None, member=None, is_admin=False): self.department = department self.member = member self.is_admin = is_admin
class SubjectArea(Base): # Themenbereich §2.3+4 __tablename__ = 'subjectareas' id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True) name: str = C(Text, nullable=False) description: str = C(Text) department_id: int = C(Integer, ForeignKey('departments.id'), nullable=False) department = relationship("Department", back_populates="areas") ballots = relationship("Ballot", back_populates="area") # Themenbereichsteilnehmer # can only be removed if not proposition in this area supported §2.3 members = association_proxy('area_members', 'member') # <-AreaMember-> User documents = relationship('Document', back_populates='area')
class PropositionNote(Base): __tablename__ = 'propositionnotes' proposition_id: LID = C(LIDType, ForeignKey('propositions.id'), primary_key=True) user_id: int = C(Integer, ForeignKey('users.id'), primary_key=True) notes: str = C(Text) vote: VoteByUser = C(Enum(VoteByUser)) def __init__(self, user, id, notes=None, vote=VoteByUser.UNSURE): self.proposition_id = id self.user_id = user self.notes = notes self.vote = vote
class User(Base): __tablename__ = 'users' id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True) name: str = C(Text, unique=True, nullable=False) email: str = C(EmailType, unique=True, comment='optional, for notifications, otherwise use user/mails/') auth_type: str = C( Text, nullable=False, server_default='system', comment='deleted,system,token,virtual,oauth(has UserProfile)' ) joined: datetime = C(DateTime, nullable=False, server_default=func.now()) active: bool = C(Boolean, nullable=False, server_default='true') last_active: datetime = C( DateTime, nullable=False, server_default=func.now(), comment='last relevant activity (to be considered active member §2.2)' ) can_login_until: datetime = C( DateTime, comment='optional expiration datetime after which login is no longer possible' ) # actions: submit/support proposition, voting, or explicit, deactivate after 2 periods profile = relationship("UserProfile", uselist=False, back_populates="user") groups = association_proxy('member_groups', 'group', creator=lambda g: GroupMember(group=g)) # <-GroupMember-> Group # from user/membership/ all_nested_groups departments = association_proxy('member_departments', 'department') # <-DepartmentMember-> Department areas = association_proxy('member_areas', 'area') # <-AreaMember-> SubjectArea supports = association_proxy('member_propositions', 'proposition') # <-Supporter-> Proposition arguments = relationship("Argument", back_populates="author") secret_voters = association_proxy('member_secretvoters', 'secretvoter') # <-SecretVoter-> Ballot urns = association_proxy('member_urns', 'urn') # <-UrnSupporter-> Urn postal_votes = association_proxy('member_postal', 'voting') # <-PostalVote-> VotingPhase @property def managed_departments(self): return [md.department for md in self.member_departments if md.is_admin]
class Ballot(Base): # conflicting qualified propositions __tablename__ = 'ballots' id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True) name: str = C(Text) # <- propositions Proposition[] # XXX: not sure if we need a status here. Add missing states to PropositionStatus or the other way round? # status: str = C(Text, nullable=False) # submitted?, qualified, locked, obsolete # §4.8 §5.2 election: int = C(Integer, nullable=False, server_default='0') # 0=no election, otherwise nr of positions, §5d.4+5 # §3.8, one proposition is for qualification of election itself voting_type: VotingType = C(Enum(VotingType)) # online, urn, assembly, board proposition_type_id: int = C(Integer, ForeignKey('propositiontypes.id')) proposition_type: PropositionType = relationship("PropositionType", back_populates="ballots") area_id: int = C(Integer, ForeignKey('subjectareas.id')) area = relationship("SubjectArea", back_populates="ballots") # contains department # optional, if assigned, set proposition to planned voting_id: int = C(Integer, ForeignKey('votingphases.id')) voting = relationship("VotingPhase", back_populates="ballots") secret_voters = association_proxy('ballot_members', 'member') # <-SecretVoter-> User propositions = relationship("Proposition", back_populates="ballot") # <-result VotingResult # optional result: dict = C(MutableDict.as_mutable(JSONB))
class ArgumentRelation(Base): __tablename__ = 'argumentrelations' id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True) parent_id: int = C(Integer, ForeignKey('argumentrelations.id'), comment='only for inter-arguments') children = relationship("ArgumentRelation", backref=backref('parent', remote_side=[id])) argument_id: int = C(Integer, ForeignKey('arguments.id')) argument = relationship("Argument", backref=backref("argument_relations", cascade="all, delete-orphan")) proposition_id: LID = C(LIDType, ForeignKey('propositions.id')) proposition = relationship("Proposition", backref=backref("proposition_arguments", cascade="all, delete-orphan")) argument_type: ArgumentType = C(Enum(ArgumentType), nullable=False) def user_vote(self, user): return object_session(self).query(ArgumentVote).filter_by(relation=self, member=user).scalar() @cached_property def score(self): return sum(rv.weight for rv in self.relation_votes)
class Argument(Base): __tablename__ = 'arguments' id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True) title: str = C(Text, nullable=False) abstract: str = C(Text, nullable=False) details: str = C(Text) author_id: int = C(Integer, ForeignKey('users.id')) author = relationship("User", backref=backref("member_arguments", cascade="all, delete-orphan")) created_at: datetime = C(DateTime, nullable=False, server_default=func.now())
class VotingPhase(Base): # Abstimmungsperiode __tablename__ = 'votingphases' __table_args__ = ( CheckConstraint( "(status='PREPARING' AND target IS NULL) OR (status!='PREPARING' AND target IS NOT NULL)", 'state_valid' ), ) id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True) status: VotingStatus = C(Enum(VotingStatus), nullable=False, server_default='PREPARING') target: datetime = C(DateTime, comment='constrained by §4.1') department_id: int = C(Integer, ForeignKey('departments.id'), nullable=False) phase_type_id: int = C(Integer, ForeignKey('voting_phase_types.id'), nullable=False) secret: bool = C( Boolean, nullable=False, server_default='false', comment='whether any secret votes will take place (decision deadline §4.2)' ) name: str = C(Text, server_default='', comment='short, readable name which can be used for URLs') title: str = C(Text, server_default='') description: str = C(Text, server_default='') voting_module_data: dict = C(MutableDict.as_mutable(JSONB), server_default='{}') ballots = relationship("Ballot", back_populates="voting") department = relationship('Department', back_populates='voting_phases') phase_type = relationship('VotingPhaseType') # <- urns Urn[] urns = relationship("Urn", back_populates="voting") postal_votes = association_proxy('voting_postal', 'member') # <-PostalVote-> User # send announcement before deadline §4.1 # send voting invitation before deadline §4.3 # deadline for vote registration before voting starts $5.6 # shall not assign more than recommended votings per period §5.2 # ask submitters for veto for move to other phase §5.3 @property def ballots_can_be_added(self): return self.status in (VotingStatus.PREPARING, VotingStatus.SCHEDULED)
class Urn(Base): __tablename__ = 'urns' id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True) voting_id: int = C(Integer, ForeignKey('votingphases.id'), nullable=False) voting = relationship("VotingPhase", back_populates="urns") accepted: bool = C(Boolean, nullable=False, server_default='false') location: str = C(Text, nullable=False) description: str = C(Text) opening = C(Time) # §5b.5 supporters = association_proxy('urn_members', 'member') # <-UrnSupporter-> User
class Vote(Base): __tablename__ = 'vote' uuid = C(UUIDType, server_default=func.gen_random_uuid(), primary_key=True) yes_no = C(Boolean) points = C(Integer, nullable=False) option_uuid = C(UUIDType, FK('ballot_option.uuid'), nullable=False) created_at = C(DateTime, nullable=False, server_default=func.now()) confirmed = C(Boolean, nullable=False, server_default='false') token = rel(VoteToken, back_populates='vote') option = rel(BallotOption, back_populates='votes')
class Document(Base): __tablename__ = 'document' id: int = integer_pk() name: str = C(Text) lang: str = C(Text) area_id: int = C(Integer, ForeignKey('subjectareas.id')) area = relationship('SubjectArea', back_populates='documents') # contains department text: str = C(Text) description: str = C(Text) proposition_type_id: int = C(Integer, ForeignKey('propositiontypes.id')) proposition_type = relationship('PropositionType') changesets = relationship('Changeset', back_populates='document') __table_args__ = (UniqueConstraint(name, lang, area_id), )
class BallotVoting(Base): __tablename__ = 'ballot_voting' uuid = C(UUIDType, server_default=func.gen_random_uuid(), primary_key=True) department = C(String, nullable=False) title = C(String) created_at = C(DateTime, nullable=False, server_default=func.now()) starts_at = C(DateTime, nullable=False) ends_at = C(DateTime, nullable=False) options = rel(BallotOption, back_populates='voting') def votes_to_confirm(self, auid): return (object_session(self).query( VoteToken.token, Vote, BallotOption).filter( VoteToken.auid == auid, Vote.confirmed == False, VoteToken.vote_uuid == Vote.uuid, BallotOption.uuid == Vote.option_uuid, BallotOption.voting == self).order_by(Vote.yes_no.desc(), Vote.points.desc()))
class UserLoginToken(Base): __tablename__ = 'user_login_token' token: str = C(Text, primary_key=True) user_id: int = C(Integer, ForeignKey('users.id')) user = relationship("User", backref=backref("login_token", uselist=False)) valid_until: datetime = C(DateTime)