Location Database — Architecture

See also: location-db • schema • usage

Data flow

OwnTracks app → HTTPS POST /loc → owntracks_loc.py (WSGI, www-data):

1. Validate payload (lat, lon, tst required; _type must be location)

2. INSERT into SQLite (/var/lib/owntracks/locations.db) — primary store, unchanged

3. Call owntracks_pg.insert_location(payload, received_at) — wrapped in try/except, cannot affect step 2

4. Write current-location note to gdata-server

owntracks_pg.py

Module at /usr/local/www/wsgi-scripts/owntracks_pg.py. Imported by owntracks_loc.py at module level (with import fallback). Connects as owntracks DB user (INSERT + SELECT) via local Unix socket with trust auth.

Key design points:

• Module-level persistent connection (_conn) — reconnects automatically if closed

• ON CONFLICT (tst, topic) DO NOTHING — idempotent, safe to re-run backfill

• All errors caught and logged to stderr; never raises to caller

• backfill_from_sqlite(db_path) — run as python3 owntracks_pg.py to sync missing rows

pg_query MCP tool

Implemented in misc_mcp_server.py (misc-server). Uses asyncpg (Architecture B: persistent pool) rather than per-query connections.

Pool config:

asyncpg.create_pool(
    'postgresql://owntracks_ro@/owntracks',
    min_size=1, max_size=3,
    max_inactive_connection_lifetime=300,  # recycle before OS kills idle conns
    max_queries=50_000,
    command_timeout=30,
)

Connects as owntracks_ro (SELECT only). Pool is created at process startup in main() and shared across all tool calls via module global.

Retry pattern

If a pool connection goes stale (e.g. after a PostgreSQL restart), the query fails with an exception. The tool returns "Query error: ... Please try again." rather than raising. On the next call the pool opens a fresh connection and the query succeeds. No explicit reconnect logic is needed — the error message is sufficient for the LLM to retry.

Authentication

pg_hba.conf rules (/etc/postgresql/15/main/pg_hba.conf):

local   owntracks   owntracks     trust   # write user (www-data, wsgi)
local   owntracks   owntracks_ro  trust   # read-only user (admin, MCP server)

Both sit above the local all all peer catch-all, so they match first regardless of OS username.

PostgreSQL setup

• Installed: apt install postgresql postgresql-postgis python3-asyncpg python3-psycopg2

• Data directory moved to NVMe: pg_dropcluster --stop 15 main && pg_createcluster 15 main --datadir=/mnt/postgres/15/main

• PostGIS enabled: CREATE EXTENSION postgis in owntracks database

• Root SSH on gravlax: john's key copied to /root/.ssh/authorized_keys — ssh root@gravlax.critchley.biz works directly

Source

Git: john-critchley/gdata-server — misc_mcp_server.py, owntracks_pg.py, owntracks_loc.py

Live files on gravlax: /home/john/py/gdata-server/misc_mcp_server.py and /usr/local/www/wsgi-scripts/owntracks_pg.py / owntracks_loc.py

version 1