Check Credit

Requirements

Consider this specification of the check credit requirement, typical of so many transactions - we roll up some values and apply some constraints:

Our cocktail napkin spec is really nothing more than a set of spreadsheet-like rules that govern how to derive and constrain our data.  And by conceiving of the rules as associated with the data (instead of a UI button), we address all of these Use Cases:

• add order
• ship / unship order
• delete order
• assign order to different customer
• re-assign an Order Detail to a different Product, with a different quantity
• add/delete Order Detail

The legacy implementation of this spec turns our 5 simple, transparent rules into over 200 lines of code (view them here):

Declare Rules using Python

Logic is declared as spreadsheet-like rules as shown below (lines 34-48) from this example, which implements the check credit requirement (the balance - a rollup of unshipped order totals - cannot exceed the credit limit).  This is exactly our cocktail napkin spec, expressed in Python (here shown in PyCharm, a Python IDE):

To activate the rules (declare_logic is the function shown in the screen above):

LogicBank.activate(session=session, activator=`declare_logic`)

Observe these are not simple single-field validations - they address complex multi-field, multi-table logic.

Logic Bank Execution

Logic Bank operates as a plugin to SQLAlchemy:

1. You declare logic as multi-table rules and Python (see "Declare Rules Using Python" as shown above).

2. Your application makes calls on sqlalchemy for inserts, updates and deletes.

3. Logic Bank handles SQLAlchemy before_flush events on Mapped Tables.

   • By injecting transaction logic into SQLAlchemy data access, your logic is automatically invoked.  So, it governs updates from hand-written code (Flask apps, APIs) or via generators such as Flask AppBuilder.

4. The logic engine operates much like a spreadsheet:

   • Watch - for inserts, deletes, and updates at the attribute level.
   • React - derivation rules referencing changed attributes are (re)executed (forward chaining rule inference); rules are pruned if their dependent data is not changed.
   • Chain - if recomputed values are referenced by still other rules, these are re-executed. Note this can be in other tables, thus automating multi-table transaction logic.

Logic  expressed in Logic Bank does not apply to updates outside SQLAlchemy, or to SQLAlchemy batch updates or unmapped sql updates.

Logic Execution: Watch, React, Chain

Let's see how.  Consider inserting the OrderDetails for an order: roll up to AmountTotal / Balance and _check credit:

The diagram illustrates chaining as each OrderDetail is inserted:

1. The OrderDetail.UnitPrice (copy, line 49) references Product, so inserts cause it to be copied

2. Amount (formula, line 48) watches UnitPrice, so its new value recomputes Amount

3. AmountTotal (sum, line 46) watches Amount, so AmountTotal is adjusted (more on adjustment, below)

4. Balance (sum, line 43) watches AmountTotal, so it is adjusted

5. And the Credit Limit constraint (line 40) is checked (exceptions are raised if constraints are violated, and the transaction is rolled back)

Extensibility: Standard Python

While rules automate much, they don't automate everything. Every app has elements that integrate with outside systems, such as sending mail or messages.

The screenshot above (see heading - "Declare Rules Using Python") includes a rule on line 51 that invokes the Python function on line 32.  So, whatever you can't do in rules, you have the full power of Python.

Scalability: Automatic Pruning and Optimization

We typically think of scalability in terms of clustering.  And containers like Flask support that.

But all the clustering in the world cannot cover inefficient database access.

Let’s face it, many automation initiatives have failed to provide effective automation for transaction logic, including Rete-based rule engines, and Object Relational Managers (ORMs).  The primary issue has been scalability: excessive and expensive SQL queries.

However, Logic Bank is specifically designed to address this.  Let’s see how.

Optimizations: Adjustment (vs. nested sum queries)

The Customer.Balance sum rule (line 43) "watches" changes to Order.AmountTotal - a different table.  So, the "react" logic has to perform a multi-table transaction, which brings our performance issue to bear.

As is commonly the case (e.g. Rete engines, some ORM systems), you may reasonably expect this is executed as a SQL select sum.

In Logic Bank, it is not.

Instead, Logic Bank optimizes it as an adjustment: as single row update to the Customers balance. This optimization dramatically reduces the SQL cost, often by orders of magnitude:

• select sum queries are expensive - imagine a customer with thousands of Orders.

• In this example, it's particularly critical, since it's a chained sum. So, a Rete approach to computing the balance would require not only reading all the orders, but all the OrderDetails of each order.  This is often discovered late in a project when testing live data volumes, and requires substantial recoding.

In addition, Logic Bank provides automatic pruning. If an update occurs that does not affect the sum, no SQL is issued at all.  Contrast this to ORM / Rete engines, where the typical approach is to run all the sum queries, all the time.

Manageability: Standard Language, Tools

Code Management: Standard Language, IDE Enabled

The "Declare Rules" screenshot shows that rules are defined in Python.  You can use standard source control systems and procedures to manage your logic.  There are no databases, xml or json files to configure and manage.

Python has recently been extended with type support, enabling your IDE to provide code completion, and spot many errors.  Logic Bank uses this type support (see line 32).

Debugging: Standard Debugger, Logic Logging

The "Declare Rules" screenshot also illustrates that you can stop in your rule logic (the red dot on line 35), and use your IDE (here, PyCharm) to see variables, step through execution, etc.

In addition, Logic Bank logs all rules that fire, to aid in debugging.  If we run add_order.py, we get the following log (see the lower pane in the following screenshot):

• Each line represents a rule execution, showing row state (old/new values), and the {reason} that caused the update (e.g., client, sum adjustment)
• Log indention shows multi-table chaining