TaxRateCollector

# TaxRateCollector

Provably accurate US sales tax rates — for billing systems that can't afford to be wrong.

Most tax-rate APIs hand you a number. TaxRateCollector hands you the number and the official government PDF, CSV, or API response it came from — hashed, timestamped, and stored alongside every rate so your audit team can trace every cent back to the statute that authorized it.

Why teams use it #

• Evidence on every row. Each TaxRate is bound to a SourceDocument holding the raw .gov artifact, its SHA-256 content hash, and the FetchedAt timestamp. Rates are independently verifiable without a live network call.
• All 14,000+ US jurisdictions. Country → State → County → City hierarchy seeded from the US Census Bureau gazetteers. ZIP-to-jurisdiction lookup covers ~33,000 ZCTAs out of the box.
• SSUTA-aligned product taxonomy. Categorize taxable items against the Streamlined Sales & Use Tax Agreement Appendix C — the same legal framework 24 member states already use — with documented overrides for non-member states like CA, TX, NY, and FL.
• Continuous re-scraping with change detection. A background scheduler re-checks each jurisdiction's official .gov source on a configurable cadence. The diff engine writes a ChangeLogEntry the moment a rate moves.
• Pay only for what you bill in. Subscribers pick the states and product categories that match their footprint at $0.01 each per month — no flat enterprise contract, no rates for jurisdictions you don't operate in.
• Plugs into your stack. Blazor Server UI for your tax team, plus CSV / XLSX / SQL / HTML exports for your billing system.

Built for compliance #

Tax compliance is not a "best guess" problem. When an auditor asks why you charged 8.25% on a transaction in Beverly Hills, "our API said so" is not a defense. TaxRateCollector gives you the original CDTFA PDF, hashed at fetch time, with a timestamp — the kind of paper trail that ends audits instead of starting them.

A Blazor Server application that builds and maintains an exhaustively researched, evidence-backed master table of US sales and excise tax rates for every jurisdiction (Country → State → County → City).

Table of Contents #

1. Architecture Overview
2. Prerequisites
3. Project Structure
4. Getting Started
5. How the Schema Gets Populated
   • On Every Startup (automatic)
   • Step 1 — SST Product/Service Taxonomy
   • Step 2 — Census Jurisdictions (Counties & Cities)
   • Step 3 — ZIP Code Crosswalks
   • Step 4 — Tax Rate Scraping
   • Other Admin-Configured Tables
6. SSUTA Membership & Non-Member States
7. Database Schema
8. Data Source URLs
9. Settings
10. Evidence & Provenance System
11. UI Pages
12. Exports
13. Database Migrations
14. NUnit Tests
15. Scraper Framework
16. Adding a New State Scraper
17. Roles & Subscriptions
18. Deploying to Azure

Architecture Overview #

TaxRateCollector.Core           Domain entities, enums, interfaces TaxRateCollector.Infrastructure EF Core, migrations, seeders, importers, scrapers, services TaxRateCollector.Blazor         Blazor Server UI, pages, exports TaxRateCollector.UnitTests      NUnit 4 — hierarchy, seeder correctness, DB population 

Stack:

Prerequisites #

• .NET 10 SDK
• SQL Server LocalDB (included with Visual Studio, or sqllocaldb create MSSQLLocalDB)
• Git
• (Optional) Visual Studio 2022 17.10+ or Rider 2024.1+

Project Structure #

TaxRateCollector/ ├── TaxRateCollector.Core/ │   ├── Entities/ │   │   ├── Jurisdiction.cs             Self-referential hierarchy node (Country/State/County/City) │   │   ├── TaxRate.cs                  Rate row with IsCurrent, TaxCategoryId, evidence link │   │   ├── TaxCategory.cs              SST taxonomy node (hierarchical) │   │   ├── SourceDocument.cs           Evidence doc (SHA-256, raw content, SourceUrl) │   │   ├── StateTaxProfile.cs          State-level metadata (agency name/URL, SST membership) │   │   ├── ExciseTaxRate.cs            Sin/excise tax (alcohol, tobacco, cannabis, fuel, hotel) │   │   ├── ScrapeRun.cs                Scrape run metadata (status, timestamps, counts) │   │   ├── ZipCodeRecord.cs            ZIP → State/County/City junction │   │   └── ...billing, changelog, logs │   ├── Enums/ │   │   ├── JurisdictionType.cs         Country=0, State=1, County=2, City=3 │   │   ├── CategoryTaxability.cs       Taxable, Exempt, Reduced, Varies │   │   ├── LocalTaxAuthorityType.cs    Piggyback, HomeRule, SstUniform, Independent │   │   └── ...ScrapeStatus, SourceType, ProductCategory │   └── Interfaces/ │       ├── IScrapeOrchestrator.cs │       ├── IScrapeStrategy.cs │       ├── ISstTaxonomyImportService.cs │       └── ... │ ├── TaxRateCollector.Infrastructure/ │   ├── Data/ │   │   └── AppDbContext.cs             EF Core DbContext, all 25 tables │   ├── Migrations/ │   ├── Seeding/ │   │   ├── JurisdictionSeeder.cs       Seeds Country + 51 States on startup │   │   ├── TaxCategorySeeder.cs        Seeds SST taxonomy from hardcoded SstTaxonomyData │   │   ├── StateTaxProfileSeeder.cs    Seeds 51 state tax profiles (hardcoded, verified 2025-01-01) │   │   └── SstTaxonomyData.cs          Hardcoded SST category hierarchy definitions │   └── Services/ │       ├── CensusJurisdictionImportService.cs  Downloads Census ZIPs → creates ~3,200 counties + ~30,000 cities │       ├── ZipImportService.cs                 Downloads Census crosswalks → links ~33,000 ZIPs │       ├── SstTaxonomyImportService.cs         Downloads SSUTA PDF → refreshes TaxCategory descriptions │       ├── ScrapeOrchestrator.cs │       ├── ScrapeSchedulerService.cs           IHostedService background re-scrape │       ├── SettingsService.cs                  Reads/writes %APPDATA%\MindAttic\TaxRateCollector\settings.json │       └── ...DiffEngine, AlertService, TaxCalculator │ ├── TaxRateCollector.Blazor/ │   ├── Components/Pages/ │   │   ├── Jurisdictions.razor         Lazy-loading hierarchy tree, inline rate edit, evidence drop zones │   │   ├── Setup.razor                 6-step admin pipeline to populate the database │   │   ├── Settings.razor              Theme, URLs, PayPal/pricing config, DB backup │   │   ├── Glossary.razor              SST term definitions │   │   └── Logs.razor │   ├── Services/ │   │   └── ViewAsService.cs            Admin preview roles (Actual / Subscriber / Guest) │   └── Program.cs                      DI registration, migrate → seed on startup │ └── TaxRateCollector.UnitTests/     └── SetupTests/         ├── SstTaxonomyStructureTests.cs         ├── TaxCategorySeederTests.cs         ├── AppSettingsTests.cs         ├── DatabaseBackupTests.cs         └── DatabasePopulationIntegrationTests.cs  (Category=Integration, needs LocalDB) 

Getting Started #

# 1. Clone git clone https://github.com/mindattic/TaxRateCollector.git cd TaxRateCollector  # 2. Restore dotnet restore  # 3. Run (migrations + seeders run automatically) dotnet run --project TaxRateCollector.Blazor  # 4. Open https://localhost:5001 #    Log in as dev admin (set DEV_ADMIN_EMAIL / DEV_ADMIN_PASSWORD env vars) #    Navigate to Setup to run the data import pipeline 

On first launch, Program.cs automatically:

1. Applies all pending EF Core migrations
2. Seeds TaxCategories (SST taxonomy, ~200 nodes)
3. Seeds Jurisdictions (Country + 51 States)
4. Seeds StateTaxProfiles (51 profiles)
5. Seeds PricingConfig (Id=1, $0.01/state)
6. Seeds PayPalConfig (Id=1, sandbox mode)
7. Creates the dev admin Identity user (if env vars are set)

The rest of the data — counties, cities, ZIP codes, actual tax rates — is populated through the Setup pipeline described below.

How the Schema Gets Populated #

This is the most important section. The database has ~25 tables that fill up in distinct phases.

On Every Startup (automatic) #

These run in Program.cs before the app accepts requests. They are all idempotent — they check before inserting and skip if data already exists.

TaxCategorySeeder uses the hardcoded definitions in SstTaxonomyData.cs — the same set the SstTaxonomyImportService (Step 1 of Setup) can optionally refresh descriptions for from the live SSUTA PDF.

StateTaxProfileSeeder contains all 50 states + DC with:

• State code, state name, GeneralSalesTaxRate
• IsSstMember (from the SST Governing Board member list)
• LocalTaxAuthorityType (Piggyback / HomeRule / SstUniform / Independent)
• State revenue agency name and official URL
• Notes on local tax caps where applicable

Step 1 — SST Product/Service Taxonomy #

Service: SstTaxonomyImportService
Trigger: Setup → "Import SST Taxonomy" button (or "Run All")
Source: SSUTA Agreement PDF, Appendix C (downloaded at runtime)
Default URL: https://www.streamlinedsalestax.org/docs/default-source/agreement/ssuta/ssuta-as-amended-through-12-20-24-with-hyperlinks-and-compiler-notes-at-end-clean-for-posting.pdf

What it does:

1. Downloads the SSUTA Agreement PDF using UglyToad.PdfPig
2. Locates Appendix C (pages between "Appendix C" and "Appendix D" headings)
3. Extracts defined terms using the regex pattern "Term" means <definition>
4. Maps extracted terms to the existing TaxCategory hierarchy
5. Upserts TaxCategory.Description fields with the official PDF definitions
6. Falls back to hardcoded KnownDescription for any term not found in the PDF

This step is optional — the startup seeder already populates the category tree. This step just enriches descriptions from the authoritative PDF source and can be re-run whenever a new version of the SSUTA is published.

Step 2 — Census Jurisdictions (Counties & Cities) #

Service: CensusJurisdictionImportService
Trigger: Setup → "Import Census Jurisdictions" button (or "Run All")
Sources: US Census Bureau — all free, no API key required

What it does:

1. Downloads and caches the four Census files to %APPDATA%\MindAttic\TaxRateCollector\cache\
2. Parses county FIPS codes and names → creates Jurisdiction rows with JurisdictionType=County, parented to the correct State
3. Parses place FIPS codes and names → creates Jurisdiction rows with JurisdictionType=City
4. Uses the ZCTA crosswalks to derive the correct county for each city (majority land-area intersection)
5. Creates placeholder TaxRate rows (0.000%) for all new jurisdictions across all TaxCategory leaves
6. Records a ScrapeRun entry for the import

Result: ~3,200 counties + ~30,000 cities, all correctly parented in the hierarchy.
Duration: 20–40 minutes on first run. Cached files speed up re-runs.

Step 3 — ZIP Code Crosswalks #

Service: ZipImportService
Trigger: Setup → "Import ZIP Codes" button (or "Run All")
Sources: Same Census ZCTA crosswalk files (already cached from Step 2)

What it does:

1. Reads the two crosswalk files (downloads if not cached)
2. Selects the primary county for each ZIP by largest AREALAND_PART value
3. Selects the primary city for each ZIP by largest AREALAND_PART value
4. Unions ~33,000 unique ZIPs from both files
5. Resolves each ZIP's StateJurisdictionId, CountyJurisdictionId, CityJurisdictionId by matching FIPS codes to Jurisdiction rows created in Step 2
6. Bulk-inserts ZipCodeRecord rows (skips ZIPs already imported — idempotent)
7. Optionally enriches city names via the USPS CityStateLookup API if usps_api_key is configured in Settings

Result: ~33,000 ZipCodeRecord rows, each pointing at three jurisdiction rows.
Duration: 15–30 minutes on first run.

How ZIP lookup works at query time:

Customer enters ZIP 90210     ↓ ZipCodeRecord lookup → State=CA + County=06037 (LA County) + City=Beverly Hills     ↓ Query TaxRates for those 3 JurisdictionIds (filtered by TaxCategoryId)     ↓ Sum: 7.25% (CA) + 1.00% (LA County) + 0.00% (BH) = 8.25%     ↓ Apply CategoryTaxability rule (e.g., Groceries in CA → Exempt → 0%)     ↓ Final: ItemPrice × 0%  (CA exempts unprepared food) 

ZIP codes carry no rates themselves — they are purely a lookup index into the jurisdiction hierarchy.

Step 4 — Tax Rate Scraping #

Service: ScrapeOrchestrator / IScrapeStrategy implementations
Trigger: Setup → "Run Scrape" button, or the ScrapeSchedulerService background service
Sources: Official .gov tax rate pages — one IScrapeStrategy per state/format

Before scraping can run, each jurisdiction's SourceUrl must be set (the URL of that state's official tax rate page). Step 5 of Setup guides the admin through assigning these URLs. The Discovery card on the Setup page can auto-probe jurisdictions to suggest source URLs.

What it does:

1. Loops all Jurisdiction rows where IsActive = true and SourceUrl is set
2. Calls strategy.CanHandle(jurisdiction) to find the right scraper plug-in
3. Scraper downloads the source (HTML table, CSV, XLSX, API) and normalizes rates via Sanitizer
4. DiffEngine compares the new rate to the current IsCurrent = true row
5. If changed: sets old row IsCurrent = false, inserts new TaxRate row, writes ChangeLogEntry
6. Attaches the raw source artifact as a SourceDocument with SHA-256 hash

Existing strategies:

Other Admin-Configured Tables #

These tables are populated through the Settings page (/settings), not the Setup pipeline:

SSUTA Membership & Non-Member States #

The SST product taxonomy seeded in TaxCategories is the authoritative classification for 24 SSUTA member states. It applies as a useful scaffold for all other states too, but taxability per category for non-members must come from each state's own statutes — not from the Taxability Matrix.

States with No Sales Tax #

Alaska, Delaware, Montana, New Hampshire, and Oregon levy no state sales tax. All five still appear in Jurisdictions and StateTaxProfiles (with GeneralSalesTaxRate = 0). No rate scraping is needed. Note: some Alaskan municipalities impose local sales taxes independently.

24 SSUTA Full Member States #

AR, GA, IN, IA, KS, KY, MI, MN, NE, NV, NJ, NC, ND, OH, OK, RI, SD, TN, UT, VT, WA, WV, WI, WY

For these states, SSUTA Appendix C definitions apply uniformly. Local jurisdictions within each member state must use the state's SST-derived taxability base (see LocalTaxAuthorityType = SstUniform).

~21 Non-Member States (have sales tax, not in SSUTA) #

These states define their own taxability rules. The seeded TaxCategory tree is still used as a classification scaffold, but the StateCategoryRule rows for these states must be populated from each state's own statutes rather than from the SST Taxability Matrix.

Database Schema #

All 25 tables grouped by domain:

Tax Rates #

Product/Service Taxonomy #

Geographic Lookup #

State Profiles #

Subscription / Billing #

Infrastructure #

Data Source URLs #

All external URLs are stored in %APPDATA%\MindAttic\TaxRateCollector\settings.json and editable from Settings → Data Source URLs. The defaults point to the free 2025 Census Bureau files and the current SSUTA agreement.

Setup → Step 1 "Validate Data Source URLs" runs HTTP HEAD checks against all of these before allowing the import pipeline to proceed.

Cache directory: %APPDATA%\MindAttic\TaxRateCollector\cache\
The four Census files are cached locally after the first download. Use the "Clear cache" buttons in Setup if you need to force a fresh download.

Settings #

Settings file: %APPDATA%\MindAttic\TaxRateCollector\settings.json
Managed by SettingsService (singleton). Created with defaults on first run.

Theme is also written to localStorage (key trc-theme) so it applies before Blazor's interactive mode initialises, preventing a flash of the wrong theme on page load.

Evidence & Provenance System #

Every TaxRate row can link to a SourceDocument that stores the raw artifact used to establish the rate:

1. Capture — raw source stored in SourceDocument.RawContent (API JSON, base64 PDF, or HTML)
2. Hash — ContentHash = SHA256(UTF8(RawContent)) as a 64-character lowercase hex string
3. Verify — re-hashing RawContent and comparing to ContentHash proves the document has not been altered
4. Audit — FetchedAt (ISO 8601 UTC) + SourceUrl make the rate independently verifiable without a live network call

Admins can also manually drag-and-drop evidence files (.pdf, .csv, .html, .xlsx, .json) onto any jurisdiction's detail row or evidence panel. The file is hashed on upload and stored in %APPDATA%\MindAttic\TaxRateCollector\evidence\.

UI Pages #

/ — Jurisdictions #

Lazy-loading hierarchy tree. States load on page init; counties load on state expand; cities load on county expand. Each node shows:

• Tier badge, name, FIPS code
• Current tax rate (editable inline — creates a new IsCurrent=true row, retires the old one)
• Cumulative rate range (∑ min% – max%) on state/county nodes
• Drag-and-drop evidence zones on each rate row and in the dedicated evidence panel

Product category picker — Goods / Services tab bar at the top filters the entire tree to rates for that SST category. Switching tabs reloads all rates with a frosted-glass spinner overlay so stale data remains visible during the reload.

Role picker — Fixed overlay (lower-right corner, admin only) to preview the UI as Visitor / Subscriber / Admin. Switching roles reloads the tree in the appropriate access level.

/setup — Setup #

Six-step admin pipeline for first-time database population:

"Run All Steps" executes steps 1–4 in sequence, skipping any already completed.

/settings — Settings #

• Theme, font, font size
• All Census + SST source URLs (with per-URL "Test" button)
• USPS API key
• Scrape frequency and evidence auto-fetch toggles
• PayPal credentials (ClientId, ClientSecret, Mode, WebhookId)
• Pricing config (PricePerState)
• Database backup (runs sqlpackage /Action:Export to produce a .bacpac)

/glossary — Glossary #

SST-defined terms from the TaxCategory.Description fields, displayed alphabetically.

/logs — Logs #

Recent LogEntry records from Serilog, filterable by level.

Exports #

From the Jurisdictions page export dropdown:

All exports download via downloadBase64File JS interop — no temp files on the server.

Database Migrations #

The EF Core tools target is TaxRateCollector.Infrastructure. Run from the repo root:

# Apply migrations (not normally needed — Program.cs does this at startup) dotnet ef database update \   --project TaxRateCollector.Infrastructure \   --startup-project TaxRateCollector.Blazor  # Create a new migration after changing entities dotnet ef migrations add <MigrationName> \   --project TaxRateCollector.Infrastructure \   --startup-project TaxRateCollector.Blazor  # Drop and recreate (dev only) dotnet ef database drop \   --project TaxRateCollector.Infrastructure \   --startup-project TaxRateCollector.Blazor 

Migration history:

NUnit Tests #

# Unit tests only (no SQL Server required) dotnet test TaxRateCollector.UnitTests  # Integration tests (requires SQL Server LocalDB with migrations applied) dotnet test TaxRateCollector.UnitTests --filter Category=Integration 

SetupTests/SstTaxonomyStructureTests.cs #

Pure unit tests against SstTaxonomyData.Definitions — no database needed.

SetupTests/TaxCategorySeederTests.cs #

EF Core InMemory database — fast, no SQL Server needed.

SetupTests/AppSettingsTests.cs #

Verifies AppSettings defaults and URL validity.

SetupTests/DatabaseBackupTests.cs #

Unit tests for connection string parsing in Settings.razor CreateBackup().

SetupTests/DatabasePopulationIntegrationTests.cs #

Requires SQL Server LocalDB with migrations applied. Run with --filter Category=Integration.

Covers:

• TaxCategories populated, exactly two roots (Goods + Services), no leaf roots, valid TopLevelType
• StateTaxProfiles exactly 51 rows, all have 2-char state codes, non-negative rates, non-empty state names
• Jurisdictions has US country row, exactly 51 state rows
• PricingConfig and PayPalConfig have at least one row each, PricePerState > 0
• TaxCategorySeeder.SeedAsync and StateTaxProfileSeeder.SeedAsync are idempotent against the real DB

Scraper Framework #

Each state has one or more IScrapeStrategy implementations registered in DI:

public interface IScrapeStrategy {     string StrategyKey { get; }     bool CanHandle(Jurisdiction jurisdiction);     Task<IReadOnlyList<RawScrapeResult>> ScrapeAsync(         Jurisdiction jurisdiction, CancellationToken ct = default); } 

The Sanitizer helper normalises raw rate strings like "6.25%", "0.0625", or "$0.231/pack" to decimal?.

The ScrapeSchedulerService (IHostedService) re-scrapes each jurisdiction on the configured cadence (default_update_frequency_days). After each run, DiffEngine compares the new rate to IsCurrent=true and writes a ChangeLogEntry if they differ.

Adding a New State Scraper #

1. Create TaxRateCollector.Infrastructure/Scrapers/Strategies/MyStateScraper.cs
2. Implement IScrapeStrategy: set StrategyKey, implement CanHandle (return true when jurisdiction.StateCode == "XX"), implement ScrapeAsync
3. Register in Program.cs:

   builder.Services.AddScoped<IScrapeStrategy, MyStateScraper>(); 

4. Set Jurisdiction.SourceUrl for the state row to the canonical .gov source URL (via Settings → Jurisdictions or the Setup source-URL step)
5. Write a unit test verifying parser output against a fixture response

Roles & Subscriptions #

ASP.NET Core Identity is implemented with two roles: Administrator and Subscriber.

• Admin — full read/write access to rates, evidence, setup pipeline, settings
• Subscriber — read-only access to rate data, filtered by subscribed states
• Guest — public view, rates redacted behind a "🔒 Locked" blur

The ViewAsService (singleton per user session) lets admins preview the UI as any role without logging out. The role picker overlay (lower-right corner, admin-only) switches between Visitor / Subscriber / Admin preview modes.

Subscription model: Pay-per-county at ~$0.01/county/month. Subscribers select states they need; billing is calculated from the county count in those states. PayPal handles checkout. PricingConfig.PricePerState is the admin-configurable unit price.

Deploying to Azure #

Dockerfile #

FROM mcr.microsoft.com/dotnet/aspnet:10.0 AS base WORKDIR /app EXPOSE 8080  FROM mcr.microsoft.com/dotnet/sdk:10.0 AS build WORKDIR /src COPY . . RUN dotnet publish TaxRateCollector.Blazor -c Release -o /app/publish  FROM base AS final COPY --from=build /app/publish . ENTRYPOINT ["dotnet", "TaxRateCollector.Blazor.dll"] 

Azure App Service #

az login az group create --name rg-taxratecollector --location eastus az appservice plan create --name asp-taxratecollector \   --resource-group rg-taxratecollector --sku B1 --is-linux az webapp create --name taxratecollector \   --resource-group rg-taxratecollector \   --plan asp-taxratecollector --runtime "DOTNETCORE:10.0" 

Store the SQL Server connection string in Azure Key Vault and reference it via builder.Configuration.GetConnectionString("DefaultConnection"). The app already uses UseSqlServer — no code changes needed.

License #

Internal tool — MindAttic proprietary. Not for public distribution.