db-web-app

Introduction

A web app for managing research projects. It is being designed to target small research groups that do not have access to a professional database administrator but want to be able to manage their data in a remote relational database. But beyond just managing database data, it is being designed as a comprehensive system for managing all aspects of a research project. It is currently unavailable but will be released as open-source once it’s in a more stable state. It will still need some level of IT support for setting up and managing a server with the required software.

Immediate Goals

These are the primary things I want the web app to accomplish:

• Act as a middleware layer between users and a backend database: Giving users that are inexperienced with relational database management systems (RDMS; e.g., MySQL, PostgreSQL, etc) and SQL direct access to a database can be dangerous if not handled correctly. A middleware application makes accessing the database more user-friendly while simultaneously acting as a protective layer for the database. A middleware layer also simplifies certain tasks that are difficult/impossible/inefficient to manage directly in an RDMS.
• Simplify the database construction process: RDMS are complex and have many settings and features that can be overwhelming for inexperienced users. The web app hides most of the complexity of creating database tables while retaining enough flexibility to handle most situations.
• Serve as a central project management location: Allows multiple projects, each with its own set of isolated tables. Using a single central system helps groups to avoid situations where files and data for projects become misplaced.
• User account management: Give project managers fine-grained control over user access to the projects. This includes specifying read/write access for individual projects and may be extended to include table and even row-level controls (e.g., a user can fix/delete data they entered into a table, but not data entered by others). Access controls help limit accidental damage from users that need to be able to read data but aren’t the ones responsible for entering or managing it.
• Data logging: Keep track of data changes within the database, including who modified it, when they made the modification, and what the change was. This allows managers to identify and revert accidental changes.
• Inventory integration: Give project managers the ability to create and manage inventory data, and integrate it with project data. This allows for things like linking field sample data with the various kits/chemicals/reagents that may have been used to process them in a lab setting (including things like lot numbers if desired).
• File management: Directly link files to related projects. This can be things like protocols, grant proposals, reports, final publications, etc. Will include a version management system so that things like protocols can be updated without losing old versions. Files can then be linked to project/lab/inventory data tables, which will make it easy to do things like finding the protocol+version used to create a reagent. Files will also have user access controls to restrict who can read/change files.
• Task management: Give users the ability to assign and manage tasks. Can be linked to specific projects to track goals, progress, and history of work done.
• Chat system: Provide a central location for tracking discussions related to projects and tasks.

Long-term Goals

Some long-term plans for what I want to accomplish:

• Build in some basic data summary tools. Essentially, I want users to be able to construct simple data reports once, and then whenever the data is updated, the reports are automatically updated. These would be relatively simple (computationally) things like simple stats (min, max, count, mean), as well as simple figures. Basically, give users the ability to quickly lookup answers about their data for simple questions.
• Create a data access API to give external tools the ability to download data copies. For example, this could be used in an R package to provide functionality for downloading a project as a local SQLite database. This local database copy can then be used locally using tools like dbplyr and makes redistributing the data with the project easy. Consideration would obviously be given for protecting user access credentials to the remote database.
• Create a smartphone app that integrates with the web app. The main feature would be that it can automatically generate data forms for projects, essentially making it a datapad to eliminate the need for hand-filled datasheets. This can lead to more strict enforcement of data collection (e.g., alerting users about a missing piece of data or wrong types of data), eliminates issues with poor handwriting, and eliminates transcription errors. Would also integrate with things like the chat system.
• Create a plugin system that allows users to create/install custom JavaScript functions to do more specialized things with their data. Essentially a way to extend the built-in summary tools.

Funding

The web app will be released for free under an open-source license. However, given the complexity involved in making it user-friendly, reliable, and secure, it is very time-consuming to build, so I may pursue different routes of income generation once it’s in a state where people can start using it. The most immediate option would be to set up a GitHub donations page, which could also be tied in and justified with other open-source projects I have done/want to do. The other source of income would be making the smartphone app non-free (i.e., you have to buy it to use it). However, it’s not clear there would be a large enough user-base for the app to fund the entire project, and it may take a long time before the smartphone app is ever ready. A third option may be finding some form of grant support. A fourth option could be finding a job that allows me to work on this project, possibly as part of managing data for a lab/organization.

Progress

My first proof-of-concept video showed user login, project creation, data table creation, and data entry. Still lots of work to be done on these.

Proof-of-concept 1

Disclaimer

I can’t promise this will ever be released. I think it is a very important project that could have a significant impact, but it’s also a very high-stakes project. There are important security issues with managing a remote database through a web app, and I need to be certain that user projects and data are secure in terms of the web app. It’s also critical that the web app is reliable and cannot accidentally corrupt user data.