Private workout ingestion canary

Purpose

This is a canary runbook, not a workout-specific Loam feature. The SGP46 brand feed uses an ordinary private customer table, pipeline, and delegated API-key credential. The required canary lifecycle uses only the standard ingestion and query REST APIs on the workspace's regional data plane. The credential uses current Engine grants under its creating user. The schema and mappings belong to the customer. No data is written to a system workspace and no ingestion action creates or changes an Everyone grant.

Do not mark this canary complete until every observation below has been captured from the real authorized workspace. CI should use a synthetic equivalent, not depend on SGP46 production account state.

Provision least privilege

1. With a human session holding Admin on the target Namespace, create a private Table through regional POST /v1/workspaces/{workspacePublicId}/query and an ordinary CREATE TABLE statement using an absolute, workspace-rooted path of arbitrary Namespace depth. Send the human Supabase access JWT; the URL carries the workspace public ID. Retain the returned stable Table node public ID, such as rw-us-east1-node-.... The table must contain a stable workout_id column; the pipeline's key declaration is the logical uniqueness contract and does not require a physical unique index.
2. Using the same human Admin session, create an upsert pipeline bound to that stable Table node ID with keyColumns: ["workout_id"] and strict schema mode.
3. On the workspace's returned dataPlaneUrl, create a dedicated API-key credential through POST /v1/workspaces/{workspacePublicId}/credentials. Send the human Supabase access JWT. Give it an allow editor rule whose resourceNodeId is the stable Table node public ID. Add a roleless explicit deny whoseresourceNodeId is a separate sensitive Table node public ID, then store the one-time API key in the brand server's deployment secret store. List, inspect, update, and revoke the credential through the corresponding workspace-scoped credential routes on that same regional host.
4. Exchange that API key through regional POST /v1/workspaces/{workspacePublicId}/token/exchange. Verify that the response's workspaceId and dataPlaneUrl exactly match the configured workspace before accepting its short-lived JWT. Use that regional URL, JWT, and workspace-scoped paths through @loam/sdk for ingestion, authenticated query readback, and reconciliation. The Editor credential executes the prepared pipeline; it must not be used to create or change tables or pipeline configuration.
5. Record the target Namespace and Table grants before the first load. Neither ancestry may provide an Everyone Viewer allow, so the Table must be private.

Submit a synthetic workout

{
  "sourceBatchId": "canary:workout:provider-activity-123:v1",
  "rows": [
    {
      "workout_id": "provider-activity-123",
      "started_at": "2026-07-25T06:30:00+12:00",
      "activity_type": "run",
      "duration_seconds": 1800,
      "distance_metres": 5000
    }
  ]
}

Submit the body with a stable idempotency key such as workout:provider-activity-123:v1, poll the run, and record its terminal status, inserted/updated counters, table ID, and commit identity. Do not use a timestamp generated at retry time as either identity.

Required observations

1. Retry the exact initial request with the same idempotency key. It must return the original run and create no second commit.
2. Submit a correction for provider-activity-123 with a new source batch and request key, changing duration_seconds to 1812. The upsert must report one update and leave one current row for that workout ID.
3. Through @loam/sdk, make an authenticated standard POST /v1/workspaces/{workspacePublicId}/query read on the returned regional dataPlaneUrl, and confirm the corrected value. No customer-specific route or secondary protocol is part of this check.
4. Reconcile removal of the synthetic workout through authenticated DELETE SQL sent to POST /v1/workspaces/{workspacePublicId}/query. Supply a stable operationKey, then query again and confirm that no row remains. If the request outcome is uncertain, retry the identical statement with the same operation key.
5. Separately retain integration evidence that a Viewer principal cannot perform SQL DML or ingestion. That authorization test is independent of the required workload canary and does not require a second transport.
6. Recheck effective public access. The Table must remain private and must not appear through any public workspace endpoint.
7. Confirm the upload/run staging cleanup reaches a terminal cleanup state without touching the table's durable parts.

Evidence to retain

• workspace public ID and exact regional data-plane URL;
• pipeline ID and immutable version ID;
• the API-key credential UUID and its Engine-confirmed allow/deny rules;
• original run ID returned by both retry attempts;
• correction run counters and committed table ID;
• standard query result and reconciliation operation key;
• before-and-after effective public-access evidence; and
• cleanup state and completion timestamp.

Keep raw API keys, JWTs, signed URLs, and customer row values out of logs and the evidence record. Neither the API key nor its JWT contains the permission rules; retain only the current Engine grant evidence.

MCP may be tested independently as an optional read-only integration, but it is not a prerequisite, fallback, or success condition for this API canary.