FM / docs

/home/techb158/cosmic-risk.abdallabala.com/docs/

/home/techb158/cosmic-risk.abdallabala.com/docs

Name	Size	Mode	Actions
00-design-study.md	3678	0644	edit dl rm
01-uml-class-diagram.puml	11869	0644	edit dl rm
02-use-case-diagram.puml	3763	0644	edit dl rm
03-sequence-diagrams.puml	9732	0644	edit dl rm
04-database-entity-model.mmd	7051	0644	edit dl rm
05-database-schema.sql	13372	0644	edit dl rm
06-diagram-preview.html	5021	0644	edit dl rm
07-design-checklist.md	3256	0644	edit dl rm
08-step-2-storage-layer.md	5325	0644	edit dl rm
09-step-3-risk-crud-ui.md	3380	0644	edit dl rm
10-step-4-mitigation-workflow.md	3996	0644	edit dl rm
11-step-5-deployment-gate-workflow.md	2292	0644	edit dl rm
12-step-6-multi-pm-integration.md	3770	0644	edit dl rm
13-step-6-1-microsoft-planner-integration.md	2335	0644	edit dl rm
14-step-7-reporting-export.md	4158	0644	edit dl rm
15-step-7-1-oauth-live-connectors.md	4545	0644	edit dl rm
16-step-8-user-roles-access-control.md	3297	0644	edit dl rm
17-step-9-production-deployment-security.md	4228	0644	edit dl rm
18-step-10-final-academic-submission.md	3199	0644	edit dl rm
19-final-report-draft.md	6814	0644	edit dl rm
20-instructor-submission-checklist.md	3639	0644	edit dl rm
21-demo-script.md	3948	0644	edit dl rm
22-traceability-matrix.md	4847	0644	edit dl rm
23-testing-evidence.md	2961	0644	edit dl rm
24-evaluation-rubric-mapping.md	2910	0644	edit dl rm
25-final-deployment-runbook.md	3214	0644	edit dl rm
26-known-limitations-and-future-work.md	2632	0644	edit dl rm
27-final-qa-checklist.md	2893	0644	edit dl rm
28-demo-rehearsal-script.md	3618	0644	edit dl rm
29-submission-freeze-report.md	2769	0644	edit dl rm
30-final-known-issues.md	1876	0644	edit dl rm
31-saas-rebuild-implementation.md	2573	0644	edit dl rm
application-documentation.md	27515	0644	edit dl rm
conversation-log.md	18503	0644	edit dl rm
dashboard-spec.md	3691	0644	edit dl rm
database-guide.md	37826	0644	edit dl rm
development-summary.md	7070	0644	edit dl rm
github-architecture.svg	6288	0644	edit dl rm
integration-pull-push-plan.md	7384	0644	edit dl rm

Edit: /home/techb158/cosmic-risk.abdallabala.com/docs/database-guide.md (37826B)

# COSMIC AI-Risk Database Guide

## Overview

PostgreSQL 16 database powering the COSMIC AI-Risk SaaS platform. Managed via [Prisma ORM](https://www.prisma.io/).

- **Database**: `cosmic_saas`
- **User**: `cosmic`
- **Password**: `cosmic`
- **Host (local)**: `localhost:5432` (exposed from Docker)
- **Host (Docker internal)**: `postgres:5432`
- **Tables**: 24
- **Enums**: 11
- **Tool**: pgAdmin at http://127.0.0.1:5050 (admin@cosmic.com / cosmic123)

---

## How to Access

### pgAdmin (Web UI) — Recommended

1. Open http://127.0.0.1:5050
2. Login: `admin@cosmic.com` / `cosmic123`
3. Right-click **Servers** → **Register** → **Server**
   - **Name**: `COSMIC DB`
   - **Host**: `postgres` (Docker internal hostname)
   - **Port**: `5432`
   - **Database**: `cosmic_saas`
   - **User**: `cosmic`
   - **Password**: `cosmic`
   - Check **Save password**

### psql (Command Line)

```powershell
docker exec -it saas-app-postgres-1 psql -U cosmic -d cosmic_saas
```

### DBeaver / Other GUI

| Field     | Value              |
|-----------|---------------------|
| Host      | `localhost`         |
| Port      | `5432`              |
| Database  | `cosmic_saas`       |
| User      | `cosmic`            |
| Password  | `cosmic`            |

---

## Entity-Relationship Diagram

```
Organization
 ├── Workspace
 │    ├── Project
 │    │    ├── Risk ──┬── Mitigation
 │    │    │           └── Evidence
 │    │    ├── LifecyclePhase
 │    │    ├── Indicator
 │    │    ├── Experiment ──┬── ModelMetric
 │    │    ├── GateEvaluation ──┬── GateCriterion
 │    │    │                    └── GateDecision
 │    │    ├── AuditEvent
 │    │    └── ReportExport
 │    ├── Integration
 │    │    ├── ExternalWorkItemMapping
 │    │    └── IntegrationSyncRun
 │    └── ReportExport
 ├── Membership ──── User
 ├── Subscription
 ├── Invitation
 ├── AuditEvent
 ├── UsageEvent
 └── OAuthToken

Session ──── User
Role ──── Membership
```

---

## Enums

### OrganizationStatus
| Value       | Description                        |
|-------------|------------------------------------|
| `TRIAL`     | Trial period, not yet subscribed   |
| `ACTIVE`    | Active paid subscription           |
| `SUSPENDED` | Access suspended                   |
| `CANCELED`  | Account closed                     |

### WorkspaceType
| Value               | Description             |
|----------------------|------------------------|
| `B2B_TEAM`          | Business-to-business    |
| `CONSULTANT_CLIENT` | Consultant-client setup |
| `ENTERPRISE`         | Enterprise org         |

### MembershipStatus
| Value      | Description          |
|------------|----------------------|
| `INVITED`  | Invite sent, pending |
| `ACTIVE`   | Active member        |
| `DISABLED` | Access revoked       |

### RiskStatus
| Value           | Description                      |
|-----------------|----------------------------------|
| `OPEN`          | Identified, not yet addressed    |
| `IN_MITIGATION` | Mitigation actions underway      |
| `ACCEPTED`      | Risk accepted without mitigation |
| `CLOSED`        | Fully mitigated or obsolete      |

### ApprovalStatus
| Value      | Description          |
|------------|----------------------|
| `PENDING`  | Awaiting review      |
| `APPROVED` | Approved by reviewer |
| `REJECTED` | Rejected by reviewer |
| `ACCEPTED` | Accepted as-is       |

### MitigationStatus
| Value         | Description           |
|---------------|-----------------------|
| `NOT_STARTED` | Not yet begun         |
| `IN_PROGRESS` | Work in progress      |
| `DONE`        | Completed             |
| `REJECTED`    | Mitigation rejected   |

### GateStatus
| Value     | Description                   |
|-----------|-------------------------------|
| `READY`   | All criteria met              |
| `WARNING` | Non-blocking criteria failing |
| `BLOCKED` | Blocking criteria failing     |

### GateDecisionValue
| Value           | Description                    |
|-----------------|--------------------------------|
| `APPROVED`      | Gate passed                    |
| `REJECTED`      | Gate failed                    |
| `ACCEPTED`      | Accepted with conditions       |
| `NEEDS_CHANGES` | Changes required for approval  |

### IntegrationProvider
| Value               | Description     |
|---------------------|-----------------|
| `TRELLO`            | Trello boards   |
| `JIRA`              | Jira Cloud      |
| `ASANA`             | Asana projects  |
| `MICROSOFT_PLANNER` | Microsoft Plan. |

### IntegrationStatus
| Value                  | Description               |
|------------------------|---------------------------|
| `CONNECTED`            | Working connection        |
| `NEEDS_CONFIGURATION`  | Setup incomplete          |
| `DISABLED`             | Disconnected by user      |

### SubscriptionSource / SubscriptionStatus
**Source**: `MANUAL`, `STRIPE`
**Status**: `TRIALING`, `ACTIVE`, `PAST_DUE`, `CANCELED`, `SUSPENDED`

---

## Models Reference

### Organization

**Table**: `Organization` — Top-level tenant. Every user belongs to at least one organization.

| Field              | Type              | Default    | Notes                       |
|--------------------|-------------------|------------|-----------------------------|
| `id`               | String (CUID)     | auto       | Primary key                 |
| `name`             | String            | required   | Display name                |
| `slug`             | String            | required   | URL-friendly (unique)       |
| `status`           | OrganizationStatus| `TRIAL`    | Current status              |
| `planCode`         | String            | `pilot`    | Plan identifier             |
| `manualBilling`    | Boolean           | `true`     | Manual billing mode         |
| `stripeCustomerId` | String?           | null       | Stripe reference            |
| `ssoEnforced`      | Boolean           | `false`    | Force SSO login             |
| `allowedDomains`   | String[]          | `[]`       | SSO-allowed email domains   |
| `dataRetentionDays`| Int?              | null       | Retention period            |
| `createdAt`        | DateTime          | auto       |                             |
| `updatedAt`        | DateTime          | auto       |                             |

**Relations**: `workspaces`, `memberships`, `subscriptions`, `auditEvents`, `usageEvents`, `invitations`, `oauthTokens`

---

### Workspace

**Table**: `Workspace` — Grouping within an org (by department or client).

| Field            | Type              | Default       | Notes                        |
|------------------|-------------------|---------------|------------------------------|
| `id`             | String (CUID)     | auto          | Primary key                  |
| `organizationId` | String            | required      | FK → Organization            |
| `name`           | String            | required      | Display name                 |
| `slug`           | String            | required      | Unique within organization   |
| `type`           | WorkspaceType     | `B2B_TEAM`    | Classification               |
| `status`         | OrganizationStatus| `TRIAL`       | Status                       |
| `createdAt`      | DateTime          | auto          |                              |
| `updatedAt`      | DateTime          | auto          |                              |

**Unique**: `[organizationId, slug]`
**Relations**: `organization`, `projects`, `integrations`, `reports`, `auditEvents`

---

### User

**Table**: `User` — Platform login accounts.

| Field            | Type              | Notes                           |
|------------------|-------------------|---------------------------------|
| `id`             | String (CUID)     | Primary key                     |
| `email`          | String            | Unique, login identifier        |
| `displayName`    | String            | Display name                    |
| `passwordHash`   | String?           | bcrypt hash (null for SSO-only) |
| `emailVerifiedAt`| DateTime?         | When email was verified         |
| `lastLoginAt`    | DateTime?         | Last successful login           |
| `createdAt`      | DateTime          |                                 |
| `updatedAt`      | DateTime          |                                 |

**Relations**: `memberships`, `invitationsSent`, `auditEvents`, `sessions`

---

### Session

**Table**: `Session` — Auth sessions linked to users.

| Field      | Type              | Notes                    |
|------------|-------------------|--------------------------|
| `id`       | String (CUID)     | Primary key              |
| `userId`   | String            | FK → User (Cascade)      |
| `token`    | String            | Unique session token     |
| `expiresAt`| DateTime          | Session expiry           |
| `createdAt`| DateTime          |                          |

---

### Role

**Table**: `Role` — RBAC roles with permission arrays.

| Field       | Type              | Notes                    |
|-------------|-------------------|--------------------------|
| `id`        | String (CUID)     | Primary key              |
| `code`      | String            | Unique code (e.g. owner) |
| `name`      | String            | Display name             |
| `permissions`| String[]         | Permission strings       |
| `system`    | Boolean           | `true` (system-managed)  |
| `createdAt` | DateTime          |                          |
| `updatedAt` | DateTime          |                          |

**Seeded roles**: `owner` (wildcard `*`), `admin`, `project_manager`, `risk_owner`, `governance_reviewer`, `legal_ethics_reviewer`, `integration_admin`, `viewer`, `consultant`

---

### Membership

**Table**: `Membership` — Links a user to an organization with a role.

| Field            | Type              | Default       | Notes                  |
|------------------|-------------------|---------------|------------------------|
| `id`             | String (CUID)     | auto          | Primary key            |
| `organizationId` | String            | required      | FK → Organization      |
| `userId`         | String            | required      | FK → User              |
| `roleId`         | String            | required      | FK → Role              |
| `status`         | MembershipStatus  | `INVITED`     | Membership state       |
| `workspaceScope` | String[]          | `[]`          | Restricted workspaces  |
| `createdAt`      | DateTime          |               |                        |
| `updatedAt`      | DateTime          |               |                        |

**Unique**: `[organizationId, userId]`

---

### Invitation

**Table**: `Invitation` — Org invite sent to an email.

| Field          | Type              | Notes                   |
|----------------|-------------------|-------------------------|
| `id`           | String (CUID)     | Primary key             |
| `organizationId`| String           | FK → Organization       |
| `email`        | String            | Invited email           |
| `roleCode`     | String            | Role to assign          |
| `tokenHash`    | String            | Unique invite token     |
| `expiresAt`    | DateTime          | Invite expiry           |
| `acceptedAt`   | DateTime?         | When accepted           |
| `invitedById`  | String?           | FK → User (sender)      |
| `createdAt`    | DateTime          |                         |

---

### Subscription

**Table**: `Subscription` — Billing subscription for an organization.

| Field               | Type              | Default      | Notes                  |
|---------------------|-------------------|--------------|------------------------|
| `id`                | String (CUID)     | auto         | Primary key            |
| `organizationId`    | String            | required     | FK → Organization      |
| `source`            | SubscriptionSource| `MANUAL`     | Stripe or manual       |
| `status`            | SubscriptionStatus| `TRIALING`   | Current status         |
| `planCode`          | String            | `pilot`      | Plan identifier        |
| `stripeSubscriptionId`| String?         | null         | Stripe reference       |
| `currentPeriodEnd`  | DateTime?         | null         | Billing period end     |
| `userLimit`         | Int               | `5`          | Max users              |
| `projectLimit`      | Int               | `3`          | Max projects           |
| `reportLimitMonthly`| Int               | `25`         | Monthly report cap     |
| `integrationLimit`  | Int               | `2`          | Max integrations       |
| `storageMbLimit`    | Int               | `1024`       | Storage limit (MB)     |
| `adminOverride`     | Boolean           | `false`      | Override limits        |
| `createdAt`         | DateTime          |              |                        |
| `updatedAt`         | DateTime          |              |                        |

---

### Project

**Table**: `Project` — The core entity. Each project contains risks, gates, lifecycle phases, etc.

| Field                 | Type              | Default       | Notes                     |
|-----------------------|-------------------|---------------|---------------------------|
| `id`                  | String (CUID)     | auto          | Primary key               |
| `workspaceId`         | String            | required      | FK → Workspace            |
| `name`                | String            | required      | Project name              |
| `subtitle`            | String?           | null          | Short description         |
| `projectType`         | String            | `AI-Enabler`  | Type classification       |
| `currentLifecyclePhase`| String?          | null          | Current phase name        |
| `riskAppetite`        | Int               | `50`          | 0-100 scale               |
| `assessmentDate`      | DateTime?         | null          | Last assessment           |
| `status`              | String            | `Active`      | Active / Archived / On Hold |
| `ownerDisplayName`    | String?           | null          | Project owner             |
| `description`         | String?           | null          | Detailed description       |
| `thresholds`          | Json              | `{}`          | Risk threshold config     |
| `reviews`             | Json              | `{}`          | Review schedule/notes     |
| `createdAt`           | DateTime          |               |                           |
| `updatedAt`           | DateTime          |               |                           |

**Relations**: `workspace`, `lifecyclePhases`, `risks`, `indicators`, `experiments`, `gateEvaluations`, `auditEvents`, `reports`

---

### LifecyclePhase

**Table**: `LifecyclePhase` — Stages within a project's lifecycle (Design, Development, Testing, Deployment).

| Field            | Type              | Notes                       |
|------------------|-------------------|-----------------------------|
| `id`             | String (CUID)     | Primary key                 |
| `projectId`      | String            | FK → Project (Cascade)      |
| `name`           | String            | Phase name                  |
| `status`         | String            | e.g. "In Progress", "Done"  |
| `readinessScore` | Int               | 0-100 readiness score       |
| `sequence`       | Int               | Order in lifecycle          |
| `createdAt`      | DateTime          |                             |
| `updatedAt`      | DateTime          |                             |

---

### Risk

**Table**: `Risk` — Identified AI risks for a project.

| Field              | Type              | Default    | Notes                    |
|--------------------|-------------------|------------|--------------------------|
| `id`               | String (CUID)     | auto       | Primary key              |
| `projectId`        | String            | required   | FK → Project (Cascade)   |
| `ownerDisplayName` | String?           | null       | Risk owner               |
| `title`            | String            | required   | Risk title                |
| `description`      | String?           | null       | Detailed description      |
| `dimension`        | String            | required   | AI risk dimension         |
| `domain`           | String            | required   | Domain classification     |
| `lifecyclePhase`   | String            | required   | Where it was identified   |
| `probability`      | Int               | required   | 1-5 scale                 |
| `impact`           | Int               | required   | 1-5 scale                 |
| `detectability`    | Int               | required   | 1-5 scale                 |
| `status`           | RiskStatus        | `OPEN`     | Current state             |
| `approvalStatus`   | ApprovalStatus    | `PENDING`  | Review state              |
| `dueDate`          | DateTime?         | null       | Target resolution date    |
| `evidenceSummary`  | String?           | null       | Supporting evidence       |
| `createdAt`        | DateTime          |            |                          |
| `updatedAt`        | DateTime          |            |                          |

**Relations**: `project`, `mitigations`, `evidence`

---

### Mitigation

**Table**: `Mitigation` — Actions taken to address a risk.

| Field                | Type              | Default        | Notes                   |
|----------------------|-------------------|----------------|-------------------------|
| `id`                 | String (CUID)     | auto           | Primary key             |
| `riskId`             | String            | required       | FK → Risk (Cascade)     |
| `ownerDisplayName`   | String?           | null           | Mitigation owner        |
| `title`              | String            | required       | Action title            |
| `description`        | String?           | null           | Detailed plan           |
| `status`             | MitigationStatus  | `NOT_STARTED`  | Current state           |
| `progressPercent`    | Int               | `0`            | 0-100% progress         |
| `effectivenessPercent`| Int              | `0`            | 0-100% effectiveness    |
| `dueDate`            | DateTime?         | null           | Target completion date  |
| `sequence`           | Int               | `1`            | Ordering                |
| `createdAt`          | DateTime          |                |                         |
| `updatedAt`          | DateTime          |                |                         |

---

### Evidence

**Table**: `Evidence` — Supporting evidence attached to risks or mitigations.

| Field           | Type              | Default    | Notes                     |
|-----------------|-------------------|------------|---------------------------|
| `id`            | String (CUID)     | auto       | Primary key               |
| `projectId`     | String            | required   | FK → Project              |
| `riskId`        | String?           | null       | FK → Risk (Cascade)       |
| `mitigationId`  | String?           | null       | FK → Mitigation (Cascade) |
| `type`          | String            | `Review`   | Evidence type             |
| `title`         | String            | required   | Evidence title            |
| `uri`           | String?           | null       | Link to file              |
| `createdById`   | String?           | null       | Uploader user ID          |
| `createdAt`     | DateTime          |            |                           |
| `updatedAt`     | DateTime          |            |                           |

---

### Indicator

**Table**: `Indicator` — Metrics to monitor AI system health.

| Field               | Type              | Notes                        |
|---------------------|-------------------|------------------------------|
| `id`                | String (CUID)     | Primary key                  |
| `projectId`         | String            | FK → Project (Cascade)       |
| `name`              | String            | Indicator name               |
| `dimension`         | String            | AI dimension                 |
| `measurand`         | String            | What is measured             |
| `unit`              | String            | Unit of measurement          |
| `target`            | String?           | Target value                 |
| `interpretationRule`| String?           | How to interpret results     |
| `createdAt`         | DateTime          |                              |
| `updatedAt`         | DateTime          |                              |

---

### Experiment

**Table**: `Experiment` — Model experiments within a project.

| Field       | Type              | Default | Notes                      |
|-------------|-------------------|---------|----------------------------|
| `id`        | String (CUID)     | auto    | Primary key                |
| `projectId` | String            | required| FK → Project (Cascade)     |
| `name`      | String            | required| Experiment name            |
| `modelName` | String?           | null    | Model identifier           |
| `selected`  | Boolean           | `false` | Is this the chosen model?  |
| `createdAt` | DateTime          |         |                            |
| `updatedAt` | DateTime          |         |                            |

---

### ModelMetric

**Table**: `ModelMetric` — Performance metrics for experiments.

| Field            | Type              | Notes                        |
|------------------|-------------------|------------------------------|
| `id`             | String (CUID)     | Primary key                  |
| `experimentId`   | String            | FK → Experiment (Cascade)    |
| `metricName`     | String            | e.g. accuracy, precision     |
| `metricValue`    | Float             | Numeric result               |
| `thresholdValue` | Float?            | Pass/fail threshold          |
| `status`         | String            | Pass / Fail / Warning        |
| `measuredAt`     | DateTime?         | When measured                |
| `createdAt`      | DateTime          |                              |
| `updatedAt`      | DateTime          |                              |

---

### GateEvaluation

**Table**: `GateEvaluation` — AI governance gate reviews for a project.

| Field               | Type              | Default            | Notes                    |
|---------------------|-------------------|--------------------|--------------------------|
| `id`                | String (CUID)     | auto               | Primary key              |
| `projectId`         | String            | required           | FK → Project (Cascade)   |
| `status`            | GateStatus        | required           | READY / WARNING / BLOCKED|
| `reviewStatus`      | String            | `Pending review`   | Current review stage     |
| `summary`           | String            | required           | Evaluation summary       |
| `evaluatedByUserId` | String?           | null               | Evaluator                |
| `evaluatedAt`       | DateTime          | required           | When evaluated           |
| `reviewedAt`        | DateTime?         | null               | When reviewed            |
| `createdAt`         | DateTime          |                    |                          |
| `updatedAt`         | DateTime          |                    |                          |

---

### GateCriterion

**Table**: `GateCriterion` — Individual criteria within a gate evaluation.

| Field            | Type              | Default | Notes                       |
|------------------|-------------------|---------|-----------------------------|
| `id`             | String (CUID)     | auto    | Primary key                 |
| `gateId`         | String            | required| FK → GateEvaluation (Cascade)|
| `name`           | String            | required| Criterion name              |
| `actualValue`    | String            | required| Measured value              |
| `expectedRule`   | String            | required| Expected threshold          |
| `status`         | String            | required| Pass / Fail / Warning       |
| `blocking`       | Boolean           | `false` | Blocks gate if failing      |
| `evidenceTitle`  | String?           | null    | Supporting evidence         |
| `evidenceUri`    | String?           | null    | Link to evidence            |
| `reviewerStatus` | String            | `Open`  | Review state                |
| `reviewerNote`   | String?           | null    | Reviewer comment            |
| `createdAt`      | DateTime          |         |                             |
| `updatedAt`      | DateTime          |         |                             |

---

### GateDecision

**Table**: `GateDecision` — Final decision on a gate evaluation.

| Field             | Type              | Notes                        |
|-------------------|-------------------|------------------------------|
| `id`              | String (CUID)     | Primary key                  |
| `gateId`          | String            | FK → GateEvaluation (Cascade)|
| `decision`        | GateDecisionValue | APPROVED / REJECTED / etc.   |
| `reviewerName`    | String            | Decision maker name          |
| `reason`          | String?           | Justification                |
| `createdByUserId` | String?           | User ID who decided          |
| `decidedAt`       | DateTime          | When decision was made       |
| `createdAt`       | DateTime          |                              |

---

### Integration

**Table**: `Integration` — External project management tool connections.

| Field              | Type              | Default                | Notes                   |
|--------------------|-------------------|------------------------|-------------------------|
| `id`               | String (CUID)     | auto                   | Primary key             |
| `workspaceId`      | String            | required               | FK → Workspace          |
| `provider`         | IntegrationProvider| required               | Trello / Jira / etc.    |
| `workspaceName`    | String            | required               | External workspace name |
| `externalProjectKey`| String?          | null                   | External project ref    |
| `baseUrl`          | String?           | null                   | API base URL            |
| `authMode`         | String            | required               | API key / OAuth / basic |
| `connectionStatus` | IntegrationStatus | `NEEDS_CONFIGURATION`  | Connection state        |
| `syncDirection`    | String            | `COSMIC to PM`         | Data flow direction     |
| `liveEnabled`      | Boolean           | `false`                | Live sync enabled       |
| `liveConfig`       | Json              | `{}`                   | Live sync configuration |
| `lastSyncAt`       | DateTime?         | null                   | Last successful sync    |
| `createdAt`        | DateTime          |                        |                         |
| `updatedAt`        | DateTime          |                        |                         |

**Unique**: `[workspaceId, provider]`

---

### ExternalWorkItemMapping

**Table**: `ExternalWorkItemMapping` — Maps COSMIC entities to external tool items.

| Field             | Type              | Default | Notes                          |
|-------------------|-------------------|---------|--------------------------------|
| `id`              | String (CUID)     | auto    | Primary key                    |
| `integrationId`   | String            | required| FK → Integration (Cascade)     |
| `projectId`       | String            | required| FK → Project                   |
| `localEntityType` | String            | required| Risk / Mitigation / etc.       |
| `localEntityId`   | String            | required| COSMIC entity ID               |
| `localTitle`      | String            | required| Entity title                   |
| `externalItemType`| String            | required| External item type             |
| `externalItemId`  | String            | required| External item ID               |
| `externalItemKey` | String            | required| External item key              |
| `externalUrl`     | String?           | null    | Link to external item          |
| `externalStatus`  | String            | required| Status in external tool        |
| `syncStatus`      | String            | required| Sync state                     |
| `fieldMapping`    | Json              | `{}`    | Custom field mappings          |
| `lastSyncedAt`    | DateTime?         | null    | Last sync timestamp            |
| `createdAt`       | DateTime          |         |                                |
| `updatedAt`       | DateTime          |         |                                |

---

### IntegrationSyncRun

**Table**: `IntegrationSyncRun` — History of sync operations.

| Field           | Type              | Default | Notes                         |
|-----------------|-------------------|---------|-------------------------------|
| `id`            | String (CUID)     | auto    | Primary key                   |
| `integrationId` | String            | required| FK → Integration (Cascade)    |
| `projectId`     | String            | required| Project being synced          |
| `provider`      | IntegrationProvider| required| Target provider               |
| `status`        | String            | required| Running / Success / Failed    |
| `startedAt`     | DateTime          |         | Start time                    |
| `finishedAt`    | DateTime?         | null    | End time                      |
| `createdCount`  | Int               | `0`     | Items created                 |
| `updatedCount`  | Int               | `0`     | Items updated                 |
| `failedCount`   | Int               | `0`     | Items failed                  |
| `summary`       | String?           | null    | Human-readable summary        |
| `failureLog`    | Json              | `[]`    | Error details                 |
| `createdAt`     | DateTime          |         |                               |

---

### OAuthToken

**Table**: `OAuthToken` — Encrypted OAuth credentials for integrations.

| Field            | Type              | Notes                          |
|------------------|-------------------|--------------------------------|
| `id`             | String (CUID)     | Primary key                    |
| `organizationId` | String            | FK → Organization (Cascade)    |
| `provider`       | IntegrationProvider| Provider type                 |
| `integrationId`  | String?           | Optional FK → Integration      |
| `encryptedToken` | Json              | Encrypted token data           |
| `redactedToken`  | String            | Masked preview (e.g. `abc***`) |
| `expiresAt`      | DateTime?         | Token expiry                   |
| `createdAt`      | DateTime          |                                |
| `updatedAt`      | DateTime          |                                |

---

### ReportExport

**Table**: `ReportExport` — Generated report metadata.

| Field        | Type              | Default     | Notes                    |
|--------------|-------------------|-------------|--------------------------|
| `id`         | String (CUID)     | auto        | Primary key              |
| `workspaceId`| String            | required    | FK → Workspace           |
| `projectId`  | String            | required    | FK → Project             |
| `reportType` | String            | required    | PDF / CSV / etc.         |
| `format`     | String            | required    | File format              |
| `status`     | String            | `Generated` | Generation state         |
| `metadata`   | Json              | `{}`        | Custom metadata          |
| `createdById`| String?           | null        | User who requested       |
| `createdAt`  | DateTime          |             |                          |

---

### AuditEvent

**Table**: `AuditEvent` — Immutable activity log for all entity changes.

| Field            | Type              | Default | Notes                        |
|------------------|-------------------|---------|------------------------------|
| `id`             | String (CUID)     | auto    | Primary key                  |
| `organizationId` | String            | required| FK → Organization            |
| `workspaceId`    | String?           | null    | FK → Workspace (SetNull)     |
| `projectId`      | String?           | null    | FK → Project (SetNull)       |
| `actorUserId`    | String?           | null    | FK → User (SetNull)          |
| `entityType`     | String            | required| e.g. Risk, Project           |
| `entityId`       | String            | required| Affected entity ID           |
| `action`         | String            | required| CREATE / UPDATE / DELETE      |
| `beforeJson`     | Json?             | null    | Snapshot before change       |
| `afterJson`      | Json?             | null    | Snapshot after change        |
| `ipAddress`      | String?           | null    | Request origin               |
| `userAgent`      | String?           | null    | Client info                  |
| `createdAt`      | DateTime          |         |                              |

---

### UsageEvent

**Table**: `UsageEvent` — Metering/usage tracking for billing.

| Field            | Type              | Default | Notes                    |
|------------------|-------------------|---------|--------------------------|
| `id`             | String (CUID)     | auto    | Primary key              |
| `organizationId` | String            | required| FK → Organization         |
| `metric`         | String            | required| Usage metric name        |
| `quantity`       | Int               | `1`     | Units consumed           |
| `metadata`       | Json              | `{}`    | Additional context       |
| `createdAt`      | DateTime          |         |                          |

---

## Seed Data

The seed script (`prisma/seed.js`) creates the following when you run `npx prisma db push && node prisma/seed.js`:

### Roles (9)
`owner`, `admin`, `project_manager`, `risk_owner`, `governance_reviewer`, `legal_ethics_reviewer`, `integration_admin`, `viewer`, `consultant`

### Users (5)

| Email                  | Password    | Role            | Risks | Org Name                  |
|------------------------|-------------|-----------------|-------|---------------------------|
| `owner@cosmic.local`   | `cosmic123` | owner           | 5     | COSMIC Demo Organization  |
| `admin@cosmic.local`   | `admin123`  | admin           | 4     | Admin Org                 |
| `pm@cosmic.local`      | `pm123`     | project_manager | 4     | PM Org                    |
| `risk-owner@cosmic.local`| `risk123` | risk_owner      | 4     | Risk Owner Org            |
| `viewer@cosmic.local`  | `view123`   | viewer          | 4     | Viewer Org                |

Each user gets:
- Their own organization, workspace, and project
- 4-5 risks with associated mitigations
- Lifecycle phases (Design/Development/Testing/Deployment)
- A gate evaluation with criteria
- 3 indicators (Fairness Score, Model Accuracy, Response Latency)
- A Trello integration (NEEDS_CONFIGURATION)

### Owner Demo Project (specific)

- **5 risks**: LLM Hallucination, Insufficient Human Oversight, Bias in Training Data, Data Privacy Violation, Model Drift
- **Mitigations** for each risk (some IN_PROGRESS, some DONE)
- **Gate evaluation**: WARNING status, 4 criteria (2 pass, 2 fail — one blocking)
- **Lifecycle phases**: Design (95), Development (80), Testing (55/IP), Deployment (15/Pending)

---

## Common Queries

### List all organizations
```sql
SELECT * FROM "Organization";
```

### Find all projects for a user
```sql
SELECT p.* FROM "Project" p
JOIN "Workspace" w ON w.id = p."workspaceId"
JOIN "Membership" m ON m."organizationId" = w."organizationId"
WHERE m."userId" = 'USER_ID_HERE';
```

### All open risks with mitigations count
```sql
SELECT r.title, r.status, r.probability, r.impact,
       (SELECT COUNT(*) FROM "Mitigation" m WHERE m."riskId" = r.id) as mitigation_count
FROM "Risk" r
WHERE r.status = 'OPEN'
ORDER BY r.probability * r.impact DESC;
```

### Gate evaluations with decisions
```sql
SELECT ge.summary, ge.status, gd.decision, gd.reviewer_name, gd.reason
FROM "GateEvaluation" ge
LEFT JOIN "GateDecision" gd ON gd."gateId" = ge.id
ORDER BY ge."evaluatedAt" DESC;
```

### Recent audit activity
```sql
SELECT ae.action, ae."entityType", ae."entityId",
       u.email as actor, ae."createdAt"
FROM "AuditEvent" ae
LEFT JOIN "User" u ON u.id = ae."actorUserId"
WHERE ae."organizationId" = 'ORG_ID_HERE'
ORDER BY ae."createdAt" DESC
LIMIT 50;
```

### Count risks by dimension
```sql
SELECT dimension, COUNT(*) as count
FROM "Risk"
GROUP BY dimension
ORDER BY count DESC;
```

### Blocked gates
```sql
SELECT p.name as project, ge.summary, ge.status
FROM "GateEvaluation" ge
JOIN "Project" p ON p.id = ge."projectId"
WHERE ge.status = 'BLOCKED';
```

### Users by role
```sql
SELECT r.name as role, u.email, u."displayName"
FROM "Membership" m
JOIN "User" u ON u.id = m."userId"
JOIN "Role" r ON r.id = m."roleId"
WHERE m.status = 'ACTIVE'
ORDER BY r.name;
```

cmd: