Database Schema & Management

Relevant source files

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

• models_sqlalchemy.py
• database.py
• infrastructure/database_ecosystem.py
• scripts/database/init_database.py
• POSTGRESQL_SETUP.md
• QUICK_REFERENCE.md
• DOCKER_MCP_DEPLOYMENT.md

The DevSkyy platform utilizes a robust and scalable database system built on SQLAlchemy, providing an asynchronous, ORM-based interface for data persistence. This system is designed to support a variety of SQL databases, with a primary focus on PostgreSQL for production environments and SQLite for development. The schema is structured to handle core e-commerce operations, user management, and the logging and state management for the platform's 54+ AI agents.

The database architecture is centered around a DatabaseManager class that handles connections, sessions, and health checks. Data models are defined using SQLAlchemy's Declarative Base, allowing for a clean, Python-native representation of the database schema. Management scripts are provided for easy initialization and population of the database.

Architecture & Connection Management

The core of the database interaction is managed by the DatabaseManager class, which provides a centralized point for handling database connections and sessions. It supports multiple database backends through a configurable DATABASE_URL.

Sources: database.py, POSTGRESQL_SETUP.md

Connection Manager

The DatabaseManager class in database.py is responsible for:

• Initializing the async database engine (create_async_engine).
• Providing an asynchronous session scope (async_session_scope) for safe, transactional database operations.
• Performing health checks to verify database connectivity.

The following diagram illustrates the application's process for obtaining a database session.

This diagram shows the flow of an application component requesting a database session from the DatabaseManager, which provides it from a connection pool for the duration of the operation.
Sources: database.py:27-41

Multi-Database Support

The system is configured to work with multiple SQL databases, determined by the DATABASE_URL environment variable. The primary supported databases are PostgreSQL (for production) and SQLite (for development).

Database	Example DATABASE_URL	Driver
PostgreSQL	postgresql+asyncpg://user:pass@host:5432/dbname	asyncpg
SQLite	sqlite+aiosqlite:///./devskyy.db	aiosqlite

Sources: POSTGRESQL_SETUP.md, ENV_SETUP_GUIDE.md

Connection Pooling

For production environments using PostgreSQL, the database engine is configured with connection pooling to enhance performance and manage resources efficiently.

Parameter	Default Value	Description
pool_size	5	The number of persistent connections to keep in the pool.
max_overflow	10	The number of additional connections that can be opened when the pool is exhausted.
pool_timeout	30	The number of seconds to wait before giving up on getting a connection from the pool.
pool_recycle	3600	The number of seconds after which a connection is recycled (re-established).
pool_pre_ping	True	Verifies connections are alive before they are checked out from the pool.
Sources: POSTGRESQL_SETUP.md

Database Schema

The database schema is defined in models_sqlalchemy.py using SQLAlchemy's declarative models. It includes tables for users, products, customers, orders, and other entities essential for the e-commerce and AI agent functionalities.

Entity Relationship Diagram

The diagram below shows the relationships between the main tables in the devskyy database.

Core Tables

users Table

Stores user account information for authentication and authorization.

Column	Type	Constraints	Description
id	INTEGER	Primary Key, Indexed	Unique identifier for the user.
email	VARCHAR(255)	Not Null, Unique, Indexed	User's email address.
username	VARCHAR(100)	Not Null, Unique, Indexed	User's unique username.
full_name	VARCHAR(200)	Nullable	User's full name.
hashed_password	VARCHAR(255)	Not Null	Hashed password for the user.
is_active	BOOLEAN	Default: True	Whether the user account is active.
is_superuser	BOOLEAN	Default: False	Whether the user has superuser privileges.
created_at	TIMESTAMP	Default: NOW()	Timestamp of user creation.
updated_at	TIMESTAMP	Default: NOW()	Timestamp of last user update.
Sources: models_sqlalchemy.py:10-23, POSTGRESQL_SETUP.md

products Table

Contains information about products in the e-commerce catalog.

Column	Type	Constraints	Description
id	INTEGER	Primary Key, Indexed	Unique identifier for the product.
name	VARCHAR	Indexed	Name of the product.
description	TEXT	Nullable	Detailed description of the product.
price	FLOAT	Not Null	Price of the product.
stock	INTEGER	Not Null	Current stock quantity.
Sources: models_sqlalchemy.py:26-33

customers Table

Manages customer profiles and preferences.

Column	Type	Constraints	Description
id	INTEGER	Primary Key, Indexed	Unique identifier for the customer.
name	VARCHAR	Nullable	Customer's name.
email	VARCHAR	Unique, Indexed	Customer's email address.
preferences	JSON	Nullable	Customer preferences stored as JSON.
Sources: models_sqlalchemy.py:44-50

orders Table

Tracks customer orders and their status.

Column	Type	Constraints	Description
id	INTEGER	Primary Key, Indexed	Unique identifier for the order.
customer_id	INTEGER	Foreign Key (customers.id)	The customer who placed the order.
order_date	DATETIME	Default: datetime.utcnow	The date and time the order was placed.
status	VARCHAR	Default: 'pending'	The current status of the order.
Sources: models_sqlalchemy.py:35-42

Database Initialization and Management

The repository includes scripts and tools for setting up and managing the database.

Initialization Script

The scripts/database/init_database.py script provides a command-line utility to initialize the database. It can create all tables based on the SQLAlchemy models and optionally populate them with sample data.

The initialization process follows these steps:

Service Management

For PostgreSQL deployments, standard brew services (on macOS) or systemd (on Linux) can be used to manage the database service. The DOCKER_MCP_DEPLOYMENT.md file also details running PostgreSQL as a Docker container.

Example psql commands:

• \dt: List all tables in the current database.
• \d users: Describe the schema of the users table.
• SELECT * FROM users;: Query data from the users table.

Sources: POSTGRESQL_SETUP.md, DOCKER_MCP_DEPLOYMENT.md

Conclusion

The DevSkyy database system is a well-structured, asynchronous, and scalable component of the platform. It uses SQLAlchemy for robust ORM capabilities and supports multiple database backends, with a production focus on PostgreSQL. The schema is designed to support complex e-commerce and AI agent operations, and the provided management scripts simplify development and deployment workflows. The use of connection pooling and async drivers ensures high performance under load.