Dev

Calculating in the Database and Revealing Tendencies

Aug 11, 2014

This last week, I focused primarily on the Cushion backend. In working locally on the app, I realized that it was becoming sluggish—especially on pages with a lot of content. I’ve been building this app with progress in mind, so I haven’t been as considerate as I should be. I query the database far too often and calculate the results in Ruby when it could all be done in the database. Because of this, performance issues are apparent.

For example, a single request to retrieve a list of projects uses a single query for the projects, but then three additional queries for each project to grab the sum of its invoices. With my current personal data, this resulted in nearly 150 queries just to pull down my projects! Not good.

I knew this would happen when I wrote the code because, at the time, I just needed it to work. I’m being a responsible dev and using test-driven development for the backend, so in order for the tests to pass, they just need the correct result. Now that I’m further along, however, I also need the code to perform well. Luckily, I can go back and fine-tune the code, knowing that my tests are in place to double-check that it still works.

After getting up to speed with PostgreSQL, I realized just how much I could do in the database rather than in Ruby—apparently, much more than I imagined. I started by focusing on those invoices sums—paid income, unpaid income, and relative estimated income. The first two are straightforward—just paid invoices and unpaid invoices—but the last one is trickier.

Every project has an estimated income, which is a ballpark amount that this project could potentially bring in. It’s not a guarantee that you’ll reach that amount, nor is it a guarantee that it will only bring in that amount. It’s just a figure to give you an idea. With this figure, however, we can do some interesting things, like seeing how close or far off you were.

If we take the estimated income for a project and subtract the sum of its invoices, we can see the actual income relative to the estimated amount. As you’re working on the project, this can be useful for seeing how much is potentially remaining, if it’s capped-off at that amount. Or, if you’re working hourly without a cap, you can see how much more you’ve made than originally anticipated. Hopefully, you fall in the latter end of this example.

2014-08-11-client-budget

I wrapped up projects and moved up the chain to clients. For clients, I have these sums of income as well, but on a wider scale, combining the sums of each client’s projects. This is useful in seeing which clients bring in the most (in case you need to spread out your income streams) and which clients owe you the most (revealing a red flag going forward).

We also have a lot of data to play with in regards to scheduling. Since every project has both estimated and actual start dates and end dates, I’ve been able reveal projects that start late or drag on too long. This is useful enough on the project level, but if we take a step back and group the projects by client, we can learn a lot about client tendencies.

2014-08-11-client-schedule

Now, instead of sums, we can calculate averages. The three main columns represent the average duration, delay and drag of clients. With them, we can expose a lot. If a client consistently feeds you only small scope projects, you can know with confidence whether you can fit them in an open slot in your schedule. On the cautionary side, we can use average delay and drag to question whether you should still work with this client or just account for the delay and drag when planning the next gig with them.

2014-08-11-tooltips

I also improved the schedule tooltip to provide written details of what you’re looking at rather than forcing you to do the math—you can now point to a specific segment of a bar and see what it represents. For clients and projects, these segments include the delay, drag, duration and estimated duration.

Under invoices, using the dates assigned to each (issued, due, and paid), the segments represent how early an invoice was paid, how late an invoice was paid (more realistic), how long an invoice took to get paid, and how long until an invoice is due.

With these new tooltips in place, I feel like I’m going in a really good direction—taking useful data and making it easily accessible. Being able to see the data in table form is easy, but I’d rather provide the ability to interact with data in context.

Share this on Twitter or Facebook

Archive

  1. Restructuring an Evolving App: Part 1
    Design
  2. My Typical Week as a Founder
    Story
  3. Building Components in a Sandbox
    Dev
  4. Reactive Time with Vue.js
    Dev
  5. Visualizing Daylight Saving Time
    Dev
  6. Recording Screencast GIFs
    Dev
  7. Writing a Job Listing
    Story
  8. Using Feature Flags to Run Betas
    Dev
  9. Our First Company Lunch
    Story
  10. How to embed Vue.js & Vuex inside an AngularJS app... wait what?
    Dev
  11. Funding Cushion
    Story
  12. Hiring a Team of Freelancers
    Story
  13. Taking a Real Break From Work
    Story
  14. Slack as a Notification Center
    Dev
  15. Document Your Features
    Story
  16. 300
    Story
  17. Vacations
    Design
  18. Offering Discounts
    Design
  19. Waves of Traffic
    Story
  20. Less Blogging, More Journaling
    Story
  21. Retention Through Useful Features
    Design
  22. The Onboarding Checklist
    Design
  23. Spreading the Word
    Story
  24. From Beta to Launch - The Subdomain
    Dev
  25. From Beta to Launch - Sign up
    Design
  26. From Beta to Launch - Messaging
    Design
  27. Launch
    Story
  28. Authenticating with 3rd Party Services
    Dev
  29. Intro to Integrations
    Design
  30. Inspiration vs Imitation
    Story
  31. The Emotional Rollercoaster
    Story
  32. Designing Project Blocks
    Design
  33. Everything in Increments
    Story
  34. Deleting Your Account
    Design
  35. Designing the Subscription Page
    Design
  36. Rewriting the Timeline
    Dev
  37. Restructuring the Individual Project Page
    Design
  38. Project Blocks
    Story
  39. Redesigning the Homepage
    Design
  40. Multiple Timelines
    Design
  41. Archiving and Estimate Differences
    Design
  42. Multiple Financial Goals
    Design
  43. Zooming in on the Timeline
    Design
  44. Currency
    Dev
  45. Preferences, Accounts, and a Typeface Change
    Design
  46. Sending Out the First Email
    Story
  47. Currency Inputs, Notifications, and Invoice Nets
    Design
  48. Dots and Lines
    Design
  49. Calculating in the Database and Revealing Tendencies
    Dev
  50. Improved Form UX
    Design
  51. Cushion is Online
    Story
  52. Schedule Timeline Patterns
    Design
  53. A Slimmer Schedule Timeline
    Design
  54. The Schedule Timeline
    Design
  55. Plugging in Real Data for the First Time
    Design
  56. Transitions and Project Lists
    Design
  57. Death to Modals
    Design
  58. The Individual Project Page
    Design
  59. Estimated Incomes and Talks with Other Freelancers
    Story
  60. Statuses to Lists and the Paid Beta
    Story
  61. The Timeline
    Story
  62. Invoice Terminology
    Dev
  63. Modal Forms
    Dev
  64. Wiring the Backend to the Frontend
    Dev
  65. Balancing Design and Dev
    Story
  66. Timecop, Monocle, and Vagrant
    Dev
  67. Going with Ruby and Sinatra
    Dev
  68. Ditching local-first and trying out Node.js
    Dev
  69. Switching to AngularJS
    Dev
  70. Building the Table with Vue.js
    Dev
  71. Clients, Projects, and Invoices
    Dev
  72. Introduction
    Story

Running Costs

Take a close look at the costs that go into running a web app and why we use specific services.

View the Costs

How It’s Made

Follow along with the journal for insight into the overall experience of building an app.

Read the Journal