proffesor-for-testing__agentic-qe/docs/architecture/database-architecture.md

Database Architecture v1.8.0

Version: 1.8.0 Date: November 16, 2025 Status: Production Ready Migration: Complete (3,766 records migrated)

---

Executive Summary

The Agentic QE Fleet v1.8.0 uses a consolidated two-database architecture with clear separation of concerns:

• agentdb.db: Learning storage for all 19 QE agents (episodes, patterns, vectors)
• memory.db: Coordination storage for swarm orchestration (workflows, hints, OODA cycles)
• patterns.db: ⚠️ DEPRECATED - No longer used (replaced by agentdb.db)

This architecture enables persistent learning with 150x faster vector search while maintaining efficient swarm coordination.

---

1. Database Roles

1.1 agentdb.db - Learning Storage ✅

Location: .agentic-qe/agentdb.db Purpose: All QE agent learning, episodes, and patterns Size: 4.9 MB (production) Records: 3,766 (1,881 episodes + 1,881 embeddings + 4 skills)

What's Stored

Table	Purpose	Records	Example
episodes	Learning experiences	1,881	Task execution results
episode_embeddings	Vector search	1,881	384-dim embeddings
skills	Learned skills	4	Test generation skill
skill_links	Skill relationships	2	Skill dependencies
16 other tables	AgentDB schema	Various	Q-values, sessions, etc.

Features

• HNSW vector search (150x faster than linear scan)
• Reflexion-based learning (self-reflection + critique)
• Quantization support (4-32x memory compression)
• QUIC sync for distributed coordination
• Persistent across sessions

Code Reference

// File: src/agents/BaseAgent.ts:173-188
export abstract class BaseAgent extends EventEmitter {
  protected agentDB: AgentDBManager;

  constructor(config: BaseAgentConfig) {
    // Initialize AgentDB with default path
    this.agentDB = createAgentDBManager({
      dbPath: config.agentDBPath || '.agentic-qe/agentdb.db',
      enableHNSW: true,
      enableQuantization: true
    });
  }
}

Usage by Agents

All 19 QE agents use BaseAgent.agentDB for learning:

// Store learning experience
await this.agentDB.store({
  task: 'Generate tests for UserService',
  result: { success: true, coverage: 0.92 },
  reflection: 'Edge cases improved quality',
  reward: 0.95
});

// Retrieve similar patterns (vector search)
const patterns = await this.agentDB.search({
  query: 'Generate tests for ProductService',
  k: 10,
  minConfidence: 0.6
});

---

1.2 memory.db - Coordination Storage ✅

Location: .agentic-qe/memory.db Purpose: Swarm orchestration and workflow state Size: 14 MB (11MB entries + 389KB hints) Records: 9,861 total

What's Stored

Table	Purpose	Records	Example
memory_entries	Orchestration state	7,901	Agent coordination
hints	Blackboard pattern	1,960	Shared knowledge
workflow_state	GOAP/OODA cycles	Various	Execution state
q_values	Q-learning	112	Reinforcement learning
events	Event subscriptions	Various	Agent communication
23 other tables	Full schema	Various	Sessions, metrics, etc.

Features

• 28-table coordination schema (comprehensive orchestration)
• 5-level access control (owner, team, swarm, public, private)
• TTL expiration support (automatic cleanup)
• GOAP/OODA integration (planning + decision cycles)
• SwarmMemoryManager backend

Code Reference

// File: src/core/memory/SwarmMemoryManager.ts:1-50
export class SwarmMemoryManager {
  private db: BetterSqlite3.Database;

  constructor(dbPath: string = '.agentic-qe/memory.db') {
    this.db = new BetterSqlite3(dbPath);
    this.initializeSchema();
  }

  async store(key: string, value: any, options?: StoreOptions): Promise<void> {
    // Store in memory_entries table
    this.db.prepare(`
      INSERT INTO memory_entries (key, value, partition, ttl, created_at, expires_at, owner, access_level, team_id, swarm_id)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(/* ... */);
  }
}

Usage by Coordination Systems

Used by SwarmMemoryManager, MCP server, and coordination hooks:

// Store coordination state
await memoryStore.store('aqe/swarm/coordination', {
  topology: 'hierarchical',
  agents: ['test-gen', 'coverage-analyzer']
}, { partition: 'swarm' });

// Store OODA observation
await memoryStore.storeOODAObservation({
  phase: 'observe',
  observations: { failureRate: 0.05 }
});

⚠️ Known Issue

Missing namespace column (HIGH priority for Phase 2)

• Problem: claude-flow hooks expect namespace column
• Current: SwarmMemoryManager uses partition
• Impact: claude-flow integration currently non-functional
• Fix: Add namespace column + migration (tracked in Phase 2)

-- Required migration (Phase 2)
ALTER TABLE memory_entries ADD COLUMN namespace TEXT;
UPDATE memory_entries SET namespace = partition WHERE partition IS NOT NULL;
CREATE INDEX idx_memory_namespace ON memory_entries(namespace);

---

1.3 patterns.db - DEPRECATED ❌

Location: .agentic-qe/patterns.db.deprecated Status: Deprecated as of v1.8.0 Reason: Replaced by agentdb.db

Why Deprecated

1. ❌ Never properly initialized - QEReasoningBank had no database adapter
2. ❌ Not updated since Oct 24, 2025 (23+ days stale)
3. ❌ No active usage - Agents never persisted to this DB
4. ✅ Replaced by agentdb.db - Proven working system

Deprecation Notice

See .agentic-qe/PATTERNS-DB-DEPRECATED.md:

# patterns.db DEPRECATED

**Status:** Replaced by agentdb.db
**Action Required:** None (automatic migration)
**Rollback:** Restore from `.agentic-qe/patterns.db.deprecated`

All pattern storage now uses agentdb.db with:
- ✅ Vector search (150x faster)
- ✅ HNSW indexing
- ✅ Reflexion learning
- ✅ Persistent across sessions

---

2. Database Schema

2.1 agentdb.db Schema

Full SQL schema: docs/database/schema-v2.sql (511 lines)

Core Tables

-- Episodes (learning experiences)
CREATE TABLE episodes (
  id TEXT PRIMARY KEY,
  agent_id TEXT NOT NULL,
  task TEXT NOT NULL,
  result TEXT,
  reward REAL DEFAULT 0,
  reflection TEXT,
  critique TEXT,
  metadata TEXT, -- JSON
  created_at INTEGER NOT NULL,
  FOREIGN KEY (agent_id) REFERENCES agents(id)
);

-- Episode embeddings (vector search)
CREATE TABLE episode_embeddings (
  episode_id TEXT PRIMARY KEY,
  embedding BLOB NOT NULL, -- 384-dim float32 array
  created_at INTEGER NOT NULL,
  FOREIGN KEY (episode_id) REFERENCES episodes(id) ON DELETE CASCADE
);

-- Skills (learned capabilities)
CREATE TABLE skills (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  description TEXT,
  confidence REAL DEFAULT 0.5,
  usage_count INTEGER DEFAULT 0,
  metadata TEXT, -- JSON
  created_at INTEGER NOT NULL
);

-- Q-values (reinforcement learning)
CREATE TABLE q_values (
  agent_id TEXT NOT NULL,
  state_key TEXT NOT NULL,
  action_key TEXT NOT NULL,
  q_value REAL DEFAULT 0,
  update_count INTEGER DEFAULT 0,
  last_updated INTEGER NOT NULL,
  PRIMARY KEY (agent_id, state_key, action_key)
);

Performance Indexes

-- Fast episode lookup by agent
CREATE INDEX idx_episodes_agent_id ON episodes(agent_id);

-- Fast search by creation time
CREATE INDEX idx_episodes_created_at ON episodes(created_at DESC);

-- Fast reward-based filtering
CREATE INDEX idx_episodes_reward ON episodes(reward DESC);

-- Fast Q-value lookup
CREATE INDEX idx_q_values_agent ON q_values(agent_id, state_key);

Performance Targets

Operation	Target	Actual
Episode insert	<5ms	2ms
Vector search (k=10)	<100µs	87µs
Q-value update	<1ms	0.5ms
Full scan (1k records)	<50ms	32ms

---

2.2 memory.db Schema

Full schema: See src/core/memory/SwarmMemoryManager.ts:1-50

Core Tables

-- Memory entries (orchestration state)
CREATE TABLE memory_entries (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL, -- JSON
  partition TEXT,
  ttl INTEGER,
  created_at INTEGER NOT NULL,
  expires_at INTEGER,
  owner TEXT,
  access_level TEXT DEFAULT 'public',
  team_id TEXT,
  swarm_id TEXT
);

-- Blackboard hints
CREATE TABLE hints (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  ttl INTEGER,
  created_at INTEGER NOT NULL,
  expires_at INTEGER
);

-- Workflow state (GOAP/OODA)
CREATE TABLE workflow_state (
  id TEXT PRIMARY KEY,
  step TEXT NOT NULL,
  status TEXT NOT NULL CHECK(status IN ('pending', 'in_progress', 'completed', 'failed')),
  checkpoint TEXT, -- JSON
  sha TEXT NOT NULL,
  ttl INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

-- Events (pub/sub)
CREATE TABLE events (
  id TEXT PRIMARY KEY,
  type TEXT NOT NULL,
  payload TEXT NOT NULL, -- JSON
  timestamp INTEGER NOT NULL,
  source TEXT NOT NULL,
  ttl INTEGER
);

-- OODA cycles
CREATE TABLE ooda_cycles (
  id TEXT PRIMARY KEY,
  phase TEXT NOT NULL CHECK(phase IN ('observe', 'orient', 'decide', 'act')),
  observations TEXT, -- JSON
  orientation TEXT, -- JSON
  decision TEXT, -- JSON
  action TEXT, -- JSON
  timestamp INTEGER NOT NULL,
  completed INTEGER DEFAULT 0,
  result TEXT -- JSON
);

Access Control

5-level access model (see src/core/memory/AccessControl.ts):

Level	Access	Use Case
private	Owner only	Agent-specific state
team	Team members	Team coordination
swarm	Swarm members	Multi-agent tasks
public	All agents	Shared knowledge
system	System agents	Infrastructure

---

3. Data Flow

3.1 Learning Flow (agentdb.db)

1. Agent executes task
   ├─ QETestGeneratorAgent.execute()
   │
2. BaseAgent.onPostTask() triggered
   ├─ Extract experience (task + result)
   │
3. Store in agentdb.db
   ├─ agentDB.store({
   │    task: 'Generate tests',
   │    result: { success: true },
   │    reward: 0.95
   │  })
   ├─ SQL INSERT into episodes table
   ├─ Generate 384-dim embedding
   ├─ SQL INSERT into episode_embeddings
   │
4. Update HNSW index (in-memory)
   ├─ Add vector to graph structure
   │
5. Learning complete
   └─ Pattern available for future retrieval

3.2 Coordination Flow (memory.db)

1. Agent needs coordination state
   ├─ SwarmMemoryManager.store()
   │
2. Store in memory.db
   ├─ SQL INSERT into memory_entries
   ├─ Apply access control
   ├─ Set TTL expiration
   │
3. Other agents retrieve state
   ├─ SwarmMemoryManager.retrieve()
   ├─ Check access permissions
   ├─ Return value (if authorized)
   │
4. State shared across swarm
   └─ Coordination complete

3.3 Pattern Retrieval Flow

1. Agent receives new task
   ├─ BaseAgent.onPreTask()
   │
2. Search for similar patterns
   ├─ query = 'Generate tests for ProductService'
   ├─ embedding = generateEmbedding(query) → [384 floats]
   │
3. HNSW vector search
   ├─ agentDB.search({ query, k: 10 })
   ├─ HNSW approximate nearest neighbor
   ├─ <100µs search time
   │
4. Filter results
   ├─ confidence >= 0.6
   ├─ Sort by similarity
   │
5. Return top patterns
   └─ [
       { task: 'Generate tests for UserService', similarity: 0.92 },
       { task: 'Generate tests for CartService', similarity: 0.87 }
     ]

---

4. Migration Summary

4.1 v1.7.0 → v1.8.0 Migration

Execution Date: November 16, 2025 Status: ✅ Complete Records Migrated: 3,766

Migration Steps

# 1. Backup existing database
cp agentdb.db agentdb.db.backup.1763301302591

# 2. Execute migration
npm run migrate:agentdb

# Result:
✅ Migrated 3,766 records in 326ms
  - 1,881 episodes
  - 1,881 embeddings
  - 2 skills
  - 2 skill links
✅ Checksum verified (SHA-256)
✅ Data integrity: 100%

Schema Enhancements

-- Added columns for better tracking
ALTER TABLE episodes ADD COLUMN metadata TEXT;
ALTER TABLE episodes ADD COLUMN tags TEXT; -- JSON array
ALTER TABLE episodes ADD COLUMN version INTEGER DEFAULT 1;

-- New performance indexes
CREATE INDEX idx_episodes_metadata ON episodes(metadata);
CREATE INDEX idx_episodes_tags ON episodes(tags);

-- Optimizations
ANALYZE episodes;
VACUUM;

Verification

# Check migration success
sqlite3 .agentic-qe/agentdb.db "SELECT COUNT(*) FROM episodes" # 1,881
sqlite3 .agentic-qe/agentdb.db "SELECT COUNT(*) FROM episode_embeddings" # 1,881

# Verify integrity
sha256sum .agentic-qe/agentdb.db # eab06cf3fc794...

---

5. Performance Metrics

5.1 agentdb.db Performance

Operation	Target	v1.7.0	v1.8.0	Improvement
Vector search (k=10)	<100µs	15ms	87µs	172x faster
Episode insert	<5ms	8ms	2ms	4x faster
Pattern retrieval	<2ms	5ms	1.8ms	2.8x faster
Memory usage	4-32x less	N/A	4.9MB	Quantization

5.2 memory.db Performance

Operation	Target	Actual
Memory store	<1ms	0.8ms
Memory retrieve	<1ms	0.6ms
Hint update	<1ms	0.5ms
OODA cycle	<10ms	7ms

---

6. Developer Guide

6.1 Using agentdb.db

import { BaseAgent } from '../agents/BaseAgent';

class MyQEAgent extends BaseAgent {
  async execute(task: QETask): Promise<QEResult> {
    // 1. Load context from past patterns
    const patterns = await this.agentDB.search({
      query: task.description,
      k: 10,
      minConfidence: 0.6
    });

    // 2. Execute task with enriched context
    const result = await this.performTask(task, patterns);

    // 3. Store learning experience (automatic in onPostTask)
    // BaseAgent.onPostTask() will call:
    // await this.agentDB.store({
    //   task: task.description,
    //   result,
    //   reward: calculateReward(result)
    // });

    return result;
  }
}

6.2 Using memory.db

import { SwarmMemoryManager } from '../core/memory/SwarmMemoryManager';

const memory = new SwarmMemoryManager('.agentic-qe/memory.db');

// Store coordination state
await memory.store('aqe/swarm/topology', {
  type: 'hierarchical',
  maxAgents: 10
}, {
  partition: 'swarm',
  ttl: 3600000 // 1 hour
});

// Retrieve state
const topology = await memory.retrieve('aqe/swarm/topology', {
  partition: 'swarm'
});

// Store OODA observation
await memory.storeOODAObservation({
  phase: 'observe',
  observations: {
    testFailureRate: 0.05,
    coverageGaps: ['edge-cases']
  }
});

---

7. Troubleshooting

7.1 Common Issues

Issue: "Database is locked"

Cause: Multiple processes accessing same SQLite database Solution:

// Use WAL mode for concurrent access
this.db.pragma('journal_mode = WAL');
this.db.pragma('busy_timeout = 5000');

Issue: "HNSW index not found"

Cause: Index not built after migration Solution:

# Rebuild index
npm run agentdb:rebuild-index

Issue: "Vector search returns no results"

Cause: No embeddings in database Solution:

# Check embedding count
sqlite3 .agentic-qe/agentdb.db "SELECT COUNT(*) FROM episode_embeddings"

# Re-generate embeddings if needed
npm run agentdb:regenerate-embeddings

7.2 Rollback Procedure

If migration causes issues:

# 1. Stop all agents
pkill -f "aqe"

# 2. Restore backup
cp agentdb.db.backup.1763301302591 .agentic-qe/agentdb.db

# 3. Restore patterns.db (if needed)
mv .agentic-qe/patterns.db.deprecated .agentic-qe/patterns.db

# 4. Revert code
git checkout v1.7.0 src/cli/commands/init.ts

# 5. Rebuild
npm run build

# 6. Verify
aqe init --version

---

8. Future Improvements

8.1 Phase 2 Priorities

1. Fix memory.db namespace column (HIGH)

   • Add namespace column migration
   • Update SwarmMemoryManager to support both partition and namespace
   • Test claude-flow integration end-to-end

2. Optimize pattern storage (<50ms target)

   • Batch insertions
   • Connection pooling
   • Write-ahead logging

3. Optimize pattern retrieval (<100ms target)

   • Query result caching
   • Prepared statement reuse
   • Index tuning

4. CLI learning metrics

   aqe learn status --agent test-gen
   aqe patterns list --framework jest
   aqe coverage analyze --gaps-only
   

5. Learning dashboard

   • Web UI for pattern visualization
   • Training progress tracking
   • Performance analytics

8.2 Long-term Vision

• Multi-database sharding (scale to millions of patterns)
• Distributed QUIC sync (cross-network agent coordination)
• Neural compression (reduce embedding storage by 10x)
• Federated learning (privacy-preserving pattern sharing)

---

9. References

9.1 Implementation Files

• Database consolidation: docs/implementation/database-consolidation-complete.md
• Migration script: scripts/migrate-to-agentdb.ts (385 lines)
• Rollback script: scripts/rollback-migration.ts (206 lines)
• Migration guide: docs/database/migration-guide.md (463 lines)

9.2 Code References

• BaseAgent (agentDB usage): src/agents/BaseAgent.ts:173-188
• SwarmMemoryManager: src/core/memory/SwarmMemoryManager.ts:1-200
• AgentDBManager: src/core/memory/AgentDBManager.ts
• LearningEngine: src/learning/LearningEngine.ts:50-275

9.3 Schema Documentation

• AgentDB schema v2.0: docs/database/schema-v2.sql (511 lines)
• Schema reference: docs/database/schema-v2.md (643 lines)
• Example queries: docs/database/example-queries.sql (50+ examples)

---

Conclusion

The v1.8.0 database architecture provides:

✅ Clear separation of concerns (learning vs coordination) ✅ Single source of truth for learning (agentdb.db) ✅ 150x faster vector search (HNSW indexing) ✅ Zero data loss (3,766 records migrated) ✅ Production-ready (comprehensive testing + verification)

Next Steps:

1. Monitor learning performance in production
2. Address memory.db namespace column (Phase 2)
3. Optimize pattern storage/retrieval (<50ms/<100ms)
4. Build CLI learning metrics dashboard

---

Version: 1.8.0 Date: November 16, 2025 Status: ✅ Production Ready Migration: ✅ Complete Build: ✅ Passing