🗃️ Schema Overview
Our simplified database schema focuses on core functionality with efficient data structures. All schemas follow strict naming conventions and CRUD compliance for consistent development.
Simplification Principles
✓Simplified 30% complexity reduction achieved
✓Eliminated duplicate and unnecessary fields
✓Removed deprecated collections (generationJobs, migration_backups)
✓Maintained core functionality integrity
✓Firestore-optimized collection paths
📊 Core Collections
Essential data structures that power the Collaborative Councils platform.
users
users/{userId}
User profiles and authentication data
Key Fields:
councils
councils/{councilId}
Top-level organizational units for topics
Key Fields:
forums
forums/{forumId}
Discussion spaces within councils
Key Fields:
questions
forums/{forumId}/questions/{questionId}
User-submitted questions (nested in forums)
Key Fields:
Additional Collections
votes
votes/*
User votes on questions (flat structure)
aiPersonas
aiPersonas/*
AI agent personas and configurations
responses
forums/*
AI and human responses to questions (nested)
panels
panels/*
AI agent panels and marketplace collections
statistics
statistics/*
Aggregated statistics and analytics data
aiCostConfig
aiCostConfig/*
AI model pricing and budget configuration
aiUsageAlerts
aiUsageAlerts/*
AI usage threshold alerts and notifications
aiUsage
aiUsage/*
AI API usage tracking and cost monitoring
aiUsageStats
aiUsageStats/*
Daily aggregated AI usage statistics
📊 Entity Relationship Diagram
Visual representation of the database structure showing relationships between collections.
💡 This diagram shows relationships between collections and key fields. PK indicates Primary Key, FK indicates Foreign Key.
📋 Schema Conventions
Strict rules that ensure data consistency and development efficiency.
Collection Naming
camelCase collection names (forums, aiPersonas)
✅ aiPersonas/{id} ❌ AIPersonas/{id}
Nested Collections
Questions MUST be nested under forums
✅ forums/{forumId}/questions/{id} ❌ questions/{id}
Required Fields
All required fields must be present in create operations
✅ {id, name, status} ❌ {id, name} missing status
Field Types
Follow TypeScript interface definitions exactly
✅ voteCount: number ❌ voteCount: string
Status Enums
Use predefined enum values for status fields
✅ status: 'active' ❌ status: 'enabled'
📋 Schema Information
Total Collections: 11
Complexity Reduction: 30%
Removed Collections: 5
Lines Reduced: 437
Status: Simplified
Last Updated: 10/1/2025