Version: 0.1 (Draft) Date: 2025-08-08 Owner: Architecture & Data Team
Defines the logical and physical data model for the ICT Governance Platform, covering core entities, relationships, naming conventions, indexing/partitioning, and data governance controls for security, privacy, and compliance.
CREATE TABLE governance_policy (
policy_id BIGINT IDENTITY PRIMARY KEY,
name NVARCHAR(200) NOT NULL,
version NVARCHAR(20) NOT NULL,
status NVARCHAR(40) NOT NULL,
owner_id BIGINT NOT NULL,
scope_json NVARCHAR(MAX) NULL,
effective_date DATE NULL,
review_date DATE NULL,
tags NVARCHAR(400) NULL,
created_utc DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
updated_utc DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
is_deleted BIT NOT NULL DEFAULT 0,
deleted_utc DATETIME2 NULL
);
CREATE UNIQUE INDEX ux_policy_name_version ON governance_policy(name, version);
CREATE TABLE audit_audit_log (
audit_id BIGINT IDENTITY PRIMARY KEY,
ref_type NVARCHAR(60) NOT NULL,
ref_id BIGINT NOT NULL,
actor_id BIGINT NOT NULL,
action NVARCHAR(60) NOT NULL,
at_utc DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
details_json NVARCHAR(MAX) NULL,
prev_hash VARBINARY(64) NULL,
tamper_hash AS HASHBYTES('SHA2_256', CONCAT(ref_type, ':', ref_id, ':', actor_id, ':', action, ':', CONVERT(nvarchar(30), at_utc, 126), ':', ISNULL(CONVERT(nvarchar(max), details_json), ''), ':', ISNULL(CONVERT(nvarchar(128), prev_hash, 1), ''))) PERSISTED
);