Database Versioning Guide

Detailed implementation guidelines for the four database versioning approaches used in CONA

Database Versioning Guide

CONA implements four different tracking approaches for database versioning, each designed for specific use cases. This guide provides detailed implementation guidelines for each approach.

1. Audit Trail Versioning

Used for: entities, addresses, organization details, payment methods

Implementation

The Audit Trail approach creates a complete history of all changes to a record by maintaining a parent-child relationship with a root record. Each update creates a new record while preserving the history through these relationships.

// Schema example
model Entity {
  id                String    @id @default(cuid())
  name              String
  // Other fields
  created_at        DateTime  @default(now())
  updated_at        DateTime  @updatedAt
  modified_by_actor_id String?
  label             String
  revision          Int       @default(1)
  is_current        Boolean   @default(true)
  root_entity_id    String?
  is_deleted        Boolean   @default(false)
  deleted_at        DateTime?
  deleted_by_actor_id String?
  deleted_reason    String?
  
  // Relations
  modified_by       actors?   @relation("EntitiesModifiedBy", fields: [modified_by_actor_id], references: [id])
  deleted_by        actors?   @relation("EntitiesDeletedBy", fields: [deleted_by_actor_id], references: [id])
  root_entity       Entity?   @relation("EntityVersions", fields: [root_entity_id], references: [id])
  child_entities    Entity[]  @relation("EntityVersions")
}

Implementation Pattern

"use server";

import { prisma } from "@cona/database";
import { auth } from "lib/auth";

export async function updateEntity(id: string, data: EntityUpdateData) {
  // Get current user
  const user = await auth.getCurrentUser();
  
  // Create transaction to handle versioning with serializable isolation
  const result = await prisma.$transaction(async (tx) => {
    // Fetch current entity record - serializable isolation level prevents concurrent modifications
    const currentEntity = await tx.entity.findUnique({
      where: { 
        id, 
        is_current: true 
      }
    });
    
    if (!currentEntity) throw new Error("Entity not found");
    
    // Determine root entity ID
    const rootEntityId = currentEntity.root_entity_id || currentEntity.id;
    
    // Mark current entity as not current
    await tx.entity.update({
      where: { id },
      data: {
        is_current: false,
        modified_by_actor_id: user.id
      }
    });
    
    // Get the latest revision number to ensure we're creating the correct next revision
    const latestRevision = await tx.entity.findFirst({
      where: {
        OR: [
          { id: rootEntityId },
          { root_entity_id: rootEntityId }
        ]
      },
      orderBy: { revision: 'desc' },
      select: { revision: true }
    });
    
    const nextRevision = (latestRevision?.revision || currentEntity.revision) + 1;
    
    // Create new entity version with the correct revision number
    const newEntity = await tx.entity.create({
      data: {
        ...currentEntity,
        ...data,
        id: undefined, // Let the database generate a new ID
        revision: nextRevision,
        is_current: true,
        root_entity_id: rootEntityId,
        modified_by_actor_id: user.id
      }
    });
    
    return newEntity;
  }, {
    isolationLevel: 'Serializable' // Ensures the highest isolation level for the transaction
  });
  
  return result;
}

Query Patterns

// Get current entity version
export async function getCurrentEntity(id: string) {
  // Get the entity if it's the current version
  const entity = await prisma.entity.findFirst({
    where: { 
      id,
      is_current: true 
    }
  });
  
  if (entity) return entity;
  
  // If not current, find the current version from the root
  return prisma.entity.findFirst({
    where: {
      OR: [
        { root_entity_id: id, is_current: true },
        { id, child_entities: { some: { is_current: true } } }
      ]
    }
  });
}

// Get entity history
export async function getEntityHistory(id: string) {
  // First determine if this is a root entity or a child entity
  const entity = await prisma.entity.findUnique({
    where: { id },
    include: { root_entity: true }
  });
  
  if (!entity) return [];
  
  // If this is a child entity, get the root ID
  const rootId = entity.root_entity_id || entity.id;
  
  // Get all versions of this entity
  return prisma.entity.findMany({
    where: {
      OR: [
        { id: rootId },
        { root_entity_id: rootId }
      ]
    },
    orderBy: { revision: 'desc' }
  });
}

// Get entity at specific revision
export async function getEntityAtRevision(id: string, revision: number) {
  // First determine if this is a root entity or a child entity
  const entity = await prisma.entity.findUnique({
    where: { id }
  });
  
  if (!entity) return null;
  
  // If this is a child entity, get the root ID
  const rootId = entity.root_entity_id || entity.id;
  
  // Find the specific revision
  return prisma.entity.findFirst({
    where: {
      OR: [
        { id: rootId, revision },
        { root_entity_id: rootId, revision }
      ]
    }
  });
}

2. Activity Log Tracking

Used for: product_variants, documents, inventory adjustments, price changes

Implementation

Activity Log Tracking records specific notable activities rather than capturing every change to a record. In CONA, this is implemented using a centralized activity_logs table that tracks changes across different entity types.

// Schema example
model activity_logs {
  id                   String      @id @default(cuid())
  timestamp            DateTime    @default(now())
  action               String
  actor_id             String
  details              String?
  created_at           DateTime    @default(now())
  updated_at           DateTime    @updatedAt
  documents_id         String?
  entities_id          String?
  items_id             String?
  product_variantsId   String?
  changed_field        String?
  new_value            String?
  old_value            String?
  org_id               String
  
  // Relations
  actor                actors      @relation(fields: [actor_id], references: [id])
  documents            documents?  @relation(fields: [documents_id], references: [id])
  entities             entities?   @relation(fields: [entities_id], references: [id])
  items                items?      @relation(fields: [items_id], references: [id])
  organization         organization @relation(fields: [org_id], references: [id])
  product_variants     product_variants? @relation(fields: [product_variantsId], references: [id])
  // Other relations...
}

Implementation Pattern

"use server";

import { prisma } from "@cona/database";
import { auth } from "lib/auth";

export async function updateProductPrice(id: string, newPrice: number) {
  // Get current user
  const user = await auth.getCurrentUser();
  
  // Fetch current product variant
  const productVariant = await prisma.productVariant.findUnique({
    where: { id }
  });
  
  if (!productVariant) throw new Error("Product variant not found");
  
  const oldPrice = productVariant.price;
  
  // Create transaction to update product and log activity
  const result = await prisma.$transaction(async (tx) => {
    // Update product variant
    const updatedVariant = await tx.productVariant.update({
      where: { id },
      data: { price: newPrice }
    });
    
    // Create activity log entry
    await tx.activity_logs.create({
      data: {
        action: "PRICE_CHANGE",
        actor_id: user.id,
        product_variantsId: id,
        changed_field: "price",
        old_value: oldPrice.toString(),
        new_value: newPrice.toString(),
        details: "Manual price update",
        org_id: user.active_organization_id
      }
    });
    
    return updatedVariant;
  });
  
  return result;
}

Query Patterns

// Get price change history for a product
export async function getProductPriceHistory(id: string) {
  return prisma.activity_logs.findMany({
    where: { 
      product_variantsId: id,
      action: "PRICE_CHANGE"
    },
    orderBy: { timestamp: 'desc' }
  });
}

// Get all activities for a product
export async function getProductActivities(id: string) {
  return prisma.activity_logs.findMany({
    where: { product_variantsId: id },
    orderBy: { timestamp: 'desc' }
  });
}

3. Simple Update

Used for: sales_channels, locations, tax rates, shipping methods

Implementation

Simple Update tracking only maintains the current state with basic timestamp tracking.

// Schema example
model SalesChannel {
  id            String   @id @default(uuid())
  name          String
  // Other fields
  created_at    DateTime @default(now())
  created_by    String
  updated_at    DateTime @updatedAt
  updated_by    String?
}

Implementation Pattern

"use server";

import { prisma } from "@cona/database";
import { auth } from "lib/auth";

export async function updateSalesChannel(id: string, data: SalesChannelUpdateData) {
  // Get current user
  const user = await auth.getCurrentUser();
  
  // Update with timestamp and user tracking
  const updatedChannel = await prisma.salesChannel.update({
    where: { id },
    data: {
      ...data,
      updated_by: user.id
    }
  });
  
  return updatedChannel;
}

Query Patterns

// Get sales channel details
export async function getSalesChannel(id: string) {
  return prisma.salesChannel.findUnique({
    where: { id }
  });
}

4. Soft Delete with New Record

Used for: contacts, email_addresses, phone_numbers, shipping addresses

Implementation

This approach maintains historical records while preserving data integrity by marking records as deleted rather than removing them from the database.

// Schema example
model email_addresses {
  id                   String       @id @default(cuid())
  label                String
  email                String
  contacts_id          String?
  is_deleted           Boolean      @default(false)
  deleted_at           DateTime?
  deleted_by_actor_id  String?
  deleted_reason       String?
  modified_by_actor_id String?
  org_id               String
  
  // Relations
  contacts             contacts?    @relation(fields: [contacts_id], references: [id])
  deleted_by           actors?      @relation("EmailAddressesDeletedBy", fields: [deleted_by_actor_id], references: [id])
  modified_by          actors?      @relation("EmailAddressesModifiedBy", fields: [modified_by_actor_id], references: [id])
  organization         organization @relation(fields: [org_id], references: [id])
}

Implementation Pattern

"use server";

import { prisma } from "@cona/database";
import { auth } from "lib/auth";

export async function updateEmailAddress(id: string, data: EmailAddressUpdateData) {
  // Get current user
  const user = await auth.getCurrentUser();
  
  // Fetch current email address
  const currentEmail = await prisma.email_addresses.findUnique({
    where: { id, is_deleted: false }
  });
  
  if (!currentEmail) throw new Error("Email address not found");
  
  // Create transaction to soft delete old record and create new one
  const result = await prisma.$transaction(async (tx) => {
    // Mark old record as deleted
    await tx.email_addresses.update({
      where: { id },
      data: {
        is_deleted: true,
        deleted_at: new Date(),
        deleted_by_actor_id: user.id,
        deleted_reason: "Updated with new information"
      }
    });
    
    // Create new email record
    const newEmail = await tx.email_addresses.create({
      data: {
        label: data.label || currentEmail.label,
        email: data.email || currentEmail.email,
        contacts_id: currentEmail.contacts_id,
        modified_by_actor_id: user.id,
        org_id: currentEmail.org_id
      }
    });
    
    return newEmail;
  });
  
  return result;
}

Query Patterns

// Get active email addresses
export async function getActiveEmailAddresses(contactId: string) {
  return prisma.email_addresses.findMany({
    where: { 
      contacts_id: contactId,
      is_deleted: false
    }
  });
}

// Get all email addresses including deleted ones
export async function getAllEmailAddresses(contactId: string) {
  return prisma.email_addresses.findMany({
    where: { contacts_id: contactId },
    orderBy: { deleted_at: 'desc' }
  });
}

// Get deleted email addresses
export async function getDeletedEmailAddresses(contactId: string) {
  return prisma.email_addresses.findMany({
    where: { 
      contacts_id: contactId,
      is_deleted: true
    },
    orderBy: { deleted_at: 'desc' }
  });
}

When to Use Each Approach

Audit Trail Versioning

Best for: Core business entities where all changes must be tracked for compliance or auditing purposes.

Characteristics:

• Complete change history is critical
• Legal or compliance requirements
• Need to restore to any previous state
• Changes are relatively infrequent

Activity Log Tracking

Best for: Records where specific activities are more important than tracking every change.

Characteristics:

• Domain-specific activities need tracking
• More concerned with what happened than complete change history
• Frequent minor changes that don’t need full history
• Need to track who performed specific actions

Simple Update

Best for: Configuration or reference data where only the current state matters.

Characteristics:

• Only current state is relevant
• Change history is not needed for business operations
• Support data rather than core business entities
• Frequent changes where history is not valuable

Soft Delete with New Record

Best for: Records where historical values may still be referenced by other records.

Characteristics:

• Need to maintain referential integrity with historical data
• Both current and previous versions may be needed
• Changes create effectively new entities
• Changes are infrequent but meaningful