Add Scheduled Effective-Dated Employee Changes to an HR Backend

You are given an existing HR system: a web application with a backend API and a frontend. Its database stores each employee's current data and the employee's historical data. Add a feature that schedules a change to an employee so that it takes effect at a future date. For example, a promotion next month is scheduled now as a change to the employee's title. The feature touches both the frontend and the backend, but the focus is the backend: how scheduled changes are stored, created and cancelled, and how reads return the right values before and after the effective date. ```hint When does a change take effect Decide whether a scheduled change is applied by something that runs at the effective time or is resolved whenever the employee is read, and what each choice does when that something is late or down. ``` ```hint Changes touch fields Two changes for the same employee may be scheduled at different times and touch different fields. Make sure that taking one of them into account can never undo the other. ``` ### Constraints and Clarifications - Historical data already exists and must stay readable. - A new change must not overwrite fields set by an earlier change. - Concentrate on the backend design and implementation; describe the frontend only briefly. ### Clarifying Questions - How is the existing history stored: one row per changed field, or a full copy of the employee for each change? - Can a scheduled change be edited or cancelled before it takes effect, and can a change be backdated? - Is the effective time a date or a timestamp, and in which time zone? - Must other systems, such as payroll or a company directory, be told when a change takes effect? - If two changes to the same field take effect at the same time, which one wins? ### What a Strong Answer Covers - A data model for effective-dated, field-level changes that coexists with the existing history - Read semantics for the employee as of any time, including now, with a deterministic order for changes that take effect together - An API to create, list and cancel scheduled changes, with validation, authorization and idempotent creation - Why a worker that rewrites the current record at the due time is fragile, and how side effects that must happen at the effective time are still delivered - Read performance for list and search pages, and tests around the effective-time boundary ### Follow-up Questions - A title change is scheduled for next month, and the next day a department change is scheduled for next week. Walk through what reads return before next week, between the two dates, and after next month. - An admin cancels a scheduled promotion one day before it takes effect. What do you store? - The employee list page filters every employee by title. How do reads stay fast without becoming wrong when a change falls due? - Payroll must be notified on the effective date. How do you do that without making the employee data depend on the notification job?