Database Schema & Models

Relevant source files

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

• README.md
• backend/app/models/base.py
• backend/app/models/user.py
• backend/app/db/session.py
• backend/app/db/base_class.py

Introduction

The database layer of the Dev-Template project is designed to provide a robust, scalable, and type-safe interface for data persistence. It utilizes an Object-Relational Mapping (ORM) approach to bridge the gap between Pythonic data structures and the underlying relational database management system.

The primary goal of the "Database Schema & Models" module is to define the structure of the application's data, manage database connections, and provide a consistent base for all entity models. This ensures data integrity and simplifies the process of performing CRUD (Create, Read, Update, Delete) operations throughout the application.

Sources: backend/app/models/base.py, backend/app/db/base_class.py

Architecture and Core Components

The database architecture is built around SQLAlchemy, providing a Declarative Base that all models inherit from. This structure allows for automated table creation and standardized metadata management.

Base Class Configuration

The Base class serves as the foundation for all database models. It automatically generates table names based on the class name if a __tablename__ is not explicitly provided, ensuring a consistent naming convention across the schema.

@as_declarative()
class Base:
    id: Any
    __name__: str
    # Generate __tablename__ automatically
    @declared_attr
    def __tablename__(cls) -> str:
        return cls.__name__.lower()

Sources: backend/app/db/base_class.py:7-15

Session Management

Database connectivity is managed through a session factory that handles connection pooling and transaction lifecycle. The application uses a scoped session to ensure thread safety and efficient resource utilization.

The diagram above illustrates the lifecycle of a database session within the application context.
Sources: backend/app/db/session.py

Data Models

The project defines specific entities as Python classes that map directly to database tables.

User Model

The User model represents the primary identity entity within the system. It contains fields for authentication, profile information, and account status.

Field	Type	Constraints	Description
id	Integer	Primary Key	Unique identifier for the user
full_name	String	Indexable	The user's displayed name
email	String	Unique, Index, Non-nullable	Primary contact and login identifier
hashed_password	String	Non-nullable	Securely stored credential
is_active	Boolean	Default: True	Account status flag
is_superuser	Boolean	Default: False	Administrative privilege flag

Sources: backend/app/models/user.py:6-14

Relationships and Schema Structure

The schema utilizes foreign keys and ORM relationships to link different data entities.

This ER diagram shows the relationship between the base declaration and the specific User implementation.
Sources: backend/app/models/user.py, backend/app/db/base_class.py

Database Initialization Flow

When the application starts or migrations are run, the following sequence occurs to ensure the schema is correctly applied to the target database.

Conclusion

The database schema and models in Dev-Template provide a structured and maintainable approach to data management. By leveraging SQLAlchemy's declarative system and a centralized session management pattern, the project ensures that data entities are well-defined and database interactions are handled efficiently. This foundation supports future scalability and simplifies the integration of additional complex business entities.