Skip to content

Issue #223: Add Status History Table to Invoice Details

Status: ✅ Closed Priority: SHOULD have Labels: F: Invoicing, Enhancement Created: 2025-10-29 Resolved: 2025-10-29

Problem

Users had no visibility into invoice status changes over time. Key questions could not be answered:

  • Who changed the invoice status?
  • When was the status changed?
  • What was the previous status?
  • Why did the status change? (implicit from old→new status)

This lack of transparency made it difficult to: - Track invoice lifecycle for auditing purposes - Debug status transition issues - Understand payment history timeline - Comply with accounting audit requirements

Solution

Implementation Overview

Created a complete status history feature by:

  1. Backend API - New endpoint to fetch status change audit logs
  2. Type Safety - Zod schemas for TypeScript validation
  3. Frontend UI - Reactive table component displaying status history
  4. Internationalization - Translations for English and German

1. Zod Schemas

Created shared TypeScript schemas for type safety across frontend and backend:

File: packages/schemas/src/StatusHistorySchema.ts (NEW)

import { z } from "zod";

export const StatusHistoryEntrySchema = z.object({
  id: z.string().uuid(),
  old_status: z.string().nullable(),
  new_status: z.string(),
  changed_by: z.string().uuid(),
  changed_by_email: z.string().email().optional(),
  changed_at: z.coerce.date(),
});

export type StatusHistoryEntry = z.infer<typeof StatusHistoryEntrySchema>;

export const StatusHistoryResponseSchema = z.object({
  entries: z.array(StatusHistoryEntrySchema),
  total: z.number(),
});

export type StatusHistoryResponse = z.infer<typeof StatusHistoryResponseSchema>;

Key Features: - old_status is nullable (null for first status when invoice created) - changed_by_email is optional (may not be available in all cases) - changed_at uses z.coerce.date() to handle string→Date conversion - Exports both Zod schemas and TypeScript types

2. Backend API Endpoint

Added new controller method to fetch status history from audit logs:

File: apps/api/src/controllers/InvoiceController.ts

async getStatusHistory(c: Context) {
  const id = c.req.param("id");

  try {
    // 1. Verify invoice ownership (security check)
    const { data: invoice, error: invoiceError } = await sb(c.get("token"))
      .from(this.tableName)
      .select("id")
      .eq("id", id)
      .eq("type", this.config.documentType)
      .eq("service_provider_id", c.get("user").id)
      .single();

    if (invoiceError || !invoice) {
      return c.json({ error: "Invoice not found" }, 404 as any);
    }

    // 2. Fetch audit entries with system client (bypasses RLS)
    const systemClient = getSystemSupabaseClient();
    const { data: auditEntries, error: auditError } = await systemClient
      .from("audit")
      .select("id, diff, actor_id, created_at")
      .eq("entity", "invoice")
      .eq("entity_id", id)
      .eq("event_type", "update")
      .order("created_at", { ascending: true });

    if (auditError) {
      throw auditError;
    }

    // 3. Filter and transform entries with status changes
    const statusEntries: StatusHistoryEntry[] = [];
    for (const entry of auditEntries || []) {
      // Only include entries that have status changes
      if (entry.diff?.status?.__old !== undefined &&
          entry.diff?.status?.__new !== undefined) {

        // 4. Fetch user email for attribution
        const { data: user } = await systemClient
          .from("service_providers")
          .select("email")
          .eq("id", entry.actor_id)
          .single();

        statusEntries.push({
          id: entry.id,
          old_status: entry.diff.status.__old,
          new_status: entry.diff.status.__new,
          changed_by: entry.actor_id,
          changed_by_email: user?.email,
          changed_at: new Date(entry.created_at),
        });
      }
    }

    return c.json({
      entries: statusEntries,
      total: statusEntries.length,
    });
  } catch (error: unknown) {
    console.error("Error fetching status history:", error);
    return c.json(
      {
        error: error instanceof Error
          ? error.message
          : "Failed to fetch status history"
      },
      500 as any
    );
  }
}

Security Features: - First checks invoice ownership using user's JWT token - Only returns history for invoices owned by the requesting user - Uses system client to query audit table (bypasses RLS for audit logs) - Email addresses are only returned if available

Data Transformation: - Filters audit logs to only include status changes (not all updates) - Extracts __old and __new values from diff JSONB structure - Joins with service_providers table to get user email - Sorts by timestamp (oldest first)

3. API Route Definition

Added OpenAPI route definition with proper schema validation:

File: apps/api/src/routes/invoices.ts

const statusHistoryRoute = createRoute({
  method: "get",
  path: "/{id}/status-history",
  tags: ["Invoices"],
  summary: "Get invoice status history",
  description: "Get a list of all status changes for a specific invoice with timestamps and user information",
  request: {
    params: InvoiceIdParamSchema,
  },
  responses: {
    200: {
      description: "Status change history",
      content: {
        "application/json": {
          schema: StatusHistoryResponseSchema,
        },
      },
    },
    401: commonResponses.unauthorized,
    404: commonResponses.notFound,
    500: commonResponses.serverError,
  },
});

app.openapi(
  statusHistoryRoute,
  (async (c: Context) => invoiceController.getStatusHistory(c)) as any
);

API Specification: - Method: GET - Path: /invoices/{id}/status-history - Authentication: Required (JWT) - Response: Array of status history entries with total count

4. Frontend Composable

Added method to useInvoice composable for API integration:

File: apps/web/composables/useInvoice.ts

const getStatusHistory = async (invoiceId: string) => {
  return await $api<StatusHistoryResponse>(
    `${BASE_URL}/${invoiceId}/status-history`,
    {
      method: "GET",
    }
  );
};

return {
  getInvoices,
  printInvoice,
  downloadInvoicePdf,
  getInvoice,
  saveInvoice,
  updateStatus,
  cloneInvoice,
  deleteInvoice,
  sendInvoiceEmail,
  resendInvoiceEmail,
  getStatusHistory,  // NEW
};

5. Frontend UI Component

Added status history table to invoice detail page:

File: apps/web/pages/member/feature/invoicing/[id].vue

<template>
  <div v-if="invoice">
    <!-- ... existing invoice form ... -->

    <!-- Status History -->
    <div v-if="statusHistory && statusHistory.entries.length > 0" class="mt-8">
      <h2 class="text-xl font-bold mb-4">
        {{ $t("headline.status_history") }}
      </h2>
      <div class="overflow-x-auto">
        <table class="table table-sm table-pin-rows">
          <thead>
            <tr>
              <th>{{ $t("label.date") }}</th>
              <th>{{ $t("label.from") }}</th>
              <th>{{ $t("label.to") }}</th>
              <th>{{ $t("label.changed_by") }}</th>
            </tr>
          </thead>
          <tbody>
            <tr
              v-for="(entry, index) in statusHistory.entries"
              :key="entry.id"
              :class="index % 2 === 0 ? 'bg-base-200' : ''"
            >
              <td class="font-mono text-sm">
                {{ new Date(entry.changed_at).toLocaleString() }}
              </td>
              <td class="text-sm">
                <InvoiceBadge
                  v-if="entry.old_status"
                  :invoiceStatus="entry.old_status!"
                />
                <span v-else class="text-base-content/50">-</span>
              </td>
              <td class="text-sm">
                <InvoiceBadge
                  v-if="entry.new_status"
                  :invoiceStatus="entry.new_status!"
                />
                <span v-else class="text-base-content/50">-</span>
              </td>
              <td class="text-sm text-base-content/70">
                {{ entry.changed_by_email || entry.changed_by }}
              </td>
            </tr>
          </tbody>
        </table>
      </div>
    </div>

    <!-- ... existing payment history ... -->
  </div>
</template>

<script setup lang="ts">
import {
  type InvoiceDocument,
  InvoiceDocumentSchema,
  DOCUMENT_STATUS_DRAFT,
  EVENT_INVOICE_UPDATED,
  EVENT_PAYMENT_BOOKED,
  EVENT_PAYMENT_FAILED,
} from "@meisterbill/schemas";

const { getInvoice, getStatusHistory } = useInvoice();
const { id } = useRoute().params as { id: string };

const invoice = ref(await getInvoice(id));
if (!invoice.value) {
  throw new Error(`Invoice not found`);
}

// Fetch status history for this invoice - make it reactive
const statusHistory = ref(await getStatusHistory(id));

// ... rest of component logic ...
</script>

UI Features: - Only displays table if status history exists (conditional rendering) - Uses InvoiceBadge component to display status with color coding - Shows "-" for NULL old_status (initial creation) - Displays user email or fallback to user ID - Alternating row colors (bg-base-200) for better readability - Responsive table with overflow-x-auto for mobile - Monospace font for dates and consistent formatting

6. Internationalization

Added translations for English and German:

File: apps/web/i18n/locales/en.json

{
  "headline": {
    "status_history": "Status History",
    "payment_history": "Payment History"
  },
  "label": {
    "date": "Date",
    "from": "From",
    "to": "To",
    "changed_by": "Changed By"
  }
}

File: apps/web/i18n/locales/de.json

{
  "headline": {
    "status_history": "Statusverlauf",
    "payment_history": "Zahlungsverlauf"
  },
  "label": {
    "date": "Datum",
    "from": "Von",
    "to": "Nach",
    "changed_by": "Geändert von"
  }
}

Benefits

  1. Full Transparency - Complete audit trail of who changed what and when
  2. Accountability - User attribution for all status changes
  3. Debugging - Easier to diagnose status transition issues
  4. Compliance - Meets accounting and auditing requirements
  5. User Experience - Visual timeline of invoice lifecycle
  6. Type Safety - Zod schemas prevent runtime errors
  7. Security - Only returns history for owned invoices

Example Usage

API Request

GET /invoices/123e4567-e89b-12d3-a456-426614174000/status-history
Authorization: Bearer <jwt_token>

API Response

{
  "entries": [
    {
      "id": "550e8400-e29b-41d4-a716-446655440000",
      "old_status": null,
      "new_status": "draft",
      "changed_by": "7c9e6679-7425-40de-944b-e07fc1f90ae7",
      "changed_by_email": "user@example.com",
      "changed_at": "2025-10-29T10:30:00Z"
    },
    {
      "id": "550e8400-e29b-41d4-a716-446655440001",
      "old_status": "draft",
      "new_status": "sent",
      "changed_by": "7c9e6679-7425-40de-944b-e07fc1f90ae7",
      "changed_by_email": "user@example.com",
      "changed_at": "2025-10-29T14:20:00Z"
    },
    {
      "id": "550e8400-e29b-41d4-a716-446655440002",
      "old_status": "sent",
      "new_status": "partially_paid",
      "changed_by": "7c9e6679-7425-40de-944b-e07fc1f90ae7",
      "changed_by_email": "user@example.com",
      "changed_at": "2025-10-29T16:45:00Z"
    }
  ],
  "total": 3
}

UI Display

Status History
┌─────────────────────┬──────────┬───────────────┬─────────────────────┐
│ Date                │ From     │ To            │ Changed By          │
├─────────────────────┼──────────┼───────────────┼─────────────────────┤
│ 10/29/2025, 10:30 AM│ -        │ 🟡 Draft      │ user@example.com    │
│ 10/29/2025, 2:20 PM │ 🟡 Draft │ 🔵 Sent       │ user@example.com    │
│ 10/29/2025, 4:45 PM │ 🔵 Sent  │ 🟢 Part. Paid │ user@example.com    │
└─────────────────────┴──────────┴───────────────┴─────────────────────┘

Files Modified

Backend

  • packages/schemas/src/StatusHistorySchema.ts - NEW: Zod schemas for status history
  • apps/api/src/controllers/InvoiceController.ts - Added getStatusHistory() method
  • apps/api/src/routes/invoices.ts - Added status-history route definition

Frontend

  • apps/web/composables/useInvoice.ts - Added getStatusHistory() method
  • apps/web/pages/member/feature/invoicing/[id].vue - Added status history table UI
  • apps/web/i18n/locales/en.json - Added English translations
  • apps/web/i18n/locales/de.json - Added German translations

Technical Details

Audit Log Structure

The audit table stores change history with this structure:

interface AuditEntry {
  id: string;
  actor_id: string;
  event_type: "create" | "update" | "delete";
  entity: "invoice" | "offer" | "credit_note";
  entity_id: string;
  diff: {
    [field: string]: {
      __old: any;
      __new: any;
    };
  };
  metadata?: Record<string, any>;
  created_at: string;
}

Status Change Detection:

if (entry.diff?.status?.__old !== undefined &&
    entry.diff?.status?.__new !== undefined) {
  // This entry contains a status change
  const oldStatus = entry.diff.status.__old;
  const newStatus = entry.diff.status.__new;
}

System Client vs User Client

  • User Client (sb(token)): Respects Row Level Security, only sees user's data
  • System Client (getSystemSupabaseClient()): Bypasses RLS, sees all data

Usage Pattern: 1. Verify ownership with user client (security check) 2. Query audit logs with system client (audit logs require elevated access) 3. Return filtered results to user

Vue Composition API

The component uses Nuxt 3's auto-imported composables:

const { getInvoice, getStatusHistory } = useInvoice();
const { id } = useRoute().params as { id: string };

const invoice = ref(await getInvoice(id));
const statusHistory = ref(await getStatusHistory(id));

Benefits: - Top-level await (Nuxt 3 feature) - Reactive refs automatically unwrap in template - Type-safe with TypeScript - Auto-imported from composables/

Future Enhancements

Potential Improvements

  1. Filtering - Filter by date range, user, or status type
  2. Pagination - For invoices with very long history
  3. Export - Download history as CSV or PDF
  4. Real-time Updates - WebSocket updates for collaborative editing
  5. Diff Visualization - Show what else changed besides status
  6. Comments - Allow users to add notes to status changes
  7. Notifications - Email/SMS alerts on specific status changes

Example: Add Filtering

const getStatusHistory = async (
  invoiceId: string,
  filters?: {
    fromDate?: Date;
    toDate?: Date;
    changedBy?: string;
  }
) => {
  return await $api<StatusHistoryResponse>(
    `${BASE_URL}/${invoiceId}/status-history`,
    {
      method: "GET",
      params: filters,
    }
  );
};

Migration Notes

No Database Changes Required

✅ This feature uses existing audit table infrastructure ✅ No schema migrations needed ✅ No data migrations required ✅ Backward compatible with existing invoices

Deployment Steps

  1. Deploy backend changes (API + schemas)
  2. Deploy frontend changes (web)
  3. No downtime required
  4. Feature immediately available for all users
  • Issue #222: Fix invoice sending issues (audit table improvements)
  • Issue #221: Remove ambiguous characters from document numbers
  • Issue #220: Fix missing document_number API error

References

  • Commit: d1f6b51
  • Supabase RLS: https://supabase.com/docs/guides/auth/row-level-security
  • Zod Validation: https://zod.dev/
  • Vue 3 Composition API: https://vuejs.org/guide/extras/composition-api-faq.html
  • Nuxt 3 Composables: https://nuxt.com/docs/guide/directory-structure/composables