opensource/iot-dc3
3
0
Fork 0
mirror of https://gitee.com/pnoker/iot-dc3.git synced 2026-11-02 11:11:57 +08:00
Code Issues Packages Projects Releases Wiki Activity
Files
develop
iot-dc3/dc3/dependencies/postgres/initdb/03-iot-dc3-data.sql
T
pnoker 72e315ea96 style(db): normalize initdb formatting and order ddl before seeds 
Join 1242 keyword-split statement lines (COMMENT/CREATE/ALTER/LOAD), move the dc3_identity_audit_log table section and the schema fingerprint INSERT so every script orders DDL before seed data, and drop one duplicated COMMENT ON COLUMN. Statement multiset verified identical apart from the intended dedup. The canonical fingerprint changes to e81e3562... and is synchronized via schema_fingerprint.py --sync into the embedded ddl_hash and all four distribution points; existing initialized databases require re-initialization (flag day).
2026-09-03 09:02:47 +08:00

773 lines
49 KiB
PL/PgSQL
Raw Permalink Blame History

/*
* Copyright 2016-present the IoT DC3 original author or authors.
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <https://www.gnu.org/licenses/>.
*/
CREATE SCHEMA IF NOT EXISTS dc3_data;
SET search_path TO dc3_data;
-- ----------------------------
-- Function for update operate time
-- ----------------------------
CREATE OR REPLACE FUNCTION update_operate_time()
RETURNS TRIGGER AS
$$
BEGIN
NEW.operate_time = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
-- ----------------------------
-- Table structure for dc3_notify
-- ----------------------------
CREATE TABLE dc3_notify
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
notify_name TEXT DEFAULT ''::TEXT NOT NULL, -- Notification name
notify_code TEXT DEFAULT ''::TEXT NOT NULL, -- Notification code
auto_confirm_flag SMALLINT DEFAULT 0 NOT NULL, -- Auto-confirm flag
notify_interval BIGINT DEFAULT 0 NOT NULL, -- Notification interval, milliseconds
notify_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Notification configuration
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_notify_auto_confirm_flag CHECK (auto_confirm_flag BETWEEN 0 AND 1),
CONSTRAINT chk_notify_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_notify_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_notify_tenant_code_active_unique ON dc3_notify (tenant_id, notify_code) WHERE deleted = 0 AND notify_code <> ''::TEXT;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_notify
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_notify IS 'Notification table';
COMMENT ON COLUMN dc3_notify.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_notify.notify_name IS 'Notification name';
COMMENT ON COLUMN dc3_notify.notify_code IS 'Notification code';
COMMENT ON COLUMN dc3_notify.auto_confirm_flag IS 'Auto-confirm flag';
COMMENT ON COLUMN dc3_notify.notify_interval IS 'Notification interval, milliseconds';
COMMENT ON COLUMN dc3_notify.notify_ext IS 'Notification configuration';
COMMENT ON COLUMN dc3_notify.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_notify.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_notify.remark IS 'Description';
COMMENT ON COLUMN dc3_notify.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_notify.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_notify.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_notify.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_notify.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_notify.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_notify.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_notify_channel
-- ----------------------------
CREATE TABLE dc3_notify_channel
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
channel_name TEXT DEFAULT ''::TEXT NOT NULL, -- Notification channel name
channel_code TEXT DEFAULT ''::TEXT NOT NULL, -- Notification channel code
channel_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Notification channel type flag
credential_ref TEXT DEFAULT ''::TEXT NOT NULL, -- Credential reference
channel_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Notification channel configuration
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_notify_channel_channel_type_flag CHECK (channel_type_flag BETWEEN 0 AND 2),
CONSTRAINT chk_notify_channel_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_notify_channel_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_notify_channel_tenant_code_active_unique ON dc3_notify_channel (tenant_id, channel_code) WHERE deleted = 0 AND channel_code <> ''::TEXT;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_notify_channel
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_notify_channel IS 'Notification channel table';
COMMENT ON COLUMN dc3_notify_channel.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_notify_channel.channel_name IS 'Notification channel name';
COMMENT ON COLUMN dc3_notify_channel.channel_code IS 'Notification channel code';
COMMENT ON COLUMN dc3_notify_channel.channel_type_flag IS 'Notification channel type flag';
COMMENT ON COLUMN dc3_notify_channel.credential_ref IS 'Credential reference';
COMMENT ON COLUMN dc3_notify_channel.channel_ext IS 'Notification channel configuration';
COMMENT ON COLUMN dc3_notify_channel.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_notify_channel.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_notify_channel.remark IS 'Description';
COMMENT ON COLUMN dc3_notify_channel.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_notify_channel.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_notify_channel.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_notify_channel.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_notify_channel.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_notify_channel.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_notify_channel.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_notify_channel_bind
-- ----------------------------
CREATE TABLE dc3_notify_channel_bind
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
notify_id BIGINT DEFAULT 0 NOT NULL, -- Notification ID
channel_id BIGINT DEFAULT 0 NOT NULL, -- Notification channel ID
bind_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Notification channel binding configuration
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_notify_channel_bind_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_notify_channel_bind_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_notify_channel_bind_active_unique ON dc3_notify_channel_bind (tenant_id, notify_id, channel_id) WHERE deleted = 0;
CREATE INDEX idx_notify_channel_bind_notify_id ON dc3_notify_channel_bind (notify_id) WHERE deleted = 0;
CREATE INDEX idx_notify_channel_bind_channel_id ON dc3_notify_channel_bind (channel_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_notify_channel_bind
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_notify_channel_bind IS 'Notification channel binding table';
COMMENT ON COLUMN dc3_notify_channel_bind.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_notify_channel_bind.notify_id IS 'Notification ID';
COMMENT ON COLUMN dc3_notify_channel_bind.channel_id IS 'Notification channel ID';
COMMENT ON COLUMN dc3_notify_channel_bind.bind_ext IS 'Notification channel binding configuration';
COMMENT ON COLUMN dc3_notify_channel_bind.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_notify_channel_bind.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_notify_channel_bind.remark IS 'Description';
COMMENT ON COLUMN dc3_notify_channel_bind.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_notify_channel_bind.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_notify_channel_bind.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_notify_channel_bind.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_notify_channel_bind.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_notify_channel_bind.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_notify_channel_bind.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_message
-- ----------------------------
CREATE TABLE dc3_message
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
message_name TEXT DEFAULT ''::TEXT NOT NULL, -- Message name
message_code TEXT DEFAULT ''::TEXT NOT NULL, -- Message code
message_level SMALLINT DEFAULT 2 NOT NULL, -- Message level
message_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Message configuration
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_message_message_level CHECK (message_level BETWEEN 0 AND 3),
CONSTRAINT chk_message_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_message_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_message_tenant_code_active_unique ON dc3_message (tenant_id, message_code) WHERE deleted = 0 AND message_code <> ''::TEXT;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_message
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_message IS 'Message table';
COMMENT ON COLUMN dc3_message.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_message.message_name IS 'Message name';
COMMENT ON COLUMN dc3_message.message_code IS 'Message code';
COMMENT ON COLUMN dc3_message.message_level IS 'Message level';
COMMENT ON COLUMN dc3_message.message_ext IS 'Message configuration';
COMMENT ON COLUMN dc3_message.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_message.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_message.remark IS 'Description';
COMMENT ON COLUMN dc3_message.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_message.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_message.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_message.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_message.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_message.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_message.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_rule
-- ----------------------------
CREATE TABLE dc3_rule
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
alarm_target_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Alarm target type flag
rule_name TEXT DEFAULT ''::TEXT NOT NULL, -- Rule name
rule_code TEXT DEFAULT ''::TEXT NOT NULL, -- Rule code
entity_id BIGINT DEFAULT 0 NOT NULL, -- Entity ID
notify_id BIGINT DEFAULT 0 NOT NULL, -- Notification ID
message_id BIGINT DEFAULT 0 NOT NULL, -- Message ID
rule_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Rule configuration
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_rule_alarm_target_type_flag CHECK (alarm_target_type_flag BETWEEN 0 AND 3),
CONSTRAINT chk_rule_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_rule_deleted CHECK (deleted IN (0, 1))
);
CREATE INDEX idx_rule_entity_id ON dc3_rule (entity_id) WHERE deleted = 0;
CREATE INDEX idx_rule_notify_id ON dc3_rule (notify_id) WHERE deleted = 0;
CREATE INDEX idx_rule_message_id ON dc3_rule (message_id) WHERE deleted = 0;
CREATE UNIQUE INDEX idx_rule_tenant_code_active_unique ON dc3_rule (tenant_id, rule_code) WHERE deleted = 0 AND rule_code <> ''::TEXT;
-- Hot-path rule lookup used by RuleCandidateLookup on every fact (tenant + target type + enable + entity).
CREATE INDEX idx_rule_eval ON dc3_rule (tenant_id, alarm_target_type_flag, enable_flag, entity_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_rule
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_rule IS 'Rule table';
COMMENT ON COLUMN dc3_rule.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_rule.alarm_target_type_flag IS 'Alarm target type flag';
COMMENT ON COLUMN dc3_rule.rule_name IS 'Rule name';
COMMENT ON COLUMN dc3_rule.rule_code IS 'Rule code';
COMMENT ON COLUMN dc3_rule.entity_id IS 'Entity ID';
COMMENT ON COLUMN dc3_rule.notify_id IS 'Notification ID';
COMMENT ON COLUMN dc3_rule.message_id IS 'Message ID';
COMMENT ON COLUMN dc3_rule.rule_ext IS 'Rule configuration';
COMMENT ON COLUMN dc3_rule.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_rule.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_rule.remark IS 'Description';
COMMENT ON COLUMN dc3_rule.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_rule.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_rule.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_rule.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_rule.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_rule.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_rule.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_rule_state
-- ----------------------------
CREATE TABLE dc3_rule_state
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
rule_id BIGINT DEFAULT 0 NOT NULL, -- Rule ID
alarm_target_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Alarm target type flag
entity_id BIGINT DEFAULT 0 NOT NULL, -- Entity ID
fingerprint TEXT DEFAULT ''::TEXT NOT NULL, -- Rule state fingerprint
entity_state_flag SMALLINT DEFAULT 0 NOT NULL, -- Rule state flag
first_trigger_time TIMESTAMPTZ, -- First trigger time
last_trigger_time TIMESTAMPTZ, -- Last trigger time
last_recover_time TIMESTAMPTZ, -- Last recovery time
last_notify_time TIMESTAMPTZ, -- Last notification time
trigger_count BIGINT DEFAULT 0 NOT NULL, -- Trigger count
alarm_id BIGINT DEFAULT 0 NOT NULL, -- Latest alarm ID (dc3_entity_alarm.id)
entity_state_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Rule state extension
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,-- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,-- Operation time
CONSTRAINT chk_rule_state_alarm_target_type_flag CHECK (alarm_target_type_flag BETWEEN 0 AND 3),
CONSTRAINT chk_rule_state_entity_state_flag CHECK (entity_state_flag BETWEEN 0 AND 3)
);
CREATE UNIQUE INDEX idx_rule_state_unique ON dc3_rule_state (tenant_id, rule_id, alarm_target_type_flag, entity_id, fingerprint);
CREATE INDEX idx_rule_state_rule ON dc3_rule_state (tenant_id, rule_id, entity_state_flag);
CREATE INDEX idx_rule_state_entity ON dc3_rule_state (tenant_id, alarm_target_type_flag, entity_id, entity_state_flag);
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_rule_state
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_rule_state IS 'Rule runtime state table';
COMMENT ON COLUMN dc3_rule_state.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_rule_state.rule_id IS 'Rule ID';
COMMENT ON COLUMN dc3_rule_state.alarm_target_type_flag IS 'Alarm target type flag';
COMMENT ON COLUMN dc3_rule_state.entity_id IS 'Entity ID';
COMMENT ON COLUMN dc3_rule_state.fingerprint IS 'Rule state fingerprint';
COMMENT ON COLUMN dc3_rule_state.entity_state_flag IS 'Rule state flag';
COMMENT ON COLUMN dc3_rule_state.first_trigger_time IS 'First trigger time';
COMMENT ON COLUMN dc3_rule_state.last_trigger_time IS 'Last trigger time';
COMMENT ON COLUMN dc3_rule_state.last_recover_time IS 'Last recovery time';
COMMENT ON COLUMN dc3_rule_state.last_notify_time IS 'Last notification time';
COMMENT ON COLUMN dc3_rule_state.trigger_count IS 'Trigger count';
COMMENT ON COLUMN dc3_rule_state.alarm_id IS 'Latest alarm ID (dc3_entity_alarm.id)';
COMMENT ON COLUMN dc3_rule_state.entity_state_ext IS 'Rule state extension';
COMMENT ON COLUMN dc3_rule_state.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_rule_state.remark IS 'Description';
COMMENT ON COLUMN dc3_rule_state.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_rule_state.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_rule_state.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_rule_state.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_rule_state.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_rule_state.operate_time IS 'Operation time';
-- ----------------------------
-- Table structure for dc3_notify_history
-- ----------------------------
CREATE TABLE dc3_notify_history
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
rule_id BIGINT DEFAULT 0 NOT NULL, -- Rule ID
notify_id BIGINT DEFAULT 0 NOT NULL, -- Notification ID
message_id BIGINT DEFAULT 0 NOT NULL, -- Message ID
channel_id BIGINT DEFAULT 0 NOT NULL, -- Notification channel ID
alarm_id BIGINT DEFAULT 0 NOT NULL, -- Alarm ID (dc3_entity_alarm.id)
dedupe_key TEXT,
channel_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Notification channel type flag
target TEXT DEFAULT ''::TEXT NOT NULL, -- Notification target
status_flag SMALLINT DEFAULT 0 NOT NULL, -- Notification history status flag
request_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Notification request
response_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Notification response
error_message TEXT DEFAULT ''::TEXT NOT NULL, -- Error message
retry_count INTEGER DEFAULT 0 NOT NULL, -- Retry count
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,-- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,-- Operation time
CONSTRAINT chk_notify_history_channel_type_flag CHECK (channel_type_flag BETWEEN 0 AND 2),
CONSTRAINT chk_notify_history_status_flag CHECK (status_flag BETWEEN 0 AND 4)
);
CREATE INDEX idx_notify_history_status ON dc3_notify_history (tenant_id, status_flag, create_time DESC);
CREATE INDEX idx_notify_history_rule ON dc3_notify_history (tenant_id, rule_id, create_time DESC);
CREATE INDEX idx_notify_history_alarm ON dc3_notify_history (tenant_id, alarm_id, create_time DESC);
CREATE INDEX idx_notify_history_channel ON dc3_notify_history (tenant_id, channel_id, status_flag, create_time DESC);
-- Pending-task scan index used by the NotifyWorker replay/reaper paths.
CREATE INDEX idx_notify_history_pending ON dc3_notify_history (tenant_id, status_flag, create_time);
CREATE UNIQUE INDEX uq_notify_history_dedupe ON dc3_notify_history (tenant_id, dedupe_key);
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_notify_history
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_notify_history IS 'Notification delivery history table';
COMMENT ON COLUMN dc3_notify_history.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_notify_history.rule_id IS 'Rule ID';
COMMENT ON COLUMN dc3_notify_history.notify_id IS 'Notification ID';
COMMENT ON COLUMN dc3_notify_history.message_id IS 'Message ID';
COMMENT ON COLUMN dc3_notify_history.channel_id IS 'Notification channel ID';
COMMENT ON COLUMN dc3_notify_history.alarm_id IS 'Alarm ID (dc3_entity_alarm.id)';
COMMENT ON COLUMN dc3_notify_history.dedupe_key IS 'Tenant-scoped notification delivery idempotency key';
COMMENT ON COLUMN dc3_notify_history.channel_type_flag IS 'Notification channel type flag';
COMMENT ON COLUMN dc3_notify_history.target IS 'Notification target';
COMMENT ON COLUMN dc3_notify_history.status_flag IS 'Notification history status flag';
COMMENT ON COLUMN dc3_notify_history.request_ext IS 'Notification request';
COMMENT ON COLUMN dc3_notify_history.response_ext IS 'Notification response';
COMMENT ON COLUMN dc3_notify_history.error_message IS 'Error message';
COMMENT ON COLUMN dc3_notify_history.retry_count IS 'Retry count';
COMMENT ON COLUMN dc3_notify_history.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_notify_history.remark IS 'Description';
COMMENT ON COLUMN dc3_notify_history.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_notify_history.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_notify_history.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_notify_history.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_notify_history.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_notify_history.operate_time IS 'Operation time';
-- ----------------------------
-- Table structure for dc3_entity_alarm
-- ----------------------------
CREATE TABLE dc3_entity_alarm
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
alarm_target_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Alarm target type flag, 0: point, 1: device, 2: driver, 3: event
entity_id BIGINT DEFAULT 0 NOT NULL, -- Alarm target entity ID
driver_id BIGINT DEFAULT 0 NOT NULL, -- Driver ID
device_id BIGINT DEFAULT 0 NOT NULL, -- Device ID
point_id BIGINT DEFAULT 0 NOT NULL, -- Point ID
rule_id BIGINT DEFAULT 0 NOT NULL, -- Rule ID
rule_state_id BIGINT DEFAULT 0 NOT NULL, -- Rule state ID
dedupe_key VARCHAR(256), -- Source-derived idempotency key
alarm_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Alarm type flag, 0: rule, 1: offline, 2: fault, 3: state flip, 4: report
alarm_source_flag SMALLINT DEFAULT 0 NOT NULL, -- Alarm source flag, 0: rule, 1: state timeout, 2: device report, 3: driver report, 4: system, 5: event report
alarm_level_flag SMALLINT DEFAULT 2 NOT NULL, -- Alarm level flag, 0: P0, 1: P1, 2: P2, 3: P3
alarm_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Alarm extension information
expired_time BIGINT DEFAULT 0 NOT NULL, -- Expiration duration, seconds
confirm_flag SMALLINT DEFAULT 0 NOT NULL, -- Confirmation flag, 0: unconfirmed, 1: confirmed
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
CONSTRAINT chk_entity_alarm_alarm_target_type_flag CHECK (alarm_target_type_flag BETWEEN 0 AND 3),
CONSTRAINT chk_entity_alarm_alarm_type_flag CHECK (alarm_type_flag BETWEEN 0 AND 4),
CONSTRAINT chk_entity_alarm_alarm_source_flag CHECK (alarm_source_flag BETWEEN 0 AND 5),
CONSTRAINT chk_alarm_level_flag CHECK (alarm_level_flag BETWEEN 0 AND 3),
CONSTRAINT chk_alarm_confirm_flag CHECK (confirm_flag IN (0, 1))
);
CREATE INDEX idx_entity_alarm_alert ON dc3_entity_alarm (tenant_id, alarm_level_flag, confirm_flag, create_time DESC);
-- Supports filtering alarms by source (driver/device/point) within a tenant.
CREATE INDEX idx_entity_alarm_source_time ON dc3_entity_alarm (tenant_id, alarm_source_flag, create_time DESC);
CREATE INDEX idx_entity_alarm_target ON dc3_entity_alarm (tenant_id, alarm_target_type_flag, entity_id, create_time DESC);
CREATE INDEX idx_entity_alarm_driver ON dc3_entity_alarm (tenant_id, driver_id, create_time DESC);
CREATE INDEX idx_entity_alarm_device ON dc3_entity_alarm (tenant_id, device_id, create_time DESC);
CREATE INDEX idx_entity_alarm_point ON dc3_entity_alarm (tenant_id, point_id, create_time DESC);
CREATE INDEX idx_entity_alarm_rule ON dc3_entity_alarm (tenant_id, rule_id, create_time DESC);
CREATE UNIQUE INDEX idx_entity_alarm_dedupe ON dc3_entity_alarm (tenant_id, dedupe_key);
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_entity_alarm
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_entity_alarm IS 'Entity alarm table';
COMMENT ON COLUMN dc3_entity_alarm.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_entity_alarm.alarm_target_type_flag IS 'Alarm target type flag, 0: point, 1: device, 2: driver, 3: event';
COMMENT ON COLUMN dc3_entity_alarm.entity_id IS 'Alarm target entity ID';
COMMENT ON COLUMN dc3_entity_alarm.driver_id IS 'Driver ID';
COMMENT ON COLUMN dc3_entity_alarm.device_id IS 'Device ID';
COMMENT ON COLUMN dc3_entity_alarm.point_id IS 'Point ID';
COMMENT ON COLUMN dc3_entity_alarm.rule_id IS 'Rule ID';
COMMENT ON COLUMN dc3_entity_alarm.rule_state_id IS 'Rule state ID';
COMMENT ON COLUMN dc3_entity_alarm.dedupe_key IS 'Tenant-scoped source-derived idempotency key';
COMMENT ON COLUMN dc3_entity_alarm.alarm_type_flag IS 'Alarm type flag, 0: rule, 1: offline, 2: fault, 3: state flip, 4: report';
COMMENT ON COLUMN dc3_entity_alarm.alarm_source_flag IS 'Alarm source flag, 0: rule, 1: state timeout, 2: device report, 3: driver report, 4: system, 5: event report';
COMMENT ON COLUMN dc3_entity_alarm.alarm_level_flag IS 'Alarm level flag, 0: P0, 1: P1, 2: P2, 3: P3';
COMMENT ON COLUMN dc3_entity_alarm.alarm_ext IS 'Alarm extension information';
COMMENT ON COLUMN dc3_entity_alarm.expired_time IS 'Expiration duration, seconds';
COMMENT ON COLUMN dc3_entity_alarm.confirm_flag IS 'Confirmation flag, 0: unconfirmed, 1: confirmed';
COMMENT ON COLUMN dc3_entity_alarm.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_entity_alarm.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_entity_alarm.operate_time IS 'Operation time';
-- ----------------------------
-- Table structure for dc3_entity_state
-- ----------------------------
CREATE TABLE dc3_entity_state
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
entity_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Entity type flag (EntityTypeEnum: 3=driver, 6=device)
entity_id BIGINT DEFAULT 0 NOT NULL, -- Entity ID (driver ID or device ID)
parent_entity_id BIGINT DEFAULT 0 NOT NULL, -- Parent entity ID (for devices: owning driver; for drivers: 0)
entity_state_flag SMALLINT DEFAULT 1 NOT NULL, -- Current status index (EntityStateStatus)
last_state_flag SMALLINT DEFAULT 1 NOT NULL, -- Previous status index
lease_version BIGINT DEFAULT 0 NOT NULL, -- Monotonic version incremented on each heartbeat
expire_time TIMESTAMPTZ NOT NULL, -- Absolute time when this lease expires
timeout_seconds INT DEFAULT 0 NOT NULL, -- Timeout in seconds used for this entry
last_heartbeat_time TIMESTAMPTZ NOT NULL, -- Latest heartbeat time
last_alarm_id BIGINT DEFAULT 0 NOT NULL, -- Latest related alarm ID
timeout_source_flag SMALLINT DEFAULT 0 NOT NULL, -- Timeout source flag, 0: system, 1: driver, 2: device, 3: profile
entity_state_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- State extension information
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
CONSTRAINT chk_entity_state_entity_type_flag CHECK (entity_type_flag BETWEEN 0 AND 8),
CONSTRAINT chk_entity_state_entity_state_flag CHECK (entity_state_flag BETWEEN 0 AND 3),
CONSTRAINT chk_entity_state_last_state_flag CHECK (last_state_flag BETWEEN 0 AND 3),
CONSTRAINT chk_entity_state_timeout_source_flag CHECK (timeout_source_flag BETWEEN 0 AND 3)
);
-- One state row per entity
CREATE UNIQUE INDEX idx_entity_state_unique ON dc3_entity_state (tenant_id, entity_type_flag, entity_id);
-- For the expiry scanner to find expired rows
CREATE INDEX idx_entity_state_expire ON dc3_entity_state (entity_type_flag, entity_state_flag, expire_time);
-- For status queries filtered by tenant and entity type
CREATE INDEX idx_entity_state_tenant_status ON dc3_entity_state (tenant_id, entity_type_flag, entity_state_flag);
-- For queries grouped by parent driver
CREATE INDEX idx_entity_state_parent ON dc3_entity_state (tenant_id, entity_type_flag, parent_entity_id, entity_state_flag);
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_entity_state
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_entity_state IS 'Entity state lease table';
COMMENT ON COLUMN dc3_entity_state.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_entity_state.entity_type_flag IS 'Entity type flag (EntityTypeEnum: 3=driver, 6=device)';
COMMENT ON COLUMN dc3_entity_state.entity_id IS 'Entity ID (driver ID or device ID)';
COMMENT ON COLUMN dc3_entity_state.parent_entity_id IS 'Parent entity ID (for devices: owning driver; for drivers: 0)';
COMMENT ON COLUMN dc3_entity_state.entity_state_flag IS 'Current status index (EntityStateStatus)';
COMMENT ON COLUMN dc3_entity_state.last_state_flag IS 'Previous status index';
COMMENT ON COLUMN dc3_entity_state.lease_version IS 'Monotonic version incremented on each heartbeat';
COMMENT ON COLUMN dc3_entity_state.expire_time IS 'Absolute time when this lease expires';
COMMENT ON COLUMN dc3_entity_state.timeout_seconds IS 'Timeout in seconds used for this entry';
COMMENT ON COLUMN dc3_entity_state.last_heartbeat_time IS 'Latest heartbeat time';
COMMENT ON COLUMN dc3_entity_state.last_alarm_id IS 'Latest related alarm ID';
COMMENT ON COLUMN dc3_entity_state.timeout_source_flag IS 'Timeout source flag, 0: system, 1: driver, 2: device, 3: profile';
COMMENT ON COLUMN dc3_entity_state.entity_state_ext IS 'State extension information';
COMMENT ON COLUMN dc3_entity_state.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_entity_state.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_entity_state.operate_time IS 'Operation time';
-- ----------------------------
-- Table structure for dc3_point_command_history
-- ----------------------------
CREATE TABLE dc3_point_command_history
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
command_id CHAR(36) NOT NULL, -- Command UUID
tenant_id BIGINT NOT NULL, -- Tenant ID
type SMALLINT NOT NULL, -- Command type flag, 0: read, 1: read-batch, 2: write, 3: write-batch, 4: config
device_id BIGINT NOT NULL, -- Device ID
point_id BIGINT NOT NULL, -- Point ID
request_value VARCHAR(256), -- Request value
response_value VARCHAR(256), -- Response value
status SMALLINT NOT NULL, -- Command status flag, 0: pending, 1: sent, 2: success, 3: failed, 4: timeout, 5: expired, 6: dead, 7: duplicate
error_code VARCHAR(64), -- Error code
error_message VARCHAR(1024), -- Error message
source SMALLINT NOT NULL, -- Command source flag, 0: http, 1: grpc, 2: agentic, 3: scheduled
source_user_id BIGINT, -- Source user ID
occur_time TIMESTAMPTZ NOT NULL, -- Occurrence time
send_time TIMESTAMPTZ, -- Sent time
finish_time TIMESTAMPTZ, -- Finished time
expire_time TIMESTAMPTZ NOT NULL, -- Expiration time
schema_version SMALLINT NOT NULL, -- Schema version
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
CONSTRAINT chk_point_command_history_type CHECK (type BETWEEN 0 AND 4),
CONSTRAINT chk_point_command_history_status CHECK (status BETWEEN 0 AND 7),
CONSTRAINT chk_point_command_history_source CHECK (source BETWEEN 0 AND 3)
);
-- command_id is an external correlation key scoped to its tenant.
CREATE UNIQUE INDEX idx_point_command_history_unique
ON dc3_point_command_history (tenant_id, command_id);
-- Tenant-scoped command history pages sort by occurrence time.
CREATE INDEX idx_point_command_history_tenant_time
ON dc3_point_command_history (tenant_id, occur_time DESC);
CREATE INDEX idx_point_command_history_lookup
ON dc3_point_command_history (tenant_id, device_id, point_id, occur_time DESC);
CREATE INDEX idx_point_command_history_pending
ON dc3_point_command_history (status, expire_time) WHERE status IN (0, 1);
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_point_command_history
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_point_command_history IS 'Point command execution history table';
COMMENT ON COLUMN dc3_point_command_history.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_point_command_history.command_id IS 'Command UUID';
COMMENT ON COLUMN dc3_point_command_history.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_point_command_history.type IS 'Command type flag, 0: read, 1: read-batch, 2: write, 3: write-batch, 4: config';
COMMENT ON COLUMN dc3_point_command_history.device_id IS 'Device ID';
COMMENT ON COLUMN dc3_point_command_history.point_id IS 'Point ID';
COMMENT ON COLUMN dc3_point_command_history.request_value IS 'Request value';
COMMENT ON COLUMN dc3_point_command_history.response_value IS 'Response value';
COMMENT ON COLUMN dc3_point_command_history.status IS 'Command status flag, 0: pending, 1: sent, 2: success, 3: failed, 4: timeout, 5: expired, 6: dead, 7: duplicate';
COMMENT ON COLUMN dc3_point_command_history.error_code IS 'Error code';
COMMENT ON COLUMN dc3_point_command_history.error_message IS 'Error message';
COMMENT ON COLUMN dc3_point_command_history.source IS 'Command source flag, 0: http, 1: grpc, 2: agentic, 3: scheduled';
COMMENT ON COLUMN dc3_point_command_history.source_user_id IS 'Source user ID';
COMMENT ON COLUMN dc3_point_command_history.occur_time IS 'Occurrence time';
COMMENT ON COLUMN dc3_point_command_history.send_time IS 'Sent time';
COMMENT ON COLUMN dc3_point_command_history.finish_time IS 'Finished time';
COMMENT ON COLUMN dc3_point_command_history.expire_time IS 'Expiration time';
COMMENT ON COLUMN dc3_point_command_history.schema_version IS 'Schema version';
COMMENT ON COLUMN dc3_point_command_history.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_point_command_history.operate_time IS 'Operation time';
-- ----------------------------
-- Table structure for dc3_command_history
-- ----------------------------
CREATE TABLE dc3_command_history
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
record_id CHAR(36) NOT NULL, -- Record UUID
tenant_id BIGINT NOT NULL, -- Tenant ID
device_id BIGINT NOT NULL, -- Device ID
command_id BIGINT NOT NULL, -- Command ID
command_code VARCHAR(128) NOT NULL, -- Command code
param_values JSONB, -- Parameter values (JSONB)
result_values JSONB, -- Result values (JSONB)
config_snapshot JSONB, -- Command config snapshot (JSONB)
status SMALLINT NOT NULL, -- Record status flag, 0: pending, 1: sent, 2: success, 3: failed, 4: timeout, 5: expired, 6: dead, 7: duplicate
error_code VARCHAR(64), -- Error code
error_message VARCHAR(1024), -- Error message
source SMALLINT NOT NULL, -- Command source flag, 0: http, 1: grpc, 2: agentic
source_user_id BIGINT, -- Source user ID
occur_time TIMESTAMPTZ NOT NULL, -- Occurrence time
send_time TIMESTAMPTZ, -- Sent time
finish_time TIMESTAMPTZ, -- Finished time
expire_time TIMESTAMPTZ NOT NULL, -- Expiration time
schema_version SMALLINT NOT NULL, -- Schema version
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
CONSTRAINT chk_command_history_status CHECK (status BETWEEN 0 AND 7),
CONSTRAINT chk_command_history_source CHECK (source BETWEEN 0 AND 2)
);
-- record_id is the external correlation key used by HTTP and RabbitMQ callbacks.
CREATE UNIQUE INDEX idx_command_history_unique
ON dc3_command_history (record_id);
-- Tenant-scoped command record pages sort by occurrence time.
CREATE INDEX idx_command_history_tenant_time
ON dc3_command_history (tenant_id, occur_time DESC);
CREATE INDEX idx_command_history_lookup
ON dc3_command_history (tenant_id, device_id, command_id, occur_time DESC);
CREATE INDEX idx_command_history_pending
ON dc3_command_history (status, expire_time) WHERE status IN (0, 1);
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_command_history
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_command_history IS 'Command execution history table';
COMMENT ON COLUMN dc3_command_history.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_command_history.record_id IS 'Record UUID';
COMMENT ON COLUMN dc3_command_history.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_command_history.device_id IS 'Device ID';
COMMENT ON COLUMN dc3_command_history.command_id IS 'Command ID';
COMMENT ON COLUMN dc3_command_history.command_code IS 'Command code';
COMMENT ON COLUMN dc3_command_history.param_values IS 'Parameter values (JSONB)';
COMMENT ON COLUMN dc3_command_history.result_values IS 'Result values (JSONB)';
COMMENT ON COLUMN dc3_command_history.config_snapshot IS 'Command config snapshot (JSONB)';
COMMENT ON COLUMN dc3_command_history.status IS 'Record status flag, 0: pending, 1: sent, 2: success, 3: failed, 4: timeout, 5: expired, 6: dead, 7: duplicate';
COMMENT ON COLUMN dc3_command_history.error_code IS 'Error code';
COMMENT ON COLUMN dc3_command_history.error_message IS 'Error message';
COMMENT ON COLUMN dc3_command_history.source IS 'Command source flag, 0: http, 1: grpc, 2: agentic';
COMMENT ON COLUMN dc3_command_history.source_user_id IS 'Source user ID';
COMMENT ON COLUMN dc3_command_history.occur_time IS 'Occurrence time';
COMMENT ON COLUMN dc3_command_history.send_time IS 'Sent time';
COMMENT ON COLUMN dc3_command_history.finish_time IS 'Finished time';
COMMENT ON COLUMN dc3_command_history.expire_time IS 'Expiration time';
COMMENT ON COLUMN dc3_command_history.schema_version IS 'Schema version';
COMMENT ON COLUMN dc3_command_history.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_command_history.operate_time IS 'Operation time';
-- ----------------------------
-- Table structure for dc3_event_history
-- ----------------------------
CREATE TABLE dc3_event_history
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
record_id CHAR(36) NOT NULL, -- Record UUID
tenant_id BIGINT NOT NULL, -- Tenant ID
device_id BIGINT NOT NULL, -- Device ID
event_id BIGINT NOT NULL, -- Event ID
event_code VARCHAR(128) NOT NULL, -- Event code
event_type_flag SMALLINT NOT NULL, -- Event type flag
event_level_flag SMALLINT NOT NULL, -- Event level flag
param_values JSONB, -- Parameter values (JSONB)
config_snapshot JSONB, -- Event config snapshot (JSONB)
message VARCHAR(1024), -- Event message
occur_time TIMESTAMPTZ NOT NULL, -- Occurrence time
receive_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Receive time
acknowledge_flag SMALLINT DEFAULT 0 NOT NULL, -- Acknowledge flag, 0: unacknowledged, 1: acknowledged
acknowledge_time TIMESTAMPTZ, -- Acknowledge time
acknowledge_user_id BIGINT, -- Acknowledge user ID
schema_version SMALLINT NOT NULL, -- Schema version
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
CONSTRAINT chk_event_history_event_type_flag CHECK (event_type_flag BETWEEN 0 AND 3),
CONSTRAINT chk_event_history_event_level_flag CHECK (event_level_flag BETWEEN 0 AND 3),
CONSTRAINT chk_event_history_acknowledge_flag CHECK (acknowledge_flag IN (0, 1))
);
-- record_id is the external correlation key used by HTTP and gRPC callbacks.
CREATE UNIQUE INDEX idx_event_history_unique
ON dc3_event_history (record_id);
-- Tenant-scoped event record pages sort by occurrence time.
CREATE INDEX idx_event_history_tenant_time
ON dc3_event_history (tenant_id, occur_time DESC);
CREATE INDEX idx_event_history_lookup
ON dc3_event_history (tenant_id, device_id, event_id, occur_time DESC);
CREATE INDEX idx_event_history_type
ON dc3_event_history (tenant_id, event_type_flag, occur_time DESC);
CREATE INDEX idx_event_history_ack
ON dc3_event_history (tenant_id, acknowledge_flag, occur_time DESC);
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_event_history
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_event_history IS 'Event report history table';
COMMENT ON COLUMN dc3_event_history.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_event_history.record_id IS 'Record UUID';
COMMENT ON COLUMN dc3_event_history.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_event_history.device_id IS 'Device ID';
COMMENT ON COLUMN dc3_event_history.event_id IS 'Event ID';
COMMENT ON COLUMN dc3_event_history.event_code IS 'Event code';
COMMENT ON COLUMN dc3_event_history.event_type_flag IS 'Event type flag';
COMMENT ON COLUMN dc3_event_history.event_level_flag IS 'Event level flag';
COMMENT ON COLUMN dc3_event_history.param_values IS 'Parameter values (JSONB)';
COMMENT ON COLUMN dc3_event_history.config_snapshot IS 'Event config snapshot (JSONB)';
COMMENT ON COLUMN dc3_event_history.message IS 'Event message';
COMMENT ON COLUMN dc3_event_history.occur_time IS 'Occurrence time';
COMMENT ON COLUMN dc3_event_history.receive_time IS 'Receive time';
COMMENT ON COLUMN dc3_event_history.acknowledge_flag IS 'Acknowledge flag, 0: unacknowledged, 1: acknowledged';
COMMENT ON COLUMN dc3_event_history.acknowledge_time IS 'Acknowledge time';
COMMENT ON COLUMN dc3_event_history.acknowledge_user_id IS 'Acknowledge user ID';
COMMENT ON COLUMN dc3_event_history.schema_version IS 'Schema version';
COMMENT ON COLUMN dc3_event_history.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_event_history.operate_time IS 'Operation time';
Reference in New Issue View Git Blame Copy Permalink