Designing the SmartLoads Partner API with hashed keys, keyset cursors and atomic rate limits

How SmartLoads built a multi-tenant partner API on Supabase Edge Functions and PostgreSQL. It covers API key format and hashing, scopes that fail closed, explicit response shapes, keyset pagination, a race-free per-key rate limit in one SQL function, and serving it all from api.smartloads.io.

SmartLoads decided to be an integration partner in its own right. Instead of building a connector into one TMS, it gives each agency an API that any TMS, spreadsheet or in-house tool can call. The first version is read-only: loads, AI carrier leads and Cover Cards, plus signed webhooks (covered in a separate paper). The public docs are here.

This paper covers the design decisions underneath: keys, tenancy, response shapes, pagination, rate limiting and hosting. The stack is Supabase (PostgreSQL 17 and Deno Edge Functions) behind Vercel.

Keys

Format

A key looks like sl_live_ followed by 40 base62 characters. The prefix makes keys easy to recognize in logs and easy for secret scanners to catch, and leaves room for sl_test_ later. 40 base62 characters carry about 238 bits of randomness.

Generating base62 from random bytes has a classic bias trap: byte % 62 makes the first eight characters slightly more likely, because 256 isn't a multiple of 62. The generator uses rejection sampling instead:

// supabase/functions/_shared/partnerApi.ts
let body = "";
while (body.length < 40) {
  for (const b of crypto.getRandomValues(new Uint8Array(64))) {
    if (b < 248 && body.length < 40) body += BASE62[b % 62]; // 248 = 4 × 62
  }
}

Storage

Only a SHA-256 hash of the key is stored, along with its first 12 characters (sl_live_ab12) so admins can tell keys apart. The full key is shown once, at creation. A fast hash is appropriate here, unlike for passwords: the keys are long and random, so there's nothing to brute-force, and the lookup runs on every request.

key_hash text NOT NULL UNIQUE CHECK (key_hash ~ '^[0-9a-f]{64}$')

Each key belongs to exactly one agency, has scopes, records last_used_at, and can be revoked. An agency can have 10 active keys, so each tool or partner gets its own and can be cut off alone.

Scopes that fail closed

There are three read scopes: loads:read, leads:read and cover_cards:read. The subtle case is a typo. An admin tool that sends ["load:read"] shouldn't end up creating an all-access key.

export function normalizeScopes(input: unknown): Scope[] {
  if (input == null) return [...SCOPES];             // omitted → documented default
  const list = Array.isArray(input) ? input : [input];
  return [...new Set(list.map(String))].filter((s) => SCOPES.includes(s)); // may be []
}

Omitting scopes gives the documented default. An explicit list keeps only recognized scopes, and if that leaves nothing, key creation is rejected with a 400.

Tenancy

The API function runs with the database's service role, so row-level security doesn't protect it. Tenancy is enforced in code instead, and it's simple: the agency comes from the key, never from the request. Every query starts with .eq("agency_id", key.agency_id), and a record from another agency is a 404, the same as a record that doesn't exist.

Response shapes

The API never returns raw table rows. Each resource has an explicit mapping (publicLoad, publicLead, publicCoverCard):

export function publicLoad(r: Row) {
  return {
    id: String(r.id),
    load_number: s(r.load_number),
    status: loadStatus(r),               // open | booked | covered | archived
    pickup: place(r.pickup_city, r.pickup_state),
    delivery: place(r.dest_city, r.dest_state),
    equipment: { type: s(r.trailer_type), length_ft: n(r.trailer_footage), tarp_required: r.tarp_required === true },
    rate: { basis: perTon ? "per_ton" : "flat", customer_total: n(r.customer_invoice_total), /* … */ },
    // …
  };
}

There are three reasons:

1. The schema can change without breaking partners. The loads table has grown through hundreds of migrations and will keep changing.
2. Nothing leaks by accident. The loads table holds the booked carrier's driver phone and other fields a partner shouldn't see. A new column isn't exposed until someone deliberately adds it to the mapping, and a test asserts the driver phone isn't in the shape.
3. Derived fields are computed once. Status, for example, is derived from several internal columns (dispatch status, is_active, archived_at, booked_at, covered_at), and partners get one clean enum.

Pagination: keyset cursors

Offset pagination (?page=3) breaks on data that changes while you page through it. Rows shift between pages, so you skip some and see others twice. Load boards change constantly, so every list uses keyset pagination on a timestamp plus the id, in ascending order: (updated_at, id) for loads and leads, and (created_at, id) for Cover Cards.

if (cursor) {
  query = query.or(
    `updated_at.gt."${cursor.ts}",and(updated_at.eq."${cursor.ts}",id.gt.${cursor.id})`,
  );
}
const rows = await query.order("updated_at").order("id").limit(limit + 1);
const more = rows.length > limit;

• The id tiebreaker makes the order total, so rows that share a timestamp are never skipped.
• Fetching limit + 1 rows tells us whether there's another page without a count query.
• The cursor is base64url-encoded JSON of [timestamp, id]. It's opaque to clients and validated on the way in; a malformed cursor is a 400, not a server error.

Incremental sync falls out of the same design. A client saves the newest updated_at it has seen and sends ?updated_since= next time. It works because loads and leads have triggers that set updated_at on every change.

Rate limiting in one SQL function

The limit is 120 requests per minute per key. The obvious approach is to count recent requests and, if under the limit, log this one. That's a race: two concurrent requests both count 119 and both proceed.

The fix is to make "count and reserve" a single atomic step, in a PostgreSQL function the API calls on every request:

CREATE FUNCTION public.api_reserve_request(
  p_key_id uuid, p_agency_id uuid, p_method text, p_path text, p_limit integer
) RETURNS TABLE (allowed boolean, used integer, log_id bigint) AS $$
DECLARE v_used integer; v_id bigint;
BEGIN
  -- Serialize requests per key for the rest of this transaction.
  PERFORM pg_advisory_xact_lock(hashtextextended('api_request_log:' || p_key_id::text, 0));
  SELECT count(*) INTO v_used FROM api_request_log
   WHERE key_id = p_key_id AND created_at >= now() - interval '1 minute'
     AND status IS DISTINCT FROM 429;
  IF v_used >= p_limit THEN
    -- Log at most one rejection per key per minute.
    IF NOT EXISTS (SELECT 1 FROM api_request_log
                    WHERE key_id = p_key_id AND status = 429
                      AND created_at >= now() - interval '1 minute') THEN
      INSERT INTO api_request_log (key_id, agency_id, method, path, status)
      VALUES (p_key_id, p_agency_id, p_method, p_path, 429) RETURNING id INTO v_id;
    END IF;
    RETURN QUERY SELECT false, v_used, v_id;
  ELSE
    INSERT INTO api_request_log (key_id, agency_id, method, path, status)
    VALUES (p_key_id, p_agency_id, p_method, p_path, NULL) RETURNING id INTO v_id;
    RETURN QUERY SELECT true, v_used + 1, v_id;
  END IF;
END $$ LANGUAGE plpgsql;

Details that matter:

• The advisory lock is per key. Concurrent requests on different keys never wait for each other.
• An allowed request inserts a reservation row with a NULL status. The API fills in the real HTTP status after responding. The same table serves as the rate-limit window, the audit log and the source for "last used".
• Rejections are logged at most once per key per minute, and don't count toward the limit. A client stuck in a retry loop can't grow the table without bound.
• It fails closed. If the function errors, the API returns 503 rate_limit_unavailable rather than skipping the check.
• Only the server can call it: REVOKE ALL … FROM PUBLIC, anon, authenticated; GRANT EXECUTE … TO service_role.
• Retention is a nightly pg_cron job that deletes log rows older than 30 days.

We tested it on a local PostgreSQL 17 with the limit set to 2: allowed, allowed, 429, 429. The anonymous role couldn't execute the function.

Responses to authenticated requests carry X-RateLimit-Limit and X-RateLimit-Remaining, and a 429 adds Retry-After: 60.

Errors that tell the truth

Every error is an HTTP status plus { "error": { "code", "message" } }. One rule came out of review: a database error is a 500, never a false 401 or 404. If the key lookup fails because the database hiccupped, telling the client "invalid API key" would send them off to rotate a perfectly good key.

last_used_at is updated at most once every five minutes per key. It's a signal for admins, not an audit trail (the request log is the audit trail), and writing it on every request would add a row update to every call.

Hosting at api.smartloads.io

The API is one Supabase Edge Function (api-v1). Supabase's gateway normally expects a Supabase JWT, which API keys aren't, so the function is deployed with verify_jwt = false and authenticates keys itself.

The public address is a Vercel host-based rewrite on the main site's project:

{
  "source": "/v1/:path*",
  "has": [{ "type": "host", "value": "api.smartloads.io" }],
  "destination": "https://<project>.supabase.co/functions/v1/api-v1/v1/:path*"
}

The router accepts both path shapes (/v1/loads and /functions/v1/api-v1/v1/loads), so the function works the same behind the rewrite or called directly. The first live keyed request returned 200 on September 26, 2026.

What we'd tell another team

• Hash keys and show them once. Store a short prefix so admins can tell keys apart.
• Make scopes fail closed. Treat "nothing recognized" as an error, not as a default.
• Use explicit response shapes from day one. Retrofitting them after partners depend on raw rows is painful.
• Use keyset pagination with a total order. Add the id tiebreaker.
• Do rate limiting's count-and-reserve in one transaction, with a per-key lock. Decide what happens when the limiter itself fails, and fail closed.
• Don't let infrastructure errors look like client errors.

Frequently asked questions

Why not use Supabase's built-in auth for the API?

Supabase auth is built for users signing in to apps. Partners need long-lived, per-tool credentials that an admin can scope and revoke on their own, which is what API keys are for. The API function checks keys itself and runs queries scoped to the key's agency.

Why is the rate limit in the database instead of Redis?

The request log already lives in PostgreSQL, and at 120 requests a minute per key the database handles it easily. One SQL function gives atomicity, the audit log and "last used" in one place, with no extra service to run. A dedicated store would make sense at much higher volume.

How do partners get changes without polling everything?

They save the newest updated_at they've seen and ask for ?updated_since= on the next call, or they subscribe to webhooks and fetch only what changed.

Is the API multi-tenant?

Yes. Every key belongs to one agency, and every query is filtered by that agency. A record from another agency returns 404, the same as one that doesn't exist.