-- ===================================================================== -- Omika Diagnostic Center — SEED DE DÉVELOPPEMENT LOCAL (FAKE DATA) -- seed_diagnostics_local.sql -- ===================================================================== -- ⚠️ RÉSERVÉ AU DÉVELOPPEMENT LOCAL — NE JAMAIS EXÉCUTER EN PRODUCTION. -- ⚠️ Ce script insère des données SYNTHÉTIQUES marquées source='local-seed' -- et message préfixé '[FAKE LOCAL]'. Aucune donnée réelle, aucune donnée -- personnelle, aucun token, aucun mot de passe, aucun CodeClient réel, -- aucun nom de société cliente réelle, aucun nom de machine réel. -- ⚠️ Aucune exécution automatique : à lancer manuellement avec psql. -- ⚠️ Nettoie UNIQUEMENT les lignes source='local-seed' avant de réinsérer -- (ré-exécutable : clear → seed → clear → seed, aucune dérive). Ne touche -- jamais aux vraies données. -- -- Tâche T13-G — SEED MULTI-CLIENTS (validation end-to-end du contexte) : -- * 3 clients synthétiques : DEMO-CLIENT-A (fort volume), DEMO-CLIENT-B -- (volume moyen), DEMO-CLIENT-C (faible volume). -- * Plusieurs ServerInstance par client (API / WORKER), faibles cardinalités, -- stables, lisibles, jamais le nom réel de la machine de dev. -- * 3 environnements : Production (majorité), Staging, Development. -- * Croisements RÉALISTES (pas de produit cartésien artificiel). -- * Quelques événements historiques SANS contexte (client_code / server_instance -- / environment = NULL) pour valider la compatibilité arrière (§T13-G/10). -- * Source = 'local-seed' partout ; messages préfixés '[FAKE LOCAL]'. -- ===================================================================== -- Garde-fou : refuse de s'exécuter hors de la base locale omika_diagnostics. -- (Ne suffit PAS à distinguer une base de production portant le même nom : -- la discipline manuelle reste obligatoire.) DO $$ BEGIN IF current_database() <> 'omika_diagnostics' THEN RAISE EXCEPTION 'REFUS : seed LOCAL uniquement sur la base omika_diagnostics (base détectée : %).', current_database(); END IF; END $$; -- Nettoyage idempotent du seed précédent (source = 'local-seed' uniquement). DELETE FROM diagnostic_event WHERE source = 'local-seed'; -- ===================================================================== -- CONVENTIONS DU SEED MULTI-CLIENTS (T13-G) -- ===================================================================== -- ClientCode (synthétique) ServerInstance Environment -- --------------------------- --------------------------- ----------- -- DEMO-CLIENT-A (fort volume) API-A-01, WORKER-A-01 Production -- DEMO-CLIENT-B (moyen) API-B-01, WORKER-B-01, Production -- API-B-STG-01 Staging -- DEMO-CLIENT-C (faible) API-C-DEV-01 Development -- -- Règle T13-G/14 : pour une même opération, API et Worker partagent -- ClientCode + Environment, mais ont des ServerInstance différentes. -- Les événements MOBILE (Omika.App) sont ingérés via POST /api/diagnostics -- → ils portent la ServerInstance de l'API qui les a reçus (ex. API-A-01). -- ===================================================================== -- ===================================================================== -- 1. VOLUME DE FOND MULTI-CLIENTS (réparti 0-6h / 6-24h / 1-7j / 7-30j) -- ===================================================================== -- 1.1 DEMO-CLIENT-A / API-A-01 / Production — 80 événements HTTP API. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('1a000000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - CASE (g % 4) WHEN 0 THEN (random() * interval '6 hours') WHEN 1 THEN interval '6 hours' + (random() * interval '18 hours') WHEN 2 THEN interval '1 day' + (random() * interval '6 days') ELSE interval '7 days' + (random() * interval '23 days') END, (ARRAY['P2','P3','P2','P1','P3'])[1 + (g % 5)], (ARRAY['Info','Info','Debug','Warning','Info'])[1 + (g % 5)], (ARRAY['OMK-API-HTTP-001','OMK-API-HTTP-002'])[1 + (g % 2)], 'Omika.Api', NULL, NULL, NULL, (ARRAY['fake-actor-001','fake-actor-002',NULL])[1 + (g % 3)], 1, (ARRAY['1.4.0.0','1.4.1.0'])[1 + (g % 2)], 'api', NULL, '[FAKE LOCAL] api DEMO-CLIENT-A production ' || g, NULL, NULL, (ARRAY[25,150,350,1200,NULL,80])[1 + (g % 6)]::bigint, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production' FROM generate_series(1, 80) AS g; -- 1.2 DEMO-CLIENT-A / WORKER-A-01 / Production — 50 traitements Worker réussis. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('1b000000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - CASE (g % 4) WHEN 0 THEN (random() * interval '6 hours') WHEN 1 THEN interval '6 hours' + (random() * interval '18 hours') WHEN 2 THEN interval '1 day' + (random() * interval '6 days') ELSE interval '7 days' + (random() * interval '23 days') END, 'P3', (ARRAY['Info','Debug','Info'])[1 + (g % 3)], 'OMK-WKR-SYNC-001', 'Omika.Worker', NULL, NULL, NULL, NULL, NULL, (ARRAY['1.4.0.0','1.4.1.0'])[1 + (g % 2)], 'worker', NULL, '[FAKE LOCAL] worker DEMO-CLIENT-A production ' || g, NULL, NULL, (ARRAY[300,450,600,900])[1 + (g % 4)]::bigint, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","retryCount":0}'::jsonb, now(), 'DEMO-CLIENT-A', 'WORKER-A-01', 'Production' FROM generate_series(1, 50) AS g; -- 1.3 DEMO-CLIENT-B / API-B-01 / Production — 40 événements HTTP API. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('1c000000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - CASE (g % 4) WHEN 0 THEN (random() * interval '6 hours') WHEN 1 THEN interval '6 hours' + (random() * interval '18 hours') WHEN 2 THEN interval '1 day' + (random() * interval '6 days') ELSE interval '7 days' + (random() * interval '23 days') END, (ARRAY['P2','P3','P2','P1','P3'])[1 + (g % 5)], (ARRAY['Info','Info','Debug','Warning','Info'])[1 + (g % 5)], (ARRAY['OMK-API-HTTP-001','OMK-API-HTTP-002'])[1 + (g % 2)], 'Omika.Api', NULL, NULL, NULL, (ARRAY['fake-actor-002','fake-actor-003',NULL])[1 + (g % 3)], 1, (ARRAY['1.3.8.0','1.4.1.0'])[1 + (g % 2)], 'api', NULL, '[FAKE LOCAL] api DEMO-CLIENT-B production ' || g, NULL, NULL, (ARRAY[30,140,360,1100,NULL,90])[1 + (g % 6)]::bigint, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production' FROM generate_series(1, 40) AS g; -- 1.4 DEMO-CLIENT-B / WORKER-B-01 / Production — 20 traitements Worker réussis. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('1d000000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - CASE (g % 4) WHEN 0 THEN (random() * interval '6 hours') WHEN 1 THEN interval '6 hours' + (random() * interval '18 hours') WHEN 2 THEN interval '1 day' + (random() * interval '6 days') ELSE interval '7 days' + (random() * interval '23 days') END, 'P3', (ARRAY['Info','Debug','Info'])[1 + (g % 3)], 'OMK-WKR-SYNC-001', 'Omika.Worker', NULL, NULL, NULL, NULL, NULL, (ARRAY['1.3.8.0','1.4.1.0'])[1 + (g % 2)], 'worker', NULL, '[FAKE LOCAL] worker DEMO-CLIENT-B production ' || g, NULL, NULL, (ARRAY[280,420,580,850])[1 + (g % 4)]::bigint, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","retryCount":0}'::jsonb, now(), 'DEMO-CLIENT-B', 'WORKER-B-01', 'Production' FROM generate_series(1, 20) AS g; -- 1.5 DEMO-CLIENT-B / API-B-STG-01 / Staging — 12 événements HTTP API. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('1e000000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - CASE (g % 4) WHEN 0 THEN (random() * interval '6 hours') WHEN 1 THEN interval '6 hours' + (random() * interval '18 hours') WHEN 2 THEN interval '1 day' + (random() * interval '6 days') ELSE interval '7 days' + (random() * interval '23 days') END, (ARRAY['P2','P3','P2'])[1 + (g % 3)], (ARRAY['Info','Debug','Info'])[1 + (g % 3)], (ARRAY['OMK-API-HTTP-001','OMK-API-HTTP-002'])[1 + (g % 2)], 'Omika.Api', NULL, NULL, NULL, 'fake-actor-003', 1, (ARRAY['1.4.1.0','1.3.8.0'])[1 + (g % 2)], 'api', NULL, '[FAKE LOCAL] api DEMO-CLIENT-B staging ' || g, NULL, NULL, (ARRAY[40,150,380])[1 + (g % 3)]::bigint, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-STG-01', 'Staging' FROM generate_series(1, 12) AS g; -- 1.6 DEMO-CLIENT-C / API-C-DEV-01 / Development — 18 événements HTTP API. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('1f000000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - CASE (g % 4) WHEN 0 THEN (random() * interval '6 hours') WHEN 1 THEN interval '6 hours' + (random() * interval '18 hours') WHEN 2 THEN interval '1 day' + (random() * interval '6 days') ELSE interval '7 days' + (random() * interval '23 days') END, 'P3', (ARRAY['Info','Debug','Warning','Info'])[1 + (g % 4)], (ARRAY['OMK-API-HTTP-001','OMK-API-HTTP-002'])[1 + (g % 2)], 'Omika.Api', NULL, NULL, NULL, NULL, NULL, '1.5.0.0', 'api', NULL, '[FAKE LOCAL] api DEMO-CLIENT-C development ' || g, NULL, NULL, (ARRAY[35,160,400])[1 + (g % 3)]::bigint, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-C', 'API-C-DEV-01', 'Development' FROM generate_series(1, 18) AS g; -- 1.7 DEMO-CLIENT-A / Production — 20 événements MOBILE (ingérés via API-A-01). -- T17-B : dimensions appareil/licence représentatives (Android / Xiaomi). INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment, license_number, device_key, device_key_version, device_manufacturer, device_model, operating_system, operating_system_version, app_build, screen_width, screen_height, logical_width, logical_height, display_scale) SELECT ('1a100000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - CASE (g % 4) WHEN 0 THEN (random() * interval '6 hours') WHEN 1 THEN interval '6 hours' + (random() * interval '18 hours') WHEN 2 THEN interval '1 day' + (random() * interval '6 days') ELSE interval '7 days' + (random() * interval '23 days') END, (ARRAY['P2','P3','P2'])[1 + (g % 3)], (ARRAY['Info','Debug','Info'])[1 + (g % 3)], (ARRAY['OMK-MOB-APP-001','OMK-MOB-APP-002','OMK-MOB-DIAG-001'])[1 + (g % 3)], 'Omika.App', NULL, NULL, NULL, (ARRAY['fake-actor-001','fake-actor-002'])[1 + (g % 2)], 1, (ARRAY['1.4.0.0','1.4.1.0'])[1 + (g % 2)], 'Android', NULL, '[FAKE LOCAL] mobile DEMO-CLIENT-A production ' || g, NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production', 'LIC-DEMO-A', (ARRAY['fake-device-key-001','fake-device-key-002'])[1 + (g % 2)], 1, 'Xiaomi', 'POCO Demo', 'Android', '16', '1410', 1080, 2400, 360, 800, 3.0 FROM generate_series(1, 20) AS g; -- 1.8 DEMO-CLIENT-B / Production — 10 événements MOBILE (ingérés via API-B-01). -- T17-B : dimensions appareil/licence représentatives (iOS / Apple). INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment, license_number, device_key, device_key_version, device_manufacturer, device_model, operating_system, operating_system_version, app_build, screen_width, screen_height, logical_width, logical_height, display_scale) SELECT ('1c100000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - CASE (g % 4) WHEN 0 THEN (random() * interval '6 hours') WHEN 1 THEN interval '6 hours' + (random() * interval '18 hours') WHEN 2 THEN interval '1 day' + (random() * interval '6 days') ELSE interval '7 days' + (random() * interval '23 days') END, 'P3', (ARRAY['Info','Debug','Info'])[1 + (g % 3)], (ARRAY['OMK-MOB-APP-001','OMK-MOB-DIAG-001'])[1 + (g % 2)], 'Omika.App', NULL, NULL, NULL, 'fake-actor-002', 1, (ARRAY['1.3.8.0','1.4.1.0'])[1 + (g % 2)], 'iOS', NULL, '[FAKE LOCAL] mobile DEMO-CLIENT-B production ' || g, NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production', 'LIC-DEMO-B', 'fake-device-key-002', 1, 'Apple', 'iPhone Demo', 'iOS', '18.5', '1390', 1179, 2556, 393, 852, 3.0 FROM generate_series(1, 10) AS g; -- ===================================================================== -- 2. GROUPES D'ERREURS MULTI-CLIENTS (fingerprint inchangé : LOWER(NULLIF(TRIM(col),''))) -- ===================================================================== -- 2.1 TimeoutException — MÊME groupe logique chez DEMO-CLIENT-A (Production) -- et DEMO-CLIENT-B (Production + Staging). Sans filtre : 1 seul groupe -- agrégeant A + B ; filtre Client / Environnement = occurrence re-comptée -- AVANT agrégation (T13-G §15/§17). -- Variations de casse/espaces volontaires (test de normalisation du fingerprint). -- 2.1a DEMO-CLIENT-A / API-A-01 / Production — 10 occurrences. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('ee000000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - (random() * interval '7 days'), 'P1', (ARRAY['Error','error'])[1 + (g % 2)], 'OMK-API-HTTP-003', (ARRAY['Omika.Api',' omika.api ','OMIKA.API'])[1 + (g % 3)], NULL, NULL, NULL, 'fake-actor-001', 1, (ARRAY['1.4.0.0','1.4.1.0'])[1 + (g % 2)], 'api', (ARRAY['TIMEOUT',' timeout ','timeout'])[1 + (g % 3)], '[FAKE LOCAL] Timeout simulation A ' || g, (ARRAY['System.TimeoutException','system.timeoutexception'])[1 + (g % 2)], E'[FAKE LOCAL]\nSystem.TimeoutException: operation timed out\n at Omika.Api.Tests.FakeService.Execute()', (ARRAY[1500,2500,3000,5000])[1 + (g % 4)]::bigint, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","method":"GET","endpoint":"/api/v1/fake","statusCode":504}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production' FROM generate_series(1, 10) AS g; -- 2.1b DEMO-CLIENT-B / API-B-01 / Production — 6 occurrences. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('ee010000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - (random() * interval '7 days'), 'P1', 'Error', 'OMK-API-HTTP-003', 'Omika.Api', NULL, NULL, NULL, 'fake-actor-002', 1, (ARRAY['1.3.8.0','1.4.1.0'])[1 + (g % 2)], 'api', 'TIMEOUT', '[FAKE LOCAL] Timeout simulation B ' || g, 'System.TimeoutException', E'[FAKE LOCAL]\nSystem.TimeoutException: operation timed out\n at Omika.Api.Tests.FakeService.Execute()', (ARRAY[1600,2600,3200])[1 + (g % 3)]::bigint, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","method":"GET","endpoint":"/api/v1/fake","statusCode":504}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production' FROM generate_series(1, 6) AS g; -- 2.1c DEMO-CLIENT-B / API-B-STG-01 / Staging — 6 occurrences (volume différent). INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('ee020000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - (random() * interval '7 days'), 'P1', 'Error', 'OMK-API-HTTP-003', 'Omika.Api', NULL, NULL, NULL, 'fake-actor-003', 1, '1.4.1.0', 'api', 'TIMEOUT', '[FAKE LOCAL] Timeout simulation B staging ' || g, 'System.TimeoutException', E'[FAKE LOCAL]\nSystem.TimeoutException: operation timed out\n at Omika.Api.Tests.FakeService.Execute()', (ARRAY[1700,2800,3400])[1 + (g % 3)]::bigint, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","method":"GET","endpoint":"/api/v1/fake","statusCode":504}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-STG-01', 'Staging' FROM generate_series(1, 6) AS g; -- 2.2 NullReferenceException — DEMO-CLIENT-A uniquement — 8 occurrences. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('ee100000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - (random() * interval '14 days'), 'P1', 'Error', 'OMK-API-REQ-001', 'Omika.Api', NULL, NULL, NULL, 'fake-actor-001', 1, (ARRAY['1.4.0.0','1.4.1.0'])[1 + (g % 2)], 'api', 'NULL_REF', '[FAKE LOCAL] NullReferenceException simulation A ' || g, 'System.NullReferenceException', E'[FAKE LOCAL]\nSystem.NullReferenceException: Object reference not set\n at Omika.Api.Tests.FakeService.Execute()', NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","endpoint":"/api/v1/fake"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production' FROM generate_series(1, 8) AS g; -- 2.3 HttpRequestException (HTTP_500) — DEMO-CLIENT-B uniquement — Production + Staging. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('ee200000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - (random() * interval '30 days'), 'P1', 'Error', 'OMK-API-HTTP-003', 'Omika.Api', NULL, NULL, NULL, 'fake-actor-002', 1, (ARRAY['1.3.8.0','1.4.1.0'])[1 + (g % 2)], 'api', 'HTTP_500', '[FAKE LOCAL] HttpRequestException simulation B ' || g, 'System.Net.Http.HttpRequestException', E'[FAKE LOCAL]\nSystem.Net.Http.HttpRequestException: 500 Internal Server Error\n at Omika.Api.Tests.FakeService.Send()', (ARRAY[800,1200,2000])[1 + (g % 3)]::bigint, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","statusCode":500}'::jsonb, now(), 'DEMO-CLIENT-B', (ARRAY['API-B-01','API-B-STG-01'])[1 + (g % 2)], (ARRAY['Production','Staging'])[1 + (g % 2)] FROM generate_series(1, 8) AS g; -- 2.4 InvalidOperationException — DEMO-CLIENT-A uniquement (mobile) — 5 occurrences. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('ee300000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - (random() * interval '10 days'), 'P2', 'Warning', 'OMK-MOB-APP-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-001', 1, '1.4.0.0', 'Android', 'INVALID_OP', '[FAKE LOCAL] InvalidOperationException simulation A ' || g, 'System.InvalidOperationException', E'[FAKE LOCAL]\nSystem.InvalidOperationException: invalid state\n at Omika.App.Tests.FakeService.Run()', NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production' FROM generate_series(1, 5) AS g; -- 2.5 Crash critique (OutOfMemoryException) — DEMO-CLIENT-C UNIQUEMENT (Development). -- Vérifie qu'un filtre Client = A/B exclut complètement ce groupe (T13-G §16). INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) SELECT ('ee400000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - (random() * interval '2 days'), 'P0', 'Critical', 'OMK-API-HTTP-003', 'Omika.Api', NULL, NULL, NULL, NULL, NULL, '1.5.0.0', 'api', 'CRASH', '[FAKE LOCAL] Critical crash simulation C ' || g, 'System.OutOfMemoryException', E'[FAKE LOCAL]\nSystem.OutOfMemoryException: insufficient memory\n at Omika.Api.Tests.FakeService.Allocate()', NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-C', 'API-C-DEV-01', 'Development' FROM generate_series(1, 3) AS g; -- ===================================================================== -- 3. TRACES SYNTHÉTIQUES (TraceId partagé) — pour tester /Traces -- (contexte client/serveur/environnement assigné par trace) -- ===================================================================== -- Trace A : DEMO-CLIENT-A / API-A-01 / Production — mobile → API → response (4 événements). INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) VALUES ('aa000000-0000-0000-0000-000000000001', now() - interval '35 minutes', 'P2', 'Info', 'OMK-MOB-APP-001', 'Omika.App', 'aaaaaaaa-1111-4111-8111-111111111111', NULL, NULL, 'fake-actor-001', 1, '1.4.1.0', 'Android', NULL, '[FAKE LOCAL] trace A - mobile started', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production'), ('aa000000-0000-0000-0000-000000000002', now() - interval '34 minutes', 'P2', 'Info', 'OMK-API-HTTP-001', 'Omika.Api', 'aaaaaaaa-1111-4111-8111-111111111111', NULL, NULL, NULL, NULL, '1.4.1.0', 'api', NULL, '[FAKE LOCAL] trace A - api request', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","method":"POST","endpoint":"/api/v1/sync"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production'), ('aa000000-0000-0000-0000-000000000003', now() - interval '33 minutes', 'P2', 'Info', 'OMK-API-HTTP-002', 'Omika.Api', 'aaaaaaaa-1111-4111-8111-111111111111', NULL, NULL, NULL, NULL, '1.4.1.0', 'api', NULL, '[FAKE LOCAL] trace A - api processing', NULL, NULL, 350, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","statusCode":200}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production'), ('aa000000-0000-0000-0000-000000000004', now() - interval '32 minutes', 'P2', 'Info', 'OMK-API-HTTP-002', 'Omika.Api', 'aaaaaaaa-1111-4111-8111-111111111111', NULL, NULL, NULL, NULL, '1.4.1.0', 'api', NULL, '[FAKE LOCAL] trace A - api response', NULL, NULL, 420, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","statusCode":200}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production'); -- Trace B : DEMO-CLIENT-B / API-B-01 / Production — API → traitement → erreur (3 événements). INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) VALUES ('bb000000-0000-0000-0000-000000000001', now() - interval '6 hours', 'P2', 'Info', 'OMK-API-HTTP-001', 'Omika.Api', 'bbbbbbbb-2222-4222-8222-222222222222', NULL, NULL, NULL, NULL, '1.4.1.0', 'api', NULL, '[FAKE LOCAL] trace B - api request', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","method":"GET","endpoint":"/api/v1/fake"}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production'), ('bb000000-0000-0000-0000-000000000003', now() - interval '6 hours' + interval '25 ms', 'P2', 'Info', 'OMK-API-HTTP-002', 'Omika.Api', 'bbbbbbbb-2222-4222-8222-222222222222', NULL, NULL, NULL, NULL, '1.4.1.0', 'api', NULL, '[FAKE LOCAL] trace B - api processing', NULL, NULL, 300, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","statusCode":200}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production'), ('bb000000-0000-0000-0000-000000000002', now() - interval '6 hours' + interval '50 ms', 'P1', 'Error', 'OMK-API-HTTP-003', 'Omika.Api', 'bbbbbbbb-2222-4222-8222-222222222222', NULL, NULL, NULL, NULL, '1.4.1.0', 'api', 'HTTP_500', '[FAKE LOCAL] trace B - traitement en erreur', 'System.Net.Http.HttpRequestException', E'[FAKE LOCAL]\nSystem.Net.Http.HttpRequestException: 500\n at Omika.Api.Tests.FakeService.Send()', 1200, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","statusCode":500}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production'); -- Trace C : DEMO-CLIENT-C / API-C-DEV-01 / Development — succès rapide (3 événements). INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) VALUES ('cc000000-0000-0000-0000-000000000001', now() - interval '12 hours', 'P3', 'Info', 'OMK-API-HTTP-001', 'Omika.Api', 'cccccccc-3333-4333-8333-333333333333', NULL, NULL, NULL, NULL, '1.5.0.0', 'api', NULL, '[FAKE LOCAL] trace C - api request', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-C', 'API-C-DEV-01', 'Development'), ('cc000000-0000-0000-0000-000000000002', now() - interval '12 hours' + interval '30 ms', 'P3', 'Info', 'OMK-API-HTTP-002', 'Omika.Api', 'cccccccc-3333-4333-8333-333333333333', NULL, NULL, NULL, NULL, '1.5.0.0', 'api', NULL, '[FAKE LOCAL] trace C - api processing', NULL, NULL, 80, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","statusCode":200}'::jsonb, now(), 'DEMO-CLIENT-C', 'API-C-DEV-01', 'Development'), ('cc000000-0000-0000-0000-000000000003', now() - interval '12 hours' + interval '45 ms', 'P3', 'Info', 'OMK-API-HTTP-002', 'Omika.Api', 'cccccccc-3333-4333-8333-333333333333', NULL, NULL, NULL, NULL, '1.5.0.0', 'api', NULL, '[FAKE LOCAL] trace C - api response', NULL, NULL, 90, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","statusCode":200}'::jsonb, now(), 'DEMO-CLIENT-C', 'API-C-DEV-01', 'Development'); -- ===================================================================== -- 4. SYNCHRONISATION SYNTHÉTIQUE (OperationId partagé) — pour tester -- /Synchronisation et /Traces. Même OperationId entre composants -- différents (mobile/API vs Worker), même ClientCode + Environment, -- ServerInstance différente (T13-G §13/§14). Aucun faux TraceId Worker. -- EventCodes exacts de DiagnosticSyncEventCodes : -- OMK-MOB-SYNC-001 / OMK-WKR-SYNC-001 / OMK-WKR-SYNC-002 -- ⚠️ FAKE LOCAL : données synthétiques (source='local-seed'), ne prouvent -- pas l'état d'instrumentation réel d'un producteur. -- ===================================================================== -- Opération X (DEMO-CLIENT-A / Production) : succès mobile → worker. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) VALUES ('55000000-0000-0000-0000-000000000001', now() - interval '1 hour', 'P2', 'Info', 'OMK-MOB-SYNC-001', 'Omika.App', NULL, '5e5e5e5e-5000-4000-8000-000000000001', '0a0a0a0a-1000-4000-8000-000000000001', 'fake-actor-001', 1, '1.4.1.0', 'Android', NULL, '[FAKE LOCAL] sync op X - operation reçue', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production'), ('55000000-0000-0000-0000-000000000002', now() - interval '59 minutes', 'P3', 'Info', 'OMK-WKR-SYNC-001', 'Omika.Worker', NULL, NULL, '0a0a0a0a-1000-4000-8000-000000000001', NULL, NULL, '1.4.1.0', 'worker', NULL, '[FAKE LOCAL] sync op X - worker processing', NULL, NULL, 450, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","retryCount":0}'::jsonb, now(), 'DEMO-CLIENT-A', 'WORKER-A-01', 'Production'), ('55000000-0000-0000-0000-000000000003', now() - interval '58 minutes', 'P3', 'Info', 'OMK-WKR-SYNC-001', 'Omika.Worker', NULL, NULL, '0a0a0a0a-1000-4000-8000-000000000001', NULL, NULL, '1.4.1.0', 'worker', NULL, '[FAKE LOCAL] sync op X - worker success', NULL, NULL, 480, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","retryCount":0}'::jsonb, now(), 'DEMO-CLIENT-A', 'WORKER-A-01', 'Production'); -- Opération Y (DEMO-CLIENT-B / Production) : échec définitif worker. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) VALUES ('55000000-0000-0000-0000-000000000004', now() - interval '3 hours', 'P1', 'Info', 'OMK-MOB-SYNC-001', 'Omika.App', NULL, '5e5e5e5e-5000-4000-8000-000000000002', '0b0b0b0b-1000-4000-8000-000000000002', 'fake-actor-002', 1, '1.3.8.0', 'iOS', NULL, '[FAKE LOCAL] sync op Y - operation reçue', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production'), ('55000000-0000-0000-0000-000000000005', now() - interval '3 hours' + interval '10 seconds', 'P1', 'Error', 'OMK-WKR-SYNC-002', 'Omika.Worker', NULL, NULL, '0b0b0b0b-1000-4000-8000-000000000002', NULL, NULL, '1.3.8.0', 'worker', 'SYNC_FAILED', '[FAKE LOCAL] sync op Y - worker failure', 'System.InvalidOperationException', E'[FAKE LOCAL]\nSystem.InvalidOperationException: sync rejected\n at Omika.Worker.Tests.FakeHandler.Apply()', 600, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","retryCount":2}'::jsonb, now(), 'DEMO-CLIENT-B', 'WORKER-B-01', 'Production'); -- Opération Z (DEMO-CLIENT-B / Production) : retry transitoire worker. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) VALUES ('55000000-0000-0000-0000-000000000006', now() - interval '9 hours', 'P2', 'Warning', 'OMK-MOB-SYNC-001', 'Omika.App', NULL, '5e5e5e5e-5000-4000-8000-000000000003', '0c0c0c0c-1000-4000-8000-000000000003', 'fake-actor-003', 1, '1.4.1.0', 'Desktop', NULL, '[FAKE LOCAL] sync op Z - operation reçue', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production'), ('55000000-0000-0000-0000-000000000007', now() - interval '9 hours' + interval '5 seconds', 'P2', 'Warning', 'OMK-WKR-SYNC-002', 'Omika.Worker', NULL, NULL, '0c0c0c0c-1000-4000-8000-000000000003', NULL, NULL, '1.4.1.0', 'worker', 'SYNC_RETRY', '[FAKE LOCAL] sync op Z - transient retry', NULL, NULL, 300, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","retryCount":1}'::jsonb, now(), 'DEMO-CLIENT-B', 'WORKER-B-01', 'Production'); -- ===================================================================== -- 5. SESSIONS PARTAGÉES (SessionId) — pour tester la corrélation du détail. -- ===================================================================== -- Session S1 (DEMO-CLIENT-A / Production). INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) VALUES ('5e000000-0000-0000-0000-000000000001', now() - interval '2 hours', 'P3', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, '5e5e5e5e-5000-4000-8000-000000000001', NULL, 'fake-actor-001', 1, '1.4.1.0', 'Android', NULL, '[FAKE LOCAL] session S1 - app started', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production'), ('5e000000-0000-0000-0000-000000000002', now() - interval '1 hour 50 minutes', 'P3', 'Info', 'OMK-MOB-DIAG-001', 'Omika.App', NULL, '5e5e5e5e-5000-4000-8000-000000000001', NULL, 'fake-actor-001', 1, '1.4.1.0', 'Android', NULL, '[FAKE LOCAL] session S1 - diagnostic init', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production'), ('5e000000-0000-0000-0000-000000000003', now() - interval '1 hour 40 minutes', 'P2', 'Warning', 'OMK-MOB-APP-002', 'Omika.App', NULL, '5e5e5e5e-5000-4000-8000-000000000001', NULL, 'fake-actor-001', 1, '1.4.1.0', 'Android', NULL, '[FAKE LOCAL] session S1 - app session stopped', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production'); -- Session S2 (DEMO-CLIENT-B / Production). INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) VALUES ('5f000000-0000-0000-0000-000000000001', now() - interval '30 minutes', 'P3', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, '5f5f5f5f-5000-4000-8000-000000000002', NULL, 'fake-actor-002', 1, '1.3.8.0', 'iOS', NULL, '[FAKE LOCAL] session S2 - app started', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production'), ('5f000000-0000-0000-0000-000000000002', now() - interval '29 minutes', 'P3', 'Info', 'OMK-MOB-DIAG-001', 'Omika.App', NULL, '5f5f5f5f-5000-4000-8000-000000000002', NULL, 'fake-actor-002', 1, '1.3.8.0', 'iOS', NULL, '[FAKE LOCAL] session S2 - diagnostic init', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production'); -- ===================================================================== -- 6. DONNÉES HISTORIQUES SANS CONTEXTE (compatibilité arrière, T13-G §10) -- client_code / server_instance / environment = NULL. Ces événements -- restent EXPLICITEMENT synthétiques (source='local-seed', [FAKE LOCAL]) et -- représentent uniquement le scénario « anciennes données avant migration ». -- Ils doivent rester VISIBLES sans filtre, et NE PAS être inclus par erreur -- quand un filtre Client/Serveur/Environnement est appliqué. -- ===================================================================== INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment) VALUES ('d0000000-0000-0000-0000-000000000001', now() - interval '18 days', 'P2', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-001', 1, '1.0.0.0', 'Android', NULL, '[FAKE LOCAL] historique sans contexte 1', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), NULL, NULL, NULL), ('d0000000-0000-0000-0000-000000000002', now() - interval '22 days', 'P3', 'Info', 'OMK-API-HTTP-001', 'Omika.Api', NULL, NULL, NULL, NULL, NULL, '1.0.1.0', 'api', NULL, '[FAKE LOCAL] historique sans contexte 2', NULL, NULL, 120, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), NULL, NULL, NULL), ('d0000000-0000-0000-0000-000000000003', now() - interval '26 days', 'P1', 'Error', 'OMK-API-HTTP-003', 'Omika.Api', NULL, NULL, NULL, NULL, NULL, '1.0.1.0', 'api', 'TIMEOUT', '[FAKE LOCAL] historique sans contexte 3', 'System.TimeoutException', E'[FAKE LOCAL]\nSystem.TimeoutException: legacy\n at Omika.Api.Tests.FakeService.Execute()', 2000, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), NULL, NULL, NULL), ('d0000000-0000-0000-0000-000000000004', now() - interval '30 days', 'P2', 'Warning', 'OMK-MOB-APP-002', 'Omika.App', NULL, NULL, NULL, 'fake-actor-002', 1, '1.1.0.0', 'iOS', NULL, '[FAKE LOCAL] historique sans contexte 4', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), NULL, NULL, NULL), ('d0000000-0000-0000-0000-000000000005', now() - interval '35 days', 'P3', 'Debug', 'OMK-MOB-DIAG-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-003', 1, '1.1.0.0', 'Desktop', NULL, '[FAKE LOCAL] historique sans contexte 5', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), NULL, NULL, NULL), ('d0000000-0000-0000-0000-000000000006', now() - interval '40 days', 'P1', 'Error', 'OMK-API-REQ-001', 'Omika.Api', NULL, NULL, NULL, NULL, NULL, '1.0.0.0', 'api', 'NULL_REF', '[FAKE LOCAL] historique sans contexte 6', 'System.NullReferenceException', E'[FAKE LOCAL]\nSystem.NullReferenceException: legacy\n at Omika.Api.Tests.FakeService.Execute()', NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics"}'::jsonb, now(), NULL, NULL, NULL); -- ===================================================================== -- 7. CAS T17-E — PARC (réattribution de licence, changement OS, changement version). -- Toutes les lignes : source='local-seed', message '[FAKE LOCAL]'. -- ===================================================================== -- 7.1 Réattribution de licence LIC-REASSIGNED (§58) : -- DEVICE-A (Samsung / Android 15) ancien → DEVICE-B (Apple / iOS 18) récent. -- MÊME LicenseNumber sur deux DeviceKey = 2 appareils DISTINCTS, jamais fusionnés. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment, license_number, device_key, device_key_version, device_manufacturer, device_model, operating_system, operating_system_version, app_build, screen_width, screen_height, logical_width, logical_height, display_scale) VALUES ('71000000-0000-0000-0000-000000000001', now() - interval '20 days', 'P2', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-001', 1, '1.4.0', 'Android', NULL, '[FAKE LOCAL] T17-E reassigned device A (old)', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t17e":"license-reassigned"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production', 'LIC-REASSIGNED', 'device-key-reassigned-a', 1, 'Samsung', 'SM-DEMO', 'Android', '15', '1400', 1080, 2400, 360, 800, 3.0), ('71000000-0000-0000-0000-000000000002', now() - interval '18 days', 'P2', 'Info', 'OMK-MOB-DIAG-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-001', 1, '1.4.0', 'Android', NULL, '[FAKE LOCAL] T17-E reassigned device A (old)', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t17e":"license-reassigned"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production', 'LIC-REASSIGNED', 'device-key-reassigned-a', 1, 'Samsung', 'SM-DEMO', 'Android', '15', '1400', 1080, 2400, 360, 800, 3.0), ('71000000-0000-0000-0000-000000000003', now() - interval '2 days', 'P2', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-002', 1, '1.4.1', 'iOS', NULL, '[FAKE LOCAL] T17-E reassigned device B (recent)', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t17e":"license-reassigned"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production', 'LIC-REASSIGNED', 'device-key-reassigned-b', 1, 'Apple', 'iPhoneDemo', 'iOS', '18', '1500', 1179, 2556, 393, 852, 3.0), ('71000000-0000-0000-0000-000000000004', now() - interval '1 day', 'P2', 'Info', 'OMK-MOB-DIAG-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-002', 1, '1.4.1', 'iOS', NULL, '[FAKE LOCAL] T17-E reassigned device B (recent)', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t17e":"license-reassigned"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production', 'LIC-REASSIGNED', 'device-key-reassigned-b', 1, 'Apple', 'iPhoneDemo', 'iOS', '18', '1500', 1179, 2556, 393, 852, 3.0); -- 7.2 Changement d'OS d'un même DeviceKey (§59) : DEVICE-C Android 15 → Android 16. -- Vue parc actuelle = Android 16 UNIQUEMENT (dernière observation). INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment, license_number, device_key, device_key_version, device_manufacturer, device_model, operating_system, operating_system_version, app_build, screen_width, screen_height, logical_width, logical_height, display_scale) VALUES ('72000000-0000-0000-0000-000000000001', now() - interval '12 days', 'P3', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-001', 1, '1.4.1', 'Android', NULL, '[FAKE LOCAL] T17-E os change device C (old Android 15)', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t17e":"os-change"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production', 'LIC-DEMO-A', 'device-key-os-change-c', 1, 'Google', 'Pixel Demo', 'Android', '15', '1410', 1080, 2400, 360, 800, 3.0), ('72000000-0000-0000-0000-000000000002', now() - interval '3 days', 'P3', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-001', 1, '1.4.1', 'Android', NULL, '[FAKE LOCAL] T17-E os change device C (recent Android 16)', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t17e":"os-change"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production', 'LIC-DEMO-A', 'device-key-os-change-c', 1, 'Google', 'Pixel Demo', 'Android', '16', '1410', 1080, 2400, 360, 800, 3.0); -- 7.3 Changement de version Omika d'un même DeviceKey (§60) : DEVICE-D 1.4.0 → 1.4.1. -- Vue parc actuelle = 1.4.1 UNIQUEMENT (dernière observation). INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment, license_number, device_key, device_key_version, device_manufacturer, device_model, operating_system, operating_system_version, app_build, screen_width, screen_height, logical_width, logical_height, display_scale) VALUES ('73000000-0000-0000-0000-000000000001', now() - interval '15 days', 'P3', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-002', 1, '1.4.0', 'Android', NULL, '[FAKE LOCAL] T17-E version change device D (old 1.4.0)', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t17e":"version-change"}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production', 'LIC-DEMO-B', 'device-key-version-change-d', 1, 'Samsung', 'SM-S928B-DEMO', 'Android', '15', '1400', 1080, 2400, 360, 800, 3.0), ('73000000-0000-0000-0000-000000000002', now() - interval '4 days', 'P3', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-002', 1, '1.4.1', 'Android', NULL, '[FAKE LOCAL] T17-E version change device D (recent 1.4.1)', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t17e":"version-change"}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production', 'LIC-DEMO-B', 'device-key-version-change-d', 1, 'Samsung', 'SM-S928B-DEMO', 'Android', '15', '1400', 1080, 2400, 360, 800, 3.0); -- 7.4 Variété multi-appareils (§57) : 4 DeviceKey supplémentaires (fabricants/modèles/OS -- variés) pour enrichir les répartitions du parc. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment, license_number, device_key, device_key_version, device_manufacturer, device_model, operating_system, operating_system_version, app_build, screen_width, screen_height, logical_width, logical_height, display_scale) VALUES ('74000000-0000-0000-0000-000000000001', now() - interval '6 days', 'P3', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-001', 1, '1.4.1', 'Android', NULL, '[FAKE LOCAL] T17-E fleet device E', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t17e":"fleet-variety"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production', 'LIC-DEMO-A', 'device-key-fleet-e', 1, 'Xiaomi', '23113RKC6G', 'Android', '16', '1400', 1080, 2400, 393, 873, 2.75), ('74000000-0000-0000-0000-000000000002', now() - interval '6 days', 'P3', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-001', 1, '1.4.1', 'Android', NULL, '[FAKE LOCAL] T17-E fleet device F', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t17e":"fleet-variety"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production', 'LIC-DEMO-A', 'device-key-fleet-f', 1, 'Samsung', 'SM-S928B', 'Android', '14', '1300', 1440, 3120, 411, 914, 3.5), ('74000000-0000-0000-0000-000000000003', now() - interval '8 days', 'P3', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-002', 1, '1.4.1', 'iOS', NULL, '[FAKE LOCAL] T17-E fleet device G', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t17e":"fleet-variety"}'::jsonb, now(), 'DEMO-CLIENT-B', 'API-B-01', 'Production', 'LIC-DEMO-B', 'device-key-fleet-g', 1, 'Apple', 'iPhone17,2', 'iOS', '18.7', '1500', 1179, 2556, 393, 852, 3.0), ('74000000-0000-0000-0000-000000000004', now() - interval '9 days', 'P3', 'Info', 'OMK-MOB-APP-001', 'Omika.App', NULL, NULL, NULL, 'fake-actor-003', 1, '1.4.0', 'iOS', NULL, '[FAKE LOCAL] T17-E fleet device H', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t17e":"fleet-variety"}'::jsonb, now(), 'DEMO-CLIENT-C', 'API-C-DEV-01', 'Development', 'LIC-DEMO-C', 'device-key-fleet-h', 1, 'Apple', 'iPhone16,2', 'iOS', '17.6', '1400', 1170, 2532, 390, 844, 3.0); -- ===================================================================== -- 8. CAS T18 — USAGE (ScreenView + FeatureUse). source='local-seed'. -- EventCode : OMK-MOB-UI-001 (ScreenView), OMK-MOB-UI-002 (FeatureUse). -- Unité = VUES/UTILISATIONS (événements), jamais la dernière observation. -- ===================================================================== -- 8.1 ScreenView multi-écrans multi-clients (§68/§69) : 180 vues sur 17 écrans, -- 6 DeviceKey, 3 clients, ~25 sessions. Distribution volontairement inégale -- (AGENDA dominant, SETTINGS rare) pour valider les classements. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment, license_number, device_key, device_key_version, device_manufacturer, device_model, operating_system, operating_system_version, app_build, screen_width, screen_height, logical_width, logical_height, display_scale, screen_code, feature_code) SELECT ('81000000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - (g % 60) * interval '6 hours', 'P4', 'Info', 'OMK-MOB-UI-001', 'Navigation', NULL, ('8a000000-0000-0000-0000-' || lpad((g % 25)::text, 12, '0'))::uuid, NULL, (ARRAY['fake-actor-001','fake-actor-002','fake-actor-003'])[1 + (g % 3)], 1, '1.4.1', (ARRAY['Android','Android','iOS'])[1 + (g % 3)], NULL, '[FAKE LOCAL] T18 ScreenView', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t18":"screen-view"}'::jsonb, now(), (ARRAY['DEMO-CLIENT-A','DEMO-CLIENT-B','DEMO-CLIENT-C'])[1 + (g % 3)], (ARRAY['API-A-01','API-B-01','API-C-DEV-01'])[1 + (g % 3)], (ARRAY['Production','Production','Development'])[1 + (g % 3)], (ARRAY['LIC-DEMO-A','LIC-DEMO-B','LIC-DEMO-C'])[1 + (g % 3)], (ARRAY['device-key-fleet-e','device-key-fleet-f','device-key-fleet-g','device-key-fleet-h','device-key-os-change-c','device-key-version-change-d'])[1 + (g % 6)], 1, (ARRAY['Xiaomi','Samsung','Apple','Apple','Google','Samsung'])[1 + (g % 6)], (ARRAY['23113RKC6G','SM-S928B','iPhone17,2','iPhone16,2','Pixel Demo','SM-S928B-DEMO'])[1 + (g % 6)], (ARRAY['Android','Android','iOS','iOS','Android','Android'])[1 + (g % 6)], (ARRAY['16','14','18.7','17.6','16','15'])[1 + (g % 6)], '1400', 1080, 2400, 393, 873, 2.75, (ARRAY['AGENDA','AGENDA','AGENDA','AGENDA','SITE_DETAIL','SITE_DETAIL','SITE_DETAIL', 'RENDEZVOUS_DETAIL','RENDEZVOUS_DETAIL','INTERVENTION_SEARCH','INTERVENTION_SEARCH', 'INTERVENTION_DETAIL','INTERVENTION_DETAIL','RAPPORT','EMAIL','TEAM','SETTINGS'])[1 + (g % 17)], NULL FROM generate_series(1, 180) AS g; -- 8.2 FeatureUse (§18/§53) : ~60 utilisations sur 3 fonctionnalités. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment, license_number, device_key, device_key_version, device_manufacturer, device_model, operating_system, operating_system_version, app_build, screen_width, screen_height, logical_width, logical_height, display_scale, screen_code, feature_code) SELECT ('82000000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid, now() - (g % 45) * interval '8 hours', 'P4', 'Info', 'OMK-MOB-UI-002', (ARRAY['InterventionsViewModel','RapportChampsViewModel','NouveauEmailViewModel'])[1 + (g % 3)], NULL, ('8a000000-0000-0000-0000-' || lpad((g % 25)::text, 12, '0'))::uuid, NULL, (ARRAY['fake-actor-001','fake-actor-002','fake-actor-003'])[1 + (g % 3)], 1, '1.4.1', (ARRAY['Android','Android','iOS'])[1 + (g % 3)], NULL, '[FAKE LOCAL] T18 FeatureUse', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t18":"feature-use"}'::jsonb, now(), (ARRAY['DEMO-CLIENT-A','DEMO-CLIENT-B','DEMO-CLIENT-C'])[1 + (g % 3)], (ARRAY['API-A-01','API-B-01','API-C-DEV-01'])[1 + (g % 3)], (ARRAY['Production','Production','Development'])[1 + (g % 3)], (ARRAY['LIC-DEMO-A','LIC-DEMO-B','LIC-DEMO-C'])[1 + (g % 3)], (ARRAY['device-key-fleet-e','device-key-fleet-f','device-key-fleet-g','device-key-fleet-h','device-key-os-change-c','device-key-version-change-d'])[1 + (g % 6)], 1, (ARRAY['Xiaomi','Samsung','Apple','Apple','Google','Samsung'])[1 + (g % 6)], (ARRAY['23113RKC6G','SM-S928B','iPhone17,2','iPhone16,2','Pixel Demo','SM-S928B-DEMO'])[1 + (g % 6)], (ARRAY['Android','Android','iOS','iOS','Android','Android'])[1 + (g % 6)], (ARRAY['16','14','18.7','17.6','16','15'])[1 + (g % 6)], '1400', 1080, 2400, 393, 873, 2.75, (ARRAY['INTERVENTION_SEARCH','RAPPORT','EMAIL'])[1 + (g % 3)], (ARRAY['INTERVENTION_SEARCH_RUN','REPORT_GENERATE','EMAIL_SEND'])[1 + (g % 3)] FROM generate_series(1, 60) AS g; -- 8.3 Licence réattribuée (§70) : LIC-REASSIGNED sur DEVICE-A (AGENDA, ancien) -- puis DEVICE-B (SITE_DETAIL, récent). Le filtre licence voit les DEUX usages ; -- aucun appareil n'est fusionné. INSERT INTO diagnostic_event (event_id, timestamp_utc, priority, level, event_code, component, trace_id, session_id, operation_id, actor_key, actor_key_version, app_version, platform, error_code, message, exception_type, stack_trace, duration_ms, source, metadata_json, ingested_at_utc, client_code, server_instance, environment, license_number, device_key, device_key_version, device_manufacturer, device_model, operating_system, operating_system_version, app_build, screen_width, screen_height, logical_width, logical_height, display_scale, screen_code, feature_code) VALUES ('83000000-0000-0000-0000-000000000001', now() - interval '20 days', 'P4', 'Info', 'OMK-MOB-UI-001', 'Navigation', NULL, '83000000-0000-0000-0000-0000000000a1', NULL, 'fake-actor-001', 1, '1.4.0', 'Android', NULL, '[FAKE LOCAL] T18 license reassigned device A (AGENDA old)', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t18":"license-reassigned"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production', 'LIC-REASSIGNED', 'device-key-reassigned-a', 1, 'Samsung', 'SM-DEMO', 'Android', '15', '1400', 1080, 2400, 360, 800, 3.0, 'AGENDA', NULL), ('83000000-0000-0000-0000-000000000002', now() - interval '1 day', 'P4', 'Info', 'OMK-MOB-UI-001', 'Navigation', NULL, '83000000-0000-0000-0000-0000000000b2', NULL, 'fake-actor-002', 1, '1.4.1', 'iOS', NULL, '[FAKE LOCAL] T18 license reassigned device B (SITE_DETAIL recent)', NULL, NULL, NULL, 'local-seed', '{"synthetic":true,"seed":"omika-local-diagnostics","t18":"license-reassigned"}'::jsonb, now(), 'DEMO-CLIENT-A', 'API-A-01', 'Production', 'LIC-REASSIGNED', 'device-key-reassigned-b', 1, 'Apple', 'iPhoneDemo', 'iOS', '18', '1500', 1179, 2556, 393, 852, 3.0, 'SITE_DETAIL', NULL); -- ===================================================================== -- FIN DU SEED. -- ===================================================================== -- VOLUME TOTAL (local-seed) : -- + 250 volume de fond (80+50+40+20+12+18+20+10) -- + 46 erreurs (10+6+6+8+8+5+3) -- + 10 traces (4+3+3) -- + 7 synchronisation (3+2+2) -- + 5 sessions (3+2) -- + 6 historiques NULL -- + 12 T17-E parc (4 réattribution + 2 changement OS + 2 changement version + 4 flotte) -- + 242 T18 usage (180 ScreenView + 60 FeatureUse + 2 licence réattribuée) -- = 578 événements. -- -- Requêtes de contrôle (à exécuter séparément, cf. rapport §46) : -- SELECT count(*) FROM diagnostic_event WHERE source = 'local-seed'; -- SELECT client_code, count(*) FROM diagnostic_event WHERE source = 'local-seed' GROUP BY client_code ORDER BY client_code NULLS LAST; -- SELECT server_instance, count(*) FROM diagnostic_event WHERE source = 'local-seed' GROUP BY server_instance ORDER BY server_instance NULLS LAST; -- SELECT environment, count(*) FROM diagnostic_event WHERE source = 'local-seed' GROUP BY environment ORDER BY environment NULLS LAST; -- SELECT count(*) FROM diagnostic_event WHERE source = 'local-seed' AND client_code IS NULL AND server_instance IS NULL AND environment IS NULL; -- SELECT operation_id, count(DISTINCT component) FROM diagnostic_event WHERE source = 'local-seed' AND operation_id IS NOT NULL GROUP BY operation_id HAVING count(DISTINCT component) > 1; -- SELECT event_code, error_code, exception_type, component, count(*) FROM diagnostic_event WHERE source = 'local-seed' AND lower(level) IN ('error','critical') GROUP BY 1,2,3,4 ORDER BY 5 DESC; -- SELECT min(timestamp_utc), max(timestamp_utc) FROM diagnostic_event WHERE source = 'local-seed'; -- =====================================================================