Building Committee: Collections, Vendors, and Resident Requests in an Auditable System

A building committee changes hands, and the only thing that carries over is the record. A ledger that can be edited is not a record, it is a current version, and nothing about last year can be proved from it. What follows is a technical description of a data structure for collections, vendor payments and resident requests, not legal, accounting or financial advice.

The row that is never edited

An entry in a committee ledger carries seven fields, all written once:

• entry_id - a stable running identifier that never changes.
• unit_id - the apartment. For a shared expense it is empty and an expense_category stands in its place.
• entry_type - charge, receipt, expense, reversal.
• amount and currency - a signed amount, so a plain sum produces a balance.
• effective_date - the date the movement belongs to, for instance the month collected.
• recorded_at and recorded_by - when and by whom it was written. Two fields distinct from effective_date.
• document_ref - a pointer to the evidence: a receipt, a vendor invoice, a transfer confirmation.
• reverses_entry_id - populated only on reversal rows.

Pairing effective_date with recorded_at is what makes two different questions answerable: what was the balance at the end of March, and what did we know about March's balance at the time. A committee handing over to the next one owes both. The principle of a record that accumulates rather than being overwritten is set out in Martin Fowler on Event Sourcing.

Technical enforcement beats policy: a trigger blocking UPDATE and DELETE on the entries table turns the rule into something nobody can forget, and that syntax is documented in SQLite CREATE TRIGGER.

Why is the unit the account rather than the person?

Because people change and the unit does not. A tenant leaving mid-year, an owner selling, an heir moving in - each changes who pays and none of them changes which unit the charge belongs to.

A structure that holds up:

Pairing a unit with a holder's date range is what answers "who owed in May". In a spreadsheet where the resident's name is the key, replacing a tenant either erases the previous one's history or assigns them debt that is not theirs. The same identity problem in a commercial context is discussed in the accounts-receivable collection workflow.

Three balances that must agree

One balance is not enough to know the books are sound. Three checked against one another are:

The monthly check is not looking for confirmation that everything is fine. It is looking for the rows that do not agree, and in most months there are two or three. A receipt landing in the bank with no identifiable reference is the common one, and it should sit in an "unassigned" state until somebody identifies it rather than being attributed to a unit on the strength of a similar amount.

Vendor payments: four documents, not four decisions

Paying a vendor in a building passes through four stages, each with a document and a date:

1. Quote - amount, scope of work, validity. Kept even when it was not selected.
2. Approval - who approved and when, against that specific quote.
3. Invoice - arrives after the work, linked to the approved quote.
4. Payment - linked to the invoice, with a transfer reference.

The stage that actually disappears is the first: unsuccessful quotes get deleted, and then there is no way to show that three quotes existed. A quote that was not chosen is exactly as valuable a document as the one that was. The approval-to-payment process itself, in its commercial form, is unpacked in supplier invoice approval through to payment.

How do you prove a resident paid two years ago?

In three parts, all present from day one rather than assembled retroactively.

The first is the receipt row itself, never edited, with its effective_date and recorded_at. The second is the document_ref pointing at the receipt or transfer confirmation as stored. The third is the ability to regenerate that month's report and get exactly the same figures.

The third point is what separates a system from a spreadsheet. If the report is produced from the entries rather than saved as a final file, regenerating it two years later returns an identical result as long as no row was deleted. If the report exists only as a saved file, there is no way to tell whether it matches the ledger. Keep both: the file as circulated, and the ability to produce it again.

Resident requests: a case, not an entry

A report of a leak or a broken light is not money, so it does not belong in the ledger. It is a case with fields of its own:

• unit_id or a common area, one of the two.
• reported_at and who reported it.
• category from a closed list: plumbing, electrical, lift, cleaning, other.
• vendor_id once it has been referred out.
• status from opened, referred, done, closed.
• linked_entries[] - pointers to any money movements it produced.

That last field is the bridge. It answers what lift maintenance cost this year without mixing requests into the charge ledger. The collection channels through which receipts arrive in the first place are discussed in business collection through Bit and PayBox.

What actually breaks

• A correction made as an edit. A mistyped amount is fixed in place, and last month's report stops matching the one that was circulated.
• A receipt with no unit. A transfer arriving with no reference counts as income and closes nobody's debt.
• A resident name as the key. Two residents sharing a surname in one building is enough to merge two histories.
• An invoice approved and never paid. With no vendor liability balance it simply disappears until the vendor calls.
• Cash recorded with no evidence. A row with no document cannot be proved, even when it is correct.
• A handover with no export. An outgoing committee passing on one file rather than the system passes on a snapshot, not a history.

Sources

• Martin Fowler - Event Sourcing
• SQLite - CREATE TRIGGER
• Masav - the Israeli banking clearing centre