mirror of
https://gitee.com/pnoker/iot-dc3.git
synced 2026-11-03 03:32:08 +08:00
pnoker
f970ebc4e8
the client_credentials grant could never complete: the authorization insert left principal_type null, state/code/refresh columns were declared not null for a grant that never sets them, and the connection grant type silently fell back to authorization_code because builders match on the enum value, not the name - stamp the service-account principal type on the cc authorization - bind explicit nulls for grant-specific columns in insertauthorization - make state_hash, authorization_code_hash and the refresh hashes nullable in seed ddl and resync the embedded schema fingerprint
238 lines
13 KiB
SQL
238 lines
13 KiB
SQL
/*
|
|
* 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/>.
|
|
*/
|
|
|
|
/* Runtime coordination tables shared by all reactive centers. */
|
|
SET search_path TO public;
|
|
|
|
CREATE TABLE IF NOT EXISTS dc3_schema_fingerprint
|
|
(
|
|
fingerprint_version SMALLINT PRIMARY KEY,
|
|
ddl_hash TEXT NOT NULL,
|
|
schema_contract TEXT NOT NULL,
|
|
id_format TEXT NOT NULL,
|
|
time_format TEXT NOT NULL,
|
|
json_format TEXT NOT NULL,
|
|
generated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
COMMENT ON TABLE dc3_schema_fingerprint IS 'Canonical clean-DDL fingerprint used for startup validation';
|
|
COMMENT ON COLUMN dc3_schema_fingerprint.fingerprint_version IS 'Fingerprint schema version';
|
|
COMMENT ON COLUMN dc3_schema_fingerprint.ddl_hash IS 'SHA-256 hash of canonical initialization SQL';
|
|
COMMENT ON COLUMN dc3_schema_fingerprint.schema_contract IS 'Flag-day architecture contract identifier';
|
|
COMMENT ON COLUMN dc3_schema_fingerprint.id_format IS 'Identifier encoding contract';
|
|
COMMENT ON COLUMN dc3_schema_fingerprint.time_format IS 'Timestamp precision and timezone contract';
|
|
COMMENT ON COLUMN dc3_schema_fingerprint.json_format IS 'Canonical JSON encoding contract';
|
|
COMMENT ON COLUMN dc3_schema_fingerprint.generated_at IS 'Fingerprint generation timestamp in UTC';
|
|
|
|
CREATE TABLE IF NOT EXISTS dc3_operation
|
|
(
|
|
operation_id UUID PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
idempotency_key TEXT NOT NULL,
|
|
request_hash CHAR(64) NOT NULL,
|
|
status TEXT NOT NULL,
|
|
progress SMALLINT NOT NULL DEFAULT 0,
|
|
result JSONB,
|
|
error JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at TIMESTAMPTZ,
|
|
CONSTRAINT chk_dc3_operation_idempotency_key CHECK (length(btrim(idempotency_key)) BETWEEN 1 AND 191),
|
|
CONSTRAINT chk_dc3_operation_progress CHECK (progress BETWEEN 0 AND 100),
|
|
CONSTRAINT chk_dc3_operation_request_hash CHECK (request_hash ~ '^[0-9a-fA-F]{64}$'),
|
|
CONSTRAINT chk_dc3_operation_status CHECK (status IN ('PENDING', 'RUNNING', 'SUCCEEDED', 'FAILED', 'CANCELLED', 'EXPIRED')),
|
|
CONSTRAINT uq_dc3_operation_tenant_key UNIQUE (tenant_id, idempotency_key)
|
|
);
|
|
COMMENT ON TABLE dc3_operation IS 'Long-running operation state and result';
|
|
COMMENT ON COLUMN dc3_operation.operation_id IS 'UUIDv7 operation identifier';
|
|
COMMENT ON COLUMN dc3_operation.tenant_id IS 'Owning tenant identifier';
|
|
COMMENT ON COLUMN dc3_operation.idempotency_key IS 'Client idempotency key; unique within tenant';
|
|
COMMENT ON COLUMN dc3_operation.request_hash IS 'Canonical request SHA-256';
|
|
COMMENT ON COLUMN dc3_operation.status IS 'Operation lifecycle status';
|
|
COMMENT ON COLUMN dc3_operation.progress IS 'Completion percentage from 0 to 100';
|
|
COMMENT ON COLUMN dc3_operation.result IS 'Successful operation result JSON';
|
|
COMMENT ON COLUMN dc3_operation.error IS 'Failure Problem Details JSON';
|
|
COMMENT ON COLUMN dc3_operation.created_at IS 'Creation timestamp in UTC';
|
|
COMMENT ON COLUMN dc3_operation.updated_at IS 'Last update timestamp in UTC';
|
|
COMMENT ON COLUMN dc3_operation.expires_at IS 'Optional expiration timestamp in UTC';
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_dc3_operation_tenant_status ON dc3_operation (tenant_id, status, created_at DESC);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_dc3_operation_tenant_id ON dc3_operation (tenant_id, operation_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS dc3_manager.dc3_device_import_job
|
|
(
|
|
operation_id UUID PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
driver_id BIGINT NOT NULL,
|
|
profile_id BIGINT NOT NULL,
|
|
operator_id BIGINT NOT NULL DEFAULT 0,
|
|
operator_name TEXT NOT NULL DEFAULT '',
|
|
file_name TEXT NOT NULL,
|
|
file_data BYTEA NOT NULL,
|
|
claimed_by TEXT NOT NULL DEFAULT '',
|
|
claimed_until TIMESTAMPTZ,
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_device_import_job_operation FOREIGN KEY (tenant_id, operation_id)
|
|
REFERENCES dc3_operation (tenant_id, operation_id) ON DELETE CASCADE,
|
|
CONSTRAINT chk_device_import_job_attempts CHECK (attempts >= 0),
|
|
CONSTRAINT chk_device_import_job_file_size CHECK (octet_length(file_data) BETWEEN 1 AND 20971520)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_device_import_job_claim
|
|
ON dc3_manager.dc3_device_import_job (claimed_until, created_at);
|
|
COMMENT ON TABLE dc3_manager.dc3_device_import_job IS 'Durable XLSX device-import job payload';
|
|
|
|
CREATE TABLE IF NOT EXISTS dc3_outbox
|
|
(
|
|
event_id UUID PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
aggregate_type TEXT NOT NULL,
|
|
aggregate_id UUID,
|
|
event_type TEXT NOT NULL,
|
|
payload JSONB NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'PENDING',
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
available_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
claimed_at TIMESTAMPTZ,
|
|
published_at TIMESTAMPTZ,
|
|
last_error TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT chk_dc3_outbox_status CHECK (status IN ('PENDING', 'CLAIMED', 'PUBLISHED', 'FAILED')),
|
|
CONSTRAINT chk_dc3_outbox_attempts CHECK (attempts >= 0)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_dc3_outbox_claim ON dc3_outbox (status, available_at, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_dc3_outbox_tenant ON dc3_outbox (tenant_id, created_at DESC);
|
|
COMMENT ON TABLE dc3_outbox IS 'Transactional outbox for durable event publication';
|
|
COMMENT ON COLUMN dc3_outbox.event_id IS 'UUIDv7 event identifier';
|
|
COMMENT ON COLUMN dc3_outbox.tenant_id IS 'Owning tenant identifier';
|
|
COMMENT ON COLUMN dc3_outbox.aggregate_type IS 'Aggregate type';
|
|
COMMENT ON COLUMN dc3_outbox.aggregate_id IS 'Aggregate UUID';
|
|
COMMENT ON COLUMN dc3_outbox.event_type IS 'Event type';
|
|
COMMENT ON COLUMN dc3_outbox.payload IS 'Canonical event JSON payload';
|
|
COMMENT ON COLUMN dc3_outbox.status IS 'Publication lifecycle status';
|
|
COMMENT ON COLUMN dc3_outbox.attempts IS 'Publication attempt count';
|
|
COMMENT ON COLUMN dc3_outbox.available_at IS 'Earliest retry publication time';
|
|
COMMENT ON COLUMN dc3_outbox.claimed_at IS 'Claim timestamp';
|
|
COMMENT ON COLUMN dc3_outbox.published_at IS 'Successful publication timestamp';
|
|
COMMENT ON COLUMN dc3_outbox.last_error IS 'Last publication failure';
|
|
COMMENT ON COLUMN dc3_outbox.created_at IS 'Creation timestamp in UTC';
|
|
|
|
CREATE TABLE IF NOT EXISTS dc3_idempotency
|
|
(
|
|
tenant_id BIGINT NOT NULL,
|
|
idempotency_key TEXT NOT NULL,
|
|
request_hash TEXT NOT NULL,
|
|
operation_id UUID NOT NULL,
|
|
status TEXT NOT NULL,
|
|
response JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
PRIMARY KEY (tenant_id, idempotency_key),
|
|
CONSTRAINT chk_dc3_idempotency_key CHECK (length(btrim(idempotency_key)) BETWEEN 1 AND 191),
|
|
CONSTRAINT chk_dc3_idempotency_request_hash CHECK (request_hash ~ '^[0-9a-fA-F]{64}$'),
|
|
CONSTRAINT chk_dc3_idempotency_status CHECK (status IN ('PENDING', 'SUCCEEDED', 'FAILED', 'CANCELLED', 'EXPIRED')),
|
|
CONSTRAINT fk_dc3_idempotency_operation FOREIGN KEY (tenant_id, operation_id)
|
|
REFERENCES dc3_operation (tenant_id, operation_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_dc3_idempotency_expiry ON dc3_idempotency (expires_at);
|
|
COMMENT ON TABLE dc3_idempotency IS 'Tenant-scoped idempotency keys and operation results';
|
|
COMMENT ON COLUMN dc3_idempotency.tenant_id IS 'Owning tenant identifier';
|
|
COMMENT ON COLUMN dc3_idempotency.idempotency_key IS 'Client supplied idempotency key';
|
|
COMMENT ON COLUMN dc3_idempotency.request_hash IS 'Canonical request SHA-256';
|
|
COMMENT ON COLUMN dc3_idempotency.operation_id IS 'Associated operation UUID';
|
|
COMMENT ON COLUMN dc3_idempotency.status IS 'Idempotency lifecycle status';
|
|
COMMENT ON COLUMN dc3_idempotency.response IS 'Canonical response JSON';
|
|
COMMENT ON COLUMN dc3_idempotency.created_at IS 'Creation timestamp in UTC';
|
|
COMMENT ON COLUMN dc3_idempotency.expires_at IS 'Expiry timestamp in UTC';
|
|
|
|
CREATE TABLE IF NOT EXISTS dc3_platform_lock
|
|
(
|
|
lock_name TEXT PRIMARY KEY,
|
|
fencing_token BIGINT NOT NULL,
|
|
holder UUID,
|
|
expires_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
COMMENT ON TABLE dc3_platform_lock IS 'Database row locks with fencing tokens';
|
|
COMMENT ON COLUMN dc3_platform_lock.lock_name IS 'Logical lock name';
|
|
COMMENT ON COLUMN dc3_platform_lock.fencing_token IS 'Monotonically increasing fencing token';
|
|
COMMENT ON COLUMN dc3_platform_lock.holder IS 'Current holder UUID';
|
|
COMMENT ON COLUMN dc3_platform_lock.expires_at IS 'Lease expiration timestamp in UTC';
|
|
|
|
-- The compose default role owns the database; deployments with a dedicated
|
|
-- runtime role can grant these tables explicitly during provisioning.
|
|
|
|
-- Durable point-value ingest receipt. A row is created before TSDB/latest writes;
|
|
-- CLAIMED leases make crash recovery deterministic and tenant-scoped message IDs
|
|
-- provide the idempotency boundary across Data Center replicas.
|
|
CREATE TABLE IF NOT EXISTS dc3_point_value_ingest_outbox
|
|
(
|
|
tenant_id BIGINT NOT NULL,
|
|
message_id TEXT NOT NULL,
|
|
schema_version INTEGER NOT NULL,
|
|
driver_node TEXT NOT NULL,
|
|
sequence BIGINT NOT NULL,
|
|
fencing_token BIGINT NOT NULL,
|
|
device_id BIGINT NOT NULL,
|
|
point_id BIGINT NOT NULL,
|
|
raw_value TEXT NOT NULL,
|
|
cal_value TEXT NOT NULL,
|
|
num_value DOUBLE PRECISION,
|
|
driver_id BIGINT NOT NULL,
|
|
create_time TIMESTAMPTZ NOT NULL,
|
|
operate_time TIMESTAMPTZ NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'PENDING',
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
available_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
claimed_at TIMESTAMPTZ,
|
|
claimed_by TEXT,
|
|
processed_at TIMESTAMPTZ,
|
|
last_error TEXT,
|
|
PRIMARY KEY (tenant_id, message_id),
|
|
CONSTRAINT chk_point_value_outbox_status CHECK (status IN ('PENDING', 'CLAIMED', 'PERSISTED', 'PROCESSED', 'FAILED')),
|
|
CONSTRAINT chk_point_value_outbox_attempts CHECK (attempts >= 0)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_point_value_outbox_claim
|
|
ON dc3_point_value_ingest_outbox (status, available_at, claimed_at);
|
|
CREATE INDEX IF NOT EXISTS idx_point_value_outbox_series
|
|
ON dc3_point_value_ingest_outbox (tenant_id, device_id, point_id, create_time DESC);
|
|
COMMENT ON TABLE dc3_point_value_ingest_outbox IS 'Durable point-value ingest receipt and replay state';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.tenant_id IS 'Owning tenant ID';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.message_id IS 'Source message idempotency key';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.schema_version IS 'Payload schema version';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.driver_node IS 'Source driver node';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.sequence IS 'Source sequence number';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.fencing_token IS 'Lease fencing token';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.device_id IS 'Device ID';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.point_id IS 'Point ID';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.raw_value IS 'Raw value';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.cal_value IS 'Calibrated value';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.num_value IS 'Numeric value';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.driver_id IS 'Driver ID';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.create_time IS 'Source creation time in UTC';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.operate_time IS 'Source operation time in UTC';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.status IS 'Ingest lifecycle status';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.attempts IS 'Claim attempt count';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.available_at IS 'Earliest replay time';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.claimed_at IS 'Current claim timestamp';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.claimed_by IS 'Current claim owner';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.processed_at IS 'Alert processing completion time';
|
|
COMMENT ON COLUMN dc3_point_value_ingest_outbox.last_error IS 'Last processing error';
|
|
|
|
INSERT INTO dc3_schema_fingerprint
|
|
(fingerprint_version, ddl_hash, schema_contract, id_format, time_format, json_format)
|
|
VALUES (2, '713c2637af52d8e1e16d3d42ae8f85f48bc9e0f35d3f316509799f6a4490e136', 'r2dbc-flag-day-v1', 'uuidv7-bigint', 'utc-micros', 'canonical-v1')
|
|
ON CONFLICT (fingerprint_version) DO NOTHING;
|