flowsint-cn-git/flowsint-api/alembic/versions/965b56353b4c_initial_migration.py

"""initial migration

Revision ID: 965b56353b4c
Revises: 
Create Date: 2025-05-19 14:35:04.755433

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = '965b56353b4c'
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('profiles',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('first_name', sa.Text(), nullable=True),
    sa.Column('last_name', sa.Text(), nullable=True),
    sa.Column('avatar_url', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('transforms',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('name', sa.Text(), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('category', postgresql.ARRAY(sa.Text()), nullable=True),
    sa.Column('transform_schema', sa.JSON(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.Column('last_updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('feedbacks',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.Column('content', sa.Text(), nullable=True),
    sa.Column('owner_id', sa.UUID(), nullable=True),
    sa.ForeignKeyConstraint(['owner_id'], ['profiles.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('investigations',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('owner_id', sa.UUID(), nullable=True),
    sa.Column('last_updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.Column('status', sa.String(), server_default='active', nullable=True),
    sa.ForeignKeyConstraint(['owner_id'], ['profiles.id'], onupdate='CASCADE', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_investigations_id', 'investigations', ['id'], unique=False)
    op.create_index('idx_investigations_owner_id', 'investigations', ['owner_id'], unique=False)
    op.create_table('investigations_profiles',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.Column('investigation_id', sa.UUID(), nullable=True),
    sa.Column('profile_id', sa.UUID(), nullable=True),
    sa.Column('role', sa.String(), server_default='member', nullable=True),
    sa.ForeignKeyConstraint(['investigation_id'], ['investigations.id'], onupdate='CASCADE', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['profile_id'], ['profiles.id'], onupdate='CASCADE', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_investigations_profiles_investigation_id', 'investigations_profiles', ['investigation_id'], unique=False)
    op.create_index('idx_investigations_profiles_profile_id', 'investigations_profiles', ['profile_id'], unique=False)
    op.create_index('projects_profiles_unique_profile_project', 'investigations_profiles', ['profile_id', 'investigation_id'], unique=True)
    op.create_table('sketches',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('title', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.Column('owner_id', sa.UUID(), nullable=True),
    sa.Column('status', sa.String(), server_default='active', nullable=True),
    sa.Column('investigation_id', sa.UUID(), nullable=True),
    sa.Column('last_updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.ForeignKeyConstraint(['investigation_id'], ['investigations.id'], onupdate='CASCADE', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['owner_id'], ['profiles.id'], onupdate='CASCADE', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_sketches_investigation_id', 'sketches', ['investigation_id'], unique=False)
    op.create_index('idx_sketches_owner_id', 'sketches', ['owner_id'], unique=False)
    op.create_table('scans',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.Column('status', sa.String(), nullable=True),
    sa.Column('results', sa.JSON(), nullable=True),
    sa.Column('values', postgresql.ARRAY(sa.Text()), nullable=True),
    sa.Column('sketch_id', sa.UUID(), nullable=True),
    sa.ForeignKeyConstraint(['sketch_id'], ['sketches.id'], onupdate='CASCADE', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_scans_sketch_id', 'scans', ['sketch_id'], unique=False)
    op.create_table('sketches_profiles',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.Column('profile_id', sa.UUID(), nullable=True),
    sa.Column('sketch_id', sa.UUID(), nullable=True),
    sa.Column('role', sa.String(), server_default='editor', nullable=True),
    sa.ForeignKeyConstraint(['profile_id'], ['profiles.id'], onupdate='CASCADE', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['sketch_id'], ['sketches.id'], onupdate='CASCADE', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_sketches_profiles_profile_id', 'sketches_profiles', ['profile_id'], unique=False)
    op.create_index('idx_sketches_profiles_sketch_id', 'sketches_profiles', ['sketch_id'], unique=False)
    op.create_index('investigations_profiles_unique_profile_investigation', 'sketches_profiles', ['profile_id', 'sketch_id'], unique=True)
    op.create_table('logs',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('scan_id', sa.UUID(), nullable=True),
    sa.Column('content', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.Column('sketch_id', sa.UUID(), nullable=True),
    sa.Column('type', sa.String(), server_default='INFO', nullable=True),
    sa.ForeignKeyConstraint(['scan_id'], ['scans.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['sketch_id'], ['sketches.id'], onupdate='CASCADE', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('logs')
    op.drop_index('investigations_profiles_unique_profile_investigation', table_name='sketches_profiles')
    op.drop_index('idx_sketches_profiles_sketch_id', table_name='sketches_profiles')
    op.drop_index('idx_sketches_profiles_profile_id', table_name='sketches_profiles')
    op.drop_table('sketches_profiles')
    op.drop_index('idx_scans_sketch_id', table_name='scans')
    op.drop_table('scans')
    op.drop_index('idx_sketches_owner_id', table_name='sketches')
    op.drop_index('idx_sketches_investigation_id', table_name='sketches')
    op.drop_table('sketches')
    op.drop_index('projects_profiles_unique_profile_project', table_name='investigations_profiles')
    op.drop_index('idx_investigations_profiles_profile_id', table_name='investigations_profiles')
    op.drop_index('idx_investigations_profiles_investigation_id', table_name='investigations_profiles')
    op.drop_table('investigations_profiles')
    op.drop_index('idx_investigations_owner_id', table_name='investigations')
    op.drop_index('idx_investigations_id', table_name='investigations')
    op.drop_table('investigations')
    op.drop_table('feedbacks')
    op.drop_table('transforms')
    op.drop_table('profiles')
    # ### end Alembic commands ###