PostgreSQL Database Usage

Relevant source files

The following files were used as context for generating this wiki page:

• core/database.py
• core/bot.py
• core/commands.py
• tests/test_commands.py
• requirements.txt

The GentelmeN-CorE project utilizes a PostgreSQL database to manage persistent user configurations and real-time cryptocurrency alerts. The database integration is handled via the psycopg2 library, which allows the bot to store user preferences such as preferred AI models and interface languages, as well as tracking price thresholds for automated notifications.

The database system is designed with a fail-safe mechanism where the bot can fall back to default values or in-memory caching if the DATABASE_URL environment variable is missing or if the connection fails. This ensures high availability for core chat functionalities even during database downtime.
Sources: core/database.py:5-8, core/database.py:46-48, requirements.txt:5

Database Architecture and Schema

The system initializes two primary tables upon startup to handle the project's data requirements: users and alerts. The initialization logic is contained within init_db(), which ensures the schema exists before the bot begins polling for messages.

Data Schema

The following ER diagram represents the relational structure of the PostgreSQL database:

The database maintains user settings and a one-to-many relationship with price alerts.
Sources: core/database.py:11-35

Table Definitions

Table	Column	Type	Description
users	user_id	BIGINT	Primary key; unique Telegram user ID.
	username	TEXT	The Telegram handle of the user.
	language	TEXT	User's preferred language (Default: 'EN').
	model_name	TEXT	Preferred AI model (Default: 'llama-3.3-70b-versatile').
	joined_at	TIMESTAMP	Auto-generated timestamp of user registration.
alerts	id	SERIAL	Primary key; auto-incrementing alert ID.
	user_id	BIGINT	ID of the user who set the alert.
	symbol	TEXT	Crypto pair (e.g., BTC/USDT).
	target_price	NUMERIC	The price threshold to trigger the alert.
	direction	TEXT	Direction of trigger ('UP' or 'DOWN').

Sources: core/database.py:16-35

User Preferences Management

User data is managed through a combination of database persistence and local caching to optimize performance. Local dictionaries user_prefs and user_models act as a first-tier cache to reduce the number of queries to the PostgreSQL instance.

Data Flow for User Settings

When a user interacts with the bot, the system checks for existing preferences. If not found in the cache, it queries the database.

The system prioritizes local cache and falls back to PostgreSQL, then hardcoded defaults.
Sources: core/database.py:38-59, tests/test_commands.py:84-118

Key Functions

• get_user_data(user_id): Retrieves the language and model settings. It checks local cache before attempting a database connection.
• update_user_db(user_id, username, lang, model): Performs an "UPSERT" operation. It inserts a new record or updates existing fields (ON CONFLICT (user_id) DO UPDATE).
  Sources: core/database.py:38-84

Cryptocurrency Alert System

The database is integral to the asynchronous price monitoring system. It stores user-defined price targets which are continuously polled by a background thread.

Alert Life Cycle

1. Creation: Admin users trigger the /alert command. The command calculates the direction (UP/DOWN) based on the current price and saves the record to the alerts table.
2. Monitoring: The price_monitor thread in core/bot.py fetches all active alerts every 60 seconds.
3. Triggering: If the current price from the KuCoin API meets the target_price condition, the bot notifies the user.
4. Deletion: Once triggered, the alert is removed from the PostgreSQL database to prevent duplicate notifications.

Asynchronous monitoring flow for price alerts stored in the database.
Sources: core/commands.py:53-76, core/bot.py:39-82

Configuration and Integration

The database connection is established using the DATABASE_URL environment variable. The project is configured for deployment on Railway, as indicated by the presence of railway.json.

Connection Parameters

The project uses psycopg2 with the following implementation details:

• Connection: established via psycopg2.connect(DB_URL).
• Transaction Management: Uses Python context managers (with) to ensure connections and cursors are closed properly.
• Binary Driver: The requirements.txt specifies psycopg2-binary, which is recommended for most deployment environments to avoid local compilation of C libraries.
  Sources: core/database.py:5-15, requirements.txt:5, railway.json:1-12

Testing and Validation

The database logic is validated using pytest. Mocking is used to simulate PostgreSQL responses and verify that the application correctly handles scenarios such as:

• Missing DATABASE_URL (graceful degradation).
• Connection failures (logging warnings instead of crashing).
• Successful data retrieval and cache updates.
  Sources: tests/test_commands.py:46-135

Conclusion

PostgreSQL serves as the persistent backbone of the GentelmeN-CorE bot, managing both static user configurations and dynamic event-driven data like price alerts. By combining SQL persistence with in-memory caching and a background monitoring thread, the system maintains a balance between performance and reliability.