-- ============================================================================
-- Memory MCP Server - Database Initialization Script
-- ============================================================================
--
-- Purpose: Complete database setup for the Memory MCP Server
-- Version: 2.0.0
-- Last Updated: 2025-12-01
--
-- This script is IDEMPOTENT - safe to run multiple times without data loss.
-- All statements use IF NOT EXISTS or CREATE OR REPLACE patterns.
--
-- USAGE:
-- psql -h <host> -p <port> -U <user> -d <database> -f init.sql
--
-- PREREQUISITES:
-- 1. PostgreSQL 14+ with pgvector extension installed
-- 2. Database already created (e.g., CREATE DATABASE mcp_memory;)
--
-- ============================================================================
-- Enable the vector extension (required for embedding storage and similarity search)
CREATE EXTENSION IF NOT EXISTS vector;
-- ============================================================================
-- PART 1: Core Tables
-- ============================================================================
-- --------------------------------------
-- 1.1 Memories Table (Semantic Memory)
-- --------------------------------------
-- Stores memories with vector embeddings for semantic search
-- Uses Aliyun text-embedding-v3 which outputs 1024-dimensional vectors
CREATE TABLE IF NOT EXISTS memories (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
type TEXT NOT NULL, -- Memory type (e.g., 'fact', 'preference', 'conversation')
content JSONB NOT NULL, -- Structured content
source TEXT NOT NULL, -- Origin of the memory
embedding vector(1024) NOT NULL, -- Aliyun text-embedding-v3 (1024 dimensions)
tags TEXT[] DEFAULT '{}', -- Searchable tags
confidence DOUBLE PRECISION NOT NULL DEFAULT 1.0, -- Confidence score (0.0 - 1.0)
access_count INTEGER DEFAULT 0, -- Number of times accessed (for decay calculation)
last_accessed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, -- Last access time (for decay calculation)
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
-- Add columns if they don't exist (for existing installations upgrading from v1)
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'memories' AND column_name = 'access_count') THEN
ALTER TABLE memories ADD COLUMN access_count INTEGER DEFAULT 0;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'memories' AND column_name = 'last_accessed_at') THEN
ALTER TABLE memories ADD COLUMN last_accessed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP;
END IF;
END $$;
-- --------------------------------------
-- 1.2 Entities Table (Knowledge Graph Nodes)
-- --------------------------------------
CREATE TABLE IF NOT EXISTS entities (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL UNIQUE, -- Unique entity name
entity_type TEXT NOT NULL, -- Entity type (e.g., 'Person', 'Project', 'Concept')
observations TEXT[] DEFAULT '{}', -- Facts/observations about the entity
embedding vector(1024), -- Optional: for semantic search on entities
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
-- --------------------------------------
-- 1.3 Relations Table (Knowledge Graph Edges)
-- --------------------------------------
CREATE TABLE IF NOT EXISTS relations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
from_entity TEXT NOT NULL REFERENCES entities(name) ON DELETE CASCADE,
to_entity TEXT NOT NULL REFERENCES entities(name) ON DELETE CASCADE,
relation_type TEXT NOT NULL, -- Relation type (e.g., 'contains', 'manages', 'uses')
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
UNIQUE(from_entity, to_entity, relation_type)
);
-- --------------------------------------
-- 1.4 Memory History Table (Version Tracking)
-- --------------------------------------
-- Tracks all changes to memories for audit and rollback purposes
CREATE TABLE IF NOT EXISTS memory_history (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
memory_id UUID NOT NULL, -- Reference to the memory (not FK to allow tracking deletes)
content JSONB NOT NULL, -- Content at time of change
change_type TEXT NOT NULL CHECK (change_type IN ('create', 'update', 'delete')),
changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
previous_confidence DOUBLE PRECISION, -- Confidence before change
new_confidence DOUBLE PRECISION -- Confidence after change
);
-- ============================================================================
-- PART 2: Indexes
-- ============================================================================
-- Memories indexes
-- HNSW index for fast vector similarity search (better than IVFFlat for most use cases)
CREATE INDEX IF NOT EXISTS idx_memories_embedding ON memories
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
CREATE INDEX IF NOT EXISTS idx_memories_type ON memories(type);
CREATE INDEX IF NOT EXISTS idx_memories_tags ON memories USING GIN(tags);
-- Entities indexes
CREATE INDEX IF NOT EXISTS idx_entities_name ON entities(name);
CREATE INDEX IF NOT EXISTS idx_entities_type ON entities(entity_type);
CREATE INDEX IF NOT EXISTS idx_entities_embedding ON entities
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64)
WHERE embedding IS NOT NULL;
-- Relations indexes
CREATE INDEX IF NOT EXISTS idx_relations_from ON relations(from_entity);
CREATE INDEX IF NOT EXISTS idx_relations_to ON relations(to_entity);
CREATE INDEX IF NOT EXISTS idx_relations_type ON relations(relation_type);
-- Memory history indexes
CREATE INDEX IF NOT EXISTS idx_memory_history_memory_id ON memory_history(memory_id);
CREATE INDEX IF NOT EXISTS idx_memory_history_changed_at ON memory_history(changed_at DESC);
-- ============================================================================
-- PART 3: Functions
-- ============================================================================
-- --------------------------------------
-- 3.1 Auto-update timestamp trigger
-- --------------------------------------
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- --------------------------------------
-- 3.2 Memory Decay Weight Calculation
-- --------------------------------------
-- Calculates memory relevance weight based on:
-- - Base confidence score
-- - Time decay: 10% reduction every 30 days
-- - Access bonus: +5% per access, capped at 50%
CREATE OR REPLACE FUNCTION calculate_memory_weight(
p_confidence DOUBLE PRECISION,
p_access_count INTEGER,
p_last_accessed_at TIMESTAMPTZ
) RETURNS DOUBLE PRECISION AS $$
DECLARE
days_since_access DOUBLE PRECISION;
time_decay DOUBLE PRECISION;
access_bonus DOUBLE PRECISION;
BEGIN
-- Calculate days since last access
days_since_access := EXTRACT(EPOCH FROM (NOW() - COALESCE(p_last_accessed_at, NOW()))) / 86400.0;
-- Time decay: 10% reduction every 30 days (0.9^(days/30))
time_decay := POWER(0.9, days_since_access / 30.0);
-- Access bonus: 5% per access, max 50%
access_bonus := 1.0 + LEAST(COALESCE(p_access_count, 0) * 0.05, 0.5);
RETURN COALESCE(p_confidence, 1.0) * time_decay * access_bonus;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- --------------------------------------
-- 3.3 Memory Access Update
-- --------------------------------------
CREATE OR REPLACE FUNCTION update_memory_access(p_memory_id UUID)
RETURNS VOID AS $$
BEGIN
UPDATE memories
SET access_count = access_count + 1,
last_accessed_at = CURRENT_TIMESTAMP
WHERE id = p_memory_id;
END;
$$ LANGUAGE plpgsql;
-- --------------------------------------
-- 3.4 Memory History Logger
-- --------------------------------------
CREATE OR REPLACE FUNCTION log_memory_change()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO memory_history (memory_id, content, change_type, new_confidence)
VALUES (NEW.id, NEW.content, 'create', NEW.confidence);
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
-- Only log if content or confidence actually changed
IF OLD.content IS DISTINCT FROM NEW.content OR OLD.confidence IS DISTINCT FROM NEW.confidence THEN
INSERT INTO memory_history (memory_id, content, change_type, previous_confidence, new_confidence)
VALUES (NEW.id, OLD.content, 'update', OLD.confidence, NEW.confidence);
END IF;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO memory_history (memory_id, content, change_type, previous_confidence)
VALUES (OLD.id, OLD.content, 'delete', OLD.confidence);
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- ============================================================================
-- PART 4: Triggers
-- ============================================================================
-- Auto-update timestamps
DROP TRIGGER IF EXISTS update_memories_updated_at ON memories;
CREATE TRIGGER update_memories_updated_at
BEFORE UPDATE ON memories
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
DROP TRIGGER IF EXISTS update_entities_updated_at ON entities;
CREATE TRIGGER update_entities_updated_at
BEFORE UPDATE ON entities
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
-- Memory history logging
DROP TRIGGER IF EXISTS memory_history_trigger ON memories;
CREATE TRIGGER memory_history_trigger
AFTER INSERT OR UPDATE OR DELETE ON memories
FOR EACH ROW
EXECUTE FUNCTION log_memory_change();
-- ============================================================================
-- PART 5: Documentation Comments
-- ============================================================================
COMMENT ON TABLE memories IS 'Semantic memory storage with vector embeddings for similarity search';
COMMENT ON TABLE entities IS 'Knowledge graph nodes representing entities';
COMMENT ON TABLE relations IS 'Knowledge graph edges representing relationships between entities';
COMMENT ON TABLE memory_history IS 'Audit log tracking all changes to memories';
COMMENT ON FUNCTION calculate_memory_weight IS 'Calculates memory relevance weight based on confidence, access frequency, and time decay';
COMMENT ON FUNCTION update_memory_access IS 'Updates access count and timestamp when a memory is retrieved';
COMMENT ON FUNCTION log_memory_change IS 'Trigger function to log memory changes to history table';
-- ============================================================================
-- Initialization Complete
-- ============================================================================
--
-- To verify installation, run:
-- SELECT COUNT(*) FROM pg_extension WHERE extname = 'vector';
-- SELECT COUNT(*) FROM information_schema.tables
-- WHERE table_name IN ('memories', 'entities', 'relations', 'memory_history');
--
-- ============================================================================
