ADR 045 Team/Project Context Sync Architecture

ADR-045: Team/Project Context Sync Architecture

Status: Accepted Date: 2025-12-30 Author: Hal Casteel Extends: ADR-044 (Custom REST Sync Architecture) Stakeholders: Platform Engineering, Backend, Frontend, DevOps

Decision

CODITECT will extend the context sync system (ADR-044) to support team and project-level backup and synchronization, enabling:

Team-level context sharing - Team members can share context within their team boundary
Project-level context isolation - Context scoped to specific projects within teams
Hierarchical backup - User → Team → Project → Organization backup hierarchy
Cross-device team sync - Team context accessible from any team member's device

Context

Current State (ADR-044)

ADR-044 implemented device-level sync with user/tenant isolation:

SQLite (local) ↔ REST API ↔ PostgreSQL (cloud)
JWT authentication with tenant_id claims
Per-user, per-device context isolation

Gap Identified

The current implementation lacks:

Team-level context sharing - Teams cannot share context insights
Project-level scoping - No project boundary for context isolation
Team backup/restore - Cannot backup entire team context
Cross-team collaboration - Cannot share context across teams (with permission)

Architecture

Extended Data Model

Organization (tenant)
├── Users (individual accounts)
│   └── UserContexts (personal device sync - ADR-044)
├── Teams
│   ├── TeamContexts (shared team knowledge)
│   └── TeamMembers (users with roles)
└── Projects
    ├── ProjectContexts (project-scoped insights)
    └── ProjectMembers (team/user access)

Database Schema Extensions

-- Team Context Table (new)
CREATE TABLE team_contexts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    team_id UUID NOT NULL REFERENCES teams(id),
    message_type VARCHAR(50) NOT NULL,
    content TEXT NOT NULL,
    content_hash VARCHAR(64) NOT NULL,
    metadata JSONB DEFAULT '{}',
    contributed_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(team_id, content_hash)
);

-- Project Context Table (new)
CREATE TABLE project_contexts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    project_id UUID NOT NULL REFERENCES projects(id),
    message_type VARCHAR(50) NOT NULL,
    content TEXT NOT NULL,
    content_hash VARCHAR(64) NOT NULL,
    metadata JSONB DEFAULT '{}',
    contributed_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(project_id, content_hash)
);

-- Team Context Sync Cursor (new)
CREATE TABLE team_sync_cursors (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    team_id UUID NOT NULL REFERENCES teams(id),
    user_id UUID NOT NULL REFERENCES users(id),
    device_id VARCHAR(255) NOT NULL,
    last_sync_at TIMESTAMP WITH TIME ZONE,
    cursor_position BIGINT DEFAULT 0,

    UNIQUE(team_id, user_id, device_id)
);

-- Project Context Sync Cursor (new)
CREATE TABLE project_sync_cursors (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id),
    user_id UUID NOT NULL REFERENCES users(id),
    device_id VARCHAR(255) NOT NULL,
    last_sync_at TIMESTAMP WITH TIME ZONE,
    cursor_position BIGINT DEFAULT 0,

    UNIQUE(project_id, user_id, device_id)
);

-- Row Level Security Policies
ALTER TABLE team_contexts ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_contexts ENABLE ROW LEVEL SECURITY;

CREATE POLICY team_contexts_isolation ON team_contexts
    USING (tenant_id = current_setting('app.tenant_id')::UUID);

CREATE POLICY project_contexts_isolation ON project_contexts
    USING (tenant_id = current_setting('app.tenant_id')::UUID);

API Endpoints (New)

Endpoint	Method	Purpose
`/api/v1/teams/{team_id}/context/push`	POST	Push context to team
`/api/v1/teams/{team_id}/context/pull`	GET	Pull team context
`/api/v1/teams/{team_id}/context/status`	GET	Team sync status
`/api/v1/teams/{team_id}/context/backup`	POST	Create team backup
`/api/v1/teams/{team_id}/context/restore`	POST	Restore team backup
`/api/v1/projects/{project_id}/context/push`	POST	Push context to project
`/api/v1/projects/{project_id}/context/pull`	GET	Pull project context
`/api/v1/projects/{project_id}/context/status`	GET	Project sync status
`/api/v1/projects/{project_id}/context/backup`	POST	Create project backup
`/api/v1/projects/{project_id}/context/restore`	POST	Restore project backup

Extended Sync Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│                          LOCAL CLIENT (per machine)                          │
│                                                                              │
│  ┌──────────────────┐    ┌──────────────────┐    ┌──────────────────┐      │
│  │    Personal      │    │      Team        │    │     Project      │      │
│  │    Context       │    │     Context      │    │    Context       │      │
│  │  (user-scoped)   │    │  (team-scoped)   │    │ (project-scoped) │      │
│  └────────┬─────────┘    └────────┬─────────┘    └────────┬─────────┘      │
│           │                       │                       │                 │
│           └───────────────────────┴───────────────────────┘                 │
│                                   │                                         │
│                           CODITECT Sync Client                              │
│                                   │                                         │
└───────────────────────────────────┼─────────────────────────────────────────┘
                                    │
                     ┌──────────────┴──────────────┐
                     │                             │
                     ▼                             ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                              GKE CLOUD CLUSTER                               │
│                                                                              │
│  ┌──────────────────────────────────────────────────────────────────────┐   │
│  │                         CODITECT API (Django)                        │   │
│  │                                                                      │   │
│  │  ┌────────────────┐  ┌────────────────┐  ┌────────────────┐         │   │
│  │  │ User Context   │  │ Team Context   │  │ Project Context│         │   │
│  │  │ Service        │  │ Service        │  │ Service        │         │   │
│  │  │                │  │                │  │                │         │   │
│  │  │ /api/v1/       │  │ /api/v1/teams/ │  │ /api/v1/       │         │   │
│  │  │ context/*      │  │ {id}/context/* │  │ projects/{id}/ │         │   │
│  │  └────────────────┘  └────────────────┘  │ context/*      │         │   │
│  │                                          └────────────────┘         │   │
│  └──────────────────────────────────────────────────────────────────────┘   │
│                                      │                                      │
│                                      ▼                                      │
│  ┌──────────────────────────────────────────────────────────────────────┐   │
│  │                     PostgreSQL (Cloud SQL)                           │   │
│  │                                                                      │   │
│  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐               │   │
│  │  │ context_     │  │ team_        │  │ project_     │               │   │
│  │  │ messages     │  │ contexts     │  │ contexts     │               │   │
│  │  │ (per-user)   │  │ (per-team)   │  │ (per-project)│               │   │
│  │  └──────────────┘  └──────────────┘  └──────────────┘               │   │
│  └──────────────────────────────────────────────────────────────────────┘   │
│                                                                              │
│  ┌──────────────────────────────────────────────────────────────────────┐   │
│  │                     GCS Backup Storage                               │   │
│  │                                                                      │   │
│  │  gs://coditect-context-backups/                                      │   │
│  │  ├── users/{user_id}/                                                │   │
│  │  ├── teams/{team_id}/                                                │   │
│  │  └── projects/{project_id}/                                          │   │
│  └──────────────────────────────────────────────────────────────────────┘   │
└──────────────────────────────────────────────────────────────────────────────┘

Permission Model

Role	Personal Context	Team Context	Project Context
User	Full access	Read (own teams)	Read (own projects)
Team Member	Own only	Read/Write	Read (team projects)
Team Admin	Own only	Full + Backup	Full + Backup
Project Owner	Own only	Read	Full + Backup
Org Admin	All users	All teams	All projects

Backup & Restore

Backup Types:

Incremental - Changes since last backup (default, hourly)
Full - Complete context snapshot (daily)
On-Demand - User-triggered backup

Backup Storage:

gs://coditect-context-backups/
├── tenants/{tenant_id}/
│   ├── users/{user_id}/
│   │   ├── incremental/YYYY-MM-DD-HH.jsonl.gz
│   │   └── full/YYYY-MM-DD.jsonl.gz
│   ├── teams/{team_id}/
│   │   ├── incremental/YYYY-MM-DD-HH.jsonl.gz
│   │   └── full/YYYY-MM-DD.jsonl.gz
│   └── projects/{project_id}/
│       ├── incremental/YYYY-MM-DD-HH.jsonl.gz
│       └── full/YYYY-MM-DD.jsonl.gz

Retention Policy:

Incremental: 7 days
Full: 90 days
On-Demand: Unlimited (user managed)

Implementation Plan

Phase 1: Database Schema (Week 1)

A.5.1: Create team_contexts table with RLS
A.5.2: Create project_contexts table with RLS
A.5.3: Create sync cursor tables
A.5.4: Run migrations

Phase 2: Team Context API (Week 2)

A.5.5: Team context Django models
A.5.6: Team context serializers
A.5.7: POST /teams/{id}/context/push endpoint
A.5.8: GET /teams/{id}/context/pull endpoint
A.5.9: GET /teams/{id}/context/status endpoint
A.5.10: Team context unit tests

Phase 3: Project Context API (Week 3)

A.5.11: Project context Django models
A.5.12: Project context serializers
A.5.13: POST /projects/{id}/context/push endpoint
A.5.14: GET /projects/{id}/context/pull endpoint
A.5.15: GET /projects/{id}/context/status endpoint
A.5.16: Project context unit tests

Phase 4: Backup System (Week 4)

A.5.17: GCS backup service
A.5.18: POST /teams/{id}/context/backup endpoint
A.5.19: POST /teams/{id}/context/restore endpoint
A.5.20: POST /projects/{id}/context/backup endpoint
A.5.21: POST /projects/{id}/context/restore endpoint
A.5.22: Scheduled backup job (Cloud Scheduler)
A.5.23: Backup/restore integration tests

Total Implementation Time: 4 weeks

Consequences

Positive

Team collaboration - Teams can share context insights
Project isolation - Context scoped appropriately
Enterprise-ready - Hierarchical backup/restore
Compliance - Full audit trail of context changes
Disaster recovery - Multi-level backup to GCS

Negative

Complexity - Additional tables and API endpoints
Storage cost - GCS backup storage (~$0.02/GB/month)
Sync conflicts - More potential for merge conflicts
Permission overhead - Complex permission checking

Risks & Mitigations

Risk	Probability	Impact	Mitigation
Permission bugs	Medium	High	Comprehensive test suite, RLS policies
Backup corruption	Low	High	Checksums, multi-region storage
Sync performance	Medium	Medium	Batch operations, cursor optimization
Storage costs	Low	Low	Compression, retention policies

ADR-044: Custom REST Sync Architecture (extended by this ADR)
ADR-004: Multi-Tenant Strategy
ADR-008: Role-Based Access Control
ADR-009: Multi-Tenant SaaS Architecture
ADR-012: Data Isolation Strategy

References

Decision Made: 2025-12-30 Extends: ADR-044 Confidence Level: 85% (High) Review Date: Q1 2026

Decision​

Context​

Current State (ADR-044)​

Gap Identified​

Architecture​

Extended Data Model​

Database Schema Extensions​

API Endpoints (New)​

Extended Sync Architecture​

Permission Model​

Backup & Restore​

Implementation Plan​

Phase 1: Database Schema (Week 1)​

Phase 2: Team Context API (Week 2)​

Phase 3: Project Context API (Week 3)​

Phase 4: Backup System (Week 4)​

Consequences​

Positive​

Negative​

Risks & Mitigations​

Related ADRs​

References​