233 lines
11 KiB
PL/PgSQL
233 lines
11 KiB
PL/PgSQL
BEGIN;
|
|
|
|
INSERT INTO tenant (id, public_id, name, status, policy_version)
|
|
VALUES
|
|
('10000000-0000-0000-0000-000000000001', '10000000-0000-0000-0000-000000000010', '合成数据一号客户组织', 'active', 'local-v1'),
|
|
('20000000-0000-0000-0000-000000000001', '20000000-0000-0000-0000-000000000010', '合成数据二号客户组织', 'active', 'local-v1')
|
|
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, status = EXCLUDED.status,
|
|
policy_version = EXCLUDED.policy_version, updated_at = now();
|
|
|
|
INSERT INTO user_account (id, public_id, tenant_id, oidc_issuer, oidc_subject, display_name, status, platform_role, account_kind)
|
|
VALUES
|
|
('10000000-0000-0000-0000-000000000101', '10000000-0000-0000-0000-000000000111', '10000000-0000-0000-0000-000000000001', 'http://localhost:58180/realms/voice-local', '00000000-0000-0000-0000-000000000101', '本地所有者', 'active', 'customer', 'customer'),
|
|
('10000000-0000-0000-0000-000000000102', '10000000-0000-0000-0000-000000000112', '10000000-0000-0000-0000-000000000001', 'http://localhost:58180/realms/voice-local', '00000000-0000-0000-0000-000000000102', '本地编辑者', 'active', 'customer', 'customer'),
|
|
('20000000-0000-0000-0000-000000000103', '20000000-0000-0000-0000-000000000113', '20000000-0000-0000-0000-000000000001', 'http://localhost:58180/realms/voice-local', '00000000-0000-0000-0000-000000000103', '隔离租户查看者', 'active', 'customer', 'customer'),
|
|
('10000000-0000-0000-0000-000000000104', '10000000-0000-0000-0000-000000000114', '10000000-0000-0000-0000-000000000001', 'http://localhost:58180/realms/voice-local', '00000000-0000-0000-0000-000000000104', '平台运维管理员', 'active', 'ops_admin', 'operator'),
|
|
('10000000-0000-0000-0000-000000000105', '10000000-0000-0000-0000-000000000115', '10000000-0000-0000-0000-000000000001', 'http://localhost:58180/realms/voice-local', '00000000-0000-0000-0000-000000000105', '平台运维审批员', 'active', 'ops_approver', 'operator')
|
|
ON CONFLICT (id) DO UPDATE SET
|
|
oidc_issuer = EXCLUDED.oidc_issuer,
|
|
oidc_subject = EXCLUDED.oidc_subject,
|
|
display_name = EXCLUDED.display_name,
|
|
status = EXCLUDED.status,
|
|
platform_role = EXCLUDED.platform_role,
|
|
account_kind = EXCLUDED.account_kind,
|
|
updated_at = now();
|
|
|
|
INSERT INTO household (id, public_id, tenant_id, name, owner_user_id, status)
|
|
VALUES
|
|
('10000000-0000-0000-0000-000000000201', '10000000-0000-0000-0000-000000000211', '10000000-0000-0000-0000-000000000001', '合成数据一号家庭', '10000000-0000-0000-0000-000000000101', 'active'),
|
|
('20000000-0000-0000-0000-000000000201', '20000000-0000-0000-0000-000000000211', '20000000-0000-0000-0000-000000000001', '合成数据二号家庭', '20000000-0000-0000-0000-000000000103', 'active')
|
|
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, status = EXCLUDED.status, updated_at = now();
|
|
|
|
INSERT INTO household_member (id, public_id, tenant_id, household_id, user_id, role, status)
|
|
VALUES
|
|
('10000000-0000-0000-0000-000000000301', '10000000-0000-0000-0000-000000000311', '10000000-0000-0000-0000-000000000001', '10000000-0000-0000-0000-000000000201', '10000000-0000-0000-0000-000000000101', 'owner', 'active'),
|
|
('10000000-0000-0000-0000-000000000302', '10000000-0000-0000-0000-000000000312', '10000000-0000-0000-0000-000000000001', '10000000-0000-0000-0000-000000000201', '10000000-0000-0000-0000-000000000102', 'editor', 'active'),
|
|
('20000000-0000-0000-0000-000000000303', '20000000-0000-0000-0000-000000000313', '20000000-0000-0000-0000-000000000001', '20000000-0000-0000-0000-000000000201', '20000000-0000-0000-0000-000000000103', 'viewer', 'active')
|
|
ON CONFLICT (id) DO UPDATE SET role = EXCLUDED.role, status = EXCLUDED.status, updated_at = now();
|
|
|
|
INSERT INTO subject_profile (id, public_id, tenant_id, household_id, preferred_name, status, profile_version)
|
|
VALUES (
|
|
'10000000-0000-0000-0000-000000000401',
|
|
'10000000-0000-0000-0000-000000000411',
|
|
'10000000-0000-0000-0000-000000000001',
|
|
'10000000-0000-0000-0000-000000000201',
|
|
'测试长者', 'active', 1
|
|
)
|
|
ON CONFLICT (id) DO UPDATE SET preferred_name = EXCLUDED.preferred_name,
|
|
status = EXCLUDED.status, profile_version = EXCLUDED.profile_version, updated_at = now();
|
|
|
|
INSERT INTO device (
|
|
id, public_id, tenant_id, serial_digest, model, hardware_revision,
|
|
status, credential_ref, minimum_security_version, minimum_firmware_version
|
|
)
|
|
VALUES (
|
|
'10000000-0000-0000-0000-000000000501',
|
|
'10000000-0000-0000-0000-000000000511',
|
|
'10000000-0000-0000-0000-000000000001',
|
|
repeat('a', 64), 'simulated-terminal', 'sim-rev-a',
|
|
'registered', 'local-dev://simulated-terminal-1', 'dev-signature-v1', '0.1.0-sim'
|
|
)
|
|
ON CONFLICT (id) DO UPDATE SET model = EXCLUDED.model,
|
|
hardware_revision = EXCLUDED.hardware_revision, status = EXCLUDED.status,
|
|
minimum_security_version = EXCLUDED.minimum_security_version,
|
|
minimum_firmware_version = EXCLUDED.minimum_firmware_version, updated_at = now();
|
|
|
|
INSERT INTO device (
|
|
id, public_id, tenant_id, serial_digest, model, hardware_revision,
|
|
status, credential_ref, minimum_security_version, minimum_firmware_version
|
|
)
|
|
VALUES (
|
|
'10000000-0000-0000-0000-000000000502',
|
|
'10000000-0000-0000-0000-000000000512',
|
|
'10000000-0000-0000-0000-000000000001',
|
|
repeat('e', 64), 'simulated-terminal', 'sim-rev-a',
|
|
'registered', 'local-dev://unbound-terminal-2', 'dev-signature-v1', '0.1.0-sim'
|
|
)
|
|
ON CONFLICT (id) DO UPDATE SET model = EXCLUDED.model,
|
|
hardware_revision = EXCLUDED.hardware_revision, status = EXCLUDED.status,
|
|
minimum_security_version = EXCLUDED.minimum_security_version,
|
|
minimum_firmware_version = EXCLUDED.minimum_firmware_version, updated_at = now();
|
|
|
|
INSERT INTO device_binding (
|
|
id, public_id, tenant_id, device_id, household_id, subject_id, bound_by, reason
|
|
)
|
|
VALUES (
|
|
'10000000-0000-0000-0000-000000000601',
|
|
'10000000-0000-0000-0000-000000000611',
|
|
'10000000-0000-0000-0000-000000000001',
|
|
'10000000-0000-0000-0000-000000000501',
|
|
'10000000-0000-0000-0000-000000000201',
|
|
'10000000-0000-0000-0000-000000000401',
|
|
'10000000-0000-0000-0000-000000000101',
|
|
'本地端到端联调合成绑定'
|
|
)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
INSERT INTO consent_snapshot (
|
|
id, public_id, tenant_id, subject_id, snapshot_version, effective_consents, source_consent_ids
|
|
)
|
|
VALUES (
|
|
'10000000-0000-0000-0000-000000000702',
|
|
'10000000-0000-0000-0000-000000000712',
|
|
'10000000-0000-0000-0000-000000000001',
|
|
'10000000-0000-0000-0000-000000000401',
|
|
2,
|
|
'{"voice_chat":true,"transcript_storage":true,"raw_audio_storage":false,"story_extraction":true,"personalized_memory":true,"family_sharing":false}'::jsonb,
|
|
'["10000000-0000-0000-0000-000000000811","10000000-0000-0000-0000-000000000812","10000000-0000-0000-0000-000000000813"]'::jsonb
|
|
)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
INSERT INTO device_twin (
|
|
device_id, tenant_id, desired_revision, desired_config,
|
|
reported_revision, reported_config, firmware_version, board_revision,
|
|
rtc_sdk_version, capabilities, last_reported_at
|
|
)
|
|
VALUES (
|
|
'10000000-0000-0000-0000-000000000501',
|
|
'10000000-0000-0000-0000-000000000001',
|
|
1,
|
|
'{"codec":"opus","sample_rate":16000,"heartbeat_interval_seconds":15}'::jsonb,
|
|
1, '{}'::jsonb, '0.1.0-sim', 'sim-rev-a', 'mock-rtc-1',
|
|
'{"codec":["opus"],"sample_rate":[16000],"duplex":true}'::jsonb,
|
|
now()
|
|
)
|
|
ON CONFLICT (device_id) DO UPDATE SET desired_config = EXCLUDED.desired_config,
|
|
firmware_version = EXCLUDED.firmware_version, capabilities = EXCLUDED.capabilities,
|
|
last_reported_at = EXCLUDED.last_reported_at, updated_at = now();
|
|
|
|
INSERT INTO device_twin (
|
|
device_id, tenant_id, desired_revision, desired_config,
|
|
reported_revision, reported_config, firmware_version, board_revision,
|
|
rtc_sdk_version, capabilities, last_reported_at
|
|
)
|
|
VALUES (
|
|
'10000000-0000-0000-0000-000000000502',
|
|
'10000000-0000-0000-0000-000000000001',
|
|
1,
|
|
'{"codec":"opus","sample_rate":16000,"heartbeat_interval_seconds":15}'::jsonb,
|
|
NULL, '{}'::jsonb, NULL, 'sim-rev-a', NULL,
|
|
'{"codec":["opus"],"sample_rate":[16000],"duplex":true}'::jsonb,
|
|
NULL
|
|
)
|
|
ON CONFLICT (device_id) DO UPDATE SET desired_config = EXCLUDED.desired_config,
|
|
capabilities = EXCLUDED.capabilities, updated_at = now();
|
|
|
|
-- This unbound terminal predates lifecycle migration 50. Seeding happens after
|
|
-- migrations, so establish its actual lifecycle explicitly for repeatable tests.
|
|
UPDATE device SET activation_state='CLAIMABLE'
|
|
WHERE id='10000000-0000-0000-0000-000000000502'::uuid
|
|
AND enrollment_mode='legacy' AND NOT EXISTS(SELECT 1 FROM device_binding WHERE device_id=device.id AND unbound_at IS NULL);
|
|
|
|
INSERT INTO device_claim (
|
|
id, public_id, tenant_id, device_id, token_digest, expires_at
|
|
)
|
|
VALUES (
|
|
'10000000-0000-0000-0000-000000000901',
|
|
'10000000-0000-0000-0000-000000000911',
|
|
'10000000-0000-0000-0000-000000000001',
|
|
'10000000-0000-0000-0000-000000000502',
|
|
encode(digest('LOCAL-CLAIM-0001', 'sha256'), 'hex'),
|
|
'2099-01-01T00:00:00Z'
|
|
)
|
|
ON CONFLICT (id) DO UPDATE SET expires_at=EXCLUDED.expires_at,
|
|
revoked_at=NULL, used_at=NULL, used_binding_id=NULL;
|
|
|
|
INSERT INTO consent_snapshot (
|
|
id, public_id, tenant_id, subject_id, snapshot_version, effective_consents, source_consent_ids
|
|
)
|
|
VALUES (
|
|
'10000000-0000-0000-0000-000000000701',
|
|
'10000000-0000-0000-0000-000000000711',
|
|
'10000000-0000-0000-0000-000000000001',
|
|
'10000000-0000-0000-0000-000000000401',
|
|
1,
|
|
'{"voice_chat":true,"transcript":true,"audio_recording":false}'::jsonb,
|
|
'[]'::jsonb
|
|
)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
INSERT INTO consent_record (
|
|
id, public_id, tenant_id, subject_id, consent_type, scope,
|
|
policy_version, notice_digest, actor_user_id, evidence_ref, granted_at
|
|
)
|
|
VALUES (
|
|
'10000000-0000-0000-0000-000000000801',
|
|
'10000000-0000-0000-0000-000000000811',
|
|
'10000000-0000-0000-0000-000000000001',
|
|
'10000000-0000-0000-0000-000000000401',
|
|
'transcript_storage', '{"scope":"future_conversations"}'::jsonb,
|
|
'local-v1', repeat('b', 64),
|
|
'10000000-0000-0000-0000-000000000101',
|
|
'local-synthetic://notice/transcript-v1',
|
|
'2026-08-23T00:00:00Z'
|
|
)
|
|
ON CONFLICT (id) DO UPDATE SET
|
|
scope=EXCLUDED.scope, policy_version=EXCLUDED.policy_version,
|
|
notice_digest=EXCLUDED.notice_digest, evidence_ref=EXCLUDED.evidence_ref,
|
|
withdrawn_at=NULL, updated_at=now();
|
|
|
|
INSERT INTO consent_record (
|
|
id, public_id, tenant_id, subject_id, consent_type, scope,
|
|
policy_version, notice_digest, actor_user_id, evidence_ref, granted_at
|
|
)
|
|
VALUES
|
|
(
|
|
'10000000-0000-0000-0000-000000000802',
|
|
'10000000-0000-0000-0000-000000000812',
|
|
'10000000-0000-0000-0000-000000000001',
|
|
'10000000-0000-0000-0000-000000000401',
|
|
'story_extraction', '{"scope":"future_conversations"}'::jsonb,
|
|
'local-v1', repeat('c', 64),
|
|
'10000000-0000-0000-0000-000000000101',
|
|
'local-synthetic://notice/story-v1',
|
|
'2026-08-23T00:00:01Z'
|
|
),
|
|
(
|
|
'10000000-0000-0000-0000-000000000803',
|
|
'10000000-0000-0000-0000-000000000813',
|
|
'10000000-0000-0000-0000-000000000001',
|
|
'10000000-0000-0000-0000-000000000401',
|
|
'personalized_memory', '{"scope":"future_conversations"}'::jsonb,
|
|
'local-v1', repeat('d', 64),
|
|
'10000000-0000-0000-0000-000000000101',
|
|
'local-synthetic://notice/memory-v1',
|
|
'2026-08-23T00:00:02Z'
|
|
)
|
|
ON CONFLICT (id) DO UPDATE SET
|
|
scope=EXCLUDED.scope, policy_version=EXCLUDED.policy_version,
|
|
notice_digest=EXCLUDED.notice_digest, evidence_ref=EXCLUDED.evidence_ref,
|
|
withdrawn_at=NULL, updated_at=now();
|
|
|
|
COMMIT;
|