Query Console
chronos_prod=> four governed analytical viewsDaily hours — filtered range
GROUP BY date_workedNeeds your signature
Time entry
Post individual line items or fill the weekly card. Every row lands in timesheets with approved = FALSE and enters the manager approval queue — nothing reaches payroll or project budgets until it is signed.
Single line item
ts_id ← nextval('timesheets_seq')Weekly time card
Approval workflow
Manager queue grouped by employee × ISO week. Approving sets ts.approved = TRUE and stamps the approver — that single boolean is what feeds Query 2 (budget burn) and Query 4 (department cost), and releases hours into the payroll register.
Analytics — four governed queries
Each panel is the materialised result of one query below, re-executed against the in-memory relations whenever a filter changes. Hover any bar or arc for the underlying grouped row.
Weekly Timesheet Summary
GROUP BY e.employee_id, p.project_idProject Budget Tracking
WHERE ts.approved = TRUEEmployee Utilisation
rolling 30 days · LEFT JOINDepartment Cost
rolling 90 days · COUNT(DISTINCT)Q1 grouped result set
full row setQ3 result set
incl. zero-hour employeesPayroll register
Computed from approved hours only. Overtime policy: daily hours above 8 → 1.5×, above 12 → 2.0×, weekly hours above 40 → 1.5×. Employer burden = FICA 7.65% + SUTA 2.4% + FUTA 0.6% + department workers-compensation rate.
Schema & query planner
Three normalised relations. timesheets is the fact table; every analytical query scans it through a composite btree index on (date_worked, employee_id, project_id) so the BETWEEN predicates stay sargable.
Indexes in use
pg_stat_user_indexesPlanner notes & optimisations
EXPLAIN (ANALYZE, BUFFERS)- LEFT JOIN + WHERE null-rejection. Query 3 as specified puts ts.date_worked >= … in the WHERE clause, which silently degrades the LEFT JOIN into an INNER JOIN and drops employees with no recent hours. The console keeps the LEFT JOIN and moves the predicate into the ON clause, then guards the division with NULLIF — so zero-hour staff still appear at 0%.
- Division type. SUM(hours)/40 is integer-safe only with a float column; hours are stored numeric(5,2) and the utilisation expression casts explicitly to avoid truncation.
- Single-pass department rollup. Query 4 groups directly on the joined e.department key rather than a correlated subquery per department — one hash aggregate over the filtered scan instead of N scans.
- Covering index. idx_ts_range_cover (date_worked, employee_id, project_id) INCLUDE (hours_logged, approved) lets Q1/Q2 satisfy the projection from the index alone — index-only scan, no heap fetches.
- Boolean selectivity. approved is ~88% true, so it is a poor leading index key; it lives in the INCLUDE list and is applied as a filter, removing ~12% of rows after the range scan.
- Determinism. All four queries carry an explicit ORDER BY so paginated exports never re-order between runs.