06 database schema

6) Database Schema Changes

6.1 New Tables

state_climate_regions:

CREATE TABLE state_climate_regions (
    id INTEGER PRIMARY KEY,
    state_code TEXT NOT NULL,
    region_name TEXT NOT NULL,
    usda_zones TEXT,  -- JSON array
    key_cities TEXT,  -- JSON array
    avg_last_frost_month INTEGER,
    avg_last_frost_day_start INTEGER,
    avg_last_frost_day_end INTEGER,
    avg_first_frost_month INTEGER,
    avg_first_frost_day_start INTEGER,
    avg_first_frost_day_end INTEGER,
    growing_season_days_min INTEGER,
    growing_season_days_max INTEGER,
    typical_soil_types TEXT,  -- JSON array
    soil_ph_min REAL,
    soil_ph_max REAL,
    elevation_range TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

state_native_plants (cache):

CREATE TABLE state_native_plants (
    id INTEGER PRIMARY KEY,
    plant_id INTEGER,
    state_code TEXT NOT NULL,
    nativity_status TEXT,  -- native, introduced, invasive
    source TEXT,  -- USDA PLANTS, state extension
    last_updated TIMESTAMP,
    FOREIGN KEY (plant_id) REFERENCES plants(id)
);

state_invasives (cache):

CREATE TABLE state_invasives (
    id INTEGER PRIMARY KEY,
    plant_id INTEGER,
    state_code TEXT NOT NULL,
    invasive_status TEXT,  -- invasive, watch list, prohibited
    eddmaps_id TEXT,
    source TEXT,
    notes TEXT,
    last_updated TIMESTAMP,
    FOREIGN KEY (plant_id) REFERENCES plants(id)
);

user_retailers (user-contributed):

CREATE TABLE user_retailers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    street_address TEXT,
    city TEXT,
    state_code TEXT,
    zip TEXT,
    latitude REAL,
    longitude REAL,
    website TEXT,
    phone TEXT,
    description TEXT,
    specialties TEXT,  -- JSON array
    is_community_added BOOLEAN DEFAULT 1,
    added_by_user_id TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

6.2 Schema Migrations

locations table:

• Rename or deprecate: georgia_region → climate_region_id
• Add: state_code TEXT
• Add: county TEXT
• Add: elevation_ft INTEGER

frost_dates table (already exists):

• Add: data_source TEXT (user, NOAA, extension)
• Add: probability TEXT (10%, 50%, 90%)