# Firestore Schema Reference

# Firestore Schema Reference

Complete Firestore data models and collections for the TeamDay platform.

## Collections Overview

TeamDay uses Firebase Firestore as its primary database. The schema follows a hierarchical structure with strong security rules and real-time subscriptions.

| Collection | Purpose | Access Control |
|------------|---------|----------------|
| `organizations` | Organization/team management | Members only |
| `users` | User account data | Owner only |
| `userProfiles` | User preferences and settings | Owner only |
| `character` | Agents (stored as `characters` in Firestore) | Visibility-based |
| `spaces` | Workspaces for agents | Visibility-based |
| `chats` | Conversations | Owner/org members |
| `tasks` | Agent-created tasks | Org members |
| `executions` | Agent execution history | Org members |
| `missions` | Long-running autonomous tasks | Org members |
| `scheduledTasks` | Scheduled agent automations | Org members |
| `skills` | Reusable prompt packages | Visibility-based |
| `subagents` | Specialized agent configs | Visibility-based |
| `plugins` | Configuration packages | Visibility-based |
| `pluginInstallations` | Plugin installs per space | Org members |
| `mcpInstances` | MCP server instances | Org members |
| `personal_access_tokens` | API tokens | Owner only |
| `invites` | Organization invites | Public read |
| `usageAggregates` | Usage metrics | Org members |
| `creditTransactions` | Credit balance history | Org members |

## Core Collections

### organizations

Organization/team management. Each user belongs to one or more organizations.

```typescript
interface Organization {
  id: string
  name: string
  logoUrl?: string
  ownerId: string                    // User who created the org
  members: string[]                  // Array of user IDs
  subscription?: OrganizationSubscription
  createdAt: Timestamp
  updatedAt: Timestamp

  // Encrypted fields (server-side only)
  encryptedAnthropicApiKey?: string  // Encrypted API key
}
```

**Example Document:**
```json
{
  "id": "org_abc123",
  "name": "Acme Corporation",
  "logoUrl": "https://storage.googleapis.com/...",
  "ownerId": "user_xyz",
  "members": ["user_xyz", "user_abc", "user_def"],
  "subscription": {
    "tier": "pro",
    "status": "active"
  },
  "createdAt": "2024-01-15T10:00:00Z",
  "updatedAt": "2024-01-15T10:00:00Z"
}
```

**Indexes Required:**
- `members` (array-contains) for member queries
- `ownerId` for owner queries

**Subcollections:**
- `gitCredentials/{credentialId}` - OAuth tokens for Git integrations

---

### users

User authentication and account data. Managed by Firebase Auth.

```typescript
interface User {
  id: string                         // Firebase Auth UID
  email: string
  displayName?: string
  photoURL?: string
  createdAt: Timestamp
  lastSignIn?: Timestamp
}
```

**Security:** Owner-only access. No client-side deletion allowed.

**Subcollections:**
- `gitCredentials/{credentialId}` - Personal Git OAuth tokens

---

### userProfiles

User preferences, settings, and last-used organization.

```typescript
interface UserProfile {
  userId: string
  lastUsedOrganizationId?: string
  preferences?: {
    theme?: 'light' | 'dark' | 'system'
    locale?: string
  }
  createdAt: Timestamp
  updatedAt?: Timestamp

  // Encrypted fields (server-side only)
  encryptedAnthropicApiKey?: string
  encryptedClaudeCodeOAuthToken?: string
}
```

**Security:** Owner can read/update. Cannot modify encrypted credential fields from client.

---

### character (Agents)

Agents collection (stored as `characters` in Firestore). These are the AI agents that users create and configure.

```typescript
type CharacterVisibility = 'private' | 'organization' | 'public' | 'unlisted'
type CharacterCategory = 'marketing' | 'finance' | 'hr' | 'engineering' | 'operations' | 'general'

interface Character {
  id: string
  name: string
  role: string                       // e.g., "Marketing Assistant"
  initialGreeting: string
  characterDescription: string       // Agent description
  system_message: string             // System prompt (legacy field name)
  systemPrompt?: string              // System prompt (preferred alias)
  image: string                      // Avatar URL
  color: string                      // Brand color

  // Configuration
  model?: string                     // e.g., 'claude-sonnet-4-6'
  visibility: CharacterVisibility     // Agent visibility
  ownerId: string
  organizationId?: string
  ownerName?: string

  // Resources
  files: any[]                       // Attached files
  linksToGetContextFrom: any[]       // URLs for context
  tools?: string[] | null            // Legacy AI tools
  advanced_tools?: string[]          // MCP tool IDs
  skillIds?: string[]                // Skill references
  subagentIds?: string[]             // Subagent references

  // Features
  suggested_topics?: any[]
  suggested_topics_conversation_starters?: string[]
  voice?: string                     // Voice model ID
  tags?: string[]
  articles?: string[]

  // Template system
  isTemplate?: boolean               // Global template flag
  category?: CharacterCategory
  featured?: boolean
  version?: number
  changelog?: string
  templateId?: string                // Source template ID
  instanceCount?: number             // For templates

  // State
  isForkable?: boolean
  archived?: boolean

  // Statistics
  statistics?: {
    likes: number
    dislikes: number
    uniqueChats: number
  }

  // Timestamps
  createdAt: Date | Timestamp
  updatedAt?: Date | Timestamp

  // Soft delete
  deletedAt?: Date | null
  isDeleted?: boolean
}
```

**Example Document:**
```json
{
  "id": "char_123",
  "name": "Marketing Maven",
  "role": "Marketing Assistant",
  "initialGreeting": "Hi! I'm here to help with marketing tasks.",
  "characterDescription": "Expert in content marketing and social media",
  "system_message": "You are a marketing specialist...",
  "systemPrompt": "You are a marketing specialist...",
  "image": "https://storage.googleapis.com/avatars/marketing.png",
  "color": "#FF6B6B",
  "model": "claude-sonnet-4-6",
  "visibility": "organization",
  "ownerId": "user_xyz",
  "organizationId": "org_abc123",
  "skillIds": ["research-assistant", "technical-writer"],
  "advanced_tools": ["mcp_google_analytics"],
  "tags": ["marketing", "content"],
  "archived": false,
  "isTemplate": false,
  "createdAt": "2024-01-15T10:00:00Z"
}
```

**Indexes Required:**
- Composite: `organizationId` + `isTemplate` + `createdAt` (desc)
- `ownerId` + `visibility`
- `isTemplate` for template queries

**Visibility Rules:**
- `public`: Visible to all authenticated users
- `organization`: Visible to all org members
- `private`: Only owner can see
- `unlisted`: Only owner can see (not in listings)
- Templates (`isTemplate: true`): Visible to all

---

### spaces

Workspaces where agents operate. Each space has its own file system and git configuration.

```typescript
type SpaceVisibility = 'private' | 'organization' | 'public'
type SpaceInitializationType = 'empty' | 'readme' | 'git' | 'starterKit'
type SpaceInitializationStatus = 'pending' | 'initializing' | 'ready' | 'failed'

interface Space {
  id: string
  organizationId: string
  name: string
  description: string

  // Branding
  coverImage?: string
  gradientColors?: [string, string]  // [fromColor, toColor]
  coverImageAttribution?: {
    source: 'unsplash' | 'upload' | 'url' | 'ai'
    photographerName?: string
    photographerUsername?: string
    unsplashUrl?: string
  }

  // Access control
  visibility: SpaceVisibility
  ownerId: string
  allowedUserIds?: string[]          // For private spaces

  // Configuration
  assignedAgents?: string[]          // Agent IDs (stored as characterIds)
  instructions?: string              // Fallback instructions
  advanced_tools?: string[]          // MCP instance IDs
  skillIds?: string[]                // Skill IDs
  subagentIds?: string[]             // Subagent IDs
  pluginIds?: string[]               // Plugin IDs to install

  // Git configuration
  git?: {
    aiSettings?: {
      autoCommitOnSessionEnd?: boolean
      generateCommitMessages?: boolean
      autoResolveConflicts?: boolean
      requireApprovalForPush?: boolean
    }
    permissions?: Record<string, {
      readers: string[]
      writers: string[]
      pushers: string[]
      defaultPermission: 'none' | 'read' | 'write' | 'push'
    }>
    defaultUserId?: string
    remotes?: Record<string, string> // Cache only
  }

  // Initialization
  initializationStatus?: SpaceInitializationStatus
  initializationType?: SpaceInitializationType
  initializationError?: string

  // Statistics
  chatCount?: number
  fileCount?: number

  // Timestamps
  createdAt: Date
  updatedAt: Date

  // Soft delete
  deletedAt?: Date | null
  isDeleted?: boolean

  // Optimistic UI
  isOptimistic?: boolean
  clientSideId?: string
}
```

**Example Document:**
```json
{
  "id": "space_456",
  "organizationId": "org_abc123",
  "name": "Marketing Campaigns",
  "description": "Space for marketing campaign work",
  "visibility": "organization",
  "ownerId": "user_xyz",
  "assignedAgents": ["char_123"],
  "skillIds": ["research-assistant"],
  "gradientColors": ["#3b82f6", "#8b5cf6"],
  "initializationStatus": "ready",
  "initializationType": "empty",
  "chatCount": 5,
  "fileCount": 23,
  "createdAt": "2024-01-15T10:00:00Z",
  "updatedAt": "2024-01-15T12:00:00Z"
}
```

**Indexes Required:**
- `organizationId` + `updatedAt` (desc)
- `ownerId` for owner queries
- `visibility` for filtering

---

### chats

Conversations between users and AI agents.

```typescript
type ChatStatus = 'standby' | 'working' | 'done' | 'needs_attention' | 'error'

interface Chat {
  id: string
  organizationId: string
  spaceId: string
  title: string
  subtitle?: string
  status: ChatStatus
  ownerId: string
  lastMessage?: string
  sessionId?: string                 // Claude Code session ID

  // Session lifecycle
  closed: boolean
  closedAt?: Date | null

  // Visibility
  visibility?: 'private' | 'organization' | 'public'

  // Timestamps
  createdAt: Date
  updatedAt: Date
}
```

**Subcollections:**
- `messages/{messageId}` - Chat messages (immutable)

**Messages Structure:**
```typescript
interface ChatMessage {
  id: string
  chatId: string
  role: 'user' | 'assistant'
  content: string
  createdAt: Date
  sessionId?: string
  toolCalls?: Array<{
    name: string
    input: any
    result?: string
  }>
}
```

**Indexes Required:**
- Composite: `organizationId` + `spaceId` + `updatedAt` (desc)
- `ownerId` for user's chats
- `sessionId` for session queries

**Security:** Messages are immutable (no update/delete).

---

### tasks

Agent-created tasks for coordination and assignment.

```typescript
type TaskPriority = 'low' | 'medium' | 'high' | 'urgent'
type TaskStatus = 'pending' | 'in_progress' | 'completed' | 'cancelled'

interface Task {
  id: string                         // Format: task-{timestamp}-{random}
  organizationId: string
  title: string
  description: string
  assignedTo?: string | null         // Agent ID or user ID
  priority: TaskPriority
  status: TaskStatus
  spaceId?: string | null
  createdBy: string | null           // Agent ID or user ID that created it

  // Timestamps
  createdAt: string                  // ISO 8601
  completedAt?: string | null        // ISO 8601
}
```

**Indexes Required:**
- Composite: `organizationId` + `status` + `createdAt` (desc)
- `assignedTo` for assignment queries
- `spaceId` for space tasks

---

### executions

Agent execution tracking and delegation history.

```typescript
type ExecutionStatus = 'pending' | 'running' | 'completed' | 'failed' | 'cancelled'

interface Execution {
  id: string                         // Format: exec-{timestamp}-{random}
  organizationId: string
  agentId: string
  chatId: string                     // Associated chat ID
  sessionId: string                  // Claude Code session ID
  userId?: string                    // User who initiated the execution
  status: ExecutionStatus

  // Content
  message: string                    // User's message (singular)
  result?: string                    // Agent's response
  error?: string                     // Error if failed

  // Agent info
  agentName?: string                 // Agent display name (for notifications)

  // Delegation tracking
  parentExecutionId?: string         // Parent execution ID (if delegated)
  delegationDepth: number            // Depth in delegation chain

  // Timing
  startedAt: Date
  completedAt?: Date
  duration?: number                  // milliseconds

  // Cost tracking
  tokensInput?: number
  tokensOutput?: number
  cost?: number
}
```

**Indexes Required:**
- Composite: `organizationId` + `chatId` + `startedAt` (desc)
- `sessionId` for session queries
- `parentExecutionId` for delegation chains

---

### missions

Long-running autonomous agent tasks.

```typescript
type MissionStatus = 'pending' | 'running' | 'paused' | 'completed' | 'failed'
type AgentType = 'claude' | 'gemini' | 'codex'

interface Mission {
  id: string
  userId: string
  organizationId: string
  spaceId?: string
  title: string
  goal: string
  agentType: AgentType

  // Schedule
  schedule: {
    type: 'once' | 'cron' | 'continuous'
    value?: string                   // Cron expression
    lastRun?: number
    nextRun?: number
  }

  // State
  state: {
    currentStep: string
    contextSummary: string
    artifacts: string[]
    memory: Record<string, any>
  }

  status: MissionStatus
  logs: MissionLogEntry[]

  // Timestamps
  createdAt: number
  updatedAt: number
}

interface MissionLogEntry {
  timestamp: number
  level: 'info' | 'warn' | 'error'
  message: string
  metadata?: any
}
```

**Indexes Required:**
- Composite: `organizationId` + `status` + `createdAt` (desc)
- `userId` for user missions
- `spaceId` for space missions

---

### scheduledTasks (Deprecated)

> **Deprecated:** This collection has been superseded by the `missions` collection. New scheduled automations should use missions instead. This collection is maintained for backward compatibility only.

Scheduled agent automations (cron jobs).

```typescript
interface ScheduledTask {
  id: string
  organizationId: string
  characterId: string                // The agent's ID
  spaceId?: string                   // Optional space context

  // Task details
  name: string
  prompt: string                     // What to ask the agent
  schedule: string                   // Cron expression

  // State
  isActive: boolean
  lastRun?: Date | null
  lastRunStatus?: 'success' | 'error' | 'running' | null
  lastRunSessionId?: string | null
  nextRun?: Date | null

  // Metadata
  createdAt: Date
  createdBy: string
  updatedAt?: Date | null
}
```

**Related Collection:** `scheduledTaskExecutions/{executionId}` stores execution history.

**Indexes Required:**
- Composite: `organizationId` + `isActive` + `nextRun` (asc)
- `characterId` for agent schedule queries
- `createdBy` for creator queries

---

### skills

Reusable prompt packages that extend agent capabilities.

```typescript
type SkillVisibility = 'private' | 'organization' | 'public'
type SkillCategory = 'research' | 'writing' | 'data' | 'code' | 'marketing' | 'productivity' | 'file-handling' | 'other'

interface Skill {
  id: string

  // Identity
  name: string                       // kebab-case (e.g., "research-assistant")
  displayName: string                // Human readable
  description: string                // When to invoke

  // Content
  prompt: string                     // SKILL.md markdown instructions

  // Dependencies
  builtInTools?: string[]            // ["Read", "Write", "Bash", "WebSearch"]
  requiredMCPs?: string[]            // ["google-analytics", "bigquery"]

  // Categorization
  category: SkillCategory
  tags?: string[]
  icon?: string                      // Emoji or icon name

  // Ownership
  organizationId: string
  ownerId: string
  visibility: SkillVisibility

  // Marketplace
  featured?: boolean
  verified?: boolean
  usageCount?: number
  rating?: number                    // 1-5
  ratingCount?: number

  // Versioning
  version: number
  changelog?: string

  // Timestamps
  createdAt: Date | Timestamp
  updatedAt: Date | Timestamp

  // Soft delete
  isDeleted?: boolean
  deletedAt?: Date | null
}
```

**Example Document:**
```json
{
  "id": "skill_research",
  "name": "research-assistant",
  "displayName": "Research Assistant",
  "description": "Deep research on a topic using web search",
  "prompt": "# Research Assistant\n\nYou are a research specialist...",
  "category": "research",
  "icon": "🔍",
  "builtInTools": ["WebSearch", "WebFetch", "Read", "Write"],
  "organizationId": "org_abc123",
  "ownerId": "user_xyz",
  "visibility": "organization",
  "version": 1,
  "createdAt": "2024-01-15T10:00:00Z"
}
```

**Indexes Required:**
- Composite: `organizationId` + `visibility` + `createdAt` (desc)
- `category` + `visibility` for marketplace
- `featured` for featured skills

---

### plugins

Configuration packages (GitHub repos) that bundle commands, agents, skills, and MCP servers.

```typescript
type PluginVisibility = 'private' | 'organization' | 'public'
type PluginSourceType = 'github' | 'url' | 'path'

interface Plugin {
  id: string

  // Basic info
  name: string                       // kebab-case identifier
  displayName: string
  description: string
  icon?: string

  // Source
  source: {
    source: PluginSourceType
    repo?: string                    // owner/repo (GitHub)
    url?: string                     // Git URL
    path?: string                    // Local path
    ref?: string                     // branch/tag/commit
  }

  // Manifest (cached from repo)
  manifest?: {
    name: string
    description: string
    version: string
    author?: {
      name: string
      email?: string
      url?: string
    }
    homepage?: string
    repository?: string
    license?: string
    keywords?: string[]
    category?: string
    // Component flags
    commands?: boolean | string[]
    agents?: boolean | string[]
    skills?: boolean | string[]
    hooks?: boolean | string[]
    mcpServers?: boolean | string[]
  }

  // Ownership
  organizationId: string
  ownerId: string
  visibility: PluginVisibility

  // Stats
  installCount?: number
  starCount?: number

  // Verification
  verified?: boolean
  featured?: boolean

  // Timestamps
  createdAt: Date
  updatedAt: Date

  // Soft delete
  isDeleted?: boolean
  deletedAt?: Date | null
}
```

**Related Collection:** `pluginInstallations/{installationId}` tracks space installations.

**Indexes Required:**
- Composite: `organizationId` + `visibility` + `createdAt` (desc)
- `visibility` for marketplace
- `featured` for featured plugins

---

### personal_access_tokens

API tokens for programmatic access.

```typescript
interface PersonalAccessToken {
  id: string                         // Token ID (not the token itself)
  uid: string                        // Owner user ID
  organizationId: string
  name: string                       // User-defined name

  // Token hash (actual token never stored)
  tokenHash: string

  // Usage tracking
  lastUsedAt?: Date | null
  createdAt: Date
  expiresAt?: Date | null
}
```

**Security:** Server-side managed. Users can only read/delete their own tokens.

**Indexes Required:**
- Composite: `uid` + `organizationId`
- `tokenHash` for authentication (unique)

---

### invites

Organization invitation management.

```typescript
interface Invite {
  id: string
  organizationId: string
  email: string
  role: 'member' | 'admin'
  invitedBy: string                  // User ID
  status: 'pending' | 'accepted' | 'expired'
  expiresAt: Date
  createdAt: Date
}
```

**Security:** Public read (needed for invite links). Only org owners can create/update/delete.

---

## Server-Side Only Collections

These collections have no client access and are managed exclusively via Admin SDK:

### authStates
Authentication tier caching for performance.

### cli_pending_auth
Temporary OAuth codes for CLI authentication.

### cli_refresh_tokens
Refresh tokens for CLI tool.

### partner_connections
RSA keys for partner authentication (encrypted private keys).

### partner_organizations
Links partners to organizations.

### usageLogs
Detailed per-request usage logs.

---

## Read-Only Collections (Client)

### usageAggregates
Aggregated usage metrics. Structure: `usageAggregates/{period}/data/{orgId}_{dateKey}`

```typescript
interface UsageAggregate {
  organizationId: string
  period: 'daily' | 'monthly'
  dateKey: string                    // YYYY-MM-DD

  // Metrics
  totalRequests: number
  totalTokensInput: number
  totalTokensOutput: number
  totalCost: number

  // Breakdowns
  byAgent: Record<string, {
    requests: number
    tokensInput: number
    tokensOutput: number
    cost: number
  }>
}
```

**Security:** Org members can read. Writing is server-side only.

### creditTransactions
Credit balance history.

```typescript
interface CreditTransaction {
  id: string
  organizationId: string
  type: 'purchase' | 'usage' | 'refund' | 'grant'
  amount: number                     // Positive or negative
  balance: number                    // Running balance
  description: string
  metadata?: any
  createdAt: Date
}
```

**Security:** Org members can read. Writing is server-side only.

---

## Indexes Summary

### Required Composite Indexes

```
// Organizations
organizations: members (array-contains)

// Agents (character collection)
character: organizationId + isTemplate + createdAt (desc)
character: ownerId + visibility

// Spaces
spaces: organizationId + updatedAt (desc)

// Chats
chats: organizationId + spaceId + updatedAt (desc)

// Tasks
tasks: organizationId + status + createdAt (desc)

// Executions
executions: organizationId + chatId + startedAt (desc)

// Missions
missions: organizationId + status + createdAt (desc)

// Scheduled Tasks
scheduledTasks: organizationId + isActive + nextRun (asc)

// Skills
skills: organizationId + visibility + createdAt (desc)
skills: category + visibility

// Plugins
plugins: organizationId + visibility + createdAt (desc)

// Personal Access Tokens
personal_access_tokens: uid + organizationId
```

### Single-Field Indexes

Most `createdAt`, `updatedAt`, `ownerId`, `visibility` fields are auto-indexed by Firestore.

---

## Security Rules Summary

### Access Patterns

**Owner-only:**
- `users` - Only owner can read/update
- `userProfiles` - Only owner can read/update
- `personal_access_tokens` - Owner can read/delete

**Org Member Access:**
- `organizations` - Members can read
- `spaces` - Filtered by visibility
- `chats` - Owner or org members
- `tasks` - Org members
- `executions` - Org members
- `missions` - Org members
- `scheduledTasks` - Org members

**Visibility-Based:**
- `character` (agents) - Public/org/private/unlisted
- `skills` - Public/org/private
- `plugins` - Public/org/private

**Server-Only:**
- `authStates`
- `cli_pending_auth`
- `cli_refresh_tokens`
- `partner_connections`
- `partner_organizations`
- `usageLogs`

**Read-Only (Client):**
- `usageAggregates` - Org members read
- `creditTransactions` - Org members read

### Encrypted Fields

These fields are encrypted server-side and cannot be modified from client:

- `organizations.encryptedAnthropicApiKey`
- `userProfiles.encryptedAnthropicApiKey`
- `userProfiles.encryptedClaudeCodeOAuthToken`

Client updates attempting to modify these fields will be rejected by security rules.

---

## Best Practices

### Firestore Constraints

1. **No Undefined Values**: Firestore doesn't allow `undefined`. Use `null` or omit the field.

```typescript
// ❌ Bad
await updateDoc(docRef, { optional: undefined })

// ✅ Good
await updateDoc(docRef, { optional: value ?? null })

// ✅ Better - omit undefined
const cleanData = Object.fromEntries(
  Object.entries(data).filter(([_, v]) => v !== undefined)
)
```

2. **Use Server Timestamps**: Always use `serverTimestamp()` for consistency.

```typescript
import { serverTimestamp } from 'firebase/firestore'

await addDoc(collection, {
  ...data,
  createdAt: serverTimestamp()
})
```

3. **Cleanup Subscriptions**: Always unsubscribe from real-time listeners.

```typescript
const unsubscribe = onSnapshot(query, snapshot => { })

onUnmounted(() => {
  if (unsubscribe) unsubscribe()
})
```

### Soft Deletes

Most collections use soft deletes for recoverability:

```typescript
// Soft delete
await updateDoc(docRef, {
  isDeleted: true,
  deletedAt: new Date()
})

// Restore
await updateDoc(docRef, {
  isDeleted: false,
  deletedAt: null
})
```

### Optimistic Updates

For better UX, the app uses optimistic updates for creates:

```typescript
// Add optimistic item immediately
optimisticItems.value.push(newItem)

// Create in Firestore
const docRef = await addDoc(collection, data)

// Snapshot listener updates with real data
```

---

## Migration Notes

### Legacy Fields

Some collections have deprecated fields maintained for backward compatibility:

**Agents (character collection):**
- `system_message` → Use `systemPrompt` (preferred)
- `skills` (inline) → Use `skillIds` (references)
- `tools` → Use `advanced_tools`

**UserProfiles:**
- `anthropicApiKey` (plaintext) → Use `encryptedAnthropicApiKey`
- `claudeCodeOAuthToken` (plaintext) → Use `encryptedClaudeCodeOAuthToken`

### Data Migration

When migrating data, always:
1. Create new fields alongside old ones
2. Update application code to read from both
3. Background job migrates data
4. Remove old field references after migration complete
5. Keep deprecated fields documented for 6 months minimum