Multitenancy and working with finances across borders

Purpose

With new regulations for charities worldwide, the need for a centralised system becomes more and more urgent. Money must not be moved between the charities, but must stay in the costcentre of the charity that raised the money in the first place.

Obviously, questions like data protection and International Safe Harbor Privacy Principles need to be considered as well.

Definition of Multitenancy

• According to Wikipedia: "In a multitenancy environment, multiple customers share the same application, running on the same operating system, on the same hardware, with the same data-storage mechanism. The distinction between the customers is achieved during application design, thus customers do not share or see each other's data."

• Multitenancy also allows cost savings: running against only one database scales much better than to run one database per tenant.
• Multitenancy allows data aggregation and data mining: This could help us with sharing data about finances between offices of a charity in different countries, obviously with a good system for permissions.

Technical implementation of Multitenancy for OpenPetra

application

With the switch from .net remoting and appdomains to a web service based approach, we already have a working implementation of an application, that scales quite well for many different tenants, and uses the program dlls installed in one place.

database

We still use one database per tenant.

See also this article: Moving to Multi-Tenant Database Model Made Easy with SQL Azure Federations

Each table would get an additional column for the tenant id. Since we are using a database abstraction layer (Ict.Common.DB.DBAccess), we can easily add a query for column TenantID to each query.

I thought at some point, that we need surrogate keys to implement this. But I realized that you still need a column as part of the primary key and foreign keys, even if you work with surrogate key: otherwise it would be very complicated to import a database from yml, or create a new database, because the value of the surrogate key changes all the time. If you have the value of the surrogate key unique per tenant, that is fine. Surrogate keys would also increase complexity for some queries (if p_partner.p_partner_status_code_c becomes p_partner_status_code_pk, you would need a join with p_partner_status each time you query a partner for his status), while reducing complexity for other queries (eg. a_general_ledger_master_period already has a surrogate key). So I suggest to leave surrogate keys out of this project, and later decide if some tables should get a surrogate key, but not all.

New Concept for ICH with Multitenancy

Current system of ICH

The International Clearing House (ICH) module in Petra currently works with many geographically distributed databases. CSV files have to be sent from all offices to a central place, where they are combined in one ledger, and the differences are calculated for each office, and a file is sent to each office to tell them how much money they need to transfer to or collect from the central bank account. This helps to save banking fees.

Unfortunately, a lot of detail about the single transaction gets lost, and it is still quite a manual process, that only happens once a month.

Proposal

Now, if all offices were tenants on a single system, this would give a whole set of new options, which I will explain in more detail later:

• money from the giving country stays in the costcentre of the giving country
• the receiving country works with the money in a foreign costcentre, which does not report to their own local costcentre
• if two offices have the same accounts, the transactions can be shown in much more detail, split up across several accounts
• new reports would allow the receiving country to see a summary for a project across all the funding costcentres
• this would also apply to the personal support that a worker must raise for his own salary
  • the HR department of the sending office of the worker could get permissions to see a summary of all income for this worker in the office where he works
• ICH would be a single operation by each tenant: it calculates how much money we must transfer from our bank account to the central bank account. No ICH stewardship batch necessary anymore, since we post each gift directly to the foreign costcentre of the receiving field, similar for the expenses.
  • the money between ICH and the local bank account is posted against each costcentre. This way, we have a bank account consolidation on the highest level in the costcentre hierarchy, but the money does not get into the local costcentre.

Technical implementation

• Each office has a local costcentre. All money in that local costcentre belongs to that office.
  • there is a costcentre hierarchy, ie. the main local costcentre is a summary costcentre, with several reporting costcentres or sub summary costcentres.
• Each office has a foreign costcentre for each office that they work with. This foreign costcentre is outside of the local costcentre. This money belongs to the other office.
• When an office wants to post a GL batch to a foreign costcentre
  • it is checked that the other office have a local costcentre fitting this costcentre (see example below).
  • it is checked that the accounts can be matched. if there is no match, it is attempted to find an account match higher up in the account hierarchy.
  • if all goes well, a copy of the batch is created in the ledger of the other tenant as well, with the costcentre modified to the local costcentre, and the accounts matched as well.
  • both batches are posted at the same time: The advantage of having it all in one database is that we can use a single database transaction for posting both batches.
  • it could be extended that the batch needs to be approved by the other office, and then posted for both offices at the same time.
• adjustment batches help with fixing problems

• there might be rules required for posting expenses to a foreign costcentre:
  • question: who is financially responsible for the project. several giving offices for one project. the local office?
  • expenses could be limited to the income on that foreign costcentre. Or use a budget per foreign costcentre, that cannot be overdrawn.

Example

Names of Costcentres can be easier in actual system, but might help with the illustration here:

• Canada support a project (unit key 430123123) in Germany, and a worker (partner key 43023413) in Switzerland.
• Germany sends personal support for the same worker (partner key 43023413) in Switzerland.

Germany has

• local CostCentre 43-4300S Germany.
  • local costcentre 43-4300-430123123 for project, for national gifts
  • local costcentre 43-4300-43023413 for national gifts to worker in Switzerland, this money belongs to Germany
• foreign CostCentre 43-4500S Canada
  • foreign CostCentre 43-4500-430123123 project cost centre, this money belongs to Canada

Switzerland has

• local CostCentre 44-4400S Switzerland
  • local CostCentre 44-4400-43023413 costcentre of worker for local gifts, money belongs to Switzerland
• foreign CostCentre 44-4300S Germany
  • foreign costcentre worker 44-4300-43023413, money belongs to Germany
• foreign CostCentre 44-4500S Canada
  • foreign costcentre worker 44-4500-43023413, money belongs to Canada

Canada has

• local costcentre 45-4500S Canada. all money inside local costcentre belongs to Canada
  • local costcentre 45-4400-43023413 gifts for worker in Switzerland. Switzerland indirectly post expenses to this costcentre
  • local costcentre 45-4300-430123123 gifts for project in Germany. Germany indirectly post expenses to this costcentre

Someone donates to Germany money for the worker in Switzerland:

• money goes to local CC 43-4300-430123123
• Switzerland should see the income on their foreign CC 44-4300-43023413 and can post expenses to it.
• the expenses from Switzerland are posted in the german ledger to the local costcentre 43-4300-430123123

Switzerland has expenses for the worker:

• expenses are split among foreign costcentres 44-4300-43023413 and 44-4500-43023413
• there might be rules required (see above)

workaround if multitenancy is not an option for some (or all) offices

For offices that cannot be part of the multitenant centrally hosted OpenPetra server, they would somehow need a system where limited functionality is implemented by using web services to regularly upload and pull batches to the central server.

Still, most of the proposed way of using Costcentres could still be applicable.