> ## Documentation Index
> Fetch the complete documentation index at: https://cona.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 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.

```typescript theme={null}
// 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

```typescript theme={null}
"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

```typescript theme={null}
// 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.

```typescript theme={null}
// 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

```typescript theme={null}
"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

```typescript theme={null}
// 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.

```typescript theme={null}
// 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

```typescript theme={null}
"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

```typescript theme={null}
// 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.

```typescript theme={null}
// 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

```typescript theme={null}
"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

```typescript theme={null}
// 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