ayurishchevandClaude Sonnet 5
a846d30872
Backend (FastAPI, SQLAlchemy 2, Alembic, PostgreSQL 16): - организации, VRF, префиксы (дерево, использование, автоназначение), адреса, операторы связи, устройства и типы устройств; JWT, роли admin/viewer; - VRF принадлежит организации (составной FK), смена VRF у префикса переносит поддерево, имя VRF уникально в организации; - журнал аудита: поиск и фильтры, ротация (срок/количество), очистка по паролю с блокировкой, IP клиента и метаданные запроса (X-Forwarded-For только от TRUSTED_PROXIES). UI (web/, без сборки): экраны и диалоги по макетам «IPAM Manager», кликабельные строки реестров, локальные шрифты IBM Plex, собственные выпадающие списки. Окружение: docker-compose (postgres + app), миграции Alembic 0001-0004, scripts/gen_env.py, scripts/seed_demo.py, 11 автотестов (pytest). Документация: README.md и docs/changes/001-005 (планы и итоги). Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
164 lines
7.9 KiB
Python
164 lines
7.9 KiB
Python
"""initial schema
|
|
|
|
Revision ID: 0001
|
|
Revises:
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
from sqlalchemy.dialects import postgresql
|
|
from sqlalchemy.dialects import postgresql
|
|
|
|
revision = '0001'
|
|
down_revision = None
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
# ### commands auto generated by Alembic - please adjust! ###
|
|
op.create_table('audit_log',
|
|
sa.Column('id', sa.BigInteger(), nullable=False),
|
|
sa.Column('ts', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('username', sa.String(length=100), nullable=False),
|
|
sa.Column('entity_type', sa.String(length=50), nullable=False),
|
|
sa.Column('entity_id', sa.Integer(), nullable=True),
|
|
sa.Column('entity_label', sa.String(length=255), nullable=False),
|
|
sa.Column('action', sa.String(length=20), nullable=False),
|
|
sa.Column('diff', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_audit_log_entity_type'), 'audit_log', ['entity_type'], unique=False)
|
|
op.create_index(op.f('ix_audit_log_ts'), 'audit_log', ['ts'], unique=False)
|
|
op.create_table('device_types',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(length=100), nullable=False),
|
|
sa.Column('is_default', sa.Boolean(), nullable=False),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('name')
|
|
)
|
|
op.create_table('organizations',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(length=255), nullable=False),
|
|
sa.Column('short_name', sa.String(length=100), nullable=False),
|
|
sa.Column('inn', sa.String(length=12), nullable=False),
|
|
sa.Column('address', sa.String(length=500), nullable=False),
|
|
sa.Column('contact_person', sa.String(length=255), nullable=False),
|
|
sa.Column('phone', sa.String(length=50), nullable=False),
|
|
sa.Column('email', sa.String(length=255), nullable=False),
|
|
sa.Column('note', sa.Text(), nullable=False),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('inn'),
|
|
sa.UniqueConstraint('name')
|
|
)
|
|
op.create_table('users',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('username', sa.String(length=100), nullable=False),
|
|
sa.Column('password_hash', sa.String(length=255), nullable=False),
|
|
sa.Column('role', sa.Enum('admin', 'viewer', name='user_role'), nullable=False),
|
|
sa.Column('is_active', sa.Boolean(), nullable=False),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('username')
|
|
)
|
|
op.create_table('devices',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(length=255), nullable=False),
|
|
sa.Column('device_type_id', sa.Integer(), nullable=False),
|
|
sa.Column('organization_id', sa.Integer(), nullable=False),
|
|
sa.Column('mac', sa.String(length=17), nullable=False),
|
|
sa.Column('note', sa.Text(), nullable=False),
|
|
sa.ForeignKeyConstraint(['device_type_id'], ['device_types.id'], ),
|
|
sa.ForeignKeyConstraint(['organization_id'], ['organizations.id'], ),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('organization_id', 'name')
|
|
)
|
|
op.create_index(op.f('ix_devices_organization_id'), 'devices', ['organization_id'], unique=False)
|
|
op.create_table('isps',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(length=255), nullable=False),
|
|
sa.Column('organization_id', sa.Integer(), nullable=False),
|
|
sa.Column('contract_number', sa.String(length=100), nullable=False),
|
|
sa.Column('hotline', sa.String(length=50), nullable=False),
|
|
sa.Column('note', sa.Text(), nullable=False),
|
|
sa.ForeignKeyConstraint(['organization_id'], ['organizations.id'], ),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_isps_organization_id'), 'isps', ['organization_id'], unique=False)
|
|
op.create_table('vrfs',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('organization_id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(length=100), nullable=False),
|
|
sa.Column('route_target', sa.String(length=50), nullable=False),
|
|
sa.Column('note', sa.Text(), nullable=False),
|
|
sa.ForeignKeyConstraint(['organization_id'], ['organizations.id'], ),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('organization_id', 'name')
|
|
)
|
|
op.create_table('isp_networks',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('isp_id', sa.Integer(), nullable=False),
|
|
sa.Column('cidr', postgresql.CIDR(), nullable=False),
|
|
sa.ForeignKeyConstraint(['isp_id'], ['isps.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_isp_networks_isp_id'), 'isp_networks', ['isp_id'], unique=False)
|
|
op.create_table('prefixes',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('organization_id', sa.Integer(), nullable=False),
|
|
sa.Column('vrf_id', sa.Integer(), nullable=False),
|
|
sa.Column('prefix', postgresql.CIDR(), nullable=False),
|
|
sa.Column('description', sa.String(length=500), nullable=False),
|
|
sa.Column('status', sa.Enum('active', 'reserved', 'deprecated', name='prefix_status'), nullable=False),
|
|
sa.Column('parent_id', sa.Integer(), nullable=True),
|
|
sa.Column('is_pool', sa.Boolean(), nullable=False),
|
|
sa.Column('note', sa.Text(), nullable=False),
|
|
sa.ForeignKeyConstraint(['organization_id'], ['organizations.id'], ),
|
|
sa.ForeignKeyConstraint(['parent_id'], ['prefixes.id'], ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['vrf_id'], ['vrfs.id'], ),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('vrf_id', 'prefix')
|
|
)
|
|
op.create_index(op.f('ix_prefixes_organization_id'), 'prefixes', ['organization_id'], unique=False)
|
|
op.create_index(op.f('ix_prefixes_vrf_id'), 'prefixes', ['vrf_id'], unique=False)
|
|
op.create_table('addresses',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('prefix_id', sa.Integer(), nullable=False),
|
|
sa.Column('address', postgresql.INET(), nullable=False),
|
|
sa.Column('status', sa.Enum('assigned', 'reserved', 'deprecated', name='address_status'), nullable=False),
|
|
sa.Column('dns_name', sa.String(length=255), nullable=False),
|
|
sa.Column('description', sa.String(length=500), nullable=False),
|
|
sa.Column('device_id', sa.Integer(), nullable=True),
|
|
sa.Column('note', sa.Text(), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['device_id'], ['devices.id'], ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['prefix_id'], ['prefixes.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('prefix_id', 'address')
|
|
)
|
|
op.create_index(op.f('ix_addresses_device_id'), 'addresses', ['device_id'], unique=False)
|
|
op.create_index(op.f('ix_addresses_prefix_id'), 'addresses', ['prefix_id'], unique=False)
|
|
# ### end Alembic commands ###
|
|
|
|
|
|
def downgrade() -> None:
|
|
# ### commands auto generated by Alembic - please adjust! ###
|
|
op.drop_index(op.f('ix_addresses_prefix_id'), table_name='addresses')
|
|
op.drop_index(op.f('ix_addresses_device_id'), table_name='addresses')
|
|
op.drop_table('addresses')
|
|
op.drop_index(op.f('ix_prefixes_vrf_id'), table_name='prefixes')
|
|
op.drop_index(op.f('ix_prefixes_organization_id'), table_name='prefixes')
|
|
op.drop_table('prefixes')
|
|
op.drop_index(op.f('ix_isp_networks_isp_id'), table_name='isp_networks')
|
|
op.drop_table('isp_networks')
|
|
op.drop_table('vrfs')
|
|
op.drop_index(op.f('ix_isps_organization_id'), table_name='isps')
|
|
op.drop_table('isps')
|
|
op.drop_index(op.f('ix_devices_organization_id'), table_name='devices')
|
|
op.drop_table('devices')
|
|
op.drop_table('users')
|
|
op.drop_table('organizations')
|
|
op.drop_table('device_types')
|
|
op.drop_index(op.f('ix_audit_log_ts'), table_name='audit_log')
|
|
op.drop_index(op.f('ix_audit_log_entity_type'), table_name='audit_log')
|
|
op.drop_table('audit_log')
|
|
# ### end Alembic commands ###
|