thermograph/backend/alembic/versions/0002_climate_hypertables.py

"""climate record: TimescaleDB hypertables

Moves the raw daily climate record out of per-cell parquet files and into
Postgres, so the app queries the database instead of the filesystem (see
data/climate_store.py). Three objects:

* ``climate_history`` — a TimescaleDB hypertable of the full daily archive,
  durable/LOGGED (a 45-year, rate-limited refetch is expensive). Range-partitioned
  on ``date`` with a large 5-year chunk interval (the access pattern is
  all-rows-for-one-cell, so time chunking is incidental — a big interval keeps the
  chunk count tiny). Old chunks compress; the recent chunk stays writable for the
  hourly tail top-up.
* ``climate_recent`` — a plain table (NOT a hypertable) for the recent+forecast
  bundle. It holds *future* dates and is fully rewritten hourly, both of which
  fight time-partitioning.
* ``climate_sync`` — per-cell freshness (epoch seconds), replacing the parquet
  file mtimes that drove the topup cadence, the forecast TTL, and the
  ``recent_stamp`` token.

Postgres-only: guarded to no-op on the SQLite bind (tests / CI / offline Alembic),
where climate.py uses the parquet backend and these tables are never needed.

Revision ID: 0002_climate_hypertables
Revises: 0001_initial_schema
Create Date: 2026-07-20
"""
from alembic import op

revision = "0002_climate_hypertables"
down_revision = "0001_initial_schema"
branch_labels = None
depends_on = None


# The daily measurement columns shared by both tables (see data/climate_store.COLS).
_MEASURES = """
    tmax   DOUBLE PRECISION,
    tmin   DOUBLE PRECISION,
    precip DOUBLE PRECISION,
    wind   DOUBLE PRECISION,
    gust   DOUBLE PRECISION,
    humid  DOUBLE PRECISION,
    fmax   DOUBLE PRECISION,
    fmin   DOUBLE PRECISION,
    feels  DOUBLE PRECISION
"""


def upgrade() -> None:
    if op.get_bind().dialect.name != "postgresql":
        return  # SQLite fallback (tests/CI): climate uses the parquet backend.

    op.execute("CREATE EXTENSION IF NOT EXISTS timescaledb")

    # History: durable hypertable. PK (cell_id, date) includes the range-partition
    # column (date), which TimescaleDB requires of any unique index, and gives the
    # single-cell chronological scan the read path needs for free.
    op.execute(f"""
        CREATE TABLE climate_history (
            cell_id TEXT NOT NULL,
            date    DATE NOT NULL,
            {_MEASURES},
            PRIMARY KEY (cell_id, date)
        )
    """)
    op.execute(
        "SELECT create_hypertable('climate_history', "
        "by_range('date', INTERVAL '5 years'))")

    # Columnar compression, grouped per cell. Only chunks older than a year are
    # compressed — the hourly tail top-up only ever writes dates within the last
    # week, i.e. the current uncompressed chunk, so compression never collides with
    # a write.
    op.execute("""
        ALTER TABLE climate_history SET (
            timescaledb.compress,
            timescaledb.compress_segmentby = 'cell_id',
            timescaledb.compress_orderby   = 'date'
        )
    """)
    op.execute("SELECT add_compression_policy('climate_history', INTERVAL '365 days')")

    # Recent + forecast bundle: plain table (holds future dates, rewritten hourly).
    op.execute(f"""
        CREATE TABLE climate_recent (
            cell_id TEXT NOT NULL,
            date    DATE NOT NULL,
            {_MEASURES},
            PRIMARY KEY (cell_id, date)
        )
    """)

    # Per-cell freshness — epoch seconds (DOUBLE mirrors store.py's updated_at and
    # makes recent_stamp = int(recent_synced_at) trivial).
    op.execute("""
        CREATE TABLE climate_sync (
            cell_id           TEXT PRIMARY KEY,
            history_synced_at DOUBLE PRECISION,
            recent_synced_at  DOUBLE PRECISION,
            cols_version      SMALLINT NOT NULL DEFAULT 1
        )
    """)


def downgrade() -> None:
    if op.get_bind().dialect.name != "postgresql":
        return
    op.execute("DROP TABLE IF EXISTS climate_sync")
    op.execute("DROP TABLE IF EXISTS climate_recent")
    op.execute("DROP TABLE IF EXISTS climate_history")
    # Leave the timescaledb extension installed — other objects may rely on it and
    # dropping an extension is a heavier, rarely-wanted operation.