# @bernierllc/database-adapter-core

Abstract Database Interface for MECE Architecture - provides database-agnostic interfaces and base implementations for all database operations across BernierLLC tools.

## Features

- **Abstract Database Interfaces** - Clean, database-agnostic interfaces for all database operations
- **Base Implementations** - Common functionality through abstract base classes
- **Row Level Security (RLS)** - Built-in support for multi-tenant security and user-based access control
- **Query Builder Foundation** - Type-safe SQL generation without string concatenation
- **Migration System** - Schema migration tracking and execution
- **Transaction Support** - ACID transaction interfaces
- **Connection Pooling** - Interface for connection pool management
- **Error Handling** - Structured error classes with sensitive data sanitization
- **Type-Safe** - Full TypeScript support with strict mode
- **Zero Runtime Overhead** - Pure interfaces and abstract classes

## Installation

```bash
npm install @bernierllc/database-adapter-core
```

## Usage

### Creating a Database Adapter

Extend `BaseDatabaseAdapter` to create a concrete database implementation:

```typescript
import {
  BaseDatabaseAdapter,
  IDatabaseConfig,
  IQueryResult,
  ITransaction,
  IDatabaseHealth,
  ConnectionError
} from '@bernierllc/database-adapter-core';

class PostgreSQLAdapter extends BaseDatabaseAdapter {
  private pool: pg.Pool;

  async connect(): Promise<void> {
    try {
      this.pool = new pg.Pool({
        host: this.config.host,
        port: this.config.port,
        database: this.config.database,
        user: this.config.username,
        password: this.config.password
      });

      await this.pool.connect();
      this.connected = true;
    } catch (error) {
      throw new ConnectionError(
        'Failed to connect to PostgreSQL',
        'CONNECTION_FAILED',
        error as Error,
        this.config.host,
        this.config.port
      );
    }
  }

  async disconnect(): Promise<void> {
    await this.pool.end();
    this.connected = false;
  }

  async query<T>(sql: string, params?: any[]): Promise<IQueryResult<T>> {
    this.ensureConnected();

    try {
      const result = await this.pool.query(sql, params);
      return {
        rows: result.rows as T[],
        rowCount: result.rowCount || 0,
        fields: result.fields.map(f => ({
          name: f.name,
          dataTypeID: f.dataTypeID,
          dataTypeName: f.dataTypeName,
          nullable: true
        })),
        command: result.command
      };
    } catch (error) {
      throw this.sanitizeError(error);
    }
  }

  async beginTransaction(): Promise<ITransaction> {
    this.ensureConnected();
    // Implementation details...
  }

  async healthCheck(): Promise<IDatabaseHealth> {
    const start = Date.now();
    await this.query('SELECT 1');
    const latencyMs = Date.now() - start;

    return {
      connected: this.connected,
      latencyMs,
      version: '15.0',
      activeConnections: this.pool.totalCount,
      maxConnections: this.pool.options.max
    };
  }
}
```

### Using Row Level Security (RLS)

```typescript
import { RLSContextManager, IRLSContext } from '@bernierllc/database-adapter-core';

const rlsManager = new RLSContextManager();

// Set RLS context for multi-tenant security
const context: IRLSContext = {
  userId: 'user-123',
  userRole: 'admin',
  tenantId: 'tenant-456',
  claims: { department: 'engineering' }
};

// Execute operations with RLS context
await rlsManager.withContext(context, async () => {
  // All database operations here will have RLS context applied
  const users = await adapter.query('SELECT * FROM users');
  // PostgreSQL RLS policies will automatically filter based on context
});

// Generate PostgreSQL SET statements for RLS
rlsManager.setContext(context);
const statements = rlsManager.generateRLSStatements();
// [
//   'SET LOCAL "app.user_id" = \'user-123\';',
//   'SET LOCAL "app.user_role" = \'admin\';',
//   'SET LOCAL "app.tenant_id" = \'tenant-456\';',
//   'SET LOCAL "app.claims" = \'{"department":"engineering"}\';'
// ]
```

### Building Type-Safe Queries

```typescript
import { BaseQueryBuilder } from '@bernierllc/database-adapter-core';

class PostgreSQLQueryBuilder extends BaseQueryBuilder<User> {
  insert(data: Partial<User>): this {
    // Implementation for INSERT
    return this;
  }

  update(data: Partial<User>): this {
    // Implementation for UPDATE
    return this;
  }

  delete(): this {
    // Implementation for DELETE
    return this;
  }

  build(): { sql: string; params: any[] } {
    // Build PostgreSQL-specific SQL
    let sql = `SELECT ${this._select.join(', ')} FROM ${this._from}`;
    if (this._where.length > 0) {
      sql += ` WHERE ${this._where.join(' AND ')}`;
    }
    return { sql, params: this._params };
  }
}

// Usage
const query = new PostgreSQLQueryBuilder()
  .select('id', 'name', 'email')
  .from('users')
  .where('age > $1', [18])
  .where('status = $2', ['active'])
  .orderBy('name', 'ASC')
  .limit(10)
  .offset(20);

const result = await query.execute(adapter);
```

### Managing Migrations

```typescript
import {
  BaseMigrationManager,
  IMigration,
  IDatabaseAdapter
} from '@bernierllc/database-adapter-core';

const migrations: IMigration[] = [
  {
    id: '001',
    name: 'create_users_table',
    async up(adapter: IDatabaseAdapter) {
      await adapter.query(`
        CREATE TABLE users (
          id SERIAL PRIMARY KEY,
          name VARCHAR(255) NOT NULL,
          email VARCHAR(255) UNIQUE NOT NULL,
          created_at TIMESTAMPTZ DEFAULT NOW()
        )
      `);
    },
    async down(adapter: IDatabaseAdapter) {
      await adapter.query('DROP TABLE users');
    }
  },
  {
    id: '002',
    name: 'add_users_status',
    async up(adapter: IDatabaseAdapter) {
      await adapter.query(`
        ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'active'
      `);
    },
    async down(adapter: IDatabaseAdapter) {
      await adapter.query('ALTER TABLE users DROP COLUMN status');
    }
  }
];

const migrationManager = new BaseMigrationManager(adapter, migrations);

// Run pending migrations
await migrationManager.up();

// Get migration status
const status = await migrationManager.status();
console.log(status);
// [
//   { id: '001', name: 'create_users_table', status: 'applied', appliedAt: Date },
//   { id: '002', name: 'add_users_status', status: 'applied', appliedAt: Date }
// ]

// Rollback last migration
await migrationManager.down();
```

### Error Handling

```typescript
import {
  DatabaseError,
  ConnectionError,
  QueryError
} from '@bernierllc/database-adapter-core';

try {
  await adapter.connect();
} catch (error) {
  if (error instanceof ConnectionError) {
    console.error(`Connection failed: ${error.host}:${error.port}`);
    console.error(`Error code: ${error.code}`);
  }
}

try {
  await adapter.query('SELECT * FROM users WHERE id = $1', [userId]);
} catch (error) {
  if (error instanceof QueryError) {
    console.error(`Query failed: ${error.query}`);
    console.error(`Parameters: ${error.params}`);
  }
}
```

## API Reference

### Interfaces

#### `IDatabaseAdapter`
Main adapter interface that all concrete adapters must implement.

**Methods:**
- `connect(): Promise<void>` - Connect to database
- `disconnect(): Promise<void>` - Disconnect from database
- `isConnected(): boolean` - Check connection status
- `query<T>(sql: string, params?: any[]): Promise<IQueryResult<T>>` - Execute query
- `beginTransaction(): Promise<ITransaction>` - Begin transaction
- `healthCheck(): Promise<IDatabaseHealth>` - Check database health

#### `ITransaction`
Transaction interface for ACID operations.

**Methods:**
- `query<T>(sql: string, params?: any[]): Promise<IQueryResult<T>>` - Execute query in transaction
- `commit(): Promise<void>` - Commit transaction
- `rollback(): Promise<void>` - Rollback transaction
- `isActive(): boolean` - Check if transaction is active

#### `IQueryResult<T>`
Query result with metadata.

**Properties:**
- `rows: T[]` - Result rows
- `rowCount: number` - Number of rows returned
- `fields: IFieldInfo[]` - Field metadata
- `command: string` - SQL command (SELECT, INSERT, UPDATE, DELETE)

#### `IDatabaseConfig`
Database connection configuration.

**Properties:**
- `type: 'postgresql' | 'mysql' | 'sqlite' | 'mssql'` - Database type
- `host?: string` - Host address
- `port?: number` - Port number
- `database: string` - Database name
- `username?: string` - Username
- `password?: string` - Password
- `ssl?: boolean | object` - SSL configuration
- `pooling?: object` - Connection pooling settings

#### `IRLSContext`
Row Level Security context.

**Properties:**
- `userId: string` - User ID
- `userRole: string` - User role
- `tenantId?: string` - Tenant ID (optional)
- `claims?: Record<string, any>` - Additional claims (optional)

### Base Classes

#### `BaseDatabaseAdapter`
Abstract base class providing common adapter functionality.

**Methods:**
- `isConnected(): boolean` - Returns connection status
- `ensureConnected(): void` - Throws error if not connected
- `sanitizeError(error: unknown): Error` - Removes sensitive data from errors

#### `BaseQueryBuilder<T>`
Abstract query builder with fluent API.

**Methods:**
- `select(...columns: string[]): this`
- `from(table: string): this`
- `where(condition: string, params?: readonly unknown[]): this`
- `whereIn(column: string, values: readonly unknown[]): this`
- `join(table: string, condition: string): this`
- `leftJoin(table: string, condition: string): this`
- `orderBy(column: string, direction?: 'ASC' | 'DESC'): this`
- `limit(count: number): this`
- `offset(count: number): this`
- `execute(adapter: IDatabaseAdapter): Promise<IQueryResult<T>>`

#### `BaseMigrationManager`
Migration manager for schema changes.

**Methods:**
- `up(): Promise<void>` - Run pending migrations
- `down(): Promise<void>` - Rollback last migration
- `status(): Promise<IMigrationStatus[]>` - Get migration status

### Utilities

#### `RLSContextManager`
Row Level Security context manager.

**Methods:**
- `setContext(context: IRLSContext): void` - Set RLS context
- `getContext(): IRLSContext | null` - Get current context
- `clearContext(): void` - Clear context
- `withContext<T>(context: IRLSContext, fn: () => Promise<T>): Promise<T>` - Execute with context
- `generateRLSStatements(): string[]` - Generate PostgreSQL SET statements

### Error Classes

- `DatabaseError` - Base database error
- `ConnectionError` - Connection-specific errors
- `QueryError` - Query-specific errors

## Integration Status

### Logger Integration
**Status**: Optional - Can be integrated for error logging and query tracing

Adapters can optionally use `@bernierllc/logger` for:
- Connection lifecycle logging
- Query execution tracing
- Error logging with context

### Docs-Suite Integration
**Status**: Ready

This package includes complete JSDoc/TypeDoc comments and can be integrated with documentation generators:
- All public APIs documented
- Interface documentation complete
- Examples included

### NeverHub Integration
**Status**: Not Applicable

This is a pure interface package with no runtime behavior requiring service discovery or event communication.

## Dependencies

- `@bernierllc/logger` - Structured logging (optional usage)
- `@bernierllc/crypto-utils` - For secure token generation in adapters

## Testing

This package has comprehensive test coverage:
- **90.27%** statement coverage
- **90.47%** branch coverage
- **100%** function coverage
- **90%** line coverage
- **65 passing tests**

Run tests:
```bash
npm test              # Watch mode
npm run test:run      # Single run
npm run test:coverage # With coverage report
```

## Building

```bash
npm run build   # Compile TypeScript
npm run lint    # Run ESLint
npm run clean   # Remove build artifacts
```

## Related Packages

### Depends On
- [@bernierllc/logger](../logger) - Structured logging
- [@bernierllc/crypto-utils](../crypto-utils) - Cryptographic utilities

### Used By
- `@bernierllc/database-adapter-postgresql` - PostgreSQL implementation (coming soon)
- `@bernierllc/content-storage-adapter` - Content CRUD operations (coming soon)
- All packages requiring database access

### Part Of
- **content-management-suite** - Database foundation layer

## Design Philosophy

This package follows MECE (Mutually Exclusive, Collectively Exhaustive) architecture principles:

- **Abstract Interfaces** - No concrete database implementations
- **Single Responsibility** - Pure interface definitions and base utilities
- **Zero Coupling** - Implementations are separate packages
- **Adapter Pattern** - Clean abstraction layer for all database operations

By keeping interfaces separate from implementations, we enable:
- **Pluggable Backends** - Switch databases without changing application code
- **Testability** - Mock adapters easily for testing
- **Flexibility** - Support multiple databases simultaneously
- **Maintainability** - Changes to one adapter don't affect others

## License

Copyright (c) 2025 Bernier LLC. All rights reserved.

This file is licensed to the client under a limited-use license. The client may use and modify this code *only within the scope of the project it was delivered for*. Redistribution or use in other products or commercial offerings is not permitted without written consent from Bernier LLC.