Edit Note - Life OS

Title *

Domain *

Project

Content

15. IMPORTANT FOCUS AREAS (GEN2 MIGRATION):
Approval boundary and event trigger (status='r'):
Define where approval happens (UI/API/DB procedure) and who can approve.
Define the approval record/table and required fields (clinical_area_id, drug_id, country_code, status, approved_by, approved_at, version_id, reason).
Define allowed status transitions (W->T->R->Q) and pre-approval checks.
Define concurrency rules (one approval in-flight per clinical area).
Define event producer (CDC/trigger/API publish) and exact emit time (on commit).
Define event payload schema (approval_id, entity_id, entity_table, clinical_area_id, country_code, version_id, commit_scn, correlation_id, retries).
idempotency key and duplicate handling rules in every pipeline stage.
failure handling if event publish fails (retry vs reconciliation).
Define approval audit trail and retention policy.
Clinical-area versioning:
Define version type (timestamp, or SCN-based).
where the version is stored (per clinical area + per table).
how version is generated (transaction-scoped assignment).
how version is shared across multiple tables in one update.
how approval captures the version boundary.
history retention and version cleanup strategy.
Define query patterns to fetch data up to an approved version.
Define conflict rules when concurrent edits occur.
Define how versioning integrates with retries and re-approval.
Clinical-area dependency mapping (Data Trees / Property Graphs):
Define the root entity and root table for each clinical area.
Define dependency edges (FKs, reference joins, graph edges).
Define which reference tables are included vs excluded by default.
Define maximum depth and cycle handling strategy.
Define cross-country/shared reference handling rules.
Define snapshot format and storage (tree_nodes, snapshot_id, hash).
Define completeness validation (no missing required nodes).
Define delta scope (what changes cause a tree rebuild).
Define governance for graph updates when schemas change.
Event bus + consumer model:
Define event bus (OCI Streaming/Kafka) topics and retention.
Define partition key (clinical_area_id or country+area) for ordering.
Define ordering and replay guarantees per clinical area.
Define the consumer/dispatcher that invokes OCI Functions.
Define retry, backoff, and dead-letter strategy.
Define event schema versioning and compatibility policy.
Define security (IAM, TLS, least privilege, encryption).
Define monitoring for consumer lag, throughput, and drop rates.
SQL Server -> Oracle SP migration:
Inventory all SPs, triggers, dependencies, and input/output contracts.
Translate T-SQL to PL/SQL (temp tables, MERGE semantics, error handling).
Define transaction boundaries and isolation requirements.
Define performance targets and benchmarking approach.
Define exception mapping and standardized return codes.
Define test plan (unit, integration, data parity).
Define operational runbooks (timeouts, retries, rollback).
Validation depth:
Record count validation by clinical area + country + version.
Referential integrity validation across tree edges.
Graph/tree completeness validation (no missing required nodes).
Schema validation (type/format constraints).
Data quality rules (nullability, domain checks, ranges).
Duplicate detection and de-duplication rules.
Audit logging (what validated, by whom, at what version).
Failure classification and alerting thresholds.
Incremental updates after approval:
Define whether edits after approval require re-approval.
Define how to detect deltas after approval (version diff, CDC).
Define TTL regeneration strategy (full vs incremental).
Define SKG update semantics (replace vs patch).
Define artifact versioning and retention for TTL files.
Define conflict handling with in-flight pipeline executions.
Operational SLOs and Observability:
Define approval-to-publish latency targets.
Define throughput targets (approvals/hour, rows/sec).
Define retry limits and escalation policy.
Define timeouts per stage and circuit-breaker rules.
Define alert thresholds (error rate, lag, DLQ depth).
Define metrics and dashboards (per clinical area, per country).
Define incident response and runbook ownership.
Gen1 Country/Version model vs Gen2 Data Tree model:
Define how COUNTRY filters map to clinical area trees.
Define how VERSION maps to clinical-area versioning.
Define multi-country shared-tree behavior (shared refs).
Define migration path for existing country/version workflows.
Define backward compatibility period and cutover plan.
TTL generation + SKG publishing:
Define TTL schema mapping and ontology alignment.
Define file naming, partitioning, and storage location.
Define incremental TTL updates and merge strategy.
Define SKG load mechanism (API, bulk load, streaming).
Define load verification and rollback plan.
Define lineage tracking from approval -> TTL -> SKG. (edited)