This project started the way most of my projects start: with a problem that seemed small enough to solve in an afternoon.

I wanted a better admissions funnel report.

That was it.

I was not trying to build a data warehouse. I was not trying to create an analytics platform. I was definitely not trying to spend my evenings thinking about effective dates, surrogate keys, or whether a deposit cancellation should count based on when it happened or whether it happened at all.

I just wanted a report.

Specifically, I wanted something that could show the basic admissions funnel across several years:

  • Applications submitted
  • Applicants admitted
  • Deposits received
  • Students enrolled

Ideally, it would compare each year at the same point in the admissions cycle. It would have a few filters. Maybe a chart. Maybe a table with totals.

Nothing dramatic.

The problem was that every simple number immediately came with several follow-up questions.

What Is an Applicant?

“Count the applicants” sounds like a complete instruction until you try to write the query.

Are we counting people or applications?

A person can submit more than one application. They might apply to different campuses, different programs, or different years. Depending on the report, that person might need to count once, twice, or not at all.

What counts as submitted?

Is it enough for the application to have a submission date? Does it also need a released decision? Are there application rounds that should be excluded? What happens when someone defers or transfers an application?

None of these questions are PostgreSQL questions.

PostgreSQL will count whatever I tell it to count. It has no opinion about whether the result makes institutional sense.

Unfortunately, people do.

That turned out to be the real project.

The SQL was rarely the hardest part. The hardest part was deciding what the numbers were supposed to mean.

The work was less about writing clever queries and more about translating phrases like “submitted applicant,” “net admitted,” and “current deposit total” into definitions precise enough that a database could follow them.

The Existing Reports Were Not Necessarily Wrong

This is an important distinction.

The problem was not that our existing Slate reports were useless or incorrect. Slate is very good at what it is designed to do. It stores operational admissions data, supports workflows, sends communications, manages applications, and lets users build reports without needing to become database administrators.

The problem was that our reporting needs had started to push beyond individual reports.

One report might calculate submitted applications one way. Another might use a slightly different filter. A leadership report might count people while an operational report counts applications. Someone might copy the results into Excel, add another condition, and create a third version.

Eventually, the question stops being:

Can Slate produce this number?

The better question becomes:

Can we produce this number consistently, explain exactly how it was calculated, and reproduce it six months from now?

Those are different questions.

I wanted a reporting layer where the business rules lived in one place. If “Net Admitted” meant admitted applicants minus those who later declined admission, that rule should not have to be rebuilt in every report.

It should have a home.

Why PostgreSQL?

I chose PostgreSQL for several deeply sophisticated technical reasons.

It is free.

It is reliable.

I already knew enough SQL to be dangerous.

More seriously, PostgreSQL sits in a useful middle ground. It is powerful enough to support a real analytical system, but it does not require the institution to buy a large cloud data platform just so I can count deposits.

It supports views, window functions, common table expressions, indexes, JSON, materialized views, and most of the other database features I could plausibly need for this project.

It can also run on an ordinary virtual machine.

That mattered. The goal was not to build the most impressive possible architecture. The goal was to build something the college could reasonably host, understand, and maintain.

There is a tendency in technical work to treat complexity as evidence that a solution is serious.

Sometimes the serious solution is just PostgreSQL running quietly on a server, doing exactly what it was asked to do.

Assuming, of course, that I eventually figure out what to ask it.

First, I Needed the Data

The initial plan was straightforward:

  1. Export data from Slate.
  2. Load it into PostgreSQL.
  3. Write some queries.
  4. Make a dashboard.
  5. Declare victory.

This plan survived for several minutes.

Slate contains different kinds of records that describe different parts of the admissions process. There are people, applications, decisions, statuses, and tags. A deposit may appear as a decision. A status can change repeatedly. One person can have multiple applications, and one application can have multiple decisions.

So I could not simply create one giant table called admissions_data and move on with my life.

I mean, I could have.

It would have worked beautifully until the first time I joined a person with two applications to an application with three decisions and accidentally turned one student into six students.

This is one of the more exciting features of relational databases. They will produce completely incorrect results with extraordinary speed.

I started separating the data into tables:

  • person
  • application
  • decision
  • status
  • tag

Each table represented a different kind of thing.

A person is a person.

An application is an application submitted by a person.

A decision belongs to an application.

A status describes the person’s state at a particular point in time.

This sounds obvious when written plainly. It was less obvious while staring at several CSV exports containing overlapping IDs, GUIDs, dates, ranks, codes, and fields with names that seemed clear until I tried to use them.

The CSV Files Were Only the Beginning

At first, the data would arrive through exported CSV files.

CSV files are useful. They are portable, readable, and easy to inspect. They are also very good at quietly creating problems.

A column that looks numeric might actually be an identifier with leading zeros.

A date might be blank.

A Boolean field might contain 1, true, t, yes, or whatever particular interpretation of truth happened to leave the system that day.

A file might contain duplicate records.

A value might change between exports.

This led me to staging tables.

The staging layer is where the raw files land before the data is cleaned and moved into the permanent tables. The staging tables do not need to be elegant. Their job is to accept the source data without making too many assumptions.

That gives me a place to inspect what arrived, convert types, handle blanks, identify duplicates, and decide what should happen when a record already exists.

In other words, the staging layer is the database equivalent of putting the groceries on the kitchen counter before pretending I have a meal plan.

Then the Dates Became Complicated

Admissions reporting is heavily dependent on dates.

A normal year-over-year report cannot simply compare the current year to the full totals from previous years. If today is October 15, comparing this year through October 15 with all of last year would make the current cycle look artificially low.

The comparison needs to use equivalent cutoffs.

If the 2026 cycle is being measured through October 15, the 2025 cycle should also be measured through its equivalent October 15 cutoff.

That is manageable for application submissions.

Then I reached admissions decisions.

Suppose an applicant was admitted before the cutoff but declined admission afterward. Should they count as admitted in the historical report?

For a current report, probably not.

For a report showing what the numbers looked like as of the earlier cutoff, probably yes.

Deposits had the same problem. A student could deposit and later cancel. Whether they count depends on what question the report is answering.

This is the difference between asking:

What is true now?

and:

What was true then?

Databases are very good at answering the first question when the current state is stored clearly.

The second question requires history.

That meant I had to pay much closer attention to decision release dates, status dates, and the difference between a record existing now and a record having existed at a particular moment.

At this point, the “simple dashboard” had developed philosophical concerns about time.

Building the App Before Finishing Everything

My first front end was built in Streamlit.

Streamlit is useful because it allows a Python script to become a working web application with relatively little ceremony. I could connect it to PostgreSQL, create filters, display tables, and make charts without first building an entire web framework.

It was not intended to be the final answer to every problem.

It was intended to make the data visible.

That turned out to be more valuable than finishing the ingestion pipeline first. Once the numbers were on a screen, I could see whether they made sense. I could compare them with existing reports. I could find duplicated counts, missing years, strange categories, and business rules that had never been written down.

There is no prize for automating the wrong answer.

A manual data refresh attached to a correct report is more useful than a beautifully automated pipeline that produces nonsense every morning at 6:00.

The automation can come later.

First, the numbers have to mean something.

The Project Kept Growing

Once the basic funnel report started taking shape, other possibilities became obvious.

Could I build a person-level timeline showing inquiry, application, admission, deposit, and enrollment events?

Could users filter by campus, student type, application round, or source?

Could the system preserve enough history to answer “as of” questions correctly?

Could the same database support broader historical analysis later?

Probably.

This is how projects expand. Not because someone deliberately decides to triple the scope, but because solving one problem reveals the shape of the next one.

The important thing is not to build everything at once.

The first goal is still fairly modest:

Create a reliable admissions funnel report with consistent definitions, year-over-year alignment, and enough detail to explain where the numbers came from.

That is already a meaningful improvement.

Everything else can grow from there.

What This Series Is About

This series will follow the project as I build it.

I will write about the database design, the ingestion process, the reporting views, the application, and the various ways I accidentally counted the same person multiple times.

There will be SQL, but this is not really a series about SQL syntax.

It is about turning messy operational data into something understandable.

It is about discovering that a number is only useful when everyone agrees on what it means.

And it is about the gap between “I just want a report” and the system required to produce that report honestly.

That gap was larger than I expected.

It usually is.

πεπαιδευμένου γάρ ἐστιν ἐπὶ τοσοῦτον τἀκριβὲς ἐπιζητεῖν καθ᾽ ἕκαστον γένος, ἐφ᾽ ὅσον ἡ τοῦ πράγματος φύσις ἐπιδέχεται.
“It is the mark of an educated person to seek precision in each subject only as far as the nature of the matter permits.”
— Aristotle, Nicomachean Ethics I.3, 1094b