add metric TTN Uplinks

Reviewed-on: #11

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,7 @@ 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=10000          
-GET_LOCATION_WIFI_PRIMITIVE=true  

server/package-lock.json

@@ -7,14 +7,14 @@
     "": {
       "name": "locationhub",
       "version": "1.0.0",
-      "license": "ISC",
+      "license": "MIT",
       "dependencies": {
         "cors": "^2.8.5",
         "dotenv": "^16.4.7",
         "express": "^4.21.2",
         "http-status-codes": "^2.3.0",
         "mariadb": "^3.4.0",
-        "memoizee": "^0.4.17",
+        "prom-client": "^15.1.3",
         "reflect-metadata": "^0.2.2",
         "sequelize": "^6.37.5",
         "swagger-jsdoc": "^6.2.8",
@@ -24,7 +24,6 @@
       },
       "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",
@@ -122,6 +121,15 @@
       "integrity": "sha512-4JQNk+3mVzK3xh2rqd6RB4J46qUR19azEHBneZyTZM+c456qOrbbM/5xcR8huNCCcbVt7+UmizG6GuUvPvKUYg==",
       "license": "MIT"
     },
+    "node_modules/@opentelemetry/api": {
+      "version": "1.9.0",
+      "resolved": "https://registry.npmjs.org/@opentelemetry/api/-/api-1.9.0.tgz",
+      "integrity": "sha512-3giAOQvZiH5F9bMlMiv8+GSPMeqg0dbaeo58/0SlA9sxSqZhnUtxzX9/2FzyhS9sWQf5S0GJE0AKBrFqjpeYcg==",
+      "license": "Apache-2.0",
+      "engines": {
+        "node": ">=8.0.0"
+      }
+    },
     "node_modules/@scarf/scarf": {
       "version": "1.4.0",
       "resolved": "https://registry.npmjs.org/@scarf/scarf/-/scarf-1.4.0.tgz",
@@ -232,13 +240,6 @@
       "integrity": "sha512-5+fP8P8MFNC+AyZCDxrB2pkZFPGzqQWUzpSeuuVLvm8VMcorNYavBqoFcxK8bQz4Qsbn4oUEEem4wDLfcysGHA==",
       "license": "MIT"
     },
-    "node_modules/@types/memoizee": {
-      "version": "0.4.11",
-      "resolved": "https://registry.npmjs.org/@types/memoizee/-/memoizee-0.4.11.tgz",
-      "integrity": "sha512-2gyorIBZu8GoDr9pYjROkxWWcFtHCquF7TVbN2I+/OvgZhnIGQS0vX5KJz4lXNKb8XOSfxFOSG5OLru1ESqLUg==",
-      "dev": true,
-      "license": "MIT"
-    },
     "node_modules/@types/mime": {
       "version": "1.3.5",
       "resolved": "https://registry.npmjs.org/@types/mime/-/mime-1.3.5.tgz",
@@ -395,6 +396,12 @@
         "url": "https://github.com/sponsors/sindresorhus"
       }
     },
+    "node_modules/bintrees": {
+      "version": "1.0.2",
+      "resolved": "https://registry.npmjs.org/bintrees/-/bintrees-1.0.2.tgz",
+      "integrity": "sha512-VOMgTMwjAaUG580SXn3LacVgjurrbMme7ZZNYGSSV7mmtY6QQRh0Eg3pwIcntQ77DErK1L0NxkbetjcoXzVwKw==",
+      "license": "MIT"
+    },
     "node_modules/body-parser": {
       "version": "1.20.3",
       "resolved": "https://registry.npmjs.org/body-parser/-/body-parser-1.20.3.tgz",
@@ -582,19 +589,6 @@
       "dev": true,
       "license": "MIT"
     },
-    "node_modules/d": {
-      "version": "1.0.2",
-      "resolved": "https://registry.npmjs.org/d/-/d-1.0.2.tgz",
-      "integrity": "sha512-MOqHvMWF9/9MX6nza0KgvFH4HpMU0EF5uUDXqX/BtxtU8NfB0QzRtJ8Oe/6SuS4kbhyzVJwjd97EA4PKrzJ8bw==",
-      "license": "ISC",
-      "dependencies": {
-        "es5-ext": "^0.10.64",
-        "type": "^2.7.2"
-      },
-      "engines": {
-        "node": ">=0.12"
-      }
-    },
     "node_modules/debug": {
       "version": "2.6.9",
       "resolved": "https://registry.npmjs.org/debug/-/debug-2.6.9.tgz",
@@ -731,79 +725,12 @@
         "node": ">= 0.4"
       }
     },
-    "node_modules/es5-ext": {
-      "version": "0.10.64",
-      "resolved": "https://registry.npmjs.org/es5-ext/-/es5-ext-0.10.64.tgz",
-      "integrity": "sha512-p2snDhiLaXe6dahss1LddxqEm+SkuDvV8dnIQG0MWjyHpcMNfXKPE+/Cc0y+PhxJX3A4xGNeFCj5oc0BUh6deg==",
-      "hasInstallScript": true,
-      "license": "ISC",
-      "dependencies": {
-        "es6-iterator": "^2.0.3",
-        "es6-symbol": "^3.1.3",
-        "esniff": "^2.0.1",
-        "next-tick": "^1.1.0"
-      },
-      "engines": {
-        "node": ">=0.10"
-      }
-    },
-    "node_modules/es6-iterator": {
-      "version": "2.0.3",
-      "resolved": "https://registry.npmjs.org/es6-iterator/-/es6-iterator-2.0.3.tgz",
-      "integrity": "sha512-zw4SRzoUkd+cl+ZoE15A9o1oQd920Bb0iOJMQkQhl3jNc03YqVjAhG7scf9C5KWRU/R13Orf588uCC6525o02g==",
-      "license": "MIT",
-      "dependencies": {
-        "d": "1",
-        "es5-ext": "^0.10.35",
-        "es6-symbol": "^3.1.1"
-      }
-    },
-    "node_modules/es6-symbol": {
-      "version": "3.1.4",
-      "resolved": "https://registry.npmjs.org/es6-symbol/-/es6-symbol-3.1.4.tgz",
-      "integrity": "sha512-U9bFFjX8tFiATgtkJ1zg25+KviIXpgRvRHS8sau3GfhVzThRQrOeksPeT0BWW2MNZs1OEWJ1DPXOQMn0KKRkvg==",
-      "license": "ISC",
-      "dependencies": {
-        "d": "^1.0.2",
-        "ext": "^1.7.0"
-      },
-      "engines": {
-        "node": ">=0.12"
-      }
-    },
-    "node_modules/es6-weak-map": {
-      "version": "2.0.3",
-      "resolved": "https://registry.npmjs.org/es6-weak-map/-/es6-weak-map-2.0.3.tgz",
-      "integrity": "sha512-p5um32HOTO1kP+w7PRnB+5lQ43Z6muuMuIMffvDN8ZB4GcnjLBV6zGStpbASIMk4DCAvEaamhe2zhyCb/QXXsA==",
-      "license": "ISC",
-      "dependencies": {
-        "d": "1",
-        "es5-ext": "^0.10.46",
-        "es6-iterator": "^2.0.3",
-        "es6-symbol": "^3.1.1"
-      }
-    },
     "node_modules/escape-html": {
       "version": "1.0.3",
       "resolved": "https://registry.npmjs.org/escape-html/-/escape-html-1.0.3.tgz",
       "integrity": "sha512-NiSupZ4OeuGwr68lGIeym/ksIZMJodUGOSCZ/FSnTxcrekbvqrgdUxlJOMpijaKZVjAJrWrGs/6Jy8OMuyj9ow==",
       "license": "MIT"
     },
-    "node_modules/esniff": {
-      "version": "2.0.1",
-      "resolved": "https://registry.npmjs.org/esniff/-/esniff-2.0.1.tgz",
-      "integrity": "sha512-kTUIGKQ/mDPFoJ0oVfcmyJn4iBDRptjNVIzwIFR7tqWXdVI9xfA2RMwY/gbSpJG3lkdWNEjLap/NqVHZiJsdfg==",
-      "license": "ISC",
-      "dependencies": {
-        "d": "^1.0.1",
-        "es5-ext": "^0.10.62",
-        "event-emitter": "^0.3.5",
-        "type": "^2.7.2"
-      },
-      "engines": {
-        "node": ">=0.10"
-      }
-    },
     "node_modules/esutils": {
       "version": "2.0.3",
       "resolved": "https://registry.npmjs.org/esutils/-/esutils-2.0.3.tgz",
@@ -822,16 +749,6 @@
         "node": ">= 0.6"
       }
     },
-    "node_modules/event-emitter": {
-      "version": "0.3.5",
-      "resolved": "https://registry.npmjs.org/event-emitter/-/event-emitter-0.3.5.tgz",
-      "integrity": "sha512-D9rRn9y7kLPnJ+hMq7S/nhvoKwwvVJahBi2BPmx3bvbsEdK3W9ii8cBSGjP+72/LnM4n6fo3+dkCX5FeTQruXA==",
-      "license": "MIT",
-      "dependencies": {
-        "d": "1",
-        "es5-ext": "~0.10.14"
-      }
-    },
     "node_modules/express": {
       "version": "4.21.2",
       "resolved": "https://registry.npmjs.org/express/-/express-4.21.2.tgz",
@@ -878,15 +795,6 @@
         "url": "https://opencollective.com/express"
       }
     },
-    "node_modules/ext": {
-      "version": "1.7.0",
-      "resolved": "https://registry.npmjs.org/ext/-/ext-1.7.0.tgz",
-      "integrity": "sha512-6hxeJYaL110a9b5TEJSj0gojyHQAmA2ch5Os+ySCiA1QGdS697XWY1pzsrSjqA9LDEEgdB/KypIlR59RcLuHYw==",
-      "license": "ISC",
-      "dependencies": {
-        "type": "^2.7.2"
-      }
-    },
     "node_modules/fill-range": {
       "version": "7.1.1",
       "resolved": "https://registry.npmjs.org/fill-range/-/fill-range-7.1.1.tgz",
@@ -1192,12 +1100,6 @@
         "node": ">=0.12.0"
       }
     },
-    "node_modules/is-promise": {
-      "version": "2.2.2",
-      "resolved": "https://registry.npmjs.org/is-promise/-/is-promise-2.2.2.tgz",
-      "integrity": "sha512-+lP4/6lKUBfQjZ2pdxThZvLUAafmZb8OAxFb8XXtiQmS35INgr85hdOGoEs124ez1FCnZJt6jau/T+alh58QFQ==",
-      "license": "MIT"
-    },
     "node_modules/js-yaml": {
       "version": "4.1.0",
       "resolved": "https://registry.npmjs.org/js-yaml/-/js-yaml-4.1.0.tgz",
@@ -1240,15 +1142,6 @@
       "integrity": "sha512-JNAzZcXrCt42VGLuYz0zfAzDfAvJWW6AfYlDBQyDV5DClI2m5sAmK+OIO7s59XfsRsWHp02jAJrRadPRGTt6SQ==",
       "license": "ISC"
     },
-    "node_modules/lru-queue": {
-      "version": "0.1.0",
-      "resolved": "https://registry.npmjs.org/lru-queue/-/lru-queue-0.1.0.tgz",
-      "integrity": "sha512-BpdYkt9EvGl8OfWHDQPISVpcl5xZthb+XPsbELj5AQXxIC8IriDZIQYjBJPEm5rS420sjZ0TLEzRcq5KdBhYrQ==",
-      "license": "MIT",
-      "dependencies": {
-        "es5-ext": "~0.10.2"
-      }
-    },
     "node_modules/make-error": {
       "version": "1.3.6",
       "resolved": "https://registry.npmjs.org/make-error/-/make-error-1.3.6.tgz",
@@ -1302,25 +1195,6 @@
         "node": ">= 0.6"
       }
     },
-    "node_modules/memoizee": {
-      "version": "0.4.17",
-      "resolved": "https://registry.npmjs.org/memoizee/-/memoizee-0.4.17.tgz",
-      "integrity": "sha512-DGqD7Hjpi/1or4F/aYAspXKNm5Yili0QDAFAY4QYvpqpgiY6+1jOfqpmByzjxbWd/T9mChbCArXAbDAsTm5oXA==",
-      "license": "ISC",
-      "dependencies": {
-        "d": "^1.0.2",
-        "es5-ext": "^0.10.64",
-        "es6-weak-map": "^2.0.3",
-        "event-emitter": "^0.3.5",
-        "is-promise": "^2.2.2",
-        "lru-queue": "^0.1.0",
-        "next-tick": "^1.1.0",
-        "timers-ext": "^0.1.7"
-      },
-      "engines": {
-        "node": ">=0.12"
-      }
-    },
     "node_modules/merge-descriptors": {
       "version": "1.0.3",
       "resolved": "https://registry.npmjs.org/merge-descriptors/-/merge-descriptors-1.0.3.tgz",
@@ -1420,12 +1294,6 @@
         "node": ">= 0.6"
       }
     },
-    "node_modules/next-tick": {
-      "version": "1.1.0",
-      "resolved": "https://registry.npmjs.org/next-tick/-/next-tick-1.1.0.tgz",
-      "integrity": "sha512-CXdUiJembsNjuToQvxayPZF9Vqht7hewsvy2sOWafLvi2awflj9mOC6bHIg50orX8IJvWKY9wYQ/zB2kogPslQ==",
-      "license": "ISC"
-    },
     "node_modules/nodemon": {
       "version": "3.1.9",
       "resolved": "https://registry.npmjs.org/nodemon/-/nodemon-3.1.9.tgz",
@@ -1582,6 +1450,19 @@
         "url": "https://github.com/sponsors/jonschlinkert"
       }
     },
+    "node_modules/prom-client": {
+      "version": "15.1.3",
+      "resolved": "https://registry.npmjs.org/prom-client/-/prom-client-15.1.3.tgz",
+      "integrity": "sha512-6ZiOBfCywsD4k1BN9IX0uZhF+tJkV8q8llP64G5Hajs4JOeVLPCwpPVcpXy3BwYiUGgyJzsJJQeOIv7+hDSq8g==",
+      "license": "Apache-2.0",
+      "dependencies": {
+        "@opentelemetry/api": "^1.4.0",
+        "tdigest": "^0.1.1"
+      },
+      "engines": {
+        "node": "^16 || ^18 || >=20"
+      }
+    },
     "node_modules/proxy-addr": {
       "version": "2.0.7",
       "resolved": "https://registry.npmjs.org/proxy-addr/-/proxy-addr-2.0.7.tgz",
@@ -2021,17 +1902,13 @@
         "express": ">=4.0.0 || >=5.0.0-beta"
       }
     },
-    "node_modules/timers-ext": {
+    "node_modules/tdigest": {
-      "version": "0.1.8",
+      "version": "0.1.2",
-      "resolved": "https://registry.npmjs.org/timers-ext/-/timers-ext-0.1.8.tgz",
+      "resolved": "https://registry.npmjs.org/tdigest/-/tdigest-0.1.2.tgz",
-      "integrity": "sha512-wFH7+SEAcKfJpfLPkrgMPvvwnEtj8W4IurvEyrKsDleXnKLCDw71w8jltvfLa8Rm4qQxxT4jmDBYbJG/z7qoww==",
+      "integrity": "sha512-+G0LLgjjo9BZX2MfdvPfH+MKLCrxlXSYec5DaPYP1fe6Iyhf0/fSmJ0bFiZ1F8BT6cGXl2LpltQptzjXKWEkKA==",
-      "license": "ISC",
+      "license": "MIT",
       "dependencies": {
-        "es5-ext": "^0.10.64",
+        "bintrees": "1.0.2"
-        "next-tick": "^1.1.0"
-      },
-      "engines": {
-        "node": ">=0.12"
       }
     },
     "node_modules/to-regex-range": {
@@ -2134,12 +2011,6 @@
         "node": ">= 6.0.0"
       }
     },
-    "node_modules/type": {
-      "version": "2.7.3",
-      "resolved": "https://registry.npmjs.org/type/-/type-2.7.3.tgz",
-      "integrity": "sha512-8j+1QmAbPvLZow5Qpi6NCaN8FB60p/6x8/vfNqOk/hC+HuvFZhL4+WfekuhQLiqFZXOgQdrs3B+XxEmCc6b3FQ==",
-      "license": "ISC"
-    },
     "node_modules/type-is": {
       "version": "1.6.18",
       "resolved": "https://registry.npmjs.org/type-is/-/type-is-1.6.18.tgz",

server/package.json

@@ -10,14 +10,13 @@
   },
   "keywords": [],
   "author": "Hendrik Schutter, Philipp Schweizer",
-  "license": "ISC",
+  "license": "MIT",
   "devDependencies": {
     "@types/express": "^5.0.0",
     "@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",
@@ -25,7 +24,7 @@
     "express": "^4.21.2",
     "http-status-codes": "^2.3.0",
     "mariadb": "^3.4.0",
-    "memoizee": "^0.4.17",
+    "prom-client": "^15.1.3",
     "reflect-metadata": "^0.2.2",
     "sequelize": "^6.37.5",
     "swagger-jsdoc": "^6.2.8",

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,13 +16,30 @@ CREATE TABLE IF NOT EXISTS wifi_scan (
     lp_ttn_end_device_uplinks_id UUID,
     mac VARCHAR(255),
     rssi NUMERIC,
-    latitude DOUBLE,
-    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)
 );
 
+CREATE TABLE IF NOT EXISTS wifi_location (
+    mac VARCHAR(255) PRIMARY KEY,
+    latitude DOUBLE,
+    longitude DOUBLE,
+    request_limit_exceeded boolean NOT NULL,
+    location_not_resolvable boolean NOT NULL,
+    created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS wifi_location_history (
+    wifi_location_history_id UUID PRIMARY KEY,
+    mac VARCHAR(255),
+    latitude DOUBLE NOT NULL,
+    longitude DOUBLE NOT NULL,
+    created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+);
+
 CREATE TABLE IF NOT EXISTS ttn_gateway_reception (
     ttn_gateway_reception_id UUID PRIMARY KEY,
     lp_ttn_end_device_uplinks_id UUID,
@@ -46,6 +61,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/metricsController.ts

@@ -0,0 +1,81 @@
+import express, { Request, Response } from "express";
+import { container } from "tsyringe";
+import { Counter, Gauge, collectDefaultMetrics, register } from "prom-client";
+import { LocationService } from "../services/locationService";
+import { WifiLocationService } from "../services/wifiLocationService";
+import { TtnGatewayReceptionService } from "../services/ttnGatewayReceptionService";
+
+const router = express.Router();
+const locationService = container.resolve(LocationService);
+const wifiLocationService = container.resolve(WifiLocationService);
+const ttnGatewayReceptionService = container.resolve(TtnGatewayReceptionService);
+
+// Collect default system metrics (e.g., CPU, memory usage)
+const prefix = 'locationhub_';
+collectDefaultMetrics({ prefix });
+
+// Define a custom Counter metric
+const requestCounter = new Counter({
+  name: `${prefix}http_requests_total`,
+  help: "Total number of HTTP requests",
+  labelNames: ["method", "route", "status"],
+});
+
+const locationsTotal = new Gauge({
+  name: `${prefix}locations_total`,
+  help: "Total number of location entries in database",
+  labelNames: ["database"],
+});
+
+const wifiLocationTotal = new Gauge({
+  name: `${prefix}wifi_locations_total`,
+  help: "Total number of wifi location entries in database",
+  labelNames: ["database"],
+});
+
+const wifiLocationNotResolvable = new Gauge({
+  name: `${prefix}wifi_locations_not_resolvable`,
+  help: "Unresolved number of wifi location entries in database",
+  labelNames: ["database"],
+});
+
+const wifiLocationRequestLimitExceeded = new Gauge({
+  name: `${prefix}wifi_locations_request_limit_exceeded`,
+  help: "Unresolved number of wifi location because request limit exceeded entries in database",
+  labelNames: ["database"],
+});
+
+const ttnGatewayReceptions = new Gauge({
+  name: `${prefix}ttn_gateway_receptions`,
+  help: "Total number of TTN Gateway receptions entries in database",
+  labelNames: ["database"],
+});
+
+
+// Define the metrics endpoint
+router.get("/", async (req: Request, res: Response) => {
+  try {
+    console.log("Metric Endpoint triggered");
+
+    locationsTotal.set((await locationService.getAllLocations()).length);
+    wifiLocationTotal.set((await wifiLocationService.getAllWifiLocations()).length);
+    wifiLocationNotResolvable.set((await wifiLocationService.getAllWifiLocationsByNotResolvable()).length);
+    wifiLocationRequestLimitExceeded.set((await wifiLocationService.getAllWifiLocationsByRequestLimitExceeded()).length);
+    ttnGatewayReceptions.set((await ttnGatewayReceptionService.getAllGatewayReceptions()).length);
+
+    // Increment the counter with labels
+    requestCounter.inc({ method: req.method, route: req.route.path, status: 200 });
+
+    // Expose metrics in Prometheus format
+    res.set("Content-Type", register.contentType);
+    res.send(await register.metrics());
+  } catch (error) {
+    // Increment the counter for errors
+    requestCounter.inc({ method: req.method, route: req.route.path, status: 500 });
+
+    console.error("Error running metrics endpoint:", error);
+    res.status(500).json({ error: "Error running metrics endpoint" });
+  }
+});
+
+export default router;

server/src/controller/ttnController.ts

@@ -1,12 +1,10 @@
 import express, { Request, Response } from "express";
+import { StatusCodes } from "http-status-codes";
 import { container } from "tsyringe";
-import { domainEventEmitter } from "../config/eventEmitter";
+import { authenticateHeader } from "../middleware/authentificationMiddleware";
-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";
@@ -19,15 +17,17 @@ const ttnGatewayReceptionService = container.resolve(
   TtnGatewayReceptionService
 );
 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,13 +40,16 @@ router.post(
           battery: message.uplink_message.decoded_payload?.messages[0].find(
             (e) => e.type === "Battery"
           )?.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]
@@ -63,36 +66,47 @@ router.post(
           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 })),
-        ttnGateways: ttnGatewayReceptions.map((g) => ({
-          rssi: g.rssi,
-          altitude: g.altitude,
-          latitude: g.latitude,
-          longitude: g.longitude,
-        })),
-      };
-
-      domainEventEmitter.emit(TtnMessageReceivedEventName, event);
-
-      await Promise.all([
           wifiScanService.createWifiScans(wifiScans),
-        ttnGatewayReceptionService.createGatewayReceptions(
+          ttnGatewayReceptionService.filterAndInsertGatewayReception(
             ttnGatewayReceptions
           ),
         ]);
 
-      res.status(200);
+        locationService.createLocationFromTriangulation({
+          lp_ttn_end_device_uplinks_id,
+          wifi: wifiResults.map(({ mac, rssi }) => ({
+            mac,
+            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();
+      res.status(StatusCodes.OK).send();
     } 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/wifiLocationController.ts

@@ -0,0 +1,32 @@
+import express, { Request, Response } from "express";
+import { container } from "tsyringe";
+import { WifiLocationService } from "../services/wifiLocationService";
+
+const wifiLocationService = container.resolve(WifiLocationService);
+const router = express.Router();
+
+router.get("/", async (req: Request, res: Response) => {
+  try {
+    const wifiLocations = await wifiLocationService.getAllWifiLocations();
+    res.status(200).json(wifiLocations);
+  } catch (error) {
+    console.log(error);
+    res.status(500).json({ error: "Error retrieving wifi location" });
+  }
+});
+
+router.delete("/:id", async (req: Request, res: Response) => {
+  try {
+    const { id } = req.params;
+    const deleted = await wifiLocationService.deleteWifiLocation(id);
+    if (!deleted) {
+      res.status(404).json({ error: "Wifi Location not found" });
+      return;
+    }
+    res.status(204).send();
+  } catch (error) {
+    res.status(500).json({ error: "Error deleting wifi location" });
+  }
+});
+
+export default router;

server/src/controller/wifiLocationHistoryController.ts

@@ -0,0 +1,35 @@
+import express, { Request, Response } from "express";
+import { container } from "tsyringe";
+import { WifiLocationHistoryService } from "../services/wifiLocationHistoryService";
+
+const wifiLocationHistoryService = container.resolve(
+  WifiLocationHistoryService
+);
+const router = express.Router();
+
+router.get("/", async (req: Request, res: Response) => {
+  try {
+    const wifiLocationHistory =
+      await wifiLocationHistoryService.getAllWifiLocationHistories();
+    res.status(200).json(wifiLocationHistory);
+  } catch (error) {
+    res.status(500).json({ error: "Error retrieving wifi location history" });
+  }
+});
+
+router.get("/:id", async (req: Request, res: Response) => {
+  try {
+    const { id } = req.params;
+    const wifiLocationHistory =
+      await wifiLocationHistoryService.getWifiLocationHistoryById(id);
+    if (!wifiLocationHistory) {
+      res.status(404).json({ error: "Wifi location history not found" });
+      return;
+    }
+    res.status(200).json(wifiLocationHistory);
+  } catch (error) {
+    res.status(500).json({ error: "Error retrieving wifi location history" });
+  }
+});
+
+export default router;

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,14 +1,16 @@
 import dotenv from "dotenv";
 import express from "express";
 import "reflect-metadata";
-import "./eventHandler/ttnMessageReceivedEventHandler";
 const cors = require("cors");
 
 import locationRoutes from "./controller/locationController";
 import lpTtnEndDeviceUplinksRoutes from "./controller/lpTtnEndDeviceUplinksController";
 import ttnRoutes from "./controller/ttnController";
 import ttnGatewayReceptionRoutes from "./controller/ttnGatewayReceptionController";
+import wifiLocationRoutes from "./controller/wifiLocationController";
+import wifiLocationHistoryRoutes from "./controller/wifiLocationHistoryController";
 import wifiScanRoutes from "./controller/wifiScanController";
+import metricsRoutes from "./controller/metricsController";
 
 dotenv.config();
 
@@ -19,11 +21,14 @@ app.use(cors());
 app.use(express.json());
 
 app.use("/api/lp-ttn-end-device-uplinks", lpTtnEndDeviceUplinksRoutes);
-app.use("/api/wifi-scans", wifiScanRoutes);
 app.use("/api/ttn-gateway-receptions", ttnGatewayReceptionRoutes);
+app.use("/api/wifi-location-history", wifiLocationHistoryRoutes);
+app.use("/api/wifi-location", wifiLocationRoutes);
+app.use("/api/wifi-scans", wifiScanRoutes);
 app.use("/api/locations", locationRoutes);
 app.use("/api/ttn", ttnRoutes);
+app.use("/api/metrics", metricsRoutes);
 
 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,41 @@
+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/wifiLocation.ts

@@ -0,0 +1,52 @@
+import { DataTypes, Model } from "sequelize";
+import { sequelize } from "../database/database";
+
+export class WifiLocation extends Model {
+  public mac!: string;
+  public latitude!: number;
+  public longitude!: number;
+  public request_limit_exceeded!: boolean;
+  public location_not_resolvable!: boolean;
+  public created_at_utc!: Date;
+  public updated_at_utc!: Date;
+}
+
+WifiLocation.init(
+  {
+    mac: {
+      type: DataTypes.STRING,
+      primaryKey: true,
+      allowNull: false,
+    },
+    latitude: {
+      type: DataTypes.NUMBER,
+    },
+    longitude: {
+      type: DataTypes.NUMBER,
+    },
+    request_limit_exceeded: {
+      type: DataTypes.BOOLEAN,
+      defaultValue: false,
+    },
+    location_not_resolvable: {
+      type: DataTypes.BOOLEAN,
+      defaultValue: false,
+    },
+    created_at_utc: {
+      type: DataTypes.DATE,
+      defaultValue: DataTypes.NOW,
+      allowNull: false,
+    },
+    updated_at_utc: {
+      type: DataTypes.DATE,
+      defaultValue: DataTypes.NOW,
+      allowNull: false,
+    },
+  },
+  {
+    sequelize,
+    modelName: "WifiLocation",
+    tableName: "wifi_location",
+    timestamps: false,
+  }
+);

server/src/models/wifiLocationHistory.ts

@@ -0,0 +1,50 @@
+import { DataTypes, Model } from "sequelize";
+import { sequelize } from "../database/database";
+
+export class WifiLocationHistory extends Model {
+  public wifi_location_history_id!: string;
+  public mac!: string;
+  public latitude!: number;
+  public longitude!: number;
+  public created_at_utc!: Date;
+  public updated_at_utc!: Date;
+}
+
+WifiLocationHistory.init(
+  {
+    wifi_location_history_id: {
+      type: DataTypes.UUID,
+      defaultValue: DataTypes.UUIDV4,
+      primaryKey: true,
+      allowNull: false,
+    },
+    mac: {
+      type: DataTypes.STRING,
+      allowNull: false,
+    },
+    latitude: {
+      type: DataTypes.NUMBER,
+      allowNull: false,
+    },
+    longitude: {
+      type: DataTypes.NUMBER,
+      allowNull: false,
+    },
+    created_at_utc: {
+      type: DataTypes.DATE,
+      defaultValue: DataTypes.NOW,
+      allowNull: false,
+    },
+    updated_at_utc: {
+      type: DataTypes.DATE,
+      defaultValue: DataTypes.NOW,
+      allowNull: false,
+    },
+  },
+  {
+    sequelize,
+    modelName: "WifiLocationHistory",
+    tableName: "wifi_location_history",
+    timestamps: false,
+  }
+);

server/src/models/wifiScan.ts

@@ -6,8 +6,6 @@ export class WifiScan extends Model {
   public wifi_scan_id!: string;
   public mac!: string;
   public rssi!: number;
-  public latitude!: number;
-  public longitude!: number;
   public created_at_utc!: Date;
   public updated_at_utc!: Date;
 }
@@ -30,15 +28,7 @@ WifiScan.init(
     },
     rssi: {
       type: DataTypes.NUMBER,
-      allowNull: true,
+      allowNull: false,
-    },
-    latitude: {
-      type: DataTypes.NUMBER,
-      allowNull: true,
-    },
-    longitude: {
-      type: DataTypes.NUMBER,
-      allowNull: true,
     },
     created_at_utc: {
       type: DataTypes.DATE,

server/src/proxy/wigle.ts

@@ -1,4 +1,7 @@
-import memoizee from "memoizee";
+interface WigleApiResonse {
+  response?: WifiLocationResponse,
+  status_code: number,
+}
 
 interface WifiLocationResponse {
   success: boolean;
@@ -43,25 +46,26 @@ interface Result {
 
 export const getLocationForWifi = async (
   mac: string
-): Promise<WifiLocationResponse | undefined> => {
+): Promise<WigleApiResonse | undefined> => {
   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 { status_code: response.status, response: await response.json() };
+    }
+    console.log(response.status);
+    return { status_code: response.status };
   } catch (error) {
-    console.error("Fehler beim Aufruf des Services:", error);
+    console.error("Error during call of API wigle.net:", error);
+    return undefined;
   }
 };
-
-export const getLocationForWifiMemoized = memoizee(getLocationForWifi, {
-  maxAge: Number(process.env.GET_LOCATION_WIFI_MAX_AGE),
-  max: Number(process.env.GET_LOCATION_WIFI_MAX),
-  primitive: process.env.GET_LOCATION_WIFI_PRIMITIVE === "true",
-});

server/src/repositories/wifiLocationHistoryRepository.ts

@@ -0,0 +1,26 @@
+import { Attributes, FindOptions } from "sequelize";
+import { injectable } from "tsyringe";
+import { WifiLocationHistory } from "../models/wifiLocationHistory";
+
+@injectable()
+export class WifiLocationHistoryRepository {
+  public async findAll() {
+    return await WifiLocationHistory.findAll();
+  }
+
+  public async findOne(options?: FindOptions<Attributes<WifiLocationHistory>>) {
+    return await WifiLocationHistory.findOne(options);
+  }
+
+  public async findById(id: string) {
+    return await WifiLocationHistory.findByPk(id);
+  }
+
+  public async create(data: Partial<WifiLocationHistory>) {
+    return await WifiLocationHistory.create(data);
+  }
+
+  public async createMany(data: Partial<WifiLocationHistory>[]) {
+    return await WifiLocationHistory.bulkCreate(data);
+  }
+}

server/src/repositories/wifiLocationRepository.ts

@@ -0,0 +1,39 @@
+import { Attributes, FindOptions } from "sequelize";
+import { injectable } from "tsyringe";
+import { WifiLocation } from "../models/wifiLocation";
+
+@injectable()
+export class WifiLocationRepository {
+  public async findAll(options?: FindOptions<Attributes<WifiLocation>>) {
+    return await WifiLocation.findAll(options);
+  }
+
+  public async findById(id: string) {
+    return await WifiLocation.findByPk(id);
+  }
+
+  public async create(data: Partial<WifiLocation>) {
+    return await WifiLocation.create(data);
+  }
+
+  public async createMany(data: Partial<WifiLocation>[]) {
+    return await WifiLocation.bulkCreate(data);
+  }
+
+  public async update(id: string, data: Partial<WifiLocation>) {
+    const wifiScan = await this.findById(id);
+    if (wifiScan) {
+      return await wifiScan.update(data);
+    }
+    return null;
+  }
+
+  public async delete(id: string) {
+    const wifiScan = await this.findById(id);
+    if (wifiScan) {
+      await wifiScan.destroy();
+      return true;
+    }
+    return false;
+  }
+}

server/src/services/locationService.ts

@@ -1,12 +1,51 @@
 import { inject, injectable } from "tsyringe";
 import { Location } from "../models/location";
 import { LocationRepository } from "../repositories/locationRepository";
+import { WifiLocationService } from "./wifiLocationService";
+
+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: {
+    mac: string;
+    rssi: number;
+  }[];
+  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(
     @inject(LocationRepository)
-    private repository: LocationRepository
+    private repository: LocationRepository,
+    @inject(WifiLocationService)
+    private wifiLocationService: WifiLocationService
   ) { }
 
   public async getAllLocations() {
@@ -17,8 +56,32 @@ 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(
+      await this.enrichWifiObjectWithLocation(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 +91,48 @@ export class LocationService {
   public async deleteLocation(id: string) {
     return this.repository.delete(id);
   }
+
+  private async enrichWifiObjectWithLocation(
+    wifis: CreateLocationTriangulationParams["wifi"]
+  ) {
+    const enrichedWifi = await Promise.all(
+      wifis.map(async (wifi) => {
+        const location = await this.wifiLocationService.getWifiLocationByMac(
+          wifi.mac
+        );
+
+        return location?.latitude !== undefined &&
+          location.longitude !== undefined
+          ? {
+            rssi: wifi.rssi,
+            latitude: location.latitude,
+            longitude: location.longitude,
+          }
+          : null;
+      })
+    );
+
+    return enrichedWifi.filter((wifi) => wifi !== null);
+  }
+
+  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(
+    data: CreateTtnGatewayReceptionParams
+  ) {
+    if (data.latitude !== undefined && data.longitude !== undefined)
       return this.repository.create(data);
   }
 
-  public async createGatewayReceptions(data: Partial<TtnGatewayReception>[]) {
+  public async filterAndInsertGatewayReception(
-    return this.repository.createMany(data);
+    data: CreateTtnGatewayReceptionParams[]
+  ) {
+    const result = await Promise.all(
+      data.map(async (gateway) => await this.createTtnGatewayReception(gateway))
+    );
+    return result.filter((gateway) => gateway !== undefined);
   }
 
-  public async updateGatewayReception(
+  public async updateGatewayReception(data: UpdateTtnGatewayReceptionParams) {
-    id: string,
+    return this.repository.update(data.ttn_gateway_reception_id, data);
-    data: Partial<TtnGatewayReception>
-  ) {
-    return this.repository.update(id, data);
   }
 
   public async deleteGatewayReception(id: string) {

server/src/services/wifiLocationHistoryService.ts

@@ -0,0 +1,51 @@
+import { inject, injectable } from "tsyringe";
+import { WifiLocationHistoryRepository } from "../repositories/wifiLocationHistoryRepository";
+
+interface CreateWifiLocationHistoryParams {
+  mac: string;
+  latitude: number;
+  longitude: number;
+}
+
+interface UpdateWifiLocationHistoryParams {
+  mac: string;
+  latitude: number;
+  longitude: number;
+}
+
+@injectable()
+export class WifiLocationHistoryService {
+  constructor(
+    @inject(WifiLocationHistoryRepository)
+    private repository: WifiLocationHistoryRepository
+  ) {}
+
+  public async getAllWifiLocationHistories() {
+    return this.repository.findAll();
+  }
+
+  public async getWifiLocationHistoryById(id: string) {
+    return this.repository.findById(id);
+  }
+
+  public async createWifiLocationHistory(
+    data: CreateWifiLocationHistoryParams
+  ) {
+    const existingEntry = await this.repository.findOne({
+      where: {
+        mac: data.mac,
+      },
+      order: [["updated_at_utc", "DESC"]],
+    });
+
+    if (
+      !existingEntry ||
+      existingEntry.latitude !== data.latitude ||
+      existingEntry.longitude !== data.longitude
+    ) {
+      return await this.repository.create(data);
+    }
+
+    return existingEntry;
+  }
+}

server/src/services/wifiLocationService.ts

@@ -0,0 +1,92 @@
+import { Op } from "sequelize";
+import { inject, injectable } from "tsyringe";
+import { getLocationForWifi } from "../proxy/wigle";
+import { WifiLocationRepository } from "../repositories/wifiLocationRepository";
+import { WifiLocationHistoryService } from "./wifiLocationHistoryService";
+import { StatusCodes } from "http-status-codes";
+
+interface UpdateWifiLocationParams {
+  mac: string;
+  latitude: number;
+  longitude: number;
+}
+
+@injectable()
+export class WifiLocationService {
+  constructor(
+    @inject(WifiLocationRepository) private repository: WifiLocationRepository,
+    @inject(WifiLocationHistoryService)
+    private wifiLocationHistory: WifiLocationHistoryService
+  ) { }
+
+  public async getAllWifiLocations() {
+    return this.repository.findAll();
+  }
+
+  public async getAllWifiLocationsByAddresses(macAddresses: string[]) {
+    return this.repository.findAll({
+      where: { mac: { [Op.in]: macAddresses } },
+    });
+  }
+
+  public async getAllWifiLocationsByNotResolvable() {
+    return this.repository.findAll({
+      where: { location_not_resolvable: true },
+    });
+  }
+
+  public async getAllWifiLocationsByRequestLimitExceeded() {
+    return this.repository.findAll({
+      where: { request_limit_exceeded: true },
+    });
+  }
+
+  public async getWifiLocationByMac(mac: string) {
+    let wifiLocation = await this.repository.findById(mac);
+
+    if (wifiLocation) return wifiLocation;
+
+    const apiResponse = await getLocationForWifi(mac);
+
+    if (apiResponse == undefined) {
+      await this.repository.create({
+        mac,
+      });
+      return undefined;
+    }
+
+    if (apiResponse.response == undefined) {
+      const request_limit_exceeded = apiResponse.status_code === StatusCodes.TOO_MANY_REQUESTS;
+      await this.repository.create({
+        mac,
+        request_limit_exceeded,
+      });
+      return undefined;
+    }
+
+    if (apiResponse.response.totalResults == 0) {
+      await this.repository.create({ mac, location_not_resolvable: true });
+      return undefined;
+    }
+
+    wifiLocation = await this.repository.create({
+      mac,
+      latitude: apiResponse.response.results[0].trilat,
+      longitude: apiResponse.response.results[0].trilong,
+    });
+
+    await this.wifiLocationHistory.createWifiLocationHistory(
+      wifiLocation.dataValues
+    );
+
+    return wifiLocation;
+  }
+
+  public async updateWifiLocation(data: UpdateWifiLocationParams) {
+    return this.repository.update(data.mac, data);
+  }
+
+  public async deleteWifiLocation(id: string) {
+    return this.repository.delete(id);
+  }
+}

server/src/services/wifiScanService.ts

@@ -1,7 +1,18 @@
 import { inject, injectable } from "tsyringe";
-import { WifiScan } from "../models/wifiScan";
 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;
+}
+
 @injectable()
 export class WifiScanService {
   constructor(
@@ -16,16 +27,16 @@ export class WifiScanService {
     return this.repository.findById(id);
   }
 
-  public async createWifiScan(data: Partial<WifiScan>) {
+  public async createWifiScan(data: CreateWifiScanParams) {
       return this.repository.create(data);
   }
 
-  public async createWifiScans(data: Partial<WifiScan>[]) {
+  public async createWifiScans(data: CreateWifiScanParams[]) {
-    return this.repository.createMany(data);
+    return await this.repository.createMany(data);
   }
 
-  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
+          .object({
             latitude: z.number(),
             longitude: z.number(),
-          altitude: z.number(),
+            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
+      .object({
         brand_id: z.string(),
         model_id: z.string(),
         hardware_version: z.string(),
         firmware_version: z.string(),
         band_id: z.string(),
-    }),
+      })
-    network_ids: z.object({
+      .optional(),
+    network_ids: z
+      .object({
         net_id: z.string(),
         ns_id: z.string(),
         tenant_id: z.string(),
         cluster_id: z.string(),
         cluster_address: z.string(),
-    }),
+      })
+      .optional(),
   }),
 });