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',
