← Back to the chapter · Previous detail · Next detail →
Technical detail · Storage Design Case Studies
City telemetry and media paths
City telemetry uses observation time for daily chunks and sensor type, district, and time for dashboard access.
CREATE TABLE city_readings (
observed_at TIMESTAMPTZ NOT NULL,
sensor_id TEXT NOT NULL,
sensor_type TEXT NOT NULL,
district_id TEXT NOT NULL,
metric TEXT NOT NULL,
value DOUBLE PRECISION,
unit TEXT,
schema_version INTEGER NOT NULL,
quality SMALLINT NOT NULL DEFAULT 100,
PRIMARY KEY (sensor_id, metric, observed_at)
);
SELECT create_hypertable(
'city_readings',
by_range('observed_at', INTERVAL '1 day'),
if_not_exists => TRUE
);
CREATE INDEX city_readings_dashboard_idx
ON city_readings (sensor_type, district_id, observed_at DESC);
Media paths distinguish raw, reviewed, public, and archive objects:
city-media/
raw/
year=2026/month=05/day=23/district=north/camera=cam-014/
reviewed/
incident_id=inc-2026-00142/
public/
release=transport-study-2026-q2/
archive/
retention_class=legal-hold/