86 lines
2.2 KiB
Python
86 lines
2.2 KiB
Python
"""bot watcher
|
|
|
|
Revision ID: c8f3b6a2d571
|
|
Revises: a6d2f8b41c9e
|
|
Create Date: 2026-09-18 12:00:00.000000
|
|
|
|
"""
|
|
|
|
from collections.abc import Sequence
|
|
|
|
from alembic import op
|
|
|
|
revision: str = "c8f3b6a2d571"
|
|
down_revision: str | None = "a6d2f8b41c9e"
|
|
branch_labels: str | Sequence[str] | None = None
|
|
depends_on: str | Sequence[str] | None = None
|
|
|
|
_BOTS = """
|
|
CREATE TABLE watched_bots (
|
|
bot_id bigint PRIMARY KEY,
|
|
username text,
|
|
title text,
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
status text NOT NULL DEFAULT 'pending',
|
|
status_detail text,
|
|
backfilled_at timestamptz,
|
|
checked_at timestamptz,
|
|
added_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
)
|
|
"""
|
|
|
|
_UPDATES = """
|
|
CREATE TABLE bot_updates (
|
|
bot_id bigint NOT NULL,
|
|
kind text NOT NULL,
|
|
chat_id bigint NOT NULL,
|
|
item_id bigint NOT NULL,
|
|
ts timestamptz NOT NULL,
|
|
source text NOT NULL,
|
|
outgoing boolean NOT NULL DEFAULT false,
|
|
sender_id bigint,
|
|
sender_username text,
|
|
sender_name text,
|
|
sender_is_bot boolean,
|
|
chat_type text,
|
|
chat_title text,
|
|
text text,
|
|
media_kind text,
|
|
edited_at timestamptz,
|
|
received_at timestamptz NOT NULL DEFAULT now(),
|
|
raw jsonb NOT NULL,
|
|
PRIMARY KEY (bot_id, kind, chat_id, item_id, ts)
|
|
)
|
|
"""
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.execute(_BOTS)
|
|
op.execute(_UPDATES)
|
|
op.execute(
|
|
"SELECT create_hypertable('bot_updates', by_range('ts', INTERVAL '1 month'))"
|
|
)
|
|
op.execute(
|
|
"ALTER TABLE bot_updates SET ("
|
|
"timescaledb.enable_columnstore = true, "
|
|
"timescaledb.segmentby = 'bot_id', "
|
|
"timescaledb.orderby = 'ts DESC')"
|
|
)
|
|
op.execute(
|
|
"CALL add_columnstore_policy('bot_updates', after => INTERVAL '30 days')"
|
|
)
|
|
op.execute("CREATE INDEX ix_bot_updates_recent ON bot_updates (bot_id, ts DESC)")
|
|
op.execute(
|
|
"CREATE INDEX ix_bot_updates_sender ON bot_updates "
|
|
"(bot_id, sender_id, ts DESC) WHERE NOT outgoing"
|
|
)
|
|
op.execute(
|
|
"CREATE INDEX ix_bot_updates_chat ON bot_updates (bot_id, chat_id, ts DESC)"
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.execute("DROP TABLE IF EXISTS bot_updates")
|
|
op.execute("DROP TABLE IF EXISTS watched_bots")
|