# Data Import

**Spec:** 40
**Date:** 2026-05-31
**Status:** Draft
**Depends on:** `customers-module`, `invoices-core`, `foundation-auth-rbac`, `foundation-design-system`, `system-communications-notifications`, `zync-subscription`
**Referenced by:** `settings-module` (spec 25 — import link in integrations hub), `00-index.md`

---

## Overview

Tenants can bulk-import **Customers**, **Invoices**, **Products/Services**, and **Time Entries** from CSV or XLSX files. The feature lives at `/settings/import` (OWNER and ADMIN only, Business+ tier). Files are uploaded to R2, enqueued for async processing via Cloudflare Queues, and results surface through the import history list with downloadable error CSVs.

Design principle: **partial success over all-or-nothing.** Each row is committed independently. Bad rows are recorded and downloadable; good rows land in the DB immediately. This matches the mental model of migrating messy real-world data.

---

## Route & Role Gate

| Property | Value |
|----------|-------|
| Route | `/settings/import` |
| Allowed roles | `OWNER`, `ADMIN` |
| Tier gate | **Business+** (freelancer sees locked state with upgrade prompt) |
| Layout | Standard app shell + settings sidebar |

### Tier Gate — Freelancer locked state

```
┌──────────────────────────────────────────────────────────────┐
│  ⬆  Data Import                                              │
│                                                              │
│  ┌──────────────────────────────────────────────────────┐   │
│  │  🔒  Data import is available on the Business plan.  │   │
│  │                                                       │   │
│  │  Migrate your customers and invoices in minutes       │   │
│  │  using a CSV or Excel file.                          │   │
│  │                                                       │   │
│  │  [  Upgrade to Business  ]                           │   │
│  └──────────────────────────────────────────────────────┘   │
└──────────────────────────────────────────────────────────────┘
```

"Upgrade to Business" opens the upgrade-upsell modal (spec 36).

---

## Import Flow — Five Stages

```
  ┌─────────────────────────────────────────────────────────────────┐
  │                     Import Flow (5 stages)                      │
  │                                                                 │
  │  [1 Select Type] → [2 Upload] → [3 Map Columns] →              │
  │  [4 Preview & Validate] → [5 Processing & Results]             │
  │                                                                 │
  │  ● = current   ○ = not yet reached   ✓ = completed             │
  └─────────────────────────────────────────────────────────────────┘
```

The stepper is rendered at the top of a centred content card (max-width 720px) inside the settings layout. Stages 1–4 are synchronous (client-side); stage 5 triggers the async pipeline.

---

## Stage 1 — Select Import Type

```
┌─────────────────────────────────────────────────────────────────┐
│  What would you like to import?                                 │
│                                                                 │
│  ┌─────────────────────────┐  ┌─────────────────────────────┐  │
│  │   👥  Customers          │  │   📄  Invoices               │  │
│  │                         │  │                             │  │
│  │  Name, email, phone,    │  │  Customer, invoice number,  │  │
│  │  address, company,      │  │  dates, status, line items  │  │
│  │  notes                  │  │                             │  │
│  │  Supports: CSV, XLSX    │  │  Supports: CSV only         │  │
│  │                         │  │                             │  │
│  │  ⬇ Download template   │  │  ⬇ Download template        │  │
│  └─────────────────────────┘  └─────────────────────────────┘  │
│                                                                 │
│  ┌─────────────────────────┐  ┌─────────────────────────────┐  │
│  │   📦  Products/Services  │  │   ⏱  Time Entries           │  │
│  │                         │  │                             │  │
│  │  Name, price, unit,     │  │  Date, duration, project,   │  │
│  │  tax rate, SKU          │  │  task, user, billable       │  │
│  │                         │  │                             │  │
│  │  Supports: CSV, XLSX    │  │  Supports: CSV only         │  │
│  │                         │  │                             │  │
│  │  ⬇ Download template   │  │  ⬇ Download template        │  │
│  └─────────────────────────┘  └─────────────────────────────┘  │
│                                                                 │
│  [ Continue → ]                                                 │
└─────────────────────────────────────────────────────────────────┘
```

- Exactly one type must be selected before "Continue" is enabled.
- "Download template" links download a ready-to-fill CSV file (see §Template CSVs).
- Invoices: XLSX is disabled in v1. The card shows "Supports: CSV only" with a tooltip: "Excel import for invoices will be added in a future update."

---

## Stage 2 — Upload File

```
┌─────────────────────────────────────────────────────────────────┐
│  Upload your file                                               │
│                                                                 │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │                                                         │   │
│  │           Drag & drop your file here                   │   │
│  │           — or —                                        │   │
│  │           [ Browse files ]                             │   │
│  │                                                         │   │
│  │   Accepted: CSV, XLSX (customers only)                 │   │
│  │   Max file size: 10 MB · Max rows: 5,000               │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│  [← Back]                          [ Upload & Continue → ]     │
└─────────────────────────────────────────────────────────────────┘
```

**Client-side validation before upload:**
- File extension must match accepted types for the chosen import type.
- File size must not exceed 10 MB.
- If validation fails, an inline error replaces the dropzone border with `--accent`.

**Upload sequence:**
1. `POST /api/imports/upload` → presigned R2 PUT URL (signed for 5 min).
2. Browser PUTs file directly to R2.
3. `POST /api/imports` with `{ type, r2_key, original_filename }` → creates `import_jobs` row (status = `pending`), returns `{ import_job_id, columns: string[] }`.
4. Advance to Stage 3.

The response `columns` array is the header row extracted server-side from the uploaded file (first row of CSV, or first non-empty row of XLSX sheet 1).

**Error states:**
- Upload network failure → toast "Upload failed. Please try again."
- File too large → toast "File exceeds 10 MB limit."
- Row count exceeds 5,000 (detected server-side on `POST /api/imports`) → toast "File contains more than 5,000 rows. Split into smaller files and re-upload."
- Unsupported format (e.g. PDF) → toast "Unsupported file type."

---

## Stage 3 — Column Mapping

The server returns `columns` (raw header names from the file). The UI shows a two-column mapping table: **File column** (left) → **Zync field** (right).

### Auto-detection

Auto-detection runs client-side against the `columns` array. Matching is case-insensitive, strips non-alphanumeric characters, then checks against a fixed alias table:

**Customer aliases:**

| Zync Field | Recognized column names |
|------------|------------------------|
| `name` | name, full name, fullname, customer name, client name, שם, שם לקוח |
| `email` | email, e-mail, email address, אימייל, דואל, דוא"ל |
| `phone` | phone, phone number, mobile, tel, טלפון, נייד |
| `company` | company, company name, business, organization, חברה, שם חברה |
| `address_street` | street, address, street address, רחוב, כתובת |
| `address_city` | city, עיר |
| `address_state` | state, region, מחוז |
| `address_zip` | zip, zip code, postal code, מיקוד |
| `address_country` | country, ארץ, מדינה |
| `notes` | notes, comments, remarks, הערות |

**Invoice aliases:**

| Zync Field | Recognized column names |
|------------|------------------------|
| `customer_name` | customer, customer name, client, client name, שם לקוח |
| `customer_email` | customer email, client email, אימייל לקוח |
| `invoice_number` | invoice number, invoice #, invoice id, מספר חשבונית |
| `issue_date` | issue date, date, invoice date, תאריך הנפקה, תאריך |
| `due_date` | due date, payment date, תאריך פירעון |
| `status` | status, סטטוס |
| `line_description` | description, item, item description, תיאור, פריט |
| `line_qty` | qty, quantity, כמות |
| `line_unit_price` | unit price, price, rate, מחיר יחידה, מחיר |
| `line_tax_rate` | tax rate, vat, vat rate, מע"מ, שיעור מס |

### Column Mapping UI

```
┌─────────────────────────────────────────────────────────────────┐
│  Map your columns                                               │
│  We recognized 6 of 8 columns automatically.                   │
│                                                                 │
│  File column          Zync field             Status            │
│  ─────────────────    ──────────────────     ──────────        │
│  Full Name            → Name *               ✓ Auto-matched    │
│  Email Address        → Email                ✓ Auto-matched    │
│  Mobile               → Phone                ✓ Auto-matched    │
│  Company Name         → Company              ✓ Auto-matched    │
│  City                 → City                 ✓ Auto-matched    │
│  Remarks              → Notes                ✓ Auto-matched    │
│  Ref Code             → [Select field ▾]     ⚠ Unmapped        │
│  Internal ID          → [Ignore this column] ✓ Ignored         │
│                                                                 │
│  * Required field                                               │
│                                                                 │
│  [← Back]                          [ Continue → ]              │
└─────────────────────────────────────────────────────────────────┘
```

- Each unmapped column shows a `<select>` dropdown populated with available Zync fields + "Ignore this column" option.
- Required fields (`name` for customers; `customer_name` or `customer_email` for invoices) must be mapped before "Continue" is enabled. A validation message appears if the user attempts to advance without mapping required fields.
- Multiple file columns can map to the same Zync field only if the field is `notes` (concatenated with `; ` separator). All other fields: duplicate mapping shows an error inline.
- Invoice CSV multi-line items: if a single CSV row contains one line item, the worker accumulates consecutive rows with the same `invoice_number` into a single invoice. If `invoice_number` is empty for a row following a row with the same customer, the row is treated as a continuation line item of the previous invoice.
- The mapping config is submitted as part of `POST /api/imports/:id/start` (see §API).

---

## Stage 4 — Preview & Validate

The worker pre-validates the first 5 rows synchronously on `POST /api/imports/:id/preview` before the full async job is enqueued.

```
┌─────────────────────────────────────────────────────────────────┐
│  Preview — first 5 rows                                         │
│  3 valid · 1 warning · 1 error                                 │
│                                                                 │
│  Row  Name              Email              Status              │
│  ───  ────────────────  ─────────────────  ──────────────────  │
│  1    Rivka Cohen        rivka@example.com  ✓                   │
│  2    Benny Levi         benny@example.com  ✓                   │
│  3    Orna Shapira       orna@example.com   ✓                   │
│  4    Dana Katz          dana@example.com   ⚠ Duplicate email   │
│                                                                 │
│       (email already exists — row will be skipped)             │
│                                                                 │
│  5    [empty]            test@example.com   ✗ Name is required  │
│                                                                 │
│  Errors will be skipped. Warnings will be skipped with a note. │
│  Remaining rows are not shown — they will be processed after   │
│  you confirm.                                                   │
│                                                                 │
│  [← Back]                      [ Confirm & Start Import → ]    │
└─────────────────────────────────────────────────────────────────┘
```

- Row background:
  - Error row: `background: var(--danger-bg)` (light danger tint).
  - Warning row: `background: oklch(97% 0.015 80)` (amber tint).
  - Valid row: default `--bg`.
- Error message shown inline below the row.
- "Confirm & Start Import" is always enabled (user can proceed even with preview errors — the full file processes with partial success).
- "← Back" returns to column mapping without discarding the mapping configuration.

---

## Stage 5 — Processing & Results

Clicking "Confirm & Start Import" calls `POST /api/imports/:id/start`, which enqueues the `import.process` job and transitions `import_jobs.status` to `processing`. The UI transitions to a progress view.

### Processing view (polling)

```
┌─────────────────────────────────────────────────────────────────┐
│  Importing customers…                                           │
│                                                                 │
│  ████████████████░░░░░░░░░░░  67% (335 / 500 rows)             │
│                                                                 │
│  This may take a minute. You can leave this page —             │
│  we'll notify you when it's done.                              │
│                                                                 │
│  [ View import history ]                                        │
└─────────────────────────────────────────────────────────────────┘
```

- Poll `GET /api/imports/:id` every 3 seconds.
- Progress bar width = `rows_processed / total_rows * 100%`.
- When `status = completed` or `status = failed`, transition to the results view.
- If the user navigates away, polling stops. They can return to `/settings/import` and see the job in history.

### Results view

```
┌─────────────────────────────────────────────────────────────────┐
│  Import complete                                                │
│                                                                 │
│  ✓  482 customers imported                                     │
│  ⚠   12 rows skipped (duplicate email)                         │
│  ✗    6 rows failed (validation error)                         │
│                                                                 │
│  [ ⬇ Download error report (CSV) ]                            │
│                                                                 │
│  [ Import another file ]    [ Go to Customers ]                │
└─────────────────────────────────────────────────────────────────┘
```

- "Download error report" only shown if `error_count > 0` or `skipped_count > 0`.
- "Go to Customers" / "Go to Invoices" deep-links to the relevant module.
- In-app notification is sent to the importing user when processing completes (spec 5 notification system, `import_completed` event).

---

## Import History List

The main `/settings/import` page (when tier is Business+) shows:

1. A "New Import" button (primary, top-right, `ps-4 pe-4 pt-2 pb-2`).
2. Import history table below.

```
┌─────────────────────────────────────────────────────────────────────────────┐
│  Data Import                                    [ + New Import ]             │
│                                                                              │
│  Type        File             Started           Status      Results          │
│  ──────────  ───────────────  ────────────────  ──────────  ───────────────  │
│  Customers   customers.csv    31 May, 14:32      ✓ Done      482 / 500       │
│                                                              ⬇ Errors (18)   │
│  Invoices    invoices_q1.csv  31 May, 11:05      ✓ Done      120 / 122       │
│                                                              ⬇ Errors (2)    │
│  Customers   old_export.xlsx  30 May, 09:18      ⚠ Done      91 / 100        │
│                                                              ⬇ Errors (9)    │
│  Invoices    march.csv        28 May, 16:44      ✗ Failed    —               │
│                                                              View error       │
└─────────────────────────────────────────────────────────────────────────────┘
```

- Status badges: `pending` (grey), `processing` (blue + spinner), `completed` (green), `failed` (red).
- "⬇ Errors (N)" link triggers `GET /api/imports/:id/errors.csv` — returns a CSV of failed/skipped rows.
- `failed` (whole-job failure, e.g. file unparseable) shows "View error" which expands an inline error message.
- Pagination: 20 rows per page, cursor-based.
- History retention: 90 days (purged by `data-retention-purge` cron, spec 28).

---

## Template CSVs

Two downloadable templates served as static files from R2 public bucket (or inlined as Worker responses):

### customers-template.csv

```
name,email,phone,company,address_street,address_city,address_zip,address_country,notes
Rivka Cohen,rivka@example.com,050-1234567,Cohen Ltd,הרצל 1,תל אביב,6120101,IL,לקוח VIP
```

### invoices-template.csv

```
customer_name,customer_email,invoice_number,issue_date,due_date,status,line_description,line_qty,line_unit_price,line_tax_rate
Cohen Ltd,rivka@example.com,INV-001,2026-01-15,2026-02-15,draft,ייעוץ עסקי,2,500,0.18
Cohen Ltd,rivka@example.com,INV-001,,,,תוכנה,1,1200,0.18
```

The second invoices row demonstrates a continuation line item (same `invoice_number`, no repeated header fields required).

### products-template.csv

```
name,description,price,currency,unit,tax_rate,sku,active
Web Design,Full website design and build,3500,ILS,item,17,SKU-001,true
Hourly Consulting,Strategy consulting,350,ILS,hour,17,SKU-002,true
```

Columns:
- `name` — **Required.** Product/service display name.
- `description` — Optional. Free text.
- `price` — **Required.** Numeric. Base price per unit.
- `currency` — Optional. ISO 4217 code. Default: `ILS`.
- `unit` — Optional. Free text (e.g. `hour`, `item`, `day`, `month`). Default: `item`.
- `tax_rate` — Optional. Integer 0–100 (percent). Default: `17`.
- `sku` — Optional. Internal stock-keeping unit code.
- `active` — Optional. `true` or `false`. Default: `true`.

### time-entries-template.csv

```
date,start_time,end_time,duration_minutes,project_name,task_name,user_email,description,billable
2026-05-31,09:00,11:30,,Project Alpha,API integration,dev@example.com,Backend work,true
2026-05-31,,,90,Project Beta,,pm@example.com,Planning session,false
```

Columns:
- `date` — **Required.** `YYYY-MM-DD` or `DD/MM/YYYY`.
- `start_time` — Optional. `HH:MM` (24-hour). Must be paired with `end_time` if provided.
- `end_time` — Optional. `HH:MM` (24-hour). Must be after `start_time`.
- `duration_minutes` — **Required if `start_time` + `end_time` are not both provided.** Positive integer.
- `project_name` — Optional. Matched to an existing project by exact name (case-insensitive) within the tenant.
- `task_name` — Optional. Matched to an existing task by name within the matched project. Ignored if `project_name` is absent.
- `user_email` — **Required.** Must be an existing tenant member's email address.
- `description` — Optional. Free text.
- `billable` — Optional. `true` or `false`. Default: `true`.

Template download endpoint: `GET /api/imports/templates/:type` where `type` = `customers` | `invoices` | `products` | `time-entries`.

---

## Validation Rules

### Customers

| Field | Rule |
|-------|------|
| `name` | **Required.** Non-empty string after trim. Error if missing → row fails. |
| `email` | Optional. If present: must match RFC 5322 simplified regex. Invalid format → row fails. Duplicate email within the tenant → row skipped with warning `"Duplicate email: {email}"`. |
| `phone` | Optional. No format validation (IL numbers vary). |
| `company` | Optional. Free text. |
| `address_*` | Optional. No format validation. |
| `notes` | Optional. Truncated to 5,000 characters silently. |

**Duplicate detection:** `SELECT id FROM customers WHERE tenant_id = $1 AND email = $2`. Check performed per-row inside the worker transaction.

### Invoices

| Field | Rule |
|-------|------|
| `customer_name` OR `customer_email` | **At least one required.** If both empty → row fails with `"Customer required"`. |
| `customer_email` | If present: valid email format. |
| `invoice_number` | Optional. If omitted, the worker auto-generates using the tenant's invoice sequence (spec 15 `invoice_sequences` table). |
| `issue_date` | Optional but recommended. If present: must parse as ISO 8601 date (`YYYY-MM-DD`). Invalid format → row fails. If absent: defaults to import date. |
| `due_date` | Optional. If present: must parse as ISO 8601 date. Must not be before `issue_date` if both present. |
| `status` | Optional. Accepted values (case-insensitive): `draft`, `sent`, `paid`, `cancelled`. Defaults to `draft` if absent or unrecognised. |
| `line_unit_price` | **Required per line.** Must be a numeric value (integer or decimal, optional leading `-` for credit lines). Non-numeric → row fails. |
| `line_qty` | Optional. Defaults to `1`. Must be a positive number if present. |
| `line_tax_rate` | Optional. Numeric 0–1 (e.g. `0.18` for 18%) or integer percentage (e.g. `18` — interpreted as `0.18`). Defaults to the tenant's default tax rate (from `tenant_settings.default_tax_rate`). |
| `line_description` | Optional. Truncated to 500 characters silently. |

**Unknown customer handling:** If `customer_name` (or derived name from email local-part) doesn't match an existing customer, the worker creates a new minimal customer record (`name`, `email` if provided) and links the invoice to it. This creation is part of the same per-row transaction.

**Multi-row line items:** Rows sharing the same `invoice_number` (and same `customer_name`/`customer_email`) are accumulated into one invoice. The worker sorts input rows by their original row number before processing. An invoice is committed once all its rows have been processed (the final row of the group is detected when the next row has a different `invoice_number` or when the batch ends).

### Products/Services

| Field | Rule |
|-------|------|
| `name` | **Required.** Non-empty string after trim. |
| `price` | **Required.** Numeric (integer or decimal, non-negative). Non-numeric or negative → row fails. |
| `currency` | Optional. Must be a valid ISO 4217 code if present. Invalid value → row fails. Default: `ILS`. |
| `unit` | Optional. Free text, max 50 characters. |
| `tax_rate` | Optional. Integer 0–100. Out-of-range or non-numeric → row fails. Default: `17`. |
| `sku` | Optional. Free text, max 100 characters. Duplicate `sku` within the tenant → row skipped with warning `"Duplicate SKU: {sku}"`. |
| `active` | Optional. Case-insensitive `true`/`false`/`1`/`0`. Invalid value → defaults to `true`. |

### Time Entries

| Field | Rule |
|-------|------|
| `date` | **Required.** Must parse as `YYYY-MM-DD` or `DD/MM/YYYY`. Invalid or future date > 1 year → row fails. |
| `start_time` | Optional. Must be `HH:MM` (24-hour). Invalid format → row fails. |
| `end_time` | Optional. Must be `HH:MM` (24-hour). Must be after `start_time` on the same date. Invalid format or before `start_time` → row fails. |
| `duration_minutes` | **Required if `start_time` and `end_time` are not both provided.** Must be a positive integer. If `start_time` + `end_time` are both provided, `duration_minutes` is ignored (calculated from the time range). |
| `project_name` | Optional. If present, must match an existing project in the tenant (case-insensitive, exact name match). No match → row fails with `"Project not found: {name}"`. |
| `task_name` | Optional. If present, `project_name` must also be present; task matched by name within the project. No match → row fails with `"Task not found: {name} in project {project}"`. |
| `user_email` | **Required.** Must be the email of an existing tenant member. No match → row fails with `"User not found: {email}"`. |
| `description` | Optional. Truncated to 1,000 characters silently. |
| `billable` | Optional. Case-insensitive `true`/`false`/`1`/`0`. Invalid value → defaults to `true`. |

**Global limits (both types):**
- Max file size: 10 MB (enforced at upload, before R2 PUT).
- Max rows: 5,000 (enforced server-side after header parse, returns HTTP 422 with `"ROW_LIMIT_EXCEEDED"`).
- Empty rows (all cells blank) are silently skipped.
- Rows beyond column count are silently truncated to the mapped column set.

---

## API Endpoints

### `POST /api/imports/upload`

Returns a presigned R2 PUT URL.

**Request body:**
```json
{
  "filename": "customers.csv",
  "content_type": "text/csv",
  "size_bytes": 48200
}
```

**Response `200`:**
```json
{
  "upload_url": "https://…r2.cloudflarestorage.com/…?X-Amz-Signature=…",
  "r2_key": "{tenantId}/imports/{uuid}-customers.csv",
  "expires_in": 300
}
```

**Errors:**
- `413` — `size_bytes` > 10 MB.
- `415` — `content_type` not in allowed list.

---

### `POST /api/imports`

Creates the `import_jobs` row after the file has been PUT to R2.

**Request body:**
```json
{
  "type": "customers",
  "r2_key": "{tenantId}/imports/{uuid}-customers.csv",
  "original_filename": "customers.csv"
}
```

**Response `201`:**
```json
{
  "id": "uuid",
  "status": "pending",
  "columns": ["Full Name", "Email Address", "Mobile", "Company Name", "City", "Remarks", "Ref Code", "Internal ID"]
}
```

**Errors:**
- `422 ROW_LIMIT_EXCEEDED` — file has >5,000 data rows.
- `422 PARSE_ERROR` — file cannot be parsed (corrupted, wrong format).

The server reads only the header row + counts total rows at this stage (no per-row validation).

---

### `POST /api/imports/:id/preview`

Validates the first 5 rows using the submitted column mapping. Synchronous.

**Request body:**
```json
{
  "mapping": {
    "Full Name": "name",
    "Email Address": "email",
    "Mobile": "phone",
    "Ref Code": null,
    "Internal ID": null
  }
}
```

`null` mapping means "ignore this column."

**Response `200`:**
```json
{
  "rows": [
    { "row": 1, "status": "valid", "data": { "name": "Rivka Cohen", "email": "rivka@example.com" } },
    { "row": 2, "status": "valid", "data": { "name": "Benny Levi", "email": "benny@example.com" } },
    { "row": 4, "status": "warning", "data": { "name": "Dana Katz", "email": "dana@example.com" }, "message": "Duplicate email: dana@example.com" },
    { "row": 5, "status": "error", "data": { "name": "", "email": "test@example.com" }, "message": "Name is required" }
  ],
  "total_rows": 500
}
```

---

### `POST /api/imports/:id/start`

Locks the mapping, transitions status to `processing`, enqueues `import.process` job.

**Request body:**
```json
{
  "mapping": { "Full Name": "name", "Email Address": "email", "Mobile": "phone" }
}
```

**Response `202`:**
```json
{
  "id": "uuid",
  "status": "processing"
}
```

**Errors:**
- `409` — job already started or completed.
- `422 REQUIRED_FIELD_UNMAPPED` — a required field has no mapping.

---

### `GET /api/imports/:id`

Poll for status and progress.

**Response `200`:**
```json
{
  "id": "uuid",
  "type": "customers",
  "status": "processing",
  "original_filename": "customers.csv",
  "total_rows": 500,
  "rows_processed": 335,
  "success_count": 320,
  "skipped_count": 12,
  "error_count": 3,
  "created_at": "2026-05-31T14:32:00Z",
  "completed_at": null,
  "error_message": null
}
```

---

### `GET /api/imports`

List all import jobs for the tenant. Cursor-based pagination.

**Query params:** `cursor`, `limit` (default 20, max 100).

**Response `200`:**
```json
{
  "jobs": [ /* array of import_jobs rows */ ],
  "next_cursor": "uuid|null"
}
```

---

### `GET /api/imports/:id/errors.csv`

Download error/skipped rows as CSV. Only available when `status = completed` and `error_count + skipped_count > 0`.

**Response:** `Content-Type: text/csv`, `Content-Disposition: attachment; filename="import-errors-{id}.csv"`.

CSV format (customers example):
```
row_number,original_data,status,message
4,"Dana Katz,dana@example.com,...",skipped,"Duplicate email: dana@example.com"
5,",test@example.com,...",error,"Name is required"
```

`original_data` is the raw CSV row as-received.

---

### `GET /api/imports/templates/:type`

**Path param:** `type` = `customers` | `invoices` | `products` | `time-entries`.

**Response:** `Content-Type: text/csv`, `Content-Disposition: attachment; filename="{type}-template.csv"`.

---

## Database Schema

### `import_jobs`

```sql
CREATE TABLE import_jobs (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  created_by      UUID NOT NULL REFERENCES users(id),
  type            TEXT NOT NULL CHECK (type IN ('customers', 'invoices', 'products', 'time_entries', 'bulk_action')),
  status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
  original_filename TEXT NOT NULL,
  r2_key          TEXT NOT NULL,          -- {tenantId}/imports/{uuid}-{filename}
  column_mapping  JSONB,                  -- { "File Col": "zync_field" | null }
  total_rows      INT,                    -- populated after parse (POST /api/imports)
  rows_processed  INT NOT NULL DEFAULT 0,
  success_count   INT NOT NULL DEFAULT 0,
  skipped_count   INT NOT NULL DEFAULT 0,
  error_count     INT NOT NULL DEFAULT 0,
  error_message   TEXT,                   -- whole-job failure message (parse errors etc.)
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  started_at      TIMESTAMPTZ,
  completed_at    TIMESTAMPTZ
);

CREATE INDEX import_jobs_tenant_created ON import_jobs (tenant_id, created_at DESC);
```

### `import_job_results`

```sql
CREATE TABLE import_job_results (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  import_job_id   UUID NOT NULL REFERENCES import_jobs(id) ON DELETE CASCADE,
  tenant_id       UUID NOT NULL,          -- denormalised for RLS
  row_number      INT NOT NULL,
  status          TEXT NOT NULL CHECK (status IN ('success', 'skipped', 'error')),
  message         TEXT,                   -- human-readable reason for skipped/error
  original_data   TEXT,                   -- raw CSV row (for error download)
  entity_id       UUID,                   -- created customer_id or invoice_id if success
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX import_job_results_job ON import_job_results (import_job_id, row_number);
CREATE INDEX import_job_results_errors ON import_job_results (import_job_id)
  WHERE status IN ('skipped', 'error');
```

**Retention:** `import_job_results` rows are purged after 90 days by the `data-retention-purge` cron (spec 28). `import_jobs` rows are also purged after 90 days; R2 objects are deleted in the same cron run.

---

## R2 Storage Convention

```
{tenantId}/imports/{uuid}-{sanitised-filename}
```

- `tenantId`: UUID string (no dashes stripped — uses canonical UUID format).
- `uuid`: `crypto.randomUUID()` generated at presign time.
- `sanitised-filename`: original filename lowercased, spaces replaced with `-`, non-alphanumeric characters (except `.` and `-`) stripped.

Example: `f47ac10b-58cc-4372-a567-0e02b2c3d479/imports/550e8400-e29b-41d4-a716-446655440000-customers.csv`

**Lifecycle:** R2 objects are NOT auto-deleted by R2 lifecycle rules. They are explicitly deleted by the `data-retention-purge` cron after 90 days, using the `r2_key` stored in `import_jobs`.

---

## Queue: `import.process`

### Binding name

`IMPORT_QUEUE` (`[[queues.producers]]` + `[[queues.consumers]]` in `wrangler.toml`).

### Job payload

```json
{
  "import_job_id": "uuid",
  "tenant_id": "uuid",
  "type": "customers",
  "r2_key": "f47ac10b-…/imports/550e8400-…-customers.csv",
  "column_mapping": { "Full Name": "name", "Email Address": "email" }
}
```

### Worker handler (`import.process` consumer)

```
import_worker.processImport(message):

1. Fetch job from DB. If status ≠ 'processing' → ack and return (idempotent).
2. Stream file from R2 using R2.get(r2_key).
3. Parse CSV (or XLSX for customers):
     - Use papaparse (CSV) or xlsx (XLSX, customers only) npm packages in Worker bundle.
     - On parse failure: update import_jobs.status = 'failed', error_message = parse error,
       send import_completed notification, ack message, return.
4. Apply column_mapping to transform each row → typed object.
5. For each data row (skip header):
   a. Run validation rules (see §Validation Rules).
   b. Open a Neon DB transaction:
      - If valid:
          - customers: INSERT INTO customers … ON CONFLICT (tenant_id, email) DO NOTHING
            returning id. If no row returned → treat as 'skipped' (duplicate email).
          - invoices: upsert customer if needed; accumulate line items; commit invoice +
            invoice_lines when the invoice group ends.
          - INSERT INTO audit_log … (spec 28 cross-cutting constraint).
          - INSERT INTO import_job_results (status='success', entity_id=…).
          - UPDATE import_jobs SET rows_processed = rows_processed + 1,
            success_count = success_count + 1.
      - If skipped:
          - INSERT INTO import_job_results (status='skipped', message=…, original_data=…).
          - UPDATE import_jobs SET rows_processed + 1, skipped_count + 1.
      - If error:
          - INSERT INTO import_job_results (status='error', message=…, original_data=…).
          - UPDATE import_jobs SET rows_processed + 1, error_count + 1.
      - Commit transaction.
   c. Each row is an independent transaction. A failing row does not roll back
      previously committed rows.
6. After all rows processed:
   - UPDATE import_jobs SET status='completed', completed_at=now().
   - Enqueue in-app notification: type='import_completed', payload={
       import_job_id, type, success_count, skipped_count, error_count }.
   - Ack message.
7. On unhandled Worker exception:
   - UPDATE import_jobs SET status='failed', error_message=<exception message>.
   - Ack message (do not retry the whole job — partial results already committed).
```

**Invoice line-item accumulation detail:**

The worker reads all rows into memory (bounded by 5,000 row limit × average row size ≈ well within Worker memory limit of 128 MB). It groups rows by `invoice_number` + `customer_name`/`customer_email` key before processing. Each invoice group is committed atomically (`invoices` + `invoice_lines` in one transaction). If any line within the group fails validation, the entire invoice group is marked as error (one `import_job_results` row per invoice group, not per CSV row).

**Invoice row counter:** `import_jobs.total_rows` reflects CSV data rows (not invoice groups). Progress bar advances per CSV row processed.

---

## Cloudflare Bindings

This spec adds the following to the Foundation Deltas (spec 1 `00-index.md`):

| Binding | Type | Purpose |
|---------|------|---------|
| `IMPORT_QUEUE` | Cloudflare Queue (producer + consumer) | Async CSV/XLSX import processing |

No new secrets. R2 bucket is the existing `STORAGE` bucket (spec 1).

---

## Design Decisions

| Decision | Choice | Rationale |
|----------|--------|-----------|
| Async queue vs. synchronous processing | **Async via Cloudflare Queue** | Workers have a 30s CPU wall time limit. 5,000 rows × DB transaction overhead can exceed 30s. Queue consumer has a 15-minute execution limit, sufficient for 5,000 rows. |
| Partial success vs. all-or-nothing | **Partial success** | Real-world import data is always messy. Rolling back 499 good rows because row 500 has a missing field creates a poor UX. Users want to land as much data as possible and fix edge cases manually. |
| CSV template download | **Static Worker response, not R2 signed URL** | Template content is static and not tenant-specific. Serving from a Worker response avoids R2 egress cost and simplifies CDN caching (Cache-Control: public, max-age=3600). |
| XLSX for invoices: v1 excluded | **CSV only for invoices** | Invoice line items require multi-row representation in flat files. XLSX's multi-sheet capability could simplify this, but adds significant parsing complexity (line-item sheet, header sheet). Defer to v2 after validating the CSV multi-row continuation pattern with real user data. |
| Column mapping client-side vs. server-side | **Client-side auto-detect, server-side apply** | Snappier UX (no round-trip for alias matching). The alias table is small and static — safe to ship in the frontend bundle. The actual mapped data transformation happens server-side in the Worker where it's auditable. |
| Invoice group commit unit | **Per invoice group (all lines atomic)** | An invoice with 3 line items is logically one entity. Committing 2 lines and failing the 3rd would produce a corrupted invoice. The group-level error is more actionable for the user. |
| Error report format | **Downloadable CSV** | Matches the user's existing workflow (they imported from CSV; they expect to fix errors in CSV). Inline error display for 5,000 rows is not practical in the UI. |
| Polling interval | **3 seconds** | Low enough for perceived responsiveness on a 500-row import (~10–15s total). High enough to avoid hammering the Neon connection pool. WebSocket upgrade deferred to v2 (adds Durable Object complexity; not warranted for this use-case). |
| R2 object deletion | **Explicit cron, not R2 lifecycle** | Allows 90-day retention parity with `import_job_results`. R2 lifecycle rules operate on wall-clock time from upload, not from job completion; this creates potential data availability gaps during the retention window. |

---

## Foundation Deltas

**New queue (append to `00-index.md` Queues table):**

| Queue | Consumer | Purpose | Added by |
|-------|----------|---------|----------|
| `import.process` | import Worker | Row-by-row async CSV/XLSX import; partial success; results to `import_job_results` | spec 40 |

**New tables (append to `00-index.md` Tables table):**

| Table | Owner spec | Notes |
|-------|-----------|-------|
| `import_jobs` | spec 40 | Tracks upload, mapping, status, row counts per import job |
| `import_job_results` | spec 40 | Per-row result (success/skipped/error); purged after 90 days |

**Tier entitlement (append to `foundation-auth-rbac.md` entitlements table):**

| Feature | Freelancer | Business | Enterprise | White Label |
|---------|-----------|---------|-----------|------------|
| Data import (CSV/XLSX) | ✗ locked | ✓ | ✓ | ✓ |