Finance Data Model
- 1 Overview
- 2 Schemas
- 3 Acquisition Units
- 4 Transactions
- 4.1 Schemas
- 4.2 APIs
- 4.3 Encumbrance as a Transaction
- 4.4 Updating Allocated/Unavailable/Available and Other Running Totals
- 4.5 Preventing Partially Paid Invoices (deprecated method)
- 4.6 "All or Nothing" Operations (deprecated method)
- 4.7 Batch all-or-nothing operations (new method)
- 4.8 Calculations
- 4.9 Transaction Restrictions
- 4.10 Tags
- 5 FundTypes
- 6 Funds
- 7 Groups
- 8 Ledgers
- 8.1 Schemas
- 8.2 APIs
- 8.3 Updating the LedgerFY table
- 9 Budgets
- 10 Fiscal Years
- 11 Expense Classes
- 12 Database Views
- 12.1 LedgerFY
- 12.2 GroupFundFY
- 12.3 LedgerBudget
- 12.4 End-to-End Flow
- 13 JIRA
- 14 Mockups
- 15 Open Issues/Other Considerations
- 16 Appendix
Overview
Most of the finance schemas are well out of date and need to be updated. Recent clarifications on requirements have also introduced new complexities and challenges that need to be overcome. Given the scope of these changes and how interrelated the various schemas and APIs will be, creating a wiki page to capture this information seemed to make sense.
Schemas
Schemas exist for most of the financial records, but will need to be updated as they're out of date. The Acquisitions Interface Fields maybe be helpful to some degree, but isn't a direct 1-to-1 mapping with the schemas due to intricacies of the data model, e.g. internal/storage use are needed which aren't captured in that spreadsheet.
Acquisition Units
The complex relationships between the various record types makes it difficult to use an inheritance pattern like we've used in other apps. For this reason, most finance records will have an acquisitionUnits field. How these are applied is slightly different as well. The fiscal year detail view for instance displays ledgers, groups and funds (budgets). In order to keep things reasonably simple, if the user has the folio permission to view the fiscal year, and belongs to one of the acquisition units assigned to the fiscal year record, the ledger, group and fund (budget) data can also be viewed. The same concept applies across the board - e.g. ledgers detail view shows groups and funds (budgets). We will need to get creative with our APIs and database views to make this work.
Transactions
Immutable
Payments, credits, allocations, transfers, encumbrance (see below)
Each of these will have their own API to allow for business logic rules, but may share a table in the storage layer.
Since there isn't a directly link to a budget, Querying by toFundId, fromFundId, and fiscalYearId is needed to satisfy:
GET transactions by Budget
Single transactions table, shared by multiple APIs
Schemas
transaction
Encumbrances
Property | Type | Default | Required | Notes |
|---|---|---|---|---|
id | string | N | UUID - System generated if not specified | |
amount | number | Y | Ideally we could filter by a range of values; also be able to search by exact amount of transaction. | |
description | string | N | ||
source | string | Y | The readable identifier of the record that resulted in the creation of this transaction | |
transactionType | string | Y | This describes the type of transaction | |
fromFundId | string | N | UUID of the fund money is moving from | |
toFundId | string | N | UUID of the fund money is moving to | |
fiscalYearId | string | Y | UUID of the fiscal year that the transaction is taking place in | |
sourceInvoiceId | string | N | UUID of the Invoice that triggered the creation of this transaction | |
sourceInvoiceLineId | string | N | UUID of the InvoiceLine that triggered the creation of this transaction. Needed to support an idempotent payments/credit API | |
sourceFiscalYearId | string | N | UUID of the fiscal year that triggered the creation of this transaction (Used during fiscal year rollover) | |
tags | tags | N | ||
currency | string | Y | currency code for this transaction - from the system currency. | |
paymentEncumbranceId | string | N | UUID of the encumbrance associated with this payment/credit taking place. | |
encumbrance | encumbrance | N | Encumbrance sub-object - holds encumbrance-specific information not applicable to other transaction types | |
awaitingPayment | awaiting_payment | N | Awaiting payment sub-object - holds awaiting payment-specific information | |
metadata | metadata | N | System generated metadata (createdBy/updatedBy/createdOn/etc.) |
encumbrance
Property | Type | Default | Required | Notes |
|---|---|---|---|---|
initialAmountEncumbered | number | Y | Shouldn't change once create | |
amountAwaitingPayment | number | N | ||
amountExpended | number | N | ||
status | string | Y | enum: Released, Unreleased | |
orderType | string | N | Taken from the purchase order. enum: One-time, Ongoing | |
subscription | boolean | N | Taken from the purchase order. Has implications for fiscal year rollover | |
reEncumber | boolean | N | Taken from the purchase order. Has implications for fiscal year rollover | |
sourcePurchaseOrderId | string | N | UUID of the purchase order associated with this encumbrance | |
sourcePoLineId | string | N | UUID of the poLine associated with this encumbrance |
awaiting_payment
Property | Type | Default | Required | Notes |
|---|---|---|---|---|
encumbranceId | string | N | UUID of the encumbrance to updated | |
amountAwaitingPayment | number | Y | The amount of money moving from encumbered → awaitingPayment | |
releaseEncumbrance | boolean | false | Y | Whether or not remaining encumbered money should be released |
invoice_transaction_summary (deprecated in Quesnelia)
Property | Type | Default | Required | Notes |
|---|---|---|---|---|
id | UUID | Y | UUID of the invoice these payments are associated with (Unique) | |
numPendingPayments | int | Y | Total number of pending payments created for this invoice upon invoice approval. | |
numPaymentsCredits | int | Y | Total number of payments/credits expected for this invoice |
temporary_invoice_transactions (deprecated in Quesnelia)
Property | Type | Default | Required | Notes |
|---|---|---|---|---|
id | UUID | N | UUID of the payment (Unique) - Generated by the system | |
transaction | transaction (jsonb) | Y | The payment or credit |
order_transaction_summary (deprecated in Quesnelia)
Property | Type | Default | Required | Notes |
|---|---|---|---|---|
id | UUID | Y | UUID of the order these payments are associated with (Unique) | |
numTransactions | int | Y | Total number of transactions (encumbrances) expected for this order |
temporary_order_transactions (deprecated in Quesnelia)
Property | Type | Default | Required | Notes |
|---|---|---|---|---|
id | UUID | N | UUID of the transaction (Unique) - Generated by the system | |
purchaseOrderId | UUID | Y | UUID of the order these payments are associated with | |
transaction | transaction (jsonb) | Y | The transaction (encumbrance) |
APIs
Business Logic Module
Method | Path | Request | Response | Description | Notes |
|---|---|---|---|---|---|
POST | /finance/allocations | transaction | transaction | Create an allocation | calls POST /finance-storage/transactions |
POST | /finance/credits | transaction | transaction | Create a credit | calls POST /finance-storage/transactions |
POST | /finance/payments | transaction | transaction | Create a payment | calls POST /finance-storage/transactions |
POST | /finance/transfers | transaction | transaction | Create a transfer | calls POST /finance-storage/transactions |
POST | /finance/encumbrances | transaction | transaction | Create an encumbrance | calls POST /finance-storage/transactions |
POST | /finance/pending-payments | transaction | transaction | Create an pending payment | calls POST /finance-storage/transactions |
GET | /finance/transactions/<id> | NA | transaction | Get a transaction by Id | |
GET | /finance/transactions | CQL Query | transaction_collection | Search/List transactions | |
PUT | /finance/encumbrance/<id> | transaction | transaction | Update an encumbrance |