Schema/API Design comments - Connor OBrien

[connorpobrien]

1. Include updated_at time stamps for all rows/tables to indicate when records were last modified.
2. Use more specific data types where possible. For example, VARCHAR with length limits can be used for fields like name and email to save space and improve data integrity.
3. Consider removing the ability to delete transactions. Instead, you could include a deleted_at column within the database. This would improve your ability to recover data and analyze historical trends.
4. Encrypt fields like 'email' to increase data security.
5. If you plan on implementing large scale analytics capabilities, consider creating a separate analytics schema that can be used for complex queries without affecting transaction performance.
6. Include versioning in the API path to improve transitions to future changes
7. Implement rate limiting to prevent abuse of your endpoints
8. Include functionality to allow for filtering/sorting in specific GET endpoints.
9. Add functionality for large bulk operations. This would reduce the number of API calls.
10. Include more standardization for structure of return statements from all types of endpoint calls.
11. Implement cascading deletes to correctly link together tables and save time/code when deleting or dropping tables.
12. Avoid using NULL default type for rows. Set a real default type as often as possible.

[jackalnom]

VARCHAR isn't great for postgres. Just use text. You can separately put constraints validation if you want.