Gleipnir/nidus-sync
Gleipnir/nidus-sync
7
0
Fork 0
Code Issues 54 Pull requests 3 Projects Releases Packages Wiki Activity Actions

Situs data alternative ingestion #147

New issue
Open
opened 2026-07-20 01:11:14 +00:00 by eliribble · 5 comments
eliribble commented 2026-07-20 01:11:14 +00:00
Owner
Copy link

Right now we have a simple system for ingesting situs data in nidus sync. Essentially we provide a URL pattern and it does a web scrape against the website using the external ID (APN). This works against a vendor, mptsweb, which so far has been the vendor for the situs data of the only county we've cared about (Tulare).

We've now interested in getting situs data for a new customer - Kings County, CA. They actually provide all of the situs data that we need through ArcGIS features. This is great - it's way better and faster that scraping mptsweb data. However, we need to design a way to scrape this data.

I'd like you to design changes to our database schema to support our broadening situs sources. We'll need some way of identifying mptsweb scrapers as one way of getting situs data, and arcgis as another. For ArcGIS we'll need to identy a feature server, layer index, and mappings for the various fields we care about. Right now from our latest situs data scrapes we have the following:

nidus-sync=> SELECT
    kv.key AS property_name,
    COUNT(*) AS row_count
FROM municipal.situs s
CROSS JOIN LATERAL jsonb_object_keys(s.raw_data) AS kv(key)
GROUP BY kv.key
ORDER BY row_count DESC;
        property_name        | row_count
-----------------------------+-----------
 Unit Seq. Number            |    168134
 Tax Rate Area(TRA)          |    168134
 Assessment Number           |    168134
 Garage Size                 |    168134
 Lot Size(SqFt)              |    168134
 Assessor Parcel Number(APN) |    168134
 Lot Size(Acres)             |    168134
 Asmt Status                 |    168134
 Current Doc Num             |    168134
 Current Document  Date      |    168134
 Current Document Number     |    168134
 Asmt Description            |    168132
 Year Built                  |    168126
 Bedrooms                    |    168126
 Building Code               |    168126
 Building Seq. Number        |    168126
 Building Square Footage     |    168126
 Full Baths                  |    168126
 Half Baths                  |    168126
 Number of  units            |    168126
 UnFinished Square Footage   |    168126
 Property Type               |    168117
 Manufactured Homes          |    161283
 Net Assessed Value          |    161283
 Fixtures Personal Property  |    161283
 Other Exemptions            |    161283
 Personal Property           |    161283
 Homeowners Exemption(HOX)   |    161283
 Total land & Improvements   |    161283
 Land                        |    161283
 Growing Imprv.              |    161283
 Structural Imprv            |    161283
 Fixtures Real Property      |    161283
 SitusAddr                   |    133554
 FirePlaces                  |     52456
 Pools                       |     20668

It would be best to move this schema out of a single JSONB field and into something far more native schema with proper data types. We'll want to continue to keep track of unstructured data so that we can pull everything the county assesor makes available, but steadily as we go we can normalize the data and standardize how we keep it to make queries easier.

Anyway, take a look at our existing patterns, propose a new database schema and a migration path. The goal ultimately will be to tie together parcels, addresses, and situs information. As we get more customers, we may also need to develop more ways of ingesting the situs data they keep.

Right now we have a simple system for ingesting situs data in nidus sync. Essentially we provide a URL pattern and it does a web scrape against the website using the external ID (APN). This works against a vendor, mptsweb, which so far has been the vendor for the situs data of the only county we've cared about (Tulare). We've now interested in getting situs data for a new customer - Kings County, CA. They actually provide all of the situs data that we need through ArcGIS features. This is great - it's way better and faster that scraping mptsweb data. However, we need to design a way to scrape this data. I'd like you to design changes to our database schema to support our broadening situs sources. We'll need some way of identifying mptsweb scrapers as one way of getting situs data, and arcgis as another. For ArcGIS we'll need to identy a feature server, layer index, and mappings for the various fields we care about. Right now from our latest situs data scrapes we have the following: ``` nidus-sync=> SELECT kv.key AS property_name, COUNT(*) AS row_count FROM municipal.situs s CROSS JOIN LATERAL jsonb_object_keys(s.raw_data) AS kv(key) GROUP BY kv.key ORDER BY row_count DESC; property_name | row_count -----------------------------+----------- Unit Seq. Number | 168134 Tax Rate Area(TRA) | 168134 Assessment Number | 168134 Garage Size | 168134 Lot Size(SqFt) | 168134 Assessor Parcel Number(APN) | 168134 Lot Size(Acres) | 168134 Asmt Status | 168134 Current Doc Num | 168134 Current Document Date | 168134 Current Document Number | 168134 Asmt Description | 168132 Year Built | 168126 Bedrooms | 168126 Building Code | 168126 Building Seq. Number | 168126 Building Square Footage | 168126 Full Baths | 168126 Half Baths | 168126 Number of units | 168126 UnFinished Square Footage | 168126 Property Type | 168117 Manufactured Homes | 161283 Net Assessed Value | 161283 Fixtures Personal Property | 161283 Other Exemptions | 161283 Personal Property | 161283 Homeowners Exemption(HOX) | 161283 Total land & Improvements | 161283 Land | 161283 Growing Imprv. | 161283 Structural Imprv | 161283 Fixtures Real Property | 161283 SitusAddr | 133554 FirePlaces | 52456 Pools | 20668 ``` It would be best to move this schema out of a single JSONB field and into something far more native schema with proper data types. We'll want to continue to keep track of unstructured data so that we can pull everything the county assesor makes available, but steadily as we go we can normalize the data and standardize how we keep it to make queries easier. Anyway, take a look at our existing patterns, propose a new database schema and a migration path. The goal ultimately will be to tie together parcels, addresses, and situs information. As we get more customers, we may also need to develop more ways of ingesting the situs data they keep.
ned commented 2026-07-20 01:12:41 +00:00
Member
Copy link

Did a thorough walk of the existing codebase — here's my assessment and proposal.

Current State Assessment

What exists today

Two separate source types, two separate tables, two different ingestion flows:

Aspect Parcels (ArcGIS) Situs (MPTSWeb scrape)
Source table municipal.source_parcel municipal.source + municipal.source_situs
ArcGIS fields On source_parcel (service_feature_item_id, layer_index, field mappings) None (only URL pattern for scraping)
Sync mechanism Queries ArcGIS Feature Service with pagination Replaces {external_id} in URL pattern, scrapes HTML
Data storage municipal.parcel (well-typed columns + geometry) municipal.situs (a few typed columns + raw_data JSONB)

Key observations:

  1. municipal.source is already a generic parent table with common metadata (created, url, region, county, etc.) — good foundation
  2. municipal.source_situs currently only tracks the MPTSWeb scraping config (url_pattern, parcel_source_id)
  3. municipal.source_parcel (ArcGIS) has the arcgis reference columns but they're not on municipal.source itself
  4. The municipal.situs table has a mix of typed columns + raw_data JSONB — the typed columns have been added incrementally in migrations 00177 and 00179
  5. The JSONB raw_data holds 35+ fields from Tulare County's assessor that haven't been normalized yet

Proposed Schema Design

Goal: Single situs source abstraction supporting both MPTSWeb and ArcGIS

Step 1: Add a source_type to municipal.source_situs

CREATE TYPE municipal.situs_source_type AS ENUM (
    'mptsweb',
    'arcgis'
);

ALTER TABLE municipal.source_situs
    ADD COLUMN source_type municipal.situs_source_type NOT NULL DEFAULT 'mptsweb',
    ADD COLUMN arcgis_service_feature_item_id TEXT,
    ADD COLUMN arcgis_layer_index INTEGER,
    -- Field mappings for ArcGIS situs features (which field in the feature maps to which situs property)
    ADD COLUMN arcgis_field_apn TEXT,
    ADD COLUMN arcgis_field_situs_address TEXT,
    ADD COLUMN arcgis_field_year_built TEXT,
    ADD COLUMN arcgis_field_bedrooms TEXT,
    ADD COLUMN arcgis_field_bathrooms_full TEXT,
    ADD COLUMN arcgis_field_bathrooms_half TEXT,
    ADD COLUMN arcgis_field_building_sqft TEXT,
    ADD COLUMN arcgis_field_lot_sqft TEXT,
    ADD COLUMN arcgis_field_lot_acres TEXT,
    ADD COLUMN arcgis_field_property_type TEXT,
    ADD COLUMN arcgis_field_assessed_value TEXT,
    ADD COLUMN arcgis_field_garage_size TEXT,
    ADD COLUMN arcgis_field_pools TEXT,
    ADD COLUMN arcgis_field_fireplaces TEXT,
    -- Foreign key to arcgis.layer
    ADD FOREIGN KEY (arcgis_service_feature_item_id, arcgis_layer_index)
        REFERENCES arcgis.layer(feature_service_item_id, index_);

COMMENT ON COLUMN municipal.source_situs.source_type IS 'Identifies how situs data is acquired: mptsweb (HTML scrape) or arcgis (feature service query)';
COMMENT ON COLUMN municipal.source_situs.arcgis_field_apn IS 'ArcGIS field name that maps to Assessor Parcel Number (APN)';

Rationale for field mappings on source_situs vs. a separate mapping table: The situs fields are well-known from the data Eli showed (36 fields). A dedicated field-per-column approach is type-safe, queryable, and follows the existing pattern from municipal.source_parcel (which uses arcgis_layer_field_mapping_external_id_name, etc.). If the field set grows substantially we could revisit a mapping table, but right now this is cleaner.

Step 2: Normalize municipal.situs columns

The existing raw_data JSONB has 36 fields. We should add typed columns for the ones most commonly queried, while keeping raw_data for everything else:

ALTER TABLE municipal.situs
    -- Numeric assessment fields
    ADD COLUMN year_built INTEGER,
    ADD COLUMN bedrooms INTEGER,
    ADD COLUMN full_baths INTEGER,
    ADD COLUMN half_baths INTEGER,
    ADD COLUMN building_sqft INTEGER,
    ADD COLUMN unfinished_sqft INTEGER,
    ADD COLUMN lot_sqft DOUBLE PRECISION,
    ADD COLUMN lot_acres DOUBLE PRECISION,
    ADD COLUMN garage_size TEXT,
    ADD COLUMN pools INTEGER,
    ADD COLUMN fireplaces INTEGER,
    ADD COLUMN manufactured_homes INTEGER,
    -- Value fields
    ADD COLUMN land_value NUMERIC(12,2),
    ADD COLUMN improvement_value NUMERIC(12,2),
    ADD COLUMN total_value NUMERIC(12,2),
    ADD COLUMN personal_property_value NUMERIC(12,2),
    ADD COLUMN fixtures_real_property_value NUMERIC(12,2),
    ADD COLUMN fixtures_personal_property_value NUMERIC(12,2),
    ADD COLUMN homeowners_exemption_value NUMERIC(12,2),
    ADD COLUMN other_exemptions_value NUMERIC(12,2),
    -- Misc
    ADD COLUMN tax_rate_area TEXT,
    ADD COLUMN assessment_number TEXT,
    ADD COLUMN property_type TEXT,
    ADD COLUMN unit_sequence_number TEXT,
    ADD COLUMN building_sequence_number TEXT,
    ADD COLUMN building_code TEXT,
    ADD COLUMN number_of_units INTEGER,
    ADD COLUMN current_document_number TEXT;

Step 3: Link to parcels properly

municipal.situs already has external_id (which maps to APN) and parcel_id (document number). The external_id should become a proper FK to municipal.parcel.external_id (or to the unified concept). We need a FK relationship:

-- Add a FK from situs to parcel (or to a unified lot/parcel concept)
-- Parcels use external_id (APN), situs uses external_id (APN) — same column, same values
ALTER TABLE municipal.situs
    ADD COLUMN parcel_id_ref INTEGER REFERENCES municipal.parcel(id),
    ADD COLUMN address_id INTEGER REFERENCES public.address(id);

But this assumes parcel sync has already run. The situs sync currently walks parcels by looking for situs records that don't exist yet — we'd need to change that to a join-based approach.

Migration Strategy

Phase 1: Schema expansion (no breaking changes)

  1. Migration to add source_type and arcgis columns to municipal.source_situs — existing MPTSWeb sources get 'mptsweb' with no arcgis fields
  2. Migration to add normalized columns to municipal.situs — all nullable, existing data unaffected
  3. Backfill script: UPDATE municipal.situs SET year_built = (raw_data->>'Year Built')::integer, ... for the common fields

Phase 2: ArcGIS situs ingestion

  1. New platform function SitusArcGISSync() — parallels MunicipalJobSourceSync() but writes to municipal.situs instead of municipal.parcel
  2. New background job type for arcgis situs sync (or reuse the existing municipal-situs-sync with a dispatch that checks source_type)
  3. API resource for configuring arcgis situs sources (similar to existing parcel source config UI)

Phase 3: Address linking (future work)

The SitusAddr field (present in ~80% of records) should eventually link to the public.address table. But we need a geocoding step or address normalization before that's reliable. For now, storing it as a typed column is a big improvement over JSONB.

ArcGIS Situs Ingestion Flow (proposed)

SitusArcGISSync(ctx, syncID)
  ├─ Lookup source_situs (get feature service, layer index, field mappings)
  ├─ Query ArcGIS: /{layer}/query?where=1=1&outFields={all mapped fields}&returnGeometry=false&f=json
  ├─ Paginate through results (just like MunicipalJobSourceSync does for parcels)
  ├─ For each feature:
  │    ├─ Extract APN from mapped field
  │    ├─ Map all configured fields to situs table columns
  │    ├─ Store any unmapped fields in raw_data JSONB
  │    └─ INSERT or UPSERT into municipal.situs
  └─ Mark sync complete

How this scales for more customers

With source_type on municipal.source_situs, adding a third ingestion method is just:

  1. Add a new enum value (e.g., 'csv_upload', 'api')
  2. Add any method-specific config columns to source_situs
  3. Write a new sync function and dispatch from the job handler

No schema redesign needed each time.

I'll wait for feedback before starting implementation. The biggest open questions before I code:

  1. ArcGIS field mapping granularity — should I go with per-column mappings on source_situs (as proposed), or a separate municipal.situs_field_mapping table with a destination enum? The former is simpler for the known 14-20 common fields; the latter is more extensible.

  2. Parcel linking — should municipal.situs.external_id become a proper FK to municipal.parcel.external_id, or should situs remain somewhat independent (since not all situs records may have a matching parcel)?

  3. Kings County specifics — do we know the ArcGIS Feature Service URL and layer for Kings County situs data yet? That'll inform the field mapping design.

Did a thorough walk of the existing codebase — here's my assessment and proposal. ## Current State Assessment ### What exists today **Two separate source types, two separate tables, two different ingestion flows:** | Aspect | Parcels (ArcGIS) | Situs (MPTSWeb scrape) | |--------|-----------------|----------------------| | Source table | `municipal.source_parcel` | `municipal.source` + `municipal.source_situs` | | ArcGIS fields | On `source_parcel` (service_feature_item_id, layer_index, field mappings) | None (only URL pattern for scraping) | | Sync mechanism | Queries ArcGIS Feature Service with pagination | Replaces `{external_id}` in URL pattern, scrapes HTML | | Data storage | `municipal.parcel` (well-typed columns + geometry) | `municipal.situs` (a few typed columns + `raw_data` JSONB) | **Key observations:** 1. `municipal.source` is already a generic parent table with common metadata (created, url, region, county, etc.) — good foundation 2. `municipal.source_situs` currently only tracks the MPTSWeb scraping config (url_pattern, parcel_source_id) 3. `municipal.source_parcel` (ArcGIS) has the arcgis reference columns but they're **not** on `municipal.source` itself 4. The `municipal.situs` table has a mix of typed columns + raw_data JSONB — the typed columns have been added incrementally in migrations 00177 and 00179 5. The JSONB `raw_data` holds 35+ fields from Tulare County's assessor that haven't been normalized yet ## Proposed Schema Design ### Goal: Single situs source abstraction supporting both MPTSWeb and ArcGIS ### Step 1: Add a `source_type` to `municipal.source_situs` ```sql CREATE TYPE municipal.situs_source_type AS ENUM ( 'mptsweb', 'arcgis' ); ALTER TABLE municipal.source_situs ADD COLUMN source_type municipal.situs_source_type NOT NULL DEFAULT 'mptsweb', ADD COLUMN arcgis_service_feature_item_id TEXT, ADD COLUMN arcgis_layer_index INTEGER, -- Field mappings for ArcGIS situs features (which field in the feature maps to which situs property) ADD COLUMN arcgis_field_apn TEXT, ADD COLUMN arcgis_field_situs_address TEXT, ADD COLUMN arcgis_field_year_built TEXT, ADD COLUMN arcgis_field_bedrooms TEXT, ADD COLUMN arcgis_field_bathrooms_full TEXT, ADD COLUMN arcgis_field_bathrooms_half TEXT, ADD COLUMN arcgis_field_building_sqft TEXT, ADD COLUMN arcgis_field_lot_sqft TEXT, ADD COLUMN arcgis_field_lot_acres TEXT, ADD COLUMN arcgis_field_property_type TEXT, ADD COLUMN arcgis_field_assessed_value TEXT, ADD COLUMN arcgis_field_garage_size TEXT, ADD COLUMN arcgis_field_pools TEXT, ADD COLUMN arcgis_field_fireplaces TEXT, -- Foreign key to arcgis.layer ADD FOREIGN KEY (arcgis_service_feature_item_id, arcgis_layer_index) REFERENCES arcgis.layer(feature_service_item_id, index_); COMMENT ON COLUMN municipal.source_situs.source_type IS 'Identifies how situs data is acquired: mptsweb (HTML scrape) or arcgis (feature service query)'; COMMENT ON COLUMN municipal.source_situs.arcgis_field_apn IS 'ArcGIS field name that maps to Assessor Parcel Number (APN)'; ``` **Rationale for field mappings on source_situs vs. a separate mapping table:** The situs fields are well-known from the data Eli showed (36 fields). A dedicated field-per-column approach is type-safe, queryable, and follows the existing pattern from `municipal.source_parcel` (which uses `arcgis_layer_field_mapping_external_id_name`, etc.). If the field set grows substantially we could revisit a mapping table, but right now this is cleaner. ### Step 2: Normalize `municipal.situs` columns The existing `raw_data` JSONB has 36 fields. We should add typed columns for the ones most commonly queried, while keeping `raw_data` for everything else: ```sql ALTER TABLE municipal.situs -- Numeric assessment fields ADD COLUMN year_built INTEGER, ADD COLUMN bedrooms INTEGER, ADD COLUMN full_baths INTEGER, ADD COLUMN half_baths INTEGER, ADD COLUMN building_sqft INTEGER, ADD COLUMN unfinished_sqft INTEGER, ADD COLUMN lot_sqft DOUBLE PRECISION, ADD COLUMN lot_acres DOUBLE PRECISION, ADD COLUMN garage_size TEXT, ADD COLUMN pools INTEGER, ADD COLUMN fireplaces INTEGER, ADD COLUMN manufactured_homes INTEGER, -- Value fields ADD COLUMN land_value NUMERIC(12,2), ADD COLUMN improvement_value NUMERIC(12,2), ADD COLUMN total_value NUMERIC(12,2), ADD COLUMN personal_property_value NUMERIC(12,2), ADD COLUMN fixtures_real_property_value NUMERIC(12,2), ADD COLUMN fixtures_personal_property_value NUMERIC(12,2), ADD COLUMN homeowners_exemption_value NUMERIC(12,2), ADD COLUMN other_exemptions_value NUMERIC(12,2), -- Misc ADD COLUMN tax_rate_area TEXT, ADD COLUMN assessment_number TEXT, ADD COLUMN property_type TEXT, ADD COLUMN unit_sequence_number TEXT, ADD COLUMN building_sequence_number TEXT, ADD COLUMN building_code TEXT, ADD COLUMN number_of_units INTEGER, ADD COLUMN current_document_number TEXT; ``` ### Step 3: Link to parcels properly `municipal.situs` already has `external_id` (which maps to APN) and `parcel_id` (document number). The `external_id` should become a proper FK to `municipal.parcel.external_id` (or to the unified concept). We need a FK relationship: ```sql -- Add a FK from situs to parcel (or to a unified lot/parcel concept) -- Parcels use external_id (APN), situs uses external_id (APN) — same column, same values ALTER TABLE municipal.situs ADD COLUMN parcel_id_ref INTEGER REFERENCES municipal.parcel(id), ADD COLUMN address_id INTEGER REFERENCES public.address(id); ``` But this assumes parcel sync has already run. The situs sync currently walks parcels by looking for situs records that don't exist yet — we'd need to change that to a join-based approach. ## Migration Strategy ### Phase 1: Schema expansion (no breaking changes) 1. Migration to add `source_type` and arcgis columns to `municipal.source_situs` — existing MPTSWeb sources get `'mptsweb'` with no arcgis fields 2. Migration to add normalized columns to `municipal.situs` — all nullable, existing data unaffected 3. Backfill script: `UPDATE municipal.situs SET year_built = (raw_data->>'Year Built')::integer, ...` for the common fields ### Phase 2: ArcGIS situs ingestion 1. New platform function `SitusArcGISSync()` — parallels `MunicipalJobSourceSync()` but writes to `municipal.situs` instead of `municipal.parcel` 2. New background job type for arcgis situs sync (or reuse the existing `municipal-situs-sync` with a dispatch that checks `source_type`) 3. API resource for configuring arcgis situs sources (similar to existing parcel source config UI) ### Phase 3: Address linking (future work) The `SitusAddr` field (present in ~80% of records) should eventually link to the public.address table. But we need a geocoding step or address normalization before that's reliable. For now, storing it as a typed column is a big improvement over JSONB. ## ArcGIS Situs Ingestion Flow (proposed) ``` SitusArcGISSync(ctx, syncID) ├─ Lookup source_situs (get feature service, layer index, field mappings) ├─ Query ArcGIS: /{layer}/query?where=1=1&outFields={all mapped fields}&returnGeometry=false&f=json ├─ Paginate through results (just like MunicipalJobSourceSync does for parcels) ├─ For each feature: │ ├─ Extract APN from mapped field │ ├─ Map all configured fields to situs table columns │ ├─ Store any unmapped fields in raw_data JSONB │ └─ INSERT or UPSERT into municipal.situs └─ Mark sync complete ``` ## How this scales for more customers With `source_type` on `municipal.source_situs`, adding a third ingestion method is just: 1. Add a new enum value (e.g., `'csv_upload'`, `'api'`) 2. Add any method-specific config columns to `source_situs` 3. Write a new sync function and dispatch from the job handler No schema redesign needed each time. --- I'll wait for feedback before starting implementation. The biggest open questions before I code: 1. **ArcGIS field mapping granularity** — should I go with per-column mappings on `source_situs` (as proposed), or a separate `municipal.situs_field_mapping` table with a destination enum? The former is simpler for the known 14-20 common fields; the latter is more extensible. 2. **Parcel linking** — should `municipal.situs.external_id` become a proper FK to `municipal.parcel.external_id`, or should situs remain somewhat independent (since not all situs records may have a matching parcel)? 3. **Kings County specifics** — do we know the ArcGIS Feature Service URL and layer for Kings County situs data yet? That'll inform the field mapping design.
eliribble commented 2026-07-20 01:45:39 +00:00
Author
Owner
Copy link

Thanks, solid report and proposal.

I don't love that the municipal.source_situstable is going to end up with a bunch of fields related to arcgis when, for mptsweb they'll all be null.

What if we were to create two separate tables, municipal.source_situs_mptsweb and municipal.source_situs_arcgis. Then we could have schema that is specific to each use case. Can you write up what it would look like with two separate tables, and how migration would be handled?

Thanks, solid report and proposal. I don't love that the `municipal.source_situs`table is going to end up with a bunch of fields related to arcgis when, for mptsweb they'll all be null. What if we were to create two separate tables, `municipal.source_situs_mptsweb` and `municipal.source_situs_arcgis`. Then we could have schema that is specific to each use case. Can you write up what it would look like with two separate tables, and how migration would be handled?
ned commented 2026-07-20 01:48:11 +00:00
Member
Copy link

Response to feedback: Two-table design

Good call, neighborino. A single table with half the columns always null is not diddly-doodley clean. Let me lay out the two-table approach.

The Two-Table Design

municipal.source_situs_mptsweb — for MPTSWeb HTML scraping sources

CREATE TABLE municipal.source_situs_mptsweb (
    source_id INTEGER PRIMARY KEY REFERENCES municipal.source(id),
    parcel_source_id INTEGER REFERENCES municipal.source_parcel(source_id),
    url_pattern TEXT NOT NULL  -- contains {external_id} placeholder
);
COMMENT ON TABLE municipal.source_situs_mptsweb IS
    'A situs source that scrapes HTML from an MPTSWeb-style URL pattern.';
COMMENT ON COLUMN municipal.source_situs_mptsweb.url_pattern IS
    'URL template with {external_id} replaced by the parcel APN.';

This is essentially the current source_situs table minus the PK rename. Nothing changes for the existing Tulare County workflow — every existing row in source_situs migrates straight in.

municipal.source_situs_arcgis — for ArcGIS Feature Service sources

CREATE TABLE municipal.source_situs_arcgis (
    source_id INTEGER PRIMARY KEY REFERENCES municipal.source(id),
    parcel_source_id INTEGER REFERENCES municipal.source_parcel(source_id),
    
    -- ArcGIS Feature Service identification
    feature_service_item_id TEXT NOT NULL,
    layer_index INTEGER NOT NULL,
    
    -- Field mappings: which ArcGIS attribute maps to which situs property
    -- Following the existing source_parcel naming convention
    field_mapping_apn TEXT NOT NULL,
    field_mapping_situs_address TEXT,
    field_mapping_year_built TEXT,
    field_mapping_bedrooms TEXT,
    field_mapping_full_baths TEXT,
    field_mapping_half_baths TEXT,
    field_mapping_building_sqft TEXT,
    field_mapping_unfinished_sqft TEXT,
    field_mapping_lot_sqft TEXT,
    field_mapping_lot_acres TEXT,
    field_mapping_garage_size TEXT,
    field_mapping_property_type TEXT,
    field_mapping_land_value TEXT,
    field_mapping_improvement_value TEXT,
    field_mapping_total_value TEXT,
    field_mapping_assessment_type TEXT,
    field_mapping_assessment_description TEXT,
    field_mapping_assessment_status TEXT,
    field_mapping_document_date TEXT,
    field_mapping_tax_rate_area TEXT,
    field_mapping_assessment_number TEXT,
    field_mapping_unit_sequence TEXT,
    field_mapping_building_sequence TEXT,
    field_mapping_building_code TEXT,
    field_mapping_number_of_units TEXT,
    field_mapping_manufactured_homes TEXT,
    field_mapping_pools TEXT,
    field_mapping_fireplaces TEXT,
    field_mapping_personal_property_value TEXT,
    field_mapping_fixtures_real_property TEXT,
    field_mapping_fixtures_personal_property TEXT,
    field_mapping_homeowners_exemption TEXT,
    field_mapping_other_exemptions TEXT,
    
    FOREIGN KEY (feature_service_item_id, layer_index)
        REFERENCES arcgis.layer(feature_service_item_id, index_)
);
COMMENT ON TABLE municipal.source_situs_arcgis IS
    'A situs source that pulls assessment data from an ArcGIS Feature Service.';

Why so many mapping columns? Because situs has ~36 distinct attributes and we want type-safe, SQL-queryable access to each. This follows the same principle as source_parcel.arcgis_layer_field_mapping_external_id_name. If the count gets unwieldy, we can refactor to a municipal.situs_field_mapping table later.

Dispatching: how does the sync job know which table to use?

Since both tables use source_id as their PK, a given municipal.source ID will have a row in exactly one of the two child tables. The dispatch logic checks both:

func SitusJobSync(ctx context.Context, syncID int32) error {
    sourceSync, err := muniquery.SourceSyncFromID(ctx, txn, syncID)
    // ...
    
    // Try arcgis first, fall back to mptsweb
    source, err := muniquery.SourceSitusArcgisFromID(ctx, txn, sourceSync.SourceID)
    if err == nil {
        return situsArcgisSync(ctx, txn, source)
    }
    if !errors.Is(err, db.ErrNoRows) {
        return err
    }
    
    mptswebSource, err := muniquery.SourceSitusMPTSWebFromID(ctx, txn, sourceSync.SourceID)
    if err != nil {
        return fmt.Errorf("no situs source config found for source %d", sourceSync.SourceID)
    }
    return situsMPTSWebSync(ctx, txn, mptswebSource)
}

This keeps the existing municipal-situs-sync JobType and SitusSyncStart entry point unchanged.

The situs data table stays shared

municipal.situs doesn't split. It holds the actual assessment records regardless of how they were collected. The normalized columns I proposed before still make sense — they're just facts about a property, not tied to any ingestion method:

ALTER TABLE municipal.situs ADD COLUMN ...  -- same list as before

The situs.external_id → parcel.external_id link works the same way whether the data came from a scrape or a feature query.

Migration path

Phase 1 — Create the new tables, migrate existing data

-- 1. Create source_situs_mptsweb
CREATE TABLE municipal.source_situs_mptsweb (
    source_id INTEGER PRIMARY KEY REFERENCES municipal.source(id),
    parcel_source_id INTEGER REFERENCES municipal.source_parcel(source_id),
    url_pattern TEXT NOT NULL
);

-- 2. Migrate existing source_situs rows into it
INSERT INTO municipal.source_situs_mptsweb (source_id, parcel_source_id, url_pattern)
SELECT source_id, parcel_source_id, url_pattern
FROM municipal.source_situs;

-- 3. Create source_situs_arcgis
CREATE TABLE municipal.source_situs_arcgis (
    -- all the columns from above
);
-- No data to migrate — no arcgis situs sources exist yet

-- 4. Drop the old unified table
DROP TABLE municipal.source_situs;

Zero data loss: the current source_situs has only MPTSWeb entries. Every row goes straight into source_situs_mptsweb with no transformation needed.

Phase 2 — Normalize situs data columns

Add typed columns to municipal.situs, backfill from raw_data, same as my previous proposal. This is independent of Phase 1.

Phase 3 — ArcGIS situs ingestion code

  • New query functions in db/query/municipal/: source_situs_arcgis.go (insert, list, fromID)
  • Rename existing query file db/query/municipal/source_situs.go → source_situs_mptsweb.go
  • Slight refactor of SitusJobSync() dispatch (shown above)
  • New situsArcgisSync() in platform/municipal.go following the same pagination pattern as MunicipalJobSourceSync()

How this scales

Adding a third ingestion method (CSV upload, direct API, etc.) means creating a third child table and adding a branch in the dispatch. No schema changes to existing source configs, no nullable columns polluting unrelated records.

Want me to turn this into a concrete PR — migrations, query files, and dispatch refactor — or would you like to bikeshed the arcgis field mapping list first?

## Response to feedback: Two-table design Good call, neighborino. A single table with half the columns always null is not diddly-doodley clean. Let me lay out the two-table approach. --- ## The Two-Table Design ### `municipal.source_situs_mptsweb` — for MPTSWeb HTML scraping sources ```sql CREATE TABLE municipal.source_situs_mptsweb ( source_id INTEGER PRIMARY KEY REFERENCES municipal.source(id), parcel_source_id INTEGER REFERENCES municipal.source_parcel(source_id), url_pattern TEXT NOT NULL -- contains {external_id} placeholder ); COMMENT ON TABLE municipal.source_situs_mptsweb IS 'A situs source that scrapes HTML from an MPTSWeb-style URL pattern.'; COMMENT ON COLUMN municipal.source_situs_mptsweb.url_pattern IS 'URL template with {external_id} replaced by the parcel APN.'; ``` This is essentially the current `source_situs` table minus the PK rename. Nothing changes for the existing Tulare County workflow — every existing row in `source_situs` migrates straight in. ### `municipal.source_situs_arcgis` — for ArcGIS Feature Service sources ```sql CREATE TABLE municipal.source_situs_arcgis ( source_id INTEGER PRIMARY KEY REFERENCES municipal.source(id), parcel_source_id INTEGER REFERENCES municipal.source_parcel(source_id), -- ArcGIS Feature Service identification feature_service_item_id TEXT NOT NULL, layer_index INTEGER NOT NULL, -- Field mappings: which ArcGIS attribute maps to which situs property -- Following the existing source_parcel naming convention field_mapping_apn TEXT NOT NULL, field_mapping_situs_address TEXT, field_mapping_year_built TEXT, field_mapping_bedrooms TEXT, field_mapping_full_baths TEXT, field_mapping_half_baths TEXT, field_mapping_building_sqft TEXT, field_mapping_unfinished_sqft TEXT, field_mapping_lot_sqft TEXT, field_mapping_lot_acres TEXT, field_mapping_garage_size TEXT, field_mapping_property_type TEXT, field_mapping_land_value TEXT, field_mapping_improvement_value TEXT, field_mapping_total_value TEXT, field_mapping_assessment_type TEXT, field_mapping_assessment_description TEXT, field_mapping_assessment_status TEXT, field_mapping_document_date TEXT, field_mapping_tax_rate_area TEXT, field_mapping_assessment_number TEXT, field_mapping_unit_sequence TEXT, field_mapping_building_sequence TEXT, field_mapping_building_code TEXT, field_mapping_number_of_units TEXT, field_mapping_manufactured_homes TEXT, field_mapping_pools TEXT, field_mapping_fireplaces TEXT, field_mapping_personal_property_value TEXT, field_mapping_fixtures_real_property TEXT, field_mapping_fixtures_personal_property TEXT, field_mapping_homeowners_exemption TEXT, field_mapping_other_exemptions TEXT, FOREIGN KEY (feature_service_item_id, layer_index) REFERENCES arcgis.layer(feature_service_item_id, index_) ); COMMENT ON TABLE municipal.source_situs_arcgis IS 'A situs source that pulls assessment data from an ArcGIS Feature Service.'; ``` **Why so many mapping columns?** Because situs has ~36 distinct attributes and we want type-safe, SQL-queryable access to each. This follows the same principle as `source_parcel.arcgis_layer_field_mapping_external_id_name`. If the count gets unwieldy, we can refactor to a `municipal.situs_field_mapping` table later. ### Dispatching: how does the sync job know which table to use? Since both tables use `source_id` as their PK, a given `municipal.source` ID will have a row in exactly one of the two child tables. The dispatch logic checks both: ```go func SitusJobSync(ctx context.Context, syncID int32) error { sourceSync, err := muniquery.SourceSyncFromID(ctx, txn, syncID) // ... // Try arcgis first, fall back to mptsweb source, err := muniquery.SourceSitusArcgisFromID(ctx, txn, sourceSync.SourceID) if err == nil { return situsArcgisSync(ctx, txn, source) } if !errors.Is(err, db.ErrNoRows) { return err } mptswebSource, err := muniquery.SourceSitusMPTSWebFromID(ctx, txn, sourceSync.SourceID) if err != nil { return fmt.Errorf("no situs source config found for source %d", sourceSync.SourceID) } return situsMPTSWebSync(ctx, txn, mptswebSource) } ``` This keeps the existing `municipal-situs-sync` JobType and `SitusSyncStart` entry point unchanged. --- ## The situs data table stays shared `municipal.situs` doesn't split. It holds the actual assessment records regardless of how they were collected. The normalized columns I proposed before still make sense — they're just facts about a property, not tied to any ingestion method: ```sql ALTER TABLE municipal.situs ADD COLUMN ... -- same list as before ``` The `situs.external_id` → `parcel.external_id` link works the same way whether the data came from a scrape or a feature query. --- ## Migration path ### Phase 1 — Create the new tables, migrate existing data ```sql -- 1. Create source_situs_mptsweb CREATE TABLE municipal.source_situs_mptsweb ( source_id INTEGER PRIMARY KEY REFERENCES municipal.source(id), parcel_source_id INTEGER REFERENCES municipal.source_parcel(source_id), url_pattern TEXT NOT NULL ); -- 2. Migrate existing source_situs rows into it INSERT INTO municipal.source_situs_mptsweb (source_id, parcel_source_id, url_pattern) SELECT source_id, parcel_source_id, url_pattern FROM municipal.source_situs; -- 3. Create source_situs_arcgis CREATE TABLE municipal.source_situs_arcgis ( -- all the columns from above ); -- No data to migrate — no arcgis situs sources exist yet -- 4. Drop the old unified table DROP TABLE municipal.source_situs; ``` Zero data loss: the current `source_situs` has only MPTSWeb entries. Every row goes straight into `source_situs_mptsweb` with no transformation needed. ### Phase 2 — Normalize situs data columns Add typed columns to `municipal.situs`, backfill from `raw_data`, same as my previous proposal. This is independent of Phase 1. ### Phase 3 — ArcGIS situs ingestion code - New query functions in `db/query/municipal/`: `source_situs_arcgis.go` (insert, list, fromID) - Rename existing query file `db/query/municipal/source_situs.go` → `source_situs_mptsweb.go` - Slight refactor of `SitusJobSync()` dispatch (shown above) - New `situsArcgisSync()` in `platform/municipal.go` following the same pagination pattern as `MunicipalJobSourceSync()` --- ## How this scales Adding a third ingestion method (CSV upload, direct API, etc.) means creating a third child table and adding a branch in the dispatch. No schema changes to existing source configs, no nullable columns polluting unrelated records. --- Want me to turn this into a concrete PR — migrations, query files, and dispatch refactor — or would you like to bikeshed the arcgis field mapping list first?
eliribble commented 2026-07-20 02:02:39 +00:00
Author
Owner
Copy link

I agree with you on the field mapping - we can go to municipal.situs_field_mapping if we get any more columns, but that may be a while.

Go ahead and put together a PR, then I'll review and see where we land.

I agree with you on the field mapping - we can go to `municipal.situs_field_mapping` if we get any more columns, but that may be a while. Go ahead and put together a PR, then I'll review and see where we land.
ned commented 2026-07-20 02:05:45 +00:00
Member
Copy link

PR is ready for your review: #148

The migration adds both subtype tables, migrates existing data, then drops the old unified source_situs. The situs data table gets 25+ normalized columns alongside raw_data. Backward-compat wrappers keep existing callers compiling without changes.

I held back on writing the actual ArcGIS scraper and the raw_data backfill SQL — want to get the schema nailed down first, then we can iterate on the ingestion plumbing.

PR is ready for your review: https://source.gleipnir.technology/Gleipnir/nidus-sync/pulls/148 The migration adds both subtype tables, migrates existing data, then drops the old unified source_situs. The situs data table gets 25+ normalized columns alongside raw_data. Backward-compat wrappers keep existing callers compiling without changes. I held back on writing the actual ArcGIS scraper and the raw_data backfill SQL — want to get the schema nailed down first, then we can iterate on the ingestion plumbing.
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
main
issue-164-overview-map
worried-pug
add-ts-ui-tests-action
implement-maintenance-mode
investigate-planet-mosaics-new-area-sync
upbeat-turtle-2
quiet-peacock
light-rat-2
light-rat
upbeat-turtle
reveal-ui-bundle
smart-baboon
snowy-gopher
bitter-moth
famous-mantis
evil-donkey-continued
elegant-mule
exciting-sheep
sleek-fireant
magical-armadillo
aquatic-monkey
issue-176-n-items
issue-176-reveal-ui-redesign
issue-184-nearmap-tiles
issue-183-crop-data
issue-13-train-data-api
issue-180-skip-trace-design-doc
issue-174-reveal-ui-mockup
issue-166-communication-search
issue-165-address-reporter-list-view
issue-160-h3cell-bad-data
issue-155-feature-observation-parser-v2
issue-155-feature-observation-parser
issue-152-land-use-soil-data
fix/issue-134-ci-ssh-auth
fix/golint-ci-submodule-auth
fix/golangci-lint-unstable
fix/incoming-text-missing-destination-phone
fix/csv-import-missing-srid-4326
79
78
77
76
75
74
73
72
70
50
48
46
45
_backup/pre-rewrite-tag44
44
42
41
40
39
38
37
35
34
33
v0.0.5
v0.0.4
32
31
30
29
28
27
26
25
24
v0.0.23
v0.0.22
v0.0.21
v0.0.20
v0.0.19
0.0.18
0.0.17
0.0.16
0.0.15
0.0.14
0.0.13
0.0.12
0.0.11
0.0.10
0.0.9
0.0.8
0.0.7
v0.0.6
v0.0.3
v0.0.2
v0.0.1
Labels
Clear labels
  
customer
Customers are aware of, and affected by the issue

  
prod
Issue has manifested in production

  
repro'd
Has been reproduced by engineering

  
retest
Issue is ready for retesting in staging

  
staging
Issue has manifested in staging

No labels
customer
prod
repro'd
retest
staging
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
ned
2 participants
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
Gleipnir/nidus-sync#147
No description provided.