← 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/

← Back to the chapter · Previous detail · Next detail →