@leather-erp/dba

Database Administration module for Leather ERP — a production-grade, multi-tenant leather garments ERP platform.

Features

• Tenant Manager — Provision, suspend, reactivate, and delete multi-tenant databases with Row-Level Security
• Backup Manager — Create, restore, list, and cleanup PostgreSQL backups via pg_dump/pg_restore
• Health Checker — Full database health checks: connection, query performance, replication lag, disk space, long queries, table bloat
• Migration Manager — Track migration status, run pending, revert, create new, validate schema
• RLS Manager — Manage Row-Level Security policies, enable/disable per table, generate tenant isolation migrations
• Database Analyzer — Table sizes, index usage, slow queries, unused indexes, optimization suggestions
• Audit Manager — Query and manage audit logs, track changes, cleanup old logs

Installation

npm install @leather-erp/dba

Quick Start

import { PrismaClient } from '@prisma/client';
import {
  TenantManager,
  BackupManager,
  HealthChecker,
  RLSManager,
  DatabaseAnalyzer,
} from '@leather-erp/dba';

const prisma = new PrismaClient();

// Provision a new tenant
const tenantManager = new TenantManager(prisma);
const tenant = await tenantManager.provisionTenant({
  slug: 'acme-leather',
  name: 'Acme Leather Co.',
  plan: 'professional',
  maxUsers: 25,
  features: ['cutting', 'production', 'export'],
});

// Health check
const healthChecker = new HealthChecker(prisma);
const health = await healthChecker.runFullCheck();
console.log(`Status: ${health.status}`);

// Database analysis
const analyzer = new DatabaseAnalyzer(prisma);
const suggestions = await analyzer.suggestOptimizations();
for (const s of suggestions) console.log(`• ${s}`);

// Backup
const backupManager = new BackupManager(prisma, process.env.DATABASE_URL!);
const backup = await backupManager.createBackup({ compress: true });
console.log(`Backup created: ${backup.filename} (${backup.sizeBytes} bytes)`);

API Reference

TenantManager

const manager = new TenantManager(prisma);

// Provision
const result = await manager.provisionTenant({ slug, name, plan, maxUsers, features });

// Suspend / Reactivate
await manager.suspendTenant(tenantId);
await manager.reactivateTenant(tenantId);

// List
const tenants = await manager.listTenants();

// Usage stats
const usage = await manager.getTenantUsage(tenantId);

BackupManager

const backup = new BackupManager(prisma, databaseUrl, './backups');

// Create backup
const result = await backup.createBackup({ format: 'custom', compress: true });

// Restore
await backup.restoreBackup({ backupFilePath: result.filePath, dropExisting: false });

// List & cleanup
const backups = await backup.listBackups();
await backup.cleanupOldBackups(10); // keep last 10

HealthChecker

const checker = new HealthChecker(prisma);

const health = await checker.runFullCheck();
// Returns: { status: 'healthy' | 'degraded' | 'unhealthy', checks: [...] }

const stats = await checker.getDatabaseStats();
// Returns connection count, cache hit ratio, deadlocks, etc.

RLSManager

const rls = new RLSManager(prisma);

// Check status
const status = await rls.getStatus();

// Enable tenant isolation
await rls.ensureTenantRLS(tenantId);

// Generate migration SQL
const sql = await rls.generateRLSMigration();

DatabaseAnalyzer

const analyzer = new DatabaseAnalyzer(prisma);

const tables = await analyzer.getTableSizes();
const indexes = await analyzer.getIndexUsage();
const slow = await analyzer.getSlowQueries(10);
const suggestions = await analyzer.suggestOptimizations();

AuditManager

const audit = new AuditManager(prisma);

await audit.ensureAuditTable();

await audit.log({
  tenantId, userId, action: 'CREATE',
  tableName: 'hides', recordId: hideId,
  newValues: { species: 'BOVINE', grade: 'A' },
});

const recent = await audit.getRecentActivity(tenantId);
const stats = await audit.getAuditStats(tenantId);

Requirements

• Node.js >= 20.0.0
• PostgreSQL >= 14
• Prisma >= 5.0.0
• pg_dump / pg_restore on PATH (for backup/restore features)

License

MIT