ORM Models & Relationships

Relevant source files

Purpose and Scope

This document explains the SQLAlchemy 2.0 async ORM model layer and the entity-relationship structure that defines the system's data model. It covers:

Model Class Patterns: Base classes, type annotations with Mapped[], and async compatibility
Entity Relationships: Many-to-many junction tables (user_role, role_menu, role_data_scope, data_scope_rule)
Model Definitions: Core administrative models (User, Role, Menu, Dept) and plugin models
Field Patterns: Primary keys, logical foreign keys, timestamps, and custom column types

For database schema initialization and SQL scripts, see Database Schema & Initialization. For CRUD operations that query and manipulate these models, see CRUD Patterns & Service Layer. For hierarchical data retrieval and tree building, see Tree Structures & Hierarchical Data.

Base Model Classes

The system uses SQLAlchemy 2.0's declarative mapping with Mapped[] type annotations and dataclass-style initialization. All models inherit from base classes that provide common functionality.

Base Class

The Base class defined in backend/common/model.py12-33 serves as the declarative base for all models. It combines MappedAsDataclass and DeclarativeBase from SQLAlchemy 2.0 to provide:

Primary Key: id: Mapped[id_key] field with Snowflake ID generation backend/common/model.py20
Timestamps: created_time and updated_time with timezone awareness backend/common/model.py21-26
Dataclass Initialization: Enables Model(**kwargs) instantiation pattern
Async Compatibility: Works with SQLAlchemy 2.0 async sessions

Models inheriting from Base:

User backend/app/admin/model/user.py12 - sys_user table
Role backend/app/admin/model/role.py8 - sys_role table
Menu backend/app/admin/model/menu.py8 - sys_menu table
Dept backend/app/admin/model/dept.py8 - sys_dept table
DataScope backend/app/admin/model/data_scope.py8 - sys_data_scope table
DataRule backend/app/admin/model/data_rule.py8 - sys_data_rule table
LoginLog, OperaLog - Audit logging tables
UserSocial backend/plugin/oauth2/model/user_social.py8 - OAuth2 bindings
GenBusiness backend/plugin/code_generator/model/business.py8 - Code generator metadata

DataClassBase Class

The DataClassBase class backend/common/model.py36-40 extends Base for models requiring full dataclass functionality with explicit field initialization during construction. Used when all fields (including id) need to be specified at instantiation.

Models using DataClassBase:

GenColumn backend/plugin/code_generator/model/column.py8 - Column metadata with explicit IDs

Diagram: Model Class Hierarchy

Sources: backend/common/model.py:12-40, backend/app/admin/model/user.py:12, backend/app/admin/model/role.py:8, backend/app/admin/model/menu.py:8, backend/app/admin/model/dept.py:8, backend/app/admin/model/data_scope.py:8, backend/app/admin/model/data_rule.py:8, backend/plugin/oauth2/model/user_social.py:8, backend/plugin/code_generator/model/business.py:8, backend/plugin/code_generator/model/column.py:8

Core Admin Models

User Model

The User model backend/app/admin/model/user.py12-37 maps to the sys_user table. It declares __tablename__ = 'sys_user' and uses SQLAlchemy 2.0 Mapped[] type annotations for all fields.

Key Fields:

Field	Type	Constraints	Description
`id`	`id_key`	Primary key	Snowflake ID
`uuid`	`String(64)`	Unique	UUID4 string for external references
`username`	`String(64)`	Unique, Indexed	Login username
`nickname`	`String(64)`	Required	Display name
`password`	`String(256)`	Optional	Hashed password with bcrypt
`salt`	`LargeBinary(255)`	Optional	Password salt
`email`	`String(256)`	Unique, Indexed, Optional	Email address
`phone`	`String(11)`	Optional	Phone number
`avatar`	`String(256)`	Optional	Avatar URL
`status`	`int`	Default 1, Indexed	0=disabled, 1=active
`is_superuser`	`bool`	Default False	Superuser privileges
`is_staff`	`bool`	Default False	Backend admin access
`is_multi_login`	`bool`	Default False	Allow concurrent sessions
`join_time`	`datetime`	Auto-generated	Registration timestamp
`last_login_time`	`datetime`	Optional, Auto-updated	Last login timestamp
`dept_id`	`BigInteger`	Optional	Logical FK to department

Model Implementation Details:

uuid: Mapped[str] uses default_factory=uuid4_str for auto-generation backend/app/admin/model/user.py18
created_time and updated_time inherit from Base with TimeZone column type backend/app/admin/model/user.py30-33
password and salt are nullable (Mapped[str | None]) to support OAuth2-only users backend/app/admin/model/user.py22-23
dept_id: Mapped[int | None] is a logical foreign key without database constraint backend/app/admin/model/user.py36
Indexed fields: username, email, status for query optimization backend/app/admin/model/user.py19-26

Role Model

The Role model backend/app/admin/model/role.py8-18 maps to the sys_role table with __tablename__ = 'sys_role':

Field	Type	Constraints	Description
`id`	`id_key`	Primary key	Snowflake ID
`name`	`String(32)`	Unique	Role name
`status`	`int`	Default 1	0=disabled, 1=active
`is_filter_scopes`	`bool`	Default True	Enable data scope filtering
`remark`	`UniversalText`	Optional	Role description

Data Scope Filtering: The is_filter_scopes: Mapped[bool] field backend/app/admin/model/role.py16 controls whether data permission filtering is applied. When False, users with this role bypass data scope restrictions (typically for admin roles).

The Menu model backend/app/admin/model/menu.py8-30 maps to the sys_menu table with __tablename__ = 'sys_menu'. It implements a self-referential tree structure for hierarchical navigation and permissions:

Field	Type	Constraints	Description
`id`	`id_key`	Primary key	Snowflake ID
`title`	`String(64)`	Required	Menu display title
`name`	`String(64)`	Required	Menu name (for routing)
`path`	`String(200)`	Optional	Route path
`sort`	`int`	Default 0	Display order
`icon`	`String(128)`	Optional	Icon identifier
`type`	`int`	Default 0	0=directory, 1=menu, 2=button, 3=embedded, 4=external
`component`	`String(256)`	Optional	Frontend component path
`perms`	`String(128)`	Optional	Permission identifier
`status`	`int`	Default 1	0=disabled, 1=active
`display`	`int`	Default 1	0=hidden, 1=visible
`cache`	`int`	Default 1	0=no cache, 1=cache
`link`	`UniversalText`	Optional	External link URL
`remark`	`UniversalText`	Optional	Menu description
`parent_id`	`BigInteger`	Optional, Indexed	Parent menu ID for tree structure

Tree Structure: The parent_id: Mapped[int | None] field backend/app/admin/model/menu.py29 creates a self-referential relationship. Root menus have parent_id = None, child menus reference their parent's id. This enables unlimited nesting depth for navigation hierarchies. See Tree Structures & Hierarchical Data for tree building utilities.

Dept Model

The Dept model backend/app/admin/model/dept.py8-24 maps to the sys_dept table with __tablename__ = 'sys_dept'. Like Menu, it uses a self-referential tree for organizational hierarchies:

Field	Type	Constraints	Description
`id`	`id_key`	Primary key	Snowflake ID
`name`	`String(64)`	Required	Department name
`sort`	`int`	Default 0	Display order
`leader`	`String(32)`	Optional	Department leader name
`phone`	`String(11)`	Optional	Contact phone
`email`	`String(64)`	Optional	Contact email
`status`	`int`	Default 1	0=disabled, 1=active
`del_flag`	`bool`	Default False	0=deleted, 1=exists (soft delete)
`parent_id`	`BigInteger`	Optional, Indexed	Parent department ID

Soft Delete: The del_flag: Mapped[bool] field backend/app/admin/model/dept.py20 implements soft deletion with default=False. When True, the department is logically deleted but database records remain. This preserves referential integrity for historical user assignments.

Sources: backend/app/admin/model/user.py:12-37, backend/app/admin/model/role.py:8-18, backend/app/admin/model/menu.py:8-30, backend/app/admin/model/dept.py:8-24, backend/app/admin/model/init.py:10-19

SQLAlchemy 2.0 Field Patterns

The system uses SQLAlchemy 2.0's Mapped[] generic type for all field declarations, providing type safety and IDE support. Several custom type aliases and patterns ensure consistency across models.

id_key Type

The id_key type alias backend/common/model.py9 defines the primary key pattern. All models declare:

Type: Resolves to Annotated[int, mapped_column(BigInteger, primary_key=True, autoincrement=True)]
init=False: Excludes from dataclass __init__, as IDs are auto-generated by Snowflake ID or database sequence
Snowflake ID: Provides globally unique, time-ordered 64-bit integers (see Database Schema & Initialization)

TimeZone Column Type

The TimeZone type backend/database/db_postgres.py15-17 provides timezone-aware datetime handling with automatic conversion. Declared as:

Base class fields: created_time and updated_time inherited by all models backend/common/model.py21-26
onupdate callback: Automatically updates updated_time on record modification backend/common/model.py26
Timezone handling: Converts to UTC for storage, to local timezone on retrieval

UniversalText Type

The UniversalText type alias backend/database/db_postgres.py20-22 provides database-agnostic text storage:

Maps to Text column type across PostgreSQL, MySQL, and SQLite, supporting arbitrary-length strings. Used for:

Role.remark backend/app/admin/model/role.py17 - Role descriptions
Menu.link backend/app/admin/model/menu.py25 - External/iframe URLs
Menu.remark backend/app/admin/model/menu.py26 - Menu notes
GenBusiness.remark backend/plugin/code_generator/model/business.py29 - Code generation notes
GenColumn.default backend/plugin/code_generator/model/column.py18 - Column default values

Logical Foreign Keys

The system uses logical foreign keys instead of database-level FOREIGN KEY constraints. These are BigInteger fields with foreign key semantics but no enforced referential integrity:

Examples:

User.dept_id → Dept.id backend/app/admin/model/user.py36
UserSocial.user_id → User.id backend/plugin/oauth2/model/user_social.py18
GenColumn.gen_business_id → GenBusiness.id backend/plugin/code_generator/model/column.py25
Menu.parent_id → Menu.id (self-referential) backend/app/admin/model/menu.py29
Dept.parent_id → Dept.id (self-referential) backend/app/admin/model/dept.py23

Design Rationale:

Soft Delete Support: Allows logical deletion (del_flag) without cascade constraint violations
Plugin Independence: Cross-plugin relationships work without tight database coupling
Migration Flexibility: Easier data migration and testing without constraint management
Dynamic Relationships: Supports optional relationships and partial data states

CRUD Layer Joins: Relationships are explicitly defined using JoinConfig in CRUD methods backend/app/admin/crud/crud_user.py101:

See CRUD Patterns & Service Layer for join operation details.

Diagram: SQLAlchemy 2.0 Field Type Patterns

Sources: backend/common/model.py:9-26, backend/database/db_postgres.py:15-22, backend/app/admin/model/user.py:17-36, backend/app/admin/model/role.py:17, backend/app/admin/model/menu.py:25-29, backend/plugin/oauth2/model/user_social.py:18, backend/plugin/code_generator/model/column.py:25, backend/app/admin/crud/crud_user.py:101

Many-to-Many Relationships

The system implements many-to-many relationships through junction tables defined as SQLAlchemy Table objects. These are defined in backend/app/admin/model/__init__.py10-19 and imported wherever join operations are needed.

Junction Table Definitions

Four junction tables define the relationship graph:

Junction Table	Database Table	Left Entity	Right Entity	Purpose
`user_role`	`sys_user_role`	`User`	`Role`	User role assignments
`role_menu`	`sys_role_menu`	`Role`	`Menu`	Role permission grants
`role_data_scope`	`sys_role_data_scope`	`Role`	`DataScope`	Role data scope bindings
`data_scope_rule`	`sys_data_scope_rule`	`DataScope`	`DataRule`	Data scope filtering rules

Code Definition: All four tables are defined in backend/app/admin/model/__init__.py10-19 and exported via __all__ for import in CRUD and service layers.

Example Import in CRUD layer backend/app/admin/crud/crud_user.py9-20:

Usage in CRUD Join Operations

The junction tables are used in JoinConfig definitions to construct SQLAlchemy joins. The CRUDUser class backend/app/admin/crud/crud_user.py76-353 demonstrates progressive join complexity:

Basic User List with Roles backend/app/admin/crud/crud_user.py97-105:

Full User Detail with All Relationships backend/app/admin/crud/crud_user.py328-342:

Join Patterns:

Junction tables (user_role, role_menu, etc.) have fill_result=False (implicit) - they don't populate result objects
Entity models (User, Role, Dept, etc.) have fill_result=True - they populate nested objects in results
Joins follow the relationship chain: User → user_role → Role → role_menu → Menu

Relationship Serialization

The select_join_serialize utility backend/utils/serializers.py transforms flat join results into nested object structures. Defined in CRUD layer backend/app/admin/crud/crud_user.py344-353 and called in service layer backend/app/admin/service/user_service.py79

Serialization Format:

Relationship Syntax:

'EntityA-m2o-EntityB': Many-to-one relationship, creates entityA.entityb (singular)
'EntityA-m2m-EntityB': Many-to-many relationship, creates entityA.entitybs (plural, auto-pluralized)
'EntityA-m2m-EntityB:custom_name': Many-to-many with explicit attribute name entityA.custom_name

Service Layer Usage backend/app/admin/service/user_service.py79:

Junction Table Manipulation

CRUD operations manipulate junction tables using direct SQL statements:

Adding User with Roles backend/app/admin/crud/crud_user.py108-131:

Updating User Roles backend/app/admin/crud/crud_user.py154-178:

Deleting User backend/app/admin/crud/crud_user.py291-304:

Entity Relationship Graph

The junction tables create a relationship graph enabling:

Permission Checking: Query accessible menus for a role via role_menu
Data Filtering: Apply data scopes to queries via role_data_scope and data_scope_rule
RBAC Authorization: Verify user permissions by traversing User → Role → Menu chain

Diagram: Many-to-Many Relationship Implementation

Sources: backend/app/admin/model/init.py:10-19, backend/app/admin/crud/crud_user.py:9-20, backend/app/admin/crud/crud_user.py:76-106, backend/app/admin/crud/crud_user.py:108-131, backend/app/admin/crud/crud_user.py:154-178, backend/app/admin/crud/crud_user.py:291-304, backend/app/admin/crud/crud_user.py:306-353, backend/utils/serializers.py, backend/app/admin/service/user_service.py:79

Hierarchical Self-Referential Models

Two models implement tree structures using self-referential parent_id logical foreign keys.

The Menu model backend/app/admin/model/menu.py29 implements a navigation hierarchy:

Tree Properties:

Root nodes: parent_id = None
Child nodes: parent_id references parent Menu.id
Unlimited nesting depth supported
Sibling ordering: sort: Mapped[int] field backend/app/admin/model/menu.py17

Menu Type Hierarchy:

type=0: Directory (container, no route)
type=1: Menu (routable page)
type=2: Button (permission-only, no display)
type=3: Embedded iframe
type=4: External link

Tree Building: See Tree Structures & Hierarchical Data for build_tree and get_vben5_tree_data utilities backend/utils/build_tree.py

Dept Tree Structure

The Dept model backend/app/admin/model/dept.py23 implements organizational hierarchy:

Tree Properties:

Root departments: parent_id = None
Multi-level organizational structures supported
Sibling ordering: sort: Mapped[int] field backend/app/admin/model/dept.py15
Soft delete: del_flag: Mapped[bool] preserves tree integrity backend/app/admin/model/dept.py20

Use Cases:

Department tree visualization in admin UI
User assignment to organizational units via User.dept_id backend/app/admin/model/user.py36
Data permission filtering by department hierarchy (see Data Scope & Row-Level Security)

Diagram: Self-Referential Tree Models

Sources: backend/app/admin/model/menu.py:8-30, backend/app/admin/model/dept.py:8-24, backend/utils/build_tree.py:25-132

Plugin Models

Plugin models extend the core system with additional functionality while maintaining architectural consistency.

OAuth2 Plugin - UserSocial Model

The UserSocial model backend/plugin/oauth2/model/user_social.py8-19 stores OAuth2 social login associations in the sys_user_social table:

Field	Type	Description
`id`	`id_key`	Primary key
`sid`	`String(256)`	Third-party user ID from OAuth provider
`source`	`String(32)`	OAuth provider name (github, google, linuxdo)
`user_id`	`BigInteger`	Logical FK to `sys_user.id`

Purpose: Links local user accounts to external OAuth2 identities, enabling social login while maintaining a unified user system.

Relationship: One local user can have multiple social account bindings (one per OAuth provider).

Code Generator Plugin Models

The code generator plugin uses two models to store table metadata for code generation.

GenBusiness Model

The GenBusiness model backend/plugin/code_generator/model/business.py8-30 stores business table metadata in the gen_business table:

Field	Type	Description
`id`	`id_key`	Primary key
`app_name`	`String(64)`	Target application module name
`table_name`	`String(256)`	Database table name (unique)
`doc_comment`	`String(256)`	Documentation comment for generated code
`table_comment`	`String(256)`	Table description from DB schema
`class_name`	`String(64)`	Generated model class name
`schema_name`	`String(64)`	Generated Pydantic schema prefix
`filename`	`String(64)`	Base filename for generated files
`default_datetime_column`	`bool`	Whether table has created_time/updated_time
`api_version`	`String(32)`	API version prefix (default: v1)
`gen_path`	`String(256)`	Output directory path
`remark`	`UniversalText`	Additional notes

GenColumn Model

The GenColumn model backend/plugin/code_generator/model/column.py8-26 stores column metadata in the gen_column table:

Field	Type	Description
`id`	`id_key`	Primary key
`name`	`String(64)`	Column name
`comment`	`String(256)`	Column description
`type`	`String(32)`	SQLAlchemy column type (e.g., String, Integer)
`pd_type`	`String(32)`	Corresponding Pydantic type (e.g., str, int)
`default`	`UniversalText`	Default value
`sort`	`int`	Column order in table
`length`	`int`	Column length (for strings)
`is_pk`	`bool`	Whether column is primary key
`is_nullable`	`bool`	Whether column allows NULL
`gen_business_id`	`BigInteger`	Logical FK to `gen_business.id`

Relationship: GenBusiness (1) → (many) GenColumn, representing a table's columns.

Diagram: Plugin Model Relationships

Sources: backend/plugin/oauth2/model/user_social.py:8-19, backend/plugin/code_generator/model/business.py:8-30, backend/plugin/code_generator/model/column.py:8-26, backend/common/model.py:12-40, backend/app/admin/model/user.py:12

Naming Conventions and Table Structure

The system follows consistent naming conventions for maintainability and clarity.

Table Naming Patterns

Prefix	Purpose	Examples
`sys_`	Core system administrative tables	`sys_user`, `sys_role`, `sys_menu`, `sys_dept`
`gen_`	Code generator plugin tables	`gen_business`, `gen_column`
(no prefix)	Plugin-specific or business tables	Custom application tables

Column Naming Conventions

Primary Keys: Always named id with id_key type
Foreign Keys: Named as {entity}_id (e.g., dept_id, user_id, parent_id)
Timestamps: created_time, updated_time, join_time, last_login_time
Status Fields: status (integer), typically 0=disabled, 1=active
Boolean Flags: Prefixed with is_ or suffixed with _flag (e.g., is_superuser, del_flag)
Text Fields: Use UniversalText for potentially long content (e.g., remark, link)

SQLAlchemy 2.0 Declarative Patterns

All models use modern SQLAlchemy 2.0 syntax:

Type Annotations:

Nullable Fields:

Required Fields:

Non-Init Fields (auto-generated):

Factory Functions:

Index Strategy

Strategic indexing is applied to:

Unique Constraints: User.username, User.email, User.uuid, Role.name, GenBusiness.table_name
Lookup Fields: User.username (indexed), User.email (indexed), User.status (indexed)
Tree Navigation: Menu.parent_id (indexed), Dept.parent_id (indexed)

This indexing strategy optimizes common query patterns like user authentication, role lookups, and tree traversal.

Sources: backend/app/admin/model/user.py, backend/app/admin/model/role.py, backend/app/admin/model/menu.py, backend/app/admin/model/dept.py, backend/plugin/oauth2/model/user_social.py, backend/plugin/code_generator/model/business.py, backend/plugin/code_generator/model/column.py