36 lines
1.3 KiB
PL/PgSQL
36 lines
1.3 KiB
PL/PgSQL
BEGIN;
|
|
|
|
SET ROLE continuity_role;
|
|
|
|
CREATE TABLE IF NOT EXISTS continuity.attention_items (
|
|
attention_item_id TEXT PRIMARY KEY,
|
|
owner_principal_id TEXT NOT NULL,
|
|
scope_id TEXT NOT NULL,
|
|
source_kind TEXT NOT NULL,
|
|
source_prospective_item_id TEXT NOT NULL,
|
|
level TEXT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
due_at_unix_millis BIGINT NOT NULL,
|
|
created_at_unix_millis BIGINT NOT NULL,
|
|
delivered_at_unix_millis BIGINT NOT NULL DEFAULT 0,
|
|
action_intent_id TEXT NOT NULL DEFAULT '',
|
|
authorized_action_id TEXT NOT NULL DEFAULT '',
|
|
action_attempt_id TEXT NOT NULL DEFAULT '',
|
|
delivery_outcome TEXT NOT NULL DEFAULT '',
|
|
revision BIGINT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE (source_kind, source_prospective_item_id),
|
|
CHECK (source_kind IN ('PROSPECTIVE_REMINDER')),
|
|
CHECK (level IN ('URGENT','ACTION_REQUIRED','DUE','IMPORTANT_CHANGE','FYI','BACKGROUND')),
|
|
CHECK (state IN ('PENDING','DELIVERING','DELIVERED','SUPPRESSED','EXPIRED','FAILED')),
|
|
CHECK (summary <> ''),
|
|
CHECK (due_at_unix_millis > 0),
|
|
CHECK (created_at_unix_millis > 0),
|
|
CHECK (revision > 0),
|
|
CHECK (state <> 'DELIVERED' OR (delivered_at_unix_millis > 0 AND action_intent_id <> '' AND authorized_action_id <> '' AND action_attempt_id <> '' AND delivery_outcome <> ''))
|
|
);
|
|
|
|
RESET ROLE;
|
|
COMMIT;
|