"""initial clean schema Revision ID: fd42b5ce0dd0 Revises: Create Date: 2026-05-19 00:24:56.762416 """ from typing import Sequence, Union from alembic import op import sqlalchemy as sa # revision identifiers, used by Alembic. revision: str = 'fd42b5ce0dd0' down_revision: Union[str, Sequence[str], None] = None branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None def upgrade() -> None: """Upgrade schema.""" # organizations and users have a circular FK dependency: # organizations.owner_id -> users.id # users.org_id -> organizations.id # Solution: create organizations WITHOUT the owner_id FK, create users, # then add the FK constraint afterward. op.create_table( 'organizations', sa.Column('name', sa.String(), nullable=False), sa.Column('slug', sa.String(), nullable=False), sa.Column('logo_url', sa.String(), nullable=True), sa.Column('status', sa.Enum('ACTIVE', 'SUSPENDED', name='organizationstatus'), nullable=False), sa.Column('owner_id', sa.UUID(), nullable=False), sa.Column('id', sa.UUID(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=True), sa.Column('updated_at', sa.DateTime(), nullable=True), sa.PrimaryKeyConstraint('id') ) op.create_index(op.f('ix_organizations_id'), 'organizations', ['id'], unique=False) op.create_index(op.f('ix_organizations_owner_id'), 'organizations', ['owner_id'], unique=False) op.create_index(op.f('ix_organizations_slug'), 'organizations', ['slug'], unique=True) op.create_table( 'users', sa.Column('name', sa.String(), nullable=False), sa.Column('email', sa.String(), nullable=False), sa.Column('password', sa.String(length=256), nullable=False), sa.Column('org_id', sa.UUID(), nullable=True), sa.Column('platform_role', sa.Enum('SUPER_ADMIN', 'ADMIN', 'USER', name='platformrole'), nullable=False), sa.Column('org_role', sa.Enum('ORG_ADMIN', 'MANAGER', 'MEMBER', name='orgrole'), nullable=False), sa.Column('is_active', sa.Boolean(), nullable=False), sa.Column('is_verified', sa.Boolean(), nullable=False), sa.Column('id', sa.UUID(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=True), sa.Column('updated_at', sa.DateTime(), nullable=True), sa.ForeignKeyConstraint(['org_id'], ['organizations.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id') ) op.create_index(op.f('ix_users_email'), 'users', ['email'], unique=True) op.create_index(op.f('ix_users_id'), 'users', ['id'], unique=False) op.create_index(op.f('ix_users_org_id'), 'users', ['org_id'], unique=False) # Now that users exists, add the deferred FK from organizations.owner_id -> users.id op.create_foreign_key( 'fk_organizations_owner_id_users', 'organizations', 'users', ['owner_id'], ['id'], ondelete='CASCADE' ) op.create_table( 'organization_registration_requests', sa.Column('company_name', sa.String(), nullable=False), sa.Column('contact_name', sa.String(), nullable=False), sa.Column('contact_email', sa.String(), nullable=False), sa.Column('contact_phone', sa.String(), nullable=True), sa.Column('message', sa.Text(), nullable=True), sa.Column('status', sa.Enum('PENDING', 'IN_PROGRESS', 'COMPLETED', 'REJECTED', name='registrationstatus'), nullable=False), sa.Column('reviewed_by', sa.UUID(), nullable=True), sa.Column('id', sa.UUID(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=True), sa.Column('updated_at', sa.DateTime(), nullable=True), sa.ForeignKeyConstraint(['reviewed_by'], ['users.id'], ondelete='SET NULL'), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('contact_email') ) op.create_index(op.f('ix_organization_registration_requests_id'), 'organization_registration_requests', ['id'], unique=False) op.create_table( 'organization_user_invites', sa.Column('org_id', sa.UUID(), nullable=False), sa.Column('email', sa.String(), nullable=False), sa.Column('token', sa.String(length=256), nullable=False), sa.Column('org_role', sa.Enum('MANAGER', 'MEMBER', name='organization_role'), nullable=False), sa.Column('invited_by', sa.UUID(), nullable=False), sa.Column('status', sa.Enum('PENDING', 'ACCEPTED', 'REJECTED', 'EXPIRED', name='invite_status'), nullable=False), sa.Column('expires_at', sa.DateTime(), nullable=False), sa.Column('id', sa.UUID(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=True), sa.Column('updated_at', sa.DateTime(), nullable=True), sa.ForeignKeyConstraint(['invited_by'], ['users.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['org_id'], ['organizations.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('token') ) op.create_index(op.f('ix_organization_user_invites_email'), 'organization_user_invites', ['email'], unique=False) op.create_index(op.f('ix_organization_user_invites_id'), 'organization_user_invites', ['id'], unique=False) op.create_table( 'projects', sa.Column('org_id', sa.UUID(), nullable=False), sa.Column('name', sa.String(length=255), nullable=False), sa.Column('description', sa.Text(), nullable=True), sa.Column('project_key', sa.String(length=255), nullable=False), sa.Column('ticket_counter', sa.Integer(), nullable=False), sa.Column('status', sa.Enum('ACTIVE', 'INACTIVE', 'ARCHIVED', name='projectstatus'), nullable=False), sa.Column('created_by', sa.UUID(), nullable=False), sa.Column('id', sa.UUID(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=True), sa.Column('updated_at', sa.DateTime(), nullable=True), sa.ForeignKeyConstraint(['created_by'], ['users.id'], ), sa.ForeignKeyConstraint(['org_id'], ['organizations.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index('idx_project_org_status', 'projects', ['org_id', 'status'], unique=False) op.create_index(op.f('ix_projects_created_by'), 'projects', ['created_by'], unique=False) op.create_index(op.f('ix_projects_id'), 'projects', ['id'], unique=False) op.create_index(op.f('ix_projects_org_id'), 'projects', ['org_id'], unique=False) op.create_index(op.f('ix_projects_project_key'), 'projects', ['project_key'], unique=True) op.create_index(op.f('ix_projects_status'), 'projects', ['status'], unique=False) op.create_table( 'tickets', sa.Column('project_id', sa.UUID(), nullable=False), sa.Column('parent_ticket_id', sa.UUID(), nullable=True), sa.Column('assignee_id', sa.UUID(), nullable=True), sa.Column('reporter_id', sa.UUID(), nullable=False), sa.Column('ticket_number', sa.String(length=100), nullable=False), sa.Column('title', sa.String(length=255), nullable=False), sa.Column('description', sa.Text(), nullable=True), sa.Column('type', sa.Enum('BUG', 'FEATURE', 'TASK', 'STORY', name='tickettype'), nullable=False), sa.Column('status', sa.Enum('BACKLOG', 'TODO', 'IN_PROGRESS', 'IN_REVIEW', 'DONE', name='ticketstatus'), nullable=False), sa.Column('priority', sa.Enum('CRITICAL', 'HIGH', 'MEDIUM', 'LOW', name='ticketpriority'), nullable=False), sa.Column('story_points', sa.Integer(), nullable=True), sa.Column('due_date', sa.Date(), nullable=True), sa.Column('environment', sa.Enum('DEV', 'STAGING', 'PRODUCTION', name='environmenttype'), nullable=True), sa.Column('deployment_status', sa.Enum('NOT_DEPLOYED', 'IN_PROGRESS', 'DEPLOYED', 'FAILED', name='deploymentstatus'), nullable=False), sa.Column('deployment_url', sa.String(length=500), nullable=True), sa.Column('deployment_notes', sa.Text(), nullable=True), sa.Column('id', sa.UUID(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=True), sa.Column('updated_at', sa.DateTime(), nullable=True), sa.ForeignKeyConstraint(['assignee_id'], ['users.id'], ), sa.ForeignKeyConstraint(['parent_ticket_id'], ['tickets.id'], ), sa.ForeignKeyConstraint(['project_id'], ['projects.id'], ), sa.ForeignKeyConstraint(['reporter_id'], ['users.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index(op.f('ix_tickets_id'), 'tickets', ['id'], unique=False) def downgrade() -> None: """Downgrade schema.""" op.drop_index(op.f('ix_tickets_id'), table_name='tickets') op.drop_table('tickets') op.drop_index(op.f('ix_projects_status'), table_name='projects') op.drop_index(op.f('ix_projects_project_key'), table_name='projects') op.drop_index(op.f('ix_projects_org_id'), table_name='projects') op.drop_index(op.f('ix_projects_id'), table_name='projects') op.drop_index(op.f('ix_projects_created_by'), table_name='projects') op.drop_index('idx_project_org_status', table_name='projects') op.drop_table('projects') op.drop_index(op.f('ix_organization_user_invites_id'), table_name='organization_user_invites') op.drop_index(op.f('ix_organization_user_invites_email'), table_name='organization_user_invites') op.drop_table('organization_user_invites') op.drop_index(op.f('ix_organization_registration_requests_id'), table_name='organization_registration_requests') op.drop_table('organization_registration_requests') # Drop the manually added FK before dropping the tables op.drop_constraint('fk_organizations_owner_id_users', 'organizations', type_='foreignkey') op.drop_index(op.f('ix_users_org_id'), table_name='users') op.drop_index(op.f('ix_users_id'), table_name='users') op.drop_index(op.f('ix_users_email'), table_name='users') op.drop_table('users') op.drop_index(op.f('ix_organizations_slug'), table_name='organizations') op.drop_index(op.f('ix_organizations_owner_id'), table_name='organizations') op.drop_index(op.f('ix_organizations_id'), table_name='organizations') op.drop_table('organizations') sa.Enum(name='organizationstatus').drop(op.get_bind()) sa.Enum(name='platformrole').drop(op.get_bind()) sa.Enum(name='orgrole').drop(op.get_bind()) sa.Enum(name='registrationstatus').drop(op.get_bind()) sa.Enum(name='organization_role').drop(op.get_bind()) sa.Enum(name='invite_status').drop(op.get_bind()) sa.Enum(name='projectstatus').drop(op.get_bind()) sa.Enum(name='tickettype').drop(op.get_bind()) sa.Enum(name='ticketstatus').drop(op.get_bind()) sa.Enum(name='ticketpriority').drop(op.get_bind()) sa.Enum(name='environmenttype').drop(op.get_bind()) sa.Enum(name='deploymentstatus').drop(op.get_bind())