This is page 16 of 18. Use http://codebase.md/minipuft/claude-prompts-mcp?lines=true&page={x} to view the full context.
# Directory Structure
```
├── .actrc
├── .gitattributes
├── .github
│ └── workflows
│ ├── ci.yml
│ ├── mcp-compliance.yml
│ └── pr-validation.yml
├── .gitignore
├── agent.md
├── assets
│ └── logo.png
├── CLAUDE.md
├── config
│ └── framework-state.json
├── docs
│ ├── architecture.md
│ ├── chain-modification-examples.md
│ ├── contributing.md
│ ├── enhanced-gate-system.md
│ ├── execution-architecture-guide.md
│ ├── installation-guide.md
│ ├── mcp-tool-usage-guide.md
│ ├── mcp-tools-reference.md
│ ├── prompt-format-guide.md
│ ├── prompt-management.md
│ ├── prompt-vs-template-guide.md
│ ├── README.md
│ ├── template-development-guide.md
│ ├── TODO.md
│ ├── troubleshooting.md
│ └── version-history.md
├── LICENSE
├── local-test.sh
├── plans
│ ├── nunjucks-dynamic-chain-orchestration.md
│ ├── outputschema-realtime-progress-and-validation.md
│ ├── parallel-conditional-execution-analysis.md
│ ├── sqlite-storage-migration.md
│ └── symbolic-command-language-implementation.md
├── README.md
├── scripts
│ ├── setup-windows-testing.sh
│ ├── test_server.js
│ ├── test-all-platforms.sh
│ └── windows-tests
│ ├── test-windows-paths.js
│ ├── test-windows-startup.sh
│ └── windows-env.sh
└── server
├── config
│ ├── framework-state.json
│ └── tool-descriptions.json
├── config.json
├── jest.config.cjs
├── LICENSE
├── package-lock.json
├── package.json
├── prompts
│ ├── analysis
│ │ ├── advanced_analysis_engine.md
│ │ ├── content_analysis.md
│ │ ├── deep_analysis.md
│ │ ├── deep_research.md
│ │ ├── markdown_notebook.md
│ │ ├── note_integration.md
│ │ ├── note_refinement.md
│ │ ├── notes.md
│ │ ├── progressive_research.md
│ │ ├── prompts.json
│ │ ├── query_refinement.md
│ │ └── review.md
│ ├── architecture
│ │ ├── prompts.json
│ │ └── strategic-system-alignment.md
│ ├── content_processing
│ │ ├── format_enhancement.md
│ │ ├── noteIntegration.md
│ │ ├── obsidian_metadata_optimizer.md
│ │ ├── prompts.json
│ │ ├── vault_related_notes_finder.md
│ │ └── video_notes_enhanced.md
│ ├── debugging
│ │ ├── analyze_logs.md
│ │ └── prompts.json
│ ├── development
│ │ ├── analyze_code_structure.md
│ │ ├── analyze_file_structure.md
│ │ ├── code_review_optimization_chain.md
│ │ ├── component_flow_analysis.md
│ │ ├── create_modularization_plan.md
│ │ ├── detect_code_issues.md
│ │ ├── detect_project_commands.md
│ │ ├── expert_code_implementation.md
│ │ ├── generate_comprehensive_claude_md.md
│ │ ├── prompts.json
│ │ ├── strategicImplement.md
│ │ ├── suggest_code_improvements.md
│ │ └── transform_code_to_modules.md
│ ├── documentation
│ │ ├── create_docs_chain.md
│ │ ├── docs-content-creation.md
│ │ ├── docs-content-planning.md
│ │ ├── docs-final-assembly.md
│ │ ├── docs-project-analysis.md
│ │ ├── docs-review-refinement.md
│ │ └── prompts.json
│ ├── education
│ │ ├── prompts.json
│ │ └── vault_integrated_notes.md
│ ├── general
│ │ ├── diagnose.md
│ │ └── prompts.json
│ ├── promptsConfig.json
│ └── testing
│ ├── final_verification_test.md
│ └── prompts.json
├── README.md
├── scripts
│ └── validate-dependencies.js
├── src
│ ├── api
│ │ └── index.ts
│ ├── chain-session
│ │ └── manager.ts
│ ├── config
│ │ └── index.ts
│ ├── Dockerfile
│ ├── execution
│ │ ├── context
│ │ │ ├── context-resolver.ts
│ │ │ ├── framework-injector.ts
│ │ │ └── index.ts
│ │ ├── index.ts
│ │ ├── parsers
│ │ │ ├── argument-parser.ts
│ │ │ ├── index.ts
│ │ │ └── unified-command-parser.ts
│ │ └── types.ts
│ ├── frameworks
│ │ ├── framework-manager.ts
│ │ ├── framework-state-manager.ts
│ │ ├── index.ts
│ │ ├── integration
│ │ │ ├── framework-semantic-integration.ts
│ │ │ └── index.ts
│ │ ├── methodology
│ │ │ ├── guides
│ │ │ │ ├── 5w1h-guide.ts
│ │ │ │ ├── cageerf-guide.ts
│ │ │ │ ├── react-guide.ts
│ │ │ │ └── scamper-guide.ts
│ │ │ ├── index.ts
│ │ │ ├── interfaces.ts
│ │ │ └── registry.ts
│ │ ├── prompt-guidance
│ │ │ ├── index.ts
│ │ │ ├── methodology-tracker.ts
│ │ │ ├── service.ts
│ │ │ ├── system-prompt-injector.ts
│ │ │ └── template-enhancer.ts
│ │ └── types
│ │ ├── index.ts
│ │ ├── integration-types.ts
│ │ ├── methodology-types.ts
│ │ └── prompt-guidance-types.ts
│ ├── gates
│ │ ├── constants.ts
│ │ ├── core
│ │ │ ├── gate-definitions.ts
│ │ │ ├── gate-loader.ts
│ │ │ ├── gate-validator.ts
│ │ │ ├── index.ts
│ │ │ └── temporary-gate-registry.ts
│ │ ├── definitions
│ │ │ ├── code-quality.json
│ │ │ ├── content-structure.json
│ │ │ ├── educational-clarity.json
│ │ │ ├── framework-compliance.json
│ │ │ ├── research-quality.json
│ │ │ ├── security-awareness.json
│ │ │ └── technical-accuracy.json
│ │ ├── gate-state-manager.ts
│ │ ├── guidance
│ │ │ ├── FrameworkGuidanceFilter.ts
│ │ │ └── GateGuidanceRenderer.ts
│ │ ├── index.ts
│ │ ├── intelligence
│ │ │ ├── GatePerformanceAnalyzer.ts
│ │ │ └── GateSelectionEngine.ts
│ │ ├── templates
│ │ │ ├── code_quality_validation.md
│ │ │ ├── educational_clarity_validation.md
│ │ │ ├── framework_compliance_validation.md
│ │ │ ├── research_self_validation.md
│ │ │ ├── security_validation.md
│ │ │ ├── structure_validation.md
│ │ │ └── technical_accuracy_validation.md
│ │ └── types.ts
│ ├── index.ts
│ ├── logging
│ │ └── index.ts
│ ├── mcp-tools
│ │ ├── config-utils.ts
│ │ ├── constants.ts
│ │ ├── index.ts
│ │ ├── prompt-engine
│ │ │ ├── core
│ │ │ │ ├── engine.ts
│ │ │ │ ├── executor.ts
│ │ │ │ ├── index.ts
│ │ │ │ └── types.ts
│ │ │ ├── index.ts
│ │ │ ├── processors
│ │ │ │ ├── response-formatter.ts
│ │ │ │ └── template-processor.ts
│ │ │ └── utils
│ │ │ ├── category-extractor.ts
│ │ │ ├── classification.ts
│ │ │ ├── context-builder.ts
│ │ │ └── validation.ts
│ │ ├── prompt-manager
│ │ │ ├── analysis
│ │ │ │ ├── comparison-engine.ts
│ │ │ │ ├── gate-analyzer.ts
│ │ │ │ └── prompt-analyzer.ts
│ │ │ ├── core
│ │ │ │ ├── index.ts
│ │ │ │ ├── manager.ts
│ │ │ │ └── types.ts
│ │ │ ├── index.ts
│ │ │ ├── operations
│ │ │ │ └── file-operations.ts
│ │ │ ├── search
│ │ │ │ ├── filter-parser.ts
│ │ │ │ └── prompt-matcher.ts
│ │ │ └── utils
│ │ │ ├── category-manager.ts
│ │ │ └── validation.ts
│ │ ├── shared
│ │ │ └── structured-response-builder.ts
│ │ ├── system-control.ts
│ │ ├── tool-description-manager.ts
│ │ └── types
│ │ └── shared-types.ts
│ ├── metrics
│ │ ├── analytics-service.ts
│ │ ├── index.ts
│ │ └── types.ts
│ ├── performance
│ │ ├── index.ts
│ │ └── monitor.ts
│ ├── prompts
│ │ ├── category-manager.ts
│ │ ├── converter.ts
│ │ ├── file-observer.ts
│ │ ├── hot-reload-manager.ts
│ │ ├── index.ts
│ │ ├── loader.ts
│ │ ├── promptUtils.ts
│ │ ├── registry.ts
│ │ └── types.ts
│ ├── runtime
│ │ ├── application.ts
│ │ └── startup.ts
│ ├── semantic
│ │ ├── configurable-semantic-analyzer.ts
│ │ └── integrations
│ │ ├── index.ts
│ │ └── llm-clients.ts
│ ├── server
│ │ ├── index.ts
│ │ └── transport
│ │ └── index.ts
│ ├── smithery.yaml
│ ├── text-references
│ │ ├── conversation.ts
│ │ └── index.ts
│ ├── types
│ │ └── index.ts
│ ├── types.ts
│ └── utils
│ ├── chainUtils.ts
│ ├── errorHandling.ts
│ ├── global-resource-tracker.ts
│ ├── index.ts
│ └── jsonUtils.ts
├── tests
│ ├── ci-startup-validation.js
│ ├── enhanced-validation
│ │ ├── contract-validation
│ │ │ ├── contract-test-suite.js
│ │ │ ├── interface-contracts.js
│ │ │ └── interface-contracts.ts
│ │ ├── environment-validation
│ │ │ ├── environment-parity-checker.js
│ │ │ └── environment-test-suite.js
│ │ ├── lifecycle-validation
│ │ │ ├── lifecycle-test-suite.js
│ │ │ └── process-lifecycle-validator.js
│ │ └── validation-orchestrator.js
│ ├── helpers
│ │ └── test-helpers.js
│ ├── integration
│ │ ├── mcp-tools.test.ts
│ │ ├── server-startup.test.ts
│ │ └── unified-parsing-integration.test.ts
│ ├── performance
│ │ ├── parsing-system-benchmark.test.ts
│ │ └── server-performance.test.ts
│ ├── scripts
│ │ ├── consolidated-tools.js
│ │ ├── establish-performance-baselines.js
│ │ ├── functional-mcp-validation.js
│ │ ├── integration-mcp-tools.js
│ │ ├── integration-routing-system.js
│ │ ├── integration-server-startup.js
│ │ ├── integration-unified-parsing.js
│ │ ├── methodology-guides.js
│ │ ├── performance-memory.js
│ │ ├── runtime-integration.js
│ │ ├── unit-conversation-manager.js
│ │ ├── unit-semantic-analyzer.js
│ │ └── unit-unified-parsing.js
│ ├── setup.ts
│ ├── test-enhanced-parsing.js
│ └── unit
│ ├── conversation-manager.test.ts
│ ├── semantic-analyzer-three-tier.test.ts
│ └── unified-parsing-system.test.ts
├── tsconfig.json
└── tsconfig.test.json
```
# Files
--------------------------------------------------------------------------------
/plans/sqlite-storage-migration.md:
--------------------------------------------------------------------------------
```markdown
1 | # SQLite Storage Layer - Comprehensive Implementation Plan
2 |
3 | **Created**: 2025-01-19
4 | **Status**: Planning
5 | **Priority**: High - Foundation for User Data Management & Versioning
6 | **Impact**: Enables change history, user data separation, scalable storage, no-fork user experience
7 |
8 | ## Executive Summary
9 |
10 | **Strategic Goal**: Migrate from JSON file-based storage to SQLite database with versioning capabilities, while maintaining backwards compatibility and enabling user data separation from the server codebase.
11 |
12 | **Core Requirements**:
13 | - **Change history** - Track all modifications with rollback capability
14 | - **User data separation** - User prompts/gates/frameworks stored in `~/.claude-prompts-mcp/` not in repo
15 | - **Modification workflow** - Enhanced MCP tool integration for seamless CRUD operations
16 | - **Scale to hundreds** - Handle 100-500 items efficiently
17 | - **Profile support** - Built-in foundation for work/life/code separation (UI comes later)
18 | - **Git-optional** - Users don't need to fork repo, but can export to JSON for Git backup
19 | - **Zero breaking changes** - Gradual migration with adapter pattern
20 |
21 | **Key Principle**: This is a **storage layer enhancement**, not a database system. Users never interact with SQL directly - all operations through existing MCP tools (`prompt_manager`, `system_control`).
22 |
23 | ---
24 |
25 | ## Table of Contents
26 |
27 | 1. [Current State Analysis](#current-state-analysis)
28 | 2. [Architecture Design](#architecture-design)
29 | 3. [Storage Abstraction Layer](#storage-abstraction-layer)
30 | 4. [SQLite Schema Design](#sqlite-schema-design)
31 | - [Profile Support Architecture](#profile-support-architecture)
32 | 5. [Migration Strategy](#migration-strategy)
33 | 6. [User Data Separation](#user-data-separation)
34 | 7. [Versioning & History System](#versioning--history-system)
35 | 8. [MCP Tool Integration](#mcp-tool-integration)
36 | 9. [Implementation Phases](#implementation-phases)
37 | 10. [Testing Strategy](#testing-strategy)
38 | 11. [Rollback & Recovery](#rollback--recovery)
39 | 12. [Performance Considerations](#performance-considerations)
40 | 13. [Future Enhancements](#future-enhancements)
41 | - [Profile Management Features (Phase 6+)](#profile-management-features-phase-6)
42 |
43 | ---
44 |
45 | ## Current State Analysis
46 |
47 | ### Existing Storage System
48 |
49 | **Current Architecture**:
50 | ```
51 | server/prompts/
52 | ├── promptsConfig.json # Main configuration
53 | ├── category-name/
54 | │ ├── prompts.json # Category prompt registry
55 | │ ├── prompt-1.md # Individual prompt files
56 | │ └── prompt-2.md
57 | └── another-category/
58 | └── ...
59 |
60 | Storage Mechanism:
61 | - JSON files for metadata and registry
62 | - Markdown files for prompt content
63 | - Hot-reload via file watching
64 | - Git version control for server defaults
65 | ```
66 |
67 | **Current Statistics** (measured):
68 | - **Files**: 48 total (JSON + Markdown)
69 | - **Storage**: 300KB total
70 | - **Categories**: 18 categories
71 | - **Scale**: Dozens of items (appropriate for file-based)
72 |
73 | **Pain Points Identified**:
74 | 1. **No change history** - Can't see previous versions or rollback changes
75 | 2. **Modification workflow** - Manual JSON editing required (though MCP tools help)
76 | 3. **User data in repo** - Users need to fork to customize, or modify in-place
77 | 4. **Limited querying** - File-based filtering is basic
78 | 5. **No audit trail** - Can't track who changed what and why
79 | 6. **No profile separation** - At 500+ items, need work/life/code separation (future concern)
80 |
81 | ### Framework & Gates Current Storage
82 |
83 | **Frameworks**:
84 | ```typescript
85 | // Location: server/src/frameworks/framework-manager.ts
86 | // Storage: In-memory Map<string, FrameworkDefinition>
87 | // Source: Generated from methodology guides (code-based)
88 | // Future need: User-customizable frameworks stored persistently
89 | ```
90 |
91 | **Gates**:
92 | ```typescript
93 | // Location: server/src/gates/core/gate-loader.ts
94 | // Storage: YAML/JSON files in server/src/gates/definitions/
95 | // Caching: In-memory Map with file watching
96 | // Future need: User-defined gates with versioning
97 | ```
98 |
99 | ### What Works Well (Keep)
100 |
101 | 1. **Hot-reload system** - File watching works great for development
102 | 2. **MCP tool interface** - `prompt_manager` provides good CRUD interface
103 | 3. **Category organization** - Logical grouping system is solid
104 | 4. **Markdown content** - Human-readable, easy to edit
105 | 5. **Git integration** - Works well for server defaults
106 |
107 | ### What Needs Enhancement
108 |
109 | 1. **Storage backend** - Add SQLite for versioning and querying
110 | 2. **User data location** - Move to `~/.claude-prompts-mcp/` for user customizations
111 | 3. **History tracking** - Automatic versioning on every change
112 | 4. **Migration path** - Smooth transition from JSON to SQLite
113 | 5. **Backup system** - Automatic backups before modifications
114 |
115 | ---
116 |
117 | ## Architecture Design
118 |
119 | ### High-Level Architecture
120 |
121 | ```
122 | ┌─────────────────────────────────────────────────────────────┐
123 | │ MCP Tools Layer │
124 | │ (prompt_manager, system_control - User Interface) │
125 | └─────────────────────────────────────────────────────────────┘
126 | │
127 | ▼
128 | ┌─────────────────────────────────────────────────────────────┐
129 | │ Storage Abstraction Layer │
130 | │ (IStorageAdapter) │
131 | └─────────────────────────────────────────────────────────────┘
132 | │ │
133 | ▼ ▼
134 | ┌──────────────────────┐ ┌──────────────────────────┐
135 | │ JSONStorageAdapter │ │ SQLiteStorageAdapter │
136 | │ (Legacy/Defaults) │ │ (User Data/History) │
137 | └──────────────────────┘ └──────────────────────────┘
138 | │ │
139 | ▼ ▼
140 | ┌──────────────────────┐ ┌──────────────────────────┐
141 | │ JSON Files │ │ SQLite Database │
142 | │ /server/defaults/ │ │ ~/.claude-prompts-mcp/ │
143 | └──────────────────────┘ └──────────────────────────┘
144 | ```
145 |
146 | ### Directory Structure (Post-Migration)
147 |
148 | ```
149 | Server Installation:
150 | /server/
151 | ├── src/
152 | │ ├── storage/ # NEW: Storage layer
153 | │ │ ├── interfaces/
154 | │ │ │ └── IStorageAdapter.ts # Storage interface
155 | │ │ ├── adapters/
156 | │ │ │ ├── json-adapter.ts # JSON implementation
157 | │ │ │ └── sqlite-adapter.ts # SQLite implementation
158 | │ │ ├── migrations/
159 | │ │ │ ├── json-to-sqlite.ts # Migration utilities
160 | │ │ │ └── schema-versions.ts # Schema versioning
161 | │ │ ├── versioning/
162 | │ │ │ ├── history-manager.ts # Change history
163 | │ │ │ └── backup-manager.ts # Automatic backups
164 | │ │ └── index.ts
165 | │ └── ...
166 | ├── defaults/ # MOVED: Server default prompts
167 | │ ├── prompts/
168 | │ │ ├── analysis/
169 | │ │ ├── development/
170 | │ │ └── ...
171 | │ ├── gates/
172 | │ └── frameworks/
173 | └── prompts/ # KEPT: Legacy location (symlink to defaults)
174 |
175 | User Environment:
176 | ~/.claude-prompts-mcp/
177 | ├── storage.db # SQLite database (all entities)
178 | ├── config.json # User preferences
179 | ├── backups/ # Automatic backups
180 | │ ├── storage-2025-01-19-143020.db
181 | │ └── storage-2025-01-18-091533.db
182 | └── exports/ # Optional JSON exports
183 | └── prompts-export-2025-01-19.json
184 | ```
185 |
186 | ### Hybrid Storage Strategy
187 |
188 | **Two-Tier Storage System**:
189 |
190 | 1. **Server Defaults** (JSON in Git)
191 | - Location: `/server/defaults/`
192 | - Format: JSON + Markdown (current structure)
193 | - Purpose: Shipped defaults, tracked in Git
194 | - Loaded: On server startup, imported to user DB if missing
195 | - Modification: Via Git commits, server updates
196 |
197 | 2. **User Data** (SQLite in Home Directory)
198 | - Location: `~/.claude-prompts-mcp/storage.db`
199 | - Format: SQLite with full versioning
200 | - Purpose: User customizations, overrides, new items
201 | - Loaded: Hot-reload from database
202 | - Modification: Via MCP tools with automatic history
203 |
204 | **Resolution Order**:
205 | ```
206 | User requests prompt "analyze_code"
207 | → Check user database first (customizations)
208 | → If not found, check server defaults
209 | → Merge defaults with user overrides
210 | ```
211 |
212 | ---
213 |
214 | ## Storage Abstraction Layer
215 |
216 | ### Interface Design
217 |
218 | ```typescript
219 | // server/src/storage/interfaces/IStorageAdapter.ts
220 |
221 | export interface IStorageAdapter {
222 | // ==================== LIFECYCLE ====================
223 | initialize(): Promise<void>;
224 | close(): Promise<void>;
225 | health(): Promise<StorageHealthStatus>;
226 |
227 | // ==================== PROMPTS ====================
228 | // Basic CRUD
229 | getPrompt(id: string): Promise<PromptData | null>;
230 | savePrompt(prompt: PromptData, reason?: string): Promise<void>;
231 | updatePrompt(id: string, updates: Partial<PromptData>, reason?: string): Promise<void>;
232 | deletePrompt(id: string, reason?: string): Promise<void>;
233 | listPrompts(filter?: FilterOptions): Promise<PromptData[]>;
234 |
235 | // Versioning
236 | getPromptHistory(id: string): Promise<PromptVersion[]>;
237 | getPromptVersion(id: string, version: number): Promise<PromptData | null>;
238 | rollbackPrompt(id: string, version: number): Promise<void>;
239 |
240 | // ==================== FRAMEWORKS ====================
241 | // Future: When framework dynamic creation is implemented
242 | getFramework(id: string): Promise<FrameworkDefinition | null>;
243 | saveFramework(framework: FrameworkDefinition, reason?: string): Promise<void>;
244 | listFrameworks(): Promise<FrameworkDefinition[]>;
245 | getFrameworkHistory(id: string): Promise<FrameworkVersion[]>;
246 |
247 | // ==================== GATES ====================
248 | // Future: When gate dynamic creation is implemented
249 | getGate(id: string): Promise<GateDefinition | null>;
250 | saveGate(gate: GateDefinition, reason?: string): Promise<void>;
251 | listGates(): Promise<GateDefinition[]>;
252 | getGateHistory(id: string): Promise<GateVersion[]>;
253 |
254 | // ==================== UTILITIES ====================
255 | // Backup & Export
256 | backup(reason?: string): Promise<string>; // Returns backup file path
257 | exportToJSON(path: string): Promise<void>;
258 | importFromJSON(path: string): Promise<ImportResult>;
259 |
260 | // Statistics
261 | getStats(): Promise<StorageStats>;
262 |
263 | // Search & Query
264 | search(query: string, options?: SearchOptions): Promise<SearchResult[]>;
265 | }
266 |
267 | // Supporting Types
268 | export interface StorageHealthStatus {
269 | healthy: boolean;
270 | storageType: 'json' | 'sqlite';
271 | location: string;
272 | size: number;
273 | itemCount: number;
274 | lastBackup?: number;
275 | errors?: string[];
276 | }
277 |
278 | export interface PromptVersion {
279 | version: number;
280 | prompt: PromptData;
281 | changedAt: number;
282 | changeReason?: string;
283 | changedBy?: string; // Future: multi-user support
284 | }
285 |
286 | export interface FilterOptions {
287 | category?: string;
288 | type?: 'prompt' | 'template' | 'chain' | 'workflow';
289 | tags?: string[];
290 | searchText?: string;
291 | createdAfter?: number;
292 | updatedAfter?: number;
293 | limit?: number;
294 | offset?: number;
295 | }
296 |
297 | export interface StorageStats {
298 | totalPrompts: number;
299 | totalFrameworks: number;
300 | totalGates: number;
301 | totalVersions: number;
302 | storageSize: number;
303 | oldestItem: number;
304 | newestItem: number;
305 | categories: { [key: string]: number };
306 | }
307 |
308 | export interface ImportResult {
309 | imported: number;
310 | updated: number;
311 | skipped: number;
312 | errors: Array<{ item: string; error: string }>;
313 | }
314 | ```
315 |
316 | ### Adapter Factory Pattern
317 |
318 | ```typescript
319 | // server/src/storage/index.ts
320 |
321 | import { IStorageAdapter } from './interfaces/IStorageAdapter.js';
322 | import { JSONStorageAdapter } from './adapters/json-adapter.js';
323 | import { SQLiteStorageAdapter } from './adapters/sqlite-adapter.js';
324 | import { Logger } from '../logging/index.js';
325 |
326 | export class StorageFactory {
327 | static async createAdapter(
328 | config: StorageConfig,
329 | logger: Logger
330 | ): Promise<IStorageAdapter> {
331 | const storageType = config.type || 'sqlite'; // Default to SQLite
332 |
333 | switch (storageType) {
334 | case 'json':
335 | logger.info('Initializing JSON storage adapter (legacy mode)');
336 | return new JSONStorageAdapter(config.jsonPath, logger);
337 |
338 | case 'sqlite':
339 | logger.info('Initializing SQLite storage adapter');
340 | const sqliteAdapter = new SQLiteStorageAdapter(
341 | config.sqlitePath || '~/.claude-prompts-mcp/storage.db',
342 | logger
343 | );
344 |
345 | // Load server defaults on first run
346 | if (config.defaultsPath && await sqliteAdapter.isEmpty()) {
347 | logger.info('First run detected, importing server defaults');
348 | await this.importDefaults(sqliteAdapter, config.defaultsPath);
349 | }
350 |
351 | return sqliteAdapter;
352 |
353 | default:
354 | throw new Error(`Unknown storage type: ${storageType}`);
355 | }
356 | }
357 |
358 | private static async importDefaults(
359 | adapter: IStorageAdapter,
360 | defaultsPath: string
361 | ): Promise<void> {
362 | // Import server defaults from JSON files
363 | const jsonAdapter = new JSONStorageAdapter(defaultsPath);
364 | await jsonAdapter.initialize();
365 |
366 | const prompts = await jsonAdapter.listPrompts();
367 | for (const prompt of prompts) {
368 | await adapter.savePrompt(prompt, 'Imported from server defaults');
369 | }
370 | }
371 | }
372 |
373 | export interface StorageConfig {
374 | type: 'json' | 'sqlite';
375 | jsonPath?: string; // For JSON adapter
376 | sqlitePath?: string; // For SQLite adapter
377 | defaultsPath?: string; // Server defaults location
378 | }
379 | ```
380 |
381 | ---
382 |
383 | ## SQLite Schema Design
384 |
385 | ### Database Schema
386 |
387 | ```sql
388 | -- ==================== CORE TABLES ====================
389 |
390 | -- Prompts table (current state)
391 | CREATE TABLE prompts (
392 | id TEXT PRIMARY KEY,
393 | name TEXT NOT NULL,
394 | description TEXT,
395 | category TEXT,
396 | content TEXT NOT NULL, -- Full prompt content (markdown)
397 | content_type TEXT DEFAULT 'markdown', -- 'markdown' | 'nunjucks'
398 |
399 | -- Metadata
400 | metadata TEXT, -- JSON: { arguments, systemMessage, tags, etc. }
401 |
402 | -- Versioning
403 | version INTEGER NOT NULL DEFAULT 1,
404 | created_at INTEGER NOT NULL, -- Unix timestamp
405 | updated_at INTEGER NOT NULL, -- Unix timestamp
406 |
407 | -- Source tracking
408 | source TEXT DEFAULT 'user', -- 'user' | 'server_default' | 'imported'
409 | is_default BOOLEAN DEFAULT 0, -- From server defaults?
410 |
411 | -- Status
412 | deleted BOOLEAN DEFAULT 0, -- Soft delete
413 | deleted_at INTEGER
414 | );
415 |
416 | -- Prompt history (all versions)
417 | CREATE TABLE prompt_history (
418 | prompt_id TEXT NOT NULL,
419 | version INTEGER NOT NULL,
420 |
421 | -- Snapshot of prompt at this version
422 | name TEXT NOT NULL,
423 | description TEXT,
424 | category TEXT,
425 | content TEXT NOT NULL,
426 | metadata TEXT,
427 |
428 | -- Change tracking
429 | changed_at INTEGER NOT NULL,
430 | change_reason TEXT,
431 | previous_version INTEGER,
432 |
433 | PRIMARY KEY (prompt_id, version),
434 | FOREIGN KEY (prompt_id) REFERENCES prompts(id) ON DELETE CASCADE
435 | );
436 |
437 | -- Frameworks table (future)
438 | CREATE TABLE frameworks (
439 | id TEXT PRIMARY KEY,
440 | name TEXT NOT NULL,
441 | description TEXT,
442 | methodology TEXT NOT NULL, -- JSON: Full methodology guide
443 |
444 | -- Metadata
445 | metadata TEXT,
446 |
447 | -- Versioning
448 | version INTEGER NOT NULL DEFAULT 1,
449 | created_at INTEGER NOT NULL,
450 | updated_at INTEGER NOT NULL,
451 |
452 | -- Source tracking
453 | source TEXT DEFAULT 'user',
454 | is_default BOOLEAN DEFAULT 0,
455 |
456 | -- Status
457 | deleted BOOLEAN DEFAULT 0,
458 | deleted_at INTEGER
459 | );
460 |
461 | -- Framework history
462 | CREATE TABLE framework_history (
463 | framework_id TEXT NOT NULL,
464 | version INTEGER NOT NULL,
465 |
466 | name TEXT NOT NULL,
467 | description TEXT,
468 | methodology TEXT NOT NULL,
469 | metadata TEXT,
470 |
471 | changed_at INTEGER NOT NULL,
472 | change_reason TEXT,
473 | previous_version INTEGER,
474 |
475 | PRIMARY KEY (framework_id, version),
476 | FOREIGN KEY (framework_id) REFERENCES frameworks(id) ON DELETE CASCADE
477 | );
478 |
479 | -- Gates table (future)
480 | CREATE TABLE gates (
481 | id TEXT PRIMARY KEY,
482 | name TEXT NOT NULL,
483 | description TEXT,
484 | gate_type TEXT NOT NULL, -- 'validation' | 'quality' | 'approval' | 'condition'
485 | definition TEXT NOT NULL, -- JSON: Full gate definition
486 |
487 | -- Metadata
488 | metadata TEXT,
489 |
490 | -- Versioning
491 | version INTEGER NOT NULL DEFAULT 1,
492 | created_at INTEGER NOT NULL,
493 | updated_at INTEGER NOT NULL,
494 |
495 | -- Source tracking
496 | source TEXT DEFAULT 'user',
497 | is_default BOOLEAN DEFAULT 0,
498 |
499 | -- Status
500 | deleted BOOLEAN DEFAULT 0,
501 | deleted_at INTEGER
502 | );
503 |
504 | -- Gate history
505 | CREATE TABLE gate_history (
506 | gate_id TEXT NOT NULL,
507 | version INTEGER NOT NULL,
508 |
509 | name TEXT NOT NULL,
510 | description TEXT,
511 | gate_type TEXT NOT NULL,
512 | definition TEXT NOT NULL,
513 | metadata TEXT,
514 |
515 | changed_at INTEGER NOT NULL,
516 | change_reason TEXT,
517 | previous_version INTEGER,
518 |
519 | PRIMARY KEY (gate_id, version),
520 | FOREIGN KEY (gate_id) REFERENCES gates(id) ON DELETE CASCADE
521 | );
522 |
523 | -- ==================== INDEXES ====================
524 |
525 | -- Prompts indexes
526 | CREATE INDEX idx_prompts_category ON prompts(category);
527 | CREATE INDEX idx_prompts_updated ON prompts(updated_at);
528 | CREATE INDEX idx_prompts_source ON prompts(source);
529 | CREATE INDEX idx_prompts_deleted ON prompts(deleted);
530 | CREATE INDEX idx_prompts_name ON prompts(name);
531 |
532 | -- History indexes
533 | CREATE INDEX idx_prompt_history_changed ON prompt_history(changed_at);
534 | CREATE INDEX idx_prompt_history_prompt ON prompt_history(prompt_id);
535 |
536 | -- Frameworks indexes
537 | CREATE INDEX idx_frameworks_updated ON frameworks(updated_at);
538 | CREATE INDEX idx_frameworks_source ON frameworks(source);
539 | CREATE INDEX idx_frameworks_deleted ON frameworks(deleted);
540 |
541 | -- Gates indexes
542 | CREATE INDEX idx_gates_type ON gates(gate_type);
543 | CREATE INDEX idx_gates_updated ON gates(updated_at);
544 | CREATE INDEX idx_gates_source ON gates(source);
545 | CREATE INDEX idx_gates_deleted ON gates(deleted);
546 |
547 | -- ==================== TRIGGERS ====================
548 |
549 | -- Automatic history archiving on update
550 | CREATE TRIGGER archive_prompt_on_update
551 | AFTER UPDATE ON prompts
552 | WHEN NEW.version > OLD.version
553 | BEGIN
554 | INSERT INTO prompt_history (
555 | prompt_id, version, name, description, category,
556 | content, metadata, changed_at, change_reason, previous_version
557 | ) VALUES (
558 | OLD.id, OLD.version, OLD.name, OLD.description, OLD.category,
559 | OLD.content, OLD.metadata, NEW.updated_at,
560 | 'Automatic archive on update', OLD.version
561 | );
562 | END;
563 |
564 | -- Update timestamp trigger
565 | CREATE TRIGGER update_prompt_timestamp
566 | AFTER UPDATE ON prompts
567 | BEGIN
568 | UPDATE prompts SET updated_at = strftime('%s', 'now') * 1000
569 | WHERE id = NEW.id;
570 | END;
571 |
572 | -- Similar triggers for frameworks and gates (future)
573 | ```
574 |
575 | ### Schema Versioning
576 |
577 | ```sql
578 | -- Schema version tracking
579 | CREATE TABLE schema_version (
580 | version INTEGER PRIMARY KEY,
581 | applied_at INTEGER NOT NULL,
582 | description TEXT
583 | );
584 |
585 | -- Initial version
586 | INSERT INTO schema_version (version, applied_at, description)
587 | VALUES (1, strftime('%s', 'now') * 1000, 'Initial schema with prompts, frameworks, gates and profile support');
588 | ```
589 |
590 | ### Profile Support Architecture
591 |
592 | **Design Decision**: Build profile support into schema from day one, enable UI features later.
593 |
594 | **Rationale**: At 500+ items, users need work/life/code separation. Adding profiles later requires painful schema migration. Adding now costs +1 day but avoids future migration and enables seamless profile features when needed.
595 |
596 | **Strategy**: Single database with `profile_id` column, not separate database files.
597 |
598 | #### Enhanced Schema with Profiles
599 |
600 | ```sql
601 | -- ==================== PROFILES MANAGEMENT ====================
602 |
603 | -- Profiles table
604 | CREATE TABLE profiles (
605 | id TEXT PRIMARY KEY,
606 | name TEXT NOT NULL,
607 | description TEXT,
608 | icon TEXT, -- Optional: emoji like 💼, 🏠, 💻
609 | color TEXT, -- Optional: hex color for UI
610 |
611 | settings TEXT, -- JSON: Profile-specific settings
612 |
613 | is_default BOOLEAN DEFAULT 0,
614 | is_active BOOLEAN DEFAULT 1,
615 |
616 | created_at INTEGER NOT NULL,
617 | updated_at INTEGER NOT NULL,
618 | last_used_at INTEGER
619 | );
620 |
621 | -- Active profile tracking (single row table)
622 | CREATE TABLE active_profile (
623 | id INTEGER PRIMARY KEY DEFAULT 1,
624 | profile_id TEXT NOT NULL,
625 | changed_at INTEGER NOT NULL,
626 | FOREIGN KEY (profile_id) REFERENCES profiles(id),
627 | CHECK (id = 1)
628 | );
629 |
630 | -- Profile-aware prompts (enhanced)
631 | CREATE TABLE prompts (
632 | id TEXT PRIMARY KEY,
633 | profile_id TEXT NOT NULL DEFAULT 'default', -- NEW: Profile association
634 | name TEXT NOT NULL,
635 | description TEXT,
636 | category TEXT,
637 | content TEXT NOT NULL,
638 | content_type TEXT DEFAULT 'markdown',
639 | metadata TEXT,
640 |
641 | version INTEGER NOT NULL DEFAULT 1,
642 | created_at INTEGER NOT NULL,
643 | updated_at INTEGER NOT NULL,
644 |
645 | source TEXT DEFAULT 'user',
646 | is_default BOOLEAN DEFAULT 0,
647 | original_profile_id TEXT, -- NEW: If shared from another profile
648 |
649 | deleted BOOLEAN DEFAULT 0,
650 | deleted_at INTEGER,
651 |
652 | FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
653 | );
654 |
655 | -- Enhanced indexes for profile-aware queries
656 | CREATE INDEX idx_prompts_profile ON prompts(profile_id);
657 | CREATE INDEX idx_prompts_profile_category ON prompts(profile_id, category);
658 | CREATE INDEX idx_prompts_profile_deleted ON prompts(profile_id, deleted);
659 |
660 | -- Profile-aware history
661 | CREATE INDEX idx_prompt_history_profile ON prompt_history(profile_id);
662 |
663 | -- Initial default profile
664 | INSERT INTO profiles (id, name, description, is_default, is_active, created_at, updated_at, last_used_at)
665 | VALUES (
666 | 'default',
667 | 'Default',
668 | 'Default profile for all prompts',
669 | 1,
670 | 1,
671 | strftime('%s', 'now') * 1000,
672 | strftime('%s', 'now') * 1000,
673 | strftime('%s', 'now') * 1000
674 | );
675 |
676 | INSERT INTO active_profile (profile_id, changed_at)
677 | VALUES ('default', strftime('%s', 'now') * 1000);
678 | ```
679 |
680 | #### Profile-Aware Adapter Implementation
681 |
682 | **Phase 1 Implementation** (default profile only):
683 | ```typescript
684 | export class SQLiteStorageAdapter implements IStorageAdapter {
685 | private activeProfileId: string = 'default';
686 |
687 | async initialize(): Promise<void> {
688 | this.initializeSchema(); // Creates profiles table + default profile
689 | this.activeProfileId = await this.loadActiveProfile(); // 'default'
690 | }
691 |
692 | async getPrompt(id: string): Promise<PromptData | null> {
693 | // Automatically scoped to active profile
694 | const row = this.db.prepare(`
695 | SELECT * FROM prompts
696 | WHERE id = ? AND profile_id = ? AND deleted = 0
697 | `).get(id, this.activeProfileId);
698 |
699 | return row ? this.rowToPromptData(row) : null;
700 | }
701 |
702 | async savePrompt(prompt: PromptData, reason?: string): Promise<void> {
703 | // Automatically associates with active profile
704 | this.db.prepare(`
705 | INSERT INTO prompts (
706 | id, profile_id, name, description, category, content, metadata,
707 | version, created_at, updated_at, source
708 | ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
709 | `).run(
710 | prompt.id,
711 | this.activeProfileId, // Always 'default' in Phase 1
712 | prompt.name,
713 | // ... other fields
714 | );
715 | }
716 | }
717 | ```
718 |
719 | **User Experience in Phase 1**:
720 | - Single "default" profile created automatically
721 | - All queries scoped to default profile (invisible to users)
722 | - Zero complexity, works exactly like no profiles
723 | - Foundation ready for future profile features
724 |
725 | **Server Defaults Handling**:
726 | - Server defaults marked with `is_default = 1`
727 | - Accessible from all profiles via SQL view or query filter
728 | - Not duplicated across profiles (efficient)
729 |
730 | ```sql
731 | -- View: Prompts for active profile + shared defaults
732 | CREATE VIEW profile_prompts_with_defaults AS
733 | SELECT p.*
734 | FROM prompts p
735 | WHERE p.profile_id = (SELECT profile_id FROM active_profile WHERE id = 1)
736 | OR p.is_default = 1;
737 | ```
738 |
739 | #### Future Profile Features (Phase 6+)
740 |
741 | **MCP Tool Commands** (deferred until needed):
742 | ```bash
743 | # Create profiles
744 | >>system_control action="create_profile" profile='{"id":"work","name":"Work Projects","icon":"💼"}'
745 |
746 | # List profiles
747 | >>system_control action="list_profiles"
748 | # Output: 💼 Work (127 prompts, active) | 🏠 Life (43 prompts) | 💻 Code (89 prompts)
749 |
750 | # Switch profiles
751 | >>system_control action="switch_profile" profile_id="work"
752 |
753 | # Cross-profile operations
754 | >>prompt_manager action="copy_to_profile" id="analyze_code" target_profile="work"
755 | >>prompt_manager action="share_prompt" id="research_template" profiles='["work","code"]'
756 | ```
757 |
758 | **Cost-Benefit**:
759 | - **Cost Now**: +1 day to Phase 1 (add profile tables, scoped queries)
760 | - **Cost Later**: 0 days (no migration needed)
761 | - **Benefit**: Seamless profile features when users hit 500+ items
762 | - **Risk**: None (hidden from users, default profile only)
763 |
764 | ---
765 |
766 | ## Migration Strategy
767 |
768 | ### Phase 1: Storage Abstraction Layer (Non-Breaking)
769 |
770 | **Goal**: Introduce adapter pattern without changing storage backend
771 |
772 | **Implementation**:
773 |
774 | 1. **Create interface and JSON adapter**:
775 | ```typescript
776 | // server/src/storage/adapters/json-adapter.ts
777 | export class JSONStorageAdapter implements IStorageAdapter {
778 | // Wraps existing file-based system
779 | // No changes to actual storage
780 | // Just provides IStorageAdapter interface
781 | }
782 | ```
783 |
784 | 2. **Update existing code to use adapter**:
785 | ```typescript
786 | // server/src/prompts/loader.ts
787 | // BEFORE:
788 | const prompts = await loadPromptsFromJSON(configPath);
789 |
790 | // AFTER:
791 | const storage = await StorageFactory.createAdapter(config, logger);
792 | const prompts = await storage.listPrompts();
793 | ```
794 |
795 | 3. **Update MCP tools**:
796 | ```typescript
797 | // server/src/mcp-tools/prompt-manager.ts
798 | // Replace direct file operations with storage adapter calls
799 | const prompt = await storage.getPrompt(id);
800 | await storage.savePrompt(updatedPrompt);
801 | ```
802 |
803 | **Testing**:
804 | - All existing tests should pass
805 | - No user-visible changes
806 | - Storage still JSON files
807 |
808 | **Duration**: 2-3 days
809 |
810 | ---
811 |
812 | ### Phase 2: SQLite Adapter Implementation
813 |
814 | **Goal**: Implement SQLite adapter with feature parity
815 |
816 | **Implementation**:
817 |
818 | 1. **SQLite adapter skeleton**:
819 | ```typescript
820 | // server/src/storage/adapters/sqlite-adapter.ts
821 | import Database from 'better-sqlite3';
822 |
823 | export class SQLiteStorageAdapter implements IStorageAdapter {
824 | private db: Database.Database;
825 |
826 | constructor(dbPath: string, logger: Logger) {
827 | this.db = new Database(dbPath);
828 | this.initializeSchema();
829 | }
830 |
831 | private initializeSchema(): void {
832 | // Execute schema.sql
833 | }
834 |
835 | async getPrompt(id: string): Promise<PromptData | null> {
836 | const row = this.db.prepare(
837 | 'SELECT * FROM prompts WHERE id = ? AND deleted = 0'
838 | ).get(id);
839 |
840 | return row ? this.rowToPromptData(row) : null;
841 | }
842 |
843 | async savePrompt(prompt: PromptData, reason?: string): Promise<void> {
844 | const exists = this.db.prepare(
845 | 'SELECT id FROM prompts WHERE id = ?'
846 | ).get(prompt.id);
847 |
848 | if (exists) {
849 | await this.updatePrompt(prompt.id, prompt, reason);
850 | } else {
851 | this.insertPrompt(prompt, reason);
852 | }
853 | }
854 |
855 | private insertPrompt(prompt: PromptData, reason?: string): void {
856 | this.db.prepare(`
857 | INSERT INTO prompts (
858 | id, name, description, category, content, metadata,
859 | version, created_at, updated_at, source
860 | ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
861 | `).run(
862 | prompt.id,
863 | prompt.name,
864 | prompt.description,
865 | prompt.category,
866 | prompt.content,
867 | JSON.stringify(prompt.metadata),
868 | 1,
869 | Date.now(),
870 | Date.now(),
871 | 'user'
872 | );
873 | }
874 |
875 | async updatePrompt(
876 | id: string,
877 | updates: Partial<PromptData>,
878 | reason?: string
879 | ): Promise<void> {
880 | // Archive current version first (manual, trigger handles auto)
881 | const current = await this.getPrompt(id);
882 | if (!current) throw new Error(`Prompt not found: ${id}`);
883 |
884 | this.db.prepare(`
885 | INSERT INTO prompt_history (
886 | prompt_id, version, name, description, category,
887 | content, metadata, changed_at, change_reason
888 | ) SELECT
889 | id, version, name, description, category,
890 | content, metadata, ?, ?
891 | FROM prompts WHERE id = ?
892 | `).run(Date.now(), reason || 'Update', id);
893 |
894 | // Update with incremented version
895 | this.db.prepare(`
896 | UPDATE prompts SET
897 | name = COALESCE(?, name),
898 | description = COALESCE(?, description),
899 | category = COALESCE(?, category),
900 | content = COALESCE(?, content),
901 | metadata = COALESCE(?, metadata),
902 | version = version + 1,
903 | updated_at = ?
904 | WHERE id = ?
905 | `).run(
906 | updates.name,
907 | updates.description,
908 | updates.category,
909 | updates.content,
910 | updates.metadata ? JSON.stringify(updates.metadata) : null,
911 | Date.now(),
912 | id
913 | );
914 | }
915 |
916 | async getPromptHistory(id: string): Promise<PromptVersion[]> {
917 | const rows = this.db.prepare(`
918 | SELECT * FROM prompt_history
919 | WHERE prompt_id = ?
920 | ORDER BY version DESC
921 | `).all(id);
922 |
923 | return rows.map(row => this.rowToPromptVersion(row));
924 | }
925 |
926 | async rollbackPrompt(id: string, version: number): Promise<void> {
927 | // Get the historical version
928 | const historical = this.db.prepare(`
929 | SELECT * FROM prompt_history
930 | WHERE prompt_id = ? AND version = ?
931 | `).get(id, version);
932 |
933 | if (!historical) {
934 | throw new Error(`Version ${version} not found for prompt ${id}`);
935 | }
936 |
937 | // Archive current before rollback
938 | await this.updatePrompt(id, {
939 | name: historical.name,
940 | description: historical.description,
941 | category: historical.category,
942 | content: historical.content,
943 | metadata: JSON.parse(historical.metadata)
944 | }, `Rollback to version ${version}`);
945 | }
946 |
947 | // ... other methods
948 | }
949 | ```
950 |
951 | 2. **Configuration support**:
952 | ```typescript
953 | // server/config.json
954 | {
955 | "storage": {
956 | "type": "sqlite", // "json" for legacy
957 | "sqlitePath": "~/.claude-prompts-mcp/storage.db",
958 | "defaultsPath": "./defaults/prompts",
959 | "backupOnStartup": true,
960 | "autoBackup": {
961 | "enabled": true,
962 | "intervalHours": 24,
963 | "keepCount": 7
964 | }
965 | }
966 | }
967 | ```
968 |
969 | **Testing**:
970 | - Unit tests for all adapter methods
971 | - Migration tests (JSON → SQLite)
972 | - Performance benchmarks
973 | - Concurrent access tests
974 |
975 | **Duration**: 3-4 days
976 |
977 | ---
978 |
979 | ### Phase 3: User Data Separation
980 |
981 | **Goal**: Move user data to home directory, keep server defaults in Git
982 |
983 | **Implementation**:
984 |
985 | 1. **Data location setup**:
986 | ```typescript
987 | // server/src/storage/paths.ts
988 | import * as path from 'path';
989 | import * as os from 'os';
990 | import * as fs from 'fs/promises';
991 |
992 | export class StoragePaths {
993 | static async getUserDataPath(): Promise<string> {
994 | const homeDir = os.homedir();
995 | const dataDir = path.join(homeDir, '.claude-prompts-mcp');
996 |
997 | // Ensure directory exists
998 | await fs.mkdir(dataDir, { recursive: true });
999 | await fs.mkdir(path.join(dataDir, 'backups'), { recursive: true });
1000 | await fs.mkdir(path.join(dataDir, 'exports'), { recursive: true });
1001 |
1002 | return dataDir;
1003 | }
1004 |
1005 | static async getStoragePath(): Promise<string> {
1006 | const dataDir = await this.getUserDataPath();
1007 | return path.join(dataDir, 'storage.db');
1008 | }
1009 |
1010 | static getServerDefaultsPath(): string {
1011 | // Server installation defaults
1012 | return path.join(__dirname, '../../defaults');
1013 | }
1014 | }
1015 | ```
1016 |
1017 | 2. **First-run initialization**:
1018 | ```typescript
1019 | // server/src/storage/initialization.ts
1020 | export class StorageInitializer {
1021 | static async initializeUserStorage(logger: Logger): Promise<void> {
1022 | const dbPath = await StoragePaths.getStoragePath();
1023 | const defaultsPath = StoragePaths.getServerDefaultsPath();
1024 |
1025 | // Check if database exists
1026 | const isFirstRun = !(await this.fileExists(dbPath));
1027 |
1028 | if (isFirstRun) {
1029 | logger.info('First run detected, creating user database');
1030 |
1031 | // Create SQLite database
1032 | const storage = new SQLiteStorageAdapter(dbPath, logger);
1033 | await storage.initialize();
1034 |
1035 | // Import server defaults
1036 | logger.info('Importing server defaults to user database');
1037 | await this.importDefaults(storage, defaultsPath, logger);
1038 |
1039 | logger.info('User storage initialized successfully');
1040 | }
1041 | }
1042 |
1043 | private static async importDefaults(
1044 | storage: IStorageAdapter,
1045 | defaultsPath: string,
1046 | logger: Logger
1047 | ): Promise<void> {
1048 | // Load defaults from JSON
1049 | const jsonAdapter = new JSONStorageAdapter(defaultsPath, logger);
1050 | await jsonAdapter.initialize();
1051 |
1052 | const prompts = await jsonAdapter.listPrompts();
1053 |
1054 | for (const prompt of prompts) {
1055 | await storage.savePrompt({
1056 | ...prompt,
1057 | source: 'server_default',
1058 | isDefault: true
1059 | }, 'Imported from server defaults');
1060 | }
1061 |
1062 | logger.info(`Imported ${prompts.length} default prompts`);
1063 | }
1064 | }
1065 | ```
1066 |
1067 | 3. **Update server startup**:
1068 | ```typescript
1069 | // server/src/runtime/application.ts
1070 | async startupPhase1_Foundation(): Promise<void> {
1071 | // ... existing code ...
1072 |
1073 | // Initialize user storage
1074 | await StorageInitializer.initializeUserStorage(this.logger);
1075 |
1076 | // Create storage adapter
1077 | const config: StorageConfig = {
1078 | type: 'sqlite',
1079 | sqlitePath: await StoragePaths.getStoragePath(),
1080 | defaultsPath: StoragePaths.getServerDefaultsPath()
1081 | };
1082 |
1083 | this.storage = await StorageFactory.createAdapter(config, this.logger);
1084 | await this.storage.initialize();
1085 | }
1086 | ```
1087 |
1088 | **Migration for Existing Users**:
1089 | ```typescript
1090 | // One-time migration script
1091 | export async function migrateExistingUserData(): Promise<void> {
1092 | const oldPath = path.join(__dirname, '../../prompts');
1093 | const newPath = await StoragePaths.getStoragePath();
1094 |
1095 | // Check if user has modified prompts in old location
1096 | const hasCustomPrompts = await detectUserModifications(oldPath);
1097 |
1098 | if (hasCustomPrompts) {
1099 | console.log('Migrating user prompts to ~/.claude-prompts-mcp/');
1100 |
1101 | // Create SQLite database
1102 | const storage = new SQLiteStorageAdapter(newPath, logger);
1103 | await storage.initialize();
1104 |
1105 | // Import existing prompts
1106 | const jsonAdapter = new JSONStorageAdapter(oldPath, logger);
1107 | const prompts = await jsonAdapter.listPrompts();
1108 |
1109 | for (const prompt of prompts) {
1110 | await storage.savePrompt(prompt, 'Migrated from server location');
1111 | }
1112 |
1113 | console.log('Migration complete!');
1114 | }
1115 | }
1116 | ```
1117 |
1118 | **Testing**:
1119 | - First-run experience
1120 | - Migration from old location
1121 | - Default import verification
1122 | - Path resolution cross-platform
1123 |
1124 | **Duration**: 2 days
1125 |
1126 | ---
1127 |
1128 | ### Phase 4: Versioning & History Features
1129 |
1130 | **Goal**: Expose history features through MCP tools
1131 |
1132 | **Implementation**:
1133 |
1134 | 1. **Enhanced MCP tool commands**:
1135 | ```typescript
1136 | // server/src/mcp-tools/prompt-manager.ts
1137 |
1138 | // Add new actions to PromptManagerAction enum
1139 | export enum PromptManagerAction {
1140 | // ... existing actions ...
1141 | HISTORY = 'history',
1142 | GET_VERSION = 'get_version',
1143 | ROLLBACK = 'rollback',
1144 | COMPARE_VERSIONS = 'compare_versions'
1145 | }
1146 |
1147 | // Implementation
1148 | async handleHistoryAction(id: string): Promise<ToolResponse> {
1149 | const history = await this.storage.getPromptHistory(id);
1150 |
1151 | if (history.length === 0) {
1152 | return {
1153 | success: false,
1154 | message: `No history found for prompt: ${id}`
1155 | };
1156 | }
1157 |
1158 | // Format for LLM consumption
1159 | const formatted = history.map(v => ({
1160 | version: v.version,
1161 | changedAt: new Date(v.changedAt).toISOString(),
1162 | reason: v.changeReason || 'No reason provided',
1163 | summary: this.summarizeChanges(v)
1164 | }));
1165 |
1166 | return {
1167 | success: true,
1168 | data: {
1169 | promptId: id,
1170 | currentVersion: history[0].version,
1171 | totalVersions: history.length,
1172 | history: formatted
1173 | },
1174 | message: `Retrieved ${history.length} versions for prompt: ${id}`
1175 | };
1176 | }
1177 |
1178 | async handleRollbackAction(
1179 | id: string,
1180 | version: number
1181 | ): Promise<ToolResponse> {
1182 | try {
1183 | // Get current state
1184 | const current = await this.storage.getPrompt(id);
1185 | if (!current) {
1186 | return { success: false, message: `Prompt not found: ${id}` };
1187 | }
1188 |
1189 | // Automatic backup before rollback
1190 | await this.storage.backup(`Before rollback of ${id} to v${version}`);
1191 |
1192 | // Perform rollback
1193 | await this.storage.rollbackPrompt(id, version);
1194 |
1195 | return {
1196 | success: true,
1197 | message: `Rolled back prompt "${id}" from v${current.version} to v${version}`,
1198 | data: {
1199 | previousVersion: current.version,
1200 | newVersion: version,
1201 | backupCreated: true
1202 | }
1203 | };
1204 | } catch (error) {
1205 | return {
1206 | success: false,
1207 | message: `Rollback failed: ${error.message}`
1208 | };
1209 | }
1210 | }
1211 | ```
1212 |
1213 | 2. **Usage examples for LLM**:
1214 | ```typescript
1215 | // Add to tool descriptions
1216 | const examples = `
1217 | View change history:
1218 | >>prompt_manager action="history" id="analyze_code"
1219 |
1220 | Output:
1221 | Version 5 (2025-01-19 14:30) - Updated argument descriptions
1222 | Version 4 (2025-01-18 10:15) - Added error handling guidance
1223 | Version 3 (2025-01-17 09:00) - Refactored structure
1224 | Version 2 (2025-01-16 15:45) - Initial refinements
1225 | Version 1 (2025-01-15 11:20) - Initial creation
1226 |
1227 | Rollback to previous version:
1228 | >>prompt_manager action="rollback" id="analyze_code" version="4"
1229 |
1230 | Output:
1231 | ✓ Rolled back "analyze_code" from v5 to v4
1232 | ✓ Automatic backup created
1233 |
1234 | Compare versions:
1235 | >>prompt_manager action="compare_versions" id="analyze_code" version1="4" version2="5"
1236 |
1237 | Output:
1238 | Changes in v5:
1239 | + Added argument: "error_handling_level"
1240 | ~ Modified: System message instructions
1241 | - Removed: Legacy compatibility note
1242 | `;
1243 | ```
1244 |
1245 | 3. **Automatic backup manager**:
1246 | ```typescript
1247 | // server/src/storage/versioning/backup-manager.ts
1248 | export class BackupManager {
1249 | private backupDir: string;
1250 | private maxBackups: number;
1251 |
1252 | async createBackup(
1253 | dbPath: string,
1254 | reason: string
1255 | ): Promise<string> {
1256 | const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
1257 | const backupName = `storage-${timestamp}.db`;
1258 | const backupPath = path.join(this.backupDir, backupName);
1259 |
1260 | // Copy database file
1261 | await fs.copyFile(dbPath, backupPath);
1262 |
1263 | // Write backup metadata
1264 | await fs.writeFile(
1265 | backupPath + '.meta.json',
1266 | JSON.stringify({
1267 | reason,
1268 | timestamp: Date.now(),
1269 | originalPath: dbPath
1270 | })
1271 | );
1272 |
1273 | // Cleanup old backups
1274 | await this.cleanupOldBackups();
1275 |
1276 | return backupPath;
1277 | }
1278 |
1279 | private async cleanupOldBackups(): Promise<void> {
1280 | const backups = await this.listBackups();
1281 |
1282 | if (backups.length > this.maxBackups) {
1283 | // Delete oldest backups
1284 | const toDelete = backups
1285 | .sort((a, b) => a.timestamp - b.timestamp)
1286 | .slice(0, backups.length - this.maxBackups);
1287 |
1288 | for (const backup of toDelete) {
1289 | await fs.unlink(backup.path);
1290 | await fs.unlink(backup.path + '.meta.json');
1291 | }
1292 | }
1293 | }
1294 | }
1295 | ```
1296 |
1297 | **Testing**:
1298 | - History retrieval accuracy
1299 | - Rollback correctness
1300 | - Backup creation and restoration
1301 | - Concurrent modification handling
1302 |
1303 | **Duration**: 2-3 days
1304 |
1305 | ---
1306 |
1307 | ## MCP Tool Integration
1308 |
1309 | ### Enhanced Prompt Manager Tool
1310 |
1311 | **New Schema Extensions**:
1312 | ```typescript
1313 | const promptManagerSchema = z.object({
1314 | action: z.enum([
1315 | 'create', 'update', 'delete', 'list', 'analyze',
1316 | 'history', 'get_version', 'rollback', 'compare_versions', // NEW
1317 | 'export', 'import', 'backup' // NEW
1318 | ]),
1319 | id: z.string().optional(),
1320 | version: z.number().optional(), // For version-specific operations
1321 | version1: z.number().optional(), // For comparisons
1322 | version2: z.number().optional(),
1323 | // ... existing fields ...
1324 | });
1325 | ```
1326 |
1327 | **Command Examples**:
1328 | ```bash
1329 | # View history
1330 | >>prompt_manager action="history" id="analyze_code"
1331 |
1332 | # Get specific version
1333 | >>prompt_manager action="get_version" id="analyze_code" version="3"
1334 |
1335 | # Rollback
1336 | >>prompt_manager action="rollback" id="analyze_code" version="4"
1337 |
1338 | # Compare versions
1339 | >>prompt_manager action="compare_versions" id="analyze_code" version1="4" version2="5"
1340 |
1341 | # Create backup
1342 | >>prompt_manager action="backup" reason="Before major refactor"
1343 |
1344 | # Export to JSON (for Git backup)
1345 | >>prompt_manager action="export" format="json" path="./my-prompts-backup/"
1346 |
1347 | # Import from JSON
1348 | >>prompt_manager action="import" path="./my-prompts-backup/prompts.json"
1349 | ```
1350 |
1351 | ### System Control Enhancements
1352 |
1353 | **Storage status command**:
1354 | ```typescript
1355 | // Add to system_control tool
1356 | async handleStorageStatus(): Promise<ToolResponse> {
1357 | const health = await this.storage.health();
1358 | const stats = await this.storage.getStats();
1359 |
1360 | return {
1361 | success: true,
1362 | data: {
1363 | storage: {
1364 | type: health.storageType,
1365 | location: health.location,
1366 | size: this.formatBytes(health.size),
1367 | healthy: health.healthy,
1368 | lastBackup: health.lastBackup
1369 | ? new Date(health.lastBackup).toISOString()
1370 | : 'Never'
1371 | },
1372 | statistics: {
1373 | totalPrompts: stats.totalPrompts,
1374 | totalFrameworks: stats.totalFrameworks,
1375 | totalGates: stats.totalGates,
1376 | totalVersions: stats.totalVersions,
1377 | categories: stats.categories
1378 | }
1379 | }
1380 | };
1381 | }
1382 | ```
1383 |
1384 | **Usage**:
1385 | ```bash
1386 | >>system_control action="storage_status"
1387 |
1388 | Output:
1389 | Storage Status:
1390 | Type: SQLite
1391 | Location: ~/.claude-prompts-mcp/storage.db
1392 | Size: 2.4 MB
1393 | Health: ✓ Healthy
1394 | Last Backup: 2025-01-19 14:30:00
1395 |
1396 | Statistics:
1397 | Prompts: 127 (892 versions)
1398 | Frameworks: 4 (12 versions)
1399 | Gates: 23 (67 versions)
1400 | Categories: 18
1401 | ```
1402 |
1403 | ---
1404 |
1405 | ## Implementation Phases
1406 |
1407 | ### Phase 1: Storage Abstraction (Week 1)
1408 |
1409 | **Deliverables**:
1410 | - ✅ `IStorageAdapter` interface defined
1411 | - ✅ `JSONStorageAdapter` wrapping existing system
1412 | - ✅ `StorageFactory` for adapter creation
1413 | - ✅ Updated MCP tools to use adapter
1414 | - ✅ All existing tests passing
1415 |
1416 | **Testing**:
1417 | - Unit tests for adapter interface
1418 | - Integration tests with existing system
1419 | - No regressions in functionality
1420 |
1421 | **Success Criteria**:
1422 | - Zero breaking changes
1423 | - All existing features work
1424 | - Adapter pattern validated
1425 |
1426 | ---
1427 |
1428 | ### Phase 2: SQLite Implementation (Week 2)
1429 |
1430 | **Deliverables**:
1431 | - ✅ `SQLiteStorageAdapter` fully implemented
1432 | - ✅ Schema creation with profile support (default profile only)
1433 | - ✅ Profile-aware CRUD operations (auto-scoped to default)
1434 | - ✅ Basic CRUD operations working
1435 | - ✅ Migration utility (JSON → SQLite)
1436 | - ✅ Configuration support
1437 |
1438 | **Profile Support Note**:
1439 | - Profile tables created, default profile initialized
1440 | - All queries automatically scoped to 'default' profile
1441 | - Zero user-visible changes (single profile mode)
1442 | - Foundation ready for future multi-profile features
1443 |
1444 | **Testing**:
1445 | - Unit tests for all SQL operations
1446 | - Profile-scoped query tests
1447 | - Migration tests
1448 | - Performance benchmarks (<10ms with profile filtering)
1449 | - Concurrent access tests
1450 |
1451 | **Success Criteria**:
1452 | - Feature parity with JSON adapter
1453 | - <10ms query performance (with profile_id filter)
1454 | - Successful migrations from JSON
1455 | - Profile foundation validated
1456 |
1457 | ---
1458 |
1459 | ### Phase 3: User Data Separation (Week 3)
1460 |
1461 | **Deliverables**:
1462 | - ✅ User data path management (`~/.claude-prompts-mcp/`)
1463 | - ✅ First-run initialization
1464 | - ✅ Server defaults import
1465 | - ✅ Migration script for existing users
1466 | - ✅ Documentation updated
1467 |
1468 | **Testing**:
1469 | - First-run experience
1470 | - Migration from old location
1471 | - Cross-platform path resolution
1472 | - Default import accuracy
1473 |
1474 | **Success Criteria**:
1475 | - User data isolated from server
1476 | - Server defaults tracked in Git
1477 | - Smooth upgrade path for existing users
1478 |
1479 | ---
1480 |
1481 | ### Phase 4: Versioning & History (Week 4)
1482 |
1483 | **Deliverables**:
1484 | - ✅ History tracking implemented
1485 | - ✅ Version retrieval methods
1486 | - ✅ Rollback functionality
1487 | - ✅ Automatic backup system
1488 | - ✅ MCP tool history commands
1489 |
1490 | **Testing**:
1491 | - History accuracy tests
1492 | - Rollback correctness
1493 | - Backup/restore verification
1494 | - Concurrent modification handling
1495 |
1496 | **Success Criteria**:
1497 | - Full change history for all entities
1498 | - Safe rollback with automatic backups
1499 | - Performance: <50ms for history queries
1500 |
1501 | ---
1502 |
1503 | ### Phase 5: Polish & Documentation (Week 5)
1504 |
1505 | **Deliverables**:
1506 | - ✅ Export/import features
1507 | - ✅ Storage statistics and health monitoring
1508 | - ✅ Comprehensive error handling
1509 | - ✅ User documentation
1510 | - ✅ Migration guide
1511 |
1512 | **Testing**:
1513 | - End-to-end testing
1514 | - Performance validation
1515 | - Edge case coverage
1516 | - User acceptance testing
1517 |
1518 | **Success Criteria**:
1519 | - All features documented
1520 | - Migration guide complete
1521 | - Performance targets met
1522 |
1523 | ---
1524 |
1525 | ## Testing Strategy
1526 |
1527 | ### Unit Tests
1528 |
1529 | **Storage Adapter Tests**:
1530 | ```typescript
1531 | // tests/storage/sqlite-adapter.test.ts
1532 | describe('SQLiteStorageAdapter', () => {
1533 | let adapter: SQLiteStorageAdapter;
1534 | let testDbPath: string;
1535 |
1536 | beforeEach(async () => {
1537 | testDbPath = path.join(tmpdir(), `test-${Date.now()}.db`);
1538 | adapter = new SQLiteStorageAdapter(testDbPath, logger);
1539 | await adapter.initialize();
1540 | });
1541 |
1542 | afterEach(async () => {
1543 | await adapter.close();
1544 | await fs.unlink(testDbPath);
1545 | });
1546 |
1547 | describe('getPrompt', () => {
1548 | it('should retrieve existing prompt', async () => {
1549 | const prompt = createTestPrompt();
1550 | await adapter.savePrompt(prompt);
1551 |
1552 | const retrieved = await adapter.getPrompt(prompt.id);
1553 |
1554 | expect(retrieved).toEqual(prompt);
1555 | });
1556 |
1557 | it('should return null for non-existent prompt', async () => {
1558 | const result = await adapter.getPrompt('non-existent');
1559 | expect(result).toBeNull();
1560 | });
1561 | });
1562 |
1563 | describe('savePrompt', () => {
1564 | it('should create new prompt', async () => {
1565 | const prompt = createTestPrompt();
1566 | await adapter.savePrompt(prompt, 'Initial creation');
1567 |
1568 | const retrieved = await adapter.getPrompt(prompt.id);
1569 | expect(retrieved).toBeTruthy();
1570 | expect(retrieved.version).toBe(1);
1571 | });
1572 |
1573 | it('should update existing prompt and increment version', async () => {
1574 | const prompt = createTestPrompt();
1575 | await adapter.savePrompt(prompt);
1576 |
1577 | const updated = { ...prompt, name: 'Updated Name' };
1578 | await adapter.savePrompt(updated, 'Name change');
1579 |
1580 | const retrieved = await adapter.getPrompt(prompt.id);
1581 | expect(retrieved.name).toBe('Updated Name');
1582 | expect(retrieved.version).toBe(2);
1583 | });
1584 | });
1585 |
1586 | describe('getPromptHistory', () => {
1587 | it('should track all versions', async () => {
1588 | const prompt = createTestPrompt();
1589 | await adapter.savePrompt(prompt);
1590 |
1591 | // Make 3 updates
1592 | for (let i = 0; i < 3; i++) {
1593 | await adapter.updatePrompt(
1594 | prompt.id,
1595 | { name: `Version ${i + 2}` },
1596 | `Update ${i + 1}`
1597 | );
1598 | }
1599 |
1600 | const history = await adapter.getPromptHistory(prompt.id);
1601 |
1602 | expect(history).toHaveLength(4); // Original + 3 updates
1603 | expect(history[0].version).toBe(4);
1604 | expect(history[3].version).toBe(1);
1605 | });
1606 | });
1607 |
1608 | describe('rollbackPrompt', () => {
1609 | it('should restore previous version', async () => {
1610 | const prompt = createTestPrompt();
1611 | await adapter.savePrompt(prompt);
1612 |
1613 | await adapter.updatePrompt(prompt.id, { name: 'V2' });
1614 | await adapter.updatePrompt(prompt.id, { name: 'V3' });
1615 |
1616 | await adapter.rollbackPrompt(prompt.id, 1);
1617 |
1618 | const current = await adapter.getPrompt(prompt.id);
1619 | expect(current.name).toBe(prompt.name);
1620 | });
1621 | });
1622 | });
1623 | ```
1624 |
1625 | ### Integration Tests
1626 |
1627 | **Migration Tests**:
1628 | ```typescript
1629 | // tests/storage/migration.test.ts
1630 | describe('JSON to SQLite Migration', () => {
1631 | it('should migrate all prompts correctly', async () => {
1632 | // Setup JSON data
1633 | const jsonPath = setupTestJSONStorage();
1634 | const jsonAdapter = new JSONStorageAdapter(jsonPath, logger);
1635 | const originalPrompts = await jsonAdapter.listPrompts();
1636 |
1637 | // Migrate to SQLite
1638 | const sqlitePath = path.join(tmpdir(), 'migrated.db');
1639 | const sqliteAdapter = new SQLiteStorageAdapter(sqlitePath, logger);
1640 |
1641 | for (const prompt of originalPrompts) {
1642 | await sqliteAdapter.savePrompt(prompt, 'Migrated from JSON');
1643 | }
1644 |
1645 | // Verify
1646 | const migratedPrompts = await sqliteAdapter.listPrompts();
1647 | expect(migratedPrompts).toHaveLength(originalPrompts.length);
1648 |
1649 | for (const original of originalPrompts) {
1650 | const migrated = await sqliteAdapter.getPrompt(original.id);
1651 | expect(migrated).toMatchObject(original);
1652 | }
1653 | });
1654 | });
1655 | ```
1656 |
1657 | ### Performance Tests
1658 |
1659 | **Benchmark Suite**:
1660 | ```typescript
1661 | // tests/storage/performance.test.ts
1662 | describe('Storage Performance', () => {
1663 | it('should retrieve prompts in <10ms', async () => {
1664 | const adapter = new SQLiteStorageAdapter(dbPath, logger);
1665 |
1666 | // Insert test data
1667 | for (let i = 0; i < 100; i++) {
1668 | await adapter.savePrompt(createTestPrompt(`prompt-${i}`));
1669 | }
1670 |
1671 | // Benchmark retrieval
1672 | const start = performance.now();
1673 | for (let i = 0; i < 100; i++) {
1674 | await adapter.getPrompt(`prompt-${i}`);
1675 | }
1676 | const duration = performance.now() - start;
1677 | const avgTime = duration / 100;
1678 |
1679 | expect(avgTime).toBeLessThan(10);
1680 | });
1681 |
1682 | it('should list prompts with filtering in <50ms', async () => {
1683 | // Insert 500 prompts across categories
1684 | for (let i = 0; i < 500; i++) {
1685 | await adapter.savePrompt({
1686 | ...createTestPrompt(`prompt-${i}`),
1687 | category: `category-${i % 10}`
1688 | });
1689 | }
1690 |
1691 | // Benchmark filtered listing
1692 | const start = performance.now();
1693 | const results = await adapter.listPrompts({
1694 | category: 'category-5',
1695 | searchText: 'test'
1696 | });
1697 | const duration = performance.now() - start;
1698 |
1699 | expect(duration).toBeLessThan(50);
1700 | expect(results.length).toBeGreaterThan(0);
1701 | });
1702 | });
1703 | ```
1704 |
1705 | ---
1706 |
1707 | ## Rollback & Recovery
1708 |
1709 | ### Automatic Backup System
1710 |
1711 | **Backup Triggers**:
1712 | 1. **Before major operations**:
1713 | - Rollback
1714 | - Bulk import
1715 | - Mass deletion
1716 |
1717 | 2. **Scheduled backups**:
1718 | - Daily at midnight
1719 | - Configurable interval
1720 |
1721 | 3. **Manual backups**:
1722 | - Via MCP tool command
1723 | - Before user-initiated migrations
1724 |
1725 | **Implementation**:
1726 | ```typescript
1727 | // server/src/storage/versioning/backup-manager.ts
1728 | export class AutomaticBackupSystem {
1729 | private backupInterval: NodeJS.Timer;
1730 |
1731 | startAutoBackup(adapter: IStorageAdapter, intervalHours: number): void {
1732 | this.backupInterval = setInterval(
1733 | async () => {
1734 | await adapter.backup('Scheduled automatic backup');
1735 | },
1736 | intervalHours * 60 * 60 * 1000
1737 | );
1738 | }
1739 |
1740 | async backupBeforeRiskyOperation(
1741 | adapter: IStorageAdapter,
1742 | operation: string
1743 | ): Promise<string> {
1744 | return await adapter.backup(`Before ${operation}`);
1745 | }
1746 | }
1747 | ```
1748 |
1749 | ### Recovery Procedures
1750 |
1751 | **Restore from Backup**:
1752 | ```typescript
1753 | async restoreFromBackup(backupPath: string): Promise<void> {
1754 | // Close current database connection
1755 | await this.storage.close();
1756 |
1757 | // Get current database path
1758 | const dbPath = await StoragePaths.getStoragePath();
1759 |
1760 | // Archive current database
1761 | const archivePath = `${dbPath}.before-restore-${Date.now()}`;
1762 | await fs.rename(dbPath, archivePath);
1763 |
1764 | // Restore from backup
1765 | await fs.copyFile(backupPath, dbPath);
1766 |
1767 | // Reinitialize storage
1768 | this.storage = new SQLiteStorageAdapter(dbPath, this.logger);
1769 | await this.storage.initialize();
1770 |
1771 | this.logger.info(`Restored from backup: ${backupPath}`);
1772 | }
1773 | ```
1774 |
1775 | **MCP Tool Command**:
1776 | ```bash
1777 | >>system_control action="list_backups"
1778 | # Shows available backups with timestamps and reasons
1779 |
1780 | >>system_control action="restore_backup" backup="storage-2025-01-19-143020.db"
1781 | # Restores from specified backup
1782 | ```
1783 |
1784 | ---
1785 |
1786 | ## Performance Considerations
1787 |
1788 | ### Query Optimization
1789 |
1790 | **Indexed Queries**:
1791 | ```sql
1792 | -- Fast category filtering (indexed)
1793 | SELECT * FROM prompts
1794 | WHERE category = ? AND deleted = 0;
1795 |
1796 | -- Fast date range queries (indexed)
1797 | SELECT * FROM prompts
1798 | WHERE updated_at > ? AND deleted = 0;
1799 |
1800 | -- Full-text search (consider FTS5 extension)
1801 | CREATE VIRTUAL TABLE prompts_fts USING fts5(
1802 | id, name, description, content,
1803 | content=prompts
1804 | );
1805 | ```
1806 |
1807 | **Prepared Statements**:
1808 | ```typescript
1809 | // Pre-compile frequently used queries
1810 | class SQLiteStorageAdapter {
1811 | private preparedQueries = {
1812 | getPrompt: null as Database.Statement | null,
1813 | savePrompt: null as Database.Statement | null,
1814 | listByCategory: null as Database.Statement | null
1815 | };
1816 |
1817 | initialize() {
1818 | // Compile once, reuse many times
1819 | this.preparedQueries.getPrompt = this.db.prepare(
1820 | 'SELECT * FROM prompts WHERE id = ? AND deleted = 0'
1821 | );
1822 |
1823 | this.preparedQueries.listByCategory = this.db.prepare(
1824 | 'SELECT * FROM prompts WHERE category = ? AND deleted = 0'
1825 | );
1826 | }
1827 |
1828 | async getPrompt(id: string): Promise<PromptData | null> {
1829 | // Use pre-compiled statement
1830 | const row = this.preparedQueries.getPrompt.get(id);
1831 | return row ? this.rowToPromptData(row) : null;
1832 | }
1833 | }
1834 | ```
1835 |
1836 | ### Transaction Batching
1837 |
1838 | **Bulk Operations**:
1839 | ```typescript
1840 | async bulkSave(prompts: PromptData[]): Promise<void> {
1841 | // Use transaction for atomic bulk insert
1842 | const transaction = this.db.transaction((prompts: PromptData[]) => {
1843 | for (const prompt of prompts) {
1844 | this.insertPrompt(prompt);
1845 | }
1846 | });
1847 |
1848 | transaction(prompts);
1849 | }
1850 | ```
1851 |
1852 | ### Memory Management
1853 |
1854 | **Streaming Large Results**:
1855 | ```typescript
1856 | async *streamPrompts(filter?: FilterOptions): AsyncGenerator<PromptData> {
1857 | const stmt = this.db.prepare('SELECT * FROM prompts WHERE deleted = 0');
1858 |
1859 | for (const row of stmt.iterate()) {
1860 | yield this.rowToPromptData(row);
1861 | }
1862 | }
1863 |
1864 | // Usage
1865 | for await (const prompt of adapter.streamPrompts()) {
1866 | // Process one at a time, memory efficient
1867 | }
1868 | ```
1869 |
1870 | ### Performance Targets
1871 |
1872 | **Benchmarks**:
1873 | - **Single prompt retrieval**: <10ms
1874 | - **List 100 prompts**: <50ms
1875 | - **History query (50 versions)**: <30ms
1876 | - **Rollback operation**: <100ms
1877 | - **Backup creation**: <500ms for 10MB database
1878 | - **Database size**: <1KB per prompt with history
1879 |
1880 | ---
1881 |
1882 | ## Future Enhancements
1883 |
1884 | ### Phase 6+: Advanced Features
1885 |
1886 | **1. Full-Text Search**:
1887 | ```sql
1888 | -- SQLite FTS5 extension
1889 | CREATE VIRTUAL TABLE prompts_fts USING fts5(
1890 | id UNINDEXED,
1891 | name,
1892 | description,
1893 | content,
1894 | tokenize='porter unicode61'
1895 | );
1896 |
1897 | -- Trigger to keep FTS in sync
1898 | CREATE TRIGGER prompts_fts_insert AFTER INSERT ON prompts BEGIN
1899 | INSERT INTO prompts_fts(id, name, description, content)
1900 | VALUES (new.id, new.name, new.description, new.content);
1901 | END;
1902 | ```
1903 |
1904 | **2. Change Diffs**:
1905 | ```typescript
1906 | async getPromptDiff(
1907 | id: string,
1908 | version1: number,
1909 | version2: number
1910 | ): Promise<PromptDiff> {
1911 | const v1 = await this.getPromptVersion(id, version1);
1912 | const v2 = await this.getPromptVersion(id, version2);
1913 |
1914 | return {
1915 | contentDiff: diffLines(v1.content, v2.content),
1916 | metadataDiff: diffJSON(v1.metadata, v2.metadata),
1917 | summary: summarizeChanges(v1, v2)
1918 | };
1919 | }
1920 | ```
1921 |
1922 | **3. Remote Sync** (Optional):
1923 | ```typescript
1924 | // Future: Sync user data across machines
1925 | interface IRemoteSyncProvider {
1926 | push(localDb: Database): Promise<void>;
1927 | pull(): Promise<Database>;
1928 | resolveConflicts(local: PromptData, remote: PromptData): PromptData;
1929 | }
1930 | ```
1931 |
1932 | **4. Profile Management Features (Phase 6+)**:
1933 |
1934 | When users accumulate 500+ items and need organization:
1935 |
1936 | ```typescript
1937 | // MCP Tool Interface
1938 | interface ProfileManagement {
1939 | // Profile CRUD
1940 | createProfile(profile: Profile): Promise<void>;
1941 | listProfiles(): Promise<Profile[]>;
1942 | switchProfile(profileId: string): Promise<void>;
1943 | deleteProfile(profileId: string): Promise<void>;
1944 |
1945 | // Cross-profile operations
1946 | copyPromptToProfile(promptId: string, targetProfileId: string): Promise<void>;
1947 | movePromptToProfile(promptId: string, targetProfileId: string): Promise<void>;
1948 | sharePromptBetweenProfiles(promptId: string, targetProfileIds: string[]): Promise<void>;
1949 |
1950 | // Profile analytics
1951 | getProfileStats(profileId: string): Promise<ProfileStats>;
1952 | }
1953 |
1954 | // Usage examples
1955 | >>system_control action="create_profile" profile='{"id":"work","name":"Work","icon":"💼"}'
1956 | >>system_control action="switch_profile" profile_id="work"
1957 | >>prompt_manager action="list" profile_id="*" // All profiles
1958 | >>prompt_manager action="copy_to_profile" id="template" target_profile="work"
1959 | ```
1960 |
1961 | **Implementation Ready**:
1962 | - Schema already supports profiles (Phase 2)
1963 | - Add MCP tool commands (2-3 days)
1964 | - Add profile switching UI (1-2 days)
1965 | - Zero migration needed (foundation built-in)
1966 |
1967 | **5. Framework & Gate Storage**:
1968 | - Once dynamic creation is implemented, use same storage pattern
1969 | - Same versioning and history capabilities
1970 | - Same MCP tool integration patterns
1971 | - Same profile support
1972 |
1973 | **6. Analytics & Insights**:
1974 | ```typescript
1975 | interface StorageAnalytics {
1976 | mostModifiedPrompts: Array<{ id: string; modifications: number }>;
1977 | recentActivity: Array<{ date: string; changes: number }>;
1978 | categoryDistribution: { [category: string]: number };
1979 | averageVersionsPerPrompt: number;
1980 | }
1981 | ```
1982 |
1983 | ---
1984 |
1985 | ## Appendix A: Configuration Reference
1986 |
1987 | ### Complete Configuration Example
1988 |
1989 | ```json
1990 | {
1991 | "storage": {
1992 | "type": "sqlite",
1993 | "sqlitePath": "~/.claude-prompts-mcp/storage.db",
1994 | "defaultsPath": "./defaults/prompts",
1995 |
1996 | "backup": {
1997 | "enabled": true,
1998 | "automatic": {
1999 | "intervalHours": 24,
2000 | "keepCount": 7
2001 | },
2002 | "beforeRiskyOperations": true
2003 | },
2004 |
2005 | "performance": {
2006 | "cacheSize": 100,
2007 | "prepareStatements": true,
2008 | "enableWAL": true
2009 | },
2010 |
2011 | "features": {
2012 | "fullTextSearch": true,
2013 | "autoVersioning": true,
2014 | "softDelete": true
2015 | }
2016 | }
2017 | }
2018 | ```
2019 |
2020 | ---
2021 |
2022 | ## Appendix B: Migration Checklist
2023 |
2024 | **Pre-Migration**:
2025 | - [ ] Backup current JSON files
2026 | - [ ] Test SQLite adapter with sample data
2027 | - [ ] Document current prompt count and structure
2028 | - [ ] Prepare rollback plan
2029 |
2030 | **Migration**:
2031 | - [ ] Create SQLite database in user directory
2032 | - [ ] Import server defaults
2033 | - [ ] Migrate existing user customizations
2034 | - [ ] Verify data integrity
2035 | - [ ] Test MCP tool functionality
2036 |
2037 | **Post-Migration**:
2038 | - [ ] Verify all prompts accessible
2039 | - [ ] Test history functionality
2040 | - [ ] Validate backup system
2041 | - [ ] Update documentation
2042 | - [ ] Monitor performance
2043 |
2044 | **Rollback Plan**:
2045 | - [ ] Keep JSON files for 30 days
2046 | - [ ] Provide export-to-JSON tool
2047 | - [ ] Document recovery procedure
2048 | - [ ] Test restoration process
2049 |
2050 | ---
2051 |
2052 | ## Appendix C: Performance Benchmarks
2053 |
2054 | **Target Performance** (SQLite vs JSON):
2055 |
2056 | | Operation | JSON | SQLite | Improvement |
2057 | |-----------|------|--------|-------------|
2058 | | Get single prompt | 5ms | 2ms | 2.5x faster |
2059 | | List 100 prompts | 80ms | 15ms | 5.3x faster |
2060 | | Filter by category | 100ms | 10ms | 10x faster |
2061 | | Get history | N/A | 25ms | New feature |
2062 | | Rollback | N/A | 80ms | New feature |
2063 | | Search text | 200ms | 30ms | 6.7x faster |
2064 |
2065 | **Memory Usage**:
2066 | - JSON: ~2MB for 100 prompts (full in-memory)
2067 | - SQLite: ~500KB baseline + query cache (streaming capable)
2068 |
2069 | ---
2070 |
2071 | ## Conclusion
2072 |
2073 | This implementation plan provides a comprehensive roadmap for migrating from JSON-based storage to SQLite while maintaining backwards compatibility, enabling versioning features, and separating user data from the server codebase.
2074 |
2075 | **Key Success Factors**:
2076 | 1. **Gradual migration** - No breaking changes, users opt-in
2077 | 2. **Storage abstraction** - Clean adapter pattern enables flexibility
2078 | 3. **User-centric design** - Data lives in user's home directory
2079 | 4. **Git-optional** - Users don't need to fork, but can export to JSON
2080 | 5. **Versioning built-in** - Automatic history tracking and rollback
2081 | 6. **Performance optimized** - SQLite provides significant speed improvements
2082 | 7. **Profile support** - Built-in from day one, enables work/life/code separation at scale
2083 | 8. **Future-ready** - Extensible to frameworks and gates
2084 |
2085 | **Timeline**: 5 weeks for full implementation
2086 | **Team Size**: 1-2 developers
2087 | **Risk Level**: Low (gradual migration, extensive testing, rollback capabilities)
2088 |
2089 | ---
2090 |
2091 | **Next Steps**:
2092 | 1. Review and approve this plan
2093 | 2. Begin Phase 1: Storage Abstraction Layer
2094 | 3. Set up testing infrastructure
2095 | 4. Prepare user communication materials
2096 | 5. Implement according to phased approach
2097 |
2098 | **Questions for Consideration**:
2099 | - Should we support remote sync in the future? (Multi-machine)
2100 | - Do we need multi-user support? (Collaboration)
2101 | - Should we add prompt templates marketplace? (Import from community)
2102 | - Performance targets acceptable? (Can optimize further if needed)
2103 |
```