29 lines
1.3 KiB
SQL
29 lines
1.3 KiB
SQL
-- Frozen latest Dashboard IoT-vs-manual report snapshot per cycle + kandang.
|
|
-- Generate again overwrites the same row. No TTL / expires_at.
|
|
|
|
CREATE TABLE IF NOT EXISTS dashboard_report_snapshots (
|
|
id SERIAL PRIMARY KEY,
|
|
cycle_id VARCHAR(255) NOT NULL,
|
|
kandang_id INTEGER NOT NULL,
|
|
payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
up_to_day INTEGER NOT NULL,
|
|
generated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (cycle_id) REFERENCES cycles(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (kandang_id) REFERENCES kandangs(id) ON DELETE CASCADE,
|
|
UNIQUE (cycle_id, kandang_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_dashboard_report_snapshots_cycle_kandang
|
|
ON dashboard_report_snapshots (cycle_id, kandang_id);
|
|
|
|
DROP TRIGGER IF EXISTS update_dashboard_report_snapshots_updated_at ON dashboard_report_snapshots;
|
|
CREATE TRIGGER update_dashboard_report_snapshots_updated_at
|
|
BEFORE UPDATE ON dashboard_report_snapshots
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
COMMENT ON TABLE dashboard_report_snapshots IS
|
|
'Latest desktop Dashboard daily IoT-vs-manual report snapshot; one row per cycle+kandang, overwritten on Generate';
|