Building a Bug/Issue Tracker with Shiny & PostgreSQL

Dan Gray



Applications are the gears of organization.

organization (noun) : or·ga·ni·za·tion |  ˌȯr-gə-nə-ˈzā-shən | the condition or manner of being organized

Applications facilitate the structuring of tasks, processes, and relationships amongst entities. The modern enterprise business is app-centric, the Web is app-centric, the smart-home is app-centric etc.

Therefore the unit of improvement for any processes should be grounded in provisioning (or occasionally taking away!) functionality from an application to better serve its purpose.


A bug/issue tracking minimum-viable-product for a generic department within a fictional company.


The app consists of the following components:

Primary features include:

Overview and Motivation

I wanted to use the tools I already possess to develop a database base with complete CRUD functionality. R provides a way for developing web-apps through the Shiny ecosystem. The application design is clearly split into two components, UI and Server.

Shiny is well documented and offers support for a broad set of use-cases and functionality.

Connection and Interaction with the Database

Connection to the database is handled via DBI - connection parameters are secured via the systems key credential manager. Other options could have setting up DSN via an ODBC connection, storing the credentials in .config files or using environment variables.

This initial connection is a pool object - connections are handled by the pool package. Therefore database connections can be optimised based on queries execution or termination. This eases connection management, and optimizes query performance.

# create pool to handle database connections
pool <- dbPool(drv = RPostgres::Postgres(),dbname = "postgres",
               host = "localhost", 
               user = keyring::key_list("postgresql")[1,2],
               password = keyring::key_get("postgresql","postgres"),

Initial primary tables are created to hold the issues and users.

# create table to hold users 
create_users_table <- function() {
    print("Connecting to App - Checking Prerequisites")
    # if table does not exist create it
    if(!tbl_owner %in% dbListTables(pool)){
        print("Creating Initial Tables - Users")
      conn <- poolCheckout(pool)
        setup_query <-dbSendQuery(conn,
                                  "CREATE TABLE users(
                                      ownerid SERIAL PRIMARY KEY,
                                      name TEXT NOT NULL,
                                      password TEXT NOT NULL,
                                      hash TEXT NOT NULL);"

Interaction with the database is driven by the following primary functions;

They all use interpolated inputs to ensure secure interaction.

# create issue
create_issue <- function(class_in, unit_in, product_in, 
                         severity_in, effort_in, desc_short_in, 
                         desc_long_in, parent_in, child_in, 

  query_createissue <-isolate(sqlInterpolate(conn,
                                             "INSERT into issues(
                                             class, unit, product, 
                                             severity, effort, desc_short, 
                                             desc_long, parent, child, 
                                             userid, assignee, date)
                                             VALUES(?value_class, ?value_unit, ?value_product, 
                                             ?value_severity, ?value_effort,?value_short, 
                                             ?value_long, ?value_parent, ?value_child, 
                                             ?value_user, ?value_assignee, ?value_date);",

These queries are safe from SQL-injection attacks, as they use placeholders for the query components, which are supplied via user-input.

Interaction and Usage Paths

The app is controlled using reactive-event and observation-event functions that execute in response to user-input - these components can steer UI elements to appear and control the flow through the app.

In the code-extract shown below the loggedIn value is used to set the login-status and used by further Observe components (not shown) to open the app and proceed to the main UI.

The login function is executed in response to user-input; a button click of the action-button (input$login).

    loggedIn <- reactiveVal(value = FALSE)
    user <- reactiveVal(value = NULL)
    # reactive event used to check user credentials
    login <- eventReactive(input$login, {
        # take user input and pass to get_user()
        potential_user <- input$username
        user_data <- get_user(potential_user)
        if(nrow(user_data) > 0){ # If the active user is in the DB then logged in
            if(sha256(input$password) == user_data[1, "hash"]){
                print(paste("- User:", user(), "logged in"))
                # set reactive event "login" to TRUE
        # otherwise leave reactive event "login" as FALSE
    # reactive event to register new users
    register_user <- eventReactive(input$register_user, {
        # take user input and pass to fucntion get_user()
        preexisting_user <- input$new_user
        users_data <-get_user(preexisting_user)
        if(nrow(users_data) > 0){
            return(span("User already exists", style = "color:red"))
        # map user inputs and then pass to create_user()
        new_user <- input$new_user
        new_password <- input$new_pw
        new_hash <- sha256(input$new_pw)

        return(span("Registration Successful!", style = "color:green"))

Video Demonstration

Here is a short video walkthrough highlighting the main features.

The project code can be found on GitHub.

Further Development

The app can be fortified by:

Hosting Considerations and Deployment Strategies

Using a continuous integration tool like Jenkins (with Docker) to build and deploy the app would offer improved stability. Hosting could be facilitated by Azure Container Registry/Containers and provisioning via App Service