mybeacon-backend/backend/alembic/versions/20251229_0118_f06499634fff_add_monitoring_tables.py

"""add_monitoring_tables

Revision ID: f06499634fff
Revises: c1148f55dcd9
Create Date: 2025-12-29 01:18:05.570528+00:00

"""
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 = 'f06499634fff'
down_revision: Union[str, None] = 'c1148f55dcd9'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Create host_metrics table
    op.create_table(
        'host_metrics',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False),
        sa.Column('cpu_percent', sa.Float(), nullable=False),
        sa.Column('cpu_count', sa.Integer(), nullable=False),
        sa.Column('memory_total', sa.BigInteger(), nullable=False),
        sa.Column('memory_used', sa.BigInteger(), nullable=False),
        sa.Column('memory_percent', sa.Float(), nullable=False),
        sa.Column('load_1', sa.Float(), nullable=False),
        sa.Column('load_5', sa.Float(), nullable=False),
        sa.Column('load_15', sa.Float(), nullable=False),
        sa.Column('disk_read_bytes', sa.BigInteger(), nullable=False),
        sa.Column('disk_write_bytes', sa.BigInteger(), nullable=False),
        sa.Column('disk_usage_percent', sa.Float(), nullable=False),
        sa.Column('net_sent_bytes', sa.BigInteger(), nullable=False),
        sa.Column('net_recv_bytes', sa.BigInteger(), nullable=False),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_host_metrics_timestamp', 'host_metrics', ['timestamp'])

    # Create security_events table
    op.create_table(
        'security_events',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False),
        sa.Column('event_type', sa.String(length=50), nullable=False),
        sa.Column('severity', sa.String(length=20), nullable=False),
        sa.Column('ip_address', sa.String(length=45), nullable=True),
        sa.Column('user_agent', sa.Text(), nullable=True),
        sa.Column('endpoint', sa.String(length=255), nullable=True),
        sa.Column('description', sa.Text(), nullable=False),
        sa.Column('event_metadata', postgresql.JSON(astext_type=sa.Text()), nullable=True),
        sa.Column('resolved', sa.Boolean(), nullable=False, server_default='false'),
        sa.Column('resolved_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('resolved_by', sa.Integer(), nullable=True),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_security_events_timestamp', 'security_events', ['timestamp'])
    op.create_index('ix_security_events_event_type', 'security_events', ['event_type'])
    op.create_index('ix_security_events_ip_address', 'security_events', ['ip_address'])
    op.create_index('ix_security_events_resolved', 'security_events', ['resolved'])

    # Create alerts table
    op.create_table(
        'alerts',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False),
        sa.Column('alert_type', sa.String(length=50), nullable=False),
        sa.Column('severity', sa.String(length=20), nullable=False),
        sa.Column('title', sa.String(length=255), nullable=False),
        sa.Column('message', sa.Text(), nullable=False),
        sa.Column('alert_metadata', postgresql.JSON(astext_type=sa.Text()), nullable=True),
        sa.Column('acknowledged', sa.Boolean(), nullable=False, server_default='false'),
        sa.Column('acknowledged_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('acknowledged_by', sa.Integer(), nullable=True),
        sa.Column('dismissed', sa.Boolean(), nullable=False, server_default='false'),
        sa.Column('dismissed_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('sent_dashboard', sa.Boolean(), nullable=False, server_default='false'),
        sa.Column('sent_telegram', sa.Boolean(), nullable=False, server_default='false'),
        sa.Column('sent_email', sa.Boolean(), nullable=False, server_default='false'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_alerts_timestamp', 'alerts', ['timestamp'])
    op.create_index('ix_alerts_alert_type', 'alerts', ['alert_type'])
    op.create_index('ix_alerts_severity', 'alerts', ['severity'])
    op.create_index('ix_alerts_acknowledged', 'alerts', ['acknowledged'])
    op.create_index('ix_alerts_dismissed', 'alerts', ['dismissed'])


def downgrade() -> None:
    op.drop_table('alerts')
    op.drop_table('security_events')
    op.drop_table('host_metrics')