🐢Implement PostgreSQL Database for Capturing Useful Statistics and Anonymous User Data Using Tortoise-ORM

[Salz0]

Issue: Implement PostgreSQL Database for Capturing Useful Statistics and Anonymous User Data Using Tortoise-ORM

Problem Description

At present, the bot doesn't keep track of useful statistics and user data that could be crucial for improving user experience and analyzing usage patterns. We would like to capture and store this data in a PostgreSQL database.

Objective 🎯

The aim is to implement a PostgreSQL database using Tortoise-ORM to store statistics and user metrics, while ensuring that all data is anonymized for user privacy.

Suggested Changes

You can divide these into separate PullRequests, to make the task easier:

Implement basic data saving:

• Use Tortoise-ORM to create database models that align with the metrics and data points you identified.
• Implement data capture and storage in the database within the bot's existing command handlers and states.
• Make sure all stored data is anonymized and not traceable to individual users.

Suggestions on what to save:

• [x] the Triggering of specific commands with datetime
• [ ] Average interaction length.
• [x] Interface language of users

Do something with the data:

• [ ] Create a module to analyze the data in general
• [ ] Analyze the most popular time of the day and day of the week using a graph (matplotlib)
• [x] Use a barplot to visualize Interface languages of users

❕ Important: this functionality should be optional, as the usage of a sophisticated database and statistical analysis can be unnecessary for some projects.

Code Snippet

Below is a hypothetical Python code snippet that demonstrates how you might use Tortoise-ORM to interact with a PostgreSQL database:

from tortoise import Tortoise, fields
from tortoise.models import Model

class UserStatistics(Model):
    id = fields.IntField(pk=True)
    command_used = fields.CharField(max_length=50)
    session_length = fields.FloatField()
    # any other fields

Tortoise.init(
    db_url='postgres://username:password@localhost:5432/mydatabase',
    modules={'models': ['your_project.models']}
)

async def track_start_command(user_id: int):
    await UserStatistics.create(command_used='start', session_length=0.0)
    # other logic

Acceptance Criteria

1. Data is properly captured and stored in a PostgreSQL database.
2. All stored data is anonymized.
3. The database setup should be modular and easily extendable for capturing more metrics in the future.
4. Testing
5. Add unit tests to verify that the database is capturing and storing the data as expected.
6. Manually review entries to ensure that they are anonymized and not traceable to individual users.
7. Please feel free to contribute by making a Pull Request to address this issue. Thank you! 📈🔒

Note: Make sure to adhere to privacy guidelines and policies such as GDPR when implementing this feature.

You can divide this task by creating multiple pull requests! No need to do all this work at once ♥

[BloodyOcean]

I'll take care of data analysis.