class NodeHistory(Base): __tablename__ = "node_history" uuid = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid()) action = relationship("NodeHistoryAction") action_uuid = Column(UUID(as_uuid=True), ForeignKey("node_history_action.uuid")) action_user_uuid = Column(UUID(as_uuid=True), ForeignKey("user.uuid")) action_user = relationship("User", foreign_keys=[action_user_uuid]) after = Column(JSONB) before = Column(JSONB) node_uuid = Column(UUID(as_uuid=True), ForeignKey("node.uuid")) node = relationship("Node", foreign_keys=[node_uuid]) timestamp = Column(DateTime, server_default=utcnow())
class Observable(Base): __tablename__ = "observable" uuid = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid()) # Using timezone=True causes PostgreSQL to store the datetime as UTC. Datetimes without timezone # information will be assumed to be UTC, whereas datetimes with timezone data will be converted to UTC. expires_on = Column(DateTime(timezone=True)) for_detection = Column(Boolean, default=False, nullable=False) type = relationship("ObservableType") type_uuid = Column(UUID(as_uuid=True), ForeignKey("observable_type.uuid"), nullable=False) value = Column(String, nullable=False) __table_args__ = ( Index( "observable_value_trgm", value, postgresql_ops={"value": "gin_trgm_ops"}, postgresql_using="gin", ), Index("type_value", type_uuid, value), UniqueConstraint("type_uuid", "value", name="type_value_uc"), )
class User(Base): __tablename__ = "user" uuid = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid()) default_alert_queue = relationship("AlertQueue") default_alert_queue_uuid = Column(UUID(as_uuid=True), ForeignKey("alert_queue.uuid"), nullable=False) display_name = Column(String, nullable=False) email = Column(String, unique=True, nullable=False) enabled = Column(Boolean, default=True, nullable=False) password = Column(String, nullable=False) roles = relationship("UserRole", secondary=user_role_mapping, passive_deletes=True) timezone = Column(String, default="UTC", nullable=False) username = Column(String, unique=True, nullable=False)
class Node(Base): __tablename__ = "node" uuid = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid()) comments = relationship("NodeComment") directives = relationship("NodeDirective", secondary=node_directive_mapping) node_type = Column(String) tags = relationship("NodeTag", secondary=node_tag_mapping) threat_actor = relationship("NodeThreatActor") threat_actor_uuid = Column(UUID(as_uuid=True), ForeignKey("node_threat_actor.uuid")) threats = relationship("NodeThreat", secondary=node_threat_mapping) version = Column(UUID(as_uuid=True), nullable=False) __mapper_args__ = { "polymorphic_identity": "node", "polymorphic_on": node_type }
class AlertTool(Base): __tablename__ = "alert_tool" uuid = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid()) description = Column(String) value = Column(String, nullable=False, unique=True, index=True)
class EventSource(Base): __tablename__ = "event_source" uuid = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid()) description = Column(String) value = Column(String, nullable=False, unique=True, index=True)
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 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 NodeDirective(Base): __tablename__ = "node_directive" uuid = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid()) description = Column(String) value = Column(String, nullable=False, unique=True, index=True)
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 AlertDisposition(Base): __tablename__ = "alert_disposition" uuid = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid()) description = Column(String) rank = Column(Integer, nullable=False, unique=True) value = Column(String, nullable=False, unique=True, index=True)
class NodeThreat(Base): __tablename__ = "node_threat" uuid = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid()) description = Column(String) types = relationship( "NodeThreatType", secondary=node_threat_node_threat_type_mapping, passive_deletes=True, ) value = Column(String, nullable=False, unique=True, index=True)
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 User(UserMixin, Model): __tablename__ = "users" id = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid()) email = Column(db.String(80), unique=True, nullable=False) #: The hashed password password = Column(db.LargeBinary(128), nullable=True) created_at = Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow) is_active = Column(db.Boolean(), default=True, server_default="f", nullable=False) is_admin = Column(db.Boolean(), default=False, server_default="f", nullable=False) # last time we sent reset password email sent_reset_password_at = Column(db.DateTime, nullable=True) # last time we reset password via reset link reset_password_at = Column(db.DateTime, nullable=True) def __init__(self, email, password=None, **kwargs): """Create instance.""" db.Model.__init__(self, email=email, **kwargs) if password: self.set_password(password) else: self.password = None def set_password(self, password): """Set password.""" self.password = bcrypt.generate_password_hash(password) def check_password(self, value): """Check password.""" return bcrypt.check_password_hash(self.password, value) def __repr__(self): return f"<User({self.email!r})>"
class AnalysisModuleType(Base): __tablename__ = "analysis_module_type" uuid = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid()) description = Column(String) extended_version = Column(JSONB) manual = Column(Boolean, default=False, nullable=False) observable_types = relationship( "ObservableType", secondary=analysis_module_type_observable_type_mapping, passive_deletes=True, ) required_directives = relationship( "NodeDirective", secondary=analysis_module_type_directive_mapping, passive_deletes=True, ) required_tags = relationship( "NodeTag", secondary=analysis_module_type_tag_mapping, passive_deletes=True, ) value = Column(String, nullable=False, index=True) version = Column(String, nullable=False) __table_args__ = (UniqueConstraint("value", "version", name="value_version_uc"), )
class NodeComment(Base): __tablename__ = "node_comment" uuid = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid()) insert_time = Column(DateTime, server_default=utcnow()) node_uuid = Column(UUID(as_uuid=True), ForeignKey("node.uuid"), index=True) user_uuid = Column(UUID(as_uuid=True), ForeignKey("user.uuid")) user = relationship("User", foreign_keys=[user_uuid]) value = Column(String, nullable=False) __table_args__ = ( Index( "comment_value_trgm", value, postgresql_ops={"value": "gin_trgm_ops"}, postgresql_using="gin", ), UniqueConstraint("node_uuid", "value", name="node_value_uc"), )
def upgrade(): op.create_extension('pgcrypto') contact_method_type.create(op.get_bind()) op.create_table( 'user_profile', Column('user_profile_id', UUID, primary_key=True, server_default=f.gen_random_uuid()), Column('display_name', String), Column('name', String, nullable=False), Column('created_at', DateTime, nullable=False, server_default=utcnow)) op.create_table( 'user_auth', Column('user_profile_id', UUID, ForeignKey('user_profile.user_profile_id'), primary_key=True), Column('password_crypt', String, nullable=False)) op.create_table( 'contact_method', Column('user_profile_id', UUID, ForeignKey('user_profile.user_profile_id'), primary_key=True), Column('contact_method_id', UUID, primary_key=True, server_default=f.gen_random_uuid()), Column('contact_method_type', contact_method_type, nullable=False), Column('verified', Boolean, nullable=False)) op.create_table( 'email_contact_method', Column('user_profile_id', UUID, ForeignKey('user_profile.user_profile_id'), primary_key=True), Column('contact_method_id', UUID, primary_key=True), Column('email_address', String, nullable=False), ForeignKeyConstraint(['user_profile_id', 'contact_method_id'], [ 'contact_method.user_profile_id', 'contact_method.contact_method_id' ])) op.create_table( 'phone_contact_method', Column('user_profile_id', UUID, ForeignKey('user_profile.user_profile_id'), primary_key=True), Column('contact_method_id', UUID, primary_key=True), Column('phone_number', String, nullable=False), ForeignKeyConstraint(['user_profile_id', 'contact_method_id'], [ 'contact_method.user_profile_id', 'contact_method.contact_method_id' ])) op.create_table( 'address_contact_method', Column('user_profile_id', UUID, ForeignKey('user_profile.user_profile_id'), primary_key=True), Column('contact_method_id', UUID, primary_key=True), Column('country_code', String(2), nullable=False), Column('administrative_area', String, nullable=False), Column('locality', String, nullable=False), Column('depdendent_locality', String, nullable=False), Column('postal_code', String, nullable=False), Column('sorting_code', String, nullable=False), Column('address_2', String, nullable=False), Column('address_1', String, nullable=False), Column('organization', String, nullable=False), Column('name', String, nullable=False), ForeignKeyConstraint(['user_profile_id', 'contact_method_id'], [ 'contact_method.user_profile_id', 'contact_method.contact_method_id' ]))
from sqlalchemy import Column, ForeignKey, ForeignKeyConstraint, Boolean, DateTime, Integer,\ Numeric, String, func as f from sqlalchemy.dialects.postgresql import ENUM as Enum, JSONB, UUID from sqlalchemy.sql import column, or_, null from vocal.constants import ISO4217Currency import vocal.util.sqlalchemy # revision identifiers, used by Alembic. revision = '88943bf40bd7' down_revision = 'aa4168fa63a9' branch_labels = None depends_on = None utcnow = f.timezone('UTC', f.now()) v4_uuid = f.gen_random_uuid() Enum = partial(Enum, values_callable=lambda en: [e.value for e in en], create_type=False) subscription_plan_status = Enum('active', 'inactive', name='subscription_plan_status', create_type=False) payment_demand_type = Enum('periodic', 'immediate', 'pay-go', name='payment_demand_type', create_type=False) payment_demand_period = Enum('daily', 'weekly',