Exhibit_D.pdf

California Department of Fish & Wildlife

RFR Management

Technical Design Document

DocuSign Envelope ID: D443F3E6-9B4F-4FAD-BF2D-E3577EE6EB8D

Table of Contents

1 Version History

2 Document Approval

3 Introduction

3.1 Overview

4 Solution Architecture

4.1 Solution Overview

5 Environment Setup

5.1 Environment Details

5.2 Promotion Process

6 Development Requirements

6.1 Azure Services

6.1.1 Microsoft Entra ID Service

6.1.2 Resource Group

6.2 Laptop Software

6.3 Software Licenses

7 Access Requirements

7.1 Resource Access

7.1.1 RFR Management

7.1.2 Azure App Resources

7.1.3 SQL Box

8 Detail technical design

8.1 API Documentation (endpoints)

8.1.1 Database schema

1 VERSION HISTORY

Version Date Author Key Differences

V4 09/28/2023 Hemanth

Balasundaram

• Uploading the clean copy of V3 for approver signatures.

V3 09/18/2023 Chandrahas

Kocharlakota

• Updated the proposed changes from CDFW Operations.

V2 08/28/2023 Chandrahas

Kocharlakota

• The existing solution framework has been upgraded to

.NET Core 8 for long-term purposes.

• This document will be versioned as V2 to accommodate the changes proposed within the document by including the REDIS cache and Microsoft Entra ID features.

V1 08/29/2023 Chandrahas

Kocharlakota

• Initial Version

2 DOCUMENT APPROVAL

CDFW DTD Branch Name Title Date Signature

IT Systems Branch

Eric Miller IT Systems Architect

IT Operations Branch Charles Jones IT Operations Branch

Chief

IT Operations Branch John Berwith IT Infrastructure and

Operational Support

Unit Manager

IT Biogeographic Data

Branch

Steve Goldman GIS Manager

9/29/2023

9/29/2023

3 INTRODUCTION

This Technical Design Document was created with input from the California Department of Fish and Wildlife (CDFW) technical team and Ravacons LLC. The purpose is to outline and come to agreement on the technical architecture and system requirements for the RFR Management project. The RFR management solution will be built using ASP.NET Core WEB API’s and ReactJS.

3.1 OVERVIEW

This document serves as a comprehensive guide for the proposed solution architecture, environment setup, development requirements, and access requirements for the RFR Management system. RFR Management is a robust .NET-based application that leverages the principles of Onion Architecture, CQRS, MediatR, Repository Pattern, and DTO patterns to deliver a scalable, maintainable, and high-performing solution.

The technical stack for RFR Management comprises the following technologies and tools:

• ASP.NET Core Web API: A modern, open-source, high-performance framework for building cloud-based internet connected applications.

• SQL Server 2022 Developer Edition: A full-featured free edition, licensed for use as a development and test database in a non-production environment.

• Visual Studio 2022: The latest IDE from Microsoft for developers, for designing, developing, testing and managing applications.

• Azure DevOps: A Microsoft product that provides version control, reporting, requirements management, project management, automated builds, lab management, testing and release management capabilities.

• Azure App Services: A fully managed platform for professional developers to build, deploy, and scale web apps.

• Azure CI/CD: Azure pipelines to implement continuous integration and continuous delivery.

• Azure SQL Managed Instance: A fully managed platform as a service (PaaS) database engine that handles most of the database management functions.

4 SOLUTION ARCHITECTURE

The following sections provide a high-level overview of the major components of the RFR

Management project.

4.1 SOLUTION OVERVIEW

This architecture contains the following components:

• A front-end application designed in ReactJS using the CA State Web Template V6 (Orange County Theme) with the following capabilities.

o A completely decoupled front-end solution with Material UI serves as a data entry point for the RFR Management application.

o Component-based architecture: Encourages the use of components, which can be reused and combined in different ways to build robust and flexible user interfaces.

o Efficient DOM manipulation: React uses a virtual DOM to minimize direct manipulation of the HTML DOM, leading to improved performance.

o One-way data binding and Flux controls: This architecture helps in maintaining the code stability and consistency.

o Unidirectional data flow: This helps in creating predictable code and is particularly useful in large applications.

o Integration capabilities: React can be easily integrated with other frameworks like Meteor, Angular, etc. Also, it has got great capabilities to connect to .NET CORE APIs and other APIs like ArcGIS.

• ASP.NET Core WEB API solution and SQL server database with latest Architecture and Design patterns.

o Onion Architecture: To emphasize separation of concerns and maintainability we are using this architecture and it has got different layers.

▪ API Layer: This is the outermost layer of the architecture and the entry point of the application. It's responsible for accepting client requests and sending responses. In your scenario, it's implemented using ASP.NET Core Web API which provides a set of HTTP services. It primarily consists of Controllers which coordinate application's flow and use MediatR to dispatch commands and queries.

▪ Application Layer: This layer has a significant role in the application, as it's orchestrating the application behavior. Beyond its duty to coordinate between the domain and the data mapping layers, it also carries part of the business logic of the application. In the application, this layer includes Data Transfer Objects (DTOs), which encapsulate data for transfer over the network, and uses the Command Query Responsibility Segregation (CQRS) pattern to clearly separate read operations (queries) from write operations (commands), both coordinated by the MediatR library. This approach provides a clean architecture, flexible enough to adapt to complex business rules and workflows.

▪ Domain Layer: This layer encapsulates the core business objects, referred to as entities, which represent meaningful concepts in your business context.

▪ Infrastructure Layer: The Infrastructure layer acts as a supporting layer, providing functionalities that are needed across different layers of the application, such as data access, networking, caching, and so on. In this application, the infrastructure layer contains the data access logic and generic repositories. These repositories serve as an abstraction on top of the raw data access operations, offering a more convenient and object-oriented way to manage persistence in the Domain layer. This layer is where you would interact with your database with ORMs like Entity Framework. This has got a lot of flexibility because in future we can use any database as a backend with very minimum changes.

Onion Architecture

Figure 1: Onion Architecture o Design Patterns

▪ CQRS (Command Query Responsibility Segregation): The software system is divided into two sub-systems or parts—the Command part and the Query part— each of them serving a different purpose.

▪ MediatR: Used to implement the Mediator pattern to ensure loosely coupled, in-process messaging between objects.

▪ Repository Pattern: Used to encapsulate the data access logic and provide better abstraction and testing capabilities.

▪ DTO (Data Transfer Object) Pattern: Used to shape the data sent over the network, improving performance in distributed applications.

• Azure SQL Managed Instance: Azure SQL managed instance Database is a fully managed relational database provided as part of Microsoft Azure's cloud services. It offers a flexible, scalable, and secure solution for data storage. Here are some of the notable features:

o Fully managed PaaS: Azure SQL Database eradicates the need for maintaining the physical infrastructure, as it is a fully managed PaaS. Microsoft Azure handles tasks like server management and patching, allowing us to focus more on the database's functionality rather than its maintenance. Security o Scalability: With Azure SQL Database, we can easily scale up or down based on your needs. It offers both on-demand and provisioned compute tiers, allowing changes to compute and storage resources in real-time.

o High Availability: Azure SQL Database is renowned for its high availability and fault tolerance. This is achieved through a technology called 'Always On', ensuring automatic failover without data loss in most cases.

o Security: Azure SQL Database employs formidable security measures such as firewall rules, Azure Active Directory integration, and threat detection. It utilizes Always Encrypted technology, facilitating private link connections and VNet service endpoints for secure network access.

o Performance Optimization: Azure SQL Database includes intelligent performance features that continuously monitor the database usage and adapt to the application.

The 'performance insights' feature can recommend indexes and identify performance-related issues.

o Automated Backups: Automated backups are another powerful feature of Azure SQL Database. It provides backups with a retention period of up to 35 days, facilitating point-in-time restore.

o Geo-Replication: Azure SQL Database supports geo-replication, allowing us to create readable secondary databases in the same or a different geographical region.

This enables load balancing and failover purposes, ensuring the service remains uninterrupted.

o Data Migration: Azure SQL Database simplifies the process of migrating data from multiple sources via the Data Migration Service (DMS).

o Integration Azure SQL Database: Integrates seamlessly with other Azure services like Power BI for data visualization, Azure Logic Apps for workflow creation, and Azure Machine Learning for predictive analytics.

• Redis Cache: Redis, which stands for Remote Dictionary Server, is an open-source, in-memory data structure store used as a database, cache, and message broker. Its versatility and performance make it a favorite amongst developers for various applications. One of the most common use cases for Redis is as a caching layer. Below, we'll delve into what makes Redis an excellent choice for caching and the benefits that come with it.

o Blazing Fast Performance

Redis operates entirely in-memory, which means data doesn’t need to be fetched from the disk. This leads to lightning-fast read and write operations, often reaching millions of operations per second, making it ideal for high-performance scenarios.

o Scalability

Redis provides various mechanisms to scale with demand, such as partitioning (sharing data across multiple Redis instances) and replication (master-slave replication to duplicate data across multiple machines). This ensures Redis can handle increased traffic loads without sacrificing performance.

o Versatile Data Structures

Unlike traditional caching systems that only support simple key-value pairs, Redis supports diverse data structures like strings, hashes, lists, sets, and sorted sets. This allows developers to implement a range of caching patterns and algorithms with ease.

o Atomic Operations

Redis supports atomic operations on these complex data structures. This ensures that operations like increments on counters, adding/removing elements from a list, or setting values in a hash are done without the risk of race conditions.

o Durability and Persistence

While Redis is primarily an in-memory database, it provides options to periodically save data to disk or append each command to a log. This ensures that in the event of a system crash, data can be recovered without significant losses.

o High Availability

With features like Sentinel and Redis Cluster, Redis offers high availability. Sentinel provides monitoring, notifications, and acts as a failover solution. Redis Cluster provides automatic partitioning and operates even when a majority of the nodes are unreachable.

o Cost-Effective

Caching Redis can lead to significant savings in infrastructure costs. By offloading the heavy read operations from primary databases to the cache, businesses can maintain smaller, cost-effective primary databases without sacrificing user experience.

o Active Community and Rich Ecosystem

Being open-source and widely adopted, Redis boasts a rich ecosystem and an active community. Regular updates, extensive documentation, and a plethora of client libraries across various languages make it a developer-friendly tool.

o Multi-utility Tool

Apart from caching, Redis can be used for various tasks like session caching, full-page caching, message brokering, real-time analytics, and more. This versatility means teams can leverage the same tool and expertise for various requirements.

• ArcGIS APIs The ArcGIS API for JavaScript is a powerful tool for integrating GIS capabilities into web applications. For ReactJS developers, this API can open-up a world of possibilities, such as:

o Mapping and Visualization: The ArcGIS API for JavaScript allows ReactJS applications to incorporate dynamic 2D and 3D maps. We can leverage ArcGIS's vast collection of base-maps or create custom maps, and enhance them with layers, symbols, and interactive pop-ups.

o Data Analysis: Spatial data analysis is a core feature of ArcGIS. With the API, ReactJS applications can perform complex spatial and statistical analysis, visualize spatial data, and create meaningful reports.

o Editing and Data Management: The API enables ReactJS applications to interact with ArcGIS's geodatabase, letting users add, update, and delete geospatial data. This data can be stored and managed in the cloud, ensuring accessibility and security.

o Geocoding and Search: ArcGIS's powerful geocoding and search functionalities can be incorporated into ReactJS applications. Users can find addresses, places, and routes, making it ideal for applications needing location-based services.

o Routing and Directions: ReactJS applications can provide detailed routing and directions using ArcGIS. This can involve point-to-point navigation, multi-stop routing, or service area analysis.

• Azure App services is a Platform as a Service (PaaS) offering from Microsoft Azure. It provides an environment for developers to build, deploy, and scale web applications and services. Azure App Service supports various programming languages, frameworks, and customizations, making it a versatile solution for a wide array of web application needs. Azure App Service comes packed with several features that make it a powerful choice for developers. Here are some of the standout features:

o Multiple Languages and Frameworks Support: Azure App Service supports a broad array of programming languages, such as .NET, Java, Node.js, Python, and PHP.

Developers can build applications in the language of their choice, ensuring flexibility and convenience. The Embed for your Customers model (App owns data) requires no user authentication to view reports, dashboards, and visualizations.

o DevOps Optimization: Azure App Service integrates seamlessly with Azure DevOps, GitHub, Docker Hub, and Bitbucket, facilitating continuous integration and continuous deployment (CI/CD) pipelines. This enables efficient code updates, testing, and deployment.

o Security and Compliance: Security is at the forefront of Azure App Service. It provides built-in infrastructure maintenance, security patching, and network protection.

Additionally, it complies with global standards like GDPR and ISO, ensuring data privacy and security.

o Integration with Azure Services: Azure App Service integrates smoothly with other Azure services, such as Azure Functions, Logic Apps, and API Management. This allows you to build comprehensive solutions, leveraging the power of the Azure ecosystem.

o Serverless Code Option: With Azure Functions, Azure App Service provides a serverless compute option, allowing developers to write less code, maintain less infrastructure, and save costs.

• Azure DevOps is a suite of development tools, services, and features provided by Microsoft

Azure that enable a robust DevOps lifecycle. It streamlines and automates the process from code integration, testing, to delivery, ensuring faster and more reliable releases. Azure DevOps provides various tools and services that help establish and manage CI/CD pipelines effectively:

o Azure Repos: Provides Git repositories for source control. They serve as the central hub where developers can share, track, and version control their code. Integration with

Azure Pipelines means any code change can trigger the CI/CD process.

o Azure Test Plans: provide a range of testing tools and services. Integrated into the

CI/CD pipeline, they allow automated testing of the software at every integration and deployment stage, ensuring any bugs or issues are promptly detected and fixed.

o Azure Artifacts: Allow for the creation, hosting, and sharing of packages across teams.

These packages can be directly integrated into CI/CD pipelines, making it easy to manage and deploy dependencies.

• Microsoft Entra ID Is Microsoft's cloud-based identity and access management service. It helps your employees sign in and access resources in external resources, such as Microsoft Office 365, the Azure portal, and thousands of other SaaS applications. It also extends on-premises Active Directory to the cloud, allowing users to leverage their existing credentials. Microsoft Entra ID comes packed with a suite of following features that bolster security, streamline access management, and enhance productivity:

o Identity and Access Management (IAM): Microsoft Entra ID provides robust IAM capabilities, enabling administrators to manage user identities and control their access to resources. This includes user provisioning, role-based access control, and directory management. Is built using React and hosted using Azure App Services.

o Single Sign-On (SSO): Microsoft Entra ID provides Single Sign-On (SSO), allowing users to sign in once with one account to access all their cloud-based applications. This simplifies the sign-in process and enhances user productivity. The public-facing end of this application will be accessible over the internet.

o Multi-Factor Authentication (MFA): To bolster security, Microsoft Entra ID provides Multi-Factor Authentication. This feature requires users to provide two or more verification methods to gain access, thereby significantly reducing the chance of malicious login attempts.

o Privileged Identity Management (PIM): Privileged Identity Management helps manage, control, and monitor access to important resources in your organization. This includes providing just-in-time privileged access to Microsoft Entra ID and Azure resources and enforcing on-demand, time-bound access to critical resources.

o Conditional Access: Microsoft Entra ID's Conditional Access feature allows administrators to define and enforce policies that evaluate the context of user login attempts and then respond accordingly, such as blocking access or requiring additional authentication steps.

o Security Reporting and Monitoring: Microsoft Entra ID provides robust security reporting and monitoring tools. Administrators can monitor suspicious activities, manage alerts, and evaluate the overall health of their organization's identities.

Solution Architecture Diagram

Figure 2: Solution Diagram

Note: In the above diagram, the font-end application (ReactJs) and the Back-end .NET Core APIs solution will be hosted in the single app service. For the depiction purposes, we have given two images for front-end and backend in the above diagram.

• Benefits of above architecture by decoupling the Front-end and Back-end solutions o Scalability: By separating the front-end and backend, you can scale them independently based on their respective workloads. For instance, if your API needs more processing power due to increased data requests, you can scale the backend without affecting the frontend.

o Flexibility in Development: Different teams can work on the frontend and backend simultaneously without stepping on each other's toes. This can expedite development processes.

o Technology Agnosticism: With a decoupled architecture, you're not tied to a particular technology stack. If you ever decide to switch from ReactJs to another front-end framework or from .NET Core to another backend framework, you can do so with minimal disruption to the other part of the system.

o Better Fault Isolation: If there's an issue with the backend, it might not directly impact the user's experience on the frontend, and vice versa. This makes it easier to identify, isolate, and address issues.

o Optimized Performance: Both layers can be optimized for their specific tasks. The backend can be optimized for data processing and business logic, while the frontend can be fine-tuned for user experience and responsiveness.

o Easier Updates & Upgrades: With separate systems, you can update or upgrade one without affecting the other. For instance, you might want to update the UI without changing the underlying API.

o Reusable Backend: The API can serve multiple frontends – web, mobile apps, third-party integrations, and more. This promotes reusability and ensures consistency across different platforms.

o Security: A clear separation between frontend and backend can lead to better security practices. The backend, which might handle sensitive data and operations, can be more tightly secured, while the frontend only gets the necessary data via API calls.

o Continuous Deployment and Integration: Decoupling makes it easier to implement CI/CD pipelines. Changes in the frontend or backend can be independently deployed without waiting for the other.

o Cost Efficiency: Depending on the infrastructure, you might find it more cost-effective to host, scale, and maintain decoupled systems, especially if they have different resource requirements.

o Focused Testing: With decoupling, testing can be more focused. Backend API endpoints can be tested separately from frontend components, ensuring each part functions correctly on its own before integration tests.

o Adaptability: With the rapid pace of technology change, having decoupled systems means you can adapt more quickly to new technologies, tools, or methodologies without a complete overhaul of the entire system.

Note: This approach will also ensure that the same set of APIs can be utilized for mobile application that is coming up in future.

5 ENVIRONMENT SETUP

This section outlines the structure for Development, hosting the system and allowing for a promotion process for Development, Test, and Production environments.

5.1 ENVIRONMENT DETAILS

• This project requires a .NET development environment and SQL Server. We recommend Visual

Studio Code, Visual Studio 2022 with .NET Core 8 and SQL Server Developer 2022.

5.2 PROMOTION PROCESS

Code changes should be promoted from development to test and finally to production, ensuring each change is thoroughly tested. Use Azure DevOps for source code management and continuous integration and deployment.

• Branching Strategy: As per the best practices, the development should always happen on the User story branch then it must be merged into the Feature branch and then to the Main branch.

The Azure CI/CD pipeline must be configured for and main branch type. This will ensure that whenever a commit happens into the repo on the main branch, a CI/CD Job would run and deploy the code to the respective Azure environment (Dev).

Figure 3: Branching strategy

6 DEVELOPMENT REQUIREMENTS

The following are the software requirements required to build and maintain the RFR Management System

6.1 AZURE SERVICES

6.1.1 Microsoft Entra ID Service

• Used for Authentication and Authorization o Microsoft Entra ID is Microsoft's cloud-based identity and access management service, which helps employees sign in and access resources based on their roles.

6.1.2 Resource Group

• Key Vault o All secure information such as usernames and passwords will be stored in Azure Key Vault, an encrypted storage repository.

• App Service o The app service will host the ASP.NET Core Web API / and web site files. It will connect to the AAD service for authentication. Decorators can be used on the API methods to ensure role-based access is upheld.

• App Insights o Telemetry and application logs will be exposed through App Insights allowing for real-time and aggregated statistics on application performance.

• Azure SQL managed instance o To store and manage data related to RFR management application

• Redis Cache Service o Cache the data for faster performance and avoid continuous calls to DB for retrieving the repetitive data.

• Redgate Service/Software:

Redgate provides services that can be integrated with Azure. Two of their products, SQL Compare and Redgate Change Control, offer features that require registration in Azure portal.

o SQL Compare is a tool that allows you to compare and synchronize SQL Server schemas. It is used to compare SQL Server database schemas, large or small, including Temporal Tables, with advanced filtering of database objects. You can explore what’s changed in each database object, down to individual lines of SQL. When you’re ready to deploy, you can create an error-free deployment script in minutes.

o Redgate Change Control helps you easily and safely version control your database schema and prepare validated deployments for SQL Server and Oracle databases. It saves the definitions of your database objects into a schema model. You can version control and share changes to the schema model with teammates to rapidly iterate on new development. When you are ready to prepare a deployment, you generate migration scripts to deploy these changes safely.

6.2 LAPTOP SOFTWARE

• Visual Studio 2022 Professional Edition

• Visual Studio Code

• SQL Server Management Studio 2022 Developer edition.

6.3 SOFTWARE LICENSES

The development team will require Visual Studio 2022 Professional Edition Licenses for the development and ArcGIS licenses for integrating the map data into the system.

7 ACCESS REQUIREMENTS

This section describes the permissions required by Ravacons LLC Developers to manage the system throughout the software development lifecycle.

7.1 RESOURCE ACCESS

7.1.1 RFR Management

Access to Dev, Test, and Production services in the Azure portal

7.1.2 Azure App Resources

All setup of Azure resources will be completed by CDFW team with the support of Ravacons team members.

• DEV / TEST

o App Service: Azure Pipelines will be used for deploying the solution.

o Vault: CDFW to setup and configure.

• PROD:

o If CDFW moves files from DEV to PROD - No access.

7.1.3 SQL Box

• DEV / TEST

o DEV : DB Owner permissions o TEST : Read/Write Permissions

• PROD:

o CDFW Run pipelines to deploy the solution and run scripts in the DB if required.

8 DETAIL TECHNICAL DESIGN

TBD.

8.1 API DOCUMENTATION (ENDPOINTS)

TBD, and will be sent in the next version of the TDD for final review and approval of this section.

8.1.1 Database schema

TBD, and will be sent in the next version of the TDD for final review and approval of this section.