e6f3e9387e
New blueprint-based module system (hm_qc, video_qc, video_master, reporting), core framework (database, config, templates), and unified web interface with progress tracking and tab navigation. Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
39 lines
2 KiB
SQL
39 lines
2 KiB
SQL
-- QC Platform Database Schema
|
|
-- SQLite database for unified HM QC platform
|
|
|
|
-- QC Reports table
|
|
-- Stores metadata for reports generated by HM QC and other modules
|
|
CREATE TABLE IF NOT EXISTS qc_reports (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
report_type VARCHAR(50) NOT NULL, -- 'hm_qc', 'video_qc', 'video_master', 'box_import'
|
|
job_number VARCHAR(100), -- Job/campaign number for grouping
|
|
filename VARCHAR(255) NOT NULL, -- Original filename
|
|
file_path VARCHAR(500) NOT NULL, -- Relative path to HTML report file
|
|
score REAL, -- Overall score (0-100) for scored reports
|
|
status VARCHAR(50) NOT NULL, -- 'passed', 'warning', 'failed', 'error', 'skipped'
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by VARCHAR(100), -- User email or ID
|
|
metadata_json TEXT, -- JSON string for flexible metadata
|
|
|
|
-- Indexes for common queries
|
|
INDEX idx_report_type (report_type),
|
|
INDEX idx_job_number (job_number),
|
|
INDEX idx_status (status),
|
|
INDEX idx_created_at (created_at)
|
|
);
|
|
|
|
-- Progress Sessions table
|
|
-- Tracks progress of long-running QC operations
|
|
CREATE TABLE IF NOT EXISTS progress_sessions (
|
|
id VARCHAR(100) PRIMARY KEY, -- UUID or session ID
|
|
percent REAL NOT NULL DEFAULT 0.0, -- Progress percentage (0-100)
|
|
message VARCHAR(500), -- Human-readable progress message
|
|
details TEXT, -- JSON string with additional details
|
|
status VARCHAR(50) NOT NULL DEFAULT 'running', -- 'running', 'completed', 'failed', 'cancelled'
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Sample data for testing (commented out)
|
|
-- INSERT INTO qc_reports (report_type, job_number, filename, file_path, score, status, created_by)
|
|
-- VALUES ('hm_qc', '2069052', 'test_report.html', 'storage/reports/hm_qc/2069052/test_report.html', 85.5, 'passed', 'test@example.com');
|