Time tracker SaaS, end-to-end

I'm building a time tracker SaaS for [freelance lawyers / dev
consultants / design agencies]. Postgres + Supabase.

Tables:
- workspaces (id, name, plan, default_billable_rate, billing_currency)
- users (id, workspace_id, email, name, role [admin, member])
- clients (id, workspace_id, name, hourly_rate (override), archived)
- projects (id, workspace_id, client_id, name, color, hourly_rate
  (override), billable boolean (default true), archived)
- tags (id, workspace_id, name, color)
- time_entries (id, user_id, workspace_id (denormalized for fast
  filter), project_id, description, started_at, ended_at nullable,
  duration_seconds (computed when ended_at is set), billable boolean,
  hourly_rate_snapshot numeric, tag_ids uuid[], created_at,
  updated_at)
- entry_locks (workspace_id, week_start_date, locked_by_user_id,
  locked_at) -- after a week is approved/invoiced, no edits

Required:
- Exactly ONE running timer per user at a time. A "running" timer is
  a time_entries row where ended_at IS NULL. Enforce with a UNIQUE
  partial index on user_id WHERE ended_at IS NULL.
- A check constraint that ended_at > started_at when ended_at is set
- duration_seconds maintained by a BEFORE INSERT/UPDATE trigger as
  EXTRACT(EPOCH FROM ended_at - started_at)::int when ended_at is set
- hourly_rate_snapshot captured at entry-create from the most
  specific rate (project > client > workspace default), so
  changing rates later doesn't retroactively rebill old work
- A locked entry (its week is in entry_locks) cannot be UPDATEd or
  DELETEd — enforce with a trigger

Indexes:
- (user_id, started_at DESC) for the user's recent entries (homepage)
- (workspace_id, started_at) BRIN for big reporting queries
- (project_id, started_at) for project rollups
- partial index on (user_id) WHERE ended_at IS NULL for "find my
  running timer" (very fast)

RLS: workspace members see only their workspace. Admins see all
entries in workspace; members only their own.

Output as one runnable SQL file. Comment why hourly_rate_snapshot
is captured at entry time (legal/audit reason: the bill the client
sees must match the rate that was active when the work was done).

Design a finite state machine for the timer in a time tracker SaaS,
implemented as a React custom hook + Postgres functions.

States:
- idle: no running entry
- running: an entry has started_at set, ended_at IS NULL
- editing_running: user is editing project/description/tags of a
  running entry (changes save optimistically; entry stays running)
- syncing: a network operation is in flight (visual indicator only)

Transitions and the SQL/RPC behind each:

1. start({ project_id, description }) from idle → running
   - RPC start_timer(user_id, project_id, description, started_at):
     - Atomic check: any open entry for this user? If yes, raise
       'concurrent_timer' (the unique partial index would also
       reject, this is just for a clean error)
     - INSERT a time_entries row with ended_at=NULL,
       hourly_rate_snapshot from project/client/workspace cascade
     - Return the new entry

2. stop() from running → idle
   - RPC stop_timer(user_id, ended_at):
     - UPDATE the open entry SET ended_at=$1
     - Trigger fills duration_seconds
     - Reject if no open entry exists

3. edit_running({ project_id?, description?, tag_ids? }) from running
   → running (just an update; ended_at stays NULL)
   - If project_id changes, snapshot the new project's rate too
     (audit-friendly: store the change in entry_history)

4. discard() from running → idle
   - DELETE the open entry (only allowed within 60 seconds of start;
     after that, force user to stop + edit instead)

5. retroactive_create({ project_id, started_at, ended_at,
   description }) from idle → idle
   - INSERT a complete entry. Validate:
     - ended_at > started_at
     - Does not overlap with any existing entry for this user
       (raise 'overlap' error pointing to the conflict so the UI
       can offer "merge" or "shift")

6. gap_fill_suggestion(user_id, day): a query that returns gaps in
   the user's day >15 min where they have no entry but their
   working hours suggest they were working. The UI shows these as
   ghost suggestions the user can accept.

Output: the React hook (useTimer) with the state, the Postgres
functions, and the React Query mutation wiring so optimistic
updates work cleanly.

Build a weekly timesheet view at /timesheet/[week-start-iso].

Layout:
- Top: a week-picker pill row (prev / current week label / next),
  total billable hours + total non-billable hours for the week
- Body: a grid with one row per project the user logged time on this
  week, columns: Project | Mon | Tue | Wed | Thu | Fri | Sat | Sun |
  Total
- Each cell shows the sum of hours that day on that project (e.g.,
  "3.25h"). Clicking a cell expands an inline editor showing the
  individual time_entries on that day in that project, each with
  start time, duration, description, billable toggle — all
  inline-editable.
- A "+ row" button at the bottom adds a new project to the week (lets
  you pre-fill a row for a project before logging entries to it)
- A "Submit week" button at the top right that calls a "lock_week"
  RPC (inserts an entry_locks row, makes all entries for that week
  immutable). Re-clicking opens an "Unlock" confirmation only for
  admins.

Requirements:
- Server-rendered with the user's entries for the week prefetched
  via React Query
- All edits are optimistic (immediate UI update, then server confirm,
  rollback + toast on error)
- Keyboard navigation: arrow keys move between cells, Enter expands,
  Esc collapses, Tab cycles within an expanded cell's fields
- Locked week shows a padlock icon and disables all edits
- Empty state if no entries: a "Get started" hint pointing to the
  global timer button

Output the page.tsx, the cell components, the React Query keys, and
the lock_week RPC.

I need a /reports page with three pre-built reports plus a custom
report builder.

Pre-built reports:

1. "Hours by client this week" — a bar chart per client, with
   billable + non-billable stacked, plus a table beneath showing
   client, hours billable, hours non-billable, total revenue
   (sum of duration_seconds * hourly_rate_snapshot / 3600 where
   billable=true).

2. "Hours by project, this month" — same shape, projects on
   the y-axis. Allow grouping by client → project (drill-down).

3. "User hours, this week" (admin only) — for each member,
   their billable hours, non-billable hours, total revenue, and a
   "utilization rate" (billable / capacity, where capacity is
   workspace.weekly_hours_capacity, default 40).

Each report shares filters: date range, client(s), project(s),
billable flag, tag(s). Filters are URL params (so reports are
shareable / bookmarkable).

For each report, give me the Postgres query (parameterized, no
string interpolation) and the indexes that make it fast on a
workspace with 500k time_entries rows. Use materialized views ONLY
if the data is provably stale-tolerant (workspace_daily_summary
refreshed every 15 min is acceptable for the user-hours report).

Custom report builder:
- A small JSON DSL: { date_range, group_by [client, project, user,
  tag, day, week, month], filter [...], metric [hours, revenue,
  count] }
- Compiles to one SQL query
- Saved reports: insert a saved_reports row per workspace; show
  them in a sidebar

Output the queries, the indexes, the materialized view DDL (if used),
and the DSL compiler.

Build a "Generate invoice" flow that converts a set of unbilled
time entries into either an internal invoice (if I'm bundling
invoicing) or a payload for a third-party invoicing API
(QuickBooks, FreshBooks).

Inputs to the flow:
- workspace_id, client_id
- A date range or a specific list of time_entry_ids
- Optional: invoice_template_id (line item grouping rule)

Steps:

1. Validate: every selected entry has billable=true, has
   ended_at IS NOT NULL, has hourly_rate_snapshot > 0, and is not
   already on an existing invoice (link table: invoice_lines pointing
   to time_entry_id).

2. Group entries into invoice line items per the template:
   - "Per project per week" (default): one line per project per
     ISO week, description = project name + week range, qty =
     hours total, unit price = effective rate (if all entries in
     the group share the same hourly_rate_snapshot; if not, split
     into multiple lines)
   - "Per entry" (verbose): one line per time_entries row
   - "Per project flat" (project total only)

3. Compute totals: subtotal, tax (per workspace tax setup), total.

4. For internal invoices: insert invoices + invoice_lines rows,
   mark each time_entry as invoiced (link in invoice_lines), return
   the invoice ID.

5. For external invoicing: build the appropriate API payload
   (QuickBooks Items API, FreshBooks Invoices API), POST it, store
   the external invoice id back on our side, mark entries invoiced.

Add an idempotency key (workspace_id + a hash of selected
time_entry_ids) so re-clicking the button doesn't double-invoice.

Output: the Postgres function for the internal path, a TypeScript
adapter pattern for the external invoicing APIs (so we can add new
ones later), and the idempotency check.