Tasking detail

⁠

Master Tasking Page⁠

here

Data Structure

To configuration pricing structure of some types of fee for key account, reference info of key account to process.

Table: public.ka_pricing

Table: public.ka_pricing

⁠

Rules:

How to track: When a price is suggested to change, we should end the current pricing configuration by setting the to_date as of effective date, and create new record with from_date as of effective date. We can’t update an existing price.

pricing_formula is the key field to configure all pricings, based on same structure: {[fee_type_1]: {block: [{val, price, type}]}}.

fee_type_1: shipping_fee|cod_fee|fail_fee

each fee can divide into separated blocks such as [1-4,4-10,10-15,...], so we have field block with value as an array

each item in array block includes 3 fields: val (upper_limit, e.g 4,10,15...) → lower_limit would be the upper_limit of previous block or 0, price: VND value, type: FLAT|LINEAR.

For example, [{"val": 4, "price": 20, "type": "FLAT"},{"val": 10, "price": 5,"type": "LINEAR"},{"val":1000, "price": 2, "type": "LINEAR"}]} →

if value in [0,4] then fee=20 with type=FLAT

if value in [4,10] then fee= (value-4)*5 with type=LINEAR

if value in [10,1000] then fee=(value-10)*2 with type=LINEAR

total_fee will be sum_fee of 3 blocks;

Query template

Query Structure

If you take a look at

“Pricing Structure” page⁠

, you will see the author suggested a very good structure to generalize the fee calculation process, divide into 3 steps:

Get valid transactions: chargeable records can be based on order/stop/tracking number → OUTCOME: valid data rows

Get valid dimension: each customer want their fee based on a dimension: such as area_zone, delivery_distance, weight, ... → OUTCOME: dimension value (dim_val) for each data row

Get calculated fee: a fee can be calculated by 2 inputs: dimension_value (from step 2), and pricing_formula (from data table

ka_pricing⁠

) → OUTCOME: fee value for each data row

Query Template

⁠

Card Template⁠

: while the standard structure has 3 layers, the actual query template have 5 layers: layer 0 to get ka pricing configuration, and layer 4 (last layer) to consolidate and format output.

Data Processing

Logical Flow

With standard query structure defined in our query template, we now can clone it into actual query for each key account customer, or we can just use a single card one with different params for different customer, but we are sure that we will have a metabase card with input as “ka_pricing.id”.

We can build a daily invoice processing pipeline as below:

Scan in ka_pricing to find any record having current_date > from_date and day_of_month(current_date) = period.end_date

For each found ka_pricing record from step 1, run metabase card or compiled query (technical debt 1# below) to get fee records.

Release invoice

Technical Implementation

The above logical flow has 2 an interesting technical debts:

how to run fee calculation query effectively, in the fact that metabase only allows 30seconds running → solution: metabase has a very convenient api to return compiled query from raw sql with params value and even snippet, hence we can use the compiled query to run directly on postgresql database, or even better, we can choose proper pg instance to maximize performance.

how to reflect pricing_formula dynamically into each data row calculation → solution: there are 2 ways: we can build a function get_ka_fee(formula json, dim_val REAL) to use in calculation query, or we can inject sql-code into sql query at run time. You can test the function get_ka_fee by below query where you put in pricing config and dimension value

Note: with below config, we can build a sql code like this:

CASE

WHEN dim_val >= 0 and dim_val < 4 THEN 20*dim_val;

WHEN dim_val > 4 and dim_val <= 10 THEN 20 + 5*dim_val;

WHEN dim_val > 10 and dim_val <=1000 THEN 50 + 2*dim_val;

END;

As you can see, sql-code injection is so more straightforward and faster than a function, we can just generate the above code at runtime by nodejs/python and inject it into our query template.

You can find the source code of get_ka_fee as below: