schema-patterns

# Database Schema Patterns for AI Applications

Production-ready PostgreSQL/Supabase database schemas optimized for AI applications including chat systems, RAG (Retrieval-Augmented Generation), multi-tenancy, and usage tracking.

## Instructions

### 1. Identify Required Pattern Type

Ask the user which schema pattern they need:
- **chat**: Conversation and messaging systems
- **rag**: Document storage with vector embeddings (pgvector)
- **multi-tenant**: Organization-based multi-tenancy
- **user-management**: Extended user profiles and metadata
- **ai-usage**: Token tracking, costs, and rate limiting
- **complete**: All patterns combined

### 2. Generate Schema

Use the generation script:
```bash
cd /home/vanman2025/Projects/ai-dev-marketplace/plugins/supabase/skills/schema-patterns
./scripts/generate-schema.sh <pattern-type> <output-file>
```

Pattern types: `chat`, `rag`, `multi-tenant`, `user-management`, `ai-usage`, `complete`

### 3. Validate Schema

Before applying, validate the generated schema:
```bash
./scripts/validate-schema.sh <schema-file>
```

This checks for:
- Proper table naming conventions (lowercase, underscores)
- Primary keys on all tables
- Foreign key relationships
- Index optimization
- pgvector extension usage (for RAG patterns)
- RLS policy structure
- Migration version format

### 4. Apply Migration

Apply the schema to your Supabase project:
```bash
./scripts/apply-migration.sh <schema-file> <migration-name>
```

This creates a timestamped migration file and validates before applying.

### 5. Seed Test Data (Optional)

For development, generate realistic test data:
```bash
./scripts/seed-data.sh <pattern-type>
```

## Available Templates

### Core Schemas
- `chat-schema.sql`: Complete chat/conversation system with users, conversations, messages, participants
- `rag-schema.sql`: RAG document storage with chunks, embeddings (pgvector), and similarity search
- `multi-tenant-schema.sql`: Organization-based multi-tenancy with orgs, teams, mem