Introduction¶
In this article, we’ll explore bframe - what it is, how it can model businesses, generate and store invoices, and export those same invoices for billing, accounting, and any number of other applications.
By the end, you should have a firm grasp on bframe’s goals and how it may serve you and the needs of your business.
Tip
We’ll be leveraging the playground as an interface. Commands throughout each section can be run directly in the playground for a more hands-on approach.
What is bframe?¶
First and foremost, bframe is a framework.
The name itself is a portmanteau of “business framework”, as it provides a structured way of how to think about building a business system.
But bframe is also library, a collection of tools designed to help make it easier to put the principles of the framework into practice.
The framework¶
At a high level, bframe sees a business system as an ETL. There is source data that lives within a database. That source data must be extracted and then transformed into a clean set of business data. Once transformed, it is loaded into a destination where it can be used to administer the business (e.g. billing, customer transparency, tax).
The library¶
bframe utilizes an embedded database, DuckDB, to function as the transformation layer for a given source and destination. The interface is primarily implemented in SQL and is operated by executing queries through the bframe library.
Under the hood, the library is injecting SQL at run time based on the configuration and specific keywords in the query. bframe is intended to be a thin logic layer on top of the source and destination databases. Below is an example of a simple query made to bframe.processed_events, and how that ends up being interpreted by bframe.
SELECT *
FROM bframe.processed_events
WHERE customer_id = '10';
SELECT *
FROM (
SELECT e.* EXCLUDE(org_id, env_id, branch_id),
COALESCE(e.customer_id, c.durable_id) AS resolved_customer_id
FROM (
SELECT *
FROM (
SELECT *
FROM src.events
WHERE org_id = 1
AND env_id = 1
AND branch_id = 1
AND received_at <= '2025-12-31'
)
) as e
LEFT JOIN (
SELECT c.durable_id, cia.alias, c.effective_at, c.ineffective_at
FROM (
SELECT * EXCLUDE(rank)
FROM (
SELECT raw.*, ROW_NUMBER() OVER(
PARTITION BY raw.id
ORDER BY raw.version DESC
) AS rank
FROM (
SELECT *
FROM (
SELECT *
FROM src.customers
WHERE org_id = 1
AND env_id = 1
AND branch_id = 1
AND created_at <= '2025-12-31'
)
) AS raw
)
) AS c
JOIN (
SELECT
c.id,
ci.value AS alias
FROM (
SELECT * EXCLUDE(rank)
FROM (
SELECT raw.*, ROW_NUMBER() OVER(
PARTITION BY raw.id
ORDER BY raw.version DESC
) AS rank
FROM (
SELECT *
FROM (
SELECT *
FROM src.customers
WHERE org_id = 1
AND env_id = 1
AND branch_id = 1
AND created_at <= '2025-12-31'
)
) AS raw
)
) AS c, UNNEST(c.ingest_aliases) AS ci(value)
GROUP BY 1, 2
) AS cia ON cia.id = c.id
) AS c ON (
c.alias = e.customer_alias
AND e.metered_at >= c.effective_at
AND e.metered_at < c.ineffective_at
)
)
WHERE customer_id = '10';
DuckDB has many database connectors out of the box (Postgres, MySQL, S3, etc), these can be used to set the source and destination within bframe. The library initializes the source and destination databases as src and dest respectively. If these stores aren’t specified within the configuration, the library will initialize them in memory.
Modeling a business¶
bframe’s primary output is an invoice. The contents of this invoice are determined by the source data. bframe prescribes a schema for the source data to be used as an input for the transformation layer. The business modeling documentation describes these inputs at length, but the best way get a handle on this is through an example.
A small example¶
Small Co. is our fictional software agency that offers services to build and maintain web applications. They currently have a single client and expect to sell their services at a flat rate based on the project. An example invoice would look like this:
Invoice Date: January 1, 2024
| Description | Amount |
|---|---|
| Fee | $1,000.00 |
| Total | $1,000.00 |
Organization setup¶
To start, Small Co. needs an organization to represent them. This allows data within bframe to be recognized as belonging to Small Co. An environment and a branch are also required to complete the setup.
INSERT INTO src.organizations (id, name) values (3, 'Small Co.');
INSERT INTO src.environments (id, org_id, name) values (3, 3, 'PROD');
INSERT INTO src.branches (id, org_id, env_id, name) values (1, 3, 3, 'main');
Count |
|---|
1 |
Once Small Co’s organization, environment and branch are created, update the bfame client by using the “Configuration” settings found in the top right-hand corner of the playground.
Because we explicitly set Small Co.’s Organization ID as 3 when we set up the tenant in the first code example - we’ll need to configure the playground’s Organization ID and Environment ID to 3 to allow bframe to query the correct data.
To check if the tenant was selected correctly, run the following query to verify your results.
SELECT _BF_ORG_ID AS organization_id, _BF_ENV_ID AS environment_id
organization_id |
environment_id |
|---|---|
3 |
3 |
_BF_ORG_ID and _BF_ENV_ID are the configuration variables that were set within the bframe client. These and other variables can be used throughout any bframe query.
Customers¶
Each financial transaction within a business model occurs between a buyer and seller. A customer within bframe represents the buyer, and an organization represents the seller.
Small Co. has a single customer, “First customer”, which must be represented before an invoice can be created.
INSERT INTO src.customers (org_id, env_id, branch_id, id, durable_id, name) values (3, 3, 1, 1, 'first_customer', 'First customer');
Count |
|---|
1 |
Products¶
A business can not exist without things to sell. Defining products within bframe represents creating goods to be sold.
Small Co. sells services, which may vary, but they can be considered under the umbrella of a single product called 'Fee'. This is a 'FIXED' product which does not have any variable components.
INSERT INTO src.products (org_id, env_id, branch_id, id, name, ptype) values (3, 3, 1, 1, 'Fee', 'FIXED');
Count |
|---|
1 |
Provisioning¶
Transactions, to sell goods and services, are represented by contracts.
Since Small Co. prices their jobs on a per-project basis, creating contracts with one off pricing is a simple way to model the transaction. The row created within contract prices dictates that the fee is $1000, paid upfront, and is for services to be rendered over 12 months.
INSERT INTO src.contracts (org_id, env_id, branch_id, id, durable_id, customer_id, started_at, ended_at, effective_at) values (3, 3, 1, 1, 'first_customer_contract', 'first_customer', '2024-01-01', '2025-01-01', '2024-01-01');
INSERT INTO src.contract_prices (org_id, env_id, branch_id, id, product_uid, contract_uid, price, invoice_delivery, invoice_schedule) values (3, 3, 1, 1, 1, 1, '1000.00', 'ADVANCED', 12);
Count |
|---|
1 |
Generating and storing invoices¶
Creating invoices¶
With the inputs created, invoices and line items can be accessed by querying the bframe schema. These documents are created on-demand and are not stored.
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
ORDER BY i.started_at;
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 |
Fee |
1.0 |
1000.0 |
1000.0 |
ADVANCED |
FINALIZED |
first_customer_contract |
2024-01-01 |
2025-01-01 |
Pricing exploration¶
With just-in-time computation, bframe can enable a quick and safe feedback loop to explore business model changes. For example, if Small Co. was interested in billing every month instead of annually this could be explored using the branching feature.
Let’s create a branch called 'Test out monthly pricing' to update the contract.
INSERT INTO src.branches (id, org_id, env_id, name) values (2, 3, 3, 'Test out monthly pricing');
INSERT INTO src.contract_prices (org_id, env_id, branch_id, id, product_uid, contract_uid, price, invoice_delivery, invoice_schedule, version) values (3, 3, 2, 1, 1, 1, '100.00', 'ADVANCED', 1, 1);
Count |
|---|
1 |
Once the change is made, the Branch ID in the bframe configuration should be set to 2. This will allow for querying the new branch.
If the same query for invoices and line items is made on the exploratory branch, a different set of results will be returned.
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
ORDER BY i.started_at;
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 |
Fee |
1.0 |
100.0 |
100.0 |
ADVANCED |
FINALIZED |
first_customer_contract |
2024-01-01 |
2024-02-01 |
1 |
Fee |
1.0 |
100.0 |
100.0 |
ADVANCED |
FINALIZED |
first_customer_contract |
2024-02-01 |
2024-03-01 |
1 |
Fee |
1.0 |
100.0 |
100.0 |
ADVANCED |
FINALIZED |
first_customer_contract |
2024-03-01 |
2024-04-01 |
1 |
Fee |
1.0 |
100.0 |
100.0 |
ADVANCED |
FINALIZED |
first_customer_contract |
2024-04-01 |
2024-05-01 |
1 |
Fee |
1.0 |
100.0 |
100.0 |
ADVANCED |
FINALIZED |
first_customer_contract |
2024-05-01 |
2024-06-01 |
1 |
Fee |
1.0 |
100.0 |
100.0 |
ADVANCED |
DRAFT |
first_customer_contract |
2024-06-01 |
2024-07-01 |
1 |
Fee |
1.0 |
100.0 |
100.0 |
ADVANCED |
DRAFT |
first_customer_contract |
2024-07-01 |
2024-08-01 |
1 |
Fee |
1.0 |
100.0 |
100.0 |
ADVANCED |
DRAFT |
first_customer_contract |
2024-08-01 |
2024-09-01 |
1 |
Fee |
1.0 |
100.0 |
100.0 |
ADVANCED |
DRAFT |
first_customer_contract |
2024-09-01 |
2024-10-01 |
1 |
Fee |
1.0 |
100.0 |
100.0 |
ADVANCED |
DRAFT |
first_customer_contract |
2024-10-01 |
2024-11-01 |
1 |
Fee |
1.0 |
100.0 |
100.0 |
ADVANCED |
DRAFT |
first_customer_contract |
2024-11-01 |
2024-12-01 |
1 |
Fee |
1.0 |
100.0 |
100.0 |
ADVANCED |
DRAFT |
first_customer_contract |
2024-12-01 |
2025-01-01 |
Although only some input data was written to the new branch, invoices were still generated. This works because bframe applies the changes made in the current branch on top of the main branch. This is effective because bframe’s schema is designed to be append-only. In this case, the newly created contract_prices row will end up replacing the former, as it’s a higher version. Conveniently, this will only take place when filtering occurs to include both the main and test branch.
Below is a high level diagram of how branch filtering works in practice.
Reverting changes¶
All things considered, this was an easy change to explore how Small Co. could apply monthly invoices. While this was a nice experiment, Small Co. has already agreed to an annual payment and the contract needs to change back.
By updating the Branch ID back to the main branch, with an ID of 1, the invoices and line items can be returned to the prior state. This can be verified by generating invoices from the bframe schema once more.
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
ORDER BY i.started_at;
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 |
Fee |
1.0 |
1000.0 |
1000.0 |
ADVANCED |
FINALIZED |
first_customer_contract |
2024-01-01 |
2025-01-01 |
Storing invoices¶
At some point, most use-cases will require invoices to be persisted. bframe can directly insert invoices into the source or destination. In this case, the source and destination are the same, so we will persist this data to src.
INSERT INTO src.invoices BY NAME (SELECT gen_random_uuid() as id, * FROM bframe.invoices);
INSERT INTO src.line_items BY NAME (SELECT gen_random_uuid() as id, * FROM bframe.line_items);
Count |
|---|
1 |
Once the invoices and line items are stored, the source can be directly queried instead of relying on the bframe schema. Storing the data in the source can make the data easier to access (for other services) and can potentially speed up queries.
To verify the invoices have been persisted, run a query against the source to validate your progress.
SELECT count(*) FROM src.invoices;
count_star() |
|---|
1 |
Exporting invoices¶
Modeling a business is often a means to an end. Let’s say in this example, Small Co. would like to bill their customers, not just create a bunch of clean, sleek and beautiful datasets.
Small Co. intends to use Bill.com for invoicing, so bframe invoices need to match their specification. This is straightforward, since Bill.com and other administrative services accept data that is a tabular format (or close to one). Let’s transform the bframe invoices to match the Bill.com format required to be uploaded via CSV.
SELECT
cust.name AS 'Customer Name',
i.id AS 'Invoice #',
strftime(i.started_at::date, '%m/%d/%Y') AS 'Invoice Date',
strftime((i.started_at + to_days(30))::date, '%m/%d/%Y') AS 'Due Date',
p.name AS 'Line Item Description',
li.quantity AS 'Line Item Quantity',
li.amount AS 'Line Item Amount',
li.amount / li.quantity AS 'Line Item Price'
FROM src.invoices AS i
JOIN src.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 p
ON li.product_uid = p.id
JOIN bframe.contracts AS c
ON i.contract_id = c.durable_id
JOIN bframe.customers AS cust
ON c.customer_id = cust.durable_id;
Customer Name |
Invoice # |
Invoice Date |
Due Date |
Line Item Description |
Line Item Quantity |
Line Item Amount |
Line Item Price |
|---|---|---|---|---|---|---|---|
First customer |
18a2d73a-5fd1-4d5c-84ff-9f4c30479e8c |
01/01/2024 |
01/31/2024 |
Fee |
1.0 |
1000.0 |
1000.0 |
This can be directly exported to CSV by using the playground’s export functionality. The “Export” button can be found next to the “Submit” button. Once exported, the result can be uploaded to the destined provider.
Below is the resulting invoice from Bill.com.
Conclusion¶
This article serves as a crash course introduction to the fundamentals of bframe. The examples covered in this guide were simple, but were intended to show the potential of bframe. For a more sophisticated model check out the Wikipedia case study or dive into the business modeling documentation to learn more about how bframe works.