graph-requirements-planner/.planning/research/STACK.md

Stack Research: Multi-Tenancy & User Accounts

Domain: Requirements management tool with multi-user support Researched: 2026-03-18 Confidence: HIGH

Executive Summary

Adding user accounts, invite-only registration, RBAC, and tenant isolation to your existing FastAPI + SQLAlchemy + SQLite stack requires minimal new dependencies. The core stack remains unchanged - only authentication primitives need upgrading from shared password to individual accounts.

Key additions:

• pwdlib with Argon2 for password hashing (replaces bcrypt/passlib)
• email-validator for email validation in Pydantic models
• secrets module (stdlib) for invite token generation
• Tenant isolation via SQLAlchemy query filters (no library needed)

No changes needed: Your existing FastAPI, SQLAlchemy 2.0, SQLite, PyJWT, and Angular 17 stack remain unchanged.

Recommended Stack Additions

Authentication & Security

Technology	Version	Purpose	Why Recommended
pwdlib	0.4.0+	Password hashing with Argon2	Modern replacement for passlib (deprecated in Python 3.13). FastAPI official docs now recommend pwdlib. Argon2 is GPU-resistant and OWASP-recommended for 2026.
email-validator	2.2.0+	Email validation for user registration	Required by Pydantic's EmailStr type. Standard for email validation with deliverability checks.
secrets	stdlib	Secure invite token generation	Python standard library (3.6+). Cryptographically secure token generation with 256-bit randomness. Use secrets.token_urlsafe(32) for invite links.

NO Library Needed (Use Patterns)

Capability	Implementation	Why No Library
Multi-tenancy	SQLAlchemy query filters with user_id FK	Simple pattern - add user_id column to all tables, filter queries by current user. PostgreSQL Row-Level Security (RLS) not available in SQLite.
RBAC	Enum + dependency injection	Three roles (viewer/editor/owner) fit in simple Enum. Use FastAPI Depends() to check role on routes. Libraries like fastapi-user-auth add unnecessary complexity for 3 roles.
Edit attribution	Add modified_by_user_id column	You already track edit history - just add user reference to existing requirement_history table.
Email sending	Python smtplib (stdlib)	Built-in library sufficient for invite emails. Use FastAPI BackgroundTasks for async sending. No need for Celery yet.

Unchanged Core Stack

Your existing stack is already optimal for this use case:

Technology	Version	Status	Notes
FastAPI	Current	✓ Keep	Dependency injection perfect for RBAC
SQLAlchemy	2.0+	✓ Keep	Event listeners support tenant filtering
SQLite	3.x	✓ Keep	WAL mode supports concurrent readers + 1 writer (sufficient for small team)
PyJWT	2.x	✓ Keep	Already used for JWT - extend payload to include user_id and role
Angular	17	✓ Keep	Frontend unchanged - JWT payload has new fields
Pydantic	2.x	✓ Keep	Add email-validator extra for EmailStr

Installation

# Backend additions only
pip install "pwdlib[argon2]>=0.4.0"
pip install "pydantic[email]>=2.0"

# No changes to frontend

Integration Patterns

1. Password Hashing (pwdlib + Argon2)

from pwdlib import PasswordHash

# Initialize once at startup
password_hash = PasswordHash.recommended()

# Hash password on registration
hashed = password_hash.hash("user_password")

# Verify on login
is_valid = password_hash.verify("user_password", hashed)

Why this pattern: FastAPI docs migrated from passlib to pwdlib in 2026. Argon2 is more secure than bcrypt and future-proof.

2. Email Validation (Pydantic EmailStr)

from pydantic import BaseModel, EmailStr

class UserCreate(BaseModel):
    email: EmailStr  # Automatically validated
    password: str

Why this pattern: Pydantic's EmailStr uses email-validator under the hood. No manual validation needed.

3. Invite Token Generation (secrets)

import secrets
from datetime import datetime, timedelta

# Generate invite token
invite_token = secrets.token_urlsafe(32)  # 256 bits
expires_at = datetime.utcnow() + timedelta(hours=24)

# Store in database
invite = Invite(token=invite_token, email=email, expires_at=expires_at)

Why this pattern: Standard library, cryptographically secure, URL-safe for email links.

4. Multi-Tenancy (SQLAlchemy Filters)

# Add to all models
class RequirementNode(Base):
    __tablename__ = "requirement_nodes"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    # ... other columns

# Filter queries by current user
def get_requirements(db: Session, current_user: User):
    return db.query(RequirementNode).filter(
        RequirementNode.user_id == current_user.id
    ).all()

Why this pattern: SQLite doesn't support PostgreSQL's Row-Level Security. Manual filtering is explicit and debuggable. Add SQLAlchemy event listener for automatic filtering if needed later.

5. RBAC (Enum + Dependency Injection)

from enum import Enum
from fastapi import Depends, HTTPException

class Role(str, Enum):
    VIEWER = "viewer"
    EDITOR = "editor"
    OWNER = "owner"

def require_role(required: Role):
    def check_role(current_user: User = Depends(get_current_user)):
        if current_user.role not in [required, Role.OWNER]:  # Owner has all permissions
            raise HTTPException(status_code=403, detail="Insufficient permissions")
        return current_user
    return check_role

# Use in routes
@app.delete("/requirements/{id}")
async def delete_requirement(
    id: int,
    current_user: User = Depends(require_role(Role.EDITOR))
):
    ...

Why this pattern: FastAPI's Depends() makes role checks declarative. No library needed for 3 roles.

6. JWT Payload Extension

# Extend existing JWT to include user info
def create_access_token(user: User) -> str:
    payload = {
        "sub": str(user.id),
        "email": user.email,
        "role": user.role.value,
        "exp": datetime.utcnow() + timedelta(hours=24)
    }
    return jwt.encode(payload, SECRET_KEY, algorithm="HS256")

Why this pattern: Minimal change to existing JWT implementation. Frontend receives role for UI decisions.

What NOT to Add

Avoid	Why	Use Instead
FastAPI-Users	Over-engineered for your use case. Brings OAuth, 2FA, cookie auth you don't need.	Custom auth with pwdlib + PyJWT (you already have JWT logic)
Alembic	SQLite migrations are low-risk. You're self-hosted single-user currently.	Direct SQL migration scripts for v2 upgrade
Celery / Redis	Overkill for invite emails. Adds deployment complexity.	FastAPI BackgroundTasks + smtplib
SQLAlchemy-History	You already have requirement_history table with snapshots.	Add modified_by_user_id FK to existing table
PostgreSQL RLS libraries	SQLite doesn't support Row-Level Security.	Manual SQLAlchemy query filters
Casbin (RBAC library)	Policy engine for 100+ permission combinations. You have 3 roles.	Python Enum + FastAPI Depends()

Database Schema Changes

New Tables

-- Users table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    hashed_password TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'viewer',
    is_active BOOLEAN NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Invite tokens table
CREATE TABLE invites (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token TEXT UNIQUE NOT NULL,
    email TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'viewer',
    expires_at TIMESTAMP NOT NULL,
    used_at TIMESTAMP,
    created_by INTEGER REFERENCES users(id)
);

Modified Tables

Add user_id FK to existing tables:

• requirement_nodes - owner of requirement
• cross_pillar_links - who created link
• projects - project owner
• requirement_history - who made the change (modified_by_user_id)
• graph_layouts - who saved layout

Migration strategy: Existing data gets assigned to "system" user (id=1) created during migration.

SQLite Concurrency Considerations

WAL Mode (Already Recommended)

# Ensure WAL mode is enabled
engine = create_engine(
    "sqlite:///data/planner.db",
    connect_args={"check_same_thread": False},
    echo=False
)
with engine.connect() as conn:
    conn.execute(text("PRAGMA journal_mode=WAL"))

Capabilities:

• Multiple concurrent readers (unlimited)
• One writer at a time (sufficient for small team collaboration)
• Writers don't block readers

Limitations:

• No true concurrent writes (BEGIN CONCURRENT is experimental, not production-ready)
• Single writer serialization means write-heavy workloads may see SQLITE_BUSY errors

When to migrate to PostgreSQL: If you exceed ~10 concurrent users with frequent writes (requirements updates). For now, SQLite + WAL handles your use case.

Version Compatibility Matrix

Package	Version	Compatible With	Notes
pwdlib	0.4.0+	Python 3.9+	Requires pwdlib[argon2] extra for Argon2 support
email-validator	2.2.0+	Pydantic 2.x	Required by pydantic[email] extra
PyJWT	2.8.0+	FastAPI 0.100+	Already in your stack - no changes needed
SQLAlchemy	2.0.0+	Python 3.12+	Already in your stack - no changes needed
Pydantic	2.0+	FastAPI 0.100+	Requires email-validator for EmailStr

Migration Checklist

• Install pwdlib and email-validator
• Create users and invites tables
• Add user_id FK to all existing tables
• Migrate existing data to "system" user
• Replace shared password logic with user registration/login
• Add role-based route decorators
• Update JWT payload to include user_id and role
• Add query filters for tenant isolation
• Update frontend to handle user-specific data
• Test multi-user scenarios with WAL mode

Security Checklist

• Use pwdlib.PasswordHash.recommended() for Argon2 defaults
• Validate all user inputs with Pydantic (EmailStr, password length)
• Use secrets.token_urlsafe(32) for invite tokens (256-bit)
• Set invite token expiration (24 hours recommended)
• Filter ALL queries by user_id (tenant isolation)
• Use FastAPI Depends() for role checks on protected routes
• Set JWT expiration (24 hours recommended)
• Enable SQLite WAL mode for concurrent access
• Rate limit registration and login endpoints (future: middleware)
• Use HTTPS in production (Caddy/nginx reverse proxy)

Sources

HIGH Confidence (Official Documentation):

• FastAPI OAuth2 with JWT - Official security tutorial
• pwdlib introduction - Modern password hashing
• Python secrets module - Secure token generation
• Pydantic email validation - EmailStr documentation
• SQLite WAL mode - Official SQLite concurrency documentation

MEDIUM Confidence (Industry Best Practices 2026):

• TestDriven.io FastAPI JWT Auth - Production patterns
• FastAPI RBAC Tutorial - Role-based access control
• Tenant Isolation with SQLAlchemy - Multi-tenancy patterns
• The New Way To Generate Secure Tokens - Token generation security
• FastAPI Dependency Injection - Current user pattern

---

Stack research for: req-planner v2.0 Multi-Tenancy & User Accounts Researched: 2026-03-18 Confidence: HIGH - All recommendations verified against official docs and 2026 best practices