81 lines
3.2 KiB
SQL
81 lines
3.2 KiB
SQL
-- Simplified schema without vector extensions
|
|
-- For basic functionality testing
|
|
|
|
-- Create basic tables for memory system
|
|
CREATE TABLE IF NOT EXISTS code_embeddings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
repo_id VARCHAR(255) NOT NULL,
|
|
file_path TEXT NOT NULL,
|
|
content_hash VARCHAR(64) NOT NULL,
|
|
embedding TEXT, -- Store as text for now
|
|
metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
last_accessed TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
access_count INTEGER DEFAULT 0,
|
|
|
|
CONSTRAINT unique_code_analysis UNIQUE(repo_id, file_path, content_hash)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS query_embeddings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
session_id VARCHAR(255) NOT NULL,
|
|
query_text TEXT NOT NULL,
|
|
query_embedding TEXT, -- Store as text for now
|
|
response_embedding TEXT,
|
|
repo_context VARCHAR(255),
|
|
timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
metadata JSONB DEFAULT '{}',
|
|
|
|
CONSTRAINT valid_session_id CHECK (LENGTH(session_id) > 0)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS knowledge_embeddings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
fact_id VARCHAR(255) UNIQUE NOT NULL,
|
|
content TEXT NOT NULL,
|
|
category VARCHAR(100) NOT NULL,
|
|
confidence_score FLOAT DEFAULT 0.0,
|
|
embedding TEXT, -- Store as text for now
|
|
source_repos TEXT[] DEFAULT '{}',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
last_accessed TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
access_frequency INTEGER DEFAULT 0,
|
|
metadata JSONB DEFAULT '{}',
|
|
|
|
CONSTRAINT valid_confidence CHECK (confidence_score >= 0.0 AND confidence_score <= 1.0)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS repository_quality_summary (
|
|
repo_id VARCHAR(255) PRIMARY KEY,
|
|
total_files INTEGER DEFAULT 0,
|
|
total_lines INTEGER DEFAULT 0,
|
|
code_quality_score FLOAT DEFAULT 0.0,
|
|
architecture_score FLOAT DEFAULT 0.0,
|
|
security_score FLOAT DEFAULT 0.0,
|
|
last_analyzed TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
analysis_metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS recent_activity (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
repo_id VARCHAR(255) NOT NULL,
|
|
activity_type VARCHAR(50) NOT NULL,
|
|
activity_data JSONB DEFAULT '{}',
|
|
timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create indexes
|
|
CREATE INDEX IF NOT EXISTS idx_code_embeddings_repo_id ON code_embeddings(repo_id);
|
|
CREATE INDEX IF NOT EXISTS idx_code_embeddings_file_path ON code_embeddings(file_path);
|
|
CREATE INDEX IF NOT EXISTS idx_query_embeddings_session_id ON query_embeddings(session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_knowledge_embeddings_category ON knowledge_embeddings(category);
|
|
CREATE INDEX IF NOT EXISTS idx_recent_activity_repo_id ON recent_activity(repo_id);
|
|
CREATE INDEX IF NOT EXISTS idx_recent_activity_timestamp ON recent_activity(timestamp);
|
|
|
|
-- Insert initial data
|
|
INSERT INTO repository_quality_summary (repo_id, total_files, total_lines, code_quality_score)
|
|
VALUES ('default', 0, 0, 5.0)
|
|
ON CONFLICT (repo_id) DO NOTHING;
|