npm package: @safetyscope/supabase-openapi-generator
Description: Generate OpenAPI 3.1 specifications from Supabase projects with RLS policy introspection, RPC mapping, and Edge Function integration
Author: Your Name
Latest version: 1.2.4

# Supabase OpenAPI Generator

Generate OpenAPI 3.1 specifications from your Supabase database schema with complete RLS policy introspection, RPC function mapping, and Edge Function integration.

[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](https://opensource.org/licenses/MIT)
[![Node Version](https://img.shields.io/badge/node-%3E%3D18-brightgreen)](https://nodejs.org/)

## Features

✨ **Complete Schema Introspection**
- Tables, columns, views, and enums
- Foreign key relationships
- Primary keys and constraints
- PostgreSQL-specific types (arrays, JSON, ranges, etc.)

🔒 **RLS Policy Analysis**
- Automatic policy parsing and categorization
- Permission matrix generation
- Complex policy detection
- Security audit reporting

🎯 **RPC Function Mapping**
- Function parameter introspection
- Return type mapping
- Volatility and security definer attributes
- Comment-based descriptions

⚡ **Edge Function Integration**
- Static code analysis
- TypeScript interface extraction
- JWT authentication detection
- Request/response schema generation

✅ **OpenAPI 3.1 Validation**
- Structural validation with Ajv
- Reference checking
- Path and schema validation
- Best practices verification

## Installation

```bash
npm install -g @safetyscope/supabase-openapi-generator
```

Or use without installation:

```bash
npx @safetyscope/supabase-openapi-generator
```

## Quick Start

### Preparation Guide

Before generating your OpenAPI spec, learn how to properly document your Supabase project:

```bash
npx @safetyscope/supabase-openapi-generator guide
```

Get detailed guidance for specific topics:

```bash
# Edge Functions documentation guide
npx @safetyscope/supabase-openapi-generator guide edge-functions

# Database tables and views guide
npx @safetyscope/supabase-openapi-generator guide database

# RPC functions guide  
npx @safetyscope/supabase-openapi-generator guide rpc

# Row Level Security guide
npx @safetyscope/supabase-openapi-generator guide rls
```

### Interactive Mode

Run the CLI and follow the prompts:

```bash
npx @safetyscope/supabase-openapi-generator
```

You'll be asked for:
1. **Database URL** - Your Supabase PostgreSQL connection string (required)
2. **Project URL** - Your Supabase project URL (optional, for reference)
3. **Schema Selection** - Which schemas to include (default: `public`)
4. **Table Selection** - Include/exclude specific tables
5. **RPC Functions** - Include/exclude specific functions
6. **Edge Functions** - Optionally include Edge Functions
7. **Output Options** - Format (YAML/JSON) and file path

### Configuration File

Save a configuration file for repeated use:

```yaml
# supabase-openapi.config.yaml
supabase:
  databaseUrl: postgresql://postgres:password@db.project.supabase.co:5432/postgres

scope:
  schemas:
    - public
  tables:
    - users
    - posts
    - comments
  rpcFunctions:
    - get_user_posts
    - create_post

output:
  path: ./openapi.yaml
  format: yaml
  generateAuditReport: true
  auditReportPath: ./security-audit.md
```

Run with config file:

```bash
npx @safetyscope/supabase-openapi-generator --config ./supabase-openapi.config.yaml
```

## Command Line Options

### Generate Command (Default)

```
Usage: supabase-openapi generate [options]

Generate OpenAPI specification

Options:
  -c, --config <path>      Load configuration from file
  -o, --output <path>      Output file path
  -f, --format <format>    Output format (yaml or json) (default: "yaml")
  -v, --verbose            Enable verbose output
  --skip-prompts           Skip interactive prompts (requires config file)
  -h, --help               Display help for command
```

### Guide Command

```
Usage: supabase-openapi guide [topic]

Display preparation guides for documenting your Supabase project

Topics:
  edge-functions    Document Edge Functions with full input/output
  database         Prepare database tables and views
  rpc              Document RPC (database) functions
  rls              Row Level Security policies
```

**Examples:**

```bash
# Display main guide menu
npx @safetyscope/supabase-openapi-generator guide

# Show Edge Functions guide
npx @safetyscope/supabase-openapi-generator guide edge-functions

# Show database documentation guide
npx @safetyscope/supabase-openapi-generator guide database
```

The guide command provides:
- ✅ Step-by-step preparation instructions
- 📝 Code examples with best practices
- ✓ Checklists for complete documentation
- 💡 Tips for automatic detection
- ❌ Common mistakes to avoid

## Database Connection

**Important:** This tool connects directly to your PostgreSQL database to introspect the schema. It does **not** use Supabase API keys (publishable/secret keys).

### Local Development

For local Supabase instances:

```bash
postgresql://postgres:postgres@127.0.0.1:54322/postgres
```

### Supabase Cloud

Get your connection string from the Supabase dashboard:
1. Go to **Settings → Database**
2. Find **Connection string** section
3. Use the **URI** format (not "Connection pooler")
4. Copy the connection string and replace `[YOUR-PASSWORD]` with your actual database password

**Note:** 
- The direct database connection string includes your **database password**, not your API keys
- If you see "Publishable" or "Secret" keys in your dashboard, those are for API access - this tool doesn't use them
- For local development with `supabase start`, the connection string is shown in the "Database" section

## Generated OpenAPI Features

### Table Operations

For each selected table, the generator creates:

- **GET** `/rest/v1/{table}` - List records with query parameters
- **POST** `/rest/v1/{table}` - Create new record
- **PATCH** `/rest/v1/{table}` - Update records
- **DELETE** `/rest/v1/{table}` - Delete records
- **GET** `/rest/v1/{table}?select=*` - Get single record by ID

### RPC Functions

Functions are mapped to:

```yaml
/rpc/v1/function_name:
  post:
    operationId: rpcFunctionName
    requestBody:
      content:
        application/json:
          schema:
            properties:
              param1: { type: string }
              param2: { type: integer }
```

### RLS Extensions

Each operation includes Supabase-specific extensions:

```yaml
x-supabase-rls:
  enabled: true
  select:
    allowed_roles: [authenticated]
    condition: "auth.uid() = user_id"
    is_complex: false
```

### Edge Functions

Edge Functions are documented with:

```yaml
/functions/v1/function-name:
  post:
    x-supabase-edge-function:
      name: function-name
      verifyJwt: true
    requestBody:
      content:
        application/json:
          schema:
            # Extracted from TypeScript interfaces
```

## Security Audit Report

When `generateAuditReport` is enabled, a markdown report is generated with:

- Tables without RLS enabled
- Complex policies requiring review
- Overly permissive configurations
- Recommended security improvements

Example audit report output:

```markdown
# Security Audit Report

## Tables Without RLS

- `public.analytics` - Consider enabling RLS if this table contains user data

## Complex Policies Detected

### `public.posts` - `complex_policy_name`
- Expression: `(auth.uid() = owner_id) OR (status = 'published' AND created_at > now() - interval '7 days')`
- Review Recommendation: Consider simplifying or documenting this policy

## Recommendations

1. Enable RLS on all tables containing user data
2. Review and simplify complex policies
3. Document custom policy logic
```

## Type Mapping

PostgreSQL types are automatically mapped to OpenAPI/JSON Schema types:

| PostgreSQL | OpenAPI | Format |
|------------|---------|--------|
| `integer`, `int4` | `integer` | `int32` |
| `bigint`, `int8` | `integer` | `int64` |
| `text`, `varchar` | `string` | - |
| `boolean` | `boolean` | - |
| `uuid` | `string` | `uuid` |
| `timestamp`, `timestamptz` | `string` | `date-time` |
| `date` | `string` | `date` |
| `json`, `jsonb` | `object` | - |
| `array types` | `array` | - |
| Custom enums | `string` with `enum` | - |

Unknown or custom types are mapped to `string` with the original PostgreSQL type preserved in `x-postgres-type`.

## Edge Cases Handled

### Schema Edge Cases
- Empty schemas (no tables)
- Reserved word identifiers
- Circular foreign key references
- Tables with no columns

### RLS Edge Cases
- Tables with RLS enabled but no policies
- NULL policy expressions
- Overly permissive policies (`true` conditions)
- Complex policies with subqueries

### Type Edge Cases
- Domain types
- Composite types
- Range and multirange types
- Extension types (ltree, hstore, etc.)

All edge cases are logged with warnings during generation.

## Troubleshooting

### Connection Errors

**Error:** `Authentication failed: Invalid username or password`
- Verify your database credentials
- Check that you're using the direct database password, not API keys

**Error:** `Connection refused: Database server is not accepting connections`
- Ensure your database is running
- Check firewall/network settings
- Verify the hostname and port are correct

**Error:** `Database does not exist`
- Check the database name in your connection string
- Verify you have access to the specified database

### Schema Introspection Errors

**Error:** `column p.proisagg does not exist`
- Your PostgreSQL version is older than 11
- Update PostgreSQL or use a newer Supabase instance

**Error:** `No tables found in schemas`
- Verify the schema names are correct
- Check that tables exist in the specified schemas
- Ensure you have SELECT permissions

### Installation Errors

**Error:** `syntax error near unexpected token` or `import: unable to open X server`
- This occurs when the npm package is installed incorrectly or cached
- Solution: Clear npm cache and reinstall:
  ```bash
  npm uninstall -g @safetyscope/supabase-openapi-generator
  npm cache clean --force
  npm install -g @safetyscope/supabase-openapi-generator
  ```
- If the issue persists, use npx instead: `npx @safetyscope/supabase-openapi-generator`

### Validation Errors

If the generated spec has validation errors:
1. Check the error messages for specific issues
2. Review complex policies that may not parse correctly
3. Verify custom types are properly defined
4. Check for circular references in schemas

## Use Cases

### API Documentation

Use with [Swagger UI](https://swagger.io/tools/swagger-ui/) or [Redoc](https://redoc.ly/):

```bash
# Generate spec
npx @safetyscope/supabase-openapi-generator -o openapi.yaml

# Serve with Redoc
npx @redocly/cli preview-docs openapi.yaml
```

### Client SDK Generation

Use with [OpenAPI Generator](https://openapi-generator.tech/):

```bash
# Generate TypeScript client
openapi-generator-cli generate \
  -i openapi.yaml \
  -g typescript-axios \
  -o ./src/client

# Generate Python client
openapi-generator-cli generate \
  -i openapi.yaml \
  -g python \
  -o ./client
```

### API Testing

Use with [Postman](https://www.postman.com/) or [Insomnia](https://insomnia.rest/):

1. Generate OpenAPI spec
2. Import into your API testing tool
3. Test endpoints with pre-populated schemas

### Contract Testing

Use the generated spec for API contract testing to ensure your backend matches the documentation.

## Configuration Reference

### Full Configuration Schema

```yaml
supabase:
  # Direct database URL (required)
  databaseUrl: string
  
  # Optional: Supabase project URL for reference
  projectUrl: string  # e.g., https://xxxxx.supabase.co

scope:
  # Schemas to introspect (default: ['public'])
  schemas: string[]
  
  # Tables to include (empty = all)
  tables: string[]
  
  # Tables to exclude
  excludeTables: string[]
  
  # RPC functions to include (empty = all)
  rpcFunctions: string[]
  
  # RPC functions to exclude
  excludeRpcFunctions: string[]

edgeFunctions:
  # Enable Edge Function introspection
  enabled: boolean
  
  # List of Edge Functions
  functions:
    - name: string
      path: string
      entryFile: string
      verifyJwt: boolean
      files:
        - name: string
          content: string

output:
  # Output file path
  path: string
  
  # Format: yaml or json
  format: 'yaml' | 'json'
  
  # Generate security audit report
  generateAuditReport: boolean
  
  # Audit report output path
  auditReportPath: string
  
  # OpenAPI info section
  title: string
  version: string
  description: string
```

## Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

## License

MIT License - see LICENSE file for details

## Related Projects

- [Supabase](https://supabase.com/) - Open source Firebase alternative
- [PostgREST](https://postgrest.org/) - REST API for PostgreSQL
- [OpenAPI Specification](https://spec.openapis.org/oas/latest.html) - OpenAPI 3.1

## Support

- 📚 [Documentation](https://github.com/yourusername/supabase-openapi-generator/wiki)
- 🐛 [Issue Tracker](https://github.com/yourusername/supabase-openapi-generator/issues)
- 💬 [Discussions](https://github.com/yourusername/supabase-openapi-generator/discussions)

---

Built with ❤️ for the Supabase community