Maxim Snesarev
33d6bb7ebd
- Updated database configuration to switch from SQLite to PostgreSQL, including changes to alembic.ini, Docker Compose, and environment settings. - Refactored application code to utilize PostgreSQL database adapters, ensuring compatibility with the new database structure. - Enhanced API routes and data handling to support the new database, including adjustments in data models and query logic. - Introduced new job processing mechanisms for full synchronization of AMO CRM entities, leveraging FastStream for background tasks. - Improved logging and error handling across the application to facilitate better monitoring and debugging. - Removed obsolete SQLite adapter files and migrations, streamlining the project structure for PostgreSQL integration.
233 lines
11 KiB
Python
233 lines
11 KiB
Python
"""Initial database schema
|
|
|
|
Revision ID: 6393093b6602
|
|
Revises:
|
|
Create Date: 2025-09-08 04:16:36.889941
|
|
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
|
|
# revision identifiers, used by Alembic.
|
|
revision = '6393093b6602'
|
|
down_revision = None
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
# ### commands auto generated by Alembic - please adjust! ###
|
|
op.create_table('amo_custom_fields',
|
|
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column('entity_type', sa.String(length=50), nullable=False),
|
|
sa.Column('entity_id', sa.Integer(), nullable=False),
|
|
sa.Column('field_id', sa.Integer(), nullable=False),
|
|
sa.Column('field_name', sa.String(length=255), nullable=False),
|
|
sa.Column('field_type', sa.String(length=50), nullable=False),
|
|
sa.Column('field_value', sa.Text(), nullable=True),
|
|
sa.Column('field_value_numeric', sa.Integer(), nullable=True),
|
|
sa.Column('field_value_date', sa.Integer(), nullable=True),
|
|
sa.Column('is_custom', sa.Boolean(), nullable=True),
|
|
sa.Column('created_at', sa.Integer(), nullable=True),
|
|
sa.Column('updated_at', sa.Integer(), nullable=True),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index('idx_custom_fields_entity', 'amo_custom_fields', ['entity_type', 'entity_id'], unique=False)
|
|
op.create_index('idx_custom_fields_field', 'amo_custom_fields', ['field_id'], unique=False)
|
|
op.create_index('idx_custom_fields_name', 'amo_custom_fields', ['field_name'], unique=False)
|
|
op.create_table('amo_pipelines',
|
|
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
|
|
sa.Column('name', sa.String(length=255), nullable=False),
|
|
sa.Column('sort', sa.Integer(), nullable=True),
|
|
sa.Column('is_main', sa.Boolean(), nullable=True),
|
|
sa.Column('is_unsorted', sa.Boolean(), nullable=True),
|
|
sa.Column('is_archive', sa.Boolean(), nullable=True),
|
|
sa.Column('account_id', sa.Integer(), nullable=True),
|
|
sa.Column('created_at', sa.Integer(), nullable=True),
|
|
sa.Column('updated_at', sa.Integer(), nullable=True),
|
|
sa.Column('raw_data', sa.JSON(), nullable=True),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_table('amo_users',
|
|
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
|
|
sa.Column('name', sa.String(length=255), nullable=False),
|
|
sa.Column('email', sa.String(length=255), nullable=True),
|
|
sa.Column('is_active', sa.Boolean(), nullable=True),
|
|
sa.Column('created_at', sa.Integer(), nullable=True),
|
|
sa.Column('updated_at', sa.Integer(), nullable=True),
|
|
sa.Column('raw_data', sa.JSON(), nullable=True),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_table('export_configuration',
|
|
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column('name', sa.String(length=255), nullable=False),
|
|
sa.Column('sheet_id', sa.String(length=255), nullable=False),
|
|
sa.Column('date_range_start', sa.Integer(), nullable=True),
|
|
sa.Column('date_range_end', sa.Integer(), nullable=True),
|
|
sa.Column('is_active', sa.Boolean(), nullable=True),
|
|
sa.Column('created_at', sa.Integer(), nullable=True),
|
|
sa.Column('updated_at', sa.Integer(), nullable=True),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_table('amo_companies',
|
|
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
|
|
sa.Column('name', sa.String(length=255), nullable=False),
|
|
sa.Column('responsible_user_id', sa.Integer(), nullable=True),
|
|
sa.Column('group_id', sa.Integer(), nullable=True),
|
|
sa.Column('created_by', sa.Integer(), nullable=True),
|
|
sa.Column('updated_by', sa.Integer(), nullable=True),
|
|
sa.Column('created_at', sa.Integer(), nullable=True),
|
|
sa.Column('updated_at', sa.Integer(), nullable=True),
|
|
sa.Column('closest_task_at', sa.Integer(), nullable=True),
|
|
sa.Column('is_deleted', sa.Boolean(), nullable=True),
|
|
sa.Column('raw_data', sa.JSON(), nullable=True),
|
|
sa.ForeignKeyConstraint(['created_by'], ['amo_users.id'], ),
|
|
sa.ForeignKeyConstraint(['responsible_user_id'], ['amo_users.id'], ),
|
|
sa.ForeignKeyConstraint(['updated_by'], ['amo_users.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_table('amo_contacts',
|
|
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
|
|
sa.Column('name', sa.String(length=255), nullable=False),
|
|
sa.Column('first_name', sa.String(length=255), nullable=True),
|
|
sa.Column('last_name', sa.String(length=255), nullable=True),
|
|
sa.Column('responsible_user_id', sa.Integer(), nullable=True),
|
|
sa.Column('group_id', sa.Integer(), nullable=True),
|
|
sa.Column('created_by', sa.Integer(), nullable=True),
|
|
sa.Column('updated_by', sa.Integer(), nullable=True),
|
|
sa.Column('created_at', sa.Integer(), nullable=True),
|
|
sa.Column('updated_at', sa.Integer(), nullable=True),
|
|
sa.Column('closest_task_at', sa.Integer(), nullable=True),
|
|
sa.Column('is_deleted', sa.Boolean(), nullable=True),
|
|
sa.Column('raw_data', sa.JSON(), nullable=True),
|
|
sa.ForeignKeyConstraint(['created_by'], ['amo_users.id'], ),
|
|
sa.ForeignKeyConstraint(['responsible_user_id'], ['amo_users.id'], ),
|
|
sa.ForeignKeyConstraint(['updated_by'], ['amo_users.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_table('amo_events',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('type', sa.String(length=50), nullable=False),
|
|
sa.Column('entity_id', sa.Integer(), nullable=True),
|
|
sa.Column('entity_type', sa.String(length=50), nullable=True),
|
|
sa.Column('created_by', sa.Integer(), nullable=True),
|
|
sa.Column('created_at', sa.Integer(), nullable=True),
|
|
sa.Column('value_after', sa.JSON(), nullable=True),
|
|
sa.Column('value_before', sa.JSON(), nullable=True),
|
|
sa.Column('account_id', sa.Integer(), nullable=True),
|
|
sa.Column('raw_data', sa.JSON(), nullable=True),
|
|
sa.CheckConstraint("type IN ('incoming_call', 'outgoing_call', 'lead_status_changed')", name='check_event_type'),
|
|
sa.ForeignKeyConstraint(['created_by'], ['amo_users.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_table('amo_pipeline_stages',
|
|
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
|
|
sa.Column('pipeline_id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(length=255), nullable=False),
|
|
sa.Column('sort', sa.Integer(), nullable=True),
|
|
sa.Column('is_editable', sa.Boolean(), nullable=True),
|
|
sa.Column('color', sa.String(length=7), nullable=True),
|
|
sa.Column('created_at', sa.Integer(), nullable=True),
|
|
sa.Column('updated_at', sa.Integer(), nullable=True),
|
|
sa.Column('raw_data', sa.JSON(), nullable=True),
|
|
sa.ForeignKeyConstraint(['pipeline_id'], ['amo_pipelines.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_table('export_entity_mappings',
|
|
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column('configuration_id', sa.Integer(), nullable=False),
|
|
sa.Column('entity_type', sa.String(length=50), nullable=False),
|
|
sa.Column('sheet_name', sa.String(length=255), nullable=False),
|
|
sa.Column('field_mapping', sa.JSON(), nullable=False),
|
|
sa.Column('is_enabled', sa.Boolean(), nullable=True),
|
|
sa.Column('created_at', sa.Integer(), nullable=True),
|
|
sa.Column('updated_at', sa.Integer(), nullable=True),
|
|
sa.ForeignKeyConstraint(['configuration_id'], ['export_configuration.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_table('export_jobs',
|
|
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column('configuration_id', sa.Integer(), nullable=False),
|
|
sa.Column('status', sa.String(length=50), nullable=False),
|
|
sa.Column('records_processed', sa.Integer(), nullable=True),
|
|
sa.Column('total_records', sa.Integer(), nullable=True),
|
|
sa.Column('error_message', sa.Text(), nullable=True),
|
|
sa.Column('started_at', sa.Integer(), nullable=True),
|
|
sa.Column('completed_at', sa.Integer(), nullable=True),
|
|
sa.Column('created_at', sa.Integer(), nullable=True),
|
|
sa.ForeignKeyConstraint(['configuration_id'], ['export_configuration.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_table('amo_contact_companies',
|
|
sa.Column('contact_id', sa.Integer(), nullable=False),
|
|
sa.Column('company_id', sa.Integer(), nullable=False),
|
|
sa.Column('is_main', sa.Boolean(), nullable=True),
|
|
sa.ForeignKeyConstraint(['company_id'], ['amo_companies.id'], ),
|
|
sa.ForeignKeyConstraint(['contact_id'], ['amo_contacts.id'], ),
|
|
sa.PrimaryKeyConstraint('contact_id', 'company_id')
|
|
)
|
|
op.create_table('amo_deals',
|
|
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
|
|
sa.Column('name', sa.String(length=255), nullable=False),
|
|
sa.Column('price', sa.Integer(), nullable=True),
|
|
sa.Column('responsible_user_id', sa.Integer(), nullable=True),
|
|
sa.Column('group_id', sa.Integer(), nullable=True),
|
|
sa.Column('status_id', sa.Integer(), nullable=True),
|
|
sa.Column('pipeline_id', sa.Integer(), nullable=True),
|
|
sa.Column('loss_reason_id', sa.Integer(), nullable=True),
|
|
sa.Column('created_by', sa.Integer(), nullable=True),
|
|
sa.Column('updated_by', sa.Integer(), nullable=True),
|
|
sa.Column('closed_at', sa.Integer(), nullable=True),
|
|
sa.Column('created_at', sa.Integer(), nullable=True),
|
|
sa.Column('updated_at', sa.Integer(), nullable=True),
|
|
sa.Column('closest_task_at', sa.Integer(), nullable=True),
|
|
sa.Column('is_deleted', sa.Boolean(), nullable=True),
|
|
sa.Column('raw_data', sa.JSON(), nullable=True),
|
|
sa.ForeignKeyConstraint(['created_by'], ['amo_users.id'], ),
|
|
sa.ForeignKeyConstraint(['pipeline_id'], ['amo_pipelines.id'], ),
|
|
sa.ForeignKeyConstraint(['responsible_user_id'], ['amo_users.id'], ),
|
|
sa.ForeignKeyConstraint(['status_id'], ['amo_pipeline_stages.id'], ),
|
|
sa.ForeignKeyConstraint(['updated_by'], ['amo_users.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_table('amo_deal_companies',
|
|
sa.Column('deal_id', sa.Integer(), nullable=False),
|
|
sa.Column('company_id', sa.Integer(), nullable=False),
|
|
sa.Column('is_main', sa.Boolean(), nullable=True),
|
|
sa.ForeignKeyConstraint(['company_id'], ['amo_companies.id'], ),
|
|
sa.ForeignKeyConstraint(['deal_id'], ['amo_deals.id'], ),
|
|
sa.PrimaryKeyConstraint('deal_id', 'company_id')
|
|
)
|
|
op.create_table('amo_deal_contacts',
|
|
sa.Column('deal_id', sa.Integer(), nullable=False),
|
|
sa.Column('contact_id', sa.Integer(), nullable=False),
|
|
sa.Column('is_main', sa.Boolean(), nullable=True),
|
|
sa.ForeignKeyConstraint(['contact_id'], ['amo_contacts.id'], ),
|
|
sa.ForeignKeyConstraint(['deal_id'], ['amo_deals.id'], ),
|
|
sa.PrimaryKeyConstraint('deal_id', 'contact_id')
|
|
)
|
|
# ### end Alembic commands ###
|
|
|
|
|
|
def downgrade() -> None:
|
|
# ### commands auto generated by Alembic - please adjust! ###
|
|
op.drop_table('amo_deal_contacts')
|
|
op.drop_table('amo_deal_companies')
|
|
op.drop_table('amo_deals')
|
|
op.drop_table('amo_contact_companies')
|
|
op.drop_table('export_jobs')
|
|
op.drop_table('export_entity_mappings')
|
|
op.drop_table('amo_pipeline_stages')
|
|
op.drop_table('amo_events')
|
|
op.drop_table('amo_contacts')
|
|
op.drop_table('amo_companies')
|
|
op.drop_table('export_configuration')
|
|
op.drop_table('amo_users')
|
|
op.drop_table('amo_pipelines')
|
|
op.drop_index('idx_custom_fields_name', table_name='amo_custom_fields')
|
|
op.drop_index('idx_custom_fields_field', table_name='amo_custom_fields')
|
|
op.drop_index('idx_custom_fields_entity', table_name='amo_custom_fields')
|
|
op.drop_table('amo_custom_fields')
|
|
# ### end Alembic commands ###
|