Working with a non profit organization to build an internal monitoring system
Tags: node, postgres, heroku, javascript
Published: 2021-05-28 01:34:31

### Developing a full stack application for Family Promise from the back end perspective

~     

### Creating a New System
I spent the entire month of May 2021 working on an internal monitoring and evaluation system for the non-profit organization [Family Promise](https://familypromise.org/). Family Promise works with homeless and low income families to help them achieve sustainable independence. They provide various prevention programs to help keep low income families from becoming homeless, as well as sheltering programs for the homeless, and aftercare programs for those who get out of sheltering. These programs are great and I personally love what the organization is doing to help the communities who need it most. That is why I am excited that I had the opportunity to work all month with a team of skilled developers. We spent a lot of time collaborating on building out a new Internal Monitoring and Evaluation System for Family Promise. This team was comprised of Myself, and 7 other Developers. We all reported to a Team Project Lead (TPL) for a daily stand-up meeting to talk about our days work. We all worked great together and its amazing what things can be done when you put different brains together.

### Understanding the Back End
This application is meant to be an internal monitoring system for family promise. Our goal was to allow administrative staff at family promise to get useful metrics from the data they track within the organization. We also wanted this data to be tracked and entered by service workers in the field. For example, when a service worker goes to a recipients home to provide a service they can take various notes about the service, and record useful information like location, age, household income/size, and more, so we can get metrics later on.

So what? Why is this useful? Because Family Promise can use these metrics to track services that they offer to their recipients, and they can see helpful information about organizational spending on services. So what we wanted to build out is a large database that can account for many different types of metrics from the data provided by service workers. Unfortunately the original database did not have all the data necessary for proper long term metrics, so our solution was to extend the database wherever we could to account for the necessary data.

### Our Thought Process
When I work on a database the first thing I do is parse it into something like [DB Designer](https://www.dbdesigner.net/) or [DrawSQL](https://drawsql.app/). This helps me identify the missing data in the database. 

![Original Database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ihrshim3pu6pv8kf7k0k.png)

I brought this to the rest of the team outlining the missing data, as well as presenting a new tentative database design. We knew we had to extend the database to account for a lot more data than we had. For example we knew we needed to track recipients, and the households they are in. This would be used in conjunction with locations and incomes to find out how Family Promise as an organization is spending money and providing services to clients. We knew a database rewrite would be costly in terms of time and we estimated that it would take us 3 to 4 days given that we would have to rewrite various model functions and routes to match with the new database tables. However, this did not turn out to be true because we found bugs in our model functions that we rewrote. Ultimately my team member Remy suggested we cascade our updates and deletes in our migration files to avoid the bugs we were running into.

![Cascade in the code](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vzi4u4tvo9tsu8wlwz0l.png)

This solved our bug for the time being but was not a permanent solution.

### Solving our Problems
We broke down our tasks on Trello. Trello allowed us to break down our tasks and assign them to each teammate. We also used Whimsical, a design tool, to mock up the front end. We worked amongst ourselves pair programming where necessary to make the new database design happen. I used DB Designer to iterate on the original database over the course of the month. Our final schema for the database, depicted here, shows a lot more of data missing from the original database

![Final Database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k8i0pkvwajc3miudci19.png)

While this schema is functional and has a lot of forethought for the future of this application, its not without its own problems. As I said earlier any update or delete is set to cascade through the whole database. In the long term, we probably would not want that on every table it was only used to bypass a bug. Also when creating the migrations I had to rename tables and columns to make them less vague. This included changing file names which was throwing an error on migration. I collaborated with my teammates Remy and Diego to try to troubleshoot this issue and after several hours I stumbled across a [stack overflow](https://stackoverflow.com/questions/37970743/postgresql-unique-violation-7-error-duplicate-key-value-violates-unique-const) post which had a perfect answer to our problem.

![Stack Overflow Answer](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ciyvoxauwq3q48y9qp2p.png)

Here we found out that when the seed files were created, they were created with a primary key. This was messing up the auto incrementing within Postgres and the indexes did not match. Our solution was to simply remove those primary key lines within the seed files. All worked well and our database was up and running.

### The Result
We came out with a database that was migrated and seeded. This was good because it now allows us to just simply build model functions on top of it. This new database design holds all the data we need and now just requires all the new models and routes to be built. Over the next week and a half after the database was updated we worked on building out more of the functionality and getting it all connected to the front end forms built by the rest of the team. We estimate that this complex database design will cause some issues down the road for future teams simply because the complexity of it makes the model functions harder to write. However, we did this in hopes of preventing future rewrites from happening after model functions were already built out. I documented it thoroughly and left the design plans for the next teams that take over after us.

![Whimsical plan the next team](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5a3n0d4nyiio0ggnre1b.png)

### Takeaways?
This has been a wonderful experience working with a team building out this system for Family Promise. I feel like this project has made me far more organized as a developer. I now understand the importance of effective communication and documentation. Without proper documents to pass on to future developers your code will not be as easily understood. I felt that there were times where certain bugs could have been avoided with just a little more communication. This point about ensuring good communication with future teams is a key take away for me. This month has taught me so much and I feel my database design skills have improved a lot since the start of my time in labs. I know that this new practice will help me in my future career as a developer. This project has further driven my passion for back end development and I am forever grateful to Family Promise and Lambda School Labs for having given me this opportunity. I can't wait to work on more projects and can't wait to see what interesting work I can do next!