README

TreasureDB is a plain SQLite database application to observe and trace the finances of a non-profit registered society or club. The ideal state of balance is zero both in debit and in credit because they even out. In Germany, such clubs may not accumulate reserves.

Basics

As it is a SQLite3 database with all consistency and calculatory logic built in, you only need the sqlite3 binary or any sqlite3 GUI software to run it. Please note the tools beyond that are not yet portable. A part of them require the bash and a unix/linux system.

1. Ensure sqlite3 is installed

2. Run the test suite: bash t/schema.sh (on Linux and unix systems with Bash installed)

    # Or go by foot and test manually a little bit further
    treasuredb $ export TRSRDB_SQLITE_FILE="..." # wherever you want to save it
    treasuredb $ cat schema/{tables,*/*}.sql | sqlite3 $TRSRDB_SQLITE_FILE
    treasuredb $ sqlite3 $TRSRDB_SQLITE_FILE
    SQLite version 3.8.7.1 2014-10-29 13:59:56
    Enter ".help" for usage hints.
    sqlite> .read t/schema.sql
    ...
   

3. Use it:

export TRSRDB_SQLITE_FILE="..." # wherever you want to save it 
cat schema/{tables,*/*}.sql | sqlite3 $TRSRDB_SQLITE_FILE
./trsr $COMMAND

$COMMAND can be one of the following:

* `charge` - input a batch of csv lines representing incoming and outgoing payments
* `ct` - charge, then make transfer (interactive assistent)
* `cts` - like ct, but output finally status of all
* `ctr` - like ct, but output finally reports of all
* `report` - get only reports
* `sql` - execute sqlite3 shell with all necessary pragmata and with line output
* `status` - get only statuses
* `transfer` - only make transfers (interactive assistent)
* `tr` - transfer, and output report
* `ts` - transfer, and output statuses

Input to ./trsr charge

You can separate the fields of each line by a comma and optional whitespace, or at least one whitespace. The order of columns is:

• The booking date in format YYYY-MM-DD,
• The account name,
• The value if it is a debit,
• The value if it is a credit,
• The purpose (in the case of a debit, it must start with bill ID and colon),
• Optional: "<< Comma-separated list of credit IDs used to pay the debt" or ">> Comma-separated list of bill IDs the booked income is used for", respectively

Multi-line purposes

Multi-line purposes must either be surrounded by " (escape literal " by doubling it), or started in the next line and terminated with an empty one.

How to book an incoming payment

In the credit column must be a value greater than 0. Cent must be passed as a decimal part, i.e. "100" really mean 100.00, not 1.00! In the debit column you MAY input a name starting with a letter (otherwise it needs to be just "+"), by which you can refer to a credit in lines below instead of the number.

How to enter a target credit

A target credit is defined as initially equal 0.

How to make an internal debt

A debit, recognized by an amount in the debit column, can have a target credit name or number in the credit column.

How to book an outgoing payment

An outgoing payment must not have a target credit. Leave that field empty.

HTTP interface

Treasure DB includes a rudimentary HTTP interface. It requires Mojolicious. The interface is developped and tested on version 7.13 onwards.

You can start it with ./trsr server. It will stay in the foreground. To start it in daemon mode, use ./trsr server >/tmp/trsr_db.log 2>&1 & disown in the bash shell.

After first start, you will need to create a user: ./httpuser -a $username -g 2. The link that will be output leads you to an extended login form. Please enter a (good) password twice, so typos are unlikely. The -g (or --grade) argument can be 0, 1 or 2:

• 2 is admin who has complete read-write access.
• 1 means auditor with complete view without writing permission.
• 0 means auditor who can view only the type-less accounts, e.g. the club account is recommended to be without type, or(!) an account named identically.

How does it work?

There are four tables: Account, Credit, Debit and Transfers.

Account table stores the static details, i.e. the type of account, the record identifier for another table that stores more information about the account owner, and the IBAN for outgoing bank transfers.

The Credit table stores bank transfers from the member to the club, and also the target records of internal transfers to the club and other members performing commercial services for it. The value of the former sort is initially >0 and should not be altered afterwards. The latter's value always starts at 0 and is increased automatically by transfers linked to the according debts.

The Debit table stores the receipts documenting a debt, their link to the credit record of the respective recipient as well as to the account from which it is paid.

The Transfer stores when which amount is transferred from which credit record to settle which debt. So, a debt can be associated with n source credits, and a souce credit can settle multiple debts. Transfers cannot be altered once inserted, they need to be revoked and re-entered. This is due to triggers that have to rebalance the involved accounts. Whenever the view Balance contains positive values for both credit and debt in a line, this indicates transfers yet to be entered.

Using the sqlite3 database purposefully means two things:

1. Keep ReconstructedBankStatement view in sync with the actual statements received from your bank by entering the incoming and outcoming bank transfers in the Credit or Debit table, respectively, best as soon as they show up.

2. Ideally, all records in the Balance view must be 0 in each of debit, credit and promised columns. If neither debit nor credit is zero in the same record, you need to make internal transfers linking records from the CurrentDebts and from the AvailableCredits tables. Otherwise, ensure that members make the transfers they are obligated to and return any money that is paid too much unless the indicated purposes, if any, also refer to dues in certain future.

License

Copyright (c) 2016, Florian Heß. All rights reserved.

See details in LICENSE file containing the General Public license, version 3.