Wish List - Proposed db changes for billing

[Ruth Frasur Davis]

From work done by Jason Boyer related to planning changes to the Evergreen Money schema

Included link includes formatted text and comments made to the following text prepared by Jason.

https://docs.google.com/document/d/1RDDpC7XqTN-cztHB8ogoY1QknqWD6PnnVpVvAUZ0TnM/

Goals
Add/remove payment types easily
Track payment types applied to specific bills and billing types
Allow finer grained permissions on payments
Track "who" owns individual billings and is owed the payments put toward them
Allow some types of bills to be prioritized over others

Use cases
Payment types:
A library in Silicon Valley has an enormous endowment and is prone to experiments charitably described as “fiscally adventurous.” They would like to add a Bitcoin payment type to track what is paid with their btc kiosks in the lobby. Currently this requires additional database tables at a minimum, which is a lot of effort for what will amount to an amusing story at a conference panel.

Payment tracking:
A library is considering going fine free because they usually will waive a large portion of users’ fines, but they want to make sure there won’t be any unexpected impacts on their budget. To plan for this they want to know the breakdown of incoming payments for lost, damaged, overdue, and so on. With a mix of actual currency payments, forgive_payments, voiding, and account adjustments, this is more difficult than it needs to be. If they could form a report such that only those bills paid with currency were totaled they could easily see the impact going fine free would have on their budget and plan accordingly.

Payment permissions:
Libraries A and B are in the same consortium and very near each other so patrons will frequently travel back and forth. Lib B is very forgiving about their overdue fines and will frequently forgive them; so frequently that they’ll sometimes accidentally forgive fines owed to Lib A, which is causing friction between the two. If Lib B desk staff weren’t able to apply forgive payments to Lib A bills this would be prevented.

Billing ownership:
A resource sharing consortium wants to allow its users to pay their bills anywhere but still route the money to the correct locations. Without any direct connection to the location that is owed the billing it can be difficult to automatically determine who gets what when an item is lost at a remote location, because all of the billings on a transaction appear to originate from the circulating library. If there were a way to automatically indicate that all lost or damaged billings were owed to the item (or call number) library it would be easy to determine who is owed what in this scenario.

Billing Priority:
Lib A uses a collection agency to assist in recouping material costs or to encourage their return. They prefer to pay off collection agency fees and lost item billings first, leaving overdue or other billings on a transaction until later when they may forgive them anyway.

Proposed db changes

New fields on config.billing_type
priority - int; higher priority is paid first within a transaction (Lost billings could be higher priority than Overdue, for example.) To ignore, set all equal to fall back to billing_ts.
context - text; How to determine which location has applied this billing. Valid values are item, call_number, circ, manual. These correspond to circ.target_copy.circ_lib, circ.target_copy.call_number.owning_lib, circ.circ_lib, or the aws.owning_lib that applied the manual billing. Item and circ are transactional only, manual is manual/grocery only (but can be manually applied to an open circ transaction just as can be done today).

New table for payment types
config.payment_type
code text, -- cash, check, credit_card, etc. Drop the _payment?
name text, -- display name
amnesty bool, -- true for forgive, work, and account_adjustment, false for cash, check, etc.
owner org_unit, -- the usual
active bool, -- You didn’t really think you’d be able to delete these, did you?

Also add a new permission just for payments where amnesty=true. Permission depth (for all payments) would be compared with Billing Context to determine if a payment can be applied. For example, currency payments may be allowed at a consortium level but amnesty only at the system or branch level; so any bill can be paid with cash anywhere but a library can only “forgive” their own bills as determined by the billing context. Voiding permission will also take billing context into account. Amnesty payment types also invert the billing priority, so a cash payment would pay for a lost billing first where a forgive payment may clear overdue fines first. Depending on requirements it may be possible add Unrecoverable Debt as an amnesty type, making it possible to more fully implement a feature initially related to the (unused?) unrecovered debt field on mbx.

A single table for all payments
money.payment -- old idl class can be mapped to a new view for reporting purposes
id bigint, -- new sequence
payment_ts timestampz, -- same as current
payment_type text, -- config.payment_type.code
amount_collected numeric (6,2), -- the exact amount entered into the payment field.
amount_applied numeric (6,2), -- how much was actually applied
note text, -- same as current
accepting_usr bigint, -- same as current, but on every payment type. Can be null.
accepting_workstation bigint, -- same as current, but on every payment type. Can be null.
voided bool, -- same as billing field. Should either be available to users or removed.
voider bigint, -- same as above.

New table to map payments to billing line items
money.billing_payment_map
id bigint, -- again with the sequencing
billing bigint, -- money.billing.id
payment bigint, -- money.payment.id
amount_applied numeric (6,2), -- the amount of the payment applied to this specific billing

A many-to-many table links payments to billings according to context and priority (as determined by the perl layer, there’s nothing in the db that knows or cares about priority).

New table for extended payment attribute definitions
config.payment_xattr
name text, -- JSON field name
label text, -- Display name for client
payment_type text, -- which payment type this is attached to
datatype text, -- string, int, etc. (same list as config.usr_setting.datatype)
required bool, -- does the client require a value before submitting payment

Stored in a new table in money schema
money.payment_xattr
id bigint, -- you know
payment bigint, -- the payment this collection of attributes is associated with
xattr text, -- JSON collection of extended attributes

If payment types just become names there needs to be a way to store additional information for type-specific information like check number and CC auth code, CC last 4 (if stored at all), cardholder name, etc. They have no effect outside of client display and receipts so shouldn’t require much special handling. Stored as a single JSON object based on a list of fields for each payment type. All “fields” for each payment type are stuck in a single JSON collection for each payment so any extended attributes can be pulled in with a simple left join of money.payment and money.payment_xattr on id. Extended attributes could potentially be applied to billing types if use cases arise.

To better mirror circulations, money.grocery should have fields added for billing_staff and billing_ws.

[Susan Morrison (smorrison425)]

Fields for billing_staff and billing_ws for grocery and circulation bills would be so helpful for tracking and training. It would be especially helpful if this were tracked for each billing line item.