Permit pipeline: map issued, report finaled, file expired

Nightly at 2 am

• Every night at 2:00 am (workspace time zone)

Then: Changed permits

Changed permits

• Reads from PostgreSQL / PostGIS with a read-only query

• SELECT permit_number, permit_type, status, address, applicant, valuation, issued_on,
         ST_Y(geom) AS lat, ST_X(geom) AS lng, updated_at
  FROM permits
  WHERE updated_at > COALESCE(CAST(:last_success_at AS timestamptz), '1970-01-01')
  ORDER BY updated_at

• Row ID column: permit_number (used for replay and lineage)

Then: Clean and check

Clean and check

• permit_type: title
• status: lower
• status: one of issued, finaled, in review, expired
• valuation: number
• applicant: required
• Records that fail a check are dropped

Then: Issued?

Issued?

• Yes when status is issued

Then: Yes → Map attributes; No → Finaled?

Map attributes

• Shapes each record into 6 fields: ref, status, address, type, lat, lng

Then: Make map point

Finaled?

• Yes when status is finaled

Then: Yes → Total by type; No → Expired?

Make map point

• Builds an ArcGIS map point from lat and lng
• Drops records without coordinates

Then: Add to the permit map

Total by type

• Counts records by permit_type and adds up valuation

Then: Email building

Expired?

• Yes when status is expired

Then: Yes → Expired permits to SFTP

Add to the permit map

• Creates Features in Esri ArcGIS feature layer

Email building

• Emails [email protected]: “Permits finaled last night”
• One summary email

Expired permits to SFTP

• Writes CSV file expired-permits-{{ $today }}.csv to outbox
• One file with every record