Skip to content

Audit Logging Setup

Overview

The audit logging system automatically tracks all create, update, and delete events in the database. It uses the generic event bus architecture and stores audit logs in the audit table.

Prerequisites

  1. Database Migration: Run the audit table migration to add new columns: sql -- File: database/migrations/001_audit_table_refactor.sql -- Adds: event_type, entity, entity_id, metadata columns

  2. Supabase Service Role Key: You need the service role key from your Supabase project settings.

Environment Configuration

Add the following to your apps/api/.env file:

# Supabase Service Role Key (for system operations like audit logging)
# This key bypasses Row Level Security and should be kept secret
# Only used server-side for audit logging and system operations
SUPABASE_SERVICE_ROLE_KEY=your_service_role_key_here

How to Get the Service Role Key

  1. Go to your Supabase project dashboard
  2. Navigate to Settings → API
  3. Under Project API keys, find the service_role key
  4. Copy it and add it to your .env file

⚠️ IMPORTANT: - Never commit the service role key to version control - Never expose it to client-side code - This key bypasses Row Level Security (RLS) - Only use it for server-side system operations

How It Works

  1. Event Emission: Controllers emit events using emitEvent(): ```typescript import { emitEvent } from "../services/EventBus";

emitEvent("create", "invoice", invoiceId, userId, { document_number: invoice.document_number }); ```

  1. Event Bus: The backend event bus receives all events

  2. Audit Subscriber: Listens to all create, update, delete events:

  3. Calculates diffs for update operations
  4. Uses the system Supabase client (with service role key)
  5. Inserts audit logs into the database

  6. Non-blocking (errors don't break the application)

  7. Database: Audit logs are stored in the audit table with:

  8. event_type: create/update/delete/notify
  9. entity: invoice/customer/product/etc.
  10. entity_id: UUID of the affected entity
  11. actor_id: User who triggered the event
  12. diff: JSON diff for updates, or event details
  13. metadata: Optional additional context
  14. Legacy fields for backward compatibility

Verification

To verify audit logging is working:

  1. Check Logs: Watch for [AuditSubscriber] debug messages in the console

  2. Query Database: Check the audit table for new entries sql SELECT * FROM audit ORDER BY created_at DESC LIMIT 10;

  3. Test Event: Create, update, or delete an entity and check the audit log

Troubleshooting

No Audit Logs Being Created

  1. Check Environment Variable: bash echo $SUPABASE_SERVICE_ROLE_KEY

  2. Check Console Logs: Look for initialization messages: [AuditSubscriber] Initializing audit logging subscriber [AuditSubscriber] Initialized successfully [SystemSupabaseClient] Initialized with service role key

  3. Check Error Messages: Look for database errors in the console

Permission Denied Errors

  • Ensure you're using the service_role key, not the anon key
  • The service role key bypasses RLS and has full database access

Migration Not Applied

Run the migration manually:

# Connect to your database and run:
psql $DATABASE_URL < database/migrations/001_audit_table_refactor.sql

Security Considerations

  1. Service Role Key Protection:
  2. Store in environment variables only
  3. Never log the key

  4. Rotate regularly if compromised

  5. Audit Log Retention:

  6. Consider implementing automatic cleanup for old logs

  7. Set up database archiving for compliance

  8. Performance:

  9. Audit logging is asynchronous and non-blocking
  10. Failed audit logs don't affect business operations
  11. Consider indexing the audit table for better query performance