Next Steps for ZAP Listserv Project

[TylerMatteo]

@wndyli and @jessicashanshanhuang have done a great job helping us define scope of MVP for the project and produce initial mock ups and user stories for feedback from stakeholders. As their time with us draws to a close on 8/16, this feels like a good point to take stock of the project and outline next steps. This will also serve as a good time to get the rest of Application Engineering more involved with the project as we transition into technical research and implementation.

Useful links

• Project folder in SP
• User Stories and Wireframes in Figma (WIP)

Application Architecture

I'll use the term "architecture" to talk about the overall technical approach for how the various vendors and DCP systems will talk to each other. Here is glossary for how I'll be referring to specific applications:

• ZAP Search Frontend (FE) - The front end web application hosted at zap.planning.nyc.gov
• ZAP Search Backend (BE) - The Nestjs backend API application maintained by AE. Code here. This is effectively a "middleware" application that ZAP Search FE uses. It uses Dynamics' OData APIs to get data from CRM
• ZAP CRM - The Microsoft Dynamics 365 application that is the source of truth for all ZAP data
• ZAP Reports Database (DB) - The SQLServer database managed by the ASM team. This is roughly equivalent to a data warehouse for ZAP. My understanding is that data are ETL'd into this database from ZAP CRM on a recurring basis
• Notification Platform - This refers to the vendor or custom built API we will use to manage the notification data and logic. DCP applications would likely call APIs provided by this platform to schedule and send notifications, manage user subscriptions, etc
• Power Automate - Power Automate is Microsoft's low- or no-code business automation platform. It can integrate with ZAP CRM (via Dynamics) and other Microsoft applications

Preferred Approaches

Based on our understanding of requirements so far and meetings with various teams within ITD, here are my two leading architectures, in order of preference.

Event Driven with Power Automate

This architecture leverages Power Automate to trigger notifications based on changes in data in ZAP CRM. Based on the meeting we had with ASM on 7/31, we believe it is possible to have a Power Automate workflow that triggers when the value for a given property of a given entity type (in our case, the dcp_publicstatus property on the dcp_project entity) is updated. This workflow could then fire off events provided by PA's "connectors". The PA "connector" we would likely use would be one that allows us to send an HTTPS call to the Notification Platform's APIs.

Note that we are not required to send notifications "in real time" and we would likely not choose to even if we could because users may prefer to get notifications "batched" on a nightly cadence. The notification platform would likely be responsible for facilitating the timing and grouping (see "batching and digests" section below) but PA does offer "delay" functionality, should we need it.

New User Sign Up Flow

sequenceDiagram
    participant U as User
    participant F as ZAP Search FE
    participant V as Notification Platform
    U->>F: User signs up for topics
    F->>V: ZAP FE Subscribes User
    Note over F,V: May need middleware<br/>call to ZAP Search BE
    V->>U: Platform sends confirmation email
    U->>V: User confirms subscriptions

Loading

Notification Flow

sequenceDiagram
    participant S as DCP Staff
    participant C as ZAP CRM
    participant P as Power Automate
    participant V as Notification Platform
    participant U as User
    S->>C: DCP Planner updates<br/>public status for project
    C->>P: Power Automate flow<br/>is triggered
    P->>V: PA flow calls platform API<br/>to schedule notification
    Note over P,V: May need middleware call<br/>to ZAP Search BE
    V->>U: Platform send email to<br/>subscribed users

Loading

Nightly Job Using ZAP Reports Database

This architecture uses a nightly job to check for projects that have had relevant changes to the dcp_publicstatus property in the previous 24 hours and call Notification Platform APIs accordingly to schedule notifications. The approach I'm suggesting for scheduling the nightly jobs would be to add them to the ZAP Search BE codebase using Nestjs' task scheduling package. This gives us a cron-like API for running code on a recurring basis. However, I'm open to other ways we might facilitate job scheduling.

While we can use CRM's OData APIs to get the current dcp_publicstatus of projects, we have no easy way of knowing when a project entered it's current status. When we posed this question to ASM, they suggested using the database that powers DCP's internal "ZAP Reports" application. This is an on-prem Microsoft SQL Server relational database that acts as a data warehouse of sorts for ZAP. ASM believes there might already be historical project-level data in that DB that would enable us to query for projects with recently updated public statuses but if that data doesn't exist today, they are open to adding it for us. Our code would connect to this database via a connection string with read-only permissions and perform the query via SQL.

Critically, this viability of this approach depends on how often data are ETL'd into the ZAP Reports database. It would have to be at least as often as the cadence on which we want to send notifications (likely daily). We should follow up with ASM about this.

New User Sign Up Flow

See New User Sign Up Flow under Event Driven approach - the process for sign up would be the same here.

Notification Flow

sequenceDiagram
    participant S as DCP Staff
    participant C as ZAP CRM
    participant D as ZAP Reports DB
    participant B as ZAP Search BE
    participant V as Notification Platform
    participant U as User
    S->>C: DCP Planner updates<br/>public status for project
    loop Existing ETL Schedule
        C-->>D: ETL project data
    end
    loop Schedule TBD, dependent on ZAP Reports ETL schedule
        B-->>D: Query historical projects<br/>by public status
    end
    B->>V: Calls vendor API<br/>to schedule notification
    V->>U: Vendor send email to<br/>subscribed users

Loading

Backup Approaches

Should either of these approaches prove impossible for any reason, here are some alternatives

Nightly Job using CRM OData API

This is similar to the ZAP Reports approach but instead of using ZAP Reports' DB, our nightly code would query data in ZAP CRM via the OData APIs we already use. Given that the existing data model doesn't seem to tell us when a project's public status last changed, there are two ways we could go about doing this: a) have the ASM team add a field to the project entity with a datetime that tracks updates to project status or b) use the OData API to query CRM's internal audit logs (docs here). Once we know the projects we need to send notifications for, the process for calling the Notification Platform API would be same as in the ZAP Reports approach.

Use nightly ZAP data exports provided by Data Engineering

This approach would see us working with DE to build upon their existing work that powers the ZAP data product that is posted to the open data portal. Based on a discussion I had with Finn and Damon, they don't store "historical" records like we would need but it isn't necessarily out of the question for them to start doing so. Once we know the projects we need to send notifications for, the process for calling the Notification Platform API would be same as in the ZAP Reports approach.

Choosing Vendors

The fellows and I (with some ad-hoc input from @dhochbaum-dcp) have started researching vendors we would need for this project, however, there is still work to be done here before making decisions. While the requirements and user stories are still WIP and have yet to be shown to any stakeholders outside of Planning Support, we should be prepared to start evaluating vendors in earnest soon.

Types of Vendors

Notification Platform

Our research will focus on evaluating vendors that provide infrastructure for managing and triggering notifications. This includes subscribing/unsubscribing, triggering notifications to multiple channels, managing subscription topics (CDs, citywide, etc), email/SMS templates, etc.

Email Service

We'll likely need a separate vendor to handle actually sending the emails. Based on early research, it looks like most notification vendors have some concept of a "provider" where you hook up notifications to a third party to handle actually sending out emails. I see two options for this:

• Sendgrid - We use DCP's existing SG account (upgrading it as necessary). SG has near-ubiquitous support among notification vendors I've seen
• Microsoft - We may be able to send the emails through DCP's Outlook email server.