Data model (PostgreSQL + PostGIS)

Defined in phimi-platform/migrations/ and applied by the phimi-migrate Job.

Table	Responsibility
`users`	Owners and authorised users; identity comes from Keycloak (`external_subject`)
`pets`	Cat profiles
`devices`	JT/T 808 terminal id, IMEI, model, firmware
`sim_cards`	ICCID, operator, data allowance, validity
`pet_devices`	Which cat wears which tracker, over time (one active cat per device)
`positions`	Position history and telemetry
`device_state`	Latest state and latest valid position per device
`geofences`	Circles (centre + radius) or polygons
`geofence_events`	`zone_enter` / `zone_exit`
`device_commands`	Commands, channel, state, platform serial, reply
`alerts`	`zone_enter`, `zone_exit`, `battery_low`, `device_offline`, `gps_unavailable`, `emergency`, `sim_changed`

Positions

Every position stores both recorded_at (device time, converted to UTC) and received_at (server time). The difference reveals late blind-area uploads, and an older point never replaces the latest known position.

location is a GEOGRAPHY(POINT, 4326) and is NULL unless the report is a plausible GNSS fix. source is gps, wifi, lbs or none. Wi-Fi access points, cells and the other V53 items go into telemetry (JSONB). UNIQUE (device_id, recorded_at, source) makes a retransmission idempotent.

For four trackers a plain indexed table is enough: (device_id, recorded_at DESC) for history, and GiST on location for spatial queries. Partition by time once retention or tracker count justifies it.

Positions​

Positions