Database Schema

Firestore database structure and collection schema definitions

🗃️ 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:

idemaildisplayNameauthProviderroles

councils

councils/{councilId}

Top-level organizational units for topics

Key Fields:

idnamedescriptioncreatedBystatus

forums

forums/{forumId}

Discussion spaces within councils

Key Fields:

idcouncilIdnameforumTypestatus

questions

forums/{forumId}/questions/{questionId}

User-submitted questions (nested in forums)

Key Fields:

idforumIdcouncilIdtextuserIdstatus

Additional Collections

votes
votes/*

User votes on questions (flat structure)

idquestionIduserId+1
aiPersonas
aiPersonas/*

AI agent personas and configurations

idnamesystemPrompt+2
responses
forums/*

AI and human responses to questions (nested)

idquestionIdresponderId+3
panels
panels/*

AI agent panels and marketplace collections

idnamevisibility+2
statistics
statistics/*

Aggregated statistics and analytics data

idtypeentityId+2
aiCostConfig
aiCostConfig/*

AI model pricing and budget configuration

idmodelsbudgets+2
aiUsageAlerts
aiUsageAlerts/*

AI usage threshold alerts and notifications

idalertTypethreshold+2
aiUsage
aiUsage/*

AI API usage tracking and cost monitoring

idmodeltotalTokens+2
aiUsageStats
aiUsageStats/*

Daily aggregated AI usage statistics

iddatetotalRequests+2

📊 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