# @bernierllc/database-adapter-postgresql

PostgreSQL/Supabase database adapter with Row Level Security, real-time subscriptions, and full-text search.

## Installation

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

## Features

- **Connection Pooling** - Efficient connection management with pg Pool
- **Row Level Security** - Multi-tenant security with Clerk JWT integration
- **Real-time Subscriptions** - Supabase real-time for collaborative editing
- **Full-Text Search** - pg_trgm fuzzy matching and ts_vector search
- **Transaction Support** - ACID transactions with savepoints
- **Automatic Retries** - Connection retry with exponential backoff
- **Type Safety** - Strict TypeScript with comprehensive type definitions
- **Logging Integration** - Structured logging with @bernierllc/logger

## Usage

### Basic Connection

```typescript
import { PostgreSQLAdapter } from '@bernierllc/database-adapter-postgresql';

const adapter = new PostgreSQLAdapter({
  type: 'postgresql',
  host: 'localhost',
  port: 5432,
  database: 'myapp',
  username: 'postgres',
  password: 'password',
  pooling: {
    min: 2,
    max: 10
  }
});

await adapter.connect();
```

### With Supabase

```typescript
const adapter = new PostgreSQLAdapter({
  type: 'postgresql',
  host: 'db.project.supabase.co',
  port: 5432,
  database: 'postgres',
  username: 'postgres',
  password: 'password',
  ssl: true,
  supabaseUrl: 'https://project.supabase.co',
  supabaseAnonKey: 'your-anon-key'
});
```

### Query Execution

```typescript
// Simple query
const result = await adapter.query('SELECT * FROM users WHERE id = $1', ['user-id']);

// Typed query
interface User {
  id: string;
  email: string;
  role: string;
}

const users = await adapter.query<User>('SELECT * FROM users');
console.log(users.rows[0].email);
```

### Transactions

```typescript
const txn = await adapter.beginTransaction();

try {
  await txn.query('INSERT INTO users (email) VALUES ($1)', ['user@example.com']);
  await txn.query('INSERT INTO profiles (user_id) VALUES ($1)', ['user-id']);

  await txn.commit();
} catch (error) {
  await txn.rollback();
  throw error;
}
```

### Savepoints

```typescript
const txn = await adapter.beginTransaction();

await txn.query('INSERT INTO content (title) VALUES ($1)', ['Draft 1']);
await txn.savepoint('draft1');

await txn.query('INSERT INTO content (title) VALUES ($1)', ['Draft 2']);

// Rollback to savepoint
await txn.rollbackToSavepoint('draft1');

await txn.commit(); // Only 'Draft 1' is committed
```

### Row Level Security

```typescript
import { RLSPolicyGenerator } from '@bernierllc/database-adapter-postgresql';

// Generate user-scoped policy
const policy = RLSPolicyGenerator.generateUserPolicy('content');
await adapter.query(policy);

// Set RLS context for queries
adapter.setRLSContext('user_123', 'user', 'tenant_456');

// All subsequent queries respect RLS policies
const content = await adapter.query('SELECT * FROM content');
// Only returns content for user_123 in tenant_456
```

### Clerk Integration

```typescript
import { ClerkIntegration } from '@bernierllc/database-adapter-postgresql';

// Set up Clerk authentication functions
const setup = ClerkIntegration.generateCompleteSetup();
await adapter.query(setup);

// RLS policies automatically use Clerk JWT
const policy = RLSPolicyGenerator.generateUserPolicy('content');
await adapter.query(policy);
```

### Real-time Subscriptions

```typescript
import { RealtimeManager } from '@bernierllc/database-adapter-postgresql';

const realtimeManager = new RealtimeManager(adapter);

// Subscribe to content changes
const unsubscribe = realtimeManager.subscribeToContent('content-id', {
  onInsert: (payload) => {
    console.log('New version created:', payload.new);
  },
  onUpdate: (payload) => {
    console.log('Content updated:', payload.new);
  },
  onDelete: (payload) => {
    console.log('Content deleted:', payload.old);
  }
});

// Broadcast presence for collaborative editing
await realtimeManager.broadcastPresence('content-id', 'user-id', {
  line: 10,
  column: 5
});

// Get current presence state
const presence = realtimeManager.getPresence('content-id');

// Cleanup
unsubscribe();
```

### Full-Text Search

```typescript
import { FullTextSearchManager } from '@bernierllc/database-adapter-postgresql';

const searchManager = new FullTextSearchManager(adapter);

// Create indexes first
await searchManager.createIndexes();

// Fuzzy search with typo tolerance
const results = await searchManager.searchContent('databse', {
  minSimilarity: 0.3,
  limit: 10
});

results.forEach(result => {
  console.log(result.title, 'Relevance:', result.totalSimilarity);
});

// Combined search (pg_trgm + ts_vector)
const combined = await searchManager.searchCombined('PostgreSQL performance', {
  minSimilarity: 0.2,
  limit: 20
});

// Search specific fields
const titleResults = await searchManager.searchContent('guide', {
  fields: ['title'],
  minSimilarity: 0.4
});
```

### Migrations

```typescript
import { migration_001_initial_schema } from '@bernierllc/database-adapter-postgresql';

// Run migration
await migration_001_initial_schema.up(adapter);

// Rollback
await migration_001_initial_schema.down(adapter);
```

### Health Checks

```typescript
const health = await adapter.healthCheck();

console.log('Connected:', health.connected);
console.log('Latency:', health.latencyMs, 'ms');
console.log('Version:', health.version);
console.log('Active connections:', health.activeConnections);
console.log('Max connections:', health.maxConnections);
```

### Pool Statistics

```typescript
const stats = adapter.getPoolStats();

console.log('Total connections:', stats.total);
console.log('Idle connections:', stats.idle);
console.log('Waiting requests:', stats.waiting);
```

## API Reference

### PostgreSQLAdapter

Main adapter class extending `BaseDatabaseAdapter` from `@bernierllc/database-adapter-core`.

#### Methods

- `connect(): Promise<void>` - Connect to PostgreSQL with retry
- `disconnect(): Promise<void>` - Close connection pool
- `query<T>(sql: string, params?: any[]): Promise<IQueryResult<T>>` - Execute query
- `beginTransaction(): Promise<ITransaction>` - Start transaction
- `healthCheck(): Promise<IDatabaseHealth>` - Check connection health
- `setRLSContext(userId: string, userRole: string, tenantId?: string): void` - Set RLS context
- `clearRLSContext(): void` - Clear RLS context
- `getSupabaseClient(): SupabaseClient | null` - Get Supabase client
- `getPoolStats(): object | null` - Get connection pool statistics

### PostgreSQLTransaction

Transaction implementation with savepoint support.

#### Methods

- `query<T>(sql: string, params?: any[]): Promise<IQueryResult<T>>` - Execute query in transaction
- `commit(): Promise<void>` - Commit transaction
- `rollback(): Promise<void>` - Rollback transaction
- `savepoint(name: string): Promise<void>` - Create savepoint
- `rollbackToSavepoint(name: string): Promise<void>` - Rollback to savepoint
- `releaseSavepoint(name: string): Promise<void>` - Release savepoint
- `isActive(): boolean` - Check if transaction is active

### RLSPolicyGenerator

Static utility for generating RLS policies.

#### Methods

- `generateUserPolicy(table: string): string` - User-scoped access
- `generateAdminPolicy(table: string): string` - Admin bypass
- `generateTenantPolicy(table: string): string` - Multi-tenant isolation
- `generatePublicReadPolicy(table: string, condition?: string): string` - Public read access
- `generateSharedResourcePolicy(table: string, sharingTable?: string): string` - Shared resources
- `generateRoleBasedPolicy(table: string, roles: string[]): string` - Role-based access
- `generateTimeBasedPolicy(table: string): string` - Time-based access
- `disableRLS(table: string): string` - Disable RLS (use with caution)

### ClerkIntegration

Static utility for Clerk JWT integration.

#### Methods

- `generateCompleteSetup(): string` - Generate all functions and schema
- `generateAuthSchema(): string` - Create auth schema
- `generateClerkUserIdFunction(): string` - Extract Clerk user ID
- `generateUserIdFunction(): string` - Get internal user ID
- `generateIsAdminFunction(): string` - Check admin status
- `generateUserRoleFunction(): string` - Get user role
- `generateHasRoleFunction(): string` - Check specific role
- `generateUserTenantIdFunction(): string` - Get user tenant ID

### RealtimeManager

Manages Supabase real-time subscriptions.

#### Methods

- `subscribeToContent(contentId: string, callbacks: RealtimeCallbacks): () => void` - Subscribe to content
- `subscribeToTable(table: string, filter: string | null, callbacks: RealtimeCallbacks): () => void` - Subscribe to table
- `broadcastPresence(contentId: string, userId: string, cursorPosition?: object): Promise<void>` - Broadcast presence
- `getPresence(contentId: string): object | null` - Get presence state
- `unsubscribe(contentId: string): Promise<void>` - Unsubscribe from channel
- `unsubscribeAll(): Promise<void>` - Unsubscribe from all channels

### FullTextSearchManager

PostgreSQL full-text search with pg_trgm.

#### Methods

- `searchContent(query: string, options?: SearchOptions): Promise<SearchResult[]>` - Fuzzy search
- `searchWithTsVector(query: string, options?: SearchOptions): Promise<any[]>` - ts_vector search
- `searchCombined(query: string, options?: SearchOptions): Promise<SearchResult[]>` - Combined search
- `createIndexes(): Promise<void>` - Create search indexes
- `dropIndexes(): Promise<void>` - Drop search indexes
- `getSearchStats(): Promise<object>` - Get index statistics

## Configuration

### IDatabaseConfig

```typescript
interface IDatabaseConfig {
  type: 'postgresql';
  host: string;
  port?: number; // Default: 5432
  database: string;
  username: string;
  password: string;
  ssl?: boolean | object;
  pooling?: {
    min?: number; // Default: 2
    max?: number; // Default: 10
    idleTimeoutMs?: number; // Default: 30000
  };
  connectionTimeoutMs?: number; // Default: 5000
}
```

### ISupabaseConfig

```typescript
interface ISupabaseConfig {
  supabaseUrl?: string;
  supabaseAnonKey?: string;
  supabaseServiceKey?: string;
}
```

## Integration Status

- **Logger**: ✅ Integrated - Uses @bernierllc/logger for structured logging
- **Docs-Suite**: ✅ Ready - TypeDoc API documentation, complete README
- **NeverHub**: 🔔 Optional - Health metrics and pool statistics publishing

### NeverHub Integration

This package provides **optional** NeverHub integration for enhanced observability:

**What NeverHub Provides:**
- Real-time connection pool monitoring
- Query performance metrics aggregation
- Database health status broadcasting
- Automatic service discovery for database adapters

**Integration Approach:**
- **Graceful Degradation**: All core functionality works without NeverHub
- **Auto-Detection**: Package detects NeverHub availability at runtime
- **Opt-In Publishing**: Metrics are published only when NeverHub is present

**Usage Example:**
```typescript
import { PostgreSQLAdapter } from '@bernierllc/database-adapter-postgresql';
import { NeverHubAdapter } from '@bernierllc/neverhub-adapter';

const adapter = new PostgreSQLAdapter({ /* config */ });
await adapter.connect();

// Optional: Publish metrics to NeverHub if available
if (await NeverHubAdapter.detect()) {
  const neverhub = new NeverHubAdapter();
  await neverhub.register({
    type: 'database-adapter',
    name: '@bernierllc/database-adapter-postgresql',
    capabilities: [
      { type: 'storage', name: 'postgresql', version: '1.0.0' }
    ]
  });

  // Publish pool statistics
  setInterval(async () => {
    const stats = adapter.getPoolStats();
    await neverhub.publishEvent({
      type: 'database.pool.stats',
      data: stats
    });
  }, 30000); // Every 30 seconds
}
```

**Metrics Published:**
- `database.pool.stats` - Connection pool statistics (total, idle, waiting)
- `database.health` - Health check results (connected, latency, version)
- `database.query.performance` - Query execution metrics (optional, requires instrumentation)

**Why Optional:**
- Core packages should minimize dependencies
- Database operations must work in all environments
- NeverHub primarily benefits distributed systems with multiple services
- Single-instance applications don't need service discovery

## Performance

- **Query Latency**: <5ms (p95) for indexed queries
- **Connection Pool Reuse**: >90%
- **Real-time Latency**: <100ms for subscriptions
- **Search**: Fuzzy matching with typo tolerance via pg_trgm

## Security

- **SQL Injection Protection**: Parameterized queries only
- **RLS Enforcement**: Row-level security for all user data
- **Clerk JWT Verification**: Automatic user authentication
- **Tenant Isolation**: Multi-tenant data separation

## Testing

This package uses testcontainers for real PostgreSQL testing (no mocks).

```bash
npm test                 # Run tests in watch mode
npm run test:run         # Run tests once
npm run test:coverage    # Run with coverage report
```

## See Also

- [@bernierllc/database-adapter-core](../database-adapter-core) - Abstract base classes
- [@bernierllc/logger](../logger) - Structured logging
- [@bernierllc/retry-policy](../retry-policy) - Retry logic
- [@bernierllc/crypto-utils](../crypto-utils) - Cryptographic utilities

## License

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