@kysera/audit

Audit logging plugin for Kysera - Automatically track all database changes with comprehensive audit logging.

Installation

npm install @kysera/audit

Overview

Metric	Value
Version	0.7.0
Bundle Size	~8 KB (minified)
Dependencies	@kysera/core (workspace)
Peer Dependencies	kysely >=0.28.8, @kysera/repository

Exports

// Main plugin
export { auditPlugin } from './index'

// Database-specific plugins
export { auditPluginPostgreSQL } from './dialects/postgres'
export { auditPluginMySQL } from './dialects/mysql'
export { auditPluginSQLite } from './dialects/sqlite'

// Types
export type {
  AuditOptions,
  AuditLogEntry,
  AuditFilters,
  AuditMethods,
  AuditRepository,
  AuditOperation,
  AuditRepositoryExtensions,
  ParsedAuditLogEntry,
  AuditPaginationOptions
}

auditPlugin

Creates an audit logging plugin instance.

function auditPlugin(options?: AuditOptions): Plugin

AuditOptions

interface AuditOptions {
  /**
   * Name of the audit log table
   * @default 'audit_logs'
   */
  auditTable?: string

  /**
   * Name of the primary key column in tracked tables
   * @default 'id'
   */
  primaryKeyColumn?: string

  /**
   * Capture old values on UPDATE/DELETE
   * @default true
   */
  captureOldValues?: boolean

  /**
   * Capture new values on INSERT/UPDATE
   * @default true
   */
  captureNewValues?: boolean

  /**
   * Skip audit for system operations (migrations, seeds)
   * @default false
   */
  skipSystemOperations?: boolean

  /**
   * List of tables to audit (whitelist)
   * If not specified, all tables except excludeTables will be audited
   */
  tables?: string[]

  /**
   * List of tables to exclude from auditing (blacklist)
   */
  excludeTables?: string[]

  /**
   * Function to get the current user ID
   */
  getUserId?: () => string | number | null

  /**
   * Function to get the timestamp for audit entries
   * @default () => new Date().toISOString()
   */
  getTimestamp?: () => Date | string

  /**
   * Function to get additional metadata for audit entries
   */
  metadata?: () => Record<string, unknown>

  /**
   * Logger for plugin operations
   */
  logger?: KyseraLogger
}

Configuration Examples

import { auditPlugin } from '@kysera/audit'

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

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

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

// Only specific tables
const plugin = auditPlugin({
  tables: ['users', 'orders', 'payments'],
  getUserId: () => currentUser?.id
})

// Custom audit table name
const plugin = auditPlugin({
  auditTable: 'change_history',
  getUserId: () => currentUser?.id
})

Repository Methods

When a repository is extended by the audit plugin, the following methods are added:

AuditMethods Interface

interface AuditMethods<T> {
  /**
   * Get change history for an entity
   */
  getAuditHistory(
    entityId: string | number,
    options?: { limit?: number; offset?: number }
  ): Promise<AuditLogEntry[]>

  /**
   * Alias for getAuditHistory
   */
  getAuditLogs(
    entityId: string | number,
    options?: { limit?: number; offset?: number }
  ): Promise<AuditLogEntry[]>

  /**
   * Get a specific audit log entry by ID
   */
  getAuditLog(auditId: number): Promise<AuditLogEntry | null>

  /**
   * Get all audit logs for this table with optional filters
   */
  getTableAuditLogs(filters?: AuditFilters): Promise<AuditLogEntry[]>

  /**
   * Get all changes made by a specific user
   */
  getUserChanges(
    userId: string | number,
    options?: { limit?: number; offset?: number }
  ): Promise<AuditLogEntry[]>

  /**
   * Restore entity to a previous state from an audit log
   */
  restoreFromAudit(auditId: number): Promise<T>
}

getAuditHistory

Get the change history for a specific entity.

async getAuditHistory(
  entityId: string | number,
  options?: { limit?: number; offset?: number }
): Promise<AuditLogEntry[]>

Parameters:

• entityId - Primary key of the entity
• options.limit - Maximum number of entries
• options.offset - Number of entries to skip

Returns: Array of audit log entries, most recent first

Example:

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

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

// Access changes
history.forEach(entry => {
  console.log(`${entry.operation} by ${entry.changed_by} at ${entry.changed_at}`)
  if (entry.old_values) {
    console.log('Before:', JSON.parse(entry.old_values))
  }
  if (entry.new_values) {
    console.log('After:', JSON.parse(entry.new_values))
  }
})

getAuditLogs

Alias for getAuditHistory.

async getAuditLogs(
  entityId: string | number,
  options?: { limit?: number; offset?: number }
): Promise<AuditLogEntry[]>

getAuditLog

Get a specific audit log entry.

async getAuditLog(auditId: number): Promise<AuditLogEntry | null>

Parameters:

• auditId - ID of the audit log entry

Returns: The audit log entry or null

Example:

const entry = await userRepo.getAuditLog(auditLogId)
if (entry) {
  console.log(`Operation: ${entry.operation}`)
  console.log(`Changed by: ${entry.changed_by}`)
}

getTableAuditLogs

Get all audit logs for the table with optional filters.

async getTableAuditLogs(filters?: AuditFilters): Promise<AuditLogEntry[]>

Parameters:

• filters - Optional filters for the query

Returns: Array of audit log entries matching the filters

Example:

// Get all audit logs for the users table
const allLogs = await userRepo.getTableAuditLogs()

// Filter by operation type
const insertLogs = await userRepo.getTableAuditLogs({
  operation: 'INSERT'
})

// Filter by date range
const recentLogs = await userRepo.getTableAuditLogs({
  fromDate: new Date('2024-01-01'),
  toDate: new Date('2024-01-31')
})

// Filter by user
const userLogs = await userRepo.getTableAuditLogs({
  changedBy: 'user-123'
})

// Combine multiple filters with pagination
const filteredLogs = await userRepo.getTableAuditLogs({
  operation: 'UPDATE',
  changedBy: 'admin',
  limit: 50,
  offset: 0
})

getUserChanges

Get all changes made by a specific user across this table.

async getUserChanges(
  userId: string | number,
  options?: { limit?: number; offset?: number }
): Promise<AuditLogEntry[]>

Parameters:

• userId - ID of the user whose changes to retrieve
• options.limit - Maximum number of entries
• options.offset - Number of entries to skip

Returns: Array of audit log entries for the user

Example:

// Get all changes made by a specific user
const userChanges = await userRepo.getUserChanges('admin-user-id')

// With pagination
const recentChanges = await userRepo.getUserChanges('admin-user-id', {
  limit: 20,
  offset: 0
})

// Review what a user changed
userChanges.forEach(entry => {
  console.log(`${entry.operation} on entity ${entry.entity_id}`)
  console.log(`At: ${entry.changed_at}`)
})

restoreFromAudit

Restore an entity to a previous state from an audit log entry.

async restoreFromAudit(auditId: number): Promise<T>

Parameters:

• auditId - ID of the audit log entry to restore from

Returns: The restored entity

Example:

// Restore user to 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

AuditLogEntry

Structure of audit log entries.

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

type AuditOperation = 'INSERT' | 'UPDATE' | 'DELETE'

AuditFilters

Filters for querying audit logs.

interface AuditFilters {
  /** Filter by operation type */
  operation?: AuditOperation
  /** Filter by user who made the change */
  changedBy?: string | number
  /** Filter changes from this date */
  fromDate?: Date | string
  /** Filter changes until this date */
  toDate?: Date | string
  /** Maximum number of entries to return */
  limit?: number
  /** Number of entries to skip */
  offset?: number
}

Parsed Values

When accessing old_values and new_values, parse them as JSON:

const history = await userRepo.getAuditHistory(userId)

history.forEach(entry => {
  const oldValues = entry.old_values ? JSON.parse(entry.old_values) : null
  const newValues = entry.new_values ? JSON.parse(entry.new_values) : null

  console.log('Changed from:', oldValues)
  console.log('Changed to:', newValues)
})

Automatic Audit Logging

The plugin automatically logs changes for INSERT, UPDATE, and DELETE operations:

On INSERT

await userRepo.create({ email: 'john@example.com', name: 'John' })

// Audit log entry:
// {
//   operation: 'INSERT',
//   old_values: null,
//   new_values: '{"email":"john@example.com","name":"John","id":1}'
// }

On UPDATE

await userRepo.update(userId, { name: 'John Smith' })

// Audit log entry:
// {
//   operation: 'UPDATE',
//   old_values: '{"name":"John"}',
//   new_values: '{"name":"John Smith"}'
// }

On DELETE

await userRepo.delete(userId)

// Audit log entry:
// {
//   operation: 'DELETE',
//   old_values: '{"id":1,"email":"john@example.com","name":"John Smith"}',
//   new_values: null
// }

Query Interception

The plugin intercepts operations to capture changes:

// Plugin implementation (simplified)
async interceptQuery(qb, context) {
  if (context.operation === 'update') {
    // Capture old values before update
    const oldValues = await fetchCurrentValues(context.entityId)

    // Execute update
    const result = await qb.execute()

    // Log audit entry
    await logAuditEntry({
      operation: 'UPDATE',
      oldValues,
      newValues: context.data
    })

    return result
  }
}

Usage with Plugin Container

import { createORM, createRepositoryFactory } from '@kysera/repository'
import { auditPlugin } from '@kysera/audit'
import { z } from 'zod'

// createORM creates a plugin container (repository manager), not a traditional ORM
const orm = await createORM(db, [
  auditPlugin({
    getUserId: () => currentUser?.id,
    captureOldValues: true,
    captureNewValues: true
  })
])

const userRepo = orm.createRepository((executor) => {
  const factory = createRepositoryFactory(executor)
  return factory.create({
    tableName: 'users',
    mapRow: (row) => ({
      id: row.id,
      email: row.email,
      name: row.name
    }),
    schemas: {
      create: z.object({
        email: z.string().email(),
        name: z.string()
      })
    }
  })
})

// 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)

Database-Specific Plugins

For optimized performance, use database-specific variants:

// PostgreSQL - uses JSONB
import { auditPluginPostgreSQL } from '@kysera/audit'

// MySQL - uses JSON type
import { auditPluginMySQL } from '@kysera/audit'

// SQLite - uses TEXT with JSON
import { auditPluginSQLite } from '@kysera/audit'

const orm = await createORM(db, [
  auditPluginPostgreSQL({
    getUserId: () => currentUser?.id
  })
])

Database Schema

Create the audit_logs table:

-- PostgreSQL
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);

-- MySQL
CREATE TABLE audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  table_name VARCHAR(255) NOT NULL,
  entity_id VARCHAR(255) NOT NULL,
  operation VARCHAR(10) NOT NULL,
  old_values JSON,
  new_values JSON,
  changed_by VARCHAR(255),
  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  metadata JSON
);

-- SQLite
CREATE TABLE audit_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  table_name TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  operation TEXT NOT NULL,
  old_values TEXT,
  new_values TEXT,
  changed_by TEXT,
  changed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  metadata TEXT
);

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

The audit plugin optimizes bulk operations:

// 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' } }
])

// Execution:
// 1 query to fetch old values (SELECT ... WHERE id IN (1, 2, 3))
// 1 query to update
// 1 query to insert audit logs (bulk insert)

TypeScript Types

AuditRepository

type AuditRepository<Entity, DB> = Repository<Entity, DB> & AuditMethods<Entity>

AuditLogEntry

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

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'],
    requestId: request.id
  })
})

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');

CREATE TABLE audit_logs_2024_q2 PARTITION OF audit_logs
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-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';

5. Use Indexes for Common Queries

-- Essential indexes
CREATE INDEX idx_audit_logs_table_entity ON audit_logs(table_name, entity_id);
CREATE INDEX idx_audit_logs_changed_at ON audit_logs(changed_at DESC);

-- Optional indexes based on usage
CREATE INDEX idx_audit_logs_changed_by ON audit_logs(changed_by);
CREATE INDEX idx_audit_logs_operation ON audit_logs(operation);

Performance Considerations

Memory Management

• Old/new values are captured per operation
• Use captureOldValues: false if not needed
• Use captureNewValues: false if not needed

Query Optimization

• Bulk operations use single queries for old values
• Audit entries are bulk-inserted
• Indexes on (table_name, entity_id) are essential

Storage Growth

• Audit logs grow with every change
• Plan for archival or partitioning
• Consider TTL policies for old entries

Combining with Other Plugins

const orm = await createORM(db, [
  timestampsPlugin(),     // Auto timestamps
  softDeletePlugin(),     // Soft delete (audited)
  auditPlugin({           // Comprehensive audit
    getUserId: () => currentUser?.id,
    excludeTables: ['audit_logs']
  })
])

// All plugins work together:
// - Soft delete creates audit entry
// - Timestamps are included in audit values

See Also

• Audit Plugin Guide
• @kysera/repository
• @kysera/soft-delete