128 lines
6.0 KiB
SQL
128 lines
6.0 KiB
SQL
-- 第三方代理线路只以客户端不透明密文参与统一账号 revision 流。
|
|
ALTER TABLE cloud_host_sync_states
|
|
ADD COLUMN minimum_sync_contract_version INTEGER NOT NULL DEFAULT 2
|
|
CHECK (minimum_sync_contract_version IN (2, 3));
|
|
|
|
CREATE TABLE cloud_proxy_profiles (
|
|
account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
id UUID NOT NULL,
|
|
ciphertext BYTEA,
|
|
nonce BYTEA,
|
|
envelope_metadata JSONB,
|
|
source_device_id UUID NOT NULL,
|
|
revision BIGINT NOT NULL CHECK (revision > 0),
|
|
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (account_id, id),
|
|
FOREIGN KEY (account_id, source_device_id)
|
|
REFERENCES devices(account_id, id) ON DELETE RESTRICT,
|
|
CHECK (ciphertext IS NULL OR octet_length(ciphertext) <= 262144),
|
|
CHECK (nonce IS NULL OR octet_length(nonce) BETWEEN 1 AND 4096),
|
|
CHECK (envelope_metadata IS NULL OR (
|
|
jsonb_typeof(envelope_metadata) = 'object'
|
|
AND pg_column_size(envelope_metadata) <= 16384
|
|
)),
|
|
CHECK (
|
|
(is_deleted AND ciphertext IS NULL AND nonce IS NULL AND envelope_metadata IS NULL)
|
|
OR (NOT is_deleted AND ciphertext IS NOT NULL AND nonce IS NOT NULL
|
|
AND envelope_metadata IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX cloud_proxy_profiles_revision_idx
|
|
ON cloud_proxy_profiles(account_id, revision, id);
|
|
CREATE INDEX cloud_proxy_profiles_tombstone_retention_idx
|
|
ON cloud_proxy_profiles(updated_at, account_id, revision, id) WHERE is_deleted;
|
|
|
|
CREATE TABLE cloud_proxy_profile_versions (
|
|
account_id UUID NOT NULL,
|
|
resource_id UUID NOT NULL,
|
|
revision BIGINT NOT NULL CHECK (revision > 0),
|
|
ciphertext BYTEA,
|
|
nonce BYTEA,
|
|
envelope_metadata JSONB,
|
|
source_device_id UUID NOT NULL,
|
|
is_deleted BOOLEAN NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (account_id, revision),
|
|
UNIQUE (account_id, resource_id, revision),
|
|
FOREIGN KEY (account_id, resource_id)
|
|
REFERENCES cloud_proxy_profiles(account_id, id) ON DELETE CASCADE,
|
|
FOREIGN KEY (account_id, source_device_id)
|
|
REFERENCES devices(account_id, id) ON DELETE RESTRICT,
|
|
CHECK (ciphertext IS NULL OR octet_length(ciphertext) <= 262144),
|
|
CHECK (nonce IS NULL OR octet_length(nonce) BETWEEN 1 AND 4096),
|
|
CHECK (envelope_metadata IS NULL OR (
|
|
jsonb_typeof(envelope_metadata) = 'object'
|
|
AND pg_column_size(envelope_metadata) <= 16384
|
|
)),
|
|
CHECK (
|
|
(is_deleted AND ciphertext IS NULL AND nonce IS NULL AND envelope_metadata IS NULL)
|
|
OR (NOT is_deleted AND ciphertext IS NOT NULL AND nonce IS NOT NULL
|
|
AND envelope_metadata IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX cloud_proxy_profile_versions_pull_idx
|
|
ON cloud_proxy_profile_versions(account_id, resource_id, revision DESC);
|
|
CREATE INDEX cloud_proxy_profile_versions_retention_idx
|
|
ON cloud_proxy_profile_versions(recorded_at, account_id, revision);
|
|
|
|
ALTER TABLE cloud_sync_push_results
|
|
DROP CONSTRAINT cloud_sync_push_results_resource_kind_check,
|
|
ADD CONSTRAINT cloud_sync_push_results_resource_kind_check
|
|
CHECK (resource_kind IN ('host', 'ai_provider_account', 'proxy_profile'));
|
|
ALTER TABLE cloud_sync_resource_deliveries
|
|
DROP CONSTRAINT cloud_sync_resource_deliveries_resource_kind_check,
|
|
ADD CONSTRAINT cloud_sync_resource_deliveries_resource_kind_check
|
|
CHECK (resource_kind IN ('host', 'ai_provider_account', 'proxy_profile'));
|
|
ALTER TABLE cloud_sync_pull_decisions
|
|
DROP CONSTRAINT cloud_sync_pull_decisions_resource_kind_check,
|
|
ADD CONSTRAINT cloud_sync_pull_decisions_resource_kind_check
|
|
CHECK (resource_kind IN ('host', 'ai_provider_account', 'proxy_profile'));
|
|
ALTER TABLE cloud_sync_rekey_resource_results
|
|
DROP CONSTRAINT cloud_sync_rekey_resource_results_resource_kind_check,
|
|
ADD CONSTRAINT cloud_sync_rekey_resource_results_resource_kind_check
|
|
CHECK (resource_kind IN ('host', 'ai_provider_account', 'proxy_profile'));
|
|
ALTER TABLE cloud_data_protection_migration_results
|
|
DROP CONSTRAINT cloud_data_protection_migration_results_resource_kind_check,
|
|
ADD CONSTRAINT cloud_data_protection_migration_results_resource_kind_check
|
|
CHECK (resource_kind IN ('host', 'ai_provider_account', 'proxy_profile'));
|
|
|
|
ALTER TABLE audit_events
|
|
DROP CONSTRAINT audit_events_sync_rekey_v2_semantic_contract;
|
|
ALTER TABLE audit_events
|
|
ADD CONSTRAINT audit_events_sync_rekey_v2_semantic_contract CHECK (
|
|
action <> 'sync.encrypted_data_rekey_v2'
|
|
OR (
|
|
actor_account_id IS NOT NULL
|
|
AND resource_kind = 'sync_account'
|
|
AND resource_id = actor_account_id::TEXT
|
|
AND outcome = 'success'
|
|
AND details ?& ARRAY[
|
|
'mutation_id','device_id','changed_hosts','changed_ai_providers',
|
|
'result_revision','previous_sync_generation','sync_generation'
|
|
]
|
|
AND details - ARRAY[
|
|
'mutation_id','device_id','changed_hosts','changed_ai_providers',
|
|
'changed_proxy_profiles','result_revision',
|
|
'previous_sync_generation','sync_generation'
|
|
] = '{}'::jsonb
|
|
AND jsonb_typeof(details->'mutation_id') = 'string'
|
|
AND jsonb_typeof(details->'device_id') = 'string'
|
|
AND jsonb_typeof(details->'changed_hosts') = 'number'
|
|
AND jsonb_typeof(details->'changed_ai_providers') = 'number'
|
|
-- 已发布的二资源审计正文不可回写；仅兼容缺少新增计数的完整旧形状。
|
|
-- 新写入由同一 typed producer 始终提供 changed_proxy_profiles。
|
|
AND (NOT (details ? 'changed_proxy_profiles')
|
|
OR jsonb_typeof(details->'changed_proxy_profiles') = 'number')
|
|
AND jsonb_typeof(details->'result_revision') = 'number'
|
|
AND jsonb_typeof(details->'previous_sync_generation') = 'number'
|
|
AND jsonb_typeof(details->'sync_generation') = 'number'
|
|
AND (details->>'previous_sync_generation')::BIGINT > 0
|
|
AND (details->>'sync_generation')::BIGINT
|
|
= (details->>'previous_sync_generation')::BIGINT + 1
|
|
)
|
|
);
|