NetForge/.planning/phases/04-inventory-expenses-and-financial-reports/04-04-PLAN.md

phase, plan, type, wave, depends_on, files_modified, autonomous, must_haves

phase	plan	type	wave	depends_on	files_modified	autonomous	must_haves
04-inventory-expenses-and-financial-reports	04	execute	2	04-03	src/lib/services/expense-report-service.ts src/lib/services/audit-trail-service.ts src/app/api/reports/expenses/route.ts src/app/api/reports/expenses/by-vendor/route.ts src/app/api/accounting/journal-entries/[id]/audit/route.ts src/lib/__tests__/expense-report-service.test.ts	true	truths artifacts key_links Admin can generate expense reports filtered by category, vendor, and date range Expense report shows totals per category and per vendor for the period Every journal entry has a full audit trail: who created it, when, what source transaction Audit trail shows referenceType and referenceId linking JE back to source (Invoice, Payment, Expense, StockMovement, Collection, Remittance) path provides exports src/lib/services/expense-report-service.ts getExpensesByCategory, getExpensesByVendor, getExpenseSummary ExpenseReportService path provides exports src/lib/services/audit-trail-service.ts getJournalEntryAudit, getAuditTrailForEntity AuditTrailService path provides min_lines src/lib/__tests__/expense-report-service.test.ts Tests for expense reports and audit trail 60 from to via pattern src/lib/services/expense-report-service.ts prisma.expense Aggregation queries on expense records expense.(groupBy|findMany|aggregate) from to via pattern src/lib/services/audit-trail-service.ts prisma.journalEntry Query JE with createdBy, approvedBy, referenceType journalEntry.find

Build expense reporting (by category, vendor, period) and the journal entry audit trail that links every accounting entry back to its source transaction.

Purpose: Gives ISP owners spending visibility (where money goes) and auditors the ability to trace any ledger entry to its origin. ACCT-08 (audit trail) applies to ALL journal entries, not just expense ones. Output: ExpenseReportService, AuditTrailService, report API routes, tests.

<execution_context> @C:\Users\KevinAsprec.claude/get-shit-done/workflows/execute-plan.md @C:\Users\KevinAsprec.claude/get-shit-done/templates/summary.md </execution_context>

@.planning/PROJECT.md @.planning/ROADMAP.md @.planning/STATE.md @.planning/phases/04-inventory-expenses-and-financial-reports/04-CONTEXT.md @.planning/phases/04-inventory-expenses-and-financial-reports/04-03-SUMMARY.md @prisma/schema.prisma @src/lib/accounting/journal-entry-service.ts @src/lib/services/expense-service.ts Task 1: ExpenseReportService and AuditTrailService src/lib/services/expense-report-service.ts src/lib/services/audit-trail-service.ts src/app/api/reports/expenses/route.ts src/app/api/reports/expenses/by-vendor/route.ts src/app/api/accounting/journal-entries/[id]/audit/route.ts **ExpenseReportService** (`src/lib/services/expense-report-service.ts`): Static class:

• getExpensesByCategory(tenantPrisma, { startDate, endDate }):

  • Query POSTED expenses grouped by categoryId within date range
  • Return: array of { categoryId, categoryName, totalAmount (Decimal), expenseCount (number) }
  • Order by totalAmount DESC

• getExpensesByVendor(tenantPrisma, { startDate, endDate, vendorId? }):

  • Query POSTED expenses grouped by vendorId within date range
  • Optional vendorId filter for single-vendor detail
  • Return: array of { vendorId, vendorName, totalAmount, expenseCount }
  • Include a "No Vendor" bucket for expenses without vendorId
  • Order by totalAmount DESC

• getExpenseSummary(tenantPrisma, { startDate, endDate }):

  • Return combined report: { totalExpenses (Decimal), byCategory: [...], byVendor: [...], expenseCount }
  • Calls the two methods above and computes grand total

AuditTrailService (src/lib/services/audit-trail-service.ts): Static class:

• getJournalEntryAudit(tenantPrisma, entryId):

  • Fetch JE with include: createdBy (select id, email, firstName, lastName), approvedBy, lines with account details
  • Return: { entryNumber, date, description, source, status, referenceType, referenceId, createdBy: {name, email}, createdAt, approvedBy?: {name, email}, approvedAt?, lines: [{account code+name, debit, credit}] }
  • This provides ACCT-08: who created it, when, what source transaction

• getAuditTrailForEntity(tenantPrisma, { referenceType, referenceId }):

  • Fetch all JEs where referenceType and referenceId match
  • Returns array of JE audit records — enables "show all journal entries for this invoice" or "for this payment"
  • Includes reversing entries (where reversesEntryId links to matching JEs)

API Routes:

• GET /api/reports/expenses — expense summary by category. Query: startDate, endDate. ADMIN only.
• GET /api/reports/expenses/by-vendor — expense summary by vendor. Query: startDate, endDate, vendorId?. ADMIN only.
• GET /api/accounting/journal-entries/[id]/audit — full audit trail for a JE. ADMIN, OFFICE_STAFF.

All routes use withPermission() HOF. API route files exist and export correct HTTP methods ExpenseReportService provides category and vendor expense reports; AuditTrailService links every JE to its creator and source transaction

Task 2: Tests for expense reports and audit trail src/lib/__tests__/expense-report-service.test.ts **Setup:** createTenant, create admin user, create 2 vendors, create expenses across 3 categories (use 2 different expense categories from the 9 seeded defaults + 1 custom category). Create expenses linked to different vendors. Ensure expenses are POSTED (so JEs exist).

Test cases:

1. Expense report by category — returns correct totals per category for date range
2. Expense report by category excludes DRAFT/VOIDED expenses (only POSTED counted)
3. Expense report by vendor — returns correct totals per vendor
4. Expense report by vendor includes "No Vendor" bucket for unlinked expenses
5. Expense summary — combines category and vendor views with grand total
6. Date range filtering works (expenses outside range excluded)
7. JE audit trail — fetch audit for a posted expense's JE: shows createdBy user, source=SYSTEM, referenceType="Expense", referenceId=expense.id
8. Audit trail for entity — fetch all JEs for an expense (original + void reversal if voided)
9. JE audit trail on a payment JE (from Phase 2) — verify referenceType="Payment" is correctly returned (proves ACCT-08 works across all JE sources, not just expenses)

Cleanup order: expenses -> vendors -> expenseCategories (custom only) -> journalEntryLines -> null reversesEntryId -> journalEntries -> invoiceLines -> invoices -> subscribers -> servicePlans -> accountingPeriods -> accounts -> ticketCategories -> expenseCategories (system) -> users -> tenant npx jest expense-report-service --verbose passes all tests Expense reports correctly aggregate by category/vendor with date filtering. Audit trail shows creator, timestamp, and source reference for any JE. All tests pass.

- `npx jest expense-report-service --verbose` — all tests pass - Expense report by category shows correct totals - Expense report by vendor shows correct totals - JE audit trail includes who/when/source for all JE types (not just expenses)

<success_criteria>

• Admin can generate expense reports by category and vendor for any date range
• Reports show totals and counts, excluding non-POSTED expenses
• Every journal entry (Invoice, Payment, Expense, Collection, Remittance, StockMovement) has traceable audit trail
• All tests pass </success_criteria>

After completion, create `.planning/phases/04-inventory-expenses-and-financial-reports/04-04-SUMMARY.md`