enduro-trails/migrations/gps_tracks_001_init.sql

PRAGMA journal_mode=WAL;
CREATE TABLE IF NOT EXISTS tracks (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    dedup_key       TEXT NOT NULL UNIQUE,
    name            TEXT,
    description     TEXT,
    activity_type   TEXT,
    user            TEXT,
    created_at      TEXT,
    length_m        REAL NOT NULL,
    points_count    INTEGER NOT NULL,
    min_lon         REAL NOT NULL,
    min_lat         REAL NOT NULL,
    max_lon         REAL NOT NULL,
    max_lat         REAL NOT NULL,
    geom            BLOB NOT NULL,
    sources_json    TEXT NOT NULL,
    external_urls_json TEXT NOT NULL,
    tags_json       TEXT,
    inserted_at     TEXT NOT NULL,
    updated_at      TEXT NOT NULL,
    source_priority INTEGER NOT NULL DEFAULT 999
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_tracks_dedup ON tracks(dedup_key);
CREATE INDEX IF NOT EXISTS idx_tracks_activity ON tracks(activity_type);
CREATE INDEX IF NOT EXISTS idx_tracks_created ON tracks(created_at);
CREATE INDEX IF NOT EXISTS idx_tracks_bbox ON tracks(min_lon, max_lon, min_lat, max_lat);

CREATE TABLE IF NOT EXISTS pipeline_runs (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    started_at      TEXT NOT NULL,
    finished_at     TEXT,
    region_id       TEXT NOT NULL,
    source_id       TEXT NOT NULL,
    status          TEXT NOT NULL,
    tracks_new      INTEGER DEFAULT 0,
    tracks_updated  INTEGER DEFAULT 0,
    errors_json     TEXT
);
CREATE INDEX IF NOT EXISTS idx_pipeline_started ON pipeline_runs(started_at);