TL;DR – Look ma — CODE that stores DATA which becomes usable in SALESFORCE via OData.

For the last couple of days, I’ve been talking about some hardware I’ve put together (here and here).  These efforts all come from a pretty simple problem I have:

My house is old, drafty and hard to heat. And I live in Chicago.

This problem is particularly noticeable in January and February, but July and August can be just as bad in the other direction so I need to address it somehow. Ugh.

Well, that which can be measured can be improved, right?  So that’s where I’m going to start. Measurement is actually the easy part. Simple Data Recorder handles that.  Where I’ve struggled is figuring out where to put that data which I measure, how to put it there and then what to do with it.  This post covers the first two.

Most of the time, I work on the Force.com side of the Salesforce1 platform.  I’m extremely comfortable in it, but the free Developer Edition will run out of storage very quickly if you start throwing a new record at it every minute or so. Enter the other side of Salesforce1: Heroku.

Naive IoT Host, deployed to Heroku, solves my storage problem. I’m using the free tier of everything so far, and should be able to for ~20 days at my current rate of data production. You can see a live dataclip of conditions in my house right now should you be interested. If you’ve even glanced at the code, you know it won’t scale to 100,000 devices any time soon.  But it will do a couple of dozen just fine, and that’s just the scale I need for this experiment.

The high level end to end looks something like this:

Everything starts out on the Arduinos.  The Raspberry Pi polls the Arduinos for data, transforms that data into JSON, and then posts that to a Node.js app running on Heroku. The Node.js app augments that data with current weather conditions and then writes it to a PostgreSQL table.  I have it set to do this about every 5 minutes.

The basic Node.js app is built using Express, but I haven’t spent any significant time optimizing it.

I’m using ipfilter to ensure that only someone from a whitelisted IP address can make a request on the app.  If you aren’t calling from some recognized IP, you get an “unauthorized” response.

Sequelize coordinates the database activities.  For some reason I didn’t look at ORM when I wrote that tweet a couple of months ago. Sequelize isn’t perfect, but it’s super fast as a getting started tool, and it’s way better than anything I might write.  You start by declaring a model, which it then automatically creates for you in the actual database.  It doesn’t do database modifications as far as I can tell — there’s some kind of a separate module for that which I haven’t investigated yet.

var Reading = sequelize.define('Reading', {
	deviceName: Sequelize.STRING,
	rangeStartDate: Sequelize.DATE,
	rangeEndDate: Sequelize.DATE,
	maxBrightness: Sequelize.INTEGER,
	minBrightness: Sequelize.INTEGER,
	maxTemperature: Sequelize.DECIMAL(4,2),
	minTemperature: Sequelize.DECIMAL(4,2),
	weatherSummary : Sequelize.STRING,
	weatherTemperature: Sequelize.DECIMAL(4,2),
	weatherWindSpeed: Sequelize.DECIMAL(4,2),
	weatherWindBearing: Sequelize.INTEGER
});

Lastly, the Forecast module connects me to Forecast.io to get the current outside weather conditions.  I thought this would be useful information to keep in mind as I start to look at the data. Now, I could have chosen to work with current-condition-then data in a number of ways, including querying for it analysis time (whenever that turns out to be), but the incremental cost of including it with each reading was so trivial I decided to do it.

And voila — for your enjoyment — the data.

OK, so far so good.

Next: Accessing the data in Salesforce using Heroku Connect External Objects OData