system_update/server/db/migrations/0002_reflective_lifeguard.sql

CREATE TABLE `important_messages` (
	`id` text PRIMARY KEY NOT NULL,
	`machine_id` text,
	`source` text NOT NULL,
	`category` text NOT NULL,
	`severity` text NOT NULL,
	`package_name` text,
	`component` text,
	`message` text NOT NULL,
	`raw_line_ref` text,
	`snapshot_id` text,
	`execution_id` text,
	`first_seen_at` text NOT NULL,
	`last_seen_at` text NOT NULL,
	`acknowledged` integer DEFAULT 0 NOT NULL,
	`acknowledged_at` text,
	`acknowledged_by` text,
	`payload_json` text,
	FOREIGN KEY (`machine_id`) REFERENCES `machines`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `machine_events` (
	`id` text PRIMARY KEY NOT NULL,
	`machine_id` text,
	`event_type` text NOT NULL,
	`severity` text NOT NULL,
	`created_at` text NOT NULL,
	`actor_type` text,
	`actor_id` text,
	`snapshot_id` text,
	`execution_id` text,
	`job_id` text,
	`message` text,
	`payload_json` text,
	FOREIGN KEY (`machine_id`) REFERENCES `machines`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `machine_hardware` (
	`machine_id` text PRIMARY KEY NOT NULL,
	`probe_snapshot_id` text,
	`cpu_model` text,
	`cpu_cores` integer,
	`memory_bytes` integer,
	`gpus_json` text,
	`disks_json` text,
	`network_json` text,
	`firmware_json` text,
	`driver_json` text,
	`warnings_json` text,
	`updated_at` text NOT NULL,
	FOREIGN KEY (`machine_id`) REFERENCES `machines`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `machine_metrics_latest` (
	`machine_id` text PRIMARY KEY NOT NULL,
	`snapshot_id` text,
	`collected_at` text NOT NULL,
	`cpu_load1` real,
	`cpu_load5` real,
	`cpu_cores` integer,
	`memory_total_bytes` integer,
	`memory_used_bytes` integer,
	`memory_available_bytes` integer,
	`memory_used_percent` real,
	`filesystems_json` text,
	`root_used_percent` real,
	`warnings_json` text,
	FOREIGN KEY (`machine_id`) REFERENCES `machines`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `machine_state` (
	`machine_id` text PRIMARY KEY NOT NULL,
	`status` text NOT NULL,
	`apt_status` text,
	`apt_updates_count` integer DEFAULT 0 NOT NULL,
	`apt_reboot_required` integer DEFAULT 0 NOT NULL,
	`apt_last_analyze_at` text,
	`docker_status` text,
	`docker_installed` integer DEFAULT 0 NOT NULL,
	`docker_stacks_count` integer DEFAULT 0 NOT NULL,
	`docker_updates_count` integer DEFAULT 0 NOT NULL,
	`docker_prune_available` integer DEFAULT 0 NOT NULL,
	`post_install_status` text,
	`metrics_last_collected_at` text,
	`cpu_load1` real,
	`memory_used_percent` real,
	`root_used_percent` real,
	`disk_warnings_count` integer DEFAULT 0 NOT NULL,
	`hardware_warnings_count` integer DEFAULT 0 NOT NULL,
	`running_job_id` text,
	`last_error_kind` text,
	`last_error_message` text,
	`updated_at` text NOT NULL,
	FOREIGN KEY (`machine_id`) REFERENCES `machines`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `raw_artifacts` (
	`id` text PRIMARY KEY NOT NULL,
	`machine_id` text,
	`kind` text NOT NULL,
	`path` text NOT NULL,
	`bytes` integer,
	`sha256` text,
	`created_at` text NOT NULL,
	`expires_at` text,
	`pinned` integer DEFAULT 0 NOT NULL,
	`redacted` integer DEFAULT 1 NOT NULL,
	`retention_policy` text,
	`deleted_at` text,
	`delete_reason` text,
	`metadata_json` text,
	FOREIGN KEY (`machine_id`) REFERENCES `machines`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `reports` (
	`id` text PRIMARY KEY NOT NULL,
	`machine_id` text,
	`execution_id` text,
	`kind` text NOT NULL,
	`title` text NOT NULL,
	`path` text NOT NULL,
	`created_at` text NOT NULL,
	`pinned` integer DEFAULT 0 NOT NULL,
	`summary_json` text,
	FOREIGN KEY (`machine_id`) REFERENCES `machines`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
ALTER TABLE `executions` ADD `schema_version` integer DEFAULT 1 NOT NULL;--> statement-breakpoint
ALTER TABLE `executions` ADD `request_id` text;--> statement-breakpoint
ALTER TABLE `executions` ADD `job_id` text;--> statement-breakpoint
ALTER TABLE `executions` ADD `important_json` text;--> statement-breakpoint
ALTER TABLE `executions` ADD `report_id` text;--> statement-breakpoint
ALTER TABLE `executions` ADD `exit_code` integer;--> statement-breakpoint
ALTER TABLE `executions` ADD `error_kind` text;--> statement-breakpoint
ALTER TABLE `executions` ADD `error_message` text;--> statement-breakpoint
ALTER TABLE `machines` ADD `os_version` text;--> statement-breakpoint
ALTER TABLE `machines` ADD `os_codename` text;--> statement-breakpoint
ALTER TABLE `machines` ADD `arch` text;--> statement-breakpoint
ALTER TABLE `machines` ADD `machine_kind` text;--> statement-breakpoint
ALTER TABLE `machines` ADD `virtualization` text;--> statement-breakpoint
ALTER TABLE `machines` ADD `hardware_profile` text;--> statement-breakpoint
ALTER TABLE `machines` ADD `last_seen_at` text;--> statement-breakpoint
ALTER TABLE `machines` ADD `updated_at` text;--> statement-breakpoint
ALTER TABLE `machines` ADD `deleted_at` text;--> statement-breakpoint
ALTER TABLE `snapshots` ADD `kind` text DEFAULT 'apt_update_analyze' NOT NULL;--> statement-breakpoint
ALTER TABLE `snapshots` ADD `schema_version` integer DEFAULT 1 NOT NULL;--> statement-breakpoint
ALTER TABLE `snapshots` ADD `important_json` text;--> statement-breakpoint
ALTER TABLE `snapshots` ADD `raw_log_path` text;--> statement-breakpoint
ALTER TABLE `snapshots` ADD `raw_artifact_id` text;--> statement-breakpoint
ALTER TABLE `snapshots` ADD `source_job_id` text;