CODITECT Database Architecture
Executive Summary
The CODITECT Cloud Platform utilizes a sophisticated multi-tenant PostgreSQL database architecture designed for enterprise-grade SaaS operations. This document provides comprehensive documentation of the database architecture, design patterns, and operational considerations.
Technology Stack
| Component | Technology | Version | Purpose |
|---|---|---|---|
| Database | PostgreSQL | 16 | Primary relational database |
| Hosting | Google Cloud SQL | Latest | Managed PostgreSQL service |
| ORM | Django | 5.2.9 | Object-relational mapping |
| Multi-Tenancy | django-multitenant | 3.2+ | Tenant isolation |
| Migration | Django Migrations | Built-in | Schema version control |
Key Capabilities
- Multi-Tenant Isolation: Row-level security with TenantModel pattern
- 65 Tables: Across 10 Django applications
- 26 Models: In active use with comprehensive indexing
- ACID Compliance: Full transactional integrity
- Cloud-Native: Managed service with HA and backups
Table of Contents
- System Vision
- Multi-Tenant Architecture
- Entity Relationship Diagram
- Domain Model
- Data Flow Patterns
- Security Model
- Performance Optimization
- Scalability & High Availability
- Operational Procedures
- Future Enhancements
1. System Vision
Primary Domains
The CODITECT database serves five primary business domains:
┌─────────────────────────────────────────────────────────────────────────────┐
│ CODITECT DATABASE DOMAINS │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │
│ │ 1. LICENSE │ │ 2. COMMERCE │ │ 3. CONTEXT │ │
│ │ MANAGEMENT │ │ & BILLING │ │ SYNC │ │
│ ├─────────────────┤ ├─────────────────┤ ├─────────────────┤ │
│ │ • Licenses │ │ • Products │ │ • ContextMessage│ │
│ │ • Activations │ │ • Carts │ │ • SyncCursor │ │
│ │ • Sessions │ │ • Orders │ │ • SyncStats │ │
│ │ │ │ • Entitlements │ │ • TaskTracking │ │
│ │ │ │ • Subscriptions │ │ • TaskMessage │ │
│ └─────────────────┘ └─────────────────┘ └─────────────────┘ │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ 4. IDENTITY │ │ 5. WORKSTATION │ │
│ │ & ACCESS │ │ MANAGEMENT │ │
│ ├─────────────────┤ ├─────────────────┤ │
│ │ • Tenants │ │ • Workstations │ │
│ │ • Users │ │ • Sessions │ │
│ │ • Memberships │ │ • Repositories │ │
│ │ • Teams │ │ │ │
│ │ • Projects │ │ │ │
│ │ • Permissions │ │ │ │
│ └─────────────────┘ └─────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Application Registry
| App | Models | Tables | Purpose |
|---|---|---|---|
tenants | 1 | 1 | Multi-tenant root entity |
users | 1 | 1 | User authentication |
organizations | 4 | 4 | Teams and projects |
licenses | 3 | 3 | License management |
commerce | 4 | 4 | Shopping and orders |
subscriptions | 3 | 3 | Recurring billing |
permissions | 1 | 1 | RBAC matrix |
context | 5 | 5 | Context sync + task tracking |
workstations | 2 | 2 | Cloud workstations |
repositories | 2 | 2 | Git repository access |
| Total | 26 | 26 | + Django system tables |
2. Multi-Tenant Architecture
TenantModel Base Pattern
All tenant-scoped models inherit from TenantModel which provides automatic tenant isolation:
from django_multitenant.models import TenantModel
from django_multitenant.fields import TenantForeignKey
from django_multitenant.mixins import TenantModelMixin
class TenantScopedModel(TenantModelMixin, models.Model):
"""Base class for all tenant-scoped models."""
tenant_id = 'tenant_id' # Composite primary key field
tenant = models.ForeignKey(
'tenants.Tenant',
on_delete=models.CASCADE,
db_column='tenant_id',
help_text='Tenant this record belongs to'
)
class Meta:
abstract = True
objects = TenantManager()
Isolation Mechanism
┌─────────────────────────────────────────────────────────────────────────────┐
│ MULTI-TENANT ISOLATION FLOW │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Request Middleware Database │
│ │ │ │ │
│ │ JWT Token │ │ │
│ ├──────────────────────►│ │ │
│ │ │ │ │
│ │ ┌────┴────┐ │ │
│ │ │ Extract │ │ │
│ │ │tenant_id│ │ │
│ │ └────┬────┘ │ │
│ │ │ │ │
│ │ ┌────┴────┐ │ │
│ │ │ set_ │ │ │
│ │ │ current │ │ │
│ │ │ tenant │ │ │
│ │ └────┬────┘ │ │
│ │ │ │ │
│ │ │ WHERE tenant_id = ? │ │
│ │ ├────────────────────────────►│ │
│ │ │ │ │
│ │ │ Filtered Results │ │
│ │◄───────────────────────┼─────────────────────────────│ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Tenant Hierarchy
Platform (AZ1.AI)
└── Tenant (Customer Organization)
├── tenant.id (UUID) ─────────────────────┐
├── Users │
│ └── user.tenant_id ───────────────────┤
├── Teams │
│ └── team.tenant_id ───────────────────┤
├── Projects │ All reference
│ └── project.tenant_id ────────────────┤ same tenant
├── Licenses │
│ └── (via user.tenant_id) ─────────────┤
├── Subscriptions │
│ └── subscription.tenant_id ───────────┤
├── Context Messages │
│ └── context.tenant_id ────────────────┤
└── Task Tracking │
└── task.tenant_id ───────────────────┘
3. Entity Relationship Diagram
4. Domain Model
4.1 Context Sync Domain (ADR-053)
The context sync domain enables Claude Code session data to sync between local SQLite and cloud PostgreSQL:
| Model | Fields | Purpose |
|---|---|---|
ContextMessage | 9 | Synced session messages |
SyncCursor | 8 | Per-device sync position |
SyncStats | 8 | Aggregated sync statistics |
TaskTracking | 20 | Task status and metrics |
TaskMessage | 6 | Task-to-message relationships |
4.2 Task Tracking Model (New - Migration 0004)
The TaskTracking model captures Claude Code todo list operations:
class TaskTracking(TenantModel):
"""Tracks task execution from Claude Code TodoWrite operations."""
# Identity
id = models.UUIDField(primary_key=True, default=uuid.uuid4)
tenant = TenantForeignKey(Tenant, on_delete=models.CASCADE)
user_id = models.CharField(max_length=63, db_index=True)
# Task Definition
task_id = models.CharField(max_length=50, db_index=True) # e.g., "A.9.1.1"
description = models.TextField() # TodoWrite content field
active_form = models.CharField(max_length=255, blank=True) # Present continuous
# Context
session_id = models.CharField(max_length=255, db_index=True, blank=True)
project_id = models.CharField(max_length=255, db_index=True, blank=True)
# Status
status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='pending')
outcome = models.CharField(max_length=20, choices=OUTCOME_CHOICES, blank=True)
outcome_score = models.FloatField(null=True, blank=True) # 0.0-1.0
# Metrics
tool_success_count = models.IntegerField(default=0)
tool_error_count = models.IntegerField(default=0)
user_corrections = models.IntegerField(default=0)
# Timestamps
started_at = models.DateTimeField(null=True, blank=True)
completed_at = models.DateTimeField(null=True, blank=True)
synced_at = models.DateTimeField(auto_now_add=True, db_index=True)
updated_at = models.DateTimeField(auto_now=True)
# Sync
content_hash = models.CharField(max_length=64, unique=True, db_index=True)
sync_cursor = models.BigIntegerField(unique=True, null=True, db_index=True)
client_version = models.CharField(max_length=50, blank=True)
hardware_id = models.CharField(max_length=255, blank=True)
4.3 TaskMessage Model (New - Migration 0004)
Links tasks to their related context messages:
class TaskMessage(TenantModel):
"""Links TaskTracking records to related ContextMessages."""
id = models.UUIDField(primary_key=True, default=uuid.uuid4)
tenant = TenantForeignKey(Tenant, on_delete=models.CASCADE)
task = TenantForeignKey(TaskTracking, on_delete=models.CASCADE, related_name='messages')
message = TenantForeignKey(ContextMessage, on_delete=models.CASCADE, related_name='tasks')
sequence = models.IntegerField(default=0)
created_at = models.DateTimeField(auto_now_add=True)
class Meta:
unique_together = [('tenant', 'task', 'message')]
5. Data Flow Patterns
5.1 License Acquisition Flow
┌─────────────────────────────────────────────────────────────────────────────┐
│ LICENSE ACQUISITION DATA FLOW │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ 1. User Authenticates │
│ ├── JWT validated by Identity Platform │
│ └── tenant_id extracted from token claims │
│ │
│ 2. License Check │
│ ├── Query: SELECT * FROM licenses WHERE user_id = ? AND status = ? │
│ └── Tenant filter auto-applied: AND tenant_id = current_tenant │
│ │
│ 3. Seat Availability (Redis Lua Script) │
│ ├── KEYS: license:{license_id}:seats │
│ ├── Atomic SCARD + SADD │
│ └── Prevents race conditions │
│ │
│ 4. Activation Record │
│ ├── INSERT INTO activations (license_id, hardware_id, ...) │
│ └── ON CONFLICT DO UPDATE last_seen_at │
│ │
│ 5. Session Creation │
│ ├── INSERT INTO sessions (activation_id, expires_at, ...) │
│ └── expires_at = NOW() + INTERVAL '6 minutes' │
│ │
│ 6. License Token Signed (Cloud KMS RSA-4096) │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
5.2 Context Sync Flow (ADR-053)
┌─────────────────────────────────────────────────────────────────────────────┐
│ CONTEXT SYNC DATA FLOW │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ LOCAL (SQLite) CLOUD (PostgreSQL) │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ context.db │ │ Cloud SQL │ │
│ ├─────────────────┤ ├─────────────────┤ │
│ │ messages │───── PUSH ────────►│ context_messages│ │
│ │ task_tracking │───── PUSH ────────►│ context_task_ │ │
│ │ task_messages │ (POST) │ tracking │ │
│ │ sync_queue │ │ context_task_ │ │
│ └─────────────────┘ │ messages │ │
│ │ └─────────────────┘ │
│ │ │ │
│ │◄────────────── PULL ─────────────────┤ │
│ (GET) │
│ │
│ Deduplication: SHA256(tenant_id:session_id:content) │
│ Sync Position: sync_cursor (BigInteger, monotonic) │
│ Conflict Resolution: Last-write-wins based on synced_at │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
5.3 Task Tracking Lifecycle
┌─────────────────────────────────────────────────────────────────────────────┐
│ TASK TRACKING LIFECYCLE │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────┐ ┌──────────────┐ ┌───────────┐ │
│ │ PENDING │─────►│ IN_PROGRESS │─────►│ COMPLETED │ │
│ └──────────┘ └──────────────┘ └───────────┘ │
│ │ │ │ │
│ │ │ ▼ │
│ │ │ ┌─────────────┐ │
│ │ │ │ OUTCOME │ │
│ │ │ ├─────────────┤ │
│ │ │ │ • success │ │
│ │ │ │ • error │ │
│ │ │ │ • partial │ │
│ │ │ │ • skipped │ │
│ │ │ └─────────────┘ │
│ │ │ │
│ ▼ ▼ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ METRICS TRACKED │ │
│ ├─────────────────────────────────────────────────────┤ │
│ │ • tool_success_count: Successful tool invocations │ │
│ │ • tool_error_count: Failed tool invocations │ │
│ │ • user_corrections: Human interventions │ │
│ │ • outcome_score: Quality score (0.0-1.0) │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
6. Security Model
6.1 Tenant Isolation
| Layer | Mechanism | Implementation |
|---|---|---|
| Application | TenantModel | Auto-filters all queries by tenant_id |
| ORM | TenantManager | set_current_tenant() in middleware |
| Database | Foreign Keys | CASCADE delete with tenant reference |
| API | JWT Claims | tenant_id extracted from token |
6.2 Data Encryption
| Type | Mechanism | Key Management |
|---|---|---|
| At Rest | Cloud SQL encryption | Google-managed or CMEK |
| In Transit | TLS 1.3 | Automatic certificate rotation |
| Application | Sensitive fields | Cloud KMS for license signing |
6.3 Access Control
# RBAC Permission Matrix
PERMISSIONS = {
'owner': ['*'], # Full access to tenant resources
'admin': ['create', 'read', 'update', 'delete'],
'member': ['read', 'create'], # Limited write
'viewer': ['read'], # Read-only
}
6.4 Audit Logging
All database operations are logged with:
user_id- Who performed the actiontenant_id- Which tenantaction- CRUD operationtimestamp- Whenchanges- Before/after diff (for updates)
7. Performance Optimization
7.1 Indexing Strategy
| Table | Index | Purpose |
|---|---|---|
context_task_tracking | (tenant_id, sync_cursor) | Efficient sync polling |
context_task_tracking | (tenant_id, user_id, status) | User task queries |
context_task_tracking | (tenant_id, project_id, status) | Project task queries |
context_task_tracking | (session_id, synced_at) | Session-based queries |
context_task_tracking | (task_id, status) | Task ID lookups |
context_messages | (tenant_id, sync_cursor) | Message sync polling |
context_messages | (user_id, content_hash) | Deduplication |
7.2 Query Optimization
# Efficient cursor-based pagination
def get_tasks_since(cursor: int, limit: int = 100):
return TaskTracking.objects.filter(
sync_cursor__gt=cursor
).order_by('sync_cursor')[:limit]
# Avoid N+1 with prefetch
def get_tasks_with_messages(task_ids: list):
return TaskTracking.objects.filter(
id__in=task_ids
).prefetch_related('messages__message')
7.3 Connection Pooling
| Setting | Value | Purpose |
|---|---|---|
CONN_MAX_AGE | 600 | Keep connections alive 10 min |
MAX_CONNECTIONS | 100 | Cloud SQL connection limit |
| PgBouncer | Transaction mode | Connection multiplexing |
8. Scalability & High Availability
8.1 Current Configuration
| Component | Configuration | Capacity |
|---|---|---|
| Cloud SQL | db-custom-2-7680 | 2 vCPU, 7.5 GB RAM |
| Storage | SSD, auto-grow | 10 GB initial |
| HA | Regional (us-central1) | Automatic failover |
| Backups | Automated daily | 7-day retention |
8.2 Read Replicas
For read-heavy workloads, deploy read replicas:
# Django database routing
DATABASES = {
'default': {'HOST': 'primary.cloudsql'},
'replica': {'HOST': 'replica.cloudsql'},
}
class DatabaseRouter:
def db_for_read(self, model, **hints):
if model._meta.app_label in ['context', 'repositories']:
return 'replica'
return 'default'
8.3 Backup & Recovery
| Metric | Target | Current |
|---|---|---|
| RPO (Recovery Point Objective) | < 5 min | Automated backups + WAL |
| RTO (Recovery Time Objective) | < 15 min | HA failover |
| Retention | 7 days | Configurable to 365 |
9. Operational Procedures
9.1 Migration Workflow
# 1. Create migration
python manage.py makemigrations context
# 2. Review migration file
cat context/migrations/0004_*.py
# 3. Test locally
python manage.py migrate --plan
python manage.py migrate
# 4. Deploy to staging
kubectl exec -it django-backend -- python manage.py migrate
# 5. Deploy to production (with approval)
kubectl exec -it django-backend -n coditect-prod -- python manage.py migrate
9.2 Monitoring Queries
-- Active connections by tenant
SELECT tenant_id, COUNT(*) as connections
FROM pg_stat_activity
WHERE datname = 'coditect'
GROUP BY tenant_id;
-- Slow queries
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;
-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
9.3 Health Checks
# Django health check endpoint
def database_health(request):
try:
from django.db import connection
with connection.cursor() as cursor:
cursor.execute("SELECT 1")
return JsonResponse({'status': 'healthy'})
except Exception as e:
return JsonResponse({'status': 'unhealthy', 'error': str(e)}, status=503)
10. Future Enhancements
10.1 Planned Improvements
| Enhancement | Priority | Timeline |
|---|---|---|
| Citus Sharding | P2 | Q2 2026 |
| TimescaleDB | P3 | Q3 2026 |
| Event Sourcing | P3 | Q4 2026 |
| Read Replicas | P2 | Q2 2026 |
10.2 Citus Horizontal Scaling
For multi-tenant horizontal scaling:
-- Distribute tables by tenant_id
SELECT create_distributed_table('context_messages', 'tenant_id');
SELECT create_distributed_table('context_task_tracking', 'tenant_id');
SELECT create_distributed_table('context_task_messages', 'tenant_id');
10.3 Event Sourcing Consideration
For audit-critical operations:
class TaskEvent(models.Model):
"""Event-sourced task state changes."""
id = models.UUIDField(primary_key=True)
task_id = models.UUIDField(db_index=True)
event_type = models.CharField(max_length=50) # created, started, completed
event_data = models.JSONField()
timestamp = models.DateTimeField(auto_now_add=True)
class Meta:
ordering = ['timestamp']
Related Documents
| Document | Purpose |
|---|---|
| database-schema.md | Detailed table definitions |
| database-integration-guide.md | Integration patterns |
| ADR-053 | Cloud sync architecture |
| ADR-052 | Intent-aware context |
Document Version: 1.0.0 Last Updated: January 4, 2026 Author: CODITECT Engineering Team Status: Active