Side project: A tool for preparing and calculating quotes with Neo4j
One part of my work is to inspect request for quotes (RfQs), gather customer requirements, estimate the effort and prepare a written offer. Most of our customers do not want to pay a separate bill for every sprint or user story but get a number or range for the total costs of the project. A big problem is the complexity of our projects. We do not only do software development but also do user interface design and application hosting.
In the last ten years I have tried and seen a lot of methods to calculate the estimated costs of software development projects: Function Point Analysis, COCOMO, Microsoft Excel, using modificators considering project management, risk and other factors, using tool combinations like JIRA or Confluence. In the end every method I have tried has disadvantages in one way or another.
This being said, one year ago I started to gather my own requirements for a cost calculation tool which fits in my workflow and should reduce my work:
- I need a user interface to capture tasks (user stories, subtasks) and required material (server, licenses, hosting costs, etc.). Each of these items can be valued (e.g. effort in hours, material cost in Euro etc.).
- Each of the items can be grouped together to form components. Each component can contain components itself.
- Changing the cost or effort of any item should result into a recalculation of parent components and the whole offer.
- I do not want to contaminate JIRA with estimations. JIRA is a project management tool and not a tool for estimating the cost of projects. Portfolio for JIRA does also not match these requirements.
- After I have structured the requirements and estimated their costs/efforts I want to export the offer in a distributable format (PDF, XML, …).
- After the customer has agreed to order components/tasks of our offer, I want to export these items from the calculation tool into JIRA.
The ideal workflow would be:
- The customer gets in touch with us, we receive the specifications.
- I break down the specifications into components and derive the tasks and the required material.
- Our team estimates the effort.
- Me and my superior are defining the quality levels of each tasks and the costs. The total costs are automatically calculated.
- We are finalizing the offer and sending the link of it to the customer.
- The customer selects the components/tasks (user stories) he wants to buy.
- The cost/pricing of selected components/tasks can be exported to the billing tool.
- Selected components/tasks can be exported to JIRA.
The whole project idea had been matured over months before I started with a first architectural draft. One big decision was choosing the DBMS to store the data. Instead of using PostgreSQL I chose Neo4j. One of the reasons for choosing Neo4j was the requirement that components can be interleaved in unlimited depth. Yes, I know Common Table Expressions (CTE) but hierarchical structures like quotes or Bill of Materials (BoMs) can be easily implemented with graph databases.
The current graph schema does not look exactly like this but it should give you an idea of the internal structure.
As you can see, containers can have an unlimited depth. For every node (task, item) multiple metrics can be defined. For performance reasons the parent’s container of a node contains the aggregated metrics of its children. The sample data can be found in the Neo4j console.
Until today I have implemented a first version of the repository and service layer in the backend. With Java’s Lambda expression I realized an internal DSL to calculate different metrics which can be based upon each other. For example I can specicy that if the amount or retail price of an item changes, both are multiplied and stored into the turnover metric for materials:
forMetrics(MetricType.AMOUNT, MetricType.RETAIL_PRICE_PER_UNIT) .when(ifAtLeastOneChanged()) .then( announce( multiply(MetricType.AMOUNT, MetricType.RETAIL_PRICE_PER_UNIT, MetricType.TURNOVER_MATERIAL) ) );
All metrics can be calculated and aggregated up to the root node. The service layer allows the concurrent editing of parts of the whole hierarchy (moving, adding or deleting subtrees).
There is still a lot to do but I am convinced that this project is cool and offers a real value for every person planning complex offers. I have no idea when (and if) I will ever finish the project. I am trying to build the tool as a SaaS platform so it should be relatively easy to make some money with it.
If you are interested in more details, drop me a line at me[at]schakko[dot]de.