"""add drone pose fields to images + seed drone prompt & schema + make source nullable Revision ID: 0002_drone_pose_fields_and_schema Revises: 0001_initial_schema_and_seed Create Date: 2025-08-19 00:00:00.000000 """ from alembic import op import sqlalchemy as sa import json # Alembic identifiers revision = "0002_drone_fields" down_revision = "b8fc40bfe3c7" branch_labels = None depends_on = None def upgrade(): # -------- Make source field nullable for drone images -------- op.alter_column("images", "source", nullable=True) # -------- Add image pose/accuracy columns (all nullable) -------- op.add_column("images", sa.Column("center_lon", sa.Float(precision=53), nullable=True)) op.add_column("images", sa.Column("center_lat", sa.Float(precision=53), nullable=True)) op.add_column("images", sa.Column("amsl_m", sa.Float(precision=53), nullable=True)) op.add_column("images", sa.Column("agl_m", sa.Float(precision=53), nullable=True)) op.add_column("images", sa.Column("heading_deg", sa.Float(precision=53), nullable=True)) op.add_column("images", sa.Column("yaw_deg", sa.Float(precision=53), nullable=True)) op.add_column("images", sa.Column("pitch_deg", sa.Float(precision=53), nullable=True)) op.add_column("images", sa.Column("roll_deg", sa.Float(precision=53), nullable=True)) op.add_column("images", sa.Column("rtk_fix", sa.Boolean(), nullable=True)) op.add_column("images", sa.Column("std_h_m", sa.Float(precision=53), nullable=True)) op.add_column("images", sa.Column("std_v_m", sa.Float(precision=53), nullable=True)) # -------- Sanity checks (permit NULL) -------- op.create_check_constraint( "chk_images_center_lat", "images", "(center_lat IS NULL) OR (center_lat BETWEEN -90 AND 90)", ) op.create_check_constraint( "chk_images_center_lon", "images", "(center_lon IS NULL) OR (center_lon BETWEEN -180 AND 180)", ) op.create_check_constraint( "chk_images_heading_deg", "images", "(heading_deg IS NULL) OR (heading_deg >= 0 AND heading_deg <= 360)", ) op.create_check_constraint( "chk_images_pitch_deg", "images", "(pitch_deg IS NULL) OR (pitch_deg BETWEEN -90 AND 90)", ) op.create_check_constraint( "chk_images_yaw_deg", "images", "(yaw_deg IS NULL) OR (yaw_deg BETWEEN -180 AND 180)", ) op.create_check_constraint( "chk_images_roll_deg", "images", "(roll_deg IS NULL) OR (roll_deg BETWEEN -180 AND 180)", ) # -------- Seed: default DRONE prompt (mirrors crisis-map prompt row) -------- op.execute( sa.text( """ INSERT INTO prompts (p_code, label, metadata_instructions) VALUES (:code, :label, :meta) ON CONFLICT (p_code) DO NOTHING """ ).bindparams( code="DEFAULT_DRONE_IMAGE", label=( "Analyze this drone image and provide an objective, concise description " "of what is visible (people, infrastructure, damage, hazards, access, context)." ), meta=( "Additionally, extract the following metadata in JSON format. All fields are optional - use null when unknown:\n\n" "- title: concise title (<= 10 words)\n" "- source: if applicable, choose from: PDC, GDACS, WFP, GFH, GGC, USGS, OTHER, otherwise null\n" "- type: if applicable, choose from: BIOLOGICAL_EMERGENCY, CHEMICAL_EMERGENCY, CIVIL_UNREST, COLD_WAVE, COMPLEX_EMERGENCY, CYCLONE, DROUGHT, EARTHQUAKE, EPIDEMIC, FIRE, FLOOD, FLOOD_INSECURITY, HEAT_WAVE, INSECT_INFESTATION, LANDSLIDE, OTHER, PLUVIAL, POPULATION_MOVEMENT, RADIOLOGICAL_EMERGENCY, STORM, TRANSPORTATION_EMERGENCY, TSUNAMI, VOLCANIC_ERUPTION, otherwise null\n" "- countries: if applicable, use ISO-2 codes (e.g., ['US','PA']), otherwise null\n" "- epsg: if applicable, choose from: 4326, 3857, 32617, 32633, 32634, OTHER, otherwise null\n" "- center_lat (-90..90), center_lon (-180..180)\n" "- amsl_m, agl_m\n" "- heading_deg (0..360), yaw_deg (-180..180), pitch_deg (-90..90), roll_deg (-180..180)\n" "- rtk_fix (boolean), std_h_m (>=0), std_v_m (>=0)\n\n" "Return ONLY the JSON object (no markdown) in this envelope:\n" "{\n" ' "analysis": "detailed description...",\n' " \"metadata\": {\n" ' "title": "...",\n' ' "source": ,\n' ' "type": ,\n' ' "countries": ,\n' ' "epsg": ,\n' ' "center_lat": ,\n' ' "center_lon": ,\n' ' "amsl_m": ,\n' ' "agl_m": ,\n' ' "heading_deg": ,\n' ' "yaw_deg": ,\n' ' "pitch_deg": ,\n' ' "roll_deg": ,\n' ' "rtk_fix": ,\n' ' "std_h_m": ,\n' ' "std_v_m": \n' " }\n" "}" ), ) ) # -------- Seed: DRONE caption JSON schema -------- schema = { "type": "object", "properties": { "analysis": {"type": "string"}, "metadata": { "type": "object", "properties": { "title": {"type": ["string", "null"]}, "source": {"type": ["string", "null"]}, "type": {"type": ["string", "null"]}, "countries": {"type": ["array", "null"], "items": {"type": "string"}}, "epsg": {"type": ["string", "null"]}, "center_lat": {"type": ["number", "null"], "minimum": -90, "maximum": 90}, "center_lon": {"type": ["number", "null"], "minimum": -180, "maximum": 180}, "amsl_m": {"type": ["number", "null"]}, "agl_m": {"type": ["number", "null"]}, "heading_deg": {"type": ["number", "null"], "minimum": 0, "maximum": 360}, "yaw_deg": {"type": ["number", "null"], "minimum": -180, "maximum": 180}, "pitch_deg": {"type": ["number", "null"], "minimum": -90, "maximum": 90}, "roll_deg": {"type": ["number", "null"], "minimum": -180, "maximum": 180}, "rtk_fix": {"type": ["boolean", "null"]}, "std_h_m": {"type": ["number", "null"], "minimum": 0}, "std_v_m": {"type": ["number", "null"], "minimum": 0}, }, }, }, "required": ["analysis", "metadata"], } op.execute( sa.text( """ INSERT INTO json_schemas (schema_id, title, schema, version) VALUES (:id, :title, CAST(:schema AS JSONB), :ver) ON CONFLICT (schema_id) DO NOTHING """ ).bindparams( id="drone_caption@1.0.0", title="Drone Caption Schema", schema=json.dumps(schema, separators=(",", ":")), ver="1.0.0", ) ) def downgrade(): # Remove seeded rows op.execute(sa.text("DELETE FROM json_schemas WHERE schema_id = :id"), {"id": "drone_caption@1.0.0"}) op.execute(sa.text("DELETE FROM prompts WHERE p_code = :code"), {"code": "DEFAULT_DRONE_IMAGE"}) # Drop check constraints op.drop_constraint("chk_images_roll_deg", "images", type_="check") op.drop_constraint("chk_images_yaw_deg", "images", type_="check") op.drop_constraint("chk_images_pitch_deg", "images", type_="check") op.drop_constraint("chk_images_heading_deg", "images", type_="check") op.drop_constraint("chk_images_center_lon", "images", type_="check") op.drop_constraint("chk_images_center_lat", "images", type_="check") # Drop columns op.drop_column("images", "std_v_m") op.drop_column("images", "std_h_m") op.drop_column("images", "rtk_fix") op.drop_column("images", "roll_deg") op.drop_column("images", "pitch_deg") op.drop_column("images", "yaw_deg") op.drop_column("images", "heading_deg") op.drop_column("images", "agl_m") op.drop_column("images", "amsl_m") op.drop_column("images", "center_lat") op.drop_column("images", "center_lon") # Make source field non-nullable again op.alter_column("images", "source", nullable=False)