Location Database — Schema

Table: locations

CREATE TABLE locations (
    id              BIGSERIAL PRIMARY KEY,
    received_at     TIMESTAMPTZ NOT NULL,      -- server clock when POST arrived
    tst             BIGINT NOT NULL,            -- GPS timestamp (Unix epoch)
    lat             DOUBLE PRECISION NOT NULL,
    lon             DOUBLE PRECISION NOT NULL,
    geom            GEOMETRY(Point, 4326),      -- PostGIS point, WGS84
    acc             REAL,                       -- horizontal accuracy (metres)
    vac             REAL,                       -- vertical accuracy (metres)
    alt             REAL,                       -- altitude (metres)
    batt            SMALLINT,                   -- battery level (%)
    bs              SMALLINT,                   -- battery status: 1=unplugged 2=charging 3=full
    conn            CHAR(1),                    -- w=WiFi  m=mobile  o=offline
    ssid            TEXT,                       -- WiFi network name
    bssid           TEXT,                       -- WiFi access-point MAC
    t               VARCHAR(4),                 -- trigger: p=ping t=timer u=user c=circular
    m               SMALLINT,                   -- OwnTracks monitoring mode
    p               REAL,                       -- barometric pressure (hPa)
    tid             VARCHAR(8),                 -- tracker ID
    motionactivities TEXT[],                   -- e.g. {stationary} {walking}
    inregions       TEXT[],                    -- named OwnTracks regions currently in
    topic           TEXT,                       -- MQTT topic (contains device UUID)
    UNIQUE (tst, topic)
);

Indexes

• locations_pkey — PRIMARY KEY btree on id

• locations_tst_topic_key — UNIQUE btree on (tst, topic) — deduplication

• locations_tst_idx — btree on tst — time-range queries

• locations_topic_tst — btree on (topic, tst) — per-device time queries

• locations_geom_idx — GiST on geom — spatial nearest-neighbour and containment queries

Notes on key columns

geom is populated from lat/lon on every insert. Always present (no NULLs). Cast to ::geography for metre-accurate distance calculations.

conn reliably indicates WiFi vs mobile — useful for filtering out inaccurate mobile fixes or understanding context.

raw_json is stored in SQLite only; the PostgreSQL table stores all fields as typed columns instead.

Distinct origins (topic/tid)

Confirmed live via SELECT topic, tid, count(*) FROM locations GROUP BY topic, tid on 2026-08-02. Only one real tracking device has ever reported:

Topic	tid	Rows	Date range	Good for
owntracks/owntracks/53AECFB9-BA35-44B8-BB70-9A35D2B32500	00	19,575	2026-06-29 → present	The live phone feed — this is the source for every real location question. Filter on this topic explicitly if a query might otherwise pick up the test rows below.
owntracks/test	(null)	2	2026-06-28	Test/setup traffic from initial OwnTracks configuration. Not a real fix — exclude from any query answering a location question.
owntracks/test/00000000-0000-0000-0000-000000000000	(null)	1	2026-06-28	Same — setup/test traffic, zero UUID. Exclude.

In practice the 3 test rows are old enough (pre-dating real tracking by one day) that any query ordering by tst DESC or filtering to a recent time window won't hit them, but an explicit WHERE topic = 'owntracks/owntracks/53AECFB9-BA35-44B8-BB70-9A35D2B32500' is the robust way to guarantee real fixes only, e.g. for full-history aggregates.