One PostgreSQL warehouse under everything: occupancy, leads, revenue, and ad spend for 38 facilities, reconciled nightly. The least visible thing I've built, and the one every other system depends on.
Every marketing tool keeps its own version of the truth. Google Ads counts a conversion one way, CallRail logs a call another, Salesforce creates a lead a third way. None of them know what a facility’s occupancy was on the day any of it happened. Ask a simple revenue question and you’d spend two days exporting CSVs, matching phone numbers to emails by hand, and still end up with three answers.
The deeper cost wasn’t the reporting time. It was that nothing could be automated. You can’t build a system that reallocates budget by occupancy if occupancy lives in a property management tool that’s never met your ads account. Every ambitious idea died at the same wall: the data wasn’t connected, and it wasn’t trusted.
Before you can automate a decision, the data has to be trustworthy enough to decide with. The warehouse wasn’t a reporting project. It was the prerequisite for every growth system that came after.
Nightly Python jobs pull each source into staging tables in PostgreSQL. From there, everything reduces to a handful of core models: a facility daily snapshot (occupancy, rentable inventory, pricing per facility per day), a lead ledger (every lead exactly once, no matter how many ways it arrived), a spend ledger (cost by channel, campaign, and facility), and an engagement log (email and lifecycle touches). Consumers only ever read the core models, never the raw feeds.
The hardest of these was the lead ledger. The same prospect calls from a mobile number, fills a form with a work email, and shows up in Salesforce a day later as a third record. Identity resolution, collapsing all of that into one lead attached to one facility, is what makes cost-per-lead numbers real instead of triple-counted.
-- the lead ledger: one lead, one row, however it arrived (simplified) with raw_events as ( select coalesce(matched_email, caller_number) as identity_key, facility_id, started_at as ts, 'call' as channel from stg_callrail union all select lower(email), facility_id, submitted_at, 'form' from stg_web_forms union all select lower(email), facility_id, created_at, 'crm' from stg_sf_leads ) select distinct on (identity_key, facility_id) identity_key, facility_id, first_value(channel) over w as first_touch_channel, min(ts) over w as first_seen_at from raw_events window w as (partition by identity_key, facility_id order by ts);
Production adds fuzzy phone/email matching, a 30-day re-inquiry window, and spam filtering. This is the shape of it, not the whole of it.
The payoff of a warehouse is lineage: every downstream system reads from the same reconciled models. Click a system on the right to trace exactly what it depends on.
The warehouse has no UI and no users. Its output is the systems it made possible:
I didn’t build the warehouse for AI. It was built to answer revenue questions and to automate budgets. But it turned out to be the thing that makes AI useful here: agent tooling connects directly to the warehouse over MCP, so an AI assistant can answer “which markets are underpacing on move-ins?” against reconciled data instead of guessing from a stale export.
Most AI initiatives don’t fail on the model. They fail on messy, contradictory data. A warehouse with one version of the truth is the difference between an AI demo and an AI coworker, and it’s the same boring foundation this whole page is about.
Start from the question, not the schema. The warehouse began as one question: "what does a move-in actually cost us, by market?" I worked backwards to the tables needed to answer it. Every model since has earned its place the same way.
Identity resolution is the whole game. Dashboards disagree because the same person is counted three times. The lead ledger was two weeks of work that made years of numbers trustworthy. It's the piece I'd build first anywhere else.
Nightly batch is a feature, not a compromise. Real-time pipelines are expensive to keep honest. For budget allocation and reporting, yesterday's reconciled truth beats this-minute's unreconciled noise, and it fails loudly at 6am instead of silently at 2pm.
Code samples are simplified from production systems. Performance figures are queried from the warehouse or indexed to protect confidential data; demo inputs are illustrative.
The first system built on this warehouse: occupancy-aware Google Ads budgets across 38 facilities, reallocated nightly.