Make DB fresh on each test run

[kamilsi]

We need to modify our Continuous Integration (CI) setup to ensure that the database is in a fresh, consistent state for each test run. Currently, the database retains data after each test, which can lead to inconsistent testing environments. Additionally, it's important to have a predefined set of test data in the database for testing purposes.

Challenges and Considerations:

• R packages face limitations in sourcing multi-transaction SQL files.
• We need a reliable way to reset the database state and populate it with test data before each test run.

Possible Solutions:

• R-Based Initialization Script: Develop a script in R to reset the database and populate it with necessary test data. This script should be executed as part of the CI pipeline before each test run.
• Test Data Management: Identify the essential test data needed and implement a method to efficiently insert this data into the database during initialization.

Acceptance Criteria

• Database Reset: The database should be reset to a clean state before each test run in the CI environment.
• Test Data Population: After resetting, the database should be populated with a predefined set of test data.
• Documentation: Update any relevant documentation to include this new database initialization process and its role in the CI pipeline.

[kamilsi]

Closed in #42