resolutionflow/docs/archive/2026-02-06-subscription-tier-implementation.md

# Subscription Tier Architecture - Implementation Plan

**Date:** 2026-02-06
**Status:** Ready for implementation
**Branch Strategy:** Feature branch (`feat/subscription-tiers`)
**Estimated Duration:** 10-12 days

---

## Context

ResolutionFlow is transitioning from a flat team-based system to a SaaS subscription model with three tiers (Free, Pro, Team). This is a **foundational architectural change** that affects the core data model, permission system, and user experience.

### Why This Change Is Needed

The current system uses `teams` as the multi-tenant boundary with basic RBAC (`role` + `is_team_admin` flags). This was designed for internal/single-team use and lacks:
- Billing/subscription tracking
- Feature gating by plan tier
- Account ownership model
- Scalable permission system for SaaS

The new architecture enables monetization while maintaining clean separation between **subscription features** (what you can access) and **account roles** (what you can do).

### Current State Summary

**Existing:**
- User model: `role`, `is_team_admin`, `team_id` fields
- Team model with FKs from 5 content tables (Tree, StepLibrary, Category, Tag, StepCategory)
- Permission system in `backend/app/core/permissions.py`
- 61 passing integration tests
- Production deployment on Railway (test/dev data only)

**Missing:**
- No Stripe integration (not even the library)
- No Account/Subscription models
- No plan limits or feature gating
- No multi-path registration flow

### Key Decisions

- **Timeline:** ASAP (1-2 weeks)
- **Data:** Only test/dev data exists - safe to drop/recreate DB if needed
- **Scope:** Full 3-tier system (Free + Pro + Team) with Stripe webhook skeleton
- **Stripe:** Build Stripe-ready infrastructure, enable when ready to monetize
- **Migration Strategy:** All existing users → Free tier accounts
- **Limits:** 3 trees, 20 sessions/month for Free (configurable via `plan_limits` table)
- **Development:** Feature branch with thorough testing before merge

---

## Implementation Phases

### Phase 1: Database Migration (Days 1-3)

Create new tables and migrate existing data in 6 separate migrations for safety and rollback capability.

#### Migration 016: Create New Tables

**File:** `backend/alembic/versions/016_add_subscription_tables.py`

Create 4 new tables:
1. **accounts** - Billing entity (replaces teams conceptually)
   - Columns: id, name, display_code (8-char unique), owner_id, stripe_customer_id, timestamps
   - Owner initially nullable (set after user creation)

2. **subscriptions** - One per account
   - Columns: id, account_id (unique FK), stripe_subscription_id, stripe_price_id, plan, billing_interval, status, seat_limit, period dates, cancel flag
   - Indexed on account_id and plan

3. **plan_limits** - Configuration table (not user data)
   - Columns: plan (unique), max_trees, max_sessions_per_month, max_users, custom_branding, priority_support, export_formats (JSONB)
   - Seed with 3 rows: free (3 trees, 20 sessions), pro (25/200), team (unlimited)

4. **account_invites** - Team invitation codes
   - Columns: id, account_id, invited_by_id, email, code, role, accepted_by_id, expires_at, timestamps

**Why separate:** Independent tables, no FK dependencies yet, zero impact on existing code.

**Rollback:** Drop tables in reverse order.

**Validation:**
```sql
SELECT COUNT(*) FROM plan_limits;  -- Should be 3
SELECT plan, max_trees FROM plan_limits ORDER BY plan;
```

---

#### Migration 017: Add Columns to Users

**File:** `backend/alembic/versions/017_add_account_id_to_users.py`

Add to `users` table:
- `account_id` (UUID, nullable, indexed)
- `account_role` (String, nullable - will become 'owner'/'engineer'/'viewer')

**Why separate:** Keeps this atomic, allows testing before data migration.

**Rollback:** Drop columns and index.

**Validation:** All 61 existing tests should still pass (columns nullable and unused).

---

#### Migration 018: Migrate Users to Accounts (CRITICAL)

**File:** `backend/alembic/versions/018_migrate_users_to_accounts.py`

**This is the most critical migration.** Must be idempotent with comprehensive validation.

**Logic:**

1. **For each existing team:**
   - Create Account (name from team.name, generate 8-char display_code)
   - Set owner_id to first team admin (or first user who joined)
   - Create free Subscription for that account
   - Update all users in that team: set account_id, map role → account_role:
     - `is_team_admin=True` → `account_role='owner'`
     - `role='engineer'` → `account_role='engineer'`
     - `role='viewer'` → `account_role='viewer'`

2. **For each user without a team:**
   - Create personal Account (name: "User's Account")
   - Set owner_id to that user
   - Create free Subscription
   - Update user: set account_id, account_role='owner' (personal accounts default to owner)

3. **Validate:**
   - `SELECT COUNT(*) FROM users WHERE account_id IS NULL` → must be 0
   - `SELECT COUNT(*) FROM accounts` → should equal (teams + teamless users)
   - `SELECT COUNT(*) FROM subscriptions` → should equal accounts

**Display code generation:** Use `secrets.choice()` with charset excluding confusing chars (0, O, I, 1, L). Check uniqueness before inserting.

**Rollback:** Downgrade clears account_id/account_role, deletes all accounts/subscriptions.

**Test before production:** Run on a copy of production DB first!

---

#### Migration 019: Migrate Foreign Keys

**File:** `backend/alembic/versions/019_migrate_team_fks_to_account.py`

For each table with `team_id`: trees, step_library, tree_categories, tree_tags, step_categories

1. Add `account_id` column (UUID, nullable, indexed)
2. Migrate data: `UPDATE table SET account_id = (SELECT account_id FROM users WHERE users.team_id = table.team_id LIMIT 1)`
3. Validate: Ensure no rows have team_id but NULL account_id

**Why separate:** Allows verifying user migration succeeded before touching content tables.

**Rollback:** Drop account_id columns and indexes.

---

#### Migration 020: Add Constraints

**File:** `backend/alembic/versions/020_finalize_account_migration.py`

1. Users table:
   - Make account_id NOT NULL
   - Make account_role NOT NULL
   - Add FK: account_id → accounts.id (CASCADE)
   - Add CHECK: account_role IN ('owner', 'engineer', 'viewer')

2. Content tables:
   - Add FK: account_id → accounts.id (CASCADE, nullable OK for global content)

3. Accounts table:
   - Make owner_id NOT NULL
   - Add FK: owner_id → users.id (RESTRICT - can't delete owner without transfer)

**Why last:** Only enforces constraints after data integrity verified.

**Point of no return:** After this, old team-based code will break.

---

#### Migration 021: Drop Old Columns

**File:** `backend/alembic/versions/021_drop_old_team_columns.py`

1. Drop team_id FKs and columns from: trees, step_library, tree_categories, tree_tags, step_categories
2. Drop from users: team_id, is_team_admin, role (replaced by account_role)
3. Drop CHECK constraint on old role field
4. Drop teams table entirely

**Why last:** Point of no return. Run ONLY after Phase 2 backend code is deployed and tested.

**Rollback:** Complex - requires recreating teams table and restoring relationships. Keep database backup!

---

### Phase 2: Backend Updates (Days 4-7)

Update all backend code to use account-based system and add subscription features.

#### 2.1: Create New Models

**Files to create:**

- `backend/app/models/account.py`
  - Relationships: owner (User), users (list), subscription (one), trees, categories, tags
  - Generated display_code in migration, not model default

- `backend/app/models/subscription.py`
  - Relationship: account (back_populates)
  - Properties: is_active, is_paid, days_until_renewal

- `backend/app/models/plan_limits.py`
  - No relationships, pure configuration
  - JSONB export_formats field

- `backend/app/models/account_invite.py`
  - Relationships: account, invited_by, accepted_by
  - Properties: is_valid, is_expired

**Files to modify:**

- `backend/app/models/user.py`
  - **Remove:** team_id, is_team_admin, role
  - **Add:** account_id (FK, NOT NULL), account_role (String, NOT NULL)
  - **Update relationships:** account (via account_id), owned_account (via Account.owner_id)
  - **Add properties:** is_account_owner, can_manage_account
  - **Add CHECK:** account_role IN ('owner', 'engineer', 'viewer')

- Update Tree, StepLibrary, Category, Tag, StepCategory:
  - Replace team_id → account_id
  - Update relationships: account (not team)

**Critical:** Handle multiple FKs to users table with explicit `foreign_keys` parameters.

---

#### 2.2: Refactor Permissions System

**File:** `backend/app/core/permissions.py` (major refactor)

**New role hierarchy:**
```python
ROLE_HIERARCHY = {
    "super_admin": 4,
    "owner": 3,       # NEW - replaces is_team_admin
    "engineer": 2,
    "viewer": 1,
}
```

**Key function updates:**

```python
def get_effective_role(user: User) -> str:
    return "super_admin" if user.is_super_admin else user.account_role

def can_manage_account(user: User) -> bool:
    return user.is_super_admin or user.account_role == "owner"

def can_edit_tree(user: User, tree: Tree) -> bool:
    if user.is_super_admin: return True
    if not can_create_content(user): return False
    if tree.author_id == user.id: return True
    # Account owners can edit any tree in their account
    if user.account_role == "owner" and tree.account_id == user.account_id:
        return True
    return False
```

**Replace everywhere:**
- `team_id` → `account_id`
- `is_team_admin` → `account_role == "owner"`
- Add `can_manage_account()` for billing/member management

---

#### 2.3: Add Subscription Helpers

**File:** `backend/app/core/subscriptions.py` (new)

Core functions:
```python
async def get_account_subscription(account_id, db) -> Subscription
async def get_plan_limits(plan: str, db) -> PlanLimits
async def get_user_plan_limits(user_id, db) -> PlanLimits

async def check_tree_limit(account_id, db) -> tuple[bool, Optional[int], int]
    # Returns: (can_create, limit, current_count)

async def check_session_limit(account_id, db) -> tuple[bool, Optional[int], int]
    # Counts sessions this month for entire account
```

These will be used by endpoints for feature gating.

---

#### 2.4: Update API Dependencies

**File:** `backend/app/api/deps.py`

Update:
```python
async def require_engineer_or_admin(current_user):
    if current_user.is_super_admin: return current_user
    if current_user.account_role in ("owner", "engineer"): return current_user
    raise HTTPException(403, "Engineer or admin access required")

async def require_account_owner(current_user):
    if current_user.is_super_admin or current_user.account_role == "owner":
        return current_user
    raise HTTPException(403, "Account owner access required")
```

Add new:
```python
async def get_plan_limits_for_user(current_user, db) -> PlanLimits:
    from app.core.subscriptions import get_user_plan_limits
    return await get_user_plan_limits(current_user.id, db)

def require_feature(feature_name: str):
    """Factory for feature-gated dependencies."""
    async def checker(limits = Depends(get_plan_limits_for_user)):
        if not getattr(limits, feature_name, False):
            raise HTTPException(402, "This feature requires a plan upgrade")
    return checker
```

Use HTTP 402 for subscription blocks (vs 403 for permission blocks).

---

#### 2.5: Update All Endpoints

**Files to modify:** trees.py, sessions.py, steps.py, categories.py, tags.py, folders.py, admin.py

**Pattern for creation endpoints:**

```python
@router.post("/trees", response_model=TreeResponse)
async def create_tree(
    tree_data: TreeCreate,
    current_user: Annotated[User, Depends(require_engineer_or_admin)],
    db: Annotated[AsyncSession, Depends(get_db)],
):
    # Check plan limit
    from app.core.subscriptions import check_tree_limit
    can_create, limit, current = await check_tree_limit(current_user.account_id, db)

    if not can_create:
        raise HTTPException(402, f"Tree limit reached ({current}/{limit}). Upgrade your plan.")

    new_tree = Tree(
        author_id=current_user.id,
        account_id=current_user.account_id,  # Changed from team_id
        # ...
    )
```

**Pattern for access filters:**

```python
def build_tree_access_filter(current_user: User):
    if current_user.is_super_admin:
        return sa_true()

    conditions = [
        Tree.is_default == True,
        Tree.is_public == True,
        Tree.author_id == current_user.id,
    ]

    if current_user.account_id:
        conditions.append(Tree.account_id == current_user.account_id)

    return or_(*conditions)
```

---

#### 2.6: Add Account Management Endpoints

**File:** `backend/app/api/endpoints/accounts.py` (new)

```python
GET  /api/v1/accounts/me              → AccountResponse
GET  /api/v1/accounts/me/subscription → SubscriptionResponse (includes limits)
GET  /api/v1/accounts/me/members      → List[User]
PATCH /api/v1/accounts/me/members/{id}/role  (owner only)
DELETE /api/v1/accounts/me/members/{id}      (owner only)
PATCH /api/v1/accounts/me              → Update account name
```

Member management requires `require_account_owner` dependency.

---

#### 2.7: Update Registration Endpoint

**File:** `backend/app/api/endpoints/auth.py`

```python
@router.post("/register")
async def register(user_data: UserCreate, db):
    # Validate invite code (if required)
    # Check email uniqueness

    # 1. Create account
    display_code = generate_unique_display_code(db)
    new_account = Account(
        name=f"{user_data.name}'s Account",
        display_code=display_code,
        owner_id=None  # Set after user creation
    )
    db.add(new_account)
    await db.flush()

    # 2. Create user
    new_user = User(
        email=user_data.email,
        password_hash=get_password_hash(user_data.password),
        name=user_data.name,
        account_id=new_account.id,
        account_role="owner",  # Personal accounts default to owner
    )
    db.add(new_user)
    await db.flush()

    # 3. Set account owner
    new_account.owner_id = new_user.id

    # 4. Create free subscription
    subscription = Subscription(
        account_id=new_account.id,
        plan="free",
        status="active",
    )
    db.add(subscription)

    await db.commit()
    return new_user
```

---

#### 2.8: Add Stripe Webhook Skeleton

**File:** `backend/app/api/endpoints/webhooks.py` (new)

```python
@router.post("/stripe")
async def stripe_webhook(request: Request, db: AsyncSession):
    if not settings.STRIPE_WEBHOOK_SECRET:
        return {"status": "ignored"}  # Not configured yet

    # Verify signature
    payload = await request.body()
    sig = request.headers.get("stripe-signature")
    event = stripe.Webhook.construct_event(payload, sig, settings.STRIPE_WEBHOOK_SECRET)

    # Dispatch to handler
    handler = WEBHOOK_HANDLERS.get(event['type'])
    if handler:
        await handler(event, db)

    return {"status": "received"}
```

**File:** `backend/app/core/stripe_handlers.py` (new)

Implement handlers for:
- `checkout.session.completed` - Create/update subscription with Stripe IDs
- `invoice.paid` - Update period dates, set status=active
- `invoice.payment_failed` - Set status=past_due, notify owner
- `customer.subscription.updated` - Sync plan changes
- `customer.subscription.deleted` - Downgrade to free

Initially these can be stubs that log events. Full implementation when Stripe is enabled.

**File:** `backend/app/core/config.py`

Add optional Stripe settings:
```python
STRIPE_SECRET_KEY: Optional[str] = None
STRIPE_PUBLISHABLE_KEY: Optional[str] = None
STRIPE_WEBHOOK_SECRET: Optional[str] = None

@property
def stripe_enabled(self) -> bool:
    return bool(self.STRIPE_SECRET_KEY and self.STRIPE_WEBHOOK_SECRET)
```

**Add to requirements.txt:** `stripe`

---

#### 2.9: Update Test Fixtures

**File:** `backend/tests/conftest.py`

**Critical updates:**

```python
@pytest.fixture
async def test_account(test_db):
    """Create account with free subscription."""
    account = Account(
        name="Test Account",
        display_code=generate_unique_code(),
        owner_id=None
    )
    test_db.add(account)
    await test_db.flush()

    subscription = Subscription(
        account_id=account.id,
        plan="free",
        status="active",
    )
    test_db.add(subscription)
    await test_db.commit()
    return account

@pytest.fixture
async def test_user(test_db, test_account):
    """Create user linked to test account."""
    user = User(
        email="test@example.com",
        password_hash=get_password_hash("TestPassword123!"),
        name="Test User",
        account_id=test_account.id,
        account_role="owner"
    )
    test_db.add(user)
    await test_db.flush()

    test_account.owner_id = user.id
    await test_db.commit()
    return user

@pytest.fixture
async def test_admin(test_db):
    """Create super admin."""
    # Create admin account + user with is_super_admin=True
```

**Strategy:** Update fixtures first, then fix failing tests incrementally (one module at a time).

---

### Phase 3: Frontend Updates (Days 8-10)

Update React app to use account-based API and add subscription UI.

#### 3.1: Update Type Definitions

**File:** `frontend/src/types/user.ts`

```typescript
export interface User {
  id: string;
  email: string;
  name: string;
  account_id: string;                        // Changed from team_id
  account_role: 'owner' | 'engineer' | 'viewer';  // Changed from role
  is_super_admin: boolean;
  is_active: boolean;
  created_at: string;
  last_login: string | null;
}
```

**File:** `frontend/src/types/account.ts` (new)

```typescript
export interface Account {
  id: string;
  name: string;
  display_code: string;
  owner_id: string;
  stripe_customer_id: string | null;
  created_at: string;
  updated_at: string;
}

export interface Subscription {
  plan: 'free' | 'pro' | 'team';
  status: 'active' | 'past_due' | 'canceled' | 'trialing';
  // ... other fields
}

export interface PlanLimits {
  plan: string;
  max_trees: number | null;
  max_sessions_per_month: number | null;
  custom_branding: boolean;
  export_formats: string[];
}

export interface SubscriptionDetails {
  subscription: Subscription;
  limits: PlanLimits;
}
```

---

#### 3.2: Update Auth Store

**File:** `frontend/src/store/authStore.ts`

Add to state:
```typescript
account: Account | null;
subscription: SubscriptionDetails | null;

setUser: (user: User, account: Account, subscription: SubscriptionDetails) => void;
```

Persist account and subscription in localStorage.

---

#### 3.3: Create Subscription Hook

**File:** `frontend/src/hooks/useSubscription.ts` (new)

```typescript
export function useSubscription() {
  const { subscription } = useAuthStore();

  return {
    plan: subscription.subscription.plan,
    limits: subscription.limits,

    canUseFeature: (feature: keyof PlanLimits) => Boolean(limits[feature]),
    isPaidPlan: plan !== 'free',
    isTeamPlan: plan === 'team',

    formatLimit: (resource: 'trees' | 'sessions') => {
      const key = resource === 'trees' ? 'max_trees' : 'max_sessions_per_month';
      return limits[key] === null ? 'Unlimited' : String(limits[key]);
    }
  };
}
```

---

#### 3.4: Update usePermissions Hook

**File:** `frontend/src/hooks/usePermissions.ts`

```typescript
const effectiveRole = user.is_super_admin ? 'super_admin' : user.account_role;

return {
  canCreateContent: effectiveRole !== 'viewer',
  canManageAccount: user.is_super_admin || user.account_role === 'owner',
  isAccountOwner: user.account_role === 'owner',

  canEditTree: (tree) => {
    if (user.is_super_admin) return true;
    if (effectiveRole === 'viewer') return false;
    if (tree.author_id === user.id) return true;
    if (user.account_role === 'owner' && tree.account_id === user.account_id) return true;
    return false;
  },

  canDeleteTree: (tree) => {
    if (user.is_super_admin) return true;
    return user.account_role === 'owner' && tree.account_id === user.account_id;
  },
};
```

---

#### 3.5: Create Account API Client

**File:** `frontend/src/api/accounts.ts` (new)

```typescript
export const accountsApi = {
  getMyAccount: () => api.get('/api/v1/accounts/me'),
  getMySubscription: () => api.get('/api/v1/accounts/me/subscription'),
  getMembers: () => api.get('/api/v1/accounts/me/members'),
  updateMemberRole: (userId: string, role: 'engineer' | 'viewer') =>
    api.patch(`/api/v1/accounts/me/members/${userId}/role`, { role }),
  removeMember: (userId: string) =>
    api.delete(`/api/v1/accounts/me/members/${userId}`),
};
```

---

#### 3.6: Update Login/Register to Fetch Account Data

**File:** `frontend/src/api/auth.ts`

```typescript
export const authApi = {
  login: async (credentials) => {
    const { data } = await api.post('/api/v1/auth/login/json', credentials);
    const { access_token, refresh_token, user } = data;

    // Fetch account and subscription
    const [accountRes, subRes] = await Promise.all([
      accountsApi.getMyAccount(),
      accountsApi.getMySubscription()
    ]);

    return {
      access_token,
      refresh_token,
      user,
      account: accountRes.data,
      subscription: subRes.data
    };
  }
};
```

Update auth store login action to call `setUser(user, account, subscription)`.

---

#### 3.7: Add Account Settings Page

**File:** `frontend/src/pages/AccountSettingsPage.tsx` (new)

Sections:
1. **Subscription Info**
   - Current plan badge (Free/Pro/Team)
   - Usage stats (trees, sessions this month)
   - Upgrade button (if free plan)

2. **Team Members** (only for account owners)
   - List members with role dropdowns
   - Remove member button
   - Generate invite link button

3. **Billing** (only for paid plans)
   - Link to Stripe Customer Portal (when Stripe enabled)

Use `useSubscription()` and `usePermissions()` hooks.

**Route:** Add `/settings/account` to router.

---

#### 3.8: Add Usage Indicators

**File:** `frontend/src/pages/TreeLibraryPage.tsx`

Near "Create Tree" button, show:
```tsx
const { formatLimit } = useSubscription();
const treeCount = trees?.length || 0;

<span className="text-sm text-muted-foreground">
  {limits.max_trees === null
    ? `${treeCount} trees`
    : `${treeCount} / ${limits.max_trees} trees`
  }
</span>
```

Disable "Create Tree" button if at limit, show upgrade prompt.

---

#### 3.9: Add Upgrade Prompt Component

**File:** `frontend/src/components/common/UpgradePrompt.tsx` (new)

```tsx
export function UpgradePrompt({ feature, description }: Props) {
  const { plan } = useSubscription();

  return (
    <div className="bg-yellow-50 border border-yellow-200 rounded-lg p-4">
      <AlertCircle className="text-yellow-600" />
      <div>
        <h3>{feature} requires an upgrade</h3>
        <p>{description}</p>
        <Button>Upgrade from {plan}</Button>
      </div>
    </div>
  );
}
```

Use when API returns 402 Payment Required.

---

#### 3.10: Add Stripe Checkout Button (Disabled)

**File:** `frontend/src/components/subscription/CheckoutButton.tsx` (new)

```tsx
const stripePromise = import.meta.env.VITE_STRIPE_KEY
  ? loadStripe(import.meta.env.VITE_STRIPE_KEY)
  : null;

export function CheckoutButton({ plan, interval }) {
  const handleCheckout = async () => {
    if (!stripePromise) {
      alert('Payment processing coming soon!');
      return;
    }

    // TODO: Create Checkout Session and redirect
  };

  return (
    <Button onClick={handleCheckout} disabled={!stripePromise}>
      {stripePromise ? 'Subscribe Now' : 'Coming Soon'}
    </Button>
  );
}
```

Shows "Coming Soon" until `VITE_STRIPE_KEY` is set.

**Add to `.env.local.example`:**
```
VITE_STRIPE_KEY=pk_test_...  # Optional - only needed for paid plans
```

---

### Phase 4: Stripe Preparation (Days 11-12)

Set up Stripe infrastructure without requiring active billing.

#### 4.1: Install Stripe Libraries

**Backend:**
```bash
pip install stripe
pip freeze > requirements.txt
```

**Frontend:**
```bash
npm install @stripe/stripe-js @stripe/react-stripe-js
```

---

#### 4.2: Stripe Dashboard Setup (Manual)

When ready to enable billing:

1. **Create Products:**
   - ResolutionFlow Pro
     - Monthly price (note price_id: `price_pro_monthly`)
     - Annual price (note price_id: `price_pro_annual`)
   - ResolutionFlow Team
     - Monthly tiers (5 users, 15 users, 50 users)
     - Annual tiers

2. **Configure Webhook:**
   - Endpoint URL: `https://api.resolutionflow.com/api/v1/webhooks/stripe`
   - Select events: checkout.session.completed, invoice.paid, invoice.payment_failed, customer.subscription.updated, customer.subscription.deleted
   - Note webhook signing secret → `STRIPE_WEBHOOK_SECRET`

3. **Customer Portal:**
   - Enable self-service billing management
   - Configure allowed actions (cancel, change plan, update payment method)

---

#### 4.3: Environment Variables

**Backend `.env` (production):**
```bash
STRIPE_SECRET_KEY=sk_live_...
STRIPE_WEBHOOK_SECRET=whsec_...
```

**Frontend `.env.production`:**
```bash
VITE_STRIPE_KEY=pk_live_...
```

Leave empty until ready to enable billing. Code handles missing keys gracefully.

---

## Testing Strategy

### Phase 1: Database Migration Testing

After each migration:
```bash
# Run migration
alembic upgrade head

# Verify schema
docker exec -it patherly_postgres psql -U postgres -d patherly -c "\d users"
docker exec -it patherly_postgres psql -U postgres -d patherly -c "\d accounts"

# Verify data integrity
docker exec -it patherly_postgres psql -U postgres -d patherly -c "
  SELECT COUNT(*) FROM users WHERE account_id IS NULL;
  SELECT COUNT(*) FROM accounts;
  SELECT COUNT(*) FROM subscriptions;
  SELECT plan, COUNT(*) FROM subscriptions GROUP BY plan;
"

# Run backend tests (should pass even before code changes for migrations 16-17)
cd backend
pytest --override-ini="addopts="
```

**Test migration 018 on production copy:**
```bash
# Dump production data
docker exec patherly_postgres pg_dump -U postgres patherly > prod_backup.sql

# Load into test DB
docker exec -i patherly_postgres psql -U postgres -c "CREATE DATABASE patherly_migration_test"
docker exec -i patherly_postgres psql -U postgres patherly_migration_test < prod_backup.sql

# Run migration 018 on test DB
# Edit DATABASE_URL to point to patherly_migration_test
alembic upgrade 018

# Verify results, then drop test DB when satisfied
```

---

### Phase 2: Backend Testing

**Incremental test fixing:**
```bash
# Fix test fixtures first
pytest tests/conftest.py -v

# Fix tests one module at a time
pytest tests/test_auth.py -v
pytest tests/test_trees.py -v
pytest tests/test_sessions.py -v
pytest tests/test_admin.py -v
pytest tests/test_permissions.py -v

# Full suite
pytest --override-ini="addopts="
```

**Add new tests:**
- `tests/test_subscription_limits.py` - Tree/session limit enforcement, 402 responses
- `tests/test_account_management.py` - Member role changes, account owner permissions
- `tests/test_permissions_account.py` - New account-role-based permission checks

**Goal:** 61+ passing tests before merging to main.

---

### Phase 3: Frontend Testing

**Type checking:**
```bash
cd frontend
npm run type-check  # or npx tsc --noEmit
```

**Manual test checklist:**
- [ ] Login with migrated user sees account info
- [ ] Settings page shows subscription (Free plan, 3/3 trees)
- [ ] Tree library shows usage count (X / Y trees)
- [ ] Creating tree at limit shows 402 error → upgrade prompt
- [ ] Account owner can see team members
- [ ] Account owner can change member role (engineer ↔ viewer)
- [ ] Viewer cannot create trees (403 error)
- [ ] Viewer can browse trees and start sessions
- [ ] Super admin bypasses all checks
- [ ] Logout and re-login preserves account/subscription in store

---

### Phase 4: Stripe Testing

**Test mode validation:**
```bash
# Use Stripe test keys
STRIPE_SECRET_KEY=sk_test_...
STRIPE_WEBHOOK_SECRET=whsec_test_...
```

**Webhook testing with Stripe CLI:**
```bash
stripe listen --forward-to localhost:8000/api/v1/webhooks/stripe

# Trigger test events
stripe trigger checkout.session.completed
stripe trigger invoice.paid
stripe trigger customer.subscription.deleted
```

Verify:
- [ ] Webhook endpoint receives events
- [ ] Signature verification works
- [ ] Events are logged (even if handlers are stubs)
- [ ] Invalid signature returns 400

---

## Deployment Strategy

### Development (Feature Branch)

```bash
# Create feature branch
git checkout -b feat/subscription-tiers

# Commit each phase incrementally
git add backend/alembic/versions/016_*.py
git commit -m "feat: add subscription tables (migration 016)"

git add backend/alembic/versions/017_*.py
git commit -m "feat: add account_id to users (migration 017)"

# ... continue for each phase
```

**Keep commits atomic:** Each migration in its own commit for easy rollback.

---

### Staging/Preview (Railway PR Environment)

1. **Open PR:** `feat/subscription-tiers` → `main`
2. Railway auto-creates preview environment
3. **Generate domains** for PR services in Railway dashboard
4. **Run migrations** on PR database (happens automatically via `releaseCommand`)
5. **Test thoroughly** using preview URL
6. **QA checklist:**
   - [ ] Migrations ran successfully (check Railway logs)
   - [ ] Backend tests pass in CI
   - [ ] Frontend builds without errors
   - [ ] Manual smoke test: register new user → see Free account → browse trees
   - [ ] Webhook endpoint returns 200 (even with no Stripe config)

---

### Production Deployment

**Prerequisites:**
- [ ] All tests passing in CI
- [ ] Manual QA complete on PR environment
- [ ] Database backup created
- [ ] Rollback plan reviewed

**Steps:**

1. **Merge PR:** `feat/subscription-tiers` → `main`
2. Railway auto-deploys to production
3. Migrations run automatically (via `releaseCommand` in railway.json)
4. **Monitor deployment:**
   - Check Railway logs for migration success
   - Watch for errors in backend logs
   - Test login/registration immediately

5. **Smoke test:**
   - [ ] Login as existing user → verify account_id populated
   - [ ] View trees → verify account_id filter works
   - [ ] Create tree → verify limit enforcement (if at limit)
   - [ ] Check settings page → see Free plan, usage stats

6. **Rollback if needed:**
   - Railway: Redeploy previous version
   - Database: `alembic downgrade <migration_number>`
   - Restore from backup if migration cannot be reversed

**Expected downtime:** <1 minute (time for migrations to run)

---

## Rollback Plans

### Rollback After Migration 016-018 (Safe)

```bash
# Downgrade migrations one at a time
alembic downgrade -1  # Undo 018
alembic downgrade -1  # Undo 017
alembic downgrade -1  # Undo 016

# Verify rollback
docker exec -it patherly_postgres psql -U postgres -d patherly -c "\dt"
# Should NOT see: accounts, subscriptions, plan_limits, account_invites
# users.account_id should be NULL for all rows

# Run tests to confirm
pytest --override-ini="addopts="
```

---

### Rollback After Migration 019-020 (Careful)

```bash
alembic downgrade 018  # Undo constraints and FK migration

# Manually verify
docker exec -it patherly_postgres psql -U postgres -d patherly -c "
  SELECT column_name, is_nullable
  FROM information_schema.columns
  WHERE table_name = 'users' AND column_name IN ('account_id', 'account_role');
"
# account_id should be nullable again
```

---

### Rollback After Migration 021 (Point of No Return)

**Cannot easily rollback** - teams table is dropped.

**Options:**
1. Restore from database backup (recommended)
2. Manually recreate teams table and reverse-migrate data (complex, error-prone)

**Prevention:** Only run migration 021 AFTER Phase 2 backend code is deployed, tested, and stable in production for at least 24 hours.

---

## Risk Assessment

### High-Risk Items

| Risk | Impact | Mitigation |
|------|--------|------------|
| **Migration 018 data loss** | High - could orphan users/content | Test on production copy first, comprehensive validation in migration code |
| **Permission system breaks access** | High - users locked out of content | Thorough test coverage, manual QA with different roles |
| **Test suite breakage** | Medium - deployment blocked | Fix fixtures first, update tests incrementally |
| **Circular FK deadlock** | Medium - migration fails | Keep owner_id nullable until after user creation |

### Medium-Risk Items

| Risk | Impact | Mitigation |
|------|--------|------------|
| **Stripe webhook downtime** | Medium - subscriptions not updated | Webhook endpoint logs events even if handlers fail |
| **Frontend type errors** | Medium - build fails | TypeScript strict mode, incremental type updates |
| **Plan limit edge cases** | Low - users blocked unexpectedly | Conservative limits initially, easy to adjust via DB |

### Low-Risk Items

- Stripe integration (disabled by default, no impact until enabled)
- UI changes (progressive enhancement, no breaking changes)
- Account settings page (new page, doesn't affect existing flows)

---

## Critical Files

These files are the foundation of the implementation. Get these right and the rest follows:

1. **`backend/alembic/versions/018_migrate_users_to_accounts.py`**
   - Most critical migration
   - Handles data transformation from teams → accounts
   - Must be idempotent and thoroughly tested on production copy

2. **`backend/app/models/user.py`**
   - Core model change
   - Affects all relationships and permission checks
   - Breaking change point

3. **`backend/app/core/permissions.py`**
   - Permission system refactor
   - Every endpoint depends on this
   - Must handle account_role correctly

4. **`backend/app/core/subscriptions.py`**
   - Feature gating logic
   - Used by all content creation endpoints
   - Pattern for limit enforcement

5. **`backend/tests/conftest.py`**
   - Test fixture foundation
   - Updating correctly ensures incremental test fixing
   - Prevents catastrophic test breakage

6. **`frontend/src/types/user.ts` + `account.ts`**
   - Type foundation for frontend
   - Breaking change for all components using User type
   - Must match backend schemas exactly

---

## Success Criteria

### Minimum Viable Implementation

- [ ] All 6 migrations run successfully
- [ ] All 61+ backend tests passing
- [ ] Frontend builds without type errors
- [ ] Can register new user → creates account + free subscription
- [ ] Can login → sees account info in settings
- [ ] Tree creation enforces limits (shows 402 at limit)
- [ ] Account owners can manage team members
- [ ] Viewers are blocked from content creation
- [ ] Super admins bypass all checks
- [ ] Webhook endpoint exists and responds 200

### Production Ready

- [ ] Deployed to Railway production
- [ ] Existing users migrated to accounts successfully
- [ ] All features working as expected
- [ ] No permission leaks or access control issues
- [ ] Monitoring in place for errors
- [ ] Rollback plan tested and documented

### Stripe Ready (Future)

- [ ] Stripe products/prices created
- [ ] Webhook handlers fully implemented
- [ ] Checkout flow tested in test mode
- [ ] Customer portal configured
- [ ] Production keys added to environment

---

## Post-Implementation

After successful deployment:

1. **Monitor for 48 hours:**
   - Watch error logs for permission issues
   - Track failed tree creation attempts (limits)
   - Monitor webhook endpoint for spam/attacks

2. **User communication:**
   - Notify users of new account system
   - Explain free tier limits
   - Link to upgrade page (when ready)

3. **Documentation updates:**
   - Update README with new architecture
   - Document account management for team admins
   - Add subscription tier comparison table

4. **Future enhancements:**
   - Usage dashboard (current vs limits)
   - Email notifications for limit warnings
   - Team invite flow UI
   - Multi-path registration (start free / start pro / join team)
   - Stripe Checkout integration

---

## Timeline Summary

| Phase | Duration | Milestone |
|-------|----------|-----------|
| Phase 1: Database Migration | 2-3 days | All migrations pass, data migrated |
| Phase 2: Backend Updates | 3-4 days | All tests passing, endpoints updated |
| Phase 3: Frontend Updates | 2-3 days | TypeScript errors fixed, UI complete |
| Phase 4: Stripe Prep | 1-2 days | Webhook skeleton, test mode working |
| **Total** | **10-12 days** | **Production deployment** |

Add 1-2 days buffer for unexpected issues, QA, and deployment.

---

## Verification Checklist

Before merging to main:

**Database:**
- [ ] All 6 migrations run successfully in order
- [ ] Rollback tested for migrations 016-018
- [ ] Data integrity verified (no NULL account_id)
- [ ] plan_limits table seeded with 3 rows

**Backend:**
- [ ] 61+ tests passing
- [ ] New tests added for subscriptions and limits
- [ ] All endpoints use account_id (not team_id)
- [ ] Permissions use account_role (not role/is_team_admin)
- [ ] 402 returned when hitting plan limits
- [ ] Webhook endpoint responds to Stripe events

**Frontend:**
- [ ] TypeScript compiles with no errors
- [ ] All components use account_id
- [ ] usePermissions hook updated
- [ ] useSubscription hook works
- [ ] Settings page shows subscription info
- [ ] Usage indicators display correctly
- [ ] Upgrade prompts show on 402 errors

**Integration:**
- [ ] Can register new user
- [ ] Can login and see account info
- [ ] Can create trees (up to limit)
- [ ] Cannot create tree at limit (shows upgrade prompt)
- [ ] Account owner can manage members
- [ ] Viewer role blocked from creation
- [ ] Super admin bypasses everything

---

## Notes

- **Keep this plan updated** as implementation progresses
- **Document deviations** if approach changes during implementation
- **Link commits** back to plan phases for traceability
- **Celebrate milestones** - this is a major architectural upgrade!