65 lines
1.9 KiB
Python
65 lines
1.9 KiB
Python
|
"""news_items table + article_summaries.model
|
||
|
|
|
||
|
|
Revision ID: 005_news_items
|
||
|
|
Revises: 004_camera_enum
|
||
|
|
Create Date: 2026-08-28
|
||
|
|
"""
|
||
|
|
|
||
|
|
from alembic import op
|
||
|
|
|
||
|
|
# revision identifiers, used by Alembic.
|
||
|
|
revision = "005_news_items"
|
||
|
|
down_revision = "004_camera_enum"
|
||
|
|
branch_labels = None
|
||
|
|
depends_on = None
|
||
|
|
|
||
|
|
|
||
|
|
def upgrade() -> None:
|
||
|
|
# Idempotent DDL: ingest (summarizer ensure_tables) may create the same
|
||
|
|
# shapes first depending on container startup order. IF NOT EXISTS makes
|
||
|
|
# both orders safe — whichever runs first wins, the other no-ops.
|
||
|
|
# One statement per op.execute: asyncpg rejects multi-command prepared
|
||
|
|
# statements (same style as 003_news).
|
||
|
|
op.execute(
|
||
|
|
"""
|
||
|
|
ALTER TABLE article_summaries
|
||
|
|
ADD COLUMN IF NOT EXISTS model TEXT
|
||
|
|
"""
|
||
|
|
)
|
||
|
|
op.execute(
|
||
|
|
"""
|
||
|
|
CREATE TABLE IF NOT EXISTS news_items (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
summary_id INTEGER REFERENCES article_summaries(id) ON DELETE CASCADE,
|
||
|
|
kind TEXT NOT NULL,
|
||
|
|
headline TEXT NOT NULL,
|
||
|
|
importance TEXT NOT NULL,
|
||
|
|
location_name TEXT,
|
||
|
|
lat DOUBLE PRECISION,
|
||
|
|
lon DOUBLE PRECISION,
|
||
|
|
location_confidence TEXT,
|
||
|
|
category TEXT,
|
||
|
|
url TEXT,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
)
|
||
|
|
"""
|
||
|
|
)
|
||
|
|
op.execute(
|
||
|
|
"""
|
||
|
|
CREATE INDEX IF NOT EXISTS ix_news_items_kind_created
|
||
|
|
ON news_items (kind, created_at DESC)
|
||
|
|
"""
|
||
|
|
)
|
||
|
|
op.execute(
|
||
|
|
"""
|
||
|
|
CREATE INDEX IF NOT EXISTS ix_news_items_map_bbox
|
||
|
|
ON news_items (lon, lat)
|
||
|
|
WHERE kind = 'map' AND lat IS NOT NULL AND lon IS NOT NULL
|
||
|
|
"""
|
||
|
|
)
|
||
|
|
|
||
|
|
|
||
|
|
def downgrade() -> None:
|
||
|
|
op.execute("DROP TABLE IF EXISTS news_items")
|
||
|
|
op.execute("ALTER TABLE article_summaries DROP COLUMN IF EXISTS model")
|