Prior to founding Crowdbitz Inc, my company Tunnell Solutions completed several large development projects. The largest and most interesting of these was the Acute Mental Health Services Database for the Vancouver Island Health Authority (British Columbia, Canada). It ended up being an engagement of over ten years, and involved extensive consultation with front line mental health workers. The project went through several evolutions over the years as technology, organizations and the legal framework changed. It became a test case for how change impacts technology and causes it to evolve or be abandoned. 

The Beginning - A Desktop Application

The first iteration of the database that the clients had requested was a desktop application to run on Windows systems in the Nanaimo General Hospital, accessing a shared database on a file share located in the hospital's data center. We started with extensive consultation with the stakeholders, focusing on the  nurses that would be using the database on a daily basis. 

We developed this for them with Visual Studio, using a set of flat files for the 'database' tables. It was initially setup on five workstations in the main offices of Mental Health Services. It was later extended to other workstations, including the main desk at the Psychiatric Ward, as well as managers and psychologists.  

The innovations in the application at the time were:

• Complete separation of personal and demographic data. This made it possible to create reports and pull statistics with no exposure of private data.
• Ability to provide access to only the demographic data, so that users that are not allowed to view private data could still gather and review information from the database.
• Ability for users to edit and control the content of drop down lists. 

Supporting the application and the database, we identified several issues that were mainly due to limitations of the technology:

• The application needed to be installed on workstations, which required coordination with the IT staff. 
• The IT staff had not been involved in the initial project and so were understandably resistant (at first) to support the application.
• There was limited reporting in the first version, and enhanced reporting required updates and/or re-installation of the application. 
• There was no auditing and logging of access.
• Not available to mobile devices or non windows workstations.

First Evolution - A Web Application

What had been the Central Vancouver Island Health Region had become the Vancouver Island Health Authority. This was both a disruption to the project and an opportunity to extend access and usage. The clients engaged with us to update the application and make it more accessible. After a period of consultation we decided to create a web application using .NET and Active Server Pages. 

We completely re-wrote the application, and built a new database structure. We moved the application to use Microsoft SQL Server instead of the custom database. We converted and migrated the data when we went live.

Several innovations came with this version:

• A distributed web application accessible only via the Health Region's VPN.
• The ability to have hundreds of users spread over a geographic area. 
• Mobile and accessibility with other devices. 
• Full logging and auditing of all access. It is possible to see who saw what when, even if the data is subsequently edited. 

However we began to have issues with our custom database and flat files that were still hosted in the Nanaimo hospital's data center. Once the database reached over about 10,000 clients and 100,000 contacts, user access and reporting started to slow down. The decision was made to migrate to a fully supported database server.

Second Evolution - Privacy and Security Modifications

The use of the database continued to increase, eventually reaching over 500 users, 50,000 clients, and 500,000 client interactions (contacts). As its use expanded, the Health Authority had some concerns about privacy and the security and safety of its users. They also wanted to be able to audit access to the information.

Our company took these requirements and delivered modifications that met and exceeded the requirements:

• An alert system to identify if a client was at particular risk of self harm, violence, or other status that would be a risk to themselves or caregivers.
• Expanded printing and reporting, including a query page to pull data from the system.
• Expanded ability of users to add, edit and remove drop down lists.
• A full logging and audit system to show who saw what when. The emphasis here is on what. The data recorded captured the actual data at the time, even if it had been changed or erased since the view. This system also tracked new entries and edits of the data.

During this time the system was also migrated to the data centers in Jubilee Hospital (Victoria, B.C), and placed on newer equipment with newer version of IIS and SQL server.

Sunset and Retirement

Eventually the health region purchased a new enterprise medical records system that included a mental health module. Initially they planned to fully retire the Acute Mental Health Services Database, but the nurses working in mental health expressed their desire to keep using the system. Eventually it was decided to move the application and database to a read only mode. It stayed in this mode for a number of years, and the data itself is still available to users more than a decade after the initial development. 

The Web Lead of the Vancouver Island Health Authority once characterized the AMHSDB as "the least troublesome critical application we run". Once the application was rebuilt with .NET and SQL Server it never experienced a data loss or error, there was never a major bug identified, and it never became unavailable due to a memory leak or similar issue.

Crowdbitz maintains the IP for the database, and has used large sections of the code for other implementations, such as mentor database, a search engine for BC Credit Unions and a character generator for a roleplaying game. 

The AMHSDB was an incredible learning experience for me and for my company. The support of the system for over a decade was a journey through technologies and concepts in data management that are of great value to this day.