relspecgo/pkg/inspector/README.md

# RelSpec Inspector

> Database Schema Validation and Linting Tool

The RelSpec Inspector validates database schemas against configurable rules, helping you maintain consistency, enforce naming conventions, and catch common schema design issues across your database models.

## Overview

The Inspector reads database schemas from any supported RelSpec format and validates them against a set of configurable rules. It generates detailed reports highlighting violations, warnings, and passed checks.

## Features

- **Flexible Rule Configuration**: YAML-based rules with three severity levels (enforce, warn, off)
- **Generic Validators**: Reusable regex-based validators for custom naming conventions
- **Multiple Input Formats**: Works with all RelSpec readers (PostgreSQL, DBML, JSON, GORM, Bun, etc.)
- **Multiple Output Formats**: Markdown with ANSI colors for terminals, JSON for tooling integration
- **Smart Defaults**: Works out-of-the-box with sensible default rules
- **Terminal-Aware**: Automatic color support detection for improved readability
- **Exit Codes**: Proper exit codes for CI/CD integration

[Todo List of Features](./TODO.md)

## Quick Start

### Basic Usage

```bash
# Inspect a PostgreSQL database with default rules
relspec inspect --from pgsql --from-conn "postgres://user:pass@localhost/mydb"

# Inspect a DBML file
relspec inspect --from dbml --from-path schema.dbml

# Inspect with custom rules
relspec inspect --from json --from-path db.json --rules my-rules.yaml

# Output JSON report to file
relspec inspect --from pgsql --from-conn "..." \
                --output-format json --output report.json

# Inspect specific schema only
relspec inspect --from pgsql --from-conn "..." --schema public
```

### Configuration

Create a `.relspec-rules.yaml` file to customize validation rules. If the file doesn't exist, the inspector uses sensible defaults.

```yaml
version: "1.0"

rules:
  # Primary key columns must start with "id_"
  primary_key_naming:
    enabled: enforce  # enforce|warn|off
    function: primary_key_naming
    pattern: "^id_"
    message: "Primary key columns must start with 'id_'"

  # Foreign key columns must start with "rid_"
  foreign_key_column_naming:
    enabled: warn
    function: foreign_key_column_naming
    pattern: "^rid_"
    message: "Foreign key columns should start with 'rid_'"

  # Table names must be lowercase snake_case
  table_naming_case:
    enabled: warn
    function: table_regexpr  # Generic regex validator
    pattern: "^[a-z][a-z0-9_]*$"
    message: "Table names should be lowercase with underscores"

  # Ensure all tables have primary keys
  missing_primary_key:
    enabled: warn
    function: have_primary_key
    message: "Table is missing a primary key"
```

## Built-in Validation Rules

### Primary Key Rules

| Rule | Function | Description |
|------|----------|-------------|
| `primary_key_naming` | `primary_key_naming` | Validate PK column names against regex pattern |
| `primary_key_datatype` | `primary_key_datatype` | Enforce approved PK data types (bigint, serial, etc.) |
| `primary_key_auto_increment` | `primary_key_auto_increment` | Check if PKs have auto-increment enabled |

### Foreign Key Rules

| Rule | Function | Description |
|------|----------|-------------|
| `foreign_key_column_naming` | `foreign_key_column_naming` | Validate FK column names against regex pattern |
| `foreign_key_constraint_naming` | `foreign_key_constraint_naming` | Validate FK constraint names against regex pattern |
| `foreign_key_index` | `foreign_key_index` | Ensure FK columns have indexes for performance |

### Naming Convention Rules

| Rule | Function | Description |
|------|----------|-------------|
| `table_naming_case` | `table_regexpr` | Generic regex validator for table names |
| `column_naming_case` | `column_regexpr` | Generic regex validator for column names |

### Length Rules

| Rule | Function | Description |
|------|----------|-------------|
| `table_name_length` | `table_name_length` | Limit table name length (default: 64 chars) |
| `column_name_length` | `column_name_length` | Limit column name length (default: 64 chars) |

### Reserved Keywords

| Rule | Function | Description |
|------|----------|-------------|
| `reserved_keywords` | `reserved_words` | Detect use of SQL reserved keywords as identifiers |

### Schema Integrity Rules

| Rule | Function | Description |
|------|----------|-------------|
| `missing_primary_key` | `have_primary_key` | Ensure tables have primary keys |
| `orphaned_foreign_key` | `orphaned_foreign_key` | Detect FKs referencing non-existent tables |
| `circular_dependency` | `circular_dependency` | Detect circular FK dependencies |

## Rule Configuration

### Severity Levels

Rules support three severity levels:

- **`enforce`**: Violations are errors (exit code 1)
- **`warn`**: Violations are warnings (exit code 0)
- **`off`**: Rule is disabled

### Rule Structure

```yaml
rule_name:
  enabled: enforce|warn|off
  function: validator_function_name
  message: "Custom message shown on violation"
  # Rule-specific parameters
  pattern: "^regex_pattern$"        # For pattern-based validators
  allowed_types: [type1, type2]     # For type validators
  max_length: 64                    # For length validators
  check_tables: true                # For keyword validator
  check_columns: true               # For keyword validator
  require_index: true               # For FK index validator
```

## Generic Validators

The inspector provides generic validator functions that can be reused for custom rules:

### `table_regexpr`

Generic regex validator for table names. Create custom table naming rules:

```yaml
# Example: Ensure table names don't contain numbers
table_no_numbers:
  enabled: warn
  function: table_regexpr
  pattern: "^[a-z_]+$"
  message: "Table names should not contain numbers"

# Example: Tables must start with "tbl_"
table_prefix:
  enabled: enforce
  function: table_regexpr
  pattern: "^tbl_[a-z][a-z0-9_]*$"
  message: "Table names must start with 'tbl_'"
```

### `column_regexpr`

Generic regex validator for column names. Create custom column naming rules:

```yaml
# Example: Audit columns must end with "_audit"
audit_column_suffix:
  enabled: enforce
  function: column_regexpr
  pattern: ".*_audit$"
  message: "Audit columns must end with '_audit'"

# Example: Timestamp columns must end with "_at"
timestamp_suffix:
  enabled: warn
  function: column_regexpr
  pattern: ".*(created|updated|deleted)_at$"
  message: "Timestamp columns should end with '_at'"
```

## Output Formats

### Markdown (Default)

Human-readable markdown report with ANSI colors when outputting to a terminal:

```
# RelSpec Inspector Report

**Database:** my_database
**Source Format:** pgsql
**Generated:** 2025-12-31T10:30:45Z

## Summary
- Rules Checked: 13
- Errors: 2
- Warnings: 5
- Passed: 120

## Violations

### Errors (2)

#### primary_key_naming
**Location:** public.users.user_id
**Message:** Primary key columns must start with 'id_'
**Details:** expected_pattern=^id_

### Warnings (5)

#### foreign_key_index
**Location:** public.orders.customer_id
**Message:** Foreign key columns should have indexes
**Details:** has_index=false
```

### JSON

Structured JSON output for tooling integration:

```json
{
  "summary": {
    "total_rules": 13,
    "rules_checked": 13,
    "error_count": 2,
    "warning_count": 5,
    "passed_count": 120
  },
  "violations": [
    {
      "rule_name": "primary_key_naming",
      "level": "error",
      "message": "Primary key columns must start with 'id_'",
      "location": "public.users.user_id",
      "context": {
        "schema": "public",
        "table": "users",
        "column": "user_id",
        "expected_pattern": "^id_"
      },
      "passed": false
    }
  ],
  "generated_at": "2025-12-31T10:30:45Z",
  "database": "my_database",
  "source_format": "pgsql"
}
```

## CLI Reference

### Flags

| Flag | Type | Description |
|------|------|-------------|
| `--from` | string | **Required**. Source format (dbml, pgsql, json, yaml, gorm, etc.) |
| `--from-path` | string | Source file path (for file-based formats) |
| `--from-conn` | string | Connection string (for database formats) |
| `--rules` | string | Path to rules YAML file (default: `.relspec-rules.yaml`) |
| `--output-format` | string | Output format: `markdown` or `json` (default: `markdown`) |
| `--output` | string | Output file path (default: stdout) |
| `--schema` | string | Filter to specific schema by name |

### Exit Codes

| Code | Meaning |
|------|---------|
| 0 | Success (no errors, only warnings or all passed) |
| 1 | Validation errors found (rules with `enabled: enforce` failed) |
| 2 | Runtime error (invalid config, reader error, etc.) |

## CI/CD Integration

### GitHub Actions Example

```yaml
name: Schema Validation

on: [pull_request]

jobs:
  validate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Install RelSpec
        run: go install git.warky.dev/wdevs/relspecgo/cmd/relspec@latest

      - name: Validate Schema
        run: |
          relspec inspect \
            --from dbml \
            --from-path schema.dbml \
            --rules .relspec-rules.yaml \
            --output-format json \
            --output validation-report.json

      - name: Upload Report
        if: always()
        uses: actions/upload-artifact@v3
        with:
          name: validation-report
          path: validation-report.json
```

### Pre-commit Hook Example

```bash
#!/bin/bash
# .git/hooks/pre-commit

echo "Running schema validation..."

relspec inspect \
  --from dbml \
  --from-path schema.dbml \
  --rules .relspec-rules.yaml

exit $?
```

## Example Configuration File

See [`.relspec-rules.yaml.example`](../../.relspec-rules.yaml.example) for a fully documented example configuration with all available rules and customization options.

## Common Use Cases

### Enforce Naming Standards

```yaml
# Ensure consistent naming across your schema
table_naming_case:
  enabled: enforce
  function: table_regexpr
  pattern: "^[a-z][a-z0-9_]*$"
  message: "Tables must use snake_case"

column_naming_case:
  enabled: enforce
  function: column_regexpr
  pattern: "^[a-z][a-z0-9_]*$"
  message: "Columns must use snake_case"

primary_key_naming:
  enabled: enforce
  function: primary_key_naming
  pattern: "^id$"
  message: "Primary key must be named 'id'"

foreign_key_column_naming:
  enabled: enforce
  function: foreign_key_column_naming
  pattern: "^[a-z]+_id$"
  message: "Foreign keys must end with '_id'"
```

### Performance Best Practices

```yaml
# Ensure optimal database performance
foreign_key_index:
  enabled: enforce
  function: foreign_key_index
  require_index: true
  message: "Foreign keys must have indexes"

primary_key_datatype:
  enabled: enforce
  function: primary_key_datatype
  allowed_types: [bigserial, bigint]
  message: "Use bigserial or bigint for primary keys"
```

### Schema Integrity

```yaml
# Prevent common schema issues
missing_primary_key:
  enabled: enforce
  function: have_primary_key
  message: "All tables must have a primary key"

orphaned_foreign_key:
  enabled: enforce
  function: orphaned_foreign_key
  message: "Foreign keys must reference existing tables"

circular_dependency:
  enabled: warn
  function: circular_dependency
  message: "Circular dependencies detected"
```

### Avoid Reserved Keywords

```yaml
reserved_keywords:
  enabled: warn
  function: reserved_words
  check_tables: true
  check_columns: true
  message: "Avoid using SQL reserved keywords"
```

## Programmatic Usage

You can use the inspector programmatically in your Go code:

```go
package main

import (
    "fmt"
    "git.warky.dev/wdevs/relspecgo/pkg/inspector"
    "git.warky.dev/wdevs/relspecgo/pkg/models"
)

func main() {
    // Load your database model
    db := &models.Database{
        Name: "my_database",
        Schemas: []*models.Schema{
            // ... your schema
        },
    }

    // Load rules configuration
    config, err := inspector.LoadConfig(".relspec-rules.yaml")
    if err != nil {
        panic(err)
    }

    // Create and run inspector
    insp := inspector.NewInspector(db, config)
    report, err := insp.Inspect()
    if err != nil {
        panic(err)
    }

    // Generate report
    formatter := inspector.NewMarkdownFormatter(os.Stdout)
    output, err := formatter.Format(report)
    if err != nil {
        panic(err)
    }

    fmt.Println(output)

    // Check for errors
    if report.HasErrors() {
        os.Exit(1)
    }
}
```

## Contributing

Contributions are welcome! To add a new validator:

1. Add the validator function to `validators.go`
2. Register it in `inspector.go` `getValidator()` function
3. Add default configuration to `rules.go` `GetDefaultConfig()`
4. Update this README with the new rule documentation

## License

Apache License 2.0 - See [LICENSE](../../LICENSE) for details.