FieldOps Demo

Three-feature demo for a multi-tenant field operations SaaS platform built with Next.js 14 App Router, Prisma + PostGIS, and Socket.io.

---

What This Demonstrates

#	Feature	Implementation
1	Multi-tenant middleware	Subdomain extracted from host header, looked up in a mock Redis store (in-memory Map), tenant context injected as x-tenant-* headers, unknown subdomains → JSON 404
2	PostGIS geofence check	POST /api/geofence accepts {lat, lng}, runs ST_Contains(polygon, ST_MakePoint(lng, lat)) via prisma.$queryRaw on a hardcoded ~1 km² Denver polygon
3	Real-time counter	Socket.io attached to a custom Node HTTP server; clicking a button on one tab updates all connected tabs in <500 ms

---

Stack

• Next.js 14 App Router, TypeScript strict mode
• Prisma 5 ORM — $queryRaw for spatial queries
• PostgreSQL 15 + PostGIS via Docker Compose
• Socket.io 4 on a custom Node HTTP server (mirrors Railway deployment pattern)
• Tailwind CSS

---

Prerequisites

• Node.js 18+
• Docker Desktop (for the PostGIS database)

---

Setup

1. Clone and install

git clone https://github.com/dbbuilder-org/fieldopsdemo.git
cd fieldopsdemo
npm install

2. Start the PostGIS database

docker compose up -d

This starts postgis/postgis:15-3.4 on port 5432. Takes ~10 seconds on first pull.

3. Configure environment

Copy the example file and fill in your database URL:

cp .env.example .env

For the Docker Compose database, use:

DATABASE_URL="postgresql://fieldops:fieldops@localhost:5432/fieldops_demo"

Or substitute a Neon (or any PostgreSQL + PostGIS) connection string.

4. Enable PostGIS and push schema

npm run db:push

If this fails with a PostGIS extension error, enable it manually first:

docker exec -it fieldops-demo-db-1 psql -U fieldops -d fieldops_demo -c "CREATE EXTENSION IF NOT EXISTS postgis;"
npm run db:push

5. Start the app

npm run dev

Open http://localhost:3000

---

Testing Each Feature

Feature 1 — Multi-tenant middleware

Add these lines to /etc/hosts (requires sudo):

127.0.0.1  acme.localhost globex.localhost initech.localhost unknown.localhost

Then visit:

• http://acme.localhost:3000 → tenant headers injected, green box appears on page
• http://globex.localhost:3000 → different tenant
• http://unknown.localhost:3000 → JSON 404 response from middleware

The middleware lives in src/middleware.ts. In production the Map is replaced with an Upstash Redis GET with a 60-second TTL.

Feature 2 — PostGIS geofence

Use the preset buttons or enter coordinates manually. The polygon covers downtown Denver (~39.729–39.742°N, ~104.988–104.970°W).

• Inside: Denver Convention Center (39.7392, -104.9847)
• Outside: Boulder (40.015, -105.2705)
• Outside: DEN Airport (39.8561, -104.6737)

Raw query in src/app/api/geofence/route.ts:

SELECT ST_Contains(
  ST_GeomFromText('<polygon>', 4326),
  ST_SetSRID(ST_MakePoint(lng, lat), 4326)
) AS inside

Feature 3 — Real-time counter

1. Open http://localhost:3000 in two browser tabs
2. Click + or − in either tab
3. The counter updates in the other tab — round-trip latency is displayed on screen

server.ts attaches Socket.io to the same HTTP server that handles Next.js requests. This mirrors the production pattern where Socket.io runs on a separate Railway service.

---

Project Structure

fieldops-demo/
├── server.ts                    # Custom Node server — Socket.io + Next.js on same port
├── docker-compose.yml           # PostgreSQL 15 + PostGIS
├── prisma/schema.prisma         # Prisma schema with postgis extension
└── src/
    ├── middleware.ts             # Multi-tenant subdomain resolver
    ├── lib/prisma.ts            # PrismaClient singleton
    └── app/
        ├── page.tsx             # Demo UI (client component)
        └── api/
            ├── tenant/route.ts  # Reads x-tenant-* headers set by middleware
            └── geofence/route.ts # PostGIS ST_Contains via prisma.$queryRaw

---

Technical Answer: GPS Ping Processing Pipeline

A GPS ping arrives from a mobile device every 10 seconds. Check whether the collector is inside any of 20 polygon geofences, update a Redis presence set, and emit a WebSocket event — all within the same request cycle.

Implementation:

export async function POST(req: NextRequest) {
  const { collectorId, communityId, lat, lng } = await req.json();

  // 1. One PostGIS query checks all 20 geofences via a JOIN — not 20 round trips.
  //    Requires a GiST index on geofences.polygon for sub-ms spatial lookup.
  const [hit] = await prisma.$queryRaw<{ geofenceId: string | null }[]>`
    SELECT g.id AS "geofenceId"
    FROM geofences g
    WHERE g.community_id = ${communityId}
      AND ST_Contains(g.polygon, ST_SetSRID(ST_MakePoint(${lng}, ${lat}), 4326))
    LIMIT 1
  `;

  // 2. Redis pipeline: SADD collector into matching geofence set (idempotent),
  //    30-second TTL on position key handles disconnected devices automatically.
  const pipeline = redis.pipeline();
  if (hit?.geofenceId) pipeline.sadd(`presence:${hit.geofenceId}`, collectorId);
  pipeline.set(`collector:${collectorId}:pos`, JSON.stringify({ lat, lng, ts: Date.now() }), { ex: 30 });
  await pipeline.exec();

  // 3. Pub/sub to Socket.io Railway instance — fire-and-forget, non-blocking.
  await redis.publish(`ws:community:${communityId}`, JSON.stringify({
    event: "collector:moved", collectorId, lat, lng,
    geofenceId: hit?.geofenceId ?? null,
  }));

  return NextResponse.json({ ok: true });
}

Failure modes:

• Missing GiST index → table scan across all community geofences; add CREATE INDEX ON geofences USING GIST (polygon) before any production data
• Redis pipeline failure → presence set goes stale; treat as non-fatal, recoverable on next 10-second ping — log to Sentry, return 200
• Socket.io instance restarting → pub/sub event dropped; dashboard falls back to 15-second polling on WebSocket disconnect
• Duplicate pings → 30-second TTL on position key prevents accumulation; Redis NX flag prevents duplicate events within the same second on mobile retries