enduro-trails/docs/work-items/ET-008/06-adr/ADR-005-storage-schema.md

type, work_item_id, adr_id, title, status, created_at, authors, supersedes, superseded_by, labels

type	work_item_id	adr_id	title	status	created_at	authors	supersedes	superseded_by	labels
adr	ET-008	ADR-005	ADR-005: Хранение публичных GPS-треков — отдельная БД data/gps_tracks.sqlite, SQLite+Spatialite, общая схема для всех источников, sources как JSON-массив	accepted	2026-06-01	agent:architect			arch:major-change

ADR-005 — Схема хранения публичных GPS-треков

Статус

Accepted

Контекст

ET-008 вводит новый класс данных в проект — публичные GPS-треки, агрегированные офлайн-pipeline'ом из ≥ 3 внешних источников по региону MVP (ЦФО + Чувашия). По BRD §3 целевой объём — ≥ 5000 треков, по BRD §6 предел — несколько ГБ на регион при дальнейшем расширении. По BRD §1 модель данных не пересекается с существующими сущностями:

• vector-tile слой trails (data/centralfederal.sqlite) — OSM-дороги/тропы, отдельный формат, отдельный pipeline (osm2pgsql-like);
• личные GPX-треки (ET-006) — живут только в памяти браузера (window.gpxTracks), на сервере не хранятся;
• POI и маршруты (PH-1/2) — другие сущности centralfederal.sqlite.

Архитектурно нужно решить:

1. Где хранить — в существующей centralfederal.sqlite или отдельным файлом.
2. Как организовать схему — одна таблица на все источники или партиционирование по источнику.
3. Как хранить мульти-источник (трек найден в N платформах после дедупа) — нормализованная таблица track_sources или JSON-массив в основной таблице.
4. Какие индексы дают приемлемый p95 ≤ 300 мс на bbox-запрос с фильтрами.
5. Совместимость с MVT-generation pipeline'ом, уже существующим в src/api/main.py для /api/tiles/{z}/{x}/{y}.mvt.

Рассмотренные варианты

Вариант D (Database) — где хранить

• D-A — отдельный файл data/gps_tracks.sqlite (выбран, совпадает с BRD §7 и TRZ §8 ADR-001-recommendation). Плюсы:

  • Pipeline пишет в свою БД — нет блокировок write на centralfederal.sqlite, который активно читается API под нагрузкой раздачи MVT.
  • Независимый цикл бэкапа (см. 07-infra-requirements.md §4): gps_tracks.sqlite бэкапится ежедневно, centralfederal.sqlite — после редкой ребилд-сессии OSM-данных.
  • Независимая ротация: ретеншн 5 лет (REQ-NF-03) применяется только к одной БД; centralfederal.sqlite пересобирается из OSM по своему графику.
  • Изоляция риска при ошибке pipeline — нельзя случайно повредить OSM-данные.
  • В будущем (BRD §6 риск роста до миллионов треков) переход на PostGIS затрагивает один файл, а не корневую БД. Минусы:
  • Второй коннект из FastAPI (мелкая сложность, ~10 строк в main.py).
  • При совместных запросах «дороги OSM × публичные треки рядом» (PH-3 Smart Route) — кросс-БД JOIN неэффективен. Принято: на горизонте MVP таких запросов нет; в PH-3 решается отдельным ADR (вариант: ATTACH DATABASE или денормализация в материализованную таблицу).

• D-B — в существующую centralfederal.sqlite, отдельные таблицы gps_tracks_*. Отклонён:

  • Pipeline writer и MVT reader конкурируют за один файл; SQLite WAL смягчает, но не устраняет.
  • Backup-цикл становится зависимым: невозможно ребилдить OSM-данные не «остановив» pipeline.
  • Сценарий «удалить весь gps-датасет и пересобрать» (R-3 ниже) требует DROP TABLE в большой production-БД; в отдельном файле — rm data/gps_tracks.sqlite && python scripts/gps_collect.py.

• D-C — PostGIS. Отклонён:

  • BRD §1 «SQLite по умолчанию, PostgreSQL когда нужно». ≥ 5000 треков для ЦФО легко влезают в SQLite (оценочно ≤ 500 МБ при средней геометрии 1240 точек × 16 байт). Spatialite даёт BLOB+R-tree, чего хватает для всех запросов TRZ.
  • Введение PostgreSQL — новый класс инфры (контейнер + бэкап + миграции через alembic). Это arch:major-change уровня всего проекта; ET-008 такого не требует.

Вариант T (Table layout) — одна или несколько таблиц

• T-A — единая таблица tracks (выбран). Поля per-источник денормализованы в JSON-колонки. Все источники приводятся к общему контракту в models.py::Track (TRZ §7). Плюсы:
  • Самый простой bbox-запрос: один SELECT с одним bbox-фильтром.
  • Дедупликация на уровне БД через UNIQUE-индекс по dedup_key (TRZ REQ-F-08).
  • MVT-генерация на низком зуме — одно сканирование R-tree → одна LineString → MVT петля.
• T-B — таблица на источник + view tracks_all UNION ALL .... Отклонён:
  • Дедупликация между источниками превращается в кросс-таблицу процедуру.
  • Изменение списка источников требует DDL-миграции, что блокирует «расширяемость на новый регион ≤ 30 строк YAML без правки кода» (BRD-метрика).

Вариант S (Sources field) — как хранить N источников у одного трека

• S-A — JSON-массив в колонках sources_json, external_urls_json (выбран, совпадает с TRZ REQ-F-09). Плюсы:
  • Запись/чтение трека — атомарная операция.
  • При мерже дубликата UPDATE sources_json = json_array_union(...) через Python-сторону (без JSON1-функций SQLite, чтобы не зависеть от SQLite-версии).
  • Фильтр API «source=osm,ttrails» работает через bbox-prefetch + Python-постфильтр (≤ 500 треков на bbox — это O(500) проверка 'osm' in sources, ничтожно). Минусы:
  • Невозможно индексировать массив без JSON1; нет нативного WHERE 'osm' = ANY(sources). Принято: на BRD-объёме это не узкое место.
• S-B — нормализованная таблица track_sources(track_id, source_id, ext_url). Отклонён:
  • JOIN на каждый bbox-запрос (1 → N запись на трек) +30–60% к p95.
  • Усложняет API: GeoJSON-формирование требует aggregate-функции (group_concat) → лишний SQL.
  • Не даёт значимого выигрыша на BRD-объёме (≤ 5–10 источников на трек после дедупа в худшем случае).

Вариант I (Indexes) — как ускорить bbox-фильтр

• I-A — Spatialite R-tree через виртуальную таблицу idx_tracks_geom + обычный B-tree на activity_type (выбран).
  • R-tree даёт O(log n) на bbox-prefetch.
  • idx_tracks_activity ускоряет fallback-фильтр.
  • created_at — обычный B-tree для GC и для health-отчёта.
• I-B — четыре B-tree-индекса на min_lon, max_lon, min_lat, max_lat (вариант из TRZ REQ-F-09). Отклонён:
  • SQLite-оптимизатор не комбинирует 4 индекса в bbox-плане; в лучшем случае использует один (по min_lon), что даёт линейный полу-скан.
  • R-tree через Spatialite — стандартный паттерн для spatial-запросов; уже используется в centralfederal.sqlite (idx_features_geom).

Вариант W (WAL) — режим записи

• W-A — WAL-mode постоянно (выбран). При запуске pipeline PRAGMA journal_mode=WAL. Даёт читателям (FastAPI) видеть консистентный снэпшот пока pipeline пишет.
• W-B — DELETE-mode + блокировка читателей на время прогона. Отклонён: означает простой /api/gps-tracks на 1–6 часов в неделю.

Решение

Принимается комбинация: D-A + T-A + S-A + I-A + W-A.

1. Отдельная БД data/gps_tracks.sqlite (Spatialite-extension загружается при коннекте). Путь в окружении — GPS_TRACKS_DB_PATH=/app/data/gps_tracks.sqlite (см. 07-infra-requirements.md §5).

2. Единая таблица tracks со схемой, зафиксированной в 08-data-requirements.md §3. Уточнения относительно TRZ REQ-F-09:

   • points_count и length_m — посчитанные на pipeline (НФТ Endpoint p95 ≤ 300 мс не оставляет бюджета считать длину на лету).
   • min_lon/max_lon/min_lat/max_lat сохраняются денормализованно вместе с R-tree (избыточно, но ускоряет MVT-генерацию: можно отбросить трек до wkb_to_coords() если bbox целиком вне тайла).
   • tags_json, description — допускается NULL (не все источники их отдают).
   • user (имя автора) сохраняется только если ADR licensing соответствующего источника явно разрешает (см. ADR-009/010/011). Иначе — NULL.

3. sources_json и external_urls_json — JSON-массивы строк, длина ≤ 8 элементов (дополнительные источники после дедупа). Порядок — стабильный (по gps_sources.yaml), что фиксирует «первый» источник для MVT-фичи properties.source (используется для цветовой палитры по умолчанию, REQ-F-16).

4. Индексация:

   • Spatialite R-tree idx_tracks_geom через CreateSpatialIndex('tracks', 'geom').
   • B-tree idx_tracks_activity(activity_type).
   • B-tree idx_tracks_created(created_at) для GC и health.
   • UNIQUE idx_tracks_dedup(dedup_key) — критичен для ON CONFLICT логики dedup (ADR-006).
   • Дополнительный bbox-индекс из TRZ REQ-F-09 (min_lon, max_lon, min_lat, max_lat) не создаётся — R-tree его покрывает; B-tree на 4 колонки даст overhead на INSERT без выгоды на SELECT.

5. WAL-mode включается в db.py::open_connection() через PRAGMA journal_mode=WAL при первом запуске; повторно команда no-op. Pipeline пишет в WAL, читатели видят последний checkpoint. После каждого (region, source) pipeline вызывает PRAGMA wal_checkpoint(PASSIVE) для контроля размера WAL-файла.

6. Размер БД оценивается ≤ 2 ГБ для ЦФО+Чувашии при ≥ 5000 треков (REQ-NF-03). Метрика db_size_mb — в /api/gps-tracks/health (REQ-F-12), порог-алерт > 2 ГБ — в 10-tech-risks.md R-4.

7. Pipeline-история — таблица pipeline_runs (TRZ REQ-F-09) в той же БД. Используется только для health-эндпоинта и оператора. Не индексируется по region/source — её объём ≤ 10⁴ строк за годы.

8. Совместимость с MVT-pipeline в main.py. Утилитарные функции tile_to_bbox, wkb_to_coords, simplify_coords уже существуют в src/api/main.py для слоя trails. ET-008 не рефакторит их (out of scope, риск регрессии слоя trails). Вместо этого:

   • В src/api/gps_tracks/mvt.py функции _tile_to_bbox / _wkb_to_coords дублируются с TODO-комментарием и ссылкой на тех-долг (10-tech-risks.md R-7).
   • Если в будущей фазе появится третий MVT-источник (BRD §1 «Видеть реальные дороги/тропы»), перед ним вводится shared-модуль src/api/tiles_util.py отдельным work item.

9. Cross-DB запросы (PH-3) — out of scope. Принципиальный путь, если понадобится в Smart Route: ATTACH DATABASE 'data/gps_tracks.sqlite' AS gps в коннекте main-API. Это решение откладывается до конкретной задачи PH-3.

Последствия

Положительные

• Pipeline пишет, не блокируя API-чтения OSM-данных.
• Бэкап и ротация независимы — оператор управляет каждой БД отдельно.
• Расширение списка источников (BRD F-04) или регионов (BRD F-12) не требует DDL — только обновление YAML.
• При ошибке pipeline (повреждение БД) — rm data/gps_tracks.sqlite && python scripts/gps_collect.py восстанавливает за один прогон (≤ 6 часов, REQ-NF-02). Это закрывает риск «pipeline испортил продакшен-данные».
• Spatialite R-tree обеспечивает p95 ≤ 300 мс на bbox-запросах без необходимости PostgreSQL.

Отрицательные / ограничения

• Денормализация sources_json/external_urls_json не позволяет нативного WHERE 'osm' = ANY(sources). Фильтр source — постфильтр на Python после bbox-prefetch (приемлемо: BRD §6 показывает ≤ 500 треков на bbox).
• Дублирование tile_to_bbox / wkb_to_coords между main.py и gps_tracks/mvt.py — технический долг (10-tech-risks.md R-7). При следующем добавлении MVT-источника обязательно вынести в shared util.
• Cross-DB запросы между OSM-данными и GPS-треками невозможны без ATTACH DATABASE. На горизонте MVP таких запросов нет, но это блокер для будущей фичи «маршрут предпочитает реально-езженые дороги» (PH-3).
• Дублирование bbox-полей (min_lon/max_lon/min_lat/max_lat) в строке трека + R-tree-индексе — избыточные ~32 байта на трек; на 5000 треков ничтожно, осознанный compromise ради быстрого «бросить трек до парсинга WKB».

Технический долг

• Если объём вырастает > 2 ГБ (расширение на всю РФ), перевод на PostGIS. Контракт API /api/gps-tracks/* стабилен; меняется только db.py. Backend-код, фронтенд, миграции — без изменений.
• Возможный future-rewrite на shared src/api/tiles_util.py (см. §8 решения).

Классификация изменения

Major change. Введение новой БД на сервере явно перечислено в правилах для агентов (CLAUDE.md, эскалация: «новый сервис, новая БД → arch:major-change»). Лейбл arch:major-change выставлен. Обязательный архитектурный approve — да.

Связанные документы

• docs/work-items/ET-008/01-brd.md §7 «БД»
• docs/work-items/ET-008/02-trz.md REQ-F-09 «Схема БД»
• docs/work-items/ET-008/06-adr/ADR-006-dedup-algorithm.md
• docs/work-items/ET-008/06-adr/ADR-007-pipeline-architecture.md
• docs/work-items/ET-008/07-infra-requirements.md §4 «Хранилища данных»
• docs/work-items/ET-008/08-data-requirements.md §3 «Серверные данные»
• docs/work-items/ET-008/10-tech-risks.md R-3, R-4, R-7