oracle-memory-by-yhw

Category: Docs & Knowledge | Uploader: Haiwen-Yin | Downloads: 0 | Version: v1.0（Latest）
Oracle AI Database Memory System v1.0.0 (Production Release) with Knowledge Base, Property Graph, Multi-Agent Architecture, Task Plan management, enterprise-grade security, Performance Optimization, and comprehensive documentation
Changelog: Source: GitHub https://github.com/Haiwen-Yin/oracle-memory-by-yhw
Directory Structure

Current level: tree/main/
📁 references/
- 📄 knowledge-base-design.md 26.9 KB
- 📄 memory-fusion-algorithm.md 11.4 KB
- 📄 multi-agent-design.md 8.9 KB
- 📄 optimized-vector-query.md 4.1 KB
- 📄 oracle-plsql-vector-pitfalls.md 2.7 KB
- 📄 partition-strategy.md 8.9 KB
- 📄 performance-optimization-guide.md 1.4 KB
- 📄 script-deployment-troubleshooting.md 3.4 KB
📁 scripts/
- 📄 agent_api.py 22.7 KB
- 📄 agent_permission_downgrade.sql 11.7 KB
- 📄 agent_schema.sql 10.3 KB
- 📄 batch_operations.py 8.8 KB
- 📄 cleanup_orphaned_data.sql 7.9 KB
- 📄 enhanced_session_cleanup.sql 10.3 KB
- 📄 enhanced_snapshot_cleanup_job.sql 9.8 KB
- 📄 final_verification_test.py 11.5 KB
- 📄 generate_vector_insert_sql.py 4.2 KB
- 📄 knowledge_base_api.py 28.6 KB
- 📄 knowledge_base_api.sql 32.1 KB
- 📄 knowledge_base_api_optimized.py 18.1 KB
- 📄 knowledge_base_schema.sql 13.0 KB
- 📄 knowledge_base_schema_v2.sql 8.4 KB
- 📄 knowledge_jobs.sql 11.9 KB
- 📄 knowledge_property_graph.sql 8.6 KB
- 📄 memory_fusion_engine.sql 16.0 KB
- 📄 monitoring_diagnostics.sql 15.6 KB
- 📄 query_optimization.sql 12.5 KB
- 📄 session_cleanup_job.sql 6.7 KB
- 📄 simple_test.py 7.2 KB
- 📄 test_agent_architecture.py 17.2 KB
- 📄 test_compatibility.py 28.8 KB
- 📄 test_complete_memory_system.py 16.1 KB
- 📄 test_knowledge_base.py 9.9 KB
- 📄 test_partition_strategy.py 1.1 KB
- 📄 test_v0.3.1_complete_workflow.sh 2.6 KB
- 📄 test_v051_complete.py 24.9 KB
- 📄 vector_index_optimization.sql 10.4 KB
- 📄 verify_schema.sh 1.8 KB
📁 security/
- 📄 aggregation_analysis.sql 5.3 KB
- 📄 context_aware_masking.py 7.5 KB
- 📄 data_masking.py 8.7 KB
- 📄 desensitize_levels.py 1.4 KB
- 📄 desensitize_levels.sql 1.4 KB
- 📄 reversible_masking.py 8.8 KB
📄 API_Reference.md 30.4 KB
📄 CHANGELOG.md 15.3 KB
📄 Examples_Guide.md 23.7 KB
📄 LICENSE 10.1 KB
📄 NOTICE 308 B
📄 Performance_Optimization.md 31.9 KB
📄 property-graph-integration-test-report.md 5.9 KB
📄 property-graph-test-report.md 8.5 KB
📄 README.md 17.6 KB
📄 README_KNOWLEDGE_BASE.md 14.7 KB
📄 RELEASE_NOTES_v0.4.1.md 6.8 KB
📄 RELEASE_NOTES_v0.4.2.md 3.5 KB
📄 RELEASE_NOTES_v0.5.0.md 6.3 KB
📄 RELEASE_NOTES_v0.5.1.md 5.5 KB
📄 RELEASE_NOTES_v1.0.0.md 11.9 KB
📄 SKILL.md 39.4 KB
SKILL.md

---
name: oracle-memory-by-yhw
version: v1.0.0 (Production Release)
author: Haiwen Yin (胖头鱼 🐟)
description: Oracle AI Database Memory System v1.0.0 (Production Release) with Knowledge Base, Property Graph, Multi-Agent Architecture, Task Plan management, enterprise-grade security, Performance Optimization, and comprehensive documentation
tags: [oracle, memory-system, knowledge-base, vector-search, production]
related_skills: [oracle-26ai, oracle-sqlcl-execution-methodology, naming-convention-yhw-enforcement]
---

# Oracle AI Database Memory System v1.0.0 (Production Release)

**Author**: Haiwen Yin (胖头鱼 🐟)  
**Version**: v1.0.0 (Production Release) - 2026-05-09  
**Status**: Production Ready ✅  
**License**: Apache License 2.0

---

## 🚀 v1.0.0 - Production-Grade AI Agent Memory System

### 🎉 **Major Milestone: Ready for Real-World AI Agent Deployment!**

**v1.0.0 is a significant breakthrough** that transforms this system from a research prototype into an **enterprise-grade, production-ready memory system for AI Agents**. This is the version you can confidently deploy in **real production AI systems**.

### 🌟 **What Makes v1.0.0 Production-Ready?**

**✅ Battle-Tested Core Operations:**
- All CRUD operations verified and working with real database
- Comprehensive error handling and recovery
- Production-grade connection management
- Query caching for optimal performance

**✅ Complete Production Documentation:**
- Detailed API reference with examples
- Performance optimization guide
- Best practices for deployment
- Troubleshooting and monitoring guides

**✅ Enterprise Features:**
- Version control for knowledge evolution
- Confidence tracking for quality management
- Validation workflows for knowledge curation
- Multi-agent collaboration support

**✅ Real AI Agent Capabilities:**
- 🤖 **Multi-Agent Support** - Enable AI agents to share and collaborate
- 🧠 **Knowledge Graph** - Build interconnected knowledge networks
- 📊 **Confidence Tracking** - Track and manage knowledge quality
- 🔄 **Version Control** - Track knowledge evolution over time
- 🎯 **Experience Distillation** - Convert raw memories into stable knowledge

### 💡 **Why This Version is Different**

**Before v1.0.0:**
- Research prototype with limited testing
- Incomplete documentation
- Missing production features
- Not suitable for real-world deployment

**After v1.0.0:**
- ✅ **Battle-tested** with real database operations
- ✅ **Fully documented** with examples and best practices
- ✅ **Production-ready** with error handling and monitoring
- ✅ **Scalable** for enterprise AI agent deployments
- ✅ **Actually works** in production AI systems!

**This is the version that makes production AI agent memory systems a reality!**

---

## 🎯 System Overview

This is a **universal memory system for all AI Agents**, built on Oracle AI Database 26ai. Provides complete semantic search, knowledge graph relationship management, vector similarity retrieval, JRD (JSON Relational Duality) views, native Property Graph capabilities, and **Task Plan persistence** with breakpoint recovery using the `oracle-sqlcl` MCP Server as the primary interface.

### ✨ Core Features (v0.5.1)

| Feature | v0.3.0 | v0.3.1 | v0.4.0 | **v0.4.1** | **v0.4.2** | **v0.5.0** | **v0.5.1** |
| **Target Users** | All AI Agents | ✅ All AI Agents | ✅ All AI Agents | ✅ All AI Agents | ✅ All AI Agents | ✅ All AI Agents | ✅ All AI Agents |
| **Task Plan Storage** | ❌ Not included | ❌ Not included | ❌ Not included | ✅ **Complete Task Plan System** | ✅ **Complete Task Plan System** | ✅ **Complete Task Plan System** | ✅ **Complete Task Plan System** |
| **Breakpoint Recovery** | ❌ None | ❌ None | ❌ None | ✅ **Auto Snapshot + Resume API** | ✅ **Auto Snapshot + Resume API** | ✅ **Auto Snapshot + Resume API** | ✅ **Auto Snapshot + Resume API** |
| **Historical Learning** | ❌ Limited | ❌ Limited | ❌ Limited | ✅ **Task Pattern Recognition** | ✅ **Task Pattern Recognition** | ✅ **Task Pattern Recognition** | ✅ **Task Pattern Recognition** |
| **Status Tracking** | ❌ Basic | ⚠️ Partial | ⚠️ Partial | ✅ **Detailed Step-by-Step Audit** | ✅ **Detailed Step-by-Step Audit** | ✅ **Detailed Step-by-Step Audit** | ✅ **Detailed Step-by-Step Audit** |
| **Embedding Models** | Multi-model | ✅ Multi-model | ✅ Multi-model | ✅ Multi-model | ✅ Multi-model | ✅ **Multi-model (BGE-M3/OpenAI/Cohere)** | ✅ **Multi-model (BGE-M3/OpenAI/Cohere)** |
| **Production Deployment** | ADG HA | ✅ ADG HA | ✅ ADG HA | ✅ ADG HA | ✅ ADG HA | ✅ ADG HA with Partition Strategy | ✅ ADG HA with Partition Strategy |
| **Vector Import** | CLOB + TO_VECTOR() | ✅ CLOB + TO_VECTOR() | ✅ CLOB + TO_VECTOR() | ✅ Native VECTOR(1024) | ✅ Native VECTOR(1024) | ✅ Native VECTOR(1024) | ✅ Native VECTOR(1024) |
| **Property Graph** | ❌ Not tested | ✅ Integration verified | ✅ **CREATE PROPERTY GRAPH + SQL/PGQ** | ✅ **CREATE PROPERTY GRAPH + SQL/PGQ** | ✅ **CREATE PROPERTY GRAPH + SQL/PGQ** | ✅ **CREATE PROPERTY GRAPH + SQL/PGQ** | ✅ **CREATE PROPERTY GRAPH + SQL/PGQ** |
| **JRD Implementation** | ❌ Plan only | ⚠️ Plan documented | ✅ **Full implementation + nested views** | ✅ **Full implementation + nested views** | ✅ **Full implementation + nested views** | ✅ **Full implementation + nested views** | ✅ **Full implementation + nested views** |
| **JSON Decomposition** | ❌ CLOB storage | ⚠️ Design documented | ✅ **6 relationship tables** | ✅ **6 relationship tables** | ✅ **6 relationship tables** | ✅ **6 relationship tables** | ✅ **6 relationship tables** |
| **Graph Traversal Views** | ❌ | ❌ | ✅ **MEMORY_GRAPH_V + MEMORY_GRAPH_JSON_V** | ✅ **MEMORY_GRAPH_V + MEMORY_GRAPH_JSON_V** | ✅ **MEMORY_GRAPH_V + MEMORY_GRAPH_JSON_V** | ✅ **MEMORY_GRAPH_V + MEMORY_GRAPH_JSON_V** | ✅ **MEMORY_GRAPH_V + MEMORY_GRAPH_JSON_V** |
| **Auxiliary Indexes** | ❌ | ⚠️ Partial | ✅ **Complete index coverage** | ✅ **Complete index coverage** | ✅ **Complete index coverage** | ✅ **Complete index coverage** | ✅ **Complete index coverage** |
| **Partition Strategy** | ❌ | ✅ Tested & verified | ✅ **Multi-table unified strategy** | ✅ **Multi-table unified strategy** | ✅ **Multi-table unified strategy** | ✅ **Multi-table unified strategy** | ✅ **Multi-table unified strategy** |
| **Security Module** | ❌ | ❌ | ❌ | ❌ | ❌ | ✅ **Enterprise-grade data masking, encryption, audit trails** | ✅ **Enterprise-grade data masking, encryption, audit trails** |
| **Performance Optimization** | ❌ | ❌ | ❌ | ❌ | ❌ | ✅ **Vector storage migration, aggregation analysis, automated cleanup** | ✅ **Enhanced cleanup framework + Memory Fusion Engine** |
| **Performance Optimization** | ❌ | ❌ | ❌ | ❌ | ❌ | ✅ **Vector storage migration, aggregation analysis, automated cleanup** | ✅ **Enhanced cleanup framework + Memory Fusion Engine** |

---

## 🆕 v0.4.1 New: Task Plan Persistence System

### Overview

The Task Plan system provides AI Agents with durable task execution tracking, enabling **breakpoint recovery after failures**, **historical pattern learning from completed tasks**, and **detailed status auditing**. This is critical for long-running agent operations that may encounter network issues, timeouts, or other disruptions.

**Key Benefits:**
- ✅ **Breakpoint Recovery** - Resume exactly where interrupted with full context
- ✅ **Historical Learning** - Learn from past task patterns and success/failure modes  
- ✅ **Status Tracking** - Complete audit trail of all agent actions

---

## 🛡️ v0.5.0 Security & Performance Enterprise Edition

### ✨ What's New in v0.5.0

The v0.5.0 release focuses on **enterprise-grade security** and **performance optimization**:

| Feature | Description | Status |
|---------|-------------|--------|
| **Vector Storage Migration** | Oracle 26ai native VECTOR(1024) type replacing CLOB storage | ✅ Implemented |
| **Automated Cleanup Jobs** | Scheduled cleanup of snapshots, sessions, and audit logs | ✅ Implemented |
| **Enterprise Data Masking Service** | Automatic PII/sensitive data redaction before storage with 4-tier strategy | ✅ v0.5.0 Complete |
| **Reversible Encryption** | Fernet AES-128-CBC encryption for internal debugging scenarios | ✅ v0.5.0 Complete |
| **Context-Aware Masking Logic** | Automatic masking level selection based on usage scenario (LOGGING/DEBUGGING/ANALYTICS/SHARING) | ✅ v0.5.0 Complete |
| **Desensitization Levels Table** | DESENSITIZE_LEVELS table with dynamic configuration support | ✅ v0.5.0 Complete |
| **Aggregation Analysis Views** | Privacy-preserving analytics views replacing exact match queries | ✅ v0.5.0 Complete |
| **Memory Fusion Algorithm** | Semantic deduplication of similar memories across conversations | 📋 Planned |

### 🆕 Multi-Agent Architecture (Integrated)

Oracle Memory System provides **multi-agent deployment** with shared and private memory isolation. This enables multiple AI agents to collaborate while maintaining appropriate access boundaries.

#### Core Features
- **Agent Registry**: Centralized registration and discovery of all agents
- **Memory Visibility Control**: Three visibility levels (SHARED/PRIVATE/COLLABORATIVE)  
- **Session Management**: Track active sessions with working context preservation
- **Access Audit Trail**: Complete logging of all memory access operations
- **Collaboration Workflow**: Request/approve mechanism for agent-to-agent knowledge sharing

---

## 🆕 v0.5.1 Core Functionality Enhancement Edition

### ✨ What's New in v0.5.1

The v0.5.1 release focuses on **core operational functionality** to make the system production-ready:

| Feature | Description | Status |
|---------|-------------|--------|
| **Agent Permission Downgrade** | Automatic COLLABORATIVE data access recovery when agents are disabled | ✅ Implemented |
| **Enhanced Snapshot Cleanup** | Centralized config table with dual-tier cleanup (daily archival + weekly full cycle) | ✅ Enhanced |
| **Session Expiry Management** | Intelligent session classification with TTL, warning threshold, and grace period | ✅ Enhanced |
| **Memory Fusion Engine** | Semantic deduplication and merging using vector similarity detection | ✅ New Feature |

### 🏗️ Agent Permission Downgrade Details

When an agent is disabled in the system, this feature ensures that:
1. The `PENDING_RECOVERY` flag is set to track recovery operations
2. All COLLABORATIVE memories accessible to the disabled agent are identified
3. The agent ID is removed from JSON ACCESSIBLE_TO arrays
4. Changes are logged in `agent_permission_log` for audit purposes
5. An hourly scheduled job checks and processes any pending recoveries

**API Functions:**
- `disable_agent_and_recover(agent_id, reason)` - Disable agent with automatic data recovery
- `enable_agent(agent_id)` - Re-enable agent and restore access
- `scheduled_permission_check()` - Cron-triggered pending recovery check

### ⚡ Enhanced Cleanup Framework

The cleanup framework has been enhanced from basic SQL scripts to a production-ready system:

**Centralized Configuration:**
- `cleanup_config` table with configurable retention policies
- `session_config` table for TTL management (24h default, 18h warning threshold)

**Improved Scheduling:**
- Daily snapshot cleanup at 2 AM
- Weekly full cycle cleanup on Sundays at 3 AM  
- Session expiry monitoring every 30 minutes
- Monthly archive statistics reports

### 🧠 Memory Fusion Engine

The new Memory Fusion Engine provides semantic deduplication and content merging:

**Core Capabilities:**
- Vector similarity detection using Oracle's VECTOR_DISTANCE function
- Intelligent merge strategies (PREFER_NEWEST, PREFER_LONGER)
- Content enrichment by combining related memories
- Comprehensive fusion operation history tracking

**API Functions:**
- `find_similar_memories(memory_id, threshold)` - Find semantically similar memories
- `deduplicate_batch(batch_size)` - Process a batch for deduplication
- `merge_similar_memories(memory_ids, strategy)` - Merge similar memories
- `enrich_memory(target_id)` - Combine related content into target memory


---

## 🗄️ Database Schema Overview

### Task Plan Core Tables

```sql
-- ============================================
-- 1. TASK_PLANS - Core task plan table
-- ============================================
CREATE TABLE TASK_PLANS (
    PLAN_ID       NUMBER PRIMARY KEY,
    PLAN_NAME     VARCHAR2(200),                    -- Task name
    PLAN_TYPE     VARCHAR2(50) NOT NULL,            -- task/deployment/research/analysis
    STATUS        VARCHAR2(30) DEFAULT 'PENDING',   -- PENDING/RUNNING/SUCCESS/FAILED/CANCELLED/PAUSED
    DESCRIPTION   CLOB,                             -- Task description and intent
    GOAL          CLOB,                             -- Final goal (structured)
    
    -- Priority and time management
    PRIORITY      NUMBER DEFAULT 2 CHECK (PRIORITY BETWEEN 1 AND 5),
    CREATED_AT    TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
    STARTED_AT    TIMESTAMP WITH TIME ZONE,         -- Start execution time
    UPDATED_AT    TIMESTAMP WITH TIME ZONE,         -- Last update status
    COMPLETED_AT  TIMESTAMP WITH TIME ZONE,         -- Completion time
    EXPIRES_AT    TIMESTAMP WITH TIME ZONE,         -- Expiration time
    
    -- Metadata (JSON)
    METADATA      CLOB,                             -- JSON: session_id, agent_context etc.
    TAGS          CLOB                              -- JSON: tag array
);

-- ============================================
-- 2. TASK_STEPS - Task step execution table
-- ============================================
CREATE TABLE TASK_STEPS (
    STEP_ID       NUMBER PRIMARY KEY,
    PLAN_ID       NUMBER NOT NULL REFERENCES TASK_PLANS(PLAN_ID),
    STEP_ORDER    NUMBER NOT NULL,                  -- Step sequence (1,2,3...)
    STEP_NAME     VARCHAR2(200),                    -- Step name
    ACTION        CLOB,                             -- Action description to execute
    TOOLS_USED    CLOB,                             -- JSON: tools used list
    
    -- Execution status
    STATUS        VARCHAR2(30) DEFAULT 'PENDING',   -- PENDING/IN_PROGRESS/SUCCESS/FAILED/BLOCKED
    RESULT        CLOB,                             -- Step execution result
    ERROR_MSG     CLOB,                             -- Error message (if any)
    
    -- Timestamps
    CREATED_AT    TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
    STARTED_AT    TIMESTAMP WITH TIME ZONE,
    COMPLETED_AT  TIMESTAMP WITH TIME ZONE,
    
    UNIQUE (PLAN_ID, STEP_ORDER)
);

-- ============================================
-- 3. TASK_CONTEXT_SNAPSHOTS - Task context snapshot (critical for breakpoint recovery)
-- ============================================
CREATE TABLE TASK_CONTEXT_SNAPSHOTS (
    SNAPSHOT_ID   NUMBER PRIMARY KEY,
    PLAN_ID       NUMBER NOT NULL REFERENCES TASK_PLANS(PLAN_ID),
    
    -- Snapshot type
    SNAPSHOT_TYPE VARCHAR2(30) DEFAULT 'AUTO',      -- AUTO/MANUAL/ON_ERROR
    
    -- Context content (complete state)
    CONTEXT_DATA  CLOB,                             -- JSON: agent_state, conversation_history etc.
    MEMORY_IDS    CLOB,                             -- JSON: associated memory node ID list
    NEXT_ACTION   CLOB,                             -- Next action to execute description
    
    -- Snapshot information
    CREATED_AT    TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
    IS_LATEST     VARCHAR2(1) CHECK (IS_LATEST IN ('Y','N')) DEFAULT 'N',
    
    -- Trigger reason (Oracle TRIGGER is a reserved word, use TRIGGER_REASON instead)
    TRIGGER_REASON  CLOB                            -- JSON: trigger_reason
);

-- ============================================
-- 4. TASK_TOOL_CALLS - Tool call records (audit trail)
-- ============================================
CREATE TABLE TASK_TOOL_CALLS (
    CALL_ID       NUMBER PRIMARY KEY,
    PLAN_ID       NUMBER NOT NULL REFERENCES TASK_PLANS(PLAN_ID),
    STEP_ID       NUMBER REFERENCES TASK_STEPS(STEP_ID),
    
    -- Tool information
    TOOL_NAME     VARCHAR2(100) NOT NULL,           -- tool name (terminal/browser/memory etc.)
    ACTION        CLOB NOT NULL,                    -- Executed operation description
    
    -- Call result
    STATUS        VARCHAR2(30) DEFAULT 'SUCCESS',   -- SUCCESS/FAILED/TIMEOUT
    RESULT_SIZE   NUMBER,                           -- Return result size (bytes)
    
    -- Time information
    CREATED_AT    TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
    DURATION_MS   NUMBER                            -- Execution duration milliseconds
);

-- ============================================
-- 5. TASK_DEPENDENCIES - Task dependency graph
-- ============================================
CREATE TABLE TASK_DEPENDENCIES (
    DEPENDENCY_ID NUMBER PRIMARY KEY,
    SOURCE_PLAN_ID NUMBER NOT NULL REFERENCES TASK_PLANS(PLAN_ID),
    TARGET_PLAN_ID NUMBER NOT NULL REFERENCES TASK_PLANS(PLAN_ID),
    
    -- Dependency type
    DEPENDENCY_TYPE VARCHAR2(30) DEFAULT 'HARD',    -- HARD/SOFT/EXCLUSIVE/RECOMMENDED
    CONDITION     CLOB,                             -- JSON: trigger condition description
    
    CREATED_AT    TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP
);
```

---

## 🔧 API Functions (Python Integration)

### create_task_plan() - Create task plan and automatically save initial context snapshot

```python
def create_task_plan(plan_name, plan_type, description, steps):
    """
    Create a new task plan and automatically save initial context
    
    Args:
        plan_name (str): Task name
        plan_type (str): task/deployment/research/analysis
        description (str): Task description
        steps (list[dict]): Step list [{order, name, action}, ...]
    
    Returns:
        dict: Created plan information
    """
    # 1. Generate UUID
    plan_id = generate_uuid()
    
    # 2. Insert into TASK_PLANS
    sql_insert_plan = """
        INSERT INTO TASK_PLANS (PLAN_ID, PLAN_NAME, PLAN_TYPE, DESCRIPTION, STATUS)
        VALUES (:id, :name, :type, :desc, 'PENDING')
    """
    execute_sql(sql_insert_plan, plan_id, ...)
    
    # 3. Insert all steps
    for step in steps:
        sql_insert_step = """
            INSERT INTO TASK_STEPS (STEP_ID, PLAN_ID, STEP_ORDER, STEP_NAME, ACTION)
            VALUES (:step_id, :plan_id, :order, :name, :action)
        """
        execute_sql(sql_insert_step, ...)
    
    # 4. Create initial context snapshot (for breakpoint recovery)
    initial_context = {
        "agent_state": get_current_agent_state(),
        "conversation_history": get_recent_messages(limit=50),
        "memory_ids": [],
        "next_action": steps[0]["action"] if steps else None,
        "created_at": now()
    }
    
    save_context_snapshot(plan_id, initial_context)
    
    return {"plan_id": plan_id, ...}
```

### update_task_progress() - Update task status and automatically create context snapshot

```python
def update_task_progress(plan_id, step_id=None, status=None, result=None):
    """
    Update task execution status and automatically create context snapshot
    
    Args:
        plan_id (str): Plan ID
        step_id (str): Step ID (optional)
        status (str): New status
        result (str): Execution result
    """
    
    # 1. Update task or step status
    if step_id:
        update_sql = "UPDATE TASK_STEPS SET STATUS = :status, RESULT = :result, COMPLETED_AT = SYSTIMESTAMP WHERE STEP_ID = :step_id"
    else:
        update_sql = "UPDATE TASK_PLANS SET STATUS = :status, UPDATED_AT = SYSTIMESTAMP WHERE PLAN_ID = :plan_id"
    
    execute_sql(update_sql, ...)
    
    # 2. Automatically create context snapshot (critical!)
    context = {
        "agent_state": get_current_agent_state(),
        "conversation_history": get_recent_messages(limit=100),
        "memory_ids": get_related_memory_ids(plan_id),
        "next_action": determine_next_action(plan_id, step_id),
        "trigger_reason": f"progress_update_{status}",
        "is_latest": "Y" if is_largest_snapshot(plan_id) else "N"
    }
    
    save_context_snapshot(plan_id, context, snapshot_type="AUTO")
```

### resume_task() - Resume task execution from breakpoint (core feature)

```python
def resume_task(plan_id):
    """
    Resume task execution from breakpoint
    
    Args:
        plan_id (str): Plan ID
    
    Returns:
        dict: Restored context information
    """
    
    # 1. Get latest snapshot (LATEST = 'Y')
    snapshot_sql = """
        SELECT CONTEXT_DATA, NEXT_ACTION, SNAPSHOT_TYPE, CREATED_AT
        FROM TASK_CONTEXT_SNAPSHOTS
        WHERE PLAN_ID = :plan_id AND IS_LATEST = 'Y'
        ORDER BY CREATED_AT DESC FETCH FIRST 1 ROWS ONLY
    """
    
    snapshot = execute_query(snapshot_sql, plan_id)
    
    # 2. Restore Agent state
    context_data = json.loads(snapshot["CONTEXT_DATA"])
    
    # 3. Get incomplete steps
    incomplete_steps = get_incomplete_steps(plan_id)
    
    # 4. Return recovery information
    return {
        "context": context_data,
        "next_action": snapshot["NEXT_ACTION"],
        "incomplete_steps": incomplete_steps,
        "snapshot_time": snapshot["CREATED_AT"]
    }
```

### search_completed_tasks() - Search completed tasks for learning and pattern reuse

```python
def search_completed_tasks(query_params):
    """
    Search completed tasks for learning and pattern reuse
    
    Args:
        query_params (dict): {type, status, tags, keywords, date_range}
    
    Returns:
        list[dict]: Matching task list (includes execution statistics)
    """
    
    # Build query conditions
    conditions = ["STATUS IN ('SUCCESS', 'FAILED')"]
    if query_params.get("type"):
        conditions.append("PLAN_TYPE = :type")
    if query_params.get("tags"):
        conditions.append("CONTAINS(TAGS, :tag) > 0")
    
    # Main query - get task information + execution statistics
    query_sql = """
        SELECT 
            t.PLAN_ID, t.PLAN_NAME, t.PLAN_TYPE, t.STATUS,
            t.CREATED_AT, t.COMPLETED_AT,
            (SELECT COUNT(*) FROM TASK_STEPS s WHERE s.PLAN_ID = t.PLAN_ID) as total_steps,
            (SELECT COUNT(*) FROM TASK_STEPS s WHERE s.PLAN_ID = t.PLAN_ID AND s.STATUS = 'SUCCESS') as success_steps,
            (SELECT AVG(DURATION_MS) FROM TASK_TOOL_CALLS c WHERE c.PLAN_ID = t.PLAN_ID) as avg_tool_duration_ms,
            t.METADATA
        FROM TASK_PLANS t
        WHERE """ + " AND ".join(conditions)
    
    return execute_query(query_sql, ...)
```

---

## 📊 Data Flow Architecture (Task Plan System)

```
┌──────────────────────────────────────────────────────────────┐
│                   AI Agent Task Execution                    │
└──────────────────────────────────────────────────────────────┘

[Agent] ──Start Task──► [create_task_plan()]
                           │
                    ┌──────▼───────┐
                    │ TASK_PLANS   │ ← Task plan (status, goals)
                    └──────┬───────┘
                           │
                    ┌──────▼───────┐
                    │ TASK_STEPS   │ ← Execution steps and results
                    └──────┬───────┘
                           │
              [Executing...] ──► [update_task_progress()]
                              │
                       ┌──────▼──────────┐
                       │CONTEXT_SNAPSHOTS│ ← **Critical for breakpoint recovery**
                       └──────┬──────────┘
                              │
                    ┌─────────▼─────────┐
                    │  AGENT_STATE      │ ← Agent current state
                    │  CONVERSATION     │ ← Conversation history
                    │  NEXT_ACTION      │ ← Next action
                    │  MEMORY_IDS       │ ← Associated memory nodes
                    └───────────────────┘

[Exception/Interruption] ◄──► [resume_task()] ──► [Load latest snapshot to continue execution]

[Task Completed] ──► [search_completed_tasks()] ──► [Pattern learning and reuse]
```

---

## 📊 Indexing Strategy (All Tables)

### Task Plan Specific Indexes

```sql
-- TASK_PLANS indexes
CREATE INDEX IDX_TASK_PLANS_STATUS ON TASK_PLANS(STATUS);
CREATE INDEX IDX_TASK_PLANS_TYPE ON TASK_PLANS(PLAN_TYPE);
CREATE INDEX IDX_TASK_PLANS_CREATED ON TASK_PLANS(CREATED_AT DESC);
CREATE INDEX IDX_TASK_PLANS_PRIORITY ON TASK_PLANS(PRIORITY, CREATED_AT);

-- TASK_STEPS indexes
CREATE INDEX IDX_TASK_STEPS_PLAN ON TASK_STEPS(PLAN_ID, STEP_ORDER);
CREATE INDEX IDX_TASK_STEPS_STATUS ON TASK_STEPS(STATUS);

-- TASK_CONTEXT_SNAPSHOTS index (Oracle does not support WHERE on index, use regular index instead)
CREATE INDEX IDX_CONTEXT_SNAPSHOT_PLAN ON TASK_CONTEXT_SNAPSHOTS(PLAN_ID);

-- TASK_TOOL_CALLS indexes
CREATE INDEX IDX_TOOL_CALLS_PLAN ON TASK_TOOL_CALLS(PLAN_ID);
CREATE INDEX IDX_TOOL_CALLS_TIME ON TASK_TOOL_CALLS(CREATED_AT DESC);

-- Sequences (for auto-increment primary keys)
CREATE SEQUENCE SEQ_TASK_PLANS START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_TASK_STEPS START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_CONTEXT_SNAPSHOTS START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_TOOL_CALLS START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_TASK_DEPS START WITH 1 INCREMENT BY 1;
```

---

## 📚 Documentation

- [CHANGELOG.md](./CHANGELOG.md) - Complete version history (v0.2.0 through v1.0.0)
- [RELEASE_NOTES_v1.0.0.md](./RELEASE_NOTES_v1.0.0.md) - Detailed release notes for v1.0.0
- [Oracle PL/SQL Vector Pitfalls](./references/oracle-plsql-vector-pitfalls.md) - TO_VECTOR/CASE WHEN/SEQUENCE 踩坑记录
- [Knowledge Base Design](./references/knowledge-base-design.md) - Knowledge Base system architecture and schema design

## 🧪 Test Suite

- `scripts/test_v051_complete.py` - **v0.5.1 完整测试套件** (8 tests): Vector Storage, Similarity Search, Data Masking, Agent Permission Downgrade, Memory Fusion Engine, Session Expiry Management, Enhanced Snapshot Cleanup, Scripts Verification
- `scripts/test_complete_memory_system.py` - Legacy v0.4.3 test suite (Vector Storage, Similarity Search, Data Masking, Scripts Verification)

**运行测试**: `cd /root/.hermes/skills/oracle-memory-by-yhw && python3 scripts/test_v051_complete.py`

---

## 🏗️ Multi-Agent Architecture (Integrated)

Oracle Memory System provides **multi-agent deployment** with shared and private memory isolation. This enables multiple AI agents to collaborate while maintaining appropriate access boundaries.

### Core Features
- **Agent Registry**: Centralized registration and discovery of all agents
- **Memory Visibility Control**: Three visibility levels (SHARED/PRIVATE/COLLABORATIVE)  
- **Session Management**: Track active sessions with working context preservation
- **Access Audit Trail**: Complete logging of all memory access operations
- **Collaboration Workflow**: Request/approve mechanism for agent-to-agent knowledge sharing

### Quick Start

#### 1. Execute Schema Extensions
```bash
echo "SQL" | sql openclaw/hermes@//10.10.10.130:1521/openclaw << 'EOF'
@scripts/agent_schema.sql
EXIT;
EOF
```

#### 2. Register an Agent
```python
from scripts.agent_api import AgentRegistryAPI

AgentRegistryAPI.register_agent(
    agent_id="my-agent-01",
    agent_name="My Analysis Agent", 
    agent_type="analysis",
    capabilities=["data-analysis", "pattern-recognition"],
    description="AI agent for data analysis"
)
```

#### 3. Create Memory with Visibility Control
```python
from scripts.agent_api import MemoryVisibilityAPI

# SHARED - accessible to all agents
MemoryVisibilityAPI.create_memory(
    memory_data={'content': 'System Documentation', 'category': 'doc'},
    visibility="SHARED"
)

# PRIVATE - only this agent can access  
MemoryVisibilityAPI.create_memory(
    memory_data={'content': 'My Private Config', 'category': 'config'},
    agent_id="my-agent-01",
    visibility="PRIVATE"
)

# COLLABORATIVE - specific agents only
MemoryVisibilityAPI.create_memory(
    memory_data={'content': 'Team Notes', 'category': 'team'},
    visibility="COLLABORATIVE",
    accessible_to=["agent-1", "agent-2"]
)
```

### Memory Visibility Matrix

| Visibility | Description | Who Can Access |
|------------|-------------|----------------|
| **SHARED** | Global knowledge (API docs, schemas) | All registered agents ✅ |
| **PRIVATE** | Agent-specific context and preferences | Only the owner agent ✅ |
| **COLLABORATIVE** | Team/project shared space | Agents in ACCESSIBLE_TO list ✅ |

### Schema Files
- `scripts/agent_schema.sql` - Complete DDL for all new tables, views, and indexes
- `scripts/agent_api.py` - High-level Python API for multi-agent operations
- `scripts/test_agent_architecture.py` - Integration test suite (run to verify setup)

### Design Document
See [references/multi-agent-design.md](./references/multi-agent-design.md) for detailed architecture explanation.

---

## 👨‍💻 Author & Maintainer

**Haiwen Yin (胖头鱼 🐟)**  
Oracle/PostgreSQL/MySQL ACE Database Expert

- **Blog**: https://blog.csdn.net/yhw1809
- **GitHub**: https://github.com/Haiwen-Yin

---

## 📄 License

This project is licensed under the Apache License, Version 2.0 - see the [LICENSE](LICENSE) file for details.

---

---

## 🆕 v1.0.0 Knowledge Base & Production Release

### ✨ What's New in v1.0.0

v1.0.0 is the first production-ready release, integrating a complete Knowledge Base system.

| Feature | Description | Status |
|---------|-------------|--------|
| **Knowledge Concepts** | Stable knowledge entities (FACT/RULE/PATTERN/EXPERIENCE/PRINCIPLE) | ✅ Implemented |
| **Knowledge Graph** | Property Graph-based relationship management (IS_A/PART_OF/CAUSES/ENABLES/CONTRADICTS/SUPPORTS) | ✅ Implemented |
| **Experience Distillation** | Automatic memory-to-knowledge transformation | ✅ Implemented |
| **Hybrid Search** | Semantic search + graph traversal combination | ✅ Implemented |
| **Version Control** | Complete version history for knowledge concepts | ✅ Implemented |
| **Automated Jobs** | 6 scheduled jobs for knowledge lifecycle management | ✅ Implemented |

### Knowledge vs Memory

| Aspect | Memory | Knowledge |
|--------|--------|-----------|
| **Stability** | Dynamic/Short-term | Stable/Long-term |
| **Lifecycle** | Can be forgotten/merged/deleted | Long-term retention/versioned |
| **Source** | Conversations/Working context | Experience distillation/Manual/Imported |
| **Quality** | Raw/Unvalidated | Validated/High confidence |
| **Structure** | Flat | Graph-based (Entities + Relationships) |

### New Files Added (v1.0.0)

- `scripts/knowledge_base_schema_v2.sql` - 7 core tables + views + triggers (8.5 KB)
- `scripts/knowledge_base_api.sql` - PL/SQL API package (32.8 KB)
- `scripts/knowledge_property_graph.sql` - Property Graph definition (8.8 KB)
- `scripts/knowledge_jobs.sql` - 6 scheduled jobs + helper procedures (12.2 KB)
- `scripts/knowledge_base_api.py` - Python client library (23.8 KB)
- `scripts/test_knowledge_base.py` - Test suite (10.2 KB)
- `references/knowledge-base-design.md` - Detailed design document (27.5 KB)
- `README_KNOWLEDGE_BASE.md` - Usage documentation (13.3 KB)
- `RELEASE_NOTES_v1.0.0.md` - Release notes (11.5 KB)

### Deployment (v1.0.0)

```bash
# 1. Deploy Knowledge Base Schema
sql openclaw/hermes@//10.10.10.130:1521/openclaw @scripts/knowledge_base_schema_v2.sql

# 2. Deploy PL/SQL API
sql openclaw/hermes@//10.10.10.130:1521/openclaw @scripts/knowledge_base_api.sql

# 3. Create Property Graph
sql openclaw/hermes@//10.10.10.130:1521/openclaw @scripts/knowledge_property_graph.sql

# 4. Deploy Scheduled Jobs
sql openclaw/hermes@//10.10.10.130:1521/openclaw @scripts/knowledge_jobs.sql

# 5. Verify deployment
python3 scripts/test_knowledge_base.py
```

### ⚠️ Critical Pitfall: SQLcl CLOB Truncation

**Problem**: When PL/SQL functions return CLOB JSON (e.g., `knowledge_base_api.get_concept()`, `knowledge_base_api.get_statistics()`, `knowledge_base_api.get_graph_metrics()`), SQLcl truncates the output, causing JSON parse errors like "Unterminated string starting at: line 1 column 71".

**Root Cause**: SQLcl has output buffer limits for CLOB data. The JSON string gets cut off mid-way.

**Solution**: In Python API, use **direct SQL queries** instead of calling PL/SQL functions that return CLOB JSON. For example:

```python
# ❌ BAD: Truncated by SQLcl
sql = "SELECT knowledge_base_api.get_concept(1) FROM DUAL"

# ✅ GOOD: Direct SQL query
sql = "SELECT CONCEPT_ID, CONCEPT_NAME, CONCEPT_TYPE FROM KNOWLEDGE_CONCEPTS WHERE CONCEPT_ID = 1"
```

**For getting IDs after INSERT**: Do NOT rely on `DBMS_OUTPUT.PUT_LINE` (not captured by SQLcl). Use sequence CURRVAL or query by name+timestamp:

```python
# ❌ BAD: DBMS_OUTPUT not captured
sql = "BEGIN DBMS_OUTPUT.PUT_LINE(v_id); END;"

# ✅ GOOD: Query sequence
query_sql = "SELECT KNOWLEDGE_CONCEPTS_SEQ.CURRVAL FROM DUAL"
# OR fallback:
query_sql = "SELECT CONCEPT_ID FROM KNOWLEDGE_CONCEPTS WHERE CONCEPT_NAME = 'xxx' ORDER BY CREATED_AT DESC FETCH FIRST 1 ROWS ONLY"
```

### ⚠️ Critical Pitfall: Schema Mismatch (v1.0.0 Actual vs Assumed)

**Problem**: The v1.0.0 schema DDL (`knowledge_base_schema_v2.sql`) defines tables, but the **actual database schema may differ** from what the DDL specifies. Writing Python API code based on assumed schema causes ORA-00904 errors.

**Actual Schema (verified 2026-05-09)**:
```sql
-- KNOWLEDGE_CONCEPTS actual columns:
CONCEPT_ID, CONCEPT_NAME, CONCEPT_TYPE, CATEGORY, TITLE, DESCRIPTION, CONTENT,
SOURCE_TYPE, SOURCE_MEMORY_IDS, CONFIDENCE, VALIDATION_STATUS, EMBEDDING, 
TAGS, METADATA, CREATED_AT, UPDATED_AT, VALIDATED_AT, DEPRECATED_AT, VERSION, IS_CURRENT

-- KNOWLEDGE_GRAPH actual columns (NOT knowledge_relationships):
RELATIONSHIP_ID, SOURCE_CONCEPT_ID, TARGET_CONCEPT_ID, RELATIONSHIP_TYPE,
RELATIONSHIP_STRENGTH, PROPERTIES, CREATED_AT, UPDATED_AT, SOURCE_TYPE, CONFIDENCE
```

**Key Differences from Assumed Schema**:
- Column is `CONCEPT_NAME` not `name`
- Table is `KNOWLEDGE_GRAPH` not `knowledge_relationships`
- Column is `RELATIONSHIP_STRENGTH` not `strength`
- No `properties` column on KNOWLEDGE_CONCEPTS (use METADATA instead)

**Root Cause**: DDL scripts define schema, but actual deployed schema may have been modified or use different naming conventions.

**Solution**: ALWAYS verify actual schema before writing code:
```bash
echo "DESCRIBE KNOWLEDGE_CONCEPTS" | sql openclaw/hermes@//10.10.10.130:1521/openclaw
echo "DESCRIBE KNOWLEDGE_GRAPH" | sql openclaw/hermes@//10.10.10.130:1521/openclaw
```

**Python API Fix**: Use actual column names in all SQL queries. The optimized API (`knowledge_base_api_optimized.py`) has been updated to match actual schema.

---

### ⚠️ Critical Pitfall: Testing Methodology for New Features

**Problem**: Claiming code is "tested" without actually running it against the database leads to hidden bugs.

**Root Cause**: Writing Python API code based on assumed schema, then claiming it works without verification.

**Mandatory Pre-Coding Step: Schema Verification**

Before writing ANY Python code that touches Oracle tables, run `DESCRIBE` on EVERY table the code will reference:

```bash
echo "DESCRIBE KNOWLEDGE_CONCEPTS" | sql openclaw/hermes@//10.10.10.130:1521/openclaw
echo "DESCRIBE KNOWLEDGE_GRAPH" | sql openclaw/hermes@//10.10.10.130:1521/openclaw
echo "DESCRIBE KNOWLEDGE_TAGS" | sql openclaw/hermes@//10.10.10.130:1521/openclaw
```

Document actual column names in code comments before writing queries. **Never assume column names from DDL scripts** — actual deployed schema may differ.

**Correct Testing Workflow**:
1. **Verify schema first**: Run `DESCRIBE table_name` for ALL tables (not just one)
2. **Test with direct SQL**: Use SQLcl to verify SQL syntax works
3. **Test Python API**: Run actual Python code against database
4. **Verify output**: Check that parsed results match expected data
5. **Clean up**: Delete all test data

**Quick Test Pattern**:
```bash
# 1. Verify table exists and has correct columns
echo "DESCRIBE KNOWLEDGE_CONCEPTS" | sql openclaw/hermes@//10.10.10.130:1521/openclaw

# 2. Test INSERT works
echo "INSERT INTO KNOWLEDGE_CONCEPTS (CONCEPT_ID, CONCEPT_NAME, CONCEPT_TYPE) VALUES (9999, 'Test', 'test')" | sql openclaw/hermes@//10.10.10.130:1521/openclaw

# 3. Verify INSERT succeeded
echo "SELECT * FROM KNOWLEDGE_CONCEPTS WHERE CONCEPT_ID = 9999" | sql openclaw/hermes@//10.10.10.130:1521/openclaw

# 4. Cleanup test data
echo "DELETE FROM KNOWLEDGE_CONCEPTS WHERE CONCEPT_ID = 9999" | sql openclaw/hermes@//10.10.10.130:1521/openclaw
```

**Never claim code is "tested" without running these steps.**

### ⚠️ Critical Pitfall: SQLcl is the ONLY Database Interface

**Problem**: Attempting to use Python Oracle drivers (`oracledb`, `cx_Oracle`) fails because they are not installed on this system.

**Root Cause**: This environment uses SQLcl command-line tool as the sole Oracle database interface. There are no Python Oracle client libraries available.

**Solution**: All Python database operations MUST use `subprocess` to call SQLcl:

```python
# ❌ BAD - Module not available
import oracledb
connection = oracledb.connect(...)

# ✅ GOOD - Use SQLcl via subprocess
import subprocess
cmd = f'echo "{sql}" | /root/sqlcl/sqlcl/bin/sql openclaw/hermes@//10.10.10.130:1521/openclaw'
result = subprocess.run(['bash', '-c', cmd], capture_output=True, text=True, timeout=30)
```

**SQLcl path**: `/root/sqlcl/sqlcl/bin/sql`
**Connection string**: `openclaw/hermes@//10.10.10.130:1521/openclaw`

---

### ⚠️ Critical Pitfall: SQLcl Table Output Format

SQLcl returns results in a fixed-width table format. Parse by looking for the separator line pattern:

```
   CONCEPT_ID CONCEPT_NAME    CONCEPT_TYPE    
_____________ _______________ _______________ 
            1 Test Concept    FACT            
```

**Parsing algorithm**:
```python
lines = output.split('\n')
for i, line in enumerate(lines):
    if '___' in line and 'HEADER_NAME' in lines[i-1]:
        # Found separator, next line is data
        data_line = lines[i + 1].strip()
        parts = data_line.split()
        # Handle multiple spaces by splitting on whitespace
```

## 📚 Documentation & Optimization Artifacts (v1.0.0)

### Documentation
- `Examples_Guide.md` (24KB) — Comprehensive usage examples: memory/concept/relationship CRUD, search, batch ops, multi-agent patterns, error handling, best practices
- `Performance_Optimization.md` (32KB) — HNSW/IVF index tuning, query optimization, connection pooling, caching strategies, monitoring, benchmarking
- `API_Reference.md` (31KB) — Complete Python API, SQL API, PL/SQL package, data types, error codes reference
- `README_KNOWLEDGE_BASE.md` — Knowledge base system usage guide
- `references/knowledge-base-design.md` — System architecture and schema design

### Optimization Scripts
- `scripts/vector_index_optimization.sql` — HNSW/IVF index creation, maintenance, and monitoring
- `scripts/query_optimization.sql` — Execution plan analysis, partition pruning, result cache, parallel query
- `scripts/monitoring_diagnostics.sql` — System health checks, performance baselines, alert thresholds

### Code Optimization
- `scripts/knowledge_base_api_optimized.py` — Production API with connection pooling, query caching, batch operations, transaction support
- `scripts/batch_operations.py` — High-performance batch insert/update/delete with parallel processing and retry logic
- `scripts/final_verification_test.py` — 10-test verification suite covering all v1.0.0 features

---

**Last Updated**: 2026-05-09 v1.0.0
Comments 0
Latest | Hot
Please login before commenting.
Loading comments...
oracle-memory-by-yhw

Directory Structure

SKILL.md

Report

Notice
