Behind Homeowners Aligned
Building a data pipeline to turn fragmented public HOA records into structured, searchable information.
The Problem
Public HOA information can be scattered across different records, documents, and sources, making it difficult to locate and compare.
In Texas the pieces live in separate places. Each association files a management certificate with the Texas Real Estate Commission, and the state publishes a list of those filings. The useful detail (transfer fees, the management company, recording references) sits inside each filed PDF. The association’s legal standing and its officers are in a different system, run by the Texas Comptroller, with no shared identifier linking the two.
The Solution
I developed Homeowners Aligned to demonstrate how automated data collection and structured processing can make this information easier to explore.
A set of Node.js scripts downloads the state dataset, reads each filed certificate, extracts its contents into structured fields, matches the association to its Comptroller record, and loads everything into a Postgres database. A Next.js application then presents each association as a searchable profile with a score that summarizes how much public information is on file.
What the project covers
- Automated collection from public sources
- Extraction from unstructured PDF filings
- Cleaning, validation, and normalization
- Record matching across two datasets
- Relational database design
- A scoring method with visible inputs
- A full-stack, server-rendered web application
- Presenting incomplete data honestly
Technical Architecture
The system has two halves that only meet at the database. The pipeline is a set of command-line scripts that write to Postgres. The web application only reads from it. There is no scheduler, queue, or custom API between them.
- Data sources
- Processing
- Storage
- Scoring
- Presentation
- 1. Data sources
Public records
Texas state sourcesHOA management certificate datasetdata.texas.gov, bulk CSVOne row per association: name, county, city, zip, type, and a link to its filed certificate.
Management certificate PDFsOne filed PDF per HOATransfer fees, management company, recording data, and CC&R references. Many are scans.
Texas ComptrollerFranchise Tax public APIEntity status, registered agent, registration date, and officers.
Then: Fetched by scripts run by hand from the command line. - 2. Processing
Pipeline
Node.js scripts, JSON files between stepsPhase A: parse and validatecsv-parseNormalizes names, cities, zips, and types. Records that fail validation are set aside.
Writes: hoas_approved, hoas_flagged
ThenPhase B: document extractionpdf-parse, Claude APIReads each PDF's text and asks the model for structured JSON. Scanned PDFs are sent whole.
Writes: hoas_enriched, hoas_errors
ThenPhase C: entity matchingComptroller API, JavaScriptFinds the HOA's state entity by name, then merges its status and officers.
Writes: hoas_complete
ThenSeed: loadSupabase REST APIFlattens each record into rows and upserts in batches, keyed on legal name.
Then: Rows written with a server-side service key. - 3. Storage
Structured database
Supabase (Postgres)hoasOne row per associationIdentity, management company, recording data, entity status, and retrieval timestamps.
hoa_officersMany per HOAName, title, and report year for each officer on the Comptroller record.
hoa_feesMany per HOAFee type and amount for each transfer fee found in the certificate.
Then: Read anonymously, limited by row-level security. - 4. Scoring
Transparency scoring
Calculated when a page renders, not storedcalculateProjectedScoreTypeScript, lib/scoring.tsChecks 12 public checkpoints across financial, governance, and legal pillars. Capped at 75, minus 12 if the entity is not active, unscored below 5 checkpoints.
Then: Score and its inputs passed to the interface. - 5. Presentation
Searchable application
Next.js 16, React 19, Tailwind CSS 4SearchSubstring match in PostgresBy name, city, zip, subdivision, or management company, with a typeahead and a city filter.
HOA profileServer-rendered pageManagement company, board members, transfer fees, public records, and source dates.
Score cardReact componentThe projected score with every checkpoint shown as met or missing.
Phase E: address-to-HOA matching
County appraisal parcel files (a Dallas CSV and TxGIO shapefiles, read with the shapefile library) are reduced to unique subdivisions and matched to HOAs by normalized name within the same city or zip. Results load into subdivision_mappings and hoa_zip_codes. It was run for Dallas County only, and the web application does not read these tables.
Technology actually used
| Part | Technology |
|---|---|
| Pipeline | Node.js (CommonJS), built-in fetch, csv-parse, pdf-parse, shapefile, dotenv |
| Document extraction | Claude API (claude-sonnet-4-20250514), called over HTTPS with a fixed JSON schema prompt |
| Database | Supabase (Postgres) with row-level security and pg_trgm indexes |
| Web application | Next.js 16 (App Router), React 19, TypeScript, Tailwind CSS 4 |
| Data access | Supabase JavaScript client using the anonymous key. No custom API layer |
| Scoring | A pure TypeScript function, run when a page renders |
Data Collection
Three public sources feed the profiles.
- HOA management certificate datasetTexas Open Data Portal (data.texas.gov), dataset 8auc-hzdi
Downloaded once as a CSV. Each row gives an association’s name, county, city, zip code, type (POA or COA), and a link to its filed certificate.
- Management certificate PDFsOne document per association, at the link in the dataset
Fetched one at a time with a 1.5 second pause between downloads, a 15 second timeout, and a User-Agent that identifies the project and gives a contact address.
- Texas Comptroller Franchise Tax recordsThe Comptroller's public data API
Queried by name, then by taxpayer number for the detail record. Requests are spaced half a second apart, retried up to three times with a growing delay, and paused when the API reports a rate limit.
The run covered six Dallas-Fort Worth counties: Dallas, Collin, Tarrant, Ellis, Rockwall, and Kaufman. The scripts define other Texas metro areas, but only DFW was collected. Every step is started by hand. Nothing refreshes on a schedule.
The DFW run in numbers
| Measure | Count |
|---|---|
| Associations in the statewide dataset | 16,611 |
| DFW records that passed validation (six counties) | 3,486 |
| DFW records set aside by validation | 3 |
| Records carried through every phase | 3,361 |
| Certificates that needed the scanned-document path | 2,460 |
| Records with at least one transfer fee extracted | 2,645 |
| Records matched to a Comptroller entity | 2,710 |
| Officer records collected | 13,002 |
Counts come from the pipeline’s output files for the May 2026 run. The live database can differ slightly.
Document Processing
The certificates are legal filings with no fixed layout, so their contents cannot be read with a template. Extraction runs in two paths.
Text path
The PDF’s text layer is read with pdf-parse, whitespace is collapsed, and the text is capped at 8,000 words. It is sent to the Claude API with a fixed system prompt that defines the exact JSON to return: subdivision name, association name, management company (name, address, phone, email, website), association address and website, a list of transfer fees, recording data, CC&R references, and association type.
Scanned-document path
If the text layer has fewer than 200 characters, the file is treated as a scan. The whole PDF is sent to the same model as a document with the same schema, so the output shape is identical either way.
Parse, record, continue
The response is parsed as JSON. A record that fails at download, extraction, or parsing is written to an errors file with the stage and message, and the run moves on. Token counts and estimated cost are saved with each record.
The prompt tells the model to return null for any field it cannot find instead of guessing. Those nulls are what the scoring step later counts as missing.
Data Cleaning and Transformation
Normalization and validation
- Normalization. Names are trimmed of stray quotes and repeated spaces, counties and cities are put in title case, zip codes are cut to five digits, and association type is accepted only as POA or COA.
- Validation. A record is set aside if its name is missing or too short, its county or city is missing, its zip is not five digits, its type is unknown, or its certificate link is not a URL. Set-aside records are written to their own file with the reason.
- Matching. To find an association in Comptroller records, its name is stripped of suffixes such as Inc. and LLC, parenthetical notes, and alternate names, then compared in three tiers: exact match after normalization, one name containing the other, and word overlap of at least 50 percent. If the legal name fails, the name extracted from the certificate is tried.
- Flattening. Each nested record (dataset row, extracted fields, Comptroller data) is flattened into one HOA row plus child rows for officers and fees. Re-running the load replaces an HOA’s officers and fees rather than appending to them.
- Staging. Each phase reads one JSON file and writes the next (approved, enriched, complete), so any phase can be re-run or inspected on its own.
Data Storage
All structured data lives in one Supabase Postgres database. Three tables hold what the site displays.
| Table | Holds | Relationship |
|---|---|---|
| hoas | Identity, location, management company, recording data, entity status, and retrieval timestamps | One row per association |
| hoa_officers | Officer name, title, and report year | Many per HOA, deleted with it |
| hoa_fees | Fee type and amount | Many per HOA, deleted with it |
Row-level security is enabled on all three: anyone may read, and only the server-side service key used by the pipeline may write. Trigram indexes on the name columns support substring search.
The schema also contains tables for accounts, reviews, a waitlist, corrections, and stored score history. They were created for the product that was planned and are not used by the public site.
Transparency Scoring
The score answers one narrow question: how much of the information a homeowner would want is actually on public record for this association? It does not measure how well an HOA is managed.
It is a checklist. Each pillar has checkpoints, and a checkpoint is met when the matching field is present.
| Pillar | Public checkpoints | Planned, never collected |
|---|---|---|
| Financial Health | Transfer fees on file, CC&R instrument recorded, association or management website found | Budget, reserve study |
| Governance & Transparency | Board members found, entity status active, management company, registered agent, registration date, complete management contact details | Meeting minutes |
| Legal History | Management certificate on file, subdivision identified, CC&R recording data | Court records |
| Resident Sentiment | None | Reviews, forum activity |
- Each pillar with at least one met checkpoint contributes its share of public checkpoints, and the result is scaled to 100.
- The projected score is capped at 75, because the remaining inputs were meant to come from residents.
- An association whose entity status is not ACTIVE loses 12 points.
- With fewer than 5 of the 12 public checkpoints met, no score is shown at all.
The function is about 200 lines of TypeScript with no dependencies. It runs when a page renders, so a card in search results and the full profile always agree, and the profile shows every checkpoint as met or missing.
Limits worth knowing: an association with no Comptroller match is treated the same as one that is not active, so a failed name match lowers its score. Presence of a field is counted, not its accuracy. The scores are experimental and are not official ratings.
Frontend Application
- Search. A case-insensitive substring match across name, city, zip, subdivision, and management company, ordered by data completeness and paged 24 at a time, with a city filter. The home page search box suggests matches as you type. Try it →
- Profiles. Each association has a server-rendered page showing its management company, board members, transfer fees, public record identifiers, a link to the filed certificate, and the date each source was retrieved.
- Score card. An animated score ring with four expandable pillars. Opening a pillar lists every checkpoint and whether it was met.
- Read-only access. Pages query the database with the anonymous key and no session. There are no accounts, forms, or write paths in the public site.
- Texas guide. A long-form reference on the statutes that govern Texas associations. Read it →
Engineering Challenges
Most certificates are scans
A text-only approach would have skipped the majority of filings. The pipeline detects a missing text layer and switches to sending the document itself, keeping one schema for both paths.
Truncation silently dropped fee tables
An early word cap cut long certificates before their fee schedules. A separate script finds only the text-path records with no fees, re-extracts them with a higher cap, and updates them in place without touching the rest of the file.
Long jobs that must survive interruption
Thousands of sequential downloads and API calls will be interrupted. Every phase saves after each record and skips names already processed on restart, so a failed run resumes instead of starting over.
Two datasets with no shared key
The certificate dataset and Comptroller records share nothing but a name, written differently in each. Matching runs in tiers from strict to loose, and a search too generic for the API to answer is recorded as such rather than forced.
Inconsistent records across counties
For the address-matching branch, each appraisal district writes legal descriptions differently. Four parsers handle the formats, matching is limited to the same city or zip, and a subdivision that matches more than one HOA is left unassigned.
Missing information
About one in five associations had no Comptroller match, and many certificates omit fields. Missing values are stored as null, shown as open checkpoints, and below a minimum the score is withheld.
Duplicate detection
Duplicates are detected by exact legal name only, both when resuming and when loading. Two filings for one association under slightly different names would become two profiles.
Being a polite client
Requests to public servers are spaced out, time out, retry with backoff, and identify the project with a contact address.
Known Limitations
- The data is a snapshot from May 2026 and is not refreshed.
- Extraction by a language model was not checked against a hand-labeled sample, so its error rate is unknown. Fee names and amounts are the most likely to be wrong.
- Name matching can attach the wrong Comptroller entity to an HOA.
- The pipeline has no automated tests and no scheduler.
- Scores are computed on demand and not stored, so there is no score history.
- The data-completeness percentage used to order results is a stored column. The step that calculated it is not part of this repository.
- Reviews, the forum, the resident dashboard, and paid reports were designed but never built. Address matching was built for one county and is not connected to the site.
- Profiles are excluded from search engines because they name individual officers and will go out of date.
What I Learned
- Checkpoint everything. Saving after each record cost almost nothing and turned every failure into a resume.
- Keep the intermediate files. Plain JSON between phases made it possible to fix one phase and re-run only what depended on it.
- A model needs a contract. A fixed schema, explicit nulls, and an errors file made language-model output usable as data. The truncation problem showed that a quiet failure is worse than a loud one.
- Matching is the weakest link. Without a shared key, every match is a guess with a confidence level, and the honest move is to leave ambiguous cases unassigned.
- A score needs a definition and a way to say “not enough data.” Showing each checkpoint made the number explainable.
- What I would do next. Add tests around extraction and matching, validate extraction against a labeled sample, use a stable key for deduplication, store score history, and run the pipeline on a schedule.
AI-assisted development
Claude Code, an AI coding assistant, was used throughout development of both the pipeline and the web application. The pipeline also calls the Claude API at run time to extract fields from certificates.
See the result
Search the records the pipeline produced, or open a profile to see the score card and its sources.