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

# Transactions and migrations

> How database transactions and schema migrations are managed

## Transaction usage

Prisma supports explicit transactions via `$transaction`. MUZE uses transactions in critical multi-step operations:

### Order placement and approval

```typescript theme={null}
await this.prisma.$transaction(async (tx) => {
  // 1. Update order status
  await tx.order.update({
    where: { id: orderId },
    data: { status: 'APPROVED' },
  });

  // 2. Record consumption against the entitlement period
  await tx.orderItem.update({
    where: { id: itemId },
    data: { entitlementQuantityConsumed: quantity },
  });

  // 3. Create status history record
  await tx.orderStatusHistory.create({
    data: {
      orderId,
      fromStatus: 'SUBMITTED',
      toStatus: 'APPROVED',
      changedById: userId,
    },
  });
});
```

Order placement wraps the employee row lock, entitlement preview, order and item creation, store-order batch linking, and number reservation in a single transaction so concurrent submissions cannot over-allocate entitlement.

### Employee CSV import

Imports are partial-failure tolerant, so rows are committed individually rather than one big transaction:

```typescript theme={null}
for (const row of validRows) {
  await this.prisma.employee.upsert({
    where: { employeeNumber: row.employeeNumber },
    update: { /* changed fields */ },
    create: { ...row },
  });
}
```

Invalid rows are skipped and reported in the `ImportBatch` record; valid rows are not rolled back.

## Transaction patterns

| Pattern                                  | When used                                          |
| ---------------------------------------- | -------------------------------------------------- |
| Single transaction for multi-step writes | Order placement, approval, rejection, cancellation |
| Per-row upserts                          | CSV imports (partial-failure tolerance)            |
| Single atomic write                      | Most CRUD operations                               |

## Migration process

### Development workflow

```mermaid theme={null}
flowchart TD
    A[Modify schema.prisma] --> B[Run prisma migrate dev]
    B --> C[Prisma generates migration SQL]
    C --> D[Review migration SQL]
    D --> E{SQL correct?}
    E -->|No| F[Modify schema again]
    F --> B
    E -->|Yes| G[Commit migration to git]
```

### Production deployment

Migrations run as part of the Render start command before the server boots, so the schema is always current when new code goes live. See [Deployment](/operations/deployment).

### Migration safety rules

| Rule                                              | Rationale                                      |
| ------------------------------------------------- | ---------------------------------------------- |
| Never delete columns in a migration               | Would break running code during deployment     |
| Never rename columns directly                     | Use add-column, migrate data, then drop-column |
| Prefer `ALTER TABLE ... ADD COLUMN` with defaults | Avoids locking large tables                    |
| Keep migrations small                             | Reduces deployment risk                        |
| Test migrations against a copy of production data | Catches data-specific issues                   |

## Connection management

Prisma connects through the URLs configured in the schema:

| Parameter             | Purpose                          |
| --------------------- | -------------------------------- |
| `connection_limit=10` | Max connections in the pool      |
| `pool_timeout=10`     | Seconds to wait for a connection |
| `sslmode=require`     | Enforce TLS for all connections  |

The `directUrl` (without pooling) is used only for migrations, because PgBouncer transaction pooling does not handle DDL statements.

Backup and recovery procedures are covered in [Backups and recovery](/operations/backups-recovery).