1 reply

Best Way to Model Integrations in Postgres?

Hey everyone, I need advice on modeling data for a system with multiple integrations.

Currently, I plan to support 4-5 integrations (e.g., BigCommerce, Shopify), each requiring unique information like API keys. For now, an organization can only have one integration, but I want to design the model so it’s easy to expand to multiple integrations per organization in the future.

I’m using Postgres and wondering about the best practices for structuring this. How should I model these integrations to handle their unique data while keeping things scalable and maintainable? Any suggestions or examples would be greatly appreciated!

This is my current setup it just holds it in a JSON which feels dirty

model Integration {
  id             String          @id @default(cuid())
  type           IntegrationType // Type of integration (BigCommerce, Shopify, etc)
  organizationId String          @unique // Ensures one integration per org for now
  organization   Organization    @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  
  // Integration-specific credentials stored securely
  credentials    Json           // Stores integration-specific auth data
  
  // Common fields across integrations
  storeName      String?
  storeUrl       String?
  storeLogo      String?
  
  // Integration status
  isActive       Boolean        @default(true)
  lastSyncedAt   DateTime?
  
  // Metadata for integration-specific data
  metadata       Json?
  
  createdAt      DateTime       @default(now())
  updatedAt      DateTime       @updatedAt

  @@index([type])
  @@index([organizationId])
} 

model Organization {
  id          String   @id @default(cuid())
  name        String
  slug        String   @unique
  domain      String?  @unique // Custom domain for the organization
  logo        String?  // Organization logo URL
  brandColor  String?  // Primary brand color (hex)
  metadata    Json?    // Flexible metadata storage
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  
  // Relations
  members     Member[]
  invitations Invitation[]
  roles       OrganizationRole[]
  integration Integration?  // One-to-one relation with integration

  @@index([name])
  @@index([slug])
  @@index([domain])
}

model Integration {
  id             String          @id @default(cuid())
  type           IntegrationType // Type of integration (BigCommerce, Shopify, etc)
  organizationId String          @unique // Ensures one integration per org for now
  organization   Organization    @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  
  // Integration-specific credentials stored securely
  credentials    Json           // Stores integration-specific auth data
  
  // Common fields across integrations
  storeName      String?
  storeUrl       String?
  storeLogo      String?
  
  // Integration status
  isActive       Boolean        @default(true)
  lastSyncedAt   DateTime?
  
  // Metadata for integration-specific data
  metadata       Json?
  
  createdAt      DateTime       @default(now())
  updatedAt      DateTime       @updatedAt

  @@index([type])
  @@index([organizationId])
} 

model Organization {
  id          String   @id @default(cuid())
  name        String
  slug        String   @unique
  domain      String?  @unique // Custom domain for the organization
  logo        String?  // Organization logo URL
  brandColor  String?  // Primary brand color (hex)
  metadata    Json?    // Flexible metadata storage
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  
  // Relations
  members     Member[]
  invitations Invitation[]
  roles       OrganizationRole[]
  integration Integration?  // One-to-one relation with integration

  @@index([name])
  @@index([slug])
  @@index([domain])
}

model Integration {
  id             String          @id @default(cuid())
  type           IntegrationType // Type of integration (BigCommerce, Shopify, etc)
  organizationId String          @unique // Ensures one integration per org for now
  organization   Organization    @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  
  // Integration-specific credentials stored securely
  credentials    Json           // Stores integration-specific auth data
  
  // Common fields across integrations
  storeName      String?
  storeUrl       String?
  storeLogo      String?
  
  // Integration status
  isActive       Boolean        @default(true)
  lastSyncedAt   DateTime?
  
  // Metadata for integration-specific data
  metadata       Json?
  
  createdAt      DateTime       @default(now())
  updatedAt      DateTime       @updatedAt

  @@index([type])
  @@index([organizationId])
} 

model Organization {
  id          String   @id @default(cuid())
  name        String
  slug        String   @unique
  domain      String?  @unique // Custom domain for the organization
  logo        String?  // Organization logo URL
  brandColor  String?  // Primary brand color (hex)
  metadata    Json?    // Flexible metadata storage
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  
  // Relations
  members     Member[]
  invitations Invitation[]
  roles       OrganizationRole[]
  integration Integration?  // One-to-one relation with integration

  @@index([name])
  @@index([slug])
  @@index([domain])
}

model Integration {
  id             String          @id @default(cuid())
  type           IntegrationType // Type of integration (BigCommerce, Shopify, etc)
  organizationId String          @unique // Ensures one integration per org for now
  organization   Organization    @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  
  // Integration-specific credentials stored securely
  credentials    Json           // Stores integration-specific auth data
  
  // Common fields across integrations
  storeName      String?
  storeUrl       String?
  storeLogo      String?
  
  // Integration status
  isActive       Boolean        @default(true)
  lastSyncedAt   DateTime?
  
  // Metadata for integration-specific data
  metadata       Json?
  
  createdAt      DateTime       @default(now())
  updatedAt      DateTime       @updatedAt

  @@index([type])
  @@index([organizationId])
} 

model Organization {
  id          String   @id @default(cuid())
  name        String
  slug        String   @unique
  domain      String?  @unique // Custom domain for the organization
  logo        String?  // Organization logo URL
  brandColor  String?  // Primary brand color (hex)
  metadata    Json?    // Flexible metadata storage
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  
  // Relations
  members     Member[]
  invitations Invitation[]
  roles       OrganizationRole[]
  integration Integration?  // One-to-one relation with integration

  @@index([name])
  @@index([slug])
  @@index([domain])
}

Best Way to Model Integrations in Postgres?

Best Way to Model Integrations in Postgres?

Similar Threads

Similar Threads

Similar Threads