Two independent classification axes on Employee (employment type, military status), with the business rules that govern them.
Table commission_order. Named Order here per the requirement's wording.
Each Commission Order has at most one Commissioned Employee via sold by — commission_order.employee_id. Range CommissionedEmployee: only commissioned employees may have orders.
| Field | Kind of value | Notes |
|---|---|---|
| Amount | amount | |
| Customer name | text |
Each Department has many Employee via has employee
| Field | Kind of value | Notes |
|---|---|---|
| Budget | amount | |
| Name | text | |
| Total salary | amount | computed by rule “Department.total_salary = sum(Employee.salary)” |
Each Employee has at most one System Configuration via governed by — employee.sys_config_id. Source of copied thresholds/rates.
Each Employee has at most one Department via works in — employee.dept_id (required).
| Field | Kind of value | Notes |
|---|---|---|
| Employee type | one of: salaried, hourly, commissioned | Column employee.type. (JSON:API wire name is 'Type'.) |
| Max hourly weekly salary | amount | computed by rule “Employee.max_hourly_weekly_salary copied from SysConfig” |
| Military | one of: yes, no | Column employee.military. |
| Military stipend | number | computed by rule “military_stipend = service_years * rate (military only, else 0)” 0 for non-military employees. |
| Military stipend rate per year | number | computed by rule “Employee.military_stipend_rate_per_year copied from SysConfig” |
| Name | text | |
| Salary | amount | computed by rule “Employee.salary, by employment type” Derived for hourly and commissioned; entered directly for salaried. |
| Total compensation | amount | computed by rule “total_compensation = salary + military_stipend” |
Each Labor Union has many Hourly Employee via has member
| Field | Kind of value | Notes |
|---|---|---|
| Dues rate | number | |
| Name | text |
Singleton of global, runtime-configurable settings (rates and thresholds).
| Field | Kind of value | Notes |
|---|---|---|
| Max hourly weekly salary config | amount | sys_config.max_hourly_weekly_salary (source of the copy). |
| Military stipend rate per year | number | computed by rule “Employee.military_stipend_rate_per_year copied from SysConfig” |
Employees are classified two independent ways. Every employee is one kind on the first axis, and may also be military.
Exactly one of these:
Commission Order records can belong only to this kind of employee.
| Field | Kind of value | Notes |
|---|---|---|
| Base salary | amount | |
| Commission total | amount | computed by rule “Commissioned commission_total = sum(Order.amount)” |
May have a Labor Union. No other kind of employee can.
| Field | Kind of value | Notes |
|---|---|---|
| Hourly rate | number | |
| Hours worked | number | |
| Union dues | amount | computed by rule “Hourly union_dues = hours_worked * union.dues_rate” |
No additional fields. Salary is entered directly.
Independent of the other classification:
| Field | Kind of value | Notes |
|---|---|---|
| Branch | one of: Army, Navy, Air Force, Marines, Coast Guard | |
| Rank | text | |
| Service years | whole number |
Each rule is enforced automatically on every change, and traces to the requirement that asked for it. added by AI marks rules nobody asked for in the requirements, so a person should confirm them.
| Rule | Type | Requirement |
|---|---|---|
| Department total_salary must not exceed budget | Check | Department.total_salary must not exceed Department.budget. If violated: Department total_salary ([total_salary]) exceeds budget ([budget]) |
| Department.total_salary = sum(Employee.salary) | Total | Department.total_salary = sum of Employee.salary (all types). |
| Rule | Type | Requirement |
|---|---|---|
| Commissioned commission_total = sum(Order.amount) | Total | Commissioned employees: commission_total = sum of Order.amount. |
| Employee.max_hourly_weekly_salary copied from SysConfig | Copied value | Hourly employees: salary must not exceed 5000 per week. (threshold lives in SysConfig, not a literal) |
| Hourly salary must not exceed weekly cap | Check | Hourly employees: salary must not exceed 5000 per week. If violated: Hourly salary ([salary]) exceeds weekly cap ([max_hourly_weekly_salary]) |
| base_salary null unless commissioned added by AI | Check | (inferred from single-table inheritance) If violated: base_salary must be null for non-commissioned employees |
| hours_worked null unless hourly added by AI | Check | (inferred from single-table inheritance: subtype columns are null for other types) If violated: hours_worked must be null for non-hourly employees |
| hourly_rate null unless hourly added by AI | Check | (inferred from single-table inheritance) If violated: hourly_rate must be null for non-hourly employees |
| Order amount > 0 added by AI | Check | (not stated; domain-standard validation added by the AI, see ad-libs.md) If violated: Order amount ([amount]) must be greater than 0 |
| Orders only for commissioned employees | Check | Commissioned employees are employees who can have Orders; only Commissioned employees may have Orders. If violated: Orders can only be assigned to commissioned employees |
| Employee.salary, by employment type | Calculation | Hourly employees: salary = hours_worked * hourly_rate. Commissioned employees: salary = base_salary + commission_total. |
| Hourly union_dues = hours_worked * union.dues_rate | Calculation | Hourly employees: union_dues = hours_worked * union.dues_rate (0 if no union assigned). |
| Only hourly employees may have a union | Check | Hourly employees may be assigned to a Union (optional); only Hourly employees may have a union_id. If violated: union_id must be null for non-hourly employees |
| Rule | Type | Requirement |
|---|---|---|
| Employee.military_stipend_rate_per_year copied from SysConfig | Copied value | Military employees receive a military_stipend (REAL) = service_years * 100. (the 100 lives in SysConfig) |
| military_stipend = service_years * rate (military only, else 0) | Calculation | Military employees receive a military_stipend (REAL) = service_years * 100. |
| branch null unless military added by AI | Check | (inferred from the military classification axis) If violated: branch must be null for non-military employees |
| rank null unless military added by AI | Check | (inferred from the military classification axis) If violated: rank must be null for non-military employees |
| service_years null unless military added by AI | Check | (inferred from the military classification axis) If violated: service_years must be null for non-military employees |
| total_compensation = salary + military_stipend | Calculation | Total compensation for military employees = salary + military_stipend. Total compensation for non-military employees = salary. |
| branch must be a recognized service branch | Check | Military employees have a branch (TEXT: 'Army', 'Navy', 'Air Force', 'Marines', 'Coast Guard') If violated: branch ([branch]) is not a recognized military branch |