Data model for storing the community spend per price building block

[vikasrohit]

Description

In new smart scope forms, for project cost estimation we are using building blocks as primitive elements which stores the price/time configuration per valid combination of answers to the various questions. We are storing those building blocks, at the time of project creation, in project_estimations table. This table has single record per building block identified for the project created, so there may be more than one record per project in this table. Schema of this table is as follows:

CREATE TABLE project_estimations
(
    id bigint NOT NULL,
    "buildingBlockKey" character varying(255) NOT NULL,
    conditions character varying(512) NOT NULL,
    price double precision NOT NULL,
    quantity integer NOT NULL,
    "minTime" integer NOT NULL,
    "maxTime" integer NOT NULL,
    metadata json NOT NULL DEFAULT '{}'::json,
    "projectId" bigint NOT NULL,
    "deletedAt" timestamp with time zone,
    "createdAt" timestamp with time zone,
    "updatedAt" timestamp with time zone,
    "deletedBy" bigint,
    "createdBy" integer NOT NULL,
    "updatedBy" integer NOT NULL,
    CONSTRAINT project_estimations_pkey PRIMARY KEY (id)
);

The price per block is the amount topcoder charges to the customer and it includes community budget, topcoder services budget and topcoder fee. We need to store this breakup per block in database so that we can control the access to the price breakup. In ideal scenario:

1. A customer would be able to see only total price per block
2. A Copilot would be able to see only the community budget per block
3. A topcoder employee (CA) would be able to see full price breakup

First approach

We have to add following more field to support the price breakup:

    communityBudget double precision NULL,
    servicesBudget double precision NULL,
    fee double precision NULL,
    markupUsed bigint NULL, // reference to `project_markup` table

We have to limit the visibility of these fields based on user role as follows:

• Customer should not be able to view any of these new fields
• Copilot should be able to view ONLY communityBudget from these new fields
• CA should be able to view all of these fields
  We have to make sure that we don't leak any of the fields to unintended users and I think for that we need to use middleware which is applied after the route processing and that middleware should ensure that we follow the rules as specified above.

This table has a column markupUsed which is a foreign key to another table project_markup having following schema:

CREATE TABLE project_settings
(
    id bigint NOT NULL,
    key varchar(255), // e.g. 'markup'
    value varchar(255),
    valueType enum(int, long, double, string),
    "projectId" bigint NOT NULL,
    metadata json NOT NULL DEFAULT '{}'::json,
    "readRoles": array(varchar),
    "writeRoles": array(varchar),
    "deletedAt" timestamp with time zone,
    "createdAt" timestamp with time zone,
    "updatedAt" timestamp with time zone,
    "deletedBy" bigint,
    "createdBy" integer NOT NULL,
    "updatedBy" integer NOT NULL,
    CONSTRAINT project_markup_pkey PRIMARY KEY (id)
);

User with roles mentioned in readRoles (e.g. Customers and Copilots for markup setting) would be able to read the value of that particular setting.
User with roles mentioned in writeRoles ( e.g., CAs) would be able to update that particular setting.

We need to profile follow endpoints for this table:
GET /projects/{id}/settings - returns settings which contains roles of the caller user
PATCH /projects/{id}/settings/{id} - update settings which contains roles of the caller user
For now, we don't need POST and DELETE endpoints.

Second Approach

Another option is to have the same capability by filtering at row level instead of column level by introducing a new table for storing the price breakup as follows

Proposed schema for such table project_estimations_items

CREATE TABLE project_estimations_items
(
    id bigint NOT NULL,
    "projectEstimationId" bigint NOT NULL,
    price double precision NOT NULL,
    type enum(community, topcoder_services, fee) NOT NULL,
    markupUsed bigint NULL, // reference to `project_markup` table
    "projectId" bigint NOT NULL,
    metadata json NOT NULL DEFAULT '{}'::json,
    "deletedAt" timestamp with time zone,
    "createdAt" timestamp with time zone,
    "updatedAt" timestamp with time zone,
    "deletedBy" bigint,
    "createdBy" integer NOT NULL,
    "updatedBy" integer NOT NULL,
    CONSTRAINT project_estimations_items_pkey PRIMARY KEY (id)
);

Customer would never get any data from this table via any endpoint.
CAs would get all records from this table (GET /projects/{id}/estimations/{id}/items)
Copilot would be able to see only community (it might be configurable via env variables) type records from this table. (GET /projects/{id}/estimations/{id}/items)

This table has a column markupUsed which is a foreign key to another table project_markup having schema as described in first approach

@maxceem Let me know your thoughts. I was kind of inclined towards second approach initially at the start of writing the spec, however the by the time I ended up this spec, I am inclined towards first approach as it seems it is less prone to accidental leaking of the information.

[maxceem]

1. I tend to the second approach. In general, it feels that it would be easier to add new types of budget in the future if needed. Regarding the data leaking, it also feels that the second approach can give us more explicit control of the data.

   • for example instead of introducing new endpoints like GET /projects/{id}/estimations/{id}/items we can return the estimation items together with project_estimations by joining them. We can introduce some class method to the ProjectEstimation model like findWithItem which would have a required argument userRoles or even the whole authUser. And findWithItem would return the records based on the user roles. The disadvantage would be that the model level would know about the users and permission details. But advantage would be that we would be required to always specify user permissions to get estimation details data
   • another security advantage I see in the second approach is that all the data would be stored in a separate table, so if in the future we use data from the table project_estimations in some other requests or we join the table in some request we wouldn't include this data accidentally.
   • as we still return estimation items inside the project_estimations objects, we can also use middleware after processing the request and implement the second filter of data if we think this makes sense (this would be duplicate logic of checking permissions)

2. I'm not sure what would be the usage of a new project_settings table. Maybe you have an example of some particular setting key/value which could be stored there.

3. If we use the second approach, should we keep markupUsed inside project_estimations instead of adding it to project_estimations_items? So we would check settings based on the main record in project_estimations and use that setting for all items. Or do we need to have different setting per items? Should we then add markupUsedto bothproject_estimationsandproject_estimations_items`?

[vikasrohit]

Thanks for quick feedback @maxceem. Good observations for both the approaches as well.

1. it would be easier to add new types of budget in the future if needed.

Yes, that was one of my purpose when I drafted that second approach. However, later I realized that it won't be very rare (except till we release it to production) to get this price break up changed and have more types of budgets. And in fact if we do, it is not difficult to add/drop columns as well, provided that having the different budget types as columns reduces our maintenance of database (less tables and joins to take care of)

The main purpose for me to be inclined towards 1st approach is that I want to make it as less error prone as we can in terms of some one accidentally, leaking the data. Problem with having a class method for the model is that a participant in our future challenges (or we ourselves) might use the model directly without using that class method and that might leak the data.

Purpose of having separate endpoint (in 2nd approach) for estimation items was to avoid calling that endpoint at all for non privileged users and have better access control before calling the route itself. I am trying to avoid joining the estimation items with the parent model so that we have to make explicit calls to get the estimation items which gives us better security control and logging details.

I did liked the idea of having a post processing middleware, in 2nd approach as well, though. In fact, we can avoid parsing the permissions in data model's class method now and just let the post processing middleware do the cleanup based on the permissions. It is easy the detect changes in the future for any change in the post processing middleware rather than the changes where we accidentally query the model directly, instead of the class method, with join and leak the data.

2. project_settings is a generic table to store any setting at project level. For now, markup would be the only one. And the purpose of markup setting is to determine the price break up for a project's estimations. For some particular type of projects, we decide the different types of budget on adhoc basis while for others we have a % markup (ranging from 1-100) which is used to break the total price estimation of the project into different types of budget. So, my intention to have the markupUsed field in project_estimation_items table is that after UI calculates the price break up based on markup setting of the project, we would store that value of markup as markupUsed. And the reason, it can't be stored at project_estimations level is that project_estimations does not change based on markup setting, rather we apply the markup on the project_estimations objects of the project and create new project_estimation_items every time (which is not the case in 1st approach and we loose that information) an admin updates the markup of the project.

[maxceem]

To sum up:

• we will use the first approach
• we will control permission using post-middleware for GET /projects/{id}/estimations endpoint
• we WON'T create additional endpoint GET /projects/{id}/estimations/{id}/items as we will use one model with post-middleware.

[vikasrohit]

@maxceem I think what I meant in my last long statements, was that we can have separate table for estimation items as suggested in second approach but still control their access via post middleware. And if we don't need the specific endpoints for accessing these estimation items, we can skip implementing them for now. This gives us the ability of keeping the information when markup is changed for a project as I describe previously:

And the reason, it can't be stored at project_estimations level is that project_estimations does not change based on markup setting, rather we apply the markup on the project_estimations objects of the project and create new project_estimation_items every time (which is not the case in 1st approach and we loose that information) an admin updates the markup of the project.

[maxceem]

Thanks, @vikasrohit I would like to clarify a few moments:

1. And if we don't need the specific endpoints for accessing these estimation items, we can skip implementing them for now.

   Does it mean that we would include project_estimation_items inside the response of /projects/{id}/estimations and /projects/{id}/estimations/{id}?

2. One note regarding the post-middleware I think that we would add it only to the specific endpoints like /projects/{id}/estimations and /projects/{id}/estimations/{id} as middleware has to know about the format of the response. This means, if in any other endpoint we would get data from project_estimations or project_estimations_items tables it would be returned.

3. Regarding project_settings. I guess the pair of key and projectId should be unique in this table. This would lead to a few things if I got the idea right:

   As we have several kinds of fees, and which of them can be calculated using different rules (I guess), so we would have several settings to control each of them:

   - id: 1, key: `communityMarkup`
   - id: 2, key: `servicesMarkup`
   - id: 3, key: `feeMarkup`
   

   And after each of the project_estimations_items records will refer to a particular setting by id inside markupUsed.

   Or would we define these rules inside metadata filed of the single record with key markup in project_settings while all the project_estimations_items would refer this single record.

4. project_estimations does not change based on markup setting, rather we apply the markup on the project_estimations objects of the project and create new project_estimation_items every time (which is not the case in 1st approach and we loose that information) an admin updates the markup of the project.

   I'm not sure how it's gonna work. Does it mean that if we change project_settings value with makrup key all the existent project_estimations_items whould be soft-deleted. While new project_estimations_items should be created based on the new value of settings in project_settings?

[vikasrohit]

Does it mean that we would include project_estimation_items inside the response of /projects/{id}/estimations and /projects/{id}/estimations/{id}?

Yes, but with our strict post middleware which would remove those records for non authorized users. We would still create separate class method to fetch estimation items for a estimation or a project and would not like to include them by default in all fetch queries of the estimations.

This means, if in any other endpoint we would get data from project_estimations or project_estimations_items tables it would be returned.

As I said in my comment above, we won't return project_estimations_items by default for every query made against project_estimations, instead we would require user to call the class method to fetch the estimations items. That would prevent such incidents where project_estimations is returned and accidentally it revealed the project_estimations_items as well.
Also, I think the only other points where we might return project_estimations are GET /projects and GET /projects/{id} and we can apply either the same post middleware or slightly modified version of it to check any possibility of presence of project_estimations_items object as any field of the response object. I know it would be kind of brute force but I don't any other way to secure the access as far as we can.

Regarding project_settings.

I think we have possibilities of both ways if required. As of now, we would have separate markup settings and each project_estimations_items would point to its own markup. Honestly, I am not 100% sure as of now about this because there are many ways to determine the price break ups. When I started thinking about this, I was thinking that we can do break up based on only one markup, but later realized that we can not break the price into 3 pieces with only one markup. So, I guess, keeping separate markup settings would work in that case.
Another use case(which in fact might be the most frequent one) still left from my side to think on, is to have the possibility of just a random price breakup per building block. We can not keep that in the templates as templates are accessible to any one who want to create a project with us. Do you have any idea how to handle such cases @maxceem?

Does it mean that if we change project_settings value with makrup key all the existent project_estimations_items whould be soft-deleted. While new project_estimations_items should be created based on the new value of settings in project_settings?

Yes

[maxceem]

Another use case(which in fact might be the most frequent one) still left from my side to think on, is to have the possibility of just a random price breakup per building block. We can not keep that in the templates as templates are accessible to any one who want to create a project with us. Do you have any idea how to handle such cases @maxceem?

@vikasrohit I'm not sure what do you mean by possibility of just a random price breakup per building block.

[maxceem]

Another use case(which in fact might be the most frequent one) still left from my side to think on, is to have the possibility of just a random price breakup per building block. We can not keep that in the templates as templates are accessible to any one who want to create a project with us. Do you have any idea how to handle such cases @maxceem?

I think I've got what you mean here. Currently, each building block has only one total price. While we would like to define the price breakdown per building block like:

buildingBlocks: {
  "BLOCK_KEY": {
    conditions: '...',
    price: 1000, // total
    priceItems: {
      community: 700,
      topcoderService: 200,
      fee: 100,
    }
  }
}

And when we create a project we would store the total sum into project_settings and breakdown by summing building blocks into project_settings_items.

But the thing, that we cannot store such a price breakdown inside templates which can be accessed by anyone because pricing breakdown is private information.

I can think about two approaches here.

1. ProjectTemplateExtended model

Create ProjectTemplateExtended or ProjectTemplatePrivate model which would hold some private data. In particular, it may also have scope field with the part of the template which holds private information. So it can store only the private part:

buildingBlocks: {
  "BLOCK_KEY": {
    priceItems: {
      community: 700,
      topcoderService: 200,
      fee: 100,
    }
  }
}

While the ProjectTemplate will hold the public part:

buildingBlocks: {
  "BLOCK_KEY": {
    conditions: '...',
    price: 1000, // total
}

• pros: the extended part would be never returned by API to the non-admin users
• cons: we would have to duplicate keys of buildingBlocks when entering data, which is prone to errors
• cons: as we move the pricing to PriceConfig in the new approach, we would have to also create PriceConfigExtended model and support two cases for extended model, when we store pricing in ProjectTeamplte, and when we store it in PriceConfig.

2. Private keys in ProjectTemplate

We may think about some kind of prefixes in the Project Template which would mark private data, like __PRIVATE__:

 buildingBlocks: {
  "BLOCK_KEY": {
    conditions: '...',
    price: 1000, // total
    __PRIVATE__priceItems: {
      community: 700,
      topcoderService: 200,
      fee: 100,
    }
  }
}

When returning ProjectTemplates to non-admin users all such fields should be removed from JSON.

• pros: we keep the data in one place and it's easy to edit
• pros: we don't have to make wast changes in the code to support it
• cons: we would have to filter project templates when we return them. And as /metadata endpoint return all the project templates and JSONs are big, filtering such fields may have performance pitfalls.

3. Separate model for BuldingBlocks

The last approach I can see here is to introduce a new model to store BuildingBlocks. And store these building blocks in a separate model. To store breakdown we may have columns inside BuildingBlocks which we wouldn't return to non-admin, for example: privateConfig (JSON).

• pros: probably we can reuse BuildingBlocks across templates
• cons: as templates are evolving very fast we may end up constantly changing this model

[vikasrohit]

And when we create a project we would store the total sum into project_settings and breakdown by summing building blocks into project_settings_items.

I think you meant project_estimations and project_estimation_items, right?

And I think now you got the pain point which I was trying to explain with random price break up. And I liked the 3rd approach as with first two approaches, we are still relying on JSON fields which I think is risky to control visibility as per user roles.

Now coming to the 3rd approach, I think essentially project_estimations is a BuildingBlock that I tried to capture as is in database for each project. However, the point is that each record in project_estimations is an instantiation of BuildingBlock coming from a project template so it has be created from somewhere (right now it is coming from the template itself). So, I think we have to create that new model building_blocks which would serve as template (via project template) for creating project_estimations records for a project. This approach is totally fine with me, the only thing that I was trying to avoid with some magic trick using project_settings table for this purpose is to avoid introducing a new table building_blocks to maintain, if we could have achieved the same effect via project_settings only. However, it seem bit tricky and confusing to achieve the same results via project_settings table, I would like to propose following approach:

1. Introduce new model building_blocks and couple it with a project_template or price_config (my best guess is to link it with price_config). The records in this table would act as template for creating project_estimations rows on project creation. And as you said, control the visibility of the columns, via the post middleware.
2. As the number of records in this new table would be very finite, we might not need any REST endpoint for managing these and we can maintain this metadata via SQL scripts only.
3. The big problem with introduction of this new model is to keep sync between the config specified in buildingBlocks key of the priceConfig metadata entity. We need to have some way to ensure that both (records from bulding_blocks table for a particular price config record and configuration in buildingBlocks key of the priceConfig record are exactly the same). I think the best way is to remove them from the config JSON field of the priceConfig entity and instead directly link them from the building_blocks table. Efforts with this approach would be in metadata UI.
4. On creation of a project, identity the building_blocks from either priceConfig linked with the project template or if there is not priceConfig linked with the template, directly look into building_blocks table by building block key (coming from the project template's building blocks). Once, we have identified the building_blocks, create project_estimations and project_estimation_items by using the price break up specified in building_blocks records.
5. On edit of a project's price markup settings (may be via API only), we have to delete the existing project_estimation_items records and create new ones based on the markups specified by the settings.

[maxceem]

@vikasrohit thanks for a detailed description.

BuildingBlocks schema

I propose the next schema for bulding_blocks model:

    "key" string NOT NULL,
    "config" json NOT NULL DEFAULT '{}'::json,
    "privateConfig" json NOT NULL DEFAULT '{}'::json,
    "deletedAt" timestamp with time zone,
    "createdAt" timestamp with time zone,
    "updatedAt" timestamp with time zone,
    "deletedBy" bigint,
    "createdBy" integer NOT NULL,
    "updatedBy" integer NOT NULL,
    CONSTRAINT bulding_blocks_pkey PRIMARY KEY (key)

• we use config to keep the data free for any future changes
• as we use key for primary we couldn't reuse the key after soft-deleting.

On edit price markup

On edit of a project's price markup settings (may be via API only), we have to delete the existing project_estimation_items records and create new ones based on the markups specified by the settings.

As per my understanding, we don't need project_settings model anymore to store price markup and it would be stored in bulding_blocks model. The config and privateConfig of bulding_blocks may look like this:

{
   "config": {
          "conditions": "( HAS_DEV_DELIVERABLE && (ONLY_ONE_OS_MOBILE || ONLY_ONE_OS_DESKTOP || ONLY_ONE_OS_PROGRESSIVE) && SCREENS_COUNT_SMALL && CA_NEEDED)",
          "price": 5075,         
          "minTime": 35,
          "maxTime": 35,
          "metadata": {
            "deliverable": "dev-qa"
          }
    },
    "privateConfig": {
         "community": 3075,
         "topcoder_service": 1000,
         "fee": 1000,
    } 
},

• If we change inside privateConfig values like: community, topcoder_service, fee we should soft-delete all project_estimation_items records and recreate them using udated building blocks. We may define the list of properties which would cause estimation recalculation.
• we also should do soft-delete for project_estimations and recreate it with an updated summary data

Permissions

I would like to conclude both approaches we have here for the final decision.

Permissions with post-middleware

At the moment to work post-middleware should work the next way:

• for BuildingBlocks: inside all fields privateConfig of all objects remove fields with keys community, topcoder_service, fee based on user permissions
• for ProjectEstimationItems: if find an object with type one of community, topcoder_services, fee such objects should be removed based on user permissions

Pros:

• easy to add to any or all endpoints
• developer doesn't have to write any code for this method to work

Cons:

• as it works implicitly, the developer may be not aware of it and rename fields in some way or create derivative data
• we can only make post-middleware to remove restricted values not too keep allowed, as in general, we wouldn't know the full list of allowed fields. This way if we add new fields, or rename existent, post-middleware would miss them
• it could be prone to errors and filtering other necessary fields, which have the same name as not allowed. For example, we should filter objects with type=community, while we have milestones with type=community-work and type=community-review. It's not the same but very close and in the future, we may add some objects with the same values.

Improve:

• I guess to improve the reliability of post-middleware we can name project_estimations_items type as project_estimations_items.privateType. This way it would be easier to filter such record and we would less likely to filter some data accidently.

Permissions with hooks

I would again suggest considering the methods of checking permissions and restricting returned data inside model class methods, like findAll.

We can use hooks beforeFind/afterFind and inside these hooks do the next things:

• beforeFind: check that we passed to the standard find or findAll method options with the user permissions, otherwise, throw an exception
• afterFind: filter returning object properties based on the user permissions
• permissions should work in the way: filter everything out, include fields which are explicitly permitted. This way if in the future we add some new fields and forget about permissions, they by default should not be returned.

Pros:

• mostly in the app we use these methods to retrieve data, we would obligate the developer to directly pass user permissions or throw an error
• when we do it inside the model we for sure know what is the object structure to properly filter results
• To warn future developers to be careful when joining this table we can put a big warning message inside the model definition file and inside SQL migration script which creates this model in DB. Most likely developer would look at one of these files to understand the model structure before joining.

Cons:

• a developer still can miss the warnings and join table somewhere

[maxceem]

One more thing, @vikasrohit

Migration script

We should write a migration script which would extract building blocks from projectTemplate.scope or priceConfig.config JSON, create records in building_blocks and updates JSON to refer the building_blocks by keys.

So:

{
    "buildingBlocks": {
        "SMALL_DEV_ONE_OS_NO_CA": {
          "conditions": "( HAS_DEV_DELIVERABLE && (ONLY_ONE_OS_MOBILE || ONLY_ONE_OS_DESKTOP || ONLY_ONE_OS_PROGRESSIVE) && SCREENS_COUNT_SMALL && CA_NOT_NEEDED)",
          "price": 3149,
          "minTime": 35,
          "maxTime": 35,
          "metadata": {
            "deliverable": "dev-qa"
          }
        },
        "MEDIUM_DEV_ONE_OS_NO_CA": {
          "conditions": "( HAS_DEV_DELIVERABLE && (ONLY_ONE_OS_MOBILE || ONLY_ONE_OS_DESKTOP || ONLY_ONE_OS_PROGRESSIVE) && SCREENS_COUNT_MEDIUM && CA_NOT_NEEDED)",
          "price": 4113,
          "minTime": 40,
          "maxTime": 40,
          "metadata": {
            "deliverable": "dev-qa"
          }
        }
   }
} 

Would become:

{
    "buildingBlocks": {
        "SMALL_DEV_ONE_OS_NO_CA": {},
        "MEDIUM_DEV_ONE_OS_NO_CA": {}
    }
} 

I guess we can keep them as objects here. In case we want to define some additional values per template.

If you think this is not necessary we can convert buildingBlocks to an array:

{
    "buildingBlocks": [
        "SMALL_DEV_ONE_OS_NO_CA",
        "MEDIUM_DEV_ONE_OS_NO_CA"
    ]
} 

[vikasrohit]

Thanks @maxceem for summarizing the requirements and solutions.

For building block schema, I would like to have the price break up columns as standard fields instead of json as we might need to perform some aggregations on those fields while creating reports and I don't think these fields would change very frequently. I would like to run migration SQL scripts to add/remove columns instead of silently adding/removing these columns because these are important data fields.
However, as I was writing this, I realized that building_blocks are kind of templates only and we might never need to do aggregations on these, so I guess we should be good to go with the schema suggested.

As per my understanding, we don't need project_settings model anymore to store price markup and it would be stored in bulding_blocks model. The config and privateConfig of bulding_blocks may look like this:

We still need markup setting at the project level because markup is a % of one of the price and it is defined at project level not at the template level. I mean for the same project template, we might need to set different markup for different customers. As of today, my understanding says that we would have price break up for projects' estimate either by markup or by adhoc price breakup mentioned in the building_blocks themselves. We might not need to apply both ways of breaking the price for a project estimation.

I am good with Permissions with hooks approach as well as after reading your concerns, I think it might be more troubles with a post middleware.

a developer still can miss the warnings and join table somewhere

Can we add custom lint rule which can prevent a developer from accessing the Sequelize model's core methods outside the model itself?

For migration script for building blocks, I think we need to keep the buildingBlocks as object as it is right now because there might be templates which have building blocks which are in the template but not in the database (building_blocks table). These would be the cases where we want to do price break up of the project estimate by markup not by the privateConfig of the building_blocks table.
This migration script reminded me, how are you planning to fetch the building_blocks data from database and merge it with the template's buildingBlocks?

[maxceem]

Can we add custom lint rule which can prevent a developer from accessing the Sequelize model's core methods outside the model itself?

I'll think about it. I guess we cannot block developer for 100%, because of the nature of JS. But probably we can cover the main cases.

Can we add custom lint rule which can prevent a developer from accessing the Sequelize model's core methods outside the model itself?

I was thinking to return buildingBlocks separately in the /metadata endpoints together with other kinds of templates and let the client side to merge with data in the project template.

If we support additional data in projectTempates for buildingBlocks like this:

{
    "buildingBlocks": {
        "SMALL_DEV_ONE_OS_NO_CA": {
           "price": 3000
        },
        "MEDIUM_DEV_ONE_OS_NO_CA": {}
    }
} 

Then the logic would be:

• for each key in buildingBlocks in projectTemplate find buildingBlocks returned by the /metadata ednpoint
• if such block found merge objects in projectTemplate and in buildingBlocks.config. Give priority to the data in projectTemplate
• NOTE: /metadata would return only the public part of building blocks, the private part would be only used inside project service to calculate estimations.

project_settings

So if we use project_settings to calculate price breakdown, the logic would be like this.

• We would store a setting record per each price type in project_settings table.
• The total price would be calculated as usual using buildingBlocks.
• After that we would retrieve project settings for the project for the predefined list of project_settins.key like: community, topcoder_services, fee and so on.
• Using values of settings we would calculate price breakdown and create according records in project_estimations_items.

We also can agree on what possible values of project_settins we should support for prices. I guess we can support two types of values:

• "2341234.123" - absolute value
• "23.123%" - percentage
• if we see the percentage sign, we treat this setting as a percentage, so the project_estimations_items would be calculated as a percentage from the total
• if the value doesn't have a percentage sign, we treat this value as absolute, so we just write this value as it is to `project_estimations_items while keeping the total as it is? right?

I don't see how we can use valueType at the moment:

valueType enum(int, long, double, string),

Probably we can skip this column at the moment until we have more use cases to understand what valueType should we support.

[vikasrohit]

@maxceem your approach for combining the building_blocks data with template's buildingBlocks looks good to me. And I think more or less approach using project_settings is also fine with minor changes (I would come to them later in this reply). However, what I am thinking is to combine both of these approaches together instead of using only one of them i.e.

1. If we are able to find a record in building_blocks table for a key mentioned in buildingBlocks config of the template, and that record (building_blocks) has a valid privateConfig, we should use that to create project_estimations_items. For now, privateConfig would be used only to provide adhoc values of different types of price line items (community, fees and topcoder_services).
2. If we are not able locate privateConfig for a building block (either there was no record in building_blocks table or the record in building_blocks has null value in privateConfig), we would look for project_settings approach to compute the price break up for project_estimations_items. Also note that, even if the template's buildingBlocks object has configuration for for the building blocks which exists in building_blocks table, we would still use database record for privateConfig while we are using template's values for all non privateConfig things.

Now some minor suggestions/clarifications to the project_settings approach:

1. project_settings is supposed to be a generic place where we can store any type of setting for a project e.g. we might want to rename Scope tab to something else for particular project. In this case we have to store that as setting and that is why we would need valueType to determine the type of the setting (string in this case)
2. if you agree with 1, we can use valueType to store percentage as value for the case when we want to store markup in percentage and store the actual number in the value field.

I know we still might be able to skip valueType as most of the times, we know the type of value by setting e.g. if we store SCOPE_TAB_TEXT as setting, programmatically we would use this value on at dedicated place and that place knows that the value is string. However, in case of price breakup calculation, we can leverage this valueType as suggested my 2nd point above, to determine the logic.