AI Enriched Search Index for Property Maintenance Photos

This project showcases how Generative AI can enhance the processing of property maintenance photos to identify priority issues like mould and damp.

It achieves this by:

• Creating a sample SQL Database to simulate an existing maintenance request system
• Using GPT-4 to generate text descriptions for case photos, offering a summary of the issue, the required tradesperson to fix, and a flag where mould is present
• Writing the AI insights back to the sample maintenance request system for integration with existing frontends (PowerBI, WebApp etc)
• Establishing an Azure AI Search index for text vector searches and Gen AI inference via chat interfaces

This is for demo purposes only.

Table of Contents

1. Architecture
2. Chat with the data
3. View in PowerBI
4. Sample Data
5. Processing
6. Getting Started
7. Deploying
8. Connecting Azure AI Studio
9. Connecting PowerBI
10. Removing Resources
11. Conclusion

Architecture

Chat with the Data

After running the script, you can chat with the data in Azure AI Studio by using the "Add your data" feature in the chat playground:

View in PowerBI

Alternatively, you can leverage Power BI to visualize the data and gain insights into maintenance requests by connecting to the SQL Database:

Sample Data

The photos located in the ./data folder are publicly sourced images of various property maintenance issues, such as broken windows, mould, blocked gutters, and water damage. Feel free to replace these with your own images if you wish to tailor the demonstration to your own property maintenance images . Only '.png', '.jpg', '.jpeg' file types are supported.

Maintenance case data such as CaseID and CustomerID are randomly generated for each case/photo for simulation purposes. See field mappings in the Processing section for more details.

Processing

During the processing of the images, the following steps are performed:

1. Sample Database Creation: Each image is uploaded to Azure Blob Storage and a sample Azure SQL database is created to mimic an existing dataset of maintenance requests.

2. AI Image Analysis:

   • Description: A text description of the photo is generated using GPT-4 Vision and stored in Azure SQL.
   • Mould Detection: The detection of Mould is extracted from the descriptions and written to a dedicated column in Azure SQL.
   • Vector Representation: A vector representation of the photo description is generated using Azure OpenAI text embedding model text-embedding-ada-002.

3. Data Storage:

   • Azure SQL Database: The analysis results (Description and MouldDetected) are stored in the Azure SQL database.
   • Azure AI Search Index: A JSON file containing the case and analysis results is created and used to populate the Azure AI Search index.

The following Index fields are created as part of the processing:

Field	Description
Description	The text description of the photo generated by GPT-4 Vision.
ImageURL	The URL of the photo stored in Azure Blob Storage.
vector	The vector representation of the photo description generated by Azure OpenAI.
FileName	The name of the file.
CustomerID	The identifier of the customer associated with the photo. (Randomly generated)
CaseID	The identifier of the case associated with the photo. (Randomly generated)
MouldDetected	A boolean indicating whether mould is detected in the photo.
DateOpened	The date when the case was opened. (Randomly generated)
JobAssigned	If the job has been assigned. (Randomly generated)

Please note this approach does not:

• Create image vectors (for image to image searches)
• Use Azure AI Search integrated vectorization
• Create an indexer for a scheduled 'pull' of data.

Getting Started

You have a few options for setting up this project. The easiest way to get started is GitHub Codespaces, since it will setup all the tools for you, but you can also set it up locally if desired.

GitHub Codespaces

You can run this repo virtually by using GitHub Codespaces, which will open a web-based VS Code in your browser:

Once the codespace opens (this may take several minutes), open a terminal window and skip to Deploying.

Local Environment

1. Install the required tools:

   • ODBC Driver
   • Azure Developer CLI
   • Python 3.9, 3.10, or 3.11
     • Important: Python and the pip package manager must be in the path in Windows for the setup scripts to work.
     • Important: Ensure you can run python --version from console. On Ubuntu, you might need to run sudo apt install python-is-python3 to link python to python3.
   • Git
   • Powershell 7+ (pwsh) - For Windows users only.
     • Important: Ensure you can run pwsh.exe from a PowerShell terminal. If this fails, you likely need to upgrade PowerShell.

Finally, clone this repository to your local machine and move to the next section.

Deploying

Permissions

To deploy the Azure Resources you'll need Contributor rights at the subscription scope. Alternatively, You can use existing resources. See: Using existing resources

Starting from scratch

Execute the following commands, if you don't have any pre-existing Azure services and want to start from a fresh deployment.

1. Clone this repository to your local machine.
2. Run azd auth login
3. Run azd up - This will provision Azure resources and deploy this demo to those resources

Using existing resources

Follow the steps below if using your own Azure resources. If you executed the 'start from scratch' commands above, this step should be skipped.

The following Azure resources are required:

• Azure Storage Account: For storing images.
• Azure OpenAI Service: To generate image descriptions and vector embeddings. Must be in a region that supports gpt-4-turbo version turbo-2024-04-09, check here.
  • Azure OpenAI models deployments: text-embedding-ada-002 and gpt-4-turbo version turbo-2024-04-09.
• Azure AI Search: To create and manage the search index. Must be in a region that supports semantic ranking, check here.
• Azure SQL Database: To create and store the sample data. A SQL user is required with db_owner permissions for the connection string.

Then set the AZD environment variables to match your resources:

1. Run azd env set AZURE_OAI_ENDPOINT {Name of existing Azure OpenAI service}

2. Run azd env set AZURE_OAI_EMBED_DEPLOYMENT_NAME {Name of existing embedding model deployment}

3. Run azd env set AZURE_OAI_API_KEY {API key for Azure OpenAI service}

4. Run azd env set AZURE_SEARCH_SERVICE_ENDPOINT {Endpoint of existing Azure AI Search service}

5. Run azd env set AZURE_SEARCH_API_KEY {API key for Azure AI Search service}

6. Run azd env set AZURE_OAI_GPTVISION_DEPLOYMENT_NAME {Name of existing GPT-4 Vision deployment}

7. Run azd env set AZURE_BLOB_CONNECTION_STRING {Connection string for Azure Blob Storage}

8. Run azd env set AZURE_SQL_SERVER {Name of existing Azure SQL server}

9. Run azd env set AZURE_PYTHON_SQL_CONNECTION_STRING {SQL Connection string in the following format}

   Driver={ODBC Driver 18 for SQL Server};Server=tcp:<YOUR_AZURE_SQL_SERVER_NAME>.database.windows.net,1433;Database=<YOUR_DATABASE_NAME>;UID=<YOUR_USER_NAME>;PWD=<YOUR_USER_PASSWORD>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30
   

10. Run azd deploy

Deploying again

If you want to run the image and index process again, but don't need to re-provision the Azure resources. You can just run:

azd deploy

Note: this will delete and recreate the SQL data and index

If you've changed the infrastructure files (infra folder or azure.yaml), then you'll need to re-provision the Azure resources. You can do that by running:

azd up

Connecting Azure AI Studio

You can connect the deployed resources to Azure AI Studio to use features such as the Chat playground as shown in Chat with the data.

Using Azure AI Studio requires a hub and project. Use this guide to get started. During creation of the hub you'll be prompted to connect to Azure AI Search and Azure OpenAI, select the services created as part of this demo.

If you already have a hub and project, follow the instruction here to add the Azure AI Search and/or OpenAI resources.

To add your data to the chat playground...

1. Browse to your project and select 'Chat' from the left menu.
2. Ensure gpt 3.5, gpt-4-turbo or gpt-4o deployment is selected. (If you've connected the OpenAI service from this project to your hub you should see the deployments in the list.)
3. Select the 'Add your data' tab and click on 'Add a new Data Source'
4. Select Azure AI Search as the data source. Click next.
5. Select the AI Search Service and index created as part of this demo deployment. Click next.
6. Make sure Add vector is selected and the Open AI connection is populated. Click next.
7. Name the index and make sure 'use custom field mapping' is ticked. Click next.
8. Make sure the index field mapping matches the image below and click next.

8. Click next and create.

9. On the chat playground main screen select the 'system message tab' and enter the following system prompt.

   You are an AI assistant for a housing maintenance system given context about maintenance requests through case details and image descriptions. The data shows various case information about the maintenance issue, the date it was opened and if the job has been assigned (yes/no). Use this data to give insights to internal staff using the chat. Priority is mould and any danger to health. Your welcome message should be: Hello! I'm your AI assistant for the maintenance request system.�I have access to case details and image descriptions submitted as part of maintenance requests. I can help identify the work needed and highlight priority issues. Feel free to ask me about any case!

10. Chat!. Experiment with the different models and settings under parameters and advanced settings within 'add your own data'

Connecting PowerBI

Follow the guide here to open the report template located in the 'powerbi' folder in this repo.

Connect to your DB - if not prompted select 'Transform Data > Data Source Settings' from the home menu and update the SQL server/database name and credentials. If you completed the full deployment from this project, the SQL username is appuser and the password is stored in keyvault. Use the following guide to give yourself 'Keyvault secret user' permissions to access the secrets.

Removing Resources

Delete all deployed resources by running:

azd down

You will also be given the option to purge any soft deleted resources. Note: This can take around 10 mins to complete.

Conclusion

With this repository, you should have gained a deeper understanding of how the process of enriching images with GenAI-generated descriptions is performed and how that enriched data can be presented across various channels.

Trademarks: This project may contain trademarks or logos for projects, products, or services. Authorized use of Microsoft trademarks or logos is subject to and must follow Microsoft’s Trademark & Brand Guidelines. Use of Microsoft trademarks or logos in modified versions of this project must not cause confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos are subject to those third-party’s policies.