/home/techb158/cosmic-risk.abdallabala.com/docs
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;
```
