ss-tools/specs/016-multi-user-auth/data-model.md

Data Model: Multi-User Authentication

Entities

User

Represents an identity that can authenticate to the system.

Field	Type	Description	Constraints
id	UUID	Unique identifier	Primary Key
username	String	Unique login name	Unique, Not Null
email	String	User email address	Unique, Optional
password_hash	String	Bcrypt hash of password	Nullable (if ADFS)
auth_source	Enum	Source of identity	LOCAL or ADFS
is_active	Boolean	Account status	Default True
created_at	DateTime	Timestamp of creation	Auto-generated
last_login	DateTime	Timestamp of last login	Nullable

Role

Represents a collection of permissions.

Field	Type	Description	Constraints
id	UUID	Unique identifier	Primary Key
name	String	Human-readable role name	Unique, Not Null
description	String	Description of role purpose	Optional

Permission

Represents a specific capability within the system.

Field	Type	Description	Constraints
id	UUID	Unique identifier	Primary Key
resource	String	Target resource (e.g. plugin:backup)	Not Null
action	Enum	Type of access	READ, EXECUTE, WRITE

ADGroupMapping

Maps an Active Directory group to a local System Role.

Field	Type	Description	Constraints
id	UUID	Unique identifier	Primary Key
ad_group_name	String	Name of the group in AD	Unique, Not Null
role_id	UUID	ID of the local role to assign	Foreign Key -> Role.id

Relationships

• User <-> Role: Many-to-Many (via user_roles table)
  • A User can have multiple Roles.
  • A Role can be assigned to multiple Users.
• Role <-> Permission: Many-to-Many (via role_permissions table)
  • A Role is defined by a set of Permissions.
  • A Permission can belong to multiple Roles.

Storage Schema (SQLAlchemy)

# Conceptual Schema Definition

class User(Base):
    __tablename__ = "users"
    id = Column(String, primary_key=True, default=generate_uuid)
    username = Column(String, unique=True, index=True, nullable=False)
    password_hash = Column(String, nullable=True)
    auth_source = Column(String, default="local")
    is_active = Column(Boolean, default=True)
    roles = relationship("Role", secondary="user_roles", back_populates="users")

class Role(Base):
    __tablename__ = "roles"
    id = Column(String, primary_key=True, default=generate_uuid)
    name = Column(String, unique=True, nullable=False)
    permissions = relationship("Permission", secondary="role_permissions")
    users = relationship("User", secondary="user_roles", back_populates="roles")

class Permission(Base):
    __tablename__ = "permissions"
    id = Column(String, primary_key=True, default=generate_uuid)
    resource = Column(String, nullable=False) # e.g., "plugin:backup"
    action = Column(String, nullable=False)   # e.g., "execute"

class ADGroupMapping(Base):
    __tablename__ = "ad_group_mappings"
    id = Column(String, primary_key=True, default=generate_uuid)
    ad_group_name = Column(String, unique=True, nullable=False)
    role_id = Column(String, ForeignKey("roles.id"), nullable=False)