Separate database models into public and private schemas

[chriscarrollsmith]

The most common approach is to use SQLAlchemy's schema parameter, which SQLModel inherits since it's built on top of SQLAlchemy.

Here's how you could modify your models to use different schemas:

// ... existing code ...

class UserRoleLink(SQLModel, table=True):
    """
    Associates users with roles. This creates a many-to-many relationship
    between users and roles.
    """
    __table_args__ = {"schema": "public"}
    
    user_id: Optional[int] = Field(foreign_key="user.id", primary_key=True)
    role_id: Optional[int] = Field(foreign_key="role.id", primary_key=True)

// ... existing code ...

class JWTToken(SQLModel, table=True):
    """
    Stores JWT tokens for authentication and tracking.
    """
    __table_args__ = {"schema": "private"}
    
    id: Optional[int] = Field(default=None, primary_key=True)
    user_id: int = Field(foreign_key="user.id")
    token: str = Field(index=True)
    expires_at: datetime
    revoked: bool = Field(default=False)
    created_at: datetime = Field(default_factory=utc_time)
    
    user: Mapped[Optional["User"]] = Relationship(back_populates="jwt_tokens")

// ... existing code ...

# Add this relationship to the User class
class User(SQLModel, table=True):
    // ... existing code ...
    
    jwt_tokens: Mapped[List["JWTToken"]] = Relationship(
        back_populates="user",
        sa_relationship_kwargs={
            "cascade": "all, delete-orphan"
        }
    )
    
    // ... existing code ...

Important considerations:

1. You'll need to create these schemas in your database first with SQL commands like CREATE SCHEMA private;

2. When setting up your database connection, you'll need to ensure the search path includes both schemas:

   engine = create_engine("postgresql://user:password@localhost/dbname", 
                         connect_args={"options": "-c search_path=public,private"})

3. For foreign keys across schemas, you'll need to use fully qualified names:

   user_id: int = Field(foreign_key="public.user.id")

4. You may need to adjust your database permissions to ensure proper access to the private schema.

This approach allows you to logically separate your models while still maintaining relationships between them.