Data Export (Sales)

workspace.matters.ai/sales/export turns the nine fragmented lead stores into a single export surface. Any combination of sources goes into one file, columns are chosen either by hand or via a canonical cross-source schema, and a live preview shows the exact rows before anything is downloaded.

The console is one configuration object driving two outcomes — preview and download — so the preview cannot drift from the file: both call the same endpoint and the same engine.

What changed (and why)#

The previous tool exported one collection at a time, with a fixed six-item field list that did not match any real document, and a withPII boolean the client chose for itself. The rewrite fixes all three:

Surface map#

apps/workspace/app/(workspace)/sales/export/
  page.tsx                       console shell (client)
  components/
    useExportConsole.ts          all state + fetch orchestration
    primitives.tsx               themed Panel / CheckRow / SegmentedControl / inputs
    SourcePanel.tsx              multi-select sources + live row counts
    FilterPanel.tsx              date presets, range, status, search
    FieldPanel.tsx               column strategy + custom picker (ordered)
    OutputPanel.tsx              layout, format, PII, row options
    PreviewPanel.tsx             stats, warnings, the actual first N rows
    PresetsPanel.tsx             presets + history

apps/workspace/app/api/workspace/sales/export/
  route.ts                       POST — preview or download
  schema/route.ts                GET  — field discovery
  presets/route.ts               GET / POST
  presets/[id]/route.ts          PATCH / DELETE
  history/route.ts               GET  — recent runs from the audit trail

lib/services/sales/
  export.types.ts                shared contract (client + server)
  export.service.ts              the engine
  export-request.schema.ts       zod contract + legacy-body normaliser
  export-schema.service.ts       field discovery
  export-serializer.ts           result → bytes
  export-xlsx.ts                 workbook writer (exceljs, dynamically imported)
  export-presets.service.ts      saved recipes
  export-history.service.ts      audit-trail reader
  lead-field-map.ts              canonical schema + alias table
  lead-normalize.ts              flatten / canonicalise / PII policy
  lead-sampler.ts                cached per-collection schema sampling
  lead-collections.ts            the source registry (unchanged)

The canonical schema#

Lead stores grew independently. A demo request calls it full_name, a webinar registration name, a careers application firstName + lastName. Union those raw and a merged CSV gets three half-empty name columns.

lead-field-map.ts declares one canonical vocabulary and, per field, the aliases that feed it in priority order:

{
  key: "email",
  label: "Email",
  aliases: ["email", "email_address", "emailAddress", "work_email", ...],
  pii: true,
  mask: "email",
  searchable: true,
  type: "string",
}

Resolution rules (see toCanonicalRow):

1. The first alias present and non-blank on the flattened document wins.
2. If no top-level alias hits, the alias is matched against the leaf segment of every dot path — so leadJourney.firstTouch.utm_campaign resolves campaign.
3. name is composed from firstName + lastName when no source carries a full name.
4. createdAt is coerced to an ISO instant regardless of how the source stored it (Date, epoch number, or string).
5. A field that did not resolve is omitted, not set to null — callers can tell "absent" from "empty".

Field discovery#

GET /api/workspace/sales/export/schema?sources=a,b,c

For each selected source, lead-sampler.ts reads the newest 150 documents via the always-indexed _id sort, flattens them to dot paths, and records per-key presence, observed types, and a first non-empty sample. Results are cached for 5 minutes so picker interactions do not re-scan.

export-schema.service.ts then merges those samples into one catalogue:

• canonical fields lead, in declaration order, with the presence of whichever alias each source actually uses;
• raw keys follow, ordered by how many sources carry them;
• every field reports sources: [{ id, presence }] so the picker can show 3/5 src · 92% and an operator can spot a column that will be mostly empty before exporting it;
• sample values for PII columns are masked server-side unless the caller holds org:observability:pii_view — the picker must never become a PII side-channel.

A source whose database is unreachable comes back with error set (never cached) so the UI greys it out instead of failing the whole request.

Column strategies#

canonical and custom read from a canonical overlay (canonical values win where they resolved), so a picked email column is populated whether the source spells it email, work_email, or contact_email. all stays strictly raw so "everything this source stores" means exactly that.

options.excludeFields is applied after the strategy — the escape hatch for "everything except these three noisy keys".

Worked example: name + email, plus whatever else#

The most common ask — "email and name always, then a few more columns, across most of the sources":

1. Data sources — tick the seven you want, or hit Select all and untick the rest. There is no limit; they all go into one file.
2. Filters — Lifetime for everything, or a preset window.
3. Columns → Custom — tick Email, Full name, then Company, Source, Asset / event, Created at, or whatever else you need. The order you tick them is the column order, and the ↑/↓ arrows on the selected list change it.
4. Output → Personal data → Mask or Include. ← This is the step people miss.
5. Optional: Deduplicate → One row per email (the same person often appears on several sources) and Require email to drop anonymous rows.

Verified live on 2026-09-07 with those seven sources — header row:

Data source,Data source name,Full name,Email,Company,Source,Asset / event,Created at

whitepaper_leads reports "no rows matched" because that database has no collection yet; the other six deliver, and cross-source dedupe on email is significant (40 of 85 rows collapsed in that run).

Combine strategies#

A CSV is a single table, so per_source CSV can only be a sectioned file. XLSX is the format that expresses per-source separation natively, and the console says so inline rather than letting someone discover it after the download.

Query construction#

Per source, buildQuery:

• Date range — $or across every timestamp alias the collection actually carries (discovered by sampling), and for each, across both the Date and the ISO-string encoding, because older records in these stores were written as strings. Boundaries are YYYY-MM-DD resolved at UTC day edges, so the same range means the same rows regardless of who runs it from where. A source with no timestamp field raises a warning rather than silently returning everything.
• Status — $or across the status aliases present.
• Search — case-insensitive regex (input escaped) across the canonical searchable aliases present.

Sorting is done in memory, deliberately#

The DB sorts on _id — time-ordered and always indexed. Sorting on a timestamp alias would table-scan, because none of the six spellings is reliably indexed across nine collections. The merge step then re-sorts the (already bounded) result set on the canonical createdAt; rows with no resolvable date sort last so they never displace real data at the top.

PII policy#

Three modes, and the request only asks:

resolvePiiMode(requested, cleared) downgrades an uncleared include to mask, not to omit: the operator explicitly wanted that column, and a masked column still supports counting and eyeballing without disclosing an identity. The downgrade is surfaced in the preview warnings and in stats.piiDowngraded.

Secrets are not PII and are never exportable at any clearance. isSecretKey drops password, token, api_key, signature, otp, hash, salt, session_id and unsubscribe* at flatten time, before a column can exist.

Row options#

Limits#

Truncation is always reported — per source (stats.sources[].truncated) and overall (stats.truncated), plus a human-readable warning.

Preview#

POST /api/workspace/sales/export with preview: true runs the same pipeline with the smaller fetch cap and returns:

{
  success: true,
  stats: ExportStats,          // per-source matched/exported, dedupe, PII, truncation
  columns: ExportColumn[],     // resolved columns, in export order, PII-flagged
  rows: Record<string, unknown>[],
  estimatedBytes: number,      // projected from the sample
  warnings: string[],
}

stats.matched is exact (a real countDocuments). stats.totalExported and duplicatesRemoved are projections from the capped sample, flagged by stats.estimated and labelled "(est.)" in the UI. The console auto-refreshes the preview 700 ms after any configuration change while Live is on, aborting the in-flight request when the operator keeps typing.

A preview that returns unmasked PII is a disclosure in its own right, so it is written to the audit trail even though no file left the building. Non-PII previews are not audited — otherwise the history panel would drown in keystrokes.

Saved exports (re-downloadable artifacts)#

Every download is kept server-side so it can be taken again without re-running the query — which matters because re-running is not equivalent: the data moves, so a second run produces a different file. The console's Recent exports panel has two tabs: Saved (artifacts still on disk, one click to re-download) and Activity (the audit trail, which outlives the files).

Where the bytes live, and why#

GridFS, in the same cluster as the source records — bucket sales_exports (sales_exports.files + sales_exports.chunks in the mattersai database).

These artifacts are concentrated lead PII. A download has to be Clerk- and RBAC-gated whatever backs it, so a blob store or CDN buys nothing here: every byte still flows through an authenticated route. What it would add is a second system holding the same personal data, with its own credentials, retention policy and access log. Keeping artifacts in the cluster that already stores the source records adds no new data-residency surface, needs no provisioning to work, and lets retention use the same primitives as the rest of the module.

Clearance: an artifact does not inherit its creator's#

canDownloadArchived(pii, viewerPiiCleared) re-checks the downloader against the stored run's PII mode. A raw-PII artifact stays gated on the downloader holding org:observability:pii_view — otherwise re-downloading someone else's export would be a clean route around the check the generating run enforced. The list still shows the row (with downloadable: false and a lock icon) so the existence of the file is not itself a secret.

Re-downloads are audited exactly like original runs, with redownload: true and originallyBy — the point of the trail is who obtained the data, not who first generated it.

Retention#

Expiry is enforced twice: pruneArchive deletes past-window artifacts (and trims the owner back to the retained count) on every archive write, and listArchivedExports filters on expiry as well — so retention holds even if nobody has run an export since the window closed.

Failure behaviour#

Archiving happens after the artifact is built and immediately before it is returned, and archiveExport never throws. A storage failure is logged, reported in the response summary as { archived: false, reason }, and recorded in the audit metadata — but the operator still gets their download. An export must never fail because the convenience copy could not be written.

Endpoints#

An expired artifact returns 404, not 403: it should read as gone, not as forbidden.

The Leads Viewer shares this vocabulary#

/sales/leads reads through the same canonical map, for the same reason. It used to read row.email literally, so every Demo Request showed a blank email — 2,300+ leads, the biggest source — because that collection calls the field workEmail. Searching them by address matched nothing either: the service's hardcoded SEARCHABLE_FIELDS had the same blind spot. One missing alias, two bugs, both invisible unless you knew the data.

getLeads now returns each row as the raw document with the canonical values overlaid, so the table addresses row.email and gets a value whatever the source calls it, while the detail panel still shows everything stored.

Search, sort and filters are server-side#

Necessarily — the table paginates at 25 rows, so sorting or filtering only the visible page would be a lie.

An unsortable request falls back to _id rather than erroring, and the response echoes the applied sort so the header shows the right arrow.

Columns#

The API returns a per-collection catalogue and the viewer offers show/hide, remembered per collection in localStorage. Two flags, because they are not the same question:

• available — the collection can produce a value. Note this is not "has a physical key": name is composed from firstName + lastName when a source stores no single name field, which is what demo-requests does. Keying availability off the physical key alone hid the Name column on the biggest source even though every row had a value.
• sortable — a single physical key backs it, so MongoDB can order by it. A composed column renders as plain text with no sort control rather than a button that silently does nothing.

PII is now masked, not blanked#

An uncleared viewer used to get a column of dashes, which made the page useless. It now gets es****@matters.ai and E. T. — enough to tell two rows apart and match a support ticket, not enough to identify anyone. This also closed a latent hole: the old substring scrubber would have let a bare canonical name key through, because "name" does not contain "firstname". The route uses applyPiiPolicy, which classifies by canonical field.

Hand-off from the Leads Viewer#

The Leads Viewer's Export these button links to /sales/export?sources=<id>&search=<q>, so "I want these as a file" is one click rather than re-picking the source and retyping the search.

configFromSearchParams() seeds the console from the query once, on mount — later edits belong to the operator, not the URL. Accepted params:

Presets#

Stored in mattersai.sales_export_presets. A preset holds the request shape, never rows — replaying it re-runs the query under the replayer's clearance, so a preset authored with PII clearance still yields masked columns for someone without it.

• shared: true grants read to everyone with export clearance; only the author can edit or delete.
• preview / previewLimit describe one interaction and are stripped before saving.
• 50 presets per operator.

Scheduled exports#

Schedules in the console: save the current configuration with a cadence and recipients, and the export runs itself. Presets removed the re-picking; a schedule removes the remembering.

Execution is a Vercel cron in the root app — /api/cron/sales-exports, registered hourly in vercel.json. Hourly is the right tick because the finest cadence a schedule can express is an hour-of-day, so every schedule fires inside its own slot. Management lives in the workspace app (/api/workspace/sales/export/schedules); only the records are managed there, never the run.

Three rules that are security decisions, not conveniences#

A scheduled run never produces raw PII. There is no interactive user at 07:00, so honouring pii: "include" would mean persisting the creator's clearance and replaying it — and it would keep working after they lose it. The schema rejects include on write, the runner clamps it again for any schedule that predates the rule, and the runner passes piiCleared: false so the engine itself cannot be talked into it. Raw PII stays a live, attributable act.

The email never carries the file. Mail is not a confidential channel and an attachment leaves the org the moment someone forwards it. The run archives its artifact and the email links to the console's Saved tab, so the recipient authenticates and passes the same clearance check as any other download. The message carries no lead data at all — a name, a row count, a format, a link.

Recipients must be on the internal email allowlist (ALLOWED_EMAIL_DOMAINS). A schedule that could mail a lead extract to an arbitrary address is an exfiltration primitive with a cron attached. Every rejected address is named in the error so the operator can fix them all at once.

Cadence#

Daily, weekly (with a weekday) or monthly (with a day of month), each at an hour-of-day. computeNextRun computes everything with UTC accessors and the UI says "UTC" rather than pretending otherwise — a schedule that drifted with the viewer's timezone would fire at a different wall-clock hour depending on who last opened the page, and would skip or double-fire across a DST boundary. The suite runs under Australia/Adelaide (+09:30, observes DST) specifically to keep that honest.

Day of month is capped at 28: a schedule set to the 31st would silently skip February.

Running#

Every scheduled run writes the same sales.export audit event as a manual one, with scheduled: true and the schedule name, so the trail is uniform.

Interaction with archive retention#

A raw-PII artifact is kept 1 day and everything else 7 (see above). Since a schedule can never produce raw PII, its artifacts get the full 7 days — but a monthly schedule's link will expire well before the next run. That is intended: the email is a notification, not a permanent store. Re-run from the console if you need it again.

History#

GET …/history?scope=mine|team reads mattersai.audit_logs where eventType = "sales.export". No second store to keep in sync — the audit event a run already writes is the history record.

Security#

CSV formula injection#

A cell beginning =, +, -, @, TAB or CR is executable in Excel and Google Sheets. A lead who types =IMPORTXML(...) into a public form would otherwise run code on the analyst's machine. csv-converter.ts prefixes such cells with a single quote — the conventional inert marker — while leaving numeric-looking values (-5, +91 98765 43210) untouched so real numbers and phone strings survive. Header labels are never sanitised.

Backward compatibility#

The pre-multi-source body is still accepted and normalised by normalizeLegacyRequest: collection → one-element sources, withPII → the include PII request (still clearance-gated), excludeFields → options.excludeFields. A body that matches neither shape returns the new schema's issues, which is the more useful diagnostic.

Tests#

tests/sales-export/ — 179 tests. All but the jsdom file are pure (no database):

pnpm exec jest tests/sales-export

Extending#

A new data source — add one entry to lead-collections.ts. Discovery, the picker, the query builder and the exporter all pick it up with no further changes. Treat additions as a security-review item: the entry is what exposes the dataset.

Live inventory (verified 2026-09-04)#

contact_submissions was removed from the registry on 2026-09-07. It had no collection and no writer, and /contact turns out to be an informational page that routes enquiries to /demo — so those leads land in demo-requests. Listing it implied contact enquiries existed somewhere and were merely empty, which was misleading. Every remaining entry has a verified write path in this repo.

The timestamp field is not createdAt on most sources#

Probed 2026-09-07 — worth knowing before you write a query by hand:

All six spellings resolve through the canonical createdAt alias list, which is exactly why the exporter reads dates through toCanonicalRow rather than touching a field name directly.

A new canonical field — add one entry to CANONICAL_FIELDS. Order in that array is the column order in canonical exports.

A new output format — add the case to serializeExport plus its CONTENT_TYPES / EXTENSIONS entries, and one option in OutputPanel.

Known gaps#

• Exports are buffered, not streamed — hence the 50,000-row ceiling. Streaming CSV would lift it, at the cost of computing dedupe/sort incrementally.
• Saved artifacts are re-downloadable but not shareable — there is no signed link, by design. Anyone fetching one authenticates and passes the clearance check themselves.
• per_source CSV is sectioned rather than multi-file; a ZIP container would need a zip dependency in the workspace app (exceljs is the only archive-capable dep reachable today, and it produces XLSX).

Related#

• RBAC & Permissions — the clearances above
• Audit Log — where sales.export events land
• Lead Attribution — leadJourney is exported like any other field
• Workspace Modules