Implement database

[dhornbein]

Currently, the network runs off a Google Spreadsheet.

A new database needs to be implemented. SQLite seems like a good option. Database currently needs to run on Vercel hobby deployment.

If the database can be accessed and manipulated through a GUI that would be a big plus, but not required.

Here's the current Database schema:

Transactions
- uid
- created # datetime
- updated # datetime
- payeeUid
- recipientUid
- amount
- fee # amount * 0.02 (2% representing the 1% fee on both sides of the transaction)
- title
- description
- effectiveDatetime # datetime, default now, gives members the ability to have a transaction land at a specific time
- brokerUid
- ipAddress
- transactionPhone

Members
- uid
- username # unique
- firstName
- lastName
- creditLimit
- balance # credit - debit
- credit # sum of received transactions * 0.99 (fee)
- debit # sum of sent transactions * 1.01 (fee)
- transactionTotal # sum of all transactions
- brokerUid # list of uid of people who are authorized to broker for member
- invitedByUid # uid of the person who invited member
- phone
- email
- created
- updated
- password
- roles
- invites # number of sent invitations claimed, or an array of invite ids

Invitations
- id 
- created # datetime
- firstName
- lastName
- email
- phone
- favor # in the future we might want to allow people to invite folks and give them favor
- invitedByUid
- revoked # boolean, allows invitations to be revoked
- claimed # UID of new user who claims invitation
- token # JWT token

Waitlist
- id
- created # datetime
- firstName
- lastName
- email
- phone

This is just my initial design, so I'm open to adjustments and better practices. I'm no database designer!

There are some more details about the database design in the API Schema documentation

[ultralightdm]

SQLite is a good option. This is in my wheelhouse if you'd like me to take it on.

[dhornbein]

Oh very cool @ultralightdm , I was going to invite Dan to take it on but he hasn't joined yet. I'm sure the two of you could work on it together. Please assign yourself to the issue and go wild.

Most of what you'll need to do is replace the /api/model code, and probably some logic in the /api/controlers (where I implemented some search functions that would be better handled by the database.

[ultralightdm]

Cool. Just to be clear, I am Dan, not Nate.

[ultralightdm]

We can't run a database directly on a "serverless" deployment like Vercel, but we can write code to connect to a separate database server, which will have to be hosted somewhere, probably costing dollars. We might be able to use Deacon's existing Bluehost account. I also have a cloud server that we could use without any marginal cost. For the time being I'm going to get a SQL model working and we can determine the correct configuration when we go live.

[dhornbein]

@ultralightdm I think running off a cloud server SQL will work for now.

Our users will be mainly in the US so the lag that distributed serverless functions hitting a location-based server will be minimal. (e.g. someone in South Africa will hit the serverless function which will then hit the US based server which will be slower if the function was run on the same US server here's an interesting video on the subject)