feat: backup db content locally and copy sql file to s3 bucket

[blenzi]

This PR adds a script and adapts the code to be able to backup db content and upload it to s3 bucket

• add scripts/dump_db.sh
• s3.py S3Bucket.upload_file: support for ascii files
• add python-dotenv dependency and load .env from root directory to be able to use env variables outside docker compose
• docker-compose.yml: add volume db_backup to frontend service

[ghost]

Nice of you to open a PR :pray: When you're ready and want to get it reviewed, post a comment in this Pull Request with this message: /quack review

[codecov[bot]]

Codecov Report

All modified and coverable lines are covered by tests :white_check_mark:

Project coverage is 100.00%. Comparing base (c10ff1c) to head (e825116). Report is 76 commits behind head on main.

Additional details and impacted files

@@             Coverage Diff             @@
##             main      #271      +/-   ##
===========================================
+ Coverage   95.00%   100.00%   +4.99%     
===========================================
  Files          60         3      -57     
  Lines        1421        74    -1347     
===========================================
- Hits         1350        74    -1276     
+ Misses         71         0      -71     

Flag	Coverage Δ
client	100.00% <ø> (ø)
unittests	?

Flags with carried forward coverage won't be shown. Click here to find out more.

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

[blenzi]

/quack review

[frgfm]

Great idea, we definitely need some backup! One suggestion though: to avoid mixing DB backup & images in the same bucket, it might be worth setting a simple cron job instead?

What I have in mind would be the CRON job doing in order:

• dumping the sql DB from outside the postgres docker into a data/binded volume
• using the backend docker to upload the dump from the volume (similar to the design here) That way we don't need to add any dependencies on root (otherwise, if we're OK with that, we can do everything without interacting with the backend container).

What do you think?

[frgfm]

Closing this as this was built on the v1, but giving it additional thoughts:

• we can't have a full-featured and easily maintanable/orchestrable DB backup system. So here for instance, we'd be storing backups that we usually restore manually which raise the question whether we'd use it actually
• if we are in need of backup, I'd suggest just having the DB remote on Supabase for instance (they handle backup, etc.)
• if we want something minimal, I think we should only focus on restoring critical tables: organizations, users, cameras which handle access. The rest is basically historical data

Ideally, we'd want a safeguard to do:

make backup-access
# pull/upgrade API version, etc
make restore-access