​
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