Files

chihlasm 2837c6e4cf docs: add Phase 1 tenant isolation schema migrations implementation plan

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

2026-04-09 04:58:24 +00:00

89 KiB

Raw Permalink Blame History

Tenant Isolation — Phase 1 Schema Migrations

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Add account_id to every tenant table that lacks it, backfill from existing FK chains, enforce NOT NULL, and create the global content tables (template_trees, platform_steps) that replace the legacy is_default/visibility='public' patterns.

Architecture: Each task is one Alembic migration file covering one logical domain group. Every migration follows the non-negotiable sequence: ADD nullable → backfill → verify zero NULLs → SET NOT NULL → CREATE INDEX. Any migration that cannot zero-out NULLs at step 3 must roll back in full — no partial state. RLS is NOT enabled in this phase. get_db() is NOT modified. Schema only.

Tech Stack: Python 3.11 · FastAPI · SQLAlchemy 2.0 async · Alembic · PostgreSQL 16 · pytest-asyncio

Spec: docs/superpowers/specs/2026-04-09-tenant-data-isolation-design.md

Prerequisite: Phase 0 merged to main (PRs #131 + #132 ✓). Alembic current head: b8d2f4a6c091.

Task ordering note: Task 9 (global content separation) runs before Task 10 (SET NOT NULL on trees/categories/tags/steps). This is a dependency: is_default=TRUE trees have account_id=NULL and cannot satisfy the zero-NULL check until they are moved to template_trees.

File Map

File	Action	Group
`backend/alembic/versions/<hash>_add_account_id_core_sessions.py`	Create	1
`backend/alembic/versions/<hash>_add_account_id_ai_branching.py`	Create	2
`backend/alembic/versions/<hash>_add_account_id_step_ratings.py`	Create	3
`backend/alembic/versions/<hash>_add_account_id_user_personalization.py`	Create	4
`backend/alembic/versions/<hash>_add_account_id_psa_notifications.py`	Create	5
`backend/alembic/versions/<hash>_add_account_id_maintenance.py`	Create	6
`backend/alembic/versions/<hash>_add_account_id_script_tables.py`	Create	7
`backend/alembic/versions/<hash>_add_account_id_target_lists.py`	Create	8
`backend/alembic/versions/<hash>_create_global_content_tables.py`	Create	9
`backend/alembic/versions/<hash>_set_not_null_account_id_phase1.py`	Create	10
`backend/app/models/session.py`	Modify	1
`backend/app/models/attachment.py`	Modify	1
`backend/app/models/supporting_data.py`	Modify	1
`backend/app/models/session_resolution_output.py`	Modify	1
`backend/app/models/session_branch.py`	Modify	2
`backend/app/models/session_handoff.py`	Modify	2
`backend/app/models/fork_point.py`	Modify	2
`backend/app/models/ai_session_step.py`	Modify	2
`backend/app/models/ai_suggestion.py`	Modify	2
`backend/app/models/step_library.py`	Modify	3 (StepRating, StepUsageLog)
`backend/app/models/folder.py`	Modify	4
`backend/app/models/user_pinned_tree.py`	Modify	4
`backend/app/models/psa_post_log.py`	Modify	5
`backend/app/models/psa_member_mapping.py`	Modify	5
`backend/app/models/notification_log.py`	Modify	5
`backend/app/models/maintenance_schedule.py`	Modify	6
`backend/app/models/script_builder_session.py`	Modify	7
`backend/app/models/script_template.py`	Modify	7 (ScriptTemplate, ScriptGeneration)
`backend/app/models/target_list.py`	Modify	8
`backend/app/models/template_tree.py`	Create	9
`backend/app/models/platform_step.py`	Create	9
`backend/app/models/user.py`	Modify	10
`backend/app/models/tree.py`	Modify	10
`backend/app/models/category.py`	Modify	10
`backend/app/models/tag.py`	Modify	10
`backend/app/models/step_category.py`	Modify	10
`backend/app/models/step_library.py`	Modify	10 (StepLibrary account_id NOT NULL)
`backend/app/models/tree_embedding.py`	Modify	10
`backend/app/models/feedback.py`	Modify	10
`backend/tests/test_phase1_migrations.py`	Create	all tasks

Task 1: Group 1 — Core sessions

Tables: sessions, attachments, session_supporting_data, session_resolution_outputs

Backfill paths:

sessions: sessions.user_id → users.account_id
attachments: attachments.session_id → sessions.account_id (chain — sessions must be backfilled first in same migration)
session_supporting_data: same chain as attachments
session_resolution_outputs: session_resolution_outputs.session_id → ai_sessions.account_id (FK is to ai_sessions, not sessions)

Files:

Create: backend/alembic/versions/<hash>_add_account_id_core_sessions.py
Modify: backend/app/models/session.py, attachment.py, supporting_data.py, session_resolution_output.py
Test: backend/tests/test_phase1_migrations.py

Step 1.1: Create the branch

git checkout main && git pull origin main
git checkout -b feat/tenant-isolation-phase-1

Step 1.2: Write the failing test

Create backend/tests/test_phase1_migrations.py:

"""Phase 1 migration tests — verify account_id backfill correctness.

These tests create objects via ORM (which uses the updated models),
then verify account_id is populated correctly. They run against a
real PostgreSQL test DB (same as all other integration tests).
"""
import pytest
import uuid
from httpx import AsyncClient
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, text

from app.models.account import Account
from app.models.user import User
from app.models.tree import Tree
from app.models.session import Session
from app.models.attachment import Attachment
from app.models.supporting_data import SessionSupportingData
from app.models.session_resolution_output import SessionResolutionOutput
from app.models.ai_session import AISession
from app.core.security import get_password_hash


# ── Helpers ──────────────────────────────────────────────────────────────────

async def _make_account_and_user(db: AsyncSession, suffix: str) -> tuple[Account, User]:
    account = Account(name=f"Corp {suffix}", display_code=uuid.uuid4().hex[:8])
    db.add(account)
    await db.flush()
    user = User(
        email=f"user-{suffix}-{uuid.uuid4().hex[:6]}@example.com",
        name=f"User {suffix}",
        password_hash=get_password_hash("TestPass123!"),
        is_active=True,
        account_id=account.id,
        account_role="engineer",
    )
    db.add(user)
    await db.flush()
    return account, user


async def _make_tree(db: AsyncSession, account: Account, user: User) -> Tree:
    tree = Tree(
        name=f"Tree {uuid.uuid4().hex[:6]}",
        account_id=account.id,
        author_id=user.id,
        visibility="team",
        tree_type="troubleshooting",
        tree_structure={"id": "root", "type": "start", "children": []},
        is_active=True,
        status="published",
    )
    db.add(tree)
    await db.flush()
    return tree


async def _make_session(db: AsyncSession, account: Account, user: User, tree: Tree) -> Session:
    s = Session(
        tree_id=tree.id,
        user_id=user.id,
        account_id=account.id,
        tree_snapshot={},
    )
    db.add(s)
    await db.flush()
    return s


# ── Group 1: Core sessions ────────────────────────────────────────────────────

@pytest.mark.asyncio
async def test_session_account_id_matches_user(test_db: AsyncSession):
    """sessions.account_id must equal the user's account_id."""
    account, user = await _make_account_and_user(test_db, "s1")
    tree = await _make_tree(test_db, account, user)
    session = await _make_session(test_db, account, user, tree)
    await test_db.commit()

    result = await test_db.execute(select(Session).where(Session.id == session.id))
    row = result.scalar_one()
    assert row.account_id == account.id, f"Expected {account.id}, got {row.account_id}"


@pytest.mark.asyncio
async def test_attachment_account_id_matches_session(test_db: AsyncSession):
    """attachments.account_id must match the parent session's account_id."""
    account, user = await _make_account_and_user(test_db, "att1")
    tree = await _make_tree(test_db, account, user)
    session = await _make_session(test_db, account, user, tree)

    attachment = Attachment(
        session_id=session.id,
        account_id=account.id,
        file_name="test.png",
        file_type="image/png",
    )
    test_db.add(attachment)
    await test_db.commit()

    result = await test_db.execute(select(Attachment).where(Attachment.id == attachment.id))
    row = result.scalar_one()
    assert row.account_id == account.id


@pytest.mark.asyncio
async def test_session_supporting_data_account_id(test_db: AsyncSession):
    """session_supporting_data.account_id must match parent session's account_id."""
    account, user = await _make_account_and_user(test_db, "sd1")
    tree = await _make_tree(test_db, account, user)
    session = await _make_session(test_db, account, user, tree)

    sd = SessionSupportingData(
        session_id=session.id,
        account_id=account.id,
        label="Log snippet",
        data_type="text_snippet",
        content="error: connection refused",
    )
    test_db.add(sd)
    await test_db.commit()

    result = await test_db.execute(
        select(SessionSupportingData).where(SessionSupportingData.id == sd.id)
    )
    row = result.scalar_one()
    assert row.account_id == account.id

Step 1.3: Run test to confirm it fails (model doesn't have account_id yet)

cd backend && python -m pytest tests/test_phase1_migrations.py::test_session_account_id_matches_user -v --override-ini="addopts="

Expected: FAIL — Session model has no account_id attribute.

Step 1.4: Generate the Alembic migration file

cd backend && alembic revision -m "add_account_id_core_sessions"

This prints a path like alembic/versions/xxxx_add_account_id_core_sessions.py. Open that file and replace its contents with:

"""add account_id to core session tables

Revision ID: <GENERATED — keep as-is from file>
Revises: b8d2f4a6c091
Create Date: <keep as-is>
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = '<GENERATED>'
down_revision: Union[str, None] = 'b8d2f4a6c091'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ── Step 1: ADD COLUMN (nullable) ────────────────────────────────────────
    for table in ('sessions', 'attachments', 'session_supporting_data',
                  'session_resolution_outputs'):
        op.add_column(table, sa.Column('account_id', sa.UUID(), nullable=True))
        op.create_foreign_key(
            f'fk_{table}_account_id',
            table, 'accounts',
            ['account_id'], ['id'],
            ondelete='CASCADE',
        )

    # ── Step 2: BACKFILL ─────────────────────────────────────────────────────
    # sessions: direct join to users
    op.execute("""
        UPDATE sessions s
        SET account_id = u.account_id
        FROM users u
        WHERE s.user_id = u.id
          AND s.account_id IS NULL
    """)

    # attachments: chain through sessions (now backfilled above)
    op.execute("""
        UPDATE attachments a
        SET account_id = s.account_id
        FROM sessions s
        WHERE a.session_id = s.id
          AND a.account_id IS NULL
    """)

    # session_supporting_data: same chain
    op.execute("""
        UPDATE session_supporting_data sd
        SET account_id = s.account_id
        FROM sessions s
        WHERE sd.session_id = s.id
          AND sd.account_id IS NULL
    """)

    # session_resolution_outputs: FK is to ai_sessions, not sessions
    op.execute("""
        UPDATE session_resolution_outputs sro
        SET account_id = ai.account_id
        FROM ai_sessions ai
        WHERE sro.session_id = ai.id
          AND sro.account_id IS NULL
    """)

    # ── Step 3: VERIFY zero NULLs — raises if any remain ────────────────────
    for table in ('sessions', 'attachments', 'session_supporting_data',
                  'session_resolution_outputs'):
        result = op.get_bind().execute(
            sa.text(f"SELECT COUNT(*) FROM {table} WHERE account_id IS NULL")
        )
        count = result.scalar()
        if count > 0:
            raise RuntimeError(
                f"ROLLBACK: {count} NULL account_id rows remain in {table}. "
                f"Fix the backfill before re-running."
            )

    # ── Step 4: SET NOT NULL ─────────────────────────────────────────────────
    for table in ('sessions', 'attachments', 'session_supporting_data',
                  'session_resolution_outputs'):
        op.alter_column(table, 'account_id', nullable=False)

    # ── Step 5: CREATE INDEX ─────────────────────────────────────────────────
    for table in ('sessions', 'attachments', 'session_supporting_data',
                  'session_resolution_outputs'):
        op.create_index(f'ix_{table}_account_id', table, ['account_id'])


def downgrade() -> None:
    for table in ('sessions', 'attachments', 'session_supporting_data',
                  'session_resolution_outputs'):
        op.drop_index(f'ix_{table}_account_id', table_name=table)
        op.drop_constraint(f'fk_{table}_account_id', table, type_='foreignkey')
        op.drop_column(table, 'account_id')

Important: The down_revision must be b8d2f4a6c091 (current head). Do NOT change the auto-generated revision value at the top.

Step 1.5: Run the migration against the test database

cd backend && alembic upgrade head

Expected: Running upgrade b8d2f4a6c091 -> <new_hash>, add account_id to core session tables

If it errors with "NULL rows remain", investigate the backfill SQL — there are rows whose users have NULL account_id.

Step 1.6: Verify zero NULLs manually

cd backend && python -c "
import asyncio
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy import text
import os

async def check():
    url = os.environ.get('DATABASE_URL', 'postgresql+asyncpg://postgres:postgres@localhost:5432/resolutionflow_test')
    engine = create_async_engine(url)
    async with engine.connect() as conn:
        for t in ('sessions', 'attachments', 'session_supporting_data', 'session_resolution_outputs'):
            r = await conn.execute(text(f'SELECT COUNT(*) FROM {t} WHERE account_id IS NULL'))
            print(f'{t}: {r.scalar()} NULLs')
    await engine.dispose()

asyncio.run(check())
"

Expected output (all zeros):

sessions: 0 NULLs
attachments: 0 NULLs
session_supporting_data: 0 NULLs
session_resolution_outputs: 0 NULLs

Step 1.7: Update SQLAlchemy models

In backend/app/models/session.py, add after the user_id column (around line 33):

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

In backend/app/models/attachment.py, add after the session_id column (around line 22):

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

In backend/app/models/supporting_data.py, add after session_id (around line 16):

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

In backend/app/models/session_resolution_output.py, add after session_id (around line 25):

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

Each model also needs from app.models.account import Account added if missing from TYPE_CHECKING block.

Step 1.8: Run tests

cd backend && python -m pytest tests/test_phase1_migrations.py -k "test_session or test_attachment or test_session_supporting" -v --override-ini="addopts="

Expected: all 3 tests PASS.

Step 1.9: Run full test suite

cd backend && python -m pytest --override-ini="addopts="

Expected: all tests pass (no regressions from model changes).

Step 1.10: Commit

git add backend/alembic/versions/*add_account_id_core_sessions* \
        backend/app/models/session.py \
        backend/app/models/attachment.py \
        backend/app/models/supporting_data.py \
        backend/app/models/session_resolution_output.py \
        backend/tests/test_phase1_migrations.py
git commit -m "feat: Phase 1 Group 1 — add account_id to core session tables

Migration sequence: add nullable → backfill via user_id/ai_session chain
→ verify zero NULLs → SET NOT NULL → CREATE INDEX.

Tables: sessions, attachments, session_supporting_data,
        session_resolution_outputs

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>"

Task 2: Group 2 — AI & branching

Tables: session_branches, session_handoffs, fork_points, ai_session_steps, ai_suggestions

Backfill paths:

session_branches, session_handoffs, fork_points, ai_session_steps: all have session_id → ai_sessions.account_id
ai_suggestions: user_id → users.account_id

Files:

Create: backend/alembic/versions/<hash>_add_account_id_ai_branching.py
Modify: backend/app/models/session_branch.py, session_handoff.py, fork_point.py, ai_session_step.py, ai_suggestion.py
Test: backend/tests/test_phase1_migrations.py

Step 2.1: Write the failing tests

Append to backend/tests/test_phase1_migrations.py:

# ── Group 2: AI & branching ───────────────────────────────────────────────────

@pytest.mark.asyncio
async def test_session_branch_account_id_matches_ai_session(test_db: AsyncSession):
    """session_branches.account_id must match parent ai_session.account_id."""
    from app.models.session_branch import SessionBranch

    account, user = await _make_account_and_user(test_db, "sb1")
    ai_session = AISession(
        user_id=user.id,
        account_id=account.id,
        problem_summary="test",
        problem_domain="networking",
        status="active",
    )
    test_db.add(ai_session)
    await test_db.flush()

    branch = SessionBranch(
        session_id=ai_session.id,
        account_id=account.id,
        label="Branch A",
        branch_order=1,
        conversation_messages=[],
    )
    test_db.add(branch)
    await test_db.commit()

    result = await test_db.execute(
        select(SessionBranch).where(SessionBranch.id == branch.id)
    )
    row = result.scalar_one()
    assert row.account_id == account.id


@pytest.mark.asyncio
async def test_ai_suggestion_account_id_matches_user(test_db: AsyncSession):
    """ai_suggestions.account_id must match the creating user's account_id."""
    from app.models.ai_suggestion import AISuggestion

    account, user = await _make_account_and_user(test_db, "ais1")
    tree = await _make_tree(test_db, account, user)

    suggestion = AISuggestion(
        tree_id=tree.id,
        user_id=user.id,
        account_id=account.id,
        action_type="add_node",
        changes_json={},
        status="pending",
    )
    test_db.add(suggestion)
    await test_db.commit()

    result = await test_db.execute(
        select(AISuggestion).where(AISuggestion.id == suggestion.id)
    )
    row = result.scalar_one()
    assert row.account_id == account.id

Step 2.2: Run tests to confirm they fail

cd backend && python -m pytest tests/test_phase1_migrations.py::test_session_branch_account_id_matches_ai_session -v --override-ini="addopts="

Expected: FAIL — SessionBranch has no account_id.

Step 2.3: Generate migration

cd backend && alembic revision -m "add_account_id_ai_branching"

Replace the generated file content with:

"""add account_id to AI branching tables

Revision ID: <GENERATED>
Revises: <HASH_FROM_TASK_1>
Create Date: <keep>
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = '<GENERATED>'
down_revision: Union[str, None] = '<HASH_FROM_TASK_1>'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Step 1: ADD COLUMN (nullable)
    ai_tables = ('session_branches', 'session_handoffs', 'fork_points',
                 'ai_session_steps')
    for table in ai_tables:
        op.add_column(table, sa.Column('account_id', sa.UUID(), nullable=True))
        op.create_foreign_key(
            f'fk_{table}_account_id', table, 'accounts',
            ['account_id'], ['id'], ondelete='CASCADE',
        )

    op.add_column('ai_suggestions', sa.Column('account_id', sa.UUID(), nullable=True))
    op.create_foreign_key(
        'fk_ai_suggestions_account_id', 'ai_suggestions', 'accounts',
        ['account_id'], ['id'], ondelete='CASCADE',
    )

    # Step 2: BACKFILL
    # session_branches, session_handoffs, fork_points, ai_session_steps
    # all FK to ai_sessions via session_id
    for table in ai_tables:
        op.execute(f"""
            UPDATE {table} t
            SET account_id = ai.account_id
            FROM ai_sessions ai
            WHERE t.session_id = ai.id
              AND t.account_id IS NULL
        """)

    # ai_suggestions: user_id → users.account_id
    op.execute("""
        UPDATE ai_suggestions s
        SET account_id = u.account_id
        FROM users u
        WHERE s.user_id = u.id
          AND s.account_id IS NULL
    """)

    # Step 3: VERIFY
    for table in ai_tables + ('ai_suggestions',):
        result = op.get_bind().execute(
            sa.text(f"SELECT COUNT(*) FROM {table} WHERE account_id IS NULL")
        )
        count = result.scalar()
        if count > 0:
            raise RuntimeError(
                f"ROLLBACK: {count} NULL account_id rows in {table}."
            )

    # Step 4: SET NOT NULL
    for table in ai_tables + ('ai_suggestions',):
        op.alter_column(table, 'account_id', nullable=False)

    # Step 5: CREATE INDEX
    for table in ai_tables + ('ai_suggestions',):
        op.create_index(f'ix_{table}_account_id', table, ['account_id'])


def downgrade() -> None:
    for table in ('session_branches', 'session_handoffs', 'fork_points',
                  'ai_session_steps', 'ai_suggestions'):
        op.drop_index(f'ix_{table}_account_id', table_name=table)
        op.drop_constraint(f'fk_{table}_account_id', table, type_='foreignkey')
        op.drop_column(table, 'account_id')

Note: Replace <HASH_FROM_TASK_1> with the actual revision hash generated in Task 1 (check the file that was created: revision: str = '...').

Step 2.4: Run migration

cd backend && alembic upgrade head

Step 2.5: Verify zero NULLs

cd backend && python -c "
import asyncio
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy import text
import os

async def check():
    url = os.environ.get('DATABASE_URL', 'postgresql+asyncpg://postgres:postgres@localhost:5432/resolutionflow_test')
    engine = create_async_engine(url)
    async with engine.connect() as conn:
        for t in ('session_branches', 'session_handoffs', 'fork_points', 'ai_session_steps', 'ai_suggestions'):
            r = await conn.execute(text(f'SELECT COUNT(*) FROM {t} WHERE account_id IS NULL'))
            print(f'{t}: {r.scalar()} NULLs')
    await engine.dispose()

asyncio.run(check())
"

Expected: all zeros.

Step 2.6: Update SQLAlchemy models

In each of these files, add account_id as NOT NULL after the session_id or user_id column:

backend/app/models/session_branch.py — add after session_id column (line 37):

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

backend/app/models/session_handoff.py — add after session_id column (line 29):

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

backend/app/models/fork_point.py — add after session_id column (line 25):

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

backend/app/models/ai_session_step.py — add after session_id column (line 52):

    account_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("accounts.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        comment="Denormalized from ai_sessions.account_id for direct tenant filtering.",
    )

backend/app/models/ai_suggestion.py — add after user_id column (line 29):

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

Step 2.7: Run tests, full suite, commit

cd backend && python -m pytest tests/test_phase1_migrations.py -k "branch or suggestion" -v --override-ini="addopts="
cd backend && python -m pytest --override-ini="addopts="
git add backend/alembic/versions/*add_account_id_ai_branching* \
        backend/app/models/session_branch.py \
        backend/app/models/session_handoff.py \
        backend/app/models/fork_point.py \
        backend/app/models/ai_session_step.py \
        backend/app/models/ai_suggestion.py \
        backend/tests/test_phase1_migrations.py
git commit -m "feat: Phase 1 Group 2 — add account_id to AI branching tables

Tables: session_branches, session_handoffs, fork_points,
        ai_session_steps, ai_suggestions
Backfill: session_id → ai_sessions.account_id (all except
ai_suggestions which uses user_id → users.account_id)

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>"

Task 3: Group 3 — Steps & ratings

Tables: step_ratings, step_usage_log

Note: session_ratings ALREADY has account_id NOT NULL — do not touch it.

Backfill paths: Both use user_id → users.account_id (the rating user's account, per design).

Table name: step_usage_log (singular, not plural — check StepUsageLog.__tablename__).

Files:

Create: backend/alembic/versions/<hash>_add_account_id_step_ratings.py
Modify: backend/app/models/step_library.py (StepRating and StepUsageLog classes)
Test: backend/tests/test_phase1_migrations.py

Step 3.1: Write the failing tests

Append to backend/tests/test_phase1_migrations.py:

# ── Group 3: Steps & ratings ──────────────────────────────────────────────────

@pytest.mark.asyncio
async def test_step_rating_account_id_is_rater_account(test_db: AsyncSession):
    """step_ratings.account_id must be the RATER's account, not the step's account."""
    from app.models.step_library import StepLibrary, StepRating

    account_a, user_a = await _make_account_and_user(test_db, "sr-rater")
    account_b, user_b = await _make_account_and_user(test_db, "sr-step-owner")

    # Step owned by account_b
    step = StepLibrary(
        title="A step",
        step_type="action",
        content={"text": "do something"},
        created_by=user_b.id,
        account_id=account_b.id,
        visibility="public",
    )
    test_db.add(step)
    await test_db.flush()

    # user_a (account_a) rates the step
    rating = StepRating(
        step_id=step.id,
        user_id=user_a.id,
        account_id=account_a.id,  # rater's account, not step owner's
        was_helpful=True,
        is_verified_use=False,
        is_visible=True,
    )
    test_db.add(rating)
    await test_db.commit()

    result = await test_db.execute(select(StepRating).where(StepRating.id == rating.id))
    row = result.scalar_one()
    assert row.account_id == account_a.id, (
        f"account_id should be rater's account ({account_a.id}), got {row.account_id}"
    )

Step 3.2: Run test to confirm fail

cd backend && python -m pytest tests/test_phase1_migrations.py::test_step_rating_account_id_is_rater_account -v --override-ini="addopts="

Expected: FAIL — StepRating has no account_id.

Step 3.3: Generate migration

cd backend && alembic revision -m "add_account_id_step_ratings"

Replace file content:

"""add account_id to step_ratings and step_usage_log

Revision ID: <GENERATED>
Revises: <HASH_FROM_TASK_2>
Create Date: <keep>
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = '<GENERATED>'
down_revision: Union[str, None] = '<HASH_FROM_TASK_2>'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    for table in ('step_ratings', 'step_usage_log'):
        op.add_column(table, sa.Column('account_id', sa.UUID(), nullable=True))
        op.create_foreign_key(
            f'fk_{table}_account_id', table, 'accounts',
            ['account_id'], ['id'], ondelete='CASCADE',
        )
        # Backfill: from the RATER/LOGGER user's account (not the step's account)
        op.execute(f"""
            UPDATE {table} t
            SET account_id = u.account_id
            FROM users u
            WHERE t.user_id = u.id
              AND t.account_id IS NULL
        """)
        result = op.get_bind().execute(
            sa.text(f"SELECT COUNT(*) FROM {table} WHERE account_id IS NULL")
        )
        count = result.scalar()
        if count > 0:
            raise RuntimeError(f"ROLLBACK: {count} NULL account_id rows in {table}.")
        op.alter_column(table, 'account_id', nullable=False)
        op.create_index(f'ix_{table}_account_id', table, ['account_id'])


def downgrade() -> None:
    for table in ('step_ratings', 'step_usage_log'):
        op.drop_index(f'ix_{table}_account_id', table_name=table)
        op.drop_constraint(f'fk_{table}_account_id', table, type_='foreignkey')
        op.drop_column(table, 'account_id')

Step 3.4: Run migration and verify

cd backend && alembic upgrade head

cd backend && python -c "
import asyncio
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy import text
import os

async def check():
    url = os.environ.get('DATABASE_URL', 'postgresql+asyncpg://postgres:postgres@localhost:5432/resolutionflow_test')
    engine = create_async_engine(url)
    async with engine.connect() as conn:
        for t in ('step_ratings', 'step_usage_log'):
            r = await conn.execute(text(f'SELECT COUNT(*) FROM {t} WHERE account_id IS NULL'))
            print(f'{t}: {r.scalar()} NULLs')
    await engine.dispose()

asyncio.run(check())
"

Step 3.5: Update SQLAlchemy models in backend/app/models/step_library.py

In the StepRating class (starts around line 125), add after the user_id column:

    account_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("accounts.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        comment="Account of the RATER (not the step owner).",
    )

In the StepUsageLog class (starts around line 172), add after the user_id column:

    account_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("accounts.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        comment="Account of the user who logged this usage.",
    )

Step 3.6: Run tests, full suite, commit

cd backend && python -m pytest tests/test_phase1_migrations.py::test_step_rating_account_id_is_rater_account -v --override-ini="addopts="
cd backend && python -m pytest --override-ini="addopts="
git add backend/alembic/versions/*add_account_id_step_ratings* \
        backend/app/models/step_library.py \
        backend/tests/test_phase1_migrations.py
git commit -m "feat: Phase 1 Group 3 — add account_id to step_ratings and step_usage_log

Backfill from rater/user's account_id (not the step's account_id).
This is an explicit design decision — step rating data is attributed
to the account that performed the rating.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>"

Task 4: Group 4 — User personalization

Tables: user_folders, user_pinned_trees

Backfill: user_id → users.account_id

Files:

Create: backend/alembic/versions/<hash>_add_account_id_user_personalization.py
Modify: backend/app/models/folder.py, backend/app/models/user_pinned_tree.py
Test: backend/tests/test_phase1_migrations.py

Step 4.1: Write failing tests

Append to backend/tests/test_phase1_migrations.py:

# ── Group 4: User personalization ────────────────────────────────────────────

@pytest.mark.asyncio
async def test_user_folder_account_id_matches_user(test_db: AsyncSession):
    """user_folders.account_id must match the owning user's account_id."""
    from app.models.folder import UserFolder

    account, user = await _make_account_and_user(test_db, "uf1")
    folder = UserFolder(
        user_id=user.id,
        account_id=account.id,
        name="My Folder",
        color="#6366f1",
        icon="folder",
        display_order=0,
    )
    test_db.add(folder)
    await test_db.commit()

    result = await test_db.execute(select(UserFolder).where(UserFolder.id == folder.id))
    row = result.scalar_one()
    assert row.account_id == account.id


@pytest.mark.asyncio
async def test_user_pinned_tree_account_id_matches_user(test_db: AsyncSession):
    """user_pinned_trees.account_id must match the pinning user's account_id."""
    from app.models.user_pinned_tree import UserPinnedTree

    account, user = await _make_account_and_user(test_db, "pt1")
    tree = await _make_tree(test_db, account, user)
    pin = UserPinnedTree(
        user_id=user.id,
        tree_id=tree.id,
        account_id=account.id,
        display_order=0,
    )
    test_db.add(pin)
    await test_db.commit()

    result = await test_db.execute(select(UserPinnedTree).where(UserPinnedTree.id == pin.id))
    row = result.scalar_one()
    assert row.account_id == account.id

Step 4.2: Generate migration

cd backend && alembic revision -m "add_account_id_user_personalization"

"""add account_id to user personalization tables

Revision ID: <GENERATED>
Revises: <HASH_FROM_TASK_3>
Create Date: <keep>
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = '<GENERATED>'
down_revision: Union[str, None] = '<HASH_FROM_TASK_3>'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    for table in ('user_folders', 'user_pinned_trees'):
        op.add_column(table, sa.Column('account_id', sa.UUID(), nullable=True))
        op.create_foreign_key(
            f'fk_{table}_account_id', table, 'accounts',
            ['account_id'], ['id'], ondelete='CASCADE',
        )
        op.execute(f"""
            UPDATE {table} t
            SET account_id = u.account_id
            FROM users u
            WHERE t.user_id = u.id
              AND t.account_id IS NULL
        """)
        result = op.get_bind().execute(
            sa.text(f"SELECT COUNT(*) FROM {table} WHERE account_id IS NULL")
        )
        count = result.scalar()
        if count > 0:
            raise RuntimeError(f"ROLLBACK: {count} NULL account_id rows in {table}.")
        op.alter_column(table, 'account_id', nullable=False)
        op.create_index(f'ix_{table}_account_id', table, ['account_id'])


def downgrade() -> None:
    for table in ('user_folders', 'user_pinned_trees'):
        op.drop_index(f'ix_{table}_account_id', table_name=table)
        op.drop_constraint(f'fk_{table}_account_id', table, type_='foreignkey')
        op.drop_column(table, 'account_id')

Step 4.3: Run migration, verify, update models, test, commit

cd backend && alembic upgrade head

In backend/app/models/folder.py, add to UserFolder after user_id:

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

In backend/app/models/user_pinned_tree.py, add after user_id:

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

cd backend && python -m pytest tests/test_phase1_migrations.py -k "folder or pinned" -v --override-ini="addopts="
cd backend && python -m pytest --override-ini="addopts="
git add backend/alembic/versions/*add_account_id_user_personalization* \
        backend/app/models/folder.py \
        backend/app/models/user_pinned_tree.py \
        backend/tests/test_phase1_migrations.py
git commit -m "feat: Phase 1 Group 4 — add account_id to user_folders and user_pinned_trees

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>"

Task 5: Group 5 — PSA & notifications

Tables: psa_post_log, psa_member_mappings, notification_logs

Backfill paths:

psa_post_log: psa_connection_id → psa_connections.account_id. If psa_connection_id is NULL, fall back to posted_by → users.account_id.
psa_member_mappings: psa_connection_id → psa_connections.account_id
notification_logs: notification_config_id → notification_configs.account_id

Pre-check: psa_connections.account_id is already NOT NULL ✓. notification_configs.account_id must also be NOT NULL — verify before running.

Files:

Create: backend/alembic/versions/<hash>_add_account_id_psa_notifications.py
Modify: backend/app/models/psa_post_log.py, psa_member_mapping.py, notification_log.py
Test: backend/tests/test_phase1_migrations.py

Step 5.1: Write failing tests

Append to backend/tests/test_phase1_migrations.py:

# ── Group 5: PSA & notifications ─────────────────────────────────────────────

@pytest.mark.asyncio
async def test_psa_member_mapping_account_id_matches_connection(test_db: AsyncSession):
    """psa_member_mappings.account_id must match psa_connection's account_id."""
    from app.models.psa_connection import PsaConnection
    from app.models.psa_member_mapping import PsaMemberMapping

    account, user = await _make_account_and_user(test_db, "psa1")
    conn = PsaConnection(
        account_id=account.id,
        provider="connectwise",
        display_name="Test CW",
        site_url="https://cw.example.com",
        company_id="TEST",
        credentials_encrypted="placeholder",
    )
    test_db.add(conn)
    await test_db.flush()

    mapping = PsaMemberMapping(
        psa_connection_id=conn.id,
        user_id=user.id,
        account_id=account.id,
        external_member_id="cw-123",
        external_member_name="Test User",
        matched_by="manual_admin",
    )
    test_db.add(mapping)
    await test_db.commit()

    result = await test_db.execute(
        select(PsaMemberMapping).where(PsaMemberMapping.id == mapping.id)
    )
    row = result.scalar_one()
    assert row.account_id == account.id

Step 5.2: Generate migration

cd backend && alembic revision -m "add_account_id_psa_notifications"

"""add account_id to PSA and notification tables

Revision ID: <GENERATED>
Revises: <HASH_FROM_TASK_4>
Create Date: <keep>
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = '<GENERATED>'
down_revision: Union[str, None] = '<HASH_FROM_TASK_4>'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Step 1: ADD COLUMN
    for table in ('psa_post_log', 'psa_member_mappings', 'notification_logs'):
        op.add_column(table, sa.Column('account_id', sa.UUID(), nullable=True))
        op.create_foreign_key(
            f'fk_{table}_account_id', table, 'accounts',
            ['account_id'], ['id'], ondelete='CASCADE',
        )

    # Step 2: BACKFILL
    # psa_post_log: prefer psa_connection → fallback to posted_by user
    op.execute("""
        UPDATE psa_post_log ppl
        SET account_id = COALESCE(pc.account_id, u.account_id)
        FROM users u
        LEFT JOIN psa_connections pc ON pc.id = ppl.psa_connection_id
        WHERE ppl.posted_by = u.id
          AND ppl.account_id IS NULL
    """)

    # psa_member_mappings: via psa_connection
    op.execute("""
        UPDATE psa_member_mappings pmm
        SET account_id = pc.account_id
        FROM psa_connections pc
        WHERE pmm.psa_connection_id = pc.id
          AND pmm.account_id IS NULL
    """)

    # notification_logs: via notification_config
    op.execute("""
        UPDATE notification_logs nl
        SET account_id = nc.account_id
        FROM notification_configs nc
        WHERE nl.notification_config_id = nc.id
          AND nl.account_id IS NULL
    """)

    # Step 3: VERIFY
    for table in ('psa_post_log', 'psa_member_mappings', 'notification_logs'):
        result = op.get_bind().execute(
            sa.text(f"SELECT COUNT(*) FROM {table} WHERE account_id IS NULL")
        )
        count = result.scalar()
        if count > 0:
            raise RuntimeError(f"ROLLBACK: {count} NULL account_id rows in {table}.")

    # Step 4: SET NOT NULL
    for table in ('psa_post_log', 'psa_member_mappings', 'notification_logs'):
        op.alter_column(table, 'account_id', nullable=False)

    # Step 5: CREATE INDEX
    for table in ('psa_post_log', 'psa_member_mappings', 'notification_logs'):
        op.create_index(f'ix_{table}_account_id', table, ['account_id'])


def downgrade() -> None:
    for table in ('psa_post_log', 'psa_member_mappings', 'notification_logs'):
        op.drop_index(f'ix_{table}_account_id', table_name=table)
        op.drop_constraint(f'fk_{table}_account_id', table, type_='foreignkey')
        op.drop_column(table, 'account_id')

Step 5.3: Run migration, verify, update models, test, commit

cd backend && alembic upgrade head

Add account_id (NOT NULL, FK to accounts) to:

backend/app/models/psa_post_log.py — after ai_session_id column
backend/app/models/psa_member_mapping.py — after psa_connection_id column
backend/app/models/notification_log.py — after notification_config_id column

Each follows the same pattern:

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

cd backend && python -m pytest tests/test_phase1_migrations.py -k "psa" -v --override-ini="addopts="
cd backend && python -m pytest --override-ini="addopts="
git add backend/alembic/versions/*add_account_id_psa_notifications* \
        backend/app/models/psa_post_log.py \
        backend/app/models/psa_member_mapping.py \
        backend/app/models/notification_log.py \
        backend/tests/test_phase1_migrations.py
git commit -m "feat: Phase 1 Group 5 — add account_id to PSA and notification tables

psa_post_log: backfill via psa_connection, fallback to posted_by user
psa_member_mappings: backfill via psa_connection
notification_logs: backfill via notification_config

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>"

Task 6: Group 6 — Maintenance

Table: maintenance_schedules

Backfill path: tree_id → trees.account_id. Note: trees.account_id is still nullable at this point. Any maintenance schedule whose tree has account_id=NULL (i.e., is_default=TRUE) will not backfill. Fall back to created_by → users.account_id for those rows.

Files:

Create: backend/alembic/versions/<hash>_add_account_id_maintenance.py
Modify: backend/app/models/maintenance_schedule.py
Test: backend/tests/test_phase1_migrations.py

Step 6.1: Write failing test

Append to backend/tests/test_phase1_migrations.py:

# ── Group 6: Maintenance ──────────────────────────────────────────────────────

@pytest.mark.asyncio
async def test_maintenance_schedule_account_id_matches_tree(test_db: AsyncSession):
    """maintenance_schedules.account_id must match the tree's account_id."""
    from app.models.maintenance_schedule import MaintenanceSchedule

    account, user = await _make_account_and_user(test_db, "ms1")
    tree = Tree(
        name="Maintenance Flow",
        account_id=account.id,
        author_id=user.id,
        visibility="team",
        tree_type="maintenance",
        tree_structure={"id": "root", "type": "start", "children": []},
        is_active=True,
        status="published",
    )
    test_db.add(tree)
    await test_db.flush()

    schedule = MaintenanceSchedule(
        tree_id=tree.id,
        account_id=account.id,
        created_by=user.id,
        cron_expression="0 9 * * 1",
        timezone="UTC",
        is_active=True,
    )
    test_db.add(schedule)
    await test_db.commit()

    result = await test_db.execute(
        select(MaintenanceSchedule).where(MaintenanceSchedule.id == schedule.id)
    )
    row = result.scalar_one()
    assert row.account_id == account.id

Step 6.2: Generate migration

cd backend && alembic revision -m "add_account_id_maintenance"

"""add account_id to maintenance_schedules

Revision ID: <GENERATED>
Revises: <HASH_FROM_TASK_5>
Create Date: <keep>
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = '<GENERATED>'
down_revision: Union[str, None] = '<HASH_FROM_TASK_5>'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.add_column('maintenance_schedules',
                  sa.Column('account_id', sa.UUID(), nullable=True))
    op.create_foreign_key(
        'fk_maintenance_schedules_account_id', 'maintenance_schedules', 'accounts',
        ['account_id'], ['id'], ondelete='CASCADE',
    )

    # Primary: tree_id → trees.account_id
    op.execute("""
        UPDATE maintenance_schedules ms
        SET account_id = t.account_id
        FROM trees t
        WHERE ms.tree_id = t.id
          AND t.account_id IS NOT NULL
          AND ms.account_id IS NULL
    """)

    # Fallback: created_by → users.account_id (for is_default trees with NULL account_id)
    op.execute("""
        UPDATE maintenance_schedules ms
        SET account_id = u.account_id
        FROM users u
        WHERE ms.created_by = u.id
          AND u.account_id IS NOT NULL
          AND ms.account_id IS NULL
    """)

    result = op.get_bind().execute(
        sa.text("SELECT COUNT(*) FROM maintenance_schedules WHERE account_id IS NULL")
    )
    count = result.scalar()
    if count > 0:
        raise RuntimeError(
            f"ROLLBACK: {count} maintenance_schedules rows have NULL account_id. "
            "Check if created_by is NULL — those rows need manual resolution."
        )

    op.alter_column('maintenance_schedules', 'account_id', nullable=False)
    op.create_index('ix_maintenance_schedules_account_id', 'maintenance_schedules', ['account_id'])


def downgrade() -> None:
    op.drop_index('ix_maintenance_schedules_account_id', table_name='maintenance_schedules')
    op.drop_constraint('fk_maintenance_schedules_account_id', 'maintenance_schedules', type_='foreignkey')
    op.drop_column('maintenance_schedules', 'account_id')

Step 6.3: Run migration, verify, update model, test, commit

cd backend && alembic upgrade head

In backend/app/models/maintenance_schedule.py, add after created_by:

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

cd backend && python -m pytest tests/test_phase1_migrations.py::test_maintenance_schedule_account_id_matches_tree -v --override-ini="addopts="
cd backend && python -m pytest --override-ini="addopts="
git add backend/alembic/versions/*add_account_id_maintenance* \
        backend/app/models/maintenance_schedule.py \
        backend/tests/test_phase1_migrations.py
git commit -m "feat: Phase 1 Group 6 — add account_id to maintenance_schedules

Primary backfill: tree_id → trees.account_id
Fallback: created_by → users.account_id (for is_default tree rows)

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>"

Task 7: Group 7 — Legacy team_id tables

Tables: script_builder_sessions, script_templates, script_generations

Backfill paths:

script_builder_sessions: user_id → users.account_id
script_templates: created_by → users.account_id (created_by is nullable — handle with fallback)
script_generations: user_id → users.account_id

Important: Do NOT drop team_id — keep it until all application code is updated.

Files:

Create: backend/alembic/versions/<hash>_add_account_id_script_tables.py
Modify: backend/app/models/script_builder_session.py, backend/app/models/script_template.py (ScriptTemplate and ScriptGeneration)
Test: backend/tests/test_phase1_migrations.py

Step 7.1: Write failing tests

Append to backend/tests/test_phase1_migrations.py:

# ── Group 7: Legacy team_id tables ───────────────────────────────────────────

@pytest.mark.asyncio
async def test_script_builder_session_account_id(test_db: AsyncSession):
    """script_builder_sessions.account_id must match user's account_id."""
    from app.models.script_builder_session import ScriptBuilderSession

    account, user = await _make_account_and_user(test_db, "sbs1")
    sbs = ScriptBuilderSession(
        user_id=user.id,
        account_id=account.id,
        language="powershell",
    )
    test_db.add(sbs)
    await test_db.commit()

    result = await test_db.execute(
        select(ScriptBuilderSession).where(ScriptBuilderSession.id == sbs.id)
    )
    row = result.scalar_one()
    assert row.account_id == account.id

Step 7.2: Generate migration

cd backend && alembic revision -m "add_account_id_script_tables"

"""add account_id to script_builder_sessions, script_templates, script_generations

Revision ID: <GENERATED>
Revises: <HASH_FROM_TASK_6>
Create Date: <keep>
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = '<GENERATED>'
down_revision: Union[str, None] = '<HASH_FROM_TASK_6>'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    for table in ('script_builder_sessions', 'script_templates', 'script_generations'):
        op.add_column(table, sa.Column('account_id', sa.UUID(), nullable=True))
        op.create_foreign_key(
            f'fk_{table}_account_id', table, 'accounts',
            ['account_id'], ['id'], ondelete='CASCADE',
        )

    # script_builder_sessions: user_id → users.account_id
    op.execute("""
        UPDATE script_builder_sessions sbs
        SET account_id = u.account_id
        FROM users u
        WHERE sbs.user_id = u.id
          AND sbs.account_id IS NULL
    """)

    # script_templates: created_by → users.account_id
    # created_by is nullable, so left join + only set where not null
    op.execute("""
        UPDATE script_templates st
        SET account_id = u.account_id
        FROM users u
        WHERE st.created_by = u.id
          AND st.account_id IS NULL
    """)
    # Fallback for script_templates with NULL created_by: team_id → team admin user
    op.execute("""
        UPDATE script_templates st
        SET account_id = u.account_id
        FROM users u
        WHERE u.team_id = st.team_id
          AND u.is_team_admin = TRUE
          AND st.account_id IS NULL
        AND EXISTS (SELECT 1 FROM users u2 WHERE u2.team_id = st.team_id)
    """)

    # script_generations: user_id → users.account_id
    op.execute("""
        UPDATE script_generations sg
        SET account_id = u.account_id
        FROM users u
        WHERE sg.user_id = u.id
          AND sg.account_id IS NULL
    """)

    # VERIFY
    for table in ('script_builder_sessions', 'script_templates', 'script_generations'):
        result = op.get_bind().execute(
            sa.text(f"SELECT COUNT(*) FROM {table} WHERE account_id IS NULL")
        )
        count = result.scalar()
        if count > 0:
            raise RuntimeError(f"ROLLBACK: {count} NULL account_id rows in {table}.")

    for table in ('script_builder_sessions', 'script_templates', 'script_generations'):
        op.alter_column(table, 'account_id', nullable=False)
        op.create_index(f'ix_{table}_account_id', table, ['account_id'])


def downgrade() -> None:
    for table in ('script_builder_sessions', 'script_templates', 'script_generations'):
        op.drop_index(f'ix_{table}_account_id', table_name=table)
        op.drop_constraint(f'fk_{table}_account_id', table, type_='foreignkey')
        op.drop_column(table, 'account_id')

Step 7.3: Run migration, verify, update models, test, commit

cd backend && alembic upgrade head

In backend/app/models/script_builder_session.py, add after user_id:

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

In backend/app/models/script_template.py:

In ScriptTemplate class, add after team_id:

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

In ScriptGeneration class, add after user_id:

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

cd backend && python -m pytest tests/test_phase1_migrations.py::test_script_builder_session_account_id -v --override-ini="addopts="
cd backend && python -m pytest --override-ini="addopts="
git add backend/alembic/versions/*add_account_id_script_tables* \
        backend/app/models/script_builder_session.py \
        backend/app/models/script_template.py \
        backend/tests/test_phase1_migrations.py
git commit -m "feat: Phase 1 Group 7 — add account_id to script tables (keep team_id)

team_id is kept in all three tables — drop deferred until app code
is fully migrated off team_id references.

Tables: script_builder_sessions, script_templates, script_generations
Backfill: user_id/created_by → users.account_id

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>"

Task 8: Group 8 — TargetList

Table: target_lists

Backfill path: team_id → users WHERE is_team_admin=TRUE → account_id

Context: Zero rows in production (confirmed 2026-04-09). The migration is schema-only in practice but must be correct for any future rows. The team_id FK to teams is NOT NULL — keep it. Do NOT drop it.

Files:

Create: backend/alembic/versions/<hash>_add_account_id_target_lists.py
Modify: backend/app/models/target_list.py
Test: backend/tests/test_phase1_migrations.py

Step 8.1: Write failing test

Append to backend/tests/test_phase1_migrations.py:

# ── Group 8: TargetList ────────────────────────────────────────────────────────

@pytest.mark.asyncio
async def test_target_list_account_id_from_team_admin(test_db: AsyncSession):
    """target_lists.account_id must be set to the team admin's account_id."""
    from app.models.target_list import TargetList
    from app.models.team import Team

    account, user = await _make_account_and_user(test_db, "tl1")
    # Make user a team admin
    team = Team(name=f"Team {uuid.uuid4().hex[:6]}")
    test_db.add(team)
    await test_db.flush()

    user.team_id = team.id
    user.is_team_admin = True
    await test_db.flush()

    target_list = TargetList(
        team_id=team.id,
        account_id=account.id,
        created_by=user.id,
        name="Server Targets",
        targets=[{"label": "SRV-01"}],
    )
    test_db.add(target_list)
    await test_db.commit()

    result = await test_db.execute(
        select(TargetList).where(TargetList.id == target_list.id)
    )
    row = result.scalar_one()
    assert row.account_id == account.id

Step 8.2: Generate migration

cd backend && alembic revision -m "add_account_id_target_lists"

"""add account_id to target_lists (keep team_id)

Revision ID: <GENERATED>
Revises: <HASH_FROM_TASK_7>
Create Date: <keep>
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = '<GENERATED>'
down_revision: Union[str, None] = '<HASH_FROM_TASK_7>'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.add_column('target_lists', sa.Column('account_id', sa.UUID(), nullable=True))
    op.create_foreign_key(
        'fk_target_lists_account_id', 'target_lists', 'accounts',
        ['account_id'], ['id'], ondelete='CASCADE',
    )

    # Backfill: team_id → team admin user → account_id
    # If any row cannot be backfilled (no team admin found) → ROLLBACK
    op.execute("""
        UPDATE target_lists tl
        SET account_id = u.account_id
        FROM users u
        WHERE u.team_id = tl.team_id
          AND u.is_team_admin = TRUE
          AND u.account_id IS NOT NULL
          AND tl.account_id IS NULL
    """)

    # Secondary fallback: created_by user
    op.execute("""
        UPDATE target_lists tl
        SET account_id = u.account_id
        FROM users u
        WHERE tl.created_by = u.id
          AND u.account_id IS NOT NULL
          AND tl.account_id IS NULL
    """)

    result = op.get_bind().execute(
        sa.text("SELECT COUNT(*) FROM target_lists WHERE account_id IS NULL")
    )
    count = result.scalar()
    if count > 0:
        raise RuntimeError(
            f"ROLLBACK: {count} target_lists rows have NULL account_id. "
            "No team admin found for these teams. Resolve before re-running."
        )

    op.alter_column('target_lists', 'account_id', nullable=False)
    op.create_index('ix_target_lists_account_id', 'target_lists', ['account_id'])


def downgrade() -> None:
    op.drop_index('ix_target_lists_account_id', table_name='target_lists')
    op.drop_constraint('fk_target_lists_account_id', 'target_lists', type_='foreignkey')
    op.drop_column('target_lists', 'account_id')

Step 8.3: Run migration, verify, update model, test, commit

cd backend && alembic upgrade head

In backend/app/models/target_list.py, add after team_id:

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

Also add the Account import to TYPE_CHECKING:

if TYPE_CHECKING:
    from app.models.user import User
    from app.models.team import Team
    from app.models.account import Account

cd backend && python -m pytest tests/test_phase1_migrations.py::test_target_list_account_id_from_team_admin -v --override-ini="addopts="
cd backend && python -m pytest --override-ini="addopts="
git add backend/alembic/versions/*add_account_id_target_lists* \
        backend/app/models/target_list.py \
        backend/tests/test_phase1_migrations.py
git commit -m "feat: Phase 1 Group 8 — add account_id to target_lists (keep team_id)

Zero rows in production — this is a schema-only migration in practice.
team_id kept for app code compatibility. Drop deferred to later cleanup.
Backfill: team_id → team admin user → account_id; fallback: created_by.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>"

Task 9: Group 10 — Global content separation (runs before Group 9)

Why this runs before Task 10: trees has account_id=NULL for is_default=TRUE rows (platform trees). Task 10 sets trees.account_id NOT NULL, which would fail without first handling these rows. This task moves them to template_trees (no account_id column), then Task 10 can safely SET NOT NULL on trees.

Action:

Create template_trees table — stores platform-owned troubleshooting trees (no account_id, no RLS)
Create platform_steps table — stores platform-owned steps (no account_id, no RLS)
Copy is_default=TRUE trees to template_trees
Copy visibility='public' steps from step_library to platform_steps
Remove the copied rows from trees (set is_default=FALSE and assign a NULL-safe account) — or delete if no sessions reference them
Handle global tree_categories, tree_tags, step_categories (NULL account_id rows = global platform items) — assign to a "ResolutionFlow Platform" internal account created in this migration

Files:

Create: backend/alembic/versions/<hash>_create_global_content_tables.py
Create: backend/app/models/template_tree.py
Create: backend/app/models/platform_step.py
Modify: backend/app/models/__init__.py (register new models)
Test: backend/tests/test_phase1_migrations.py

Step 9.1: Write failing tests

Append to backend/tests/test_phase1_migrations.py:

# ── Group 10 (runs first): Global content tables ──────────────────────────────

@pytest.mark.asyncio
async def test_template_trees_table_exists_and_has_no_account_id(test_db: AsyncSession):
    """template_trees must exist and must NOT have an account_id column."""
    result = await test_db.execute(text("""
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = 'template_trees'
    """))
    columns = {row[0] for row in result.fetchall()}
    assert 'id' in columns, "template_trees.id must exist"
    assert 'account_id' not in columns, "template_trees must not have account_id (global content)"


@pytest.mark.asyncio
async def test_platform_steps_table_exists_and_has_no_account_id(test_db: AsyncSession):
    """platform_steps must exist and must NOT have an account_id column."""
    result = await test_db.execute(text("""
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = 'platform_steps'
    """))
    columns = {row[0] for row in result.fetchall()}
    assert 'id' in columns, "platform_steps.id must exist"
    assert 'account_id' not in columns, "platform_steps must not have account_id (global content)"

Step 9.2: Generate migration

cd backend && alembic revision -m "create_global_content_tables"

"""create template_trees and platform_steps global content tables

Revision ID: <GENERATED>
Revises: <HASH_FROM_TASK_8>
Create Date: <keep>

These tables hold platform-owned content that is readable by all
authenticated users. No account_id. No RLS. Ever.
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID, JSONB

revision: str = '<GENERATED>'
down_revision: Union[str, None] = '<HASH_FROM_TASK_8>'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ── Create template_trees ─────────────────────────────────────────────────
    op.create_table(
        'template_trees',
        sa.Column('id', UUID(), primary_key=True),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('category', sa.String(100), nullable=True),
        sa.Column('tree_type', sa.String(20), nullable=False),
        sa.Column('tree_structure', JSONB(), nullable=False),
        sa.Column('tags', JSONB(), nullable=False, server_default='[]'),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default='true'),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
        # source_tree_id: original tree this was promoted from (nullable)
        sa.Column('source_tree_id', UUID(), sa.ForeignKey('trees.id', ondelete='SET NULL'), nullable=True),
    )
    op.create_index('ix_template_trees_tree_type', 'template_trees', ['tree_type'])

    # ── Create platform_steps ────────────────────────────────────────────────
    op.create_table(
        'platform_steps',
        sa.Column('id', UUID(), primary_key=True),
        sa.Column('title', sa.String(255), nullable=False),
        sa.Column('step_type', sa.String(50), nullable=False),
        sa.Column('content', JSONB(), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default='true'),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
        # source_step_id: original step this was promoted from (nullable)
        sa.Column('source_step_id', UUID(), sa.ForeignKey('step_library.id', ondelete='SET NULL'), nullable=True),
    )
    op.create_index('ix_platform_steps_step_type', 'platform_steps', ['step_type'])

    # ── Migrate is_default=TRUE trees → template_trees ─────────────────────
    op.execute("""
        INSERT INTO template_trees
            (id, name, description, category, tree_type, tree_structure,
             is_active, created_at, updated_at, source_tree_id)
        SELECT
            gen_random_uuid(), name, description, category, tree_type,
            tree_structure, is_active,
            COALESCE(created_at, NOW()), COALESCE(updated_at, NOW()), id
        FROM trees
        WHERE is_default = TRUE
    """)

    # ── Migrate visibility='public' steps → platform_steps ─────────────────
    op.execute("""
        INSERT INTO platform_steps
            (id, title, step_type, content, is_active, created_at, updated_at, source_step_id)
        SELECT
            gen_random_uuid(), title, step_type, content, is_active,
            COALESCE(created_at, NOW()), COALESCE(updated_at, NOW()), id
        FROM step_library
        WHERE visibility = 'public'
    """)

    # ── Create a ResolutionFlow platform account for global content ──────────
    # Used to satisfy NOT NULL on trees, tree_categories, tree_tags, etc.
    # This is a sentinel account — it is NOT a real customer account.
    op.execute("""
        INSERT INTO accounts (id, name, display_code, created_at, updated_at)
        VALUES (
            '00000000-0000-0000-0000-000000000001',
            'ResolutionFlow Platform',
            'PLATFORM',
            NOW(),
            NOW()
        )
        ON CONFLICT (id) DO NOTHING
    """)

    # ── Assign is_default trees to platform account ──────────────────────────
    op.execute("""
        UPDATE trees
        SET account_id = '00000000-0000-0000-0000-000000000001'
        WHERE is_default = TRUE
          AND account_id IS NULL
    """)

    # ── Assign global tree_categories (team_id=NULL, account_id=NULL) ────────
    op.execute("""
        UPDATE tree_categories
        SET account_id = '00000000-0000-0000-0000-000000000001'
        WHERE account_id IS NULL
    """)

    # ── Assign global tree_tags (team_id=NULL, account_id=NULL) ─────────────
    op.execute("""
        UPDATE tree_tags
        SET account_id = '00000000-0000-0000-0000-000000000001'
        WHERE account_id IS NULL
    """)

    # ── Assign global step_categories (account_id=NULL) ──────────────────────
    op.execute("""
        UPDATE step_categories
        SET account_id = '00000000-0000-0000-0000-000000000001'
        WHERE account_id IS NULL
    """)

    # ── Assign global step_library entries (visibility='public', account_id=NULL) ─
    op.execute("""
        UPDATE step_library
        SET account_id = '00000000-0000-0000-0000-000000000001'
        WHERE account_id IS NULL
    """)

    # ── Verify all target tables now have zero NULLs ─────────────────────────
    for table in ('trees', 'tree_categories', 'tree_tags', 'step_categories', 'step_library'):
        result = op.get_bind().execute(
            sa.text(f"SELECT COUNT(*) FROM {table} WHERE account_id IS NULL")
        )
        count = result.scalar()
        if count > 0:
            raise RuntimeError(
                f"ROLLBACK: {count} NULL account_id rows remain in {table} "
                "after platform account assignment. Investigate before re-running."
            )


def downgrade() -> None:
    # Reverse platform account assignments (set back to NULL where platform account)
    platform_id = '00000000-0000-0000-0000-000000000001'
    for table in ('trees', 'tree_categories', 'tree_tags', 'step_categories', 'step_library'):
        op.execute(f"UPDATE {table} SET account_id = NULL WHERE account_id = '{platform_id}'")

    op.execute(f"DELETE FROM accounts WHERE id = '{platform_id}'")
    op.drop_index('ix_platform_steps_step_type', table_name='platform_steps')
    op.drop_index('ix_template_trees_tree_type', table_name='template_trees')
    op.drop_table('platform_steps')
    op.drop_table('template_trees')

Step 9.3: Create the SQLAlchemy model files

Create backend/app/models/template_tree.py:

"""Template tree model — platform-owned troubleshooting trees, readable by all users.

No account_id. No RLS. Readable by any authenticated user.
Populated by promoting is_default=TRUE trees from the trees table.
"""
import uuid
from datetime import datetime, timezone
from typing import Optional, Any

from sqlalchemy import String, Text, Boolean, DateTime, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.dialects.postgresql import UUID, JSONB

from app.core.database import Base


class TemplateTree(Base):
    __tablename__ = "template_trees"

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    category: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    tree_type: Mapped[str] = mapped_column(String(20), nullable=False, index=True)
    tree_structure: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False)
    tags: Mapped[list] = mapped_column(JSONB, nullable=False, default=list)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    source_tree_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("trees.id", ondelete="SET NULL"),
        nullable=True,
    )
    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),
    )

Create backend/app/models/platform_step.py:

"""Platform step model — platform-owned steps, readable by all users.

No account_id. No RLS. Readable by any authenticated user.
Populated by promoting visibility='public' steps from step_library.
"""
import uuid
from datetime import datetime, timezone
from typing import Optional, Any

from sqlalchemy import String, Boolean, DateTime, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.dialects.postgresql import UUID, JSONB

from app.core.database import Base


class PlatformStep(Base):
    __tablename__ = "platform_steps"

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    title: Mapped[str] = mapped_column(String(255), nullable=False)
    step_type: Mapped[str] = mapped_column(String(50), nullable=False, index=True)
    content: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    source_step_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("step_library.id", ondelete="SET NULL"),
        nullable=True,
    )
    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),
    )

In backend/app/models/__init__.py, add:

from .template_tree import TemplateTree
from .platform_step import PlatformStep

And add "TemplateTree" and "PlatformStep" to the __all__ list.

Step 9.4: Run migration, run tests, commit

cd backend && alembic upgrade head
cd backend && python -m pytest tests/test_phase1_migrations.py -k "template_trees or platform_steps" -v --override-ini="addopts="
cd backend && python -m pytest --override-ini="addopts="
git add backend/alembic/versions/*create_global_content_tables* \
        backend/app/models/template_tree.py \
        backend/app/models/platform_step.py \
        backend/app/models/__init__.py \
        backend/tests/test_phase1_migrations.py
git commit -m "feat: Phase 1 Group 10 — create global content tables and platform account

Creates template_trees and platform_steps (no account_id, no RLS).
Migrates is_default=TRUE trees and public steps into them.
Creates sentinel platform account (00000000-...-0001) for global
tree_categories, tree_tags, step_categories, step_library, and
is_default trees — clearing all NULL account_id rows in those tables
as prerequisite for Group 9 SET NOT NULL.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>"

Task 10: Group 9 — SET NOT NULL on existing nullable account_id columns

Why this runs last: Depends on Task 9 having cleared all NULL account_id rows via platform account assignment.

Tables: users, trees, tree_categories, tree_tags, step_categories, step_library, tree_embeddings, feedback

Action: For each table:

Verify zero NULLs (if any remain, backfill or delete)
SET NOT NULL
If index doesn't already exist, CREATE INDEX

Special cases:

users.account_id: Any user with NULL account_id must be investigated — they are orphaned. If none, proceed.
tree_embeddings.account_id: Backfill from tree_id → trees.account_id (trees now all have account_id after Task 9).
feedback.account_id: Backfill from user_id → users.account_id.

Files:

Create: backend/alembic/versions/<hash>_set_not_null_account_id_phase1.py
Modify: backend/app/models/user.py, tree.py, category.py, tag.py, step_category.py, step_library.py (StepLibrary), tree_embedding.py, feedback.py
Test: backend/tests/test_phase1_migrations.py

Step 10.1: Write failing tests

Append to backend/tests/test_phase1_migrations.py:

# ── Group 9: SET NOT NULL on existing nullable columns ────────────────────────

@pytest.mark.asyncio
async def test_tree_account_id_is_not_null(test_db: AsyncSession):
    """trees.account_id must be NOT NULL after Phase 1 — enforced at DB level."""
    # Try to insert a tree with no account_id — must fail
    from sqlalchemy.exc import IntegrityError
    with pytest.raises(IntegrityError):
        test_db.add(Tree(
            name="Bad tree",
            # account_id intentionally omitted
            author_id=None,
            visibility="private",
            tree_type="troubleshooting",
            tree_structure={},
            is_active=True,
            status="draft",
        ))
        await test_db.flush()


@pytest.mark.asyncio
async def test_user_account_id_is_not_null(test_db: AsyncSession):
    """users.account_id must be NOT NULL after Phase 1."""
    from sqlalchemy.exc import IntegrityError
    with pytest.raises(IntegrityError):
        test_db.add(User(
            email=f"orphan-{uuid.uuid4().hex[:6]}@example.com",
            name="Orphan",
            password_hash=get_password_hash("x"),
            is_active=True,
            # account_id intentionally omitted
        ))
        await test_db.flush()

Step 10.2: Generate migration

cd backend && alembic revision -m "set_not_null_account_id_phase1"

"""set NOT NULL on all previously-nullable account_id columns

Revision ID: <GENERATED>
Revises: <HASH_FROM_TASK_9>
Create Date: <keep>

All tables in this migration had account_id set to nullable previously.
Task 9 (create_global_content_tables) cleared all NULL rows.
This migration enforces the NOT NULL constraint.
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = '<GENERATED>'
down_revision: Union[str, None] = '<HASH_FROM_TASK_9>'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # tree_embeddings: backfill from trees (must happen before SET NOT NULL)
    op.execute("""
        UPDATE tree_embeddings te
        SET account_id = t.account_id
        FROM trees t
        WHERE te.tree_id = t.id
          AND te.account_id IS NULL
    """)

    # feedback: backfill from users
    op.execute("""
        UPDATE feedback f
        SET account_id = u.account_id
        FROM users u
        WHERE f.user_id = u.id
          AND f.account_id IS NULL
    """)

    # Verify ALL tables before touching any SET NOT NULL
    tables_with_account_id = [
        'users', 'trees', 'tree_categories', 'tree_tags',
        'step_categories', 'step_library', 'tree_embeddings', 'feedback',
    ]
    for table in tables_with_account_id:
        result = op.get_bind().execute(
            sa.text(f"SELECT COUNT(*) FROM {table} WHERE account_id IS NULL")
        )
        count = result.scalar()
        if count > 0:
            raise RuntimeError(
                f"ROLLBACK: {count} NULL account_id rows in {table}. "
                "Run Task 9 (create_global_content_tables) first, or "
                "manually backfill/delete orphaned rows."
            )

    # SET NOT NULL on all
    for table in tables_with_account_id:
        op.alter_column(table, 'account_id', nullable=False)

    # Create indexes where they don't already exist
    # (some tables like trees already have ix_trees_account_id from prior work)
    new_indexes = [
        ('tree_embeddings', 'ix_tree_embeddings_account_id'),
        ('feedback', 'ix_feedback_account_id'),
    ]
    for table, index_name in new_indexes:
        # Check if index exists to avoid duplicate error
        result = op.get_bind().execute(sa.text(
            f"SELECT 1 FROM pg_indexes WHERE tablename='{table}' AND indexname='{index_name}'"
        ))
        if not result.fetchone():
            op.create_index(index_name, table, ['account_id'])


def downgrade() -> None:
    # Revert to nullable
    for table in ('users', 'trees', 'tree_categories', 'tree_tags',
                  'step_categories', 'step_library', 'tree_embeddings', 'feedback'):
        op.alter_column(table, 'account_id', nullable=True)
    for table, index_name in (
        ('tree_embeddings', 'ix_tree_embeddings_account_id'),
        ('feedback', 'ix_feedback_account_id'),
    ):
        try:
            op.drop_index(index_name, table_name=table)
        except Exception:
            pass

Step 10.3: Run migration

cd backend && alembic upgrade head

If this errors with "NULL account_id rows remain in users", investigate:

-- Run from VPS SSH via docker exec
SELECT id, email, account_id FROM users WHERE account_id IS NULL;

These are orphaned users. Either assign them to an account or delete them if they are test/seed data.

Step 10.4: Update SQLAlchemy models — change Mapped[Optional[uuid.UUID]] to Mapped[uuid.UUID] and nullable=True to nullable=False

backend/app/models/user.py — find account_id (around line 46) and change:

# BEFORE:
    account_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("accounts.id", ondelete="RESTRICT"),
        nullable=True,
        ...
    )

# AFTER:
    account_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("accounts.id", ondelete="RESTRICT"),
        nullable=False,
        index=True,
    )

backend/app/models/tree.py — find account_id (around line 79) and change:

# BEFORE:
    account_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        UUID(as_uuid=True), ..., nullable=True, ...
    )

# AFTER:
    account_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("accounts.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

Apply the same pattern (Optional → required, nullable=True → nullable=False) to:

backend/app/models/category.py — TreeCategory.account_id
backend/app/models/tag.py — TreeTag.account_id
backend/app/models/step_category.py — StepCategory.account_id
backend/app/models/step_library.py — StepLibrary.account_id
backend/app/models/tree_embedding.py — TreeEmbedding.account_id
backend/app/models/feedback.py — Feedback.account_id
Step 10.5: Run tests, full suite, commit

cd backend && python -m pytest tests/test_phase1_migrations.py -v --override-ini="addopts="
cd backend && python -m pytest --override-ini="addopts="

If any existing tests fail because they create objects without account_id, update those test fixtures to provide the required field.

git add backend/alembic/versions/*set_not_null_account_id* \
        backend/app/models/user.py \
        backend/app/models/tree.py \
        backend/app/models/category.py \
        backend/app/models/tag.py \
        backend/app/models/step_category.py \
        backend/app/models/step_library.py \
        backend/app/models/tree_embedding.py \
        backend/app/models/feedback.py \
        backend/tests/test_phase1_migrations.py
git commit -m "feat: Phase 1 Group 9 — enforce NOT NULL on all account_id columns

All previously-nullable account_id columns are now NOT NULL.
tree_embeddings and feedback backfilled before constraint applied.
Global content assigned to platform sentinel account (00000000-...-0001)
in preceding migration.

Tables updated: users, trees, tree_categories, tree_tags,
step_categories, step_library, tree_embeddings, feedback

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>"

Task 11: Phase 1 gate verification

Run the gate verification query across all tenant tables. All must return zero NULLs.

Files: No code changes — verification only.

Step 11.1: Run the gate verification query

From VPS SSH:

docker exec -it resolutionflow_postgres psql -U postgres -d resolutionflow -c "
SELECT tablename, null_count
FROM (
    SELECT 'sessions'                  AS tablename, COUNT(*) FILTER (WHERE account_id IS NULL) AS null_count FROM sessions
    UNION ALL
    SELECT 'attachments',               COUNT(*) FILTER (WHERE account_id IS NULL) FROM attachments
    UNION ALL
    SELECT 'session_supporting_data',   COUNT(*) FILTER (WHERE account_id IS NULL) FROM session_supporting_data
    UNION ALL
    SELECT 'session_resolution_outputs',COUNT(*) FILTER (WHERE account_id IS NULL) FROM session_resolution_outputs
    UNION ALL
    SELECT 'session_branches',          COUNT(*) FILTER (WHERE account_id IS NULL) FROM session_branches
    UNION ALL
    SELECT 'session_handoffs',          COUNT(*) FILTER (WHERE account_id IS NULL) FROM session_handoffs
    UNION ALL
    SELECT 'fork_points',               COUNT(*) FILTER (WHERE account_id IS NULL) FROM fork_points
    UNION ALL
    SELECT 'ai_session_steps',          COUNT(*) FILTER (WHERE account_id IS NULL) FROM ai_session_steps
    UNION ALL
    SELECT 'ai_suggestions',            COUNT(*) FILTER (WHERE account_id IS NULL) FROM ai_suggestions
    UNION ALL
    SELECT 'step_ratings',              COUNT(*) FILTER (WHERE account_id IS NULL) FROM step_ratings
    UNION ALL
    SELECT 'step_usage_log',            COUNT(*) FILTER (WHERE account_id IS NULL) FROM step_usage_log
    UNION ALL
    SELECT 'user_folders',              COUNT(*) FILTER (WHERE account_id IS NULL) FROM user_folders
    UNION ALL
    SELECT 'user_pinned_trees',         COUNT(*) FILTER (WHERE account_id IS NULL) FROM user_pinned_trees
    UNION ALL
    SELECT 'psa_post_log',              COUNT(*) FILTER (WHERE account_id IS NULL) FROM psa_post_log
    UNION ALL
    SELECT 'psa_member_mappings',       COUNT(*) FILTER (WHERE account_id IS NULL) FROM psa_member_mappings
    UNION ALL
    SELECT 'notification_logs',         COUNT(*) FILTER (WHERE account_id IS NULL) FROM notification_logs
    UNION ALL
    SELECT 'maintenance_schedules',     COUNT(*) FILTER (WHERE account_id IS NULL) FROM maintenance_schedules
    UNION ALL
    SELECT 'script_builder_sessions',   COUNT(*) FILTER (WHERE account_id IS NULL) FROM script_builder_sessions
    UNION ALL
    SELECT 'script_templates',          COUNT(*) FILTER (WHERE account_id IS NULL) FROM script_templates
    UNION ALL
    SELECT 'script_generations',        COUNT(*) FILTER (WHERE account_id IS NULL) FROM script_generations
    UNION ALL
    SELECT 'target_lists',              COUNT(*) FILTER (WHERE account_id IS NULL) FROM target_lists
    UNION ALL
    SELECT 'users',                     COUNT(*) FILTER (WHERE account_id IS NULL) FROM users
    UNION ALL
    SELECT 'trees',                     COUNT(*) FILTER (WHERE account_id IS NULL) FROM trees
    UNION ALL
    SELECT 'tree_categories',           COUNT(*) FILTER (WHERE account_id IS NULL) FROM tree_categories
    UNION ALL
    SELECT 'tree_tags',                 COUNT(*) FILTER (WHERE account_id IS NULL) FROM tree_tags
    UNION ALL
    SELECT 'step_categories',           COUNT(*) FILTER (WHERE account_id IS NULL) FROM step_categories
    UNION ALL
    SELECT 'step_library',              COUNT(*) FILTER (WHERE account_id IS NULL) FROM step_library
    UNION ALL
    SELECT 'tree_embeddings',           COUNT(*) FILTER (WHERE account_id IS NULL) FROM tree_embeddings
    UNION ALL
    SELECT 'feedback',                  COUNT(*) FILTER (WHERE account_id IS NULL) FROM feedback
) t
ORDER BY null_count DESC, tablename;
"

Expected: all rows show null_count = 0.

Any non-zero row is a blocker — do not proceed to Phase 2 until resolved.

Step 11.2: Verify CI is still green

gh run list --limit 3

Check that the latest CI run on feat/tenant-isolation-phase-1 is green. The tenant filter check will now report fewer warnings (tables that gained account_id no longer trigger false positives).

Step 11.3: Create PR

git push -u origin feat/tenant-isolation-phase-1
gh pr create \
  --base main \
  --title "feat: tenant isolation Phase 1 — add account_id to all tenant tables" \
  --body "Adds account_id NOT NULL to all tenant tables, creates global content tables, and enforces the platform account sentinel for legacy global content. Phase 2 (RLS + SET LOCAL in get_db) is unblocked once this merges and gate query returns all zeros."

Phase 1 Gate Checklist

Before merging and declaring Phase 1 complete:

All 10 migrations in alembic/versions/ chained correctly (down_revision points to previous)
All migrations run cleanly: alembic upgrade head exits 0
All 28 tenant tables show null_count = 0 in gate verification query
Full test suite passes: python -m pytest --override-ini="addopts="
python scripts/check_tenant_filters.py warning count has decreased (tables with account_id no longer flagged)
session_ratings not touched (already had account_id NOT NULL ✓)
team_id columns NOT dropped on script tables, target_lists (deferred cleanup)
CI passes on feat/tenant-isolation-phase-1 branch
Gate verification query run against production DB (VPS SSH) and returns all zeros

89 KiB Raw Permalink Blame History

Tenant Isolation — Phase 1 Schema Migrations

File Map

Task 1: Group 1 — Core sessions

Task 2: Group 2 — AI & branching

Task 3: Group 3 — Steps & ratings

Task 4: Group 4 — User personalization

Task 5: Group 5 — PSA & notifications

Task 6: Group 6 — Maintenance

Task 7: Group 7 — Legacy team_id tables

Task 8: Group 8 — TargetList

Task 9: Group 10 — Global content separation (runs before Group 9)

Task 10: Group 9 — SET NOT NULL on existing nullable account_id columns

Task 11: Phase 1 gate verification

Phase 1 Gate Checklist

89 KiB

Raw Permalink Blame History