CREATE TABLE `log_build_run` (
`BuildGuid` char(64) NOT NULL
COMMENT 'Unique identifier for this pipeline run',
`Package` char(64) NOT NULL
COMMENT 'Target package',
`Substrate` varchar(20) DEFAULT NULL
COMMENT 'K3S, DOCKER, SUPERVISOR, or LXC',
`HostArch` varchar(20) DEFAULT NULL
COMMENT 'CPU architecture — x86_64, aarch64, etc.',
`HostOS` varchar(100) DEFAULT NULL
COMMENT 'Operating system of the build host',
`HostKernel` varchar(60) DEFAULT NULL
COMMENT 'Kernel version of the build host',
`DockerVersion` varchar(30) DEFAULT NULL
COMMENT 'Docker engine version',
`Platform` varchar(100) DEFAULT NULL
COMMENT 'Target platform(s) — linux/amd64, linux/arm64, etc.',
`GitBranch` varchar(150) DEFAULT NULL
COMMENT 'Branch at build time',
`GitCommit` char(40) DEFAULT NULL
COMMENT 'Commit SHA',
`Status` varchar(20) NOT NULL DEFAULT 'PENDING'
COMMENT 'PENDING, RUNNING, SUCCESS, or FAILED',
`StartTime` datetime DEFAULT NULL
COMMENT 'Pipeline start timestamp',
`EndTime` datetime DEFAULT NULL
COMMENT 'Pipeline end timestamp',
`ElapsedSeconds` decimal(10,1) DEFAULT NULL
COMMENT 'Total pipeline duration',
`TotalModules` int DEFAULT 0
COMMENT 'Number of modules compiled',
`FailedModules` int DEFAULT 0
COMMENT 'Number of modules that failed compilation',
`TriggerSource` varchar(50) DEFAULT 'CLI'
COMMENT 'CLI, WEBHOOK, or SCHEDULED',
`ErrorMessage` text DEFAULT NULL
COMMENT 'Top-level failure reason if any',
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`BuildGuid`),
KEY `idx_package` (`Package`),
KEY `idx_status` (`Status`),
KEY `idx_start` (`StartTime`),
KEY `idx_created` (`CreatedDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
COMMENT='One row per build pipeline execution';
CREATE TABLE `log_build_step` (
`BuildGuid` char(64) NOT NULL
COMMENT 'FK to log_build_run',
`StepName` varchar(50) NOT NULL
COMMENT 'config, pem_keys, compile, image, push, topology, scaffold, deploy',
`StepOrder` int NOT NULL DEFAULT 0
COMMENT 'Execution sequence',
`Status` varchar(20) NOT NULL DEFAULT 'PENDING'
COMMENT 'PENDING, RUNNING, SUCCESS, FAILED, or SKIPPED',
`StartTime` datetime DEFAULT NULL,
`EndTime` datetime DEFAULT NULL,
`ElapsedSeconds` decimal(10,1) DEFAULT NULL,
`ErrorMessage` text DEFAULT NULL
COMMENT 'Failure detail',
`ResultDetail` text DEFAULT NULL
COMMENT 'JSON metadata — image tag, file counts, etc.',
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`BuildGuid`, `StepName`),
KEY `idx_status` (`Status`),
KEY `idx_step_order` (`BuildGuid`, `StepOrder`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
COMMENT='One row per pipeline step per build run';
CREATE TABLE `log_build_compile` (
`BuildGuid` char(64) NOT NULL
COMMENT 'FK to log_build_run',
`Package` char(64) NOT NULL
COMMENT 'Package identifier',
`FactorySet` varchar(50) NOT NULL
COMMENT 'core, service, report, web, etc.',
`Module` varchar(100) NOT NULL
COMMENT 'Module name without extension',
`CompileSuccess` char(1) NOT NULL DEFAULT 'N'
COMMENT 'Y or N',
`CompileError` text DEFAULT NULL
COMMENT 'Cython error output',
`PythonVersion` varchar(10) DEFAULT NULL,
`Architecture` varchar(80) DEFAULT NULL
COMMENT 'Extension suffix from sysconfig',
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (
`BuildGuid`, `FactorySet`, `Module`
),
KEY `idx_package` (`Package`),
KEY `idx_success` (`CompileSuccess`),
KEY `idx_factory` (`FactorySet`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
COMMENT='One row per compiled module per build run';
CREATE TABLE `log_build_scan` (
`BuildGuid` char(64) NOT NULL
COMMENT 'FK to log_build_run',
`ImageTag` varchar(255) NOT NULL
COMMENT 'Scanned image reference',
`Critical` int NOT NULL DEFAULT 0,
`High` int NOT NULL DEFAULT 0,
`Medium` int NOT NULL DEFAULT 0,
`Low` int NOT NULL DEFAULT 0,
`Unknown` int NOT NULL DEFAULT 0,
`TotalVulns` int NOT NULL DEFAULT 0,
`ScanPassed` char(1) NOT NULL DEFAULT 'Y'
COMMENT 'Y if no critical/high vulns',
`ScanOutput` mediumtext DEFAULT NULL
COMMENT 'JSON summary from Trivy',
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`BuildGuid`, `ImageTag`),
KEY `idx_passed` (`ScanPassed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
COMMENT='Trivy vulnerability scan results per build';
CREATE TABLE `log_build_review` (
`BuildGuid` char(64) NOT NULL,
`ReviewText` mediumtext DEFAULT NULL,
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`BuildGuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
COMMENT='AI-generated build review per pipeline run';
CREATE TABLE `log_build_image` (
`BuildGuid` char(64) NOT NULL,
`Target` varchar(50) NOT NULL,
`ImageTag` varchar(255) NOT NULL,
`SizeBytes` bigint DEFAULT 0,
`SizeMB` decimal(10,1) DEFAULT 0,
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`BuildGuid`, `Target`),
KEY `idx_target` (`Target`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
COMMENT='Image sizes per build target';
CREATE TABLE `log_build_compile_set` (
`BuildGuid` char(64) NOT NULL
COMMENT 'FK to log_build_run',
`FactorySet` varchar(50) NOT NULL
COMMENT 'core, service, report, etc.',
`TotalModules` int NOT NULL DEFAULT 0,
`FailedModules` int NOT NULL DEFAULT 0,
`ElapsedSeconds` decimal(10,1) DEFAULT NULL
COMMENT 'Wall-clock time for this factory set',
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`BuildGuid`, `FactorySet`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
COMMENT='Per-factory-set compile timing summary';
INSERT INTO log_build_run (
BuildGuid, Package, Substrate, Platform,
GitBranch, GitCommit,
Status, StartTime, TriggerSource
) VALUES (
'{build_guid}', '{package}', '{substrate}',
'{platform}',
'{git_branch}', '{git_commit}',
'RUNNING', NOW(), '{trigger_source}'
)
UPDATE log_build_run
SET HostArch = '{host_arch}',
HostOS = '{host_os}',
HostKernel = '{host_kernel}',
DockerVersion = '{docker_version}',
Substrate = '{substrate}'
WHERE BuildGuid = '{build_guid}'
UPDATE log_build_run
SET Status = '{status}',
EndTime = NOW(),
ElapsedSeconds = {elapsed_seconds},
TotalModules = {total_modules},
FailedModules = {failed_modules},
ErrorMessage = '{error_message}'
WHERE BuildGuid = '{build_guid}'
INSERT INTO log_build_step (
BuildGuid, StepName, StepOrder,
Status, StartTime
) VALUES (
'{build_guid}', '{step_name}', {step_order},
'RUNNING', NOW()
)
UPDATE log_build_step
SET Status = '{status}',
EndTime = NOW(),
ElapsedSeconds = {elapsed_seconds},
ErrorMessage = '{error_message}',
ResultDetail = '{result_detail}'
WHERE BuildGuid = '{build_guid}'
AND StepName = '{step_name}'
INSERT INTO log_build_compile (
BuildGuid, Package, FactorySet, Module,
CompileSuccess, CompileError,
PythonVersion, Architecture
) VALUES (
'{build_guid}', '{package}',
'{factory_set}', '{module}',
'{compile_success}', '{compile_error}',
'{python_version}', '{architecture}'
)
INSERT INTO log_build_scan (
BuildGuid, ImageTag,
Critical, High, Medium, Low,
Unknown, TotalVulns,
ScanPassed, ScanOutput
) VALUES (
'{build_guid}', '{image_tag}',
{critical}, {high}, {medium}, {low},
{unknown}, {total_vulns},
'{scan_passed}', '{scan_output}'
)
SELECT
ImageTag, Critical, High,
Medium, Low, Unknown, TotalVulns,
ScanPassed
FROM log_build_scan
WHERE BuildGuid = '{build_guid}'
INSERT INTO log_build_compile_set (
BuildGuid, FactorySet,
TotalModules, FailedModules,
ElapsedSeconds
) VALUES (
'{build_guid}', '{factory_set}',
{total_modules}, {failed_modules},
{elapsed_seconds}
)
SELECT
r.BuildGuid, r.Package, r.Substrate,
r.HostArch, r.HostOS,
r.HostKernel, r.DockerVersion,
r.Platform,
r.GitBranch, r.GitCommit,
r.Status, r.StartTime, r.EndTime,
r.ElapsedSeconds,
r.TotalModules, r.FailedModules,
r.TriggerSource, r.ErrorMessage
FROM log_build_run r
WHERE r.BuildGuid = '{build_guid}'
SELECT
StepName, StepOrder, Status,
StartTime, EndTime, ElapsedSeconds,
ErrorMessage, ResultDetail
FROM log_build_step
WHERE BuildGuid = '{build_guid}'
ORDER BY StepOrder
SELECT
FactorySet,
COUNT(*) AS TotalModules,
SUM(CASE WHEN CompileSuccess = 'Y'
THEN 1 ELSE 0 END) AS Passed,
SUM(CASE WHEN CompileSuccess = 'N'
THEN 1 ELSE 0 END) AS Failed
FROM log_build_compile
WHERE BuildGuid = '{build_guid}'
GROUP BY FactorySet
ORDER BY FactorySet
SELECT
FactorySet, Module,
CompileError, PythonVersion
FROM log_build_compile
WHERE BuildGuid = '{build_guid}'
AND CompileSuccess = 'N'
ORDER BY FactorySet, Module
SELECT
r.BuildGuid, r.Package, r.Substrate,
r.GitBranch, r.Status,
r.StartTime, r.ElapsedSeconds,
r.TotalModules, r.FailedModules
FROM log_build_run r
WHERE r.Package = '{package}'
ORDER BY r.StartTime DESC
LIMIT {limit}