server/migrations/0001_init.sql

 1-- Devices registered for pushes. No location: the app sends a 5-degree
 2-- geomagnetic latitude band (signed, south negative) and its UTC offset.
 3CREATE TABLE devices (
 4  token TEXT PRIMARY KEY,
 5  environment TEXT NOT NULL CHECK (environment IN ('sandbox', 'production')),
 6  band INTEGER NOT NULL,
 7  utc_offset_minutes INTEGER NOT NULL,
 8  aurora INTEGER NOT NULL DEFAULT 1,
 9  last_seen TEXT NOT NULL
10);
11
12CREATE INDEX devices_band ON devices (band) WHERE aurora = 1;
13
14-- One alert per kind, band and event key (e.g. aurora per band per storm window).
15CREATE TABLE alerts_sent (
16  kind TEXT NOT NULL,
17  band INTEGER NOT NULL,
18  event TEXT NOT NULL,
19  sent_at TEXT NOT NULL,
20  PRIMARY KEY (kind, band, event)
21);
22
23-- Latest result per upstream feed.
24CREATE TABLE feed_snapshots (
25  feed TEXT PRIMARY KEY,
26  fetched_at TEXT NOT NULL,
27  ok INTEGER NOT NULL,
28  body TEXT
29);