Production-ready database schema patterns for AI applications including chat/conversation schemas, RAG document storage with pgvector, multi-tenant organization models, user management, and AI usage tracking. Use when building AI applications, creating database schemas, setting up chat systems, implementing RAG, designing multi-tenant databases, or when user mentions supabase schemas, chat database, RAG storage, pgvector, embeddings, conversation history, or AI application database.
View on GitHubFebruary 1, 2026
Select agents to install to:
npx add-skill https://github.com/vanman2024/ai-dev-marketplace/blob/main/plugins/supabase/skills/schema-patterns/SKILL.md -a claude-code --skill schema-patternsInstallation paths:
.claude/skills/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