Skip to content

2026-05-06 Archived Timelog Backfill

This feature adds historical timelogs for archived Zoho Projects into the Project Timesheets Power BI model without adding daily Zoho load.

What Changed

  • Live timelogs stay on /delivery-model-timelogs-report and cover current portal-visible data from January 2025 onward.
  • Archived timelogs use /delivery-model-archived-timelogs-backfill-report, which discovers Delivery Model projects, keeps only archived projects, and fetches logs through Zoho Projects REST APIs.
  • Archived logs are fetched in six-month windows, paginated, mapped to the same CSV schema as live timelogs, and cached as a generated CSV.
  • The Power BI timelogs query combines archived and current rows. Calendar and dimensions were widened so archived 2024 rows contribute to completion, project, task, tasklist, and user visuals.
  • User filtering is modeled as users[user_zuid] -> timelogs[owner_zpuid]. The final timelogs query normalizes mixed owner IDs to ZUID where possible; the final users query appends fallback users from timelogs when needed.

Endpoints

All endpoints below use Basic auth with EXCEL_BASIC_USER and EXCEL_BASIC_PASS.

GET /delivery-model-archived-timelogs-backfill-report

Returns the archived backfill CSV when cached. If the cache is missing, it starts background generation and returns 503 with Retry-After: 60.

Query parameters:

  • start_date: optional, defaults to 2024-01-01
  • end_date: optional, defaults to the last day of the previous month
  • project_ids: optional troubleshooting filter; bypasses the auto cache and should not be used by Power BI

POST /delivery-model-archived-timelogs-backfill-cache-reset

Clears the archived backfill cache for the requested/default date range and immediately starts regeneration. Returns 202 with Retry-After: 60.

If a fresh generation lock exists, it returns 409 and leaves the existing job alone. Locks older than 30 minutes are treated as stale.

Manual Commands

Start or poll the cached archived backfill:

curl -i -u "$EXCEL_BASIC_USER:$EXCEL_BASIC_PASS" \
  "https://pepit.fxbn.dev/delivery-model-archived-timelogs-backfill-report"

Force cache reset and regeneration:

curl -i -X POST -u "$EXCEL_BASIC_USER:$EXCEL_BASIC_PASS" \
  "https://pepit.fxbn.dev/delivery-model-archived-timelogs-backfill-cache-reset"

Reset a specific historical range:

curl -i -X POST -u "$EXCEL_BASIC_USER:$EXCEL_BASIC_PASS" \
  "https://pepit.fxbn.dev/delivery-model-archived-timelogs-backfill-cache-reset?start_date=2024-01-01&end_date=2026-04-30"

Troubleshoot one archived project without using the cache:

curl -i -u "$EXCEL_BASIC_USER:$EXCEL_BASIC_PASS" \
  "https://pepit.fxbn.dev/delivery-model-archived-timelogs-backfill-report?project_ids=16099000001684333"

Power BI Notes

  • timelogs_archived_backfill points at the archived backfill report endpoint.
  • timelogs_current points at the live timelog report endpoint.
  • Final timelogs combines both tables and is the fact table used downstream.
  • After a successful archived import, disable scheduled refresh for timelogs_archived_backfill; refresh it manually only after historical edits.
  • To pick up historical edits, call the cache reset endpoint, wait until the report endpoint returns CSV, then refresh Power BI.

Cache Notes

The default cache directory is:

/tmp/pepperback-archived-timelogs-backfill

Without a Docker volume for that path, recreating the container clears the cache. The reset endpoint is now the preferred way to regenerate it deliberately.