Technical: You are responsible for replacing a complex Excel-based tracker that contains project progress, material status, and handoffs. What data model and integration approach would you design to ensure data integrity, support automation (notifications, status updates), enable auditability/versioning, and allow integrations with other systems (e.g., procurement, scheduling)? Describe trade-offs and key implementation considerations.

I’d model this as a canonical relational schema: Projects, Tasks/Handoffs, Materials, Suppliers, Users, and StatusHistory. Materials link to Projects and Suppliers; StatusHistory captures every state change with timestamp, user, reason, and source system. For integrity I’d enforce foreign keys, unique constraints, and domain enums, plus validation at API layer. Integration uses event-driven APIs: upstream systems post events to a message bus (Kafka or Pub/Sub), we apply idempotent consumers and write to the canonical DB. For auditability I’d keep an append-only event log and snapshots for quick queries (event sourcing hybrid). Notifications run from a rules engine reacting to events (thresholds, ETA changes) with retry logic. Trade-offs: event-driven adds complexity but enables near real-time updates and better scalability; a simpler ETL is faster to deliver but risks stale data. Key considerations: data cleanup/mapping tools, reconciliation jobs, RBAC, SLA for integrations, and retained history (e.g., 7+ years) to meet audit needs.