🎬 Open-source, self-hosted AI agent that runs a YouTube channel end to end: researches topics, writes scripts, generates narration, visuals and video, optimizes SEO, and publishes after your approval, then learns from analytics. Works with Gemini, OpenAI, OpenRouter, DeepSeek and more.
JavaScript ai-agentsautomationcontent-automationcontent-creationcontent-strategygoogle-geminijavascriptnodejsopenaiself-hostedseo-optimizationsocial-media-automationthumbnail-generatorvideo-generationyoutube-apiyoutube-automationyoutube-botyoutube-channelyoutube-shortsyoutube-uploader
const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs').promises;
const { Logger } = require('../utils/logger');
class Database {
constructor() {
this.dbPath = path.join(__dirname, '..', 'data', 'youtube_automation.db');
this.db = null;
this.logger = new Logger('Database');
}
async initialize() {
try {
this.logger.info('Initializing database...');
// Ensure data directory exists
await fs.mkdir(path.dirname(this.dbPath), { recursive: true });
// Connect to database
this.db = new sqlite3.Database(this.dbPath);
// Create tables
await this.createTables();
this.logger.success('Database initialized successfully');
return true;
} catch (error) {
this.logger.error('Failed to initialize database:', error);
throw error;
}
}
async createTables() {
const tables = [
// Content Strategy
`CREATE TABLE IF NOT EXISTS content_strategies (
id TEXT PRIMARY KEY,
topic TEXT NOT NULL,
angle TEXT NOT NULL,
target_audience TEXT NOT NULL,
content_type TEXT NOT NULL,
keywords TEXT NOT NULL,
estimated_views INTEGER DEFAULT 0,
best_publish_time TEXT,
competitor_analysis TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
// Scripts
`CREATE TABLE IF NOT EXISTS scripts (
id TEXT PRIMARY KEY,
strategy_id TEXT,
title TEXT NOT NULL,
hook TEXT,
introduction TEXT,
main_content TEXT NOT NULL,
conclusion TEXT,
call_to_action TEXT,
full_script TEXT,
duration TEXT,
tone TEXT,
pacing TEXT,
keywords TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (strategy_id) REFERENCES content_strategies(id)
)`,
// Thumbnails
`CREATE TABLE IF NOT EXISTS thumbnails (
id TEXT PRIMARY KEY,
script_id TEXT,
path TEXT NOT NULL,
concept TEXT,
prompt TEXT,
dimensions TEXT,
file_size INTEGER,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (script_id) REFERENCES scripts(id)
)`,
// SEO Data
`CREATE TABLE IF NOT EXISTS seo_data (
id TEXT PRIMARY KEY,
script_id TEXT,
title TEXT NOT NULL,
description TEXT NOT NULL,
tags TEXT NOT NULL,
hashtags TEXT,
chapters TEXT,
end_screen TEXT,
seo_score INTEGER DEFAULT 0,
metadata TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (script_id) REFERENCES scripts(id)
)`,
// Production Data
`CREATE TABLE IF NOT EXISTS productions (
id TEXT PRIMARY KEY,
strategy_id TEXT,
script_id TEXT,
thumbnail_id TEXT,
seo_id TEXT,
status TEXT DEFAULT 'processing',
assets TEXT,
timeline TEXT,
scheduled_publish_time TEXT,
priority INTEGER DEFAULT 50,
estimated_duration TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (strategy_id) REFERENCES content_strategies(id),
FOREIGN KEY (script_id) REFERENCES scripts(id),
FOREIGN KEY (thumbnail_id) REFERENCES thumbnails(id),
FOREIGN KEY (seo_id) REFERENCES seo_data(id)
)`,
// Publishing Schedule
`CREATE TABLE IF NOT EXISTS publish_schedule (
id TEXT PRIMARY KEY,
production_id TEXT NOT NULL,
title TEXT NOT NULL,
publish_time TEXT NOT NULL,
status TEXT DEFAULT 'scheduled',
priority INTEGER DEFAULT 50,
metadata TEXT,
youtube_id TEXT,
youtube_url TEXT,
published_at TEXT,
error_message TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (production_id) REFERENCES productions(id)
)`,
// Analytics Reports
`CREATE TABLE IF NOT EXISTS analytics_reports (
id TEXT PRIMARY KEY,
video_id TEXT NOT NULL,
youtube_id TEXT,
video_details TEXT,
analytics_data TEXT,
thumbnail_metrics TEXT,
seo_metrics TEXT,
insights TEXT,
performance_score INTEGER DEFAULT 0,
performance_grade TEXT,
analyzed_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
// Evidence-backed channel learning
`CREATE TABLE IF NOT EXISTS performance_snapshots (
id TEXT PRIMARY KEY,
video_id TEXT NOT NULL,
production_id TEXT,
measurement_window TEXT NOT NULL,
published_at TEXT,
metrics TEXT NOT NULL,
content_attributes TEXT NOT NULL,
baseline TEXT,
deltas TEXT,
confidence TEXT DEFAULT 'low',
simulated INTEGER DEFAULT 0,
measured_at TEXT DEFAULT CURRENT_TIMESTAMP,
UNIQUE(video_id, measurement_window)
)`,
`CREATE TABLE IF NOT EXISTS learning_recommendations (
id TEXT PRIMARY KEY,
fingerprint TEXT NOT NULL UNIQUE,
category TEXT NOT NULL,
title TEXT NOT NULL,
rationale TEXT NOT NULL,
evidence TEXT NOT NULL,
proposed_change TEXT NOT NULL,
confidence TEXT DEFAULT 'low',
status TEXT DEFAULT 'pending',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
reviewed_at TEXT
)`,
`CREATE TABLE IF NOT EXISTS growth_experiments (
id TEXT PRIMARY KEY,
production_id TEXT NOT NULL,
video_id TEXT NOT NULL,
recommendation_id TEXT,
title TEXT NOT NULL,
hypothesis TEXT NOT NULL,
primary_metric TEXT NOT NULL DEFAULT 'ctr',
status TEXT NOT NULL DEFAULT 'draft',
arm_duration_hours INTEGER NOT NULL DEFAULT 48,
min_impressions INTEGER NOT NULL DEFAULT 1000,
guardrails TEXT NOT NULL DEFAULT '{}',
current_arm_id TEXT,
winning_arm_id TEXT,
result TEXT NOT NULL DEFAULT '{}',
approved_at TEXT,
started_at TEXT,
completed_at TEXT,
adopted_at TEXT,
cancelled_at TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (production_id) REFERENCES productions(id)
)`,
`CREATE TABLE IF NOT EXISTS experiment_arms (
id TEXT PRIMARY KEY,
experiment_id TEXT NOT NULL,
arm_index INTEGER NOT NULL,
label TEXT NOT NULL,
title TEXT NOT NULL,
thumbnail_path TEXT NOT NULL,
is_control INTEGER NOT NULL DEFAULT 0,
status TEXT NOT NULL DEFAULT 'pending',
baseline_metrics TEXT NOT NULL DEFAULT '{}',
final_metrics TEXT NOT NULL DEFAULT '{}',
result TEXT NOT NULL DEFAULT '{}',
started_at TEXT,
ended_at TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
UNIQUE(experiment_id, arm_index),
FOREIGN KEY (experiment_id) REFERENCES growth_experiments(id)
)`,
`CREATE TABLE IF NOT EXISTS experiment_samples (
id TEXT PRIMARY KEY,
experiment_id TEXT NOT NULL,
arm_id TEXT NOT NULL,
metrics TEXT NOT NULL,
traffic_sources TEXT NOT NULL DEFAULT '[]',
captured_at TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (experiment_id) REFERENCES growth_experiments(id),
FOREIGN KEY (arm_id) REFERENCES experiment_arms(id)
)`,
`CREATE TABLE IF NOT EXISTS retention_snapshots (
id TEXT PRIMARY KEY,
video_id TEXT NOT NULL,
production_id TEXT,
short_clip_id TEXT,
title TEXT,
surface TEXT NOT NULL DEFAULT 'long_form',
measurement_window TEXT NOT NULL,
published_at TEXT,
duration_seconds REAL NOT NULL,
points TEXT NOT NULL,
scene_metrics TEXT NOT NULL,
summary TEXT NOT NULL,
confidence TEXT DEFAULT 'low',
measured_at TEXT DEFAULT CURRENT_TIMESTAMP,
UNIQUE(video_id, measurement_window)
)`,
`CREATE TABLE IF NOT EXISTS audience_comments (
id TEXT PRIMARY KEY,
comment_id TEXT NOT NULL UNIQUE,
video_id TEXT NOT NULL,
parent_comment_id TEXT,
author_name TEXT,
author_channel_id TEXT,
is_channel_owner INTEGER DEFAULT 0,
text TEXT NOT NULL,
like_count INTEGER DEFAULT 0,
reply_count INTEGER DEFAULT 0,
published_at TEXT,
updated_at_youtube TEXT,
flags TEXT NOT NULL DEFAULT '[]',
analysis_state TEXT DEFAULT 'pending',
replied_by_agent INTEGER DEFAULT 0,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE TABLE IF NOT EXISTS engagement_insights (
id TEXT PRIMARY KEY,
video_id TEXT NOT NULL UNIQUE,
production_id TEXT,
title TEXT,
comment_count INTEGER DEFAULT 0,
analyzed_count INTEGER DEFAULT 0,
sentiment TEXT NOT NULL DEFAULT '{}',
themes TEXT NOT NULL DEFAULT '[]',
attention_flags TEXT NOT NULL DEFAULT '[]',
analysis_method TEXT DEFAULT 'ai',
analyzed_at TEXT,
last_synced_at TEXT,
newest_comment_at TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE TABLE IF NOT EXISTS reply_drafts (
id TEXT PRIMARY KEY,
comment_id TEXT NOT NULL UNIQUE,
video_id TEXT NOT NULL,
draft_text TEXT NOT NULL,
edited_text TEXT,
status TEXT DEFAULT 'proposed',
rationale TEXT,
posted_comment_id TEXT,
posted_at TEXT,
failure_reason TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
// Keywords Performance
`CREATE TABLE IF NOT EXISTS keyword_performance (
id TEXT PRIMARY KEY,
keyword TEXT NOT NULL UNIQUE,
total_uses INTEGER DEFAULT 0,
total_views INTEGER DEFAULT 0,
average_views INTEGER DEFAULT 0,
best_performing_video TEXT,
last_used TEXT,
performance_score INTEGER DEFAULT 0
)`,
// Content Performance History
`CREATE TABLE IF NOT EXISTS content_history (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
topic TEXT NOT NULL,
content_type TEXT NOT NULL,
publish_date TEXT NOT NULL,
views INTEGER DEFAULT 0,
likes INTEGER DEFAULT 0,
comments INTEGER DEFAULT 0,
watch_time INTEGER DEFAULT 0,
ctr REAL DEFAULT 0,
retention_rate REAL DEFAULT 0,
performance_score INTEGER DEFAULT 0,
youtube_id TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
// Automation Events
`CREATE TABLE IF NOT EXISTS automation_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
event_type TEXT NOT NULL,
status TEXT NOT NULL,
data TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE TABLE IF NOT EXISTS generation_jobs (
id TEXT PRIMARY KEY,
topic TEXT,
style TEXT,
length TEXT DEFAULT 'medium',
source TEXT DEFAULT 'manual',
status TEXT DEFAULT 'queued',
stage TEXT DEFAULT 'queued',
progress INTEGER DEFAULT 0,
production_id TEXT,
title TEXT,
error TEXT,
details TEXT,
cancel_requested INTEGER DEFAULT 0,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
completed_at TEXT
)`,
`CREATE TABLE IF NOT EXISTS generation_checkpoints (
job_id TEXT NOT NULL,
stage TEXT NOT NULL,
status TEXT DEFAULT 'pending',
artifact TEXT,
attempt_count INTEGER DEFAULT 0,
error TEXT,
started_at TEXT,
completed_at TEXT,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (job_id, stage),
FOREIGN KEY (job_id) REFERENCES generation_jobs(id)
)`,
`CREATE TABLE IF NOT EXISTS media_generation_tasks (
id TEXT PRIMARY KEY,
job_id TEXT,
production_id TEXT,
scene_index INTEGER NOT NULL,
provider TEXT NOT NULL,
model TEXT NOT NULL,
external_task_id TEXT,
status TEXT DEFAULT 'submitting',
request TEXT NOT NULL DEFAULT '{}',
provider_data TEXT NOT NULL DEFAULT '{}',
output_path TEXT,
error TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
completed_at TEXT,
UNIQUE(job_id, scene_index, provider),
FOREIGN KEY (job_id) REFERENCES generation_jobs(id)
)`,
`CREATE TABLE IF NOT EXISTS production_snapshots (
production_id TEXT PRIMARY KEY,
strategy TEXT,
script TEXT,
thumbnail TEXT,
seo TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (production_id) REFERENCES productions(id)
)`,
`CREATE TABLE IF NOT EXISTS content_reviews (
production_id TEXT PRIMARY KEY,
status TEXT DEFAULT 'needs_review',
editor_data TEXT,
quality_checks TEXT,
review_notes TEXT,
reviewed_at TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (production_id) REFERENCES productions(id)
)`,
`CREATE TABLE IF NOT EXISTS content_provenance (
production_id TEXT PRIMARY KEY,
sources TEXT NOT NULL DEFAULT '[]',
claims TEXT NOT NULL DEFAULT '[]',
contains_synthetic_media INTEGER DEFAULT 0,
status TEXT DEFAULT 'not_required',
summary TEXT NOT NULL DEFAULT '{}',
reviewed_at TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (production_id) REFERENCES productions(id)
)`,
`CREATE TABLE IF NOT EXISTS discoverability_audits (
id TEXT PRIMARY KEY,
production_id TEXT NOT NULL,
platform TEXT NOT NULL DEFAULT 'youtube',
mode TEXT NOT NULL DEFAULT 'content',
engine TEXT NOT NULL DEFAULT 'darkzseo',
engine_version TEXT,
schema_version TEXT,
status TEXT NOT NULL,
summary TEXT NOT NULL DEFAULT '{}',
error_code TEXT,
error TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (production_id) REFERENCES productions(id)
)`,
`CREATE TABLE IF NOT EXISTS discoverability_findings (
id TEXT PRIMARY KEY,
audit_id TEXT NOT NULL,
rule_id TEXT NOT NULL,
category TEXT NOT NULL,
severity TEXT NOT NULL,
applicability TEXT NOT NULL DEFAULT '[]',
message TEXT NOT NULL,
remediation TEXT,
fingerprint TEXT NOT NULL,
review_status TEXT NOT NULL DEFAULT 'pending',
review_reason TEXT,
reviewed_at TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (audit_id) REFERENCES discoverability_audits(id)
)`,
`CREATE INDEX IF NOT EXISTS idx_discoverability_audits_production
ON discoverability_audits(production_id, platform, created_at)`,
`CREATE INDEX IF NOT EXISTS idx_discoverability_findings_audit
ON discoverability_findings(audit_id, severity, review_status)`,
`CREATE TABLE IF NOT EXISTS production_scenes (
id TEXT PRIMARY KEY,
production_id TEXT NOT NULL,
position INTEGER NOT NULL,
label TEXT NOT NULL,
script_text TEXT NOT NULL DEFAULT '',
prompt TEXT NOT NULL DEFAULT '',
duration REAL NOT NULL DEFAULT 5,
asset_type TEXT DEFAULT 'missing',
asset_origin TEXT DEFAULT 'generated',
asset_path TEXT,
audio_path TEXT,
narration_provider TEXT,
narration_model TEXT,
narration_task_id TEXT,
narration_error TEXT,
narration_generated_at TEXT,
narration_cost TEXT NOT NULL DEFAULT '{}',
provider TEXT,
model TEXT,
external_task_id TEXT,
status TEXT DEFAULT 'ready',
narration_status TEXT DEFAULT 'current',
revision INTEGER DEFAULT 1,
locked INTEGER DEFAULT 0,
rights_confirmed INTEGER DEFAULT 0,
provenance_source_ids TEXT NOT NULL DEFAULT '[]',
contains_synthetic_media INTEGER DEFAULT 0,
estimated_cost TEXT NOT NULL DEFAULT '{}',
actual_cost TEXT NOT NULL DEFAULT '{}',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
UNIQUE(production_id, position),
FOREIGN KEY (production_id) REFERENCES productions(id)
)`,
`CREATE TABLE IF NOT EXISTS production_scene_revisions (
id TEXT PRIMARY KEY,
production_id TEXT NOT NULL,
scene_id TEXT NOT NULL,
action TEXT NOT NULL,
status TEXT DEFAULT 'completed',
before_state TEXT NOT NULL DEFAULT '{}',
after_state TEXT NOT NULL DEFAULT '{}',
cost_evidence TEXT NOT NULL DEFAULT '{}',
error TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
completed_at TEXT,
FOREIGN KEY (production_id) REFERENCES productions(id),
FOREIGN KEY (scene_id) REFERENCES production_scenes(id)
)`,
`CREATE TABLE IF NOT EXISTS shorts_clips (
id TEXT PRIMARY KEY,
production_id TEXT NOT NULL,
position INTEGER NOT NULL,
title TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
tags TEXT NOT NULL DEFAULT '[]',
source_scene_ids TEXT NOT NULL DEFAULT '[]',
start_seconds REAL NOT NULL DEFAULT 0,
duration REAL NOT NULL DEFAULT 30,
layout TEXT NOT NULL DEFAULT 'blur',
rationale TEXT,
status TEXT NOT NULL DEFAULT 'proposed',
output_path TEXT,
captions_path TEXT,
publish_time TEXT,
privacy_status TEXT DEFAULT 'private',
inherited_evidence TEXT NOT NULL DEFAULT '{}',
rendered_at TEXT,
approved_at TEXT,
schedule_id TEXT,
youtube_id TEXT,
youtube_url TEXT,
error TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
UNIQUE(production_id, position),
FOREIGN KEY (production_id) REFERENCES productions(id)
)`,
`CREATE TABLE IF NOT EXISTS channel_profiles (
id TEXT PRIMARY KEY,
channel_name TEXT,
goal TEXT,
target_audience TEXT,
brand_voice TEXT,
default_style TEXT,
call_to_action TEXT,
banned_topics TEXT,
visual_style TEXT,
timezone TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE TABLE IF NOT EXISTS content_ideas (
id TEXT PRIMARY KEY,
topic TEXT NOT NULL,
angle TEXT,
style TEXT,
status TEXT DEFAULT 'backlog',
rationale TEXT,
scheduled_for TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE TABLE IF NOT EXISTS channel_strategies (
id TEXT PRIMARY KEY,
objective TEXT NOT NULL,
audience TEXT NOT NULL,
value_proposition TEXT,
content_pillars TEXT NOT NULL,
cadence_per_week INTEGER DEFAULT 1,
videos_per_run INTEGER DEFAULT 1,
default_format TEXT DEFAULT 'explainer',
default_length TEXT DEFAULT 'medium',
success_metric TEXT,
primary_kpi TEXT DEFAULT 'views',
target_value REAL,
target_window_days INTEGER DEFAULT 28,
monthly_budget REAL,
outcome_currency TEXT DEFAULT 'USD',
constraints TEXT,
status TEXT DEFAULT 'draft',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE TABLE IF NOT EXISTS operator_runs (
id TEXT PRIMARY KEY,
strategy_id TEXT NOT NULL,
status TEXT DEFAULT 'queued',
stage TEXT DEFAULT 'queued',
progress INTEGER DEFAULT 0,
research TEXT,
plan TEXT,
generated_jobs TEXT,
summary TEXT,
error TEXT,
cancel_requested INTEGER DEFAULT 0,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
completed_at TEXT,
FOREIGN KEY (strategy_id) REFERENCES channel_strategies(id)
)`,
`CREATE TABLE IF NOT EXISTS notifications (
id TEXT PRIMARY KEY,
type TEXT NOT NULL,
level TEXT DEFAULT 'info',
title TEXT NOT NULL,
message TEXT NOT NULL,
data TEXT,
status TEXT DEFAULT 'unread',
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE TABLE IF NOT EXISTS readiness_runs (
id TEXT PRIMARY KEY,
status TEXT NOT NULL,
checks TEXT NOT NULL,
summary TEXT NOT NULL,
started_at TEXT NOT NULL,
completed_at TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
// System Settings
`CREATE TABLE IF NOT EXISTS settings (
key TEXT PRIMARY KEY,
value TEXT NOT NULL,
description TEXT,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
)`
];
for (const tableQuery of tables) {
await this.executeQuery(tableQuery);
}
await this.ensureColumns('production_scenes', {
narration_provider: 'TEXT',
narration_model: 'TEXT',
narration_task_id: 'TEXT',
narration_error: 'TEXT',
narration_generated_at: 'TEXT',
narration_cost: "TEXT NOT NULL DEFAULT '{}'"
});
await this.ensureColumns('channel_strategies', {
primary_kpi: "TEXT DEFAULT 'views'",
target_value: 'REAL',
target_window_days: 'INTEGER DEFAULT 28',
monthly_budget: 'REAL',
outcome_currency: "TEXT DEFAULT 'USD'"
});
await this.ensureColumns('discoverability_audits', {
error_code: 'TEXT'
});
// Insert default settings
await this.insertDefaultSettings();
}
async ensureColumns(tableName, columns) {
const allowedTables = new Set(['production_scenes', 'channel_strategies', 'discoverability_audits']);
if (!allowedTables.has(tableName)) throw new Error(`Unsupported migration table: ${tableName}`);
const existing = new Set((await this.getAllRows(`PRAGMA table_info(${tableName})`)).map(column => column.name));
for (const [columnName, definition] of Object.entries(columns)) {
if (!existing.has(columnName)) {
await this.executeQuery(`ALTER TABLE ${tableName} ADD COLUMN ${columnName} ${definition}`);
}
}
}
async insertDefaultSettings() {
const defaultSettings = [
['daily_content_enabled', 'true', 'Enable daily content generation'],
['auto_publish_enabled', 'true', 'Enable automatic publishing'],
['analytics_enabled', 'true', 'Enable analytics collection'],
['optimization_enabled', 'true', 'Enable automatic optimization'],
['publish_time_optimization', 'true', 'Optimize publishing times automatically'],
['thumbnail_ab_testing', 'false', 'Enable thumbnail A/B testing'],
['content_backup_enabled', 'true', 'Enable content backup'],
['notification_enabled', 'true', 'Enable system notifications'],
['approval_required', 'true', 'Require human approval before scheduling generated content'],
['automation_paused', 'false', 'Pause generation and publishing automation'],
['channel_timezone', 'America/Chicago', 'Timezone used to present channel schedules'],
['max_daily_posts', '1', 'Maximum posts per day'],
['content_buffer_days', '3', 'Days of content to keep in buffer'],
['video_provider', 'slideshow', 'Video provider: slideshow, auto, seedance, minimax_h3, google_omni, kling, or wan'],
['video_provider_order', 'seedance,minimax_h3,google_omni,kling,wan,slideshow', 'Provider priority used by automatic routing'],
['video_generation_mode', 'hybrid', 'Use provider clips within a locally assembled long-form video'],
['video_clip_duration', '8', 'Requested duration for each generated provider clip'],
['video_max_generated_seconds', '60', 'Maximum paid provider seconds per production'],
['video_resolution', '720p', 'Requested generated clip resolution'],
['video_aspect_ratio', '16:9', 'Requested generated clip aspect ratio']
];
for (const [key, value, description] of defaultSettings) {
await this.executeQuery(
'INSERT OR IGNORE INTO settings (key, value, description) VALUES (?, ?, ?)',
[key, value, description]
);
}
await this.executeQuery(
`INSERT OR IGNORE INTO channel_profiles (
id, channel_name, goal, target_audience, brand_voice, default_style,
call_to_action, banned_topics, visual_style, timezone
) VALUES ('default', ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
process.env.CHANNEL_NAME || 'My YouTube Channel',
'Grow a trusted, useful YouTube channel',
process.env.TARGET_AUDIENCE || 'General audience',
'Clear, credible, and engaging',
'explainer',
'Subscribe for more useful videos.',
'[]',
'Clean, high-contrast, and readable',
process.env.CHANNEL_TIMEZONE || 'America/Chicago'
]
);
}
// Content Strategy methods
async saveContentStrategy(strategy) {
const id = this.generateId('strategy');
await this.executeQuery(
`INSERT INTO content_strategies (
id, topic, angle, target_audience, content_type, keywords,
estimated_views, best_publish_time, competitor_analysis
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
id,
strategy.topic,
strategy.angle,
strategy.targetAudience,
strategy.contentType,
JSON.stringify(strategy.keywords),
strategy.estimatedViews,
strategy.bestPublishTime,
JSON.stringify(strategy.competitorAnalysis)
]
);
return id;
}
async getContentHistory() {
const rows = await this.getAllRows('SELECT * FROM content_history ORDER BY publish_date DESC');
return rows;
}
// Script methods
async saveScript(script) {
const id = this.generateId('script');
await this.executeQuery(
`INSERT INTO scripts (
id, title, hook, introduction, main_content, conclusion,
call_to_action, full_script, duration, tone, pacing, keywords
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
id,
script.title,
JSON.stringify(script.hook),
JSON.stringify(script.introduction),
JSON.stringify(script.mainContent),
JSON.stringify(script.conclusion),
JSON.stringify(script.callToAction),
script.fullScript,
script.duration,
script.tone,
script.pacing,
JSON.stringify(script.keywords)
]
);
return id;
}
// Thumbnail methods
async saveThumbnail(thumbnail) {
const id = this.generateId('thumbnail');
await this.executeQuery(
`INSERT INTO thumbnails (
id, path, concept, prompt, dimensions, file_size
) VALUES (?, ?, ?, ?, ?, ?)`,
[
id,
thumbnail.path,
JSON.stringify(thumbnail.concept),
thumbnail.prompt,
JSON.stringify(thumbnail.dimensions),
thumbnail.fileSize
]
);
return id;
}
// SEO methods
async saveSEOData(seoData) {
const id = this.generateId('seo');
await this.executeQuery(
`INSERT INTO seo_data (
id, title, description, tags, hashtags, chapters,
end_screen, seo_score, metadata
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
id,
seoData.title,
seoData.description,
JSON.stringify(seoData.tags),
JSON.stringify(seoData.hashtags),
JSON.stringify(seoData.chapters),
JSON.stringify(seoData.endScreen),
seoData.seoScore,
JSON.stringify(seoData.metadata)
]
);
return id;
}
// Production methods
async saveProductionData(production) {
await this.executeQuery(
`INSERT OR REPLACE INTO productions (
id, status, assets, timeline, scheduled_publish_time,
priority, estimated_duration
) VALUES (?, ?, ?, ?, ?, ?, ?)`,
[
production.id,
production.status,
JSON.stringify(production.assets),
JSON.stringify(production.timeline),
production.scheduledPublishTime,
production.priority,
production.estimatedDuration
]
);
return production.id;
}
async updateProductionData(production) {
await this.executeQuery(
`UPDATE productions SET
status = ?, assets = ?, timeline = ?,
scheduled_publish_time = ?, priority = ?
WHERE id = ?`,
[
production.status,
JSON.stringify(production.assets),
JSON.stringify(production.timeline),
production.scheduledPublishTime,
production.priority,
production.id
]
);
}
async getProductionPipeline() {
const rows = await this.getAllRows(
'SELECT * FROM productions ORDER BY priority DESC, created_at ASC'
);
return rows.map(row => ({
...row,
assets: JSON.parse(row.assets || '{}'),
timeline: JSON.parse(row.timeline || '{}')
}));
}
async saveProductionSnapshot(production) {
await this.executeQuery(
`INSERT OR REPLACE INTO production_snapshots (
production_id, strategy, script, thumbnail, seo
) VALUES (?, ?, ?, ?, ?)`,
[
production.id,
JSON.stringify(production.strategy || {}),
JSON.stringify(production.script || {}),
JSON.stringify(production.thumbnail || {}),
JSON.stringify(production.seo || {})
]
);
}
async updateProductionStatus(productionId, status) {
await this.executeQuery('UPDATE productions SET status = ? WHERE id = ?', [status, productionId]);
}
async getProductionBundle(productionId) {
const row = await this.getRow(
`SELECT p.*, ps.strategy, ps.script, ps.thumbnail, ps.seo,
cr.status AS review_status, cr.editor_data, cr.quality_checks,
cr.review_notes, cr.reviewed_at
FROM productions p
LEFT JOIN production_snapshots ps ON ps.production_id = p.id
LEFT JOIN content_reviews cr ON cr.production_id = p.id
WHERE p.id = ?`,
[productionId]
);
if (!row) return null;
const schedule = await this.getRow(
'SELECT * FROM publish_schedule WHERE production_id = ? ORDER BY created_at DESC LIMIT 1',
[productionId]
);
const provenance = await this.getContentProvenance(productionId);
const discoverability = await this.getLatestDiscoverabilityAudit(productionId, 'youtube');
const scenes = await this.listProductionScenes(productionId);
const sceneRevisions = scenes.length ? await this.listProductionSceneRevisions(productionId, 50) : [];
const shorts = await this.listShortClips(productionId);
return {
...row,
assets: JSON.parse(row.assets || '{}'),
timeline: JSON.parse(row.timeline || '{}'),
strategy: JSON.parse(row.strategy || '{}'),
script: JSON.parse(row.script || '{}'),
thumbnail: JSON.parse(row.thumbnail || '{}'),
seo: JSON.parse(row.seo || '{}'),
editorData: JSON.parse(row.editor_data || '{}'),
qualityChecks: JSON.parse(row.quality_checks || '[]'),
schedule: schedule ? { ...schedule, metadata: JSON.parse(schedule.metadata || '{}') } : null,
provenance: provenance || {
sources: [], claims: [], containsSyntheticMedia: false, status: 'not_required',
summary: { sourceCount: 0, verifiedSources: 0, claimCount: 0, resolvedClaims: 0, highRiskClaims: 0, unresolvedClaims: 0 }
},
discoverability,
scenes,
sceneRevisions,
shorts
};
}
async getPipelineOverview(limit = 50) {
const rows = await this.getAllRows(
`SELECT p.id, p.status, p.assets, p.timeline, p.scheduled_publish_time,
p.priority, p.estimated_duration, p.created_at,
ps.strategy, ps.script, ps.seo,
cr.status AS review_status, cr.quality_checks,
sch.id AS schedule_id, sch.status AS schedule_status,
sch.publish_time, sch.youtube_url, sch.error_message
FROM productions p
LEFT JOIN production_snapshots ps ON ps.production_id = p.id
LEFT JOIN content_reviews cr ON cr.production_id = p.id
LEFT JOIN publish_schedule sch ON sch.id = (
SELECT id FROM publish_schedule WHERE production_id = p.id ORDER BY created_at DESC LIMIT 1
)
ORDER BY p.created_at DESC LIMIT ?`,
[limit]
);
return rows.map(row => {
const strategy = JSON.parse(row.strategy || '{}');
const script = JSON.parse(row.script || '{}');
const seo = JSON.parse(row.seo || '{}');
const assets = JSON.parse(row.assets || '{}');
return {
...row,
strategy,
script,
seo,
assets,
timeline: JSON.parse(row.timeline || '{}'),
qualityChecks: JSON.parse(row.quality_checks || '[]'),
title: seo.title || script.title || strategy.topic || row.id,
topic: strategy.topic || '',
hasVideo: Boolean(assets.finalVideo?.path && !assets.finalVideo?.simulated),
hasThumbnail: Boolean(assets.thumbnail?.path)
};
});
}
async createGenerationJob(input = {}) {
const id = this.generateId('job');
const details = {
strategyContext: input.strategyContext || {},
resumeCount: 0,
reusedStages: []
};
await this.executeQuery(
`INSERT INTO generation_jobs (
id, topic, style, length, source, status, stage, progress, details
) VALUES (?, ?, ?, ?, ?, 'queued', 'queued', 0, ?)`,
[id, input.topic || null, input.style || null, input.length || 'medium', input.source || 'manual', JSON.stringify(details)]
);
return this.getGenerationJob(id);
}
async getGenerationJob(id) {
const row = await this.getRow('SELECT * FROM generation_jobs WHERE id = ?', [id]);
return row ? { ...row, details: JSON.parse(row.details || '{}'), cancelRequested: Boolean(row.cancel_requested) } : null;
}
async updateGenerationJob(id, changes = {}) {
const current = await this.getGenerationJob(id);
if (!current) return null;
const details = { ...(current.details || {}), ...(changes.details || {}) };
await this.executeQuery(
`UPDATE generation_jobs SET status = ?, stage = ?, progress = ?, production_id = ?,
title = ?, error = ?, details = ?, cancel_requested = ?, updated_at = datetime('now'),
completed_at = ? WHERE id = ?`,
[
changes.status ?? current.status,
changes.stage ?? current.stage,
changes.progress ?? current.progress,
changes.productionId ?? current.production_id,
changes.title ?? current.title,
changes.error === undefined ? current.error : changes.error,
JSON.stringify(details),
changes.cancelRequested === undefined ? current.cancel_requested : Number(changes.cancelRequested),
changes.completedAt === undefined ? current.completed_at : changes.completedAt,
id
]
);
return this.getGenerationJob(id);
}
async listGenerationJobs(limit = 30) {
const rows = await this.getAllRows('SELECT * FROM generation_jobs ORDER BY created_at DESC LIMIT ?', [limit]);
return Promise.all(rows.map(async row => {
const job = { ...row, details: JSON.parse(row.details || '{}'), cancelRequested: Boolean(row.cancel_requested) };
job.checkpoints = await this.listGenerationCheckpoints(job.id);
job.mediaTasks = await this.listMediaGenerationTasks(job.id);
return job;
}));
}
async saveGenerationCheckpoint(jobId, stage, changes = {}) {
const current = await this.getGenerationCheckpoint(jobId, stage);
const artifact = changes.artifact === undefined ? current?.artifact : changes.artifact;
const attemptCount = changes.incrementAttempt
? Number(current?.attempt_count || 0) + 1
: changes.attemptCount ?? current?.attempt_count ?? 0;
await this.executeQuery(
`INSERT INTO generation_checkpoints (
job_id, stage, status, artifact, attempt_count, error, started_at, completed_at, updated_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))
ON CONFLICT(job_id, stage) DO UPDATE SET
status = excluded.status, artifact = excluded.artifact,
attempt_count = excluded.attempt_count, error = excluded.error,
started_at = excluded.started_at, completed_at = excluded.completed_at,
updated_at = datetime('now')`,
[
jobId,
stage,
changes.status ?? current?.status ?? 'pending',
artifact === undefined ? null : JSON.stringify(artifact),
attemptCount,
changes.error === undefined ? current?.error ?? null : changes.error,
changes.startedAt === undefined ? current?.started_at ?? null : changes.startedAt,
changes.completedAt === undefined ? current?.completed_at ?? null : changes.completedAt
]
);
return this.getGenerationCheckpoint(jobId, stage);
}
async getGenerationCheckpoint(jobId, stage) {
const row = await this.getRow(
'SELECT * FROM generation_checkpoints WHERE job_id = ? AND stage = ?',
[jobId, stage]
);
return row ? { ...row, artifact: JSON.parse(row.artifact || 'null') } : null;
}
async listGenerationCheckpoints(jobId) {
const rows = await this.getAllRows(
'SELECT * FROM generation_checkpoints WHERE job_id = ? ORDER BY updated_at, stage',
[jobId]
);
return rows.map(row => ({ ...row, artifact: JSON.parse(row.artifact || 'null') }));
}
async deleteGenerationCheckpoints(jobId, stages = []) {
if (!stages.length) return;
const placeholders = stages.map(() => '?').join(', ');
await this.executeQuery(
`DELETE FROM generation_checkpoints WHERE job_id = ? AND stage IN (${placeholders})`,
[jobId, ...stages]
);
}
async createMediaGenerationTask(input = {}) {
const id = this.generateId('media');
await this.executeQuery(
`INSERT INTO media_generation_tasks (
id, job_id, production_id, scene_index, provider, model, status, request, provider_data
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, '{}')
ON CONFLICT(job_id, scene_index, provider) DO UPDATE SET
production_id = excluded.production_id, request = excluded.request, updated_at = datetime('now')`,
[id, input.jobId || null, input.productionId || null, input.sceneIndex, input.provider, input.model, input.status || 'submitting', JSON.stringify(input.request || {})]
);
return this.findMediaGenerationTask(input.jobId, input.sceneIndex, input.provider);
}
async findMediaGenerationTask(jobId, sceneIndex, provider) {
const row = await this.getRow(
'SELECT * FROM media_generation_tasks WHERE job_id IS ? AND scene_index = ? AND provider = ?',
[jobId || null, sceneIndex, provider]
);
return this.parseMediaGenerationTask(row);
}
async updateMediaGenerationTask(id, changes = {}) {
const current = this.parseMediaGenerationTask(await this.getRow('SELECT * FROM media_generation_tasks WHERE id = ?', [id]));
if (!current) return null;
await this.executeQuery(
`UPDATE media_generation_tasks SET model = ?, external_task_id = ?, status = ?, provider_data = ?,
output_path = ?, error = ?, completed_at = ?, updated_at = datetime('now') WHERE id = ?`,
[
changes.model ?? current.model,
changes.externalTaskId ?? current.external_task_id,
changes.status ?? current.status,
JSON.stringify(changes.providerData ?? current.providerData ?? {}),
changes.outputPath ?? current.output_path,
changes.error === undefined ? current.error : changes.error,
changes.completedAt === undefined ? current.completed_at : changes.completedAt,
id
]
);
return this.parseMediaGenerationTask(await this.getRow('SELECT * FROM media_generation_tasks WHERE id = ?', [id]));
}
async listMediaGenerationTasks(jobId) {
const rows = await this.getAllRows('SELECT * FROM media_generation_tasks WHERE job_id = ? ORDER BY scene_index, created_at', [jobId]);
return rows.map(row => this.parseMediaGenerationTask(row));
}
parseMediaGenerationTask(row) {
return row ? {
...row,
request: JSON.parse(row.request || '{}'),
providerData: JSON.parse(row.provider_data || '{}')
} : null;
}
async replaceProductionScenes(productionId, scenes = []) {
await this.executeQuery('DELETE FROM production_scenes WHERE production_id = ?', [productionId]);
for (const [position, scene] of scenes.entries()) {
const id = scene.id || this.generateId('scene');
await this.executeQuery(
`INSERT INTO production_scenes (
id, production_id, position, label, script_text, prompt, duration,
asset_type, asset_origin, asset_path, audio_path,
narration_provider, narration_model, narration_task_id, narration_error,
narration_generated_at, narration_cost, provider, model,
external_task_id, status, narration_status, revision, locked,
rights_confirmed, provenance_source_ids, contains_synthetic_media,
estimated_cost, actual_cost
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
id, productionId, position, scene.label, scene.scriptText || '', scene.prompt || '', scene.duration,
scene.assetType || 'missing', scene.assetOrigin || 'generated', scene.assetPath || null,
scene.audioPath || null, scene.narrationProvider || null, scene.narrationModel || null,
scene.narrationTaskId || null, scene.narrationError || null, scene.narrationGeneratedAt || null,
JSON.stringify(scene.narrationCost || {}), scene.provider || null, scene.model || null, scene.externalTaskId || null,
scene.status || 'ready', scene.narrationStatus || 'current', scene.revision || 1,
scene.locked ? 1 : 0, scene.rightsConfirmed ? 1 : 0,
JSON.stringify(scene.provenanceSourceIds || []), scene.containsSyntheticMedia ? 1 : 0,
JSON.stringify(scene.estimatedCost || {}), JSON.stringify(scene.actualCost || {})
]
);
}
return this.listProductionScenes(productionId);
}
async listProductionScenes(productionId) {
const rows = await this.getAllRows(
'SELECT * FROM production_scenes WHERE production_id = ? ORDER BY position, created_at',
[productionId]
);
return rows.map(row => this.parseProductionScene(row));
}
async getProductionScene(productionId, sceneId) {
return this.parseProductionScene(await this.getRow(
'SELECT * FROM production_scenes WHERE production_id = ? AND id = ?',
[productionId, sceneId]
));
}
async updateProductionScene(productionId, sceneId, changes = {}) {
const current = await this.getProductionScene(productionId, sceneId);
if (!current) return null;
const next = { ...current, ...changes };
await this.executeQuery(
`UPDATE production_scenes SET
position = ?, label = ?, script_text = ?, prompt = ?, duration = ?,
asset_type = ?, asset_origin = ?, asset_path = ?, audio_path = ?,
narration_provider = ?, narration_model = ?, narration_task_id = ?, narration_error = ?,
narration_generated_at = ?, narration_cost = ?, provider = ?,
model = ?, external_task_id = ?, status = ?, narration_status = ?, revision = ?,
locked = ?, rights_confirmed = ?, provenance_source_ids = ?,
contains_synthetic_media = ?, estimated_cost = ?, actual_cost = ?,
updated_at = datetime('now')
WHERE production_id = ? AND id = ?`,
[
next.position, next.label, next.scriptText || '', next.prompt || '', next.duration,
next.assetType || 'missing', next.assetOrigin || 'generated', next.assetPath || null,
next.audioPath || null, next.narrationProvider || null, next.narrationModel || null,
next.narrationTaskId || null, next.narrationError || null, next.narrationGeneratedAt || null,
JSON.stringify(next.narrationCost || {}), next.provider || null, next.model || null, next.externalTaskId || null,
next.status || 'ready', next.narrationStatus || 'current', next.revision || 1,
next.locked ? 1 : 0, next.rightsConfirmed ? 1 : 0,
JSON.stringify(next.provenanceSourceIds || []), next.containsSyntheticMedia ? 1 : 0,
JSON.stringify(next.estimatedCost || {}), JSON.stringify(next.actualCost || {}),
productionId, sceneId
]
);
return this.getProductionScene(productionId, sceneId);
}
async reorderProductionScenes(productionId, orderedIds = []) {
const scenes = await this.listProductionScenes(productionId);
if (orderedIds.length !== scenes.length || new Set(orderedIds).size !== scenes.length ||
scenes.some(scene => !orderedIds.includes(scene.id))) {
throw new Error('Scene order must contain every scene exactly once');
}
// Move through temporary negative positions to preserve the unique constraint.
for (const scene of scenes) {
await this.executeQuery('UPDATE production_scenes SET position = ? WHERE id = ?', [-(scene.position + 1), scene.id]);
}
for (const [position, sceneId] of orderedIds.entries()) {
await this.executeQuery(
`UPDATE production_scenes SET position = ?, status = 'needs_rebuild', updated_at = datetime('now') WHERE id = ?`,
[position, sceneId]
);
}
return this.listProductionScenes(productionId);
}
async saveProductionSceneRevision(input = {}) {
const id = this.generateId('scene_revision');
await this.executeQuery(
`INSERT INTO production_scene_revisions (
id, production_id, scene_id, action, status, before_state, after_state,
cost_evidence, error, completed_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
id, input.productionId, input.sceneId, input.action, input.status || 'completed',
JSON.stringify(input.before || {}), JSON.stringify(input.after || {}),
JSON.stringify(input.costEvidence || {}), input.error || null,
input.completedAt || new Date().toISOString()
]
);
return this.getProductionSceneRevision(id);
}
async getProductionSceneRevision(id) {
return this.parseProductionSceneRevision(await this.getRow('SELECT * FROM production_scene_revisions WHERE id = ?', [id]));
}
async listProductionSceneRevisions(productionId, limit = 100) {
const rows = await this.getAllRows(
'SELECT * FROM production_scene_revisions WHERE production_id = ? ORDER BY created_at DESC LIMIT ?',
[productionId, limit]
);
return rows.map(row => this.parseProductionSceneRevision(row));
}
parseProductionScene(row) {
if (!row) return null;
return {
...row,
position: Number(row.position),
duration: Number(row.duration),
scriptText: row.script_text || '',
assetType: row.asset_type || 'missing',
assetOrigin: row.asset_origin || 'generated',
assetPath: row.asset_path || null,
audioPath: row.audio_path || null,
narrationProvider: row.narration_provider || null,
narrationModel: row.narration_model || null,
narrationTaskId: row.narration_task_id || null,
narrationError: row.narration_error || null,
narrationGeneratedAt: row.narration_generated_at || null,
narrationCost: JSON.parse(row.narration_cost || '{}'),
externalTaskId: row.external_task_id || null,
narrationStatus: row.narration_status || 'current',
revision: Number(row.revision || 1),
locked: Boolean(row.locked),
rightsConfirmed: Boolean(row.rights_confirmed),
provenanceSourceIds: JSON.parse(row.provenance_source_ids || '[]'),
containsSyntheticMedia: Boolean(row.contains_synthetic_media),
estimatedCost: JSON.parse(row.estimated_cost || '{}'),
actualCost: JSON.parse(row.actual_cost || '{}')
};
}
parseProductionSceneRevision(row) {
return row ? {
...row,
before: JSON.parse(row.before_state || '{}'),
after: JSON.parse(row.after_state || '{}'),
costEvidence: JSON.parse(row.cost_evidence || '{}')
} : null;
}
async replaceShortClips(productionId, clips = []) {
await this.executeQuery('DELETE FROM shorts_clips WHERE production_id = ?', [productionId]);
for (const [position, clip] of clips.entries()) {
const id = clip.id || this.generateId('short');
await this.executeQuery(
`INSERT INTO shorts_clips (
id, production_id, position, title, description, tags, source_scene_ids,
start_seconds, duration, layout, rationale, status, output_path, captions_path,
publish_time, privacy_status, inherited_evidence, rendered_at, approved_at,
schedule_id, youtube_id, youtube_url, error
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
id, productionId, position, clip.title, clip.description || '', JSON.stringify(clip.tags || []),
JSON.stringify(clip.sourceSceneIds || []), Number(clip.startSeconds || 0), Number(clip.duration || 30),
clip.layout || 'blur', clip.rationale || null, clip.status || 'proposed', clip.outputPath || null,
clip.captionsPath || null, clip.publishTime || null, clip.privacyStatus || 'private',
JSON.stringify(clip.inheritedEvidence || {}), clip.renderedAt || null, clip.approvedAt || null,
clip.scheduleId || null, clip.youtubeId || null, clip.youtubeUrl || null, clip.error || null
]
);
}
return this.listShortClips(productionId);
}
async listShortClips(productionId) {
const rows = await this.getAllRows(
'SELECT * FROM shorts_clips WHERE production_id = ? ORDER BY position, created_at',
[productionId]
);
return rows.map(row => this.parseShortClip(row));
}
async getShortClip(id) {
return this.parseShortClip(await this.getRow('SELECT * FROM shorts_clips WHERE id = ?', [id]));
}
async updateShortClip(id, changes = {}) {
const current = await this.getShortClip(id);
if (!current) return null;
const next = { ...current, ...changes };
await this.executeQuery(
`UPDATE shorts_clips SET
title = ?, description = ?, tags = ?, source_scene_ids = ?, start_seconds = ?,
duration = ?, layout = ?, rationale = ?, status = ?, output_path = ?, captions_path = ?,
publish_time = ?, privacy_status = ?, inherited_evidence = ?, rendered_at = ?, approved_at = ?,
schedule_id = ?, youtube_id = ?, youtube_url = ?, error = ?, updated_at = datetime('now')
WHERE id = ?`,
[
next.title, next.description || '', JSON.stringify(next.tags || []), JSON.stringify(next.sourceSceneIds || []),
Number(next.startSeconds || 0), Number(next.duration || 30), next.layout || 'blur', next.rationale || null,
next.status || 'proposed', next.outputPath || null, next.captionsPath || null, next.publishTime || null,
next.privacyStatus || 'private', JSON.stringify(next.inheritedEvidence || {}), next.renderedAt || null,
next.approvedAt || null, next.scheduleId || null, next.youtubeId || null, next.youtubeUrl || null,
next.error || null, id
]
);
return this.getShortClip(id);
}
parseShortClip(row) {
if (!row) return null;
return {
...row,
productionId: row.production_id,
position: Number(row.position),
tags: JSON.parse(row.tags || '[]'),
sourceSceneIds: JSON.parse(row.source_scene_ids || '[]'),
startSeconds: Number(row.start_seconds || 0),
duration: Number(row.duration || 0),
outputPath: row.output_path || null,
captionsPath: row.captions_path || null,
publishTime: row.publish_time || null,
privacyStatus: row.privacy_status || 'private',
inheritedEvidence: JSON.parse(row.inherited_evidence || '{}'),
renderedAt: row.rendered_at || null,
approvedAt: row.approved_at || null,
scheduleId: row.schedule_id || null,
youtubeId: row.youtube_id || null,
youtubeUrl: row.youtube_url || null
};
}
async markInterruptedJobs() {
await this.executeQuery(
`UPDATE generation_jobs SET status = 'interrupted',
error = 'The application restarted before this job finished', updated_at = datetime('now'),
completed_at = datetime('now') WHERE status IN ('queued', 'running')`
);
await this.executeQuery(
`UPDATE operator_runs SET status = 'interrupted', stage = 'interrupted',
error = 'The application restarted before this operator run finished', updated_at = datetime('now'),
completed_at = datetime('now') WHERE status IN ('queued', 'running', 'cancelling')`
);
}
async saveContentReview(productionId, review = {}) {
await this.executeQuery(
`INSERT INTO content_reviews (
production_id, status, editor_data, quality_checks, review_notes, reviewed_at, updated_at
) VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
ON CONFLICT(production_id) DO UPDATE SET
status = excluded.status, editor_data = excluded.editor_data,
quality_checks = excluded.quality_checks, review_notes = excluded.review_notes,
reviewed_at = excluded.reviewed_at, updated_at = datetime('now')`,
[
productionId,
review.status || 'needs_review',
JSON.stringify(review.editorData || {}),
JSON.stringify(review.qualityChecks || []),
review.reviewNotes || null,
review.reviewedAt || null
]
);
return this.getProductionBundle(productionId);
}
async saveContentProvenance(productionId, provenance = {}) {
await this.executeQuery(
`INSERT INTO content_provenance (
production_id, sources, claims, contains_synthetic_media, status, summary, reviewed_at, updated_at
) VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'))
ON CONFLICT(production_id) DO UPDATE SET
sources = excluded.sources, claims = excluded.claims,
contains_synthetic_media = excluded.contains_synthetic_media,
status = excluded.status, summary = excluded.summary,
reviewed_at = excluded.reviewed_at, updated_at = datetime('now')`,
[
productionId,
JSON.stringify(provenance.sources || []),
JSON.stringify(provenance.claims || []),
Number(provenance.containsSyntheticMedia === true),
provenance.status || 'not_required',
JSON.stringify(provenance.summary || {}),
provenance.reviewedAt || null
]
);
return this.getContentProvenance(productionId);
}
async getContentProvenance(productionId) {
const row = await this.getRow('SELECT * FROM content_provenance WHERE production_id = ?', [productionId]);
return row ? {
...row,
sources: JSON.parse(row.sources || '[]'),
claims: JSON.parse(row.claims || '[]'),
containsSyntheticMedia: Boolean(row.contains_synthetic_media),
summary: JSON.parse(row.summary || '{}'),
reviewedAt: row.reviewed_at
} : null;
}
async saveDiscoverabilityAudit(productionId, platform, report = {}) {
const auditId = this.generateId('discoverability');
const findings = Array.isArray(report.findings) ? report.findings : [];
await this.executeQuery('BEGIN TRANSACTION');
try {
await this.executeQuery(
`INSERT INTO discoverability_audits (
id, production_id, platform, mode, engine, engine_version,
schema_version, status, summary, error_code, error
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
auditId,
productionId,
platform || 'youtube',
report.mode || 'content',
report.engine?.name || 'darkzseo',
report.engine?.version || null,
report.schemaVersion || null,
report.status || 'unavailable',
JSON.stringify(report.summary || {}),
report.errorCode || null,
report.error || null
]
);
for (const finding of findings) {
const previous = await this.getRow(
`SELECT df.review_status, df.review_reason, df.reviewed_at
FROM discoverability_findings df
JOIN discoverability_audits da ON da.id = df.audit_id
WHERE da.production_id = ? AND da.platform = ? AND df.fingerprint = ?
ORDER BY df.created_at DESC, df.rowid DESC LIMIT 1`,
[productionId, platform || 'youtube', finding.fingerprint]
);
await this.executeQuery(
`INSERT INTO discoverability_findings (
id, audit_id, rule_id, category, severity, applicability,
message, remediation, fingerprint, review_status, review_reason, reviewed_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
this.generateId('finding'), auditId, finding.ruleId, finding.category,
finding.severity, JSON.stringify(finding.applicability || []), finding.message,
finding.remediation || null, finding.fingerprint,
previous?.review_status || 'pending', previous?.review_reason || null,
previous?.reviewed_at || null
]
);
}
await this.executeQuery('COMMIT');
} catch (error) {
await this.executeQuery('ROLLBACK');
throw error;
}
return this.getDiscoverabilityAudit(auditId);
}
async getDiscoverabilityAudit(auditId) {
const row = await this.getRow('SELECT * FROM discoverability_audits WHERE id = ?', [auditId]);
if (!row) return null;
const findings = await this.getAllRows(
'SELECT * FROM discoverability_findings WHERE audit_id = ? ORDER BY CASE severity WHEN \'CRITICAL\' THEN 0 WHEN \'HIGH\' THEN 1 WHEN \'MEDIUM\' THEN 2 WHEN \'LOW\' THEN 3 ELSE 4 END, created_at ASC',
[auditId]
);
return this.deserializeDiscoverabilityAudit(row, findings);
}
async getLatestDiscoverabilityAudit(productionId, platform = 'youtube') {
const row = await this.getRow(
`SELECT * FROM discoverability_audits
WHERE production_id = ? AND platform = ? ORDER BY created_at DESC, rowid DESC LIMIT 1`,
[productionId, platform]
);
if (!row) return null;
const findings = await this.getAllRows(
'SELECT * FROM discoverability_findings WHERE audit_id = ? ORDER BY CASE severity WHEN \'CRITICAL\' THEN 0 WHEN \'HIGH\' THEN 1 WHEN \'MEDIUM\' THEN 2 WHEN \'LOW\' THEN 3 ELSE 4 END, created_at ASC',
[row.id]
);
return this.deserializeDiscoverabilityAudit(row, findings);
}
deserializeDiscoverabilityAudit(row, findings = []) {
const parsedFindings = findings.map(finding => ({
...finding,
ruleId: finding.rule_id,
applicability: JSON.parse(finding.applicability || '[]'),
reviewStatus: finding.review_status,
reviewReason: finding.review_reason,
reviewedAt: finding.reviewed_at
}));
return {
...row,
productionId: row.production_id,
engineVersion: row.engine_version,
schemaVersion: row.schema_version,
errorCode: row.error_code,
summary: JSON.parse(row.summary || '{}'),
findings: parsedFindings,
pendingCount: parsedFindings.filter(finding => finding.reviewStatus === 'pending').length
};
}
async getDiscoverabilityFinding(findingId) {
const row = await this.getRow(
`SELECT df.*, da.production_id, da.platform
FROM discoverability_findings df
JOIN discoverability_audits da ON da.id = df.audit_id WHERE df.id = ?`,
[findingId]
);
return row ? {
...row,
ruleId: row.rule_id,
applicability: JSON.parse(row.applicability || '[]'),
reviewStatus: row.review_status,
reviewReason: row.review_reason,
reviewedAt: row.reviewed_at
} : null;
}
async reviewDiscoverabilityFinding(findingId, status, reason) {
await this.executeQuery(
`UPDATE discoverability_findings SET review_status = ?, review_reason = ?, reviewed_at = datetime('now')
WHERE id = ?`,
[status, reason || null, findingId]
);
return this.getDiscoverabilityFinding(findingId);
}
async getChannelProfile() {
const row = await this.getRow("SELECT * FROM channel_profiles WHERE id = 'default'");
return row ? { ...row, bannedTopics: JSON.parse(row.banned_topics || '[]') } : null;
}
async saveChannelProfile(profile) {
const current = await this.getChannelProfile() || {};
await this.executeQuery(
`INSERT OR REPLACE INTO channel_profiles (
id, channel_name, goal, target_audience, brand_voice, default_style,
call_to_action, banned_topics, visual_style, timezone, created_at, updated_at
) VALUES ('default', ?, ?, ?, ?, ?, ?, ?, ?, ?, COALESCE(?, datetime('now')), datetime('now'))`,
[
profile.channelName ?? current.channel_name ?? '',
profile.goal ?? current.goal ?? '',
profile.targetAudience ?? current.target_audience ?? '',
profile.brandVoice ?? current.brand_voice ?? '',
profile.defaultStyle ?? current.default_style ?? 'explainer',
profile.callToAction ?? current.call_to_action ?? '',
JSON.stringify(profile.bannedTopics ?? current.bannedTopics ?? []),
profile.visualStyle ?? current.visual_style ?? '',
profile.timezone ?? current.timezone ?? 'America/Chicago',
current.created_at || null
]
);
return this.getChannelProfile();
}
async createContentIdea(idea) {
const id = this.generateId('idea');
await this.executeQuery(
`INSERT INTO content_ideas (id, topic, angle, style, status, rationale, scheduled_for)
VALUES (?, ?, ?, ?, ?, ?, ?)`,
[id, idea.topic, idea.angle || null, idea.style || 'explainer', idea.status || 'backlog', idea.rationale || null, idea.scheduledFor || null]
);
return this.getRow('SELECT * FROM content_ideas WHERE id = ?', [id]);
}
async listContentIdeas() {
return this.getAllRows("SELECT * FROM content_ideas WHERE status != 'archived' ORDER BY COALESCE(scheduled_for, '9999-12-31'), created_at DESC");
}
async updateContentIdea(id, changes) {
const current = await this.getRow('SELECT * FROM content_ideas WHERE id = ?', [id]);
if (!current) return null;
await this.executeQuery(
`UPDATE content_ideas SET topic = ?, angle = ?, style = ?, status = ?,
rationale = ?, scheduled_for = ?, updated_at = datetime('now') WHERE id = ?`,
[
changes.topic ?? current.topic,
changes.angle ?? current.angle,
changes.style ?? current.style,
changes.status ?? current.status,
changes.rationale ?? current.rationale,
changes.scheduledFor === undefined ? current.scheduled_for : changes.scheduledFor,
id
]
);
return this.getRow('SELECT * FROM content_ideas WHERE id = ?', [id]);
}
async getChannelStrategy() {
const row = await this.getRow("SELECT * FROM channel_strategies WHERE id = 'default'");
return row ? this.deserializeChannelStrategy(row) : null;
}
async saveChannelStrategy(strategy) {
const current = await this.getChannelStrategy() || {};
await this.executeQuery(
`INSERT INTO channel_strategies (
id, objective, audience, value_proposition, content_pillars, cadence_per_week,
videos_per_run, default_format, default_length, success_metric, primary_kpi,
target_value, target_window_days, monthly_budget, outcome_currency, constraints,
status, created_at, updated_at
) VALUES ('default', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, COALESCE(?, datetime('now')), datetime('now'))
ON CONFLICT(id) DO UPDATE SET
objective = excluded.objective, audience = excluded.audience,
value_proposition = excluded.value_proposition, content_pillars = excluded.content_pillars,
cadence_per_week = excluded.cadence_per_week, videos_per_run = excluded.videos_per_run,
default_format = excluded.default_format, default_length = excluded.default_length,
success_metric = excluded.success_metric, primary_kpi = excluded.primary_kpi,
target_value = excluded.target_value, target_window_days = excluded.target_window_days,
monthly_budget = excluded.monthly_budget, outcome_currency = excluded.outcome_currency,
constraints = excluded.constraints,
status = excluded.status, updated_at = datetime('now')`,
[
strategy.objective ?? current.objective ?? '',
strategy.audience ?? current.audience ?? '',
strategy.valueProposition ?? current.value_proposition ?? '',
JSON.stringify(strategy.contentPillars ?? current.contentPillars ?? []),
strategy.cadencePerWeek ?? current.cadence_per_week ?? 1,
strategy.videosPerRun ?? current.videos_per_run ?? 1,
strategy.defaultFormat ?? current.default_format ?? 'explainer',
strategy.defaultLength ?? current.default_length ?? 'medium',
strategy.successMetric ?? current.success_metric ?? '',
strategy.primaryKpi ?? current.primary_kpi ?? 'views',
strategy.targetValue ?? current.target_value ?? null,
strategy.targetWindowDays ?? current.target_window_days ?? 28,
strategy.monthlyBudget ?? current.monthly_budget ?? null,
strategy.outcomeCurrency ?? current.outcome_currency ?? 'USD',
strategy.constraints ?? current.constraints ?? '',
strategy.status ?? current.status ?? 'draft',
current.created_at || null
]
);
return this.getChannelStrategy();
}
deserializeChannelStrategy(row) {
return {
...row,
contentPillars: JSON.parse(row.content_pillars || '[]')
};
}
async createOperatorRun(strategyId = 'default') {
const id = this.generateId('operator');
await this.executeQuery(
`INSERT INTO operator_runs (id, strategy_id, research, plan, generated_jobs, summary)
VALUES (?, ?, '{}', '[]', '[]', '{}')`,
[id, strategyId]
);
return this.getOperatorRun(id);
}
async getOperatorRun(id) {
const row = await this.getRow('SELECT * FROM operator_runs WHERE id = ?', [id]);
return row ? this.deserializeOperatorRun(row) : null;
}
async getActiveOperatorRun() {
const row = await this.getRow(
"SELECT * FROM operator_runs WHERE status IN ('queued', 'running', 'cancelling') ORDER BY created_at DESC LIMIT 1"
);
return row ? this.deserializeOperatorRun(row) : null;
}
async listOperatorRuns(limit = 10) {
const rows = await this.getAllRows('SELECT * FROM operator_runs ORDER BY created_at DESC LIMIT ?', [limit]);
return rows.map(row => this.deserializeOperatorRun(row));
}
async updateOperatorRun(id, changes = {}) {
const current = await this.getOperatorRun(id);
if (!current) return null;
await this.executeQuery(
`UPDATE operator_runs SET status = ?, stage = ?, progress = ?, research = ?, plan = ?,
generated_jobs = ?, summary = ?, error = ?, cancel_requested = ?,
updated_at = datetime('now'), completed_at = ? WHERE id = ?`,
[
changes.status ?? current.status,
changes.stage ?? current.stage,
changes.progress ?? current.progress,
JSON.stringify(changes.research ?? current.research ?? {}),
JSON.stringify(changes.plan ?? current.plan ?? []),
JSON.stringify(changes.generatedJobs ?? current.generatedJobs ?? []),
JSON.stringify(changes.summary ?? current.summary ?? {}),
changes.error === undefined ? current.error : changes.error,
changes.cancelRequested === undefined ? current.cancel_requested : Number(changes.cancelRequested),
changes.completedAt === undefined ? current.completed_at : changes.completedAt,
id
]
);
return this.getOperatorRun(id);
}
deserializeOperatorRun(row) {
return {
...row,
research: JSON.parse(row.research || '{}'),
plan: JSON.parse(row.plan || '[]'),
generatedJobs: JSON.parse(row.generated_jobs || '[]'),
summary: JSON.parse(row.summary || '{}'),
cancelRequested: Boolean(row.cancel_requested)
};
}
async createNotification(notification) {
const id = this.generateId('notice');
await this.executeQuery(
`INSERT INTO notifications (id, type, level, title, message, data)
VALUES (?, ?, ?, ?, ?, ?)`,
[id, notification.type || 'system', notification.level || 'info', notification.title, notification.message, JSON.stringify(notification.data || {})]
);
return id;
}
async listNotifications(limit = 20) {
const rows = await this.getAllRows('SELECT * FROM notifications ORDER BY created_at DESC LIMIT ?', [limit]);
return rows.map(row => ({ ...row, data: JSON.parse(row.data || '{}') }));
}
async markNotificationRead(id) {
await this.executeQuery("UPDATE notifications SET status = 'read' WHERE id = ?", [id]);
}
async getRecentAutomationEvents(limit = 30) {
const rows = await this.getAllRows('SELECT * FROM automation_events ORDER BY created_at DESC LIMIT ?', [limit]);
return rows.map(row => ({ ...row, data: JSON.parse(row.data || '{}') }));
}
// Publishing methods
async saveScheduleEntry(entry) {
const existing = await this.getLatestScheduleEntry(entry.productionId);
if (existing) return existing;
const id = this.generateId('schedule');
entry.id = id;
await this.executeQuery(
`INSERT INTO publish_schedule (
id, production_id, title, publish_time, status,
priority, metadata
) VALUES (?, ?, ?, ?, ?, ?, ?)`,
[
id,
entry.productionId,
entry.title,
entry.publishTime,
entry.status,
entry.priority,
JSON.stringify(entry.metadata)
]
);
return entry;
}
async updateScheduleEntry(entry) {
await this.executeQuery(
`UPDATE publish_schedule SET
title = COALESCE(?, title), publish_time = COALESCE(?, publish_time),
status = ?, priority = COALESCE(?, priority), metadata = COALESCE(?, metadata),
youtube_id = ?, youtube_url = ?, published_at = ?, error_message = ?
WHERE id = ?`,
[
entry.title || null,
entry.publishTime || entry.publish_time || null,
entry.status,
entry.priority ?? null,
entry.metadata ? JSON.stringify(entry.metadata) : null,
entry.youtubeId || null,
entry.youtubeUrl || null,
entry.publishedAt || null,
entry.error || null,
entry.id
]
);
}
async deleteScheduleEntry(id) {
await this.executeQuery('DELETE FROM publish_schedule WHERE id = ?', [id]);
}
async getLatestScheduleEntry(productionId) {
const row = await this.getRow(
'SELECT * FROM publish_schedule WHERE production_id = ? ORDER BY created_at DESC LIMIT 1',
[productionId]
);
return row ? this.deserializeScheduleEntry(row) : null;
}
deserializeScheduleEntry(row) {
return {
...row,
productionId: row.production_id,
publishTime: row.publish_time,
youtubeId: row.youtube_id,
youtubeUrl: row.youtube_url,
publishedAt: row.published_at,
error: row.error_message,
metadata: JSON.parse(row.metadata || '{}')
};
}
async getPublishQueue() {
const rows = await this.getAllRows(
`SELECT * FROM publish_schedule
WHERE status IN ('scheduled', 'paused')
ORDER BY publish_time ASC`
);
return rows.map(row => this.deserializeScheduleEntry(row));
}
async getUpcomingSchedule(days = 7) {
const endDate = new Date();
endDate.setDate(endDate.getDate() + days);
const rows = await this.getAllRows(
`SELECT * FROM publish_schedule
WHERE publish_time BETWEEN datetime('now') AND datetime(?)
ORDER BY publish_time ASC`,
[endDate.toISOString()]
);
return rows.map(row => this.deserializeScheduleEntry(row));
}
// Analytics methods
async saveAnalyticsReport(report) {
const id = this.generateId('analytics');
await this.executeQuery(
`INSERT INTO analytics_reports (
id, video_id, youtube_id, video_details, analytics_data,
thumbnail_metrics, seo_metrics, insights, performance_score,
performance_grade
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
id,
report.videoId,
report.youtubeId || null,
JSON.stringify(report.videoDetails),
JSON.stringify(report.analytics),
JSON.stringify(report.thumbnailMetrics),
JSON.stringify(report.seoMetrics),
JSON.stringify(report.insights),
report.performance.score,
report.performance.grade
]
);
return id;
}
async getAnalyticsHistory() {
const rows = await this.getAllRows(
'SELECT * FROM analytics_reports ORDER BY analyzed_at DESC'
);
return rows.map(row => ({
...row,
videoDetails: JSON.parse(row.video_details || '{}'),
analytics: JSON.parse(row.analytics_data || '{}'),
thumbnailMetrics: JSON.parse(row.thumbnail_metrics || '{}'),
seoMetrics: JSON.parse(row.seo_metrics || '{}'),
insights: JSON.parse(row.insights || '[]')
}));
}
async savePerformanceSnapshot(snapshot) {
const existing = await this.getRow(
'SELECT id FROM performance_snapshots WHERE video_id = ? AND measurement_window = ?',
[snapshot.videoId, snapshot.measurementWindow]
);
const id = existing?.id || this.generateId('snapshot');
await this.executeQuery(
`INSERT INTO performance_snapshots (
id, video_id, production_id, measurement_window, published_at, metrics,
content_attributes, baseline, deltas, confidence, simulated, measured_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(video_id, measurement_window) DO UPDATE SET
production_id = excluded.production_id,
published_at = excluded.published_at,
metrics = excluded.metrics,
content_attributes = excluded.content_attributes,
baseline = excluded.baseline,
deltas = excluded.deltas,
confidence = excluded.confidence,
simulated = excluded.simulated,
measured_at = excluded.measured_at`,
[
id,
snapshot.videoId,
snapshot.productionId || null,
snapshot.measurementWindow,
snapshot.publishedAt || null,
JSON.stringify(snapshot.metrics || {}),
JSON.stringify(snapshot.contentAttributes || {}),
JSON.stringify(snapshot.baseline || {}),
JSON.stringify(snapshot.deltas || {}),
snapshot.confidence || 'low',
snapshot.simulated ? 1 : 0,
snapshot.measuredAt || new Date().toISOString()
]
);
return this.getPerformanceSnapshot(id);
}
async getPerformanceSnapshot(id) {
const row = await this.getRow('SELECT * FROM performance_snapshots WHERE id = ?', [id]);
return this.parsePerformanceSnapshot(row);
}
async listPerformanceSnapshots(options = {}) {
const conditions = [];
const params = [];
if (options.videoId) {
conditions.push('video_id = ?');
params.push(options.videoId);
}
if (options.excludeVideoId) {
conditions.push('video_id != ?');
params.push(options.excludeVideoId);
}
if (options.measurementWindow) {
conditions.push('measurement_window = ?');
params.push(options.measurementWindow);
}
if (options.reliableOnly) conditions.push('simulated = 0');
const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';
const rows = await this.getAllRows(
`SELECT * FROM performance_snapshots ${where} ORDER BY measured_at DESC`,
params
);
return rows.map(row => this.parsePerformanceSnapshot(row));
}
parsePerformanceSnapshot(row) {
if (!row) return null;
return {
...row,
videoId: row.video_id,
productionId: row.production_id,
measurementWindow: row.measurement_window,
publishedAt: row.published_at,
measuredAt: row.measured_at,
simulated: Boolean(row.simulated),
metrics: JSON.parse(row.metrics || '{}'),
contentAttributes: JSON.parse(row.content_attributes || '{}'),
baseline: JSON.parse(row.baseline || '{}'),
deltas: JSON.parse(row.deltas || '{}')
};
}
async saveRetentionSnapshot(snapshot) {
const existing = await this.getRow(
'SELECT id FROM retention_snapshots WHERE video_id = ? AND measurement_window = ?',
[snapshot.videoId, snapshot.measurementWindow]
);
const id = existing?.id || this.generateId('retention');
await this.executeQuery(
`INSERT INTO retention_snapshots (
id, video_id, production_id, short_clip_id, title, surface,
measurement_window, published_at, duration_seconds, points,
scene_metrics, summary, confidence, measured_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(video_id, measurement_window) DO UPDATE SET
production_id = excluded.production_id,
short_clip_id = excluded.short_clip_id,
title = excluded.title,
surface = excluded.surface,
published_at = excluded.published_at,
duration_seconds = excluded.duration_seconds,
points = excluded.points,
scene_metrics = excluded.scene_metrics,
summary = excluded.summary,
confidence = excluded.confidence,
measured_at = excluded.measured_at`,
[
id,
snapshot.videoId,
snapshot.productionId || null,
snapshot.shortClipId || null,
snapshot.title || null,
snapshot.surface || 'long_form',
snapshot.measurementWindow,
snapshot.publishedAt || null,
Number(snapshot.durationSeconds || 0),
JSON.stringify(snapshot.points || []),
JSON.stringify(snapshot.sceneMetrics || []),
JSON.stringify(snapshot.summary || {}),
snapshot.confidence || 'low',
snapshot.measuredAt || new Date().toISOString()
]
);
return this.getRetentionSnapshot(id);
}
async getRetentionSnapshot(id) {
const row = await this.getRow('SELECT * FROM retention_snapshots WHERE id = ?', [id]);
return this.parseRetentionSnapshot(row);
}
async listRetentionSnapshots(options = {}) {
const conditions = [];
const params = [];
if (options.videoId) {
conditions.push('video_id = ?');
params.push(options.videoId);
}
if (options.surface) {
conditions.push('surface = ?');
params.push(options.surface);
}
if (options.measurementWindow) {
conditions.push('measurement_window = ?');
params.push(options.measurementWindow);
}
const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';
const limit = Math.max(1, Math.min(50, Number(options.limit || 12)));
const rows = await this.getAllRows(
`SELECT * FROM retention_snapshots ${where} ORDER BY measured_at DESC LIMIT ?`,
[...params, limit]
);
return rows.map(row => this.parseRetentionSnapshot(row));
}
parseRetentionSnapshot(row) {
if (!row) return null;
return {
...row,
videoId: row.video_id,
productionId: row.production_id,
shortClipId: row.short_clip_id,
measurementWindow: row.measurement_window,
publishedAt: row.published_at,
durationSeconds: Number(row.duration_seconds || 0),
measuredAt: row.measured_at,
points: JSON.parse(row.points || '[]'),
sceneMetrics: JSON.parse(row.scene_metrics || '[]'),
summary: JSON.parse(row.summary || '{}')
};
}
async saveLearningRecommendation(recommendation) {
const existing = await this.getRow(
'SELECT id FROM learning_recommendations WHERE fingerprint = ?',
[recommendation.fingerprint]
);
const id = existing?.id || this.generateId('learning');
await this.executeQuery(
`INSERT INTO learning_recommendations (
id, fingerprint, category, title, rationale, evidence, proposed_change, confidence
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(fingerprint) DO UPDATE SET
category = excluded.category,
title = excluded.title,
rationale = excluded.rationale,
evidence = excluded.evidence,
proposed_change = excluded.proposed_change,
confidence = excluded.confidence,
updated_at = CURRENT_TIMESTAMP`,
[
id,
recommendation.fingerprint,
recommendation.category,
recommendation.title,
recommendation.rationale,
JSON.stringify(recommendation.evidence || {}),
JSON.stringify(recommendation.proposedChange || {}),
recommendation.confidence || 'low'
]
);
return this.getLearningRecommendation(id);
}
async getLearningRecommendation(id) {
const row = await this.getRow('SELECT * FROM learning_recommendations WHERE id = ?', [id]);
return this.parseLearningRecommendation(row);
}
async listLearningRecommendations(options = {}) {
const conditions = [];
const params = [];
if (options.status) {
conditions.push('status = ?');
params.push(options.status);
}
const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';
const limit = Math.max(1, Math.min(100, Number(options.limit || 25)));
const rows = await this.getAllRows(
`SELECT * FROM learning_recommendations ${where}
ORDER BY CASE status WHEN 'pending' THEN 0 WHEN 'approved' THEN 1 ELSE 2 END,
updated_at DESC LIMIT ?`,
[...params, limit]
);
return rows.map(row => this.parseLearningRecommendation(row));
}
async reviewLearningRecommendation(id, status) {
if (!['approved', 'rejected'].includes(status)) return null;
await this.executeQuery(
`UPDATE learning_recommendations
SET status = ?, reviewed_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
WHERE id = ?`,
[status, id]
);
return this.getLearningRecommendation(id);
}
parseLearningRecommendation(row) {
if (!row) return null;
return {
...row,
proposedChange: JSON.parse(row.proposed_change || '{}'),
evidence: JSON.parse(row.evidence || '{}'),
createdAt: row.created_at,
updatedAt: row.updated_at,
reviewedAt: row.reviewed_at
};
}
async createGrowthExperiment(experiment, arms) {
const id = experiment.id || this.generateId('experiment');
await this.executeQuery(
`INSERT INTO growth_experiments (
id, production_id, video_id, recommendation_id, title, hypothesis,
primary_metric, status, arm_duration_hours, min_impressions, guardrails
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
id, experiment.productionId, experiment.videoId, experiment.recommendationId || null,
experiment.title, experiment.hypothesis, experiment.primaryMetric || 'ctr',
experiment.status || 'draft', experiment.armDurationHours || 48,
experiment.minImpressions || 1000, JSON.stringify(experiment.guardrails || {})
]
);
for (const [index, arm] of arms.entries()) {
await this.executeQuery(
`INSERT INTO experiment_arms (
id, experiment_id, arm_index, label, title, thumbnail_path, is_control
) VALUES (?, ?, ?, ?, ?, ?, ?)`,
[
arm.id || this.generateId('arm'), id, index, arm.label,
arm.title, arm.thumbnailPath, arm.isControl ? 1 : 0
]
);
}
return this.getGrowthExperiment(id);
}
async getGrowthExperiment(id) {
const row = await this.getRow('SELECT * FROM growth_experiments WHERE id = ?', [id]);
if (!row) return null;
const arms = await this.getAllRows(
'SELECT * FROM experiment_arms WHERE experiment_id = ? ORDER BY arm_index ASC',
[id]
);
return this.parseGrowthExperiment(row, arms);
}
async listGrowthExperiments(options = {}) {
const conditions = [];
const params = [];
if (options.status) {
const statuses = Array.isArray(options.status) ? options.status : [options.status];
conditions.push(`status IN (${statuses.map(() => '?').join(', ')})`);
params.push(...statuses);
}
if (options.productionId) {
conditions.push('production_id = ?');
params.push(options.productionId);
}
const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';
const limit = Math.max(1, Math.min(100, Number(options.limit || 25)));
const rows = await this.getAllRows(
`SELECT * FROM growth_experiments ${where} ORDER BY updated_at DESC LIMIT ?`,
[...params, limit]
);
return Promise.all(rows.map(async row => {
const arms = await this.getAllRows(
'SELECT * FROM experiment_arms WHERE experiment_id = ? ORDER BY arm_index ASC',
[row.id]
);
return this.parseGrowthExperiment(row, arms);
}));
}
async updateGrowthExperiment(id, changes = {}) {
const allowed = {
status: 'status', currentArmId: 'current_arm_id', winningArmId: 'winning_arm_id',
result: 'result', approvedAt: 'approved_at', startedAt: 'started_at',
completedAt: 'completed_at', adoptedAt: 'adopted_at', cancelledAt: 'cancelled_at'
};
const assignments = [];
const params = [];
for (const [key, column] of Object.entries(allowed)) {
if (changes[key] === undefined) continue;
assignments.push(`${column} = ?`);
params.push(key === 'result' ? JSON.stringify(changes[key] || {}) : changes[key]);
}
if (!assignments.length) return this.getGrowthExperiment(id);
assignments.push('updated_at = CURRENT_TIMESTAMP');
await this.executeQuery(
`UPDATE growth_experiments SET ${assignments.join(', ')} WHERE id = ?`,
[...params, id]
);
return this.getGrowthExperiment(id);
}
async updateExperimentArm(id, changes = {}) {
const allowed = {
status: 'status', baselineMetrics: 'baseline_metrics', finalMetrics: 'final_metrics',
result: 'result', startedAt: 'started_at', endedAt: 'ended_at'
};
const assignments = [];
const params = [];
for (const [key, column] of Object.entries(allowed)) {
if (changes[key] === undefined) continue;
assignments.push(`${column} = ?`);
params.push(['baselineMetrics', 'finalMetrics', 'result'].includes(key)
? JSON.stringify(changes[key] || {})
: changes[key]);
}
if (!assignments.length) return null;
assignments.push('updated_at = CURRENT_TIMESTAMP');
await this.executeQuery(`UPDATE experiment_arms SET ${assignments.join(', ')} WHERE id = ?`, [...params, id]);
const row = await this.getRow('SELECT * FROM experiment_arms WHERE id = ?', [id]);
return this.parseExperimentArm(row);
}
async saveExperimentSample(sample) {
const id = this.generateId('sample');
await this.executeQuery(
`INSERT INTO experiment_samples (
id, experiment_id, arm_id, metrics, traffic_sources, captured_at
) VALUES (?, ?, ?, ?, ?, ?)`,
[
id, sample.experimentId, sample.armId, JSON.stringify(sample.metrics || {}),
JSON.stringify(sample.trafficSources || []), sample.capturedAt || new Date().toISOString()
]
);
return id;
}
async listExperimentSamples(experimentId, limit = 250) {
const rows = await this.getAllRows(
`SELECT * FROM experiment_samples WHERE experiment_id = ?
ORDER BY captured_at ASC LIMIT ?`,
[experimentId, Math.max(1, Math.min(1000, Number(limit || 250)))]
);
return rows.map(row => ({
...row,
experimentId: row.experiment_id,
armId: row.arm_id,
capturedAt: row.captured_at,
metrics: JSON.parse(row.metrics || '{}'),
trafficSources: JSON.parse(row.traffic_sources || '[]')
}));
}
parseExperimentArm(row) {
if (!row) return null;
return {
...row,
experimentId: row.experiment_id,
index: Number(row.arm_index),
thumbnailPath: row.thumbnail_path,
isControl: Boolean(row.is_control),
baselineMetrics: JSON.parse(row.baseline_metrics || '{}'),
finalMetrics: JSON.parse(row.final_metrics || '{}'),
result: JSON.parse(row.result || '{}'),
startedAt: row.started_at,
endedAt: row.ended_at
};
}
parseGrowthExperiment(row, arms = []) {
return {
...row,
productionId: row.production_id,
videoId: row.video_id,
recommendationId: row.recommendation_id,
primaryMetric: row.primary_metric,
armDurationHours: Number(row.arm_duration_hours),
minImpressions: Number(row.min_impressions),
currentArmId: row.current_arm_id,
winningArmId: row.winning_arm_id,
approvedAt: row.approved_at,
startedAt: row.started_at,
completedAt: row.completed_at,
adoptedAt: row.adopted_at,
cancelledAt: row.cancelled_at,
guardrails: JSON.parse(row.guardrails || '{}'),
result: JSON.parse(row.result || '{}'),
arms: arms.map(arm => this.parseExperimentArm(arm))
};
}
async upsertAudienceComment(comment) {
const existing = await this.getRow(
'SELECT id FROM audience_comments WHERE comment_id = ?',
[comment.commentId]
);
const id = existing?.id || this.generateId('comment');
await this.executeQuery(
`INSERT INTO audience_comments (
id, comment_id, video_id, parent_comment_id, author_name, author_channel_id,
is_channel_owner, text, like_count, reply_count, published_at, updated_at_youtube
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(comment_id) DO UPDATE SET
text = excluded.text,
like_count = excluded.like_count,
reply_count = excluded.reply_count,
updated_at_youtube = excluded.updated_at_youtube,
updated_at = CURRENT_TIMESTAMP`,
[
id,
comment.commentId,
comment.videoId,
comment.parentCommentId || null,
comment.authorName || null,
comment.authorChannelId || null,
comment.isChannelOwner ? 1 : 0,
comment.text,
Number(comment.likeCount || 0),
Number(comment.replyCount || 0),
comment.publishedAt || null,
comment.updatedAtYouTube || null
]
);
return this.getAudienceComment(comment.commentId);
}
async getAudienceComment(commentId) {
const row = await this.getRow('SELECT * FROM audience_comments WHERE comment_id = ?', [commentId]);
return this.parseAudienceComment(row);
}
async listAudienceComments(options = {}) {
const conditions = ['video_id = ?'];
const params = [options.videoId];
if (options.topLevelOnly) conditions.push('parent_comment_id IS NULL');
if (options.analysisState) {
conditions.push('analysis_state = ?');
params.push(options.analysisState);
}
const limit = Math.max(1, Math.min(500, Number(options.limit || 200)));
const rows = await this.getAllRows(
`SELECT * FROM audience_comments WHERE ${conditions.join(' AND ')}
ORDER BY like_count DESC, published_at DESC LIMIT ?`,
[...params, limit]
);
return rows.map(row => this.parseAudienceComment(row));
}
async countAudienceComments(videoId) {
const row = await this.getRow(
`SELECT COUNT(*) AS total,
SUM(CASE WHEN parent_comment_id IS NULL THEN 1 ELSE 0 END) AS top_level
FROM audience_comments WHERE video_id = ?`,
[videoId]
);
return { total: Number(row?.total || 0), topLevel: Number(row?.top_level || 0) };
}
async setAudienceCommentAnalysis(commentId, flags) {
await this.executeQuery(
`UPDATE audience_comments
SET flags = ?, analysis_state = 'analyzed', updated_at = CURRENT_TIMESTAMP
WHERE comment_id = ?`,
[JSON.stringify(flags || []), commentId]
);
return this.getAudienceComment(commentId);
}
async markAudienceCommentReplied(commentId) {
await this.executeQuery(
`UPDATE audience_comments SET replied_by_agent = 1, updated_at = CURRENT_TIMESTAMP WHERE comment_id = ?`,
[commentId]
);
return this.getAudienceComment(commentId);
}
parseAudienceComment(row) {
if (!row) return null;
return {
...row,
commentId: row.comment_id,
videoId: row.video_id,
parentCommentId: row.parent_comment_id,
authorName: row.author_name,
authorChannelId: row.author_channel_id,
isChannelOwner: Boolean(row.is_channel_owner),
likeCount: Number(row.like_count || 0),
replyCount: Number(row.reply_count || 0),
publishedAt: row.published_at,
updatedAtYouTube: row.updated_at_youtube,
flags: JSON.parse(row.flags || '[]'),
analysisState: row.analysis_state,
repliedByAgent: Boolean(row.replied_by_agent)
};
}
async saveEngagementInsight(insight) {
const existing = await this.getEngagementInsight(insight.videoId);
const merged = { ...(existing || {}), ...insight };
const id = existing?.id || this.generateId('insight');
await this.executeQuery(
`INSERT INTO engagement_insights (
id, video_id, production_id, title, comment_count, analyzed_count,
sentiment, themes, attention_flags, analysis_method, analyzed_at,
last_synced_at, newest_comment_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(video_id) DO UPDATE SET
production_id = excluded.production_id,
title = excluded.title,
comment_count = excluded.comment_count,
analyzed_count = excluded.analyzed_count,
sentiment = excluded.sentiment,
themes = excluded.themes,
attention_flags = excluded.attention_flags,
analysis_method = excluded.analysis_method,
analyzed_at = excluded.analyzed_at,
last_synced_at = excluded.last_synced_at,
newest_comment_at = excluded.newest_comment_at,
updated_at = CURRENT_TIMESTAMP`,
[
id,
insight.videoId,
merged.productionId || null,
merged.title || null,
Number(merged.commentCount || 0),
Number(merged.analyzedCount || 0),
JSON.stringify(merged.sentiment || {}),
JSON.stringify(merged.themes || []),
JSON.stringify(merged.attentionFlags || []),
merged.analysisMethod || 'ai',
merged.analyzedAt || null,
merged.lastSyncedAt || null,
merged.newestCommentAt || null
]
);
return this.getEngagementInsight(insight.videoId);
}
async getEngagementInsight(videoId) {
const row = await this.getRow('SELECT * FROM engagement_insights WHERE video_id = ?', [videoId]);
return this.parseEngagementInsight(row);
}
async listEngagementInsights(options = {}) {
const limit = Math.max(1, Math.min(50, Number(options.limit || 12)));
const rows = await this.getAllRows(
'SELECT * FROM engagement_insights ORDER BY updated_at DESC LIMIT ?',
[limit]
);
return rows.map(row => this.parseEngagementInsight(row));
}
parseEngagementInsight(row) {
if (!row) return null;
return {
...row,
videoId: row.video_id,
productionId: row.production_id,
commentCount: Number(row.comment_count || 0),
analyzedCount: Number(row.analyzed_count || 0),
sentiment: JSON.parse(row.sentiment || '{}'),
themes: JSON.parse(row.themes || '[]'),
attentionFlags: JSON.parse(row.attention_flags || '[]'),
analysisMethod: row.analysis_method,
analyzedAt: row.analyzed_at,
lastSyncedAt: row.last_synced_at,
newestCommentAt: row.newest_comment_at
};
}
async saveReplyDraft(draft) {
const existing = await this.getRow('SELECT id, status FROM reply_drafts WHERE comment_id = ?', [draft.commentId]);
if (existing?.status === 'posted') {
const error = new Error('A posted reply cannot be replaced');
error.status = 409;
throw error;
}
const id = existing?.id || this.generateId('reply');
await this.executeQuery(
`INSERT INTO reply_drafts (id, comment_id, video_id, draft_text, rationale)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT(comment_id) DO UPDATE SET
draft_text = excluded.draft_text,
rationale = excluded.rationale,
edited_text = NULL,
status = 'proposed',
posted_comment_id = NULL,
posted_at = NULL,
failure_reason = NULL,
updated_at = CURRENT_TIMESTAMP`,
[id, draft.commentId, draft.videoId, draft.draftText, draft.rationale || null]
);
return this.getReplyDraft(id);
}
async getReplyDraft(id) {
const row = await this.getRow('SELECT * FROM reply_drafts WHERE id = ?', [id]);
return this.parseReplyDraft(row);
}
async listReplyDrafts(options = {}) {
const conditions = [];
const params = [];
if (options.videoId) {
conditions.push('video_id = ?');
params.push(options.videoId);
}
if (options.status) {
conditions.push('status = ?');
params.push(options.status);
}
const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';
const limit = Math.max(1, Math.min(100, Number(options.limit || 50)));
const rows = await this.getAllRows(
`SELECT * FROM reply_drafts ${where} ORDER BY updated_at DESC LIMIT ?`,
[...params, limit]
);
return rows.map(row => this.parseReplyDraft(row));
}
async updateReplyDraft(id, changes = {}) {
const columns = {
editedText: 'edited_text',
status: 'status',
postedCommentId: 'posted_comment_id',
postedAt: 'posted_at',
failureReason: 'failure_reason'
};
const sets = [];
const params = [];
for (const [key, column] of Object.entries(columns)) {
if (key in changes) {
sets.push(`${column} = ?`);
params.push(changes[key]);
}
}
if (sets.length) {
await this.executeQuery(
`UPDATE reply_drafts SET ${sets.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
[...params, id]
);
}
return this.getReplyDraft(id);
}
async countReplyDraftsPostedSince(isoTime) {
const row = await this.getRow(
"SELECT COUNT(*) AS posted FROM reply_drafts WHERE status = 'posted' AND posted_at >= ?",
[isoTime]
);
return Number(row?.posted || 0);
}
parseReplyDraft(row) {
if (!row) return null;
return {
...row,
commentId: row.comment_id,
videoId: row.video_id,
draftText: row.draft_text,
editedText: row.edited_text,
postedCommentId: row.posted_comment_id,
postedAt: row.posted_at,
failureReason: row.failure_reason
};
}
async getPublishedContentContext(youtubeId) {
const schedule = await this.getRow(
`SELECT production_id, published_at, title, metadata
FROM publish_schedule WHERE youtube_id = ? ORDER BY published_at DESC LIMIT 1`,
[youtubeId]
);
if (!schedule) return {};
const metadata = JSON.parse(schedule.metadata || '{}');
const sourceProductionId = metadata.sourceProductionId || schedule.production_id;
const row = await this.getRow(
`SELECT ps.strategy, ps.script, ps.thumbnail, ps.seo, cr.editor_data
FROM production_snapshots ps
LEFT JOIN content_reviews cr ON cr.production_id = ps.production_id
WHERE ps.production_id = ?`,
[sourceProductionId]
) || {};
const editorData = JSON.parse(row.editor_data || '{}');
const thumbnail = JSON.parse(row.thumbnail || '{}');
const selectedThumbnail = editorData.packagingExperiment?.thumbnailVariants?.[editorData.selectedThumbnailVariant];
const isShort = metadata.contentType === 'short';
const strategy = JSON.parse(row.strategy || '{}');
const sourceScenes = await this.listProductionScenes(sourceProductionId);
const shortClip = isShort && metadata.shortClipId ? await this.getShortClip(metadata.shortClipId) : null;
return {
productionId: sourceProductionId,
shortClipId: metadata.shortClipId || null,
contentFormat: isShort ? 'short' : 'long_form',
publishedAt: schedule.published_at,
title: isShort ? schedule.title : editorData.title || schedule.title,
strategy: isShort
? { ...strategy, contentType: 'shorts', requestedStyle: 'shorts', requestedLengthKey: 'short' }
: strategy,
script: JSON.parse(row.script || '{}'),
thumbnail: selectedThumbnail?.concept ? { ...thumbnail, concept: selectedThumbnail.concept } : thumbnail,
seo: JSON.parse(row.seo || '{}'),
productionCost: this.summarizeProductionCost(sourceScenes),
retentionScenes: this.buildRetentionSceneContext(sourceScenes, shortClip),
retentionDuration: isShort ? shortClip?.duration || null : sourceScenes.reduce((sum, scene) => sum + Number(scene.duration || 0), 0)
};
}
summarizeProductionCost(scenes = []) {
const entries = scenes.flatMap(scene => [scene.actualCost, scene.narrationCost]).filter(entry => entry && (
entry.amount !== undefined || entry.billed !== undefined || entry.invoiceRequired || entry.provider || entry.generatedSeconds
));
const currencies = new Set();
const providers = new Set();
let amount = 0;
let knownEntries = 0;
let unknownEntries = 0;
let freeEntries = 0;
for (const entry of entries) {
if (entry.provider) providers.add(String(entry.provider));
if (entry.billed === false) {
freeEntries++;
continue;
}
const numericAmount = Number(entry.amount);
if (Number.isFinite(numericAmount) && numericAmount >= 0) {
amount += numericAmount;
knownEntries++;
if (entry.currency) currencies.add(String(entry.currency).toUpperCase());
} else if (entry.invoiceRequired || entry.billed !== false) {
unknownEntries++;
}
}
return {
amount: currencies.size > 1 ? null : knownEntries ? Number(amount.toFixed(4)) : freeEntries && unknownEntries === 0 ? 0 : null,
currency: currencies.size === 1 ? [...currencies][0] : null,
complete: unknownEntries === 0 && currencies.size <= 1,
knownEntries,
unknownEntries,
freeEntries,
providers: [...providers]
};
}
buildRetentionSceneContext(scenes = [], shortClip = null) {
let cursor = 0;
const timeline = scenes.map(scene => {
const duration = Math.max(0, Number(scene.duration || 0));
const item = { ...scene, startSeconds: cursor, endSeconds: cursor + duration };
cursor += duration;
return item;
});
if (!shortClip) return timeline;
const clipStart = Math.max(0, Number(shortClip.startSeconds || 0));
const clipEnd = clipStart + Math.max(0, Number(shortClip.duration || 0));
const allowed = new Set(shortClip.sourceSceneIds || []);
return timeline.flatMap(scene => {
if (allowed.size && !allowed.has(scene.id)) return [];
const start = Math.max(scene.startSeconds, clipStart);
const end = Math.min(scene.endSeconds, clipEnd);
if (end <= start) return [];
return [{
...scene,
sourceStartSeconds: start,
startSeconds: start - clipStart,
endSeconds: end - clipStart,
duration: end - start
}];
});
}
// Keyword performance
async updateKeywordPerformance(keyword, views, videoId) {
const existing = await this.getRow(
'SELECT * FROM keyword_performance WHERE keyword = ?',
[keyword]
);
if (existing) {
await this.executeQuery(
`UPDATE keyword_performance SET
total_uses = total_uses + 1,
total_views = total_views + ?,
average_views = (total_views + ?) / (total_uses + 1),
best_performing_video = CASE
WHEN ? > (total_views / total_uses) THEN ?
ELSE best_performing_video
END,
last_used = datetime('now')
WHERE keyword = ?`,
[views, views, views, videoId, keyword]
);
} else {
await this.executeQuery(
`INSERT INTO keyword_performance (
keyword, total_uses, total_views, average_views,
best_performing_video, last_used, performance_score
) VALUES (?, 1, ?, ?, ?, datetime('now'), ?)`,
[keyword, views, views, videoId, Math.min(100, views / 1000)]
);
}
}
async getKeywordHistory() {
const rows = await this.getAllRows(
'SELECT * FROM keyword_performance ORDER BY performance_score DESC'
);
return rows;
}
// Settings
async getSetting(key) {
const row = await this.getRow(
'SELECT value FROM settings WHERE key = ?',
[key]
);
return row ? row.value : null;
}
async setSetting(key, value, description = null) {
await this.executeQuery(
`INSERT OR REPLACE INTO settings (key, value, description, updated_at)
VALUES (?, ?, COALESCE(?, (SELECT description FROM settings WHERE key = ?)), datetime('now'))`,
[key, value, description, key]
);
}
async getAllSettings() {
const rows = await this.getAllRows('SELECT * FROM settings ORDER BY key');
return rows.reduce((settings, row) => {
settings[row.key] = row.value;
return settings;
}, {});
}
// Production readiness
async saveReadinessRun(run) {
await this.executeQuery(
`INSERT OR REPLACE INTO readiness_runs (
id, status, checks, summary, started_at, completed_at
) VALUES (?, ?, ?, ?, ?, ?)`,
[
run.id,
run.status,
JSON.stringify(run.checks || []),
JSON.stringify(run.summary || {}),
run.startedAt,
run.completedAt
]
);
return this.getReadinessRun(run.id);
}
async getReadinessRun(id) {
const row = await this.getRow('SELECT * FROM readiness_runs WHERE id = ?', [id]);
return this.parseReadinessRun(row);
}
async getLatestReadinessRun() {
const row = await this.getRow('SELECT * FROM readiness_runs ORDER BY completed_at DESC LIMIT 1');
return this.parseReadinessRun(row);
}
parseReadinessRun(row) {
if (!row) return null;
return {
...row,
checks: JSON.parse(row.checks || '[]'),
summary: JSON.parse(row.summary || '{}')
};
}
// Utility methods
generateId(prefix) {
return `${prefix}_${Date.now()}_${Math.random().toString(36).substring(7)}`;
}
async executeQuery(query, params = []) {
return new Promise((resolve, reject) => {
this.db.run(query, params, function(error) {
if (error) {
reject(error);
} else {
resolve({ lastID: this.lastID, changes: this.changes });
}
});
});
}
async getRow(query, params = []) {
return new Promise((resolve, reject) => {
this.db.get(query, params, (error, row) => {
if (error) {
reject(error);
} else {
resolve(row);
}
});
});
}
async getAllRows(query, params = []) {
return new Promise((resolve, reject) => {
this.db.all(query, params, (error, rows) => {
if (error) {
reject(error);
} else {
resolve(rows || []);
}
});
});
}
async close() {
if (this.db) {
return new Promise((resolve) => {
this.db.close((error) => {
if (error) {
this.logger.error('Error closing database:', error);
}
resolve();
});
});
}
}
async backup() {
try {
const backupPath = path.join(
path.dirname(this.dbPath),
`backup_${Date.now()}.db`
);
const fs = require('fs').promises;
await fs.copyFile(this.dbPath, backupPath);
this.logger.info(`Database backed up to: ${backupPath}`);
return backupPath;
} catch (error) {
this.logger.error('Database backup failed:', error);
throw error;
}
}
async getStats() {
const [
strategiesCount,
scriptsCount,
productionsCount,
publishedCount,
analyticsCount
] = await Promise.all([
this.getRow('SELECT COUNT(*) as count FROM content_strategies'),
this.getRow('SELECT COUNT(*) as count FROM scripts'),
this.getRow('SELECT COUNT(*) as count FROM productions'),
this.getRow('SELECT COUNT(*) as count FROM publish_schedule WHERE status = "published"'),
this.getRow('SELECT COUNT(*) as count FROM analytics_reports')
]);
return {
strategies: strategiesCount.count,
scripts: scriptsCount.count,
productions: productionsCount.count,
published: publishedCount.count,
analytics: analyticsCount.count,
dbSize: await this.getDatabaseSize()
};
}
async getDatabaseSize() {
try {
const fs = require('fs').promises;
const stats = await fs.stat(this.dbPath);
return `${(stats.size / 1024 / 1024).toFixed(2)} MB`;
} catch (error) {
return 'Unknown';
}
}
}
module.exports = { Database };