resolutionflow/docs/archive/2026-02-07-foundational-schema-design.md

Foundational Domain Model: Step Library, User Trees, and Session Sharing

Context

This design establishes the foundational schema for three critical ResolutionFlow features that define the durable domain model. All future functionality depends on these tables, fields, and constraints being correctly designed from the start.

Features Covered

1. Step Library Core Schema (Issues #4-#7): Reusable troubleshooting steps with categories, ratings, and usage tracking
2. User Trees & Forking (Issue #11): Personal/forked tree data model for engineer customization
3. Session Sharing (Issue #15): Read-only share links with configurable access control

Why These First

These features establish:

• Step Library: Canonical data model for reusable steps before any UI or session integrations
• User Trees: Fork relationships and ownership model before implementing fork/share workflows
• Sharing Schema: Token mechanics and access control before APIs/UI solidify

Getting these schemas right now prevents costly migrations and refactoring later.

---

Part 1: Tree Forking Model

Design Principle

Forked trees are regular trees with added fork metadata. They use the same ownership/visibility model as existing trees:

• author_id: Engineer who created the fork
• account_id: Account that owns the fork
• is_public / visibility: Controls who can see/use it

Schema Changes: New Fields on trees Table

Add three fields to track fork relationships:

# Fork relationship tracking
parent_tree_id: Mapped[Optional[uuid.UUID]] = mapped_column(
    UUID(as_uuid=True),
    ForeignKey("trees.id", ondelete="SET NULL"),  # Orphan forks on parent delete
    nullable=True,
    index=True
)

fork_reason: Mapped[Optional[str]] = mapped_column(
    String(255),
    nullable=True,
    comment="Brief reason: 'Added Cisco Meraki steps for our network'"
)

parent_updated_at: Mapped[Optional[datetime]] = mapped_column(
    DateTime(timezone=True),
    nullable=True,
    comment="Snapshot of parent's updated_at when fork created. Compare to detect parent updates."
)

New Relationships on Tree Model

# In Tree class:
parent: Mapped[Optional["Tree"]] = relationship(
    "Tree",
    remote_side=[id],
    foreign_keys=[parent_tree_id],
    back_populates="forks"
)

forks: Mapped[list["Tree"]] = relationship(
    "Tree",
    foreign_keys=[parent_tree_id],
    back_populates="parent"
    # No cascade - ondelete="SET NULL" handles orphaning at DB level
)

Field Explanations

Field	Type	Purpose
parent_tree_id	UUID nullable	NULL = root tree, not-NULL = forked tree. Points to original tree.
fork_reason	String(255)	Optional engineer note: "Added wireless troubleshooting for our Meraki APs"
parent_updated_at	Datetime nullable	Timestamp snapshot. Compare to parent's actual updated_at to detect changes.

Key Behaviors

Fork Creation:

1. Copy parent's tree_structure JSONB
2. Set parent_tree_id to parent's ID
3. Set fork_reason from user input
4. Snapshot parent_updated_at = parent.updated_at
5. Set author_id = current_user.id
6. Set account_id = current_user.account_id
7. Default is_public = False (private fork)
8. Reset version = 1 for fork

Fork Updates:

• Engineer edits fork → version increments
• Parent tree unaffected
• parent_updated_at remains frozen at fork time

Parent Deletion:

• Hard delete → parent_tree_id becomes NULL (orphaned)
• Fork survives as independent tree
• Soft delete → parent_tree_id preserved, fork still references parent

Update Notifications:

def has_parent_updates(fork: Tree) -> bool:
    """Check if parent tree has updates since fork."""
    if not fork.parent_tree_id or not fork.parent_updated_at:
        return False

    parent = db.query(Tree).get(fork.parent_tree_id)
    if not parent:
        return False  # Parent deleted or soft-deleted

    return parent.updated_at > fork.parent_updated_at

Migration 1: Tree Forking Fields

File: alembic/versions/022_add_tree_forking.py

def upgrade():
    # Add fork tracking columns
    op.add_column('trees', sa.Column('parent_tree_id', UUID, nullable=True))
    op.add_column('trees', sa.Column('fork_reason', sa.String(255), nullable=True))
    op.add_column('trees', sa.Column('parent_updated_at', sa.DateTime(timezone=True), nullable=True))

    # Add foreign key
    op.create_foreign_key(
        'fk_trees_parent_tree_id',
        'trees', 'trees',
        ['parent_tree_id'], ['id'],
        ondelete='SET NULL'
    )

    # Add index for fork queries
    op.create_index('ix_trees_parent_tree_id', 'trees', ['parent_tree_id'])

def downgrade():
    op.drop_index('ix_trees_parent_tree_id')
    op.drop_constraint('fk_trees_parent_tree_id', 'trees')
    op.drop_column('trees', 'parent_updated_at')
    op.drop_column('trees', 'fork_reason')
    op.drop_column('trees', 'parent_tree_id')

---

Part 2: Session Custom Steps Enhancement

Current State

sessions.custom_steps already exists as JSONB field. Current structure:

{
  "custom_steps": [
    {
      "type": "action",
      "content": "Check Meraki dashboard for AP status",
      "notes": "Found AP offline"
    }
  ]
}

Enhanced Structure (Backward Compatible)

No migration needed - pure JSONB enhancement:

{
  "custom_steps": [
    {
      "type": "action",
      "content": "Check Meraki dashboard for AP status",
      "notes": "Found AP offline",

      // NEW FIELDS (optional, added to new sessions only):
      "source": "ad-hoc",  // "ad-hoc" | "step-library" | "forked-tree"
      "source_step_id": null,  // UUID string if from StepLibrary
      "inserted_at": "2026-02-07T15:30:00Z",  // ISO datetime
      "inserted_after_node_id": "ad_verify_identity"  // Node ID from tree_structure
    },
    {
      "type": "action",
      "content": "Verify DNS configuration",
      "notes": "DNS servers correct",
      "source": "step-library",
      "source_step_id": "123e4567-e89b-12d3-a456-426614174000",
      "inserted_at": "2026-02-07T15:32:00Z",
      "inserted_after_node_id": "network_check"
    }
  ]
}

New Field Definitions

Field	Type	Purpose
source	string	Origin: ad-hoc (typed by engineer), step-library (from library), forked-tree (from fork)
source_step_id	UUID string	If source=step-library, points to StepLibrary.id for usage tracking
inserted_at	ISO datetime	When engineer added this step to session
inserted_after_node_id	string	Node ID from tree_structure where step was inserted (e.g., "ad_verify_identity")

Use Cases

1. Step Library Usage Tracking

• Engineer inserts step from library → logs to step_usage_log
• Increments StepLibrary.usage_count
• Even if step later deleted, source_step_id preserved (orphaned reference for analytics)

2. Identify Popular Ad-Hoc Steps

• Analytics query: "What ad-hoc steps appear across multiple sessions?"
• Example: "Check Meraki dashboard" appears 15 times → suggest adding to library
• Helps identify gaps in official step library

3. Save Session as Tree (Future)

• Engineer completes session with custom steps
• "Save as Tree" reconstructs tree structure
• Uses inserted_after_node_id to place custom steps correctly

Backward Compatibility Strategy

Pydantic schema (backend):

class CustomStepSchema(BaseModel):
    type: str  # "decision" | "action" | "solution"
    content: str
    notes: Optional[str] = None

    # New fields with defaults for old sessions:
    source: str = "ad-hoc"
    source_step_id: Optional[UUID] = None
    inserted_at: Optional[datetime] = None
    inserted_after_node_id: Optional[str] = None

Result: Old sessions load without error. New fields auto-populate with defaults in-memory. Database unchanged.

Step Library Reference Handling

When StepLibrary entry deleted:

• source_step_id remains in sessions (orphaned UUID)
• No foreign key constraint (it's in JSONB)
• Analytics can still count: "Deleted step XYZ was used 50 times historically"
• Frontend shows: "From step library (no longer available)"

Usage Tracking Implementation

When engineer inserts library step into session:

async def insert_step_from_library(
    session_id: UUID,
    step_id: UUID,
    current_node_id: str,
    current_user: User,
    db: AsyncSession
):
    # 1. Load step from library
    step = await db.get(StepLibrary, step_id)

    # 2. Add to session.custom_steps
    new_step = {
        "type": step.step_type,
        "content": step.content,
        "notes": "",
        "source": "step-library",
        "source_step_id": str(step.id),
        "inserted_at": datetime.now(timezone.utc).isoformat(),
        "inserted_after_node_id": current_node_id
    }

    session.custom_steps.append(new_step)

    # 3. Log usage immediately
    usage_log = StepUsageLog(
        step_id=step.id,
        user_id=current_user.id,
        session_id=session.id,
        used_at=datetime.now(timezone.utc)
    )
    db.add(usage_log)

    # 4. Increment counter
    step.usage_count += 1

    await db.commit()

Multiple insertions: Same step inserted twice in one session → 2 StepUsageLog entries, 2 items in custom_steps array, usage_count += 2.

No Migration Required

✅ This is pure application-layer enhancement. Existing sessions remain valid. New sessions use enhanced structure.

---

Part 3: Session Share Tokens

Overview

Enable engineers to share read-only session links with configurable access control:

• Public shares: Anyone with link can view (no auth required)
• Account-only shares: Requires login + account membership

Account owners control whether engineers can create public shares via policy setting.

New Table 1: session_shares

class SessionShare(Base):
    __tablename__ = "session_shares"
    __table_args__ = (
        CheckConstraint(
            "visibility IN ('public', 'account')",
            name='ck_session_shares_visibility'
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        primary_key=True,
        default=uuid.uuid4
    )

    session_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("sessions.id", ondelete="CASCADE"),
        nullable=False,
        index=True
    )

    share_token: Mapped[str] = mapped_column(
        String(64),
        unique=True,
        nullable=False,
        index=True,
        comment="URL-safe random token (48 bytes → 64 base64 chars)"
    )

    share_name: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="Optional label: 'Training link', 'Customer escalation #1234'"
    )

    visibility: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        default="public",
        comment="public = anyone with link, account = account members only"
    )

    created_by: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=False,
        index=True  # For "My Shares" view performance
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc)
    )

    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc)
    )

    expires_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True),
        nullable=True,
        index=True,
        comment="Optional expiration for time-limited shares"
    )

    # Deprecated: Simple counting replaced by session_share_views table
    view_count: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        default=0
    )

    last_viewed_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True),
        nullable=True
    )

    is_active: Mapped[bool] = mapped_column(
        Boolean,
        nullable=False,
        default=True,
        index=True
    )

    # Relationships
    session: Mapped["Session"] = relationship("Session", back_populates="shares")
    creator: Mapped["User"] = relationship("User", foreign_keys=[created_by])
    views: Mapped[list["SessionShareView"]] = relationship(
        "SessionShareView",
        back_populates="share",
        cascade="all, delete-orphan"
    )

New Table 2: session_share_views

Track detailed view analytics, including WHO viewed account-only shares:

class SessionShareView(Base):
    __tablename__ = "session_share_views"

    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        primary_key=True,
        default=uuid.uuid4
    )

    share_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("session_shares.id", ondelete="CASCADE"),
        nullable=False,
        index=True
    )

    viewer_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("users.id", ondelete="SET NULL"),
        nullable=True,
        index=True,
        comment="NULL for public shares (unauthenticated views)"
    )

    viewed_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        index=True
    )

    viewer_ip: Mapped[Optional[str]] = mapped_column(
        String(45),  # IPv6 max length
        nullable=True
    )

    viewer_user_agent: Mapped[Optional[str]] = mapped_column(
        String(500),
        nullable=True
    )

    # Relationships
    share: Mapped["SessionShare"] = relationship("SessionShare", back_populates="views")
    viewer: Mapped[Optional["User"]] = relationship("User")

Account-Level Sharing Policy

Add to accounts table:

# In Account model:
allow_public_shares: Mapped[bool] = mapped_column(
    Boolean,
    nullable=False,
    default=True,
    comment="Policy: engineers can create public shares. Only affects NEW shares (grandfathered)."
)

Policy enforcement (at share creation time only):

# In create_share endpoint:
if visibility == "public" and not current_user.account.allow_public_shares:
    raise HTTPException(
        status_code=403,
        detail="Your organization does not allow public session sharing. Use account-only visibility."
    )

Existing shares: If account owner toggles policy OFF, existing public shares remain active (grandfathered). Owner can manually revoke individual shares if needed.

Share Token Generation

Requirements:

• URL-safe (no special chars)
• Cryptographically random (non-guessable)
• Collision-resistant

Implementation:

import secrets
from sqlalchemy.exc import IntegrityError

async def create_session_share(
    db: AsyncSession,
    session_id: UUID,
    created_by: UUID,
    visibility: str,
    share_name: Optional[str] = None,
    expires_at: Optional[datetime] = None,
    max_retries: int = 3
) -> SessionShare:
    """Create share with automatic token collision retry."""

    for attempt in range(max_retries):
        try:
            share_token = secrets.token_urlsafe(48)  # 48 bytes → 64 chars

            share = SessionShare(
                session_id=session_id,
                share_token=share_token,
                share_name=share_name,
                visibility=visibility,
                created_by=created_by,
                expires_at=expires_at
            )

            db.add(share)
            await db.commit()
            await db.refresh(share)
            return share

        except IntegrityError as e:
            if "session_shares_share_token_key" in str(e):
                # Token collision (extremely rare), retry
                await db.rollback()
                if attempt == max_retries - 1:
                    raise
                continue
            else:
                raise

Share URL format:

https://patherly.com/share/x3K9mN_2pQ7vR8sT4wZ1aB5cD6eF7gH8iJ9kL0mN

Access Control Flow

When user visits share link:

async def access_share(
    token: str,
    current_user: Optional[User],
    request: Request,
    db: AsyncSession
) -> SessionResponse:
    # 1. Lookup share
    share = await db.execute(
        select(SessionShare)
        .where(SessionShare.share_token == token)
        .options(joinedload(SessionShare.session))
    )
    share = share.scalar_one_or_none()

    # 2. Validate share
    if not share or not share.is_active:
        raise HTTPException(404, "Share not found or has been revoked")

    if share.expires_at and share.expires_at < datetime.now(timezone.utc):
        raise HTTPException(410, "Share link has expired")

    # 3. Check visibility
    if share.visibility == "account":
        if not current_user:
            raise HTTPException(401, "This share requires authentication")

        # Check account membership
        session_owner = await db.get(User, share.session.user_id)
        if current_user.account_id != session_owner.account_id:
            raise HTTPException(403, "You don't have access to this session")

    # 4. Record view
    view = SessionShareView(
        share_id=share.id,
        viewer_id=current_user.id if current_user else None,
        viewed_at=datetime.now(timezone.utc),
        viewer_ip=request.client.host,
        viewer_user_agent=request.headers.get("user-agent")
    )
    db.add(view)

    share.last_viewed_at = datetime.now(timezone.utc)
    await db.commit()

    # 5. Return read-only session view
    return SessionResponse.from_orm(share.session)

Share Management Permissions

Create share: POST /api/v1/sessions/{session_id}/shares

• Requires: session.user_id == current_user.id (session owner only)
• Validates: account.allow_public_shares if visibility='public'

Revoke share: DELETE /api/v1/shares/{share_id}

• Requires: share.created_by == current_user.id (share creator only)
• Sets is_active = False (soft revoke)

List my shares: GET /api/v1/shares/my-shares

• Returns: WHERE created_by == current_user.id
• Uses created_by index for performance

Multiple Shares Per Session

✅ Supported: No unique constraint on session_id. Engineer can create:

• Public link for customer
• Account-only link for internal team
• Time-limited link (24h) for contractor
• Named links: "Training link for new hires", "Escalation to senior"

Cleanup Job for Expired Shares

Background task (runs daily via cron/scheduler):

async def cleanup_expired_shares(
    db: AsyncSession,
    retention_days: int = 30
):
    """Hard-delete expired shares after retention period."""

    cutoff = datetime.now(timezone.utc) - timedelta(days=retention_days)

    # Find shares expired > retention_days ago
    expired_shares = await db.execute(
        select(SessionShare)
        .where(SessionShare.expires_at < cutoff)
        .where(SessionShare.is_active == False)
    )

    for share in expired_shares.scalars():
        await db.delete(share)  # Cascades to session_share_views

    await db.commit()

    logger.info(f"Cleaned up {len(list(expired_shares))} expired shares")

Migration 2: Session Sharing

File: alembic/versions/023_add_session_sharing.py

def upgrade():
    # Create session_shares table
    op.create_table(
        'session_shares',
        sa.Column('id', UUID, primary_key=True),
        sa.Column('session_id', UUID, nullable=False),
        sa.Column('share_token', sa.String(64), nullable=False, unique=True),
        sa.Column('share_name', sa.String(100), nullable=True),
        sa.Column('visibility', sa.String(20), nullable=False),
        sa.Column('created_by', UUID, nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
        sa.Column('expires_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('view_count', sa.Integer, nullable=False, server_default='0'),
        sa.Column('last_viewed_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('is_active', sa.Boolean, nullable=False, server_default='true'),
        sa.ForeignKeyConstraint(['session_id'], ['sessions.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['created_by'], ['users.id'], ondelete='CASCADE'),
        sa.CheckConstraint("visibility IN ('public', 'account')", name='ck_session_shares_visibility')
    )

    # Create indexes
    op.create_index('ix_session_shares_session_id', 'session_shares', ['session_id'])
    op.create_index('ix_session_shares_share_token', 'session_shares', ['share_token'])
    op.create_index('ix_session_shares_created_by', 'session_shares', ['created_by'])
    op.create_index('ix_session_shares_expires_at', 'session_shares', ['expires_at'])
    op.create_index('ix_session_shares_is_active', 'session_shares', ['is_active'])

    # Create session_share_views table
    op.create_table(
        'session_share_views',
        sa.Column('id', UUID, primary_key=True),
        sa.Column('share_id', UUID, nullable=False),
        sa.Column('viewer_id', UUID, nullable=True),
        sa.Column('viewed_at', sa.DateTime(timezone=True), nullable=False),
        sa.Column('viewer_ip', sa.String(45), nullable=True),
        sa.Column('viewer_user_agent', sa.String(500), nullable=True),
        sa.ForeignKeyConstraint(['share_id'], ['session_shares.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['viewer_id'], ['users.id'], ondelete='SET NULL')
    )

    # Create indexes
    op.create_index('ix_session_share_views_share_id', 'session_share_views', ['share_id'])
    op.create_index('ix_session_share_views_viewer_id', 'session_share_views', ['viewer_id'])
    op.create_index('ix_session_share_views_viewed_at', 'session_share_views', ['viewed_at'])

    # Add account policy
    op.add_column('accounts', sa.Column('allow_public_shares', sa.Boolean, nullable=False, server_default='true'))

def downgrade():
    op.drop_column('accounts', 'allow_public_shares')
    op.drop_table('session_share_views')
    op.drop_table('session_shares')

---

Summary of Schema Changes

Migration 022: Tree Forking

New columns on trees:

• parent_tree_id (UUID nullable, FK to trees.id, SET NULL on delete)
• fork_reason (String 255)
• parent_updated_at (Datetime nullable)

Indexes:

• ix_trees_parent_tree_id

Migration 023: Session Sharing

New tables:

• session_shares (13 columns, 5 indexes)
• session_share_views (6 columns, 3 indexes)

New columns on accounts:

• allow_public_shares (Boolean, default true)

No Migration: Custom Steps Enhancement

Pure JSONB enhancement with backward compatibility. Existing sessions remain valid.

---

Implementation Checklist

Phase 1: Tree Forking (Issues #11)

• Create migration 022 (tree forking fields)
• Update Tree model with new fields and relationships
• Add fork creation endpoint: POST /api/v1/trees/{tree_id}/fork
• Add fork list endpoint: GET /api/v1/trees/{tree_id}/forks
• Implement parent update detection logic
• Frontend: Fork button on tree detail page
• Frontend: "Parent updated" notification badge
• Tests: Fork creation, orphaning, update detection

Phase 2: Custom Steps Enhancement (Issues #4-#7 partial)

• Update CustomStepSchema with new optional fields
• Implement step insertion from library endpoint
• Create StepUsageLog entry on insertion
• Increment StepLibrary.usage_count
• Analytics endpoint: Popular ad-hoc steps
• Frontend: Insert from library button in session
• Tests: Usage tracking, backward compatibility

Phase 3: Session Sharing (Issue #15)

• Create migration 023 (session sharing tables)
• Update SessionShare and SessionShareView models
• Add Account.allow_public_shares field
• Implement share creation endpoint with token retry
• Implement share access endpoint with view tracking
• Implement share revocation endpoint
• Setup cleanup job for expired shares
• Frontend: Share button in session detail
• Frontend: Share management modal
• Frontend: Public share view page
• Tests: Share creation, access control, expiration

---

Verification Plan

Tree Forking Tests

• Create fork from tree → parent_tree_id set correctly
• Update parent tree → fork unaffected
• Delete parent tree (hard) → fork orphaned (parent_tree_id = NULL)
• Soft delete parent → fork still references parent
• has_parent_updates() returns true after parent updated
• has_parent_updates() returns false if parent unchanged
• Fork reason stored and retrieved correctly

Custom Steps Tests

• Old session loads without error (backward compat)
• Insert step from library → source_step_id populated
• Insert step from library → StepUsageLog created
• Insert step from library → usage_count incremented
• Insert same step twice → 2 log entries, count += 2
• Delete step → source_step_id remains (orphaned)
• Ad-hoc step → source = "ad-hoc", source_step_id = null

Session Sharing Tests

• Create public share → token generated, unique
• Access public share without auth → works
• Create account-only share → requires auth
• Access account-only share as outsider → 403 error
• Access account-only share as member → works, view logged
• Account with allow_public_shares=False → cannot create public share
• Expired share → 410 error
• Revoked share → 404 error
• Multiple shares per session → all work independently
• View tracking → SessionShareView entries created
• Delete session → shares cascade deleted

---

Future Enhancements (Out of Scope)

Tree Forking:

• Merge fork changes back to parent (pull request model)
• Visual diff view: fork vs parent tree
• Bulk fork operations: fork all trees in category

Custom Steps:

• One-click "Promote to library" for ad-hoc steps
• Auto-suggest library steps based on session context
• "Save session as tree" reconstruction

Session Sharing:

• Embed shares in iframe (oEmbed support)
• Password-protected shares
• Share groups (one link, multiple sessions)
• Share templates with pre-filled expiration/visibility

---

References

• Issues: #4, #5, #6, #7 (Step Library), #11 (User Trees), #15 (Sharing)
• Existing Models: Tree, Session, StepLibrary, StepRating, StepUsageLog
• Feature Brainstorm: docs/plans/2026-02-04-feature-ideas-brainstorm.md
• Tree Structure: backend/scripts/seed_trees.py (examples)