Import your data into TimeRetain

Convert the attached file to TimeRetain v0.12 JSON using the specification below. Use deterministic IDs derived from each source row's stable key so re-imports update existing entries instead of duplicating them. The resulting file is imported in TimeRetain through Data & Sync -> Import.

## TimeRetain v0.12 JSON import

Use this specification when converting external time tracking data into a TimeRetain JSON file.

## Conversion recipe

1. Choose a stable external row key from the source file, such as an original row ID. If none exists, build one from stable source columns like date, start time, end time, client, project, task, and description.
2. Derive TimeRetain IDs deterministically from that external key: `first 16 bytes of SHA-256(utf8(externalId))`, encoded as URL-safe Base64 without padding. Use distinct seeds for related rows, such as `stopwatch:<externalId>`, `interval:<externalId>`, `timeline:<client>/<project>`, `tag:<tagName>`, and `tagStopwatch:<externalId>:<tagName>`.
3. Set the envelope `version` to `"0.12"` and `timestamp` to an ISO-8601 string.
4. Include all seven arrays inside `data` in this exact order: `interval`, `preferences`, `stopwatch`, `tag`, `timeline`, `tagStopwatch`, `exportOptions`. Arrays may be empty except `preferences` and `timeline`. `exportOptions` is usually `[]`.
5. Model clients, projects, workspaces, accounts, and other grouped billing or reporting dimensions as `timeline` rows. Group related timelines under broader timelines, and set each stopwatch's `timelineId` to the best matching specific timeline.
6. Model cross-cutting activity labels like meetings, coding, writing, admin, research, and support as flat `tag` rows. Tags cannot be grouped in v0.12.
7. Use Unix time in milliseconds for `interval.start` and `interval.end`. If the source uses local calendar dates or times, interpret them in the source's timezone before writing Unix milliseconds. Use `null` for `interval.end` only when the entry is still in progress.
8. Use `0` or `1` for every `Bool` field. Do not use JSON booleans.
9. Compute `checksum` as lowercase hex SHA-256 of `JSON.stringify(data)`, where `data` is the inner data object with the key order above. Do not include the envelope fields in the checksum.

Rows are upserted by `id`, so deterministic IDs make re-imports idempotent.

## Timeline rules

- Always include a default Personal timeline row if the source has no grouped client or project timelines.
- A timeline that is not grouped under another is already a billing boundary. Set `isBillingBoundary: 1` on a grouped timeline only when it can bill independently from related timelines.
- `parentTimelineId` points to the `timeline.id` this timeline is grouped under, or is `null` when it is not grouped. Do not create grouping cycles or references to missing timelines.
- Archived timelines are valid for history but hidden from new selection. Use `archivedAt: null` for normal imports.
- Archived tags are valid for history but hidden from new selection. Use `archivedAt: null` for normal imports.
- Put project/client rates on `timeline.hourlyRate` when they are rate defaults. Put the resolved per-entry rate on `stopwatch.hourlyRate`.
- When `stopwatch.hourlyRate` is non-null, set `rateCurrencyCodeAtWrite` to the import's workspace currency. Set both fields to `null` when the entry has no resolved rate.
- Use `timelineId: null` only when the entry intentionally belongs to No timeline.
- `preferences.defaultTimelineId` and `preferences.lastUsedTimelineId` must point to an existing, non-archived timeline, usually Personal.

## Types

- `Id` - 22-character URL-safe Base64 string using `A-Z`, `a-z`, `0-9`, `-`, and `_`, with no padding. It encodes 16 bytes.
- `Bool` - `0` or `1`, never `false` or `true`.
- Row timestamps (`interval.start`, `interval.end`) - Unix time in milliseconds. The envelope `timestamp` is an ISO-8601 string. All numbers must be finite.

## Envelope

```json
{
  "version": "0.12",
  "timestamp": "<ISO-8601 string>",
  "checksum": "<sha256 hex of JSON.stringify(data)>",
  "data": {
    "interval": [],
    "preferences": [],
    "stopwatch": [],
    "tag": [],
    "timeline": [],
    "tagStopwatch": [],
    "exportOptions": []
  }
}
```

All seven keys inside `data` must be present and appear in exactly this order: `interval`, `preferences`, `stopwatch`, `tag`, `timeline`, `tagStopwatch`, `exportOptions`.

## Schema

```ts
interval: {
  id: Id;
  stopwatchId: Id; // -> stopwatch.id
  start: number; // Unix ms
  end: number | null; // Unix ms, or null if in progress
}

preferences: {
  // exactly one row
  id: Id;
  useMilitaryTime: Bool;
  mergeOverlappingStopwatches: Bool; // legacy field; usually 0
  useAdjust: Bool;
  adjustDefault: number; // minutes; may be 0 or negative
  backupReminderIntervalInDays: number;
  currencyCode: string; // ISO 4217, for example "USD"
  earningsTrackingEnabled: Bool;
  defaultHourlyRate: number | null;
  weekStartsOn: number; // 0 = Sunday ... 6 = Saturday
  alternativeColor: string | null; // Open Color token, or null for gray
  defaultTimelineId: Id | null; // -> timeline.id
  lastUsedTimelineId: Id | null; // -> timeline.id
  forgottenRemindersMode: "both" | "start" | "stop" | "off";
}

stopwatch: {
  id: Id;
  description: string;
  adjustMinutes: number; // integer; may be negative
  isActive: Bool;
  isExpanded: Bool;
  hourlyRate: number | null; // stored rate for this stopwatch
  rateCurrencyCodeAtWrite: string | null; // workspace currency when rate was stored
  isHourlyRateManual: Bool | null;
  targetMinutes: number | null;
  timelineId: Id | null; // -> timeline.id; null means No timeline
}

tag: {
  id: Id;
  name: string;
  hourlyRate: number | null;
  color: string | null; // Open Color token like "blue.6", or null
  isPinned: Bool | null; // pinned in the tag list; null is treated as pinned
  archivedAt: number | null; // Unix ms when archived; null keeps the tag active
}

timeline: {
  id: Id;
  name: string;
  parentTimelineId: Id | null; // -> timeline.id grouped under, or null when not grouped
  hourlyRate: number | null;
  isBillingBoundary: Bool;
  archivedAt: number | null; // Unix ms when archived; null keeps the timeline active
}

tagStopwatch: {
  id: Id;
  tagId: Id; // -> tag.id
  stopwatchId: Id; // -> stopwatch.id
}

exportOptions: {
  // at most one row; most imports use an empty array instead
  // Prefer id "4WnR17vMjH50XmwttC218Q" (the app singleton). Other ids are
  // rewritten to that value on import.
  id: Id;
  format: "csv";
  explodeIntervals: 0 | 1;
  explodeTags: 0 | 1;
  durationBreakdown: 0 | 1;
  roundTarget: "off" | "interval" | "total";
  roundIncrement: "5" | "6" | "10" | "15" | "30" | "60";
  roundDirection: "nearest" | "up" | "down";
  timeColumns: "utc" | "split";
}
```

## Checksum

`envelope.checksum` is the lowercase hex SHA-256 digest of `JSON.stringify(data)`, where `data` is the inner object, not the whole envelope.

JavaScript:

```js
import { createHash } from "node:crypto";

const checksum = createHash("sha256")
  .update(JSON.stringify(data))
  .digest("hex");
```

Python:

```python
import hashlib
import json

payload = json.dumps(data, separators=(",", ":"), ensure_ascii=False)
checksum = hashlib.sha256(payload.encode("utf-8")).hexdigest()
```

## Minimal example shape

```json
{
  "version": "0.12",
  "timestamp": "2026-04-17T12:00:00.000Z",
  "checksum": "3149c7d5240911d6fe1112839e7511b43d4b8e93500a3173c309771769c83f3a",
  "data": {
    "interval": [
      {
        "id": "YpC0F378hvhs9cwPrPZqjA",
        "stopwatchId": "uHDqdpI_ehZ1fWTWH7I1dw",
        "start": 1763884800000,
        "end": 1763890200000
      }
    ],
    "preferences": [
      {
        "id": "NymWULO4G6GjGMp6_e5MOA",
        "useMilitaryTime": 0,
        "mergeOverlappingStopwatches": 0,
        "useAdjust": 0,
        "adjustDefault": 0,
        "backupReminderIntervalInDays": 7,
        "currencyCode": "USD",
        "earningsTrackingEnabled": 1,
        "defaultHourlyRate": null,
        "weekStartsOn": 1,
        "alternativeColor": null,
        "defaultTimelineId": "nM-FuMhKJ-Qc34ihF_5GAQ",
        "lastUsedTimelineId": "nM-FuMhKJ-Qc34ihF_5GAQ",
        "forgottenRemindersMode": "both"
      }
    ],
    "stopwatch": [
      {
        "id": "uHDqdpI_ehZ1fWTWH7I1dw",
        "description": "Client work",
        "adjustMinutes": 0,
        "isActive": 0,
        "isExpanded": 0,
        "hourlyRate": 125,
        "rateCurrencyCodeAtWrite": "USD",
        "isHourlyRateManual": 0,
        "targetMinutes": null,
        "timelineId": "C_MCKuU1uvIkXqlM2cuvvA"
      }
    ],
    "tag": [
      {
        "id": "-PpIeZugec_a_57XR7os3A",
        "name": "writing",
        "hourlyRate": null,
        "color": null,
        "isPinned": null,
        "archivedAt": null
      }
    ],
    "timeline": [
      {
        "id": "nM-FuMhKJ-Qc34ihF_5GAQ",
        "name": "Personal",
        "parentTimelineId": null,
        "hourlyRate": null,
        "isBillingBoundary": 0,
        "archivedAt": null
      },
      {
        "id": "C_MCKuU1uvIkXqlM2cuvvA",
        "name": "Client A",
        "parentTimelineId": null,
        "hourlyRate": 125,
        "isBillingBoundary": 0,
        "archivedAt": null
      }
    ],
    "tagStopwatch": [
      {
        "id": "Z5XfUVYDOcXMuFH02iHMxQ",
        "tagId": "-PpIeZugec_a_57XR7os3A",
        "stopwatchId": "uHDqdpI_ehZ1fWTWH7I1dw"
      }
    ],
    "exportOptions": []
  }
}
```

## Common mistakes

- Using `true` or `false` instead of `0` or `1` for `Bool` fields.
- Using seconds instead of milliseconds for `interval.start` or `interval.end`.
- Using ISO strings for row timestamps. Only the envelope `timestamp` is an ISO string.
- Adding a `timezone` field to the envelope. Source timezone may be needed during conversion, but the TimeRetain import stores row times as Unix milliseconds.
- Forgetting the checksum or computing it from the whole envelope instead of the inner `data` object.
- Pretty-printing or alphabetically sorting `data` before checksum calculation. Use the exact object key order shown above.
- Omitting one of the seven `data` arrays, even when it is empty.
- Grouping clients or projects with tags. Use `timeline` rows to group them under related timelines.
- Adding `tag.parentTagId`. Tags cannot be grouped in v0.11.
- Creating timelines with missing group targets or grouping cycles.
- Leaving `stopwatch.timelineId` unset when the source has a known client, project, account, or workspace.
- Creating tags without matching `tagStopwatch` rows for tagged stopwatches.