Audit Plugin

Automatically track all database changes with comprehensive audit logging. Works through @kysera/executor's Unified Execution Layer for consistent behavior.

Installation

npm install @kysera/audit

Basic Usage

With Repository Pattern

import { createORM } from '@kysera/repository'
import { auditPlugin } from '@kysera/audit'

const orm = await createORM(db, [
  auditPlugin({
    getUserId: () => currentUser?.id || null,
    captureOldValues: true,
    captureNewValues: true
  })
])

const userRepo = orm.createRepository(executor => {
  const factory = createRepositoryFactory(executor)
  return factory.create({ tableName: 'users' /* ... */ })
})

// All operations are automatically audited
await userRepo.create({ email: 'john@example.com', name: 'John' })
await userRepo.update(userId, { name: 'John Smith' })
await userRepo.delete(userId)

// Get audit history
const history = await userRepo.getAuditHistory(userId)

With Executor Directly

import { createExecutor } from '@kysera/executor'
import { auditPlugin } from '@kysera/audit'

const executor = await createExecutor(db, [
  auditPlugin({
    getUserId: () => currentUser?.id || null
  })
])

// Audit logging works with direct executor usage
const user = await executor
  .insertInto('users')
  .values({ email: 'john@example.com', name: 'John' })
  .returningAll()
  .executeTakeFirst()
// Audit log entry created automatically

Configuration

interface AuditOptions {
  auditTable?: string // Default: 'audit_logs'
  primaryKeyColumn?: string // Default: 'id'
  captureOldValues?: boolean // Default: true
  captureNewValues?: boolean // Default: true
  skipSystemOperations?: boolean // Default: false
  tables?: string[] // Whitelist tables
  excludeTables?: string[] // Blacklist tables
  getUserId?: () => string | null
  getTimestamp?: () => Date | string
  metadata?: () => Record<string, unknown> // Custom metadata
  logger?: KyseraLogger
}

Configuration Examples

// Basic setup
auditPlugin({
  getUserId: () => currentUser?.id
})

// Full setup with metadata
auditPlugin({
  getUserId: () => currentUser?.id,
  captureOldValues: true,
  captureNewValues: true,
  metadata: () => ({
    ip: request.ip,
    userAgent: request.headers['user-agent'],
    requestId: request.id
  }),
  excludeTables: ['sessions', 'audit_logs']
})

// UUID primary keys
auditPlugin({
  primaryKeyColumn: 'uuid',
  getUserId: () => currentUser?.uuid
})

Audit Log Structure

interface AuditLogEntry {
  id: number
  table_name: string
  entity_id: string
  operation: 'INSERT' | 'UPDATE' | 'DELETE'
  old_values: string | null // JSON
  new_values: string | null // JSON
  changed_by: string | null
  changed_at: string
  metadata: string | null // JSON
}

Added Methods

Method	Description	Returns
getAuditHistory(entityId, options?)	Get change history for an entity	ParsedAuditLogEntry[]
getAuditLogs(entityId, options?)	Alias for getAuditHistory	ParsedAuditLogEntry[]
getAuditLog(auditId)	Get specific audit log entry (raw)	AuditLogEntry | null
getTableAuditLogs(filters?)	Query audit logs across the table with filters	ParsedAuditLogEntry[]
getUserChanges(userId, options?)	Get all changes made by a specific user	ParsedAuditLogEntry[]
restoreFromAudit(auditId)	Restore entity to previous state	T

Parsed vs Raw

Most query methods return ParsedAuditLogEntry[] where old_values, new_values, and metadata are automatically parsed from JSON strings into objects. Only getAuditLog() returns the raw AuditLogEntry with JSON strings.

Querying Audit Logs

// Get history for specific entity
const history = await userRepo.getAuditHistory(userId)

// With pagination
const history = await userRepo.getAuditHistory(userId, {
  limit: 10,
  offset: 0
})

// Get specific audit entry
const entry = await userRepo.getAuditLog(auditLogId)

// Values are already parsed from JSON -- no JSON.parse needed
console.log(history[0].old_values) // Record<string, unknown> | null
console.log(history[0].new_values) // Record<string, unknown> | null

Table-Wide Queries

// Query audit logs across the entire table with filters
const logs = await userRepo.getTableAuditLogs({
  operation: 'UPDATE',
  startDate: new Date('2025-01-01'),
  endDate: new Date('2025-01-31'),
  limit: 100
})

// Filter by specific operations
const deletions = await userRepo.getTableAuditLogs({
  operation: 'DELETE',
  limit: 50
})

User Activity Tracking

// Get all changes made by a specific user
const userActivity = await userRepo.getUserChanges('admin-123', {
  limit: 100,
  offset: 0
})

// Track what a user modified
for (const change of userActivity) {
  console.log(`${change.operation} on ${change.entity_id} at ${change.changed_at}`)
}

Filter Types

interface AuditFilters extends AuditPaginationOptions {
  /** Filter by operation type */
  operation?: 'INSERT' | 'UPDATE' | 'DELETE'
  /** Filter by user ID (changed_by field) */
  userId?: string
  /** Filter by start date (inclusive) - accepts Date, ISO string, or unix timestamp (ms) */
  startDate?: Date | string | number
  /** Filter by end date (inclusive) - accepts Date, ISO string, or unix timestamp (ms) */
  endDate?: Date | string | number
}

interface AuditPaginationOptions {
  /** Maximum number of records to return */
  limit?: number
  /** Number of records to skip */
  offset?: number
}

Restoring from Audit

// Restore entity to a previous state
const restoredUser = await userRepo.restoreFromAudit(auditLogId)

// This will:
// 1. Read the old_values from the audit log
// 2. Update the entity with those values
// 3. Create a new audit entry for the restore operation

Database Schema

Create the audit_logs table:

CREATE TABLE audit_logs (
  id SERIAL PRIMARY KEY,
  table_name VARCHAR(255) NOT NULL,
  entity_id VARCHAR(255) NOT NULL,
  operation VARCHAR(10) NOT NULL,
  old_values JSONB,
  new_values JSONB,
  changed_by VARCHAR(255),
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  metadata JSONB
);

-- Indexes for common queries
CREATE INDEX idx_audit_logs_table_entity ON audit_logs(table_name, entity_id);
CREATE INDEX idx_audit_logs_changed_by ON audit_logs(changed_by);
CREATE INDEX idx_audit_logs_changed_at ON audit_logs(changed_at);

Transaction Support

Audit logs are transaction-aware:

await db.transaction().execute(async (trx) => {
  const repos = createRepos(trx)

  await repos.users.update(userId, { status: 'active' })
  await repos.orders.create({ user_id: userId, ... })

  // If transaction fails, audit logs are also rolled back
})

Bulk Operation Optimization

Performance Optimizations (v0.7.3)

The audit plugin includes significant performance optimizations for batch operations, achieving ~100x faster execution for large batches compared to naive implementations.

The audit plugin optimizes bulk operations using batch INSERT for all audit entries:

// Instead of N queries for old values, uses single IN query
await userRepo.bulkUpdate([
  { id: 1, data: { status: 'active' } },
  { id: 2, data: { status: 'active' } },
  { id: 3, data: { status: 'active' } }
])
// 1 query to fetch old values
// 1 query to update
// 1 query to insert audit logs (batch INSERT)

Performance Comparison

Approach	100 records	Query Count
Old (N+1)	100 INSERT queries	~102 queries
New (batch)	1 batch INSERT	~3 queries
Improvement	~100x faster	~97% fewer queries

Optimized Methods

All bulk methods use batch audit logging:

• createMany(inputs) - Single batch INSERT for audit entries
• updateMany(ids, data) - Batch fetch old values, batch audit INSERT
• deleteMany(ids) - Batch fetch old values, batch audit INSERT
• bulkUpdate(updates) - Optimized for mixed updates

Database-Specific Plugins

For optimal compatibility, use the database-specific audit plugin:

Database	Plugin	Notes
PostgreSQL	auditPluginPostgreSQL()	Full feature support
MySQL	auditPluginMySQL()	DATETIME timestamp handling
SQLite	auditPluginSQLite()	SQLite-specific optimizations

MySQL Timestamp Handling

MySQL's DATETIME type requires specific formatting:

import { auditPluginMySQL } from '@kysera/audit'

const plugins = [
  auditPluginMySQL({
    tableName: 'audit_logs',
    getUserId: () => getCurrentUserId()
  })
]

const executor = await createExecutor(db, plugins)

All Database-Specific Variants

// PostgreSQL-optimized (ISO8601 timestamps)
import { auditPluginPostgreSQL } from '@kysera/audit'

// MySQL-optimized (DATETIME format: 'YYYY-MM-DD HH:MM:SS')
import { auditPluginMySQL } from '@kysera/audit'

// SQLite-optimized (ISO8601 timestamps)
import { auditPluginSQLite } from '@kysera/audit'

note

All database-specific plugins currently use the same core implementation with database-appropriate timestamp formatting. The generic auditPlugin() also works across all databases. The database-specific variants are provided for future optimizations and explicit type clarity.

Best Practices

1. Exclude Audit Table from Auditing

auditPlugin({
  excludeTables: ['audit_logs'] // Prevent infinite loop
})

2. Include Request Context

auditPlugin({
  getUserId: () => currentUser?.id,
  metadata: () => ({
    ip: request.ip,
    sessionId: session.id,
    userAgent: request.headers['user-agent']
  })
})

3. Partition Large Audit Tables

-- PostgreSQL partitioning by date
CREATE TABLE audit_logs (
  ...
) PARTITION BY RANGE (changed_at);

CREATE TABLE audit_logs_2024_q1 PARTITION OF audit_logs
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

4. Archive Old Audit Logs

-- Move old logs to archive
INSERT INTO audit_logs_archive
SELECT * FROM audit_logs
WHERE changed_at < NOW() - INTERVAL '1 year';

DELETE FROM audit_logs
WHERE changed_at < NOW() - INTERVAL '1 year';