87 lines
2.7 KiB
Python
87 lines
2.7 KiB
Python
"""watch names and webhooks
|
|
|
|
Revision ID: d8e3b5a1c7f4
|
|
Revises: d8f4a1c6e2b7
|
|
Create Date: 2026-10-19 03:00:00.000000
|
|
|
|
"""
|
|
|
|
from collections.abc import Sequence
|
|
|
|
from alembic import op
|
|
|
|
revision: str = "d8e3b5a1c7f4"
|
|
down_revision: str | None = "d8f4a1c6e2b7"
|
|
branch_labels: str | Sequence[str] | None = None
|
|
depends_on: str | Sequence[str] | None = None
|
|
|
|
_WEBHOOKS = """
|
|
CREATE TABLE webhooks (
|
|
id serial PRIMARY KEY,
|
|
account_id integer NOT NULL,
|
|
url text NOT NULL,
|
|
secret text NOT NULL,
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
watch_ids integer[],
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
)
|
|
"""
|
|
|
|
_DELIVERIES = """
|
|
CREATE TABLE webhook_deliveries (
|
|
id bigserial PRIMARY KEY,
|
|
webhook_id integer NOT NULL REFERENCES webhooks (id) ON DELETE CASCADE,
|
|
alert_id integer REFERENCES alerts (id) ON DELETE CASCADE,
|
|
event text NOT NULL,
|
|
status text NOT NULL DEFAULT 'pending',
|
|
attempts integer NOT NULL DEFAULT 0,
|
|
next_attempt_at timestamptz NOT NULL DEFAULT now(),
|
|
response_code integer,
|
|
last_error text,
|
|
body text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
finished_at timestamptz
|
|
)
|
|
"""
|
|
|
|
_LEGACY_KEYWORD = """
|
|
UPDATE watches
|
|
SET params = (params - 'keyword') || jsonb_build_object('pattern', params->'keyword')
|
|
WHERE kind = 'keyword' AND params ? 'keyword' AND NOT params ? 'pattern'
|
|
"""
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.execute("ALTER TABLE watches ADD COLUMN name text")
|
|
op.execute(_LEGACY_KEYWORD)
|
|
op.execute("ALTER TABLE alerts DROP CONSTRAINT alerts_watch_id_fkey")
|
|
op.execute(
|
|
"ALTER TABLE alerts ADD CONSTRAINT alerts_watch_id_fkey "
|
|
"FOREIGN KEY (watch_id) REFERENCES watches (id) ON DELETE CASCADE"
|
|
)
|
|
op.execute("CREATE INDEX ix_alerts_account_id ON alerts (account_id, id DESC)")
|
|
op.execute(_WEBHOOKS)
|
|
op.execute("CREATE INDEX ix_webhooks_account ON webhooks (account_id)")
|
|
op.execute(_DELIVERIES)
|
|
op.execute(
|
|
"CREATE INDEX ix_webhook_deliveries_due ON webhook_deliveries "
|
|
"(next_attempt_at) WHERE status = 'pending'"
|
|
)
|
|
op.execute(
|
|
"CREATE INDEX ix_webhook_deliveries_webhook ON webhook_deliveries "
|
|
"(webhook_id, id DESC)"
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.execute("DROP TABLE IF EXISTS webhook_deliveries")
|
|
op.execute("DROP TABLE IF EXISTS webhooks")
|
|
op.execute("DROP INDEX IF EXISTS ix_alerts_account_id")
|
|
op.execute("ALTER TABLE alerts DROP CONSTRAINT alerts_watch_id_fkey")
|
|
op.execute(
|
|
"ALTER TABLE alerts ADD CONSTRAINT alerts_watch_id_fkey "
|
|
"FOREIGN KEY (watch_id) REFERENCES watches (id)"
|
|
)
|
|
op.execute("ALTER TABLE watches DROP COLUMN name")
|