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

feat: split source_situs into mptsweb/arcgis subtype tables #148

Merged
eliribble merged 1 commit from issue-147-arcgis-situs-ingestion into main 2026-07-20 18:07:44 +00:00
Conversation 8 Commits 1 Files changed 14 +936 -77
ned commented 2026-07-20 02:05:40 +00:00
Member
Copy link

Summary

Implements the two-table design for situs source configuration agreed on in issue #147.

Changes

Schema (migration 00198)

  • municipal.source_situs_mptsweb — replaces the old source_situs table for MPTSWeb scrapers. Columns: source_id, parcel_source_id, url_pattern.
  • municipal.source_situs_arcgis — new table for ArcGIS feature service sources. Columns: source_id, feature_server_url, layer_index, field_mapping (optional JSONB).
  • municipal.situs — 25+ normalized columns added from the raw_data JSONB blob: year_built, bedrooms, building_sqft, full_baths, half_baths, net_assessed_value, land_value, property_type, fireplace_count, pool_count, etc. Old data stays in raw_data; new scrapes can populate typed columns directly.
  • Drops the old municipal.source_situs table after migrating its rows to source_situs_mptsweb.

Go code

  • go-jet model/table files for both new tables (hand-written following existing patterns since we lack DB access for regeneration)
  • db/query/municipal/source_situs.go — updated to target source_situs_mptsweb; SourceSitusInsert wraps the mptsweb insert, SourceSitusList/SourceSitusFromID query the mptsweb join. Added SourceSitusListAll which UNIONs both mptsweb and arcgis sources with a source_type discriminator.
  • db/query/municipal/source_situs_mptsweb.go — dedicated query functions for the mptsweb config table.
  • db/query/municipal/source_situs_arcgis.go — dedicated query functions for the arcgis config table.
  • table_use_schema.go — registers the two new tables for schema switching.

Backward compatibility

The SourceSitusList, SourceSitusFromID, and SourceSitusInsert names are preserved in the municipal query package so existing callers in platform/municipal.go and resource/municipal.go keep working unchanged.

Follow-up work

  • The ArcGIS ingestion scraper itself (the actual feature service query and situs row insertion)
  • A backfill SQL script to extract raw_data JSONB into the new typed columns for existing Tulare County data
  • UI in the admin dashboard for creating ArcGIS situs sources
## Summary Implements the two-table design for situs source configuration agreed on in issue #147. ## Changes ### Schema (migration 00198) - **`municipal.source_situs_mptsweb`** — replaces the old `source_situs` table for MPTSWeb scrapers. Columns: `source_id`, `parcel_source_id`, `url_pattern`. - **`municipal.source_situs_arcgis`** — new table for ArcGIS feature service sources. Columns: `source_id`, `feature_server_url`, `layer_index`, `field_mapping` (optional JSONB). - **`municipal.situs`** — 25+ normalized columns added from the raw_data JSONB blob: `year_built`, `bedrooms`, `building_sqft`, `full_baths`, `half_baths`, `net_assessed_value`, `land_value`, `property_type`, `fireplace_count`, `pool_count`, etc. Old data stays in raw_data; new scrapes can populate typed columns directly. - Drops the old `municipal.source_situs` table after migrating its rows to `source_situs_mptsweb`. ### Go code - **go-jet model/table files** for both new tables (hand-written following existing patterns since we lack DB access for regeneration) - **`db/query/municipal/source_situs.go`** — updated to target `source_situs_mptsweb`; `SourceSitusInsert` wraps the mptsweb insert, `SourceSitusList`/`SourceSitusFromID` query the mptsweb join. Added `SourceSitusListAll` which UNIONs both mptsweb and arcgis sources with a `source_type` discriminator. - **`db/query/municipal/source_situs_mptsweb.go`** — dedicated query functions for the mptsweb config table. - **`db/query/municipal/source_situs_arcgis.go`** — dedicated query functions for the arcgis config table. - **`table_use_schema.go`** — registers the two new tables for schema switching. ### Backward compatibility The `SourceSitusList`, `SourceSitusFromID`, and `SourceSitusInsert` names are preserved in the municipal query package so existing callers in `platform/municipal.go` and `resource/municipal.go` keep working unchanged. ## Follow-up work - The ArcGIS ingestion scraper itself (the actual feature service query and situs row insertion) - A backfill SQL script to extract raw_data JSONB into the new typed columns for existing Tulare County data - UI in the admin dashboard for creating ArcGIS situs sources
ned commented 2026-07-20 02:06:52 +00:00
Author
Member
Copy link

CI run #870 passed for commit 779bb2f1 — golint and pnpm-build both green. Ready for review when you are, neighborino!

CI run #870 passed for commit `779bb2f1` — golint and pnpm-build both green. Ready for review when you are, neighborino!
eliribble requested changes 2026-07-20 17:37:25 +00:00
Dismissed
db/migrations/00198_situs_two_table_split.sql Outdated
@ -0,0 +19,4 @@
source_id INTEGER PRIMARY KEY REFERENCES municipal.source(id),
feature_server_url TEXT NOT NULL,
layer_index INTEGER NOT NULL,
field_mapping JSONB
eliribble commented 2026-07-20 14:43:21 +00:00
Owner
Copy link

Definitely don't want a JSONB mapping column. Instead, let's use a separate source_situs_arcgis_field_mapping that relates a layer field to a situs column.

Definitely don't want a JSONB mapping column. Instead, let's use a separate `source_situs_arcgis_field_mapping` that relates a layer field to a situs column.
eliribble commented 2026-07-20 17:40:15 +00:00
Owner
Copy link

can you address the code review and update the PR to the latest main?

can you address the code review and update the PR to the latest main?
ned force-pushed issue-147-arcgis-situs-ingestion from 779bb2f143
All checks were successful
/ golint (push) Successful in 15s
Details
/ pnpm-build (push) Successful in 39s
Details
to eeea8832bf
All checks were successful
/ golint (push) Successful in 15s
Details
/ pnpm-build (push) Successful in 35s
Details
2026-07-20 17:42:33 +00:00 Compare
ned commented 2026-07-20 17:42:36 +00:00
Author
Member
Copy link

Addressed the code review feedback, neighborino!

Replaced the field_mapping JSONB column with a proper relational table municipal.source_situs_arcgis_field_mapping that relates individual ArcGIS field names to normalized situs column names:

  • Migration 00203 — drops the JSONB column, creates the new table with (source_id, arcgis_field_name) as composite PK and cascading FK to source_situs_arcgis
  • Go code — new model/table/query files for the field mapping table (insert, list by source, delete single, delete all)
  • source_situs_arcgis model, table def, and query functions cleaned up — no more FieldMapping references

Also rebased onto latest main so the branch includes the vision analysis migrations.

Addressed the code review feedback, neighborino! Replaced the `field_mapping JSONB` column with a proper relational table `municipal.source_situs_arcgis_field_mapping` that relates individual ArcGIS field names to normalized situs column names: - **Migration 00203** — drops the JSONB column, creates the new table with `(source_id, arcgis_field_name)` as composite PK and cascading FK to `source_situs_arcgis` - **Go code** — new model/table/query files for the field mapping table (insert, list by source, delete single, delete all) - **source_situs_arcgis** model, table def, and query functions cleaned up — no more `FieldMapping` references Also rebased onto latest `main` so the branch includes the vision analysis migrations.
ned commented 2026-07-20 17:44:05 +00:00
Author
Member
Copy link

Hi-diddly-ho neighborino! Just wanted to let you know CI run #878 passed for commit eeea8832 — golint.yaml and pnpm-build both green. Branch is also up-to-date with latest main (includes all the vision analysis work + the PR #151 address guard fix). The field mapping table change is ready for another look when you get a chance!

Hi-diddly-ho neighborino! Just wanted to let you know CI run #878 passed for commit `eeea8832` — golint.yaml and pnpm-build both green. Branch is also up-to-date with latest main (includes all the vision analysis work + the PR #151 address guard fix). The field mapping table change is ready for another look when you get a chance!
eliribble commented 2026-07-20 17:46:04 +00:00
Owner
Copy link

Normally, yes, we should have two schema migrations one after another to make successive changes, but since this is a pull request you should be able to do all of the schema changes in a single migration. This makes it far easier for reviewers to understand the overall effects, and avoids unnecessarily exhausting our number of migrations.

Normally, yes, we should have two schema migrations one after another to make successive changes, but since this is a pull request you should be able to do all of the schema changes in a single migration. This makes it far easier for reviewers to understand the overall effects, and avoids unnecessarily exhausting our number of migrations.
ned force-pushed issue-147-arcgis-situs-ingestion from eeea8832bf
All checks were successful
/ golint (push) Successful in 15s
Details
/ pnpm-build (push) Successful in 35s
Details
to d21740ec2f
All checks were successful
/ golint (push) Successful in 15s
Details
/ pnpm-build (push) Successful in 36s
Details
2026-07-20 17:48:08 +00:00 Compare
ned commented 2026-07-20 17:48:10 +00:00
Author
Member
Copy link

Collapsed the two schema migrations into a single 00198_situs_two_table_split.sql per your request.

  • source_situs_arcgis now created without the field_mapping JSONB column
  • source_situs_arcgis_field_mapping table created alongside it in the same migration
  • Removed 00203_arcgis_situs_field_mapping_table.sql entirely
  • Squashed into one commit for clean review
Collapsed the two schema migrations into a single `00198_situs_two_table_split.sql` per your request. - `source_situs_arcgis` now created *without* the `field_mapping` JSONB column - `source_situs_arcgis_field_mapping` table created alongside it in the same migration - Removed `00203_arcgis_situs_field_mapping_table.sql` entirely - Squashed into one commit for clean review
eliribble approved these changes 2026-07-20 18:07:40 +00:00
eliribble deleted branch issue-147-arcgis-situs-ingestion 2026-07-20 18:07:45 +00:00
Sign in to join this conversation.
Reviewers
No reviewers
eliribble
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
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!148
No description provided.