Files
Homelab-manager/server/drizzle/0094_integration_figures.sql
bobbanandClaude Sonnet 5.5 e7f23ba13f Keep a history of integration figures and draw it on each integration's page
The numbers integrations report (queries and blocked share, queue and missing
items, speeds, photos, documents, failed runs, firing rules, targets down,
monthly cost and more) are kept about every quarter of an hour for five weeks,
then as hours and days for up to two years, taken from each integration's
stored reading. Every card on the 14 integration pages gets a Show the
history button with a chart per figure, how it changed and its lowest and
highest. A stretch when the integration could not be read is a gap.

Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
2026-11-12 21:12:47 +01:00

33 lines
1.3 KiB
SQL

CREATE TABLE `integration_figure_rollup` (
`figure_id` integer NOT NULL,
`grain` text NOT NULL,
`at` text NOT NULL,
`sum` real NOT NULL,
`n` integer NOT NULL,
`min` real NOT NULL,
`max` real NOT NULL,
PRIMARY KEY(`figure_id`, `grain`, `at`),
FOREIGN KEY (`figure_id`) REFERENCES `integration_figures`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `integration_figure_samples` (
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
`figure_id` integer NOT NULL,
`at` text NOT NULL,
`value` real NOT NULL,
FOREIGN KEY (`figure_id`) REFERENCES `integration_figures`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `integration_figure_samples_idx` ON `integration_figure_samples` (`figure_id`,`at`);--> statement-breakpoint
CREATE TABLE `integration_figures` (
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
`integration_id` integer NOT NULL,
`key` text NOT NULL,
`label` text NOT NULL,
`unit` text NOT NULL,
`latest` real NOT NULL,
`updated_at` text NOT NULL,
FOREIGN KEY (`integration_id`) REFERENCES `integrations`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `integration_figures_key_idx` ON `integration_figures` (`integration_id`,`key`);