SQL DB schema (for rapid prototyping applications)

After a month of play with a Gateway, Devices, Integrations and Applications,
I think it’s time to dump traffic in my own database for more fine grained control/usage for my applications.

Some 10 minutes sketching, based on the Cayenne/HTTP integration, gets me this Schema below:

Question: Any suggestions from more advanced TTNers?

Allows for:

  • Sharing my back-end with other developers
  • any type of sensors (payloadfields value can be any datatype or JSON string)
  • moving gateways
  • easy cleanup of old testdata (_created_timestamp)

Next steps:

  • NodeJS/Express/Restify layer
  • React or, more likely, W3C WebComponents, front-end
1 Like

Current design:

Normalized it to:


I developed the HTTP Integration for a IoT Proof of Concept for a LAMP stack client, and couldn’t use NodeJS
so went with PHP/MySQL (that still is 80+ % of sites out there)

I share the PHP HTTP Integration (which also creates the MySQL DB Schema on first call) on GitHub;
it just has not been reviewed by anyone yet.
So all I know it ran on my clients environment; and runs on mine.
Comes with an MIT-Beer license (you buy me a beer if we ever meet in person)

I tried Free Hosting sites to make a public dashboard; but those sites don’t like being called with a HTTP Integration every 30 seconds. They took down my sites in hours or days claiming I broke the Terms of Service.

If anyone wants to review this PHP/MySQL homebrew in their own xxMP environment.
let me know at danny@engelman.nl

Dashboard is full of CSS4, HTML5 and ES6 (Custom Elements) so only runs on Chrome


Above Dashboard is all HTML5:


1 Like

hey man, thanks for sharing your project. Looks very promising.
Are you still maintaining the project?

I was looking for a simple ttn http integration with a hosted php/mysql site. It seems your solution might exceed that expectation.

There is little to maintain, it does what it does.

You DO need some PHP/MySQL understanding to get this running.

If someone posts an Issue on GitHub but doesn’t know PHP/MySQL I won’t help.

Hmm… maybe I never posted the GH Repo here :slight_smile: