FoundationDB Queries & Patterns

FoundationDB Queries & Patterns

How to Use This Skill

1. Review the patterns and examples below
2. Apply the relevant patterns to your implementation
3. Follow the best practices outlined in this skill

Expert skill for working with FoundationDB in the T2 multi-tenant architecture.

When to Use

✅ Use this skill when:

• Implementing new backend endpoints with FDB persistence (users, sessions, workflows)
• Debugging FDB key patterns or tenant isolation issues
• Adding new data models that require multi-tenant support
• Writing repository query methods (backend/src/db/repositories.rs)
• Reviewing code for FDB security vulnerabilities (missing tenant_id)
• Understanding existing FDB schema from backend/src/db/models.rs
• Need time savings: 60% faster FDB implementation (30→12 min debugging)

❌ Don't use this skill when:

• Working on frontend-only features (use React/TypeScript patterns)
• Using OPFS browser cache (different pattern)
• Simple file reads without database queries
• Cloud infrastructure changes (use GKE deployment skills)

Key Patterns

Tenant Isolation

All FDB keys MUST be prefixed with tenant_id:

// CORRECT: Tenant-isolated key
let key = format!("/{}/users/{}", tenant_id, user_id);

// WRONG: No tenant isolation (security vulnerability!)
let key = format!("/users/{}", user_id);

Common Key Patterns (from backend/src/db/models.rs)

// Users
/{tenant_id}/users/{user_id}

// Auth Sessions
/{tenant_id}/auth_sessions/{session_id}

// Workspace Sessions
/{tenant_id}/workspace_sessions/{session_id}

// Workflows (Recursive)
/{tenant_id}/workflows/{workflow_id}/state
/{tenant_id}/workflows/{workflow_id}/history/{transition_id}
/{tenant_id}/workflows/{workflow_id}/checkpoints/{checkpoint_id}

// Conversations
/{tenant_id}/conversations/{conversation_id}
/{tenant_id}/messages/{message_id}

// Files
/{tenant_id}/files/{file_id}

// Agents
/{tenant_id}/agents/{agent_id}

// Audit
/{tenant_id}/audit/{audit_id}

Query Helpers (Python)

See core/query_builder.py for FDB key construction with validation.

Best Practices

✅ Always use tenant_id in keys ✅ Use repositories (backend/src/db/repositories.rs) - never direct FDB ✅ Transaction pattern: create → get → set → commit ✅ Error handling: Use map_err() for TransactionCommitError

❌ Never hardcode keys ❌ Never skip tenant validation ❌ Never use global keys

Repository Query Patterns

ALWAYS use repository methods, NEVER raw FDB calls:

// CORRECT: Use repository pattern (backend/src/db/repositories.rs)
use crate::db::repositories::UserRepository;

pub async fn create_user(
    db: web::Data<Database>,
    tenant_id: &Uuid,
    user: CreateUserRequest
) -> Result<User, Error> {
    UserRepository::create(&db, tenant_id, &user).await
}

// WRONG: Direct FDB access (bypasses validation, error-prone)
let key = format!("/{}/users/{}", tenant_id, user_id);
trx.set(&key.as_bytes(), &value);  // ❌ Don't do this!

Complete CRUD Example (Repository Pattern)

// backend/src/db/repositories.rs

pub struct SessionRepository;

impl SessionRepository {
    // CREATE - With tenant isolation
    pub async fn create(
        db: &Database,
        tenant_id: &Uuid,
        session: &CreateSessionRequest
    ) -> Result<Session, FdbError> {
        let trx = db.create_trx()?;
        let session_id = Uuid::new_v4();
        let key = format!("/{}/auth_sessions/{}", tenant_id, session_id);
        let session = Session {
            session_id,
            tenant_id: *tenant_id,
            user_id: session.user_id,
            is_active: true,
            created_at: Utc::now(),
        };
        let value = serde_json::to_vec(&session)?;
        trx.set(&key.as_bytes(), &value);
        trx.commit().await.map_err(|e| {
            FdbError::TransactionError(format!("Failed to create session: {}", e))
        })?;
        Ok(session)
    }

    // READ - Single item
    pub async fn get(
        db: &Database,
        tenant_id: &Uuid,
        session_id: &Uuid
    ) -> Result<Option<Session>, FdbError> {
        let trx = db.create_trx()?;
        let key = format!("/{}/auth_sessions/{}", tenant_id, session_id);
        match trx.get(&key.as_bytes(), false).await? {
            Some(bytes) => {
                let session: Session = serde_json::from_slice(&bytes)?;
                Ok(Some(session))
            },
            None => Ok(None)
        }
    }

    // READ - List by tenant (range query)
    pub async fn list_by_tenant(
        db: &Database,
        tenant_id: &Uuid
    ) -> Result<Vec<Session>, FdbError> {
        let trx = db.create_trx()?;
        let prefix = format!("/{}/auth_sessions/", tenant_id);
        let range = fdb::RangeOption {
            begin: fdb::KeySelector::first_greater_or_equal(prefix.as_bytes()),
            end: fdb::KeySelector::first_greater_or_equal(
                format!("{}\xFF", prefix).as_bytes()
            ),
            ..Default::default()
        };

        let mut sessions = Vec::new();
        let results = trx.get_range(&range, 1000, false).await?;
        for kv in results {
            let session: Session = serde_json::from_slice(&kv.value())?;
            sessions.push(session);
        }
        Ok(sessions)
    }

    // UPDATE
    pub async fn update(
        db: &Database,
        tenant_id: &Uuid,
        session_id: &Uuid,
        is_active: bool
    ) -> Result<Session, FdbError> {
        let trx = db.create_trx()?;
        let key = format!("/{}/auth_sessions/{}", tenant_id, session_id);

        // Get existing session
        let bytes = trx.get(&key.as_bytes(), false).await?
            .ok_or_else(|| FdbError::NotFound(format!("Session {} not found", session_id)))?;

        let mut session: Session = serde_json::from_slice(&bytes)?;
        session.is_active = is_active;

        // Update
        let value = serde_json::to_vec(&session)?;
        trx.set(&key.as_bytes(), &value);
        trx.commit().await.map_err(|e| {
            FdbError::TransactionError(format!("Failed to update session: {}", e))
        })?;

        Ok(session)
    }

    // DELETE
    pub async fn delete(
        db: &Database,
        tenant_id: &Uuid,
        session_id: &Uuid
    ) -> Result<(), FdbError> {
        let trx = db.create_trx()?;
        let key = format!("/{}/auth_sessions/{}", tenant_id, session_id);
        trx.clear(&key.as_bytes());
        trx.commit().await.map_err(|e| {
            FdbError::TransactionError(format!("Failed to delete session: {}", e))
        })?;
        Ok(())
    }
}

Integration with T2 Orchestrator

Orchestrator Phase 3: Backend Implementation

When the orchestrator coordinates backend feature development, it uses this skill for FDB data layer:

Orchestrator Phase 3: Backend Implementation
├─ Use rust-backend-patterns for endpoint structure
├─ Use foundationdb-queries for data persistence ← THIS SKILL
├─ Use production-patterns for error handling
└─ Validate with TDD validator (repository tests)

Example Delegation:

"Use foundationdb-queries skill to implement user profile repository with CRUD operations."

Token Efficiency: Query patterns save 60% debugging time (30→12 min) by providing:

• Proven tenant isolation patterns
• Repository template code ready to adapt
• Common error handling patterns
• Range query examples

Troubleshooting

Issue 1: TransactionCommitError

Symptom:

Error: TransactionCommitError: "transaction too old"

Cause: Transaction held open too long (>5 seconds typical limit)

Fix: Break into smaller transactions or use snapshot reads

// WRONG: Single large transaction
let trx = db.create_trx()?;
// ... many operations taking >5s ...
trx.commit().await?;

// CORRECT: Smaller transactions
let sessions = SessionRepository::list_by_tenant(&db, &tenant_id).await?;  // Read
for session in sessions {
    SessionRepository::update(&db, &tenant_id, &session.session_id, false).await?;  // Separate write
}

Issue 2: Missing Tenant Isolation

Symptom: User from tenant A can access tenant B's data

Cause: Key doesn't include tenant_id prefix

Fix: Always use repository methods with tenant_id parameter

// WRONG: No tenant isolation
let key = format!("/users/{}", user_id);

// CORRECT: Tenant-isolated key
let key = format!("/{}/users/{}", tenant_id, user_id);

Issue 3: Range Query Returns No Results

Symptom: list_by_tenant() returns empty vector when data exists

Cause: Range selector doesn't match key format

Fix: Verify prefix format matches key pattern exactly

// Key format
let key = format!("/{}/sessions/{}", tenant_id, session_id);

// Range prefix MUST match (with trailing slash)
let prefix = format!("/{}/sessions/", tenant_id);  // ✅ Correct
let prefix = format!("/{}/sessions", tenant_id);   // ❌ Wrong (no trailing slash)

Issue 4: Serialization Error

Symptom:

Error: serde_json::Error: missing field `created_at`

Cause: FDB stored old schema, code expects new schema

Fix: Implement migration or use #[serde(default)]

#[derive(Serialize, Deserialize)]
pub struct Session {
    pub session_id: Uuid,
    pub tenant_id: Uuid,
    #[serde(default = "default_created_at")]  // ✅ Handles missing field
    pub created_at: DateTime<Utc>,
}

fn default_created_at() -> DateTime<Utc> {
    Utc::now()
}

---

Multi-Context Window Support

This skill supports long-running FoundationDB operations across multiple context windows using Claude 4.5's enhanced state management capabilities.

State Tracking

Checkpoint State (JSON):

{
  "checkpoint_id": "fdb_20251129_151500",
  "operation_type": "multi_tenant_migration",
  "tenants_processed": [
    {"tenant_id": "tenant-001", "keys_migrated": 1250, "status": "complete"},
    {"tenant_id": "tenant-002", "keys_migrated": 890, "status": "complete"},
    {"tenant_id": "tenant-003", "keys_migrated": 320, "status": "in_progress"}
  ],
  "total_keys_migrated": 2460,
  "total_transactions": 45,
  "validation_passed": true,
  "repository_changes": [
    "SessionRepository::create",
    "UserRepository::update"
  ],
  "token_usage": 12000,
  "created_at": "2025-11-29T15:15:00Z"
}

Progress Notes (Markdown):

# FoundationDB Operations Progress - 2025-11-29

## Completed Operations
- Tenant-001: Migrated 1250 keys (sessions, users, workflows)
- Tenant-002: Migrated 890 keys (sessions, users)
- Repository pattern validation: All CRUD operations tested

## In Progress
- Tenant-003: 320/450 keys migrated (71%)
  - Remaining: workflows, audit logs

## Validation Status
- ✅ Tenant isolation verified (no cross-tenant leaks)
- ✅ Transaction commit success rate: 98.5%
- ✅ Range queries performance: <50ms average
- ⏳ Final audit log migration

## Next Actions
- Complete Tenant-003 workflow migration (130 keys)
- Migrate audit logs for all tenants
- Run comprehensive validation suite
- Update repository documentation

Session Recovery

When starting a fresh context window after FDB operations:

1. Load Checkpoint State: Read .coditect/checkpoints/fdb-latest.json
2. Review Progress Notes: Check fdb-operations-progress.md for tenant status
3. Verify FDB State: Query FDB to confirm key counts per tenant
4. Check Transaction Log: Review successful vs failed transactions
5. Resume Operations: Continue from last completed tenant/key batch

Recovery Commands:

# 1. Check latest FDB checkpoint
cat .coditect/checkpoints/fdb-latest.json | jq '.tenants_processed'

# 2. Review progress notes
tail -30 fdb-operations-progress.md

# 3. Verify FDB key counts (via fdbcli)
fdbcli --exec "get /<tenant-id>/"

# 4. Check repository tests
cargo test --package backend --lib db::repositories

# 5. Validate tenant isolation
cargo test test_tenant_isolation

State Management Best Practices

Checkpoint Files (JSON Schema):

• Store in .coditect/checkpoints/fdb-{timestamp}.json
• Track tenants processed vs remaining with key counts
• Record transaction success/failure rates for rollback planning
• Include validation results per tenant for audit trail
• Document repository pattern changes with file:line references

Progress Tracking (Markdown Narrative):

• Maintain fdb-operations-progress.md with tenant-level status
• Document migration decisions (why certain key patterns chosen)
• Note transaction performance metrics for optimization
• List tenant isolation validation results
• Track repository CRUD implementation progress

Git Integration:

• Create checkpoint after each tenant migration batch
• Commit repository changes with descriptive FDB operation tags
• Use conventional commits: feat(fdb): Add multi-tenant session migration
• Tag critical migrations: git tag fdb-migration-tenant-batch-1

Progress Checkpoints

Natural Breaking Points:

1. After completing each tenant's key migration
2. After implementing new repository CRUD methods
3. After validation suite runs successfully
4. After range query optimization
5. After schema migration completes

Checkpoint Creation Pattern:

# Automatic checkpoint creation at critical phases
if tenants_processed > 0 or keys_migrated > 1000:
    create_checkpoint({
        "tenants": tenant_status_list,
        "migrations": {
            "keys_total": total_keys_migrated,
            "transactions": transaction_count
        },
        "repositories": repository_changes,
        "tokens": current_token_usage
    })

Example: Multi-Context FDB Migration

Context Window 1: First 2 Tenants + Repository Setup

{
  "checkpoint_id": "fdb_batch1_complete",
  "phase": "initial_migration",
  "tenants_processed": 2,
  "keys_migrated": 2140,
  "repositories_implemented": ["SessionRepository", "UserRepository"],
  "validation_passed": true,
  "next_action": "Migrate remaining 3 tenants",
  "token_usage": 10000
}

Context Window 2: Remaining Tenants + Validation

# Resume from checkpoint
cat .coditect/checkpoints/fdb_batch1_complete.json

# Continue tenant migrations
# (Context restored in 2 minutes vs 15 minutes from scratch)

# Complete all tenants and validation
{
  "checkpoint_id": "fdb_migration_complete",
  "phase": "migration_complete",
  "tenants_processed": 5,
  "total_keys_migrated": 4500,
  "all_validations_passed": true,
  "performance_optimized": true,
  "token_usage": 8000
}

Token Savings: 10000 (first context) + 8000 (second context) = 18000 total vs. 30000 without checkpoint = 40% reduction

See docs/CLAUDE-4.5-BEST-PRACTICES.md for complete multi-context patterns.

---

Success Output

When successful, this skill MUST output:

✅ SKILL COMPLETE: foundationdb-queries

Completed:
- [x] Repository pattern implemented (UserRepository|SessionRepository|etc.)
- [x] Tenant isolation verified (all keys prefixed with /{tenant_id}/)
- [x] CRUD operations tested (Create, Read, Update, Delete)
- [x] Range queries implemented (list_by_tenant with prefix scan)
- [x] Transaction error handling (TransactionCommitError mapped)
- [x] Multi-tenant isolation validated (no cross-tenant data leaks)

Outputs:
- backend/src/db/repositories.rs (repository implementations)
- backend/src/db/models.rs (data models with tenant_id)
- tests/test_repositories.rs (CRUD tests)
- tests/test_tenant_isolation.rs (isolation validation)

Repository Methods:
- create(db, tenant_id, data) -> Result<Model, FdbError>
- get(db, tenant_id, id) -> Result<Option<Model>, FdbError>
- list_by_tenant(db, tenant_id) -> Result<Vec<Model>, FdbError>
- update(db, tenant_id, id, data) -> Result<Model, FdbError>
- delete(db, tenant_id, id) -> Result<(), FdbError>

Completion Checklist

Before marking this skill as complete, verify:

• All repository methods use tenant_id parameter (no global keys)
• Key format follows pattern: /{tenant_id}/{resource_type}/{resource_id}
• Range queries use prefix scan with tenant isolation
• TransactionCommitError handled with map_err()
• Serde serialization/deserialization working for all models
• Repository tests pass (CRUD operations)
• Tenant isolation tests pass (no cross-tenant access)
• No direct FDB calls (all via repository pattern)
• Transaction pattern: create → get → set → commit
• Error messages include tenant_id and resource_id for debugging

Failure Indicators

This skill has FAILED if:

• ❌ Keys created without tenant_id prefix (security vulnerability)
• ❌ TransactionCommitError "transaction too old" (transaction held >5s)
• ❌ Range query returns empty when data exists (prefix mismatch)
• ❌ Serde deserialization error "missing field" (schema mismatch)
• ❌ Cross-tenant data leak detected (tenant A can read tenant B data)
• ❌ Direct FDB calls bypassing repository pattern
• ❌ Repository method doesn't validate tenant_id parameter
• ❌ Transaction error not mapped to FdbError enum

When NOT to Use

Do NOT use this skill when:

• Frontend-only features (no backend database needed)
• Using OPFS browser cache (different storage pattern)
• Simple file reads without database queries
• Cloud infrastructure changes (use GKE deployment skills)
• PostgreSQL/MySQL databases (use SQL query patterns)
• Redis cache operations (use cache-specific patterns)
• Read-only operations on existing FDB data (use simpler read patterns)

Use alternative skills:

• sql-query-patterns - For PostgreSQL/MySQL databases
• redis-cache-patterns - For Redis caching
• opfs-storage-patterns - For browser-side storage
• read-only-fdb-patterns - For simple FDB reads

Anti-Patterns (Avoid)

Anti-Pattern	Problem	Solution
No tenant_id in keys	Security vulnerability (cross-tenant access)	Always prefix keys with /{tenant_id}/
Direct FDB calls	Bypasses validation, error-prone	Always use repository pattern
Long-running transactions	"transaction too old" errors	Keep transactions <5s, use snapshot reads
Missing trailing slash in prefix	Range query returns no results	Use /{tenant_id}/resource_type/ (with slash)
Hardcoded keys	Brittle, difficult to change	Use format!() with constants
No serde(default) for new fields	Deserialization fails on old data	Add #[serde(default)] for backward compatibility
Single large transaction	Performance issues, timeout risk	Break into smaller transactions
No error mapping	Generic errors, hard to debug	Map FdbError variants with context

Principles

This skill embodies:

• #5 Eliminate Ambiguity - Clear key patterns, explicit tenant isolation
• #8 No Assumptions - Validate tenant_id, verify key format
• #11 Reliability - Repository pattern ensures consistency
• #12 Observability - Detailed error messages with context
• Security - Multi-tenant isolation prevents data leaks
• Performance - Transaction optimization (<5s), range query efficiency

Full Standard: CODITECT-STANDARD-AUTOMATION.md

---

Version: 1.1.0 | Updated: 2026-01-04 | Author: CODITECT Team