lunarfront-app/planning/26_Audit_Trail.md

Music Store Management Platform

Audit Trail — Database Change History

Version 1.0 | Draft

1. Overview

The audit trail captures every data change across all domain tables — who changed what, when, and the before/after values. This provides a complete history of business operations for troubleshooting, compliance, and accountability. While not required for SOC 2 certification, the audit trail satisfies the "monitoring" control family and provides evidence of change management.

2. Design

2.1 Approach

A single audit_log table captures all changes via a database trigger or application-level middleware. Each row represents one field change on one record.

Pros of application-level (chosen):

• Access to the authenticated user ID (DB triggers don't know who's logged in)
• Can exclude bulk/system operations selectively
• Works with any database (no Postgres-specific trigger functions)
• Easier to test

Cons:

• Every service write must call the audit logger
• Possible to miss a write (mitigated by a shared helper)

2.2 What Gets Audited

Audited (all domain tables):

• account, member, member_identifier
• product, inventory_unit, category, supplier
• rental, rental_agreement, rental_payment
• repair_ticket, repair_line_item, repair_part
• transaction, transaction_line_item
• tax_exemption, account_payment_method, account_processor_link
• user, role, role_permission, user_role
• file (uploads and deletes)

NOT audited (high-volume, low-value):

• email_log (already an append-only log)
• stock_receipt (already append-only)
• price_history (already append-only)
• inventory_unit_status, item_condition (lookup tables — low risk)
• Session/token data

3. Database Schema

3.1 audit_log

Column | Type | Notes id | uuid PK | company_id | uuid | Tenant scoping table_name | varchar(100) | Which table was changed record_id | uuid | Primary key of the changed record action | varchar(10) | insert, update, delete field_name | varchar(100) | Nullable for insert/delete. Which column changed for updates. old_value | text | Nullable. Previous value (JSON-encoded for complex types) new_value | text | Nullable. New value (JSON-encoded for complex types) changed_by | uuid | User who made the change (nullable for system operations) changed_at | timestamptz | When the change occurred request_id | varchar(100) | Nullable. Correlates with Pino request ID for tracing.

3.2 Indexes

• (company_id, table_name, record_id) — find all changes to a specific record
• (company_id, changed_by) — find all changes by a specific user
• (company_id, changed_at) — time-range queries for retention
• (changed_at) — for retention cleanup job

3.3 Storage Estimate

Typical music store: ~50 users, ~5000 accounts, ~200 transactions/day

• ~500 audit rows/day (updates, creates across all tables)
• ~15,000 rows/month
• ~180,000 rows/year
• At ~200 bytes/row: ~36 MB/year

Retention at 30 days: ~15,000 rows, under 5 MB. Negligible.

4. Audit Helper

4.1 Service Pattern

import { auditLog } from '../lib/audit.js'

// In a service method:
async update(db, companyId, id, input, changedBy, requestId) {
  const existing = await this.getById(db, companyId, id)

  const [updated] = await db.update(table).set(input).where(...).returning()

  // Log each changed field
  await auditLog(db, {
    companyId,
    tableName: 'account',
    recordId: id,
    action: 'update',
    changes: diffFields(existing, updated),
    changedBy,
    requestId,
  })

  return updated
}

4.2 Diff Helper

function diffFields(before: Record<string, unknown>, after: Record<string, unknown>) {
  const changes: { field: string; old: unknown; new: unknown }[] = []
  for (const key of Object.keys(after)) {
    if (JSON.stringify(before[key]) !== JSON.stringify(after[key])) {
      changes.push({ field: key, old: before[key], new: after[key] })
    }
  }
  return changes
}

4.3 Bulk Insert

For efficiency, audit entries are batch-inserted. An update that changes 3 fields creates 3 audit_log rows in one INSERT.

5. Retention

5.1 Default: 30 Days

Audit records older than 30 days are automatically deleted by a daily cleanup job.

5.2 Configuration

Variable | Description | Default AUDIT_RETENTION_DAYS | Days to keep audit records | 30 AUDIT_ENABLED | Enable/disable audit trail | true

5.3 Cleanup Job

A BullMQ recurring job runs daily at 3 AM (store timezone):

DELETE FROM audit_log WHERE changed_at < NOW() - INTERVAL '30 days'

5.4 Extended Retention

Stores requiring longer retention (compliance, legal) can set AUDIT_RETENTION_DAYS=365 or higher. Storage impact is minimal (~36 MB/year).

6. Querying the Audit Trail

6.1 API Endpoints

GET /v1/audit?table=account&recordId={uuid}

• All changes to a specific record

GET /v1/audit?table=account&changedBy={userId}

• All changes by a specific user

GET /v1/audit?startDate=2026-03-01&endDate=2026-03-31

• All changes in a date range

GET /v1/audit?table=account&recordId={uuid}&field=name

• Changes to a specific field on a specific record

All endpoints require users.admin permission.

6.2 Admin UI

An "Audit Log" page in the admin panel:

• Searchable, filterable table
• Filter by: table, user, date range, record ID
• Click a record to see the full change history
• Each entry shows: timestamp, user, table, record, field, old → new value
• Export as CSV for compliance reports

7. What Gets Logged Per Action

7.1 Insert

One audit_log row with:

• action = 'insert'
• field_name = NULL
• old_value = NULL
• new_value = JSON of entire inserted record

7.2 Update

One audit_log row per changed field:

• action = 'update'
• field_name = the column name
• old_value = previous value
• new_value = new value

Unchanged fields are NOT logged.

7.3 Delete (soft)

One audit_log row with:

• action = 'delete'
• field_name = 'is_active'
• old_value = 'true'
• new_value = 'false'

7.4 Delete (hard)

One audit_log row with:

• action = 'delete'
• field_name = NULL
• old_value = JSON of deleted record
• new_value = NULL

8. Sensitive Field Masking

Some fields should not have their values stored in the audit trail:

Field | Masking user.password_hash | Never logged. Action logged but old/new values are '***' account_payment_method.processor_payment_method_id | Last 4 chars only member_identifier.value | Masked except last 4 chars

9. Implementation Order

1. Create audit_log table and migration
2. Implement audit helper (diffFields, bulk insert)
3. Add to account and member services (highest traffic)
4. Add to tax exemption (compliance-sensitive)
5. Add to user management (security-sensitive)
6. Add retention cleanup BullMQ job
7. Add API endpoints for querying
8. Add admin UI page
9. Add to remaining domain services as they're built

10. Business Rules

• Audit records are append-only — never updated or manually deleted
• Retention cleanup is the only deletion mechanism
• All audit queries are company-scoped (multi-tenant isolation)
• System operations (migrations, seeds) log with changed_by = NULL
• Bulk operations (imports, batch updates) are audited per-record
• Password and payment token fields are masked in audit values
• Audit trail must not impact request latency — inserts are batched and non-blocking where possible
• The audit_log table itself is NOT audited (prevents infinite recursion)