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.

In this fictional city finance scenario, an assessment is a billable charge created for a taxpayer account. Payments are applied against those assessments, statuses describe where each account stands, and the finance team needs a clear way to see what was assessed, what was collected, what remains due, and which records need review.

The central deliverable is a reconciliation view: one row per assessment with taxpayer, tax type, status, amount assessed, total paid, balance due, collection state, and audit exception logic that can support Tableau reporting and account follow-up.

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 operations-style practice 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. The name signals a fuller operating model, but the data is still fictional. It contains fuller practice 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;

Municipal Finance Live now includes a web-based revenue operations console for searching taxpayer accounts, reviewing assessments, tracing payments to transactions, monitoring delinquency, and running approved management and audit reports.

The local version reads directly from PostgreSQL. The public portfolio version uses a read-only snapshot of the same synthetic 500-account environment, so the workflow can be explored without exposing a database connection.

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 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 reporting view that combines taxpayer information, taxpayer type, tax type, assessment status, tax year, due date, assessment amount, payment totals, balance due, and transaction activity.

That same dataset will support the ERP report center and dashboards focused on assessment totals, status monitoring, delinquency, tax category performance, collections, exceptions, and management review.