Reference & Guides › Database Schema & Data Tables

Database Schema & Data Tables

Field definitions and official state reporting data lineage.

Audited Release: 2026.09 • Coverage: All Michigan LEAs & Charters

Database Schema & Data Tables

The Michigan School Almanac compiles public educational and financial records published by the Center for Educational Performance and Information (CEPI), the Michigan Department of Education (MDE), and the Michigan Department of Treasury.

This document serves as the Data Dictionary & Sources Ledger. You can trace every data field back to its statutory source, state reporting form, and accounting definition under the Michigan Public School Accounting Manual (Bulletin 1022).

Source data

To power the Almanac, we collect public government files released across state portals, validate them against statutory accounting rules, and assemble them into standard tables:

Data Domain Raw Source File / Form Publishing Agency Official State Source Link Target Tables
Pupil Counts District FTE Counts (Fall & Spring) CEPI MI School Data Portal fct_pupil_counts
Staffing Census Registry of Educational Personnel (REP) CEPI CEPI State Portal fct_pupil_counts, dim_district_staffing
Revenues & Spend Bulletin 1014 Annual Reports MDE MDE State Aid Management System fct_state_aid, dim_district_staffing
Fund Balance Sheets Bulletin 1011 Fund Analysis MDE MDE State Aid Management System fct_state_aid
Accounting Manual Bulletin 1022 Chart of Accounts MDE MDE State Aid & Financial Management dim_account
Detailed Ledgers Financial Information Database (FID) CEPI CEPI State Portal fct_state_aid, dim_account
State Aid Payments State Aid Financial Status Runs MDE MDE State Aid Management System fct_state_aid
Facility Directory Educational Entity Master (EEM) CEPI CEPI State Portal dim_district, fct_district_facilities
Property Tax Rates Form L-4029 Tax Rate Requests MI Treasury Michigan Property Taxes Portal district_millage_rates

Quick Navigation

Dimension Tables

dim_district

Primary registry of all Michigan public school districts (Traditional LEAs and Public School Academies / Charters) and Intermediate School Districts (ISDs).

Column Name Type State Report Source / Identifier Description & Notes
district_code VARCHAR CEPI District Code Official 5-digit state assigned district code (e.g., 81010 for Ann Arbor Public Schools).
district_name VARCHAR CEPI EEM OfficialName Legal entity name as registered with the State of Michigan.
isd_code VARCHAR CEPI ISD Code 2- or 5-digit code of the governing Intermediate School District / RESA (e.g., 81 for Washtenaw ISD).
county_name VARCHAR CEPI County Assignment Geographic county where the district central administration resides.
locale_type VARCHAR NCES Urban-Centric Locale NCES locale classification (e.g., City: Midsize, Suburb: Large, Rural: Fringe).
entity_status VARCHAR CEPI EEM EntityStatus Statutory operational status (Active, Consolidated, Annexed, Dissolved, Closed).
closed_fiscal_year BIGINT CEPI EEM CloseDate / Final Year Final active operational fiscal year (e.g. 2013), or NULL if active.
successor_district_code VARCHAR CEPI EEM Relationship Master Official 5-digit code of absorbing or receiving consolidated district (e.g., 81020), or NULL.
successor_district_name VARCHAR CEPI EEM Relationship Master Legal name of absorbing or receiving consolidated district (e.g., Ypsilanti Community Schools), or NULL.
entity_status VARCHAR CEPI EEM Entity Status Operational status of the district (Active, Closed, Consolidated, Annexed, Dissolved).
closed_fiscal_year BIGINT CEPI EEM Closure Date Fiscal year in which an inactive district ceased operations (NULL for active districts).
successor_district_code VARCHAR CEPI EEM Reorganization 5-digit code of the receiving/successor LEA following consolidation or annexation (NULL if active).
successor_district_name VARCHAR CEPI EEM Reorganization Legal name of the receiving/successor district following consolidation or annexation (NULL if active).

dim_year

Fiscal year definition table mapping school fiscal cycles to standardized calendar and reporting labels.

  • Primary Key: fiscal_year
Column Name Type State Report Source / Identifier Description & Notes
fiscal_year BIGINT State Fiscal Year School fiscal year ending June 30 (e.g., 2024 covers July 1, 2023 to June 30, 2024).
school_year_label VARCHAR Standard Academic Year String Human-readable academic year identifier (e.g., '2023-2024').

dim_district_staffing

Audited average teacher salaries, classroom educator headcounts, career experience, and regional ISD and statewide benchmark medians.

Column Name Type State Report Source / Identifier Description & Notes
district_code VARCHAR CEPI District Code Official 5-digit state assigned district code (e.g., 81010).
fiscal_year BIGINT State Fiscal Year Four-digit school fiscal year ending June 30 (e.g., 2025).
teacher_fte DOUBLE CEPI REP & MDE Bulletin 1014 Audited full-time equivalent certified classroom instructional staff.
avg_teacher_salary DOUBLE MDE Bulletin 1014 AVG T SAL Audited average annual base salary for certified classroom teachers.
avg_years_experience DOUBLE CEPI REP Educator Qualifications Average total career teaching experience in years.
avg_district_experience DOUBLE CEPI REP In-District Tenure Average years of continuous service within current employing district.
pct_inexperienced DOUBLE CEPI REP / ESSA Qualifications Percentage of classroom teaching staff with fewer than three years experience.
isd_median_teacher_salary DOUBLE Derived ISD Benchmark Median teacher salary across peer reporting districts in the Intermediate School District.
state_median_teacher_salary DOUBLE Derived Statewide Benchmark Median teacher salary across all peer reporting districts statewide for the fiscal year.
isd_median_experience DOUBLE Derived ISD Benchmark Median educator career experience in years across peer districts in the Intermediate School District.
state_median_experience DOUBLE Derived Statewide Benchmark Median educator career experience in years across all peer reporting districts statewide for the fiscal year.

dim_account

Hierarchical chart of accounts lookup mapping Bulletin 1022 accounting dimensions into standard civic reporting categories.

Column Name Type State Report Source / Identifier Description & Notes
fund_code BIGINT Bulletin 1022 Fund Dimension Fund identifier (e.g., 11 = General Fund, 41 = Building & Site / Capital Projects).
function_code VARCHAR Bulletin 1022 Function Dimension 3-digit function code specifying purpose (e.g., 111 Elementary Instruction, 261 Operations & Maintenance).
object_code VARCHAR Bulletin 1022 Object Dimension 4-digit object code specifying expenditure type (e.g., 1xxx Salaries, 2820 MPSERS Retirement, 31xx Purchased Services).
category_group VARCHAR Almanac Aggregation Bucket High-level reporting bucket (e.g., 'INSTRUCTION', 'SUPPORT', 'ADMINISTRATION', 'OPERATIONS').
category_label VARCHAR Almanac UI Label Clear, non-jargon display name used across charts and breakdown tables.

dim_provenance

Audit trail of all upstream state publications ingested into the database build.

  • Target Table: dim_provenance
Column Name Type Description & Notes
source_id VARCHAR Unique source identifier slug (e.g., mde_bulletin_1014, cepi_rep_staff).
source_name VARCHAR Official state publication title.
agency VARCHAR Publishing state agency (MDE, CEPI, Michigan Department of Treasury).
description VARCHAR Summary of variables, statutory context, and collection methodology.
publication_url VARCHAR Direct state web address or portal link for verification.
target_table VARCHAR Database table populated by this data source.
min_year BIGINT Earliest historical fiscal year available in the publication dataset.
max_year BIGINT Most recent audited fiscal year available in the publication dataset.
release_date VARCHAR Date when the state released the annual data file.

Fact Tables

fct_pupil_counts

Annual audited pupil enrollment counts, statutory blend calculations, and staff full-time equivalents (FTE).

Column Name Type State Report Source / Identifier Description & Notes
district_code VARCHAR CEPI District Code 5-digit state assigned district code.
fiscal_year BIGINT State Fiscal Year School fiscal year.
fall_count DOUBLE CEPI October Count Day FTE Audited student headcount on the first Wednesday in October (weighted 90% in state aid blend).
spring_count DOUBLE CEPI February Count Day FTE Audited student headcount on the second Wednesday in February (weighted 10% in state aid blend).
blended_count DOUBLE State School Aid Act (MCL 388.1606) Statutory foundation membership count: $(0.90 \times \text{Fall}) + (0.10 \times \text{Spring})$.
teacher_fte DOUBLE CEPI REP Assignment Code 000xx Total classroom teacher Full-Time Equivalents.
instructional_fte DOUBLE CEPI REP Category Instructional Teacher FTEs plus paraprofessionals, literacy coaches, and instructional aides.
support_fte DOUBLE CEPI REP Category Support Guidance counselors, social workers, psychologists, librarians, and speech therapists.
admin_fte DOUBLE CEPI REP Assignment Codes 111xx–119xx Superintendents, assistant superintendents, principals, and central directors.
total_fte DOUBLE CEPI REP Total FTE Total aggregate district staffing Full-Time Equivalents across all roles.

fct_state_aid

Comprehensive audited financial expenditures, revenues, balance sheet reserves, and categorical grant allocations.

Column Name Type State Report Source / Identifier Description & Notes
district_code VARCHAR CEPI District Code 5-digit state assigned district code.
fiscal_year BIGINT State Fiscal Year School fiscal year.
foundation_allowance VARCHAR MDE State Aid Financial Status Report Base per-pupil foundation grant under MCL 388.1620.
operational_membership DOUBLE MDE Bulletin 1014 Membership Official operational pupil membership used for state aid apportionments.
local_revenue DOUBLE Bulletin 1014 Revenue Class 1xx Local revenue (operating property taxes, fees, athletics, tuition).
state_revenue DOUBLE Bulletin 1014 Revenue Class 3xx State revenue (Foundation Allowance, categorical aid, Special Ed, 31a At-Risk).
federal_revenue DOUBLE Bulletin 1014 Revenue Class 4xx Federal revenue (Title I, IDEA Part B, ESSER/ARPA relief grants).
total_revenue DOUBLE Bulletin 1014 Total Revenues Total General Fund (Fund 11) revenues.
total_expenditure DOUBLE Bulletin 1014 Total Expenditures Total General Fund (Fund 11) gross expenditures.
basic_instruction_exp DOUBLE Bulletin 1022 Function 11x Direct elementary, middle, and high school classroom instructional expenditures.
added_needs_exp DOUBLE Bulletin 1022 Function 12x Special education, compensatory education, and English learner instruction.
total_instruction_exp DOUBLE Bulletin 1022 Function 1xx Aggregate instruction spend (basic_instruction_exp + added_needs_exp).
instructional_exp_per_pupil DOUBLE Bulletin 1014 Line Item 1 Total instruction spend divided by operational membership.
operations_exp DOUBLE Bulletin 1022 Functions 26x–29x Plant operations, building maintenance, custodial services, and pupil transportation.
administration_exp DOUBLE Bulletin 1022 Functions 23x–24x Total administrative spend (central office + school building administration).
school_admin_exp DOUBLE Bulletin 1022 Function 24x School building administration (principals, assistant principals, building secretaries).
central_admin_exp DOUBLE Bulletin 1022 Function 23x Board of Education, superintendent office, and district-wide administration.
business_central_services_exp DOUBLE Bulletin 1022 Functions 25x, 28x Fiscal services, purchasing, human resources, and IT central services.
support_services_exp DOUBLE Bulletin 1022 Functions 21x–22x Student support (counseling, health, social work) and instructional staff support (curriculum, library).
current_operating_exp DOUBLE Bulletin 1014 Total COE Current Operating Expenditures (Functions 1xx + 2xx excluding capital outlay and debt).
current_operating_exp_per_pupil DOUBLE Bulletin 1014 Line Item 8 Standard statewide benchmark: COE divided by operational pupil membership.
total_exp_per_pupil DOUBLE Bulletin 1014 Line Item 10 Total gross expenditures divided by operational pupil membership.
capital_outlay_exp DOUBLE Bulletin 1022 Object 6xxx Equipment, building remodeling, and capital purchases charged to Fund 11.
salaries_exp DOUBLE Bulletin 1022 Object 1xxx Gross payroll across all employee groups in General Fund.
teacher_salaries DOUBLE Bulletin 1022 Function 11x, Object 1xxx Classroom teacher base and supplemental salary payroll.
mpsers_exp DOUBLE Bulletin 1022 Object 2820 Mandatory employer contributions to Michigan Public School Employees' Retirement System.
health_benefits_exp DOUBLE Bulletin 1022 Object 2130 Group health, dental, and vision insurance premiums paid by the district.
total_benefits_exp DOUBLE Bulletin 1022 Object 2xxx Total employee benefits (MPSERS, health, FICA, life/disability insurance).
total_personnel_exp DOUBLE Bulletin 1022 Objects 1xxx + 2xxx Total labor compensation (Salaries + Benefits).
purchased_services_exp DOUBLE Bulletin 1022 Object 3xxx–4xxx Contracted services (custodial, transportation contractors, legal, software, utilities).
supplies_exp DOUBLE Bulletin 1022 Object 5xxx Textbooks, consumables, teaching supplies, and fuel.
section_147c_amount DOUBLE State Aid Section 147c Pass-Through State UAAL MPSERS rate cap stabilization payment (non-discretionary pass-through).
fund_11_ending_balance DOUBLE FID Balance Sheet Account 741 Total ending General Operating Fund balance on June 30.
fund_11_unrestricted_balance DOUBLE GASB 54 Unassigned + Assigned Unrestricted reserves available to meet operating obligations.
fund_11_nonspendable DOUBLE GASB 54 Nonspendable Prepaid expenses, inventory, and endowments that cannot be spent.
fund_11_restricted DOUBLE GASB 54 Restricted Externally restricted funds (statute, grantors, creditors).
fund_11_committed DOUBLE GASB 54 Committed Formal board-committed funds requiring official board resolution to release.
fund_11_assigned DOUBLE GASB 54 Assigned Management-assigned reserves for intended future purposes.
fund_11_unassigned DOUBLE GASB 54 Unassigned Pure unencumbered emergency operating cushion.
fund_11_total_assets DOUBLE FID Balance Sheet Asset 1xx Total current assets held by the General Operating Fund.
fund_11_total_liabilities DOUBLE FID Balance Sheet Liability 4xx Total current liabilities and deferred inflows in General Fund.
fund_41_revenue DOUBLE Bulletin 1011 Fund 41 Revenue Capital projects tax millage and bond proceeds.
fund_41_expenditure DOUBLE Bulletin 1011 Fund 41 Expense Facility construction, renovation, and major infrastructure capital outlay.
fund_41_ending_balance DOUBLE Bulletin 1011 Fund 41 Ending Balance Capital Projects balance (Statutorily restricted under MCL 380.1212 / 380.1351a).
reserve_pct DOUBLE Calculated Metric Total Fund 11 Ending Balance as a percentage of Total Expenditures.
unassigned_reserve_pct DOUBLE Calculated Metric GASB 54 Unassigned balance as a percentage of Current Operating Expenditures.
section_31a_amount DOUBLE State Aid Section 31a State At-Risk pupil categorical allocation.
title_i_amount DOUBLE Federal Title I Grant Allocation Federal compensatory education aid for low-income students.
federal_relief_esser_amount DOUBLE Federal ESSER I/II/III Grants Pandemic emergency education relief grant allocations.
special_ed_amount DOUBLE State Aid Section 51a / Federal IDEA Mandated special education reimbursement allocations.
has_center_program BIGINT ISD Center Program Flag Indicator (1 or 0) if district operates regional county center programs for specialized needs.
center_program_desc VARCHAR Program Designation Name/description of countywide center programs hosted by the district.
has_encumbered_reserve_risk BIGINT Reserve Encumbrance Flag Indicator (1 or 0) if general fund balance contains significant encumbrances.
reserve_audit_flag_reason VARCHAR Audit Clarification Note Explicit disclosure explaining encumbrance risk or deficit notification.

fct_district_facilities

Annual physical facility footprint and active instructional building inventory.

Column Name Type State Report Source / Identifier Description & Notes
district_code VARCHAR CEPI District Code 5-digit state assigned district code.
fiscal_year BIGINT State Fiscal Year School fiscal year.
total_active_buildings BIGINT CEPI EEM Active School Units Total count of open, active instructional school facilities in the district.
elementary_count BIGINT CEPI EEM Grade Config PreK–5 Active primary and elementary school buildings.
middle_count BIGINT CEPI EEM Grade Config 6–8 Active middle and junior high school facilities.
high_count BIGINT CEPI EEM Grade Config 9–12 Active high school buildings.
specialized_center_count BIGINT CEPI EEM Special Facility Active early childhood, alternative ed, and specialized special education centers.

district_millage_rates

Audited local property tax millage levies, taxable valuations, and Headlee rollback cushions.

Column Name Type State Report Source / Identifier Description & Notes
district_code VARCHAR Treasury / CEPI Code 5-digit state assigned district code.
fiscal_year BIGINT Tax Levy Year Tax year corresponding to the school fiscal cycle.
operating_homestead_mills DOUBLE Form L-4029 Column 9 (Homestead) Operating millage levied on primary residences (typically 0 mills under Proposal A, except hold-harmless).
operating_non_homestead_mills DOUBLE Form L-4029 Column 9 (Non-Homestead) Operating millage levied on businesses and second homes (statutory target of 18.0000 mills).
sinking_fund_mills DOUBLE Form L-4029 Building/Sinking Fund Voted Building & Site Sinking Fund millage under MCL 380.1212 (pay-as-you-go capital).
debt_mills DOUBLE Form L-4029 Debt Service Voted debt service millage levied to repay voter-approved capital bonds (MCL 380.1351a).
recreation_mills DOUBLE Form L-4029 Community Recreation Voted community service / recreation millage (MCL 380.1455).
total_homestead_mills DOUBLE Total Homestead Rate Aggregate millage rate applied to primary homeowner property.
total_non_homestead_mills DOUBLE Total Non-Homestead Rate Aggregate millage rate applied to commercial / industrial / non-primary property.
taxable_value_homestead DOUBLE County Apportionment Report Total taxable valuation of Principal Residence Exemption (PRE) properties ($).
taxable_value_non_homestead DOUBLE County Apportionment Report Total taxable valuation of non-homestead properties ($).
total_taxable_value DOUBLE Form L-4029 Box 1 Total district taxable property base ($).
headlee_cushion_mills DOUBLE Form L-4029 Headlee Calculation Millage rate cushion authorized by voters above the Headlee roll-back limit.

Core Analytical Views

v_expenditure_waterfall

Standardizes district operational spending into the four core Bulletin 1022 functional categories: Direct Instruction, Student Support Services, Administration (Central & School), and Operations/Transportation.

  • Underlying Tables: fct_state_aid, dim_district, dim_year
View Column Source / Formula Accounting Reference
direct_instruction_amount fct_state_aid.total_instruction_exp Bulletin 1022 Function 1xx
direct_instruction_per_pupil total_instruction_exp / operational_membership Bulletin 1014 Line Item 1
direct_instruction_pct (total_instruction_exp / current_operating_exp) * 100 Share of Current Operating Expenditures
student_support_amount fct_state_aid.support_services_exp Bulletin 1022 Functions 21x–22x
student_support_per_pupil support_services_exp / operational_membership Student Support Spend Per Pupil
student_support_pct (support_services_exp / current_operating_exp) * 100 Share of Current Operating Expenditures
administration_amount fct_state_aid.administration_exp Bulletin 1022 Functions 23x–24x
school_admin_amount fct_state_aid.school_admin_exp Bulletin 1022 Function 24x (Building Principals)
central_admin_amount fct_state_aid.central_admin_exp Bulletin 1022 Function 23x (Board & Superintendent)
operations_transport_amount fct_state_aid.operations_exp Bulletin 1022 Functions 26x–27x
current_operating_exp fct_state_aid.current_operating_exp Bulletin 1014 Total Current Operating Exp

v_fund_firewall

Enforces statutory transparency by contrasting unrestricted General Operating balances (Fund 11) with restricted Capital Projects & Bond Funds (Fund 41), preventing misleading claims about capital reserves being available for payroll.

  • Statutory Authority: MCL 380.1212 & MCL 380.1351a
View Column Source / Formula Statutory Restriction
fund_11_balance fct_state_aid.fund_11_ending_balance General Operating Fund (Unrestricted operating cushion).
fund_11_reserve_pct (fund_11_ending_balance / total_expenditure) * 100 Unrestricted reserve margin benchmark.
fund_41_balance fct_state_aid.fund_41_ending_balance Statutorily Restricted: Building & Site / Bond proceeds. Cannot be transferred to Fund 11.
fund_41_capital_spend fct_state_aid.fund_41_expenditure Dedicated facility and capital construction outlay.

Data Quality & Reporting Rules

  1. Official Records Only: No estimated, simulated, or interpolated figures exist in the dataset. If state filings for a specific district or year are unreleased, values remain NULL ("Not Reported").
  2. Statutory Segregation: Traditional Local Educational Agencies (LEAs) and Public School Academies (Charters under MCL 380.501) are never blended or conflated without clear entity notation.
  3. Pass-Through Insulation: State pension stabilization funding under Section 147c is tracked separately from discretionary operating revenues to avoid inflating apparent operating wealth.
← Data Sources & Links Educational Glossary →
Source: Michigan Department of Education (MDE) Bulletin 1014/1022, CEPI FID / EEM / REP, and Michigan Department of Treasury Form L-4029. Maintained in Markdown format.