Skip to content

Data & Backup — Technical Reference

Unified data-portability capability: every local data domain (a LocalJsonStore collection) can be exported to a local, round-trippable .xlsx file and/or synced (opt-in, per domain) to the user's own Google Drive/Sheets. Local JSON stays the source of truth in every case — Excel and Drive are export/sync targets, never a second store the app reads back from on its own.

Friendlier version: ../plain/data-backup.md

Three tiers

  1. Local JSON (unchanged). LocalJsonStore (src/WorkWingman.Infrastructure/Services/LocalJsonStore.cs) remains the only thing the app reads at runtime. Nothing in this feature changes that.
  2. Local Excel export/import (ClosedXML). Any store collection → one worksheet. Round-trippable: export → the user opens and edits the .xlsx in Excel/LibreOffice/etc. → re-import writes the edited data back into the JSON store via LocalJsonStore.
  3. Google Drive/Sheets sync (opt-in, per domain). Reuses the existing GoogleSheetsClient / IGoogleTokenProvider — no new OAuth flow. Requires the user's Google connection; no-ops with a clear message when it isn't connected yet.

Store durability (LocalJsonStore hardening)

The JSON tier is deliberately EF/SQLite-free, so the store itself carries the durability guarantees a database would otherwise provide. All of this is internal to src/WorkWingman.Infrastructure/Services/LocalJsonStore.cs — the LoadAsync/SaveAsync API is unchanged, and the sidecar files below never appear in ListCollections() (and therefore never in a Data & Backup export).

  • Atomic saves. Every write stages to a uniquely-named name.json.<guid>.tmp, flushes to disk (Flush(flushToDisk: true)), then swaps into place with File.Replace. A process killed — or a machine powered off — mid-write can never leave a torn name.json; at worst it leaves temp-file litter, which the next save of that collection sweeps once it's old enough to be a genuine orphan (not another writer mid-swap).
  • Last-good backup. The swap keeps the previous good document as name.json.bak.
  • Corruption recovery. A name.json that fails to parse never throws to the caller: it is quarantined as name.json.corrupt-<utc-timestamp> (archived, never deleted), and the .bak is restored and returned if it parses — otherwise the collection simply reads as not-present. A corrupt .bak is quarantined the same way.
  • Concurrent access. In-process, a per-collection gate serializes writers on the same key while different collections proceed in parallel. Across processes (a second app instance), readers open with FileShare.ReadWrite | FileShare.Delete so a concurrent atomic swap never fails mid-read, and transient sharing violations get a bounded exponential-backoff retry (25→400 ms, 5 attempts) before the IOException is allowed to surface. Not-found errors are never retried — they're answers, not contention.

Why this is data-driven, not per-feature

Every past feature (profile, jobs, applications, documents, study plans…) would otherwise need its own export/import/sync code. Instead, one registry — BackupDomainRegistry (src/WorkWingman.Core/Models/BackupDomainRegistry.cs) — lists every LocalJsonStore collection key that should be visible to Data & Backup, plus whether it's sensitive. IDataPortabilityService (src/WorkWingman.Infrastructure/Services/DataPortabilityService.cs) and JsonTableConverter (src/WorkWingman.Infrastructure/Services/JsonTableConverter.cs) work generically over the JSON tree for any domain — they never touch a domain's C# model type. Adding a new domain is a one-entry addition to BackupDomainRegistry.All. No controller, service, or frontend code needs to change.

public class BackupDomain
{
    public required string Key { get; init; }               // LocalJsonStore collection name
    public required string DisplayName { get; init; }
    public required bool IsSensitive { get; init; }          // see "Sensitive domains" below
    public required BackupDomainShape Shape { get; init; }   // see "JsonTableConverter" below
    public string Description { get; init; } = "";
}

Shape is the domain's own declaration of what its LocalJsonStore collection looks like (List, SingleObject, Dictionary, or ScalarDictionary — see the next section). It's authoritative, never guessed at runtime: a domain that was never saved yet exports as an empty sheet with zero rows, which carries no signal of its own about whether it should come back as {} or [] on import. Without the registry stating the shape up front, a never-saved intake (a SingleObject) would round-trip as [] and break the next typed LoadAsync<IntakeProfile>.

Today's registry (see BackupDomainRegistry.All), matching the store keys used across ProfileService, JobQueueService, ApplicationTrackerService, DocumentService, StudyPlanService, ConnectionsService, and ApplicationRunService:

Key Domain Shape Sensitive
intake Onboarding intake profile (contact, eligibility, demographics, salary expectation/history, security clearance) SingleObject Yes
story Story / voice profile SingleObject No
jobs Job queue List No
applications Applications tracker List No
documents Generated documents Dictionary No
study-plans Study plans Dictionary No
settings Automation settings SingleObject No
fill-rules Form fill rules ScalarDictionary No

Only intake is sensitive today because that's the one domain that actually carries financial/ salary/demographic data (IntakeProfile.Eligibility.SalaryExpectation, IntakeProfile.Extended.SalaryHistory, IntakeProfile.Demographics, security clearance level). If a future domain adds health/benefits or equity-compensation data (see the overnight-mission worktrees for benefits, equity, comp-signals, fin-profile), mark its registry entry IsSensitive = true when it merges — that one boolean is the entire gate.

IDataPortabilityService

public interface IDataPortabilityService
{
    IReadOnlyList<BackupDomain> ListDomains();

    Task<ExcelExportResult> ExportToExcelAsync(
        IReadOnlyList<string> domainKeys, string path, string? passphrase = null, CancellationToken ct = default);

    Task<IReadOnlyList<DomainImportResult>> ImportFromExcelAsync(
        string path, string? passphrase = null, CancellationToken ct = default);

    Task<DriveSyncResult> SyncToDriveAsync(
        string domainKey, bool confirmSensitive = false, CancellationToken ct = default);
}
  • ExcelExportResult(string Path, IReadOnlyList<string> ExportedDomainKeys, bool Encrypted)
  • DomainImportResult(string DomainKey, bool Success, int RecordsWritten, string Message)
  • DriveSyncResult(string DomainKey, bool Synced, string Message, string? SpreadsheetUrl)

Implementation: DataPortabilityService(LocalJsonStore store, GoogleSheetsClient sheets, IGoogleTokenProvider tokens), registered as a singleton in Program.cs alongside the other domain services.

JsonTableConverter — the shape rules

LocalJsonStore collections aren't all shaped the same way. JsonTableConverter.ToTable/FromTable (src/WorkWingman.Infrastructure/Services/JsonTableConverter.cs) is the one generic conversion every domain goes through, keyed off its registry-declared BackupDomainShape:

  • List (a JSON array of objects — jobs, applications) → one row per element.
  • SingleObject (intake, story, settings) → exactly one row.
  • Dictionary (values are complex records, e.g. documents, study-plans) → one row per entry, with the dictionary key captured in a synthetic _key column so the mapping round-trips.
  • ScalarDictionary (values are plain strings — fill-rules, a Dictionary<string, string>) → one row per entry, always exactly two columns (_key, value). This is deliberately not flattened into one-column-per-rule-name: that would make the sheet's shape depend on which rules happen to exist, and two exports with different learned rules would produce workbooks with different columns.
  • Nested object properties flatten into dotted column names (e.g. contact.email, contact.address.city) — with one exception: a nested dictionary with arbitrary/dynamic keys (e.g. GeneratedDocuments.CopyPasteFields, keyed by whatever ATS field label was scraped) is not flattened if any of its keys contain a literal . — that would be indistinguishable from the dotted-column nesting separator on the way back in. Such a subtree collapses to a single JSON cell instead, same treatment as arrays, for the same reason.
  • Arrays (of primitives or of objects, e.g. topSkills, interviews) collapse to a single cell holding compact JSON text rather than flattening into columns, because a variable-length nested list has no fixed column shape. Re-import parses that cell back into the original JSON array. This is the one explicit "your call" decision the design brief flagged — documented here rather than silently done. A hand-edited cell with broken JSON is caught per-sheet on import (JsonException, FormatException, InvalidOperationException are all mapped to a clean DomainImportResult failure, never an unhandled 500).
  • A JSON null value renders as a distinguishable sentinel cell (NullSentinel, the Unicode "empty set" character ∅) so it re-imports as null, never confused with a legitimate empty string — most domains default optional text fields to "", not null (e.g. IntakeProfile.Contact.PreferredName), and the two must not collapse into each other. This matters for nullable properties like bool?/nullable nested objects (JobPosting.PostingSignal).
  • Every column's original JSON token kind is tracked (string/number/bool/JSON) in a hidden _ww_columns worksheet written alongside the domain sheets, so re-import never has to guess a cell's type from its text. Without this, a phone number, ZIP code, or a free-text answer of "true" would get silently reinterpreted as a JSON number/bool on import. Numbers are parsed with CultureInfo.InvariantCulture on both sides (matching System.Text.Json's own invariant serialization) so a workbook edited on a comma-decimal locale (e.g. de-DE) doesn't corrupt or reject decimal values.
  • Domain keys are matched case-insensitively against the registry, but export always uses the registry's canonical casing for the LocalJsonStore file read and the sheet name — so a caller passing "Jobs" still reads jobs.json and produces a sheet that resolves back to jobs on import. Duplicate/case-variant keys in one export request are de-duplicated rather than causing a worksheet-name collision.

Export

POST /api/data-backup/export — { domainKeys: string[], passphrase?: string } → streams the .xlsx back as a file download (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet). One worksheet per domain, named after the (sanitized) store key. Written with ClosedXML — header row frozen, columns auto-sized.

Import

POST /api/data-backup/import — multipart form (file, optional passphrase) → per-sheet DomainImportResult[]. For each worksheet:

  1. Resolve the sheet name back to a BackupDomain (unknown sheets are reported, not silently dropped or thrown on — the rest of the workbook still imports).
  2. Rebuild the JSON node via JsonTableConverter.FromTable, using the domain's registry-declared Shape (never guessed from the sheet's row count).
  3. Validate structurally (must parse to an object or array; malformed JSON in an array cell raises a caught, per-sheet failure rather than aborting the whole import).
  4. Write back through LocalJsonStore.SaveRawAsync — the same atomic stage-flush-swap path every typed SaveAsync caller uses (see "Store durability" below).

A corrupt or non-Excel upload (ClosedXML/OpenXml throw a variety of undocumented exception types for a bad file) is mapped to 400 Bad Request at the controller, never an unhandled 500 — the upload is user-controlled input, not server state.

This is genuinely round-trippable: export a domain, open it in Excel, edit a value, save, re-import — the edited value lands in the JSON store; everything else survives unchanged.

Local Excel encryption

The design brief's instruction was: "at minimum implement the local .xlsx encryption ClosedXML supports." Investigating that directly: ClosedXML does not implement file encryption-at-rest. Its IXLElementProtection/IXLProtectable API (Protect(password)) only sets the OOXML sheet/workbook structure-protection flags Excel's UI uses to grey out "lock cells while editing" — it does not encrypt any bytes, and the password is trivially strippable by anyone who edits the XML directly. Presenting that as "encryption" would be dishonest, so this feature does not use it.

Instead, when a passphrase is supplied, the finished .xlsx file's bytes are encrypted with AES-256-GCM, keyed via PBKDF2-HMAC-SHA256 (210,000 iterations — the 2023 OWASP-recommended floor) over the passphrase. See Aes256GcmFileCodec (src/WorkWingman.Infrastructure/Services/Aes256GcmFileCodec.cs).

On-disk envelope format: MAGIC(8 bytes "WWENCV1\0") | salt(16) | nonce(12) | tag(16) | ciphertext(N). The magic bytes let Aes256GcmFileCodec.IsEncrypted(path) recognize an encrypted export without needing the passphrase (a plain .xlsx starts with the ZIP local-file-header signature PK\x03\x04, which never matches). Wrong passphrase → AesGcm's built-in authentication tag check fails → InvalidOperationException("Wrong passphrase, or the file is corrupted.") — GCM is authenticated encryption, so silent bit-flipping corruption and a wrong key both surface the same way: a hard failure, never garbage data.

There is no passphrase recovery. Losing it means the encrypted export is unrecoverable — that's the same trade-off as any local encrypted archive (7-Zip AES-256, VeraCrypt, etc.).

The cloud story

Google Sheets has no client-side-encrypted cell type — a value written to a cell is stored and rendered by Google in plaintext. There is no way to sync "encrypted data" into a live-editable Sheet. Syncing to Drive/Sheets means the data leaves the machine as plaintext, subject to the user's own Google account security (2FA, Drive sharing settings, etc.) — same as pasting the data into any Google Sheet by hand. This is stated plainly in the UI (see below) and is why sensitive domains default to local-only rather than being encrypted-then-synced.

If a future iteration wants an encrypted cloud backup rather than a live Sheet, the same Aes256GcmFileCodec envelope could be uploaded as an opaque binary blob via Drive's file-upload API (not Sheets) — trading live in-Sheet editing for confidentiality. Not built; noted here as the documented path if it's ever wanted.

Sensitive-domain sync gating

SyncToDriveAsync throws InvalidOperationException for a sensitive domain unless confirmSensitive: true is passed. The controller (DataBackupController.SyncToDrive) turns that into an HTTP 409 Conflict — the request is well-formed, it's just refused pending the user explicitly confirming they've seen the "this leaves the machine" warning. The frontend shows that warning as a modal (see Frontend) before ever sending confirmSensitive: true.

Non-sensitive domains sync as soon as the user clicks "Sync to Drive" — no extra confirmation step, consistent with the existing Google-sync language already in ConnectionsService ("Drive folder … synced as backup").

Formula-injection guard on Drive sync

GoogleSheetsClient.AppendRowAsync writes with valueInputOption=USER_ENTERED — the same parsing Sheets applies to something a human typed into a cell. Left unguarded, a backed-up value that happens to start with =, +, -, or @ would be reinterpreted as a formula instead of stored as the literal local JSON text (the classic CSV/Sheets injection pattern — see the OWASP CSV Injection cheat sheet). Every cell SyncToDriveAsync sends is passed through EscapeForSheetsUserEntered, which prefixes a leading apostrophe when needed — Sheets' own "force text" convention, which strips the mark from display but never evaluates what follows as a formula.

Drive sync no-ops cleanly when Google isn't connected

SyncToDriveAsync calls IGoogleTokenProvider.GetAccessToken(GoogleScopes.Sheets) as a pre-check. Today that's UnconfiguredGoogleTokenProvider, which always throws (src/WorkWingman.Infrastructure/Clients/GoogleTokenProvider.cs) — the service catches that specific InvalidOperationException and returns DriveSyncResult(Synced: false, "Google isn't connected yet…") instead of letting the exception (or a downstream HTTP 401 from Google) surface. Once the real OAuth flow lands (see connections-and-sso.md), this same code path keeps working unchanged — a configured token provider simply won't throw, and the sync proceeds.

Endpoints

DataBackupController (src/WorkWingman.Api/Controllers/DataBackupController.cs), api/data-backup:

Method Route Body Notes
GET /domains — The full BackupDomain registry
POST /export { domainKeys, passphrase? } Returns the .xlsx as a file download
POST /import multipart: file, passphrase? Per-sheet DomainImportResult[]
POST /{domainKey}/sync { confirmSensitive? } DriveSyncResult; 409 if sensitive + unconfirmed

Frontend

frontend/src/app/features/data-backup/ (data-backup.ts / .html / .scss), routed at /data-backup, linked from the sidebar as "Data & Backup". Shows every domain in a table (name, description, a Sensitive badge for gated domains), checkboxes for a multi-domain export, an "Export all" shortcut, a passphrase field (applies to both export and import), a file input for import (with per-sheet result feedback), and a "Sync to Drive" button per row — disabled with a "Connect Google first" hint when the Connections screen reports Google as disconnected, and routed through a confirm modal for sensitive domains before the actual sync call fires.

Tests

tests/WorkWingman.Tests/:

  • JsonTableConverterTests.cs — the shape rules (list/object/dictionary/scalar-dictionary), flattening, array-cell collapse, dotted-key escaping, null-vs-empty-string preservation, culture-invariant number parsing, and round-trip identity for every shape.
  • Aes256GcmFileCodecTests.cs — encrypt/decrypt round-trip, wrong-passphrase failure, "not an encrypted file" detection.
  • DataPortabilityServiceTests.cs — export→import round-trip per shape (list, single object, dictionary, scalar dictionary), multi-domain export, encrypted round-trip, missing-passphrase and wrong-passphrase import failures, unknown-sheet and malformed-cell handling, case-insensitive domain keys, duplicate-key de-duplication, sensitive-domain gating (with and without confirm), non-sensitive no-confirm-needed sync, the Google-unconnected no-op, and the Sheets formula-injection escape (via Flurl.Http.Testing.HttpTest).
  • ApiSmokeTests.cs — the new /api/data-backup/* routes (200/400/404/409 as appropriate), including a corrupt-upload-returns-400 case.

Frontend: data-backup.spec.ts covers domain loading, select/select-all, export (including the "nothing selected" guard and failure path), import (including the "no file chosen" guard), the sensitive-domain confirm-dialog flow (open → confirm/cancel), and the Google-unconnected sync no-op.