Data Model

┌────────────────┐       ┌────────────────┐
│     User       │       │  Organization  │
├────────────────┤       ├────────────────┤
│ id             │       │ id             │
│ email          │       │ name           │
│ name           │       │ slug           │
│ avatarUrl      │       │ logoUrl        │
│ emailVerified  │       │ settings       │
└───────┬────────┘       └───────┬────────┘
        │                        │
        │   ┌────────────────┐   │
        └───│   Membership   │───┘
            ├────────────────┤
            │ userId         │
            │ organizationId │
            │ role           │
            │ permissions    │
            └───────┬────────┘
                    │
        ┌───────────┴───────────┐
        │                       │
┌───────▼────────┐     ┌───────▼────────┐
│  Subscription  │     │    Product     │
├────────────────┤     ├────────────────┤
│ id             │     │ id             │
│ organizationId │◄────│ name           │
│ productId      │     │ slug           │
│ status         │     │ features       │
│ seats          │     │ description    │
└────────────────┘     └────────────────┘

interface User {
  id: string;           // Unique identifier
  email: string;        // Email address (unique)
  name?: string;        // Display name
  avatarUrl?: string;   // Profile picture
  emailVerified: boolean;
  createdAt: Date;
  updatedAt: Date;
}

interface Organization {
  id: string;
  name: string;         // Display name
  slug: string;         // URL-safe identifier (unique)
  logoUrl?: string;
  settings: OrgSettings;
  createdAt: Date;
}
 
interface OrgSettings {
  allowedDomains?: string[];
  requireMfa?: boolean;
  sessionTimeout?: number;
  samlConfig?: SAMLConfig;
}

interface Membership {
  id: string;
  userId: string;
  organizationId: string;
  role: 'owner' | 'admin' | 'member' | 'guest';
  customPermissions?: string[];
  createdAt: Date;
}

interface Product {
  id: string;
  name: string;         // "Pro Plan"
  slug: string;         // "pro"
  description?: string;
  features: string[];   // ["sso", "analytics"]
  metadata?: Record<string, any>;
}

interface Subscription {
  id: string;
  organizationId: string;
  productId: string;
  status: 'active' | 'trial' | 'cancelled' | 'expired';
  totalSeats: number;
  usedSeats: number;
  pricePerSeat?: number;
  billingCycle?: 'monthly' | 'yearly';
  startDate: Date;
  endDate?: Date;
}

interface Session {
  id: string;
  userId: string;
  token: string;
  expiresAt: Date;
  ipAddress?: string;
  userAgent?: string;
  createdAt: Date;
}

interface OAuthClient {
  id: string;
  organizationId: string;
  name: string;
  clientId: string;     // Public identifier
  clientSecret?: string; // For confidential clients
  redirectUris: string[];
  grantTypes: string[];
  scopes: string[];
  createdAt: Date;
}

SELECT o.*, m.role
FROM organizations o
JOIN memberships m ON m.organization_id = o.id
WHERE m.user_id = $userId;

SELECT u.*, m.role
FROM users u
JOIN memberships m ON m.user_id = u.id
WHERE m.organization_id = $orgId;

SELECT p.features
FROM subscriptions s
JOIN products p ON p.id = s.product_id
WHERE s.organization_id = $orgId
  AND s.status = 'active';

SELECT role FROM memberships
WHERE user_id = $userId
  AND organization_id = $orgId;