Nationalizing the Houston New-Home Dashboard

Table of Contents

1. Executive Summary
2. Houston Presentation Metric Inventory
3. Public and Government-Adjacent Source Catalog
   • Federal "Backbone" Sources
   • Federal "Distribution Layers" (ArcGIS/Open Data)
   • CBSA Mapping Primitives
4. Metric-to-Source Mapping and Per-Metric Replication Difficulty
   • Core Houston KPIs and Best National Source Candidates
5. Automated Ingestion Playbooks by Connector Family
   • BPS (Permits) Connector
   • SOC/NRC/NRS Connector
   • HMDA Connector
   • HUD-USPS Vacancy/No-Stat Connector
   • ArcGIS Hub / ArcGIS REST Connector
   • Socrata (SODA) Connector
   • Permitting Platforms: Accela, Tyler, OpenGov
6. Houston-Specific Source Inference and Gap Analysis
   • Public vs. Likely CBAS Proprietary
   • Gap Analysis Table
7. Modeling Methods
   • Permit-to-Start and Permit-to-Completion Stock-Flow Model
   • Finished-Vacant "CO Minus Sale" Model
   • Cash Closings Estimation (HMDA + Recorder Fusion)
8. National Rollout Plan Across Top-25 CBSAs
   • Placeholder Top-25 CBSA List
   • CBSA Data-Availability Card Template
   • Prioritized Connector Backlog
   • Rollout Prioritization Heuristic
9. Implementation Artifacts
   • Normalization Schema Recommendation
   • Mermaid ETL Diagrams
   • SQL DDL: Permits, Lifecycle, and Closings Tables
   • Sample SQL Queries
   • Documentation Links Summary

Executive Summary

The Houston presentation (Land Tejas Private Consult dated Feb 12, 2026) is essentially a multi-layer housing "operating system": it mixes (a) macro demand drivers (population, jobs), (b) resale pressure/relief (MLS inventory & closings), and (c) new-home supply pipeline metrics that require construction-lifecycle visibility (starts → under construction → completed → sold/closed, plus lot pipeline: VDL and "future lots"). In Houston, the presenter's "CBAS" metrics strongly indicate a hybrid system: public sources for macro/permits + a field-research / community-level operational database for starts/closings/UC/FV/VDL/future lots (that level of granularity is not available nationally from federal statistics). This is consistent with CBAS's positioning around "primary and secondary granular housing data" and the presence of a "Director of Field Research Operations."

For a national rollout across "top-25 CBSAs" (unspecified—placeholders maintained), the most automatable national spine is:

• Permits issued by metro & jurisdiction (monthly): U.S. Census Bureau Building Permits Survey (BPS), including CBSA ASCII/Excel downloads and technical layouts.
• National/region "truth" for starts/UC/completions and new-home for-sale inventory (monthly/quarterly): U.S. Census Bureau Survey of Construction outputs (New Residential Construction / New Residential Sales).
• Vacancy and "no-stat" proxies (quarterly, restricted access): U.S. Department of Housing and Urban Development / USPS aggregated vacancy data (tract/ZIP crosswalk), with strong relevance to "finished vacant new homes" modeling but access-limited.
• Sales/closings (financed proxy + lender detail) (annual with strong filtering): HMDA public datasets via Federal Financial Institutions Examination Council / Consumer Financial Protection Bureau interfaces.
• Prices & home value indices (monthly/quarterly): Federal Housing Finance Agency HPI master CSV and metro indices.
• Economic series access layer (APIs): Federal Reserve Bank of St. Louis FRED API and series endpoints.

To reproduce Houston-style "starts / UC / completed / finished vacant / VDL / future lots" at CBSA and submarket level, you have three scalable options (in increasing fidelity):

1. Federal-only approximation (fast, lower fidelity): Use CBSA permits (BPS) + national/region SOC lags + HUD-USPS vacancy/no-stat (where available) to estimate UC stock and "finished vacant." This will be directionally useful but not "CBAS-equivalent" at community/submarket resolution.
2. Permit-system lifecycle extraction (best public path): Build connectors to local permitting/inspection systems (Accela, Tyler, OpenGov, ArcGIS/Socrata open-data portals) and compute starts/UC/completions directly from inspection events and permit statuses. Accela documents offset/limit pagination and rate-limit headers; ArcGIS query pagination uses resultOffset/resultRecordCount and exceededTransferLimit.
3. Recorder + assessor fusion (true closings; hardest): Pull deed transfers from county clerk/recorder systems (often account-gated and fee-based, as in Harris County) and combine with assessor parcels and HMDA to classify purchase type (cash vs financed) and new-construction flags.

A pragmatic national rollout is therefore a connector program: federal spine first, then add (a) the "local permitting lifecycle" connector family and (b) a smaller set of "recorder/assessor" patterns for top markets where automation is feasible.

Houston Presentation Metric Inventory

The table below itemizes every market metric / chart / table-type element present in the Houston deck (excluding purely administrative "member events/training" slides). Where a slide is clearly visual but text extraction does not reveal the data series (e.g., some image-only charts), the element is marked ambiguous and treated as a placeholder.

Public and Government-Adjacent Source Catalog with Endpoints and Automation Assessment

This section is an automatable catalog oriented around what can be pulled nationally and how it maps to Houston-style KPIs.

Federal "Backbone" Sources (High Coverage, High Stability)

Critical constraint: SOC (starts/UC/completions) and NRS (new-home inventory stages) are not published at CBSA in the standard public tables—only national and region are guaranteed. So for "top-25 CBSAs," permits are the only guaranteed federal CBSA-level production indicator from Census.

Federal "Distribution Layers" (ArcGIS/Open Data Shells)

HUD republishes some Census BPS aggregations through ArcGIS Hub, which can be an easier ingestion surface when you want GIS-ready joins. Example: "Residential Construction Permits by County" on HUD's open data hub. This is particularly useful when your internal geography is county-based and you need quick joins to CBSA via a county→CBSA delineation file.

CBSA Mapping Primitives You Will Need Everywhere

To nationalize cleanly, you need a single authoritative CBSA crosswalk. The U.S. Census Bureau delineation files provide county membership for each CBSA; the "List1" Excel is a canonical artifact. For HMDA, the Federal Financial Institutions Examination Council also publishes tract lists and crosswalks keyed to MSA/MD for compliance geocoding, which can help reconcile annual boundary changes.

Metric-to-Source Mapping and Per-Metric Replication Difficulty

The Houston deck's metrics fall into three replicability classes:

• Class A: Fully reproducible from federal public sources (national spine).
• Class B: Reproducible with local open-data / permitting-system integration (scales with connector coverage).
• Class C: Likely proprietary/manual in Houston (MLS feeds, field counts, builder inventory); requires modeling substitutes or paid sources.

Core Houston KPIs and Best National Source Candidates

Automated Ingestion Playbooks by Connector Family

You asked for per-metric step-by-step; the most scalable way to implement that is connector-first. Once each connector is in place, metrics become SQL transforms.

BPS (Permits) Connector

Endpoints & Layouts

• CBSA ASCII documentation: cbsaasc.pdf
• Place ASCII documentation (to backfill jurisdiction-level detail): placeasc.pdf
• BPS methodology and revision behavior context: methodology.html

Rate limits / failure modes: Bulk downloads are stable; primary risks are schema updates and file format shifts (Census has changed SOC tables from .xls to .xlsx historically).

SOC/NRC/NRS Connector (National Calibration + Stage-of-Construction Inventory)

HMDA Connector (Financed Closings Proxy)

HUD-USPS Vacancy/No-Stat Connector (Restricted, High Value for "Finished Vacant")

ArcGIS Hub / ArcGIS REST Connector (Open-Data Permitting and Parcel Layers)

Many cities/counties publish permitting, inspections, parcels, subdivision plats, etc. through ArcGIS Hub or ArcGIS Server feature services.

Socrata (SODA) Connector (Common for City Open Data)

Socrata APIs support app tokens and throttling improvements; default result size is commonly 1,000 rows unless you use $limit/$offset.

Permitting Platforms: Accela, Tyler Enterprise Permitting & Licensing, OpenGov

These systems are ubiquitous in large metros, but public access varies dramatically.

• Accela Construct API documents rate-limit headers (x-ratelimit-*) and offset pagination with max limit 1000.
• Tyler Enterprise Permits & Code Enforcement API Toolkit is marketed as providing programmatic access for permits/inspections, but access is typically contractual.
• OpenGov Developer Portal advertises a Permitting & Licensing API with managed API keys.

Automation reality: These are "government-adjacent" but often not open. For top-25 CBSAs you will likely need a strategy of:

• use open-data layers where available (ArcGIS/Socrata),
• otherwise negotiate API credentials with jurisdictions (or accept partial coverage).

Houston-Specific Source Inference and Gap Analysis

What Houston Elements Are Clearly Public vs. Likely CBAS Proprietary

Clear Public Signals Used in the Deck

• BLS employment/unemployment is explicitly cited in the deck, consistent with using public labor statistics APIs/datasets.
• FRED / St. Louis Fed is explicitly referenced in the "permits vs starts" slide; FRED provides programmatic series pulls via API.
• MLS resale metrics cite HAR in the deck; HAR publishes monthly MLS activity summaries and market highlights (though APIs may be member-locked).

Strong Evidence of Proprietary / Field Research Components

• The deck relies heavily on community-level and submarket-level lifecycle counts (starts/closings/UC/FV/VDL/future lots) that are not available from federal CBSA-level releases. SOC itself is national/region for starts/UC/completions.
• CBAS describes delivering "primary and secondary granular housing data" and has a named "Director of Field Research Operations," consistent with direct community tracking and curated inventories.

Gap Analysis Table

Modeling Methods to Estimate Metro-Level Starts/UC/Completions and Finished-Vacant Inventory

These are practical approaches when you cannot get direct lifecycle events for every jurisdiction.

Permit-to-Start and Permit-to-Completion Stock-Flow Model (CBSA Level)

Inputs

• BPS permits by place/county/CBSA (monthly).
• SOC national/region relationships and definitions (starts, UC, completions) to calibrate lag distributions.

Model Sketch

• Let P_t be permitted units (single-family) in month t for the CBSA.
• Start estimate:

where w_k is a lag distribution (k months from permit to start).

• Completion estimate:

where v_j is a start-to-completion distribution.

• Under-construction stock at end of month t:

Calibration

Calibrate w_k and v_j to match regional SOC starts/completions and UC stock dynamics, but allow CBSA-specific adjustment using:

• construction-cycle proxies (local climate, permitting delays),
• observed permit backlogs,
• "exceededTransferLimit" style portal volumes (if partial).

Limitations

Cannot reproduce community/submarket counts like CBAS; it's a CBSA-level approximation.

Finished-Vacant "CO Minus Sale" Model

Goal: estimate completed but unsold/unoccupied new homes.

Best-Available Public Approximation

If you can get completion events (CO/final inspection) from permitting portals (local), and sale/closing events from recorder or HMDA, then:

Practical Proxy Layers

• Use HUD-USPS no-stat and vacancy: no-stat includes "under construction and not yet occupied," while vacant is generally mail-not-collected for 90+ days; HUD warns no-stat mixes phenomena and requires careful interpretation.
• Use address-base changes (total AMS addresses) plus no-stat changes to identify growth vs distress (HUD's ZIP crosswalk notes ratios change over time as addresses are created/removed).

Cash Closings Estimation (HMDA + Recorder Fusion)

• HMDA provides a high-quality signal on financed purchase originations but misses cash deals.
• County clerk/recorder systems provide deed transfers but can be paywalled/account-based (Harris County's portal requires accounts for copies; documents are purchasable).

Approach

1. Use recorder deeds as "ground truth closings count" where automatable.
2. Match or compare to HMDA purchase-loan counts by MSA/MD.
3. Estimate cash share as:

by time and price bucket.

National Rollout Plan Across Top-25 CBSAs with Connectors, Effort, and "Data Availability Cards"

Placeholder Top-25 CBSA List

Top-25 is unspecified. A reproducible way to define "top 25 new-home markets" is to rank CBSAs by single-family permits (BPS CBSA annual or rolling 12-month). Use placeholders until you choose the definition (population, permits, starts estimates, revenue).

CBSA Data-Availability Card Template (Prefilled with Houston Examples)

Federal Baseline

• BPS permits at CBSA level: Yes (monthly).
• SOC starts/UC/completions at CBSA: No (national/region only).
• FHFA HPI metro index availability: Yes (MSA/city coverage broad).
• HMDA MSA/MD purchase-lending proxy: Yes.

Local Permitting / Lifecycle

• City permitting portal exists (apply/search/request inspections) — public UI, API unknown.
• County ePermits exist (public UI for project status/inspections).
• Planning permit activity reports (XLSX archives) exist.
• Expected system type: custom portals + possible GIS layers (ArcGIS) + potential vendor platforms (varies).

Recorder / Assessor

• Real property records portal exists, account/purchase flows.
• Appraisal district GIS downloads update quarterly.

Difficulty Score Per Metric

• Permits: 2
• Starts/UC/completions lifecycle: 4
• Closings (all-cash included): 4–5
• Finished vacant: 5
• VDL/future lots: 5

Prioritized Connector Backlog with Weeks-to-Build

Rollout Prioritization Heuristic for CBSAs

Because lifecycle and recorder access dominate difficulty, prioritize CBSAs in this order:

1. CBSAs where major jurisdictions publish permits/inspections as ArcGIS or Socrata open data (fastest path to starts/UC/completions using lifecycle events).
2. CBSAs where permitting platform APIs are available with manageable access (OpenGov / Accela / Tyler) and jurisdictions are willing to issue credentials.
3. CBSAs where recorder data is programmatically accessible or sold in bulk (otherwise you'll be stuck with HMDA-only closings proxies).

Implementation Artifacts: Normalization Schema, ETL Diagrams, and SQL DDL + Sample Queries

Normalization Schema Recommendation (Minimum Viable)

You want to support:

• permit issuance,
• construction lifecycle (start/inspection milestones; completion/CO),
• closings (financed + cash),
• inventory snapshots (UC stock, finished vacant, VDL/future lots),
• geography rollups (jurisdiction → county → CBSA).

Suggested core tables:

• dim_cbsa (CBSA codes/names; county membership)
• dim_jurisdiction (permit office / city / county; source system)
• fact_permit_monthly (BPS-style aggregates)
• fact_permit_record (local permit record-level)
• fact_inspection_event (local inspections including "foundation", "framing", "final")
• fact_completion_event (CO/finalized completion)
• fact_sale_transfer (recorder deeds)
• fact_mortgage_origination (HMDA curated)
• fact_inventory_snapshot (derived: UC stock, finished vacant, VDL, future lots)

Mermaid: Data Flow / ETL

flowchart LR
  A[BPS CBSA/county/place files] --> S[(Raw landing zone)]
  B[SOC NRC/NRS tables] --> S
  C[HMDA bulk/yearly] --> S
  D[HUD-USPS vacancy/no-stat (restricted)] --> S
  E[Local open data: ArcGIS/Socrata] --> S
  F[Permitting platforms: Accela/Tyler/OpenGov (credentialed)] --> S
  G[Recorder/Assessor portals] --> S

  S --> N[Normalize & map geographies (county->CBSA)]
  N --> W[(Warehouse: permit/lifecycle/sales)]
  W --> M[Metric transforms: Q starts, Q closings, UC stock, finished vacant, VDL]
  M --> DSH[Dashboard/API layer]

Mermaid: Release-Timeline Gantt (Illustrative)

gantt
  title Housing data release/refresh cadence (typical)
  dateFormat  YYYY-MM-DD
  axisFormat  %b %Y

  section Monthly
  BPS permits (monthly)            :active, 2025-01-01, 2026-12-31
  NRC starts/UC/completions        :active, 2025-01-01, 2026-12-31
  NRS sales/inventory              :active, 2025-01-01, 2026-12-31
  FHFA HPI (monthly file)          :active, 2025-01-01, 2026-12-31

  section Quarterly
  HUD-USPS vacancy/no-stat (Q)     :active, 2025-01-01, 2026-12-31

  section Annual
  HMDA public release (annual)     :active, 2025-01-01, 2026-12-31

Notes: SOC release schedule details (monthly press releases, quarterly tables, seasonal revisions) are documented by Census. HUD's dataset schedule calls out USPS vacancies posting timelines and a "20th business day" cadence for HUD SOCDS permits (where used).

SQL DDL: Permits, Lifecycle, and Closings Tables (Houston-Like)

-- 1) Permit aggregates (BPS-like, monthly, CBSA/jurisdiction)
CREATE TABLE fact_permit_monthly (
  source_system         TEXT NOT NULL,          -- e.g., 'census_bps'
  geo_level             TEXT NOT NULL,          -- 'cbsa' | 'county' | 'place'
  geo_id                TEXT NOT NULL,          -- cbsa_code, county_geoid, or place_id
  survey_month          DATE NOT NULL,          -- normalized to first day of month
  units_1u              INTEGER,                -- if available; otherwise total_units
  units_2u              INTEGER,
  units_3_4u            INTEGER,
  units_5pu             INTEGER,
  units_total           INTEGER NOT NULL,
  valuation_kusd_total  NUMERIC,                -- valuation in $000 if from BPS ASCII
  is_imputed_series     BOOLEAN NOT NULL,       -- true = "with imputation", false = "reported only"
  loaded_at             TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (source_system, geo_level, geo_id, survey_month, is_imputed_series)
);

-- 2) Permit record-level (local portals / vendor APIs)
CREATE TABLE fact_permit_record (
  source_system        TEXT NOT NULL,           -- 'accela', 'arcgis', 'socrata', 'houston_portal', etc.
  jurisdiction_id      TEXT NOT NULL,
  permit_id            TEXT NOT NULL,
  permit_type          TEXT,
  work_type            TEXT,                    -- new construction, addition, etc.
  structure_type       TEXT,                    -- SF, MF, etc.
  units                INTEGER,
  valuation_usd        NUMERIC,
  status               TEXT,                    -- issued, finaled, expired, etc.
  applied_date         DATE,
  issued_date          DATE,
  final_date           DATE,                    -- final inspection or CO date if present
  address              TEXT,
  latitude             NUMERIC,
  longitude            NUMERIC,
  raw_payload          JSONB,                   -- keep original for schema drift
  loaded_at            TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (source_system, jurisdiction_id, permit_id)
);

-- 3) Inspection / milestone events (start/completion inference)
CREATE TABLE fact_inspection_event (
  source_system        TEXT NOT NULL,
  jurisdiction_id      TEXT NOT NULL,
  permit_id            TEXT NOT NULL,
  inspection_id        TEXT NOT NULL,
  inspection_code      TEXT,                    -- e.g., 'FOUNDATION', 'FRAMING', 'FINAL'
  inspection_result    TEXT,                    -- pass/fail/cancel
  scheduled_date       TIMESTAMP,
  completed_date       TIMESTAMP,
  raw_payload          JSONB,
  loaded_at            TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (source_system, jurisdiction_id, inspection_id)
);

-- 4) Closings / transfers (recorder)
CREATE TABLE fact_sale_transfer (
  source_system        TEXT NOT NULL,           -- 'county_recorder'
  county_geoid         TEXT NOT NULL,
  document_number      TEXT NOT NULL,
  recording_date       DATE NOT NULL,
  sale_date            DATE,
  grantor              TEXT,
  grantee              TEXT,
  sale_price_usd        NUMERIC,
  address              TEXT,
  parcel_id            TEXT,
  deed_type            TEXT,
  raw_payload          JSONB,
  loaded_at            TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (source_system, county_geoid, document_number)
);

-- 5) HMDA curated (financed purchase proxy)
CREATE TABLE fact_mortgage_origination (
  source_system        TEXT NOT NULL,           -- 'hmda'
  year                 INTEGER NOT NULL,
  msa_md_code          TEXT,                    -- 5-digit
  action_taken         TEXT,
  loan_purpose         TEXT,
  loan_amount          NUMERIC,
  occupancy_type       TEXT,
  property_type        TEXT,
  lien_status          TEXT,
  purchaser_type       TEXT,
  is_purchase          BOOLEAN NOT NULL,
  is_originated        BOOLEAN NOT NULL,
  loaded_at            TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (source_system, year, msa_md_code, action_taken, loan_purpose,
               loan_amount, occupancy_type, property_type, lien_status, purchaser_type)
);

Sample SQL Queries: Q4 Starts, Completions, UC Stock, Finished Vacant Inventory

Assume:

• "Start" is inferred as the first foundation inspection completion date (or another jurisdiction-specific start proxy),
• "Completion" is inferred as final inspection completion date or CO date.

-- Parameters
-- :cbsa_code, :q_start_date, :q_end_date are inputs
-- Example q_start_date='2025-10-01', q_end_date='2025-12-31'

-- A) Q4 Starts (count permits with a start-event in the quarter)
SELECT
  :cbsa_code AS cbsa_code,
  COUNT(DISTINCT pr.permit_id) AS q_starts
FROM fact_permit_record pr
JOIN fact_inspection_event ie
  ON pr.source_system = ie.source_system
 AND pr.jurisdiction_id = ie.jurisdiction_id
 AND pr.permit_id = ie.permit_id
WHERE pr.structure_type = 'SF'
  AND ie.inspection_code IN ('FOUNDATION', 'SLAB', 'PIER_BEAM')   -- customize per jurisdiction
  AND ie.completed_date::date BETWEEN :q_start_date AND :q_end_date;

-- B) Q4 Completions (count permits with final/CO event in quarter)
SELECT
  :cbsa_code AS cbsa_code,
  COUNT(DISTINCT pr.permit_id) AS q_completions
FROM fact_permit_record pr
JOIN fact_inspection_event ie
  ON pr.source_system = ie.source_system
 AND pr.jurisdiction_id = ie.jurisdiction_id
 AND pr.permit_id = ie.permit_id
WHERE pr.structure_type = 'SF'
  AND ie.inspection_code IN ('FINAL', 'CERTIFICATE_OF_OCCUPANCY', 'CO') -- customize
  AND ie.completed_date::date BETWEEN :q_start_date AND :q_end_date;

-- C) Under-Construction Stock at Quarter End
-- UC = started on/before q_end and NOT completed by q_end
WITH starts AS (
  SELECT
    pr.source_system, pr.jurisdiction_id, pr.permit_id,
    MIN(ie.completed_date)::date AS start_date
  FROM fact_permit_record pr
  JOIN fact_inspection_event ie
    ON pr.source_system = ie.source_system
   AND pr.jurisdiction_id = ie.jurisdiction_id
   AND pr.permit_id = ie.permit_id
  WHERE pr.structure_type='SF'
    AND ie.inspection_code IN ('FOUNDATION','SLAB','PIER_BEAM')
  GROUP BY 1,2,3
),
completions AS (
  SELECT
    pr.source_system, pr.jurisdiction_id, pr.permit_id,
    MIN(ie.completed_date)::date AS completion_date
  FROM fact_permit_record pr
  JOIN fact_inspection_event ie
    ON pr.source_system = ie.source_system
   AND pr.jurisdiction_id = ie.jurisdiction_id
   AND pr.permit_id = ie.permit_id
  WHERE pr.structure_type='SF'
    AND ie.inspection_code IN ('FINAL','CERTIFICATE_OF_OCCUPANCY','CO')
  GROUP BY 1,2,3
)
SELECT
  :cbsa_code AS cbsa_code,
  COUNT(*) AS uc_stock_end_q
FROM starts s
LEFT JOIN completions c
  ON s.source_system=c.source_system
 AND s.jurisdiction_id=c.jurisdiction_id
 AND s.permit_id=c.permit_id
WHERE s.start_date <= :q_end_date
  AND (c.completion_date IS NULL OR c.completion_date > :q_end_date);

-- D) Finished Vacant Inventory at Quarter End (completed but not sold/closed by end date)
-- Requires at least one sales signal (recorder or HMDA proxy).
WITH completed_units AS (
  SELECT
    pr.source_system, pr.jurisdiction_id, pr.permit_id,
    MIN(ie.completed_date)::date AS completion_date,
    pr.address
  FROM fact_permit_record pr
  JOIN fact_inspection_event ie
    ON pr.source_system = ie.source_system
   AND pr.jurisdiction_id = ie.jurisdiction_id
   AND pr.permit_id = ie.permit_id
  WHERE pr.structure_type='SF'
    AND ie.inspection_code IN ('FINAL','CERTIFICATE_OF_OCCUPANCY','CO')
  GROUP BY 1,2,3,5
),
sales AS (
  SELECT
    st.address,
    MIN(st.recording_date) AS first_recording_date
  FROM fact_sale_transfer st
  GROUP BY 1
)
SELECT
  :cbsa_code AS cbsa_code,
  COUNT(*) AS finished_vacant_end_q
FROM completed_units cu
LEFT JOIN sales s
  ON cu.address = s.address
WHERE cu.completion_date <= :q_end_date
  AND (s.first_recording_date IS NULL OR s.first_recording_date > :q_end_date);

Important: Address matching for sales is the hardest part in practice (standardization, unit numbers, parcels). This is why recorder/assessor integration tends to be the long pole.

Documentation Links Summary

• BPS landing: https://www.census.gov/permits
• BPS CBSA ASCII doc: https://www2.census.gov/econ/bps/Documentation/cbsaasc.pdf
• SOC/NRC: https://www.census.gov/construction/nrc/
• SOC/NRS: https://www.census.gov/construction/nrs/
• SOC schedule concepts: https://www.census.gov/construction/soc/schedule.html
• HUD USPS vacancy dataset: https://www.huduser.gov/portal/datasets/usps.html
• HMDA overview: https://www.ffiec.gov/data/hmda
• FHFA HPI master CSV: https://www.fhfa.gov/hpi/download/monthly/hpi_master.csv
• FRED API docs: https://fred.stlouisfed.org/docs/api/fred/series_observations.html
• Accela headers/pagination: httpHeaders.html and pagination.html
• ArcGIS query pagination: query-feature-service-layer