Using WordPress as a CRUD Database, API Included

Ethernet cordBased on this HN discussion, which I discussed a while back, I looked at how to set up WP as a CRUD database accessible via API.

It wasn’t hard. Steps:

  1. Install WordPress (I used ec2 and the Cloudformation sample template)
  2. Install the following plugins
  3. I also installed the following optional plugins
  4. I created a custom post type of ‘todo’ and added a couple of custom fields.
  5. I was able to get the todos by going to these URLs (apparently you can have the API live at wp-json, but that required some rejiggering of url permalinks).
    • http://host/wordpress/?rest_route=/wp/v2/todo/8
    • http://host/wordpress/?rest_route=/wp/v2/todos

Here’s an example of the output:

{
  "id": 8,
  "date": "2018-03-05T02:38:26",
  "date_gmt": "2018-03-05T02:38:26",
  "guid": {
    "rendered": "http://host/wordpress/?post_type=todo&p=8"
  },
  "modified": "2018-03-05T02:40:01",
  "modified_gmt": "2018-03-05T02:40:01",
  "slug": "auto-draft",
  "status": "publish",
  "type": "todo",
  "link": "http://host/wordpress/todo/auto-draft/",
  "title": {
    "rendered": "Buy Milk"
  },
  "template": "",
  "acf": {
    "": false,
    "due_date": "20180308",
    "description": "please buy milk.",
    "who_owns_it": {
      "ID": "1",
      "user_firstname": "",
      "user_lastname": "",
      "nickname": "mooreds",
      "user_nicename": "mooreds",
      "display_name": "mooreds",
      "user_email": "...",
      "user_url": "",
      "user_registered": "2018-03-05 02:21:36",
      "user_description": "",
      "user_avatar": "..."
    },
    "done": false
  },
  "_links": {
    "self": [
      {
        "href": "http://host/wordpress/wp-json/wp/v2/todo/8"
      }
    ],
    "collection": [
      {
        "href": "http://host/wordpress/wp-json/wp/v2/todo"
      }
    ],
    "about": [
      {
        "href": "http://host/wordpress/wp-json/wp/v2/types/todo"
      }
    ],
    "wp:attachment": [
      {
        "href": "http://host/wordpress/wp-json/wp/v2/media?parent=8"
      }
    ],
    "curies": [
      {
        "name": "wp",
        "href": "https://api.w.org/{rel}",
        "templated": true
      }
    ]
  }
}

The custom post fields are all under the ACF key, and you can see that there was an expansion of the who_owns_it field. If you are going to do this, make sure have the the normal title tag be part of the custom post, otherwise the WP UX for editing the custom posts won’t be much use.

Not perfectly restful, but a super simple way to set up an API that non technical folks can use to create, update or delete records and that you can consume in other systems.


Easily visualizing location data with Google Fusion Tables

Hands with map on itSometimes you have a list of locations in a Google spreadsheet and want to visualize where the locations using a map. Google Fusion tables lets you do just that, for free, with no technical expertise needed.

To do so, you need to create a spreadsheet. I created a spreadsheet of birthplaces of the Presidents of the USA. Here’s the spreadsheet. You want to make sure you have column headers and that your location information is all in one column. You can see that I concatenated birth city and state into one column, because you can only map one column (unless you have lat/lng, in which case you can use those two columns). You also can’t concatenate any columns when the data has been pulled into fusion tables.

Then, create a new google fusion table (under the ‘more’ menu). Choose a spreadsheet as your data source and then past in the spreadsheet link.

Your location column may or may not have been given a data type of location. If not, use the ‘edit’ menu then ‘change columns’ to convert it to a location type.

Add a map using the red plus sign and select your column as the location. Wait for your column to be geocoded (if you have lat/lng you shouldn’t need to do this).

And there you are. Here’s the map I generated. You can see that if you click on the marker you will get information about each president, which is a nice bonus feature. You can also share this within your organization or with the wider world and do additional filtering.

Fusion tables is great when you have the structured data and just need a simple map representation.

Caveats:

  • Once your data is in fusion tables, you are extremely limited on what you can do with it (see the concatenation above, for example). Do whatever data massaging you need in the spreadsheet. This also means that you probably want the spreadsheet to be your source of record.
  • There’s no way to update your data. So when the next president enters office, I will either have to create a whole new fusion table or delete all the rows and re-import.
  • Fusion tables seems to no longer be under active development. At least I haven’t seen many feature changes over the past couple of years. It is out of beta. I think it’s fine to build adhoc tooling on top of this service, but if I were looking for the core of my business I’d avoid this.

Passwordsafe, redux

Guy looking menacingly at a computerI have written before about passwordsafe (about a decade ago!). I just wanted to reiterate how having all your authentication information in one or more password safe files is such a lifesaver for transitions. You can easily change all the passwords at one go (using randomly generated strong passwords, of course), though you may have to deploy applications or change environment variables to do this. You also can easily see all the systems and external services that help make your application “go”.

I’ve heard about good things about tools like Lastpass and whatnot, but passwordsafe is open source, can live on a thumb drive, is multi platform and has a small feature set. It’s also extremely easy to set up and get going, especially if you store it in version control. And for a small team moving fast, the perfect solution is the enemy of the good.

Do something–even a shared google doc will get you some of the benefits.


Useful Tool: Intercom

Intercom In WallIntercom has been extremely helpful in allowing targeted messages to help users gain knowledge of The Food Corridor application. (Thanks for the recommendation, OTL!) What I’ve found most useful about Intercom is that it allows non technical users to build rulesets to target specific messages. This helps you help your users uncover new functionality, but only at the moment when the functionality is useful.

For example, if someone has signed up but not created a booking in TFC, we can send them a message a week after they sign up with a helpful link. This message can be via email or an in-app message, and if they respond to the in-app message, it notifies customer support just as if a chat was started any other way.

As a developer, all I had to do to get this data (which lives in our database) up to Intercom was to craft a javascript object. I added a method in the application_controller and have it cached for a while, because we ended up sending dozens of attributes up, and they are slow changing. From then on, the message targeting is done entirely within Intercom, where the non technical user can build rules based on this custom data plus any data that Intercom collects by default (last login, etc).

If you do want to target messages specific web pages (only pop up a message on page XXX, but not page YYY) you need an understanding of regular expressions. Depending on how comfortable your non technical users are and how complicated your site is, a developer may need to write the first regular expression and then have the other users extend it.

I’m skipping over other pieces of Intercom, including a knowledge base and in app synchronous chat. I think those are valuable as well, but the real win for me was allowing non technical users to control in app messaging with minimal software development investment.


Metabase: An Easy Query Builder For Your RDBMS

If you have a relational database as the primary datastore for your application, and you want to easily allow non technical folks to build reports against data in that database, I highly recommend evaluating metabase. This open source query builder is dead simple to install (takes about one minute on a free heroku dyno). You can create individual users, and they all get access to an easy to use interface so they can look at data in your tables. All the nomenclature is non technical, nary a select, group by or from clause to be seen. It’s written in java, but you don’t have to know java to run it.

We haven’t used this for long, so I can’t talk much about the warts, but I was referred to this by someone else who had great success in using it in their organization. The only downside that I’ve seen so far is that joins are not supported, so you end up having to create views if your database is normalized. More on that decision.


Speed up development by catching your mail locally

Have you ever been developing some kind of application that sends email? You need to test how the email looks, so you have to have access to an external SMTP server and you have to configure your application to use that. You can definitely set up sendgrid or another MTA to send email from your local computer and then use a real email address as your target. However, then to develop this portion of the application you need to be online.

Another option that I’ve found is the Mailcatcher gem. This is a small ruby program that you can easily configure as your SMTP endpoint. Then when your development environment sends mail, mailcatcher catches it. Then you can visit a URL on your local computer and view received emails. As soon as mailcatcher shuts down, the emails are lost, however.

Even though this is a ruby gem, you can use the app with different languages–as long as it you can configure the application to point to an SMTP server, you’re good (in the readme, there are examples for Django and PHP).

One note about it being a gem. Don’t put it in your Gemfile if you are building a rails app, because of possible conflicts. This means that if you manage your ruby environments via rvm you’ll need to re-install mailcatcher every time you change your ruby version.

Bonus: mailcatcher even has an API so you can use it in your integration test environment to verify that certain actions in your application caused certain emails to be sent.


How Trello Wowed Me By Handling An Edge Case

We are using Trello for our product development planning at The Food Corridor. Previously we were using Pivotal Tracker, which I chose, but a new team came on to help us and they were more comfortable with Trello. I may do a compare and contrast of these tools in the future, but for now I wanted to celebrate the beauty of a well designed piece of software.

I don’t mean how Trello looks, though it certainly looks pretty. I mean how they handle UX edge cases. I ran into one the other day, and it blew my mind that Trello acted as I had hoped.

Here’s the situation. We use the Trello numeric card ID along with this git hook:

#!/bin/sh

# from http://stackoverflow.com/a/16061192/203619

if story_id=`git branch |grep '*'|sed 's/.*-//'`
then
    echo "[#$story_id]" >> "$1"
fi

To help tie commits to stories. If someone is working on a story with the id 123, they work on a feature branch called add-new-feature-123. When committing, they may write a message like: “Updated the message to the end user when they save”, and this hook will automatically add “[#123]” onto that commit message.

When someone is looking at the code six months or two years from now, they will be able to look up that story and get context about why the message was changed beyond what was in the commit log.

We were cleaning up old releases in Trello and had moved all the released stories to another board. However, I noticed that the cards were renumbered when they were moved to that board. Whoops! That meant that the commit messages wouldn’t be useful in looking up the cards. I had discussions with the product manager and we decided to keep all future releases on the same board to maintain the numbers–we’d just archive them (it’s worth noting that when you search for 123 and the card is archived, the search won’t return the card unless you add the is:archived search operator to your query).

However, I was ready to write off the cards that had been moved to the other board. What were the chances that if I moved the cards back to the original board, the card numbers would be maintained? I gave it a try just to see.

Trello did the right thing! The cards, when moved back to the original board, assumed their original numeric ID.

I am very impressed, as I imagine there are a very small portion of Trello users who care about this behavior. As someone who doesn’t really care about design but does care about user experience, that is an example of attention to detail that I wanted to call out and praise.


Smashing: A Quick Dashboard Solution

I’m putting together a business metrics dashboard for The Food Corridor (what is old is new again, I remember a project at XOR, my first job out of school, that was all about creating a dashboard). I could have just thrown together some rails views, but I looked around and saw Smashing, which is a fork of Dashing, a dashboard project that came out of shopify.

Smashing is a sinatra app and is fairly simple to set up. It looks gorgeous, a lot better than anything I could hack together. I could install it on a free heroku dyno. Even though it will take a bit of time to spin up, it is now running for free. Smashing has nice MVC separation–you have dashboards which assemble widgets, and then jobs which push data to widgets on a schedule. Sending data looks something like this: send_event('val', { current: current }) where val is referenced in the widget.

You can create more than one dashboard (I did only one). They aren’t customizable by non developers, but once the widgets are written, they can be created by someone with a modicum of experience editing HTML.

Some tips:

  • Smashing stores its state in a file. If you are running on heroku, the filesystem is ephemeral. You have two options. You can store the state in an external data store like redis (patch mentioned here, I didn’t try it). Or you can rely on the systems you are polling for metrics to maintain the state. That’s the path I took.
  • The number widget has the ability to display percentage changed since last updated: send_event('val', { current: current, last: last }). Make sure that val is an integer–I sent a string like “100000” and that was treated as a zero for purposes of calculation.
  • If you are accessing any external systems, make sure you inject any secrets via environment variables.  For local development, I used dotenv.
  • You’ll want some kind of authentication system.
  • The widgets that come with Smashing aren’t complicated, but neither are they documented, so prepare to spend some time understanding what they expect.
  • I grouped jobs, which gather the data, by data source.  You can send multiple events per job, and I thought that made it clearer.  Connections to APIs or databases only needed to happen once as well.
  • The business metrics which I was displaying really only change on a monthly basis.  So I wanted to run the data gathering immediately, then in a week or two weeks.  Because of the ephemeral state, I expect the second run will never happy, but wanted to be prepared for it.  I did so by creating a function and calling it once on job load and then in the scheduler.

Here’s pseudo code for the job that pulls data from stripe:


Stripe.api_key = ENV['STRIPE_SECRET_KEY']

def stripe
  # pull data from stripe...
  send_event('stripeval', { current: current })
end

stripe

SCHEDULER.interval '1w' do
  stripe
end

Smashing is no full on technical metrics solution (like Scout or New Relic), but can be useful for displaying limited data in a beautiful format with a minimum of developmetn effort. If you’re looking for a dead simple dashboard, Smashing will work for you.


Interview with a early stage SaaS founder

I had the chance to talk with my good friend and former colleague Corey Snipes about his SaaS project. He recently launched Meeting Star, a lightweight SaaS tool to help coordinate tech meetups. This interview has been lightly edited for clarity.

——-

Why did you come up with this product?

It began as a desire to fill my own need, for a lightweight and inexpensive place to manage small local tech events. It seemed like a fairly straightforward set of features, which wouldn’t take long to build and release as a product. (Don’t they always?) I was aware of several other tech meetup organizers who were looking for an alternative to meetup.com (often due to price, sometimes due to dislike of the feature set or UI). I did quite a bit of research last fall and didn’t find any suitable alternatives so I built it.

What do you hope to achieve with this app?

I have a few parallel interests here. I want a tool that’s useful to me as a meetup organizer. I want to leverage my experience building, marketing, and operating other software products — both my own, and for customers I’ve had over the years. I want to add a business line in my portfolio that provides value and makes people happy, while also being financially sustainable. I also run a separate, conference-related application and I anticipate some complementary lift between the two.

How much research did you do before plunging in and writing it?

Quite a bit. You can always do more, of course. I poked around online and found several lists, articles, and discussion threads about alternative platforms. I followed conversations of other meetup organizers discussing the relative merits of various methods. I made a list and tried seven or eight of what seemed like the top contender products. I was looking for a place to run my meetups, though, not specifically looking at competition. But in the end, everything was either trying to be a full-featured community management piece, or was such a terribly crafted alternative that I felt there were exactly zero real usable options for what I wanted to do.

Who is the product aimed at?

This particular [app] was born of my own needs, and I tend toward tech and entrepreneurship meetups. It’s well-suited to tech and software meetups. Those are the people in my network. Those are the meetups I attend and organize, and those are the users whose needs I can most easily identify and meet. Since it’s a reasonably lightweight application, it’s actually well-suited to many different kinds of groups, but software/tech/biz meetups are my focus.

What would make you consider this product a success?

Right now, success is getting ten paying, happy customers and getting things dialed in to their needs. I subscribe to Patrick McKenzie’s wisdom that the first ten customers are critical for turning your idea into something people want to use. And also, for proving it’s not a fluke. If you can get to ten, you can get to a hundred. And if you can get to a hundred, you can get to a thousand. For me, long-term success is a useful, sustainable product that has revenue to turn into improvements, runs smoothly, and maybe also puts a little money in my kids’ college fund.

——-

You can find out more about Corey, including why he’s moving to Cleveland, at his website.


Feature branch development

I remember when I had lunch with a friend, back when I was using SVN and he was using git.  He said “it’ll change your life”.  I had read about darcs years ago and had read Joel’s post about distributed version control systems.  I was still like “meh.  SVN does what I need it to do–tag releases, keep track of changes, and I can branch if I need to”.

Boy, was I wrong.

I’ve been using git since around 2014, and it’s great.  I don’t remember where I heard it, but someone said “branching in SVN is easy, it’s the merging that is difficult”.  Git takes the pain out of merging (mostly, of course you can still hose yourself pretty well).  I have to also reference this classic XKCD comic whenever I talk about git.

Regardless, one of the things I’m loving about working with git is that if you always use feature branches, use story numbers in your branch name (like story-update-123), and you set up your prepare-commit-msg script, you can track back every change to a story.  Here’s my prepare-commit-msg script:

#!/bin/sh

# from http://stackoverflow.com/a/16061192/203619

if story_id=`git branch |grep '*'|sed 's/.*-//'`
then
    echo "[#$story_id]" >> "$1"
fi

Another nice feature of the branch handling is that you can roll forward and backward branches. I use bitbucket, so I use the GUI revert command, which creates a nice revert PR. (I then immediately revert the revert PR so that I can apply the intended changes in the future by merging the second PR.) This makes it possible to push a feature to staging, realize it isn’t fully baked, and revert it so you can get out another feature release. Perhaps you could do this with SVN or another centralized VCS, but I was never comfortable enough with branches to do it.

All in all, git has been life changing as a developer. Thanks Russ, you were right!



© Moore Consulting, 2003-2017 +