Merge pull request 'feat: Webhook use auth token' (#10) from feat/webhook-auth into main

Reviewed-on: #10

README.md

@@ -10,10 +10,16 @@ TODO
 ### Database
 **Change name of database and credentials as you like!**
 
-- Create new database: `CREATE DATABASE locationhub;`
+- Create new database: `CREATE DATABASE dev_locationhub;`
 - Create new user for database: `GRANT ALL PRIVILEGES ON dev_locationhub.* TO 'dbuser'@'localhost' IDENTIFIED BY '1234';`
 - Import tables: `/usr/bin/mariadb -u dbuser -p1234 dev_locationhub < server/sql/tables.sql`
 
+### TTN Integration
+Create new Webhook for application. Set base url and enable "Uplink message" to api `/api/ttn/webhook`.
+Add a addidtional header: 
+- Type: `authorization` 
+- Value: `Bearer your-very-secure-token`
+
 ### Testing Webhook
 - To test the webhook use the python script `ttn-webhook-dummy.py` to send prerecorded TTN Uplinks.
 - To test the script you can use `while true; do echo -e "HTTP/1.1 200 OK\r\nContent-Type: text/plain\r\n\r\nSuccess"; nc -l -p 8080 -q 1; done`

server/.env.template

@@ -4,9 +4,10 @@ DB_PASSWORD=""
 DB_HOST=""
 DB_DIALECT=""
 DB_PORT=""
-WIGLE_TOKEN=""
+WEBHOOK_TOKEN="" #Token that is placed a the TTN Webhook auth
+WIGLE_TOKEN="" # Go to account and generate token "Encoded for use"
 WIGLE_BASE_URL="https://api.wigle.net"
 WIGLE_NETWORK_SEARCH="/api/v2/network/search"
-GET_LOCATION_WIFI_MAX_AGE=1209600000 # 14 Tage in Millisekunden (14 * 24 * 60 * 60 * 1000)
+GET_LOCATION_WIFI_MAX_AGE=1209600000 # 14 days in milliseconds (14 * 24 * 60 * 60 * 1000)
 GET_LOCATION_WIFI_MAX=10000          
 GET_LOCATION_WIFI_PRIMITIVE=true  

server/package.json

@@ -13,11 +13,11 @@
   "license": "ISC",
   "devDependencies": {
     "@types/express": "^5.0.0",
+    "@types/memoizee": "^0.4.11",
     "@types/node": "^22.10.2",
     "nodemon": "^3.1.9",
     "ts-node": "^10.9.2",
-    "typescript": "^5.7.2",
+    "typescript": "^5.7.2"
-    "@types/memoizee": "^0.4.11"
   },
   "dependencies": {
     "cors": "^2.8.5",

server/scripts/ttn-webhook-dummy.py

@@ -9,11 +9,16 @@ import json
 import argparse
 import random
 
-def send_post_request(uri, data):
+def send_post_request(uri, data, token):
+    headers = {
+        "Authorization": f"Bearer {token}",
+        "Content-Type": "application/json",
+    }
     try:
-        requests.post(uri, json=data, timeout=1)
+        response = requests.post(uri, json=data, timeout=1, headers=headers)
+        print("Return code: " + str(response.status_code))
     except requests.exceptions.RequestException as e:
-        pass
+        print(e)
 
 def main():
     parser = argparse.ArgumentParser(
@@ -24,6 +29,11 @@ def main():
         type=str,
         help="The URI to send POST requests to (e.g., http://127.0.0.1:8080/api)",
     )
+    parser.add_argument(
+        "token",
+        type=str,
+        help="Bearer authorization token)",
+    )
     parser.add_argument(
         "directory",
         type=str,
@@ -46,7 +56,7 @@ def main():
                 try:
                     data = json.load(file)
                     print(f"Sending {args.directory} to {args.uri}")
-                    send_post_request(args.uri, data)
+                    send_post_request(args.uri, data, args.token)
                 except json.JSONDecodeError as e:
                     print(f"Error reading {args.directory}: {e}")
             return
@@ -67,7 +77,7 @@ def main():
                 try:
                     data = json.load(file)
                     print(f"Sending {filename} to {args.uri}")
-                    send_post_request(args.uri, data)
+                    send_post_request(args.uri, data, args.token)
                 except json.JSONDecodeError as e:
                     print(f"Error reading {filename}: {e}")
 
@@ -78,7 +88,7 @@ def main():
                 try:
                     data = json.load(file)
                     print(f"Sending {filename} to {args.uri}")
-                    send_post_request(args.uri, data)
+                    send_post_request(args.uri, data, args.token)
                     input("Press Enter to send the next file...")
                 except json.JSONDecodeError as e:
                     print(f"Error reading {filename}: {e}")
@@ -91,11 +101,10 @@ def main():
                 try:
                     data = json.load(file)
                     print(f"Sending {filename} to {args.uri}")
-                    send_post_request(args.uri, data)
+                    send_post_request(args.uri, data, args.token)
                     input("Press Enter to send another random file...")
                 except json.JSONDecodeError as e:
                     print(f"Error reading {filename}: {e}")
 
-
 if __name__ == "__main__":
     main()

server/scripts/wigle-dummy.py

@@ -0,0 +1,88 @@
+#!/usr/bin/env python3
+# -*- coding: utf-8 -*-
+""" Author:                     Hendrik Schutter, mail@hendrikschutter.com
+"""
+
+import requests
+import os
+import json
+import argparse
+import random
+import http.server
+import json
+from urllib.parse import urlparse, parse_qs
+
+port = 8000
+
+def generateDummyResponse(netid):
+    response_payload = {
+        "success": True,
+        "totalResults": 1,
+        "first": 0,
+        "last": 0,
+        "resultCount": 1,
+        "results": [
+            {
+                "trilat": random.uniform(-90, 90),
+                "trilong": random.uniform(-180, 180),
+                "ssid": "Wifi-Name",
+                "qos": 0,
+                "transid": "string",
+                "firsttime": "2025-01-02T16:48:28.368Z",
+                "lasttime": "2025-01-02T16:48:28.368Z",
+                "lastupdt": "2025-01-02T16:48:28.368Z",
+                "netid": netid,
+                "name": "string",
+                "type": "string",
+                "comment": "string",
+                "wep": "string",
+                "bcninterval": 0,
+                "freenet": "string",
+                "dhcp": "string",
+                "paynet": "string",
+                "userfound": False,
+                "channel": 0,
+                "rcois": "string",
+                "encryption": "none",
+                "country": "string",
+                "region": "string",
+                "road": "string",
+                "city": "string",
+                "housenumber": "string",
+                "postalcode": "string",
+            }
+        ],
+        "searchAfter": "string",
+        "search_after": 0,
+    }
+
+    return response_payload
+
+class SimpleHTTPRequestHandler(http.server.BaseHTTPRequestHandler):
+    def do_GET(self):
+        # Parse the URL and query parameters
+        parsed_url = urlparse(self.path)
+        if parsed_url.path == "/api/v2/network/search":
+            query_params = parse_qs(parsed_url.query)
+            netid = query_params.get("netid", [""])[0]
+
+            # Send response headers
+            self.send_response(200)
+            self.send_header("Content-Type", "application/json")
+            self.end_headers()
+
+            # Send the JSON response
+            self.wfile.write(json.dumps(generateDummyResponse(netid)).encode("utf-8"))
+        else:
+            # Handle 404 Not Found
+            self.send_response(404)
+            self.end_headers()
+            self.wfile.write(b"Not Found")
+
+def main():
+    server = http.server.HTTPServer(("127.0.0.1", port), SimpleHTTPRequestHandler)
+    print(f"Server running on http://127.0.0.1:{port}/api/v2/network/search'...")
+    server.serve_forever()
+
+if __name__ == "__main__":
+    main()

server/sql/tables.sql

@@ -7,8 +7,6 @@ CREATE TABLE IF NOT EXISTS lp_ttn_end_device_uplinks (
     dev_addr VARCHAR(255),
     received_at_utc DATE,
     battery NUMERIC,
-    latitude DOUBLE,
-    longitude DOUBLE,
     created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 );
@@ -18,8 +16,8 @@ CREATE TABLE IF NOT EXISTS wifi_scan (
     lp_ttn_end_device_uplinks_id UUID,
     mac VARCHAR(255),
     rssi NUMERIC,
-    latitude DOUBLE,
+    latitude DOUBLE NOT NULL,
-    longitude DOUBLE,
+    longitude DOUBLE NOT NULL,
     created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)
@@ -46,6 +44,8 @@ CREATE TABLE IF NOT EXISTS location (
     wifi_longitude DOUBLE,
     gnss_latitude DOUBLE,
     gnss_longitude DOUBLE,
+    ttn_gw_latitude DOUBLE,
+    ttn_gw_longitude DOUBLE,
     created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)

server/src/controller/ttnController.ts

@@ -1,16 +1,14 @@
 import express, { Request, Response } from "express";
 import { container } from "tsyringe";
-import { domainEventEmitter } from "../config/eventEmitter";
-import {
-  TtnMessageReceivedEvent,
-  TtnMessageReceivedEventName,
-} from "../event/ttnMessageReceivedEvent";
 import { validateData } from "../middleware/validationMiddleware";
 import { TtnMessage } from "../models/ttnMessage";
+import { LocationService } from "../services/locationService";
 import { LpTtnEndDeviceUplinksService } from "../services/lpTtnEndDeviceUplinksService";
 import { TtnGatewayReceptionService } from "../services/ttnGatewayReceptionService";
 import { WifiScanService } from "../services/wifiScanService";
 import { ttnMessageValidator } from "../validation/ttn/ttnMessageValidation";
+import { authenticateHeader } from "../middleware/authentificationMiddleware";
+import { StatusCodes } from "http-status-codes";
 
 const lpTtnEndDeviceUplinksService = container.resolve(
   LpTtnEndDeviceUplinksService
@@ -20,14 +18,17 @@ const ttnGatewayReceptionService = container.resolve(
 );
 const wifiScanService = container.resolve(WifiScanService);
 
+const locationService = container.resolve(LocationService);
+
 const router = express.Router();
 
 router.post(
   "/webhook",
-  validateData(ttnMessageValidator),
+  [authenticateHeader, validateData(ttnMessageValidator)],
   async (req: Request, res: Response) => {
     try {
       const message = req.body as TtnMessage;
+
       const { lp_ttn_end_device_uplinks_id } =
         await lpTtnEndDeviceUplinksService.createUplink({
           device_id: message.end_device_ids.device_id,
@@ -40,14 +41,17 @@ router.post(
           battery: message.uplink_message.decoded_payload?.messages[0].find(
             (e) => e.type === "Battery"
           )?.measurementValue,
-          latitude: message.uplink_message.decoded_payload?.messages[0].find(
-            (e) => e.type === "Latitude"
-          )?.measurementValue,
-          longitude: message.uplink_message.decoded_payload?.messages[0].find(
-            (e) => e.type === "Longitude"
-          )?.measurementValue,
         });
 
+      const gnnsLocation = {
+        latitude: message.uplink_message.decoded_payload?.messages[0].find(
+          (e) => e.type === "Latitude"
+        )?.measurementValue,
+        longitude: message.uplink_message.decoded_payload?.messages[0].find(
+          (e) => e.type === "Longitude"
+        )?.measurementValue,
+      };
+
       const wifiScans =
         message.uplink_message.decoded_payload?.messages[0]
           .find((e) => e.type === "Wi-Fi Scan")
@@ -57,42 +61,54 @@ router.post(
             rssi: w.rssi,
           })) ?? [];
 
+      console.log(wifiScans);
+
       const ttnGatewayReceptions = message.uplink_message.rx_metadata.map(
         (g) => ({
           lp_ttn_end_device_uplinks_id,
           gateway_id: g.gateway_ids.gateway_id,
           eui: g.gateway_ids.eui,
           rssi: g.rssi,
-          latitude: g.location.latitude,
+          latitude: g.location?.latitude,
-          longitude: g.location.longitude,
+          longitude: g.location?.longitude,
-          altitude: g.location.altitude,
+          altitude: g.location?.altitude,
         })
       );
 
-      const event: TtnMessageReceivedEvent = {
+      const createDatabaseEntries = async () => {
-        lp_ttn_end_device_uplinks_id,
+        const [wifiResults, gatewayResults] = await Promise.all([
-        wifis: wifiScans.map((w) => ({ mac: w.mac, rssi: w.rssi })),
+          wifiScanService.createWifiScans(wifiScans),
-        ttnGateways: ttnGatewayReceptions.map((g) => ({
+          ttnGatewayReceptionService.filterAndInsertGatewayReception(
-          rssi: g.rssi,
+            ttnGatewayReceptions
-          altitude: g.altitude,
+          ),
-          latitude: g.latitude,
+        ]);
-          longitude: g.longitude,
+
-        })),
+        locationService.createLocationFromTriangulation({
+          lp_ttn_end_device_uplinks_id,
+          wifi: wifiResults.map(({ latitude, longitude, rssi }) => ({
+            latitude,
+            longitude,
+            rssi,
+          })),
+          ttn_gw: gatewayResults.map(({ latitude, longitude, rssi }) => ({
+            latitude,
+            longitude,
+            rssi,
+          })),
+          gnss:
+            gnnsLocation.latitude && gnnsLocation.longitude
+              ? {
+                latitude: gnnsLocation.latitude,
+                longitude: gnnsLocation.longitude,
+              }
+              : undefined,
+        });
       };
-
+      createDatabaseEntries().then();
-      domainEventEmitter.emit(TtnMessageReceivedEventName, event);
+      res.status(StatusCodes.OK).send();
-
-      await Promise.all([
-        wifiScanService.createWifiScans(wifiScans),
-        ttnGatewayReceptionService.createGatewayReceptions(
-          ttnGatewayReceptions
-        ),
-      ]);
-
-      res.status(200);
     } catch (error) {
       console.log(error);
-      res.status(500).json({ error: "Error creating uplink" });
+      res.status(StatusCodes.INTERNAL_SERVER_ERROR).json({ error: "Error creating uplink" });
     }
   }
 );

server/src/controller/ttnGatewayReceptionController.ts

@@ -1,6 +1,6 @@
 import express, { Request, Response } from "express";
-import { TtnGatewayReceptionService } from "../services/ttnGatewayReceptionService";
 import { container } from "tsyringe";
+import { TtnGatewayReceptionService } from "../services/ttnGatewayReceptionService";
 
 const ttnGatewayReceptionService = container.resolve(
   TtnGatewayReceptionService
@@ -35,7 +35,7 @@ router.get("/:id", async (req: Request, res: Response) => {
 router.post("/", async (req: Request, res: Response) => {
   try {
     const newGatewayReception =
-      await ttnGatewayReceptionService.createGatewayReception(req.body);
+      await ttnGatewayReceptionService.createTtnGatewayReception(req.body);
     res.status(201).json(newGatewayReception);
   } catch (error) {
     res.status(500).json({ error: "Error creating gateway reception" });
@@ -46,7 +46,10 @@ router.put("/:id", async (req: Request, res: Response) => {
   try {
     const { id } = req.params;
     const updatedGatewayReception =
-      await ttnGatewayReceptionService.updateGatewayReception(id, req.body);
+      await ttnGatewayReceptionService.updateGatewayReception({
+        ...req.body,
+        ttn_gateway_reception_id: id,
+      });
     if (!updatedGatewayReception) {
       res.status(404).json({ error: "Gateway reception not found" });
       return;

server/src/controller/wifiScanController.ts

@@ -40,7 +40,10 @@ router.post("/", async (req: Request, res: Response) => {
 router.put("/:id", async (req: Request, res: Response) => {
   try {
     const { id } = req.params;
-    const updatedWifiScan = await wifiScanService.updateWifiScan(id, req.body);
+    const updatedWifiScan = await wifiScanService.updateWifiScan({
+      ...req.body,
+      wifi_scan_id: id,
+    });
     if (!updatedWifiScan) {
       res.status(404).json({ error: "Wifi scan not found" });
       return;

server/src/event/ttnMessageReceivedEvent.ts

@@ -1,14 +0,0 @@
-export const TtnMessageReceivedEventName = "TtnMessageReceived";
-export type TtnMessageReceivedEvent = {
-  lp_ttn_end_device_uplinks_id: string;
-  wifis: {
-    mac: string;
-    rssi: number;
-  }[];
-  ttnGateways: {
-    rssi: number;
-    latitude: number;
-    longitude: number;
-    altitude: number;
-  }[];
-};

server/src/eventHandler/ttnMessageReceivedEventHandler.ts

@@ -1,13 +0,0 @@
-import { domainEventEmitter } from "../config/eventEmitter";
-import {
-  TtnMessageReceivedEvent,
-  TtnMessageReceivedEventName,
-} from "../event/ttnMessageReceivedEvent";
-
-domainEventEmitter.on(
-  TtnMessageReceivedEventName,
-  async (event: TtnMessageReceivedEvent) => {
-    console.log(event);
-    // TODO Hendrik 🚀
-  }
-);

server/src/index.ts

@@ -1,7 +1,6 @@
 import dotenv from "dotenv";
 import express from "express";
 import "reflect-metadata";
-import "./eventHandler/ttnMessageReceivedEventHandler";
 const cors = require("cors");
 
 import locationRoutes from "./controller/locationController";
@@ -25,5 +24,5 @@ app.use("/api/locations", locationRoutes);
 app.use("/api/ttn", ttnRoutes);
 
 app.listen(PORT, () => {
-  console.log(`🚀 Server läuft auf http://localhost:${PORT}`);
+  console.log(`🚀 Server runs here: http://localhost:${PORT}`);
 });

server/src/middleware/authentificationMiddleware.ts

@@ -0,0 +1,42 @@
+import { NextFunction, Request, Response } from "express";
+import { StatusCodes } from "http-status-codes";
+
+const validateBearerToken = (authorizationHeader: string | undefined): boolean => {
+    if (!authorizationHeader) {
+        console.log("Authorization header is missing!");
+        return false;
+    }
+
+    const token = authorizationHeader.split(' ')[1]; // Extract token after 'Bearer'
+    if (!token) {
+        console.log("Bearer token is missing!");
+        return false;
+    }
+
+    if (token !== process.env.WEBHOOK_TOKEN) {
+        console.log("Bearer token is incorrect!");
+        return false;
+    }
+
+    return true;
+};
+
+export function authenticateHeader(req: Request, res: Response, next: NextFunction) {
+    try {
+        const authorizationHeader = req.headers['authorization'];
+
+        if (!validateBearerToken(authorizationHeader as string)) {
+            res.status(StatusCodes.UNAUTHORIZED).json({ error: "Authentication failed" });
+            return;
+        }
+        console.log("Bearer token is correct!");
+
+        next();
+    } catch (error) {
+        res.status(StatusCodes.INTERNAL_SERVER_ERROR)
+            .json({ error: "Internal Server Error" });
+    }
+};
+
+
+

server/src/models/location.ts

@@ -8,6 +8,8 @@ export class Location extends Model {
   public wifi_longitude!: number;
   public gnss_latitude!: number;
   public gnss_longitude!: number;
+  public ttn_gw_latitude!: number;
+  public ttn_gw_longitude!: number;
   public created_at_utc!: Date;
   public updated_at_utc!: Date;
 }
@@ -18,27 +20,27 @@ Location.init(
       type: DataTypes.UUID,
       defaultValue: DataTypes.UUIDV4,
       primaryKey: true,
-      allowNull: false,
     },
     lp_ttn_end_device_uplinks_id: {
       type: DataTypes.UUID,
-      allowNull: false,
     },
     wifi_latitude: {
       type: DataTypes.NUMBER,
-      allowNull: true,
     },
     wifi_longitude: {
       type: DataTypes.NUMBER,
-      allowNull: true,
     },
     gnss_latitude: {
       type: DataTypes.NUMBER,
-      allowNull: true,
     },
     gnss_longitude: {
       type: DataTypes.NUMBER,
-      allowNull: true,
+    },
+    ttn_gw_latitude: {
+      type: DataTypes.NUMBER,
+    },
+    ttn_gw_longitude: {
+      type: DataTypes.NUMBER,
     },
     created_at_utc: {
       type: DataTypes.DATE,

server/src/models/lpTtnEndDeviceUplinks.ts

@@ -10,8 +10,6 @@ export class LpTtnEndDeviceUplinks extends Model {
   public dev_addr!: string;
   public received_at_utc!: Date;
   public battery!: number;
-  public latitude!: number;
-  public longitude!: number;
   public created_at_utc!: Date;
   public updated_at_utc!: Date;
 }
@@ -30,35 +28,21 @@ LpTtnEndDeviceUplinks.init(
     },
     application_ids: {
       type: DataTypes.STRING,
-      allowNull: true,
     },
     dev_eui: {
       type: DataTypes.STRING,
-      allowNull: true,
     },
     join_eui: {
       type: DataTypes.STRING,
-      allowNull: true,
     },
     dev_addr: {
       type: DataTypes.STRING,
-      allowNull: true,
     },
     received_at_utc: {
       type: DataTypes.DATE,
-      allowNull: true,
     },
     battery: {
       type: DataTypes.NUMBER,
-      allowNull: true,
-    },
-    latitude: {
-      type: DataTypes.NUMBER,
-      allowNull: true,
-    },
-    longitude: {
-      type: DataTypes.NUMBER,
-      allowNull: true,
     },
     created_at_utc: {
       type: DataTypes.DATE,

server/src/models/ttnGatewayReception.ts

@@ -32,23 +32,18 @@ TtnGatewayReception.init(
     },
     eui: {
       type: DataTypes.STRING,
-      allowNull: false,
     },
     rssi: {
       type: DataTypes.NUMBER,
-      allowNull: true,
     },
     latitude: {
       type: DataTypes.NUMBER,
-      allowNull: true,
     },
     longitude: {
       type: DataTypes.NUMBER,
-      allowNull: true,
     },
     altitude: {
       type: DataTypes.NUMBER,
-      allowNull: true,
     },
     created_at_utc: {
       type: DataTypes.DATE,

server/src/models/ttnMessage.ts

@@ -6,14 +6,14 @@ export interface TtnMessage {
     };
     dev_eui: string;
     join_eui: string;
-    dev_addr: string;
+    dev_addr?: string;
   };
   correlation_ids: string[];
   received_at: string;
   uplink_message: {
-    session_key_id: string;
+    session_key_id?: string;
     f_port?: number;
-    f_cnt: number;
+    f_cnt?: number;
     frm_payload?: string;
     decoded_payload?: {
       err: number;
@@ -22,8 +22,8 @@ export interface TtnMessage {
           {
             measurementId: "4200";
             measurementValue: any[];
-            motionId: number;
+            motionId?: number;
-            timestamp: number;
+            timestamp?: number;
             type: "Event Status";
           },
           {
@@ -32,29 +32,29 @@ export interface TtnMessage {
               mac: string;
               rssi: number;
             }[];
-            motionId: number;
+            motionId?: number;
-            timestamp: number;
+            timestamp?: number;
             type: "Wi-Fi Scan";
           },
           {
             measurementId: "3000";
             measurementValue: number;
-            motionId: number;
+            motionId?: number;
-            timestamp: number;
+            timestamp?: number;
             type: "Battery";
           },
           {
             measurementId: "4197";
             measurementValue: number;
-            motionId: number;
+            motionId?: number;
-            timestamp: number;
+            timestamp?: number;
             type: "Longitude";
           },
           {
             measurementId: "4198";
             measurementValue: number;
-            motionId: number;
+            motionId?: number;
-            timestamp: number;
+            timestamp?: number;
             type: "Latitude";
           }
         ]
@@ -67,44 +67,44 @@ export interface TtnMessage {
         gateway_id: string;
         eui?: string;
       };
-      time: string;
+      time?: string;
       timestamp?: number;
       rssi: number;
       channel_rssi: number;
-      snr: number;
+      snr?: number;
-      location: {
+      location?: {
         latitude: number;
         longitude: number;
-        altitude: number;
+        altitude?: number;
         source?: string;
       };
-      uplink_token: string;
+      uplink_token?: string;
       channel_index?: number;
-      received_at: string;
+      received_at?: string;
     }[];
     settings: {
       data_rate: {
         lora: {
           bandwidth: number;
           spreading_factor: number;
-          coding_rate: string;
+          coding_rate?: string;
         };
       };
       frequency: string;
       timestamp?: number;
       time?: Date;
     };
-    received_at: Date;
+    received_at?: Date;
     confirmed?: boolean;
-    consumed_airtime: string;
+    consumed_airtime?: string;
-    version_ids: {
+    version_ids?: {
       brand_id: string;
       model_id: string;
       hardware_version: string;
       firmware_version: string;
       band_id: string;
     };
-    network_ids: {
+    network_ids?: {
       net_id: string;
       ns_id: string;
       tenant_id: string;

server/src/models/wifiScan.ts

@@ -30,15 +30,15 @@ WifiScan.init(
     },
     rssi: {
       type: DataTypes.NUMBER,
-      allowNull: true,
+      allowNull: false,
     },
     latitude: {
       type: DataTypes.NUMBER,
-      allowNull: true,
+      allowNull: false,
     },
     longitude: {
       type: DataTypes.NUMBER,
-      allowNull: true,
+      allowNull: false,
     },
     created_at_utc: {
       type: DataTypes.DATE,

server/src/proxy/wigle.ts

@@ -47,16 +47,22 @@ export const getLocationForWifi = async (
   try {
     const url = `${process.env.WIGLE_BASE_URL!}${process.env
       .WIGLE_NETWORK_SEARCH!}?netid=${encodeURIComponent(mac)}`;
+
     const response = await fetch(url, {
       method: "GET",
       headers: {
         "Content-Type": "application/json",
-        Cookie: `auth=${process.env.WIGLE_TOKEN}`,
+        Authorization: `Basic ${process.env.WIGLE_TOKEN}`,
       },
     });
-    return await response.json();
+
+    if (response.ok) {
+      return await response.json();
+    }
+    console.log(response.status);
+    return undefined;
   } catch (error) {
-    console.error("Fehler beim Aufruf des Services:", error);
+    console.error("Error during call of API wigle.net:", error);
   }
 };
 

server/src/services/locationService.ts

@@ -2,6 +2,39 @@ import { inject, injectable } from "tsyringe";
 import { Location } from "../models/location";
 import { LocationRepository } from "../repositories/locationRepository";
 
+interface CreateLocationParams {
+  lp_ttn_end_device_uplinks_id: string;
+  wifi?: Coordinates;
+  gnss?: Coordinates;
+  ttn_gw?: Coordinates;
+}
+
+interface CreateLocationTriangulationParams {
+  lp_ttn_end_device_uplinks_id: string;
+  wifi: LocationSignal[];
+  ttn_gw: LocationSignal[];
+  gnss?: Coordinates;
+}
+
+interface LocationSignal extends Coordinates {
+  rssi: number;
+}
+
+interface Coordinates {
+  latitude: number;
+  longitude: number;
+}
+
+interface UpdateTtnGatewayReceptionParams {
+  ttn_gateway_reception_id: string;
+  gateway_id?: string;
+  eui?: string;
+  rssi?: number;
+  latitude?: number;
+  longitude?: number;
+  altitude?: number;
+}
+
 @injectable()
 export class LocationService {
   constructor(
@@ -17,8 +50,30 @@ export class LocationService {
     return this.repository.findById(id);
   }
 
-  public async createLocation(data: Partial<Location>) {
+  public async createLocation(data: CreateLocationParams) {
-    return this.repository.create(data);
+    return this.repository.create({
+      lp_ttn_end_device_uplinks_id: data.lp_ttn_end_device_uplinks_id,
+      wifi_latitude: data.wifi?.latitude,
+      wifi_longitude: data.wifi?.longitude,
+      ttn_gw_latitude: data.ttn_gw?.latitude,
+      ttn_gw_longitude: data.ttn_gw?.longitude,
+      gnss_latitude: data.gnss?.latitude,
+      gnss_longitude: data.gnss?.longitude,
+    });
+  }
+
+  public async createLocationFromTriangulation(
+    data: CreateLocationTriangulationParams
+  ) {
+    const wifi_location = this.calculateVirtualLocation(data.wifi);
+    const gateway_location = this.calculateVirtualLocation(data.ttn_gw);
+
+    return this.createLocation({
+      lp_ttn_end_device_uplinks_id: data.lp_ttn_end_device_uplinks_id,
+      wifi: wifi_location,
+      ttn_gw: gateway_location,
+      gnss: data.gnss,
+    });
   }
 
   public async updateLocation(id: string, data: Partial<Location>) {
@@ -28,4 +83,25 @@ export class LocationService {
   public async deleteLocation(id: string) {
     return this.repository.delete(id);
   }
+
+  private calculateVirtualLocation(locations: LocationSignal[]) {
+    if (locations.length === 0) return undefined;
+
+    const { totalWeight, weightedLatitude, weightedLongitude } =
+      locations.reduce(
+        (acc, { latitude, longitude, rssi }) => {
+          const weight = 1 / Math.abs(rssi);
+          acc.totalWeight += weight;
+          acc.weightedLatitude += latitude * weight;
+          acc.weightedLongitude += longitude * weight;
+          return acc;
+        },
+        { totalWeight: 0, weightedLatitude: 0, weightedLongitude: 0 }
+      );
+
+    return {
+      latitude: weightedLatitude / totalWeight,
+      longitude: weightedLongitude / totalWeight,
+    };
+  }
 }

server/src/services/ttnGatewayReceptionService.ts

@@ -1,7 +1,26 @@
 import { inject, injectable } from "tsyringe";
-import { TtnGatewayReception } from "../models/ttnGatewayReception";
 import { TtnGatewayReceptionRepository } from "../repositories/ttnGatewayReceptionRepository";
 
+interface CreateTtnGatewayReceptionParams {
+  lp_ttn_end_device_uplinks_id: string;
+  gateway_id: string;
+  eui?: string;
+  rssi?: number;
+  latitude?: number;
+  longitude?: number;
+  altitude?: number;
+}
+
+interface UpdateTtnGatewayReceptionParams {
+  ttn_gateway_reception_id: string;
+  gateway_id?: string;
+  eui?: string;
+  rssi?: number;
+  latitude?: number;
+  longitude?: number;
+  altitude?: number;
+}
+
 @injectable()
 export class TtnGatewayReceptionService {
   constructor(
@@ -17,19 +36,24 @@ export class TtnGatewayReceptionService {
     return this.repository.findById(id);
   }
 
-  public async createGatewayReception(data: Partial<TtnGatewayReception>) {
+  public async createTtnGatewayReception(
-    return this.repository.create(data);
+    data: CreateTtnGatewayReceptionParams
-  }
-
-  public async createGatewayReceptions(data: Partial<TtnGatewayReception>[]) {
-    return this.repository.createMany(data);
-  }
-
-  public async updateGatewayReception(
-    id: string,
-    data: Partial<TtnGatewayReception>
   ) {
-    return this.repository.update(id, data);
+    if (data.latitude !== undefined && data.longitude !== undefined)
+      return this.repository.create(data);
+  }
+
+  public async filterAndInsertGatewayReception(
+    data: CreateTtnGatewayReceptionParams[]
+  ) {
+    const result = await Promise.all(
+      data.map(async (gateway) => await this.createTtnGatewayReception(gateway))
+    );
+    return result.filter((gateway) => gateway !== undefined);
+  }
+
+  public async updateGatewayReception(data: UpdateTtnGatewayReceptionParams) {
+    return this.repository.update(data.ttn_gateway_reception_id, data);
   }
 
   public async deleteGatewayReception(id: string) {

server/src/services/wifiScanService.ts

@@ -1,7 +1,21 @@
 import { inject, injectable } from "tsyringe";
-import { WifiScan } from "../models/wifiScan";
+import { getLocationForWifiMemoized } from "../proxy/wigle";
 import { WifiScanRepository } from "../repositories/wifiScanRepository";
 
+interface CreateWifiScanParams {
+  lp_ttn_end_device_uplinks_id: string;
+  mac: string;
+  rssi: number;
+}
+
+interface UpdateWifiScanParams {
+  wifi_scan_id: string;
+  mac?: string;
+  rssi?: number;
+  latitude?: number;
+  longitude?: number;
+}
+
 @injectable()
 export class WifiScanService {
   constructor(
@@ -16,16 +30,28 @@ export class WifiScanService {
     return this.repository.findById(id);
   }
 
-  public async createWifiScan(data: Partial<WifiScan>) {
+  public async createWifiScan(data: CreateWifiScanParams) {
-    return this.repository.create(data);
+    const apiResponse = await getLocationForWifiMemoized(data.mac);
+
+    if (apiResponse !== undefined && apiResponse.results.length > 0)
+      return this.repository.create({
+        ...data,
+        latitude: apiResponse.results[0].trilat,
+        longitude: apiResponse.results[0].trilong,
+      });
   }
 
-  public async createWifiScans(data: Partial<WifiScan>[]) {
+  public async createWifiScans(data: CreateWifiScanParams[]) {
-    return this.repository.createMany(data);
+    let wifiScans = await Promise.all(
+      data.map(async (wifi) => {
+        return await this.createWifiScan(wifi);
+      })
+    );
+    return wifiScans.filter((wifi) => wifi !== undefined);
   }
 
-  public async updateWifiScan(id: string, data: Partial<WifiScan>) {
+  public async updateWifiScan(data: UpdateWifiScanParams) {
-    return this.repository.update(id, data);
+    return this.repository.update(data.wifi_scan_id, data);
   }
 
   public async deleteWifiScan(id: string) {

server/src/validation/ttn/ttnMessageValidation.ts

@@ -8,14 +8,14 @@ export const ttnMessageValidator = z.object({
     }),
     dev_eui: z.string(),
     join_eui: z.string(),
-    dev_addr: z.string(),
+    dev_addr: z.string().optional(),
   }),
   correlation_ids: z.array(z.string()),
   received_at: z.string(),
   uplink_message: z.object({
-    session_key_id: z.string(),
+    session_key_id: z.string().optional(),
     f_port: z.number().optional(),
-    f_cnt: z.number(),
+    f_cnt: z.number().optional(),
     frm_payload: z.string().optional(),
     decoded_payload: z
       .object({
@@ -25,8 +25,8 @@ export const ttnMessageValidator = z.object({
             z.object({
               measurementId: z.string(),
               measurementValue: z.union([z.array(z.any()), z.number()]),
-              motionId: z.number(),
+              motionId: z.number().optional(),
-              timestamp: z.number(),
+              timestamp: z.number().optional(),
               type: z.string(),
             })
           )
@@ -41,20 +41,22 @@ export const ttnMessageValidator = z.object({
           gateway_id: z.string(),
           eui: z.string().optional(),
         }),
-        time: z.string(),
+        time: z.string().optional(),
         timestamp: z.number().optional(),
         rssi: z.number(),
         channel_rssi: z.number(),
-        snr: z.number(),
+        snr: z.number().optional(),
-        location: z.object({
+        location: z
-          latitude: z.number(),
+          .object({
-          longitude: z.number(),
+            latitude: z.number(),
-          altitude: z.number(),
+            longitude: z.number(),
-          source: z.string().optional(),
+            altitude: z.number().optional(),
-        }),
+            source: z.string().optional(),
-        uplink_token: z.string(),
+          })
+          .optional(),
+        uplink_token: z.string().optional(),
         channel_index: z.number().optional(),
-        received_at: z.string(),
+        received_at: z.string().optional(),
       })
     ),
     settings: z.object({
@@ -62,29 +64,33 @@ export const ttnMessageValidator = z.object({
         lora: z.object({
           bandwidth: z.number(),
           spreading_factor: z.number(),
-          coding_rate: z.string(),
+          coding_rate: z.string().optional(),
         }),
       }),
       frequency: z.string(),
       timestamp: z.number().optional(),
       time: z.string().optional(),
     }),
-    received_at: z.string(),
+    received_at: z.string().optional(),
     confirmed: z.boolean().optional(),
-    consumed_airtime: z.string(),
+    consumed_airtime: z.string().optional(),
-    version_ids: z.object({
+    version_ids: z
-      brand_id: z.string(),
+      .object({
-      model_id: z.string(),
+        brand_id: z.string(),
-      hardware_version: z.string(),
+        model_id: z.string(),
-      firmware_version: z.string(),
+        hardware_version: z.string(),
-      band_id: z.string(),
+        firmware_version: z.string(),
-    }),
+        band_id: z.string(),
-    network_ids: z.object({
+      })
-      net_id: z.string(),
+      .optional(),
-      ns_id: z.string(),
+    network_ids: z
-      tenant_id: z.string(),
+      .object({
-      cluster_id: z.string(),
+        net_id: z.string(),
-      cluster_address: z.string(),
+        ns_id: z.string(),
-    }),
+        tenant_id: z.string(),
+        cluster_id: z.string(),
+        cluster_address: z.string(),
+      })
+      .optional(),
   }),
 });