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:

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:

./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:

./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:

./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, members, roles

• user-management-schema.sql : Extended user profiles, metadata, preferences

• ai-usage-tracking-schema.sql : Token usage, API costs, rate limiting, usage analytics

Supporting Templates

• migration-template.sql : Boilerplate migration structure with version tracking

• indexes-template.sql : Performance optimization index patterns

• rls-policies-template.sql : Row Level Security policy patterns

Key Features

pgvector Integration (RAG Schemas)

All RAG schemas include:

• Vector column setup with proper dimensions

• HNSW indexing for similarity search

• Cosine distance operators

• Automatic embedding column generation

• Metadata storage alongside embeddings

Multi-Tenancy Support

Organization-based isolation:

• Tenant identification (org_id on all tables)

• Team-based access control

• Member role management

• RLS policies for data isolation

Chat System Optimization

Optimized for real-time messaging:

• Conversation participants tracking

• Message ordering and pagination indexes

• Read/unread status tracking

• Typing indicators support

• Message search with full-text indexes

Performance Patterns

• Composite indexes for common queries

• Partial indexes for filtered queries

• Generated columns for computed fields

• Proper foreign key cascades

• Optimized join patterns

Examples

See the examples directory for:

• complete-ai-app-schema.md : Full schema combining all patterns

• migration-guide.md : Schema evolution and versioning

• indexing-strategy.md : Performance optimization guide

Best Practices

• Always use lowercase with underscores for table/column names

• Enable pgvector extension before creating vector columns

• Add indexes on foreign keys for join performance

• Use generated columns for computed fields (created_at, updated_at)

• Implement RLS policies for security and multi-tenancy

• Version all migrations with timestamps

• Use halfvec for embeddings to save storage (16-bit vs 32-bit)

• Add metadata JSONB columns for flexibility

• Plan for soft deletes (deleted_at timestamp)

• Include audit trails (created_by, updated_by)

Common Workflows

Setting Up a Chat Application

./scripts/generate-schema.sh chat schema.sql ./scripts/validate-schema.sh schema.sql ./scripts/apply-migration.sh schema.sql "initial-chat-schema" ./scripts/seed-data.sh chat

Building a RAG System

./scripts/generate-schema.sh rag schema.sql ./scripts/validate-schema.sh schema.sql ./scripts/apply-migration.sh schema.sql "add-rag-storage" ./scripts/seed-data.sh rag

Complete AI Platform

./scripts/generate-schema.sh complete schema.sql ./scripts/validate-schema.sh schema.sql ./scripts/apply-migration.sh schema.sql "complete-ai-platform"

Troubleshooting

pgvector not found: Enable the vector extension in Supabase dashboard (Database > Extensions)

RLS blocks queries: Check RLS policies or temporarily disable for testing (not recommended for production)

Slow similarity search: Ensure HNSW index is created on vector columns with proper operator class

Migration conflicts: Check migration version ordering and resolve conflicts manually

Skill Location: /home/vanman2025/Projects/ai-dev-marketplace/plugins/supabase/skills/schema-patterns/ Version: 1.0.0