npm package: @procbay/tenant-schema
Description: A set of utilities for managing Prisma database schemas, seeding, and maintenance operations for the Procure-to-Pay system.
Author: Azhar Pathan
Latest version: 1.0.0

# @procbay/tenant-schema

A professional-grade multi-database Prisma schema management package for the Procure-to-Pay system, featuring advanced CLI tools, migration management, and seeding capabilities.

## Features

- 🗄️ **Multi-Database Support** - Manage multiple database instances simultaneously
- 🚀 **Advanced CLI Interface** - Professional command-line tools with interactive options
- 📊 **Migration Management** - Deploy, rollback, and track database schema changes
- 🌱 **Intelligent Seeding** - Modular data seeding with dependency resolution
- 🔄 **Batch Processing** - Efficient bulk operations with memory management
- 📈 **Progress Tracking** - Real-time progress bars and status reporting
- 🎨 **Enhanced UX** - Colored output, spinners, and table formatting
- ✅ **Comprehensive Validation** - Input validation and health checking
- 🔧 **Configuration Management** - Centralized database and seeder configuration

## Installation

```bash
npm install @procbay/tenant-schema
```

## Quick Start

### CLI Usage

```bash
# Migrate all databases
npx db-schema migrate --all

# Seed specific databases with selected seeders
npx db-schema seed --databases dev --seeders users,products

# Show status of all databases
npx db-schema status --all

# Reset and reseed a database
npx db-schema reset --database test --confirm
```

### Programmatic Usage

```javascript
import { DatabaseSchema, migrate, seed } from "@procbay/tenant-schema";

// High-level API
const schema = new DatabaseSchema({
  databases: ["development", "staging"],
  config: "./config/databases.json",
});

await schema.migrate();
await schema.seed(["users", "products"]);

// Quick functions
await migrate(["development"]);
await seed(["development"], ["users", "categories"]);
```

## CLI Commands

### Migration Commands

```bash
# Deploy migrations to all databases
db-schema migrate --all

# Deploy to specific databases
db-schema migrate --databases dev,staging

# Reset database with confirmation
db-schema migrate --type reset --database test --confirm

# Deploy migrations with parallel processing
db-schema migrate --databases dev,staging --parallel
```

### Seeding Commands

```bash
# Seed all databases with all seeders
db-schema seed --all

# Seed specific databases with selected seeders
db-schema seed --databases dev --seeders users,products,categories

# Seed with truncation (clear tables first)
db-schema seed --databases test --truncate --seeders users

# Interactive seeder selection
db-schema seed --databases dev --interactive
```

### Reset Commands

```bash
# Reset specific database (requires confirmation)
db-schema reset --database test --confirm

# Reset and seed immediately after
db-schema reset --database test --seed --seeders users,products

# Reset multiple databases in parallel
db-schema reset --databases test1,test2 --parallel --confirm
```

### Status Commands

```bash
# Show comprehensive status
db-schema status --all

# Show only migration status
db-schema status --migrations --databases dev,staging

# Health check with detailed output
db-schema status --health --format table

# Export status to JSON
db-schema status --all --export status-report.json
```

## Configuration

### Database Configuration

Create `config/databases.json`:

```json
{
  "development": {
    "url": "postgresql://user:pass@localhost:5432/dev_db",
    "alias": "dev",
    "migrations": {
      "enabled": true,
      "autoApply": false
    },
    "seeding": {
      "enabled": true,
      "truncateFirst": false
    }
  },
  "staging": {
    "url": "postgresql://user:pass@staging:5432/staging_db",
    "alias": "staging",
    "migrations": {
      "enabled": true,
      "autoApply": true
    },
    "seeding": {
      "enabled": false
    }
  }
}
```

### Seeder Configuration

Create `seeders/config/seeder-config.json`:

```json
{
  "seeders": [
    {
      "name": "users",
      "file": "data/users.json",
      "table": "User",
      "batchSize": 1000,
      "dependencies": [],
      "enabled": true
    },
    {
      "name": "products",
      "file": "data/products.json",
      "table": "Product",
      "batchSize": 500,
      "dependencies": ["users"],
      "enabled": true
    }
  ],
  "globalSettings": {
    "truncateBeforeSeed": false,
    "continueOnError": false,
    "logLevel": "info",
    "batchSize": 1000,
    "parallelProcessing": true
  }
}
```

## Environment Variables

```bash
# Logging
LOG_LEVEL=info                    # debug, info, warn, error, silent
NO_COLOR=1                        # Disable colored output

# Database
DATABASE_TIMEOUT=30000            # Connection timeout in ms
PRISMA_CLIENT_CONNECTION_REUSE=1  # Reuse database connections

# Performance
MAX_BATCH_SIZE=5000              # Maximum batch size for operations
MEMORY_THRESHOLD=500             # Memory threshold in MB
```

## API Reference

### DatabaseSchema Class

```javascript
import { DatabaseSchema } from "@procbay/tenant-schema";

const schema = new DatabaseSchema({
  databases: ["dev", "staging"], // Target databases
  config: "./config/databases.json", // Config file path
  retryAttempts: 3, // Number of retry attempts
});

// Methods
await schema.migrate(options); // Run migrations
await schema.seed(seeders, options); // Run seeders
await schema.reset(options); // Reset databases
await schema.getStatus(); // Get status
await schema.testConnections(); // Test connections
await schema.cleanup(); // Clean up resources
```

### Quick Functions

```javascript
import { migrate, seed, reset, getStatus } from "@procbay/tenant-schema";

// Quick operations
await migrate(["dev"], { type: "deploy" });
await seed(["dev"], ["users"], { truncate: true });
await reset(["test"], { confirm: true });
const status = await getStatus(["dev", "staging"]);
```

### Utilities

```javascript
import { BatchProcessor, Validator, Logger } from "@procbay/tenant-schema";

// Batch processing
const processor = new BatchProcessor({ batchSize: 1000 });
await processor.process(data, async (batch) => {
  // Process batch
});

// Validation
const validator = new Validator();
const isValid = validator.validateSeederConfig(config);

// Logging
const logger = new Logger({ level: "info" });
logger.info("Operation completed");
```

## Package Scripts

```bash
# Development
npm run build          # Build package
npm run test           # Run tests
npm run lint           # Lint code
npm run format         # Format code

# Database Operations
npm run db:migrate     # Migrate all databases
npm run db:seed        # Seed all databases
npm run db:reset       # Reset databases with confirmation
npm run db:status      # Show status of all databases
```

## Error Handling

The package provides comprehensive error handling with structured error types:

```javascript
import { DatabaseSchema } from "@procbay/tenant-schema";

try {
  await schema.migrate();
} catch (error) {
  if (error.code === "DATABASE_CONNECTION_FAILED") {
    console.error("Failed to connect:", error.message);
    console.error("Details:", error.details);
  }
}
```

## Contributing

1. Fork the repository
2. Create a feature branch: `git checkout -b feature/my-feature`
3. Commit changes: `git commit -am 'Add my feature'`
4. Push to branch: `git push origin feature/my-feature`
5. Submit a pull request

## License

MIT License - see LICENSE file for details.

## Support

- 📧 Email: apathan@katalysttech.com
- 🐛 Issues: [GitHub Issues](https://ntgitlab.novatechset.com/procure-2-pay/backend/schema/-/issues)
- 📖 Docs: [Package Documentation](https://ntgitlab.novatechset.com/procure-2-pay/backend/schema#readme)