VOLUME 2: SYSTEM DOCUMENTATION
Y. Yuan, S. Leelaram, S. Dahbour, M. Greene, A. Acker and E. Swartz
WRMA, Inc.
ABSTRACT
In September 2013, HHS/ASPE began a 2-year effort to design, develop, and pilot a national reporting system based on data from state adult protective services (APS) agency information systems. The project was funded by Prevention and Public Health funds through an interagency agreement with ACL. The project team conducted extensive outreach to gain an understanding of information needs. More than 40 state administrators, researchers, service providers, and other individuals in the field participated in stakeholder calls. Over 30 state representatives from 25 states participated in three in-person working sessions to discuss the uses of collected data and the key functionalities that should be included in a national system. The national system was named the National Adult Maltreatment Reporting System (NAMRS). Stakeholder meetings resulted in the general design of the data reporting system, conceptualized as three components:
- Agency Component data, submitted by all agencies, on their policies and practices.
- Case Component data on client characteristics, services, and perpetrator characteristics, provided by agencies that have report-level tracking systems.
- Key Indicators Component data consisting of aggregated data on key statistics of investigations and victims, provided by agencies that do not have report-level tracking systems or are unable to provide case-level data.
From January through May 2015, nine states--Colorado, Georgia, Illinois, Maine, Massachusetts (Disabled Persons Protection Commission), Missouri, Montana, Pennsylvania, and Texas--participated in a pilot of the data system and submitted the Agency Component and either the Key Indicators Component or the Case Component data. This report (Volume 1) consists of a description of the NAMRS Pilot, findings from the piloting process, and recommendations for the future NAMRS.
DISCLAIMER: The opinions and views expressed in this report are those of the authors. They do not necessarily reflect the views of the Department of Health and Human Services, the contractor or any other funding organization.
"Acronyms
The following acronyms are mentioned in this report and/or appendices.
| ACL | Access Control List |
|---|---|
| App | Application |
| API | Application Programming Interfact |
| APS | Adult Protective Services |
| CIDR | Classless Inter-Domain Routing |
| DBO | Database Object |
| ELMAH | Error Logging Modules and Handlers |
| ERD | Entity Relationship Diagram |
| HTTP | Hypertext Transfer Protocol |
| HTTPS | HTTP Secure |
| ID | Identifier |
| IP | Internet Protocol |
| LINQ | Language-Integrated Query |
| NAMRS | National Adult Maltreatment Reporting System |
| Portable Document Format | |
| SHA | Secure Hash Algorithm |
| SMTP | Simple Mail Transfer Protocol |
| SQL | Structured Query Language |
| SSL | Security Sockets Layer |
| TLS | Transport Layer Security |
| URL | Uniform Resource Locator |
| VHD | Virtual Hard Disk |
| VM | Virtual Machine |
| XML | Extensible Markup Language |
| XSD | XML Schema Definition |
1. Introduction
The National Adult Maltreatment Reporting System (NAMRS) Pilot was designed and developed to test the capacity to collect administrative data from state adult protective services (APS) agencies, which in turn could be transformed into information that would provide knowledge to the field on the extent and underlying features of abuse, neglect, mistreatment, and exploitation of vulnerable adults. The NAMRS Pilot design was founded upon several technical development concepts and strategies. These strategies provided insight into the key abstractions and mechanisms used in the systems architecture. The NAMRS Pilot was a modern data system utilizing state-of-the-art concepts and technologies. The major concepts used strongly support data quality, data integrity, data security, ease of operation, and effective access to the data.
Purpose of the Document
The purpose of this document is to provide technical details for the NAMRS Pilot that will serve as a guide for the operation and maintenance of the system. This document addresses the details of how aggregate and case-level administrative data were submitted, validated, and stored in the NAMRS Pilot system. The guide is focused on helping the system architects, developers, and designers understand the technical details and concepts of the NAMRS Pilot.
Organization of the Document
This document describes the main components of the NAMRS Pilot. Each component will be discussed in detail and the discussion will be framed in four parts:
-
Function--What was the function of the component and how was it implemented?
-
Access--Which users/components could access the component, and which users/components did the component access?
-
Security--What security measures were implemented by the component?
-
Configuration--Where appropriate, there is a section that explains how configuration data was stored.
2. System Architecture
The NAMRS Pilot was a multi-tiered cloud application consisting of the user interface layer, business services layer, and the data layer. The user interface layer provided the web pages with control elements to access the various functionalities. The business rules and data processing were carried out in the business services layer, with each service implementing a particular function. Data were stored in the data layer using an advanced database management system. Figure 2.1 illustrates the NAMRS Pilot architecture on the cloud platform.
The NAMRS Pilot consisted of four main system components: (1) a website that users interacted with to upload data and obtain results; (2) an application called the "Loader" for validating the Case Component data files in Extensible Markup Language (XML) format and loading them into the database; (3) a database that stored all the data for the system; and (4) a data warehouse that stored all data to be accessed by analytical users. Additional cloud components that supported the operation of the main system components were the shared storage, email client, and the temporary cache.
The NAMRS Pilot was hosted in the Microsoft Azure Cloud service. It took advantage of the services that are provided in the cloud:
Azure Web Apps--This is a managed, secure, scalable, highly-available, load-balanced, and geographically load-balanced web application hosting service. It removes the need to implement a web server or virtual machine (VM).
Azure Redis Cache--This is a managed implementation of the Redis Cache service. This provides secure, scalable, caching inside the Azure Cloud.
Azure Virtual Machine--This is a scalable, highly-available, secure, virtual server located inside the Azure Cloud. This server can be loaded with any operating system, software, and virtual networking/hardware resources needed.
Azure Blob Storage--This is a scalable, highly-available, geographically redundant data storage service for files inside the Azure Cloud.
Azure Service Marketplace--This is a marketplace for services from 3rd party vendors that are available inside the Azure Cloud.
There are many other services available inside the Microsoft Azure Cloud, but the ones above are the set that were used for the NAMRS Pilot.
The data centers for the Microsoft Azure Cloud are extremely secure, and they many different compliance certifications. More about their compliance can be found here: http://azure.microsoft.com/en-us/support/trust-center/compliance/.
| FIGURE 2.1. NAMRS Pilot System Architecture |
|---|
 |
3. Namrs Pilot Website
The NAMRS Pilot Website was the main web interface that users interacted with. This website allowed state users, federal users, technical users, and administrators to log in, view/edit data, upload XML files, download reports, etc.
Function
There were several functional types of pages on the website:
-
Administrative--These pages allowed a technical user or administrative user to manage (i.e., create/update/disable) resources, announcements, and users.
-
Account--These pages allowed any user to change their own password or log off.
-
Component Data--These pages allowed state users to enter/upload, change, and view their Agency, Case, and Key Indicators data. Federal users could view these pages for any state (although they could not change any data). Technical users and administrators could update data for any state and change the workflow status (i.e., accept/reject data).
-
Informational--These pages allowed any user to view information about NAMRS.
The website was built as a Microsoft ASP.Net MVC Web Application. The site was written in C# and used Entity Framework and LINQ for all database access. The site used HTML5, CSS3, and JavaScript, as well as many open-source components and frameworks, including jQuery, jQuery UI, Bootstrap, ELMAH, Unity, iTextSharp, and other components.
The website was hosted in the Microsoft Azure Cloud as a "Web App." A Web App (previously called an Azure Website) is a scalable, highly-available website. They provide a directory to upload the website into. They manage the all the underlying servers, security, server updates, networking, etc. Like all cloud services, if underlying hardware fails, the site is immediately moved and will become available immediately. Because of the redundant nature of the Web App service, there is no downtime for hardware upgrades and patches. If the site becomes slow because of traffic, it can be scaled to multiple server instances, and it can even spread those instances around the country and geographical load balancing will occur. The site can also be automatically scaled out based on traffic and scaled back in as traffic goes down (after hours) to keep costs down.
Access
The site was accessed directly by users, like any other website. The user logged in to access any pages--there were no pages accessible without logging in, other than the login page itself and the NAMRS XML Schema Definition (XSD) file (the XSD file that needed to be accessed by users submitting Case Component data). The reason that the XSD file was accessible was that many (most) XML validators do not have a way of logging into a website before they retrieve an XSD file. Therefore, the general paradigm on the web is to make XSD files available without login through HTTP. There was no sensitive data in the XSD file.
The site also accessed several other components in the Azure Cloud (each component is covered in more detail later):
-
Session Data--There were just a few bytes of session data used across the site. An example of this data is that when a state user logged in, their assigned state was stored into session data for convenience. Session data was generally used for performance and convenience. The NAMRS Pilot used very little session data. Session data was stored in the Azure Redis Cache service and was very fast and very secure.
-
Website Data--All data for the NAMRS Pilot Website, including the state's Agency, Key Indicators, and Case Components, announcements and resources that were displayed on the website, login data, and other data was all stored in the NAMRS Pilot Database. This database used Microsoft SQL Server running on an Azure VM. (We looked at using SQL Azure, which (like Web Apps) was a managed SQL Service, but the space required and the performance levels would have been prohibitive for our purposes.)
-
Email--The NAMRS Pilot Website sent emails to state users and technical users as the status of state data was updated. For example, when a state user submitted their Agency Component data, an email was sent to a technical user to inform them that they needed to inspect this data. When the technical user approves/rejects the data, an email was sent to the state users to let them know the updated status.
Obviously, sending email requires an SMTP server, however Azure does not have an SMTP service available. However, Microsoft has contracted with a company called SendGrid to supply SMTP service through the Azure Marketplace. SendGrid offers an SMTP service (the service is also called SendGrid) to Azure customers for free. The service can send up to 25,000 emails a month for free, and additional credits can be purchased very inexpensively.
Security
There were multiple security measures in the website, as described below.
Login
As stated above, the entire site (other than the login page and XSD file) was protected by the requirement to login.
Encryption
All communications between the user's browser and the website were encrypted with SSL. (The lock was displayed in the user's browser.) The site was configured to immediately switch to HTTPS if the user attempted to access through HTTP, so the connection was always encrypted.
The only exception to this was the XSD file. Users could access the XSD file using HTTP without being switched to HTTPS--this was the only file on the site that could be accessed through HTTP. The reason for this is that many (most) XML Editors/Validators cannot handle HTTPS.
Hashed Passwords
The site did not store user passwords in the database. Instead, only password hashes were stored. The password hash algorithm used in Microsoft Identity 2 was SHA1--more specifically, it ran 1000 iterations of a salted SHA1 (where the salt becomes part of the actual hash). Therefore, even if someone gained access to the database, it would require tremendous work to figure out the passwords (if it was possible at all). When a user entered their password, the password was sent to the website, where it was hashed and compared to the hashed value stored in the database. Since the website used HTTPS for all communications, the actual password was encrypted while it is being transported.
Roles
Every user was assigned a role when their account was created by an administrator. There were four roles:
-
State User--State users were also assigned to a state. State users could access data only for their state. There was no way for a state user to access data for a different state--there were checks in the site to block this and log it if a user attempted to do so.
-
Federal User--Federal users could access data for all states. They could view data but could not change any data on the site (except their own password).
-
Technical User--Technical users could access data for all states. They could view and change data for any state. They could also change the workflow status (i.e., accept/reject data components for each state). They could also manage (i.e., create/update/disable) resources and announcements.
-
Administrators--Administrators could do everything that technical users could do. They were also able to manage (i.e., create/update/disable) users.
Firewall
The Web Apps service was firewalled from the Internet, and all physical and network security was handled by Azure.
Data Precautions
There was no personally identifiable information in the NAMRS Pilot, so there was no way to link any particular piece of data to an individual person. States always encrypted all their database IDs (using their preferred encryption methodology) that could potentially be tracked to an individual.
Configuration
Like all ASP.Net Apps, configuration was done through the web.config file, located in the root of the App. All the parameters were documented in the web.config file.
4. Namrs Pilot Database
The NAMRS Pilot Database stored all the data for the NAMRS Pilot.
Function
There were several functional types of data that were stored in the database:
-
NAMRS Component Data--This included the Agency, Case, and Key Indicators Component datasets that were entered/uploaded by the states.
-
Announcements/Resources--These were the announcements and resource files that were displayed in the NAMRS Pilot Website. These items were loaded into the database through the website by administrators and technical users.
-
Case Component XML Validation Rules--These were the data validation rules that were used to validate the Case Component XML files that were uploaded by state users.
-
Error Logs--These were "ELMAH logs" that tracked errors, exceptions, and security violation attempts in the website.
-
User Profiles--This was contact and other information about NAMRS Pilot users.
-
Credentials and Roles--This data included the credentials and roles for all users. This was standard Microsoft Identity 2 tables.
Schemas
The six functional types above comprised the 95 different tables that made up the database. Those tables were broken up into four schemas:
-
DBO--The DBO schema contained the identity and ELMAH tables.
-
Core--The core schema contained all the tables for data that was not submitted by states, such as the announcements, resources, user profiles, and validation rules.
-
Lookup--The lookup schema contained all the lookup tables.
-
NAMRS--The NAMRS schema contained all the tables for the Agency, Case, and Key Indicators Component datasets.
Appendix A provides ERD for the physical database structure. The ERD is provided as a PDF and can be modified using Adobe Acrobat software. Appendix B lists the definition of all the database tables and data elements.
Views
There were nine different views in the database that were used only for data extract functionality (which was a manual process run by a system administrator). These views were not accessed by the website.
Stored Procedures
There were only five stored procedures in the database. Three of them were used by the ELMAH component in the site. One was used to build the data extracts and one was used to load the data warehouse. The last two were both manual processes run by a system administrator.
Custom Functions
There were only two user-defined (scalar-valued) functions in the database. Both were used in the data extract views.
The database ran on Microsoft SQL Server 2014 (Web Edition), installed on a VM in the Microsoft Azure Cloud. Since this was a VM, normal maintenance had to be performed. In addition to manual service pack updates, there were two scheduled maintenance plans:
-
Nightly--The nightly maintenance plan ran at 1 a.m. every night and backed up all databases on the server (to Azure Storage), checked database integrity, reorganized indexes, and updated statistics.
-
Weekly--The weekly maintenance plan ran at 3 a.m. on Saturdays and rebuilt all indexes on all databases on the server.
Access
The NAMRS Pilot Database was accessed by the following:
-
NAMRS Pilot Website--The website accessed the database so that it could store and retrieve data for the website.
-
NAMRS Pilot Case Loader--The case loader application accessed the database so that it could load data from Case Component XML files.
The NAMRS Pilot Database accessed the following:
-
NAMRS Pilot Storage--The nightly backup process created a full database backup each night. The backup file was stored in NAMRS Pilot Storage.
-
NAMRS Pilot Data Warehouse--Data from the database was stored into the data warehouse. This was a manual process performed by a system administrator.
Security
There were two levels of security for the NAMRS Pilot Database.
Network Security
An Azure VM basically ran behind a firewall. One must expose "endpoints" which are essentially TCP ports. Each endpoint could be protected with an Access Control List (ACL) which allows one to limit access to the endpoint to one or more IP addresses (or address ranges that are specified as classless inter-domain routing [CIDR] addresses).
The VM that ran this SQL Server only has the following endpoints exposed:
-
Powershell--This allowed powershell scripting against the VM.
-
Remote Desktop--This allowed a system administrator to get remote access to the server. Two-factor authentication was enabled for all remote desktop access.
-
SQL Server--This allowed the SQL to be queried and managed.
Each of the endpoints was open only to a single IP address--the WRMA office. These servers could not be accessed from any other IP address on the Internet other than the WRMA office. Also, the SQL Server endpoint was open to IP addresses for Azure Web Apps. This was required so that the NAMRS Pilot Website (which is an Azure Web App) could access the database. Since a Web App can change IP addresses without notice (as it moves around in the cloud), the database had to be open to the full range of IP addresses that could be used by Web Apps.
Database Security
Each person/component that accessed the database had database credentials. The only accounts available in SQL Server were for the system administrator and for the website to access the database.
5. Namrs Pilot Case Loader
The NAMRS Pilot Case Loader was an application that does processing on the Case Component XML files that were uploaded by state users. These files had to be validated against the NAMRS Case Component XSD file. State users should have validated the file against the XSD before they uploaded it to the NAMRS Pilot Website (although the file was validated as part of processing). If the file was very large the state user could zip the file and upload the zipped file.
Function
The Case Component Loader performed the functions on all files waiting to be processed:
-
Downloaded the file from NAMRS Pilot Storage to the local temp space.
-
Unzipped the file if the file was zipped.
-
Validated the file against the XSD. If it was not valid, saved an error message for the user and stopped.
-
Ran the data validation rules against the file (and performed actions on rule violations) and built a list of warning messages (which could be quite long) for the user. If the file was not valid (i.e., it had no investigations left at the end of validation), then added an error message for the user and stopped. After the file had been validated and any invalid date had been removed (the actions mentioned above), a new version of the file without any invalid tags/data was saved.
-
Loaded the data from the valid case XML file produced by the previous step into the database. This was the longest part of the process.
-
Calculated the basic counts that were displayed on the website.
-
Built the PDF files for the summary counts and frequency counts.
-
Saved the computed Key Indicators to the database.
The Case XML Loader was a "console application" that could be run from the command line by executing the following command: Namrs.WebJob.CaseXmlLoader.exe -- it did not take any parameters.
The Case XML Loader was built as a Microsoft Console Application. The site was written in C# and used Entity Framework and LINQ for all database access. The site used several open-source components and frameworks, including DotNetZip, iTextSharp, log4net, RazorEngine.
This application ran on an Azure VM. (It ran on the same VM that hosted the NAMRS Pilot Database.) It was executed as a Scheduled Task on the VM. It ran once a minute every day. So on average, it started processing an XML file within 30 seconds of the state user uploading the file. The scheduler was set so that it only ran one instance of the loader at a time.
The validation rules applied at the time of data entry of Agency Component data, validation rules applied at the time of data entry of Key Indicator data, and the validation rules applied on the Case Component XML file along with the action taken when invalid data were found--were all included as Appendix F in Volume 1.
Access
Since the Case XML Loader was a console application, it was not accessed by any other process (although one could say that it was accessed by the Task Scheduler, which runs it every minute).
The Case XML Loader accessed the following:
-
NAMRS Pilot Database--The loader queried the database to find which Case Component XML files were awaiting processing, to save the Case Component data from the XML files into the database, to save other statistics and computed counts into the database, etc.
-
NAMRS Pilot Storage--The loader downloaded the Case XML file from NAMRS Pilot Storage into a local disk so that it could be easily accessed (and unzipped if necessary).
-
NAMRS Pilot Email--The loader would send an email to the state user when it was finished processing the file.
Security
The NAMRS Pilot Case Component XML Loader was a command line application and did not accept any incoming connections or parameters. It ran on the same VM that hosted the NAMRS Pilot Database, so all the same security measures for that VM were in place for this process.
Configuration
The NAMRS Pilot Case XML Loader was configured using the NAMRS.WebJob.CaseXmlLoader.exe.config file, located in the root of the App. All parameters were documented in the config file. The configuration parameters were similar to the parameters for the website, although there were not as many.
6. Namrs Pilot Data Warehouse
The NAMRS Pilot Data Warehouse provided a way for analysts to query the Case Component data collected from the states using statistical analysis tools, data visualization tools, or any other type of tool that can query a SQL database. The data warehouse contained only the Case Component data, and was connected to the Internet.
Function
There were several functional types of data that were stored in the data warehouse:
-
CaseDataSet--stored the various states/periods for the case data.
-
Investigation--stored the investigations.
-
Clients--stored the client data.
-
Maltreatments--stored the data about the maltreatments.
-
Perpetrators--stored the data about the perpetrators.
-
Relationships--stored the data about the client-perpetrator relationships.
Schemas
There was a single schema in the data warehouse:
-
Data Warehouse--The data warehouse schema contained all the objects that could be queried in the data warehouse.
Views
There were no views that could be accessed in the data warehouse. (There was a single view that could not be accessed through the data warehouse schema--it could be used for testing, but would probably be deleted.)
Stored Procedures
There were no stored procedures that could be accessed in the data warehouse.
Custom Functions
There were no user-defined functions that could be accessed in the data warehouse.
The database ran on Microsoft SQL Server 2014 (Web Edition), which was installed on a VM in the Microsoft Azure Cloud. Since this was a VM, normal maintenance had to be performed. In addition to manual service pack updates, there were two scheduled maintenance plans:
-
Nightly--The nightly maintenance plan ran at 1 a.m. every night and backed up all databases on the server (to Azure Storage), checked database integrity, reorganized indexes, and updated statistics.
-
Weekly--The weekly maintenance plan ran at 3 a.m. on Saturdays and rebuildt all indexes on all databases on the server.
Appendix C provides the ERD for the physical database structure. The ERD is provided as a PDF and can be modified using Adobe Acrobat software. Appendix D lists the definition of all the data warehouse tables and data elements.
Access
The NAMRS Pilot Data Warehouse was accessed by the following:
-
NAMRS Pilot Database--The database loaded data into the data warehouse.
-
Analysts--Analysts connected directly to this database to do queries.
The NAMRS Pilot Data Warehouse did not access any other components.
Security
There were two levels of security for the NAMRS Pilot Database.
Network Security
An Azure VM basically ran behind a firewall. One must expose "endpoints" which are essentially TCP ports. Each endpoint could be protected with an ACL which lets one limit access to the endpoint to one or more IP addresses (or address ranges that are specified as CIDR addresses).
The VM that ran this SQL Server only had the following endpoints exposed:
-
Powershell--This allowed powershell scripting against the VM.
-
Remote Desktop--This allowed a system administrator to get remote access to the server.
-
SQL Server--This allowed the SQL to be queried and managed.
The Powershell and Remote Desktop endpoints were open only to a single IP address--the WRMA office. These endpoints could not be accessed from any other IP address on the Internet other than the WRMA office. The SQL Server endpoint was wide open to the Internet. This was required so that the analysts could log in to query the database.
Database Security
Each person/component that accessed the database had database credentials. The only accounts available in SQL Server were for the system administrator and for the NAMRS Pilot database (so that it could load data). A role called data warehouse users was created in SQL server and in the database. This role had been denied access to most objects in the master database, denied access to all databases on the server (except the data warehouse) and it had been granted read-only access to the data warehouse schema.
New analysts could be given access to the data warehouse as needed. Analyst accounts were assigned to the data warehouse user role. This effectively allowed analysts to query the data in the data warehouse while denying access to any other database on the server.
7. Namrs Pilot Storage
The NAMRS Pilot utilized shared storage space where multiple components could access a file. In the Microsoft Azure Cloud, the mechanism for doing this is called Azure Storage.
Function
There were three types of storage mechanisms provided in Azure:
-
Azure Blob Storage--provided a way to store files.
-
Azure Table Storage--provided a way to store (unstructured) table rows.
-
Azure Queues--provided a common message queue--a way to share messages.
Azure Storage is a very economical, infinitely scalable storage service. You only pay for what you use, and there is (practically) no limit to the amount of data that can be stored.
The NAMRS Pilot only used Azure Blob Storage for storing and sharing files. Azure Blob Storage uses the following hierarchy:
-
Storage Account--There must be at least one Storage Accounts.
-
Container--Each storage account must have at least one Container. A container is analogous to a folder (or directory) on a disk.
-
Files--A Container can have zero or more files in it.
The NAMRS Pilot used a single storage account that had the following containers in it:
-
CaseFiles--This container had all the case files. This included the actual XML/zip files that were uploaded by the state users through the website. Once the XML file was processed by the NAMRS Case XML Loader, the following files were added for each case XML file: (1) a text file that had all the error/warning messages produced for the XML file; (2) a second version of the XML file without any invalid data; (3) a PDF file containing the frequency counts which was available on the website; and (4) a PDF file containing the summary counts which was available on the website.
-
ResourceFiles--This container had all the resource files that were uploaded by technical users. These files were available to users on the website.
-
DbBackup--This container had all the database backups created by SQL Server maintenance plans.
-
VHDs--This container had all the "virtual hard disk" files that were used by the Azure VMs.
Azure Storage is a service provided by the Azure Cloud. It is a "geographically redundant," meaning that files are not only backed up, but backed up across different regions. This means that if an entire data center was to ever go offline (or possibly be destroyed), the files will remain intact. (For the NAMRS Pilot, the primary region was in the Eastern United States region, and the secondary region was in Western United States.)
Access
The following components accessed NAMRS Pilot Storage:
-
NAMRS Pilot Website--The website stored Case Component XML files that were uploaded by state users, as well as resource files uploaded by technical/administrative users. It also read PDF and text files that were created by the Case Component XML Loader, as well as resource files.
-
NAMRS Pilot Case Component Loader--The loader retrieved the Case XML files, and stored PDF and text files.
-
NAMRS Pilot Database (not shown on diagram)--The database saved its backup files.
-
NAMRS Pilot Data Warehouse (not shown on diagram)--The data warehouse saved its backup files.
The NAMRS Pilot Storage is a service provided by the Azure Cloud. It did not access any components in the NAMRS Pilot.
Security
Security for NAMRS Pilot Storage was handled by the Microsoft Azure Cloud.
Azure Blob Storage is an application programming interface (API) and it is available from the Internet. It is available by HTTPS. There are many positive aspects to this because Azure Blob Storage can be used for many different purposes. The files were not available on the Internet in the NAMRS Pilot.
Access to Azure Blob Storage API is through an URL and a secret access key. To use the API, one needs the access key. Access keys for Azure Storage are almost 90 characters long, so it was highly unlikely that anyone could guess this key.
All access to the Azure Blob Storage API uses SSL encryption over HTTPS. So, data was always encrypted as it moved between the web server (and the Case XML Loader) and Azure Storage.
8. Namrs Pilot Email
The NAMRS Pilot sent email alerts on a regular basis to support the various workflow and system processes.
Function
There were several types of emails that were sent:
-
Emails to Technical Users--These emails informed technical users that they needed to take action. (For example, that a technical user needed to review a data component that had been entered/uploaded by a state user.)
-
Emails to State Users--These emails informed state users that the system was awaiting input from them. (For example, that a technical user had accepted a data component for their state.)
-
Emails to System Administrators--These emails informed a system administrator of some action. (For example, which nightly backup has completed successfully.)
Email required an SMTP service. The Azure Cloud does not have an SMTP service. However, as described earlier, there is an SMTP service called SendGrid that is available in the Azure Cloud. The NAMRS Pilot used SendGrid as the NAMRS Pilot email component.
Access
The following components access NAMRS Pilot email:
-
NAMRS Pilot Website--The website sent email to both state users and technical users.
-
NAMRS Pilot Case Loader--The loader sent email to state users.
-
NAMRS Pilot Database (not shown on diagram)--The database sent email every time it completed a maintenance cycle.
-
NAMRS Pilot Data Warehouse (not shown on diagram)--The data warehouse sent email every time it completed a maintenance cycle.
The NAMRS Pilot Storage was a service--it did not access any components in the NAMRS Pilot.
Security
Like any SMTP server, the SMTP Server from SendGrid uses a host, port, username, and password for security. The username is quite long (almost 50 random characters) and the password is strong. SendGrid is used port 587, so TLS encryption is in place inside the Azure Cloud. This means that data was encrypted as it moved between the web server (and Case Component XML Loader) and the SendGrid server.
If the user's server supported TLS, then SendGrid would use encryption when sending the email to the receiving SMTP server. (And if the user had an email client that supported encryption, the email would encrypt as it was sent.)
9. Namrs Pilot Session Cache
Like most websites, the NAMRS Pilot used session data to store data between requests for a particular user during a login session. NAMRS used very little session data--only a couple of items such as the state ID and state name.
Function
There were various ways to store session data. NAMRS used Azure Redis Cache to store its session data. This is a best practice when using SQL Azure. The NAMRS Pilot had been migrated from SQL Azure onto a VM running SQL Server and still used Redis for maximum portability.
Redis is an industry standard, open-source caching service. The website used a standard Redis Session Provider that plugged into the ASP.Net Provider Framework. This meant that there were no code changes if the session provider was changed. It would be very simple to switch from Redis session state to SQL session state, to even in-memory session state. (And this had all been tested--it is purely configuration without any code changes.)
Access
The NAMRS Pilot Session Cache was accessed by the NAMRS Pilot Website.
The NAMRS Pilot Session Cache was a service--it did not access any components in the NAMRS Pilot.
Security
Redis security was controlled through a host, port, and secret access key. The access key was almost 50 random characters long and it was unlikely that someone would guess the key.
Data were always encrypted as it moved across the network inside the Azure Cloud between the web server and the Azure Redis service.
10. Workflows
The following workflow took place when a state submitted any of the data Components. The approval process required a technical user review the data for the Agency and Key Indicators Components and the validation results and reports for the Case Component.
In this flow, the status for the data component started at not submitted, then became one of the following based on the state and technical user's action:
-
In Process--Once the state had submitted the data, the technical user reviewed the data to confirm if the data quality requirements were met by the data submission. During this review period the data status remained marked as in process.
-
Resubmit--In this case, the technical user had reviewed the data (Agency and Key Indicators Components) or results/report (Case Component) and decided that the data were not valid. The technical user requested the state to resubmit and needed to contact the state and discuss the issues with them.
-
Approved--In this case, the technical user found no errors during the review of the data component and approved the data.
Automatic email alerts were sent to appropriate state and technical users to communicate the data status changes. Figure 10.1 provides a schematic representation of the workflow and interaction between the NAMRS, state user, and the technical team user.
Agency Component Data Entry and Submission
State users filled a data entry form that contained fields for all the data points in the Agency Component. The user could fill out the form, save their data, and return later to work on it some more. Once they were satisfied with all the data, they could submit it. This page had basic type validation (i.e., if a field required numbers and the user entered letters in that field) and required field validation at the time of saving the data. If the user submitted their data, they may no longer edit their data unless data resubmission was requested.
A federal user could view data for the Agency Component for any state. A federal user would see the same page as the state user. However, the federal user could view the data for any state by toggling the list of states on the top of the page. All fields would be disabled, and there would be no way to save or submit data.
A technical user or administrator could submit or approve data for the Agency Component for any state. A technical or administrative user would see the same page as the state user. However, the user could view the data for any state by toggling the list of states on the top of the page. The user could edit/save data for any state. The user would see radio buttons for each status, with the current status selected. The user could change the data status and save. The appropriate emails were sent, depending on the new status. There would be no submit button for this user but data could be saved by changing the status to in process.
| FIGURE 10.1. Workflow to Accept Data for Each Component |
|---|
 |
Key Indicators Component Data Entry and Submission
State users filled a data entry form that contained fields for all the data points in the Key Indicators Component data. The user could fill out the form, save their data, and return later to work on it more. Once they were satisfied with all the data, they could submit it. This page had basic type validation (i.e., if a field required numbers and the user entered letters in that field) and required field validation at the time of saving the data. If the user submitted their data they could no longer edit their data unless data resubmission was requested.
A federal user could view data for the Key Indicators Component for any state. A federal user would see the same page as the state user. However, the federal user could view the data for any state by toggling the list of states on the top of the page. All fields would be disabled, and there would be no way to save or submit data.
A technical user or administrator could submit or approve data for the Key Indicators Component for any state. A technical or administrator would see the same page as the state user. However, these user could view the data for any state by toggling the list of states on the top of the page. The user could edit/save data for any state. The user would see radio buttons for each status, with the current status selected. The user could change the data status and save. The appropriate emails were sent, depending on the new status. There would be no submit button for this user but data could be saved by changing the status to in process.
Case Component Data Entry and Submission
A state user generated the XML file. The data for the Case Component would be submitted in XML format. A state user would first export the data from the state information system into the required XML format. Following that, the state user validated for XML structure and data element characteristics requirements using the XSD file available for download on the NAMRS Pilot website. The state user could perform the validation using their favorite XML validator software. XmlValidator (Sourceforge) is an open-source XML validation software. XML Spy is a commercially available XML validator and editor. The XML file without any errors would be ready to be uploaded to NAMRS.
A state user could upload and submit data for the Case Component. A state user could upload an XML file containing the Case Component data on the file upload tab. When the file was uploaded, it was validated against an XSD by the NAMRS Pilot system to determine validity. If the XML was invalid (structure and field definition validation), validation error messages describing the issues were displayed and the entire XML file is considered invalid. The user could download the list of error messages. The data status for the component remained as in process because NAMRS could not read the data in the file. The user has to upload a valid file to proceed further.
If the XML was valid, it proceeded to the next step which was the content validation. This was an asynchronous process where a number of validation rules across fields and records were applied and could take some time. The data status for the component changed to in validation while the file was being validated and a new file could not be uploaded during this time. When data validation was complete, the data status for the component changed to data valid when there were no errors and to data invalid when errors were identified.
If there were no errors the user could view the validation results on the data validation results tab. The results may have contained warnings showing where the data were missing. Basic counts like the number of investigations accepted would be displayed in the data report tab. The user could submit the data by clicking on the submit button or could upload another XML file and go through the entire upload process again.
If there were errors, the user could view the validation results on the data validation results tab. The results listed the data errors where the XML violated the validation rules. The data corresponding to the errors would not be saved in the database. The results could also contain warnings showing where the data were missing. A new XML file without any errors needed to be uploaded to proceed further. Figure 10.2 describes this process visually.
A federal user could view status and reports for the Case Component for any state. A federal user would see the same page as the state user but only the data validation results and the data report tabs would be visible. The file upload tab would not be visible. All fields would be disabled, and there would be no way to save or submit data.
A technical user or administrator could submit, view, and approve data for the Case Component for any state. A technical or administrative user would see the same page as the state user. There would also be a dropdown with all states in it so that the user could select which state they wanted to view. The user could upload an XML file for any state. The user would see radio buttons for each data status, with the current status selected. The user could change the status and save. There would be no submit button for this user--data could be submitted by changing the status to in process. Figure 10.2 shows the data submission and approval process as a flow chart.
| FIGURE 10.2. Case Component Approval Workflow |
|---|
 |
Appendix A. Namrs Pilot Database Entity Relationship Diagram
This diagram is oversized and should be viewed in the PDF version of this report.
Appendix B. Namrs Pilot Database Tables and Columns
This section lists the Tables, Columns, and Column Attributes for the NAMRS Pilot Database.
B.1. [Core]
| [Core].[Announcement]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AnnouncementId | Int | 4 | N | Y | N | Primary key |
| Title | NVarChar (200) | 200 | N | N | N | |
| Body | NVarCharMax | -1 | Y | N | N | |
| AnnouncementDate | Date | 3 | N | N | N | |
| IsActive | Bit | 1 | N | N | N | |
| CreationTime | DateTime | 8 | N | N | N | |
| CreationUserId | NVarChar (128) | 128 | Y | N | N | |
| [Core].[Resource]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ResourceId | Int | 4 | N | Y | N | Primary key |
| ResourceTypeId | Int | 4 | N | N | Y | Link to ResourceType table |
| Title | NVarChar (200) | 200 | N | N | N | |
| Body | NVarCharMax | -1 | Y | N | N | |
| IsFile | Bit | 1 | N | N | N | |
| DisplayFilename | NVarChar (100) | 100 | Y | N | N | |
| Filename | NVarChar (100) | 100 | Y | N | N | |
| FileLength | Int | 4 | Y | N | N | |
| FileType | NVarChar (100) | 100 | Y | N | N | |
| Ord | Int | 4 | N | N | N | |
| IsActive | Bit | 1 | N | N | N | |
| CreationTime | DateTime | 8 | N | N | N | |
| CreationUserId | NVarChar (128) | 128 | Y | N | N | |
| [Core].[ResourceType]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ResourceTypeId | Int | 4 | N | Y | N | Primary key |
| Title | NVarChar (200) | 200 | N | N | N | |
| Body | NVarChar (1000) | 1000 | N | N | N | |
| Ord | Int | 4 | N | N | N | |
| IsActive | Bit | 1 | N | N | N | |
| IsVisibleToState | Bit | 1 | N | N | N | |
| IsDataExtract | Bit | 1 | N | N | N | |
| CreationTime | DateTime | 8 | N | N | N | |
| CreationUserId | NVarChar (128) | 128 | Y | N | N | |
| [Core].[UserProfile]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| UserId | NVarChar (128) | 128 | N | Y | Y | Link to AspNetUsers table |
| FirstName | NVarChar (50) | 50 | Y | N | N | |
| LastName | NVarChar (50) | 50 | Y | N | N | |
| Phone | NVarChar (50) | 50 | Y | N | N | |
| RegionId | Int | 4 | Y | N | Y | Link to Region table |
| LastLoginTime | DateTime | 8 | Y | N | N | |
| CreationTime | DateTime | 8 | N | N | N | |
| CreationUserId | NVarChar (128) | 128 | Y | N | N | |
| [Core].[ValidationRule]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ValidationRuleId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | Y | N | N | |
| Description | NVarChar (4000) | 4000 | Y | N | N | |
| Definition | NVarCharMax | -1 | Y | N | N | |
| ElementPathMessage | NVarChar (200) | 200 | Y | N | N | |
| ErrorMessage | NVarChar (1000) | 1000 | Y | N | N | |
| ValidationRuleStepLuId | Int | 4 | Y | N | Y | Link to ValidationRuleStepLu table |
| IsCriticalRule | Bit | 1 | N | N | N | |
| IsActive | Bit | 1 | N | N | N | |
| CreationTime | DateTime | 8 | N | N | N | |
| CreationUserId | NVarChar (128) | 128 | Y | N | N | |
| [Core].[ValidationRuleStepLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ValidationRuleStepLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (50) | 50 | Y | N | N | |
B.2. [dbo]
| [dbo].[__MigrationHistory]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| MigrationId | NVarChar (150) | 150 | N | Y | N | Primary key |
| ContextKey | NVarChar (300) | 300 | N | Y | N | Primary key |
| Model | VarBinaryMax | -1 | N | N | N | |
| ProductVersion | NVarChar (32) | 32 | N | N | N | |
| [dbo].[AspNetRoles]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| Id | NVarChar (128) | 128 | N | Y | N | Primary key |
| Name | NVarChar (256) | 256 | N | N | N | |
| [dbo].[AspNetUserClaims]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| Id | Int | 4 | N | Y | N | Primary key |
| UserId | NVarChar (128) | 128 | N | N | Y | Link to AspNetUsers table |
| ClaimType | NVarCharMax | -1 | Y | N | N | |
| ClaimValue | NVarCharMax | -1 | Y | N | N | |
| [dbo].[AspNetUserLogins]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| LoginProvider | NVarChar (128) | 128 | N | Y | N | Primary key |
| ProviderKey | NVarChar (128) | 128 | N | Y | N | Primary key |
| UserId | NVarChar (128) | 128 | N | Y | Y | Link to AspNetUsers table |
| [dbo].[AspNetUserRoles]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| UserId | NVarChar (128) | 128 | N | Y | Y | Link to AspNetUsers table |
| RoleId | NVarChar (128) | 128 | N | Y | Y | Link to AspNetRoles table |
| [dbo].[AspNetUsers]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| Id | NVarChar (128) | 128 | N | Y | N | Primary key |
| NVarChar (256) | 256 | Y | N | N | ||
| EmailConfirmed | Bit | 1 | N | N | N | |
| PasswordHash | NVarCharMax | -1 | Y | N | N | |
| SecurityStamp | NVarCharMax | -1 | Y | N | N | |
| PhoneNumber | NVarCharMax | -1 | Y | N | N | |
| PhoneNumberConfirmed | Bit | 1 | N | N | N | |
| TwoFactorEnabled | Bit | 1 | N | N | N | |
| LockoutEndDateUtc | DateTime | 8 | Y | N | N | |
| LockoutEnabled | Bit | 1 | N | N | N | |
| AccessFailedCount | Int | 4 | N | N | N | |
| UserName | NVarChar (256) | 256 | N | N | N | |
| [dbo].[ELMAH_Error]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ErrorId | UniqueIdentifier | 16 | N | Y | N | Primary key |
| Application | NVarChar (60) | 60 | N | N | N | |
| Host | NVarChar (50) | 50 | N | N | N | |
| Type | NVarChar (100) | 100 | N | N | N | |
| Source | NVarChar (60) | 60 | N | N | N | |
| Message | NVarChar (500) | 500 | N | N | N | |
| User | NVarChar (50) | 50 | N | N | N | |
| StatusCode | Int | 4 | N | N | N | |
| TimeUtc | DateTime | 8 | N | N | N | |
| Sequence | Int | 4 | N | N | N | |
| AllXml | NText | 16 | N | N | N | |
B.3. [Lookup]
| [Lookup].[AcceptingReportLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AcceptingReportLuId | Int | 4 | N | Y | N | Primary key |
| Name | VarChar (1000) | 1000 | N | N | N | |
| [Lookup].[AgeLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AgeLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[AgencyAddressTypeLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AgencyAddressTypeLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| [Lookup].[AgencyContactRoleLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AgencyContactRoleLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| [Lookup].[AssessmentToolLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AssessmentToolLuId | Int | 4 | N | Y | N | Primary key |
| Name | VarChar (1000) | 1000 | N | N | N | |
| [Lookup].[AssociationLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AssociationLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[AttributeStatusLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AttributeStatusLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| [Lookup].[BehaviorHealthLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| BehaviorHealthLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[BenefitLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| BenefitLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[CaseServiceLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| CaseServiceLuId | Int | 4 | N | Y | N | Primary key |
| Name | VarChar (1000) | 1000 | N | N | N | |
| [Lookup].[CloseReasonLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| CloseReasonLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[CohabLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| CohabLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[DataSourceLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| DataSourceLuId | Int | 4 | N | Y | N | Primary key |
| Name | VarChar (1000) | 1000 | N | N | N | |
| [Lookup].[DecisionLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| DecisionLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[DisabilityLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| DisabilityLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[EmployLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| EmployLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[EngCompLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| EngCompLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[EthnicityLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| EthnicityLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[EvidenceStandardLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| EvidenceStandardLuId | Int | 4 | N | Y | N | Primary key |
| Name | VarChar (1000) | 1000 | N | N | N | |
| [Lookup].[GenderIdentityLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| GenderIdentityLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[IncomeLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| IncomeLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[InteragencyLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| InteragencyLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[KinshipLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| KinshipLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[LegalRemedyLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| LegalRemedyLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[LivingSetLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| LivingSetLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[MaltreatmentTypeLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| MaltreatmentTypeLuId | Int | 4 | N | Y | N | Primary key |
| Name | VarChar (1000) | 1000 | N | N | N | |
| [Lookup].[MaltxSettingLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| MaltxSettingLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[MaritalLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| MaritalLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[MtxDispositionLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| MtxDispositionLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[PreviousReportLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PreviousReportLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[PrimLangLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PrimLangLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[RaceLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| RaceLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[ReportSourceLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ReportSourceLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[SchoolingLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| SchoolingLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[ServiceLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ServiceLuId | Int | 4 | N | Y | N | Primary key |
| Name | VarChar (1000) | 1000 | N | N | N | |
| [Lookup].[SexOrientLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| SexOrientLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[UsStateLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| UsStateLuId | Char (2) | 2 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| [Lookup].[VeteranLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| VeteranLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
| Description | NVarChar (1000) | 1000 | Y | N | N | |
| [Lookup].[WorkflowStatusLu]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| WorkflowStatusLuId | Int | 4 | N | Y | N | Primary key |
| Name | NVarChar (100) | 100 | N | N | N | |
B.4. [Namrs]
| [Namrs].[Agency]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AgencyId | Int | 4 | N | Y | N | Primary key |
| FiscalYear | Int | 4 | N | N | Y | Link to ReportingPeriod table |
| RegionId | Int | 4 | N | N | Y | Link to Region table |
| WorkflowStatusLuId | Int | 4 | N | N | Y | Link to WorkflowStatusLu table |
| AgencyName1 | NVarChar (100) | 100 | N | N | N | |
| AgencyName2 | NVarChar (100) | 100 | Y | N | N | |
| DataSourceLuId | Int | 4 | Y | N | Y | Link to DataSourceLu table |
| DataSourceComment | NVarCharMax | -1 | Y | N | N | |
| FteBudgetedInvestigator | Int | 4 | Y | N | N | |
| FteBudgetedSupervisor | Int | 4 | Y | N | N | |
| FteFilledInvestigator | Int | 4 | Y | N | N | |
| FteFilledSupervisor | Int | 4 | Y | N | N | |
| FteComment | NVarCharMax | -1 | Y | N | N | |
| AcceptingReportLuId | Int | 4 | Y | N | Y | Link to AcceptingReportLu table |
| AcceptingReportComment | NVarCharMax | -1 | Y | N | N | |
| ReportCountNotAccepted | Int | 4 | Y | N | N | |
| ReportCountResolved | Int | 4 | Y | N | N | |
| ReportCountAccepted | Int | 4 | Y | N | N | |
| ReportCountComment | NVarCharMax | -1 | Y | N | N | |
| EvidenceStandardLuId | Int | 4 | Y | N | Y | Link to EvidenceStandardLu table |
| EvidenceStandardComment | NVarCharMax | -1 | Y | N | N | |
| CompletionTime | Int | 4 | Y | N | N | |
| CompletionComment | NVarCharMax | -1 | Y | N | N | |
| AssessmentToolLuId | Int | 4 | Y | N | Y | Link to AssessmentToolLu table |
| AssessmentComment | NVarCharMax | -1 | Y | N | N | |
| ServiceGapsComment | NVarCharMax | -1 | Y | N | N | |
| MaltreatmentTypesComment | NVarCharMax | -1 | Y | N | N | |
| CreationTime | DateTime | 8 | N | N | N | |
| CreationUserId | NVarChar (128) | 128 | Y | N | N | |
| [Namrs].[AgencyAddress]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AgencyAddressId | Int | 4 | N | Y | N | Primary key |
| AgencyId | Int | 4 | N | N | Y | Link to Agency table |
| AgencyAddressTypeLuId | Int | 4 | N | N | Y | Link to AgencyAddressTypeLu table |
| Street1 | NVarChar (100) | 100 | Y | N | N | |
| Street2 | NVarChar (100) | 100 | Y | N | N | |
| City | NVarChar (100) | 100 | Y | N | N | |
| USStateLuId | Char (2) | 2 | Y | N | Y | Link to UsStateLu table |
| Zip | NVarChar (10) | 10 | Y | N | N | |
| [Namrs].[AgencyContact]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AgencyContactId | Int | 4 | N | Y | N | Primary key |
| AgencyId | Int | 4 | N | N | Y | Link to Agency table |
| Name | NVarChar (100) | 100 | Y | N | N | |
| Title | NVarChar (100) | 100 | Y | N | N | |
| NVarChar (100) | 100 | Y | N | N | ||
| Phone | NVarChar (50) | 50 | Y | N | N | |
| AgencyContactRoleLuId | Int | 4 | N | N | Y | Link to AgencyContactRoleLu table |
| [Namrs].[AgencyMaltreatmentType]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AgencyId | Int | 4 | N | Y | Y | Link to Agency table |
| MaltreatmentTypeLuId | Int | 4 | N | Y | Y | Link to MaltreatmentTypeLu table |
| [Namrs].[AgencyServiceGap]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| AgencyId | Int | 4 | N | Y | Y | Link to Agency table |
| ServiceLuId | Int | 4 | N | Y | Y | Link to ServiceLu table |
| [Namrs].[CaseBasicCount]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| CaseBasicCountId | Int | 4 | N | Y | N | Primary key |
| CaseDataSetId | Int | 4 | Y | N | Y | Link to CaseDataSet table |
| UniqueInvestigationCnt | Int | 4 | Y | N | N | |
| UniqueClientCnt | Int | 4 | Y | N | N | |
| UniqueFacilityCnt | Int | 4 | Y | N | N | |
| UniquePerpCnt | Int | 4 | Y | N | N | |
| AllClientCnt | Int | 4 | Y | N | N | |
| AllPerpCnt | Int | 4 | Y | N | N | |
| AllMtxCnt | Int | 4 | Y | N | N | |
| AllRelCnt | Int | 4 | Y | N | N | |
| [Namrs].[CaseBasicCountBool]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| CaseBasicCountBoolId | Int | 4 | N | Y | N | Primary key |
| CaseBasicCountId | Int | 4 | N | N | Y | Link to CaseBasicCount table |
| BoolTypeNum | Int | 4 | N | N | N | |
| FieldNum | Int | 4 | N | N | N | |
| LookupId | Int | 4 | N | N | N | |
| IsUsed | Bit | 1 | N | N | N | |
| [Namrs].[CaseBasicCountDist]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| CaseBasicCountDistId | Int | 4 | N | Y | N | Primary key |
| CaseBasicCountId | Int | 4 | N | N | Y | Link to CaseBasicCount table |
| DistributionTypeNum | Int | 4 | N | N | N | |
| FieldNum | Int | 4 | Y | N | N | |
| Cnt0 | Int | 4 | Y | N | N | |
| Cnt1 | Int | 4 | Y | N | N | |
| Cnt2 | Int | 4 | Y | N | N | |
| Cnt3 | Int | 4 | Y | N | N | |
| Cnt4 | Int | 4 | Y | N | N | |
| [Namrs].[CaseBasicCountFld]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| CaseBasicCountFldId | Int | 4 | N | Y | N | Primary key |
| CaseBasicCountId | Int | 4 | N | N | Y | Link to CaseBasicCount table |
| FieldTypeNum | Int | 4 | N | N | N | |
| FieldNum | Int | 4 | N | N | N | |
| Cnt | Int | 4 | N | N | N | |
| [Namrs].[CaseDataSet]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| CaseDataSetId | Int | 4 | N | Y | N | Primary key |
| FiscalYear | Int | 4 | N | N | Y | Link to ReportingPeriod table |
| RegionId | Int | 4 | N | N | Y | Link to Region table |
| WorkflowStatusLuId | Int | 4 | N | N | Y | Link to WorkflowStatusLu table |
| XmlFilename | NVarChar (100) | 100 | N | N | N | |
| CreationTime | DateTime | 8 | N | N | N | |
| CreationUserId | NVarChar (128) | 128 | Y | N | N | |
| [Namrs].[CaseStatistic]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| CaseStatisticId | Int | 4 | N | Y | N | Primary key |
| CaseDataSetId | Int | 4 | Y | N | Y | Link to CaseDataSet table |
| InvestigationCountStart | Int | 4 | Y | N | N | |
| InvestigationCountFinal | Int | 4 | Y | N | N | |
| ClientCountStart | Int | 4 | Y | N | N | |
| ClientCountFinal | Int | 4 | Y | N | N | |
| PerpetratorCountStart | Int | 4 | Y | N | N | |
| PerpetratorCountFinal | Int | 4 | Y | N | N | |
| MaltreatmentCountStart | Int | 4 | Y | N | N | |
| MaltreatmentCountFinal | Int | 4 | Y | N | N | |
| RelationshipCountStart | Int | 4 | Y | N | N | |
| RelationshipCountFinal | Int | 4 | Y | N | N | |
| [Namrs].[Client]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | N | Primary key |
| InvestigationId | Int | 4 | N | N | Y | Link to Investigation table |
| ClientCode | NVarChar (32) | 32 | N | N | N | |
| FacilityCode | NVarChar (15) | 15 | Y | N | N | |
| FacilityCodeStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| ClientFipsId | Char (5) | 5 | Y | N | Y | Link to FIPS table |
| ClientFipsStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| CloseReasonLuId | Int | 4 | Y | N | Y | Link to CloseReasonLu table |
| CloseReasonStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| AgeLuId | Int | 4 | Y | N | Y | Link to AgeLu table |
| AgeStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| GenderIdentityLuId | Int | 4 | Y | N | Y | Link to GenderIdentityLu table |
| GenderIdentityStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| SexOrientLuId | Int | 4 | Y | N | Y | Link to SexOrientLu table |
| SexOrientStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| EngCompLuId | Int | 4 | Y | N | Y | Link to EngCompLu table |
| EngCompStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| PrimLangLuId | Int | 4 | Y | N | Y | Link to PrimLangLu table |
| PrimLangStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| MaritalLuId | Int | 4 | Y | N | Y | Link to MaritalLu table |
| MaritalStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| SchoolingLuId | Int | 4 | Y | N | Y | Link to SchoolingLu table |
| SchoolingStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| EmployLuId | Int | 4 | Y | N | Y | Link to EmployLu table |
| EmployStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| IncomeLuId | Int | 4 | Y | N | Y | Link to IncomeLu table |
| IncomeStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| VeteranLuId | Int | 4 | Y | N | Y | Link to VeteranLu table |
| VeteranStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| AdlScore | Int | 4 | Y | N | N | |
| AdlScoreStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| IadlScore | Int | 4 | Y | N | N | |
| IadlScoreStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| LivingSetStartLuId | Int | 4 | Y | N | Y | Link to LivingSetLu table |
| LivingSetStartStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| LivingSetCloseLuId | Int | 4 | Y | N | Y | Link to LivingSetLu table |
| LivingSetCloseStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| PreviousReportLuId | Int | 4 | Y | N | Y | Link to PreviousReportLu table |
| PreviousReportStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| MaltxSettingLuId | Int | 4 | Y | N | Y | Link to MaltxSettingLu table |
| MaltxSettingStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| RaceStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| EthnicityStLuId | Int | 4 | N | N | N | |
| BenefitStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| DisabilityStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| BhScreeningStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| BhDiagnosisStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| DecisionStartStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| DecisionCloseStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| InterAgencyStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| ServiceStartStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| ServiceCloseStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| ServiceApsStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| ServiceReferredStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| [Namrs].[ClientBenefit]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| BenefitLuId | Int | 4 | N | Y | Y | Link to BenefitLu table |
| [Namrs].[ClientBhDiagnosis]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| BhDiagnosisLuId | Int | 4 | N | Y | Y | Link to BehaviorHealthLu table |
| [Namrs].[ClientBhScreening]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| BehaviorHealthLuId | Int | 4 | N | Y | Y | Link to BehaviorHealthLu table |
| [Namrs].[ClientDecisionClose]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| DecisionLuId | Int | 4 | N | Y | Y | Link to DecisionLu table |
| [Namrs].[ClientDecisionStart]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| DecisionLuId | Int | 4 | N | Y | Y | Link to DecisionLu table |
| [Namrs].[ClientDisability]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| DisabilityLuId | Int | 4 | N | Y | Y | Link to DisabilityLu table |
| [Namrs].[ClientEthnicity]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| EthnicityId | Int | 4 | N | Y | Y | Link to EthnicityLu table |
| Namrs].[ClientInteragency]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| InteragencyLuId | Int | 4 | N | Y | Y | Link to InteragencyLu table |
| [Namrs].[ClientRace]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| RaceLuId | Int | 4 | N | Y | Y | Link to RaceLu table |
| [Namrs].[ClientServiceAps]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| ServiceLuId | Int | 4 | N | Y | Y | Link to CaseServiceLu table |
| [Namrs].[ClientServiceClose]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| ServiceLuId | Int | 4 | N | Y | Y | Link to CaseServiceLu table |
| [Namrs].[ClientServiceReferred]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| ServiceLuId | Int | 4 | N | Y | Y | Link to CaseServiceLu table |
| [Namrs].[ClientServiceStart]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | Y | Link to Client table |
| ServiceLuId | Int | 4 | N | Y | Y | Link to CaseServiceLu table |
| [Namrs].[Fips]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| FipsId | Char (5) | 5 | N | Y | N | Primary key |
| StateFips | Char (2) | 2 | N | N | N | |
| CountyFips | Char (3) | 3 | N | N | N | |
| UsStateLuId | Char (2) | 2 | N | N | Y | Link to UsStateLu table |
| CountyName | NVarChar (100) | 100 | N | N | N | |
| [Namrs].[Investigation]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| InvestigationId | Int | 4 | N | Y | N | Primary key |
| CaseDataSetId | Int | 4 | N | N | Y | Link to CaseDataSet table |
| InvestigationCode | NVarChar (32) | 32 | N | N | N | |
| ReportDate | Date | 3 | Y | N | N | |
| ReportDateStLuId | Int | 4 | N | N | N | |
| ReportSourceLuId | Int | 4 | Y | N | Y | Link to ReportSourceLu table |
| ReportSourceStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| AgencyFipsId | Char (5) | 5 | Y | N | Y | Link to FIPS table |
| AgencyFipsStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| StartDate | Date | 3 | Y | N | N | |
| StartDateStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| DispositionDate | Date | 3 | Y | N | N | |
| DispositionDateStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| CaseClosureDate | Date | 3 | N | N | N | |
| [Namrs].[KeyIndicatorSet]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| KeyIndicatorSetId | Int | 4 | N | Y | N | Primary key |
| FiscalYear | Int | 4 | N | N | Y | Link to ReportingPeriod table |
| RegionId | Int | 4 | N | N | Y | Link to Region table |
| WorkflowStatusLuId | Int | 4 | N | N | Y | Link to WorkflowStatusLu table |
| IsCalculated | Bit | 1 | N | N | N | |
| ClientInv | Int | 4 | Y | N | N | |
| ClientInvComment | NVarCharMax | -1 | Y | N | N | |
| ClientInteragency | Int | 4 | Y | N | N | |
| ClientInteragencyComment | NVarCharMax | -1 | Y | N | N | |
| ClientClosureInvComplete | Int | 4 | Y | N | N | |
| ClientClosureProtComplete | Int | 4 | Y | N | N | |
| ClientClosureRefused | Int | 4 | Y | N | N | |
| ClientClosureIncomplete | Int | 4 | Y | N | N | |
| ClientClosureDied | Int | 4 | Y | N | N | |
| ClientClosureOther | Int | 4 | Y | N | N | |
| ClientClosureUnknown | Int | 4 | Y | N | N | |
| ClientClosureComment | NVarCharMax | -1 | Y | N | N | |
| VictimMultMalt | Int | 4 | Y | N | N | |
| VictimMultMaltComment | NVarCharMax | -1 | Y | N | N | |
| VictimAge18 | Int | 4 | Y | N | N | |
| VictimAge30 | Int | 4 | Y | N | N | |
| VictimAge40 | Int | 4 | Y | N | N | |
| VictimAge50 | Int | 4 | Y | N | N | |
| VictimAge60 | Int | 4 | Y | N | N | |
| VictimAge70 | Int | 4 | Y | N | N | |
| VictimAge75 | Int | 4 | Y | N | N | |
| VictimAge85 | Int | 4 | Y | N | N | |
| VictimAgeUnknown | Int | 4 | Y | N | N | |
| VictimAgeComment | NVarCharMax | -1 | Y | N | N | |
| VictimRaceAian | Int | 4 | Y | N | N | |
| VictimRaceAsian | Int | 4 | Y | N | N | |
| VictimRaceBlackAa | Int | 4 | Y | N | N | |
| VictimRaceHawaiianPi | Int | 4 | Y | N | N | |
| VictimRaceWhite | Int | 4 | Y | N | N | |
| VictimRaceMultiRacial | Int | 4 | Y | N | N | |
| VictimRaceUnknown | Int | 4 | Y | N | N | |
| VictimRaceComment | NVarCharMax | -1 | Y | N | N | |
| VictimEthnicityHispanic | Int | 4 | Y | N | N | |
| VictimEthnicityNotHispanic | Int | 4 | Y | N | N | |
| VictimEthnicityUnknown | Int | 4 | Y | N | N | |
| VictimEthnicityComment | NVarCharMax | -1 | Y | N | N | |
| VictimGenderMale | Int | 4 | Y | N | N | |
| VictimGenderFemale | Int | 4 | Y | N | N | |
| VictimGenderTransgender | Int | 4 | Y | N | N | |
| VictimGenderUnknown | Int | 4 | Y | N | N | |
| VictimGenderComment | NVarCharMax | -1 | Y | N | N | |
| VictimWithBenefit | Int | 4 | Y | N | N | |
| VictimWithBenefitComment | NVarCharMax | -1 | Y | N | N | |
| VictimWithDisability | Int | 4 | Y | N | N | |
| VictimWithDisabilityComment | NVarCharMax | -1 | Y | N | N | |
| VictimWithScrnBehavior | Int | 4 | Y | N | N | |
| VictimWithScrnBehaviorComment | NVarCharMax | -1 | Y | N | N | |
| VictimMaltAbandon | Int | 4 | Y | N | N | |
| VictimMaltEmotAbuse | Int | 4 | Y | N | N | |
| VictimMaltFinExpl | Int | 4 | Y | N | N | |
| VictimMaltNeglect | Int | 4 | Y | N | N | |
| VictimMaltPhysAbuse | Int | 4 | Y | N | N | |
| VictimMaltSexAbuse | Int | 4 | Y | N | N | |
| VictimMaltSuspDeath | Int | 4 | Y | N | N | |
| VictimMaltSelfNeglect | Int | 4 | Y | N | N | |
| VictimMaltOther | Int | 4 | Y | N | N | |
| VictimMaltUnknown | Int | 4 | Y | N | N | |
| VictimMaltComment | NVarCharMax | -1 | Y | N | N | |
| VictimWithGuardOrConv | Int | 4 | Y | N | N | |
| VictimWithGuardOrConvComment | NVarCharMax | -1 | Y | N | N | |
| VictimWithSvcAps | Int | 4 | Y | N | N | |
| VictimWithSvcApsComment | NVarCharMax | -1 | Y | N | N | |
| PerpAge18 | Int | 4 | Y | N | N | |
| PerpAge30 | Int | 4 | Y | N | N | |
| PerpAge40 | Int | 4 | Y | N | N | |
| PerpAge50 | Int | 4 | Y | N | N | |
| PerpAge60 | Int | 4 | Y | N | N | |
| PerpAge70 | Int | 4 | Y | N | N | |
| PerpAge75 | Int | 4 | Y | N | N | |
| PerpAge85 | Int | 4 | Y | N | N | |
| PerpAgeUnknown | Int | 4 | Y | N | N | |
| PerpAgeComment | NVarCharMax | -1 | Y | N | N | |
| PerpGenderMale | Int | 4 | Y | N | N | |
| PerpGenderFemale | Int | 4 | Y | N | N | |
| PerpGenderTransgender | Int | 4 | Y | N | N | |
| PerpGenderUnknown | Int | 4 | Y | N | N | |
| PerpGenderComment | NVarCharMax | -1 | Y | N | N | |
| PerpWithKinship | Int | 4 | Y | N | N | |
| PerpWithKinshipComment | NVarCharMax | -1 | Y | N | N | |
| PerpWithAssoc | Int | 4 | Y | N | N | |
| PerpWithAssocComment | NVarCharMax | -1 | Y | N | N | |
| PerpWithLegal | Int | 4 | Y | N | N | |
| PerpWithLegalComment | NVarCharMax | -1 | Y | N | N | |
| CreationTime | DateTime | 8 | N | N | N | |
| CreationUserId | NVarChar (128) | 128 | Y | N | N | |
| [Namrs].[Malperp]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| MaltreatmentId | Int | 4 | N | Y | Y | Link to Maltreatment table |
| PerpetratorId | Int | 4 | N | Y | Y | Link to Perpetrator table |
| [Namrs].[Maltreatment]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| MaltreatmentId | Int | 4 | N | Y | N | Primary key |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| MaltreatmentTypeLuId | Int | 4 | Y | N | Y | Link to MaltreatmentTypeLu table |
| MtxDispositionLuId | Int | 4 | Y | N | Y | Link to MtxDispositionLu table |
| [Namrs].[PerpBhDiagnosis]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PerpetratorId | Int | 4 | N | Y | Y | Link to Perpetrator table |
| BehaviorHealthLuId | Int | 4 | N | Y | Y | Link to BehaviorHealthLu table |
| [Namrs].[PerpBhScreening]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PerpetratorId | Int | 4 | N | Y | Y | Link to Perpetrator table |
| BehaviorHealthLuId | Int | 4 | N | Y | Y | Link to BehaviorHealthLu table |
| [Namrs].[PerpDisability]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PerpetratorId | Int | 4 | N | Y | Y | Link to Perpetrator table |
| DisabilityLuId | Int | 4 | N | Y | Y | Link to DisabilityLu table |
| [Namrs].[PerpEthnicity]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PerpetratorId | Int | 4 | N | Y | Y | Link to Perpetrator table |
| EthnicityId | Int | 4 | N | Y | Y | Link to EthnicityLu table |
| [Namrs].[Perpetrator]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PerpetratorId | Int | 4 | N | Y | N | Primary key |
| InvestigationId | Int | 4 | N | N | Y | Link to Investigation table |
| PerpetratorCode | NVarChar (32) | 32 | Y | N | N | |
| AgeLuId | Int | 4 | Y | N | Y | Link to AgeLu table |
| AgeStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| GenderIdentityLuId | Int | 4 | Y | N | Y | Link to GenderIdentityLu table |
| GenderIdentityStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| EthnicityStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| DisabilityStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| BhScreeningStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| BhDiagnosisStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| RaceStLuId | Int | 4 | N | N | N | |
| [Namrs].[PerpRace]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PerpetratorId | Int | 4 | N | Y | Y | Link to Perpetrator table |
| RaceLuId | Int | 4 | N | Y | Y | Link to RaceLu table |
| [Namrs].[Region]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| RegionId | Int | 4 | N | Y | N | Primary key |
| IsState | Bit | 1 | N | N | N | |
| IsAian | Bit | 1 | N | N | N | |
| IsActive | Bit | 1 | N | N | N | |
| ShortName | NVarChar (100) | 100 | N | N | N | |
| LongName | NVarChar (500) | 500 | N | N | N | |
| [Namrs].[Relationship]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| RelationshipId | Int | 4 | N | Y | N | Primary key |
| PerpetratorId | Int | 4 | N | N | Y | Link to Perpetrator table |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CohabStartLuId | Int | 4 | Y | N | Y | Link to CohabLu table |
| CohabStartStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| CohabCloseLuId | Int | 4 | Y | N | Y | Link to CohabLu table |
| CohabCloseStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| KinshipLuId | Int | 4 | Y | N | Y | Link to KinshipLu table |
| KinshipStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| PerpAssocStartLuId | Int | 4 | Y | N | Y | Link to AssociationLu table |
| PerpAssocStartStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| PerpAssocCloseLuId | Int | 4 | Y | N | Y | Link to AssociationLu table |
| PerpAssocCloseStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| PerpDecisionStartStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| PerpDecisionCloseStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| PerpLegalRemedyStLuId | Int | 4 | N | N | Y | Link to AttributeStatusLu table |
| [Namrs].[RelPerpDecisionClose]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| RelationshipId | Int | 4 | N | Y | Y | Link to Relationship table |
| DecisionLuId | Int | 4 | N | Y | Y | Link to DecisionLu table |
| [Namrs].[RelPerpDecisionStart]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| RelationshipId | Int | 4 | N | Y | Y | Link to Relationship table |
| DecisionLuId | Int | 4 | N | Y | Y | Link to DecisionLu table |
| [Namrs].[RelPerpLegalRemedy]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| RelationshipId | Int | 4 | N | Y | Y | Link to Relationship table |
| LegalRemedyLuId | Int | 4 | N | Y | Y | Link to LegalRemedyLu table |
| [Namrs].[ReportingPeriod]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| FiscalYear | Int | 4 | N | Y | N | Primary key |
| Comments | NVarCharMax | -1 | Y | N | N | |
| IsActive | Bit | 1 | N | N | N | |
| CreationTime | DateTime | 8 | N | N | N | |
| CreationUserId | NVarChar (128) | 128 | Y | N | N | |
Appendix C. Namrs Pilot Data Warehouse Entity Relationship Diagram
This diagram is oversized and should be viewed in the PDF version of this report.
Appendix D. Namrs Pilot Data Warehouse Tables and Columns
This section lists the Tables, Columns, and Column Attributes for the NAMRS Pilot Data Warehouse.
| [DataStore].[CaseDataSet]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| CaseDataSetId | Int | 4 | N | Y | N | Primary key |
| CasFiscalYear | Int | 4 | N | N | N | |
| CasRegionName | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[Client]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | Y | N | Primary key |
| InvestigationId | Int | 4 | N | N | Y | Link to Investigation table |
| ClientCode | NVarChar (32) | 32 | N | N | N | |
| CliFacilityCode | NVarChar (32) | 32 | Y | N | N | |
| CliFacilityCodeStatus | NVarChar (10) | 10 | N | N | N | |
| CliFips | Char (5) | 5 | Y | N | N | |
| CliFipsStatus | NVarChar (10) | 10 | N | N | N | |
| CliState | NVarChar (100) | 100 | Y | N | N | |
| CliCounty | NVarChar (100) | 100 | Y | N | N | |
| CliCloseReason | NVarChar (100) | 100 | Y | N | N | |
| CliCloseReasonStatus | NVarChar (10) | 10 | N | N | N | |
| CliAge | NVarChar (100) | 100 | Y | N | N | |
| CliAgeStatus | NVarChar (10) | 10 | N | N | N | |
| CliGenderIdentity | NVarChar (100) | 100 | Y | N | N | |
| CliGenderIdentityStatus | NVarChar (10) | 10 | N | N | N | |
| CliSexualOrientation | NVarChar (100) | 100 | Y | N | N | |
| CliSexualOrientationStatus | NVarChar (10) | 10 | N | N | N | |
| CliEnglishCompetency | NVarChar (100) | 100 | Y | N | N | |
| CliEnglishCompetencyStatus | NVarChar (10) | 10 | N | N | N | |
| CliPrimaryLanguage | NVarChar (100) | 100 | Y | N | N | |
| CliPrimaryLanguageStatus | NVarChar (10) | 10 | N | N | N | |
| CliMarital | NVarChar (100) | 100 | Y | N | N | |
| CliMaritalStatus | NVarChar (10) | 10 | N | N | N | |
| CliSchooling | NVarChar (100) | 100 | Y | N | N | |
| CliSchoolingStatus | NVarChar (10) | 10 | N | N | N | |
| CliEmployment | NVarChar (100) | 100 | Y | N | N | |
| CliEmploymentStatus | NVarChar (10) | 10 | N | N | N | |
| CliIncome | NVarChar (100) | 100 | Y | N | N | |
| CliIncomeStatus | NVarChar (10) | 10 | N | N | N | |
| CliVeteran | NVarChar (100) | 100 | Y | N | N | |
| CliVeteranStatus | NVarChar (10) | 10 | N | N | N | |
| CliAdlScore | NVarChar (100) | 100 | Y | N | N | |
| CliAdlScoreStatus | NVarChar (10) | 10 | N | N | N | |
| CliIadlScore | NVarChar (100) | 100 | Y | N | N | |
| CliIadlScoreStatus | NVarChar (10) | 10 | N | N | N | |
| CliLivingSettingStart | NVarChar (100) | 100 | Y | N | N | |
| CliLivingSettingStartStatus | NVarChar (10) | 10 | N | N | N | |
| CliLivingSettingClose | NVarChar (100) | 100 | Y | N | N | |
| CliLivingSettingCloseStatus | NVarChar (10) | 10 | N | N | N | |
| CliPreviousReport | NVarChar (100) | 100 | Y | N | N | |
| CliPreviousReportStatus | NVarChar (10) | 10 | N | N | N | |
| CliMaltreatmentSetting | NVarChar (100) | 100 | Y | N | N | |
| CliMaltreatmentSettingStatus | NVarChar (10) | 10 | N | N | N | |
| CliRaceStatus | NVarChar (10) | 10 | N | N | N | |
| CliEthnicityStatus | NVarChar (10) | 10 | N | N | N | |
| CliBenefitStatus | NVarChar (10) | 10 | N | N | N | |
| CliDisabilityStatus | NVarChar (10) | 10 | N | N | N | |
| CliBehavioralHealthScreeningStatus | NVarChar (10) | 10 | N | N | N | |
| CliBehavioralHealthDiagnosisStatus | NVarChar (10) | 10 | N | N | N | |
| CliDecisionmakerStartStatus | NVarChar (10) | 10 | N | N | N | |
| CliDecisionmakerCloseStatus | NVarChar (10) | 10 | N | N | N | |
| CliInteragencyCoordinationStatus | NVarChar (10) | 10 | N | N | N | |
| CliServiceStartStatus | NVarChar (10) | 10 | N | N | N | |
| CliServiceCloseStatus | NVarChar (10) | 10 | N | N | N | |
| CliServiceApsStatus | NVarChar (10) | 10 | N | N | N | |
| CliServiceReferredStatus | NVarChar (10) | 10 | N | N | N | |
| [DataStore].[ClientBehavioralHealthDiagnosis]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliBehavioralHealthDiagnosis | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[ClientBehavioralHealthScreening]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliBehaviorHealthScreening | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[ClientBenefit]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliBenefit | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[ClientDecisionmakerClose]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliDecisionmakerClose | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[ClientDecisionmakerStart]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliDecisionmakerStart | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[ClientDisability]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliDisability | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[ClientEthnicity]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliEthnicity | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[ClientInteragency]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliInteragency | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[ClientRace]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliRace | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[ClientServiceAps]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliServiceAps | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[ClientServiceClose]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliServiceClose | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[ClientServiceReferred]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliServiceReferred | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[ClientServiceStart]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| CliServiceStart | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[Investigation]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| InvestigationId | Int | 4 | N | Y | N | Primary key |
| CaseDataSetId | Int | 4 | N | N | Y | Link to CaseDataSet table |
| InvestigationCode | NVarChar (32) | 32 | N | N | N | |
| InvReportDate | Date | 3 | Y | N | N | |
| InvReportDateStatus | NVarChar (10) | 10 | N | N | N | |
| InvReportSource | NVarChar (100) | 100 | Y | N | N | |
| InvReportSourceStatus | NVarChar (10) | 10 | N | N | N | |
| InvAgencyFips | Char (5) | 5 | Y | N | N | |
| InvAgencyFipsStatus | NVarChar (10) | 10 | N | N | N | |
| InvAgencyState | NVarChar (100) | 100 | Y | N | N | |
| InvAgencyCounty | NVarChar (100) | 100 | Y | N | N | |
| InvStartDate | Date | 3 | Y | N | N | |
| InvStartDateStatus | NVarChar (10) | 10 | N | N | N | |
| InvDispositionDate | Date | 3 | Y | N | N | |
| InvDispositionDateStatus | NVarChar (10) | 10 | N | N | N | |
| InvCaseClosureDate | Date | 3 | N | N | N | |
| [DataStore].[Maltreatment]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| MaltreatmentId | Int | 4 | N | Y | N | Primary key |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| MaltreatmentType | NVarChar (100) | 100 | Y | N | N | |
| MaltreatmentDisposition | NVarChar (100) | 100 | Y | N | N | |
| [DataStore].[MaltreatmentPerpetrator]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| MaltreatmentId | Int | 4 | N | Y | Y | Link to Maltreatment table |
| PerpetratorId | Int | 4 | N | Y | Y | Link to Perpetrator table |
| [DataStore].[PerpBehavioralHealthDiagnosis]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PerpetratorId | Int | 4 | N | N | Y | Link to Perpetrator table |
| PrpBehaviorHealthDiagnosis | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[PerpBehavioralHealthScreening]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PerpetratorId | Int | 4 | N | N | Y | Link to Perpetrator table |
| PrpBehaviorHealthScreening | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[PerpDisability]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PerpetratorId | Int | 4 | N | N | Y | Link to Perpetrator table |
| PrpDisability | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[PerpEthnicity]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PerpetratorId | Int | 4 | N | N | Y | Link to Perpetrator table |
| PrpEthnicity | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[Perpetrator]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PerpetratorId | Int | 4 | N | Y | N | Primary key |
| InvestigationId | Int | 4 | N | N | Y | Link to Investigation table |
| PerpetratorCode | NVarChar (32) | 32 | Y | N | N | |
| PrpAge | NVarChar (100) | 100 | Y | N | N | |
| PrpAgeStatus | NVarChar (10) | 10 | N | N | N | |
| PrpGenderIdentity | NVarChar (100) | 100 | Y | N | N | |
| PrpGenderIdentityStatus | NVarChar (10) | 10 | N | N | N | |
| PrpRaceStatus | NVarChar (10) | 10 | N | N | N | |
| PrpEthnicityStatus | NVarChar (10) | 10 | N | N | N | |
| PrpDisabilityStatus | NVarChar (10) | 10 | N | N | N | |
| PrpBehavioralHealthScreeningStatus | NVarChar (10) | 10 | N | N | N | |
| PrpBehavioralHealthDiagnosisStatus | NVarChar (10) | 10 | N | N | N | |
| [DataStore].[PerpRace]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| PerpetratorId | Int | 4 | N | N | Y | Link to Perpetrator table |
| PrpRace | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[Relationship]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| RelationshipId | Int | 4 | N | Y | N | Primary key |
| PerpetratorId | Int | 4 | N | N | Y | Link to Perpetrator table |
| ClientId | Int | 4 | N | N | Y | Link to Client table |
| RelCohabitationStart | NVarChar (100) | 100 | Y | N | N | |
| RelCohabitationStartStatus | NVarChar (10) | 10 | N | N | N | |
| RelCohabitationClose | NVarChar (100) | 100 | Y | N | N | |
| RelCohabitationCloseStatus | NVarChar (10) | 10 | N | N | N | |
| RelKinship | NVarChar (100) | 100 | Y | N | N | |
| RelKinshipStatus | NVarChar (10) | 10 | N | N | N | |
| RelAssociationStart | NVarChar (100) | 100 | Y | N | N | |
| RelAssociationStartStatus | NVarChar (10) | 10 | N | N | N | |
| RelAssociationClose | NVarChar (100) | 100 | Y | N | N | |
| RelAssociationCloseStatus | NVarChar (10) | 10 | N | N | N | |
| RelDecisionmakerStartStatus | NVarChar (10) | 10 | N | N | N | |
| RelDecisionmakerCloseStatus | NVarChar (10) | 10 | N | N | N | |
| RelLegalRemedyStatus | NVarChar (10) | 10 | N | N | N | |
| [DataStore].[RelDecisionmakerClose]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| RelationshipId | Int | 4 | N | N | Y | Link to Relationship table |
| RelDecisionmakerClose | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[RelDecisionmakerStart]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| RelationshipId | Int | 4 | N | N | Y | Link to Relationship table |
| RelDecisionmakerStart | NVarChar (100) | 100 | N | N | N | |
| [DataStore].[RelLegalRemedy]: | ||||||
|---|---|---|---|---|---|---|
| Column | Datatype | Size | Nullable | InPrimaryKey | IsForeignKey | Description |
| RelationshipId | Int | 4 | N | N | Y | Link to Relationship table |
| RelLegalRemedy | NVarChar (100) | 100 | N | N | N | |
Project and Report Information
This report was prepared under contract #HHSP23320095656WC between the U.S. Department of Health and Human Services (HHS), Office of Disability, Aging and Long-Term Care Policy (DALTCP) and Walter R. McDonald and Associates. For additional information about this subject, you can visit the DALTCP home page at http://aspe.hhs.gov/office-disability-aging-and-long-term-care-policy-daltcp or contact the ASPE Project Officer, Helen Lamont, at HHS/ASPE/DALTCP, Room 424E, H.H. Humphrey Building, 200 Independence Avenue, SW, Washington, DC 20201. Her e-mail address is: Helen.Lamont@hhs.gov.
Files Available
VOLUME 1: PILOT OVERVIEW AND FUTURE RECOMMENDATIONS
- HTML https://aspe.hhs.gov/report/development-national-adult-protective-services-data-system-namrs-pilot-final-report-volume-1
- PDF https://aspe.hhs.gov/pdf-report/development-national-adult-protective-services-data-system-namrs-pilot-final-report-volume-1
VOLUME 2: SYSTEM DOCUMENTATION