Database Schema
Cairn uses PostgreSQL as its primary database with TypeORM for entity management; the schema is owned by versioned migrations.
Entity Relationship Diagram
erDiagram
User ||--o{ Project : "owns"
User }o--|| Role : "roleId"
User ||--o{ Secret : "createdBy"
User ||--o{ SecretVersion : "createdBy"
User ||--o{ AuditLog : "actor"
Project ||--o{ Environment : "has"
Project ||--o{ Deployment : "has"
Project ||--|| ProjectConfig : "has"
Project ||--o{ Secret : "scopes"
Project ||--o{ DbConnection : "provisions"
Project ||--o{ ServiceRegistration : "registers"
Project ||--o{ SdkCredential : "issues"
Project ||--o{ File : "stores"
Environment ||--o{ Deployment : "target"
Environment ||--o{ Secret : "scopes"
Deployment ||--o{ ClickupTaskLink : "linked to"
Secret ||--o{ SecretVersion : "revision"
DbConnection ||--o{ DbBackup : "backup"
User {
uuid id PK
string name
string email UK
enum role "admin devops tech_lead developer viewer"
uuid roleId FK
string gitlabId
text avatarUrl
timestamp lastLogin
boolean isActive
}
Role {
uuid id PK
string name UK
text description
text_array permissions
boolean isSystem
boolean isActive
}
Project {
uuid id PK
string name
text description
string gitRepo
string gitProvider "github | gitlab"
string sdkToken
uuid ownerId FK
string argoCDAppName
timestamp createdAt
timestamp updatedAt
}
Environment {
uuid id PK
string name "dev | staging | production"
uuid projectId FK
boolean isProduction
string domain
}
Deployment {
uuid id PK
uuid projectId FK
uuid environmentId FK
string branch
string commitSha
enum status "pending building deploying active failed terminated"
string previewUrl
string containerImage
int replicas
timestamp createdAt
timestamp expiresAt
}
ProjectConfig {
uuid id PK
uuid projectId FK
jsonb config
timestamp createdAt
timestamp updatedAt
}
Secret {
uuid id PK
string key
text encryptedValue "iv:authTag:ciphertext"
uuid environmentId FK
uuid projectId FK
uuid createdBy FK
int version
timestamp createdAt
timestamp updatedAt
}
SecretVersion {
uuid id PK
uuid secretId FK "CASCADE"
int version
text encryptedValue
uuid createdBy FK
timestamp createdAt
}
DbConnection {
uuid id PK
uuid projectId FK
enum type "postgres | mongodb | redis"
string host
int port
string database
string username
text encryptedPassword
enum status "active failed provisioning"
}
DbBackup {
uuid id PK
uuid connectionId FK
string fileName
string s3Key
enum status
int size
}
ServiceRegistration {
uuid id PK
uuid projectId FK
string serviceName
int port
string status
}
SdkCredential {
uuid id PK
uuid projectId FK
string token "hashed"
string status
}
File {
uuid id PK
uuid projectId FK
string fileName
string s3Key
int size
string mimeType
}
AuditLog {
uuid id PK
uuid userId FK
string action
string targetType
uuid targetId
jsonb metadata
string ip
timestamp createdAt
}
ClickupTaskLink {
uuid id PK
uuid deploymentId FK
string taskId
string listId
string spaceId
}
Entity Definitions
User (users)
| Column | Type | Description |
|---|---|---|
| id | UUID (PK) | Primary key |
| name | VARCHAR(255) | Display name |
| VARCHAR(255) | Login email (unique) | |
| role | ENUM | Role preset: admin, devops, tech_lead, developer, viewer |
| roleId | UUID (FK→roles) | Optional custom role reference |
| gitlabId | VARCHAR(255) | GitLab account ID for OAuth |
| avatarUrl | TEXT | Profile picture URL |
| lastLogin | TIMESTAMP | Last successful login |
| isActive | BOOLEAN | Soft-delete flag |
Role (roles)
| Column | Type | Description |
|---|---|---|
| id | UUID (PK) | Primary key |
| name | VARCHAR(100) | Role name (unique) |
| description | TEXT | Human-readable description |
| permissions | TEXT[] | Array of permission strings |
| isSystem | BOOLEAN | System roles cannot be modified/deleted |
| isActive | BOOLEAN | Whether role is active |
Project (projects)
| Column | Type | Description |
|---|---|---|
| id | UUID (PK) | Primary key |
| name | VARCHAR(255) | Project name |
| description | TEXT | Project description |
| gitRepo | VARCHAR(500) | Git repository URL |
| gitProvider | VARCHAR(50) | github / gitlab |
| sdkToken | VARCHAR(255) | Auto-generated SDK token |
| ownerId | UUID (FK→users) | Project owner |
| argoCDAppName | VARCHAR(255) | ArgoCD application name |
| createdAt | TIMESTAMP | Creation timestamp |
| updatedAt | TIMESTAMP | Last update timestamp |
Environment (environments)
| Column | Type | Description |
|---|---|---|
| id | UUID (PK) | Primary key |
| name | VARCHAR(100) | dev / staging / production |
| projectId | UUID (FK→projects) | Parent project |
| isProduction | BOOLEAN | Production flag (affects protection rules) |
| domain | VARCHAR(500) | Custom domain for this environment |
Deployment (deployments)
| Column | Type | Description |
|---|---|---|
| id | UUID (PK) | Primary key |
| projectId | UUID (FK→projects) | Parent project |
| environmentId | UUID (FK→environments) | Target environment |
| branch | VARCHAR(255) | Git branch name |
| commitSha | VARCHAR(40) | Git commit hash |
| status | ENUM | pending / building / deploying / active / failed / terminated |
| previewUrl | VARCHAR(500) | Preview environment URL |
| containerImage | VARCHAR(500) | Docker image reference |
| replicas | INTEGER | Pod replica count |
| createdAt | TIMESTAMP | Creation timestamp |
| expiresAt | TIMESTAMP | Preview expiry (72h TTL) |
Secret (secrets)
| Column | Type | Description |
|---|---|---|
| id | UUID (PK) | Primary key |
| key | VARCHAR(255) | Secret key name |
| encryptedValue | TEXT | AES-256-GCM ciphertext (format: iv:authTag:ciphertext) |
| environmentId | UUID (FK→environments) | Environment scope (nullable = all) |
| projectId | UUID (FK→projects) | Parent project |
| createdBy | UUID (FK→users) | Creator |
| version | INTEGER | Current version number |
| createdAt | TIMESTAMP | Creation timestamp |
| updatedAt | TIMESTAMP | Last update timestamp |
SecretVersion (secret_versions)
| Column | Type | Description |
|---|---|---|
| id | UUID (PK) | Primary key |
| secretId | UUID (FK→secrets, CASCADE) | Parent secret |
| version | INTEGER | Version number |
| encryptedValue | TEXT | Encrypted value at this version |
| createdBy | UUID (FK→users) | Who created this version |
| createdAt | TIMESTAMP | Creation timestamp |
DbConnection (db_connections)
| Column | Type | Description |
|---|---|---|
| id | UUID (PK) | Primary key |
| projectId | UUID (FK→projects) | Parent project |
| type | ENUM | postgres / mongodb / redis |
| name | VARCHAR(255) | Connection name |
| host | VARCHAR(500) | Database host |
| port | INTEGER | Database port |
| database | VARCHAR(255) | Database name |
| username | VARCHAR(255) | Database user |
| encryptedPassword | TEXT | Encrypted password |
| status | ENUM | active / failed / provisioning |
AuditLog (audit_logs)
| Column | Type | Description |
|---|---|---|
| id | UUID (PK) | Primary key |
| userId | UUID (FK→users) | Actor |
| action | VARCHAR(100) | Action identifier (e.g., user.created, secret.read) |
| targetType | VARCHAR(50) | Entity type |
| targetId | UUID | Entity ID |
| metadata | JSONB | Additional context |
| ip | VARCHAR(45) | Client IP |
| createdAt | TIMESTAMP | Creation timestamp |
Other Entities
| Entity | Table | Key Columns |
|---|---|---|
| ServiceRegistration | service_registrations |
projectId, serviceName, port, status |
| File | files |
projectId, fileName, s3Key, size, mimeType |
| Alert | alerts |
projectId, metric, condition, threshold, enabled |
| DbBackup | db_backups |
connectionId, fileName, s3Key, status, size |
| SmtpConfig | smtp_configs |
host, port, user, encryptedPass, fromEmail |
| StorageProvider | storage_providers |
endpoint, bucket, accessKey, encryptedSecretKey |
| ClickupTaskLink | clickup_task_links |
deploymentId, taskId, listId, spaceId |
| SdkCredential | sdk_credentials |
projectId, token (hashed), status |
| BackgroundJob | background_jobs |
type, targetId, status, step, attempts, maxAttempts, lockedBy, lockedUntil |
| Operation | operations |
type, projectId, lockKey, lockFence, status, currentStep, inputHash, attempts |
| OperationStep | operation_steps |
operationId, index, name, status, attempts, output, error |
| OutboxMessage | outbox_messages |
operationId, kind, payload, status, attempts, availableAt |
| WebhookDelivery | webhook_deliveries |
provider, deliveryId (unique together), event |
| IdempotencyKey | idempotency_keys |
userId, route, requestHash, state, expiresAt |
MongoDB Collections
Cairn also uses MongoDB (via Mongoose) for high-volume, ephemeral data:
| Collection | Schema | Purpose |
|---|---|---|
| logs | Log | Application logs forwarded by SDKs |
| api_metrics | ApiMetric | Per-route latency records (p50/p95/p99) |
| bug_reports | BugReport | User-submitted bug reports with screenshots |
| error_docs | ErrorDoc | Aggregated error documentation |
| sdk_events | SdkEvent | SDK lifecycle events (register, heartbeat, etc.) |
| metrics_raw | MetricsRaw | Raw metric data points |
| feature_flags | FeatureFlag | Feature flag configurations |
Key Relationships
- User → Role: Many-to-One (via
roleId, optional) - Project → User: Many-to-One (via
ownerId) - Project → Environment: One-to-Many
- Project → Deployment: One-to-Many
- Environment → Deployment: One-to-Many
- Project → Secret: One-to-Many
- Secret → SecretVersion: One-to-Many (CASCADE delete)
- Project → DbConnection: One-to-Many
- Deployment → ClickupTaskLink: One-to-Many
Synchronization
The schema is managed by versioned TypeORM migrations in platform/api/src/migrations (synchronize is off everywhere). They are applied by node dist/migrate.js, which platformctl runs as the platform-api-migrate Job before each API rollout. See platform/api/README.md. Every new table or column ships as a new migration (for example BackgroundJobsAndOperations adds the background job, operation, outbox, webhook-delivery and idempotency tables); npm run migration:check fails CI on any drift.