The Lunenburg Budget Project·The money →

Lunenburg Budget Project · Reference

The town’s ledger — structure, naming, and every trap in it

This document is written and generated as Markdown. It is shown below exactly as the file reads — nothing has re-rendered it — so what you see is character for character what you get from /reference/LEDGER-STRUCTURE.md, which is byte-identical to notes/reference/LEDGER-STRUCTURE.md in the repository.

# The town’s ledger — structure, naming, and every trap in it

**Generated by `scripts/build_ledger_structure.py`. Do not edit.**

`SCHEMA.md` documents *our* database. This documents the *town’s* data as it arrives: how an account number is built, what a fund number means, what the columns that look alike actually distinguish, and above all what the names do not say.

> **The rule underneath all of it: never identify a thing by its name in this ledger.** Identify it by account number, fund number or department — those are assigned and stable. The name is a ten-character abbreviation typed by a person.


## 1. The shape of an account number

| shape | count | what it is |
|---|---:|---|
| `NNNN-NNNNN-NNNNNN` | 636 | fund–org–object, an ordinary detail account |
| `NNNN-SNNNNNNN-NNNNNN` | 258 | fund–org–object where the org is **S-prefixed** |
| `NNNN-NNN` | 69 | fund–department — a **roll-up** row, not a detail account |
| `NNNN-NNNNNNNN-NNNNNN` | 10 | fund–org–object, an ordinary detail account |
| `NNNN-NNNN-NNNNNN` | 8 | fund–org–object, an ordinary detail account |

**The `S` prefix means school.** 258 accounts carry it and all of them are department 300; department 300 has 258 detail accounts and 0 of them lack the prefix. It is an exact match in both directions, which makes `account_id LIKE '0100-S%'` a **structural** way to find school spending — one that does not depend on any name.


## 2. Two grains of the same money, told apart only by period

| period | grain | rows | total |
|---:|---|---:|---:|
| 9 | department | 67 | $51,189,961 |
| 12 | account | 635 | $51,189,965 |

The same general fund, twice, $4 apart. They are **different MUNIS reports**, and `period` is the only column that distinguishes them — so a query without it adds one report to the other.


## 3. Three columns that look optional and are not

| column | if you omit it |
|---|---|
| `level` | department roll-ups are summed **on top of their own detail** |
| `account_type` | revenue is stored **negative** and nets against expense — this is how the town’s whole budget once computed as **minus $997,871** |
| `period` | two different reports are added together (see above) |

This is the same rule that governs `status` on the annual-report extracts and `v1` on the page tables: **a column that says what a row IS cannot be skipped because the rows look alike.** It has now been got wrong on three unrelated datasets.


## 4. Fund numbers, by range

| range | funds | example |
|---|---:|---|
| `01xx` | 1 | GENERAL FUND |
| `13xx` | 13 | ADULT EDUCATION REVOLVING FUND |
| `15xx` | 1 | TECHNOLOGY FOR SCHOOL CHILDREN |
| `22xx` | 1 | SCHOOL LUNCH REVOLVING |
| `25xx` | 1 | EECBG ENERGY EFFICIENCY GRANT |
| `26xx` | 9 | COMP SCHOOL HEALTH SERV GRANT |
| `27xx` | 27 | FY12  REG DISSEMINATION GR#321 |
| `28xx` | 5 | FY25 #240 |
| `29xx` | 4 | BC/BS MINI GRANT |
| `50xx` | 1 | SEWER BETTERMENTS |
| `51xx` | 1 | WATER BETTERMENT FUND |
| `60xx` | 1 | SEWER ENTERPRISE FUND |
| `61xx` | 1 | WATER ENTERPRISE FUND |
| `62xx` | 1 | PEG ACCESS ENTERPRISE FUND |
| `79xx` | 1 | SOLID WASTE/RECYCLING ENTERPRI |

School money is spread across `13xx` (revolving), `15xx`, `22xx` (lunch) and `26xx`–`29xx` (grants). **There is no single range that means "school"**, which is why the node list enumerates funds by number rather than filtering on a name.


## 5. The ten-character ceiling

The longest account name in the ledger is **10 characters**, and 574 accounts sit exactly at it. The district’s own budget book runs to 48. MUNIS truncates hard, and the casualties are readable in the data: `COUNT RET`, `SCHRETHLTH`, `PS TEACH R`, `PRE-SCHOOL T UIT ION` once OCR gets involved.

Two consequences that have already bitten:

- **Names collide.** `MS GUIDANC` and `HS GUIDANC` are distinct rows in the district’s book and the same ten characters minus one in the ledger. This is why `crosswalk` is deliberately empty: the function code joins a **category**, never a line.

- **A name cannot be searched reliably.** Anything that matters must be found by number.


## 6. The decoder — what a name turned out to mean, and how we know

| name | where | what it actually is | how that was established |
|---|---|---|---|
| `CHAPTER 658 REVOLVING FUND` | fund 1301 | The athletics revolving fund | Its cash journal comments read `CHAPTER 658 ATHLET` and it issues refunds to families by name. |
| `WRRS ASSESSMENT` | dept 820 | Worcester Regional Retirement System — the town’s pension assessment | The only account under it is `COUNT[Y] RET`; the FY2019 annual report’s budget narrative prints *"Worcester Regional Retirement ($1,354,353)"* as an expenditure category. |
| `SCHRETHLTH` | `0100-19142-570018` | School retiree health insurance | Sits under dept 914 INSURANCE beside `TNRETHLTHI` (town retiree health) and `HEALTH INS` (active town employees). |
| `SCHCOSTREI` | general fund revenue | Special education circuit breaker reimbursement | Same magnitude as fund 2640 `SPECIAL ED CIRCUIT BREAKER`. **Whether these are the same money booked twice or two different flows is NOT established.** |
| `FBCYBUDGET` | general fund revenue | Free cash and transfers in | Ranked with the other financing sources. |
| `SCHRESSTIP` | `0100-12101-519021` | A school-related stipend inside the police department | **Inferred from the abbreviation.** The amount is immaterial and the expansion is a guess, marked as one. |
| `MTGS/SCHOO` | several departments | Meeting expenses, at a school — not school spending | Under Fire, Land Use, Finance Committee, Town Clerk and others, at $500–$5,050. A name match on "SCHOO" pulls these in and they are not school money. |

## 7. Things the structure hides completely

- **A grant fund can spend without booking revenue in the same year.** Nine did in FY2026. Any query that finds school money by looking at revenue misses every one of them, and they are real spending.

- **A department is not a purpose.** School retiree health is in the insurance department; a school resource stipend is in the police department; meeting expenses *at* a school are in five departments and are not school money at all.

- **Function codes outrun their names.** The district’s budget book names 45 of them. The ledger uses more. A code the book does not name is shown here by the accounts inside it rather than named from the DESE chart of accounts, because naming it from general knowledge would be a derived thing quoted as an observed one.