Preamble
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.
Deliverable
A bug/issue tracking minimum-viable-product for a generic department within a fictional company.
Architecture
The app consists of the following components:
- A web-based UI [Shiny]
- A backend database [PostgreSQL] with connection pooling
Primary features include:
- Secure user authentication and creation
- Secure communication with the server via SQL interpolation
- Submission, tracking and reporting of issues
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.
the UI (ui.R) presents the components that offer user input, and output
the Server (server.R) handles the functional logic of the app. Here data is requested, user input is processed and output is generated. Modules can be used to further package sections of common operations
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"),
port=5432)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);"
)
setup_query
poolReturn(conn)
}
}Interaction with the database is driven by the following primary functions;
- create queries such as
create_issue()andcreate_user() - get queries such as
get_issue(),get_user(), andreport_generation()
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,
user_in,assignee_in,date_in){
conn<-poolCheckout(pool)
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);",
value_class=class_in,
value_unit=unit_in,
value_product=product_in,
value_severity=severity_in,
value_parent=parent_in,
value_effort=effort_in,
value_short=desc_short_in,
value_long=desc_long_in,
value_parent=parent_in,
value_child=child_in,
value_user=user_in,
value_assignee=assignee_in,
value_date=date_in
)
)
dbSendQuery(conn,query_createissue)
poolReturn(conn)
}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"]){
user(input$username)
loggedIn(TRUE)
print(paste("- User:", user(), "logged in"))
# set reactive event "login" to TRUE
return(TRUE)
}
}
# otherwise leave reactive event "login" as FALSE
return(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)
create_user(new_user,new_password,new_hash)
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:
- customizing the UI with some templates or custom CSS
- production ready functionality such as issue-control (active/inactive)
- improved admin/user-management (password reset, overview etc.)
- visual tracking of issues via charting
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