# We Built a Timesheet Auto-Filler by Removing Every Layer We Started With

By João Raul Furtado Lucas (https://joaoraul.dev/) · 2026-06-18 · 8 min read. First published on Singular Articles (Medium): https://medium.com/singular-articles/we-built-a-timesheet-auto-filler-by-removing-every-layer-we-started-with-d3f223cfe471

Canonical: https://joaoraul.dev/writing/timesheet-auto-filler/

The interesting part wasn’t adding an LLM. It was throwing out the MCP layer, dropping the time-tracking integration, and letting Git commits and the spreadsheet itself do most of the work.

This project started as a side effect of another one. I’d automated my brag document using MCP servers, and during a follow-up a teammate floated a better idea: automate the monthly timesheet. My first instinct was to reach for MCPs again. My second, after feeling the overhead, was to rip them out and rebuild on plain REST APIs. The result is a web app that pulls your Jira tickets and Git commits, uses Gemini to map the ambiguous parts onto working days, and writes descriptions and hours straight into a Google Sheet. The throughline of the engineering is subtraction. Nearly every reliability win came from doing less, not more. This post walks you through the full pipeline and the two layers we deliberately deleted along the way.

A timesheet is a deceptively hard reconstruction problem. The data you need exists, but it’s scattered and recorded at the wrong granularity:

- **Jira** knows a ticket moved to `Ready To Test` on a Tuesday, but not which of the preceding ten working days you actually spent on it.
- **Git** knows you pushed commits referencing `TT-2281` on a Monday and a Thursday, but says nothing about the gap day between them.
- **The sheet** itself may already have manual entries you must not clobber.

So the task is this: given roughly 20 working days, a pile of tickets with only completion dates, and a list of commits, produce a plausible distribution of 2 to 3 tickets per day that a human would have written. There’s no single correct answer, but there are plenty of wrong ones. Assign a ticket to a day before its first commit, invent a ticket key, or overwrite a column the user hand-edited, and the tool is worse than doing it by hand.

This is exactly the kind of fuzzy mapping an LLM is good at. It’s also exactly the kind of problem where an LLM will confidently hallucinate, and, as it turned out, exactly the kind of problem where the surrounding plumbing matters more than the model.

## Why naive solutions failed

The honest version of this project’s history is a sequence of things we removed.

### First attempt: delegate everything to MCPs

Coming off the brag-document automation, MCP servers were the hammer and everything looked like a nail. The plan was to let an agent orchestrate Jira, Git, and Sheets entirely through MCP tools. It worked in the demo sense, but the overhead was real. Delegating every list-tickets and read-cell call through an MCP round-trip added latency and indirection for operations that are, fundamentally, a couple of HTTP requests. We were paying an abstraction tax for flexibility we didn’t need.

### The pivot: just call the REST APIs

So I rebuilt it on the raw REST APIs (Jira’s JQL search and changelog endpoints, the Git provider APIs, the Google Sheets and Drive APIs) and orchestrated them in plain TypeScript. It was more code in one sense and far less system in another: no agent loop, no tool-routing layer, predictable control flow. It worked, and it was a genuinely good learning exercise in how these APIs actually behave under the hood.

### Dropping the time-tracking integration

The second thing we cut was Tiquetaque, a time-tracking tool we’d considered wiring in to source the daily hours. Same lesson: it was overhead that would have complicated the system for a number we could already derive. The monthly sheet already contains an “expected hours” total in its header. So instead of integrating another system, we read that cell and infer the daily split (full-time becomes 8h/day, half-time becomes 4h/day). One fewer integration, one fewer set of credentials, one fewer thing to break.

### The assumption we unlearned

We went in believing the smart layer, first MCPs and then the LLM, was the engine. It wasn’t. Git commits are not a hint about when work happened. They’re an authoritative record. The reframing that fixed everything was simple: **use deterministic code for anything you can prove, and reserve the model for genuine ambiguity.**

## Architecture and implementation

The system is a TypeScript monorepo: an Express 5 backend (ESM, file-backed sessions, no database) and a React 18 + Vite frontend. The pipeline is one orchestrator, `generateTimesheet`, that runs through nine stages and streams progress to the browser over Server-Sent Events:

```
Jira tickets → Git commits → read sheet → pre-assign from commits
   → LLM maps the remainder → merge → (optional) summarize
   → write descriptions → write hours → read back & confirm
```

The decision that came out of the pivot lives in stage 4. Before we ever call Gemini, we partition the work in code:

```
// generate.ts: pre-assignment happens before the LLM
const evidence = buildCommitEvidence(commits);
const { preAssigned, ticketsWithCommitEvidence } =
  preAssignFromEvidence(evidence, allWorkingDays);
const occupiedDates = findOccupiedDates(sheetData, allWorkingDays);
const workingDaysForGemini = allWorkingDays.filter(d => !occupiedDates.has(d));
const { completed, active } = partitionTicketsForLLM(tickets, ticketsWithCommitEvidence);
```

Any ticket with commit evidence is assigned to its exact commit dates in code and **excluded from the LLM call entirely**. Days the user already filled by hand are removed from the candidate set. Only the genuinely ambiguous remainder is handed to Gemini.

### A small abstraction worth keeping: pluggable code sources

Not every layer deserved deletion. We support GitHub, Bitbucket, and Azure DevOps, each with different auth (OAuth, app password, per-session PAT) and commit shapes. Rather than branch on provider throughout the orchestrator, each source implements one interface and normalizes to a common `GitCommit`:

```
export interface CodeSourceProvider<Session extends CodeSourceSession = CodeSourceSession> {
  kind: CodeSourceKind; // 'github' | 'azuredevops' | 'bitbucket'
  listRepos(session: Session): Promise<CodeSourceRepo[]>;
  fetchCommits(session: Session, query: FetchCommitsQuery): Promise<GitCommit[]>;
}
```

The orchestrator never knows which provider it’s using. It just calls `provider.fetchCommits(...)`. This abstraction earns its place because it absorbs real, irreducible variation. The MCP layer didn’t. It abstracted over things that were already simple.

## Code

The most load-bearing code is how we treat the model’s output: never trust it, always validate it. Gemini gets a heavily constrained prompt with explicit “VALID TICKET KEYS” and “VALID WORKING DAYS” lists, `temperature: 0`, and JSON response mode. But the prompt is a request, not a guarantee, so every response is scrubbed:

```
// gemini-client.ts: strip hallucinations, enforce hard constraints
const validKeys = new Set([...completedTickets, ...activeTickets].map(t => t.key));
const validDays = new Set(input.workingDays);

const cleaned: DateTicketMap = {};
for (const [date, tickets] of Object.entries(raw)) {
  if (!validDays.has(date)) continue;                        // drop invented dates
  const realTickets = tickets.filter(k => validKeys.has(k)); // drop invented keys
  cleaned[date] = realTickets.slice(0, TIMESHEET.maxTicketsPerDay); // enforce cap
}
// Guarantee every working day exists; fill blanks with filler for developers
for (const day of input.workingDays) {
  if (!cleaned[day]) cleaned[day] = [];
  if (cleaned[day].length === 0 && input.isDeveloper) cleaned[day] = [TIMESHEET.filler];
}
```

Three guarantees fall out of this loop regardless of what the model returns: no out-of-range date survives, no unknown ticket key survives, and no day exceeds the cap. The prompt asks nicely. The code enforces.

The hours column is the payoff from dropping Tiquetaque. Sheets vary between developers, so rather than hardcode a column index we scan the header rows for the expected-total cell (`Expected`, `Esperado`, `Previsto`) and an hours column (`H`, `Hours`, `Horas`), infer full-time versus half-time from that total, and refuse to touch the date or description columns. If detection fails, we skip hours rather than write garbage. It’s best-effort by design.

## Performance and metrics

There’s no CI or benchmark suite yet, so here’s an honest qualitative comparison rather than invented numbers:

| Aspect | First version (delegate everything) | Current version (REST + commits as ground truth) |
|---|---|---|
| Orchestration | MCP tool-routing per call | Direct REST in plain TypeScript |
| Hours source | Considered a 3rd-party integration | Inferred from the sheet’s own expected-hours cell |
| Tickets sent to the LLM | All of them | Only those without commit evidence, often far fewer |
| Commit-dated work | Guessed | Assigned deterministically, correct by construction |
| Invented dates/keys | Could reach the sheet | Stripped before every write |

The qualitative win is that the most verifiable work, anything backed by a commit, is now correct by construction rather than probably correct, and we got there by removing layers, not adding them.

## Production and operational considerations

The app runs as a split deploy: the Express backend on Render (Docker, multi-stage `node:22-slim`) and the static React frontend on Vercel. That split made cross-origin auth the trickiest operational detail. A cookie set by the Render backend has to survive a request from the Vercel origin:

```
app.set('trust proxy', 1); // behind Render's proxy: secure cookies + x-forwarded-proto
app.use(cors({ origin: process.env.FRONTEND_URL, credentials: true }));
app.use(session({
  // ...
  cookie: {
    secure: isProduction,
    sameSite: isProduction ? 'none' : 'lax', // cross-site cookie needs 'none', which demands secure
    maxAge: 24 * 60 * 60 * 1000,
  },
}));
```

`sameSite: 'none'` is mandatory for a cross-origin cookie, and browsers only accept it with `secure: true`, so the two are coupled. CORS with `credentials: true` can’t use a wildcard origin, so `FRONTEND_URL` must be concrete. We make these non-negotiable by failing loud at boot if `SESSION_SECRET` or `FRONTEND_URL` is missing in production, rather than silently falling back to a forge-able default.

Progress streams to the UI as SSE. The frontend parser is deliberately defensive. Chunks arrive on arbitrary byte boundaries, so it buffers and only emits on complete `data:` lines:

```
buffer += decoder.decode(value, { stream: true });
let nl: number;
while ((nl = buffer.indexOf('\n')) !== -1) {
  const line = buffer.slice(0, nl).replace(/\r$/, '');
  buffer = buffer.slice(nl + 1);
  if (line.startsWith('data: ')) yield JSON.parse(line.slice(6));
}
```

## Trade-offs and lessons learned

The surprise was that the system got more reliable every time I removed something. Dropping MCPs removed an indirection layer. Dropping Tiquetaque removed an integration. Shrinking the LLM’s input removed a class of hallucinations. Subtraction was the feature.

On building with AI, most of this was written with heavy AI assistance, and the lesson there is quieter than the hype.

> You really can ship a real, useful internal tool this way, provided you keep making the architectural calls yourself.

The “delegate everything to MCPs” dead end is the cautionary half of that. AI is great at producing the code, but deciding what layers shouldn’t exist is still the engineer’s job.

The technical debt we took on knowingly is that the sheet is mutated before the run reports completion. If the SSE connection drops mid-write, the user loses the final `complete` payload even though their sheet is already updated. The fix is a `runId` plus a recovery endpoint the client can poll on reconnect. We deferred it because the failure is recoverable (refresh the sheet) and rare.

What we’d change starting over: business rules like the `TT` project, the `Ready To Test` status, and the Portuguese filler string are hardcoded as if there’s one company-wide convention. They’re really per-team, and would need to become configuration before this generalizes.

## References and further reading

- [MDN: Using Server-Sent Events](https://developer.mozilla.org/en-US/docs/Web/API/Server-sent_events/Using_server-sent_events)
- [MDN: SameSite cookies](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Set-Cookie/SameSite)
- [Express: behind proxies (`trust proxy`)](https://expressjs.com/en/guide/behind-proxies.html)
- [Google Gemini API: generateContent](https://ai.google.dev/api/generate-content)