Behind the Scenes: HTML5 Offline SQL [Interview]

Posted by Kuty Shalev | Dec 4, '12

Clevertech, a leading provider of custom development solutions, is here today to discuss the complexity of a problem that AirTrain, one of their clients, was having, and the solution they offered using the HTML 5 and SQL offline capabilities. Kuty Shalev (CEO of Clevertech), Joshua Schechter (Business Analyst and Project Manager) and Sergii Gamaiunov (Lead technical developer for the project) will tell us about it.


Q: Tell us about your client AirTrain and what was special about them?

Joshua S.: AirTrain works with Port Authority and run a train between JFK airport and the main train terminal for New York. They have millions of customers every month and we are going to focus on the discrepancies and track maintenance and the application we built to help them manage that.

Kuty S.: AirTrain is a New York Port Authority client that has the responsibility for maintaining this track. For example, if somebody had complained or an engineer saw some problems, like overgrown vegetation or some cracks, they send someone to figure out what the problem is and then they fix it.

Q: Can you tell us about the complexity of their challenge?

Kuty S.: Basically, the way they are handling that entire process today is sending someone out there, using a phone camera to take pictures, writing it down on a piece of paper and then going back to the office. So they were looking for an application that was going to give them the same capabilities of a piece of paper and a pen. They were looking for a way to improve that entire process. Basically, that was the problem we were asked to help with.

Joshua S.: Another problem they were also having was that once the maintenance guy was out there and wrote the report on the particular problem, then the supervisor was having a hard time locating it.

Q: What was the solution you offered?

Kuty S.: They needed a product that would allow them to go out on the track, make pictures and videos and write down information in a ticket tracking system, but that system needed to be able to work offline and then synchronize back to their systems. The main issue was to make sure that even if there was no Internet access, which many places along the track do not have, the system was still going to work.

Joshua S.: When developing the system, we added the capability for them to be able to draw on the pictures – for example drawing an arrow to where they see the issue. That was something that helped them improve their process and made it more efficient as well.

Q: What resources were involved in the creation of this product and what difficulties did you experience during the implementation process?

Sergii G.: The task was to create a ticket system that holds all of the client's discrepancies and ensures their entire process of work could be accomplished offline. Initially, we made it, as we usually do, in PHP and HTML. When the whole database was done we had the entire process and logic ready and it worked, but we still needed to make it available offline.

Going through our options, we realized that our best solution was to use HTML 5 and take advantage of its storage capabilities. That way we were going to be able to hold the entire database on the device and it was going to be available even without Internet access. All the employees needed to do is run the application first time in their office, so it could install all the data set that was managed through the main application, and then they could just go to the track itself and use the application offline. After their work on the track is done, they just have to go to any place that has access to their network and hit a button – the collected data will upload to their server.

Kuty S.: To visualize it, picture a maintenance guy who is picking up a tablet and physically going to the track and examining what he has seen.

Joshua S.: It's basically a hand-held tablet that they can drop. We made the system very touch-friendly – it's almost like using an ATM at your local bank where the buttons are big and everything is clear. Also something that doesn't really get talked about a lot, were the colors we used. These guys are going to be outside in the sun a lot, so we needed to make sure that the colors were correct and they can visually see things and be able to easily use the system.

Kuty S.: Yes, I actually remember when we were talking about this, our initial drop-down menu was more user-friendly if it was for a computer.

Joshua S.: It was blue with white and when you were outside it reflected so badly that we couldn't use it.

Kuty S.: And then we had to create a whole new drop-down menu that also used different colors and used up the entire screen, as opposed to being a standard menu, it had to pop out so you could easily touch the maintenance issue that you had.

Sergii G.: Another difficulty we experienced during implementation was the limitation in storage capacity. HTML 5 has local storage that is used to store data on offline machines, but there were limits on how many bytes of information could be held on one machine.

After some research it turned out that we can actually support a full-on database, like SQL database on HTML drivers.

Q: What was the storage capacity for HTML 5?

Sergii G.: It has a limit of about 2,5 to 5 MB, based on the browser used – developers' browsers tend to have a bit more capability. It's great to store little things and if there are interruptions of some sort, it remembers things like the last items you went through and stuff like that. But it couldn't hold the amount of information our client needed. The client had a whole map of tracks with zones and sections and information on different equipment that could be used.

Joshua S.: It was relationships and multiple tables.

Kuty S.: I think that's a key point. Anytime you are building an application that has some serious enterprise value, you need a database that's going to back it up. When you are offline you don't have the ability to hit a database that's on a server somewhere. You need to have a copy of that database sitting locally and SQL is the right way to do that.

Sergii G.: Yes, we used SQLite. That way the client can run the application, then go offline, get into the field, collect data and when he is back online, he can upload the new data back to the server.

Q: What about synchronization? What did you use to be able to let the client collect data locally and then bring it back to the server?

Sergii G.: Well, that was just HTTP. We also used Backbone as a JavaScript framework to hold those screens and forums. We used a custom built extension to ensure Backbone models could be stored locally using SQLite.

Kuty S.: Actually this is the first time we had the combination of Backbone, HTML 5 and using the SQLite database.

Q: How has the client been using your product so far?

Joshua S.: The client just completed testing. They basically took a team of their top maintenance guys to go out with hand-held tablets to the sides and tested the system. They went to different track sections, maintenance areas and tried different maintenance issues. They took pictures, videos, drew on the pictures and so on. The system worked pretty flawlessly and all the maintenance personnel were pretty impressed at how simple it was to use it and they felt like everybody in the department could use the application efficiently without much training.

Kuty S.: That's a really key issue actually – when you are dealing with a computer-based user interface, you can't just jot some notes on the side. It has to be able to accept all the different inputs that are required in order to get it out.

Joshua S.: And to visualize that – they actually put 178 requests into the system already – and that is just for testing. You can see who did it, when they did it, what urgency level it is – if it's critical or low. They also have statuses of the discrepancies – is it pending, approved by supervisor or already dealt with?

Q: How long did it take to put together that entire system?

Kuty S.: This system started as MVP when we initially started it. We ended up delivering the whole thing in four 30-day implementations. So it was delivered in 120 days. Actually, they didn't believe when we first started that it would be possible to put a system like that together. And we did quite a number of things in a lean MVP way. In fact, this particular client is going to be showcased at the Lean conference in San Francisco. The client is going to present what it we did in order to create that type of rapid MVP and ensure it got out to the field and is actually in use.

Q: What are the major advantages of that product considering the type of work your client is engaged in?

Kuty S.: It has replaced their pen and paper system. From a management perspective this actually gives them a tool to deal with Air agencies. When the safety inspectors from New York state come in, looking to get reports on what has been happening with the system, the ability to move from pen-and-paper system, or even Word and Excel files, to a web based system that has auditing and reporting is a critical part of creating the safety levels that they are charged with over at AirTrain.

That's what we are about. It turns out that if you are focused on the really critical pieces, then you can actually get something out there that really works.

Topics: video, Clevertech, Clever Success, Clevertech Success, Client Success

Have an Idea?

Posts by Topic

see all

Subscribe to Email Updates

Our Slidedeck


Need a job?

View Job Board