User defined views

What are user defined views?

User defined views (UDVs) are a way to create or modify views (e.g. bframe.prices or bframe.invoices) in the bframe schema. While creating a brand new independent view, like bframe.my_random_pricing, might not be useful combining this with the modification of default views can be impactful. For example, bframe.line_items is simple a union of two types of line item views.

-- bframe.line_items sql
SELECT *
FROM bframe.fixed_line_items
UNION
SELECT *
FROM bframe.event_line_items

This abstraction is intended so that new types of line items appended. This can be utilized by modifying bframe.line_items to include an additional union.

-- bframe.line_items sql (modified)
SELECT *
FROM bframe.fixed_line_items
UNION
SELECT *
FROM bframe.event_line_items
UNION
SELECT *
FROM bframe.my_random_pricing

Now when consumers of the view employ bframe.line_items it will include the additional bframe.my_random_pricing. Downstream applications like business intelligence and billing will automatically include the new pricing since they already use line items. This is a basic example, but it exhibits how UDVs can enable bframe to be extended and customized.

Why are user defined views important?

bframe library intends to be an extensibile set of functions that enable the bframe framework. UDVs are the primary feature that enables this flexibility. If a user understands bframe, then just about anything can be modified to operationalize a unique business model.

A goal of bframe is to handle most business modeling use cases but it is recognized that full coverage is impossible. UDVs exist to give users a path to resolution in the scenario that bframe misses a specific requirement. Without UDVs, users would have to settle for less than a perfect fit.

How to create user defined views

Creating a UDV is straight forward. A name and SQL template are the requirements for creation. To demonstrate let’s create a new type of pricing and enable it for Wikipedia.

Wikipedia wants to create a free tier for customers on their standard plan. They believe that folks who try out the product will definitely use more in the future if they are a good fit. The leadership team has decided they can give up roughly $100 in free usage a month without breaking the bank.

Creating the new pricing

The first step to creating a new type of pricing is creating a product type. We will be modifying an existing product but the steps would be similar for generating one from scratch. The product type determines how metering and rating is applied. The product type will be called 'TIERED'.

Query

INSERT INTO src.products (org_id, env_id, branch_id, id, name, ptype, filters, agg_property, event_name, version) values (2, 2, 1, 2, 'Creates (w/ Free tier)', 'TIERED', '{}', 'agg_value', 'create', 1);

Result

Count
1

Now that the product exists we will modify the existing list prices to use it on the Standard pricebook. Additionally for this specific example we also need additional information to understand where a tier begins and ends. The pricing_metadata field can be utilized for storage in this case.

Since we have two tiers we will need to create an additional list price to hold the free tier.

Query

INSERT INTO src.list_prices (org_id, env_id, branch_id, id, product_uid, pricebook_uid, price, pricing_metadata, version) values (2, 2, 1, 2, 2, 1, '0.00', '{"tier_start": 0, "tier_end": 2000}', 1);
INSERT INTO src.list_prices (org_id, env_id, branch_id, id, product_uid, pricebook_uid, price, pricing_metadata) values (2, 2, 1, 100, 2, 1, '0.05', '{"tier_start": 2000}');

Result

Count
1

Create the tiered pricing views

Now that products and prices have been established, instantiating the UDVs is the last step to support the free tier.

Tiered rated events

Rating each event with tiered pricing requires knowledge of the prior events. For example, if we want to know if the next event should be free or charged an understanding of the past events is necessary. Due to this, creating a new view called bframe.tiered_rated_events is a more straightforward solution.

User defined view

SELECT
    rs.*
        EXCLUDE (running_agg, tier_start, tier_end)
        REPLACE(
            rsq.quantity AS quantity,
            rsq.quantity * rs.price::DECIMAL AS amount
        )
FROM (
    SELECT 
        _BF_ORG_ID as org_id,
        _BF_ENV_ID as env_id,
        _BF_BRANCH_ID as branch_id,
        COALESCE(me.transaction_id, 'EMPTY_RATED_EVENT') as transaction_id,
        COALESCE(me.customer_id, ps.customer_id) as customer_id,
        COALESCE(me.properties, '{}') as properties,
        COALESCE(me.metered_at, ps.started_at) as metered_at,
        COALESCE(me.received_at, ps.started_at) as received_at,
        ps.list_price_uid,
        ps.contract_price_uid,
        ps.product_uid,
        ps.product_type,
        ps.price,
        COALESCE(me.quantity, 0) as quantity,
        ps.price::DECIMAL * COALESCE(me.quantity, 0) AS amount,
        ps.contract_id,
        ps.started_at,
        ps.ended_at,
        ps.effective_at,
        ps.ineffective_at,
        (CASE
            WHEN _BF_RATING_AS_OF_DT >= ps.ended_at
            THEN 'FINALIZED'
            ELSE 'DRAFT'
        END) AS status,
        ps.invoice_delivery,
        CAST(ps.pricing_metadata->'$.tier_start' AS DECIMAL) as tier_start,
        CAST(ps.pricing_metadata->'$.tier_end' AS DECIMAL) as tier_end,
        SUM(me.quantity) OVER (
            PARTITION BY 
                me.customer_id,
                me.product_uid,
                ps.contract_id,
                ps.list_price_uid,
                ps.contract_price_uid,
                ps.started_at,
                ps.ended_at
            ORDER BY me.metered_at ASC
            ROWS BETWEEN
                UNBOUNDED PRECEDING
                AND CURRENT ROW
        ) AS running_agg
    FROM (
        SELECT *
        FROM bframe.matched_events
        WHERE product_type = 'TIERED'
    ) AS me
    RIGHT JOIN bframe.price_spans AS ps
        ON me.product_uid = ps.product_uid
        AND me.metered_at >= ps.effective_at 
        AND me.metered_at < date_trunc('day', ps.ineffective_at)
        AND me.customer_id = ps.customer_id
) AS rs,
LATERAL (
    SELECT (CASE
        WHEN ((rs.running_agg - rs.quantity) < rs.tier_end AND rs.running_agg >= rs.tier_end)
        THEN COALESCE(rs.tier_end - (rs.running_agg - rs.quantity), 0)
        WHEN ((rs.running_agg - rs.quantity) < rs.tier_start AND rs.running_agg >= rs.tier_start)
        THEN COALESCE(rs.running_agg, 0) - tier_start
        WHEN (rs.running_agg >= rs.tier_start AND rs.running_agg < rs.tier_end)
        THEN rs.quantity
        WHEN (rs.running_agg >= rs.tier_start AND rs.tier_end IS NULL)
        THEN rs.quantity
        ELSE NULL
    END) as quantity
) AS rsq
WHERE
    rs.running_agg >= rs.tier_start
    AND (
        rs.running_agg < rs.tier_end
        OR ((rs.running_agg - rs.quantity) < rs.tier_end AND rs.running_agg >= rs.tier_end)
        OR rs.tier_end IS NULL
    )

Sample query

SELECT
    transaction_id,
    customer_id,
    contract_id,
    properties,
    metered_at,
    received_at,
    product_uid,
    list_price_uid,
    product_type,
    price,
    quantity,
    amount,
    started_at,
    ended_at,
    status,
    invoice_delivery
FROM bframe.tiered_rated_events
WHERE customer_id = 'Xenophon'
ORDER BY received_at, list_price_uid
LIMIT 5;

Sample result

transaction_id	customer_id	contract_id	properties	metered_at	received_at	product_uid	list_price_uid	product_type	price	quantity	amount	started_at	ended_at	status	invoice_delivery
1715260620.0Diözesanmuseum_Lapidarium_-_Türsturz.jpg	Xenophon	Xenophon_contract	{“name”:”create”,”agg_value”:336.0,”category”:”Collection_highlights_of_Koninklijke_Bibliotheek”}	2024-05-09 14:00:00	2024-05-09 14:00:00	2	2	TIERED	0.00	336.0	0.0	2024-05-01	2024-06-01	DRAFT	ARREARS
1715363107.0Bamberger_Dom_Kreuzgang_-_Schlussstein_Wappen.jpg	Xenophon	Xenophon_contract	{“name”:”create”,”agg_value”:776.0,”category”:”UNESCO”}	2024-05-10 18:00:00	2024-05-10 18:00:00	2	2	TIERED	0.00	776.0	0.0	2024-05-01	2024-06-01	DRAFT	ARREARS
1715435108.0Diözesanmuseum_Bamberg_-_Gunthertuch_4.jpg	Xenophon	Xenophon_contract	{“name”:”create”,”agg_value”:2600.0,”category”:”UNESCO”}	2024-05-11 14:00:00	2024-05-11 14:00:00	2	2	TIERED	0.00	888.0	0.0	2024-05-01	2024-06-01	DRAFT	ARREARS
1715435108.0Diözesanmuseum_Bamberg_-_Gunthertuch_4.jpg	Xenophon	Xenophon_contract	{“name”:”create”,”agg_value”:2600.0,”category”:”UNESCO”}	2024-05-11 14:00:00	2024-05-11 14:00:00	2	100	TIERED	0.05	1712.0	85.6	2024-05-01	2024-06-01	DRAFT	ARREARS

Tiered line items

Once we have rated events, they need to be aggregated to create line items. This can be accomplished by updating the existing bframe.event_line_items view or making a new one called bframe.tiered_event_line_items. We will opt for the latter.

User defined view

SELECT
    _BF_ORG_ID as org_id,
    _BF_ENV_ID as env_id,
    _BF_BRANCH_ID as branch_id,
    CURRENT_TIMESTAMP as created_at,
    re.list_price_uid,
    re.contract_price_uid,
    re.product_uid,
    re.contract_id,
    re.invoice_delivery,
    re.started_at,
    re.ended_at,
    re.effective_at,
    re.ineffective_at,
    (CASE
        WHEN _BF_RATING_AS_OF_DT >= re.ended_at
        THEN 'FINALIZED'
        ELSE 'DRAFT' 
    END) AS status,
    SUM(re.quantity) as quantity,
    round(SUM(CAST(COALESCE(re.amount, 0) AS decimal)), 2) AS amount
FROM bframe.tiered_rated_events AS re
GROUP BY ALL

Sample query

SELECT *
FROM bframe.tiered_event_line_items
WHERE contract_id = 'Xenophon_contract'
ORDER BY started_at, list_price_uid
LIMIT 5;

Sample result

org_id	env_id	branch_id	created_at	list_price_uid	contract_price_uid	product_uid	contract_id	invoice_delivery	started_at	ended_at	effective_at	ineffective_at	status	quantity	amount
2	2	1	2025-01-16 12:21:39.376000-08:00	2		2	Xenophon_contract	ARREARS	2024-05-01	2024-06-01	2024-05-01	2024-06-01	DRAFT	2000.0	0.0
2	2	1	2025-01-16 12:21:39.376000-08:00	100		2	Xenophon_contract	ARREARS	2024-05-01	2024-06-01	2024-05-01	2024-06-01	DRAFT	1712.0	85.6

Modify line items

To fully integrate the tiered pricing into bframe the UDVs must be included in the top level default views. In this case that means appending bframe.tiered_line_items along with other line item types within bframe.line_items.

User defined view

SELECT *
FROM bframe.fixed_line_items
UNION
SELECT *
FROM bframe.event_line_items
UNION
SELECT *
FROM bframe.tiered_event_line_items

Sample query

SELECT *
FROM bframe.line_items
WHERE contract_id = 'Xenophon_contract'
ORDER BY started_at, list_price_uid
LIMIT 5;

Sample result

org_id	env_id	branch_id	created_at	list_price_uid	contract_price_uid	product_uid	contract_id	invoice_delivery	started_at	ended_at	effective_at	ineffective_at	status	quantity	amount
2	2	1	2025-01-16 12:21:42.400000-08:00	1		1	Xenophon_contract	ARREARS	2024-05-01	2024-06-01	2024-05-01	2024-06-01	DRAFT	58.0	5.8
2	2	1	2025-01-16 12:21:42.400000-08:00	2		2	Xenophon_contract	ARREARS	2024-05-01	2024-06-01	2024-05-01	2024-06-01	DRAFT	2000.0	0.0
2	2	1	2025-01-16 12:21:42.400000-08:00	100		2	Xenophon_contract	ARREARS	2024-05-01	2024-06-01	2024-05-01	2024-06-01	DRAFT	1712.0	85.6

Exploring the change

The free tier is live and the impact can be seen across customers. Let’s see what it looks like for one of our larger customers.

Result

products.id	products.name	line_items.quantity	line_items.amount	invoices.total	invoices.invoice_delivery	invoices.status	invoices.contract_id	invoices.started_at	invoices.ended_at
1	Updates	1162.0	116.2	23403.8	ARREARS	DRAFT	Pikiwikisrael_contract	2024-05-01	2024-06-01
2	Creates (w/ Free tier)	2000.0	0.0	23403.8	ARREARS	DRAFT	Pikiwikisrael_contract	2024-05-01	2024-06-01
2	Creates (w/ Free tier)	465752.0	23287.6	23403.8	ARREARS	DRAFT	Pikiwikisrael_contract	2024-05-01	2024-06-01

Query

SELECT
    prod.id as 'products.id',
    prod.name as 'products.name',
    li.quantity as 'line_items.quantity',
    li.amount as 'line_items.amount',
    i.total as 'invoices.total',
    i.invoice_delivery as 'invoices.invoice_delivery',
    i.status as 'invoices.status',
    i.contract_id as 'invoices.contract_id',
    i.started_at as 'invoices.started_at',
    i.ended_at as 'invoices.ended_at'
FROM bframe.invoices AS i
JOIN bframe.line_items AS li
    ON i.contract_id = li.contract_id
    AND i.ended_at = li.ended_at
    AND i.started_at = li.started_at
    AND i.invoice_delivery = li.invoice_delivery
JOIN bframe.products as prod
    ON li.product_uid = prod.id
WHERE i.contract_id = 'Pikiwikisrael_contract'
ORDER BY li.started_at, li.list_price_uid;

As expected only $100 of savings were accomplished which is only a small part of their total cost. To ensure Wikipedia isn’t losing out too much we can do a query to see the impact to the bottom line.

Result

list_price_uid	product_uid	actual_amt	no_free_tier_amt
2	2	0.0	242564.0
100	2	1523350.8	1523350.8

Query

SELECT 
    li.list_price_uid,
    li.product_uid,
    ROUND(SUM(li.amount), 2) AS actual_amt,
    ROUND(SUM(li.quantity * 0.05), 2) AS no_free_tier_amt
FROM bframe.line_items AS li
WHERE li.product_uid = 2
GROUP BY 1, 2
ORDER BY li.list_price_uid

The relative difference between a free tier and without one is about 14%. This outcome is much larger than the exective team expected when they first proposed the change. Due to this result, the change isn’t rolled out to production and a free tier is no longer in the strategy for Wikipedia.