# CSKE Town Accounting Classification Schema and Query Guide **Guide version:** v0.1 **Date:** 2026-08-11 **Schema package documented:** `CSKE-Town-Accounting-CSKE-Reporting-Classification-v0.2-20260810.zip` **Migration:** `0014_TOWN_ACCT_CSKE_REPORTING_CLASSIFICATION_V02` ## 1. Purpose and current status The classification schema adds several independent CSKE analytical lenses to the source-authoritative FY2027 Town accounting expense-control data. It is designed to answer different questions without forcing every dollar into one overloaded hierarchy: - Which Town department or source cost center contains the budget? - What public service or organizational purpose does it support? - What is being purchased: salary, benefits, facilities, technology, debt, or another cost type? - What kind of fund or financing mechanism contains it? - Can an expense be attributed to a municipal facility? - Is ledger placement potentially misleading because a cost is centralized? - What appears to drive the cost, and what evidence is still missing? - What legal, contractual, operational, timing, and policy constraints affect the practical ability to change it? The v0.2 package has been prepared and statically validated, but at the time of this guide it has **not yet been executed against PostgreSQL**. The views and queries below become available only after: ```text Run-CSKE-Town-Accounting-CSKE-Classification-v0.2.command ``` finishes with `OVERALL PASS`. ## 2. Non-negotiable provenance boundary Town codes and labels remain exactly as reported by the Town. CSKE does not rename or overwrite them. Town source fields generally end in `_raw`, for example: ```text fund_code_raw fund_name_raw department_code_raw department_name_raw object_code_raw account_description_raw full_account_code_raw ``` CSKE analytical fields begin with `cske_`, for example: ```text cske_reporting_category_code cske_expense_group_code cske_facility_code cske_spending_flexibility_code ``` Every CSKE reference or mapping includes or inherits the controls: ```sql classification_origin = 'CSKE' town_source_authoritative_flag = false ``` Database constraints prevent CSKE labels from being represented as Town-authoritative classifications. Report headings should use language such as: > CSKE analytical classification — not a Town of Wilbraham accounting classification. ## 3. Source accounting layer retained underneath The classification schema builds on, but does not replace, the existing Town accounting objects: | Source object | Purpose | |---|---| | `cske.town_acct_code_set` | Identifies the FY2027 Town account-code system. | | `cske.town_acct_fund` | Town fund codes and raw fund names. | | `cske.town_acct_department` | Town department codes and raw department names. | | `cske.town_acct_object` | Town object codes and first-observed raw descriptions. | | `cske.town_acct_detail_code` | Town detail-code component. | | `cske.town_acct_account` | Full fund-department-object-detail account string. | | `cske.town_acct_report` | Source report identity and reporting period. | | `cske.town_acct_report_line` | Source line and page provenance. | | `cske.town_acct_expense_control_fact` | FY2027 budget, actual, and balance amounts. | | `cske.v_town_acct_expense_control_detail` | Source-detail reporting view used by the classification views. | The controlling FY2027 report is: ```text WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709 ``` It contains 473 detail accounts, 48 populated departments, 14 funds, and 101 object codes. ## 4. Classification architecture ### 4.1 Department purpose and organizational structure Reference tables: ```text cske.town_acct_ref_cske_reporting_category cske.town_acct_ref_cske_reporting_subcategory cske.town_acct_ref_cske_organizational_type ``` Mapping table: ```text cske.town_acct_cske_department_classification ``` Important keys and controls: ```text PK: town_acct_cske_department_classification_id FK: town_acct_department_id AK: town_acct_department_id + effective_fiscal_year FK: cske_reporting_category_code FK: cske_reporting_subcategory_code FK: cske_organizational_type_code ``` The 13 primary categories are: 1. Education 2. Public Safety and Emergency Services 3. Public Health, Protection, and Inspection 4. Public Works, Utilities, and Environmental Services 5. Community and Resident Services 6. Planning, Conservation, and Community Character 7. Governance, Elections, and Civic Administration 8. Financial Administration and Revenue 9. Internal Administration and Support 10. Facilities and Municipal Asset Stewardship 11. Employee Benefits 12. Risk Management and Required Financial Protections 13. Debt Service and Financing Costs The mapping also stores people-facing and enterprise-operation indicators, classification status, basis, confidence, follow-up question, source locator, review information, and effective fiscal year. ### 4.2 Fund family and financing mechanism Reference tables: ```text cske.town_acct_ref_cske_fund_family cske.town_acct_ref_cske_financing_mechanism ``` Mapping table: ```text cske.town_acct_cske_fund_classification ``` The current fund families are: ```text GENERAL_OPERATING SPECIAL_ARTICLE REVOLVING RECEIPTS_RESERVED COMMUNITY_PRESERVATION ENTERPRISE ``` Fund classification also identifies enterprise funds and funds that appear restricted or dedicated. These fields do **not** prove the ultimate source, legal restriction, recurrence, or availability of every dollar. Those require revenue, grant, warrant, reserve, or legal evidence. ### 4.3 Expense group and expense type Reference tables: ```text cske.town_acct_ref_cske_expense_group cske.town_acct_ref_cske_expense_type ``` Default object mapping: ```text cske.town_acct_cske_object_classification ``` Account-specific override: ```text cske.town_acct_cske_account_expense_override ``` The schema uses a two-level hierarchy: ```text Expense group -> Expense type ``` Examples: | Expense group | Expense types | |---|---| | Personnel | Regular salary; temporary/part-time; overtime/premium; employee benefits | | Facilities | Facility operations; maintenance and repair; capital renewal | | Technology, equipment, and materials | Technology; equipment and vehicles; supplies and materials | | Services and administration | Professional/contracted services; administrative operations | | Programs and aid | Direct program delivery; aid, assessments, and transfers | | Risk | Insurance, surety, security, and financial protection | | Capital | Capital projects and major acquisitions | | Debt | Principal; interest; other financing costs | | Reserve | Central salary pool; reserve and contingency | | Other | Unresolved or mixed operating cost | All 101 loaded object codes receive exactly one default expense type. An account override takes precedence where an object code is too general. For example, the two Memorial School heating-upgrade accounts use the generic Town object `5580`, but the explicit account descriptions support a CSKE `CAPITAL_PROJECT` override. Object classification also supplies these analytical flags: ```text cske_personnel_cost_flag cske_facility_related_flag cske_capital_cost_flag cske_debt_financing_flag cske_recurrence_assessment ``` ### 4.4 Facility and property attribution Reference table: ```text cske.town_acct_ref_cske_facility ``` Allocation table: ```text cske.town_acct_cske_account_facility_map ``` This is a many-to-many analytical allocation table. Its alternate key is: ```text town_acct_account_id + effective_fiscal_year + cske_facility_code ``` Important fields include: ```text cske_allocation_percent cske_allocation_method cske_direct_or_shared_code cske_owning_department_code_raw cske_occupying_department_code_raw cske_paying_department_code_raw cske_mapping_basis cske_confidence_code supporting_source_locator authoritative_confirmation_needed_flag ``` The official account and expense are stored only once. The allocation table does not create another official expense. Controls prevent the facility allocations for an account-year from exceeding 100%. The initial evidence-backed mappings are: | CSKE facility | Current basis | Accounts | |---|---|---:| | Memorial School | Explicit `MEMORIAL` account descriptions | 3 | | Town Hall | Town department 199 `TOWN HALL` | 7 | | Little Red Schoolhouse | User-confirmed department 690 Public Access context | 6 | Unallocated centralized facility costs remain visible as evidence gaps rather than being forced into an unsupported building allocation. ### 4.5 Spending flexibility Reference tables: ```text cske.town_acct_ref_cske_obligation_basis cske.town_acct_ref_cske_flexibility_rating cske.town_acct_ref_cske_change_horizon cske.town_acct_ref_cske_reduction_consequence ``` Assessment table: ```text cske.town_acct_cske_account_spending_flexibility ``` The schema deliberately separates four questions: 1. **Obligation basis:** Why might the expense be constrained? 2. **Flexibility rating:** How much practical choice appears to exist? 3. **Change horizon:** When could a change realistically take effect? 4. **Reduction consequence:** What might happen if it is reduced? All 473 accounts start with all four dimensions set to `UNASSESSED`. An account name or department code alone is not enough to declare an expense mandatory or discretionary. An eventual evidence-supported classification might look like: ```text Obligation basis: COLLECTIVE_BARGAINING Flexibility rating: COMMITTED_FIXED Change horizon: NEXT_CONTRACT_CYCLE Reduction consequence: SERVICE_REDUCTION Assessment status: EVIDENCE_VERIFIED ``` ### 4.6 Cost driver and controllability Reference tables: ```text cske.town_acct_ref_cske_cost_driver cske.town_acct_ref_cske_controllability ``` Assessment table: ```text cske.town_acct_cske_account_cost_driver ``` Cost driver asks what principally causes the cost—for example headcount, building energy, facility condition, fleet size, service volume, regional formula, or debt schedule. Controllability separately asks what the Town can influence. Only source-obvious cost drivers are initialized provisionally. Controllability remains unassessed because the expense-control report cannot establish whether the Town controls price, quantity, both, or neither. ### 4.7 Accounting presentation and centralization Reference table: ```text cske.town_acct_ref_cske_accounting_presentation ``` Mapping table: ```text cske.town_acct_cske_accounting_presentation_map ``` This identifies ledger placement that might mislead a reader: ```text CENTRAL_SALARY_POOL CENTRAL_BENEFITS CENTRAL_INSURANCE CENTRAL_FACILITIES CENTRAL_IT REGIONAL_ASSESSMENT PASS_THROUGH_TRANSFER RESERVE_CONTINGENCY SOURCE_DEPARTMENT_COST_CENTER UNKNOWN ``` For example, the $471,062 FY2027 salary pool remains in Town department 122 `SELECT BOARD`, while the CSKE presentation classification identifies it as a central salary pool whose ultimate beneficiaries are not yet allocated. ### 4.8 Evidence and confidence Reference table: ```text cske.town_acct_ref_cske_confidence ``` Supported confidence codes are: ```text UNASSESSED LOW MODERATE HIGH ``` Consequential mappings support fields such as: ```text cske_classification_status cske_classification_basis cske_confidence_code supporting_source_artifact_id supporting_source_locator cske_reviewed_by cske_reviewed_at cske_follow_up_question authoritative_confirmation_needed_flag cske_mapping_version ``` This allows reports to distinguish a direct source match from a CSKE working inference or an unresolved question. ## 5. Reporting views | View | Use | |---|---| | `cske.v_town_acct_expense_control_cske_classified_detail` | One row per official Town account with all principal CSKE dimensions attached. | | `cske.v_town_acct_expense_control_cske_category` | Totals by CSKE service-purpose category. | | `cske.v_town_acct_expense_control_cske_expense_type` | Totals by expense group and detailed expense type. | | `cske.v_town_acct_expense_control_cske_fund_family` | Totals by CSKE fund family. | | `cske.v_town_acct_expense_control_cske_facility` | Percentage-allocated totals by mapped facility. | | `cske.v_town_acct_expense_control_cske_presentation` | Totals by centralized or source-ledger presentation type. | | `cske.v_town_acct_expense_control_cske_flexibility_status` | Totals by flexibility status and rating. | | `cske.v_town_acct_expense_control_cske_evidence_gap` | Counts and dollars still unassessed or unallocated. | The classified-detail view preserves one row per official account even though facility allocation is many-to-many. Facility codes and labels are aggregated onto the detail row. The dedicated facility view applies percentages when calculating facility totals. ## 6. Expense sign convention The Town expense-control source stores actual expenditures as negative values. The source relationship is: ```text ytd_adjusted_budget + ytd_actual_expended = ytd_unexpended_balance ``` The views preserve that source value and also expose: ```text analytical_positive_spend = -ytd_actual_expended ``` For public-facing reporting, use `analytical_positive_spend` or the aggregate `spent_positive` column. Retain the source-sign field for reconciliation. ## 7. Query examples All examples are read-only. The report-code predicate is included so the query does not silently mix future reports. ### 7.1 Confirm installation and migration status ```sql select migration_code, migration_name, migration_checksum, applied_at from cske.control_schema_migration where migration_code = '0014_TOWN_ACCT_CSKE_REPORTING_CLASSIFICATION_V02'; ``` ### 7.2 List Town departments beside CSKE categories ```sql select distinct department_code_raw, department_name_raw, cske_reporting_category_code, cske_reporting_category_label, cske_reporting_subcategory_code, cske_reporting_subcategory_label, cske_organizational_type_label, cske_classification_status from cske.v_town_acct_expense_control_cske_classified_detail where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' order by cske_category_display_order, department_code_raw; ``` ### 7.3 Budget and spending by service-purpose category ```sql select cske_reporting_category_label, account_count, adjusted_budget, spent_positive, unexpended_balance, round( 100 * spent_positive / nullif(adjusted_budget, 0), 2 ) as percent_spent from cske.v_town_acct_expense_control_cske_category where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' order by cske_category_display_order; ``` ### 7.4 Budget and spending by expense group and type ```sql select cske_expense_group_label, cske_expense_type_label, account_count, adjusted_budget, spent_positive, unexpended_balance from cske.v_town_acct_expense_control_cske_expense_type where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' order by cske_expense_group_label, cske_expense_type_label; ``` ### 7.5 Personnel versus facilities versus other major uses ```sql select cske_expense_group_code, max(cske_expense_group_label) as expense_group, count(*) as account_count, sum(ytd_adjusted_budget) as adjusted_budget, sum(analytical_positive_spend) as spent_positive, sum(ytd_unexpended_balance) as unexpended_balance from cske.v_town_acct_expense_control_cske_classified_detail where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' group by cske_expense_group_code order by adjusted_budget desc; ``` ### 7.6 Service category by expense group matrix This shows whether a service category is primarily personnel, facilities, capital, debt, or another use of money. ```sql select cske_reporting_category_label, cske_expense_group_label, sum(ytd_adjusted_budget) as adjusted_budget, sum(analytical_positive_spend) as spent_positive from cske.v_town_acct_expense_control_cske_classified_detail where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' group by cske_category_display_order, cske_reporting_category_label, cske_expense_group_label order by cske_category_display_order, adjusted_budget desc; ``` ### 7.7 Fund-family totals ```sql select cske_fund_family_label, account_count, adjusted_budget, spent_positive, unexpended_balance from cske.v_town_acct_expense_control_cske_fund_family where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' order by adjusted_budget desc; ``` ### 7.8 Every fund and each department using it ```sql select fund_code_raw, fund_name_raw, cske_fund_family_label, department_code_raw, department_name_raw, sum(ytd_adjusted_budget) as adjusted_budget, sum(analytical_positive_spend) as spent_positive, sum(ytd_unexpended_balance) as unexpended_balance from cske.v_town_acct_expense_control_cske_classified_detail where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' group by fund_code_raw, fund_name_raw, cske_fund_family_label, department_code_raw, department_name_raw order by fund_code_raw, department_code_raw; ``` ### 7.9 Facility-allocated totals This query uses allocated values and therefore does not duplicate the official expense when an account is shared across buildings. ```sql select cske_facility_label, account_count, allocated_adjusted_budget, allocated_spent_positive, allocated_unexpended_balance, minimum_mapping_confidence from cske.v_town_acct_expense_control_cske_facility where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' order by cske_facility_label; ``` ### 7.10 Account detail supporting one facility ```sql select d.full_account_code_raw, d.department_code_raw, d.department_name_raw, d.account_description_raw, m.cske_allocation_percent, m.cske_allocation_method, m.cske_direct_or_shared_code, d.ytd_adjusted_budget, d.analytical_positive_spend, d.ytd_adjusted_budget * m.cske_allocation_percent / 100 as allocated_budget, d.analytical_positive_spend * m.cske_allocation_percent / 100 as allocated_spend, m.cske_mapping_basis, m.cske_confidence_code from cske.v_town_acct_expense_control_detail d join cske.town_acct_code_set c on c.code_set_code = 'WILBRAHAM_FY2027_ACCOUNT_STRING' join cske.town_acct_account a on a.town_acct_code_set_id = c.town_acct_code_set_id and a.full_account_code_raw = d.full_account_code_raw join cske.town_acct_cske_account_facility_map m on m.town_acct_account_id = a.town_acct_account_id and m.effective_fiscal_year = d.fiscal_year where d.report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' and m.cske_facility_code = 'MEMORIAL_SCHOOL' order by d.full_account_code_raw; ``` Replace `MEMORIAL_SCHOOL` with `TOWN_HALL` or `LITTLE_RED_SCHOOLHOUSE` for the other initial facility mappings. ### 7.11 Facility-related costs that are not allocated to a building ```sql select full_account_code_raw, department_code_raw, department_name_raw, account_description_raw, cske_expense_type_label, ytd_adjusted_budget, analytical_positive_spend from cske.v_town_acct_expense_control_cske_classified_detail where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' and cske_facility_related_flag and cske_facility_codes is null order by ytd_adjusted_budget desc; ``` This is a useful targeted follow-up list for Facilities staff or invoice review. ### 7.12 Centralized and potentially misleading ledger placement ```sql select cske_accounting_presentation_label, account_count, adjusted_budget, spent_positive, unexpended_balance from cske.v_town_acct_expense_control_cske_presentation where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' order by adjusted_budget desc; ``` ### 7.13 Select Board cost center and centralized salary pool ```sql select full_account_code_raw, department_name_raw, account_description_raw, cske_expense_type_label, cske_accounting_presentation_label, ytd_adjusted_budget, analytical_positive_spend, cske_spending_flexibility_assessment_status from cske.v_town_acct_expense_control_cske_classified_detail where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' and department_code_raw = '122' order by ytd_adjusted_budget desc; ``` ### 7.14 Current evidence-gap summary ```sql select * from cske.v_town_acct_expense_control_cske_evidence_gap where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709'; ``` This reports the number and budget value of: - Flexibility assessments still unassessed; - Cost drivers still unassessed; - Facility-related accounts not allocated to a facility. ### 7.15 Flexibility assessment status Initially this should show all 473 accounts as unassessed. ```sql select cske_spending_flexibility_assessment_status, cske_spending_flexibility_label, account_count, adjusted_budget, spent_positive from cske.v_town_acct_expense_control_cske_flexibility_status where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' order by cske_spending_flexibility_assessment_status, cske_spending_flexibility_label; ``` ### 7.16 Follow-up queue for flexibility evidence ```sql select d.full_account_code_raw, d.department_code_raw, d.department_name_raw, d.account_description_raw, d.ytd_adjusted_budget, sf.cske_follow_up_question, sf.cske_confidence_code, sf.authoritative_confirmation_needed_flag from cske.v_town_acct_expense_control_cske_classified_detail d join cske.town_acct_code_set c on c.code_set_code = 'WILBRAHAM_FY2027_ACCOUNT_STRING' join cske.town_acct_account a on a.town_acct_code_set_id = c.town_acct_code_set_id and a.full_account_code_raw = d.full_account_code_raw join cske.town_acct_cske_account_spending_flexibility sf on sf.town_acct_account_id = a.town_acct_account_id and sf.effective_fiscal_year = d.fiscal_year where d.report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' and sf.cske_assessment_status = 'UNASSESSED' order by d.ytd_adjusted_budget desc; ``` ### 7.17 Provisional cost drivers and unresolved controllability ```sql select cske_cost_driver_label, cske_controllability_label, cske_cost_driver_assessment_status, count(*) as account_count, sum(ytd_adjusted_budget) as adjusted_budget from cske.v_town_acct_expense_control_cske_classified_detail where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' group by cske_cost_driver_label, cske_controllability_label, cske_cost_driver_assessment_status order by adjusted_budget desc; ``` ### 7.18 Enterprise-supported versus other operations ```sql select cske_enterprise_fund_flag, cske_fund_family_label, count(*) as account_count, sum(ytd_adjusted_budget) as adjusted_budget, sum(analytical_positive_spend) as spent_positive from cske.v_town_acct_expense_control_cske_classified_detail where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' group by cske_enterprise_fund_flag, cske_fund_family_label order by cske_enterprise_fund_flag desc, adjusted_budget desc; ``` This identifies enterprise-fund accounting context; it does not prove that every dollar was funded by a current-year user charge. ### 7.19 Memorial, Little Red Schoolhouse, Town Hall, and current IT ```sql select case when cske_facility_codes like '%MEMORIAL_SCHOOL%' then 'Memorial School' when cske_facility_codes like '%LITTLE_RED_SCHOOLHOUSE%' then 'Little Red Schoolhouse / Public Access' when cske_facility_codes like '%TOWN_HALL%' then 'Town Hall' when department_code_raw = '155' then 'Current IT Department' end as requested_area, fund_code_raw, fund_name_raw, sum(ytd_adjusted_budget) as adjusted_budget, sum(analytical_positive_spend) as spent_positive, sum(ytd_unexpended_balance) as unexpended_balance from cske.v_town_acct_expense_control_cske_classified_detail where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' and ( cske_facility_codes like '%MEMORIAL_SCHOOL%' or cske_facility_codes like '%LITTLE_RED_SCHOOLHOUSE%' or cske_facility_codes like '%TOWN_HALL%' or department_code_raw = '155' ) group by requested_area, fund_code_raw, fund_name_raw order by requested_area, fund_code_raw; ``` For precise shared-facility totals after future percentage allocations, use `cske.v_town_acct_expense_control_cske_facility` rather than summing the unallocated official account values in the classified-detail view. ### 7.20 Reconcile classified detail to the Town source ```sql with source_totals as ( select count(*) as account_count, sum(ytd_adjusted_budget) as adjusted_budget, sum(ytd_actual_expended) as actual_source_sign from cske.v_town_acct_expense_control_detail where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' ), classified_totals as ( select count(*) as account_count, sum(ytd_adjusted_budget) as adjusted_budget, sum(ytd_actual_expended) as actual_source_sign from cske.v_town_acct_expense_control_cske_classified_detail where report_code = 'WILBRAHAM_FY2027_EXPENSE_CONTROL_20260709' ) select s.account_count as source_accounts, c.account_count as classified_accounts, s.adjusted_budget as source_budget, c.adjusted_budget as classified_budget, s.actual_source_sign as source_actual, c.actual_source_sign as classified_actual, (s.account_count, s.adjusted_budget, s.actual_source_sign) = (c.account_count, c.adjusted_budget, c.actual_source_sign) as exact_reconciliation from source_totals s cross join classified_totals c; ``` The expected result is 473 source accounts, 473 classified accounts, identical budget and actual totals, and `exact_reconciliation = true`. ## 8. Safe classification maintenance pattern Do not make undocumented ad hoc updates to classification tables. New or revised classifications should be delivered through a versioned, idempotent migration containing: 1. Exact account, department, fund, object, or facility key; 2. Effective fiscal year; 3. CSKE mapping version; 4. Classification status; 5. Rationale; 6. Supporting source and locator; 7. Confidence; 8. Reviewer and review date when applicable; 9. Follow-up question and confirmation flag; 10. Preflight, reconciliation, repeat-load, and idempotency tests. A controlled flexibility update would conceptually resemble: ```sql begin; update cske.town_acct_cske_account_spending_flexibility sf set cske_obligation_basis_code = 'CONTRACTUAL', cske_spending_flexibility_code = 'COMMITTED_FIXED', cske_change_horizon_code = 'NEXT_CONTRACT_CYCLE', cske_reduction_consequence_code = 'CONTRACT_BREACH_COST', cske_assessment_status = 'EVIDENCE_VERIFIED', cske_confidence_code = 'HIGH', supporting_source_artifact_id = :source_artifact_id, supporting_source_locator = :source_locator, cske_classification_basis = :classification_basis, cske_reviewed_by = :reviewed_by, cske_reviewed_at = clock_timestamp(), authoritative_confirmation_needed_flag = false from cske.town_acct_account a where sf.town_acct_account_id = a.town_acct_account_id and sf.effective_fiscal_year = 2027 and a.full_account_code_raw = :full_account_code_raw; -- Verification belongs here before commit. commit; ``` The placeholders must be supplied by a controlled migration. This is an illustration of structure, not an instruction to update the live database manually. ## 9. What this schema cannot answer alone The expense-control dataset records appropriations and expenditure totals. It does not provide a complete inflow ledger. A responsible sources-and-uses report will need additional public or targeted sources such as: - Revenue budget and actual reports; - Property-tax levy and Proposition 2½ records; - State aid; - Grants and reimbursements; - Enterprise receipts and charges; - Free cash, reserves, stabilization, and fund balances; - Borrowing and debt proceeds; - Asset-sale proceeds; - Town Meeting warrants and votes; - Contracts and collective-bargaining agreements; - Regional assessment documentation; - Capital plans and asset records. Complete HR, AP, or unrestricted accounting-system access is not required for the model to be useful. Unknown, provisional, unallocated, and confirmation- needed values are intentional parts of the reporting design. ## 10. Recommended progression for future reports 1. **Where money comes from:** revenue origin, recurrence, restriction, reliability, availability, matching requirements, and fund. 2. **Where money goes:** department, service purpose, expense group, expense type, facility, and accounting presentation. 3. **What is committed:** verified debt, mandates, contracts, assessments, benefits, and grant conditions. 4. **What can change:** flexibility rating, time horizon, controllability, and consequences. 5. **What remains hidden:** centralized pools, shared services, unallocated facility costs, and evidence gaps. 6. **What creates future exposure:** capital renewal, deferred maintenance, debt, staffing, and future operating commitments. The central design principle is to retain each classification as an independent axis. That permits an evidence-supported statement such as: > This is a recurring, tax-supported personnel cost for a public-facing > service, presented in a centralized cost center, constrained in the current > year, potentially adjustable over a bargaining cycle, with the ultimate > beneficiary allocation still unresolved. That is more accurate and useful than treating every budget line as equally avoidable—or equally untouchable.