System Overview
The project idea "Fedora Financial System" can be used within the project to track and analyse the financial activities for FAms (Fedora Ambassadors), Managing Fund Requests, Receipts, Budget Proposals and generating financial reports. In this system we will try to implement the current financial process for fedora ambassador described below in respect to standardization between regions that will give Fedora Ambassadors Steering Committee (FAmSCo) a cross region reports generation.
Current Financial Process
To ask FAmSCo for budget is necessary to complete the following steps:
- Have organized/structured the event or the events that are you going to do.
- Estimate costs supported by receipts or buy orders.
- Update your user page on the Fedora wiki to make sure you are working for the project.
- Have a PayPal account (other options are available) to receive the money.
- Log in with your FAS Account on FAmSCo Trac.
- Create a new ticket.
- In the body of the ticket, explain in details the reason of the budget request. This must include to which events the budget will supply and what impact these events would have to Fedora project. Also, detail where the budget is going to be spent and attach all receipts and buy orders.
- Assign your region, the component "Budget" and the required amount.
- Create the ticket and wait for some member of the FAmSCo team to value it.
If you were approved, you will receive the money depending on the decision you and main contact of the credit card decided. Else, you must rewrite your ticket to see if they reconsider sending that money. Source
Then the current Reimbursements Process.
From the above process, a lot of steps and tools where used and here comes our role to create a standardized and all in place process for FAms to request funds, report budgets and for Budget Owners and FAMSCO (any other administrator) to be able to monitor, approve and generate reports.
Initial Given Scenarios
- At the start of the year Budget owner set limits to regions (NA,EMEA,LATAM,APAC)
- FAm make a fund request.
- Once approved budget owner should approve the request and then deduct the amount from the allocated budget.
- Once the FAm submit the receipts it should be forwarded to the Ledger(assume this is like updating a table for now).
- At the end of the year FAm can submit their next year budget requirements to the system.
- Budget owner can see those budget proposals and able to query them for region, amount, types and so on.
Definitions
- Regions - Fedora Ambassadors are divided into regions (APAC, EMEA, NA & LATAM).
- FAm - Fedora Ambassadors are the representatives of Fedora. Ambassadors ensure the public understand Fedora's principles and the work that Fedora is doing.
- Budget Owners - Every region have a Budget Owners who determine budget limit and approve fund requests.
- Double Entry Accounting System - In a ‘double entry’ system each value is stored twice, once as a credit, once as a debit.
- Sponsor - Fedora Sponsors are entities or organizations which fund the Fedora Project.
Frameworks & Tools
PHP Framework
The project will be developed using php and Laravel 4 (In beta but can be used for production), Laravel 4 is a framework for web artisans, applies the Model View Control (MVC) architecture/design pattern. Laravel contains a very strong database Object Relationship Mapper (ORM) called Eloquent (ActiveRecord Implementation) which will help us with our complex relations.
Laravel 4 also implements restful controllers / Resource Controllers and restful routes pattern in core.
It also contains a strong logic-less templating engine called blade which support layouts and sections (to ensue code reusability).
Why Laravel?
- MIT licence which is GPL compatible.
- Strong Community
- MVC Framework
- Composer Friendly. Can get multiple packages from Packagist.
- Strong ORM, Called Eloquent.
- Database Migrations. Simple Version control for Database Structure.
- Come in handy with a built in CSRF protection.
- Can force HTTPS on all routes or specific routes.
- RESTful ready, Helps creating RESTful controllers, routes, resources.
- JSON packed in, can return JSON, and auto detect requests types (If JSON will auto parse it, If normal form submissions will deal with it normally to capture the inputs)
- Backed by many Symphony2 components.
- All components are Unit Tested.
Note: I can't list all laravel features, I am only listing the features that will help us in our system.
UI Framework
I am planning to use Twitter Bootstrap v3 (Still in development but the milestone is near to the end), It should be launched before GSOC13 coding period starts.
The Twitter Bootstrap library will be themed in an external CSS file (To ensure that we will get regular bug fixes and don't break our theme), I will theme all the UI elements to make the project looks unique and match Fedora's branding guideline, Will try to keep the design Flat as much as possible (less shadows).
Twitter Bootstrap also plays well with Laravel in pagination and other elements.
Version Control & Issues Tracking
I will be using Git and Github, I will push code updates regularly, and I will use the GitHub issue tracker to track bugs and feature requests. At the end of the project we can migrate it to fedora's git repos.
Database
Data Models
- Region: Responsible on each region (NA,EMEA,LATAM,APAC).
- FundRequests: Handles the fund request by FAm details.
- Receipts: Represents each receipt and related it to it’s FundRequest.
- User
- User Role
- Account
- Journal
- PostingEntry
- FinancialYear (Optional, We don't needed but it's availability may ease some report generation and keep the data models organized)
- BudgetProposals: Represents Budget Proposal by FAm at the start of each year.
- ProposalItems: Represent each item in the BudgetProposal. (Need more clarifications on type of items and needed information)
Note: Data models are subject to change according to the final requirements and implementation of additional drivers.
An old detailed database structure with relations and tables can be found here.
Access Control
FAS authentication
The Fedora Account System keeps track of Fedora Project contributors and the projects they work on. It is used to grant authentication and authorization to various components. FAS will be implemented in our system to authenticate users by implementing the Fedora Account System JSON interface.
Process:
- User ask the system to authenticate
- FAS will authenticate the user, (We can also use the single sign-on to make it easy for users.).
- FAS will send the username or unique user id.
- Username or unique user id will be saved in a table with the corresponding role_id (If user already already in table will apply the role_id), If new user we will have to attach the less privilege role to the user).
- When the user perform an action the system will check the corresponding role id if that role is permitted with Permission to do the action.
Suggestion: I am not sure that FAS support groups, If it support groups and the api allows to query the authenticated user groups, I suggest creating two groups in FAS one for the Budget owners and one for the FAm (Maybe both are already created), and instead of doing the role assignment inside our system, It happens inside FAS.
Roles
We will create roles to be able to determine if that user have the right permission to perform a specific action. (Creating additional Roles will not be a burden, Because the system check permissions not roles, we can create new roles and assign them with a set of pre implemented permissions anytime).
Basic Roles:
- Adminstrator
- FAmSCo members
- Budget Owner
- Sponsor
- FAm
Permissions
Permissions is a set of actions which maybe shared by one or more Role(s). In the system we are going to check the user permission before making any action, If the logged in user role has the needed permission to perfore the action It will be performed otherwise he/she will be prompt by an error message e.g ("Access Denied") or we can throw an exception.
Example of permissions:
- view_budget
- allocate_budget
- view_fund_request
- create_fund_request
- approve_fund_request
Determining all the Permissions now is a hard job, I will maintain a list of permissions and explanation for each one in a wiki page while implementing the project.
Double Entry System
In a ‘double entry’ system each value is stored twice, once as a credit, once as a debit. More info about double entry system with relational database here.
Rules to consider when making a double entry:
- Every entry into the system must balance – i.e. the sum of any transaction must be zero.
- The sum of all the values in the system at any one time must be zero (the ‘trial balance’).
- No values can ever be amended or deleted. They must be negated with an opposing entry (a ‘contra’) and re-entered (‘re-booked’).
Transaction Types
There will be two types of transactions in our system.
- Deposit: Will be used when the Budget Owner allocates budget.
- Withdrawal/Payment: Will be used when the FAm attach the receipt to an approved Fund Request, to create the needed journal entries.
Accounts
Basic Accounts to be used in any journal entry, Account will increase as the system expand, as a start we will track the following accounts:
- Cash Books: To track the cash in/out flow to the system
- Allocated Budget: To track the decreases and increases for each region allocated budget.
- Region Expense: To track the expenses for each region.
Note: Each region will have two accounts (Allocated Budget and Region Expense) and after any transaction related to a region the system will auto update the account_balance after doing some logic described below in the Journal Section. So we will always have the account balances to query about when generating reports.
The account table will save the following:
- account_id
- account_name
- account_balance (Final balance of the account).
Journal
Journal is a representation for both debit and credit side of any transaction (Check Posting Below), Here we will save a unique sequential id for the journal, Transaction Type, User and Region, When creating a new journal the system will auto create two entries in the Posting table one for the debit and the other for the credit each with the amount and accounting period. Then the system will get the amount of each posting entry, corresponding amount and check the posting type against the account nature and if the credit means subtracting from account the posting amount will be subtracted from the current account balance to save the final balance for easy report generation on accounts and vise versa.
Account Nature Definition: More details here or here.
- Cash -> Assets (Debit = Increase, Credit = Decrease)
- Allocated Budget -> Income Account (Debit = Decrease, Credit = Increase) HEADS UP!: Check if allocated budget account nature is an Income Account or Owner Equity account.
- Region Expense -> Expenses (Debit = Increase, Credit = Decrease)
Note: The system should verify that every journal entry created have two corresponding posting entries, to ensure that the accounting rule is applied.
Posting
Posting is where the actual amounts are saved whether to the debit side or the credit side, we will generate a unique id for each entry then save:
- journal id: The corresponding journal id from the above.
- account id: The corresponding account e.g (cash books, allocated budget, region expense).
- posting type: Debit (dr) or Credit (cr).
- amount: The absoute value of the transaction amount.
Note:
- System must not allow an incomplete one (debit without credit or vise versa).
Examples & Scenarios
Example Scenario 1:
- Budget Owner "Tom" allocate $500 for region "EMEA". Deposite transaction.
- In normal paper double entry accounting system the allocated budget account will be credited by $500, and the Cash Book account will be debited by $500 to match.
- The system create a new Journal entry which will save Deposit as transaction type, "Tom's" user id, region "EMEA" id.
- The system will aslo create two entries in the Posting table.
- 1st Entry: Journal Transaction Id, Region "EMEA" Account (Allocated Budget) id, amount = 500, posting type = cr.
- 2nd Entry: Same Journal Transaction Id, Account (Cash Book) Id, amount = 500, posting type = dr.
- The system will also check Allocated Budget account nature in our case is (Income Account or Owner Equity) and will do an addition to the account balance in the accounts table to the original value there. (500 + Old Account Balance). (That way we will always have an updated account balance to query about for report generation).
Example Scenario 2:
- FAm "John" submitted a receipt related to a previously approved fund request with a value of $100, He is in "EMEA" region and want to get paid. Withdrawal Transaction.
- In normal paper double entry accounting system we will create one journal entry.
- "EMEA" Region Expense account will be debited by $100 and the Cash Books will be credited by $100
- The system will create a new Journal entry which will save Withdraw as transaction type, "John's" user id, region "EMEA" id.
- The system will also create two entries in the Posting table.
- 1st Entry: Journal Transaction Id, ("EMEA" Region Expense account) id, amount = 100, posting type = dr.
- 2nd Entry: Journal Transaction Id, (Cash Books account) id, amount = 100, posting type = cr
- The system will also check "EMEA" Region Expense account nature in our case is (Expense) and will do a subtraction to the account balance in the accounts table to the original value there. (Old Account Balance - 100) (That way we will always have an updated account balance to query about for report generation).
Fund Requests
Will handle Fund Requests by FAm and handle approval by Budget Owners, receipts attachments. (Was handled by trac tickets in the old system).
- Each fund request will have a status field (0 -> requested, 1 -> approved, 2 -> claimed, 3 -> reimbursed, 4-> disapproved).
- Fund Requests creation by FAm (Status = Requested)
- Fund Requests approval by Budget Owners. (Status = approved or disapproved).
- Receipts or Evidence attachment by FAm. (Status = claimed)
- Budget Owner will reimburse the evidence. (Status = reimbursed).
- Fund Request amount will be issued as a Journal Entry then updating the region account balance.
Notes and Suggested Features:
- Instead of the disapproved status on fund requests we can enable the Eloquent Soft Delete method. (Which is not actually removing the fund request from the database. Instead, a deleted_at timestamp is set on the record, and can be restored later in a breeze)
- FAm will not be able to edit the Fund Request details after Budget Owner changed the status to approved, He will be able to delete it (Soft Delete in our case or we can change the status to cancelled for example).
- Budget Owner(s) will receives an email notification when a new Fund Request is submitted to his/her region.
- FAm will receives an email notification when his/her fund request status changed by Budget Owner (Adminstrator).
Budgeting
A Budgeting Sub Module to handle new year budget planning.
Components
- Budget Proposals & Moderation
- Budget Query Filtering (Type, Amount, Region)
- Budget Allocation & Deficit Reduction
Suggested Senario
- At the end of each year, Fedora Ambassadors are requested to submit next year budget proposals
- After submission budget owners will approve budget proposals.
- Approved proposal will be added to the new year requested region budget amount.
- Total amount of regions will be requested from the sponsor.
- Sponsor will submit an amount to the system
- System will auto calculate the percentage of total amount requested from total amount submitted by sponsor.
- According to the computed percentage, each region will get the corresponding percentage from original amount requested in the allocated budget balance.
- System will auto deduct the unavailable percentages from each budget proposal to reduce deficit.
Note: There are many deficit reduction techniques, I implemented a percentage reduction which is the fair method and the only method I can think about that can be auto applied without any human intervention.
Suggested Merge
The Budgeting system could be merged to the Fund Requests module and extending it's functionality:
- Fund Requests instead of Budget Proposals.
- Will add an additional two status to Fund Requests (suggested & allocated).
- Create the Budget Filtering Interface as an implementation of the report generation REST API described below.
- Use the same backend interface for allocating & managing region budget.
- Deficit Reduction will be auto done by the system on all regions with the same percentage.
Report Generation
Reports Generation Module is needed to generate high level managerial data and account balances. It will consist of two components.
REST API
A basic REST API (JSON Output) for reports generation with FAS authentication integration.
Laravel 4 is also pretty smart when dealing with JSON requests and responses.
- Auto detect requests HTTP Header Content-Type (If text/json will auto parse it, If normal form submissions will deal with it normally to capture the form inputs).
- When returning a Model directly from the controller will auto convert it to JSON object, or Array of JSON object if it's a collection of models.
Authentication
- User will login to our System by FAS credentials (Check if have needed permission to access the report generation api).
- System will send the user an API_Key
- User will use the above API_Key as HTTP Authentication with each API Call.
API Options (Parameters)
- Report Types
- Final Account Balances
- Custom Fields. (If available to report type).
- Date Periods or Ranges
- Specific Users. (Query against specific users)
Less Priority
- Rate Limit, Apply a rate limit on requests per hour.
- Conditional Gets & Reports Caching
Pre-Set Reports
This sub module will implement the REST API to provide the system administrators with a set of pre configured simple and flexible reports which depends on their most frequently use cases.
Suggested Pre-Set Reports
- The below are only suggested, Final List will be decided with mentors before implementation.
Fund Requests Reports
Query Fund requests by Region, Status, Value and Financial Year.
Regions Ledger Report
- Query by Region and Financial Year.
- Shows Allocated Budget
- Shows Remaining Budget
- Shows Total of claimed Fund Requests
Budget Proposals Reports
Query all budget proposals by some attributes:
- Type
- Items
- Region
- Total Value
- Other Attributes if Available.
FAms by Fund Requests Reports
Query all FAms with Fund Requests