"""Initial schema. Revision ID: 0001 Revises: Create Date: 2026-09-14 Canonical vocabulary is AFP / avalanche.org: danger 0-5 per band (upper/middle/lower), problem types by AFP avalanche_problem_id (1-9), locations as " ", likelihood 1-5, size as D-scale numeric. """ from alembic import op revision = "0001" down_revision = None branch_labels = None depends_on = None UPGRADE = """ CREATE EXTENSION IF NOT EXISTS citext; CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE TABLE centers ( id serial PRIMARY KEY, slug text NOT NULL UNIQUE, name text NOT NULL, afp_center_id text, timezone text NOT NULL DEFAULT 'America/Los_Angeles', locale text NOT NULL DEFAULT 'en', danger_scale text NOT NULL DEFAULT 'NAC' CHECK (danger_scale IN ('NAC', 'SAC')), created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE zones ( id serial PRIMARY KEY, center_id int NOT NULL REFERENCES centers(id) ON DELETE CASCADE, name text NOT NULL, afp_zone_id text, band_labels jsonb, UNIQUE (center_id, name) ); CREATE TABLE users ( id serial PRIMARY KEY, email citext NOT NULL UNIQUE, name text NOT NULL, password_hash text, external_auth_id text UNIQUE, is_admin boolean NOT NULL DEFAULT false, is_active boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now(), last_login_at timestamptz ); CREATE TABLE memberships ( user_id int NOT NULL REFERENCES users(id) ON DELETE CASCADE, center_id int NOT NULL REFERENCES centers(id) ON DELETE CASCADE, role text NOT NULL CHECK (role IN ('center_admin', 'forecaster')), PRIMARY KEY (user_id, center_id) ); CREATE TABLE invites ( id serial PRIMARY KEY, token_hash text NOT NULL UNIQUE, email citext NOT NULL, center_id int REFERENCES centers(id) ON DELETE CASCADE, role text CHECK (role IN ('center_admin', 'forecaster')), is_admin boolean NOT NULL DEFAULT false, created_by int REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now(), expires_at timestamptz NOT NULL, accepted_at timestamptz, accepted_user_id int REFERENCES users(id) ); CREATE TABLE sessions ( id text PRIMARY KEY, -- sha256 of the cookie token user_id int NOT NULL REFERENCES users(id) ON DELETE CASCADE, csrf_token text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), last_seen_at timestamptz NOT NULL DEFAULT now(), expires_at timestamptz NOT NULL ); CREATE INDEX sessions_user_idx ON sessions(user_id); -- Versioned, append-only center configuration (tables, rules, labels). CREATE TABLE center_configs ( id serial PRIMARY KEY, center_id int NOT NULL REFERENCES centers(id) ON DELETE CASCADE, version int NOT NULL, status text NOT NULL CHECK (status IN ('draft', 'proposed', 'active', 'retired')), config jsonb NOT NULL, rationale text, parent_id int REFERENCES center_configs(id), created_by int REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now(), proposed_at timestamptz, approved_by int REFERENCES users(id), approved_at timestamptz, UNIQUE (center_id, version) ); CREATE UNIQUE INDEX center_configs_one_active ON center_configs(center_id) WHERE status = 'active'; -- Published forecasts: imported history and (later) forecasts built with the tool. CREATE TABLE forecasts ( id serial PRIMARY KEY, center_id int NOT NULL REFERENCES centers(id) ON DELETE CASCADE, zone_id int REFERENCES zones(id), source text NOT NULL CHECK (source IN ('manual', 'drupal', 'nac_v2', 'afp_v3', 'tool')), source_id text NOT NULL, valid_date date NOT NULL, published_at timestamptz, title text, author text, danger_upper smallint CHECK (danger_upper BETWEEN 0 AND 5), danger_middle smallint CHECK (danger_middle BETWEEN 0 AND 5), danger_lower smallint CHECK (danger_lower BETWEEN 0 AND 5), danger_overall smallint, normal_caution boolean NOT NULL DEFAULT false, bottom_line text, hazard_discussion text, weather_discussion text, recent_activity text, season text, -- 'legacy' = produced before the current avalanche-problem guidance; treat -- as descriptive history, not ground truth. guidance_era text NOT NULL DEFAULT 'legacy' CHECK (guidance_era IN ('legacy', 'current')), raw jsonb, imported_at timestamptz NOT NULL DEFAULT now(), tsv tsvector GENERATED ALWAYS AS ( to_tsvector('english', coalesce(bottom_line, '') || ' ' || coalesce(hazard_discussion, '')) ) STORED, UNIQUE (source, source_id) ); CREATE INDEX forecasts_center_date_idx ON forecasts(center_id, valid_date); CREATE INDEX forecasts_tsv_idx ON forecasts USING gin(tsv); CREATE TABLE forecast_problems ( id serial PRIMARY KEY, forecast_id int NOT NULL REFERENCES forecasts(id) ON DELETE CASCADE, rank smallint NOT NULL, problem_type smallint NOT NULL CHECK (problem_type BETWEEN 1 AND 9), locations text[] NOT NULL DEFAULT '{}', likelihood smallint CHECK (likelihood BETWEEN 1 AND 5), size_min numeric(2, 1), size_max numeric(2, 1), discussion text, source_problem_key text, UNIQUE (forecast_id, rank) ); CREATE INDEX forecast_problems_type_idx ON forecast_problems(problem_type); -- What the engine would have suggested for a forecast under a given config. CREATE TABLE forecast_suggestions ( id serial PRIMARY KEY, forecast_id int NOT NULL REFERENCES forecasts(id) ON DELETE CASCADE, config_id int NOT NULL REFERENCES center_configs(id) ON DELETE CASCADE, engine_version text NOT NULL, suggested_upper smallint, suggested_middle smallint, suggested_lower smallint, suggested_overall smallint, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (forecast_id, config_id, engine_version) ); -- Every wizard run: inputs, the engine's output, and the forecaster's final call. CREATE TABLE tool_runs ( id serial PRIMARY KEY, center_id int NOT NULL REFERENCES centers(id) ON DELETE CASCADE, zone_id int REFERENCES zones(id), user_id int NOT NULL REFERENCES users(id), forecast_id int REFERENCES forecasts(id), config_id int NOT NULL REFERENCES center_configs(id), engine_version text NOT NULL, valid_date date NOT NULL, status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'finalized')), inputs jsonb NOT NULL, computed jsonb NOT NULL, justifications jsonb NOT NULL DEFAULT '{}', suggested_upper smallint, suggested_middle smallint, suggested_lower smallint, suggested_overall smallint, final_upper smallint CHECK (final_upper BETWEEN 0 AND 5), final_middle smallint CHECK (final_middle BETWEEN 0 AND 5), final_lower smallint CHECK (final_lower BETWEEN 0 AND 5), final_overall smallint, final_problems jsonb, bottom_line text, notes text, client_mismatch boolean NOT NULL DEFAULT false, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), finalized_at timestamptz ); CREATE INDEX tool_runs_center_idx ON tool_runs(center_id, valid_date); CREATE TABLE ai_reviews ( id serial PRIMARY KEY, center_id int NOT NULL REFERENCES centers(id) ON DELETE CASCADE, tool_run_id int REFERENCES tool_runs(id) ON DELETE CASCADE, forecast_id int REFERENCES forecasts(id) ON DELETE CASCADE, user_id int REFERENCES users(id), mode text NOT NULL CHECK (mode IN ('review_draft', 'draft_bottom_line', 'sanity_check')), model text NOT NULL, prompt_version text NOT NULL, retrieved_forecast_ids int[] NOT NULL DEFAULT '{}', output jsonb, stop_reason text, fallback_model text, input_tokens int, output_tokens int, cache_read_tokens int, cache_write_tokens int, cost_usd numeric(10, 5), error text, feedback smallint CHECK (feedback IN (-1, 0, 1)), feedback_note text, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE import_jobs ( id serial PRIMARY KEY, source text NOT NULL, center_id int REFERENCES centers(id), params jsonb, status text NOT NULL DEFAULT 'running' CHECK (status IN ('running', 'done', 'failed')), counts jsonb, errors jsonb, started_at timestamptz NOT NULL DEFAULT now(), finished_at timestamptz ); """ DOWNGRADE = """ DROP TABLE IF EXISTS import_jobs, ai_reviews, tool_runs, forecast_suggestions, forecast_problems, forecasts, center_configs, sessions, invites, memberships, users, zones, centers CASCADE; """ def upgrade() -> None: op.execute(UPGRADE) def downgrade() -> None: op.execute(DOWNGRADE)