julian
411b08d02f
Substantial design artifact + canonical-source ingest for the TRM business plane. Schema draft (synthesis): - wiki/synthesis/directus-schema-draft.md — working agreement for the multi-tenant schema. Pseudo multi-tenant under organizations; entries as the unit of timing; course definition (stages/segments/geofences/ waypoints/SLZs); penalty system "numbers in DB, math in code" with an evaluator registry and progressive bracket math; per-entry timing tables; per-stage start-order strategies (manual / previous_stage_clean_result / inverse_top_n_then_natural / inverse_of_overall) covering both Tirana 24h and Rally Albania patterns. Two role surfaces (org role vs racing role) called out explicitly. Decisions captured; Open questions reduced to one (geometry retroactivity engine, deferred to Phase 2.5). Source ingest: - raw/Regulations_2025.pdf + wiki/sources/rally-albania-regulations- 2025.md — formal ingest of the canonical Rally Albania 2025 rulebook. Section numbers preserved as §X.Y so the schema draft and future SPA work can cite precisely. Flagged follow-ups: the SLZ formula lives in the Supplementary Regulations (don't hardcode); M-7 numbering bug; unmodeled neutralization zones. Faulty-position flag (cross-plane operator workflow): - entities/postgres-timescaledb.md, entities/processor.md, concepts/position-record.md — operator-controlled boolean on the positions hypertable; processor filters WHERE faulty = false on every read; flagging triggers windowed recompute via the recompute:requests stream. Implementation strategy on entity pages: - entities/directus.md — Schema management section documenting the snapshots/ + db-init/ convention, container-startup apply pipeline. - entities/processor.md — Phase 2 long-lived branch model with PROCESSOR_PHASE_2_ENABLED flag-gating for incremental main merges; Phase 2.5 deferral note. Index and log updated.
47 lines
2.0 KiB
Markdown
47 lines
2.0 KiB
Markdown
---
|
|
title: PostgreSQL + TimescaleDB
|
|
type: entity
|
|
created: 2026-04-30
|
|
updated: 2026-05-01
|
|
sources: [gps-tracking-architecture]
|
|
tags: [infrastructure, business-plane, database]
|
|
---
|
|
|
|
# PostgreSQL + TimescaleDB
|
|
|
|
The durable storage layer. PostgreSQL with the TimescaleDB extension. Holds the positions hypertable and all business schema owned by [[directus]].
|
|
|
|
## Writers
|
|
|
|
- **[[processor]]** — sole writer for high-volume telemetry (positions hypertable) and writer for derived business entities (events, violations, alerts).
|
|
- **[[directus]]** — writes from the admin UI, custom endpoints, and Flows. Owns schema definition and migrations.
|
|
|
|
[[react-spa]] never writes (or reads) directly. [[tcp-ingestion]] does not touch the database.
|
|
|
|
## Schema authority
|
|
|
|
Schema is **defined and migrated through [[directus]]** — see that page for why. The Processor inserts rows respecting that schema; it does not create tables.
|
|
|
|
## Positions hypertable
|
|
|
|
Stores normalized [[position-record]] rows from [[processor]]. Beyond the wire-shape fields (device_id, timestamp, lat/lon/alt, angle, speed, satellites, priority, attributes), the hypertable carries one storage-only field:
|
|
|
|
- **`faulty boolean DEFAULT false`** — set by track operators via [[directus]] when a position is unrealistic (jumpy GPS, impossible speed/coordinate). The [[processor]]'s evaluators (peak-speed, crossing detection, recompute) filter `WHERE faulty = false` on every read of position data. Untouched at write time; mutated only through the operator workflow described in the schema draft.
|
|
|
|
## Operational note
|
|
|
|
The database is the **only single point of failure** in the architecture. Everything else is restartable, replaceable, or naturally redundant. Operational attention concentrates here:
|
|
|
|
- Replication
|
|
- Backups
|
|
- Point-in-time recovery via TimescaleDB
|
|
|
|
## Scaling
|
|
|
|
- **Vertical** for write throughput.
|
|
- **Read replicas** for analytics workloads.
|
|
|
|
## Deployment
|
|
|
|
Internal-only container. Persistence volume. Regular backups. Accessed only by [[directus]] and [[processor]].
|