db-init.js•16.8 kB
/**
* Database Initialization Module
* Sets up SQLite database for media history storage
*/
const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs').promises;
const DB_PATH = path.join(__dirname, 'data', 'media-history.db');
async function initializeDatabase() {
// Ensure data directory exists
const dataDir = path.dirname(DB_PATH);
try {
await fs.mkdir(dataDir, { recursive: true });
console.log(`✅ Data directory ensured: ${dataDir}`);
} catch (error) {
console.error('Failed to create data directory:', error);
throw error;
}
return new Promise((resolve, reject) => {
const db = new sqlite3.Database(DB_PATH, (err) => {
if (err) {
console.error('Failed to open database:', err);
reject(err);
return;
}
console.log(`✅ Connected to SQLite database: ${DB_PATH}`);
});
// Enable foreign keys
db.run('PRAGMA foreign_keys = ON');
// Create tables
db.serialize(() => {
// Media items table
db.run(`
CREATE TABLE IF NOT EXISTS media_items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
type TEXT NOT NULL CHECK(type IN ('screenshot', 'recording', 'browser')),
title TEXT,
description TEXT,
path TEXT NOT NULL,
thumbnail_path TEXT,
file_size INTEGER,
duration INTEGER,
width INTEGER,
height INTEGER,
format TEXT,
project TEXT,
session_id TEXT,
context TEXT,
metadata TEXT,
annotations TEXT,
tags TEXT,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`, (err) => {
if (err) console.error('Error creating media_items table:', err);
else console.log('✅ media_items table ready');
});
// Sessions table
db.run(`
CREATE TABLE IF NOT EXISTS sessions (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP,
end_time DATETIME,
duration INTEGER,
screenshot_count INTEGER DEFAULT 0,
recording_count INTEGER DEFAULT 0,
browser_capture_count INTEGER DEFAULT 0,
total_size INTEGER DEFAULT 0,
project TEXT,
tags TEXT,
metadata TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`, (err) => {
if (err) console.error('Error creating sessions table:', err);
else console.log('✅ sessions table ready');
});
// Annotations table
db.run(`
CREATE TABLE IF NOT EXISTS annotations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
media_id INTEGER NOT NULL,
type TEXT NOT NULL,
data TEXT NOT NULL,
position TEXT,
author TEXT,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (media_id) REFERENCES media_items (id) ON DELETE CASCADE
)
`, (err) => {
if (err) console.error('Error creating annotations table:', err);
else console.log('✅ annotations table ready');
});
// Voice commands table
db.run(`
CREATE TABLE IF NOT EXISTS voice_commands (
id INTEGER PRIMARY KEY AUTOINCREMENT,
command TEXT NOT NULL,
response TEXT,
tool_used TEXT,
context TEXT,
success BOOLEAN DEFAULT 1,
duration_ms INTEGER,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)
`, (err) => {
if (err) console.error('Error creating voice_commands table:', err);
else console.log('✅ voice_commands table ready');
});
// Analytics table
db.run(`
CREATE TABLE IF NOT EXISTS analytics (
id INTEGER PRIMARY KEY AUTOINCREMENT,
date DATE NOT NULL UNIQUE,
screenshot_count INTEGER DEFAULT 0,
recording_count INTEGER DEFAULT 0,
browser_capture_count INTEGER DEFAULT 0,
voice_command_count INTEGER DEFAULT 0,
total_size INTEGER DEFAULT 0,
avg_response_time_ms INTEGER,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`, (err) => {
if (err) console.error('Error creating analytics table:', err);
else console.log('✅ analytics table ready');
});
// Create indexes for better performance
db.run(`
CREATE INDEX IF NOT EXISTS idx_media_timestamp
ON media_items(timestamp DESC)
`);
db.run(`
CREATE INDEX IF NOT EXISTS idx_media_type
ON media_items(type)
`);
db.run(`
CREATE INDEX IF NOT EXISTS idx_media_project
ON media_items(project)
`);
db.run(`
CREATE INDEX IF NOT EXISTS idx_media_session
ON media_items(session_id)
`);
db.run(`
CREATE INDEX IF NOT EXISTS idx_sessions_project
ON sessions(project)
`);
db.run(`
CREATE INDEX IF NOT EXISTS idx_voice_timestamp
ON voice_commands(timestamp DESC)
`);
db.run(`
CREATE INDEX IF NOT EXISTS idx_analytics_date
ON analytics(date DESC)
`, (err) => {
if (err) {
console.error('Error creating indexes:', err);
reject(err);
} else {
console.log('✅ Database indexes created');
resolve(db);
}
});
});
});
}
// Database helper functions
class MediaDatabase {
constructor(db) {
this.db = db;
}
// Insert media item
async insertMedia(mediaData) {
return new Promise((resolve, reject) => {
const {
type, title, description, path, thumbnailPath,
fileSize, duration, width, height, format,
project, sessionId, context, metadata, tags
} = mediaData;
const sql = `
INSERT INTO media_items (
type, title, description, path, thumbnail_path,
file_size, duration, width, height, format,
project, session_id, context, metadata, tags
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`;
this.db.run(sql, [
type, title, description, path, thumbnailPath,
fileSize, duration, width, height, format,
project, sessionId,
JSON.stringify(context || {}),
JSON.stringify(metadata || {}),
JSON.stringify(tags || [])
], function(err) {
if (err) {
console.error('Error inserting media:', err);
reject(err);
} else {
resolve(this.lastID);
}
});
});
}
// Get media items with filters
async getMediaItems(filters = {}) {
return new Promise((resolve, reject) => {
let sql = 'SELECT * FROM media_items WHERE 1=1';
const params = [];
if (filters.type && filters.type !== 'all') {
sql += ' AND type = ?';
params.push(filters.type);
}
if (filters.project && filters.project !== 'all') {
sql += ' AND project = ?';
params.push(filters.project);
}
if (filters.sessionId) {
sql += ' AND session_id = ?';
params.push(filters.sessionId);
}
if (filters.date) {
sql += ' AND DATE(timestamp) = DATE(?)';
params.push(filters.date);
}
if (filters.search) {
sql += ' AND (title LIKE ? OR description LIKE ? OR tags LIKE ?)';
const searchTerm = `%${filters.search}%`;
params.push(searchTerm, searchTerm, searchTerm);
}
sql += ' ORDER BY timestamp DESC';
if (filters.limit) {
sql += ' LIMIT ?';
params.push(filters.limit);
}
this.db.all(sql, params, (err, rows) => {
if (err) {
console.error('Error fetching media:', err);
reject(err);
} else {
// Parse JSON fields
const items = rows.map(row => ({
...row,
context: JSON.parse(row.context || '{}'),
metadata: JSON.parse(row.metadata || '{}'),
tags: JSON.parse(row.tags || '[]'),
annotations: JSON.parse(row.annotations || 'null')
}));
resolve(items);
}
});
});
}
// Create or update session
async upsertSession(sessionData) {
return new Promise((resolve, reject) => {
const { id, name, description, project, tags, metadata } = sessionData;
const sql = `
INSERT INTO sessions (id, name, description, project, tags, metadata)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
name = excluded.name,
description = excluded.description,
project = excluded.project,
tags = excluded.tags,
metadata = excluded.metadata,
updated_at = CURRENT_TIMESTAMP
`;
this.db.run(sql, [
id, name, description, project,
JSON.stringify(tags || []),
JSON.stringify(metadata || {})
], (err) => {
if (err) {
console.error('Error upserting session:', err);
reject(err);
} else {
resolve(id);
}
});
});
}
// Update session stats
async updateSessionStats(sessionId, type) {
return new Promise((resolve, reject) => {
const columnMap = {
'screenshot': 'screenshot_count',
'recording': 'recording_count',
'browser': 'browser_capture_count'
};
const column = columnMap[type];
if (!column) {
reject(new Error(`Invalid media type: ${type}`));
return;
}
const sql = `
UPDATE sessions
SET ${column} = ${column} + 1
WHERE id = ?
`;
this.db.run(sql, [sessionId], (err) => {
if (err) {
console.error('Error updating session stats:', err);
reject(err);
} else {
resolve();
}
});
});
}
// Get sessions
async getSessions(filters = {}) {
return new Promise((resolve, reject) => {
let sql = 'SELECT * FROM sessions WHERE 1=1';
const params = [];
if (filters.project) {
sql += ' AND project = ?';
params.push(filters.project);
}
sql += ' ORDER BY start_time DESC';
this.db.all(sql, params, (err, rows) => {
if (err) {
console.error('Error fetching sessions:', err);
reject(err);
} else {
const sessions = rows.map(row => ({
...row,
tags: JSON.parse(row.tags || '[]'),
metadata: JSON.parse(row.metadata || '{}')
}));
resolve(sessions);
}
});
});
}
// Record voice command
async recordVoiceCommand(commandData) {
return new Promise((resolve, reject) => {
const { command, response, toolUsed, context, success, durationMs } = commandData;
const sql = `
INSERT INTO voice_commands (
command, response, tool_used, context, success, duration_ms
) VALUES (?, ?, ?, ?, ?, ?)
`;
this.db.run(sql, [
command, response, toolUsed,
JSON.stringify(context || {}),
success ? 1 : 0,
durationMs
], function(err) {
if (err) {
console.error('Error recording voice command:', err);
reject(err);
} else {
resolve(this.lastID);
}
});
});
}
// Get analytics
async getAnalytics(days = 30) {
return new Promise((resolve, reject) => {
const sql = `
SELECT
COUNT(CASE WHEN type = 'screenshot' THEN 1 END) as screenshot_count,
COUNT(CASE WHEN type = 'recording' THEN 1 END) as recording_count,
COUNT(CASE WHEN type = 'browser' THEN 1 END) as browser_count,
SUM(file_size) as total_size,
AVG(CASE WHEN type = 'recording' THEN duration END) as avg_recording_duration,
COUNT(*) as total_items
FROM media_items
WHERE timestamp >= datetime('now', '-' || ? || ' days')
`;
this.db.get(sql, [days], (err, row) => {
if (err) {
console.error('Error fetching analytics:', err);
reject(err);
} else {
resolve(row);
}
});
});
}
// Clean up old items
async cleanupOldItems(daysToKeep = 30) {
return new Promise((resolve, reject) => {
const sql = `
DELETE FROM media_items
WHERE timestamp < datetime('now', '-' || ? || ' days')
`;
this.db.run(sql, [daysToKeep], function(err) {
if (err) {
console.error('Error cleaning up old items:', err);
reject(err);
} else {
console.log(`Cleaned up ${this.changes} old media items`);
resolve(this.changes);
}
});
});
}
// Close database connection
close() {
return new Promise((resolve, reject) => {
this.db.close((err) => {
if (err) {
console.error('Error closing database:', err);
reject(err);
} else {
console.log('Database connection closed');
resolve();
}
});
});
}
}
// Export initialization function and database class
module.exports = {
initializeDatabase,
MediaDatabase
};
// Run initialization if called directly
if (require.main === module) {
initializeDatabase()
.then(db => {
console.log('✅ Database initialization complete');
db.close();
})
.catch(err => {
console.error('❌ Database initialization failed:', err);
process.exit(1);
});
}