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

Requirements

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