Derek Sivers

Entrepreneur, programmer, avid student of life. I make useful things, and share what I learn.

Projects » Beekeep

Double-entry bookkeeping, similar to Gnucash, but built right into a company's database.

No list of customers, sending of invoices, or duplication of anything a company's website already has. Just bookkeeping: a detailed record of financial transactions, which generate financial statements, and corporate records.

Reason/Usage: Web-based companies keep all company knowledge in server-side database, except accounting things like bank and credit card transactions, which are kept in programs like Quickbooks. Beekeep brings that info into the server-side database as well.

Beekeep's tables will be in the same database with a company's usual server-side database, though Beekeep will only deal with the bookkeeping ones, named below. It'll have no knowledge of the others, but of course the main app can use Beekeep's tables directly if needed.

Status: UNSTARTED.

Schema

Assuming one installation of this Rails app per-company, so everything below is assumed to belong to that company.

Each company has multiple beekeeper (admin) users with either read-write or read-only permission.

A company has at least one fact: a simple key-value pair holding anything from tax id to incorporation date to registered address.

The core is the named account: each of type bank, cash, asset, liability, equity, expense, or income.

Every transaction is a txn (shorthand for “transaction”), which just has a date, note, and flag for whether it's balanced, because the core info is in the entry: two per txn, with a from-account and to-account, currency, debit account, credit account, and notes. The entries need to balance. Usually directly, but occasionally, like in currency-exchanging, the numbers won't match up directly, so txn.balanced will be set to yes if it's balanced.

Bank accounts are linked linked by id to a bank.

To import OFX, Direct Connect, or QIF files, the admin downloads it from their bank and uploads it here, which creates an import_file, tied to a bank_id and account_id, and the software should import it into that account.

Part of bookkeeping is corporate minutes, so each company can enter a meeting, which is just a dated note, a reminder, which is a future-dated note to be emailed to an admin, or a reference_file, which might be a document like articles of incorporation, share certificates, or whatever.

Site

The role model is GnuCash: a nice free accounting app you should install if possible. We should imitate their accounts and transactions almost exactly.

Quite unusually, a company's books should be world-readable, minus only the data that affects others (like customer names). Part of the inspiration for doing this, instead of using an existing online accounting package, is to be world-auditable, so clients and customers can see a company's entire transparent accounting.

The site will generate the four main financial statements, formatted for easy printing.

REST API

URI GET POST PUT DELETE
/beekeepers beekeepers (admins) for these books person_id + permission to add admin . .
/beekeepers/1 info about this beekeeper / linked person info . person_id, permissions to change delete beekeeper
/facts facts for company key+value to add new fact . .
/facts/2 fact info . update fact delete fact
/accounts list of accounts for company name+accttype to add new account . .
/accounts/2 account info . update account delete account
/accounts/2/entries entries from or to this account . . .
/entries/2 entry info including linked txn + account info . update entry delete entry
/txns transactions for company matching entries to create new transaction+entries . .
/txns/2 transaction info including entries entry info to add to this transaction update transaction delete transaction
/txns/2008-07/2010-07 transactions during this date range . . .
/txns/2010-07 transactions during this month . . .
/txns/2010 transactions during this year . . .
/reminders reminders for company name, send_on, email_to, message to add new reminder . .
/reminders/2 reminder info . update reminder delete reminder
/meetings meetings for company created_at, notes to add new meeting . .
/meetings/2 meeting info . update meeting delete meeting
/import_files import_files for company bank_id, filename, md5 to add new import_file . .
/import_files/2 import_file info . update import_file delete import_file
/reference_files reference_files for company filename, md5 to add new reference_file . .
/reference_files/2 reference_file info . update reference_file delete reference_file
/banks list of all banks name to add new bank . .
/banks/2 bank info . update bank delete bank
/banks/2/accounts accounts from this bank . . .
/banks/2/import_files import_files for this bank . . .
/ snapshot of all info (see above) for company, linked to each, for API discovery .

Requirements

dodetails
install dev requirementsinstall postgresql, ruby19, pgcrypto, rails3 & do your own tests to make sure they're installed
create postgresql databasecreateuser beekeep ; createdb -O beekeep -E UTF8 beekeep & load in schema
create people databasecreateuser people ; createdb -O people -E UTF8 people & load in schema & fixtures
test on pgsql command-linemake sure both are there. load in fixtures.pgsql if you want to test queries.
git clonegit clone http://github.com/thoughts/beekeep - Rails3 project with only models & schema
acts_as_personA plugin for Person class - we could use on any model (in any Rails site) whose table has a person_id field, so the '.person' method would pull up the Person model - which connects to the people database, and does "SELECT * FROM persons WHERE id={person_id}", returning a Person. Is a shared plugin the best way to do it?
create plugin
test that it works with different projects
write README on how to integrate into any project
upload to Github
acts_as_moneyA plugin for Money class - as described here: http://martinfowler.com/eaaCatalog/money.html
Database stores millicents (1/100th of a cent) + 3-letter currency code.
See if this could adapt for our millicents. http://github.com/aflatter/money
Should be able to make any model acts_as_money to get "currency", and "millicents" to make money.
Though fieldname needs to be editable. 'millicents' is default.
Currency classfor each currency, knows conversion rates, code, full name, sprintf number formatting.
http://www.oanda.com/convert/fxdaily easy to bring new numbers into Currency class
porting functions from Money.php + Currency.phpsee those two PHP classes to understand. what functions to keep/ditch/refactor?
Currency classfunction __construct($code) creates Currency object with code if valid. throws error if invalid.
function multiply_to_get($new_code)used for making new Money object
static function is_valid($code)boolean: is this 3-letter code known & valid?
Money class __construct($millicents, $code)to create new Money object
function show_no_code()to display "$13.52" or "€12" but no "USD" or "EUR"
function show_with_code()same but add 3-letter code
function show_with_name()same but full name instead of code "U.S. Dollars", "Brazilian Real"
function code()return just 3-letter code
function amount()return just float, rounded to 2 decimals: 13.52
function times($float)return new Money object, multiplying millicents by this number
function plus(Money $m)add two money objects, return in currency of the first
function minus(Money $m)subtract 2nd money object from first, keep first's currency
function equals(Money $m)boolean: 2 moneys have equal amounts? (less than 100 millicents)
function converted_to($new_code)return new Money converted to new currency
static function from_float($float, $code)create new Money from float (13.52, 'EUR')
static function sum($array_of_money)input array of money objects, return new money of sum, in currency of first
account.rb
account.rb test belongs_to :bank
look at entry.rb from_account and to_accountsee how Account model should join these entries (together or separate)
account.rb test has_many :entries for both(whatever you came up with)
account.rb validationsname is not null. name is unique.
accttype is in list
bank_id references banks
beekeeper.rb
beekeeper.rb test .personshould pull up related Person from people database
beekeeper.rb validationsperson_id is unique. permissions can only be 'read-write' or 'read-only'
bank.rb
bank.rb test has_many :accounts
bank.rb test has_many :import_files
bank.rb validate: name is unique
entry.rb
entry.rb join accounttwice: as to_account using account_id and from_account using from_account_id
entry.rb test belongs_to :txn
entry.rb test belongs_to both account-joins
entry.rb validations:not null & references exist for: txn_id, account_id, from_account_id
fact.rb
fact.rb validations:not null not empty: factkey and factvalue
import_file.rb
import_file.rb test belongs_to :bank
import_file.rb validations:not null & references exist for :bank
file_exists? and md5 matches database+file
method to download/deliver file itself
meeting.rb
meeting.rb validations:not null not empty: notes
reference_file.rb
reference_file.rb validations:file_exists? and md5 matches database+file
method to download/deliver file itself
reminder.rbset up email_to as a belongs_to :beekeeper key
reminder.rb method to mark as completesets today's date in completed_at. this is clicked by user, not cron job.
reminder.rb method to send today'sif today's date == send_on then send message to email_to.beekeeper.person.email.
reminder.rb cronjobshell script to use model's send_todays. able to be run by OS cron
txn.rbunderstand a transaction is a balanced combination of two or more entries (1 debit 1 credit)
txn.rb test has_many :entries
txn.rb method is_balanced?this wouldn't need to exist but for one situation: currency exchange. $100 USD leaves one account, £66.81 GBP hits another. the bank used their exchange rate which is slightly different than ours, but still this transaction needs to be marked as balanced if a person says so. So it may be an enhanced-boolean response: TRUE if exact match, FALSE if not even close, or FOREX/MAYBE if a person needs to verify.
Beekeeper REST controllernormal REST. on GET, bring in name and email from Person.
Fact REST controllernormal REST
Account REST controllernormal REST plus next feature...
/entries parameter on Account REST/accounts/2/entries should do SELECT * FROM entries WHERE account_id=2 OR from_account_id=2 ORDER BY id DESC
Entries REST controllernormal REST. I guess /entries could show all entries (newest first) even though that's not very useful, it is the norm.
include linked txn_id and account id+name
Txns REST controllernormal REST plus next few search features...
/yyyy parameter on Txns REST/txns/2010 should show all Txns in 2010
/yyyy-mm parameter on Txns REST/txns/2010-03 should show all Txns in March 2010
/yyyy-mm/yyyy-mm parameter on Txns REST/txns/2008-11/2010-03 should show all Txns from 2008-11-01 through 2010-03-31
see http://www.postgresql.org/docs/8.4/static/functions-datetime.html
Reminders REST controllernormal REST controller
Meetings REST controllernormal REST controller
ImportFiles REST controllernormal REST controller
ReferenceFiles REST controllernormal REST controller
Banks REST controllernormal REST controller plus next few features...
/accounts parameter on Banks REST/banks/2/accounts should do SELECT * FROM accounts WHERE bank_id=2
/import_files parameter on Banks REST/banks/2/import_files should do SELECT * FROM import_files WHERE bank_id=2
home/index controllercompany overview, with linked list of all accounts, facts, most recent txns, reminders, meetings, and reference_files. No need for banks or import_files.
basic working RESTno import of bank files yet
bank file importbig one. we need to talk about this. version 2.0 kind of thing, but crucial.

Database Schema

BEGIN;

CREATE SCHEMA beekeep;
SET search_path = beekeep;

CREATE TABLE beekeepers (
	id serial primary key,
	person_id integer not null unique,
	permissions varchar(10) not null CHECK (permissions IN ('read-only', 'read-write'))
);

CREATE TABLE facts (
	id serial primary key,
	factkey varchar(32),
	factvalue text
);
CREATE INDEX fctky ON facts(factkey);

-- accounting:

CREATE TABLE banks (
	id serial primary key,
	name varchar(64) not null unique
);

CREATE TABLE accounts (
	id serial primary key,
	name varchar(64) not null unique,
	accttype varchar(9) not null CHECK (accttype IN ('bank', 'cash', 'asset', 'liability', 'equity', 'expense', 'income')),
	bank_id integer references banks,
	notes text
);

CREATE TABLE txns (
	id serial primary key,
	created_at date not null,
	balanced boolean not null default false,
	notes text
);

CREATE TABLE entries (
	id serial primary key,
	txn_id integer not null references txns,
	account_id integer not null references accounts,
	from_account_id integer references accounts,  -- null if split 
	currency char(3) not null default 'USD',
	debit numeric,
	credit numeric,
	name varchar(255),
	ref varchar(32),  -- check no, paypal transaction id
	notes text
);
CREATE INDEX entxn ON entries(txn_id);
CREATE INDEX entai ON entries(account_id);
CREATE INDEX entfi ON entries(from_account_id);

--- non-accounting:

CREATE TABLE reminders (
	id serial primary key,
	name text,
	send_on date not null,
	email_to integer not null references beekeepers,
	message text,
	completed_at date
);

CREATE TABLE meetings (
	id serial primary key,
	created_at date,
	notes text
);

CREATE TABLE import_files (
	id serial primary key,
	bank_id integer not null references banks,
	created_at date not null,
	completed_at date,
	filename varchar(127),
	md5 char(32)
);

CREATE TABLE reference_files (
	id serial primary key,
	created_at date not null,
	filename varchar(127),
	md5 char(32)
);

COMMIT;

Afterthoughts:

2010-03-11

QUESTION: SHOULD THIS BE A WEBSERVICE OR NOT?

At first I thought this should be a webservice because all of its bookkeeping features would be the same across many different companies. So why repeat?

But that logic was flawed. If this was a Rails project that accessed only a few tables right inside a company's existing database, then each company could also do good things to join up queries with invoices or payments, for example, into accounts, txns, entries, facts, reminders.

Two Rails projects. One database.
One Rails app does the stuff that users and admins need to see.
One Rails app does just the corporate accounting/bookkeeping that maybe only the CEO and accountant need to see.

Rename admins to beekeepers - so as not to clash with other tables.

It can still be a REST service, though there might be no reason why, unless it's just future iPhone/Android apps or something.