vitepress-template

Appearance

Database Schema and Data Model ​

**Referenced Files in This Document** - [[schema.sql]](file/bi-tenant/docs/database/schema.sql) - [[init.sql]](file/bi-tenant/docs/database/init.sql) - [[design.md]](file/bi-tenant/docs/database/design.md) - [[data.go]](file/bi-tenant/internal/data/data.go) - [[tenant.go]](file/bi-tenant/internal/data/tenant.go) - [[tenant.go]](file/bi-tenant/internal/biz/tenant.go)

Table of Contents ​

  1. Introduction
  2. Project Structure
  3. Core Components
  4. Architecture Overview
  5. Detailed Component Analysis
  6. Dependency Analysis
  7. Performance Considerations
  8. Troubleshooting Guide
  9. Conclusion
  10. Appendices

Introduction ​

This document provides comprehensive data model documentation for the bi-tenant service database schema. It details entity relationships among tenants, users, organizations, roles, and permissions, and explains table structures, primary keys, foreign keys, indexes, constraints, and tenant isolation mechanisms. It also covers data validation rules, business logic constraints, referential integrity enforcement, access patterns, query optimization strategies, performance characteristics, data lifecycle management, retention policies, archival strategies, and database-level security measures.

Project Structure ​

The bi-tenant service organizes its database design artifacts and runtime code as follows:

  • Database schema and initialization scripts reside under bi-tenant/docs/database.
  • Business logic and persistence layers are implemented under bi-tenant/internal/biz and bi-tenant/internal/data.
  • Runtime data access is configured via bi-tenant/internal/data/data.go.

Diagram sources

Section sources

Core Components ​

This section outlines the core entities and their attributes, focusing on multi-tenant isolation and permission modeling.

  • Tenant (tenant): Represents SaaS tenants with status, contact info, and lifecycle metadata.
  • Tenant Package (tenant_package): Defines feature scope; pricing and duration are set per subscription.
  • Tenant Menu (tenant_menu): Hierarchical navigation items (directories/pages/actions) with permissions.
  • Tenant Organization (tenant_org): Multi-level organizational hierarchy (company/department/team).
  • Tenant User (tenant_user): Users linked to tenant and organization; supports admin flag and credentials.
  • Tenant Role (tenant_role): Roles scoped to tenant for permission assignment.
  • Tenant Subscription (tenant_subscription): Links tenant to package with effective dates and status.
  • Association tables:
    • tenant_user_role: Many-to-many between users and roles.
    • tenant_role_menu: Many-to-many between roles and menus.
    • tenant_package_menu: Defines package feature scope.
    • tenant_user_store: Grants users access to external stores.
    • tenant_user_delegate: Allows users to inherit another user’s store permissions.
    • tenant_role_template: Links roles to analysis templates (external).
    • tenant_user_favorite: Stores user favorites (shortcuts).
  • Logging tables:
    • tenant_login_log: Duplicate-key table with dynamic monthly partitions.
    • tenant_operation_log: Duplicate-key table with dynamic monthly partitions.

Key isolation and constraints:

  • All tenant-scoped tables include tenant_id to enforce logical separation.
  • Soft delete pattern via deleted_at (BIGINT timestamp) with bitmap indexes for filtering.
  • Primary Key tables with BITMAP indexes on frequently filtered columns (status, tenant_id, etc.).
  • Dynamic partitioning by time for logging tables to manage growth.

Section sources

Architecture Overview ​

The database architecture leverages StarRocks with Primary Key and Duplicate Key tables, BITMAP indexes, and dynamic partitioning to support multi-tenant SaaS workloads.

Diagram sources

Detailed Component Analysis ​

Tenant Isolation and Multi-Tenant Access Control ​

  • All tenant-scoped tables include tenant_id as part of primary keys or filters to ensure logical separation.
  • Soft deletion via deleted_at enables audit trails while maintaining isolation.
  • BITMAP indexes on tenant_id, status, and deleted_at accelerate tenant-aware queries.
  • Business logic enforces tenant boundaries at runtime:
    • Use-case creates tenant, default organization, and admin user in sequence.
    • Subscription creation links tenant to package with pricing and duration.
    • Admin users receive full access to package menus; non-admins inherit role-based permissions intersected with current package menus.

Diagram sources

Section sources

Data Validation Rules and Constraints ​

  • Required fields enforced by NOT NULL constraints on identifiers and core attributes.
  • Enum-like constraints via tinyint enums for status, type, visibility, and admin flags.
  • Unique constraints:
    • tenant_code uniqueness enforced at application level during creation.
  • Referential integrity:
    • Foreign keys implied by tenant_* tables referencing tenant and other core entities.
    • Association tables define composite keys and tenant-scoped FKs.
  • Soft delete:
    • deleted_at default 0 indicates active records; queries filter by deleted_at = 0.

Section sources

Indexes and Constraints Summary ​

  • Primary Key tables:
    • tenant, tenant_package, tenant_menu, tenant_org, tenant_user, tenant_role, and association tables use primary keys.
  • BITMAP indexes:
    • tenant: idx_tenant_status, idx_tenant_deleted_at
    • tenant_package: idx_package_status, idx_package_deleted_at
    • tenant_menu: idx_menu_parent_id, idx_menu_menu_type, idx_menu_status
    • tenant_org: idx_org_tenant_id, idx_org_parent_id, idx_org_org_type, idx_org_status, idx_org_deleted_at
    • tenant_user: idx_user_tenant_id, idx_user_org_id, idx_user_is_admin, idx_user_status, idx_user_deleted_at
    • tenant_role: idx_role_tenant_id, idx_role_status, idx_role_deleted_at
    • tenant_user_role: idx_ur_tenant_id, idx_ur_user_id, idx_ur_role_id
    • tenant_role_menu: idx_rm_tenant_id, idx_rm_role_id, idx_rm_menu_id
    • tenant_package_menu: idx_pm_package_id, idx_pm_menu_id
    • tenant_user_store: idx_us_tenant_id, idx_us_user_id, idx_us_store_id
    • tenant_user_delegate: idx_ud_tenant_id, idx_ud_user_id, idx_ud_delegate_user_id
    • tenant_role_template: idx_rt_tenant_id, idx_rt_role_id, idx_rt_template_id
    • tenant_user_favorite: idx_uf_tenant_id, idx_uf_user_id
  • Duplicate Key + Dynamic Partition:
    • tenant_login_log: DUPLICATE KEY (log_id, login_time) with monthly dynamic partitions
    • tenant_operation_log: DUPLICATE KEY (log_id, operation_time) with monthly dynamic partitions

Section sources

Data Lifecycle Management and Retention ​

  • Soft delete pattern via deleted_at allows historical auditing without physical removal.
  • Dynamic partitioning on logging tables enables automated retention management by dropping old partitions.
  • Subscription-based lifecycle:
    • tenant_subscription tracks effective periods; status transitions mark expiration.
    • Downgrades retain role-menu associations; UI marks unavailable menus until re-upgraded.

Section sources

Security Measures and Access Control ​

  • Passwords stored as bcrypt hashes; admin flag prevents accidental deletion or disabling of super-admin accounts.
  • Tenant isolation enforced by tenant_id in all queries and indexes.
  • RBAC model:
    • Super-admins inherit all package menus.
    • Non-admins’ accessible menus = role menus ∩ package menus.
    • Store-level delegation allows inheriting another user’s store permissions.

Section sources

Dependency Analysis ​

Runtime data access depends on a shared data client initialized with database and Redis clients. Repositories encapsulate persistence logic and enforce tenant isolation.

Diagram sources

Section sources

Performance Considerations ​

  • Storage engine and distribution:
    • Primary Key tables distributed by tenant_id or entity-specific keys to balance data locality and concurrency.
  • Indexing strategy:
    • BITMAP indexes on tenant_id, status, and deleted_at improve filtering performance for tenant-scoped queries.
  • Partitioning:
    • Monthly dynamic partitions on logging tables reduce scan sizes and improve maintenance.
  • Query patterns:
    • Prefer tenant_id filters and bitmap indexes for multi-tenant scans.
    • Use association tables to avoid expensive joins; precompute accessible menus per user based on package and roles.
  • Transactions:
    • StarRocks transaction limitations require careful sequencing of inserts; mixed operations are split across calls.

Section sources

Troubleshooting Guide ​

Common issues and resolutions:

  • Duplicate tenant code:
    • Symptom: Creation fails due to existing tenant_code.
    • Resolution: Ensure unique tenant_code or allow auto-generation; repository validates uniqueness excluding self.
  • Subscription updates:
    • Symptom: Downgrade appears ineffective.
    • Resolution: Downgrades mark previous subscription inactive; new subscription created; accessible menus reflect current package.
  • Admin account lockout:
    • Symptom: Super-admin cannot be disabled or deleted.
    • Resolution: is_admin flag prevents modification; reset password via use-case if needed.
  • Logging retention:
    • Symptom: Disk usage grows rapidly.
    • Resolution: Verify dynamic partition settings and retention policy; drop old partitions as needed.

Section sources

Conclusion ​

The bi-tenant database schema implements robust multi-tenant isolation using tenant_id, soft deletes, and BITMAP indexing. The RBAC model combines package-defined permissions with role-based access, while store-level delegation and dynamic partitioning support scalable operations. Initialization scripts seed core packages and menus, enabling immediate tenant onboarding. Adhering to the outlined constraints, indexes, and access patterns ensures secure, efficient, and maintainable multi-tenant operations.

Appendices ​

Appendix A: Initialization Data Overview ​

  • Seed packages with distinct feature scopes and descriptions.
  • Populate tenant menus aligned with platform capabilities (Taobao, Douyin, Base, Org, System).
  • Create package-menu mappings per tier (Trial, Flagship, Professional, Enterprise).
  • Insert test tenants with subscriptions and default admin users for development.

Section sources