DEV Community: Paul Riviera

The SaaS Apocalypse: How AI Lets Solo Devs Rebuild Tools In-House and Save Millions in 2026

Paul Riviera — Sat, 03 Jan 2026 05:01:08 +0000

As we see AI-assisted coding tools and models get better and better, a key question keeps coming up: What tools are organizations paying hefty sums for that a single capable developer could recreate using current AI tools? I predict this year companies will figure out that one skilled coder could now solo develop and sustain a range of apps, potentially saving firms tens of thousands—or even millions—in yearly licensing fees by ditching external vendors for tailored, in-house alternatives that emphasize customization and efficiency.

I still believe that Vibe Coding or Context Coding will create a lot of AI slop, but while consumer tools may not be ready yet, internal tools that help run a company certainly can be. Why not use GitHub Copilot to create a new expense tracking system, or time card tool? AI tools today can effectively supplant many off-the-shelf SaaS options, unlocking substantial cost reductions without the bloat of vendor dependencies.

On a personal note, I'm embarking on this journey myself: Swapping out my subscriptions for AI-built tools just for me and my family, aiming to trim expenses. Stay tuned—I'll share the ups, downs, and hopefully some wins as I experiment.

Below, I outline seven SaaS areas I believe are prime for in-house reinvention. These carry significant ongoing costs but straightforward setups that AI can mimic, provided the developer refines outputs to avoid too much slop but get all the required value.

Document and Spend Automation: Tools for scanning bills, approvals, and payouts.
Task Coordination Platforms: For organizing work, boards, and updates.
Contact Management Basics: Aggregating leads, segments, and outreach.
Hours and Workflow Tracking: Logging time, reviews, and rules.
Simple Data Insights Tools: Querying and dashboards sans code, $30–$100 per user monthly.
Customer Interaction Bots: Systems for handling queries via chat, tickets, and info hubs.
Messaging and Content Automation: For campaigns, tests, and metrics.

To be clear, I am not currently advocating for mission-critical tools to be rewritten and managed by a single person, as that could be too risky. However, if there is an internal tool that can have some accepted downtime, why not try a rewrite to save some money?

My 2026 Prediction: Changes, Adjustments, and Opportunities

This year could redefine how firms approach software, with solo developers using AI to build efficient internal suites, saving fortunes while acknowledging limits for large-scale operations. I believe many organizations will switch this year to a build-in-house vs. buy option. Sadly, this may mean a number of high-profile companies will need to pivot quickly, and some may go the way of Blackberry or Nokia; simply be a page in the history books. I will not be as bold as to predict which major players this could affect, but if you want to chat privately, I am happy to share my thoughts. For now, let's keep building.

Building a Personal Blog in Just a Few Hours with AI Tools

Paul Riviera — Thu, 01 Jan 2026 05:00:00 +0000

In today's fast-paced world of AI-assisted development, I set out to build a personal blog from scratch in as little time as possible. Using a combination of tools like Grok, Figma Make, GitHub Copilot, and Azure, I managed to get a basic site up and running quickly. While it's not fully functional yet, this experiment shows how AI can accelerate the process dramatically. Here's a step-by-step breakdown of how I did it.

Here is the result of the site: https://www.paulriviera.com (Basically just this same post and placeholder content)

Step 1: Planning with Grok (15 Minutes)

I kicked things off by chatting with Grok to outline my basic requirements. We discussed the tools I'd use, the minimalistic style I wanted (clean, simple, and focused on readability), and the overall structure of the blog. This conversation helped me refine my ideas and generate a solid starting prompt for my conversation with Figma Make.

The Grok session was quick—about 15 minutes—and resulted in a tailored prompt I could feed directly into Figma Make without having to use a starting design or assets in Figma.

Step 2: Designing and Generating with Figma Make (2 Hours)

I may get a better result using assets or an existing Figma design to build off of but I have not tried that yet.

With the prompt from Grok in hand, I jumped into Figma Make to create the initial blog page. It generated a basic layout based on my description, and from there, I iterated through simple conversations to add more pages and tweak the designs.

This phase was surprisingly efficient. In just two hours, I went from a blank slate to a solid, exportable prototype. Figma Make allowed me to push the output directly to GitHub. I could edit the code in Figma if I needed to but mainly I like this tool because I can be very specific on the elements I ask Figma to change/create as opposed to other Vibe coding tools I have used.

Step 3: Refining and Deploying with GitHub Copilot (20 Minutes + Setup)

Once the repo was on GitHub, I cloned it locally and started making improvements using GitHub Copilot in VS Code, with Claude Haiku 4.5 in Agent Mode for additional assistance. Here's what I tackled:

Install packages: Ensured all dependencies were set up.
Test the build and run in dev mode: Everything worked perfectly out of the gate.
Add Prettier for formatting: To keep the code clean and consistent.
Convert mock articles to Markdown: Moved them from TypeScript files to Markdown for easier content management and future additions.
Add/Update .gitignore: The Figma output didn't include one, so I fixed that to avoid committing unnecessary files.
Add missing Azure configuration: Prepared for static web app deployment.
Connect the repository to Azure: Used a pre-written script for this.
Set up GitHub Actions: Configured a workflow to deploy the Vite-based app to Azure Static Web Apps (created via the Azure Portal to save time).
Deploy to Azure: Pushed the changes and watched it go live.

This hands-on coding and deployment phase took about 20 minutes, plus a quick 2 minutes to provision the Static Web App in Azure. The site deployed successfully, though many features are still broken (e.g., contact page and external links are broken).

Running Clock and Next Steps

Total time so far:

15 minutes with Grok
2 hours with Figma Make
2 minutes provisioning in Azure
20 minutes with GitHub Copilot and Claude

That's under 3 hours for a deployed (albeit basic) blog!

The site isn't production-ready—it's more of a skeleton. However, while the site is broken, it will remain live in the free tier of Azure Static Web Apps while things are added. My next moves include:

Creating a GitHub Project board.
Generating detailed issues using Grok and/or GitHub Copilot.
Assigning tasks to the GitHub Copilot Agent for simpler ones, or handling complex ones myself.

Final Thoughts

This was an experiment in speed-building with my current AI toolkit, and it proved how far these tools have come. While it'll take more time to make it a fully functional blog, getting to a deployed state so quickly was satisfying. While this workflow requires a lot of refinement I do like the direction and I am excited for how it can improve my development.

Deploy NextJS 14+ app to Linux Azure App Service

Paul Riviera — Fri, 10 Nov 2023 12:07:35 +0000

NextJS is a fantastic framework for building React apps. It's easy to get started with and has a great developer experience. However it is really optimized to be deployed to Vercel, which is understandable given the good folks at Vercel created NextJS. I prefer to use Azure for my application deployments and in this guide I will walk you through the different changes that need to be made to your NextJS app to ensure it will run on Azure.

All deployments will be done using GitHub actions, so you will need to have a GitHub account and a repository with your NextJS app. I also assume you have the proper permissions to create Azure resources and have the Azure CLI installed. If you need help connecting your GitHub Action to Azure you can review my previous article Connect GitHub Actions to Azure using OpenID Connect which will walk you through it.

Getting Started

I will not provide a guide for creating a NextJS app, but you can follow the official guide to get started. Once you have your app created you can follow the steps below to get it ready for deployment to Azure.

As of writing this NextJS is at version 14.0.1, so I will be using that version for this guide. If you are using a different version you may need to make some adjustments.

Step 1: Provision the Azure App Service

Like all Azure resources we will create a resource group to contain our app service and then create the app service. The following script will create a resource group, an app service plan, and an app service. You will need to replace the values in the script with your own.

$RESOURCE_GROUP_NAME = "<Name of Resource Group>"
$LOCATION = "<Location of Resource Group>"

$APP_SERVICE_PLAN_NAME = "<Name of App Service Plan>"
$APP_SERVICE_PLAN_SKU = "<SKU of App Service Plan>"

$WEB_APP_NAME = "<Name of Web App>"
$WEB_APP_RUNTIME = "NODE:18-lts" # This is not the latest version of Node, but it is the version supported by Azure App Service at the time of writing this article.

az group create `
    --name $RESOURCE_GROUP_NAME `
    --location $LOCATION

az appservice plan create `
    --resource-group $RESOURCE_GROUP_NAME `
    --location $LOCATION `
    --is-linux `
    --name $APP_SERVICE_PLAN_NAME `
    --sku $APP_SERVICE_PLAN_SKU

az webapp create `
    --resource-group $RESOURCE_GROUP_NAME `
    --plan $APP_SERVICE_PLAN_NAME `
    --name $WEB_APP_NAME `
    --https-only $true `
    --runtime $WEB_APP_RUNTIME `
    --assign-identity [system]

Step 2: Configure the Azure App Service for NextJS

The app service needs to be told how to run the NextJS app, to accomplish this we will set a startup command that will be run once the deployment is complete. Normally you would just run a npm start on the build output but in the next step we will modify the output to require some changes to the startup command.

$WEB_APP_NAME = "<Name of Web App>"
$RESOURCE_GROUP_NAME = "<Name of Resource Group>"

az webapp config set `
    --resource-group $RESOURCE_GROUP_NAME `
    --name $WEB_APP_NAME `
    --startup-file "node .next/standalone/server.js"

Note that the parameter is --startup-file and not --startup-command, while I don't like this naming, this naming it is what the Azure CLI provides..

Step 2: Configure the NextJS App output to be Standalone

Since NextJS is optimized for Vercel the default build output will not work on Azure, it can be deployed but the required files will not exist. To fix this we need to change the build output to be standalone. This will ensure all the required files are included in the build output.

All you need to do for this is update the output in next.config.js to be standalone.

const nextConfig = {
    output: "standalone"
}

module.exports = nextConfig

Once that is done you can build your app and your will notice the .next build folder will now contain a standalone folder. This folder will be the focus of your deployment to Azure, however it still has dependencies on the .next/server folder and we will have to do a little more work to get it ready for Azure, but this will be in the pipeline.

Step 3: Create a GitHub Action to Deploy to Azure

In this step we will start with the initial GitHub Action workflow and slowly add additional steps. When we are done we will have a workflow that will build the NextJS app, move the required files to the standalone folder, and then deploy the app to Azure.

Step 3.1: Create the initial GitHub Action workflow

The following workflow will be the starting point for our GitHub Action. It will be triggered on a push to the main branch and will run on an Ubuntu VM. It will also install NodeJS and the required dependencies for the NextJS app. The last step will login to Azure using the OIDC authentication method. Replace the login step if you're using a different method to connect to Azure.

name: Deploy to Azure

on:
  push:
    branches: ["main"]

  workflow_dispatch:

permissions:
  id-token: write # This is required for requesting the JWT
  contents: read # This is required for actions/checkout

jobs:
  build:
    name: Build
    runs-on: ubuntu-latest
    steps:
      - name: Checkout # Checked out your repository
        uses: actions/checkout@v3

      - name: Setup Node # Installs NodeJS 18
        uses: actions/setup-node@v3
        with:
          node-version: "18"

      - name: Install dependencies # Installs your app dependencies
        run: |
          npm install

      - name: 'Login to Azure Subscription' # This is required for OIDC authentication to Azure
        uses: azure/login@v1
        with:
            client-id: ${{ secrets.AZURE_CLIENT_ID }}
            tenant-id: ${{ secrets.AZURE_TENANT_ID }}
            subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

Step 3.2: Add the build NextJS app step

The next step to add is the build step. Since our app is configured for standalone output we need to move a few folders to get it running. The public and static folders are required to be in the standalone folders when the app is run, unless you choose to serve them from a CDN (Content Delivery Network). This step will move the static folder to the correct location in the standalone output. Notice the mv public .next/standalone/public is commented out, if you don't have anything in your public folder at build then the public folder will not be created. Only uncomment this line if you have a public folder in your output you want to include.

      - name: Build Next.js app
        run: |
          npm run build
          mv .next/static .next/standalone/.next/static
          # mv public .next/standalone/public

Step 3.3 Add the deployment step

While there are a few options to actually deploy your code, I believe the following is the best way to show what is actually happening with limited abstractions.

The first thing we need to do is zip the contents of our .next build folder.

NOTE: the zip file can be whatever name you choose, I personally like to use a combination of the branch and workflow id so I can trace the path of the deployment later

This uses the Azure App Service zipDeploy method initiated by the Azure CLI command, if you would like to read more about deployments you can reference the Microsoft documentation for deployment best practices.

      - name: Deploy
        shell: pwsh
        run: |
            $ZIP_FILE_NAME = "./${FILE_NAME}.zip"
            zip $ZIP_FILE_NAME ./* .next -qr

            az webapp deploy `
                --resource-group $RESOURCE_GROUP_NAME `
                --name $WEB_APP_NAME `
                --src-path $ZIP_FILE_NAME `
                --slot staging `
                --type zip `
                --clean true 
        env:
            FILE_NAME: "<Name of your choice>"
            RESOURCE_GROUP_NAME: "<Name of Resource Group>"
            WEB_APP_NAME: "<Name of Web App>"

Conclusion

When I first began using NextJS configuring this was a bit of a pain, the documentation for the standalone configuration was not very clear (It's a little better now). My hope is that this information will save you time as you work with NextJS on Azure. If you have questions of concerns feel free to reach out. Thanks.

Connect GitHub Actions to Azure using OpenID Connect

Paul Riviera — Fri, 10 Nov 2023 01:09:08 +0000

Using Open ID Connect to connect GitHub Actions to Azure is the new recommended method. This is an updated guide for connecting GitHub Actions to Azure using OpenID Connect, detailing script-based Azure AD app and service principal creation, role assignment, and federated credentials setup for GitHub integration.

My Environment

I am running this on macOS 14.0, and the following prerequisites are needed to execute the script snippets in this article.

PowerShell 7.2.4
Azure CLI 2.53.0
GitHub CLI 2.38.0

Step 1: Create an Application Registration in Microsoft Entra ID

NOTE: Replace the values in the script with your own and be sure you have proper permission to create app registrations/service principals and edit them in Azure AD.

The App Registration object id will be used to in the request to create federated credentials for GitHub Actions, this part is required.

$AppName = "<Name of App Registration>"

$APP_JSON = az ad app create `
    --display-name "${AppName}" `

$APP_DATA = $APP_JSON | ConvertFrom-Json
$APP_ID = $APP_DATA.appId
$APP_OBJECT_ID = $APP_DATA.id

Step 2 (Optional): Create a Service Principal for the Application Registration

While you don't have to create a service principal for the application registration, it is recommended. This will allow you to assign a role to the service principal and limit the scope of the permissions. The service principal object id will be used in the role assignment request in the following step.

$SP_JSON = az ad sp create `
    --id $APP_ID

$SP_DATA = $SP_JSON | ConvertFrom-Json
$SP_ASSIGNEE_OBJECT_ID = $SP_DATA.id

Step 3 (Optional): Assign a Role to the Service Principal

This step is optional but recommended. This will allow you to assign a role to the service principal and limit the scope of the permissions. The following assumes you're currently logged into the Azure CLI and have the correct subscription set.

NOTE: Replace the values in the script with your own and be sure you have proper permission to create app registrations/service principals and edit them in Microsoft Entra ID. Change the Role Array to match your needs.

$SUBSCRIPTION_ID = az account show --query id --output tsv # Returns the current subscription ID

# Create an array of roles with their respective scopes
$RoleArray = @(
    [PSCustomObject]@{Role="Contributor"; Scope="/subscriptions/${SUBSCRIPTION_ID}"},
    [PSCustomObject]@{Role="User Access Administrator"; Scope="/subscriptions/${SUBSCRIPTION_ID}"},
)

# Loop over the array and print the role and scope of each object
foreach ($obj in $objectArray) {
    Write-Host "Role: $($obj.Role), Scope: $($obj.Scope)"
    az role assignment create `
        --role $obj.Role `
        --subscription $SUBSCRIPTION_ID `
        --assignee-object-id  $SP_ASSIGNEE_OBJECT_ID `
        --assignee-principal-type ServicePrincipal `
        --scope $obj.Scope
}

Step 4: Add federated credentials for GitHub Actions

This step is required. The following assumes you're currently logged into the Azure CLI and have the correct subscription set. The following will create a federated credential for the App Registration that will be used to connect to Azure from GitHub Actions.

NOTE: Replace the variable values in the script with your own.

$CREDENTIAL_NAME = $AppName
$GITHUB_ORG = "<Name of GitHub Org or GitHub Username>"
$GITHUB_REPO = "<Name of GitHub Repo>"
$GITHUB_REPO_BRANCH = "<Name of GitHub Repo Branch>"

$REQUEST = @{
    name = $CREDENTIAL_NAME
    issuer = "https://token.actions.githubusercontent.com"
    subject = "repo:${GITHUB_ORG}/${GITHUB_REPO}:ref:refs/heads/${GITHUB_REPO_BRANCH}"
    description = "Federate GitHub Action"
    audiences = @("api://AzureADTokenExchange")
}

$REQUEST_JSON = ($REQUEST | ConvertTo-Json -Compress) -replace '"', '\"'

az rest `
    --headers "Content-Type=application/json" `
    --method POST `
    --uri "https://graph.microsoft.com/beta/applications/${APP_OBJECT_ID}/federatedIdentityCredentials" `
    --body $REQUEST_JSON

Step 5: Add secrets to GitHub repo via GitHub CLI

Once the federated credentials have been created, you will need to add the following secrets to your GitHub repo. This script will assign the required values from the app registration to the to the GitHub repository through the GitHub CLI. The following assumes you're currently logged into the GitHub CLI.

NOTE: The names of the secrets to not have to be exactly as written below, but they do need to match the GitHub Action Workflow in the next step.

$AZURE_CLIENT_ID = az ad app show `
    --id $APP_OBJECT_ID `
    --query appId `
    --output tsv

$AZURE_TENANT_ID = az account show `
    --query "tenantId" `
    --output tsv

# $SUBSCRIPTION_ID = az account show ` # Uncomment if not set in optional Step 3
#     --query "id" `
#     --output tsv

$SecretsArray = @(
    [PSCustomObject]@{Name="AZURE_CLIENT_ID"; Value=$AZURE_CLIENT_ID},
    [PSCustomObject]@{Name="AZURE_TENANT_ID"; Value=$AZURE_TENANT_ID},
    [PSCustomObject]@{Name="AZURE_SUBSCRIPTION_ID"; Value=$SUBSCRIPTION_ID}
)

# Loop over the array and print the role and scope of each object
foreach ($secret in $SecretsArray) {
    Write-Host "Name: $($secret.Name), Repo: ${RepoName}"
    gh secret set $secret.name `
        --repo "${Username}/${RepoName}" `
        --body $secret.Value
}

Step 6: Add GitHub Action and test Azure connection

This is an example of a GitHub Actions workflow that will connect to Azure using the OpenID Connect method. You can find details on the permissions required for this action here.

The client id, tenant id and subscription id need to be assigned from the secrets in the repository from step 5.

name: Azure Login with OpenID Connect

on:
    push:
        branches: [ main ]

permissions:
    id-token: write # This is required for requesting the JWT
    contents: read  # This is required for actions/checkout

jobs:
  login:
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3

      - name: 'Az CLI login'
        uses: azure/login@v1
        with:
            client-id: ${{ secrets.AZURE_CLIENT_ID }}
            tenant-id: ${{ secrets.AZURE_TENANT_ID }}
            subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

      - name: Show Subscription
        run: |
          az account show

Thanks for reading

These scripts are a really helpful way initiate a connection from GitHub to Azure. I use this for all my projects and I hope if helps. If you have questions feel free to reach out.

Connecting Azure API Management with an Azure Function with Azure CLI

Paul Riviera — Sat, 11 Mar 2023 14:15:12 +0000

Why is this important?

When working with serverless functions we need to decide how to expose the functions to our clients. If you're working with event driven architectures this may not apply but when creating REST endpoints with Azure Functions we need to make a choice. We can expose each of our function apps directly to the internet or we can use Azure API Management to expose our functions.

If we expose the functions directly then each function api host url will need to be known to the client application and changes to these urls will require changes to the client applications or at least their configurations.

If we use Azure API Management then we can expose a single url to the client application and changes to the function route can be abstracted away from the client application. If you are working with a single function then it may not be worth the effort to use Azure API Management but as your application grows it will be helpful.

Getting Started

Prerequisites

If you choose to follow along with this article you will need the following:

Part 1: Create Resources

The first step is to create our function, the focus of this article is the connection between the function and the api management instance so the content of the function is not important. I will create a basic hello world function but feel free to make your own as complex or simple as you would like.

Step 1: Creating the Azure Function

Create Azure Function with the Azure Function Core Tools. This will create a new directory for your function app and initialize the function app. It will also create a new function with the name "HelloWorld" and set the authentication level to function, this will require a function code to be passed through the query parameters to execute the function.

mkdir MyFunctionApp && cd MyFunctionApp # Create a directory for your function app
func init --language "C#" --worker-runtime dotnet --name MyFunctionApp # Initialize the function app
func new --template "HttpTrigger" --name "HelloWorld" --authlevel "function" # Create a new function

Here is where you can choose the complexity of your function. If you want to follow along with a simple hello world function here is the code.

[FunctionName("HelloWorld")]
public static async Task<IActionResult> Run([HttpTrigger(AuthorizationLevel.Function, "get", Route = "ping")] HttpRequest req, ILogger log)
{
    var responseMessage = new
    {
        Message = "Hello World"
    };

    return new OkObjectResult(responseMessage);
}

You can run and test the function locally with the following command.

NOTE: The function code will be used when the function is deployed to Azure, it's not needed for local executions

func start

Step 2: Provision and Deploy Azure Function App

Now that we have a working function we can deploy it to Azure. We will use the Azure CLI to provision the resources and the Azure Function Core Tools to deploy the function. This script will also create an Application Insights instance for the function app, this is done by default when you create a function app through the az functionapp create command.

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $StorageAccountName,
    [Parameter(Mandatory = $false)]
    [String]
    $StorageAccountType = "Standard_LRS",
    [Parameter(Mandatory = $true)]
    [String]
    $FunctionAppName,
    [Parameter(Mandatory = $true)]
    [String]
    $Location
)

# ------------------------------------------------------
# Variables
# ------------------------------------------------------
$RESOURCE_GROUP_NAME = $ResourceGroupName
$LOCATION = $Location

$FUNC_APP_NAME = $FunctionAppName
$FUNC_STORAGE_ACCOUNT = $StorageAccountName.Replace("-", "").ToLower()
$FUNC_STORAGE_ACCOUNT_TYPE = $StorageAccountType

# ------------------------------------------------------
# Provision Resource Group
# ------------------------------------------------------
az group create `
    --name $RESOURCE_GROUP_NAME `
    --location $LOCATION

# ------------------------------------------------------
# Provision Storage Account
# ------------------------------------------------------
az storage account create `
    --name $FUNC_STORAGE_ACCOUNT `
    --resource-group $RESOURCE_GROUP_NAME `
    --location $LOCATION `
    --sku $FUNC_STORAGE_ACCOUNT_TYPE

# ------------------------------------------------------
# Provision Function App
# ------------------------------------------------------
az functionapp create `
    --resource-group $RESOURCE_GROUP_NAME `
    --name $FUNC_APP_NAME `
    --storage-account $FUNC_STORAGE_ACCOUNT `
    --consumption-plan-location $LOCATION `
    --runtime dotnet `
    --assign-identity [system] `
    --functions-version 4 `
    --os-type Windows `
    --https-only true

once the script has completed you can deploy the function with the following command. Be sure this command is run in the directory with the function and replace the values with your own function app name.

func azure functionapp publish <Your Azure Function App Name>

Step 3: Provision Azure API Management instance

Now that we have a function app we can provision an Azure API Management instance. We will use the Azure CLI to provision the resources. This will create a consumption (serverless) tier instance of Azure API Management, this can take a bit of time to provision as its a heavy resource.

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $ApiManagementName,
    [Parameter(Mandatory = $true)]
    [String]
    $Location,
    [Parameter(Mandatory = $true)]
    [String]
    $PublisherEmail,
    [Parameter(Mandatory = $true)]
    [String]
    $PublisherName
)

# ------------------------------------------------------
# Variables
# ------------------------------------------------------
$RESOURCE_GROUP_NAME = $ResourceGroupName
$APIM_NAME = $ApiManagementName
$APIM_SKU = "Consumption"
$APIM_LOCATION = $Location
$APIM_PUBLISHER_EMAIL = $PublisherEmail
$APIM_PUBLISHER_NAME = $PublisherName

# ------------------------------------------------------
# Provision Resource Group
# ------------------------------------------------------
az group create `
    --name $RESOURCE_GROUP_NAME `
    --location $LOCATION

# ------------------------------------------------------
# Provision API Management Instance
# ------------------------------------------------------
az apim create `
    --resource-group $RESOURCE_GROUP_NAME `
    --name $APIM_NAME `
    --location $APIM_LOCATION `
    --sku-name $APIM_SKU `
    --enable-managed-identity true `
    --publisher-email $APIM_PUBLISHER_EMAIL `
    --publisher-name $APIM_PUBLISHER_NAME

Part 2: Connect Function App to API Management

We will now create an API in API Management and connect it to our function app, at this point we are not using any security on either the function app or the API in API Management, but we will add that here in step 2.

Step 1: Create API Management Operation Policy XML

Azure API Management allows you to create policies to control the behavior of your API requests. In this case we will create a policy that will add the function code to the url so we don't have to pass it in ourselves, in fact we don't even need to know what the function code is. This will force all traffic through the API Management instance.

We will add the function code to the xml file in the following step but first we need to create the xml template. Create a new file called policy.xml and add the following xml.

<policies>
  <inbound>
    <base />
  </inbound>
  <backend>
    <base />
  </backend>
  <outbound>
    <base />
  </outbound>
  <on-error>
    <base />
  </on-error>
</policies>

You don't have to use that file name, the important thing is the file content and that you know the path to the file so you can reference it in the next step.

Use this link if you want to learn more about API Management Policies.

Step 2: Connect API Management to Function

The following script performs the following steps:

Creates an API in API Management
Creates an operation in the API
Creates a policy for the operation and deploys it through a Powershell created ARM template; this is the part that may look complex.

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $ApiManagementName,
    [Parameter(Mandatory = $true)]
    [String]
    $FunctionAppName,
    [Parameter(Mandatory = $true)]
    [String]
    $ApiManagementPolicyPath
)

# ------------------------------------------------------
# Variables
# ------------------------------------------------------

$RESOURCE_GROUP_NAME = $ResourceGroupName
$APIM_NAME = $ApiManagementName
$FUNCTION_APP_NAME = $FunctionAppName
$API_NAME = "helloworld-api"

# ------------------------------------------------------
# Get Function App Base URI
# ------------------------------------------------------

$DEFAULT_HOST_NAME = az functionapp show `
    --resource-group $RESOURCE_GROUP_NAME `
    --name $FUNCTION_APP_NAME `
    --query "defaultHostName" `
    --output tsv

$FUNCTION_APP_BASE_URI = "https://${DEFAULT_HOST_NAME}/api"

# ------------------------------------------------------
# Add Hello World API to API Management Instance
# ------------------------------------------------------

az apim api create `
    --resource-group $RESOURCE_GROUP_NAME `
    --service-name $APIM_NAME `
    --api-id $API_NAME `
    --display-name "Hello World API" `
    --path "helloworld" `
    --service-url $FUNCTION_APP_BASE_URI `
    --protocols "https" `
    --description "This is a simple Hello World API" `

# ------------------------------------------------------
# Add /Ping Operation to Hello World API
# ------------------------------------------------------

$APIM_OPERATION_ID = az apim api operation create `
    --display-name "Get Hello World" `
    --method "GET" `
    --resource-group $RESOURCE_GROUP_NAME `
    --service-name $APIM_NAME `
    --api-id $API_NAME `
    --url-template "/ping" `
    --query "name" `
    --output tsv `

# ------------------------------------------------------
# Get Function key for use in API Management Policy
# ------------------------------------------------------

$FUNCTION_KEY = az functionapp keys list `
    --resource-group $RESOURCE_GROUP_NAME `
    --name $FUNCTION_APP_NAME `
    --query "functionKeys.default" `
    --output tsv

# ------------------------------------------------------
# Build and Deploy Query Param Policy for Function Key
# ------------------------------------------------------

[System.Xml.XmlDocument]$policy = Get-Content $ApiManagementPolicyPath
$InBoundXml = '<base/><set-query-parameter name="code" exists-action="override"><value>' + $FUNCTION_KEY + '</value></set-query-parameter>'
$policy.policies.inbound.InnerXml = $InBoundXml
$policy.Save($ApiManagementPolicyPath)

[string]$PolicyAsString = Get-Content $ApiManagementPolicyPath

[PSCustomObject]$POLICY_RESOURCE = @{
    type = "Microsoft.ApiManagement/service/apis/operations/policies"
    apiVersion = "2022-08-01"
    name = "${APIM_NAME}/${API_NAME}/${APIM_OPERATION_ID}/policy"
    properties = @{
        format = "xml"
        value = $PolicyAsString
    }
}

$TEMPLATE = @{
   '$schema' = "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#"
    contentVersion = "1.0"
    resources = @($POLICY_RESOURCE)
}

touch ./azure/apimPolicyDeploy.json
$TEMPLATE | ConvertTo-Json -Depth 10 | Out-File ./azure/apimPolicyDeploy.json

az deployment group create `
    --name "apim-op-${APIM_OPERATION_ID}-policy-deploy" `
    --resource-group $RESOURCE_GROUP_NAME `
    --template-file ./azure/apimPolicyDeploy.json

At this point the you can test the API at with the following url structure:

curl <API Management Gateway URL>/<API Path>/<Operation Path>

If you followed along the with the code above the API Path and the Operation Path would be helloworld and ping respectively, however your values may differ depending on how you named your API and operation.

NOTE: at least at the time of writing this the Azure CLI cannot define a policy for API Management operations, so we have to use an ARM template to do it.

I hope this helps you get started with Azure API Management and Azure Functions. If you have any questions or comments please feel free to reach out to me.

Deploy and connect Azure Function to SQL in Azure

Paul Riviera — Sun, 26 Feb 2023 20:14:11 +0000

This Article is the third in the series about using Azure Form Recognizer to extract data from a paystub and save it to a database. In the previous article we converted the console application to an Azure Function. Here we will setup the Azure environment and deploy the Azure Function to Azure. The code we will be deploying was written in the previous articles, if you haven't followed along the code is also available on GitHub.

This article also assumes you have provisioned the following:

Azure SQL Database
Azure Form Recognizer
Azure KeyVault

All these were setup in the initial article of this series, I encourage you to take a look but if you're in a hurry the scripts can be found on GitHub

Update KeyVault for RBAC Authentication

Because the Azure Function will be running under a Managed Identity, we need to update the KeyVault to allow the Azure Function to access the secrets stored in the KeyVault through it's Active Directory identity. To do this we need to enable RBAC authorization on the KeyVault. To complete this step you need the User Access Administrator role assigned. If you already have a KeyVault that needs RBAC enabled (the previous articles did not enable RBAC) you can run the following script to enable RBAC on the KeyVault. If you don't have the resources provisioned yet, you can run the updated Provision Form Recognizer Resources which will provision the KeyVault with RBAC enabled.

NOTE: If creating a new KeyVault with RBAC ensure you have Key Vault Secrets Officer role assigned so you can save secrets to the KeyVault.

Step 1: Enable RBAC on KeyVault

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $KeyVaultName
)

# -------------------------------------------------------
# Variables
# -------------------------------------------------------
$RESOURCE_GROUP_NAME = $ResourceGroupName
$KEY_VAULT_NAME = $KeyVaultName

# -------------------------------------------------------
# Update Key Vault to Enable RBAC Authorization
# -------------------------------------------------------
az keyvault update `
    --name $KEY_VAULT_NAME `
    --resource-group $RESOURCE_GROUP_NAME `
    --enable-rbac-authorization true

Setup Azure Function App Resources

Before we can deploy our function we need an Azure Function App created in Azure. The following script will create a C# Azure Function App with an associated Application Insights component and Storage Account. Application Insights is used to monitor the Azure Function. The Storage Account is used to store the function code and logs.

Step 1: Create Azure Function App

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $StorageAccountName,
    [Parameter(Mandatory = $false)]
    [String]
    $StorageAccountType = "Standard_LRS",
    [Parameter(Mandatory = $true)]
    [String]
    $FunctionAppName,
    [Parameter(Mandatory = $true)]
    [String]
    $Location
)

$RESOURCE_GROUP_NAME = $ResourceGroupName
$LOCATION = $Location

$FUNC_APP_NAME = $FunctionAppName
$FUNC_STORAGE_ACCOUNT = $StorageAccountName
$FUNC_STORAGE_ACCOUNT_TYPE = $StorageAccountType

# -------------------------------------------------------
# Provision Resource Group
# -------------------------------------------------------
az group create `
    --name $RESOURCE_GROUP_NAME `
    --location $LOCATION

# -------------------------------------------------------
# Provision Storage Account
# -------------------------------------------------------
az storage account create `
    --name $FUNC_STORAGE_ACCOUNT `
    --resource-group $RESOURCE_GROUP_NAME `
    --location $LOCATION `
    --sku $FUNC_STORAGE_ACCOUNT_TYPE

# -------------------------------------------------------
# Provision Function App
# -------------------------------------------------------
az functionapp create `
    --resource-group $RESOURCE_GROUP_NAME `
    --name $FUNC_APP_NAME `
    --storage-account $FUNC_STORAGE_ACCOUNT `
    --consumption-plan-location $LOCATION `
    --runtime dotnet `
    --assign-identity [system] `
    --functions-version 4 `
    --os-type Windows `
    --https-only true

Step 2: Assign Permissions to Azure Function

In order for the function to call Key Vault it needs the Key Vault Secrets User role assigned. To do this we need to get the Azure Function's Object ID. The following script will get the Azure Function's Object ID and assign the Key Vault Secrets User role to the Azure Function.

Note: The script is designed to accept an array of Roles in case you need to assign more roles in the future.

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $FunctionAppName,
    [Parameter(Mandatory = $true)]
    [String[]]
    $RoleAssignments
)

# -------------------------------------------------------
# Define Variables
# -------------------------------------------------------
$RESOURCE_GROUP_NAME = $ResourceGroupName
$FUNC_APP_NAME = $FunctionAppName

# -------------------------------------------------------
# Retrieve Resource Group ID for use as Scope
# -------------------------------------------------------
$RESOURCE_GROUP_ID = az group show `
    --name $RESOURCE_GROUP_NAME `
    --query id

# -------------------------------------------------------
# Assign Role to Function App
# -------------------------------------------------------
$FUNC_ID = az functionapp show `
    --resource-group $RESOURCE_GROUP_NAME `
    --name $FUNC_APP_NAME `
    --query id

$PRINCIPAL_ID = az functionapp identity show `
    --ids $FUNC_ID `
    --query principalId

foreach ($role in $RoleAssignments) {
    az role assignment create `
        --assignee $PRINCIPAL_ID `
        --role $role `
        --scope $RESOURCE_GROUP_ID
}

Step 3: Set Azure Function App Settings

Recall in the previous article we set our environment variables in the local.settings.json file. In this step we will set the applicaiton settings, which populate our environment variables, in the Azure Function App. The following script will set the environment variables in the Azure Function App.

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $FunctionAppName,
    [Parameter(Mandatory = $true)]
    [String[]]
    $AppSettings
)

# -------------------------------------------------------
# Define Variables
# -------------------------------------------------------
$RESOURCE_GROUP_NAME = $ResourceGroupName
$FUNC_APP_NAME = $FunctionAppName
$APP_SETTINGS = $AppSettings

# -------------------------------------------------------
# Assign Azure Function App Settings
# -------------------------------------------------------
az functionapp config appsettings set `
    --resource-group $RESOURCE_GROUP_NAME `
    --name $FUNC_APP_NAME `
    --settings $APP_SETTINGS

Thats all that needs to be done to the function app. Next we need to configure the Azure SQL Database to allow our Azure Function to connect to it.

Configure Azure SQL Server

For the purposes of this article we will be assigning the Azure Function's Identity to the server admin on the SQL Database. This is not recommended for production environments. For production environments you should create a SQL Server User and assign the Azure Function's Identity to that user. However I wanted to keep this article simple and focused on the function deployment.

Step 1: Create SQL Server Firewall Rule for Azure Services

Similar to our Admin approach I will be opening the firewall to allow all Azure IP addresses, I do this by assigning the IP start and end addresses to 0.0.0.0 this is the simplest approach for our purposes.

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $SqlServerName
)

# -------------------------------------------------------
# Variables
# -------------------------------------------------------
$RESOURCE_GROUP_NAME = $ResourceGroupName
$SQL_SERVER_NAME = $SqlServerName

$START_IP = "0.0.0.0" # Allow all Azure IP addresses
$END_IP = "0.0.0.0" # Allow all Azure IP addresses

# -------------------------------------------------------
# Configure a server-based firewall rule
# -------------------------------------------------------
az sql server firewall-rule create `
    --resource-group $RESOURCE_GROUP_NAME `
    --server $SQL_SERVER_NAME `
    --name AzureServicesRule `
    --start-ip-address $START_IP `
    --end-ip-address $END_IP

Step 2: Configure Server Admin

When I first created the SQL Server I set it to only allow Active Directory authentication, and set the current user as the admin. This means I could access the SQL Server using my Azure AD credentials. If I assign only the function to the Server Admin role I won't be able to access the server with my own credentials. The solution is to create an Active Directory Group and assign that group to the Server Admin role. Then I only need to add my user and the function identity to the group; the script for this is below:

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $SqlServerName,
    [Parameter(Mandatory = $true)]
    [String]
    $FunctionAppName
)

# -------------------------------------------------------
# Define Variables
# -------------------------------------------------------
$SQL_GROUP_NAME = "${SqlServerName}-admins"

# -------------------------------------------------------
# Create AD Group
# -------------------------------------------------------
$GROUP_OBJECT_ID = az ad group create `
    --display-name $SQL_GROUP_NAME `
    --mail-nickname $SQL_GROUP_NAME `
    --query id `
    --output tsv

# -------------------------------------------------------
# Assign AD Group to SQL Server as Admins
# -------------------------------------------------------
az sql server ad-admin create `
    --resource-group $ResourceGroupName `
    --server $SqlServerName `
    --display-name $SQL_GROUP_NAME `
    --object-id $GROUP_OBJECT_ID

# -------------------------------------------------------
# Add Current Logged in User to AD Group
# -------------------------------------------------------
$CURRENT_USER_NAME_OBJECT_ID = az ad signed-in-user show `
    --query id `
    --output tsv

az ad group member add `
    --group $GROUP_OBJECT_ID `
    --member-id $CURRENT_USER_NAME_OBJECT_ID

# -------------------------------------------------------
# Add Function App Identity to AD Group
# -------------------------------------------------------
$FUNCTION_PRINCIPAL_ID = az functionapp identity show `
    --resource-group $ResourceGroupName `
    --name $FunctionAppName `
    --query "principalId" `
    --output tsv

az ad group member add `
    --group $GROUP_OBJECT_ID `
    --member-id $FUNCTION_PRINCIPAL_ID

Deploy and Test Function App

To deploy the function app to Azure we will use the azure-function-core-tools command line tool. If you followed along with the previous article this should already be installed. Because azure function names need to be unique accross Azure you only need to specify the function name when deploying. Simply navigate to the directory where your function app project files are located and run the following command:

func azure functionapp publish <Your Azure Function App Name>

Once the deploy is complete I encourage you to test the function in Azure. You may notice it's still a little slow, but now that it is in Azure we can focus on optimization of our Architecture and Code. In order to optimize we need to observe our application live and understand where the bottle necks are. This will be the topic of the next article in this series.

Converting PayStub logic to an Azure Function

Paul Riviera — Tue, 21 Feb 2023 00:27:30 +0000

In the previous article on this topic I created a console application that can be used to process a Workday Paystub (because thats what my company uses) PDF file and convert it to SQL. In this article I we will migrate the working console application to an Azure Function.

While the console application was mainly to test the logic, the Azure Function will be the first version of this feature in a more formal sense, like all applications it will need to be observed and optimized once it is in production. If you find any issues with the code the source code is available on GitHub

NOTE: Something you will notice is the console app was written in dotnet 7.0 and the Azure Function is written in dotnet 6.0 (LTS). This is because Azure Functions use the dotnet 6.0 runtime, that being said this code is still compatible with dotnet 7.0 and can be upgraded when needed.

Prerequisites

You will need to install the Azure Functions Core Tools in order to follow along with the code.

Getting Started

Just to outline the steps we will be taking:

Move core logic into a Class Library.
Create an Azure Function and implement the core functionality.
Test the Azure Function locally.

STEP 1: Move core logic into a Class Library

We begin by creating a dotnet solution with a class library, we will do all of this with the dotnet cli; the process is easier to follow along that way.
Run the following to create a new solution and class library:

dotnet new sln --output PaystubService # Create Solution
cd PaystubService # Change to Solution Directory
dotnet new classlib --framework net6.0 --output PaystubLibrary # Create Class Library
dotnet sln add PaystubLibrary # Add Class Library to Solution so it can be referenced

Now we have a dotnet solution with a class library. We will move the core logic from the console application into the class library. If you recall from the console application the core logic was broken down into the following steps:

Get the Paystub file from the request.
Get KeyVault Values for Form Recognizer.
Call Form Recognizer.
Convert the Form Recognizer Table result to SQL.
Connect to SQL and run the SQL commands.

We will skip the first step here as it will be handled in the actual function, but the rest of the steps will be moved into the class library. The class library will then be referenced by the Azure Function.

Before we begin adding the code to the class library, we need to install our NuGet packages.

NOTE: The first four packages are the same ones used in the console application. The last package had to be updated to ensure compatibility with dotnet 6.0.

dotnet add package Azure.Identity
dotnet add package Azure.AI.FormRecognizer
dotnet add package Azure.Security.KeyVault.Secrets
dotnet add package Microsoft.Data.SqlClient
dotnet add package AzFormRecognizer.Table.ToSQL

Now we can begin adding the code to the class library. The first step is to create a new file called PaystubProcessor.cs and add the following code:

using Azure;
using Azure.Identity;
using Azure.Security.KeyVault.Secrets;
using Azure.AI.FormRecognizer.DocumentAnalysis;
using AzFormRecognizer.Table;
using AzFormRecognizer.Table.ToSQL;
using Microsoft.Data.SqlClient;

public class PaystubProcessor
{
    public async Task<(DocumentDetails, IEnumerable<string>)> ProcessPaystubAsync(Stream fileStream, string fileName)
    {
        throw new NotImplementedException();
    }

    private async Task<(string Endpoint, string ApiKey)> GetKeyVaultSecretsAsync(string keyVaultName)
    {
        throw new NotImplementedException();
    }

    private async Task<AnalyzeResult> AnalyzeWithFormRecognizerAsync(Stream fileStream, string endpoint, string apiKey)
    {
        throw new NotImplementedException();
    }

    private IEnumerable<string> ConvertToSQL(PaystubFormat format, AnalyzeResult result, DocumentDetails details)
    {
        throw new NotImplementedException();
    }

    public async Task UploadToSQLAsync(string serverName, string databaseName, IEnumerable<string> commands, bool createTables = false)
    {
        throw new NotImplementedException();
    }
}

We will now begin filling in the code for each of the methods. The first method ProcessPaystubAsync and the last method UploadToSQLAsync will be called by the Azure Function. The middle three methods are helper methods that will be used by the first and last methods, we will fill those out first.

GetKeyVaultSecretsAsync

The first method we will fill out is GetKeyVaultSecretsAsync. This method will be used to get the Form Recognizer endpoint and API key from KeyVault.

public async Task<(string Endpoint, string ApiKey)> GetKeyVaultSecretsAsync(string keyVaultName)
{
    var keyVaultUri = new Uri($"https://{keyVaultName}.vault.azure.net");
    var secretClient = new SecretClient(keyVaultUri, new DefaultAzureCredential());

    var formRecognizerEndpoint = await secretClient.GetSecretAsync("FormRecognizerEndpoint");
    var formRecognizerKey = await secretClient.GetSecretAsync("FormRecognizerKey");

    return (formRecognizerEndpoint.Value.Value, formRecognizerKey.Value.Value);
}

AnalyzeWithFormRecognizerAsync

The next method we will fill out is AnalyzeWithFormRecognizerAsync. This method will be used to call Form Recognizer and get the results.

public async Task<AnalyzeResult> AnalyzeWithFormRecognizerAsync(Stream fileStream, string endpoint, string apiKey)
{
    string modelName = "prebuilt-layout";

    AzureKeyCredential credential = new AzureKeyCredential(apiKey);
    var documentClient = new DocumentAnalysisClient(new Uri(endpoint), credential);

    AnalyzeDocumentOperation operation = await documentClient.AnalyzeDocumentAsync(WaitUntil.Completed, modelName, fileStream);
    return operation.Value;
}

ConvertToSQL

The next method we will fill out is ConvertToSQL. This method will be used to convert the Form Recognizer results into SQL commands. Notice the Workday.FormatPaystub method, this is a method that is used to assign relationships between the tables and to add names to any tables that don't have a name. Similar to the previous console application, you should pass in a method that will format the results for your specific file.

public IEnumerable<string> ConvertToSQL(PaystubFormat format, AnalyzeResult result, DocumentDetails details)
{
    switch (format)
    {
        case PaystubFormat.Workday:
            var listOfSQLCommands = result.Tables.ToSQL(details, Workday.FormatPaystub);
            return listOfSQLCommands;
        default:
            throw new Exception("Paystub format not supported.");
    }
}

ProcessPaystubAsync

The next method we will fill out is ProcessPaystubAsync. This method will be used to call the other methods and return the results.

public async Task<(DocumentDetails, IEnumerable<string>)> ProcessPaystubAsync(Stream fileStream, string fileName, string keyVaultName)
{
    var details = new DocumentDetails()
    {
        Title = fileName,
        Id = Guid.NewGuid().ToString()
    };

    var (endpoint, apiKey) = await GetKeyVaultSecretsAsync(keyVaultName);
    var result = await AnalyzeWithFormRecognizerAsync(fileStream, endpoint, apiKey);

    var commands = ConvertToSQL(PaystubFormat.Workday, result, details);

    return (details, commands);
}

UploadToSQLAsync

The last method we will fill out is UploadToSQLAsync. This method will be used to connect to SQL and run the SQL commands.

public async Task UploadToSQLAsync(string serverName, string databaseName, IEnumerable<string> commands, bool createTables = false)
{
    string ConnectionString = $"Server={serverName}.database.windows.net; Authentication=Active Directory Default; Encrypt=True; Database={databaseName}";

    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        await connection.OpenAsync();

        if (createTables)
        {
            var createTableCmds = commands.Where(cmd => cmd.Contains("CREATE TABLE")).ToList();
            foreach (var sqlTableStr in createTableCmds)
            {
                using (var command = new SqlCommand(sqlTableStr, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        var inserValuesCmds = commands.Where(cmd => !cmd.Contains("CREATE TABLE")).ToList();
        foreach (var sqlTableStr in inserValuesCmds)
        {
            using (var command = new SqlCommand(sqlTableStr, connection))
            {
                command.ExecuteNonQuery();
            }
        }

        await connection.CloseAsync();
    }
}

Now that we have filled out all the methods, we have our core functioanlity. Notice we are not retrieving any environment variables or configuration settings. We will be grabbing all our environment variables from the Azure Function App Settings.

STEP 2: Create Azure Function and reference code

First step is to create the Azure Function app, to do this we will need the Azure Function Core Tools. Once you have installed the Azure Function Core Tools, you can create a new Azure Function with the following commands, just be sure you are in the same directory as the PaystubService solution.

Create PaystubFunction directory and change directory to it

mkdir PaystubFunction && cd PaystubFunction

Create Azure Function project

func init --language "C#" --worker-runtime dotnet --name PaystubFunction

Create Azure Function

func new --template "HttpTrigger" --name "Processor" --authlevel "function"

Add Azure Function to solution

cd ../ && dotnet sln add PaystubFunction

Return to the Azure Function directory and add the PaystubLibrary project reference

cd PaystubFunction && dotnet add reference ../PaystubLibrary

You can go ahead and remove the default code in the Processor.cs Run method and replace it with the following:

[FunctionName("Processor")]
public static async Task<IActionResult> Run([HttpTrigger(AuthorizationLevel.Function, "post", Route = null)] HttpRequest req, ILogger log)
{
    var formCollection = await req.ReadFormAsync();
    if (formCollection.Files.Count == 0)
    {
        return new BadRequestObjectResult("No files were uploaded.");
    }
    log.LogInformation($"$Processing ${formCollection.Files.Count} files.");

    string keyVaultName = Environment.GetEnvironmentVariable("KEY_VAULT_NAME");
    if (string.IsNullOrEmpty(keyVaultName))
    {
        throw new Exception("Please set the KEY_VAULT_NAME environment variable.");
    }

    string serverName = Environment.GetEnvironmentVariable("SQL_SERVER_NAME");
    if (string.IsNullOrEmpty(serverName))
    {
        throw new Exception("Please set the SQL_SERVER_NAME environment variable.");
    }

    string databaseName = Environment.GetEnvironmentVariable("SQL_DATABASE_NAME");
    if (string.IsNullOrEmpty(databaseName))
    {
        throw new Exception("Please set the SQL_DATABASE_NAME environment variable.");
    }

    var processor = new PaystubProcessor();
    List<string> filesProcessed = new List<string>();
    List<string> commands = new List<string>();

    foreach (var file in formCollection.Files)
    {
        log.LogInformation($"Processing file: {file.FileName}");
        var fileStream = file.OpenReadStream();

        var (details, sqlCommands) = await processor.ProcessPaystubAsync(fileStream, file.FileName, keyVaultName);
        commands.AddRange(sqlCommands);
        filesProcessed.Add(details.Id);
    }

    try {
        await processor.UploadToSQLAsync(serverName, databaseName, commands, createTables: true);
    } catch (Exception ex) {
        log.LogError(ex, "Error uploading to SQL");
        return new BadRequestObjectResult(ex.Message);
    }

    return new OkObjectResult(filesProcessed);
}

STEP 3: Test the Function Locally

Inorder to test the function locally you will need to populate the Azure Function App Settings with your environment variables. Look for the local.settings.json file in the PaystubFunction directory. You can add the following to the Values section:

{
    "Values": {
        "KEY_VAULT_NAME": "YOUR_KEY_VAULT_NAME",
        "SQL_SERVER_NAME": "YOUR_SQL_SERVER_NAME",
        "SQL_DATABASE_NAME": "YOUR_SQL_DATABASE_NAME"
    }
}

Once you have added the environment variables, navigate to the PaystubFunction directory and run the following command to start the Azure Function:

func start

Assuming there are no build errors and the Azure Function starts successfully, you can test the function by sending a POST request to the Azure Function endpoint. You can use the following PowerShell script to test the function:

NOTE: You will need to update the payslip-1.pdf file path to match your local file path.

curl http://localhost:7071/api/Processor -i -X POST -H "Content-Type: multipart/form-data" -F "Files=@payslip-1.pdf"

You will notice the request takes some time to complete, this is an indicator that the Azure Function is working as expected, but is in need of some optimization. We will be optimizing the Azure Function in the next few articles. In the meantime, I hope you found this article helpful. If you have any questions or comments, please feel free to reach out to me.

Converting PayStub to SQL with Azure Form Recognizer

Paul Riviera — Tue, 07 Feb 2023 10:24:48 +0000

Why

Like many people I set a series of goals in the beginning of the year. For 2023, one goal was to more accuratly measure aspects of my life, in this case my finances. I have tried using numerous financial tracking apps and I have never been pleased, if anyone has a recommendation please let me know. I decided to take a different approach and try to build my own solution; to start, I am putting the input (income from job) into a sql database so I can being to analyze it. The code outlined here is the first step in that process, you can find the complete codebase on my GitHub.

Begin by provisioning Azure resources

The scripts below prevision the following Azure resources:

NOTE: I choose to use Azure Key Vault to store my Azure Form Recognizer key and endpoint, but you can use any key management method you choose.

Create Azure Form Recognizer with Azure Key Vault

I began with provisioning the Azure Form Recognizer, the code for which is below.

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $FormRecognizerName,
    [Parameter(Mandatory = $true)]
    [String]
    $KeyVaultName,
    [Parameter(Mandatory = $true)]
    [String]
    $Location
)

# ------------------------------------------------------------------------------
# Variables
# ------------------------------------------------------------------------------
$RESOURCE_GROUP_NAME = $ResourceGroupName
$FORM_RECOGNIZER_ACCOUNT = $FormRecognizerName
$KEY_VAULT_NAME = $KeyVaultName

# ------------------------------------------------------------------------------
# Provision Resource Group
# ------------------------------------------------------------------------------
az group create `
    --name $RESOURCE_GROUP_NAME `
    --location $LOCATION

# ------------------------------------------------------------------------------
# Provision Azure Key Vault
# ------------------------------------------------------------------------------

az keyvault create `
    --name $KEY_VAULT_NAME `
    --resource-group $RESOURCE_GROUP_NAME `
    --location $LOCATION

# ------------------------------------------------------------------------------
# Provision Azure Form Recognizer
# ------------------------------------------------------------------------------

$FORM_RECOGNIZER_ACCOUNT_ENDPOINT = az cognitiveservices account create `
    --kind "FormRecognizer" `
    --name $FORM_RECOGNIZER_ACCOUNT `
    --resource-group $RESOURCE_GROUP_NAME `
    --location $LOCATION `
    --sku "S0" `
    --assign-identity `
    --yes `
    --query "properties.endpoint" `
    --output tsv

$FORM_RECOGNIZER_ACCOUNT_KEY = az cognitiveservices account keys list `
    --name $FORM_RECOGNIZER_ACCOUNT `
    --resource-group $RESOURCE_GROUP_NAME `
    --query "key1" `
    --output tsv

# ------------------------------------------------------------------------------
# Store Azure Form Recognizer Keys in Vault
# ------------------------------------------------------------------------------

az keyvault secret set `
    --vault-name $KEY_VAULT_NAME `
    --name "FormRecognizerEndpoint" `
    --value $FORM_RECOGNIZER_ACCOUNT_ENDPOINT

az keyvault secret set `
    --vault-name $KEY_VAULT_NAME `
    --name "FormRecognizerKey" `
    --value $FORM_RECOGNIZER_ACCOUNT_KEY

Create Azure SQL Server and Database

Next step is to provision the SQL Database, the script for which is below. If you choose to copy this script and run it in a pipeline, GitHub Actions as an example, you will need to update the external admin on the SQL Server. Similarly the Client IP Range should not be added when run in a pipeline, but rather when you are running the script locally. Feel free to modify the script to suit your needs.

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $SqlServerName,
    [Parameter(Mandatory = $true)]
    [String]
    $SqlDatabaseName,
    [Parameter(Mandatory = $true)]
    [String]
    $ClientIPStart,
    [Parameter(Mandatory = $true)]
    [String]
    $ClientIPEnd,
    [Parameter(Mandatory = $true)]
    [String]
    $Location
)

# ------------------------------------------------------------------------------
# Variables
# ------------------------------------------------------------------------------

$RESOURCE_GROUP_NAME = $ResourceGroupName
$LOCATION = $Location

$SQL_SERVER_NAME = $SqlServerName
$SQL_DATABASE_NAME = $SqlDatabaseName

$START_IP = $ClientIPStart
$END_IP = $ClientIPEnd

# ------------------------------------------------------------------------------
# Provision Resource Group
# ------------------------------------------------------------------------------
az group create `
    --name $RESOURCE_GROUP_NAME `
    --location $LOCATION

# ------------------------------------------------------------------------------
# Provision Server (for current signed-in user)
# ------------------------------------------------------------------------------
$SQL_ADMIN_NAME = az ad signed-in-user show `
    --query displayName `
    --output tsv

$SQL_ADMIN_USER_OBJECT_ID = az ad signed-in-user show `
    --query id `
    --output tsv

az sql server create `
    --name $SQL_SERVER_NAME `
    --resource-group $RESOURCE_GROUP_NAME `
    --location $LOCATION `
    --enable-ad-only-auth `
    --external-admin-principal-type User `
    --external-admin-name $SQL_ADMIN_NAME `
    --external-admin-sid $SQL_ADMIN_USER_OBJECT_ID

# ------------------------------------------------------------------------------
# Configure a server-based firewall rule
# ------------------------------------------------------------------------------
az sql server firewall-rule create `
    --resource-group $RESOURCE_GROUP_NAME `
    --server $SQL_SERVER_NAME `
    --name AllowMyIp `
    --start-ip-address $START_IP `
    --end-ip-address $END_IP

# ------------------------------------------------------------------------------
# Create a database
# ------------------------------------------------------------------------------
az sql db create `
    --resource-group $RESOURCE_GROUP_NAME `
    --server $SQL_SERVER_NAME `
    --name $SQL_DATABASE_NAME `
    --edition GeneralPurpose `
    --compute-model Serverless `
    --family Gen5 `
    --capacity 2

Now that resources are provisioned we can begin to build the application.

Building the application.

I chose to build a console app because it will be easy to integrate into my home automation, but in the future I would like to move it to an Azure Function.

lets review the goal again: to pass in a PDF (because thats what I have) and have the application extract the data and insert it into the SQL database.

Setup the project

I created a new console app using the .NET CLI, but you can use Visual Studio or Visual Studio Code to create a new console app.

dotnet new console --name "<Name of your project>"

NOTE: I suggest also adding a .gitignore for dotnet at the repository root as this command will not create one for you.

Add NuGet packages

dotnet add package Azure.Identity
dotnet add package Azure.AI.FormRecognizer
dotnet add package Azure.Security.KeyVault.Secrets
dotnet add package Microsoft.Data.SqlClient
dotnet add package AzFormRecognizer.Table.ToSQL

Before we begin to write code lets talk through the packages.

Azure.Identity: Used to authenticate to Azure services, in the code below I use the DefaultAzureCredential which require you to be logged into Azure through several methods like the Azure CLI.
Azure.AI.FormRecognizer: Used to interact with the Form Recognizer service.
Azure.Security.KeyVault.Secrets: Used to interact with the Key Vault service.
Microsoft.Data.SqlClient: Used to interact with the Azure SQL Server instance.
AzFormRecognizer.Table.ToSQL: This is a custom package I created to help with the conversion of the Form Recognizer output to SQL. The logic is currently minimal but I plan to extend the functionality with time. Feel free to explore the package and contribute on GitHub.

The Code

I break out the code into 5 core sections to make it easier to follow. To keep things simple, I will simply be placing these sections one after another in the Program.cs file. Feel free to break these sections into functions or different files as you see fit, afterall in its current form unit tests would be painful.

You can remove the Console.WriteLine("Hello, World!"); line as it is not needed.