Design the backend behind a fleet tracking product: batched GPS ingestion, latest-position and history tables, trip detection, PostGIS geofences and alert fan-out, built by hand on AWS or in TypeScript with Encore in your own account.

A fleet tracking product starts as a map with dots on it and turns into a write-heavy backend. Every vehicle or driver phone sends a GPS ping every few seconds, and five hundred vehicles pinging every five seconds is 100 writes a second before anyone has opened a dashboard.
Dispatch wants the latest position of every vehicle instantly, and operations want an alert when a truck leaves a yard after hours. Finance wants mileage per trip at the end of the month.
This guide designs that backend: ingestion, the two storage shapes, trip detection, geofences and alerts. It starts with a hand-built version on AWS, then builds it in TypeScript with Encore, with the data in your own AWS or GCP account.
Devices and driver apps lose coverage in tunnels and rural areas, then flush a backlog when they reconnect, so the ingestion endpoint should accept a batch of pings, not one at a time, and tolerate pings that arrive minutes late and out of order. Each ping carries the device ID, the device's own timestamp, latitude, longitude, speed, heading and, for hardware trackers, ignition state. A unique constraint on (device_id, recorded_at) makes retries harmless.
Reject obviously bad points (zero coordinates, impossible speeds between consecutive pings) at ingestion rather than in every report. Respond fast and push everything else (trip logic, geofences, alerts) onto a queue so a slow geofence check never makes a device retry its upload.
Live maps and dispatch read one row per vehicle, so keep a vehicle_positions table and upsert it on each batch, only when the new ping is newer than the stored one. History is append-only and grows by millions of rows a day; a table partitioned by day with PostgreSQL declarative partitioning keeps indexes small and makes retention a DROP TABLE of old partitions instead of a slow DELETE.
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE pings (
device_id TEXT NOT NULL,
recorded_at TIMESTAMPTZ NOT NULL,
location GEOGRAPHY(Point, 4326) NOT NULL,
speed_kmh REAL,
ignition BOOLEAN,
PRIMARY KEY (device_id, recorded_at)
) PARTITION BY RANGE (recorded_at);
CREATE TABLE pings_2026_11_18 PARTITION OF pings
FOR VALUES FROM ('2026-11-18') TO ('2026-11-19');
CREATE TABLE vehicle_positions (
device_id TEXT PRIMARY KEY,
recorded_at TIMESTAMPTZ NOT NULL,
location GEOGRAPHY(Point, 4326) NOT NULL,
speed_kmh REAL
);
CREATE TABLE geofences (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
area GEOGRAPHY(Polygon, 4326) NOT NULL
);
CREATE INDEX geofences_area_idx ON geofences USING GIST (area);
CREATE TABLE device_geofences (
device_id TEXT NOT NULL,
geofence TEXT NOT NULL,
PRIMARY KEY (device_id, geofence)
);
CREATE TABLE geofence_events (
device_id TEXT NOT NULL,
geofence TEXT NOT NULL,
event TEXT NOT NULL, -- enter or exit
recorded_at TIMESTAMPTZ NOT NULL,
published_at TIMESTAMPTZ,
PRIMARY KEY (device_id, geofence, event, recorded_at)
);
CREATE TABLE device_geofence_cursor (
device_id TEXT PRIMARY KEY,
last_processed_at TIMESTAMPTZ NOT NULL
);
Partitions have to exist before data arrives, so create the next few days ahead of time from a scheduled job.
A trip starts when ignition turns on (or speed rises above a threshold for phones) and ends after ignition off or a gap of several minutes with no movement. Trip detection is a small state machine per vehicle: keep the open trip in a table, extend it with each ping, close it on the stop condition, and compute distance from the history with ST_Length over the trip's points. Recompute a closed trip when late points fall inside it, since a phone that lost coverage can deliver pings minutes after the fact.
Geofence checks walk every ping in a batch in time order and compare each point with the previous one: ST_Covers(area, location) for the new point and the prior membership tells you whether this ping is an entry, an exit or neither. A per-device cursor advances only over newer pings so a late older batch cannot rewind current membership, while those late pings are still walked to record any missed historical transitions. Recording each transition with a unique key keeps a retried batch from inserting twice, and each alert carries recordedAt so a subscriber can ignore a retried publish of the same crossing. PostGIS is available as an extension on Amazon RDS for PostgreSQL and on Cloud SQL, and a GiST index keeps the lookup fast with thousands of fences.
Run the ingestion API on ECS Fargate behind an Application Load Balancer, writing pings and latest positions to RDS for PostgreSQL in private subnets, then sending a message per batch to an SQS queue. A worker service reads the queue, runs trip detection and geofence checks, and publishes alerts to an SNS topic that fans out to SMS, email and a customer webhook worker. A scheduled task creates tomorrow's partitions and drops expired ones. Each piece needs a security group, an IAM role scoped to its queue or topic, a dead-letter queue, and Terraform to hold it together. AWS IoT Core is worth a look if the devices speak MQTT rather than HTTPS.
Encore brings infrastructure from code to this TypeScript service, and the database, the position topic and the alert topic are declared next to the endpoints that use them. encore run starts Postgres locally from an image with PostGIS among its pre-installed extensions and runs Pub/Sub in memory, so you can replay a recorded drive through the ingestion endpoint and watch trips and geofence alerts appear in the local trace. On deploy to your own AWS account Encore provisions RDS, SNS and SQS (Cloud SQL and Pub/Sub on GCP), and Encore provisions an IAM role for the service, or a service account on GCP. Quiqup and Skrym built their logistics platforms with Encore.
Run in your terminal to get started locally.
// fleet/ingest.ts
import { api } from "encore.dev/api";
import { SQLDatabase } from "encore.dev/storage/sqldb";
import { Topic, Subscription, Attribute } from "encore.dev/pubsub";
const db = new SQLDatabase("fleet", { migrations: "./migrations" });
interface Ping {
recordedAt: string;
lat: number;
lng: number;
speedKmh?: number;
ignition?: boolean;
}
export interface PositionsReceived {
deviceId: Attribute<string>;
from: string;
to: string;
}
export const positionsReceived = new Topic<PositionsReceived>("positions-received", {
deliveryGuarantee: "at-least-once",
orderingAttribute: "deviceId", // per-vehicle order for trip detection
});
export const ingest = api(
{ expose: true, auth: true, method: "POST", path: "/devices/:deviceId/pings" },
async ({ deviceId, pings }: { deviceId: string; pings: Ping[] }): Promise<{ accepted: number }> => {
const valid = pings.filter((p) => !(p.lat === 0 && p.lng === 0));
if (valid.length === 0) return { accepted: 0 };
for (const p of valid) {
await db.exec`
INSERT INTO pings (device_id, recorded_at, location, speed_kmh, ignition)
VALUES (${deviceId}, ${p.recordedAt}, ST_MakePoint(${p.lng}, ${p.lat})::geography, ${p.speedKmh ?? null}, ${p.ignition ?? null})
ON CONFLICT DO NOTHING`;
}
const latest = valid.reduce((a, b) => (a.recordedAt > b.recordedAt ? a : b));
await db.exec`
INSERT INTO vehicle_positions (device_id, recorded_at, location, speed_kmh)
VALUES (${deviceId}, ${latest.recordedAt}, ST_MakePoint(${latest.lng}, ${latest.lat})::geography, ${latest.speedKmh ?? null})
ON CONFLICT (device_id) DO UPDATE
SET recorded_at = EXCLUDED.recorded_at, location = EXCLUDED.location, speed_kmh = EXCLUDED.speed_kmh
WHERE vehicle_positions.recorded_at < EXCLUDED.recorded_at`;
const times = valid.map((p) => p.recordedAt).sort();
await positionsReceived.publish({ deviceId, from: times[0], to: times[times.length - 1] });
return { accepted: valid.length };
},
);
export const geofenceAlerts = new Topic<{
deviceId: string;
geofence: string;
event: "enter" | "exit";
recordedAt: string;
}>(
"geofence-alerts",
{ deliveryGuarantee: "at-least-once" },
);
const _ = new Subscription(positionsReceived, "geofences", {
handler: async ({ deviceId, from, to }) => {
const cursor = await db.queryRow<{ last_processed_at: string }>`
SELECT last_processed_at FROM device_geofence_cursor WHERE device_id = ${deviceId}`;
const after = cursor?.last_processed_at;
const all = await db.queryAll<{ recorded_at: string }>`
SELECT recorded_at FROM pings
WHERE device_id = ${deviceId} AND recorded_at >= ${from} AND recorded_at <= ${to}
ORDER BY recorded_at`;
const late = after ? all.filter((p) => p.recorded_at <= after) : [];
const points = after ? all.filter((p) => p.recorded_at > after) : all;
const recordAndPublish = async (name: string, event: "enter" | "exit", at: string) => {
await db.exec`
INSERT INTO geofence_events (device_id, geofence, event, recorded_at)
VALUES (${deviceId}, ${name}, ${event}, ${at})
ON CONFLICT DO NOTHING`;
const pending = await db.queryRow<{ geofence: string }>`
SELECT geofence FROM geofence_events
WHERE device_id = ${deviceId} AND geofence = ${name} AND event = ${event}
AND recorded_at = ${at} AND published_at IS NULL`;
if (!pending) return;
// recordedAt is the unique transition key; subscribers should ignore a retry of the same key.
await geofenceAlerts.publish({ deviceId, geofence: name, event, recordedAt: at });
await db.exec`
UPDATE geofence_events SET published_at = now()
WHERE device_id = ${deviceId} AND geofence = ${name} AND event = ${event}
AND recorded_at = ${at} AND published_at IS NULL`;
};
const walk = async (seq: { recorded_at: string }[], was: Set<string>, updateLive: boolean) => {
for (const p of seq) {
const now = await db.queryAll<{ name: string }>`
SELECT g.name FROM geofences g
JOIN pings x ON ST_Covers(g.area, x.location)
WHERE x.device_id = ${deviceId} AND x.recorded_at = ${p.recorded_at}`;
const inside = new Set(now.map((r) => r.name));
for (const name of inside) {
if (was.has(name)) continue;
await recordAndPublish(name, "enter", p.recorded_at);
if (updateLive) {
await db.exec`INSERT INTO device_geofences (device_id, geofence) VALUES (${deviceId}, ${name}) ON CONFLICT DO NOTHING`;
}
}
for (const name of was) {
if (inside.has(name)) continue;
await recordAndPublish(name, "exit", p.recorded_at);
if (updateLive) {
await db.exec`DELETE FROM device_geofences WHERE device_id = ${deviceId} AND geofence = ${name}`;
}
}
was = inside;
}
};
if (late.length > 0) {
const prior = await db.queryRow<{ recorded_at: string | null }>`
SELECT max(recorded_at) AS recorded_at FROM pings
WHERE device_id = ${deviceId} AND recorded_at < ${late[0].recorded_at}`;
const start = prior?.recorded_at
? await db.queryAll<{ name: string }>`
SELECT g.name FROM geofences g
JOIN pings p ON ST_Covers(g.area, p.location)
WHERE p.device_id = ${deviceId} AND p.recorded_at = ${prior.recorded_at}`
: [];
// Record historical transitions without moving live membership or the cursor.
await walk(late, new Set(start.map((r) => r.name)), false);
}
if (points.length === 0) return;
const start = after
? await db.queryAll<{ name: string }>`
SELECT geofence AS name FROM device_geofences WHERE device_id = ${deviceId}`
: await db.queryAll<{ name: string }>`
SELECT g.name FROM geofences g
JOIN pings p ON ST_Covers(g.area, p.location)
WHERE p.device_id = ${deviceId} AND p.recorded_at = (
SELECT max(recorded_at) FROM pings
WHERE device_id = ${deviceId} AND recorded_at < ${points[0].recorded_at})`;
await walk(points, new Set(start.map((r) => r.name)), true);
const last = points[points.length - 1].recorded_at;
await db.exec`
INSERT INTO device_geofence_cursor (device_id, last_processed_at)
VALUES (${deviceId}, ${last})
ON CONFLICT (device_id) DO UPDATE
SET last_processed_at = EXCLUDED.last_processed_at
WHERE device_geofence_cursor.last_processed_at < EXCLUDED.last_processed_at`;
},
});
The ordered topic delivers each vehicle's batches in publish order, which keeps trip detection simple; per the docs, ordering applies after deploy and has no effect under encore run, and on AWS an ordered topic is limited to 300 messages per second, so publish once per batch rather than once per ping. Partition maintenance is a cron job, Encore platform managed and not run locally or in preview environments, so call its endpoint by hand when testing. Alert channels are subscriptions on geofence-alerts, one per channel, each with its own retries.
A coding agent working in this repository can add a new alert rule, such as speeding inside a depot fence, as a subscriber and test it locally by replaying a recorded drive against local Postgres with PostGIS.
Best fit: fleet and delivery products that ingest GPS from vehicles or driver apps and want positions, trips and geofence alerts in their own AWS or GCP account.
Keep two shapes: a small latest-position table with one row per vehicle, updated on every ping, for live maps and dispatch, and an append-only history table partitioned by time for trips, replay and reporting. Ingest pings in batches from devices or driver apps, deduplicate on device ID plus timestamp, and drop or archive old partitions instead of deleting rows.
Declare a Postgres database with PostGIS, a Pub/Sub topic for position updates and a topic for alerts in TypeScript. A batch ingestion endpoint writes history and latest positions, then publishes; subscribers detect trips and check geofences with ST_Contains. encore run starts Postgres locally with PostGIS available, and on deploy to your own AWS account Encore provisions RDS, SNS and SQS, and an IAM role for the service.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.