Skip to content

erd.md

Latest commit

 

History

History
236 lines (175 loc) · 7.63 KB

erd.md

File metadata and controls

236 lines (175 loc) · 7.63 KB

Database Entity Relationship Diagram (ERD)

This document contains the automatically generated Entity Relationship Diagram for the FastAPI Template database schema. The ERD is generated from SQLModel definitions and updated automatically via git pre-commit hooks.

Overview

The ERD below shows the current database schema with all tables, fields, relationships, and constraints. This diagram is automatically maintained and reflects the actual SQLModel definitions in the codebase.

Row-Level Security (RLS)

The database implements Row-Level Security (RLS) for automatic data isolation. Models that inherit from UserScopedBase automatically have:

  • owner_id field: Foreign key to user.id for data ownership
  • RLS policies: Automatically generated and applied during migrations
  • User isolation: Users can only access their own data at the database level
  • Admin bypass: Superusers can access all data through RLS policies

RLS-Scoped Models

Models marked with 🔒 in the ERD are RLS-scoped and have automatic user isolation:

  • User: Base model for authentication and user management
  • Item: Example RLS-scoped model demonstrating user-owned data

RLS Policies

Each RLS-scoped model has the following policies automatically applied:

  • SELECT: Users can only see records where owner_id matches their user ID
  • INSERT: Users can only insert records with their own owner_id
  • UPDATE: Users can only update records they own
  • DELETE: Users can only delete records they own

Database Roles

The system uses multiple database roles for security:

  • Application User (rls_app_user): Normal application operations (subject to RLS)
  • Maintenance Admin (rls_maintenance_admin): Maintenance operations (bypasses RLS)

Generated ERD

%% This diagram is automatically generated from SQLModel definitions
%% Last updated: 2024-12-19
%% Generated by: ERD Generator v1.0
%% RLS: Row-Level Security enabled for user-owned models

erDiagram

USER {
    uuid id PK
    string email UK
    string hashed_password
    boolean is_active
    boolean is_superuser
    string full_name
    datetime created_at
    datetime updated_at
}

ITEM {
    uuid id PK
    uuid owner_id FK NOT NULL 🔒
    string title
    string description
    datetime created_at
    datetime updated_at
}

USER ||--o{ ITEM : "owns items (RLS enforced)"
Loading

Schema Details

Tables

USER

  • Purpose: Stores user account information
  • Primary Key: id (UUID)
  • Fields:
    • id: Primary key (UUID, auto-generated)
    • hashed_password: User's hashed password (string)

ITEM

  • Purpose: Stores user-owned items
  • Primary Key: id (UUID)
  • Foreign Keys:
    • owner_id → USER.id
  • Fields:
    • id: Primary key (UUID, auto-generated)
    • owner_id: Foreign key to USER table (UUID, required)

Relationships

USER → ITEM (One-to-Many)

  • Type: One-to-Many
  • Description: A user can own multiple items
  • Implementation: Foreign key owner_id in ITEM table
  • Cascade: Not specified (default behavior)

How This ERD is Maintained

Automatic Updates

This ERD diagram is automatically updated whenever:

  • SQLModel definitions are modified in backend/app/models.py
  • New models are added or removed
  • Relationships between models change
  • Field definitions are updated

Update Mechanism

The ERD is updated via a git pre-commit hook that:

  1. Detects changes to SQLModel files
  2. Regenerates the ERD from current model definitions
  3. Updates this documentation file
  4. Validates the generated ERD syntax

Manual Generation

You can manually regenerate the ERD using:

# Generate ERD from current models
python -m backend.scripts.generate_erd

# Generate with validation
python -m backend.scripts.generate_erd --validate --verbose

# Generate to custom location
python -m backend.scripts.generate_erd --output-path custom/erd.mmd

Validation

The ERD generation process includes validation to ensure:

  • All SQLModel classes with table=True are included
  • Primary keys are properly defined
  • Foreign key relationships are valid
  • Generated Mermaid syntax is correct
  • All entities have at least one field

Validation Checks

  • Model Validation: Ensures all models have required fields and valid relationships
  • Syntax Validation: Validates generated Mermaid ERD syntax
  • Relationship Validation: Ensures all relationships reference valid entities
  • Constraint Validation: Verifies database constraints are properly represented

Performance

The ERD generation system is designed to handle:

  • Small schemas (< 5 tables): < 1 second
  • Medium schemas (5-10 tables): < 5 seconds
  • Large schemas (10-20 tables): < 30 seconds
  • Very large schemas (20+ tables): Scales linearly

Troubleshooting

Common Issues

ERD Not Updating

  • Cause: Pre-commit hook not installed or not running
  • Solution: Run pre-commit install and ensure hooks are enabled

Invalid ERD Syntax

  • Cause: Malformed SQLModel definitions or relationship issues
  • Solution: Run python -m backend.scripts.generate_erd --validate to identify issues

Missing Tables

  • Cause: SQLModel class missing table=True parameter
  • Solution: Ensure all database models have table=True in their class definition

Relationship Issues

  • Cause: Incorrect back_populates or foreign key definitions
  • Solution: Verify relationship definitions match between related models

Getting Help

If you encounter issues with the ERD generation:

  1. Check validation output:

    python -m backend.scripts.generate_erd --validate --verbose

  2. Review model definitions in backend/app/models.py

  3. Check pre-commit hook status:

    pre-commit run erd-generation --verbose

  4. Regenerate from scratch:

    rm docs/database/erd.mmd
python -m backend.scripts.generate_erd

Technical Details

Generation Process

  1. Model Discovery: Scan for SQLModel classes in specified paths
  2. Metadata Extraction: Parse SQLModel definitions to extract schema information
  3. Relationship Analysis: Detect and analyze relationships between models
  4. ERD Generation: Create Mermaid ERD syntax from extracted metadata
  5. Validation: Validate generated ERD for syntax and semantic correctness
  6. Output: Write ERD to documentation file

Bidirectional Relationship Handling

The ERD generator intelligently handles bidirectional relationships by:

  • Detecting relationships with back_populates parameters
  • Showing only the one-to-many direction to reduce visual clutter
  • Displaying foreign key fields to indicate reverse relationships
  • Avoiding redundant relationship lines

File Formats

  • Input: SQLModel Python classes in backend/app/models.py
  • Output: Mermaid ERD syntax in docs/database/erd.mmd
  • Documentation: Markdown with embedded Mermaid in docs/database/erd.md

Integration

This ERD system integrates with:

  • Git Workflow: Automatic updates via pre-commit hooks
  • Documentation: Part of project documentation standards
  • CI/CD: Can be included in build and deployment pipelines
  • Development: Provides real-time schema visualization

Related Documentation

This documentation is automatically maintained. Do not edit manually - changes will be overwritten.