base.py

This file defines foundational SQLAlchemy ORM base classes and an auditing mixin. It provides common attributes like creation and update timestamps, along with user tracking, ensuring consistent data modeling and integrity across all application entities.

Scope

• Defines common attributes for all ORM models.

• Provides auditing fields for tracking data changes.

• Establishes a base class for persistent entities.

• Ensures consistency in model structure across the application.

Imports

• datetime from datetime

  This import provides the datetime object, essential for setting default values and onupdate triggers for timestamp columns, ensuring accurate record creation and modification tracking.

• Column, DateTime, Integer, func, event from sqlalchemy

  This import brings core SQLAlchemy components like Column, DateTime, Integer, func, and event, which are fundamental for defining database schema and ORM model attributes.

• declarative_base, declared_attr from sqlalchemy.orm

  This import provides declarative_base and declared_attr, crucial for defining ORM models declaratively and dynamically injecting column definitions into inheriting classes.

• Base from app.core.database

  This import brings the application's configured SQLAlchemy declarative Base class, serving as the foundational parent for all ORM models within the application's data layer.

Variables

• __abstract__

  This attribute marks BaseModel as an abstract base class, preventing direct instantiation and ensuring that it serves solely as a template for other concrete ORM models to inherit common functionalities.

Classes

AuditMixin

Provides a reusable set of common audit fields for SQLAlchemy models. This mixin automatically adds columns like creation and update timestamps, and user tracking, ensuring consistent data integrity and historical record-keeping across various database entities.

Properties

Name	Type	Visibility	Description
date_added	DateTime	public	Represents the exact timestamp when a record was initially created in the database. Automatically populated upon insertion, this field provides crucial historical context for data auditing and lifecycle tracking within the application.
date_updated	DateTime	public	Stores the timestamp of the last modification made to a record. Automatically updated on each change, this field is essential for tracking data evolution, ensuring data freshness, and supporting audit trails effectively.
added_by	Integer	public	References the identifier of the user who initially created the record. This nullable field provides accountability and traceability, linking data creation events to specific users for auditing and security compliance purposes.
updated_by	Integer	public	References the identifier of the user who last modified the record. This nullable field ensures traceability of changes, providing crucial information for auditing, debugging, and maintaining data integrity across system operations.
deleted_at	DateTime	public	Records the timestamp when a record was soft-deleted, rather than permanently removed from the database. This nullable field supports soft-deletion strategies, preserving historical data for auditing and potential recovery operations.

Methods

date_added

public

This `declared_attr` defines a `DateTime` column named `date_added` for SQLAlchemy models. It automatically sets the current UTC timestamp upon record creation, ensuring consistent tracking of when an entity was added to the database.

=== " "

Activity DiagramMethod DiagramSequence Diagram

Syntax

def date_added(cls):

Input Parameters

• cls (type)

  The cls parameter represents the class to which this declared_attr is being applied. It provides the context for defining the column within the SQLAlchemy declarative base, enabling dynamic column creation for various models.

Output / Return Values

• date_added_column (Column)

  This method returns a SQLAlchemy Column object configured for DateTime type. It includes a default value of datetime.utcnow and is marked as non-nullable, ensuring every record has a creation timestamp automatically populated upon insertion.

Logic / Execution Flow

• Return a SQLAlchemy Column object.
• The Column is configured as a DateTime type.
• The Column has a default value set to datetime.utcnow, which provides the current UTC time upon record creation.
• The Column is marked as nullable=False, ensuring that a value must always be present for this field.

Usage Tips

• Utilise this date_added declared_attr within your SQLAlchemy declarative models to automatically include a creation timestamp. This ensures every new record consistently captures its insertion time without manual intervention.

Additional Notes

• The datetime.utcnow function is assigned directly to default, meaning it will be called only once when the class is defined. For dynamic default values at instance creation, ensure default=datetime.utcnow is used without parentheses.

• Using declared_attr allows this column definition to be reused across multiple SQLAlchemy models, promoting DRY principles. It dynamically creates the Column instance for each inheriting class, simplifying schema management and consistency.

date_updated

public

This `declared_attr` defines a `DateTime` column for SQLAlchemy models, automatically managing creation and update timestamps. It ensures data integrity by recording when records were last modified, crucial for auditing and versioning.

=== " "

Activity DiagramMethod DiagramSequence Diagram

Syntax

def date_updated(cls):

Input Parameters

• cls (Type[Any])

  The cls parameter represents the class itself, allowing the declared_attr to dynamically define a Column attribute on the SQLAlchemy model. It provides the necessary context for column creation.

Output / Return Values

• column_definition (Column[DateTime])

  Returns a SQLAlchemy Column object configured as DateTime, with default and onupdate set to datetime.utcnow. This column automatically tracks the last modification time for database records.

Logic / Execution Flow

• Return a SQLAlchemy Column object configured for DateTime type.
• Set the default value for the column to the current UTC datetime using datetime.utcnow.
• Set the onupdate value for the column to datetime.utcnow, ensuring it updates on record modification.
• Mark the column as nullable=False, meaning it cannot contain NULL values.

Usage Tips

• This declared_attr ensures automatic timestamp management for record updates. Apply it to SQLAlchemy models to consistently track modification times, simplifying data integrity and auditing processes across your application.

• Use datetime.utcnow for default and onupdate to maintain timezone-agnostic timestamps. This prevents issues with local time differences, ensuring consistent and comparable modification records across diverse environments.

Additional Notes

• The declared_attr decorator makes this function behave like a class-level attribute, dynamically creating a Column instance for each inheriting model. This promotes reusability and reduces boilerplate code.

• While datetime.utcnow is common, consider timezone.utc with datetime.now(timezone.utc) for more explicit timezone handling in Python 3. This offers better clarity and future-proofing for timestamp generation.

added_by

public

This method defines a `Column` for SQLAlchemy models, representing the ID of the user who created a record. It ensures consistent `added_by` tracking across multiple model classes. This attribute is crucial for auditing and understanding data provenance within the application.

=== " "

Activity DiagramMethod DiagramSequence Diagram

Syntax

def added_by(cls):

Input Parameters

• cls (type)

  The cls parameter represents the class to which this attribute is being added. It is implicitly passed by the @declared_attr decorator, allowing the column definition to be dynamically associated with the specific SQLAlchemy model class.

Output / Return Values

• column_definition (Column)

  This method returns a SQLAlchemy Column object configured as an Integer type. The column is named added_by and is nullable, allowing for records without an associated creator. It integrates seamlessly into the model's table definition.

Logic / Execution Flow

• Return a SQLAlchemy Column object configured as an Integer type, named added_by, and set to be nullable.

Usage Tips

• Utilise this declared_attr to automatically include an added_by column in all relevant SQLAlchemy models. This standardises user tracking for creation events, simplifying auditing and data management across your application's entities effectively.

Additional Notes

• The @declared_attr decorator is essential for defining attributes that depend on the class itself, like dynamically named columns or relationships. It ensures the Column is correctly instantiated for each inheriting model, providing flexible schema generation.

updated_by

public

This `declared_attr` method dynamically creates a SQLAlchemy `Column` for tracking the last updater's ID. It integrates into ORM models, providing an automatic `updated_by` field for auditing purposes within the application.

=== " "

Activity DiagramMethod DiagramSequence Diagram

Syntax

@declared_attr
def updated_by(cls):
        return Column(Integer, nullable=True)

Input Parameters

• cls (type)

  Represents the class to which this attribute is being added by SQLAlchemy's ORM. It allows dynamic column definition based on the specific model class context. This parameter is automatically provided by the decorator.

Output / Return Values

• updated_by_column (Column)

  Returns a SQLAlchemy Column instance configured as an Integer and nullable. This column stores the ID of the user who last modified the record, facilitating audit trails.

Logic / Execution Flow

• The updated_by method is invoked by the @declared_attr decorator, receiving the class cls as an argument.
• It constructs and returns a SQLAlchemy Column object.
• This Column is defined as an Integer type.
• The Column is configured to be nullable=True, meaning it can store None values.

Usage Tips

• Utilize this updated_by column in your SQLAlchemy models to automatically track user modifications. Ensure your application logic correctly populates this field upon record updates for comprehensive auditing.

• Combine updated_by with updated_at fields to create a robust audit trail for all model changes. This provides valuable historical context for data integrity and debugging.

Additional Notes

• The declared_attr decorator ensures this column is dynamically added to each inheriting model, preventing boilerplate code. It evaluates once per class, not per instance, optimising performance.

• While nullable=True is set, consider implementing application-level logic to always populate updated_by for non-initial updates. This ensures data consistency and accountability for changes.

deleted_at

public

This method defines a `Column` for tracking soft deletion timestamps in SQLAlchemy models. It provides a consistent `deleted_at` attribute across various entities, enabling logical record removal without physical data loss.

=== " "

Activity DiagramMethod DiagramSequence Diagram

Syntax

def deleted_at(cls):

Input Parameters

• cls (Any)

  This parameter represents the class to which the deleted_at column will be added. It is automatically passed by the @declared_attr decorator, allowing dynamic column definition for different SQLAlchemy models.

Output / Return Values

• deleted_at_column (Column)

  This output is a SQLAlchemy Column instance, specifically configured as DateTime and nullable=True. It represents the timestamp when a record was logically deleted, facilitating soft deletion strategies within the application.

Logic / Execution Flow

• Return a Column object configured for DateTime type.
• The column is set to be nullable=True, allowing NULL values for records that have not been soft-deleted.

Usage Tips

• Integrate this deleted_at column into your SQLAlchemy models for implementing soft deletion. This approach preserves data integrity and allows for easy recovery of logically deleted records, improving data management flexibility.

Additional Notes

• The deleted_at column is typically used in conjunction with query filters to exclude soft-deleted records from standard queries. This ensures that application logic only interacts with active data by default.

• While nullable=True is set, consider adding a default value or a trigger to automatically populate deleted_at upon deletion. This ensures consistent timestamping for all soft-deleted entries, simplifying data auditing.

BaseModel

Serves as an abstract base class for all ORM models, providing foundational structure and common functionalities. It integrates auditing capabilities through `AuditMixin` and database mapping via `Base`, ensuring consistent model behavior.

Inheritance

Extends: Base, AuditMixin

Properties

Name	Type	Visibility	Description
__abstract__	bool	public	This class-level attribute indicates that `BaseModel` is an abstract base class, not intended for direct instantiation. It serves as a template for concrete ORM models, providing shared schema definitions and behavioral contracts.

FAQs

Why is AuditMixin used instead of directly adding fields to BaseModel?

The AuditMixin is designed to centralize common auditing fields like creation/update timestamps and user identifiers. This promotes consistency across all models, reducing boilerplate and ensuring every entity automatically inherits these crucial tracking capabilities.

What is the purpose of __abstract__ = True in BaseModel?

The BaseModel is marked __abstract__ = True to prevent direct instantiation. It serves as a template, ensuring that all concrete models inherit its foundational ORM capabilities and auditing features, enforcing a consistent data structure.

How does declared_attr function within the AuditMixin?

The declared_attr decorator is used to dynamically define SQLAlchemy columns within the AuditMixin. This ensures that when AuditMixin is inherited, these columns are correctly added to the inheriting model's table definition.

Insights

Metric	Score	Level
Complexity	0.60	High Complexity
Security	0.60	Moderate Security
Performance	0.50	Needs Improvement

Complexity

Medium - Missing User Context Injection for Audit Fields

The added_by and updated_by fields are defined but lack an explicit, automated mechanism for population from user context, increasing manual effort and potential for errors.

Security

Medium - No Enforcement for Audit Field Population

While audit fields exist, there's no built-in mechanism or invariant to ensure added_by and updated_by are always populated, potentially leading to incomplete audit trails.