OSPulse

Export data to a warehouse

OSPulse answers supply-chain questions well. It does not know your headcount, your incident tickets, or which team owns which service. This guide covers pushing OSPulse datasets into your own warehouse so you can join them against everything else you hold — plus the lighter alternatives when a full pipeline is more than you need.

When a warehouse export is the right tool

Four reasons come up repeatedly:

  • BI and executive reporting. Your board deck is built in your BI tool, not in ours. Exported datasets slot into the models you already have.
  • Custom dashboards. Any cut of the data we did not anticipate — exposure by cost centre, drift by team, remediation velocity per squad — is a query once the data is local.
  • Long-term retention. Platform retention is configurable (GET /api/v1/settings/retention) but finite. A warehouse copy is yours for as long as you keep it.
  • Joining with internal data. The highest-value questions are joins: which exposed repositories are in scope for the audit, which owners have the most overdue findings.

If none of those apply, skip to lighter alternatives at the end — scheduled reports or the analytics endpoints will cost you far less to operate.

The schema contract

Before configuring anything, read the contract. It is versioned, discoverable, and the thing your downstream queries actually depend on.

bash
curl https://api.ospulse.app/api/v1/analytics/export/schema \
  -H "Authorization: Bearer $OSPULSE_TOKEN"
json
{
  "contractVersion": "1.0.0",
  "changelogUrl": "…",
  "datasets": [
    {
      "kind": "Dependencies",
      "tableName": "dependencies",
      "columns": [
        { "name": "repository_id", "type": "String", "isKey": true, "isNullable": false },
        { "name": "package_id", "type": "String", "isKey": true, "isNullable": false },
        { "name": "declared_version", "type": "String", "isKey": true, "isNullable": false },
        { "name": "ecosystem", "type": "String", "isKey": false, "isNullable": false },
        { "name": "package_name", "type": "String", "isKey": false, "isNullable": false },
        { "name": "resolved_version", "type": "String", "isKey": false, "isNullable": true },
        { "name": "is_direct", "type": "Bool", "isKey": false, "isNullable": false },
        { "name": "first_seen_at", "type": "Timestamp", "isKey": false, "isNullable": false }
      ]
    }
  ]
}

Eight datasets are available. Each is one flat table, keyed for merge-by-key, and joinable across datasets on repository_id and package_id:

kindTableContents
RepositoriesrepositoriesEnrolled repositories, provider, default branch, last scan
DependenciesdependenciesResolved graph edges, direct and transitive
VulnerabilityExposuresvulnerability_exposuresExposure rows with CVE, severity, CVSS, EPSS, KEV flag
LicenceVerdictslicence_verdictsPer-version licence expression and policy verdict
HealthScoreshealth_scoresPackage health score snapshots with confidence
PqcFindingspqc_findingsPost-quantum classification per package version
CraFindingscra_findingsCRA classification per component, per product
PolicyExceptionspolicy_exceptionsGranted exceptions with justification and state

Every table carries tenant_id and _contract_version. Timestamps are UTC. Actor references are pseudonymised as opaque *_ref values — the export never ships user identities into your warehouse.

Version your queries against the contract

Additive columns are a minor version bump; a drop, rename, or retype is a major bump with a changelog entry. Pin your transformations to _contract_version and you get a clean signal when something breaks rather than a silently wrong dashboard.

Configure a destination

Create the destination

A destination pairs a warehouse endpoint with a dataset selection, a serialisation format, and an optional schedule.

namestringRequired

Human-readable label.

warehouseTypestringRequired

Snowflake, BigQuery, Synapse, or LocalSql (a Postgres stand-in for development and CI).

endpointConfigJsonstringRequired

Warehouse-specific connection configuration, serialised as a JSON string.

grantReferencestringRequired

Your reference for the credential grant. The secret itself is never stored in this field.

selectedDatasetsstring[]Required

One or more kind values from the table above. At least one is required.

formatstringRequired

Parquet (default choice) or Csv.

scheduleCronstringOptional

Cron expression for scheduled runs. Omit for manual-only.

grantMaterialBase64stringOptional

Base64-encoded credential material. Write-only — never returned by any endpoint.

bash
curl -X POST https://api.ospulse.app/api/v1/analytics/export/destinations \
  -H "Authorization: Bearer $OSPULSE_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "analytics-snowflake-prod",
    "warehouseType": "Snowflake",
    "endpointConfigJson": "{\"account\":\"ex12345.eu-west-1\",\"database\":\"SECURITY\",\"schema\":\"OSPULSE\",\"warehouse\":\"LOAD_WH\"}",
    "grantReference": "kv://ospulse/snowflake-loader",
    "selectedDatasets": ["Repositories", "Dependencies", "VulnerabilityExposures"],
    "format": "Parquet",
    "scheduleCron": "0 3 * * *",
    "grantMaterialBase64": "…"
  }'
json
{
  "id": "e5a71c30-8b64-4d29-91f7-0c3e6a84b2d5",
  "name": "analytics-snowflake-prod",
  "warehouseType": "Snowflake",
  "endpointConfigJson": "{\"account\":\"ex12345.eu-west-1\",…}",
  "selectedDatasets": ["Repositories", "Dependencies", "VulnerabilityExposures"],
  "format": "Parquet",
  "scheduleCron": "0 3 * * *",
  "enabled": true,
  "createdAtUtc": "2026-07-28T11:04:18Z",
  "updatedAtUtc": "2026-07-28T11:04:18Z",
  "hasGrantMaterial": true
}

Note what is not in that response: no grant material, no secret, no credential. You get hasGrantMaterial as a boolean and nothing more.

Test the connection

Run the same validation a real run performs — SSRF checks and reachability — without writing anything.

bash
curl -X POST "https://api.ospulse.app/api/v1/analytics/export/destinations/$DEST_ID:test" \
  -H "Authorization: Bearer $OSPULSE_TOKEN"
json
{ "ok": true, "error": null }

A rejected endpoint — a private address range, an unresolvable host — comes back as 400 with an ExportEndpointRejected problem type at create time, so misconfiguration surfaces before you schedule anything.

Trigger a run

bash
curl -X POST "https://api.ospulse.app/api/v1/analytics/export/destinations/$DEST_ID:run?mode=full" \
  -H "Authorization: Bearer $OSPULSE_TOKEN"
json
{
  "id": "b93f07e2-51ac-4d8b-a604-7e21c9f530da",
  "destinationId": "e5a71c30-8b64-4d29-91f7-0c3e6a84b2d5",
  "trigger": "Manual",
  "status": "Pending",
  "mode": "Full",
  "schemaContractVersion": "1.0.0",
  "startedAtUtc": null,
  "finishedAtUtc": null,
  "error": null,
  "correlationId": "4c19a7f0-2d63-4b81-95ec-8f0a3d726b14"
}

mode is full or incremental; omit it and the service defaults to incremental. Only one run per destination may be in flight — a second trigger returns 409 with an ExportRunInFlight problem type rather than racing.

Read run history

bash
curl "https://api.ospulse.app/api/v1/analytics/export/destinations/$DEST_ID/runs?limit=10" \
  -H "Authorization: Bearer $OSPULSE_TOKEN"
json
[
  {
    "id": "b93f07e2-51ac-4d8b-a604-7e21c9f530da",
    "destinationId": "e5a71c30-8b64-4d29-91f7-0c3e6a84b2d5",
    "trigger": "Scheduled",
    "status": "Succeeded",
    "mode": "Incremental",
    "schemaContractVersion": "1.0.0",
    "startedAtUtc": "2026-07-28T03:00:04Z",
    "finishedAtUtc": "2026-07-28T03:02:41Z",
    "error": null,
    "correlationId": "4c19a7f0-2d63-4b81-95ec-8f0a3d726b14",
    "datasets": [
      { "kind": "Repositories", "rowCount": 142, "watermarkAdvancedTo": "2026-07-28T02:58:00Z" },
      { "kind": "Dependencies", "rowCount": 18734, "watermarkAdvancedTo": "2026-07-28T02:58:00Z" },
      { "kind": "VulnerabilityExposures", "rowCount": 903, "watermarkAdvancedTo": "2026-07-28T02:58:00Z" }
    ]
  }
]

limit defaults to 50 and is capped at 200. Single runs are readable directly from GET /api/v1/analytics/export/runs/{runId}. Per-dataset row counts and watermarks are what you reconcile against — if rowCount for a dataset is zero on an incremental run, nothing changed, which is a valid answer.

Scheduling and incremental patterns

scheduleCron drives recurring runs; those arrive with trigger: "Scheduled". A sensible default is a daily incremental with a periodic full re-seed.

How incremental works. Each dataset carries a watermark. An incremental run ships only rows past it and advances the watermark on success. Data lands in the target with a MERGE by natural key, so a re-run converges rather than duplicating.

Failure is safe. A failed run never advances the watermark. Replay it and an incremental resumes exactly from the last successful position:

bash
curl -X POST "https://api.ospulse.app/api/v1/analytics/export/runs/$RUN_ID:replay" \
  -H "Authorization: Bearer $OSPULSE_TOKEN"

Only a Failed run is replayable — anything else returns 409 with an ExportRunNotReplayable problem type. A replay is a fresh run against the same destination and mode, so it appears in history in its own right.

When to force a full run. Re-seed with mode=full after adding a dataset to selectedDatasets, after a major contract version bump, or if you have truncated the target tables. Use PATCH /api/v1/analytics/export/destinations/{id} to change the selection, schedule, format, or enabled flag; DELETE removes the destination together with its grant reference and material.

A worked example: exposure by owning team

The join that pays for the whole pipeline. With repositories, dependencies, and vulnerability_exposures landed, join them to your own service catalogue:

sql
SELECT
    t.team_name,
    COUNT(DISTINCT e.repository_id)                             AS affected_repos,
    COUNT(*)                                                    AS open_exposures,
    SUM(CASE WHEN e.known_exploited THEN 1 ELSE 0 END)          AS kev_exposures,
    ROUND(AVG(e.cvss), 2)                                       AS mean_cvss
FROM ospulse.vulnerability_exposures e
JOIN ospulse.repositories           r ON r.repository_id = e.repository_id
JOIN internal.service_catalogue     t ON t.repo_slug     = r.name
WHERE e._contract_version LIKE '1.%'
  AND e.severity IN ('High', 'Critical')
  AND r.is_active
GROUP BY t.team_name
ORDER BY kev_exposures DESC, open_exposures DESC;

known_exploited is the column to sort on. Counting critical CVEs produces a long list nobody actions; counting exploited ones produces a short list somebody will.

One-off tenant archive

For a point-in-time archive of everything rather than a recurring analytical feed:

bash
curl -X POST https://api.ospulse.app/api/v1/tenants/current/data-export \
  -H "Authorization: Bearer $OSPULSE_TOKEN"
json
{
  "exportId": "d0c47b91-3fa2-4e86-9517-b6082ea3c74f",
  "blobUri": "tenant-exports/…/d0c47b913fa24e869517b6082ea3c74f.json",
  "integrityHash": "1b8e0f37c2a9…",
  "bytes": 41582236,
  "rowCounts": {
    "repositories": 142,
    "dependencies": 18734,
    "vulnerabilityExposures": 903
  },
  "generatedAtUtc": "2026-07-28T11:22:07Z"
}

This is the right tool for a migration, a retention snapshot, or a data-portability request — not for a nightly feed. integrityHash and rowCounts let you verify the archive is complete before you rely on it.

Lighter alternatives

A warehouse pipeline is real infrastructure. Two cheaper options cover most reporting needs.

Scheduled reports. Define a report once, generate it on a schedule or on demand, and download the artefact:

bash
curl -X POST https://api.ospulse.app/api/v1/reports \
  -H "Authorization: Bearer $OSPULSE_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Monthly exposure summary",
    "reportType": "ExposureSummary",
    "format": "pdf",
    "scheduleCron": "0 6 1 * *",
    "recipientsJson": "[\"security@example.com\"]",
    "filtersJson": "{\"severity\":[\"High\",\"Critical\"]}",
    "enabled": true
  }'

POST /api/v1/reports/{reportId}/generate runs it now; GET /api/v1/generated-reports lists what has been produced, and GET /api/v1/generated-reports/{generatedReportId}/download fetches the file. Generated reports carry an integrityHash and a status, and a download before generation completes returns 409.

Analytics endpoints. For dashboards you want live rather than warehoused, GET /api/v1/analytics/trends returns the tenant trend series and GET /api/v1/analytics/trends/{day}/contributors explains a specific day's movement. GET /api/v1/analytics/benchmark compares you against the anonymised cohort — opt in first via PUT /api/v1/analytics/benchmark/opt-in. For a periodic narrative, POST /api/v1/analytics/quarterly-summary queues one and the matching /download route returns it once ready.

Next steps

Pagination

Walk large collections when polling the API directly.

CRA compliance workflow

The source of the cra_findings dataset.

SSO & SCIM provisioning

The other half of enterprise rollout.