OpsTech Technical Design
Share
Explore
KA Pricing

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
0
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 , 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 ) → OUTCOME: fee value for each data row
Query 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
select public.get_ka_fee('{"block": [{"val": 4, "price": 20, "type": "FLAT"},{"val": 10, "price": 5,"type": "LINEAR"},{"val":1000, "price": 2, "type": "LINEAR"}]}'::json, 11)
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:
CREATE OR REPLACE FUNCTION public.get_ka_fee(formula json, dim_val REAL)
RETURNS real
AS
\$total\$
DECLARE
block json;
fee real := 0;
last_val real := 0;
BEGIN
FOR block IN SELECT * FROM json_array_elements(formula->'block')
LOOP
IF (block->>'type'='FLAT') THEN
fee = fee + (block->>'price')::real;???
ELSE
fee = fee + (LEAST((block->>'val')::real, dim_val) - last_val) * (block->>'price')::real ;
END IF;
last_val = (block->>'val')::real;
IF (dim_val < last_val) THEN
EXIT;
END IF;
END LOOP;
RETURN fee;
END;
\$total\$
LANGUAGE plpgsql;
Sample Pricing Configuration
0
Search
Name
Sample
Giải thích
1
Shipping fee
{"block": [{"val": 4, "price": 23000, "type": "FLAT"},{"val": 1000, "price": 4500,"type": "LINEAR"}]}
Phí giao hàng, được tính theo từng block. Trong ví dụ bên cạnh, phí giao hàng dc tính theo giá trị x :
0<x<=4 tính block giá 23k
4<x<=1000 tính theo giá 4500 cho 1 km
Như vậy , nếu x = 3 thì fee = 23k, nếu x = 5 thì fee = 23k + 4.5k = 27.5k
2
Fail fee
{"price":0,"type":"FLAT","apply_for":"order"}
Phí giao thất bại
3
COD fee
{"price":0.008,"type":"LINEAR","milestone":2000000}
Phí COD
4
Return fee
{"price":0.8,"type":"LINEAR"}
Phí quay về điểm lấy hàng
5
Other settings
{"stop_fee": {"price":5000},
"insurance_fee": {"type":"PUBLIC"}}
Các loại phí ko có line riêng thì configure ở đạy
6
Metabase card
29018
Metabase card phải co 3 params: partner, start_date, end_date , và return invoice data của partner trong khoảng từ ngay đến ngày
There are no rows in this table