Shell
/**
* Database operations for Reddit Research Agent
*/
import Database from 'better-sqlite3';
import { randomUUID } from 'crypto';
import { SCHEMA } from './schema.js';
import type { GoldmineScore } from '../types.js';
let db: Database.Database | null = null;
/**
* Initialize database connection
*/
export function initDatabase(dbPath = './reddit-research.db'): Database.Database {
if (db) return db;
db = new Database(dbPath);
db.pragma('journal_mode = WAL');
db.exec(SCHEMA);
return db;
}
/**
* Get database instance
*/
export function getDb(): Database.Database {
if (!db) {
throw new Error('Database not initialized. Call initDatabase() first.');
}
return db;
}
/**
* Close database connection
*/
export function closeDatabase(): void {
if (db) {
db.close();
db = null;
}
}
// ==================== Thread Operations ====================
/**
* Save scanned threads to database
*/
export function saveThreads(threads: GoldmineScore[]): number {
const database = getDb();
const stmt = database.prepare(`
INSERT OR REPLACE INTO threads
(id, url, subreddit, title, score, num_comments, upvote_ratio, goldmine_score, scanned_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`);
let count = 0;
const now = new Date().toISOString();
for (const thread of threads) {
const id = thread.url.split('/comments/')[1]?.split('/')[0] || randomUUID();
stmt.run(
id,
thread.url,
thread.subreddit,
thread.title,
thread.score,
thread.numComments,
thread.upvoteRatio,
thread.goldmineScore,
now
);
count++;
}
return count;
}
/**
* Get unanalyzed threads sorted by goldmine score
*/
export function getUnanalyzedThreads(limit = 10): Array<{ id: string; url: string; goldmineScore: number }> {
const database = getDb();
return database.prepare(`
SELECT id, url, goldmine_score as goldmineScore
FROM threads
WHERE analyzed = 0
ORDER BY goldmine_score DESC
LIMIT ?
`).all(limit) as Array<{ id: string; url: string; goldmineScore: number }>;
}
/**
* Mark thread as analyzed
*/
export function markThreadAnalyzed(threadId: string): void {
const database = getDb();
database.prepare('UPDATE threads SET analyzed = 1 WHERE id = ?').run(threadId);
}
/**
* Get thread by ID
*/
export function getThread(threadId: string): GoldmineScore | null {
const database = getDb();
const row = database.prepare('SELECT * FROM threads WHERE id = ?').get(threadId) as any;
if (!row) return null;
return {
url: row.url,
title: row.title,
score: row.score,
numComments: row.num_comments,
upvoteRatio: row.upvote_ratio,
goldmineScore: row.goldmine_score,
subreddit: row.subreddit,
};
}
// ==================== Insight Operations ====================
/**
* Save market insights
*/
export function saveInsight(
threadId: string,
type: string,
content: string,
metadata?: Record<string, unknown>,
confidence?: string
): string {
const database = getDb();
const id = randomUUID();
database.prepare(`
INSERT INTO insights (id, thread_id, type, content, metadata, confidence, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?)
`).run(
id,
threadId,
type,
content,
metadata ? JSON.stringify(metadata) : null,
confidence || null,
new Date().toISOString()
);
return id;
}
/**
* Get insights by type
*/
export function getInsightsByType(type: string, limit = 50): Array<{
id: string;
threadId: string;
content: string;
metadata: Record<string, unknown> | null;
confidence: string | null;
createdAt: string;
}> {
const database = getDb();
const rows = database.prepare(`
SELECT id, thread_id as threadId, content, metadata, confidence, created_at as createdAt
FROM insights
WHERE type = ?
ORDER BY created_at DESC
LIMIT ?
`).all(type, limit) as any[];
return rows.map(row => ({
...row,
metadata: row.metadata ? JSON.parse(row.metadata) : null,
}));
}
/**
* Get all insights for a thread
*/
export function getThreadInsights(threadId: string): Array<{
type: string;
content: string;
metadata: Record<string, unknown> | null;
}> {
const database = getDb();
const rows = database.prepare(`
SELECT type, content, metadata
FROM insights
WHERE thread_id = ?
ORDER BY type, created_at
`).all(threadId) as any[];
return rows.map(row => ({
type: row.type,
content: row.content,
metadata: row.metadata ? JSON.parse(row.metadata) : null,
}));
}
// ==================== Agent Run Operations ====================
/**
* Start a new agent run
*/
export function startAgentRun(): number {
const database = getDb();
const result = database.prepare(`
INSERT INTO agent_runs (started_at, status)
VALUES (?, 'running')
`).run(new Date().toISOString());
return result.lastInsertRowid as number;
}
/**
* Complete an agent run
*/
export function completeAgentRun(
runId: number,
stats: { threadsScanned: number; insightsFound: number; costUsd: number }
): void {
const database = getDb();
database.prepare(`
UPDATE agent_runs
SET completed_at = ?, status = 'completed',
threads_scanned = ?, insights_found = ?, cost_usd = ?
WHERE id = ?
`).run(
new Date().toISOString(),
stats.threadsScanned,
stats.insightsFound,
stats.costUsd,
runId
);
}
/**
* Fail an agent run
*/
export function failAgentRun(runId: number, error: string): void {
const database = getDb();
database.prepare(`
UPDATE agent_runs
SET completed_at = ?, status = 'failed', error_message = ?
WHERE id = ?
`).run(new Date().toISOString(), error, runId);
}
/**
* Get recent agent runs
*/
export function getRecentRuns(limit = 10): Array<{
id: number;
startedAt: string;
completedAt: string | null;
status: string;
threadsScanned: number;
insightsFound: number;
costUsd: number;
}> {
const database = getDb();
return database.prepare(`
SELECT id, started_at as startedAt, completed_at as completedAt,
status, threads_scanned as threadsScanned,
insights_found as insightsFound, cost_usd as costUsd
FROM agent_runs
ORDER BY id DESC
LIMIT ?
`).all(limit) as any[];
}
// ==================== Tracked Sources ====================
/**
* Add a subreddit to track
*/
export function addTrackedSource(subreddit: string, query?: string): void {
const database = getDb();
database.prepare(`
INSERT OR IGNORE INTO tracked_sources (subreddit, query, created_at)
VALUES (?, ?, ?)
`).run(subreddit, query || null, new Date().toISOString());
}
/**
* Get all tracked sources
*/
export function getTrackedSources(): Array<{
id: number;
subreddit: string;
query: string | null;
enabled: boolean;
lastScanned: string | null;
}> {
const database = getDb();
return database.prepare(`
SELECT id, subreddit, query, enabled, last_scanned as lastScanned
FROM tracked_sources
WHERE enabled = 1
`).all() as any[];
}
/**
* Update last scanned time for a source
*/
export function updateSourceScanned(subreddit: string, query?: string): void {
const database = getDb();
if (query) {
database.prepare(`
UPDATE tracked_sources SET last_scanned = ? WHERE subreddit = ? AND query = ?
`).run(new Date().toISOString(), subreddit, query);
} else {
database.prepare(`
UPDATE tracked_sources SET last_scanned = ? WHERE subreddit = ? AND query IS NULL
`).run(new Date().toISOString(), subreddit);
}
}
// ==================== Stats ====================
/**
* Get database statistics
*/
export function getStats(): {
totalThreads: number;
analyzedThreads: number;
totalInsights: number;
insightsByType: Record<string, number>;
topSubreddits: Array<{ subreddit: string; count: number }>;
} {
const database = getDb();
const totalThreads = (database.prepare('SELECT COUNT(*) as count FROM threads').get() as any).count;
const analyzedThreads = (database.prepare('SELECT COUNT(*) as count FROM threads WHERE analyzed = 1').get() as any).count;
const totalInsights = (database.prepare('SELECT COUNT(*) as count FROM insights').get() as any).count;
const insightsByTypeRows = database.prepare(`
SELECT type, COUNT(*) as count FROM insights GROUP BY type
`).all() as any[];
const insightsByType: Record<string, number> = {};
for (const row of insightsByTypeRows) {
insightsByType[row.type] = row.count;
}
const topSubreddits = database.prepare(`
SELECT subreddit, COUNT(*) as count
FROM threads
GROUP BY subreddit
ORDER BY count DESC
LIMIT 10
`).all() as any[];
return {
totalThreads,
analyzedThreads,
totalInsights,
insightsByType,
topSubreddits,
};
}