Data Model

OpenPrime uses PostgreSQL with Sequelize ORM for data persistence.

Entity Relationship Diagram

┌─────────────────────┐       ┌──────────────────────────┐
│       Users         │       │      Environments        │
├─────────────────────┤       ├──────────────────────────┤
│ id (PK)             │───┐   │ id (PK)                  │
│ keycloak_id (UQ)    │   │   │ name                     │
│ username            │   │   │ provider                 │
│ email               │   │   │ region                   │
│ created_at          │   └──▶│ user_id (FK)             │
│ updated_at          │       │ configuration (JSONB)    │
└─────────────────────┘       │ services (JSONB)         │
         │                    │ helm_charts (JSONB)      │
         │                    │ status                   │
         │                    │ created_at               │
         │                    │ updated_at               │
         │                    └──────────────────────────┘
         │
         │                    ┌──────────────────────────┐
         │                    │    CloudCredentials      │
         │                    ├──────────────────────────┤
         │                    │ id (PK)                  │
         └───────────────────▶│ user_id (FK)             │
                              │ provider                 │
                              │ name                     │
                              │ credentials (encrypted)  │
                              │ created_at               │
                              │ updated_at               │
                              └──────────────────────────┘

Models

User

const User = sequelize.define('User', {
  id: {
    type: DataTypes.UUID,
    defaultValue: DataTypes.UUIDV4,
    primaryKey: true
  },
  keycloakId: {
    type: DataTypes.STRING,
    unique: true,
    allowNull: false,
    field: 'keycloak_id'
  },
  username: {
    type: DataTypes.STRING,
    allowNull: false
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      isEmail: true
    }
  }
}, {
  tableName: 'users',
  underscored: true,
  timestamps: true
});

Environment

const Environment = sequelize.define('Environment', {
  id: {
    type: DataTypes.UUID,
    defaultValue: DataTypes.UUIDV4,
    primaryKey: true
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      is: /^[a-z][a-z0-9-]*$/
    }
  },
  provider: {
    type: DataTypes.ENUM('aws', 'azure', 'gcp', 'onpremise'),
    allowNull: false
  },
  region: {
    type: DataTypes.STRING,
    allowNull: false
  },
  userId: {
    type: DataTypes.UUID,
    allowNull: false,
    references: {
      model: 'users',
      key: 'id'
    },
    field: 'user_id'
  },
  configuration: {
    type: DataTypes.JSONB,
    defaultValue: {}
  },
  services: {
    type: DataTypes.JSONB,
    defaultValue: {}
  },
  helmCharts: {
    type: DataTypes.JSONB,
    defaultValue: {},
    field: 'helm_charts'
  },
  status: {
    type: DataTypes.ENUM('draft', 'active', 'deployed', 'archived'),
    defaultValue: 'draft'
  }
}, {
  tableName: 'environments',
  underscored: true,
  timestamps: true
});

CloudCredential

const CloudCredential = sequelize.define('CloudCredential', {
  id: {
    type: DataTypes.UUID,
    defaultValue: DataTypes.UUIDV4,
    primaryKey: true
  },
  userId: {
    type: DataTypes.UUID,
    allowNull: false,
    references: {
      model: 'users',
      key: 'id'
    },
    field: 'user_id'
  },
  provider: {
    type: DataTypes.ENUM('aws', 'azure', 'gcp'),
    allowNull: false
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
  credentials: {
    type: DataTypes.TEXT,
    allowNull: false,
    // Stored encrypted
    get() {
      const value = this.getDataValue('credentials');
      return decrypt(value);
    },
    set(value) {
      this.setDataValue('credentials', encrypt(value));
    }
  }
}, {
  tableName: 'cloud_credentials',
  underscored: true,
  timestamps: true
});

Associations

// User has many Environments
User.hasMany(Environment, {
  foreignKey: 'userId',
  as: 'environments'
});

Environment.belongsTo(User, {
  foreignKey: 'userId',
  as: 'user'
});

// User has many CloudCredentials
User.hasMany(CloudCredential, {
  foreignKey: 'userId',
  as: 'credentials'
});

CloudCredential.belongsTo(User, {
  foreignKey: 'userId',
  as: 'user'
});

JSONB Structures

Environment Configuration

{
  "tags": {
    "Environment": "production",
    "Team": "platform"
  },
  "networking": {
    "vpcCidr": "10.0.0.0/16",
    "publicSubnets": ["10.0.1.0/24", "10.0.2.0/24"],
    "privateSubnets": ["10.0.10.0/24", "10.0.11.0/24"]
  }
}

Services Structure

{
  "kubernetes": {
    "enabled": true,
    "clusterName": "my-cluster",
    "version": "1.28",
    "nodeGroups": [
      {
        "name": "general",
        "instanceType": "t3.medium",
        "desiredSize": 3,
        "minSize": 1,
        "maxSize": 10
      }
    ]
  },
  "database": {
    "enabled": true,
    "engine": "postgresql",
    "version": "15",
    "instanceClass": "db.t3.medium"
  }
}

Helm Charts Structure

{
  "nginx-ingress": {
    "enabled": true,
    "customValues": false
  },
  "cert-manager": {
    "enabled": true,
    "customValues": true,
    "values": "installCRDs: true\nprometheus:\n  enabled: true"
  }
}

Querying JSONB

Filter by Service

// Find environments with Kubernetes enabled
const environments = await Environment.findAll({
  where: {
    'services.kubernetes.enabled': true
  }
});

Extract Specific Fields

// Get environments with their Kubernetes version
const environments = await Environment.findAll({
  attributes: [
    'id',
    'name',
    [sequelize.json('services.kubernetes.version'), 'k8sVersion']
  ]
});

Update JSONB Fields

// Update specific JSONB path
await Environment.update(
  {
    services: sequelize.fn(
      'jsonb_set',
      sequelize.col('services'),
      '{kubernetes,version}',
      '"1.28"'
    )
  },
  { where: { id: environmentId } }
);

Encryption

AES-256-GCM Implementation

const crypto = require('crypto');

const ALGORITHM = 'aes-256-gcm';
const IV_LENGTH = 16;
const AUTH_TAG_LENGTH = 16;
const KEY = Buffer.from(process.env.ENCRYPTION_KEY, 'hex');

function encrypt(text) {
  const iv = crypto.randomBytes(IV_LENGTH);
  const cipher = crypto.createCipheriv(ALGORITHM, KEY, iv);

  let encrypted = cipher.update(text, 'utf8', 'hex');
  encrypted += cipher.final('hex');

  const authTag = cipher.getAuthTag();

  return iv.toString('hex') + ':' + authTag.toString('hex') + ':' + encrypted;
}

function decrypt(data) {
  const parts = data.split(':');
  const iv = Buffer.from(parts[0], 'hex');
  const authTag = Buffer.from(parts[1], 'hex');
  const encrypted = parts[2];

  const decipher = crypto.createDecipheriv(ALGORITHM, KEY, iv);
  decipher.setAuthTag(authTag);

  let decrypted = decipher.update(encrypted, 'hex', 'utf8');
  decrypted += decipher.final('utf8');

  return decrypted;
}

Indexes

-- Optimize common queries
CREATE INDEX idx_environments_user_id ON environments(user_id);
CREATE INDEX idx_environments_provider ON environments(provider);
CREATE INDEX idx_environments_status ON environments(status);

-- JSONB indexes
CREATE INDEX idx_environments_services_gin ON environments USING GIN (services);
CREATE INDEX idx_environments_k8s_enabled ON environments ((services->'kubernetes'->>'enabled'));

-- Credentials lookup
CREATE INDEX idx_credentials_user_provider ON cloud_credentials(user_id, provider);

Migrations

Sequelize handles migrations automatically in development. For production:

# Generate migration
npx sequelize-cli migration:generate --name add-environment-status

# Run migrations
npx sequelize-cli db:migrate

# Rollback
npx sequelize-cli db:migrate:undo