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