Initial Workflows Implementation in MS Flow

[slathrop]

Background

• Workflows to be integrated with SQL Server and LoopBack
• Examples of workflows (simplified scenarios):
  • Every 15 minutes, query the "Contact/Party Comments" table in SQL Server and...
    • Look for any new rows that match the criteria: "Status = R (response entered), Response = (not null), and Contact/Party Email = (valid email)", sorted by: "ValidFrom (ascending)"
    • Send email to Contact/Party Email address with body set to Response
    • Mark row with Status = C (completed)
  • Every 15 minutes, hit a well-known LB endpoint to kick-off collection of another sample from an external API and save the sample to SQL Server
• Microsoft Flow recommended for simplicity, breadth of "connectors", UI, etc.

Tasks

• Perform research, reqs. review, and testing necessary to come to final decision on platform/approach... assume this will be MS Flow
• Determine best user account to use for MS Flow work
• Determine FROM email address(es) to use for automated emails
  • A blanket "no-reply" address?
  • A distro? A different distro per applet?
• Determine approach for handling certain use-cases/edge-cases... e.g., in "Contact/Party Comments" example above, how do we handle...
  • Multiple rows meeting criteria at the same time (i.e., when interval elapses and flow kicks off)
    • Do we make the flow more complicated and loop through all of the rows? If so, how do we handle any problems w/ individual rows?
    • Or do we keep the flow simple by only handling a single row at a time? (suggested)
      • In this case, we'd likely schedule the flow to run more frequently (60 second interval?)
      • And we need to be much more careful about potential race conditions (see query hints below in Next Steps)
• Setup account for development on https://flow.microsoft.com (dev/test/prod account)
• Begin developing flows
• Enter test data needed for developing and testing flows
• Periodically save MS Flow specs into version control so that they do not exist solely in MS Flow website and can be re-created quickly in a disaster scenario

Next Steps

• Do another round of review into entire universe of use-cases required for workflows
• Make workflows more production-ready and reliable by implementing best practices such as...
  • For SQL Server queries where rows are basically acting as a "queue", be sure to perform things in a transaction where possible, using query hints on the select statement (e.g.., "with updlock, rowlock, readpast"). See here for details