Municipal Finance Lab

Municipal Finance Lab simulates a local government finance operation that manages taxpayer accounts, tax categories, assessments, statuses, payments, transactions, reporting, and dashboards.

The project started as SQL practice, then became a structured environment for learning how database design supports business questions, financial reporting, operational review, and audit evidence.

The Municipal Finance Lab Companion turns the project into a case-based learning tool. Instead of only reading about tables, joins, balances, and exceptions, the companion asks the learner to build the analyst path from business question to SQL-ready reporting view.

This keeps the project connected to the larger site idea: learn the concept, apply it in a working system, reflect on the process, then turn the result into public proof.

Municipal Finance Lab serves as a practical environment for developing skills in SQL, PostgreSQL, relational data modeling, reporting, business analysis, financial systems, audit review, and dashboard development.

The project bridges technical practice with real operational questions that analysts, ERP teams, finance departments, and compliance-focused organizations need to answer with reliable data.

The live-style database supports questions about assessment volume, revenue categories, payment activity, outstanding balances, delinquency, and audit exceptions.

Each question becomes a way to practice the analyst workflow: define the business need, identify the grain of the data, join the right tables, reconcile the totals, and explain the result.

The project now uses two database environments. The original municipal_finance_lab database remains a small reference model for basic SQL, joins, and schema learning.

The richer municipal_finance_live database is the main working environment. It contains fuller operating data, payment activity, transaction history, status exceptions, and the reporting examples used for portfolio work.

Data modeling in this lab means deciding what the real-world business objects are, how they relate, which values deserve lookup tables, and which rules should be visible through SQL.

A key modeling decision was separating payments from broader assessment transactions. Payments can have receipt numbers and payment methods, while adjustments, cancellations, refunds, and write-offs still need transaction history even when they are not payments.

The reporting work is shifting from "how do I write SQL?" to "what business question am I answering?" That is the practical difference between syntax practice and analyst work.

SELECT
  tt.tax_type_name,
  COUNT(*) AS assessment_count,
  SUM(a.assessment_amount) AS total_assessment_amount
FROM assessments AS a
JOIN tax_types AS tt
  ON a.tax_type_id = tt.tax_type_id
GROUP BY tt.tax_type_name
ORDER BY total_assessment_amount DESC;

The first Tableau dashboard focuses on assessment count, total assessed amount, assessment amount by status, and assessment amount by tax type. It is intentionally an early lab version, not the final live operations dashboard.

The next dashboard iteration will use municipal_finance_live to add payments received, outstanding balances, collection rates, delinquency, transaction activity, and audit exceptions.

The next Tableau phase will start with one clean query that combines taxpayer information, taxpayer type, tax type, assessment status, tax year, due date, assessment amount, payment totals, balance due, and transaction activity.

That dataset will become the foundation for dashboards focused on assessment totals, status monitoring, delinquency, tax category performance, collections, exceptions, and management review.