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

# General Ledger Entry Consolidation for DATEV Export

> RFC for implementing GL entry consolidation to reduce DATEV export size while maintaining reconciliation integrity

## 🎉 Implementation Status Update (2025-01-04)

**Phase 1 (Virtual Consolidation) is COMPLETE and ready for testing!**

✅ **Implemented:**

* Core consolidation logic with advanced reconciliation handling
* Sammelbeleg PDF generation using `pdf-lib`
* DATEV export integration with ZIP download
* UI toggle enabled with BETA label
* Comprehensive unit tests

⏳ **Next Steps:**

1. Test with real production-like data
2. Deploy to staging for team testing
3. Code review and approval
4. Monitor for errors and gather feedback
5. Proceed to Phase 2 (Persistent Consolidation) after validation

📖 **See Phase 1 Implementation Details:** [gl-entry-consolidation-phase1-virtual.mdx](./gl-entry-consolidation-phase1-virtual.mdx)

***

## Executive Summary

Implement GL entry consolidation to reduce the number of entries in DATEV exports by aggregating entries with matching accounts and dimensions. This RFC outlines a hybrid approach that uses virtual consolidation for test exports and persistent consolidation for period closure exports.

**Key Goals:**

* Reduce DATEV export line count by consolidating similar entries
* Maintain reconciliation integrity (Belegfeld 1)
* Generate **Sammelbeleg (Collective Receipt) PDF** for each consolidated entry
* Store Sammelbeleg as proper source document (Beleg) for GoBD compliance
* Provide full audit trail through formal supporting documentation

**Critical Design Decision:**

**Consolidation happens ONLY by accounts + dimensions, NOT by date.** This means entries from different dates (e.g., Jan 10, Jan 15, Jan 20) that share the same debit account, credit account, and dimensions will be consolidated into a single entry. The date range is preserved in metadata, and the latest date is used as the Belegdatum in the DATEV export.

**Reconciliation Constraint:** Only entries where ALL reconciliation groups are complete/closed within the exported period can be consolidated. This ensures each consolidated entry is fully reconcilable. A new Belegfeld 1 is assigned to the consolidated entry (format: `CONS-{cuid}`, e.g., `CONS-clh5z1234567890abcdef`), which references a **Sammelbeleg PDF** containing all individual transaction details.

**Rationale:** Accounting periods typically span multiple days, weeks, or months. Consolidating across dates provides maximum reduction in export size (potentially 80-90% reduction vs 40-60% with date grouping) while maintaining accounting accuracy. This is standard practice in accounting consolidation.

**Example Impact:**

```
Original Entries (100 entries total):
- 2024-01-01 to 2024-01-31: Daily sales entries, same accounts (1000→4000)
- Each day has 3-5 transactions
- Mix of positive amounts (+100, +150, +200) and negative amounts (-50 for credit notes)

❌ Alternative Approach (NOT used): With Date Grouping
100 entries → 31 consolidated entries (one per day) = 69% reduction
↑ NOT what we're doing

✅ This RFC Approach: WITHOUT Date Grouping (consolidate across all dates)
100 entries → 1 consolidated entry (all dates combined) = 99% reduction
↑ This is what we implement

Consolidation Rules (what we DO consolidate across):
  ✅ Transaction dates (Jan 1, Jan 5, Jan 20 → all combine)
  ✅ Amount signs (positive + negative → algebraic sum)
     - Example: +100, +150, -50, +200 = 400 total
     - Soll/Haben indicator set based on final sign (positive = S, negative = H)

What we DON'T consolidate across (must match):
  ❌ Accounts (debit/credit must be same)
  ❌ Tax rates (19% vs 7% stay separate)
  ❌ Dimensions (different cost centers stay separate)

Why? Consolidating across dates AND signs provides maximum reduction while
maintaining accounting accuracy. The date range (Jan 1-31) is preserved in
metadata and the Sammelbeleg PDF shows all individual transaction dates and amounts.
```

## Problem Statement

### Current Situation

DATEV exports contain one line per general ledger entry, which can result in:

* Large CSV files with thousands of entries
* Difficult to review and verify exports
* Redundant data when multiple transactions share the same characteristics
* DATEV import processing overhead

### User Requirements

1. Consolidate entries that match on:
   * Debit account
   * Credit account
   * All GL dimensions (including custom dimensions)
   * **Tax rate** - Entries must have the same tax rate (or both null)
     * **Rationale:** Different tax rates represent different tax treatment and must be kept separate
   * **NOT by transaction date** - Entries from different dates CAN be consolidated together
     * **Rationale:** Accounting periods often span multiple days/weeks/months. Consolidating across dates reduces export size significantly while maintaining account accuracy. The date range is preserved in metadata for reference.
   * **NOT by amount sign** - Positive and negative amounts CAN be consolidated together
     * **Rationale:** Amounts are summed algebraically (e.g., +100, +150, -50, +200 = 400). In DATEV export, the consolidated amount is always exported as an absolute value with the Soll/Haben indicator (S or H) determined by the final sign. Since we always use "S" for the consolidated entries in this implementation (debit posting), consolidating across signs maximizes reduction while maintaining accounting accuracy.

2. **Reconciliation Completeness Constraint** - Only consolidate entries where ALL reconciliation groups are complete/closed within the exported period
   * **Rationale:** Ensures each consolidated entry represents a complete reconcilable unit
   * **Critical:** Prevents partial reconciliation exports (e.g., consolidating entries where some reconcile in Period 1 and others in Period 2)
   * **Implementation:** Check that all `reconciliation_groups.reconciled_at` timestamps are within the period being exported

3. **New Belegfeld 1 Assignment** - Generate a NEW reconciliation ID (Belegfeld 1) for each consolidated entry
   * **Rationale:** Indicates to DATEV that this consolidated entry is reconcilable as a complete unit
   * **Format:** `CONS-{cuid}` (e.g., `CONS-clh5z1234567890abcdef`)
   * **Benefit:** Simplifies DATEV export - one line per consolidated entry with single Belegfeld 1

4. Provide transparency and GoBD compliance:
   * Generate **Sammelbeleg (Collective Receipt) PDF** for each consolidated entry
   * Store PDF as proper source document (Beleg) in document management system
   * PDF contains full detail of all consolidated transactions
   * Store consolidation metadata in document custom properties

5. Hybrid approach:
   * Virtual (non-persistent) consolidation for "Test File" exports (PDF generated but not stored)
   * Persistent consolidation for "Close Period and Export" operations (PDF stored as document)

## Proposed Solution

### Architecture Overview

```mermaid theme={null}
flowchart TD
    Start[DATEV Export Flow] --> ExportType{Export Type?}

    ExportType -->|Test File| TestFile[Test File Export]
    ExportType -->|Close Period| ClosePeriod[Close Period Export]

    TestFile --> Virtual[Virtual Consolidation<br/>• No DB write<br/>• Fast<br/>• Temporary]
    ClosePeriod --> Persistent[Persistent Consolidation<br/>• DB writes<br/>• Audit trail<br/>• Permanent]

    Virtual --> Generate[DATEV CSV Generation<br/>+ Sammelbeleg PDFs]
    Persistent --> Generate

    Generate --> Output[Single ZIP Download<br/>• datev_export.csv<br/>• sammelbeleg_*.pdf files]

    style Start fill:#e1f5ff
    style ExportType fill:#fff3cd
    style TestFile fill:#d4edda
    style ClosePeriod fill:#d4edda
    style Virtual fill:#cfe2ff
    style Persistent fill:#f8d7da
    style Generate fill:#d1ecf1
    style Output fill:#d4edda
```

### Database Schema Changes

#### General Ledger Table Extensions

```sql theme={null}
-- Migration: 20241023_add_consolidation_tracking.sql

ALTER TABLE "general_ledger"
  ADD COLUMN "consolidation_id" TEXT,
  ADD COLUMN "is_consolidated" BOOLEAN DEFAULT false,
  ADD COLUMN "consolidated_from_ids" JSONB,
  ADD COLUMN "consolidation_metadata" JSONB,
  ADD COLUMN "sammelbeleg_document_id" TEXT; -- References documents table

-- Foreign key to documents table
ALTER TABLE "general_ledger"
  ADD CONSTRAINT "general_ledger_sammelbeleg_document_fkey"
  FOREIGN KEY ("sammelbeleg_document_id") REFERENCES "documents"("id")
  ON DELETE SET NULL;

-- Indexes for performance
CREATE INDEX "general_ledger_consolidation_id_idx"
  ON "general_ledger"("consolidation_id");

CREATE INDEX "general_ledger_is_consolidated_org_idx"
  ON "general_ledger"("is_consolidated", "org_id");

CREATE INDEX "general_ledger_sammelbeleg_document_id_idx"
  ON "general_ledger"("sammelbeleg_document_id");

-- Add composite index for consolidation grouping (excludes transaction_date, includes tax_rate)
CREATE INDEX "general_ledger_consolidation_key_idx"
  ON "general_ledger"(
    "org_id",
    "accounting_period_id",
    "debit_account_id",
    "credit_account_id",
    "tax_rate",
    "is_deleted"
  ) WHERE "is_deleted" = false;
```

#### Consolidation Logs Table (Audit Trail)

```sql theme={null}
CREATE TABLE "consolidation_logs" (
    "id" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "period_id" TEXT NOT NULL,
    "org_id" TEXT NOT NULL,
    "actor_id" TEXT,
    "export_type" TEXT NOT NULL, -- 'test' or 'period_close'
    "consolidation_count" INTEGER NOT NULL,
    "original_entry_count" INTEGER NOT NULL,
    "reduction_percentage" DOUBLE PRECISION NOT NULL,
    "consolidation_rules" JSONB NOT NULL,
    "csv_export_path" TEXT,
    "is_virtual" BOOLEAN NOT NULL DEFAULT false,

    CONSTRAINT "consolidation_logs_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "consolidation_logs_period_id_idx"
  ON "consolidation_logs"("period_id");

CREATE INDEX "consolidation_logs_org_created_idx"
  ON "consolidation_logs"("org_id", "created_at");
```

#### Object Type for Sammelbeleg Documents

```sql theme={null}
-- Create dedicated object_type for Sammelbeleg documents
-- This allows easy filtering and identification of consolidation documents

INSERT INTO "object_types" ("id", "label", "category")
VALUES ('SAMMELBELEG', 'Sammelbeleg', 'INTERNAL');

-- Note: This creates a distinct document type that can be:
-- - Filtered from normal document lists
-- - Queried separately for consolidation reports
-- - Clearly identified in document management UI
```

#### Design Decision: Dedicated Object Type

**Why use a separate `object_type` for Sammelbeleg documents?**

Sammelbeleg documents are stored in the `documents` table alongside regular business documents (invoices, receipts, etc.), which might initially seem like "duplication." However, using a dedicated `object_type_id = "SAMMELBELEG"` provides clear separation:

**Benefits:**

1. **Clear Identification** - Easy to distinguish consolidation documents from business documents
2. **Flexible Filtering** - Can exclude/include from queries as needed
3. **UI Separation** - Default document lists show only business documents
4. **Proper GoBD Compliance** - Sammelbeleg is treated as a legitimate source document (Beleg)
5. **Document Lifecycle** - Benefits from standard document management (archival, retention, permissions)

**Example Queries:**

```typescript theme={null}
// Get only business documents (exclude Sammelbeleg)
const businessDocuments = await prisma.documents.findMany({
  where: {
    org_id: organizationId,
    object_type_id: { not: "SAMMELBELEG" },
    is_deleted: false,
  },
});

// Get only Sammelbeleg documents
const sammelbelegDocuments = await prisma.documents.findMany({
  where: {
    org_id: organizationId,
    object_type_id: "SAMMELBELEG",
    is_deleted: false,
  },
});

// Get all documents (including Sammelbeleg)
const allDocuments = await prisma.documents.findMany({
  where: {
    org_id: organizationId,
    is_deleted: false,
  },
});
```

**This is NOT duplication** - it's proper document categorization. Similar to how:

* Monthly expense reports exist alongside individual receipts
* Summary statements exist alongside transaction details
* Consolidated invoices exist alongside line items

The Sammelbeleg is a **derived summary document** that serves a different purpose than the original business documents.

### Consolidation Logic

#### Grouping Key Algorithm

Entries are grouped for consolidation using a composite key (excluding transaction date):

```typescript theme={null}
interface ConsolidationGroupKey {
  debitAccountId: string;
  creditAccountId: string;
  dimensionsHash: string; // Deterministic hash of sorted dimensions
  currency: string;
  taxRate: string; // Tax rate as string (null becomes 'null')
  // NOTE: Transaction date is NOT part of the consolidation key
  // NOTE: Amount sign is NOT part of the consolidation key (amounts sum algebraically)
}

function generateConsolidationKey(entry: GeneralLedgerEntry): string {
  // Sort dimensions for consistent hashing
  const sortedDimensions = entry.dimensions
    ? Object.entries(entry.dimensions)
        .sort(([a], [b]) => a.localeCompare(b))
        .map(([key, value]) => `${key}:${value}`)
        .join("|")
    : "null";

  // Tax rate as string (null becomes 'null')
  const taxRateKey = entry.tax_rate !== null ? entry.tax_rate.toString() : "null";

  // NOTE: Date is NOT included in consolidation key
  // This allows entries from different dates to be consolidated together
  // NOTE: Amount sign is NOT included - amounts sum algebraically (e.g., 100 + (-50) = 50)
  return [
    entry.debit_account_id,
    entry.credit_account_id,
    sortedDimensions,
    entry.currency,
    taxRateKey,
  ].join("::");
}
```

#### Consolidation Process

```typescript theme={null}
interface ConsolidatedEntry {
  consolidationId: string; // CUID for the consolidated entry
  originalEntryIds: string[]; // Array of original GL entry IDs
  transactionDateRange: {
    earliest: Date; // Earliest transaction date from consolidated entries
    latest: Date; // Latest transaction date from consolidated entries
  };
  debitAccountId: string;
  creditAccountId: string;
  dimensions: Record<string, any>;
  consolidatedAmount: number; // Sum of all amounts
  currency: string;
  taxRate: number | null; // Consolidated tax rate (all entries must have same rate)

  // New Belegfeld 1 for consolidated entry (DATEV compliant: max 36 chars)
  consolidatedBelegfeld1: string; // Format: CONS-{cuid} (e.g., "CONS-clh5z1234567890abcdef")

  // Original reconciliation data (for audit trail)
  originalReconciliationGroups: Array<{
    id: string;
    reconciledOn: string | null; // Original Belegfeld 1 value
    reconciledAt: Date | null; // When reconciliation was completed
    amount: number; // Amount from this group
    entryIds: string[]; // Which original entries used this group
  }>;

  // Metadata
  entryCount: number;
  taxRates: Array<{ rate: number; amount: number }>; // Multiple tax rates
  documents: Array<{ id: string; number: string; date: Date }>; // Source documents with dates
  allReconciledWithinPeriod: boolean; // Validation flag

  // Sammelbeleg (Collective Receipt) PDF
  sammelbelegDocumentId?: string; // ID of the Sammelbeleg document in documents table
  sammelbelegPdfUrl?: string; // URL to the generated PDF
}
```

### Reconciliation Handling

**Key Constraint:** Only consolidate entries where ALL reconciliation groups are complete within the exported period.

**Key Implementation:** Each consolidated entry gets a NEW Belegfeld 1 (reconciliation ID) in format `CONS-{consolidationId}`.

#### Example Scenario

```
Original Entries (Note: Different dates, same tax rate, all recon groups complete):
1. Date: 2024-01-10, 1000 → 4000, Amount: 100 EUR, Tax: 19%, Recon: Group-A (Reconciled: 2024-01-12)
2. Date: 2024-01-15, 1000 → 4000, Amount: 150 EUR, Tax: 19%, Recon: Group-B (Reconciled: 2024-01-18)
3. Date: 2024-01-20, 1000 → 4000, Amount: 200 EUR, Tax: 19%, Recon: Group-C (Reconciled: 2024-01-22)
4. Date: 2024-01-25, 1000 → 4000, Amount:  50 EUR, Tax: 19%, Recon: Group-D (Reconciled: 2024-01-28)

Exported Period: 2024-01-01 to 2024-01-31
✅ Same accounts + dimensions + tax rate (19%)
✅ All reconciliation groups reconciled within period
→ Can consolidate

Consolidated Entry:
- Amount: 450 EUR (100+150+200+50)
- Tax Rate: 19%
- Date Range: 2024-01-10 to 2024-01-25 (earliest to latest)
- New Belegfeld 1: "CONS-clh5z1234567890abc" (NEW reconciliation ID for consolidated entry)
- Original Reconciliation Groups: [Group-A, Group-B, Group-C, Group-D] (stored in metadata)
- Entry Count: 4 entries
```

**Counter-Examples (Would NOT be consolidated):**

**Example 1: Incomplete Reconciliation**

```
Entry 1: Reconciled 2024-01-28 ✅ (within period Jan 2024)
Entry 2: Reconciled 2024-02-05 ❌ (outside period, in Feb 2024)
→ These entries would NOT be consolidated together in Jan 2024 export

Why? Entry 2's reconciliation is not complete in January, so it would appear
as "unreconciled" in the January export. Consolidating would create an
incomplete reconciliation unit.

Solution: Entry 1 exports individually in Jan with its original Belegfeld 1.
          Entry 2 exports individually in Feb with its original Belegfeld 1.
```

**Example 2: Different Tax Rates**

```
Entry 1: Tax Rate: 19%, Account: 1000 → 4000, Dimensions: {}, Reconciled ✅
Entry 2: Tax Rate: 7%,  Account: 1000 → 4000, Dimensions: {}, Reconciled ✅
→ These entries would NOT be consolidated together

Why? Different tax rates represent different tax treatment and must be
reported separately for tax compliance.

Solution: Each exports as separate line (or consolidates with entries of same tax rate)
```

**Example 3: Positive and Negative Amounts (Algebraic Summation)**

```
Entry 1: Amount: +100 EUR, Account: 1000 → 4000, Tax: 19%, Reconciled ✅ (Invoice)
Entry 2: Amount: -50 EUR,  Account: 1000 → 4000, Tax: 19%, Reconciled ✅ (Credit Note)
→ These entries WOULD be consolidated together ✅

Why? Amounts sum algebraically in consolidation (100 + (-50) = 50).
In DATEV export, the amount is always absolute with a Soll/Haben indicator.

Result:
  - Consolidated Amount: 50 EUR
  - DATEV Export: Umsatz=50,00, Soll/Haben=S (if result is positive)

This is correct because the net effect is +50 EUR on the accounts.
```

**Reconciliation Completeness Flow:**

```mermaid theme={null}
flowchart TD
    Start[Entries with Same<br/>Accounts + Dimensions] --> CheckRecon{All Reconciliation<br/>Groups Complete<br/>in Period?}

    CheckRecon -->|Yes| Consolidate[✅ Consolidate<br/>Assign NEW Belegfeld 1<br/>CONS-clh5z1234...]
    CheckRecon -->|No| Individual[❌ Export Individually<br/>Keep Original Belegfeld 1]

    Consolidate --> Export1[DATEV Export:<br/>1 consolidated line<br/>Belegfeld: CONS-clh5z1234...]
    Individual --> Export2[DATEV Export:<br/>Multiple individual lines<br/>Original Belegfeld values]

    style Start fill:#e3f2fd
    style CheckRecon fill:#fff3cd
    style Consolidate fill:#d4edda
    style Individual fill:#f8d7da
    style Export1 fill:#d1ecf1
    style Export2 fill:#d1ecf1
```

**DATEV Export Strategy:**

**Single Line with New Belegfeld 1 (Chosen Approach)**

```csv theme={null}
# One consolidated entry with new reconciliation ID
Umsatz,Konto,Gegenkonto,Belegfeld 1,...
450.00,1000,4000,CONS-clh5z1234567890,...  # New Belegfeld 1 for consolidated entry
```

**Benefits:**

* ✅ Simpler export (one line per consolidated entry)
* ✅ Clear indication that entry is reconcilable as a unit
* ✅ DATEV recognizes the entry as complete and reconciled
* ✅ Original reconciliation groups preserved in metadata for audit

### Core Function: `consolidateGLEntries`

```typescript theme={null}
// packages/core/src/general_ledger/consolidate-entries.ts
import { createId } from "@paralleldrive/cuid2";

interface ConsolidateGLEntriesParams {
  periodId: string;
  organizationId: string;
  actorId?: string;
  isPersistent: boolean; // true for period close, false for test export
}

interface ConsolidationResult {
  consolidatedEntries: ConsolidatedEntry[];
  consolidationMap: Map<string, string[]>; // consolidationId → originalEntryIds
  originalCount: number;
  consolidatedCount: number;
  reductionPercentage: number;
  csvData: ConsolidationCSVRow[];
}

export async function consolidateGLEntries({
  periodId,
  organizationId,
  actorId,
  isPersistent,
}: ConsolidateGLEntriesParams): Promise<ActionResponse<ConsolidationResult>> {
  try {
    // 0. Get period date range for reconciliation validation
    const period = await prisma.accounting_periods.findUnique({
      where: { id: periodId },
      select: { start_date: true, end_date: true },
    });

    if (!period) {
      throw new Error("Accounting period not found");
    }

    // 1. Fetch all GL entries for the period
    const entries = await prisma.general_ledger.findMany({
      where: {
        accounting_period_id: periodId,
        org_id: organizationId,
        is_deleted: false,
      },
      include: {
        debit_account: { select: { account_nr: true } },
        credit_account: { select: { account_nr: true } },
        document: { select: { id: true, nr: true } },
        debit_reconciliation_group: {
          select: {
            id: true,
            reconciled_on: true,
            reconciled_at: true, // CRITICAL: Need this to check if reconciled within period
          },
        },
        credit_reconciliation_group: {
          select: {
            id: true,
            reconciled_on: true,
            reconciled_at: true, // CRITICAL: Need this to check if reconciled within period
          },
        },
      },
    });

    // 2. Group entries by consolidation key
    const groups = new Map<string, GeneralLedgerEntry[]>();

    for (const entry of entries) {
      const key = generateConsolidationKey(entry);
      if (!groups.has(key)) {
        groups.set(key, []);
      }
      groups.get(key)!.push(entry);
    }

    // 3. Create consolidated entries
    const consolidatedEntries: ConsolidatedEntry[] = [];
    const consolidationMap = new Map<string, string[]>();
    const csvData: ConsolidationCSVRow[] = [];

    for (const [key, groupEntries] of groups.entries()) {
      if (groupEntries.length === 1) {
        // Don't consolidate single entries
        continue;
      }

      // CRITICAL: Check if ALL reconciliation groups are complete within period
      const allReconciledWithinPeriod = groupEntries.every((entry) => {
        const debitReconDate = entry.debit_reconciliation_group?.reconciled_at;
        const creditReconDate = entry.credit_reconciliation_group?.reconciled_at;

        // If entry has reconciliation, it must be reconciled within period
        const debitOk =
          !entry.debit_reconciliation_group_id ||
          (debitReconDate &&
            debitReconDate >= period.start_date &&
            debitReconDate <= period.end_date);

        const creditOk =
          !entry.credit_reconciliation_group_id ||
          (creditReconDate &&
            creditReconDate >= period.start_date &&
            creditReconDate <= period.end_date);

        return debitOk && creditOk;
      });

      if (!allReconciledWithinPeriod) {
        // Skip consolidation if not all reconciliation groups are complete in period
        continue;
      }

      const consolidationId = createId(); // CUID for tracking/database
      const originalIds = groupEntries.map((e) => e.id);

      // Aggregate amounts
      const consolidatedAmount = groupEntries.reduce((sum, e) => sum + e.amount, 0);

      // Collect original reconciliation groups for audit trail
      const originalReconGroups = new Map<
        string,
        {
          id: string;
          reconciledOn: string | null;
          reconciledAt: Date | null;
          amount: number;
          entryIds: string[];
        }
      >();

      for (const entry of groupEntries) {
        // Collect credit reconciliation groups
        if (entry.credit_reconciliation_group_id) {
          const reconId = entry.credit_reconciliation_group_id;
          if (!originalReconGroups.has(reconId)) {
            originalReconGroups.set(reconId, {
              id: reconId,
              reconciledOn: entry.credit_reconciliation_group?.reconciled_on || null,
              reconciledAt: entry.credit_reconciliation_group?.reconciled_at || null,
              amount: entry.amount,
              entryIds: [entry.id],
            });
          } else {
            const existing = originalReconGroups.get(reconId)!;
            existing.amount += entry.amount;
            existing.entryIds.push(entry.id);
          }
        }
      }

      // Calculate date range from all entries
      const dates = groupEntries.map((e) => e.transaction_date);
      const earliestDate = new Date(Math.min(...dates.map((d) => d.getTime())));
      const latestDate = new Date(Math.max(...dates.map((d) => d.getTime())));

      // Build consolidated entry
      const consolidated: ConsolidatedEntry = {
        consolidationId,
        originalEntryIds: originalIds,
        transactionDateRange: {
          earliest: earliestDate,
          latest: latestDate,
        },
        debitAccountId: groupEntries[0].debit_account_id,
        creditAccountId: groupEntries[0].credit_account_id,
        dimensions: groupEntries[0].dimensions as Record<string, any>,
        consolidatedAmount,
        currency: groupEntries[0].currency,
        taxRate: groupEntries[0].tax_rate, // All entries have same tax rate (validated by grouping key)

        // NEW: Generate new Belegfeld 1 for consolidated entry (DATEV compliant: max 36 chars)
        // Format: CONS-{cuid} = 5 + 25 = 30 characters total (well within 36 char limit)
        consolidatedBelegfeld1: `CONS-${consolidationId}`,

        // Store original reconciliation groups for audit trail
        originalReconciliationGroups: Array.from(originalReconGroups.values()),

        allReconciledWithinPeriod: true, // Already validated above
        entryCount: groupEntries.length,
        taxRates: extractTaxRates(groupEntries),
        documents: groupEntries.map((e) => ({
          id: e.document_id || "",
          number: e.document?.nr || "",
          date: e.transaction_date,
        })),
      };

      consolidatedEntries.push(consolidated);
      consolidationMap.set(consolidationId, originalIds);

      // Build CSV data
      for (const entry of groupEntries) {
        csvData.push({
          consolidation_id: consolidationId,
          original_entry_id: entry.id,
          document_id: entry.document_id || "",
          document_number: entry.document?.nr || "",
          transaction_date: entry.transaction_date.toISOString().split("T")[0],
          debit_account: entry.debit_account?.account_nr || "",
          credit_account: entry.credit_account?.account_nr || "",
          amount: entry.amount,
          currency: entry.currency,
          reconciled_on:
            entry.debit_reconciliation_group?.reconciled_on ||
            entry.credit_reconciliation_group?.reconciled_on ||
            "",
          dimensions: JSON.stringify(entry.dimensions),
          consolidated_amount: consolidatedAmount,
          entries_in_group: groupEntries.length,
        });
      }
    }

    // 4. Persist if required (period close)
    if (isPersistent && consolidatedEntries.length > 0) {
      await persistConsolidation({
        consolidatedEntries,
        periodId,
        organizationId,
        actorId,
      });
    }

    const reductionPercentage =
      ((entries.length - consolidatedEntries.length) / entries.length) * 100;

    return {
      success: true,
      message: `Consolidated ${entries.length} entries into ${consolidatedEntries.length}`,
      data: {
        consolidatedEntries,
        consolidationMap,
        originalCount: entries.length,
        consolidatedCount: consolidatedEntries.length,
        reductionPercentage,
        csvData,
      },
    };
  } catch (error) {
    return {
      success: false,
      message: "Failed to consolidate GL entries",
      error: error instanceof Error ? error.message : "Unknown error",
    };
  }
}
```

### Sammelbeleg PDF Generation

#### What is a Sammelbeleg?

A **Sammelbeleg** (collective receipt/document) is a recognized German accounting practice where multiple similar transactions are documented on a single supporting document. This serves as the "Beleg" (source document) for the consolidated general ledger entry, maintaining the GoBD principle of **Belegprinzip** (document principle).

**Key Benefits:**

* ✅ Creates a proper source document (Beleg) for consolidated entries
* ✅ Maintains GoBD compliance through formal supporting documentation
* ✅ Provides complete audit trail in professional format
* ✅ Integrates with existing document management system
* ✅ Can be archived for 10+ years like any other receipt
* ✅ Printable for physical audits if needed

#### PDF Structure

```
┌─────────────────────────────────────────────────────────────┐
│       SAMMELBELEG / COLLECTIVE POSTING DOCUMENT             │
│                                                              │
│  Beleg-Nr: CONS-clh5z1234567890                             │
│  Datum: 31.01.2024                                           │
│  Zeitraum: 01.01.2024 - 31.01.2024                          │
│  Organisation: Example GmbH                                  │
│  Buchungskreis: 2024-01                                      │
│  Erstellt: 31.01.2024 15:30:00 UTC                          │
├─────────────────────────────────────────────────────────────┤
│  ZUSAMMENFASSUNG / SUMMARY                                  │
│  ──────────────────────────────────────────────────────     │
│  Konto (Soll):    1000 - Bank Account EUR                   │
│  Konto (Haben):   4000 - Sales Revenue                      │
│  Gesamtbetrag:    450,00 EUR                                │
│  Steuersatz:      19%                                        │
│  Anzahl Posten:   4 Einzelbuchungen                         │
├─────────────────────────────────────────────────────────────┤
│  EINZELBUCHUNGEN / INDIVIDUAL POSTINGS                      │
├───────┬──────────┬──────────┬──────────┬────────────────────┤
│ Pos.  │  Datum   │ Beleg-Nr │  Betrag  │ Dokument           │
├───────┼──────────┼──────────┼──────────┼────────────────────┤
│   1   │ 10.01.24 │ INV-001  │  100,00  │ Invoice #001       │
│   2   │ 15.01.24 │ INV-002  │  150,00  │ Invoice #002       │
│   3   │ 20.01.24 │ INV-003  │  200,00  │ Invoice #003       │
│   4   │ 25.01.24 │ INV-004  │   50,00  │ Invoice #004       │
├───────┴──────────┴──────────┼──────────┼────────────────────┤
│             GESAMT / TOTAL   │  450,00  │                    │
└──────────────────────────────┴──────────┴────────────────────┘

  ORIGINAL RECONCILIATION GROUPS
  ──────────────────────────────────────────────────────────
  • Group-A (REC-001): Reconciled 12.01.2024
  • Group-B (REC-002): Reconciled 18.01.2024
  • Group-C (REC-003): Reconciled 22.01.2024
  • Group-D (REC-004): Reconciled 28.01.2024

  DIMENSIONEN / DIMENSIONS
  ──────────────────────────────────────────────────────────
  • Kostenstelle: CC-001
  • Projekt: PRJ-2024-Q1
  • Abteilung: Vertrieb

  VERIFICATION / PRÜFUNG
  ──────────────────────────────────────────────────────────
  Generiert: 2024-01-31 15:30:00 UTC
  Erstellt von: Max Mustermann (max@example.com)
  Digitale Signatur: SHA256:abc123def456...

  Dieses Dokument dient als Belegnachweis für die
  konsolidierte Hauptbuchbuchung im Zeitraum Jan 2024.
  Alle Einzeltransaktionen bleiben im System nachvollziehbar.
```

*Note: Transaction dates vary from 2024-01-10 to 2024-01-25, demonstrating that entries from different dates are consolidated together in one Sammelbeleg.*

#### PDF Generation Function

```typescript theme={null}
// packages/core/src/general_ledger/generate-sammelbeleg-pdf.ts

import PDFDocument from "pdfkit";
import type { ConsolidatedEntry } from "./consolidate-entries";

interface GenerateSammelbelegParams {
  consolidatedEntry: ConsolidatedEntry;
  organizationName: string;
  periodName: string;
  createdBy: {
    name: string;
    email: string;
  };
  locale?: string; // 'de' or 'en', default 'de'
}

export async function generateSammelbelegPDF({
  consolidatedEntry,
  organizationName,
  periodName,
  createdBy,
  locale = "de",
}: GenerateSammelbelegParams): Promise<Buffer> {
  const doc = new PDFDocument({
    size: "A4",
    margin: 50,
    info: {
      Title: `Sammelbeleg ${consolidatedEntry.consolidatedBelegfeld1}`,
      Author: createdBy.name,
      Subject: "Collective Posting Document",
      Keywords: "sammelbeleg, consolidation, accounting",
    },
  });

  const chunks: Buffer[] = [];
  doc.on("data", (chunk) => chunks.push(chunk));

  // === HEADER ===
  doc.fontSize(16).font("Helvetica-Bold");
  doc.text("SAMMELBELEG / COLLECTIVE POSTING DOCUMENT", {
    align: "center",
  });
  doc.moveDown(1.5);

  // === METADATA SECTION ===
  doc.fontSize(10).font("Helvetica");
  const metadataStartY = doc.y;

  doc.text(`Beleg-Nr: ${consolidatedEntry.consolidatedBelegfeld1}`, 50, metadataStartY);
  doc.text(`Datum: ${formatDateGerman(consolidatedEntry.transactionDateRange.latest)}`);
  doc.text(
    `Zeitraum: ${formatDateGerman(consolidatedEntry.transactionDateRange.earliest)} - ${formatDateGerman(consolidatedEntry.transactionDateRange.latest)}`
  );
  doc.text(`Organisation: ${organizationName}`);
  doc.text(`Buchungskreis: ${periodName}`);
  doc.text(`Erstellt: ${new Date().toISOString().replace("T", " ").substring(0, 19)} UTC`);
  doc.moveDown(1.5);

  // Horizontal line
  doc.moveTo(50, doc.y).lineTo(550, doc.y).stroke();
  doc.moveDown(0.5);

  // === SUMMARY SECTION ===
  doc.fontSize(12).font("Helvetica-Bold");
  doc.text("ZUSAMMENFASSUNG / SUMMARY");
  doc.moveDown(0.5);

  doc.fontSize(10).font("Helvetica");
  doc.text(
    `Konto (Soll):    ${consolidatedEntry.debitAccount?.account_nr || "N/A"} - ${consolidatedEntry.debitAccount?.name || ""}`
  );
  doc.text(
    `Konto (Haben):   ${consolidatedEntry.creditAccount?.account_nr || "N/A"} - ${consolidatedEntry.creditAccount?.name || ""}`
  );
  doc.text(
    `Gesamtbetrag:    ${formatCurrency(consolidatedEntry.consolidatedAmount)} ${consolidatedEntry.currency}`
  );
  doc.text(
    `Steuersatz:      ${consolidatedEntry.taxRate !== null ? consolidatedEntry.taxRate + "%" : "N/A"}`
  );
  doc.text(`Anzahl Posten:   ${consolidatedEntry.entryCount} Einzelbuchungen`);
  doc.moveDown(1.5);

  // Horizontal line
  doc.moveTo(50, doc.y).lineTo(550, doc.y).stroke();
  doc.moveDown(0.5);

  // === INDIVIDUAL POSTINGS TABLE ===
  doc.fontSize(12).font("Helvetica-Bold");
  doc.text("EINZELBUCHUNGEN / INDIVIDUAL POSTINGS");
  doc.moveDown(0.5);

  // Table header
  const tableTop = doc.y;
  const col1 = 50; // Pos
  const col2 = 90; // Datum
  const col3 = 160; // Beleg-Nr
  const col4 = 270; // Betrag
  const col5 = 350; // Dokument

  doc.fontSize(9).font("Helvetica-Bold");
  doc.text("Pos.", col1, tableTop);
  doc.text("Datum", col2, tableTop);
  doc.text("Beleg-Nr", col3, tableTop);
  doc.text("Betrag", col4, tableTop);
  doc.text("Dokument", col5, tableTop);

  // Header underline
  doc
    .moveTo(col1, tableTop + 15)
    .lineTo(550, tableTop + 15)
    .stroke();

  // Table rows
  doc.font("Helvetica");
  let rowY = tableTop + 20;

  for (let i = 0; i < consolidatedEntry.documents.length; i++) {
    const docInfo = consolidatedEntry.documents[i];

    // Check if we need a new page
    if (rowY > 700) {
      doc.addPage();
      rowY = 50;
    }

    doc.text((i + 1).toString(), col1, rowY);
    doc.text(formatDateGerman(docInfo.date), col2, rowY);
    doc.text(docInfo.number, col3, rowY);
    doc.text(formatCurrency(docInfo.amount || 0), col4, rowY);
    doc.text(docInfo.description || "-", col5, rowY, { width: 180 });

    rowY += 20;
  }

  // Total line
  doc.moveTo(col1, rowY).lineTo(550, rowY).stroke();
  rowY += 10;

  doc.font("Helvetica-Bold");
  doc.text("GESAMT / TOTAL", col3, rowY);
  doc.text(formatCurrency(consolidatedEntry.consolidatedAmount), col4, rowY);

  doc.moveDown(2);

  // === RECONCILIATION GROUPS ===
  if (consolidatedEntry.originalReconciliationGroups.length > 0) {
    doc.fontSize(10).font("Helvetica-Bold");
    doc.text("ORIGINAL RECONCILIATION GROUPS");
    doc.moveDown(0.3);

    doc.font("Helvetica");
    for (const group of consolidatedEntry.originalReconciliationGroups) {
      doc.text(
        `• ${group.reconciledOn || "N/A"}: Reconciled ${formatDateTimeGerman(group.reconciledAt)}`
      );
    }
    doc.moveDown(1);
  }

  // === DIMENSIONS ===
  if (consolidatedEntry.dimensions && Object.keys(consolidatedEntry.dimensions).length > 0) {
    doc.fontSize(10).font("Helvetica-Bold");
    doc.text("DIMENSIONEN / DIMENSIONS");
    doc.moveDown(0.3);

    doc.font("Helvetica");
    for (const [key, value] of Object.entries(consolidatedEntry.dimensions)) {
      doc.text(`• ${key}: ${value}`);
    }
    doc.moveDown(1);
  }

  // === VERIFICATION ===
  doc.fontSize(9).font("Helvetica-Bold");
  doc.text("VERIFICATION / PRÜFUNG");
  doc.moveDown(0.3);

  doc.font("Helvetica");
  doc.text(`Generiert: ${new Date().toISOString()}`);
  doc.text(`Erstellt von: ${createdBy.name} (${createdBy.email})`);
  doc.text(`Digitale Signatur: SHA256:${generateSignature(consolidatedEntry)}`);
  doc.moveDown(1);

  // === FOOTER ===
  doc.fontSize(7).font("Helvetica");
  doc.text(
    "Dieses Dokument dient als Belegnachweis für die konsolidierte Hauptbuchbuchung. " +
      "Alle Einzeltransaktionen bleiben im System nachvollziehbar und können jederzeit " +
      "eingesehen werden. Dieses Sammelbeleg erfüllt die Anforderungen der GoBD.",
    50,
    doc.y,
    {
      align: "center",
      width: 500,
    }
  );

  doc.end();

  return new Promise((resolve) => {
    doc.on("end", () => resolve(Buffer.concat(chunks)));
  });
}

// Helper functions
function formatDateGerman(date: Date | null): string {
  if (!date) return "N/A";
  return new Intl.DateTimeFormat("de-DE", {
    day: "2-digit",
    month: "2-digit",
    year: "numeric",
  }).format(new Date(date));
}

function formatDateTimeGerman(date: Date | null): string {
  if (!date) return "N/A";
  return new Intl.DateTimeFormat("de-DE", {
    day: "2-digit",
    month: "2-digit",
    year: "numeric",
    hour: "2-digit",
    minute: "2-digit",
  }).format(new Date(date));
}

function formatCurrency(amount: number): string {
  return new Intl.NumberFormat("de-DE", {
    minimumFractionDigits: 2,
    maximumFractionDigits: 2,
  }).format(amount);
}

function generateSignature(entry: ConsolidatedEntry): string {
  // Generate SHA256 hash of consolidation data for verification
  const crypto = require("crypto");
  const data = JSON.stringify({
    id: entry.consolidationId,
    amount: entry.consolidatedAmount,
    entries: entry.originalEntryIds,
  });
  return crypto.createHash("sha256").update(data).digest("hex").substring(0, 16);
}
```

### Sammelbeleg Document Creation and Storage

```typescript theme={null}
// packages/core/src/general_ledger/create-sammelbeleg-document.ts

interface CreateSammelbelegDocumentParams {
  consolidatedEntry: ConsolidatedEntry;
  organizationId: string;
  periodId: string;
  pdfBuffer: Buffer;
  actorId?: string;
}

export async function createSammelbelegDocument({
  consolidatedEntry,
  organizationId,
  periodId,
  pdfBuffer,
  actorId,
}: CreateSammelbelegDocumentParams): Promise<ActionResponse<documents>> {
  try {
    // 1. Upload PDF to storage (UploadThing, S3, or local storage)
    const pdfUrl = await uploadSammelbelegPDF({
      file: pdfBuffer,
      filename: `${consolidatedEntry.consolidatedBelegfeld1}.pdf`,
      organizationId,
      periodId,
    });

    // 2. Create document record for the Sammelbeleg
    const sammelbelegDocument = await prisma.documents.create({
      data: {
        id: createId(),
        org_id: organizationId,
        nr: consolidatedEntry.consolidatedBelegfeld1, // "CONS-clh5z1234..."
        object_type_id: "SAMMELBELEG", // Dedicated object type for consolidation documents
        date: consolidatedEntry.transactionDateRange.latest,
        accounting_period_id: periodId,
        modified_by_actor_id: actorId,
        custom_properties: {
          cona_sammelbeleg: {
            label: "Sammelbeleg Information",
            value: {
              type: "sammelbeleg",
              consolidation_id: consolidatedEntry.consolidationId,
              entry_count: consolidatedEntry.entryCount,
              date_range: {
                start: consolidatedEntry.transactionDateRange.earliest.toISOString(),
                end: consolidatedEntry.transactionDateRange.latest.toISOString(),
              },
              consolidated_amount: consolidatedEntry.consolidatedAmount,
              currency: consolidatedEntry.currency,
              tax_rate: consolidatedEntry.taxRate,
              original_documents: consolidatedEntry.documents.map((d) => ({
                id: d.id,
                number: d.number,
                date: d.date.toISOString(),
              })),
              original_entry_ids: consolidatedEntry.originalEntryIds,
              reconciliation_groups: consolidatedEntry.originalReconciliationGroups.map(
                (g) => g.id
              ),
            },
          },
        },
        // Additional metadata
        description: `Sammelbeleg for ${consolidatedEntry.entryCount} consolidated entries`,
      },
    });

    return {
      success: true,
      message: "Sammelbeleg document created",
      data: sammelbelegDocument,
    };
  } catch (error) {
    return {
      success: false,
      message: "Failed to create Sammelbeleg document",
      error: error instanceof Error ? error.message : "Unknown error",
    };
  }
}

// Helper function to upload PDF
async function uploadSammelbelegPDF({
  file,
  filename,
  organizationId,
  periodId,
}: {
  file: Buffer;
  filename: string;
  organizationId: string;
  periodId: string;
}): Promise<string> {
  // Implementation depends on your storage solution:
  // - UploadThing
  // - AWS S3
  // - Local file system
  // - Supabase Storage

  // Example with S3:
  const key = `organizations/${organizationId}/periods/${periodId}/sammelbeleg/${filename}`;
  await s3Client.putObject({
    Bucket: process.env.S3_BUCKET,
    Key: key,
    Body: file,
    ContentType: "application/pdf",
    Metadata: {
      organizationId,
      periodId,
      documentType: "sammelbeleg",
    },
  });

  return `https://${process.env.S3_BUCKET}.s3.amazonaws.com/${key}`;
}
```

### Document Relationships & Traceability

#### Document Storage Without document\_relations (Recommended)

**Design Decision:** Do NOT use `document_relations` for Sammelbeleg ↔ Original Documents.

**Rationale:**

1. **Redundant** - GL entries already have `sammelbeleg_document_id` (source of truth)
2. **Derivable** - Can query related documents via GL entries
3. **Simpler** - One less relationship to maintain and keep in sync
4. **Avoids Confusion** - One document can have GL entries in multiple Sammelbelegs (edge case complexity)
5. **Cleaner Queries** - Direct GL-level queries are more accurate

**The Single Source of Truth:** `general_ledger.sammelbeleg_document_id`

```typescript theme={null}
// packages/core/src/general_ledger/create-sammelbeleg-document.ts

export async function createSammelbelegDocument({
  consolidatedEntry,
  organizationId,
  periodId,
  pdfBuffer,
  actorId,
}: CreateSammelbelegDocumentParams): Promise<ActionResponse<documents>> {
  try {
    // 1. Upload PDF and create Sammelbeleg document (as shown above)
    const pdfUrl = await uploadSammelbelegPDF({
      file: pdfBuffer,
      filename: `${consolidatedEntry.consolidatedBelegfeld1}.pdf`,
      organizationId,
      periodId,
    });

    const sammelbelegDoc = await prisma.documents.create({
      data: {
        id: createId(),
        org_id: organizationId,
        nr: consolidatedEntry.consolidatedBelegfeld1,
        object_type_id: "SAMMELBELEG",
        date: consolidatedEntry.transactionDateRange.latest,
        accounting_period_id: periodId,
        modified_by_actor_id: actorId,
        custom_properties: {
          cona_sammelbeleg: {
            label: "Sammelbeleg Information",
            value: {
              type: "sammelbeleg",
              consolidation_id: consolidatedEntry.consolidationId,
              entry_count: consolidatedEntry.entryCount,
              date_range: {
                start: consolidatedEntry.transactionDateRange.earliest.toISOString(),
                end: consolidatedEntry.transactionDateRange.latest.toISOString(),
              },
              consolidated_amount: consolidatedEntry.consolidatedAmount,
              currency: consolidatedEntry.currency,
              tax_rate: consolidatedEntry.taxRate,
              // Store GL entry details for traceability
              gl_entries: consolidatedEntry.originalEntryIds.map((glId) => {
                const gl = allGlEntries.find((g) => g.id === glId);
                return {
                  gl_entry_id: glId,
                  document_id: gl.document_id,
                  document_nr: gl.document?.nr,
                  amount: gl.amount,
                  transaction_date: gl.transaction_date,
                };
              }),
            },
          },
        },
        description: `Sammelbeleg for ${consolidatedEntry.entryCount} consolidated entries`,
      },
    });

    // 2. Create attachment for the PDF
    await prisma.attachments.create({
      data: {
        id: createId(),
        org_id: organizationId,
        documents_id: sammelbelegDoc.id,
        file_name: `${consolidatedEntry.consolidatedBelegfeld1}.pdf`,
        file_url: pdfUrl,
        file_type: "application/pdf",
        file_size: pdfBuffer.length,
        role: "sammelbeleg_pdf", // Special role for easy identification
        modified_by_actor_id: actorId,
      },
    });

    // 3. Update GL entries to reference the Sammelbeleg document
    // This is the SINGLE SOURCE OF TRUTH for the relationship
    await prisma.general_ledger.updateMany({
      where: {
        id: { in: consolidatedEntry.originalEntryIds },
      },
      data: {
        sammelbeleg_document_id: sammelbelegDoc.id,
        consolidation_id: consolidatedEntry.consolidationId,
        is_consolidated: true,
        consolidation_metadata: {
          sammelbeleg_nr: consolidatedEntry.consolidatedBelegfeld1,
          sammelbeleg_document_id: sammelbelegDoc.id,
          consolidated_at: new Date().toISOString(),
        },
      },
    });

    return {
      success: true,
      message: "Sammelbeleg document created",
      data: sammelbelegDoc,
    };
  } catch (error) {
    return {
      success: false,
      message: "Failed to create Sammelbeleg document",
      error: error instanceof Error ? error.message : "Unknown error",
    };
  }
}
```

#### Querying Sammelbeleg and Related Documents

**Query via GL entries (single source of truth):**

```typescript theme={null}
// Get Sammelbeleg with all related original documents (via GL entries)
const sammelbeleg = await prisma.documents.findUnique({
  where: { id: sammelbelegId },
  include: {
    attachments: {
      where: { role: "sammelbeleg_pdf" },
    },
  },
});

// Get related GL entries and their documents
const relatedGlEntries = await prisma.general_ledger.findMany({
  where: {
    sammelbeleg_document_id: sammelbelegId,
  },
  include: {
    document: {
      include: {
        entity: true, // Customer/supplier
      },
    },
    debit_account: true,
    credit_account: true,
  },
});

// Extract unique original documents
const originalDocuments = [...new Set(relatedGlEntries.map((gl) => gl.document))].filter(Boolean);

// Get original document and check if any GL entries are in a Sammelbeleg
const originalDoc = await prisma.documents.findUnique({
  where: { id: originalDocId },
  include: {
    general_ledger: {
      include: {
        sammelbeleg_document: {
          select: {
            id: true,
            nr: true,
            date: true,
          },
        },
      },
    },
  },
});

// Group GL entries by Sammelbeleg
const glBySammelbeleg = originalDoc.general_ledger.reduce((acc, gl) => {
  const sammelbelegId = gl.sammelbeleg_document_id;
  if (sammelbelegId) {
    if (!acc[sammelbelegId]) {
      acc[sammelbelegId] = {
        sammelbeleg: gl.sammelbeleg_document,
        gl_entries: [],
      };
    }
    acc[sammelbelegId].gl_entries.push(gl);
  }
  return acc;
}, {});
```

#### Document Hierarchy Visualization (Simplified)

```
┌────────────────────────────────────────────────────────────┐
│              documents (Sammelbeleg)                       │
│  id: "sammel-123"                                          │
│  object_type_id: "SAMMELBELEG"  ← Distinct type           │
│  nr: "CONS-clh5z1234..."                                   │
│  custom_properties: {                                      │
│    cona_sammelbeleg: {                                     │
│      gl_entries: [...],  ← Stores GL entry details        │
│      consolidation_id: "...",                              │
│      ...                                                   │
│    }                                                       │
│  }                                                         │
└──────────────────────┬─────────────────────────────────────┘
                       │
                       │ (has attachment)
                       ▼
              ┌──────────────────┐
              │   attachments    │
              │ role: "sammelbeleg_pdf" │
              │ file_url: PDF    │
              └──────────────────┘

┌───────────────────────────────────────────────────────────┐
│            general_ledger (GL Entries)                    │
│                                                           │
│  GL Entry 1:                                              │
│    id: "gl-1"                                             │
│    document_id: "inv-1"                                   │
│    sammelbeleg_document_id: "sammel-123"  ← Single source │
│    amount: 100                                            │
│    ...                                                    │
│                                                           │
│  GL Entry 2:                                              │
│    id: "gl-2"                                             │
│    document_id: "inv-2"                                   │
│    sammelbeleg_document_id: "sammel-123"  ← Single source │
│    amount: 150                                            │
│    ...                                                    │
│                                                           │
│  GL Entry 3:                                              │
│    id: "gl-3"                                             │
│    document_id: "inv-3"                                   │
│    sammelbeleg_document_id: "sammel-123"  ← Single source │
│    amount: 200                                            │
│    ...                                                    │
└─────┬──────────┬──────────┬─────────────────────────────┘
      │          │          │
      ▼          ▼          ▼
┌──────────┐ ┌──────────┐ ┌──────────┐
│documents │ │documents │ │documents │
│id: inv-1 │ │id: inv-2 │ │id: inv-3 │
│object_   │ │object_   │ │object_   │
│type: SALES│ │type: SALES│ │type: SALES│
└──────────┘ └──────────┘ └──────────┘

Key Points:
✅ GL entries are the SINGLE SOURCE OF TRUTH
✅ No document_relations table needed
✅ Query original documents via GL entries
✅ Simpler architecture, less maintenance
```

#### Why Not Use document\_relations?

**Question:** Should we create parent-child relationships between Sammelbeleg and original documents?

**Answer:** No, for these reasons:

| Issue                | Without document\_relations                            | With document\_relations                                  |
| -------------------- | ------------------------------------------------------ | --------------------------------------------------------- |
| **Source of Truth**  | ✅ Single: `general_ledger.sammelbeleg_document_id`     | ❌ Dual: GL entries + document\_relations                  |
| **Edge Case**        | ✅ Naturally handles documents in multiple Sammelbelegs | ⚠️ Confusing: one document → multiple parent Sammelbelegs |
| **Maintenance**      | ✅ One update: mark GL entries                          | ❌ Two updates: GL entries + document relations            |
| **Query Complexity** | ✅ Direct GL queries                                    | ⚠️ Need to sync two sources                               |
| **Accuracy**         | ✅ GL-level precision                                   | ⚠️ Document-level approximation                           |
| **Sync Issues**      | ✅ Cannot drift                                         | ❌ Can get out of sync                                     |

**Edge Case Example:**

```typescript theme={null}
// Document INV-001 has two GL entries
GL Entry 1: 1000 → 4000, Tax: 19%, Project: A → Sammelbeleg-1
GL Entry 2: 1000 → 4100, Tax:  7%, Project: B → Sammelbeleg-2

// With document_relations:
document_relations:
  - Sammelbeleg-1 → INV-001  (because of GL Entry 1)
  - Sammelbeleg-2 → INV-001  (because of GL Entry 2)

// Problem: UI shows "INV-001 is in 2 Sammelbelegs?"
// Confusing! Which entries? How much from each?

// Without document_relations (query GL directly):
SELECT * FROM general_ledger WHERE document_id = 'inv-001'
// Shows exactly which GL entries → which Sammelbeleg
// Clear and precise!
```

**Benefits of GL-Only Approach:**

1. **Precision** - Track at GL entry level, not document level
2. **Simplicity** - One relationship to maintain
3. **Correctness** - Always accurate, cannot drift
4. **Clarity** - UI can show exact GL entries per Sammelbeleg
5. **Storage** - GL entry details already in Sammelbeleg's `custom_properties`

### DATEV Export Integration

#### Updated Export Flow

```typescript theme={null}
// apps/webapp/app/lib/utils/export-datev.ts

export async function exportDatevCSV({
  settings,
  periodId,
  exportType = "test", // 'test' | 'period_close'
}: DatevTransformOptions) {
  const organizationId = await getOrganizationId();

  // 1. Get journal entries for the period
  const entriesResult = await getJournalEntriesByPeriod({ periodId });

  if (!entriesResult.success || !entriesResult.data) {
    throw new Error("Failed to fetch journal entries");
  }

  let entriesToExport = entriesResult.data;
  let sammelbelegPdfs: Array<{ filename: string; buffer: Buffer }> = [];

  // 2. Consolidate if enabled
  if (settings.datevSettings.aggregateJournalEntries) {
    const consolidationResult = await consolidateGLEntries({
      periodId,
      organizationId,
      actorId: await getActorId(),
      isPersistent: exportType === "period_close",
    });

    if (consolidationResult.success && consolidationResult.data) {
      const { consolidatedEntries } = consolidationResult.data;

      // Transform consolidated entries to DATEV format
      entriesToExport = transformConsolidatedEntriesToDatevFormat(consolidatedEntries, settings);

      // Generate Sammelbeleg PDFs for each consolidated entry
      const actorInfo = await getActorInfo(await getActorId());
      const orgName = await getOrganizationName(organizationId);
      const periodName = await getPeriodName(periodId);

      for (const consolidated of consolidatedEntries) {
        const pdfBuffer = await generateSammelbelegPDF({
          consolidatedEntry: consolidated,
          organizationName: orgName,
          periodName,
          createdBy: {
            name: actorInfo.name,
            email: actorInfo.email,
          },
        });

        sammelbelegPdfs.push({
          filename: `${consolidated.consolidatedBelegfeld1}.pdf`,
          buffer: pdfBuffer,
        });

        // For persistent consolidation (period close), store PDFs as documents
        if (exportType === "period_close") {
          const docResult = await createSammelbelegDocument({
            consolidatedEntry: consolidated,
            organizationId,
            periodId,
            pdfBuffer,
            actorId: await getActorId(),
          });

          // Update GL entries to reference the Sammelbeleg document
          if (docResult.success && docResult.data) {
            await prisma.general_ledger.updateMany({
              where: {
                id: { in: consolidated.originalEntryIds },
              },
              data: {
                sammelbeleg_document_id: docResult.data.id,
              },
            });
          }
        }
      }
    }
  }

  // 3. Generate DATEV CSV (existing logic)
  const datevCSV = await generateDatevCSV({
    entries: entriesToExport,
    settings,
    periodId,
    // ... other params
  });

  // 4. Return DATEV CSV and Sammelbeleg PDFs
  return {
    datevCSV,
    sammelbelegPdfs, // Array of PDF buffers with filenames
  };
}

/**
 * Transform consolidated entries to DATEV format
 * Each consolidated entry becomes ONE DATEV row with new Belegfeld 1
 */
function transformConsolidatedEntriesToDatevFormat(
  consolidatedEntries: ConsolidatedEntry[],
  settings: DATEVExportSettings
): DatevExportRow[] {
  const datevRows: DatevExportRow[] = [];

  for (const consolidated of consolidatedEntries) {
    datevRows.push({
      Umsatz: formatAmountToDatevFormat(consolidated.consolidatedAmount),
      "Soll-/Haben-Kennzeichen": consolidated.consolidatedAmount > 0 ? "S" : "H",
      "WKZ Umsatz": consolidated.currency,
      Konto: consolidated.debitAccount.account_nr,
      "Gegenkonto (ohne BU-Schlüssel)": consolidated.creditAccount.account_nr,
      "BU-Schlüssel": getMappedGLDimensionValue(
        consolidated.dimensions,
        settings.dimensionMappings,
        "BU"
      ),
      Belegdatum: formatDateTTMM(consolidated.transactionDateRange.latest), // Use latest date
      "Belegfeld 1": consolidated.consolidatedBelegfeld1, // NEW: CONS-{cuid} (e.g., "CONS-clh5z1234567890abcdef")
      Buchungstext: `Consolidated entry (${consolidated.entryCount} entries, ${consolidated.originalReconciliationGroups.length} recon groups)`,
      // ... other DATEV fields
      Festschreibung: "0",
    });
  }

  return datevRows;
}
```

### ZIP Download Implementation

```typescript theme={null}
// apps/webapp/app/lib/actions/datev/export-datev-csv.ts

import JSZip from "jszip";

export async function exportDatevCsvAction({
  settings,
  periodId,
  exportType = "test",
}: ExportDatevCsvParams): Promise<ActionResponse<{ base64: string; isZip: boolean }>> {
  await requireAuth();

  try {
    const { datevCSV, sammelbelegPdfs } = await exportDatevCSV({
      settings,
      periodId,
      exportType,
    });

    // If consolidation is enabled and we have Sammelbeleg PDFs
    if (settings.datevSettings.aggregateJournalEntries && sammelbelegPdfs.length > 0) {
      // Create ZIP file
      const zip = new JSZip();

      // Add DATEV export
      zip.file("EXTF_CONA_datev_export.csv", datevCSV, {
        binary: true,
      });

      // Add Sammelbeleg PDFs
      for (const pdf of sammelbelegPdfs) {
        zip.file(`sammelbeleg/${pdf.filename}`, pdf.buffer, {
          binary: true,
        });
      }

      // Add README explaining the Sammelbeleg documents
      const readme = generateSammelbelegReadme(sammelbelegPdfs.length);
      zip.file("README_SAMMELBELEG.txt", readme);

      // Generate ZIP
      const zipBuffer = await zip.generateAsync({
        type: "nodebuffer",
        compression: "DEFLATE",
        compressionOptions: { level: 9 },
      });

      const base64 = zipBuffer.toString("base64");

      return {
        success: true,
        message: `DATEV export with ${sammelbelegPdfs.length} Sammelbeleg documents generated`,
        data: { base64, isZip: true },
      };
    } else {
      // Single CSV file (no consolidation)
      const base64 = Buffer.from(datevCSV, "latin1").toString("base64");

      return {
        success: true,
        message: "DATEV CSV generated",
        data: { base64, isZip: false },
      };
    }
  } catch (error) {
    return {
      success: false,
      message: "Failed to generate DATEV export",
      error: error instanceof Error ? error.message : "Unknown error",
    };
  }
}

function generateSammelbelegReadme(pdfCount: number): string {
  return `
SAMMELBELEG (COLLECTIVE RECEIPT) DOCUMENTS
==========================================

This ZIP file contains ${pdfCount} Sammelbeleg PDF document(s) that serve as
supporting documentation for consolidated general ledger entries in the
DATEV export.

What is a Sammelbeleg?
----------------------
A Sammelbeleg (collective receipt) is a recognized German accounting practice
where multiple similar transactions are documented on a single supporting
document. This maintains the GoBD principle of "Belegprinzip" (document principle).

Structure:
----------
- EXTF_CONA_datev_export.csv: Main DATEV export with consolidated entries
- sammelbeleg/*.pdf: Individual Sammelbeleg PDFs (one per consolidation)

Each Sammelbeleg PDF contains:
- Summary of the consolidated entry
- Complete list of all individual transactions
- Original document references and dates
- Reconciliation information
- Digital signature for verification

GoBD Compliance:
----------------
The Sammelbeleg PDFs provide the complete audit trail required by GoBD:
- Einzelaufzeichnungspflicht: All individual transactions are documented
- Belegprinzip: Each consolidated entry has a proper source document (Beleg)
- Nachvollziehbarkeit: Full traceability from DATEV entry to original documents
- Unveränderbarkeit: PDFs are immutable and digitally signed

For period close exports, these PDFs are also stored in the document management
system and can be accessed at any time.

Generated: ${new Date().toISOString()}
`;
}
```

### UI Changes

#### Enable Aggregation Toggle

```typescript theme={null}
// apps/webapp/app/ui/modals/datev-export-settings-dialog.tsx

<FormField
  control={form.control}
  name="datevSettings.aggregateJournalEntries"
  render={({ field }) => (
    <FormItem className="flex flex-row items-start space-x-3 space-y-0">
      <FormControl>
        <Switch
          checked={field.value}
          onCheckedChange={field.onChange}
          // Remove disabled prop to enable toggle
        />
      </FormControl>
      <div className="space-y-1 leading-none">
        <FormLabel>Aggregate Journal Entries</FormLabel>
        <FormDescription>
          Consolidate entries with matching accounts and dimensions (across dates).
          This reduces the number of lines in the DATEV export while maintaining GoBD compliance.
          <br /><br />
          For each consolidated entry, a <strong>Sammelbeleg (Collective Receipt) PDF</strong> is
          generated containing all individual transaction details. These PDFs serve as proper
          source documents (Beleg) and are included in the export ZIP file.
          <br /><br />
          For period close exports, Sammelbeleg PDFs are also stored in your document
          management system for long-term archival.
        </FormDescription>
      </div>
    </FormItem>
  )}
/>
```

#### Update Download Handler

```typescript theme={null}
// apps/webapp/app/ui/cards/datev-export-main-card.tsx

const handleDownloadDatevFileBase64 = (base64: string, isZip: boolean) => {
  const link = document.createElement("a");

  if (isZip) {
    link.href = `data:application/zip;base64,${base64}`;
    const timestamp = new Date().toISOString().split("T")[0];
    const companyPart = organizationSlug ? `_${organizationSlug}` : "";
    const periodPart = accountingPeriodName
      ? `_${accountingPeriodName.replace(/[^a-zA-Z0-9]/g, "_")}`
      : "";
    link.download = `DATEV_Export${companyPart}${periodPart}_${timestamp}.zip`;
  } else {
    link.href = `data:text/csv;charset=iso-8859-1;base64,${base64}`;
    const timestamp = new Date().toISOString().split("T")[0];
    const companyPart = organizationSlug ? `_${organizationSlug}` : "";
    const periodPart = accountingPeriodName
      ? `_${accountingPeriodName.replace(/[^a-zA-Z0-9]/g, "_")}`
      : "";
    link.download = `EXTF_CONA${companyPart}${periodPart}_${timestamp}.csv`;
  }

  document.body.appendChild(link);
  link.click();
  document.body.removeChild(link);
};

const handleTestFileExport = async () => {
  setIsExportingTestFile(true);
  try {
    const result = await exportDatevCsvAction({
      settings: form.getValues(),
      periodId: selectedPeriod,
      exportType: "test", // Virtual consolidation
    });
    if (!result.success || !result.data) throw new Error(result.error || result.message);
    handleDownloadDatevFileBase64(result.data.base64, result.data.isZip);

    if (result.data.isZip) {
      toast.success("DATEV export with Sammelbeleg documents downloaded");
    } else {
      toast.success("DATEV file exported successfully");
    }
  } catch (error) {
    toast.error(error instanceof Error ? error.message : "Failed to export DATEV file");
  } finally {
    setIsExportingTestFile(false);
  }
};

const handleClosePeriodExport = async () => {
  setIsExportingClosePeriod(true);
  try {
    // ... existing reconciliation logic ...

    const result = await exportDatevCsvAction({
      settings: form.getValues(),
      periodId: selectedPeriod,
      exportType: "period_close", // Persistent consolidation
    });

    if (!result.success || !result.data) {
      throw new Error(result.error || result.message);
    }

    // Only close the period after successful export
    const closeResult = await closeAccountingPeriod({
      periodId: selectedPeriod,
    });

    if (!closeResult.success || !closeResult.data) {
      throw new Error(closeResult.error || closeResult.message);
    }

    handleDownloadDatevFileBase64(result.data.base64, result.data.isZip);

    if (result.data.isZip) {
      toast.success("Period closed and exported with Sammelbeleg documents");
    } else {
      toast.success("Period closed and exported successfully");
    }
  } catch (error) {
    toast.error(error instanceof Error ? error.message : "Failed to close period and export");
  } finally {
    setIsExportingClosePeriod(false);
  }
};
```

## Implementation Plan

### Phase 1: Virtual Consolidation (Non-Persistent) ✅ **COMPLETE**

**Status:** ✅ Fully implemented and ready for testing

**What was delivered:**

1. ✅ Core consolidation logic (`@cona/core/general_ledger/consolidate-entries.ts`)
   * Advanced reconciliation handling with union signatures
   * Cross-period validation
   * Detailed diagnostics

2. ✅ Sammelbeleg PDF generation (`apps/webapp/app/lib/utils/generate-sammelbeleg-pdf.tsx`)
   * Using `pdf-lib` (Next.js compatible)
   * Professional German formatting
   * Complete audit trail

3. ✅ DATEV export integration
   * Consolidation before export
   * ZIP generation with Sammelbeleg PDFs
   * README documentation

4. ✅ UI implementation
   * Aggregation toggle enabled (BETA)
   * ZIP download support
   * Success messages

5. ✅ Unit tests
   * Consolidation logic tests
   * PDF generation tests

**Dependencies installed:**

```bash theme={null}
# Core package
@paralleldrive/cuid2@^2.2.2  # Installed in @cona/core

# Webapp package
pdf-lib@^1.17.1              # Installed in webapp (better than pdfkit for Next.js)
jszip@3.10.1                 # Already present
```

**Key Differences from Plan:**

* Uses `pdf-lib` instead of `pdfkit` (better Next.js compatibility)
* Enhanced reconciliation logic beyond original spec
* Multiple consolidated entries can share belegfeld1 (optimized PDF generation)

**See:** [Phase 1 Implementation Details](./gl-entry-consolidation-phase1-virtual.mdx)

***

### Phase 2: Persistent Consolidation (Period Close) ⏳ **PENDING**

**Status:** Not yet started (awaiting Phase 1 validation)

**Will include:**

1. Database migrations for consolidation tracking
2. `SAMMELBELEG` object\_type creation
3. Document storage implementation
4. GL entry updates with consolidation markers
5. Consolidation audit logs

**Dependencies:**

```bash theme={null}
# Database migrations
pnpm --filter=@cona/database migrate:dev
```

***

### Phase 3: Testing & Validation ⏳ **IN PROGRESS**

**Current tasks:**

1. ⏳ Test with real production-like data
2. ⏳ Verify 80-90% reduction in export size
3. ⏳ Validate PDF content and formatting
4. ⏳ Performance testing with large datasets
5. ⏳ Deploy to staging
6. ⏳ Code review and approval
7. ⏳ Monitor logs for errors
8. ⏳ User acceptance testing
9. ⏳ **Consultation with Steuerberater** for GoBD compliance confirmation

**Expected deliverables:**

* Test results with real data
* Performance benchmarks
* User feedback
* Professional GoBD compliance confirmation
* Decision to proceed to Phase 2

***

### Phase 4: Production Rollout ⏳ **FUTURE**

**After successful testing:**

1. Enable feature flag for production
2. Internal rollout first
3. Gradual rollout to customers
4. Monitor performance and errors
5. Gather user feedback
6. Iterate based on feedback

## Technical Considerations

### Performance

**Estimated Performance:**

* Consolidation for 10,000 entries: \~2-3 seconds
* DATEV CSV generation: \~1 second
* Sammelbeleg PDF generation: \~200-500ms per PDF (depends on entry count)
* ZIP creation with multiple PDFs: \~1-2 seconds

**Example Scenario:**

* 10,000 original entries → 50 consolidated entries
* 50 Sammelbeleg PDFs × 300ms = \~15 seconds for PDF generation
* Total export time: \~20 seconds (acceptable for period close)

**Optimization strategies:**

1. Index on consolidation key fields (accounts + tax\_rate + dimensions, NOT date or amount sign)
2. Batch processing for document updates
3. **Parallel PDF generation** (generate multiple Sammelbeleg PDFs concurrently)
4. Stream-based ZIP creation for large exports
5. Consider PDF generation queue for very large exports (100+ consolidated entries)

### Data Integrity

**Safety Measures:**

1. Transaction-based consolidation for persistent mode
2. Audit trail in `consolidation_logs`
3. Reversibility via `consolidated_from_ids`
4. Reconciliation validation before consolidation
5. **Sammelbeleg PDFs stored as immutable documents** (proper Belege)
6. **GL entries reference Sammelbeleg documents** via `sammelbeleg_document_id`
7. **Digital signatures** on PDFs for verification

### Edge Cases

1. **Single Entry Groups:** Don't consolidate, export as-is
2. **Missing Dimensions:** Treat as empty object, group separately
3. **Multiple Currencies:** Never consolidate across currencies
4. **Different Tax Rates:** Never consolidate entries with different tax rates (entries are grouped by tax rate)
5. **Null Tax Rates:** Entries with null tax\_rate consolidate separately from entries with tax rates
6. **Algebraic Summation:** Positive and negative amounts consolidate together and sum algebraically (e.g., 100 + (-50) = 50)
7. **Zero Amount Results:** If consolidated entries sum to zero, still create a consolidated entry (represents offsetting transactions)
8. **Deleted Entries:** Exclude from consolidation
9. **Date Range:** Store earliest and latest transaction dates in metadata for reference
10. **Belegdatum:** Use the latest transaction date from the consolidated group
11. **Reconciliation Completeness:** Skip consolidation if any reconciliation group is NOT complete within the period
12. **Partial Reconciliation:** Entries with incomplete reconciliation are exported individually, not consolidated

### Rollback Strategy

**Virtual Consolidation:** No rollback needed (non-persistent)

**Persistent Consolidation:**

1. Keep original entries (mark as consolidated, don't delete)
2. Store mapping in `consolidated_from_ids`
3. Implement `reverseConsolidation` function if needed

```typescript theme={null}
async function reverseConsolidation(consolidationId: string) {
  await prisma.$transaction(async (tx) => {
    // 1. Find all original entries
    const entries = await tx.general_ledger.findMany({
      where: { consolidation_id: consolidationId },
    });

    // 2. Clear consolidation markers
    await tx.general_ledger.updateMany({
      where: { consolidation_id: consolidationId },
      data: {
        consolidation_id: null,
        is_consolidated: false,
        consolidation_metadata: null,
      },
    });

    // 3. Log reversal
    await tx.consolidation_logs.create({
      data: {
        /* reversal log */
      },
    });
  });
}
```

## Success Metrics

1. **Consolidation Ratio:** Target 60-90% reduction in entry count
   * Note: Ratio depends on reconciliation completeness within period
   * Entries with incomplete reconciliation exported individually
2. **Export Time:** \< 5 seconds for 10,000 entries
3. **Data Integrity:** 100% reconciliation preservation
   * Each consolidated entry is fully reconcilable as a unit
   * All original reconciliation groups complete within period
4. **User Adoption:** > 50% of period closes use consolidation
5. **Error Rate:** \< 0.1% failed consolidations
6. **Reconciliation Integrity:** 0% partial consolidations (all-or-nothing approach)

## Security & Compliance

### GoBD Compliance with Sammelbeleg Approach

**Key GoBD Principles Maintained:**

1. **Einzelaufzeichnungspflicht (Individual Recording):**
   * ✅ All individual transactions documented in Sammelbeleg PDF
   * ✅ Original dates, amounts, and document references preserved
   * ✅ No loss of transaction-level detail

2. **Belegprinzip (Document Principle):**
   * ✅ Sammelbeleg PDF serves as proper source document (Beleg)
   * ✅ DATEV Belegfeld 1 references the Sammelbeleg document
   * ✅ Follows recognized German accounting practice

3. **Nachvollziehbarkeit (Traceability):**
   * ✅ Clear path from DATEV entry → Sammelbeleg PDF → Original transactions
   * ✅ All stored in document management system
   * ✅ Digital signatures for verification

4. **Unveränderbarkeit (Immutability):**
   * ✅ PDFs are immutable once generated
   * ✅ Original GL entries remain unchanged (marked as consolidated, not deleted)
   * ✅ Audit trail in consolidation\_logs

5. **Aufbewahrung (Retention):**
   * ✅ Sammelbeleg PDFs archived like any other receipt (10 years)
   * ✅ Stored in compliant document management system

### Additional Compliance Measures

1. **Audit Trail:** All consolidations logged with actor ID and timestamp
2. **Data Retention:** Original entries and Sammelbeleg PDFs retained for 10+ years
3. **Access Control:** Only period close permissions allow persistent consolidation
4. **GDPR Compliance:** Consolidation doesn't expose additional PII
5. **Professional Verification:** **Recommendation to consult Steuerberater** before production use

### Important Note

⚠️ **While the Sammelbeleg approach significantly strengthens GoBD compliance compared to simple consolidation, it is strongly recommended to:**

1. Consult with a **Steuerberater** or tax advisor
2. Contact **DATEV** to verify this approach is acceptable
3. Review with your **Wirtschaftsprüfer** (auditor) if applicable
4. Document the decision and approvals in your accounting policies

## Potential Oversights & Considerations

### Fields Currently Included in Consolidation Key ✅

1. **debit\_account\_id** - ✅ Included
2. **credit\_account\_id** - ✅ Included
3. **dimensions** (JSON) - ✅ Included (all dimensions)
4. **currency** - ✅ Included
5. **tax\_rate** - ✅ Included
6. **transaction\_date** - ✅ Explicitly EXCLUDED (by design)
7. **amount\_sign** - ✅ Explicitly EXCLUDED (amounts sum algebraically)
8. **Reconciliation completeness** - ✅ Validated (all must be complete in period)

### Fields NOT Currently Considered (Analysis)

#### 1. **tax\_base\_amount** ⚠️ CONSIDER

**Current:** Not in consolidation key
**Question:** Should entries with same tax\_rate but different tax\_base\_amount be kept separate?

**Analysis:**

* `tax_base_amount` is the net amount before tax
* `tax_rate` determines the tax treatment
* With same `tax_rate`, the `tax_base_amount` values will naturally sum correctly

**Recommendation:** ✅ **No action needed** - If tax rates match, base amounts will aggregate correctly.

**Example:**

```
Entry 1: Base: 100, Rate: 19%, Tax: 19, Total: 119
Entry 2: Base: 200, Rate: 19%, Tax: 38, Total: 238
Consolidated: Base: 300, Rate: 19%, Tax: 57, Total: 357 ✓
```

#### 2. **Amount Sign (Positive vs Negative)** ✅ EXCLUDED (CORRECT)

**Current:** ✅ Explicitly EXCLUDED from consolidation key
**Decision:** Allow positive and negative amounts to consolidate together (algebraic summation)

**Analysis:**

* In DATEV export, amounts are ALWAYS exported as **absolute values** using `Math.abs(amount)`
* The sign is indicated separately by `"Soll-/Haben-Kennzeichen"` field (S or H)
* Negative amounts don't create different transactions - they switch the debit/credit indicator
* Consolidating entries with mixed signs results in correct algebraic summation (e.g., 100 + (-50) = 50)

**DATEV Export Behavior:**

```typescript theme={null}
// From export-datev.ts
{
  Umsatz: formatAmountToDatevFormat(entry.amount), // Math.abs() applied
  "Soll-/Haben-Kennzeichen": entry.amount > 0 ? "S" : "H", // Sign as indicator
  // ...
}
```

**Why This is Correct:**

* ✅ DATEV doesn't distinguish amounts by sign - only by Soll/Haben
* ✅ Algebraic summation (100 + (-50) = 50) correctly represents the net accounting effect
* ✅ Consolidated result will have correct Soll/Haben indicator based on final sign
* ✅ Simplifies consolidation logic
* ✅ No need to track amount sign in the consolidation key

#### 3. **Document object\_type / TYPE\_CATEGORY** ✅ NOT NEEDED

**Current:** Not in consolidation key
**Question:** Should invoices consolidate with credit notes?

**Analysis:**

* Documents have `object_type` with categories: SALES, PURCHASE, INTERNAL
* Invoices vs Credit Notes represent opposite business events
* Credit notes typically have negative amounts

**Recommendation:** ✅ **NOT NEEDED**

Since amount signs consolidate together algebraically, invoices and credit notes will naturally consolidate if they share the same accounts and dimensions. This is correct behavior because:

* The net accounting effect is what matters (100 invoice - 50 credit note = 50 net revenue)
* DATEV export handles the sign via Soll/Haben indicator
* Document-level detail is preserved in the consolidation report CSV

#### 4. **Subsidiary** ⚠️ CONSIDER FOR MULTI-ENTITY

**Current:** Not in consolidation key
**Question:** Should entries from different subsidiaries consolidate together?

**Analysis:**

* Documents have `subsidiary_id` for multi-entity organizations
* Different legal entities should likely keep separate books
* Consolidating across subsidiaries might violate legal requirements

**Recommendation:** ⚠️ **ADD IF USING SUBSIDIARIES**

**Implementation:**

```typescript theme={null}
// If using subsidiaries, add to grouping key
const subsidiaryKey = entry.document?.subsidiary_id || "null";
```

**However:** GL entries don't have direct `subsidiary_id` - it's on the document level. This might already be captured in dimensions.

#### 5. **Account Type (CHART\_OF\_ACCOUNT\_TYPE: Debit vs Credit)** ✅ COVERED

**Current:** Implicitly covered by account IDs
**Question:** N/A - Already handled

**Analysis:**

* Each `chart_of_accounts` has `account_type` (Debit or Credit enum)
* Since we're consolidating by specific `debit_account_id` and `credit_account_id`, the account types are implicit
* No additional consideration needed

#### 6. **Entity (Customer/Supplier)** ⚠️ CONSIDER

**Current:** Not in consolidation key
**Question:** Should entries for different customers consolidate together?

**Analysis:**

* Documents have `entity_id` (customer/supplier)
* From an accounting perspective, revenue from Customer A vs Customer B might be the same account
* From an audit perspective, keeping customers separate might be valuable

**Recommendation:** ⚠️ **LIKELY NOT NEEDED**

* For financial reporting, customer identity doesn't matter (account balance is what matters)
* Customer tracking is handled at document level, not GL entry level
* If needed, entity could be added to dimensions

#### 7. **Organization (org\_id)** ✅ COVERED

**Current:** Filtering by org\_id
**Question:** N/A - Already handled

**Analysis:**

* All queries filter by `org_id`
* Entries from different organizations will never consolidate
* ✅ Already implemented correctly

### Summary of Recommendations

| Field             | Currently Handled | Recommendation                           | Priority | Status    |
| ----------------- | ----------------- | ---------------------------------------- | -------- | --------- |
| tax\_base\_amount | ❌ Not in key      | ✅ No action needed                       | Low      | ✅ Done    |
| **Amount sign**   | ✅ **EXCLUDED**    | ✅ **Algebraic summation (CORRECT)**      | **HIGH** | ✅ Done    |
| Document type     | ❌ Not in key      | ✅ Not needed (amounts sum algebraically) | Low      | ✅ Done    |
| Subsidiary        | ❌ Not in key      | ⚠️ Add if using multi-entity             | Medium   | 🔄 Future |
| Account type      | ✅ Implicit        | ✅ No action needed                       | N/A      | ✅ Done    |
| Entity            | ❌ Not in key      | ✅ Likely not needed                      | Low      | ✅ Done    |
| Organization      | ✅ Filtered        | ✅ Already handled                        | N/A      | ✅ Done    |

### ✅ Critical Decision Made

**Amount Sign Handling - RESOLVED**

**Decision:** Allow positive and negative amounts to consolidate together (algebraic summation) ✅

**Scenario:**

```
Entry 1: 1000 → 4000, Amount: +100 EUR (Invoice)
Entry 2: 1000 → 4000, Amount: -50 EUR  (Credit Note)

✅ Chosen Approach (Consolidate & Net):
→ Export 1 line: 50 EUR with Soll/Haben=S
→ Algebraically correct net effect: 100 + (-50) = 50
→ DATEV exports amount as absolute value with Soll/Haben indicator
→ Maximum consolidation while maintaining accounting accuracy

❌ Rejected Approach (Keep Separate):
→ Would export 2 lines: 100 EUR, 50 EUR (both as absolute values)
→ Less consolidation benefit
→ Not necessary since DATEV doesn't distinguish by sign in the key
```

**Why This Works:**

In DATEV export (see `export-datev.ts`):

```typescript theme={null}
{
  Umsatz: Math.abs(amount),           // Always absolute value
  "Soll-/Haben-Kennzeichen": amount > 0 ? "S" : "H"  // Sign as indicator
}
```

Since DATEV always uses absolute amounts with a separate Soll/Haben indicator, there's no accounting difference between:

* Two separate entries (100S, 50H)
* One consolidated entry (50S)

Both represent the same net accounting effect.

**Implementation:** Amount sign is explicitly EXCLUDED from the consolidation key.

## Open Questions

1. **UI for viewing consolidation details?**
   * Should we add a page to view consolidation history?
   * Show consolidation preview before export?

2. **Undo consolidation for period close?**
   * Should persistent consolidation be reversible?
   * What's the business requirement?

3. **Consolidation on dimension subsets?**
   * Allow users to exclude certain dimensions from consolidation?
   * Example: Consolidate ignoring "project" dimension?

4. **Notification system?**
   * Send email with consolidation summary after period close?
   * Include consolidation statistics in export?

5. **Partial reconciliation handling?**
   * Should we provide a report of entries that couldn't be consolidated due to incomplete reconciliation?
   * Alert users when consolidation ratio is lower than expected?

6. **Belegfeld 1 format?** ✅ **RESOLVED - USING CUID**

   * DATEV regex: `^(["][\w$%\-\/]{0,36}["])$`
   * Allowed characters: alphanumeric, underscore, `$`, `%`, `-`, `/`
   * Maximum length: 36 characters (excluding quotes)

   **Selected Format: CONS-{cuid}**

   ```typescript theme={null}
   import { createId } from "@paralleldrive/cuid2";

   // Format: CONS-{cuid}
   // Example: "CONS-clh5z1234567890abcdef"
   // Length: 5 + 25 = 30 characters ✅ (within 36 char limit)

   const consolidationId = createId(); // Full CUID for tracking
   const belegfeld1 = `CONS-${consolidationId}`;
   ```

   **Why CUID?**

   * ✅ **Collision-resistant** - Better uniqueness than shortened UUID
   * ✅ **Sortable** - CUIDs are lexicographically sortable by creation time
   * ✅ **No truncation** - Uses full CUID (25 chars), not a shortened version
   * ✅ **Well within limit** - 30 characters total (6 chars under limit)
   * ✅ **Simple to implement** - No slicing or hashing needed
   * ✅ **No database changes** - CUID stored directly in `consolidation_id` field
   * ✅ **Human-readable prefix** - `CONS-` clearly indicates consolidated entry

   **CUID Format:**

   * Length: 25 characters
   * Characters: lowercase letters and digits (a-z, 0-9)
   * Pattern: starts with letter, followed by timestamp-based + random segments
   * Collision resistance: \~3.6 × 10^47 possible combinations

   **Alternatives Considered (Not Chosen):**

   * Shortened UUID (8 chars): Lower uniqueness, requires truncation
   * Database Sequence: Requires schema changes, not globally unique
   * Timestamp-based: Collision risk within same millisecond
   * Hash-based: More complex, deterministic (not guaranteed unique)

7. **Amount Sign Handling** ✅ **RESOLVED - EXCLUDED**
   * Positive and negative amounts consolidate together (algebraic summation)
   * DATEV export uses absolute amounts with Soll/Haben indicator
   * Correct accounting behavior: 100 + (-50) = 50

## Document Storage Strategy

### Storing Sammelbeleg as `documents` Record

**Decision:** Store Sammelbeleg PDFs as proper document records in the `documents` table with a dedicated `object_type_id = "SAMMELBELEG"`.

**Rationale:**

This approach treats Sammelbeleg documents as **first-class accounting documents** rather than mere metadata or attachments. This is NOT duplication—it's proper document categorization.

### Key Benefits

| Benefit                 | Description                                                                          |
| ----------------------- | ------------------------------------------------------------------------------------ |
| **GoBD Compliance**     | Sammelbeleg is a proper "Beleg" (source document), not just metadata                 |
| **Document Management** | Full integration with existing document lifecycle (archival, retention, permissions) |
| **Traceability**        | Standard document queries work: "Show me all documents for period X"                 |
| **UI Integration**      | Can be viewed, downloaded, and managed like any other document                       |
| **Audit Trail**         | Proper document history, versioning, and actor tracking                              |
| **Retention**           | Automatic 10+ year retention alongside other accounting documents                    |

### Not Duplication—Document Hierarchy

Think of it like:

* **Bank Statement** (summary) + **Individual Transactions** (details) → Both are valid documents
* **Expense Report** (summary) + **Individual Receipts** (details) → Both archived separately
* **Consolidated Invoice** (summary) + **Line Items** (details) → Both part of audit trail

Similarly:

* **Sammelbeleg** (consolidation document) + **Original Invoices** (business documents) → Different document types, different purposes

### Clear Separation via `object_type`

```typescript theme={null}
// Default document list - exclude Sammelbeleg
const docs = await getDocuments({
  where: {
    object_type_id: { not: "SAMMELBELEG" },
  },
});

// Consolidation reports - only Sammelbeleg
const sammelbelegs = await getDocuments({
  where: {
    object_type_id: "SAMMELBELEG",
  },
});
```

This design provides:

1. ✅ **Clear identification** of document types
2. ✅ **Flexible filtering** in queries and UI
3. ✅ **Proper relationships** via `document_relations`
4. ✅ **GoBD compliance** - Sammelbeleg is a legitimate Beleg
5. ✅ **No confusion** - users see business docs by default, consolidation docs on demand

## Why Sammelbeleg PDF Instead of CSV?

### The Evolution

**Original Approach (Rejected):**

* Export separate CSV with consolidation details
* Two-file dependency (DATEV CSV + consolidation CSV)
* CSV not recognized as proper "Beleg" (source document)

**Sammelbeleg Approach (Chosen):**

* Generate formal PDF documents (Sammelbeleg = Collective Receipt)
* Store PDFs as proper source documents in document management system
* DATEV Belegfeld 1 references the Sammelbeleg document ID

### Key Advantages

| Aspect                      | CSV Approach                  | Sammelbeleg PDF Approach                    |
| --------------------------- | ----------------------------- | ------------------------------------------- |
| **Belegprinzip**            | ❌ CSV not a proper Beleg      | ✅ PDF is proper source document             |
| **Professional Acceptance** | ⚠️ Questionable               | ✅ Recognized accounting practice            |
| **Document Storage**        | ❌ Separate file, not archived | ✅ Stored in document management (10+ years) |
| **Audit Workflow**          | ⚠️ Requires cross-referencing | ✅ Standard workflow (show Beleg)            |
| **Immutability**            | ⚠️ CSV can be edited          | ✅ PDF immutable, digitally signed           |
| **Presentation**            | ❌ Raw data format             | ✅ Professional, formatted document          |
| **GoBD Compliance**         | ⚠️ Uncertain                  | ✅ Strong case for compliance                |

### GoBD Compliance Comparison

**CSV Approach Concerns:**

* CSV is not traditionally recognized as a "Beleg" (source document)
* Tax authorities may question split-file approach
* DATEV import only sees consolidated entries, not details
* Requires explanation of cross-referencing system

**Sammelbeleg Approach Benefits:**

* Aligns with traditional German accounting concept of "Sammelbuchungsbeleg"
* PDFs stored like any other receipt/invoice
* Clear audit trail: DATEV → Document System → PDF with all details
* Professional format acceptable in tax audits
* Follows established accounting practice

### Recommendation

**The Sammelbeleg PDF approach is significantly stronger** for GoBD compliance because:

1. ✅ Creates actual source documents (Belege) recognized in German accounting
2. ✅ Integrates with existing document retention systems
3. ✅ Follows established practice (Sammelbuchungsbeleg)
4. ✅ Professional presentation acceptable to tax authorities
5. ✅ No ambiguity about which file is the "official" record

**However**, even with Sammelbeleg approach, professional confirmation is still recommended before production use.

## References

* DATEV Format Specification v13
* GoBD Compliance Requirements (BMF-Schreiben vom 28.11.2019)
* Existing reconciliation system (`reconciliation_groups`)
* Current DATEV export implementation
* German accounting best practices for Sammelbuchungsbeleg

## Approval

**Status:** Draft - Awaiting Review

**Reviewers:**

* [ ] Backend Lead
* [ ] Product Owner
* [ ] Finance/Accounting Team
* [ ] QA Lead

**Next Steps:**

1. **Consult with Steuerberater** regarding Sammelbeleg approach for GoBD compliance
2. **Optional:** Contact DATEV to verify acceptance of this approach
3. Review and approve RFC (pending professional confirmation)
4. Create implementation tickets
5. Begin Phase 1 development

***

## Change Log

| Date       | Change                                                                           | Author           |
| ---------- | -------------------------------------------------------------------------------- | ---------------- |
| 2025-01-04 | ✅ Phase 1 implementation complete - added status update and implementation notes | Development Team |
| 2024-10-29 | Created Phase 1 implementation plan (virtual consolidation MVP)                  | Development Team |
| 2024-10-24 | Added Sammelbeleg PDF approach for improved GoBD compliance                      | Development Team |
| 2024-10-23 | Initial RFC creation                                                             | Development Team |

***

*Last Updated: 2025-01-04 - Phase 1 (Virtual Consolidation) implementation complete*