Reproducing the Houston CBAS Metrics Nationally With Public and Government-Adjacent Data

Table of Contents

1. Executive Summary and Scope
2. Houston Presentation Metric Inventory
   • Catalog of Metrics and Visuals in the Houston Deck
   • Ambiguities in the Houston Deck
3. Public and Government-Adjacent Sources
   • Federal Datasets and APIs
   • HUD Distribution Layers and ArcGIS Endpoints
   • Local/County/City Permitting and Open-Data Systems
4. Metric-to-Source Mapping and National Replicability Assessment
   • Replicability Summary Table
   • Per-Metric Reproduction Recipes
5. Inferred Houston-Specific Sources and Evidence
6. Practical Automation Design for Local Permitting and Recorder Ingestion
   • Connector Patterns
   • County Recorder / Assessor Ingestion
   • Recommended Normalization Schema
7. Modeling Approach to Estimate Starts, UC, Completions, and Finished Vacant Inventory
8. National Rollout Plan, Top-25 Placeholders, and Implementation Artifacts
   • Top-25 CBSAs Placeholder List
   • Prioritized Connector Roadmap
   • Rollout Prioritization Heuristic
   • CBSA Data Availability Card Template
   • Data Flow and ETL Diagram
   • Release Timeline Gantt
9. Reference Implementation: SQL DDL and Sample Queries
   • Canonical Tables
   • Sample Queries for Q4 Metrics
10. Closing Synthesis

Executive Summary and Scope

The Houston presentation you referenced (and the uploaded PDF dated 02/12/2026) is best understood as a hybrid of: (a) federal macro indicators that are easy to automate nationwide, (b) local government process data (permits, plats, inspections, certificates, recordings) that is automatable but fragmented and vendor-dependent, and (c) proprietary "new-home market intelligence" (community-level starts/closings, spec inventory, floorplan/base-price catalogs, vacant developed lots, future-lots pipelines) that is difficult to replicate purely from public sources without building a sophisticated local-data fusion + modeling layer.

A national rollout across "top-25 CBSAs" (unspecified; placeholders provided later) is feasible if you design the product as a layered stack:

• Federal baseline layer (fast, uniform): revised building permits at CBSA/county/place, metro population and components of change, metro labor market series, house price indices for CBSA, mortgage-originations proxies from HMDA, and selected HUD distribution layers. These come primarily from U.S. Census Bureau, Bureau of Labor Statistics, Federal Housing Finance Agency, and the Federal Reserve Bank of St. Louis (FRED).
• Local operations layer (harder, fragmented): permit issuance + inspection events + certificates of occupancy and local platting/entitlements and deed recordings. These vary by county/city systems (e.g., Accela, Tyler Technologies / EnerGov, OpenGov, custom portals).
• Derived "Houston-style new-home KPIs" layer (model + fusion): use local lifecycle signals to estimate "starts," "under construction," "completions," and "finished vacant inventory," then reconcile to federal totals for calibration (national NRC/SOC and NRS stage-of-construction series).

Important scope note: the Houston deck's new-home and lot pipeline metrics appear to have been produced by Community Builders Advisory Services ("Source: CBAS" appears repeatedly in the deck), implying an internal address/community database and field-verified pipeline. A public-data-only implementation will inevitably be more probabilistic unless you integrate deep local permitting/inspection/plat/parcel/recording data at address level.

Houston Presentation Metric Inventory

This section itemizes every metric/chart/table in the Houston presentation, focusing on: metric name, interpretation/definition, units, time window, and visual type. Where the deck does not define the metric precisely, it is flagged as ambiguous (per your instruction).

Catalog of Metrics and Visuals in the Houston Deck

Ambiguities in the Houston Deck to Treat as "Unspecified"

The following are central to reproducing the deck but not formally defined on-slide, so a national build should treat them as metric-spec decisions you must codify:

• What exactly constitutes a "start" (permit issuance date? foundation inspection? slab poured? vertical framing?).
• What exactly constitutes a "closing" in the new-home context (builder closing date vs deed recording date vs first mortgage origination date).
• How "homes under construction" is counted (permit status? inspections? field audit).
• Definition of "finished vacant homes in inventory" (completed-but-unsold? completed-but-unoccupied? does it include model homes?).
• How months' supply is computed for finished vacant and for VDL (what is the absorption denominator: closings, starts, trailing 3-month annualized, etc.).
• Definitions of VDL, Future Lots, and Future Lots w/ Active Site Work (which entitlements qualify; how "active" is detected).

These are solvable, but they materially affect replication success.

Public and Government-Adjacent Sources to Power Houston-Style Metrics

This section is a catalog of the most relevant nationwide public sources, with endpoints/patterns, cadence, granularity, and automation difficulty.

Federal Datasets and APIs

Building Permits

The U.S. Census Bureau Building Permits Survey (BPS) is the most important uniform national feed for local-market construction activity. Revised permits are released on the 17th workday of each month and are published down to CBSA, county, and permit-issuing place.

BPS bulk files are distributed through Census "FTP-style" directories (public HTTP). The directory structure includes CBSA, County, Place, State, and a large "Master Data Set."

Concrete endpoint patterns (BPS):

# Directory landing (browseable)
https://www2.census.gov/econ/bps/

# CBSA revised monthly & year-to-date files (text)
https://www2.census.gov/econ/bps/CBSA%20%28beginning%20Jan%202024%29/cbsaYYMMc.txt
https://www2.census.gov/econ/bps/CBSA%20%28beginning%20Jan%202024%29/cbsaYYMMy.txt

# Example shown in directory listing
.../cbsa2512c.txt  (revised monthly, Dec 2025)
.../cbsa2512y.txt  (YTD, Dec 2025)

# Master compiled data documentation (notes it is extremely large)
https://www2.census.gov/econ/bps/Master%20Data%20Set/Compiled%20Data%20Documentation.docx

The Master Compiled Data Set is described as extremely large (millions of rows / multi-GB) and is usually unnecessary if you only need top CBSAs; it's typically easier to ingest the monthly CBSA files plus county/place as needed.

Automation difficulty: 1/5 (bulk file ingest; stable)
Time to automate ingestion: ~1–2 engineer-days for a robust downloader/parser + 2–3 days for QA and schema stabilization.

National Starts, Under Construction, Completions, and New-Home Inventory Stages (Calibration, Not Metro)

The Census "New Residential Construction" (NRC) and "New Residential Sales" (NRS) releases (from the Survey of Construction and BPS) provide national and regional estimates for: starts, under construction, completions, and stage-of-construction inventory, including "completed houses for sale."

These are released on the Survey of Construction schedule: NRC and NRS typically on the 12th workday, and revised permits on the 17th workday.

Key constraint: NRC/NRS are not published at CBSA granularity for the "under construction / completions / stage inventory" measures, so they function mainly as macro calibration targets for any metro estimation model.

Automation difficulty: 1/5 (direct Excel downloads)
Time to automate ingestion: <1 week including field dictionary mapping.

Mortgage-Backed "Closings" Proxy (HMDA)

HMDA is maintained/published through the FFIEC HMDA platform (with CFPB stewardship). The dataset is a powerful proxy for mortgage-financed purchase originations and can be summarized by MSA/MD (metro). Federal Financial Institutions Examination Council provides a "Data Browser API" that returns either aggregated JSON or raw CSV subsets, filtered by filing year and geography.

Key endpoints from the official documentation include:

# Aggregations (JSON)
GET https://ffiec.cfpb.gov/v2/data-browser-api/view/aggregations?years=YYYY&msamds=#####&actions_taken=...

# Raw streamed CSV (careful: can be huge)
GET https://ffiec.cfpb.gov/v2/data-browser-api/view/csv?years=YYYY&msamds=#####&actions_taken=...

The documentation specifies required parameters (year + at least one HMDA data filter) and geographic filters including msamds and counties.

For bulk files, "HMDA File Serving" documents institution-level modified LAR endpoints (CSV/TXT per LEI) and describes that other files are served from a public bucket with a fixed prefix.

Automation difficulty: 3/5

• Technically straightforward, but large outputs, annual cycles, and careful interpretation required.

Time to automate ingestion: ~2–4 weeks for a production-grade pipeline (query builder, paging/streaming, retries, audit logs, and a stable metric layer).

Prices: FHFA HPI

The Federal Housing Finance Agency publishes a "master" House Price Index file as direct CSV; it contains CBSA-level series (among many levels).

Concrete endpoint (FHFA HPI):

https://www.fhfa.gov/hpi/download/monthly/hpi_master.csv

Automation difficulty: 1/5
Time to automate ingestion: ~2–4 engineer-days including a CBSA filter/extract and QA.

"Macro Convenience" Replication via FRED

Many of the BPS permit series and other macro series are mirrored in FRED. FRED is particularly useful when you want simple CBSA series IDs and consistent format options, and you're comfortable depending on FRED as a distributor.

Example: Houston 1-unit permits series includes a definition that 1-unit structures correspond to single-family homes (including certain attached forms if separated by ground-to-roof walls).

FRED Web Services provide a stable API with a required API key parameter and support JSON/CSV output via file_type.

Automation difficulty: 1/5
Time to automate ingestion: <1 week.

Population Levels and Migration Components

The Census Population Estimates Program publishes metro/micro population totals and components of change (including natural change and net domestic/international migration components) in downloadable files for 2020–2024 vintages.

This is a direct match for the deck's "births–deaths / domestic migration / international migration" visuals at the metro level.

Automation difficulty: 2/5 (file layout + annual refresh)
Time to automate ingestion: ~1–2 weeks including crosswalk stabilization across vintages.

HUD Distribution Layers and ArcGIS Endpoints

HUD has two major "distribution surfaces" relevant here:

• HUD's ArcGIS-based "HUD Open Data Site / eGIS Storefront" (geospatial layers).
• HUD User datasets portal (non-spatial and special-access datasets).

HUD–USPS Vacancy / No-Stat Data (Restricted Access)

HUD's aggregated USPS administrative data is extremely relevant to your "vacancy / under construction" problem because it includes quarterly counts and defines "No-Stat" as including addresses like homes under construction and not yet occupied.

However, HUD states that under its agreement with USPS it can make the data accessible only to governmental entities and registered nonprofits, and access requires registration.

HUD also describes a newer "Neighborhood Change Web Map API" and a HUD User dataset API tester that requires an access token; again the access model is restricted.

Automation difficulty: 5/5 (because access eligibility and licensing is the gating factor)
Time to automate ingestion: Engineering time ~2–4 weeks once access is granted, but "time to access" is organizational/legal and may dominate.

Local/County/City Permitting and Open-Data Systems

This is where national replication becomes "connector-driven." You should assume each CBSA will require a portfolio of permitting sources (city + county + special districts) rather than one.

Open-Data Portals

Two common patterns:

• Socrata / SODA APIs (often data.city.gov): default $limit behavior and offset-based paging are standard; application tokens can raise throttling limits.
• ArcGIS REST feature services (common on county GIS/open-data sites): use query endpoints with pagination via resultOffset and resultRecordCount, when supported.

Permitting Workflow Platforms

• Accela's REST API docs describe required headers, offset/limit pagination, and rate-limit headers.
• Tyler's "Permits and Code Enforcement API Toolkit" indicates programmatic access to permit/inspection/code enforcement resources (details may be gated by customer licensing).
• OpenGov provides a developer portal and explicitly includes a "Permitting & Licensing" suite, implying API access but typically requires customer credentials.

Houston Examples Visible in Public Web

• City of Houston has a dedicated online permit portal with search/application/payment/inspection scheduling capabilities.
• Harris County has an ePermits portal (JS-based login) for county permitting.
• Houston's planning department publishes plat and permit activity reports in Excel format for upcoming planning commission actions — an "entitlement pipeline" signal that can help model future lots.
• Harris County Appraisal District provides quarterly-updated GIS downloads in shapefile form and provides property-data downloads — useful for parcels/lot geometry.

Metric-to-Source Mapping and National Replicability Assessment

This section maps each Houston metric family to the most plausible public sources, plus an explicit replicability assessment (difficulty 1–5). Where a metric is likely CBAS-proprietary, the mapping focuses on best-effort public substitutes and modeling strategies.

Summary: What You Can Replicate "Cleanly" Nationwide vs What Becomes Probabilistic

Per-Metric Reproduction Recipes

Below are the key Houston metrics you called out (permits, quarterly starts, UC, completions/closings, finished vacant inventory), each with a reproduction plan.

Inferred Houston-Specific Sources and Evidence

This section addresses: "Which local Houston sources or vendor systems did the presenter likely use for each non-federal metric, and why?"

MLS Resale Metrics

The deck explicitly cites "HAR.com" as the source for the resale table and the resale closings/inventory time series. This strongly indicates the presenter used data from Houston Association of Realtors (HAR MLS data distribution), which is proprietary and not a government dataset.

Implication for national rollout: you cannot reproduce "Active Listings," "Days on Market," or "Sales/List Price" purely from government sources; you need MLS partnerships or accept alternative proxies.

New-Home Starts, Closings, Inventory, VDL, and Future Lots

Across the new-home and lot pipeline slides, the deck repeatedly cites "Source: CBAS," and many visuals are at community/subdivision granularity rather than jurisdiction granularity. That pattern is consistent with a proprietary internal "new-home market census" (address/community-level tracking). The data elements that are especially indicative of private tracking rather than a single public feed include:

• Community-level quarterly starts/closings tables (288 corridor, Grand Magnolia, River Ranch, Lago Mar East)
• Floorplan catalog counts and base-price changes
• VDL and future lot status buckets ("Clearing," "WS&D," "Paving")

Public-data inference: CBAS likely used a blend of (1) permit data and inspection milestones from local permitting agencies, (2) parcel/subdivision geometry from county appraisal/GIS sources, and (3) field validation / builder portal scraping for floorplans and prices.

Houston-area publicly visible building-process systems that could contribute include:

• City of Houston permit portal (applications/inspection scheduling).
• Harris County ePermits portal.
• Planning plat activity reports (Excel) as forward-looking entitlement signals.
• Harris County Appraisal District parcel/GIS downloads and property data downloads (parcel geometry + land attributes).

Closings and Transfers

If CBAS produced new-home "closings," they likely relied on either: (a) builder-reported closings, (b) deed recording data, or (c) MLS new construction closings (if covered). Public recorder systems exist in the Houston region, but they commonly have account requirements and transaction frictions. Example: Harris County Clerk real property records portal emphasizes account creation and paid copies.

For national replication, this is one of the hardest domains to automate cleanly.

Practical Automation Design for Local Permitting and Recorder Ingestion

This section gives you the "connector playbook" for city/county permitting, inspection, platting, and recording systems, including typical API patterns, auth/rate limits, and normalization.

Connector Patterns You Should Build

Socrata Connector

• Use $limit + $offset paging; defaults to ~1,000 rows and supports ordering.
• Use app tokens (X-App-Token) for improved throttling and traceability.

Typical query pattern:

GET https://{domain}/resource/{dataset_id}.json?$select=...&$where=...&$order=...&$limit=1000&$offset=...
Header: X-App-Token: <token>

ArcGIS FeatureServer Connector

• Use query endpoints and page using resultOffset and resultRecordCount where supported.
• Detect exceededTransferLimit to continue paging.

Typical query pattern:

GET https://{host}/ArcGIS/rest/services/{svc}/FeatureServer/{layer}/query
  ?where=1%3D1
  &outFields=*
  &f=json
  &resultRecordCount=2000
  &resultOffset=0

Accela (When You Can Obtain API Credentials)

Accela publishes an API with explicit offset/limit pagination and rate limit headers (x-ratelimit-*). This is a "best case" compared with scraping public portals.

Tyler / EnerGov and OpenGov

Tyler describes an API toolkit for permits and code enforcement, implying access to building permits and inspections data programmatically. OpenGov provides a developer portal and Permitting & Licensing API catalog access, again typically gated.

For both, your connector strategy should assume:

• Credentialed API access where your organization is a customer or has interlocal permission.
• Otherwise, fall back to public exports, public search endpoints, or formal data requests.

County Recorder / Assessor Ingestion

Recorder portals vary widely, often require user accounts, and may not have stable JSON APIs. Harris County Clerk's real property records portal emphasizes portal login/account and copy purchasing. Texas also has county-aggregating portals (e.g., Tyler-hosted "countygovernmentrecords" experience), indicating vendor concentration but not necessarily a public API.

Practical automation stance:

• Treat recorder data as "connector per county vendor", not "one national API."
• Prefer official bulk exports where offered; otherwise treat as "scrape with legal review" or "data partnership."

Recommended Normalization Schema (BLDS-Inspired Plus Lifecycle)

The Building & Land Development Specification (BLDS) exists specifically for standardizing building permit open data. It is a useful reference point for your schema layer.

A Houston-style dashboard, however, needs more than permit issuance: it needs a unit lifecycle. Recommended canonical entities:

• Jurisdiction (city/county/agency)
• Permit (application → issued → final)
• Inspection (typed events)
• Certificate of occupancy / completion (explicit record or derived event)
• Parcel / address / geocode (spatial join key)
• Transfer/closing (deed recording + optional lender proxy)
• Unit lifecycle (derived start/completion/closing statuses)

Modeling Approach to Estimate Starts, Under Construction, Completions, and Finished Vacant Inventory

This section gives detailed practical methods when direct measurements are not uniformly available.

Method 1: "Federal-First" Estimation (Fast, Uniform, Approximate)

Method 2: "Lifecycle-Fusion" Estimation (Closest to Houston Deck)

Method 3: USPS Vacancy/No-Stat Proxy (Restricted, but Powerful if Eligible)

National Rollout Plan, Top-25 Placeholders, and Implementation Artifacts

Top-25 CBSAs Placeholder List

You requested that the exact top-25 list be treated as unspecified. Use placeholders until you define whether "top" means population, new-home starts, transaction volume, or another criterion:

<CBSA_01>, <CBSA_02>, …, <CBSA_25>

Prioritized Connector Roadmap (Actionable)

Rollout Prioritization Heuristic for CBSAs

A practical heuristic is to prioritize CBSAs where you can achieve a "minimum viable Houston" with high automation:

1. Permits coverage: large share of new construction occurs in jurisdictions with open-data exports or ArcGIS/Socrata.
2. Recorder accessibility: county recorder exposes bulk exports or modern searchable systems with predictable patterns.
3. Parcel layer availability: county appraisal district provides GIS downloads and parcel identifiers, enabling address-to-parcel joins. (Houston example: HCAD provides quarterly GIS downloads.)
4. Complexity: fewer jurisdictions dominate the CBSA's construction volume.

CBSA Data Availability Card Template (Prefilled with Houston Examples)

Data Flow and ETL Diagram (Mermaid)

flowchart LR
  subgraph Federal[Federal baseline]
    BPS[Census BPS permits files]
    PEP[Census metro pop + components]
    BLS[BLS employment/unemployment]
    FHFA[FHFA HPI]
    HMDA[FFIEC HMDA Data Browser API]
    FRED[FRED series API]
  end

  subgraph Local[Local government-adjacent]
    PERMITS[City/County permit systems]
    INSP[Inspections + CO events]
    PLATS[Plats / entitlement agendas]
    PARCELS[Assessor / parcel GIS]
    REC[Recorder deeds / transfers]
  end

  subgraph ETL[Ingestion + normalization]
    RAW[(Raw landing tables)]
    STG[(Staging/standardization)]
    CORE[(Canonical warehouse)]
    METRICS[(Metric marts)]
  end

  subgraph Outputs[Products]
    DASH[CBSA dashboards]
    API[Public/internal metrics API]
  end

  BPS-->RAW
  PEP-->RAW
  BLS-->RAW
  FHFA-->RAW
  HMDA-->RAW
  FRED-->RAW

  PERMITS-->RAW
  INSP-->RAW
  PLATS-->RAW
  PARCELS-->RAW
  REC-->RAW

  RAW-->STG-->CORE-->METRICS
  METRICS-->DASH
  METRICS-->API

Release Timeline Gantt (Conceptual, Based on Official Schedules)

gantt
  title Typical monthly release cadence (conceptual)
  dateFormat  YYYY-MM-DD
  axisFormat  %d

  section Census/SOC releases
  New Residential Construction (12th workday) :a1, 2026-03-12, 1d
  New Residential Sales (12th workday)        :a2, 2026-03-12, 1d
  Revised Building Permits (17th workday)     :a3, 2026-03-19, 1d

  section Other feeds
  FHFA HPI (monthly, lagged)                  :b1, 2026-03-28, 1d
  BLS metro series (monthly cycles vary)      :b2, 2026-03-15, 2d
  HMDA (annual publication window varies)     :b3, 2026-04-01, 30d

The 12th/17th workday schedule for NRC/NRS and revised permits is documented on Census SOC and BPS schedules. HMDA publication cadence is annual and not "monthly schedule-based"; public announcements confirm annual availability for a filing year.

Reference Implementation: SQL DDL and Sample Queries

Below are exact SQL DDL examples (PostgreSQL-style) for a Houston-like pipeline, and sample queries to compute Q4 starts/completions/under-construction/finished-vacant metrics.

Canonical Tables

-- Jurisdictions (cities, counties, agencies)
CREATE TABLE dim_jurisdiction (
  jurisdiction_id      BIGSERIAL PRIMARY KEY,
  name                 TEXT NOT NULL,
  jurisdiction_type    TEXT NOT NULL,   -- city, county, agency, special_district
  state_fips           TEXT,
  county_fips          TEXT,
  source_system        TEXT,            -- accela, energov, opengov, socrata, arcgis, custom
  source_base_url      TEXT,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Core CBSA dimension (your own, seeded from Census delineation files)
CREATE TABLE dim_cbsa (
  cbsa_code            TEXT PRIMARY KEY, -- e.g., '26420'
  cbsa_name            TEXT NOT NULL,
  delineation_year     INT NOT NULL,     -- e.g., 2023
  is_metro             BOOLEAN NOT NULL
);

-- Address/parcel entity (normalized)
CREATE TABLE dim_property (
  property_id          BIGSERIAL PRIMARY KEY,
  address_full         TEXT,
  address_norm         TEXT,             -- normalized (USPS style)
  city                 TEXT,
  state                TEXT,
  postal_code          TEXT,
  parcel_id_raw        TEXT,
  latitude             NUMERIC(10,7),
  longitude            NUMERIC(10,7),
  county_fips          TEXT,
  cbsa_code            TEXT REFERENCES dim_cbsa(cbsa_code),
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Permit header (raw ingest may be separate; this is standardized)
CREATE TABLE fact_permit (
  permit_id            BIGSERIAL PRIMARY KEY,
  jurisdiction_id      BIGINT REFERENCES dim_jurisdiction(jurisdiction_id),
  permit_number        TEXT NOT NULL,
  permit_type          TEXT,            -- building, electrical, plumbing, etc.
  work_class           TEXT,            -- new, addition, alteration, demo, etc
  residential_flag     BOOLEAN,
  single_family_flag   BOOLEAN,
  units_authorized     INT,
  declared_value_usd   NUMERIC(14,2),
  application_date     DATE,
  issue_date           DATE,
  status               TEXT,
  source_record_id     TEXT,            -- vendor/system id
  property_id          BIGINT REFERENCES dim_property(property_id),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(jurisdiction_id, permit_number)
);

-- Inspection/event log (foundation, framing, final, CO, etc)
CREATE TABLE fact_inspection_event (
  inspection_event_id  BIGSERIAL PRIMARY KEY,
  jurisdiction_id      BIGINT REFERENCES dim_jurisdiction(jurisdiction_id),
  permit_number        TEXT NOT NULL,
  event_type           TEXT NOT NULL,   -- foundation_pass, framing_pass, final_pass, co_issued, etc
  event_status         TEXT,            -- pass/fail/issued/scheduled
  event_date           DATE NOT NULL,
  source_record_id     TEXT,
  property_id          BIGINT REFERENCES dim_property(property_id),
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Recorder transfers (deeds / conveyances)
CREATE TABLE fact_property_transfer (
  transfer_id          BIGSERIAL PRIMARY KEY,
  county_fips          TEXT NOT NULL,
  instrument_number    TEXT,
  document_type        TEXT,            -- warranty deed, deed, etc
  record_date          DATE NOT NULL,    -- recording date
  sale_date            DATE,             -- if available; else null
  sale_price_usd       NUMERIC(14,2),    -- if available
  grantor              TEXT,
  grantee              TEXT,
  property_id          BIGINT REFERENCES dim_property(property_id),
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Derived unit lifecycle table (one row per "housing unit observation key")
-- If you track at address+unit granularity, add unit_number or a unit_id.
CREATE TABLE fact_unit_lifecycle (
  unit_id              BIGSERIAL PRIMARY KEY,
  property_id          BIGINT REFERENCES dim_property(property_id),
  cbsa_code            TEXT REFERENCES dim_cbsa(cbsa_code),

  start_date           DATE,             -- your chosen start definition
  completion_date      DATE,             -- CO or final pass
  closing_date         DATE,             -- deed record date or best proxy

  is_model_home         BOOLEAN DEFAULT FALSE,
  source_confidence     NUMERIC(3,2) DEFAULT 0.70, -- 0..1
  created_at            TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Optional: monthly permits mart (BPS)
CREATE TABLE fact_cbsa_permits_bps (
  cbsa_code            TEXT REFERENCES dim_cbsa(cbsa_code),
  ym                  DATE NOT NULL,     -- first day of month
  units_1unit          INT,
  units_total          INT,
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (cbsa_code, ym)
);

Sample Queries for Q4 Metrics

The queries below assume you have derived start_date, completion_date, and closing_date in fact_unit_lifecycle.

Q4 Starts and Completions (by CBSA)

-- Parameterize these in your application
-- Example for Q4 2025:
WITH params AS (
  SELECT DATE '2025-10-01' AS q_start,
         DATE '2025-12-31' AS q_end
)
SELECT
  ul.cbsa_code,
  COUNT(*) FILTER (WHERE ul.start_date BETWEEN p.q_start AND p.q_end)      AS q4_starts,
  COUNT(*) FILTER (WHERE ul.completion_date BETWEEN p.q_start AND p.q_end) AS q4_completions,
  COUNT(*) FILTER (WHERE ul.closing_date BETWEEN p.q_start AND p.q_end)    AS q4_closings
FROM fact_unit_lifecycle ul
CROSS JOIN params p
GROUP BY ul.cbsa_code
ORDER BY ul.cbsa_code;

Under-Construction Stock at Quarter End

WITH params AS (
  SELECT DATE '2025-12-31' AS q_end
)
SELECT
  ul.cbsa_code,
  COUNT(*) AS under_construction_stock
FROM fact_unit_lifecycle ul
CROSS JOIN params p
WHERE ul.start_date IS NOT NULL
  AND ul.start_date <= p.q_end
  AND (ul.completion_date IS NULL OR ul.completion_date > p.q_end)
GROUP BY ul.cbsa_code;

Finished Vacant Inventory at Quarter End

WITH params AS (
  SELECT DATE '2025-12-31' AS q_end
)
SELECT
  ul.cbsa_code,
  COUNT(*) AS finished_vacant_inventory
FROM fact_unit_lifecycle ul
CROSS JOIN params p
WHERE ul.completion_date IS NOT NULL
  AND ul.completion_date <= p.q_end
  AND (ul.closing_date IS NULL OR ul.closing_date > p.q_end)
  AND COALESCE(ul.is_model_home, FALSE) = FALSE
GROUP BY ul.cbsa_code;

Months' Supply of Finished Vacant Inventory (Example Spec)

A common definition is: months supply = (finished vacant inventory) / (average monthly closings over trailing 3 months).

WITH params AS (
  SELECT DATE '2025-12-31' AS q_end,
         DATE '2025-10-01' AS trailing_start
),
finished_vacant AS (
  SELECT ul.cbsa_code, COUNT(*) AS fv
  FROM fact_unit_lifecycle ul, params p
  WHERE ul.completion_date <= p.q_end
    AND (ul.closing_date IS NULL OR ul.closing_date > p.q_end)
    AND COALESCE(ul.is_model_home, FALSE) = FALSE
  GROUP BY ul.cbsa_code
),
trailing_closings AS (
  SELECT ul.cbsa_code, COUNT(*) AS closings_3mo
  FROM fact_unit_lifecycle ul, params p
  WHERE ul.closing_date BETWEEN p.trailing_start AND p.q_end
  GROUP BY ul.cbsa_code
)
SELECT
  fv.cbsa_code,
  fv.fv AS finished_vacant_inventory,
  tc.closings_3mo,
  CASE
    WHEN tc.closings_3mo = 0 THEN NULL
    ELSE (fv.fv::NUMERIC / (tc.closings_3mo::NUMERIC / 3.0))
  END AS months_supply_finished_vacant
FROM finished_vacant fv
LEFT JOIN trailing_closings tc USING (cbsa_code)
ORDER BY fv.cbsa_code;

Closing Synthesis

A Houston-style national product is achievable if you explicitly separate:

• "Uniform federal metrics" (easy, automatable; strong QA; baseline product), from
• "Houston-style operational KPIs" that require a lifecycle model built on fragmented local government data, plus recorder and HMDA fusion.

The biggest strategic decision is whether your national MVP should:

• replicate the look of Houston metrics with modeled approximations (permits-driven starts, modeled UC/completions), or
• replicate the semantics by building a real address-level lifecycle database in each CBSA (slower, but closer to CBAS' apparent internal methodology).

Either way, the sources and connector patterns above provide a realistic, automatable path grounded in primary federal and government-adjacent systems.