# Clockify CSV imports — parse → stage → resolve → commit (M4)

The first write path: time data enters the app through a Clockify
Detailed Report CSV upload at `/import`, a staged two-step resolution
(every unknown operator/client/project name needs an explicit human
decision — 02 §5 invariant 4 / ADR #041 carried), and a transactional
commit that stamps money at write time. Parity bar:
`/srv/apps/work/docs/rebuild/01-current-system/features/imports.md`
(the B1–B77 behaviors + T-IMP/T-CLK test specs, as amended by its
"Rebuild deltas" section); deltas recorded in
[ADR #005](../decisions.md).

**Toggl is gone** (judgment #8): the importer speaks Clockify + manual
only. No Toggl header aliases exist in the parser; historical
toggl/backfill rows migrated as plain data.

## The parser (`app/services/imports/clockify.py`)

Faithful port of the legacy `lib/clockify.ts` — the whole 44-fixture
CLK suite runs against it verbatim (`tests/imports/
test_clockify_parser.py`), and `scripts/cross_validate_csv.py` diffs
it field-by-field against the actual TS code of record for every
archived real CSV (`harness/csv-archive/README.md`).

- **Header matching** (B14): lowercased exact-match candidates, first
  column wins. Required: project, client, description, email,
  start/end date, start/end time, duration. `billable` optional →
  defaults **true**. Clockify-only candidates (no member/stop-date
  aliases).
- **Date auto-detect** (B17–B19): ISO / DD-signal / MM-signal, then
  the **tightest-span tiebreaker** for fully-ambiguous CSVs (parse
  both ways, count distinct months, strictly-fewer wins; equal →
  abstain). The "26-01-08" incident is a pinned regression
  (T-IMP-030). When detection abstains the fallback is `MM/DD/YYYY`
  (the Clockify US default) — there is **no format picker in the UI**
  (auto-detection over configuration; the legacy fallback selector
  did not port).
- **Time parsing** (B21): AM/PM auto-detected per string; the fmt
  argument survives only for port-fidelity and is ignored.
- **Rows** (B24–B28): all-blank rows skipped; invalid START →
  row skipped + `Row <n>: invalid start date/time (…)` error; invalid
  END → row kept, end nulled, **and surfaced as a warning** (the
  legacy app swallowed it — wart #1 fixed, T-IMP-011). CSV "Client"
  column → `operator`; CSV "Project" splits on the FIRST literal
  `" : "`. Emails lowercased. Durations: `H:MM:SS` / `H:MM` / decimal
  hours.
- **Batch label** (B34–B36): `Jun 2 – Jun 8, 2026` (en dash), single
  day / cross-month / cross-year variants.

## Staging (`staging.py`, table `pending_imports`, alembic 0004)

Upload → validate (can_manage_imports; non-empty file; 25 MB cap) →
parse → stage the normalized entries as jsonb → the SPA routes to the
resolver. Attribution (`uploaded_by`) is the EFFECTIVE user (ADR #033
carried: view-as is true impersonation).

- Batch auto-naming (B10): typed name > date-range label > filename
  sans extension.
- **Expiry is enforced** (T-IMP-031, fixing legacy wart #5): expired
  rows purge on upload and read as not-found (then delete) on access.
- `pending_imports.tenant_id` deliberately has **no FK** — the nightly
  rebuild-from-legacy transforms truncate the entity tables and an FK
  would break that pipeline (see the 0004 migration docstring).
- The parse report (`parse_errors`, `parse_warnings`, `total_rows`)
  is staged too, so the resolver page can show "N of M rows parsed"
  with the per-row messages.
- Visibility (B38/B40 as scope rules, RLS deleted): uploader sees
  their own; owner / super-admin (not viewing-as) see all; everyone
  else gets the not-found message (existence never leaks).

## Resolver (`resolver.py`, GET /api/import/pending/{id})

Computes known/unknown per section with **case-insensitive matching
scoped by parent, chained** (B42): operator by name; client under its
resolved operator; project under its resolved (operator, client).
Incomplete rows (any of the triple blank) group into **buckets** by
signature, sorted by count descending (B44). The payload also carries
the whole entity catalog (operators / clients-with-edges / projects
labeled `{client} : {project}`) so the SPA computes live candidate
scoping — the legacy `effectiveClientId` reactivity (B51) is a pure
function in `frontend/src/features/import/resolverState.ts`.

Entity model mapping (02 §2): an operator is an org party; a client is
an org party joined to its operator by an active `direct_client`
engagement; a project row carries both party FKs. The tenant party
itself is an operator ("one party wearing two hats").

## Commit (`commit.py`, POST /api/import/pending/{id}/commit)

Guards in order: can_manage_imports → pending visible → `can_commit`
(uploader or can_transfer — B46). Then, **in one database
transaction**:

1. **Buckets first** (B56): map (empty selection = silent skip,
   T-IMP-020) / create (all three fields required, chains
   resolve-or-create inline) / skip.
2. **Operators → clients → projects** (B58–B60): an existing
   case-insensitive match ALWAYS wins over any decision; unknown
   names require an explicit decision (`No decision provided for
   unknown <kind> "<name>"…`); map targets are parent-checked
   (`…is under a different operator/client than expected.`); creates
   go through `services/entities/resolve.py` — the ONE
   resolve-or-create implementation (kills the legacy B57
   duplication), using exact `lower()` matching (never `ilike` —
   wart #4 gone).
3. **Batch row**: `row_count` = parsed-entry count (B62/T-IMP-029),
   `imported_by` = effective user, source `clockify`.
4. **Per-row**: worker resolved by email → person party (party.email)
   with an active subcontract/freelance engagement → `worker_party_id`
   + `via_engagement_id`; no match = unresolved row (NULL worker, NULL
   stamps — the post-commit summary lists the distinct unknown
   emails). `raw_operator/raw_client/raw_project` store the AS-PARSED
   text verbatim — the immutable import payload (02 §3); the legacy
   canonical-name rewrite is moot because resolved rows display via
   the project FK chain.
5. **Money stamps** — `services/money/stamping.py` is the only
   stamping site (04): cost/billout from hourly `compensation_terms`
   in force at write time (project-scoped term > engagement default),
   billout additionally through `rate_overrides` (worker-specific >
   project-wide; absolute is the rate, pct modifies the term default,
   pct+NULL default = NULL). Amount = source hours × rate, cents,
   ROUND_HALF_UP; **NULL = never priced, 0.00 = priced at zero**
   (judgment #19 — Gary stamps 0.00 cost / 250.00 billout on 10h).
   Currency rides with the amount as a pair (02 §3). Rates memoized
   per (engagement, project).
6. **Chunked insert** (B68): 500-row chunks; a failing chunk raises
   `Insert failed at rows <a>–<b>: <msg>` and the WHOLE transaction
   rolls back — entries, batch, AND created entities (stronger than
   legacy, which leaked entity debris — wart #2 gone). The staging row
   survives for retry from the resolver.
7. On success the staging row is consumed and the response carries the
   matched/unmatched summary.

## Batches (`batches.py`)

- `GET /api/import/batches` — newest first, scope()-filtered (view-as
  of a non-owner narrows to the effective user's batches). Per-batch
  entry counts come from ONE grouped query (legacy N+1 not ported).
- `GET /api/import/batches/{id}/summary` — the post-import card data
  (matched/unmatched + unknown emails, B73).
- `DELETE /api/import/batches/{id}` — **the delete guard re-keys from
  "transferred" to invoice attachment** (rebuild delta / T-IMP-003):
  any entry with `invoice_line_id` set blocks the delete with the real
  count in the message. Delete removes entries + batch in one
  transaction (no orphan window, T-IMP-028). View-as out-of-scope
  deletes refuse (`OUT_OF_SCOPE`).

## UI (`frontend/src/features/import/`)

- **/import**: upload panel (file + optional batch name + notes;
  "formats are auto-detected — nothing to configure"; submit label
  flips to "Parsing CSV…"), post-import green summary card
  (`?import_id=`), batch table (name links to `/entries?batch=`,
  Locked label with tooltip when invoice-attached, delete confirm
  embeds the REAL entry count).
- **/import/resolve?id=**: header (batch · filename · source · count ·
  "← Cancel and go back"), amber parse-issues panel ("N of M rows
  parsed" + per-row messages), summary counts with the red
  missing-fields stat and the ✓ all-known state, ONE collapsed
  known-entities accordion, map-first inline radios per unknown name
  (map disabled "(no candidates at this scope)"; create is the
  default; "(new operator/client — will be created)" hints; live
  project-candidate scoping), buckets with the bold `op / cl / pr`
  identity line (— for blanks), Map (datalist combobox labeled
  `{client} : {project}`) → Create (3 prefilled inputs with name
  datalists) → tiny grey skip, focus-capture auto-switching radios.
  Submit: "Apply decisions and import" / "Import", pending
  "Importing…"; Cancel with "Cancelling…"; the canonical-names note.
- Nav: TIME → Import is live.

## Permissions

| action | rule |
|---|---|
| upload, commit, delete batch | `can_manage_imports` (owner, manager, super-admin not viewing-as) |
| resolve/commit someone else's pending | uploader, or `can_transfer` (owner / super-admin) |
| view another's pending row | owner / super-admin only — others get not-found |
| batch list | any tenant role (metadata tenant-visible); view-as narrows |

## Test map

- `tests/imports/test_clockify_parser.py` — the CLK port (42 tests).
- `tests/imports/test_staging.py` — T-IMP-004/005/006/007/008/011/012/
  025/026/027/031.
- `tests/imports/test_resolver.py` — B42/B43/B44 computation.
- `tests/imports/test_commit.py` — T-IMP-001/002/015/016/017/018/019/
  020/021/022/023/026/027/029 + T-CLK-018 + entity-rollback.
- `tests/imports/test_batches.py` — T-IMP-003/006/027/028 + summary.
- `tests/imports/test_import_api.py` — HTTP round-trip (multipart,
  structured errors).
- `tests/money/test_stamping.py` — T-CLK-021b/022b/023, T-MNY-001,
  T-TEAM-07/08, T-ADJ-001/002/003/004/008 (write-path halves).
- `frontend/src/features/import/resolverState.test.ts` — B47 defaults,
  T-IMP-014 live scoping, payload build (T-IMP-020 passthrough).
- Cross-validation: `scripts/cross_validate_csv.py` (+ the archived
  weekly CSVs per `harness/csv-archive/README.md`).

## Changelog

- **0.5.0 (2026-07-08)** — M4: the whole pipeline shipped (parser port
  + staging + resolver + transactional commit + stamping + batches +
  /import UI + cross-validation harness). Deltas in ADR #005.