Loam
Sign up

Analyse uploaded files with SQL

Create a private temporary Namespace, import multiple large CSV or Parquet files, query ordinary tables, then extend, delete, or keep the Namespace.

One temporary Namespace is the analysis workspace

File analysis uses the same Loam resources as retained customer data: one ordinary private Namespace, ordinary tables, standard ingestion runs, and the canonical SQL endpoint. Create the Namespace once, import any number of CSV or Parquet files as named tables, and join them with SQL. The Namespace itself is the complete analysis workspace.

The encoded-file service ceiling is 5 GiB(5,368,709,120 bytes), and a plan may set a lower limit. Loam chooses single PUT or multipart transfer in the returned standard upload DTO. Large inputs are schema-inferred and materialized with bounded streaming or range reads, so neither Loam nor a minisite Worker needs to buffer the whole source file.

Before you start

  1. Create a service account with the File analysis preset and grant it Admin on each parent Namespace where it may create private temporary Namespaces. The root preset includes file_analysis plus query so assigned humans can use the normal Loam UI; neither capability implies access to another Namespace.
  2. Create one child key for this site and environment with only file_analysis plus query, then restrict its Engine grants to the dedicated private parent subtree. The shared bootstrap handler uses query to resolve and verify that configured parent ancestry before creation; that capability remains effective inside the granted subtree. Keep the key and JWT outside browser JavaScript; exact-Namespace SQL uses the separate scoped analysis token instead.
  3. Decide where the scoped analysis token lives. A browser keeps it only in memory, a Worker may retain it server-side behind an application handle, and an MCP server keeps it in MCP session state.

1. Create a temporary Namespace

bash
curl -X POST "$DATA_PLANE_URL/v1/workspaces/$WORKSPACE_ID/namespaces" \
  -H "Authorization: Bearer $SERVICE_JWT" \
  -H "Content-Type: application/json" \
  -d '{
    "parentId": "rw-us-east1-node-0195a3c47d3e7ab18c4f123456789abf",
    "name": "visitor_analysis",
    "isSearchDiscoverable": false,
    "lease": { "expiresInSeconds": 86400 }
  }'

The ordinary Namespace request uses parentId, name, and an optional lease.expiresInSeconds. Set isSearchDiscoverable to false for scratch analysis. The parent and returned Namespace use stable generic node IDs. Loam creates the ordinary private Namespace, separate expiring lease, scoped token, and exact ordinary Admin grant in one transaction. The lease is lifecycle metadata, not an authorization bypass or a special Namespace kind.

json
{
  "node": {
    "id": "rw-us-east1-node-0195a3c47d3e7ab18c4f123456789abc",
    "parentId": "rw-us-east1-node-0195a3c47d3e7ab18c4f123456789abf",
    "kind": "namespace",
    "name": "visitor_analysis",
    "hierarchyVersion": 1
  },
  "lease": {
    "expiresAt": "2026-08-22T03:00:00Z",
    "analysisToken": "loam_an_v1_AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
  }
}

The response is Cache-Control: no-store. Its lease.analysisToken is a reveal-once, high-entropy bearer with the loam_an_v1_ prefix. Loam stores only a digest and non-secret bindings. Every later allowed regional request sends this value as the only Authorization: Bearer credential; the originating service JWT does not accompany it.

The token is authority; the Namespace ID is not

A Namespace node public ID identifies a resource and grants nothing. Each token is bound to one exact workspace, Namespace, service account, originating child key or durable human use assignment, purpose, and expiry. Every request live-rechecks the token, Namespace, service account, originating key or assignment, file_analysis, exact current Namespace grant, resource ceilings, entitlement, and explicit denies.

Revoking or narrowing the originating key, removing the human assignment or Namespace grant, suspending the account, deleting the Namespace, or expiring its lease denies the next request. A token created during a 15-minute human use-as session may live to the Namespace lease's recorded expiry because it binds the actor and durable assignment, not the short initiating JWT; those parent relationships are still rechecked live.

Never place an analysis token in a URL, filename, local or session storage, referrer, log, analytics event, error, or audit body. A direct API or CLI caller treats it as a scoped credential. MCP and browser clients use that same Bearer contract rather than a transport-specific authorization protocol.

Each visitor gets an independent Namespace and token

A minisite may use one service-account child key for all visitors, but it creates a new temporary Namespace and analysis token for each analysis workspace. If Visitor B combines B's valid token with Visitor A's Namespace, import, table, or run identifier, Loam returns the same redacted not-found response as a random resource and reads or mutates none of Visitor A's data. All visitors still share the service-account root's quotas, billing identity, and audit grouping.

2. Reserve a named file import

bash
curl -X POST "$DATA_PLANE_URL/v1/workspaces/$WORKSPACE_ID/nodes/$NAMESPACE_ID/file-imports" \
  -H "Authorization: Bearer $ANALYSIS_TOKEN" \
  -H "Idempotency-Key: import-orders-2026-08-21" \
  -H "Content-Type: application/json" \
  -d '{
    "tableName": "orders",
    "filename": "upload.parquet",
    "format": "parquet",
    "sizeBytes": 100663296,
    "mediaType": "application/vnd.apache.parquet",
    "sourceBatchId": "orders-2026-08-21"
  }'

Reserve the table and upload before sending bytes. The request requires an Idempotency-Key and exact tableName, filename, format, sizeBytes, and mediaType. Optional sha256 is a lowercase digest; optional sourceBatchId identifies the producer load independently of HTTP retry.

The filename is display and provenance metadata only. It never becomes an object key, SQL relation, resource ID, or authorization input. Public minisites send a synthetic value such as upload.csv or upload.parquet, not the visitor's local filename.

json
{
  "import": {
    "importId": "0190e714-e9d5-7c25-95e3-123456789abc",
    "namespaceId": "rw-us-east1-node-0195a3c47d3e7ab18c4f123456789abc",
    "targetTableId": "rw-us-east1-node-0195a3c47d3e7ab18c4f123456789abe",
    "tableName": "orders",
    "filename": "upload.parquet",
    "format": "parquet",
    "sizeBytes": 100663296,
    "status": "awaitingUpload",
    "uploadId": "0190e715-0a55-7dd8-9c81-abcdef123456",
    "runId": null,
    "counters": null,
    "terminalError": null,
    "createdAt": "2026-08-21T03:00:00Z",
    "updatedAt": "2026-08-21T03:00:00Z",
    "expiresAt": "2026-08-22T03:00:00Z"
  },
  "upload": {
    "uploadId": "0190e715-0a55-7dd8-9c81-abcdef123456",
    "uploadMode": "multipart",
    "completionId": "0190e715-0a55-7dd8-9c81-abcdef123457",
    "partSizeBytes": 67108864,
    "uploadOrigin": "https://storage.example.invalid",
    "parts": [],
    "expiresAt": "2026-08-21T03:15:00Z"
  }
}

The response contains a bounded FileImportSummary and the standard ingestion SignedPutCapability or MultipartUploadSession. The opaque UUIDv7 import, upload, completion, part, run, and future table identifiers grant no authority. The future table becomes catalog- and query-visible only after successful atomic publication.

3. Upload directly with the standard ingestion contract

For multipart transfer, request outstanding part capabilities in batches of at most 100 part numbers:

bash
curl -X POST "$DATA_PLANE_URL/v1/workspaces/$WORKSPACE_ID/nodes/$NAMESPACE_ID/file-imports/$IMPORT_ID/uploads/$UPLOAD_ID/parts" \
  -H "Authorization: Bearer $ANALYSIS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"partNumbers":[1,2]}'

Upload bytes directly to each exact uploadUrl, using its required headers and no Loam Authorization header. Keep signed URLs in memory only. Record each opaque partId, part number, and response ETag for completion. The DTO's expiresAt is authoritative; request replacement capabilities for unfinished parts after expiry.

The upload DTO also carries the trusted exact HTTPS uploadOrigin resolved by the regional plane. Local development may use an exact HTTP loopback origin. A browser uploader must reject every initial or replacement signed URL whose origin differs exactly; do not infer a host from a display name, accept redirects, or allow a caller override.

bash
curl -X POST "$DATA_PLANE_URL/v1/workspaces/$WORKSPACE_ID/nodes/$NAMESPACE_ID/file-imports/$IMPORT_ID/uploads/$UPLOAD_ID/complete" \
  -H "Authorization: Bearer $ANALYSIS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "completionId": "0190e715-0a55-7dd8-9c81-abcdef123457",
    "parts": [
      {"partId":"0190e716-0a55-7dd8-9c81-abcdef123458","partNumber":1,"etag":"<etag-1>"},
      {"partId":"0190e716-0a55-7dd8-9c81-abcdef123459","partNumber":2,"etag":"<etag-2>"}
    ]
  }'

Multipart completion validates the recorded completion identity, ordered part coverage, ETags, size, media type, and checksum when declared. It moves the import to readyToCommit but does not publish a table. A single PUT skips multipart completion.

Browser upload with the Loam SDK

A browser that intentionally owns the token constructs its client with createAnalysisClient. The generic uploadSignedData helper accepts the standard upload DTO and file bytes, pins every signed URL to the DTO's exact uploadOrigin, sends no Loam credential to object storage, and supports progress, cancellation, retry, concurrency, and non-secret part checkpoints:

typescript
import { createAnalysisClient, uploadSignedData } from "@loam/sdk";

// namespaceSession is the no-store create response intentionally returned by your Worker.
const analysis = createAnalysisClient({
  analysisToken: namespaceSession.lease.analysisToken,
  analysisTokenExpiresAt: namespaceSession.lease.expiresAt,
  workspaceId,
  dataPlaneUrl,
});
const createKey = crypto.randomUUID();
const commitKey = crypto.randomUUID();
const controller = new AbortController();

const reserved = await analysis.createFileImport(namespaceSession.node.id, {
  tableName: "orders",
  filename: "upload.parquet", // synthetic public-minisite provenance
  format: "parquet",
  sizeBytes: file.size,
  mediaType: "application/vnd.apache.parquet",
  idempotencyKey: createKey,
});
const upload = reserved.upload;

const parts = await uploadSignedData(upload, file, {
  signal: controller.signal,
  onProgress: ({ loadedBytes, totalBytes }) => updateProgress(loadedBytes, totalBytes),
  requestParts: upload.uploadMode === "multipart"
    ? (partNumbers, { signal }) => analysis.requestFileImportUploadParts(
        namespaceSession.node.id, reserved.import.importId,
        upload.uploadId, partNumbers, { signal },
      )
    : undefined,
});

if (upload.uploadMode === "multipart") {
  await analysis.completeFileImportUpload(
    namespaceSession.node.id, reserved.import.importId,
    upload.uploadId, upload.completionId, parts,
  );
}
await analysis.commitFileImport(
  namespaceSession.node.id, reserved.import.importId,
  upload.uploadId,
  {
    idempotencyKey: commitKey,
    ...(upload.uploadMode === "multipart"
      ? { completionId: upload.completionId }
      : {}),
  },
);

Keep the create and commit idempotency keys stable across retries. A server, Worker, or CLI may use asAnalysis after creation, or importFileAndWait as the all-in-one reserve, upload, complete, commit, and run-wait convenience. Both still use the same temporary Namespace and ordinary ingestion resources.

Existing bucket, existing ingestion staging

File imports use the configured regional production bucket and server-derived ingestion-staging/v1/ keys. They create no new bucket, credentials, or storage prefix. The API never accepts a bucket, object key, provider upload ID, destination path, source URL, or anonymous tmp/ object. The separate anonymous conversion namespace tmp/YYYY-MM-DD/... cannot be adopted into catalog data.

Production browser upload remains fail-closed until the existing bucket origin, exact CORS policy, multipart ETag exposure, and a real canary are verified. File bytes go from the browser to object storage; a minisite Worker handles bounded control messages only.

4. Commit and follow the ordinary ingestion run

bash
curl -X POST "$DATA_PLANE_URL/v1/workspaces/$WORKSPACE_ID/nodes/$NAMESPACE_ID/file-imports/$IMPORT_ID/uploads/$UPLOAD_ID/commit" \
  -H "Authorization: Bearer $ANALYSIS_TOKEN" \
  -H "Idempotency-Key: commit-orders-2026-08-21" \
  -H "Content-Type: application/json" \
  -d '{"completionId":"0190e715-0a55-7dd8-9c81-abcdef123457"}'

Commit repeats live authorization and validates the exact recorded object before accepting an ordinary ingestion run. For a single PUT, send strict {}; multipart sends its opaque completionId. The accepted response supplies the ordinary runId.

bash
curl "$DATA_PLANE_URL/v1/workspaces/$WORKSPACE_ID/nodes/$NAMESPACE_ID/file-imports/$IMPORT_ID" \
  -H "Authorization: Bearer $ANALYSIS_TOKEN"

Import states are awaitingUpload, readyToCommit, accepted, processing, succeeded, failed, cancelled, and expired. Poll the bounded import resource and its ordinary ingestion run for counters and a redacted terminal error.

The import's expiresAt is the staging reservation deadline; it does not extend the parent temporary Namespace's fixed expiry.

CSV or Parquet schema inference and bounded materialization publish one normal table, columns, snapshot, and parts atomically. A failed, cancelled, expired, or abandoned import exposes no partial table and uses standard exact-prefix ingestion-staging cleanup.

5. Add another file and use ordinary SQL

Repeat the file-import collection with another table name, such as customers. Both successful imports remain ordinary tables in the same Namespace. Assuming its absolute path is analysis.visitor_analysis, the analysis token can call the canonical query endpoint and join them with fully qualified relation paths:

bash
curl -X POST "$DATA_PLANE_URL/v1/workspaces/$WORKSPACE_ID/query" \
  -H "Authorization: Bearer $ANALYSIS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT c.segment, SUM(o.amount) AS total FROM analysis.visitor_analysis.customers c JOIN analysis.visitor_analysis.orders o ON o.customer_id = c.id GROUP BY c.segment",
    "format": "json"
  }'

The token permits exactly one parsed SELECT. Every relation under joins, CTEs, subqueries, views, and table-valued constructs must resolve to immutable catalog IDs inside the token's exact Namespace. Cross-Namespace references, external file, object, or network access, unsafe table functions, multiple statements, DDL, and DML fail before execution. Query results use the ordinary JSON, NDJSON, or Arrow IPC formats, row and byte limits, deadlines, admission, and read leases.

6. Extend, keep permanently, or delete now

A temporary Namespace lease has a fixed expiresAt. Uploading, importing, polling, or querying never silently extends it. The analysis token may explicitly extend within plan and seven-day hard-lifetime caps:

bash
curl -X PATCH "$DATA_PLANE_URL/v1/workspaces/$WORKSPACE_ID/nodes/$NAMESPACE_ID/lease" \
  -H "Authorization: Bearer $ANALYSIS_TOKEN" \
  -H "Idempotency-Key: extend-analysis-2026-08-21" \
  -H "Content-Type: application/json" \
  -d '{"expiresInSeconds":172800}'

Extension changes the lease and token expiry without moving or renaming the Namespace and without rotating or revealing another token. It requires an Idempotency-Key.

Keeping the data permanently is an explicit full-identity action, not a copy. Use a current service-account or human-assumption bearer with namespace_lifecycle and Admin on the Namespace:

bash
curl -X DELETE "$DATA_PLANE_URL/v1/workspaces/$WORKSPACE_ID/nodes/$NAMESPACE_ID/lease" \
  -H "Authorization: Bearer $SERVICE_JWT" \
  -H "Idempotency-Key: retain-analysis-2026-08-21"

Retain removes the separate lease, clears expiry, preserves the ordinary Namespace and every Table and part ID, and revokes its analysis tokens. An analysis token cannot retain data.

To discard the analysis immediately, use the generic node delete route with the exact analysis token:

bash
curl -X DELETE "$DATA_PLANE_URL/v1/workspaces/$WORKSPACE_ID/nodes/$NAMESPACE_ID?expectedHierarchyVersion=1&cascade=true" \
  -H "Authorization: Bearer $ANALYSIS_TOKEN"

Delete or expiry first makes the Namespace, tables, imports, runs, and tokens inaccessible. The ordinary Namespace tombstone, read-lease-aware deferred deletion, ingestion-staging cleanup, retry, metrics, and alerts then remove exact recorded objects. Customer storage accounting stops at logical deletion even if physical cleanup is still retrying.

The SDK exposes these operations as extendNamespaceLease, retainNamespace, and deleteNode. Retention requires a full human or service-account client; use the scoped analysis client for extension or early deletion.

Quota, audit, and retry behavior

Plan-governed admission covers temporary Namespaces, imports, declared and staged bytes, active multipart uploads, materialization, query work, extensions, and maximum lifetime before Loam issues capabilities or reserves work. Every child key, analysis token, and assigned human shares the service-account root meters; rotating a key cannot reset quota or usage.

Audit records the actor, effective service account, originating key or assignment, token record, Namespace, import, run, table, action, and decision. It excludes token plaintext, SQL text, visitor filenames, signed URLs, object keys, ETags, row values, and file contents.

  • 400: invalid strict request, metadata, checksum, or SQL.
  • 401: malformed or missing Bearer credential.
  • 403: a full human or service identity lacks current capability, grant, ceiling, or entitlement.
  • 404: an analysis token is invalid, expired, substituted, or paired with an inaccessible resource; these cases share one redacted response.
  • 409: idempotency payload or lifecycle state conflicts.
  • 413: encoded file or bounded response limit exceeded.
  • 429: root quota or regional capacity is full; respect Retry-After.
  • 503 / 504: temporary storage, ingestion, query, or deadline failure; retry only an idempotent operation.

Last updated 2026-08-21