# CSV Imports (Clockify/Toggl → time_entries) — current-system spec (parity bar for the rebuild) > The import pipeline is how time data enters the app: a manager or owner uploads a Clockify (or, historically, Toggl) Detailed Report CSV, the parser normalizes every row into a naive-timestamp entry, the two-step resolver forces an explicit human decision for every operator/client/project name that doesn't already exist as an entity, and commit writes an immutable-ish batch (`clockify_imports`) plus one `time_entries` row per parsed row — with cost and billout dollars **stamped at write time** (ADR #014). It is the most behavior-dense feature in the app and the most battle-tested (44-test CLK-* suite); the rebuild ports it faithfully (judgment call #14) minus the Toggl dialect. Code of record: `lib/clockify.ts`, `app/(app)/import/page.tsx`, `app/(app)/import/actions.ts`, `app/(app)/import/upload-form.tsx`, `app/(app)/import/resolve/page.tsx`, `app/(app)/import/resolve/actions.ts`, `app/(app)/import/resolve/resolver-form.tsx`, migration `supabase/migrations/20260524000003_pending_imports.sql`, tests `tests/clockify.test.ts`. ## User workflows 1. **Adi's Clockify upload (the primary pipeline).** Adi (manager, Tingang team) exports a weekly/monthly Clockify Detailed Report and uploads it at `/import`. He should not have to know or care about date/time formats — "we both have the same upload screen. how does the app know what format to expect?" (rian, 05-22). Formats are auto-detected; the batch is auto-named from the CSV's date range (e.g. "Jun 2 – Jun 8, 2026") if he doesn't type a name. 2. **Resolving new names before anything is created.** If the CSV contains an operator/client/project name the org doesn't know, the resolver page forces a per-name decision: *Create new* or *Map to existing* (fixing CSV typos like "Vicotria" → mapping to "Victoria" without polluting the entity list — ADR #041 replaced the old silent create-on-the-fly behavior). 3. **Rows with missing fields.** Clockify projects with no client assigned (e.g. "Bowden Works → Brentwood, no client") parse with blank fields. The resolver surfaces them as "buckets" and lets the user assign each bucket to a real project (map or create the full chain) or skip (row imports Blocked). 4. **Rian's Toggl history (retired).** `source=toggl` exists because rian imported his own Toggl logs in May 2026. As of 2026-07-07: "I no longer have any need for toggl." 5. **Batch management.** Every upload is a named batch. Batches list at `/import`, link to their entries (`/entries?batch=`), and can be deleted — cascading their entries — unless any entry has been transferred/invoiced (rian, 05-21: "delete an import batch unless an item in that import has been transferred already"). ## Complete behavior ### A. Upload form (`/import`, client component `UploadForm`) - **B1.** Form fields: `source` radio (`clockify` default | `toggl`), `name` text (optional batch name), `file` (`accept=".csv,text/csv"`, required), `date_format` select (`MM/DD/YYYY (Clockify US default)` | `DD/MM/YYYY (Clockify EU)` | `YYYY-MM-DD (ISO / Toggl default)`), `notes` text (optional). There is **no time-format field** — it was removed when AM/PM auto-detect landed (2026-05-27). - **B2.** The date-format select is a **fallback only** (label: "auto-detected; this is a fallback for fully-ambiguous CSVs"). It is keyed by source (`key={'date-'+source}`) so flipping the radio remounts it with the per-source default: Clockify → `MM/DD/YYYY`, Toggl → `YYYY-MM-DD`. - **B3.** Submit button uses `useFormStatus` (`SubmitButton`): label "Upload and import" flips to "Parsing CSV…" in flight (double-submit protection — a real incident, 06-10). - **B4.** Helper text lists expected columns per source. Clockify: `Project, Client, Description, Task, User, Group, Email, Tags, Billable, Start Date, Start Time, End Date, End Time, Duration (h)`. Toggl: `Project, Client, Description, Email, Member, Start date, Start time, Stop date, Stop time, Duration. Billable is optional.` ### B. Upload action (`uploadClockifyCsv` in `app/(app)/import/actions.ts`) All failures `redirect('/import?error=')`. - **B5.** Permission: `canManageImports(eu)` required → else "You do not have permission to import." - **B6.** `source` is lowercased; any value other than `'toggl'` coerces to `'clockify'`. - **B7.** `date_format` default when absent: `'YYYY-MM-DD'` (toggl) / `'MM/DD/YYYY'` (clockify). `time_format` default `'24h'` (toggl) / `'12h'` (clockify) — stored in `pending_imports` but **ignored by the parser** (dead plumbing). - **B8.** File required and non-empty → else "Please choose a CSV file to upload." Size cap **25 MB** (`25 * 1024 * 1024`) → else "File too large (25MB max)." Next.js server-action `bodySizeLimit` is raised to `'25mb'` in `next.config.mjs` to match. - **B9.** If `parseClockifyCsv` yields **zero** entries → fail with `result.errors[0]?.message` or "No valid rows found in the CSV. Check the format selections." If **some** rows parsed, per-row parse errors are **silently discarded** (known wart — see Edge cases). - **B10.** **Batch auto-naming:** `batchName = typedName ?? (formatDateRangeLabel(dateRange) ?? filename-sans-extension)`. Typed (non-blank, trimmed) name always wins. `formatDateRangeLabel` renders the CSV's start-date range (see B34–B36); when the range is unusable, the filename with its last `.ext` stripped is used. - **B11.** **Fail-fast team check:** the importer must have a `teams` row in the org (`getTeamForUser(supabase, org.id, eu.effective_user_id)` — teams are keyed by `owner_user_id`) → else "You don't have a team in this organization yet. An org owner needs to create one for you before you can import." (Email→team_member resolution happens at commit; this stops the user reaching the resolver only to fail there.) - **B12.** **Staging:** insert one `pending_imports` row: `{ org_id, uploaded_by: eu.effective_user_id, filename: file.name, batch_name, notes, source, date_format, time_format, parsed_entries: ParsedEntry[] (jsonb) }`. Attribution uses **effective** user id (ADR #033 — view-as is true impersonation). Then `redirect('/import/resolve?id=')`. ### C. Parsing — headers and dialects (`parseClockifyCsv` in `lib/clockify.ts`) - **B13.** CSV parsed with papaparse: `Papa.parse(csv.trim(), { skipEmptyLines: true })`. Zero rows → single error `{ row: 0, message: 'CSV is empty.' }`. - **B14.** Header matching: each header cell is lowercased + trimmed and checked for **exact membership** in a per-key candidate list; the **first matching column index wins**. Candidates (this is what makes Clockify and Toggl one parser): | key | candidates | |---|---| | project | `project` | | client | `client` | | description | `description` | | task | `task` (indexed, never read) | | user | `user`, `member` | | email | `email` | | billable | `billable` | | startDate | `start date`, `startdate` | | startTime | `start time`, `starttime` | | endDate | `end date`, `enddate`, `stop date`, `stopdate` | | endTime | `end time`, `endtime`, `stop time`, `stoptime` | | duration | `duration (h)`, `duration` | | rate | `billable rate (usd)`, `billable rate` | | amount | `billable amount (usd)`, `billable amount` | - **B15.** **Required** columns: `project, client, description, email, startDate, startTime, endDate, endTime, duration`. Any missing → abort with row-0 error: `CSV is missing required columns: . Make sure you exported with Clockify's default Detailed Report layout.` **Optional:** `billable` (Toggl Detailed Report omits it), `user`, `task`, `rate`, `amount`. - **B16.** **Billable default:** column present → `parseBoolean(cell)` where true iff trimmed-lowercased value ∈ {`yes`, `true`, `1`, `y`}; column absent → **`true`** ("typical for client time tracking"). ### D. Parsing — date-format auto-detect (`detectDateFormat`) Applied to **all non-empty Start Date cells** up front; result overrides the form selector; selector is only used when detection returns `null`: `detectedDateFormat = detectDateFormat(startDates) ?? dateFormat`. - **B17.** Per string: matches `/^\d{4}-\d{1,2}-\d{1,2}$/` → ISO signal (`sawIso`). Otherwise matches `/^(\d{1,4})[/\-.](\d{1,4})[/\-.](\d{1,4})$/` (separators `/`, `-`, `.`): first slot > 12 → DD/MM signal; **else if** second slot > 12 → MM/DD signal. - **B18.** Resolution order: ISO-only → `'YYYY-MM-DD'`; DD/MM signal without MM/DD → `'DD/MM/YYYY'`; MM/DD without DD/MM → `'MM/DD/YYYY'`; ISO mixed with both slash signals → `'YYYY-MM-DD'`. (Mixed DD/MM + MM/DD in one CSV is documented as unsupported — "the parser picks whichever unambiguous signal it sees first.") - **B19.** **Tightest-span tiebreaker** (fully ambiguous input — every slot ≤ 12, e.g. a weekly export of June 1–8): parse the whole date list under BOTH `DD/MM/YYYY` and `MM/DD/YYYY`, count **distinct `YYYY-MM` months** each yields (Set of `parsed.slice(0,7)`), pick the interpretation with **strictly fewer** months. Both counts 0 → `null`. Equal counts → `null` (don't guess; caller falls back to the form selector). Rationale: the correct reading of a weekly export clusters into 1 month; the wrong one scatters across ~8 (real incident: the "26-01-08" batch scattered 132 June entries across Jan–Aug and had to be deleted and re-imported). ### E. Parsing — dates, times, durations, rows - **B20.** `parseDateOnly(s, fmt)` → zero-padded `YYYY-MM-DD` or `null`. ISO branch is strict (`/^(\d{4})-(\d{1,2})-(\d{1,2})$/`, dashes only). Slash branch accepts separators `/`, `-`, `.`; MM/DD/YYYY maps (m,d,y)=(a,b,c); DD/MM/YYYY maps (d,m,y)=(a,b,c). **2-digit years: `y < 100 → y += 2000`.** Validation: `1 ≤ m ≤ 12`, `1 ≤ d ≤ 31` (no per-month day check — Feb 31 passes), `2000 ≤ y ≤ 2100`; else `null`. - **B21.** `parseTimeOnly(s, _fmt?)` → 24h-normalized `HH:MM:SS` or `null`. **Format auto-detected PER-STRING** by AM/PM suffix (`/\s*[AaPp][Mm]\s*$/`); the `fmt` parameter is accepted for back-compat and **ignored**. With meridiem: `/^(\d{1,2}):(\d{2})(?::(\d{2}))?\s*([AaPp][Mm])$/`, requires `1 ≤ h ≤ 12`, `m ≤ 59`, `sec ≤ 59`; `PM && h<12 → h+=12`; `AM && h===12 → h=0`; missing seconds → `00`. Without meridiem: `/^(\d{1,2}):(\d{2})(?::(\d{2}))?$/`, `h ≤ 23`, `m ≤ 59`, `sec ≤ 59`. - **B22.** `joinDateTime(date, time)`: either null → null; else the naive string `` `${date} ${time}` `` (`YYYY-MM-DD HH:MM:SS`). **No timezone conversion anywhere** — timestamps are source-local wall-clock end-to-end (ADR #003). - **B23.** `parseDurationSeconds(s)`: `H+:MM:SS` → `h*3600+m*60+s`; `H+:MM` → `h*3600+m*60`; else decimal hours (`Number(t)` finite and `≥ 0`) → `Math.round(dec*3600)`; else/blank → `null`. - **B24.** Row loop (data rows = index 1..n): rows where **every** cell is null/blank are skipped silently. Invalid start date OR start time → error `Row : invalid start date/time ( )` (1-indexed counting the header as row 1) and the row is skipped. **Invalid end date/time is silent**: `end_at` becomes `null`, row still imports. - **B25.** Field mapping per row: `source_user_email` = trimmed + **lowercased** email cell; `source_user_name` = trimmed user/member cell ('' if column absent); **`operator` = trimmed raw CSV "Client" column** (the operator agency); the CSV **"Project" column is split** by `splitClientProject`. - **B26.** `splitClientProject(raw)`: splits on the **first occurrence** of the literal delimiter `" : "` (space-colon-space — the legacy `{Client} : {Project}` naming convention). No delimiter → `{ client: null, project: raw.trim() }`. With delimiter → `{ client: left.trim() || null, project: right.trim() }`. - **B27.** `source_rate_usd` / `source_amount_usd` from the optional rate/amount columns via `parseNumberOrNull` (blank → null; non-finite → null). - **B28.** `ParsedEntry` shape (this exact shape is persisted as jsonb in `pending_imports.parsed_entries`): ```ts { source_user_name: string, source_user_email: string, operator: string, client: string | null, project: string, description: string, billable: boolean, start_at: 'YYYY-MM-DD HH:MM:SS', end_at: string | null, duration_seconds: number | null, source_rate_usd: number | null, source_amount_usd: number | null } ``` - **B29.** `ParseResult` also carries: `errors[{row, message}]`, `totalRows` = raw data-row count (`rows.length - 1`, including skipped/errored), `uniqueEmails` (lowercased, sorted), `dateRange` = `{min, max}` of parsed **start** dates (lexicographic on ISO strings) or null. ### F. Batch auto-name label (`formatDateRangeLabel`) - **B34.** Input endpoints must both match `/^(\d{4})-(\d{2})-(\d{2})$/`, else `null`. Month names: `Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec`. Day numbers unpadded. - **B35.** Same day → `Jun 2, 2026`. Same year → `Jun 2 – Jun 8, 2026` (also cross-month: `May 28 – Jun 3, 2026`). Cross-year → `Dec 28, 2025 – Jan 3, 2026`. - **B36.** The range separator is an **en dash with spaces** (`" – "`), not a hyphen. ### G. Staging table (`pending_imports`) - **B37.** Columns: `id uuid pk`, `org_id fk organizations ON DELETE CASCADE`, `uploaded_by fk auth.users ON DELETE CASCADE`, `uploaded_at default now()`, `expires_at default now() + interval '24 hours'`, `filename`, `batch_name`, `notes`, `source text CHECK in ('clockify','toggl')`, `date_format`, `time_format`, `parsed_entries jsonb not null`. Indexes on `uploaded_by` and `expires_at`. - **B38.** RLS (both select and all-modify policies): `uploaded_by = auth.uid() OR is_org_owner(org_id) OR is_super_admin_caller()` — uploader sees/manages own; owner/super-admin see all. - **B39.** **The 24h expiry is nominal only.** `expires_at` is set and indexed, and the UI says pending imports "last 24h", but **no cron/cleanup job exists and no query filters on `expires_at`** — stale rows accumulate harmlessly and a "24h-old" pending import still commits fine. (Migration comment: "cleanup is a follow-up.") ### H. Resolver page (`/import/resolve?id=`, server component) - **B40.** Route uses a **query param** `id`, not a path segment. Missing `id` → error page "Missing pending-import id." with back-link. Pending row not found (RLS-hidden, cancelled, or deleted) → "Pending import not found. It may have expired (they last 24h) or someone else cancelled it." - **B41.** Page header shows: batch_name (or "Unnamed batch") · filename (or "(no filename)") · source · entry count, plus "← Cancel and go back" link. - **B42.** Loads all org `operators(id,name)`, `clients(id,operator_id,name)`, `projects(id,client_id,name)` ordered by name. Known-ness is computed by **case-insensitive name matching scoped by parent**: operator by `lower(name)`; client by `` `${operator_id}|${lower(name)}` ``; project by `` `${client_id}|${lower(name)}` ``. A project can only be "known" if its operator AND client resolve first (chained). - **B43.** Distinct names are extracted only from entries where the relevant parts are truthy: `distinctOperators` (sorted), `distinctOpClients` keyed `` `${op}|${client}` ``, `distinctTriples` keyed `` `${op}|${client}|${project}` ``. **Composite keys are pipe-delimited everywhere** (known wart: a name containing `|` corrupts key matching). - **B44.** **Incomplete buckets:** entries where **any** of operator/client/project is blank are grouped by signature `` `${op ?? ''}|${cl ?? ''}|${pr ?? ''}` `` into buckets `{key, opName|null, clName|null, prName|null, count}`, sorted by count **descending**. (Before 2026-06-09 these rows were silently excluded from the resolver and imported with NULLs = Blocked with no warning.) - **B45.** Summary panel: per-section known/unknown counts ("N known · M unknown"), plus a red "Missing fields: X rows · Y bucket(s)" stat when buckets exist. If everything is known and there are no buckets: "✓ All names already exist. Click **Import** below to commit N entries." Otherwise a warning explains Create-new / Map-to-existing. - **B46.** **`canSubmit` gate (page-level):** `canManageImports(eu) && (pending.uploaded_by === eu.effective_user_id || canTransfer(eu))`. Compares against **effective** user id (ADR #033). Managers resolve their own pending imports; owners and super-admins (not in view-as) can resolve anyone's. Fails → "You don't have permission to commit this import." instead of the form. (Server-side, cross-user access is additionally blocked by pending_imports RLS at row load.) ### I. Resolver form (client component `ResolverForm`) — wire contract - **B47.** Decision encoding: `'create'` or `'map:'`. Every unknown row and every incomplete bucket **defaults to `'create'`**. - **B48.** Hidden fields pair a name with a decision; indices are the original (possibly non-contiguous) array indices: - `op_name_` = raw operator name; `op_decision_` - `cl_name_` = `` `${opName}|${clName}` ``; `cl_decision_` - `pr_name_` = `` `${opName}|${clName}|${prName}` ``; `pr_decision_` - `incomplete_key_` = bucket signature; `incomplete_decision_` ∈ `map | create | skip`; `incomplete_target_` = existing project id (from `SearchableCombobox`, options labeled `` `${clientName} : ${projectName}` `` sorted by label); `incomplete_create_op_` / `incomplete_create_cl_` / `incomplete_create_pr_` = free-text inputs **prefilled from the bucket's parsed values**, each with a `` of existing names. - **B49.** Known entities across all three sections fold into ONE collapsed `
` accordion: "Already in your org: **N operators · M clients · K projects** (click to view)" with ✓-prefixed lists. (Was three accordions; rian: "the 3 accordions is overkill.") - **B50.** Unknown-row UI: two radios — "Map to existing:" with an inline `