User ID Migration Analysis: From User-Centric to Organization-Centric Architecture

Table of Contents

1. Current Architecture
2. The Problem
3. Proposed Solution
4. Authorization Comparison

---

Current Architecture

Database Schema (Current State)

erDiagram
    erp_connections {
        uuid id PK
        text clerk_organization_id "NOT NULL"
        text owner_user_id "NOT NULL"
        text type
        boolean active
    }

    erp_connection_access {
        uuid id PK
        uuid connection_id FK
        text user_id "NOT NULL"
    }

    orders {
        uuid id PK
        text clerk_organization_id "NOT NULL"
        text user_id "NOT NULL - REQUIRED"
        uuid erp_connection_id FK
        text customer_name
        text status
    }

    pdf_documents {
        uuid id PK
        text clerk_organization_id "NOT NULL"
        text user_id "NOT NULL - REQUIRED"
        varchar filename
        text status
        text source
    }

    extracted_orders {
        uuid id PK
        text clerk_organization_id "NULLABLE"
        uuid pdf_document_id FK
    }

    erp_connections ||--o{ orders : "has many"
    erp_connections ||--o{ erp_connection_access : "grants access"
    erp_connections ||--o{ pdf_documents : "processes"
    pdf_documents ||--o{ extracted_orders : "extracts"
    extracted_orders ||--o| orders : "creates"

Current Order Creation Flow

sequenceDiagram
    participant User
    participant WebApp
    participant Auth as Clerk Auth
    participant DB as Database
    participant ERP as ERP System

    User->>WebApp: Upload PDF / Create Order
    WebApp->>Auth: Verify user session
    Auth-->>WebApp: userId + organizationId

    WebApp->>DB: Check user has access
    DB-->>WebApp: Verify userId in org

    WebApp->>DB: INSERT pdf_documents
    Note over DB: userId = "user_123" (REQUIRED)
    Note over DB: clerkOrganizationId = "org_ABC"

    DB->>DB: Process document

    DB->>DB: INSERT extracted_orders
    DB->>DB: INSERT orders
    Note over DB: userId = "user_123" (REQUIRED)

    WebApp->>ERP: Submit order
    ERP-->>WebApp: Success

    WebApp-->>User: Order created successfully

Current Authorization Pattern

// Current query pattern - REQUIRES userId
const userOrders = await db.query.orders.findMany({
  where: and(
    eq(orders.userId, userId), // ← Must match user
    eq(orders.clerkOrganizationId, orgId) // AND organization
  ),
});

// Current document creation - REQUIRES userId
await db.insert(pdfDocuments).values({
  userId: userId, // ← NOT NULL constraint
  clerkOrganizationId: orgId,
  filename: 'order.pdf',
  status: 'pending',
});

Current Inbox View

graph TD
    A[User logs in] --> B{Has userId?}
    B -->|Yes| C[Query: userId = current_user]
    C --> D[Show ONLY my documents]
    D --> E[Personal Inbox View]

    B -->|No| F[ERROR: Cannot create record]

    style F fill:#ff6b6b
    style E fill:#51cf66

Current Behavior:

• ✅ User can only see their own orders/documents
• ✅ Clear ownership model
• ❌ Cannot create orders without authenticated user
• ❌ No shared organization inbox
• ❌ External systems cannot create orders (no user context)

---

The Problem

Blocking Issue: External Order Ingestion

The current architecture blocks external order creation via email aliases because:

1. Email arrives at organization alias (orders+ACME@erpunlocked.com)
2. No authenticated user in the email context
3. Database requires user_id NOT NULL for both pdf_documents and orders
4. Creation fails ❌

flowchart TD
    A[Email: orders+ACME@erpunlocked.com] -->|Email Processor| B{Lookup Alias}
    B -->|Found: orgId only| C[Try Create Document]
    C -->|userId = ???| D{Database Constraint}
    D -->|user_id NOT NULL| E[❌ FAILS]
    D -->|Workaround: fake user| F[⚠️ Bad Data]

    style E fill:#ff6b6b
    style F fill:#ffd43b

Problem Scenarios

Scenario 1: Organization Email Alias

// Email received at: orders+ACME@erpunlocked.com
// Alias lookup returns:
{
  subaddress: "ACME",
  clerkOrganizationId: "org_123",
  userId: null,  // ← Organization-wide alias
  erpConnectionId: "conn_456"
}

// Attempt to create document
await db.insert(pdfDocuments).values({
  clerkOrganizationId: "org_123",
  userId: null,  // ← ERROR: violates NOT NULL constraint
  filename: "po-001.pdf"
});
// ❌ Database error: null value in column "user_id"

Scenario 2: Current Workarounds (All Bad)

Option A: Create fake “system” user

userId: 'system_bot_user'; // ❌ Pollutes user data

• Breaks audit trails
• Confuses analytics
• Not semantically correct

Option B: Assign to random org member

const randomUser = orgMembers[0];
userId: randomUser.id; // ❌ Incorrect ownership

• False attribution
• User gets orders they didn’t create
• Permission issues

Option C: Reject external orders

if (!userId) {
  throw new Error('User required'); // ❌ Feature doesn't work
}

• Email alias feature is unusable
• Manual workarounds required

Impact on Authorization Queries

Current queries fail to support organization-scoped access:

// ❌ Current: Can only filter by user
const orders = await db.query.orders.findMany({
  where: eq(orders.userId, userId), // Misses org-wide orders
});

// ❌ Current: No way to show unassigned orders
const unassignedOrders = await db.query.orders.findMany({
  where: isNull(orders.userId), // ERROR: Violates NOT NULL
});

Database Constraint Violations

-- Current schema
CREATE TABLE pdf_documents (
  id UUID PRIMARY KEY,
  user_id TEXT NOT NULL,  -- ← BLOCKER
  clerk_organization_id TEXT NOT NULL,
  filename VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id TEXT NOT NULL,  -- ← BLOCKER
  clerk_organization_id TEXT NOT NULL,
  customer_name TEXT NOT NULL
);

Result: Cannot insert records with user_id = NULL → External order ingestion is impossible.

---

Proposed Solution

New Database Schema

erDiagram
    erp_connections {
        uuid id PK
        text clerk_organization_id "NOT NULL"
        text owner_user_id "NOT NULL"
        text type
        boolean active
    }

    erp_connection_access {
        uuid id PK
        uuid connection_id FK
        text user_id "NOT NULL"
    }

    orders {
        uuid id PK
        text clerk_organization_id "NOT NULL - Primary Scope"
        text user_id "NULLABLE - Optional Assignment"
        uuid erp_connection_id FK
        text customer_name
        text status
    }

    pdf_documents {
        uuid id PK
        text clerk_organization_id "NOT NULL - Primary Scope"
        text user_id "NULLABLE - Optional Assignment"
        varchar filename
        text status
        text source
    }

    extracted_orders {
        uuid id PK
        text clerk_organization_id "NULLABLE"
        uuid pdf_document_id FK
    }

    email_order_aliases {
        uuid id PK
        text clerk_organization_id "NOT NULL"
        text user_id "NULLABLE - For user aliases"
        varchar subaddress "UNIQUE"
        text alias_type "org or user"
    }

    erp_connections ||--o{ orders : "has many"
    erp_connections ||--o{ erp_connection_access : "grants access"
    erp_connections ||--o{ pdf_documents : "processes"
    pdf_documents ||--o{ extracted_orders : "extracts"
    extracted_orders ||--o| orders : "creates"
    email_order_aliases ||--o{ pdf_documents : "triggers"

Key Changes:

• ✅ orders.user_id → NULLABLE
• ✅ pdf_documents.user_id → NULLABLE
• ✅ clerk_organization_id becomes primary scope
• ✅ New email_order_aliases table for routing

New Order Creation Flow (External)

sequenceDiagram
    participant Customer
    participant EmailSvc as Email Service
    participant EmailProc as Email Processor
    participant DB as Database
    participant Worker as PDF Worker
    participant ERP as ERP System

    Customer->>EmailSvc: Forward email to<br/>orders+ACME@erpunlocked.com
    EmailSvc->>EmailProc: Webhook notification

    EmailProc->>DB: SELECT FROM email_order_aliases<br/>WHERE subaddress = 'ACME'
    DB-->>EmailProc: {orgId: "org_123", userId: null}

    EmailProc->>DB: INSERT pdf_documents
    Note over DB: clerkOrganizationId = "org_123"
    Note over DB: userId = NULL ✅
    Note over DB: source = "external"

    DB-->>EmailProc: Document created

    EmailProc->>Worker: Trigger processing
    Worker->>Worker: Extract order data
    Worker->>DB: INSERT extracted_orders
    Worker->>DB: INSERT orders
    Note over DB: clerkOrganizationId = "org_123"
    Note over DB: userId = NULL ✅

    Worker->>ERP: Validate items
    ERP-->>Worker: Items valid

    Worker->>DB: Update status: ready
    DB-->>Customer: Email confirmation (optional)

New Order Creation Flow (Manual Upload)

sequenceDiagram
    participant User
    participant WebApp
    participant Auth as Clerk Auth
    participant DB as Database
    participant ERP as ERP System

    User->>WebApp: Upload PDF manually
    WebApp->>Auth: Verify user session
    Auth-->>WebApp: userId + organizationId

    WebApp->>DB: INSERT pdf_documents
    Note over DB: clerkOrganizationId = "org_ABC"
    Note over DB: userId = "user_123" ✅
    Note over DB: source = "manual"

    DB->>DB: Process & extract
    DB->>DB: INSERT orders
    Note over DB: userId = "user_123" ✅

    WebApp->>ERP: Submit order
    ERP-->>WebApp: Success
    WebApp-->>User: Order created

New Authorization Pattern (Organization-Scoped)

// ✅ NEW: Organization-scoped with optional user filter
const organizationOrders = await db.query.orders.findMany({
  where: and(
    eq(orders.clerkOrganizationId, orgId), // ← Primary filter
    or(
      isNull(orders.userId), // Unassigned orders
      eq(orders.userId, userId), // My orders
      inArray(orders.erpConnectionId, userConnections) // Shared
    )
  ),
});

// ✅ NEW: Can create without user
await db.insert(pdfDocuments).values({
  clerkOrganizationId: orgId,
  userId: null, // ← Now allowed!
  filename: 'order.pdf',
  source: 'external',
});

// ✅ NEW: Can create with user (backward compatible)
await db.insert(pdfDocuments).values({
  clerkOrganizationId: orgId,
  userId: userId, // ← Still supported
  filename: 'order.pdf',
  source: 'manual',
});

New Dual Inbox System

graph TD
    A[User logs in] --> B{Select View}
    B -->|Personal| C[Query: userId = current_user]
    B -->|Team| D[Query: clerkOrganizationId = org]

    C --> E[My Inbox]
    E --> F[Show only MY documents]

    D --> G[Team Inbox]
    G --> H{Filter Options}
    H -->|All| I[Show ALL org documents]
    H -->|Unassigned| J[Show userId = NULL]
    H -->|Assigned| K[Show userId IS NOT NULL]
    H -->|By User| L[Show specific user's docs]

    J --> M[Claim Button Available]
    M --> N[Assign to Me]
    N --> O[Update userId = current_user]

    style E fill:#51cf66
    style G fill:#339af0
    style M fill:#ffd43b

Three Creation Scenarios

flowchart TD
    A[Order Source] --> B{Type?}


    B -->|User Email Alias| F["orders+ACME_john@..."]
    F --> G[Lookup: userId = 'user_123']
    G --> H[userId = 'user_123']
    H --> I[Document in John's Inbox]

    B -->|Org Email Alias| J["orders+ACME@..."]
    J --> K[Lookup: userId = null]
    K --> L[userId = NULL]
    L --> M[Document in Team Inbox]
    M --> N[Any member can claim]

    style I fill:#51cf66
    style M fill:#ffd43b

Inbox Views Comparison

Personal Inbox (/inbox)

// Show ONLY documents assigned to current user
const myDocuments = await db.query.pdfDocuments.findMany({
  where: and(
    eq(pdfDocuments.clerkOrganizationId, orgId),
    eq(pdfDocuments.userId, userId) // ← My assignments only
  ),
  orderBy: desc(pdfDocuments.createdAt),
});

Displays:

• ✅ Documents I uploaded
• ✅ Documents assigned to me via user alias
• ✅ Documents I claimed from team inbox
• ❌ NOT unassigned documents
• ❌ NOT other users’ documents

Team Inbox (/inbox/team)

// Show ALL documents in organization
const teamDocuments = await db.query.pdfDocuments.findMany({
  where: eq(pdfDocuments.clerkOrganizationId, orgId), // ← All org docs
  orderBy: desc(pdfDocuments.createdAt),
});

Displays:

• ✅ ALL organization documents
• ✅ Unassigned documents (userId = null)
• ✅ Documents assigned to any team member
• ✅ With visual badges showing assignment status
• ✅ “Assign to Me” button for unassigned docs

---

Backward Compatibility

flowchart TD
    A[Existing Records] --> B{Has user_id?}
    B -->|Yes - 100% of current data| C[✅ No Changes Needed]
    C --> D[Continues working exactly as before]

    E[New Records] --> F{Source?}
    F -->|Manual Upload| G[userId = current_user]
    F -->|User Alias| H[userId = alias.userId]
    F -->|Org Alias| I[userId = NULL]

    G --> J[✅ Works with new schema]
    H --> J
    I --> J

    style C fill:#51cf66
    style D fill:#51cf66
    style J fill:#51cf66

Compatibility Guarantee:

• ✅ All existing records have user_id populated (no data loss)
• ✅ Existing queries continue to work (they see more data, not less)
• ✅ User-uploaded documents still assigned to uploading user
• ✅ No breaking changes to API contracts
• ✅ Authorization boundaries maintained (organization isolation)

---

Authorization Comparison

Before: User-Centric Authorization

// ❌ OLD: User is the primary boundary
async function getOrders(userId: string) {
  return await db.query.orders.findMany({
    where: eq(orders.userId, userId),
  });
}

// Problems:
// 1. No organization context
// 2. Cannot query org-wide orders
// 3. No support for unassigned orders
// 4. Cross-org access possible if userId reused

Security Model:

User → Orders → Data
 ↑
 └─ Primary security boundary (weak)

After: Organization-Centric Authorization

// ✅ NEW: Organization is the primary boundary
async function getOrders(orgId: string, userId: string, view: 'personal' | 'team') {
  const baseConditions = [
    eq(orders.clerkOrganizationId, orgId), // ← Primary boundary
  ];

  if (view === 'personal') {
    baseConditions.push(eq(orders.userId, userId));
  }
  // Team view shows all org orders

  return await db.query.orders.findMany({
    where: and(...baseConditions),
  });
}

// Benefits:
// 1. Organization is the security boundary
// 2. User assignment is optional metadata
// 3. Supports unassigned orders
// 4. Strong multi-tenant isolation

Security Model:

Organization → Orders → User Assignment (optional)
     ↑
     └─ Primary security boundary (strong)

Authorization Flow Comparison

Current Flow (User-Centric)

sequenceDiagram
    participant User
    participant Auth
    participant DB

    User->>Auth: Request orders
    Auth->>Auth: Verify userId
    Auth->>DB: Query WHERE userId = ?
    DB-->>Auth: Return user's orders only
    Auth-->>User: Orders

    Note over DB: Cannot access org orders
    Note over DB: Cannot see unassigned

New Flow (Organization-Centric)

sequenceDiagram
    participant User
    participant Auth
    participant DB

    User->>Auth: Request orders
    Auth->>Auth: Verify userId + orgId
    Auth->>DB: Query WHERE orgId = ? AND<br/>(userId = ? OR userId IS NULL)
    DB-->>Auth: Return org orders with optional filter
    Auth-->>User: Orders (personal or team view)

    Note over DB: ✅ Can access org orders
    Note over DB: ✅ Can see unassigned
    Note over DB: ✅ Can filter by user

Security Boundaries

Aspect	Before (User-Centric)	After (Organization-Centric)
Primary Filter	user_id	clerk_organization_id
Secondary Filter	None	user_id (optional)
Cross-Org Protection	⚠️ Weak (user_id only)	✅ Strong (org-first)
Unassigned Records	❌ Not supported	✅ Supported
Team Visibility	❌ No visibility	✅ Full visibility
External Creation	❌ Blocked	✅ Enabled
Audit Trail	user_id required	org + optional user

---

Summary

Key Improvements

Feature	Before	After
External Order Creation	❌ Blocked	✅ Enabled
Organization Email Aliases	❌ Not possible	✅ Fully supported
Team Visibility	❌ None	✅ Dual inbox system
Unassigned Orders	❌ Cannot exist	✅ Supported with assignment
Security Model	⚠️ User-first (weak)	✅ Org-first (strong)
Backward Compatibility	N/A	✅ 100% compatible
Manual Uploads	✅ Works	✅ Works (unchanged)
User Assignment	Required	Optional

Manual Upload Flow - NO CHANGES

Important: The manual upload flow remains exactly the same as the current implementation. Users will not notice any difference in their experience.

Current Manual Upload Process (Unchanged):

Key Points:

• ✅ Manual uploads always have userId populated
• ✅ User experience is identical to current behavior
• ✅ No changes to existing workflows
• ✅ Full backward compatibility maintained

The migration only affects:

• External order creation (new feature)
• Organization email aliases (new feature)
• Team inbox visibility (new feature)
• Unassigned order handling (new feature)

Manual uploads continue to work exactly as they do today.