Weather

schema.sql 32 lines

CREATE TABLE locations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  lat REAL NOT NULL,
  lon REAL NOT NULL,
  added_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE weather_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  location_id INTEGER NOT NULL REFERENCES locations(id),
  temperature_c REAL,
  feels_like_c REAL,
  humidity_pct REAL,
  wind_speed_kmh REAL,
  weather_code INTEGER,
  condition TEXT,
  uv_index REAL,
  precipitation_mm REAL,
  fetched_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE forecasts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  location_id INTEGER NOT NULL REFERENCES locations(id),
  date TEXT NOT NULL /* Local calendar date at the saved location, YYYY-MM-DD. */,
  weather_code INTEGER /* Open-Meteo WMO weather code. */,
  condition TEXT /* Same English condition labels as weather_log and weather-updated. */,
  temperature_max_c REAL /* Daily maximum, degrees Celsius. */,
  temperature_min_c REAL /* Daily minimum, degrees Celsius. */,
  precipitation_mm REAL /* Daily total precipitation, millimeters. */,
  fetched_at TEXT DEFAULT (datetime('now')) /* Fetch time, UTC. */,
  UNIQUE(location_id, date)
);