Database Model

JBILLING_TABLE and Textual Descriptions

The jbilling_table table contains a list of all the tables within the jBilling schema. We use this list of tables to form arbitrary associations to any table within the system. This is important for something like a unified translation table, since it would be wasteful to have a key constraint for every single possible table mapping in the system. It's easiest to think of this approach as a standalone foreign key constraint system.

The international_description table_id column references the jbilling_table id. This gives us the mapping to other tables within the jBilling system. In the case of international_description, "where table_id = 14" would denote a description row for an item.

If we think of this as a foreign key constraint, then table_id would be table association, and foreign_id would be the corresponding ID of the associated table. Again, in the case of international_description, "where table_id = 14 and foreign_id = 100" would denote a description row for an item with ID 100.

The international_description psudo_column column is simply an identifier for the text value. Not all descriptions will be labeled "description"; some tables may have a "title" or "name" as well.

This pattern is used elsewhere in the jBilling schema, but it's easy to identify as such when you see the mapping columns table_id and foreign_id.

Users

Users reside in the base_user table. A user is considered a partner or a customer depending on the associations it has. A customer can be a partner, a customer, or neither. A user without an associated customer record is considered to be an internal jBilling user, such as an admin or a clerk.

Most of your users (if not all) will have contact information necessary for billing. However, contact information is not directly associated to a base_user. The contact table is constructed so that contact information can be associated with any record in any table through the system. Through the API, jBilling currently only allows contact information to be applied to customers, but it is still an important distinction to note if you're writing your own queries.

You'll notice that the contact_map table shares some similar characteristics to the international_description table in the previous section. This is because they both use the jbilling_table to arbitrarily map associations to other tables within the jBilling schema and should be handled the same way.

Items

All order items within jBilling exist in the item table. These items will always have a price as an association in the item_price table, whether the price is zero or not.

Items have a type assigned to them via the item_type table and the item_type_map table. Items may belong to more than one type. Types are an ad hoc organizational mechanism that is useful for categorizing items for custom plug-in logic or rules; for example, add extra tax if item is a 'call' item.

Items are added to orders and invoices as lines (invoice_line and order_line tables) using the item ID as a back reference. Since items can change over time, jBilling invoice and order lines always have their own price and line description; jBilling only uses the item data to initialize the lines item_price and item_id values. This means that a line always shows exactly what is being billed, regardless of recent updates to the base item (changes to the price, item description, etc).

Note that item does not contain a description field. All textual descriptions within jBilling come from the international_description table. Descriptions are covered in more detail in the JBILLING_TABLE and Textual Description section.

Purchase Orders

Purchase orders represent all the charges made to your customers, including subscriptions and one-time purchases. For more information on the attributes of an order, please read the User Guide. Purchase orders can be created manually through the UI or through the mediation process as incoming data is read into the system.

Orders can exist in several states marked by the status_id column:

Each order has a collection of order lines, these lines represent a quantity of an item that has been purchased at a set price. The price of the order line may be different from that of the base item. jBIlling only uses the base item to initialize the order line; price changes, discounts, and other events occur independently.

Orders themselves are not payable; they simply represent charges accrued by the customer. The billing process periodically creates invoices (which are payable) from a customer's open purchase orders. This can be tracked through the order_process table, which links an order to an invoice.

Invoices

Similar to the way items are only used to create order lines, orders are only used to create invoices. There is no direct association between order and invoice, or between order_line and invoice_line. Orders and invoices share much of the same data, but because invoices may change (get paid, deleted, etc.), and recurring orders can generate multiple invoices, jBilling essentially "copies" order data into the invoice when it is created.

Invoices don't always represent charges for just one period, either. If an invoice from a previous period was not paid or only partially paid, the remaining balance is included as the carried_balance. In this case, the previous invoice is marked as 'carried' and the new invoice represents the current owing charges of a customer.

Like orders, invoices can exist in several states marked by the status_id column.

The balance column represents the total balance of this invoice, including the carried_balance. When you pay an invoice, the balance is lowered by the amount of the payment. An invoice with a balance of zero is considered to be 'paid.'

Payments made to the invoice are mapped through the payment_invoice table.

Payments

jBilling accepts multiple forms of payment: credit card, automatic clearing house (ACH) direct bank transfers, and paper check payments. In most cases, the payment method is determined beforehand (ACH and credit card) and payments are made using these details by an appropriate payment processor. Generally a payment "uses" a credit card or ACH to submit a payment and retrieve a result. If the returned result is a success, the invoice balance is lowered by the payment amount.

In the case of payments by check, things work a little differently. With check payments, the payment record is created first, and the payment_info_check record is added after, so that the source of the payment can be tracked.

In all cases, the payment record shows exactly how much has been paid, if it is a refund, when it was paid, and by whom.

Generally payments are made to an invoice, however this is not always the case. Payments can be made against the entire account, leaving a positive balance instead of a negative owing balance. In these cases, jBilling will attempt to apply the payment to any unpaid invoices first. The remaining balance will be used against the next invoice that is generated. In these cases, you may see multiple entries in the payment_invoice table, or none at all if no invoice was paid (don't worry, the payment will eventually be used).

Credit card and ACH payments will always have an associated payment_authorization record. The authorization contains all the details of the transaction, including error codes, response messages, and other details returned by the payment processor.

Credit Cards

Credit card details are stored in the credit_card table in an encrypted form. By default the credit_card number is never available in plaintext, only as encrypted text.

Many payment gateways go beyond the need for jBilling to store the credit card number (even in an encrypted form), and will provide a transaction ID or storage ID that can be used for future credit card payments without the need to pass all the details. jBilling stores this value as a gateway_key. When a gateway key is present, the credit card number is stored as an obscured string meant only for display purposes (something like ************1234).

The same credit card can be used by multiple users. Each user is mapped to a credit card through the user_credit_card_map table.

Mediation

The mediation process stores a record of every processed event in the mediation_record table. The mediation_record tells us what record was processed (id_key), when it was processed, and the outcome of each particular record.

Records can exist in several states marked by the status_id column:

Any mediation_record that adds a line to an order has an associated mediation_record_line entry. The mediation_record_line shows what item was added to an order line and in what quantity. Note that order_lines of the same item will be "merged" together into a single line. This means that you may have multiple mediation_record_line entries pointing at the same order_line.

mediation_record_line can be seen as a history of all the events that make up a mediated order.