codeNforce needs to display data about each parcel in participating municipalities (as of Summer 2021, this means Chalfant and E. McKeesport) including the following fields:
All mailing addresses associated with the parcel, including un-ranged addresses (e.g. a parcel with a county address of "2022-2030 Lark Ave" appears five separate address records linked to this parcel: 2022 Lark Ave., 2024 Lark Ave., 2026 Lark Ave., 2028 Lark Ave., and 2030 Lark Ave.)
Current parcel owner name
Boolean flag indicating the owner listed is believed by the parser to be corporate entity.
Boolean flag indicating the owner name is believed by the parser to represent two distinct humans (e.g. KOCSIS GEORGE R & CYNTHIA A (W)). Note that in the long-run, we'd like to try to automatically create a human record for George Kocsis and a sparate record for Cynthia but not yet)
Perhaps consider the Trust category: somewhere in between business and person.
Current parcel owner mailing address
Current parcel owner tax bill mailing address
Previous owners and title transfer date (or the closest we can get to an actual title change date) for the recent past (2-5 years)
Allegheny county tax status (are there any past due taxes on parcel X?)
Human data
codeNforce needs to display data about individual humans/persons (in Java land, a Person is a subclass of Human containing that Human's phone numbers, email addresses, and associated mailing addresses) including:
All mailing addresses associated with the selected human, including a relationship role descriptor, currently implemented as a FK on a linking table to a record in the linkedobjectrole table which contains schemas for all sorts of linked object roles. (e.g. codeNforce would display something like "KOCSIS GEORGE R owner of 2022 Lark Ave" and "KOCSIS GEORGE R owner of 2024 Lark Ave, etc. and maybe something like: "KOCSIS GEORGE R receives tax bills at 3348 Underwater Way, Kansas City, MO 99999". The 'owner ofand 'receives tax bills at are attributes of a record in the linkedobjectrole table.)
All parcels associated with a selected human, including historic data. E.g. "Ellen Bascomb owner of 1120 Ocean Flood Dr. until 20-JAN-2021 .
Business entities connected to the human and perhaps a link role such as KOCSIS GEORGE R same tax mailing address as EVERGREEN RENTALS LLC
Change of owner flagging
codeNforce users are very interested in automated detection of parcels which change owners because such transfers are legally required to be associated with a valid occupancy permit for the transferred parcel. In some cases, we have reason to believe properties are changing hands without the requisite occupancy permitting process occurring.
Queries for non-compliant parcel owner transfers
Our goal between Summer 2021 and Summer 2022 is to have the occupancy UI and parcel data in decent enough shape that we can periodically query the database asking "Find me all parcels in municipality C which have changed owners in the past 30 days and are NOT associated with an occupancy period of type Change of owner.
Owner change dates
Given this query requirement, our data set needs to maintain a date-stamped record of parcel owner changes with the appropriate linkages to the former owner's human record and the sale parcel as well as the parcel's new owner, i.e. the new owner's human record. To accomplish this, perhaps our linking table parcelhuman that connects a human to a parcel record needs to have additional fields to denote start and end dates of that linked record's validity.
Data persistence
codeNforce currently scrapes data from the Allegheny County Real Estate Portal for owner names and queries the WPRDC property database for parcel metadata. All this data is written to PostgreSQL tables for later use by codeNforce's Java codebase. We are not making any on-demand API calls at the GUI user's request, and we remain open to new data flows that include "on-demand" API requests.
Because property owner, address, and parcel connections are used extensively in the creation of official/legal documents such as notices of violations and occupancy permits, these records will need to be live in the codeNforce database at the time of their use (i.e. when a notice letter is generated and finalized, or an occ permit is generated and printed). This requirements means perhaps the owner/LLC/Parcel connections don't need to live in codeNforce's database until they are used in a particular workflow such as notice generation or rental registration. In this case, our Java folks would benefit from a set of sample API call structures that we could implement as a data source/service inside the Tomcat server called by Java methods.
Table structure
Our PostgreSQL table structure has been upgraded to center on the parcel, human, and mailingaddress tables. Linking tables connect these and each record in these tables contains unified fields for user-stamping and time stamping creation, updates, and deactivation (our equivalent of delete). The simplified ERD for these core tables is a diagrams.net google drive app file shared here.
Note that we have tentatively decided to use a third party download of official ZIP Codes, City names, and States for address parsing and lives in a table called mailingcitystatezip
Supporting database documentation
An extensive discussion of the human and parcel table philosophy lives in a git hub issue that was created before Zac appeared on the horizon and could be useful in designing data pathways. We are open to revisions of this table structure, particularly with respect to how to work with business entities which own parcels which are currently a quasi human, meaning they are recorded as a record in human with the businessentity flag set to TRUE.
Sample database
A SQL script for recreating our PSQL database with test data can be found in [codeEnfDatabaseProject root}/database/
Tech stack
codeNforce is running on an Ubuntu VPS and its primary Java server is Wildfly/Tomcat. We've also got a node.js server for some map tool management. We have a mostly deprecated python server for making calls to the WPRDC/scraping the county but these scripts are writing to deprecated (non normalized) property and person tables, not our upgraded parcel and human tables and their family of linking tables. If your tools are open source and Linux friendly, we're open to any other tech stack that can talk to a PostgreSQL server and a Wildfly data source.
Parcel data
codeNforce needs to display data about each parcel in participating municipalities (as of Summer 2021, this means Chalfant and E. McKeesport) including the following fields:
KOCSIS GEORGE R & CYNTHIA A (W)). Note that in the long-run, we'd like to try to automatically create a human record for George Kocsis and a sparate record for Cynthia but not yet)Human data
codeNforce needs to display data about individual humans/persons (in Java land, a Person is a subclass of Human containing that Human's phone numbers, email addresses, and associated mailing addresses) including:
linkedobjectroletable which contains schemas for all sorts of linked object roles. (e.g. codeNforce would display something like "KOCSIS GEORGE Rowner of2022 Lark Ave" and "KOCSIS GEORGE Rowner of2024 Lark Ave, etc. and maybe something like: "KOCSIS GEORGE Rreceives tax bills at3348 Underwater Way, Kansas City, MO 99999". The 'owner ofand 'receives tax bills atare attributes of a record in thelinkedobjectroletable.)owner of1120 Ocean Flood Dr. until 20-JAN-2021 .same tax mailing address asEVERGREEN RENTALS LLCChange of owner flagging
codeNforce users are very interested in automated detection of parcels which change owners because such transfers are legally required to be associated with a valid occupancy permit for the transferred parcel. In some cases, we have reason to believe properties are changing hands without the requisite occupancy permitting process occurring.
Queries for non-compliant parcel owner transfers
Our goal between Summer 2021 and Summer 2022 is to have the occupancy UI and parcel data in decent enough shape that we can periodically query the database asking "Find me all parcels in municipality C which have changed owners in the past 30 days and are NOT associated with an occupancy period of type
Change of owner.Owner change dates
Given this query requirement, our data set needs to maintain a date-stamped record of parcel owner changes with the appropriate linkages to the former owner's
humanrecord and the sale parcel as well as the parcel's new owner, i.e. the new owner'shumanrecord. To accomplish this, perhaps our linking tableparcelhumanthat connects ahumanto aparcelrecord needs to have additional fields to denote start and end dates of that linked record's validity.Data persistence
codeNforce currently scrapes data from the Allegheny County Real Estate Portal for owner names and queries the WPRDC property database for parcel metadata. All this data is written to PostgreSQL tables for later use by codeNforce's Java codebase. We are not making any on-demand API calls at the GUI user's request, and we remain open to new data flows that include "on-demand" API requests.
Because property owner, address, and parcel connections are used extensively in the creation of official/legal documents such as notices of violations and occupancy permits, these records will need to be live in the codeNforce database at the time of their use (i.e. when a notice letter is generated and finalized, or an occ permit is generated and printed). This requirements means perhaps the owner/LLC/Parcel connections don't need to live in codeNforce's database until they are used in a particular workflow such as notice generation or rental registration. In this case, our Java folks would benefit from a set of sample API call structures that we could implement as a data source/service inside the Tomcat server called by Java methods.
Table structure
Our PostgreSQL table structure has been upgraded to center on the
parcel,human, andmailingaddresstables. Linking tables connect these and each record in these tables contains unified fields for user-stamping and time stamping creation, updates, and deactivation (our equivalent of delete). The simplified ERD for these core tables is a diagrams.net google drive app file shared here.Note that we have tentatively decided to use a third party download of official ZIP Codes, City names, and States for address parsing and lives in a table called
mailingcitystatezipSupporting database documentation
An extensive discussion of the
humanandparceltable philosophy lives in a git hub issue that was created before Zac appeared on the horizon and could be useful in designing data pathways. We are open to revisions of this table structure, particularly with respect to how to work with business entities which own parcels which are currently a quasi human, meaning they are recorded as a record inhumanwith thebusinessentityflag set to TRUE.Sample database
A SQL script for recreating our PSQL database with test data can be found in
[codeEnfDatabaseProject root}/database/Tech stack
codeNforce is running on an Ubuntu VPS and its primary Java server is Wildfly/Tomcat. We've also got a node.js server for some map tool management. We have a mostly deprecated python server for making calls to the WPRDC/scraping the county but these scripts are writing to deprecated (non normalized)
propertyandpersontables, not our upgradedparcelandhumantables and their family of linking tables. If your tools are open source and Linux friendly, we're open to any other tech stack that can talk to a PostgreSQL server and a Wildfly data source.