17 lines
873 B
MySQL
17 lines
873 B
MySQL
|
-- Preserve the exact management request before transport. No error/timeout may
|
||
|
|
-- delete a pending intent or turn it into an acknowledgement.
|
||
|
|
CREATE TABLE gui_publications (
|
||
|
|
id UUID PRIMARY KEY,
|
||
|
|
computer_id TEXT NOT NULL REFERENCES computers(id) ON DELETE CASCADE,
|
||
|
|
bot_id TEXT NOT NULL,
|
||
|
|
request JSONB NOT NULL,
|
||
|
|
receipt JSONB,
|
||
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','acknowledged')),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
acknowledged_at TIMESTAMPTZ,
|
||
|
|
CHECK ((status='pending' AND receipt IS NULL AND acknowledged_at IS NULL)
|
||
|
|
OR (status='acknowledged' AND receipt IS NOT NULL AND acknowledged_at IS NOT NULL))
|
||
|
|
);
|
||
|
|
CREATE INDEX gui_publication_scope ON gui_publications(computer_id,bot_id,created_at DESC);
|
||
|
|
CREATE INDEX gui_publication_pending ON gui_publications(bot_id) WHERE status='pending';
|