Setting Up Database Infrastructure: PostgreSQL & Redis

Hey guys! Let's dive into setting up the core of our system: the database infrastructure. This is where we'll be storing all the important stuff, from project details to equipment information. We're going to use PostgreSQL, a powerful and versatile database, and Redis, a speedy cache to make things even faster. This guide walks you through the whole process, from installing and configuring everything to making sure we have backups and can recover from any issues. So, let's get started and build a solid foundation for our application!

1. PostgreSQL Setup: Laying the Foundation

Alright, first things first: we need to get PostgreSQL up and running. Think of PostgreSQL as the backbone of our data storage. We'll be using version 15 or higher because it comes with some cool features and improvements. It’s like getting the latest gaming console for the best performance. The main goal here is to get PostgreSQL installed in our production environment.

Step-by-Step Guide for PostgreSQL Installation

1. Installation: We'll need to install PostgreSQL on our production environment. This usually involves using your system's package manager (like apt on Ubuntu or yum on CentOS). The exact commands will depend on your operating system, so make sure you consult the official PostgreSQL documentation for your specific setup. Imagine installing a really cool game; you need to find the right installer and run it.
2. Enabling Extensions: After installation, we need to enable some useful extensions for our project. We will focus on two specific PostgreSQL extensions: PostGIS and pgvector. PostGIS will help us handle geospatial data, which is useful for location-based analysis. pgvector, on the other hand, allows us to implement semantic search capabilities, making it easier to search for equipment based on meaning rather than just exact keywords. Think of it like adding extra features to your game that allow you to explore more areas and find hidden treasures. To enable these, you'll need to run SQL commands like CREATE EXTENSION postgis; and CREATE EXTENSION pgvector; within your PostgreSQL database.
3. User and Permissions Configuration: Now, security is key! We will need to set up users and permissions to control access to our database. This is like creating user profiles in your game so you can have different access levels. We'll create a dedicated user for our application and grant it the necessary permissions to read and write data. This helps protect our data from unauthorized access.
4. Initial Schema Application: We'll be using Alembic, a migration tool, to manage our database schema. We'll apply the initial schema defined in haas/alembic/. This is like loading the initial game world, which sets up all the tables, relationships, and other database elements. This is the foundation upon which all the data will be stored.
5. Connectivity Testing: Once the schema is applied, we'll test the connection between our application and the database. We will use the models defined in haas/app/database/models.py to ensure that our application can successfully communicate with the database. This is like testing that your game can connect to the server so that you can play online.

By following these steps, we'll have a robust and secure PostgreSQL setup ready to store and manage our data.

2. Redis Setup: Boosting Performance with Caching

Now, let's turbocharge our system with Redis! Redis is an in-memory data store that acts as a cache, speeding up data retrieval. It’s like adding a supercharger to our database, making things lightning-fast. The main objective here is to install and configure Redis 7+ for our cache layer.

Step-by-Step Guide for Redis Configuration

1. Installation: We need to install Redis on our production environment. Similar to PostgreSQL, you can use your system's package manager to install Redis. It’s a very straightforward process and usually involves a single command.
2. TTL Policies Configuration: We'll configure Time-To-Live (TTL) policies for our cached data. TTLs determine how long data stays in the cache before it expires and is refreshed from the database. This is like setting how long your power-ups last in a game. For example, we might set a TTL of 1 hour for tariff data and 24 hours for INMETRO certificates. This is to ensure that the data is refreshed at appropriate intervals, depending on how often it changes.
3. Cache Decorators Implementation: We'll implement cache decorators in haas/app/services/. These decorators will automatically cache the results of frequently used functions. This reduces the number of database queries, as the results are retrieved from the cache whenever possible. Think of these decorators as quick save points in your game. You don't have to replay the same level repeatedly. They make sure we are not repeatedly querying the database for the same data.
4. Cache Invalidation Testing: We'll test the cache invalidation to make sure that the cache is correctly updated when the underlying data changes. It's like ensuring your game updates your high score correctly when you get a new one. This ensures that the cache always holds the most up-to-date information.

With Redis in place, we will significantly reduce the load on our database and improve the overall performance of our application.

3. Database Migrations and Schema Management

Now, let's talk about managing our database schema using Alembic. This is like version control for our database. This task involves reviewing all the database models, generating migrations, applying them, and validating referential integrity.

Steps for Database Migrations

1. Model Review: First, we'll review all the models defined in haas/app/database/models.py. It's like checking the features of all the characters and items in your game to make sure everything is perfect.
2. Initial Migrations Generation: We'll generate the initial migrations using Alembic. This is done by running the command `alembic revision --autogenerate -m