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). |image1| 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. |image2| 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. .. tab-set:: .. tab-item:: Input query .. literalinclude:: /_static/ex_sql/pe_before.sql :language: sql .. tab-item:: Executed query .. literalinclude:: /_static/ex_sql/pe_after.sql :language: sql 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| Organization setup ~~~~~~~~~~~~~~~~~~ To start, Small Co. needs an `organization <./erd/organizations.html>`_ to represent them. This allows data within bframe to be recognized as belonging to Small Co. An `environment <./erd/environments.html>`_ and a `branch <./erd/branches.html>`_ are also required to complete the setup. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/quick_start_1_1.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/quick_start_1_1.csv :class: bmodel-table :header-rows: 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. .. figure:: /_static/images/introduction/bframe_config_ui.png :width: 500px :align: center 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. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/quick_start_1_2.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/quick_start_1_2.csv :class: bmodel-table :header-rows: 1 ``_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 <./erd/customers.html>`_ 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. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/quick_start_1_3.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/quick_start_1_3.csv :class: bmodel-table :header-rows: 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 <./erd/products.html>`_ called ``'Fee'``. This is a ``'FIXED'`` `product `_ which does not have any variable components. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/quick_start_1_4.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/quick_start_1_4.csv :class: bmodel-table :header-rows: 1 Provisioning ~~~~~~~~~~~~ Transactions, to sell goods and services, are represented by `contracts <./erd/contracts.html>`_. 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 <./erd/contract_prices.html>`_ dictates that the fee is $1000, paid upfront, and is for services to be rendered over 12 months. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/quick_start_1_5.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/quick_start_1_5.csv :class: bmodel-table :header-rows: 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. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/quick_start_1_6.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/quick_start_1_6.csv :class: bmodel-table :header-rows: 1 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. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/quick_start_1_10.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/quick_start_1_10.csv :class: bmodel-table :header-rows: 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. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/quick_start_1_11.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/quick_start_1_11.csv :class: bmodel-table :header-rows: 1 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. .. figure:: /_static/images/introduction/branch_methodology.png :width: 500px :align: center 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. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/quick_start_1_12.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/quick_start_1_12.csv :class: bmodel-table :header-rows: 1 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``. .. TODO2 (should we create a `dest` db like the `src` db in the bframe client?) - with a bit of thought i'd say it should optional to set a destination. Often times it's going to the same as the source. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/quick_start_1_7.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/quick_start_1_7.csv :class: bmodel-table :header-rows: 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. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/quick_start_1_8.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/quick_start_1_8.csv :class: bmodel-table :header-rows: 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. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/quick_start_1_9.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/quick_start_1_9.csv :class: bmodel-table :header-rows: 1 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. |image0| 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. .. |invoice| raw:: html :file: ./_static/html/quick_start_invoice.html .. |image0| image:: /_static/images/introduction/bill_com_invoice.png .. |image1| image:: /_static/images/introduction/bframe_high_level_erd.png .. |image2| image:: /_static/images/introduction/bframe_lib_high_level.png