# DB Schema

## Tables BetterAuth (gerees automatiquement)

| Table | Description |
|-------|-------------|
| `user` | Utilisateurs (id, name, email, role, stripeCustomerId) |
| `session` | Sessions actives |
| `account` | Comptes lies (magic-link, google, github) |
| `organization` | Organisations (name, slug) |
| `member` | Membres d'org (userId, organizationId, role: owner/admin/member) |
| `subscription` | Abonnements Stripe (plan, referenceId, status) |
| `invitation` | Invitations a rejoindre une org |
| `passkey` | Cles WebAuthn |
| `twoFactor` | Config 2FA par user |
| `verification` | Tokens de verification email |
| `jwks` | JSON Web Key Sets |
| `oauthClient` / `oauthAccessToken` / `oauthRefreshToken` / `oauthConsent` | OAuth Provider |
| `ssoProvider` | Config SSO/SAML |
| `scimProvider` | Config SCIM |
| `deviceCode` | Device Authorization flow |

## Tables custom CEOInsight

### `api_key`
```sql
api_key (
  id, user_id FK, organization_id FK,
  provider, label, encrypted_key, iv,
  last_used_at, created_at, updated_at
)
-- Index: user_id, organization_id
```

### `site`
```sql
site (
  id, user_id FK, organization_id FK,
  url, display_name, created_at
)
-- Index: user_id, organization_id
```

### `usage_log`
```sql
usage_log (
  id, user_id FK, date, provider, endpoint,
  count, created_at, updated_at
  UNIQUE(user_id, date, provider, endpoint)
)
-- Index: (user_id, date)
-- Increment: INSERT ... ON CONFLICT DO UPDATE SET count = count + 1
```

### `mcp_tool_config`
```sql
mcp_tool_config (
  id, user_id FK, organization_id FK,
  tool_name, enabled, config_json JSONB,
  created_at, updated_at
  UNIQUE(user_id, organization_id, tool_name)
)
```

## Relations

```
user ──< account
user ──< session
user ──< member >── organization
user ──< api_key >── organization
user ──< site >── organization
user ──< usage_log
user ──< mcp_tool_config >── organization
user ──< subscription (via referenceId)
organization ──< subscription (via referenceId, futur Enterprise)
```