V4 Database Models Analysis for T2 Project

Created: 2025-10-14 Purpose: Understand V4 FoundationDB schema patterns for T2 (V5) implementation

Overview

The V4 database models provide comprehensive FoundationDB schema documentation for the CODITECT v4 platform. These models define multi-tenant architecture patterns and key-value structures that can inform T2 (V5) data model design.

Location: docs/reference/database-models/ Source: Copied from archive/coditect-v4/docs/reference/database-models/ Total Files: 20 (19 models + README) Total Size: 260KB documentation

Key Pattern: Multi-Tenant Key Structure

All V4 models follow this pattern:

{tenant_id}/{entity_type}/{entity_id}

Example Keys:

tenant_123/users/user_456
tenant_123/sessions/session_789
tenant_123/projects/project_abc

This ensures complete data isolation at the database level while maintaining efficient query patterns.

Model Categories

1. Core Models (4 files)

Essential models for system foundation:

Model	File	Key Pattern	Purpose
Tenant	`tenant-model.md`	`{tenant_id}/tenant`	Multi-tenant isolation, plan tiers (Free/Starter/Pro/Enterprise)
User	`user-model.md`	`{tenant_id}/users/{user_id}`	User accounts, Argon2id password hashing, roles (Admin/Developer/Manager/Viewer)
Entity	`entity-model.md`	`{tenant_id}/entities/{entity_id}`	Hierarchical org structure (Corporation → Division → Department → Team)
Member	`member-model.md`	`{tenant_id}/members/{member_id}`	Unified abstraction for humans and AI agents

Relevance to T2:

✅ User Model: Critical for V5 authentication and profile management
✅ Tenant Model: Needed if T2 supports multi-tenancy
⚠️ Entity Model: Complex org hierarchy - may not be needed for MVP
⚠️ Member Model: AI/human abstraction - interesting but not MVP critical

2. Project Management (4 files)

Models for project tracking and task management:

Model	File	Key Pattern	Purpose
Project	`project-model.md`	`{tenant_id}/projects/{project_id}`	Project definitions and lifecycle
Task	`task-model.md`	`{tenant_id}/tasks/{task_id}`	Task management and tracking
Workflow	`workflow-model.md`	`{tenant_id}/workflows/{workflow_id}`	Workflow definitions and execution
Task Assignment	`task-assignment-model.md`	`{tenant_id}/task_assignments/{assignment_id}`	Task allocation to members

Relevance to T2:

⚠️ Project Model: T2 uses "sessions" not "projects" - conceptually similar
❌ Task/Workflow Models: Not needed for V5 MVP - these are V4-specific features

3. AI & Automation (3 files)

Models for AI agent integration:

Model	File	Key Pattern	Purpose
Agent Models	`agent-models.md`	`{tenant_id}/agents/{agent_id}`	AI agent configurations and instances (20KB - largest file!)
Session	`session-model.md`	`{tenant_id}/sessions/{session_id}`	User and agent session management, JWT token families
Conversation	`conversation-model.md`	`{tenant_id}/conversations/{conversation_id}`	AI conversation history and context

Relevance to T2:

✅ Session Model: CRITICAL - V5 needs robust session management
✅ Agent Models: Relevant for T2 multi-agent system
✅ Conversation Model: Needed for AI chat history in theia

Key Insight: The session-model.md (422 lines) is the most detailed model and shows:

JWT token family rotation patterns
Refresh token security
Session lifecycle management
Multi-device session handling

4. Access Control & Compliance (3 files)

Security and regulatory models:

Model	File	Key Pattern	Purpose
RBAC	`rbac-model.md`	`{tenant_id}/roles/{role_id}`	Role-based access control
Audit	`audit-model.md`	`{tenant_id}/audit/{audit_id}`	Comprehensive audit logging
Compliance	`compliance-model.md`	`{tenant_id}/compliance/{compliance_id}`	Regulatory compliance tracking

Relevance to T2:

✅ RBAC: Important for V5 permissions (Admin vs User)
✅ Audit: Needed for SOC2/GDPR compliance
⚠️ Compliance: Enterprise feature - not MVP critical

5. Business Operations (3 files)

Usage tracking and billing:

Model	File	Key Pattern	Purpose
Cost	`cost-model.md`	`{tenant_id}/costs/{cost_id}`	Usage tracking and billing
Usage	`usage-model.md`	`{tenant_id}/usage/{usage_id}`	Resource consumption metrics
Subsidiary	`subsidiary-model.md`	`{tenant_id}/subsidiaries/{subsidiary_id}`	Multi-entity support

Relevance to T2:

⚠️ Cost/Usage: Nice-to-have for monitoring llm usage
❌ Subsidiary: Enterprise feature - not needed for MVP

6. Associations (2 files)

Relationship models:

Model	File	Key Pattern	Purpose
User-Tenant Association	`user-tenant-association-model.md`	`{tenant_id}/user_tenants/{association_id}`	User-tenant relationships
Project-Member	`PROJECT-member-model.md`	`{tenant_id}/project_members/{member_id}`	Project team assignments

Relevance to T2:

⚠️ User-Tenant: Only needed if T2 supports multi-tenancy
❌ Project-Member: V4-specific project management

Priority Models for T2 (V5) MVP

High Priority ✅ (Must implement)

user-model.md - Foundation for authentication
- Argon2id password hashing
- Role-based permissions (Admin/User)
- Profile management
session-model.md - Critical for session management
- JWT token family rotation
- Refresh token security
- Multi-device session handling
- 422 lines of detailed patterns
audit-model.md - Compliance requirement
- Event logging
- User activity tracking
- Security audit trail

Medium Priority ⚠️ (Consider for post-MVP)

agent-models.md - AI agent configuration (20KB of patterns)
conversation-model.md - AI chat history
rbac-model.md - Advanced permissions
tenant-model.md - If multi-tenancy is added

Low Priority ❌ (V4-specific, not needed)

Task, Project, Workflow models (V4 project management)
Cost, Usage models (billing)
Subsidiary, Entity models (enterprise features)

Key Differences: V4 vs V5 (T2)

Aspect	V4 Pattern	T2 (V5) Pattern
Key Structure	`{tenant_id}/users/{user_id}`	`users/{user_id}` (tenant_id in value)
Multi-Tenancy	Hard tenant isolation in keys	Simplified single-tenant MVP
Project Model	Projects + Tasks + Workflows	Sessions (simpler)
IDE Integration	Custom web app	Eclipse theia (95% provided)
Agent System	CODITECT agents	MCP + A2A protocols

Migration Considerations

When implementing V5 data models:

Simplify Key Structure: T2 uses simpler keys without tenant prefix for MVP
- V4: {tenant_id}/users/{user_id}
- V5: users/{user_id} (with tenant_id in value)
Focus on Core Models: Start with User, Session, Audit
- Skip V4-specific models (Task, Project, Workflow)
- Defer enterprise features (Subsidiary, Cost tracking)
Adapt Patterns, Not Implementation: Learn from V4 patterns but design for T2 architecture
- V4 uses custom web app
- T2 uses theia (95% provided)
- Don't copy V4 patterns blindly
Session Management Priority: session-model.md is the most detailed (422 lines)
- Study JWT token family rotation
- Understand refresh token security
- Learn multi-device session handling

How to Use These Models

For Reading/Learning:

# Start with core models
cat docs/reference/database-models/user-model.md
cat docs/reference/database-models/session-model.md
cat docs/reference/database-models/tenant-model.md

# Study multi-tenant key patterns
grep -r "{tenant_id}" docs/reference/database-models/

# Understand session management (most detailed model)
cat docs/reference/database-models/session-model.md | wc -l  # 422 lines!

For Implementation Reference:

Never copy V4 code directly - T2 has different architecture
Learn patterns - Multi-tenant isolation, JWT security, audit logging
Adapt for theia - T2 uses Eclipse theia, not custom web app
Check if theia provides it - Before implementing, see if theia already has the feature

Summary

The V4 database models provide valuable reference patterns for T2 (V5) FoundationDB implementation:

✅ Use for:

Understanding multi-tenant key structures
Learning JWT session management patterns
Studying audit logging approaches
Designing user authentication

❌ Don't use for:

Copying code directly (different architecture)
Implementing V4-specific features (Task/Project management)
Building features theia already provides
Enterprise features not needed for MVP

🎯 Focus on:

user-model.md (authentication foundation)
session-model.md (422 lines - most detailed!)
audit-model.md (compliance requirement)
Adapting patterns, not copying implementation

Total Documentation: 260KB across 20 files Most Detailed Model: session-model.md (422 lines, 12KB) Most Complex Model: agent-models.md (20KB) Key Pattern: {tenant_id}/{entity_type}/{entity_id}

Overview​

Key Pattern: Multi-Tenant Key Structure​

Model Categories​

1. Core Models (4 files)​

2. Project Management (4 files)​

3. AI & Automation (3 files)​

4. Access Control & Compliance (3 files)​

5. Business Operations (3 files)​

6. Associations (2 files)​

Priority Models for T2 (V5) MVP​

High Priority ✅ (Must implement)​

Medium Priority ⚠️ (Consider for post-MVP)​

Low Priority ❌ (V4-specific, not needed)​

Key Differences: V4 vs V5 (T2)​

Migration Considerations​

How to Use These Models​

For Reading/Learning:​

For Implementation Reference:​

Related Documentation​

FDB Migration:​

V4 Reference:​

Architecture Decisions:​

Summary​

Overview

Key Pattern: Multi-Tenant Key Structure

Model Categories

1. Core Models (4 files)

2. Project Management (4 files)

3. AI & Automation (3 files)

4. Access Control & Compliance (3 files)

5. Business Operations (3 files)

6. Associations (2 files)

Priority Models for T2 (V5) MVP

High Priority ✅ (Must implement)

Medium Priority ⚠️ (Consider for post-MVP)

Low Priority ❌ (V4-specific, not needed)

Key Differences: V4 vs V5 (T2)

Migration Considerations

How to Use These Models

For Reading/Learning:

For Implementation Reference:

Related Documentation

FDB Migration:

V4 Reference:

Architecture Decisions:

Summary